Database DesignNormalizationLesson 24 / 69

Normalize a messy table

Exercise35 min

Normalize a messy table

Time to do it for real. Take this log table:

log(id, student, student_email, course, course_hours, lesson, score, graded_by, graded_by_email)

Walk it: 1NF — is anything a list? 2NF — what does the key not fully justify? 3NF — which non-key column names another?

The clean result is four tables (students, courses, lessons, graders) plus one facts table with foreign keys. Notice the surprise: normalized schemas answer questions the original could not even ask — "average score per instructor" falls out of the join.

Exercise

Normalize the log table above to 3NF. Then write, in one sentence each, two questions the new schema answers that the old one could not.

Practice in the diagram lab

This lesson's practice belongs in the diagram editor — design the schema you just read about, grade it against the solution, and save it to your account.

Open the diagram lab