Second Normal Form (2NF)

WHY IT EXISTS: Database normalization was developed to structure relations so that dependencies between attributes are handled cleanly. When a relation has a composite candidate key made of multiple attributes, a non-prime attribute might be determined by only part of that key. This creates a partial dependency, which 2NF was designed to eliminate. Edgar F. Codd defined 2NF as the level above 1NF where every non-prime attribute must depend on the entire candidate key, not merely a subset of it.

THE MENTAL MODEL: Treat a composite candidate key like a complete identifier. If a non-prime attribute can be understood by looking at only one piece of that identifier, then it is not truly describing the whole identifier. It belongs to a separate relation where that piece alone is sufficient to identify it.

HOW IT WORKS: A relation is in 2NF if it is already in 1NF and contains no partial dependencies. A partial dependency exists when a non-prime attribute is functionally dependent on only a proper subset of the attributes making up a candidate key. To satisfy 2NF, every non-prime attribute must depend on the whole set of attributes of every candidate key. If an attribute depends on just part of a candidate key, the relation must be decomposed so that attribute moves to a relation where the subset it depends on becomes the entire key.

WHEN TO USE IT: Use 2NF when designing or reviewing any relation that is in 1NF and has a composite candidate key. If the key consists of multiple attributes, examine each non-prime attribute to verify that it requires all of those attributes to be determined. This check matters whenever a relation uses a multi-attribute identifier.

WHEN NOT TO USE IT: A relation whose candidate keys are all single attributes is automatically in 2NF as long as it is in 1NF, because a single attribute has no proper subset that can functionally determine another attribute. Therefore, the 2NF check is unnecessary when no composite candidate keys exist.

ONE CANONICAL EXAMPLE: Consider a relation with a candidate key composed of attribute A and attribute B. Suppose a non-prime attribute C is functionally dependent only on attribute A. Because C depends on a proper subset of the candidate key, the relation is not in 2NF. The correction is to form a new relation with attribute A as the key and attribute C as the non-prime attribute, while the original relation retains A and B along with any attributes that depend on the full combination.