Corrigé du Controle de Connaissance du
Vendredi 13 décembre 1996

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é 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, tiers...).

  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

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)

Partie 3

Q1:
CREATE VIEW private_host AS
SELECT * FROM H WHERE hostid NOT IN
(SELECT AU.hostid FROM AU UNION SELECT AG.hostid FROM AG, G WHERE AG.netgrpid=G.netgrpid
) 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 */

Q2:
CREATE VIEW acces AS
SELECT * FROM AU UNION SELECT AG.hostid, G.userid FROM AG, G WHERE AG.netgrpid=G.netgrpid;
/* 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 */