Project

General

Profile

« Previous | Next » 

Revision 4311

inputs/CTFS/: Moved DBv5.txt to _archive because it's for a different version of the CTFS database than the extract we received (bci.sql)

View differences:

inputs/CTFS/DBv5.txt
1
--
2
-- ER/Studio Data Architect 9.0 SQL Code Generation
3
-- Company :      STRI
4
-- Project :      Physical-09-1-08mod_ERStudio_comp.DM1
5
-- Author :       Mario Campos, Zach Caschetta, Michael Koeber, BUCS, STRI
6
--
7
-- Date Created : Monday, November 14, 2011 17:39:33
8
-- Target DBMS : MySQL 5.x
9
--
10

  
11
-- 
12
-- TABLE: Census 
13
--
14

  
15
CREATE TABLE Census(
16
    CensusID            INT UNSIGNED    AUTO_INCREMENT,
17
    PlotID              INT UNSIGNED    NOT NULL,
18
    PlotCensusNumber    CHAR(16),
19
    StartDate           DATE,
20
    EndDate             DATE,
21
    Description         VARCHAR(128),
22
    PRIMARY KEY (CensusID)
23
)ENGINE=INNODB
24
;
25

  
26

  
27

  
28
-- 
29
-- TABLE: CensusQuadrat 
30
--
31

  
32
CREATE TABLE CensusQuadrat(
33
    CensusQuadratID    INT UNSIGNED  AUTO_INCREMENT,
34
    CensusID           INT UNSIGNED  NOT NULL,
35
    QuadratID          INT UNSIGNED  NOT NULL,
36
    PRIMARY KEY (CensusQuadratID)
37
)ENGINE=INNODB
38
;
39

  
40

  
41

  
42
-- 
43
-- TABLE: Coordinates 
44
--
45

  
46
CREATE TABLE Coordinates(
47
    CoorID          INT UNSIGNED     AUTO_INCREMENT,
48
    FeatureID       INT UNSIGNED     NOT NULL,
49
    QuadratID       INT UNSIGNED     NOT NULL,
50
    PlotID          INT UNSIGNED     NOT NULL,
51
    GX              DECIMAL(0, 0),
52
    GY              DECIMAL(0, 0),
53
    GZ              DECIMAL(0, 0),
54
    PX              DECIMAL(0, 0),
55
    PY              DECIMAL(0, 0),
56
    PZ              DECIMAL(0, 0),
57
    QX              DECIMAL(0, 0),
58
    QY              DECIMAL(0, 0),
59
    QZ              DECIMAL(0, 0),
60
    CoordinateNo    INT,
61
    PRIMARY KEY (CoorID)
62
)ENGINE=INNODB
63
;
64

  
65

  
66

  
67
-- 
68
-- TABLE: Country 
69
--
70

  
71
CREATE TABLE Country(
72
    CountryID      INT UNSIGNED   AUTO_INCREMENT,
73
    CountryName    VARCHAR(64),
74
    PRIMARY KEY (CountryID)
75
)ENGINE=INNODB
76
;
77

  
78

  
79

  
80
-- 
81
-- TABLE: CurrentObsolete 
82
--
83

  
84
CREATE TABLE CurrentObsolete(
85
    SpeciesID            INT UNSIGNED    NOT NULL,
86
    ObsoleteSpeciesID    INT UNSIGNED    NOT NULL,
87
    ChangeDate           DATETIME        NOT NULL,
88
    ChangeCodeID         INT UNSIGNED    NOT NULL,
89
    ChangeNote           VARCHAR(128),
90
    PRIMARY KEY (SpeciesID, ObsoleteSpeciesID, ChangeDate)
91
)ENGINE=INNODB
92
;
93

  
94

  
95

  
96
-- 
97
-- TABLE: DataCollection 
98
--
99

  
100
CREATE TABLE DataCollection(
101
    DataCollectionID    INT UNSIGNED  AUTO_INCREMENT,
102
    CensusID            INT UNSIGNED  NOT NULL,
103
    QuadratID           INT UNSIGNED  NOT NULL,
104
    PersonnelRoleID     INT UNSIGNED  NOT NULL,
105
    StartDate           DATE,
106
    EndDate             DATE,
107
    PRIMARY KEY (DataCollectionID)
108
)ENGINE=INNODB
109
;
110

  
111

  
112

  
113
-- 
114
-- TABLE: DBH 
115
--
116

  
117
CREATE TABLE DBH(
118
    DBHID          INT UNSIGNED    AUTO_INCREMENT,
119
    CensusID       INT UNSIGNED    NOT NULL,
120
    StemID         INT UNSIGNED    NOT NULL,
121
    DBH            FLOAT(7),
122
    HOM            CHAR(16),
123
    PrimaryStem    VARCHAR(20),
124
    ExactDate      DATE,
125
    Comments       VARCHAR(128),
126
    PRIMARY KEY (DBHID)
127
)ENGINE=INNODB
128
;
129

  
130

  
131

  
132
-- 
133
-- TABLE: DBHAttributes 
134
--
135

  
136
CREATE TABLE DBHAttributes(
137
    DBHAttID    INT UNSIGNED  AUTO_INCREMENT,
138
    DBHID       INT UNSIGNED  NOT NULL,
139
    CensusID    INT UNSIGNED  NOT NULL,
140
    TSMID       INT UNSIGNED  NOT NULL,
141
    PRIMARY KEY (DBHAttID)
142
)ENGINE=INNODB
143
;
144

  
145

  
146

  
147
-- 
148
-- TABLE: Family 
149
--
150

  
151
CREATE TABLE Family(
152
    FamilyID       INT UNSIGNED  AUTO_INCREMENT,
153
    Family         CHAR(32),
154
    ReferenceID    INT UNSIGNED  NOT NULL,
155
    PRIMARY KEY (FamilyID)
156
)ENGINE=INNODB
157
;
158

  
159

  
160

  
161
-- 
162
-- TABLE: Features 
163
--
164

  
165
CREATE TABLE Features(
166
    FeatureID           INT UNSIGNED    AUTO_INCREMENT,
167
    FeatureTypeID       INT UNSIGNED    NOT NULL,
168
    Name                VARCHAR(32),
169
    ShortDescription    VARCHAR(32),
170
    LongDescription     VARCHAR(128),
171
    PRIMARY KEY (FeatureID)
172
)ENGINE=INNODB
173
;
174

  
175

  
176

  
177
-- 
178
-- TABLE: FeatureTypes 
179
--
180

  
181
CREATE TABLE FeatureTypes(
182
    FeatureTypeID    INT UNSIGNED   AUTO_INCREMENT,
183
    Type             VARCHAR(32)    NOT NULL,
184
    PRIMARY KEY (FeatureTypeID)
185
)ENGINE=INNODB
186
;
187

  
188

  
189

  
190
-- 
191
-- TABLE: Genus 
192
--
193

  
194
CREATE TABLE Genus(
195
    GenusID        INT UNSIGNED  AUTO_INCREMENT,
196
    Genus          CHAR(32),
197
    ReferenceID    INT UNSIGNED  NOT NULL,
198
    Authority      CHAR(32),
199
    FamilyID       INT UNSIGNED  NOT NULL,
200
    PRIMARY KEY (GenusID)
201
)ENGINE=INNODB
202
;
203

  
204

  
205

  
206
-- 
207
-- TABLE: Log 
208
--
209

  
210
CREATE TABLE Log(
211
    LogID            INT UNSIGNED    AUTO_INCREMENT,
212
    PersonnelID      INT UNSIGNED    NOT NULL,
213
    ChangedTable     VARCHAR(32)     NOT NULL,
214
    ChangedColumn    VARCHAR(32)     NOT NULL,
215
    ChangeDate       DATE            NOT NULL,
216
    ChangeTime       TIMESTAMP,
217
    PrimaryKey       VARCHAR(32),
218
    Old              VARCHAR(128),
219
    New              VARCHAR(128),
220
    Action           CHAR(1)         NOT NULL,
221
    Description      VARCHAR(256),
222
    PRIMARY KEY (LogID)
223
)ENGINE=INNODB
224
;
225

  
226

  
227

  
228
-- 
229
-- TABLE: Measurement 
230
--
231

  
232
CREATE TABLE Measurement(
233
    MeasureID            INT UNSIGNED    AUTO_INCREMENT,
234
    CensusID             INT UNSIGNED,
235
    StemID               INT UNSIGNED    NOT NULL,
236
    MeasurementTypeID    INT UNSIGNED    NOT NULL,
237
    Measure              VARCHAR(32)     NOT NULL,
238
    ExactDate            DATE            NOT NULL,
239
    Comments             VARCHAR(128),
240
    PRIMARY KEY (MeasureID)
241
)ENGINE=INNODB
242
;
243

  
244

  
245

  
246
-- 
247
-- TABLE: MeasurementAttributes 
248
--
249

  
250
CREATE TABLE MeasurementAttributes(
251
    MAttID       INT UNSIGNED  AUTO_INCREMENT,
252
    MeasureID    INT UNSIGNED  NOT NULL,
253
    CensusID     INT UNSIGNED  NOT NULL,
254
    TSMID        INT UNSIGNED  NOT NULL,
255
    PRIMARY KEY (MAttID)
256
)ENGINE=INNODB
257
;
258

  
259

  
260

  
261
-- 
262
-- TABLE: MeasurementType 
263
--
264

  
265
CREATE TABLE MeasurementType(
266
    MeasurementTypeID    INT UNSIGNED   AUTO_INCREMENT,
267
    Type                 CHAR(32)       NOT NULL,
268
    UOM                  VARCHAR(32),
269
    PRIMARY KEY (MeasurementTypeID)
270
)ENGINE=INNODB
271
;
272

  
273

  
274

  
275
-- 
276
-- TABLE: Personnel 
277
--
278

  
279
CREATE TABLE Personnel(
280
    PersonnelID    INT UNSIGNED   AUTO_INCREMENT,
281
    FirstName      VARCHAR(32),
282
    LastName       VARCHAR(32)    NOT NULL,
283
    PRIMARY KEY (PersonnelID)
284
)ENGINE=INNODB
285
;
286

  
287

  
288

  
289
-- 
290
-- TABLE: PersonnelRole 
291
--
292

  
293
CREATE TABLE PersonnelRole(
294
    PersonnelRoleID    INT UNSIGNED  AUTO_INCREMENT,
295
    PersonnelID        INT UNSIGNED  NOT NULL,
296
    RoleID             INT UNSIGNED  NOT NULL,
297
    PRIMARY KEY (PersonnelRoleID)
298
)ENGINE=INNODB
299
;
300

  
301

  
302

  
303
-- 
304
-- TABLE: Quadrat 
305
--
306

  
307
CREATE TABLE Quadrat(
308
    QuadratID          INT UNSIGNED     AUTO_INCREMENT,
309
    PlotID             INT UNSIGNED     NOT NULL,
310
    QuadratName        CHAR(8),
311
    IsStandardShape    CHAR(1)          NOT NULL,
312
    Area               DECIMAL(0, 0),
313
    PRIMARY KEY (QuadratID)
314
)ENGINE=INNODB
315
;
316

  
317

  
318

  
319
-- 
320
-- TABLE: Reference 
321
--
322

  
323
CREATE TABLE Reference(
324
    ReferenceID          INT UNSIGNED    AUTO_INCREMENT,
325
    PublicationTitle     VARCHAR(64),
326
    FullReference        VARCHAR(256),
327
    DateofPublication    DATE,
328
    PRIMARY KEY (ReferenceID)
329
)ENGINE=INNODB
330
;
331

  
332

  
333

  
334
-- 
335
-- TABLE: RemeasAttribs 
336
--
337

  
338
CREATE TABLE RemeasAttribs(
339
    RmAttID        INT UNSIGNED  AUTO_INCREMENT,
340
    RemeasureID    INT UNSIGNED  NOT NULL,
341
    CensusID       INT UNSIGNED  NOT NULL,
342
    TSMID          INT UNSIGNED  NOT NULL,
343
    PRIMARY KEY (RmAttID)
344
)ENGINE=INNODB
345
;
346

  
347

  
348

  
349
-- 
350
-- TABLE: Remeasurement 
351
--
352

  
353
CREATE TABLE Remeasurement(
354
    RemeasureID    INT UNSIGNED  AUTO_INCREMENT,
355
    CensusID       INT UNSIGNED  NOT NULL,
356
    StemID         INT UNSIGNED  NOT NULL,
357
    DBH            FLOAT(7),
358
    HOM            FLOAT(7),
359
    ExactDate      DATE,
360
    PRIMARY KEY (RemeasureID)
361
)ENGINE=INNODB
362
;
363

  
364

  
365

  
366
-- 
367
-- TABLE: RoleReference 
368
--
369

  
370
CREATE TABLE RoleReference(
371
    RoleID         INT UNSIGNED    AUTO_INCREMENT,
372
    Description    VARCHAR(128),
373
    PRIMARY KEY (RoleID)
374
)ENGINE=INNODB
375
;
376

  
377

  
378

  
379
-- 
380
-- TABLE: Site 
381
--
382

  
383
CREATE TABLE Site(
384
    PlotID               INT UNSIGNED     AUTO_INCREMENT,
385
    CountryID            INT UNSIGNED     NOT NULL,
386
    Area                 DECIMAL(0, 0)    NOT NULL,
387
    PlotName             CHAR(64),
388
    LocationName         VARCHAR(128),
389
    QDimX                DECIMAL(0, 0)    NOT NULL,
390
    QDimY                DECIMAL(0, 0)    NOT NULL,
391
    SizeOfSite           CHAR(32),
392
    ShapeOfSite          CHAR(32),
393
    DescriptionOfSite    VARCHAR(128),
394
    GUnit                VARCHAR(32),
395
    GZUOM                VARCHAR(32),
396
    PUnit                VARCHAR(32),
397
    QUnit                VARCHAR(32),
398
    GCoorCollected       VARCHAR(32),
399
    PCoorCollected       VARCHAR(32),
400
    QCoorCollected       VARCHAR(32),
401
    PRIMARY KEY (PlotID)
402
)ENGINE=INNODB
403
;
404

  
405

  
406

  
407
-- 
408
-- TABLE: Species 
409
--
410

  
411
CREATE TABLE Species(
412
    SpeciesID            INT UNSIGNED     AUTO_INCREMENT,
413
    CurrentTaxonFlag     DECIMAL(0, 0),
414
    ObsoleteTaxonFlag    DECIMAL(0, 0),
415
    GenusID              INT UNSIGNED     NOT NULL,
416
    ReferenceID          INT UNSIGNED     NOT NULL,
417
    SpeciesName          CHAR(64),
418
    Mnemonic             CHAR(10),
419
    Authority            VARCHAR(128),
420
    IDLevel              CHAR(8),
421
    FieldFamily          CHAR(32),
422
    Description          VARCHAR(128),
423
    PRIMARY KEY (SpeciesID)
424
)ENGINE=INNODB
425
;
426

  
427

  
428

  
429
-- 
430
-- TABLE: SpeciesInventory 
431
--
432

  
433
CREATE TABLE SpeciesInventory(
434
    SpeciesInvID    INT UNSIGNED  AUTO_INCREMENT,
435
    CensusID        INT UNSIGNED  NOT NULL,
436
    PlotID          INT UNSIGNED  NOT NULL,
437
    SpeciesID       INT UNSIGNED  NOT NULL,
438
    SubSpeciesID    INT UNSIGNED  NOT NULL,
439
    PRIMARY KEY (SpeciesInvID)
440
)ENGINE=INNODB
441
;
442

  
443

  
444

  
445
-- 
446
-- TABLE: Specimen 
447
--
448

  
449
CREATE TABLE Specimen(
450
    SpecimenID        INT UNSIGNED     AUTO_INCREMENT,
451
    TreeID            INT UNSIGNED     NOT NULL,
452
    Collector         CHAR(64),
453
    SpecimenNumber    DECIMAL(0, 0),
454
    SpeciesID         INT UNSIGNED     NOT NULL,
455
    SubSpeciesID      INT UNSIGNED     NOT NULL,
456
    Herbarium         CHAR(32),
457
    Voucher           DECIMAL(0, 0),
458
    CollectionDate    DATE,
459
    DeterminedBy      CHAR(64),
460
    Description       VARCHAR(128),
461
    PRIMARY KEY (SpecimenID)
462
)ENGINE=INNODB
463
;
464

  
465

  
466

  
467
-- 
468
-- TABLE: Stem 
469
--
470

  
471
CREATE TABLE Stem(
472
    StemID             INT UNSIGNED     AUTO_INCREMENT,
473
    TreeID             INT UNSIGNED     NOT NULL,
474
    QuadratID          INT UNSIGNED     NOT NULL,
475
    StemTag            VARCHAR(32),
476
    StemDescription    VARCHAR(128),
477
    StemNumber         DECIMAL(0, 0)    NOT NULL,
478
    GX                 DECIMAL(0, 0),
479
    GY                 DECIMAL(0, 0),
480
    GZ                 DECIMAL(0, 0),
481
    PX                 DECIMAL(0, 0),
482
    PY                 CHAR(18),
483
    PZ                 CHAR(18),
484
    QX                 DECIMAL(0, 0),
485
    QY                 DECIMAL(0, 0),
486
    QZ                 DECIMAL(0, 0),
487
    Moved              CHAR(1)          NOT NULL,
488
    PRIMARY KEY (StemID)
489
)ENGINE=INNODB
490
;
491

  
492

  
493

  
494
-- 
495
-- TABLE: SubSpecies 
496
--
497

  
498
CREATE TABLE SubSpecies(
499
    SubSpeciesID          INT UNSIGNED     AUTO_INCREMENT,
500
    SpeciesID             INT UNSIGNED     NOT NULL,
501
    CurrentTaxonFlag      DECIMAL(0, 0),
502
    ObsoleteTaxonFlag     DECIMAL(0, 0),
503
    SubSpeciesName        CHAR(64),
504
    Mnemonic              CHAR(10),
505
    Authority             VARCHAR(128),
506
    InfraSpecificLevel    CHAR(32),
507
    PRIMARY KEY (SubSpeciesID)
508
)ENGINE=INNODB
509
;
510

  
511

  
512

  
513
-- 
514
-- TABLE: Tree 
515
--
516

  
517
CREATE TABLE Tree(
518
    TreeID          INT UNSIGNED  AUTO_INCREMENT,
519
    Tag             CHAR(16),
520
    SpeciesID       INT UNSIGNED  NOT NULL,
521
    SubSpeciesID    INT UNSIGNED  NOT NULL,
522
    PRIMARY KEY (TreeID)
523
)ENGINE=INNODB
524
;
525

  
526

  
527

  
528
-- 
529
-- TABLE: TreeAttributes 
530
--
531

  
532
CREATE TABLE TreeAttributes(
533
    TAttID      INT UNSIGNED  AUTO_INCREMENT,
534
    CensusID    INT UNSIGNED  NOT NULL,
535
    TreeID      INT UNSIGNED  NOT NULL,
536
    TSMID       INT UNSIGNED  NOT NULL,
537
    PRIMARY KEY (TAttID)
538
)ENGINE=INNODB
539
;
540

  
541

  
542

  
543
-- 
544
-- TABLE: TreeTaxChange 
545
--
546

  
547
CREATE TABLE TreeTaxChange(
548
    ChangeCodeID    INT UNSIGNED    AUTO_INCREMENT,
549
    Description     VARCHAR(128),
550
    PRIMARY KEY (ChangeCodeID)
551
)ENGINE=INNODB
552
;
553

  
554

  
555

  
556
-- 
557
-- TABLE: TSMAttributes 
558
--
559

  
560
CREATE TABLE TSMAttributes(
561
    TSMID          INT UNSIGNED    AUTO_INCREMENT,
562
    TSMCode        CHAR(10)        NOT NULL,
563
    Description    VARCHAR(128)    NOT NULL,
564
    PRIMARY KEY (TSMID)
565
)ENGINE=INNODB
566
;
567

  
568

  
569

  
570
-- 
571
-- TABLE: Census 
572
--
573

  
574
ALTER TABLE Census ADD CONSTRAINT RefSite2 
575
    FOREIGN KEY (PlotID)
