unit 4 Normalazation

บทท่ี 4 ข้อมลู ในรูปแบบบรรทดั ฐาน (Normalization)

สาระสาคญั ประเด็นที่สาคญั ของฐานข้อมลู เชงิ สมั พนั ธ์ (Relational Database) คือการออกแบบฐานข้อมลู ที่จดั เก็บว้้าาตารางข้อมลู (Table) วมม่ ีค้ามซา้ ซ้อนเกิดขนึ ้ ดงั นนั ้ Normalization ก็ คือการปรับปรุงโครงสร้างข้อมลู ของฐานท่ีมีค้ามซา้ ซ้อนให้อ ่ในรูปแบบท่ีเป็นบรรทดั ฐาน

จดุ ประสงค์ท้ั่ วป• เพื่อให้เข้าใจถงึ ปัญหาตา่ ง ๆ ของการจดั การฐานข้อมลู• เพ่ือให้ทราบค้ามหมา ของ Functional Dependency• เพื่อให้ทราบ และเข้าใจลกั ษณะของ Functional Dependency ในแต่ ละประเาท• เพื่อให้เข้าใจ้ธิ ีการจดกั ารข้อมลู ให้อ ใู่ นรูปแบบบรรทดั ฐาน• เพ่ือให้ทราบลกั ษณะของรูปแบบบรรทดั ฐานในแตล่ ะชนั้

จดุ ประสงค์เชิงพฤติกรรม• สามารถ กต้ั อ า่ งปัญหาตา่ ง ๆ ของการจดั การฐานข้อมลู วด้• สามารถกาหนด Functional Dependency ในแตล่ ะขนั้ ในตาราง ฐานข้อมลู วด้ถกู ต้อง

เนือ้ หาสาระ• ปัญหาการจดั การฐานข้อมลู (Anomalies)• Function Dependency• การจดั การข้อมลู ในรูปแบบบรรทดั ฐาน (Normalization)

4.1 ปัญหาการจดั การฐานข้อมลู (Anomalies) Anomalies เป็นปัญหาที่สาคญั ในการจดั การฐานข้อมลู โด มที งั ้ หมด 3 ประเาท คือ การเพ่ิม ลบ แก้วข ในการอธิบา จะใช้ตารางเก็บข้อมลู พนกั งาน ต่อวปนี ้Table:EmployeeDepartmentemployeeID Name Job salary departmentID Name Programmer 15000 1281111 Somchai Research and DBA 13500 42 Development2222 Wichit Programmer 16500 1283333 Somjai Finance System 12000 1304444 Aphitsit Adminstrator Research and Programmer Development Programmer Marketing4445 Youngyut 20000 128 Research and 16500 Development4446 Somjit 128 Marketing

ปัญหาการเพม่ิ ข้อมลู (Insertion Anomalies) จะเกิดขนึ ้ เมอ่ื ทาการเพ่มิ ข้อมลู ใหมล่ งในตาราง สมมติเราทาการเพ่ิมข้อมลู ของพนกั งานใหมล่ งในตารางซง่ึ จะต้องเพมิ่ข้อมลู รหสั พนกั งาน ชื่อพนกั งาน ตาแหน่งงาน เงินเดือน รหสั แผนก และช่ือแผนก พนกั งานคนหนง่ึ อ แู่ ผนกรหสั 42 ช่ือแผนกคือFinance แตป่ รากฏ้า่ เราพมิ พ์ชื่อแผนกเป็น Human Resource จะทาให้ตอนนีข้ ้อมลู ในตารางจะมพี นกั งานที่อ แู่ ผนกรหสั42 จาน้น 2 คน คนหนง่ึ อ ่ชู ื่อแผนก Finance แต่อีกคนอ ชู่ ่ือแผนก Human Resource จะเหน็ ้่าข้อมลู ของพนกั งาน 2คนนีเ้กิดข้อผดิ พลาดข้อมลู วมถ่ กู ต้องปรากฏการณ์นีจ้ ะเรี ก้า่ Isertion Anomalies

ปัญหาการลบข้อมลู (Deletetion Anomalies) จะเกิดขนึ ้ เมื่อทาการลบข้อมลู ออกจากตาราง สมมติ้นั หนง่ึ บริษัทแห่งนีพ้ นกั งานที่อ ่แู ผนกรหัส 128 ลาออกพร้อมกนัทงั ้ หมด ดงั นนั ้ เราจะทาการลบแถ้ของข้อมลู ที่พนกั งานอ แู่ ผนกรหสั 128 ออกจากตารางทงั ้ หมด ทาให้ตอนนีข้ ้อมลู ของบริษัทจะวมว่ ด้เก็บรหสั แผนกรหสั 128 ชื่อแผนก Research and Development เอาว้้แล้้ ทงั ้ ๆ ท่ีแผนกนีว้ มว่ ด้ถูกล บุ แต่อ ่างใด ปรากฏการณ์นีเ้รี ก้า่ Deletion Anomalies

