Access Database Design and Programming, Second Edition

Second Normal Form

Intuitively, a table scheme T is in second normal form, or 2NF, if all of the strictly informational attributes (attributes that do not belong to any key) are attributes of the entities in the table scheme, and not of some other class of entities. In other words, the informational attributes provide information specifically about the entities in this entity class and not about some other entities.

Let us illustrate with an example.

Consider a simplified table scheme designed to store house addresses. One possibility is:

{City,Street,HouseNumber,HouseColor,CityPopulation}

The CityPopulation attribute is out of place here, because it is an attribute of cities, not house addresses. More specifically, CityPopulation is strictly an informational attribute (not for identification of houses) but it gives information about cities, not house addresses. Thus, this table scheme is not in second normal form.

We can be a little bit more formal about the meaning of second normal form as follows. Referring to the previous example, we have the dependency:

{City} → {CityPopulation}

where CityPopulation does not belong to any key, and where City is a proper subset of a key, namely, the key {City, Street, HouseNumber}. (By proper subset, we mean a subset that is not the whole set.)

A table scheme is in 2NF if it is not possible to have a dependency of the form:

{A1,. . .,Ak} → {B}

where B does not belong to any key (is strictly informational) and {A₁,...,A_k} is a proper subset of some key, ...