comp101-lect04

COMP10
1:
Foundations
of
Information
Systems
Lecture 4: Stephen
Cranefield
Data Modelling Department of
Information
Science
(II)
Entity-Relationship
Diagrams
Paper Structure
Section 1: Databases
(storing and retrieving You are (still) here
organized information)
Foundations of Section 2: Key Issues in

ICT (networks, security,
Information AI and machine
learning)
Section 3: Data
COMP 101:
Representations
Section 4: Algorithms
(execution of
instructions)
From Lecture 3: Data modelling
• To set up a database, you must first define a
model of the data to be stored in it
• A data model:
• models the types of things (entities) of
interest
• specifies the attributes needed to
describe each entity
• models relationships between entities
• is used by analysts to capture requirements
and inform the design of databases
• An entity-relationship diagram (ERD) is a
common type of data model
Example: recording sales
Entity-relationship diagram (ERD)
Entity (may end up as a table in a database)
} Attributes
(may end up as columns
in a database table)
Relationship
Unique identifier
(primary key).
Can be a
combination of two {
attributes as shown
here
Notes
:
1) Th is u s es t h e “ In fo r m a t io n E n g in e e r in
• T hi s is In fo r m a tio n E ng i n ee rin g n o ta t io n ,
go”n: evaorfimanatnoy fdEifRfeDrenntoEtaRtDionnotations
2) •DaSsohlieddanadnddassohleidd lliinneess
hfoavreresulabttiloyndsifhfeipresnht amveeasnpinegcsif–icwme
weaillniginngosreththaattwineCwOoMnP’t101 discuss
Objectives for today
• Understand the purpose of an ERD

• Understand the ERD concepts and notation
• In conjunction with this week’s lab, be able
to:
• Explain ERD key concepts
• Read and interpret ERD examples
• Draw an ERD that matches given business rules
ERD concept 1: Entity
• “…a thing or object of significance,
whether real or imagined, about which
information needs to be known or held”
(Barker, 1990)
Employee Product
Customer Location
Transaction Departme
nt
• Shown as a named rectangle in an ERD

• “Our business” usually does not need
to be modelled as an entity
???
17
How do we tell
whether something is
an entity?
1. Is it a specific type of thing we need to
know about?
2. Is it relevant to the problem?
3. Do you need to deal with more

than one occurrence?
If yes to all three, then it’s (probably) an

entity.
Entities vs. Instances/Data items
Types
Instances
(possibly many instances of a single
entity)
…
Entities vs. Instances/Data items
Entity in an ERD Row in a table

• Describes common features • Individual data item recorded
of a type of thing or object: in a database:
• e.g., “Person” • e.g., Janet Smith

• Has a value for each attribute
• A set of attributes
need to describe a
person • e.g. “Janet Smith”, 9/7/1999
• Actual data used by an IS
• e.g., name, date of birth
• ... can have many rows
• Part of a data model representing different
instances of the entity
• One entity in an ERD ...
ERD concept 2: Attribute
• An individual property of an entity

• Holds a single value of some well-defined type
• A value may be required (indicated using a ●) or
optional
ERD concept 2: Attribute
• ERDs may omit the attributes (e.g. in the initial

phase of modelling)
ERD concept 3: Unique identifier
• Combination of one or more attributes
whose value uniquely identifies an
instance
• Entire combined value must be unique
• Usually only one per entity type

• Even when it comprises several attributes
• Often referred to as the primary key (Lecture 4)
• Examples:
• A student ID is unique to a specific student
• A sale line needs both the sale number and
product code to identify it uniquely
Notation for unique IDs
• Often shown in a
separate
Student ID
compartment
• Unique ID
(“primary
key”)
• Then other attributes
Example multi-attribute unique ID
• A sale number does not uniquely identify a
sale line There can be multiple sale lines in
the sale
• A product number does not uniquely identify a
sale line The product can appear in multiple
sales
• These two attributes combined uniquely identify a sale line
ERD concept 3: Relationship
• Reflects a real-world association
between entities:
• A sale is to a customer
• A sale is made by an employee
• An employee has an assigned office
• Properties of a relationship:
• Cardinality: one to one, one to many, many to many
• Participation: optional or mandatory
Relationship cardinality
• Denotes “how many” instances

of one entity can be related to
instances of another entity, and Employee
vice versa
• Three types
• 1:1 (one-to-one) — not common
Look
like
“1”s
Note: We are modelling an organisation’s “business logic” here. Some

