(Extended) Entity Relationship Modelling

and
Mappings to the Relational Data Model
Simplified phases of Database Design
Mini World

Requirements collection & analysis

Database requirements

Conceptual design
DBMS
Independent Conceptual schema
(in a high level data model)

Data model design

Conceptual schema
(in the data model of a specific DBMS
DBMS
specific Physical design

Internal schema
(for the same DBMS)
Data Definition Language Statements
 Conceptual Data Model Concepts
 “There exist things which have certain
properties and which may be related in some
way(s) to other things. Data represents
specific facts about the things”
 Entity
 thing or object that exists in its own right and is
distinguishable, represented by an Entity Type of
which there will be many Entity Instances…...
physical objects, events, activities, associations
 Relationship
 an association between several entities
represented by a Relationship Type of which there
will be many Relationship Instances
Conceptual Data Model Concepts

 Attribute
 fact about an Entity Type or Relationship Type
 an entity is often expressed as a set of attributes

 Entity Set or Extent

 Set of all Entity Instances of the same Entity Type

 Relationship Set or Extent

 Set of all Relationship Instances of the same
Relationship Type
Entity Types and Relationship Types
Staff Works_for Department
ST1 r1
D1
ST2
r2
ST3 D2
r3 D3
ST4
r4
ST5
….
ST6 r5
…. r6
….
Optional Relationship Types
Staff Manages Department
ST1 r1
D1
ST2
ST3 D2
r2 D3
ST4
r3
ST5
….
ST6
….
….
Many:many Relationship Types
Staff Teaches Course
ST1 r1
C1
ST2
r2
ST3 C2
r3 C3
ST4
C4
r4
….
….
r5

r6
….
 Recursive Relationship Types
Staff r1 Manages
ST1 1
unmanaged ST2 1
2 r2
ST3
2
ST4 2 r3
1
ST5 2
r4
2
ST6 r5
…. 1 ….
1
1. Manager
2. Employee
Entity Relationship Model
given family
1
SCHOOL
name REG
studno
m hons
STUDENT faculty
m
n m YEARREG
year
1
labmark YEAR
ENROL TUTOR
1
exammark
slot
YEARTUTOR

courseno m 1 1
m n
COURSE TEACH
name STAFF

subject 1 m
roomno
equip appraisee
appraiser

APPRAISAL
 Attributes in Conceptual Modelling
 For each and every attribute must define domain,
data type, format and whether it can be null
 Every entity type must have a key attribute or set
of attributes
 Composite or Atomic
 Single-valued or Multi-valued
 Derived
given family
 Null valued labmark no. of
courseno
equip
name students
m n
STUDENT ENROL COURSE

exammark
studno subject
Properties of Relationship Types
 Degree
 The number of participating entity types
 Cardinality ratios
 The number of instances of each of the participating entity types
which can partake in a single instance of the relationship type 1:1,
1:many, many:1, many:many
 Participation (optionality)
 The relationship instance doesn’t have to exist
 Whether an entity instance has to participate in a relationship
instance
 Role
 The function that a particular entity type plays in a relationship
type
Semantic Data Models
Extended-Entity-Relationship Modelling
Entity Attribute Relationship Modelling
Entity Relationship Attribute Modelling
Entity Modelling
Object Modelling
IFO,
NIAMetc.…
Extensions for temporal, constraints, rules etc

Chen 1976
Entity Relationship Modelling
Composite Keys

name

PERSON

dateofbirth
Roles & Recursive Relationships

 The function of an entity type in a

relationship type

name STAFF

1 m
roomno
appraisee
appraiser

APPRAISAL
Roles & Association Relationships

 The function of an entity type in a

relationship type

given family

name
name
SUPERVISE
1
m

STAFF
1 m STUDENT

EXAMINER
roomno studno
Non-binary Relationship

roomno
STAFF
name STAFF

p
given family courseno
equip
name TUTORS
m n
COURSE
STUDENT
slot
subject
studno
 Entity Relationship Model
given family
1
SCHOOL
name REG
studno STUDENT
m hons
STUDENT faculty (studno, givenname,
m
familyname, hons,
n m YEARREG year tutor, slot, year)
1
ENROL(studno, courseno,
labmark YEAR labmark,exammark)
ENROL TUTOR
1
exammark COURSE(courseno, subject, equip)
slot
YEARTUTOR
STAFF(lecturer, roomno, appraiser)
courseno m 1 1
m n TEACH(courseno, lecturer)
COURSE TEACH
name STAFF
m YEAR(year, yeartutor)
subject roomno 1
equip appraisee
appraiser SCHOOL(hons, faculty)

APPRAISAL
 Mapping Entity Types to Relations
 For every entity type create a relation
{ primary_key (E) U {a1…am} }
 Every attribute in entity becomes a relation attribute
 The relation is a subset of the X of the domains of the attributes
 Composite attributes—just include all the atomic attributes
 Derived attributes are not included but their derivation rules are

given family courseno

labmark no. of
equip
name students
m n
ENROL COURSE
STUDENT

exammark
studno subject
Mapping many:many Relationship Types to
Relations
 Create a relation:
n (degree of relationship)
U primary_key(E ) i U {a1…am}
i=1
primary keys of each attributes on the
participating entity relationship type (if any)
type in the relationship

given family courseno

labmark no. of
equip
name students
m n
ENROL COURSE
STUDENT

exammark
studno subject
Mapping one:many Relationship Types to Relations
 Mostly: ‘Posting the primary key’
 Given E1 at ‘many’ end of relationship and E2 at ‘one’
end of relationship, add to the relation for E1
 Make the primary key of the entity at the ‘one’ end (the determined
entity) a foreign key in the entity at the ‘many’ end (the determining
entity). Include any relationship attributes with the foreign key entity

{ E1 U primary_key(E2) U {a1…an} }

relation for attributes on

primary key for E2, is
entity E1 the relationship
now a foreign key to E2
type (if any)
given family name
slot roomno
name
m 1
studno TUTOR STAFF
STUDENT
Mapping one:many Relationship Types to
Relations

STUDENT STAFF
studno given family tutor slot name roomno
s1 fred jones bush 12B kahn IT206
s2 mary brown kahn 12B bush 2.26
s3 sue smith goble 10A goble 2.82
s4 fred bloggs goble 11A zobel 2.34
s5 peter jones zobel 13B watson IT212
s6 jill peters kahn 12A woods IT204
capon A14
lindsey 2.10
barringer 2.125
Mapping one:many Relationship Types to Relations
 Sometimes...
 If relationship type is optional to both entity types and an
instance of the relationship is rare, and there are lots of
attributes on the relationship then…
 Create a relation for the relationship type:
{primary_key(E1) U primary_key(E2) U {a1…am}

primary key for E1, is now a primary key for attributes on the
foreign key to E1; E2, is now a relationship type
also the PK for this relation foreign key to E2 (if any)

given family name

slot roomno
name
m 1
studno TUTOR STAFF
STUDENT
Mapping one:many Relationship Types to Relations

STUDENT
studno given family
s1 fred jones STAFF
s2 mary brown name roomno
s3 sue smith
kahn IT206
s4 fred bloggs
bush 2.26
s5 peter jones
s6 jill peters
goble 2.82
zobel 2.34
TUTOR watson IT212
studno tutor slot woods IT204
s1 bush 12B capon A14
s2 kahn 12B lindsey 2.10
s3 goble 10A barringer 2.125
s4 goble 11A
s5 zobel 13B
s6 kahn 12A
Optional Participation of Determined Entity
(‘one end’)

A student entity instance A school entity instance

must participate in a does not have to
relationship instance of REG participate in a relationship
instance of REG

given family
1
SCHOOL
name REG
studno
m hons
STUDENT faculty

 SCHOOL(hons,faculty)
 STUDENT(studno,givenname,familyname, ??? )
 Optional Participation of Determined Entity
STUDENT
studno given family hons
s1 fred jones ca hons can’t be null
s2 mary brown cis
s3 sue smith cs because it is mandatory
s4 fred bloggs ca for a student to be
s5 peter jones cs registered for a school.
s6 jill peters ca

SCHOOL
hons faculty
ca accountancy no-one registered for mi
cis information systems
cs computer science
so doesn’t occur as a
ce computer science foreign key value
mi medicine
cm mathematics
Optional Participation of the Determinant
Entity (‘many end’)

given family name

slot roomno
name
m 1
studno TUTOR STAFF
STUDENT

A student entity instance A staff entity instance

does not have to must participate in a
participate in a relationship relationship instance of
instance of TUTOR TUTOR
 Optional Participation of the Determinant
Entity (‘many end’)
1. STUDENT (studno,givenname,familyname,tutor,slot)
STAFF(name, roomno)
Integrity constraints:
name) STAFF – tutor) STUDENT = 

