Archive Ensembl HomeArchive Ensembl Home

Compara Database Schema

This document refers to the schema of EnsEMBL Compara version 66.

Introduction

EnsEMBL Compara DB is divided into two parts:

Genomic Alignments

This part is dedicated to DNA-DNA alignments. It includes alignments made with different alignment tools, and a list of syntenic regions.

Homologues and Families (or Protein Clusters)

This part is dedicated to gene-gene alignments and it includes protein families, protein trees and homologues defined from the protein trees. It also includes ncRNA families, alignments and trees.

The protein families are clusters of proteins. They are defined using the full set of Ensembl proteins plus all the metazoan proteins from SwissProt and SPTrEMBL.

The protein trees are built using the longest transcript of each protein coding Ensembl gene. Homologies are derived from them.

The ncRNA trees are based on RFAM families and alignments based on secondary structure and genomic alignments.

List of Tables

General Tables

meta

Contains configuration variables.

meta table description
Field Type Null Key Default Extra Description
meta_id int(11) NO PRI NULL auto_increment internal unique ID
species_id int(10) unsigned NO PRI 1 Only used in core databases
meta_key varchar(40) NO MUL
meta_value text NO MUL

Meta table stores miscelaneous values:

mysql> 
SELECT * FROM meta WHERE meta_key = "schema_version";
+---------+------------+----------------+------------+
| meta_id | species_id | meta_key       | meta_value |
+---------+------------+----------------+------------+
|       1 |       NULL | schema_version | 67         |
+---------+------------+----------------+------------+

This entry defines which API version must be used to access this database.

ncbi_taxa_node

Contains a description of the taxonomic relathionships between all the taxa used in this database. It is usually read together with the ncbi_taxa_name table

ncbi_taxa_node table description
Field Type Null Key Default Extra Description
taxon_id int(10) unsigned NO PRI the NCBI taxonomy ID
parent_id int(10) unsigned NO MUL the parent taxonomy ID for this node (refers to ncbi_taxa_node.taxon_id)
rank char(32) NO MUL '' e.g. kingdom, family, genus, etc.
genbank_hidden_flag tinyint(1) NO 0 boolean value which defines whether this rank is used or not in the abbreviated lineage
left_index int(10) NO sub-set left index. All sub-nodes have left_index and right_index values larger than this left_index
right_index int(10) NO sub-set right index. All sub-nodes have left_index and right_index values smaller than this right_index
root_id int(10) 1 the root taxonomy ID for this node (refers to ncbi_taxa_node.taxon_id)

(c.f. ncbi_taxa_name for examples)

ncbi_taxa_name

Contains descriptions the taxonimc nodes defined in the ncbi_taxa_node table.

ncbi_taxa_name table description
Field Type Null Key Default Extra Description
taxon_id int(10) unsigned NO MUL external reference to ncbi_taxa_node.taxon_id
name varchar(255) YES MUL NULL information assigned to this taxon_id
name_class varchar(50) YES MUL NULL type of information. e.g. common name, genbank_synonym, scientif name, etc.

Here is an example on how to get the taxonomic ID for a species:

mysql> SELECT * FROM ncbi_taxa_name WHERE name_class = "scientific name" AND name = "Homo sapiens";
+----------+--------------+-----------------+
| taxon_id | name         | name_class      |
+----------+--------------+-----------------+
|     9606 | Homo sapiens | scientific name |
+----------+--------------+-----------------+

Here is another example on how to get the lineage for Homo sapiens:

mysql> SELECT * FROM ncbi_taxa_node WHERE left_index <= 339687 AND right_index >= 339690 ORDER BY left_index;
+----------+-----------+--------------+---------------------+------------+-------------+---------+
| taxon_id | parent_id | rank         | genbank_hidden_flag | left_index | right_index | root_id |
+----------+-----------+--------------+---------------------+------------+-------------+---------+
|        1 |         0 | no rank      |                   0 |          1 |     1767202 |       1 |
|   131567 |         1 | no rank      |                   1 |        180 |     1586567 |       1 |
|     2759 |    131567 | superkingdom |                   0 |      12669 |     1120686 |       1 |
|    33154 |      2759 | no rank      |                   1 |     273806 |     1064781 |       1 |
|    33208 |     33154 | kingdom      |                   0 |     274043 |      908694 |       1 |
|     6072 |     33208 | no rank      |                   1 |     274274 |      905223 |       1 |
|    33213 |      6072 | no rank      |                   1 |     274453 |      895354 |       1 |
|    33316 |     33213 | no rank      |                   1 |     285534 |      882177 |       1 |
|    33511 |     33316 | no rank      |                   1 |     285535 |      412718 |       1 |
|     7711 |     33511 | phylum       |                   0 |     290580 |      412717 |       1 |
|    89593 |      7711 | subphylum    |                   0 |     290589 |      411882 |       1 |
|     7742 |     89593 | no rank      |                   0 |     290668 |      411881 |       1 |
|     7776 |      7742 | superclass   |                   1 |     290823 |      411880 |       1 |
|   117570 |      7776 | no rank      |                   1 |     290824 |      409395 |       1 |
|   117571 |    117570 | no rank      |                   0 |     290825 |      409394 |       1 |
|     8287 |    117571 | no rank      |                   1 |     290826 |      362175 |       1 |
|    32523 |      8287 | no rank      |                   1 |     290891 |      362174 |       1 |
|    32524 |     32523 | no rank      |                   1 |     290892 |      349501 |       1 |
|    40674 |     32524 | class        |                   0 |     332151 |      349500 |       1 |
|    32525 |     40674 | no rank      |                   1 |     332178 |      349499 |       1 |
|     9347 |     32525 | no rank      |                   0 |     332179 |      348482 |       1 |
|   314146 |      9347 | superorder   |                   0 |     332302 |      340667 |       1 |
|     9443 |    314146 | order        |                   0 |     339155 |      340666 |       1 |
|   376913 |      9443 | suborder     |                   0 |     339156 |      340247 |       1 |
|   314293 |    376913 | infraorder   |                   1 |     339183 |      340246 |       1 |
|     9526 |    314293 | parvorder    |                   0 |     339184 |      339797 |       1 |
|   314295 |      9526 | superfamily  |                   1 |     339653 |      339796 |       1 |
|     9604 |    314295 | family       |                   0 |     339654 |      339711 |       1 |
|   207598 |      9604 | subfamily    |                   1 |     339669 |      339710 |       1 |
|     9605 |    207598 | genus        |                   0 |     339686 |      339693 |       1 |
|     9606 |      9605 | species      |                   1 |     339687 |      339690 |       1 |
+----------+-----------+--------------+---------------------+------------+-------------+---------+

genome_db

Contains information about the version of the genome assemblies used in this database.

genome_db table description
Field Type Null Key Default Extra Description
genome_db_id int(10) unsigned NO PRI NULL auto_increment internal unique ID
taxon_id int(10) unsigned NO MUL 0 external reference to ncbi_taxa_node.taxon_id
name varchar(40) NO MUL name of the species
assembly varchar(100) NO assembly version of the genome
assembly_default tinyint(1) YES 1 boolean value describing if this assembly is the default one or not, so that we can handle more than one assempbly version for a given species.
genebuild varchar(100) NO version of the genebuild
locator varchar(400) YES NULL used for production purposes or for user configuration in in-house installation.

Eg the rows:

mysql> SELECT * FROM genome_db WHERE name IN ("Homo_sapiens", "Gallus_gallus");
+--------------+----------+---------------+----------+------------------+-----------------+---------+
| genome_db_id | taxon_id | name          | assembly | assembly_default | genebuild       | locator |
+--------------+----------+---------------+----------+------------------+-----------------+---------+
|           42 |     9031 | gallus_gallus | WASHUC2  |                1 | 2006-08-Ensembl |         |
|           90 |     9606 | homo_sapiens  | GRCh37   |                1 | 2010-07-Ensembl |         |
+--------------+----------+---------------+----------+------------------+-----------------+---------+

correspond to the Human and Chicken genomes

species_set

Contains groups or sets of species which are used in the method_link_species_set table.

species_set table description
Field Type Null Key Default Extra Description
species_set_id int(10) unsigned NO PRI auto_increment internal (non-unique) ID
genome_db_id int(10) unsigned YES PRI 0 external reference to genome_db.genome_db_id

E.g. the rows

mysql> SELECT * FROM species_set WHERE species_set_id = 32279;
+----------------+--------------+
| species_set_id | genome_db_id |
+----------------+--------------+
|          32279 |            3 |
|          32279 |           90 |
+----------------+--------------+

correspond to the species_set for the Human and Rat genomes:

mysql> SELECT species_set_id, name, assembly FROM species_set LEFT JOIN genome_db USING (genome_db_id) WHERE species_set_id = 32279;
+----------------+-------------------+----------+
| species_set_id | name              | assembly |
+----------------+-------------------+----------+
|          32279 | rattus_norvegicus | RGSC3.4  |
|          32279 | homo_sapiens      | GRCh37   |
+----------------+-------------------+----------+

species_set_tag

Contains descriptive tags for the species_set_ids in the species_set table.

species_set_tag table description
Field Type Null Key Default Extra Description
species_set_id int(10) unsigned NO MUL 0 internal (non-unique) ID
tag varchar(50) NO MUL external reference to species_set.species_set_id
value mediumtext YES more informative description for the species_set_id

E.g. the rows

mysql> SELECT * FROM species_set_tag WHERE value LIKE "%fish%";
+----------------+------+-------+
| species_set_id | tag  | value |
+----------------+------+-------+
|          34693 | name | fish  |
|          33241 | name | fish  |
+----------------+------+-------+

Contains the list of alignment methods used to find links (homologies) between entities in compara.

method_link table description
Field Type Null Key Default Extra Description
method_link_id int(10) unsigned NO PRI auto_increment internal unique ID
type varchar(50) NO MUL the common name of the linking method between species.
class varchar(50) NO Description of type of data associated with the \"type\" field and the main table to find these data

Current values are:

mysql> SELECT * FROM method_link;
+----------------+---------------------------+----------------------------------------+
| method_link_id | type                      | class                                  |
+----------------+---------------------------+----------------------------------------+
|              1 | BLASTZ_NET                | GenomicAlignBlock.pairwise_alignment   |
|              6 | TRANSLATED_BLAT           | GenomicAlignBlock.pairwise_alignment   |
|              7 | TRANSLATED_BLAT_NET       | GenomicAlignBlock.pairwise_alignment   |
|              9 | MLAGAN                    | GenomicAlignBlock.multiple_alignment   |
|             10 | PECAN                     | GenomicAlignBlock.multiple_alignment   |
|             11 | GERP_CONSTRAINED_ELEMENT  | ConstrainedElement.constrained_element |
|             12 | ORTHEUS                   | GenomicAlignTree.tree_alignment        |
|             13 | EPO                       | GenomicAlignTree.ancestral_alignment   |
|             14 | EPO_LOW_COVERAGE          | GenomicAlignTree.tree_alignment        |
|             15 | ENREDO                    | SyntenyRegion.enredo                   |
|             16 | LASTZ_NET                 | GenomicAlignBlock.pairwise_alignment   |
|             17 | pEPO                      | GenomicAlignTree.ancestral_alignment   |
|             18 | pGERP_CONSTRAINED_ELEMENT | ConstrainedElement.constrained_element |
|             19 | LASTZ_PATCH               | GenomicAlignBlock.pairwise_alignment   |
|             20 | LASTZ_NET_HARD_MASKED     | GenomicAlignBlock.pairwise_alignment   |
|            101 | SYNTENY                   | SyntenyRegion.synteny                  |
|            102 | SYNTENY_HARD_MASKED       | SyntenyRegion.synteny                  |
|            201 | ENSEMBL_ORTHOLOGUES       | Homology.homology                      |
|            202 | ENSEMBL_PARALOGUES        | Homology.homology                      |
|            204 | ENSEMBL_HOMOLOGUES        | Homology.homology                      |
|            205 | ENSEMBL_PROJECTIONS       | Homology.homology                      |
|            301 | FAMILY                    | Family.family                          |
|            401 | PROTEIN_TREES             | ProteinTree.protein_tree_node          |
|            402 | NC_TREES                  | NCTree.nc_tree_node                    |
|            501 | GERP_CONSERVATION_SCORE   | ConservationScore.conservation_score   |
|            502 | pGERP_CONSERVATION_SCORE  | ConservationScore.conservation_score   |
+----------------+---------------------------+----------------------------------------+

NOTE: We use method_link_ids between 1 and 100 for DNA-DNA alignments, between 101 and 200 for genomic syntenies, between 201 and 300 for protein homologies, between 301 and 400 for protein families and between 401 and 500 for protein and ncRNA trees. Each category corresponds to data stored in different tables

