Second Normal Form: 2NF
A table is in Second Normal Form if: (1) it is already in 1NF, and (2) it has no partial dependency.
What is a partial dependency?
A partial dependency occurs when a non-key attribute depends on only part of a composite primary key.
For a given table (X, Y, Z, W) →
the primary key is composite → (X, Y)
Partial dependency occurs if,
That is, the non-key attributes Z and W are partly dependent on / uniquely identified by only part of the primary key — either X or Y.
the primary key is composite → (X, Y)
Partial dependency occurs if,
X → Z orY → WThat is, the non-key attributes Z and W are partly dependent on / uniquely identified by only part of the primary key — either X or Y.
Corollary worth a mark: if the primary key is a single attribute, a partial dependency is impossible — so any 1NF table with a single-attribute key is automatically in 2NF.
Example
| Student_ID | Course_ID | Student_Name | Course_Title | Grade |
|---|---|---|---|---|
| 101 | CSE101 | Rahim | Database | A |
| 101 | CSE102 | Rahim | Programming | B |
| 102 | CSE101 | Karim | Database | A |
Primary key for this table is: (Student_ID, Course_ID)
Functional dependencies are:
Student_ID → Student_NameCourse_ID → Course_Title(Student_ID, Course_ID) → Grade Here,
- Student_Name depends only on Student_ID, not on the full primary key.
- Course_Title depends only on Course_ID, not on the full primary key.
Solution — break the table based on functional dependency
Student Table
| Student_ID | Student_Name |
|---|---|
| 101 | Rahim |
| 102 | Karim |
PK = Student_ID
Course Table
| Course_ID | Course_Title |
|---|---|
| CSE101 | Database |
| CSE102 | Programming |
PK = Course_ID
Enrollment Table
| Student_ID | Course_ID | Grade |
|---|---|---|
| 101 | CSE101 | A |
| 101 | CSE102 | B |
| 102 | CSE101 | A |
PK = (Student_ID, Course_ID)
Now all non-key attributes fully depend on the whole primary key, so the design is in 2NF. Notice the mechanical rule: each FD becomes its own table, with the determinant as that table’s primary key.
The method, step by step
1. Identify the primary key — is it composite?
if not, the table is already in 2NF
2. List every functional dependency
3. Mark any FD whose determinant is only PART of the key
these are the partial dependencies
4. Move each such FD into its own table (determinant = PK)
5. What remains keeps the composite key + the fully dependent attributes
Next: Third Normal Form →