LECTURE 2:

ENTITY – RELATIONSHIP (E-R)

DIAGRAM

1
 Overview
SDLC phases
Planning Analysis Design Implementation Maintenance

Logical Physical Data

Database Database Database and database
database database
fundamentals analysis implementation administration
design design and security

• database • entity- • relational data • SQL • SQL • SQL

environment relationship model • technical • PL/SQL • data security
and (ER) diagram • transforming specifications • database backup
development • enhanced ER ER diagram of the database and recovery
process diagram into relations
• normalization

2
Entity-Relationship (ER) Diagram
Business Rules
Each department must have many staff.
Each staff may belong to one department.

ER Diagram
DEPARTMENT has STAFF
D_ID S_ID
D_Name S_Name

ER Diagram is a graphical representation of business rules

3
Business Rules
• Define or constrain some aspect of the business
• Names and definitions of entity types, attributes and relationships
• Constraints on above data objects

• Express in terms familiar to end users

Constraint
imposed

Every student in the university must have a faculty advisor

Entity name Entity name

4
Represent business rules by ERD
Business rules

Every student in the university must have a faculty advisor

When the business rule is represented by ERD, it becomes…

STUDENT has FACULTY_ADVISOR

5
ERD Components – Entities and Attributes

Entity Strong Weak

ENTITY NAME
Identifier
Attributes Partial identifier
Simple attribute
[Derived]
{Multivalued}
Composite (1, 2, 3)

6
ERD Components - Relationships
Binary Ternary
Unary
Relationship
Binary

Relationship
Cardinality
Mandatory one Mandatory many Optional one Optional many

Associative
Entity Type Associative

7
What Should An Entity Be?

8
 What Should An Entity Be?

Student Information System

Relevant to the business
STUDENT TREE

9
 What Should An Entity Be?

Student Information System

Relevant to the business

Multiple Instances – STUDENT MARY CHAN

Should not be a single user!

10
 What Should An Entity Be?
Student Information System

STUDENT ID: 001

Relevant to the business ID Name: Mary Chan
Name Email: mchan@ust.hk
Email Gender: F
Multiple Instances – Gender
Should not be a single user! ID: 002
Name: Peter Wong
Email: pwong@ust.hk
Multiple Attributes Gender: M

11
 What Should An Entity Be?
TRANSCRIPT
Student_Name
Transcript is NOT entity.
Course_Name
Relevant to the business Semester It is just an output
Grade generated from other
entities !!
Multiple Instances –
Should not be a single user! Student Information System

STUDENT ENROLLMENT COURSE

Multiple Attributes ID ID ID
Name STU_ID Name
Email COU_ID Instructor
Not an output of the database Gender Semester Quota
system Grade

12
Practice: A database analyst identified some entities of
an accounting system, which entities are invalid?

PETER LEE, EXPENSE

receives
THE TREASURER REPORT

manages summarizes

is charged
ACCOUNT EXPENSE

13
 Report
A single person generated from
“EXPENSE”

PETER LEE, EXPENSE

receives
THE TREASURER REPORT

manages summarizes

is charged
ACCOUNT EXPENSE

14
ERD EXAMPLE OF A BUSINESS

15
 ERD of a Startup Travel Agency – 1st Year
Entity Type: DEPARTMENT STAFF
D_ID has S_ID
D_Name S_Name

001 Peter Li
Entity Instance: A Development 002 Mary Chan
003 Anna Lee
B Marketing 004 Tom Lee
005 John Lam

16
 ERD of the Travel Agency – 2nd Year
STAFF
DEPARMENT
S_ID
D_ID has S_Name
D_Name

is entitled to

Entity Instance:
BENEFIT 001 MTR
B_CODE
002 MTR & Taxi
B_DESC
003 MTR & Taxi & Air

17
 ERD of the Travel Agency – 3rd Year
001 Peter Li
DEPARMENT STAFF 003 Anna Lee
D_ID S_ID
has
D_Name S_Name

has
is entitled to

001 Peter’s sons

Bosco Li 27-APR-93
BENEFIT FAMILY Tommy Li 16-SEP-98
B_CODE is offered to Name
B_DESC Birthdate 003 Anna’s husband
Bosco Li 5-JAN-70

