Third Normal Form (3NF)

Third Normal Form (3NF)

Third Normal Form (3NF) is an important stage in the normalization of relational databases. It builds upon Second Normal Form (2NF) and focuses on eliminating transitive dependencies.

The main goal of 3NF is to reduce data redundancy, improve data consistency, and prevent unnecessary duplication of information. A relation is generally considered to be in 3NF when it is already in 2NF and its non-key attributes do not depend on other non-key attributes.

In this article, we will understand the definition of 3NF, examine a practical example, learn how to convert a table into 3NF, and explore the limitations of 3NF.

What is Third Normal Form (3NF)?

A relation is in Third Normal Form (3NF) if, for every non-trivial functional dependency X → Y, at least one of the following conditions is satisfied:

1. X is a super key, or
2. Y is a prime attribute, meaning it is part of at least one candidate key.

In simpler terms, 3NF prevents a non-prime attribute from depending transitively on a candidate key.

For example, if we have:

EMP_ID → EMP_ZIP
EMP_ZIP → EMP_CITY

then we can derive:

EMP_ID → EMP_CITY

Here, EMP_CITY depends on EMP_ID indirectly through EMP_ZIP. This is called a transitive dependency and needs to be removed to achieve 3NF in this example.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here

Why is 3NF Important?

Third Normal Form helps database designers create tables that are easier to maintain and less prone to inconsistencies.

• Reduces unnecessary data duplication.
• Eliminates transitive dependencies.
• Improves data consistency.
• Reduces update, insertion, and deletion problems caused by redundant data.
• Makes database structures easier to maintain.

Example of Third Normal Form

EMPLOYEE_DETAIL Table

Consider the following EMPLOYEE_DETAIL table:

EMP_ID	EMP_NAME	EMP_ZIP	EMP_STATE	EMP_CITY
222	Harry	201010	UP	Noida
333	Stephan	02228	US	Boston
444	Lan	60007	US	Chicago
555	Katharine	06389	UK	Norwich
666	John	462007	MP	Bhopal

In this example:

• Candidate Key: EMP_ID
• Super Keys: {EMP_ID}, {EMP_ID, EMP_NAME}, {EMP_ID, EMP_NAME, EMP_ZIP}, and so on.
• Prime Attribute: EMP_ID
• Non-Prime Attributes: EMP_NAME, EMP_ZIP, EMP_STATE, and EMP_CITY

Identifying the Transitive Dependency

Suppose the functional dependencies are:

EMP_ID → EMP_NAME, EMP_ZIP
EMP_ZIP → EMP_STATE, EMP_CITY

Since EMP_ID determines EMP_ZIP, and EMP_ZIP determines EMP_STATE and EMP_CITY, we have a transitive dependency:

EMP_ID → EMP_ZIP → EMP_STATE, EMP_CITY

Because EMP_ZIP is not a candidate key and EMP_STATE and EMP_CITY are non-prime attributes, this dependency violates the requirements for 3NF.

How to Convert a Relation into 3NF

To remove the transitive dependency, we can decompose the original table into two separate relations.

1. EMPLOYEE Table

EMP_ID	EMP_NAME	EMP_ZIP
222	Harry	201010
333	Stephan	02228
444	Lan	60007
555	Katharine	06389
666	John	462007

Here, EMP_ID is the primary key.

2. EMPLOYEE_ZIP Table

EMP_ZIP	EMP_STATE	EMP_CITY
201010	UP	Noida
02228	US	Boston
60007	US	Chicago
06389	UK	Norwich
462007	MP	Bhopal

In the EMPLOYEE_ZIP table, EMP_ZIP acts as the key that determines the corresponding state and city.

The decomposition removes the transitive dependency from the employee relation. The two tables can be connected using EMP_ZIP as a foreign key in the EMPLOYEE table.

3NF and Database Anomalies

3NF significantly reduces redundancy and eliminates transitive dependencies, but it does not eliminate every possible type of database anomaly.

In particular, a relation can satisfy 3NF and still contain multivalued dependencies. Such dependencies are addressed by a higher normal form known as Fourth Normal Form (4NF).

Example: STAFF Relation

Consider a STAFF relation containing information about staff members, the equipment they use, and the languages they know:

STAFF(S_Name, Equipment, Language)

Assume that the combination of S_Name, Equipment, and Language forms the key.

S_Name	Equipment	Language
Anurag	PC	English
Anurag	PC	French
Anurag	Mainframe	English
Anurag	Mainframe	French
Kapil	PC	English
Kapil	PC	French
Kapil	PC	Japanese

There are no transitive dependencies in this relation, so it can satisfy the requirements of 3NF. However, the table can still contain unnecessary repetition because a staff member’s equipment and languages are independent multi-valued facts.

YT:- DecodeIT

Insertion Anomaly

Suppose Anurag learns Japanese. If Anurag uses multiple types of equipment, adding his new language may require multiple rows.

This leads to unnecessary duplication because the language information has to be repeated for each applicable equipment value.

Deletion Anomaly

Suppose Anurag’s PC is deallocated and the rows associated with that equipment are deleted. Depending on the stored data, deleting those rows could unintentionally remove information about the languages Anurag knows.

This is an example of how independent facts can become unnecessarily tied together in a single relation.

Update Anomaly

If a staff member’s name changes from Anurag to Anuraj, the name may need to be updated in multiple rows.

If even one occurrence is missed, the database can contain inconsistent information.

3NF vs 4NF

The important distinction is that 3NF primarily deals with functional dependencies and transitive dependencies, while 4NF deals with non-trivial multivalued dependencies.

Normal Form	Main Purpose
1NF	Eliminates repeating groups and ensures atomic values.
2NF	Removes partial dependencies on a composite candidate key.
3NF	Removes problematic transitive dependencies.
4NF	Removes problematic multivalued dependencies.

Key Points of Third Normal Form

• A relation should first satisfy 2NF.
• 3NF eliminates problematic transitive dependencies.
• Every non-trivial functional dependency must satisfy the 3NF condition.
• The determinant should be a super key, or the dependent attribute should be prime.
• 3NF reduces redundancy and improves data consistency.
• 3NF does not necessarily eliminate multivalued dependencies.
• Relations with problematic multivalued dependencies may require further decomposition into 4NF.

Conclusion

Third Normal Form (3NF) is an important stage in relational database normalization. Its primary objective is to eliminate problematic transitive dependencies and reduce unnecessary data redundancy.

By separating attributes that depend on non-key attributes into their own relations, database designers can create tables that are more consistent, maintainable, and efficient.

However, achieving 3NF does not guarantee that every type of redundancy or anomaly has been eliminated. Multivalued dependencies can still exist in a 3NF relation, which is why Fourth Normal Form (4NF) is required for more advanced normalization.

Understanding 3NF and its limitations provides a strong foundation for designing well-structured relational databases.

Related Tutorials

• Machine Learning Tutorial
• Data Science Tutorial
• Deep Learning Tutorial
• Complete Python Course with Advanced Topics
• SQL Tutorial
• Real-Time Projects

Keywords

Third Normal Form (3NF), 3NF in DBMS, Third Normal Form in DBMS, 3NF normalization, database normalization, normalization in DBMS, transitive dependency, functional dependency, 2NF vs 3NF, 3NF example, 3NF decomposition, database anomalies, insertion anomaly, deletion anomaly, update anomaly, multivalued dependency, Fourth Normal Form, 4NF in DBMS