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 49 50 51 52 53 54
| CREATE TABLE LI_livre(
LI_ident INT IDENTITY,
LI_titre VARCHAR(255) NOT NULL,
PRIMARY KEY(LI_ident)
);
CREATE TABLE EO_edition(
LI_ident INT,
EO_ident INT IDENTITY,
EO_ISBN CHAR(13),
EO_resume VARCHAR(1024),
EO_date DATE NOT NULL,
PRIMARY KEY(LI_ident, EO_ident),
UNIQUE(EO_ISBN),
FOREIGN KEY(LI_ident) REFERENCES LI_livre(LI_ident)
);
CREATE TABLE IN_intervenant(
IN_ident INT IDENTITY,
IN_nom VARCHAR(50) NOT NULL,
IN_prenom VARCHAR(50) NOT NULL,
IN_pseudo VARCHAR(5),
IN_ddn DATE NOT NULL,
PRIMARY KEY(IN_ident)
);
CREATE TABLE RL_role(
RL_ident INT IDENTITY,
RL_code CHAR(4) NOT NULL,
RL_libelle VARCHAR(128) NOT NULL,
PRIMARY KEY(RL_ident),
UNIQUE(RL_code)
);
CREATE TABLE EX_exemplaire(
LI_ident INT,
EO_ident INT,
EX_ident INT IDENTITY,
EX_codbar CHAR(12) NOT NULL,
EX_datimp DATE NOT NULL,
PRIMARY KEY(LI_ident, EO_ident, EX_ident),
UNIQUE(EX_codbar),
FOREIGN KEY(LI_ident, EO_ident) REFERENCES EO_edition(LI_ident, EO_ident)
);
CREATE TABLE IR_intervenir(
LI_ident INT,
IN_ident INT,
RL_ident INT,
PRIMARY KEY(LI_ident, IN_ident, RL_ident),
FOREIGN KEY(LI_ident) REFERENCES LI_livre(LI_ident),
FOREIGN KEY(IN_ident) REFERENCES IN_intervenant(IN_ident),
FOREIGN KEY(RL_ident) REFERENCES RL_role(RL_ident)
); |