Page 2 of 11
Step 2:decompose R1(A,B,C,E) into R3(A,B,C) and R4(E,A). A->BC violates BCNF
in R1
We see that
R2(B,D) is in BCNF
because B is the candidate key.
R1(A,B,C,E) is NOT in BCNF
because violation caused by relations:
A
BC
C
A
Therefore, we perform this decomposition.
New functional dependencies:
F3 (in R3):
A
BC
C
A
F4 (in R4):
E
A
We see that
R3 is in BCNF
because no functional dependency in F3 violates BCNF.
Also,
R4 is in BCNF
because F4 does not violate BCNF.
This decomposition is also dependency preserving, since
(F2
∪
F3
∪
F4)
+
= F
+
Answer
: the desired schema is: R2(B,D), R3(A,B,C), R4(E,A)
Derivation tree:
R(A,B,C,D,E)
F: {E
A, A
BC, C
A, B
D}
/\
/
\
/
\
/
\
R1(A,B,C,E)
R2(B,D)
F1: {E
A, A
BC, C
A}
F2: {B
D}
(dependency preserving)
/\
/
\
/
\
/
\
R3(A,B,C)
R4(E,A)
F3: {A
BC, C
A}
F4: {E
A}
(dependency preserving)