chap3 morgan importing p 98
Download
Skip this Video
Download Presentation
Chap3 Morgan Importing (p. 98)

Loading in 2 Seconds...

play fullscreen
1 / 23

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


  • 1686 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
slide2
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).
slide4
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.
slide6
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)
slide12
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
slide13
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

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

VendorName  VendorContact

VendorName  VendorFax

slide15
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
slide18

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
slide19
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.
slide21
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
slide22
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

ad