1 / 10

Morgan Importing

Morgan Importing.

lucas-barnett
Download Presentation

Morgan Importing

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Morgan Importing Phillip Morgan makes periodic buying trips to various countries. During the trips, he keeps notes about the items he purchases and basic data about their shipments. He hired a college student as an intern, and she transformed his notes into the spreadsheets as shown in the following slide. This is just sample data. Phillip has purchased hundreds of items over the years, and they have been shipped in dozens of different shipments. Phillip wants to enter the information age, thus has decided to develop a database of his inventory. He wants to keep track of the items he has purchased, their shipments, and eventually customers and sales. To get started, he has asked you to create a database for the data in Figure 4-10.

  2. Fig. 4.10

  3. A • Follow the procedure shown in Figure 4-1 to assess this data. List multi-valued dependencies, functional dependencies, candidate keys, primary keys, and foreign keys. State your assumptions: SHIPMENT (ShipmentNumber, Shipper, Phone, Contact, From, Departure, Arrival, Contents, InsuredValue) ITEM (Item, Date, City, Store, Salesperson, Price)

  4. A1 – SHIPMENT table • Multivalued Dependencies • None • Functional Dependencies ShipmentNumber  Shipper ShipmentNumber  Phone ShipmentNumber  Contact ShipmentNumber  From ShipmentNumber  Departure ShipmentNumber  Arrival ShipmentNumber  Contents Shipper  Phone Shipper  Contact • Candidate Keys • ShipmentNumber • SHIPMENT ( • ShipmentNumber, Shipper, Phone, Contact, From, Departure, Arrival, Contents, InsuredValue • ) • ITEM ( • Item, Date, City, Store, Salesperson, Price • )

  5. ITEM (Item, Date, City, Store, Salesperson, Price) • Multivalued Dependencies: City  Store (City, Store)  Item (City, Store)  Salesperson • Functional Dependencies (Item, Date, City, Store) Salesperson (Item, Date, City, Store) Price • Candidate Keys: (Item, Date, City, Store)

  6. B. List questions you would ask Phillip to verify your assumptions. (1) Do you always use just one Contact at each Shipper? (2) Does each shipper have just one Phone? (3) Do you or can you buy from more than one store in each city in each country? (4) Can more than one store in the same country, but in a different city, have the same name? (5) Can you have the same city name in more than one country? (6) Can the same item be bought from different stores? (7) Can the same type of item (“brass lamps”) be bought from different stores? (8) Are all of the items purchased in one purchase (“brass lamps”) from a store shipped on the same shipment?

  7. C. Create tables as necessary to eliminate multivalued dependencies, if any. • In ITEM: • Assume that there are more than one item purchased from each store City  Store CITY_STORE (City, Store) (City, Store)  Item • Assume also that an item may be available for many stores: Item  (City, Store) ITEM (Item, Date, City, Store, Salesperson, Price)

  8. We need to create a separate ITEM table to track data on Items purchased for import. CITY_STORE_ITEM(Item, City, Store) (City, Store)  Salesperson CITY_STORE_SALESPERSON (City, Store, Salesperson) ITEM (Item, Date, City, Store, Salesperson, Price)

  9. F. Assume that Phillip wishes to create an updateable database from this data. Design tables you think are appropriate. State all referential integrity constraints. • SHIPMENT.Contents has been replaced with the SHIPMENT_ITME table ITEM (Item, {additional columns – Description, Weight, etc.}) CITY_STORE (City, Store, {additional columns – Country, Address, etc.}) CITY_STORE_SALESPERSON (City, Store, Salesperson) WHERE CITY_STORE_SALESPERSON.(City, Store) must exist in CITY_STORE.(City, Store) CITY_STORE_ITEM (Item, City, Store) WHERE CITY_STORE_ITEM.(City, Store) must exist in CITY_STORE.(City, Store), AND WHERE CITY_STORE_ITEM.Item must exist in ITEM.Item

  10. ITEM_PURCHASE (Item, Date, City, Store, Salesperson, Price) WHERE ITEM_PURCHASE.(Item, City, Store) must exist in CITY_STORE_ITEM.(Item, City, Store) SHIPPER (Shipper, Phone, Contact) SHIPMENT (ShipmentNumber, Shipper, From, Departure, Arrival, Contents, InsuredValue) WHERE SHIPMENT.Shipper must exist in SHIPPER.Shipper SHIPMENT_ITEM (ShipmentNumber, Item, Date, City, Store) WHERE SHIPMENT_ITEM.ShipmentNumber must exist in SHIPMENT.ShipmentNumber, AND WHERE SHIPMENT_ITEM.(Item, Date, City, Store) must exist in ITEM_PURCHASE. (Item, Date, City, Store)

More Related