บทที่ 4 Relational Database

1. บทที่ 4 Relational Database • Relational Databaseเป็นการจัดเก็บข้อมูล ลักษณะตารางสองมิติมีแถว(Tuple/record /row) และคอลัมน์(Attribute/field/column) • ทุกรีเลชั่นจะต้องผ่านกระบวนการ บรรทัดฐานข้อมูล(The Normalization Process)ในช่วงระหว่างการออกแบบฐานข้อมูล • เป็นฐานข้อมูลที่แสดงให้ผู้ใช้ได้มองเห็นภาพของข้อมูล ในระดับภายนอก (External Level) และระดับแนวคิด (Conceptual Level)โดยที่ผู้ใช้ไม่ต้องทราบ ถึงการจัดเก็บข้อมูลภายใน (Internal Level) • มีการใช้ 4 GLเป็นเครื่องมือในการเรียกใช้และจัดการข้อมูลโดยใช้คำสั่งแบบง่ายๆ เช่น SQL ใช้ร่วมกับ DBMS บนฐานข้อมูลเชิงสัมพันธ์ เช่น DB2 ,ORACLE, INGRES, FOXPRO , ACCESS เป็นต้น

2. Relational Data Model concept • relational data model เป็นแบบจำลองฐานข้อมูลของกลุ่ม relations ในรูป table รูปแสดง attributes และ tuples ของ relation นักศึกษา

3. Relational Data Structure • Relation คือตารางที่ประกอบด้วย rows และ columns เช่น ประวัติ น.ศ. • Attributeคือ column ของ relation เช่น รหัส ชื่อ-นามสกุล • Tupleส่วนของแถวข้อมูลแต่ละแถว(rows) ภายในตารางข้อมูล เช่น ข้อมูลประวัติของ น.ศ. ชื่อนายดำ ขำดี • Domain คือประเภทของ attribute หรือประเภทของข้อมูล เช่น id : string[10]; • Catalog คือส่วนที่ใช้จัดเก็บคำอธิบายข้อมูลของ Relation (Meta-Data) ในฐานข้อมูล

4. Relational Data Structure ตาราง แสดงความหมายของ relational data model • Cardinality ของ relation คือ จำนวนของทัพเพิล ที่อยู่ใน relation • Relational databaseคือ กลุ่มของ normalized relations • Relation schemaคือองค์ประกอบของ relation ที่แสดงกลุ่มของ attributes กับ Domain name ซึ่งแสดงในรูป R(A1: D1, A2: D2, A3: D3,…, An: Dn) เช่น นักศึกษา(รหัส:string[10],ชื่อ:string[30] ,ที่อยู่:string[45], โทรศัพท์:string[10],อายุ:int)

5. คุณสมบัติของ domain • Physical Description คือ domain ในทางกายภาพ เป็นการกำหนดประเภทของ attribute เช่น ชื่อของนักศึกษาเป็น text ขนาด 30 ตัวอักษร • Logical Description คือ domain ในทางตรรกเป็นกลุ่มของข้อมูลจริงๆ ที่จัดเก็บ เช่น ข้อมูลที่อยู่ของนายสกล อยู่ดี เก็บคนละ location กับข้อมูลที่อยู่ของนายเสกสรรค์

6. ดีกรีของความสัมพันธ์ • Degreeของ relation คือ จำนวนของ attributes ที่อยู่ใน relation • relation ที่มีเพียงหนึ่ง attribute เรียกว่า Unary • relation ที่มีสอง attributes เรียกว่า Binary • relation ที่มี 3 attributes เรียกว่า Ternary • ดังนั้นเรียกจำนวนของ attributes ใน relation ตามจำนวน attribute(n-ary)

