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:

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.

While second normal form (2NF) is a valuable step in the normalization process, it does have some limitations:

1. Redundancy can still exist: Even if a table is in 2NF, it can still contain redundant data. For example, if two separate tables have a foreign key relationship with a common third table, data duplication can still occur.
2. Dependencies between non-key attributes: 2NF only considers functional dependencies between attributes and the primary key. It does not consider dependencies between non-key attributes. If non-key attributes depend on each other, this can lead to data anomalies.
3. Does not guarantee absence of anomalies: While 2NF can help eliminate certain types of data anomalies, it does not guarantee that all anomalies will be eliminated. Anomalies can still occur due to complex relationships between data elements.
4. Can lead to excessive table splitting: In some cases, applying 2NF can lead to an excessive number of tables, which can make the database more complex and difficult to manage.
5. Can be difficult to achieve in practice: Achieving 2NF can be difficult in practice, particularly in complex databases with many interrelated tables. It may require significant effort to properly analyze and decompose the data in a way that satisfies 2NF.

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.