Consider the relation REFRIG(Model#, Year, Price, Manuf_plant, Color), which is
ID: 3636483 • Letter: C
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 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)}
1. Is this decomposition lossless? Show why. And
2. Is this decomposition has the dependency preservation property?
Explanation / Answer
Answers:
(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)
R={M,Y,MP,C}
R1={M.Y,P}
R2={M,MP,C}
F={M,MP, {M,Y},P,MP,C},
F+ = { {M}+ {M,MP,C},{M,Y}+ {M,Y,MP,C},
{MP}+ {M[,C}}
R1 intersection R2 =M
R2- R1 ={MP,C}
D(R1,R2)has the lossless join property since Property :LJ1:FD((R1 intersection R2) (R2-R1)) is in F+ is satisfied (due to M {MP,C} ).