Database Normalization in Livesql

Normalization is one of the most important concepts in database design. In this blog, we’ll walk through normalizing a student-course-instructor dataset step by step — from 1NF to 3NF — using SQL CREATE TABLE and INSERT INTO statements.

Identify Anomalies

Insertion anomaly:

Can’t insert a new course unless a student enrolls.

Update anomaly:

If Dr. Kumar’s phone number changes, we must update multiple rows.

Deletion anomaly:

If student S02 drops the course, we lose info about the DBMS course entirely.

Convert to 1NF (First Normal Form)

Remove repeating groups, ensure atomic values.

Creation of table

Insertion of data

Output

Convert to 2NF (Second Normal Form)

Remove partial dependency (non-key attributes should depend on the whole key).

Create separate tables for Students, Courses, Instructors, Studentcourses.

Create table for Students

Insert values into the table

Output

Create table for Courses

Insertion of values

Output

Create table for Instructors

Insertion of values

Output

Create table for Studentcourses

Insertion of values

Output

Convert to 3NF (Third Normal Form)

Remove transitive dependency (non-key attributes shouldn’t depend on other non-keys).

👉 In our case, the InstructorPhone depends on Instructor, not directly on Course.

So we already separated Instructors, which makes this 3NF compliant.

Final tables in 3NF:

• Students(StudentID, StudentName)


• Instructors(Instructor, InstructorPhone)


• Courses(CourseID, CourseName, Instructor)


• StudentCourses(StudentID, CourseID)

Query Using JOINs

Simple Breakdown

By breaking down our initial table step by step:

• 1NF → Atomic values


• 2NF → Remove partial dependencies


• 3NF → Remove transitive dependencies

We achieved a clean, normalized schema that eliminates anomalies and improves consistency.