SET SCHEMA 'fsmrel' ;
DROP TABLE IF EXISTS INSCRIPTION CASCADE ;
DROP TABLE IF EXISTS PRE_INSCRIPTION CASCADE ;
DROP TABLE IF EXISTS CANDIDATURE CASCADE ;
DROP TABLE IF EXISTS PERSONNE CASCADE ;
DROP TABLE IF EXISTS CATALOGUE CASCADE ;
DROP TABLE IF EXISTS SESSION CASCADE ;
DROP TABLE IF EXISTS AVOIR_MODE CASCADE ;
DROP TABLE IF EXISTS MODE_FORMATION CASCADE ;
DROP TABLE IF EXISTS FORMATION CASCADE ;
CREATE TABLE FORMATION
(
FormationId INT NOT NULL
, FormationCode CHAR(7) NOT NULL
, FormationNom VARCHAR(64) NOT NULL
, CONSTRAINT FORMATION_PK PRIMARY KEY (FormationId)
, CONSTRAINT FORMATION_AK UNIQUE (FormationCode)
) ;
CREATE TABLE MODE_FORMATION
(
ModeFormationId INT NOT NULL
, ModeFormationCode CHAR(2) NOT NULL
, ModeFormationNom VARCHAR(64) NOT NULL
, CONSTRAINT MODE_FORMATION_PK PRIMARY KEY (ModeFormationId)
, CONSTRAINT MODE_FORMATION_AK UNIQUE (ModeFormationCode)
) ;
CREATE TABLE AVOIR_MODE
(
FormationId INT NOT NULL
, ModeFormationId INT NOT NULL
, CONSTRAINT AVOIR_MODE_PK PRIMARY KEY (FormationId, ModeFormationId)
, CONSTRAINT AVOIR_MODE_FORMATION_FK FOREIGN KEY (FormationId)
REFERENCES FORMATION (FormationId)
, CONSTRAINT AVOIR_MODE_MODE_FORMATION_FK FOREIGN KEY (ModeFormationId)
REFERENCES MODE_FORMATION (ModeFormationId)
) ;
CREATE TABLE SESSION
(
SessionId INT NOT NULL
, SessionCode CHAR(7) NOT NULL
, SessionAnnee INT NOT NULL
, SessionMois INT NOT NULL
, CONSTRAINT SESSION_PK PRIMARY KEY (SessionId)
, CONSTRAINT SESSION_CODE_AK UNIQUE (SessionCode)
, CONSTRAINT SESSION_ANNEE_AK UNIQUE (SessionAnnee)
) ;
CREATE TABLE CATALOGUE
(
FormationId INT NOT NULL
, ModeFormationId INT NOT NULL
, SessionId INT NOT NULL
, CONSTRAINT CATALOGUE_PK PRIMARY KEY (FormationId, ModeFormationId, SessionId)
, CONSTRAINT CATALOGUE_SESSION_FK FOREIGN KEY (SessionId)
REFERENCES SESSION (SessionId)
, CONSTRAINT CATALOGUE_AVOIR_MODE_FK FOREIGN KEY (FormationId, ModeFormationId)
REFERENCES AVOIR_MODE (FormationId, ModeFormationId)
) ;
CREATE TABLE PERSONNE
(
PersonneId INT NOT NULL
, PersonneNom VARCHAR(64) NOT NULL
, PersonnePrenom VARCHAR(64) NOT NULL
, Etc VARCHAR(64) NOT NULL
, CONSTRAINT PERSONNE_PK PRIMARY KEY (PersonneId)
) ;
CREATE TABLE CANDIDATURE
(
CandidatId INT NOT NULL
, SessionId INT NOT NULL
, PersonneId INT NOT NULL
, CandidatNumero INT NOT NULL
, CONSTRAINT CANDIDATURE_PK PRIMARY KEY (CandidatId)
, CONSTRAINT CANDIDATURE_NUMERO_AK UNIQUE (CandidatNumero)
, CONSTRAINT CANDIDATURE_SESSION_AK UNIQUE (CandidatId, SessionId)
, CONSTRAINT CANDIDATURE_SESSION_FK FOREIGN KEY (SessionId)
REFERENCES SESSION (SessionId)
, CONSTRAINT CANDIDATURE_PERSONNE_FK FOREIGN KEY (PersonneId)
REFERENCES PERSONNE (PersonneId)
) ;
CREATE TABLE PRE_INSCRIPTION
(
CandidatId INT NOT NULL
, FormationId INT NOT NULL
, ModeFormationId INT NOT NULL
, SessionId INT NOT NULL
, CONSTRAINT PRE_INSCRIPTION_PK PRIMARY KEY (CandidatId, FormationId, ModeFormationId)
, CONSTRAINT PRE_INSCRIPTION_CATALOGUE_FK FOREIGN KEY (FormationId, ModeFormationId, SessionId)
REFERENCES CATALOGUE (FormationId, ModeFormationId, SessionId)
, CONSTRAINT PRE_INSCRIPTION_CANDIDATURE_FK FOREIGN KEY (CandidatId, SessionId)
REFERENCES CANDIDATURE (CandidatId, SessionId)
) ;
CREATE TABLE INSCRIPTION
(
CandidatId INT NOT NULL
, FormationId INT NOT NULL
, ModeFormationId INT NOT NULL
, CONSTRAINT INSCRIPTION_PK PRIMARY KEY (CandidatId)
, CONSTRAINT INSCRIPTION_PRE_INSCRIPTION_FK FOREIGN KEY (CandidatId, FormationId, ModeFormationId)
REFERENCES PRE_INSCRIPTION (CandidatId, FormationId, ModeFormationId)
) ;
En passant, quel SGBD utilisez-vous ?
Partager