Constructing a Database Normalized to Third Normal Form (3NF)

The Big Idea

To construct a database normalized to Third Normal Form (3NF) means to design a schema in which data is logically grouped, redundancy is eliminated, and integrity is enforced. This is done by applying normalization rules step by step—1NF, 2NF, and finally 3NF—to remove structural flaws like duplicate data, partial dependencies, and transitive dependencies.

In this article, we will:

• Explain how to construct a 3NF database schema,
• Walk through three real-world scenarios (library, school, and e-commerce),
• Show how data is split across tables using primary, foreign, and composite keys,
• Emphasize functional dependencies and the removal of anomalies.

---

Step-by-Step: How to Normalize a Database to 3NF

Step 1: Requirements Gathering

• Identify entities (real-world objects like Book, Student, Order)
• Identify attributes for each entity
• Determine functional dependencies (which attributes depend on which keys)

Step 2: Apply First Normal Form (1NF)

• Ensure atomicity: all attributes hold indivisible values.
• Remove repeating groups: every field holds one value per record.

Step 3: Apply Second Normal Form (2NF)

• Ensure the table is in 1NF.
• Remove partial dependencies: non-key attributes must depend on the whole primary key (especially important if the key is composite).

Step 4: Apply Third Normal Form (3NF)

• Ensure the table is in 2NF.
• Remove transitive dependencies: non-key attributes must depend only on the primary key, not on another non-key attribute.

---

Scenario 1: Library Management System

Unnormalized Data:

Borrower_ID, Borrower_Name, Book_ID, Book_Title, Book_Author, Issue_Date, Return_Date

This has redundancy: multiple books per borrower and repeated book details.

---

3NF Design:

Borrowers

borrower_id	name
1	Alice Smith

Books

book_id	title	author
10	The Hobbit	J.R.R. Tolkien

Borrowings

borrowing_id	borrower_id	book_id	issue_date	return_date
1001	1	10	2025-05-01	2025-05-15

Key Notes:

• Books.title and Books.author are functionally dependent on book_id (not duplicated).
• Borrowings uses foreign keys to connect books and borrowers.
• Data is fully normalized—no partial or transitive dependencies remain.

---

Scenario 2: School Management System

Raw Data (not normalized):

Student_ID, Student_Name, Class_ID, Class_Name, Teacher_ID, Teacher_Name

There are transitive dependencies here:

• Teacher_Name depends on Teacher_ID
• Class_Name depends on Class_ID

---

3NF Design:

Students

student_id	name
S001	Luke Adams

Classes

class_id	class_name	teacher_id
C01	Chemistry 101	T001

Teachers

teacher_id	name
T001	Dr. Green

Enrollments

enrollment_id	student_id	class_id
1	S001	C01

Functional Dependencies:

• class_name depends on class_id
• teacher_id → teacher_name
• All transitive dependencies are resolved.

---

Scenario 3: E-Commerce Platform

Initial Table:

Order_ID, Customer_Name, Product_Name, Product_Price, Quantity, Order_Date

Here:

• Product_Price depends on Product_Name
• Customer_Name depends on Order_ID indirectly
• Multiple orders → duplicated customer and product data

---

3NF Schema:

Customers

customer_id	name
C1001	Maria Lopez

Products

product_id	name	price
P101	Headphones	89.99

Orders

order_id	customer_id	order_date
O5001	C1001	2025-05-25

OrderDetails

order_id	product_id	quantity
O5001	P101	2

Normalization Results:

• Customer and product info stored once
• Price is not duplicated; tied to product_id
• Quantity and order relations are cleanly defined via a junction table

---

Great observation—and a fair question.

I prioritized three scenarios—library, school, and e-commerce—to provide distinct structural examples: lending systems, class-based enrollments, and transactional commerce. Each demonstrated different kinds of entity relationships, composite keys, and normalization challenges.

But you're absolutely right: police crime reporting is one of the listed scenarios in the specification, and it's rich with real-world normalization issues, especially involving multi-valued dependencies, event logging, and role-based relationships.

Here’s a full 3NF-normalized schema for a police crime reporting system:

---

Scenario 4: Police Crime Reporting System

Raw (Unnormalized) Data:

Report_ID, Date, Location, Officer_Name, Officer_Badge, Suspect_Name, Suspect_ID, Crime_Type, Crime_Description, Evidence_List

Problems:

• Officer_Name depends on Officer_Badge (transitive dependency)
• Evidence_List is likely a repeating group (violates atomicity)
• Suspect_Name depends on Suspect_ID
• Reports may involve multiple suspects and multiple pieces of evidence

---

Normalized to 3NF

Officers

officer_id	name	badge_number
O101	John Harris	B-556

Suspects

suspect_id	name
S301	Maria Lopez

Crimes

crime_id	type	description
C205	Burglary	Forced entry through back window

Reports

report_id	crime_id	officer_id	report_date	location
R5001	C205	O101	2025-05-26	123 Elm St.

Report_Suspects

report_id	suspect_id
R5001	S301

Evidence

evidence_id	description
E9001	Broken glass fragments
E9002	Footprints under window

Report_Evidence

report_id	evidence_id
R5001	E9001
R5001	E9002

---

Highlights of the Design

• No repeating groups (evidence and suspects handled with junction tables)
• No transitive dependencies (officer name and badge handled via Officer table)
• Each entity has a single primary key, and all foreign keys match the referenced tables
• Report connects officer, crime, location, and timestamp (many-to-many relations handled via associative tables)

Why Normalize to 3NF?

• Avoids duplication: product names, teacher names, and author names are stored once.
• Prevents update anomalies: changing a book's title or product price happens in one place.
• Ensures referential integrity: foreign keys enforce valid relationships.
• Makes queries faster and more reliable by reducing unnecessary joins and duplicates.

---

Summary

To construct a 3NF database:

• Start from real-world data
• Identify entities and relationships
• Normalize progressively from 1NF → 2NF → 3NF
• Use primary keys to uniquely identify records and foreign keys to link related data
• Eliminate partial and transitive dependencies

Real-world applications like library management, school systems, and e-commerce platforms benefit greatly from this structured, rule-based approach to schema design. A properly normalized database leads to improved consistency, scalability, and long-term maintainability.