31 |
aurelien |
1 |
CREATE TABLE ods_triples (
|
|
|
2 |
ot_id_triple INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
3 |
ot_cle VARCHAR(255) NULL ,
|
|
|
4 |
ot_ce_parent INTEGER UNSIGNED NULL ,
|
|
|
5 |
ot_valeur VARCHAR(255) NULL ,
|
|
|
6 |
ot_description TEXT NULL ,
|
|
|
7 |
PRIMARY KEY(ot_id_triple),
|
|
|
8 |
FOREIGN KEY(ot_ce_parent)
|
|
|
9 |
REFERENCES ods_triples(ot_id_triple)
|
|
|
10 |
ON DELETE NO ACTION
|
|
|
11 |
ON UPDATE NO ACTION);
|
|
|
12 |
|
|
|
13 |
|
|
|
14 |
|
|
|
15 |
|
|
|
16 |
|
|
|
17 |
|
|
|
18 |
|
|
|
19 |
CREATE INDEX IFK_fk_ot_id_triple_ot_ce_pare ON ods_triples (ot_ce_parent);
|
|
|
20 |
|
|
|
21 |
|
|
|
22 |
CREATE TABLE ods_communes (
|
|
|
23 |
oc_id_commune INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
24 |
oc_code_insee INTEGER UNSIGNED NULL ,
|
|
|
25 |
oc_nom TEXT NULL ,
|
|
|
26 |
oc_secteur VARCHAR(50) NULL ,
|
|
|
27 |
oc_latitude FLOAT NULL ,
|
|
|
28 |
oc_longitude FLOAT NULL ,
|
|
|
29 |
PRIMARY KEY(oc_id_commune));
|
|
|
30 |
|
|
|
31 |
|
|
|
32 |
|
|
|
33 |
|
|
|
34 |
|
|
|
35 |
|
|
|
36 |
|
|
|
37 |
|
|
|
38 |
|
|
|
39 |
|
|
|
40 |
CREATE TABLE participants (
|
|
|
41 |
id_participant INTEGER UNSIGNED NOT NULL AUTO_INCREMENT ,
|
|
|
42 |
PRIMARY KEY(id_participant));
|
|
|
43 |
|
|
|
44 |
|
|
|
45 |
|
|
|
46 |
|
|
|
47 |
CREATE TABLE obs_stations (
|
|
|
48 |
os_id_station INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
49 |
os_ce_participant INTEGER UNSIGNED NULL ,
|
|
|
50 |
os_nom VARCHAR(255) NULL ,
|
|
|
51 |
os_ce_commune INTEGER UNSIGNED NULL ,
|
|
|
52 |
os_latitude FLOAT NULL ,
|
|
|
53 |
os_longitude FLOAT NULL ,
|
|
|
54 |
os_altitude FLOAT NULL ,
|
|
|
55 |
os_ce_environnement INTEGER UNSIGNED NULL ,
|
|
|
56 |
os_commentaire TEXT NULL ,
|
|
|
57 |
PRIMARY KEY(os_id_station),
|
|
|
58 |
FOREIGN KEY(os_ce_participant)
|
|
|
59 |
REFERENCES participants(id_participant)
|
|
|
60 |
ON DELETE NO ACTION
|
|
|
61 |
ON UPDATE NO ACTION,
|
|
|
62 |
FOREIGN KEY(os_ce_commune)
|
|
|
63 |
REFERENCES ods_communes(oc_id_commune)
|
|
|
64 |
ON DELETE NO ACTION
|
|
|
65 |
ON UPDATE NO ACTION,
|
|
|
66 |
FOREIGN KEY(os_ce_environnement)
|
|
|
67 |
REFERENCES ods_triples(ot_id_triple)
|
|
|
68 |
ON DELETE NO ACTION
|
|
|
69 |
ON UPDATE NO ACTION);
|
|
|
70 |
|
|
|
71 |
|
|
|
72 |
|
|
|
73 |
|
|
|
74 |
|
|
|
75 |
|
|
|
76 |
|
|
|
77 |
|
|
|
78 |
|
|
|
79 |
|
|
|
80 |
|
|
|
81 |
CREATE INDEX IFK_fk_id_participant_os_ce_pa ON obs_stations (os_ce_participant);
|
|
|
82 |
CREATE INDEX IFK_fk_oc_id_commune_os_ce_com ON obs_stations (os_ce_commune);
|
|
|
83 |
CREATE INDEX IFK_fk_ot_id_triple_os_ce_envi ON obs_stations (os_ce_environnement);
|
|
|
84 |
|
|
|
85 |
|
|
|
86 |
CREATE TABLE ods_especes (
|
|
|
87 |
oe_id_espece INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
88 |
oe_nom_vernaculaire VARCHAR(255) NULL ,
|
|
|
89 |
oe_nom_scientifique VARCHAR(255) NULL ,
|
|
|
90 |
oe_espece_ecole BOOL NULL ,
|
|
|
91 |
oe_description TEXT NULL ,
|
|
|
92 |
oe_ce_climat INTEGER UNSIGNED NULL ,
|
|
|
93 |
oe_ce_evenements INTEGER UNSIGNED NULL ,
|
|
|
94 |
oe_ce_type INTEGER UNSIGNED NULL ,
|
|
|
95 |
PRIMARY KEY(oe_id_espece),
|
|
|
96 |
FOREIGN KEY(oe_ce_climat)
|
|
|
97 |
REFERENCES ods_triples(ot_id_triple)
|
|
|
98 |
ON DELETE NO ACTION
|
|
|
99 |
ON UPDATE NO ACTION,
|
|
|
100 |
FOREIGN KEY(oe_ce_evenements)
|
|
|
101 |
REFERENCES ods_triples(ot_id_triple)
|
|
|
102 |
ON DELETE NO ACTION
|
|
|
103 |
ON UPDATE NO ACTION,
|
|
|
104 |
FOREIGN KEY(oe_ce_type)
|
|
|
105 |
REFERENCES ods_triples(ot_id_triple)
|
|
|
106 |
ON DELETE NO ACTION
|
|
|
107 |
ON UPDATE NO ACTION);
|
|
|
108 |
|
|
|
109 |
|
|
|
110 |
|
|
|
111 |
|
|
|
112 |
|
|
|
113 |
|
|
|
114 |
|
|
|
115 |
|
|
|
116 |
|
|
|
117 |
|
|
|
118 |
CREATE INDEX IFK_fk_ot_id_triple_oe_ce_clim ON ods_especes (oe_ce_climat);
|
|
|
119 |
CREATE INDEX IFK_fk_ot_id_triples_oe_ce_eve ON ods_especes (oe_ce_evenements);
|
|
|
120 |
CREATE INDEX IFK_fk_ot_id_triple_oe_ce_type ON ods_especes (oe_ce_type);
|
|
|
121 |
|
|
|
122 |
|
|
|
123 |
CREATE TABLE ods_individus (
|
|
|
124 |
oi_id_individu INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
125 |
oi_ce_espece INTEGER UNSIGNED NULL ,
|
|
|
126 |
oi_ce_station INTEGER UNSIGNED NULL ,
|
|
|
127 |
PRIMARY KEY(oi_id_individu),
|
|
|
128 |
FOREIGN KEY(oi_ce_espece)
|
|
|
129 |
REFERENCES ods_especes(oe_id_espece)
|
|
|
130 |
ON DELETE NO ACTION
|
|
|
131 |
ON UPDATE NO ACTION,
|
|
|
132 |
FOREIGN KEY(oi_ce_station)
|
|
|
133 |
REFERENCES obs_stations(os_id_station)
|
|
|
134 |
ON DELETE NO ACTION
|
|
|
135 |
ON UPDATE NO ACTION);
|
|
|
136 |
|
|
|
137 |
|
|
|
138 |
|
|
|
139 |
|
|
|
140 |
|
|
|
141 |
CREATE INDEX IFK_fk_oi_ce_espece_oe_id_espe ON ods_individus (oi_ce_espece);
|
|
|
142 |
CREATE INDEX IFK_fk_os_id_station_oi_ce_ind ON ods_individus (oi_ce_station);
|
|
|
143 |
|
|
|
144 |
|
|
|
145 |
CREATE TABLE ods_observations (
|
|
|
146 |
oo_id_observation INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
147 |
oo_ordre INTEGER UNSIGNED NOT NULL ,
|
|
|
148 |
oo_ce_individu INTEGER UNSIGNED NULL ,
|
|
|
149 |
oo_ce_evenement INTEGER UNSIGNED NULL ,
|
|
|
150 |
oo_date DATE NULL ,
|
|
|
151 |
oo_commentaire TEXT NULL ,
|
|
|
152 |
oo_date_saisie DATE NULL ,
|
|
|
153 |
oo_date_modification DATE NULL ,
|
|
|
154 |
PRIMARY KEY(oo_id_observation),
|
|
|
155 |
FOREIGN KEY(oo_ce_evenement)
|
|
|
156 |
REFERENCES ods_triples(ot_id_triple)
|
|
|
157 |
ON DELETE NO ACTION
|
|
|
158 |
ON UPDATE NO ACTION,
|
|
|
159 |
FOREIGN KEY(oo_ce_individu)
|
|
|
160 |
REFERENCES ods_individus(oi_id_individu)
|
|
|
161 |
ON DELETE NO ACTION
|
|
|
162 |
ON UPDATE NO ACTION);
|
|
|
163 |
|
|
|
164 |
|
|
|
165 |
|
|
|
166 |
|
|
|
167 |
|
|
|
168 |
|
|
|
169 |
|
|
|
170 |
|
|
|
171 |
|
|
|
172 |
|
|
|
173 |
CREATE INDEX IFK_fk_ot_id_triple_oo_ce_even ON ods_observations (oo_ce_evenement);
|
|
|
174 |
CREATE INDEX IFK_fk_oi_id_individu_oo_ce_in ON ods_observations (oo_ce_individu);
|
|
|
175 |
|
|
|
176 |
|
|
|
177 |
|