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 38

Understanding and Identifying Transitive Dependencies

Recommended resources

Welcome to this lesson on the next stage of database normalization. In our previous lessons, you've learned how to structure data into First Normal Form (1NF) and then refine it into Second Normal Form (2NF) by eliminating partial dependencies. You now have a solid understanding that normalization is about removing dependencies that cause data anomalies.

This lesson takes you one step further, focusing on the Third Normal Form (3NF). Our goal is to define and identify a more subtle type of problematic relationship called a transitive dependency. Mastering this will allow you to diagnose the final issues preventing a table from being fully normalized and efficient, setting the stage for our next lesson where we will fix these issues.

1. What is a Transitive Dependency?

After a table is in 2NF, all its non-key attributes are fully dependent on the primary key. However, a problem can still exist if a non-key attribute depends on another non-key attribute. This indirect dependency is called a transitive dependency.

Think of it as a chain of dependency:

  • The Primary Key determines non-key Attribute X.
  • Non-key Attribute X determines non-key Attribute Y.

Therefore, the Primary Key indirectly determines Attribute Y through Attribute X.

Relation with Transitive Dependency
This diagram shows a classic transitive dependency. The primary key `Cust_ID` determines the `Salesperson`. The `Salesperson` (a non-key attribute) in turn determines the `Region` (another non-key attribute). Thus, `Region` is transitively dependent on `Cust_ID`.

The formal definition is straightforward. Let's get it from our textbook resource.

Chapter 12 Normalization

This section from the "Database Design" textbook provides the formal definition of Third Normal Form and transitive dependency.

Read the two sentences under the heading "Third Normal Form (3NF)". Focus on the definition it gives for a transitive dependency.

As the text states, a transitive dependency exists when a non-key attribute is functionally dependent on another non-key attribute. This violates the principle that all attributes should depend only on the primary key.

2. Why are Transitive Dependencies a Problem?

Like partial dependencies, transitive dependencies create data redundancy, leading to insertion, update, and deletion anomalies. Let's watch a short video that clearly explains how an update anomaly can occur because of a transitive dependency.

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

The video "Learn Database Normalization" by Decomplexify provides an excellent example of a transitive dependency and the problems it causes.

Watch the section that introduces the Player table example. Then, pay close attention as the video explains how a data inconsistency arises and defines the transitive dependency responsible for it.

In the video's example, the dependencies are:

  • Player_ID → Player_Skill_Level
  • Player_Skill_Level → Player_Rating

Here, Player_Rating (a non-key attribute) depends on Player_Skill_Level (another non-key attribute). This is a transitive dependency. If you update a player's skill level, you must also remember to update their rating, or the data becomes inconsistent. A properly designed database should prevent this possibility.

3. How to Identify Transitive Dependencies

To get a table into 3NF, you first need to be able to spot transitive dependencies. You can do this by examining the functional dependencies or by analyzing the business rules represented in the table's data.

Let's look at another example, this time involving a composite primary key.

Third Normal Form (3NF) | Database Normalization | DBMS

The video "Third Normal Form (3NF)" by Studytonight with Abhishek demonstrates a transitive dependency in a table with a composite key.

Watch the section that uses the score table to illustrate a new dependency. Notice how total_marks doesn't depend directly on the primary key. Then, listen to the clear definition of transitive dependency that follows.

In this case, the dependencies within the score table are:

  • Primary Key: {student_ID, subject_ID}
  • Full dependency: {student_ID, subject_ID} → exam_name
  • Transitive dependency: exam_name → total_marks

The attribute total_marks is determined by exam_name, which is a non-prime attribute. This is a clear violation of 3NF.

Let's look at one more visual example.

Transitive Dependency Example
In this table, the instructor's office (`I_OFFICE`) is determined by the instructor (`INSTR`). Since `INSTR` is not the primary key, this represents a transitive dependency. If an instructor changes their office, the update must be made for every course section they teach, risking inconsistency.

4. Practice Exercise

Now, let's put your identification skills to the test. You'll be given a table schema and its functional dependencies. Your task is to identify any partial or transitive dependencies.

Scenario:
An agency tracks which employees are working on which contracts.

Table Schema:
WORK_ASSIGNMENT(NIN, ContractNo, Hours, EName, HotelNo, HotelLocation)
(NIN is National Insurance Number, EName is Employee Name)

Functional Dependencies:

  • The primary key is {NIN, ContractNo}.
  • The hours an employee works depends on both the employee and the contract: {NIN, ContractNo} -> Hours
  • An employee's name is determined by their NIN: NIN -> EName
  • A contract is for a specific hotel: ContractNo -> HotelNo
  • A hotel's number determines its location: HotelNo -> HotelLocation

Your Task:
Examine the functional dependencies above and identify:

  1. Any partial dependencies.
  2. Any transitive dependencies.

Write down the functional dependencies that represent these violations.

Click here for the solution

Solution:

Let's break down the dependencies based on the primary key {NIN, ContractNo}.

  1. Partial Dependencies:

    • NIN -> EName: This is a partial dependency because EName depends on NIN, which is only part of the primary key.
    • ContractNo -> HotelNo: This is also a partial dependency because HotelNo depends on ContractNo, which is only part of the primary key.

  2. Transitive Dependency:

    • ContractNo -> HotelNo -> HotelLocation: This represents a transitive dependency. HotelLocation is functionally dependent on HotelNo, which is a non-key attribute. Because ContractNo determines HotelNo, ContractNo indirectly determines HotelLocation. The specific transitive dependency is ContractNo -> HotelLocation.

Well done if you spotted all of them! Recognizing these dependencies is the essential first step before you can correct the database design.

Conclusion

In this lesson, we've focused on the final requirement for a well-normalized database: the Third Normal Form. You've learned how to define and, most importantly, identify transitive dependencies.

Key Takeaways:

  • A table is in 3NF if it is in 2NF and contains no transitive dependencies.
  • A transitive dependency exists when a non-key attribute is functionally dependent on another non-key attribute.
  • Like other dependencies, transitive dependencies cause data redundancy and lead to update, insertion, and deletion anomalies.
  • A useful mnemonic sums up the rules for 1NF, 2NF, and 3NF: "Every non-key attribute must provide a fact about the key, the whole key, and nothing but the key."
    • "The whole key" addresses 2NF (no partial dependencies).
    • "Nothing but the key" addresses 3NF (no transitive dependencies).

Now that you can identify tables that violate 3NF, the next logical step is to fix them. In our next lesson, we will learn the process of decomposing a 2NF table into a set of 3NF tables, completing your core knowledge of the normalization process.