Second Normal Form

A relation is in second normal form if it is in first normal form and in addition all its attributes are dependent on the entire candidate key. The key in Figure 2-15, for example, is {ProductName, SupplierName}, but the SupplierPhoneNumber field is dependent only on the SupplierName, not on the full composite key.

Figure 2-15. All the attributes in a relation should depend on the whole key.

We've already seen that this causes redundancy, and that the redundancy can, in turn, result in unpleasant maintenance problems. A better model would be that shown in Figure 2-16.

Figure 2-16. These two relations are in second normal form.

Logically, this is an issue of not trying to represent two distinct entities, Products and Suppliers, in a single relation. By separating the representation, you're not only eliminating the redundancy, you're also providing a mechanism for storing information that you couldn't otherwise capture. In the example in Figure 2-16, it becomes possible to capture information about Suppliers before obtaining any information regarding their products. That could not be done in the first relation, since neither component of a primary key can be empty.

The other way that people get into trouble with second normal form is in confusing constraints that happen to be true at any given moment with those that are true for all time. The relation shown in Figure 2-17, for example, assumes that a supplier has only one address, which might be true at the moment but will not necessarily remain true in the future.

Figure 2-17. Suppliers might have more than one address.