ISTA
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 3
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
EMPLOYE (Matricule, nom, prenom, echelle)
SERVICE (Numero, Nom, Adresse)
PROJET (code, Matricule, Numero, DateDebut, NbreJour, Comission) code incrémenté
automatiquement.
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe
déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau
service sera inséré, s’il existe, le service trouvé sera modifié).
4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero
de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi
retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à
la fois.
5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de
réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à
un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet
d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
Exercice 2: Soit le modèle relationnel suivant :
TECHNICIEN (code, nom, agence, prix_heure)
ORDINATEUR (réf, marque, processeur, mémoire, disque)
REPARER (numéro, codeTec, réfOrdi, date, nbreHeure) numéro incrémenté
automatiquement
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un technicien après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un ordinateur après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
4) Créer une procédure qui permet d’enregistrer une réparation en vérifiant si la réf de
l’ordinateur et le code du technicien existent déjà (valeur retournée 0) ou non (valeur
retournée -1), on désire aussi retourner le numéro affecté à la réparation.
5) Créer une procédure qui supprime une réparation après avoir vérifié qu’elle n’est pas en
cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un ordinateur après avoir vérifié qu’il n’est pas affecté
à une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un technicien après avoir vérifié qu’il n’est affecté à
une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
8) Créer une procédure qui permet de modifier le prix de maintenance d’un technicien (dont
le code est passé en paramètre) par heure selon la valeur de ce dernier :
a. <100 il sera diminué de 10%
b. Entre 100 et 150 il restera inchangé.
c. >150 il sera augmenté de 2%
On désire récupérer la nouvelle valeur du prix dans un paramètre de sortie.
create database TP_11_EX2
USE TP_11_EX2
-- 1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
create table EMPLOYE (
Matricule int primary key,
nom varchar(50),
prenom varchar(50),
echelle int
)
create table SERVICEE (
Numero int primary key,
Nom varchar(50),
Adresse varchar(100)
)
create table PROJET (
code int identity (1,1),
Matricule int foreign key references employe(matricule) on delete cascade on update cascade,
Numero int foreign key references servicee(numero) on delete cascade on update cascade,
DateDebut datetime ,
NbreJour int,
Comission float
)
-- 2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
CREATE PROCEDURE pro_employee (
@matricule INT OUTPUT,
@Nom VARCHAR(30),
@prenom varchar(30),
@echelle VARCHAR(30)
)
AS
begin
if not exists (select * from employe where @matricule=matricule)
begin
INSERT INTO Employe (matricule,nom,prenom,echelle)
VALUES (@matricule,@nom,@prenom,@echelle)
RETURN 0
end
return -1
end
-- 3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau service sera inséré, s’il existe, le service trouvé sera modifié).
CREATE PROCEDURE insert_service (
@numero INT ,
@Nom VARCHAR(30),
@adresse varchar(30)
)
AS
declare @etat int
declare @con int
set @etat=0
select @con=count (*)from servicee where numero= @numero
if @con=0
begin
INSERT INTO servicee values (@numero,@nom,@adresse)
end
else
begin
update servicee set nom=@nom,adresse=@adresse where @numero=numero
end
-- 4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à la fois.
CREATE PROCEDURE proc_projet (
@code INT OUTPUT,
@matricule INT,
@numero VARCHAR(30),
@DateDebut datetime ,
@NbreJour int,
@Comission float
)
AS
if exists (select * from employe where matricule= @matricule )
and exists (select * from servvicee where numero= @numero )
and not exists (select * from projet where @datedebut between datedebut and dateadd(day,nbrejour,datedebut) and matricule=@matricule)
begin
INSERT INTO projet (matricule,numero,DateDebut,NbreJour,Comission)
VALUES (@matricule,@numero,@DateDebut,@NbreJour,@Comission)
SELECT @code=SCOPE_IDENTITY()
RETURN 0
end
return -1
-- 5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_projet(
@code INT
)
AS
if not exists (select * from projet where @code=code and getdate() not between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from projet where code=@code
RETURN 0
end
return -1
-- 6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE suprimmer_employe(
@matricule INT
)
AS
if not exists (select matricule from projet where getdate() between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from employe where matricule=@matricule
RETURN 0
end
return -1
end catch
-- 7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_service(
@numero INT
)
AS
begin
begin try
if exists (select * from projet where @numero=numero)
delete from employe where @matricule=matricule
RETURN 0
end try
begin catch
return -1
end catch
end
------------------------------------------------------------------------------------------------------
create database EX2_ser3
use EX2_ser3
create table TECHNICIEN (
code int primary key ,
nom varchar(50),
agence varchar(50),
prix_heure money )
create table ORDINATEUR (
réf int primary key,
marque varchar(50),
processeur varchar(50),
mémoire varchar(50),
disque varchar(50))
create table REPARER (
numéro int primary key identity,
codeTec int foreign key references TECHNICIEN(code)on delete cascade on update cascade ,
réfOrdi int foreign key references ORDINATEUR (réf) on delete cascade on update cascade,
datee datetime ,
nbreHeure int )
--2
create procedure insert_tec (@code int , @nom varchar , @Age varchar , @prix_h varchar)
as
if not exists (select * from TECHNICIEN where code = @code)
begin
insert into TECHNICIEN values (@code,@nom,@Age,@prix_h)
return 0
end
return -1
--3
create procedure insert_Ord (@ref int , @Marque varchar , @processeur varchar , @mémoire varchar ,@disque varchar)
as
if not exists (select * from ORDINATEUR where réf = @ref)
begin
insert into ORDINATEUR values (@ref,@Marque,@processeur,@mémoire ,@disque)
return 0
end
return -1
--4
Sidi Youssef
Ben Ali
Marrakech
SGBD 2
Série N° 3
Formateur : LAMOURI Najib
Exercice 1: Soit le modèle relationnel suivant :
EMPLOYE (Matricule, nom, prenom, echelle)
SERVICE (Numero, Nom, Adresse)
PROJET (code, Matricule, Numero, DateDebut, NbreJour, Comission) code incrémenté
automatiquement.
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe
déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau
service sera inséré, s’il existe, le service trouvé sera modifié).
4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero
de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi
retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à
la fois.
5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de
réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à
un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet
d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
Exercice 2: Soit le modèle relationnel suivant :
TECHNICIEN (code, nom, agence, prix_heure)
ORDINATEUR (réf, marque, processeur, mémoire, disque)
REPARER (numéro, codeTec, réfOrdi, date, nbreHeure) numéro incrémenté
automatiquement
1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
2) Créer une procédure qui permet d’insérer un technicien après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
3) Créer une procédure qui permet d’insérer un ordinateur après avoir vérifié si celui-ci
existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
4) Créer une procédure qui permet d’enregistrer une réparation en vérifiant si la réf de
l’ordinateur et le code du technicien existent déjà (valeur retournée 0) ou non (valeur
retournée -1), on désire aussi retourner le numéro affecté à la réparation.
5) Créer une procédure qui supprime une réparation après avoir vérifié qu’elle n’est pas en
cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
6) Créer une procédure qui supprime un ordinateur après avoir vérifié qu’il n’est pas affecté
à une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
7) Créer une procédure qui supprime un technicien après avoir vérifié qu’il n’est affecté à
une réparation actuellement. (valeur retournée 0 si supprimé, -1 si non)
8) Créer une procédure qui permet de modifier le prix de maintenance d’un technicien (dont
le code est passé en paramètre) par heure selon la valeur de ce dernier :
a. <100 il sera diminué de 10%
b. Entre 100 et 150 il restera inchangé.
c. >150 il sera augmenté de 2%
On désire récupérer la nouvelle valeur du prix dans un paramètre de sortie.
create database TP_11_EX2
USE TP_11_EX2
-- 1) Créer les tables du modèle relationnel puis remplir ces tables par un jeu de données.
create table EMPLOYE (
Matricule int primary key,
nom varchar(50),
prenom varchar(50),
echelle int
)
create table SERVICEE (
Numero int primary key,
Nom varchar(50),
Adresse varchar(100)
)
create table PROJET (
code int identity (1,1),
Matricule int foreign key references employe(matricule) on delete cascade on update cascade,
Numero int foreign key references servicee(numero) on delete cascade on update cascade,
DateDebut datetime ,
NbreJour int,
Comission float
)
-- 2) Créer une procédure qui permet d’insérer un employé après avoir vérifié si celui-ci existe déjà (valeur retournée est -1) ou non (valeur retournée est 0).
CREATE PROCEDURE pro_employee (
@matricule INT OUTPUT,
@Nom VARCHAR(30),
@prenom varchar(30),
@echelle VARCHAR(30)
)
AS
begin
if not exists (select * from employe where @matricule=matricule)
begin
INSERT INTO Employe (matricule,nom,prenom,echelle)
VALUES (@matricule,@nom,@prenom,@echelle)
RETURN 0
end
return -1
end
-- 3) Créer une procédure qui permet d’insérer un service (si le numero n’existe pas le nouveau service sera inséré, s’il existe, le service trouvé sera modifié).
CREATE PROCEDURE insert_service (
@numero INT ,
@Nom VARCHAR(30),
@adresse varchar(30)
)
AS
declare @etat int
declare @con int
set @etat=0
select @con=count (*)from servicee where numero= @numero
if @con=0
begin
INSERT INTO servicee values (@numero,@nom,@adresse)
end
else
begin
update servicee set nom=@nom,adresse=@adresse where @numero=numero
end
-- 4) Créer une procédure qui permet d’insérer un projet en vérifiant si le matricule et le numero de service existent déjà (valeur retournée 0) ou non (valeur retournée -1), on désire aussi retourner le code affecté au projet. En plus, un employé doit être affecté à un seul projet à la fois.
CREATE PROCEDURE proc_projet (
@code INT OUTPUT,
@matricule INT,
@numero VARCHAR(30),
@DateDebut datetime ,
@NbreJour int,
@Comission float
)
AS
if exists (select * from employe where matricule= @matricule )
and exists (select * from servvicee where numero= @numero )
and not exists (select * from projet where @datedebut between datedebut and dateadd(day,nbrejour,datedebut) and matricule=@matricule)
begin
INSERT INTO projet (matricule,numero,DateDebut,NbreJour,Comission)
VALUES (@matricule,@numero,@DateDebut,@NbreJour,@Comission)
SELECT @code=SCOPE_IDENTITY()
RETURN 0
end
return -1
-- 5) Créer une procédure qui supprime un projet après avoir vérifié qu’il n’est pas en cours de réalisation. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_projet(
@code INT
)
AS
if not exists (select * from projet where @code=code and getdate() not between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from projet where code=@code
RETURN 0
end
return -1
-- 6) Créer une procédure qui supprime un employé après avoir vérifié qu’il n’est pas affecté à un projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE suprimmer_employe(
@matricule INT
)
AS
if not exists (select matricule from projet where getdate() between datedebut and dateadd(day,nbrjour,datedebut))
begin
delete from employe where matricule=@matricule
RETURN 0
end
return -1
end catch
-- 7) Créer une procédure qui supprime un service après avoir vérifié que le service n’est sujet d’aucun projet actuellement. (valeur retournée 0 si supprimé, -1 si non)
CREATE PROCEDURE pro_delete_service(
@numero INT
)
AS
begin
begin try
if exists (select * from projet where @numero=numero)
delete from employe where @matricule=matricule
RETURN 0
end try
begin catch
return -1
end catch
end
------------------------------------------------------------------------------------------------------
create database EX2_ser3
use EX2_ser3
create table TECHNICIEN (
code int primary key ,
nom varchar(50),
agence varchar(50),
prix_heure money )
create table ORDINATEUR (
réf int primary key,
marque varchar(50),
processeur varchar(50),
mémoire varchar(50),
disque varchar(50))
create table REPARER (
numéro int primary key identity,
codeTec int foreign key references TECHNICIEN(code)on delete cascade on update cascade ,
réfOrdi int foreign key references ORDINATEUR (réf) on delete cascade on update cascade,
datee datetime ,
nbreHeure int )
--2
create procedure insert_tec (@code int , @nom varchar , @Age varchar , @prix_h varchar)
as
if not exists (select * from TECHNICIEN where code = @code)
begin
insert into TECHNICIEN values (@code,@nom,@Age,@prix_h)
return 0
end
return -1
--3
create procedure insert_Ord (@ref int , @Marque varchar , @processeur varchar , @mémoire varchar ,@disque varchar)
as
if not exists (select * from ORDINATEUR where réf = @ref)
begin
insert into ORDINATEUR values (@ref,@Marque,@processeur,@mémoire ,@disque)
return 0
end
return -1
--4