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
| CREATE TABLE UT_utilisateur(
UT_ident INT AUTO_INCREMENT,
UT_nom VARCHAR(50) NOT NULL,
UT_prenom VARCHAR(50) NOT NULL,
UT_courriel VARCHAR(64),
PRIMARY KEY(UT_ident)
);
CREATE TABLE PR_profil(
PR_ident INT AUTO_INCREMENT,
PR_code CHAR(4) NOT NULL,
PR_nom VARCHAR(50) NOT NULL,
PR_debval DATE NOT NULL,
PR_finval DATE NOT NULL,
PRIMARY KEY(PR_ident),
UNIQUE(PR_code)
);
CREATE TABLE FC_fonction(
FC_ident INT AUTO_INCREMENT,
FC_code CHAR(6) NOT NULL,
FC_libelle VARCHAR(50) NOT NULL,
FC_debval DATE NOT NULL,
FC_finval DATE NOT NULL,
PRIMARY KEY(FC_ident),
UNIQUE(FC_code)
);
CREATE TABLE UP_ut_profil(
UT_ident INT,
PR_ident INT,
CA_date DATE,
PRIMARY KEY(UT_ident, PR_ident, CA_date),
FOREIGN KEY(UT_ident) REFERENCES UT_utilisateur(UT_ident),
FOREIGN KEY(PR_ident) REFERENCES PR_profil(PR_ident)
);
CREATE TABLE HA_habiliter(
PR_ident INT,
FC_ident INT,
CA_date DATE,
PRIMARY KEY(PR_ident, FC_ident, CA_date),
FOREIGN KEY(PR_ident) REFERENCES PR_profil(PR_ident),
FOREIGN KEY(FC_ident) REFERENCES FC_fonction(FC_ident)
); |
Partager