Reference no: EM132493608

Problem: 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 Morgan's database:

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

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

2. Write functional dependency that expresses the fact that ItemID determines an item's description, its value, and everything about its vendor.

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

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

5. Write 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.).