--
-- ER/Studio Data Architect 9.0 SQL Code Generation
-- Company :      STRI
-- Project :      Physical-09-1-08mod_ERStudio_comp.DM1
-- Author :       Mario Campos, Zach Caschetta, Michael Koeber, BUCS, STRI
--
-- Date Created : Monday, November 14, 2011 17:39:33
-- Target DBMS : MySQL 5.x
--

-- 
-- TABLE: Census 
--

CREATE TABLE Census(
    CensusID            INT UNSIGNED    AUTO_INCREMENT,
    PlotID              INT UNSIGNED    NOT NULL,
    PlotCensusNumber    CHAR(16),
    StartDate           DATE,
    EndDate             DATE,
    Description         VARCHAR(128),
    PRIMARY KEY (CensusID)
)ENGINE=INNODB
;



-- 
-- TABLE: CensusQuadrat 
--

CREATE TABLE CensusQuadrat(
    CensusQuadratID    INT UNSIGNED  AUTO_INCREMENT,
    CensusID           INT UNSIGNED  NOT NULL,
    QuadratID          INT UNSIGNED  NOT NULL,
    PRIMARY KEY (CensusQuadratID)
)ENGINE=INNODB
;



-- 
-- TABLE: Coordinates 
--

CREATE TABLE Coordinates(
    CoorID          INT UNSIGNED     AUTO_INCREMENT,
    FeatureID       INT UNSIGNED     NOT NULL,
    QuadratID       INT UNSIGNED     NOT NULL,
    PlotID          INT UNSIGNED     NOT NULL,
    GX              DECIMAL(0, 0),
    GY              DECIMAL(0, 0),
    GZ              DECIMAL(0, 0),
    PX              DECIMAL(0, 0),
    PY              DECIMAL(0, 0),
    PZ              DECIMAL(0, 0),
    QX              DECIMAL(0, 0),
    QY              DECIMAL(0, 0),
    QZ              DECIMAL(0, 0),
    CoordinateNo    INT,
    PRIMARY KEY (CoorID)
)ENGINE=INNODB
;



-- 
-- TABLE: Country 
--

CREATE TABLE Country(
    CountryID      INT UNSIGNED   AUTO_INCREMENT,
    CountryName    VARCHAR(64),
    PRIMARY KEY (CountryID)
)ENGINE=INNODB
;



-- 
-- TABLE: CurrentObsolete 
--

CREATE TABLE CurrentObsolete(
    SpeciesID            INT UNSIGNED    NOT NULL,
    ObsoleteSpeciesID    INT UNSIGNED    NOT NULL,
    ChangeDate           DATETIME        NOT NULL,
    ChangeCodeID         INT UNSIGNED    NOT NULL,
    ChangeNote           VARCHAR(128),
    PRIMARY KEY (SpeciesID, ObsoleteSpeciesID, ChangeDate)
)ENGINE=INNODB
;



-- 
-- TABLE: DataCollection 
--

CREATE TABLE DataCollection(
    DataCollectionID    INT UNSIGNED  AUTO_INCREMENT,
    CensusID            INT UNSIGNED  NOT NULL,
    QuadratID           INT UNSIGNED  NOT NULL,
    PersonnelRoleID     INT UNSIGNED  NOT NULL,
    StartDate           DATE,
    EndDate             DATE,
    PRIMARY KEY (DataCollectionID)
)ENGINE=INNODB
;



-- 
-- TABLE: DBH 
--

CREATE TABLE DBH(
    DBHID          INT UNSIGNED    AUTO_INCREMENT,
    CensusID       INT UNSIGNED    NOT NULL,
    StemID         INT UNSIGNED    NOT NULL,
    DBH            FLOAT(7),
    HOM            CHAR(16),
    PrimaryStem    VARCHAR(20),
    ExactDate      DATE,
    Comments       VARCHAR(128),
    PRIMARY KEY (DBHID)
)ENGINE=INNODB
;



-- 
-- TABLE: DBHAttributes 
--

CREATE TABLE DBHAttributes(
    DBHAttID    INT UNSIGNED  AUTO_INCREMENT,
    DBHID       INT UNSIGNED  NOT NULL,
    CensusID    INT UNSIGNED  NOT NULL,
    TSMID       INT UNSIGNED  NOT NULL,
    PRIMARY KEY (DBHAttID)
)ENGINE=INNODB
;



-- 
-- TABLE: Family 
--

