Tutoriel de Bases de Données Relationnelles

Corrigé du controle pour les EI 95/96

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.