Second Normal Form (2NF) — Database Management Systems

Definition

A relation is in Second Normal Form (2NF) if it satisfies two conditions: first, it must already be in First Normal Form (1NF), and second, every non-prime attribute must be fully functionally dependent on the entire primary key. In other words, there should be no partial dependencies — no non-key attribute should depend on only a part of a composite primary key.

To understand this clearly, let us first define the key terms. A prime attribute is any attribute that is part of a candidate key. A non-prime attribute is any attribute that is not part of any candidate key. A partial dependency occurs when a non-prime attribute depends on a proper subset of a composite candidate key rather than the full key.

An important observation: if a relation has a single-attribute primary key (not composite), it is automatically in 2NF because partial dependency can only occur when the primary key consists of multiple attributes. Partial dependency means depending on "part of" the key — if the key has only one attribute, there is no "part" to depend on.

Decomposition to Achieve 2NF

The solution is to decompose the relation by removing partial dependencies into separate relations:

After decomposition, ProductName and UnitPrice are stored exactly once per product. The OrderDetail table only stores the quantity for each order-product combination, with a foreign key linking back to the Product table.

Detailed Example: Student-Course Database

Consider a university enrollment scenario:

Decomposition to 2NF:

Now each student's information is stored once, each course's information is stored once, and the Enrollment table only records the relationship and grade.

Visual Representation of 2NF Violation

Anomalies Eliminated by 2NF

Algorithm for Converting to 2NF

1. Identify the primary key (must be composite for partial dependency to exist)
2. List all functional dependencies
3. For each FD where the determinant is a proper subset of the primary key, that is a partial dependency
4. Create a new relation with the partial key as primary key and its dependent attributes
5. Remove the dependent attributes from the original relation (keep the partial key as FK)
6. Repeat until no partial dependencies remain

Important Points to Remember

• 2NF only addresses partial dependencies — transitive dependencies may still exist (handled by 3NF)
• Relations with single-attribute primary keys are automatically in 2NF
• 2NF is a necessary step toward higher normal forms but is not sufficient for a fully normalized design
• In practice, most well-designed databases naturally satisfy 2NF because designers intuitively separate entity information into distinct tables
• The decomposition must be lossless — joining the decomposed tables must reproduce the original data exactly