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