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
| CREATE TABLE "filiere" (
"num_filiere" int NOT NULL,
"nom_filiere" varchar DEFAULT NULL,
PRIMARY KEY ("num_filiere")
);
CREATE TABLE "lieu" (
"num_lieu" int NOT NULL,
"id_lieu" varchar DEFAULT NULL,
"nom_lieu" varchar DEFAULT NULL,
PRIMARY KEY ("num_lieu")
);
CREATE TABLE "niveau_geo" (
"num_niceau_geo" int NOT NULL,
"nom_niveau_geo" varchar DEFAULT NULL,
PRIMARY KEY ("num_niceau_geo")
);
CREATE TABLE "regroupement" (
"num_regroupement" int NOT NULL,
"lib_court" varchar DEFAULT NULL,
"lib_long" varchar DEFAULT NULL,
PRIMARY KEY ("num_regroupement")
);
CREATE TABLE "secteur" (
"num_secteur" int NOT NULL,
"nom_secteru" varchar DEFAULT NULL,
PRIMARY KEY ("num_secteur")
);
CREATE TABLE "sexe" (
"num_sexe" int NOT NULL,
"lib_sexe" varchar DEFAULT NULL,
PRIMARY KEY ("num_sexe")
);
CREATE TABLE "proposer" (
"num_lieu" int NOT NULL,
"num_regroupement" int NOT NULL,
"num_secteur" int NOT NULL,
"num_sexe" int NOT NULL,
"num_niceau_geo" int NOT NULL,
PRIMARY KEY ("num_lieu","num_regroupement","num_secteur","num_sexe","num_niceau_geo"),
CONSTRAINT "FK_proposer_num_niceau_geo" FOREIGN KEY ("num_niceau_geo") REFERENCES "niveau_geo" ("num_niceau_geo"),
CONSTRAINT "FK_proposer_num_lieu" FOREIGN KEY ("num_lieu") REFERENCES "lieu" ("num_lieu"),
CONSTRAINT "FK_proposer_num_regroupement" FOREIGN KEY ("num_regroupement") REFERENCES "regroupement" ("num_regroupement"),
CONSTRAINT "FK_proposer_num_secteur" FOREIGN KEY ("num_secteur") REFERENCES "secteur" ("num_secteur"),
CONSTRAINT "FK_proposer_num_sexe" FOREIGN KEY ("num_sexe") REFERENCES "sexe" ("num_sexe")
);
CREATE TABLE "donnee_stat" (
"num_donnee_stat" int NOT NULL,
"annee" int DEFAULT NULL,
"diffusable" varchar DEFAULT NULL,
"secret" varchar DEFAULT NULL,
"reserve" varchar DEFAULT NULL,
"num_niceau_geo" int DEFAULT NULL,
"num_regroupement" int DEFAULT NULL,
"num_lieu" int DEFAULT NULL,
"num_secteur" int DEFAULT NULL,
PRIMARY KEY ("num_donnee_stat"),
CONSTRAINT "FK_DONNEE_STAT_num_secteur" FOREIGN KEY ("num_secteur") REFERENCES "secteur" ("num_secteur"),
CONSTRAINT "FK_DONNEE_STAT_num_lieu" FOREIGN KEY ("num_lieu") REFERENCES "lieu" ("num_lieu"),
CONSTRAINT "FK_DONNEE_STAT_num_niceau_geo" FOREIGN KEY ("num_niceau_geo") REFERENCES "niveau_geo" ("num_niceau_geo"),
CONSTRAINT "FK_DONNEE_STAT_num_regroupement" FOREIGN KEY ("num_regroupement") REFERENCES "regroupement" ("num_regroupement")
);
CREATE TABLE "comptabiliser_filiere" (
"effectif" int DEFAULT NULL,
"num_sexe" int NOT NULL,
"num_donnee_stat" int NOT NULL,
"num_filiere" int NOT NULL,
PRIMARY KEY ("num_sexe","num_donnee_stat","num_filiere"),
CONSTRAINT "FK_comptabiliser_filiere_num_filiere" FOREIGN KEY ("num_filiere") REFERENCES "filiere" ("num_filiere"),
CONSTRAINT "FK_comptabiliser_filiere_num_donnee_stat" FOREIGN KEY ("num_donnee_stat") REFERENCES "donnee_stat" ("num_donnee_stat"),
CONSTRAINT "FK_comptabiliser_filiere_num_sexe" FOREIGN KEY ("num_sexe") REFERENCES "sexe" ("num_sexe")
);
CREATE TABLE "comptabiliser" (
"effectif" int DEFAULT NULL,
"num_sexe" int NOT NULL,
"num_donnee_stat" int NOT NULL,
PRIMARY KEY ("num_sexe","num_donnee_stat"),
CONSTRAINT "FK_comptabiliser_num_donnee_stat" FOREIGN KEY ("num_donnee_stat") REFERENCES "donnee_stat" ("num_donnee_stat"),
CONSTRAINT "FK_comptabiliser_num_sexe" FOREIGN KEY ("num_sexe") REFERENCES "sexe" ("num_sexe")
); |
Partager