Chapter 2A.

Entity- Relationship
Model
COMP3278 Introduction to
Database Management Systems

Department of Computer Science, The University of Hong Kong

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278
For other uses, please email : [email protected]
Outcome based learning (OBL)

Outcome 1. Information Modeling

Able to understand the modeling of real life information in a database
system.

Outcome 2. Query Languages

Able to understand and use the languages designed for data access.

Outcome 3. System Design

Able to understand the design of an efficient and reliable database
system.

Outcome 4. Application Development

Able to implement a practical application on a real database.
2
We are going to learn…
E-R Diagram basics

More on E-R Diagram

3
Section 2A.1

E-R Diagram Basics

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278

For other uses, please email : [email protected]
E-R Diagram basics
1. Entity and Entity set
2. Relationship and Relationship set
3. Constraints
4. Keys

5
Entity and Entity set
Entity
An object that exists and is distinguishable from other objects.
E.g., A customer, an account, a department, etc.

Entities have Attributes

People have names and address.
Entity set
A set of entities of the same type that share the same
properties (attributes).
E.g., a set of all customers, all saving accounts, all
departments in the company, etc.
6
Entity and Entity set
In the E-R Diagram
Rectangles – entity sets.
Ellipses – attributes.
Line between a rectangle and an ellipse – link
between an attribute and an entity set.

customer_id account_
balance

customer account

name address account_id 7

Relationship and Relationship set

A relationship is an association among entities.

E.g., the relationship between the customers and the
accounts.
A relationship set is a set of relationships of the
same type.

8
Relationship and Relationship set
In the E-R Diagram
Diamonds – relationship sets

What are the constraints in

specifying the relationship between
two entity sets?

customer_id account_
balance

name
customer

address
? owner
? account

account_id 9
Constraints
Mapping cardinalities
Concerns the number of entities to which another
entity can be associated via a relationship set.
E.g., For each customer, how many accounts
he/she can have? One or more than one?
Participation constraints
Concerns whether all entities in the entity set have
to participate in the relationship set.
E.g., Whether a customer must have an account
record, or there can be some customers without
any accounts?
10
Mapping cardinalities
We express cardinality constraints by drawing either a
directed line (→), signifying “one,” or an undirected
line (—), signifying “many,” between the relationship
set and the entity set.

account_
customer_id
balance

customer ? owner ? account

name address account_id

11
Mapping cardinalities
A customer can have at most one account.

account_
customer_id
balance

customer owner account

name address account_id

12
Mapping cardinalities
A customer can have more than one accounts.

account_
customer_id
balance

customer owner account

name address account_id

13
Mapping cardinalities
An account can be associated with at most one
customer.

account_
customer_id
balance

customer owner account

name address account_id

14
Mapping cardinalities
An account can be associated with more than one
customers.

account_
customer_id
balance

customer owner account

name address account_id

15
Mapping cardinalities
Please build a system to store the customer and account
information of our bank. For each customer, we record
his/her customer ID, name and address; for each
account, we record its account ID and account balance.
Each customer can have one or more accounts, and
each account has to be owned by only one customer.

Step1. Identify the Entity sets.

customer account

16
Mapping cardinalities
Please build a system to store the customer and account
information of our bank. For each customer, we record
his/her customer ID, name and address; for each
account, we record its account ID and account balance.
Each customer can have one or more accounts, and
each account has to be owned by only one customer.

Step2. Identify the Relationship sets.

customer owner account

17
Mapping cardinalities
Please build a system to store the customer and account
information of our bank. For each customer, we record
his/her customer ID, name and address; for each
account, we record its account ID and account balance.
Each customer can have one or more accounts, and
each account has to be owned by only one customer.

account_
customer_id Step3. Identify the attributes. balance

customer owner account

name address account_id

18
Mapping cardinalities
Please build a system to store the customer and account
information of our bank. For each customer, we record
his/her customer ID, name and address; for each
account, we record its account ID and account balance.
Each customer can have one or more accounts, and
each account has to be owned by only one customer.

account_
customer_id Step4. Mapping cardinalities. balance

customer owner account

name address account_id

Note: this ER-Diagram is incomplete! Some more steps in the

next few slides including the participation, primary keys …etc 19
Mapping cardinalities
Different mapping relationships:
Many to many.
a b

One to many (from a to b).

a b

Many to one (from a to b).

a b

One to one.
a b
20
Participation constraints
Concerns whether all entities in the entity set have to
participate in the relationship.
Total participation (indicated by double line): every
entity in the entity set participates in at least one
relationship in the relationship set.

Partial participation (indicated by single line): some

entity may not participate in any relationship in the
relationship set.

