ฐานข้อมูลเชิงสัมพันธ์ และตัวแบบข้อมูลเชิงสัมพันธ์

1. บทที่ 3 ฐานข้อมูลเชิงสัมพันธ์ และตัวแบบข้อมูลเชิงสัมพันธ์ อ. ดร. ชุรี เตชะวุฒิ CS (204)321 ระบบฐานข้อมูล 1 (Database System I)

2. Outlines 1) ฐานข้อมูลเชิงสัมพันธ์ (Relational Database) 2) ตัวแบบข้อมูลเชิงสัมพันธ์(Relational Data Model) 3) Relational Database Constraints and Relational Database Schemas 4) Update Operations on Relations 5) Relational Algebra

3. Definition (by C.J. Date) “ A relational database is a database that is perceived by its users as a collection of tables. It is not just a database in which the data is physically stored as tables. ” Relational Database Note: Relation is a mathematical term for a table.

4. Concepts • ตัวแบบข้อมูลหรือตัวแบบฐานข้อมูลเชิงสัมพันธ์ เป็นตัวแบบเชิงตรรกะที่แสดงโครงสร้างข้อมูลและความสัมพันธ์ของข้อมูลแบบนามธรรม โดยมองปัญหาในระดับแนวคิดที่ไม่เจาะลึกลงไปถึงรายละเอียดในระดับกายภาพ • ประกอบด้วย ส่วนของโครงสร้าง (Structural part) ส่วนของการจัดดำเนินการ (Manipulative part) และส่วนของกฎความคงสภาพ (Set of integrity constraints) • ตัวแบบข้อมูลเชิงสัมพันธ์มีหลายประเภทแต่ที่นิยมใช้ปัจจุบันคือตัวแบบฐานข้อมูลเชิงสัมพันธ์ที่มีโครงสร้างข้อมูลแบบตาราง (Table)ที่เข้าใจง่าย Relational Data Model

5. Concepts • The relational model of data is based on the concept of a Relation. • A relation is a mathematical concept based on the ideas of sets. • The strength of the relational approach to data management comes from the formal foundation provided by the theory of relations. • We review the essentials of the relational approach in this chapter. Relational Data Model

6. What is “Relation” ? RELATION: A table of values Relational Data Model • A relation may be thought of as a set of rows. • A relation may alternately be thought of as a set of columns. • Each row represents a fact that corresponds to a real-world entity or relationship. • Each row has a value of an item or set of items that uniquely identifies that row in the table. • Sometimes row-ids or sequential numbers are assigned to identify the rows in the table. • Each column typically is called by its column name or column header or attribute name.

7. Example of Relation STUDENT Relational Data Model

8. Relation properties • A relation is a named, 2-dimentional table of data. • Not all tables qualify as relation. Relational Data Model • Every relation has a unique name. • Each column has a unique name. • No 2 rows are identical. • Ordering of rows is not significant. • Ordering of columns is not significant. • All attribute values are atomic. • (There always exists precisely one value, never a set of values) • Column values are of the same kind.

