Field Definitions

Publication Details

Estimated reading time: 2 minutes

Can you direct me to the correct file that provides detailed definitions for primary keys and table fields?

Most table field descriptions are still valid and are located in the Data Dictionary. Changes to the tables in each build are listed in the Schema Change document. (3/28/05)

What is the field definition for the refSNP number?

dbSNP stores the refSNP number as an integer (a big integer) (7/6/05).

What are the definitions of contig_chr, contig_pos, and contig_build_id?

contig_chr is the chromosome for this SNP (if known).
contig_pos is the position on the chromosome.
contig_build_id is the build in which the SNP was last edited.

What is the definition of "contig_pos=0"?

This is a case in which the SNP has not been mapped to the human genome
reference sequence.

SNPAncestralAllele table

What is the definition for the ancestral_allele_id field located in the SNPAncestralAllele table? If the ancestral_allele_id is equal to 7, what does the id represent?

SNPAncestralAllel.ancestral_allele_id refers to Allele.allele_id.

If ancestral_allele_id = 7, select allele from Allele where allele_id =7 gets "C".(3/7/05)

Does the "ancestral_allele_id" column in the "SNPAncestralAllele" table refer to the "univar_id" column in the"UniVariation" table?

You are on the right track: "ancestral_allele_id" refers to table Allele.

Please note that "ancestral allele" refers to the allele state in ancestral chromosomes. "ancestral allele" does not contain variation information in itself. UniVariation lists at least two alleles (A/G). (01/23/08)

I want the ancestral allele for a number of rsIDs, but can’t find rsID numbers in Allele.bcp.gz and SNPAncestralAllele.bcp.gz.

If you go to the dbSNP Data Dictionary and search for “SNPAncestralAllele”, you’ll find that the rsID is stored as an integer in the first column of the table.

Here’s an example to help you — say you want to find the ancestral allele for rs3:

1.

Look for the number “3” in first column of the SNPAncestralAllele table and you’ll find that the corresponding ancestral allele ID (located in the second column) is 7.

2.

Now, look in the Allele table for the allele letter that corresponds to
Ancestral allele ID = 7. In the allele table, the allele ID number is again located in the first column:

7 C 2003-02-22 01:11:00.0 2 2003-10-06 17:42:00.0

So you can see from this example, that the ancestral allele for rs3 is “C”. (08/29/08)

SNPChrPosOnRef

What does what does orient=0 and orient=1 represent in the "orient" column of the SNPChrPosOnRef table?

Orient=0 means that the SNP is in the same orientation as the chromosome. Orient=1 means that the SNP is in the opposite orientation as the chromosome. (01/03/08)

SNPContigLocusId table

How do I determine to the refSNP (rs) ID for the snpID in theSNPContigLocusId.bcp.gz file?

The refSNP (rs) ID is the ID number given in the "snp_id" field of the SNPContigLocusId.bcp.gz in file. You can confirm this by doing the following:

Look at the rs number associated with a gene on a refSNP page. For example, go to the refSNP cluster report for rs268. If you look at the very top of the “Geneview” section of this report, you will see that the SNP is associated with the LPL gene (Lipoprotein Lipase). If you click on the “LPL” link at the top of “Geneview” section, you will get a Entrez Gene report for LPL, which states that the gene ID for LPL is 4023.

Now, if you go to the SNPContigLocusId.bcp file, you will see a row that contains snp_id 268. That same row will have gene_id 4023.

I should mention that you will see two rows for rs268, one representing the reference contig, and one representing the Celera contig. (11/19/07)

Is SNP location (coding region, splice site or promoter region) stored in the fxn_class field of the SNPContigLocusId table? If so, where is the corresponding function class for promoter region?

Yes, the location information for SNPs are in the the fxn_class field of the SNPContigLocusId table, but I don't think that the current snp function class set includes "promoter region".

SubInd table

Where can I find the definition for the submitted_strand_code column that is located in the SubInd table?

You can search for the column definition using the online database dictionary. (4/28/06)