ER Diagram

Chapter 4 ERD 1
 Learning Outcome
Successful students will be able to:
 Understand ER diagrams
 Use ER diagrams to model a database
system

Chapter 4 ERD 2
Chapter 4 ERD 3
 Entity Relationship (ER) Diagrams
 Entity Relationship Diagrams are a major
data modeling tool and represents the
conceptual database as viewed by the end
user.
 ERDs depict the database’s main
components: entities, attributes and
relationships.

Chapter 4 ERD 4
 ER Model
 Entities
 Represent person, place, object, event, concept (often
corresponds to a row in a table)
 The entity name, a noun is usually written in all capital letters.
 For example, STUDENT, COURSE, CUSTOMER

 Attributes
 property or characteristic of an entity type (often corresponds
to a field in a table)
 there are two model use to represent attribute in ER diagram,
Chen and Crow’s Foot model
 because Chen take more space, software vendors have used
Crow’s Foot model
 For example: STUDENT (STUDID, SNAME, SADDRESS)

Chapter 4 ERD 5
We will try to model the ERD using both methods.

Chapter 4 ERD 6
 Domains and Identifiers (Primary
keys)
 Domain– is the set of possible values for a
given attribute.
 For example, domain for gender is F/M; the domain
for GPA is written (0,4)
 Identifiers – one or more attributes that
uniquely identify each entity instance.
 In relational model, entities are mapped to tables,
entity identifier is mapped as the table’s primary key.
 In relational schema / table structure, primary key is
underline:
 For example:
 CAR (CAR_ID, MOD_CODE, CAR_YEAR)

Chapter 4 ERD 7
 Identifiers (Primary Keys)
 Characteristics
 Will not change in value & will not be null
 No intelligent identifiers (e.g. containing
locations or people that might change)
 Substitute new, simple keys for long,
composite keys

Chapter 4 ERD 8
 Composite identifiers
 It is where it have more than one
attribute act as primary key in a
relation.
 For example:
 CLASS (CRS_CODE, CLASS_SECTION,
CLASS_TIME, ROOM_CODE)

Chapter 4 ERD 9
Sample ER Diagram

Chapter 4 ERD 10
Basic ER Notation

Chapter 4 ERD 11
Attributes

 Property or characteristic of an entity

type
 Classifications of attributes:
 Simple Attribute – attribute can not be
breaking down, is atomic
 Composite Attribute
 Multi valued Attribute
 Derived Attributes
 Identifier Attributes

Chapter 4 ERD 12
 Required attribute and optional
attributes
 Required attribute is an attribute
that must have a value: it can not left
empty.
 For example: name
 Optional attribute – is an attribute
that does not require a value; it can be
left empty
 For example: phone number or email

Chapter 4 ERD 13
Different types of attributes
 Simple attribute
 Key attribute / primary key
 Normally underline
 Multi-valued
 Composite attributes
 Derived attribute

Chapter 4 ERD 14
 Simple Attribute – is an attribute where it has only
one value.

The key is
underlined

Chapter 4 ERD 15
 Composite Attribute
An
attribute
where it
can be
broken into
few
attributes

Chapter 4 ERD 16
Multi valued Attribute – is an attribute
where it has more than one value

Multivalued:
an employee can have
more than one skill

Chapter 4 ERD 17
Derived Attribute is an attribute which
is calculated from other attribute

Derived
from date employed and
current date

Chapter 4 ERD 18
 Relationships
 Relationship is association between entities.
 The relationship name is an active / passive verb.
 For examples:
 a STUDENT takes a class
 a PROFESSOR teaches a CLASS
 a DEPARTMENT employs a PROFESSOR
 a DIVISION is managed by an EMPLOYEE
 an AIRCRAFT is flown by a CREW
 Relationships between entities always operate in
both directions:
 A CUSTOMER may generate many INVOICES
 Each INVOICE is generated by one CUSTOMER
Chapter 4 ERD 19
 Degree of Relationships
 Degree of a Relationship is the number of entity
