Normalization — Step by Step Fix

⚠ starting point

Meet the broken table — student_courses

A university database tracks which students take which courses, who teaches them, and grades. Someone crammed everything into one table. It breaks all three normal forms.

student_id	course_id	student_name	student_city	course_name	instructor	instructor_office	grades
S01	C01	Ramesh	Kathmandu	Database	Mr. Sharma	Room 201	midterm:A, final:A+
S01	C02	Ramesh	Kathmandu	Python	Ms. Rai	Room 305	midterm:B, final:B+
S02	C01	Sita	Pokhara	Database	Mr. Sharma	Room 201	midterm:A+, final:A
S02	C02	Sita	Pokhara	Python	Ms. Rai	Room 305	midterm:B, final:A
S03	C01	Bikash	Dharan	Database	Mr. Sharma	Room 201	midterm:C, final:B

Primary key: (student_id, course_id) — composite key, because one student can take many courses.

🔴 1NF violation — The grades column stores multiple values: "midterm:A, final:A+". That's a comma-separated list in a single cell, not an atomic value.

🟡 2NF violation — student_name and student_city depend only on student_id, not on the full (student_id, course_id) key. Same for course_name and instructor — they depend only on course_id. These are partial dependencies.

🔴 3NF violation — instructor_office depends on instructor, not on the primary key. Knowing the instructor tells you their office — a transitive dependency.

"Before I show the fix — look at the highlighted cells. Can you spot which columns cause which violation?"

① first normal form

Fix 1NF — primary key + atomic values

1NF has two requirements: the table must have a primary key, and every cell must hold exactly one atomic value. We handle both — first identify the key, then fix the multi-valued grades column.

Part 1 — identify the primary key

Ask: which column(s) uniquely identify every row?

A primary key is the minimum set of columns where no two rows ever share the same combination of values. Test each candidate against the actual data — look for duplicates:

✗ student_id alone?

S01 ← appears twice

S01 ← duplicate

S02

S03

Ramesh takes 2 courses — S01 repeats. Not unique. ✗

✗ course_id alone?

C01 ← appears 3×

C02

C01 ← duplicate

C02

C01 ← duplicate

3 students take Database — C01 repeats. Not unique. ✗

✓ (student_id, course_id)?

S01 + C01 — unique

S01 + C02 — unique

S02 + C01 — unique

S02 + C02 — unique

S03 + C01 — unique

Every pair is distinct. This is the primary key — a composite key. ✓

📌 Why a composite key? A composite key signals that this table represents a relationship between two entities — student and course. One student can enroll in many courses; one course can have many students. Neither alone is enough to pinpoint a specific row. You'll see why this matters in the 2NF step.

Part 2 — fix the non-atomic value

Now the second 1NF rule: every cell must hold exactly one value. The grades column stores "midterm:A, final:A+" — two values crammed into one cell. You can't query a specific exam without string parsing.

✗ Before — multi-valued cell

student	course	grades
S01	C01	midterm:A, final:A+
S01	C02	midterm:B, final:B+

Can't query "find students who got A in the midterm" without string parsing.

✓ After — separate columns

student	course	midterm_grade	final_grade
S01	C01	A	A+
S01	C02	B	B+

Each grade is its own column — a single atomic value per cell.

① What we did

Split grades into midterm_grade and final_grade — each holds one value. Now you can write WHERE midterm_grade = 'A' without string manipulation.

💡 Alternative approach: If the number of grade types is dynamic (quizzes, assignments, labs…), a separate grades table with rows like (S01, C01, 'midterm', 'A') would be better. For a fixed two-exam system, two columns is simpler.

✓ 1NF fixed

✗ 2NF still broken

✗ 3NF still broken

Table after 1NF fix

student_id	course_id	student_name	student_city	course_name	instructor	instructor_office	midterm_grade	final_grade
S01	C01	Ramesh	Kathmandu	Database	Mr. Sharma	Room 201	A	A+
S01	C02	Ramesh	Kathmandu	Python	Ms. Rai	Room 305	B	B+
S02	C01	Sita	Pokhara	Database	Mr. Sharma	Room 201	A+	A
S02	C02	Sita	Pokhara	Python	Ms. Rai	Room 305	B	A
S03	C01	Bikash	Dharan	Database	Mr. Sharma	Room 201	C	B

✓ grades fixed. But the yellow and red columns still have dependency problems — let's fix those next.

② second normal form

Fix 2NF — remove partial dependencies

The PK is (student_id, course_id). But student_name depends only on student_id, and course_name depends only on course_id. They each depend on part of the key — not the whole key.

The partial dependencies

Partial dep 1 student_id → student_name, student_city

student_id (part of PK)

→

student_name

student_city

Ramesh's name and city are the same regardless of which course he's in. They depend on the student, not the enrollment.

Partial dep 2 course_id → course_name, instructor

course_id (part of PK)

→

course_name

instructor

