คําศัพทที่เกี่ยวของ FD
(Functionally Dependent or Functional Dependency) Full FD (Full Functional Dependency) Partial Dependency Transitive Dependency Multivalued Dependency
Normalization อ. พรอนันต เอี่ยมขจรชัย สาขาวิชาเทคโนโลยีสารสนเทศ 204204 DBASE DES & DEV
FD (Functional Dependency)
ความสัมพันธระหวางคางของ Attribute แบบฟงกชัน Attribute หนึ่ ง หรื อ มากกว า หนึ่ ง Attribute ประกอบกั น สามารถระบุ คาของ Attribute อื่นๆ ในทูเพิ เพิล (Tuple) Tuple) หนึ่งไดชัดเจน แบงไดเปน 2 รูปแบบ คือ
แบบ 1 : 1
แบบ m : 1
Stu_ID
Stu_ID
3
204204 DBASE DES & DEV
4
มีฟงกชันขึ้นกับ x (y เปน FD กับ x) ก็ตอเมื่อถา 2 ทูเพิ เพิลใน R มีคาของ x ตรงกันแลว ทั้งสอง ทูเพิ เพิลจะตองมีคาของ y ตรงกัน เชน relation R มี attribute x, y จะไดวา R.x. R.y. ความหมาย : x เปนตัวกําหนดคา (determine (determine)) ของ y หรือคาของ y ขึ้นอยูกับคาของ x (y depends on x หรือ y เปน FD กับ x) y
Stu_Name Dependency-attribute
204204 DBASE DES & DEV
D_Complete
กําหนดให x และ y เปน Attribute ของ relation R
เชน ความสัมพันธระหวาง รหัสพนักงานและชื่อพนักงาน เมื่อระบุคาของรหัสพนักงาน (Emp_ID (Emp_ID)) จะสามารถทราบชื่อพนักงาน (Emp_Name) Emp_Name) ที่มีรหัสตามที่ระบุได
Determinant-attribute
Course_No
Functional Dependency : นิยาม
Dependency-attributes
DeterminantDeterminant-attribute คือ Attribute ที่ระบุคาแลวสามารถแสดงคาของ DependencyDependency-attribute ได
Stu_ID
Stu_Name
แบบ m : 1
ความสัมพันธที่เกิดขึ้นมักถูกนิยามดวยรูปแบบทางคณิตศาสตร Determinant-attributes
2
ตัวอยาง Functional Dependency แบบ 1 : 1
คาของ Attribute ทางดานซาย 1 คา บงบอกคาของ Attribute ทางขวา 1 คา คาของ Attribute ทางดานซายตั้งแต 1 คาขึ้นไป บงบอก คาของ Attribute ทางขวา 1 คา
204204 DBASE DES & DEV
204204 DBASE DES & DEV
1
5
204204 DBASE DES & DEV
6
1
Functional Dependency
: รูปแบบการเขียน
Functional Dependency : ประเภท
FDs : determinant-attribute
dependency-attribute
Functional Dependency ที่เกิดขึ้นจากความสัมพันธระหวาง Determinant และ Dependencyอย Dependencyอยางละ 1 คา Stu_ID
FDs : Student_ID
Student_ID
Student_Name
Student_Name
7
Stu_ID, Course_No
หรือ เปน FD ที่มี Determinant ที่มีขนาดเล็กที่สุด และ สามารถระบุถึง Dependency ได
D_Complete D_Complete
Full Functional Dependency D1 : Perdon_ID Address D2 : Perdon_ID, Person_name
9
204204 DBASE DES & DEV
: ตัวอยาง
Address
D3 : Product_line, Item_no Use_qty D4 : Product_line, Item_no, Manager Use_qty
D1 และ D3 เปน Full Functional Dependency 11
10
Partial Dependency
204204 DBASE DES & DEV
8
ถา y เปน FD กับ x และไมเปน FD กับ Subset ใดๆ ของ x
204204 DBASE DES & DEV
สรุป
Advisor
Attribute y ของ relation R จะเปน Full FD บน Attribute x ของ relation R
Manager
Course_No
School
Full Functional Dependency : นิยาม
Functional Dependency ที่ใช Determinant มากกวา 1 คา เพื่ออางถึง Dependency Stu_ID
Stu_Name
204204 DBASE DES & DEV
Functional Dependency ที่มีความสัมพันธ 2 ทาง โดยที่ Determinant และ Dependency สามารถระบุคาของอีกฝายหนึ่งได Project_Name
Functional Dependency ที่เกิดขึ้นจากความสัมพันธระหวาง Determinant 1 คา กับ Dependency หลายคา Stu_ID
204204 DBASE DES & DEV
Stu_Name
เนื่องจาก relation หนึ่งที่มี Determinant มากกวา 1 ตัว (มี Primary key มากกวา 1 Attributes) Attributes) เปนคียผสม (Composite (Composite key) key) ความสัมพันธระหวางคาของ Attribute แบบบางสวน เกิดขึ้นเมื่อ Attribute ที่เปน Determinant บางตัวของ PK สามารถระบุคาของ Attributes อื่นๆ ที่ไมใช PK ของ relation ได (Non (Non--key attribute) attribute) ความสัมพันธแบบนี้จะทําให เกิดปญหา ในเรื่องความซ้ําซอน และ การปรับปรุงขอมูล
204204 DBASE DES & DEV
12
2
ตัวอยาง
Transitive Dependency
Stu_ID
Course_No
D_Complete
Course_Name
Stu_ID, Course_No
D_Complete
Course_No
Course_Name
204204 DBASE DES & DEV
204204 DBASE DES & DEV
13
ตัวอยาง
14
Multivalued Dependency
Stu_ID
Stu_Name
School_ID
School_Name
ความสัมพันธนี้จะเกิดขึ้นกับ relation ที่ประกอบดวย Attribute อยางนอย 3 Attribute และเปน relation ที่ Attribute หนึ่งสามารถระบุคาของ Attribute อื่นๆ ใน relation ไดมากกวาหนึ่งคา
ความสัมพันธในลักษณะดังกลาว เรียกวา
Transitive Dependency
Stu_ID
Stu_Name, School_ID, School_Name School_ID
“ความสัมพันธในการระบุคาของ Attribute แบบหลายคา”
School_Name
204204 DBASE DES & DEV
15
หลักการของ Multivalued Dependency สัญลักษณที่ใชคือ Attribute A
เปนความสัมพันธระหวาง Attributes เมื่อ Attributes อื่นๆ ที่ไมใช Primary key (NonNon-key Attribute) Attribute) สามารถ ระบุคาของ Attribute อื่นๆ ในทูเพิ เพิล (Tuple (Tuple)) ได ความสัมพันธในลักษณะดังกลาว เรียกวา “ความสัมพันธระหวางคาของ Attribute แบบ Transitive” Transitive”
A
16
ทูเพิ เพิล 2 ทูเพิ เพิลในความสัมพันธ R ที่มีคา B เหมือนกันไมจําเปนตอง
มีคา A เดียวกัน คาของ Attribute C สองคาที่มีความสัมพันธกับคา A เดียวกัน จะตองสัมพันธกับคาของ B ในกลุมเดียวกัน และเปนกลุมที่ถูก กําหนดโดยคา A นั้นๆ
B
คาหนึ่งจะเปนตัวกําหนดกลุมของคา Attribute B
เมื่อทูเพิ เพิล 2 ทูเพิ เพิลในความสัมพันธ R มีคา A
เดียวกัน ไมจําเปน ตองมีคา B เหมือนกัน แต คาของ B จะตองอยูในกลุมของคา B ที่ถูกกําหนดโดย A
คาของ Determinant 1 คาสามารถระบุคาของ Attribute ที่ทําหนาที่เปน Dependency ไดตั้งแต 2 Attribute ขึ้นไป ซึ่งอยูในรูปของชุดขอมูล
การเปลี่ยนแปลงคาใน Attribute C จะไมมีผลกระทบตอคา B
204204 DBASE DES & DEV
204204 DBASE DES & DEV
Employee#
17
Department#, Project#
204204 DBASE DES & DEV
18
3
ความสัมพันธ R
ตัวอยาง
ความสัมพันธ R ประกอบดวย Attribute ดังนี้ Attribute S_No Attribute PJ_No Attribute City
หมายถึง รหัสผูผลิต หมายถึง รหัสโครงการที่สั่งผลิต หมายถึง ชื่อจังหวัดที่ผูผลิตตั้งอยู
ขอสมมติฐานของความสัมพันธของขอมูล คือ
ผูผลิตแตละรายสามารถรับงานไดมากกวา 1 โครงการ ผูผลิตแตละรายมีโรงงานตั้งอยูในจังหวัดตางๆ ไดมากกวา 1 จังหวัด
204204 DBASE DES & DEV
19
ความสัมพันธ R เปนรีเลชั่นที่มีความสัมพันธของ Attribute ในการระบุคาไดมากกวา 1 คา คือ S_No ระบุคา PJ_No ไดหลายคา S_No
S_No ระบุคา City S_No
City
S1 S1 S1 S1 S2 S2 S2 S2
PJ01 PJ01 PJ02 PJ02 PJ03 PJ03 PJ04 PJ04
Bangkok Samuthprakarn Bangkok Samuthprakarn Rayong Chonburi Rayong Chonburi 20
relation R เกิดความสัมพันธในลักษณะการระบุคาของ Attribute แบบหลายคาระหวาง Attribute S_No กับ PJ_No และ S_No กับ City Attribute PJ_No และ City ไมมีความสัมพันธตอกัน แต ปรากฏอยูใน relation เดียวกัน ทําใหคาของ City ปรากฏซ้ําซอนอยูใน หลายทูเพิล เพื่อใหขอมูลของ relation ครบถวน กอใหเกิดปญหาในการเพิ่ม ลบ หรือปรับปรุงขอมูลได
ไดหลายคา City
204204 DBASE DES & DEV
21
Trivial FD’ FD’s Functional Dependency เปน Trivial FD’ FD’s ก็ตอเมื่อ Attribute
ตัวอยาง : SUPPLIER (S_NAME, ADDRESS, ITEM, PRICE) S_NAME ADDRESS S_NAME, ITEM PRICE ADDRESS PRICE 204204 DBASE DES & DEV
204204 DBASE DES & DEV
Trivial FD’s 23
22
Normalization
ทางดานขวาอยูใน Attribute ทางดานซาย
S_NAME S_NAME,ITEM
PJ_No
204204 DBASE DES & DEV
PJ_No
S_No
E.F. Codd เปนผูคิดแนวคิดการทํา relation ใหอยูในรูปแบบบรรทัดฐาน (Normalization Process) Process) เปนกระบวนการที่นําเคารางของ relation มาทําใหอยูในรูปแบบที่เปน บรรทัดฐาน (Normal (Normal Form) Form) เปนกระบวนการเพื่อพัฒนาการเชื่อมตอของขอมูล เพื่อแกปญหา ของ relation เพื่อใหแนใจวาการออกแบบเคารางของ relation เปนการออกแบบ ที่เหมาะสม 204204 DBASE DES & DEV
24
4
ซึ่งไดมา จากการวิเคราะหระบบ (การออกแบบในระดับแนวคิด) โดยแสดงใหเห็น
Normalization : วัตถุประสงค
ขอมูลที่ใชในการทํา Normalization นั้นจะเอามาจาก E-R Model
ขอมูล (Entity (Entity)) ที่เกิดขึ้นในระบบ
ลดเนื้อที่ในการจัดเก็บขอมูล การทํา relation ใหเปนบรรทัดฐาน เปนการลดความซ้ําซอนของขอมูล ใน relation ทําใหลดเนื้อที่ในการจัดเก็บขอมูล / ลดที่วางที่ใชในการเก็บขอมูล
Attributes ของขอมูล Relation ระหวางขอมูล
ลดปญหาเรื่องขอมูลไมถูกตอง เนื่องจากขอมูลใน relation หนึ่งจะมีขอมูลไมซ้ํากัน เมื่อมีการปรับปรุงขอมูลก็จะปรับปรุงทูเพิ เพิลนั้นๆ ครั้งเดียว ไมตองปรับปรุง หลายแหง ลดโอกาสที่จะเกิดความผิดพลาดในการปรับปรุงขอมูลไมครบถวน
204204 DBASE DES & DEV
204204 DBASE DES & DEV
25
26
Normalization : รูปแบบ
ลดปญหาการเกิ หาการเกิดอะนอรมัลไล (Anomalies (Anomalies)) ในการเพิ่ม ปรับปรุง และลบขอมูล ชวยแกปญหาที่อาจจะเกิดขึน้ จากการปรับปรุงขอมูลไมครบ ชวยแกปญหาที่อาจจะเกิดขึน้ จากการเพิ่มขอมูล ้ จากการลบขอมูล ชวยแกปญหาที่อาจจะเกิดขึน ชวยแกปญหาในเรื่องขอมูลสูญหายจากฐานขอมูล
โดยทั่วไปการทํา Normalization มีดวยกันทั้งสิ้น 6 รูปแบบ คือ
First Normal Form (1NF) Second Normal Form (2NF) Third Normal Form (3NF)
BoyceBoyce-Codd Normal Form (BCNF)
เพิ่มความคงทนแกโครงสรางฐานขอมูล
Forth Normal Form (4NF) Fifth Normal Form (5NF)
204204 DBASE DES & DEV
27
ขั้นตอนการทํา Normalization
1NF “ขจัด Repeating Group” 2NF “ขจัด Partial Dependency” 3NF “ขจัด Transitive Dependency”
204204 DBASE DES & DEV
First Normal Form : 1NF
BCNF “ขจัดปญหา Overlap Multiple Candidate Key ”
28
Relation ที่จะอยูในรูปแบบ 1NF ก็ตอเมื่อ relation นั้นๆ ไมมี Attribute ของทูเพิ เพิลที่มีคาของขอมูลหลายคา (No (No Repeating Group) Group) คาของ Attribute ตางๆ ในแตละทูเพิ เพิล มีคาของขอมูลเพียงคาเดียว (Atomic Value) Value)
4NF “ขจัด Nontrivial Multivalued Dependency” 5NF “ขจัด Join Dependency” 204204 DBASE DES & DEV
29
204204 DBASE DES & DEV
30
5
ตารางที่มีลักษณะขอมูลหลายคาในฟลดใดฟลดหนึ่ง (Repeating (Repeating Group) Group) S_No S_Name S1 ติ๋ม
City กรุงเทพฯ
S2
ตอย
ปทุมธานี
S3 S4
นอย แดง
ปทุมธานี กรุงเทพฯ
P_No P1 P2 P3 P4 P1 P2 P2 P2 P4
ปรับเปลี่ยนขอมูลในตารางใ หอยูในรูป 1NF S_No S1 S1 S1 S1 S2 S2 S3 S4 S4
Qty 300 200 400 200 300 400 200 200 300
204204 DBASE DES & DEV
31
Second Normal Form : 2NF
Attribute ทุก Attribute ที่ไมไดเปนคียหลัก (PK (PK)) จะตองมีความสัมพันธ
ระหวางคาของ Attribute แบบฟงกชันกับคียหลัก (Full (Full FD) FD) หรือ
คาของ Attribute ที่ไมไดเปนคียหลักจะสามารถระบุคาโดย Attribute
ที่เปนคียหลัก หรือ Attribute ทั้งหมดที่ประกอบกันเปนคียหลักในกรณีที่ คียหลักเปนคียผสม
Relation Order1
P_No
P_Name
Qty
S1
P1
เพชร
100
S1
P2
ทับทิม
200
S1
P3
ไพลิน
300
S2
P1
เพชร
300
S2
P3
ไพลิน
400
S3
P4
พลอย
100
204204 DBASE DES & DEV
32
เปนการขจัด Attribute หรือกลุม Attribute ที่ซ้ํากัน
ความผิดพลาดที่เกิดขึ้นกับขอมูลในระดับ 1NF 1NF จากการเพิ่มขอมูล (Insert (Insert Anomaly) Anomaly) อาจทําใหไมสามารถเพิ่มขอมูลบางอยาง ไมได หรือเพิม่ ขอมูลแลวเกิดความขัดแยงกับขอมูลเดิม จากการลบขอมูล (Delete (Delete Anomaly) Anomaly) ถาตองลบขอมูลบางสวน จะสงผลให ลบขอมูลอื่นๆ ออกไปดวยโดยไมไดตั้งใจ จากการแกไขขอมูล (Update (Update Anomaly) Anomaly) จะตองแกไขทุกทูเพิ เพิล ผูใชงาน ฐานขอมูลจะตอง แกไขขอมูลมากกวา 1 แหง
33
ประกอบดวย Attribute ดังนี้
S_No
Qty 300 200 400 200 300 400 200 200 300
กลาวคือ
204204 DBASE DES & DEV
P_No P1 P2 P3 P4 P1 P2 P2 P2 P4
การทํา Normalization ในระดับ 1NF
Attribute จะตองไมมีความสัมพันธแบบ Partial Dependency
City กรุงเทพฯ กรุงเทพฯ กรุงเทพฯ กรุงเทพฯ ปทุมธานี ปทุมธานี ปทุมธานี กรุงเทพฯ กรุงเทพฯ 204204 DBASE DES & DEV
Relation ที่จะอยูในรูปของ 2NF 2NF ก็ตอเมื่อ Relation นั้นๆ อยูในรูปแบบบรรทัดฐานขั้นที่ 1 (1NF) 1NF)
S_Name ติ๋ม ติ๋ม ติ๋ม ติ๋ม ตอย ตอย นอย แดง แดง
204204 DBASE DES & DEV
34
Attribute รหัสผูผลิต (S_No (S_No)) และรหัสสินคา (P_No (P_No)) ประกอบ
กันเปนคียหลัก (PK (PK)) ที่สามารถระบุคาของชื่อสินคา (P_Name (P_Name)) และจํานวนสินคาที่ถูกจัดสง (Qty (Qty)) ได Attribute รหัสสินคา (P_Name (P_Name)) สามารถระบุคาของชื่อสินคา (P_Name) P_Name) ไดอีกดวย
S_No
35
P_No
P_Name
204204 DBASE DES & DEV
Qty
36
6
จึงไมไดอยูในรูปแบบ 2NF ดังนั้น ตองทําการแตก relation เพื่อลดปญหาความซ้ําซอนของขอมูล
Third Normal Form : 3NF
เพราะฉะนั้น relation Order 1
Order1 (S_No, P_No, Qty) มี S_No S_No และ P_No P_No เปน PK
Relation ที่จะอยูในรูปของ 3NF 3NF ก็ตอเมื่อ Relation นั้นๆ อยูในรูปแบบบรรทัดฐานขั้นที่ 2 (2NF) 2NF)
Product (P_No, P_Name) มี P_No P_No เปน PK
S_No
P_No
Qty
Attribute ที่ไมไดเปนคียหลัก (PK (PK)) ไมมีคุณสมบัติในการกําหนดคา
ของ Attribute อื่นที่ไมใชคียหลัก Attribute จะตองไมมีความสัมพันธแบบ Transitive Dependency
Product P_No
P_Name
Order1 204204 DBASE DES & DEV
Relation Supplier1
ประกอบดวย Attribute ดังนี้
S_No S_Name
City
Rating
ติ๋ม
กรุงเทพฯ
2
S2
ตอย
ปทุมธานี
3
S3
นอย
ปทุมธานี
3
S4
แดง
กรุงเทพฯ
2
S5
จุม
สมุทรปราการ
1
38
นอกจากนี้ยังพบวาความสัมพันธระหวาง Attribute ที่ไมไดเปน PK คือ Attribute City และ Rating โดย Attribute City สามารถระบุคาการจัดอันดับของผูผลิต ซึ่งเปนความสัมพันธ ระหวางคาของ Attribute แบบ Transitive Dependency ิ ังกลาวจะกอใหเกิดความผิดพลาดในการเพิ่ม ลบ หาก relation ใดมีคุณสมบัตด หรือปรับปรุงขอมูลได เชน
ความผิดพลาดที่เกิดจากการปรับปรุงขอมูล (Update (Update Anomaly) Anomaly) ความผิดพลาดที่เกิดจากการลบขอมูล (Delete (Delete Anomaly) Anomaly)
39
Relation Supplier1 อยูในรูปแบบ 2NF
204204 DBASE DES & DEV
ไมมคี าของขอมูลซ้ํากัน คาของ Attribute ที่ไมไดเปนคียหลักอื่นๆ สามารถระบุคาไดโดยคาของ Attribute ที่เปนคียหลัก ตัวอยาง : กรณีที่ทราบรหัสผูผลิต คือ S1 จะสามาถ ทราบวาผูผลิตชื่ออะไร จะสามาถทราบว อยูที่จังหวัดใด และอยูในจังหวัดที่ถูกจัดอันดับไวเปนอันดับที่เทาไร แู ขง เพราะวาไมมีคียใดทีม่ ีคุณสมบัติเปนคียหลักเชนเดียวกับ Relation นี้ไมมีคียค รหัสผูผลิต ถึงแมวาชื่อจังหวัดสามารถที่จะระบุการจัดอันดับของผูผลิต แตไม สามารถระบุชื่อของผูผลิตไดชัดเจน ตัวอยาง : กรณีที่ทราบชื่อจังหวัด คือ กรุงเทพฯ ชื่อของผูผลิตอาจเปน ติ๋ม หรือ แดง ก็ได ซึ่งไมสามารถกําหนดชัดเจนไดวาผูผลิตชื่ออะไร
204204 DBASE DES & DEV
204204 DBASE DES & DEV
S1
204204 DBASE DES & DEV
37
41
40
การแกไขปญหา Attribute City ที่สามารถระบุคา Attribute Rating ไดนั้น จะตองทําการแตก relation Supplier1 เปน 2 relation (Decomposition) โดยการแยก Attribute ที่ถูกกําหนดคากับ Attribute ที่เปนตัวกําหนดคา (Determinant) Determinant) ออกเปน relation ใหม และ กําหนดให Attribute ที่เปนตัวกําหนดคาเปน PK ของ relation ใหม relation ใหมประกอบดวย Supplier (S_No, S_Name, City) มี S_No S_No เปน PK และมี City เปน FK City (City, Rating) มี City เปน PK
204204 DBASE DES & DEV
42
7
ตัวอยาง Bad Decomposition กรณีที่แตก relation Supplier1 ออกเปน Supplier (S_No, S_Name, City) มี S_No S_No เปน PK Supplier2 (S_No, Rating) มี S_No S_No เปน PK
วิธีการแตก relation (Decomposition) Decomposition) มีขั้นตอนดังนี้ นํา Attribute ที่ถูกกําหนดคา กับ Attribute ที่เปนตัวกําหนดคา แยกออกเปน relation ใหม กําหนดให Attribute ที่เปนตัวกําหนดคาเปน PK ของ relation ใหม
ขอควรระวัง : ในบางกรณีผูออกแบบเคารางของฐานขอมูลพยายามแตก relation ที่มีจํานวน Attribute มากๆ ออกเปน relation ตางๆ มากมายนั้น อาจกอใหเกิดปญหาการแตกฐาน Relation ที่ไมเหมาะสมได (Bad Decomposition) ได
204204 DBASE DES & DEV
การเพิ่มชื่อจังหวัดและการจั และการจัดอันดับจะทําไมได จนกวาจะมีผูผลิตรายใดรายหนึ่ง อยูที่จังหวัดที่จะเพิ่มชื่อและการจัดอันดับนั้น Relation Supplier และ Supplier2 เปน relation ที่ไมมีความเปนอิสระตอกัน เพราะวา Attribute City เปนตัวกําหนด Attribute Rating นั่นเอง
S_No
Rating
City
กรุงเทพฯ สมุทรปราการ
Supplier2
S3 S4
3 2
S5
1
204204 DBASE DES & DEV
44
Relation ที่จะอยูในรูปของ BCNF ก็ตอเมื่อ Relation นั้นๆ อยูในรูปแบบบรรทัดฐานขั้นที่ 3 (3NF) 3NF) ไมมี Attribute ใดๆ ใน relation ที่สามารถระบุคาของ Attribute ที่เปน
คียหลัก (PK (PK)) หรือสวนหนึ่งสวนใดของคียหลัก (ในกรณีที่คียหลักเปน คียผสม) สม) ได
45
โดยทั่วไปรูปของ BCNF จะอยูในรูปของ 3NF แตไมจําเปนเสมอไป ที่รูปของ 3NF 3NF จะอยูในรูปของ BCNF เนื่องจาก
ปทุมธานี
แดง จุม
Rating 2 3
Rating 204204 DBASE DES & DEV
นอย
S4 S5
S_No S1 S2
Boyce / Codd Normal Form : BCNF
City
S3
43
จากตัวอยางจะพบวา การแตก relation ดังกลาวอาจกอใหเกิดความ ผิดพลาดในการเพิ่ม ลบ หรือแกไขขอมูลได เนื่องจาก
S_No
Supplier
S_No S_Name City ติ๋ม กรุงเทพฯ S1 ตอย ปทุมธานี S2
รูปแบบ BCNF เปนรูปแบบที่ขยายขอบเขตของรูปแบบ 3NF ใหมีความเหมาะสมยิ่งขึ้น รูปแบบของ Relation ที่จะตองผานการทําใหเปน BCNF จะตองมีคุณสมบัติ ดังนี้ ูแขงหลายคีย (Multiple เปน relation ที่มีคียค (Multiple Candidate Key) Key) โดยที่ - คียค ูแขงเปนคียผสม (Composite (Composite Key) Key) และ - คียค ูแขงนั้นมีบางสวนซ้ําซอนกัน (Overlapped (Overlapped)) (มี attribute บางตัวรวมกัน)
204204 DBASE DES & DEV
47
204204 DBASE DES & DEV
Relation Supplier3 Supplier3
สมมติ : ชื่อของผูผลิต (S_Name (S_Name)) เปนคาไมซ้ํากัน และ มีคุณสมบัติเปน PK ไดเชนกัน
46
ประกอบดวย Attribute ดังนี้ S_No S1
S_Name ติ๋ม
P_No P1
Qty 300
S1 S1 S1
ติ๋ม ติ๋ม ติ๋ม
200 400 200
S2 S2 S3
ตอย ตอย นอย
P2 P3 P4 P1 P2 P2
S4 S4 S4
แดง แดง แดง
P2 P4 P5
200 300 400
204204 DBASE DES & DEV
300 400 200
48
8
Relation Supplier3 เปน relation ที่มีปญหา เพราะ
มีคียค ูแขงเปนคียผสม และมีความซ้ําซอนกัน ู ขงทีม่ ีคุณสมบัติเปน PK ของ relation อาจจะเปน Attribute S_No และ P_No คียคแ P_No หรือ Attribute S_Name และ P_No P_No ก็ได
ในกรณีที่ชื่อผูผลิตไมซ้ํากัน relation นี้ไมไดอยูในรูปแบบ BCNF เพราะ
เมื่อเลือกคียค ูแขงใดเปน PK แลว คียค แู ขงที่ไมไดถูกเลือกจะยังปรากฏซ้ําซอนอยู ใน relation นี้ และมีคุณสมบัติในการระบุคาของ Attribute ที่เปน PK
204204 DBASE DES & DEV
Attribute S_Name จะมีคุณสมบัติในการระบุคาของ Attribute S_No
S_No
S_Name
Qty
P_No
Relation นี้ยังเปน relation ที่ประกอบดวยขอมูลที่ซ้ําซอนกัน ซึ่งจะ กอใหเกิดความผิดพลาดจากการเพิ่ม แกไขหรือปรับปรุงขอมูล เชน การเปลี่ยนชื่อของผูผลิต S1 จะตองแกไขขอมูลในทูเพิ เพิลตางๆ ซึ่งอาจเกิดความผิดพลาดได (แกไขขอมูลไมครบถวน) น)
49
แนวทางแกไข
กรณีที่เกลือ Attribute S_No และ P_No เปน PK จะเกิดปญหา
204204 DBASE DES & DEV
50
สรุป :
การแตก relation Supplier3 ออกเปน relation ใหม โดยการแยก Attribute ที่สามารถระบุคาของคียคูแขงแยกเปนอีก relation หนึ่ง ซึ่งอาจทําได 2 กรณี คือ
กรณีที่หนึ่ง
Supplier4 Supplier4 (S_No, S_Name) S_Name) มี S_No, S_No, S_Name เปน PK Order2 (S_No, P_No, Qty) Qty) มี S_No, S_No, P_No เปน PK
กรณีที่สอง
Supplier4 Supplier4 (S_No, S_Name) S_Name) มี S_No, S_No, S_Name เปน PK Order2 (S_Name, P_No, Qty) Qty) มี S_Name, S_Name, P_No เปน PK
รูปแบบ BCNF เปนรูปแบบที่ขยายขอบเขตของรูปแบบ 3NF โดยมี
การตรวจสอบ relation วามี Attribute อื่นที่ไมใช PK แตสามารถระบุคา ของ PK ได ดังนี้ Attribute ที่เปน PK
Attribute อื่นๆ
C
A
D
B 204204 DBASE DES & DEV
51
Forth Normal Form : 4NF Relation ที่จะอยูในรูปของ 4NF ก็ตอเมื่อ
204204 DBASE DES & DEV
Relation SPJC
เปน relation ที่ไมมีความสัมพันธในการระบุคาของ Attribute
แบบหลายคา โดยที่ Attribute ที่ถูกระบุคาหลายคาเหลานี้ไมมี ความสัมพันธกัน Attribute จะตองไมมีความสัมพันธแบบ Independently Multivalued Dependency Relation ที่อยูในรูปแบบ 4NF จะตองมี Attribute อยางนอย 3 Attribute 53
52
ประกอบดวย Attribute ดังนี้
Relation SPJC ประกอบดวย
Relation นั้นๆ อยูในรูปแบบ BCNF
204204 DBASE DES & DEV
E
Attribute รหัสผูผลิต (S_No) Attribute รหัสโครงการ (PJ_No) Attribute ชื่อจังหวัดที่โรงงานของ ผูผลิตตั้งอยู (City)
ขอกําหนด :
S_No
PJ_No
S1
PJ01 PJ01 PJ02
City กรุงเทพฯ ปทุมธานี กรุงเทพฯ
PJ02 PJ03
ปทุมธานี สมุทรปราการ
PJ03 PJ04
อยุธยา สมุทรปราการ
PJ04 PJ05 PJ05
อยุธยา สมุทรปราการ อยุธยา
S2
ผูผลิตแตละรายสามารถผลิตใหกับ โครงการตางๆ ไดมากกวา 1 โครงการ และ ผูผลิตแตละรายมีโรงงานตั้งอยูใน จังหวัดตางๆ ไดมากกวา 1 จังหวัด 204204 DBASE DES & DEV
54
9
S_No S1
S2
PJ_No PJ01
City กรุงเทพฯ
PJ01 PJ02 PJ02
ปทุมธานี กรุงเทพฯ ปทุมธานี
PJ03
สมุทรปราการ
PJ03 PJ04 PJ04
อยุธยา สมุทรปราการ อยุธยา
PJ05 PJ05
สมุทรปราการ อยุธยา
ทําให relation SPJC อยูในรูปแบบ BCNF โดยใหทุก Attribute ประกอบกันเปน PK S_No
PJ_No
S1 S1 S1
PJ01 PJ01 PJ02
City กรุงเทพฯ ปทุมธานี กรุงเทพฯ
S1 S2
PJ02 PJ03
ปทุมธานี สมุทรปราการ
S2 S2
PJ03 PJ04
อยุธยา สมุทรปราการ
PJ04 PJ05 PJ05
อยุธยา สมุทรปราการ อยุธยา
S2 S2 S2 204204 DBASE DES & DEV
Relation SPJC อยูในรูปแบบ BCNF เมื่อมี Attribute ทุก Attribute ประกอบกันเปน PK S_No มีความสัมพันธในการระบุคา PJ_No แบบหลายคา (S_No (S_No >> PJ_No) PJ_No) S_No มีความสัมพันธในการระบุคา City แบบหลายคา (S_No (S_No >> City) City)
ดวยเหตุที่ Attribute ทั้งสองไมมีความสัมพันธกันแตมาอยูใน relation เดียวกันจะกอใหเกิดความซ้ําซอนของขอมูลขึ้นได เพราะวา คาของชื่อ จังหวัดจะปรากฏใน relation ซ้ํากัน เพื่อใหขอมูลในแตละทูเพิ เพิลมีความ สมบูรณ
แมวา relation SPJC จะอยูในรูปแบบ BCNF โดยที่ทุก Attribute ประกอบกันเปน PK แต relation ก็ยังมีปญหาแฝงอยูเชนกัน 204204 DBASE DES & DEV
57
Fifth Normal Form : 5NF ชื่ออื่นของรูปแบบบรรทัดฐานขั้นที่ 5 (5NF) 5NF)
คือ
ProjectProject-Join Normal Form (PJ/NF)
Relation ที่จะอยูในรูปของ 5NF 5NF ก็ตอเมื่อ Relation นั้นๆ อยูในรูปแบบบรรทัดฐานขั้นที่ 4 (4NF) 4NF) ไมมี Symmetric Constraint หากมีการแตก relation ออกเปน relation ยอย (Projection (Projection)) เมื่อทําการเชื่อมโยง relation ยอยทั้งหมด (Join (Join)) จะไมกอใหเกิดขอมูลใหม
ที่ไมเหมือน relation เดิม (Spurious (Spurious Tuple) Tuple) 204204 DBASE DES & DEV
ผูผลิตรหัส S2 ยายโรงงานจาก สมุทรปราการ ไปอยูจังหวัดอื่น ก็ตองทํา การแกไขขอมูลในทูเพิ เพิลตางๆ หรือ กรณีที่การเก็บขอมูลชื่อจังหวัดที่ตั้งของผูผลิตคนใหมที่ยงั ไมเคยรับงานโครงการ ใดเลยจะทําไมได
แนวทางการแกไขปญหา
การแตก relation SPJC ออกเปน 2 relation SPJ (S_No, PJ_No) มี S_No, S_No, PJ_No เปน PK SC (S_No, City) มี S_No, S_No, City เปน PK
204204 DBASE DES & DEV
56
SPJ
SPJC
S_No ซึ่งมีความสัมพันธในการระบุคา PJ_No และ City ไดหลายคา
Relation SPJC อาจเกิดปญหาความผิดพลาดจากการเพิ่ม ลบ หรือปรับปรุง ขอมูลได เชน
55
Relation SPJC ประกอบดวย
59
S_No S1
PJ_No PJ01
City กรุงเทพฯ
S1 S1 S1 S2
PJ01 PJ02 PJ02
ปทุมธานี กรุงเทพฯ ปทุมธานี
PJ03
สมุทรปราการ
S2 S2 S2
PJ03 PJ04 PJ04
อยุธยา สมุทรปราการ อยุธยา
S2 S2
PJ05 PJ05
สมุทรปราการ อยุธยา
S_No
PJ_No
S1 S1 S2
PJ01 PJ02 PJ03
S2
PJ04
S2
PJ05
S_No S1 S1 S2
City กรุงเทพฯ ปทุมธานี สมุทรปราการ
S2
อยุธยา
SC
204204 DBASE DES & DEV
58
ตัวอยางที่ 1 relation SPP ประกอบดวย Attribute ตางๆ ดังนี้ Attribute ชื่อผูผลิต (S_Name) Attribute ชื่อสินคา (P_Name) Attribute ชื่อโครงการ (PJ_Name) SPP โดยที่ Attribute ทั้งสามเปน PK S_Name SERI SERI WICHAI SERI 204204 DBASE DES & DEV
P_Name PJ_Name PEN Proj2 TABLE Proj1 PEN Proj1 PEN Proj1 60
10
S_Name
Relation PPJ
P_Name
SERI
PEN
SERI WICHAI
TABLE PEN
Relation SP
SPP
P_Name
S_Name
Proj2
SERI
Proj2 Proj1 Proj1
Proj1 Proj1
SERI WICHAI
PEN TABLE PEN
S_Name SERI SERI WICHAI SERI
PJ_Name
PJ_Name
P_Name PJ_Name PEN Proj2 TABLE Proj1 PEN Proj1 PEN Proj1
61
เมื่อแตก relation ออกมาแลวทําการเชื่อมโยง relation ยอยอีกครั้ง หากไมมี ขอมูลทีแ่ ตกตางไปจาก relation เดิม ก็สามารถจะแตก relation นั้นได เมื่อแตก relation ออกมาแลวทําการเชื่อมโยง relation ยอยอีกครั้ง แลวขอมูล แตกตางไปจาก relation เดิม หรือเกิดขอมูลไมเหมือนกับ relation เดิม ก็ไม ควรแตก relation นั้น ใหถือวา relation เดิมอยูในรูปแบบ 5NF
204204 DBASE DES & DEV
Relation CPJ
City
Bangkok
S1 S3
Rayong Rayong
Relation SC
City
Bangkok Rayong Rayong
แนวทางแกไขปญหาคือ
S1
การลบขอมูลของทู ของทูกเพิลที่ 3 (WICHAI, PEN, Proj1) Proj1) สงผลใหขอมูลของ ชื่อผูผลิตที่ชื่อ “WICHAI” WICHAI” หายไปจากฐานขอมูล หรือ การเปลี่ยนชื่อสินคาใดสินคาหนึ่ง จะตองทําการปรับปรุงขอมูลหลายทูเพิ เพิล
เมื่อแตก relation SPP ออกเปน relation SP, PPJ และ PJS แลว หากนํา relation ทั้งสามมาเชื่อมโยงกัน (Join (Join)) จะมีขอมูลเหมือนใน relation เดิม (Symmetric Constraint) Constraint) คือ ไมมีลักษณะของทูเพิ เพิลที่เกินมา เรียกวา Spurious Tuple
S_No
Relation SPP อยูในรูปแบบ 4NF ประกอบดวย Attribute ทั้งสามประกอบ กันเปน PK แตประสบปญหาในการปรับปรุงแกไขขอมูล เชน
Relation PJS
204204 DBASE DES & DEV
204204 DBASE DES & DEV
S_No
PJ_No
S1
PJ01
PJ01 PJ02 PJ01
S1 S3
PJ02 PJ01
Relation PJS
62
ตัวอยางที่ 2 relation SCPJ ประกอบดวย Attribute ตางๆ ดังนี้ Attribute รหัสผูผลิต (S_No) Attribute ชื่อจังหวัดของผูผลิต (City) Attribute รหัสโครงการ (PJ_No) SCPJ โดยที่ Attribute ทั้งสามเปน PK
63
PJ_N0
การแตก relation SPP ออกเปน 3 relation ยอย (Projection (Projection)) แตละ relation ยอยประกอบดวย Attribute แตละคูเปน PK
S_No S1
City Bangkok
PJ_No PJ01
S1 S3
Rayong Rayong
PJ02 PJ01
204204 DBASE DES & DEV
64
Relation SCPJ อยูในรูปแบบ 4NF ประกอบดวย Attribute ทั้งสามประกอบ กันเปน PK แตมีปญหาที่อาจเกิดความผิดในการเพิ่ม ลบ หรือปรับปรุง ขอมูล แนวทางแกไขปญหาคือ การแตก relation SPP ออกเปน 3 relation ยอย (Projection (Projection)) ประกอบดวย Relation SC, CPJ และ PJC มูลเกินมา แตปญหาที่เกิดขึ้น คือ เมื่อนํา relation ทั้งสามมาเชื่อมโยงกัน จะมีขอ (Spurious Tuple) คือ S1 Rayong PJ01 ซึ่งไมมีใน relation เดิม ้ กรณีที่เกิดปญหาในลักษณะนี้ก็ไมควรจะแตก relation นัน
Spurious Tuple
S_No S1 S1 S1 S3
City Bangkok Rayong Rayong Rayong
PJ_No PJ01 PJ02 PJ01 PJ01
204204 DBASE DES & DEV
65
204204 DBASE DES & DEV
66
11
การแตก relation มากเกินไป (Overnormalization)
ประเด็นที่ควรคํานึงถึง ในการทําใหเปนรูปแบบบรรทัดฐาน (Normal (Normal Form) Form)
การแตก relation มากเกินไป (Overnormalization (Overnormalization))
การดีนอร นอรมอลไลเซชั อลไลเซชัน (Denormalization (Denormalization))
204204 DBASE DES & DEV
เพื่อลดปญหาในดานความซ้ําซอนของขอมูล เพื่อลดปญหาในเรื่องการเพิ่ม การลบ หรือการปรับปรุงขอมูล
โดยทั่วไปการออกแบบในระดับแนวคิด ผูออกแบบจะพยายามวิเคราะห relation ใหอยูในรูปแบบ 3NF 3NF กรณีที่เกิดปญหาตางๆ ที่จําเปนตองทําตอไปถึงรูปแบบ BCNF, BCNF, 4NF และ 5NF (เกิดขึ้นนอยมากในทางปฏิบัติ) 204204 DBASE DES & DEV
68
การดีนอร นอรมอลไลเซชัน (Denormalization)
การแตก relation ออกเปน relation ยอยมากเกินไปมีผลตอประสิทธิภาพใน การทํางานของฐานขอมูล เชน ในการคนคืนขอมูลจะตองใชเวลามากกวาเดิม เปนตน
204204 DBASE DES & DEV
67
ดวยเหตุผลดังกลาว ผูออกแบบไมควรพยายามที่จะแตก relation มากเกินความจําเปน (Overnormalization (Overnormalization)) เพราะ
วัตถุประสงคของการทําใหเปนรูปแบบบรรทัดฐาน คือ
69
กรณีที่บาง relation ถูกออกแบบโดยการไมทําใหอยูในรูปแบบบรรทัดฐาน ที่เปนไปตามกฏเกณฑ ไปตามกฏเกณฑทีท่กี ําหนดไว เชน relation นั้นควรจะปรับใหอยูในรูปแบบ 3NF 3NF แตหยุดอยูเพียง รูปแบบ 2NF NF เป น ต น 2 อาจเปนเพราะเหตุผลในเรื่องของประสิทธิภาพในการเรียกดู หรือ การคนคืนขอมูล และยอมใหเกิดความซ้ําซอนของขอมูลได
204204 DBASE DES & DEV
70
204204 DBASE DES & DEV
72
การดีนอร นอรมอลไลเซชั อลไลเซชันอาจกอใหเกิดปญหาความซ้ําซอนของขอมูล เกิดขึ้นได ควรมีการระบุสาเหตุ และวิธีการในการปรับปรุงขอมูลในโปรแกรม ประยุกตใชงาน เพื่อปองกันไมใหเกิดปญหาขอมูลไมถูกตอง ถาขอมูลใน relation นั้นๆ สวนใหญจะเปนการเรียกดูขอมูล (Select (Select)) มากกวา การเพิ่ม ลบ หรือปรับปรุงขอมูล เพื่อเพิม่ ประสิทธิภาพในการทํางานของ ฐานขอมูล และไมมปี ญหาดานความไมถูกตองของขอมูลที่ซ้ําซอนกันได
204204 DBASE DES & DEV
71
12