Database Normalization — Case Study
Database Normalization
A Case Study: Student Course Enrollment System
Topics covered: anomalies, 1NF / 2NF / 3NF, ER diagrams, relational schema, SQL DDL, system
architecture
Page 1
Database Normalization — Case Study
Table of Contents
TOC \h \o "1-3"
Page 2
Database Normalization — Case Study
1. Case Study Background
A small university department tracks which students are enrolled in which courses, and which
instructor teaches each course. The records were originally kept in a single spreadsheet-style
table, shown below, before any normalization was applied. This document walks through why
that design causes problems, and how normalization (1NF → 2NF → 3NF) resolves them —
ending with a full relational schema, SQL implementation, and a simple system architecture for
an application built on top of it.
1.1 Original (Unnormalized) Table
Table name: Enrollment_Raw
StudentID StudentName CourseID CourseName InstructorName
1 Ali C101 Database Dr. Khan
1 Ali C102 Calculus Dr. Ahmed
2 Sara C101 Database Dr. Khan
Every column lives in one table, so any fact about a student, a course, or an instructor is
repeated once per enrollment row. This redundancy is the root cause of the three classic
anomalies.
2. Problem Statement: The Three Anomalies
2.1 Insertion Anomaly
A new course cannot be recorded until at least one student enrolls in it, because CourseID is
bundled into a row that requires a StudentID. Example: the department cannot add “C103 –
Networks, Dr. Ahmed” until someone signs up.
2.2 Update Anomaly
InstructorName is duplicated across every row for that instructor's courses. If “Dr. Khan” is
corrected to “Dr. M. Khan,” every matching row must be updated. Missing even one row leaves
the database internally inconsistent.
2.3 Deletion Anomaly
If Sara is the only student in C101 and her enrollment row is deleted, the table also loses the
only record that “C101 – Database” taught by “Dr. Khan” ever existed — even though that
course data is still valid.
3. Normalization Process
3.1 First Normal Form (1NF)
Requirement: all column values must be atomic, and each row must be unique.
Page 3
Database Normalization — Case Study
The Enrollment_Raw table already satisfies 1NF — no cell holds a list of values (e.g. no “C101,
C102” in one cell), and each row is distinguishable. 1NF is the starting point for the next two
steps.
3.2 Second Normal Form (2NF)
Requirement: must be in 1NF, and every non-key column must depend on the entire
composite primary key — not just part of it.
The natural primary key of Enrollment_Raw is the composite (StudentID, CourseID). Checking
each column:
• StudentName depends only on StudentID → partial dependency
• CourseName and InstructorName depend only on CourseID → partial dependency
Result: split into three tables.
Students (2NF)
StudentID (PK) StudentName
1 Ali
2 Sara
Courses_2NF (instructor name still embedded)
CourseID (PK) CourseName InstructorName
C101 Database Dr. Khan
C102 Calculus Dr. Ahmed
Enrollment (2NF)
StudentID (PK, FK) CourseID (PK, FK)
1 C101
1 C102
2 C101
3.3 Third Normal Form (3NF)
Requirement: must be in 2NF, and no non-key column may depend transitively on the
primary key through another non-key column.
Inside Courses_2NF: CourseID → InstructorName is not direct. In reality, CourseID →
InstructorID → InstructorName. InstructorName depends on the instructor, not on the course
itself — a transitive dependency. Instructors is split into its own table.
Page 4
Database Normalization — Case Study
Instructors (new, 3NF)
InstructorID (PK) InstructorName
101 Dr. Khan
102 Dr. Ahmed
Courses (3NF)
CourseID (PK) CourseName InstructorID (FK)
C101 Database 101
C102 Calculus 102
Students (unchanged) and Enrollment (unchanged)
Students and Enrollment carry over from 2NF without further change — neither table has a
transitive dependency.
3.4 Anomalies Resolved
Anomaly Before normalization After 3NF
INSERT INTO Courses needs no
Insertion Cannot add a course without a student
student row
Instructor name repeated in every Update Instructors once; Courses
Update
matching row references it by ID
Deleting the last enrollment erases Deleting an Enrollment row leaves
Deletion
course/instructor data Courses and Instructors intact
4. Entity-Relationship Design
The 3NF schema produces four entities with the following relationships:
• Instructors (1) — teaches — (M) Courses: one instructor can teach many courses
• Students (M) — enrolls in — (M) Courses, resolved via the Enrollment junction table
• Enrollment is a many-to-many resolver: one row = one student in one course
4.1 Relationship Summary
Relationship Cardinality Implementing table
Instructor → Course 1:M [Link] (FK)
Student ↔ Course M:N Enrollment (junction table)
Page 5
Database Normalization — Case Study
4.2 Entity Diagram (Text Form)
Instructors (1) ----teaches----> (M) Courses
|
| (1)
| includes
| (M)
Enrollment
| (M)
| enrolls
| (1)
Students
5. Final Relational Schema (3NF)
Table Column Type Constraint
Students StudentID INT PRIMARY KEY
StudentName VARCHAR(50) NOT NULL
Instructors InstructorID INT PRIMARY KEY
InstructorName VARCHAR(50) NOT NULL
Courses CourseID VARCHAR(10) PRIMARY KEY
CourseName VARCHAR(50) NOT NULL
FOREIGN KEY →
InstructorID INT
Instructors
PK (composite), FK →
Enrollment StudentID INT
Students
PK (composite), FK →
CourseID VARCHAR(10)
Courses
5.1 SQL DDL Implementation
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(50) NOT NULL
);
CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50) NOT NULL
);
CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50) NOT NULL,
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
Page 6
Database Normalization — Case Study
CREATE TABLE Enrollment (
StudentID INT,
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
5.2 Sample Data Population
INSERT INTO Students VALUES (1, 'Ali'), (2, 'Sara');
INSERT INTO Instructors VALUES (101, 'Dr. Khan'), (102, 'Dr. Ahmed');
INSERT INTO Courses VALUES
('C101', 'Database', 101),
('C102', 'Calculus', 102);
INSERT INTO Enrollment VALUES
(1, 'C101'), (1, 'C102'), (2, 'C101');
6. System Architecture
A typical application built on this schema follows a layered architecture, separating the database
from the application logic and the user interface. This keeps the normalized schema isolated
behind a data-access layer, so the rest of the system never has to deal with raw SQL or
redundant data directly.
6.1 Layered Architecture Overview
+-------------------------------------------------+
| Presentation Layer |
| (Student/Instructor Web Portal, Admin Panel) |
+--------------------------+------------------------+
| HTTP / REST API
+--------------------------v------------------------+
| Application Layer |
| - EnrollmentService (insert/update/delete) |
| - CourseService (manage course catalog) |
| - InstructorService (manage instructor records) |
+--------------------------+------------------------+
| SQL queries / ORM calls
+--------------------------v------------------------+
| Data Access Layer |
| (Repository pattern / ORM mappings) |
+--------------------------+------------------------+
|
+--------------------------v------------------------+
| Database Layer (3NF) |
Page 7
Database Normalization — Case Study
| Students | Instructors | Courses | Enrollment |
+-----------------------------------------------------+
6.2 Layer Responsibilities
Layer Responsibility
UI for students to enroll, instructors to view rosters, admins to manage
Presentation
courses
Business rules — e.g. prevent duplicate enrollment, validate course
Application
capacity
Data Access Translates service calls into SQL against the normalized schema
Database Stores normalized data; enforces integrity via PK/FK constraints
6.3 Why Normalization Matters at the Architecture Level
• Data integrity is enforced by the database itself (FK constraints), reducing validation
logic needed in the application layer
• Each service maps cleanly to one table/entity, simplifying the data access layer
• Changes to instructor or course data only require a single UPDATE, regardless of how
many students are affected
• The schema can scale to more entities (e.g. Departments, Semesters) by extending
relationships, without restructuring existing tables
7. Conclusion
Starting from a single denormalized table, applying 2NF removed partial dependencies on the
composite key, and applying 3NF removed the transitive dependency between course and
instructor data. The result is a four-table schema — Students, Instructors, Courses, and
Enrollment — that eliminates all three classic anomalies, enforces integrity through foreign keys,
and provides a clean foundation for a layered application architecture.
Page 8