To decompose the give relation into 3nf and BCNF

Question

consider a relation R(a,b,c,d,e,f,g,h,,i,j)

following fd's hold-

AB->C

A->DE

B->F

F->GH

D->IJ

decompose the relation into 3nf and BCNF.

Answer 1

First of all find the keys. 

This is what I do for it, find all attributes not appearing on RHS of FDs. These attributes must be prime attributes-  must be in present in some candidate key. Now, find their minimal cover and add other attributes so that the minimal cover covers the entire attributes. 

Here, A and B are not on RHS, so must be in candidate key. AB determines {A,B,C,D,E,F,G,H,I,J} and hence becomes the candidate key as well. 

Now, A-> D, A->E B ->F are partial FDs. So, the relation is not in 2NF. 

To make it in 2NF, we decompose in to 3 relations

A->DEIJ giving ADEIJ, B->F and F->GH giving BFGH and AB -> C giving ABC.

These 3 relations are in 2NF. and ABC is in BCNF. In ADEIJ, we have transitive dependencies

A-> D and D -> IJ

and in BFGH we have transitive dependency B -> F and F-> GH

So, to make the table into 3NF, we make a separate relation for DIJ and FGH. So, now we get 5 relations

ABC, ADE, DIJ, BF and FGH which is in 3NF. None of the prime-attribute comes on right hand side of any FD => so as soon as the relation is in 3NF, it is in BCNF as well. Or you can see for each non-trivial FD α → β, whether α is a candidate key. (non-trivial means β ⊄ α)

Comments

condition for 3NF x ->y.either y=prime attribute or x= super key( we say every non prime attribute should non transitively be dependent on primary key(only)). for ADE a->d a->e neither d,e are prime nor a is primary key. plz explain.

---

In ADE, A is a candidate key. Once we decompose a relation, we have to find the key for the decomposed relation and consider FDs only in the decomposed relation.

Answer 2

Here is the solution ...

Youtube Link (https://www.youtube.com/watch?v=I3Zwz4lKmDU)