Third Normal Form (3NF)

Sub-Page 3: Eliminating Transitive Dependencies

What is 3NF?

Third Normal Form (3NF) builds on 2NF by ensuring no transitive dependencies: non-key attributes must not depend on other non-key attributes. A table is in 3NF if it is in 2NF and every non-key attribute is directly dependent on the primary key.

Transitive dependency: A → B, B → C implies A → C, where C is a non-key attribute depending on another non-key attribute B.

Example Table (in 2NF):

StudentID	Name	Department	DeptHead
101	Alice	Computer Science	Dr. Smith
102	Bob	Mathematics	Dr. Jones

Transitive dependency: StudentID → Department, Department → DeptHead, so StudentID → DeptHead indirectly.

3NF Real-World Scenarios

Scenario 1: University Student Records

StudentID	Name	Department	DeptHead
101	Alice	Computer Science	Dr. Smith
102	Bob	Mathematics	Dr. Jones

Transitive dependency: Department → DeptHead, not directly on StudentID. Updating DeptHead requires updating multiple rows, risking inconsistencies.

3NF Solution

Split into Students and Departments tables to store department information once.

Students(StudentID, Name, Department)

Departments(Department, DeptHead)

Scenario 2: Corporate Employee Assignments

EmployeeID	Name	OfficeID	OfficeLocation
E1	John	O1	Building A
E2	Jane	O2	Building B

Transitive dependency: OfficeID → OfficeLocation, not directly on EmployeeID. OfficeLocation changes require multiple updates.

3NF Solution

Split into Employees and Offices tables to centralize office details.

Employees(EmployeeID, Name, OfficeID)

Offices(OfficeID, OfficeLocation)

Scenario 3: E-Commerce Customer Orders

OrderID	CustomerID	CustomerName	Region	RegionManager
1	C101	Alice	East	Mgr. Lee
2	C102	Bob	West	Mgr. Smith

Transitive dependency: Region → RegionManager, not directly on OrderID. Updating RegionManager requires multiple row updates.

3NF Solution

Split into Orders, Customers, and Regions tables.

Orders(OrderID, CustomerID)

Customers(CustomerID, CustomerName, Region)

Regions(Region, RegionManager)

Scenario 4: Library Book Loans

LoanID	BookID	Title	Publisher	PublisherCity
L1	B101	Database Systems	Pearson	New York
L2	B102	SQL Guide	O'Reilly	Boston

Transitive dependency: Publisher → PublisherCity, not directly on LoanID. PublisherCity changes require multiple updates.

3NF Solution

Split into Loans, Books, and Publishers tables.

Loans(LoanID, BookID)

Books(BookID, Title, Publisher)

Publishers(Publisher, PublisherCity)

SQL for 3NF

-- SQL for Scenario 1: University Student Records
CREATE TABLE Departments (
    Department VARCHAR(50) PRIMARY KEY,
    DeptHead VARCHAR(50)
);
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Department VARCHAR(50),
    FOREIGN KEY (Department) REFERENCES Departments(Department)
);

-- SQL for Scenario 2: Corporate Employee Assignments
CREATE TABLE Offices (
    OfficeID VARCHAR(10) PRIMARY KEY,
    OfficeLocation VARCHAR(50)
);
CREATE TABLE Employees (
    EmployeeID VARCHAR(10) PRIMARY KEY,
    Name VARCHAR(50),
    OfficeID VARCHAR(10),
    FOREIGN KEY (OfficeID) REFERENCES Offices(OfficeID)
);

-- SQL for Scenario 3: E-Commerce Customer Orders
CREATE TABLE Regions (
    Region VARCHAR(50) PRIMARY KEY,
    RegionManager VARCHAR(50)
);
CREATE TABLE Customers (
    CustomerID VARCHAR(10) PRIMARY KEY,
    CustomerName VARCHAR(50),
    Region VARCHAR(50),
    FOREIGN KEY (Region) REFERENCES Regions(Region)
);
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID VARCHAR(10),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- SQL for Scenario 4: Library Book Loans
CREATE TABLE Publishers (
    Publisher VARCHAR(50) PRIMARY KEY,
    PublisherCity VARCHAR(50)
);
CREATE TABLE Books (
    BookID VARCHAR(10) PRIMARY KEY,
    Title VARCHAR(100),
    Publisher VARCHAR(50),
    FOREIGN KEY (Publisher) REFERENCES Publishers(Publisher)
);
CREATE TABLE Loans (
    LoanID VARCHAR(10) PRIMARY KEY,
    BookID VARCHAR(10),
    FOREIGN KEY (BookID) REFERENCES Books(BookID)
);

These schemas eliminate transitive dependencies by isolating non-key attributes into separate tables with appropriate foreign key constraints.

Key Takeaways

Transitive Dependency

3NF removes dependencies where non-key attributes depend on other non-key attributes.

Direct Dependency

Non-key attributes must depend only on the primary key.

Foundation for BCNF

3NF sets the stage for stricter normalization rules like BCNF.

Back to 2NF Next: BCNF