Third Normal Form 3NF Explained with Examples

Third Normal Form is where most real-world production database schemas land — normalized enough to eliminate the redundancy and anomaly risks covered so far, without pushing into the more theoretical, less commonly needed territory of BCNF and beyond. 3NF's job is eliminating transitive dependencies: non-key columns that depend on other non-key columns instead of depending directly on the primary key.

Key Definitions

• Third Normal Form (3NF): A table satisfies 3NF if it is already in 2NF, and no non-key column depends transitively on the primary key through another non-key column.
• Transitive dependency: A dependency chain where a non-key column depends on another non-key column, which in turn depends on the primary key, rather than the first column depending directly on the primary key.

What You'll Learn

• Define Third Normal Form and its transitive dependency requirement.
• Identify a transitive dependency violating 3NF in a sample table.
• Decompose a 3NF-violating table into properly structured tables.
• Understand why 3NF is the practical target for most production schemas.

Detailed Explanation

Recall the transitive dependency example from Lesson 8.6: a doctors table with columns doctor_id (primary key), doctor_name, department_id, and department_location. Here, doctor_id → department_id holds (each doctor belongs to one department), and department_id → department_location also holds (each department has one location). This creates a transitive chain: doctor_id → department_id → department_location. In other words, department_location doesn't depend directly on doctor_id — it depends on department_id, which itself depends on doctor_id. This indirect dependency is precisely what 3NF forbids.

The symptom, once again, is redundancy: every doctor in the same department repeats that department's location on their own row. If a department relocates, every single doctor row referencing that department must be updated — the classic update anomaly from Lesson 8.5, now diagnosed with precision using the language of transitive dependency.

The fix is familiar: extract the transitively-dependent column into its own table. department_location moves into a departments table, keyed by department_id, and the doctors table keeps only department_id as a foreign key reference, removing department_location entirely. Now department_location depends directly and only on department_id, its actual determinant, with no transitive chain involved. This exact decomposition is why the departments and doctors tables have been kept separate throughout this entire course — they exist as separate tables specifically because keeping them merged would violate 3NF.

Visual Summary

A chain diagram showing the violation: [doctor_id] --→--> [department_id] --→--> [department_location], with a red bracket labeled 'Transitive dependency — 3NF violation' spanning the full chain. Below, the fix: two separate boxes [doctors: doctor_id, doctor_name, department_id (FK)] and [departments: department_id, department_location], connected by a foreign key arrow, both with green checkmarks.

Quick Reference

Table State	Dependency Chain	3NF Status
Merged doctors+department_location table	doctor_id → department_id → department_location	Violates 3NF (transitive dependency)
Separated doctors and departments tables	doctor_id → department_id (FK); department_id → department_location	Satisfies 3NF (no transitive chain)

SQL Example

-- VIOLATES 3NF: department_location is transitively dependent on doctor_id
-- doctor_id | doctor_name | department_id | department_location
-- 101       | Dr. Verma   | 1              | Building A
-- 104       | Dr. Khan    | 1              | Building A   <-- location repeated!

-- 3NF-COMPLIANT fix: extract the transitively-dependent column
CREATE TABLE departments (
  department_id       INT PRIMARY KEY AUTO_INCREMENT,
  department_location  VARCHAR(100)
);

CREATE TABLE doctors (
  doctor_id     INT PRIMARY KEY AUTO_INCREMENT,
  doctor_name   VARCHAR(100) NOT NULL,
  department_id INT,             -- FK only; location no longer stored here
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

In the violating table, 'Building A' is repeated for both Dr. Verma and Dr. Khan since they share a department — a direct symptom of department_location being transitively dependent on doctor_id through department_id rather than being determined by doctor_id itself. After the fix, department_location exists exactly once per department in the departments table, and doctors simply references department_id, achieving 3NF by eliminating the transitive chain entirely.

Real-World Examples

• Most production relational schemas at companies handling transactional data — e-commerce, banking, healthcare — are explicitly designed to satisfy 3NF as their baseline standard.
• Database design courses and certifications treat 3NF as the practical, expected level of normalization for real-world OLTP (transactional) systems.
• Schema review processes at many engineering organizations specifically flag transitive dependencies as a design smell requiring correction before a table is approved for production.

Common Mistakes to Avoid

• Confusing a transitive dependency (non-key depends on non-key) with a partial dependency (non-key depends on part of a composite key) — they are different violations addressed by different normal forms.
• Stopping normalization analysis at 2NF without checking for transitive dependencies as well.
• Not recognizing that keeping related entities like Doctor and Department in separate tables is itself the practical result of applying 3NF.

Interview Questions

Q1. What does Third Normal Form require?

3NF requires a table to already satisfy 2NF, and additionally requires that no non-key column depend transitively on the primary key through another non-key column — every non-key column must depend directly on the primary key.

Q2. What is a transitive dependency, and how does 3NF eliminate it?

A transitive dependency is a chain where a non-key column depends on another non-key column, which in turn depends on the primary key. 3NF eliminates this by moving the transitively-dependent column into its own table, keyed by the intermediate column it actually depends on.

Q3. Why is 3NF considered the practical target for most production databases?

3NF eliminates the most impactful sources of redundancy and anomalies (partial and transitive dependencies) while remaining straightforward to design and query, making it a strong balance between data integrity and practical usability for most transactional systems.

Practice MCQs

1. What does 3NF specifically eliminate?

1. Partial dependencies
2. Transitive dependencies
3. Multi-valued attributes
4. Composite keys

Answer: B. Transitive dependencies

Explanation: Third Normal Form's defining rule is the elimination of transitive dependencies, where a non-key column depends on another non-key column rather than directly on the primary key.

2. In the dependency chain doctor_id → department_id → department_location, what is the 3NF violation?

1. doctor_id doesn't determine department_id
2. department_location depends transitively on doctor_id through department_id
3. department_id is not unique
4. There is no violation

Answer: B. department_location depends transitively on doctor_id through department_id

Explanation: This chain is the textbook example of a transitive dependency, which 3NF requires to be eliminated by relocating department_location to its own table.

Quick Revision Points

• 3NF requires no transitive dependencies: every non-key column must depend directly on the primary key.
• 3NF is the industry-standard practical target for most production relational schemas.
• The distinction between 2NF (partial dependency) and 3NF (transitive dependency) is a frequently tested exam comparison.

Conclusion

• 3NF eliminates transitive dependencies, where a non-key column depends on another non-key column.
• The fix is relocating the transitively-dependent column into its own table, keyed by its actual determinant.
• 3NF is the practical normalization target most real-world production databases aim for.

Third Normal Form eliminates transitive dependencies — cases where a non-key column depends on another non-key column rather than directly on the primary key, such as department_location depending on doctor_id only indirectly, through department_id. The fix relocates the transitively-dependent column into its own properly-keyed table, exactly the reasoning behind keeping doctors and departments as separate tables throughout this course. 3NF is widely regarded as the practical, industry-standard target for most production relational database schemas, balancing strong data integrity with manageable query complexity.