Database Reference
In-Depth Information
Definition:
Any column (or collection of columns) that determines another column is called a determinant.
Certainly, the primary key in a table will be a determinant. In fact, by definition, any candidate key will
be a determinant. (Remember that a candidate key is a column or a collection of columns that could func-
tion as the primary key.) In this case, RepNum is a determinant, but it is not a candidate key, and that is the
problem.
Definition:
A table (relation) is in third normal form (3NF) when it is in second normal form and the only
determinants it contains are candidate keys.
167
NOTE
The previous definition is not the original definition of third normal form. This more recent definition, which is preferable to the
original, is often referred to as Boyce-Codd normal form (BCNF) when it is important to make a distinction between this defi-
nition and the original. This text will not make such a distinction, but it will take this to be the definition of third normal form.
Again, for an additional perspective, you can use a dependency diagram, as shown in Figure 5-11. The
arrows above the boxes represent the normal dependencies of all columns on the primary key. The arrows
below the boxes represent the problem
these arrows make RepNum a determinant. If there were arrows
from RepNum to all the columns, RepNum would be a candidate key and you would not have a problem. The
absence of these arrows indicates that this table contains a determinant that is not a candidate key. Thus,
the table is not in third normal form.
—
CustomerNum
CustomerName
Balance
CreditLimit
RepNum
LastName
FirstName
FIGURE 5-11
Dependencies in the Customer table
You now have identified the problem with the Customer table: It is not in third normal form. The follow-
ing method corrects the deficiency in the Customer table and in all tables having similar deficiencies.
First, for each determinant that is not a candidate key, remove from the table the columns that depend
on this determinant (but don
t remove the determinant). Next, create a new table containing all the columns
from the original table that depend on this determinant. Finally, make the determinant the primary key of
this new table. In the Customer table, for example, you would remove LastName and FirstName because they
depend on the determinant RepNum, which is not a candidate key. A new table is formed, consisting of
RepNum (as the primary key), LastName, and FirstName.
'
Customer (CustomerNum, CustomerName, Balance, CreditLimit, RepNum)
Rep (RepNum, LastName, FirstName)
Figure 5-12 shows samples of the revised Customer table and the new Rep table.
