SIT103/SIT772 Database Fundamentals

Week 3
Entity Relationship Diagram
(ERD)

Dr Iynkaran Natgunanathan,
email:
[Link]@[Link],
Phone: +61 3 924 68825.
Last Week
• Database design – Conceptual and logical design
• Relational model – Entity, Attribute, Relationships, and
constraints
• Keys (composite, super, candidate, primary, natural, surrogate,
foreign, secondary)
• Integrity Rules – Entity and Referential Integrity
• Entity Relationship Diagram

3-2
Last Week’s OnTrack Task

• Task 2.1P Database Modelling Tools

- Basics of relational database modelling
- To familiarise you with modelling tools
- LucidChart and MS Visio

3-3
Questions?
Any questions/comments so far

Last week’s content

OnTrack tasks
Anything in general about the unit

3-4
This week
• More on conceptual and logic design
- Entity Relationship Diagram (ERD)
• Some case studies

3-5
Entity Relationships Model (Recap)
• Entity: person, place, thing, or event about which data
will be collected and stored
- e.g., Student, Course, Product, Order, Transaction, etc.
• Attribute: characteristic of an entity
- e.g., ID, Name, DoB, Address, etc.
• Relationship: association among entities
- One-to-one (1:1 OR 1..1)
- One-to-many (1:M OR 1..*)
- Many-to-many (M:N OR *..*)
• Constraint: restriction placed on data
3-6 - Ensures data integrity, e.g., Unique, Not NULL, etc.
Entity Relationships Model (2)

Business Rules

Entities, Attributes, Relationships

Entity Relationships Model

3-7
Attributes and domain

• Required attribute: must have a value (NOT NULL)

• Optional attribute: may be left empty (NULL is
allowed)
• Domain: set of possible values for an attribute
• States of Australia - {ACT, NT, NSW, QLD, SA, TAS,
VIC, WA}
• Height, Weight – decimal numbers
• Age – integer numbers

3-8
Types of Attributes

• Composite attribute – can be further subdivided

• Simple attribute – cannot be further subdivided

• Single-valued attribute – can only have a single value at a

particular instance of time
e.g., a person has one weight

• Multi-valued attribute – can have many values, e.g., a

person can have several aliases, or multiple contact numbers
or several skills, or several qualifications, and so on
3-9
Types of Attributes (2)

Entity Single-Value Attribute Simple Attribute

Composite Attribute STU_NAME can be

subdivided into these three simple attributes Textbook
Figure 4.1
3-10
 Multi-valued Attribute
Car’s color – body color, roof color, and trim color

Textbook
Figure 4.3

Primary Key Multi-valued attribute

A double line denotes a
is not differentiated
2-11
multi-valued attribute
Multi-valued Attribute (2)

• Problematic – we should avoid them

- Remember, in relational model
“Each row and column intersection must represent a single data value”
• Two possible solutions
– Create new attributes one for each of the original multi-
valued attribute’s components
– Create a new entity composed of original multi-valued
attribute’s components

2-12
Multi-valued Attribute (3)
Splitting the multi-valued attribute
(CAR_COLOR) into three attributes

Textbook
13-13 Figure 4.4
Multi-valued Attribute (4)

A new entity from a

multi-valued attribute

Textbook Table
4.1

Textbook
Figure 4.5
(bottom figure)
13-14
Derivable Attributes
• An attribute whose value may be calculated (derived) from
other attributes
– Need not be physically stored in the DB
– Can be derived when needed
A dashed line also depicts a derived attribute,
alternative symbol in Chen’s notation

Textbook
Figure 4.6

2-15
Should we store derived attributes?

Table 4.2 Stored Not Stored

Advantage Saves CPU processing cycles Saves storage space

Saves data access time Computation always yields
Data value is readily available current value
Can be used to keep track of
historical data
Disadvantage Requires constant maintenance Uses CPU processing cycles
to ensure derived value is Increases data access time
current, especially if any values Adds coding complexity to
used in the calculation change queries

Textbook Table 4.2

3-16
ERD Notations (Recap)

3-17
Textbook Figure 2.3
ERD: Relationships
• Mandatory vs Optional relationships
Textbook Table 4.3

Textbook Textbook
Figure 4.13 Figure 4.14
3-18
ERD: Relationship Strength
• Weak (non-identifying) relationship
– Primary key of the related entity does not contain a
primary key component of the other entity
– The relationship is denoted using dashed line in the ERD
• Strong (identifying) relationships
– Primary key of the related entity contains a primary key
component of the other entity
– A relationship that occurs when two entities are existence
dependent
– The relationship is denoted using solid line in the ERD
3-19
Weak Relationship

