Chapter $10(30$ pts$)$

$$

Database Design

Complete the following database design based on the requirements.$$ If you chose to free$-$hand draw your solution, you can either scan it or take a picture of it and attach it to the Brightspace assignment $($just make sure it is dark enough I can read it$).$ If you do it on your computer, you can just attach that file to the Brightspace assignment.

$$

Background:

$$

I have a company and need to design a database that houses data on my products, customers and their orders.$$ After lengthy discussions with the business units, we have come up with the following tables of data.

$$

Customer:$$ CustomerID, CustLastName, CustFirstName, CustAddress, CustCity, CustST, CustZip, CustPhone

$$

Orders:$$ OrderID, CustomerID $($customer that ordered it$),$ OrderDate, ShipToAddress, ShipToCity, ShipToST, ShipToZip, ShipDate

$$

OrderLineItem:$$ OrderID, OrderSequence, ProductID, OrderQty,

$$

Product:$$ ProductID, ProductName, UnitPrice

$$

Step #$1.$ Draw a database diagram with these $4$ tables.$$ Indicate the relationship between the tables and label primary keys and foreign keys in each table.

$$

Step #$2.$ We just got more information from the business units that we need to have an additional $2$ tables added to our diagram.$$We need an Employees table so that we can track which employee created the order.$$ We need a shipper table so we can track which shipping company was used to ship the order.$$ Add both of these tables to the diagram, add necessary fields to the appropriate existing tables, indicate the relationships to those tables, and identify primary and foreign keys on the new and existing tables.$$

$$

The first new table is an Employees table.$$ It should have EmployeeID, EmpFirstName, EmpLastName, SSN$,$ HireDate.$$

$$

The second new table is a Shippers table.$$ It should have$$ ShipperID, ShipperName, ShipperAddress, ShipperCity, ShipperST, ShipperZip, ShipperPhone

$$

Step #$3$ Now that the design has been completed, identify which fields on each table need to be indexed.$($You don$$t need to identify PKs as they are already indexed$).$ You can highlight or put an asterisk next to the fields that should be indexed.$$ For each field, explain why you would index that field.$$ Think about ways that users might search for information on these tables.