Create the tables as per the Database design given to you in the content. If you think about a different design, that's fine too, just justify why you think that will work better.

Create the primary keys and foreign keys for each table as specified in the Entity Relationship Diagram given.

Submit a screenshot (or however many you want) of your design from mySQL.

Module 8 - Relational Databases

In the previous module you were required to design some database tables. This was to see how you think. Here, I'm giving you a schema for our project. Note that you don't have to use this schema. If you have a better schema idea please go ahead and use that. See the flow of the final project and the database schema given below.

Application Flow:

1. Productlist.php - You should have completed this last week.

This page will query the products from the PRODUCTS table. The products shown on this page should be dynamically populated through the query results. The Add to Cart button should be dynamically built.

2. Shoppingcart.php - This you worked on earlier. We will work more on this in the coming weeks.

This page will pull the cart details from the session. It will build another row in the cart using the url parameters you pass in through the Add to Cart button. It will also contain totals calculated.

3. Customerinfo.php - You already worked on this earlier.

This page is a form that collects customer information. This information once submitted should be saved in the session along with the shopping cart.

4. Review Order

This page summarizes from the session all of the cart details and customer info details. It includes totals, shipping and tax.

5. Confirm order

This page will do a couple of things - save the order in the tables (item and order), save the customer details in the customer table, email the customer the order details and customer details.

Relational Database Concepts:

Primary key and Foreign Key

A primary key is a field that uniquely identifies a row in the table. A key value can not occur twice in one table. With a primary key, you can only find one row.

A foreign key is the linking pin between two tables. So a primary key of one table can be a foreign key of another table. Example, CustomerID is a foreign key in the Order table, or Order ID and ProductID are foreign keys in the item table.

Referential Integrity:

Referential Integrity makes sure that a foreign key value always points to an existing row. By setting up relationships correctly, you prevent hanging child records. Example, an order from the order table should never be deleted without first deleting items. Similarly, items in the item table should not be added without having an order first inserted into the Order table.

Database Tables:

Product table

Store all of your product details, images and prices here manually. The productlist.php page is powered by this table. Data in this table is manually entered by you directly through mysql. You don't write into this table through code, you only read it through code.

Primary key: ProductID

Customer table

This table will contain only information related to the customer. The details are collected on the customer info page. Saving is done on the confirm order page.

Primary Key: CustomerID

Order table

This table contains high level order, shipping and billing info. Part of this info comes from the customerinfo page and the other part comes from the order itself. Saving is done on the confirm order page.

Primary Key: OrderID

Foreign Key: CustomerID

Item table

This table contains details about the order. You enter details into the order table first and then into the item table. Saving is done on the confirm order page.

Primary Key: ItemID

Foreign Key: OrderID

Foreign Key: Product ID

Basic Database Queries

To query the products,

SELECT productid, title, description, image, stock, price FROM product

To insert into the Customer table:

INSERT INTO CUSTOMER(customerID, FirstName, LastName, Address, City, State, Zip) values (2, 'John', 'Doe', '1244 1st Street', 'San Francisco' 'CA', '99999'); 

To insert into the Order table:

INSERT INTO ORDER (orderID, customerID, billAddress, billCity, billState, billZip, shipAddress, shipCity, shipState, shipZip, shipType, orderSubtotal, tax, shipCost, orderTotal) values (1, 2, '1244 1st Street', 'San Francisco' 'CA', '99999', '1244 1st Street', 'San Francisco' 'CA', '99999''overnight', '200.00', '20.00', '5.00', '225.00');

To insert into the Item table:

INSERT INTO ITEM(itemID, orderID, productID, unitPrice, quantity, subtotal) values (1, 1, 1, '10.00', '1', '10.00'); 

Note: you don't have to have all of these tables. If you want to simplify you may.

Example, you may decide to combine the order and customer tables and not save customers separately. That is fine too.