Database Storage & Design Chapter 3 Homework The Second Multi dependency Requires Row Data For

Unlock access to all the studying documents.

View Full Document

Chapter 3 – The Relational Model and Normalization

Page 3-33

E. Given your assumptions, comment on the appropriateness of the following designs:

1. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

2. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

3. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

4. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor, Phone)

5. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate)

and:

VENDOR (Vendor, Phone)

purchases of a particular item to one per day.

6. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)

and:

VENDOR (Vendor, Phone)

7. PURCHASE (PurchaseItem, PurchasePrice, PurchaseDate, Vendor)

and:

VENDOR (Vendor, Phone)

Chapter 3 – The Relational Model and Normalization

Page 3-34

F. Modify what you consider to be the best design in part E to include surrogate ID columns

called PurchaseID and VendorID. How does this improve the design?

The best design in part E was number 7, so we’ll put in the ID columns. These columns

will become the new primary keys, and we’ll need to adjust the foreign key in

PURCHASE. The result is:

G. The relations in your design from part D and part F are not connected. Modify the

database design so that sales data and purchase data are related.

The connection between the two parts of the database design is the item being first purchased and

then sold. Thus, we can create an integrated design by replacing InvoiceItem in SALE_ITEM

with PurchaseID as a foreign key. We will rename Price in SALE_ITEM as SalePrice. Our final

design will be:

Chapter 3 – The Relational Model and Normalization

❖ ANSWERS TO MORGAN IMPORTING PROJECT QUESTIONS

Morgan keeps a table of data about the stores from which he purchases. The stores are

located in different countries and have different specialties.

A. Consider the following relation:

STORE (StoreName, City, Country, OwnerName, Specialty)

Explain the conditions under which the following are true:

2. City → StoreName

TRUE when there is only one store in each city.

4. (StoreName, Country) → (City, OwnerName)

5. (City, Specialty) → StoreName

6. OwnerName →→ StoreName

7. StoreName →→ Specialty

