Third normal form
Third normal form definition
Third normal form (3NF) refers to a principle in relational database design that is used to eliminate redundancy and undesirable characteristics like insertion, update, and deletion anomalies.
See also: database replication, digital data
First, second, and third normal forms
- First normal form (1NF). Each column should have atomic (indivisible) values and they should be of the same type. Columns must have unique names, while the order of stored data is irrelevant.
- Second normal form (2NF). It is about removing partial dependencies in tables with composite primary keys. In other words, all non-primary key attributes are fully functionally dependent on the primary key.
- Third normal form (3NF). It is about refining the structure of the data to remove dependencies that aren’t directly related to the primary key, ensuring clarity, reducing redundancy, and enhancing the integrity and flexibility of the database.
3NF example
In a table with columns StudentID, Course, Instructor, and InstructorOffice, the first two together can be a composite primary key since a student can enroll in multiple courses and each course can have multiple students.
But if each instructor has a specific office, InstructorOffice and Instructor is a transitive dependency, because the InstructorOffice does not depend on the primary key (StudentID and Course) directly, but rather depends on the Instructor.
In 3NF, this table would be split where one contains the StudentID, Course, and Instructor columns, and the other links Instructor with InstructorOffice.