CREATE TABLE Family(
    FamilyID       INT UNSIGNED  AUTO_INCREMENT,
    Family         CHAR(32),
    ReferenceID    INT UNSIGNED  NOT NULL,
    PRIMARY KEY (FamilyID)
)ENGINE=INNODB
;



-- 
-- TABLE: Features 
--

CREATE TABLE Features(
    FeatureID           INT UNSIGNED    AUTO_INCREMENT,
    FeatureTypeID       INT UNSIGNED    NOT NULL,
    Name                VARCHAR(32),
    ShortDescription    VARCHAR(32),
    LongDescription     VARCHAR(128),
    PRIMARY KEY (FeatureID)
)ENGINE=INNODB
;



-- 
-- TABLE: FeatureTypes 
--

CREATE TABLE FeatureTypes(
    FeatureTypeID    INT UNSIGNED   AUTO_INCREMENT,
    Type             VARCHAR(32)    NOT NULL,
    PRIMARY KEY (FeatureTypeID)
)ENGINE=INNODB
;



-- 
-- TABLE: Genus 
--

CREATE TABLE Genus(
    GenusID        INT UNSIGNED  AUTO_INCREMENT,
    Genus          CHAR(32),
    ReferenceID    INT UNSIGNED  NOT NULL,
    Authority      CHAR(32),
    FamilyID       INT UNSIGNED  NOT NULL,
    PRIMARY KEY (GenusID)
)ENGINE=INNODB
;



-- 
-- TABLE: Log 
--

CREATE TABLE Log(
    LogID            INT UNSIGNED    AUTO_INCREMENT,
    PersonnelID      INT UNSIGNED    NOT NULL,
    ChangedTable     VARCHAR(32)     NOT NULL,
    ChangedColumn    VARCHAR(32)     NOT NULL,
    ChangeDate       DATE            NOT NULL,
    ChangeTime       TIMESTAMP,
    PrimaryKey       VARCHAR(32),
    Old              VARCHAR(128),
    New              VARCHAR(128),
    Action           CHAR(1)         NOT NULL,
    Description      VARCHAR(256),
    PRIMARY KEY (LogID)
)ENGINE=INNODB
;



-- 
-- TABLE: Measurement 
--

CREATE TABLE Measurement(
    MeasureID            INT UNSIGNED    AUTO_INCREMENT,
    CensusID             INT UNSIGNED,
    StemID               INT UNSIGNED    NOT NULL,
    MeasurementTypeID    INT UNSIGNED    NOT NULL,
    Measure              VARCHAR(32)     NOT NULL,
    ExactDate            DATE            NOT NULL,
    Comments             VARCHAR(128),
    PRIMARY KEY (MeasureID)
)ENGINE=INNODB
;



-- 
-- TABLE: MeasurementAttributes 
--

CREATE TABLE MeasurementAttributes(
    MAttID       INT UNSIGNED  AUTO_INCREMENT,
    MeasureID    INT UNSIGNED  NOT NULL,
    CensusID     INT UNSIGNED  NOT NULL,
    TSMID        INT UNSIGNED  NOT NULL,
    PRIMARY KEY (MAttID)
)ENGINE=INNODB
;



-- 
-- TABLE: MeasurementType 
--

CREATE TABLE MeasurementType(
    MeasurementTypeID    INT UNSIGNED   AUTO_INCREMENT,
    Type                 CHAR(32)       NOT NULL,
    UOM                  VARCHAR(32),
    PRIMARY KEY (MeasurementTypeID)
)ENGINE=INNODB
;



-- 
-- TABLE: Personnel 
--

CREATE TABLE Personnel(
    PersonnelID    INT UNSIGNED   AUTO_INCREMENT,
    FirstName      VARCHAR(32),
    LastName       VARCHAR(32)    NOT NULL,
    PRIMARY KEY (PersonnelID)
)ENGINE=INNODB
;



-- 
-- TABLE: PersonnelRole 
--

CREATE TABLE PersonnelRole(
    PersonnelRoleID    INT UNSIGNED  AUTO_INCREMENT,
    PersonnelID        INT UNSIGNED  NOT NULL,
    RoleID             INT UNSIGNED  NOT NULL,
    PRIMARY KEY (PersonnelRoleID)
)ENGINE=INNODB
;



-- 
-- TABLE: Quadrat 
--

CREATE TABLE Quadrat(
    QuadratID          INT UNSIGNED     AUTO_INCREMENT,
    PlotID             INT UNSIGNED     NOT NULL,
    QuadratName        CHAR(8),
    IsStandardShape    CHAR(1)          NOT NULL,
    Area               DECIMAL(0, 0),
    PRIMARY KEY (QuadratID)
)ENGINE=INNODB
;



