The Relational Model and Normalization - Database Processing: Fundamentals, Design, and Implementation - page 138

You can also eliminate such problems by simply designing (or redesigning) your tables so
that every determinant is a candidate key. This condition, which, of course, is the definition
of BCNF, will eliminate all anomalies due to functional dependencies. We will refer to this
method as the “Straight-to-BCNF” or “general normalization” method.
We prefer the “Straight-to-BCNF” general normalization strategy and will use it exten-
sively, but not exclusively, in this topic. However, this is merely our preference—either method
produces the same results, and you (or your professor) may prefer the “Step-by-Step” method.
The general normalization method is summarized in Figure 3-19. Identify every functional
dependency in the relation, and then identify the candidate keys. If there are determinants that are
not candidate keys, then the relation is not in BCNF and is subject to modification anomalies. To
put the relation into BCNF, follow the procedure in step 3. To fix this procedure in your mind, we
will illustrate it with five different examples. We will also compare it to the “Step-by-Step” approach.

Our process rule that a relation is in BCNF if and only if every determinant
is a candidate key is summed up in a variation of a widely known phrase:

By The WAy

I swear to construct my tables so that all non-key columns are dependent on the key,
the whole key and nothing but the key, so help me Codd!

By The WAy The goal of the normalization process is to create relations that are in
BCNF. It is sometimes stated that the goal is to create relations that are
in 3NF, but after the discussion in this chapter, you should understand why BCNF is
preferred to 3NF.
Note that there are some problems that are not resolved by even BCNF, and we
will discuss those after we discuss our examples of normalizing to BCNF.

Normalization example 1
Consider the SKU_DATA table:

SKU_DATA (SKU, SKU_Description, Department, Buyer)

Figure 3-19
Process for Putting a
relation into BCNF

Process for Putting a Relation into BCNF

1. Identify every functional dependency.

2. Identify every candidate key.

3. If there is a functional dependency that has a
determinant that is not a candidate key:

A. Move the columns of that functional

dependency into a new relation.

B. Make the determinant of that functional

dependency the primary key of the new relation.

C. Leave a copy of the determinant as a foreign

key in the original relation.

D. Create a referential integrity constraint between

the original relation and the new relation.

4. Repeat step 3 until every determinant of every
relation is a candidate key.

Note: In step 3, if there is more than one such functional dependency,
start with the one with the most columns.