Chap3 morgan importing p 98
Download
1 / 23

Chap3 Morgan Importing (p. 98) - PowerPoint PPT Presentation


  • 1570 Views
  • Uploaded on

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)

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Chap3 Morgan Importing (p. 98)' - sian


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

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


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.


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)


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


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


6. dependencies for the SHIPMENT relation for a small import–export business. State what you believe are reasonable multivalued dependencies for the SHIPMENT relation.

VendorName  VendorContact

VendorName  VendorFax


  • 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 set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.

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 ( set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.VendorName, VendorContact)

    • Candidate keys:

      (VendorName, VendorContact)

      • It is in BCNF

    • Multivalued dependency

      • None

    • The relation is both in 4NF and BCNF


    SHIPMENT_2 set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.(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


    • Candidate Keys? set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.

      • 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) set of tables in BCNF and 4NF. Indicate the primary keys, candidate keys, foreign keys, and referential integrity constraints.

    • Functional Dependencies:

      VendorName  VendorFax

      VendorFax  VendorName

    • Candidate Keys

      VendorName

      VendorFax

    • Is every determinant a candidate key?

    • YES, therefore the relation is in BCNF.


    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


    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 ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue, Insurer)

    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


    ad