7. Relational key • Relational key ใช้ในการกำหนดเอกลักษณ์ของแต่ละแถวในตาราง • Relational key ที่ต้องศึกษามีดังนี้ • Superkey คือ attribute หรือกลุ่มของ attributes ที่ใช้ในการระบุค่าของแต่ละแถวในตาราง • Candidate key คือ superkey ที่อยู่ใน relation ซึ่งมีคุณสมบัติหลักดังนี้ • 1. Uniqueness ค่าของ attribute ต้องไม่ซ้ำกับแถวอื่น • 2. Irreducibility บางส่วนของ key ไม่มีคุณสมบัติเอกลักษณ์(uniqueness) • 3. not null ไม่เป็นค่าว่าง

8. Candidate key คือ attribute ที่สามารถเป็นคีย์ได้ นั่นคือมีค่าไม่ซ้ำกับข้อมูลแถวอื่น และไม่เป็นค่าว่าง เช่นรหัสและ หมายเลขบัตรประชาชน นักศึกษา ตารางแสดง relation นักศึกษา

9. Composite key Composite key คือ key ที่มี attribute ประกอบกันมากกว่าหนึ่ง attributes รายงานผลการเรียน • จากตารางจะได้ว่า composite key คือรหัส กับ รหัสวิชา • Primary key คือ candidate key ที่ถูกเลือกให้เป็นตัวกำหนดค่าที่แตกต่างกันภายใน relation ในการเลือก primary key จะเลือกจาก attribute หรือบางส่วนของ attributes ที่สามารถกำหนดความแตกต่างระหว่าง tuple ได้ เช่น จาก relation นักศึกษา เลือก attribute รหัส เป็น primary key

10. Alternate key หรือ secondary keyคือ คีย์สำรอง หรือ candidate key ที่ไม่ได้รับเลือกให้เป็น primary key เช่น หมายเลขบัตรประชาชน • Foreign keyคือ candidate keyที่ถูกเลือกเป็น primary keyใน relationอื่น Foreign key อาจารย์ คณะ • Conceptual model หรือ Conceptual schema คือ relation ทั้งหมดในฐานข้อมูล เช่น ฐานข้อมูลของมหาวิทยาลัย มีกลุ่ม schemas คือ นักศึกษา อาจารย์ คณะ รายวิชา กลุ่มเรียน อาคาร เป็นต้น Primary key

11. นักศึกษา อาจารย์ คณะ รายวิชา Conceptual model หรือ Conceptual schemaของ ฐานข้อมูลของมหาวิทยาลัย (บางส่วน)

12. Relational Constraints • ข้อบังคับของ relational modelออกเป็น 3 ข้อ ดังนี้ • 1. Domain constraint 2. Key constraint 3. Integrity constraint • 1. Domain Constraints • เป็นข้อบังคับที่เกี่ยวกับการกำหนดค่าของแต่ละ attribute ซึ่งค่าของแต่ละ attribute ต้องมีประเภทตามที่กำหนดไว้ในโดเมน เช่น ตรวจสอบข้อมูลที่มีอยู่ตรงตามประเภทที่กำหนดหรือไม่ ถ้าข้อมูลไม่ตรงตามที่กำหนด แสดงว่าข้อมูลนั้นฝ่าฝืน Domain constraint เป็นต้น

13. key constraint • 2. Key constraint เป็นข้อบังคับเกี่ยวกับคีย์ • Key เป็นตัวกำหนดว่าข้อมูลในแต่ละแถวต้องไม่ซ้ำกัน และใช้กฎข้อบังคับเดียวกับ candidate key, primary key, composite key , secondary key/alternate key ดังนี้ • 1. Uniqueness มีค่าเดียว ไม่ซ้ำกับแถวอื่น • 2. Irreducibility คีย์ผสม เมื่อแยกแอตริบิวต์แล้วไม่สามารถเป็นคีย์ได้อีก • 3. not null ไม่เป็นค่าว่าง

