Tutoriel de Bases de Données Relationnelles

Corrigé du controle pour les EM 94/95

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.