[Solved] CS6360.001- Quiz 4

Database Design

1. Consider the following relation:

CAR_SALE(Car#, Date_sold, Salesperson#, Commission%, Discount_amt)

Assume that a car may be sold by multiple salespeople, and hence {Car#, Salesperson#} is the primary key. Additional dependencies are

Date_sold Discount_amt and

Salesperson# Commission%

Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively normalize it completely?

2. Consider the following relation for published books:

BOOK (Book_title, Author_name, Book_type, List_price, Author_affil, Publisher)

Author_affil refers to the affiliation of author. Suppose the following dependencies exist:

Book_title Publisher, Book_type

Book_type List_price

Author_name Author_affil

1. What normal form is the relation in? Explain your answer.
2. Apply normalization until you cannot decompose the relations further.

3. Consider the relation REFRIG(Model#, Year, Price, Manuf_plant, Color),which is abbreviated as REFRIG(M, Y, P, M_P, C), and the following set F of functional dependencies: F = {M M_P, {M, Y} P, M_P C}

1. Evaluate each of the following as a candidate key for REFRIG, giving reasons why it can or cannot be a key: {M}, {M, Y}, {M, C}.

1. Based on the above key determination, state whether the relation REFRIG is in 3NF and in BCNF. Explain why. If relation is not already in 3NF, normalize it into 3NF.

4. Are F and G equivalent?

F = {A->C, AC->D, E->AD, E->H}

G = {A->CD, E->AH}

5. Consider the relation schema S (P, C, R, A) and below functional dependencies. Normalize S into 3NF by using minimal cover method.

P -> R, C, A

R, C -> A, P

A->C