Ensembl Variation - Schema documentation

Defines various attributes used elsewhere in the database

Example:

See below the query to display a subset of the attrib entries:

SELECT * FROM attrib WHERE attrib_type_id IN (469,470,471) ORDER BY attrib_id LIMIT 21;

Show query results

attrib_id	attrib_type_id	value
1	469	SO:0001483
2	470	SNV
3	471	SNP
4	469	SO:1000002
5	470	substitution
6	469	SO:0001019
7	470	copy_number_variation
8	471	CNV
9	469	SO:0000667
10	470	insertion
11	469	SO:0000159
12	470	deletion
13	469	SO:1000032
14	470	indel
15	469	SO:0000705
16	470	tandem_repeat
17	469	SO:0001059
18	470	sequence_alteration
19	469	SO:0001628
20	470	intergenic_variant
21	471	INTERGENIC

Groups related attributes together

Defines the set of possible attribute types used in the attrib table

Example:

See below the command to display a subset of the attrib_type entries:

SELECT * FROM attrib_type WHERE attrib_type_id > 468 LIMIT 10;

Show query results

attrib_type_id	code	name	description
469	SO_accession	SO accession	Sequence Ontology accession
470	SO_term	SO term	Sequence Ontology term
471	display_term	display term	Ensembl display term
472	NCBI_term	NCBI term	NCBI term
473	feature_SO_term	feature SO term	Sequence Ontology term for the associated feature
474	rank	rank	Relative severity of this variation consequence
475	polyphen_prediction	polyphen prediction	PolyPhen-2 prediction
476	sift_prediction	sift prediction	SIFT prediction
477	short_name	Short name	A shorter name for an instance, e.g. a VariationSet
478	dbsnp_clin_sig	dbSNP/ClinVar clinical significance	The clinical significance of a variant as reported by ClinVar and dbSNP

Contains alleles that did not pass the Ensembl filters

This table contains descriptions of reasons for a variation being flagged as failed.

Example:

See below the list of the descriptions available in the Ensembl variation databases:

SELECT * FROM failed_description;

Show query results

failed_description_id	description
1	Variant maps to more than 3 different locations
2	None of the variant alleles match the reference allele
3	Variant has more than 3 different alleles
4	Loci with no observed variant alleles in dbSNP
5	Variant does not map to the genome
6	Variant has no genotypes
7	Genotype frequencies do not add up to 1
8	Variant has no associated sequence
9	Variant submission has been withdrawn by the 1000 genomes project due to high false positive rate
11	Additional submitted allele data from dbSNP does not agree with the dbSNP refSNP alleles
12	Variant has more than 3 different submitted alleles
13	Alleles contain non-nucleotide characters
14	Alleles contain ambiguity codes
15	Mapped position is not compatible with reported alleles
16	Flagged as suspect by dbSNP
17	Variant can not be re-mapped to the current assembly
18	Supporting evidence can not be re-mapped to the current assembly
19	Variant maps to more than one genomic location
20	Variant at first base in sequence
21	Reference allele does not match the bases at this genome location
22	Alleles cannot be resolved

For various reasons it may be necessary to store information about a structural variation that has failed quality checks (mappings) in the Structural Variation pipeline. This table acts as a flag for such failures.

For various reasons it may be necessary to store information about a variation that has failed quality checks in the Variation pipeline. This table acts as a flag for such failures.

For various reasons it may be necessary to store information about a variation feature that has failed quality checks. This table acts as a flag for such failures.

This table holds genotypes compressed using the pack() method in Perl. These genotypes are mapped to particular genomic locations rather than variation objects. The data have been compressed to reduce table size and increase the speed of the web code when retrieving strain slices and LD data. Only data from resequenced samples are used for LD calculations are included in this table

This table holds genotypes compressed using the pack() method in Perl. These genotypes are mapped directly to variation objects. The data have been compressed to reduce table size. All genotypes in the database are included in this table (included duplicates of those genotypes contained in the compressed_genotype_region table). This table is optimised for retrieval from variation.

This table stores genotypes and frequencies for variations in given populations.

This table stores the read coverage of resequenced samples. Each row contains sample ID, chromosomal coordinates and a read coverage level.

This table holds uncompressed genotypes for given variations.

This table is only needed to create master schema when run healthcheck system. Needed for other species, but human, so keep it.

This table stores various metadata relating to the database, generally used by the Ensembl web code.

This table gives the coordinate system used by various tables in the database.

This table stores the relationship between the internal allele identifiers and the alleles themselves.