2. STUDENT(studno,givenname,familyname)
STAFF(name,roomno)
TUTOR(studno,tutor,slot)

3. same as 2 if lots of attributes on TUTOR

Optional Participation of the Determinant
Entity

STUDENT STAFF
studno given family tutor slot name roomno
s1 fred jones bush 12B kahn IT206
s2 mary brown kahn 12B bush 2.26
s3 sue smith goble 10A goble 2.82
s4 fred bloggs null null
zobel 2.34
s5 peter jones zobel 13B
s6 jill peters null null
watson IT212
woods IT204
capon A14
lindsey 2.10
barringer 2.125
Mapping one:one Relationship Types to Relations
1. Post the primary key of one of the entity types into
the other entity type as a foreign key, including any
relationship attributes with it or
2.Merge the entity types together
STAFF year

name roomno 1
kahn IT206 YEAR
bush 2.26 1
goble 2.82 YEAR
zobel 2.34 year yeartutor YEARTUTOR

watson IT212 1 zobel

name 1 roomno
woods IT204 2 bush
capon A14 3 capon STAFF
lindsey 2.10
barringer 2.125
 Multi-Valued Attributes
 Create a relation for each multi-valued attribute
{ primary_key(Ei) U multi-valued attribute }
The primary key is (primary_key(Ei) U multi-valued attribute)

STUDENT
studno given family dateofbirth contact
s1 fred jones 10/4/78 Mr. Jones
Mrs Jones
given
s2 mary brown 12/1/72 Bill Brown
family
Mrs Jones
dateofbirth Billy-Jo Woods
name

studno STUDENT STUDENT_CONTACTS

studno contact
s1 Mr. Jones
contact s1 Mrs Jones
s2 Bill Brown
s2 Mrs Jones
s2 Billy-Jo Woods
Mapping Roles & Recursive Relationships
 The function of an entity type in a relationship
type

name STAFF

1 m
roomno
appraisee
appraiser

APPRAISAL

STAFF(name, roomno, ??? )

 Multiple Roles between Entity Types
1. Treat each relationship type separately
2. Distinct roles are represented by different foreign
keys drawing on the same relation given family

name
name
SUPERVISE
1
m

STAFF
1 m STUDENT

EXAMINER
roomno studno

STAFF(name,roomno)
STUDENT(studno,given,family, ??? )
STAFF(name,roomno)
EXAMINER( ??? )
SUPERVISOR( ??? )
EXAM-SUPER( ??? )
Non-binary Relationship
roomno
STAFF
STAFF
name
p
given family courseno
equip
name TUTORS
m n
COURSE
STUDENT
slot
subject
studno

COURSE(courseno, subject, equip)

STUDENT(studno, givenname, familyname)
STAFF(staffname, roomno)
TUTORS( ??? )
 Comparative Terms
EER Relational
Formal Informal
Entity Type Schema Relational Schema Table description
Entity Type Relation Table
Entity instance Tuple Row
1:many relationship type Use foreign keys Use foreign keys
1:many relationship instance Use foreign keys Use foreign keys
Attribute Attribute Column
Domain or Value Set Domain Data Type
Key Candidate Key Candidate Key
No equivalent Primary Key Primary Key
Multivalued attribute No equivalent No equivalent
Composite attribute No equivalent No equivalent
Superclasses, Subclasses;
Specialisation & Generalisation Relationships

 Subclasses and Superclasses

a subclass entity type is a specialised type
of superclass entity type
 a subclass entity type represents a subset
or subgrouping of superclass entity type’s
instances
 e.g. undergraduates and postgraduates
are subclasses of student superclass
 Attribute Inheritance
 subclasses inherit properties (attributes) of
