1 / 23

Chap3 Morgan Importing (p. 98)

Chap3 Morgan Importing (p. 98). A. Morgan keeps a table of data about the stores from which he purchases. The stores are located in different countries and have different specialties. Consider the following relation: STORE (Name, City, Country, Owner, Specialty)

sian
Download Presentation

Chap3 Morgan Importing (p. 98)

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. Chap3 Morgan Importing(p. 98)

  2. A. • Morgan keeps a table of data about the stores from which he purchases. The stores are located in different countries and have different specialties. Consider the following relation: STORE (Name, City, Country, Owner, Specialty) • Explain the conditions under which the following are true: 1. Name  City • TRUE when store names are unique and in one specific city. 2. City  Name • TRUE when there is only one store in each city. 3. City  Country • TRUE when city names are unique. 4. (Name, Country)  (City, Owner) • TRUE when store names are unique in each country, but may repeat in different countries.

  3. A – Conti. STORE (Name, City, Country, Owner, Specialty) • Explain the conditions under which the following are true: 5.(City, Specialty)  Name • TRUEwhen there may be more than one store in each city, but there is only one store with that specialty in each city. 6. Owner  Name • TRUEwhen owner names are unique, store names are unique, and at least one owner owns more than one store (i.e., owners may own more that one store). 7. Name  Specialty • TRUE when store names are unique and at least one store has more than one specialty (i.e., stores may have more than one specialty).

  4. B1. Specify which of the dependencies in part A seem most appropriate for a small import–export business. STORE (Name, City, Country, Owner, Specialty) • Name  City • NO, names may repeat in different cities within one country, and between countries. • City  Name • NO, There may be more than one store in each city. • City  Country • YES & NO. Although city names in general are not unique, for a small import-export business they probably will be. • (Name, Country)  (City, Owner) • NO, names may be repeated in different cities within a country.

  5. STORE (Name, City, Country, Owner, Specialty) • (City, Specialty)  Name • NO. It is possible that more than one store in a city supplies the same specialty. In fact, since specialties may be associated with countries, it is likely that stores in the same city will have the same specialty. • Owner  Name • NO, although one owner may own more than one store, stores are identified as (Name, City) since City values are unique. Owner  (Name, City) would be OK, and would mean that one owner may own more than one store. • Name  Specialty • NO, not if names are not unique. (Name, City)  Specialty would be OK, and would mean that one store in a particular city may have more than one specialty.

  6. B2. • Given your assumptions in B1, transform the STORE table into a set of tables that are in both 4NF and BCNF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints. STORE (Name, City, Country, Owner, Specialty)

  7. STORE (Name, City, Country, Owner, Specialty) • Step 1: Identify Multivalued Dependencies: • Owner  (Name, City) • (Name, City)  Specialty • Create own tables: STORE_2 (Name, City, Country) STORE_OWNER (Name, City,Owner) STORE_SPECIALTY (Name, City, Specialty) • We will worry about Referential Integrity until we have converted these to BCNF.

  8. STORE_2 (Name, City, Country) • Step 2: Identify Functional Dependencies: (Name, City, Country)  • Step 3: Identify Candidate Keys (Name, City, Country) • Is every determinant a candidate key? • Yes, the relation is in BCNF. • Step 4: Iteration • Unnecessary since we already have it in BCNF.

  9. STORE_OWNER (Name, City,Owner) • Step 5: Remove Multivalued Dependencies: • None • Are the fields of the multivalued dependency the only fields in this table? • YES, STORE_OWNER is in 4NF.

  10. STORE_SPECIALTY (Name, City, Specialty) • Multivalued Dependencies: • None • Are the fields of the multivalued dependency the only fields in this table? • YES, STORE_SPECIALTY is in 4NF.

  11. Final Model • STORE_2 (Name, City, Country) • STORE_OWNER (Name, City, Owner) • WHERE (STORE_OWNER.Name, STORE_OWNER.City) must exist in (STORE_2.Name, STORE_2.City) • STORE_SPECIALTY (Name, City, Specialty) • WHERE (STORE_SPECIALTY.Name, STORE_SPECIALTY.City) must exist in (STORE_2.Name, STORE_2.City)

  12. C. Consider the relation: SHIPMENT (ShipmentNumber, VendorName, VendorContact, VendorFax, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer) • Write a functional dependency that expresses the fact that the cost of a shipment between two Countries is always the same. • (CountryOfOrigin, Destination)  ShipmentCost • Write a functional dependency that expresses the fact that the insurance value is always the same for a given vendor. • VendorName  InsuranceValue • Write a functional dependency that expresses the fact the insurance value is always the same for a given vendor and country of origin. • (VendorName, CountryOfOrigin)  InsuranceValue • Describe two possible multivalued dependencies in SHIPMENT. • VendorName  VendorContact • VendorName  VendorFax

  13. 5. State what you believe are reasonable functional dependencies for the SHIPMENT relation for a small import–export business. ShipmentNumber  VendorName ShipmentNumber  VendorFax ShipmentNumber  DepartureDate ShipmentNumber  ArrivalDate ShipmentNumber  CountryOfOrigin ShipmentNumber  Destination ShipmentNumber  ShipmentCost ShipmentNumber  InsuranceValue ShipmentNumber  Insurer VendorName   VendorFax

  14. 6. State what you believe are reasonable multivalued dependencies for the SHIPMENT relation. VendorName  VendorContact VendorName  VendorFax

  15. Using your assumptions in 5 and 6, transform SHIPMENT into a set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints. SHIPMENT (ShipmentNumber, VendorName, VendorContact, VendorFax, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer)

  16. Examine multivalued dependencies VendorName  VendorContact • Break this out into its own table now: VENDOR_CONTACT (VendorName, VendorContact) SHIPMENT_2 (ShipmentNumber, VendorName, VendorFax, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer) • Now, let’s examine both relations for BCNF & 4NF

  17. VENDOR_CONTACT (VendorName, VendorContact) • Candidate keys: (VendorName, VendorContact) • It is in BCNF • Multivalued dependency • None • The relation is both in 4NF and BCNF

  18. SHIPMENT_2 (ShipmentNumber, VendorName, VendorFax, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer) • Functional Dependencies: • ShipmentNumber  VendorName • ShipmentNumber  VendorFax • ShipmentNumber  DepartureDate • ShipmentNumber  ArrivalDate • ShipmentNumber  CountryOfOrigin • ShipmentNumber  Destination • ShipmentNumber  ShipmentCost • ShipmentNumber  InsuranceValue • ShipmentNumber  Insurer • VendorName  VendorFax • VendorFax  VendorName

  19. Candidate Keys? • ShipmentNumber • VendorName is NOT a candidate key. • Therefore the relation is NOT in BCNF. VENDOR_FAX_NUMBER (VendorName, VendorFax) SHIPMENT_3 (ShipmentNumber, VendorName, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer)

  20. VENDOR_FAX_NUMBER (VendorName, VendorFax) • Functional Dependencies: VendorName  VendorFax VendorFax  VendorName • Candidate Keys VendorName VendorFax • Is every determinant a candidate key? • YES, therefore the relation is in BCNF.

  21. SHIPMENT_3 (ShipmentNumber, VendorName, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer) • The Functional Dependencies • ShipmentNumber  VendorName • ShipmentNumber  DepartureDate • ShipmentNumber  ArrivalDate • ShipmentNumber  CountryOfOrigin • ShipmentNumber  Destination • ShipmentNumber  ShipmentCost • ShipmentNumber  InsuranceValue • ShipmentNumber  Insurer

  22. SHIPMENT_3 (ShipmentNumber, VendorName, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer) • Candidate Keys • ShipmentNumber • Is every determinant a candidate key? • YES, the relation is in BCNF.

  23. Final model SHIPMENT_3 (ShipmentNumber, VendorName, DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer) VENDOR_FAX_NUMBER (VendorName, VendorFax) WHERE VENDOR_FAX.VendorName must exist in SHIPMENT_3.VendorName VENDOR_CONTACT (VendorName, VendorContact) WHERE VENDOR_CONTACT.VendorName must exist in SHIPMENT_3.VendorName

More Related