The Entity-Relationship
Model
Davood Rafiei
Database Design Process
Real World
Functional Database
Specifications Specifications
Conceptual Schema
E-R Modeling
Design
Mapping to DBMS
Relational Model Data Model
Normal Forms Scheme Refinement
Tuning, index Physical Design
selection, ...
2
ER Model Overview
Developed by Peter Chen in the mid 70’s
Used for the design of conceptual schema.
The “world” is described in terms of
• entities
• relationships
• attributes
The model is visualized by creating an ER
diagram.
ER Model Basics
Entity: a distinguishable object
• e.g. person, thing, concept
Entity set: a set of entities of the same type.
Examples of entity sets:
• students registered at UofA
• cars currently registered in Alberta
• flights offered by Air Canada
Graphical representation:
students cars flights
4
ER Model Basics
Relationship: represents the fact that certain
entities are related to each other.
• e.g. John has taken CMPUT 291.
Relationship set: set of relationships of the
same type.
Examples of relationship sets:
• students enrolled in courses
• cars registered to owners
• passengers booked on flights
Graphical representation:
enrolled registered booked
5
ER Model Basics
Attribute: describes a property of an entity or a
relationship.
Attributes of entities - examples
• student: id, name, major, …
• car: VIN, year, model, …
• flight: No, source, destination, …
Graphical representation:
id major VIN year model No source
name dest
students cars flights
6
ER Model Basics
Key: a minimal set of attributes that uniquely
identifies each entity in an entity set.
Attributes of relationships - examples
• student enrolled in a course: year, semester, grade
• book on loan: loan date, due date
semester grade
loan
year date
enrolled
loan
due
date
7
Examples name
sin
since
name pname Employees
sin pno budget
super- subor-
visor dinate
Employees Works_On Projects
Reports_To
• Role: the function of an entity set in a relationship set.
• Role labels are needed whenever an entity set has
multiple functions in a relationship set.
8
Constraints and Complications
Key constraints
• in binary relationships:
binary relationship types
• in general relationships
Participation constraints
Set-valued attributes
Weak entities
ISA hierarchies
Binary Relationship Types: Many-to-Many
Constraint: none.
Employees Works_On Projects
• Each employee can be in relationships with many
projects and vice versa.
• Alternative representation
N N
Employees Works_On Projects
10
Binary Relationship Types: Many-to-One
Constraint: each employee works in at most one
department.
Employees Works_In Departments
• Given an employee, we can uniquely identify the
department he/she works in.
11
Binary Relationship Types: One-to-One
Constraint: each employee can manage at most
one department and each department is
managed by at most one employee.
Employees Manages Departments
• Each employee can be in relationship with at most
one department and vice versa.
12
Ternary Relationships
Suppliers Supplies Projects
Parts
Meaning: Supplier s supplies part p for project r
Can we represent this using binary relationships?
Complication: add the Constraint “each part is supplied by a unique
supplier for a unique project,”
• i.e. each part is in relationship with at most one supplier and one project.
13
Participation Constraints
Does every project have a supervisor?
• If so, this is a participation constraint: the participation of Projects
in Supervises is said to be total (vs. partial).
Every pid value in Projects table must appear in a row of the
Supervises table (with a non-null sin value!)
since
name pname
sin pid budget
Employees Supervises Projects
Works_On
since
14
Set-Valued Attributes
Each employee can have one or more hobby.
• Attribute value can be a set (in contrast to relational model)
• E.g. (111111, John, (stamps, coins))
name
hobby
sin
Employees
15
Weak Entities
A weak entity can be identified uniquely only by considering
the primary key of another (owner) entity.
• Models PART-OF relationships.
• Owner entity set and weak entity set must participate in a one-to-
many relationship set (one owner, many weak entities).
• Weak entity set must have total participation in this identifying
relationship set.
name
cost dname age
sin
Employees Policy Dependents
16
Weak Entities
Prof
Examples:
name teaches
cid sno
Course sec_of Section
ino lno desc amount
Invoice line_of line
17
ISA Hierarchy
Consider forming a new entity set as the union
of two or more entity sets.
name
sin
Employees
hourly_wages hours_worked
ISA
contractid
Attributes and relationships
common to all lower-level Contract_Emps
Hourly_Emps
entity sets are moved to the
higher-level entity set. Contract_Emps and Hourly_Emps cover Employees.
18
ISA Hierarchy (cont.)
Also consider forming a derived entity set by
taking a subset of a given entity set.
sid name
We did a specialization Students
ISA
Undergrad_students
major
19
Properties of ISA
Inheritance
• Attributes of supertype are attributes of subtype
• Key of supertype is key of subtype
• Relationship of supertype is relationship of subtype
Transitivity - Hierarchy of IsA
• Undergard student is subtype of Student, Student is subtype of
Person, so Undergard student is also a subtype of Person
Reasons for using ISA:
• Makes ER diagram more concise and readable.
• Common attributes/relationships need not be
repeated.
20
ISA Constraints
name
Covering constraints: ssn
Does every Employees entity also
have to be an Hourly_Emps or a Employees
Contract_Emps entity? hourly_wages hours_worked
(default:no) ISA
contractid
Hourly_Emps Contract_Emps
Overlap constraints: Can Joe be Contract_Emps and Hourly_Emps cover Employees.
an Hourly_Emps as well as a Contract_Emps overlap Hourly_Emps.
Contract_Emps entity?
(default:disallowed)
21
Conceptual Design Using the ER
Model
Design choices:
• Should a concept be modeled as an entity or an attribute?
• Should a concept be modeled as an entity or a relationship?
• Identifying relationships: Binary or ternary?
22
Entity vs. Attribute
Should address be an attribute of Employees or an
entity (connected to Employees by a relationship)?
Depends on the use we want to make of address
information and the semantics of the data:
• is it an object that we want to keep information about
(independent from employees)?
• does it participate in a relationship with an entity other than
employees?
• are there many employees with no addresses?
• can several employees share the same address?
A positive answer to one or more of those questions
implies address better be modeled as an entity.
23
Entity vs. Attribute (Contd.)
from to
Compare & contrast
Can an employee work
in two or more periods? Employees Works_In Departments
• first diagram: No
• second diagram: Yes
Employees Works_In Departments
from Duration to
We have made up an entity called Duration!
24
Entity vs. Relationship
Compare & contrast
Employees Manages Departments
time
apptnum Mgr_Appts
budget
time budget
Employees Manages Departments
25
Binary vs. Ternary Relationships
qty
Suppliers Supplies Projects
Parts
Can we represent this using binary relationships?
• No combination of binary relationships is an adequate substitute:
supplier s “supplies” part p,
part p “used_in” project r, and
Supplier s “supplies_to” project r
• They do not imply that part p supplied by supplier s is used in
project r.
• How do we record qty?
26
Binary vs. Ternary Relationships
qty
Now add the constraint:
each part is supplied by a Suppliers Supplies Projects
unique supplier.
• not possible!
• a key constraint on Parts Parts
would also mean each part
can only be used in a single
project!
Solution: qty
Suppliers Supplies Parts Used_in Projects
What is the additional constraint here?
27
Summary of Conceptual Design
Conceptual design follows requirements analysis,
• Yields a high-level description of data to be stored
ER model popular for conceptual design
• Constructs are expressive, close to the way people think
about their applications.
Basic constructs: entities, relationships, and
attributes (of entities and relationships).
Some additional constructs: weak entities and ISA
hierarchies.
28
Summary of ER (Contd.)
Constraints play an important role in determining the
best database design for an enterprise.
• Several kinds of integrity constraints can be expressed in
the ER model.
• Some constraints (notably, functional dependencies) cannot
be expressed in the ER model.
ER design is subjective. There are often many ways
to model a given scenario!
29
ER Exercise
• Professors have a SIN, a name, an age, a rank, and a research specialty.
• Projects have a project number, a sponsor name (e.g. NSERC), a starting date, an
ending date, and a budget.
• Graduate students have a SIN, a name, an age, and a degree program (e.g. MS or PhD)
• Each project is managed by one professor (principal investigator).
• Each project is worked on by one or more professors (co-investigators).
• Professors can manage and/or work on multiple projects.
• Each project is worked on by one or more graduate students (research assistants).
• When graduate students work on a project, a professor must supervise their work
on the project. Graduate students can work on multiple projects, in which case they
will have a (potentially different) supervisor for each one.
• Departments have a department number, a department name, and a main office.
• Departments have a professor (chairman) who runs the department.
• Professors work in one or more departments, and for each department that they
work in, a time percentage is associated with their job.
Design an ER diagram ...
30