Question

Consider a relation R(A,B,C,D,E) with the following functional dependencies: 8. AB C BCD CDE DEA (a) Specify all candidate keys for R. (b) Which of the given functional dependencies are Boyce-Codd Normal Form (BCNF) violations'? (c) Give a decomposition of R into BCNF based on the given functional dependencies. (d) Give a different decomposition of R into BCNF based on the given functional dependencies. (e) Give a decomposition of R into 3NF based on the given functional dependencies.

Answer 1

(a) Here there are 2 candidate keys are AB and BC because {AB}⁺ = {A,B,C,D,E} using FDs AB -> C, BC -> D, CD -> E and DE -> A and  {BC}⁺ = {A,B,C,D,E} using FDs BC -> D, CD -> E , DE -> A and AB -> C.  

There is no other candidate key because every candidate key must contain attribute B since B cannot be obtained by other attributes than B.

(b) Here the FD CD -> E and DE -> A are violating BCNF because neither CD nor DE are candidate key( since every FD of the form X-> A should have X as candidate key in BCNF).

(c) Decomposing R based on FD CD -> E, will given R1(C, D, E) and R2( {A,B, C,D,E} - {C,D,E} + {C,D} ) = R2(A,B,C,D).

Hence the decomposition of R based on FD CD -> E will give  R1(C, D, E) and  R2(A,B,C,D).

(d) Decomposing R based on FD DE -> A, will given R1(A, D, E) and R2( {A,B, C,D,E} - {A,D,E} + {D,E} ) = R2( B,C,D, E).

Hence the decomposition of R based on FD DE -> A will give  R1(A, D, E) and R2( B,C,D, E).

(e) Here in relation R, the FD CD -> E is violating 3NF because neither CD is candidate key nor E is part of any key. Also FD DE -> A is violating 3NF because neither DE is candidate key nor A is part of any key.

Hence in decomposition of R based on FD  CD -> E will give R1(C, D, E) , based on FD DE -> A will give R2(A, D, E) and hence R3(B, D, E) will be in 3NF( DE is common attribute between R2 and R3).

Hence decomposition of R into  R1(C, D, E) , R2(A, D, E) and hence R3(B, D, E) will be in 3NF.

Please comment for any clarification.