Third normal form (3NF) is a relational design level that reduces redundancy and protects data integrity. A table reaches 3NF when it has a primary key, no multivalued columns, and non-key columns depend directly on that key rather than on another non-key column. section 6.2 section 6.8
Check a table against these practical 3NF rules:
- Confirm that the table has a primary key. section 6.8
- Remove multivalued columns. section 6.8
- Ensure every non-key column depends directly on the key and on nothing else. section 6.7
- Separate columns involved in a transitive dependency into related tables, using a foreign key when appropriate. section 6.7
Employee and department
Suppose employee_id identifies an employee, employee_id determines department_id, and department_id determines department_name. Because department_name depends on employee_id through another non-key column, the employee table has a transitive dependency. Move department_id and department_name into a department table keyed by department_id, and keep department_id as a foreign key in the employee table. section 6.7
The normal forms are hierarchical: 3NF builds on earlier forms, while BCNF includes the characteristics of 1NF through 3NF. A fully normalized design commonly uses BCNF as the practical target. section 6.2
