Functional preserving

Question

Given $R(A,B,C,D,E)$ and $F:\left \{A\rightarrow BC ,CD\rightarrow E,B\rightarrow D,E\rightarrow A\right \}$,Decompose into $BCNF?$

$(a)$Every $BCNF$ is $3NF$ and vice versa $?$

$(b)FD$ preserving or not$?$

$(c)$ Lossless decomposition or lossy  decomposition?

Comments

I have the only problem, how to decompose the relation?

can you show, how to decompose?

Answer 1

We have to decompose it for the functional dependency that doesnot satisfy the BCNF condition.out of all the given functional dependencies the property of BCNF that the left hand side should be a super key is satisfied by all the functional dependencies but not by B implies D as B is not a super key. So in order to make it IN BCNF form we need to decompose it into 2 relations such that these  conditions are satisfied, so we break it up intp BD and ABCE. Here for BD relation B->D holds and B is a superkey so BCNF(every relation with two attributes is in BCNF) and similarly ABCE also satisfies the properties of BCNF but  all fds are not preserved.

Comments

Yeah, you solutions are right.

But I have doubt how to decompose the table?

I'm not getting,$R(ABCDE)$ to $R_{1}(BD)$ and $R_{2}(ABCE)$

can you please explain a bit more?

---

see out of all the given functional dependencies the property of BCNF that the left hand side should be a super key is satisfied by all the functional dependencies but not by B implies D as B is not a super key. So in order to make it IN BCNF form we need to decompose it into 2 relations such that these  conditions are satisfied, so we break it up intp BD and ABCE. Here for BD relation B->D holds and B is a superkey so BCNF(every relation with two attributes is in BCNF) and similarly ABCE also satisfies the properties of BCNF but  all fds are not preserved.

---

Thank you I understand.

---

@anjali007 what is problem in my decomposition??

---

@Prateek In your decomposition after you have decomposed the given relation into many relations I guess to conserve the functional dependencies... but we have only one functional dependency which is not following the constraint of BCNF and so we reduce the table in such a way that this particular fd is in another table.. the other ones are perfctly fine so we neednot decompose them further.. The dependency CD implies E is not preserved but BCNF may or mat not be dependency preserving so it is fine and we neednot construct another table for it as redundancy would increase.

---

@anjali007 ,redudancy is not there if we split like i did and but I think there is no problem to split in this way.

See this:

https://gateoverflow.in/102652/bcnf-decomposition

---

@prateek in that question it is asking for dependency preserving so I think that is why we are having 4 relations but there is no such constraint here.. we just have to decompose it into BCNF and tell whether it is dependency preserving.. Had the question been the link that you have mentioned we would need 4 tables.(as we have to conserve all fds)

---

@anjali both are same question ,in that question also asking for decomposition into bcnf with dependency preservation and lossless join.

---

@prateek exactly that question is asking but this one is not... please read this question again

---

@anjali I am not getting any difference ,can you tell me what is difference in both questions??

---

Let  me know  if I done wrong.

---

in my question I ask only decompose into BCNF, then find it's dependency preserving and lossless join or not?

but in https://gateoverflow.in/102652/bcnf-decomposition question explicitly asks for dependency preserving hence we have to add a redundant relation ECD it will be lossless because we can combine it with EA without spurious tuples

---

@prateek

after CD implies E you decomposed it again as this fd is not implied... BUT IN THIS PARTICULAR QUESTION THEY HAVENT ASKED US TO GIVE A DEPENDENCY PRESERVING DECOMPOSITION but in the link that u gve it was asked so your ans is absolutely right according to that...

Given R(A,B,C,D,E) and F:{A→BC,CD→E,B→D,E→A},Decompose into BCNF?

(a)Every BCNF is 3NF and vice versa ?

(b)FD preserving or not?

(c) Lossless decomposition or lossy  decomposition?

They are asking whether it is dependency preseving or not.

---

@Lakshman ya exactly so we decomposed it into BCNF and told that it is not fd preserving...

---

Yes, you are right.

both questions asked for something different.

---

@anjali @Lakshman ,got it thank you

---

they ask about BCNF decomposition in the question

and ask is it Dependency preserving or NOT ?

If you say, the BCNF decomposition is Dependency Preserving, then there exist atleast one such decomposition which will have Dependency Preserving.

For this R(A,B,C,D,E) and F:{A→BC,CD→E,B→D,E→A} , there exist a Decomposition which is in BCNF and Dependency Preserving.

 

∴ (b)FD preserving or not?  ===> is FD preserving !

---

@shaik so does that mean whenever we are decomposing it into BCNF we have to see that whether it can be decomposed into such a form such that dependency preserving decomposition exists for it?? I mean like in this question they didnt ask us to give a dependency preserving decomposition so do we need to do further after once we have got BCNF form to further get fd preserving?? pls clarify!

---

yes.. you should do that !

if you are saying it is not FD preserving, then there is no possibility to get a BCNF decomposition which will have FD preserving.

---

@Shaik Masthan

the answer is BCNF, lossless decomposition, not FD preserving.

---

it should be wrong.

if questioner gives decomposition, then you will say that decomposition is FD preserving or not ?

But in this question, questioner ask about a decomposition which is in BCNF, then i can give a decomposition which is in BCNF and Loss-less with FD preserving.

---

@Shaik Masthan

see this, please verify, I did right?

 

---

Check this decomposition

R₁(A,B,C,E) , R₂(B,D), and R₃(C,D,E)

it is FD preserving, right ?

---

Yes, it is, but your decomposition required more table, what is the problem in my decomposition?

---

but your decomposition required more table,

it is not matter here.

 

what is the problem in my decomposition?

there is no problem in your decomposition. if questioner gives decomposition, then you will say that decomposition is FD preserving or not ?

But in this question, questioner ask about a decomposition which is in BCNF, then i can give a decomposition which is in BCNF and Loss-less with FD preserving.

---

In this question, I asked, decompose int BCNF form then check it is FD preserving or not?

So.what is the answer to this question?

---

finally hoe many number of table needed here?

4??

A,CD,E super keys

So, 3 tables and BD has no superkeys , so 1 more table

right?

---

The decomposition which I gave, requires only 3 tables, right?

---

ma'am see my decomposition

---

how ABCE?

I havenot got this

---

$B\rightarrow D$

find $B^{+}=BD$

then i decompose $R(ABCD)=R_{1}(BD)$ and $R_{2}(ABCE)$

---

Mam, is there any problem with my decomposition?

---

Please tell me $B\rightarrow D$ is not in the form of $BCNF$, how to decompose the table?

my decomposition is right or not?

---

@Shaik

I was telling u

how u got ABCE table here?

why not decompose it furthur?

R1(A,B,C,E) , R2(B,D), and R3(C,D,E)

Can u explain the logic of ur decomposition? 

---

ma'am, my decomposition is right and FD is not preserved and it is lossless BCNF.

please check it

---

@Lakshman decomposition has some procedure to follow

So, plz check more question on this

chk this https://gateoverflow.in/79463/decomposition

---

Ok ma'am thank you