The Third Normal Form (3NF) is a standard for database design that ensures data integrity by removing transitive dependencies. Its development was part of the foundational era of the relational model.
Comprehensive Timeline of 3NF and Normalization
- 1970 — The Birth of Relational Theory: Dr. E.F. Codd, a researcher at IBM, published his seminal paper, “A Relational Model of Data for Large Shared Data Banks.” This introduced the concepts of First Normal Form (1NF) and the initial framework for normalization.
- 1971 — Official Definition of 3NF: Codd formally defined Third Normal Form in his paper “Further Normalization of the Data Base Relational Model.” He also refined Second Normal Form (2NF) in this same period.
- 1971 (August) — Technical Specification: The specific requirements for 3NF were further detailed in the IBM Research Report RJ909, solidifying the mathematical rules for removing transitive functional dependencies.
- 1974 — Extension to Boyce-Codd Normal Form (BCNF): Together with Raymond F. Boyce, Codd introduced BCNF. Often considered a stronger version of 3NF, it addresses certain anomalies that 3NF might still permit.
- 1977–1979 — Higher Normal Forms: Ronald Fagin introduced Fourth Normal Form (4NF) in 1977 and Fifth Normal Form (5NF) in 1979 to address multi-valued and join dependencies, respectively.
- 1980s–Present — Industry Standard: 3NF became the most commonly used level of normalization for Relational Database Management Systems (RDBMS) because it strikes an ideal balance between reducing redundancy and maintaining query performance.
- 2002 — 6NF Definition: C.J. Date, Hugh Darwen, and Nikos Lorentzos defined Sixth Normal Form (6NF) specifically for temporal databases.
3NF Requirement Summary
To reach 3NF, a table must follow a cumulative progression:
- 1NF: Each cell must contain atomic values, and there should be no repeating groups.
- 2NF: The table must be in 1NF, and every non-key attribute must depend on the entire primary key (no partial dependencies).
- 3NF: The table must be in 2NF, and every non-key attribute must depend only on the primary key (no transitive dependencies).
To reach Third Normal Form (3NF), a database table must first satisfy the requirements of 1NF and 2NF. The primary goal of 3NF is to ensure that all non-key columns depend only on the primary key, effectively eliminating “transitive dependencies”.
Step-by-Step Process
- Verify Second Normal Form (2NF)
- Ensure the table has a primary key.
- Confirm all non-key attributes depend on the entire primary key (no partial dependencies).
- Identify Transitive Dependencies
- Look for “hidden” relationships where a non-prime attribute depends on another non-prime attribute.
- Logic: If Attribute A (Primary Key) → Attribute B, and Attribute B → Attribute C, then Attribute C has a transitive dependency on the Primary Key through B.
- Remove the Dependent Attributes
- Select the attributes that do not directly depend on the primary key.
- Move these attributes into a new, separate table.
- Establish Relationships
- In the original table, keep the attribute that served as the “determinant” (the non-key attribute that others depended on) to act as a foreign key.
- In the new table, set that same attribute as the primary key.
Practical Example
Consider a Student table with: StudentID (PK), StudentName, ZipCode, and City.
- Problem:
Citydepends onZipCode, andZipCodedepends onStudentID. This is a transitive dependency (StudentID→ZipCode→City). - 3NF Solution:
- Table 1 (Students):
StudentID(PK),StudentName,ZipCode(FK). - Table 2 (Locations):
ZipCode(PK),City.
- Table 1 (Students):
By following these steps, you eliminate data redundancy and prevent update anomalies where changing a city name would otherwise require updating every student record in that zip code.
Third Normal Form 3NF Development Timeline and Example