Stores information about the available co-ordinate systems for the species identified through the species_id field. Note that for each species, there must be one co-ordinate system that has the attribute "top_level" and one that has the attribute "sequence_level".

This table stores genotype codes as multiple rows of allele_code identifiers, linked by genotype_code_id and ordered by haplotype_id.

This table stores the relationship between Ensembl's internal coordinate system identifiers and traditional chromosome names.

This table holds a short string to distinguish data submitters

This table contains the SubSNP(ss) ID and the name of the submitter handle of dbSNP.

This table stores details of the phenotypes associated with phenotype_features.

This table stores information linking entities (variants, genes, QTLs) and phenotypes.

This table stores additional information on a given phenotype/object association. It is styled as an attrib table to allow for a variety of fields to be populated across different object types.

This table stores accessions of phenotype ontology terms which have been linked to phenotype.descriptions

Contains encoded protein function predictions for every protein-coding transcript in this species.

Contains information on the data use in protein function predictions

Maps a hex MD5 hash of a translation sequence to an ID used for the protein function predictions

Used to store groups of populations displayed separately on the Population Genetics page

Stores information about an identifiable individual, including gender and the identifiers of the individual's parents (if known).

Used to store alternative names for individuals when data comes from multiple sources.

This table gives a detailed description for each of the possible individual types: fully_inbred, partly_inbred, outbred, mutant

Example:

See below the list of individual types:

SELECT * FROM individual_type;

Show query results

individual_type_id	name	description
1	fully_inbred	multiple organisms have the same genome sequence
2	partly_inbred	single organisms have reduced genome variability due to human intervention
3	outbred	a single organism which breeds freely
4	mutant	a single or multiple organisms with the same genome sequence that have a natural or experimentally induced mutation

Stores information about a population. A population may be an ethnic group (e.g. Caucasian, Hispanic), assay group (e.g. 24 Europeans), phenotypic group (e.g. blue eyed, diabetes) etc. Populations may be composed of other populations by defining relationships in the population_structure table.

This table stores hierarchical relationships between populations by relating them as populations and sub-populations.

Used to store alternative names for populations when data comes from multiple sources.

Stores information about a sample. A sample belongs to an individual. An individual can have multiple samples. A sample can belong only to one individual. A sample can be associated with a study.

This table resolves the many-to-many relationship between the sample and population tables; i.e. samples may belong to more than one population. Hence it is composed of rows of sample and population identifiers.

Used to store alternative names for samples when data comes from multiple sources.

This table contains identifiers of associated studies (e.g. NHGRI and EGA studies with the same PubMed identifier).

This table contains details of publications citing variations. This information comes from dbSNP, UCSC and Europe PMC.

This table contains details of the source from which a variation is derived. Most commonly this is NCBI's dbSNP; other sources include SNPs called by Ensembl.
You can see the complete list, by species, here.

Example:

See below the command listing all the data sources in the human variation database:

SELECT * FROM source ORDER BY source_id;

Show query results

