Kaushik's Notes

3NF - Third Normal Form

8 min read

Overview

  • Definition: A relation is in 3NF if it’s in 2NF and has no transitive dependencies
  • Purpose: Eliminates transitive functional dependencies
  • Requirement: Must first be in 2NF
  • Focus: Non-key attributes should not depend on other non-key attributes

Key Concepts

Transitive Dependency

  • Definition: When non-key attribute A depends on non-key attribute B, which depends on the primary key
  • Pattern: Primary Key → Attribute B → Attribute A
  • Problem: Creates redundancy and anomalies
  • Example: Employee_ID → Department_ID → Department_Name

Direct Dependency

  • Definition: Non-key attribute depends directly on primary key
  • Pattern: Primary Key → Attribute
  • Allowed in 3NF: These dependencies are acceptable
  • Example: Employee_ID → Employee_Name

Rules for 3NF

Prerequisites

  • Must be in 2NF: No partial dependencies exist
  • Identify all functional dependencies: Map out all attribute relationships

Requirements

  • No Transitive Dependencies: Non-key attributes cannot depend on other non-key attributes
  • Direct Dependencies Only: All non-key attributes must depend directly on primary key
  • Alternative Definition: For every functional dependency X → Y, either:
    • X is a superkey, OR
    • Y is a prime attribute (part of candidate key)

Violations of 3NF

Example 1: Employee Department Information

Table in 2NF but NOT 3NF:

Employee Table:
Emp_ID | Name     | Dept_ID | Dept_Name | Dept_Location | Salary
101    | John     | D1      | IT        | Building A    | 75000
102    | Mary     | D2      | HR        | Building B    | 65000
103    | Bob      | D1      | IT        | Building A    | 80000
104    | Alice    | D3      | Finance   | Building C    | 70000

Functional Dependencies:

  • Emp_ID → Name (Direct)
  • Emp_ID → Dept_ID (Direct)
  • Emp_ID → Salary (Direct)
  • Dept_ID → Dept_Name (Direct)
  • Dept_ID → Dept_Location (Direct)

Transitive Dependencies:

  • Emp_ID → Dept_ID → Dept_Name
  • Emp_ID → Dept_ID → Dept_Location

Converting to 3NF

Example 1: Employee Department System

Before (2NF but not 3NF):

Employee Table:
Emp_ID | Name  | Dept_ID | Dept_Name | Dept_Location | Salary
101    | John  | D1      | IT        | Building A    | 75000
102    | Mary  | D2      | HR        | Building B    | 65000
103    | Bob   | D1      | IT        | Building A    | 80000

After (3NF):

Departments Table:
Dept_ID | Dept_Name | Dept_Location
D1      | IT        | Building A
D2      | HR        | Building B
D3      | Finance   | Building C

Employees Table:
Emp_ID | Name  | Dept_ID | Salary
101    | John  | D1      | 75000
102    | Mary  | D2      | 65000
103    | Bob   | D1      | 80000

Example 2: Student Course Registration

Before (NOT 2NF, so also not 3NF):

Student_Course Table:
Student_ID | Course_ID | Course_Name | Instructor_ID | Instructor_Name | Grade
1          | CS101     | Programming | I001          | Dr. Smith       | A
1          | CS201     | Database    | I002          | Dr. Jones       | B
2          | CS101     | Programming | I001          | Dr. Smith       | B
2          | CS301     | Networks    | I003          | Dr. Brown       | A

Dependencies (key = Student_ID + Course_ID):

  • Course_ID → Course_Name, Instructor_ID (partial dependency on part of the key, a 2NF violation)
  • Course_ID → Instructor_ID → Instructor_Name (transitive, a 3NF violation)
  • (Student_ID, Course_ID) → Grade (full dependency)
  • Note: Splitting out Courses fixes 2NF; splitting Instructors out of Courses fixes 3NF

After (3NF):

Instructors Table:
Instructor_ID | Instructor_Name
I001          | Dr. Smith
I002          | Dr. Jones
I003          | Dr. Brown

Courses Table:
Course_ID | Course_Name | Instructor_ID
CS101     | Programming | I001
CS201     | Database    | I002
CS301     | Networks    | I003

Enrollments Table:
Student_ID | Course_ID | Grade
1          | CS101     | A
1          | CS201     | B
2          | CS101     | B
2          | CS301     | A

Example 3: Product Order System

Before (NOT 2NF, so also not 3NF):

Order_Items Table:
Order_ID | Product_ID | Product_Name | Category_ID | Category_Name | Quantity | Unit_Price
O001     | P001       | Laptop       | C001        | Electronics   | 2        | 1000
O001     | P002       | Mouse        | C001        | Electronics   | 3        | 25
O002     | P003       | Chair        | C002        | Furniture     | 1        | 200
O002     | P001       | Laptop       | C001        | Electronics   | 1        | 1000

Dependencies (key = Order_ID + Product_ID):

  • Product_ID → Product_Name, Category_ID, Unit_Price (partial dependency on part of the key, a 2NF violation)
  • Product_ID → Category_ID → Category_Name (transitive, a 3NF violation)
  • (Order_ID, Product_ID) → Quantity (full dependency)
  • Note: Splitting out Products fixes 2NF; splitting Categories out of Products fixes 3NF

After (3NF):

Categories Table:
Category_ID | Category_Name
C001        | Electronics
C002        | Furniture

Products Table:
Product_ID | Product_Name | Category_ID | Unit_Price
P001       | Laptop       | C001        | 1000
P002       | Mouse        | C001        | 25
P003       | Chair        | C002        | 200

