Case Study 5 - Pesendorfer

Case Study 5 - Pesendorfer Morgan Importing Project A. Follow the procedure shown in Figure 4-1 to assess these data. 1. List all functional dependencies.  ShipmentNumber  Shipper  ShipmentNumber  Phone  ShipmentNumber  Contact  ShipmentNumber  From  ShipmentNumber  Departure  ShipmentNumber  Arrival  ShipmentNumber  Contents  Shipper  Phone  Shipper  Contact  (Item, Date, City, Store)  Salesperson  (Item, Date, City, Store)  Price 2. List any multivalued dependencies.  City  Store  (City, Store)  Item  (City, Store)  Salesperson 3. List all candidate keys.  SHIPMENT table: ShipmentNumber  ITEM table: (Item, Date, City, Store) 4. List all primary keys.  SHIPMENT table: ShipmentNumber  ITEM table: (Item, Date, City, Store) 5. List all foreign keys.  SHIPMENT table: Item (referring to ITEM table)  ITEM table: No FKs 6. State any assumptions you make as you list these components.  There are only two tables  Each item has a unique description and price  Each shipment has a distinct source, date, and destination  Each item can appear in multiple shipments  The quantity of an item in a shipment can vary B. List questions you would ask Phillip to verify your assumptions.  Do multiple stores sell the same product?  Do some shippers have more than one phone?  Can stores in the same names within a country but within different cities?  Can cities in different countries have the same name?  Does is shipping store only have one contact person?  Are there items with identical descriptions but different prices