Relation Decomposition

Question

Decompose below relation into 2NF,3NF and BCNF with lossless join and Dependency Preserving.

$R(ABCDE)$

$F=\{AB\rightarrow C,DE \rightarrow C,B \rightarrow D\}$

 

According to my analysis

The only key here is $ABE$

So, Dependencies $AB\rightarrow C,B\rightarrow D$ are partial dependencies.

I decomposed as below

$R_1(ABC) \{AB\rightarrow C\}$

$R_2(BD)\{B \rightarrow D\}$

$R_3(CDE) \{DE \rightarrow C\}$

And All three above are in BCNF as well.

Is my decomposition correct?

Comments

I have a doubt. Is it lossless?

---

I feel decomposition is correct and all are in BCNF. Also dependencies are preserved. But i think its a lossy decomposition.

Like in R1 and R2, common attribute is B and it is key for R2.

But between R3 and R2, D is common attribute and its not a key in R2 or R3.

And R3 and R1, C is common attribute and its not a key in R1 or R3.

For lossless decomposition, Common attribute must be a key for at least one relation, https://www.geeksforgeeks.org/lossless-join-and-dependency-preserving-decomposition/

---

@Ayush Upadhyaya , the decomposition is lossy . But I can't figure out a way to make this decomposition lossless. Is this a test series question ? 

---

make one more table, (A,B,E) to make it lossless

---

Which relation should be joined with R3 at last to obtain the original relation ?

---

(A,B,E) with (A,B,C)

then (A,B,C,E) with (B,D)

then (A,B,C,D,E) with (C,D,E)

Answer 1

It is Lossy Decomposition. Dependency preserving decomposition is not possible till BCNF.

Comments

@YesAbhisa-Sorry I wrote it wrong. Please check now.

---

@Ayush Upadhyaya Yes,it is correct as the dependencies are preserved and LHS of each FD is a key to the table(BCNF).
For making decomposition lossless, we generally create a relation taking the candidate key, which has been done in  [

R4(ABE)] . It helps in joining all the tables.

I had not decomposed it to R3(CDE) {DE→C} taking CDE as a result of which the dependency was not preserved.
Thanks for feedback. :)
 

---

For making relation lossless we always take candidate key.kindly reply.