Project

General

Profile

1 537 aaronmk
-- Existing tables
2
3
CREATE TABLE aux_role
4
(
5
  role_id int(11) NOT NULL AUTO_INCREMENT,
6
  rolecode character varying(30) NOT NULL,
7
  roledescription character varying(200),
8
  CONSTRAINT aux_role_pkey PRIMARY KEY (role_id )
9
);
10
11
CREATE TABLE reference
12
(
13
  reference_id int(11) NOT NULL AUTO_INCREMENT,
14
  shortname character varying(250),
15
  fulltext__ text,
16
  referencetype character varying(250)
17
);
18
19
CREATE TABLE party
20
(
21
  party_id int(11) NOT NULL AUTO_INCREMENT,
22
  salutation character varying(20),
23
  givenname character varying(50),
24
  middlename character varying(50),
25
  surname character varying(50),
26
  organizationname character varying(100)
27
);
28
29
CREATE TABLE plantname
30
(
31
  plantname_id int(11) NOT NULL AUTO_INCREMENT,
32
  plantname character varying(255) NOT NULL,
33
  reference_id int(11),
34
  dateentered timestamp DEFAULT now(),
35
  CONSTRAINT plantname_pkey PRIMARY KEY (plantname_id ),
36
  CONSTRAINT plantname_reference_id FOREIGN KEY (reference_id)
37
      REFERENCES reference (reference_id) MATCH SIMPLE
38
      ON UPDATE CASCADE ON DELETE CASCADE
39
);
40
41
CREATE TABLE plantconcept
42
(
43
  plantconcept_id int(11) NOT NULL AUTO_INCREMENT,
44
  plantname_id int(11) NOT NULL,
45
  reference_id int(11) NOT NULL,
46
  plantname character varying(200),
47
  plantcode character varying(23),
48
  plantdescription text,
49
  d_obscount int(11),
50
  d_currentaccepted int(1),
51
  accessioncode character varying(255),
52
  CONSTRAINT plantconcept_pkey PRIMARY KEY (plantconcept_id ),
53
  CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id)
54
      REFERENCES plantname (plantname_id) MATCH SIMPLE
55
      ON UPDATE CASCADE ON DELETE CASCADE,
56
  CONSTRAINT plantconcept_reference_id FOREIGN KEY (reference_id)
57
      REFERENCES reference (reference_id) MATCH SIMPLE
58
      ON UPDATE CASCADE ON DELETE CASCADE
