Third Normal Form (3NF) in DBMS

Once a table meets the requirements of Second Normal Form (2NF), the next step is to convert it into Third Normal Form (3NF). This is used to eliminate transitive dependency. Read this chapter to get a clear understanding of 3NF and transitive dependency.

What is Third Normal Form (3NF)?

In relational database, the third normal form has two certain conditions −

• The table is already in Second Normal Form (2NF).
• The table does not have any transitive dependency.

What is Transitive Dependency?

A transitive dependency occurs when a non-prime attribute depends on another non-prime attribute rather than directly depending on a candidate key.

To put it simply, in a transitive dependency, one non-prime attribute is indirectly connected to the candidate key through another non-prime attribute. This will may make unnecessary redundancy and inconsistency in the data.

To understand this better, let us elaborate some of the important terms −

• Candidate Key − A minimal set of attributes that uniquely identify each row in the table.
• Prime Attribute − An attribute that is part of a candidate key.
• Non-Prime Attribute − An attribute that is not part of any candidate key.

Example of Transitive Dependency

Consider the Student table with the following data

The Functional Dependencies are −

• Roll Number → State
• State → City

In this example, the candidate key is the Roll Number because it uniquely identifies each row. State and City are non-prime attributes.

The Roll Number determines the State. And, the State determines the City. It creates a transitive dependency. Here, the Roll Number indirectly determines the City through the State.

While "Roll Number → State" is valid, this dependency "State → City" violates 3NF because it is a relationship between two non-prime attributes.

How to Eliminate Transitive Dependency?

To remove transitive dependency, we need to split the table into smaller tables or decomposition.

Step 1: Divide the Table

We create two separate tables: one table links the candidate key to the first non-prime attribute (State), and the other table links the first non-prime attribute (State) to the second non-prime attribute (City).

Step 2: Identify the Keys

In the Student Table, the candidate key remains Roll Number. The State is a non-prime attribute. In the State Table, the primary key is State, but we can see there are duplicate States. So, we can add another key attribute called state id, and that can be used inside the Student table as well.

By splitting the table, we can ensure that all non-prime attributes directly depend on the candidate key or are part of a separate table where they follow the same rule.

Another Example of 3NF: Table and Functional Dependencies

Consider a relation {A, B, C, D} with the following dependencies −

• AB → C
• C → D

Step 1: Identify the Candidate Key

To find the candidate key −

• AB → C means AB determines C.
• C → D means C determines D.

From AB, we can easily determine all the attributes: {AB → C → D}. So, the candidate key is {AB}.

Step 2: Classify the Attributes

Next, let's classify the attributes −

• Prime Attributes − A, B (since they form the candidate key).
• Non-Prime Attributes − C, D.

Step 3: Check for Transitive Dependency

• AB → C: This is valid because the candidate key determines a non-prime attribute.
• C → D: This creates a transitive dependency since:
• D is a non-prime attribute.
• D is determined by another non-prime attribute (C).

Step 4: Eliminate Transitive Dependency

Split the table into two −

Now,

• In the Main Table, AB is the candidate key and C is a non-prime attribute directly dependent on it.
• In the Derived Table, C is the candidate key and D depends directly on it.

How to Check for 3NF Compliance

To determine if a table is in 3NF, follow the steps give below –

• Ensure the Table is in 2NF − No partial dependency should exist.
• Check Functional Dependencies − For each dependency, ensure that either:
  • The left-hand side (LHS) is a candidate key or super key, or
  • The right-hand side (RHS) is a prime attribute.

A quick rule of thumb − if a non-prime attribute depends on another non-prime attribute, it is a transitive dependency. This is violating 3NF.

Practical Steps to Achieve 3NF

Follow the steps given below to turn a table into its Third Normal Form –

• Find the Candidate Keys − Use closure methods to identify all the candidate keys.
• Classify the Attributes − Divide the attributes into prime and non-prime categories.
• Check the Dependencies − If the LHS of a functional dependency is not a candidate key or super key, check if the RHS is a prime attribute.
• Eliminate the Transitive Dependencies − Split the table into smaller ones if necessary.

Conclusion

In this chapter, we explained how to transform a table into its Third Normal Form (3NF) by addressing transitive dependency. We started with the basic rules of 3NF and clarified the concept of transitive dependency using examples.

Through step-by-step methods, we explored how to check for 3NF compliance and how to eliminate transitive dependencies by splitting the tables. By normalizing the tables to 3NF, we can ensure that non-prime attributes depend directly on candidate keys. This is making the database more consistent and reducing redundancy.