Entity Relationship Modeling

SECD2523 Database
Semester 1 2020/2021
SCSD2523 Database - Entity Relationship Modeling

Learning Objective
• At the end of the topic, students will be able to:
• Define basic concepts associated with ER diagram
• Produce ER model to represent information to application system.

14-Oct-21 2
SCSD2523 Database - Entity Relationship Modeling

Entity Relationship Modeling

PRICE L EVEL
# CO DE
* DESCR IPTIO N

• Models business, not implementation

def ined by applied to
p art o f

ORGANIZATIO N
o EMAIL
* NAME
o POSTAL CODE
at o REGION
o STREET
o TOWN p aren t o rga nizatio n o f
o TELEPHONE N UMBER
TITLE MOV IE o CO NTACT NAME
# PRODUCT CODE * CATEGORY o CO NTACT EXTENSION
* TITLE o AGE RATING

• Is a well-established technique
o DESCRIPTION * DUR ATION the
* MON OCHROME GAME
o AUD IO * CATEGORY distributo r fo
o PREVIEW * MEDIUM SUPPL IER
o MINIMUM MEMORY r # SUPPLIER CODE
o EMAIL
available * APPROVED
fo r * REFERENCE
from
PRICE HISTO RY
the source of
# EFFECTIVE DATE re viewed in available as on
* PRICE
* DEFAULT DAYS
* OVERDUE RATE

• Has a robust syntax

OTHE R O RG ANIZATION

PUBL ICATION
# REFER ENCE CATALO G the holder o
* TITLE # REFER ENCE
o VOLUME o CATALOG DATE f
o ISSUE o DESCRIPTION CUSTOME R
o PUBLISH DATE o EMAIL
ma na ge d by
* DESIGNATION
* FIRST NAME

• Results in easy-to-read diagrams…

EMPL OYEE th e man ag er of
the source of the source of * POSITION
*
o
LAST NAME
OTH ER INITIALS
* LAST NAME * STREET
o FIRST NAME * TOWN
o OTH ER INITIALS * POSTAL CODE
o EMAIL * REGION
o HO ME PH ONE
o WORK EXTENSION
acq uired f ro the cancellor o f o WORK PHONE
m re sp onsib le re sp onsib le o PHOTOGRAPH
o STAFF R EMARKS
of fo r fo r

COPY

• …although they may look rather complex

* ACQUIRE DATE
* PURCHASE COST the holder o f
* SHELF CODE
o CO NDITION re sp onsib le fo r
o CU STOMER REMARKS
... MEMBERS HIP TYPE
# CO DE
* DESCR IPTIO N
re nted o n re se rved o n * DISCOUNT PERCEN TAG E
o STANDARD FEE

at first sight
held by the typ e o
held by f
in of
in of
MEMBERS HIP
REVIEW # NU MBER
# SEQ UENCE o TERMINATIO N REASON
* ARTICLE o TERMINATIO N DATE
* HOT
o AUTHO R approved by
o UR L

re ne wed f o use d fo
the reservatio n fo r r r
cancelle d by
fo r
re quested authorized by of
against
the
BOO KING MEMBERS HIP PERIOD
* BOOK DATE re questor # START DATE
o EXPIRE D ATE o ACTUAL FEE PAID
o NO TIFY DATE of
o RESERVE DATE
o STAFF R EMARKS

approved by fo r
fulf illed as
RENTAL
* RENTAL DATE
o STAFF R EMARKS
o CO MPLETED

composed o f

the rental f or part of

fo r
RENTAL ITEM
# LINE NO
* RENTAL PERIO D
* PRICE PAID
o RETURN DATE
o STAFF R EMARKS

14-Oct-21 3
SCSD2523 Database - Entity Relationship Modeling

Goals of Entity Relationship Modeling

• Capture all required information
• Information appears only once
• Model no information that is derivable from other information already modelled
• Information is in a predictable, logical place

14-Oct-21 4
SCSD2523 Database - Entity Relationship Modeling

ER Diagram of Branch View of DreamHome

14-Oct-21 5
SCSD2523 Database - Entity Relationship Modeling

ER Modelling Notations
UML Notation Chen’s Notation Crow’s Feet Notation

We are using this in this course

14-Oct-21 6
SCSD2523 Database - Entity Relationship Modeling

Concepts of the ER Model

• Entity types
• Relationship types
• Attributes

14-Oct-21 7
SCSD2523 Database - Entity Relationship Modeling

Entity Types
• Entity types
• Group of objects with same properties, identified by enterprise as having an independent existence.
• “Something” of significance to the business about which data must be known
• A name for the things that you can list
• Usually a noun
• Entity occurrence/instances
• Uniquely identifiable object of an entity type.

14-Oct-21 8
SCSD2523 Database - Entity Relationship Modeling

