3NF Examples

Theory becomes mastery through practice. This final page applies everything we've learned about Third Normal Form—transitive dependencies, the formal definition, violation identification, and the synthesis algorithm—to real-world database design scenarios.

We'll work through complete examples from multiple business domains, showing the full journey from an unnormalized "universal relation" through analysis to a properly decomposed 3NF schema. These examples demonstrate how 3NF principles translate into practical database designs that eliminate redundancy while remaining efficient and maintainable.

Scenario: An online retailer needs a database to track orders, customers, products, and shipping.

Initial Universal Relation:

OrderData(OrderID, OrderDate, CustomerID, CustomerName, CustomerEmail, CustomerAddress, ProductID, ProductName, ProductCategory, CategoryDiscount, Quantity, UnitPrice, ShipperID, ShipperName, ShipperPhone, TrackingNumber)

Functional Dependencies Identified:

F = {
  OrderID → CustomerID, OrderDate, ShipperID, TrackingNumber
  CustomerID → CustomerName, CustomerEmail, CustomerAddress
  ProductID → ProductName, ProductCategory, UnitPrice
  ProductCategory → CategoryDiscount
  ShipperID → ShipperName, ShipperPhone
  {OrderID, ProductID} → Quantity
}

Candidate Key Analysis:

{OrderID, ProductID}⁺ = all attributes (one order can have multiple products)

Candidate Key: {OrderID, ProductID}

Applying 3NF Synthesis Algorithm:

Step 1: Canonical Cover (simplified—combining same left sides)

Fc = {
  OrderID → CustomerID, OrderDate, ShipperID, TrackingNumber
  CustomerID → CustomerName, CustomerEmail, CustomerAddress
  ProductID → ProductName, ProductCategory, UnitPrice
  ProductCategory → CategoryDiscount
  ShipperID → ShipperName, ShipperPhone
  {OrderID, ProductID} → Quantity
}

Step 2: Create Relations

Step 3: OrderItems contains candidate key {OrderID, ProductID} ✓

Step 4: No subsumptions to remove.

Scenario: A hospital needs to track patient visits, doctors, diagnoses, and treatments.

Initial Universal Relation:

PatientVisit(VisitID, VisitDate, PatientID, PatientName, PatientDOB, InsuranceID, InsuranceName, InsuranceCoverage, DoctorID, DoctorName, DoctorSpecialty, SpecialtyDept, DiagnosisCode, DiagnosisName, TreatmentID, TreatmentName, TreatmentCost)

Business Rules (Functional Dependencies):

F = {
  VisitID → PatientID, VisitDate, DoctorID
  PatientID → PatientName, PatientDOB, InsuranceID
  InsuranceID → InsuranceName, InsuranceCoverage
  DoctorID → DoctorName, DoctorSpecialty
  DoctorSpecialty → SpecialtyDept
  DiagnosisCode → DiagnosisName
  TreatmentID → TreatmentName, TreatmentCost
  {VisitID, DiagnosisCode} → TreatmentID  (each diagnosis in a visit has one treatment)
}

Candidate Key: {VisitID, DiagnosisCode} — A visit can have multiple diagnoses, each with its own treatment.

3NF Decomposition Result:

Scenario: A university tracks courses, sections, instructors, students, and enrollments.

Initial Universal Relation:

Registration(StudentID, StudentName, StudentMajor, MajorDept, CourseID, CourseName, CourseCredits, SectionID, Semester, InstructorID, InstructorName, InstructorOffice, RoomNumber, RoomCapacity, Grade)

Functional Dependencies:

F = {
  StudentID → StudentName, StudentMajor
  StudentMajor → MajorDept
  CourseID → CourseName, CourseCredits
  {CourseID, SectionID, Semester} → InstructorID, RoomNumber
  InstructorID → InstructorName, InstructorOffice
  RoomNumber → RoomCapacity
  {StudentID, CourseID, SectionID, Semester} → Grade
}

Candidate Key Analysis:

{StudentID, CourseID, SectionID, Semester}⁺ = all attributes

Candidate Key: {StudentID, CourseID, SectionID, Semester}

Scenario: A corporation tracks employees, departments, projects, skills, and assignments.

Initial Universal Relation:

Workforce(EmpID, EmpName, EmpAddress, DeptID, DeptName, DeptLocation, ManagerID, ManagerName, ProjectID, ProjectName, ProjectBudget, ProjectDeptID, SkillID, SkillName, SkillCategory, CategoryDesc, HoursWorked, SkillLevel)

Business Rules & Functional Dependencies:

F = {
  EmpID → EmpName, EmpAddress, DeptID
  DeptID → DeptName, DeptLocation, ManagerID
  ManagerID → ManagerName
  ProjectID → ProjectName, ProjectBudget, ProjectDeptID
  SkillID → SkillName, SkillCategory
  SkillCategory → CategoryDesc
  {EmpID, ProjectID} → HoursWorked
  {EmpID, SkillID} → SkillLevel
}

Note: This example has two types of many-to-many relationships:

• Employees work on Projects (with HoursWorked)
• Employees have Skills (with SkillLevel)

3NF Decomposition:

Special Design Note — Self-Referencing:

Notice that we do NOT create a separate Managers table. Since managers are employees, we use a self-referencing foreign key:

CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(100),
    DeptLocation VARCHAR(100),
    ManagerID INT REFERENCES Employees(EmpID)
);

This avoids duplicating employee information for managers—ManagerName comes from joining Departments with Employees on ManagerID.

After seeing multiple examples, clear patterns emerge in 3NF database design. Understanding these patterns accelerates future designs.

Let's quantify the benefits of 3NF normalization by comparing before and after scenarios.

Trade-off Analysis:

* The read performance advantage of unnormalized design is often overstated. Modern databases optimize joins efficiently, and caching can address hot paths. The maintenance and integrity costs of redundancy usually outweigh query performance gains.

After completing a 3NF decomposition, use this checklist to verify correctness:

Quick Verification Technique:

For each table in your decomposition, perform these rapid checks:

1. State what entity type each row represents (if unclear, table may be mixing entities)

2. For each non-key attribute, ask: "Does this describe the entity, or some other related entity?" (if other entity, split needed)

3. Try to construct an anomaly scenario: Can you update, insert, or delete in a way that creates inconsistency? (if yes, violation remains)

4. Write the original FDs and check each one: Can you verify each FD using only one table in your decomposition? (if no, dependency not preserved)