ปัญหาการแก้วขข้อมลู (Deletetion Anomalies) จะเกิดขนึ ้ เม่ือทาการเปล่ี นแปลงแก้วขข้อมลู ในตาราง สมมตุ บิ ริษัทแหง่ นีต้ ้องการแก้วขช่ือแผนก Research andDevelopment เป็น Information Technology ดงั นนั ้ เราจะต้องทาการแก้วขในแถ้ที่มีข้อมลู ของพนกั งานอ ่ใู นแผนกรหสั 128 ทงั ้ หมด แต่ถ้าบงั เอิญในการแก้วขนีอ้ าจจะแก้วขวมค่ รบทกุ แถ้

4.2 Functional Dependency กาหนดนิ ามของ Functional Dependency ดงั นี ้“กาหนดให้ A และ B เป็นฟิลด์ในตาราง R จะวด้้า่ B มีฟังก์ชนั ขนึ ้ กบั A ก็ต่อเมือ่ แตล่ ะค่าของ A จะกาหนดค่าของ B วด้เสมอ” สามารถเขี นค้ามสมั พนั ธ์วด้ดงั นี ้ A ===== B ในท่ีนีเ้ราให้ฟิลด์ A เป็นต้ั กาหนดคา่ (Determinant) ของฟิลด์ B (Dependency)Employee Employee_ID Employee_Name 001 มานี 002 ชูใจ 003 ปิ ติ 004 ชูใจ 005 วีระ

2. Functional Dependency ที่เกิดจาก Determaint 1 ค่า และ Dependency หลา ค่า Employee_ID ====> Employee_Name,Address,Salary หมา ค้าม้า่ ถ้ากาหนดรหสัพนกั งาน (Employee_ID) ขนึ ้ มาค่า 1 คา่ จะสามารถหาค่าของช่ือพนกั งาน (Employee_Name) ที่อ ู่ (Address)และเงินเดือน (Salary) วด้ 1 ชดุ เสมอEmployeeEmployee_ID Emplyee_name Address Salary 001 มานี กรุงเทพฯ 15,000 002 ชูใจ นนทบุรี 18,500 003 ปิ ติ เชียงใหม่ 9300 004 ชูใจ สงขลา 20,000 005 วีระ นครราชสีมา 12,500

3. Functional Dependency ที่เกิดจาก Determinant หลา คา่ และ Dependency 1 คา่ Customer_ID, Product_ID ====> Quantity หมา ค้าม้า่ ถ้ากาหนดรหสั ลลกู ค้า(Customer_ID) และรหสั สนิ ค้า (Product_ID) ขนึ ้ มา 1 ชดุ จะสามารถหาคา่ ของจาน้นสนิ ค้าวด้ 1 ค่าOrder_ProductCustomer_ID Product_ID Quantity 001 P01 5 P02 10 002 P01 15 P03 20

4. Functional Dependency ในลกั ษณะค้ามสมั พนั ธ์แบบ 2 ทางProject_No  Manager_IDManager_ID  Project_NoProject_No  Manager_ID หมา ค้าม้า่ ถ้ากาหนดรหสั โครงการ (Project_No) ขนึ ้ มา 1 คา่ จะสามารถกาหนด รหสั ผ้จู ดั การท่ีดแู ลโครงการ (Manager_ID)วด้ 1 คา่ และในทางกลบั กนั ถ้ากาหนดรหสั ผ้จู ดั การที่ดแู ลโครงการ(Manager_ID) ขนึ ้ มาคา่ 1 คา่ และในทางกลบั กนั ถ้ากาหนดรหสั ผ้จู ดั การที่ดแู ลโครงการ(Manager_ID) ขนึ ้ มา 1 คา่ จะสามารถกาหนดรหลสั โครงการ (Project_No) วด้ 1 คา่ เชน่ กนัProject_Manager Project_No Manager_ID P001 M111 P002 M222 P003 M333 P004 M444

4.3 การจดั การข้อมลู ในรูปแบบบรรทดั ฐาน (Normalization) กาหนดตารางฐานข้อมลู ตอ่ วปนีส้ าหรับเป็นต้ั อ ่างการทา Normalization ในระดบั ตา่ ง ๆ Table : Employee ตารางแสดงข้อมลู พนกั งาน และค้ามถนดั ของพนกั งานemployeeID name job salary departmentID Skill1111 Somchai Programmer 15000 128 C, Java, Perl2222 Witchit DBA 13500 423333 Somjai Programmer 16500 128 DB2 Java, VB4444 Aphisit System 12000 130 Linux, NT4445 PHP4446 Adminstrator JSP Youngyut Programmer 20000 128 Somjit Programmer 16500 128