source_id	name	version	description	url	type	somatic_status	data_types
1	dbSNP	156	Variants (including SNPs and indels) imported from dbSNP	http://www.ncbi.nlm.nih.gov/projects/SNP/	NULL	mixed	variation
2	Archive dbSNP	156	Former dbSNP variant names, merged by variant	http://www.ncbi.nlm.nih.gov/projects/SNP/	NULL	mixed	variation_synonym
3	dbSNP HGVS	156	HGVS annotation from dbSNP	http://www.ncbi.nlm.nih.gov/projects/SNP/	NULL	mixed	variation_synonym
4	Former dbSNP	156	Former dbSNP variant names, merged by allele	http://www.ncbi.nlm.nih.gov/projects/SNP/	NULL	mixed	variation_synonym
5	PharmGKB	20240521	A pharmacogenomics knowledge resource	https://www.pharmgkb.org/	NULL	germline	variation_synonym
8	HGMD-PUBLIC	20204	Variants from HGMD-PUBLIC dataset December 2020	http://www.hgmd.cf.ac.uk/ac/index.php	NULL	germline	variation,phenotype_feature
11	DGVa	202001	Database of Genomic Variants Archive	https://www.ebi.ac.uk/dgva/	NULL	mixed	structural_variation,phenotype_feature,study
13	NHGRI-EBI GWAS catalog	20240520	Variants associated with phenotype data from the NHGRI-EBI GWAS catalog	https://www.ebi.ac.uk/gwas/	NULL	germline	phenotype_feature,study
15	EGA	202405	Variants imported from the European Genome-phenome Archive with phenotype association	https://www.ebi.ac.uk/ega/	NULL	germline	study
16	UniProt	20240521	Variants with protein annotation imported from UniProt	http://www.uniprot.org/	NULL	mixed	variation_synonym
18	OMIM	202404	Variants linked to entries in the Online Mendelian Inheritance in Man (OMIM) database [imported via ClinVar]	http://www.omim.org/	NULL	germline	variation_synonym
20	Illumina_CytoSNP12v1	1	Variants from the Illumina Cyto SNP-12 v1 whole genome SNP genotyping chip designed for cytogenetic analysis	http://www.illumina.com/	chip	germline	structural_variation
21	Illumina_Human660W-quad	NULL	Variants from the Illumina Human 660W-Quad whole genome SNP genotyping chip designed for association studies	http://www.illumina.com/	chip	germline	structural_variation
22	Illumina_Human1M-duo	3	Variants from the Illumina Human 1M-Duo v3 whole genome SNP genotyping chip designed for association studies	http://www.illumina.com/	chip	germline	structural_variation
23	Affy GenomeWideSNP_6 CNV	NULL	Copy Number Variation (CNV) probes from the Affymetrix Genome-Wide Human SNP Array 6.0	http://www.affymetrix.com/	chip	germline	structural_variation
26	COSMIC	99	Somatic mutations found in human cancers from the COSMIC catalogue	https://cancer.sanger.ac.uk/cosmic/	NULL	somatic	variation,variation_synonym,phenotype_feature
32	ClinVar	202404	Variants of clinical significance imported from ClinVar	https://www.ncbi.nlm.nih.gov/clinvar/	NULL	germline	variation,variation_synonym,phenotype_feature
33	DDG2P	20221021	Genotype-to-Phenotype Database	https://www.ebi.ac.uk/gene2phenotype	NULL	germline	phenotype_feature
34	GIANT	1	The Genetic Investigation of ANthropometric Traits (GIANT) consortium is an international collaboration that seeks to identify genetic loci that modulate human body size and shape, including height and measures of obesity.	http://www.broadinstitute.org/collaboration/giant/index.php/Main_Page	NULL	germline	phenotype_feature
35	MIM morbid	20240620	Online Mendelian Inheritance in Man (OMIM) database	https://www.omim.org/	NULL	germline	phenotype_feature
36	Orphanet	20231204	The portal for rare diseases and drugs	https://www.orpha.net/	NULL	germline	phenotype_feature
37	MAGIC	1	MAGIC (the Meta-Analyses of Glucose and Insulin-related traits Consortium) represents a collaborative effort to combine data from multiple GWAS to identify additional loci that impact on glycemic and metabolic traits	http://www.magicinvestigators.org/	NULL	germline	phenotype_feature
39	HbVar	NULL	A Database of Human Hemoglobin Variants and Thalassemias	http://globin.bx.psu.edu/hbvar/menu.html	lsdb	germline	variation_synonym
40	LMDD	NULL	Leiden Muscular Dystrophy Database	http://www.dmd.nl/	lsdb	germline	variation_synonym
46	dbGaP	201405	The database of Genotypes and Phenotypes.	http://www.ncbi.nlm.nih.gov/gap	NULL	germline	phenotype_feature,study
47	AMDGC	1	The AMD Gene consortium is an international collaboration that seeks to identify genetic loci associated with age-related macular degeneration .	http://www.sph.umich.edu/csg/abecasis/public/amdgene2012/	NULL	germline	phenotype_feature,study
48	GEFOS	1	The GEnetic Factors for OSteoporosis Consortium international collaboration that seeks to identify common risk gene variants for osteoporosis	http://www.gefos.org	NULL	germline	phenotype_feature
50	Teslovich	1	Biological, clinical and population relevance of 95 loci for blood lipids	http://www.sph.umich.edu/csg/abecasis/public/lipids2010/	NULL	germline	phenotype_feature
54	Cancer Gene Census	202406	Catalog of genes of which mutations have been causally implicated in cancer	https://cancer.sanger.ac.uk/census	NULL	somatic	phenotype_feature,study
55	dbVar	202310	NCBI database of human genomic structural variation	https://www.ncbi.nlm.nih.gov/dbvar/	NULL	mixed	structural_variation,phenotype_feature,study
57	PhenCode	NULL	PhenCode is a collaborative project to better understand the relationship between genotype and phenotype in humans	http://phencode.bx.psu.edu/	NULL	germline	variation_synonym
58	OIVD	NULL	Osteogenesis Imperfecta Variant Database	https://oi.gene.le.ac.uk/home.php	NULL	germline	variation_synonym
59	dbPEX	NULL	dbPEX, PEX Gene Database	http://www.dbpex.org/home.php?select_db=PEX1	NULL	germline	variation_synonym
60	KAT6BDB	NULL	K(lysine) acetyltransferase 6B database, BCM	https://grenada.lumc.nl/LOVD2/BCM/home.php?select_db=KAT6B	NULL	germline	variation_synonym
61	Infevers	NULL	The registry of Hereditary Auto-inflammatory Disorders Mutations	http://infevers.umai-montpellier.fr/web/	NULL	germline	variation_synonym
62	PAHdb	NULL	Phenylalanine hydroxylase database	http://www.pahdb.mcgill.ca/	NULL	germline	variation_synonym
63	G2P	20240620	Genotype-to-Phenotype Database	https://www.ebi.ac.uk/gene2phenotype	NULL	germline	phenotype_feature

