Normalization

1. Chapter 3 Normalization SUCHADA PUNNOI Dept. of Computer Science & Information Technology http://computer.pcru.ac.th/suchada/

2. Normalization การตรวจสอบความถูกต้องของการออกแบบระบบฐานข้อมูล ต้องมีขบวนการการตรวจสอบอย่างเป็นขั้นตอน เราเรียกขบวนการนี้ว่า Normalization. Normalization is a formal method involved with a series of test to help database designer to be able to identify the optimal grouping of attributes for each relation in the relational schema. Normalization can be applied to individual relations so that database can be normalized to a specific form to prevent the possible occurrence of update anomaly.

3. Data Redundancy and Update Anomalies วัตถุประสงค์หลักของการออกแบบระบบฐานข้อมูลคือ การจัดกลุ่ม attributes ในแต่ละรีเลชั่นเพื่อลดความซ้ำซ้อนของข้อมูล ซึ่งส่งผลถึงการประหยัดเนื้อที่ในการจัดเก็บฐานข้อมูลด้วย รีเลชั่นที่มีข้อมูลซ้ำซ้อนมาก ก่อปัญหา UPDATE ANOMALIES ซึ่งแบ่งออกเป็น 3 ประเภทคือ Insertion anomalies Deletion Anomalies Modification Anomalies

4. Insertion Anomalies Deletion Anomalies Modification Anomalies

5. Insertion Anomalies To insert the details of new members of staff into the Staff_Branch relation, we must include the details of the branch at which the staff are to be located. Deletion Anomalies If we delete a row from the Staff_Branch relation that represents the last member of staff located at a branch, the details about that branch are also lost from the database. Modification Anomalies If we want to change the value of one of the attributes of a particular branch in the Staff_Branch relation, we must update the rows of all staff located at that branch. If this modification is not carried out on all the appropriate rows of the Staff_Branch relation, the data base will become inconsistent.

6. Staff_Branch Relation

7. Delete Anomalies Staff_Branch Relation

8. Modification Anomalies Staff_Branch Relation

9. ความสัมพันธ์ระหว่างแอททริบิวต์ในแต่ละรีเลชันความสัมพันธ์ระหว่างแอททริบิวต์ในแต่ละรีเลชัน • ความสัมพันธ์ระหว่างแอททริบิวต์ในแต่ละรีเลชันอาจจำแนกได้ 3 ลักษณะ • ความสัมพันธ์ระหว่าง แอททริบิวต์แบบฟังก์ชัน (functional dependency) • ความสัมพันธ์ระหว่างแอททริบิวต์แบบหลายค่า (multivalued dependency) • ความสัมพันธ์ระหว่างแอททริบิวต์แบบ Join (join dependency)

10. A B Functional Dependencies One of the main concepts associated with normalization is functional dependency, which describes the relationship between attributes. ความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชัน หมายถึง การที่ค่าของแอททริบิวต์หนึ่งหรือมากกว่าหนึ่งแอททริบิวต์ขึ้นไปที่ประกอบกันสามารถระบุค่าของแอททริบิวต์อื่น ๆ ในทูเพิลเดียวกันของ รีเลชันนั้นได้ โดยแอททริบิวต์หรือกลุ่มของแอททริบิวต์ที่เป็นตัวระบุค่าของแอททริบิวต์อื่น ๆ เรียกว่า Determinant และแอททริบิวต์อื่น ๆ ที่ถูกระบุค่า เรียกว่า Dependent

11. B is functionally dependent on A A B Position is functionally dependent on Staff_No Staff_No Position SL21 System Engineer Staff_No is not functionally dependent on Position Position Staff_No SL21 SG5 System Engineer

12. Functional Dependencies • ความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันสามารถจำแนกได้ 3 แบบ คือ • ความสัมพันธ์ระหว่างแอททริบิวต์แบบทั้งหมด (fully functional dependency) • ความสัมพันธ์ระหว่างแอททริบิวต์แบบ บางส่วน (partial dependency) • ความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive (transitive dependency)

13. fully functional dependency • ความสัมพันธ์ระหว่างแอททริบิวต์แบบทั้งหมด • หมายถึง การที่แอททริบิวต์ซึ่งเป็น Determinant มีขนาดเล็กที่สุด และสามารถระบุค่าของแอททริบิวต์อื่น ๆ ที่เป็น Dependent ได้อย่างชัดเจน • รหัสนักศึกษา ชื่อ , สกุล, เพศ

