Index Suivant

1   Requêtes (8 points)

Soit les tables relationnelles suivantes :
TGV(NumTGV, NomTGV, GareTerm)
:

Arrêt(NumTGV, NumArr, GareArr, HeureArr)
:

Réservation(NomPass, NumTGV, Wagon, Place, GareDep, GareArr, Date)
:
Exemple : Le TGV 311 s'appelle ``Le Mistral'' et sa gare terminus est Marseille. Il part de Paris à 13h23, fait un arrêt à Lyon à 15h15 et à Aix en Provence à 16h20, et arrive à Marseille a 16h30 :

TGV
NumTGV NomTGV GareTerm
311 Le Mistral Marseille
Arrêt
NumTGV NumArr GareArr HeureArr
311 0 Paris 13h23
311 1 Lyon 15h15
311 2 Aix 16h20
311 3 Marseille 16h30

Bernard et Bianca ont réservé deux places dans le wagon 16 du TGV de Paris à Lyon pour le 22 Juin 2001:

Réservation
NomPass NumTGV Wagon Place GareDep GareArr Date
Bernard 311 16 58 Paris Lyon 22/06/2001
Bianca 311 16 59 Paris Lyon 22/06/2001

Ecrivez les requêtes suivantes dans le(s) langage(s) indiqué(s):
  1. SQL et algèbre: Les noms des TGV qui s'arrêtent à Lyon (le départ et l'arrivée sont comptés comme arrêts)

    Solution: SQL: 
    
select NomTGV
  from TGV, Arret
 where TGV.NumTGV = Arret.NumTGV
   and GareArr = 'Lyon'
    Algèbre:
    pNomTGV(TGV |><| sGareArr='Lyon'(Arret))

  2. SQL: Les numéros des TGV qui s'arrêtent d'abord à Lyon et après à Aix en Provence

    Solution: SQL: 
    
select A.NumTGV
  from Arret A, Arret B
 where A.NumTGV = B.TGV
   and A.GareArr = 'Lyon'
   and A.GareArr = 'Aix'
   and A.NumArr < B.NumArr
  3. SQL et algèbre: Les numéros des TGV qui s'arrêtent à Lyon mais pas à Aix en Provence

    Solution: SQL: 
    
select NumTGV
  from Arret 
 where GareArr = 'Lyon'
   and NumTGV not in (select NumTGV
                        from Arret 
                       where GareArr = 'Aix')
    Algèbre:
    pNumTGV(sGareArr='Lyon'(Arret)) - pNumTGV(sGareArr=Aix'(Arret))

  4. SQL et algebre: Les noms des passagers qui descendent du train 411 dans sa gare terminus.

    Solution: SQL: 
    
select NomPass
  from Reservation R, TGV T
 where R.NumTGV = 411
   and T.NumTGV = 411
   and R.GareArr = T.GareTerm
    ou 
    
select NomPass
  from Reservation R, TGV T
 where R.NumTGV = 411
   and T.NumTGV = R.NumTGVC
   and R.GareArr = T.GareTerm
    Alg:
    pNomPass(sNumTGV=411(Reservation) |><|GareArr=GareTerm
    (pGareTerm(sNumTGV=411TGV)))

  5. SQL: Traduisez la requête calcul domaine suivante en SQL :
    {a,b,h| $ c, d (TGV(a, b, c) Ù Arret(a, d, c, h))}

    Solution: 
    
select TGV.NumTGV, TGV.NomTGV, Arret.HeureArr 
  from TGV, Arret 
 where TGV.NumTGV = Arret.NumTGV
   and TGV.GareTerm = Arret.GareArr
  6. Expliquez le résultat de la requête précédente en Français?

    Solution: Les TGVs (nom et numéro) et l'heure d'arrivée à leur gare terminus.

  7. SQL: Le nombre de réservations dans le TGV 'Le Valais' pour le 21/06/2001

    Solution: 
    
select count(*)
  from TGV, Reservation
 where NomTGV = 'Le Valais'
   and TGV.NumTGV = Reservation.TGV
   and Date='21/06/2001'
  8. SQL: Les noms des voyageurs qui ont pris (réservé) plus que trois fois le TGV 512.

    Solution: 
    
select NomPass
  from Reservation
 where NumTGV = 512
 group by NomPass
having count(*) > 3
    ou 
    
select R1.NomPass
  from Reservation R1
 where 3 < (select count(*)
              from Reservation R2
             where R2.NumTGV = 512
               and R2.NomPass = R1.NomPass)
Index Suivant