Normalization of Database Tables

1. Normalization of Database Tables การNormalize คือหลักการออกแบบฐานข้อมูล คิดค้นโดย ดร.คอร์ด การทำ Normalize ในแต่ละระดับจะช่วยแยก Attribute ที่ซ้ำซ้อนกันออกไป

2. วัตถุประสงค์การเรียนรู้วัตถุประสงค์การเรียนรู้ • สามารถอธิบายขั้นตอนการทำ Normalization ในแต่ละรูปแบบได้ • มีความเข้าใจในการทำ Normalizationในแต่ละรูปแบบ

3. วัตถุประสงค์ของการทำ Normalization • เพื่อลดเนื้อที่ในการจัดเก็บข้อมูล • เพื่อลดปัญหาที่ข้อมูลไม่ถูกต้อง(Inconsistency) • เป็นการลดปัญหาที่เกิดจากการเพิ่ม ปรับปรุงและลบข้อมูล(Insert, Update and Delete Anomalies)

4. Series of stages called normal forms: • (First Normal Form : 1NF) • (Second Normal Form : 2NF) • (Third Normal Form : 3NF) • (Boyce/Codd Normal Form : BCNF) • (Fourth Normal Form : 4NF) • (Fifth Normal Form : 5NF)

5. The term first normal form (1NF) • All the key attribute are defined • There are no repeating groups in the table • All attributes are dependent on the primary key

6. First Normal Form : 1NF • First Normal Form : 1NF • Relation หนึ่งๆ จะอยู่ในรูปแบบ 1NF ก็ต่อเมื่อ • “ค่าของ Attribute ต่างๆ ในแต่ละ Tuple จะมีค่าของข้อมูลเพียงค่าเดียว นั่นคือไม่มี Repeating Group และ Multi-valued”

9. First Normal Form : 1NF ความผิดพลาดบางอย่างที่อาจเกิดขึ้นกับข้อมูลใน Relation ก็ยังมีอยู่ เช่น 1. Insert Anomalyการที่จะเพิ่มข้อมูลของผู้ผลิตจะทำได้ต่อเมื่อผู้ผลิตรายนั้นมีการส่งสินค้าไปให้ผู้ซื้อ รีเลชั่นจะไม่แสดงรายละเอียดของผู้ผลิต S5 ที่อยู่ที่ Athens ถ้ายังไม่มีการส่งสินค้า ดังนั้นหากมีเพียงข้อมูลของผู้ผลิตแต่ยังไม่เคยได้รับการสั่งสินค้าก็จะเพิ่มข้อมูลของผู้ผลิตไม่ได้

10. First Normal Form : 1NF • 2. Delete Anomalyในการลบข้อมูลบาง Tuple จะลบทั้งข้อมูลผู้ผลิตและข้อมูลการส่งสินค้า ปัญหาของ Relation นี้คือ Relation นี้ประกอบด้วย Attribute มากเกินไปโดยที่ข้อมูลบาง Attribute อาจไม่จำเป็นต้องใช้งานแต่มาผูกติดกับ Attribute ที่ต้องใช้งาน

11. First Normal Form : 1NF • 3. Update Anomaly การปรับปรุงจะทำให้ยุ่งยากและเสียเวลา รวมทั้งอาจก่อให้เกิดความผิดพลาดที่ข้อมูลไม่เหมือนกัน เช่น เปลี่ยนชื่อเมืองของ S1 เป็น New York

