Third Normal Form (3NF): Definition and Database Example
Third normal form (3NF) is a normalization rule for organizing facts according to their functional dependencies. In a common table with one candidate key, it removes dependencies in which one non-key attribute determines another. For example, an employee table should not repeat the department name when that name is determined by a department identifier.
Sound relational database design requires adherence to specific rules that ensure data integrity and reduce redundancy. Among the key techniques used to achieve this is normalization, a process that systematically organizes data within relational tables to eliminate anomalies and preserve logical consistency.
Understanding the Goal of Normalization
When designing a database for an enterprise, the goal is to create a structure that accurately represents:
- The entities and attributes that describe the data,
- The relationships among these entities, and
- The constraints that maintain data validity within the enterprise context.
Normalization is achieved by examining functional dependencies between attributes—how one attribute determines another. Through successive stages known as normal forms, redundant data and undesirable dependencies are systematically removed. The Third Normal Form (3NF) is one of the most critical milestones in this process.
Learning Objectives
After completing this module, you should be able to:
- Explain the requirements for achieving Third Normal Form (3NF).
- Identify and eliminate transitive dependencies.
- Normalize a relation to 3NF.
- Describe Codd’s 12 rules for a fully relational database system.
- Relate Codd’s rules to normalization principles.
- Recognize when normalization beyond 3NF (e.g., BCNF) is necessary.
- Define and evaluate the role of denormalization in performance optimization.
What Is a 3NF Database?
A database design is described as being in 3NF when its relations satisfy the third normal form rule. For every nontrivial functional dependency X → A, either X is a superkey or A is a prime attribute (part of at least one candidate key). A superkey is a set of attributes that uniquely identifies a row.
For the common case with a single candidate key, a practical check is: first satisfy 2NF, then remove transitive dependencies between non-key attributes. Use all candidate keys when assessing a more complex relation; checking only the chosen primary key is not sufficient.
Third Normal Form Example: Before and After
Assume EmployeeID is the only candidate key, each employee belongs to one department, and DepartmentID determines DepartmentName.
| EmployeeID (PK) | EmployeeName | DepartmentID | DepartmentName |
|---|---|---|---|
| 101 | Ava | 10 | Sales |
| 102 | Ben | 10 | Sales |
EmployeeID → DepartmentID → DepartmentName is the transitive dependency. Renaming Sales requires updating every employee row for department 10. Removing the last employee would also remove the stored department name.
After: separate the department facts. Retain the employee's department identifier as a foreign key.
| EmployeeID (PK) | EmployeeName | DepartmentID (FK) |
|---|---|---|
| 101 | Ava | 10 |
| 102 | Ben | 10 |
| DepartmentID (PK) | DepartmentName |
|---|---|
| 10 | Sales |
The department name is now stored once per department. Joining the tables on DepartmentID reconstructs the original information. Review the limitations of second normal form to understand why a table can satisfy 2NF and still have this problem.
Why 3NF Matters
- Eliminates Anomalies: Separating department facts removes the insertion, update, and deletion anomalies caused by repeating those facts in employee rows.
- Improves Maintainability: Storing a department name in one place makes a department rename easier to maintain. Query performance still depends on indexes, joins, workload, and the DBMS.
- Preserves Data Integrity: Appropriate keys and foreign key constraints help keep the separated facts and their references consistent.
While 3NF is a strong design goal, not every database must be fully normalized. Some applications—especially those requiring high-speed analytical queries—may selectively denormalize data to improve performance.
Example: Third Normal Form in Practice
This student database separates teacher, course, student, subject, and locality facts. PK marks a primary key and FK marks a foreign key. The 1:N labels show maximum cardinality; they do not specify whether participation is optional or mandatory.
- Teacher and Course: One teacher can teach many courses; each course references a teacher.
- Course and Student: One course can have many students. This simplified model stores one course reference per student.
- Subject Enrollment: The composite primary key
(StudentID, SubjectID)permits one row per student-subject pair. Both columns are also foreign keys, resolving the many-to-many relationship between students and subjects. - Address Code: Students and teachers reference shared locality records through
AddressCodeID, while their street addresses remain in their own tables. The model does not assume that a ZIP code uniquely identifies a city or suburb.
Separate entities clarify which table owns each fact. Validate all candidate keys and functional dependencies against the business rules: adding identifiers or drawing an ER diagram does not by itself prove that a design is in 3NF.
Beyond Third Normal Form
Although 3NF eliminates most redundancy and transitive dependencies, some databases benefit from further normalization. The Boyce-Codd Normal Form (BCNF) strengthens 3NF by addressing situations where multiple candidate keys can still lead to anomalies. Understanding when to stop normalizing—and when to denormalize—is an essential skill for database architects.
Summary
Third normal form organizes relations around their functional dependencies. In the employee example, moving department facts into a Department table removes repeated department names and the associated update anomalies. Check candidate keys and business rules before decomposing a relation, and evaluate performance separately from normalization.