14. รหัสชุดวิชา, หมู่เรียน ชื่ออาจารย์ผู้สอน

15. partial dependency ความสัมพันธ์ระหว่างแอททริบิวต์แบบบางส่วน จะเกิดขึ้นได้เมื่อ รีเลชันหนึ่ง ๆ มีคีย์หลักเป็นคีย์ผสม และแอททริบิวต์บางส่วนของคีย์หลักสามารถระบุค่าของแอททริบิวต์อื่น ๆ ในทูเพิลเดียวกันที่ไม่ใช่คีย์หลัก (non-key attribute) ของรีเลชันได้ นั่นคือ หากทราบค่าใดค่าหนึ่งของบางแอททริบิวต์ที่เป็น Determinant ก็จะทำให้ทราบค่าของแอททริบิวต์อื่น ๆ ที่เป็น Dependent ได้

16. รีเลชันการลงทะเบียน

17. transitive dependency • แม้ว่าแอททริบิวต์ที่มีคุณสมบัติเป็นคีย์หลักจะสามารถระบุค่าของ • แอททริบิวต์อื่น ๆ ในทูเพิลเดียวกันของรีเลชันได้ แต่ทว่าในบางรีเลชัน • ที่มีการออกแบบไม่เหมาะสม อาจมีแอททริบิวต์อื่นที่ไม่ใช่คีย์หลักแต่ • สามารถระบุค่าของแอททริบิวต์อื่น ๆ ในทูเพิลเดียวกันของรีเลชันได้เช่นกัน ลักษณะของความสัมพันธ์ดังกล่าวนี้เรียกว่า ความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive

18. transitive dependency • รีเลชันที่ปรึกษา

19. Normalization • Normal Form มีอยู่ 3 ระดับด้วยกัน คือ 1. Normal Form ระดับที่ 1 หรือเรียกว่า 1NF 2. Normal Form ระดับที่ 2 หรือเรียกว่า 2NF 3. Normal Form ระดับที่ 3 หรือเรียกว่า 3NF

20. Normalization (ต่อ) • นอกจากนี้ยังมีระดับที่ทำให้ Normal Form ระดับที่ 3 มีความแข็งแกร่งขึ้นกว่าเดิม เรียกว่า BCNF ( Boyce-Codd Normal Form) ซึ่งพัฒนาขึ้นโดย R.Boyce และ E.F.Codd โดย Normal Form ทุกระดับตั้งอยู่บนพื้นฐานของฟังก์ชันการขึ้นต่อกันระหว่างแอททริบิวต์ของ Relation( Functional Dependency ) • Normal Form ในระดับที่สูงขึ้นไปอีกที่อยู่ถัดจาก BCNF ก็ได้ถูกพัฒนาขึ้น คือ Normal Form ระดับที่ 4 ( 4NF) และระดับที่ 5 (5NF) ซึ่งพัฒนาโดย Fagin(1977,1979) อย่างไรก็ตามรูปแบบ 4NF และ 5NF ในทางปฏิบัติถือว่าเกิดขึ้นได้ยากมาก

21. กระบวนการ Normalization Steps in normalization UNF

22. Relationships of Normal Forms Higher Normal forms

23. การทำให้เป็นรูปแบบที่เป็นบรรทัดฐาน : • รูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 • รูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 (First Normal Form : 1NF) เป็นขั้นตอนแรกสุดในการจัดทำรีเลชันให้เป็นรูปแบบที่เป็นบรรทัดฐาน • 1. คุณสมบัติของรูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 • รีเลชันใด ๆ จะมีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 ก็ต่อเมื่อทุกแอททริบิวต์ในแต่ละ ทูเพิลมีค่าของข้อมูลเพียงค่าเดียว • 2. ขั้นตอนการจัดทำรีเลชันให้เป็นรูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 • เพื่อแสดงให้เห็นถึงการจัดทำรีเลชันให้เป็นรูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 ในที่นี้ขอนำตัวอย่าง รีเลชันซึ่งไม่อยู่ในรูปแบบที่เป็นบรรทัดฐานมาประกอบดังนี้

26. รูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 • รูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 (Second Normal Form : 2NF) เป็นขั้นตอนถัดมาจากการจัดทำ รีเลชันให้อยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 ซึ่งเป็นขั้นตอนที่ยังไม่มีการวิเคราะห์ความสัมพันธ์ระหว่าง แอททริบิวต์ • การจัดทำรีเลชันให้มีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 จะเป็นการตรวจสอบเพื่อแก้ไขปัญหาความซ้ำซ้อนของข้อมูลที่ปรากฏในรีเลชันที่มีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 โดยให้ความสำคัญในเรื่องของคีย์หลักและความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชัน โดยเฉพาะอย่างยิ่ง ความสัมพันธ์ระหว่างแอททริบิวต์แบบทั้งหมด

27. 1. คุณสมบัติของรูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 • รีเลชันใด ๆ จะมีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 ก็ต่อเมื่อ • 1) รีเลชันนั้นมีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 1 แล้ว • 2) ทุกแอททริบิวต์ที่ไม่ใช่คีย์หลักต้องมีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันกับคีย์หลักหรือแอททริบิวต์ที่ประกอบกันเป็นคีย์หลัก ซึ่งความสัมพันธ์ดังกล่าวเป็นความสัมพันธ์ระหว่างแอททริบิวต์แบบทั้งหมด (fully functional dependency) หรืออีกนัยหนึ่งรีเลชันนั้นต้องไม่มีความสัมพันธ์ระหว่าง แอททริบิวต์แบบบางส่วนเกิดขึ้น

28. 2. ขั้นตอนการจัดทำรีเลชันให้เป็นรูปแบบที่เป็นบรรทัดฐานขั้นที่ 2

29. รีเลชันการลงทะเบียนมีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันดังนี้รีเลชันการลงทะเบียนมีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันดังนี้ เขียน FD ไดอะแกรมได้ดังนี้

30. ภาพที่ 4.12 การจำแนกรีเลชันการลงทะเบียน

31. รูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 แม้ว่ารีเลชันใด ๆ จะมีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 แล้ว ก็อาจก็ยังมีปัญหาที่เกิดจากความซ้ำซ้อนของข้อมูลในรีเลชันอยู่อีก ดังนั้น รูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 (Third Normal Form : 3NF) จึงเป็นขั้นตอนถัดมาในการแก้ไขปัญหาความซ้ำซ้อนของข้อมูลที่พบในรีเลชันที่มีคุณสมบัติอยู่ใน รูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 ซึ่งในขั้นตอนนี้จะให้ความสำคัญกับความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive

32. 1. คุณสมบัติของรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 • รีเลชันใด ๆ จะมีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 ก็ต่อเมื่อ • 1) รีเลชันนั้นมีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 2 แล้ว • 2)ทุกแอททริบิวต์ที่ไม่ใช่คีย์หลักไม่มีคุณสมบัติในการกำหนดค่าของแอททริบิวต์อื่นที่ไม่ใช่ คีย์หลัก หรืออีกนัยหนึ่งรีเลชันนั้นต้องไม่มีความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive เกิดขึ้น

33. 2. ขั้นตอนการจัดทำรีเลชันให้เป็นรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 ภาพที่ 4.13 รีเลชันข้อมูลนักศึกษา รีเลชันข้อมูลลงทะเบียน และรีเลชันชุดวิชา

34. รีเลชันข้อมูลนักศึกษามีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันดังนี้ รีเลชันข้อมูลนักศึกษามีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันดังนี้

35. จากภาพจะพบว่า แอททริบิวต์รหัสนักศึกษาเป็นคีย์หลักหรือ Determinant ที่สามารถระบุค่าของ Dependent หรือแอททริบิวต์อื่นในทูเพิลเดียวกัน คือ ชื่อนักศึกษา รหัสอาจารย์ และชื่ออาจารย์ที่ปรึกษาได้ ในขณะเดียวกัน รีเลชันข้อมูลนักศึกษามีความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive เกิดขึ้น เนื่องจากรหัสอาจารย์ซึ่งเป็นแอททริบิวต์ที่ไม่ใช่คีย์หลักสามารถระบุค่าของแอททริบิวต์อื่นในทูเพิลเดียวกัน คือ ชื่ออาจารย์ที่ปรึกษา ได้ ดังนั้น จึงกล่าวได้ว่า รีเลชันข้อมูลนักศึกษาไม่มีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 เพื่อขจัดปัญหาความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive ที่เกิดขึ้น จึงต้องทำการจำแนก ข้อมูลนักศึกษาออกเป็น 2 รีเลชันย่อย ด้วยการนำแอททริบิวต์ที่เป็นตัวระบุค่าของแอททริบิวต์อื่นและ แอททริบิวต์หรือกลุ่มของแอททริบิวต์ที่ถูกระบุค่าแยกออกเป็นรีเลชันใหม่ โดยคำนึงถึงหลักการของ ความสัมพันธ์ระหว่างแอททริบิวต์แบบ Join

