Thực hành hệ điều hành

Entity-Relationship Model D D T Kh hr. ang ran an Department of Information Systems Faculty of CSE, HCMUT 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 , , , What is ER Model? And Why? E x t e r n a l E x t e r n a l E x t e r n a l S c h e m a 1 S c h e m a 2 S c h e m a N I n t e r f a c e b e t w e e n ƒ Database Approach: where is ER modeling? c o n c e p t u a l s c h e m a a n d e x t e r n a l s c h e m a s I n t e r f a c e b e t w e e n c o n c e p t u a l s c h e m a C o n c e p t u a l S c h e m a I n t e r n a l S c h e m a a n d i n t e r n a l s c h e m a 3 D a t a b a s e p h y s i c a l l y s t o r e d i n f i l e s o n d i s k s 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 Overview of Database Design Process ƒ Two main activities: • Database design • Applications design ƒ Focus in this chapter on database design • To design the conceptual schema for a database application ƒ Applications design focuses on the programs and interfaces that access the database • Generally considered part of software engineering 5 Overview of Database Design Process 6 Outline ƒ What is ER Model? And Why? ƒ Overview of Database Design Process ƒ Example COMPANY Database ƒ ER Model Concepts ER Diƒ agram ƒ 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 7 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 Example COMPANY Database ƒ Requirements of the Company (oversimplified for illustrative purposes) • We store each EMPLOYEE’s social security number, address, salary, sex, and birthdate. 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 • Each employee may have a number of DEPENDENTs. For each dependent, we keep track of their name, sex, bi thd t d l ti hi t lr a e, an re a ons p o emp oyee 9 Example COMPANY Database 10 ER Model Concepts ƒ Entities and Attributes • Entities are specific objects or things in the mini-world that are represented in the database →E.g., the EMPLOYEE John Smith, the Research DEPARTMENT, the ProductX PROJECT, etc. Att ib t ti d t d ib tit• r u es are proper es use o escr e an en y →E.g., an EMPLOYEE entity may have a Name, SSN, Address, Sex, BirthDate • A specific entity will have a value for each of its attributes →E.g., a specific employee entity may have Name='John Smith', SSN='123456789', Address ='731, Fondren, Houston, TX', Sex='M', BirthDate='09-JAN-55‘ • Each attribute has a value set (or data type) associated with it →E.g., integer, string, subrange, enumerated type, etc. 11 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 ER Model Concepts ƒ Types of Attributes • In general composite and multi valued attributes may be , - nested arbitrarily to any number of levels although this is rare →E.g., PreviousDegrees of a STUDENT is a composite multi- valued attribute denoted by { PreviousDegrees (College, Year, Degree, Field) } • Derived Attribute →Attribute that represents a value that is derivable from value of a related attribute or set of attributes not necessarily in the same , , entity type 13 Example COMPANY Database 14 ER Model Concepts ƒ Entity Types and Key Attributes • Entities with the same basic attributes are grouped or typed into an entity type. For example, the EMPLOYEE entity type or the PROJECT entity type • An attribute of an entity type for which each entity must h i l i ll d k tt ib t f th titave a un que va ue s ca e a ey a r u e o e en y type. For example, SSN of EMPLOYEE • A key attribute may be composite. For example, V hi l T N b i k f th CAR tit t ithe c e ag um er s a ey o e en y ype w components (Number, State) • An entity type may have more than one key. For example, the CAR entity type may have two keys: →VehicleIdentificationNumber (popularly called VIN) and →VehicleTagNumber (Number, State), also known as license_plate number 15 Entity Type CAR with two keys and a corresponding Entity Set 16 ER Model Concepts ƒ Relationships and Relationship Types • A relationship relates two or more distinct entities with a specific meaning. For example, EMPLOYEE John Smith works on the ProductX PROJECT or EMPLOYEE Franklin Wong manages the Research DEPARTMENT R l ti hi f th t d t d i t• e a ons ps o e same ype are groupe or ype n o a relationship type. For example, the WORKS_FOR relationship type in which EMPLOYEEs & DEPARTMENTs participate or the MANAGES , relationship type in which EMPLOYEEs & DEPARTMENTs participate • The degree of a relationship type is the number of participating entity types. Both MANAGES and WORKS_ON are binary relationships 17 Example COMPANY Database 18 Example relationship instances 19 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 O ffEMPL YEE and DEPARTMENT, but with di erent meanings and different relationship instances 20 Summary of the Notation for ER Diagrams 21 Example COMPANY Database 22 ER Model Concepts ƒ Attributes of Relationship Types: • A relationship type can have attributes; for example, HoursPerWeek of WORKS_ON; its value for each relationship instance describes the number of hours per week that an EMPLOYEE works on a PROJECT 23 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 Example COMPANY Database 25 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 One-to-many (1:N) or Many-to-one (N:1) RELATIONSHIP EMPLOYEE WORKS_FOR DEPARTMENT e1 z e r1 z d1 2 z e3 z e r2 r3 z d2 4 z e5 z e r4 r5 z d3 6 z e7 z r6 27 r7 Many-to-many (M:N) RELATIONSHIP r9 EMPLOYEE WORKS_ON PROJECT e1 z e r1 z p1 2 z e3 z e r2 r3 z p2 4 z e5 z e6 r4 r5 z p3 z e7 z r6 28 r7r8 ER Model Concepts ƒ Membership class (participation constraint): • Mandatory (total participation) - every instance of a participating tit t t ti i t i th l ti hi E l ATTENDen y ype mus par c pa e n e re a ons p. xamp e: relationship between STUDENTS and COURSE • Optional (partial participation) - not every instance of a participating entity type must participate in the relationship. Example: OFFER relationship between SCHOOL and MODULE is optional for SCHOOL but mandatory for MODULE ƒ Notation: • Cardinality ratio (of a binary relationship): 1:1, 1:N, N:1, or M:N SHOWN BY PLACING APPROPRIATE NUMBER ON THE LINK • Participation constraint (on each participating entity type): total (called existence dependency) or partial. IN ER DIAGRAMS, TOTAL PARTICIPATION IS DISPLAYED AS A DOUBLE LINE CONNECTING THE PARTICIPATING ENTITY TYPE TO THE RELATIONSHIP, WHEREAS PARTIAL PARTICIPATION IS 29 REPRESENTED BY A SINGLE LINE Example COMPANY Database 30 ER Model Concepts ƒ Recursive relationships (involuted relationship): relationship among different instances of the same ient ty 1 PERSON MARRY 1 M EMPLOYEE SUPERVISE N PART COMPRISE N 1 31 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 A Recursive Relationship SUPERVISION EMPLOYEE SUPERVISION e1 z e2 z r1 2 1 e3 z e4 z r2 1 2 1 2 e5 z e6 z r3 r4 2 1 1 2 e7 z r5 1 2 33 r6 Example COMPANY Database 34 ER Diagram ƒ An ER model can be expressed in the form of the ER diagram • An entity type is represented by a rectangular box • A relationship is represented by a diamond-shaped box • Relationships are linked to their constituent entity types by arcs • The functionality of a relationship is indicated on the arc • Attributes of entity types/relationships, and membership classes of entity types are listed separately from the diagram • The key attribute(s) is underlined 35 ER Diagram Example University Database ƒ Example: The university database maintains records of its departments, lecturers, course d l d dmo u es, an stu ents ƒ 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 l t h diff t ( ) Th• ec urers ave eren names we assume so anyway . ey 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 ER Diagram Example University Database DEPARTMENT 1 ƒ Incomplete ER diagram: OFFER 1 1 HEAD_OF IS_IN N 1 N MODULE LECTURERTEACH M 1 N STUDENT M ENROL 37 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 ER Diagram Example University Database ƒ Relationships: • HEAD_OF: →1:1 between LECTURER and DEPARTMENT →Membership: Mandatory for DEPARTMENT • IS_IN: → 1:N between DEPARTMENT and LECTURER → Membership: Mandatory for both • OFFER: → 1:N between DEPARTMENT and MODULE → Membership: Mandatory for MODULE • ENROL: → M:N between STUDENT and MODULE → Membership: Mandatory for both → Attribute: DATE (date of enrolment) • TEACH: → 1:M between LECTURER and MODULE f 39 → Membership: Mandatory or both ƒ Homework: Do complete the ER Diagram !! ER Diagram (min, max) notation for relationship structural constraints ƒ Specified on each participation of an entity type E in a relationship type R S ifi th t h tit i E ti i t i t l t iƒ pec es a eac en y e n par c pa es n a eas m n 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 S if (4 ) f ti i ti f DEPARTMENT i WORKS FOR 40 → pec y ,n or par c pa on o n _ ER Diagram (min, max) notation for relationship structural constraints (1,1)(0,1) (4,N)(1,1) 41 ER diagrams for the COMPANY schema, with structural constraints specified using (min, max) notation 42 Alternative Diagrammatic Notations ƒ Current use (in this class): • Chen notation ƒ Some others: • Crow’s Feet notation • UML (Unified Modeling Language): Rational Rose 43 Alternative Diagrammatic Notations Symbols for entity type / class Di l i tt ib t , attribute and relationship sp ay ng a r u es Displaying cardinality ratios Various (min, max) notations Notations for displaying specialization / generalization 44 The COMPANY conceptual schema in UML class diagram notation. 45 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 Problems with ER Models ƒ Fan Trap • Where a model represents a relationship between entity types, but pathway between certain entity occurrences is ambiguous • Usually: two or more 1:N relationships fan out from the same entity Chasm Trapƒ • Where a model suggests the existence of a relationship between entity types, but pathway does not exist between certain entity occurrences • Usually: optional participation 47 An Example of a Fan Trap 48 At which branch office does staff number SG37 work? Restructuring ER model to remove Fan Trap 49 SG37 works at branch B003 An Example of a Chasm Trap At hi h b h ffi i t PA14 il bl ? 50 w c ranc o ce s proper y ava a e ER Model restructured to remove Chasm Trap ER Model restructured to remove Chasm Trap 52 Summary ƒ What is ER Model? And Why? ƒ Overview of Database Design Process ƒ Example COMPANY Database ƒ ER Model Concepts ƒ ER Diagram ƒ Alternative Diagrammatic Notations (UML) ƒ Problems with ER Models ƒ Next week: • EER • Reading: →[1] Chapter 4 →[2] Chapter 12 53 Q&A 54