18
 ERD of the Travel Agency – 3rd Year
001 Peter Li
DEPARMENT STAFF 003 Anna Lee
D_ID S_ID
has
D_Name S_Name
identifying
relationship

has
is entitled to

001 Peter’s sons

Bosco Li 27-APR-93
BENEFIT FAMILY Tommy Li 16-SEP-98
B_CODE is offered to Name
B_DESC Birthdate 003 Anna’s husband
Bosco Li 5-JAN-70

19
 ERD of the Travel Agency – 3rd Year
001 Peter Li
DEPARMENT STAFF 003 Anna Lee
D_ID S_ID
has
D_Name S_Name
identifying
relationship

has
is entitled to

001 Peter’s sons

Bosco Li 27-APR-93
BENEFIT FAMILY Tommy Li 16-SEP-98
B_CODE is offered to Name
B_DESC Birthdate 003 Anna’s husband
Bosco Li 5-JAN-70

20
 ERD of the Travel Agency – 3rd Year
001 Peter Li
DEPARMENT STAFF 003 Anna Lee
D_ID S_ID
has
D_Name S_Name
identifying
relationship

has
is entitled to

001 Peter’s sons

Bosco Li 27-APR-93
BENEFIT FAMILY Tommy Li 16-SEP-98
B_CODE is offered to Name
B_DESC Birthdate 003 Anna’s husband
Bosco Li 5-JAN-70

21
 ERD of the Travel Agency – 3rd Year
001 Peter Li
DEPARMENT STAFF 003 Anna Lee
D_ID S_ID
has
D_Name S_Name
identifying
relationship

has
is entitled to

001 Peter’s sons

Bosco Li 27-APR-93
BENEFIT FAMILY Tommy Li 16-SEP-98
B_CODE is offered to Name
B_DESC Birthdate 003 Anna’s husband
Bosco Li 5-JAN-70

22
Strong vs. Weak Entity Type
DEPARMENT BENEFIT STAFF FAMILY
has
D_ID B_CODE S_ID Name
D_Name B_DESC S_Name Birthdate

Strong Entity Weak Entity

• Exists independently of other types of •
entities
• Has its own unique identifier

23
Strong vs. Weak Entity Type
DEPARMENT BENEFIT STAFF FAMILY
has
D_ID B_CODE S_ID Name
D_Name B_DESC S_Name Birthdate

Strong Entity Weak Entity

• Exists independently of other types of • Dependent on a strong entity, cannot
entities exist on its own
• Has its own unique identifier • Does not have a unique identifier but
has a partial identifier

24
Strong vs. Weak Entity Type
DEPARMENT BENEFIT STAFF FAMILY
has
D_ID B_CODE S_ID Name
D_Name B_DESC S_Name Birthdate

Strong Entity Weak Entity

• Exists independently of other types of • Dependent on a strong entity, cannot
entities exist on its own
• Has its own unique identifier • Does not have a unique identifier but
has a partial identifier

• Connects to the depending strong

entity by identifying relationship, which
is represented by double lines
• Entity box and partial identifier have
double lines

25
DIFFERENT TYPES OF ATTRIBUTES

26
Attributes
• Property or characteristic of an entity type
• Classifications of attributes
• Simple attributes ENTITY NAME

• Composite attributes
Identifier
Simple attribute

• Multivalued attributes
Composite (1, 2, 3)
{Multivalued}

• Derived attributes
[Derived]

• Identifier attributes

27
 Example of Employee entity

EMPLOYEE
Employee_ID

Date Employed

Employee_Name
(First Name, Last Name)

Employee Address
(Street Address, City,
State, Postal Code)

{Skills}

[Years Employed]

28
 Example of Employee entity

EMPLOYEE
Employee_ID
Identifier attribute

Date Employed

Employee_Name Simple attribute

(First Name, Last Name)

Employee Address
(Street Address, City,
State, Postal Code)

{Skills}

[Years Employed]

29
 Example of Employee entity

EMPLOYEE
Employee_ID

Date Employed
Composite attribute
Employee_Name
(First Name, Last Name)

Employee Address
(Street Address, City,
Component attributes
State, Postal Code)

{Skills}

[Years Employed]

30
 Example of Employee entity

