OFPPT
|
|
Direction Régionale Tensift Atlantique
Etablissement : Ista Ntic Syba Marrakech
Examen de fin module : SGBD II
2011/2012
Filière:TDI Groupe(s): TDI2GE
Niveau :
2ème année Formateur : OUATOUCH Abdeljalil
Durée :2h30 Barème: /20
Partie
I : Programmation TSQL : (4
pts )
1) En
utilisant les fonctions :
-
ascii(caractère)
permet de renvoyer le code ascii du caractère précisé en paramètre.
-
char (int)
permet d’afficher le caractère dont le code ascii est l’entier précisé en
paramètre.
Afficher tous les alphabets
(majuscule et minuscule) en précisant pour chacun le code ascii. (1 pt ).
2) On
considère la table :
Livre (IdL int primary key,
Titre varchar (4))
Créer un programme TSQL permettant
de remplir cette table par 100 enregistrement sachant que :
(1 pt )
-
Le champ IdA est
un compteur qui s’incrémente automatiquement,
-
Le champ Titre
respecte le masque suivant : ‘Ti’ avec i est un entier compris entre 0 et
99.
3) On
rajoute sur la même base la table :
Livre_Old (IdL int primary key, Titre varchar (3))
On désire remplir cette table par le contenu de la
table initiale en respectant les contraintes suivantes : (2 pts )
o Un enregistrement ne doit pas figurer à la fois dans
les deux tables.
o Une fois la table Livre_Old est remplie la
table Livre doit être vide.
o Si l’opération de transfert de données a généré un
problème, elle sera complètement annulée et on veillera à afficher un message
pour l’utilisateur. « gestion d’erreurs »
Partie
II : Procédures stockées et Triggers (16 pts )
On considère l’exemple de la base de données
suivante :
Cinéma (nomCinéma, numéro, rue, ville)
Salle (nomCinéma, noSalle, capacité, climatisée)
Horaire (idHoraire, heureDébut, heureFin,
Durée)
Séance (idFilm, nomCinéma, noSalle,
idHoraire, tarif)
Film (idFilm, titre, année, genre, résumé)
Acteur (idActeur, nom, prénom, DateNaissance)
Rôle (idActeur, idFilm, nomRôle)
1) Réaliser
la base de données sous le nom Projections. (1,5 pt )
2) Remplir
les tables avec des données correctes. (1,5 pt )
3) Triggers : (6 pts )
a. Chaque
suppression d’un enregistrement de la table « Cinéma » ne doit pas
s’effectuer mais s’affiche plutôt un message indiquant cela.
b. Le
champ « Nom » de la même table doit être en majuscule pour tous les
champs de la table et le « Prénom » doit commencer par une lettre
majuscule. (Acteur)
c. Créer
un (des) trigger(s) qui supprime (nt) en cascade après la suppression d'une
cinéma
d. La
« Durée » doit se remplir automatiquement.
e. Le
« Tarif » de chaque séance ne doit pas dépasser 1000 DH.
f. Créer
un trigger qui affiche le type de chaque opération sur la table ainsi que le
nombre des enregistrements concernés.
4) Procédures : (7 pts )
a. Ecrire
une Procédure stockée qui, étant donné un acteur, affiche le nombre de films
auxquels il a participé, utiliser un paramètre de sortie.
b. Réaliser
une procédure stockée qui permet de remplir la colonne Durée pour tous les
enregistrements de la table en procédant aux vérifications suivantes :
i.
L’heure de début
doit être inférieure à celle de fin
ii.
La durée
maximale est de 5 heures ; en cas de non respect de cette condition, il
faut afficher un message qui demande à l’utilisateur de ressaisir les données
de l’enregistrement en question.
c. Afficher
sous forme de phrases le nombre de cinémas de chaque ville avec leurs salles.
(Ordre croissant des villes)
d. Ajouter
une table Grp_Cin (code, description) contenant les groupes de familles
comme suit :
-
Grp
1 : Nombre de salles <
= 1
-
Grp
2 : 1 < Nombre de salles <
= 3
-
Grp
3 : 3 < Nombre de salles <
= 5
-
Grp
4 : 5 < Nombre de salles
Créer une procédure stockée
permettant de remplir cette table comme suit : ((1, Groupe1), (2,
Groupe2)…)
e. Ajouter
une colonne CodeG comme clé étrangère dans la table « cinéma » ;
puis réaliser une procédure stockée permettant de remplir cette colonne.
f. Ajouter
une table « Cinémas_Salles » qui a un schéma relationnel résultat de
la combinaison du schéma de la 1ère et de la 2ème sans
répétition des champs. Réaliser une procédure stockée qui permet de remplir
cette table.
g. Réaliser
une procédure stockée qui permettra d’afficher l’état actuel d’un cinéma (passé
en argument) en affichant les éléments suivants :
i.
Le plus court,
le plus long et le plus cher film projeté à ce cinéma.
ii.
L’acteur qui a
apparu le plus dans ce cinéma.
iii.
La durée totale
de toutes les projections de ce cinéma.
Remarque :
Le script des réponses doit être
lisible, commenté et enregistré sur le bureau dans un dossier qui porte votre
nom et votre groupe
Correction
Create Database FEM_SGBDII
use FEM_SGBDII
/* Partie I : Programmation TSQL : (4 pts) */
--1
select char(65+32)
declare @i int
declare @t table (AlphaMaG varchar,ASCIIMaG int, AlphaMin varchar,ASCIIMin int)
set @i = 65
while(@i < 91)
begin
insert into @t values (CHAR(@i), @i,CHAR(@i+32), @i+32)
set @i = @i+1
end
select * from @t
--2
create table Livre(
IdL int primary key,
Titre varchar (4)
)
delete from Livre
declare @i int
set @i=0
while @i < 100
begin
insert into Livre values (@i+1,'T'+cast(@i as varchar(25)))
set @i = @i + 1
end
select * from Livre
--3
create table Livre_OLD(
IdL int primary key,
Titre varchar (4)
)
declare old cursor for (select * from Livre)
open old
declare @id int, @titre varchar(255)
fetch next from old into @id ,@titre
while @@FETCH_STATUS = 0
begin
if not exists(select * from Livre_OLD where IdL=@id and Titre = @titre)
begin
begin tran a
insert into Livre_OLD values (@id,@titre)
delete from Livre where IdL=@id and Titre = @titre
commit a
end
fetch next from old int @id,@titre
end
xs
/* Partie II : Procédures stockées et Triggers */
create table Cinéma (
nomCinéma varchar(255) primary key,
numéro int,
rue varchar(255),
ville varchar(255)
)
create table Salle (
nomCinéma varchar(255),
noSalle int,
capacité int,
climatisée int
primary key (noSalle)
)
insert into Salle values('s',1,1,1)
create table Horaire (
idHoraire int primary key,
heureDébut time,
heureFin time,
Durée float
)
insert into Séance values(1,'alhillal',1,1,100000)
insert into Séance values(101,'alhillal',1,1,100000)
insert into Séance values(100,'alhillal',1,1,100)
create table Séance (
idFilm int foreign key references Film(idFilm),
nomCinéma varchar(255) foreign key references Cinéma(nomCinéma),
noSalle int foreign key references Salle(noSalle),
idHoraire int foreign key references Horaire(idHoraire),
tarif int,
primary key (idFilm,nomCinéma,noSalle,idHoraire)
)
create table Film (
idFilm int primary key,
titre varchar(255),
année int,
genre varchar(255),
résumé varchar(255)
)
insert into Film values (1,'Planet des signe',1995,'Science fiction','Monde des singes')
insert into Film values (2,'Avatar',2010,'Science fiction','autre vie')
insert into Film values (3,'Le petit monde de borweur',2012,'comique','monde de petit')
insert into Film values (4,'La rélité',2005,'drama','la fin de monde')
create table Acteur (
idActeur int primary key,
nom varchar(255),
prénom varchar(255),
DateNaissance date
)
create table Rôle (
idActeur int foreign key references Acteur(idActeur),
idFilm int foreign key references Film(idFilm),
nomRôle varchar(255)
primary key(idActeur,idFilm)
)
insert into Cinéma values('MegaRama',1,'Agdal','Casa')
insert into Cinéma values('Arif',2,'Dawdiyat','Marrakech')
insert into Cinéma values('Alhillal',3,'Sou9 Arabi3','Rabat')
insert into Cinéma values('Mabrouka',4,'Prince','marrakech')
insert into Rôle values (1,1,'Pressonage pricipal')
insert into Rôle values (2,1,'Scéane comique')
insert into Rôle values (3,1,'Combat')
insert into Rôle values (2,3,'Police')
insert into Salle values ('MegaRama',12,100,0)
insert into Salle values ('MegaRama',5,250,1)
insert into Salle values ('Arif',6,85,0)
insert into Salle values ('Alhillal',10,150,0)
insert into Horaire values (1,'2:0','4:0',NULL)
insert into Horaire values (2,'8:0','1:0',NULL)
insert into Horaire values (3,'1:0','6:0',NULL)
insert into Horaire values (4,'9:0','10:0',NULL)
insert into Séance values (1,'Alhillal',1,1,100)
insert into Séance values (1,'Arif',2,2,150)
insert into Séance values (2,'Alhillal',2,2,80)
insert into Séance values (2,'MegaRama',1,2,200)
insert into Séance values (3,'MegaRama',2,2,600)
insert into Séance values (3,'Alhillal',3,1,100)
insert into Séance values (1,'Mabrouka',3,3,250)
select * from Acteur,Séance,Horaire,Salle,Cinéma
insert into Acteur values (12,'hillal',7,'03/11/2010')
insert into Acteur values (2,'shari','anas','15/10/1989')
insert into Acteur values (3,'Zaid','badir','2/2/1991')
insert into Acteur values (4,'Dahamne','brahim','1/6/1198')
delete from Acteur where idActeur=1
/* 3) Triggers */
-- a
create trigger a on Cinéma after delete
As
begin
print 'Imposible de supprimer'
rollback
end
-- b
create trigger bb on Acteur after insert
As
begin
declare @nom varchar(255),@prenom varchar(255),@id int
Select @nom = (select nom from inserted), @prenom = (select prénom from inserted)
select @id = (select idActeur from inserted)
update Acteur set nom = upper(@nom), prénom = upper(left(@prenom,1))+lower(right(@prenom,len(@prenom-1))) where idActeur= @id
commit
end
-- cdelete f
delete from Cinéma where
create trigger c on Cinéma after delete
As
begin
declare @nomCinéma varchar(255)
set @nomCinéma = (select nomCinéma from deleted)
delete from Salle where nomCinéma = @nomCinéma
delete from Séance where nomCinéma = @nomCinéma
commit
end
-- d
create trigger d on Horaire after insert, update
As
begin
declare @heureDébut time, @heureFin time, @Durée float,@idHoraire int
set @heureDébut = (select heureDébut from inserted)
set @heureFin = (select heureFin from inserted)
set @idHoraire = (select idHoraire from inserted)
update Horaire set Durée = (datediff(second,@heureDébut,@heureFin))/3600 where idHoraire = @idHoraire
commit
end
-- e
create trigger ee on Séance after insert, update
As
begin
if (select tarif from inserted) > 1000
begin
print 'Le Tarif ne doit pas dépasser 1000 DH.'
rollback
end
end
/* Procédures : */
--a
alter proc PS_1(@code int, @nbr int output)
As
begin
select @nbr = count(r.idFilm)
from Acteur a, Rôle r
where a.idActeur = @code and r.idActeur = a.idActeur
group by a.idActeur
end
declare @a int
exec PS_1 2, @a output
select @a
--b
create proc PS_2
As
begin
declare c cursor for (select * from Horaire)
declare @idHoraire int, @heureDébut time, @heureFin time,@Durée float, @test int
set @test = 1
declare @t table (idHoraire int, heureDébut time, heureFin time,Durée float)
open c
fetch next from c into @idHoraire, @heureDébut, @heureFin,@Durée
while @@fetch_status = 0
Begin
if (datediff(second,@heureDébut, @heureFin)) < 0 or ((datediff(second,@heureDébut, @heureFin)) / 3600) > 5
set @test = @idHoraire
fetch next from c into @idHoraire, @heureDébut, @heureFin,@Durée
end
if @test = 0
print 'les conndtion est faux dans lenregistrement est : '+ cast(@idHoraire as varchar)
else
begin
delete from Horaire
insert into Horaire select * from @t
end
close c
deallocate c
end
--c
create proc PS_3
As
begin
declare c1 cursor for (select c.ville, COUNT(*),COUNT(s.noSalle) from Cinéma c, Salle s where c.nomCinéma = s.nomCinéma group by c.ville order by c.ville asc)
declare @v varchar(255), @nbrcénima int, @nbrsalle int
open c1
fetch next from c1 into @v, @nbrcénima, @nbrsalle
while @@FETCH_STATUS = 0
begin
print 'la ville est : '+ cast(@v as varchar(255))+'Nbr Cénima '+cast(@nbrcénima as varchar(255))+'Nbr Salle '+cast(@nbrsalle as varchar(255))
fetch next from c1 into @v, @nbrcénima, @nbrsalle
end
close c1
deallocate c1
end
-- d
create table Grp_Cin (code int , descriptions varchar(255))
alter proc PS_4
As
begin
declare @i int
set @i = 1
while (@i < 5)
begin
insert into Grp_Cin values (@i,'Groupe'+CAST(@i as varchar))
set @i = @i +1
end
end
exec PS_4
select * from Grp_Cin
--e
alter table Cinéma add CodeG int
alter proc PS_4
As
begin
declare c2 cursor for (select c.nomCinéma,COUNT(s.noSalle) from Cinéma c, Salle s where c.nomCinéma = s.nomCinéma group by c.nomCinéma)
declare @NomC varchar(255), @compt int
open c2
fetch next from c2 into @NomC,@compt
while @@FETCH_STATUS = 0
begin
if @compt <= 1
update Cinéma set CodeG = 1 where nomCinéma = @NomC
if @compt between 1 and 3
update Cinéma set CodeG = 2 where nomCinéma = @NomC
if @compt between 3 and 5
update Cinéma set CodeG = 3 where nomCinéma = @NomC
if @compt > 5
update Cinéma set CodeG = 4 where nomCinéma = @NomC
fetch next from c2 into @NomC,@compt
end
end
exec PS_4
--f
create table T (
nomCinéma varchar(255),
numéro int,
rue varchar(255),
ville varchar(255),
noSalle int,
capacité int,
climatisée int
)
select * from t
create proc PS_5
As
begin
insert into T select Cinéma.nomCinéma, numéro, rue, ville,noSalle, capacité, climatisée from Cinéma , salle
end
exec PS_5
use FEM_SGBDII
/* Partie I : Programmation TSQL : (4 pts) */
--1
select char(65+32)
declare @i int
declare @t table (AlphaMaG varchar,ASCIIMaG int, AlphaMin varchar,ASCIIMin int)
set @i = 65
while(@i < 91)
begin
insert into @t values (CHAR(@i), @i,CHAR(@i+32), @i+32)
set @i = @i+1
end
select * from @t
--2
create table Livre(
IdL int primary key,
Titre varchar (4)
)
delete from Livre
declare @i int
set @i=0
while @i < 100
begin
insert into Livre values (@i+1,'T'+cast(@i as varchar(25)))
set @i = @i + 1
end
select * from Livre
--3
create table Livre_OLD(
IdL int primary key,
Titre varchar (4)
)
declare old cursor for (select * from Livre)
open old
declare @id int, @titre varchar(255)
fetch next from old into @id ,@titre
while @@FETCH_STATUS = 0
begin
if not exists(select * from Livre_OLD where IdL=@id and Titre = @titre)
begin
begin tran a
insert into Livre_OLD values (@id,@titre)
delete from Livre where IdL=@id and Titre = @titre
commit a
end
fetch next from old int @id,@titre
end
xs
/* Partie II : Procédures stockées et Triggers */
create table Cinéma (
nomCinéma varchar(255) primary key,
numéro int,
rue varchar(255),
ville varchar(255)
)
create table Salle (
nomCinéma varchar(255),
noSalle int,
capacité int,
climatisée int
primary key (noSalle)
)
insert into Salle values('s',1,1,1)
create table Horaire (
idHoraire int primary key,
heureDébut time,
heureFin time,
Durée float
)
insert into Séance values(1,'alhillal',1,1,100000)
insert into Séance values(101,'alhillal',1,1,100000)
insert into Séance values(100,'alhillal',1,1,100)
create table Séance (
idFilm int foreign key references Film(idFilm),
nomCinéma varchar(255) foreign key references Cinéma(nomCinéma),
noSalle int foreign key references Salle(noSalle),
idHoraire int foreign key references Horaire(idHoraire),
tarif int,
primary key (idFilm,nomCinéma,noSalle,idHoraire)
)
create table Film (
idFilm int primary key,
titre varchar(255),
année int,
genre varchar(255),
résumé varchar(255)
)
insert into Film values (1,'Planet des signe',1995,'Science fiction','Monde des singes')
insert into Film values (2,'Avatar',2010,'Science fiction','autre vie')
insert into Film values (3,'Le petit monde de borweur',2012,'comique','monde de petit')
insert into Film values (4,'La rélité',2005,'drama','la fin de monde')
create table Acteur (
idActeur int primary key,
nom varchar(255),
prénom varchar(255),
DateNaissance date
)
create table Rôle (
idActeur int foreign key references Acteur(idActeur),
idFilm int foreign key references Film(idFilm),
nomRôle varchar(255)
primary key(idActeur,idFilm)
)
insert into Cinéma values('MegaRama',1,'Agdal','Casa')
insert into Cinéma values('Arif',2,'Dawdiyat','Marrakech')
insert into Cinéma values('Alhillal',3,'Sou9 Arabi3','Rabat')
insert into Cinéma values('Mabrouka',4,'Prince','marrakech')
insert into Rôle values (1,1,'Pressonage pricipal')
insert into Rôle values (2,1,'Scéane comique')
insert into Rôle values (3,1,'Combat')
insert into Rôle values (2,3,'Police')
insert into Salle values ('MegaRama',12,100,0)
insert into Salle values ('MegaRama',5,250,1)
insert into Salle values ('Arif',6,85,0)
insert into Salle values ('Alhillal',10,150,0)
insert into Horaire values (1,'2:0','4:0',NULL)
insert into Horaire values (2,'8:0','1:0',NULL)
insert into Horaire values (3,'1:0','6:0',NULL)
insert into Horaire values (4,'9:0','10:0',NULL)
insert into Séance values (1,'Alhillal',1,1,100)
insert into Séance values (1,'Arif',2,2,150)
insert into Séance values (2,'Alhillal',2,2,80)
insert into Séance values (2,'MegaRama',1,2,200)
insert into Séance values (3,'MegaRama',2,2,600)
insert into Séance values (3,'Alhillal',3,1,100)
insert into Séance values (1,'Mabrouka',3,3,250)
select * from Acteur,Séance,Horaire,Salle,Cinéma
insert into Acteur values (12,'hillal',7,'03/11/2010')
insert into Acteur values (2,'shari','anas','15/10/1989')
insert into Acteur values (3,'Zaid','badir','2/2/1991')
insert into Acteur values (4,'Dahamne','brahim','1/6/1198')
delete from Acteur where idActeur=1
/* 3) Triggers */
-- a
create trigger a on Cinéma after delete
As
begin
print 'Imposible de supprimer'
rollback
end
-- b
create trigger bb on Acteur after insert
As
begin
declare @nom varchar(255),@prenom varchar(255),@id int
Select @nom = (select nom from inserted), @prenom = (select prénom from inserted)
select @id = (select idActeur from inserted)
update Acteur set nom = upper(@nom), prénom = upper(left(@prenom,1))+lower(right(@prenom,len(@prenom-1))) where idActeur= @id
commit
end
-- cdelete f
delete from Cinéma where
create trigger c on Cinéma after delete
As
begin
declare @nomCinéma varchar(255)
set @nomCinéma = (select nomCinéma from deleted)
delete from Salle where nomCinéma = @nomCinéma
delete from Séance where nomCinéma = @nomCinéma
commit
end
-- d
create trigger d on Horaire after insert, update
As
begin
declare @heureDébut time, @heureFin time, @Durée float,@idHoraire int
set @heureDébut = (select heureDébut from inserted)
set @heureFin = (select heureFin from inserted)
set @idHoraire = (select idHoraire from inserted)
update Horaire set Durée = (datediff(second,@heureDébut,@heureFin))/3600 where idHoraire = @idHoraire
commit
end
-- e
create trigger ee on Séance after insert, update
As
begin
if (select tarif from inserted) > 1000
begin
print 'Le Tarif ne doit pas dépasser 1000 DH.'
rollback
end
end
/* Procédures : */
--a
alter proc PS_1(@code int, @nbr int output)
As
begin
select @nbr = count(r.idFilm)
from Acteur a, Rôle r
where a.idActeur = @code and r.idActeur = a.idActeur
group by a.idActeur
end
declare @a int
exec PS_1 2, @a output
select @a
--b
create proc PS_2
As
begin
declare c cursor for (select * from Horaire)
declare @idHoraire int, @heureDébut time, @heureFin time,@Durée float, @test int
set @test = 1
declare @t table (idHoraire int, heureDébut time, heureFin time,Durée float)
open c
fetch next from c into @idHoraire, @heureDébut, @heureFin,@Durée
while @@fetch_status = 0
Begin
if (datediff(second,@heureDébut, @heureFin)) < 0 or ((datediff(second,@heureDébut, @heureFin)) / 3600) > 5
set @test = @idHoraire
fetch next from c into @idHoraire, @heureDébut, @heureFin,@Durée
end
if @test = 0
print 'les conndtion est faux dans lenregistrement est : '+ cast(@idHoraire as varchar)
else
begin
delete from Horaire
insert into Horaire select * from @t
end
close c
deallocate c
end
--c
create proc PS_3
As
begin
declare c1 cursor for (select c.ville, COUNT(*),COUNT(s.noSalle) from Cinéma c, Salle s where c.nomCinéma = s.nomCinéma group by c.ville order by c.ville asc)
declare @v varchar(255), @nbrcénima int, @nbrsalle int
open c1
fetch next from c1 into @v, @nbrcénima, @nbrsalle
while @@FETCH_STATUS = 0
begin
print 'la ville est : '+ cast(@v as varchar(255))+'Nbr Cénima '+cast(@nbrcénima as varchar(255))+'Nbr Salle '+cast(@nbrsalle as varchar(255))
fetch next from c1 into @v, @nbrcénima, @nbrsalle
end
close c1
deallocate c1
end
-- d
create table Grp_Cin (code int , descriptions varchar(255))
alter proc PS_4
As
begin
declare @i int
set @i = 1
while (@i < 5)
begin
insert into Grp_Cin values (@i,'Groupe'+CAST(@i as varchar))
set @i = @i +1
end
end
exec PS_4
select * from Grp_Cin
--e
alter table Cinéma add CodeG int
alter proc PS_4
As
begin
declare c2 cursor for (select c.nomCinéma,COUNT(s.noSalle) from Cinéma c, Salle s where c.nomCinéma = s.nomCinéma group by c.nomCinéma)
declare @NomC varchar(255), @compt int
open c2
fetch next from c2 into @NomC,@compt
while @@FETCH_STATUS = 0
begin
if @compt <= 1
update Cinéma set CodeG = 1 where nomCinéma = @NomC
if @compt between 1 and 3
update Cinéma set CodeG = 2 where nomCinéma = @NomC
if @compt between 3 and 5
update Cinéma set CodeG = 3 where nomCinéma = @NomC
if @compt > 5
update Cinéma set CodeG = 4 where nomCinéma = @NomC
fetch next from c2 into @NomC,@compt
end
end
exec PS_4
--f
create table T (
nomCinéma varchar(255),
numéro int,
rue varchar(255),
ville varchar(255),
noSalle int,
capacité int,
climatisée int
)
select * from t
create proc PS_5
As
begin
insert into T select Cinéma.nomCinéma, numéro, rue, ville,noSalle, capacité, climatisée from Cinéma , salle
end
exec PS_5
Formateur
|
Directeur Pédagogique
|
Directeur du complexe/Directeur de
l'EFP
|
Visa de La DRTA
|