Table : EmployeeDepartmentemployeeID name job salary departmentID Name1111 Somchai Programmer 15000 128 Research and2222 Development3333 Witchit DBA 13500 42 Finance44444445 Somjai Programmer 16500 128 Research and4446 Development Aphisit System 12000 130 Marketing Adminstrator Research and Development Youngyut Programmer 20000 128 Marketing Somjit Programmer 16500 128

O First Normal Form 1NF 1NF เป็นการทา normalization ขนั ้ ท่ี 1 โด กาหนด้า่ table ใด ๆ จะเป็น 1NF จะต้องเป็น table ที่วมม่ ีค่า multivalued column หรือ repeating group หรือพดู ในอีกมมุ หนึ่ง้่า table นนั ้ จะต้องมคี ณุ สมบตั ิatomicity

O First Normal Form 1NFemployeeID name job salary departmentID skill 1111 Somchai C 1111 Somchai Programmer 15000 128 Java 1111 Somchai Perl 2222 Wichit Programmer 15000 128 DB2 3333 Somjai Java 3333 Somjai Programmer 15000 128 VB 4444 Aphitsit Linux DBA 13500 42 4444 Aphitsit NT Programmer 16500 128 4445 Youngyut PHP Porgrammer 16500 128 System 12000 130 Administrator 12000 130 20000 128 System Administrator Programmer

O Second Normal Form : 2NF 2NF เป็นการทา normalization ขนั้ ที่ 2 โด กาหนด้า่ table ใด ๆ จะเป็น2NF จะต้องเป็น table ที่เป็น 1NF และจะต้องวมม่ ี non-key column ที่ขนึ ้ กบั บางส้่ นของ key จากตารางท่ี 4.8 ตาราง Employee หลงั จากปรับให้เป็น 1NF แตต่ ารางนี ้ งัวมเ่ ป็น 2NF เนื่องจากมีฟิลด์ employeeID และ Sklls เป็น Key ซงึ่ ถ้ากาหนดคา่ขนึ ้ มา 1 ชดุ จะสามารถอ้างถงึ คา่ ของฟิลด์ name ฟิลด์ job ฟิลด์ salary และฟิลด์departmentID วด้ 1 คา่ ดงั นนั้ เขี นแสดงค้ามสมั พนั ธ์ของ FunctionalDependency ชดุ นีว้ ด้เป็น

employeeID, skills  name, job, salary, departmentID นอกจากนนั้ ถ้ากาหนดคา่ ของฟิลด์ employeeID ขนึ ้ มา 1 คา่ กจ็ ะสามารถกาหนดคา่ ของฟิลด์ name ฟิลด์ salary และฟิลด์ departmentID วด้ 1 คา่ เช่นกนั employeeID  name, job, salary, departmentID ซงึ่ ทาให้ตาราง employee นี ้ งั วมเ่ ป็น 2NF เพราะมี non-key columnในทนี่ ีค้ ือฟิลด์ name ฟิลด์ job ฟิลด์ salary และฟิลด์ departmentID

Table : EmployeeemployeeID name job salary departmentID 15000 1281111 Somchai Programmer 13500 42 16500 1282222 Witchit DBA 12000 130 20000 1283333 Somjai Programmer 16500 1284444 Aphisit System4445 Adminstrator Youngyut Programmer4446 Somjit Programmer

ส้่ นตารางที่เหลอื ก็คือ ตดั ฟิลด์ที่เป็น non-key column คือ ฟิลด์ name ฟิลด์ job ฟิลด์ salary และ ฟิลด์departmentID ทีขนึ ้ กบั บางส้่ นของ key ออกวป ดงั นี ้Table : EmployeeemployeeID skill1111 C1111 Java1111 Perl2222 DB23333 Java3333 VB4444 Linux4444 NT4445 PHP4446 JSP

O Third Normal Form : 3NF 3NF เป็นการทา normalization ขนั้ ที่ 3 โด กาหนด้า่ table ใด ๆ จะเป็น 3NF จะต้องเป็น table ที่เป็น 2NF และจะต้องวมม่ ี non-key column คใู่ ดที่ขนึ ้ ตอ่ กนั จากตารางท่ี 4.7 ตาราง EmployeeDepartment จะสามารถเขี น FD วด้คือ employeeID  name,job,salary,departmentID,Name departmentID  Name โด ในท่ีนีฟ้ ิลด์ employeeID เป็น Primary Key แต้่ ่าฟิลด์ DepartmentID วมว่ ด้เป็น Key ซง่ึ ฟิลด์ departmentID และ Name เป็น non-key column ท่ีขนึ ้ ตอ่ กนั

้ธิ ีการปรับให้ตารางเป็น 3NF ก็คือ การแตกตารางเป็น 2 ตาราง โด แ กตารางท่ีมคี ขู่ อง non-key column ท่ีขนึ ้ ตอ่ กนั ออกมา ดงั นี ้Table : DepartmentdepartmentID Name 42 Finance 128 Research and Development 130 Marketing

