Controle de connaissance
Vendredi 13 décembre 1996
Durée 1h30. Documents autorisés.
A- Modélisation
Une compagnie d'assurance veut utiliser un SGBD pour stocker ses
contrats d'assurances de voiture. Une police d'assurance est souscrite
par une seule personne mais peut concerner plusieurs vehicules. Chaque
véhicule doit avoir un conducteur principal qui peut être différent de
l'assuré lui-même. Chaque véhicule peut être assuré sous un régime
particulier (tous risques, tiers...). Le coefficient de bonus est
propre au véhicule pour une police d'assurance donnée. Une personne ne
peut pas être conducteur principal de plus d'une voiture pour une même
police d'assurance.
On propose la relation universelle suivante.
Assurance (NumAssurance, NumPersonne,
Nom, Prenom, Adresse, NumAssuré, NumCond, NumImmat, TypeAss, Bonus)
où NumAss est le numéro de la police d'assurance; NumPersonne, Nom,
Prénom et Adresse les coordonnées de toutes les personnes connues par
la compagnie, assurés ou conducteurs; NumAssuré et NumCond sont
respectivement les numéros des personnes en tant qu'Assuré ou en tant
que conducteur; NumImmat le numéro d'immatriculation d'une voiture;
TypeAss et Bonus le type d'assurance et le bonus du vehicule pour une
police particulière.
Dans un premier temps, nous supposerons que les numéros de
conducteur et d'assurés correspondent de façon univoque au numero de
personne (et inversement) mais qu'ils peuvent avoir des valeurs
différentes.
- Donner la liste des dépendances fonctionnelles en les validant
par les hypothèses de l'énoncé ou par des hypothèses supplémentaires
que vous ne manquerez pas de préciser. En particulier, vous vous
interrogerez sur les points suivants, à savoir si un assuré peut ou
non souscrire plusieurs polices d'assurances différentes, si une
voiture peut ou non être assurée plusieurs fois sous la même police
d'assurance, sous des polices d'assurance différentes, etc.
-
Donner une cle de la relation Assurance.
- Proposer une décomposition en 3 FN, sans perte et qui preserve
les DFs.
- On suppose maintenant que les personnes ont des numéros
identiques en tant que personne, assuré ou conducteur. Comment pouvez
vous alors simplifier votre schéma. Auriez vous obtenu la même
décomposition en supprimant les attributs NumCond et NumAssure de la
relation universelle. Donnez une explication.
B- requêtes relationnelles
Pour avoir le droit d'accès à une machine Unix de l'école, il faut
être individuellement déclaré comme ayant un droit d'accès à cette
machine, ou bien appartenir à un groupe d'utilisateurs (dit net-group)
qui a lui même accès à la machine. Bien entendu, ces possibilités ne
sont pas exclusives l'une de l'autre.
Les informations concernant ces droits d'accès sont stockées dans
le schéma relationnel suivant:
host (hostid, hostname)
user (userid, login, name, firstname)
netgroup (netgrpid, userid)
Accessgroup (netgrpid, hostid)
Accessuser (userid, hostid)
B1- Requêtes relationnelles
Répondre en SQL aux questions suivantes :
- Quels sont les utilisateurs qui ont accès a "erebe" ?
- Quels sont les utilisateurs qui ont à la fois un accès groupé et
un accès individuel à "erebe"?
- Quels sont les utilisateurs ayant accès à toutes les machines?
Donner l'arbre algébrique pour la 3eme question.
B2- Vues
- Créer la vue indiquant les machines qui ne sont accessibles à
personne ?
Peut on la mettre à jour ? Pourquoi ?
- Créer la vue qui donne les machines dont le nombre
d'utilisateurs autorisés est inférieur à 20. On prendra garde à ne
compter chaque utilisateur qu'une seule fois.
Peut on mettre à jour cette vue ? Pourquoi ?
C- questions diverses
Répondez en deux lignes aux questions suivantes :
- Etant données une relation universelle, et une décomposition de
cette relation universelle qui préserve les DFs. Si cette
décomposition n'est pas sans perte, que suffit il de faire pour la
rendre sans perte ?
- Etant données une relation universelle, et une décomposition de
cette relation universelle qui est sans perte, cette décomposition
préserve-t-elle obligatoirement les DFS ?