EnsemblEnsembl Home

Ensembl Core - Schema documentation

This document gives a high-level description of the tables that make up the EnsEMBL core schema. Tables are grouped into logical groups, and the purpose of each table is explained. It is intended to allow people to familiarise themselves with the schema when encountering it for the first time, or when they need to use some tables that they've not used before.

This document refers to version 77 of the EnsEMBL core schema.

The core database schema is available in several diagrams (PDF format) here:

External References Schema Diagram Fundamental Tables Schema Diagram Features and Analyses Schema Diagram External References Schema Diagram ID Mapping Schema Diagram

List of the tables:

Assembly Tables

The assembly table states, which parts of seq_regions are exactly equal. It enables to transform coordinates between seq_regions. Typically this contains how chromosomes are made of contigs, clones out of contigs, and chromosomes out of supercontigs. It allows you to artificially chunk chromosome sequence into smaller parts. The data in this table defines the "static golden path", i.e. the best effort draft full genome sequence as determined by the UCSC or NCBI (depending which assembly you are using). Each row represents a component, e.g. a contig, (comp_seq_region_id, FK from seq_region table) at least part of which is present in the golden path. The part of the component that is in the path is delimited by fields cmp_start and cmp_end (start < end), and the absolute position within the golden path chromosome (or other appropriate assembled structure) (asm_seq_region_id) is given by asm_start and asm_end.

See also:

List of species with populated data: show Show species

assembly_exception

Allows multiple sequence regions to point to the same sequence, analogous to a symbolic link in a filesystem pointing to the actual file. This mechanism has been implemented specifically to support haplotypes and PARs, but may be useful for other similar structures in the future.

See also:

List of species with populated data: show Show species

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".

See also:

List of species with populated data: show Show species

Allows the storage of flat file locations used to store large quanitities of data currently unsuitable in a traditional database table.

Contains DNA sequence. This table has a 1:1 relationship with the seq_region table.

See also:

List of species with populated data: show Show species

genome_statistics

Contains genome and assembly related statistics These include but are not limited to: feature counts, sequence lengths

List of species with populated data: show Show species

Describes bands that can be stained on the chromosome.

See also:

List of species with populated data: show Show species

Stores data about the data in the current schema. Taxonomy information, version information and the default value for the type column in the assembly table are stored here. Unlike other tables, data in the meta table is stored as key-value pairs. Also stores (via assembly.mapping keys) the relationships between co-ordinate systems in the assembly table. The species_id field of the meta table is used in multi-species databases and makes it possible to have species-specific meta key-value pairs. The species-specific meta key-value pairs needs to be repeated for each species_id. Entries in the meta table that are not specific to any one species, such as the schema_version key and any other schema-related information must have their species_id field set to NULL. The default species_id, and the only species_id value allowed in single-species databases, is 1.

See also:

List of species with populated data: show Show species

Describes which co-ordinate systems the different feature tables use.

See also:

List of species with populated data: show Show species

Stores information about sequence regions. The primary key is used as a pointer into the dna table so that actual sequence can be obtained, and the coord_system_id allows sequence regions of multiple types to be stored. Clones, contigs and chromosomes are all now stored in the seq_region table. Contigs are stored with the co-ordinate system 'contig'. The relationship between contigs and clones is stored in the assembly table. The relationships between contigs and chromosomes, and between contigs and supercontigs, are stored in the assembly table.

See also:

List of species with populated data: show Show species

seq_region_attrib

Allows "attributes" to be defined for certain seq_regions. Provides a way of storing extra information about particular seq_regions without adding extra columns to the seq_region table. e.g.

See also:

List of species with populated data: show Show species

seq_region_synonym

Allows for storing multiple names for sequence regions.

See also:

List of species with populated data: show Show species

External References

associated_group

Groups together xref associations under a single description. Used when more than one associated xref term must be used to describe a condition

This table associates extra associated annotations with a given ontology xref evidence and source under a specific condition. For GO this allows qualifiers (with/from) or annotation extensions to be added to a given ontology annotation.

See also:

Describes dependent external references which can't be directly mapped to Ensembl entities. They are linked to primary external references instead.

See also:

List of species with populated data: show Show species

Stores data about the external databases in which the objects described in the xref table are stored.

See also:

List of species with populated data: show Show species

external_synonym

Some xref objects can be referred to by more than one name. This table relates names to xref IDs.

See also:

List of species with populated data: show Show species

Describes how well a particular xref object matches the EnsEMBL object.

See also:

List of species with populated data: show Show species

Allows storage of links to the InterPro database. InterPro is a database of protein families, domains and functional sites in which identifiable features found in known proteins can be applied to unknown protein sequences. InterPro - The InterPro website