types that participate in the relationship.

 3 types of relationship
1. Unary Relationship (one entity)
2. Binary Relationship (two entities)
3. Ternary Relationship (three entities)
4. N’ary relationship

 Binary relationship is the most common

Chapter 4 ERD 20
 Degree of Relationships

One entity Entities of

related to two different Entities of three
another of types related different types
the same to each other related to each
entity type other
Chapter 4 ERD 21
Unary Relationships (recursive)

Chapter 4 ERD 22
Binary Relationships

Chapter 4 ERD 23
 Ternary Relationships
 Tenary: 3 entities are required in this
relationship
 Example:
 A technician uses notebook in a project
 Notebook and project are act as a combination
always stay together
 Any of these 3 entities

has to participate the

relationship

Chapter 4 ERD 24
 N’ary Relationship Example
 4 entities
 A physician operates on a patient, with certain
nurses and supplies participating in this
operation at the same time

Chapter 4 ERD 25
 Connectivity … (1)
 The connectivity of a relationship is its
classification.
 It may be a one to one (1:1), one to many
(1:M) or many to many (M:N) relationship. 
 A relationships connectivity is represented by
a 1, M or N next to the related entity.

Chapter 4 ERD 26
Connectivity … (2)

Chapter 4 ERD 27
 1) One-to-many (1:M or 1..*) relationship

 This relationship should be norm in any

relational database design
 It is easily implemented in the relational model
by putting the primary key of the “1” side in the
table of the “many” side as a foreign key.
 For examples:
 Each painting was created by one and only one painter
but each painter could have created many paintings.
 Each COURSE can have many CLASSes, but each
CLASS references to only one COURSE

Chapter 3 Relational Model 28

Chapter 3 Relational Model 29
Chapter 3 Relational Model 30
 2) One-to-one (1:1 or 1..1) relationship
 This relationship should be rare in database design.
 Can be related to only 1 other entity and vice versa.
 For examples:
 One department chair – a professor – can chair one
department and one department can have only one
department chair
 A retail company’s management structure require each
of the stores be managed by a single employee. In turn,
each store manager who is an employee, manages only
1 single store.

Chapter 3 Relational Model 31

Chapter 3 Relational Model 32
 3) Many-to-many (M:N or *..*)
relationship
 This relationship is not supported directly in the
relational environment
 However, this can be implemented by creating a
new entity in 1:M relationships with the original
entities.
 For examples:
 An employee may learn many job skills and each job
skill may be learned by many employees.
 A student can take many classes, and each class can be
taken by many students.

Chapter 3 Relational Model 33

Chapter 3 Relational Model 34
It will be wrongly assume that this relationship can be implemented by
Adding foreign key in the “many” side of the relationship that points to
The primary key of the related table, as the above.

This relationship can not be implemented because of 2 reasons:

Chapter 3 Relational Model 35
 2 reasons why M:N relationship can not
be implemented
1. the tables create many redundancies.
 The values in each of the tables may occur many
times in the table
2. Given the structure and contents of the two
tables, the relational operations become very
complex and are likely lead to system
efficiency errors and output errors.

Chapter 3 Relational Model 36

 How to solve this ?
 Creating a composite entity / bridge entity / associative
entity.
 This table is use to link the tables that were originally
related in an M:N relationship, the composite entity
structure includes – as foreign keys – at least the
primary keys of the tables that are to be linked.

Chapter 3 Relational Model 37

Chapter 3 Relational Model 38
 Composite table: ENROLL
 ENROLL table’s primary key is the combination
of its foreign keys CLASS_CODE and STU_NUM
 However, the designer can decide to have any
number of attributes in the composite table.
 This table is created just to eliminate the
potential for multiple redundancies in the
original M:N relationship

Chapter 3 Relational Model 39

 Cardinality … (1)
 Cardinality expresses the minimum and
maximum number of entity occurrences associated
with one occurrence of the related entity.
 Knowing the minimum and maximum number of
