What should be the rule for excluding an FD from consideration while doing BCNF or any other Normalization

database-designnormalizationrelational-theory

Let us consider this example:

http://www.mathcs.emory.edu/~cheung/Courses/377/Syllabus/9-NormalForms/examples.html

At the first decomposition step, it is using A -> B C D E.

On the next step, the same FD is not considered any more.

Is it because,

  • A -> B C D E has already been used up in a decomposition.

Or,

  • A -> B C D E is irrelevant to the new calculation.

Note: So, the answer is, " A -> B C D E has already been used up in a decomposition."

Best Answer

The algorithm that decomposes a relation schema R to produce the BCNF operates in the following way: at each step, a dependency is searched that violates the Normal Form. If one if found (as A -> B C D E in the example), the schema is decomposed in two subschemas, one with the attributes of A+, and the other one with the attributes of the relation R minus all the attributes determined by A. Then the same steps are applied to all the schemas obtained, while there are other dependencies that violates the BCNF.

So, in the example, the original schema R is decomposed in the following two schemas:

R1 = (A B C D E F G H)  (with key A)

and

R2 = (A I J K L M)  (with key AIL)

In the next step, all the functional dependencies (projected over the subschemas) should be tested to see if they violate the BCNF, first for R1, then for R2.

But obvioulsy in relation R1, (which has key A, for the way in which it has been build), the dependency A -> B C D E does not violate the BCNF (that says that each dependency should have a superkey as determinant), so it is not used anymore to decompose the relation.

So, from this example you can see that the dependency used for the decomposition is not used in the later steps of the algorithm: its determinant is the key of the first of the two new relations produced, and for this reason that particular dependency will not used anymore, since it does violate the BCNF of the schema produced (while other dependencies in the schema could still be used to decompose, like in the example linked).

Related Question