Second Normal Form

The second normalization rule applies only in tables whose primary keys consist of multiple columns. It states that every non-key column must depend on the entire primary key. A table must not contain a non-key column that pertains to only part of a composite primary key. Putting a table into second normal form requires making sure that all the nonprimary key columns (the columns that give information about the subject but do not uniquely define it) relate to the entire primary key and not just to one of its components.

To illustrate, look at the contract column in the titleauthors table (Figure 2.10). Does it apply to each author-title combination? If each author on a book has a separate contract, it does, but if the company signs contracts only when all authors are in agreement, it doesn't.

In this case, your company's legal division informs you that a contract is about a book, not about each individual author, and you move the column to the titles table (Figure 2.11). This illustrates why database design is tricky: Your decisions often depend on the particular business model your company uses.

To summarize: Second normal form requires that no non-key column be a fact about a subset of the primary key. It applies when the primary key is made up of more than one column and is irrelevant when the primary key is one column only.