MDA104: Tutorial 2
Relational Model
Conversion from ERD
Vlastislav Dohnal
2
2
◼ Convert the following E-R model to a relational
model.
reader
ID No.
name date of birth
age
telephone
address
street place postcode
MDA104, Vlastislav Dohnal, FI MUNI, 2024
3
3
◼ Convert the following E-R model to a relational
model.
reader
ID No.
name date of birth
age
telephone
address
street place postcode
MDA104, Vlastislav Dohnal, FI MUNI, 2024
◼ Solution:
❑ A table for Reader:
Reader(id_no, name,
date of birth, address_street,
address_place,
address_postcode)
❑ Derived attribute is ignored
(not explicitly stored in table)
❑ Composite attribute as its
components.
❑ Multivalued attribute as a
separate table
Reader_phone(id_no, telephone)
4
4
◼ Convert the following E-R model to a relational
model.
❑ Consider the different mapping cardinality
(m-n, 1-n, n-1, 1-1).
◼ How will the relational model change?
account
id name
balancenumber
customer has
Date of change in
balance
MDA104, Vlastislav Dohnal, FI MUNI, 2024
5
5
◼ Convert the following E-R model to a relational
model.
❑ Consider the different mapping cardinality
(m-n, 1-n, n-1, 1-1).
◼ How will the relational model change?
account
id name
balancenumber
customer has
Date of change in
balance
MDA104, Vlastislav Dohnal, FI MUNI, 2024
◼ Solution: (for illustration, which
is m-n)
Customer(id, name)
Account(number, balance)
Has(customer_id, account_number,
date_of_change_in_balance_change)
❑ If cardinality is 1-m, i.e.
customer may have multiple
accounts, but account has only
one owner
Has(customer_id, account_number,
date_of_change_in_balance_change)
◼ Tables Customer, Account
remain unchanged
6
◼ Convert the following E-R model to a
relational model.
MDA104, Vlastislav Dohnal, FI MUNI, 2024 6
account
id name
balancenumber
customer has
7
◼ Convert the following E-R model to a
relational model.
MDA104, Vlastislav Dohnal, FI MUNI, 2024 7
account
id name
balancenumber
customer has
◼ Solution:
❑ Cardinality is 1-m, but account
must have an owner, so we
may optimize:
Customer(id, name)
Account(number, balance, customer_id,
date_of_change_in_balance_change)
❑ Reference to customer is
added as customer_id, which
is a foreign key to Customer
❑ No table for “has” is created!
8
8
◼ Convert the following E-R model to a relational
model.
MDA104, Vlastislav Dohnal, FI MUNI, 2024
code title
number of hours
course
has
seminar
capacitynumber
9
9
◼ Convert the following E-R model to a relational
model.
MDA104, Vlastislav Dohnal, FI MUNI, 2024
code title
number of hours
course
has
seminar
capacitynumber
◼ Solution:
❑ We have weak entity, which is
analogous to the previous slide.
Course(code, title, number_of_hours)
Seminar(code, number, capacity)
❑ Code is added to seminar, which
implements the reference to the
corresponding course.
❑ Primary is a compound of code
and number
◼ Allows having a course to
number seminars starting from 1.
10
◼ Convert the following E-R model to a relational
model.
MDA104, Vlastislav Dohnal, FI MUNI, 2024 10
course
code
seminar
title number of hours
student
učo name
number capacity
has
enrollment
selection
11
◼ Convert the following E-R model to a relational
model.
MDA104, Vlastislav Dohnal, FI MUNI, 2024 11
course
code
seminar
title number of hours
student
učo name
number capacity
has
enrollment
selection
◼ Solution:
Course(code, title, number_of_hours)
Student(učo, name)
Enrollment(code, učo)
❑ Standard conversion so far…
◼ Weak entity set:
Seminar(code, number, capacity)
❑ No table for “has”
◼ Aggregation of enrollment has
no impact to tables!
◼ Selection leads to the table:
Selection(code, number, učo)
❑ Form by attributes of PK from
connecting tables, i.e.
◼ Code, number (from seminar)
◼ Code, učo (from student)
12
12
◼ Convert the following E-R model to a relational
model.
◼ Consider different cases of
generalization/specialization constraints
❑ Disjoint vs. overlapping
❑ Total vs. partial
person
učo name
IS-A
student employee
program position salary
MDA104, Vlastislav Dohnal, FI MUNI, 2024
13
13
◼ Convert the following E-R model to a relational
model.
◼ Consider different cases of
generalization/specialization constraints
❑ Disjoint vs. overlapping
❑ Total vs. partial
person
učo name
IS-A
student employee
program position salary
MDA104, Vlastislav Dohnal, FI MUNI, 2024
◼ Solution: (general one)
❑ Tables for each entity set with
references in the specialized
ones.
Person(učo, name)
Student(učo, program)
Employee(učo, position, salary)
❑ To allow faster filtering by
“type” of person, we typically
add such an attribute, so:
Person(učo, name, type)
❑ Type contains
◼ “P” for person,
◼ “S” for student,
◼ “E” for employee, and
◼ “X” for being student and
employee at the same time.
14
14
◼ For the given relational scheme, create an E-R
diagram.
❑ Useful if you do not have any docs for an existing
system.
ssn name street place
700523/4532 Novák Petr Brněnská 25 Praha
565130/9823 Černá Lucie Kartouzská 10 Brno
customer
ssn number
700523/4532 602123569
565130/9823 549491111
700523/4532 777895364
telephone
MDA104, Vlastislav Dohnal, FI MUNI, 2024
15
15
◼ For the given relational scheme, create an E-R
diagram.
❑ Useful if you do not have any docs for an existing
system.
ssn name street place
700523/4532 Novák Petr Brněnská 25 Praha
565130/9823 Černá Lucie Kartouzská 10 Brno
customer
ssn number
700523/4532 602123569
565130/9823 549491111
700523/4532 777895364
telephone
MDA104, Vlastislav Dohnal, FI MUNI, 2024
◼ Solution:
❑ Street and place as a
composite attribute
❑ Telephone numbers as a
multivalued attribute
16
16
◼ For the given relational scheme, create an
E-R diagram.
title street place
Praha-Spořilov Brněnská 25 Praha
Brno-Královo pole Kartouzská 10 Brno
branch
number balance
P-152 3000
P-015 1700
B-094 4000
account
branch_name account_number
Praha-Spořilov P-152
Praha-Spořilov P-015
Brno-Královo pole B-094
maintains
MDA104, Vlastislav Dohnal, FI MUNI, 2024
17
17
◼ For the given relational scheme, create an
E-R diagram.
title street place
Praha-Spořilov Brněnská 25 Praha
Brno-Královo pole Kartouzská 10 Brno
branch
number balance
P-152 3000
P-015 1700
B-094 4000
account
branch_name account_number
Praha-Spořilov P-152
Praha-Spořilov P-015
Brno-Královo pole B-094
maintains
MDA104, Vlastislav Dohnal, FI MUNI, 2024
◼ Solution:
❑ Use of composite attributes is
not mandatory
❑ Mind mapping cardinality
◼ It must respect PK in the table!