Introduction to ER
Modelling
COMP23111 - Database Systems
Gareth Henshall
Department of Computer Science
1
Phases of DB design
1 requirements analysis what problem is being solved?
what does the DB need to do?
2 logical design identification of
entities and their
relationships (make ER model)
3 normalization systematic simplification and clarification of the
logical design
4 physical design implementation in DBMS
2
Entity-Relationship model
§ ER model concepts
1. entity: an identifiable unique “thing” which independently
exists. Has a type. A noun.
2. attribute: a property of an entity type. An adjective.
3. relationship: a semantic link between entity types. A verb.
3
Entity example
§ entity: an identifiable
unique “thing” which
independently exists. Comic book character
A noun.
* alias
name
§ attribute: a property of an
qualities
entity. An adjective. At
least one attribute data lifestyle
value (*) should be unique.
4
Entity instances
§ here, one entity type
§ but two instances of it,
each with different data
Comic book character Comic book character
* alias: Batman * alias: The Penguin
name: Bruce Wayne name: Oswald Cobblepot
qualities: {genius, athletic,5 qualities: {expert thief, 5
detective, stunt driver} violent, intelligent, ruthless}
lifestyle: does good lifestyle: does evil
5
Relationships between
entity types
relationship name
Comic book character
City
* alias lives in * name
name
state
qualities
population
lifestyle
§ we define semantic relationships between types of entity
6
ER model example
§ company sells products
§ customers place orders for products
§ employees process the orders
§ let’s define the context as “customers buying things” (not, for
example, “the structure of the company)
§ we can identify some entities immediately
Customer Product Order Employee
7
Attributes
§ each type of entity has a set of attributes which describe it
§ * marks an attribute which will always have a unique value,
aka the identifier of the entity
Customer Product Order Employee
* id * id * id * id
name name date name
address status office
balance processor 8
Attributes
§ during conceptual design, attributes and
relationships express logical structure, and
also semantics Customer
§ at DB implementation time, we will add PKs * id
and FKs, and data constraints name
§ however, it is common to think about keys address
during conceptual design too. In fact, it is balance
hard not to do so!
9
Relationships
§ a relationship connects two or more entities with meaning
§ example: entities Customer and Order
§ a customer places an order
§ here, places is a called a “binary” relationship, since it involves
two types of entity; relationships can also be ternary (3 entity
types) etc.
10
Representing relationships
§ let’s look at the relationship between Customer and Order
§ a customer can place an order
Customer
Order
* id
* id
name
date
address
status
balance
processor 11
Cardinality constraints
§ how many instances of entities of each type can take part
in the relationship between them?
Customer
Order
* id places * id
name
date
address
status
balance
processor 12
Cardinality constraints
§ a customer can place 0 or many orders
§ an order can have only one customer
Customer
Order
* id places * id
name
date
address
status
balance
processor
13
Cardinality constraints
§ we indicate cardinality by annotating the relationship line
a customer can have minimum 0 and maximum many orders
Customer
Order
* id places * id
name
date
address
status
balance
processor
an order can have minimum 1 and maximum 1 customer 14
Cardinality constraints
§ we indicate cardinality by annotating the relationship line
a customer can have minimum 0 and maximum many orders
Customer
Order
* id places * id
name
date
address
status
balance
processor
an order can have minimum 1 and maximum 1 customer 15
Crow’s foot diagram
§ This type of diagram is called crow’s foot
§ invented by Gordon Everest
§ there are alternative kinds of ER diagram
that you will see, such as Chen
16
Chen notation Crow’s foot
§ these two diagrams show
the same E-R
§ we will use crow’s foot
address Chen diagram status
date
name balance processor
id
id
1 N
Customer places Order
17
Cardinality constraints
§ now we look at relationship between Order and Product
Order can have minimum 1 and maximum many products
Order
* id Product
has
date * id
status name
processor
Product can have minimum 0 or maximum many orders 18
Cardinality constraints
§ now we look at relationship between Order and Product
Order can have minimum 1 and maximum many products
Order
* id Product
has
date * id
status name
processor
Product can have minimum 0 or maximum many orders 19
Cardinality constraints
§ now relationship between Order and Employee
an order is processed by one employee only (minimum 1, maximum 1)
Order
Employee
* id
processes * id
date
name
status
office
processor
an employee processes 0 or many orders 20
Notation summary
§ 1 and 1 only entity
§ 0 or 1 entity
§ 1 or many entity
read it this way
max min
§ 0 or many entity
21
Notation summary
§ 1 and 1 only entity
minimum cardinality
§ 0 or 1 entity maximum cardinality
§ 1 or many entity
read it this way
max min
§ 0 or many entity
22
Cardinality: exercise
§ how do we decide cardinality?
§ use semantics gathered by requirements analysis
A Trump tweet
B Trump tweet
which is most likely?
C Trump tweet
D Trump tweet
23
Cardinality: exercise
which is most likely?
enrols
A Student Course why not ask,
“which is
B Student Course correct?”
C Student Course
D Student Course
24
Cardinality: exercise
uploads
User YouTube video
What does this cardinality mean?
A A user must upload many videos; a video may belong to one user
B A user may upload many videos; a video must belong to one user
C A user may upload many videos; a video may belong to one user
D A user must upload many videos; a video must belong to one user
25
Cardinality: exercise
subscribes
A User YouTube channel
B User YouTube channel
C User YouTube channel
D User YouTube channel
which is most likely? 26
Cardinality: exercise
has An employee must have a locker; a
Employee Locker locker must belong to an employee
An employee must have a locker; a
Employee Locker locker may belong to an employee
An employee may have a locker; a
Employee Locker locker must belong to an employee
Employee Locker An employee may have a locker; a
locker may belong to an employee
27
Mandatory-mandatory
cardinality
§ a mandatory-mandatory relationship can be a problem. Why?
An employee must have a
Employee Locker locker; a locker must
belong to an employee
§ suppose we have a new employee E
§ to satisfy integrity, when we create E’s record she must already
have a locker allocated
§ if we first try to allocate a locker to E, that fails, because her
record isn’t created yet!
§ can you suggest a solution?
28
Multiple relationships
between entities
§ can have multiple relationships between entity types,
with different semantics
Student President Club
* id * id
name name
email member office
§ here, a club may have a student as its President
§ a student may be a member of the club 29
A more standard notation
§ we separate the attributes from the keys
Order Order
* id
PK id
date date
status status
FK processor
processor
§ but note we are still thinking conceptually
§ so not adding data domains or constraints (keys, ok) 30
Relationships and FKs
§ [Link] is a FK into Employee
Employee Order
PK id PK id
name date
office status
FK processor
31
Weak entity
parent entity P child entity W
Employee FamilyMember
PK id has name
name relationship
office address
§ FamilyMember depends on Employee for its existence
§ FamilyMember is called a WEAK entity
§ Employee is the “parent”, FamilyMember is the “child” 32
Weak entity
parent entity P child entity W
Employee FamilyMember
PK id has name
name relationship
office address
§ a weak entity W is identified by a composite key
§ ([Link], W.some_attribute)
§ in this case: ([Link], [Link]) 33
Entity or attribute?
§ Often not obvious what is the boundary between entity and set of attributes
Employee or this?
PK id Employee Position
has position
name PK id PK type
DOB name office
office DOB title
title salary
salary bonus
bonus this? 34
Entity or attribute?
§ Often not obvious what is the boundary between entity and set of attributes
Employee
or this? Position
PK id
Employee
name has position PK type
DOB PK id office
office name title
title DOB and should the
relationship be salary
salary this? Depends on bonus
bonus this? the requirements
35