R(A,B,C,D) is a relation. Which of the following does not have a lossless join, dependency preserving BCNF decomposition?
(A) A->B, B->CD
(B) A->B, B->C, C->D
(C) AB->C, C->AD
(D) A ->BCD
Explanation: Background :
Decomposition of R into R1 and R2 is a lossless-join decomposition if at least one of the following functional dependencies are in F+ (Closure of functional dependencies)
R1 ∩ R2 → R1 OR R1 ∩ R2 → R2
- dependency preserving :
Decomposition of R into R1 and R2 is a dependency preserving decomposition if closure of functional dependencies after decomposition is same as closure of of FDs before decomposition.
A simple way is to just check whether we can derive all the original FDs from the FDs present after decomposition.
We know that for lossless decomposition common attribute should be candidate key in one of the relation.
A) A->B, B->CD
R1(AB) and R2(BCD)
B is the key of second and hence decomposition is lossless.
B) A->B, B->C, C->D
R1(AB) , R2(BC), R3(CD)
B is the key of second and C is the key of third, hence lossless.
C) AB->C, C->AD
C is key of second, but C->A violates BCNF condition in ABC as C is not a key. We cannot decompose ABC further as AB->C dependency would be lost.
D) A ->BCD
Already in BCNF.
Therefore, Option C AB->C, C->AD is the answer.
Quiz of this Question