path: root/sql/schema-from-in-db-documentation.org

This is a description of the GeneNetwork database extracted from the tables
/TableFieldAnnotation/ and /TableComments/.

* Vlookup
It's been used by Arthur for annotation updates.
** alias
** AlignID
** assembly
** AvgMethodId
** BlatSeq
** CAS_number
** cdsEnd
** cdsStart
** ChEBI_ID
** ChEMBL_ID
** ChemSpider_ID
** Chr
** DatasetId
** description
** EC_number
** exonCount
** exonEnds
** exonStarts
** Full_Description
** GeneChipId
** GeneId
** GN_AccesionId
** HMDB_ID
** Id
** InbredSetId
** InfoFileId
** InfoPageName
** KEGG_ID
** kgID
** Mb
** Molecular_Weight
** Name
** NM_ID
** Nugowiki_ID
** Position
** Probe_set_Blat_Mb_end
** Probe_set_Blat_Mb_start
** ProteinID
** PubChem_ID
** SnpName
** SpeciesId
** Strand
** Symbol
** TissueId
** TxEnd
** TxStart
** UNII_ID
** VLBlatSeq
** VLProbeSetId

* user_openids
TO BE IMPLEMENTED (maybe). Table to link to OpenID. Probably not needed.
** openid_url
** user_id

* user_collection
GN2 user collection of traits.
** changed_timestamp
** created_timestamp
** id
** members
** name
** user

* UserPrivilege
Used to define if an Assay data set (old "ProbeSetFreeze") can be opened and downloaded.
** download_result_priv
** ProbeSetFreezeId
** UserId

* USER
GN2 only. 

Comment on User type and password.
This table has 47 users in GN2 as of Mar 2016.
** active
** confirmed
** createtime
** disable
** email
** email_address
** full_name
** grpName
** id
** lastlogin
** name
** organization
** password
this is a hash value of user's password
** privilege
** registration_info
** superuser
** user_ip

* TissueProbeSetData
These 'Tissue' tables are used by the Tissue Correlation tool in GN1. Not implemented yet in GN2. Mainly Illumina Mouse  version 6.1 data.
** Id
** TissueID
** value

* TissueProbeFreeze
These 'Tissue' tables are used by the Tissue Correlation tool in GN1. Not implemented yet in GN2. 
** ChipId
** CreateTime
** FullName
** Id
** InbredSetId
** Name
** ShortName
** StrainId

* Tissue
(RWW Nov 2007): A small table that may be used to build pull-down menus in GN1 and GN2. This table contains simple one to three word terms describing the tissue, cell, organ, or other biological material used in the microarray experiment. This table is used in conjunction with the FIND RECORDS "Type" field.

As of Nov 2007 this table contained only 15 rows:
As of Mar 2016 this table contains 158 rows.

Whole Brain
Cerebellum
Hematopoietic Cells
Liver
etc. 

How is this table used? Probably to create menu fields. Talk with Chris Mungall and colleagues about controlled vocabulary for APIs.

** BIRN_lex_ID
Need to get official IDs for tissues, cells, etc.
** BIRN_lex_Name
Need to get official IDs for tissues, cells, etc.
** Id
Incremented integer uniquely identifies the record.
** Name
Name of the biological material used in the experiment.
** Short_Name
** TissueId
** TissueName

* temporary
This is probably the content of the user's collection. Lost at end of user's session.
** GeneID
** HomoloGene
** OMIM
** Other_GeneID
** Symbol
** tax_id

* TempData

** Id
** NStrain
** SE
** StrainId
** value

* Temp

** createtime
** DataId
** description
** Id
** InbredSetId
** IP
** Name

* StrainXRef
RENAME something like "SubjectXRef". This table links the information in the Strain table with InbredSet and provides order for the strains in the mapping population.
** InbredSetId
Foreign key to InbredSet:Id
The Id of the mapping population in the InbredSet table. 
** OrderId
Order of the strains in the Genotype file or mapping population, follows the pattern 10, 20 rather than 1,2. 
** PedigreeStatus
** StrainId
Foreign key to Strain:Id
The Id of the mapping population strain in Strain table. 
** Used_for_mapping

* Strain
RENAME "SubjectDescription" . This table to keep track of case/subject identifiers (cases or F2 or strains). But we have the problem that there may be multiple observation per subject or the time series data for one case, (multiple ages for one strain, etc). Generic sample/case identifiers here.
Contains 14,000 rows as of March 2016. We really need a new table "ObservationOfSubject" (RWW 2016)

Apurva would like to extend this table to include "DateOfBirth" and "BatchNumber".


** Alias
** Id
GN internal identifier for the strain
** Name
official strain name/symbol
** Name2
** SpeciesId
Foreign key to Species:Id
species of strain
** Symbol
short strain symbol used in graphs and tables

* Species
Contains the internal Ids and names for various species.
** Id
internal GeneNetwork species identifier
** Name
the common name of the species

* Source
This table contains one record for every SNP dataset represented in the Snp table.
[Created by Robert Crowell, August 18, 2005. Annotation of table, Robert Crowell, Aug 22, 2005.]

<b>See also:</b>
<a href="/cgi-bin/beta/schema.py#Snp">Snp</a>
** DateAdded
date when the data source was added to our database
** DateCreated
date when the data source was produced
** Name
name of this source
** SourceId
internal GeneNetwork identifier for the source dataset

* SNP_perlegen
DEPRECATED: use the Snp table instead.

The complete perlegen dataset.  This table is deprecated.
Created by Robert Crowell, 2005.
** Id
** mb
** A_J
** AKR_J
** BALB_CBYJ
** BTBR_T_TF_J
** C3H_HEJ
** C57BL_6J
** DBA_2J
** FVB_NJ
** KK_H1J
** MOLF_EIJ
** NOD_LTJ
** NZW_LACJ
** PWD_PHJ
** WSB_EIJ
** 129S1_SVIMJ
** chr
** B6_D2
** B6_AJ
** D2_AJ
** old
old=1 SNPs belong to the Flint dataset (chr2 only)
** source
** nes
** nc
** length
** 5_flanking
** 5_assay
** observed
** 3_assay
** 3_flanking
** sequence
** strand
** score
** fraction
** major_count
** minor_count
** n_count
** state_string
** CAST_EIJ
** Score_Blat
** chr_Blat
** Mb_Blat

* SNP_mpd
The complete MPD dataset.  This table is soon to be deprecated.
** id
** chr
** mb
** class
** function
** 129S1_SvImJ
** 129X1_SvJ
** A_HeJ
** A_J
** AKR_J
** ALR_LtJ
** ALS_LtJ
** D2_Hc_0_H2_d_H2_T18_c_oSnJ
** BALB_cByJ
** BALB_cJ
** BPH_2J
** BPN_3J
** BTBR_T_tf_J
** BUB_BnJ
** C3H_HeJ
** C3HeB_FeJ
** C57BL_10J
** C57BL_6J
** C57BR_cdJ
** C57L_J
** C58_J
** CAST_EiJ
** CBA_CaJ
** CBA_J
** CE_J
** CZECHII_EiJ
** DBA_1J
** DBA_2J
** FVB_NJ
** I_LnJ
** KK_HlJ
** LG_J
** LP_J
** MA_MyJ
** MRL_MpJ
** NOD_LtJ
** NON_LtJ
** NZB_BlNJ
** NZW_LacJ
** PERA_EiJ
** PL_J
** RF_J
** RIIIS_J
** SB_LeJ
** SEA_GnJ
** SJL_J
** SM_J
** SPRET_EiJ
** ST_bJ
** SWR_J
** WSB_EiJ
** ZALENDE_EiJ
** source
** ss
** ss_orientation
** rs
** nmappings
** snpID
** insertions
** B6_D2
** B6_AJ
** D2_AJ

