BCNF Decomposition

Question

Comments

Please provide solution

---

answer should be 4

---

3 relation

BD, ABCE, CDE

Answer 1

Relation: R(ABCDE):

FDs: A->BC, CD->E, B->D, E->A

Find Candidate Keys: {A, E, BC, CD}

All FDs left side are superkey except B->D

So we will decompose at B:

Find B's Closure: {B,D}

BD will be one side and other side ABCE,

Now BD has become a Relation having SuperKey as B (B->D)

Check ABCE:

FDs: A->BC, E->A {We are not talking about D because D is not here}

Find Candidate Keys : {E}

The FD  A->BC, haven't superkey at left side so decompose at A

Find closure of A : {A,B,C}

Now ABC has become a relation having superkey as A ( A->BC )

ABC will be one side and other side EA (EA also have a superkey {E->A} )

So three relations: ABC, EA, BD

Comments

How does CD->E implied?

---

It is mentioned in the question

---

I mean it how is it impied in you decomposition?

---

It's left side is superkey, so we will not focus on this, we just want to make all left side attr Superkey. So we decomposed where it is violating the BCNF rule.

---

But BCNF should be depndency preserving and loselees also i guess?

---

In BCNF FD may or may not preserved but it should be lossless. In this question, it is not preserving FD

---

but question explicitly asks for dependency presurving hence we have to add a redundant relation ECD it will be lossless because we can combine it with EA without spurious tuples

hence the answer should be 4

---

Yes 4 relations required.

---

You are forgetting A -> E in ABCE according to transitive property, so A is a superkey also hence no further decomposition required

Answer 2

Candidate Keys: {A, E, CD, BC }

decomposition to bcnf:

R(ABC)

R(CDE)

R(BD)

R(EA)

above decomposition is not only in bcnf but also in 3nf.

Hence, it is dependency preserving and lossless.

Comments

@ Aegon

How it is lossless ?

R(ABC) ∩ R(CDE)= C and C is not superkey in any of these two relation, hence violating the property of lossless dependency.

---

no property is violated because if your check this property after union of al relations except (CDE)

$R_{134}(ABCDE) \cap R_{3}(CDE) = CDE$ and $CDE$ is a super key.

---

Can anyone please provide the detailed solution. I’m confused with the below 2 points:

1. If we have only 3 relations (ABC, EA, BD) , then (CD->E) FD is not preserved.
2. So inorder to make FD preserve as per the question, tried it by adding CDE as a relation. Giving 4 relations in total

   R(ABC), R(CDE) ,R(BD) ,R(EA). Which in addition making lossy decomposition.