projet-bdd-dvf/split_into_tables.mysql

-- ----------------- --
-- TABLE Departement --
-- ----------------- --
CREATE TABLE IF NOT EXISTS Departement (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Departement_pk PRIMARY KEY (id),
    Code_departement VARCHAR(20)
);
CREATE INDEX Code_departement_idx ON Departement (Code_departement);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Departement (Code_departement) SELECT DISTINCT Code_departement FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Departement à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Departement_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Departement ON MACRO_DRAFT.Code_departement=Departement.Code_departement
SET MACRO_DRAFT.Departement_id=Departement.id;

-- ------------- --
-- TABLE Commune --
-- ------------- --
CREATE TABLE IF NOT EXISTS Commune (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Commune_pk PRIMARY KEY (id),
    Commune VARCHAR(50),
    Code_commune VARCHAR(10),
    Departement_id INT NOT NULL,
    CONSTRAINT Commune_Departement_fk FOREIGN KEY (Departement_id) REFERENCES Departement(id),
    Temp_concat VARCHAR(60) # Pour créer toutes les valeurs possibles
);
CREATE INDEX Commune_idx ON Commune (Commune);
CREATE INDEX Temp_idx ON Commune (Temp_concat);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Commune (Commune, Code_commune, Departement_id, Temp_concat)
SELECT DISTINCT Commune, Code_commune, Departement_id, CONCAT(Commune, Code_commune)
FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Commune à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Commune_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Commune
ON CONCAT(MACRO_DRAFT.Commune, MACRO_DRAFT.Code_commune) = Commune.Temp_concat
SET MACRO_DRAFT.Commune_id = Commune.id;
# Suppression de la colonne temporaire
ALTER TABLE Commune DROP INDEX Temp_idx, DROP COLUMN Temp_concat;

-- ------------------ --
-- TABLE Type_de_voie --
-- ------------------ --
CREATE TABLE IF NOT EXISTS Type_de_voie (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Type_de_voie_pk PRIMARY KEY (id),
    Type_de_voie VARCHAR(50) NOT NULL
);
CREATE INDEX Type_de_voie_idx ON Type_de_voie (Type_de_voie);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Type_de_voie (Type_de_voie) SELECT DISTINCT Type_de_voie FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Type_de_voie à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Type_de_voie_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Type_de_voie ON MACRO_DRAFT.Type_de_voie=Type_de_voie.Type_de_voie
SET MACRO_DRAFT.Type_de_voie_id=Type_de_voie.id;

-- ---------- --
-- TABLE Voie --
-- ---------- --
CREATE TABLE IF NOT EXISTS Voie (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Voie_pk PRIMARY KEY (id),
    Voie VARCHAR(100),
    Code_voie VARCHAR(10),
    Type_de_voie_id INT NOT NULL,
    CONSTRAINT Voie_Type_de_Voie_fk FOREIGN KEY (Type_de_voie_id) REFERENCES Type_de_voie(id),
    Commune_id INT NOT NULL,
    CONSTRAINT Voie_Commune_fk FOREIGN KEY (Commune_id) REFERENCES Commune(id),
    Temp_concat VARCHAR(110) # Pour créer toutes les valeurs possibles
);
CREATE INDEX Voie_idx ON Voie (Voie);
CREATE INDEX Temp_idx ON Voie (Temp_concat);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Voie (Voie, Code_voie, Type_de_voie_id, Commune_id, Temp_concat)
SELECT DISTINCT Voie, Code_voie, Type_de_voie_id, Commune_id, CONCAT(Voie, Code_voie) FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Voie à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Voie_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Voie
ON CONCAT(MACRO_DRAFT.Voie, MACRO_DRAFT.Code_voie) = Voie.Temp_concat
SET MACRO_DRAFT.Voie_id = Voie.id;
# Suppression de la colonne temporaire
ALTER TABLE Voie DROP INDEX Temp_idx, DROP COLUMN Temp_concat;

