Question

Consider the relation concerning refrigerators Ref (Model#, Year, Price, Manuf_Plant, Color) and the following set of functional dependencies: Model# → Manuf_Plant Model#, Year → Price Manuf_Plant → Color

(i) Evaluate each of the following as a candidate key for Ref, giving reasons why it can or cannot be a candidate key: {Model#}, {Model#, Year}, {Model#, Color}.

(ii) Based on the result of (i) above, determine whether the relation Ref is in 3NF and in BCNF. You should justify your answers.

(iii) Consider the decomposition of Ref into R1 (Model#, Year, Price) R2 (Model#, Manuf_Plant, Color) Determine whether this is a lossless decomposition. You should justify your answers.

Answer 1

(1) candidate key of relation Ref={model # year} BCNF def": A relation is in BCNF Gf Bou every non-trivial FD X7A ,x is a superkey. . In the given relation in FD Manuf-plant color X is manuf plant which is not an attribute of ck. il, here x is not a sceperkey. So this relation is not in BCNE. 3NF defn: A relation is in 3NF if for every non- trivial FD X A, X is a superkey or A is part of some key for R(prime attribute). Here in the given relation, in FD , Marib-plant xcolor, neither manuf-plant us a superkey, nor color is part of candidate key so this relation is not in 3NF. Scan cs

Decomposition of Ref into RI (Model # year, price) R2 (model #, Manuf-plant, color) To check for lossless join decomposition using FD set, following conditions must hold: D ALE (RI) U AH (R2) = A(R) (U-union) 3) Attiri) 0 AH (R2) • Co- intersection) 3) Cormon attribute must be a (Alt = Attributes) Key for atleast one relation Ribu Rz) Here the given relation is a lossless decomposition since it satisfies these 3 conditions. 1 First-conditions hold true as AL CRI) U AL CR2) = (Model #,year, price u (model # Manuf-plant, color) = (Model # year, price, Manif-plant, color )= Att (R). 3 second condition holds true as AHLORIDO AH (R2) = (Model # year, price) (Model # Manuf-plant, color) to 3) Third condition holds true as Alt(Ri)n Att (R2) model which is a key of R2 (model #, Manis plant, color). CamScanner

Relation Ref (Model #, year, price , Manub-plant, colon) For care : Model # Manuf-plant Model # year price Manuf plant color A candidate key of a relation is a minimal Superkey for that relation. Superkey is defined as a set of attributes of a relation schema upon which all attributes of the scheman are functionally dependent. model #} is not a candidate key since it is not a superkey. smodel #, year} is a candidate key according to defen {model #, color} is not a candidate key since it o is not a superkey are every attributes of this relation not depend on this ck.

Relation Ref (Model #, year, price , Manub-plant, colon) For care : Model # Manuf-plant Model # year price Manuf plant color A candidate key of a relation is a minimal Superkey for that relation. Superkey is defined as a set of attributes of a relation schema upon which all attributes of the scheman are functionally dependent. model #} is not a candidate key since it is not a superkey. smodel #, year} is a candidate key according to defen {model #, color} is not a candidate key since it o is not a superkey are every attributes of this relation not depend on this ck.