Company
organisations might consider allocating more than one company car to an
employee, but not this one Car

instances of another entity, and Customer
vice versa
• Three types Looks
• 1:1 (one-to-one) — not like
“many”
common branch
• 1:M (one-to-many) — the es
most common
Order

instances of another entity, and Student
vice versa
• Three types
• 1:1 (one-to-one) — not common
• 1:M (one-to-many) — the
most common
• M:N (many-to-many) — Paper
fairly common
The N is deliberately different from M to
suggest that (e.g.) student Sam may take
154 students (7 ≠ 38)

7 papers, and paper COMP101 may have
Adding participation symbols
(we now allow 0s)
• The relationship line
There can
now has two symbols be many
at each end movies by
a director It is optional (“O”):
• At each end: to have any
movies for a
• Cardinality symbol given director
(closest symbol to the
entity box)
The model insists
• Participation (“I”) that there
A movie must be a
symbol has at most director for a
(innermost symbol) one movie
director (it is
mandator
y)
For illustrative purposes, we assume movies have a single director
Combined meanings of
cardinality and participation
Note: It depends on the drawing tool whether the line is visible

through the O (both versions are OK)
Diagram source: https://www.lucidchart.com/pages/ER-diagram-symbols-and-
meaning
How to “read” relationships
Movie Director
• You can read the relationship in two directions

• Read the symbols at the end of the line in the direction you are
reading
• Movie to Director:
A Movie must be directed by one (and only one) Director
(or: a movie is directed by exactly one director)
• Director to Movie:
A Director may direct many Movies
(or: a director directs zero or more movies)
Adding details to an M:N
relationship
• Consider the relationship between
patient and illness:
• The relationship has additional information:

• e.g., date of diagnosis, severity of symptoms, …
• Where should we store this information?
• It is specific to a particular patient and a
particular illness
Solution
• Insert an associative entity (e.g.
Diagnosis) to split the M:N
relationship
• Diagnosis attributes:
• Needs the primary keys for a Patient and an illness
• Plus new diagnosis attributes: date, severity, etc.
1.
Split M:N
relationship
into
separate
directions
2.
Insert associative entity
3. Add cardinality and participation for original

entities: A diagnosis is for exactly one patient and
exactly one illness
ERD Summary
• Business analysts need to analyse the nature
of the data that the business collects and
generates
• ERDs provide a high level notation for
modelling the structure and relationships
between different types of data
• ERDs can be used to capture database
requirements graphically, discuss with
others, and pass on to database designers
• ERDs are models of the data that needs to be
stored
– they don’t deal with actual data instances.
Exercise 1: understand ERD notation
• Identify the
following in the ERD
at left:
• Entities
• Attributes
• Unique identifiers
• Relationship(s) where
participation is
mandatory on both
sides
• Describe the
relationship between
Customer and Sale
Header in plain
English
Exercise 2: interpret an ERD
Direction: Employee to Office:

Every employee is allocated exactly one office
Direction: Office to Employee:

Cardinality: An office may ....
Participation: An office may ...
Combined: An office may ....
Homework exercises
• Identify some real-world entities and

work out what their attributes and
unique identifiers might be.
• How would you model their relationships

with other entities?
Thanks!
Questions?

comp101-lect04

Uploaded by

Copyright:

Available Formats

comp101-lect04

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

comp101-lect04

Uploaded by

Copyright:

Available Formats

COMP10

Foundations of Section 2: Key Issues in

• Understand the purpose of an ERD

• Shown as a named rectangle in an ERD

2. Is it relevant to the problem?

3. Do you need to deal with more

If yes to all three, then it’s (probably) an

Entity in an ERD Row in a table

• e.g., “Person” • e.g., Janet Smith

• An individual property of an entity

• ERDs may omit the attributes (e.g. in the initial

• Usually only one per entity type

• Denotes “how many” instances

Note: We are modelling an organisation’s “business logic” here. Some

• Denotes “how many” instances

• Denotes “how many” instances

154 students (7 ≠ 38)

Note: It depends on the drawing tool whether the line is visible

• You can read the relationship in two directions

• The relationship has additional information:

3. Add cardinality and participation for original

Direction: Employee to Office:

Direction: Office to Employee:

• Identify some real-world entities and

• How would you model their relationships

You might also like