Example of Entities and Instances

ENTITY INSTANCES
• PERSON • Mahatma Gandhi
• PRODUCT • 2.5 x 35 mm copper nail
• PRODUCT TYPE
• nail
• EMPLOYMENT CONTRACT
• my previous contract
• JOB
• SKILL LEVEL • violinist
• TICKET RESERVATION • fluent
• PURCHASE • tonight: Hamlet in the Royal
• ELECTION • the CD I bought yesterday
• PRINTER PREFERENCE
• for parliament next fall
• DOCUMENT VERSION
• …

14-Oct-21 9
SCSD2523 Database - Entity Relationship Modeling

Examples of Entity Types

• Something that exists physically or conceptually

14-Oct-21 10
SCSD2523 Database - Entity Relationship Modeling

Entities and Sets

• An entity represents a set of instances that are of interest to a particular business.

Set of instances of
manager entity JOB
JOB cook
waitress
dish washer
financial controller
porter
entity waiter piano player

14-Oct-21 11
SCSD2523 Database - Entity Relationship Modeling

Entity Representation in Diagram

JOB
• Drawn as a box EMPLOYEE

ELECTION
• Name singular
• Name inside TICKET
• Neither size, ORDER
RESERVATION

nor position
has a special JOB ASSIGNMENT

meaning
During design, entities usually lead to tables.

14-Oct-21 12
SCSD2523 Database - Entity Relationship Modeling

Remarks on creating Entities

• Give the entity a unique name
• Create a formal description of
the entity
• Add a few attributes, if possible
• Be aware of homonyms (words with same pronunciation but different meaning)
• Check entity names and descriptions regularly
• Avoid use of reserved words
• Remove relationship name from entity name

14-Oct-21 13
SCSD2523 Database - Entity Relationship Modeling

Relationship
• Relationship type
• Set of meaningful associations among entity types.
• Express how entities are mutually related
• Always exist between two entities (or one entity twice)
• Relationship occurrence
• Uniquely identifiable association, which includes one occurrence from each participating entity type.

14-Oct-21 14
SCSD2523 Database - Entity Relationship Modeling

Relationship Examples
• BRANCH has STAFF
STAFF belongs to BRANCH
• EMPLOYEES have JOBS
JOBS are held by EMPLOYEES
• PEOPLE make TICKET RESERVATIONS
TICKET RESERVATIONS are made by PEOPLE

14-Oct-21 15
SCSD2523 Database - Entity Relationship Modeling

Semantic Net of Has Relationship Type

14-Oct-21 16
SCSD2523 Database - Entity Relationship Modeling

Relationship Has Between Staff and Branch

14-Oct-21 17
SCSD2523 Database - Entity Relationship Modeling

Employees have Jobs

JOB manager
EMPLOYEE cook
Shintaro waitress
dish washer
Jill financial controller
Adam
Ahmed porter
waiter
Maria
piano player

Numerical observation:
Whether all these are true, it all
• All EMPLOYEES have a JOB
depends on business rules
• No EMPLOYEE has more than one JOB
• Not all JOBS are held by an EMPLOYEE
• Some JOBS are held by more than one EMPLOYEE
14-Oct-21 18
SCSD2523 Database - Entity Relationship Modeling

Employees Have Jobs

HAVE
EMPLOYEES JOBS

14-Oct-21 19
SCSD2523 Database - Entity Relationship Modeling

Relationship Types
• Degree of a Relationship
• Number of participating entities in relationship.

• Degree of relationship:
• two entities  binary;
• three entities  ternary;
• four entities  quaternary.

14-Oct-21 20
SCSD2523 Database - Entity Relationship Modeling

Example a binary relationship called: POwns

14-Oct-21 21
SCSD2523 Database - Entity Relationship Modeling

Example of a ternary relationship called:

Registers

14-Oct-21 22
SCSD2523 Database - Entity Relationship Modeling

Example of a quaternary relationship called:

Arranges

14-Oct-21 23
SCSD2523 Database - Entity Relationship Modeling

Relationship Types
• Recursive Relationship
• Relationship type where same entity type participates more than once in different roles.
• Relationships may be given role names to indicate purpose that each participating entity
type plays in a relationship.

14-Oct-21 24
SCSD2523 Database - Entity Relationship Modeling

Example of recursive relationship called:

Supervises with role names

14-Oct-21 25
SCSD2523 Database - Entity Relationship Modeling

Entities associated through two distinct

Relationships with Role Names

14-Oct-21 26
SCSD2523 Database - Entity Relationship Modeling

Practice 1 (Problem 1): Identify all entities

