Tutoriel de Bases de Données Relationnelles

Tutoriel de Bases de Données Relationnelles

Accueil  > Supports pédagogiques > Annales > Ingénieurs > EI 97 corrigé

Corrigé du contrôle pour les EI 97/98

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

[fil RSS du site]
Dernière mise à jour : 04/09/2009