E-Commerce: Fundamentals and Applications

1. E-Commerce: Fundamentals and Applications Chapter 5 : Server-Side Programming II Database Connectivity

2. Outline • Overview of relational database systems • Common SQL commands and their usage

3. Relational database • Database: a shared collection of interrelated data in a structured form. • Relational database: uses relations to represent entities and relationships (the E-R relationship). • Schema: describes the structure of the database. • The relations are essentially "tables". These tables can be manipulated using SQL (Structured Query Language). • Fig. 5.1 shows a snapshot of BOOK database which contains three related tables.

4. Snapshot of BOOK Database (Fig. 5.1)

5. Basic SQL statements • In this chapter, we will provide a brief overview of SQL (The Structured Query Language) using the BOOK database as an example of how to manipulate a relational database based on SQL commands. • There are four different basic SQL statements. They are: • SELECT statement; • INSERT statement; • UPDATE statement; and • DELETE statement.

6. SELECT Statement • The SELECT command displays the results according to the user’s specified condition(s). • A typical form of the SELECT statement is given by: SELECT fields FROM table WHERE criteria • Example SELECT ISBN, Name FROM sbook WHERE Publisher='Wiley' • It displays all the records with Publisher name "Wiley" and the records will contain only the fields (ISBN, Name) as shown in Fig. 5.2.

7. ISBN Name 0471176117 Java Electronic Commerce Sourcebook 0471192236 E-commerce Security Conditional Query on sbook Table (Fig. 5.2)

8. Composite SELECT Statement • Actually, we can link-up different tables during a query. • Let’s see the following example: SELECT Customer.Customer_ID, Customer.Firstname, Transaction.Transaction_No, Transaction.ISBN FROM Customer, Transaction WHERE Customer.Customer_ID = Transaction.Customer_No AND Customer.Customer_ID='JO321' • A snapshot of the search result is shown in Fig. 5.3.

9. Customer_ID Firstname Transaction_NO ISBN JO321 Jones 0001 0137491360 JO321 Jones 0002 1562054961 JO321 Jones 0003 0471176117 JO321 Jones 0009 0471176117 A Composite Query on Customer and Transaction Tables (Fig. 5.3)

10. INSERT Statement • The INSERT statement is for adding new rows to a table. • INSERT statement comes in two different forms • allows the user to add only a single new row • allows the user to add a set of rows to a table using information from another table. • INSERT statement to add a new row • The typical form of this SQL INSERT is: INSERT INTO table (field_name1, field_name2, .., field_nameN) VALUES (field_value1, field_value2, … , field_valueN )

11. Customer_ID Lastname Firstname Tel no CH222 Simon Chan 1243-4524 CL444 Tim Clarke 5468-1453 JO321 Larry Jones 4562-4555 LE123 Lee Peter 1234-9142 TO133 Tommy Raymond 2478-4699 INSERT to Add New Record (Fig. 5.4) • Example INSERT INTO Customer (Customer_ID, Lastname, Firstname, “Tel no”) VALUES (’TO133', 'Tommy', ’Raymond', '2478-4699')

12. UPDATE Statement • The UPDATE statement is used to modify the value(s) of the data within a table, and the typical form of it is: UPDATE table SET field_name1 = field_value1, field_name2 = field_value2, .. , field_nameN = field_valueN [WHERE <condition>] • Using the UPDATE statement, we can also modify the specified record(s) utilising some calculations. • Example: • Update transaction no ‘0010’ (in transaction table) for buying 2 books instead of 1 book and recalculate the total amount as well: UPDATE Transaction SET Qty = 2, Total = Total * 2 WHERE Transaction_No = ‘0010’

13. Transaction_No Customer_No Date ISBN Qty Total 0001 JO321 1/1/00 0137491360 1 $380.00 0002 JO321 1/1/00 1562054961 2 $456.00 0003 JO321 1/1/00 0471176117 1 $280.00 0004 LE123 1/5/00 0079132707 2 $810.00 0005 LE123 1/5/00 0471192236 2 $440.00 0006 CL444 1/5/00 0471176117 1 $280.00 0007 CL444 1/5/00 0471192236 1 $220.00 0008 CL444 1/5/00 1886801088 1 $324.00 0009 JO321 1/10/00 0471176117 2 $560.00 0010 CH222 1/18/00 1562054961 2 $456.00 Using UPDATE Statement to Modify Transaction Table (Fig. 5.5)

14. DELETE Statement • The DELETE statement is used to remove a record or a series of records from a table under specific condition(s). • The typical form of it is given by: DELETE FROM table [WHERE <condition> ] • Using the Customer Table as an example, we can delete all the records with Lastname(s) containing the character “i” by using the following statement: DELETE FROM Customer WHERE Lastname LIKE ‘%i%’

15. Customer_ID Lastname Firstname Tel no CH222 Chan Simon 1243-4524 CR443 Jane Craw 4875-2222 JO321 Larry Jones 4562-4555 TO133 Tommy Raymond 2478-4699 LE123 Lee Peter 1234-9142 Customer Table After DELETE Operation (Fig. 5.6)