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 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148
| CREATE TABLE GROUPE_SOCIETE(
groupe_societe_Id INT,
PRIMARY KEY(groupe_societe_Id)
);
CREATE TABLE TYPE_PIECE(
type_piece_Id INT,
liste_type_piece VARCHAR(50) NOT NULL,
PRIMARY KEY(type_piece_Id),
UNIQUE(liste_type_piece)
);
CREATE TABLE CONTRAINTE_PIECE(
contrainte_piece_Id INT,
liste_con_piece VARCHAR(50) NOT NULL,
PRIMARY KEY(contrainte_piece_Id),
UNIQUE(liste_con_piece)
);
CREATE TABLE CONTRAINTE_BATIMENT(
contrainte_batiment_Id INT,
liste_con_bat VARCHAR(50) NOT NULL,
PRIMARY KEY(contrainte_batiment_Id),
UNIQUE(liste_con_bat)
);
CREATE TABLE TYPE_SITE(
type_site_Id INT,
liste_type_site VARCHAR(50) NOT NULL,
PRIMARY KEY(type_site_Id),
UNIQUE(liste_type_site)
);
CREATE TABLE CONTRAINTE_SITE(
contrainte_site_Id INT,
liste_contrainte_site VARCHAR(50) NOT NULL,
PRIMARY KEY(contrainte_site_Id),
UNIQUE(liste_contrainte_site)
);
CREATE TABLE ADRESSE(
adresse_Id INT,
pays VARCHAR(50) NOT NULL,
ville VARCHAR(50) NOT NULL,
code_postal INT NOT NULL,
adresse VARCHAR(50),
PRIMARY KEY(adresse_Id)
);
CREATE TABLE SITE(
site_Id INT,
nom_site VARCHAR(50) NOT NULL,
desc_site TEXT,
adresse_Id INT NOT NULL,
PRIMARY KEY(site_Id),
UNIQUE(nom_site),
FOREIGN KEY(adresse_Id) REFERENCES ADRESSE(adresse_Id)
);
CREATE TABLE BATIMENT(
batiment_id INT,
description_batiment TEXT,
adresse_Id INT NOT NULL,
site_Id INT,
PRIMARY KEY(batiment_id),
FOREIGN KEY(adresse_Id) REFERENCES ADRESSE(adresse_Id),
FOREIGN KEY(site_Id) REFERENCES SITE(site_Id)
);
CREATE TABLE ETAGE(
batiment_id INT,
etage_Id INT,
numero INT NOT NULL,
PRIMARY KEY(batiment_id, etage_Id),
FOREIGN KEY(batiment_id) REFERENCES BATIMENT(batiment_id)
);
CREATE TABLE PIECE(
batiment_id INT,
etage_Id INT,
piece_Id INT,
PRIMARY KEY(batiment_id, etage_Id, piece_Id),
FOREIGN KEY(batiment_id, etage_Id) REFERENCES ETAGE(batiment_id, etage_Id)
);
CREATE TABLE BATIMENT_SOCIETE(
batiment_id INT,
groupe_societe_Id INT,
PRIMARY KEY(batiment_id, groupe_societe_Id),
FOREIGN KEY(batiment_id) REFERENCES BATIMENT(batiment_id),
FOREIGN KEY(groupe_societe_Id) REFERENCES GROUPE_SOCIETE(groupe_societe_Id)
);
CREATE TABLE SITE_SOC(
site_Id INT,
groupe_societe_Id INT,
PRIMARY KEY(site_Id, groupe_societe_Id),
FOREIGN KEY(site_Id) REFERENCES SITE(site_Id),
FOREIGN KEY(groupe_societe_Id) REFERENCES GROUPE_SOCIETE(groupe_societe_Id)
);
CREATE TABLE PIECE_TYPE(
batiment_id INT,
etage_Id INT,
piece_Id INT,
type_piece_Id INT,
DESC_PIECE TEXT,
PRIMARY KEY(batiment_id, etage_Id, piece_Id, type_piece_Id),
FOREIGN KEY(batiment_id, etage_Id, piece_Id) REFERENCES PIECE(batiment_id, etage_Id, piece_Id),
FOREIGN KEY(type_piece_Id) REFERENCES TYPE_PIECE(type_piece_Id)
);
CREATE TABLE PIECE_CONTRAINTE(
batiment_id INT,
etage_Id INT,
piece_Id INT,
contrainte_piece_Id INT,
description_contrainte_piece TEXT,
PRIMARY KEY(batiment_id, etage_Id, piece_Id, contrainte_piece_Id),
FOREIGN KEY(batiment_id, etage_Id, piece_Id) REFERENCES PIECE(batiment_id, etage_Id, piece_Id),
FOREIGN KEY(contrainte_piece_Id) REFERENCES CONTRAINTE_PIECE(contrainte_piece_Id)
);
CREATE TABLE BAT_CON(
batiment_id INT,
contrainte_batiment_Id INT,
DESC_CON_BAT TEXT,
PRIMARY KEY(batiment_id, contrainte_batiment_Id),
FOREIGN KEY(batiment_id) REFERENCES BATIMENT(batiment_id),
FOREIGN KEY(contrainte_batiment_Id) REFERENCES CONTRAINTE_BATIMENT(contrainte_batiment_Id)
);
CREATE TABLE SITE_TYPE(
site_Id INT,
type_site_Id INT,
PRIMARY KEY(site_Id, type_site_Id),
FOREIGN KEY(site_Id) REFERENCES SITE(site_Id),
FOREIGN KEY(type_site_Id) REFERENCES TYPE_SITE(type_site_Id)
);
CREATE TABLE SITE_CONTRAINTE(
site_Id INT,
contrainte_site_Id INT,
desc_con_site TEXT,
PRIMARY KEY(site_Id, contrainte_site_Id),
FOREIGN KEY(site_Id) REFERENCES SITE(site_Id),
FOREIGN KEY(contrainte_site_Id) REFERENCES CONTRAINTE_SITE(contrainte_site_Id)
); |
Partager