9. Relation schema R (A1, A2, .....An) Relational Data Model R is the name of relation. Degree of relation is the numbers of attributes of its relation schema. Relation schema R is defined over attributes A1, A2, .....An • For Example: • CUSTOMER (Cust-id, Cust-name, Address, Phone#) CUSTOMER is a relation defined over the four attributes Cust-id, Cust-name, Address, Phone#, each of which has a domain or a set of valid values. Degree of relation is 4.

10. Domain “ A domain D is a set of atomic values, all of the same type. ” Relational Data Model All values in a column come from the same domain. • For Example: • Cust-id : The set of valid 6-digit numbers. • Cust-name : The set of customer names. • Address : The set of home address where customers live. • Phone# : The set of 10-digit phone numbers valid in Thailand.

11. Domain (Continued) • A domain has a logical definition. e.g., “USA_phone_numbers” are the set of 10 digit phone numbers valid in the U.S. • A domain may have a data-type or a format defined for it. e.g., The USA_phone_numbers may have a format: (ddd)-ddd-dddd where each d is a decimal digit. e.g., Dates have various formats such as monthname, date, year or yyyy-mm-dd, or dd mm,yyyy etc. • An attribute designates the role played by the domain. e.g., The domain Date may be used to define attributes “Invoice-date” and “Payment-date”. Relational Data Model

12. Domain (Continued) • Each attribute in the model should be assigned domain information which includes: - Data type - Length - Data format (e.g., Date format is dd/mm/yy) - Range - Constraints (special restrictions on allowable values) - Null support - Default value (if any) Relational Data Model • Tillmann-95: Domain includes • - Data type (e.g., tinyint represents numbers from -128 to 127.) • - Range (e.g., GPA rages from 0.00 to 4.00) • - Acceptable value (e.g., Gender has two values, male and female.)

13. Relation instance • A relation instance, r , of the relation schema R (A1, A2, .....An), Relational Data Model denoted by r(R) is a mathematical relation of degree n on the domains dom(A1), dom(A2),…, dom(An), which is the subset of the Cartesian Product of the domains that define R. R is also called the intension of a relation. r is also called the extension of a relation.

14. Cartesian product Relational Data Model

15. Tuple • A tuple is an ordered set of values. Relational Data Model • Each value is derived from an appropriate domain. • Each row in the CUSTOMER table may be referred to as a tuple in the table and would consist of four values. • t = <632895, "John Smith", "101 Main St. Atlanta, GA 30332", "(404) 894-2000">is a tuple belonging to the CUSTOMER relation. • A relation may be regarded as a set of tuples (rows). • Columns in a table are also called attributes of the relation.

16. Tuple (Continued) We refer to component values of a tuplet by t[Ai] = vi (the value of attribute Ai for tuplet). Similarly, t[Au, Av, ..., Aw] refers to the subtuple of t containing the values of attributes Au, Av, ..., Aw, respectively. Relational Data Model eg., t[name] = < "John Smith"> t[name, address] = < "John Smith” , "101 Main St. Atlanta, GA 30332">

17. การระบุว่า attribute มาจาก relation ใด จะใช้สัญลักษณ์ dot (.) ในรูปแบบ Relation name . Attribute name Relational Data Model • เช่น CUSTOMER.name • CUSTOMER.address

18. Let S1 = {0,1} • Let S2 = {a,b,c} Relational Data Model • Let • Then for example: is one possible ‘state’ or ‘population’ or ‘extension’ r of the relation R, defined over domains S1 and S2. It has three tuples.

19. Terminology Relational Data Model

20. Example Relational Data Model

21. Key fields • Keys are special fields that serve two main purposes: Relational Data Model - Primary keys are unique identifiers of the relation.Examples can use SSN as a primary key. This is how we can guarantee that all rows are unique. - Foreign key is a column or columns whose values are the same as a primary key of another table. • Keys can be simple (a single field) or composite (more than one field) • Keys usually are used as indexes to speed up the response to user queries.

22. ประเภทของคีย์ • Primary key หรือ คีย์หลักคีย์คู่แข่งที่ถูกเลือกมาเพื่อใช้เจาะจงทัพเพิลของรีเลชัน และคีย์คู่แข่งที่เหลือจะเรียกว่าคีย์สำรอง (Alternate key) • เช่น ถ้าเลือก Student_codeเป็น คีย์หลัก แล้วemail จะเป็น คีย์สำรอง Relational Data Model

23. ประเภทของคีย์ • Primary key หรือ คีย์หลัก • โดย คีย์หลัก จะแสดงในเค้าร่างรีเลชันด้วย การขีดเส้นใต้ เช่น • STUDENT(Student_code, Student_name, email) • คุณสมบัติของคีย์หลักคือ • Uniquenessมีความเป็นหนึ่งเดียว คือ ค่าของคีย์หลักต้องไม่ซ้ำกันเลย • Minimalityคือประกอบด้วยจำนวนแอททริบิวต์น้อยที่สุดที่สามารถเจาะจงทัพเพิลหนึ่งในรีเลชันได้ Relational Data Model

24. ประเภทของคีย์ • Composite key หรือ คีย์ประกอบคีย์ที่ประกอบด้วยแอททริบิวต์มากกว่า 1 ตัว เมื่อนำแอททริบิวต์ทั้งหมดมาผสมกัน ทำให้ได้ค่าที่ไม่ซ้ำกันเลย เช่น • GRADE(Student_code, course, grade) Relational Data Model GRADE

25. ประเภทของคีย์ • Superkeyหรือ ซูเปอร์คีย์กลุ่มของแอททริบิวต์ของรีเลชันที่สามารถเจาะจง (identify)ความเป็นหนึ่งเดียวของทัพเพิลที่แตกต่างจากทัพเพิลอื่นได้ • ทุกรีเลชั่นจะมีซูเปอร์คีย์อย่างน้อย 1 ตัวเสมอ • แอททริบิวต์ทุกตัวของรีเลชันจะเป็นซูเปอร์คีย์โดยปริยาย Relational Data Model

26. ประเภทของคีย์ • Superkeyหรือ ซูเปอร์คีย์ จากตาราง STUDENTซูเปอร์คีย์อาจเป็นได้ ดังนี้ • {Student_code, Student name, Major, GPA} • {Student_code, Student name} • {Student_code, Student name, Major} Relational Data Model STUDENT

27. ประเภทของคีย์ • Key หรือ คีย์คือซูเปอร์คีย์ที่เล็กที่สุด (Minimal superkey)ของรีเลชัน ซึ่งไม่มีเซ็ตย่อยที่ตัวมันเองเป็นซูเปอร์คีย์ นั่นคือ คีย์เป็นซูเปอร์คีย์ที่ไม่สามารถตัดแอททริบิวต์ใดได้อีกโดยยังคงคุณสมบัติของทัพเพิลไว้เหมือนเดิม • บางรีเลชันอาจมีคีย์ได้มากกว่า 1 ตัว เช่นตาราง STUDENTมีคีย์ 2 ตัว คือ student_codeและ email • เราสามารถใช้ตัวใดตัวหนึ่งเป็นคีย์ที่เจาะจงทัพเพิลได้ ซึ่งคีย์แต่ละตัวนี้เรียกว่า คีย์คู่แข่ง (Candidate key) Relational Data Model

28. What are relational constraints? “ Constraints are conditions that must hold on all valid relation instances. ” (Elmasri&Navathe, 2000) Relational Constraints “ Restrictions on data that can be specified on a relational database schema. ” (Date, 2000) • There are three main types of constraints: • Key constraints • Domainconstraints • Entity integrity constraints • Referential integrity constraints

29. Domain constraints • The value of each attribute A must be an atomic value from the domain dom(A). • The data types associated with domains typically includes: • - Numeric: integer (short integer,long)orreal number (float, double) • - Character: fixed-length string or variable-length string • - Date and time: e.g., dd/mm/yyhh:mm • - Logical: e.g., true or false, yes or no • - Timestamp • - Currency Relational Constraints

30. Entity integrity constraints • Relational Database Schema: A set S of relation schemas that belong to the same database. S is the name of the database. S = {R1, R2, ..., Rn} • Entity Integrity: The primary key attributesPK of each relation schema R in Scannot have null values in any tuple of r(R). This is because primary key values are used to identify the individual tuples. • t[PK] null for any tuple t in r(R) • Note: Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key. Relational Constraints

31. Entity integrity constraints(Continued) • A Null is created by making no entry at all, so a null denotes the absence of a value. Relational Constraints • A null can have any of the following meanings: • The value does not exist. • The value exists, but it is not known. • The value is unknown, or it is not applicable.

32. Referential integrity constraints • A constraint involving two relations (the previous constraints involve a single relation). • Used to specify a relationship among tuples in two relations: the referencing relation and the referenced relation. • Tuples in the referencing relationR1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relationR2. A tuple t1in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK]. • A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1. FK to R2. Relational Constraints

