Third Normal Form (3NF) - Detailed Explanation

 

Third Normal Form (3NF)

Third Normal Form (3NF) is a database normalization rule that removes transitive dependencies from a relation. It ensures that non-key attributes depend only on the primary key and nothing else.

1. What is a Transitive Dependency?

A transitive dependency occurs when:

  • X → Z

  • Z → Y

So indirectly:

  • X → Y through Z

But Z is not a candidate key.

This means Y depends on X indirectly through Z, which causes redundancy and possible update anomalies.

2. Definition of 3NF

According to Edgar F. Codd’s definition:

A relation schema R is in Third Normal Form (3NF) if:

  1. It is already in Second Normal Form (2NF), and

  2. No non-prime attribute is transitively dependent on the primary key.

Important Terms

  • Prime attribute → part of a candidate key

  • Non-prime attribute → not part of any candidate key

So in simple terms:

Non-key attributes should depend directly on the primary key, not through another non-key attribute.

3. Example 

Relation: EMP_DEPT

SsnDnumberDnameDmgr_ssn

Functional Dependencies

  1. Ssn → Dnumber

  2. Dnumber → Dmgr_ssn

  3. Dnumber → Dname

So we get a transitive dependency:

Ssn → Dnumber → Dmgr_ssn
Ssn → Dnumber → Dname

Here:

  • Ssn = Primary Key

  • Dnumber = Non-key attribute

  • Dmgr_ssn, Dname depend on Dnumber, not directly on Ssn

Therefore:

Ssn → Dmgr_ssn (transitively)
Ssn → Dname (transitively)

This violates 3NF.


4. Problem with Transitive Dependency

If the table is kept as it is:

  • Data redundancy occurs

  • Update anomalies occur

  • Department information repeats for many employees

Example:

Ssn    Dnumber    Dname
E1    D1    Sales
E2    D1    Sales

If Sales changes to Marketing, we must update many rows.

5. Converting to 3NF (Decomposition)

The solution is splitting the table into two relations.

Table 1: ED1 (Employee)

SsnDnumber

Dependency:

Ssn → Dnumber

Table 2: ED2 (Department)

DnumberDnameDmgr_ssn

Dependencies:

Dnumber → Dname
Dnumber → Dmgr_ssn

Now:

  • Each table represents independent facts

  • No transitive dependencies exist

6. Recovering Original Table

Using NATURAL JOIN:

ED1 ⨝ ED2

We can reconstruct the original EMP_DEPT table without spurious tuples.

Fig:Normalizing EMP_DEPT table into 3NF Relations


7. Intuitive Understanding of 3NF

3NF removes two problematic dependencies:

  1. Partial dependency → handled by 2NF

  2. Transitive dependency → handled by 3NF

Rule of thumb:

Non-key attributes must depend only on the key, the whole key, and nothing but the key.

8. Quick Summary

✅ 3NF Goal:
Ensure that every non-key attribute depends only on the primary key and not on other non-key attributes.

Normal FormRule        Problem Removed
1NF    Atomic attributes    Repeating groups
2NF    No partial dependency on key    Partial dependency
3NF    No transitive dependency    Indirect dependency


Examples

Problem 1

Relation

STUDENT(SID, SName, DeptID, DeptName)

Functional Dependencies

SID → SName, DeptID
DeptID → DeptName

Step 1: Primary Key

SID

Step 2: Transitive Dependency

SID → DeptID
DeptID → DeptName

So:

SID → DeptName (transitive)

Step 3: Decomposition into 3NF

Table 1

STUDENT(SID, SName, DeptID)

Table 2

DEPARTMENT(DeptID, DeptName)

Problem 2

Relation

EMP(EmpID, EmpName, DeptID, DeptName, Manager)

Functional Dependencies

EmpID → EmpName, DeptID
DeptID → DeptName, Manager

Primary Key

EmpID

Transitive Dependency

EmpID → DeptID
DeptID → DeptName, Manager

3NF Decomposition

EMPLOYEE