and relationship between entities.
• I am the manager of a training company that provides instructor-led courses in management
techniques. We teach many courses, each of which has a code, a name and a fee. Introduction to UNIX
and C Programming are two of our more popular courses. Courses vary in length from one day to four
days. An instructor can teach several courses. Paul Rogers and Maria Gonzales are two of our best
teachers. We track each instructor's name and phone number. Each course is taught by only one
instructor. We create a course and then line up an instructor. The students can take several courses
over time, and many of them do this. Jamie Brown from AT&T took every course we offer! We track each
student's name and phone number. Some of our students and instructors do not give us their phone
numbers.

14-Oct-21 27
SCSD2523 Database - Entity Relationship Modeling

Attributes
• Attribute
• Also represents something of significance to the business
• Is a specific piece of information that:
• Describes
• Quantifies
• Qualifies
• Classifies
• Specifies an entity
• Is a property of an entity or a relationship type.
• Attribute Domain
• Set of allowable values for one or more attributes.

14-Oct-21 28
SCSD2523 Database - Entity Relationship Modeling

Attribute examples

14-Oct-21 29
SCSD2523 Database - Entity Relationship Modeling

Attribute (Simple vs Composite)

• Simple Attribute
• Attribute composed of a single component with an independent existence.
• Cannot be further subdivided into smaller components.
• Composite Attribute
• Attribute composed of multiple components, each with an independent existence.
• Example: address (simple attribute)  can be subdivided into attributes of street, city, postcode

14-Oct-21 30
SCSD2523 Database - Entity Relationship Modeling

Attribute (Single-valued vs Multi-valued)

• Single-valued Attribute
• Attribute that holds a single value for each occurrence of an entity type.
• Majority of attributes are single-valued.
• Multi-valued Attribute
• Attribute that holds multiple values for each occurrence of an entity type.
• E.g.: Each branch have multiple telephone numbers. Thus, in this case, the attribute telNo is multi-
valued.

14-Oct-21 31
SCSD2523 Database - Entity Relationship Modeling

Attribute (Derived)
• Derived Attribute
• Attribute that represents a value that is derivable from value of a related attribute, or set of
attributes, not necessarily in the same entity type.
• E.g.: the value for the duration attribute of the Lease entity is calculated from the rentStart and
rentFinish attributes, also of the Lease entity type.

14-Oct-21 32
SCSD2523 Database - Entity Relationship Modeling

Practice 2 (Problem 1)
• Identify all attributes for each entity identified in Practice 1.
• Can you determine the type of each attribute?

14-Oct-21 33
SCSD2523 Database - Entity Relationship Modeling

Attribute: Keys
• Candidate Key
• Minimal set of attributes that uniquely identifies each occurrence of an entity type.
• Composite Key
• A candidate key that consists of two or more attributes.
• Primary Key
• Candidate key selected to uniquely identify each occurrence of an entity type.

14-Oct-21 34
SCSD2523 Database - Entity Relationship Modeling

ER Diagram of Staff and Branch Entities and

their Attributes

14-Oct-21 35
SCSD2523 Database - Entity Relationship Modeling

Practice 3 (Problem 1)
• Identify the primary key for each entity.

14-Oct-21 36
SCSD2523 Database - Entity Relationship Modeling

Entity Type
• Strong Entity Type
• Entity type that is not existence-dependent on some other entity type.
• Characteristic:
• Each entity occurrence is uniquely identifiable using PK attribute of the entity type
• Weak Entity Type
• Entity type that is existence-dependent on some other entity type.
• Characteristic:
• Each entity occurrence cannot be uniquely identified using attributes associated with the entity type

14-Oct-21 37
SCSD2523 Database - Entity Relationship Modeling

Strong vs Weak Entity

14-Oct-21 38
SCSD2523 Database - Entity Relationship Modeling

Relationship with Attributes

When to have
relationship with
attributes?

14-Oct-21 39
SCSD2523 Database - Entity Relationship Modeling

Structural Constraints
• Main type of constraint on relationships is called multiplicity.
• Multiplicity: number (or range) of possible occurrences of an entity type that may
relate to a single occurrence of an associated entity type through a particular
relationship.
• Constrains the way the entities are related in the relationship
• Represents policies (called business rules) established by user or company.

14-Oct-21 40
SCSD2523 Database - Entity Relationship Modeling

Structural Constraint (Binary Relationship)

• Binary relationship (i.e. the most common degree for relationships) is generally
referred to as having a multiplicity of:
• one-to-one (1:1), or
• one-to-many (1:*), or
• many-to-many (*:*)
• How to determine multiplicity:
• Produce a semantic net with sample occurrences that can best represent the relationship of
entities
• Analyze the semantic net

14-Oct-21 41
SCSD2523 Database - Entity Relationship Modeling

Semantic Net: Staff manages Branch

14-Oct-21 42
SCSD2523 Database - Entity Relationship Modeling

ERD: Staff manages Branch

Multiplicity of Manages relationship is one-to-one (1:1)  shown through the maximum range value
on the multiplicities at both ends of relationship

