MySQL + SQL · Lesson 1
Normalization In Dbms
Normalization चरण-दर-चरण
आइए एक messy table को student-course example से 1NF, 2NF और 3NF तक normalize करें।
Unnormalized Table
student(roll, name, subjects, teacher, teacher_phone)
1, Aman, "Math,Science", Mr.Rao, 99999समस्याएं: एक cell में कई subjects, teacher info repeat।
1NF — Atomic Values
student(roll, name, subject, teacher, teacher_phone)
1, Aman, Math, Mr.Rao, 99999
1, Aman, Science, Ms.Sen, 888882NF — Partial Dependency हटाएं
ऐसे split करें कि non-key columns पूरी key पर निर्भर हों:
enrollment(roll, subject)
subject_info(subject, teacher, teacher_phone)3NF — Transitive Dependency हटाएं
teacher_phone, teacher पर निर्भर है, subject पर नहीं — अलग करें:
subject_info(subject, teacher)
teacher(teacher, teacher_phone)सारांश
- 1NF: atomic values। 2NF: partial dependency नहीं। 3NF: transitive dependency नहीं।
- हर step एक specific समस्या हटाने के लिए table split करता है।