Database Normalization Explained with a Student Enrollment Example

A step-by-step HND-level example of moving from repeated course columns to linked tables using keys and relationships.

Database normalization is easier to understand when you solve a real data problem. Consider a college keeping student details and course enrollments in one table. We will identify repeated data, separate entities and check how the design avoids common update problems.

Understand the unnormalized example

Suppose one row contains student_id, student_name, course_1, course_2 and course_3. This forces a limit on the number of courses, creates many empty cells and becomes difficult to query when a student adds another course.

First Normal Form (1NF) asks us to store atomic values and avoid repeating groups. One row per student-course combination is a better starting structure: student_id, student_name, course_id, course_name and enrollment_date.

Remove partial dependencies for Second Normal Form

If the enrollment rows use a composite key (student_id, course_id), the student_name depends only on student_id; the course_name depends only on course_id. These values do not depend on the entire composite key.

To reach Second Normal Form in this example, store Students(student_id, student_name), Courses(course_id, course_name) and Enrollments(student_id, course_id, enrollment_date). The enrollment table references both other tables.

• Students — one row per student, keyed by student_id.
• Courses — one row per course, keyed by course_id.
• Enrollments — records a student's membership of a course.
• Foreign keys help prevent an enrollment referencing a nonexistent student or course.

Look for transitive dependencies

Third Normal Form (3NF) helps us avoid storing non-key values that depend on other non-key values. If Students also stores department_id and department_name, and department_name is determined by department_id, a Departments table may be appropriate.

Do not split every column into a table. The goal is to represent genuine entities and dependencies; a database design should remain easy to query and enforce the rules the application actually needs.

Test the design with operations

Check an INSERT: can you add a course before anyone enrolls? Check an UPDATE: if a course changes its name, is there only one row to change? Check a DELETE: if the last student leaves a course, is the course information still available?

Draw a small entity-relationship diagram and label the primary and foreign keys. Then write a simple JOIN query to produce a student timetable. This confirms the separated tables can still answer a real question.

Quick checklist

• I can identify repeated groups and atomic values.
• I know why partial dependencies can create duplication.
• Student, course and enrollment entities have clear keys.
• Foreign keys support the required relationships.
• Update, insert and delete anomalies were considered.
• I can join the normalized tables to answer a user question.

Always confirm current syllabus, coursework and assessment rules with your institute or lecturer.