This lecture covers Second Normal Form (2NF). Once a table is in First Normal Form (1NF), the next step is to check if it satisfies Second Normal Form (2NF). The rule for 2NF is:
A table is in 2NF if it is in 1NF and every non-key attribute is fully functionally dependent on the entire primary key.
That sounds complicated, but the concept is simpler than the definition. It mainly applies to tables that have a composite primary key (a primary key made up of two or more columns). It means that no non-key column should depend on only *part* of the composite primary key.
An Example of a 2NF Violation
Let’s imagine a table that tracks which students are enrolled in which courses, and also stores the location of the course.
Let’s say the primary key for this table is the combination of `StudentID` and `CourseID`.
Enrollment Table (Violates 2NF)
| StudentID | CourseID | StudentName | CourseLocation |
|---|---|---|---|
| 101 | CS101 | Alice | Room 1A |
| 101 | ENGL202 | Alice | Room 3B |
| 102 | CS101 | Bob | Room 1A |
Let’s analyze the dependencies:
StudentNamedepends only on `StudentID`. It has nothing to do with the course. This is a partial dependency because `StudentName` depends on only a part of the composite primary key (`StudentID`, `CourseID`).CourseLocationdepends only on `CourseID`. It has nothing to do with the student. This is also a partial dependency.
This design leads to data redundancy. Alice’s name is repeated for every course she takes. The location of CS101 is repeated for every student enrolled in it. If the location of a course changes, you have to update it in many places.
Making it 2NF
To fix this, we must remove the partial dependencies by splitting the table into smaller tables, where each table has a single purpose.
Students Table
| StudentID (PK) | StudentName |
|---|---|
| 101 | Alice |
| 102 | Bob |
Courses Table
| CourseID (PK) | CourseLocation |
|---|---|
| CS101 | Room 1A |
| ENGL202 | Room 3B |
Enrollment Table
| StudentID (FK) | CourseID (FK) |
|---|---|
| 101 | CS101 |
| 101 | ENGL202 |
| 102 | CS101 |
Now, the `StudentName` is stored only once per student. The `CourseLocation` is stored only once per course. The `Enrollment` table exists only to link students and courses. There are no more partial dependencies, and our design is in 2NF.
If a table is in 1NF and its primary key is a single column (not composite), then the table is automatically in 2NF.
In the Next Lecture…
We will cover the last of the common normal forms: Third Normal Form (3NF). 3NF deals with another type of problematic dependency called a transitive dependency.