14. Integrity constraint • 3. Integrity constraint มี 2 ข้อ คือ • 3.1 Entity integrity constraint (กฎความคงสภาพของ entity) • 3.2 Referential integrity constraint(กฎความคงสภาพของการอ้างอิง) • 3.1 Entity Integrity constraint เป็นข้อบังคับที่กำหนดว่า attributes ใดๆ ที่ประกอบกันเป็น primary key ของตารางต้องไม่มีค่าเป็นค่าว่าง(null)

15. Referential integrity constraint • 3.2Referential integrity constraintเป็นข้อบังคับในการกำหนด relation ระหว่าง 2 relations ซึ่งเป็นข้อบังคับที่ใช้ในการจัดการความขัดแย้งกันของข้อมูลที่อยู่ใน 2 relations • โดยแถวที่อยู่ในตารางหนึ่ง สามารถอ้างอิงถึงแถวที่อยู่ในอีกตารางได้ เช่น แถวของตารางอาจารย์ สามารถอ้างอิงถึงแถวของตารางคณะได้ เพราะค่าของรหัสคณะที่อยู่ตารางอาจารย์ ต้องตรงกับค่ารหัสคณะที่อยู่ในตารางคณะ ซึ่งเป็นแนวคิดของ Foreign key คณะ อาจารย์

16. Referential integrity constraint การกำหนด referential integrity constraint ระหว่าง 2 ตาราง โดยการกำหนด foreign key ของ R1 ซึ่งสามารถอ้างถึง R2 นั้น ต้องอยู่ในข้อบังคับดังนี้ 1. Foreign keyในตาราง R1ต้องมี domain เดียวกันกับ Primary key ของตาราง R2 ซึ่งหมายถึงForeign key ใน R1สามารถอ้างอิงค่าใน R2 ได้ 2. ค่าของ Foreign key ที่อยู่ใน R1จะแสดงค่าที่เป็น primary key ใน R2 หรือ แสดงค่า null

17. Referential integrity constraint • ตัวอย่างจากการจัดเก็บข้อมูลของอาจารย์ Foreign key อาจารย์ Primary key คณะ รูป แสดง foreign key ของตารางอาจารย์

18. การควบคุมให้เป็นไปตามกฎทั้ง 4 ข้อ • เหตุการณ์ ที่สามารถฝ่าฝืนกฎทั้ง 4 ข้อ มีดังนี้ • 1. Retrieval operation (Relational Algebra Operations) • 2. Updates operations • 2.1 Insert • 2.2 Delete • 2.3 Modify

19. Insert operation • insert operation เป็นการจัดการในส่วนของการเพิ่มข้อมูลในตารางซึ่งอาจฝ่าฝืนกฎ 4 ข้อ ดังนี้ • Domain constraintถ้าค่าของ attributes ไม่ตรงกับ domain • Key constraintถ้าค่าของคีย์ในแถวใหม่ซ้ำกับค่าของข้อมูลอื่น • Entity integrity constraintถ้าค่าของคีย์ในแถวใหม่เป็นค่าว่าง • Referential integrity constraintถ้าค่าของ foreign key ในแถวใหม่ไม่มีใน referenced relation

20. Insert operation อาจารย์ • 1. Insert <‘สมภพ’, ‘ทองคำ’, null, ‘12-5-1965’,’102 พระโขนง กรุงเทพฯ’,’ชาย’,’อาจารย์2’ ,’12,000’,’3’ ใน relation อาจารย์ • เป็นการ insert ฝ่าฝืน Entity integrity constraint(ค่า primary key เป็นค่าว่าง) • 2. Insert <‘วิภา’, ‘ฉัตรทอง’, ‘32140’, ‘10-6-1960’,’12 พญาไท กรุงเทพฯ’,’หญิง’,’อาจารย์2’ ,’12,000’,’1’ ใน relation อาจารย์ • เป็นการ insert ฝ่าฝืน Key constraint (ค่า key ซ้ำกับ tuple อื่น)