576
    REFERENCES Site(PlotID)
577
;
578

  
579

  
580
-- 
581
-- TABLE: CensusQuadrat 
582
--
583

  
584
ALTER TABLE CensusQuadrat ADD CONSTRAINT RefCensus18 
585
    FOREIGN KEY (CensusID)
586
    REFERENCES Census(CensusID)
587
;
588

  
589
ALTER TABLE CensusQuadrat ADD CONSTRAINT RefQuadrat65 
590
    FOREIGN KEY (QuadratID)
591
    REFERENCES Quadrat(QuadratID)
592
;
593

  
594

  
595
-- 
596
-- TABLE: Coordinates 
597
--
598

  
599
ALTER TABLE Coordinates ADD CONSTRAINT RefFeatures52 
600
    FOREIGN KEY (FeatureID)
601
    REFERENCES Features(FeatureID)
602
;
603

  
604
ALTER TABLE Coordinates ADD CONSTRAINT RefSite53 
605
    FOREIGN KEY (PlotID)
606
    REFERENCES Site(PlotID)
607
;
608

  
609
ALTER TABLE Coordinates ADD CONSTRAINT RefQuadrat62 
610
    FOREIGN KEY (QuadratID)
611
    REFERENCES Quadrat(QuadratID)
612
;
613

  
614

  
615
-- 
616
-- TABLE: CurrentObsolete 
617
--
618

  
619
ALTER TABLE CurrentObsolete ADD CONSTRAINT RefTreeTaxChange27 
620
    FOREIGN KEY (ChangeCodeID)