This table contains information about the comparisons stored in the database. A given method_link_species_set_id exist for each comparison made and relates a method_link.method_link_id with a set of species (species_set.species_set_id.

method_link_species table description
Field Type Null Key Default Extra Description
method_link_species_set_id int(10) unsigned NO PRI auto_increment internal unique ID.
method_link_id int(10) unsigned YES MUL NULL external reference to method_link.method_link_id
species_set_id int(10) unsigned NO 0 external reference to species_set.species_set_id
name varchar(255) NO NULL human-readable description for this method_link_species_set
source varchar(255) NO ensembl source of the data. Currently either ensembl or ucsc if data were imported from UCSC
url varchar(255) NO A URL where you can find the orignal data if they were imported.

E.g. the rows

mysql> SELECT * FROM method_link_species_set WHERE method_link_id = 13;
+----------------------------+----------------+----------------+--------------------------+---------+-----+
| method_link_species_set_id | method_link_id | species_set_id | name                     | source  | url |
+----------------------------+----------------+----------------+--------------------------+---------+-----+
|                        528 |             13 |          33241 | 5 teleost fish EPO       | ensembl |     |
|                        505 |             13 |          33554 | 3 neognath birds EPO     | ensembl |     |
|                        548 |             13 |          34115 | 6 primates EPO           | ensembl |     |
|                        578 |             13 |          34466 | 12 eutherian mammals EPO | ensembl |     |
+----------------------------+----------------+----------------+--------------------------+---------+-----+

correspond to all the EPO alignments in this database.

Contains serveral tag/value data associated with method_link_species_set entries

method_link_species_set_tag
Field Type Null Key Default Extra Description
method_link_species_set_id int(10) unsigned NO MUL external reference to method_link_species_set.method_link_species_set_id
tag varchar(50) NO MUL
value mediumtext NO

Genomic Alignment Tables

dnafrag

This table defines the genomic sequences used in the comparative genomics analyisis. It is used by the genomic_align_block table to define aligned sequences. It is also used by the dnafrag_region table to define syntenic regions.

dnafrag table description
Field Type Null Key Default Extra Description
dnafrag_id bigint(20) unsigned NO PRI auto_increment internal unique ID
length int(11) NO 0
name varchar(40) NO name of the DNA sequence (e.g., the name of the chromosome)
genome_db_id int(10) unsigned NO MUL 0 external reference to genome_db.genome_db_id
coord_system_name varchar(40) YES NULL refers to the coord system in which this dnafrag has been defined
is_reference tinyint(1) NO 1 boolean, whether dnafrag is reference (1) or non-reference (0) eg haplotype

E.g. the row

mysql> SELECT dnafrag.* FROM dnafrag LEFT JOIN genome_db USING (genome_db_id) WHERE dnafrag.name = "14" AND genome_db.name = "homo_sapiens";
+------------+-----------+------+--------------+-------------------+--------------+
| dnafrag_id | length    | name | genome_db_id | coord_system_name | is_reference |
+------------+-----------+------+--------------+-------------------+--------------+
|   12179421 | 107349540 | 14   |           90 | chromosome        |            1 |
+------------+-----------+------+--------------+-------------------+--------------+

refer to the chromosome 14 of the Human genome (genome_db.genome_db_id = 90 refers to Human genome in this example) which is 107349540 nucleotides long.

genomic_align_block

This table is the key table for the genomic alignments. The software used to align the genomic blocks is refered as an external key to the method_link table. Nevertheless, actual aligned sequences are defined in the genomic_align table.

Tree alignments (EPO alignments) are best accessed through the genomic_align_tree table although the alignments are also indexed in this table. This allows the user to also access the tree alignments as normal multiple alignments.

genomic_align_block table description
Field Type Null Key Default Extra Description
genomic_align_block_id bigint(20) unsigned NO PRI auto_increment internal unique ID
method_link_species_set_id int(10) unsigned NO MUL 0 external reference to method_link_species_set.method_link_species_set_id
score double YES NULL score returned by the homology search program
perc_id tinyint(3) unsigned YES NULL Used for pairwise comparison. Defines the percentage of identity between both sequences
length int(10) YES NULL total length of the alignment
group_id bigint(20) unsigned YES NULL used to group alignments

E.g. the row

mysql> SELECT * FROM genomic_align_block WHERE genomic_align_block_id = 5480000000010;
+------------------------+----------------------------+-------+---------+--------+----------+
| genomic_align_block_id | method_link_species_set_id | score | perc_id | length | group_id |
+------------------------+----------------------------+-------+---------+--------+----------+
|          5480000000010 |                        548 |  NULL | NULL    |  21571 |     NULL |
+------------------------+----------------------------+-------+---------+--------+----------+

will refer to a primates EPO alignment (method_link_species_set.method_link_species_set_id = 548) with a length of 21571 nucleotides. The actual sequences corresponding to this aligment are defined in the genomic_align table.

genomic_align

This table contains the coordinates and all the information needed to rebuild genomic alignments. Every entry corresponds to one of the aligned sequences. It also contains an external key to the method_link_species_set which refers to the software and set of species used for getting the corresponding alignment. The aligned sequence is defined by an external reference to the dnafrag table, the starting and ending position within this dnafrag, the strand and a cigar_line.

The original aligned sequence is not stored but it can be retrieved using the cigar_line field and the original sequence. The cigar line defines the sequence of matches/mismatches and deletions (or gaps). For example, this cigar line 2MD3M2D2M will mean that the alignment contains 2 matches/mismatches, 1 deletion (number 1 is omitted in order to save some space), 3 matches/mismatches, 2 deletions and 2 matches/mismatches. If the original sequence is:

  • Original sequence: AACGCTT

The aligned sequence will be:

cigar line: 2MD3M2D2M
M M D M M M D D M M
A A - C G C - - T T
genomic_align_block table description
Field Type Null Key Default Extra Description
genomic_align_id bigint(20) unsigned NO PRI auto_increment unique internal id
genomic_align_block_id bigint(20) unsigned NO MUL external reference to genomic_align_block.genomic_align_block_id
method_link_species_set_id int(10) unsigned NO 0 external reference to method_link_species_set.method_link_species_set_id. This information is redundant because it also appears in the genomic_align_block table but it is used to speed up the queries
dnafrag_id bigint(20) unsigned NO MUL 0 external reference to dnafrag.dnafrag_id
dnafrag_start int(10) NO 0 starting position within the dnafrag defined by dnafrag_id
dnafrag_end int(10) NO 0 ending position within the dnafrag defined by dnafrag_id
dnafrag_strand tinyint(4) NO 0 strand in the dnafrag defined by dnafrag_id
cigar_line mediumtext YES NULL internal description of the aligned sequence
level_id tinyint(2) unsigned NO 0 level of orhologous layer. 1 corresponds to the first layer of orthologous sequences found, 2 and over are addiotional layers. Use for building the syntenies (based on level_id = 1 only)

E.g. the rows

mysql> SELECT * FROM genomic_align WHERE genomic_align_block_id = 5480000000010;
+------------------+------------------------+----------------------------+------------+---------------+-------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
| genomic_align_id | genomic_align_block_id | method_link_species_set_id | dnafrag_id | dnafrag_start | dnafrag_end | dnafrag_strand | cigar_line | level_id |
+------------------+------------------------+----------------------------+------------+---------------+-------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+
|    5480000000019 |          5480000000010 | 548 |    1045560 | 49963980  |    49974667 |              1 | 401MD210MD16M6706D64M2D125MD20MD96M3D24M987D167M9D190M3D16M4D153M11D72M14D88MD36MD22M2D59MD186M5D216M15D4M5D83M3D55MD73M137D109M2D89MD61MD158MD123MD323MD20MD25M3D22MD66M1044D45M387D5M6D251MD107MD9M2D238M3D33M7D38M16D63M3D144MD218MD50M11D60M9D191MD158M4D41M13D59M2D49M3D39MD32MD58M2D17M5D55M3D30M5D185M7D7M9D10M633D99M32D2M9D69M2DMD9M7D44M6D98MD11MD194MD28MD137MD384M2D11M14D154M4D3MD394M582D3MD64M4D95MD46M6D3M2D17M15D230MD362MD107M3D344M5DMD417M5D27M2D58M20D286MD84M4D44M4D186M14D16M10D162MD157M4D88MD231MD429M4D29M |            1 |
|    5480000000020 |          5480000000010 | 548 |   12179431 |      70205099 |    70215984 |              1 | 355M5D85M4D180M6706D64M2D270M987D64M3D318M4D89M18D47M5D31MD22M6D17M14D432M10D37MD43MD112M5D134M3D4MD73M137D2MD66M40D29MD406MD298M3D43MD25M2D23MD66M1044D45M387D5M6D79M99D73MD107MD285M7D38M16D40M5D18M3D61M14D288MD50M11D60M9D401M10D56M2D91MD110M5D50M3D2M3D148M10D62MD13M9D10M262D11M3D2M6D315M10D131M6D41MD56M2D62M54D50MD58M7D34M2D6M6D5M3D241MD10MD70MD313M2D11M14D154M8D394M582D214M6D3M18DM15D230MD817M5DMD212MD204M5D27M2D363MD79M4D2M4D81M3D83M4D56M2D37M4D166MD297M2DMD180MD7M4D241M2D24M15D169M |        1 |
|    5480000000021 |          5480000000010 | 548 |   13113625 |      45200028 |    45220283 |             -1 | 355M5D27M4D51M7D11M10D36MD18M4D71MD13M5D6803M2D70M2D51MD1174M3D185MD16M17D192M18D46M17D20MD40M2D47M3D92M2D120MD184MD66M4D45M2D36M2D136M4D47M3D19M8D21MD168MD36MD29M42D27MDM8D83MD281MD40MD14M4D413MD1582M127D65MD117M4D3MD208M5D134MD6M5D3M3D19MD57M14D4M2D48MD13MDM2D155M6D288M2D39M8D113M2D21M2D31MD69MD4M16D16M3D90M4D19MD22MD21M3DMD58M2D56MD15M3D36M2D55M2D44M2D12M10D62MDM629DM10D161M12D5MD64M13D53MD152M7D32M9DM6D5M3D30M2D8M3D180MD17MD22M2D47M2D8MD96M4D109MD164MD118M75D179M4D34MD17M4D739M4D57M8D27M3D164M4D122MD243MD335MD437MD119M7D232MD86M4D198MD2M2D78M4D84M8D84M4D56M6D28M6D22M4D6M3D114MD16MD297M4D105M2D129MD222M5D179M |        1 |
|    5480000000022 |          5480000000010 | 548 |   13203833 |      52175893 |    52186820 |              1 | 355M5D85M4D180M6706D64M2D185M2D83M987D64M3D119MD198M4D89M18D47M5D5M3D23MD45M14D255MD176M10D81MD112M5D83M3D48M3D4MD73M137D2MD66M42D27MD372M4D30MD298M3D43MD25M2D23MD66M1044D45M387D5M6D79M99D73MD107MD285M7D38M16D40M5D18M3D62M4DM8D288MD50M11D60M9D467M2D91MD91M2D17M5D50M3D2M3D148M10D62MD352M9D10M108D18M134D17M6D118M6D27MD13MD56M2D62M54D50MD58M7D42M6D5M3D241MD10MD70MD313M2D11M14D65M3D7M4D477M582D212M8D3M18DM15D230MD817M5DMD417M5D27M2D363M2D78M4D2M4D81M3D180M6D464M2D182MD7M4D267M15D169M |        1 |
|    5480000000023 |          5480000000010 | 548 |   13211513 |      69435755 |    69446651 |              1 | 143M4D208M5D85M4D14M7D159M6706D64M2D270M987D64M3D318M4D89M18D83MD45M14D432M10D37MD43MD112M5D84MD49M3D4MD73M137D2MD66M40D29MD406MD298M3D43MD25M2D23MD66M1044D45M387D5M6D79M99D73MD107MD276MD8M7D38M16D40M5D18M3D67M8D69MD63M4D151MD50M11D60M9D467M2D91MD32MD58M2D17M5D50M3D2M3D148M10D76M9D10M262D11M3D2M6D280MD34M10D131M6D24M6D11MD56M2D62M54D50MD18M2D38M7D33M3D6M6D5M3D241MD10MD70MD313M2D11M14D556M582D210M12DM18DM15D230MD817M5DMD417M5D27M2D363M2D78M4D2M4D81M3D352MD297M2DMD180MD7M4D267M15D169M |        1 |
+------------------+------------------------+----------------------------+------------+---------------+-------------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+----------+

correspond to the three sequences included in the alignment described above (see genomic_align_block table description). The first sequence includes the nucleotides from 49963980 to 49974667 in the forward strand of the chromosome 17 of the macaca_mulatta genome (dnafrag.dnafrag_id = 1045560). And so on

Here is a better way to get this by joining the dnafrag and genome_db tables:

mysql> SELECT genome_db.name, dnafrag.name, dnafrag_start, dnafrag_end, dnafrag_strand str, cigar_line FROM genomic_align LEFT JOIN dnafrag USING (dnafrag_id) LEFT JOIN genome_db USING (genome_db_id) WHERE genomic_align_block_id = 5480000000010;
+--------------------+------+---------------+-------------+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| name               | name | dnafrag_start | dnafrag_end | str |
  cigar_line
  |
+--------------------+------+---------------+-------------+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| macaca_mulatta     | 17   |      49963980 |    49974667 |   1 |
  401MD210MD16M6706D64M2D125MD20MD96M3D24M987D167M9D190M3D16M4D153M11D72M14D88MD36MD22M2D59MD186M5D216M15D4M5D83M3D55MD73M137D109M2D89MD61MD158MD123MD323MD20MD25M3D22MD66M1044D45M387D5M6D251MD107MD9M2D238M3D33M7D38M16D63M3D144MD218MD50M11D60M9D191MD158M4D41M13D59M2D49M3D39MD32MD58M2D17M5D55M3D30M5D185M7D7M9D10M633D99M32D2M9D69M2DMD9M7D44M6D98MD11MD194MD28MD137MD384M2D11M14D154M4D3MD394M582D3MD64M4D95MD46M6D3M2D17M15D230MD362MD107M3D344M5DMD417M5D27M2D58M20D286MD84M4D44M4D186M14D16M10D162MD157M4D88MD231MD429M4D29M
  |
| homo_sapiens       | 13   |      70205099 |    70215984 |   1 |
  355M5D85M4D180M6706D64M2D270M987D64M3D318M4D89M18D47M5D31MD22M6D17M14D432M10D37MD43MD112M5D134M3D4MD73M137D2MD66M40D29MD406MD298M3D43MD25M2D23MD66M1044D45M387D5M6D79M99D73MD107MD285M7D38M16D40M5D18M3D61M14D288MD50M11D60M9D401M10D56M2D91MD110M5D50M3D2M3D148M10D62MD13M9D10M262D11M3D2M6D315M10D131M6D41MD56M2D62M54D50MD58M7D34M2D6M6D5M3D241MD10MD70MD313M2D11M14D154M8D394M582D214M6D3M18DM15D230MD817M5DMD212MD204M5D27M2D363MD79M4D2M4D81M3D83M4D56M2D37M4D166MD297M2DMD180MD7M4D241M2D24M15D169M
  |
| callithrix_jacchus | 1    |      45200028 |    45220283 |  -1 |
  355M5D27M4D51M7D11M10D36MD18M4D71MD13M5D6803M2D70M2D51MD1174M3D185MD16M17D192M18D46M17D20MD40M2D47M3D92M2D120MD184MD66M4D45M2D36M2D136M4D47M3D19M8D21MD168MD36MD29M42D27MDM8D83MD281MD40MD14M4D413MD1582M127D65MD117M4D3MD208M5D134MD6M5D3M3D19MD57M14D4M2D48MD13MDM2D155M6D288M2D39M8D113M2D21M2D31MD69MD4M16D16M3D90M4D19MD22MD21M3DMD58M2D56MD15M3D36M2D55M2D44M2D12M10D62MDM629DM10D161M12D5MD64M13D53MD152M7D32M9DM6D5M3D30M2D8M3D180MD17MD22M2D47M2D8MD96M4D109MD164MD118M75D179M4D34MD17M4D739M4D57M8D27M3D164M4D122MD243MD335MD437MD119M7D232MD86M4D198MD2M2D78M4D84M8D84M4D56M6D28M6D22M4D6M3D114MD16MD297M4D105M2D129MD222M5D179M
  |
| gorilla_gorilla    | 13   |      52175893 |    52186820 |   1 |
  355M5D85M4D180M6706D64M2D185M2D83M987D64M3D119MD198M4D89M18D47M5D5M3D23MD45M14D255MD176M10D81MD112M5D83M3D48M3D4MD73M137D2MD66M42D27MD372M4D30MD298M3D43MD25M2D23MD66M1044D45M387D5M6D79M99D73MD107MD285M7D38M16D40M5D18M3D62M4DM8D288MD50M11D60M9D467M2D91MD91M2D17M5D50M3D2M3D148M10D62MD352M9D10M108D18M134D17M6D118M6D27MD13MD56M2D62M54D50MD58M7D42M6D5M3D241MD10MD70MD313M2D11M14D65M3D7M4D477M582D212M8D3M18DM15D230MD817M5DMD417M5D27M2D363M2D78M4D2M4D81M3D180M6D464M2D182MD7M4D267M15D169M
  |
| pan_troglodytes    | 13   |      69435755 |    69446651 |   1 |
  143M4D208M5D85M4D14M7D159M6706D64M2D270M987D64M3D318M4D89M18D83MD45M14D432M10D37MD43MD112M5D84MD49M3D4MD73M137D2MD66M40D29MD406MD298M3D43MD25M2D23MD66M1044D45M387D5M6D79M99D73MD107MD276MD8M7D38M16D40M5D18M3D67M8D69MD63M4D151MD50M11D60M9D467M2D91MD32MD58M2D17M5D50M3D2M3D148M10D76M9D10M262D11M3D2M6D280MD34M10D131M6D24M6D11MD56M2D62M54D50MD18M2D38M7D33M3D6M6D5M3D241MD10MD70MD313M2D11M14D556M582D210M12DM18DM15D230MD817M5DMD417M5D27M2D363M2D78M4D2M4D81M3D352MD297M2DMD180MD7M4D267M15D169M
  |
+--------------------+------+---------------+-------------+-----+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The aligned sequences can be rebuild using the original sequences fetched from the corresponding core databases and the cigar lines as explained before.

genomic_align_tree

This table is used to index tree alignments, e.g. EPO alignments. These alignments include inferred ancestral sequences. The tree required to index these sequences is stored in this table. This table stores the structure of the tree. Each node links to an entry in the genomic_align_group table, which links to one or several entries in the genomic_align table.

genomic_align_tree table description
Field Type Null Key Default Extra Description
node_id bigint(20) unsigned NO PRI auto_increment internal unique id
parent_id bigint(20) unsigned NO MUL 0 link to the parent node
root_id bigint(20) unsigned NO MUL 0 link to the root node
left_index int(10) NO MUL 0 internal index. See below
right_index int(10) NO MUL 0 internal index. See below
left_node_id bigint(10) NO 0 link to the node on the left side of this node
right_node_id bigint(10) NO 0 link to the node on the right side of this node
distance_to_parent double NO 1 phylogenetic distance between this node and its parent

Left_index and right_index are used to speed up fetching trees from the database. Any given node has its left_index larger than the left_index of its parent node and its right index smaller than the right_index of its parent node. In other words, all descendent nodes of a given node can be obtained by fetching all the node with a left_index (or right_index or both) between the left_index and the right_index of that node.

E.g. the row

mysql > SELECT * FROM genomic_align_tree WHERE node_id = 5050000000384;
+---------------+-----------+---------------+------------+-------------+--------------+---------------+--------------------+
| node_id       | parent_id | root_id       | left_index | right_index | left_node_id | right_node_id | distance_to_parent |
+---------------+-----------+---------------+------------+-------------+--------------+---------------+--------------------+
| 5050000000384 |         0 | 5050000000384 |  700000767 |   700000780 |            0 |             0 |              1e-05 |
+---------------+-----------+---------------+------------+-------------+--------------+---------------+--------------------+

correspond to the root of a tree, because parent_id = 0 and root_id = node_id. In order to fetch all the nodes of this tree, one can use the left_index and right_index values:

mysql > SELECT * FROM genomic_align_tree WHERE left_index >= 700000767 and left_index <= 700000780;
+---------------+---------------+---------------+------------+-------------+---------------+---------------+--------------------+
| node_id       | parent_id     | root_id       | left_index | right_index | left_node_id  | right_node_id | distance_to_parent |
+---------------+---------------+---------------+------------+-------------+---------------+---------------+--------------------+
| 5050000000384 |             0 | 5050000000384 |  700000767 |   700000780 |             0 |             0 |              1e-05 |
| 5050000000385 | 5050000000384 | 5050000000384 |  700000768 |   700000773 |             0 |             0 |              1e-05 |
| 5050000000387 | 5050000000385 | 5050000000384 |  700000769 |   700000770 | 5050000017109 | 5050000018754 |           0.005763 |
| 5050000000386 | 5050000000385 | 5050000000384 |  700000771 |   700000772 | 5050000004222 | 5050000006029 |            0.00664 |
| 5050000000388 | 5050000000384 | 5050000000384 |  700000774 |   700000779 | 5050000022808 | 5050000018751 |           0.281407 |
| 5050000000390 | 5050000000388 | 5050000000384 |  700000775 |   700000776 | 5050000022809 | 5050000018752 |           0.033656 |
| 5050000000389 | 5050000000388 | 5050000000384 |  700000777 |   700000778 | 5050000022810 | 5050000018753 |           0.041709 |
+---------------+---------------+---------------+------------+-------------+---------------+---------------+--------------------+

The API rebuilds the tree using this information. In this example, the tree is:

(5050000000384 700000767,700000780)Tgut-Mgal-Ggal-Tgut[4]
|-(5050000000385 700000768,700000773)Tgut-Tgut[2]
| |-(5050000000387 700000769,700000770)Tgut_1A_67082846_67096418[-]
| |-(5050000000386 700000771,700000772)Tgut_Un_165936071_165946337[+]
|-----------------------------(5050000000388 700000774,700000779)Mgal-Ggal[2]
                              |----(5050000000390 700000775,700000776)Ggal_1_58700491_58713206[-]
                              |-----(5050000000389 700000777,700000778)Mgal_1_57744366_57757821[-]

Please, refer to the genomic_align_group table for more examples on how to access tree alignments

conservation_score

Contains conservation scores calculated from the whole-genome multiple alignments stored in the genomic_align_block table.

conservation_score table description
Field Type Null Key Default Extra Description
genomic_align_block_id bigint(20) unsigned NO MUL external reference to genomic_align_block.node_id
window_size smallint(5) unsigned NO The scores are stored at different resolution levels. This column defines the window size used to calculate the average score.
position int(10) unsigned NO
expected_score blob YES MUL expected score
diff_score blob YES MUL diff_score is the difference between the expected and observed variation, i.e. the conservation score.

Several scores are stored per row. expected_score and diff_score are binary columns and you need to use the Perl API to access these data.

constrained_element

Contains constrained elements calculated from the whole-genome multiple alignments stored in the genomic_align_block table.

constrained_element table description
Field Type Null Key Default Extra Description
constrained_element_id bigint(20) unsigned NO MUL internal ID
dnafrag_id int(12) unsigned NO MUL external reference to dnafrag.dnafrag_id
dnafrag_start int(12) NO start of the constrained element
dnafrag_end int(12) NO end of the constrained element
method_link_species_set_id int(10) unsigned NO MUL external reference to method_link_species_set.method_link_species_set_id
p_value DOUBLE YES NULL p-value derived from Gerp
score double NO 0 score derived from Gerp
dnafrag_strand int(2) YES NULL Strand of the sequence in the alignment

E.g. the row

mysql> SELECT * FROM constrained_element LIMIT 1;
+------------------------+------------+---------------+-------------+----------------+----------------------------+-------------+-----------------+-------+
| constrained_element_id | dnafrag_id | dnafrag_start | dnafrag_end | dnafrag_strand | method_link_species_set_id | p_value     | taxonomic_level | score |
+------------------------+------------+---------------+-------------+----------------+----------------------------+-------------+-----------------+-------+
|          5060000000001 |   12160541 |        403245 |      403580 |            -1  |                        506 | 1.53905e-11 | neognath birds  | 104.6 |
+------------------------+------------+---------------+-------------+----------------+----------------------------+-------------+-----------------+-------+

refers to a constrained element from chromosome 1A_random of taeniopygia_guttata located between nucleotide positions 403245 and 403580.

There are 2 other elements in the same constrained_element:

mysql> SELECT constrained_element_id, genome_db.name, dnafrag.name FROM constrained_element JOIN dnafrag USING (dnafrag_id) JOIN genome_db USING (genome_db_id) WHERE constrained_element_id = 5060000000001;
+------------------------+---------------------+-----------+
| constrained_element_id | name                | name      |
+------------------------+---------------------+-----------+
|          5060000000001 | taeniopygia_guttata | 1A_random |
|          5060000000001 | gallus_gallus       | 1         |
|          5060000000001 | meleagris_gallopavo | 1         |
+------------------------+---------------------+-----------+

synteny_region

Contains all the syntenic relationships found and the relative orientation of both syntenic regions.

synteny_region table description
Field Type Null Key Default Extra Description
synteny_region_id int(10) unsigned NO PRI auto_increment internal unique ID
method_link_species_set_id int(10) unsigned NO MUL external reference to method_link_species_set.method_link_species_set_id.

E.g. the row

mysql> SELECT * FROM synteny_region WHERE synteny_region_id = 33234;
+-------------------+----------------------------+
| synteny_region_id | method_link_species_set_id |
+-------------------+----------------------------+
|             33234 |                      10021 |
+-------------------+----------------------------+

means that the syntenic region 33234 corresponds to a synteny relationship between the Mouse and Rat genomes.

mysql> SELECT synteny_region_id, name FROM synteny_region join method_link_species_set USING(method_link_species_set_id) WHERE synteny_region_id = 33234;
+-------------------+---------------------+
| synteny_region_id | name                |
+-------------------+---------------------+
|             33234 | M.mus-R.nor synteny |
+-------------------+---------------------+

See dnafrag_region table for more details.

dnafrag_region

Contains the genomic regions corresponding to every synteny relationship found. There are two genomic regions for every synteny relationship.

dnafrag_region table description
Field Type Null Key Default Extra Description
synteny_region_id int(10) unsigned NO MUL 0 external reference to synteny_region.synteny_region_id
dnafrag_id bigint(20) unsigned NO MUL 0 external reference to dnafrag.dnafrag_id
dnafrag_start int(10) unsigned NO 0 first nucleotide from this dnafrag which is in synteny
dnafrag_end int(10) unsigned NO 0 last nucleotide from this dnafrag which is in synteny
dnafrag_strand tinyint(4) NO 0 strand of this region

E.g. the rows

mysql> SELECT * FROM dnafrag_region WHERE synteny_region_id = 33234;
+-------------------+------------+---------------+-------------+----------------+
| synteny_region_id | dnafrag_id | dnafrag_start | dnafrag_end | dnafrag_strand |
+-------------------+------------+---------------+-------------+----------------+
|             33234 |     175243 |        106695 |    16719004 |             -1 |
|             33234 |    3997580 |       3000306 |    17925237 |              1 |
+-------------------+------------+---------------+-------------+----------------+

Here is a better way to get this by joining the dnafrag and genome_db tables:

A
mysql> SELECT genome_db.name, dnafrag.name, dnafrag_start, dnafrag_end, dnafrag_strand FROM dnafrag_region LEFT JOIN dnafrag USING (dnafrag_id) LEFT JOIN genome_db USING (genome_db_id) WHERE synteny_region_id = 33234; +-------------------+------+---------------+-------------+----------------+ | name | name | dnafrag_start | dnafrag_end | dnafrag_strand | +-------------------+------+---------------+-------------+----------------+ | rattus_norvegicus | 5 | 106695 | 16719004 | -1 | | mus_musculus | 1 | 3000306 | 17925237 | 1 | +-------------------+------+---------------+-------------+----------------+ +-------------------+------+---------------+-------------+----------------+ | name | name | dnafrag_start | dnafrag_end | dnafrag_strand | +-------------------+------+---------------+-------------+----------------+ | Rattus norvegicus | X | 498 | 12057776 | -1 | | Homo sapiens | X | 115311888 | 125710881 | 1 | +-------------------+------+---------------+-------------+----------------+

correspond to both genomic regions of the synteny region 33234. In this case, the first genomic region corresponds to the negative strand of the sequence from 106695 to 16719004 of the chromosome 5 of the Rat genome (dnafrag_id = 175243 for this chromosome) and the second one corresponds to the sequence from 3000306 to 17925237 of the chromosome 1 of the Mouse genome (dnafrag_id = 3997580 for this chromosome).

Tables For Orthologues and Protein Clusters

member

This table links sequences to the EnsEMBL core DB or to external DBs.

member table description
Field Type Null Key Default Extra Description
member_id int(10) unsigned NO PRI auto_increment internal unique ID
stable_id varchar(128) NO MUL EnsEMBL stable ID or external ID (for Uniprot/SWISSPROT and Uniprot/SPTREMBL)
version int(10) YES 0 version of the stable ID (see EnsEMBL core DB)
source_name enum('ENSEMBLGENE', 'ENSEMBLPEP', 'Uniprot/SPTREMBL', 'Uniprot/SWISSPROT', 'ENSEMBLTRANS', 'EXTERNALCDS') NO MUL NULL the source of the member
taxon_id int(10) unsigned NO MUL external reference to ncbi_taxa_node.taxon_id
genome_db_id int(10) unsigned YES MUL NULL external reference to genome_db.genome_db_id
sequence_id int(10) unsigned YES MUL NULL external reference to sequence.sequence_id.
May be 0 when the sequence is not available in the sequence table, e.g. for a gene instance.
gene_member_id int(10) unsigned YES MUL NULL external reference to member.memebr_id to allow linkage from peptides to genes.
description text YES NULL the description of the gene/protein as described in the core database or from the Uniprot entry
chr_name char(40) YES NULL chromosome where this sequence is located
chr_start int(10) YES NULL first nucleotide of this chromosome which corresponds to this member
chr_end int(10) YES NULL last nucleotide of this chromosome which corresponds to this member
chr_strand tinyint(1) NO strand of the chromosome in which the member is
display_label varchar(128) YES NULL

E.g. the row

mysql> SELECT * FROM member WHERE stable_id = "ENSP00000309431";

+-----------+-----------------+---------+-------------+----------+--------------+-------------+----------------+--
| member_id | stable_id       | version | source_name | taxon_id | genome_db_id | sequence_id | gene_member_id |
+-----------+-----------------+---------+-------------+----------+--------------+-------------+----------------+--
|   5245378 | ENSP00000309431 |       5 | ENSEMBLPEP  |     9606 |           90 |     2042683 |        5245376 |
+-----------+-----------------+---------+-------------+----------+--------------+-------------+----------------+--

--+------------------------------------------------------------------------------+----------+-----------+---------+------------+-------------------+
  | description                                                                  | chr_name | chr_start | chr_end | chr_strand | display_label     |
--+------------------------------------------------------------------------------+----------+-----------+---------+------------+-------------------+
  | Transcript:ENST00000308911 Gene:ENSG00000173213 Chr:18 Start:47225 End:50240 | 18       |     47390 |   49557 |         -1 | RP11-683L23.1-001 |
--+------------------------------------------------------------------------------+----------+-----------+---------+------------+-------------------+

refers to the human (ncbi_taxa_node.taxon_id = 9606 or genome_db_id = 90) peptide ENSP00000309431 which is located in the chromosome 18 (from 47390 to 49557, in the reverse strand). This peptide is described as "Transcript:ENST00000308911 Gene:ENSG00000173213 Chr:18 Start:47390 End:49557" and the sequence can be found on the sequence table.

sequence

This table contains the protein sequences present in the member table used in the protein alignment part of the EnsEMBL Compara DB.

sequence table description
Field Type Null Key Default Extra Description
sequence_id int(10) unsigned NO PRI auto_increment internal unique ID
length int(10) NO the length of the sequence
sequence longtext NO MUL the sequence

E.g. the row

mysql> SELECT * FROM sequence WHERE sequence_id = 30;
+-------------+--------+--------------------------------------------+
| sequence_id | length | sequence                                   |
+-------------+--------+--------------------------------------------+
|          30 |     42 | MLVESEYGDEILNAIAAIVNPSTKLAGEVGAAAQLVQKTARK |
+-------------+--------+--------------------------------------------+

contains a 42 aminoacids long sequence.

peptide_align_feature

This tables stores the raw HSP local alignment results of peptide to peptide alignments returned by a BLAST run it is translated from a FeaturePair object.

peptide_align_feature table description
Field Type Null Key Default Extra Description
peptide_align_feature_id int(10) unsigned NO PRI auto_increment internal unique ID
qmember_id int(10) unsigned NO MUL external reference to member.member_id for the query peptide
hmember_id int(10) unsigned NO MUL external reference to member.member_id for the hit peptide
qgenome_db_id int(10) unsigned NO MUL external reference to genome_db.genome_db_id for the query peptide (for query optimization)
hgenome_db_id int(10) unsigned NO MUL external reference to genome_db.genome_db_id for the hit peptide (for query optimization)
qstart int(10) NO 0 starting position in the query peptide sequence
qend int(10) NO 0 ending position in the query peptide sequence
hstart int(11) NO 0 starting position in the hit peptide sequence
hend int(11) NO 0 ending position in the hit peptide sequence
score double(16,4) NO 0.0000 blast score for this HSP
evalue double YES NULL blast evalue for this HSP
align_length int(10) YES NULL alignment length of HSP
identical_matches int(10) YES NULL blast HSP match score
perc_ident int(10) YES NULL percent identical matches in the HSP length
positive_matches int(10) YES NULL blast HSP positive score
perc_pos int(10) YES NULL precent positive matches in the HSP length
hit_rank int(10) YES NULL rank in blast result
cigar_line mediumtext YES NULL cigar string coding the actual alignment

E.g. the rows:

mysql> SELECT * FROM peptide_align_feature_homo_sapiens_22 WHERE peptide_align_feature_id = 5003775;
+--------------------------+------------+------------+---------------+---------------+--
| peptide_align_feature_id | qmember_id | hmember_id | qgenome_db_id | hgenome_db_id |
+--------------------------+------------+------------+---------------+---------------+--
|                  5003775 |    5536387 |    4443305 |            90 |           111 |
+--------------------------+------------+------------+---------------+---------------+--

--+--------+------+--------+------+---------+----------+--------------+-------------------+--
  | qstart | qend | hstart | hend | score   | evalue   | align_length | identical_matches |
--+--------+------+--------+------+---------+----------+--------------+-------------------+--
  |    744 |  986 |    836 | 1083 | 90.0000 | 2.2e-123 |          257 |                57 |
--+--------+------+--------+------+---------+----------+--------------+-------------------+--

--+------------+------------------+----------+----------+-----------------------------------------------+
  | perc_ident | positive_matches | perc_pos | hit_rank | cigar_line                                    |
--+------------+------------------+----------+----------+-----------------------------------------------+
  |         22 |              105 |       40 |        9 | 17MD6M4I6MI11MI18MI24MI38M6D22MI19M3D39M4D34M |
--+------------+------------------+----------+----------+-----------------------------------------------+

corresponds to a particular hit found between a Homo sapiens protein and a Anolis carolinensis protein:

mysql> SELECT g1.name as qgenome, m1.stable_id as qstable_id, g2.name as hgenome, m2.stable_id as hstable_id, score, evalue FROM peptide_align_feature_homo_sapiens_90 LEFT JOIN member m1 ON (qmember_id = m1.member_id) LEFT JOIN member m2 ON (hmember_id = m2.member_id) LEFT JOIN genome_db g1 ON (qgenome_db_id = g1.genome_db_id) LEFT JOIN genome_db g2 ON (hgenome_db_id = g2.genome_db_id) WHERE peptide_align_feature_id = 5003775;
+--------------+-----------------+---------------------+--------------------+---------+----------+
| qgenome      | qstable_id      | hgenome             | hstable_id         | score   | evalue   |
+--------------+-----------------+---------------------+--------------------+---------+----------+
| homo_sapiens | ENSP00000341905 | anolis_carolinensis | ENSACAP00000012641 | 90.0000 | 2.2e-123 |
+--------------+-----------------+---------------------+--------------------+---------+----------+

homology

Contains all the genomic homologies found. There are two homology_member entries for each homology entry for now, but both the schema and the API can handle more than just pairwise relations.

homology table description
Field Type Null Key Default Extra Description
homology_id int(10) unsigned NO PRI auto_increment internal unique ID
method_link_species_set_id int(10) unsigned MUL external reference to method_link_species_set.method_link_species_set_id
description enum('ortholog_one2one','apparent_ortholog_one2one','ortholog_one2many','ortholog_many2many','within_species_paralog','other_paralog','putative_gene_split','contiguous_gene_split','between_species_paralog','possible_ortholog','UBRH','BRH','MBRH','RHS','projection_unchanged','projection_altered') YES NULL describes the type of homology found
subtype varchar(40) NO gives the taxonomy name of the latest common taxonomic ancestor for the considered homology e.g.
  • Homo sapiens
  • Amniota
  • Diptera
  • Etc...
dn float(10,5) YES NULL number of nonsynonymous substitutions per nonsynonymous site
ds float(10,5) YES NULL number of synonymous substitutions per synonymous site
n float(10,1) YES NULL number of nonsynonymous substitutions
s float(10,1) YES NULL number of synonymous substitutions
lnl float(10,3) YES NULL maximum likelihood test value
threshold_on_ds float(10,5) YES NULL used by the EnsEMBL Web Browser to decide whether or not to display dN/DS ratio
ancestor_node_id int(10) unsigned NO external reference to protein_tree_node.node_id that represents the ancestor node between the 2 genes involved in the homology relation
tree_node_id int(10) unsigned NO MUL external reference to protein_tree_node.node_id that represents the root node of the tree that relates the 2 genes involved in the homology relation

dN, dS, N, S and lnL are statistical values given by the codeml program of the Phylogenetic Analysis by Maximum Likelihood (PAML) package.

E.g. the row

mysql> SELECT * FROM homology WHERE homology_id = 4650;
+-------------+-----------+----------------------------+-------------------+----------+---------+---------+-------+------+----------+-----------------+------------------+--------------+
| homology_id | stable_id | method_link_species_set_id | description       | subtype  | dn      | ds      | n     | s    | lnl      | threshold_on_ds | ancestor_node_id | tree_node_id |
+-------------+-----------+----------------------------+-------------------+----------+---------+---------+-------+------+----------+-----------------+------------------+--------------+
|        4650 | NULL      |                      25276 | possible_ortholog | Eutheria | 0.19210 | 0.82390 | 241.1 | 82.9 | -687.349 |            NULL |          1078947 |       504362 |
+-------------+-----------+----------------------------+-------------------+----------+---------+---------+-------+------+----------+-----------------+------------------+--------------+

defines a pair of paralogous xenopous genes. See homology_member for more details

mysql>
SELECT homology_id, description, GROUP_CONCAT(genome_db.name) AS species FROM homology LEFT JOIN method_link_species_set USING (method_link_species_set_id) LEFT JOIN species_set USING (species_set_id) LEFT JOIN genome_db USING(genome_db_id) WHERE homology_id = 4650 GROUP BY homology_id;
+-------------+------------------------+--------------------+
| homology_id | description            | species            |
+-------------+------------------------+--------------------+
|        4650 | within_species_paralog | xenopus_tropicalis |
+-------------+------------------------+--------------------+

homology_member

Contains the sequences corresponding to every genomic homology relationship found. There are two homology_member entries for each pairwise homology entry. As written in the homology table section, both schema and API can deal with more than pairwise relationships.

The original alignment is not stored but it can be retrieved using the cigar_line field and the original sequences. The cigar line defines the sequence of matches or mismatches and deletions in the alignment.

  • First peptide sequence: SERCQVVVISIGPISVLSMILDFY
  • Second peptide sequence: SDRCQVLVISILSMIGLDFY
  • First corresponding cigar line: 20MD4M
  • Second corresponding cigar line: 11M5D9M

The alignment will be:

Example of alignment reconstruction
First peptide cigar line M M M M M M M M M M M M M M M M M M M M D M M M M
First aligned peptide S E R C Q V V V I S I G P I S V L S M I - L D F Y
Second aligned peptide S D R C Q V L V I S I - - - - - L S M I G L D F Y
Second peptide cigar line M M M M M M M M M M M D D D D D M M M M M M M M M
homology_member table description
Field Type Null Key Default Extra Description
homology_id int(10) unsigned NO PRI external reference to homology.homology_id
member_id int(10) unsigned NO PRI external reference to member.member_id. It refers to the corresponding gene (ENSEMBL_GENE).
peptide_member_id int(10) unsigned YES MUL NULL external reference to member.member_id. It refers to the peptide/protein (ENSEMBL_PEP).
cigar_line mediumtext YES NULL an internal description of the alignment. It contains mathces/mismatches (M) and delations (D)and refers to the corresponding peptide_member_id sequence.
perc_cov int(10) YES NULL defines the percentage of the peptide which has been aligned
perc_id int(10) YES NULL defines the percentage of identity between both homologues
perc_pos int(10) YES NULL defines the percentage of positivity (similarity) between both homologues

E.g. the rows

mysql> SELECT * FROM homology_member WHERE homology_id = 4650;
+-------------+-----------+-------------------+------------+----------+---------+----------+
| homology_id | member_id | peptide_member_id | cigar_line | perc_cov | perc_id | perc_pos |
+-------------+-----------+-------------------+------------+----------+---------+----------+
|        4650 |   3400902 |           3400903 | D321M      |      100 |      87 |       90 |
|        4650 |   3400906 |           3400907 | 308M9D5M   |      100 |      89 |       92 |
+-------------+-----------+-------------------+------------+----------+---------+----------+

refer to the two homologue sequences defined by the homology.homology_id 4650. The gene corresponding to the first sequence can be retrieved using the member.member_id 423179 and the corresponding peptide using the member.member_id 423181. Gene and peptide sequence of the second homologue can retrieved in the same way.

family

Contains all the group homologies found. There are several family_member entries for each family entry.

family table description
Field Type Null Key Default Extra Description
family_id int(10) unsigned NO PRI auto_increment internal unique ID
stable_id varchar(40) NO UNI stable family ID
version int(10) NO version of the stable_id (may only change when members migrate between this family and another one; stays the same otherwise)
method_link_species_set_id int(10) unsigned NO MUL external reference to method_link_species_set.method_link_species_set_id
description varchar(255) YES MUL NULL description of the family as found using the Longest Common String (LCS) of the descriptions of the member proteins.
description_score double YES NULL Scores the accuracy of the annotation (max. 100)

E.g. the row

mysql> SELECT * FROM family WHERE family_id = 29739;
+-----------+---------------------+---------+----------------------------+--------------+-------------------+
| family_id | stable_id           | version | method_link_species_set_id | description  | description_score |
+-----------+---------------------+---------+----------------------------+--------------+-------------------+
|     29739 | ENSFM00350000109081 |       2 |                      30037 | CATHELICIDIN |               100 |
+-----------+---------------------+---------+----------------------------+--------------+-------------------+

defines a family of "CATHELICIDIN" proteins. The score of the description is 100.

NOTE: stable_id are currently not stable. We are working in getting IDs stable between releases.

family_member

Contains the proteins corresponding to protein family relationship found. There are several family_member entries for each family entry.

family_member table description
Field Type Null Key Default Extra Description
family_id int(10) unsigned NO PRI external reference to family.family_id
member_id int(10) unsigned NO PRI external reference to member.member_id
cigar_line mediumtext YES NULL internal description of the multiple alignment (see homology_member table)

E.g. the rows

mysql> SELECT * FROM family_member WHERE family_id = 29739;
+-----------+-----------+-------------------------------------+
| family_id | member_id | cigar_line                          |
+-----------+-----------+-------------------------------------+
|     29739 | 100519523 | 25D112M7D19M31D11M3D22M24D          |
|     29739 | 100531782 | 140M4D19M19D7M3D2M3D23M5D22M4D3M    |
|     29739 | 100581163 | 163M25D17M3D22M24D                  |
|     29739 | 100771938 | 137M7D13M3D3M25D17M3D38M8D          |
|     29739 | 100776954 | 140M4D11M5D3M25D15M9D18M24D         |
|     29739 | 100928923 | 139M5D17M27D6M3D8M3D21M9D16M        |
|     29739 | 100998297 | 137M7D13M3D3M25D17M3D22M24D         |
|     29739 | 101047098 | 137M7D13M3D3M25D17M3D38M8D          |
|     29739 | 101183977 | 101M43D19M25D17M3D22M24D            |
|     29739 | 101186624 | 140M4D19M19D7M3D2M3D23M5D22M4D3M    |
|     29739 | 101197586 | 30D71M43D19M25D17M3D22M24D          |
|     29739 | 101230726 | 140M4D61M3D21M25D                   |
|     29739 | 101266724 | 139M5D17M27DM3D2M3D8M3D12M5D22M4D3M |
|     29739 | 101304015 | 137M7D13M3D3M25D17M3D38M8D          |
+-----------+-----------+-------------------------------------+

refer to the four members of the protein family 54177. The proteins can be retieved using the member_ids. The multiple alignment can be restored using the cigar_lines.

gene_tree_root

Header table to gene trees

gene_tree_root table description
Field Type Null Key Default Extra Description
root_id int(10) unsigned NO PRI auto_increment internal unique ID
tree_type enum('clusterset','supertree','tree') NO the type of tree
member_type enum('protein','ncrna') NO the type of members used in the tree
clusterset_id int(10) unsigned YES NULL root_id for the set of clusters
method_link_species_set_id int(10) unsigned NO MUL NULL links to method_link_species_set.method_link_species_set_id
stable_id varchar(40) YES UNI Stable id for the tree
version int(10) YES NULL version of the stable_id

The database is able to contain several sets of trees computed on the same genes. We call these analysis "clustersets" and they can be distinguished with the clusterset_id field. Traditionally, the compara databases have contained only one clusterset (clusterset_id=1), but currently (starting on release 66) we have at least 2 (one for protein trees and one for ncRNA trees). The following SQL query retrieves all the node_id of the current clustersets

mysql> SELECT * from gene_tree_root where tree_type = 'clusterset';
+-----------+-------------+------------+---------------+----------------------------+-----------+---------+
| root_id   | member_type | tree_type  | clusterset_id | method_link_species_set_id | stable_id | version |
+-----------+-------------+------------+---------------+----------------------------+-----------+---------+
|         1 | protein     | clusterset |          NULL |                      40081 | NULL      |    NULL |
| 100000001 | ncrna       | clusterset |          NULL |                      40082 | NULL      |    NULL |
+-----------+-------------+------------+---------------+----------------------------+-----------+---------+

To get the number of trees of each type:

mysql> SELECT member_type, tree_type, COUNT(*) FROM gene_tree_root GROUP BY member_type, tree_type;
+-------------+------------+----------+
| member_type | tree_type  | COUNT(*) |
+-------------+------------+----------+
| protein     | clusterset |        1 |
| protein     | supertree  |      405 |
| protein     | tree       |    19562 |
| ncrna       | clusterset |        1 |
| ncrna       | tree       |      554 |
+-------------+------------+----------+

gene_tree_node

Contains the data structure of each tree.

gene_tree_node table description
Field Type Null Key Default Extra Description
node_id int(10) unsigned NO PRI auto_increment internal unique ID
parent_id int(10) unsigned YES MUL NULL link to the parent node
root_id int(10) unsigned YES MUL NULL link to the root node
left_index int(10) NO MUL internal index. See below
right_index int(10) NO MUL internal index. See below
distance_to_parent double NO 1 phylogenetic distance between this node and its parent

In our data structure, all the trees of a given clusterset are arbitrarily connected to the same root. This eases to store and query in the same database the data from independant tree building analysis. Hence the "biological roots" of the trees are the children nodes of the main clusterset root. For instance the following SQL query

mysql> SELECT gtn.node_id FROM gene_tree_node gtn LEFT JOIN gene_tree_root gtr ON (gtn.parent_id = gtr.root_id) WHERE gtr.tree_type = 'clusterset' AND gtr.member_type = 'protein' LIMIT 10;
+---------+
| node_id |
+---------+
|       2 |
|    1399 |
|    2745 |
|    4043 |
|    5205 |
+---------+
...
+---------+
| 1037872 |
| 1037876 |
| 1037880 |
| 1037884 |
| 1037888 |
| 1037892 |
| 1037896 |
+---------+
18947 rows in set (0.34 sec)

will return the root nodes of the independant protein trees stored in the database

gene_tree_member

Contains the data about the leaf (joining the table with a member_id from the member table, all ENSEMBLPEP members (ENSEMBLTRANS for ncRNAs)
present in each tree. The method_link_species_set should link to an entry that has a method_link.type="PROTEIN_TREES" (for protein trees) or "NC TREES" (for ncRNA trees)

gene_tree_member
Field Type Null Key Default Extra Description
node_id int(10) unsigned NO PRI the id of node associated with this name
member_id int(10) unsigned NO MUL external reference to member.member_id.  Should always link to a protein (usually member.source_name='ENSEMBLPEP')
cigar_line mediumtext YES NULL compressed alignment information

protein_tree_member_score

to allow certain nodes (leaves) to have aligned protein member_scores attached to them

protein_tree_member_score table description
Field Type Null Key Default Extra Description
node_id int(10) unsigned NO PRI the id of node associated with this name
root_id int(10) unsigned NO MUL the id of the tree root
member_id int(10) unsigned NO MUL link to member.member_id in many-1 relation (single member per node)
method_link_species_set_id int(10) unsigned NO external reference to method_link_species_set.method_link_species_set_id
cigar_line mediumtext YES NULL string with the alignment score values

gene_tree_tag

Contains several tag/value data attached to node_ids

gene_tree_node_tag table description
Field Type Null Key Default Extra Description
node_id int(10) unsigned NO MUL external reference to gene_tree_node.node_id
tag varchar(50) NO MUL
value mediumtext NO

gene_tree_node_attr

Contains several protein tree attributes data attached to node_ids

gene_tree_node_attr table description
Field Type Null Key Default Extra Description
node_id int(10) unsigned NO PRI external reference to protein_tree_node.node_id
node_type enum('duplication','dubious','speciation','gene_split') YES type of homology
taxon_id int(10) unsigned YES MUL NULL refers to ncbi_taxa_node.node_id
taxon_name varchar(255) YES NULL refers to ncbi_taxa_name.name where ncbi_taxa_name.name_class='scientific name'
bootstrap tinyint(3) unsigned YES NULL the bootstrap value of the node in the tree
duplication_confidence_score double(4,5) YES NULL
tree_suport set('phyml_nt','nj_ds','phyml_aa','nj_dn','nj_mm','quicktree') YES NULL All the final trees are the result of merging different trees.
This column gives information about what trees support this node after the merge:
  • phyml_aa: phyml tree built with the amino-acid model WAG
  • phyml_nt: phyml tree built with the nucleotide model HKY
  • nj_dn: Neighbor-joining tree built using dn as a distance (number of non-synonymous changes)
  • nj_ds: Neighbor-joining tree built using ds as a distance (number of synonymous changes)
  • nj_mm: Neighbor-joining tree built using the "p-distance"
  • quicktree: Neighbor-joining tree using QuickTree

gene_tree_root_tagr

Contains several tag/value data for gene_tree_roots

gene_tree_root_tag table description
Field Type Null Key Default Extra Description
root_id int(10) unsigned NO PRI external reference to gene_tree_root.node_id
tag varchar(50) NO
value mediumtext NO

sitewise_aln

Contains site-wise omega values found in the multiple alignments underlining the protein trees.

sitewise_aln table description
Field Type Null Key Default Extra Description
sitewise_id int(10) unsigned NO PRI auto_increment internal unique ID
aln_position int(10) unsigned NO MUL the position in the whole GeneTree alignment, even if it is all_gaps in the subtree
node_id int(10) unsigned NO MUL the root of the subtree for which the sitewise is calculated
tree_node_id int(10) unsigned NO the root of the tree. it will be equal to node_id if we are calculating sitewise for the whole tree
omega float(10,5) YES NULL the estimated omega value at the position
omega_lower float(10,5) YES NULL the lower bound of the confidence interval
omega_upper float(10,5) YES NULL the upper bound of the confidence interval
threshold_on_branch_ds float(10,5) YES NULL the used threshold to break a tree into subtrees when the dS value of a given branch is too big. This is defined in the configuration file for the genetree pipeline
type enum('single_character', 'random', 'all_gaps', 'constant', 'default', 'negative1', 'negative2', 'negative3', 'negative4', 'positive1', 'positive2', 'positive3', 'positive4', 'synonymous') NO the predicted type for the codon/aminoacid

mapping_session

Contains one entry per stable_id mapping session (either for Families or for Protein Trees), which contains the type, the date of the mapping, and which releases were linked together.

mapping_session table description
Field Type Null Key Default Extra Description
mapping_session_id int(10) unsigned NO PRI auto_increment internal unique ID
type enum('family', 'tree') YES MUL NULL Type of stable_ids that were mapped during this session
when_mapped timestamp NO CURRENT_TIMESTAMP Normally, we use the date of creation of the mapping file being loaded. This prevents the date from chaging even if we accidentally remove the entry and have to re-load it.
rel_from int(10) unsigned YES NULL rel.number from which the stable_ids were mapped during this session. rel_from < rel_to
rel_to int(10) unsigned YES NULL rel.number to which the stable_ids were mapped during this session. rel_from < rel_to
prefix char(4) NO

stable_id_history

stable_id_history' table keeps the history of stable_id changes from one release to another. The primary key 'object' describes a set of members migrating from stable_id_from to stable_id_to. Their volume (related to the 'shared_size' of the new class) is reflected by the fractional 'contribution' field. Since both stable_ids are listed in the primary key, they are not allowed to be NULLs. We shall treat empty strings as NULLs. If stable_id_from is empty, it means these members are newcomers into the new release. If stable_id_to is empty, it means these previously known members are disappearing in the new release. If both neither stable_id_from nor stable_id_to is empty, these members are truly migrating.

stable_id_history table description
Field Type Null Key Default Extra Description
mapping_session_id int(10) unsigned NO PRI
stable_id_from varchar(40) NO PRI
version_from int(10) unsigned YES NULL
stable_id_to varchar(40) NO PRI
version_to int(10) unsigned YES NULL
contribution float YES NULL

protein_tree_stable_id

to allow protein trees have trackable stable_ids

protein_tree_stable_id table description
Field Type Null Key Default Extra Description
node_id int(10) unsigned NO PRI node_id of the root of the tree
stable_id varchar(40) NO UNI the main part of the stable_id ( follows the pattern: label(5).release_introduced(4).unique_id(10)
version int(10) unsigned NO NULL numeric version of the stable_id (changes only when members move to/from existing trees)