59
);
60
61
CREATE TABLE namedplace
62
(
63
  namedplace_id int(11) NOT NULL AUTO_INCREMENT,
64
  placesystem character varying(50),
65
  placename character varying(100) NOT NULL,
66
  placedescription text,
67
  placecode character varying(15),
68
  owner character varying(100),
69
  reference_id int(11),
70
  d_obscount int(11),
71
  accessioncode character varying(255),
72
  CONSTRAINT namedplace_pkey PRIMARY KEY (namedplace_id ),
73
  CONSTRAINT namedplace_reference_id FOREIGN KEY (reference_id)
74
      REFERENCES reference (reference_id) MATCH SIMPLE
75
      ON UPDATE CASCADE ON DELETE CASCADE,
76
  CONSTRAINT namedplace_keys UNIQUE (placesystem , placename )
77
);
78
79
CREATE TABLE project
80
(
81
  project_id int(11) NOT NULL AUTO_INCREMENT,
82
  projectname character varying(150) NOT NULL,
83
  projectdescription text,
84
  startdate timestamp,
85
  stopdate timestamp,
86
  d_obscount int(11),
87
  d_lastlocationaddeddate timestamp,
88
  accessioncode character varying(255),
89
  CONSTRAINT project_pkey PRIMARY KEY (project_id ),
90
  CONSTRAINT project_keys UNIQUE (projectname , startdate , stopdate )
91
);
92
93
-- New tables
94
95 536 aaronmk
CREATE TABLE location
96
(
97
  location_id int(11) NOT NULL AUTO_INCREMENT,
98
  sourceid character varying(30),
99
  reference_id int(11),
100
  parent_id int(11),
101
  reallatitude double precision,
102
  reallongitude double precision,
103
  locationaccuracy double precision,
104
  confidentialitystatus int(11) NOT NULL,
105
  confidentialityreason character varying(200),
106
  latitude double precision,
107
  longitude double precision,
108
  -- ...
109
  accessioncode character varying(255),
110
  sublocationxposition double precision,
111
  sublocationyposition double precision,
112
  namedplace_id int(11),
113
  CONSTRAINT location_pkey PRIMARY KEY (location_id ),
114
  CONSTRAINT location_namedplace_id FOREIGN KEY (namedplace_id)
115
      REFERENCES namedplace (namedplace_id) MATCH SIMPLE
116
      ON UPDATE CASCADE ON DELETE CASCADE,
117
  CONSTRAINT location_parent_id FOREIGN KEY (parent_id)
118
      REFERENCES location (location_id) MATCH SIMPLE
119
      ON UPDATE CASCADE ON DELETE CASCADE,
120
  CONSTRAINT location_reference_id FOREIGN KEY (reference_id)
121
      REFERENCES reference (reference_id) MATCH SIMPLE
122
      ON UPDATE CASCADE ON DELETE CASCADE,
123
  CONSTRAINT location_keys UNIQUE (reference_id , parent_id , sourceid )
124
);
125
126
CREATE TABLE locationevent -- VegBank's observation table.
127
(
128
  locationevent_id int(11) NOT NULL AUTO_INCREMENT,
129
  previous_id int(11),
130
  location_id int(11) NOT NULL,
131
  project_id int(11),
132
  sourceid character varying(30),
133
  -- ...
134
  accessioncode character varying(255),
135
  CONSTRAINT locationevent_pkey PRIMARY KEY (locationevent_id ),
136
  CONSTRAINT locationevent_location_id FOREIGN KEY (location_id)
137
      REFERENCES location (location_id) MATCH SIMPLE
138
      ON UPDATE CASCADE ON DELETE CASCADE,
139
  CONSTRAINT locationevent_project_id FOREIGN KEY (project_id)
140
      REFERENCES project (project_id) MATCH SIMPLE
141
      ON UPDATE CASCADE ON DELETE CASCADE,
142
  CONSTRAINT locationevent_keys UNIQUE (location_id , project_id , sourceid )
143
);
144
145
CREATE TABLE taxonoccurrence -- VegBank's taxonobservation table.
146
(
147
  taxonoccurrence_id int(11) NOT NULL AUTO_INCREMENT,
148
  locationevent_id int(11) NOT NULL,
149
  authorplantname character varying(255),
150
  reference_id int(11),
151
  taxoninferencearea double precision,
152
  emb_taxonoccurrence int(11),
153 539 aaronmk
  -- ...
154 536 aaronmk
  accessioncode character varying(255),
155
  CONSTRAINT taxonoccurrence_pkey PRIMARY KEY (taxonoccurrence_id ),
156
  CONSTRAINT taxonoccurrence_locationevent_id FOREIGN KEY (locationevent_id)
157
      REFERENCES locationevent (locationevent_id) MATCH SIMPLE
158
      ON UPDATE CASCADE ON DELETE CASCADE,
159
  CONSTRAINT taxonoccurrence_reference_id FOREIGN KEY (reference_id)
160
      REFERENCES reference (reference_id) MATCH SIMPLE
161
      ON UPDATE CASCADE ON DELETE CASCADE
162
);
163
164
CREATE TABLE aggregateoccurrence -- VegBank's taxonimportance table.
165
(
166
  aggregateoccurrence_id int(11) NOT NULL AUTO_INCREMENT,
167
  taxonoccurrence_id int(11) NOT NULL,
168
  taxonbin_id int(11),
169
  cover double precision,
170
  basalarea double precision,
171
  biomass double precision,
172
  inferencearea double precision,
173
  stratumbase double precision,
174
  stratumheight double precision,
175
  emb_aggregateoccurrence int(11),
176
  covercode character varying(10),
177
  count int(11) NOT NULL,
178
  accessioncode character varying(255),
179
  CONSTRAINT aggregateoccurrence_pkey PRIMARY KEY (aggregateoccurrence_id ),
180
  CONSTRAINT aggregateoccurrence_taxonbin_id FOREIGN KEY (taxonbin_id)
181
      REFERENCES taxonbin (taxonbin_id) MATCH SIMPLE
182
      ON UPDATE CASCADE ON DELETE CASCADE,
183
  CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
184
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
185
      ON UPDATE CASCADE ON DELETE CASCADE,
186
  CONSTRAINT aggregateoccurrence_keys UNIQUE (taxonoccurrence_id , taxonbin_id )
187
);
188
189
CREATE TABLE individualplant -- VegBank's stemcount table.
190
(
191
  individualplant_id int(11) NOT NULL AUTO_INCREMENT,
192
  aggregateoccurrence_id int(11) NOT NULL,
193
  height double precision,
194
  heightaccuracy double precision,
195
  emb_individualplant int(11),
196
  sourceid character varying(20),
197
  accessioncode character varying(255),
198
  stemcount int(11),
199
  CONSTRAINT individualplant_pkey PRIMARY KEY (individualplant_id ),
200
  CONSTRAINT individualplant_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id)
201
      REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE
202
      ON UPDATE CASCADE ON DELETE CASCADE,
203
  CONSTRAINT individualplant_keys UNIQUE (aggregateoccurrence_id , sourceid )
