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 63 64 65 66 67 68 69 70 71
| CREATE TABLE GEN_genre(
GEN_ident INT IDENTITY,
GEN_code CHAR(4) NOT NULL,
GEN_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(GEN_ident),
UNIQUE(GEN_code)
);
CREATE TABLE PRS_personne(
PRS_ident INT IDENTITY,
PRS_nom VARCHAR(50) NOT NULL,
PRS_prenom VARCHAR(50) NOT NULL,
PRIMARY KEY(PRS_ident)
);
CREATE TABLE CLI_client(
PRS_ident INT,
PRIMARY KEY(PRS_ident),
FOREIGN KEY(PRS_ident) REFERENCES PRS_personne(PRS_ident)
);
CREATE TABLE EMP_employe(
PRS_ident INT,
EMP_matricule CHAR(5) NOT NULL,
EMP_dtemb DATE,
PRIMARY KEY(PRS_ident),
UNIQUE(EMP_matricule),
FOREIGN KEY(PRS_ident) REFERENCES PRS_personne(PRS_ident)
);
CREATE TABLE CAR_caracteristique(
CAR_ident INT IDENTITY,
CAR_code CHAR(6) NOT NULL,
CAR_libelle VARCHAR(50) NOT NULL,
PRIMARY KEY(CAR_ident),
UNIQUE(CAR_code)
);
CREATE TABLE ANI_animal(
GEN_ident INT,
ANI_seq SMALLINT,
ANI_nom VARCHAR(50) NOT NULL,
ANI_ddn DATE NOT NULL,
PRS_ident INT NOT NULL,
PRIMARY KEY(GEN_ident, ANI_seq),
FOREIGN KEY(GEN_ident) REFERENCES GEN_genre(GEN_ident),
FOREIGN KEY(PRS_ident) REFERENCES CLI_client(PRS_ident)
);
CREATE TABLE CGN_carac_genre(
GEN_ident INT,
CAR_ident INT,
PRIMARY KEY(GEN_ident, CAR_ident),
FOREIGN KEY(GEN_ident) REFERENCES GEN_genre(GEN_ident),
FOREIGN KEY(CAR_ident) REFERENCES CAR_caracteristique(CAR_ident)
);
CREATE TABLE CAN_carac_ani(
GEN_ident INT,
ANI_seq SMALLINT,
CAL_date DATE,
CAR_ident INT NOT NULL,
PRIMARY KEY(GEN_ident, ANI_seq, CAL_date),
FOREIGN KEY(GEN_ident, ANI_seq) REFERENCES ANI_animal(GEN_ident, ANI_seq),
FOREIGN KEY(CAR_ident) REFERENCES CAR_caracteristique(CAR_ident)
);
ALTER TABLE CAN_carac_ani
ADD CONSTRAINT CAN_FK_001
FOREIGN KEY(GEN_ident, CAR_ident)
REFERENCES CGN_carac_genre(GEN_ident, CAR_ident); |
Partager