RAPPORT SUR LE TP3 :

« Gestion de la réservation dans un hôtel »

Redouane NAJI N°34

 I. MCD de la base de données

CHAMBRE
CLIENT 0,* RESERVATION 0,* num_chambre
num_client prix
nom date_arr nbr_lits
prenom date_dep nbr_pers
adresse confort
equipement

N.B :

La table réservation a pour clé primaire la combinaison de trois attributs :

« num_client, num_chambre et date_arr » parce qu’un client peut réserver une
chambre plusieurs fois dans des dates différentes. On n’a pas ajouté « date_dep »
à la clé car le client a le droit de ne pas préciser sa date de départ et dans ce cas
cet attribut va être nul (ce qui est interdit pour une clé).

II. Préparation de la base de données

Durant cette phase j’ai créé l’utilisateur « najiTP3 » ainsi que les différentes
tables en utilisant les scripts suivants :

a) Création de l’utilisateur

PAGE 1
b) Création des tables

clear screen
create table client(
num_client number(6) primary key,
nom varchar2(20) not null,
prenom varchar2(20) not null,
adresse varchar2(40));
commit;

clear screen
create table chambre(
num_chambre number(3),
prix number(8,2) not null,
nbr_lits number(1) not null,
nbr_pers number(1) not null,
confort varchar2(6) not null,
equipement varchar2(3) not null,
constraint chambre_pk primary key (num_chambre));
commit;

clear screen
create table reservation(
num_client number(6),
num_chambre number(3),
date_arr date,
date_dep date,
constraint reserv_pk primary key
(num_client,num_chambre,date_arr),
constraint reserv_client_fk foreign key (num_client) references
client(num_client),
constraint reserv_chambre_fk foreign key (num_chambre)
references chambre(num_chambre));
commit;

c) Insertion des données

clear screen

PAGE 2
insert into client values (1000,'GASCON','GASTON','12 av. du Général
1239 1C1');
insert into client values (1001,'DUPONT','PIERRE','12 ch. des hirondelles
1238 LABAS');
insert into client values (1002,'DUFOR','JEAN','10 av. de la gar 1300
AILLEURS');
insert into client values (1003,'ZORO','DIEGO','10 ch des voleurs Los
Angeles');
insert into client values (1004,'EINSTEIN','ALBERT','10 rte de relativ 1004
PLUS-LOIN');
insert into client values (1005,'DUMAS','ALEXANDRE','10 route du
moulins LE-SUD');
insert into client values (1007,'NOBODY','FRANCOISE','403 rte de
l''inconnu 75000 Paris');
insert into client values (1006,'ROMULUS','BERNADETTE','352 route de
rome 1409 Lion');
insert into client values (1009,'AGDA','BRUNO','10 route de l'impossible
1508 TEXAS');
insert into client values (1008,'CHADOK','AMELIE','25 rue de la rame 1456
Tombouctou');
commit;

clear screen
insert into chambre values (10,80,1,2,'WC','NON');
insert into chambre values (20,80,1,2,'WC','NON');
insert into chambre values (30,80,1,2,'WC','NON');
insert into chambre values (40,80,1,2,'WC','NON');

insert into chambre values (11,90,2,2,'WC','NON');

insert into chambre values (21,90,2,2,'WC','NON');
insert into chambre values (31,90,2,2,'WC','NON');
insert into chambre values (41,90,2,2,'WC','NON');
insert into chambre values (12,100,2,2,'DOUCHE','NON');
insert into chambre values (22,100,2,2,'DOUCHE','NON');
insert into chambre values (32,100,2,2,'DOUCHE','NON');
insert into chambre values (42,100,2,2,'DOUCHE','NON');
insert into chambre values (13,120,1,2,'BAIN','NON');
insert into chambre values (23,120,1,2,'BAIN','NON');
insert into chambre values (33,120,1,2,'BAIN','NON');
insert into chambre values (43,120,1,2,'BAIN','NON');
insert into chambre values (14,140,2,2,'BAIN','TV');
insert into chambre values (24,140,2,2,'BAIN','TV');
insert into chambre values (34,140,2,2,'BAIN','TV');
insert into chambre values (44,140,2,2,'BAIN','TV');
insert into chambre values (15,180,3,4,'BAIN','TV');
insert into chambre values (25,180,3,4,'BAIN','TV');
insert into chambre values (35,180,3,4,'BAIN','TV');

PAGE 3
 insert into chambre values (45,180,3,4,'BAIN','TV');
commit;

clear screen
insert into reservation values (1000,11,'11/01/90','15/01/90');
insert into reservation values (1001,21,'10/01/90','');
insert into reservation values (1002,34,'20/12/89','27/12/89');
insert into reservation values (1003,44,'24/12/89','27/12/89');
insert into reservation values (1005,45,'23/12/89','28/12/89');
insert into reservation values (1006,14,'01/12/89','28/12/89');
insert into reservation values (1007,23,'01/12/89','02/12/89');
insert into reservation values (1007,23,'08/12/89','09/12/89');
insert into reservation values (1007,23,'15/12/89','16/12/89');
insert into reservation values (1007,23,'22/12/89','23/12/89');
insert into reservation values (1007,23,'29/12/89','30/12/89');
commit;

III. Requêtes SQL et Algèbre Relationnel

1) Les chambres avec BAIN et TV

∏ (chambre)
num_chambre

PAGE 4
2) Les numéros des chambres et leur capacité

∏ (chambre)
num_chambre, nbr_pers

3) Les noms des clients ayant réservé une chambre pour le 25/12/1989

T1  ∏ (client)
nom,prenom,num_client
T2  ∏ (reservation)
date_arr,date_dep,num_client
T3  (T1 x T2)
T1.num_client = T2.num_client

PAGE 5
 T4  ∏ (T3)
nom,prenom,date_arr,date_dep

4) Les noms des clients et le confort des chambres qu’ils ont réservées

Requête optimisée : (élimination des tuples en double)

T1  ∏ (client)
nom,prenom,num_client
T2  ∏ (reservation)
num_chambre,num_client
T3  ∏ (chambre)
num_chambre,confort

T4  (T1 x T2)

PAGE 6
 T1.num_client = T2.num_client
T5  (T4 x T3)
T3.num_chambre = T4.num_chambre
T6  ∏ (T5)
nom,prenom,confort

5) La capacité théorique d’accueil de l’hôtel

« Pas possible en algèbre relationnel »

6) Le prix par personne des chambres ayant une TV

T1  ∏ (chambre)
prix,equipement
T2  (T1)
equipement = 'TV'
T3  ∏ (T2)
prix
7) Les chambres ayant le même confort et le même équipement qu’une autre
chambre mais ayant un prix inférieur de 10% à celle-ci

PAGE 7
 T1  ∏ (chambre)
num_chambre,confort,equipement,prix
T2  ∏ (chambre)
num_chambre,confort,equipement,prix
T3  (T1 x T2)
T1.equipement = T2.equipement AND
T1.equipement ≠ 'NON' AND
T1.confort = T2.confort AND
T1.prix <T2.prix * 0.9
T3  ∏ (T2)
T1.num_chambre,T1.prix
T2.num_chambre,T2.prix,
T1.confort,T2.equipement
8) Les chambres coutant au max. 80 francs ou ayant un BAIN et valant au
max. 120

PAGE 8
 T1  ∏ (chambre)
num_chambre,confort,prix
T2  (T1)
(confort = 'BAIN' AND prix=120) OR
prix = 80
9) Les chambres avec un moyen de se laver

T1  ∏ (chambre)
num_chambre,confort
T2  (T1)
confort = 'BAIN' OR
confort = 'DOUCHE'
10) La recette du 25/12/1989

