Chap3 morgan importing p 98
This presentation is the property of its rightful owner.
Sponsored Links
1 / 23

Chap3 Morgan Importing (p. 98) PowerPoint PPT Presentation


  • 1261 Views
  • Uploaded on
  • Presentation posted in: General

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)

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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Chap3 morgan importing p 98

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)

  • 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.


A conti

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


Chap3 morgan importing p 98

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.


Store name city country owner specialty

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.


Chap3 morgan importing p 98

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)


Store name city country owner specialty1

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.


Store 2 name city country

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.


Store owner name city owner

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.


Store specialty name city specialty

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.


Final model

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)


Chap3 morgan importing p 98

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


Chap3 morgan importing p 98

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


Chap3 morgan importing p 98

6. State what you believe are reasonable multivalued dependencies for the SHIPMENT relation.

VendorName  VendorContact

VendorName  VendorFax


Chap3 morgan importing p 98

  • 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)


Examine multivalued dependencies

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


  • Vendor contact vendorname vendorcontact

    VENDOR_CONTACT (VendorName, VendorContact)

    • Candidate keys:

      (VendorName, VendorContact)

      • It is in BCNF

    • Multivalued dependency

      • None

    • The relation is both in 4NF and BCNF


    Chap3 morgan importing p 98

    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


    Chap3 morgan importing p 98

    • 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)


    Vendor fax number vendorname vendorfax

    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.


    Chap3 morgan importing p 98

    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


    Chap3 morgan importing p 98

    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.


    Final model1

    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


  • Login