* SnpPattern
Data used by SNP Browser. Variant browser needs to be redone from scratch using a decent architecture. 80 million SNPs in this table from sequence data of 2011 (Keane et al. Nature) but imputed to many other strains.
** 129P2/OlaHsd
** 129S1/SvImJ
** 129S2/SvHsd
** 129S4/SvJae
** 129S5/SvEvBrd
** 129S6/SvEv
** 129T2/SvEmsJ
** 129X1/SvJ
** A/J
** AKR/J
** B6A6_Esline_Regeneron
** BALB/cByJ
** BALB/cJ
** BPH/2J
** BPL/1J
** BPN/3J
** BTBRT<+>tf/J
** BUB/BnJ
** C2T1_Esline_Nagy
** C3H/HeJ
** C3HeB/FeJ
** C57BL/10J
** C57BL/6ByJ
** C57BL/6J
** C57BL/6JBomTac
** C57BL/6JCrl
** C57BL/6JOlaHsd
** C57BL/6NCrl
** C57BL/6NHsd
** C57BL/6NJ
** C57BL/6NNIH
** C57BL/6NTac
** C57BLKS/J
** C57BR/cdJ
** C57L/J
** C58/J
** CALB/RkJ
** CAST/EiJ
** CBA/J
** CE/J
** CZECHII/EiJ
** DBA/1J
** DBA/2J
** DDK/Pas
** DDY/JclSidSeyFrkJ
** EL/SuzSeyFrkJ
** Fline
** FVB/NJ
** HTG/GoSfSnJ
** I/LnJ
** ILS/IbgTejJ
** IS/CamRkJ
** ISS/IbgTejJ
** JF1/Ms
** KK/HlJ
** LEWES/EiJ
** LG/J
** Lline
** LP/J
** MA/MyJ
** MAI/Pas
** MOLF/EiJ
** MOLG/DnJ
** MRL/MpJ
** MSM/Ms
** NOD/ShiLtJ
** NON/LtJ
** NOR/LtJ
** NZB/BlNJ
** NZL/LtJ
** NZO/HlLtJ
** NZW/LacJ
** O20
** P/J
** PERA/EiJ
** PERC/EiJ
** PL/J
** PWD/PhJ
** PWK/PhJ
** Qsi5
** RBA/DnJ
** RF/J
** RIIIS/J
** SEA/GnJ
** SEG/Pas
** SJL/J
** SKIVE/EiJ
** SM/J
** SnpId
** SOD1/EiJ
** SPRET/EiJ
** ST/bJ
** SWR/J
** TALLYHO/JngJ
** WSB/EiJ
** ZALENDE/EiJ

* SnpAllele_to_be_deleted
OK, delete this then. (RWW March 2016)
** Base
** Id
** Info

* SnpAll
CHECK, UPDATE or DELETE. Antique data that may be used by SNP Browser or by SNP displays in GN1 maps (single chromosome views). Probably only data for mouse in the table although structured for other species too.
** 3Prime_UTR
** 5Prime_UTR
** Alleles
** BlatScore
** Chromosome
** Class
** conservation
** ConservationScore
** Domain
** Downstream
** Exon
** Flanking3
** Flanking5
** Function
** Gene
** Id
** Intergenic
** Intron
** Mb
** MbCelera
** Mb_mm6
** ncbi
** Non_Splice_Site
** Non_Synonymous_Coding
** Position
** Rs
** SnpName
** Source
** SourceId
** SpeciesId
** Splice_Site
** Ss
** Start_Gained
** Start_Lost
** Stop_Gained
** Stop_Lost
** Strand
** Synonymous_Coding
** Type
** Unknown_Effect_In_Exon
** Upstream

* Snp
This table contains a record for every SNP available in GN.  To locate SNPs at a certain location, first query the SNP table.  Using the Id values, the Allele table can be queried to obtain the allele calls for all strains for this SNP.
[Created by Robert Crowell, August 18, 2005. Annotation of table, Robert Crowell, Aug 24, 2005.]

<b>See also:</b>
<a href="/cgi-bin/beta/schema.py#Allele">Allele</a>
<a href="/cgi-bin/beta/schema.py#Source">Source</a>
** BlatScore
score of the BLAT alignment
** Chromosome
chromosome from the UCSC Genome Brower; for mouse SNPs we have used mm6 BLAT analysis or other source
** Class
if a singleton occurs in a wild strain Class is 0, otherwise it is the same as MinorCount
** Flanking3
100 base sequence on the 3' side of this SNP
** Flanking5
100 base sequence on the 5' side of this SNP
** Function
function class annotation using conventions of Mouse Phenome project SNP display
** Id
internal GeneNetwork identifier for this SNP
** MajorAllele
the more common allele for all strains in this SNP
** MajorCount
number of strains in the dataset containing this SNP's major allele (see MajorAllele below)
** Mb
position in megabases from the BLAT alignment or other source
** MbCelera
position in megabases given by Celera (obsolete field)
** MinorAllele
the less common allele for all strains in this SNP
** MinorCount
number of strains in the dataset containing this SNP's minor allele (see MinorAllele below)
** MissingCount
number of strains in the dataset without an allele call
** Rs
dbSNP rs#
** SnpId
commonly used identifier of a SNP from dbSNP, Celera, Perlegen or other source
** SourceId
Foreign key to Source.SourceId
internal GeneNetwork identifier for the source dataset
** Type
polymorphism classification  using conventions of Mouse Phenome project SNP display

* SE
This simple but huge table contains Standard Error of the Mean data that matches the "Data" table.

Created by Jintao, March 2003. 
To retrieve or insert the data for an experiment there should be also corresponding entries in the ProbeXRef table for the raw data (references ProbeFreeze) or in the ProbeSetXRef table for transformed data (references ProbeSetFreeze).
** DataId
** error
** StrainId

* SampleXRef
DEPRECATED. Used only in GN1 from 2004 to 2006 to display CEL files and array scan images for QC.
** ProbeFreezeId
** SampleId

* Sample
DEPRECATED. Only used in GN1 between 2004 and about 2006 to display images of microarray data. 

A table that provides access to low-level array data in GeneNetwork. This table is used only from tables embedded in INFO files such as http://www.genenetwork.org/dbdoc/BR_U_1203_MR.html.  The table will call an Image URL and CEL file URL, a DAT file URL, etc. This format and table has not been used since about 2005. We should develop a better method to allow access to raw data from GN.

** Age
** CELURL
** CHPURL
** CreateTime
** DATURL
** EXPURL
** FromSrc
** Id
** ImageURL
** Name
** RPTURL
** Sex
** StrainId
** TissueType
** TXTURL

* role
Noble intent table. Can be deleted or implemented. Idea was "administrator", "curator", "owner", "user"
** description
** name
** the_id

* QuickSearch
Check if needed by GN2 with Zach. If not, then delete (Lei Yan March 2016).
** result_fields
** table_name
** terms
** the_key

* PValue

** DataId
** pvalue

* pubmedsearch
Data table used to find gene symbols associated with authors. Created by Lei Yan for Author search functions in GN1. Check if this works in GN2. This function has alrways been a bit flaky.
** authorfullname
** authorshortname
** geneid
** id
** institute
** pubmedid
** title

* PublishXRef

** comments
** DataId
** Id
** InbredSetId
** PhenotypeId
** PublicationId
** Sequence

* PublishSE
Table contains the standard error of the phenotype value.
** DataId
** error
** StrainId

* PublishFreeze
This is a table of cohorts/populations that have associated Phenotype data in the "Type" menu of the GN "Select and Search" page. As of March 2016 this table has 34 rows corresponding to 34 Published Phenotype data sets for different groups. When we enter a new group (cohort or population or RI set) that will have phenotypes into GeneNetwork, then we need to add data to this table. Be careful, when you add a new "PublishFreeze.Name" here then you must also make sure that the Group name (e.g. "BXD" in the "InbredSet" table) exactly matches the first part of the PublishFreeze.Name" or else the code will generate an error.

** AuthorisedUsers
** confidentiality
** CreateTime
** FullName
This is the long name that goes into the menu. Order is not controlled yet but this could be added. 
** Id
Unique integer. Just an integer that unique specifies one of the Published Phenotype data sets.  1 = BXDPublish, 34 = HSNIHPPublish
** InbredSetId
** Name
Must be unique. This is the name of the Published (and unpublished) data associated with a cohort or population. The name here (example "BXDPublish") must match a specific cohort name (e.g. "BXD",  also known as an "InbredSet" name). 
** public
** ShortName