33. Referential integrity constraints displayed on the COMPANY relational database schema Relational Constraints

34. Summary of relational constraints Domain constraints Relational Constraints Entity integrity constraints (rows) Referential integrity constraints (between tables)

35. กฎความคงสภาพจะถูกใช้ในการนิยามฐานข้อมูลด้วยภาษานิยามข้อมูล (Data definition language) ซึ่งจะทำให้ระบบจัดการฐานข้อมูลบังคับใช้กลไกโดยอัตโนมัติ เช่น ภาษานิยามข้อมูล ของ MySQL • create table STUDENT{ • student_code char(9) NOT NULL, • student _name varchar(30) NOT NULL, • major char(4) Default ‘CS’, • gpa float CHECK gpa>=0.00 AND gpa<=4.00, • Advisor_code char(3) REFERENCES ADVISOR (adv_code), • PRIMARY KEY (student_code)} Relational Constraints Domain integrity Referential integrity Entity integrity

36. กฎเกณฑ์อื่นๆได้แก่ • กฎเกณฑ์ทั่วไปเพื่อตรวจสอบความถูกต้องของข้อมูล (Static constraint or Semantic integrity constraint) เช่น เงินเดือนของพนักงาน ต้องน้อยกว่าเงินเดือนของพนักงานที่เป็น Supervisor • กฎเกณฑ์ที่เกี่ยวข้องกับการปรับปรุงค่าของข้อมูล (Dynamic constraint or Transaction constraints) ซึ่งอาจใช้ Trigger เป็นตัวดำเนินการ เช่น การปรับค่าของเงินเดือนพนักงานต้องปรับเพิ่มขึ้นเท่านั้น ไม่สามารถลดลงจากปัจจุบันได้ Relational Constraints