21. Insert operation อาจารย์ • 3. Insert <‘เหินฟ้า’, ‘สกุลสวัสดิ์’, ‘35640’, ‘25-6-1970’,’25 ดาวคนอง กรุงเทพฯ’,’ชาย’,’อาจารย์1’ ,’9,000’,’10’ ใน relation อาจารย์ • เป็นการ insert ฝ่าฝืน Referential integrity constraint • (ไม่มีค่าของรหัสคณะ = 10 ใน relation คณะ)

22. Delete operation • delete operation เป็นการจัดการในส่วนของการลบแถวของตาราง R ซึ่งการลบอาจฝ่าฝืนเพียง Referential integrityconstraints ถ้ามีการลบแถวที่ถูกอ้างอิงถึงโดย foreign key ที่อยู่ใน relation เช่น • 1. Delete แถวที่อยู่ในตารางสังกัด <‘1’,’411200’> • การ delete ไม่ฝ่าฝืน Referential integrity constraint • 2. Delete แถวที่อยู่ในตารางคณะ โดย รหัสคณะ = 1 • การ delete ฝ่าฝืน Referential integrity constraint • (กระทบกับ relation อาจารย์และ relation สังกัด ที่ใช้รหัสคณะ=1) สังกัด คณะ

23. Delete operation รูปแสดง Referential integrity constraints ของผังงาน relational database schema มหาวิทยาลัย

24. Delete operation วิธีในการจัดการกับปัญหาที่เกิดขึ้นของ delete operation 1. ทำการ reject การ delete คือไม่อนุญาตให้มีการลบข้อมูล ถ้าข้อมูลนั้นมีการอ้างอิงอยู่ใน relation อื่น 2. ทำการ cascade หรือ propagateคือ ถ้าอนุญาตให้ทำการลบข้อมูล ต้องตามไปลบข้อมูลที่มีคีย์นอกอ้างอิงถึงข้อมูลนั้นใน relation อื่นด้วย 3. ทำการ modify ค่าของข้อมูลที่มีการอ้างอิงถึง โดยปรับเป็นค่าว่าง(nullify) แต่อาจฝ่าฝืน entity integrity constraint แทน และเมื่อ user ทำการใส่ค่าข้อมูลเข้ามายัง relation ก็สามารถตามไปใส่ข้อมูลใน relation อื่น ที่อ้างอิงถึงด้วย

25. Update operation update operation เป็นการจัดการในส่วนของการเปลี่ยนแปลงค่าของ attribute ที่อยู่ใน tuple ของตาราง พิจารณาตัวอย่าง 1. Update แถวที่อยู่ในตารางสังกัด จาก <‘1’,’411200’> เป็น <‘2’,’411200’> การ update ไม่ฝ่าฝืน Referential integrity constraint 2. Update แถวที่อยู่ในตารางคณะ จาก รหัสคณะ = 1 เป็น รหัสคณะ = 12 การ update ฝ่าฝืน Referential integrity constraint การ update attributes ที่เกิดปัญหาส่วนใหญ่เป็นการ update attribute ที่เป็นprimaryหรือ foreign key DBMS ต้องทำการตรวจสอบค่าที่ใส่เข้ามาใหม่ว่าตรงตาม data typeหรือdomain ที่กำหนดไว้หรือไม่

26. Update operation วิธีในการจัดการกับปัญหาที่เกิดขึ้นของ update operation 1. ทำการ reject การ updateคือไม่อนุญาตให้มีการ update ข้อมูล ถ้าข้อมูลนั้นมีการอ้างอิงอยู่ใน relation อื่น 2. ทำการ cascade หรือ propagate คือถ้าอนุญาตให้ทำการ update ข้อมูล ต้องตามไป update ข้อมูลที่มีคีย์นอกอ้างอิงถึงข้อมูลนั้นใน relation อื่นด้วย 3. ทำการ modify ค่าของข้อมูลที่มีการอ้างอิงถึง โดยปรับเป็นค่าว่าง(nullify) แต่อาจฝ่าฝืน entity integrity constraint แทน และเมื่อ user ทำการใส่ค่าข้อมูลเข้ามายัง relation ก็สามารถตามไปใส่ข้อมูลใน relation อื่นที่อ้างอิงถึงได้

