Chapter05 The Relational Data Model And Relational Database Constraints

5-1

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

บทที่ 5* รูปแบบขอมูลเชิงสัมพันธและขอบังคับของฐานขอมูลเชิงสัมพันธ The Relational Data Model and Relational Database Constraints

วัตถุประสงค 1. เพื่อศึกษาแนวคิดรูปแบบเชิงสัมพันธ 2. เพือ่ ใหสามารถกําหนดรูปแบบเชิงสัมพันธและเคารางฐานขอมูลเชิงสัมพันธ 3. เพือ่ ใหสามารถสรางขอกําหนดการใชงานในการแทรกขอมูล การลบขอมูลและการเปลี่ยนแปลงขอมูล

5.1

แนวคิดรูปแบบเชิงสัมพันธ รูปแบบเชิงสัมพันธนี้ถูกคิดคนโดย ดร. อี เอฟ คอดด (Dr. E. F. Codd) จากบริษัทไอบีเอ็มในปค.ศ 1970 ซึ่ง กลาวไววา “รูปแบบเชิงสัมพันธนี้ถือไดวาเปนการเก็บขอมูลรวมกันที่มีขนาดใหญ” โดยฐานขอมูลเชิงสัมพันธจะ ประกอบดวยกลุมขอมูลที่มีความสัมพันธซึ่งกันและกัน มาเชื่อมโยงกันโดยอาศัยหลักทฤษฎีความสัมพันธเชิง คณิตศาสตร มาใชในการจัดการขอมูลไดเปนอยางดี

5.2

นิยามของความสัมพันธ (Relation) 5.2.1 นิยามอยางไมเปนทางการ ความสัมพันธ (Relation) หรือเรียกทับศัพทวารีเลชัน มีคําเรียกทั่วไปวาตาราง (Table) ซึ่งประกอบไปดวยกลุม ของแถว (Row) หรือเรคคอรด (Record) โดยเรคคอรดจะแทนกลุมของขอมูลที่มีความสัมพันธกัน ในแบบจําลอง เชิงสัมพันธนั้นขอมูลในแตละแถวจะแทนเอนทิตีจริง ซึ่งชื่อของตารางและคอลัมน (Column) จะถูกใชชวยในการ แปลงความหมายคาในแตละแถว ซึ่งขอมูลที่อยูในแตละแถวนั้นจะตองไมซ้ํากัน บางครั้งขอมูลในแตละแถวที่กําหนด ไวเปนหมายเลขรหัสหรือหมายเลขที่เรียงกัน จะถูกใชในการอางถึงในแตละตาราง โดยคอลัมนที่ถูกอางถึงนั้นจะเปน คอลัมนที่สําคัญ เราจะเรียกชื่อในแตละคอลัมนวาแอทตริบิวต (Attribute)