"Database" is taught by Mr. Sharma regardless of which student is enrolled. These belong to the course, not the enrollment.

The fix — extract into separate tables

students — NEW

student_idPK

student_name

student_city

courses — NEW

course_idPK

course_name

instructor

instructor_office⚠ 3NF issue

enrollments — CLEANED

student_idPK, FK

course_idPK, FK

midterm_grade

final_grade

✓ Ramesh's name now exists in exactly one row in students. "Database" exists once in courses. Enrollments only stores what's genuinely per-enrollment: the grades.

✓ 1NF fixed

✓ 2NF fixed

✗ 3NF still broken — instructor_office in courses

⚠ Look at the courses table. instructor_office depends on instructor, not on course_id. If Mr. Sharma moves offices, we'd have to update every course he teaches. That's a transitive dependency — we'll fix it in the next step.

③ third normal form

Fix 3NF — remove transitive dependencies

In the courses table, instructor_office doesn't depend on course_id — it depends on instructor. That's a transitive dependency: course_id → instructor → instructor_office.

Transitive dependency

course_id (PK)

→

instructor

→

instructor_office

The office belongs to the instructor — not to the course. If Mr. Sharma teaches 5 courses, his office "Room 201" is duplicated 5 times. If he moves to Room 310, you must update 5 rows.

The fix — extract instructors into their own table

✗ Before — office inside courses

course_id	course_name	instructor	instructor_office
C01	Database	Mr. Sharma	Room 201
C02	Python	Ms. Rai	Room 305
C03	Statistics	Mr. Sharma	Room 201

"Room 201" repeated for every course Mr. Sharma teaches.

✓ After — separate instructors table

courses

course_id	course_name	instructor_id
C01	Database	I01
C02	Python	I02
C03	Statistics	I01

instructors

instructor_id	name	office
I01	Mr. Sharma	Room 201
I02	Ms. Rai	Room 305

Office exists once per instructor — update one row to change it.

✓ 1NF fixed

✓ 2NF fixed

✓ 3NF fixed

✓ Every non-key column now depends on the key, the whole key, and nothing but the key. No redundancy. No anomalies.

✓ fully normalized

The final schema — 4 clean tables

From one broken table to four clean ones. Each entity has its own table. Each fact exists in exactly one place.

students

student_idPK

student_nameTEXT

student_cityTEXT

instructors

instructor_idPK

nameTEXT

officeTEXT

courses

course_idPK

course_nameTEXT

instructor_idFK

enrollments

student_idPK, FK

course_idPK, FK

midterm_gradeCHAR(2)

final_gradeCHAR(2)

Test the three anomalies — all gone

✓ Update — Mr. Sharma moves offices

Change one row in instructors: UPDATE instructors SET office = 'Room 310' WHERE instructor_id = 'I01'. Every course he teaches automatically reflects the new office via JOIN. Zero duplication.

✓ Insert — new student with no enrollments yet

INSERT INTO students VALUES ('S04', 'Anita', 'Biratnagar') — done. No enrollment needed. The student exists as an entity independently of any course.

✓ Delete — Bikash drops the Database course

DELETE FROM enrollments WHERE student_id = 'S03' AND course_id = 'C01'. Bikash's student record survives. The Database course survives. Only the enrollment link is removed.

The full journey — one summary

Step	Problem	Fix	Tables after
1NF	`grades` column has comma-separated values	Split into `midterm_grade` + `final_grade`	1 table
2NF	student_name depends on student_id only (partial dep)	Extract `students` and `courses` tables	3 tables
3NF	instructor_office depends on instructor (transitive dep)	Extract `instructors` table	4 tables

🎯 The key insight: each fix follows the same pattern — find columns that don't depend on the full PK, extract the entity they do depend on into its own table, and replace with a foreign key reference. That's all normalization is.

"Now map this back to our rides table. The pattern is the same — driver_name is like instructor, the rides table is like enrollments. What tables would you extract?"

→ apply to your project

Same pattern — apply to the rides table

The student_courses example was the practice. Your rides table is the real thing. The same dependency analysis, the same extraction pattern.

University example

Entity hidden in the flat table: instructor

Symptom: instructor_office repeats per course

Fix: extract instructors table, reference via FK

Result: 4 tables (students, instructors, courses, enrollments)

Your rides table

Entity hidden in the flat table: driver, rider, location

Symptom: driver_name repeats per ride, casing splits data

Fix: extract drivers, riders, locations — reference via FK

Result: 4 tables (drivers, riders, locations, trips)

💡 The pattern is always the same: find the real-world entity hiding inside the flat table → give it its own table with a primary key → replace the repeated text with a foreign key integer. One table per entity. One row per fact.

🎯 On Day 2, you'll actually build the normalized rides schema in PostgreSQL, migrate the data using the string-cleaning functions from the cheat sheet, and write JOINs to query across all four tables.

Fix a broken table — 1NF, 2NF, 3NF