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
Question 1
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.
Question 2
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.
NB : on aurait pu également factoriser les numéros
d'immatriculation et de sécurité sociale au niveau de PERSONNE
mais cela supposerait qu'ils ont même format ce qui n'est pas
sûr.
2-2 Donner ensuite la traduction de cette partie vers le modèle
relationnel.
Nous traduisons le lien de généralisation/spécialisation
par des relations de base et une vue :
MEDECIN(noimmat, spécialité, nom, prénom, adresse)
PATIENT(noss, mutuelle, nom, prénom, adresse)
vue PERSONNE comme
SELECT nom, prénom, adresse
FROM MEDECIN
UNION
SELECT nom, prénom, adresse
FROM PATIENT
Par rapport au schéma initial la seule différence est donc
l'ajout de la vue PERSONNE.
Question 3
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'.
projection(nommédic, jointure(PRESCRIPTION.novisite=VISITE.novisite,
PRESCRIPTION,
jointure(VISITE.noss=PATIENT.noss,
VISITE,
sélection(nom=Dupond, PATIENT))
)
)
SELECT DISTINCT nommédic
FROM PRESCRIPTION P, PATIENT PA, VISITE V
WHERE P.novisite=V.novisite AND V.noss=PA.noss AND PA.nom=Dupond
NB : le même médicament pouvant être prescrit plusieurs fois
lors de visites différents à un patient, la clause DISTINCT est
nécessaire en SQL.
(Q3.2) Donner l'adresse des médecins qui ont fait au moins une
visite au patient de nom `Dupond'.
projection(adresse, jointure(MEDECIN.noimmat=VISITE.noimmat,
MEDECIN,
jointure(VISITE.noss=PATIENT.noss,
VISITE,
sélection(nom=Dupond, PATIENT)
)
)
)
SELECT DISTINCT adresse
FROM MEDECIN M, VISITE V, PATIENT P
WHERE M.noimmat=V.noimmat AND P.noss=V.noss AND P.nom=Dupond
NB : Même remarque que pour la question précédente
concernant le DISTINCT. Il faut noter que le "au moins une visite" se traduit
simplement par le fait que l'on peut joindre un tuple de VISITE avec un tuple
de PATIENT.
Question 4
Exprimer les 3 questions suivantes en SQL :
(Q4.1) Donner le nombre de visites effectuées par le
médecin de nom `Martin'.
SELECT COUNT(*)
FROM MEDECIN M, VISITE V
WHERE M.noimmat=V.noimmat AND nom=Martin
(Q4.2) Donner pour chaque patient le nombre total de visites qu'il a
subies.
SELECT noss, COUNT(*)
FROM VISITE
GROUP BY noss
(Q4.3) Donner le nom du médicament le plus prescrit.
SELECT nommédic
FROM PRESCRIPTION
GROUP BY nommédic
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM PRESCRIPTION
GROUP BY nommédic)
NB : la clause HAVING peut également s'écrire COUNT(*) >=
ALL (SELECT COUNT(*) FROM ...
Question 5
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 ?
Tout d'abord il faut calculer la fermeture transitive de ce schéma.
Ici aucune DF nouvelle n'est engendrée (la seule qui peut être
obtenue par transitivité de NO donne A et A donne S est
déjà présente).
Ensuite il faut extraire la (ou les) clés potentielles.
Ici la clé est NO et est unique.
Les attributs clés sont NO et les attributs non clés les
autres.
Le schéma est en 1FN puisqu'il respecte le modèle relationnel, il
doit être en 2FN si les DF de type clé vers attribut non
clé sont élémentaires. Ici la clé est mono-attribut
donc les DF sont forcément élémentaires. Le schéma
est donc en 2FN.
Il est en 3FN si il n'y a pas de DF entre attributs non clés. Ici on a A
donne S donc le schéma n'est pas 3FN mais 2FN.
Posté le 4 septembre 2009