621
    REFERENCES TreeTaxChange(ChangeCodeID)
622
;
623

  
624
ALTER TABLE CurrentObsolete ADD CONSTRAINT RefSpecies28 
625
    FOREIGN KEY (SpeciesID)
626
    REFERENCES Species(SpeciesID)
627
;
628

  
629
ALTER TABLE CurrentObsolete ADD CONSTRAINT RefSpecies29 
630
    FOREIGN KEY (ObsoleteSpeciesID)
631
    REFERENCES Species(SpeciesID)
632
;
633

  
634

  
635
-- 
636
-- TABLE: DataCollection 
637
--
638

  
639
ALTER TABLE DataCollection ADD CONSTRAINT RefPersonnelRole73 
640
    FOREIGN KEY (PersonnelRoleID)
641
    REFERENCES PersonnelRole(PersonnelRoleID)
642
;
643

  
644
ALTER TABLE DataCollection ADD CONSTRAINT RefCensus21 
645
    FOREIGN KEY (CensusID)
646
    REFERENCES Census(CensusID)
647
;
648

  
649
ALTER TABLE DataCollection ADD CONSTRAINT RefQuadrat64 
650
    FOREIGN KEY (QuadratID)
651
    REFERENCES Quadrat(QuadratID)
652
;
653

  
654

  
655
-- 
656
-- TABLE: DBH 
657
--
658

  
659
ALTER TABLE DBH ADD CONSTRAINT RefCensus12 
660
    FOREIGN KEY (CensusID)
