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

i think it can be split using B as key (BD and ABCE) but dependency cannot be preserved

---

ya, m also getting the same remark, but after this

Check ABCE:

FDs: A->BC, E->A {We are not talking about D because D is not here}

Find Candidate Keys : {E}

The FD  A->BC, haven't superkey at left side so decompose at A

Find closure of A : {A,B,C}

Now ABC has become a relation having superkey as A ( A->BC )

ABC will be one side and other side EA (EA also have a superkey {E->A} )

So three relations: ABC, EA, BD

this decomposition is still lossless but not dependency preserving as CD->E is still not preserved here.

---

A is already a candidate key right?

why are u decomposing using A again?

---

in relation ABCE, we have only two FD i.e.  A->BC, and E->A 

according to this CK is E, thus It is not in BCNF bcz of A->BC  as A is not superkey, thats y i split it into {ABC, EA, BD}

---

do we check like that again?? am not sure

---

I think 4 tables required

otherwise dependency preservation will be violated

Only BCNF decomposition 2 table are enough

but here BCNF decomposition +dependency preserving+ lossless property

---

@ srestha 

by considering 4 tables, i.e.

R(ABC)

R(CDE)

R(BD)

R(EA)

 lossless property is violated as R(ABC) ∩ R(CDE)= C and C is not superkey in any of these two relation, hence violating the property of lossless dependency.

---

@ A_i_$_h

but i think so...

---

i don tink its possible

---

@ A_i_$_h

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

---

@ rishu_darkshadow

lossless not violated

As we can do R(ABC) and R(BD) decomposition

and merge in a relation R(ABCD)

Now do decomposition on R(ABCD) and R(CDE)

It will be  lossless rt?

---

@  srestha

ok i agree with you, so now R(ABCD) ∩ R(CDE)= CD and CD is a superkey of R(CDE) so it still lossless ok.. now according to you merge thses two and we get R(ABCDE).

Now check it with R(EA): R(ABCDE).∩ R(EA)= AE, which is not superkey of any these two..so now it is not lossless..

correct me if m wrong ...

---

May its not possible to create a BCNF with it being lossless as well as dependency preserving...are u sure its possible?

---

@ A_i_$_h 

m not also sure thats y m asking that question...

by splitting it into 4 tables i.e.

R(ABC)

R(CDE)

R(BD)

R(EA)

we are preserving dependency but still lossless property does not hold...and BCNF guarantee lossless property but in this case it is now BCNF but not lossless...

plzz make me out

---

why AE not a superkey?

---

@ 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.