How to do BCNF decompostion?

A relation R(A,B.C,D)

and functional dependency

AB-> C

C-AD

H ow to decompose into BCNF ?

4Comments
GAURAV PARASHAR @parashar25
15 Sep 2017 04:31 pm

1)Assuming no attribute has multiple values, the relation is in 1NF.

2) here the candidate key is AB (as B is not in the right side in any FD, and B comes with A from FD-1. So, candidate key is AB. D is derived transitively from C). Since C is fully functional dependent on AB , and D is transitively dependent on C. So this is in 2NF.

3) since, we can derive D and A from C , which is not a candidate key, this is not in 3NF.

To make it in 3NF, we make 2 relations R¹(A,B,C) having FDs {AB->C} and R² (C,D) having FDs { C->D}. Now, it is in 3NF.

4) Now, BCNF says that , right side of all FD should be a super key.For R¹ AB is the candidate key and hence, it is also a super key. C is dependent on AB. So R¹ is in BCNF.

For R²,  C->D, i.e., D is dependent on C, so C is made a foreign key in R², so this relation is also in BCNF.

 

Hence, breaking R in R¹ (A,B,C) and R²(C,D) makes both the relations in BCNF.

 

kanika arora @kanikaarora
15 Sep 2017 11:30 pm

we eliminated A from relation R2 because it is in first relation is this the only reason ?

GAURAV PARASHAR @parashar25
16 Sep 2017 12:44 am

Yes. Since AB is the candidate key, A belongs to prime attribute. So any non-prime attribute functionally detrimining a prime attribute makes no sense. So we can eliminate that..also if you look closely, the dependencies are preserved.

kanika arora @kanikaarora
16 Sep 2017 11:41 pm

thankyou for the answer