--HGVbaseG2P relational schema v1.0 
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AFCAM` (
  `AFCAMID` int(10) unsigned NOT NULL auto_increment,
  `AlleleFrequencyClusterID` int(12) unsigned NOT NULL,
  `AssayMarkerID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`AFCAMID`),
  UNIQUE KEY `AlleleFrequencyClusterID` (`AlleleFrequencyClusterID`,`AssayMarkerID`),
  KEY `AssayMarkerID` (`AssayMarkerID`),
  CONSTRAINT `AFCAM_ibfk_1` FOREIGN KEY (`AlleleFrequencyClusterID`) REFERENCES `AlleleFrequencyCluster` (`AlleleFrequencyClusterID`),
  CONSTRAINT `AFCAM_ibfk_2` FOREIGN KEY (`AssayMarkerID`) REFERENCES `AssayMarker` (`AssayMarkerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AFCS` (
  `AFCSID` int(10) unsigned NOT NULL auto_increment,
  `SignificanceID` int(12) unsigned NOT NULL,
  `AlleleFrequencyClusterID` int(12) unsigned NOT NULL,
  PRIMARY KEY  (`AFCSID`),
  UNIQUE KEY `AlleleSignificanceID` (`SignificanceID`,`AlleleFrequencyClusterID`),
  KEY `AlleleFrequencyClusterID` (`AlleleFrequencyClusterID`),
  CONSTRAINT `AFCS_ibfk_2` FOREIGN KEY (`SignificanceID`) REFERENCES `Significance` (`SignificanceID`) ON DELETE CASCADE,
  CONSTRAINT `AFCS_ibfk_3` FOREIGN KEY (`AlleleFrequencyClusterID`) REFERENCES `AlleleFrequencyCluster` (`AlleleFrequencyClusterID`)
) ENGINE=InnoDB AUTO_INCREMENT=7670095 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `APAPC` (
  `APAPCID` int(10) unsigned NOT NULL auto_increment,
  `AssayedpanelCollectionID` int(10) unsigned NOT NULL,
  `AssayedpanelID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`APAPCID`),
  UNIQUE KEY `AssayedpanelCollectionID` (`AssayedpanelCollectionID`,`AssayedpanelID`),
  KEY `AssayedpanelID` (`AssayedpanelID`),
  CONSTRAINT `APAPC_ibfk_3` FOREIGN KEY (`AssayedpanelCollectionID`) REFERENCES `AssayedpanelCollection` (`AssayedpanelCollectionID`) ON DELETE CASCADE,
  CONSTRAINT `APAPC_ibfk_4` FOREIGN KEY (`AssayedpanelID`) REFERENCES `Assayedpanel` (`AssayedpanelID`)
) ENGINE=InnoDB AUTO_INCREMENT=126 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Allele` (
  `AutoAlleleID` int(10) unsigned NOT NULL auto_increment,
  `AlleleID` varchar(15) default NULL,
  `Status` varchar(20) NOT NULL default 'active',
  `AutoMarkerID` int(10) unsigned NOT NULL default '0',
  `Upstream30bp` varchar(30) NOT NULL default '' COMMENT 'type & strand as in dbSNP, to contextualize alleles',
  `AlleleSeq` text NOT NULL COMMENT 'type & strand as in dbSNP',
  `AlleleSeqDigest` varchar(32) default NULL COMMENT 'MD5 digest of allele seq, to check if seqs are the same or not',
  `Downstream30bp` varchar(30) NOT NULL default '' COMMENT 'type & strand as in dbSNP, to contextualize alleles',
  `SeqType` enum('G','C') NOT NULL default 'G' COMMENT '''G'' (Genomic) or ''C'' (cDNA), as used in dbSNP',
  PRIMARY KEY  (`AutoAlleleID`),
  UNIQUE KEY `AutoMarkerID` (`AutoMarkerID`,`AlleleSeqDigest`),
  CONSTRAINT `Allele_ibfk_1` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=40891740 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AlleleCombo` (
  `AlleleComboID` int(10) unsigned NOT NULL auto_increment,
  `AlleleFrequencyID` int(10) unsigned NOT NULL,
  `AutoAlleleID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`AlleleComboID`),
  UNIQUE KEY `AlleleFrequencyID` (`AlleleFrequencyID`,`AutoAlleleID`),
  KEY `AutoAlleleID` (`AutoAlleleID`),
  CONSTRAINT `AlleleCombo_ibfk_6` FOREIGN KEY (`AlleleFrequencyID`) REFERENCES `AlleleFrequency` (`AlleleFrequencyID`) ON DELETE CASCADE,
  CONSTRAINT `AlleleCombo_ibfk_7` FOREIGN KEY (`AutoAlleleID`) REFERENCES `Allele` (`AutoAlleleID`)
) ENGINE=InnoDB AUTO_INCREMENT=45014078 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AlleleFrequency` (
  `AlleleFrequencyID` int(10) unsigned NOT NULL auto_increment,
  `AlleleFrequencyClusterID` int(12) unsigned NOT NULL default '0',
  `FrequencyAsProportion` decimal(4,3) unsigned NOT NULL,
  PRIMARY KEY  (`AlleleFrequencyID`),
  KEY `AlleleFrequencyClusterID` (`AlleleFrequencyClusterID`),
  CONSTRAINT `AlleleFrequency_ibfk_1` FOREIGN KEY (`AlleleFrequencyClusterID`) REFERENCES `AlleleFrequencyCluster` (`AlleleFrequencyClusterID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=45014083 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AlleleFrequencyCluster` (
  `AlleleFrequencyClusterID` int(12) unsigned NOT NULL auto_increment,
  `UsedmarkersetID` int(10) unsigned NOT NULL,
  `AssayedpanelID` int(10) unsigned NOT NULL default '0',
  `NumberOfGenotypedSamples` smallint(5) NOT NULL,
  PRIMARY KEY  (`AlleleFrequencyClusterID`),
  UNIQUE KEY `AutoUsedmarkersetID` (`UsedmarkersetID`,`AssayedpanelID`),
  KEY `AssayedpanelID` (`AssayedpanelID`),
  CONSTRAINT `AlleleFrequencyCluster_ibfk_2` FOREIGN KEY (`UsedmarkersetID`) REFERENCES `Usedmarkerset` (`UsedmarkersetID`) ON DELETE CASCADE,
  CONSTRAINT `AlleleFrequencyCluster_ibfk_3` FOREIGN KEY (`AssayedpanelID`) REFERENCES `Assayedpanel` (`AssayedpanelID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19662783 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AnalysisMethod` (
  `AnalysisMethodID` int(10) unsigned NOT NULL auto_increment,
  `ExperimentID` int(10) unsigned NOT NULL,
  `MethodName` varchar(200) NOT NULL,
  `MethodDetails` text,
  PRIMARY KEY  (`AnalysisMethodID`),
  UNIQUE KEY `MethodName` (`MethodName`),
  KEY `ExperimentID` (`ExperimentID`),
  CONSTRAINT `AnalysisMethod_ibfk_1` FOREIGN KEY (`ExperimentID`) REFERENCES `Experiment` (`ExperimentID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=274 DEFAULT CHARSET=latin1 COMMENT='Details on analysis methods of various kinds';
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Assay` (
  `AssayID` int(10) unsigned NOT NULL default '0',
  `AutoStudyID` mediumint(8) unsigned NOT NULL,
  `LabID4Assay` varchar(100) NOT NULL default '',
  `Type` varchar(100) default NULL COMMENT 'In (SELECT [Type] FROM [Assay_TypeList]',
  `Method` varchar(250) default NULL COMMENT 'In (SELECT [Method] FROM [Assay_MethodList];)',
  `Conditions` text,
  PRIMARY KEY  (`AssayID`),
  KEY `StudyID` (`AutoStudyID`),
  CONSTRAINT `Assay_ibfk_1` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AssayCitation` (
  `AssayCitationID` int(10) unsigned NOT NULL auto_increment,
  `AssayID` int(10) unsigned NOT NULL,
  `CitationID` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`AssayCitationID`),
  UNIQUE KEY `AssayID` (`AssayID`,`CitationID`),
  KEY `CitationID` (`CitationID`),
  CONSTRAINT `AssayCitation_ibfk_1` FOREIGN KEY (`AssayID`) REFERENCES `Assay` (`AssayID`) ON DELETE CASCADE,
  CONSTRAINT `AssayCitation_ibfk_2` FOREIGN KEY (`CitationID`) REFERENCES `Citation` (`CitationID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AssayMarker` (
  `AssayMarkerID` int(10) unsigned NOT NULL default '0',
  `AssayID` int(10) unsigned NOT NULL default '0',
  `AutoMarkerID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`AssayMarkerID`),
  KEY `AutoMarkerID` (`AutoMarkerID`),
  KEY `AssayID` (`AssayID`),
  CONSTRAINT `assaymarker_ibfk_1` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`),
  CONSTRAINT `assaymarker_ibfk_2` FOREIGN KEY (`AssayID`) REFERENCES `Assay` (`AssayID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AssayMarkerGenotype` (
  `AssayMarkerGenotypeID` int(10) unsigned NOT NULL auto_increment,
  `AssayMarkerID` int(10) unsigned NOT NULL,
  `GenotypeID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`AssayMarkerGenotypeID`),
  UNIQUE KEY `AssayMarkerID` (`AssayMarkerID`,`GenotypeID`),
  KEY `GenotypeID` (`GenotypeID`),
  CONSTRAINT `AssayMarkerGenotype_ibfk_3` FOREIGN KEY (`AssayMarkerID`) REFERENCES `AssayMarker` (`AssayMarkerID`),
  CONSTRAINT `AssayMarkerGenotype_ibfk_4` FOREIGN KEY (`GenotypeID`) REFERENCES `Genotype` (`GenotypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Assay_MethodList` (
  `Method` varchar(250) NOT NULL default '' COMMENT 'initial values: (''ABI 5''-Nuclease'',''Affymetrix 10k array'',''Affymetrix 100k array'',',
  PRIMARY KEY  (`Method`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Assay_TypeList` (
  `Type` varchar(100) NOT NULL COMMENT 'initial values: (''SNP:singleplex'',',
  PRIMARY KEY  (`Type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AssayedGenotype` (
  `AssayMarkerID` int(10) unsigned NOT NULL default '0',
  `GenotypeID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`AssayMarkerID`,`GenotypeID`),
  KEY `GenotypeID` (`GenotypeID`),
  CONSTRAINT `AssayedGenotype_ibfk_1` FOREIGN KEY (`AssayMarkerID`) REFERENCES `AssayMarker` (`AssayMarkerID`),
  CONSTRAINT `AssayedGenotype_ibfk_2` FOREIGN KEY (`GenotypeID`) REFERENCES `Genotype` (`GenotypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Assayedpanel` (
  `AssayedpanelID` int(10) unsigned NOT NULL auto_increment,
  `AutoStudyID` mediumint(8) unsigned NOT NULL,
  `LocalID` varchar(100) NOT NULL COMMENT 'Submitter name for the panel, must be unique within the study',
  `TotalNumberOfIndividuals` int(6) unsigned default NULL COMMENT '*...but required for association datasets',
  `NumberOfSexMale` int(6) unsigned default NULL,
  `NumberOfSexFemale` int(6) unsigned default NULL,
  `NumberOfSexUnknown` int(6) unsigned default NULL,
  `NumberOfProbands` int(6) unsigned default NULL COMMENT 'field used only if Composition = ''Trios''',
  `NumberOfParents` int(6) unsigned default NULL COMMENT 'field used only if Composition = ''Trios''',
  `Description` text,
  PRIMARY KEY  (`AssayedpanelID`),
  UNIQUE KEY `LocalID_2` (`LocalID`,`AutoStudyID`),
  KEY `AutoStudyID` (`AutoStudyID`),
  CONSTRAINT `Assayedpanel_ibfk_2` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1785 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `AssayedpanelCollection` (
  `AssayedpanelCollectionID` int(10) unsigned NOT NULL auto_increment,
  `ExperimentID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`AssayedpanelCollectionID`),
  KEY `ExperimentID` (`ExperimentID`),
  CONSTRAINT `AssayedpanelCollection_ibfk_1` FOREIGN KEY (`ExperimentID`) REFERENCES `Experiment` (`ExperimentID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=60 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Author` (
  `AuthorID` int(10) unsigned NOT NULL auto_increment,
  `ResearcherID` smallint(5) unsigned NOT NULL default '0',
  `AutoStudyID` mediumint(8) unsigned NOT NULL,
  PRIMARY KEY  (`AuthorID`),
  UNIQUE KEY `ResearcherID` (`ResearcherID`,`AutoStudyID`),
  KEY `StudyID` (`AutoStudyID`),
  CONSTRAINT `Author_ibfk_1` FOREIGN KEY (`ResearcherID`) REFERENCES `Researcher` (`ResearcherID`),
  CONSTRAINT `Author_ibfk_2` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`)
) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Build` (
  `BuildID` int(10) unsigned NOT NULL auto_increment,
  `Database` varchar(50) NOT NULL,
  `FullName` varchar(250) default NULL,
  `URL` varchar(250) default NULL,
  `Build` varchar(10) NOT NULL default 'N/A' COMMENT 'Start at 21 for HGVbase-G2P. Latest download for dbSNP',
  `Date` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`BuildID`),
  UNIQUE KEY `Database` (`Database`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `BundleLoci` (
  `BundleLociID` int(6) unsigned NOT NULL auto_increment,
  `BundleName` varchar(100) NOT NULL COMMENT 'Name of the standard bundle of markers',
  `Loci` text NOT NULL COMMENT 'Pipe (',
  PRIMARY KEY  (`BundleLociID`),
  UNIQUE KEY `BundleName` (`BundleName`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Citation` (
  `CitationID` smallint(5) unsigned NOT NULL auto_increment,
  `PubmedID` int(10) unsigned NOT NULL,
  `Authors` varchar(250) default NULL,
  `Title` varchar(250) default NULL,
  `Detail` varchar(250) default NULL,
  `MeshTerms` text,
  PRIMARY KEY  (`CitationID`),
  UNIQUE KEY `PubmedID` (`PubmedID`)
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `CitationCrossref` (
  `CitationCrossrefID` int(10) unsigned NOT NULL auto_increment,
  `CitationID` smallint(5) unsigned NOT NULL default '0',
  `CrossrefID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`CitationCrossrefID`),
  UNIQUE KEY `CitationID` (`CitationID`,`CrossrefID`),
  KEY `CrossrefID` (`CrossrefID`),
  CONSTRAINT `CitationCrossref_ibfk_3` FOREIGN KEY (`CitationID`) REFERENCES `Citation` (`CitationID`) ON DELETE CASCADE,
  CONSTRAINT `CitationCrossref_ibfk_4` FOREIGN KEY (`CrossrefID`) REFERENCES `Crossref` (`CrossrefID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Composition_TypeList` (
  `Type` varchar(100) NOT NULL COMMENT 'initial values: (''Undefined'', ''Unrelated cases'',',
  PRIMARY KEY  (`Type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Crossref` (
  `CrossrefID` int(10) unsigned NOT NULL auto_increment,
  `HotlinkID` int(10) unsigned NOT NULL default '0',
  `UrlID` varchar(100) default '',
  PRIMARY KEY  (`CrossrefID`),
  UNIQUE KEY `HotlinkID` (`HotlinkID`,`UrlID`),
  CONSTRAINT `Crossref_ibfk_1` FOREIGN KEY (`HotlinkID`) REFERENCES `Hotlink` (`HotlinkID`)
) ENGINE=InnoDB AUTO_INCREMENT=1341 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `DiseaseCategory` (
  `DiseaseCategoryID` int(6) unsigned NOT NULL auto_increment,
  `ParentCategoryID` int(6) unsigned default NULL COMMENT 'PK for parent category, if this is not a root term',
  `MeSHTreeID` varchar(30) default NULL COMMENT 'MeSH tree ID, for linking back to NLM website',
  `CategoryName` varchar(100) NOT NULL COMMENT 'Full name of MeSH term',
  PRIMARY KEY  (`DiseaseCategoryID`),
  KEY `ParentCategoryID` (`ParentCategoryID`),
  CONSTRAINT `DiseaseCategory_ibfk_1` FOREIGN KEY (`ParentCategoryID`) REFERENCES `DiseaseCategory` (`DiseaseCategoryID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 COMMENT='Rough disease categories for phenotype properties, mostly ba';
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Experiment` (
  `ExperimentID` int(10) unsigned NOT NULL auto_increment,
  `AutoStudyID` mediumint(8) unsigned NOT NULL,
  `AutoPhenotypeMethodID` int(10) unsigned default NULL,
  `Title` varchar(100) NOT NULL,
  `Type` varchar(100) NOT NULL default '' COMMENT 'In (SELECT [Type] FROM [Experiment_TypeList];)',
  `HotlinkID` int(10) unsigned default NULL,
  `TotalMarkersTested` int(6) unsigned default NULL,
  `Objective` text NOT NULL,
  `Outcome` text NOT NULL,
  `Comments` text,
  `IndividualDataStatement` text,
  `FrequencySummary` text,
  `SignificanceSummary` text,
  `TimeCreated` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ExperimentID`),
  UNIQUE KEY `AutoStudyID` (`AutoStudyID`,`Title`),
  KEY `Title` (`Title`),
  KEY `AutoPhenotypeMethodID` (`AutoPhenotypeMethodID`),
  KEY `HotlinkID` (`HotlinkID`),
  CONSTRAINT `Experiment_ibfk_1` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE,
  CONSTRAINT `Experiment_ibfk_2` FOREIGN KEY (`AutoPhenotypeMethodID`) REFERENCES `PhenotypeMethod` (`AutoPhenotypeMethodID`),
  CONSTRAINT `Experiment_ibfk_3` FOREIGN KEY (`HotlinkID`) REFERENCES `Hotlink` (`HotlinkID`)
) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Experiment_TypeList` (
  `Type` varchar(100) NOT NULL default '' COMMENT 'initial values: (''Polymorphism frequency determination'', ''Case-control association study'',',
  PRIMARY KEY  (`Type`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `GFCAM` (
  `GFCAMID` int(10) unsigned NOT NULL auto_increment,
  `GenotypeFrequencyClusterID` int(10) unsigned NOT NULL,
  `AssayMarkerID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`GFCAMID`),
  UNIQUE KEY `GenotypeFrequencyClusterID` (`GenotypeFrequencyClusterID`,`AssayMarkerID`),
  KEY `AssayMarkerID` (`AssayMarkerID`),
  CONSTRAINT `GFCAM_ibfk_1` FOREIGN KEY (`GenotypeFrequencyClusterID`) REFERENCES `GenotypeFrequencyCluster` (`GenotypeFrequencyClusterID`),
  CONSTRAINT `GFCAM_ibfk_2` FOREIGN KEY (`AssayMarkerID`) REFERENCES `AssayMarker` (`AssayMarkerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `GFCS` (
  `GFCSID` int(10) unsigned NOT NULL auto_increment,
  `SignificanceID` int(12) unsigned NOT NULL,
  `GenotypeFrequencyClusterID` int(12) unsigned NOT NULL,
  PRIMARY KEY  (`GFCSID`),
  UNIQUE KEY `GenotypeSignificanceID` (`SignificanceID`,`GenotypeFrequencyClusterID`),
  KEY `GenotypeFrequencyClusterID` (`GenotypeFrequencyClusterID`),
  CONSTRAINT `GFCS_ibfk_2` FOREIGN KEY (`SignificanceID`) REFERENCES `Significance` (`SignificanceID`) ON DELETE CASCADE,
  CONSTRAINT `GFCS_ibfk_3` FOREIGN KEY (`GenotypeFrequencyClusterID`) REFERENCES `GenotypeFrequencyCluster` (`GenotypeFrequencyClusterID`)
) ENGINE=InnoDB AUTO_INCREMENT=24502023 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Genotype` (
  `GenotypeID` int(10) unsigned NOT NULL auto_increment,
  `AutoMarkerID` int(10) unsigned NOT NULL,
  `GenotypeLabel` varchar(100) NOT NULL,
  PRIMARY KEY  (`GenotypeID`),
  UNIQUE KEY `AutoMarkerID_2` (`AutoMarkerID`,`GenotypeLabel`),
  CONSTRAINT `Genotype_ibfk_1` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`)
) ENGINE=InnoDB AUTO_INCREMENT=4602471 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `GenotypeCombo` (
  `GenotypeComboID` int(10) unsigned NOT NULL auto_increment,
  `GenotypeFrequencyID` int(10) unsigned NOT NULL,
  `GenotypeID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`GenotypeComboID`),
  UNIQUE KEY `GenotypeFrequencyID` (`GenotypeFrequencyID`,`GenotypeID`),
  KEY `GenotypeID` (`GenotypeID`),
  CONSTRAINT `GenotypeCombo_ibfk_1` FOREIGN KEY (`GenotypeFrequencyID`) REFERENCES `GenotypeFrequency` (`GenotypeFrequencyID`) ON DELETE CASCADE,
  CONSTRAINT `GenotypeCombo_ibfk_2` FOREIGN KEY (`GenotypeID`) REFERENCES `Genotype` (`GenotypeID`)
) ENGINE=InnoDB AUTO_INCREMENT=60723925 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `GenotypeFrequency` (
  `GenotypeFrequencyID` int(10) unsigned NOT NULL auto_increment,
  `GenotypeFrequencyClusterID` int(12) unsigned NOT NULL default '0',
  `FrequencyAsProportion` decimal(4,3) unsigned NOT NULL,
  `NumberSamplesWithGenotype` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`GenotypeFrequencyID`),
  KEY `GenotypeFrequencyClusterID` (`GenotypeFrequencyClusterID`),
  CONSTRAINT `GenotypeFrequency_ibfk_1` FOREIGN KEY (`GenotypeFrequencyClusterID`) REFERENCES `GenotypeFrequencyCluster` (`GenotypeFrequencyClusterID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=60723927 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `GenotypeFrequencyCluster` (
  `GenotypeFrequencyClusterID` int(12) unsigned NOT NULL auto_increment,
  `AssayedpanelID` int(10) unsigned NOT NULL,
  `UsedmarkersetID` int(10) unsigned NOT NULL,
  `NumberOfGenotypedSamples` smallint(5) unsigned NOT NULL,
  `PValueHWE` decimal(6,5) unsigned default NULL,
  PRIMARY KEY  (`GenotypeFrequencyClusterID`),
  UNIQUE KEY `AutoMarkersetID` (`UsedmarkersetID`,`AssayedpanelID`),
  KEY `AssayedpanelID` (`AssayedpanelID`),
  CONSTRAINT `GenotypeFrequencyCluster_ibfk_1` FOREIGN KEY (`AssayedpanelID`) REFERENCES `Assayedpanel` (`AssayedpanelID`) ON DELETE CASCADE,
  CONSTRAINT `GenotypeFrequencyCluster_ibfk_2` FOREIGN KEY (`UsedmarkersetID`) REFERENCES `Usedmarkerset` (`UsedmarkersetID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=17749846 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `GenotypedBundle` (
  `GenotypedBundleID` int(10) unsigned NOT NULL auto_increment,
  `BundleLociID` int(6) unsigned NOT NULL,
  `ExperimentID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`GenotypedBundleID`),
  UNIQUE KEY `BundleLociID_2` (`BundleLociID`,`ExperimentID`),
  KEY `ExperimentID` (`ExperimentID`),
  CONSTRAINT `GenotypedBundle_ibfk_4` FOREIGN KEY (`ExperimentID`) REFERENCES `Experiment` (`ExperimentID`) ON DELETE CASCADE,
  CONSTRAINT `GenotypedBundle_ibfk_5` FOREIGN KEY (`BundleLociID`) REFERENCES `BundleLoci` (`BundleLociID`)
) ENGINE=InnoDB AUTO_INCREMENT=117 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `GenotypedLoci` (
  `GenotypedLociID` int(10) unsigned NOT NULL,
  `AutoStudyID` mediumint(8) unsigned NOT NULL,
  `GenotypedLoci` text NOT NULL COMMENT 'Pipe (',
  PRIMARY KEY  (`GenotypedLociID`),
  KEY `StudyID` (`AutoStudyID`),
  CONSTRAINT `GenotypedLoci_ibfk_1` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Genotypedef` (
  `GenotypedefID` int(10) unsigned NOT NULL auto_increment,
  `GenotypeID` int(10) unsigned NOT NULL default '0',
  `AutoAlleleID` int(10) unsigned default NULL COMMENT ' Enter values in at most one',
  `Type` enum('Observed','Ratio','Count','Signal') NOT NULL COMMENT 'Allowed values: "Observed", "Ratio", "Count", "Signal"',
  `Value` varchar(50) NOT NULL COMMENT 'Actual value observed',
  PRIMARY KEY  (`GenotypedefID`),
  KEY `GenotypeID` (`GenotypeID`),
  KEY `AutoAlleleID` (`AutoAlleleID`),
  CONSTRAINT `Genotypedef_ibfk_1` FOREIGN KEY (`AutoAlleleID`) REFERENCES `Allele` (`AutoAlleleID`),
  CONSTRAINT `Genotypedef_ibfk_2` FOREIGN KEY (`GenotypeID`) REFERENCES `Genotype` (`GenotypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `HFCAM` (
  `HFCAMID` int(10) unsigned NOT NULL auto_increment,
  `HaplotypeFrequencyClusterID` int(10) unsigned NOT NULL,
  `AssayMarkerID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`HFCAMID`),
  UNIQUE KEY `HaplotypeFrequencyClusterID` (`HaplotypeFrequencyClusterID`,`AssayMarkerID`),
  KEY `AssayMarkerID` (`AssayMarkerID`),
  CONSTRAINT `HFCAM_ibfk_1` FOREIGN KEY (`HaplotypeFrequencyClusterID`) REFERENCES `HaplotypeFrequencyCluster` (`HaplotypeFrequencyClusterID`),
  CONSTRAINT `HFCAM_ibfk_2` FOREIGN KEY (`AssayMarkerID`) REFERENCES `AssayMarker` (`AssayMarkerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `HFCS` (
  `HFCSID` int(10) unsigned NOT NULL auto_increment,
  `SignificanceID` int(12) unsigned NOT NULL,
  `HaplotypeFrequencyClusterID` int(12) unsigned NOT NULL,
  PRIMARY KEY  (`HFCSID`),
  UNIQUE KEY `HaplotypeSignificanceID` (`SignificanceID`,`HaplotypeFrequencyClusterID`),
  KEY `HaplotypeFrequencyClusterID` (`HaplotypeFrequencyClusterID`),
  CONSTRAINT `HFCS_ibfk_3` FOREIGN KEY (`SignificanceID`) REFERENCES `Significance` (`SignificanceID`) ON DELETE CASCADE,
  CONSTRAINT `HFCS_ibfk_4` FOREIGN KEY (`HaplotypeFrequencyClusterID`) REFERENCES `HaplotypeFrequencyCluster` (`HaplotypeFrequencyClusterID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Haplotype` (
  `AutoHaplotypeID` int(10) unsigned NOT NULL default '0',
  `HaplotypeID` varchar(14) NOT NULL default '' COMMENT '''HAP...''',
  `AutoMarkersetID` int(10) unsigned default NULL,
  `LocalID` varchar(100) NOT NULL,
  PRIMARY KEY  (`AutoHaplotypeID`),
  UNIQUE KEY `HaplotypeID` (`HaplotypeID`),
  KEY `AutoMarkersetID` (`AutoMarkersetID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `HaplotypeAllele` (
  `HaplotypeAlleleID` int(10) unsigned NOT NULL auto_increment,
  `AutoHaplotypeID` int(10) unsigned NOT NULL default '0',
  `AutoAlleleID` int(10) unsigned NOT NULL default '0',
  `PQOrder` tinyint(3) unsigned NOT NULL default '0',
  PRIMARY KEY  (`HaplotypeAlleleID`),
  UNIQUE KEY `AutoHaplotypeID` (`AutoHaplotypeID`,`AutoAlleleID`),
  KEY `AutoAlleleID` (`AutoAlleleID`),
  CONSTRAINT `HaplotypeAllele_ibfk_1` FOREIGN KEY (`AutoHaplotypeID`) REFERENCES `Haplotype` (`AutoHaplotypeID`),
  CONSTRAINT `HaplotypeAllele_ibfk_2` FOREIGN KEY (`AutoAlleleID`) REFERENCES `Allele` (`AutoAlleleID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `HaplotypeCombo` (
  `HaplotypeComboID` int(10) unsigned NOT NULL auto_increment,
  `HaplotypeFrequencyID` int(10) unsigned NOT NULL,
  `AutoHaplotypeID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`HaplotypeComboID`),
  UNIQUE KEY `GenotypeFrequencyID` (`HaplotypeFrequencyID`,`AutoHaplotypeID`),
  KEY `AutoHaplotypeID` (`AutoHaplotypeID`),
  CONSTRAINT `HaplotypeCombo_ibfk_2` FOREIGN KEY (`HaplotypeFrequencyID`) REFERENCES `HaplotypeFrequency` (`HaplotypeFrequencyID`),
  CONSTRAINT `HaplotypeCombo_ibfk_3` FOREIGN KEY (`AutoHaplotypeID`) REFERENCES `Haplotype` (`AutoHaplotypeID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `HaplotypeCrossref` (
  `HaplotypeCrossrefID` int(10) NOT NULL auto_increment,
  `AutoHaplotypeID` int(10) unsigned NOT NULL default '0',
  `CrossrefID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`HaplotypeCrossrefID`),
  UNIQUE KEY `AutoHaplotypeID` (`AutoHaplotypeID`,`CrossrefID`),
  KEY `CrossrefID` (`CrossrefID`),
  CONSTRAINT `HaplotypeCrossref_ibfk_1` FOREIGN KEY (`AutoHaplotypeID`) REFERENCES `Haplotype` (`AutoHaplotypeID`),
  CONSTRAINT `HaplotypeCrossref_ibfk_2` FOREIGN KEY (`CrossrefID`) REFERENCES `Crossref` (`CrossrefID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `HaplotypeFrequency` (
  `HaplotypeFrequencyID` int(10) unsigned NOT NULL auto_increment,
  `HaplotypeFrequencyClusterID` int(12) unsigned NOT NULL default '0',
  `FrequencyAsProportion` decimal(4,3) unsigned NOT NULL COMMENT 'For TDT data, give transmisson rate from heterozygote parents',
  `NumberOfHaplotypes` smallint(5) NOT NULL,
  PRIMARY KEY  (`HaplotypeFrequencyID`),
  KEY `HaplotypeFrequencyClusterID` (`HaplotypeFrequencyClusterID`),
  CONSTRAINT `HaplotypeFrequency_ibfk_1` FOREIGN KEY (`HaplotypeFrequencyClusterID`) REFERENCES `HaplotypeFrequencyCluster` (`HaplotypeFrequencyClusterID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `HaplotypeFrequencyCluster` (
  `HaplotypeFrequencyClusterID` int(12) unsigned NOT NULL auto_increment,
  `AssayedpanelID` int(10) unsigned NOT NULL,
  `UsedmarkersetID` int(12) unsigned NOT NULL,
  `NumberOfGenotypedSamples` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`HaplotypeFrequencyClusterID`),
  KEY `AssayedpanelID` (`AssayedpanelID`),
  KEY `AutoMarkersetID` (`UsedmarkersetID`),
  CONSTRAINT `HaplotypeFrequencyCluster_ibfk_1` FOREIGN KEY (`AssayedpanelID`) REFERENCES `Assayedpanel` (`AssayedpanelID`) ON DELETE CASCADE,
  CONSTRAINT `HaplotypeFrequencyCluster_ibfk_2` FOREIGN KEY (`UsedmarkersetID`) REFERENCES `Usedmarkerset` (`UsedmarkersetID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Hotlink` (
  `HotlinkID` int(10) unsigned NOT NULL auto_increment,
  `HotlinkLabel` varchar(100) NOT NULL COMMENT 'Indicate where the URL is pointing, e.g. "dbSNP refSNP report"',
  `UrlPrefix` varchar(300) NOT NULL,
  `UrlSuffix` varchar(100) default NULL,
  PRIMARY KEY  (`HotlinkID`),
  UNIQUE KEY `HotlinkLabel` (`HotlinkLabel`)
) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MHCAM` (
  `MHCAMID` int(10) unsigned NOT NULL auto_increment,
  `MarkerHeterozygosityClusterID` int(10) unsigned NOT NULL,
  `AssayMarkerID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`MHCAMID`),
  UNIQUE KEY `MarkerHeterozygosityClusterID` (`MarkerHeterozygosityClusterID`,`AssayMarkerID`),
  KEY `AssayMarkerID` (`AssayMarkerID`),
  CONSTRAINT `MHCAM_ibfk_1` FOREIGN KEY (`MarkerHeterozygosityClusterID`) REFERENCES `MarkerHeterozygosityCluster` (`MarkerHeterozygosityClusterID`),
  CONSTRAINT `MHCAM_ibfk_2` FOREIGN KEY (`AssayMarkerID`) REFERENCES `AssayMarker` (`AssayMarkerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MHCS` (
  `MHCSID` int(10) unsigned NOT NULL auto_increment,
  `SignificanceID` int(12) unsigned NOT NULL,
  `MarkerHeterozygosityClusterID` int(12) unsigned NOT NULL,
  PRIMARY KEY  (`MHCSID`),
  UNIQUE KEY `MarkerSignificanceID` (`SignificanceID`,`MarkerHeterozygosityClusterID`),
  KEY `MarkerHeterozygosityClusterID` (`MarkerHeterozygosityClusterID`),
  CONSTRAINT `MHCS_ibfk_3` FOREIGN KEY (`SignificanceID`) REFERENCES `Significance` (`SignificanceID`) ON DELETE CASCADE,
  CONSTRAINT `MHCS_ibfk_4` FOREIGN KEY (`MarkerHeterozygosityClusterID`) REFERENCES `MarkerHeterozygosityCluster` (`MarkerHeterozygosityClusterID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Marker` (
  `AutoMarkerID` int(10) unsigned NOT NULL auto_increment,
  `MarkerID` varchar(15) default NULL COMMENT 'HGVbase-assigned identifier for the marker',
  `Status` varchar(20) NOT NULL default 'active' COMMENT 'Indicates whether the marker is active, dead or withheld or other',
  `VariationType` varchar(30) NOT NULL COMMENT 'SO term for the type of the marker feature',
  `Source` varchar(20) default NULL,
  `LocalID` varchar(20) default NULL,
  `HotlinkID` int(10) unsigned default NULL,
  `Upstream30bp` varchar(30) NOT NULL,
  `AlleleSeqsShorthand` text COMMENT 'shorthand-string with active alleleseqs from the Allele table',
  `Downstream30bp` varchar(30) NOT NULL,
  `ValidationCode` text,
  `AddedFromSourceBuild` varchar(10) default NULL COMMENT 'Label of source database build (or nickname) from where we first got the marker. Example: ''b128'' for dbSNP.',
  `TimeLastTouched` timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (`AutoMarkerID`),
  UNIQUE KEY `SourceDB` (`Source`,`LocalID`),
  UNIQUE KEY `MarkerID` (`MarkerID`),
  KEY `VariationType` (`VariationType`),
  KEY `HotlinkID` (`HotlinkID`),
  KEY `TimeLastTouched` (`TimeLastTouched`),
  KEY `LocalID` (`LocalID`)
) ENGINE=InnoDB AUTO_INCREMENT=22373703 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MarkerAlias` (
  `MarkerAliasID` int(10) unsigned NOT NULL auto_increment,
  `AutoMarkerID` int(10) unsigned NOT NULL,
  `Alias` varchar(50) NOT NULL COMMENT 'One of possibly several external aliases for the marker, e.g. D3S2404 for a microsatellite',
  `IsPrimary` tinyint(1) unsigned default NULL,
  PRIMARY KEY  (`MarkerAliasID`),
  UNIQUE KEY `AutoMarkerID` (`AutoMarkerID`,`IsPrimary`),
  KEY `Alias` (`Alias`),
  CONSTRAINT `MarkerAlias_ibfk_1` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='For looking up by an external alias for a marker (e.g. D3S24';
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MarkerCoord` (
  `MarkerCoordID` int(10) unsigned NOT NULL auto_increment,
  `AutoMarkerID` int(10) unsigned NOT NULL default '0',
  `Chr` varchar(20) NOT NULL COMMENT 'Chromosome that marker maps to. ''MT'' for mitochondrial genome, ''Un'' if unplaced on chromosome',
  `Start` int(10) unsigned default NULL,
  `Stop` int(10) unsigned default NULL,
  `Span` varchar(10) default NULL,
  `Strand` smallint(1) NOT NULL default '0' COMMENT 'Strand orientation relative to the reference sequence: +1 for forward strand, -1 for reverse strand and 0 if not applicable or unknown',
  `MapWeight` varchar(20) default NULL,
  `GenomeBuild` varchar(5) NOT NULL default '',
  `AssemblyType` varchar(30) NOT NULL,
  `AssemblyName` varchar(30) NOT NULL,
  PRIMARY KEY  (`MarkerCoordID`),
  KEY `AutoMarkerID` (`AutoMarkerID`),
  KEY `Chr_2` (`Chr`,`Start`,`Stop`),
  CONSTRAINT `MarkerCoord_ibfk_1` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=59204838 DEFAULT CHARSET=latin1 COMMENT='Markers only represented in this table if mapping data exist';
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MarkerCrossref` (
  `MarkerCrossrefID` int(10) unsigned NOT NULL auto_increment,
  `AutoMarkerID` int(10) unsigned NOT NULL default '0',
  `CrossrefID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`MarkerCrossrefID`),
  UNIQUE KEY `AutoMarkerID` (`AutoMarkerID`,`CrossrefID`),
  KEY `CrossrefID` (`CrossrefID`),
  CONSTRAINT `MarkerCrossref_ibfk_3` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`) ON DELETE CASCADE,
  CONSTRAINT `MarkerCrossref_ibfk_4` FOREIGN KEY (`CrossrefID`) REFERENCES `Crossref` (`CrossrefID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MarkerHeterozygosity` (
  `MarkerHeterozygosityID` int(10) unsigned NOT NULL auto_increment,
  `AutoMarkerID` int(10) unsigned NOT NULL default '0',
  `MarkerHeterozygosityClusterID` int(12) unsigned NOT NULL default '0',
  `HeterozygosityAsProportion` decimal(4,3) unsigned NOT NULL default '0.000',
  `StdErr` decimal(6,5) unsigned default NULL,
  PRIMARY KEY  (`MarkerHeterozygosityID`),
  KEY `MarkerHeterozygosityClusterID` (`MarkerHeterozygosityClusterID`),
  KEY `AutoMarkerID` (`AutoMarkerID`),
  CONSTRAINT `MarkerHeterozygosity_ibfk_4` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`),
  CONSTRAINT `MarkerHeterozygosity_ibfk_5` FOREIGN KEY (`MarkerHeterozygosityClusterID`) REFERENCES `MarkerHeterozygosityCluster` (`MarkerHeterozygosityClusterID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MarkerHeterozygosityCluster` (
  `MarkerHeterozygosityClusterID` int(12) unsigned NOT NULL auto_increment,
  `UsedmarkersetID` int(12) unsigned NOT NULL,
  `NumberOfGenotypedSamples` smallint(5) unsigned NOT NULL,
  PRIMARY KEY  (`MarkerHeterozygosityClusterID`),
  KEY `AutoMarkersetID` (`UsedmarkersetID`),
  CONSTRAINT `MarkerHeterozygosityCluster_ibfk_1` FOREIGN KEY (`UsedmarkersetID`) REFERENCES `Usedmarkerset` (`UsedmarkersetID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MarkerRevision` (
  `MarkerRevisionID` int(10) unsigned NOT NULL auto_increment,
  `AutoMarkerID` int(10) unsigned NOT NULL,
  `AutoReplacedByMarkerID` int(10) unsigned default NULL COMMENT 'Pointer to marker that replaced this one, if applicable.',
  `StrandFlipped` varchar(5) default NULL COMMENT 'Boolean flag to indicate whether the revision resulted in a strand-flip of flanks (and alleles). Mainly happens when one rs# is merged into another rs# in dbSNP',
  `ChangeType` varchar(100) NOT NULL COMMENT 'Change type category (e.g. Deletion, Merging and so on). Can be Unknown if it is something not seen before',
  `ChangeTrigger` varchar(100) NOT NULL COMMENT 'What triggered this particular change. Usually the processing of a particular db-build, but can be something else',
  `TimeCreated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `Comment` varchar(250) default NULL,
  PRIMARY KEY  (`MarkerRevisionID`),
  UNIQUE KEY `AutoMarkerID` (`AutoMarkerID`,`AutoReplacedByMarkerID`),
  KEY `ReplacedByAutoMarkerID` (`AutoReplacedByMarkerID`),
  CONSTRAINT `MarkerRevision_ibfk_3` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2798745 DEFAULT CHARSET=latin1 COMMENT='Tracks marker revision history';
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Markerset` (
  `AutoMarkersetID` int(10) unsigned NOT NULL auto_increment,
  `MarkersetID` varchar(14) default NULL,
  PRIMARY KEY  (`AutoMarkersetID`),
  UNIQUE KEY `MarkersetID` (`MarkersetID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `MarkersetMarker` (
  `MarkersetMarkerID` int(10) unsigned NOT NULL auto_increment,
  `UsedmarkersetID` int(10) unsigned NOT NULL,
  `AutoMarkerID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`MarkersetMarkerID`),
  UNIQUE KEY `AutoMarkersetID` (`UsedmarkersetID`,`AutoMarkerID`),
  KEY `AutoMarkerID` (`AutoMarkerID`),
  CONSTRAINT `MarkersetMarker_ibfk_2` FOREIGN KEY (`UsedmarkersetID`) REFERENCES `Usedmarkerset` (`UsedmarkersetID`) ON DELETE CASCADE,
  CONSTRAINT `MarkersetMarker_ibfk_3` FOREIGN KEY (`AutoMarkerID`) REFERENCES `Marker` (`AutoMarkerID`)
) ENGINE=InnoDB AUTO_INCREMENT=13628384 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Oligo` (
  `OligoID` int(10) unsigned NOT NULL default '0',
  `AssayID` int(10) unsigned NOT NULL default '0',
  `OligoSequence` varchar(250) NOT NULL,
  `Name` varchar(100) default NULL,
  `Modifications` varchar(250) default NULL,
  `Role` varchar(100) default NULL COMMENT '(SELECT [Type] FROM [Oligo_RoleList]',
  PRIMARY KEY  (`OligoID`),
  KEY `AssayID` (`AssayID`),
  CONSTRAINT `oligo_ibfk_1` FOREIGN KEY (`AssayID`) REFERENCES `Assay` (`AssayID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Oligo_RoleList` (
  `Role` varchar(100) NOT NULL default '' COMMENT 'initial values: (''Primer'', ''Probe'')',
  PRIMARY KEY  (`Role`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PVSC` (
  `PVSCID` int(10) unsigned NOT NULL auto_increment,
  `PhenotypeValueID` int(10) unsigned NOT NULL,
  `SelectionCriteriaID` int(8) unsigned NOT NULL,
  PRIMARY KEY  (`PVSCID`),
  UNIQUE KEY `PhenotypeValueID` (`PhenotypeValueID`,`SelectionCriteriaID`),
  KEY `SelectionCriteriaID` (`SelectionCriteriaID`),
  CONSTRAINT `PVSC_ibfk_2` FOREIGN KEY (`PhenotypeValueID`) REFERENCES `PhenotypeValue` (`PhenotypeValueID`),
  CONSTRAINT `PVSC_ibfk_3` FOREIGN KEY (`SelectionCriteriaID`) REFERENCES `SelectionCriteria` (`SelectionCriteriaID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=734 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PhenotypeMethod` (
  `AutoPhenotypeMethodID` int(10) unsigned NOT NULL auto_increment,
  `PhenotypeMethodID` varchar(15) default NULL COMMENT 'HGVbase-assigned identifier',
  `AutoStudyID` mediumint(8) unsigned NOT NULL COMMENT 'Points to study that this method came in',
  `PhenotypePropertyID` int(10) unsigned NOT NULL,
  `MethodName` varchar(100) default NULL COMMENT 'Formal/Standard name for the assessment or test',
  `Sample` varchar(100) default NULL COMMENT 'Biological system or sample type being assessed',
  `MeasuredAttribute` varchar(100) default NULL COMMENT 'i.e., analyte/feature & property being tested, E.g., Alzheimers\n Disease state, NaCl concentration',
  `VariableType` varchar(25) NOT NULL COMMENT 'Kind of variable: "Continuous", "nominal" or "ordinal"',
  `Unit` varchar(25) default NULL COMMENT 'Unit of measurement (e.g. kg)',
  `Circumstance` text COMMENT 'Any particular conditions under which the test is done',
  `TimeInstant` text COMMENT 'Either; when was the assessment done',
  `TimePeriod` text COMMENT 'Or; over what period was the assessment done',
  `Details` text COMMENT 'Any other specific details of the test',
  PRIMARY KEY  (`AutoPhenotypeMethodID`),
  UNIQUE KEY `PhenotypeMethodID2` (`PhenotypeMethodID`),
  UNIQUE KEY `MethodName` (`MethodName`,`AutoStudyID`),
  KEY `PhenotypePropertyID` (`PhenotypePropertyID`),
  KEY `StudyID` (`AutoStudyID`),
  CONSTRAINT `PhenotypeMethod_ibfk_2` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE,
  CONSTRAINT `PhenotypeMethod_ibfk_3` FOREIGN KEY (`PhenotypePropertyID`) REFERENCES `PhenotypeProperty` (`PhenotypePropertyID`)
) ENGINE=InnoDB AUTO_INCREMENT=153 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PhenotypeMethodCitation` (
  `PhenotypeMethodCitationID` int(10) unsigned NOT NULL auto_increment,
  `AutoPhenotypeMethodID` int(10) unsigned NOT NULL default '0',
  `CitationID` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`PhenotypeMethodCitationID`),
  UNIQUE KEY `PhenotypeMethodID` (`AutoPhenotypeMethodID`,`CitationID`),
  KEY `CitationID` (`CitationID`),
  CONSTRAINT `PhenotypeMethodCitation_ibfk_2` FOREIGN KEY (`AutoPhenotypeMethodID`) REFERENCES `PhenotypeMethod` (`AutoPhenotypeMethodID`) ON DELETE CASCADE,
  CONSTRAINT `PhenotypeMethodCitation_ibfk_3` FOREIGN KEY (`CitationID`) REFERENCES `Citation` (`CitationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PhenotypeMethodCrossref` (
  `PhenotypeMethodCrossrefID` int(10) unsigned NOT NULL auto_increment,
  `AutoPhenotypeMethodID` int(10) unsigned NOT NULL,
  `CrossrefID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`PhenotypeMethodCrossrefID`),
  UNIQUE KEY `AutoPhenotypeMethodID` (`AutoPhenotypeMethodID`,`CrossrefID`),
  KEY `CrossrefID` (`CrossrefID`),
  CONSTRAINT `PhenotypeMethodCrossref_ibfk_1` FOREIGN KEY (`AutoPhenotypeMethodID`) REFERENCES `PhenotypeMethod` (`AutoPhenotypeMethodID`) ON DELETE CASCADE,
  CONSTRAINT `PhenotypeMethodCrossref_ibfk_2` FOREIGN KEY (`CrossrefID`) REFERENCES `Crossref` (`CrossrefID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PhenotypeProperty` (
  `PhenotypePropertyID` int(10) unsigned NOT NULL auto_increment,
  `PropertyName` varchar(100) NOT NULL,
  `DiseaseCategoryID` int(6) unsigned default NULL,
  `Comment` text,
  PRIMARY KEY  (`PhenotypePropertyID`),
  UNIQUE KEY `PropertyName` (`PropertyName`),
  KEY `DiseaseCategoryID` (`DiseaseCategoryID`),
  CONSTRAINT `PhenotypeProperty_ibfk_1` FOREIGN KEY (`DiseaseCategoryID`) REFERENCES `DiseaseCategory` (`DiseaseCategoryID`)
) ENGINE=InnoDB AUTO_INCREMENT=46 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PhenotypePropertyCitation` (
  `PhenotypePropertyCitationID` int(10) unsigned NOT NULL auto_increment,
  `PhenotypePropertyID` int(10) unsigned NOT NULL default '0',
  `CitationID` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`PhenotypePropertyCitationID`),
  UNIQUE KEY `PhenotypePropertyID` (`PhenotypePropertyID`,`CitationID`),
  KEY `CitationID` (`CitationID`),
  CONSTRAINT `PhenotypePropertyCitation_ibfk_2` FOREIGN KEY (`PhenotypePropertyID`) REFERENCES `PhenotypeProperty` (`PhenotypePropertyID`),
  CONSTRAINT `PhenotypePropertyCitation_ibfk_3` FOREIGN KEY (`CitationID`) REFERENCES `Citation` (`CitationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PhenotypeValue` (
  `PhenotypeValueID` int(10) unsigned NOT NULL auto_increment,
  `AutoPhenotypeMethodID` int(10) unsigned NOT NULL COMMENT 'Reference to the method used to generate this value',
  `SamplepanelID` int(10) unsigned default NULL COMMENT 'Reference to the Samplepanel that was measured with the method to get this value (if applicable; not all values are connected to a panel)',
  `Value` varchar(500) NOT NULL COMMENT 'The actual result from the measurement (returned by the assay)  for the individuals on the panel. Can be a mean if variable is continuous (e.g. mean weight)',
  `ValueRank` int(3) unsigned default NULL COMMENT 'Rank of this value, if this is an ordinal (or ranked) variable.',
  `ValueIsMean` varchar(3) default NULL COMMENT 'Flag indicating whether the value is a mean (for a continuous variable) or not',
  `StdDev` float unsigned default NULL COMMENT 'Standard deviation (for continuous variables)',
  `Median` float unsigned default NULL COMMENT 'Median (for continuous variables)',
  `Min` float unsigned default NULL COMMENT 'Minimum value (for continuous variables)',
  `Max` float unsigned default NULL COMMENT 'Maximum value (for continuous variables)',
  `NumberOfIndividuals` int(6) unsigned default NULL COMMENT 'Number of individuals with this value if variable is discreet, or contributing to mean (if variable is continuous)',
  `Qualifier` varchar(100) default NULL COMMENT 'Can be used for setting thresholds (e.g. weight > 80kg) when splitting up panels.',
  PRIMARY KEY  (`PhenotypeValueID`),
  UNIQUE KEY `AutoPhenotypeMethodID` (`AutoPhenotypeMethodID`,`SamplepanelID`,`Value`),
  KEY `PhenotypeMethodID` (`AutoPhenotypeMethodID`),
  KEY `SamplepanelID` (`SamplepanelID`),
  CONSTRAINT `PhenotypeValue_ibfk_1` FOREIGN KEY (`AutoPhenotypeMethodID`) REFERENCES `PhenotypeMethod` (`AutoPhenotypeMethodID`) ON DELETE CASCADE,
  CONSTRAINT `PhenotypeValue_ibfk_2` FOREIGN KEY (`SamplepanelID`) REFERENCES `Samplepanel` (`SamplepanelID`)
) ENGINE=InnoDB AUTO_INCREMENT=806 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PhenotypeValueDetail` (
  `PhenotypeValueDetailID` int(10) unsigned NOT NULL auto_increment,
  `PhenotypeValueID` int(10) unsigned NOT NULL default '0',
  `Attribute` varchar(100) NOT NULL default '',
  `PhenotypeValueDetail` varchar(150) NOT NULL,
  `UnitOfMeasurement` varchar(30) default NULL,
  `Qualifier` varchar(100) default NULL,
  PRIMARY KEY  (`PhenotypeValueDetailID`),
  UNIQUE KEY `Attribute` (`Attribute`,`PhenotypeValueID`),
  KEY `PhenotypeValueID` (`PhenotypeValueID`),
  CONSTRAINT `PhenotypeValueDetail_ibfk_1` FOREIGN KEY (`PhenotypeValueID`) REFERENCES `PhenotypeValue` (`PhenotypeValueID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `PrevalenceInSamplepanel` (
  `PrevalenceInSamplepanelID` int(10) unsigned NOT NULL auto_increment,
  `SamplepanelID` int(10) unsigned NOT NULL,
  `PhenotypeValueID` int(10) unsigned NOT NULL,
  `NumberWithPhenotype` smallint(5) unsigned default NULL,
  PRIMARY KEY  (`PrevalenceInSamplepanelID`),
  UNIQUE KEY `PhenotypeValueID` (`PhenotypeValueID`),
  KEY `SamplepanelID` (`SamplepanelID`),
  CONSTRAINT `PrevalenceInSamplepanel_ibfk_3` FOREIGN KEY (`SamplepanelID`) REFERENCES `Samplepanel` (`SamplepanelID`) ON DELETE CASCADE,
  CONSTRAINT `PrevalenceInSamplepanel_ibfk_4` FOREIGN KEY (`PhenotypeValueID`) REFERENCES `PhenotypeValue` (`PhenotypeValueID`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `ReplacedMarker` (
  `AutoReplacedMarker` int(10) unsigned NOT NULL default '0' COMMENT 'Incrementing number that tracks the order that entries were added',
  `OldMarkerID` varchar(12) NOT NULL default '' COMMENT '''SNP...'' or ''IND...'' or ''STR...'' or ''MXD...'' or ''MNP...'' or ''GEN...''',
  `NewMarkerID` varchar(12) default NULL COMMENT '''SNP...'' or ''IND...'' or ''STR...'' or ''MXD...'' or ''MNP...'' or ''GEN...''',
  `ChangeType` varchar(20) NOT NULL default '' COMMENT '''Deletion'' or ''Merging'' or ''UnDeletion'' or ''UnMerging'' or ''VariantTypeChange''',
  `Trigger` varchar(50) NOT NULL default '' COMMENT 'Specifies what event triggered this entry in this table',
  PRIMARY KEY  (`AutoReplacedMarker`),
  KEY `OldMarkerID` (`OldMarkerID`),
  KEY `NewMarkerID` (`NewMarkerID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Researcher` (
  `ResearcherID` smallint(5) unsigned NOT NULL auto_increment,
  `SubmitterHandle` varchar(20) default NULL,
  `ShortName` varchar(100) NOT NULL,
  `FullName` varchar(200) default NULL,
  `Department` varchar(100) default NULL,
  `Institution` varchar(100) NOT NULL,
  `Address` varchar(250) default NULL,
  `Phone` varchar(50) default NULL,
  `Fax` varchar(30) default NULL,
  `Email` varchar(150) default NULL,
  `WWW` varchar(100) default NULL,
  PRIMARY KEY  (`ResearcherID`),
  UNIQUE KEY `ShortName` (`ShortName`),
  UNIQUE KEY `SubmitterHandle` (`SubmitterHandle`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Samplepanel` (
  `SamplepanelID` int(10) unsigned NOT NULL auto_increment,
  `AutoStudyID` mediumint(8) unsigned NOT NULL,
  `ParentSamplepanelID` int(10) unsigned default NULL,
  `LocalID` varchar(100) NOT NULL COMMENT 'Submitter name for the panel, must be unique within the study',
  `SummaryDescription` text,
  `Composition` varchar(100) default NULL COMMENT 'In (SELECT [Composition] FROM [CompositionList];)',
  `TotalNumberOfIndividuals` int(6) unsigned default NULL COMMENT '*...but required for association datasets',
  `NumberOfSexMale` int(6) unsigned default NULL,
  `NumberOfSexFemale` int(6) unsigned default NULL,
  `NumberOfSexUnknown` int(6) unsigned default NULL,
  `NumberOfProbands` int(6) unsigned default NULL COMMENT 'field used only if Composition = ''Trios''',
  `NumberOfParents` int(6) unsigned default NULL COMMENT 'field used only if Composition = ''Trios''',
  `ModeOfRecruitment` varchar(250) default NULL,
  `DiagnosisAgeRange` varchar(150) default NULL,
  `DiagnosisPeriod` varchar(150) default NULL,
  `SamplingAgeRange` varchar(150) default NULL,
  `SamplingPeriod` varchar(150) default NULL,
  `PopulationInfo` varchar(250) default NULL,
  `GeographicRegionInfo` varchar(250) default NULL,
  `EthnicityInfo` varchar(250) default NULL,
  `BirthPlaceInfo` varchar(250) default NULL,
  `AdmixtureInfo` varchar(250) default NULL,
  `EnvironmentInfo` text,
  `SourceOfDNA` varchar(100) default NULL COMMENT 'In (SELECT [SourceOfDNA] FROM [Sampleset_SourceOfDNAList];)',
  `DNAsArePooled` enum('Undefined','Pre-prep','Post-prep','No') NOT NULL default 'Undefined',
  `DNAsAreWGA` enum('Undefined','None','All','Some') NOT NULL default 'Undefined',
  PRIMARY KEY  (`SamplepanelID`),
  UNIQUE KEY `LocalID` (`LocalID`,`AutoStudyID`),
  KEY `StudyID` (`AutoStudyID`),
  KEY `ParentSamplepanelID` (`ParentSamplepanelID`),
  CONSTRAINT `Samplepanel_ibfk_1` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE,
  CONSTRAINT `Samplepanel_ibfk_2` FOREIGN KEY (`ParentSamplepanelID`) REFERENCES `Samplepanel` (`SamplepanelID`)
) ENGINE=InnoDB AUTO_INCREMENT=1691 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `SamplepanelCrossref` (
  `SamplepanelCrossrefID` int(10) unsigned NOT NULL auto_increment,
  `SamplepanelID` int(10) unsigned NOT NULL,
  `CrossrefID` int(10) unsigned NOT NULL,
  PRIMARY KEY  (`SamplepanelCrossrefID`),
  UNIQUE KEY `SamplepanelID` (`SamplepanelID`,`CrossrefID`),
  KEY `CrossrefID` (`CrossrefID`),
  CONSTRAINT `SamplepanelCrossref_ibfk_5` FOREIGN KEY (`SamplepanelID`) REFERENCES `Samplepanel` (`SamplepanelID`) ON DELETE CASCADE,
  CONSTRAINT `SamplepanelCrossref_ibfk_6` FOREIGN KEY (`CrossrefID`) REFERENCES `Crossref` (`CrossrefID`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1410 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `SamplepanelOverlap` (
  `SamplepanelOverlapID` smallint(5) unsigned NOT NULL default '0',
  `SamplepanelID_New` int(10) unsigned NOT NULL default '0' COMMENT 'This ID is the current Sampleset',
  `SamplepanelID_Old` int(10) unsigned NOT NULL default '0' COMMENT 'This ID is the overlapping Sampleset',
  `OverlapDetails` varchar(250) default 'This samplepanel overlaps with another',
  PRIMARY KEY  (`SamplepanelOverlapID`),
  KEY `SamplesetID_New` (`SamplepanelID_New`),
  KEY `SamplesetID_Old` (`SamplepanelID_Old`),
  CONSTRAINT `SamplepanelOverlap_ibfk_1` FOREIGN KEY (`SamplepanelID_New`) REFERENCES `Samplepanel` (`SamplepanelID`),
  CONSTRAINT `SamplepanelOverlap_ibfk_2` FOREIGN KEY (`SamplepanelID_Old`) REFERENCES `Samplepanel` (`SamplepanelID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Samplepanel_SourceOfDNAList` (
  `SourceOfDNA` varchar(100) NOT NULL default '' COMMENT 'initial values: (''Cultured cells'',''Tissue biopsy''',
  PRIMARY KEY  (`SourceOfDNA`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `SelectionCriteria` (
  `SelectionCriteriaID` int(8) unsigned NOT NULL auto_increment,
  `AssayedpanelID` int(10) unsigned NOT NULL,
  `SamplepanelID` int(10) unsigned default NULL,
  `SourceAssayedpanelID` int(10) unsigned default NULL COMMENT 'Points to assayedpanel (usually more than one) that a given apanel is made up from',
  `EnvironmentCriteria` varchar(500) default 'No selection',
  `NumberOfIndividuals` int(6) unsigned default NULL,
  PRIMARY KEY  (`SelectionCriteriaID`),
  UNIQUE KEY `AssayedpanelID` (`AssayedpanelID`,`SamplepanelID`),
  UNIQUE KEY `SourceAssayedpanelID` (`SourceAssayedpanelID`,`AssayedpanelID`),
  KEY `SamplepanelID` (`SamplepanelID`),
  CONSTRAINT `SelectionCriteria_ibfk_16` FOREIGN KEY (`AssayedpanelID`) REFERENCES `Assayedpanel` (`AssayedpanelID`) ON DELETE CASCADE,
  CONSTRAINT `SelectionCriteria_ibfk_17` FOREIGN KEY (`SamplepanelID`) REFERENCES `Samplepanel` (`SamplepanelID`) ON DELETE CASCADE,
  CONSTRAINT `SelectionCriteria_ibfk_18` FOREIGN KEY (`SourceAssayedpanelID`) REFERENCES `Assayedpanel` (`AssayedpanelID`)
) ENGINE=InnoDB AUTO_INCREMENT=1652 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Significance` (
  `SignificanceID` int(12) unsigned NOT NULL auto_increment,
  `AssayedpanelCollectionID` int(10) unsigned NOT NULL,
  `UsedmarkersetID` int(12) unsigned NOT NULL,
  `AnalysisMethodID` int(10) unsigned NOT NULL COMMENT 'Which method was used in the analysis',
  `UnadjustedPValue` float unsigned default NULL,
  `AdjustedPValue` float unsigned default NULL,
  `OddsRatioStatement` varchar(250) default NULL,
  `AttributableRiskStatement` varchar(250) default NULL,
  PRIMARY KEY  (`SignificanceID`),
  UNIQUE KEY `AnalysisMethodID` (`AnalysisMethodID`,`UsedmarkersetID`),
  KEY `AssayedpanelCollectionID` (`AssayedpanelCollectionID`),
  KEY `UsedmarkersetID` (`UsedmarkersetID`),
  KEY `UnadjustedPValue` (`UnadjustedPValue`),
  CONSTRAINT `Significance_ibfk_22` FOREIGN KEY (`AssayedpanelCollectionID`) REFERENCES `AssayedpanelCollection` (`AssayedpanelCollectionID`) ON DELETE CASCADE,
  CONSTRAINT `Significance_ibfk_23` FOREIGN KEY (`UsedmarkersetID`) REFERENCES `Usedmarkerset` (`UsedmarkersetID`) ON DELETE CASCADE,
  CONSTRAINT `Significance_ibfk_24` FOREIGN KEY (`AnalysisMethodID`) REFERENCES `AnalysisMethod` (`AnalysisMethodID`)
) ENGINE=InnoDB AUTO_INCREMENT=14563822 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Study` (
  `AutoStudyID` mediumint(8) unsigned NOT NULL auto_increment,
  `StudyID` varchar(15) default NULL COMMENT 'HGVbase-assigned identifier',
  `IsHidden` char(5) default NULL,
  `ResearcherID` smallint(5) unsigned default NULL,
  `Title` varchar(200) NOT NULL,
  `Abstract` text NOT NULL,
  `Background` text,
  `Objectives` text NOT NULL,
  `KeyResults` text NOT NULL,
  `Conclusions` text NOT NULL,
  `StudyDesign` text,
  `StudySizeReason` text,
  `StudyPower` text,
  `SourcesOfBias` text,
  `Limitations` text,
  `Acknowledgements` text,
  `TimeCreated` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `TimeUpdated` timestamp NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`AutoStudyID`),
  UNIQUE KEY `Title` (`Title`),
  UNIQUE KEY `StudyID2` (`StudyID`),
  KEY `ResearcherID` (`ResearcherID`),
  CONSTRAINT `Study_ibfk_1` FOREIGN KEY (`ResearcherID`) REFERENCES `Researcher` (`ResearcherID`)
) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=latin1 COMMENT='A DATASET COMPRISING ALL EXPERIMENTS RELEVANT TO ONE RESEARC';
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `StudyCitation` (
  `StudyCitationID` int(10) unsigned NOT NULL auto_increment,
  `AutoStudyID` mediumint(8) unsigned NOT NULL default '0',
  `CitationID` smallint(5) unsigned NOT NULL default '0',
  PRIMARY KEY  (`StudyCitationID`),
  UNIQUE KEY `StudyID` (`AutoStudyID`,`CitationID`),
  KEY `CitationID` (`CitationID`),
  CONSTRAINT `StudyCitation_ibfk_2` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE,
  CONSTRAINT `StudyCitation_ibfk_3` FOREIGN KEY (`CitationID`) REFERENCES `Citation` (`CitationID`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `StudyCrossref` (
  `StudyCrossrefID` int(10) unsigned NOT NULL auto_increment,
  `AutoStudyID` mediumint(8) unsigned NOT NULL default '0',
  `CrossrefID` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`StudyCrossrefID`),
  UNIQUE KEY `StudyID` (`AutoStudyID`,`CrossrefID`),
  KEY `CrossrefID` (`CrossrefID`),
  CONSTRAINT `StudyCrossref_ibfk_4` FOREIGN KEY (`AutoStudyID`) REFERENCES `Study` (`AutoStudyID`) ON DELETE CASCADE,
  CONSTRAINT `StudyCrossref_ibfk_5` FOREIGN KEY (`CrossrefID`) REFERENCES `Crossref` (`CrossrefID`)
) ENGINE=InnoDB AUTO_INCREMENT=58 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `StudyOverlap` (
  `StudyOverlapID` smallint(5) unsigned NOT NULL,
  `AutoStudyID_New` mediumint(8) unsigned NOT NULL COMMENT 'This ID is the current Study ',
  `AutoStudyID_Old` mediumint(8) unsigned NOT NULL COMMENT 'This ID is the overlapping Study',
  `OverlapDetails` varchar(50) NOT NULL default 'This Study overlaps with another',
  PRIMARY KEY  (`StudyOverlapID`),
  UNIQUE KEY `AutoStudyID_New` (`AutoStudyID_New`,`AutoStudyID_Old`),
  KEY `AutoStudyID_Old` (`AutoStudyID_Old`),
  CONSTRAINT `StudyOverlap_ibfk_1` FOREIGN KEY (`AutoStudyID_New`) REFERENCES `Study` (`AutoStudyID`),
  CONSTRAINT `StudyOverlap_ibfk_2` FOREIGN KEY (`AutoStudyID_Old`) REFERENCES `Study` (`AutoStudyID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;
SET @saved_cs_client     = @@character_set_client;
SET character_set_client = utf8;
CREATE TABLE `Usedmarkerset` (
  `UsedmarkersetID` int(10) unsigned NOT NULL auto_increment,
  `ExperimentID` int(10) unsigned NOT NULL,
  `AutoMarkersetID` int(10) unsigned default NULL,
  `LocalID` varchar(50) NOT NULL,
  PRIMARY KEY  (`UsedmarkersetID`),
  UNIQUE KEY `ExperimentID` (`ExperimentID`,`LocalID`),
  KEY `LocalID` (`LocalID`),
  KEY `AutoMarkersetID` (`AutoMarkersetID`),
  CONSTRAINT `Usedmarkerset_ibfk_1` FOREIGN KEY (`ExperimentID`) REFERENCES `Experiment` (`ExperimentID`) ON DELETE CASCADE,
  CONSTRAINT `Usedmarkerset_ibfk_2` FOREIGN KEY (`AutoMarkersetID`) REFERENCES `Markerset` (`AutoMarkersetID`)
) ENGINE=InnoDB AUTO_INCREMENT=13628385 DEFAULT CHARSET=latin1;
SET character_set_client = @saved_cs_client;