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

Assurance (NumAssurance, NumPersonne, Nom, Prenom, Adresse, NumAssuré, NumCond, NumImmat, TypeAss, Bonus)

Partie 1

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)

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 */