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, and thus does not identify the entities of this entity class, but rather identifies the entities of some other entity class.

Let us consider another example of a table scheme that is not in second normal form.

Consider the following table scheme, and assume for the purposes of illustration that, while there may be many books with the same title, no two of them have the same publisher and author:

{Title,PubID,AuID,Price,AuAddress}

Thus, {Title, PubID, AuID} is the only key. Now, AuAddress does not belong to any key, but it depends upon {AuID}, which is a proper subset of the key, in symbols:

{AuID} → {AuAddress}

Hence, this table scheme is not in second normal form. In fact, AuAddress is not a piece of information about the entities modeled in the table scheme (i.e., books), but rather about authors. Of course, we could remove the AuAddress attribute to bring the table scheme into second normal form. (If each publisher charged a single price for all of its books, then Price would also cause a violation of second normal form, but this is not the case, of course.)