Third Normal Form (3NF) & Transitive Dependencies0%

Denormalization Strategies & Performance Trade-offs

Third Normal Form (3NF) & Transitive Dependencies

Intermediate12 min read•Updated: 2026-09-12

Third Normal Form (3NF) & Transitive Dependencies

A table in Second Normal Form (2NF) has eliminated partial dependencies. However, data redundancy and update anomalies can still persist if non-key columns depend on other non-key columns. The Third Normal Form (3NF) eliminates these Transitive Dependencies.

As the famous computer science proverb states:

"Every non-key attribute must depend on the key, the whole key, and nothing but the key, so help me Codd."

1. What is a Transitive Dependency?

A Transitive Dependency occurs when attribute A determines attribute B, and attribute B determines attribute C:

1   A --------> B --------> C
2   (Primary Key)   (Non-Key Col)   (Non-Key Col)

Because A -> B and B -> C, attribute C is transitively dependent on A through B. Non-key column C does not depend directly on the primary key!

2. The Rules of Third Normal Form (3NF)

A table is in Third Normal Form (3NF) if and only if:

1
It is already in Second Normal Form (2NF).
2
It contains NO Transitive Dependencies: No non-key column can depend on another non-key column!

3. The 3NF Violation: Transitive Column Chaining

Consider an employee table with a single primary key (emp_id):

 TABLE: employees_flawed (In 2NF, but VIOLATES 3NF!)
 PRIMARY KEY: emp_id
 
 +--------+---------------+---------+--------------------+---------------------+
 | emp_id | full_name     | dept_id | dept_name          | dept_building       |
 +--------+---------------+---------+--------------------+---------------------+
 |    101 | Aarav Sharma  |       1 | Engineering        | Tech Tower 4        |
 |    102 | Diya Patel    |       1 | Engineering        | Tech Tower 4        |
 |    103 | Rohan Verma   |       2 | Human Resources    | Corporate Block A   |
 +--------+---------------+---------+--------------------+---------------------+

Analyzing the Dependencies:

1
emp_id -> dept_id: Valid! Knowing an employee determines their department ID.
2
dept_id -> dept_name, dept_building: Valid! Knowing a department ID determines its name and building.
3
Therefore: emp_id -> dept_name is a Transitive Dependency!

Neither dept_name nor dept_building are candidate keys. They depend directly on dept_id, which is a non-key column in this table!

Anomalies Caused by this 3NF Violation:

Update Anomaly: If Engineering moves to Tower 9, you must update every single employee row in Engineering!
Insertion Anomaly: You cannot create a new department unless you hire at least one employee to hold the row!
Deletion Anomaly: Deleting the only HR employee wipes out the existence of the HR department and its building address!

4. The 3NF Solution: Decomposing into Master & Detail Tables

To satisfy 3NF, remove the transitively dependent attributes and place them in their own independent master table:

1-- Table 1: Departments (Independent Master Entity)
2CREATE TABLE departments (
3    dept_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    dept_name VARCHAR(100) NOT NULL UNIQUE,
5    dept_building VARCHAR(100) NOT NULL
6) ENGINE = InnoDB;
7 
8-- Table 2: Employees (Now in strict 3NF!)
9CREATE TABLE employees (
10    emp_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11    full_name VARCHAR(100) NOT NULL,
12    dept_id INT UNSIGNED NOT NULL, -- Foreign Key
13    
14    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
15) ENGINE = InnoDB;

Now:

Engineering's building is stored in exactly one row in departments.
Updating it requires one row update.
Departments can exist with zero employees.

5. Best Practices & Common Pitfalls

Calculated / Derived Columns: Storing calculated columns (e.g., unit_price, quantity, and total_price = unit_price * quantity) technically violates 3NF because total_price depends on other non-key columns! Calculate totals dynamically in queries, or use MySQL 8.0 Generated Columns (GENERATED ALWAYS AS (unit_price * quantity) STORED).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does Third Normal Form (3NF) eliminate from database tables?

Question 2

In an orders table with order_id (PK), customer_id, and customer_city, why does customer_city violate 3NF?

Question 3

Which normal form is colloquially summarized as: "Every attribute must depend on the key, the whole key, and nothing but the key"?

Question 4

Why does storing a total_cost column alongside quantity and unit_price technically violate 3NF?

Question 5

What is the primary benefit achieved by eliminating transitive dependencies?

Next Lesson

