Database Systems - Lec 2: Entity - Relationship Model - Nguyen Thanh Tung

Nội dung text: Database Systems - Lec 2: Entity - Relationship Model - Nguyen Thanh Tung

1. Outline ▪ What is ER Model? And Why? ▪ Overview of Database Design Process ▪ Example COMPANY Database ▪ ER Model Concepts ▪ ER Diagram ▪ Alternative Diagrammatic Notations ▪ Problems with ER Models ▪ Reading Suggestion: • [1]: Chapter 3 • [2]: Chapter 11 • A. Badia: ”Entity-Relationship Modeling Revisited”, SIGMOD Record, 33(1), March 2004, 77-82 2
2. What is ER Model? And Why? ▪ ER model is a logical organisation of data within a database system ▪ ER model technique is based on relational data model ▪ Why use ER data modelling: • User requirements can be specified formally & unambiguously • The conceptual data model is independent of any particular DBMS • It does not involve any physical or implemental details • It can be easily understood by ordinary users. • It provides an effective bridge between informal user requirements and logical database design and implementation 4
3. Overview of Database Design Process 6
4. Example COMPANY Database ▪ Requirements of the Company (oversimplified for illustrative purposes) • The company is organized into DEPARTMENTs. Each department has a name, number and an employee who manages the department. We keep track of the start date of the department manager • Each department controls a number of PROJECTs. Each project has a name, number and is located at a single location 8
5. Example COMPANY Database 10
6. ER Model Concepts ▪ Types of Attributes • Simple →Each entity has a single atomic value for the attribute. For example, SSN or Sex • Composite →The attribute may be composed of several components. For example, Address (Apt#, House#, Street, City, State, ZipCode, Country) or Name (FirstName, MiddleName, LastName). Composition may form a hierarchy where some components are themselves composite • Multi-valued →An entity may have multiple values for that attribute. For example, Color of a CAR or PreviousDegrees of a STUDENT. Denoted as {Color} or {PreviousDegrees} 12
7. Example COMPANY Database 14
8. Entity Type CAR with two keys and a corresponding Entity Set 16
9. Example COMPANY Database 18
10. ER Model Concepts ▪ Relationships and Relationship Types • More than one relationship type can exist with the same participating entity types. For example, MANAGES and WORKS_FOR are distinct relationships between EMPLOYEE and DEPARTMENT, but with different meanings and different relationship instances 20
11. Example COMPANY Database 22
12. ER Model Concepts ▪ Weak Entity Types • 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 • 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 (unique wrt. each EMPLOYEE), 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 24
13. ER Model Concepts ▪ Structural constraints: one way to express semantics of relationship: cardinality ratio and membership class ▪ Cardinality ratio (functionality): It specifies the number of relationship instances that an 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) ▪ An example of a 1:1 binary relationship is MANAGES which relates a department entity to the employee who manages that department. This represents the miniworld constraints that an employee can manage only one department and that a department has only one manager ▪ Relationship types of degree 2 are called binary. Relationship types of degree 3 are called ternary and of degree n are called n-ary. In general, an n-ary relationship is not equivalent to n binary relationships (reading suggestion !!) 26
14. Many-to-many (M:N) RELATIONSHIP EMPLOYEE WORKS_ON PROJECT r9 r e1 1 p1 e 2 r2 p e3 2 r3 e4 r4 p3 e5 r5 e6 r e7 6 r7 r8 28
15. Example COMPANY Database 30
16. ER Model Concepts ▪ Recursive relationships: • Both participations are same entity type in different roles • For example, SUPERVISION relationships between EMPLOYEE (in role of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or worker) • In following figure, first role participation labeled with 1 and second role participation labeled with 2 • In ER diagram, need to display role names to distinguish participations 32
17. Example COMPANY Database 34
18. ER Diagram Example University Database ▪ Example: The university database maintains records of its departments, lecturers, course modules, and students ▪ The requirements are summarised as follows: • 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 and taught by lecturers. 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 36
19. ER Diagram Example University Database ▪ Entity types and their attributes: • DEPARTMENT: DNAME, LOCATION, FACULTY, • MODULE: MDL-NUMBER, TITLE, TERM, • STUDENT: SNUMBER,SNAME,ADDRESS,SEX,DOB, • LECTURER: LNAME, ROOMNUMBER, PHONE, 38
20. ER Diagram (min, max) notation for relationship structural constraints ▪ Specified on each participation of an entity type E in a relationship type R ▪ Specifies that each entity e in E participates in at least min and at most max relationship instances in R ▪ Default(no constraint): min=0, max=n ▪ Must have min max, min 0, max 1 ▪ Derived from the knowledge of mini-world constraints ▪ Examples: • A department has exactly one manager and an employee can manage at most one department →Specify (0,1) for participation of EMPLOYEE in MANAGES →Specify (1,1) for participation of DEPARTMENT in MANAGES • An employee can work for exactly one department but a department must have at least 4 employees →Specify (1,1) for participation of EMPLOYEE in WORKS_FOR →Specify (4,n) for participation of DEPARTMENT in WORKS_FOR 40
21. ER diagrams for the COMPANY schema, with structural constraints specified using (min, max) notation 42
22. 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 44
23. Problems with ER Models ▪ Problems may arise when designing a conceptual data model called 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 46
24. An Example of a Fan Trap At which branch office does staff number SG37 work? 48
25. An Example of a Chasm Trap At which branch office is property PA14 available? 50
26. ER Model restructured to remove Chasm Trap 52
27. Q&A 54