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

how u got $ BD->C$

moreover is it in 3NF

$D->E$ is a nonprime to nonprime dependency

---

BE is a key so E is a prime attribute..

---

Shouldnt R1 be ABCD ?

---

@srestha ma'am,  (BD)+= {B, D, E, A, C} so BD->C is applicable on the relation R and hence is also applicable on the table R1(BCD).

---

@s9k96, how are u getting R1 as ABCD? if R1(ABCD), R2(DE) and R3(EA) are the tables after decomposition ,then it wouldn't be a lossless decomposition..it would be lossy.

---

BD->C

where r u  getting this?

no dependency like this in question

---

@srestha ma'am, there isn't any dependency like this in the question. But when we decompose a table, we need to find out what are the dependencies that are applicable on these new tables that have been formed due to decomposition so as to ensure that all the dependencies that are applicable on that table have their left hand side as a  superkey of the corresponding tables that they are applicable in if the table is in BCNF. So here when we find out the closure of BD, we see that it can determine C also as C is present in its closure. So this functional dependency is an additional functional dependency that is applicable on the table BCD. As BD can determine C, so BD forms the superkey of table BCD and hence the table BCD is in BCNF.

---

Hey I got 4 tables

R1(ABCD)  which is already in a bcnf form ( AB --> CD)

R2 (EA)  

R3(BE)

R4(DE)

---

@Magma,  Why do u need 4 tables? 3 tables are itself sufficient for it to be in BCNF.

---

Shouldn't we keep the original keys so that if we want to join tables we can do it later?

Like BCD and DE have D in common so they can be joined using the D, but BDC and EA have nothing in common, so they cant be joined directly.

Also for lossless decomposition, the common attribute should be a key in any one table, but in BCD and EA, nothing is common. how is this lossless?

---

Somoshree Datta 5  yup you're right
 

               

---

in R1(BCD) and R2(DE) attribute D is common and it forms the candidate key of table R2. On merging R1 and R2 using D, the resultant table is R'(BCDE).now between R' and R3(EA), attribute E is common which forms the candidate key for relation R3. So this is a lossless decomposition.

---

ya, this is fine.

im talking about when we divide tables as <ABCD><DE><AE>

in your table division Dependency is not preserved.

if we divide it as i above mentioned, dependency is preserved.

---

how are u getting R1 as ABCD? if R1(ABCD), R2(DE) and R3(EA) are the tables after decomposition ,then it wouldn't be a lossless decomposition..it would be lossy.

how is it lossy decomposition ?

we can join <ABCD> & <DE> firstly using 'D' as a key.

then <ABCDE> & <EA> using 'E' as a key.

am i wrong ?

---

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