NATIONAL POLYTECHNICAL SCHOOL

Department of Preparatory Classes

2nd year
Academic year 2024/2025

Course: Computer Science 4

Responsible: Mme BENALIA

Directed work series N° 1

Data Modelling Using the Entity - Relationship (ER) Model

Objective: Interpret and develop Entity/Association (E/As) diagrams, study cardinalities, and construct
the logical relational model of the data.

Exercise 01
1. You are given an E/A diagram (Figure 1) representing visits to a medical center. Answer the
following questions based on the characteristics of this diagram (i.e.: indicate whether the
situation described is representable, regardless of its plausibility).

Fig. 1 – Medical Center association entity model.

a. Can a patient make several visits?

b. Can a doctor receive several patients in the same consultation? (one patient per
consultation).
c. Can several medications be prescribed in the same consultation?
d. Can two different doctors prescribe the same medication?

2. Here is the E/A diagram (Figure 2) of the (very simplified) information system of a dailynewspaper:

1/3
 Fig. 2 – Information system of a daily newspaper association entity model.

1. Can an article be written by several journalists?

2. Can an article be published several times?
3. Can there be several articles on the same subject in the same issue?
4. Knowing an article, do I know the newspaper where it appeared?

Exercise 02
The following is a description of some data requirements for a chain of pharmacies. Draw the appropriate
entity-relationship (E-R) diagram. Clearly show all cardinality constraints, cardinality limits, and
existence dependencies.
(a) A pharmaceutical company manufactures one or more drugs, and each drug is manufactured and
marketed by exactly one pharmaceutical company.
(b) Drugs are sold in pharmacies. Each pharmacy has a unique identification. Every pharmacy sells one
or more drugs, but some pharmacies do not sell every drug.
(c) Drug sales must be recorded by prescription, which are kept as a record by the pharmacy. A
prescription clearly identifies the drug, physician, and patient, as well as the date it is filled.
(d) Doctors prescribe drugs for patients. A doctor can prescribe one or more drugs for a patient and a
patient can get one or more prescriptions, but a prescription is written by only one doctor.
(e) Pharmaceutical companies may have long-term contracts with pharmacies and a pharmacy can
contract with zero, one, or more pharmaceutical companies. Each contract is uniquely identified by a
contract number.

Exercise 03
At the assistant principal's, Current information about a student is collected.
• A student is always registered in a class identified by a code.
• Each class has a homeroom teacher.
• A teacher teaches one or even several subjects, even in the same class.
• The contact details of a teacher must be known.

Present the Conceptual Data Model (CDM) then the Relational Data Model.
name 7aja
number

1 n 1:n n m
1:1 0:n 0:m
STUDENT Regestred_in
CLASS teach_in TEACHER
1:m

code
2/3
name_s n m
1:n
homeroom_teacher SUBJECT teach
Exercise 04
A house and apartment rental agency wants to manage its list of accommodations. She would indeed
like to know the location of each accommodation (name of the municipality and district) as well as the
people who occupy them (the signatories only).

The rent depends on the accommodation, but depending on its type (house, studio, T1, T2, etc.) the
agency will always charge its clients the same lump sum in addition to the rent. For example, the price
of a studio will always be equal to the price of the rent + 3000 DA in fixed charges per month.

For each accommodation, we also want to have the address, the surface area and the rent. As for the
individuals who occupy the accommodation (the signatories of the contract only), we will only
provide their surnames, first names, date of birth and telephone number.

For each municipality, we want to know the number of inhabitants as well as the distance separating
the municipality from the agency.

The agency wishes to manage the history of housing occupancy by individuals; for each rental, we
record the start date and the end date.

We will also consider that an individual can be a signatory to several rental contracts.
It is also specified that accommodation can be the subject of several separate rentals over time. The
geographical unit used for housing management is the district and each municipality is considered to
have at least one district.

From this text, construct the Conceptual Data Model (CDM) then the Relational Data Model.

Additional exercises

Exercise 05
Construct an E-R diagram for a car-insurance company whose customers own one or more cars each.
Each car has associated with it zero to any number of recorded accidents.

Exercise 06
Construct an E-R diagram for a hospital with a set of patients and a set of medical doctors. Associate
with each patient a log of the various tests and examinations conducted.

Exercise 07
A university registrar’s office maintains data about the following entities: (a) courses, including number,
title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester,
section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and
program; and (d) instructors, including identification number, name, department, and title.
Further, the enrolment of students in courses and grades awarded to students in each course they are
enrolled for must be appropriately modelled.
Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the
mapping constraints.

Exercise 08
A driving school wants to build a database to manage its students' theoretical highway code exams. Each
student is identified by a unique number and is characterized by a surname, first name, address and date

3/3
of birth. Each student attends several coding sessions (as many as they want). Each session is
characterized by a date and time. At each coding session, the driving school director chooses a series of
questions on a CD-ROM.

Each CD-ROM is identified by a number and is characterized by a publisher name. Each CD-ROM is
made up of 6 series, numbered from 1 to 6. Each series is made up of 40 questions. Each question is
identified by a title and is characterized by an answer, a level of difficulty and a theme. The same
question can appear in several series with a sequence number for each series; for example, the same
question can appear as question N ◦ 2 in series 5 of CD-ROM 15 and as question N ◦ 12 in series 3 of
CD-ROM 4. The same series can be shown several times at different sessions. When a student attends
a session, he obtains the number of mistakes (a mark out of 40) that he made for the series passed during
the session.

When a student has obtained, during the last four sessions he has attended, a number of faults less than
or equal to 5, the director of the driving school authorizes him to take the theoretical examination of the
highway code on a given date (only one exam for a given date). The driving school can only present a
maximum of 8 students on each exam date. Students who obtain more than 5 mistakes on the exam are
failed and must attend coding sessions again before being able to take the exam again.

From this text, construct the Conceptual Data Model (CDM) then the Relational Data Model.

4/3