Suivant Index

1  Modèle Relationnel (11 points)

L'agence de voyages MesAventures organise des voyages d'aventure avec des visites d'endroits touristiques. Un client (identifié par son nom) peut participer à différents voyages. On considère le schéma relationnel suivant :

Voyages (NumV, DatD, DatR, Prix)
Clients (NumV, NomC, AddrC)
Visites (NumV, DatV, EndV)

Les attributs sont:

NumV: numéro de voyage
DatD: date de départ du voyage
DatR: date de retour du voyage
Prix: prix du voyage
NomC: nom du client
AddrC: addresse du client
DatV date d'une visite
EndV endroit visité

1.1  Algèbre et SQL (5 points)

Écrivez les requêtes suivantes en algèbre relationnelle et en SQL :
  1. Les noms des clients qui visitent le parc Kruger au cours de voyages à moins de 5000 francs? (1 point)



    Solution:

    piNomC(Clients join (sigmaPrix < 5000(Voyages)) join (sigmaEndV = 'parc Kruger'(Visites))) 
    select NomC
  from Clients, Voyages, Visites
 where Prix < 5000
   and EndV = 'parc Kruger'
   and Clients.NumV = Voyages.NumV
   and Visites.NumV = Voyages.NumV;
  2. Les endroits visités le même jour par des clients de deux voyages différents? (2 points)

    Solution:

    piEndV(sigmaNumV2 <> NumV(rhoNumV/NumV2(Visites) join Visites)) 
    select A.EndV
  from Visites A, Visites B
 where A.NumV <> B.NumV
   and A.DatV = B.DatV
   and A.EndV = B.EndV;
  3. Les nombre de clients de l'agence qui vont visiter les chutes Victoria le 13/10/98?



    Solution:

    algèbre impossible 
    select count(distinct NomC)
  from Visites, Clients
 where Visites.NumV = Clients.NumV
   and Visites.EndV = 'chutes Victoria'
   and Visites.DatV = '13/10/98';
  4. Les endroits les plus visités de l'agence (chaque visite d'un client est comptabilisée)? (2 points)



    Solution:

    algèbre impossible 
    select EndV
  from Visites, Clients
 where Visites.NumV = Clients.NumV
 group by EndV
having (count(NomC)) = (select max(count(NomC))
                          from Visites, Clients
                         where Visites.NumV = Clients.NumV
                         group by EndV);

1.2  Dépendances fonctionnelles (6 points)

  1. Soit l'ensemble de dépendances fonctionnelles F={NomC -> AddrC} de la relation Clients. Est-ce que la relation Clients est en 3e Forme Normale? (1 point)
  2. Comment peut-on exprimer le fait qu'il ne peut y avoir deux visites du même endroit pendant un voyage et à deux jours différents? (1 point)

    Solution:

    NumV,EndV -> DatV

  3. Soit la relation R(A,B,C) avec la dépendance fonctionnelle A -> B. Donnez une instance r de cette relation, telle que la jointure naturelle des deux relations obtenues après une projection (décomposition) sur (A,B) et (B,C) soit différente de r:
    r <> piA,B(r) join piB,C(r)
    (1point)

    Solution:



  4. Soit la relation R(A,B,C,D,E). Soit S(A,B,C) une projection de R sur les attributs A,B et C. Quelles dépendances fonctionnelles sont satisfaites dans S si les dépendances fonctionnelles pour R sont les suivantes (utilisez les axiomes d'Armstrong) (3 points):
    1. A -> B, B -> C, C -> D, D -> E, et E -> A. (1 point)

      Solution:

      A -> B, A -> C, B -> C, B -> A, C -> A, C -> B

    2. AB -> DE, C -> E, D -> C et E -> A. (1 point)

      Solution:

      AB -> C

    3. A -> D, BD -> E, AC -> E, et DE -> B. (1 point)

      Solution:

      AC -> B
Suivant Index