36. ดังนั้น รีเลชันข้อมูลนักศึกษาจึงจำแนกได้เป็น 2 รีเลชันย่อย คือ รีเลชันอาจารย์ที่ปรึกษา และ รีเลชันนักศึกษา • - รีเลชันอาจารย์ที่ปรึกษาประกอบด้วย แอททริบิวต์รหัสอาจารย์ และชื่ออาจารย์ที่ปรึกษา โดยมีแอททริบิวต์รหัสอาจารย์เป็นคีย์หลัก • - รีเลชันนักศึกษาประกอบด้วย แอททริบิวต์รหัสนักศึกษา ชื่อนักศึกษา และรหัสอาจารย์ โดยมีแอททริบิวต์รหัสนักศึกษาเป็นคีย์หลัก

37. ภาพที่ 4.14 การจำแนกรีเลชันข้อมูลนักศึกษา

38. ส่วนการตรวจสอบรีเลชันข้อมูลลงทะเบียนว่า ทุกแอททริบิวต์ที่ไม่ใช่คีย์หลักมีคุณสมบัติในการกำหนดค่าของแอททริบิวต์อื่นที่ไม่ใช่คีย์หลัก หรืออีกนัยหนึ่งรีเลชันข้อมูลลงทะเบียนมีความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive เกิดขึ้น หรือไม่ • จากการตรวจสอบพบว่า คีย์หลักหรือ Determinant ของรีเลชันข้อมูลลงทะเบียนประกอบด้วย แอททริบิวต์รหัสนักศึกษาและรหัสชุดวิชา ส่วนแอททริบิวต์อื่นในรีเลชันข้อมูลลงทะเบียนที่ไม่ได้เป็นคีย์หลัก คือ หมู่เรียน • รีเลชันข้อมูลลงทะเบียนมีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันดังนี้

39. จากภาพจะพบว่า แอททริบิวต์รหัสนักศึกษาและรหัสชุดวิชาที่ประกอบกันเป็นคีย์หลักสามารถระบุค่าของ Dependent หรือแอททริบิวต์อื่นที่ไม่ได้เป็นคีย์หลัก คือ หมู่เรียน ได้ และทุกแอททริบิวต์ที่ไม่ใช่ คีย์หลักไม่มีคุณสมบัติในการกำหนดค่าของแอททริบิวต์อื่นที่ไม่ใช่คีย์หลัก ดังนั้น จึงกล่าวได้ว่า รีเลชันข้อมูลลงทะเบียนมีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 แล้ว ส่วนการตรวจสอบรีเลชันชุดวิชาว่า ทุกแอททริบิวต์ที่ไม่ใช่คีย์หลักมีคุณสมบัติในการกำหนดค่าของแอททริบิวต์อื่นที่ไม่ใช่คีย์หลัก หรืออีกนัยหนึ่งรีเลชันชุดวิชามีความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive เกิดขึ้น หรือไม่ จากการตรวจสอบพบว่า คีย์หลักหรือ Determinant ของรีเลชันชุดวิชามีเพียงหนึ่งแอททริบิวต์ คือ รหัสชุดวิชา และแอททริบิวต์อื่นในรีเลชันชุดวิชาที่ไม่ได้เป็นคีย์หลัก คือ ชื่อชุดวิชาที่ลงทะเบียน และหน่วยกิต

40. รีเลชันชุดวิชามีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันดังนี้ รีเลชันชุดวิชามีความสัมพันธ์ระหว่างแอททริบิวต์แบบฟังก์ชันดังนี้