* PublishData
Data on phenotypes. Equivalent roughly to ProbeSetData. 1 million records as of March 2016. Much of these data are actually not published yet. 
This is really "StandardPhenotypeData". Currently also includes some metagenomic data.
** Id
** StrainId
** value

* Publiction
Comment

* Publication
Used by Phenotypes data sets. Each published phenotype is associated with a PubMed ID. All data should ideally be populated automatically from PubMed rather entered by users.
** Abstract
** Authors
** Id
** Journal
** Month
** Pages
** PubMed_ID
** Title
** Volume
** Year

* ProbeXRef

** DataId
** ProbeFreezeId
** ProbeId

* ProbeSetXRef_TEMP

** DataId
** Locus
** Locus_old
** LRS
** LRS_old
** mean
** ProbeSetFreezeId
** ProbeSetId
** pValue
** pValue_old
** se

* ProbeSetXRef
This table contains summary data used by the Advanced Search feature in GN1 and GN2 (e.g "mean=(6 20)", for a particular data sets, including information on average expression of probe sets and probes, phenotypes, LRS values of single best QTL, p values of single best QTL, additive effect of single best QTL, locus ID of the marker closest to the single best QTL, etc.
** additive
additive effect size from QTL Reaper at highest QTL peak
** DataId
** h2
** Locus
locus or marker closest to highest QTL peak
** Locus_old
** LRS
LRS from QTL Reaper of highest QTL peak
** LRS_old
** mean
average expression across data set for a particular probe set
** ProbeSetFreezeId
** ProbeSetId
** pValue
p value based on QTL Reaper of highest QTL peak
** pValue_old
** se
range of expression (not actually SE) across data set for a particular probe set

* ProbeSetSE
Contains standard error of assays in ProbeSetData. Roughly 0.5 billion rows as of March 2016. Human data do not have error terms usually.
** DataId
** error
** StrainId

* ProbeSetFreeze
RENAME: AssayDataSet or something more neutral. Delete the word "Freeze" from this table.

ProbeSetFreeze contains the information of the data analysis method used in the processing the microarray experiment data described in the ProbeFreeze table and the confidentiality of the resulting data. New records should be inserted only if the relevant ProbeFreeze and AvgMethod records are in place. The use of the four different name fields effectively containing 4 versions of the same information needs to be clarified. 120 records on Dec 2006. About 700 records March 2016.

The name of a data set in GN is "ProbeSetFreeze.FullName" and is used in several output tables and graphs, for example ClusterMap in this format -- ProbesetID::FullName


Comment by PP and RW March 2016: This key table needs to be evaluated and renamed. Was initially designed to hold large microarray data, but now must accommodate any large "omics" data, but not genotypes and not classic phenotypes. Some trait data is ambiguous such as metagenomics. Metagenomics probably should be included here rather than in Phenotypes.
** AuthorisedUsers
** AvgID
Foreign key to AvgMethod:Id
Links to the method used in the processing of the microarray experiment data (like RMA, MAS etc.). ID1206
** confidentiality
0 = not confidential, 1 = confidential. ID1206. confidential means the dataset will appear in the search page, but will be serached only after the user login
** CreateTime
now()
** DataScale
** FullName
Similar to ProbeFreeze name (institute tissue chip date)+ the data analysis method used. ID1206
** Id
Foreign key to Primary Key
Incremented integer, uniquely identifies the record. ID1206
** Name
Very short abbreviation of the microarray experiment description, the use needs to be clarified. Contains tissue abbreviaton_chip abbreviation_date_processing method abbreviation. ID1206
** Name2
The same as in Name, only a bit longer - tissue_chip_method_date. The use needs to be clarified. ID1206
** OrderList
** ProbeFreezeId
Foreign key to ProbeFreeze:Id
Links to the microarray experiment description in ProbeFreeze table. ID1206
** public
1 = beta data, 2 = available in GeneNetwork. ID1206. beta means the dataset will not appear in the search page if the user does not login
** ShortName
Similar to FullName with random items abbreviated. ID1206

* ProbeSetData
Almost all important molecular assay data is in this table including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. In comparison,  ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes).


"StrainId" should be "CaseId" or "SampleId".

"ProbeSetData" should probably be "AssayData" or something more neutral. 
** Id
** StrainId
** value

* ProbeSet
PLEASE CHANGE TABLE NAME and rework fields carefully. This is a terrible table but it works well (RWW March 2016). It is used in combination with the crucial TRAIT DATA and ANALYSIS pages in GN1 and GN2. It is also used by annotators using the UPDATE INFO AND DATA web form to correct and update annotation. It is used by Arthur to enter new annotation files and metadata for arrays, genes, proteins, metabolites. The main problem with this table is that it is doing too much work.

Initially (2003) this table contained only Affymetrix ProbeSet data for mouse (U74aV2 initially). Many other array platforms for different species were added. At least four other major categories of molecular assays have been added since about 2010.

1. RNA-seq annotation and sequence data for transcripts using ENSEMBL identifiers or NCBI NM_XXXXX and NR_XXXXX type identifiers

2.  Protein and peptide annotation and sequence data (see BXD Liver Proteome data, SRM and SWATH type data) with identifiers such as "abcb10_q9ji39_t311" for SRM data and "LLGNMIVIVLGHHLGKDFTPAAQAA" for SWATH data where the latter is just the peptide fragment that has been quantified. Data first entered in 2015 for work by Rudi Aebersold and colleagues.

3.  Metabolite annotation and metadata (see BXD Liver Metabolome data) with identifiers that are usually Mass charge ratios such as "149.0970810_MZ"

4. Epigenomic and methylome data (e.g. Human CANDLE Methylation data with identifiers such as "cg24523000")

It would make good sense to break this table into four or more types of molecular assay metadata or annotation tables) (AssayRNA_Anno, AssayProtein_Anno, AssayMetabolite_Anno, AssayEpigenome_Anno, AssayMetagenome_Anno), since these assays will have many differences in annotation content compared to RNAs.

Some complex logic is used to update contents of this table when annotators modify and correct the information (for example, updating gene symbols).  These features requested by Rob so that annotating one gene symbol in one species would annotate all gene symbols in the same species based on common NCBI GeneID number.  For example, changing the gene alias for one ProbeSet.Id will changing the list of aliases in all instances with the same gene symbol.

If the ProbeSet.BlatSeq (or is this ProbSetTargetSeq) is identical between different ProbeSet.Ids then annotation is forced to be the same even if the symbol or geneID is different. This "feature" was implemented when we found many probe sets with identical sequence but different annotations and identifiers.

