How to decompose the relation R(a,b,c,d,e) whith following FDs into BCNF

normalization

I have a FDs

  • ABC->DE
  • AB->D
  • DE->ABCE
  • E->C

My trial :

Step 1 :

  • A+ = A
  • B+ = B
  • C+ = C
  • D+ = D
  • E+ = EC
  • AB+ = ABD
  • ABC+ = ABCDE
  • DE+ = ABCDE

From above we get ABC,DE are our candidate keys

Step 2 :

  1. ABC -> DE ==> no violation. bcoz abc is key.
  2. AB -> D ==> violation.
  3. (ABD)(CE)
    • in (ABD) AB is a candidate key. So it is in bcnf
    • in (CE) C is the candidate key. So it is in bcnf.

I have done here. But unable to process further. There is confusion after this that the step 2 is right or not.
Can anyone solve this?

Best Answer

In your relation schema, there are three candidate keys: ABC, ABE and DE.

Since, for instance, AB → D violates the BCNF, we can decompose the original relation in:

R1(ABD) (with dependency AB → D and candidate key AB), and
R2(ABCE) (with dependencies E → C and ABC → E, and candidate keys ABC and ABE)

this is because we decompose in two relations, AB+ and R - (AB+) + AB.

The second relation is still not in BCNF, since in E → C the attribute E is not a superkey.

So we can apply again this method to decompose R2 in:

R3(CE) (with dependency E → C and candidate key E)
R4(ABE) (with no dependency and candidate key ABE)

Both are in BCNF and the final decomposition is constituted by R1, R3, R4.

Finally, it is worth to note that the following dependencies:

DE → AB
ABC → E

are lost in the decomposition.

Related Question