37. UPDATE operations consist of: INSERT a tuple DELETE a tuple MODIFY a tuple Update Operations on Relations • Integrity constraints should not be violated by the update operations. • Several update operations may have to be grouped together. • Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints.

38. In case of integrity violation, several actions can be taken: • Cancel the operation that causes the violation (REJECT option) • Perform the operation but inform the user of the violation • Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) • Execute a user-specified error-correction routine Update Operations on Relations e

39. ในระหว่างการนิยามข้อมูล ผู้ใช้หรือผู้บริหารฐานข้อมูลจะเป็นผู้กำหนดว่า ต้องการให้ระบบฐานข้อมูลแก้ไขสถานการณ์ในลักษณะใด จึงควรทำความเข้าใจเพื่อตอบคำถาม 3 ข้อต่อไปนี้ Update Operations on Relations • 1. ค่าของคีย์นอกเป็นค่าว่างได้หรือไม่ • 2. อนุญาตให้มีการลบทัพเพิลที่ถูกอ้างอิงโดยคีย์นอกหรือไม่ และถ้ายอมให้ลบแล้วจะทำอย่างไรให้ข้อมูลยังคงมีความคงสภาพดังที่นิยามไว้ • 3. อนุญาตให้มีการแก้ไขค่าคีย์หลักของทัพเพิลที่ถูกอ้างอิงโดยคีย์นอกหรือไม่ และถ้ายอมให้แก้ไขแล้วจะทำอย่างไรให้คีย์นอกยังคงอ้างอิงถึงค่าใหม่ของคีย์หลักได้ e

40. Insert operation กรณีที่ 1: การเพิ่มทัพเพิลโดยไม่ระบุค่าของแอททริบิวต์ที่เป็นคีย์หลัก เช่น Update Operations on Relations • Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, null, ‘1960-04-05’, ‘6357 Windy Lane, Katy, TX’, F, 28000, null, 4> into EMPLOYEE. This insertion violates the entity integrity constraint (null for the primary key SSN), so it is rejected.

41. Insert operation กรณีที่ 2: การเพิ่มทัพเพิลโดยกำหนดค่าของแอททริบิวต์ที่เป็นคีย์หลักซ้ำกับค่าที่มีอยู่แล้ว เช่น Update Operations on Relations • Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, 123456789, ‘1960-04-05’, ‘6357 Windy Lane, Katy, TX’, F, 28000, null, 4> into EMPLOYEE. This insertion violates the entity integrity constraint (value repetition of the primary key SSN), so it is rejected.

42. Insert operation กรณีที่ 3: การเพิ่มทัพเพิลที่ค่าคีย์นอกไม่สามารถอ้างอิงถึงคีย์หลักได้ เช่น Update Operations on Relations • Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, 677678989, ‘1960-04-05’, ‘6357 Windy Lane, Katy, TX’, F, 28000, null, 8> into EMPLOYEE. This insertion violates the referential integrity constraint (DNUMBER in DEPARTMENT does not contain 8), so it is rejected.

43. Insert operation กรณีที่ 4: การเพิ่มทัพเพิลที่สอดคล้องกับกฎความคงสภาพ เช่น Update Operations on Relations • Insert <‘Cecilia’, ‘F’, ‘Kolonsky’, ‘677678989’, ‘1960-04-05’, ‘6357 Windy Lane, Katy, TX’, F, 28000, null, 4> into EMPLOYEE. This insertion satisfies all constraints, so it is acceptable.

