MDA104: Tutorial 1
E-R Model
Vlastislav Dohnal
MDA104, Vlastislav Dohnal, FI MUNI, 2024 2
2 E-shop
Assignment:
◼ Create an E-R model for a registry of customers of a company trading on internet.
❑ We need to keep a record of the customer's name, phone numbers, shipping address and
customer’s age.
◼ Consider multiple phone numbers per customer.
Expected result:
◼ The result should contain a single entity set.
❑ Mark the primary key.
◼ SW tools in MU IS:
❑ ERD editor - https://disa.fi.muni.cz/projects/MDA104/erd/index_chen.html
◼ Accessible from MU intranet or via MU VPN
❑ ROPOT in IS – see MD104 e-learning section
MDA104, Vlastislav Dohnal, FI MUNI, 2024 3
3 E-shop
Assignment:
◼ In the previous example, add information about customer accounts.
❑ The account is used to record the credit balance.
Solve step by step:
1. First, consider that each customer has exactly one account.
❑ What are the advantages/disadvantages of this solution?
2. Now consider that multiple customers can share the same (joint) account.
❑ Discuss cardinality of relationships when using two entity sets.
❑ Record the date of change in the credit balance.
◼ Hint: You may add an attribute to a relationship.
3. Add recording individual transactions changing the account balance.
❑ transaction number, description, date and time, amount
4 University system
Assignment:
◼ Design an E-R model for a registry of
courses (name, code, number of hours)
seminar groups (number, capacity)
◼ The designation of courses and groups is the same as in the IS MU.
❑ Example: The course code is PB168. The group number is PB168/01.
❑ A seminar group cannot exist without a course.
❑ There can be courses that do not have any seminar group.
Solve step by step:
1. Apply totality to a relationship
❑ Determine the primary keys of entity sets.
2. Use a weak entity set.
❑ Determine the primary keys of entity sets.
MDA104, Vlastislav Dohnal, FI MUNI, 2024 4
5 University system
Assignment:
◼ To the previous E-R model, add the
entity set student (učo, name).
◼ We want to model:
❑ The student enrolls in courses.
❑ The student registers for seminar
groups.
❑ He/she can choose a maximum of
one group for a given course.
MDA104, Vlastislav Dohnal, FI MUNI, 2024 5
student
učo name
course
code
title teaching_hours
has
group
number capacity
*učo == University Personal Number (ID)
MDA104, Vlastislav Dohnal, FI MUNI, 2024 6
6 University system
Assignment:
◼ For the previous model, use
aggregation to solve the redundancy
problem.
❑ The student enrolls in courses.
❑ The student registers in groups.
student
učo name
course
code
title teaching_hours
has
group
number capacity
7 University system
Assignment:
◼ Add the teacher of the course to the previous model.
❑ The teacher as a course’s lecturer and a teacher as a seminar instructor.
◼ Also consider the situation that even a student can lead a seminar...
❑ Is it possible to use generalization/specialization?
Assignment:
◼ Next, add the prerequisites of the courses, i.e. this course has another subject in its
prerequisites
❑ Can "roles" be used?
MDA104, Vlastislav Dohnal, FI MUNI, 2024 7
8 Marketing campaigns
◼ Design a marketing campaign database
❑ Manage information about campaigns, target audiences, marketing channels, and
campaign results.
◼ Create an ERD for this scenario.
❑ decide what entity sets you create and what relationship among them you create.
MDA104, Vlastislav Dohnal, FI MUNI, 2024 8