14-Oct-21 43
SCSD2523 Database - Entity Relationship Modeling

Semantic Net: Staff oversees PropertyForRent

14-Oct-21 44
SCSD2523 Database - Entity Relationship Modeling

ERD: Staff oversees PropertyForRent

Oversees is a one-to-many (1:*) relationship

14-Oct-21 45
SCSD2523 Database - Entity Relationship Modeling

Semantic Net: Newspaper Advertises

PropertyForRent

14-Oct-21 46
SCSD2523 Database - Entity Relationship Modeling

ERD: Newspaper Advertises PropertyForRent

Advertises is a many-to-many (*:*) relationship

14-Oct-21 47
SCSD2523 Database - Entity Relationship Modeling

Structural Constraint (Complex Relationship)

• Multiplicity for Complex Relationships
• Complex relationship – relationship higher than binary
• Number (or range) of possible occurrences of an entity type in an n-ary relationship when other (n-
1) values are fixed.

14-Oct-21 48
SCSD2523 Database - Entity Relationship Modeling

Semantic Net: Ternary Registers Relationship with Values for Staff and Branch
Entities Fixed

14-Oct-21 49
SCSD2523 Database - Entity Relationship Modeling

Multiplicity of Ternary Registers Relationship

Staff of a branch registers zero client or more

14-Oct-21 50
SCSD2523 Database - Entity Relationship Modeling

Summary of Multiplicity Constraints

14-Oct-21 51
SCSD2523 Database - Entity Relationship Modeling

Constraints (Cardinality & Participation)

• From multiplicity we can actually identify two type of restrictions on relationships:
cardinality and participation.
• Cardinality
• Describes maximum number of possible relationship occurrences for an entity participating in a
given relationship type.
• Appears as the maximum values for the multiplicity ranges on either side of the relationship

14-Oct-21 52
SCSD2523 Database - Entity Relationship Modeling

Constraints (Cardinality & Participation)

• Participation (mandatory/optional)
• determines whether all or only some entity occurrences are involved (participate) in a
relationship.
• All is mandatory (minimum value 1) ;
• only some is optional (minimum value 0)
• How to determine an entity’s participation on a diagram?
• Look at the minimum value of a range at one entity.
• If value is 0, then the opposite entity (in that relationship) has an optional participation
• If value is 1, then the opposite entity (in that relationship) has a mandatory participation

14-Oct-21 53
SCSD2523 Database - Entity Relationship Modeling

Multiplicity as Cardinality and Participation

Constraints

14-Oct-21 54
SCSD2523 Database - Entity Relationship Modeling

Practice 4: Problem 1
• Complete the ERD for the problem by:
• Determine whether the entities are strong entity or weak entity;
• Determine the multiplicity and structural constraints (cardinality and participation) for each
relationship in the diagram

14-Oct-21 55
SCSD2523 Database - Entity Relationship Modeling

Structural Constraints (cont)

• Why need to understand and able to differentiate between the different type of structural
constraints in data model (ERD)?
• Relational schemas will be created from entities-relationship in ERD
• Different type of constraints will affect the schemas created (will be discussed in logical database
design topic)

14-Oct-21 56
SCSD2523 Database - Entity Relationship Modeling

Problem 2: Produce ERD to represent

the following situation
• Model the USA-type geography, where the following is of interest:
• States of a country including their name, population, area and date established.
• Cities including their name, population, date established and founder's name.
• Rivers including their name and length.
• The capital city of each state, including the date declared.
• The source of each river, i.e. the state in which it begins (which may be none)
• The fact that cities may be located on particular rivers.
• The fact that cities are located in states.
• The length of each river within each state.
• Information about states that adjoin each another, and the length of their common border.
• Assume that the names of states and rivers are unique. City names are only unique within a state.

14-Oct-21 57
SCSD2523 Database - Entity Relationship Modeling

Problem 3: Produce ERD to represent

the following situation
• Consider the following set of requirements for a university database that is used to keep track of students' transcripts.
• The university keeps track of each student's name, student number, current address, permanent address and phone, birthdate, gender, year of study
(1st, 2nd, 3rd, 4th ), major department, minor department (if any), and degree program (B.A., B.Sc., ..., Ph.D.). Some user applications need to refer to
the city, county, and post code of the student's permanent address and to the student's last name.
• Each department is described by name, department code, office number and office phones. Both name and code have unique values for each
department.
• Each course has a course name, description, code number, number of semester hours and offering department. The value of the code number is
unique for each course.
• Each module has an instructor, semester, year, course and module number. The module number distinguishes different modules of the same course
that are taught during the same semester/year; its values are 1, 2, 3, ..., up to the number of taught during each semester.
• A grade report has a student, module and grade. modules

14-Oct-21 58