| New file |
|---|
| 0,0 → 1,177 |
|---|
| CREATE TABLE ods_triples ( |
| ot_id_triple INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, |
| ot_cle VARCHAR(255) NULL , |
| ot_ce_parent INTEGER UNSIGNED NULL , |
| ot_valeur VARCHAR(255) NULL , |
| ot_description TEXT NULL , |
| PRIMARY KEY(ot_id_triple), |
| FOREIGN KEY(ot_ce_parent) |
| REFERENCES ods_triples(ot_id_triple) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION); |
| |
| |
| |
| |
| |
| |
| |
| CREATE INDEX IFK_fk_ot_id_triple_ot_ce_pare ON ods_triples (ot_ce_parent); |
| |
| |
| CREATE TABLE ods_communes ( |
| oc_id_commune INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, |
| oc_code_insee INTEGER UNSIGNED NULL , |
| oc_nom TEXT NULL , |
| oc_secteur VARCHAR(50) NULL , |
| oc_latitude FLOAT NULL , |
| oc_longitude FLOAT NULL , |
| PRIMARY KEY(oc_id_commune)); |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| CREATE TABLE participants ( |
| id_participant INTEGER UNSIGNED NOT NULL AUTO_INCREMENT , |
| PRIMARY KEY(id_participant)); |
| |
| |
| |
| |
| CREATE TABLE obs_stations ( |
| os_id_station INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, |
| os_ce_participant INTEGER UNSIGNED NULL , |
| os_nom VARCHAR(255) NULL , |
| os_ce_commune INTEGER UNSIGNED NULL , |
| os_latitude FLOAT NULL , |
| os_longitude FLOAT NULL , |
| os_altitude FLOAT NULL , |
| os_ce_environnement INTEGER UNSIGNED NULL , |
| os_commentaire TEXT NULL , |
| PRIMARY KEY(os_id_station), |
| FOREIGN KEY(os_ce_participant) |
| REFERENCES participants(id_participant) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(os_ce_commune) |
| REFERENCES ods_communes(oc_id_commune) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(os_ce_environnement) |
| REFERENCES ods_triples(ot_id_triple) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION); |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| CREATE INDEX IFK_fk_id_participant_os_ce_pa ON obs_stations (os_ce_participant); |
| CREATE INDEX IFK_fk_oc_id_commune_os_ce_com ON obs_stations (os_ce_commune); |
| CREATE INDEX IFK_fk_ot_id_triple_os_ce_envi ON obs_stations (os_ce_environnement); |
| |
| |
| CREATE TABLE ods_especes ( |
| oe_id_espece INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, |
| oe_nom_vernaculaire VARCHAR(255) NULL , |
| oe_nom_scientifique VARCHAR(255) NULL , |
| oe_espece_ecole BOOL NULL , |
| oe_description TEXT NULL , |
| oe_ce_climat INTEGER UNSIGNED NULL , |
| oe_ce_evenements INTEGER UNSIGNED NULL , |
| oe_ce_type INTEGER UNSIGNED NULL , |
| PRIMARY KEY(oe_id_espece), |
| FOREIGN KEY(oe_ce_climat) |
| REFERENCES ods_triples(ot_id_triple) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(oe_ce_evenements) |
| REFERENCES ods_triples(ot_id_triple) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(oe_ce_type) |
| REFERENCES ods_triples(ot_id_triple) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION); |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| CREATE INDEX IFK_fk_ot_id_triple_oe_ce_clim ON ods_especes (oe_ce_climat); |
| CREATE INDEX IFK_fk_ot_id_triples_oe_ce_eve ON ods_especes (oe_ce_evenements); |
| CREATE INDEX IFK_fk_ot_id_triple_oe_ce_type ON ods_especes (oe_ce_type); |
| |
| |
| CREATE TABLE ods_individus ( |
| oi_id_individu INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, |
| oi_ce_espece INTEGER UNSIGNED NULL , |
| oi_ce_station INTEGER UNSIGNED NULL , |
| PRIMARY KEY(oi_id_individu), |
| FOREIGN KEY(oi_ce_espece) |
| REFERENCES ods_especes(oe_id_espece) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(oi_ce_station) |
| REFERENCES obs_stations(os_id_station) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION); |
| |
| |
| |
| |
| |
| CREATE INDEX IFK_fk_oi_ce_espece_oe_id_espe ON ods_individus (oi_ce_espece); |
| CREATE INDEX IFK_fk_os_id_station_oi_ce_ind ON ods_individus (oi_ce_station); |
| |
| |
| CREATE TABLE ods_observations ( |
| oo_id_observation INTEGER UNSIGNED NOT NULL AUTO_INCREMENT, |
| oo_ordre INTEGER UNSIGNED NOT NULL , |
| oo_ce_individu INTEGER UNSIGNED NULL , |
| oo_ce_evenement INTEGER UNSIGNED NULL , |
| oo_date DATE NULL , |
| oo_commentaire TEXT NULL , |
| oo_date_saisie DATE NULL , |
| oo_date_modification DATE NULL , |
| PRIMARY KEY(oo_id_observation), |
| FOREIGN KEY(oo_ce_evenement) |
| REFERENCES ods_triples(ot_id_triple) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION, |
| FOREIGN KEY(oo_ce_individu) |
| REFERENCES ods_individus(oi_id_individu) |
| ON DELETE NO ACTION |
| ON UPDATE NO ACTION); |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| |
| CREATE INDEX IFK_fk_ot_id_triple_oo_ce_even ON ods_observations (oo_ce_evenement); |
| CREATE INDEX IFK_fk_oi_id_individu_oo_ce_in ON ods_observations (oo_ce_individu); |
| |
| |
| |