Second Normal Form: Eliminating Partial Dependencies

In the previous page, we developed an intuitive understanding of partial dependencies—what they are, why they occur, and the damage they cause. Now we elevate that intuition to mathematical precision.

Second Normal Form (2NF) was introduced by E.F. Codd in 1971 as part of his pioneering work on relational database theory. It represents the second level in the normalization hierarchy, building upon First Normal Form and addressing a specific class of redundancy problems: those caused by partial dependencies.

Let's state the definition with complete mathematical rigor:

Definition: Second Normal Form (2NF)

A relation R is in Second Normal Form if and only if:

1. R is in First Normal Form (1NF), AND
2. Every non-prime attribute of R is fully functionally dependent on every candidate key of R

Equivalently, a relation is in 2NF if:

1. It is in 1NF, AND
2. It has NO partial dependencies (no non-prime attribute depends on a proper subset of any candidate key)

Breaking Down the Definition:

The definition has two parts, both of which must be satisfied:

Part 1: 1NF Prerequisite

Before checking for 2NF, the relation must already be in 1NF. This means:

• All attributes must have atomic values (no repeating groups, no multi-valued attributes)
• Each cell contains exactly one value
• There is a defined primary key

Part 2: No Partial Dependencies

This is the core requirement unique to 2NF. For every non-prime attribute A in the relation:

• A must depend on the COMPLETE candidate key
• No proper subset of any candidate key should determine A
• The dependency must be "full" not "partial"

This requirement applies to EVERY candidate key, not just the primary key. If a relation has multiple candidate keys, the non-prime attributes must be fully dependent on ALL of them.

There are important special cases where a relation is AUTOMATICALLY in 2NF without further analysis:

Case 1: Single-Attribute Candidate Keys

