Cairo University

Faculty of Computers and Information

Assignment
(ERD)
Question-1:
Design the conceptual and physical models for the Olympic Games. If you don't think
enough information is available, do your assumptions that make it clear.
The database should store data about all sports, disciplines and competitors. For each sport, the
database should contain its name, code and all the disciplines it includes. For each discipline, the
database stores a unique name, date of events scheduled for the discipline, the world record, the
best result achieved during the games and the continental records for that discipline (including
the name of the continent and the result). For each competitor, the database stores his/her name,
the country he/she competes for, unique number, gender and birth date. For each discipline a
competitor is listed for, we store his/her own record and for each event the competitor
participates in we store his/her start number and the result achieved. The database should store
the following information about a judge: number (unique), name, the country he/she comes from,
and the list of events in which the judge referees. There is additional data for each country like
its name.

Question-2:
Design the conceptual and physical models for the Publishing Company. If you don't think
enough information is available, do your assumptions that make it clear.
A publishing company produces scientific books on various subjects. The books are written by
authors who specialize in one particular subject. The company employs editors who, not
necessarily being specialists in a particular area, each take sole responsibility for editing one or
more publications. A publication covers essentially one of the specialist subjects and is normally
written by a single author.When writing a particular book, each author works with on editor, but
may submit another work for publication to be supervised by other editors. To improve their
competitiveness, the company tries to employ a variety of authors, more than one author being a
specialist in a particular subject.

1
Question-3:
Design the conceptual and physical models for the Car Rental Company. If you don't think
enough information is available, do your assumptions that make it clear.
A database is to be designed for a Car Rental Co. (CRC). The information required includes a
description of cars, subcontractors (i.e. garages), company expenditures, company revenues and
customers. Cars are to be described by such data as: make, model, year of production, engine
size, fuel type, number of passengers, registration number, purchase price, purchase date, rent
price and insurance details. It is the company policy not to keep any car for a period exceeding
one year. All major repairs and maintenance are done by subcontractors (i.e. franchised garages),
with whom CRC has long-term agreements. Therefore the data about garages to be kept in the
database includes garage names, addresses, range of services and the like. Some garages require
payments immediately after a repair has been made; with other CRC has made arrangements for
credit facilities. Company expenditures are to be registered for all outgoings connected with
purchases, repairs, maintenance, insurance etc. Similarly the cash inflow coming from all sources
- car hire, car sales, insurance claims - must be kept of file. CRC maintains a reasonably stable
client base. For this privileged category of customers special credit card facilities are provided.
These customers may also book in advance a particular car. These reservations can be made for
any period of time up to one month. Casual customers must pay a deposit for an estimated time
of rental, unless they wish to pay by credit card. All major credit cards are accepted. Personal
details (such as name, address, telephone number, driving license, number) about each customer
are kept in the database.

Question-4:
Design the conceptual and physical models for the Coca Cola Company. If you don't think
enough information is available, do your assumptions that make it clear.
The Coca Cola Company in Atlanta, Georgia produces a wide range of products that are
delivered to its worldwide clients once a week. The company stores information about its
employees, products, and customers in a database that includes the following set of tables:
a. The company records the following information about its customers: customer identification
number, name, address, X (longitude) and Y (latitude) coordinates of their location, and the
amount of time (in fractions of an hour) required making a stop at that location.

2
b. Each employee has an employee identification number, name, address (which consists of a
city, state, and zip code), gender, birth date, position in the company, wage earned per hour of
regular time work, wage earned per hour of overtime work, number of dependents, and
number of years worked for the Coca Cola Company.
c. Each product has a product identification number, price, and number of units produced per
day. Products may be ordered by one or more customers, and a customer may order one or
more products. Furthermore, employees produce one or more products, and a product may be
produced by exactly one employee.

Question-5:
Design the conceptual and physical models for the Hotel System. If you don't think enough
information is available, do your assumptions that make it clear.
One of the three-star hotels in the Miami area is in the process of updating its database. The hotel
has various room types on each of its floors. The rooms may be regular, deluxe, or a suite and
each can be either a single, double, or triple. The suites have ocean views and are bigger than the
regular rooms. The deluxe rooms are as big as suites, but they do not have an ocean view. All the
rooms have air conditioning. Most of the rooms are non-smoking, but the hotel offers some
smoking rooms as well. Each floor has a different number of a particular room type. The price of
each room differs by the size of the room, the view, and the room’s location (first floor, second
floor, etc.). The customers are charged on a per-day basis. The number of days is computed
based on the check-in time and the check-out time. The following details are stored for each
customer: name, address, check-in date, check-out date, payment method, and final bill amount.
In addition to the room charges, there may be extra fees, such as telephone usage, fax services,
extra beds, and room service. For this application, we assume that a room can be booked by more
than one customer as long as there is no overlap and that a customer can be assigned to any
available room.

3
Question-6:
Design the conceptual and physical models for the Blood Bank. If you don't think enough
information is available, do your assumptions that make it clear.
A blood bank serves a critical purpose in providing a required type of blood to patients at critical
times. A blood bank’s database monitors the inventory of the blood together with relevant
information such as blood type, date received, location, date of expiry, and donor. The database
stores information such as name, address, and telephone number for a blood bank.
Supplementary information about the donors is recorded as well. Donors are classified into
occasional and regular donors. For the regular donors, the database keeps information such as
identification number, blood type, and history of donations. The database also keeps a list of
healthcare providers in the area along with their addresses and telephone numbers. The
healthcare providers are the customers of the blood bank. They keep track of the blood
transactions performed. These transactions are classified into normal transactions and
unexpected transactions (for example, due to car accidents during the holiday season). The
reason for keeping track of the unexpected transactions is to use this information to estimate the
extra amount of blood needed in the inventory for each age group during the next holiday season.
A blood bank receives a particular bag of blood from exactly one donor. The blood bank then
distributes the blood to health care providers.

Deliverable rules:
 The assignment MUST be solved individually.
 The assignment MUST be solved using Power Designer and printed.
 Submission of assignment is at labs of the week starting on 2 May 2015 at your lab time.
 Assignment copies will be given ZERO.