Access Database Design and Programming, Second Edition

Third Normal Form

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 ...