Identifying 3NF Violations

Welcome to the next step in your journey through database normalization. In the last lesson, we established the formal rules for Third Normal Form (3NF), concluding that a table must be in 2NF and contain no transitive dependencies. We summarized this with the principle that every non-key attribute must depend on "the key, the whole key, and nothing but the key."

Today, we'll put that theory into practice. This lesson is all about developing your ability to spot the red flags that signal a 3NF violation. You will learn how to analyze a table's structure and its data to identify the transitive dependencies that break the "nothing but the key" rule. This skill is essential for diagnosing and correcting flaws in a database design, a core competency for your upcoming project.

1. Recognizing Transitive Dependencies in Action

Before we start hunting for violations, let's quickly refresh our understanding of what a transitive dependency looks like. It's a chain of dependencies where the primary key determines a non-key attribute, which in turn determines another non-key attribute.

Schematically, if A is the primary key and B and C are non-key attributes, a transitive dependency exists if $A \to B$ and $B \to C$ . The dependency $B \to C$ is the problem—it's a violation of 3NF because the non-key attribute C depends on another non-key attribute B, not directly on the primary key A.

Let's watch a clear explanation of this concept.

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

This video from the channel Decomplexify provides a very clear, animated breakdown of a transitive dependency.

Watch the segment from the introduction to Third Normal Form. Pay close attention to how the Player_Rating is shown to depend on the Player_Skill_Level, which in turn depends on the Player_ID. This visualizes the problematic dependency chain perfectly.

As the video illustrates, the core issue is that one non-key attribute (Player_Rating) can be figured out from another non-key attribute (Player_Skill_Level). This indirect relationship is the hallmark of a transitive dependency and a clear violation of 3NF.

2. Examples of 3NF Violations

Now that we have a clear mental model, let's look at a few quick examples to see how this pattern appears in different scenarios. The key to spotting these is to ask: "Can I determine the value of one non-key column by looking at another non-key column?"

The following video presents several straightforward examples of tables that violate 3NF.

1NF 2NF 3NF DBMS

This video from Saghir School offers several simple and distinct examples of 3NF violations.

Watch the following short clips. For each one, identify the non-key attribute that determines another non-key attribute. Student Exams: How are Exam Type and Maximum Marks related? Cricket Matches: What is the relationship between Ground and Capacity? Employee Salaries: How are Designation and Salary connected?

In each case, you can see a clear dependency between two non-key attributes:

The Exam Type determines the Maximum Marks.
The Ground determines the Capacity.
The Designation determines the Salary.

These are all transitive dependencies and therefore violate 3NF.

Why These Violations Matter

It's crucial to remember why we're trying to find these issues. Transitive dependencies re-introduce the data anomalies (insertion, update, and deletion problems) that we worked to eliminate in 2NF.

Let's look at one more example that explicitly connects the violation to the problems it causes.

Understanding Third Normal Form (3NF)

This article provides another clear example and breaks down the specific anomalies that result from the 3NF violation.

Start at the section "Table Before 3NF (with Transitive Dependency)". Examine the Courses table and the identified dependency between Instructor and Phone. Then, read the section "Anomalies Caused by Transitive Dependency" to see the practical consequences.

As the article explains, storing the instructor's phone number in the Courses table is problematic:

Update Anomaly: If an instructor changes their phone number, you have to find and update every course they teach.
Deletion Anomaly: If you delete the only course an instructor teaches, you lose their phone number.
Insertion Anomaly: You can't add a new instructor and their phone number until they are assigned to at least one course.

This confirms that the Phone attribute is a fact about the Instructor, not the Course. It violates the "nothing but the key" rule.

3. Practice: Identify the Violation

Now it's your turn. Let's analyze a couple of tables and identify the 3NF violations.

Exercise 1: The Candidate Table

Examine the following table, which stores information about political candidates.

CANDIDATE Table for 3NF Violation Identification — A table of candidate information with columns for number, name, state, country, and age.

Identify the Primary Key: Which column uniquely identifies each row?
Identify the Non-Key Attributes: List the remaining columns.
Find the Transitive Dependency: Look at the non-key attributes. Is there a dependency where one non-key attribute determines another?

Click to reveal the solution

Primary Key: CAND_NO is the primary key.
Non-Key Attributes: CAND_NAME, CAND_STATE, CAND_COUNTRY, CAND_AGE.
Transitive Dependency: A candidate's country is determined by their state (CAND_STATE → CAND_COUNTRY). For example, if the state is "West Bengal," the country must be "India." Because CAND_COUNTRY (a non-key attribute) is dependent on CAND_STATE (another non-key attribute), this is a transitive dependency and a violation of 3NF.

Exercise 2: The Invoice Table

This next example is more complex and reflects a common business scenario. The table below represents invoice data. A single invoice (INV_NUM) can contain multiple products (PROD_NUM).

Normalization Exercise: Identifying 3NF Violations — An exercise showing an INVOICE table with a composite primary key and asking to identify dependencies.

The primary key for this table is a composite key: (INV_NUM, PROD_NUM).

Your task is to find a transitive dependency in this table. Remember, you are looking for a dependency between two non-key attributes.

Hint: Look at the product and vendor information. Is there an attribute that is a fact about the vendor, not about the specific product on a specific invoice?

Click to reveal the solution

The transitive dependency is VEND_CODE → VEND_NAME.

Let's break this down:

VEND_CODE and VEND_NAME are both non-key attributes.
The vendor's name (VEND_NAME) is determined by the vendor's code (VEND_CODE). If you know the code is 211, you know the name is "Bryson, Inc.".
This is a dependency between two non-key attributes, which is the definition of a transitive dependency. The vendor's name is a fact about the vendor, not about the product line on this specific invoice. Therefore, this table violates 3NF.

(Note: This table also has partial dependencies, like PROD_NUM → PROD_LABEL, making it violate 2NF as well. But our focus here is on spotting the transitive dependency.)

Conclusion

In this lesson, you've moved from knowing the definition of 3NF to actively identifying its violations in practice. By examining the relationships between non-key attributes, you can now spot the transitive dependencies that lead to data anomalies and compromise database integrity.

Key Takeaways:

A violation of 3NF occurs when there is a transitive dependency.
A transitive dependency exists when a non-key attribute is functionally dependent on another non-key attribute.
To find these violations, analyze a table and ask: "Can I determine one non-key attribute from another non-key attribute?"
These dependencies cause update, insertion, and deletion anomalies, reinforcing the need for normalization.

You are now equipped to diagnose tables that are not in 3NF. In our next and final lesson on normalization, we will focus on the solution: how to convert a 2NF table into a set of 3NF tables by removing these transitive dependencies.

Recommended resources