Understanding the Limitations of Second Normal Form (2NF)

Second Normal Form (2NF) improves upon First Normal Form (1NF) by eliminating partial dependencies. However, even after achieving 2NF, a database design can still suffer from redundancy and anomalies due to transitive dependencies. This lesson explores these limitations and prepares you for understanding Third Normal Form (3NF), which addresses them.

Example: The Item Table

Consider a relation that stores monthly sales data for different CD categories:

Item (ItemNo, Title, Price, CategoryID, CategoryName, CategoryManager)

The functional dependency for this relation can be represented as:

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

Since each CD belongs to only one category, every non-key attribute is dependent on the primary key ItemNo. The table therefore satisfies Second Normal Form. However, a transitive dependency exists: CategoryID determines CategoryName and CategoryManager. This means that CategoryManager indirectly depends on ItemNo through CategoryID.

Why 2NF Is Not Enough

Even though 2NF removes partial dependencies, it does not address transitive dependencies. In the Item example, if the category manager changes, every item in that category must be updated. Such redundancy introduces risks of inconsistent data.

Third Normal Form (3NF) resolves this issue by ensuring that every non-key attribute depends only on the primary key — not through another non-key attribute.

Mathematical Basis of Transitivity

If A → B and B → C,
then A → C.

In a database context, this means if ItemNo determines CategoryID and CategoryID determines CategoryManager, then ItemNo also determines CategoryManager. This indirect relationship causes redundancy and update anomalies.

Revisiting Second Normal Form

Let’s revisit the definition of 2NF. First Normal Form requires that each table have a primary key and that each field contain only atomic (indivisible) values. Second Normal Form builds on this by ensuring that all non-key attributes are fully dependent on the whole of a composite key — not just part of it.

Example: The ActorFilms Table

Imagine you decide to store a list of films and their actors. The data includes film and actor information:

The composite primary key is (FilmId, ActorId). This table satisfies 1NF, but not 2NF. Some columns — like FilmName — depend only on FilmId, while ActorName and DateOfBirth depend only on ActorId. These are partial dependencies, violating 2NF.

How to Fix Partial Dependencies

To bring the design into 2NF, separate the data into three related tables that isolate independent concepts:

Table 1: Film Details

Table 2: Actor Details

Table 3: Actor–Film Relationship

This structure eliminates partial dependencies and satisfies 2NF. Yet, transitive dependencies may still exist if non-key attributes depend on other non-key attributes. That limitation leads naturally to the next level of refinement: Third Normal Form.

Key Terms

• Functional Dependency: A one-way relationship where, for each unique value of one attribute, there is only one corresponding value of another.
• Transitive Dependency: A dependency where one attribute depends on another through a third attribute.

Understanding these principles helps prevent anomalies, reduce redundancy, and prepare your schema for full normalization in 3NF.