Bài giảng Database systems - Entity-relationship model

DATABASE SYSTEMS Nguyen Ngoc Thien An ENTITY-RELATIONSHIP MODEL Spring 2014 Contents  Database Design Process  What is ER Model? And Why?  A Sample Database Application  ER Model Concepts  ER Diagram and Naming Conventions  Alternative Diagrammatic Notations  Problems with ER Models  Reading Suggestion: [1] Chapter 7 2 3 Database Design Process (1) 4  Two main activities:  Database design.  Application design.  Focus in this chapter on database design.  To design the conceptual schema for a database application.  Application design focuses on the programs and interfaces that access the database.  Generally considered part of software engineering. Database Design Process (2) 5  Requirements collection and analysis  Database designers interview prospective database users to understand and document data requirements.  Result:  Data requirements.  Functional requirements of the application.  Conceptual design  Create a conceptual schema for the database using a high-level conceptual data model.  Conceptual schema  Description of data requirements.  Includes detailed descriptions of the entity types, relationships, and constraints.  Independent of storage and implementation details. Database Design Process (3) 6  Logical design (data model mapping)  The conceptual schema is transformed from high- level data model into implementation data model.  The result is a database schema in the implementation data model of DBMS.  Physical design phase  Internal storage structures, file organizations, indexes, access paths, and physical design parameters for the database files are specified. Contents  Database Design Process  What is ER Model? And Why?  A Sample Database Application  ER Model Concepts  ER Diagram and Naming Conventions  Alternative Diagrammatic Notations  Problems with ER Models 7 What is ER Model? 8  Entity-Relationship (ER) model  A popular high-level conceptual data model.  A logical organisation of data within a database system.  ER model technique is based on relational data model.  ER diagrams (ERD)  Diagrammatic notation associated with the ER model. Why use ER data modelling? 9  User requirements can be specified formally and 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. Contents  Database Design Process  What is ER Model? And Why?  A Sample Database Application  ER Model Concepts  ER Diagram and Naming Conventions  Alternative Diagrammatic Notations  Problems with ER Models 10 A Sample Database Application (1) 11  The COMPANY database  Keep track of employees, departments, and projects.  Company is organized into DEPARTMENTs.  Each department: a unique name, a unique number, a particular employee who manages it.  Keep track of the start date when that employee began managing the department.  A department may have several locations.  Department controls a number of PROJECTs.  Each project: a unique name, a unique number, and a single location. A Sample Database Application (2) 12  The COMPANY database  Each EMPLOYEE  Has name, Social Security number, address, salary, sex (gender), birth date.  Be assigned to one department.  May work on several projects, which are not necessarily controlled by the same department.  Keep track of the current number of hours per week that an employee works on each project.  Keep track of the direct supervisor of each employee (who is another employee).  Keep track of the DEPENDENTs of each employee.  Each dependent’s first name, sex, birth date, and relation-ship to the employee. A Sample Database Application (3) 13 Contents  Database Design Process  What is ER Model? And Why?  A Sample Database Application  ER Model Concepts  ER Diagram and Naming Conventions  Alternative Diagrammatic Notations  Problems with ER Models 14 Contents - ER Model Concepts 15  Entities & Attributes  Types of Attributes  NULL Values  Entity Types & Entity Sets  Key or Uniqueness Constraint  Relationships & Relationship Types  Relationship Degree  Recursive Relationships  Constraints on Binary Relationship Types  Min-max Notation  Attributes of Relationship Types  Weak Entity Types  Exercise: University Database  Relationship Types of Degree Higher than Two How ER model describes data? 16 Data in ER model Entities Attributes Relationships Entities & Attributes (1) 17  Entity is a specific object or a thing in the mini-world with independent existence.  E.g.: the EMPLOYEE John Smith, the Research DEPARTMENT, the ProductX PROJECT, etc.  Attributes are properties used to describe an entity.  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 domain of values) associated with that attribute to specify set of values that may be assigned to it for each individual entity.  E.g.: integer, string, subrange, enumerated type, etc. Entities & Attributes (2) 18 Two entities (EMPLOYEE e1 and COMPANY c1) and their attributes Types of Attributes (1) 19  Simple (atomic) vs. Composite attributes  Simple attribute: Each entity has a single atomic value for the attribute.  E.g.: SSN; Sex  Composite attribute: The attribute may be composed of several components.  E.g.: Address (Apt#, House#, Street, City, State, ZipCode, Country); Name (FirstName, MiddleName, LastName)  Composition may form a hierarchy where some components are themselves composite.  Single-valued vs. Multi-valued attributes  Multi-valued attribute: An entity may have multiple values for that attribute.  E.g.: Color of a CAR or PreviousDegrees of a STUDENT  Denoted as {Colors} or {PreviousDegrees} Types of Attributes (2) 20 Types of Attributes (3) 21  Stored vs. Derived attributes  Derived attribute: represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type.  E.g.: GPA of a STUDENT  Complex attributes  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)}. NULL Values 22  NULL values are used for:  Not applicable: In some cases, a particular entity may not have an applicable value for an attribute.  E.g.: a College_degrees attribute applies only to people with college degrees.  Unknown: Missing: It is known that the attribute value exists but is missing.  Not known: It is not known whether the attribute value exists. COMPANY Database 23 Entity Types & Entity Sets (1) 24  Entities with the same basic attributes are grouped or typed into an entity type.  E.g.: the EMPLOYEE entity type or the PROJECT entity type.  The collection of all entities of a particular entity type in the database at any point in time is called an entity set.  The entity set is usually referred to using the same name as the entity type.  E.g.: EMPLOYEE refers to both a type of entity as well as the current set of all employee entities in the database. Entity Types & Entity Sets (2) 25 Key or Uniqueness Constraint (1) 26  An entity type has one or more key attributes whose values are distinct for each individual entity in entity set.  E.g.: SSN of EMPLOYEE  A key attribute may be composite.  E.g.: VehicleTagNumber is a key of the CAR entity type with components (Number, State).  An entity type may have more than one key attribute.  E.g.: the STUDENT entity type may have two keys (in university context): Citizen ID and Student ID. Key or Uniqueness Constraint (2) 27 Entity Type CAR with two keys and a corresponding Entity Set COMPANY Database 28 Initial Conceptual Design of the COMPANY Database Relationships & Relationship Types (1) 29  A relationship relates two or more distinct entities with a specific meaning.  E.g.: EMPLOYEE John Smith works on the ProductX PROJECT; EMPLOYEE Franklin Wong manages the Research DEPARTMENT.  Relationships of the same type are grouped or typed into a relationship type.  E.g.: the WORKS_FOR relationship type in which EMPLOYEEs & DEPARTMENTs participate; the MANAGES relationship type in which EMPLOYEEs & DEPARTMENTs participate. Relationships & Relationship Types (2) 30  Relationship type R among n entity types E1, E2, ..., En  Defines a set of associations - or a relationship set - among entities from these entity types.  Relationship instances ri  Each ri associates n individual entities (e1, e2, ..., en).  Each entity ej in ri is a member of entity set Ej. Relationship Degree (1) 31  The degree of a relationship type  The number of participating entity types.  Binary (degree 2), ternary (degree 3), n-ary (degree n)  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, but with different meanings and different relationship instances. Relationship Degree (2) 32 Relationship instances of a binary relationship type Relationship Degree (3) 33 Relationship instances of a ternary relationship type Recursive Relationships (1) 34  Recursive relationships  Same entity type participates more than once in a relationship type in different roles.  Must specify the role that a participating entity plays in each relationship instance.  E.g.: SUPERVISION relationships between EMPLOYEE (in role of supervisor or boss) and (another) EMPLOYEE (in role of subordinate or worker). Recursive Relationships (2) 35 COMPANY Database 36 Constraints on Binary Relationship Types (1) 37  Structural constraints: one way to express semantics of relationship  Cardinality ratio  Membership class  Cardinality Ratios (functionality) (for a binary relationship)  Specifies the maximum number of relationship instances that an entity can participate in.  one-to-one (1:1)  E.g.: 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.  one-to-many (1:M) or many-to-one (M:1)  many-to-many (M:N) Constraints on Binary Relationship Types (2) 38 1:N or N:1 Relationship e1 r1 e2 r2 e3 r3 e4 r4 e5 r5 e6 r6 e7 r7 d1 d2 d3 EMPLOYEE WORKS_FOR DEPARTMENT Constraints on Binary Relationship Types (3) 39 M:N Relationship e1 r1 e2 r2 e3 r3 e4 r4 e5 r5 e6 r6 e7 r7 p1 p2 p3 EMPLOYEE WORKS_ON PROJECT r8 r9 Constraints on Binary Relationship Types (4) 40  Membership class (participation constraint):  Mandatory (total participation): every instance of a participating entity type must participate in the relationship (double line).  E.g.: ATTEND relationship between STUDENTS and COURSE.  Optional (partial participatiaon): not every instance of a participating entity type must participate in the relationship (single line).  E.g.: OFFER relationship between SCHOOL and MODULE is optional for SCHOOL but mandatory for MODULE. COMPANY Database 41 Min-max Notation (1) 42  Min-max notation for relationship types:  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. Min-max Notation (2) 43 A department has exactly one manager and an employee can manage at most one department. An employee can work for exactly one department but a department must have at least 4 employees. Employee DepartmentWorks_for (1,1) (4,n) Employee DepartmentManages (0,1) (1,1) 44 Attributes of Relationship Types 45  A relationship type can have attributes.  E.g.: HoursPerWeek of WORKS_ON  Attributes of 1:1 or 1:N relationship types can be migrated to one entity type.  For 1:N relationship types:  Relationship attribute can be migrated only to entity type on N-side of relationship.  For M:N relationship types:  Must be specified as relationship attributes. COMPANY Database 46 Weak Entity Types (1) 47  Weak entity types  Do not have key attributes of their own.  Identified by being related to specific entities from another entity type.  Identifying relationship: A weak entity type must participate in an identifying relationship type with an owner or identifying entity type.  A weak entity type normally has a partial key, which is the attribute that can uniquely identify weak entities that are related to the same owner entity.  If a weak entity has no a partial key, a composite attribute of all the weak entity’s attributes will be the partial key. Weak Entity Types (2) 48  A weak entity is identified by the combination of:  Its partial key.  The particular entities which the weak entity is related to in the identifying relationship.  E.g.: 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.  A weak entity type always has a total participation constraint in its identifying relationship type. COMPANY Database 49 Exercise: University Database 50  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 at least 10 students. Each module has a unique module number.  Students must enrol for a number of modules. Each student is given a unique student number. Relationship Types of Degree Higher than Two (1) 54 one relationship type of degree n several relationship types of smaller degrees Relationship Types of Degree Higher than Two (2) 55  In general, an n-ary relationship is not equivalent to n binary relationships. Make decisions based on the semantics or meaning of the particular situation being represented.  May include the n-ary relationship plus one or more of the smaller degree relationships if necessary.  Two notations for specifying structural constraints on n-ary relationships specify different constraints:  Based on the cardinality ratio notation.  Based on the (min, max) notation.  Both should be used if it is important to fully specify structural constraints. Relationship Types of Degree Higher than Two (3) 56  Some database design tools permit only binary relationships.  Two ways:  Way 1:  The n-ary relationship must be represented as a weak entity type.  With n identifying relationships.  No partial key.  Way 2:  Represent n-ary relationship as a regular entity type.  Introducing an artificial or surrogate key. Reading suggestion: [1] 7.9 Contents  Database Design Process  What is ER Model? And Why?  A Sample Database Application  ER Model Concepts  ER Diagram and Naming Conventions  Alternative Diagrammatic Notations  Problems with ER Models 58 Naming Conventions  An ER model can be expressed in the form of the ER diagram (ERD).  Proper naming of schema constructs  Choose names that convey meanings attached to different constructs in schema.  Nouns give rise to entity type names.  Verbs indicate names of relationship types.  Choose binary relationship names to make ER diagram readable from left to right and from top to bottom. Summary of the Notation for ERD COMPANY Database 61 Contents  Database Design Process  What is ER Model? And Why?  A Sample Database Application  ER Model Concepts  ER Diagram and Naming Conventions  Alternative Diagrammatic Notations  Problems with ER Models 62 Alternative Diagrammatic Notations 63  Current use (in this class):  Chen notation  Some others:  Crow’s Feet notation  UML (Unified Modeling Language): Rational Rose 64 Symbols for entity type/class, attribute and relationship Displaying attributes Displaying cardinality ratios Various (min, max) notations Notations for displaying specialization/generalization UML Class Diagrams (1) 65  UML methodology  Used extensively in software design.  Many types of diagrams for various software design purposes.  UML class diagrams  Entity in ER corresponds to an object in UML.  Class includes three sections:  Top section gives the class name.  Middle section includes the attributes.  Last section includes operations that can be applied to individual objects.  Associations: relationship types.  Relationship instances: links. UML Class Diagrams (2) 66  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.  Multiplicities: min..max, asterisk (*) indicates no maximum limit on participation.  Types of relationships: association and aggregation.  Distinguish between unidirectional and idirectional associations.  Model weak entities using qualified association. The COMPANY conceptual schema in UML class diagram notation Contents  Database Design Process  What is ER Model? And Why?  A Sample Database Application  ER Model Concepts  ER Diagram and Naming Conventions  Alternative Diagrammatic Notations  Problems with ER Models 68 Problems with ER Models (1) 69  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. Problems with ER Models (2) 70  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. An Example of a Fan Trap 71 At which branch office does staff number SG37 work? Staff DivisionWorks_for (1,1) (1,n) Operates Branch (1,n) (1,1) SG37 r1 SA9 r2 SL21 r3 D1 Staff entities Division entities Branch entities r4 r6 B003 B007 B005 D2 r5 Restructuring ER Model to Remove Fan Trap 72 SG37 works at branch B003 D1 r1 D2 r2 B005r3 B003 Division entities Branch entities Staff entities r4 r6 SG37 SA9 SL21 B007 r5 Division BranchOperates (1,n) (1,n) Has Staff (1,1) (1,1) Restructuring the model resolves the fan trap An Example of a Chasm Trap 73 At which branch office is property PA14 available? Branch StaffHas (1,n) (0,n) Oversees Property ForRent (1,1) (0,1) B003 r1 B007 r2 B005 r3 SG37 SL21 Branch entities Staff entities PropertyForRent entities r4 r5 PG36 PA14 PL94 SA9 Restructuring ER Model to Remove Chasm Trap (1) 74 Branch StaffHas (1,n) (0,n) Oversees Property ForRent (1,1) (0,1) Adding the Offers relationship resolves the chasm trap Offers (1,n) (1,1) Restructuring ER Model to Remove Chasm Trap (2) 75 B003 r1 B007 r2 B005 r3 SG37 SL21 Branch entities Staff entities PropertyForRent entities r4 r5 PG36 PA14 PL94 SA9 r6 r7 r8 Q & A 76