Annotation by Rob Williams, Aug 19, 2005. Created by Jintao Wang, 2003. This annotation updated March 22, 2016 by Rob.
** alias
gene aliases and old symbols associated with the gene assigned to the probe set or probe  (editable using Update page interface)
** alias_H
official human gene description from NCBI ftp site (Build 35)
** Biotype_ENS
** BlatSeq
probe sequence or concatenated probe set sequence ( trimmed of overlap) used for BLAT alignment to genome (viewable but not editable from Update page)
** CAS_number
** ChEBI_ID
** ChEMBL_ID
** ChemSpider_ID
** ChipId
identifier of the array type
** Chr
chromosome assigned to the probe set or probe (editable using Update page interface)
** chromosome_H
official human gene description from UCSC ftp site (Build 35)
** Chr_mm8
** chr_num
the numerical value of chromosomes, for example, X is 20 or 21 depending on species
** comments
record of modification time and person making modifications. Used to prevent overwriting of modified records.
** Confidence
** description
gene description assigned to the probe set or probe  (editable using Update page interface)
** description_H
official human gene description from NCBI ftp site (Build 35)
** EC_number
** flag
a status flag on the probe set: 0=mismatch between blat results and affy symbols (Problem!!!!); 1=match between blat results and affy symbols (Excellent); 2=symbols from TIGR  or replaced by Blat symbols  (The original affy symbols have "///" or "_predicted") (ok); 3: symbols from BLAT (ok); 4. symbols from Affy (not bad) or no symbol (sad); 5. symbols from target sequence blating (??); 6. symbols from genebank sequence blating (???); 7=symbols from blating to mouse genome(????)
** Flybase_Id
** GenbankId
GenBank ID assigned to the probe set or probe as given to us by Affymetrix or Agilent (not editable from Update page)
** GeneId
Entrez gene ID assigned to the probe set or probe  (editable using Update page interface)
** GeneId_H
official human gene description from NCBI ftp site (Build 35)
** HMDB_ID
** HomoloGeneID
** Id
internal identifer used by GeneNetwork
** KEGG_ID
** Mb
** MB_H
Converted from ProbeSet.Mb_mm6 by Batch Coordinate Conversion 
** Mb_mm6
megabase position assigned to the probe set or probe  (editable using Update page interface). The most proximal Mb was used irrespective of whether this was 3' or 5' end. mm6 refers to a particular mouse assembly (perhaps inappropriate)
** Mb_mm8
** Molecular_Weight
** Name
Affymetrix probe set identifier or Agilent probe identifier
** name_num
the numerical value of Affymetrix probe set identifier or Agilent probe identifier
** Nugowiki_ID
** OMIM
OMIM identifier assigned to the probe set or probe (editable using Update page interface)
** PeptideSequence
** PrimaryName
** Probe_set_Blat_Mb_end
the distal (high) megabase position matched by the probe set sequence  (5' or 3' end)
** Probe_set_Blat_Mb_end_mm8
** Probe_set_Blat_Mb_start
the proximal (low) megabase position matched by the probe set sequence (3' or 5' end)
** Probe_set_Blat_Mb_start_mm8
** Probe_set_BLAT_score
the BLAT score generated by the concatenated probe set (or probe) sequence for the correct target mRNA. This will usually be the highest BLAT score, but in some cases a non-trasncribed genomic sequence may match better than the actual transcribed mRNA target sequence.
** Probe_set_Note_by_JG
notes on the probe set by Jing Gu
** Probe_set_Note_by_RW
notes of the probe set by Robert Williams
** Probe_set_specificity
the BLAT score of the correct probe set target mRNA divided by the best or next best BLAT score
** Probe_set_strand
the DNA strand (+ or -) that is identical to the probe set nucleotide sequence. By convention, correctly directed probe sets have the same direction as the gene.
** Probe_set_target_region
DO NOT USE. Unknown use. May want to delete this field after review of possible use.
** Probe_Target_Description
description of the region of the gene and transcript targetted by the probe set or probe (this text is displayed after the semicolon in Search Results; this is a searchable field) 
** ProteinID
** ProteinName
** PubChem_ID
** RefSeq_TranscriptId
the reference sequence of the mRNA associated with the probe set. These are always receded with "NM_". This field was added to allow easier linkage from the UCSC Genome Browser to GN.
** SecondaryNames
** SNP
unknown use
** Strand_Gene
the DNA strand (+ or -) of the gene assigned to the probe set or probe (editable using Update page interface)
** Strand_Probe
unknown use (redundant with Probe_set_strand ?) (editable using Update page interface)
** symbol
gene symbol assigned to the probe set or probe  (editable using Update page interface)
** symbol_H
official human gene symbol from UCSC ftp site (hg17)
** TargetId
** TargetSeq
target sequence as given to us by Affymetrix (viewable but not editable from Update page)
** Tissue
** Type
** UniGeneId
chromosome assigned to the probe set or probe (viewable but not editable from Update page)
** UNII_ID

* ProbeH2
DEPRECATED. Will not be used in GN2. Please compare to H2.  Used only for some the heritability of probes shown in the Probe table.
** h2
** ProbeFreezeId
** ProbeId
** weight

* ProbeFreeze
About the Name:  ProbeFreeze is a stupid (historic) name for this table. The table should be renamed to more general and sensible such as "Data_Set_Group_Info" table and ProbeSetFreeze should be changed to something like "Data_Set_Info" table. At present, every ProbeSetFreeze record needs a parent ProbeFreeze record, even when the relation is 1-to-1.

About This Table:  The ProbeFreeze table provides information about the overall set of microarray hybridization experiments - a meaningful name that identifies the experiment, the link to the microarray chip name, the link to tissue, organ or other generic biological material name, the link to the mapping population, inbred strain set name or similar used in the experiment. 

A ProbeFreeze may have a subset of ProbeSetFreezes (one ProbeFreeze to many ProbeSetFreezes) to which it belongs as children data sets (for example, male data only, female data only, RMA data or MAS5 data. The name provides a short description of the experiment. New records in the table should be inserted only after the relevant records in the GeneChip, Tissue and InbredSet are in place. 34 records on Dec14,2006. ID1206
** ChipId
Foreign key to GeneChip:Id
Links to the information about the microarray chip used. ID1206
** CreateTime
now()
** FullName
Empty field. ID1206
** Id
Foreign key to Primary key
Incremented integer, uniquely idenifies the record. ID1206
** InbredSetId
Foreign key to InbredSet:Id
Links to the information about the cross, mapping population, inbred strain set or similar used in the experiment. ID1206
** Name
Abbreviated description that identifies the microarray experiment. The existing records contain institute id, short biological material description, the microarray chip name and date in brackets, but the field can contain any meaningful description of the microarray experiment. ID1206
** ProbeFreezeId
** ShortName
Empty field. ID1206
** TissueId
Foreign key to Tissue:Id
Links to the information about the biological material analysed . ID1206

* ProbeData
Table with fine-grained probe level Affymetrix data only. Contains 1 billion rows March 2016. This table may be deletable since it is only used by the Probe Table display in GN1. Not used in GN2 (double-check). 

In comparison the "ProbeSetData" table contains more molecular assay data, including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. In comparison,  ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes).


"ProbeData.StrainId" should be "CaseId" or "SampleId".

"ProbeData" should probably be "AssayData" or something more neutral. 
** Id
** StrainId
** value

* Probe
DEPRECATED. Used only for a few array platforms in GN1. Not used for GN2. This table contains data on the characteristics of individual Affymetrix probes (not probe sets). Data are used to populate the Probe Tables which display sequences of the perfect match and mismatch probes. This table could also contain data on the Agilent 60-mer probes.
Created by Yanhua Qu and Jintao Wang, 2003.
** ExonNo
exon to which the probe sequence corresponds. When a probe straddles two exons we use the format 10*11
** E_GSB
the gene-specific binding energy computed using Li Zhang's PDNN method. Data provided by Li Zhang
** E_NSB
the non-specific binding energy computed using Li Zhang's PDNN method. Data provided by Li Zhang
** Id
internal identifier
** Name
six to eight character name (depending on array) XXX coordinate then YYY coordinate with 0 used a buffer
** ProbeSetId
Affymetrix probe set identifier to which the probe belongs using the conventional CDF probe-probeset mapping
** Sequence
25 nucleotide sequence
** SerialOrder
probe order from the most 3' probe to the most 5' probe
** Tm
theoretical melting temperature of a DNA-DNA hybrid. The actual probes are cRNA

* Phenotype
This table contains names, full descriptions, and short symbols for traits and phenotype used primarily in the Published Phenotypes databases.  

Contains 10k rows, March 2016, of which 5000 are for the BXDs).

Created by Jintao Wang, March 2003.
** Abbreviation
abbreviation of the phenotype
** Authorized_Users
** Id
** Lab_code
** Name
description of the phenotype
** Original_description
** Owner
** Post_publication_abbreviation
** Post_publication_description
** Pre_publication_abbreviation
** Pre_publication_description
** Submitter
** Units
units of measurement of the phenotype

* Organizations
Table generated by Arthur Centeno for INFO files and metadata.
** OrganizationId
** OrganizationName

* NStrain
Merge this table to "PublishSE" and "PublishData". Values are used to track n of cases or n or strains for "published" phenotype values. Move these data to PublishSE and PublishData, phenotype SE, and N strain are three columns shows to users of Published Phenotypes. Unknown function at this time, seems like this contains information which is derived, so looks like it might be unneeded (DA March 2016).

Contains 160,000 rows, March 2016. 
** count
** DataId
** StrainId