EmpID, EmpName, DeptID

DEPARTMENT

DeptID, DeptName, Manager

Problem 3

Relation

BOOK(BookID, Title, AuthorID, AuthorName)

Functional Dependencies

BookID → Title, AuthorID
AuthorID → AuthorName

Primary Key

BookID

Transitive Dependency

BookID → AuthorID
AuthorID → AuthorName

3NF Decomposition

BOOK

BookID, Title, AuthorID

AUTHOR

AuthorID, AuthorName

Problem 4

Relation

ORDER(OrderID, OrderDate, CustomerID, CustomerName)

Functional Dependencies

OrderID → OrderDate, CustomerID
CustomerID → CustomerName

Primary Key

OrderID

Transitive Dependency

OrderID → CustomerID
CustomerID → CustomerName

3NF Decomposition

ORDER

OrderID, OrderDate, CustomerID

CUSTOMER

CustomerID, CustomerName

Problem 5

Relation

COURSE_REG(StudentID, StudentName, CourseID, CourseName, Instructor)

Functional Dependencies

StudentID → StudentName
CourseID → CourseName, Instructor
(StudentID, CourseID) → StudentName, CourseName, Instructor

Candidate Key

(StudentID, CourseID)

Transitive Dependencies

StudentID → StudentName
CourseID → CourseName, Instructor

3NF Decomposition

STUDENT

StudentID, StudentName

COURSE

CourseID, CourseName, Instructor

REGISTRATION

StudentID, CourseID

Comments

Post a Comment

Popular posts from this blog

Database Management Systems DBMS PCCST402 Semester 4 KTU CS 2024 Scheme

 About Me Database Management Systems DBMS PCCST402 Scheme and Syllabus Module-1 Introduction to DBMS Introduction to DBMS Example  Database Database System Vs Traditional File Processing Advantages of using DBMS Database System Concepts and Architecture Data Models, Schemas and Instances   Three schema architecture and Data Independence Database Languages and Interfaces Centralized and Client Server Architectures in DBMS Classification of Database Management Systems Conceptual Data Modeling and Database Design Conceptual Modeling and the Role of the ER Model in Database Design Entities and Attributes  Company Database Application - Example Entity Types, Entity Sets, Keys, and Value Sets in the ER Model Initial Conceptual Design of the COMPANY Database Relationship Types in the ER Model Relationship Degree, Role Names, and Recursive Relationships Constraints on Binary Relationship Types Attributes of Relationship Types Weak Entity Types Summary of the Notations Used ...
Read more

Data Models, Schemas and Instances

  Data Models, Schemas and Instances  1. Data Abstraction In databases, data abstraction means hiding the complex details of how data is stored and focusing only on what is important. Different users can view data at different levels of detail depending on their needs. 2. Data Models A data model is a collection of concepts used to describe the structure of a database. It defines: Data types Relationships among data Constraints on data Operations for accessing and updating data Data models help us understand and design databases without worrying about low-level storage details. Categories of Data Models a) High-level / Conceptual Data Models Close to how users think about data. Use concepts like entities , attributes , and relationships . Example: ER (Entity–Relationship) Model . b) Representational (Implementation) Data Models In between conceptual and physical models. Easy for users to understand but still close to how data is st...
Read more

Introduction to Database Management System -DBMS

Image
In today’s digital world, almost every application—from mobile banking and e-commerce to university portals and hospital systems—depends on the efficient storage, organization, and retrieval of data. As the amount of information generated by individuals and organizations continues to grow, managing this data in a structured and meaningful way becomes essential. This is where database systems play a central role. A database can be thought of as a well-organized collection of related data, designed so that it can be easily accessed, managed, and updated. But the database itself is only one component. To manage it effectively, we rely on a specialized software package known as a Database Management System (DBMS) . A DBMS provides a systematic and automated way to store large volumes of data, enforce rules for consistency and correctness, protect data from unauthorized access, and allow multiple users to work with the data simultaneously without conflicts. A database typically has three...
Read more