-- 
-- TABLE: Reference 
--

CREATE TABLE Reference(
    ReferenceID          INT UNSIGNED    AUTO_INCREMENT,
    PublicationTitle     VARCHAR(64),
    FullReference        VARCHAR(256),
    DateofPublication    DATE,
    PRIMARY KEY (ReferenceID)
)ENGINE=INNODB
;



-- 
-- TABLE: RemeasAttribs 
--

CREATE TABLE RemeasAttribs(
    RmAttID        INT UNSIGNED  AUTO_INCREMENT,
    RemeasureID    INT UNSIGNED  NOT NULL,
    CensusID       INT UNSIGNED  NOT NULL,
    TSMID          INT UNSIGNED  NOT NULL,
    PRIMARY KEY (RmAttID)
)ENGINE=INNODB
;



-- 
-- TABLE: Remeasurement 
--

CREATE TABLE Remeasurement(
    RemeasureID    INT UNSIGNED  AUTO_INCREMENT,
    CensusID       INT UNSIGNED  NOT NULL,
    StemID         INT UNSIGNED  NOT NULL,
    DBH            FLOAT(7),
    HOM            FLOAT(7),
    ExactDate      DATE,
    PRIMARY KEY (RemeasureID)
)ENGINE=INNODB
;



-- 
-- TABLE: RoleReference 
--

CREATE TABLE RoleReference(
    RoleID         INT UNSIGNED    AUTO_INCREMENT,
    Description    VARCHAR(128),
    PRIMARY KEY (RoleID)
)ENGINE=INNODB
;



-- 
-- TABLE: Site 
--

CREATE TABLE Site(
    PlotID               INT UNSIGNED     AUTO_INCREMENT,
    CountryID            INT UNSIGNED     NOT NULL,
    Area                 DECIMAL(0, 0)    NOT NULL,
    PlotName             CHAR(64),
    LocationName         VARCHAR(128),
    QDimX                DECIMAL(0, 0)    NOT NULL,
    QDimY                DECIMAL(0, 0)    NOT NULL,
    SizeOfSite           CHAR(32),
    ShapeOfSite          CHAR(32),
    DescriptionOfSite    VARCHAR(128),
    GUnit                VARCHAR(32),
    GZUOM                VARCHAR(32),
    PUnit                VARCHAR(32),
    QUnit                VARCHAR(32),
    GCoorCollected       VARCHAR(32),
    PCoorCollected       VARCHAR(32),
    QCoorCollected       VARCHAR(32),
    PRIMARY KEY (PlotID)
)ENGINE=INNODB
;



-- 
-- TABLE: Species 
--

CREATE TABLE Species(
    SpeciesID            INT UNSIGNED     AUTO_INCREMENT,
    CurrentTaxonFlag     DECIMAL(0, 0),
    ObsoleteTaxonFlag    DECIMAL(0, 0),
    GenusID              INT UNSIGNED     NOT NULL,
    ReferenceID          INT UNSIGNED     NOT NULL,
    SpeciesName          CHAR(64),
    Mnemonic             CHAR(10),
    Authority            VARCHAR(128),
    IDLevel              CHAR(8),
    FieldFamily          CHAR(32),
    Description          VARCHAR(128),
    PRIMARY KEY (SpeciesID)
)ENGINE=INNODB
;



-- 
-- TABLE: SpeciesInventory 
--

CREATE TABLE SpeciesInventory(
    SpeciesInvID    INT UNSIGNED  AUTO_INCREMENT,
    CensusID        INT UNSIGNED  NOT NULL,
    PlotID          INT UNSIGNED  NOT NULL,
    SpeciesID       INT UNSIGNED  NOT NULL,
    SubSpeciesID    INT UNSIGNED  NOT NULL,
    PRIMARY KEY (SpeciesInvID)
)ENGINE=INNODB
;



-- 
-- TABLE: Specimen 
--

CREATE TABLE Specimen(
    SpecimenID        INT UNSIGNED     AUTO_INCREMENT,
    TreeID            INT UNSIGNED     NOT NULL,
    Collector         CHAR(64),
    SpecimenNumber    DECIMAL(0, 0),
    SpeciesID         INT UNSIGNED     NOT NULL,
    SubSpeciesID      INT UNSIGNED     NOT NULL,
    Herbarium         CHAR(32),
    Voucher           DECIMAL(0, 0),
    CollectionDate    DATE,
    DeterminedBy      CHAR(64),
    Description       VARCHAR(128),
    PRIMARY KEY (SpecimenID)
)ENGINE=INNODB
;