21
Participation constraints
Each customer must have an account.
Total participation: All customers must participate in
the owner relationship.

account_
customer_id
balance

customer owner account

name address account_id

22
Participation constraints
Not all customers are required to have an account.
Partial participation: Not all customers participate in
the owner relationship.

account_
customer_id
balance

customer owner account

name address account_id

23
Participation constraints
Each account must be owned by customers.
Total participation: All accounts participate in the
owner relationship.

account_
customer_id
balance

customer owner account

name address account_id

24
Exercise

Student takes Course

Can you understand the data model

captured by this E-R Diagram?
Exercise
Mapping cardinality
Arrow head (One)
Each student takes one course only.

Student takes Course

26
Exercise
Mapping cardinality
No arrow head (Many) Arrow head (One)
Each course can be taken by many students. Each student takes one course only.

Student takes Course

27
Exercise
Mapping cardinality
No arrow head (Many) Arrow head (One)
Each course can be taken by many students. Each student takes one course only.

Student takes Course

Total / partial participation

Full participation (double line)
Each entity in the “Student” entity set
must participate in this relationship.
(i.e. Each student must take course.)
28
Exercise
Mapping cardinality
No arrow head (Many) Arrow head (One)
Each course can be taken by many students. Each student takes one course only.

Student takes Course

Total / partial participation

Full participation (double line) Full participation (double line)
Each entity in the “Student” entity set Each entity in the “Course” entity set
must participate in this relationship. must participate in this relationship
(i.e. Each student must take course.) (i.e. Each course must be taken by some
student.) 29
Practical issues
As a professional DB designer, you have to be able to:
Understand and model the data of an application
using a E-R diagram.
Interact with the client to work out a clear problem
definition.
Realize the missing information and ask your client
for clarification.
Provide professional suggestions to better design the
database for the specific application.

30
Practical issues
Please build a system to store the customer and account
information of our bank. For each customer, we record
his/her customer ID, name and address; for each account,
we record its account ID and account balance.

Do we have enough information to

model the data of this application?
What questions should I ask?
account_
customer_id
balance

customer
? owner
? account

name address account_id

31
Keys
Super key

Candidate keys
Primary key

32
Super key
A super key of an entity set is a set of one or more
attributes whose values uniquely determine each
entity.
No two entities have exactly the same values in super
key.
In this example, if each customer has his/her
unique customer_id, then
{customer_id} is a super key.
customer_id {customer_id, name} is another super key.
{customer_id, address} is another super key.
{customer_id, name, address} is also a super key.
customer

name address
33
Candidate key
A candidate key of an entity set is a minimal super
key.
Minimal – no redundant attributes, i.e., no subset of
a candidate key is still a key.
Although the following are super keys:
{customer_id}, { customer_id, name},
{customer_id, address},
customer_id {customer_id, name, address}
Only the {customer_id} is a candidate
customer key because it is minimal.

name address Question:

Can it be more than one candidate key?
34
Primary key
Although several candidate keys may exist, one of the
candidate keys is selected to be the primary key.
In the E-R Diagram
Underline the attribute – The attribute is a primary
key of the entity.

account_
customer_id
balance

customer account

name address account_id

35
Section 2A.2

More on E-R Diagram

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278

For other uses, please email : [email protected]
More on E-R Diagram
Different attribute types

Weak entity set

Role

Specialization and generalization

37
Different attribute types
Single v.s. Composite attributes
Single attribute customer Composite attributes

customer_id name address

first middle last street city state

Component attributes

street-number Street-name apartment-number

38
Different attribute types
Single-valued v.s. Multi-valued attributes
Multi-valued attributes are represented by double ellipses
in the E-R Diagram.
customer Multi-valued attribute

customer_id name phone

Single-valued attributes customer_id name phone

6012 3456,
1 Kit 9888 8888,
2857 8435
6987 6543,
2 Yvonne
2859 1104
9876 1234,
3 Jolly 39
2857 8434
Different attribute types
Derived attribute
Values in this attribute can be derived from other
attributes.
Derived attributes are represented by dashed ellipses in
the E-R Diagram.
Since “age” can be derived from the “date of
birth”, we treat “age” as a derived attribute,
and use a dashed ellipse to represent it in
customer
the E-R Diagram.

customer_id Age date_of_birth

Derived attribute 40
Weak Entity Set
An entity set that does not have a primary key is
referred to as a weak entity set.
The existence of a weak entity set depends on the
existence of an identifying entity set.
We need the identifying entity set to help to uniquely
identify the entities in the weak entity set.

Create a database for storing

the NBA teams and the NBA
players…
41
Weak Entity Set

team player

1. Identify Entity sets:

