Limitations of Second Normal Form (2NF)

Second normal form removes partial dependencies, but it does not eliminate every source of redundancy. A table can satisfy 2NF and still repeat facts because one non-key attribute determines another. These transitive dependencies can cause update, insertion, and deletion anomalies. Third normal form addresses this problem in the example below.

What Does 2NF Guarantee?

A relation is in 2NF when it is in first normal form and every non-prime attribute depends on the whole of every candidate key, rather than a proper subset of a candidate key. A candidate key is a minimal set of attributes that uniquely identifies a row; a non-prime attribute belongs to no candidate key.

For example, in ActorFilm(FilmID, ActorID, FilmName, ActorName), assume the only candidate key is (FilmID, ActorID). The dependencies FilmID → FilmName and ActorID → ActorName are partial dependencies. Separating Film, Actor, and ActorFilm removes them.

When all candidate keys consist of a single attribute, partial-key dependencies are not the issue, so a relation in 1NF satisfies 2NF. However, a single-column primary key alone is not enough to establish this: there may be other, composite candidate keys to examine. First normal form concerns single values from the intended domains and the absence of repeating groups; declaring a primary key alone does not establish normalization.

Example: An Item Table That Is in 2NF

Consider a product catalog with these business rules:

• ItemNo is the only candidate key. Titles and prices need not be unique.
• Each item belongs to exactly one category, and a category can contain many items.
• Each CategoryID determines one current category name and one current manager. Neither the name nor the manager is assumed to identify the category uniquely.
• The catalog records current facts, not a history of category assignments or manager changes.

Under these rules, the important functional dependencies are:

ItemNo → Title, Price, CategoryID
CategoryID → CategoryName, CategoryManager

The table is in 2NF: its attributes contain single values, and its only candidate key, ItemNo, has no component that could determine an attribute independently. Nevertheless, category facts are repeated. A functional dependency expresses a rule that must hold for all valid data, not merely a pattern found in these three sample rows.

The Remaining Problem: Transitive Dependencies

Because an item identifies its category and that category identifies its manager, the table contains this dependency chain:

ItemNo → CategoryID → CategoryManager

The same reasoning applies to CategoryName. The intermediate determinant, CategoryID, is not a superkey of Item because multiple items can share it. The category name and manager are non-prime attributes. These dependencies violate 3NF even though the table satisfies 2NF.

Transitivity itself is a valid inference rule: if A → B and B → C, then A → C. The design problem is storing category-level facts repeatedly in an item-level relation, not the existence of every possible dependency chain.

Three Anomalies That 2NF Can Leave Behind

• Update anomaly: If the manager of C10 changes, both item rows must be updated. Changing only one leaves conflicting managers for the same category.
• Insertion anomaly: A new category cannot be recorded independently of an item without inventing an item row or weakening the intended design.
• Deletion anomaly: Deleting item 103, the last item in C20, also removes the only stored record of that category's name and manager.

Move Category Facts into Their Own Table

Separate the relation into Item and Category. Keep CategoryID in Item as a foreign key:

Item(ItemNo PK, Title, Price, CategoryID FK)
Category(CategoryID PK, CategoryName, CategoryManager)

Item.CategoryID references Category.CategoryID

This is schema notation, not executable SQL. The tables now store the same facts at their appropriate levels:

Now a category manager changes in one row, a category can exist without products, and deleting an item does not delete its category. Joining Item to Category on CategoryID reconstructs the original item information without introducing extra combinations, because CategoryID uniquely identifies each Category row. The decomposition also preserves the stated dependencies within the two tables.

Enforce the relationship with a foreign key and, for the stated mandatory category rule, a NOT NULL constraint on Item's CategoryID. Normalization organizes facts; constraints enforce the permitted references and values. If the system also needs manager contact details, give managers their own identifiers and model those facts separately.

How 3NF Strengthens the Design

Formally, for every nontrivial functional dependency X → A, 3NF requires that X be a superkey or that A be prime (part of some candidate key). The original category dependencies fail both tests in Item. After decomposition, CategoryID is the key of Category, where those facts belong.

Under the stated dependencies and with no additional dependencies among their attributes, both resulting tables satisfy 3NF. Adding a surrogate ID without separating category facts would not fix the original problem.

Quick Check

An Employee table has EmployeeID as its only candidate key and stores DepartmentID and DepartmentName. Many employees share a department, and each department ID determines its name. Can this table satisfy 2NF while violating 3NF?

Summary

2NF removes dependencies on part of a candidate key. It does not guarantee that every remaining fact belongs in the same table. Identify the business rules, examine every candidate key, and look for non-key determinants such as CategoryID in the Item example. The next lesson explains transitive dependencies in more detail.

Further reading: Data Analysis for DB Design.