661
    REFERENCES Census(CensusID)
662
;
663

  
664
ALTER TABLE DBH ADD CONSTRAINT RefStem13 
665
    FOREIGN KEY (StemID)
666
    REFERENCES Stem(StemID)
667
;
668

  
669

  
670
-- 
671
-- TABLE: DBHAttributes 
672
--
673

  
674
ALTER TABLE DBHAttributes ADD CONSTRAINT RefDBH7 
675
    FOREIGN KEY (DBHID)
676
    REFERENCES DBH(DBHID)
677
;
678

  
679
ALTER TABLE DBHAttributes ADD CONSTRAINT RefCensus31 
680
    FOREIGN KEY (CensusID)
681
    REFERENCES Census(CensusID)
682
;
683

  
684
ALTER TABLE DBHAttributes ADD CONSTRAINT RefTSMAttributes32 
685
    FOREIGN KEY (TSMID)
686
    REFERENCES TSMAttributes(TSMID)
687
;
688

  
689

  
690
-- 
691
-- TABLE: Family 
692
--
693

  
694
ALTER TABLE Family ADD CONSTRAINT RefReference24 
695
    FOREIGN KEY (ReferenceID)
696
    REFERENCES Reference(ReferenceID)
697
;
698

  
699

  
700
-- 
701
-- TABLE: Features 
702
--
703

  
704
ALTER TABLE Features ADD CONSTRAINT RefFeatureTypes51 
705
    FOREIGN KEY (FeatureTypeID)
706
    REFERENCES FeatureTypes(FeatureTypeID)
707
;
708

  
709

  
710
-- 
711
-- TABLE: Genus 
712
--
713

  
714
ALTER TABLE Genus ADD CONSTRAINT RefFamily25 
715
    FOREIGN KEY (FamilyID)
716
    REFERENCES Family(FamilyID)
717
;
718

  
719
ALTER TABLE Genus ADD CONSTRAINT RefReference30 
720
    FOREIGN KEY (ReferenceID)
721
    REFERENCES Reference(ReferenceID)
722
;
723

  
724

  
725
-- 
726
-- TABLE: Log 
727
--
728

  
729
ALTER TABLE Log ADD CONSTRAINT RefPersonnel55 
730
    FOREIGN KEY (PersonnelID)
731
    REFERENCES Personnel(PersonnelID)
732
;
733

  
734

  
735
-- 
736
-- TABLE: Measurement 
737
--
738

  
739
ALTER TABLE Measurement ADD CONSTRAINT RefCensus9 
740
    FOREIGN KEY (CensusID)
741
    REFERENCES Census(CensusID)
742
;
743

  
744
ALTER TABLE Measurement ADD CONSTRAINT RefMeasurementType10 
745
    FOREIGN KEY (MeasurementTypeID)
746
    REFERENCES MeasurementType(MeasurementTypeID)