List of species with populated data: show Show species

Describes links between EnsEMBL objects and objects held in external databases. The EnsEMBL object can be one of several types; the type is held in the ensembl_object_type column. The ID of the particular EnsEMBL gene, translation or whatever is given in the ensembl_id column. The xref_id points to the entry in the xref table that holds data about the external object. Each EnsEMBL object can be associated with zero or more xrefs. An xref object can be associated with one or more EnsEMBL objects.

See also:

List of species with populated data: show Show species

This table associates Evidence Tags to the relationship between EnsEMBL objects and ontology accessions (primarily GO accessions). The relationship to GO that is stored in the database is actually derived through the relationship of EnsEMBL peptides to SwissProt peptides, i.e. the relationship is derived like this: ENSP -> SWISSPROT -> GO And the evidence tag describes the relationship between the SwissProt Peptide and the GO entry. In reality, however, we store this in the database like this: ENSP -> SWISSPROT ENSP -> GO and the evidence tag hangs off of the relationship between the ENSP and the GO identifier. Some ENSPs are associated with multiple closely related Swissprot entries which may both be associated with the same GO identifier but with different evidence tags. For this reason a single Ensembl - external db object relationship in the object_xref table can be associated with multiple evidence tags in the ontology_xref table.

See also:

List of species with populated data: show Show species

Describes why a particular external entity was not mapped to an ensembl one.

See also:

List of species with populated data: show Show species

Describes the reason why a mapping failed.

List of species with populated data: show Show species

Holds data about objects which are external to EnsEMBL, but need to be associated with EnsEMBL objects. Information about the database that the external object is stored in is held in the external_db table entry referred to by the external_db column.

See also:

List of species with populated data: show Show species

Features

Describes features representing a density, or precentage coverage etc. in a given region.

See also:

List of species with populated data: show Show species

Describes type representing a density, or percentage coverage etc. in a given region.

See also:

List of species with populated data: show Show species

Represents a ditag object in the EnsEMBL database. Corresponds to original tag containing the full sequence. This can be a single piece of sequence like CAGE tags or a ditag with concatenated sequence from 5' and 3' end like GIS or GSC tags. This data is available as a DAS track in ContigView on the EnsEMBL web site.

See also:

List of species with populated data: show Show species

Describes where ditags hit on the genome. Represents a mapped ditag object in the EnsEMBL database. These are the original tags separated into start ("L") and end ("R") parts if applicable, successfully aligned to the genome. Two DitagFeatures usually relate to one parent Ditag. Alternatively there are CAGE tags e.g. which only have a 5\'tag ("F").

See also:

List of species with populated data: show Show species

intron_supporting_evidence

Provides the evidence which we have used to declare an intronic region

See also:

List of species with populated data: show Show species

Stores the names of different genetic or radiation hybrid maps, for which there is marker map information.

See also:

List of species with populated data: show Show species

Stores data about the marker itself. A marker in Ensembl consists of a pair of primer sequences, an expected product size and a set of associated identifiers known as synonyms.

See also:

List of species with populated data: show Show species

Used to describe positions of markers on the assembly. Markers are placed on the genome electronically using an analysis program.

See also:

List of species with populated data: show Show species

marker_map_location

Stores map locations (genetic, radiation hybrid and in situ hybridization) for markers obtained from experimental evidence.

See also:

List of species with populated data: show Show species

Stores alternative names for markers, as well as their sources.

See also:

List of species with populated data: show Show species

Stores arbitrary attributes about the features in the misc_feature table.

See also:

List of species with populated data: show Show species

Allows for storage of arbitrary features.

See also:

List of species with populated data: show Show species

misc_feature_misc_set

This table classifies features into distinct sets.

See also:

List of species with populated data: show Show species

Defines "sets" that the features held in the misc_feature table can be grouped into.

See also:

List of species with populated data: show Show species

Stores exons that are predicted by ab initio gene finder programs. Unlike EnsEMBL exons they are not supported by any evidence.

See also:

List of species with populated data: show Show species

prediction_transcript

Stores transcripts that are predicted by ab initio gene finder programs (e.g. genscan, SNAP). Unlike EnsEMBL transcripts they are not supported by any evidence.

See also:

List of species with populated data: show Show species

repeat_consensus

Stores consensus sequences obtained from analysing repeat features.

List of species with populated data: show Show species

Describes sequence repeat regions.

See also:

List of species with populated data: show Show species

Describes general genomic features that don't fit into any of the more specific feature tables.

See also:

List of species with populated data: show Show species

transcript_intron_supporting_evidence

Links intronic evidence to a pair of exons used within a transcript and to resolve the m:m relationship between introns and transcripts

See also:

