ตัวแบบความสัมพันธ์ของ เอนทิ ตี

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

2. Outlines 1) Database design process 2) ER Model concepts 3) ER diagrams notation 4) Relationships and relationship types 5) Degree of relationship types 6) Cardinality of relationships 7) Cardinality constraints and cardinality ratio 8) Weak entity types 9) Transforming ER diagrams into relations

3. Miniworld Database Design Process Requirements collection & analysis Functional requirements DBMS-independent Database requirements Functional analysis Conceptual design High-level transaction specification Conceptual schema Logical design (Data model mapping) Application program design Logical schema DBMS-specific Physical design Transaction implementation Internal schema Application programs

4. ER model describes data as entities, relationships, and attributes. • Three important concepts are: ER Model Concepts • Entities and attributes • Entity types and key attributes • Relationships and relationships types

5. Entities are specific objects or things in the mini-world that are represented in the database. An entity may be an object with Entities and attributes • a physical existence (for example, PERSON, CAR, HOUSE, or EMPLOYEE) or with • a conceptual existence (for example, DEPARTMENT, PROJECT, JOB, or UNIVERSITY COURSE.) Entities • Attributes Attributes are properties used to describe an entity, for example an EMPLOYEE entity may have a Name, SSN, Address, Sex, and BirthDate.

6. Value A specific entity will have a value for each of its attributes, for example a specific employee entity may have Name=‘John Smith’, SSN=‘123456789’, Address=‘731 Fondren, Houston, TX’, Sex=‘M’, BirthDate=’09-JAN-55’. Entities and attributes Each attribute has a value set (or data type) associated with it – e.g., integer, string, subrange, enumerated type, .....

