-- phpMyAdmin SQL Dump
-- version 3.4.6
-- http://www.phpmyadmin.net
--
-- Host: 127.0.0.1
-- Generation Time: Oct 19, 2011 at 06:00 PM
-- Server version: 5.5.16
-- PHP Version: 5.3.6

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `bien2_foreign_keys`
--

-- --------------------------------------------------------

--
-- Table structure for table `bien_genus_family`
--

CREATE TABLE IF NOT EXISTS `bien_genus_family` (
  `family` varchar(250) CHARACTER SET utf8 NOT NULL,
  `genus` varchar(150) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `tot_families` int(1) DEFAULT NULL,
  KEY `genus` (`genus`),
  KEY `family` (`family`),
  KEY `tot_families` (`tot_families`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `clade`
--

CREATE TABLE IF NOT EXISTS `clade` (
  `cladeID` int(11) unsigned NOT NULL,
  `parentCladeID` int(11) unsigned DEFAULT NULL,
  `cladeNameStd` varchar(250) NOT NULL,
  `rank` varchar(50) DEFAULT NULL,
  `isHybrid` int(1) unsigned NOT NULL,
  `family` varchar(100) DEFAULT NULL,
  `leftIndex` int(11) unsigned DEFAULT NULL,
  `rightIndex` int(11) unsigned DEFAULT NULL,
  `source` varchar(25) DEFAULT NULL,
  `majorPlantGroup` varchar(45) DEFAULT NULL,
  `embryophytes` varchar(45) DEFAULT NULL,
  `vascularPlants` varchar(45) DEFAULT NULL,
  `seedPlants` varchar(45) DEFAULT NULL,
  `floweringPlants` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`cladeID`) USING BTREE,
  KEY `nameStaging_isHybrid` (`isHybrid`),
  KEY `nameStaging_family` (`family`),
  KEY `nameStaging_leftIndex` (`leftIndex`),
  KEY `nameStaging_rightIndex` (`rightIndex`),
  KEY `idx_source` (`source`),
  KEY `nameStaging_rank` (`rank`) USING BTREE,
  KEY `Index_majorPlantGroup` (`majorPlantGroup`) USING BTREE,
  KEY `Index_embryophytes` (`embryophytes`) USING BTREE,
  KEY `Index_seedPlants` (`seedPlants`) USING BTREE,
  KEY `Index_vascularPlants` (`vascularPlants`) USING BTREE,
  KEY `Index_floweringPlants` (`floweringPlants`) USING BTREE,
  KEY `nameStaging_parentCladeID` (`parentCladeID`) USING BTREE,
  KEY `nameStaging_cladeNameStd` (`cladeNameStd`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `cladeName`
--

CREATE TABLE IF NOT EXISTS `cladeName` (
  `cladeNameID` int(11) NOT NULL AUTO_INCREMENT,
  `cladeID` int(10) unsigned NOT NULL,
  `cladeName` varchar(250) NOT NULL,
  `cladeNameUnique` varchar(300) DEFAULT NULL,
  `nameStatus` varchar(50) DEFAULT NULL,
  `source` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`cladeNameID`) USING BTREE,
  KEY `idx_cladeID` (`cladeID`) USING BTREE,
  KEY `Index_source` (`source`),
  KEY `idx_name` (`cladeName`) USING BTREE,
  KEY `idx_nameStatus` (`nameStatus`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `DataSource`
--

CREATE TABLE IF NOT EXISTS `DataSource` (
  `DataSourceID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DataSourceName` varchar(255) NOT NULL,
  `DataSourceType` varchar(255) NOT NULL,
  PRIMARY KEY (`DataSourceID`),
  KEY `DataSourceName` (`DataSourceName`),
  KEY `DataSourceType` (`DataSourceType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data sources for plots or specimens';

-- --------------------------------------------------------

--
-- Table structure for table `geoIndividualObservation`
--

CREATE TABLE IF NOT EXISTS `geoIndividualObservation` (
  `ID` int(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID',
  `countryISO` varchar(25) DEFAULT NULL,
  `countryStd` varchar(150) DEFAULT NULL,
  `isInCountry` int(1) DEFAULT NULL,
  `countryDistError` double DEFAULT NULL,
  `stateProvinceStd` varchar(150) DEFAULT NULL,
  `isInStateProvince` int(1) DEFAULT NULL,
  `stateProvinceDistError` double DEFAULT NULL,
  `countyParishStd` varchar(150) DEFAULT NULL,
  `isInCountyParish` int(1) DEFAULT NULL,
  `countyParishDistError` double DEFAULT NULL,
  `isValidLatLong` int(1) DEFAULT NULL,
  `isCultivated` int(1) DEFAULT '0',
  `isCultivatedReason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `geoI_countryISO_NDX` (`countryISO`),
  KEY `geoI_countryStd_NDX` (`countryStd`),
  KEY `geoI_isInCountry_NDX` (`isInCountry`),
  KEY `geoI_countryDistError_NDX` (`countryDistError`),
  KEY `geoI_stateProvinceStd_NDX` (`stateProvinceStd`),
  KEY `geoI_isInStateProvince_NDX` (`isInStateProvince`),
  KEY `geoI_stateProvinceDistError_NDX` (`stateProvinceDistError`),
  KEY `geoI_countyParishStd_NDX` (`countyParishStd`),
  KEY `geoI_isInCountyParish_NDX` (`isInCountyParish`),
  KEY `geoI_countyParishDistError_NDX` (`countyParishDistError`),
  KEY `geoI_isValidLatLong_NDX` (`isValidLatLong`),
  KEY `geoI_isCultivated` (`isCultivated`),
  KEY `geoI_isCultivatedReason` (`isCultivatedReason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `geoPlotMetaDataDimension`
--

CREATE TABLE IF NOT EXISTS `geoPlotMetaDataDimension` (
  `ID` int(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID',
  `countryISO` varchar(25) DEFAULT NULL,
  `countryStd` varchar(150) DEFAULT NULL,
  `isInCountry` int(1) DEFAULT NULL,
  `countryDistError` double DEFAULT NULL,
  `stateProvinceStd` varchar(150) DEFAULT NULL,
  `isInStateProvince` int(1) DEFAULT NULL,
  `stateProvinceDistError` double DEFAULT NULL,
  `countyParishStd` varchar(150) DEFAULT NULL,
  `isInCountyParish` int(1) DEFAULT NULL,
  `countyParishDistError` double DEFAULT NULL,
  `isValidLatLong` int(1) DEFAULT NULL,
  `isCultivated` int(1) DEFAULT '0',
  `isCultivatedReason` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `geoP_countryISO_NDX` (`countryISO`),
  KEY `geoP_countryStd_NDX` (`countryStd`),
  KEY `geoP_isInCountry_NDX` (`isInCountry`),
  KEY `geoP_countryDistError_NDX` (`countryDistError`),
  KEY `geoP_stateProvinceStd_NDX` (`stateProvinceStd`),
  KEY `geoP_isInStateProvince_NDX` (`isInStateProvince`),
  KEY `geoP_stateProvinceDistError_NDX` (`stateProvinceDistError`),
  KEY `geoP_countyParishStd_NDX` (`countyParishStd`),
  KEY `geoP_isInCountyParish_NDX` (`isInCountyParish`),
  KEY `geoP_countyParishDistError_NDX` (`countyParishDistError`),
  KEY `geoP_isValidLatLong_NDX` (`isValidLatLong`),
  KEY `geoP_isCultivated` (`isCultivated`),
  KEY `geo_isCultivatedReason` (`isCultivatedReason`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `IndividualObservation`
--

CREATE TABLE IF NOT EXISTS `IndividualObservation` (
  `ObservationID` int(11) NOT NULL AUTO_INCREMENT,
  `ObservationType` int(11) DEFAULT NULL,
  `PlotTreeTag` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `DBPlotID` int(11) DEFAULT NULL,
  `SpecimenCatalogNO` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ObservationSpecimenID` int(11) DEFAULT NULL,
  `LatestDateCollected` date DEFAULT NULL,
  `DecimalLatitude` decimal(10,5) DEFAULT NULL,
  `DecimalLongitude` decimal(10,5) DEFAULT NULL,
  `NumberOfStems` int(11) DEFAULT NULL,
  `BasalDiameter` decimal(10,5) DEFAULT NULL,
  `Height` decimal(10,5) DEFAULT NULL,
  `TaxonomyID` int(11) NOT NULL,
  `RawTreeID` int(10) DEFAULT NULL,
  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`ObservationID`),
  KEY `RefPlotMetaDataDimension411` (`DBPlotID`),
  KEY `RefTaxonDimension381` (`TaxonomyID`),
  KEY `ObservationSpecimenID` (`ObservationSpecimenID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `IndividualObservationMeasurement`
--

CREATE TABLE IF NOT EXISTS `IndividualObservationMeasurement` (
  `ObservationID` int(11) NOT NULL,
  `StemNO` int(11) NOT NULL AUTO_INCREMENT,
  `ObservationNO` int(11) NOT NULL,
  `PrimaryStem` int(11) DEFAULT NULL,
  `DBH` decimal(10,5) DEFAULT NULL,
  `HOM` decimal(10,5) DEFAULT NULL,
  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`StemNO`),
  UNIQUE KEY `ObservationID` (`ObservationID`,`StemNO`,`ObservationNO`),
  KEY `RefIndividualObservation11` (`ObservationID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `ObservationSpecimen`
--

CREATE TABLE IF NOT EXISTS `ObservationSpecimen` (
  `ObservationSpecimenID` int(11) NOT NULL AUTO_INCREMENT,
  `DBSourceID` int(11) NOT NULL,
  `Continent` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Country` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `StateProvince` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `verbatimCountry` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `County` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `Locality` varchar(1024) COLLATE utf8_bin DEFAULT NULL,
  `Collector` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `IdentifiedBy` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `IdentifiedDate` date DEFAULT NULL,
  `CoordinateUncertainty` decimal(10,5) DEFAULT NULL,
  `CoordinateUncertaintyUnits` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `VegetationCategory` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `test` tinyint(3) unsigned NOT NULL,
  `Cultivated` int(1) NOT NULL,
  `CollectionNumber` varchar(24) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`ObservationSpecimenID`),
  KEY `RefSpecimenSourceData421` (`DBSourceID`),
  KEY `DBSourceID` (`DBSourceID`,`Continent`,`Country`,`StateProvince`,`County`,`Locality`(255),`Collector`(255),`IdentifiedBy`,`IdentifiedDate`,`CoordinateUncertainty`,`CoordinateUncertaintyUnits`,`VegetationCategory`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `PlotAggregateFact`
--

CREATE TABLE IF NOT EXISTS `PlotAggregateFact` (
  `DBPlotID` int(11) NOT NULL,
  `PlotTimeID` int(11) NOT NULL,
  `TotalIndividuals` int(11) DEFAULT NULL,
  `TotalIndividualsAbove1cm` int(11) DEFAULT NULL,
  `TotalIndividualsAbove2.5cm` int(11) DEFAULT NULL,
  `TotalIndividualsAbove10cm` int(11) DEFAULT NULL,
  `PercentCover` decimal(10,5) DEFAULT NULL,
  `TaxonomyID` int(11) NOT NULL,
  `del` int(1) unsigned DEFAULT NULL,
  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`DBPlotID`,`PlotTimeID`,`TaxonomyID`),
  KEY `RefPlotMetaDataDimension61` (`DBPlotID`),
  KEY `RefPlotTimeDimension371` (`PlotTimeID`),
  KEY `RefTaxonDimension181` (`TaxonomyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `PlotMetaDataDimension`
--

CREATE TABLE IF NOT EXISTS `PlotMetaDataDimension` (
  `DBPlotID` int(11) NOT NULL AUTO_INCREMENT,
  `DBSourceName` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ContactName` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ContactEmail` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ContactTelephone` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `Continent` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Country` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `StateProvince` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `verbatimCountry` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `County` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Locality` varchar(256) COLLATE utf8_bin DEFAULT NULL,
  `VegetationCategory` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `PlotCD` bigint(32) DEFAULT NULL,
  `PlotName` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `PlotCensusMethod` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `PlotArea` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `PlotAreaUnits` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `PlotShape` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `PlotDecimalLatitude` decimal(10,5) DEFAULT NULL,
  `PlotDecimalLongitude` decimal(10,5) DEFAULT NULL,
  `CoordinateUncertainty` decimal(10,5) DEFAULT NULL,
  `CoordinateUncertaintyUnits` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `PlotReferenceX` decimal(10,5) DEFAULT NULL,
  `PlotReferenceY` decimal(10,5) DEFAULT NULL,
  `PlotMaxDBH` decimal(10,5) DEFAULT NULL,
  `PlotMinDBH` decimal(10,5) DEFAULT NULL,
  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`DBPlotID`),
  KEY `DBSourceName` (`DBSourceName`),
  KEY `PlotName_2` (`PlotName`,`DBSourceName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `PlotTimeDimension`
--

CREATE TABLE IF NOT EXISTS `PlotTimeDimension` (
  `PlotTimeID` int(11) NOT NULL AUTO_INCREMENT,
  `CensusID` int(11) DEFAULT NULL,
  `CensusName` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `CensusStartDate` date DEFAULT NULL,
  `CensusEndDate` date DEFAULT NULL,
  `DBPlotID` int(11) NOT NULL,
  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`PlotTimeID`),
  KEY `Ref613` (`DBPlotID`),
  KEY `CensusName_2` (`CensusName`,`DBPlotID`,`CensusID`),
  KEY `CensusName_5` (`CensusName`,`CensusID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `SpecimenSourceData`
--

CREATE TABLE IF NOT EXISTS `SpecimenSourceData` (
  `DBSourceID` int(11) NOT NULL AUTO_INCREMENT,
  `DBSourceName` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `ContactName` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ContactEmail` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `ContactTelephone` varchar(32) COLLATE utf8_bin DEFAULT NULL,
  `SpecimenInstitutionCD` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `SpecimenCollectionCD` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`DBSourceID`),
  KEY `DBSourceName` (`DBSourceName`),
  KEY `SpecimenInstitutionCD` (`SpecimenInstitutionCD`,`SpecimenCollectionCD`,`DBSourceName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `TaxonDataSource`
--

CREATE TABLE IF NOT EXISTS `TaxonDataSource` (
  `TaxonDataSourceID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `DataSourceID` int(10) unsigned NOT NULL,
  `TaxonomyID` int(11) NOT NULL,
  `TotalObservations` int(10) unsigned DEFAULT NULL,
  `GeoreferencedObservations` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`TaxonDataSourceID`),
  KEY `DataSourceID` (`DataSourceID`),
  KEY `TaxonomyID` (`TaxonomyID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Links taxa to data sources in which they are used';

-- --------------------------------------------------------

--
-- Table structure for table `TaxonDimension`
--

CREATE TABLE IF NOT EXISTS `TaxonDimension` (
  `TaxonomyID` int(11) NOT NULL AUTO_INCREMENT,
  `FamilyName` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `GenusName` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `SpecificEpithet` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `InfraspecificEpithet` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `InfraspecificRank` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Authority` varchar(128) COLLATE utf8_bin DEFAULT NULL,
  `is_plotName` int(1) unsigned DEFAULT '0',
  `taxonAuthorityVerbatim` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `taxonMatched` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `taxonMatchedAuthority` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `unmatchedTerms` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `matchScore` decimal(3,2) DEFAULT NULL,
  `taxonMatchedAcceptance` varchar(1) COLLATE utf8_bin DEFAULT NULL,
  `familyCorrected` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `genusCorrected` varchar(150) COLLATE utf8_bin DEFAULT NULL,
  `speciesCorrected` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `taxonCorrected` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `authorityCorrected` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `taxonCorrectedTnrsID` int(10) unsigned DEFAULT NULL,
  `taxonCorrectedRank` varchar(25) COLLATE utf8_bin DEFAULT NULL,
  `taxonCorrectedAcceptance` varchar(1) COLLATE utf8_bin DEFAULT NULL,
  `morphospecies` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `taxonMorphospecies` varchar(250) COLLATE utf8_bin DEFAULT NULL,
  `is_scrubbed` int(1) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`TaxonomyID`),
  KEY `FamilyName` (`FamilyName`),
  KEY `GenusName` (`GenusName`),
  KEY `SpecificEpithet` (`SpecificEpithet`),
  KEY `InfraspecificRank` (`InfraspecificRank`),
  KEY `FamilyName_2` (`FamilyName`,`GenusName`,`SpecificEpithet`,`InfraspecificEpithet`,`InfraspecificRank`,`Authority`),
  KEY `InfraspecificEpithet` (`InfraspecificEpithet`),
  KEY `taxonAuthorityVerbatim` (`taxonAuthorityVerbatim`),
  KEY `taxonCorrected` (`taxonCorrected`),
  KEY `taxonMorphospecies` (`taxonMorphospecies`),
  KEY `is_scrubbed` (`is_scrubbed`),
  KEY `TD_familyCorrected` (`familyCorrected`),
  KEY `TD_taxonMatchedAcceptance` (`taxonMatchedAcceptance`),
  KEY `TD_taxonCorrectedAcceptance` (`taxonCorrectedAcceptance`),
  KEY `TD_taxonCorrectedRank` (`taxonCorrectedRank`),
  KEY `taxonCorrectedTnrsID` (`taxonCorrectedTnrsID`),
  KEY `TD_is_plotName` (`is_plotName`),
  KEY `genusCorrected_idx` (`genusCorrected`),
  KEY `speciesCorrected` (`speciesCorrected`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin ROW_FORMAT=FIXED;

-- --------------------------------------------------------

--
-- Table structure for table `TaxonObservation`
--

CREATE TABLE IF NOT EXISTS `TaxonObservation` (
  `TaxonomyObservationID` int(11) NOT NULL AUTO_INCREMENT,
  `TaxonomyID` int(11) DEFAULT NULL,
  `PlotOrSpecimenFlag` int(11) DEFAULT NULL,
  `DBPlotID` int(11) NOT NULL,
  `DBSourceID` int(11) NOT NULL,
  `GBIFScientificNameOriginal` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `GBIFFamilyOriginal` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `GBIFGenusOriginal` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `test` tinyint(3) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (`TaxonomyObservationID`),
  KEY `RefTaxonDimension301` (`TaxonomyID`),
  KEY `Ref614` (`DBPlotID`),
  KEY `TaxonomyID_2` (`TaxonomyID`,`DBPlotID`),
  KEY `DBSourceID` (`DBSourceID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

--
-- Table structure for table `TraitObservation`
--

CREATE TABLE IF NOT EXISTS `TraitObservation` (
  `TraitObservationID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `TaxonomyID` int(11) NOT NULL,
  `Family` varchar(100) DEFAULT NULL,
  `Genus` varchar(100) DEFAULT NULL,
  `SpecificEpithet` varchar(100) DEFAULT NULL,
  `InfraspecificRank` varchar(25) DEFAULT NULL,
  `InfraspecificEpithet` varchar(100) DEFAULT NULL,
  `Taxon` varchar(255) DEFAULT NULL,
  `AuthorOfScientificName` varchar(255) DEFAULT NULL,
  `TaxonAuthor` varchar(255) DEFAULT NULL,
  `Habit` varchar(50) DEFAULT NULL,
  `TraitName` varchar(200) DEFAULT NULL,
  `TraitValue` varchar(100) DEFAULT NULL,
  `Unit` varchar(100) DEFAULT NULL,
  `Method` varchar(255) DEFAULT NULL,
  `Region` varchar(100) DEFAULT NULL,
  `Country` varchar(100) DEFAULT NULL,
  `StateProvince` varchar(100) DEFAULT NULL,
  `LowerPolitical` varchar(100) DEFAULT NULL,
  `LocalityDescription` varchar(100) DEFAULT NULL,
  `VerbatimLatitude` varchar(150) DEFAULT NULL,
  `VerbatimLongitude` varchar(150) DEFAULT NULL,
  `VerbatimElevation` varchar(150) DEFAULT NULL,
  `Source` varchar(100) DEFAULT NULL,
  `URLSource` varchar(100) DEFAULT NULL,
  `SourceCitation` varchar(1000) DEFAULT NULL,
  `SourceID` varchar(255) DEFAULT NULL,
  `VisitingDate` varchar(100) DEFAULT NULL,
  `ReferenceID` int(11) unsigned DEFAULT NULL,
  `Access` varchar(100) DEFAULT NULL,
  `Project_PI` varchar(255) DEFAULT NULL,
  `Project_PI_contact` varchar(100) DEFAULT NULL,
  `Observation` varchar(255) DEFAULT NULL,
  `Authorship` varchar(255) DEFAULT NULL,
  `AuthorshipContact` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`TraitObservationID`),
  KEY `TaxonomyID` (`TaxonomyID`),
  KEY `Family` (`Family`),
  KEY `Taxon` (`Taxon`),
  KEY `TraitName` (`TraitName`),
  KEY `ReferenceID` (`ReferenceID`),
  KEY `TaxonAuthor` (`TaxonAuthor`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Species trait observations';

-- --------------------------------------------------------

--
-- Table structure for table `viewFullOccurrence`
--

CREATE TABLE IF NOT EXISTS `viewFullOccurrence` (
  `OccurID` int(11) NOT NULL AUTO_INCREMENT,
  `DBPlotID` int(11) NOT NULL,
  `ObservationID` int(11) DEFAULT NULL,
  `Family` varchar(64) DEFAULT NULL,
  `TaxonomyID` int(11) NOT NULL,
  `OrigGenus` varchar(64) DEFAULT NULL,
  `OrigSpecies` varchar(64) DEFAULT NULL,
  `TaxonCorrected` varchar(250) DEFAULT NULL,
  `Genus` varchar(64) DEFAULT NULL,
  `Species` varchar(64) DEFAULT NULL,
  `Latin` varchar(64) DEFAULT NULL,
  `Rank` varchar(16) DEFAULT NULL,
  `Accepted` varchar(2) DEFAULT NULL,
  `TaxonMorphoSpecies` varchar(255) DEFAULT NULL,
  `isCultivated` int(1) DEFAULT '0',
  `isCultivatedReason` varchar(255) DEFAULT NULL,
  `CountryOrig` varchar(100) DEFAULT NULL,
  `CountryStd` varchar(150) DEFAULT NULL,
  `CountryError` double DEFAULT NULL,
  `ProvinceStd` varchar(64) DEFAULT NULL,
  `ProvinceError` double DEFAULT NULL,
  `County` varchar(64) DEFAULT NULL,
  `Plot` varchar(32) DEFAULT NULL,
  `PlotArea` varchar(10) DEFAULT NULL,
  `Latitude` decimal(10,5) DEFAULT NULL,
  `Longitude` decimal(10,5) DEFAULT NULL,
  `isValidLatLong` int(1) DEFAULT NULL,
  `Date` date DEFAULT NULL,
  `SurveyType` varchar(10) DEFAULT NULL,
  `Abund` int(11) DEFAULT NULL,
  `Abund1` int(11) DEFAULT NULL,
  `Abund2.5` int(11) DEFAULT NULL,
  `Abund10` int(11) DEFAULT NULL,
  `PctCover` decimal(10,5) DEFAULT NULL,
  `DataSource` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`OccurID`),
  KEY `DBPlotID` (`DBPlotID`),
  KEY `ObservationID` (`ObservationID`),
  KEY `Family` (`Family`(16)),
  KEY `TaxonCorrected` (`TaxonCorrected`(25)),
  KEY `Latin` (`Latin`(25)),
  KEY `TaxonomyID` (`TaxonomyID`),
  KEY `Genus` (`Genus`(16)),
  KEY `Species` (`Species`(20)),
  KEY `ORIGLATIN` (`OrigGenus`(16),`OrigSpecies`(20)),
  KEY `CountryStd` (`CountryStd`),
  KEY `SurveyType` (`SurveyType`),
  KEY `DataSource` (`DataSource`),
  KEY `Rank` (`Rank`),
  KEY `Accepted` (`Accepted`),
  KEY `isCultivated` (`isCultivated`),
  KEY `isCultivatedReason` (`isCultivatedReason`),
  KEY `isValidLatLong` (`isValidLatLong`),
  KEY `CountryError` (`CountryError`),
  KEY `ProvinceError` (`ProvinceError`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `cladeName`
--
ALTER TABLE `cladeName`
  ADD CONSTRAINT `cladeName_ibfk_1` FOREIGN KEY (`cladeID`) REFERENCES `clade` (`cladeID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `geoIndividualObservation`
--
ALTER TABLE `geoIndividualObservation`
  ADD CONSTRAINT `geoindividualobservation_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `PlotMetaDataDimension` (`DBPlotID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `geoPlotMetaDataDimension`
--
ALTER TABLE `geoPlotMetaDataDimension`
  ADD CONSTRAINT `geoplotmetadatadimension_ibfk_1` FOREIGN KEY (`ID`) REFERENCES `PlotMetaDataDimension` (`DBPlotID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `IndividualObservation`
--
ALTER TABLE `IndividualObservation`
  ADD CONSTRAINT `individualobservation_ibfk_3` FOREIGN KEY (`TaxonomyID`) REFERENCES `TaxonDimension` (`TaxonomyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `individualobservation_ibfk_1` FOREIGN KEY (`ObservationSpecimenID`) REFERENCES `ObservationSpecimen` (`ObservationSpecimenID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `individualobservation_ibfk_2` FOREIGN KEY (`DBPlotID`) REFERENCES `PlotMetaDataDimension` (`DBPlotID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `IndividualObservationMeasurement`
--
ALTER TABLE `IndividualObservationMeasurement`
  ADD CONSTRAINT `IndividualObservationMeasurement_ibfk_1` FOREIGN KEY (`ObservationID`) REFERENCES `IndividualObservation` (`ObservationID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `ObservationSpecimen`
--
ALTER TABLE `ObservationSpecimen`
  ADD CONSTRAINT `observationspecimen_ibfk_1` FOREIGN KEY (`DBSourceID`) REFERENCES `SpecimenSourceData` (`DBSourceID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `PlotAggregateFact`
--
ALTER TABLE `PlotAggregateFact`
  ADD CONSTRAINT `plotaggregatefact_ibfk_3` FOREIGN KEY (`TaxonomyID`) REFERENCES `TaxonDimension` (`TaxonomyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `plotaggregatefact_ibfk_1` FOREIGN KEY (`DBPlotID`) REFERENCES `PlotMetaDataDimension` (`DBPlotID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `plotaggregatefact_ibfk_2` FOREIGN KEY (`PlotTimeID`) REFERENCES `PlotTimeDimension` (`PlotTimeID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `PlotTimeDimension`
--
ALTER TABLE `PlotTimeDimension`
  ADD CONSTRAINT `plottimedimension_ibfk_1` FOREIGN KEY (`DBPlotID`) REFERENCES `PlotMetaDataDimension` (`DBPlotID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `TaxonDataSource`
--
ALTER TABLE `TaxonDataSource`
  ADD CONSTRAINT `TaxonDataSource_ibfk_2` FOREIGN KEY (`TaxonomyID`) REFERENCES `TaxonDimension` (`TaxonomyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `TaxonDataSource_ibfk_1` FOREIGN KEY (`DataSourceID`) REFERENCES `DataSource` (`DataSourceID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `TaxonObservation`
--
ALTER TABLE `TaxonObservation`
  ADD CONSTRAINT `taxonobservation_ibfk_3` FOREIGN KEY (`TaxonomyID`) REFERENCES `TaxonDimension` (`TaxonomyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `taxonobservation_ibfk_1` FOREIGN KEY (`DBPlotID`) REFERENCES `PlotMetaDataDimension` (`DBPlotID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `taxonobservation_ibfk_2` FOREIGN KEY (`DBSourceID`) REFERENCES `SpecimenSourceData` (`DBSourceID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `TraitObservation`
--
ALTER TABLE `TraitObservation`
  ADD CONSTRAINT `traitobservation_ibfk_1` FOREIGN KEY (`TaxonomyID`) REFERENCES `TaxonDimension` (`TaxonomyID`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `viewFullOccurrence`
--
ALTER TABLE `viewFullOccurrence`
  ADD CONSTRAINT `viewFullOccurrence_ibfk_1` FOREIGN KEY (`ObservationID`) REFERENCES `IndividualObservation` (`ObservationID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `viewFullOccurrence_ibfk_3` FOREIGN KEY (`DBPlotID`) REFERENCES `PlotMetaDataDimension` (`DBPlotID`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `viewFullOccurrence_ibfk_2` FOREIGN KEY (`TaxonomyID`) REFERENCES `TaxonDimension` (`TaxonomyID`) ON DELETE CASCADE ON UPDATE CASCADE;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;