Project

General

Profile

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

    
(2-2/7)