Database Systems - Chapter 2: Entity-Relationship Model - Trương Quỳnh Chi

Nội dung text: Database Systems - Chapter 2: Entity-Relationship Model - Trương Quỳnh Chi

1. Contents 1 Overview of Database Design Process 2 A Sample Database Application 3 What is ER Model? And Why? 4 ER Model Concepts 5 ER Diagram and Naming Conventions 6 Alternative Diagrammatic Notations 7 Problems with ER Models 2
2. Contents 1 Overview of Database Design Process 2 A Sample Database Application 3 What is ER Model? And Why? 4 ER Model Concepts 5 ER Diagram and Naming Conventions 6 Alternative Diagrammatic Notations 7 Problems with ER Models 4
3. Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS Functional requirements Data requirements FUNCTIONAL ANALYSIS CONCEPTUAL DESIGN independent Conceptual schema – High-level transaction specification DBMS LOGICAL DESIGN (DATA MODEL MAPPING) APPLICATION PROGRAM Database schema specific DESIGN – PHYSICAL DESIGN DBMS TRANSACTION Internal schema IMPLEMENTATION Application program 6 Application Design Database Design
4. Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS • Create a conceptual Data requirements schema using a high- CONCEPTUAL DESIGN level conceptual data independent Conceptual schema model (Entity- – Relationship model) DBMS LOGICAL DESIGN • Descriptions of entity (DATA MODEL MAPPING) types, relationships, and constraints Database schema specific • Independent of – PHYSICAL DESIGN storage and DBMS implementation details. Internal schema 8 Database Design
5. Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS Data requirements CONCEPTUAL DESIGN independent Conceptual schema – • Specify internal storage DBMS LOGICAL DESIGN structures, file (DATA MODEL MAPPING) organizations, indexes, Database schema specific access paths, and – physical design PHYSICAL DESIGN parameters for the DBMS database files. Internal schema 10 Database Design
6. A Sample Database Application  Design a database for a COMPANY that keeps track of employees, departments, and projects REQUIREMENTS - COLLECTION & ANALYSIS Data requirements - Entities - Attributes - Relationships - Constraints 12
7. A Sample Database Application  We store EMPLOYEE’s name, Social Security number, address, salary, sex, and birth date. An employee is assigned to one department, but may work on several projects, which are not necessarily controlled by the same department. We keep track of the current number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee.  We want to keep track of the DEPENDENTs of each employee, including first name, sex, birth date, and relationship to the employee. 14
8. Case study: Requirements - Collection & Analysis Data requirements - Entities - Attributes - Relationships - Constraints GROUP A GROUP B A system for course A system for a Library of a registration of HCMUT University 16
9. What is ER Model?  Entity-Relationship (ER) model  Popular high-level conceptual data model  A logical organisation of data within a database system  ER Diagrams (ERD):  Diagrammatic notation associated with the ER model  Conceptual Design: Data requirements Conceptual Schema (ERD) 18
10. Contents 1 Overview of Database Design Process 2 A Sample Database Application 3 What is ER Model? And Why? 4 ER Model Concepts 5 ER Diagram and Naming Conventions 6 Alternative Diagrammatic Notations 7 Problems with ER Models 20
11. ERD for COMPANY Database 22
12. Attribute  Attributes are properties described an entity.  Ex: an EMPLOYEE entity may have Name, SSN, Address, Sex, BirthDate  A specific entity will have a value for each of its attributes.  Each attribute has a value set (or data type) associated with it. 24
13. Entities and Attributes Two entities, EMPLOYEE e1, and COMPANY c1, and their attributes. 26
14. Keys  Key or uniqueness constraint  Attributes whose values are distinct for each individual entity in entity set  Uniqueness property must hold for every entity set of the entity type  Ex: SSN of EMPLOYEE  An entity type may have more than one key.  Ex: the STUDENT entity type may have two keys (in university context):  Citizen ID and  Student ID 28
15. Entity Type CAR with two keys and a corresponding Entity Set 30
16. Identify Entity Types, Attributes  We store EMPLOYEEemployee’s name,’s name, socialSocial security Security number, number , address, salary, salary, sex, sex and, and birthbirth date. date An. An employee employee is is assigned to to one one department, department but, but may may work work on on several projects,several projects which are, which not necessarily are not necessarily controlled controlled by the sameby the department. same department. We keep We track keep of track the currentof the current number ofof hourshours per per week week thatthat an an employee employee works works on on each project. We also keep track of the direct supervisor of of each each employee. employee.  We want to keep track of the dependentsDEPENDENTs of eachof each employee, including first first name, name, sex, sex, birth birth date, date and, and relationship toto the the employee. employee. 32
17. ERD for COMPANY Database 34
18. Relationships and Relationship Types  Relationship type R among n entity types E1, E2, , En  Defines a set of associations among entities from these entity types  Ex: Relationship type WORKS_FOR between EMPLOYEEs and DEPARTMENTs  Relationship instances ri  Each ri associates n individual entities (e1, e2, , en). Each entity ej in ri is a member of entity set Ej  Ex: EMPLOYEE John Smith works on the PROJECT ProductX 36
19. Example relationship instances A binary relationship 38
20. Relationships and Relationship Types  Recursive relationships  Same entity type participates more than once in a relationship type in different roles  Must specify role that a participating entity plays in each relationship instance  Ex: SUPERVISION relationships between EMPLOYEE (in role of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or worker) 40
21. Constraints on Binary Relationship Type  Structural constraints: one way to express semantics of relationship: cardinality ratio and participation constraint.  Cardinality ratio: specifies maximum number of relationship instances that entity can participate in a binary relationship.  one-to-one (1:1)  one-to-many (1:M) or many-to-one (M:1)  many-to-many (M:N) 42
22. Many-to-many (M:N) RELATIONSHIP 44
23. Notations of Relationship type  Relationship type Cardinality An EMPLOYEE works for one DEPARTMENT. ratio A DEPARTMENT has many EMPLOYEEs. N Works- 1 EMPLOYEE DEPARTMENT for An EMPLOYEE must works for a DEPARTMENT. Participation A DEPARTMENT may have no EMPLOYEE. constraint 46
24. ERD for COMPANY Database 48
25. Notations of Relationship type  Weak entity type  Identifying relationship type  Partial key Name SSN N 1 DEPENDENT Of EMPLOYEE Identifying/Parent/ Owner entity type 50
26. Identify Entity Types, Attributes, Relationships  We store EMPLOYEEemployee’s name,’s name, social Social security Security number, number , address, salary, salary, sex, sex and, and birthbirth date. date An. An employee employee is is assigned to to one one department, department, but but may may work work on on severalseveral projects, which are not necessarily controlled by the same department. We keep track of the current number numberof hours perof hours week perthat week an employee that an employee works on workseach on eachproject. project. We also We keep also keeptrack track of the of direct the direct supervisor of supervisoreach employee. of each employee.  We want to keep track of the dependentsDEPENDENTs of eachof each employee, including first first name, name, sex, sex, birth birth date, date and, and relationship toto the the employee. employee. 52
27. Contents 1 Overview of Database Design Process 2 A Sample Database Application 3 What is ER Model? And Why? 4 ER Model Concepts 5 ER Diagram and Naming Conventions 6 Alternative Diagrammatic Notations 7 Problems with ER Models 54
28. Summary of the Notation for ER Diagrams 56
29. Draw Entity-Relationship Diagram  We store EMPLOYEEemployee’s name,’s name, social Social security Security number, number , address, salary, salary, sex, sex and, and birthbirth date. date An. An employee employee is is assigned to to one one department, department, but but may may work work on on severalseveral projects, which are not necessarily controlled by the same department. We keep track of the current number numberof hours perof hours week perthat week an employee that an employee works on workseach on eachproject. project. We also We keep also keeptrack track of the of direct the direct supervisor of supervisoreach employee. of each employee.  We want to keep track of the dependentsDEPENDENTs of eachof each employee, including first first name, name, sex, sex, birth birth date, date and, and relationship toto the the employee. employee. 58
30. Case study: Draw ERD GROUP A GROUP B A system for course A system for a Library of a registration of HCMUT University 60
31. Alternative Diagrammatic Notations  (Min-max) notation for relationships  Specify structural constraints on relationships  Replaces cardinality ratio (1:1, 1:N, M:N) and single/double line notation for participation constraints  Associate a pair of integer numbers (min, max) with each participation of an entity type E in a relationship type R, where 0 ≤ min ≤ max and max ≥ 1 62
32. Alternative Diagrammatic Notations  UML class diagrams  Binary association  Represented as a line connecting participating classes  May optionally have a name  Link attribute  Placed in a box connected to the association’s line by a dashed line 68
33. Alternative Diagrammatic Notations Symbols for entity type / class, Displaying attributes attribute and relationship Notations for displaying Various (min, Displaying specialization / generalization max) notations cardinality ratios 70
34. Problems with ER Models  Semantic constraints  Connection traps 72
35. Connection traps  Often due to a misinterpretation of the meaning of certain relationships  Two main types of connection traps are called fan traps and chasm traps 74
36. An Example of a Fan Trap At which branch office does staff number SG37 work? 76
37. An Example of a Chasm Trap At which branch office is property PA14 available? 78
38. Contents 1 Overview of Database Design Process 2 A Sample Database Application 3 What is ER Model? And Why? 4 ER Model Concepts 5 ER Diagram and Naming Conventions 6 Alternative Diagrammatic Notations 7 Problems with ER Models 80
39. Exercise 1: University Database The university database maintains records of its departments, lecturers, course modules, and students. The university consists of departments. Each department has a unique name and some other descriptive attributes. A department must also have a number of lecturers, one of which is the head of department. All lecturers have different names (we assume so anyway). They must teach one or more modules. A lecturer can only belong to one department. Modules are offered by departments . A module is taught by one lecturer. They must also be attended by some students. Each module has a unique module number. Students must enrol for a number of modules. Each student is given a unique student number 82