Second Normal Form (2NF)
What you'll learn: How to eliminate partial dependencies from tables with composite primary keys to reach Second Normal Form.
The Core Idea
Second Normal Form (2NF) builds directly on First Normal Form. A table is in 2NF when it's already in 1NF and every non-key column depends on the entire primary key—not just part of it. This issue only arises when you have a composite primary key (a primary key made of multiple columns).
Understanding Partial Dependencies
Imagine a table tracking student course enrollments:
Enrollments (StudentID, CourseID, StudentName, CourseName, Grade)
- Primary Key: (StudentID, CourseID)
Here's the problem: StudentName depends only on StudentID, not on the full composite key. Similarly, CourseName depends only on CourseID. These are partial dependencies—non-key columns depending on just part of the primary key.
This creates redundancy: if a student enrolls in five courses, their name is stored five times. Update anomalies follow: change a student's name once, and you must update all their enrollments.
Moving to 2NF
To fix this, split the table so each non-key column depends on the complete primary key:
Students (StudentID, StudentName)
Courses (CourseID, CourseName)
Enrollments (StudentID, CourseID, Grade)
Now Grade depends on both StudentID and CourseID (the full key in Enrollments), while student and course details live in their own tables with simple primary keys.
Analogy
Think of a filing system: instead of writing your full address on every document you file under different categories, you write it once in your personal profile and just reference your ID on each document.
Key Takeaway: Second Normal Form eliminates partial dependencies by ensuring every non-key column depends on the entire composite primary key, reducing redundancy and update anomalies.