27. คุณสมบัติของ Relation 1. ข้อมูลในแต่ละ attribute มีค่าเพียงค่าเดียวตามที่กำหนดไว้ในโดเมน(atomic) 2. ข้อมูลใน attribute เดียวกันจะมีค่าของข้อมูลชนิดเดียวกัน(Domain) เดียวกัน 3. ในแต่ละ attribute จะมีชื่อที่แตกต่างจาก attribute อื่น 4. ในแต่ละ relation จะมีชื่อที่แตกต่างจาก relation อื่น 5. ลำดับของ attribute ซ้ายไปขวา หรือขวาไปซ้าย ไม่มีความสำคัญ ถึงแม้มีการสลับ attribute ค่าของข้อมูลก็เหมือนเดิม 6. ลำดับของ rowsบนลงล่าง หรือล่างขึ้นบน ไม่มีความสำคัญ ถึงแม้มีการสลับ rows ค่าของข้อมูลก็เหมือนเดิม 7. ค่าของข้อมูลในแต่ละ row จะแตกต่างกัน (Each row is Unique)

28. ตัวอย่างของฐานข้อมูลเชิงสัมพันธ์ตัวอย่างของฐานข้อมูลเชิงสัมพันธ์ • ฐานข้อมูลของ พนักงาน และ แผนกงาน ซึ่งประกอบด้วย 2 รีเลชั่น ดังนี้ • 1. รีเลชั่น Employee เป็น รีเลชั่นที่จัดเก็บรายละเอียดของพนักงาน ประกอบด้วย Attribute ต่อไปนี้ คือ • 1.1 EMPNUM รหัสพนักงาน 1.5 POSITION ชื่อตำแหน่ง • 1.2 EMPNAME ชื่อพนักงาน 1.6 DEPNOรหัสแผนกงาน • 1.3 HIREDATE วันที่เริ่มทำงาน 1.7 MGRNO รหัสผู้บังคับบัญชา • 1.4 SALARY อัตราเงินเดือน • 2. รีเลชั่น Department เป็น รีเลชั่นที่จัดเก็บรายละเอียดของแผนกงาน ประกอบด้วย Attribute ต่อไปนี้ คือ • 2.1 DEPNO รหัสแผนกงาน • 2.2 DEPNAME ชื่อแผนกงาน • 2.3 LOCATION ที่ตั้งของแผนกงานนั้นๆ

29. ตัวอย่างของฐานข้อมูลเชิงสัมพันธ์ตัวอย่างของฐานข้อมูลเชิงสัมพันธ์ ดังแสดงข้อมูลตัวอย่างของแต่ละ Relation ดังนี้ รีเลชั่น Employee EMPMUM EMPNAME HIREDATE SALARY POSITION DEPNO MGRNO 1001 SIRIWAN 06/13/93 9000.00 CLERK 10 1002 3001 ARLEE 08/15/93 17000.00 SALESMAN 30 3004 4001 WICHAI 12/26/93 33000.00 MANAGER 40 2002 1002 JINTANA 10/31/93 30000.00 CONTROLLER 10 1003 3002 MITREE 12/05/93 13000.00 SALESMAN 30 3004 3003 BENJAWAN 06/11/94 29000.00 MANAGER 30 2002 รีเลชั่น DEPARTMENT DEPNO DEPNAME LOCATION 10 ACCOUNTING SILOM 20 ADMINISTRATION SUKHUMVIT 30 MARKETING RATCHADA 40 RESEARCH SUKHUMVIT

30. งานบทที่ 4 • จงอธิบายคำต่อไปนี้ • primary key • foreign key • candidate key • alternate key • secondary key • composite key • Domain Constraints • Key Constraints • Integrity constraints • Entity integrity constraints • Referential integrity constraints