doubt in databases

Question

Answer 1

Given the functional dependencies applicable on the table, AB, BD and BE are the candidate keys of this relation.

By default the relation is in 1NF.

Also the relation R is in 2NF as there are no partial FDs.

Also the relation is in 3NF since there are no transitive functional dependencies.

But the relation is not in BCNF due to the functional dependencies E->A and D->E since the left hand side of these FDs are not superkeys.

(D)+ ={D,E,A}

(E)+= {E, A}

So the table R(ABCDE) is decomposed into R1(BCD),R2(DE) and R3(EA) and the functional dependencies applicable on these tables are:

On R1: BD->C

On R2: D->E

On R3: E->A

As left hand side of all these dependencies are superkeys of the respective tables that they are applicable on, so the resulting tables are in BCNF.

hence 3 tables are needed.

Option (b)  is the correct answer.

Comments

@akshayak You can't use only E as the key.. AE together has to be used as the key. Then in that case, this decomposition is also lossless.

---

@Somoshree

if u getting 3 tables not less than that

why r u not taking ABCD as another table?

Because there is only 3 dependencies as per relation

---------------------------------------------------------------

Moreover why key is needed to create table?

$E\rightarrow A$

$D\rightarrow E$

all r superkeys?

---

plz check I have given answer, If anything wrong

Answer 2

For the given functional dependency AB->C, AB->D, D->E, E->A

Candidate keys are {AB, BE, BD}

highest normal forms of FD's are :

AB->C BCNF

AB->D BCNF

D->E 3NF

E->A 3NF

so we decompose them into R1(BCD) and R2(ADE)

R1(BCD): Candidate key {BD}

BD->C .........here AB->C can be replaced by BD->C as D->E and E->A so D->A transitively--------BCNF

BD->D...........no need as it is trivial FD

R2(ADE): Candidate key {D}

D->E----BCNF

E->A-----2NF

again decomposition of R2(ADE) into R21(AE) and R22(DE)

R21(AE): Candidate key {E}

E->A-----BCNF

R2(DE): Candidate key {D}

D->E----BCNF

so the relation R(ABCDE) is decomposed into R1(BCD)  R21(AE)   R22(DE)

therefore option B is correct with 3 tables

Answer 3

It can be decompose into $3$ tables

**First need to remember BCNF preserves lossless decomposition but not preserves dependency **

Now, check here candidate keys are AB,BD,BE

Now, $E\rightarrow A$

          $D\rightarrow E$

are odd dependencies.

So, we can create table for those.

So, tables are $\left ( A,E \right ),\left ( D,E \right ),\left ( B,C \right )$

But it is not lossless.

To became lossless tables need to be $\left ( A,B,E \right ),\left ( B,D,E \right ),\left ( A,B,C \right )$

https://gateoverflow.in/79463/decomposition

 

 

Comments

it's our wish to add it or not?

But without adding it, we can't get dependency preserving.

Without adding it, we can't get Loss-less property of decomposition.

( Note that more than one way of decomposition, have loss-less and Dependency Preservation, May you go some other way )

 

 

But coming to your Decomposition

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

 As per you, it is loss-less.... ok i agree, it is loss-less.

But what about it's NF ?

in the table R1(A,B,E) ===> AB,BE are the keys

AB --> E  ====> left side is Key ===> No problem with this Dependency

E ---> A ====> left side is not key ===> problem with this Dependency ==> Not in BCNF but in 3NF

---

See I got a good link :)
https://www.cs.colostate.edu/~cs430dl/yr2016sp/more_examples/Ch8/Decomposition%20into%20BCNF.pdf
Here it is saying , if there is a dependency, we  can even remove it
See both examples, specially 2nd one
So, that last table does not hold dependency preservation

---

i checked the link.

R (A,B,C,D) and FD's ={ AB --> C, C-->A, B ---> D }

 

they decompose it as R1(B,D) , R2 (C,A) and R3(B,C) ----> it is lossless but it is not Dependency Preserving ( they also said that only. )

Answer 4

We get CK = {AB,BD,BE} so, Prime Attributes(PA)= A,B,D,E

now if we check the given fd’s E→ A, D→ E are in 3NF but not in BCNF. now we decompose them into BCNF.

Decomposition:

D+ = DEA, E+ = EA . 1^st decompose into DEA , then E→ A, D→ E goes to this table  and CK = {D} 

but E→ A is not in 3NF. so we again decompose DEA into DE  and EA .

if we remove EA  from the given R(ABCDE) , we get BCD which will be the another relation.

So, we get total 3 relations(DE, EA and BCD)

Checking lossless : D is common and is a key from DE, EA. it forms DEA. 

D is common and is a key from DEA, BCD

Checking FD preserving:  AB→ CD can be preserved directly or indirectly from these 3 relations. 

Answer is 3 BCNF Relations which is lossless but not fd preserving.