12. First Normal Form : 1NF • ปัญหาที่เกิดขึ้น สามารถแก้ไขได้โดยแตก Relation (Decomposition) เป็น 2 Relation คือ Relation SUPPLIER และ Relation ORDER • SUPPLIER(S#, SNAME, CITY) • ORDER(S#, P#, QTY)

13. A table is in second normal form(2NF) if: • It isin 1NF • It includes no partial dependencies; that is, no attribute is dependent on only a portion of the primary key.

14. Second Normal Form : 2NF • Relation หนึ่งๆ จะอยู่ในรูปแบบ 2NF ก็ต่อเมื่อ • “1. Relation นั้นๆ ต้องอยู่ในรูปแบบ 1NF • 2. Attribute ทุกตัวที่ไม่ได้เป็นคีย์หลัก จะต้องมีความสัมพันธ์ระหว่างค่าของ Attribute แบบฟังก์ชันกับคีย์หลัก(Fully Functional Dependency) หรือกล่าวง่ายๆ ว่า ไม่มี Partial Dependency เกิดขึ้น”

15. Relation SUPPLIER และ ORDER อยู่ใน 2NF แล้ว แต่หากสมมติให้ Relation ORDER1ถูกออกแบบเป็นดังนี้ ORDER1

16. Second Normal Form : 2NF S# P# PNAME QTY

17. Second Normal Form : 2NF • เพราะฉะนั้น Relation ORDER1 ไม่ได้อยู่ในรูป 2NF ต้องทำการแตก Relation เพื่อลดความซ้ำซ้อนของข้อมูล ดังนี้ • ORDER1(S#, P#, QTY) • PRODUCT(P#, PNAME)

18. Second Normal Form Step 1: Write Each Key Component on a Separate Line • Write each key component on separate line, then write original (composite) key on last line • Each component will become key in new table

19. Second Normal Form Step 2: Assign Corresponding Dependent Attributes • Determine those attributes that are dependent on other attributes • At this point, most anomalies have been eliminated

20. A table is in third normal form(3NF) if: • It is in 2NF • It contains no transitivedependencies • transitivedependency; that is, one or more attribute may be functionally dependent on nonkey attributes

21. Third Normal Form : 3NF • Relation หนึ่งๆ จะอยู่ในรูปแบบ 3NF ก็ต่อเมื่อ • “1. Relation นั้นๆ ต้องอยู่ในรูปแบบ 2NF • 2. Attribute ทุกตัวที่ไม่ได้เป็นคีย์หลัก ไม่มีคุณสมบัติในการกำหนดค่าของ Attribute อื่นที่ไม่ใช่คีย์หลัก หรือกล่าวง่ายๆ ว่า ไม่มี Transitive Dependency เกิดขึ้น”

22. Third normal form(3NF) Data anomalies created are easily eliminated by completing three steps Step 1: Identify Each New Determinant • For every transitive dependency, write its determinant as PK for new table • Determinant • Any attribute whose value determines other values within a row

23. Third normal form(3NF) Step 2: Identify the Dependent Attributes • Identify attributes dependent on each determinant identified in Step 1 and identify dependency • Name table to reflect its contents and function

24. Third normal form(3NF) SUPPLIER1

25. Third normal form(3NF) SUPPLIER1 อยู่ใน 2NF แล้วแต่ยังมี Transitive Dependency เกิดขึ้นอยู่ ทำให้มีความผิดพลาดบางอย่างที่อาจเกิดขึ้นกับข้อมูลใน Relation ก็ยังมีอยู่ เช่น 1. Update Anomaly หากมีการแก้ไขการจัดอันดับของจังหวัดของผู้ผลิต จะต้องทำการแก้ไขข้อมูลหลาย Tuple

26. Third normal form(3NF) 2. Delete Anomalyหากมีการลบข้อมูลการจัดอันดับของจังหวัดของผู้ผลิต จะทำให้ข้อมูลการจัดการจัดอันดับหายไปจากฐานข้อมูล

27. Third normal form(3NF) • ปัญหาที่เกิดขึ้น สามารถแก้ไขได้โดยแตก Relation (Decomposition)SUPPLIER1 เป็น 2 Relation • โดยแยก Attribute ที่ถูกกำหนดค่ากับ Attribute ที่เป็นตัวกำหนดค่า(Determinant) ออกเป็น Relation ใหม่และ • กำหนดให้ Attribute ที่เป็นDeterminant เป็นคีย์หลักของ Relation ใหม่

28. Third normal form(3NF) • จะได้ • SUPPLIER(S#, SNAME, CITY) • CITY(CITY, RATING)

29. ในบางครั้งผู้ออกแบบพยายามแตก Relation ที่มี attribute มากๆ ออกเป็นหลายๆ Relation ซึ่งจะทำให้เกิดปัญหาการแตก Relation ที่ไม่เหมาะสมได้(Bad Decomposition)เช่น • หากแตก Relation SUPPLIER1 เป็นดังนี้ • SUPPLIER(S#, SNAME, CITY) • SUPPLIER2(S#, RATING)

30. Normal Form SUPPLIER

31. Normal Form SUPPLIER2

32. Third normal form(3NF) จากตัวอย่าง การแตก Relation นี้ดูเหมือนจะแก้ปัญหาความผิดพลาดที่อาจจะเกิดจากการเพิ่ม ลบ ปรับปรุงข้อมูลได้ เพราะได้แตกออกเป็น 2 Relation และอยู่ในรูป 3NF แล้ว แต่การแตก Relation อาจก่อให้เกิดความผิดพลาดขึ้นได้อีกเช่นกัน จะเห็นว่า การเพิ่มชื่อจังหวัดและการจัดอันดับของจังหวัดใหม่จะทำไม่ได้ จนกว่าจะมีผู้ผลิตรายใดอยู่ที่จังหวัดที่จะเพิ่มชื่อและจัดอันดับนั้น

33. Third normal form(3NF) Relation SUPPLIER และ SUPPLIER2 เป็น Relation ที่ไม่เป็นอิสระต่อกัน เพราะจังหวัดเป็นตัวกำหนดการจัดอันดับได้ S# S# CITY CITY RATING RATING

34. The Boyce-Codd Normal Form (BCNF) • Every determinant in table is a candidate key • Has same characteristics as primary key, but for some reason, not chosen to be primary key • When table contains only one candidate key, the 3NF and the BCNF are equivalent • BCNF can be violated only when table contains more than one candidate key

35. The Boyce-Codd Normal Form (BCNF) • Table is in 3NF when it is in 2NF and there are no transitive dependencies • Table can be in 3NF and fails to meet BCNF • No partial dependencies, nor does it contain transitive dependencies • A nonkey attribute is the determinant of a key attribute

36. Boyce/Codd Normal Form : BCNF Relation หนึ่งๆ จะอยู่ในรูปแบบ BCNF ก็ต่อเมื่อ “1. Relation นั้นๆ ต้องอยู่ในรูปแบบ 3NF 2. ไม่มี Attribute อื่นใน Relation ที่สามารถระบุค่าของ Attribute ที่เป็นคีย์หลักหรือส่วนหนึ่งส่วนใดของคีย์หลักในกรณีที่คีย์หลักเป็นคีย์ผสม(Composite Key)”

37. The Boyce-Codd Normal Form (BCNF) • BCNF จะอยู่ในรูปแบบ 3NF แต่ไม่จำเป็นเสมอไปว่ารูปแบบ 3NF จะอยู่ในรูปแบบของ BCNF • เนื่องจากรูปแบบนี้เป็นรูปแบบที่ขยายขอบเขตของรูปแบบ 3NF ให้เหมาะสมยิ่งขึ้น • โดยรูปแบบของ Relation ที่จะต้องผ่านการทำให้เป็น BCNF มักจะมีคุณสมบัติ ดังนี้

38. The Boyce-Codd Normal Form (BCNF) • เป็น Relation ที่มี CK หลายคีย์(Multiple Candidate Key) • CK เป็นคีย์ผสม(Composite Key) • CK นั้นมีบางส่วนซ้ำซ้อนกัน(Overlapped)

39. The Boyce-Codd Normal Form (BCNF) จะใช้ Relation SUPPLIER3 โดยสมมติว่า SNAME เป็นค่าไม่ซ้ำกันและมีคุณสมบัติเป็นคีย์หลักได้เช่นกัน

40. SUPPLIER3

41. The Boyce-Codd Normal Form (BCNF) จากตัวอย่างจะเป็น Relation ที่มีปัญหาเพราะมี CK เป็นคีย์ผสมและมีความซ้ำซ้อนกัน CK ที่มีคุณสมบัติเป็นคีย์หลักของรีเลชัน อาจเป็น S# และ P# หรือ SNAME และ P# Relation นี้ไม่ได้อยู่ในรูปแบบ BCNF เพราะเมื่อเลือก CK ใดเป็นคีย์หลักแล้ว CK ที่ไม่ถูกเลือกจะยังปรากฎซ้ำซ้อนอยู่ใน Relation นี้และมีคุณสมบัติในการระบุค่าของ Attribute ที่เป็นคีย์หลัก

42. The Boyce-Codd Normal Form (BCNF) เช่น หากเลือก S# และ P# เป็นคีย์หลักแล้วจะเกิดปัญหาใน Relation นี้คือ SNAME จะมีคุณสมบัติในการระบุค่าของ Attribute S# ดังนี้ S# SNAME P# QTY

43. The Boyce-Codd Normal Form (BCNF) ดังนั้น Relation SUPPLIER3 ต้องทำการแตกรีเลชันออก โดยแยก attribute ที่สามารถระบุค่าของ PK แยกเป็นอีกรีเลชันหนึ่ง ซึ่งทำได้ 2 กรณีคือ 1. SUPPLIER4(S#, SNAME) ORDER2(S#, P#, QTY) หรือ 2. SUPPLIER4(S#, SNAME) ORDER2(SNAME, P#, QTY)

44. The Boyce-Codd Normal Form (BCNF) A C D B E

45. Fourth Normal Form (4NF) • Table is in fourth normal form (4NF) when both of the following are true: • It is in 3NF • Has no multiple sets of multivalued dependencies • 4NF is largely academic if tables conform to following two rules: • All attributes must be dependent on primary key, but independent of each other • No row contains two or more multivalued facts about an entity

46. Fourth Normal Form (4NF) Fourth Normal Form : 4NF Relation หนึ่งๆ จะอยู่ในรูปแบบ 4NF ก็ต่อเมื่อ “1. Relation นั้นๆ ต้องอยู่ในรูปแบบ BCNF 2. เป็น Relation ที่ไม่มีความสัมพันธ์ในการระบุค่าของ Attribute แบบหลายค่าโดยที่ Attribute ที่ถูกระบุค่าเหล่านี้ไม่มีความสัมพันธ์กัน(Independently Multivalued Dependency)”

47. Fourth Normal Form (4NF) เช่น ผู้ผลิตหนึ่งผลิตได้หลายโครงการและผู้ผลิตหนึ่งๆ มีโรงงานตั้งอยู่หลายจังหวัด ทำให้อยู่ในรูป BCNF โดยให้ทุก Attribute ประกอบกันเป็นคีย์หลัก

48. SPJC

49. Fourth Normal Form (4NF) จะได้ S# ->-> PJ# S# ->-> CITY การที่ attribute ทั้งสองไม่มีความสัมพันธ์กันแต่มาอยู่ใน Relation เดียวกันจะก่อให้เกิดความซ้ำซ้อนของข้อมูล ถึงแม้ว่า Relation SPJC จะอยู่ในรูป BCNF โดยมีทุก Attribute ประกอบเป็นคีย์หลักก็ตามแต่ยังมีปัญหาอยู่

50. Fourth Normal Form (4NF) ดังนั้น Relation SPJC ต้องทำการแตกรีเลชันออกเป็น 2 Relation ดังนี้ SPJ(S#, PJ#) SC(S#, CITY)