Entity-Relationship Model Chapter 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 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 3 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 Overview of Database Design Process Database design To design the conceptual schema for a database application Applications design Focus on the programs and interfaces that access the database Generally considered part of software engineering 5 Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS Functional requirements Data requirements DBMS–independent FUNCTIONAL ANALYSIS CONCEPTUAL DESIGN High-level transaction Conceptual schema specification LOGICAL DESIGN (DATA MODEL MAPPING) DBMS–specific APPLICATION PROGRAM Database schema DESIGN PHYSICAL DESIGN TRANSACTION Internal schema IMPLEMENTATION Application program 6 Application Design Database Design Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS Data requirements DBMS–independent • Interview prospective database users CONCEPTUAL DESIGN • Result: Conceptual schema Data requirements Functional LOGICAL DESIGN (DATA MODEL MAPPING) requirements DBMS–specific Database schema PHYSICAL DESIGN Internal schema 7 Database Design Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS • Create a conceptual Data requirements DBMS–independent schema using a high- CONCEPTUAL DESIGN level conceptual data model (Entity- Conceptual schema Relationship model) LOGICAL DESIGN • Descriptions of entity (DATA MODEL MAPPING) types, relationships, and constraints DBMS–specific Database schema • Independent of PHYSICAL DESIGN storage and implementation details. Internal schema 8 Database Design Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS • Create a database Data requirements DBMS–independent schema in CONCEPTUAL DESIGN implementation data Conceptual schema model of a commercial DBMS LOGICAL DESIGN • Data model mapping (DATA MODEL MAPPING) DBMS–specific is often automated or Database schema semi-automated within the database design PHYSICAL DESIGN tool. Internal schema 9 Database Design Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS Data requirements DBMS–independent CONCEPTUAL DESIGN Conceptual schema • Specify internal storage LOGICAL DESIGN structures, file (DATA MODEL MAPPING) DBMS–specific organizations, indexes, Database schema access paths, and physical design PHYSICAL DESIGN parameters for the Internal schema database files. 10 Database Design 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 11 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 A Sample Database Application The COMPANY database: keeps track of employees, departments, and projects.
The company is organized into DEPARTMENTs. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations.
A department controls a number of PROJECTs, each of which has a unique name, a unique number, and a single location. 13 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 Entity-Relationship Diagram (ERD) for COMPANY Database 15 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 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 17 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 Why use ER data modelling? User requirements can be specified formally & unambiguously It can be easily understood by ordinary users. It provides an effective bridge between user requirements and logical database design and implementation The conceptual data model is independent of any particular DBMS It does not involve any physical or implemental details 19 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 ER Model Concepts ER model describes data as: Entities Attributes Relationships 21 ERD for COMPANY Database 22 Entity Entity is a thing in the real world with an independent existence.
An entity may be an object with a physical existence (a person, a car, a house, or an employee) or an object with a conceptual existence (a company, a job, or a university course) Examples: In a COMPANY: the EMPLOYEE John Smith the Research DEPARTMENT the ProductX PROJECT 23 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 Types of Attributes Simple attribute has a single atomic value.
SSN, Sex Composite attribute may be composed of several components. Name (First name, Middle name, Last name) Multi-valued attribute has multiple values. Colors of a Car {Color}, Phones of a Person {Phone} Derived attribute has a value that is derivable from values of related attributes. Number of students in a class Complex attribute is a combination of composite and multivalued attributes.
25 Entities and Attributes Two entities, EMPLOYEE e1, and COMPANY c1, and their attributes. 26 Entity Types Collection (or set) of entities that have the same attributes 27 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 Notations of Entity type, Attributes, Key Entity type Simple attribute Composite attribute Multi-valued attribute Derived attribute Key Key 29 Entity Type CAR with two keys and a corresponding Entity Set 30 Identify Entity Types and Attributes The COMPANY database: The company is organized into departments. Each Each department has a unique unique name, name,aaunique uniquenumber, number,and a particular and employee a particular who who employee manages the department.
manages the We keep track department. Weofkeep the start trackdate when of the that start employee date when that began managing employee the department. began managing A department the department. A may have severalmay department locations.
have several locations. A department controls a number of projects, PROJECTs, each of of each which has a unique uniquename, name,aaunique uniquenumber, number,and a a and single location. 31 Identify Entity Types, Attributes We store EMPLOYEE’s employee’s name, name, social Social security Security number, number, address,salary, address, salary,sex, sex,and andbirth birthdate. AnAnemployee employee is is assigned to assigned toone onedepartment, department,but butmay maywork work ononseveral projects,projects, several which are which not necessarily are not necessarily controlledcontrolled by the same by thedepartment.
We keep Wetrack keepoftrack the current of the current number of number ofhours hoursper perweek weekthat thatan anemployee employeeworks workson on each project. We also keep track of the direct direct supervisor of supervisor ofeach eachemployee. We want to keep track of the dependents DEPENDENTs of each of each employee, including includingfirst firstname, name,sex, sex,birth birth date, date,and and relationship to relationship tothe theemployee. 32 Initial Conceptual Design of COMPANY Database 33 ERD for COMPANY Database 34 Case study: Identify Entity Types and Attributes GROUP A GROUP B A system for course A system for a Library of a registration of HCMUT University Entity type Composite att.
Key Key Multi-valued Derived 35 attribute attribute 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,. Each entity ej in ri is a member of entity set Ej Ex: EMPLOYEE John Smith works on the PROJECT ProductX 36 Relationships and Relationship Types Degree of a relationship type Number of participating entity types Binary (degree 2), ternary (degree 3), and n-ary (degree n) More than one relationship type can exist with the same participating entity types. EMPLOYEE – Works-for – DEPARTMENT EMPLOYEE - Manages – DEPARTMENT 37 Example relationship instances A binary relationship 38 Example relationship instances A ternary relationship 39 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 A Recursive Relationship SUPERVISION 1: supervisor 41 2: supervisee 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 One-to-one (1:1) RELATIONSHIP 43 Many-to-many (M:N) RELATIONSHIP 44 Constraints on Binary Relationship Type Participation constraint (or membership class): specifies whether existence of entity depends on its being related to another entity Mandatory (total participation) - every instance of a participating entity type must participate in the relationship. (double line) Optional (partial participation) - not every instance of a participating entity type must participate in the relationship. (single line) 45 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 Attributes of Relationship Types A relationship type can have attributes. HoursPerWeek of WORKS_ON 1:1 relationship type: relationship attributes can be migrated to any participating entity type.
1:N relationship type: relationship attributes can be migrated only to entity type on N-side of relationship. M:N relationship types: relationship attributes cannot be migrated to any entity type.