+1 vote
163 views
Consider the relation $R(A,B,C,D,E)$ with $FD\{ A\rightarrow C, D\rightarrow CE\}$. Which of the following decomposition is in 3NF?

(A) $R_1(A,C); R_2(B,E); R_3(A,B,D)$
(B) $R_1(A,C); R_2(D,C,E); R_3(A,B,D)$
(D) None of these
| 163 views

+1 vote
$R(A,B,C,D,E)$ with $FD\{ A\rightarrow C, D\rightarrow CE\}$.where ABD is candidate key. is only in 1nf.

$R_1(A,C); R_2(D,C,E); R_3(A,B,D)$ is in BCNF but not lossless decompostion , but quetion ask only 3NF no matter lossless or lossy so this is answer
by Veteran (63k points)
+2

Some doubts:

1. Why option B is lossless decomposition?
$R_1 \cap R_3=A$ and $A$ is a superkey in $R_1$. Also $R_2 \cap R_3=D$ and $D$ is a superkey in $R_2$. Also, I guess I have read all decompositions 2NF, 3NF and BCNF are lossless and only BCNF decomposition can be not dependency preserving. Both 2NF and 3NF decompositions are dependency preserving.
2. Why option A is not in 3NF?
Is it because it looses dependency $D\rightarrow CE$?
0
Is ABD only candidate key? Or other candidate keys are possible?

Option B will be right option for it

Ck::ABD

PD=A->D,D->CE

So it is by default in 1NF

for converting it into 2NF we take closure of the A+ and D+

then we remove transitive dependency if any exist

then resulting table will be

R1(AC),R2(DCE) and R3(ABD) -->3NF

by Boss (10.2k points)