Boyce Codd Normal Form understanding

database-designnormalization

I have a knotty (at least for me) problem to solve.

Normalize, with respect to Boyce Codd NF, the relational scheme:

E(A, B, C, D, E, F)

by assuming that (A, B, C) is the unique candidate key and that the following additional functional dependencies hold:

A,B → D
C,D → E
E → A

Actually what I did is:

E1(ABCEF), E2(ABD), E3(ABCDF), E4(CDE)

then I added E5(ABC) and E6(EA) getting the final result:

E1(ABC), E2(ABD), E3(CDE), E4(EA).

Unfortunately, what I wrote is wrong… Can anyone help me?

Best Answer

You dropped "F" somewhere.

Since you're given {ABC} is a candidate key, then you know that ABC->DEF. (That just follows from the definition of "candidate key".) And ABC->D, ABC->E, and ABC->F, by Armstrong's axiom of decomposition.

The important part as far as BCNF is concerned is E->A.

So the final result should be: E1(ABC), E2(ABD), E3(CDE), E4(EA), E5(ABCF)

I think that's right, except for E1. You don't need a separate relation for a candidate key. I'm not sure how you derived that one given what's in the problem.

Usually, your textbook will have at least one algorithm for normalizing relations. In the real world, there's often more than one 5NF schema for a given set of FDs.

Related Question