5.2.2 นิยามอยางเปนทางการ รีเลชั่นสามารถกําหนดรูปแบบไดหลายแบบ ถาเคารางของรีเลชั่นเปน R(A1,A2,A3,…,An) สัญกรณ R เปนชื่อ ของความสัมพันธหรือตาราง และ Ai คือชื่อแอทตริบิวตตัวที่ i โดย i มีคาตั้งแต 1 จนถึง n ซึ่งแตละแอทตริบิวตจะมี คาทั้งหมดที่เปนไปได จะถูกเรียกวาโดเมน (Domain) ของแอทตริบิวต ซึ่งสามารถเขียนไดวา Dom(Ai) ตัวอยาง Customer(Cust-id,Cust-name,Address,Phone#)

หมายถึงตารางความสัมพันธชื่อ Customer ประกอบดวย 4 แอทตริบิวต ไดแก Cust-id Cust-name Address และ Phone# โดยแตละแอทตริบิวตนั้น จะมีกลุมขอมูลหรือโดเมนเปนของตัวเอง ซึ่งจะขึ้นอยูกับชนิด ขอมูลตามที่ไดกําหนดไว เชนแอทตริบิวต Cust-id สามารถกําหนดใหเปนชนิด Text หรือ Character โดยมี 6 ตัวอักษร (Digit) หมายถึงแอทตริบิวต Cust-id จะมีขอมูลเปนตัวอักษร (ไมสามารถคํานวณได) ที่ไมเกิน 6 ตัวอักษร * อางอิงจากบทที่ 5 ของเอกสารอางอิง [1]

5-2

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

กลุมขอมูลในแตละแถวจะถูกเรียกวา ทูเพิล (Tuple) โดยขอมูลนั้นจะประกอบดวยขอมูลในโดเมนของแตละแอทตริ บิวต เชน ทูเพิล < “243658”, “Wiwat”, “235 Pomprakarn Bangkok” , “0815648523” > ซึ่งจะ ประกอบดวยขอมูล 4 กลุม โดยขอมูลแตละกลุมนั้นจะเปนสมาชิกของโดเมนในแตละแอทตริบิวต เชน Wiwat เปน สมาชิกของโดเมนในแอทตริบิวต Cust-name ของตารางความสัมพันธ Customer โดเมนนั้นจะถูกกําหนดตามชนิดของขอมูล (data-type) หรือรูปแบบของขอมูล (format) ซึ่งอาจจะอยูในรูปของ เลขหมายโทรศัพท เชน (xxx)-xxx-xxxx หรือกําหนดใหอยูในรูปของวัน (Date) ก็สามารถกําหนดไดเปน ddmmm-yyyy ซึ่ง แอทตริบิวตที่เกี่ยวกับวันที่นั้นจะถูกนําไปใชในการกําหนดขอมูลอยางเชน วันที่อยูในใบแจงยอด สินคา หรือวันที่อยูในใบเสร็จรับเงิน ถา R(A1,A2,A3,…..,An) แลว r(R) หมายถึง เซตของทูเพิลที่อยูในรีเลชั่นนั้น โดยแตละทูเพิลนั้นจะ ประกอบดวยคาของโดเมนในแตละแอทตริบิวต ซึ่งรวมไปถึงคาวาง (null) ดวย ดังนั้น ถา R เปนเคารางของรีเลชัน (schema) แลว r จะเปนขอมูลของกลุมสมาชิกทั้งหมดที่อยูใน R หรือ R ถูกนับไดวาเปนรีเลชันโดยตรง (intension) แลวเรียก r เปนสวนขยายของรีเลชั่น (extension) ตัวอยาง กําหนดตารางรีเลชั่น X(A,B,C) แลว Dom(A)={1,2,3} Dom(B)={M,N} Dom(C)={0,1} เราจะไดผลผลิตของจํานวนทูเพิลทั้งหมดเปนผลคูณคารทีเซียน (Cartesian product) ของจํานวน แอทตริบิวตของทุกโดเมน ดังนั้น จํานวน Dom(A) x จํานวนDom(B )x จํานวนDom(C) = 3x2x2 = 12 ทูเพิล เพราะฉะนั้น ตารางรีเลชั่น X มีทูเพิลสูงสุดได 12 ทูเพิล ในที่นี้ r(X) = {<1,M,1>,<2,M,0>,<3,N,0>,<2} สรุปนิยามศัพทที่เกี่ยวของ

ศัพททั่วไป

ศัพทเทคนิค

ตาราง (Table)

รีเลชัน (Relation)

คอลัมน (Column)

แอทตริบิวต (Attribute)/ โดเมน (Domain)

แถว (Row)

ทูเพิล (Tuple)

ขอบเขตคาของขอมูล (Values in a column)

โดเมน (Domain)

ตารางที่ 5.1 ขอบเขตตาราง (Table Definition) สรุปนิยามศัพท ที่เกี่ยวของ ตารางที่มีขอมูลแลว (Populated Table)

เคารางของรีเลชัน (Schema of a Relation) สวนขยาย (Extension)

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

5-3

รูปที่ 5.1 ตัวอยางรีเลชัน รูปที่ 5.1 แสดงถึงชื่อรีเลชัน คือ Student มีแอทตริบิวต 7 แอทตริบิวต ประกอบดวย Name, SSN, HomePhone, Address, OfficePhone, Age และ GPA โดยมีจํานวน 5 ทูเพิล

5.3

ลักษณะของรีเลชัน • • • • • • •

ทูเพิลที่อยูในรีเลชันจะแสดงอยูในรูปตาราง แอทตริบิวตที่อยูในเคารางรีเลชันนั้นจะพิจารณาจาก R(A1,A2,A3,….,An) โดยมีขอบเขตขอมูลอยูในแตละ ทูเพิล t= ขอบเขตขอมูลในทูเพิลจะไมสามารถแยกขอมูลยอยออกไปไดอีก (Atomic) R(A1,A2,A3,…An) หมายถึง เคารางของรีเลชัน R ในระดับที่ n t = หมายถึง ทูเพิลที่อยูในเคารางรีเลชัน r(R) ซึ่ง Vi จะเปนคาขอมูลที่อยูภายใต แอทตริบิวต Ai เราจะอางถึงคาขอมูลของแตละทูเพิลไดเปน t[Ai] =Vi หรือ t.Ai = Vi (คาขอมูลของแอทตริบิวต Ai สําหรับทูเพิลที่ t) ซึ่งในทํานองเดียวกัน t(Au,Av,…….,Aw) จะเปนการอางถึง ซับทูเพิลของ t โดยมีคาขอมูลของแอทตริบิวต เปน Au,Av,….,Aw ตามลําดับ

รูปที่ 5.2 แสดงถึงลักษณะของ รีเลชัน

5.4

เงื่อนไขบังคับ 5.4.1 เงื่อนไขบังคับของฐานขอมูลเชิงสัมพันธ (Relational Database Constraints) • • •

เงื่อนไขบังคับที่ขึ้นอยูกับภายในโมเดล : จะไมมีทูเพิลที่ซ้ํากัน เงื่อนไขบังคับที่ขึ้นอยูกับเคาราง : จะตองมีการกําหนดขอบเขตขอมูลตามเคาราง ที่ไดมีการกําหนดไวอยูใน ภาษาสําหรับนิยามขอมูล (Data Definition Language หรือ DDL) เงื่อนไขบังคับที่ขึ้นอยูกับโปรแกรมประยุกต : จะถูกแสดงและนําไปใชโดยโปรแกรมประยุกต

5.4.2 เงื่อนไขบังคับของรีเลชัน (Relation Constraints)

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

5-4

จะพิจารณาจากความสัมพันธที่เปนไปไดทั้งหมด ซึ่งแบงเปนชนิดตางๆดังนี้ • เงื่อนไขบังคับของโดเมน (Domain constraints) • เงื่อนไขบังคับของ Key (Key constraints) • เงื่อนไขบังคับของ Entity (Entity integrity constraints) • เงื่อนไขบังคับของการอางอิง (Referential integrity constraints) • เงื่อนไขบังคับของชนิดอื่นๆ (Other types of constraints)

5.4.3 เงื่อนไขบังคับของโดเมน (Domain Constraints) • • •

ภายในแตละทูเพิลจะประกอบไปดวยคาขอมูลของแตละแอทตริบิวต (attribute A) ซึ่งจะตองเปนคาที่ไม สามารถแตกยอยไปไดอีกโดยเปนคาขอมูลที่สัมพันธกับโดเมน Dom(A) ชนิดขอมูลในแตละแอทตริบิวตนั้น สามารถกําหนดไดเปน เลขจํานวนเต็ม (Integers), เลขจํานวนจริง (real number) , ตัวอักษร (Characters) , บูลีน (Booleans) , วัน (Date) , เวลา (Time) เปนตน ขนาดของขอมูลจะขึ้นอยูกับชนิดขอมูลแตละประเภท

5.4.4 เงื่อนไขบังคับของคีย (Key constraints) • • • • • • • •

•

•

รูปที่ 5.3 แสดงถึงรีเลชันของ CAR ที่มี 2 คีย คูแขง

รีเลชันจะเปนตัวกําหนดเซตของทูเพิล (Key constraints) องคประกอบทั้งหมดที่อยูในเซตของทูเพิลมีความแตกตางกันอยางชัดเจน ซึ่งไมมี 2 ทูเพิลใดๆ จะมีคาของ ขอมูลเหมือนกันในแตละแอทตริบิวต ซับเซตของแอทตริบิวตในรีเลชันจะมีคุณสมบัติวา ไมมี 2 ทูเพิลในรีเลชันใดๆ ที่มีคาของขอมูลเหมือนกันในแต ละแอทตริบิวต ซึ่งซับเซตของแอทตริบิวตนั้นจะถูกเรียกวาซูเปอรคีย (Superkey หรือ SK) คาของ t1[SK] จะไมเทากันกับ คาของ t2[SK] ในแตละรีเลชันจะมีซูเปอรคียอยางนอยหนึ่งคา ซึ่งก็คือเซตของแอทตริบิวตทั้งหมด ซูเปอรคียสามารถมีแอทตริบิวตที่ซ้ํากันได ถา คีย K เปนซูเปอรคีย ของเคารางรีเลชัน R แลวถาไดมีการดึงแอทตริบิวตใดๆก็ตามจาก K จะทําให คีย K นั้นไมไดเปนซูเปอรคียของเคารางรีเลชัน R อีกตอไป เงื่อนไขบังคับ 2 ประการ ที่เปนคียอยางเดนชัด - ถามี 2 ทูเพิลที่มีความแตกตางกันทุกประการในรีเลชัน คาของขอมูลสําหรับแอทตริบิวตทั้งหมดที่เปนคีย จะไมสามารถมีคาเหมือนกัน - เราไมสามารถดึงแอทตริบิวตจากซูเปอรคียที่เล็กที่สุด (minimal superkey) เพราะวาตามเงื่อนไข บังคับนั้นจะตองมีคาขอมูลอยางนอย 1 คา ซูเปอรคียที่เล็กที่สุดจะเกิดจาก ซูเปอรคียใดๆก็ตามถูกดึงแอทตริบิวตออกแลว เซตของแอทตริบิวตไมเปนซูเปอร คีย - ตัวอยาง Car(State,Reg#,SerialNo,Make,Model,year) จะพบวามีคีย 2 ตัวคือ {State,Reg#} กับ {SerialNO} ซึ่ง {SerialNO,Make} จะเปนซูเปอรคีย แตไมไดเปนคีย ถาในรีเลชันนั้นมีคียคูแขง (Candidate Keys) ตองเลือกคียที่เหมาะสม 1 ตัว ใหเปนคียหลัก (Primary Key) แลวทําการขีดเสนใตใตชื่อแอทตริบิวตที่เปนคียหลัก

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

5-5

5.4.5 เงื่อนไขบังคับความบูรณภาพของเอนทิตี (Entity Integrity) • • • •

รูปที่ 5.4 แสดงถึง เคาราง ฐานขอมูลเชิงสัมพันธ ชื่อ Company

เคารางฐานขอมูลเชิงสัมพันธ จะเปนเซตของรีเลชัน จะไดเปน S={R1,R2,R3,…Rn} โดยที่ S เปนชื่อฐานขอมูล แอทตริบิวตที่เปนคียหลักในแตละรีเลชันของฐานขอมูล ไมสามารถเปนคาวางในทูเพิลใดๆ เพราะวาคาขอมูล ของคียหลักนั้น จะถูกใชในการระบุกับทูเพิลที่สัมพันธกัน t[PK] จะไมเปนคาวางสําหรับทูเพิลใดๆก็ตามที่อยูใน r(R) แอทตริบิวตที่ไมไดเปนคียหลัก บางครั้งก็ไมอนุญาต ใหเปนคาวางได

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

5-6

รูปที่ 5.5 แสดงถึงขอมูลใน เคารางฐานขอมูล Company

5.4.6 เงื่อนไขบังคับความบูรณภาพในการอางอิง (Referential integrity constraints) • • • •

เงื่อนไขบังคับนี้จะใชก็ตอเมื่อมีตั้งแต 2 รีเลชันขึ้นไป กําหนดความสัมพันธของทูเพิลทั้ง 2 รีเลชัน โดยการอางอิงรีเลชันซึ่งกันและกัน ทูเพิลที่อยูในรีเลชัน R1 จะมีแอทตริบิวตที่เปนคียนอก (Foreign Key หรือ FK) จะมีการอางอิงกับแอทตริ บิวตที่เปนคียหลักที่อยูในรีเลชัน R2 ถาจะกลาววาทูเพิล t1 ในรีเลชัน R1 จะไปอางอิงกับ t2 ในรีเลชัน R2 จะสามารถเขียนไดเปน t1[FK] = t2[PK]

•

รายการของเงื่อนไขบังคับ - คาของขอมูลในคียนอก (FK) ในรีเลชัน R1 จะมีการอางอิงไดก็ตอเมื่อเปนอยางใดอยางหนึ่งดังตอไปนี้ คือ 1. คาขอมูลนั้นจะตองเปนคาที่มีอยูแลวในคียหลักของรีเลชัน R2 หรือ 2. เปนคาวาง (null) - ถาเปนขอ (2) คียนอกที่อยูในรีเลชัน R1 ไมควรเปนสวนใดสวนหนึ่งของคียหลัก

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

5-7

รูปที่ 5.6 แสดงถึงการ เชื่อมโยงกันของเคา รางฐานขอมูลเชิง สัมพันธ Company

5.4.7 เงื่อนไขบังคับของชนิดอืน่ ๆ •

•

5.5

เงื่อนไขบังคับของความหมาย - ขึ้นอยูกับการใชงานโปรแกรมประยุกต แตไมสามารถกําหนดชัดเจนโดยใชโมเดล - เงื่อนไขบังคับนั้นจะกําหนดเฉพาะสําหรับผูใชงาน - SQL-99 จะเปนตัวกระตุน(trigger) และ มั่นคง(assertion) ที่สามารถใชงานได เงื่อนไขบังคับของการขึ้นอยูกับหนาที่การทํางาน - ถามีการสราง เซตของแอทตริบิวต 2 กลุม คือ แอทตริบิวต X กับแอทตริบิวต Y แลวใหมค ี วามสัมพันธ ตามหนาที่ โดยใหคาของขอมูลใน X ขึ้นอยูกับคาของขอมูลใน Y จะสามารถเขียนไดเปน XÆY

การจัดการขอมูลของรีเลชัน ในการจัดการขอมูลของรีเลชันนั้นจะมีการดําเนินการอยู 3 อยางคือ 1) การแทรกขอมูล (Insert) ซึ่งเปนคําสั่งที่ใช ในการเพิ่มทูเพิลใหมลงในรีเลชัน 2) การลบขอมูล (Delete) จะเปนคําสั่งที่ใชในการลบทูเพิลที่อยูในรีเลชัน และ 3) การปรับปรุงขอมูล (Update/Modify) จะเปนคําสั่งที่ใชในการเปลี่ยนแปลงขอมูลของทูเพิลที่อยูในรีเลชัน ทั้งนี้ เงื่อนไขบังคับของการบูรณาภาพไมควรไดรับผลกระทบจากการจัดดําเนินการขอมูล

