Exercices corrigés Bases de Données Avancées

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

Part of the document

Exercices corrigés (1)
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.
iDonner 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 diffférentes, si une voiture
peut ou non être assurée plusieurs fois sous la même police d'assurance, sous des
polices d'assurance diffférentes, etc.
iDonner une cle de la relation Assurance.
iProposer une décomposition en 3 FN, sans perte et qui preserve les DFs.
iOn suppose maintenant que les personnes ont des numéros identiques en tant que
personne, assuré ou conducteur. Comment pouvez vous alors simpliifier 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 :
iQuels sont les utilisateurs qui ont accès a "erebe" ?
iQuels sont les utilisateurs qui ont à la fois un accès groupé et un accès individuel à
"erebe"?
iQuels sont les utilisateurs ayant accès à toutes les machines?
Donner l'arbre algébrique pour la 3eme question.
B2- Vues
iCréer la vue indiquant les machines qui ne sont accessibles à personne ?
Peut on la mettre à jour ? Pourquoi ?

iCré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 :
iEtant 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 suiÌifiÌit
il de faire pour la rendre sans perte ?
iEtant 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 ?
Correction
Assurance (NumAssurance, NumPersonne, Nom, Prenom, Adresse, NumAssuré, NumCond, NumImmat,
TypeAss, Bonus)
Partie 1
iUne police d'assurance est souscrite par une seule personne
NumAssurance -> NumAssure
Hyp. supp. Mais on suppose qu'une personne peut souscrire plusieurs polices d'assurance.

imais peut concerner plusieurs vehicules
On n'a donc pas la DF numAssurance -> NumImmat

iChaque véhicule doit avoir un conducteur principal qui peut être diffférent de l'assuré
lui-même.
Hyp. supp. Un véhicule peut être assuré plusieurs fois, mais à chaque fois sous des polices
d'assurances différentes.
NumAssurance, NumImmat -> NumCond

iChaque véhicule peut être assuré sous un régime particulier (tous risques, tiers...).
NumAssurance, NumImmat -> TypeAss

iLe coeiÌifiÌicient de bonus est propre au véhicule pour une police d'assurance donnée.
NumAssurance, NumImmat -> Bonus

iUne personne ne peut pas être conducteur principal de plus d'une voiture pour une
même police d'assurance.
NumCond, NumAssurance -> NumImmat

iPlus les hypothese classsiques
NumPers -> Nom, Prénom, Adresse
Donc on a les DF suivantes :
DF1: NumPers -> Nom, Prénom, Adresse
DF2: NumCond, NumAssurance -> NumImmat
DF3: NumAssurance, NumImmat -> Bonus, TypeAss, NumCond
DF4: NumAssurance -> NumAssure
Il y a en outre dépendance entre NumCond et NumPers, NumAss et NumPers. Les assurés sont des
personnes, Idem pour les conducteurs.
DF5: NumAssure -> NumPers
DF6: NumCond -> NumPers
La clé de la relation universelle est donc (NumAssurance, NumImmat).
Algo 3FN qui preserve les DFs
Personne(NumPers, Nom, Prénom, Adresse)
VehiculeAssure(NumAssurance, NumImmat, Bonus, TypeAss, NumCond)
Assurance (NumAssurance, NumAssure)
Assure(NumAssure, NumPers)
Conducteur(NumCond, NumPers)
Si les domaines de NumCond, NumAssure et NumPers sont identiques, alors les dépendances fonctionnelles
5 et 6 deviennent des dépendances d'inclusion, les deux dernières relations sont superflues et les relations
précédentes deviennent :
VehiculeAssure(NumAssurance, NumImmat, Bonus, TypeAss, NumPers)
Assurance (NumAssurance, NumPers)
Partie 2
On désignera par les synonymes U, G, AU, AG, H respectivement les relations User, NetGroup, AccessUser,
AccessGrp, Host.
Q1:
SELECT login FROM U, AU, H WHERE U.userid=AU.userid AND AU.hostid=H.hostid AND
H.hostname='erebe'
UNION
SELECT login FROM U, G, AG, H WHERE U.userid=G.userid AND G.netgrpid=AG.netgrpid AND
AG.hostid=H.hostid AND H.hostname='erebe'
Q2:
idem avec INTERSECTS au lieu d' UNION
Q3:
SELECT login FROM U WHERE NOT EXISTS
(SELECT * FROM H WHERE hostid NOT IN
(SELECT AU.hostid FROM AU WHERE H.hostid=AU.hostid AND AU.userid=U.userid
UNION
SELECT AG.hostid FROM AG,G WHERE H.hostid=AG.hostid AND AG.netgrpid=G.netgrpid AND
G.userid = U.userid)