Textbook
Figure 4.8

3-20
Strong Relationship

Textbook
Figure 4.9

3-21
 Strong vs Weak Relationships Textbook
Figure 4.8

Weak
Relationship

Strong
Relationship

Textbook
• Entity and Referential Integrity Figure 4.9
- Primary key: NOT NULL Relationship can be strong/weak depending on
how primary keys are defined in entities
- Foreign key: Can be NULL
A class record can be created without course information – Weak relationship
A class record can’t be created without course information – Strong relationship
3-22
Existence Dependence

• Existence dependence
– Entity exists in the database only when it is associated with
another related entity occurrence
• Existence independence
– Entity exists apart from all of its related entities
– Referred to as a strong entity or regular entity

3-23
Weak Entities

• Conditions of a weak entity

– Existence-dependent
– Has a primary key that is partially or totally derived from
parent entity in the relationship
– Condition of strong relationships

Weak entity has a strong relationships with another entity

3-24
Weak Entities
• A company insurance policy insures an employee and any dependents

Textbook
Figure 4.10

Textbook
Figure 4.11

3-25
Implementing Relationships
1:1 Relationship:
PK of one entity as a FK in another entity
Textbook
Figure 5.8

3-26
Implementing Relationships (2)
1:M Relationship:
PK of the “1” side in the table of the “M” side as a FK

Textbook
Figure 3.17

Textbook
3-27 Figure 3.18
Implementing Relationships (3)
M:N Relationship:
Textbook
Figure 3.23

Any idea how can we implement it?

Do you think it can be implemented just using PK/FK?

It is little bit tricky!

3-28
Implementing M:N Relationship

Replacing it with two 1:M relationships by creating a new composite table

- bridge table or associative entity
• Including foreign keys based on the primary keys of the 2 tables
• Assigning additional attributes as needed to the composite table

Textbook
Figure 3.26

3-29
M:N Relationship Example

Could also be called

STUDENT-CLASS

Composite/Bridge
Table

Textbook
Figure 3.25

3-30
Associative Entity (Revisit)
• Used to implement an M:N relationship between two or
more entities
• Composed of the primary key attributes of each parent entity
• May also contain additional attributes that play no role in
connective process
Textbook
Figure 4.24

Implemented case
Business case

Textbook
3-31 Figure 4.25
Associative Entity (Revisit) (2)
• DOCTORs prescribe DRUGs for PATIENTs.

Textbook
Figure 4.15

Textbook
Figure 4.15

3-32
Extended ER Model

• Advanced data modelling

• Result of adding more semantic constructs to ER model
- modelling data requirements in complex real-world
applications
- Entity supertypes
- Entity subtypes
- Entity clustering

3-33
Extended ER Model (2)
• Consider a scenario, in which most employees possess a wide range of skills
and special qualifications,
• Database designer must find a variety of ways to group employees based on
their characteristics.
• For instance, a retail company could group employees as salaried and hourly,
• while a university could group employees as faculty, and admin staff
• The grouping of employees into various types provides two important
benefits:
– It avoids unnecessary NULLs in attributes when some employees have characteristics
that are not shared by other employees.

• It enables a particular employee type to participate in relationships that are

unique to that employee type.
3-34
Issues of NULLs
Textbook
Figure 5.1

These attributes are applicable to

certain types of Employees only
3-35
Entity Supertypes and Subtypes
• Entity supertype
– Generic entity type related to one or more entity subtypes
– Contains common characteristics

• Entity subtype
– Contains unique characteristics of each entity subtype

• Criteria to determine usage

– The different kinds of instances should each have one or more attributes
that are unique to that kind of instance
– Define a special Supertype attribute known as the Subtype
discriminator.
– Define disjoint or overlapping constraints and complete or partial
3-36
constraints.
Specialization Hierarchy
• Entity supertypes and subtypes are organized in a
specialization hierarchy
– Depicts arrangement of higher-level entity supertypes and
lower-level entity subtypes
– Relationships are described in terms of “is-a” relationships
– Every subtype has one supertype to which it is directly related
– Supertype can have many subtypes
E.g. Full-time employee is an Employee
Part-time employee is an Employee

3-37 Subtype (special case) Supertype (general case)

Specialization Hierarchy (2) Textbook
Figure 5.2

3-38
Disjoint and Overlapping Constraints

• Disjoint subtypes: contain a unique subset of the