EMPLOYEE
Employee_ID

Date Employed

Employee_Name
(First Name, Last Name)
Composite attribute
Employee Address
(Street Address, City,
State, Postal Code)

{Skills} Component attributes

[Years Employed]

31
 Example of Employee entity

EMPLOYEE
Employee_ID

Date Employed

Employee_Name
(First Name, Last Name)

Employee Address
(Street Address, City,
State, Postal Code)
Multivalued attribute
{Skills}

[Years Employed]

32
 Example of Employee entity

EMPLOYEE
Employee_ID

Date Employed

Employee_Name
(First Name, Last Name)

Employee Address
(Street Address, City, Years Employed = (Current Date - Date Employed)/365.25
State, Postal Code)

{Skills}
Derived attribute
[Years Employed]

33
Identifier Attribute/Key

• An attribute that uniquely identifies individual instances of an entity type

• Simple Identifier: A simple attribute
• Composite Identifier: A composite attribute

34
1. Simple identifier

STUDENT Examples of Student_ID

Student_ID • 20090151
Student_Name • 20581132
…
…

35
1. Simple identifier

STUDENT Examples of Student_ID

Student_ID • 20090151
Student_Name • 20581132
…
…

2. Composite identifier

FLIGHT Examples of Flight_ID

Flight_ID • CX888, 27-APR-2020
(Flight Number, Date) • CX888, 29-MAY-2020
… • AC007, 1-AUG-2020
…

36
Characteristics of Identifiers
• Will not be null
• Can one’s student ID be “[null]”?
• Will not change in value
• In HKUST : UG student ID = PG student ID = Staff ID
• No intelligent identifiers
• Should we use “LSK4066Samuel” as an identifier?

37
CARDINALITY CONSTRAINTS

38
 ERD of a Startup Travel Agency – 1st Year
Relationship DEPARTMENT STAFF
Type: D_ID has S_ID
D_Name S_Name

001 Peter Li
Relationship A Development
002 Mary Chan
Instance:
003 Anna Lee
B Marketing 004 Tom Lee
005 John Lam

39
 ERD of a Startup Travel Agency – 1st Year
Relationship DEPARTMENT STAFF
Type: D_ID has S_ID
D_Name S_Name

001 Peter Li
Relationship A Development
002 Mary Chan
Instance:
003 Anna Lee
B Marketing 004 Tom Lee
005 John Lam

40
Cardinality Constraints
DEPARTMENT STAFF
D_ID has S_ID
D_Name S_Name

• Minimum Cardinality: the minimum number of instances associated

• If zero, then Optional
• If one, then Mandatory
• Maximum Cardinality: the maximum number of instances associated
• One
• Many
41
Cardinality Constraints
DEPARTMENT STAFF
D_ID has S_ID
D_Name S_Name

001 Peter Li
A Development
002 Mary Chan
003 Anna Lee
B Marketing 004 Tom Lee
005 John Lam

A department (instance) has mandatory many staff (instance).

In other words, a department has one to many staff.

42
Cardinality Constraints
DEPARTMENT STAFF
D_ID has S_ID
D_Name S_Name

No associated
001 Peter Li
department
A Development
002 Mary Chan
003 Anna Lee
B Marketing 004 Tom Lee
005 John Lam

A staff (instance) belongs to optional one department (instance).

In other words, a staff belongs to zero or one department.

43
Cardinality Constraints Summary
Minimum Cardinality

Optional Mandatory

One
Optional one Mandatory one
Range: 0 or 1 Range: 1
Maximum
Cardinality

Many
Optional many Mandatory many
Range: 0, 1, 2…N Range: 1, 2...N

Range: allowable range of number of associated instances 44

 is assigned contains
PARKING PRODUCT
EMPLOYEE PRODUCT
SPACE LINE

One-to-one One-to-many

registers for
STUDENT COURSE

Many-to-many

45
Example 1:
Interpret the relationship with cardinality constraints
has PATIENT
PATIENT
HISTORY

Business rules: A patient has one to many patient history.

Each patient history is belonged to one and only one patient.

H01 Mark’s Visit 1

P01 Mark
H02 Sarah’s Visit 1
P02 Sarah H03 Sarah’s Visit 2
P03 Eric H04 Eric’s Visit 1