747
;
748

  
749
ALTER TABLE Measurement ADD CONSTRAINT RefStem11 
750
    FOREIGN KEY (StemID)
751
    REFERENCES Stem(StemID)
752
;
753

  
754

  
755
-- 
756
-- TABLE: MeasurementAttributes 
757
--
758

  
759
ALTER TABLE MeasurementAttributes ADD CONSTRAINT RefMeasurement8 
760
    FOREIGN KEY (MeasureID)
761
    REFERENCES Measurement(MeasureID)
762
;
763

  
764
ALTER TABLE MeasurementAttributes ADD CONSTRAINT RefCensus58 
765
    FOREIGN KEY (CensusID)
766
    REFERENCES Census(CensusID)
767
;
768

  
769
ALTER TABLE MeasurementAttributes ADD CONSTRAINT RefTSMAttributes59 
770
    FOREIGN KEY (TSMID)
771
    REFERENCES TSMAttributes(TSMID)
772
;
773

  
774

  
775
-- 
776
-- TABLE: PersonnelRole 
777
--
778

  
779
ALTER TABLE PersonnelRole ADD CONSTRAINT RefRoleReference71 
780
    FOREIGN KEY (RoleID)
781
    REFERENCES RoleReference(RoleID)
782
;
783

  
784
ALTER TABLE PersonnelRole ADD CONSTRAINT RefPersonnel72 
785
    FOREIGN KEY (PersonnelID)
786
    REFERENCES Personnel(PersonnelID)
787
;
788

  
789

  
790
-- 
791
-- TABLE: Quadrat 
792
--
793

  
794
ALTER TABLE Quadrat ADD CONSTRAINT RefSite60 
795
    FOREIGN KEY (PlotID)
796
    REFERENCES Site(PlotID)
797
;
798

  
799

  
800
-- 
801
-- TABLE: RemeasAttribs 
802
--
803

  
804
ALTER TABLE RemeasAttribs ADD CONSTRAINT RefCensus34 
805
    FOREIGN KEY (CensusID)
806
    REFERENCES Census(CensusID)
807
;
808

  
809
ALTER TABLE RemeasAttribs ADD CONSTRAINT RefTSMAttributes35 
810
    FOREIGN KEY (TSMID)
811
    REFERENCES TSMAttributes(TSMID)
812
;
813

  
814
ALTER TABLE RemeasAttribs ADD CONSTRAINT RefRemeasurement36 
815
    FOREIGN KEY (RemeasureID)
816
    REFERENCES Remeasurement(RemeasureID)
817
;
818

  
819

  
820
-- 
821
-- TABLE: Remeasurement 
822
--
823

  
824
ALTER TABLE Remeasurement ADD CONSTRAINT RefCensus14 
825
    FOREIGN KEY (CensusID)
826
    REFERENCES Census(CensusID)
827
;
828

  
829
ALTER TABLE Remeasurement ADD CONSTRAINT RefStem15 
830
    FOREIGN KEY (StemID)
831
    REFERENCES Stem(StemID)
832
;
833

  
834

  
835
-- 
836
-- TABLE: Site 
837
--
838

  
839
ALTER TABLE Site ADD CONSTRAINT RefCountry37 
840
    FOREIGN KEY (CountryID)
841
    REFERENCES Country(CountryID)
842
;
843

  
844

  
845
-- 
846
-- TABLE: Species 
847
--
848

  
849
ALTER TABLE Species ADD CONSTRAINT RefReference70 
850
    FOREIGN KEY (ReferenceID)
851
    REFERENCES Reference(ReferenceID)
852
;
853

  
854
ALTER TABLE Species ADD CONSTRAINT RefGenus38 
855
    FOREIGN KEY (GenusID)
856
    REFERENCES Genus(GenusID)
857
;
858

  
859

  
860
-- 
861
-- TABLE: SpeciesInventory 
862
--
863

  
864
ALTER TABLE SpeciesInventory ADD CONSTRAINT RefCensus39 
865
    FOREIGN KEY (CensusID)
866
    REFERENCES Census(CensusID)
867
;
868

  
869
ALTER TABLE SpeciesInventory ADD CONSTRAINT RefSite40 
870
    FOREIGN KEY (PlotID)
871
    REFERENCES Site(PlotID)
872
;
873

  
874
ALTER TABLE SpeciesInventory ADD CONSTRAINT RefSpecies41 
875
    FOREIGN KEY (SpeciesID)
876
    REFERENCES Species(SpeciesID)
877
;
878

  
879
ALTER TABLE SpeciesInventory ADD CONSTRAINT RefSubSpecies42 
880
    FOREIGN KEY (SubSpeciesID)
881
    REFERENCES SubSpecies(SubSpeciesID)
882
;
883

  
884

  
885
-- 
886
-- TABLE: Specimen 
887
--
888

  
889
ALTER TABLE Specimen ADD CONSTRAINT RefTree43 
890
    FOREIGN KEY (TreeID)
891
    REFERENCES Tree(TreeID)
892
;
893

  
894
ALTER TABLE Specimen ADD CONSTRAINT RefSpecies44 
895
    FOREIGN KEY (SpeciesID)
896
    REFERENCES Species(SpeciesID)
897
;
898

  
899
ALTER TABLE Specimen ADD CONSTRAINT RefSubSpecies45 
900
    FOREIGN KEY (SubSpeciesID)
901
    REFERENCES SubSpecies(SubSpeciesID)
902
;
903

  
904

  
905
-- 
906
-- TABLE: Stem 
907
--
908

  
909
ALTER TABLE Stem ADD CONSTRAINT RefTree56 
910
    FOREIGN KEY (TreeID)
911
    REFERENCES Tree(TreeID)
912
;
913

  
914
ALTER TABLE Stem ADD CONSTRAINT RefQuadrat63 
915
    FOREIGN KEY (QuadratID)
916
    REFERENCES Quadrat(QuadratID)
917
;
918

  
919

  
920
-- 
921
-- TABLE: SubSpecies 
922
--
923

  
924
ALTER TABLE SubSpecies ADD CONSTRAINT RefSpecies47 
925
    FOREIGN KEY (SpeciesID)
926
    REFERENCES Species(SpeciesID)
927
;
928

  
929

  
930
-- 
931
-- TABLE: Tree 
932
--
933

  
934
ALTER TABLE Tree ADD CONSTRAINT RefSubSpecies69 
935
    FOREIGN KEY (SubSpeciesID)
936
    REFERENCES SubSpecies(SubSpeciesID)
