ERD_&_mapping_lab
(Note that the Identification number and Code are unique identifiers)
1. Intermediary Car rental company:
An intermediary car rental company has branches across the city; intended
to construct a system to facilitate its transactions. You are asked to build an
ER diagram and mapping based on the following information.
● The company has many offices to serve a big sector; each office has an ID,
address and a contact no.
● The owner who is willing to provide his car/s for rent to the office needs to
register his full name, national ID as unique identifier and the bank account
number to which the company will transfer the money to.
● The owner must apply for car renting in only one office, but an office can
have many owners.
● Data about car is ID, model (brand, model name, model year), copy of a
valid license, rent amount and car status (Ex: the car status is booked, in
maintenance or available)
● Owner can provide one or more cars to rent. In addition, it is not allowed to
save a car information that is not related to specified owner. An owner
must have at least one car to be registered in the system.
● In regard to the tenant (the person who pays rent for car usage); some
information needs to be saved, his National ID as a unique identifier, a copy
of a valid driving license to secure the car and a mobile number as a
contact.
● A tenant should rent one or more cars and a car can be rented by many
tenants.
● When a tenant orders a car, some information about this transaction must
be recorded such as the start date for renting the car, the duration, and
payment method.
2. XX medical analysis laboratory
A medical analysis laboratory decided to design a system to facilitate data
gathering and hence improve the service quality to reach the maximum result
accuracy and patient satisfaction accordingly.
You are asked to draw an ER diagram and mapping based on the following
information.
● The laboratory has branches in different locations; each branch has a code,
short name, detailed address composed of city, street, and building no.
● There is a group of medical tests, which the branch provides.
Each test has a code, name, cost, duration that each test takes until the
result is ready.
● When the patient visits the branch to perform a test or more, there are
some data needed about this patient to record by the system: patient ID,
full name, age, mobile number, date of birth, gender, and chronic diseases.
● The system needs to save additional information when a patient visited a
certain branch to perform specific test/s, this info is the date and time of
performing the test.
● Finally, the system has to keep track of the employees working in each
branch by storing employee ID, full name, salary, team (which employees
belonging to ex: chemists’ team, clean workers team, accountants’ team,
doctors team, etc.) and also we record the start and end time the
employees spent to keep track of the duration (Ex: fixed hours from 8:00
am to 5:00 pm)
● . Each employee must be assigned in only one branch and each branch may
have many employees.
● In addition, the direct supervisor of each employee has to be considered (if
any).
● Every branch must have only one manager and a manager is not allowed to
manage more than one branch.
3. XYZ take-away Restaurants:
XYZ, a take away restaurants chain needs to build a database System that
helps to capture transactions with accuracy and run every day processes
more efficiently. You are asked to draw an ER diagram and mapping based
on the following information.
● There are many restaurant branches, each branch has an Identification
number beside its address and contact number.
● Each branch has its own staff members, which belong to this branch, one
member of this staff acts as the branch manager. Each branch must have
one manager.
● Each Staff member described by the employee’s full name, ID, age, address
that is described as (city and region), role, joining date and monthly salary.
● The system is required to track the customers’ information. Each customer
has a code on the system, name, and a contact number.
● The system is asked to record the menu items provided by each branch.
Data about menu item is the code, item name, unit price, item category
(e.g. pizza, beverage ...).
● In addition, it is important to track the orders information placed by
customers; each order has an ID, desired address to send the order to, total
bill as well as the items ordered. The date and time of the placed order are
important information to save.
● Each order has one or more menu items.
● Some information needs to be stored about the delivery boys, the national
ID number, contact number, and names, to communicate in case there are
orders required to be delivered.
4. International school database system:
A well-known international school seeks to develop a database for its
system to ease the data handling process. You are asked to build an ERD
and mapping based on the below information.
● Each student of the school has an application (only one) with a unique
serial number, furthermore the division in which the student joins
(example: American, National or British division).
● Each student is asked to fill in a medical report form with the
commencement of joining the school. The medical report has data of
weight, length and if the student suffering from any disease/s. There is no
need for the student’s medical report if he/she left our school.
● Every student has his/her own medical report to help in tracking and caring.
● Data about the student are Serial Number, full name, age, gender, date of
birth and a copy of completion certificate of his/her last academic year,
besides; the school creates an email for each student which has to be
added to his record.
● Some students may have siblings in our school; the system has to save this
piece of information to consider sibling discount percent.
● The sibling discount recorded.
● The system is required to save information about parents. Parent ID, name,
relationship (father/mother).
● On the other hand, the system has to keep track of teacher entity which
has attributes; teacher ID, name, subject, phone number, email address and
monthly salary.
● Also there is a senior teacher for each group of teachers which teach
certain subject, acts as a subject head.
● Each group of students attends in certain class. In addition, the class is
dedicated only for them. The class has a unique name, location (building
no, floor no), class capacity and attached schedule.
● The system has to save which teacher joins which class.