้ิธีการปรับให้ตารางเป็น 3NF ก็คือ การแตกตารางเป็น 2 ตาราง โด แ กตารางที่มคี ขู่ อง non-key column ท่ีขนึ ้ ตอ่ กนั ออกมา ดงั นี ้employeeID name job salary departmentID 1111 Somchai TaPbrolegr:aEmmmpelor yee 15000 1282222 Wichit DBA 13500 423333 Somjai Programmer 16500 1284444 Aphisit System 12000 130 Administrator4445 Youngyut Programmer 20000 128 Programmer 16500 1284446 Somjit

หลงั จากทา normaliozation มาถงึ 3NF แล้้จะวด้ตารางข้อมลู ทงั้ หมด 3 ตารางดงั นี ้ • Employee (employeeID,name,job,salary,departmentID) • Department (departmentID, name) • EmployeeSkills (employeeID,skill)

นอกจาการทา Normalization ทงั ้ 3 ระดบั คือ 1NF,2NF และ 3NF แล้้ งั มรี ะดบั ที่ทาให้ NormalForm ระดบั ท่ี 3 มีค้ามแข็งแกร่งขนึ ้ อีก เรี ก้า่ Boyce-Codd Normal Form (BCNF) พฒั นาขนึ ้ โด Rboyceและ E.F. Codd Normalization ในระดบั ที่สงู ขนึ ้ วปอีกคือ Fourth Normal Form (4NF) และ Fifth NormalForm (5NF) อ ่างวรก็ตามรูปแบบ 4NF และ 5NF ในทางปฏิบตั ถิ ือ้า่ เกิดขนึ ้ วด้ ากมาก

ข้อสงั เกตุ ในการทา Normalization ตารางฐานข้อมลู จะช่้ แก้ปัญหาเร่ืองการจดั การฐานข้อมลู ทงั ้ 3 กรณีวด้คือIsertion Anomalies, Deletion Anomalies และ Update Anomalies วด้ แต่เมอื่ เราทาNormalization ตารางฐานข้อมลู โด เฉพาะเมอื่ ทา normal form ในระดบั ที่สงู ขนึ ้ เร่ือ ๆ แล้้จะทาให้เกิดการแตกตารางเพม่ิ ขนึ ้ ดงั นนั ้ ปัญหาที่ตามมาก็คือ การเข้าถงึ ข้อมลู ในตารางทงั ้ หมด จะต้องใช้้ธิ ีการเชอื่ มตาราง (Join Table) ซง่ึ การเขี นSQL (Structure Query Language) เพื่อเช่ือมตารางก็จะมีค้าม ่งุ ากเพ่ิมขนึ ้ และการเข้าถงึ ข้อมลู จากหลา ๆ ตารางก็จะใช้เ้ลาในการเข้าถึงมากก้่าเดิม

สรุป Anomalies เป็นปัญหาท่ีสาคญั ในการจดั การฐานข้อมลู โด มที งั ้ หมด 3 ประเาท คือ การเพิ่ม ลบ แก้วข ในการแก้ปัญหา Anomalies ทงั ้ 3 นีส้ ามารถทาวด้ด้้ การทาให้ตารางข้อมลู อ ใู่ นรูปแบบบรรทดั ฐาน (Normalization) กาหนดนิ ามของ Functional Dependency ดงั นี ้“กาหนดให้ A และ B เป็นฟิลด์ในตาราง R จะวด้้า่ B มีฟังก์ชนั ขนึ ้ กบั A ก็ตอ่ เมื่อ และค่าของ A จะกาหนดคา่ ของ B วด้เสมอ “ สามารถเขี นค้ามสมั พนั ธ์วด้ดงั นี ้AB การจดั การข้อมลู ในรูปแบบบรรทดั ฐาน (Normalization) กาหนดลกั ษณะของแต่ละระดบั วด้ดงั นี ้ 1NF เป็นการทา Normalization ขนั ้ ที่ 1 โด กาหนด้า่ table ใดๆ จะเป็น 1NF จะต้องเป็น table ท่ีวมม่ ีคา่ multivalues column หรือ repeating group 2NF เป็นการทา Normalization ขนั ้ ที่ 2 โด กาหนด้า่ table ใดๆ จะเป็น 2NF จะต้องเป็น table ที่เป็น1NF และจะต้องวมม่ ี non-key column ท่ีขนึ ้ กบั บางส้่ นของ key 3NF เป็นการทา Normalization ขนั ้ ท่ี 3 โด กาหนด้า่ table ใดๆ จะเป็น 3NF จะต้องเป็น table ท่ีเป็น2NF และจะต้องวมม่ ี non-key column คใู่ ดที่ขนึ ้ ต่อกนั