List of species with populated data: show Show species

Fundamental Tables

Stores information about genes on haplotypes that may be orthologous.

See also:

List of species with populated data: show Show species

alt_allele_attrib

Holds all the different attributes assigned to individual alleles.

List of species with populated data: show Show species

alt_allele_group

A minimal table used for tracking unique alt_allele_group_id's. MySQL does not allow multiple autoincrement fields. Further information about a group could be added here at a later date.

List of species with populated data: show Show species

Usually describes a program and some database that together are used to create a feature on a piece of sequence. Each feature is marked with an analysis_id. The most important column is logic_name, which is used by the webteam to render a feature correctly on contigview (or even retrieve the right feature). Logic_name is also used in the pipeline to identify the analysis which has to run in a given status of the pipeline. The module column tells the pipeline which Perl module does the whole analysis, typically a RunnableDB module.

See also:

List of species with populated data: show Show species

analysis_description

Allows the storage of a textual description of the analysis, as well as a "display label", primarily for the EnsEMBL web site.

See also:

List of species with populated data: show Show species

Provides codes, names and desctriptions of attribute types.

See also:

List of species with populated data: show Show species

dna_align_feature

Stores DNA sequence alignments generated from Blast (or Blast-like) comparisons.

See also:

List of species with populated data: show Show species

Stores data about exons. Associated with transcripts via exon_transcript. Allows access to contigs seq_regions. Note seq_region_start is always less that seq_region_end, i.e. when the exon is on the other strand the seq_region_start is specifying the 3prime end of the exon.

See also:

List of species with populated data: show Show species

Relationship table linking exons with transcripts. The rank column indicates the 5' to 3' position of the exon within the transcript, i.e. a rank of 1 means the exon is the 5' most within this transcript.

See also:

List of species with populated data: show Show species

Allows transcripts to be related to genes.

See also:

List of species with populated data: show Show species

Enables storage of attributes that relate to genes.

See also:

List of species with populated data: show Show species

protein_align_feature

Stores translation alignments generated from Blast (or Blast-like) comparisons.

See also:

List of species with populated data: show Show species

Describes features on the translations (as opposed to the DNA sequence itself), i.e. parts of the peptide. In peptide co-ordinates rather than contig co-ordinates.

See also:

List of species with populated data: show Show species

supporting_feature

Describes the exon prediction process by linking exons to DNA or protein alignment features. As in several other tables, the feature_id column is a foreign key; the feature_type column specifies which table feature_id refers to.

See also:

List of species with populated data: show Show species

Stores information about transcripts. Has seq_region_start, seq_region_end and seq_region_strand for faster retrieval and to allow storage independently of genes and exons. Note that a transcript is usually associated with a translation, but may not be, e.g. in the case of pseudogenes and RNA genes (those that code for RNA molecules).

See also:

List of species with populated data: show Show species

transcript_attrib

Enables storage of attributes that relate to transcripts.

See also:

List of species with populated data: show Show species

transcript_supporting_feature

Describes the exon prediction process by linking transcripts to DNA or protein alignment features. As in several other tables, the feature_id column is a foreign key; the feature_type column specifies which table feature_id refers to.

See also:

List of species with populated data: show Show species

Describes which parts of which exons are used in translation. The seq_start and seq_end columns are 1-based offsets into the relative coordinate system of start_exon_id and end_exon_id. i.e, if the translation starts at the first base of the exon, seq_start would be 1. Transcripts are related to translations by the transcript_id key in this table.

See also:

List of species with populated data: show Show species

translation_attrib

Enables storage of attributes that relate to translations.

See also:

List of species with populated data: show Show species

ID Mapping

Contains a snapshot of the stable IDs associated with genes deleted or changed between releases. Includes gene, transcript and translation stable IDs.

See also:

List of species with populated data: show Show species

Stores details of ID mapping sessions - a mapping session represents the session when stable IDs where mapped from one database to another. Details of the "old" and "new" databases are stored.

See also:

List of species with populated data: show Show species

Table structure for seq_region mapping between releases.

List of species with populated data: show Show species

Contains the peptides for deleted or changed translations.

List of species with populated data: show Show species

seq_region_mapping

Describes how the core seq_region_id have changed from release to release.

See also:

List of species with populated data: show Show species

Represents what happened to all gene, transcript and translation stable IDs during a mapping session. This includes which IDs where deleted, created and related to each other. Each event is represented by one or more rows in the table.

See also:

List of species with populated data: show Show species

Misc

allows one or more polycistronic transcripts to be grouped together

See also:

operon_transcript

represents polycistronic transcripts which belong to operons and encode more than one gene

See also:

operon_transcript_gene

allows association of genes with polycistronic transcripts

See also: