Question 1. Design a database to store data for customer orders. The company requesting this database uses the web as a platform to sell books directly to the public. The order form included as Figure 1 shows the data used during the ordering process, but of course the company has easy-to-use graphical forms on its web site. You can assume the following about the ordering process in this organization:

• Orders are placed over the web or by telephone. The data on the order form below is captured when an order is taken.
• The company does not capture credit card data in this database that information is stored by their billing agency.
• The company stores the billing address information for a customer as part of the customer information. Customers are kept on file whether or not there is a current order for a customer. It is possible for a customer to have more than one order on file, but each order is for one and only one customer. There is only one customer billing address per customer.
• There is only one shipping address per order. (Note: we will discuss in class how the design must change to accommodate multiple shipping addresses per order, but you can assume only one per order for your design.)
• The shipping address may differ from the customer billing address.
• Each order consists of one or more books. Each book can be on zero or more orders.
• The price for a given book is the same for every order. The price is not calculated it is functionally dependent on the ISBN for the book. This company ships only new books.
• The extended price, shipping cost, discount amount, and order total are all calculated (derived) fields.
• The discount code is entered when an order is entered into the database.
• Each book on an order may have a different quantity ordered. In the sample data, the customer ordered a quantity of one for ISBN 978-013544619, but ordered a quantity of two for ISBN .
• Each book on an order may have a different estimated shipping date. The estimated shipping date is related to a book on an order the quantity ordered does not affect the stored estimated shipping date structure.

After an order is placed, the company ships the book(s) to the customer. The data on the shipping form included as Figure 2 is captured when the company ships the book(s) to a customer. Here is some information about the shipping process:

• Books are shipped from a variety of different warehouse locations around the world to customers. Each location is identified by its locationID. The company keeps track of the name of the warehouse at the location. A given locationID has only one warehouse name associated with that location.
• The company may receive an order for a quantity of two for the same book, as shown in the sample order, but then actually ship that same book from two different locations, as shown in the two sample shipping forms. The final shipping quantity is an overshipment in this example, but the company needed to ship from two different warehouse locations and made a mistake in the quantity on the second shipment. Thus, a book on an order may have a minimum of zero shipments associated with it, or may have multiple shipments associated with it. There will be a unique tracking number for each shipment made.
• A shipment may consist of more than one book on an order, as shown in shipping form example #1 (Figure 2), however, there will only be one order affiliated with a given tracking #. A unique tracking number is always generated for a shipment of a different order.
• The actual shipping date may differ from the estimated shipping date. The actual ship date is the "Date Shipped" recorded on Figure 2. The company wants to store both the actual and estimated shipping date in this database.
• The company may make a partial shipment (ex: ship only 1 book when the order calls for 2 of the same book; another ex: ship 2 of the books on an order and delay shipment of the third book).
• The company may accidentally overship a book (example: the order says that a quantity of 2 is ordered, but the company accidentally ships 3 of ISBN 978-0345807298). The company wants to keep track of the quantity shipped of each book on each order.
• The database should not prevent partial shipments or overshipments these are simply circumstances that may happen.