5.5.1 การแทรกขอมูล (The Insert Operation) • •

จัดเตรียมรายการชองขอมูลสําหรับทูเพิลใหมซึ่งจะเขาไปแทรกอยูในรีเลชัน การแทรกจะทําใหเกิดปญหาไดดังตอไปนี้ - ทางดานโดเมน : ถาคาของขอมูลในแอทตริบิวตไมถูกตองตามเงื่อนไขที่กําหนด - ทางดานคีย : ถาคาขอมูลของคียในทูเพิลใหมมีอยูแลวในทูเพิลของฐานขอมูล - ทางดานเอนทิตี : ถาคียหลักของทูเพิลใหมเปนคาวาง - ทางดานการอางอิง : ถาคาของขอมูลในคียนอกไมตรงกับคาขอมูลกับคียหลักที่อยูในรีเลชันที่ใชในการ อางอิง

2110422 การออกแบบระบบการจัดการฐานขอมูล ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

5-8

5.5.2 การลบขอมูล (The Delete Operation) • •

การลบขอมูลจะเกิดปญหาเฉพาะการอางอิงเทานั้น - ถาทูเพิลถูกลบแลวยังมีการอางอิงคียนอกจากทูเพิลอื่นๆที่อยูในฐานขอมูล ถาเกิดเหตุการณที่ไมสามารลบขอมูลได - ยกเลิกการลบขอมูล - พยายามลบทูเพิลที่มีการอางอิงกับทูเพิลที่ไดถูกลบไปแลว - ปรับคาของขอมูลในแอทตริบิวตที่มีการใชอางอิง อาจจะปรับเปนคาวางหรือเปนคาอื่นที่มีอยูในทูเพิล - ดําเนินการกับผูใชอยางเฉพาะเจาะจง - ถาแอทตริบิวตที่ถูกใชอางอิงเปนสวนหนึ่งของคียหลัก จะไมสามารถทําใหเปนคาวางได

