|
1 |
CREATE TABLE location
|
|
2 |
(
|
|
3 |
location_id int(11) NOT NULL AUTO_INCREMENT,
|
|
4 |
sourceid character varying(30),
|
|
5 |
reference_id int(11),
|
|
6 |
parent_id int(11),
|
|
7 |
reallatitude double precision,
|
|
8 |
reallongitude double precision,
|
|
9 |
locationaccuracy double precision,
|
|
10 |
confidentialitystatus int(11) NOT NULL,
|
|
11 |
confidentialityreason character varying(200),
|
|
12 |
latitude double precision,
|
|
13 |
longitude double precision,
|
|
14 |
-- ...
|
|
15 |
accessioncode character varying(255),
|
|
16 |
sublocationxposition double precision,
|
|
17 |
sublocationyposition double precision,
|
|
18 |
namedplace_id int(11),
|
|
19 |
CONSTRAINT location_pkey PRIMARY KEY (location_id ),
|
|
20 |
CONSTRAINT location_namedplace_id FOREIGN KEY (namedplace_id)
|
|
21 |
REFERENCES namedplace (namedplace_id) MATCH SIMPLE
|
|
22 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
23 |
CONSTRAINT location_parent_id FOREIGN KEY (parent_id)
|
|
24 |
REFERENCES location (location_id) MATCH SIMPLE
|
|
25 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
26 |
CONSTRAINT location_reference_id FOREIGN KEY (reference_id)
|
|
27 |
REFERENCES reference (reference_id) MATCH SIMPLE
|
|
28 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
29 |
CONSTRAINT location_keys UNIQUE (reference_id , parent_id , sourceid )
|
|
30 |
);
|
|
31 |
|
|
32 |
CREATE TABLE locationevent -- VegBank's observation table.
|
|
33 |
(
|
|
34 |
locationevent_id int(11) NOT NULL AUTO_INCREMENT,
|
|
35 |
previous_id int(11),
|
|
36 |
location_id int(11) NOT NULL,
|
|
37 |
project_id int(11),
|
|
38 |
sourceid character varying(30),
|
|
39 |
-- ...
|
|
40 |
accessioncode character varying(255),
|
|
41 |
CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ),
|
|
42 |
CONSTRAINT locationevent_location_id FOREIGN KEY (location_id)
|
|
43 |
REFERENCES location (location_id) MATCH SIMPLE
|
|
44 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
45 |
CONSTRAINT locationevent_project_id FOREIGN KEY (project_id)
|
|
46 |
REFERENCES project (project_id) MATCH SIMPLE
|
|
47 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
48 |
CONSTRAINT locationevent_keys UNIQUE (location_id , project_id , sourceid )
|
|
49 |
);
|
|
50 |
|
|
51 |
CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table.
|
|
52 |
(
|
|
53 |
taxonoccurrence_id int(11) NOT NULL AUTO_INCREMENT,
|
|
54 |
locationevent_id int(11) NOT NULL,
|
|
55 |
authorplantname character varying(255),
|
|
56 |
reference_id int(11),
|
|
57 |
taxoninferencearea double precision,
|
|
58 |
emb_taxonoccurrence int(11),
|
|
59 |
int_origplantconcept_id int(11),
|
|
60 |
int_origplantscifull character varying(255),
|
|
61 |
int_origplantscinamenoauth character varying(255),
|
|
62 |
int_origplantcommon character varying(255),
|
|
63 |
int_origplantcode character varying(255),
|
|
64 |
int_currplantconcept_id int(11),
|
|
65 |
int_currplantscifull character varying(255),
|
|
66 |
int_currplantscinamenoauth character varying(255),
|
|
67 |
int_currplantcommon character varying(255),
|
|
68 |
int_currplantcode character varying(255),
|
|
69 |
accessioncode character varying(255),
|
|
70 |
CONSTRAINT taxonoccurrence_pkey PRIMARY KEY (taxonoccurrence_id ),
|
|
71 |
CONSTRAINT taxonoccurrence_locationevent_id FOREIGN KEY (locationevent_id)
|
|
72 |
REFERENCES locationevent (locationevent_id) MATCH SIMPLE
|
|
73 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
74 |
CONSTRAINT taxonoccurrence_reference_id FOREIGN KEY (reference_id)
|
|
75 |
REFERENCES reference (reference_id) MATCH SIMPLE
|
|
76 |
ON UPDATE CASCADE ON DELETE CASCADE
|
|
77 |
);
|
|
78 |
|
|
79 |
CREATE TABLE aggregateoccurrence -- VegBank's taxonimportance table.
|
|
80 |
(
|
|
81 |
aggregateoccurrence_id int(11) NOT NULL AUTO_INCREMENT,
|
|
82 |
taxonoccurrence_id int(11) NOT NULL,
|
|
83 |
taxonbin_id int(11),
|
|
84 |
cover double precision,
|
|
85 |
basalarea double precision,
|
|
86 |
biomass double precision,
|
|
87 |
inferencearea double precision,
|
|
88 |
stratumbase double precision,
|
|
89 |
stratumheight double precision,
|
|
90 |
emb_aggregateoccurrence int(11),
|
|
91 |
covercode character varying(10),
|
|
92 |
count int(11) NOT NULL,
|
|
93 |
accessioncode character varying(255),
|
|
94 |
CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ),
|
|
95 |
CONSTRAINT aggregateoccurrence_taxonbin_id FOREIGN KEY (taxonbin_id)
|
|
96 |
REFERENCES taxonbin (taxonbin_id) MATCH SIMPLE
|
|
97 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
98 |
CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
|
|
99 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
|
|
100 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
101 |
CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id , taxonbin_id )
|
|
102 |
);
|
|
103 |
|
|
104 |
CREATE TABLE individualplant -- VegBank's stemcount table.
|
|
105 |
(
|
|
106 |
individualplant_id int(11) NOT NULL AUTO_INCREMENT,
|
|
107 |
aggregateoccurrence_id int(11) NOT NULL,
|
|
108 |
height double precision,
|
|
109 |
heightaccuracy double precision,
|
|
110 |
emb_individualplant int(11),
|
|
111 |
sourceid character varying(20),
|
|
112 |
accessioncode character varying(255),
|
|
113 |
stemcount int(11),
|
|
114 |
CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ),
|
|
115 |
CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id)
|
|
116 |
REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE
|
|
117 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
118 |
CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id , sourceid )
|
|
119 |
);
|
|
120 |
|
|
121 |
CREATE TABLE stem -- VegBank's stemlocation table.
|
|
122 |
(
|
|
123 |
stem_id int(11) NOT NULL AUTO_INCREMENT,
|
|
124 |
individualplant_id int(11) NOT NULL,
|
|
125 |
sourceid character varying(20),
|
|
126 |
xposition double precision,
|
|
127 |
yposition double precision,
|
|
128 |
health character varying(50),
|
|
129 |
emb_stem int(11),
|
|
130 |
diameter double precision,
|
|
131 |
height double precision,
|
|
132 |
heightaccuracy double precision,
|
|
133 |
age double precision,
|
|
134 |
accessioncode character varying(255),
|
|
135 |
CONSTRAINT stem_pkey PRIMARY KEY (stem_id ),
|
|
136 |
CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id)
|
|
137 |
REFERENCES individualplant (individualplant_id) MATCH SIMPLE
|
|
138 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
139 |
CONSTRAINT stem_keys UNIQUE (individualplant_id , sourceid )
|
|
140 |
);
|
|
141 |
|
|
142 |
CREATE TABLE taxondetermination -- VegBank's taxoninterpretation table.
|
|
143 |
(
|
|
144 |
taxondetermination_id int(11) NOT NULL AUTO_INCREMENT,
|
|
145 |
taxonoccurrence_id int(11) NOT NULL,
|
|
146 |
plantconcept_id int(11) NOT NULL,
|
|
147 |
party_id int(11) NOT NULL,
|
|
148 |
role_id int(11) NOT NULL,
|
|
149 |
determinationtype character varying(30),
|
|
150 |
reference_id int(11),
|
|
151 |
originaldetermination int(1) NOT NULL,
|
|
152 |
currentdetermination int(1) NOT NULL,
|
|
153 |
taxonfit character varying(50),
|
|
154 |
taxonconfidence character varying(50),
|
|
155 |
grouptype character varying(20),
|
|
156 |
notes text,
|
|
157 |
notespublic int(1),
|
|
158 |
notesmgt int(1),
|
|
159 |
revisions int(1),
|
|
160 |
determinationdate timestamp with time zone NOT NULL,
|
|
161 |
emb_taxondetermination int(11),
|
|
162 |
accessioncode character varying(255),
|
|
163 |
CONSTRAINT taxondetermination_pkey PRIMARY KEY (taxondetermination_id ),
|
|
164 |
CONSTRAINT taxondetermination_party_id FOREIGN KEY (party_id)
|
|
165 |
REFERENCES party (party_id) MATCH SIMPLE
|
|
166 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
167 |
CONSTRAINT taxondetermination_plantconcept_id FOREIGN KEY (plantconcept_id)
|
|
168 |
REFERENCES plantconcept (plantconcept_id) MATCH SIMPLE
|
|
169 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
170 |
CONSTRAINT taxondetermination_reference_id FOREIGN KEY (reference_id)
|
|
171 |
REFERENCES reference (reference_id) MATCH SIMPLE
|
|
172 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
173 |
CONSTRAINT taxondetermination_role_id FOREIGN KEY (role_id)
|
|
174 |
REFERENCES aux_role (role_id) MATCH SIMPLE
|
|
175 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
176 |
CONSTRAINT taxondetermination_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
|
|
177 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
|
|
178 |
ON UPDATE CASCADE ON DELETE CASCADE
|
|
179 |
);
|
|
180 |
|
|
181 |
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data.
|
|
182 |
(
|
|
183 |
specimen_id int(11) NOT NULL AUTO_INCREMENT,
|
|
184 |
individualplant_id int(11),
|
|
185 |
reference_id int(11) NOT NULL,
|
|
186 |
collectioncode_dwc character varying(255),
|
|
187 |
catalognumber_dwc character varying(255),
|
|
188 |
collectiondate timestamp with time zone,
|
|
189 |
collector_id int(11),
|
|
190 |
museum_id int(11),
|
|
191 |
sourceaccessionnumber character varying(100),
|
|
192 |
accessioncode character varying(255),
|
|
193 |
taxonoccurrence_id int(11),
|
|
194 |
CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ),
|
|
195 |
CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id)
|
|
196 |
REFERENCES party (party_id) MATCH SIMPLE
|
|
197 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
198 |
CONSTRAINT specimen_individualplant_id_fkey FOREIGN KEY (individualplant_id)
|
|
199 |
REFERENCES individualplant (individualplant_id) MATCH SIMPLE
|
|
200 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
201 |
CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id)
|
|
202 |
REFERENCES party (party_id) MATCH SIMPLE
|
|
203 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
204 |
CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id)
|
|
205 |
REFERENCES reference (reference_id) MATCH SIMPLE
|
|
206 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
207 |
CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
|
|
208 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
|
|
209 |
ON UPDATE CASCADE ON DELETE CASCADE,
|
|
210 |
CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc )
|
|
211 |
);
|
|
212 |
|
|
213 |
CREATE TABLE taxonbin -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
|
|
214 |
(
|
|
215 |
taxonbin_id int(11) NOT NULL,
|
|
216 |
label character varying(255) NOT NULL,
|
|
217 |
stratum_id int(11),
|
|
218 |
sizeclass_id int(11),
|
|
219 |
coverindex_id int(11),
|
|
220 |
accessioncode character varying(255),
|
|
221 |
CONSTRAINT taxonbin_pkey PRIMARY KEY (taxonbin_id ),
|
|
222 |
CONSTRAINT taxonbin_sizeclass_id FOREIGN KEY (sizeclass_id)
|
|
223 |
REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE
|
|
224 |
ON UPDATE CASCADE ON DELETE CASCADE
|
|
225 |
);
|
|
226 |
|
|
227 |
CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.
|
|
228 |
(
|
|
229 |
sizeclass_id int(11) NOT NULL AUTO_INCREMENT,
|
|
230 |
mindiameter double precision,
|
|
231 |
diameteraccuracy double precision,
|
|
232 |
minheight double precision,
|
|
233 |
heightaccuracy double precision,
|
|
234 |
maxdiameter double precision,
|
|
235 |
maxheight double precision,
|
|
236 |
accessioncode character varying(255),
|
|
237 |
CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id )
|
|
238 |
);
|
Added schemas/vegbien.for_ERD.sql and generated MySQL