entity occurrences is very useful at the application
software level.
 For example, Tiny College might want to ensure
that a class is not taught unless it has at least 10
students enrolled.
 Similarly, if the classroom can hold only 30 students,
the application software should use the cardinality to
limit the enrollment in the class.

Chapter 4 ERD 40
Cardinality … (2)

Cardinality

Chapter 4 ERD 41
 Cardinality Constraints
 Cardinality Constraints - the number of
instances of one entity that can or must be
associated with each instance of another
entity.
 Example (Minimum, Maximum)
 Minimum Cardinality
 If zero, then optional
 If one or more, then mandatory
 Maximum Cardinality: The maximum number

Chapter 4 ERD 42
 Existence Dependence
 An entity is said to be existence-dependent if
one entity cannot exist without the existence of
some other related entity. 
 For example:
 If an employee want to claim one or more dependents for
income tax purpose, the relationship “EMPLOYEE claims
DEPENDENT” – the DEPENDENT entity is clearly existence-
dependence on the EMPLOYEE entity because it is
impossible for the dependent to exist without the EMPLOYEE
in the database
 A Subject being offered at the University, must have the
relevant Offering details associated with it, so OFFERING is
existence-dependent on SUBJECT. 

Chapter 4 ERD 43
Existence dependence

Employee claim dependent

University offer offering

Chapter 4 ERD 44
 Weak entity … (1)
 Weak entity is an entity which is existence
dependence – which mean this entity can not
exist without another entity.
 And the primary key of this weak entity is getting from
the entity that depends on.

 The following example is not weak entity, because the

offering has its own primary key (offering number)
 A Subject being offered at the University, must have the
relevant Offering details associated with it, so OFFERING is
existence-dependent on SUBJECT. 

Chapter 4 ERD 45
 Weak Entity … (2)
 A weak entity is the one that meets two
conditions:
 The entity is existence-dependent; it can not
exist without the entity with which it has a
relationship
 The entity has a primary key that is partially
or totally derived from the parent entity in the
relationship
 Related to foreign key constraint

Chapter 4 ERD 46
Weak entity … (3)

Chapter 4 ERD 47
Weak entity

Chapter 4 ERD 48
Chapter 4 ERD 49
 Strong (Identifying) relationship
 Exists when the primary key of the
related entity contains a primary key
component of the parent entity.
 For example:
 Suppose the 1:M relationship between
COURSE and CLASS is defined as
 COURSE (CRS_CODE, DEPT_CODE,
CRS_DESCRIPTION, CRS_CREDIT)
 CLASS (CRS_CODE, CLASS_SECTION, CLASS_TIME,
ROOM_CODE, PROF_NUM)

Chapter 4 ERD 50
STRONG RELATIONSHIP

Chapter 4 ERD 51
Weak relationship (non-identifying)
 Exists when the primary key of
the related entity is NOT the
primary key component of the
parent entity.

Chapter 4 ERD 52
Chapter 4 ERD 53
RELATIONAL MODEL VS. ER MODEL

 ER model is a type of conceptual

model, which only capture the essential
information from the ‘real world’
without considering the implementation
constraints.
 Relational model is a type of logical
model, which takes the implementation
constraints into considerations.

Chapter 3 Relational Model 54

RELATIONAL MODEL VS. ER MODEL
 An ER model cannot be implemented
directly into a database, but a
relational model can be implemented
in a database physically.
 However, ER model can be converted
into a relational model for physical
implementation.

Chapter 3 Relational Model 55

Example of ER model – Chen
model

Chapter 3 Relational Model 56

Example of ER model – Crow’s
Foot model

Chapter 3 Relational Model 57

ER MODEL TO RELATIONAL MODEL
( Entity Type – Relation )

Sid Name ICno Gender

Student
Sid
Name
ICno
Gender

Student (Sid, Name, ICno, Gender)

Chapter 3 Relational Model 58

THE END

Continue… Drawing ERD diagram

Chapter 4 ERD 59