The following corrected exercises concern the Association Entity model, construction, modeling and understanding based on use cases.
Exercise 1
Below is the E/A diagram representing visits to a medical center. Answer the following questions (strictly according to what is indicated in the diagram).
1. Can a patient have multiple consultations?
2. Can a doctor see several patients in the same consultation?
3. Can several medications be prescribed in the same consultation?
4. Can two different doctors prescribe the same medication?
- Yes => (1,n) to assist
- No => consultation to assistance (1,1)
- Yes => consultation to prescribed (0,n), Y to n medications are prescribed in a consultation
Yes => Nothing prohibits it in the diagram. A doctor gives consultations, medications are prescribed but no link between the medication and the prescribing doctor No information on this point without writing constraints in addition to the diagram.
Exercise 2
Below is the E/A diagram representing a tennis tournament. Answer the following questions (strictly based on what is shown in the diagram)
- Can we play doubles matches?
- Can a player win a match without having played it?
- Can there be two matches on the same field at the same time?
- How to prevent 2 matches at the same time on the same field?
- No, only singles matches => match to participate (2,2) 2 players only
- Yes, nothing prohibits it in the scheme, no link between participating and winning.
It is necessary to add an integrity constraint which cannot be expressed in the model
- Yes according to the schema => schedule is a property of Match only
- The schedule becomes a property of (match, field) therefore a property of the association
Exercise 3
A group of airlines working on European territory wishes to organize a common information system. Each company organizes flights between the cities it serves.
A company is defined by its name and a flight is identified by a flight number (AF234 for example)
- Write the corresponding E/A diagram
- Complete the diagram taking into account the following information:
The flights are chartered on planes that it owns or leases to other companies in the group.
An aircraft is identified by an aircraft number and is of a defined type (Airbus A340) for example 3. Integrate into your diagram the following need (for monitoring of its personnel) expressed by the airlines
Who flew on which flight and what was their role (Captain, Flight Pilot, Mechanic, Cabin Crew)?
For pilots, it is necessary to integrate the notion of rank and qualification of pilots for this or that type of aircraft.
Exercise 4
Construct the relational diagram of the previous cases:
- Medical Center
- Tennis tournament
- Airline company
Medical Center
Medicine ( Coded , Wording )
Doctor ( Registration number , Name )
Patient ( SS No. , Name )
Consultation ( No. , Date ,#N°SS , #Matricule )
Prescribed (#No., #Code , NbSockets )
Tennis tournament
Player (N°J , Name )
Match (N°M, Schedule, #N°T, #N°J1, #N°J2, #N°G)
Ground ( No. T , Surface )
Airline company
company ( nameC , nationality, address)
flights ( numFlight , day , time , cname , #departureVname , #arrivalVname , #devicenum )
planes ( numDevice , yearFab, #model)
cities ( nameV , country )
aircraft_type ( model , numberPassenger )
staff ( numSS , name, function)
rent (#numC , #numDevice , startdate , enddate )
has (#numC , #num_device , datePurchase )
uses (#numC ,#numSS , HireDate )
has_for_function (#numVol , #numSS , functionOnFlight )
is_qualified (#numSS, #model , grade )
Exercise 5
We want to create a hospital information system. The objective is to be able to answer the following questions:
- Which doctor works in which department?
- Which doctor follows which patient? and reciprocally ?
- Which department was a patient admitted to?
- Allow monitoring of prescriptions given to a patient (what and by whom, etc.)
In this hospital, there are several departments, each identified by name;
Each doctor is attached to a department;
A patient is admitted to a department and never changes it;
A patient can be followed by several doctors (potentially from different departments).
The information we want to have in the IS is:
- service : specialty, number_beds, location (building-floor)
- mdoctor : name, num_telephone, num_inscription_order, specialty
- patient : name, num_SS, pathology, date_entry, date_exit
- prescription : patient, doctor, medication, dosage, date
You will add all the information that seems necessary to you to construct the E/A diagram and you will write the E/A diagram of the hospital's IS then the relational diagram.
Hospital ( nameH:varchar , telephoneH:varchar)
Services (nameS:varchar , specialty: varchar, nbre_beds: int, #nomH: varchar, #num_floor: int)
Doctors (order_num: int, telephone_num: varchar, medical_specialty: varchar, #nomH: varchar, #nomS: varchar)
Patients ( num_SS , P_name )
Hospitalized ( #num_SS , #nomS , entry_date , exit_date )
Neat ( #num_order ,# num_SS , pathology)
Floor ( number_floor, #name_building )
Building ( building_name , number of floors)
Consultation (number_Consultation, date_Consultation, #num_order, #num_ss)
Prescribed (number_Consultation, drug_number, dosage_Prescribed)
Medicine (drug_number, libelle_Medicament)
Exercise 6
The head of the GG master's degree wants to develop a web application for managing information on students registered in the option and former students of the option.
The information to be kept is contact details (surname, first name, address, email, password, withdrawal or suspension), progress in the course (course taken, and grades as well as courses to be taken), information on the dissertation ( title, director(s), readers, date obtained, note). For former students we would also like to keep contact details and current and former jobs.
Modifications to data are governed by the following rules:
– The person responsible for the option can modify all data;
– Students can update their contact details, the course to be followed and the grades obtained in the courses taken (the system is not connected to the School system and the grades are there only for the information of the person responsible for the course option) as well as information on their jobs.
The system must remember the date of the last modification to the course made by a student so that the manager can consult the modifications.
Only the option manager and the student concerned can view information concerning the course.
We would like to be able, for authorized persons, to send an email to current students and/or former students.
We also wish to publish, for the general public, the directory of alumni of the DataScience option as well as the list of students currently in the course.
You will add all the information that seems necessary to you to construct the E/A diagram and then the relational diagram.
Be careful not to want to write everything in the E/A model, reading, writing and modification rights will be set up according to user profiles
Sending emails is not part of the data structures but of the actions to be carried out. You just have to make sure that the data will be there to make it happen.
EN 
FR 
ES