This table contains details of the studies. The studies information can come from internal studies (DGVa, EGA) or from external studies (UniProt, NHGRI, ...).

This table contains descriptions of group submitting data to public repositories such as ClinVar

This table links a variation to a publication

This table stores information about structural variation.

This table stores the associations between structural variations and their supporting evidences.

This table stores information about structural variation features (i.e. mappings of structural variations to genomic locations).

This table stores sample and strain information for structural variants and their supporting evidences.

This table relates a single allele of a variation_feature to a motif feature (see Regulation documentation). It contains the consequence of the allele.

This table relates a single allele of a variation_feature to a regulatory feature (see Regulation documentation). It contains the consequence of the allele.

This table relates a single allele of a variation_feature to a transcript (see Core documentation). It contains the consequence of the allele e.g. intron_variant, non_synonymous_codon, stop_lost etc, along with the change in amino acid in the resulting protein if applicable.

This table is used in web index creation. It links a variation_id to the names of the genes the variation is within

This table is used in web index creation. It links a variation_id to all possible transcript and protein level change descriptions in HGVS annotation.

This table contains the name of sets and subsets of variations stored in the database. It usually represents the name of the project or subproject where a group of variations has been identified.

Example:

See below the command to display the list of variation set entries, e.g. for human:

SELECT * FROM variation_set;

Show query results

