1
CS331 - Database Management Systems
SE Junior
ERD – Modeling
1 Modelling Mini-worlds: Getting Started
Model the following relationships in ER.
1. An apartment is located in a house in a street in a city in a country.
2. Two teams play football against each other. A referee makes sure the rules are followed.
2 International Wholesale Supplier
Model the following mini-world of an international wholesale supplier in ER. Identify the keys and give the
cardinalities of all relationships (e.g., 1:1, N:1, N:M).
The wholesale supplier has customers that place orders, which are placed on a particular date and have a total price,
current status, and an order number (starting from 1 for each customer).
In each order, a customer can order several parts (products), each in a different quantity and at a (possibly discounted)
price. We also want to model the date on which each of the parts has been sent. The parts are provided by suppliers.
Each part may be provided by several suppliers and customers may order the same part of different suppliers in the
same order, but in this case, they may have different (retail) prices. Customers and suppliers have a name, an address,
a phone number, and a customer/supplier number and they come from a certain nation, which in turn is from a
particular region (of the world). Parts have a brand, a size, and a retail price.
3 Olympics
Olympic games happen in a certain year at a certain place. Each year, there is at most one instance of Olympic games.
In each discipline of an olympic game, there is exactly one gold medalist, one silver medalist, and one bronze medalist.
All these medalists are athletes. Give an ER model for this mini-world. Identify the keys and give the cardinalities of
all relationships.
4 Travelling
Consider the following mini-world. A person has a name and an age. Cities have a name and are located in a country.
Every year, persons can form groups in order to travel together to a city. A person may be part of the same or a
different group in different years, but may be part of at most one group in any given year. Furthermore, a group travels
to the same or different city in different years, but travels to exactly one city in any given year. Give an ER model for
this mini-world. Identify the keys and give the cardinalities of all relationships (e.g., 1:1, N:1, N:M).
5 Library System
Assume there is a library system with the following properties. The library contains one or several copies of the same
book. Every copy of a book has a copy number and is located at a specific location in a shelf. A copy is identified by
the copy number and the ISBN number of the book. Every book has a unique ISBN, a publication year, a title, an
author, and a number of pages. Books are published by publishers. A publisher has a name as well as a location.
Within the library system, books are assigned to one or several categories. A category can be a subcategory of exactly
one other category. A category has a name and no further properties.
Each reader needs to provide his/her family name, his/her first name, his/her city, and his/her date of birth to register
at the library. Each reader gets a unique reader number. Readers borrow copies of books. Upon borrowing the return
date is stored. Create an ER diagram of this library system.
