Assignment

Planning a Database Worksheet

	Which Table Should the Field be Placed in?	How Should the Field be Designed? (e.g. text, 20 characters in length)	Primary Key or Foreign Key? In which table? (PK: table, FK: table)
LastName
Size
OrderDate
Category
ProductDescription
FirstName
ZipCode
OrderDetailId
OrderID
CustomerID
State
HomePhone
Address
City
ProductID
Descrption
QuantityOrdered
NoInStock
CustomerSSN
Price

Using the list of tables below, determine which of the fields on the worksheet will go into which tables. Some items may appear in more than one table and may form a link between the two tables.

tblCustomer - information about customers

tblProduct - information about the product

tblOrder - information about orders

tblOrderDetail - Because relationships that you establish need to be one to many (or one to one), you will not be able to connect an order to many products without another table. This table is the Order Detail Table. This allows you to list several products ordered by the customer, the quantity, and indicate which order they are included in. Think of the Order table as the header portion of an order and the OrderDetail table as the line items on an order.

Fill in the remaining two columns for each of the fields.

Write a paragraph in which you support your reasoning for how you distributed the fields in your tables.

Once you have completed the assignment above, show the relationships among the tables. In order to do this, you will need to add the fields and draw the lines indicating the relationships. The Tables Worksheet will assist you in setting up the tables and their fields.