Lesson 11 | Limitations of 2NF |

Objective | Describe the limitations of (2NF) Second Normal Form |

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:

Nonetheless, there are still a number of problems that can occur with tables in 2NF.

Consider the following 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.

To prevent these problems, you can decompose your tables to

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:

- There is one insertion anomaly: You cannot insert data about a distributor until you have an item from that distributor.
- There is one deletion anomaly: If you delete the only item from a distributor, you lose data about the distributor.
- There is also a modification anomaly: The distributor's warehouse phone number is duplicated for every item the company gets from that distributor.

[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.