MySQL + SQL · Lesson 1
Normalization In Dbms
Normalization Step by Step
Let us normalize one messy table through 1NF, 2NF and 3NF using a student-course example.
Unnormalized Table
student(roll, name, subjects, teacher, teacher_phone)
1, Aman, "Math,Science", Mr.Rao, 99999Problems: multiple subjects in one cell, repeated teacher info.
1NF — Atomic Values
student(roll, name, subject, teacher, teacher_phone)
1, Aman, Math, Mr.Rao, 99999
1, Aman, Science, Ms.Sen, 888882NF — Remove Partial Dependency
Split so non-key columns depend on the whole key:
enrollment(roll, subject)
subject_info(subject, teacher, teacher_phone)3NF — Remove Transitive Dependency
teacher_phone depends on teacher, not subject — separate it:
subject_info(subject, teacher)
teacher(teacher, teacher_phone)Summary
- 1NF: atomic values. 2NF: no partial dependency. 3NF: no transitive dependency.
- Each step splits the table to remove a specific problem.