1
|
-- phpMyAdmin SQL Dump
|
2
|
-- version 3.4.6
|
3
|
-- http://www.phpmyadmin.net
|
4
|
--
|
5
|
-- Host: 127.0.0.1
|
6
|
-- Generation Time: Feb 24, 2012 at 02:12 PM
|
7
|
-- Server version: 5.5.16
|
8
|
-- PHP Version: 5.3.8
|
9
|
|
10
|
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
|
11
|
SET time_zone = "+00:00";
|
12
|
|
13
|
|
14
|
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
|
15
|
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
|
16
|
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
|
17
|
/*!40101 SET NAMES utf8 */;
|
18
|
|
19
|
--
|
20
|
-- Database: `CTFS`
|
21
|
--
|
22
|
|
23
|
-- --------------------------------------------------------
|
24
|
|
25
|
--
|
26
|
-- Table structure for table `Census`
|
27
|
--
|
28
|
|
29
|
CREATE TABLE IF NOT EXISTS `Census` (
|
30
|
`CensusID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
31
|
`PlotID` int(10) unsigned NOT NULL,
|
32
|
`PlotCensusNumber` int(11) DEFAULT NULL,
|
33
|
`StartDate` date DEFAULT NULL,
|
34
|
`EndDate` date DEFAULT NULL,
|
35
|
`Description` varchar(128) DEFAULT NULL,
|
36
|
PRIMARY KEY (`CensusID`),
|
37
|
KEY `Ref610` (`PlotID`)
|
38
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=200 ;
|
39
|
|
40
|
-- --------------------------------------------------------
|
41
|
|
42
|
--
|
43
|
-- Table structure for table `CensusQuadrat`
|
44
|
--
|
45
|
|
46
|
CREATE TABLE IF NOT EXISTS `CensusQuadrat` (
|
47
|
`CensusID` int(10) unsigned NOT NULL,
|
48
|
`QuadratID` int(10) unsigned NOT NULL,
|
49
|
`PlotID` int(10) unsigned NOT NULL,
|
50
|
PRIMARY KEY (`CensusID`,`QuadratID`,`PlotID`),
|
51
|
KEY `Ref733` (`QuadratID`,`PlotID`),
|
52
|
KEY `Ref534` (`CensusID`)
|
53
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
54
|
|
55
|
-- --------------------------------------------------------
|
56
|
|
57
|
--
|
58
|
-- Table structure for table `Country`
|
59
|
--
|
60
|
|
61
|
CREATE TABLE IF NOT EXISTS `Country` (
|
62
|
`CountryID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
|
63
|
`CountryName` varchar(64) DEFAULT NULL,
|
64
|
PRIMARY KEY (`CountryID`)
|
65
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;
|
66
|
|
67
|
-- --------------------------------------------------------
|
68
|
|
69
|
--
|
70
|
-- Table structure for table `CurrentObsolete`
|
71
|
--
|
72
|
|
73
|
CREATE TABLE IF NOT EXISTS `CurrentObsolete` (
|
74
|
`SpeciesID` int(10) unsigned NOT NULL,
|
75
|
`ObsoleteSpeciesID` int(10) unsigned NOT NULL,
|
76
|
`ChangeDate` datetime NOT NULL,
|
77
|
`ChangeCodeID` int(10) unsigned NOT NULL,
|
78
|
`ChangeNote` varchar(128) DEFAULT NULL,
|
79
|
PRIMARY KEY (`SpeciesID`,`ObsoleteSpeciesID`,`ChangeDate`),
|
80
|
KEY `Ref32191` (`ChangeCodeID`),
|
81
|
KEY `Ref92192` (`SpeciesID`),
|
82
|
KEY `Ref92212` (`ObsoleteSpeciesID`)
|
83
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
84
|
|
85
|
-- --------------------------------------------------------
|
86
|
|
87
|
--
|
88
|
-- Table structure for table `DataCollection`
|
89
|
--
|
90
|
|
91
|
CREATE TABLE IF NOT EXISTS `DataCollection` (
|
92
|
`PersonnelID` smallint(5) unsigned NOT NULL,
|
93
|
`RoleID` smallint(5) unsigned NOT NULL,
|
94
|
`CensusID` int(10) unsigned NOT NULL,
|
95
|
`QuadratID` int(10) unsigned NOT NULL,
|
96
|
`PlotID` int(10) unsigned NOT NULL,
|
97
|
`StartDate` date DEFAULT NULL,
|
98
|
`EndDate` date DEFAULT NULL,
|
99
|
PRIMARY KEY (`PersonnelID`,`RoleID`,`CensusID`,`QuadratID`,`PlotID`),
|
100
|
KEY `Ref3883` (`RoleID`),
|
101
|
KEY `Ref1743` (`CensusID`,`QuadratID`,`PlotID`),
|
102
|
KEY `Ref1544` (`PersonnelID`)
|
103
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
104
|
|
105
|
-- --------------------------------------------------------
|
106
|
|
107
|
--
|
108
|
-- Table structure for table `DataEntry`
|
109
|
--
|
110
|
|
111
|
CREATE TABLE IF NOT EXISTS `DataEntry` (
|
112
|
`PersonnelID` smallint(5) unsigned NOT NULL,
|
113
|
`CensusID` int(10) unsigned NOT NULL,
|
114
|
`QuadratID` int(10) unsigned NOT NULL,
|
115
|
`PlotID` int(10) unsigned NOT NULL,
|
116
|
`StartDate` date DEFAULT NULL,
|
117
|
`EndDate` date DEFAULT NULL,
|
118
|
PRIMARY KEY (`PersonnelID`,`CensusID`,`QuadratID`,`PlotID`),
|
119
|
KEY `Ref1745` (`CensusID`,`QuadratID`,`PlotID`),
|
120
|
KEY `Ref1547` (`PersonnelID`)
|
121
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
122
|
|
123
|
-- --------------------------------------------------------
|
124
|
|
125
|
--
|
126
|
-- Table structure for table `DFtemp`
|
127
|
--
|
128
|
|
129
|
CREATE TABLE IF NOT EXISTS `DFtemp` (
|
130
|
`MeasureID` int(11) NOT NULL,
|
131
|
`PlotID` int(11) NOT NULL,
|
132
|
`Plot` varchar(35) DEFAULT NULL,
|
133
|
`Family` char(32) DEFAULT NULL,
|
134
|
`GenusSpecies` char(64) DEFAULT NULL,
|
135
|
`Genus` char(32) DEFAULT NULL,
|
136
|
`SpeciesName` char(64) DEFAULT NULL,
|
137
|
`SpeciesID` int(10) unsigned DEFAULT NULL,
|
138
|
`Mnemonic` char(10) DEFAULT NULL,
|
139
|
`QuadratID` int(11) NOT NULL,
|
140
|
`QuadratName` varchar(12) DEFAULT NULL,
|
141
|
`x` float DEFAULT NULL,
|
142
|
`y` float DEFAULT NULL,
|
143
|
`gx` float DEFAULT NULL,
|
144
|
`gy` float DEFAULT NULL,
|
145
|
`TreeID` int(11) NOT NULL,
|
146
|
`Tag` char(10) DEFAULT NULL,
|
147
|
`StemID` int(11) NOT NULL,
|
148
|
`StemTag` varchar(32) DEFAULT NULL,
|
149
|
`PrimaryStem` char(20) DEFAULT NULL,
|
150
|
`CensusID` int(11) NOT NULL,
|
151
|
`PlotCensusNumber` int(11) DEFAULT NULL,
|
152
|
`DBH` float DEFAULT NULL,
|
153
|
`HOM` float DEFAULT NULL,
|
154
|
`ExactDate` date DEFAULT NULL,
|
155
|
`ListOfTSM` varchar(256) DEFAULT NULL,
|
156
|
`Status` varchar(15) DEFAULT NULL,
|
157
|
PRIMARY KEY (`MeasureID`,`CensusID`),
|
158
|
KEY `PlotID` (`PlotID`),
|
159
|
KEY `Genus` (`Genus`(5)),
|
160
|
KEY `SpeciesName` (`SpeciesName`(5)),
|
161
|
KEY `SpeciesID` (`SpeciesID`),
|
162
|
KEY `QuadratName` (`QuadratName`(4)),
|
163
|
KEY `PlotCensusNumber` (`PlotCensusNumber`),
|
164
|
KEY `DBH` (`DBH`),
|
165
|
KEY `PrimaryStem` (`PrimaryStem`),
|
166
|
KEY `ListOfTSM` (`ListOfTSM`),
|
167
|
KEY `Status` (`Status`),
|
168
|
KEY `ForDataEntry` (`TreeID`,`StemID`,`CensusID`),
|
169
|
KEY `ForDataScreening` (`Tag`,`StemTag`,`PlotID`)
|
170
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
|
171
|
|
172
|
-- --------------------------------------------------------
|
173
|
|
174
|
--
|
175
|
-- Table structure for table `Family`
|
176
|
--
|
177
|
|
178
|
CREATE TABLE IF NOT EXISTS `Family` (
|
179
|
`FamilyID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
180
|
`Family` char(32) DEFAULT NULL,
|
181
|
`ReferenceID` smallint(5) unsigned DEFAULT NULL,
|
182
|
PRIMARY KEY (`FamilyID`),
|
183
|
KEY `Ref84175` (`ReferenceID`)
|
184
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=550 ;
|
185
|
|
186
|
-- --------------------------------------------------------
|
187
|
|
188
|
--
|
189
|
-- Table structure for table `Genus`
|
190
|
--
|
191
|
|
192
|
CREATE TABLE IF NOT EXISTS `Genus` (
|
193
|
`GenusID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
194
|
`Genus` char(32) DEFAULT NULL,
|
195
|
`ReferenceID` smallint(5) unsigned DEFAULT NULL,
|
196
|
`Authority` char(32) DEFAULT NULL,
|
197
|
`FamilyID` int(10) unsigned NOT NULL,
|
198
|
PRIMARY KEY (`GenusID`),
|
199
|
KEY `Ref2868` (`FamilyID`),
|
200
|
KEY `Ref84176` (`ReferenceID`)
|
201
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=21244 ;
|
202
|
|
203
|
-- --------------------------------------------------------
|
204
|
|
205
|
--
|
206
|
-- Table structure for table `LogMAttrHistory`
|
207
|
--
|
208
|
|
209
|
CREATE TABLE IF NOT EXISTS `LogMAttrHistory` (
|
210
|
`LogMAttrHistoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
211
|
`MeasureID` int(10) unsigned NOT NULL,
|
212
|
`CensusID` int(10) unsigned NOT NULL,
|
213
|
`TSMID` int(10) unsigned NOT NULL,
|
214
|
`DateOfChange` date DEFAULT NULL,
|
215
|
`DescriptionOfChange` varchar(128) DEFAULT NULL,
|
216
|
PRIMARY KEY (`LogMAttrHistoryID`),
|
217
|
KEY `Ref21225` (`MeasureID`,`CensusID`,`TSMID`)
|
218
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13754 ;
|
219
|
|
220
|
-- --------------------------------------------------------
|
221
|
|
222
|
--
|
223
|
-- Table structure for table `LogMAttrHistoryd`
|
224
|
--
|
225
|
|
226
|
CREATE TABLE IF NOT EXISTS `LogMAttrHistoryd` (
|
227
|
`LogMAttrHistoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
228
|
`MeasureID` int(10) unsigned NOT NULL,
|
229
|
`CensusID` int(10) unsigned NOT NULL,
|
230
|
`TSMID` int(10) unsigned NOT NULL,
|
231
|
`DateOfChange` date DEFAULT NULL,
|
232
|
`DescriptionOfChange` varchar(128) DEFAULT NULL,
|
233
|
PRIMARY KEY (`LogMAttrHistoryID`),
|
234
|
KEY `Ref21225` (`MeasureID`,`CensusID`,`TSMID`)
|
235
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
|
236
|
|
237
|
-- --------------------------------------------------------
|
238
|
|
239
|
--
|
240
|
-- Table structure for table `LogMeasurementHistory`
|
241
|
--
|
242
|
|
243
|
CREATE TABLE IF NOT EXISTS `LogMeasurementHistory` (
|
244
|
`MeasureID` int(10) unsigned NOT NULL,
|
245
|
`CensusID` int(10) unsigned NOT NULL,
|
246
|
`MeasurementHistoryID` int(10) unsigned NOT NULL,
|
247
|
`StemID` int(10) unsigned DEFAULT NULL,
|
248
|
`TreeID` int(10) unsigned DEFAULT NULL,
|
249
|
`DateOfChange` date NOT NULL,
|
250
|
`DescriptionOfChange` varchar(128) DEFAULT NULL,
|
251
|
`DBH` float DEFAULT NULL,
|
252
|
`HOM` float DEFAULT NULL,
|
253
|
`ExactDate` date DEFAULT NULL,
|
254
|
PRIMARY KEY (`MeasureID`,`CensusID`,`MeasurementHistoryID`),
|
255
|
KEY `Ref287` (`MeasureID`,`CensusID`)
|
256
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
257
|
|
258
|
-- --------------------------------------------------------
|
259
|
|
260
|
--
|
261
|
-- Table structure for table `LogMeasurementHistoryd`
|
262
|
--
|
263
|
|
264
|
CREATE TABLE IF NOT EXISTS `LogMeasurementHistoryd` (
|
265
|
`MeasureID` int(10) unsigned NOT NULL,
|
266
|
`CensusID` int(10) unsigned NOT NULL,
|
267
|
`MeasurementHistoryID` int(10) unsigned NOT NULL,
|
268
|
`StemID` int(10) unsigned DEFAULT NULL,
|
269
|
`TreeID` int(10) unsigned DEFAULT NULL,
|
270
|
`DateOfChange` date NOT NULL,
|
271
|
`DescriptionOfChange` varchar(128) DEFAULT NULL,
|
272
|
`DBH` float DEFAULT NULL,
|
273
|
`HOM` float DEFAULT NULL,
|
274
|
`ExactDate` date DEFAULT NULL,
|
275
|
PRIMARY KEY (`MeasureID`,`CensusID`,`MeasurementHistoryID`),
|
276
|
KEY `Ref287` (`MeasureID`,`CensusID`)
|
277
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
278
|
|
279
|
-- --------------------------------------------------------
|
280
|
|
281
|
--
|
282
|
-- Table structure for table `LogSpeciesInventory`
|
283
|
--
|
284
|
|
285
|
CREATE TABLE IF NOT EXISTS `LogSpeciesInventory` (
|
286
|
`SpeciesInvHistID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
287
|
`SpeciesInvID` int(10) unsigned NOT NULL,
|
288
|
`CensusID` int(10) unsigned NOT NULL,
|
289
|
`PlotID` int(10) unsigned NOT NULL,
|
290
|
`SpeciesID` int(10) unsigned NOT NULL,
|
291
|
`SubSpeciesID` int(10) unsigned DEFAULT NULL,
|
292
|
`DateOfChange` date DEFAULT NULL,
|
293
|
`DescriptionOfChange` varchar(128) DEFAULT NULL,
|
294
|
PRIMARY KEY (`SpeciesInvHistID`)
|
295
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=47 ;
|
296
|
|
297
|
-- --------------------------------------------------------
|
298
|
|
299
|
--
|
300
|
-- Table structure for table `LogTreeAttrHistory`
|
301
|
--
|
302
|
|
303
|
CREATE TABLE IF NOT EXISTS `LogTreeAttrHistory` (
|
304
|
`LogTAttrHistoryID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
305
|
`CensusID` int(10) unsigned NOT NULL,
|
306
|
`TreeID` int(10) unsigned NOT NULL,
|
307
|
`TSMID` int(10) unsigned NOT NULL,
|
308
|
`DateOfChange` date DEFAULT NULL,
|
309
|
`DescriptionOfChange` varchar(128) DEFAULT NULL,
|
310
|
PRIMARY KEY (`LogTAttrHistoryID`),
|
311
|
KEY `Ref24226` (`CensusID`,`TreeID`,`TSMID`)
|
312
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
|
313
|
|
314
|
-- --------------------------------------------------------
|
315
|
|
316
|
--
|
317
|
-- Table structure for table `LogTreeHistory`
|
318
|
--
|
319
|
|
320
|
CREATE TABLE IF NOT EXISTS `LogTreeHistory` (
|
321
|
`TreeID` int(10) unsigned NOT NULL,
|
322
|
`TreeHistoryID` int(10) unsigned NOT NULL,
|
323
|
`ChangeCodeID` int(10) unsigned DEFAULT NULL,
|
324
|
`ChangeDate` date DEFAULT NULL,
|
325
|
`ChangeDescription` varchar(128) DEFAULT NULL,
|
326
|
`QuadratID` int(10) unsigned DEFAULT NULL,
|
327
|
`PlotID` int(10) unsigned DEFAULT NULL,
|
328
|
`Tag` char(10) DEFAULT NULL,
|
329
|
`X` float DEFAULT NULL,
|
330
|
`Y` float DEFAULT NULL,
|
331
|
`SpeciesID` int(10) unsigned DEFAULT NULL,
|
332
|
`SubSpeciesID` int(10) unsigned DEFAULT NULL,
|
333
|
PRIMARY KEY (`TreeID`,`TreeHistoryID`),
|
334
|
KEY `Ref186` (`TreeID`),
|
335
|
KEY `Ref32221` (`ChangeCodeID`)
|
336
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
337
|
|
338
|
-- --------------------------------------------------------
|
339
|
|
340
|
--
|
341
|
-- Table structure for table `LogTreeHistoryd`
|
342
|
--
|
343
|
|
344
|
CREATE TABLE IF NOT EXISTS `LogTreeHistoryd` (
|
345
|
`TreeID` int(10) unsigned NOT NULL,
|
346
|
`TreeHistoryID` int(10) unsigned NOT NULL,
|
347
|
`ChangeCodeID` int(10) unsigned DEFAULT NULL,
|
348
|
`ChangeDate` date DEFAULT NULL,
|
349
|
`ChangeDescription` varchar(128) DEFAULT NULL,
|
350
|
`QuadratID` int(10) unsigned DEFAULT NULL,
|
351
|
`PlotID` int(10) unsigned DEFAULT NULL,
|
352
|
`Tag` char(10) DEFAULT NULL,
|
353
|
`X` float DEFAULT NULL,
|
354
|
`Y` float DEFAULT NULL,
|
355
|
`SpeciesID` int(10) unsigned DEFAULT NULL,
|
356
|
`SubSpeciesID` int(10) unsigned DEFAULT NULL,
|
357
|
PRIMARY KEY (`TreeID`,`TreeHistoryID`),
|
358
|
KEY `Ref186` (`TreeID`),
|
359
|
KEY `Ref32221` (`ChangeCodeID`)
|
360
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
361
|
|
362
|
-- --------------------------------------------------------
|
363
|
|
364
|
--
|
365
|
-- Table structure for table `Measurement`
|
366
|
--
|
367
|
|
368
|
CREATE TABLE IF NOT EXISTS `Measurement` (
|
369
|
`MeasureID` int(10) unsigned NOT NULL,
|
370
|
`CensusID` int(10) unsigned NOT NULL,
|
371
|
`StemID` int(10) unsigned NOT NULL,
|
372
|
`TreeID` int(10) unsigned NOT NULL,
|
373
|
`DBH` float DEFAULT NULL,
|
374
|
`HOM` float DEFAULT NULL,
|
375
|
`PrimaryStem` varchar(20) DEFAULT NULL,
|
376
|
`ExactDate` date DEFAULT NULL,
|
377
|
PRIMARY KEY (`MeasureID`,`CensusID`),
|
378
|
KEY `Ref549` (`CensusID`),
|
379
|
KEY `Ref1951` (`StemID`,`TreeID`),
|
380
|
KEY `TreeID` (`TreeID`)
|
381
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
382
|
|
383
|
-- --------------------------------------------------------
|
384
|
|
385
|
--
|
386
|
-- Table structure for table `MeasurementAttributes`
|
387
|
--
|
388
|
|
389
|
CREATE TABLE IF NOT EXISTS `MeasurementAttributes` (
|
390
|
`MeasureID` int(10) unsigned NOT NULL,
|
391
|
`CensusID` int(10) unsigned NOT NULL,
|
392
|
`TSMID` int(10) unsigned NOT NULL,
|
393
|
PRIMARY KEY (`MeasureID`,`CensusID`,`TSMID`),
|
394
|
KEY `Ref252` (`MeasureID`,`CensusID`),
|
395
|
KEY `Ref2053` (`TSMID`)
|
396
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
397
|
|
398
|
-- --------------------------------------------------------
|
399
|
|
400
|
--
|
401
|
-- Table structure for table `Personnel`
|
402
|
--
|
403
|
|
404
|
CREATE TABLE IF NOT EXISTS `Personnel` (
|
405
|
`PersonnelID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
|
406
|
`FirstName` varchar(32) DEFAULT NULL,
|
407
|
`LastName` varchar(32) NOT NULL,
|
408
|
PRIMARY KEY (`PersonnelID`)
|
409
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=95 ;
|
410
|
|
411
|
-- --------------------------------------------------------
|
412
|
|
413
|
--
|
414
|
-- Table structure for table `Quadrat`
|
415
|
--
|
416
|
|
417
|
CREATE TABLE IF NOT EXISTS `Quadrat` (
|
418
|
`QuadratID` int(10) unsigned NOT NULL,
|
419
|
`PlotID` int(10) unsigned NOT NULL,
|
420
|
`QuadratName` char(8) DEFAULT NULL,
|
421
|
`StartX` float DEFAULT NULL,
|
422
|
`StartY` float DEFAULT NULL,
|
423
|
`DimX` float DEFAULT NULL,
|
424
|
`DimY` float DEFAULT NULL,
|
425
|
PRIMARY KEY (`QuadratID`,`PlotID`),
|
426
|
KEY `Ref69` (`PlotID`),
|
427
|
KEY `QuadratName` (`QuadratName`,`PlotID`)
|
428
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
429
|
|
430
|
-- --------------------------------------------------------
|
431
|
|
432
|
--
|
433
|
-- Table structure for table `Reference`
|
434
|
--
|
435
|
|
436
|
CREATE TABLE IF NOT EXISTS `Reference` (
|
437
|
`ReferenceID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
|
438
|
`PublicationTitle` varchar(64) DEFAULT NULL,
|
439
|
`FullReference` varchar(256) DEFAULT NULL,
|
440
|
`DateofPublication` date DEFAULT NULL,
|
441
|
PRIMARY KEY (`ReferenceID`)
|
442
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
|
443
|
|
444
|
-- --------------------------------------------------------
|
445
|
|
446
|
--
|
447
|
-- Table structure for table `RemeasAttribs`
|
448
|
--
|
449
|
|
450
|
CREATE TABLE IF NOT EXISTS `RemeasAttribs` (
|
451
|
`RemeasureID` int(10) unsigned NOT NULL,
|
452
|
`CensusID` int(10) unsigned NOT NULL,
|
453
|
`TSMID` int(10) unsigned NOT NULL,
|
454
|
PRIMARY KEY (`RemeasureID`,`CensusID`,`TSMID`),
|
455
|
KEY `Ref2073` (`TSMID`),
|
456
|
KEY `Ref2274` (`RemeasureID`,`CensusID`)
|
457
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
458
|
|
459
|
-- --------------------------------------------------------
|
460
|
|
461
|
--
|
462
|
-- Table structure for table `Remeasurement`
|
463
|
--
|
464
|
|
465
|
CREATE TABLE IF NOT EXISTS `Remeasurement` (
|
466
|
`RemeasureID` int(10) unsigned NOT NULL,
|
467
|
`CensusID` int(10) unsigned NOT NULL,
|
468
|
`StemID` int(10) unsigned NOT NULL,
|
469
|
`TreeID` int(10) unsigned NOT NULL,
|
470
|
`DBH` float DEFAULT NULL,
|
471
|
`HOM` float DEFAULT NULL,
|
472
|
`ExactDate` date DEFAULT NULL,
|
473
|
PRIMARY KEY (`RemeasureID`,`CensusID`),
|
474
|
KEY `Ref1957` (`StemID`,`TreeID`),
|
475
|
KEY `Ref5106` (`CensusID`),
|
476
|
KEY `RefTree57` (`TreeID`)
|
477
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
478
|
|
479
|
-- --------------------------------------------------------
|
480
|
|
481
|
--
|
482
|
-- Table structure for table `ReviewChange`
|
483
|
--
|
484
|
|
485
|
CREATE TABLE IF NOT EXISTS `ReviewChange` (
|
486
|
`RID` int(4) unsigned NOT NULL,
|
487
|
`TreeID` int(10) unsigned NOT NULL,
|
488
|
`QuadratID` int(10) unsigned NOT NULL,
|
489
|
`PlotID` int(10) unsigned NOT NULL,
|
490
|
`FmSpeciesID` int(10) unsigned NOT NULL,
|
491
|
`ToSpeciesID` int(10) unsigned NOT NULL,
|
492
|
`ChangeCodeID` int(10) unsigned NOT NULL,
|
493
|
`Tag` char(10) DEFAULT NULL,
|
494
|
PRIMARY KEY (`RID`)
|
495
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
496
|
|
497
|
-- --------------------------------------------------------
|
498
|
|
499
|
--
|
500
|
-- Table structure for table `ReviewNewSpecies`
|
501
|
--
|
502
|
|
503
|
CREATE TABLE IF NOT EXISTS `ReviewNewSpecies` (
|
504
|
`SpeciesID` int(10) unsigned NOT NULL,
|
505
|
`genusID` int(10) unsigned NOT NULL,
|
506
|
`ReferenceID` smallint(5) unsigned DEFAULT NULL,
|
507
|
`FullSpeciesName` char(128) DEFAULT NULL,
|
508
|
`Authority` varchar(128) DEFAULT NULL,
|
509
|
`IDLevel` char(8) DEFAULT NULL,
|
510
|
`FieldFamily` char(32) DEFAULT NULL,
|
511
|
`Description` varchar(128) DEFAULT NULL,
|
512
|
`PublicationTitle` varchar(128) DEFAULT NULL,
|
513
|
`FullReference` varchar(256) DEFAULT NULL,
|
514
|
`DateOfPublication` date DEFAULT NULL,
|
515
|
PRIMARY KEY (`SpeciesID`)
|
516
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
517
|
|
518
|
-- --------------------------------------------------------
|
519
|
|
520
|
--
|
521
|
-- Table structure for table `RoleReference`
|
522
|
--
|
523
|
|
524
|
CREATE TABLE IF NOT EXISTS `RoleReference` (
|
525
|
`RoleID` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
|
526
|
`Description` varchar(128) DEFAULT NULL,
|
527
|
PRIMARY KEY (`RoleID`)
|
528
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;
|
529
|
|
530
|
-- --------------------------------------------------------
|
531
|
|
532
|
--
|
533
|
-- Table structure for table `Site`
|
534
|
--
|
535
|
|
536
|
CREATE TABLE IF NOT EXISTS `Site` (
|
537
|
`PlotID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
538
|
`PlotName` char(64) DEFAULT NULL,
|
539
|
`LocationName` varchar(128) DEFAULT NULL,
|
540
|
`CountryID` smallint(5) unsigned NOT NULL,
|
541
|
`Latitude` float DEFAULT NULL,
|
542
|
`Longitude` float DEFAULT NULL,
|
543
|
`Elevation` float DEFAULT NULL,
|
544
|
`ReferenceX` float DEFAULT NULL,
|
545
|
`ReferenceY` float DEFAULT NULL,
|
546
|
`SizeOfSite` char(32) DEFAULT NULL,
|
547
|
`ShapeOfSite` char(32) DEFAULT NULL,
|
548
|
`DescriptionOfSite` varchar(128) DEFAULT NULL,
|
549
|
PRIMARY KEY (`PlotID`),
|
550
|
KEY `Ref87173` (`CountryID`)
|
551
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=158 ;
|
552
|
|
553
|
-- --------------------------------------------------------
|
554
|
|
555
|
--
|
556
|
-- Table structure for table `Species`
|
557
|
--
|
558
|
|
559
|
CREATE TABLE IF NOT EXISTS `Species` (
|
560
|
`SpeciesID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
561
|
`CurrentTaxonFlag` smallint(6) DEFAULT NULL,
|
562
|
`ObsoleteTaxonFlag` smallint(6) DEFAULT NULL,
|
563
|
`GenusID` int(10) unsigned NOT NULL,
|
564
|
`ReferenceID` smallint(5) unsigned DEFAULT NULL,
|
565
|
`SpeciesName` char(64) DEFAULT NULL,
|
566
|
`Mnemonic` char(10) DEFAULT NULL,
|
567
|
`Authority` varchar(128) DEFAULT NULL,
|
568
|
`IDLevel` char(8) DEFAULT NULL,
|
569
|
`FieldFamily` char(32) DEFAULT NULL,
|
570
|
`Description` varchar(128) DEFAULT NULL,
|
571
|
PRIMARY KEY (`SpeciesID`),
|
572
|
KEY `Ref26208` (`GenusID`),
|
573
|
KEY `Ref84209` (`ReferenceID`),
|
574
|
KEY `Mnemonic` (`Mnemonic`)
|
575
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1473 ;
|
576
|
|
577
|
-- --------------------------------------------------------
|
578
|
|
579
|
--
|
580
|
-- Table structure for table `SpeciesInventory`
|
581
|
--
|
582
|
|
583
|
CREATE TABLE IF NOT EXISTS `SpeciesInventory` (
|
584
|
`SpeciesInvID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
585
|
`CensusID` int(10) unsigned NOT NULL,
|
586
|
`PlotID` int(10) unsigned NOT NULL,
|
587
|
`SpeciesID` int(10) unsigned NOT NULL,
|
588
|
`SubSpeciesID` int(10) unsigned DEFAULT NULL,
|
589
|
PRIMARY KEY (`SpeciesInvID`),
|
590
|
KEY `Ref92198` (`SpeciesID`),
|
591
|
KEY `Ref93199` (`SubSpeciesID`),
|
592
|
KEY `Ref5222` (`CensusID`),
|
593
|
KEY `Ref642` (`PlotID`)
|
594
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10226 ;
|
595
|
|
596
|
-- --------------------------------------------------------
|
597
|
|
598
|
--
|
599
|
-- Table structure for table `Specimen`
|
600
|
--
|
601
|
|
602
|
CREATE TABLE IF NOT EXISTS `Specimen` (
|
603
|
`SpecimenID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
604
|
`TreeID` int(10) unsigned NOT NULL,
|
605
|
`Collector` char(64) DEFAULT NULL,
|
606
|
`SpecimenNumber` int(10) unsigned DEFAULT NULL,
|
607
|
`SpeciesID` int(10) unsigned NOT NULL,
|
608
|
`SubSpeciesID` int(10) unsigned DEFAULT NULL,
|
609
|
`Herbarium` char(32) DEFAULT NULL,
|
610
|
`Voucher` smallint(5) unsigned DEFAULT NULL,
|
611
|
`CollectionDate` date DEFAULT NULL,
|
612
|
`DeterminedBy` char(64) DEFAULT NULL,
|
613
|
`Description` varchar(128) DEFAULT NULL,
|
614
|
PRIMARY KEY (`SpecimenID`),
|
615
|
KEY `Ref93194` (`SubSpeciesID`),
|
616
|
KEY `Ref92196` (`SpeciesID`),
|
617
|
KEY `Ref1171` (`TreeID`)
|
618
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
|
619
|
|
620
|
-- --------------------------------------------------------
|
621
|
|
622
|
--
|
623
|
-- Table structure for table `SqlLog`
|
624
|
--
|
625
|
|
626
|
CREATE TABLE IF NOT EXISTS `SqlLog` (
|
627
|
`SqlID` int(4) NOT NULL DEFAULT '0',
|
628
|
`ToTableName` varchar(23) DEFAULT NULL,
|
629
|
`SqlStmt` varchar(16384) DEFAULT NULL,
|
630
|
PRIMARY KEY (`SqlID`)
|
631
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
632
|
|
633
|
-- --------------------------------------------------------
|
634
|
|
635
|
--
|
636
|
-- Table structure for table `StageSource`
|
637
|
--
|
638
|
|
639
|
CREATE TABLE IF NOT EXISTS `StageSource` (
|
640
|
`ChangeID` int(10) unsigned NOT NULL,
|
641
|
`SourceNo` int(10) unsigned NOT NULL,
|
642
|
`SpeciesID` int(10) unsigned NOT NULL,
|
643
|
PRIMARY KEY (`ChangeID`,`SourceNo`),
|
644
|
KEY `Ref96202` (`ChangeID`),
|
645
|
KEY `Ref92214` (`SpeciesID`)
|
646
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
647
|
|
648
|
-- --------------------------------------------------------
|
649
|
|
650
|
--
|
651
|
-- Table structure for table `StageTarget`
|
652
|
--
|
653
|
|
654
|
CREATE TABLE IF NOT EXISTS `StageTarget` (
|
655
|
`ChangeID` int(10) unsigned NOT NULL,
|
656
|
`TargetNo` int(10) unsigned NOT NULL,
|
657
|
`SpeciesID` int(10) unsigned NOT NULL,
|
658
|
PRIMARY KEY (`ChangeID`,`TargetNo`),
|
659
|
KEY `Ref96203` (`ChangeID`),
|
660
|
KEY `Ref92215` (`SpeciesID`)
|
661
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
662
|
|
663
|
-- --------------------------------------------------------
|
664
|
|
665
|
--
|
666
|
-- Table structure for table `StageTargetNew`
|
667
|
--
|
668
|
|
669
|
CREATE TABLE IF NOT EXISTS `StageTargetNew` (
|
670
|
`ChangeID` int(10) unsigned NOT NULL,
|
671
|
`TargetNo` int(10) unsigned NOT NULL,
|
672
|
`SpeciesID` int(10) unsigned NOT NULL,
|
673
|
`CurrentTaxonFlag` smallint(6) DEFAULT NULL,
|
674
|
`ObsoleteTaxonFlag` smallint(6) DEFAULT NULL,
|
675
|
`GenusID` int(10) unsigned NOT NULL,
|
676
|
`ReferenceID` smallint(5) unsigned NOT NULL,
|
677
|
`SpeciesName` char(64) DEFAULT NULL,
|
678
|
`Mnemonic` char(10) DEFAULT NULL,
|
679
|
`Authority` varchar(128) DEFAULT NULL,
|
680
|
`IDLevel` char(8) DEFAULT NULL,
|
681
|
`FieldFamily` char(32) DEFAULT NULL,
|
682
|
`Description` char(64) DEFAULT NULL,
|
683
|
PRIMARY KEY (`ChangeID`,`TargetNo`),
|
684
|
KEY `Ref96223` (`ChangeID`)
|
685
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
686
|
|
687
|
-- --------------------------------------------------------
|
688
|
|
689
|
--
|
690
|
-- Table structure for table `StageTaxonomicChanges`
|
691
|
--
|
692
|
|
693
|
CREATE TABLE IF NOT EXISTS `StageTaxonomicChanges` (
|
694
|
`ChangeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
695
|
`ChangeCodeID` int(10) unsigned NOT NULL,
|
696
|
`PersonnelID` smallint(5) unsigned NOT NULL,
|
697
|
`ChangeDate` date DEFAULT NULL,
|
698
|
`NumberOfSource` int(10) unsigned DEFAULT NULL,
|
699
|
`NumberOfTarget` int(10) unsigned DEFAULT NULL,
|
700
|
`NumberOfTargetNew` int(10) unsigned DEFAULT NULL,
|
701
|
PRIMARY KEY (`ChangeID`),
|
702
|
KEY `Ref32216` (`ChangeCodeID`),
|
703
|
KEY `Ref15224` (`PersonnelID`)
|
704
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
|
705
|
|
706
|
-- --------------------------------------------------------
|
707
|
|
708
|
--
|
709
|
-- Table structure for table `Stem`
|
710
|
--
|
711
|
|
712
|
CREATE TABLE IF NOT EXISTS `Stem` (
|
713
|
`StemID` int(10) unsigned NOT NULL,
|
714
|
`TreeID` int(10) unsigned NOT NULL,
|
715
|
`StemTag` varchar(32) DEFAULT NULL,
|
716
|
`StemDescription` varchar(128) DEFAULT NULL,
|
717
|
PRIMARY KEY (`StemID`,`TreeID`),
|
718
|
KEY `Ref150` (`TreeID`)
|
719
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
720
|
|
721
|
-- --------------------------------------------------------
|
722
|
|
723
|
--
|
724
|
-- Table structure for table `SubSpecies`
|
725
|
--
|
726
|
|
727
|
CREATE TABLE IF NOT EXISTS `SubSpecies` (
|
728
|
`SubSpeciesID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
729
|
`SpeciesID` int(10) unsigned NOT NULL,
|
730
|
`CurrentTaxonFlag` smallint(6) DEFAULT NULL,
|
731
|
`ObsoleteTaxonFlag` smallint(6) DEFAULT NULL,
|
732
|
`SubSpeciesName` char(64) DEFAULT NULL,
|
733
|
`Mnemonic` char(10) DEFAULT NULL,
|
734
|
`Authority` varchar(128) DEFAULT NULL,
|
735
|
`InfraSpecificLevel` char(32) DEFAULT NULL,
|
736
|
PRIMARY KEY (`SubSpeciesID`),
|
737
|
KEY `Ref92193` (`SpeciesID`)
|
738
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=13 ;
|
739
|
|
740
|
-- --------------------------------------------------------
|
741
|
|
742
|
--
|
743
|
-- Table structure for table `TAX1temp`
|
744
|
--
|
745
|
|
746
|
CREATE TABLE IF NOT EXISTS `TAX1temp` (
|
747
|
`SpeciesID` int(11) NOT NULL,
|
748
|
`SubspeciesID` int(11) NOT NULL DEFAULT '0',
|
749
|
`Family` char(32) DEFAULT NULL,
|
750
|
`Genus` char(32) DEFAULT NULL,
|
751
|
`Mnemonic` char(10) DEFAULT NULL,
|
752
|
`SpeciesName` char(64) DEFAULT NULL,
|
753
|
`SubspeciesName` char(64) DEFAULT NULL,
|
754
|
`IDLevel` char(8) DEFAULT NULL,
|
755
|
`Authority` char(124) DEFAULT NULL,
|
756
|
`ListOfOldNames` varchar(255) DEFAULT NULL,
|
757
|
`NumberOfHerbarium` int(11) DEFAULT NULL,
|
758
|
`ListOfHerbarium` varchar(255) DEFAULT NULL,
|
759
|
`Description` varchar(128) DEFAULT NULL,
|
760
|
PRIMARY KEY (`SpeciesID`,`SubspeciesID`)
|
761
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
|
762
|
|
763
|
-- --------------------------------------------------------
|
764
|
|
765
|
--
|
766
|
-- Table structure for table `TAX2temp`
|
767
|
--
|
768
|
|
769
|
CREATE TABLE IF NOT EXISTS `TAX2temp` (
|
770
|
`SpeciesID` int(11) NOT NULL,
|
771
|
`ObsoleteSpeciesID` int(11) NOT NULL,
|
772
|
`ObsoleteGenusName` char(32) DEFAULT NULL,
|
773
|
`ObsoleteSpeciesName` char(64) DEFAULT NULL,
|
774
|
`ObsoleteGenSpeName` char(128) DEFAULT NULL,
|
775
|
`Description` char(128) DEFAULT NULL,
|
776
|
`ChangeDate` date NOT NULL DEFAULT '0000-00-00',
|
777
|
`Family` char(32) DEFAULT NULL,
|
778
|
`Genus` char(32) DEFAULT NULL,
|
779
|
`SpeciesName` char(64) DEFAULT NULL,
|
780
|
`Authority` char(128) DEFAULT NULL,
|
781
|
`IDLevel` char(8) DEFAULT NULL,
|
782
|
PRIMARY KEY (`SpeciesID`,`ObsoleteSpeciesID`,`ChangeDate`)
|
783
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
|
784
|
|
785
|
-- --------------------------------------------------------
|
786
|
|
787
|
--
|
788
|
-- Table structure for table `TAX3temp`
|
789
|
--
|
790
|
|
791
|
CREATE TABLE IF NOT EXISTS `TAX3temp` (
|
792
|
`PlotSpeciesID` int(11) NOT NULL AUTO_INCREMENT,
|
793
|
`PlotID` int(11) NOT NULL,
|
794
|
`SpeciesID` int(11) NOT NULL,
|
795
|
`SubSpeciesID` int(11) DEFAULT NULL,
|
796
|
PRIMARY KEY (`PlotSpeciesID`),
|
797
|
KEY `TAX3Plot` (`PlotID`,`SpeciesID`,`SubSpeciesID`)
|
798
|
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=17167 ;
|
799
|
|
800
|
-- --------------------------------------------------------
|
801
|
|
802
|
--
|
803
|
-- Table structure for table `Tree`
|
804
|
--
|
805
|
|
806
|
CREATE TABLE IF NOT EXISTS `Tree` (
|
807
|
`TreeID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
808
|
`QuadratID` int(10) unsigned NOT NULL,
|
809
|
`PlotID` int(10) unsigned NOT NULL,
|
810
|
`Tag` char(10) DEFAULT NULL,
|
811
|
`x` float DEFAULT NULL,
|
812
|
`y` float DEFAULT NULL,
|
813
|
`SpeciesID` int(10) unsigned NOT NULL,
|
814
|
`SubSpeciesID` int(10) unsigned DEFAULT NULL,
|
815
|
PRIMARY KEY (`TreeID`),
|
816
|
KEY `Ref92217` (`SpeciesID`),
|
817
|
KEY `Ref93219` (`SubSpeciesID`),
|
818
|
KEY `Ref735` (`QuadratID`,`PlotID`),
|
819
|
KEY `Tag` (`Tag`,`PlotID`)
|
820
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=482149 ;
|
821
|
|
822
|
-- --------------------------------------------------------
|
823
|
|
824
|
--
|
825
|
-- Table structure for table `TreeAttributes`
|
826
|
--
|
827
|
|
828
|
CREATE TABLE IF NOT EXISTS `TreeAttributes` (
|
829
|
`CensusID` int(10) unsigned NOT NULL,
|
830
|
`TreeID` int(10) unsigned NOT NULL,
|
831
|
`TSMID` int(10) unsigned NOT NULL,
|
832
|
PRIMARY KEY (`CensusID`,`TreeID`,`TSMID`),
|
833
|
KEY `Ref163` (`TreeID`),
|
834
|
KEY `Ref2064` (`TSMID`),
|
835
|
KEY `Ref5107` (`CensusID`)
|
836
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
837
|
|
838
|
-- --------------------------------------------------------
|
839
|
|
840
|
--
|
841
|
-- Table structure for table `TreeTaxChange`
|
842
|
--
|
843
|
|
844
|
CREATE TABLE IF NOT EXISTS `TreeTaxChange` (
|
845
|
`ChangeCodeID` int(10) unsigned NOT NULL,
|
846
|
`Description` varchar(128) DEFAULT NULL,
|
847
|
PRIMARY KEY (`ChangeCodeID`)
|
848
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
|
849
|
|
850
|
-- --------------------------------------------------------
|
851
|
|
852
|
--
|
853
|
-- Table structure for table `TSMAttributes`
|
854
|
--
|
855
|
|
856
|
CREATE TABLE IF NOT EXISTS `TSMAttributes` (
|
857
|
`TSMID` int(10) unsigned NOT NULL AUTO_INCREMENT,
|
858
|
`TSMCode` char(10) NOT NULL,
|
859
|
`Description` varchar(128) NOT NULL,
|
860
|
PRIMARY KEY (`TSMID`)
|
861
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=60 ;
|
862
|
|
863
|
--
|
864
|
-- Constraints for dumped tables
|
865
|
--
|
866
|
|
867
|
--
|
868
|
-- Constraints for table `Census`
|
869
|
--
|
870
|
ALTER TABLE `Census`
|
871
|
ADD CONSTRAINT `Census_ibfk_1` FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`),
|
872
|
ADD CONSTRAINT `Census_ibfk_2` FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`);
|
873
|
|
874
|
--
|
875
|
-- Constraints for table `CensusQuadrat`
|
876
|
--
|
877
|
ALTER TABLE `CensusQuadrat`
|
878
|
ADD CONSTRAINT `CensusQuadrat_ibfk_1` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
879
|
ADD CONSTRAINT `CensusQuadrat_ibfk_2` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
880
|
ADD CONSTRAINT `CensusQuadrat_ibfk_3` FOREIGN KEY (`QuadratID`, `PlotID`) REFERENCES `Quadrat` (`QuadratID`, `PlotID`),
|
881
|
ADD CONSTRAINT `CensusQuadrat_ibfk_4` FOREIGN KEY (`QuadratID`, `PlotID`) REFERENCES `Quadrat` (`QuadratID`, `PlotID`);
|
882
|
|
883
|
--
|
884
|
-- Constraints for table `CurrentObsolete`
|
885
|
--
|
886
|
ALTER TABLE `CurrentObsolete`
|
887
|
ADD CONSTRAINT `CurrentObsolete_ibfk_1` FOREIGN KEY (`ObsoleteSpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
888
|
ADD CONSTRAINT `CurrentObsolete_ibfk_2` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
889
|
ADD CONSTRAINT `CurrentObsolete_ibfk_3` FOREIGN KEY (`ObsoleteSpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
890
|
ADD CONSTRAINT `CurrentObsolete_ibfk_4` FOREIGN KEY (`ChangeCodeID`) REFERENCES `TreeTaxChange` (`ChangeCodeID`),
|
891
|
ADD CONSTRAINT `CurrentObsolete_ibfk_5` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
892
|
ADD CONSTRAINT `CurrentObsolete_ibfk_6` FOREIGN KEY (`ChangeCodeID`) REFERENCES `TreeTaxChange` (`ChangeCodeID`);
|
893
|
|
894
|
--
|
895
|
-- Constraints for table `DataCollection`
|
896
|
--
|
897
|
ALTER TABLE `DataCollection`
|
898
|
ADD CONSTRAINT `DataCollection_ibfk_1` FOREIGN KEY (`RoleID`) REFERENCES `RoleReference` (`RoleID`),
|
899
|
ADD CONSTRAINT `DataCollection_ibfk_2` FOREIGN KEY (`CensusID`, `QuadratID`, `PlotID`) REFERENCES `CensusQuadrat` (`CensusID`, `QuadratID`, `PlotID`),
|
900
|
ADD CONSTRAINT `DataCollection_ibfk_3` FOREIGN KEY (`PersonnelID`) REFERENCES `Personnel` (`PersonnelID`),
|
901
|
ADD CONSTRAINT `DataCollection_ibfk_4` FOREIGN KEY (`RoleID`) REFERENCES `RoleReference` (`RoleID`),
|
902
|
ADD CONSTRAINT `DataCollection_ibfk_5` FOREIGN KEY (`CensusID`, `QuadratID`, `PlotID`) REFERENCES `CensusQuadrat` (`CensusID`, `QuadratID`, `PlotID`),
|
903
|
ADD CONSTRAINT `DataCollection_ibfk_6` FOREIGN KEY (`PersonnelID`) REFERENCES `Personnel` (`PersonnelID`);
|
904
|
|
905
|
--
|
906
|
-- Constraints for table `DataEntry`
|
907
|
--
|
908
|
ALTER TABLE `DataEntry`
|
909
|
ADD CONSTRAINT `DataEntry_ibfk_1` FOREIGN KEY (`CensusID`, `QuadratID`, `PlotID`) REFERENCES `CensusQuadrat` (`CensusID`, `QuadratID`, `PlotID`),
|
910
|
ADD CONSTRAINT `DataEntry_ibfk_2` FOREIGN KEY (`CensusID`, `QuadratID`, `PlotID`) REFERENCES `CensusQuadrat` (`CensusID`, `QuadratID`, `PlotID`),
|
911
|
ADD CONSTRAINT `DataEntry_ibfk_3` FOREIGN KEY (`PersonnelID`) REFERENCES `Personnel` (`PersonnelID`);
|
912
|
|
913
|
--
|
914
|
-- Constraints for table `Family`
|
915
|
--
|
916
|
ALTER TABLE `Family`
|
917
|
ADD CONSTRAINT `Family_ibfk_1` FOREIGN KEY (`ReferenceID`) REFERENCES `Reference` (`ReferenceID`);
|
918
|
|
919
|
--
|
920
|
-- Constraints for table `Genus`
|
921
|
--
|
922
|
ALTER TABLE `Genus`
|
923
|
ADD CONSTRAINT `Genus_ibfk_1` FOREIGN KEY (`FamilyID`) REFERENCES `Family` (`FamilyID`),
|
924
|
ADD CONSTRAINT `Genus_ibfk_2` FOREIGN KEY (`FamilyID`) REFERENCES `Family` (`FamilyID`),
|
925
|
ADD CONSTRAINT `Genus_ibfk_3` FOREIGN KEY (`ReferenceID`) REFERENCES `Reference` (`ReferenceID`),
|
926
|
ADD CONSTRAINT `Genus_ibfk_4` FOREIGN KEY (`ReferenceID`) REFERENCES `Reference` (`ReferenceID`);
|
927
|
|
928
|
--
|
929
|
-- Constraints for table `LogMAttrHistory`
|
930
|
--
|
931
|
ALTER TABLE `LogMAttrHistory`
|
932
|
ADD CONSTRAINT `LogMAttrHistory_ibfk_1` FOREIGN KEY (`MeasureID`, `CensusID`, `TSMID`) REFERENCES `MeasurementAttributes` (`MeasureID`, `CensusID`, `TSMID`);
|
933
|
|
934
|
--
|
935
|
-- Constraints for table `LogMeasurementHistory`
|
936
|
--
|
937
|
ALTER TABLE `LogMeasurementHistory`
|
938
|
ADD CONSTRAINT `LogMeasurementHistory_ibfk_1` FOREIGN KEY (`MeasureID`, `CensusID`) REFERENCES `Measurement` (`MeasureID`, `CensusID`);
|
939
|
|
940
|
--
|
941
|
-- Constraints for table `LogTreeAttrHistory`
|
942
|
--
|
943
|
ALTER TABLE `LogTreeAttrHistory`
|
944
|
ADD CONSTRAINT `LogTreeAttrHistory_ibfk_1` FOREIGN KEY (`CensusID`, `TreeID`, `TSMID`) REFERENCES `TreeAttributes` (`CensusID`, `TreeID`, `TSMID`);
|
945
|
|
946
|
--
|
947
|
-- Constraints for table `LogTreeHistory`
|
948
|
--
|
949
|
ALTER TABLE `LogTreeHistory`
|
950
|
ADD CONSTRAINT `LogTreeHistory_ibfk_1` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`);
|
951
|
|
952
|
--
|
953
|
-- Constraints for table `Measurement`
|
954
|
--
|
955
|
ALTER TABLE `Measurement`
|
956
|
ADD CONSTRAINT `Measurement_ibfk_1` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
957
|
ADD CONSTRAINT `Measurement_ibfk_2` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
958
|
ADD CONSTRAINT `Measurement_ibfk_3` FOREIGN KEY (`StemID`, `TreeID`) REFERENCES `Stem` (`StemID`, `TreeID`),
|
959
|
ADD CONSTRAINT `Measurement_ibfk_4` FOREIGN KEY (`StemID`, `TreeID`) REFERENCES `Stem` (`StemID`, `TreeID`);
|
960
|
|
961
|
--
|
962
|
-- Constraints for table `MeasurementAttributes`
|
963
|
--
|
964
|
ALTER TABLE `MeasurementAttributes`
|
965
|
ADD CONSTRAINT `MeasurementAttributes_ibfk_1` FOREIGN KEY (`MeasureID`, `CensusID`) REFERENCES `Measurement` (`MeasureID`, `CensusID`),
|
966
|
ADD CONSTRAINT `MeasurementAttributes_ibfk_2` FOREIGN KEY (`MeasureID`, `CensusID`) REFERENCES `Measurement` (`MeasureID`, `CensusID`),
|
967
|
ADD CONSTRAINT `MeasurementAttributes_ibfk_3` FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`);
|
968
|
|
969
|
--
|
970
|
-- Constraints for table `Quadrat`
|
971
|
--
|
972
|
ALTER TABLE `Quadrat`
|
973
|
ADD CONSTRAINT `Quadrat_ibfk_1` FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`),
|
974
|
ADD CONSTRAINT `Quadrat_ibfk_2` FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`);
|
975
|
|
976
|
--
|
977
|
-- Constraints for table `RemeasAttribs`
|
978
|
--
|
979
|
ALTER TABLE `RemeasAttribs`
|
980
|
ADD CONSTRAINT `RemeasAttribs_ibfk_1` FOREIGN KEY (`RemeasureID`, `CensusID`) REFERENCES `Remeasurement` (`RemeasureID`, `CensusID`),
|
981
|
ADD CONSTRAINT `RemeasAttribs_ibfk_2` FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`),
|
982
|
ADD CONSTRAINT `RemeasAttribs_ibfk_3` FOREIGN KEY (`RemeasureID`, `CensusID`) REFERENCES `Remeasurement` (`RemeasureID`, `CensusID`),
|
983
|
ADD CONSTRAINT `RemeasAttribs_ibfk_4` FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`);
|
984
|
|
985
|
--
|
986
|
-- Constraints for table `Remeasurement`
|
987
|
--
|
988
|
ALTER TABLE `Remeasurement`
|
989
|
ADD CONSTRAINT `Remeasurement_ibfk_1` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
990
|
ADD CONSTRAINT `Remeasurement_ibfk_2` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),
|
991
|
ADD CONSTRAINT `Remeasurement_ibfk_3` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
992
|
ADD CONSTRAINT `Remeasurement_ibfk_4` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`);
|
993
|
|
994
|
--
|
995
|
-- Constraints for table `Site`
|
996
|
--
|
997
|
ALTER TABLE `Site`
|
998
|
ADD CONSTRAINT `Site_ibfk_1` FOREIGN KEY (`CountryID`) REFERENCES `Country` (`CountryID`),
|
999
|
ADD CONSTRAINT `Site_ibfk_2` FOREIGN KEY (`CountryID`) REFERENCES `Country` (`CountryID`);
|
1000
|
|
1001
|
--
|
1002
|
-- Constraints for table `Species`
|
1003
|
--
|
1004
|
ALTER TABLE `Species`
|
1005
|
ADD CONSTRAINT `Species_ibfk_1` FOREIGN KEY (`GenusID`) REFERENCES `Genus` (`GenusID`),
|
1006
|
ADD CONSTRAINT `Species_ibfk_2` FOREIGN KEY (`GenusID`) REFERENCES `Genus` (`GenusID`);
|
1007
|
|
1008
|
--
|
1009
|
-- Constraints for table `SpeciesInventory`
|
1010
|
--
|
1011
|
ALTER TABLE `SpeciesInventory`
|
1012
|
ADD CONSTRAINT `SpeciesInventory_ibfk_1` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
1013
|
ADD CONSTRAINT `SpeciesInventory_ibfk_2` FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`),
|
1014
|
ADD CONSTRAINT `SpeciesInventory_ibfk_3` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1015
|
ADD CONSTRAINT `SpeciesInventory_ibfk_4` FOREIGN KEY (`SubSpeciesID`) REFERENCES `SubSpecies` (`SubSpeciesID`),
|
1016
|
ADD CONSTRAINT `SpeciesInventory_ibfk_5` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
1017
|
ADD CONSTRAINT `SpeciesInventory_ibfk_6` FOREIGN KEY (`PlotID`) REFERENCES `Site` (`PlotID`),
|
1018
|
ADD CONSTRAINT `SpeciesInventory_ibfk_7` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1019
|
ADD CONSTRAINT `SpeciesInventory_ibfk_8` FOREIGN KEY (`SubSpeciesID`) REFERENCES `SubSpecies` (`SubSpeciesID`);
|
1020
|
|
1021
|
--
|
1022
|
-- Constraints for table `Specimen`
|
1023
|
--
|
1024
|
ALTER TABLE `Specimen`
|
1025
|
ADD CONSTRAINT `Specimen_ibfk_1` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1026
|
ADD CONSTRAINT `Specimen_ibfk_2` FOREIGN KEY (`SubSpeciesID`) REFERENCES `SubSpecies` (`SubSpeciesID`),
|
1027
|
ADD CONSTRAINT `Specimen_ibfk_3` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),
|
1028
|
ADD CONSTRAINT `Specimen_ibfk_4` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1029
|
ADD CONSTRAINT `Specimen_ibfk_5` FOREIGN KEY (`SubSpeciesID`) REFERENCES `SubSpecies` (`SubSpeciesID`),
|
1030
|
ADD CONSTRAINT `Specimen_ibfk_6` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`);
|
1031
|
|
1032
|
--
|
1033
|
-- Constraints for table `StageSource`
|
1034
|
--
|
1035
|
ALTER TABLE `StageSource`
|
1036
|
ADD CONSTRAINT `StageSource_ibfk_1` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1037
|
ADD CONSTRAINT `StageSource_ibfk_2` FOREIGN KEY (`ChangeID`) REFERENCES `StageTaxonomicChanges` (`ChangeID`);
|
1038
|
|
1039
|
--
|
1040
|
-- Constraints for table `StageTarget`
|
1041
|
--
|
1042
|
ALTER TABLE `StageTarget`
|
1043
|
ADD CONSTRAINT `StageTarget_ibfk_1` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1044
|
ADD CONSTRAINT `StageTarget_ibfk_2` FOREIGN KEY (`ChangeID`) REFERENCES `StageTaxonomicChanges` (`ChangeID`);
|
1045
|
|
1046
|
--
|
1047
|
-- Constraints for table `StageTargetNew`
|
1048
|
--
|
1049
|
ALTER TABLE `StageTargetNew`
|
1050
|
ADD CONSTRAINT `StageTargetNew_ibfk_1` FOREIGN KEY (`ChangeID`) REFERENCES `StageTaxonomicChanges` (`ChangeID`);
|
1051
|
|
1052
|
--
|
1053
|
-- Constraints for table `StageTaxonomicChanges`
|
1054
|
--
|
1055
|
ALTER TABLE `StageTaxonomicChanges`
|
1056
|
ADD CONSTRAINT `StageTaxonomicChanges_ibfk_1` FOREIGN KEY (`PersonnelID`) REFERENCES `Personnel` (`PersonnelID`),
|
1057
|
ADD CONSTRAINT `StageTaxonomicChanges_ibfk_2` FOREIGN KEY (`ChangeCodeID`) REFERENCES `TreeTaxChange` (`ChangeCodeID`);
|
1058
|
|
1059
|
--
|
1060
|
-- Constraints for table `Stem`
|
1061
|
--
|
1062
|
ALTER TABLE `Stem`
|
1063
|
ADD CONSTRAINT `Stem_ibfk_1` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),
|
1064
|
ADD CONSTRAINT `Stem_ibfk_2` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`);
|
1065
|
|
1066
|
--
|
1067
|
-- Constraints for table `SubSpecies`
|
1068
|
--
|
1069
|
ALTER TABLE `SubSpecies`
|
1070
|
ADD CONSTRAINT `SubSpecies_ibfk_1` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1071
|
ADD CONSTRAINT `SubSpecies_ibfk_2` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`);
|
1072
|
|
1073
|
--
|
1074
|
-- Constraints for table `Tree`
|
1075
|
--
|
1076
|
ALTER TABLE `Tree`
|
1077
|
ADD CONSTRAINT `Tree_ibfk_1` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1078
|
ADD CONSTRAINT `Tree_ibfk_2` FOREIGN KEY (`QuadratID`, `PlotID`) REFERENCES `Quadrat` (`QuadratID`, `PlotID`),
|
1079
|
ADD CONSTRAINT `Tree_ibfk_3` FOREIGN KEY (`SubSpeciesID`) REFERENCES `SubSpecies` (`SubSpeciesID`),
|
1080
|
ADD CONSTRAINT `Tree_ibfk_4` FOREIGN KEY (`QuadratID`, `PlotID`) REFERENCES `Quadrat` (`QuadratID`, `PlotID`),
|
1081
|
ADD CONSTRAINT `Tree_ibfk_5` FOREIGN KEY (`SpeciesID`) REFERENCES `Species` (`SpeciesID`),
|
1082
|
ADD CONSTRAINT `Tree_ibfk_6` FOREIGN KEY (`SubSpeciesID`) REFERENCES `SubSpecies` (`SubSpeciesID`);
|
1083
|
|
1084
|
--
|
1085
|
-- Constraints for table `TreeAttributes`
|
1086
|
--
|
1087
|
ALTER TABLE `TreeAttributes`
|
1088
|
ADD CONSTRAINT `TreeAttributes_ibfk_1` FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`),
|
1089
|
ADD CONSTRAINT `TreeAttributes_ibfk_2` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
1090
|
ADD CONSTRAINT `TreeAttributes_ibfk_3` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),
|
1091
|
ADD CONSTRAINT `TreeAttributes_ibfk_4` FOREIGN KEY (`CensusID`) REFERENCES `Census` (`CensusID`),
|
1092
|
ADD CONSTRAINT `TreeAttributes_ibfk_5` FOREIGN KEY (`TreeID`) REFERENCES `Tree` (`TreeID`),
|
1093
|
ADD CONSTRAINT `TreeAttributes_ibfk_6` FOREIGN KEY (`TSMID`) REFERENCES `TSMAttributes` (`TSMID`);
|
1094
|
|
1095
|
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
|
1096
|
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
|
1097
|
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|