Tutoriel de Bases de Données Relationnelles
Corrigé du controle pour les EM 95/96
INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : GBD20
Durée : 1h30 Date : 15/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.
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 les noms et prénoms des assurés dont un contrat arrive à expiration le 31-12-95.
NB : Bien penser à la clause DISTINCT un assuré pouvant avoir plusieurs contrats se terminant à la même date.
projection(nom, prénom,
jointure(ASSURES.noassuré=CONTRAT.noassuré,
sélection(datefin=311295, CONTRAT),
ASSURES)
SELECT DISTINCT A.nom, A.prénom
FROM ASSURES A, CONTRAT C
WHERE A.noassuré=C.noassuré AND datefin=311295
(Q2.2) Donner les dommages couverts par les différents contrats d'assurances de l'assuré de nom Defude.
NB : ici le DISTINCT est discutable, on peut espérer que les différents contrats pris par un même assuré ne se recouvrent pas en termes de risques couverts!
projection(dommage, jointure(RISQUES.nopolice=CONTRAT.nopolice,
RISQUES,
jointure(ASSURES.noassuré=CONTRAT.noassuré,
sélection(nom=Defude, ASSURES),
CONTRAT)
)
)
SELECT DISTINCT dommage
FROM ASSURES A, CONTRAT C, RISQUES R
WHERE A.noassuré=C.noassuré AND C.nopolice=R.nopolice AND
nom=Defude
Question 3
Exprimer les 4 questions suivantes en SQL :
(Q3.1) Donner le le prix payé par l'assuré de nom Defude pour l'ensemble de ses contrats.
NB : il n'est pas clair dans l'énoncé si le prix donné pour un contrat tient compte du coefficient de bonus/malus. La réponse SELECT SUM(prix) est donc tout à fait valable.
SELECT SUM(prix) * bonus
FROM ASSURES A, CONTRAT C
WHERE A.noassuré=C.noassuré AND nom=Defude
(Q3.2) Donner pour chaque numéro d'assuré le nombre total de contrats qu'il a pris.
(Q3.3) Donner le numéro de police qui couvre le plus de dommages.
SELECT noassuré, COUNT(*)
FROM CONTRAT
GROUP BY noassuré
NB : on peut aussi exprimer la clause HAVING par COUNT(*) >= ALL (SELECT COUNT(*) FROM RISQUES GROUP BY nopolice)
SELECT nopolice
FROM RISQUES
GROUP BY nopolice
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM RISQUES
GROUP BY nopolice)
(Q3.4) Donner les numéros d'assurés dont les contrats couvrent tous les dommages.
SELECT noassuré
FROM CONTRAT C, RISQUES R
WHERE C.nopolice=R.nopolice
GROUP BY noassuré
HAVING COUNT(DISTINCT dommage) =
(SELECT COUNT(*) FROM DOMMAGESCOUVERTS)
Question 4
Soit le schéma relationnel M(NS, N, P, NR, O, D, A) avec les dépendances fonctionnelles suivantes :
{ NS -> N; NS -> P; NR -> NS; NR -> O; NR, D -> A }
(Q4.1) Donner la fermeture transitive de l'ensemble des dépendances fonctionnelles.
Les nouvelles dépendances élémentaires que l'on peut produire sont les suivantes :
NR -> NS et NS -> N donc NR -> N
NR -> NS et NS -> P donc NR -> P
Il n'y en a pas d'autres car NR et NS sont les seuls attributs que l'on retrouve en partie gauche des DF.
(Q4.2) Donner la(les) clé(s) de ce schéma relationnel.
De la question précédente on déduit que :
NR -> NS, O, N, P
Il ne dérive ni D ni A donc il ne peut être clé candidate.
Par contre on a NR, D -> A, donc NR, D est une clé possible de ce schéma.
De plus c'est la seule clé car aucun groupe d'attributs ne dérive NR (D fait forcément partie de la clé car jamais présent en partie droite).
(Q4.3) Donner la forme normale de ce schéma et s'il n'est pas en 3FN proposer une décomposition en 3FN.
Ce schéma est forcément en 1ère forme normale puisqu'il respecte le modèle relationnel.
Il est en 2ème forme normale si tous les attributs non clés dépendent pleinement de la clé :
attributs clés : NR, D
attributs non clés : NS, N, P, O, A
on a la DF NR -> O donc ce schéma n'est pas en 2ème forme normale.
On peut noter que la définition de 3ème forme normale n'est pas plus respectée, puisque l'on a une DF entre attributs non clés (NS -> P par exemple).
Une décomposition possible en 3ème normale est :
M1(NR, D, A)
clé NR, D pas d'autre DF donc en 3FN
M2(NS, N, P)
clé NS pas d'autres DF donc en 3FN
M3(NR, NS, O)
clé NR pas d'autres DF donc en 3FN
© B. Defude - INT Evry