5.5.3 การปรับปรุงขอมูล (The Update Operation) •

การปรับปรุงสามารถกอใหเกิดปญหากับโดเมน คียหลัก เอนทิตี และการอางอิงได

แบบฝกหัด 1.

จงพิจารณารีเลชันนี้ แลวทําใหอยูในรูปแบบการเชื่อมโยงของรีเลชัน

2.

Student(SSN,Name,Major,Bdate) Course(Course#,Cname,Dept) Enroll(SSN,Course#,Quarter,Grade) Book_Adoption(Course#,Quarter,Book_ISBN) Text(Book_ISBN,Book_Title,Publisher,Author) รีเลชัน Car_Rent ประกอบดวยแอทตริบิวต Gear, Seat และ Fuel จงหาจํานวนทูเพิลสูงสุดของรีเลชั่น Car_Rent ถา โดเมน(Gear) = {Manual, Auto} โดเมน (Seat) = {5, 7, 12} โดเมน (Fuel) = {Diesel, Benzine , LPG, NGV} จงพิจารณาฐานขอมูล Companyในรูป เพื่อตอบคําถามตอไปนี้ แอทตริบิวสใดในรีเลชั่น WORKS_ON ใน ไมสามารถเปนคา NULL ได เพราะเหตุใด

3. •