Limitations of Second Normal Form

Second Normal Form (2NF) removes partial dependencies, but it does not remove every source of redundant facts. A relation can satisfy 2NF and still repeat a category name or a distributor's contact details across many rows. Those repeated facts can cause update, insertion, and deletion anomalies.

Precisely, a relation is in 2NF when it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key. A non-prime attribute belongs to no candidate key. Checking only the chosen primary key is insufficient when other candidate keys exist.

This lesson distinguishes the problems that 2NF fixes from the dependencies it can leave behind. The next stage is to examine Third Normal Form (3NF), rather than assume that every table with a simple primary key is fully normalized.

In the preceding lesson, student and course details were separated from enrollments. The following related example includes a grade. Assume one teaching period, one enrollment per student-course pair, one recorded name per student, and one title per course.

This original table is not in 2NF. Alice's name and the title of C101 repeat because facts with different determinants are combined. Its decomposition is:

Under these dependencies, the decomposition removes the partial-dependency problems. A student's name can be changed once, a course can exist before enrollment, and deleting the last enrollment does not delete the course. In the original sample, deleting Bob's C101 enrollment alone would not lose that course: Alice still has a C101 enrollment. The loss occurs only when its final enrollment is removed.

These tables do not demonstrate a remaining 2NF defect under the stated rules. To understand the limitation, we need another dependency inside a relation that already satisfies 2NF.

Consider this catalog extract. Assume each CD has one current title, list price, and category; each category has one current name; and a CD can be supplied by several distributors. The price here is a catalog price, not a distributor-specific offer.

CDNo cannot be the key of this extract because 102 appears twice. Under the stated rules, (CDNo, DistID) identifies a distributor assignment, while CDNo alone determines the CD details. The extract therefore has partial dependencies and is not yet in 2NF.

Remove the Partial Dependencies First

CD(CDNo PK, Title, Price, CatNo, CatName)
CDDistributor(CDNo PK/FK, DistID PK)
-- CDDistributor primary key: (CDNo, DistID)
-- DistID may additionally reference a separate Distributor table.

The CD relation now has three rows, one for each CD number. CDDistributor retains all four original pairs: (101, 101), (102, 101), (102, 102), and (103, 102). Neither the second distributor for CD 102 nor any catalog fact is discarded.

Assume CDNo is the only candidate key of CD and none of its descriptive attributes is constant across all valid records. This 1NF relation has no partial dependencies, so it satisfies 2NF. Nevertheless, the following dependencies remain:

CDNo → Title, Price, CatNo
CatNo → CatName
Therefore: CDNo → CatName

The name YA-8 still appears in the CD rows for 101 and 102. The category number determines its name but does not determine a complete CD row. This is the important residual defect: CatNo → CatName is not prohibited by 2NF in this relation.

1. Update anomaly: renaming category 1 requires updating every CD in that category. Updating only one row produces inconsistent category names.
2. Insertion anomaly: a new category, such as 3 with name YA-15, has nowhere to be stored independently of a CD. A fabricated CD record would misrepresent the data.
3. Deletion anomaly: deleting the last CD in category 2 also deletes the only stored copy of that category's name, even if the category should remain available for future CDs.

The problem is not that the category number repeats. A foreign key normally repeats when several CDs belong to one category. The redundant fact is the repeated association between a category number and its name.

Move the category description to its own relation while retaining CatNo as the connection:

Category(CatNo PK, CatName)
CD(CDNo PK, Title, Price, CatNo FK)
CDDistributor(CDNo PK/FK, DistID PK)

Category contains (1, YA-8) and (2, YA-11). Joining CD to Category on CatNo reconstructs the catalog descriptions. Joining to CDDistributor restores the four original distributor assignments. Primary and foreign keys enforce the required uniqueness and valid references.

A functional dependency^[1] can follow a chain. Suppose an item has one current distributor and each distributor has one designated warehouse contact number:

Item(item_numb, title, distrib_numb, warehouse_phone_number)

item_numb → title, distrib_numb
distrib_numb → warehouse_phone_number
Therefore: item_numb → warehouse_phone_number

Assume item_numb is the only candidate key and the relation is in 1NF, with no constant-attribute dependencies. It satisfies 2NF, but many items can repeat the same distributor phone number. Changing that number requires multiple updates; deleting a distributor's final item loses the number; and a distributor cannot be recorded before an item exists.

Item(item_numb PK, title, distrib_numb FK)
Distributor(distrib_numb PK, warehouse_phone_number)

The existing distrib_numb becomes the enforced foreign-key connection; it is not a new replacement for the phone number. The phone number moves to Distributor. If a distributor has multiple warehouses or contact numbers, revise the model to represent those facts instead of assuming a single number.

These chains illustrate the usual introductory 3NF problem: a non-superkey determinant controls a non-prime attribute. Not every dependency derived by transitivity violates 3NF. The formal rule matters when relations have multiple or overlapping candidate keys.

[1] Functional dependency: X determines Y when rows agreeing on X must also agree on Y in every valid state of the relation.

[2] Third Normal Form: A 1NF relation in which every nontrivial dependency X → A has a superkey determinant X or a prime dependent attribute A.