1

Consider a relation R(A, B, C, D, E) with the following function dependencies: A->BC, D->CE, C->E

AD+ = ABCDE

Prime Attributes: AD Non-Prime Attributes: BCE

Decomposed into 3NF but not BCNF

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

Cillian Hughes
  • 47
  • 7

2 Answers2

0

It's been a long time for me since I've done this. But if I remember right in 3NF it is not allowed for a column to be in a table if it is transitively dependent on another column.

In this case, the only transitive dependency is A -> C -> E, which means E needs to be extracted from R.

This you have done to my understanding. Something tells me you might need to extract C from R1, but that is probably only nessecary in BCNF.

Glubus
  • 2,819
  • 1
  • 12
  • 26
0

The third normal form in your case is the following:

R1 (A B C)

R2 (C E)

R3 (C D)

R4 (A D)

Note that this is the only way of decomposing your relations in third normal form preserving the dependencies, and that all the resulting dependencies are such that all the decomposed schemas are also in BCNF.

Finally, one can note that the same relation can be decomposed in BCNF in different ways by losing some functional dependency.

Renzo
  • 26,848
  • 5
  • 49
  • 61
  • Your paragraphs are self-contradictory. You say there's only one 3NF decompostion, but there are many BCNF. But every BCNF is also in 3NF. Maybe you mean, there's only one FD-preserving 3NF decomposition. (Your 1st paragraph is a bit strange because you say "all the schema" but there is only one.) – philipxy Feb 08 '16 at 10:30