46
Example 2:
Interpret the relationship with cardinality constraints
is assigned
EMPLOYEE LAPTOP

Business rule: An employee is assigned with zero or one laptop.

A laptop is assigned to one and only one employee.

001 Ada 4877 X1 Carbon

002 Betty

003 Christy 9025 MacBook Air

47
ASSOCIATIVE ENTITIES

48
 Mandatory many to optional many relationship
between EMPLOYEE and PROJECT

is assigned to EMPLOYEE PROJECT

EMPLOYEE PROJECT
Rose BPR

Peter TQM

Debbie OO
Assign_Date
Tom CR

Heidi

Note. In the example, we omit the attributes for presentation clarity. 49

Mandatory many to optional many relationship
between EMPLOYEE and PROJECT

is assigned to
EMPLOYEE PROJECT

Assign_Date

Relationship Attribute

50
Converting many to many relationship into associative entity

is assigned to
EMPLOYEE PROJECT

Assign_Date

ASSIGNMENT

Associative Entity

51
Associative Entities
ASSIGNMENT
EMPLOYEE Assign_Date PROJECT

• It’s an entity – it has attributes

• AND it’s a relationship – it links entities together
• Converting a relationship to an associative entity
• Requirement: a many-to-many relationship
• Ternary relationships should be converted to associative entities
• An associative entity may or may not have an identifier
52
 ASSIGNMENT
EMPLOYEE Assign_Date PROJECT

Rose 1-Sep BPR

Peter 2-Sep TQM
Debbie 3-Sep OO
Tom 3-Sep CR
Heidi 3-Sep
5-Sep

⚫ An instance of the associative entity = An instance of the many-to-many relationship

⚫ An instance of the associative entity belongs to exactly one employee and one project

⚫ Rose was assigned to the BPR project on 1-Sep

53
DEGREE OF A RELATIONSHIP

54
Degree of a Relationship
• Degree of a Relationship is the number of entity types that participate in it
Unary (Degree 1) Binary (Degree 2) Ternary (Degree 3)

One entity related to Two different entity types Three different entity types
another same entity type related to each other related to each other

55
 Example 1: Unary Relationship – Optional one to optional one

Shirley
is married to
Eric

David
PERSON
Cathy

Mark

Mary

Business rule: A person is married to zero or one person.

56
Example 2: Unary Relationship – Optional one to optional many

manages

EMPLOYEE

Business rule: An employee manages zero to many employees

Each employee is managed by zero or one employee.

57
Example 3: Unary Relationship – Optional many to optional many

is composed of
A

B
ITEM
C

58
An example: Car parts

is composed of
Sporty Rim Rim Body Japan Wheel Cap
(Type A1)
ITEM

Race Wheel Fast Tire Air Valve

59
An example: Car parts Item_ID: 801
“Sporty Rim” is Sporty Rim
composed Component items:
of 2 items Rim body (Type A1),
Japan Wheel cap

is composed of
“Race Wheel” is Item_ID: 1002
composed of a Race Wheel
“Sporty Rim” and Component items:
ITEM 2 other items Sporty Rim,
Fast Tire, Air valve

Item_ID: 6601
“Fast Tire” is not Fast Tire
composed by Component items:
extra items None

60
An example: Car parts Item_ID: 801
Sporty Rim
Component items:
Rim body (Type A1),
Japan Wheel cap

is composed of
Item_ID: 1002
Race Wheel
Component items:
ITEM The same item -
Sporty Rim,
Fast Tire, Air valve
“Sporty Rim” is
included
in 2 other items Item_ID: 1003
Snow Racing Wheel
Component items:
Sporty Rim,
Snow Tire, Air valve

61
Example 4: Ternary Relationship – an example of a factory

PART

supplies
VENDOR WAREHOUSE

Shipping Mode
Unit Cost

Any ternary relationship should be converted to an associative entity.

62
 PART

SUPPLY SCHEDULE
Shipping Mode
VENDOR Unit Cost WAREHOUSE

63
DBMS, BUSIENSS RULES AND ERD
• Business rules are automated through DBMS software
• Some business rules cannot be represented in E-R diagram and
implemented in DBMS; instead they are stated in natural language

64