204
);
205
206
CREATE TABLE stem -- VegBank's stemlocation table.
207
(
208
  stem_id int(11) NOT NULL AUTO_INCREMENT,
209
  individualplant_id int(11) NOT NULL,
210
  sourceid character varying(20),
211
  xposition double precision,
212
  yposition double precision,
213
  health character varying(50),
214
  emb_stem int(11),
215
  diameter double precision,
216
  height double precision,
217
  heightaccuracy double precision,
218
  age double precision,
219
  accessioncode character varying(255),
220
  CONSTRAINT stem_pkey PRIMARY KEY (stem_id ),
221
  CONSTRAINT stem_individualplant_id FOREIGN KEY (individualplant_id)
222
      REFERENCES individualplant (individualplant_id) MATCH SIMPLE
223
      ON UPDATE CASCADE ON DELETE CASCADE,
224
  CONSTRAINT stem_keys UNIQUE (individualplant_id , sourceid )
225
);
226
227 537 aaronmk
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data.
228
(
229
  specimen_id int(11) NOT NULL AUTO_INCREMENT,
230
  reference_id int(11) NOT NULL,
231
  collectioncode_dwc character varying(255),
232
  catalognumber_dwc character varying(255),
233
  collectiondate timestamp,
234
  collector_id int(11),
235
  museum_id int(11),
236
  sourceaccessionnumber character varying(100),
237
  accessioncode character varying(255),
238
  taxonoccurrence_id int(11),
239
  CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ),
240
  CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id)
241
      REFERENCES party (party_id) MATCH SIMPLE
242
      ON UPDATE CASCADE ON DELETE CASCADE,
243
  CONSTRAINT specimen_individualplant_id_fkey FOREIGN KEY (individualplant_id)
244
      REFERENCES individualplant (individualplant_id) MATCH SIMPLE
245
      ON UPDATE CASCADE ON DELETE CASCADE,
246
  CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id)
247
      REFERENCES party (party_id) MATCH SIMPLE
248
      ON UPDATE CASCADE ON DELETE CASCADE,
249
  CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id)
250
      REFERENCES reference (reference_id) MATCH SIMPLE
251
      ON UPDATE CASCADE ON DELETE CASCADE,
252
  CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
253
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
254
      ON UPDATE CASCADE ON DELETE CASCADE,
255
  CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc )
256
);
257
258 536 aaronmk
CREATE TABLE taxondetermination -- VegBank's taxoninterpretation table.
259
(
260
  taxondetermination_id int(11) NOT NULL AUTO_INCREMENT,
261
  taxonoccurrence_id int(11) NOT NULL,
262
  plantconcept_id int(11) NOT NULL,
263
  party_id int(11) NOT NULL,
264
  role_id int(11) NOT NULL,
265
  determinationtype character varying(30),
266
  reference_id int(11),
267
  originaldetermination int(1) NOT NULL,
268
  currentdetermination int(1) NOT NULL,
269
  taxonfit character varying(50),
270
  taxonconfidence character varying(50),
271
  grouptype character varying(20),
272
  notes text,
273
  notespublic int(1),
274
  notesmgt int(1),
275
  revisions int(1),
276 537 aaronmk
  determinationdate timestamp NOT NULL,
277 536 aaronmk
  emb_taxondetermination int(11),
278
  accessioncode character varying(255),
279
  CONSTRAINT taxondetermination_pkey PRIMARY KEY (taxondetermination_id ),
280
  CONSTRAINT taxondetermination_party_id FOREIGN KEY (party_id)
281
      REFERENCES party (party_id) MATCH SIMPLE
282
      ON UPDATE CASCADE ON DELETE CASCADE,
283
  CONSTRAINT taxondetermination_plantconcept_id FOREIGN KEY (plantconcept_id)
284
      REFERENCES plantconcept (plantconcept_id) MATCH SIMPLE
285
      ON UPDATE CASCADE ON DELETE CASCADE,
286
  CONSTRAINT taxondetermination_reference_id FOREIGN KEY (reference_id)
287
      REFERENCES reference (reference_id) MATCH SIMPLE
288
      ON UPDATE CASCADE ON DELETE CASCADE,
289
  CONSTRAINT taxondetermination_role_id FOREIGN KEY (role_id)
290
      REFERENCES aux_role (role_id) MATCH SIMPLE
291
      ON UPDATE CASCADE ON DELETE CASCADE,
292
  CONSTRAINT taxondetermination_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id)
293
      REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE
294
      ON UPDATE CASCADE ON DELETE CASCADE
295
);
296
297 537 aaronmk
CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.
298 536 aaronmk
(
299 537 aaronmk
  sizeclass_id int(11) NOT NULL AUTO_INCREMENT,
300
  mindiameter double precision,
301
  diameteraccuracy double precision,
302
  minheight double precision,
303
  heightaccuracy double precision,
304
  maxdiameter double precision,
305
  maxheight double precision,
306 536 aaronmk
  accessioncode character varying(255),
307 537 aaronmk
  CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id )
308 536 aaronmk
);
309
310
CREATE TABLE taxonbin -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
311
(
312
  taxonbin_id int(11) NOT NULL,
313
  label character varying(255) NOT NULL,
314
  stratum_id int(11),
315
  sizeclass_id int(11),
316
  coverindex_id int(11),
317
  accessioncode character varying(255),
318
  CONSTRAINT taxonbin_pkey PRIMARY KEY (taxonbin_id ),
319
  CONSTRAINT taxonbin_sizeclass_id FOREIGN KEY (sizeclass_id)
320
      REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE
321
      ON UPDATE CASCADE ON DELETE CASCADE
322
);