CHAPTER 2
ENTITY RELATIONSHIP
MODEL
LESSON LEARNING
OBJECTIVES
(LLO)
At the end of this chapter, students should be
able to :
Define an entity.
Identify the entity types and sets of entity.
Explain the attribute.
Explain E-R model.
Explain the following relationship types in E-
R model:
1:1 (one to one)
1:M (one to many)
M:N (many to many)
Draw E-R model based on a given
DEC5073– DAT ABASE
SYS T E M situation.
INTRODUCTION
E-R model is widely accepted and adapted
graphical
tool for data modeling.
Peter Chen first introduced the E-R
data model in 1976.
The E-R Model yielded a graphical
representation of entities and their
relationships in a database structure.
The E-R model complemented the
relational data model concepts.
DEC5073– DAT ABASE
SYS T EM
DATA MODELING VERSION
BASIC STRUCTURE
E-R Model are normally represented in an
Entity Relationship Diagram (ERD),
which uses graphic representations to
model the database components.
The E-R modeling process identifies three
basic
elements :
ENTITIES
ATTRIBUTES
RELATIONSHIPS
DEC5073– DAT ABASE
SYS T EM
ERD COMPONENTS
Attribut
Entity
e
Cardinalit Relationsh
y ip
ENTITY
ENTITY An object or concept
that can be
uniquely identified.
Usually, a database contain many
different types of entities.
Entities can be classified into
types :
Strong Entity
Weak Entity.
DEC5073– DAT ABASE
SYS T EM
ENTITY
The term entity type refers to a
number of
The termitems.
related Entity = Entity Type.
Entity Instance refers to a single
occurrence of an entity type.
Example : employee no 123-456
Entity Set = Set of all entities of
the same type
DEC5073– DAT ABASE
SYS T EM
ENTITY
STUDENT SARAH EMIR
ID_NO 1234 5678
COURSE ET601 ET501
PROGRAM DTK DET
Entity Entity
type instances
EXAMPLE OF ENTITY
TANGIBLE • Example: Person, Product
• Example: Course Title
CONCEPTUAL
• Example: Delivery, Registration
ACTIVE
• Example: Delivery, Registration
PERMANENT
• Example: Stock
VOLATILE
ENTITY
STRONG ENTITY an entity referred
to as a strong entity if its existence is
not dependent on the existence of
another entity.
Strong entity sometimes referred to as
the parent
entity, owner or dominant.
Each strong entity shown in the shape
Examples : Staff and PAINTER
of a square
Branch
and labeled with the name of the
entity.
DEC5073– DAT ABASE
SYS T EM
ENTITY
WEAK ENTITY weak entity depends on
the
existence of another entity.
They can not exist in the model if a member
does not exist in corresponds to other
entities.
Weak entity represents by a square with
two lines
Weak entities sometimes called child entity,
dependentNEXT_OF_KIN
or subordinate.
Examples : next of kin (staff)
DEC5073– DAT ABASE
SYS T EM
ENTITY
WEAK ENTITY can be identified when one
that meets
this two conditions :
It is existence-dependent it cannot exist without
the entity with which it has a relationship
It has a primary key that is partially or totally
derived from the
parent entity in the relationship.
DEC5073– DAT ABASE
SYS T EM
ENTITY
An Entity is represented in the ERD by a
rectangle, also known as an entity box.
The name of the entity, a noun is written
in the
center of the rectangle.
The entity name is written in CAPITAL
LETTERS and is written in the singular :
PAINTER rather than PAINTERS.
Each entity is described by a set of
attributes that
describe particular characteristics of the
DEC5073– DAT ABASE
SYS T EM entity.
ATTRIBUTE
ATTRIBUTE
facts, property or characteristic of
an entity type (often corresponds to a
All field in a
entity instances oftable)
a given entity set have the
same attributes, but vary in the values of those
attributes. STUDENT SARAH EMIR
ID_NO 1234 5678
Attribute COURSE ET601 ET501
PROGRAM DTK DET
field
***ATTRIBUTE DOMAIN = Possible value of an attribute
ATTRIBUTES
Attributes can be classified
into :
Composite / Simple
(Atomic)
Single Value / Multi
Value
Derived
DEC5073– DAT ABASE
SYS T EM
ATTRIBUTES
COMPOSITE
Attribute that can be further subdivided
to additional attributes
Example : ADDRESS can be
subdivided into street, city, state and zip
code
SIMPLE / ATOMIC
• Attribute that cannot be subdivided.
• Example : AGE, GENDER, MARITAL
STATUS
DEC5073– DAT ABASE
SYS T EM ec601 database system (NCA)
SIMPLE VS COMPOSITE
ATTRIBUTE
No_ID
STUDENT
Name
Program
First Last
Name Name
Composed of 2 subparts
ATTRIBUTES
SINGLE
•VALUE
Attribute that can have only a single value.
• Example : IC, MATRIX NO
MULTI VALUE
• Attribute that can have multi value.
• In Chen Model, multi valued attributes are shown by a
double line oval
•Example : the
connecting CAR
CARattribute to the entity.
cco
COLOR
ollo
DEC5073– DAT ABASE
SYS T EM orr
SINGLE VS MULTIVALUE
ATTRIBUTE
No_ID
STUDENT
Name
Skills
Skill
First Last
Multivalued: Name Name
A student can have
more than one skill
ATTRIBUTES
DERIVED
Attribute whose value may be
calculated from other
attributes.
It need not be physically stored
within the database,instead, it
can be derived by using an
algorithm.
Example : EMP_AGE can
DEC5073– DAT ABASE
SYS T EM
be calculated with EMP_DOB
ATTRIBUTES
TEACHE CIRCLE
TEACHE
R R
ic_n are
subjects radiu
o a
s
Single Multivalu Derive
Value ed d
Attribut Attribut Attribu
e e te
TEACHE
R
addre
ss p_cod
e
stree stat
Compos
t e
ite
DEC5073– DAT ABASE Attribu
SYS T EM te
DOMAINS
Attributes have a domain.
A DOMAIN is the attributes set of
possible values.
Domains for attributes = Value Set
Attributes may share a domain.
Examples :
ATTRIBUTES DOMAIN
GPA (0,4) 0 low, 4 highest
GENDER M or F
DEC5073– DAT ABASE
SYS T EM
PRIMARY KEYS
PRIMARY KEYS (PK) is an attribute
that
uniquely identifies any given entity
(row).
Also known as key attributes.
Cannot contain null entries.
Examples :
Students name would not be a good
primary key because it is possible to
find several students who has a same
name.
DEC5073– DAT ABASE
SYS T EM
PRIMARY KEYS
Primary keys are underlined in the ER
diagram.
Primary keys are also underlined
in a frequently used table
structure using the format :
TABLE NAME (PRIMARY KEY, ATTRIBUTE 1,
ATTRIBUTE 2,
…, ATTRIBUTE K)
STUDENT (IDNO, NAME,
GENDER, AGE)
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIPS
RELATIONSHIPS Describe
associations among data.
Degree of a Relationship the number
of entities involved in the
relationship.
3 Relationship Degrees :
Unary Relationship (Recursive Relationship)
Binary Relationship
Ternary Relationship
N-ary Relationship
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIP NOTATION
No_ID No_ID
LECTURER teach STUDENT
verb
Name Name
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIP NOTATION
No_ID Member of No_Team
STUDENT TEAM
Leader of
Name
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIP NOTATION
PurchaseDate
No_ID No_ID
LECTURER order BOOK
Name Tittle
TotalCost
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIPS DEGREE
UNARY RELATIONSHIP (RECURSIVE)
•A relationship involving a single entity.
BINARY RELATIONSHIP
•A relationship between two entities.
TERNARY RELATIONSHIP
•A relationship between three entities.
N-ARY RELATIONSHIP
•A relationship that involve more than
three entities
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIPS DEGREE
supervisi
on
PROFESSOR CLASS
EMPLOYE teach
es
E
Unary/ Binary
Recursive Relations
Relationsh hip
ip
CONTRIBUTO RECIPIENT
CF
R R
FUN
D
Terna
ry
Relations
DEC5073– DAT ABASE hip
SYS T EM
RELATIONSHIPS STRENGTH
TWO types of
strength :
Strong
Relationship
Weak Relationship
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIPS STRENGTH
STRONG RELATIONSHIP
• Also known as an identifying relationship
• Exists when the related entities are
existence-dependent.
• In database design perspective, a strong
relationship between two entities exists whenever
the primary key of the related entity contains a
primary key component of the parent entity.
• Example :
• COURSE (CRS_CODE, DEPT_CODE, CRS_CREDIT)
• CLASS (CRS_CODE, CLASS_SECTION,
CLASS_TIME)
• #: The CRS_CODE in CLASS is also a foreign key
to the COURSE entity.
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIPS STRENGTH
WEAK RELATIONSHIP
•Also known as an non-identifying relationship
•If one entity is not existence-independent on
another entity, the relationship between them is
described as a weak relationship.
•In database design perspective, a weak relationship
exists if the primary key of the related entity does
not contain a primary key component of the parent
entity.
•Example :
•COURSE (CRS_CODE, DEPT_CODE, CRS_CREDIT)
•CLASS (CLASS_CODE, CRS_CODE, CLASS_TIME)
•#: The CLASS primary key did not inherit the
DEC5073–
primary key component from the COURSE
DAT ABASE
SYS T EM entity.
RELATIONSHIPS
Relationships are represented by a diamond
connected
to
Thethe related
name entities
of the throughan
relationship, a relationship
active or
line.
passive verb, is written inside the
diamond.
Strong Weak
relationsh relationsh
ip ip
DEC5073– DAT ABASE
SYS T EM
RELATIONSHIPS
1 M
PAINTE paint PAINTIN
R s G
The ERD shown is based on the
Chen
Model.
Although the relationships are
shown in a horizontal format, they
also may be oriented in vertically.
DEC5073– DAT ABASE
SYS T EM
CLASSIFICATION OF
RELATIONSHIPS
Optional Relationship
If one entity occurrence does not require a
corresponding entity occurrence in a particular
relationship
The existence of an optional indicates
that the minimum cardinality is 0 for the
optional entity.
An optional relationship between entities is
shown by drawing a small circle (o) on the side
of the optional entity.
Mandatory Relationship
If one entity
cardinality isoccurrence
1 for the requires a
DEC5073– corresponding
optional entity.entity
DAT ABASE
SYS T EM
CLASSIFICATION OF
RELATIONSHIPS
Example :
Optional Relationship
An Employee may or may not be
assigned to a Department
A Patient may or may not be
assigned to a Bed
Mandatory Relationship
Every Course must be taught by
Every
at mother
least have at
one Teacher
least a Child
DEC5073– DAT ABASE
SYS T EM
CLASSIFICATION OF
RELATIONSHIPS
1 M
PROFESS teach CLAS
es
OR S
mandato option
ry al
Example :
Suppose that Tiny College employs some professors who
conduct research without teaching classes. If we examine
the “PROFESSOR teaches CLASS” relationship, it is quit
possible for a PROFESSOR not to teach a CLASS. Therefore,
CLASS is optional to PROFESSOR. On the other hand, a
CLASS must be taught by a PROFESSOR. Therefore
PROFESSOR is mandatory to CLASS.
DEC5073– DAT ABASE
CARDINALITY
Express the number of entities to
which another
entity canConstraints
Cardinality be associated vianumber
- the a of
instances relationship set. can or must be
of one entity that
associated with each instance of another
entity.
Minimum Cardinality
If zero, then optional
If one or more, then mandatory
Maximum Cardinality
The maximum number
DEC5073– DAT ABASE
SYS T EM
CONNECTIVITIES TYPES
1:1 (one to
one)
• In this relationship, one entity
can be
related to only one other entity.
1:M (one to many)
• In this relationship, one entity
can be related to many entity.
M:N (many to many)
• In this relationship, many
entity can be
related to many other entity.
DEC5073– DAT ABASE
ONE TO ONE (1:1)
E
One branch has one manager AND
every manager is appointed to one
branch
Manager_No Branch_No
1Manages 1
MANAGER BRANCH
ONE TO MANY (1:M)
Example:
One publisher publishes any
particular book BUT many books
may be published by a publisher
Publisher_No Book_No
1Publishes N
PUBLISHER BOOK
MANY TO MANY(M:N)
Example:
Every student can take many
subjects AND every subject can
have many students
Student_No Subject_No
MRegister N
STUDENT SUBJECT
STEPS TO CREATE AN ER
DIAGRAM
Identify ENTITY
Identify ATTRIBUTE and PRIMARY KEY
Identify the RELATION between
ENTITIES
DRAW and check the ER diagram
Identify the RELATIONSHIP and CARDINALITY
EXAMPLE
A company has several departments. Each
department has a supervisor and at least
one employee. Employees must be assigned
to at least one, but possibly more departments.
At least one employee is assigned to a project,
but an employee may be on vacation and not
assigned to any projects. The important data
fields are the names of the departments,
projects, supervisors and employees, as well as
the supervisor and employee number and a
unique project number.
DEC5073– DAT ABASE
SYS T EM
Identify entities
One approach to this is to work through the information
and highlight those words which you think
correspond to entities.
A company has several departments. Each department has
a supervisor and at least one employee. Employees
must be assigned to at least one, but possibly more
departments. At least one employee is assigned to a
project, but an employee may be on vacation and
not assigned to any projects. The important data fields
are the names of the departments, projects, supervisors
and employees, as well as the supervisor and
employee number and a unique project number.
DEC5073– DAT ABASE
SYS T EM
Identify entities
DEPARTMENT PROJECT
EMPLOYEE SUPERVISOR
DEC5073– DAT ABASE
SYS T EM
Identify Attributes
DEPARTMENT PROJECT
departmen
t_ project_na project_n
name me o
EMPLOYEE SUPERVISOR
employe employe superviso superviso
e_ e_ no r_ r_ no
name name
DEC5073– DAT ABASE
SYS T EM
Identify Primary
Keys
DEPARTMENT PROJECT
departmen
t_ project_na project_n
name me o
EMPLOYEE SUPERVISOR
employe employe superviso superviso
e_ e_ no r_ r_ no
name name
ec601 database system
Identified
Relationships
Names placed in the cells are meant to
capture/describe the relationships. So you
can use them like this
A Department is assigned by an
employee
A Department is run by a supervisor
An employee works on a project
A supervisor runs a department
DEC5073– DAT ABASE
SYS T EM
Draw Rough ERD
Draw a diagram and:
Place all the entities in
rectangles
Use diamonds and lines to
represent the relationships
between entities.
DEC5073– DAT ABASE
SYS T EM
Drawing Rough ERD
(Contd.)
EMPLOYE
E
work
s
PROJEC
T
on
DEPARTME run SUPERVIS
NT by OR
is
DEPARTME EMPLOYE
assig
NT E
n
DEC5073– DAT ABASE
SYS T EM
Drawing Rough ERD
(Contd.)
DEPARTMENT
run SUPERVIS
by OR
is
assig
n
EMPLOYEE work
PROJECT
s
S
on
DEC5073– DAT ABASE
SYS T EM
Identify Cardinality
Supervisor
Each department has one supervisor.
Department
Each supervisor has one department.
Each employee can belong to one or more
departments
Employee
Each department must have one or more
employees
Each project must have one or more
employees
Project
DEC5073– DAT ABASE
SYS T EM
ERD with cardinality
DEPARTMENT
(1,1) run SUPERVIS
(1,
(1,N) by OR
1)
is
assig
n
(1,N)
EMPLOYEE (1,N) work (0,N
) PROJECT
s
S
on
DEC5073– DAT ABASE
SYS T EM
ERD Diagram
DEPARTME run SUPERVIS
(1, (1,
NT by OR
(1, 1) 1)
N) Departme Supervis Supervis
nt nam or or no
is e name
assig
n
(1,N)
EMPLOYEE (1,N) work (0,N
) PROJECT
s
S
on
employe Employ project Project
e_ ee n _ no
name o name
DEC5073– DAT ABASE
SYS T EM
SUMMARY of ER
DIAGRAM NOTATION
SYMB MEANING
OL
ENTITY/
ENTITY TYPE
WEAK ENTITY
TYPE
RELATIONSHIP
TYPE
WEAK RELATIONSHIP
TYPE
DEC5073– DAT ABASE
SYS T EM
SUMMARY of ER
DIAGRAM NOTATION
SYMB MEANI
OL NG
ATTRIBU
TE
KEY ATTRIBUTE
MULTIVALUED
ATTRIBUTE
DERIVED ATTRIBUTE
COMPOSITE
ATTRIBUTE
DEC5073– DAT ABASE
