The RM3 lecture exercise solution

Exercise.
Given relation R(A, B, C, D) with FDs
A → B
A → D
What normal form is R in (is it in BCNF or 3NF)? If not in BCNF or 3NF, decompose R into BCNF or 3NF
relations.

Solution.

What are the keys for the given relation?
We start with attributes on the left of given FDs, and use inference rules to compute the closures for these
attributes and decide candidate keys for the relation.

A→ B, D, C?
No. {A} is not a key for relation R.
A, C → B, D, C, A
Yes. {A, C} is the only key for relation R.

Once candidate keys are decided for the relation, we apply the definition of BCNF and 3NF to decide if
the relation is in BCNF or 3NF.

R(A, B, C, D) is not in BCNF. Reason:
The only key of R is {A, C}. But for each of the given FDs, the left hand side is not the key.

R(A,B,C,D) is not in 3NF. Reason:
For each FD, the l.h.s. is not a the key and the right hand side is not part of the key either.

Following the BCNF/3NF decomposition algorithm (4 steps) to decompose the relation into relations in
BCNF/3NF.

Decomposition:
R1(A, B)
R2(A, D)
R3(A*, C)

As R1 and R2 share the same Primary key, they can be combined and keep the BCNF. The final set of
relations are:
R12(A, B, D)
R3(A*, C)
Both are in BCNF. The decomposition is lossless and dependency preserving.












































学霸联盟