Lossless decomposition

Question

Relation: R(ABCDE):

FDs: A->BC, CD->E, B->D, E->A

This relation is not in BCNF bcz of FD B->D. So how it make BCNF by decomposition so that the resultant decomposition is both lossless and dependency preserving ? 

plzz explain ?

Comments

@ srestha

bcz A and E are superkey in its own 

 R(ABCDE).∩ R(EA)= AE 

for R(ABCDE) CK= A and for R(AE) CK= E

so AE belongs to which relation ? means it is superkey of which relation ?

---

u have R(EA) rt?

if (AE) is a candidate key, then we can say it is minimal super key too

---

@ srestha 

ya but AE is not a candidate key here, only A is  candidate keys for relation for R(ABCDE) and E is candidate key for relation R(AE)...

i think u also got confused.. :p :) so dnt be confused and thanks your valuable support.. :) 

Now i concluded one thing, the above relation holds only one property i.e lossless dependency and this dependency is achieved by decomposing it into three tables which are

R(ABC)

R(BD)

R(EA)

Answer 1

As we know if we decompose the relation R(ABCDE) into R1(ABCE) and R2(BD) is enough to make it BCNF and lossless.

For dependency preserving, we can add one more table in such a way that it won't disturb the current normalisation status and also preserve the dependency. i.e. R3(CDE)

therefore.... R1(ABCE) R2(BD) and R3(CDE)

plz any correction would be appreciated.

Comments

A--BC is a candidate key for whole relation i.e. R(ABCDE) but after decomposing it into R1(ABCE) so for this relation we further check or find candidate key which is E as this relation supports only two FD i.e.

A->BC, E->A

---

the idea of re-evaluating the candidate key after the decomposition is quite wrd to me. i have never solved the question in this way neither we have taught like this . ARe you sure about this methodology?? let me do some digging...

---

follow this: https://gateoverflow.in/33678/determining-the-highest-bnormal-form-the-table-decomposed

Answer 2

The table can be decomposed as follows:

R1:A,C,D,E with functional dependencies A>D,  A>C, CD>E

R2:A,B with functional dependency A>B

R3:B,D with functional dependency B>D

We can see that R1 join (R2 join R3) is lossless ,dependency preserving and BCNF.