-- 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 */;