Questions

James Morgan owns and operates Morgan Importing, which purchases antiques and home furnishings in Asia, ships those items to a warehouse facility in Los Angeles, and then sells these items in the United States. James tracks the Asian purchases and subsequent shipments of these items to Los Angeles by using a database to keep a list of items purchased, shipments of the purchased items, and the items in each shipment.

Now, consider one of the relations in Morgans database:

SHIPMENT_ITEM (ShipmentNumber, ItemID, ItemDescription, ItemValue, VendorID, VendorName, VendorContact, VendorFax, DepartureDate, ArrivalDate, Origin, Destination, ShipmentCost, ItemQuantity)

Questions

1. Write a functional dependency that expresses the fact that VendorID determines a vendors name, contact information and fax number.

2. Write a functional dependency that expresses the fact that ItemID determines an items description, its value, as well as the ID, name, contact, and fax number of this items vendor.

3. Write a functional dependency that expresses the fact that ShipmentNumber determines the DepartureDate, ArrivalDate, Origin, Destination, and ShipmentCost of a shipment.

4. Based only on the functional dependencies in step1~3 and the functional dependency implied by the primary key of the original SHIPMENT_ITEM relation, transform the relation SHIPMENT_ITEM into a set of tables in BCNF. Indicate the primary keys, foreign keys, and referential integrity constraints of these tables in the standard format. Note that you just need to report your final normalization results but not the intermediary steps.

5. Write a SQL statement or statements that will create the table or tables you designed in step 4. You can make assumptions about data types and various constraints as needed. You can also add surrogate keys if needed. But you SQL statements should be consistent with your results in step 4 (columns, primary keys, foreign keys, referential integrity constraints, etc.).