Exercices corrigés (1)

1 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, ). 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).

2 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.

3 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.

4 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" ?

5 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.

6 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 ? Correction Assurance (NumAssurance, NumPersonne, Nom, Prenom, Adresse, NumAssur , NumCond, NumImmat, TypeAss, Bonus). Partie 1. Une 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. mais peut concerner plusieurs vehicules On n'a donc pas la DF numAssurance -> NumImmat Chaque v hicule doit avoir un conducteur principal qui peut tre diff rent de l'assur.

7 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 Chaque v hicule peut tre assur sous un r gime particulier (tous risques, ). NumAssurance, NumImmat -> TypeAss Le coefficient de bonus est propre au v hicule pour une police d'assurance donn e. NumAssurance, NumImmat -> Bonus Une personne ne peut pas tre conducteur principal de plus d'une voiture pour une m me police d'assurance. NumCond, NumAssurance -> NumImmat Plus 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.

8 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).

9 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 AND AND. 'erebe'. UNION. SELECT login FROM U, G, AG, H WHERE AND AND. AND '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 FROM AU WHERE AND UNION. SELECT FROM AG,G WHERE AND AND. = ). Partie 3. Q1: CREATE VIEW private_host AS. SELECT * FROM H WHERE hostid NOT IN. (SELECT FROM AU UNION SELECT FROM AG, G WHERE. ) WITH CHECK OPTION. /* peut tre mise jour, en insertion, pourvu que le hostid ne soit pas d j dans un tuple de AU ou de AG */.)

10 Q2: CREATE VIEW acces AS. SELECT * FROM AU UNION SELECT , FROM AG, G WHERE. ;. /* cette vue ne peut tre directement mise jour */. CREATE VIEW low_access_host AS. SELECT hostid, hostname FROM acces GROUP BY hostid, hostname HAVING COUNT(userid) < 20. /* cette vue, construite par agr gation de tuples, ne peut tre mise jour */. Exercices corrig s (2). A. Normalisation Pr ambule: Vous savez tous d sormais ce qu'est une op ration de jointure relationnelle. Si on remplace les attributs de jointures alphanum riques par des attributs spatiaux (type complexe repr sentant par exemple une suite de points, de lignes, etc) et l'op rateur (=, >, <, etc) par un op rateur spatial (inclusion, intersection, adjacence, etc), on parle alors de jointure spatiale.