/* 
Time-stamp: <2012-09-11 19:08:29 novatux>
Création : 2012-09-11T19:01:35+0200
Version pour postgreSQL
*/

CREATE TABLE zeat (
       ZEAT_Id INTEGER NOT NULL,
       ZEAT_Id_NUTS VARCHAR(3) NOT NULL,
       Nom VARCHAR(47) NOT NULL,
       CONSTRAINT code_insee PRIMARY KEY (ZEAT_Id),
       CONSTRAINT code_nuts UNIQUE (ZEAT_Id_NUTS)
);

CREATE TABLE regions (
       RegId CHAR(2),
       ChefLieu CHAR(5),
       TNCC INTEGER,
       NomSimp VARCHAR(43),
       Nom VARCHAR(43),
       ZEAT_Id INTEGER,
       Statut VARCHAR(25),
       Abrev VARCHAR(5),
       CONSTRAINT cle_region PRIMARY KEY (RegId),
       CONSTRAINT ref_zeat FOREIGN KEY (ZEAT_Id) REFERENCES zeat(ZEAT_Id)
);

CREATE TABLE departements (
       RegId CHAR(2),
       DepId VARCHAR(3),
       ChefLieu CHAR(5),
       TNCC INTEGER,
       NomSimp VARCHAR(23),
       Nom VARCHAR(23),
       CONSTRAINT cle_departement PRIMARY KEY (DepId),
       CONSTRAINT ref_region FOREIGN KEY (RegId) REFERENCES regions(RegId)
);

CREATE TABLE arrondissements (
       DepId VARCHAR(3),
       ArrondId INTEGER,
       ChefLieu CHAR(5),
       TNCC INTEGER,
       ArtMaj VARCHAR(3),
       NomSimp VARCHAR(24),
       ArtMin VARCHAR(3),
       Nom VARCHAR(24),
       CONSTRAINT cle_arrondissement PRIMARY KEY (DepId, ArrondId),
       CONSTRAINT ref_arrdep FOREIGN KEY (DepId) REFERENCES departements(DepId)
);

CREATE TABLE cantons (
       DepId VARCHAR(3),
       ArrondId INTEGER DEFAULT 0,
       CantonId INTEGER,
       Type INTEGER,
       ChefLieu CHAR(5),
       TNCC INTEGER,
       ArtMaj VARCHAR(3),
       NomSimp VARCHAR(45),
       ArtMin VARCHAR(3),
       Nom VARCHAR(45),
       CONSTRAINT cle_canton PRIMARY KEY (DepId, CantonId),
       CONSTRAINT ref_candep FOREIGN KEY (DepId) REFERENCES departements(DepId)
);

CREATE TABLE communes (
       CDC INTEGER,
       DepId VARCHAR(3),
       ComId CHAR(5),
       CantonId INTEGER,
       TNCC INTEGER,
       ArtMaj VARCHAR(3),
       NomSimp VARCHAR(45),
       ArtMin VARCHAR(3),
       Nom VARCHAR(45),
       CONSTRAINT cle_commune PRIMARY KEY (ComId),
       CONSTRAINT ref_comdep FOREIGN KEY (DepId) REFERENCES departements(DepId)
);

create table regadj (
       Reg1 CHAR(2) not null,
       Reg2 CHAR(2) not null,
       Dir INTEGER,
       CONSTRAINT cle_regadj PRIMARY KEY (Reg1,Reg2),
       CONSTRAINT ref_reg1 FOREIGN KEY (Reg1) REFERENCES regions(RegId),
       CONSTRAINT ref_reg2 FOREIGN KEY (Reg2) REFERENCES regions(RegId)
);