their superclasses
Constraints on Specialisation & Generalisation
 Specialisation
 the process of defining a set of more specialised entity
types of an entity type
 Generalisation
 the process of defining a generalised entity type from a
set of entity types
 Predicate/Condition defined
 determine the entities that will become members of each
subclass by a condition on an attribute value. All member
instances of the subclass must satisfy the predicate
 e.g. first years and second years are subclasses of
undergraduates based on their year attribute.
 User defined
 no condition for determining subclass membership
Constraints on Specialisation & Generalisation
 Disjointness
 Overlap
 the same entity instance may be a member of more than one subclass of the
specialisation
 Disjoint
 the same entity instance may be a member of only one subclass of the
specialisation

 Completeness
 Total
 every entity instance in the superclass must be a member of some subclass in
the specialisation
 Partial
 an entity instance in the superclass need not be a member of any subclass in
the specialisation
Specialisation & Generalisation Relationships

given family
name studno

STUDENT

d
STAFF 
1 
year

tutor m
undergraduate postgraduate

thesis title
Superclasses, Subclasses
Specialisation & Generalisation Relationships

payroll no name
STAFF

length of service



level  grade

ACADEMIC TECHNICAL ADMIN

project
Superclasses, Subclasses
Specialisation & Generalisation Relationships
name PERSON

address
O


salary
fee

EMPLOYEE STUDENT

O 


d 


thesis

RESEARCH TEACHING
POST UNDER
1-2 GRAD GRAD

O


year = 3


TUTORS 1-2
LECTURING SUPERVISOR FINAL
YEAR

courseno project
Categories and Categorisation
 a single superclass/subclass relationship with
more than one superclass, where the
superclasses represent different entity types
(sometimes with different keys)

COMPANY
PERSON

personid compid
U

duration of ownership

OWNER
 Specialisation & Generalisation Option A
1. Create a relation for superclass
2. Create a relation for each subclass such that:
{primary_key of superclass} U {attributes of subclass}
key for subclass is (primary_key of superclass)
given family
Inclusion dependency:
name
<key>(superclass) <key>(subclass ) studno
STUDENT
Covering dependency:
n (number of subclasses)
 <key>(subclass ) = <key>(superclass) d
i=1 

Disjoint dependency: year
n (number of subclasses)
undergraduate postgraduate
 <key>(subclass ) = 
i=1 thesis title
 Specialisation & Generalisation Option B
1. Create a relation for each subclass such that:
{primary_key U {attributes U {attributes of
of superclass} of superclass} subclass}
key for each relation is (primary_key of superclass)

given family
name studno
STUDENT

•Works for total and disjoint

constraints d
•Partial: lose any entity that is not in 
a subclass 
•Overlapping: redundancy year

•To recover the superclass can do undergraduate postgraduate

an OUTER UNION on the subclass
relations thesis title
 Specialisation & Generalisation Option C
1. Create one relation such that:
{primary_key U {attributes U {attributes U {type
of superclass} of superclass} of all subclasses} attribute}
 key for subclass is (primary_key of superclass)

given family
name studno

•Many ‘not-applicable’ nulls STUDENT

•Does away with joins
•Disjoint: one type which indicates
which subclass the tuple represents d
•Overlap: set of types = number of 
subclasses

year
•Partial: type is null represents
superclass undergraduate postgraduate

thesis title
Specialisation & Generalisation Overlapping
1.STAFF(payrollno,name,lengthofservice)
name
ACADEMIC(payrollno,level)
payroll no
STAFF TECHNICAL(payrollno,project)
ADMIN(payrollno,grade)
length of service
2.ACADEMIC(payrollno,name,lengthofservice,
O level)


 TECHNICAL(payrollno,name,lengthofservice,
level
grade project)
ADMIN(payrollno,name,lengthofservice,grade)
ACADEMIC TECHNICAL ADMIN 3.STAFF(payrollno,name,lengthofservice,level,
project,grade,type1,type2,type3)
project STAFF(payrollno,name,lengthofservice,level,
project,grade,type)
type = powerset of classes
Specialisation & Generalisation Relationships

name PERSON

address
O


salary
fee

EMPLOYEE STUDENT

O 


d 


thesis

RESEARCH TEACHING
POST UNDER
1-2 GRAD GRAD

