Third Normal Form (3NF)

Introduction

Third Normal Form (3NF): highest practical normalization level for most databases. Prerequisite: 2NF. Goal: eliminate transitive dependencies. Non-key attributes must depend directly on primary key, not on other non-key attributes.

Core problem: 2NF table with non-key attribute depending on another non-key attribute. Example: Employee(EmpID, DeptID, DeptName). DeptName depends on DeptID (non-key), not directly on EmpID. Causes redundancy: DeptName repeated for all employees in department.

3NF standard in practice: provides good balance between normalization benefits (consistency, efficiency) and query complexity. Most business databases target 3NF. BCNF stricter but rarely necessary.

"Third Normal Form ensures non-key attributes depend on primary key alone, not transitively through other non-key attributes. Achieves practical database design balancing normalization and usability." -- Database design standards

3NF Definition

Formal Definition

Relation in 3NF if: (1) in 2NF, (2) no non-key attribute transitively dependent on primary key. Every non-key attribute depends directly on primary key, not on other non-key attributes.

Key Concept

Transitivity: A -> B and B -> C implies A -> C. 3NF prevents: primary key -> non-key A -> non-key B. Instead: primary key -> non-key A (direct) and primary key -> non-key B (direct).

Practical Meaning

Each non-key attribute describes primary key entity directly, not indirectly through other attributes. Single source of truth for each fact. Minimizes redundancy.

Scope

3NF covers functional dependencies. Newer forms (4NF, 5NF) address other issues (multi-valued deps, join deps). But 3NF solves majority of design problems.

Transitive Dependencies

Definition

Transitive dependency: non-key attribute A depends on non-key attribute B, which depends on primary key. Chain: PK -> B -> A. Example: EmpID -> DeptID -> DeptName.

Recognition

Pattern: non-key attribute seems to describe another non-key attribute, not primary key directly. Example: DeptName describes DeptID (not Employee). Department ID determines Department Name.

Example

Employee(EmpID, Name, DeptID, DeptName):
EmpID -> DeptID (primary key determines department)
DeptID -> DeptName (department determines department name)
Therefore: EmpID -> DeptName (transitively)

Violates 3NF: DeptName transitively depends on EmpID via DeptID

Why Problematic

Redundancy: DeptName repeated for all employees in department. Update anomaly: change department name requires multiple updates. Deletion anomaly: remove employee loses department info.

Verification

Check each non-key attribute: is its value determined by primary key directly, or through another non-key attribute? If latter: transitive dependency (violates 3NF).

Non-Key Determinants

Definition

Non-key determinant: non-key attribute that functionally determines another attribute. Example: DeptID determines DeptName (DeptID is determinant of DeptName).

Problem

Non-key determinants indicate related attributes that shouldn't be in same table. They belong in separate table with determinant as key.

Example

CourseSchedule(CourseID, InstructorID, InstructorName, Room):
InstructorID -> InstructorName (non-key determinant)

Violates 3NF: InstructorName determined by non-key InstructorID, not primary key

Solution

Identify non-key determinants. Create separate table with determinant as key. Maintain foreign key relationship. Eliminates transitive dependency.

Detection Algorithm

For each non-key attribute: does another non-key attribute determine it? If yes: non-key determinant exists. Indicates decomposition needed.

3NF Violations

Violation Pattern

Table in 2NF but not 3NF: contains transitive dependencies. Non-key attributes depend on other non-key attributes (through primary key).

Real-World Example

Student(StudentID, Name, Major, MajorChair):
StudentID -> Major (primary key determines major)
Major -> MajorChair (major determines chair)
StudentID -> MajorChair (transitively)

Violates 3NF: MajorChair is transitive dependency

Consequences

Redundancy: MajorChair repeated for all students in major. Anomalies: insert new major without students impossible, update major chair requires multiple updates, remove student loses major chair info.

Recognition

Check: can table reorganized into separate logical entities? If yes, likely 3NF violation. Example: Student-Major and Major-Chair should be separate.

Converting to 3NF

Decomposition Steps

1. Identify transitive dependencies. 2. For each: extract attributes into separate table. 3. Determinant becomes primary key in new table. 4. Add foreign key in original table.

Example Conversion

Before (violates 3NF):
Employee(EmpID, Name, DeptID, DeptName, DeptLocation)

Transitive dependencies:
DeptID -> DeptName, DeptLocation

After (3NF):
Employee(EmpID, Name, DeptID fk)
Department(DeptID, DeptName, DeptLocation)

Preservation

Original data preserved: join Employee and Department recovers original table. No information lost. Foreign key ensures consistency.

Verification

