Table of Contents
### 4.6. Third Normal Form

Entire Site

Second normal form is good, but we can do better. We have seen that if a table scheme is in second normal form, then no strictly informational attribute depends on a proper subset of a key. However, there is another undesirable possibility. Let us illustrate with an example.

Consider the following table scheme and assume, for the purposes of illustration, that no two books with the same title have the same publisher:

{Title,PubID,PageCount,Price}

The only key for this table scheme is {Title,PubID}. Both PageCount and Price are informational attributes only.

Now, let us assume that each publisher decides the price of its books based solely on the page count. First, we observe that this table is in second normal form. To see this, consider the proper subsets of the key. These are:

{Title} and {PubID}

But none of the dependencies:

{Title} → {PageCount} {Title} → {Price} {PubID} → {PageCount} {PubID} → {Price}

hold for this table scheme. After all, knowing the title does not determine the book, since there may be many books of the same title, published by different publishers. Hence, the table is in second normal form.

It is also not correct to say that:

{PageCount} → {Price}

holds, because different publishers may use different price schemes, based on page count. In other words, one publisher may price books over 1000 pages at one price, whereas another may price books over 1000 pages at a different price. However, it is true that:

{PubID,PageCount} → {Price}

holds. In other words, here we have an informational attribute (Price) that depends not on a proper subset of a key, but on a proper subset of a key (PubID) together with another informational attribute (PageCount).

This is bad, since it may produce redundancy. For instance, consider Table 4.3. Note that the price attribute is redundant. After all, we could fill in the Price value for the third row if it were blank, because we know that PubID 2 charges $34.95 for 500-page books.

Title | PubID | PageCount | Price |
---|---|---|---|

Moby Dick | 1 | 500 | 29.95 |

Giant | 2 | 500 | 34.95 |

Moby Dick | 2 | 500 | 34.95 |

We can summarize the problem with the dependency:

{PubID,PageCount} → {Price}

by saying that the attribute Price depends upon a set of attributes:

{PubID,PageCount}

that is not a key, not a superkey, and not a proper subset of a key. It is a mix containing one attribute from the key {Title,PubID} and one attribute that is not in any key.

With this example in mind, we can now define third normal form. A table scheme is in third normal form, or 3NF, if it is not possible to have a dependency of the form:

{A1,. . .,Ak} → {B}

where B does not belong to any key (is strictly
informational) and
{A_{1},...,A_{k}} is
not a superkey. In other words, third normal
form does not permit any strictly informational attribute to depend
upon anything other than a
superkey. Of course, superkeys determine all attributes, including
strictly informational attributes, and so all attributes depend on
any superkey. The point is that, with third normal form, strictly
informational attributes depend only on
superkeys.