Order_Items Table:
Order_ID | Product_ID | Quantity
O001     | P001       | 2
O001     | P002       | 3
O002     | P003       | 1
O002     | P001       | 1

Real-World Examples

Hospital Management System

Before (Not 3NF):

Patient_Admission Table:
Patient_ID | Patient_Name | Room_No | Room_Type | Room_Rate | Doctor_ID | Doctor_Name | Admission_Date
P001       | John Smith   | R101    | Private   | 500       | D001      | Dr. Brown   | 2024-01-15
P002       | Mary Jones   | R102    | General   | 300       | D001      | Dr. Brown   | 2024-01-16
P003       | Bob Wilson   | R101    | Private   | 500       | D002      | Dr. White   | 2024-01-17

Transitive Dependencies:

  • Room_No → Room_Type → Room_Rate
  • Doctor_ID → Doctor_Name

After (3NF):

Room_Types Table:
Room_Type | Room_Rate
Private   | 500
General   | 300
ICU       | 1000

Rooms Table:
Room_No | Room_Type
R101    | Private
R102    | General
R103    | ICU

Doctors Table:
Doctor_ID | Doctor_Name
D001      | Dr. Brown
D002      | Dr. White

Patients Table:
Patient_ID | Patient_Name
P001       | John Smith
P002       | Mary Jones
P003       | Bob Wilson

Admissions Table:
Patient_ID | Room_No | Doctor_ID | Admission_Date
P001       | R101    | D001      | 2024-01-15
P002       | R102    | D001      | 2024-01-16
P003       | R101    | D002      | 2024-01-17

Book Publishing System

Before (Not 3NF):

Book_Sales Table:
Book_ID | Title        | Author_ID | Author_Name | Publisher_ID | Publisher_Name | Sales_Date | Quantity
B001    | Database     | A001      | John Doe    | P001         | Tech Books     | 2024-01-15 | 50
B002    | Networks     | A002      | Jane Smith  | P001         | Tech Books     | 2024-01-16 | 30
B003    | Programming  | A001      | John Doe    | P002         | Code Press     | 2024-01-17 | 40

After (3NF):

Publishers Table:
Publisher_ID | Publisher_Name
P001         | Tech Books
P002         | Code Press

Authors Table:
Author_ID | Author_Name
A001      | John Doe
A002      | Jane Smith

Books Table:
Book_ID | Title       | Author_ID | Publisher_ID
B001    | Database    | A001      | P001
B002    | Networks    | A002      | P001
B003    | Programming | A001      | P002

Sales Table:
Book_ID | Sales_Date | Quantity
B001    | 2024-01-15 | 50
B002    | 2024-01-16 | 30
B003    | 2024-01-17 | 40

Benefits of 3NF

Eliminates Redundancy

  • Reduced Data Duplication: Each fact stored in one place
  • Storage Efficiency: Minimizes storage requirements
  • Example: Department name stored once instead of for each employee

Prevents Anomalies

Insert Anomaly Prevention

  • Before 3NF: Cannot add department without hiring employee
  • After 3NF: Can add departments independently

Update Anomaly Prevention

  • Before 3NF: Must update department name in multiple employee records
  • After 3NF: Update department name once in departments table

Delete Anomaly Prevention

  • Before 3NF: Deleting last employee loses department information
  • After 3NF: Department information preserved

Improves Data Integrity

  • Single Source of Truth: Each piece of information has authoritative location
  • Referential Integrity: Foreign key constraints maintain consistency
  • Easier Maintenance: Changes made in one place

Implementation Steps

Step 1: Ensure 2NF

  • Verify table is in Second Normal Form
  • No partial dependencies exist

Step 2: Identify Transitive Dependencies

  • Map all functional dependencies
  • Look for patterns: A → B → C where A is primary key
  • Identify non-key attributes depending on other non-key attributes

Step 3: Create New Tables

  • Create separate table for each transitive dependency
  • Move transitively dependent attributes to new tables
  • Use intermediate attribute as primary key in new table

Step 4: Update Original Table

  • Remove transitively dependent attributes from original table
  • Keep intermediate attribute as foreign key
  • Maintain direct dependencies on primary key

Step 5: Verify Results

  • Ensure no transitive dependencies remain
  • Check all functional dependencies are direct
  • Validate referential integrity

Advanced Scenarios

Multiple Transitive Dependencies

Before:

Employee Table:
Emp_ID | Name | Dept_ID | Dept_Name | Manager_ID | Manager_Name | Project_ID | Project_Name

Dependencies:

  • Emp_ID → Dept_ID → Dept_Name
  • Emp_ID → Manager_ID → Manager_Name
  • Emp_ID → Project_ID → Project_Name

After (3NF):

Departments Table:
Dept_ID | Dept_Name

Managers Table:
Manager_ID | Manager_Name

Projects Table:
Project_ID | Project_Name

Employees Table:
Emp_ID | Name | Dept_ID | Manager_ID | Project_ID

Overlapping Dependencies

Complex Scenario:

Student_Course Table:
Student_ID | Course_ID | Instructor_ID | Room_ID | Building | Room_Capacity

Dependencies:

  • (Student_ID, Course_ID) → Instructor_ID
  • Course_ID → Room_ID
  • Room_ID → Building
  • Room_ID → Room_Capacity

Summary

  • Purpose: Eliminates transitive dependencies between non-key attributes
  • Requirement: Must be in 2NF first
  • Process: Move transitively dependent attributes to separate tables
  • Benefit: Further reduces redundancy, prevents more anomalies
  • Achievement: Most database designs stop at 3NF as it provides good balance
  • Next Step: Consider BCNF for more stringent requirements

Graph View

Backlinks