BCNF-decomposition

Question

Consider the following relation:
                                                   R (ABCDEF)
                                                   F = {AB → C, BC → A, AC → B, B → D, C → E}
The minimum number of relations required which satisfy BCNF, lossless join decomposition and dependency preserving decomposition are

Answer 1

4 Tables i.e BD,CE, ABC and ABF  which satisfy Lossless Decomposition and FD preserving. 

BD and CE decomposed due to 2NF violation 

ABC and ABF decomposed from ABCF due to  BCNF violation.

Answer 2

Relation R(ABCDEF)...

Because closure of AB,BC,AC giving all elements of relation except F and G so Candidate keys are ABFG , BCFG, ACFG ...

 

AB->C , BC->A , AC->B giving all elements except F and G so it be in one relation ..

R1(A,B,C) because of candidate keys another relation R2(A,B,F,G) or R2(B,C,F,G) or R2(A,C,F,G) and because in B->D functional dependency B is not Candidate key so we have to seperate it out to R3(B,C) relation and R4(D,E) for same reason….

These 4 relations ensures Lossless Join ,Dependency preservation as well as BCNF ....

 

1. https://gateoverflow.in/176799/Bcnf-decompostion 

 

2. https://gateoverflow.in/60889/Made-easy 

 

3. https://gateoverflow.in/191120/Db-lossless-join-and-dependency-preserving-decomposition 

 

4. https://gateoverflow.in/60890/Madeeasy