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