Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 46

Eliminating Transitive Dependencies with 3NF

Recommended resources

Hello! Welcome back to our course on database design.

In the last lesson, we focused on Second Normal Form (2NF), learning how to identify and eliminate partial dependencies. We saw that by decomposing tables, we could resolve issues where a non-key attribute depended on only part of a composite primary key.

We ended by looking at the Courses2 table from our normalization example:

course department lecturer
Relational Databases Computer Science Jeremy Brown
Cloud Databases Computer Science Jeremy Brown
Algorithms Computer Science James Cormen
Data Science Mathematics Jane Downing
Calculus Mathematics John Smith

This table is in 2NF because it has a single-column primary key (course), so partial dependencies are impossible. However, a different kind of problem lurks here. Notice that the department depends on the lecturer (lecturer -> department), and the lecturer depends on the course (course -> lecturer). This indirect dependency still causes redundancy and anomalies.

Today, we will address this final step in basic normalization. Our learning outcome is to apply Third Normal Form (3NF) to eliminate transitive dependencies.

What is a Transitive Dependency?

While 2NF deals with dependencies on parts of the key, 3NF deals with dependencies that are passed through other non-key attributes. This is called a transitive dependency.

A transitive dependency exists when a non-key attribute is functionally dependent on another non-key attribute.

Your interest in logical systems will appreciate this abstract representation. If we have a primary key A and two non-key attributes B and C:

  • The key determines attribute B ().
  • Attribute B in turn determines attribute C ().
  • This creates a transitive dependency where the key A indirectly determines C via B.
Transitive Dependency vs. Not a Transitive Dependency
This diagram illustrates a transitive dependency. The primary key (A) determines a non-key attribute (B), which in turn determines another non-key attribute (C). 3NF aims to resolve this indirect dependency shown on the left.

This structure leads to the same kinds of modification anomalies (insertion, update, deletion) we saw with 2NF. To understand why these anomalies occur in practice, let's watch a clear explanation.

Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF

The 'Decomplexify' channel video we've used before has an excellent segment that defines 3NF and explains the problems caused by transitive dependencies using a practical example.

Please watch from 16:12 to 18:50. The video adds a Player_Skill_Level to the Player table, which creates a transitive dependency with Player_Rating. Pay close attention to how this leads to a data inconsistency (update anomaly) and how the video identifies the dependency chain.

As the video showed, because Player_Rating depends on Player_Skill_Level (a non-key attribute), updating the skill level without also updating the rating leads to contradictory data. This is precisely the kind of integrity failure that 3NF is designed to prevent.

The Solution: Applying Third Normal Form (3NF)

The rule for Third Normal Form is simple and builds directly upon 2NF:

  1. The table must be in 2NF.
  2. The table must have no transitive dependencies.

A very helpful mnemonic for achieving 3NF is that every non-key attribute must depend on "the key, the whole key, and nothing but the key."

  • "The key" (covered by 1NF)
  • "The whole key" (covered by 2NF, removes partial dependencies)
  • "Nothing but the key" (covered by 3NF, removes transitive dependencies)

The process to fix a 3NF violation is the same as for 2NF: decomposition. We identify the transitive dependency and move the involved attributes into a new, separate table.

Let's see this in action by resolving the issue in our Courses2 table.

How to Normalize a Database Into 2NF and 3NF

The Red-gate article from our last lesson continues directly into 3NF, showing how to fix the Courses2 table we started with.

Read the section titled 'Normalization to 3NF'. It clearly identifies the transitive dependency (course -> lecturer -> department) and shows how to decompose Courses2 into two new tables, Lecturers and Courses3.

By following the article's logic, we break the transitive dependency chain. We create:

  1. A Lecturers table to store the direct relationship between a lecturer and their department.
  2. A Courses3 table that now only links a course to a lecturer.

This decomposition cleanly separates the different "facts" the original table was trying to store: which lecturer teaches which course, and which department a lecturer belongs to.

Here is another visual example of the process:

Third Normal Form (3NF) Example
This image shows an `Emp` (Employee) table where `eno` (employee number) is the key. The `salary` depends on the `title`, which in turn depends on the `eno`. This is a classic transitive dependency. The table is decomposed into a new `Emp` table and a `Pay` table to achieve 3NF.
Test your understanding!

You have a table ProductDetails that stores information about products, their suppliers, and the supplier's location. The primary key is ProductID.

ProductID ProductName SupplierID SupplierName SupplierCity
P101 Laptop S1 TechCorp San Francisco
P102 Mouse S1 TechCorp San Francisco
P201 Keyboard S2 Gadgetron New York
P202 Monitor S2 Gadgetron New York

This table is in 2NF (since the key is not composite) but violates 3NF.

  1. Identify the functional dependencies that create a transitive dependency.
  2. Describe the anomalies this design could cause (e.g., what happens if you need to update a supplier's city?).
  3. How would you decompose this table to achieve 3NF?
Show answer

  1. Functional Dependencies:

    • ProductID -> SupplierID (The product determines its supplier)
    • SupplierID -> {SupplierName, SupplierCity} (The supplier ID determines the supplier's name and city)
    • This creates a transitive dependency: ProductID -> SupplierID -> {SupplierName, SupplierCity}.

  2. Anomalies:

    • Update Anomaly: If TechCorp moves from San Francisco to Austin, you would have to update this information in every row for every product supplied by TechCorp. Missing even one row would lead to data inconsistency.
    • Insertion Anomaly: You cannot add a new supplier (e.g., a potential future supplier) until they supply at least one product, because ProductID is the primary key and cannot be null.
    • Deletion Anomaly: If you delete the last product supplied by Gadgetron (Monitor, P202), you lose all information about Gadgetron itself, including its name and location.

  3. Decomposition to 3NF:
    You would decompose the table into two separate tables:

    • Products table:
      • ProductID (Primary Key)
      • ProductName
      • SupplierID (Foreign Key referencing the Suppliers table)
    • Suppliers table:
      • SupplierID (Primary Key)
      • SupplierName
      • SupplierCity

This design cleanly separates product information from supplier information, eliminating the transitive dependency and all associated anomalies.

Conclusion

In this lesson, we completed our journey through the fundamental normal forms by mastering 3NF. By identifying and eliminating transitive dependencies, we ensure that each attribute in a table is dependent only on the primary key. This step is critical for creating a robust, maintainable, and scalable database schema that protects data integrity.

Key Takeaways:

  • A transitive dependency occurs when a non-key attribute depends on another non-key attribute.
  • A table is in Third Normal Form (3NF) if it is in 2NF and has no transitive dependencies.
  • The guiding principle for 3NF is that every non-key attribute must depend on "the key, the whole key, and nothing but the key."
  • To achieve 3NF, we decompose the table by moving the transitively dependent attributes and the non-key attribute they depend on into a new table.

As a final summary, this table from the DataCamp article What is Third Normal Form (3NF)? neatly compares the first three normal forms.

Feature 1NF 2NF 3NF
Atomic data ✅ ✅ ✅
No partial dependencies ❌ ✅ ✅
No transitive dependencies ❌ ❌ ✅

Preview of the Next Lesson:
So far, we have focused on breaking down large, problematic tables into smaller, well-structured ones. But what happens when a business relationship is inherently complex? For example, a single student enrolls in many courses, and a single course has many students. In our next lesson, we will learn how to properly model these many-to-many relationships by designing a special kind of table called a junction table.