The Gateway to Computer Science Excellence
+1 vote

in Databases by Active (2k points)
reshown by | 71 views

Edit:should be lossless but dependency not preserved (AB->C)

@Ashwin Kulkarni when you do C+={C,D,E,A}you will have two tables BC and ACDE

and after if EA,DE,CD so i think AB-C is getting lost???

A) Lossless but dependency preserved ?? ABC, CD, DE, EA are relations.
Yes, Answer Is A. But How?

Could please you describe briefly?
Here keys are AB, BC, BD, BE

hence relations C - > D, D->E, E->A forms partial dependencies.

Hence to remove Partial dependencies and convert to BCNF we need to make these as seperate relations so that LHS is superkey.

Hence ABC : AB->C (AB is superkey)

CD : C-> D(C is superkey)

DE : D->E (D is superkey)

EA : E->A (E is superkey)

hence all relations obey BCNF law. and dependencies are preserved.


Now to say it is lossless or not, Common attribute between two relations is should be a key on either ot both sides.

Relations ABC , CD (C is common attribute and a key in CD)

Relations CD, DE (D is common attribute and a key in DE)

Relations DE, EA (E is common attribute and a key in EA)


hence lossless decomposition


According to what I learned about DP, Same Ans I also got where I lost AB->C for DP( dependency preserving) So I have given answer Lossless only but not  Dependency preserve. 

But option A. was right given test series. 

Ashwin Kulkarni 

I think here partial dependency not form. this relation is already in 3NF. 

1 Answer

+1 vote

Here Candidate keys are (AB, BC, BD, BE) and As you can see Relation {A, B, C, D, E} is already in 3NF but NOT in BCNF because of  Functional dependencies { C -> D, D->E, E->A} So We need to take the closure 

                                                            (c)+ ={CDEA} 


We need to take the decomposition of the relation R(ABCDE) into R1(CDEA) and R2(BC)


                                                            R1(CDEA)                      R2(BC)

                                                             C -> D              {As you can see here A attribute not present So we are not taking AB->C}

                                                             D -> E

                                                             E -> A

                                                    Here C.K.= {C}

So further decomposition in (CD) and (DEA), but

Again you find (DEA) have problem So gain decomposition

relation (DEA) into (DE) and (EA)

Finally What we get 



                                     R1(CDEA)                                                                                     R2(BC)

               R11(CD)                         R12(DEA)

                                       R121(DE)                     R122(EA)

          {C=>D}                {D=>E}                            {E=>A}       

But Here you can see that {AB=>C} is not present that mean Dependency not preserving, But we can somehow manage to achieve Dependency preserving by Adding attribute  "A" ( this would not be always possible for every case, but here somehow possible) here Finally, we achieve {BCNF, LL, DP } 

by Active (2k points)
edited by
Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true
50,737 questions
57,388 answers
105,412 users