* MStrain
Comment

* MappingMethod
Needs to be updated, but this is used in GN1 to select mapping methods for different cohorts/populations. PLINK is used for map human cohorts in GN1. Happy is not implemented as of March 2016 in either GN1 or GN2. R/qtl implemented by DA in GN2 in 2015. FastMap probably equal pyLMM and should be renamed. QTL Reaper = Haley Knott regression mapping and probably should be renamed HKMap. 

QTL Reaper, R/qtl, Happy, PLINK, FastMap
** Id
** Name

* MachineAccessLog

** accesstime
** action
** data_id
** db_id
** id
** ip_address

* login
Used by GN2. Just a login file. 277 login in GN2 as of March 2016.
** assumed_by
** id
** ip_address
** session_id
** successful
** timestamp
** user

* LitCorr

** ProbeSetId1
** ProbeSetId2
** value

* LCorrRamin3
Should be updated in 2016. Literature correlations by Prof Ramin Homayouni (v3) in GN1 and GN2. These are mouse GeneIDs (table starts with GeneId1 = 381629 = the gene with symbol Atraid in mouse. This genes maps to HomoloGene 15412 and to human ATRAID (human GeneId 51374). This table should ideally work for mouse, human, and rat since most genes will have 1-to-1 homologs with matched symbols.
** GeneId1
Entrez gene ID values (mouse)
** GeneId2
Entrez gene ID values (mouse)
** value
Latent sematic index scorel, value between 0 and 1

* LCorr

** GeneId1
** GeneId2
** value

* Investigators
What is this used for? This is part of Arthur Centeno's database for INFO files. 
As of March 2016 has about 86.54 rows.
** Address
** City
** Country
** Email
** FirstName
** InvestigatorId
** LastName
** OrganizationId
** Phone
** State
** Url
** UserDate
** UserLevel
** UserName
** UserPass
** ZipCode

* InfoFilesUser_md5
Password information GN1 (unsecure no salt) use SALT !
Only two users as of March 2016, Rob and Arthur.
** Password
** Username

* InfoFiles
INFO file metadata. INFO files are currently limited to molecular data sets (mRNA, protein, metabolomes). Majority of mRNA transcriptome data sets. This table set up by Arthur 2014-2015. Compare to "Datasets" which appears to be a subset of this table. 

"InfoFiles" has 700 rows whereas "Datasets" has only 230 rows (RWW March 2016)
** About_Array_Platform
** About_Cases
** About_Data_Values_Processing
** About_Download
** About_Tissue
** AuthorizedUsers
** AvgMethodId
** Citation
** City
** Contact_Name
** Contributor
** Country
** DatasetId
** Data_Source_Acknowledge
** DB_Name
** Department
** Emails
** Experiment_Type
** GeneChipId
** GEO_Series
** GN_AccesionId
** InbredSet
** InbredSetId
** InfoFileId
** InfoPageName
** InfoPageTitle
** Laboratory
** Normalization
** Organism
** Organism_Id
** Organization_Name
** Overall_Design
** Phone
** Platforms
** Progreso
** QualityControlStatus
** Samples
** Species
** SpeciesId
** State
** Status
** Street
** Submission_Date
** Summary
** Tissue
** TissueId
** Title
** URL
** ZIP

* IndelXRef
Just for C57BL/6J and DBA/2J indels with 140,000 rows
** IndelId
** StrainId1
** StrainId2

* IndelAll
Information about indels (comaprable to the SNP data) used by the Mouse SNP browser GN1 only at this time. Only B6 versus D2 mouse indels have been entered so far. Has 140,000 rows but should have at least twice as many. Probably entered at an early stage of sequence analysis (circa 2010) (DA March 2016)
** Chromosome
** Id
** InDelSequence
** Mb_end
** Mb_start
** Name
** Size
** SourceId
** SpeciesId
** Strand
** Type

* InbredSet
Important table that is used to select appropriate analytic and mapping methods. We should change the name of this table to "Population_Description" or "Sample_Description"  (RWW March 2016)

"orderid" is used to set the order of in the pull-down menu.
** FullName
** GeneticType
e.g. intercross
** Id
** InbredSetId
** InbredSetName
** MappingMethodId
prefered mapping method to use ??
** Name
** orderid
** public
** SpeciesId

* HumanGene

** description
** hLocusId
** Id
** mLocusId
** Name
** rLocusId

* Homologene
Coupling genes to other genes using homology between them. (DA March 2016)
** GeneId
** HomologeneId
** TaxonomyId

* H2
Deprecated or delete this table (RWW March 2016). Compare to "Probe h2" used in Probe Table. Plain "H2" table has 60,000. This could be at the probe set level whereas "Probe h2" is for the individual probes on the probe set. (Danny Arends = DA March 2016)
** DataId
** H2SE
heritability standard error ??
** HPH2
** ICH2

* GORef
Gene Ontology identifiers linked to gene symbols. Only properly implemented for mouse. Note that this table does not have a GORef.species field. GN1 code does return hits even for Drosophila but symbols have mouse format. Check how this happens. (RWW March 2016)

 Important and should probably be updated periodically. Used for GO searches in GN1 and GN2. E.g. GO:0045202 searches for synapse-associated genes listed in the Gene Ontology using information in this table.
** genes
** goterm
** id

* GenoXRef
The table is used to establish links to other tables that contain genotype data. contains the information on which markers should be used for QTL mapping
** cM
cM is the centiMorgan location of the marker
** DataId
DataID is an identifier for a specific vector of genotypes
** GenoFreezeId
The GenoFreezeID is the number that references a single coherent genotype file. For example the first BXDgeno file is 0001.
** GenoId
GenoID is an identifier for a specific marker (SNP or other)
** Used_for_mapping
Flag used to decide if a marker is used for mapping or not

* GenoSE
DELETE THIS TABLE. This is to be a stupid table that can be deleted (Rob W, March 2016). Likely to have been added as a parallel table similar to those used from phenotypes and quantitative traits.
** DataId
** error
** StrainId

* GenoFreeze
Population or cohort description. Links the population or cohort data described here with the genotype information. The entry in this table must be present to enter the genotype data in the GenXRef and Data tables.

As of March 2016 has about 26 rows for mouse, rat, Drosopholia, potatoe, human, etc.
** AuthorisedUsers
** confidentiality
0 = not confidential, 1 = confidential.
** CreateTime
now()
** FullName
Extended genotype file name for the cohort.
** Id
Primary key referenced in GenoXRef.GenoFreezeId.
** InbredSetId
Foreign key to InbredSet.Id
links to the mapping population. 
** Name
Genotype file for the cohort name. 
** public
1 = beta data, 2 = available in GeneNetwork. 
** ShortName
Shortened genotype name.

* GenoData
This is the table that actually contains genotypes for individuals. Could be renamed "Genotype_Data". The GenoData.value will need to be updated to allow a wider range of values and  probabilities to accommodate complex crosses and cohorts. Right now the GenoData.value is labeled as a "float" but most of the code in GN expects to see values of  -1, 0, or 1. 

As of March 2016 (140 million rows where each row is genotype from individual X at marker Y)
** Id
Primary key identifier
** StrainId
This field should be renamed SampleID.
** value
The actual genotype of the case. Usually -1, 0, 1. U is not allowed yet. No blank allowed. 

* GenoCode
Only has one row as of March 2016 and used exclusively for BXD or B6xD2 crosses.
May be used for Haplotype Analyst display function in QTL maps. May also be used to determine polarity of effect size (B allele defined as -1 and D allele defined as 1).

Inbred Set 1, AlleleType values mat, pat, het, unk, and AlleleSymbol B, D, H, U, and DatabaseValue -1, 1, 0 or NULL

"InbredSetId" should be renamed "GroupID" or "CohortID"
** AlleleSymbol
** AlleleType
** DatabaseValue
** InbredSetId

* GenoAssembly
Small table which identifies the assembly of the genotype markers, or similar, information important for mouse data, where there is more than one assembly per species. The entry in this table must be present before enering the data in GenoPos table. ID1206
** Comment
** Disable
'Y','N' The disabled assemblies are not used in the analysis? Not verified. ID1206
** Id
PK referenced in GenoPos.AssemblyId, ProbeSetPos.AssemblyId. ID1206
** Name
Name of assembly. ID1206
** SpeciesId
Foreign key to Species.Id

* Geno2
Contains descriptions of markers used in the genotype (.geno file). 
This table looks like abbreviated Geno table. It has the same number of records as Geno and Geno_0609. ID1206
** Id
Can reference Geno.Id, Geno_0609.Id and GenoXRef.GenoId. ID1206
** Name
Marker name or other identificator
** Sequence
Marker sequence, if known.
** Source
The provider of the information, could be institute. ID1206
** Source_Old
The provider of the information, could be institute. Often the same as Source. ID1206
** SpeciesId
Foreign key to Species.Id
References Species table

* Geno
Genotype marker information, not the actual genotypes. Should probably be renamed "Marker_Information" or "Genotype_Marker_Info".  If genotype data is held in MySQL, then this table is used for updating genotypes and for the production of a new ".GENO" file after an update. Currently, the update feature i used almost exclusively for BXD mouse cohort. Contains 400,000 rows each with sequence data around markers (usually SNPs, but some microsatellites and other weird variants). (RWW March 2016)

This table contains descriptions of markers, the same ones that are used in the corresponding .geno file. This table is exactly the same as Geno_0609, so one of them might be redundant and also has one-to-one relationship with Geno2 table. For some information there is more than one entry (marker name, position) which may lead to inconsistent state. The marker fields look like references to single nucleotide poymorphisms within the marker, but this hasn't been confirmed. 

For a few groups, this  table can be updated by authorized users using an interface at www.genenetwork.org/manager.html (Update Genotype functions) (comment updated March 14, 2016 by RWW)
** Chr
Foreign key to Chr_Length.Name
** Chr_mm8
Chromosome name. Should be deleted or updated to mm10 (March2016)
** chr_num
Foreign key to Chr_Length.OrderId
This is a number used to order chromosomes, thus Chr X in mouse = Chr 20, and Chr Y = 21
** cM
** Comments
Comment 
** Id
Primary key, used in Geno2, GenoXRef
** Marker_Name
** Mb
This may be the genome-wide megabase values that is cumulative. Chr 2 starts at about 200 Mb. (Double check)
** Mb_mm8
Megabase position (not bp). Should be deleted or updated to mm10 (March2016)
** MB_UCSC
Position, not sure about the units. MB probably. ID1206
** Name
Marker name, or other ID used in .geno file
** Sequence
The sequence of the marker, if known. Usually 100 nt on each side of the SNP with the SNP is square brackets for BLAT analysis. gctcctaattgctgagatttctctccagctc<br> TGCCTCCCTTTCACACTCTCCTGCCCGTCCC<br> AATCAGAACATTAGAGCTGATAAGATTTACT<br> TATGGAC[CT]GATCTAAAATAGAAGTCCTT<br> TGGAGAACTTTGAGAGCTTTTCCAAGAAGTA<br> AAGTCGGTTAGTTGCTTTTCCAAAGAAATAA<br> AGTTAGTGATTCTCCACA
** SNP_129x1_SvJ
** SNP_AJ
** SNP_B6
** SNP_BALBc
** SNP_C3H
** SNP_DBA2
** Source
The provider of the information, could be institute.
** Source2
The provider of the information, could be institute, often the same as Source.
** SpeciesId
** used_by_geno_file
This is a flag that determines if a marker is used for mapping. Can be set by authorized users using the "Update Genotype" function. May be redundant with field in GenoXRef.

* GeneRIF_BASIC
Gene Reference into Function data table from NCBI. Data are updated each night from NCBI and used to perform RIF queries in GN1 and GN2. (RWW March 2016). Probably should be called "GeneRIF_Data_NCBI"
** comment
** createtime
** GeneId
** PubMed_ID
** SpeciesId
** symbol
** VersionId

* GeneRIFXRef
Gene RIF link to corresponding NCBI PubMed Reference ID. (RWW March 2016). Odd that there are three primary keys. (RWW March 2016)
** GeneCategoryId
** GeneRIFId
** versionId

* GeneRIF
This is to be more than just GeneRIF but also includes GeneWIKI fields used to allow users to enter new GeneWiki data sets. Should be renamed GeneWiki_Entry_Data
** comment
** createtime
** display
** email
** Id
** initial
** PubMed_ID
** reason
** SpeciesId
** symbol
Official gene symbol taken from NCBI
** user_ip
** versionId
** weburl

* GeneMap_cuiyan
Deprecated. This is a table used to link from GN1 single chromosome maps to Yan Cui's polyMiRTS database of microRNA binding sites linked to SNPs.
** GeneID
** id
** Symbol
** TranscriptID

* GeneList_rn3
This table contains information on recognized genes in Rattus norvegicus (rn) from the rat build 3 (RGSC v3.4 of November 2004; see http://www.ncbi.nlm.nih.gov/genome/guide/rat/release_notes.html). Data are used by the Interval Analyst table in WebQTL. Data originally taken from UCSC ftp site July 2005. Annotation by Rob Williams and Senhua Yu, Aug. 2005. Created by Evan Williams, July 2005.
** chromosome
** flag
Check status of this field. Really a probe set field, not a gene field. 
** genBankID
GenBank identifier
** geneDescription
official gene description
** geneID
Entrez gene ID 
** geneSymbol
official gene symbol
** id
** identity
** kgID
** ProbeSet
** qEnd
** qSize
** qStart
** score
** sequence
Check status of this field. Really a probe set field, not a gene field. 
** span
** specificity
Check status of this field. Really a probe set field, not a gene field. 
** strand
** txEnd
** txSize
** txStart
** unigenID
Unigene identifier (Build 144)

* GeneList_mm8
Annotation by Samina (November 15, 2006)
** agilentProbeSetID
** alignID
** cdsEnd
End of the coding sequence
** cdsStart
Start of the coding sequence
** chromosome
Chromosome where the gene is found
** exonCount
Number of exons in this gene
** exonEnds
Ending position of the exons
** exonStarts
Starting position of the exons 
** geneDescription
Description of the gene
** geneID
Gene identifier
** geneSymbol
Gene Symbol
** id
Internal identifier used by GeneNetwork
** m430ProbeSetID
** NM_ID
** proteinID
Protein identifier
** strand
Sense strand (+) or Antisense strand (-)
** txEnd
End position of the gene
** txStart
Starting position of the gene
** u74ProbeSetID
Affymetrix u74av2 array probe set identifier

* GeneList_mm6
This table contains the complete dataset from the Mar 2005 (mm6) mouse genome from UCSC.
** agilentProbeSetID
** alignID
** cdsEnd
** cdsStart
** chromosome
** exonCount
** exonEnds
** exonStarts
** geneDescription
** geneID
** geneSymbol
** id
** m430ProbeSetID
** NM_ID
** proteinID
** strand
** txEnd
** txStart
** u74ProbeSetID

* GeneList_hg17

** alignID
** cdsEnd
** cdsStart
** chromosome
** exonCount
** exonEnds
** exonStarts
** geneID
** geneSymbol
** id
** isGuess
** NM_ID
** proteinID
** strand
** txEnd
** txStart

* GeneList
Out of date in 2016. May be used by chromosome QTL maps and by SNP Tables. GN would need these data for multiple species. This is apparently meant as a generic table for any species. Should be updated periodically from BioMart or UCSC Genome Browser.
** AlignID
** cdsEnd
This is the end of the coding sequence (cds = protein coding part of mRNA)
** cdsEnd_mm8
** cdsStart
This is the start of the coding sequence (cds = protein coding part of mRNA)
** cdsStart_mm8
** Chromosome
** Chromosome_mm8
** exonCount
** exonCount_mm8
** exonEnds
This is a concatenated list of exon starts, separated by comma
** exonEnds_mm8
** exonStarts
This is a concatenated list of exon starts, separated by comma
** exonStarts_mm8
** GenBankID
** GeneDescription
** GeneID
This is the GeneID which used to be called the LocusLinkID
** GeneSymbol
** Id
** Info_mm9
** kgID
** NM_ID
This is  the messenger RNA reference id number (nucleotide Message)
** ProteinID
** SpeciesId
** Strand
** Strand_mm8
** TxEnd
We currently think that this should be the end of the 3' UTR   but need to confirm
** TxEnd_mm8
** TxStart
We currently think that this should be the start of the 5' UTR  but need to confirm
** TxStart_mm8
** UnigenID
Unigene IDs, often multiple

* GeneChip
This table lists the array platforms that are used by the GeneNetwork. For example Affymetrix U74Av2, M430A, RAE230A. etc. 
The name 'GeneChip' is much too specific. 'Platform' would be better term.

A new record can be inserted only if the relevant record in Species table is in place. ID1206

Please use the following conventions for naming array platform:

MG_U74AV2  is a good form but
Affy_U74Av2 would be better

Start with a four letter vendor code (Upper case lower case underscore)
"Affy_" for Affymetrix
"Illu_" for Illumina
"Agil_"

then use the name of the array given by the vendor
for example:  U74AV2, MOE430A, MOE430B, MOE430V2, RAE230A, G4121A, MOUSE6.0, MOUSE6.1, MOUSE6.2
** GeneChipId
** GeneChipName
** GeoPlatform
** GO_tree_value
** Id
internal GN identifier of the array platform
** Name
array platform long identifier or name
** SpeciesId
Foreign key to Species:Id
species for which this array was designed to work best (U74Av2 for mouse, RAE230A for rat)
** Title

* GeneCategory
DEPRECATED. Used by GeneWiki notes to classify notes. This table has never been used.
If this table is deleted then please delete all checkboxes associated with GeneWiki data entry. RWW March 2016.
** Id
** Name

* Genbank
DEPRECATED. Apparently only used by the "Probe Tools". This table contains a complete or truncated copy of GenBank sequence data associated with particular Affymetrix Probe sets.  When a GenBank sequence entry was long, we took only the most terminal 1000 nt under the assumption that this was the 3' end of the sequence. This assumption will often be incorrect. This table is used primarily in association with Affymetrix Probe sets generated using GenBank sequence. The Probe Tools table in GN is able to BLAT the GenBank sequences provided in this table.
Created by Yanhua Qu, August 2005. Deprecated by RWW March 2016.
** GenbankId
** Id
conventional GenBank identifier of the sequence
** Sequence
up to 1000 nucleotides of sequence
** SpeciesId
species identifier used by GenBank and NCBI

* EnsemblChip
Deprecated and not a function in GN2. For that matter, not even a function of most array data in GN1. One of several tables created by Hongqiang Li to be used with Probe Tool functions for M430 Affymetrix array to show location of probes only in mouse. All locations are equivalent to mm8 in mouse. Xusheng Wang points out that this could be done from UCSC browser. 

This table should also be updated at some point. 


Probe locations were obtained from Ensembl ftp://ftp.ensembl.org/pub/current_mus_musculus/data/mysql/mus_musculus_core_43 by Hongqiang Li. We made use of text files and MySQL tables called:

oligo_feature.txt.table.gz (25774 KB file of 3/1/07 1:53:00 AM)
oligo_probe.txt.table.gz (24411 KB 3/1/07 1:54:00 AM)
seq_region.txt.table.gz (383 KB, 3/1/07 1:59:00 AM)
** Id
** Name
** ProbeSetSize
** Type

* Docs
Rob suspects that this table is used to track images, PDF, etc, that are uploaded into GN documentation (e.g., References and Glossary).  Check with Arthur or Lei. (RWW March 2016)
** content
** entry
** id
** title

* Description_of_Schema
<P>This page provides a partial description of the database tables used by The GeneNetwork.  This schema is dynamically updated from the MySQL database. Short text annotation at the top of many tables is entered manually. [Implemented by Hongqiang Li, Aug 2005.]

<P><B>Suggested Conventions for Table Names</B>: Please start with an upper case character for each distinct word used to name the table, for example "AccessLog",  "AvgMethod", "ProbeFreeze". A mix of upper case and lower case is fine. In general, avoid unscore. However, use of the underscore character is apppropriate for particular freezes or source of data in a table, for example,  "GeneList_hg17" and "CeleraInfo_mm6".

<P><B>Suggested Conventions for Field Names</B>: Use of lower case is preferred when possible. Separate words in a field name with underscore: examples: ip_address, allele_B6.  Please try to make field names self-explanatory to a bioinformatics expert. Please annotate and describe the field name when you make a new table or add a new field.  Avoid cryptic suffixes and prefixes to field names.

<P>Last edited March 21, 2016

* DBType

** Id
** Name

* DBList

** Code
** DBTypeId
** FreezeId
** Id
** Name

* Dataset_mbat
DEPRECATED. What is mbat? Is this associated with 8 tissues and phenotypes of BXD only.  Hippocampus, brain, cerebellum, eye, neocortex, NAc, PFC, striatum and phenotypes. All before 2008. (RWW Mar 2016)
** cross
** database
** database_LongName
** id
** species
** switch
** tissue

* DatasetStatus
This is the private versus public flag for a large molecular phenotype data sets (aka prior to 2016 as a ProbeSetFreeze) (RWW March 2016). This table probably created by Arthur Centeno in 2014-2015.
** DatasetStatusId
** DatasetStatusName

* Datasets
DEPRECATED in favor of "InfoFiles". Check if this is still needed (March 2016). These are the annotation/metadata fields used to describe molecular phenotypes only (aka ProbeSetFreezes). This table designed by Arthur Centeno and used to generate and modify "INFO" tables. Created in 2014-2015.
** AboutCases
** AboutDataProcessing
** AboutPlatform
** AboutTissue
** Acknowledgment
** Citation
** Contributors
** DatasetId
** DatasetName
** DatasetStatusId
** ExperimentDesign
** GeoSeries
** InvestigatorId
** Notes
** PublicationTitle
** Summary

* DatasetMapInvestigator
Dataset owner or creator. Not sure how this is used? Is it used to for data access or password control?  Check with Arthur Centeno or Lei Yan if this is used to define private ProbeSetFreeze (RWW Mar 2016). Probably created by Arthur Centeno 2014-2015. Why is the word "Map" in the table name?
** DatasetId
** Id
** InvestigatorId

* Data
This simple but huge table actually contains the bulk of data in GeneNetwork. Almost all trait data is defined by a data identifier and a case value (e.g., strain or F2 or individual). This table only contains the main value (typically the mean or average) for each case, strain, or individual. The Standard Error of the Mean is kept in the table called "SE".

Created by Jintao, March 2003. 
To retrieve or insert the data for an experiment there should be also corresponding entries in the ProbeXRef table for the raw data (references ProbeFreeze) or in the ProbeSetXRef table for transformed data (references ProbeSetFreeze). ID1206
** Id
** StrainId
Foreign key to InbredStrain:Id
** value

* Chr_Length_Evan
This is a table created by Evan Williams, July 2006. Not sure what it is for. [Rob Williams, July 24, 2006)
** Length
length of the chromosome, presumably in megabases
** Name
name of the chromosome, usually a number but also UN=unknown, X, Y, Mt (mitochondria)
** OrderId
Not
** SpeciesId
the identifier for the species from NCBI

* Chr_Length_0609
PLEASE DELETE IF POSSIBLE. May be used by ARCHIVE SITE. This table is identical to Chr_Length table, both structure and records. One of them could be redundant.
ID1206
** Length
** Name
** OrderId
** SpeciesId
Foreign key to Species.Id

* Chr_Length
This table provides the approximate length of a chromosome in megabases taken from the most recent public genome assemblies of several species. As of Aug. 2005, this table contains lengths for mouse, rat, and Arabidopsis chromosomes. No data for Barley since there is not physical assembly yet.  We are usually missing the Y chromosome and the mitochondrial genome (Chr Y and Chr M, respectively).

Some of these fields should be semantically registered (SpeciesId, Chromosome_name).
Created by Jintao Wang, 2004.
** Length
the length of the chromosome in megabase units
** Name
At most 3 letter name of the chromosome ID1206
** OrderId
the numerical order of chromosomes for purposes of generating whole genome maps (Chr X is 20 in mouse)
** SpeciesId
Foreign key to Species.Id
the identifier for the species. This may be the official NCBI species code or just an internal GN code. Check

* CeleraINFO_mm6
PLEASE DELETE: DEPRECATED: use the Snp table instead.

CeleraINFO_mm6 provides information on single nucelotide polymorphisms (SNPs) for five strains of mice, C57BL/6J, DBA/2J, A/J, 129S1/SvImJ, and 129X1/SvJ, obtained from Celera Genomics by Alex Williams and Chris Vincent, summer 2003. 

"mm6" refers to the megabase positions taken from the sixth build of Mus musculus as presented in UCSC Genome Browser (also known as NCBI Mouse Build 34 that was current from March 2005 through approximately March 2006). 

mm6 data were generated by Yanhua Qu by BLAT sequence alignment of ~300 bp of sequence around each SNP. This table was generated by Robert Crowell and Yanhua Qu, June 2005, for use on WebQTL physical maps. 

** allele_AJ
the allele of A/J
** allele_B6
the allele of C57BL/6J
** allele_D2
the allele of DBA/2J 
** allele_S1
** allele_X1
** B6_AJ
same as above but used for the AXB/BXA cross
** B6_D2
a flag (0 or 1) that denotes if B6 and D2 have ths same allele (0) or a different allele (1) that is used in generating SNP tracks on BXD physical maps
** chromosome
mouse chromosome from BLAT alignment
** D2_AJ
same as above
** flanking3
** flanking5
** id
** MB_celera
megabase position given by Celera
** MB_UCSC
mouse mm6 position in megabases from the UCSC Genome Brower mm6 BLAT analysis (NCBI Build 34)
** MB_UCSC_OLD
the mm5 position values
** SNPID
from Celera Genomics

* CeleraINFO_mm5
OBSOLETE: Replaced by CeleraINFO_mm6

CeleraINFO_mm5 provides information on single nucelotide polymorphisms (SNPs) for three strains of mice--C57BL/6J, DBA/2J, and A/J--obtained from Celera Genomics by Alex Williams and Chris Vincent, summer 2003. 

"mm5" refers to the megabase positions taken from the fifth build of Mus musculus as presented in UCSC Genome Browser (also known as NCBI Mouse Build 33 that was current until March 2005). 

mm5 values were generated by Yanhua Qu by BLAT sequence alignment of ~300 bp of sequence of about 3 million Celera SNP sequences. This table was generated by Alex Williams and Robert Crowell (?), July-August 2004, for use on WebQTL physical maps.  

This table is now replaced by CeleraINFO_mm6. 


* CeleraINFO
Obsolete table. Jintao, can we delete this table? 

* CaseAttributeXRef
Table used for handle database metadata. One to one mapping for ProbeSetFreeze etc.
** CaseAttributeId
** ProbeSetFreezeId
** StrainId
** Value

* CaseAttribute
This is a new table created by Zach Sloan and Lei Yan in 2014-2015 to allow us to add cofactors and metadata that typically display in the Trait Data and Analysis page along side of the phenotype values for each case/sample. For example, if you review any of the human data sets where GROUP = "Brain, Development: Normal Gene Expression (Yale/Sestan)" you will find CaseAttribute data for the expression data sets such as Sex, PostMortem Interval (PMI), Age, Ethnicity, Tissue, pH. 

This table may not really be necessary, since all data here could also be put into the Phenotype data table. But then we need code that would allow the user (or programmer or annotator) to decide what cofactors to show in the Trait Data and Analysis page.
** Id
** Name
The name of the cofactor shown in the Trait Data and Analysis page (e.g. "Ethn.", "pH", "Sex")

* B_D_Tissue_CNS_GI_Merged_Average_Spearman
This static table was precomputed by Xusheng Wang, Sept 2008. It provides the correlations of gene expression across 23 tissues (combined data from both C57BL/6J (B) male and DBA/2J (D) male). The 23 "tissues" are actually based on data for about 60 samples, but manny of the samples are not independent and were pooled together prior to computing the correlations (for example, many brain regions were combined). The following tissues were included in computing the Spearman rank order Rho correlation. P values were also precomputed by Xusheng. The GeneId1 and GeneId2 are currently all mouse GeneIds. However, we probably should us HomolGene Ids or have ids for multiple species (animals only at this point). This table should be usable for mouse, rat, human, and other vertebrates.
** Correlation
** GeneId1
** GeneId2
** PValue

* B_D_Tissue_CNS_GI_Merged_Average_Pearson
This static table was precomputed by Xusheng Wang, Sept 2008. It provides the correlations of gene expression across 23 tissues (combined data from both C57BL/6J (B) male and DBA/2J (D) male). The following tissues were included in computing the Pearson product moment correlations. P values were also precomputed by Xusheng. The GeneId1 and GeneId2 are currently all mouse GeneIds. However, we probably should us HomolGene Ids or have ids for multiple species (animals only at this point). This table should be usable for mouse, rat, human, and other vertebrates.
** Correlation
** GeneId1
** GeneId2
** PValue

* BXDSnpPosition
PLEASE CORRECT TALBE AND FIELD NAMES. A table created by Hongqiang (October 2007) to be used to display SNPs on the interval maps for all crosses of mice including BXD, AXB, AKXD and B6D2F2 mouse crosses. Field are not well annotated. All data in this table is taken from the main mouse SNP table.  This table should be refreshed every day or week. Hongqiang would need to write a script to do this. Not done yet (Nov 13, 2007).  Reviewed by Kev and Rob, July 2008. Evan may have used this table to get SNPs to show up on the BHF2 interval maps. Still needs to be renamed. Needs to handle CXB RI set and LXS RI set.
** Chr
Chromosome
** Mb
SNP position in megabases rather than basepairs from mm8. Is this really mm8. Should be mm9 up to Mar 2016.
** StrainId1
Not sure, Strain identifier (probably C57BL/6J)
** StrainId2
Not sure, Strain identifier (probably DBA/2J

* AvgMethod
Should be deprecated. Used by ProbeSetFreeze (March 2016 by RWW)

A table that simply lists the transformations used to generate microarray probe or probe set data. For example, for the Affymetrix platform MAS5, PDNN, RMA, are common transformation methods. For Illumina, the transform methods included Rank, RankInv, LOESS, LOESS_NB (no background). A single raw data set can be transformed in several ways, giving rise to a family of related data sets. This table was originally set up by Jintao Wang. We should confirm that this table is still used. Possibly a development vestige. "mlratio" was added by Evan Williams June 2008 to describe the Agilent "mean log" method used by Lusis and colleagues.
The table is referenced in ProbeSetFreeze, so it is still in use on Dec 14,2006. 6 records on Dec14, 2006. ID12065
** AvgMethodId
** Id
internal identifier
** Name
name or abbreviation of the method used to generate the probe set consensus estimates of transcript abundance
** Normalization

* Allele
This table is one of a group of four tables used by the SNP Viewer component of GeneNetwork.  Contains one record for every allele for all SNPs across all datasets.
[Created by Robert Crowell, August 18, 2005. Annotation of table, RWW, Aug 19, 2005; Robert Crowell, Aug 22, 2005.]

<b>See also:</b>
<a href="/cgi-bin/beta/schema.py#Snp">Snp</a>
<a href="/cgi-bin/beta/schema.py#Strain">Strain</a>
** Id
internal GeneNetwork identifier for allele
** SnpId
Foreign key to Snp.Id
internal GeneNetwork identifier for this SNP
** StrainId
Foreign key to Strain.Id
internal GeneNetwork identifier for strain
** Value
allele nucleotide in strain; conventions: upper case are firm SNPs, lower case imputed, heterozygotes (e.g., A/C), dash (-) used for deletion, w,x,y,z,~ used for deprecated A,C,G,T,- SNPs. 

* AccessLog
This table tracks access time and IP addresses. Used for logging in registered users and tracking cookies.
Created by Jintao Wang, 2004.
** accesstime
time stamp of login
** id
internal identifier
** ip_address
Internet protocol address