-- ------------- --
-- TABLE Adresse --
-- ------------- --
CREATE TABLE IF NOT EXISTS Adresse (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Adresse_pk PRIMARY KEY (id),
    No_voie VARCHAR(10),
    B_T_Q VARCHAR(2),
    Voie_id INT NOT NULL, CONSTRAINT Adresse_Voie_fk FOREIGN KEY (Voie_id) REFERENCES Voie(id),
    Temp_concat VARCHAR(12) # Pour créer toutes les valeurs possibles
);
CREATE INDEX Temp_idx ON Adresse (Temp_concat);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Adresse (No_voie, B_T_Q, Voie_id, Temp_concat)
SELECT DISTINCT No_voie, B_T_Q, Voie_id, CONCAT(No_voie, B_T_Q)
FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Adresse à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Adresse_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Adresse
ON CONCAT(MACRO_DRAFT.No_voie, MACRO_DRAFT.B_T_Q) = Adresse.Temp_concat
SET MACRO_DRAFT.Adresse_id = Adresse.id;
# Suppression de la colonne temporaire
ALTER TABLE Adresse DROP INDEX Temp_idx, DROP COLUMN Temp_concat;

-- --------------------- --
-- TABLE Nature_mutation --
-- --------------------- --
CREATE TABLE IF NOT EXISTS Nature_mutation (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Nature_mutation_pk PRIMARY KEY (id),
    Nature_mutation VARCHAR(50) NOT NULL
);
CREATE INDEX Nature_mutation_idx ON Nature_mutation (Nature_mutation);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Nature_mutation (Nature_mutation) SELECT DISTINCT Nature_mutation FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Nature_mutation à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Nature_mutation_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Nature_mutation ON MACRO_DRAFT.Nature_mutation = Nature_mutation.Nature_mutation
SET MACRO_DRAFT.Nature_mutation_id = Nature_mutation.id;

-- ---------------- --
-- TABLE Type_local --
-- ---------------- --
CREATE TABLE IF NOT EXISTS Type_local (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Type_local_pk PRIMARY KEY (id),
    Type_local VARCHAR(50) NOT NULL,
    Code_type_local VARCHAR(1) NOT NULL
);
CREATE INDEX Type_local_idx ON Type_local (Type_local, Code_type_local);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Type_local (Type_local, Code_type_local) SELECT DISTINCT Type_local, Code_type_local FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Type_local à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Type_local_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Type_local ON MACRO_DRAFT.Type_local = Type_local.Type_local
SET MACRO_DRAFT.Type_local_id = Type_local.id;

-- ------------ --
-- TABLE Nature --
-- ------------ --
CREATE TABLE IF NOT EXISTS Nature (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Nature_pk PRIMARY KEY (id),
    Nature_culture VARCHAR(20),
    Nature_culture_speciale VARCHAR(20),
    Temp_concat VARCHAR(40) # Pour créer toutes les valeurs possibles
);
CREATE INDEX Temp_idx ON Nature (Temp_concat);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Nature (Nature_culture, Nature_culture_speciale, Temp_concat)
SELECT DISTINCT Nature_culture, Nature_culture_speciale, CONCAT(Nature_culture, Nature_culture_speciale) FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Nature à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Nature_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Nature
ON CONCAT(MACRO_DRAFT.Nature_culture, MACRO_DRAFT.Nature_culture_speciale) = Nature.Temp_concat
SET MACRO_DRAFT.Nature_id = Nature.id;
# Suppression de la colonne temporaire
ALTER TABLE Nature DROP INDEX Temp_idx, DROP COLUMN Temp_concat;

-- ----------------- --
-- TABLE Code_postal --
-- ----------------- --
CREATE TABLE IF NOT EXISTS Code_postal (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Code_postal_pk PRIMARY KEY (id),
    Code_postal VARCHAR(5) NOT NULL,
    Commune_id INT NOT NULL,
    CONSTRAINT Code_postal_Commune_fk FOREIGN KEY (Commune_id) REFERENCES Commune(id)
);
CREATE INDEX Code_postal_idx ON Code_postal (Code_postal);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Code_postal (Code_postal, Commune_id)
SELECT DISTINCT Code_postal, Commune_id FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Code_postal à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Code_postal_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Code_postal ON MACRO_DRAFT.Code_postal = Code_postal.Code_postal
SET MACRO_DRAFT.Code_postal_id = Code_postal.id;