-- 
-- TABLE: Stem 
--

CREATE TABLE Stem(
    StemID             INT UNSIGNED     AUTO_INCREMENT,
    TreeID             INT UNSIGNED     NOT NULL,
    QuadratID          INT UNSIGNED     NOT NULL,
    StemTag            VARCHAR(32),
    StemDescription    VARCHAR(128),
    StemNumber         DECIMAL(0, 0)    NOT NULL,
    GX                 DECIMAL(0, 0),
    GY                 DECIMAL(0, 0),
    GZ                 DECIMAL(0, 0),
    PX                 DECIMAL(0, 0),
    PY                 CHAR(18),
    PZ                 CHAR(18),
    QX                 DECIMAL(0, 0),
    QY                 DECIMAL(0, 0),
    QZ                 DECIMAL(0, 0),
    Moved              CHAR(1)          NOT NULL,
    PRIMARY KEY (StemID)
)ENGINE=INNODB
;



-- 
-- TABLE: SubSpecies 
--

CREATE TABLE SubSpecies(
    SubSpeciesID          INT UNSIGNED     AUTO_INCREMENT,
    SpeciesID             INT UNSIGNED     NOT NULL,
    CurrentTaxonFlag      DECIMAL(0, 0),
    ObsoleteTaxonFlag     DECIMAL(0, 0),
    SubSpeciesName        CHAR(64),
    Mnemonic              CHAR(10),
    Authority             VARCHAR(128),
    InfraSpecificLevel    CHAR(32),
    PRIMARY KEY (SubSpeciesID)
)ENGINE=INNODB
;



-- 
-- TABLE: Tree 
--

CREATE TABLE Tree(
    TreeID          INT UNSIGNED  AUTO_INCREMENT,
    Tag             CHAR(16),
    SpeciesID       INT UNSIGNED  NOT NULL,
    SubSpeciesID    INT UNSIGNED  NOT NULL,
    PRIMARY KEY (TreeID)
)ENGINE=INNODB
;



-- 
-- TABLE: TreeAttributes 
--

CREATE TABLE TreeAttributes(
    TAttID      INT UNSIGNED  AUTO_INCREMENT,
    CensusID    INT UNSIGNED  NOT NULL,
    TreeID      INT UNSIGNED  NOT NULL,
    TSMID       INT UNSIGNED  NOT NULL,
    PRIMARY KEY (TAttID)
)ENGINE=INNODB
;



-- 
-- TABLE: TreeTaxChange 
--

CREATE TABLE TreeTaxChange(
    ChangeCodeID    INT UNSIGNED    AUTO_INCREMENT,
    Description     VARCHAR(128),
    PRIMARY KEY (ChangeCodeID)
)ENGINE=INNODB
;



-- 
-- TABLE: TSMAttributes 
--

CREATE TABLE TSMAttributes(
    TSMID          INT UNSIGNED    AUTO_INCREMENT,
    TSMCode        CHAR(10)        NOT NULL,
    Description    VARCHAR(128)    NOT NULL,
    PRIMARY KEY (TSMID)
)ENGINE=INNODB
;



-- 
-- TABLE: Census 
--

ALTER TABLE Census ADD CONSTRAINT RefSite2 
    FOREIGN KEY (PlotID)
    REFERENCES Site(PlotID)
;


-- 
-- TABLE: CensusQuadrat 
--

ALTER TABLE CensusQuadrat ADD CONSTRAINT RefCensus18 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE CensusQuadrat ADD CONSTRAINT RefQuadrat65 
    FOREIGN KEY (QuadratID)
    REFERENCES Quadrat(QuadratID)
;


-- 
-- TABLE: Coordinates 
--

ALTER TABLE Coordinates ADD CONSTRAINT RefFeatures52 
    FOREIGN KEY (FeatureID)
    REFERENCES Features(FeatureID)
;

ALTER TABLE Coordinates ADD CONSTRAINT RefSite53 
    FOREIGN KEY (PlotID)
    REFERENCES Site(PlotID)
;

ALTER TABLE Coordinates ADD CONSTRAINT RefQuadrat62 
    FOREIGN KEY (QuadratID)
    REFERENCES Quadrat(QuadratID)
;


-- 
-- TABLE: CurrentObsolete 
--