variation_set_id	name	description	short_name_attrib_id
1	All failed variations	Variations that have failed the Ensembl QC checks	179
2	Genotyping chip variants	Variants which have assays on commercial chips held in ensembl	360
3	Illumina_CytoSNP12v1	Variants from the Illumina Cyto SNP-12 v1 whole genome SNP genotyping chip designed for cytogenetic analysis	339
4	Affy GenomeWideSNP_6.0	Variants from the Affymetrix Genome-Wide Human SNP Array 6.0	333
5	Illumina_Human660W-quad	Variants from the Illumina Human660W-quad whole genome genotyping array designed for association studies	334
6	Illumina_1M-duo	Variants from the Illumina Human1M-duo v3 whole genome genotyping array designed for association studies	335
7	Affy GeneChip 500K	Variants from the Affymetrix GeneChip Human Mapping 500K Array Set	332
8	Illumina_Cardio-Metabo_Chip	Variants from the Illumina Cardio-Metabo_Chip genotyping array designed to target variants of interest for metabolic and cardiovascular disease traits	337
9	Illumina_HumanOmni1-Quad	Variants from the Illumina HumanOmni1-Quad whole genome genotyping array designed for association studies	338
10	Illumina_HumanHap650Y	Variants from the Illumina HumanHap650Y v3.0 whole genome genotyping array designed for association studies	340
11	Illumina_HumanOmni2.5	Variants from the Illumina HumanOmni2.5 4v1 whole genome genotyping array designed for association studies	341
12	Illumina_Human610_Quad	Variants from the Illumina Human610_Quad v1_B whole genome genotyping array designed for association studies	342
13	Illumina_HumanHap550	Variants from the Illumina Human550 v3.0 whole genome genotyping array designed for association studies	343
14	Illumina_HumanOmni5	Variants from the Illumina HumanOmni5v1 whole genome genotyping array designed for association studies	354
15	Illumina_ExomeChip	Variants from the Illumina ExomeChip genotyping array designed to target variants within exons	358
16	Illumina_ImmunoChip	Variants from the Illumina ImmunoChip genotyping array designed to target variants of interest for autoimmune and inflammatory diseases	359
17	HumanOmniExpress	Variants from the Illumina HumanOmniExpress 12v1-1_a whole genome genotyping array	373
23	All phenotype/disease-associated variants	Variants that have been associated with a phenotype or a disease	195
24	OMIM phenotype variants	Variants linked to entries in the Online Mendelian Inheritance in Man (OMIM) database	194
25	UniProt variants	Variants with annotations provided by UniProt	196
26	NHGRI-EBI catalog phenotype variants	Variants associated with phenotype data from the NHGRI-EBI GWAS catalog [http://www.ebi.ac.uk/gwas/]	193
27	PhenCode	Variants from the PhenCode Project	355
28	COSMIC phenotype variants	Phenotype annotations of somatic mutations found in human cancers from the COSMIC project	197
29	HGMD-PUBLIC variants	Variants annotated by HGMD	191
30	All ClinVar	Variants with ClinVar annotation	374
31	Clinically associated variants	Variants described by ClinVar as being probable-pathogenic, pathogenic, drug-response or histocompatibility	345
33	dbPEX	Variants from the PEX Gene Database	396
34	KAT6BDB	Variants from the K(lysine) acetyltransferase 6B database, BCM	399
35	HbVar	Variants for the Human Hemoglobin Variants and Thalassemias database	397
36	LMDD	Variants from the Leiden Muscular Dystrophy Database	400
37	OIVD	Variants from the Osteogenesis Imperfecta Variant Database	401
38	PAHdb	Variants from the Phenylalanine hydroxylase database	402
39	Infevers	Variants from the registry of Hereditary Auto-inflammatory Disorders Mutations	398
40	HumanCoreExome-12	Variants from the Illumina HumanCoreExome-12 v1 genotyping chip.	390
41	All LSDB-associated variants	Variants association from one or several Locus Specific DataBase (LSDB)	419
42	1000 Genomes 3 - All	Variants genotyped by the 1000 Genomes project (phase 3)	404
43	1000 Genomes 3 - EUR - common	Variants genotyped in European individuals by the 1000 Genomes project (phase 3) with frequency of at least 1%	415
44	1000 Genomes 3 - SAS - common	Variants genotyped in South Asian individuals by the 1000 Genomes project (phase 3) with frequency of at least 1%	414
45	1000 Genomes 3 - EAS - common	Variants genotyped in East Asian individuals by the 1000 Genomes project (phase 3) with frequency of at least 1%	413
47	1000 Genomes 3 - AMR - common	Variants genotyped in admixed American individuals by the 1000 Genomes project (phase 3) with frequency of at least 1%	412
48	1000 Genomes 3 - AFR - common	Variants genotyped in African individuals by the 1000 Genomes project (phase 3) with frequency of at least 1%	411
49	1000 Genomes 3 - All - common	Variants genotyped by the 1000 Genomes project (phase 3) with frequency of at least 1%	410
50	1000 Genomes 3 - EUR	Variants genotyped in European individuals by the 1000 Genomes project (phase 3)	409
51	1000 Genomes 3 - SAS	Variants genotyped in South Asian individuals by the 1000 Genomes project (phase 3)	408
52	1000 Genomes 3 - EAS	Variants genotyped in East Asian individuals by the 1000 Genomes project (phase 3)	407
53	1000 Genomes 3 - AFR	Variants genotyped in African individuals by the 1000 Genomes project (phase 3)	405
54	1000 Genomes 3 - AMR	Variants genotyped in admixed American individuals by the 1000 Genomes project (phase 3)	406
55	ESP_6500	Variants from the NHLBI Exome Sequencing Project (investigating heart, lung and blood disorders)	344
56	ExAC	Variants identified by the Exome Aggregation Consortium (ExAC) - release 0.3	420
57	gnomAD	Variants reported by the Genome Aggregation Database	586

A table for mapping structural variations to variation_sets.

This table stores hierarchical relationships between variation sets by relating them as variation sets and variation subsets.

A table for mapping variations to variation_sets.

This table stores information about each of a variation's alleles, along with population frequencies.

This table allows for the allele of a variant to have multiple IDs.

This is the schema's generic representation of a variation, defined as a genetic feature that varies between individuals of the same species. The most common type is the single nucleotide variation (SNP) though the schema also accommodates copy number variations (CNVs) and structural variations (SVs).
In Ensembl, a variation is defined by its flanking sequence rather than its mapped location on a chromosome; a variation may in fact have multiple mappings across a genome, although this fails our Quality Control.
This table stores a variation's name (commonly an ID of the form e.g. rs123456, assigned by dbSNP).

This table stores miscellaneous attributes associated with a variation entry.

This table represents mappings of variations to genomic locations. It stores an allele string representing the different possible alleles that are found at that locus e.g. "A/T" for a SNP, as well as a "worst case" consequence of the mutation. It also acts as part of the relationship between variations and transcripts.

This table allows for a variation to have multiple IDs, generally given by multiple sources.