No solutions provided.

Question

Consider the relation REFRIG(MODEL#, YEAR, PRICE, MANUF_PLANT, COLOR), which is abbreviated as REFRIG(M, Y, P, MP, C), and the following set of F of functional dependencies: F={M -> MP, {M,Y} -> P, MP -> C}

(a) 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}

(b) Based on the above key determination, state whether the relation REFRIG is in 3NF and in BCNF, giving proper reasons.

(c) Consider the decomposition of REFRIG into D={R1(M,Y,P), R2(M,MP,C)}. Is this decomposition lossless? Show why. (You may consult the test under Property LJ1 in Section 15.2.4)

Answer 1

(a)

- {M} IS NOT a candidate key since it does not functionally determine attributes Y or P.

- {M, Y} IS a candidate key since it functionally determines the remaining attributes P, MP,

and C.

i.e.

{M, Y} P, But M MP

By augmentation {M, Y} MP

Since MP C, by transitivity M MP, MP C, gives M C

By augmentation {M, Y} C

Thus {M, Y} P, MP, C and {M, Y} can be a candidiate key

- {M, C} IS NOT a candidate key since it does not functionally determine attributes Y or P.



(b)

REFRIG is not in 2NF, due to the partial dependency {M, Y} MP (since {M} MP

holds). Therefore REFRIG is neither in 3NF nor in BCNF.

Alternatively: BCNF can be directly tested by using all of the given dependencies and

finding out if the left hand side of each is a superkey (or if the right hand side is a prime

attribute). In the two fields in REFRIG: M MP and MP C. Since neither M nor MP

is a superkey, we can conclude that REFRIG is is neither in 3NF nor in BCNF.



(c)