ALTER TABLE CurrentObsolete ADD CONSTRAINT RefTreeTaxChange27 
    FOREIGN KEY (ChangeCodeID)
    REFERENCES TreeTaxChange(ChangeCodeID)
;

ALTER TABLE CurrentObsolete ADD CONSTRAINT RefSpecies28 
    FOREIGN KEY (SpeciesID)
    REFERENCES Species(SpeciesID)
;

ALTER TABLE CurrentObsolete ADD CONSTRAINT RefSpecies29 
    FOREIGN KEY (ObsoleteSpeciesID)
    REFERENCES Species(SpeciesID)
;


-- 
-- TABLE: DataCollection 
--

ALTER TABLE DataCollection ADD CONSTRAINT RefPersonnelRole73 
    FOREIGN KEY (PersonnelRoleID)
    REFERENCES PersonnelRole(PersonnelRoleID)
;

ALTER TABLE DataCollection ADD CONSTRAINT RefCensus21 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE DataCollection ADD CONSTRAINT RefQuadrat64 
    FOREIGN KEY (QuadratID)
    REFERENCES Quadrat(QuadratID)
;


-- 
-- TABLE: DBH 
--

ALTER TABLE DBH ADD CONSTRAINT RefCensus12 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE DBH ADD CONSTRAINT RefStem13 
    FOREIGN KEY (StemID)
    REFERENCES Stem(StemID)
;


-- 
-- TABLE: DBHAttributes 
--

ALTER TABLE DBHAttributes ADD CONSTRAINT RefDBH7 
    FOREIGN KEY (DBHID)
    REFERENCES DBH(DBHID)
;

ALTER TABLE DBHAttributes ADD CONSTRAINT RefCensus31 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE DBHAttributes ADD CONSTRAINT RefTSMAttributes32 
    FOREIGN KEY (TSMID)
    REFERENCES TSMAttributes(TSMID)
;


-- 
-- TABLE: Family 
--

ALTER TABLE Family ADD CONSTRAINT RefReference24 
    FOREIGN KEY (ReferenceID)
    REFERENCES Reference(ReferenceID)
;


-- 
-- TABLE: Features 
--

ALTER TABLE Features ADD CONSTRAINT RefFeatureTypes51 
    FOREIGN KEY (FeatureTypeID)
    REFERENCES FeatureTypes(FeatureTypeID)
;


-- 
-- TABLE: Genus 
--

ALTER TABLE Genus ADD CONSTRAINT RefFamily25 
    FOREIGN KEY (FamilyID)
    REFERENCES Family(FamilyID)
;

ALTER TABLE Genus ADD CONSTRAINT RefReference30 
    FOREIGN KEY (ReferenceID)
    REFERENCES Reference(ReferenceID)
;


-- 
-- TABLE: Log 
--

ALTER TABLE Log ADD CONSTRAINT RefPersonnel55 
    FOREIGN KEY (PersonnelID)
    REFERENCES Personnel(PersonnelID)
;


-- 
-- TABLE: Measurement 
--

ALTER TABLE Measurement ADD CONSTRAINT RefCensus9 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE Measurement ADD CONSTRAINT RefMeasurementType10 
    FOREIGN KEY (MeasurementTypeID)
    REFERENCES MeasurementType(MeasurementTypeID)
;

ALTER TABLE Measurement ADD CONSTRAINT RefStem11 
    FOREIGN KEY (StemID)
    REFERENCES Stem(StemID)
;


-- 
-- TABLE: MeasurementAttributes 
--

ALTER TABLE MeasurementAttributes ADD CONSTRAINT RefMeasurement8 
    FOREIGN KEY (MeasureID)
    REFERENCES Measurement(MeasureID)
;

ALTER TABLE MeasurementAttributes ADD CONSTRAINT RefCensus58 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE MeasurementAttributes ADD CONSTRAINT RefTSMAttributes59 
    FOREIGN KEY (TSMID)
    REFERENCES TSMAttributes(TSMID)
;


-- 
-- TABLE: PersonnelRole 
--

ALTER TABLE PersonnelRole ADD CONSTRAINT RefRoleReference71 
    FOREIGN KEY (RoleID)
    REFERENCES RoleReference(RoleID)
;

ALTER TABLE PersonnelRole ADD CONSTRAINT RefPersonnel72 
    FOREIGN KEY (PersonnelID)
    REFERENCES Personnel(PersonnelID)
;


-- 
-- TABLE: Quadrat 
--

