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.
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
NB : Bien penser à la clause DISTINCT un assuré pouvant avoir
plusieurs contrats se terminant à la même date.
(Q2.2) Donner les dommages couverts par les différents contrats
d'assurances de l'assuré de nom Defude.
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
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!
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.
SELECT SUM(prix) * bonus
FROM ASSURES A, CONTRAT C
WHERE A.noassuré=C.noassuré AND nom=Defude
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.
(Q3.2) Donner pour chaque numéro d'assuré le nombre total
de contrats qu'il a pris.
SELECT noassuré, COUNT(*)
FROM CONTRAT
GROUP BY noassuré
(Q3.3) Donner le numéro de police qui couvre le plus de
dommages.
SELECT nopolice
FROM RISQUES
GROUP BY nopolice
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM RISQUES
GROUP BY nopolice)
NB : on peut aussi exprimer la clause HAVING par COUNT(*) >= ALL (SELECT
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
Posté le 4 septembre 2009