If all candidate keys consist of single attributes, the relation is automatically in 2NF (assuming it's in 1NF).

Why? Partial dependencies require depending on a PROPER SUBSET of the key. If the key has only one attribute, the only proper subset is the empty set, and no attribute can depend on nothing. Therefore, partial dependencies are mathematically impossible.

Case 2: All Attributes Are Prime

If every attribute in the relation is part of some candidate key (i.e., there are NO non-prime attributes), the relation is automatically in 2NF.

Why? The 2NF definition specifically concerns non-prime attributes. If there are no non-prime attributes, there's nothing that can have a partial dependency.

Example:

Meeting(RoomID, TimeSlot, MeetingID)
Candidate Keys: {RoomID, TimeSlot} and {MeetingID}

All three attributes are prime (each appears in at least one candidate key), so there are no non-prime attributes to check. The relation is automatically in 2NF.

To rigorously determine whether a relation is in 2NF, follow this systematic procedure:

Algorithm: 2NF Verification

Worked Example: Full Verification

Relation: ProjectAssignment(EmpID, ProjID, EmpName, ProjName, HoursWorked)

Step 1: Verify 1NF ✓

• All values are atomic
• Primary key is {EmpID, ProjID}

Step 2: Candidate Keys

• {EmpID, ProjID} is the only candidate key (composite)

Step 3: Quick Exit — FAILS (key is composite, proceed)

Step 4: Prime Attributes

• EmpID, ProjID

Step 5: Non-Prime Attributes

• EmpName, ProjName, HoursWorked

Step 6: Quick Exit — FAILS (non-prime attributes exist, proceed)

Step 7: Proper Subsets of Key

• {EmpID}
• {ProjID}

Step 8: Check Dependencies

Step 9: Conclusion

Partial dependencies exist:

• {EmpID} → EmpName
• {ProjID} → ProjName

The relation is NOT in 2NF.

Understanding where 2NF fits in the normalization hierarchy clarifies its role and limitations:

The Normalization Ladder:

Key Relationships:

2NF ⊂ 1NF: Every relation in 2NF is also in 1NF (by definition, 2NF requires 1NF)

3NF ⊂ 2NF: Every relation in 3NF is also in 2NF (3NF adds additional constraints)

BCNF ⊂ 3NF (with some nuance): Generally, BCNF is stricter than 3NF

Implication:

If you achieve 3NF or higher, you automatically have 2NF. However, when normalizing step-by-step, achieving 2NF is a necessary intermediate step. You cannot jump directly from 1NF to 3NF without passing through 2NF conditions.

When a relation violates 2NF, the remedy is decomposition—breaking the relation into smaller relations that each satisfy 2NF. Understanding the relationship between 2NF and decomposition is crucial.

The Decomposition Principle:

For each partial dependency X → A (where X is a proper subset of the key and A is a non-prime attribute):

1. Create a new relation containing X and A (where X is the key)
2. Remove A from the original relation

This process is repeated until no partial dependencies remain.

Preview Example:

Original relation NOT in 2NF:

StudentCourse(StudentID, CourseID, StudentName, CourseName, Grade)

Partial Dependencies:
  {StudentID} → StudentName
  {CourseID} → CourseName

Decomposition to achieve 2NF:

Student(StudentID, StudentName)
  Key: {StudentID}
  
Course(CourseID, CourseName)
  Key: {CourseID}
  
Enrollment(StudentID, CourseID, Grade)
  Key: {StudentID, CourseID}

Each resulting relation is now in 2NF:

• Student: Single-attribute key, automatically 2NF
• Course: Single-attribute key, automatically 2NF
• Enrollment: All non-prime attributes (Grade) fully depend on the complete key

Let's address common misunderstandings that can lead to incorrect 2NF analysis:

Subtle Point: Prime vs Non-Prime

A frequent source of confusion is the focus on NON-PRIME attributes. The 2NF definition specifically says:

"Every non-prime attribute is fully functionally dependent on every candidate key"

Prime attributes are exempt from this requirement. If a prime attribute (one that's part of some candidate key) depends on a proper subset of another candidate key, this is NOT a 2NF violation.

Example:

Relation: R(A, B, C)
Candidate Keys: {A, B} and {C}
Functional Dependencies: C → A

Here, A is a prime attribute (part of candidate key {A, B}). The dependency C → A means A depends on C, which is a proper subset of the key {A, B}. But this does NOT violate 2NF because A is prime.

However, this might violate BCNF, which has stricter requirements. The distinction matters when doing precise normalization analysis.

For those who appreciate formal rigor, let's express the 2NF definition using set notation and first-order logic:

Formal Definition using Set Notation:

Let R be a relation schema with:

• Set of attributes: U = {A₁, A₂, ..., Aₙ}
• Set of functional dependencies: F
• Set of candidate keys: CK = {K₁, K₂, ..., Kₘ}

Define:

• Prime attributes: P = ∪ᵢ Kᵢ (union of all candidate key attributes)
• Non-prime attributes: NP = U \ P (attributes not in any candidate key)

R is in 2NF if and only if:

∀A ∈ NP, ∀K ∈ CK where |K| ≥ 2: (K → A ∈ F⁺) ∧ (∄S ⊂ K : S → A ∈ F⁺)

Where F⁺ denotes the closure of F (all dependencies derivable from F using Armstrong's axioms).

Logical Formulation:

Another way to express 2NF:

R is in 2NF ⟺ R is in 1NF ∧ ¬∃(A, K, S) where:

• A ∈ NP (A is non-prime)
• K ∈ CK (K is a candidate key)
• S ⊂ K ∧ S ≠ ∅ (S is a proper, non-empty subset of K)
• S → A holds (S determines A)

In words: "There is no non-prime attribute determined by a proper non-empty subset of any candidate key."

Closure-Based Test:

To check if a partial dependency X → A exists (where X is a proper subset of some candidate key K):

1. Compute X⁺ (the attribute closure of X under F)
2. If A ∈ X⁺, then X → A holds
3. If X is a proper subset of K and A is non-prime, we have a 2NF violation

We've established a rigorous understanding of what Second Normal Form means. Let's consolidate:

What's Next:

With the formal definition in hand, we'll turn to practical detection. The next page provides systematic techniques for identifying 2NF violations in real schemas—translating the abstract definition into actionable analysis.