44. Delete operation กรณีที่ 1: การลบทัพเพิลที่สอดคล้องกับกฎความคงสภาพ เช่น Update Operations on Relations • Delete the WORKS_ON tuple with ESSN = ‘999887777’ and PNO = 10. This deletion is acceptable.

45. Delete operation กรณีที่ 2: ทัพเพิลที่ต้องการลบยังถูกอ้างอิงจากคีย์นอก เช่น Update Operations on Relations • Delete the EMPLOYEE tuple with SSN = ‘999887777’ This deletion is not acceptable, because tuples in WORKS_ON refer to this tuple. Hence, if the tuple is deleted, referential integrity violations will result.

46. Delete operation เมื่อการลบทัพเพิลทำให้เกิดการละเมิดกฎความคงสภาพของการอ้างอิง ระบบจัดการฐานข้อมูลจะเลือกปฏิบัติจากทางเลือก 4 ทาง ดังนี้ Update Operations on Relations 1. ปฏิเสธ (Reject)คำสั่งถ้ามีคีย์นอกอ้างอิงถึงทัพเพิลนั้น หรือ ยอมให้ลบทัพเพิลถ้าไม่มีการอ้างอิงจากคีย์นอกใด ซึ่งเป็นการลบแบบมีเงื่อนไข เรียกว่า การลบแบบเข้มงวด (Restrict delete) 2. ลบทุกๆทัพเพิลที่คีย์นอกอ้างอิงถึง โดยจะลบกันเป็นทอดๆ เรียกว่า การลบแบบต่อเรียง (Cascade delete) ตัวอย่าง การลบในกรณีที่ 2 เมื่อลบในตาราง EMPLOYEE แล้วจะลบแบบต่อเรียงในตาราง WORKS_ON ที่มีการอ้างถึงSSN = ‘999887777’

47. Delete operation • 3. เปลี่ยนค่าแอททริบิวต์ที่ทำให้เกิดการละเมิดกฎ เช่น การทำ Nullifyหรือ Set to nullคือการเปลี่ยนค่าของแอททริบิวต์ข้อมูลนั้นให้เป็น nullแล้วจึงลบค่าของแอททริบิวต์นั้นตามต้องการ • ตัวอย่าง การลบค่าบางแผนกออกจากตารางDEPARTMENT • Delete from DEPARTMENT where DNUMBER=‘5’ • การลบจะถูกปฏิเสธเพราะมีทัพเพิลของ EMPLOYEE อ้างอิงทัพเพิลที่ DNO=‘5’ • ในกรณีนี้ จะทำให้ค่า DNO ในตาราง EMPLOYEEเป็นnullก่อน แล้วจึงทำการลบ ค่าบางแผนกออกจากตาราง DEPARTMENT ได้ Update Operations on Relations

48. Delete operation 4. เปลี่ยนค่าแอททริบิวต์ที่ทำให้เกิดการละเมิดกฎ เป็นค่าที่กำหนดให้ค่าหนึ่ง (Default value) มักใช้ในกรณีที่ไม่อนุญาตให้ค่าของคีย์นอกเป็นค่าว่างหรือกรณีที่คีย์นอกเป็นส่วนหนึ่งของคีย์หลัก หมายเหตุ ทุกครั้งที่มีการบังคับใช้กฎความคงสภาพของการอ้างอิง ผู้ใช้ต้องระบุในขณะที่นิยามฐานข้อมูลว่าต้องการใช้ทางเลือกใดเมื่อมีการลบข้อมูล Update Operations on Relations

49. Modify/Update operation กรณีที่ 1:การแก้ไขแอทริบิวต์ที่ไม่ได้เป็นคีย์หลักหรือคีย์นอก จึงไม่ละเมิดกฎความคงสภาพของข้อมูล เช่น Update Operations on Relations • Update the SALARY of the EMPLOYEE tuple with SSN = ‘999887777’ to 28000. This update is acceptable.

50. Modify/Update operation กรณีที่ 2: การแก้ไขแอทริบิวต์ที่เป็นคีย์นอกและมีการอ้างอิงถึงคีย์หลัก เช่น Update Operations on Relations • Update the DNO of the EMPLOYEE tuple with SSN = ‘999887777’ to 8. This update is unacceptable. No primary key with value 8 in DEPARTMENT. คำสั่งนี้ถูกปฏิเสธเพราะค่าใหม่นี้ไม่สามารถอ้างอิงถึงคีย์หลักที่มีอยู่แล้วได้