Normal Forms   «Prev  Next»

Lesson 11 Limitations of 2NF
ObjectiveDescribe the limitations of (2NF) Second Normal Form

Second Normal Form Limitations

Normalizing a table to 2NF ensures that every column in the table is functionally dependent [1] on the entire primary key.
Nonetheless, there are still a number of problems that can occur with tables in 2NF.
Consider the following table:

Diagram above represents a non-decomposed table

It is represented in relational notation as:

CD (CDNo, Title, Price, CatNo, CatName, DistID
Note that the CatNo and CatName values are repeated in every record. One problem with this table is that you cannot insert information about a category (such as YA-8) until there is a CD to put into that category. Another problem is that deleting the record of the last CD in a category (such as CDNo 103) will delete any record of that category.
To prevent these problems, you can decompose your tables to third normal form[2], which we will look at in the next module.

Problems with 2NF Relations

Although second normal form eliminates problems from many relations, you will occasionally run into relations that are in second normal form yet still exhibit anomalies. Assume, for example, that each new DVD title that DistributedNetworks carries comes from one distributor and that each distributor has only one warehouse that has only one phone number. The following relation is therefore in second normal form:
Item (item_numb, title, distrib_numb, warehouse_phone_number)
For each item number, there is only one value for the item's title, distributor, and warehouse phone number. The following anomalies exist:
  1. There is one insertion anomaly: You cannot insert data about a distributor until you have an item from that distributor.
  2. There is one deletion anomaly: If you delete the only item from a distributor, you lose data about the distributor.
  3. There is also a modification anomaly: The distributor's warehouse phone number is duplicated for every item the company gets from that distributor.
The relation is in second normal form but not third.
[1]functionally dependent: A one-way relationship between two attributes so that, for any unique value of one attribute, there is only one value of the other attribute.
[2]third normal form (3NF): A table is in third normal form if it is 1) in second normal form and 2) there is no transitive functional dependency.