Entity Relationship Modelling
Entity Relationship Modelling
P.J. Mc.Brien
Imperial College London
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 1 / 49
Introduction
Designing a Relational Database Schema
How do you design a relational database schema for a particular UoD?
1 Need some way to model the semantics of the UoD as a conceptual schema
ER (many variants exist)
UML class diagrams
2 Need to map the ER/UML schema into a relational schema
3 Need to ensure that the relational schema is a good design
Normalisation
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 2 / 49
Introduction
Semantic Modelling: ER Schemas
CREATE TABLE branch
( sortcode INTEGER NOT NULL,
bname VARCHAR(20) NOT NULL,
cash DECIMAL(10,2) NOT NULL,
CONSTRAINT branch pk PRIMARY KEY (sortcode)
)
CREATE TABLE account
( no INTEGER NOT NULL,
type CHAR(8) NOT NULL,
cname VARCHAR(20) NOT NULL,
rate DECIMAL(4,2) NULL,
sortcode INTEGER NOT NULL,
CONSTRAINT account pk PRIMARY KEY (no),
CONSTRAINT account fk FOREIGN KEY (sortcode) REFERENCES branch
)
CREATE INDEX account type ON account (type)
CREATE TABLE movement
( mid INTEGER NOT NULL,
no INTEGER NOT NULL,
amount DECIMAL(10,2) NOT NULL,
tdate DATETIME NOT NULL,
CONSTRAINT movement pk PRIMARY KEY (mid),
CONSTRAINT movement fk FOREIGN KEY (no) REFERENCES account
)
branchbname
cash
sortcode
account
rate?
cname
type
no
movement
amount
tdate
mid
has
0:N
1:1
holds
1:1
0:N
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 3 / 49
Core ER Entities and Relationships
Core ER: Entities and Relationships
Entities
E An entity E represents a set of objects which conceptually are the same type of
thing
nouns → entity set
proper nouns imply instances, which are not entity sets.
Relationships
R A relationship R represents a set of tuples of objects where each tuple is some type
of conceptual association between entities E1, E2
verbs → relationship
R ⊆ {〈e1, e2〉 | e1 ∈ E1 ∧ e2 ∈ E2}
Identifying entities and relationships
In News Ltd, each person works in
exactly one department; there are
no restrictions on the number of
persons a department may employ.
person works
in
department
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 4 / 49
Core ER Attributes
Core ERKMO: Attributes of Entities
Attributes ER
M
ER
O
and ER
K
M A mandatory attribute E.A is a function that maps from entity set E to value
set V .
1 E.A ⊆ {〈e, v〉|e ∈ E ∧ v ∈ V }
2 unique: 〈e, v1〉 ∈ E.A ∧ 〈e, v2〉 ∈ E.A → v1 = v2
3 mandatory: E = {e | 〈e, v〉 ∈ E.A}
adjective, adjective noun → attribute
O an optional attribute removes property (3)
K certain attribute(s) E.A1 . . .E.An of E are denoted key attributes such that
E = {〈v1, . . . , vn〉|〈e, v〉 ∈ E.A1 ∧ . . . ∧ 〈e, vn〉 ∈ E.An}
Identifying attributes
We record the name of each person working in the department; and identify them by
their salary number. Optionally they might have a bonus figure recorded.
Departments are identified by their name.
person
salary number
name
bonus?
department dname
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 5 / 49
Core ER Cardinality Constraints
ER
L: Look-Here Cardinality Constraints
ER
L
E1 E2R
L:U
An upper bound cardinality constraint U states that each instance of E1 may
appear at most U times in R. An upper bound of N indicates no limit.
Additionally with ERO : a lower bound cardinality constraint L states that each
instance of E1 must appear at least L times in R
Adding look-here cardinality constraints in ER
LO
Each person works in exactly one department; there are no restrictions on the
number of persons a department may employ.
person departmentworks
in1:1
0:N
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 6 / 49
Core ER Cardinality Constraints
Quiz 1: Extent of Relationships
person = {‘Peter’, ‘Jane’, ‘Mary’} dept = {‘CS’, ‘Maths’}
person departmentworks
in0:1
0:N
Which is not a possible extent of works in?
A
works in={〈‘Peter’,‘Maths’〉, 〈‘Peter’,‘CS’〉, 〈‘Mary’,‘Maths’〉, 〈‘Jane’,‘Maths’〉}
B
works in={〈‘Peter’,‘Maths’〉, 〈‘Mary’,‘Maths’〉, 〈‘Jane’,‘Maths’〉}
C
works in={〈‘Peter’,‘CS’〉, 〈‘Mary’,‘Maths’〉, 〈‘Jane’,‘Maths’〉}
D
works in={〈‘Peter’,‘CS’〉, 〈‘Jane’,‘Maths’〉}
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 7 / 49
Core ER Cardinality Constraints
Quiz 2: Cardinality Constraints on Relationships
branch
sortcode
town
area manager name
in
x
y
Branches based in towns are all assigned to an area manager for that town; and area
managers are only assigned to towns that have branches
What should be the cardinality constraints of in?
A
x = 1:1, y = 0:N
B
x = 0:1, y = 0:N
C
x = 0:N, y = 1:N
D
x = 0:1, y = 1:N
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 8 / 49
Core ER Cardinality Constraints
ERC : Look-Across Cardinality Constraints
This course uses look-here cardinality constraints: state the number of
occurrences of the entity next to the constraint
person departmentworks
in1:1
0:N
Other variants of ER modelling use look-across cardinality constraints
person departmentworks
in0..N
1..1
For binary relationships, ERC and ERL are equally expressive.
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 9 / 49
Core ER Subset
ER
S : Subset/isa hierarchies
ER
S
S : if it is found that the instances of one entity Es are a subset of a another entity
E, we may add a subset constraint.
Es ⊆ E
specialisation of nouns → subset
Identifying subsets with ER
S
Some employees are ranked as managers, and receive a mobile phone.
personmanager ✲
mobile number
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 10 / 49
Core ER Subset
Quiz 3: Extent of subset and superset entities
manager = {‘Jane’, ‘Mary’}
mobile
phone
manager engineer
iet
grade
✣ ❪
person
Which is not a possible extent of person and engineer?
A
person={‘Peter’,‘Jane’,‘Mary’}
engineer={‘Jane’,‘Mary’}
B
person={‘Peter’,‘Jane’,‘Mary’,‘John’}
engineer={}
C
person={‘Peter’,‘Jane’,‘Mary’}
engineer={‘John’}
D
person={‘Peter’,‘Jane’,‘Mary’,‘John’}
engineer={‘Peter’,‘John’}
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 11 / 49
Core ER Composition
Combining Fragments
manager ✲
mobile
number
person
salary number
name bonus?
works
in1:1
0:N
department
dname
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 12 / 49
Core ER UML Class Diagrams
Using UML Class Diagrams as ER Models
salary number〈〈PK〉〉
name
bonus[0..1]
dname 〈〈FK〉〉
person
mobile number
manager
works in0..* 1 dname 〈〈PK〉〉
department
How to Use UML Class Diagrams as an ER Schema
Use UML stereotypes to denote at least primary key information
Various approaches exist
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 13 / 49
Core ER UML Class Diagrams
ER Modelling Constructs CKLMOS
Construct Description
C Look-across cardinality constraints
L Look-here cardinality constraints
K Key attributes
M Mandatory attributes
O Optional attributes
S Isa hierarchy between entities
A particular ER Modelling language normally chooses between C or L
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 14 / 49
Core ER UML Class Diagrams
Worksheet: ER Modelling
Draw an ERKLMOS schema to describe the following domain
The payroll system for BIG Inc records the salaries, status, joining date,
name, and payroll number for all of the corporation’s 30,000 employees.
Each employee works for one division, and each division has an account
number for paying its staff. We identify divisions by their name, and record
the address where the division’s HQ is located.
For employees sent abroad by BIG Inc, we record the address, country and
telephone number of the foreign tax office that will handle the employee. It
is assumed that each country has one central tax office that we have to deal
with. All other employees have their tax affairs dealt with by the Inland
Revenue.
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 15 / 49
Core ER UML Class Diagrams
Worksheet: ER Modelling
Draw an ERKLMOS schema to describe the following domain
The payroll system for BIG Inc records the salaries, status, joining date,
name, and payroll number for all of the corporation’s 30,000 employees.
Each employee works for one division, and each division has an account
number for paying its staff. We identify divisions by their name, and record
the address where the division’s HQ is located.
For employees sent abroad by BIG Inc, we record the address, country and
telephone number of the foreign tax office that will handle the employee. It
is assumed that each country has one central tax office that we have to deal
with. All other employees have their tax affairs dealt with by the Inland
Revenue.
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 16 / 49
ER → Relational Entities and Attributes
Mapping ERKLMOS to a relational model: entities and attributes
Taking a table per type (TPT) approach, there is a simple mapping of entities and
attributes to tables and columns:
1 Each entity E maps to a table RE
2 Each attribute A maps to a column CA of RE
3 If A is an optional attribute, then CA is nullable, otherwise CA is not nullable
4 If ~K are key attribute(s), then ~CK are a key of RE
Tables generated from entities
person
salary number
name
bonus?
department dname
person(salary number,name,bonus?)
department(dname)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 17 / 49
ER → Relational Relationships
Mapping ERKLMOS to a relational model: relationships
Taking a table per type (TPT) approach, for each relationship R between E1, E2,
entities E1, E2 map to R1, R2 as before, and
1 If R is a many-many relationship then it maps to
1 a table R R1 R2( ~K1, ~K2)
2 a foreign key R R1 R2( ~K1)
fk
⇒ R1( ~K1)
3 a foreign key R R1 R2( ~K2)
fk
⇒ R2( ~K2)
2 If R is a one-many relationship then it maps to
1 a column ~K2 in R1
2 a foreign key R1( ~K2)
fk
⇒ R2( ~K2)
3 if the participation of E1 in R is optional, then ~K2 is an optional column of R1
Tables generated from relationships
person
salary numbername
bonus?
department
dname
works
in1:1
0:N
person(salary number,name,bonus?,dname)
department(dname)
person(dname)
fk
⇒ department(dname)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 18 / 49
ER → Relational Relationships
Mapping ERKLMOS to a relational model: relationships
Taking a table per type (TPT) approach, for each relationship R between E1, E2,
entities E1, E2 map to R1, R2 as before, and
1 If R is a many-many relationship then it maps to
1 a table R R1 R2( ~K1, ~K2)
2 a foreign key R R1 R2( ~K1)
fk
⇒ R1( ~K1)
3 a foreign key R R1 R2( ~K2)
fk
⇒ R2( ~K2)
2 If R is a one-many relationship then it maps to
1 a column ~K2 in R1
2 a foreign key R1( ~K2)
fk
⇒ R2( ~K2)
3 if the participation of E1 in R is optional, then ~K2 is an optional column of R1
Tables generated from relationships
person
salary numbername
bonus?
department
dname
works
in0:N
0:N
person(salary number,name,bonus?)
department(dname)
works in(salary number,dname)
works in(salary number)
fk
⇒ person(salary number)
works in(dname)
fk
⇒ department(dname)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 18 / 49
ER → Relational Subset
Mapping ERKLMOS to a relational model: subsets
Taking a table per type (TPT) approach, for each subset Es of E, entities Es, E
map to tables Rs, R as before and:
1 a key ~K in Rs (where ~K is the key of R)
2 a foreign key Rs( ~K)
fk
⇒ R( ~K)
Tables generated from subsets
salary number
namebonus?
personmanager ✲
mobile number
person(salary number,name,bonus?)
manager(salary number,mobile phone)
manager(salary number)
fk
⇒
person(salary number)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 19 / 49
ER → Relational Subset
Worksheet: Mapping ERKLMOS to a relational model
Take your ERKLMOS schema in the worksheet, and map it into a relational schema.
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 20 / 49
Extended ER Generalisation
ER
D: Disjointness and Generalisation Hierarchies
In ERD: the disjointness of entities E1 . . . En may be specified, enforcing that
∀x, y.x 6= y→Ex ∩ Ey = ∅
The notion of generalisation hierarchies combines the use of disjointness and
subset.
disjoint specialisation of nouns → generalisation
Identifying generalisation hierarchies in ER
SD
Employees may also be divided, according to how they like to receive messages, into
email users and non-email users. The former must have a email address recorded,
the later must have a pigeon hole number recorded.
pigeon
hole
non email
user
email
user
person
✻
messages
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 21 / 49
Extended ER Generalisation
Quiz 4: Extent of generalisation entities
mobile
phone
manager engineer
✻
iet
grade
person
Which is not a possible extent the entities?
A
person={‘Peter’,‘Jane’,‘Mary’,‘John’}
engineer={‘Peter’,‘John’}
manager={‘Jane’,‘Mary’}
B
person={‘Peter’,‘Jane’,‘Mary’,‘John’}
engineer={}
manager={‘Jane’,‘Mary’}
C
person={‘Peter’,‘Jane’,‘Mary’,‘John’}
engineer={‘John’}
manager={‘Jane’,‘Mary’}
D
person={‘Peter’,‘Jane’,‘Mary’,‘John’}
engineer={‘Peter’,‘John’,‘Mary’}
manager={‘Jane’,‘Mary’}
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 22 / 49
Extended ER Weak Entities
ER
W : Weak entities
swipe
card
issue
date
for
1:1
0:N
person
salary
number
If we allow the participation of an entity in a relationship to be part of the
entity key, we have a weak entity
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 23 / 49
Extended ER Weak Entities
Quiz 5: Subsets and weak entities
managermobile
phone
✲ person name
Which of the following is equivalent to the schema above?
A
manager mobile
phone
of
1:1
1:1
person name
B
manager mobile
phone
of
1:1
0:1
person name
C
manager
mobile
phone
of
1:1
1:1
person name
D
manager
mobile
phone
of
1:1
0:1
person name
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 24 / 49
Extended ER n-ary relationships
ER
H: Allowing an n-ary relationship
In graph theory, an edge connecting more that two nodes is called a
hyper-edge.
In ERH: allow n-ary relationships between entities, rather than just binary
An n-ary relationship is equivalent to a weak entity with n binary relationships
Identifying an n-ary relationship
A person may work in multiple departments, and for each department the person
works in, the person will be assigned a manager
person works
in0:N
0:N
department
manager
0:N
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 25 / 49
Extended ER n-ary relationships
Ternary Relationships: Inability to Express Constraints in ERLH
branch service
postcode
provides
0:N
0:N
0:N
branch service
postcode
provides
1..1
1..1
0..N
each branch provides only one type of service in any postcode area, and each service
is only provided one branch in any postcode area
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 26 / 49
Extended ER n-ary relationships
Ternary Relationships: Inability to Express Constraints in ERCH
atm site bank
atm
located
0:N
0:N
0:1
atm site bank
atm
located
1..1
1..1
0..N
an atm machine from a leasing company may be assigned to a particular bank at a
particular site, but banks do not have exclusive use of a site
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 27 / 49
Extended ER Attributes on relationships
ER
A: Allowing attributes on relationships
Use when there are values to be associated with the relationship between entities
Identifying an attribute of a relationship
We record the start date when a person joined a department, and when the person
leaves, record the end date they left the department. We keep a history of all
departments the person worked in.
person
start date end date?
works
in0:N
0:N
department
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 28 / 49
Extended ER Attributes on relationships
Quiz 6: Appropriate use of attributes on relationships
In the stock control system, we identify products by the pno, and keep our stock in a
number of warehouses identified by wcode. We record single price of each product,
and the quantity qty of product we keep in each warehouse.
Which of the following best models the above domain?
A
warehouse wcode
stock
0:N
0:N
product
pno
qty
price
B
warehouse wcode
qty
price
stock
0:N
0:N
product pno
C
warehouse
wcode
qty
stock
0:N
0:N
product
pno
price
D
warehouse wcode
qtystock
0:N
0:N
product
pno
price
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 29 / 49
Extended ER Nested Relationships
ER
N : Allowing nested relationships
Identifying a nested relationship
When a person works in a department, they may work on any number of projects
with a certain role. People may take different roles on the project for each department
that they work in.
person works
in0:N
0:N
department
project
member
0:N
0:N
role
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 30 / 49
Extended ER Nested Relationships
Nested relationship equivalences
Need for using nested relationships
If a relationship to which a nested edge connects is mandatory and unique with
entity E, then the nested relationship can instead connect to E
Equivalent ER Schemas
person department
project
works
in1:1
0:N
member
0:N
1:1
role ≡
person works
in1:1
0:N
member
0:N
1:1
department
project
role
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 31 / 49
Extended ER Nested Relationships
Quiz 7: Nested relationship equivalences
person works
in0:1
0:N
department
project
member
0:N
1:1
role ≡?
person works
in0:1
0:N
department
project
member
0:N
1:1
role
Are the two ER schemas equivalent?
True False
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 32 / 49
Extended ER Multi-valued Attributes
ER
V : Multi-valued Attributes
Multi-valued Attributes
M A mandatory attribute E.A is a function that maps from entity set E to value
set V .
1 E.A ⊆ {〈e, v〉|e ∈ E ∧ v ∈ V }
2 unique: 〈e, v1〉 ∈ E.A ∧ 〈e, v2〉 ∈ E.A → v1 = v2
3 mandatory: E = {e | 〈e, v〉 ∈ E.A}
adjective, adjective noun → attribute
O an optional attribute removes property (3) ?
V a multi-valued attribute removes property (2) +
an attribute can be both optional and multi-valued ∗
Identifying multi-valued attributes
Each person must have at least one home phone number recorded, and may have any
number of cars registered as having access to the car park.
person
salary number
phone+
car*
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 33 / 49
Extended ER Summary of EER Constructs
EER Modelling Constructs ADHKLMNOSVW
EER
Define Extended ER (EER) modelling language as one that supports KLMOS
plus at least one of ADHNVW
Construct Description
A Attributes can be placed on relationships
D Disjointness between sub-classes can be denoted
C Look-across cardinality constraints
H hyper-edges (n-ary relationships) allowed
L Look-here cardinality constraints
K Key attributes
M Mandatory attributes
N Nested relationships
O Optional attributes
S Isa hierarchy between entities
V Multi-valued attributes
W Weak entities can be identified
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 34 / 49
Extended ER Summary of EER Constructs
Worksheet: Constructing an ERADHKLMOSW Schema
The customer and supplier database of Big Inc will hold all accounts of the
company, divided into customer accounts and supplier accounts. All accounts have
an account number, and one account manager assigned from the company’s staff.
Big Inc identifies staff by a sid, and records the staff member’s name and room.
The account managers have a limit on the number of accounts they can manage.
Only certain staff members are permitted to be account managers.
For customer accounts we need to record a credit limit on the balance of the
account, and the telephone number of the accounts department at the customer.
For supplier accounts we need to record which Big Inc products are supplied, and
at what price.
Big Inc products are identified by the company standard part no and all have a
description. For some we record the colour. Some products have a record of the
components, each component identified by a combination of part no and component
number, and again each has a description. Some products do not have a supplier.
Big Inc has purchased a copy of the Post Office address file, and associates every
account to an address from this file. The address data includes street number,
street name, town, county and post code, and uses a combination of street number
and post code as a key.
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 35 / 49
EER → Relational Generalisation
Mapping ERD to a relational model
Taking a table per type (TPT) approach, if E is a generalisation of E1, . . . , En,
then entities E1, . . . , En, E map to tables R1, . . . , Rn, R as before and:
1 treat each Ex ∈ E1, . . . , En as a subset of E
2 no implementation of disjointness using just PKs and FKs
Tables generated from generalisations
pigeon
hole
non email
user
email
user
person salary number
name
bonus?
✻
messages
person(salary number,name,bonus?)
non email user(salary number,pigeon hole)
non email user(salary number)
fk
⇒
person(salary number)
email user(salary number,email)
email user(salary number)
fk
⇒
person(salary number)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 36 / 49
EER → Relational Weak Entities
Mapping ERW to a relational model
If EW is a weak entity that maps to a relation RW , the foreign key RK due to
the participation in a relationship is also used in the key of RK
Tables generated from weak entities
person
name
bonus?
salary
number
swipe
card
for
1:1
0:N
issue
date
person(salary number,name,bonus?)
swipe card(salary number,issue,date)
swipe card(salary number)
fk
⇒ person(salary number)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 37 / 49
EER → Relational Weak Entities
Mapping ERW to a relational model
If EW is a weak entity that maps to a relation RW , the foreign key RK due to
the participation in a relationship is also used in the key of RK
Tables generated from weak entities
person
name
bonus?
salary
number
swipe
card
for
1:1
0:N
issue
date
person(salary number,name,bonus?)
swipe card(salary number,issue,date)
swipe card(salary number)
fk
⇒ person(salary number)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 37 / 49
EER → Relational n-ary Relationships
Mapping ERH to a relational model
Rules for binary relationship R between E1, E2 generalise to rules for R between
E1, . . . , En
Tables generated from n-ary entities
person
salary number
department
dname
manager
❄
works
in0:N
0:N
0:N
person(salary number)
manager(salary number)
manager(salary number)
fk
⇒ person(salary number)
department(dname)
works in(person salary number,manager salary number,dname)
works in(person salary number)
fk
⇒ person(salary number)
works in(manager salary number)
fk
⇒ manager(salary number)
works in(dname)
fk
⇒ department(dname)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 38 / 49
EER → Relational Attributes on Relationships
Mapping ERA to a relational model
Attributes on Relationships
Attributes of a relationship go on the same table as that which implements the
relationship
Tables generated from attributes of relationships
person
salary number
start date end date?
works
in0:N
0:N
department
dname
person(salary number)
department(dname)
works in(salary number,dname,start date,end date?)
works in(salary number)
fk
⇒ person(salary number)
works in(dname)
fk
⇒ department(dname)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 39 / 49
EER → Relational Attributes on Relationships
Mapping ERA to a relational model
Attributes on Relationships
Attributes of a relationship go on the same table as that which implements the
relationship
Tables generated from attributes of relationships
person
salary number
start date end date?
works
in1:1
0:N
department
dname
person(salary number,dname,start date,end date?)
department(dname)
person(dname)
fk
⇒ department(dname)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 39 / 49
EER → Relational Attributes on Relationships
Quiz 8: Handling of ERA 0:1 cardinality
person
salary number
start date end date?
works
in0:1
0:N
department
dname
Which is the most precise mapping of the ER schema?
A
person(salary number)
department(dname)
works in(salary number,dname,start date,end date?)
works in(salary number)
fk
⇒ person(salary number)
works in(dname)
fk
⇒ department(dname)
B
person(salary number)
department(dname)
works in(salary number,dname,start date,end date?)
works in(salary number)
fk
⇒ person(salary number)
works in(dname)
fk
⇒ department(dname)
C
person(salary number,dname,start date,end date?)
department(dname)
person(dname)
fk
⇒ department(dname)
D
person(salary number,dname)
department(dname,salary number,start date,end date?)
department(salary number)
fk
⇒
person(salary number)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 40 / 49
EER → Relational Nested Relationships
Mapping ERN to a relational model
Nested Relationships
If relationship R connects to relationship S, (1) map S as normal, (2) when mapping
R, treat S as if it were an entity, and apply the normal rules for mapping R.
Mapping Nested Relationships
person works
in0:N
0:N
department
project pcode
member
0:N
0:N
role
person(salary number)
department(dname)
project(pcode)
works in(salary number,dname)
works in(salary number)
fk
⇒
person(salary number)
works in(dname)
fk
⇒department(dname)
member(pcode,salary number,dname,role)
member(salary number,dname)
fk
⇒ works in(salary number,dname)
member(pcode)
fk
⇒ project(pcode)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 41 / 49
EER → Relational Nested Relationships
Mapping ERN to a relational model
Nested Relationships
If relationship R connects to relationship S, (1) map S as normal, (2) when mapping
R, treat S as if it were an entity, and apply the normal rules for mapping R.
Mapping Nested Relationships
person works
in0:N
0:N
department
project pcode
member
0:N
1:1
role
person(salary number)
department(dname)
project(pcode)
works in(salary number,dname,pcode,role)
works in(salary number)
fk
⇒
person(salary number)
works in(dname)
fk
⇒ department(dname)
works in(pcode)
fk
⇒ project(pcode)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 41 / 49
EER → Relational Multi-valued Attributes
Mapping ERV to a relational model
Multi-valued Attributes
Each multi-valued attribute E.Av is stored in its own table RAv, together with the
key attributes of the table R used to represent the entity R.
All attributes of RAv form the key of RAv, and there is a foreign key from RAv to R
No efficient method of representing + constraint
Tables for multi-valued attributes
person
salary number
phone+
car*
person(salary number)
person phone(salary number,phone)
person phone(salary number)
fk
⇒ person(salary number)
person car(salary number,car)
person car(salary number)
fk
⇒ person(salary number)
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 42 / 49
EER → Relational Multi-valued Attributes
Worksheet: Mapping ERADHKLMOSWN to a relational model
Take your ERADHKLMOSWN schema in the worksheet, and map it into a relational
schema.
P.J. Mc.Brien (Imperial College London) Entity Relationship Modelling 43 / 49
Introduction
Core ER
Entities and Relationships
Attributes
Cardinality Constraints
Subset
Composition
UML Class Diagrams
ER Relational
Entities and Attributes
Relationships
Subset
Extended ER
Generalisation
Weak Entities
n-ary relationships
Attributes on relationships
Nested Relationships
Multi-valued Attributes
Summary of EER Constructs
EER Relational
Generalisation
Weak Entities
n-ary Relationships
Attributes on Relationships
Nested Relationships
Multi-valued Attributes