Denormalization Strategies & Performance Trade-offs

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Second Normal Form (2NF) & Functional Dependencies	Denormalization Strategies & Performance Trade-offs

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Denormalization Strategies & Performance Trade-offs

Third Normal Form (3NF) & Transitive Dependencies

Intermediate12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Third Normal Form (3NF) & Transitive Dependencies

As the famous computer science proverb states:

"Every non-key attribute must depend on the key, the whole key, and nothing but the key, so help me Codd."

1. What is a Transitive Dependency?

A Transitive Dependency occurs when attribute A determines attribute B, and attribute B determines attribute C:

1   A --------> B --------> C
2   (Primary Key)   (Non-Key Col)   (Non-Key Col)

Because A -> B and B -> C, attribute C is transitively dependent on A through B. Non-key column C does not depend directly on the primary key!

2. The Rules of Third Normal Form (3NF)

A table is in Third Normal Form (3NF) if and only if:

1
It is already in Second Normal Form (2NF).
2
It contains NO Transitive Dependencies: No non-key column can depend on another non-key column!

3. The 3NF Violation: Transitive Column Chaining

Consider an employee table with a single primary key (emp_id):

 TABLE: employees_flawed (In 2NF, but VIOLATES 3NF!)
 PRIMARY KEY: emp_id
 
 +--------+---------------+---------+--------------------+---------------------+
 | emp_id | full_name     | dept_id | dept_name          | dept_building       |
 +--------+---------------+---------+--------------------+---------------------+
 |    101 | Aarav Sharma  |       1 | Engineering        | Tech Tower 4        |
 |    102 | Diya Patel    |       1 | Engineering        | Tech Tower 4        |
 |    103 | Rohan Verma   |       2 | Human Resources    | Corporate Block A   |
 +--------+---------------+---------+--------------------+---------------------+

Analyzing the Dependencies:

1
emp_id -> dept_id: Valid! Knowing an employee determines their department ID.
2
dept_id -> dept_name, dept_building: Valid! Knowing a department ID determines its name and building.
3
Therefore: emp_id -> dept_name is a Transitive Dependency!

Neither dept_name nor dept_building are candidate keys. They depend directly on dept_id, which is a non-key column in this table!

Anomalies Caused by this 3NF Violation:

Update Anomaly: If Engineering moves to Tower 9, you must update every single employee row in Engineering!
Insertion Anomaly: You cannot create a new department unless you hire at least one employee to hold the row!
Deletion Anomaly: Deleting the only HR employee wipes out the existence of the HR department and its building address!

4. The 3NF Solution: Decomposing into Master & Detail Tables

To satisfy 3NF, remove the transitively dependent attributes and place them in their own independent master table:

1-- Table 1: Departments (Independent Master Entity)
2CREATE TABLE departments (
3    dept_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
4    dept_name VARCHAR(100) NOT NULL UNIQUE,
5    dept_building VARCHAR(100) NOT NULL
6) ENGINE = InnoDB;
7 
8-- Table 2: Employees (Now in strict 3NF!)
9CREATE TABLE employees (
10    emp_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
11    full_name VARCHAR(100) NOT NULL,
12    dept_id INT UNSIGNED NOT NULL, -- Foreign Key
13    
14    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
15) ENGINE = InnoDB;

Now:

Engineering's building is stored in exactly one row in departments.
Updating it requires one row update.
Departments can exist with zero employees.

5. Best Practices & Common Pitfalls

Calculated / Derived Columns: Storing calculated columns (e.g., unit_price, quantity, and total_price = unit_price * quantity) technically violates 3NF because total_price depends on other non-key columns! Calculate totals dynamically in queries, or use MySQL 8.0 Generated Columns (GENERATED ALWAYS AS (unit_price * quantity) STORED).

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does Third Normal Form (3NF) eliminate from database tables?

Question 2

In an orders table with order_id (PK), customer_id, and customer_city, why does customer_city violate 3NF?

Question 3

Which normal form is colloquially summarized as: "Every attribute must depend on the key, the whole key, and nothing but the key"?

Question 4

Why does storing a total_cost column alongside quantity and unit_price technically violate 3NF?

Question 5

What is the primary benefit achieved by eliminating transitive dependencies?

Next Lesson

Denormalization Strategies & Performance Trade-offs

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Second Normal Form (2NF) & Functional Dependencies	Denormalization Strategies & Performance Trade-offs

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next