Third Normal Form

Third normal form applies the principle addressed by second normal form in a more general way: It's not limited to composite primary keys. Third normal form requires that no non-key column depend on another non-key column. Each non-key column must be a fact about the primary key column.

In the authors table, the primary key is au_id. When you check each column, you find that au_ord (the position of an author's name on a multiauthor book) is not about an individual author (au_id) because an author might have several books and might occupy a different position on each (first, second, or third author). Author order really concerns each author-title relationship. The same is true of royaltyper. Both of these columns belong in the titleauthors table.

The qty_ordered and qty_shipped columns in the sales table also illustrate this principle. They concern individual line items, not the whole sales order, and should be moved to the salesdetails table.

The date_shipped column is more of a puzzle:

• If orders are shipped only when all line items are ready, date_shipped applies to the order as a whole and should go in the sales table.

• If items are shipped as they become available, the column belongs in the salesdetails table.

Since books are often out of print or otherwise unavailable, we'll assume the second model. The revised diagram is shown in Figure 2.12.

Analyzing the structures of these tables, you'll see that they satisfy both second and third normal forms:

• They satisfy second normal form because every non-key column is a fact about the entire primary key.

• They satisfy third normal form because no non-key column is a fact about another non-key column.

To summarize: Every non-key column must describe the key, the whole key, and nothing but the key.