602 Project 5 CREATING A WEB PORTAL FOR A BOOKSTORE

Ordering a Book on the Web Site

The procedure to order a book on the Web site is as follows:

1.The user selects a book to order in the Orders page.

2.The user then clicks on the Order button to order the book.

3.The user is taken to the Orders page. On the Orders page, the customer details, such as name, address, and credit card information, are specified.

4.After specifying the required details, the user clicks on the Order button.

5.When the Order button is clicked, the details submitted by the user is sent to the database maintained by the publishing house.

NOTE

To send the information submitted by the user to the database of the publishing house, the Web site uses a Web method created in the Web service. You will learn about this method, AcceptDetails(), in Chapter 29, “Developing Web Services.”

6.The details of the customer and the order placed by the customer are stored in the database at the publishing house.

7.At the site of the publishing house, the request of the user is processed and the book is delivered to the client.

Project Design

Having examined the project requirements in detail, you need to create a detailed design of the project. You can create a design of the application in the project design stage. The project design stage includes identifying the database design and the database schema. To finalize the database schema, you first need to decide on the tables required in the database. Then, a detailed analysis of the database design is required to identify the relationships between various tables in the database. Based on this, a detailed schema of the database is created. It is critical to examine all possible requirements and incorporate them in the database schema to avoid reworking at a later stage. The following section delineates the database design for the Web portal.

Database Design

After creating the structure of database tables and normalizing the structure, you arrive at a database schema that is appropriate for your Web portal project. Normalizing the database design helps to remove data redundancy.

In the case of this Web portal, data is not stored in the database of any one organization.Data is spread across the databases of Bookers Paradise and the two publishing houses. It therefore becomes all the more essential for you to carefully plan the database design and normalize the design. This will help you avoid data redundancy across tables in the databases of different organizations. You have learned about the basic concepts of creating an SQL database and normalization in Chapter 7, “Project Case Study,” in the section “Normalization.”

In addition to creating the database design for the three organizations, you need to establish relationships between the tables in the databases. This will help you program your application to query and update data in the related tables. I will discuss how to create relationships between tables later in this chapter. However, I will first discuss the structure of the tables in the databases of the three organizations.

Database Design for Bookers Paradise

Bookers Paradise plans to launch a Web site that allows visitors to view information about books and to place an order for a book. To do this, Bookers Paradise maintains a database called BookersDB. This database includes two tables, BookersOrders and BookersCustDetails. The following section discusses these tables in detail.

The BookersOrders Table

The BookersOrders table stores information about the order placed by a customer for a book.Therefore, this table includes information such as OrderNo, CustomerID, and ISBNNo of the book. When a customer places an order, a unique identification number, CustomerID, is assigned to the customer. In addition, for each order, a unique number, OrderNo, is assigned.The OrderNo is defined as the primary key for the table. Table 27-1 shows the fields in the BookersOrders table in the BookersDB database.

604 Project 5 CREATING A WEB PORTAL FOR A BOOKSTORE

Table 27-1 Structure of the BookersOrders Table

NOTE

In the preceding structure of the BookersOrders table, the value 0 for the Allow Nulls column does not allow the user to leave this field blank. However, if you specify the value as 1, the field is optional when adding a new record. I will be using this convention while discussing the structure of the rest of the tables.

The BookersCustDetails Table

In addition to storing the details of the order, Bookers Paradise wants to store the details of all the customers who place an order for a book. To do this, the BookersCustDetails table is created. The BookersCustDetails table contains fields

such as CustomerID, CustomerName, BillingAddress, and so on. The CustomerID

field is set as primary key for this table. Table 27-2 shows the details of the

BookersCustDetails table.

Table 27-2 Structure of the BookersCustDetails Table

about the books published by Black and White Publications and the customers who order the books. To store this information, the BWDB database has two tables, BWCatalog and BWOrders. The following sections discuss these tables in detail.

The BWCatalog Table

The BWCatalog table stores information about the books published by Black and White Publications. Table 27-5 shows the structure of the BWCatalog table.

Table 27-5 Structure of the BWCatalog Table

As you can see, the BWCatalog table stores information such as the ISBN number, name, author, price, and category of the book. In addition, the preceding table contains the AboutTheAuthor field. You can store some information about the author of the book in this field. The ISBNNo field is set as the primary key for this table.

The BWOrders Table

Similar to the orders table of Deepthoughts Publications,the orders table of Black and White Publications stores information about the orders that are sent by the site of Bookers Paradise. Table 27-6 shows the fields contained in the BWOrders table in the BWDB database.