supertype entity set
– Known as nonoverlapping subtypes
– Implementation is based on the value of the subtype discriminator
attribute in the supertype (in the parent entity)

• Overlapping subtypes: contain nonunique subsets of

the supertype entity set
– Implementation requires the use of one discriminator attribute for
each subtype (in the parent entity)

3-39
Disjoint and Overlapping Constraints (2) Textbook
Figure 5.5

One
subtype Two
attributes subtype
attributes

D=disjoint O=overlapping

3-40
Completeness constraints
• Specifies whether each supertype occurrence must also
be a member of at least one subtype
– Partial completeness: not every supertype occurrence is a
member of a subtype
– Total completeness: every supertype occurrence must be a
member of at least one subtypes Textbook
Figure 5.2

3-41
Levels of hierarchy

Textbook
Figure 5.4

Disjoint Total: Student must be either

graduate or undergard, but cannot be
both, Subtype discriminator:
STU_TYPE cannot be NULL

3-42 Overlapping partial: All employee may not be (either administrator or a professor)
Subtype discriminator: EMP_IS_ADM,EMP_IS_PROFC for this can be NULL
Specialization and Generalization

Specialization
- Top-down process
- Identifies lower-level, more specific entity subtypes from a higher-level
entity supertype
- Based on grouping unique characteristics and relationships of the subtypes
Generalization
- Bottom-up process
- Identifies a higher-level, more generic entity supertype from lower-level
entity subtypes
- Based on grouping common characteristics and relationships of the
subtypes

3-43
Entity Clustering
“Virtual/Abstract” entity type used to represent multiple
entities and relationships in ERD
- To simply complex ERD
- in some problems you may have hundreds of entities
- Formed by combining multiple interrelated entities into a single, abstract
entity object
- General rule: avoid the display of attributes to eliminate complications
that result when the inheritance rules change
- Not an actual entity from the business rule
- Not implemented
3-44
Entity Clustering (2) Textbook
Figure 5.6

OFFERING groups
SEMESTER, COURSE, and
CLASS

LOCATION groups
BUILDING and ROOM

Relationships of Entities
within the Entity cluster is
represented in a separate ERD

3-45
History of Time-variant data
• Time-variant data: data whose values change over time
and for which a history of the data changes must be
retained
– Requires creating a new entity in a 1:M relationship with the
original entity
– New entity contains the new value, date of the change, and
any other pertinent attribute

E.g., tracking salary histories of employees

3-46
Maintaining data history

Textbook
Figure 5.9

3-47
Maintaining data history (2)
• An employee could be the manager of many different departments over
time,
• A department could have different managers over time.
• Because you are recording time-variant data, you must store the
DATE_ASSIGN attribute in the MGR_HIST entity to provide the date
that the employee (EMP_NUM) became the department manager.

Textbook
Figure 5.10

3-48
 ERD Exercise: Case 1

Draw an ER diagram for the following description

The HEG has twelve instructors and can handle up to
thirty trainees per class. HEG offers five "advanced
technology" courses, each of which may generate several
classes. If a class has fewer than ten trainees in it, it will
be cancelled. It is, therefore, possible for a course not to
generate any classes during a session. Each class is
taught by one instructor. Each instructor may teach up to
two classes or may be assigned to do research only. Each
trainee may take up to two classes per session.

3-49
 ERD Exercise: Case 1 (2)

Determine Entities
The HEG has twelve instructors and can handle up to
thirty trainees per class. HEG offers five "advanced
technology" courses, each of which may generate several
classes. If a class has fewer than ten trainees in it, it will
be cancelled. It is, therefore, possible for a course not to
generate any classes during a session. Each class is
taught by one instructor. Each instructor may teach up to
two classes or may be assigned to do research only.
Each trainee may take up to two classes per session.
3-50
 ERD Exercise: Case 1 (3)
Determine Relationships
• One instructor Many classes (teach)
• One class One instructor (taught by)
• Some instructors MAY NOT teach any classes

• One course Many classes (generate)

• One class One course (generated by)
• Some courses MAY NOT generate any classes

• One trainee Many classes (enroll)

• One class Many trainees (enrolled by)
3-51
 ERD Exercise: Case 1 (4)

HEG has twelve instructors and can handle up to thirty trainees

per class. …
Also consider the business rules about: Instructor, Class, Trainee

TEACH ENROLL

3-52
 ERD Exercise: Case 1 (4)
… HEG offers five "advanced technology" courses, each of which
may generate several classes. …
Also consider the business rules about: Class, Course

TEACH ENROLL

GENERATE

