INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : GBD20
Durée : 1h30 Date : 30/11/94
Documents autorisés :
ceux distribués en cours
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barême indicatif
Question 1 : 5 points
Question 2 : 3 points
Question 3 : 4 points (Q3.1 = 2 pts, Q3.2 = 2 pts)
Question 4 : 6 points (Q4.1 = 2 pts, Q4.2 = 2 pts, Q4.3 = 2 pts)
Question 5 : 2 points
MEDECIN(noimmat, nom, prénom, adresse, spécialité)
PATIENT(noss, nom, prénom, adresse, mutuelle)
MEDICAMENT(nommédic, labo, maladie)
VISITE(noviste, date, noimmat, noss)
PRESCRIPTION(novisite, nommédic)
La sémantique des diverses relations est la suivante :
MEDECIN(no, n, p, a, s) : le médecin identifié par le numéro d'immatriculation no a pour nom nom n, prénom p, habite à l'adresse a et exerce la spécialité s (qui peut éventuellement être généraliste),
PATIENT(no, n, p, a, m) : le patient identifié par le numéro de sécurité sociale no a pour nom nom n, prénom p, habite à l'adresse a et est affilié à la mutuelle m,
MEDICAMENT(n, l, m) : le médicament identifié par son nom n est fabriqué par le laboratoire l et traite la maladie m,
VISITE(no, d, nm, np) : une visite de numéro d'identification no a été faite par le médecin de numéro nm auprès du patient np à la date d,
PRESCRIPTION(no, n) : lors de la visite de numéro no, le médicament de nom n a été prescrit.
En vous aidant des règles de passage d'une modélisation Entité/association à une modélisation relationnelle "à l'envers", proposer un schéma Entité/Association équivalent à ce schéma relationnel. Ce schéma devra comporter la description des entités (avec leurs propriétés), des associations (avec leurs propriétés) ainsi que les cardinalités minimum et maximum des associations binaires.
En étudiant le schéma relationnel, on s'aperçoit que la définition des relations MEDECIN et PATIENT présente des éléments communs.
2-1 Proposer une modification de votre modélisation Entité-Association qui tienne compte de cette remarque en utilisant des liens de généralisation/spécialisation.
2-2 Donner ensuite la traduction de cette partie vers le modèle relationnel.
Attention, les questions 3 et 4 doivent être exprimées sur le schéma relationnel donné dans l'énoncé et non celui que vous avez proposé dans la question 2.
Exprimer chacune des 2 questions suivantes, d'une part en algèbre relationnelle sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q3.1) Donner les noms des médicaments qui ont été prescrits au moins une fois au patient de nom `Dupond'.
(Q3.2) Donner l'adresse des médecins qui ont fait au moins une visite au patient de nom `Dupond'.
Exprimer les 3 questions suivantes en SQL :
(Q4.1) Donner le nombre de visites effectuées par le médecin de nom `Martin'.
(Q4.2) Donner pour chaque patient le nombre total de visites qu'il a subies.
(Q4.3) Donner le nom du médicament le plus prescrit.
Soit le schéma relationnel M(NO, N, P, A, S) avec les dépendances fonctionnelles suivantes :
{ NO -> N; NO -> P; A -> S; NO -> S; NO -> A }
Dans quelle forme normale est ce schéma ?