After conversion: verify no table has transitive dependencies. Each non-key attribute directly depends on primary key. Check all tables.

Detailed Examples

Example 1: Course Scheduling

Before (violates 3NF):
CourseSchedule(CourseID, Section, InstructorID, InstructorName, Time)
InstructorID -> InstructorName (transitive)

After (3NF):
Schedule(CourseID, Section, InstructorID fk, Time)
Instructor(InstructorID, InstructorName)

Example 2: Order Processing

Before (violates 3NF):
Order(OrderID, CustomerID, CustomerCity, CustomerCountry)
CustomerID -> CustomerCity, CustomerCountry (transitive)

After (3NF):
Order(OrderID, CustomerID fk)
Customer(CustomerID, City, Country)

Example 3: Already 3NF

Employee(EmpID, Name, Salary, DeptID):
All non-key attributes directly depend on EmpID, no transitive.
Already in 3NF. No decomposition needed.

Fixing Anomalies

Insertion Anomaly

Before: add new department without employees impossible (primary key requires employee). After: insert Department independently. Can exist without employees.

Update Anomaly

Before: change department name requires updating all employees in department. After: single update in Department table. Efficient, safe.

Deletion Anomaly

Before: delete last employee loses department information. After: delete Employee row, Department remains. No information loss.

Verification

After conversion: verify all anomalies resolved. Can insert partial entities, update safely, delete without data loss.

3NF vs. BCNF

Boyce-Codd Normal Form (BCNF)

Stricter than 3NF. Requirement: every determinant must be candidate key. Eliminates even more anomalies. Rarely needed in practice.

Difference

3NF allows: if A -> B where A is non-key and B is key. BCNF disallows: every determinant must be key. 3NF easier to achieve, BCNF rarely justifiable.

Practical Choice

3NF standard: balances normalization and complexity. BCNF for complex multi-candidate-key scenarios. Most businesses stop at 3NF.

Trade-off

BCNF guarantees fewer anomalies but more tables/joins. 3NF practical sweet spot. Database design typically aims for 3NF.

Denormalization Considerations

When to Denormalize

Performance critical: denormalize strategically. Add redundancy (violate 3NF) for query speed. Example: cache calculated values, duplicate frequently-joined attributes.

Documented Denormalization

If denormalize: document decision, rationale, trade-offs. Monitor: ensure redundancy maintained correctly. Careful: avoid accidental inconsistency.

Common Denormalizations

Caching: store computed values. Pre-joined: duplicate frequently-joined columns. Materialized views: store query results. Summary tables: aggregate data.

Cost-Benefit

Benefit: query performance. Cost: storage, maintenance complexity, consistency risk. Only denormalize if performance metrics justify.

Performance and Practicality

Join Overhead

3NF requires joins to reconstruct data. Multiple tables, more joins = slower. But modern databases optimize: joins usually fast.

Index Optimization

Foreign keys can be indexed. Joins then efficient. Proper indexing critical for 3NF performance. Index design guides join optimization.

Query Complexity

3NF may require complex queries with many joins. ORMs hide complexity. Materialized views precalculate. Trade-offs: complexity vs. consistency.

Development Impact

3NF schema easier to modify (isolated changes). Less denormalization drift. Lower maintenance cost long-term. Initial development slightly slower.

Industry Practice

Standard: design 3NF, then optimize. Measure: if joins slow, add indexes. Only denormalize if necessary. 3NF provides good foundation.

Real-World Applications

Business Databases

ERP, CRM, accounting systems: typically 3NF. Ensures consistency, supports complex queries, minimizes redundancy. Standard enterprise architecture.

Data Warehousing

Operational: 3NF. OLTP systems maintain normalized schemas. Analytical: sometimes denormalized (OLAP cubes) for performance.

Legacy System Modernization

Old systems may violate norms. Modernization: convert to 3NF. Improves maintainability, reduces bugs, enables scalability.

Schema Design Tools

Tools check 3NF compliance. Suggest decompositions. Validate designs. Guide designers toward normalized schemas.

References

• Codd, E. F. "Further Normalization of the Data Base Relational Model." IBM Research Report RJ909, 1971.
• Boyce, R. F., and Codd, E. F. "Further Normalization of the Database Relational Model." Proceedings of SIGFIDET Workshop, 1974.
• Elmasri, R., and Navathe, S. B. "Fundamentals of Database Systems." Pearson, 7th edition, 2016.
• Date, C. J. "Database in Depth: Relational Theory for Practitioners." O'Reilly Media, 2005.
• Kent, W. "A Simple Guide to Five Normal Forms in Relational Database Theory." Communications of the ACM, vol. 26, no. 2, 1983, pp. 120-125.