« Pas possible en algèbre relationnel »

11) Noms des clients commençant par "DU"

PAGE 9
 « Pas possible en algèbre relationnel »

12) Noms des clients ayant un "O" pour quatrième lettre

« Pas possible en algèbre relationnel »

13) Nombre des chambres dont le prix est entre 85 et 120 francs

« Pas possible en algèbre relationnel »

PAGE 10
14) Clients n’ayant pas fixé leur date de départ

T1  ∏ (client)
num_client,nom,prenom
T2  ∏ (reservation)
num_client,date_dep
T3  (T2)
Date_dep = NULL
T4  (T1 x T3)
T1.num_client = T3.num_client
T5  ∏ (T4)
num_client,nom,prenom
15) Tous les numéros des chambres et les numéros des clients ayant réservé
la chambre pour le
25/12/1989

T1  ∏ (chambre)
num_chambre
T2  (reservation)
(date_dep = NULL OR
date_dep>= '25/12/1989') AND

PAGE 11
 date_arr<= '25/12/1989'
T3  ∏ (T2)
num_chambre,num_client
T4  (T1 RIGHT OUTER JOIN T3)

16) Prix moyen des chambres ayant le même confort que celle du client N°
234 (puis le client N° 1006)

« Pas possible en algèbre relationnel »

17) Chambres ayant un prix 10% inférieur à la moyenne pour une même
catégorie de confort

« Pas possible en algèbre relationnel »

18) Prix moyen des chambres par type de confort

PAGE 12
 « Pas possible en algèbre relationnel »

19) Prix minimum et maximum des chambres par type de confort

« Pas possible en algèbre relationnel »

20) Prix minimum et maximum des chambres par type de confort, mais
dont le prix Min est plus petit que 100

« Pas possible en algèbre relationnel »

21) La liste des noms et des adresses des clients triée alphabétiquement
par les noms

PAGE 13
« Pas possible en algèbre relationnel »

PAGE 14