Suivant Index

1  Langages Relationnels (10 points)

On considère les trois tables suivantes:

HOTELS Hnom Gérant
  Bellevue Montmartre France Tours
  Ritz Vendôme France Tours
  Palais Royal France Tours
  Formule 1 Evry France Voyages
  Ibis Paris 20ème France Voyages




CHAMBRES Hnom Numéro Lits Prix
  Bellevue Montmartre 1 2 500
  Bellevue Montmartre 2 2 400
  Bellevue Montmartre 3 2 600
  Ritz Vendôme 1 2 1800
  Ritz Vendôme 2 2 2000
  Ritz Vendôme 3 4 2800
  Palais Royal 1 2 1000
  Palais Royal 2 4 1500
  Palais Royal 3 2 900
  Formule 1 Evry 1 2 150
  Formule 1 Evry 2 3 200
  Formule 1 Evry 3 2 150
  Ibis Paris 20ème 1 2 300
  Ibis Paris 20ème 2 4 500
  Ibis Paris 20ème 3 2 300




RESERVATIONS Hnom Numéro Cnom Arrivée Départ
  Bellevue Montmartre 1 Bedos 5.7 8.7
  Bellevue Montmartre 2 Marchand 5.7 9.7
  Ritz Vendôme 1 Ross 10.7 17.8
  Ritz Vendôme 3 Streisand 12.7 15.7
  Formule 1 Evry 1 Dupont 10.7 17.7
  Formule 1 Evry 2 Durand 12.7 15.7
  Ibis Paris 20ème 1 Yamamoto 15.7 16.7

1.1  Calcul Relationnel (5 Points)

  1. Calculer:
    1. {h.Gerant | HOTELS(h) } (0.5 points)
    2. {c.Prix | CHAMBRES(c) /\ il existe r : (RESERVATIONS(r) /\ c.Hnom = r.Hnom /\ r.Cnom = ``Ross'') } (1 point)
    3. {C | il existe H,N,L,P,A,D : CHAMBRES(H,N,L,P) /\ RESERVATIONS(H,N,C,A,D) /\ P < 500} (1 point)
    Solution:
    1. {France Tours, France Voyages}
    2. {1800,2000,2800}
    3. {Marchand,Dupont,Durand,Yamamoto}
  2. Écrire les requêtes suivantes en calcul relationelle (domaine ou n-uplet) :
    1. Le nom des gérants d'hôtels avec des chambres à deux lits qui coûtent plus que 1000 francs la nuit (0.5 points).
    2. Le nom des hôtels qui n'ont pas de chambre à 4 lits (1 point).
    3. Le nom des hôtels qui n'ont que des chambres à 2 lits (1 point).
    Solution:
    1. {G | il existe H,N,P : HOTELS(H,G) /\ CHAMBRES(H,N,2,P) /\ P >= 1000 }
    2. {H | il existe G : HOTELS(H,G) /\ ¬il existeN,P: CHAMBRES(H,N,4,P)}
    3. {H | il existe G,N,P : HOTELS(H,G) /\ CHAMBRES(H,N,2,P)
      /\ ¬il existeN',L',P': CHAMBRES(H,N',L',P') /\ L' <> 2}

1.2  SQL (5 Points)

Écrire les requêtes suivantes en SQL:
  1. Le nom des gérants d'hôtels avec des chambres à deux lits qui coûtent plus que 1000 francs la nuit (0.5 points).
  2. Le nom des hôtels qui n'ont pas de chambre à 4 lits (1 point).
  3. Le nom des hôtels qui n'ont que des chambres à 2 lits (1 point).
  4. Le nom de l'hôtel avec la chambre à 2 lits qui coûte le plus cher (1 point).
  5. Le nom des hôtels et leurs prix moyen d'un lit (1.5 points).
Solution:
  1. SELECT Gérant
  FROM HOTELS, CHAMBRES
 WHERE HOTELS.Hnom = CHAMBRES.Hnom 
   AND Lits = 2
   AND Prix >= 1000

  2. SELECT HOTELS.Hnom
  FROM HOTELS
 WHERE NOT EXISTS (SELECT *
                     FROM CHAMBRES
                    WHERE HOTELS.Hnom = CHAMBRES.Hnom 
                      AND Lits = 4)

  3. SELECT C1.Hnom
  FROM CHAMBRES C1
 WHERE C1.Lits = 2
   AND NOT EXISTS (SELECT *
                     FROM CHAMBRES C2
                    WHERE C1.Hnom = C2.Hnom 
                      AND Lits <> 2)

  4. SELECT Hnom
  FROM CHAMBRES C1
 WHERE Lits = 2
   AND Prix > ALL (SELECT Prix
                     FROM CHAMBRES C2
                    WHERE C2 Lits = 2
                      AND C2.Hnom = C1.Hnom 
                      AND C2.Numéro <> C1.Numéro)

  5. SELECT Hnom,AVG(Prix/Lits)
  FROM CHAMBRES
 GROUP BY Hnom
Suivant Index