Project

General

Profile

1 1549 aaronmk
--
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