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
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barême indicatif
Question 1 : 5 points
Question 2 : 2 points (Q2.1 = 1 pt, Q2.2 = 1 pt)
Question 3 : 8 points (Q3.1 = 2 pts, Q3.2 = 2 pts, Q3.3 = 2 pts, Q3.4 = 2 pts)
Question 4 : 5 points (Q4.1 = 2 pts, Q4.2 = 1 pt, Q4.3 = 2 pts)
ASSURES(noassuré, nom, prénom, bonus, nocompte)
CONTRAT(nopolice, datedébut, datefin, prix, noassuré)
SINISTRE(nosinistre, date, noassuré, dommage)
RISQUES(nopolice, dommage)
DOMMAGESCOUVERTS(dommage)
La sémantique des diverses relations est la suivante :
ASSURES(noassuré, nom, prénom, bonus, nocompte) : un assuré est identifié de manière unique par un numéro d'assuré et est décrit par son nom, son prénom, son pourcentage de bonus/malus et son numéro de compte bancaire,
CONTRAT(nopolice, datedébut, datefin, prix, noassuré) : un contrat d'assurances est pris par un assuré identifié par son numéro. Ce contrat est identifié de manière unique par son numéro de police, a un certain prix et est valable depuis une date de début jusqu'à une date de fin,
SINISTRE(nosinistre, date, noassuré, dommage) : un sinistre est identifié de manière unique par un numéro et est déclaré par un assuré identifié par son numéro. Un sinistre est décrit par sa date de déclaration ainsi que par le dommage subi (incendie, dommage corporel, ...),
RISQUES(nopolice, dommage) : un contrat d'assurances identifié par son numéro couvre un certain nombre de dommages,
DOMMAGESCOUVERTS(dommage) : cette relation de référence décrit l'ensemble des dommages couverts par la compagnie.
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.
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.
(Q2.2) Donner les dommages couverts par les différents contrats d'assurances de l'assuré de nom Defude.
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.
(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.
(Q3.4) Donner les numéros d'assurés dont les contrats couvrent tous les dommages.
Soit le schéma relationnel M(NS, N, P, NR, O, D, A) avec les dépendances fonctionnelles suivantes :
{ NS -> N; NS -> P; NR -> [[Nu]]S; NR ->[[Omicron]]; NR, D -> A }
(Q4.1) Donner la fermeture transitive de l'ensemble des dépendances fonctionnelles.
(Q4.2) Donner la(les) clé(s) de ce schéma relationnel.
(Q4.3) Donner la forme normale de ce schéma et s'il n'est pas en 3FN proposer une décomposition en 3FN.