Scribd
Upload
15 views2 pages

Script BDD SQL Server

The document outlines the SQL commands to create a database named CACI_Inscription2026, including tables for Personne, Candidat, AgentScolarite, formation, promo, and Inscription. Each table includes various constraints such as primary keys, foreign keys, and checks to ensure data integrity. Additionally, indexes are created for efficient querying on specific fields like email and statut.

Uploaded by

Iliés Abtout
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
15 views2 pages

Script BDD SQL Server

The document outlines the SQL commands to create a database named CACI_Inscription2026, including tables for Personne, Candidat, AgentScolarite, formation, promo, and Inscription. Each table includes various constraints such as primary keys, foreign keys, and checks to ensure data integrity. Additionally, indexes are created for efficient querying on specific fields like email and statut.

Uploaded by

Iliés Abtout
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

create database CACI_Inscription2026

go
use CACI_Inscription2026
go
create table Personne
(idPer nvarchar(20) primary key,nom nvarchar(30) not null,prenom
nvarchar(30) not null,
dateNaiss date not null constraint ck_datenaiss
check(dateNaiss<=GetDATE()),email nvarchar(100)
not null constraint u_Email unique,sexe char(1) constraint ck_sexe
check(sexe in ('f','m')),
modePassHash nvarchar(255) not null,
role nvarchar(20) not null constraint ck_role check(role in
('Candidat','Agent','Admin')),
photo varbinary(max) )
go

create table Candidat (idPer nvarchar(20) primary key,CV varbinary(max),


constraint FK_Candidat_Personne foreign key (idPer) references Personne
(idPer )
on delete cascade)
go
create table AgentScolarite (idPer nvarchar(20) primary key,DateEmbauche
date not null
constraint ck_DateEmbauche check(DateEmbauche<=GetDATE()),salaire
decimal (10,2) not null
constraint ck_salaire check (salaire>=24000)
constraint FK_AgentScolarite_Personne foreign key (idPer) references
Personne (idPer )
on delete cascade)
go

create table formation (idForm nvarchar(20) primary key,nomForm


nvarchar(150) not null constraint
uq_nomForm unique )
go

create table promo (idPromo nvarchar(20) primary key , AnneePromo


nvarchar(9) constraint
ck_AnneePromo check (AnneePromo like '[0-9][0-9][0-9][0-9]/[0-9][0-9]
[0-9][0-9]'),
description nvarchar(255))
go

create table Inscription


( idIns nvarchar(20) primary key ,dateIns date not null constraint
ck_dateIns check(dateIns<=GetDATE()),
statut nvarchar(20) not null constraint df_statut default 'En-Attente'
constraint ck_statut check (statut in ('En-
Attente','Validee','Refusee')),
IdPer nvarchar(20) not null,
IdForm nvarchar(20) not null,
IdPromo nvarchar(20) not null,
idAgent nvarchar(20) not null,
constraint FK_Ins_Condidate foreign key (idPer) references Candidat
(idPer ) ,
constraint FK_Ins_Formation foreign key (idform) references formation
(idform ),
constraint FK_Ins_promo foreign key (idpromo) references promo
(idpromo ),
constraint FK_Ins_Agent foreign key (idAgent) references Agentscolarite
(idper ) ,
Constraint u_inscription unique (idPer,idform,idpromo))
go

create index ix_email on personne(email)


create index ix_statut on inscription(statut)
create index ix_idfrom on inscription(idform)

You might also like