Design an EER schema for the following application and translate the EER schema into relational schemas (and constraints). It is possible that some requirements cannot be captured by the EER schema and some constraints in the EER schema cannot be captured in your relational schemas. Identify these and describe them in succinct manner if there are any. List additional reasonable assumptions
if you definitely need them; listing unnecessary assumptions will be considered as changing the requirements and will be penalized.
The following narrative describes a simplified version of the organization of Olympic facilities. (This is adapted from an exercise in a text book.)
The facilities consist of a number of sports complexes.
A sports complex has a unique address, and has an area.
Sports complexes are divided into two types: the single sport type and the multi-sport type. No other types of sports complex exist.
Each multi-sport complex is partitioned into different parts; each part has a location indicator (e.g. center, NE corner, ...) indicating where it is within the complex; each such part is allocated to exactly one sport.
Each complex has exactly one chief organizing person.
Each complex will hold a number of events (e.g. different stages of swimming competitions).
Each event is identified by an event ID, and for each event there is a planed date, duration, number of participants, number of officials, and so on.
We want to keep track of which officials participate in which event. (But for simplicity we do not keep track of the sport persons in the event.) There are two types of persons that we want to keep track of: Organizing persons, and Officials. Each person is identified by a person ID, and has a name and sex.
Different equipments are needed for different events.
We are only interested in equipments that are needed by at least one event.