937
;
938

  
939
ALTER TABLE Tree ADD CONSTRAINT RefSpecies68 
940
    FOREIGN KEY (SpeciesID)
941
    REFERENCES Species(SpeciesID)
942
;
943

  
944

  
945
-- 
946
-- TABLE: TreeAttributes 
947
--
948

  
949
ALTER TABLE TreeAttributes ADD CONSTRAINT RefCensus48 
950
    FOREIGN KEY (CensusID)
951
    REFERENCES Census(CensusID)
952
;
953

  
954
ALTER TABLE TreeAttributes ADD CONSTRAINT RefTSMAttributes49 
955
    FOREIGN KEY (TSMID)
956
    REFERENCES TSMAttributes(TSMID)
957
;
958

  
959
ALTER TABLE TreeAttributes ADD CONSTRAINT RefTree50 
960
    FOREIGN KEY (TreeID)
961
    REFERENCES Tree(TreeID)
962
;
963

  
964

  
inputs/CTFS/_archive/DBv5.txt
1
--
2
-- ER/Studio Data Architect 9.0 SQL Code Generation
3
-- Company :      STRI
4
-- Project :      Physical-09-1-08mod_ERStudio_comp.DM1
5
-- Author :       Mario Campos, Zach Caschetta, Michael Koeber, BUCS, STRI
6
--
7
-- Date Created : Monday, November 14, 2011 17:39:33
8
-- Target DBMS : MySQL 5.x
9
--
10

  
11
-- 
12
-- TABLE: Census 
13
--
14

  
15
CREATE TABLE Census(
16
    CensusID            INT UNSIGNED    AUTO_INCREMENT,
17
    PlotID              INT UNSIGNED    NOT NULL,
18
    PlotCensusNumber    CHAR(16),
19
    StartDate           DATE,
20
    EndDate             DATE,
21
    Description         VARCHAR(128),
22
    PRIMARY KEY (CensusID)
23
)ENGINE=INNODB
24
;
25

  
26

  
27

  
28
-- 
29
-- TABLE: CensusQuadrat 
30
--
31

  
32
CREATE TABLE CensusQuadrat(
33
    CensusQuadratID    INT UNSIGNED  AUTO_INCREMENT,
34
    CensusID           INT UNSIGNED  NOT NULL,
35
    QuadratID          INT UNSIGNED  NOT NULL,
36
    PRIMARY KEY (CensusQuadratID)
37
)ENGINE=INNODB
38
;
39

  
40

  
41

  
42
-- 
43
-- TABLE: Coordinates 
44
--
45

  
46
CREATE TABLE Coordinates(
47
    CoorID          INT UNSIGNED     AUTO_INCREMENT,
48
    FeatureID       INT UNSIGNED     NOT NULL,
49
    QuadratID       INT UNSIGNED     NOT NULL,
50
    PlotID          INT UNSIGNED     NOT NULL,
51
    GX              DECIMAL(0, 0),
52
    GY              DECIMAL(0, 0),
53
    GZ              DECIMAL(0, 0),
54
    PX              DECIMAL(0, 0),
55
    PY              DECIMAL(0, 0),
56
    PZ              DECIMAL(0, 0),
57
    QX              DECIMAL(0, 0),
58
    QY              DECIMAL(0, 0),
59
    QZ              DECIMAL(0, 0),
60
    CoordinateNo    INT,
61
    PRIMARY KEY (CoorID)
62
)ENGINE=INNODB
63
;
64

  
65

  
66

  
67
-- 
68
-- TABLE: Country 
69
--
70

  
71
CREATE TABLE Country(
72
    CountryID      INT UNSIGNED   AUTO_INCREMENT,
73
    CountryName    VARCHAR(64),
74
    PRIMARY KEY (CountryID)
75
)ENGINE=INNODB
76
;
77

  
78

  
79

  
80
-- 
81
-- TABLE: CurrentObsolete 
82
--
83

  
84
CREATE TABLE CurrentObsolete(
85
    SpeciesID            INT UNSIGNED    NOT NULL,
86
    ObsoleteSpeciesID    INT UNSIGNED    NOT NULL,
87
    ChangeDate           DATETIME        NOT NULL,
88
    ChangeCodeID         INT UNSIGNED    NOT NULL,
89
    ChangeNote           VARCHAR(128),
90
    PRIMARY KEY (SpeciesID, ObsoleteSpeciesID, ChangeDate)
91
)ENGINE=INNODB
92
;
93

  
94

  
95

  
96
-- 
97
-- TABLE: DataCollection 
98
--
99

  
100
CREATE TABLE DataCollection(
101
    DataCollectionID    INT UNSIGNED  AUTO_INCREMENT,
102
    CensusID            INT UNSIGNED  NOT NULL,
103
    QuadratID           INT UNSIGNED  NOT NULL,
104
    PersonnelRoleID     INT UNSIGNED  NOT NULL,
105
    StartDate           DATE,
106
    EndDate             DATE,
107
    PRIMARY KEY (DataCollectionID)
108
)ENGINE=INNODB
109
;
110

  
111

  
112

  
113
-- 
114
-- TABLE: DBH 
115
--
116

  
117
CREATE TABLE DBH(
118
    DBHID          INT UNSIGNED    AUTO_INCREMENT,
119
    CensusID       INT UNSIGNED    NOT NULL,
120
    StemID         INT UNSIGNED    NOT NULL,
121
    DBH            FLOAT(7),
122
    HOM            CHAR(16),
123
    PrimaryStem    VARCHAR(20),
124
    ExactDate      DATE,
125
    Comments       VARCHAR(128),
126
    PRIMARY KEY (DBHID)
127
)ENGINE=INNODB
128
;
129

  
130

  
131

  
132
-- 
133
-- TABLE: DBHAttributes 
134
--
135

  
136
CREATE TABLE DBHAttributes(
137
    DBHAttID    INT UNSIGNED  AUTO_INCREMENT,
138
    DBHID       INT UNSIGNED  NOT NULL,
139
    CensusID    INT UNSIGNED  NOT NULL,
140
    TSMID       INT UNSIGNED  NOT NULL,
141
    PRIMARY KEY (DBHAttID)
142
)ENGINE=INNODB
143
;
144

  
145

  
146

  
147
-- 
148
-- TABLE: Family 
149
--
150

  
151
CREATE TABLE Family(
152
    FamilyID       INT UNSIGNED  AUTO_INCREMENT,
153
    Family         CHAR(32),
154
    ReferenceID    INT UNSIGNED  NOT NULL,
155
    PRIMARY KEY (FamilyID)
156
)ENGINE=INNODB
157
;
158

  
159

  
160

  
161
-- 
162
-- TABLE: Features 
163
--
164

  
165
CREATE TABLE Features(
166
    FeatureID           INT UNSIGNED    AUTO_INCREMENT,
167
    FeatureTypeID       INT UNSIGNED    NOT NULL,
168
    Name                VARCHAR(32),
169
    ShortDescription    VARCHAR(32),
170
    LongDescription     VARCHAR(128),
171
    PRIMARY KEY (FeatureID)
172
)ENGINE=INNODB
173
;
174

  
175

  
176

  
177
-- 
178
-- TABLE: FeatureTypes 
179
--
180

  
181
CREATE TABLE FeatureTypes(
182
    FeatureTypeID    INT UNSIGNED   AUTO_INCREMENT,
183
    Type             VARCHAR(32)    NOT NULL,
184
    PRIMARY KEY (FeatureTypeID)
185
)ENGINE=INNODB
186
;
187

  
188

  
189

  
190
-- 
191
-- TABLE: Genus 
192
--
193

  
194
CREATE TABLE Genus(
195
    GenusID        INT UNSIGNED  AUTO_INCREMENT,
196
    Genus          CHAR(32),
197
    ReferenceID    INT UNSIGNED  NOT NULL,
198
    Authority      CHAR(32),
199
    FamilyID       INT UNSIGNED  NOT NULL,
200
    PRIMARY KEY (GenusID)
201
)ENGINE=INNODB
202
;
203

  
204

  
205

  
206
-- 
207
-- TABLE: Log 
208
--
209

  
210
CREATE TABLE Log(
211
    LogID            INT UNSIGNED    AUTO_INCREMENT,
212
    PersonnelID      INT UNSIGNED    NOT NULL,
213
    ChangedTable     VARCHAR(32)     NOT NULL,
214
    ChangedColumn    VARCHAR(32)     NOT NULL,
215
    ChangeDate       DATE            NOT NULL,
216
    ChangeTime       TIMESTAMP,
217
    PrimaryKey       VARCHAR(32),
218
    Old              VARCHAR(128),
219
    New              VARCHAR(128),
220
    Action           CHAR(1)         NOT NULL,
221
    Description      VARCHAR(256),
222
    PRIMARY KEY (LogID)
223
)ENGINE=INNODB
224
;
225

  
226

  
227

  
228
-- 
229
-- TABLE: Measurement 
230
--
231

  
232
CREATE TABLE Measurement(
233
    MeasureID            INT UNSIGNED    AUTO_INCREMENT,
234
    CensusID             INT UNSIGNED,
235
    StemID               INT UNSIGNED    NOT NULL,
236
    MeasurementTypeID    INT UNSIGNED    NOT NULL,
237
    Measure              VARCHAR(32)     NOT NULL,
238
    ExactDate            DATE            NOT NULL,
239
    Comments             VARCHAR(128),
240
    PRIMARY KEY (MeasureID)
241
)ENGINE=INNODB
242
;
243

  
244

  
245

  
246
-- 
247
-- TABLE: MeasurementAttributes 
248
--
249

  
250
CREATE TABLE MeasurementAttributes(
251
    MAttID       INT UNSIGNED  AUTO_INCREMENT,
252
    MeasureID    INT UNSIGNED  NOT NULL,
253
    CensusID     INT UNSIGNED  NOT NULL,
254
    TSMID        INT UNSIGNED  NOT NULL,
255
    PRIMARY KEY (MAttID)
256
)ENGINE=INNODB
257
;
258

  
259

  
260

  
261
-- 
262
-- TABLE: MeasurementType 
263
--
264

  
265
CREATE TABLE MeasurementType(
266
    MeasurementTypeID    INT UNSIGNED   AUTO_INCREMENT,
267
    Type                 CHAR(32)       NOT NULL,
268
    UOM                  VARCHAR(32),
269
    PRIMARY KEY (MeasurementTypeID)
270
)ENGINE=INNODB
271
;
272

  
273

  
274

  
275
-- 
276
-- TABLE: Personnel 
277
--
278

  
279
CREATE TABLE Personnel(
280
    PersonnelID    INT UNSIGNED   AUTO_INCREMENT,
281
    FirstName      VARCHAR(32),
282
    LastName       VARCHAR(32)    NOT NULL,
283
    PRIMARY KEY (PersonnelID)
284
)ENGINE=INNODB
285
;
286

  
287

  
288

  
289
-- 
290
-- TABLE: PersonnelRole 
291
--
292

  
293
CREATE TABLE PersonnelRole(
294
    PersonnelRoleID    INT UNSIGNED  AUTO_INCREMENT,
295
    PersonnelID        INT UNSIGNED  NOT NULL,
296
    RoleID             INT UNSIGNED  NOT NULL,
297
    PRIMARY KEY (PersonnelRoleID)
298
)ENGINE=INNODB
299
;
300

  
301

  
302

  
303
-- 
304
-- TABLE: Quadrat 
305
--
306

  
307
CREATE TABLE Quadrat(
308
    QuadratID          INT UNSIGNED     AUTO_INCREMENT,
309
    PlotID             INT UNSIGNED     NOT NULL,
310
    QuadratName        CHAR(8),
311
    IsStandardShape    CHAR(1)          NOT NULL,
312
    Area               DECIMAL(0, 0),
313
    PRIMARY KEY (QuadratID)
314
)ENGINE=INNODB
315
;
316

  
317

  
318

  
319
-- 
320
-- TABLE: Reference 
321
--
322

  
323
CREATE TABLE Reference(
324
    ReferenceID          INT UNSIGNED    AUTO_INCREMENT,
325
    PublicationTitle     VARCHAR(64),
326
    FullReference        VARCHAR(256),
327
    DateofPublication    DATE,
328
    PRIMARY KEY (ReferenceID)
329
)ENGINE=INNODB
330
;
331

  
332

  
333

  
334
-- 
335
-- TABLE: RemeasAttribs 
336
--
337

  
338
CREATE TABLE RemeasAttribs(
339
    RmAttID        INT UNSIGNED  AUTO_INCREMENT,
340
    RemeasureID    INT UNSIGNED  NOT NULL,
341
    CensusID       INT UNSIGNED  NOT NULL,
342
    TSMID          INT UNSIGNED  NOT NULL,
343
    PRIMARY KEY (RmAttID)
344
)ENGINE=INNODB
345
;
346

  
347

  
348

  
349
-- 
350
-- TABLE: Remeasurement 
351
--
352

  
353
CREATE TABLE Remeasurement(
354
    RemeasureID    INT UNSIGNED  AUTO_INCREMENT,
355
    CensusID       INT UNSIGNED  NOT NULL,
356
    StemID         INT UNSIGNED  NOT NULL,
357
    DBH            FLOAT(7),
358
    HOM            FLOAT(7),
359
    ExactDate      DATE,
360
    PRIMARY KEY (RemeasureID)
361
)ENGINE=INNODB
362
;
363

  
364

  
365

  
366
-- 
367
-- TABLE: RoleReference 
368
--
369

  
370
CREATE TABLE RoleReference(
371
    RoleID         INT UNSIGNED    AUTO_INCREMENT,
372
    Description    VARCHAR(128),
373
    PRIMARY KEY (RoleID)
374
)ENGINE=INNODB
375
;
376

  
377

  
378

  
379
-- 
380
-- TABLE: Site 
381
--
382

  
383
CREATE TABLE Site(
384
    PlotID               INT UNSIGNED     AUTO_INCREMENT,
385
    CountryID            INT UNSIGNED     NOT NULL,
386
    Area                 DECIMAL(0, 0)    NOT NULL,
387
    PlotName             CHAR(64),
388
    LocationName         VARCHAR(128),
389
    QDimX                DECIMAL(0, 0)    NOT NULL,
390
    QDimY                DECIMAL(0, 0)    NOT NULL,
391
    SizeOfSite           CHAR(32),
392
    ShapeOfSite          CHAR(32),
393
    DescriptionOfSite    VARCHAR(128),
394
    GUnit                VARCHAR(32),
395
    GZUOM                VARCHAR(32),
396
    PUnit                VARCHAR(32),
397
    QUnit                VARCHAR(32),
398
    GCoorCollected       VARCHAR(32),
399
    PCoorCollected       VARCHAR(32),
400
    QCoorCollected       VARCHAR(32),
401
    PRIMARY KEY (PlotID)
402
)ENGINE=INNODB
403
;
404

  
405

  
406

  
407
-- 
408
-- TABLE: Species 
409
--
410

  
411
CREATE TABLE Species(
412
    SpeciesID            INT UNSIGNED     AUTO_INCREMENT,
413
    CurrentTaxonFlag     DECIMAL(0, 0),
414
    ObsoleteTaxonFlag    DECIMAL(0, 0),
415
    GenusID              INT UNSIGNED     NOT NULL,
416
    ReferenceID          INT UNSIGNED     NOT NULL,
417
    SpeciesName          CHAR(64),
418
    Mnemonic             CHAR(10),
419
    Authority            VARCHAR(128),
420
    IDLevel              CHAR(8),
421
    FieldFamily          CHAR(32),
422
    Description          VARCHAR(128),
423
    PRIMARY KEY (SpeciesID)
424
)ENGINE=INNODB
425
;
426

  
427

  
428

  
429
-- 
430
-- TABLE: SpeciesInventory 
431
--
432

  
433
CREATE TABLE SpeciesInventory(
434
    SpeciesInvID    INT UNSIGNED  AUTO_INCREMENT,
435
    CensusID        INT UNSIGNED  NOT NULL,
436
    PlotID          INT UNSIGNED  NOT NULL,
437
    SpeciesID       INT UNSIGNED  NOT NULL,
438
    SubSpeciesID    INT UNSIGNED  NOT NULL,
439
    PRIMARY KEY (SpeciesInvID)
440
)ENGINE=INNODB
441
;
442

  
443

  
444

  
445
-- 
446
-- TABLE: Specimen 
447
--
448

  
449
CREATE TABLE Specimen(
450
    SpecimenID        INT UNSIGNED     AUTO_INCREMENT,
451
    TreeID            INT UNSIGNED     NOT NULL,
452
    Collector         CHAR(64),
453
    SpecimenNumber    DECIMAL(0, 0),
454
    SpeciesID         INT UNSIGNED     NOT NULL,
455
    SubSpeciesID      INT UNSIGNED     NOT NULL,
456
    Herbarium         CHAR(32),
457
    Voucher           DECIMAL(0, 0),
458
    CollectionDate    DATE,
459
    DeterminedBy      CHAR(64),
460
    Description       VARCHAR(128),
461
    PRIMARY KEY (SpecimenID)
462
)ENGINE=INNODB
463
;
464

  
465

  
466

  
467
-- 
468
-- TABLE: Stem 
469
--
470

  
471
CREATE TABLE Stem(
472
    StemID             INT UNSIGNED     AUTO_INCREMENT,
473
    TreeID             INT UNSIGNED     NOT NULL,
474
    QuadratID          INT UNSIGNED     NOT NULL,
475
    StemTag            VARCHAR(32),
476
    StemDescription    VARCHAR(128),
477
    StemNumber         DECIMAL(0, 0)    NOT NULL,
478
    GX                 DECIMAL(0, 0),
479
    GY                 DECIMAL(0, 0),
480
    GZ                 DECIMAL(0, 0),
481
    PX                 DECIMAL(0, 0),
482
    PY                 CHAR(18),
483
    PZ                 CHAR(18),
484
    QX                 DECIMAL(0, 0),
485
    QY                 DECIMAL(0, 0),
486
    QZ                 DECIMAL(0, 0),
487
    Moved              CHAR(1)          NOT NULL,
488
    PRIMARY KEY (StemID)
489
)ENGINE=INNODB
490
;
491

  
492

  
493

  
494
-- 
495
-- TABLE: SubSpecies 
496
--
497

  
498
CREATE TABLE SubSpecies(
499
    SubSpeciesID          INT UNSIGNED     AUTO_INCREMENT,
500
    SpeciesID             INT UNSIGNED     NOT NULL,
501
    CurrentTaxonFlag      DECIMAL(0, 0),
502
    ObsoleteTaxonFlag     DECIMAL(0, 0),
503
    SubSpeciesName        CHAR(64),
504
    Mnemonic              CHAR(10),
505
    Authority             VARCHAR(128),
506
    InfraSpecificLevel    CHAR(32),
507
    PRIMARY KEY (SubSpeciesID)
508
)ENGINE=INNODB
509
;
510

  
511

  
512

  
513
-- 
514
-- TABLE: Tree 
515
--
516

  
517
CREATE TABLE Tree(
518
    TreeID          INT UNSIGNED  AUTO_INCREMENT,
519
    Tag             CHAR(16),
520
    SpeciesID       INT UNSIGNED  NOT NULL,
521
    SubSpeciesID    INT UNSIGNED  NOT NULL,
522
    PRIMARY KEY (TreeID)
523
)ENGINE=INNODB
524
;
525

  
526

  
527

  
... This diff was truncated because it exceeds the maximum size that can be displayed.

Also available in: Unified diff