1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48
| CREATE TABLE DIR_direction(
DIR_ident INT IDENTITY,
DIR_code CHAR(4) NOT NULL,
DIR_libelle VARCHAR(128) NOT NULL,
PRIMARY KEY(DIR_ident),
UNIQUE(DIR_code)
);
CREATE TABLE SDI_sous_direction(
DIR_ident_SDI INT,
SDI_chrono SMALLINT,
SDI_code CHAR(6),
SDI_libelle VARCHAR(128) NOT NULL,
PRIMARY KEY(DIR_ident_SDI, SDI_chrono),
UNIQUE(SDI_code),
FOREIGN KEY(DIR_ident_SDI) REFERENCES DIR_direction(DIR_ident)
);
CREATE TABLE SVC_service(
SVC_ident INT IDENTITY,
SVC_code CHAR(6) NOT NULL,
SVC_libelle VARCHAR(128) NOT NULL,
DIR_ident_SDI INT NOT NULL,
SDI_chrono SMALLINT NOT NULL,
PRIMARY KEY(SVC_ident),
UNIQUE(SVC_code),
FOREIGN KEY(DIR_ident_SDI, SDI_chrono) REFERENCES SDI_sous_direction(DIR_ident_SDI, SDI_chrono)
);
CREATE TABLE PRS_personne(
PRS_ident INT IDENTITY,
PRS_nom VARCHAR(50) NOT NULL,
PRS_prenom VARCHAR(50) NOT NULL,
SVC_ident INT,
DIR_ident_SDI INT,
SDI_chrono SMALLINT,
DIR_ident INT,
PRIMARY KEY(PRS_ident),
FOREIGN KEY(SVC_ident) REFERENCES SVC_service(SVC_ident),
FOREIGN KEY(DIR_ident_SDI, SDI_chrono) REFERENCES SDI_sous_direction(DIR_ident_SDI, SDI_chrono),
FOREIGN KEY(DIR_ident) REFERENCES DIR_direction(DIR_ident)
);
alter table PRS_personne
add constraint PRS_CHK_RATT
check ( (DIR_ident is not null and DIR_ident_sdi is null and SVC_ident is null)
or (DIR_ident is null and DIR_ident_sdi is not null and SVC_ident is null)
or (DIR_ident is null and DIR_ident_sdi is null and SVC_ident is not null)) |
Partager