ALTER TABLE Quadrat ADD CONSTRAINT RefSite60 
    FOREIGN KEY (PlotID)
    REFERENCES Site(PlotID)
;


-- 
-- TABLE: RemeasAttribs 
--

ALTER TABLE RemeasAttribs ADD CONSTRAINT RefCensus34 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE RemeasAttribs ADD CONSTRAINT RefTSMAttributes35 
    FOREIGN KEY (TSMID)
    REFERENCES TSMAttributes(TSMID)
;

ALTER TABLE RemeasAttribs ADD CONSTRAINT RefRemeasurement36 
    FOREIGN KEY (RemeasureID)
    REFERENCES Remeasurement(RemeasureID)
;


-- 
-- TABLE: Remeasurement 
--

ALTER TABLE Remeasurement ADD CONSTRAINT RefCensus14 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE Remeasurement ADD CONSTRAINT RefStem15 
    FOREIGN KEY (StemID)
    REFERENCES Stem(StemID)
;


-- 
-- TABLE: Site 
--

ALTER TABLE Site ADD CONSTRAINT RefCountry37 
    FOREIGN KEY (CountryID)
    REFERENCES Country(CountryID)
;


-- 
-- TABLE: Species 
--

ALTER TABLE Species ADD CONSTRAINT RefReference70 
    FOREIGN KEY (ReferenceID)
    REFERENCES Reference(ReferenceID)
;

ALTER TABLE Species ADD CONSTRAINT RefGenus38 
    FOREIGN KEY (GenusID)
    REFERENCES Genus(GenusID)
;


-- 
-- TABLE: SpeciesInventory 
--

ALTER TABLE SpeciesInventory ADD CONSTRAINT RefCensus39 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE SpeciesInventory ADD CONSTRAINT RefSite40 
    FOREIGN KEY (PlotID)
    REFERENCES Site(PlotID)
;

ALTER TABLE SpeciesInventory ADD CONSTRAINT RefSpecies41 
    FOREIGN KEY (SpeciesID)
    REFERENCES Species(SpeciesID)
;

ALTER TABLE SpeciesInventory ADD CONSTRAINT RefSubSpecies42 
    FOREIGN KEY (SubSpeciesID)
    REFERENCES SubSpecies(SubSpeciesID)
;


-- 
-- TABLE: Specimen 
--

ALTER TABLE Specimen ADD CONSTRAINT RefTree43 
    FOREIGN KEY (TreeID)
    REFERENCES Tree(TreeID)
;

ALTER TABLE Specimen ADD CONSTRAINT RefSpecies44 
    FOREIGN KEY (SpeciesID)
    REFERENCES Species(SpeciesID)
;

ALTER TABLE Specimen ADD CONSTRAINT RefSubSpecies45 
    FOREIGN KEY (SubSpeciesID)
    REFERENCES SubSpecies(SubSpeciesID)
;


-- 
-- TABLE: Stem 
--

ALTER TABLE Stem ADD CONSTRAINT RefTree56 
    FOREIGN KEY (TreeID)
    REFERENCES Tree(TreeID)
;

ALTER TABLE Stem ADD CONSTRAINT RefQuadrat63 
    FOREIGN KEY (QuadratID)
    REFERENCES Quadrat(QuadratID)
;


-- 
-- TABLE: SubSpecies 
--

ALTER TABLE SubSpecies ADD CONSTRAINT RefSpecies47 
    FOREIGN KEY (SpeciesID)
    REFERENCES Species(SpeciesID)
;


-- 
-- TABLE: Tree 
--

ALTER TABLE Tree ADD CONSTRAINT RefSubSpecies69 
    FOREIGN KEY (SubSpeciesID)
    REFERENCES SubSpecies(SubSpeciesID)
;

ALTER TABLE Tree ADD CONSTRAINT RefSpecies68 
    FOREIGN KEY (SpeciesID)
    REFERENCES Species(SpeciesID)
;


-- 
-- TABLE: TreeAttributes 
--

ALTER TABLE TreeAttributes ADD CONSTRAINT RefCensus48 
    FOREIGN KEY (CensusID)
    REFERENCES Census(CensusID)
;

ALTER TABLE TreeAttributes ADD CONSTRAINT RefTSMAttributes49 
    FOREIGN KEY (TSMID)
    REFERENCES TSMAttributes(TSMID)
;

ALTER TABLE TreeAttributes ADD CONSTRAINT RefTree50 
    FOREIGN KEY (TreeID)
    REFERENCES Tree(TreeID)
;