INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : BD22
Durée : 1h30 Date : 12/12/95
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.
NB : EXAMENS est faible par rapport à SEJOURS car la clé
de la relation EXEMENS comprend la clé de la relation
SEJOURS.
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.
(Q2.1) Donner le type d'appareil utilisé ainsi que le
résultat des examens suivis par le patient de nom Defude.
projection(appareil, résultat,
jointure(PATIENTS.noss=SEJOURS.noss,
sélection(nom=Defude,PATIENTS),
jointure(EXAMENS.noséjour=SEJOURS.noexamen,
EXAMENS,
SEJOURS)
)
)
SELECT distinct appareil, résultat
FROM PATIENTS P, EXAMENS E, SEJOURS S
WHERE P.noss = S.noss AND S.noséjour = E.noséjour AND
nom=Defude
(Q2.2) Donner le nom des patients pour lesquels le médecin de
nom Augier a pratiqué un examen.
projection(PATIENTS.nom, jointure(MEDECINS.noordre=EXAMENS.noordre,
sélection(nom=Augier, MEDECINS),
jointure(EXAMENS.noséjour=SEJOURS.noséjour,
jointure(SEJOURS.noss=PATIENTS.noss,
SEJOURS,
PATIENTS),
EXAMENS)
)
SELECT DISTINCT P.nom
FROM PATIENTS P, SEJOURS S, EXAMENS E, MEDECINS M
WHERE M.nom=Augier AND P.noss=S.noss AND
S.noséjour=E.noséjour AND E.noordre=M.noordre
Question 3
Exprimer les 4 questions suivantes en SQL :
(Q3.1) Donner le nombre d'examens pratiqués sur le patient de
nom Defude.
SELECT COUNT(*)
FROM PATIENTS P, SEJOURS S, EXAMENS E
WHERE P.noss=S.noss AND S.noséjour=E.noséjour AND P.nom=Defude
(Q3.2) Donner pour chaque médecin le nombre total d'examens
pratiqués.
SELECT noordre, COUNT(*)
FROM EXAMENS
GROUP BY noordre
(Q3.3) Donner le nom du médecin qui a pratiqué le plus
grand nombre d'examens.
SELECT M.nom
FROM MEDECINS M, EXAMENS E
WHERE E.noordre=M.noordre
GROUP BY P.noordre, P.nom
HAVING COUNT(*) = (SELECT MAX(COUNT(*))
FROM EXAMENS
GROUP BY noordre)
NB : une variante consiste à utiliser >= ALL (SELECT COUNT(*)...
à la place de = (SELECT MAX(COUNT(*))...
Attention dans le GROUP BY externe, pour pouvoir sélectionner M.nom on
doit le rajouter dans le GROUP BY!!!
(Q3.4) Donner le nom des patients qui pour chacun de leurs
séjours ont eu au moins trois examens.
C'est une question que l'on va traiter en répondant d'abord à son
complémentaire, c'est à dire chercher les patients qui ont eu au
moins un séjour pendant lequel ils ont eu moins de 3 examens.
SELECT P.nom
FROM PATIENTS P, SEJOURS S, EXAMENS E
WHERE P.noss=S.noss AND S.noséjour=E.noséjour
MINUS
SELECT P.nom
FROM PATIENTS P, SEJOURS S, EXAMENS E
WHERE P.noss=S.noss AND S.noséjour=E.noséjour
GROUP BY P.noss, P.nom, S.noséjour
HAVING COUNT(*) < 3
Là encore bien faire attention à l'inclusion des attributs du
SELECT dans les attributs du GROUP BY.
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle
ou en SQL :
(Q4.1) Donner le numéro d'ordre des médecins qui ne font
des examens que sur le même appareil.
projection(noordre, MEDECINS) -
projection(M1.noordre,
jointure(M1.noordre=M2.noordre AND M1.appareil!=M2.appareil,
MEDECINS M1, MEDECINS M2))
NB : on chercher les médecins qui ont pratiqués deux examens sur
deux appareils différents (c'est la jointure) et on les soustrait
à l'ensemble des médecins. Cela nous donne bien ceux qui
pratiquent toujours sur le même appareil.
SELECT M.nom
FROM MEDECINS M, EXAMENS E
WHERE M.noordre=E.noordre
GROUP BY M.noordre, M.nom
HAVING COUNT(DISTINCT appareil) = 1
Avec SQL on peut proposer une autre solution en utilisant le GROUP BY. Il faut
bien penser à utiliser le DISTINCT dans la clause HAVING et toujours
bien faire attention à ce que les attributs du SELECT soient
présents dans le GROUP BY.
(Q4.2) Donner le numéro de sécurité sociale des
patients qui ont eu au moins un examen sur chaque appareil.
division(projection(SEJOURS.noss, appareil,
jointure(SEJOURS.noséjour=EXAMENS.noséjour, EXAMENS, SEJOURS))
projection(appareil, EXAMENS))
Le premier argument de la division calcule l'ensemble des couples (noss,
appareil). On divise ensuite par l'ensemble des appareils et on obtient les
noss qui ont eu un examen (au moins) sur chaque appareil.
SELECT S.noss
FROM EXAMENS E, SEJOURS S
WHERE E.noséjour=S.noséjour
GROUP BY S.noss
HAVING COUNT(DISTINCT appareil) = (SELECT COUNT(DISTINCT appareil)
FROM EXAMENS)
Là encore bien penser à utiliser le DISTINCT.
Posté le 4 septembre 2009