We have to store the “NBA Teams”
and “NBA Players”.

Create a database for storing

the NBA teams and the NBA
players…
42
Weak Entity Set

team belongs player

2. Identify relationship set:

Each player belongs to a team.

Create a database for storing

the NBA teams and the NBA
players…
43
Weak Entity Set
team_name player_number player_name

team belongs player

3. Identify the attributes of the entity sets:

For each NBA team, we store the team’s
name; for each player, we store his number
and name.

Create a database for storing

the NBA teams and the NBA
players…
44
Weak Entity Set
team_name player_number player_name

team belongs player

4. Identify the mapping cardinalities:

A team can have more than one players.
A player can belongs to only one team.

Create a database for storing

the NBA teams and the NBA
players…
45
Weak Entity Set
team_name player_number player_name

team belongs player

5. Identify the total/ partial participation:

A team must have some players.
A player must belong to a team.

Create a database for storing

the NBA teams and the NBA
players…
46
Weak Entity Set
team_name player_number player_name

team belongs player

6. Identify the primary key of the entity sets:

The team name can uniquely identify a team.
Problem: The player_number cannot uniquely
identify a player!
E.g., Michael Jordan and LeBron James were both
#23!
47
Weak Entity Set
Weak entity set

team_name player_number player_name

team belongs player

An entity set that does not have a primary key is

referred to as a weak entity set.
We depict a weak entity set by a double rectangle.

48
Weak Entity Set
Identifying entity set Identifying relationship set Weak entity set

team_name player_number player_name

team belongs player

The existence of a weak entity set depends on the

existence of an identifying entity set.
The weak entity set must relate to its identifying entity set
via a total, many-to-one identifying relationship set from
the weak entity set to the identifying entity set.
We depict an identifying relationship set as a double
diamond. 49
Weak Entity Set Discriminator

Identifying entity set Identifying relationship set Weak entity set

team_name player_number player_name

team belongs player

The discriminator (or, partial key) of a weak entity set

is a set of attributes that distinguish among the weak
entities that depend on the same identifying entity.
The primary key of a weak entity set is formed by the
primary key of the identifying entity set plus the weak
entity set’s discriminator.
50
Role
Entity sets of a relationship need not be distinct.
emplyee_id
manager

employee works-for
worker

emplyee_name phone_number

The label “manager” and “worker” are called roles. They

specify how employee entities interact via the “works-for”
relationship set.
51
Role
Entity sets of a relationship need not be distinct.
emplyee_id
manager

employee works-for
worker

emplyee_name phone_number

Cardinality
An employee (worker) works for one manager.
An employee (manager) can have more than one workers
work for him/her.
52
Role
Entity sets of a relationship need not be distinct.
emplyee_id
manager

employee works-for
worker

emplyee_name phone_number

Participation
An employee (worker) may not work for any manager.
An employee (manager) can have no workers work for
him/her.
53
Specialization and Generalization

Specialization
We designate sub-groupings within an entity set that are
distinctive from other entities in the set.
A lower-level entity set inherits all
attributes and relationship set MSc student
participation of the higher-level
entity set to which it is linked.
Lower-level entity set can have its
own attributes. ISA

Full-time Part-time
student student
54
Specialization and Generalization

Total or partial
Specifies whether an entity in the higher level-entity set
must belong to at least one of the lower-level entity sets
within a specialization.
Total specialization:
MSc student
An MSc student MUST BE either
a full-time student or a part-
time student, so all MSc
students must participate in this ISA
specialization.
Full-time Part-time
student student
55
Specialization and Generalization

Total or partial
Specifies whether an entity in the higher level-entity set
must belong to at least one of the lower-level entity sets
within a specialization.

Partial specialization: book

A book may not be specialized to
story book or comics, so not all
books are participating in this
ISA
specialization.

story_book comic

56
Specialization and Generalization

Disjoint or overlapping
Constraints on whether entities may belong to more than
one lower-level entity set within a single specialization.
Disjoint specialization:
MSc student
An MSc student must be either a
full-time student or a part-time
student, so the specialization is
disjoint. We use a keyword ISA Disjoint
“Disjoint” to indicate it.

Full-time Part-time
student student
57
Specialization and Generalization

Disjoint or overlapping
Constraints on whether entities may belong to more than
one lower-level entity set within a single specialization.

Overlapping specialization: book

A book can be both a story book and
comic, so the specialization is
overlapping.
We do not need to specify anything, as ISA
overlapping specialization is the default.

story_book comic

58
Chapter 2A.

END
COMP3278 Introduction to
Database Management Systems

Slides prepared by - Dr. Chui Chun Kit for students in COMP3278

For other uses, please email : [email protected]