O


year = 3


TUTORS 1-2
LECTURING SUPERVISOR FINAL
YEAR

courseno project
 Specialisation Lattice with Shared Subclass
 To be a shared subclass the superclasses must have the
same key, so any of the options A, B or C stand.

payroll no Staff
 d


d 
Manager Hourly Staff




 Salaried Staff
Academic Technical Admin



Admin Manager
Categories and Categorisation
 A category is a subclass of the union of two or more
superclasses that can have different keys because they
can be of different entity types
 If defining superclasses have different keys, specify a
new surrogate key

COMPANY
PERSON

personid compid
U

duration of ownership
OWNER( ??? )
PERSON( ??? )
OWNER COMPANY( ??? )
Entity Constraints
 If an entity instance X depends on the existence of an
entity instance Y, then X is existence dependent on
 entity type Y is dominant
 entity type X is subordinate

customer orderid date part quantity

RipOff Inc 123 23/6/94 widget 20
thingie 24
RipOff Inc 678 3/10/94 widget 20
UpYa Ltd 123 27/9/94 wotsits 800
widget 50
thingie 24
Strong and Weak Entities (identifier dependency)
 a strong entity type has an identifying primary key
 a weak entity type does not have a primary key but
does have a discriminator

customer

CUSTOMER
address
1

CUST-ORDER
m orderid

ORDER
date
Weak Entity
customer
CUSTOMER
address
1

CUST-ORDER
m orderid

ORDER
date
1

ORDER-MAKEUP

m part
ORDER_
ORDER
LINES
LINES quantity
 Mapping Weak Entities to Relations
 Create a relation
n
U primary_key(Ei) U partial_key U {ai…an}
i=1
Primary key of each Partial key of Attributes of the
participating identifying weak entity (if weak entity type
entity type any) (if any)

customer

CUSTOMER
address
1

CUST-ORDER
m orderid

ORDER
date
Association Entity Type
 An entity type that represents an association
relationship type
 Useful if:
 a relationship has lots of attributes
 you want a relationship type with a relationship
type
labmark exammark
m n
STUDENT ENROL COURSE

TUTOR

STAFF
Association Entity Type plus Mapping
 An entity type that represents an association
relationship type

given family
courseno
name equip

m
STUDENT COURSE
1 1
studno subject

STUD_ENROL m m COURSE_ENROL
ENROL
labmark
exammark

COURSE(courseno, subject, equip)

STUDENT(studno, givenname, familyname )
Aggregation
 Aggregation is an abstraction concept for
building composite entities from their
components
1. aggregate attribute values to form a whole
entity
2. combining entities that are related by an
association relationship into higher-level
aggregate entity
 IS-A-PART-OF
 IS-A-COMPONENT-OF
 Sadly, not catered for in EER modelling.
Aggregation

STUDENT ENROL COURSE

COURSE
TUTORIAL

studno courseno

STAFF
STUDENT ENROL COURSE

slot
COURSE
TUTORIAL

TUTORS STAFF
Hints for EER Modelling
 identify entity types by searching for nouns and noun
phrases
 assume all entities are strong and check for weak ones
on a later pass
 need an identifier for each strong entity
 assume all relationships are partial participation (optional)
and check for total (mandatory) ones on a later pass
 expect to keep changing your mind about whether things
are entities, relationships or attributes
 keep level of detail relevant and consistent (for example
leave out attributes at first)
 approach diagram through different views and merge
them
 Lets Practice!
 A record company wishes to use a computer database to help with
its operations regarding its performers, recordings and song
catalogue.
 Songs have a unique song number, a non-unique title and a
composition date. A song can be written by a number of
composers; the composer’s full name is required. Songs are
recorded by recording artists (bands or solo performers). A song is
recorded as a track of a CD. A CD has many songs on it, called
tracks. CDs have a unique record catalogue number, a title and
must have a producer (the full name of the producer is required).
Each track must have the recording date and the track number of
the CD.
 A song can appear on many (or no) CDs, and be recorded by many
different recording artists. The same recording artist might re-record
the same song on different CDs. A CD must have only 1 recording
artist appearing on it. CDs can be released a number of times, and
each time the release date and associated number of sales is
required.