-- ---------- --
-- TABLE Bien --
-- ---------- --
CREATE TABLE IF NOT EXISTS Bien (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Bien_pk PRIMARY KEY (id),
    Surface_reelle_bati INT NOT NULL,
    Nombre_pieces_principales INT NOT NULL,
    Surface_terrain INT NOT NULL,
    Adresse_id INT NOT NULL, CONSTRAINT Bien_Adresse_fk FOREIGN KEY (Adresse_id) REFERENCES Adresse(id),
    Temp_concat VARCHAR(255) # Pour créer toutes les valeurs possibles
);
CREATE INDEX Temp_idx ON Bien (Temp_concat);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Bien (Surface_reelle_bati, Nombre_pieces_principales, Surface_terrain, Adresse_id, Temp_concat)
SELECT DISTINCT Surface_reelle_bati, Nombre_pieces_principales, Surface_terrain, Adresse_id, CONCAT(Surface_reelle_bati, Nombre_pieces_principales, Surface_terrain)
FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Bien à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Bien_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Bien
ON CONCAT(MACRO_DRAFT.Surface_reelle_bati, MACRO_DRAFT.Nombre_pieces_principales, MACRO_DRAFT.Surface_terrain) = Bien.Temp_concat
SET MACRO_DRAFT.Bien_id = Bien.id;
# Suppression de la colonne temporaire
ALTER TABLE Bien DROP INDEX Temp_idx, DROP COLUMN Temp_concat;

-- -------------- --
-- TABLE Mutation --
-- -------------- --
CREATE TABLE IF NOT EXISTS Mutation (
    id INT NOT NULL AUTO_INCREMENT, CONSTRAINT Mutation_pk PRIMARY KEY (id),
    Date_mutation DATE NOT NULL,
    Valeur_fonciere DOUBLE PRECISION (20, 2) NOT NULL,
    Nature_mutation_id INT NOT NULL,
    CONSTRAINT Mutation_Nature_mutation_fk FOREIGN KEY (Nature_mutation_id) REFERENCES Nature_mutation(id),
    Bien_id INT NOT NULL,
    CONSTRAINT Mutation_Bien_fk FOREIGN KEY (Bien_id) REFERENCES Bien(id),
    Temp_concat VARCHAR(30) # Pour créer toutes les valeurs possibles
);
CREATE INDEX Temp_idx ON Mutation (Temp_concat);
# Peuplement de la table depuis MACRO_DRAFT
INSERT INTO Mutation (Date_mutation, Valeur_fonciere, Nature_mutation_id, Bien_id, Temp_concat)
SELECT DISTINCT Date_mutation, Valeur_fonciere, Nature_mutation_id, Bien_id, CONCAT(Date_mutation, Valeur_fonciere) FROM MACRO_DRAFT ORDER BY id;
# Ajout d'une colonne id pour Mutation à la table MACRO_DRAFT
ALTER TABLE MACRO_DRAFT ADD Mutation_id INT NOT NULL;
# Ajout des id calculés par le peuplement à la table MACRO_DRAFT
UPDATE MACRO_DRAFT INNER JOIN Mutation
ON CONCAT(MACRO_DRAFT.Date_mutation, MACRO_DRAFT.Valeur_fonciere) = Mutation.Temp_concat
SET MACRO_DRAFT.Mutation_id = Mutation.id;
# Suppression de la colonne temporaire
ALTER TABLE Mutation DROP INDEX Temp_idx, DROP COLUMN Temp_concat;

-- ------------------- --
-- MANY TO MANY TABLES --
-- ------------------- --
CREATE TABLE Bien_Nature (
    Bien_id INT NOT NULL,
    Nature_id INT NOT NULL,
    CONSTRAINT Bien_Nature_pk PRIMARY KEY (Bien_id, Nature_id),
    CONSTRAINT Bien_Nature_fk FOREIGN KEY (Nature_id) REFERENCES Nature(id),
    CONSTRAINT Nature_Bien_fk FOREIGN KEY (Bien_id) REFERENCES Bien(id)
);
INSERT INTO Bien_Nature (Bien_id, Nature_id)
SELECT DISTINCT Bien_id, Nature_id
FROM MACRO_DRAFT ORDER BY id;

--

CREATE TABLE Bien_Type_local (
    Type_local_id INT NOT NULL ,
    Bien_id INT NOT NULL,
    CONSTRAINT Bien_Type_local_pk PRIMARY KEY (Type_local_id, Bien_id),
    CONSTRAINT Bien_Type_local_fk FOREIGN KEY (Type_local_id) REFERENCES Type_local(id),
    CONSTRAINT Type_local_Bien_fk FOREIGN KEY (Bien_id) REFERENCES Bien(id)
);
INSERT INTO Bien_Type_local (Type_local_id, Bien_id)
SELECT DISTINCT Type_local_id, Bien_id
FROM MACRO_DRAFT ORDER BY id;

DROP TABLE MACRO_DRAFT;

-- vim: foldmethod=indent