7. Types of attributes • Simple VS. Composite Entities and attributes Simple (or atomic) attributes: Attributes that are not divisible such as SSN and sex. Composite attributes: Attributes that can be divided into smaller subparts. For example, Address(Apt#, House#, Street, City, State, Zipcode, Country) or Name(FirstName, MiddleName, LastName).

8. Types of attributes (Continued) • Single-valued VS. Multi-valued Entities and attributes Single-valued attributes: An attribute that has a single value, such as Age of person. Multivalued attributes: Attributes that have numbers of values. For example, Color of a car or PreviousDegrees of a STUDENT. Denoted as {Color}and {PreviousDegrees}. • Stored VS. Derived Stored attributes: e.g., BirthDate Derived attributes: It is derivable from stored attribute.; such as Age • Null-valued attribute: An attribute that it’s attribute value exists but is missing, or is unknown whether the attribute value exists.

9. EMPLOYEE Ssn, Name, Age, Salary • e1 (152056009, John Smith, 55, 80k) • e2 (123098987, Fred Brown, 40, 30k) • e3 (143024992, Judy Clark, 25, 20k) … A set of entities that have the same attributes. Each entity type in the database is described by a name and a list of attributes. Entity types and key attributes • Example of entity type Entity type name: Attributes: Entity types Entity set: (Extension)

10. ER diagrammatic notation Entity types and key attributes Use a rectangular box enclosing the entity type name. Attribute names are enclosed in ovals and are attached to their entity type by straight lines. Entity types (Continued) Composite attributes are attached to their component attributes by straight lines. Multivalued attributes are displayed in double ovals.

11. Example of ER diagram Entity types and key attributes EMPLOYEE Entity types (Continued) Salary Ssn Name Age

12. Attributes of whose values are distinct for each individual entity and its values can be used to identify each entity uniquely. Entity types and key attributes A key attributes may be composite. • Example key attributes Entity type Key attribute Key attributes Social Security Number PERSON CAR Registration (Number + State) • ER diagrammatic notation Each key attribute has its name underlined inside the oval.

13. Example of ER diagram Entity types and key attributes EMPLOYEE Key attributes (Continued) Salary Ssn Name Age

14. Requirements for CAR are as followings: CAR is an entity type. Registration is a key attribute consisting of RegistrationNumber and State. Other important attributes for a car are VehicleID, Make, Model, Year. A car can have more than one Color. Exercise • Possible attribute values for CAR entity type: … … … … … … … …

15. Answer the following questions: What is the name of your entity type? What are the attributes for the entity type? What are the types of each attributes? Write ER diagram from the requirements above. Exercise

16. car1 ((ABC123, TEXAS), TK629, Ford Mustang, Convertible, 1989, {red, black}) • car2 ((ABC123, NEW YORK), WP9872, Nissan Sentra, 2-door, 1992, {blue}) CAR • car3 ((VSY720, TEXAS), TD729, Chrysler LeBaron, 4-door, 1993, {white, blue}) … Entity type name: CAR Attributes: Registration(RegistrationNumber, State), VehicleID, Make, Model, Year, {Color} Exercise Entity set: (Extension)

17. ER diagram CAR Exercise Registration Number Color State Year Model VehicleID Make

18. ENTITY TYPE ER diagram notation WEAK ENTITY TYPE RELATIONSHIP TYPE IDENTIFYING RELATIONSHIP TYPE ATTRIBUTE KEY ATTRIBUTE MULTIVALUED ATTRIBUTE COMPOSITE ATTRIBUTE DERIVED ATTRIBUTE

19. The company is organized into DEPARTMENTs. Each department has a name, number, and a employee who manages the department. We keep track of the start date of the department manager. A department may have several locations. Example of COMPANY Database • Each department controls a number of PROJECTs. Each project has a name, number, and is located at a single location. • We store each EMPLOYEE’s social security number, address, salary, sex and birth date. Each employee works for one department but may work on several projects. We keep track of the number of hours per week that an employee currently works on each project. We also keep track of the direct supervisor of each employee. Requirements for the COMPANY database • Each employee may have a number of DEPENDENTs. For each dependent, we keep their name, sex, birth date, and relationship to the employee. • ER diagram of COMPANY database

20. กฎธุรกิจ (Business rule) การพัฒนาตัวแบบข้อมูลให้มีความถูกต้องนั้น นอกจากผู้ออกแบบฐานข้อมูลจะต้องเข้าใจข้อมูลขององค์กรอย่างดีแล้ว ยังต้องเข้าใจกฎเกณฑ์การทำงานหรือระเบียบปฏิบัติที่กำหนดขึ้นขององค์กรนั้นๆ กฎเกณฑ์เหล่านี้เรียกว่า “กฎธุรกิจ” กฎธุรกิจไม่เป็นเอกภาพ (Universal) แต่ละองค์กรมีกฎเกณพ์หรือนโยบายแตกต่างกันและสามารถเปลี่ยนแปลงได้ ตัวแบบข้อมูลจะต้องมีความสัมพันธ์กันกับกฎธุรกิจ ถึงแม้ว่าตัวแบบข้อมูลจะไม่สามารถแสดงกฎธุรกิจได้ทั้งทั้งหมดก็ตาม

21. กฎธุรกิจ (Business rule) ตัวอย่างของกฎธุรกิจ เช่น นักศึกษาจะลงทะเบียนวิชาหนึ่งได้ก็ต่อเมื่อได้สอบผ่านวิชาที่ต้องผ่านก่อนของวิชานั้นๆแล้ว ลูกค้าของสหกรณ์จะเป็นเจ้าของบัญชีเงินฝากได้เพียงบัญชีเดียวและแต่ละบัญชีต้องมีเจ้าของเพียงคนเดียว ให้พนักงานทุกคนแต่งกายในชุดประจำชาติทุกวันศุกร์ ในระหว่างการพัฒนาตัวแบบอีอาร์ เราอาจนำกฎเหล่านี้ประยุกต์ใช้ได้ เนื่องจากกฎบางกฎบ่งบอกถึงความสัมพันธ์ระหว่างเอนทิตี หรือค่าของคาร์ดินัลลิตีการมีส่วนร่วมในความสัมพันธ์ หรือกฎความคงสภาพอื่น แต่กฎบางกฎอาจนำไปแสดงในตัวแบบข้อมูลไม่ได้ เช่น ตัวอย่างของกฎธุรกิจ ข้อสุดท้าย เป็นต้น

22. Relationships A relationship relates two or more distinct entities with a specific meaning. e.g. EMPLOYEE “John Smith” works on “the Product X” PROJECT. EMPLOYEE “Franklin Wong” manages “the Research” DEPARMENT. Relationships and relationship types • Relationship types A relationship type defines a set of associations among entities. e.g. WORKS_ON relationship type in which EMPLOYEEs and PROJECTs participate. MANAGEs relationship type in which EMPLOYEEs and DEPARMENTs participate.

23. Relationship instances Each relationship typeR consists of relationship instances ri. • Example of relationship type WORKS_FOR between two entity types Relationships and relationship types EMPLOYEE WORKS_FOR DEPARTMENT … … …

24. ER diagrammatic notation Relationships and relationship types Use a diamond-shaped box to display a relationship type enclosing the relationship name. It must be connected by straight lines to the rectangular boxes representing the participating entity types.

25. Example of ER diagram WORKS_FOR Relationships and relationship types EMPLOYEE PROJECT Name Ssn Number Name Location Age Salary

26. It is the number of participating entity types. • Three kinds of degree of a relationship type: Degree of relationship types • Unary relationship • Binary relationship • Ternary relationship • More than one relationship type can exist with the same participating entity types. e.g. MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and DEPARTMENT participate.

27. Degree of relationship types Ternary or n-ary Unary Binary One entity related to another of the same entity type. Entities of two different types related to each other. Entities of three different types related to each other.

28. one - to - one Each entity in the relationship will have exactly one related entity. Cardinality of relationships • one - to - many An entity on one side of the relationship can have many related entities, but an entity on the other side will have a maximum of one related entity. • many - to - many Entities on both sides of the relationship can have many related entities on the other side.

29. Cardinality constraints The number of instances of one entity that can or must be associated with each instance of another entity. Cardinality constraints and cardinality ratio • Cardinality ratio (or Relationship participant) คือ การบ่งบอกลักษณะของความสัมพันธ์ที่ละเอียดมากขึ้นกว่าการระบุคาร์ดินัลลิตีทำให้ทราบจำนวนเอนทิตีต่ำที่สุดและสูงที่สุดที่สัมพันธ์กับเอนทิตีในอีกเซตหนึ่ง • Minimum cardinality - If zero, then optional. - If one or more, then mandatory. • Maximum cardinality - The maximum number

30. ความสัมพันธ์แบบบังคับ (Mandatory relationship) เป็นความสัมพันธ์ที่เกิดขึ้นเมื่อเอนทิตีทุกตัวของเซตต้องมีส่วนร่วมในความสัมพันธ์ของเอนทิตีเซตเสมอ Cardinality constraints and cardinality ratio • ความสัมพันธ์แบบทางเลือก (Optional relationship) เป็นความสัมพันธ์ที่เกิดขึ้นเมื่อมีเอนทิตีบางตัวไม่มีส่วนร่วมในความสัมพันธ์ของเอนทิตีเซต

31. ร้านให้เช่าวิดีโอเก็บสต็อกของวิดีโอ (VDO)ที่ผลิตจากภาพยนตร์ (MOVIE) โดยภาพยนตร์หนึ่งเรื่องจะนำไปจัดทำเป็นม้วนวิดีโอสูงสุด 20 ม้วนหรือไม่จัดทำเลย และวิดีโอหนึ่งม้วนสร้างจากภาพยนตร์เพียงหนึ่งเรื่องเท่านั้น Example make MOVIE VDO 1 N make MOVIE VDO (0,20) (1,1) make MOVIE VDO 1 N

32. Cardinality ratio (Continued) Cardinality constraints and cardinality ratio 1 Mandatory one N Mandatory many 1 Optional one N Optional many

33. Cardinality ratio (Continued) Cardinality constraints and cardinality ratio Mandatory one Mandatory many Optional one Optional many

34. Cardinality ratio (Continued) สัญลักษณ์ที่ใช้กำหนดคาร์ดินัลลิตีในตัวแบบอีอาร์ มีดังนี้ Cardinality constraints and cardinality ratio 1. Chen’s model 2. Crow’s foot model 3. IDEF1X (Integrating Definition for Information modeling) 1 1 1 N M N ใช้จุดแทนความสัมพันธ์ด้าน many 4. Rein85 แทนความสัมพันธ์ด้าน many แทนความสัมพันธ์ด้าน one

35. is_married_to PERSON one-to-one Examples of unary relationships manages EMPLOYEE one-to-many

36. is_assigned EMPLOYEE PARKING PLACE Examples of binary relationships one-to-one contains PRODUCT LINE PRODUCT one-to-many registers_for STUDENT COURSE many-to-many

37. sell COMPANY PRODUCT M N Examples of ternary/n-aryrelationships P COUNTRY • ใช้เมื่อความสัมพันธ์แบบไบนารีไม่สามารถอธิบายความสัมพันธ์ระหว่าง 3 เอนทิตีได้ และสอดคล้องกับความเป็นจริงมากกว่า • บริษัทส่งสินค้าไปจำหน่ายยังประเทศต่างๆทั่วโลก และสินค้าชนิดเดียวกันอาจมีราคาต่างกันเมื่อส่งไปขายต่างประเทศกัน บริษัทจะส่งสินค้าไปบางประเทศเท่านั้นขึ้นอยู่กับประเภทสินค้า

38. Occurrence diagram PRODUCT COMPANY Examples of ternary/n-ary relationships COUNTRY … … … …

39. บางครั้งเอนทิตีหรือความสัมพันธ์ของเอนทิตีอาจมีจุดเริ่มต้นมาจากแอททริบิวต์ของเอนทิตี ตัวอย่างเช่น หน่วยอาคารสถานที่ต้องการเก็บข่อมูลเกี่ยวกับการใช้จักรยานยนต์ของนักศึกษา โดยจะบันทึกข้อมูลของนักศึกษาได้แก่ รหัสประจำตัว ชื่อ ที่อยู่และข้อมูลของรถจักรยานยนต์ที่นักศึกษาเป็นเจ้าของ ความสัมพันธ์ที่พัฒนามาจากแอททริบิวต์ std_name address std_code motorcycle STUDENT

40. จากการสำรวจพบว่า นักศึกษาส่วนใหญ่ไม่ได้เป็นเจ้าของรถจักรยานยนต์ ดังนั้นค่าของแอททริบิวต์motorcycleส่วนใหญ่จะเป็นค่าว่าง (Null) นอกจากนี้ยังต้องการเก็บข้อมูลเฉพาะของแอททริบิวต์นี้เพิ่มอีก เช่น ทะเบียนรถ ยี่ห้อ และรุ่นเป็นต้น ในกรณีเช่นนี้ motorcycleควรเป็นเอนทิตีมากกว่าแอททริบิวต์ ความสัมพันธ์ที่พัฒนามาจากแอททริบิวต์ std_name model license address std_code make own MOTORCYCLE STUDENT 1 1

41. เอนทิตี 2 เอนทิตีอาจมีความสัมพันธ์มากกว่า 1 แบบ work_in Multiple relationship N 1 PERSON DIVISION manage 1 1 เอนทิตีPERSONทำงานในแผนกหนึ่ง และในขณะเดียวกันมีบางคนที่เป็นผู้จัดการของแผนกด้วย

42. โดยทั่วไปเอนทิตีเซตแบ่งเป็น 2 ประเภท 1. Strong/Independent entity คือเอนทิตีเซตที่สามารถเกิดขึ้นได้โดยอิสระ และมีตัวชี้เฉพาะเพื่อบ่งชี้เอนทิตีเสมอ Weak entity types 2. Weak/Dependent entity คือเอนทิตีเซตแบบอ่อนที่ไม่สามารถเกิดขึ้นได้ด้วยตัวเอง และต้องพึ่งพาเอนทิตีเซต อื่นเสมอ ไม่มีตัวชี้เฉพาะของตัวเอง

43. An entity that does not have a key attribute. • A weak entity must participate in an identifying relationship type with an owner or identifying entity type. Weak entity types • Entities are identified by the combination of: • A partial key of the weak entity type • The particular entity they are related to in the identifying entity type • Example: • Suppose that a DEPENDENT entity is identified by the dependent’s first name and birhtdate, and the specific EMPLOYEE that the dependent is related to. • DEPENDENT is a weak entity type with EMPLOYEE as its identifying entity type via the identifying relationship type DEPENDENT_OF.

44. DEPENDENTS _OF EMPLOYEE DEPENDENT 1 N Weak entity types EMPLOYEE(fname, lname, ssn, bdate, address, sex, salary, superssn, dno) DEPENDENT(essn, dependent_name, sex, bdate, relationship) สัญลักษณ์แทน Weak entity type Partial key/identifier สัญลักษณ์แทน Identifying relationship type

45. หรือ การสร้างตัวแบบสำหรับข้อมูลที่แปรตามกาลเวลา • เอนทิตีแบบอ่อนสามารถนำมาประยุกต์ใช้ เมื่อแอททริบิวต์บางตัวมีค่าแปรเปลี่ยนตามเวลา (Time-dependent attribute) บางครั้งเรียก Historical attribute Attribute variation • ตัวอย่าง ต้องการติดตามการพัฒนาสุขภาพของสถานเลี้ยงเด็กกำพร้า โดยรวบรวมข้อมูลความสูง และน้ำหนักของเด็กที่เปลี่ยนแปลงในแต่ละเดือน develop PERSON WEIGH_ HEIGH 1 N PERSON(person_id, name, date_of_birth) DEVELOP(person_id, date_measured, weight, height)

46. หรือ เอนทิตีประกอบ • เป็นเอนทิตีที่นำตัวชี้เฉพาะของเอนทิตีอื่น 2 ตัวหรือมากกว่ามาประกอบกันเป็นตัวชี้เฉพาะของตัวเอง ซึ่งเอนทิตีประเภทนี้มักจะมาจากความสัมพันธ์แบบ M:N Composite/Associative entity REGISTRY STUDENT COURSE 1 N REGISTRY STUDENT COURSE N N 1 1

47. กำหนดตัวชี้เฉพาะของเอนทิตีประกอบได้ 2 วิธี Default identifier นำตัวชี้เฉพาะของเอนทิตีประกอบเดิมมาผสมกัน เช่น{std_code, crs_code} 2. Surrogate identifier สร้างตัวชี้เฉพาะขึ้นมาใหม่ ในกรณีที่พบว่าค่า default identifier มีโอกาสซ้ำกันหรือมีตัวชี้เฉพาะที่ผู้ใช้คุ้นเคยอยู่แล้ว เช่น{reg_no} Composite/Associative entity

48. Step 1: Mapping Regular Entities to Relations Step 2: Mapping Weak Entities Transforming ER Diagrams into Relations Step 3: Mapping Unary/Binary Relationships Step 4: Mapping Multivalued attributes Step 5: Mapping N-ary Relationship Types.

49. 1. Mapping Regular Entities to Relations • Simple attributes: ER attributes map directly onto the relation Transforming ER Diagrams into Relations • Composite attributes: Use only their simple, component attributes • Multi-valued attributes: Becomes a separate relation with a foreign key taken from the superior entity Example: We create the relations EMPLOYEE, DEPARTMENT, and PROJECT in the relational schema corresponding to the regular entities in the ER diagram. SSN is a primary key for the relation EMPLOYEE DNUMBER is a primary key for the relation DEPARTMENT and PNUMBER is a primary key for the relation PROJECT.

50. Figure 3.2:The ER conceptual schema diagram for the COMPANY database. Transforming ER Diagrams into Relations