Limitations of Second Normal Form

Second Normal Form (2NF) is a significant milestone in the normalization process. It addresses a specific class of design defect - partial dependency in tables that use a composite primary key. When a relation is in 2NF, every non-key attribute is fully functionally dependent on the entire primary key, not just part of it.

That is a meaningful improvement over 1NF, but it is not the finish line. A table in 2NF can still contain repeated values, insertion anomalies, deletion anomalies, and modification anomalies. The reason is that 2NF addresses only the relationship between non-key attributes and the primary key. It says nothing about dependencies among non-key attributes themselves. Those are addressed by Third Normal Form.

The diagram below illustrates the limitations of 2NF using an Enrollment table with a composite primary key of (StudentID, CourseID).

Data Analysis for DB Design

The original Enrollment table mixes data about three distinct entities - students, courses, and enrollments - in a single table:

StudentID (PK part)	CourseID (PK part)	StudentName	CourseTitle	Grade
S001	C101	Alice	Intro to DB	A
S001	C102	Alice	Programming	B
S002	C101	Bob	Intro to DB	C

Two partial dependencies are visible immediately:

• StudentID → StudentName - StudentName depends only on StudentID, not on the full composite key (StudentID, CourseID). Alice's name appears twice because she is enrolled in two courses.
• CourseID → CourseTitle - CourseTitle depends only on CourseID. "Intro to DB" appears twice because two different students are enrolled in that course.

Only Grade depends on the full composite key: (StudentID, CourseID) → Grade.

These partial dependencies cause three anomalies:

1. Update Anomaly. Changing Alice's name requires updating every row where Alice is enrolled. If Alice is enrolled in ten courses, ten rows must be updated simultaneously. A missed row leaves the database with two different names for the same student.
2. Insertion Anomaly. A new course cannot be added to the database until at least one student enrolls in it. The course's title is stored in the same row as the enrollment, so there is no row to store a course that has no enrollments yet.
3. Deletion Anomaly. Deleting student S002's enrollment in C101 also deletes the only record of the course "Intro to DB" if S002 was the last student enrolled. The course information is lost along with the enrollment.

After 2NF Decomposition

Decomposing the Enrollment table into three separate tables eliminates all partial dependencies:

Students
StudentID (PK)	StudentName
S001	Alice
S002	Bob

Courses
CourseID (PK)	CourseTitle
C101	Intro to DB
C102	Programming

Enrollments
StudentID (FK)	CourseID (FK)	Grade
S001	C101	A
S001	C102	B
S002	C101	C

Every non-key column now depends on the entire primary key of its table. No partial dependencies remain. The decomposition is complete - and the three anomalies are gone.

Second Normal Form resolves partial dependencies, but it does not resolve all anomalies. The following CD table illustrates a problem that 2NF does not prevent:

CD
CDNo	Title	Price	CatNo	CatName	DistId
101	Tales 1	14.95	1	YA-8	101
102	Tales 2	13.95	1	YA-8	101
102	Tales 2	13.95	1	YA-8	102
103	Tales 3	12.95	2	YA-11	102

In relational notation: CD (CDNo, Title, Price, CatNo, CatName, DistID)

CatNo and CatName repeat across multiple rows. This creates the same three anomalies:

1. Insertion anomaly. A new category (for example, CatNo=3, CatName=YA-15) cannot be stored until at least one CD exists in that category.
2. Deletion anomaly. Deleting the last CD in a category accidentally deletes the only remaining record of that category's name.
3. Modification anomaly. If category YA-8 is renamed, every row with CatNo=1 must be updated. A missed row creates an inconsistency.

The normalization process is signaling that "category" is its own concept and belongs in a separate table. But 2NF alone does not force this decomposition when the primary key is a single column - because there can be no partial dependency on a single-column key.

For further reading on database normalization: Data Analysis for DB Design

2NF answers one specific question: does every non-key attribute depend on the whole primary key? It does not answer: do non-key attributes depend on other non-key attributes? That second question is the domain of Third Normal Form.

Five specific limitations of 2NF:

1. Transitive dependencies are still permitted. If Key → A and A → B, then B is transitively dependent on the key through A. 2NF places no restriction on this pattern. Transitive dependencies are a primary cause of update anomalies in 2NF-compliant tables.
2. Redundancy can persist without partial dependency. Repeated attribute values - like a category name or a warehouse phone number - can appear in a 2NF table because the table is storing descriptive facts about a referenced entity rather than about its own subject. 2NF does not force that descriptive data into a separate relation. Third Normal Form typically will.
3. Anomalies can survive 2NF. Insertion, deletion, and modification anomalies can exist in a 2NF-compliant table when they are caused by transitive dependencies rather than partial dependencies. 2NF reduces one set of problems without eliminating all of them.
4. 2NF is silent on many real-world constraints. Functional dependencies capture many business rules, but databases also need to handle multivalued attributes, optional relationships, derived values, and constraints that functional dependency theory does not express. Normal forms provide important structure but do not replace thoughtful schema design.
5. Mechanical normalization can be over-applied. Normalization is a correctness tool, not a performance tool. Aggressive decomposition without clear dependency justification increases join complexity and can degrade query performance. The correct approach is to normalize based on actual dependencies and apply deliberate, documented denormalization only when performance requirements justify it.

The most important limitation of 2NF is that it leaves transitive dependencies unresolved. A transitive dependency occurs when:

X --> Y  and  Y --> Z  (therefore X --> Z transitively)

Where X is the primary key and Y and Z are both non-key attributes. The dependency of Z on X runs through Y rather than directly.

Consider an Item table where each item comes from one distributor, and each distributor has one warehouse phone number:

Item (item_numb, title, distrib_numb, warehouse_phone_number)

The primary key is item_numb - a single column, so the table is automatically in 2NF. But the functional dependencies reveal a transitive dependency:

item_numb        --> distrib_numb
distrib_numb     --> warehouse_phone_number
therefore:
item_numb        --> warehouse_phone_number  (transitively)

The warehouse phone number is a fact about the distributor, not about the item. Storing it in the Item table causes the same three anomalies:

1. Insertion anomaly. A distributor's phone number cannot be stored until at least one item exists for that distributor.
2. Deletion anomaly. Deleting the only item for a distributor deletes the only stored copy of that distributor's phone number.
3. Modification anomaly. The phone number repeats for every item from the same distributor. An update must touch multiple rows and can easily become inconsistent if any row is missed.

This relation satisfies 2NF but remains poorly designed. That is the core practical limitation of 2NF: it does not prevent transitive dependencies, and transitive dependencies cause anomalies.

The solution is Third Normal Form decomposition: move the distributor data into a separate Distributor table where distrib_numb is the primary key, and replace warehouse_phone_number in the Item table with a foreign key reference to Distributor.

The next lesson introduces Third Normal Form.

[1] Functionally dependent: A one-way relationship between two attributes such 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 in second normal form and there is no transitive functional dependency of a non-key attribute on the primary key.