TRUE when store names are unique and at least one store has more than one specialty (i.e., stores

Chapter 3 – The Relational Model and Normalization

Page 3-36

B. With regard to the relation in part A:

1. Specify which of the dependencies in part A seems most appropriate for a small

import–export business.

NOTE: VARIABLE ANSWERS – Your students may make different assumptions.

StoreName → City NO, store names may repeat in different

cities within one country, and between

countries.

OwnerName →→ StoreName NO. Although one owner may own more than

one store, stores are identified as

(StoreName, City) since City values are

unique.

OwnerName →→ (StoreName, City)

would be OK, and would mean that one

owner may own more than one store.

2. Given your assumptions in B.1, 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.

NOTE: VARIABLE ANSWER – If your students made different assumptions in B.1,

their answers to this question will vary. You may want to specify the assumptions you

Chapter 3 – The Relational Model and Normalization

Page 3-37

The second multidependency requires a row of data for each specialty, leading to

redundant data. To simplify the other steps, we’ll break these out into their own

tables now:

STORE_2 (StoreName, City, Country, OwnerName)

The foreign key will be OwnerName in STORE_2 and (StoreName, Country) in

STORE_SPECIALTY. We still have normalization to BCNF to check, so we’ll add the

referential integrity constraints for these relations at the end of the process when

we have all the normalized relations.

STEP TWO: LIST THE FUNCTIONAL DEPENDENCIES:

Is every determinant a candidate key?

Yes, therefore the relation is in BCNF.

Chapter 3 – The Relational Model and Normalization

Page 3-38

STEP FIVE: CHECK THE TABLES HOLDING MULTIVALUED DEPENDENCIES

Are the fields of the multivalued dependency the only fields in this

table?

Are the fields of the multivalued dependency the only fields in this

table?

STEP SIX: STATE FINAL MODEL SPECIFICATIONS:

Primary Keys are underlined.

Foreign Keys are italicized.

Non-Primary Key Candidate Keys (Alternate Primary Keys) are stated following

each relation as Alternate Keys [NOTE: None exist].

Referential Integrity Constraints are stated following each relation.

It may seem silly to store a table with just the OwnerName, but in an actual database there

may be additional Owner information, such as owneraddress that would be included in the

STORE_OWNER table. We need to cascade updates from STORE_OWNER.OwnerName to

STORE_2.OwnerName.

Chapter 3 – The Relational Model and Normalization

Page 3-39

C. Consider the relation:

SHIPMENT (ShipmentNumber, ShipperName, ShipperContact, ShipperFax,

DepartureDate, ArrivalDate, CountryOfOrigin, Destination, ShipmentCost,

InsuranceValue, Insurer)

1. Write a functional dependency that expresses the fact that the cost of a shipment

between two cities is always the same.

2. Write a functional dependency that expresses the fact that the insurance value is

always the same for a given shipper.

3. Write a functional dependency that expresses the fact that the insurance value is

always the same for a given shipper and country of origin.

4. Describe two possible multivalued dependencies in SHIPMENT.

5. State what you believe are reasonable functional dependencies for the

SHIPMENT relation for a small import–export business.

NOTE: VARIABLE ANSWER – Your students may make different assumptions, and

the answer to this question will vary.

ShipmentNumber → ShipperName

Chapter 3 – The Relational Model and Normalization

Page 3-40

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

SHIPMENT relation.

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

NOTE: VARIABLE ANSWER – If your students made different assumptions in F.5 and

F.6, their answers to this question will vary. You may want to specify the assumptions

you want your students to use so that you are evaluating their work against one specific

answer.

STEP ONE: LIST THE MULTIVALUED DEPENDENCIES:

ShipperName →→ ShipperContact

To simplify the other steps, we’ll break this out into its own table now:

Chapter 3 – The Relational Model and Normalization

Page 3-41

STEP TWO: LIST THE FUNCTIONAL DEPENDENCIES:

SHIPMENT_2 (ShipmentNumber, ShipperName, ShipperFax, DepartureDate,

ArrivalDate, CountryOfOrigin, Destination, ShipmentCost, InsuranceValue,

Insurer)

ShipmentNumber → ShipmentCost

ShipmentNumber → InsuranceValue

ShipmentNumber → Insurer

STEP THREE: LIST THE INITIAL CANDIDATE KEYS

ShipmentNumber

Is every determinant a candidate key?

NO, ShipperName is NOT a candidate key.

Therefore the relation is NOT in BCNF.

Chapter 3 – The Relational Model and Normalization

Page 3-42

STEP FOUR: FIRST INTERATION:

SHIPMENT_3 (ShipmentNumber, ShipperName, DepartureDate, ArrivalDate,

STEP FOUR (A): CHECK SHIPMENT_3

STEP FOUR (A)(1) – LIST THE FUNCTIONAL DEPENDENCIES

SHIPMENT_3 (ShipmentNumber, ShipperName, DepartureDate,

ArrivalDate, CountryOfOrigin, Destination, ShipmentCost,

InsuranceValue, Insurer)

STEP FOUR (A)(2) – LIST THE CANDIDATE KEYS

ShipmentNumber

Is every determinant a candidate key?

Chapter 3 – The Relational Model and Normalization

Page 3-43

STEP FOUR (B): CHECK VENDOR_FAX_NUMBER

STEP FOUR (B)(1) – LIST THE FUNCTIONAL DEPENDENCIES

SHIPPER_FAX_NUMBER (ShipperName, ShipperFax)

Is every determinant a candidate key?

YES, therefore the relation is in BCNF.

STEP FIVE: CHECK THE TABLES HOLDING MULTIVALUED DEPENDENCIES

STEP FIVE (A): CHECK SHIPPER_CONTACT

Are the fields of the multivalued dependency the only fields in this

table?

ALL TABLES ARE NOW IN BCNF AND 4NF!

Chapter 3 – The Relational Model and Normalization

Page 3-44

STEP SIX: STATE FINAL MODEL SPECIFICATIONS:

Primary Keys are underlined.

Foreign Keys are italicized.

Non-Primary Key Candidate Keys (Alternate Primary Keys) are stated following

each relation as Alternate Keys [NOTE: None exist].

Referential Integrity Constraints are stated following each relation.