Correction Var 6 2011 partie Base de donnée

par Mr.Hicham Mar Juil 12, 2011 3:13 pm

Voila la correction dial partie BD var 6 2011, mais sans Question 6 mamt2akadch manno, et je lé pas posté

Q 1 :

Code:: use master go if exists(select name from sys.databases where name = 'v6_2011') drop database v6_2011 create database v6_2011 go use v6_2011 go if OBJECT_ID('Personne','U') is not null drop table Personne create table Personne(login varchar(20) primary key not null, nom_personne nvarchar(20), prenom_personne nvarchar(20), passe varchar(15), email varchar(20)) go if OBJECT_ID('Ville','U') is not null drop table Ville create table Ville(code_ville int primary key not null, nom_ville nvarchar(50)) go if OBJECT_ID('Circuit','U') is not null drop table Circuit create table Circuit(code_circuit int primary key not null, description_circuit varchar(max), prix_moyen float, code_ville int foreign key references Ville(code_ville) on delete cascade on update cascade) go if OBJECT_ID('Caleche','U') is not null drop table Caleche create table Caleche(code_caleche int identity(1,1) primary key not null, description_caleche varchar(max), login varchar(20) foreign key references Personne(login) on delete cascade on update cascade, code_ville int foreign key references Ville(code_ville) on delete cascade on update cascade) go if OBJECT_ID('Image','U') is not null drop table Image create table Image(code_image int identity(1,1) primary key not null, chemin_image varchar(100), description_image varchar(max), code_caleche int foreign key references Caleche(code_caleche) on delete cascade on update cascade) go if OBJECT_ID('Lieu','U') is not null drop table Lieu create table Lieu(code_lieu int primary key not null, description_lieu varchar(max)) go if OBJECT_ID('Englobe','U') is not null drop table Englobe create table Englobe(code_circuit int foreign key references Circuit(code_circuit) on delete cascade on update cascade, code_lieu int foreign key references Lieu(code_lieu) on delete cascade on update cascade, order_passage int, constraint pk_englobe primary key(code_circuit,code_lieu)) go /* Jeu D'essai */ insert into Personne values('tdi2','hicham','karim','azqswx','ttt@tt.com') insert into Personne values('tri2','hanan','souad','azqswx123','ttt@tt.com') insert into Ville values(1,'taza') insert into Ville values(2,'casa') insert into Circuit values(1,'description 1',50.20,1) insert into Circuit values(2,'description 2',30.70,2) insert into Caleche values('description 1','tdi2',1) insert into Caleche values('description 2','tri2',2) insert into Image values('d:\t.jpg','description 1',1) insert into Image values('d:\h.jpg','description 2',2) insert into Lieu values(1,'description 1') insert into Lieu values(2,'description 2') insert into Englobe values(1,1,30) insert into Englobe values(2,2,70)

Q 2 :

Code:: alter table Personne add constraint chk_email check(email like '%@%') go

Q 3:

Code:: if OBJECT_ID('tr1','TR') is not null drop trigger tr1 go create trigger tr1 ON Image for delete as begin raiserror('Impossible',16,1) rollback transaction end

Q 4:

Code:: if OBJECT_ID('fn1','FN') is not null drop function fn1 go create function fn1(@nom nvarchar(20)) returns table as begin return( select C.code_caleche,C.description_caleche,C.login,C.code_ville from Caleche C INNER JOIN Ville V ON C.code_ville = V.code_ville and V.nom_ville = @nom) end

Q 5:

Code:: if OBJECT_ID('ps1','P') is not null drop proc ps1 go create proc ps1 as delete from Circuit where code_circuit not in (select code_circuit from Englobe)

par Souka_Souka Mer Juil 13, 2011 12:45 am

Merciiii

par Souka_Souka Mer Juil 13, 2011 12:47 am

Merciiii

par Contenu sponsorisé

Correction Var 6 2011 partie Base de donnée

Correction Var 6 2011 partie Base de donnée

Re: Correction Var 6 2011 partie Base de donnée

Re: Correction Var 6 2011 partie Base de donnée

Re: Correction Var 6 2011 partie Base de donnée