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 55 56 57 58 59 60 61 62
| CREATE TABLE CL_client(
CL_ident INT IDENTITY,
CL_numero CHAR(6) NOT NULL,
CL_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(CL_ident),
UNIQUE(CL_numero)
);
CREATE TABLE CT_contact(
CL_ident INT,
CT_seq SMALLINT,
CT_nom VARCHAR(50) NOT NULL,
PRIMARY KEY(CL_ident, CT_seq),
FOREIGN KEY(CL_ident) REFERENCES CL_client(CL_ident)
);
CREATE TABLE MD_media(
MD_ident INT IDENTITY,
MD_code CHAR(4) NOT NULL,
MD_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(MD_ident),
UNIQUE(MD_code)
);
CREATE TABLE DE_demande(
CL_ident INT,
DE_ident SMALLINT,
DE_num_cde_client CHAR(10) NOT NULL,
MD_ident INT NOT NULL,
CL_ident_contact INT,
CT_seq_contact SMALLINT,
PRIMARY KEY(CL_ident, DE_ident),
FOREIGN KEY(CL_ident) REFERENCES CL_client(CL_ident),
FOREIGN KEY(MD_ident) REFERENCES MD_media(MD_ident),
FOREIGN KEY(CL_ident_contact, CT_seq_contact) REFERENCES CT_contact(CL_ident, CT_seq)
);
CREATE TABLE OD_ods(
CL_ident INT,
DE_ident SMALLINT,
OD_seq SMALLINT,
OD_descriptif VARCHAR(50) NOT NULL,
OD_etat CHAR(2) NOT NULL,
PRIMARY KEY(CL_ident, DE_ident, OD_seq),
FOREIGN KEY(CL_ident, DE_ident) REFERENCES DE_demande(CL_ident, DE_ident)
);
CREATE TABLE IT_intervention(
CL_ident INT,
DE_ident SMALLINT,
OD_seq SMALLINT,
IT_seq SMALLINT,
IT_dtdeb_prevue DATE NOT NULL,
IT_dtdeb_reelle DATE,
PRIMARY KEY(CL_ident, DE_ident, OD_seq, IT_seq),
FOREIGN KEY(CL_ident, DE_ident, OD_seq) REFERENCES OD_ods(CL_ident, DE_ident, OD_seq)
);
ALTER TABLE DE_demande
add constraint DE_CHK_001
check (CL_ident_contact = CL_ident or CL_ident_contact is null)
; |
Partager