INSTITUT NATIONAL DES TELECOMMUNICATIONS
CONTROLE DES CONNAISSANCES
Bloc : Bases de données Code : BD21
Durée : 1h30 Date : 06/01/98
Coordonnateur :
Mr Defude
____________________________________________________
Avertissements
1/ Lisez attentivement le sujet.
2/ Les questions sont indépendantes les unes des autres
3/ Essayez d'être clair et précis dans vos réponses.
4/ Soignez la présentation, dans la mesure du possible.
5/ Barême indicatif
Question 1 : 5 points
Question 2 : 5 points (Q2.1 = 1,5 pts, Q2.1 = 1,5 pts, Q2.3 = 2 pts)
Question 3 : 6 points (Q3.1 = 2 pts, Q3.2 = 2 pts, Q3.3 = 2 pts)
Question 4 : 4 points (Q4.1 = 2 pts, Q4.2 = 2 pts)
Un office de tourisme vous
demande d'informatiser la gestion de ses réservations d'hôtels. Ce
système doit s'appuyer sur une base de données relationnelles décrite
par le schéma suivant :
HOTELS(nomh, adresse, catégorie)
CHAMBRES(nomh, nochambre, prix, nbplaces)
CLIENTS(noclient, nom, adresse)
RESERVATIONS(nomh, nochambre, date, noclient)
Les clés sont soulignées.
Question 1
En vous aidant des règles de passage d'une modélisation
Entité/association à une modélisation relationnelle "à l'envers",
proposer un schéma Entité/Association équivalent à ce schéma
relationnel. Ce schéma devra comporter la description des entités (avec
leurs propriétés), des entités faibles (s'il y a lieu), des
associations (avec leurs propriétés) ainsi que les cardinalités minimum
et maximum des associations binaires.
Question 2
Exprimer chacune des 3 questions suivantes, d'une part en algèbre relationnelle sous la forme d'un arbre algébrique, et d'autre part en SQL.
(Q2.1) Donner les chambres (nom de l'hôtel, numéro de la chambre et prix) réservées par le client de numéro 10.
SELECT DISTINCT CH.nomh, CH.nochambre, CH.prix
FROM CHAMBRES CH, RESERVATIONS R
WHERE R.nomh = CH.nomh AND R.nochambre = CH.nochambre
AND R.noclient = 10
Un client peut très bien avoir réservé plusieurs
fois la même chambre à des dates différentes, donc il vaut mieux mettre
un DISTINCT.
(Q2.2) Donner le
prix des chambres des hôtels situés à l'adresse 'Coquibus Evry' (on
veut le nom de l'hôtel, le numéro de la chambre et le prix de la
chambre).
SELECT C.nomh, C.nochambre, C.prix
FROM CHAMBRES C, HOTELS H
WHERE C.nomh = H.nomh AND H.adresse='Cocquibus Evry'
(Q2.3) Donner les chambres (nom de l'hôtel et numéro de chambre) qui ne font l'objet d'aucune réservation.
SELECT nomh, nochambre
FROM CHAMBRES
MINUS
SELECT nom, nochambre
FROM RESERVATIONS
Attention de bien avoir des domaines compatibles lorsque l'on utilise les opérateurs ensemblistes.
Question 3
Exprimer les 3 questions suivantes en SQL :
(Q3.1) Donner le nombre de chambres à trois places de l'hôtel de nom 'Au Lion d'Or'.
SELECT COUNT(*)
FROM CHAMBRES
WHERE nomh='AU Lion dor' AND nbplaces=3
(Q3.2) Donner pour chaque client (numéro et nom) le nombre de réservations effectuées.
SELECT C.noclient, C.nom, COUNT(*)
FROM CLIENTS C, RESERVATIONS R
WHERE C.noclient=R.noclient
GROUP BY C.noclient, C.nom
Attention de bien penser à grouper par numéro de client et nom.
(Q3.3) Donner l'hôtel (nom et adresse) qui a le plus de réservations.
SELECT H.nomh, H.adresse
FROM HOTELS H, RESERVATIONS R
WHERE H.nomh = R.nomh
GROUP BY H.nomh, H.adresse
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM RESERVATIONS R1 GROUP BY R1.nomh)
Ne pas oublier de grouper par adresse.
Question 4
Exprimer les 2 questions suivantes en algèbre relationnelle ou en SQL :
(Q4.1) Donner les clients (numéro) qui ont effectué au moins une réservation dans chaque hôtel.
SELECT R.noclient
FROM RESERVATIONS R
GROUP BY R.noclient
HAVING COUNT(DISTINCT R.nomh) = (SELECT COUNT(*) FROM HOTELS)
Ne pas oublier la clause DISTINCT dans le COUNT, sinon on va compter les réservations et non pas les hotels.
(Q4.2) Donner les clients (numéro et nom) qui ont réservé au moins deux jours consécutifs la même chambre dans le même hôtel.
SELECT C.noclient, C.nom
FROM CLIENT C, RESERVATIONS R1, RESERVATIONS R2
WHERE C.noclient = R1.noclient AND R1.noclient = R2.noclient AND
R1.nomh = R2.nomh AND R1.nochambre = R2.nochambre AND R1.date = R2.date - 1
Posté le 4 septembre 2009