41. จากภาพจะพบว่า แอททริบิวต์รหัสชุดวิชาเป็นคีย์หลักหรือ Determinant ที่สามารถระบุค่าของ Dependent หรือแอททริบิวต์อื่นในทูเพิลเดียวกัน คือ ชื่อชุดวิชาที่ลงทะเบียน และหน่วยกิต ได้ ขณะเดียวกัน รีเลชันชุดวิชามีความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive เกิดขึ้น เนื่องจากชื่อชุดวิชาที่ลงทะเบียนซึ่งเป็นแอททริบิวต์ที่ไม่ใช่คีย์หลักสามารถระบุค่าของแอททริบิวต์อื่นในทูเพิลเดียวกัน คือ หน่วยกิต ได้ ดังนั้น จึงกล่าวได้ว่า รีเลชันชุดวิชาไม่มีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3

42. เพื่อขจัดปัญหาความสัมพันธ์ระหว่างแอททริบิวต์แบบ Transitive ที่เกิดขึ้น จึงต้องทำการจำแนกรีเลชันชุดวิชาออกเป็น 2 รีเลชันย่อย ด้วยการนำแอททริบิวต์ที่เป็นตัวระบุค่าของแอททริบิวต์อื่น และแอททริบิวต์หรือกลุ่มของแอททริบิวต์ที่ถูกระบุค่าแยกออกเป็นรีเลชันใหม่ โดยคำนึงถึงหลักการของความสัมพันธ์ระหว่างแอททริบิวต์แบบ Join ดังนั้น รีเลชันชุดวิชาจึงจำแนกได้เป็น 2 รีเลชันย่อย คือ รีเลชันรหัส-ชื่อชุดวิชา และ รีเลชันชุดวิชา-หน่วยกิต - รีเลชันรหัส-ชื่อชุดวิชา ประกอบด้วย แอททริบิวต์รหัสชุดวิชา และชื่อชุดวิชาที่ลงทะเบียนโดยมีแอททริบิวต์รหัสชุดวิชา เป็นคีย์หลัก - รีเลชันชื่อชุดวิชา-หน่วยกิต ประกอบด้วย แอททริบิวต์ชื่อชุดวิชาที่ลงทะเบียน และหน่วยกิต โดยมีแอททริบิวต์ชื่อชุดวิชาที่ลงทะเบียนเป็นคีย์หลัก

43. ภาพที่ 4.15 การจำแนกรีเลชันชุดวิชา

44. แม้ว่าจะจัดทำรีเลชันชุดวิชาให้อยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 ซึ่งเป็นรูปแบบที่ขจัดความซ้ำซ้อนของข้อมูลให้ลดลงจนแทบจะไม่มีเหลืออยู่ด้วยการแตกออกเป็น 2 รีเลชันย่อย คือ รีเลชันรหัส-ชื่อชุดวิชา และ รีเลชันชุดวิชา-หน่วยกิตแล้วก็ตาม แต่ก็ยังมีโอกาสที่จะเกิดปัญหาความผิดพลาดขึ้นได้อีกเช่นกัน จากกรณีข้างต้นจะเห็นว่า การเพิ่มชื่อชุดวิชาฯ ใหม่และหน่วยกิตจะทำไม่ได้ หากไม่มีการระบุรหัสชุดวิชา ทั้งนี้ เนื่องจากรีเลชันรหัส-ชื่อชุดวิชาและรีเลชันชื่อชุดวิชา-หน่วยกิตเป็นรีเลชันที่ไม่มีความเป็นอิสระต่อกัน เนื่องจากแอททริบิวต์รหัสชุดวิชาและชื่อชุดวิชาฯ ต่างก็สามารถกำหนดค่าของหน่วยกิตได้เช่นกัน ปัญหาดังกล่าวนี้จึงเป็นปัญหาที่เกิดจากการแตกรีเลชันมากเกินความจำเป็น

45. ดังนั้น จากกรณีดังกล่าวข้างต้น ผู้ออกแบบฐานข้อมูลจึงไม่จำเป็นต้องทำการจำแนกรีเลชันชุดวิชาออกเป็นรีเลชันรหัส-ชื่อชุดวิชา และ รีเลชันชุดวิชา-หน่วยกิต รวมทั้งอาจกล่าวได้ว่า รีเลชันชุดวิชามีคุณสมบัติอยู่ในรูปแบบที่เป็นบรรทัดฐานขั้นที่ 3 แล้ว

46. F Q A Thank You !