Normalize a database to third normal form (3NF) by first achieving 1NF, then 2NF, and finally removing all transitive dependencies. In 3NF, every non-key attribute depends on the primary key, the whole primary key, and nothing except the primary key.
The Normalization Process
Normalization organizes data into related tables to reduce redundancy and prevent insertion, update, and deletion anomalies.
| Normal form | Requirement | Action required |
|---|---|---|
| First normal form (1NF) | Each field contains one atomic value, and records can be uniquely identified. | Remove repeating groups and choose a primary key. |
| Second normal form (2NF) | The table is in 1NF, and every non-key attribute depends on the whole primary key. | Remove partial dependencies, which can occur with a composite primary key. |
| Third normal form (3NF) | The table is in 2NF, and non-key attributes do not depend on other non-key attributes. | Remove transitive dependencies. |
Consider this relation:
ENROLMENT(StudentID, CourseID, StudentName, CourseName, InstructorID, InstructorName, Grade)
The composite primary key is (StudentID, CourseID).
- To reach 2NF, remove attributes that depend on only part of this key.
StudentNamedepends only onStudentID, while course details depend only onCourseID. - Create
STUDENT(StudentID, StudentName),COURSE(CourseID, CourseName, InstructorID, InstructorName), andENROLMENT(StudentID, CourseID, Grade). - To reach 3NF, remove the transitive dependency in
COURSE:CourseIDdeterminesInstructorID, andInstructorIDdeterminesInstructorName. - Create
INSTRUCTOR(InstructorID, InstructorName)and retainCOURSE(CourseID, CourseName, InstructorID).InstructorIDbecomes a foreign key inCOURSE.
A common misconception is that 3NF means placing every attribute in a separate table. Instead, attributes should remain together when they depend directly on the same primary key.
Exam Technique
For an IB Computer Science A3.2 response, identify the primary key, state each functional dependency, name the dependency being removed, and show the resulting tables with their primary and foreign keys.
