Third Normal Form: 3NF

A table is in Third Normal Form if: (1) it is already in 2NF, and (2) it has no transitive dependency.

Informal definition — transitive dependency

A transitive dependency means a non-key attributedepends on another non-key attribute.

A transitive dependency happens when:
A → B and B → C
So indirectly: A → C

In database terms: Primary key → Non-key attribute → Another non-key attribute

Example

Employee_ID	Employee_Name	Project_ID	Project_Name	Project_Location
E01	Rahim	P01	Hospital Management System	Dhaka
E02	Karim	P02	Banking System	Chattogram
E03	Mina	P01	Hospital Management System	Dhaka
E04	Sumi	P03	Library System	Rajshahi

Here, the primary key is Employee_ID. Functional dependencies:

Employee_ID → Employee_Name, Project_ID — because one employee has one name and is assigned to one project.
Project_ID → Project_Name, Project_Location — because one project ID determines the project name and location.

Employee_ID does not uniquely identify the Project_Name and Project_Location — it only reaches them through Project_ID. So indirectly:

Employee_ID → Project_ID → Project_Name, Project_Location
Key → Non-key → Non-key

This is called a transitive dependency, and it violates 3NF.

Problems caused by the transitive dependency

Anomaly	What goes wrong
Update	If the location of project P01 changes from Dhaka to Gazipur, it must be updated in multiple rows. If only one row is changed the database becomes inconsistent.
Insertion	A new project cannot be inserted unless at least one employee is assigned to it — a new project P04 cannot be stored properly without employee information.
Deletion	If employee E04 is deleted, and that employee is the only person assigned to project P03, then information about P03 may also be lost.

Solution

Break the table based on functional dependency:

Employee table

Employee_ID	Employee_Name	Project_ID
E01	Rahim	P01
E02	Karim	P02
E03	Mina	P01
E04	Sumi	P03

Project table

Project_ID	Project_Name	Project_Location
P01	Hospital Management System	Dhaka
P02	Banking System	Chattogram
P03	Library System	Rajshahi

Solution validation

Functional Dependency	Valid or Not
Employee_ID → Employee_Name	Valid
Employee_ID → Project_ID	Valid

Functional Dependency	Valid or Not
Project_ID → Project_Name	Valid
Project_ID → Project_Location	Valid

This removes the transitive dependency.

Formal definition of 3NF

For every functional dependency X → Y, the table is in 3NF if at least one of these is true:

X is a super key, or
Y is a prime attribute.

A prime attribute means an attribute that is part of any candidate key. This definition is more general and mathematically accurate.

Form of X → Y	Allowed in 3NF?
`super key → prime attribute`	Allowed ✔
`super key → non-prime attribute`	Allowed ✔
`non-super key → prime attribute`	Allowed ✔
`non-super key → non-prime attribute`	NOT allowed ✘

Applying the formal definition to the Employee table

3NF-abigCheck the Employee/Project table with the formal definition.

Another example — when the informal test cannot be used

Student_ID	Course_ID	Instructor
S01	CSE101	Dr. Hasan
S02	CSE101	Dr. Hasan
S03	CSE102	Dr. Karim
S04	CSE102	Dr. Karim
S01	CSE102	Dr. Karim

Assume the rules are: (1) a student can take many courses; (2) a course can be taught by only one instructor.

3NF-bbigIs the Student / Course / Instructor table in 3NF?

Next: Boyce-Codd Normal Form →