-- Time-stamp: <2012-07-11 19:03:07 novatux>
-- Création : 2012-07-09T10:27:29+0200

-- création de la base « france »

-- utiliser la ligne de commande
--  	    mysql --local-infile -u yvon -p

-- tout ce qui risque d’être un fichier à un nom en bas de casse

CREATE DATABASE france;

USE france;

CREATE TABLE zeat (
       ZEAT_Id ENUM('1','2','3','4','5','6','7','8','9','0') NOT NULL,
       ZEAT_Id_NUTS ENUM('FR1','FR2','FR3','FR4','FR5','FR6','FR7','FR8','FR9','FRZ') NOT NULL,
       Nom VARCHAR(47) NOT NULL,
       CONSTRAINT code_insee PRIMARY KEY (ZEAT_Id),
       CONSTRAINT code_nuts UNIQUE (ZEAT_Id_NUTS)
)ENGINE InnoDB;

LOAD DATA LOCAL INFILE 'zeat.data' REPLACE INTO TABLE zeat FIELDS TERMINATED BY ';' IGNORE 1 LINES;

CREATE TABLE regions (
       RegId CHAR(2),
       ChefLieu CHAR(5),
       TNCC ENUM('0','1','2','3','4','5','6','7','8'),
       NomSimp VARCHAR(43),
       Nom VARCHAR(43),
       ZEAT_Id ENUM('1','2','3','4','5','6','7','8','9','0'),       
       Statut VARCHAR(25),
       Abrev VARCHAR(5),
       CONSTRAINT cle_region PRIMARY KEY (RegId),
       CONSTRAINT ref_zeat FOREIGN KEY (ZEAT_Id) REFERENCES zeat(ZEAT_Id)
)ENGINE InnoDB;

LOAD DATA LOCAL INFILE 'regions.data' REPLACE INTO TABLE regions FIELDS TERMINATED BY ';' IGNORE 1 LINES;

CREATE TABLE departements (
       RegId CHAR(2),
       DepId VARCHAR(3),
       ChefLieu CHAR(5),
       TNCC ENUM('0','1','2','3','4','5','6','7','8'),
       NomSimp VARCHAR(23),
       Nom VARCHAR(23),
       CONSTRAINT cle_departement PRIMARY KEY (DepId),
       CONSTRAINT ref_region FOREIGN KEY (RegId) REFERENCES regions(RegId)
)ENGINE InnoDB;

LOAD DATA LOCAL INFILE 'departements.data' REPLACE INTO TABLE departements FIELDS TERMINATED BY ';' IGNORE 1 LINES;

CREATE TABLE arrondissements (
       DepId VARCHAR(3),
       ArrondId TINYINT,
       ChefLieu CHAR(5),
       TNCC ENUM('0','1','2','3','4','5'),
       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)
)ENGINE InnoDB;

LOAD DATA LOCAL INFILE 'arrondissements.data' REPLACE INTO TABLE arrondissements FIELDS TERMINATED BY ';' IGNORE 1 LINES;

CREATE TABLE cantons (
       DepId VARCHAR(3),
       ArrondId TINYINT DEFAULT 0,
       CantonId TINYINT,
       Type ENUM('0','1','2','3','4','5'),
       ChefLieu CHAR(5),
       TNCC ENUM('0','1','2','3','4','5'),
       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)
)ENGINE InnoDB;

LOAD DATA LOCAL INFILE 'cantons.data' REPLACE INTO TABLE cantons FIELDS TERMINATED BY ';' IGNORE 1 LINES;

CREATE TABLE communes (
       CDC ENUM('0','1','2'),
       DepId VARCHAR(3),
       ComId CHAR(5),
       CantonId TINYINT,
       TNCC ENUM('0','1','2','3','4','5','6','7','8'),
       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)
)ENGINE InnoDB;

LOAD DATA LOCAL INFILE 'communes.data' REPLACE INTO TABLE communes FIELDS TERMINATED BY ';' IGNORE 1 LINES;

-- tables d’adjacence

create table regadj (
       Reg1 CHAR(2) not null,
       Reg2 CHAR(2) not null,
       Dir ENUM('0','1','2','3','4','5','6','7'),
       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)
)ENGINE InnoDB;

LOAD DATA LOCAL INFILE 'regadj.data' REPLACE INTO TABLE regadj FIELDS TERMINATED BY ';' IGNORE 1 LINES;



-- dictionnaire des variables

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)
);

-- Local Variables: 
-- mode: SQL[mysql]
-- coding: utf-8-unix
-- End: 