3-53
 ERD Exercise: Case 1 (5)
… twelve instructors can handle up to thirty trainees per class. … .
If a class has fewer than ten trainees in it, it will be cancelled.

TEACH ENROLL

(10,30)

GENERATE

3-54
 ERD Exercise: Case 1 (6)
… each course may generate several classes. … It is, therefore,
possible for a course not to generate any classes during a session.

TEACH ENROLL

(10,30)

(0,N)
GENERATE
(1,1)

3-55
 ERD Exercise: Case 1 (7)
… . Each class is taught by one instructor. Each instructor may
teach up to two classes or may be assigned to do research only.

TEACH ENROLL

(1,1) (0,2) (10,30)

(0,N)
GENERATE
(1,1)

3-56
 ERD Exercise: Case 1 (8)
… Each trainee may take up to two classes per session.

TEACH ENROLL

(1,1) (0,2) (1,2) (10,30)

(0,N)
GENERATE
(1,1)

3-57
 ERD Exercise: Case 1 (3)

Note that we did not consider Attributes here

What next:
• Attributes This is a homework for you!
• Primary keys
• Maintaining relationships
- using FK
- using bridge/associative entity (M:N relationships)
3-58
 ERD Exercise: Case 2

Draw an E-R diagram for the following description

The Jonesburgh County Basketball Conference (JCBC) is
an amateur basketball association. Each city in the county
has one team that represents it. Each team has a maximum
of twelve players and a minimum of nine players. Each
team also has up to three coaches (offensive, defensive
and PT coaches). Each team plays two games (home and
visitor) against each of the other teams during the season.

3-59
 ERD Exercise: Case 2 (2)

Determine Entities
The Jonesburgh County Basketball Conference (JCBC)
is an amateur basketball association. Each city in the
county has one team that represents it. Each team has a
maximum of twelve players and a minimum of nine
players. Each team also has up to three coaches
(offensive, defensive and PT coaches). Each team plays
two games (home and visitor) against each of the other
teams during the season.

3-60
 ERD Exercise: Case 2 (3)

Determine Relationships
• One city One team (posses)
• One team One city (represent)

• One team Many players (has)

• One player One team (belong to)

• One team Many coaches (employ)

• One coach One team (employed by)

• One team Many teams (play against)

• One team Many teams (play against)
3-61
 ERD Exercise: Case 2 (4)

… Each city in the county has one team that

represents it. …

CITY TEAM
REPRESENT

(1,1) (1,1)

3-62
 ERD Exercise: Case 2 (5)

…. Each team has a minimum of nine players and a

maximum of twelve players.….

CITY TEAM PLAYER

REPRESENT HAS

(1,1) (1,1) (1,1) (9,12)

3-63
 ERD Exercise: Case 2 (6)

… Each team also has up to three coaches …

CITY TEAM PLAYER

REPRESENT HAS

(1,1) (1,1) (1,1) (9,12)

(1,1)
EMPLOY

(1,3)

COACH

3-64
 ERD Exercise: Case 2 (7)

…. Each team plays two games (home and visitor)

against each of the other teams during the season.
PLAY

(2,N) (2,N)

CITY TEAM PLAYER

REPRESENT HAS

(1,1) (1,1) (1,1) (9,12)

(1,1)
EMPLOY
(1,3)
COACH

3-65
 ERD Exercise: Case 2 (8)
…. Each team plays two games (home and visitor) against each of the other teams during the
season.
GAME

(2,N) (2,N)
PLAY PLAY
(1,1) (1,1)

CITY TEAM PLAYER

REPRESENT HAS

(1,1) (1,1) (1,1) (9,12)

(1,1)
EMPLOY
(1,3)
COACH

3-66
Summary
• Types of Attributes
• Strengths of Relationships
• Types of Entities
• Implementing Relationships
• Extended/Advanced ERD concepts
• Modelling historical time-variant data
• Case studies
3-67
This Week’s OnTrack Tasks

• 3.1P Modelling database for a given business scenario in terms

of ERD
- Using Lucid Chart or MS Visio

• 3.2HD Research Report and Presentation

- On a topic of your interest related to database and/or data management
- Select one from the given list or propose your own (discuss with us)
- Due on Friday of Week 9 (16 Sept 2022)

• Please check the task sheets and start working on them

3-68
Next Week
• Normalisation

Thank you
See you next week
Any questions/comments?

3-69
Readings and References:
• Chapters 2-5 and 9
Database Systems : Design, Implementation, & Management
13TH EDITION, by Carlos Coronel, Steven Morris

3-70