CREATE TABLE population (
       CODGEO CHAR(5),
       P09_POP INTEGER,
       P99_POP INTEGER,
       D90_POP INTEGER,
       D82_POP INTEGER,
       D75_POP INTEGER,
       D68_POP INTEGER,
       SUPERF REAL,
       NAIS9909 INTEGER,
       NAIS9099 INTEGER,
       NAIS8290 INTEGER,
       NAIS7582 INTEGER,
       NAIS6875 INTEGER,
       DECE9909 INTEGER,
       DECE9099 INTEGER,
       DECE8290 INTEGER,
       DECE7582 INTEGER,
       DECE6875 INTEGER,
       NAIS10 INTEGER,
       NAIS09 INTEGER,
       NAIS08 INTEGER,
       NAIS07 INTEGER,
       NAIS06 INTEGER,
       NAIS05 INTEGER,
       NAIS04 INTEGER,
       NAIS03 INTEGER,
       NAIS02 INTEGER,
       NAIS01 INTEGER,
       NAIS00 INTEGER,
       NAIS99 INTEGER,
       DECE10 INTEGER,
       DECE09 INTEGER,
       DECE08 INTEGER,
       DECE07 INTEGER,
       DECE06 INTEGER,
       DECE05 INTEGER,
       DECE04 INTEGER,
       DECE03 INTEGER,
       DECE02 INTEGER,
       DECE01 INTEGER,
       DECE00 INTEGER,
       DECE99 INTEGER,
       P09_POP0014 INTEGER,
       P09_POP1529 INTEGER,
       P09_POP3044 INTEGER,
       P09_POP4559 INTEGER,
       P09_POP6074 INTEGER,
       P09_POP75P INTEGER,
       P99_POP0014 INTEGER,
       P99_POP1529 INTEGER,
       P99_POP3044 INTEGER,
       P99_POP4559 INTEGER,
       P99_POP6074 INTEGER,
       P99_POP75P INTEGER,
       P09_POPH INTEGER,
       P09_H0014 INTEGER,
       P09_H1529 INTEGER,
       P09_H3044 INTEGER,
       P09_H4559 INTEGER,
       P09_H6074 INTEGER,
       P09_H7589 INTEGER,
       P09_H90P INTEGER,
       P09_H0019 INTEGER,
       P09_H2064 INTEGER,
       P09_H65P INTEGER,
       P09_POPF INTEGER,
       P09_F0014 INTEGER,
       P09_F1529 INTEGER,
       P09_F3044 INTEGER,
       P09_F4559 INTEGER,
       P09_F6074 INTEGER,
       P09_F7589 INTEGER,
       P09_F90P INTEGER,
       P09_F0019 INTEGER,
       P09_F2064 INTEGER,
       P09_F65P INTEGER,
       C09_POP15P INTEGER,
       C09_POP15P_CS1 INTEGER,
       C09_POP15P_CS2 INTEGER,
       C09_POP15P_CS3 INTEGER,
       C09_POP15P_CS4 INTEGER,
       C09_POP15P_CS5 INTEGER,
       C09_POP15P_CS6 INTEGER,
       C09_POP15P_CS7 INTEGER,
       C09_POP15P_CS8 INTEGER,
       C99_POP15P INTEGER,
       C99_POP15P_CS1 INTEGER,
       C99_POP15P_CS2 INTEGER,
       C99_POP15P_CS3 INTEGER,
       C99_POP15P_CS4 INTEGER,
       C99_POP15P_CS5 INTEGER,
       C99_POP15P_CS6 INTEGER,
       C99_POP15P_CS7 INTEGER,
       C99_POP15P_CS8 INTEGER,
       C09_H15P INTEGER,
       C09_H15P_CS1 INTEGER,
       C09_H15P_CS2 INTEGER,
       C09_H15P_CS3 INTEGER,
       C09_H15P_CS4 INTEGER,
       C09_H15P_CS5 INTEGER,
       C09_H15P_CS6 INTEGER,
       C09_H15P_CS7 INTEGER,
       C09_H15P_CS8 INTEGER,
       C09_F15P INTEGER,
       C09_F15P_CS1 INTEGER,
       C09_F15P_CS2 INTEGER,
       C09_F15P_CS3 INTEGER,
       C09_F15P_CS4 INTEGER,
       C09_F15P_CS5 INTEGER,
       C09_F15P_CS6 INTEGER,
       C09_F15P_CS7 INTEGER,
       C09_F15P_CS8 INTEGER,
       C09_POP1524 INTEGER,
       C09_POP1524_CS1 INTEGER,
       C09_POP1524_CS2 INTEGER,
       C09_POP1524_CS3 INTEGER,
       C09_POP1524_CS4 INTEGER,
       C09_POP1524_CS5 INTEGER,
       C09_POP1524_CS6 INTEGER,
       C09_POP1524_CS7 INTEGER,
       C09_POP1524_CS8 INTEGER,
       C09_POP2554 INTEGER,
       C09_POP2554_CS1 INTEGER,
       C09_POP2554_CS2 INTEGER,
       C09_POP2554_CS3 INTEGER,
       C09_POP2554_CS4 INTEGER,
       C09_POP2554_CS5 INTEGER,
       C09_POP2554_CS6 INTEGER,
       C09_POP2554_CS7 INTEGER,
       C09_POP2554_CS8 INTEGER,
       C09_POP55P INTEGER,
       C09_POP55P_CS1 INTEGER,
       C09_POP55P_CS2 INTEGER,
       C09_POP55P_CS3 INTEGER,
       C09_POP55P_CS4 INTEGER,
       C09_POP55P_CS5 INTEGER,
       C09_POP55P_CS6 INTEGER,
       C09_POP55P_CS7 INTEGER,
       C09_POP55P_CS8 INTEGER,
       CONSTRAINT cle_popu PRIMARY KEY (CODGEO)
);


CREATE TABLE dictionnaire (
       VAR_ID VARCHAR(20),
       VAR_LIB VARCHAR(60),
       VAR_LIB_LONG VARCHAR(100),
       ANNEE VARCHAR(9),
       SOURCE VARCHAR(40),
       CONSTRAINT cle_dico PRIMARY KEY (VAR_ID)
);
