TDI 2A - Corrigé

1. Créer la base de données. create database gestion_Reservation -use gestion_Reservation create table Client( idClient smallint primary key, nomCLient varchar(30), adresseClient varchar(200), telClient varchar(15))

create table Reservation( codeReservation smallint primary key, idClient smallint foreign key references client, dateRes smalldatetime, pensionComplete bit, tauxReduction int)

create table Sejour ( numSejour smallint primary key, codeReservation smallint foreign key references reservation, dateSejour smalldatetime, typeSejour varchar(30), dureeSejour smallint)

create table reservationAnnulee( codeReservation smallint foreign key references reservation, idClient smallint foreign key references client, dateAnnulation smalldatetime, primary key (codeReservation,idClient))

3. Ajouter une contrainte à la table Réservation concernant la colonne TauxReduction : Le taux doit être compris entre 0 et 75.

alter table reservation add constraint ch_taux check( tauxreduction between 0 and 75); 4. Ecrire une requête qui augmente de 20 % le taux de réservation du mois en cours (attention l’année doit être l’année actuelle).

update reservation set tauxReduction +=20 where MONTH(dateRes)= MONTH(GETDATE()) and YEAR(dateRes)= YEAR(GETDATE()); 5. Ecrire une requête qui liste toutes les réservations par type de séjour et qui ne concerne que les mois prochains.

select * from sejour where ((MONTH(dateSejour)> MONTH(GETDATE())AND YEAR(dateSejour)= YEAR(GETDATE())) OR YEAR(dateSejour)> YEAR(GETDATE())) order by typeSejour; 6. Créer une procédure stockée qui permet de lister les réservations faites par un client donné et retourner leur nombre.

Proposer un jeu d’essai correspondant. create procedure list_reserv @id smallint, @count smallint output

-- appel declare @nb smallint exec list_reserv 10,@nb output select @nb [nombre des reservation] 7. Créer une fonction qui retourne le nombre des réservations annulées par un client donnée.

begin declare @count smallint select @count = count(*) from reservationAnnulee where idclient = @id return @count end -declare @res smallint exec @res = reserv_annule 10 select @res 8. Créer un déclencheur qui permet de vérifier lors d’insertion ou une mise à jour dans la table ReservationAnnulee que la date d’annulation est supérieure à la date de la réservation correspondante.