-- MySQL dump 9.10
--
-- Host: localhost Database: db_webqtl
-- ------------------------------------------------------
-- Server version 4.0.18-standard
--
-- Table structure for table `AvgMethod`
-- Provides a name for the method used to average microarray probe values
--
CREATE TABLE AvgMethod (
Id smallint(5) unsigned NOT NULL auto_increment,
Name char(30) NOT NULL default '',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `Data`
-- All phenotype and genotype values are stored in value field of this table
-- genotypes are stored as integer-valued doubles
-- The Id and StrainId fields link to other tables
--
CREATE TABLE Data (
Id int(10) unsigned NOT NULL default '0',
StrainId smallint(5) unsigned NOT NULL default '0',
value double NOT NULL default '0',
UNIQUE KEY DataId (Id,StrainId)
) TYPE=MyISAM;
--
-- Table structure for table `GeneChip`
-- Provides a name for the microarray used for gene expression assay (U74Av2, M430)
--
CREATE TABLE GeneChip (
Id smallint(5) unsigned NOT NULL auto_increment,
Name char(30) NOT NULL default '',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `Geno`
-- Name and characteristics of genetic markers for which Data table has genotypes
-- Name: Marker name
-- Chr: Chromosome
-- MB: Physical location (in Mb) on chromosome
-- GMB: Physical location (in Mb) in genome
--
CREATE TABLE Geno (
id int(10) unsigned NOT NULL auto_increment,
Name char(20) NOT NULL default '',
Chr char(6) default NULL,
MB double default NULL,
GMB double default NULL,
PRIMARY KEY (id),
UNIQUE KEY Name (Name)
) TYPE=MyISAM;
--
-- Table structure for table `GenoFreeze`
-- Information characterizing a dated version of genotype data
-- Name: Internal identifier (no spaces)
-- FullName: Most descriptive name
-- ShortName: Short name version for use where space is limited
-- CreateTime: Creation date and time
-- public: Visibility restriction (0 -> invisible, 1 -> visible on beta site only, >1 -> public)
--
CREATE TABLE GenoFreeze (
Id smallint(5) unsigned NOT NULL auto_increment,
Name varchar(100) NOT NULL default '',
FullName varchar(100) NOT NULL default '',
ShortName varchar(100) NOT NULL default '',
CreateTime date NOT NULL default '2001-01-01',
public tinyint(4) NOT NULL default '0',
InbredSetId smallint(5) unsigned default '1',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `GenoXRef`
-- Linking table to connect marker information and version information with genotypes in Data table
--
CREATE TABLE GenoXRef (
GenoFreezeId smallint(5) unsigned NOT NULL default '0',
GenoId int(10) unsigned NOT NULL default '0',
DataId int(10) unsigned NOT NULL default '0',
UNIQUE KEY ProbeSetId (GenoFreezeId,GenoId)
) TYPE=MyISAM;
--
-- Table structure for table `InbredSet`
-- Provides a name for a recombinant inbred set or progeny of a cross
--
CREATE TABLE InbredSet (
Id smallint(5) unsigned NOT NULL auto_increment,
Name char(30) NOT NULL default '',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `Probe`
-- Characteristics of microarray probes
-- Name: X-Y location of probe spot on microarray
-- Sequence: nucleotide sequence
-- ExonNo: exon number of gene in which the target sequence appears
-- SerialOrder: position of this probe in relation to others of probe set
-- Tm: melting temperature calculated for DNA of the probe sequence
-- E_GSB: gene-specific binding energy calculated by Zhang and Miles
-- E_NSB: non-specific binding energy calculated by Zhang and Miles
-- Zhang L, Miles MF, and Aldape KD (2003). A model of molecular interactions on short oligonucleotide microarrays. Nat Biotechnol 21: 818-21.
--
-- See http://www.webqtl.org/cgi-bin/WebQTL.py?FormID=showProbeInfo&database=U74Av2Mas5_December03&ProbeSetID=101312_at&CellID=&RISet=BXD&parentsf1=ON
--
CREATE TABLE Probe (
Id int(10) unsigned NOT NULL auto_increment,
ProbeSetId int(10) unsigned NOT NULL default '0',
Name char(20) default NULL,
Sequence char(30) default NULL,
ExonNo char(7) default NULL,
SerialOrder double default NULL,
Tm double default NULL,
E_GSB double default NULL,
E_NSB double default NULL,
PRIMARY KEY (Id),
UNIQUE KEY ProbeSetId (ProbeSetId,Name),
KEY SerialOrder (ProbeSetId,SerialOrder)
) TYPE=MyISAM;
--
-- Table structure for table `ProbeH2`
-- h2: raw heritability estimate for probe data
-- weight: relative heritability among all probes of probeset
--
CREATE TABLE ProbeH2 (
ProbeFreezeId smallint(5) unsigned NOT NULL default '0',
ProbeId int(10) unsigned NOT NULL default '0',
h2 double default NULL,
weight double default NULL,
UNIQUE KEY ProbeId (ProbeFreezeId,ProbeId)
) TYPE=MyISAM;
--
-- Table structure for table `ProbeFreeze`
-- Information characterizing a dated version of expression data
-- Name: Internal identifier (no spaces)
-- FullName: Most descriptive name
-- ShortName: Short name version for use where space is limited
-- CreateTime: Creation date and time
--
CREATE TABLE ProbeFreeze (
Id smallint(5) unsigned NOT NULL auto_increment,
ChipId smallint(5) unsigned NOT NULL default '0',
TissueId smallint(5) unsigned NOT NULL default '0',
Name varchar(100) NOT NULL default '',
FullName varchar(100) NOT NULL default '',
ShortName varchar(100) NOT NULL default '',
CreateTime date NOT NULL default '0000-00-00',
InbredSetId smallint(5) unsigned default '1',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `ProbeSet`
-- Information characterizing the a probe set of an Affymetrix microarray
-- Name: Affymetrix probe set code
-- symbol: gene symbol
-- description: Affymetrix gene annotation
-- chromosome: chr location of gene
-- MB: physical chromosomal location according to public assembly
-- MB_celera: physical chromosomal location according to Celera
-- GMB: physical genomic location according to public assembly
-- alias: gene name aliases
-- LocusId: Identifier for gene at Entrez Gene
-- GenbankId: Identier for sequence at Entrez Nucleotide
-- SNP: ?? not currently used
-- BlatSeq: concatenation of probe sequences used for verifying genomic location by database search
--
CREATE TABLE ProbeSet (
Id int(10) unsigned NOT NULL auto_increment,
ChipId smallint(5) unsigned NOT NULL default '0',
Name varchar(40) NOT NULL default '',
symbol varchar(40) default NULL,
description varchar(255) default NULL,
chromosome char(3) default NULL,
MB double default NULL,
MB_celera double default NULL,
GMB double default NULL,
alias varchar(255) default '',
LocusId varchar(20) default NULL,
GenbankId varchar(10) default NULL,
SNP int(2) default NULL,
BlatSeq text NOT NULL,
PRIMARY KEY (Id),
UNIQUE KEY ProbeSetId (ChipId,Name),
KEY NameIndex (Name)
) TYPE=MyISAM;
--
-- Table structure for table `ProbeSetFreeze`
-- Information characterizing a dated version of expression data
-- including the averaging method used to combine probe-specific values
--
-- Name: Internal identifier (no spaces)
-- FullName: Most descriptive name
-- ShortName: Short name version for use where space is limited
-- CreateTime: Creation date and time
-- public: Visibility restriction (0 -> invisible, 1 -> visible on beta site only, >1 -> public)
-- confidentiality: restricted to users who have logged in
--
CREATE TABLE ProbeSetFreeze (
Id smallint(5) unsigned NOT NULL auto_increment,
ProbeFreezeId smallint(5) unsigned NOT NULL default '0',
AvgID smallint(5) unsigned NOT NULL default '0',
Name varchar(100) NOT NULL default '',
FullName varchar(100) NOT NULL default '',
ShortName varchar(100) NOT NULL default '',
CreateTime date NOT NULL default '0000-00-00',
public tinyint(4) NOT NULL default '0',
confidentiality tinyint(4) NOT NULL default '0',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `ProbeSetXRef`
-- Linking table to connect expression information and version information with values in Data table
--
CREATE TABLE ProbeSetXRef (
ProbeSetFreezeId smallint(5) unsigned NOT NULL default '0',
ProbeSetId int(10) unsigned NOT NULL default '0',
DataId int(10) unsigned NOT NULL default '0',
UNIQUE KEY ProbeSetId (ProbeSetFreezeId,ProbeSetId)
) TYPE=MyISAM;
--
-- Table structure for table `ProbeXRef`
-- Linking table to connect expression information and version information with values in Data table
--
CREATE TABLE ProbeXRef (
ProbeFreezeId smallint(5) unsigned NOT NULL default '0',
ProbeId int(10) unsigned NOT NULL default '0',
DataId int(10) unsigned NOT NULL default '0',
UNIQUE KEY ProbeId (ProbeFreezeId,ProbeId)
) TYPE=MyISAM;
--
-- Table structure for table `SE`
-- Provides standard error for some values in the Data table
--
CREATE TABLE SE (
DataId int(10) unsigned default NULL,
StrainId smallint(5) unsigned default NULL,
error double NOT NULL default '0',
UNIQUE KEY DataId (DataId,StrainId)
) TYPE=MyISAM;
--
-- Table structure for table `Sample`
-- Provides information for a tissue sample used for expression estimates
-- Name: Identifier for animal from which sample comes
-- Age: Animal age in days
-- CreateTime: Date of harvesting the sample
-- TissueType: Tissue or organ name
-- FromSrc: ??
-- ImageName: File name for image of microarray from this sample
--
CREATE TABLE Sample (
Id smallint(5) unsigned NOT NULL auto_increment,
StrainId smallint(5) unsigned NOT NULL default '0',
Name char(20) NOT NULL default '',
Age smallint(6) NOT NULL default '0',
Sex enum('F','M') NOT NULL default 'F',
CreateTime date NOT NULL default '2001-01-01',
TissueType char(30) default NULL,
FromSrc char(10) default NULL,
ImageName char(30) NOT NULL default '',
PRIMARY KEY (Id),
UNIQUE KEY Name (StrainId,Name,CreateTime)
) TYPE=MyISAM;
--
-- Table structure for table `SampleXRef`
-- Linking table to connect samples to possible multiple gene expression estimates
--
CREATE TABLE SampleXRef (
SampleId smallint(5) unsigned NOT NULL default '0',
ProbeFreezeId smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (ProbeFreezeId,SampleId)
) TYPE=MyISAM;
--
-- Table structure for table `Strain`
-- Name for an inbred line, usually a member of a recombinant inbred set
--
CREATE TABLE Strain (
Id smallint(5) unsigned NOT NULL auto_increment,
Name char(30) NOT NULL default '',
PRIMARY KEY (Id),
KEY Name (Name)
) TYPE=MyISAM;
--
-- Table structure for table `StrainXRef`
-- Connects inbred lines to the recombinant inbred sets to which they belong
-- OrderId: serial order for standard display
--
CREATE TABLE StrainXRef (
InbredSetId smallint(5) unsigned NOT NULL default '0',
StrainId smallint(5) unsigned NOT NULL default '0',
OrderId smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (InbredSetId,StrainId),
UNIQUE KEY Orders (InbredSetId,OrderId)
) TYPE=MyISAM;
--
-- Table structure for table `Tissue`
-- Provides a name for a tissue that is a source of gene expression data
--
CREATE TABLE Tissue (
Id smallint(5) unsigned NOT NULL auto_increment,
Name char(30) NOT NULL default '',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `Phenotype`
-- Provides name and measurement unit for a published phenotype
--
CREATE TABLE Phenotype (
Id smallint(5) unsigned NOT NULL auto_increment,
Name text,
Units varchar(100) NOT NULL default 'Unknown',
PRIMARY KEY (Id),
UNIQUE KEY Name (Name(255),Units)
) TYPE=MyISAM;
--
-- Table structure for table `Publication`
-- Bibliographic reference information for a published phenotype
-- This information is obviously not fully normalized, but for our purposes it is sufficient
-- PubMed_ID: Identification number at Entrez PubMed, http://www.ncbi.nlm.nih.gov/entrez/
--
CREATE TABLE Publication (
Id smallint(5) unsigned NOT NULL auto_increment,
PubMed_ID int(10) unsigned default NULL,
Abstract text,
Authors varchar(255) default NULL,
Title varchar(255) default NULL,
Journal varchar(255) default NULL,
Volume varchar(255) default NULL,
Pages varchar(255) default NULL,
Month varchar(255) default NULL,
Year varchar(255) NOT NULL default '0',
PRIMARY KEY (Id),
UNIQUE KEY Name (PubMed_ID)
) TYPE=MyISAM;
--
-- Table structure for table `PublishFreeze`
-- Information for a version of published phenotype data, currently a version for each RI set
-- In the future we might have versions for individual users
-- Name: Internal identifier (no spaces)
-- FullName: Most descriptive name
-- ShortName: Short name version for use where space is limited
-- CreateTime: Creation date and time
-- public: Visibility restriction (0 -> invisible, 1 -> visible on beta site only, >1 -> public)
--
CREATE TABLE PublishFreeze (
Id smallint(5) unsigned NOT NULL auto_increment,
Name varchar(100) NOT NULL default '',
FullName varchar(100) NOT NULL default '',
ShortName varchar(100) NOT NULL default '',
CreateTime date NOT NULL default '2001-01-01',
public tinyint(4) NOT NULL default '0',
InbredSetId smallint(5) unsigned default '1',
PRIMARY KEY (Id)
) TYPE=MyISAM;
--
-- Table structure for table `PublishXRef`
-- Linking table to connect published phenotype data to meta-data
-- Sequence:
--
CREATE TABLE PublishXRef (
Id smallint(5) unsigned NOT NULL auto_increment,
InbredSetId smallint(5) unsigned NOT NULL default '0',
PhenotypeId smallint(5) unsigned NOT NULL default '0',
PublicationId smallint(5) unsigned NOT NULL default '0',
DataId int(10) unsigned NOT NULL default '0',
Sequence smallint(5) unsigned NOT NULL default '1',
UNIQUE KEY InbredSet (InbredSetId,Id),
UNIQUE KEY record (InbredSetId,PhenotypeId,PublicationId,Sequence),
KEY InbredSetId (InbredSetId)
) TYPE=MyISAM;
