Description of Schema

Tables

phylotree
phylotree_pub
phylonode
phylonode_dbxref
phylonode_pub
phylonode_organism
phylonodeprop
phylonode_relationship

phylotree

Top
Comments:

$Id: phylogeny.sql,v 1.11 2007-04-12 17:00:30 briano Exp $
==========================================
Chado phylogenetics module
Richard Bruskiewich
Chris Mungall
Initial design: 2004-05-27
============
DEPENDENCIES
============
:import feature from sequence
:import cvterm from cv
:import pub from pub
:import organism from organism
:import dbxref from general
:import analysis from companalysis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: phylotree
================================================
Global anchor for phylogenetic tree.
Field Name Data Type Size Default Value Other Foreign Key
phylotree_id integer 11 PRIMARY KEY, UNIQUE, NOT NULL
dbxref_id integer 10 NOT NULL dbxref.dbxref_id
name varchar 255 NULL
type_id integer 10 Type: protein, nucleotide, taxonomy, for example. The type should be any SO type, or "taxonomy". cvterm.cvterm_id
analysis_id integer 10 NULL analysis.analysis_id
comment text 64000 NULL

Indices

Name Fields
phylotree_idx1 phylotree_id

Constraints

Type Fields
NOT NULL phylotree_id
NOT NULL dbxref_id
FOREIGN KEY dbxref_id
FOREIGN KEY type_id
FOREIGN KEY analysis_id
UNIQUE phylotree_id

phylotree_pub

Top
Comments:

================================================
TABLE: phylotree_pub
================================================
Tracks citations global to the tree e.g. multiple sequence alignment supporting tree construction.
Field Name Data Type Size Default Value Other Foreign Key
phylotree_pub_id integer 11 PRIMARY KEY, NOT NULL
phylotree_id integer 10 UNIQUE, NOT NULL phylotree.phylotree_id
pub_id integer 10 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
phylotree_pub_idx1 phylotree_id
phylotree_pub_idx2 pub_id

Constraints

Type Fields
NOT NULL phylotree_pub_id
NOT NULL phylotree_id
FOREIGN KEY phylotree_id
NOT NULL pub_id
FOREIGN KEY pub_id
UNIQUE phylotree_id, pub_id

phylonode

Top
Comments:

================================================
TABLE: phylonode
================================================
Bootstrap float null.
This is the most pervasive element in the phylogeny module, cataloging the "phylonodes" of tree graphs. Edges are implied by the parent_phylonode_id reflexive closure. For all nodes in a nested set implementation the left and right index will be *between* the parents left and right indexes.
Field Name Data Type Size Default Value Other Foreign Key
phylonode_id integer 11 PRIMARY KEY, NOT NULL
phylotree_id integer 10 UNIQUE, NOT NULL phylotree.phylotree_id
parent_phylonode_id integer 10 NULL Root phylonode can have null parent_phylonode_id value. phylonode.phylonode_id
left_idx integer 10 UNIQUE, NOT NULL
right_idx integer 10 UNIQUE, NOT NULL
type_id integer 10 Type: e.g. root, interior, leaf. cvterm.cvterm_id
feature_id integer 10 Phylonodes can have optional features attached to them e.g. a protein or nucleotide sequence usually attached to a leaf of the phylotree for non-leaf nodes, the feature may be a feature that is an instance of SO:match; this feature is the alignment of all leaf features beneath it. feature.feature_id
label varchar 255 NULL
distance float 20 NULL

Constraints

Type Fields
NOT NULL phylonode_id
NOT NULL phylotree_id
FOREIGN KEY phylotree_id
FOREIGN KEY parent_phylonode_id
NOT NULL left_idx
NOT NULL right_idx
FOREIGN KEY type_id
FOREIGN KEY feature_id
UNIQUE phylotree_id, left_idx
UNIQUE phylotree_id, right_idx

phylonode_dbxref

Top
Comments:

================================================
TABLE: phylonode_dbxref
================================================
For example, for orthology, paralogy group identifiers; could also be used for NCBI taxonomy; for sequences, refer to phylonode_feature, feature associated dbxrefs.
Field Name Data Type Size Default Value Other Foreign Key
phylonode_dbxref_id integer 11 PRIMARY KEY, NOT NULL
phylonode_id integer 10 UNIQUE, NOT NULL phylonode.phylonode_id
dbxref_id integer 10 UNIQUE, NOT NULL dbxref.dbxref_id

Indices

Name Fields
phylonode_dbxref_idx1 phylonode_id
phylonode_dbxref_idx2 dbxref_id

Constraints

Type Fields
NOT NULL phylonode_dbxref_id
NOT NULL phylonode_id
FOREIGN KEY phylonode_id
NOT NULL dbxref_id
FOREIGN KEY dbxref_id
UNIQUE phylonode_id, dbxref_id

phylonode_pub

Top
Comments:

================================================
TABLE: phylonode_pub
================================================
Field Name Data Type Size Default Value Other Foreign Key
phylonode_pub_id integer 11 PRIMARY KEY, NOT NULL
phylonode_id integer 10 UNIQUE, NOT NULL phylonode.phylonode_id
pub_id integer 10 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
phylonode_pub_idx1 phylonode_id
phylonode_pub_idx2 pub_id

Constraints

Type Fields
NOT NULL phylonode_pub_id
NOT NULL phylonode_id
FOREIGN KEY phylonode_id
NOT NULL pub_id
FOREIGN KEY pub_id
UNIQUE phylonode_id, pub_id

phylonode_organism

Top
Comments:

================================================
TABLE: phylonode_organism
================================================
This linking table should only be used for nodes in taxonomy trees; it provides a mapping between the node and an organism. One node can have zero or one organisms, one organism can have zero or more nodes (although typically it should only have one in the standard NCBI taxonomy tree).
Field Name Data Type Size Default Value Other Foreign Key
phylonode_organism_id integer 11 PRIMARY KEY, NOT NULL
phylonode_id integer 10 UNIQUE, NOT NULL, One phylonode cannot refer to >1 organism. phylonode.phylonode_id
organism_id integer 10 NOT NULL organism.organism_id

Indices

Name Fields
phylonode_organism_idx1 phylonode_id
phylonode_organism_idx2 organism_id

Constraints

Type Fields
NOT NULL phylonode_organism_id
NOT NULL phylonode_id
FOREIGN KEY phylonode_id
NOT NULL organism_id
FOREIGN KEY organism_id
UNIQUE phylonode_id

phylonodeprop

Top
Comments:

================================================
TABLE: phylonodeprop
================================================
Field Name Data Type Size Default Value Other Foreign Key
phylonodeprop_id integer 11 PRIMARY KEY, NOT NULL
phylonode_id integer 10 UNIQUE, NOT NULL phylonode.phylonode_id
type_id integer 10 UNIQUE, NOT NULL, type_id could designate phylonode hierarchy relationships, for example: species taxonomy (kingdom, order, family, genus, species), "ortholog/paralog", "fold/superfold", etc. cvterm.cvterm_id
value text 64000 UNIQUE, NOT NULL
rank integer 10 0 UNIQUE, NOT NULL, It is not clear how useful the rank concept is here, leave it in for now.

Indices

Name Fields
phylonodeprop_idx1 phylonode_id
phylonodeprop_idx2 type_id

Constraints

Type Fields
NOT NULL phylonodeprop_id
NOT NULL phylonode_id
FOREIGN KEY phylonode_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL value
NOT NULL rank
UNIQUE phylonode_id, type_id, value, rank

phylonode_relationship

Top
Comments:

================================================
TABLE: phylonode_relationship
================================================
This is for relationships that are not strictly hierarchical; for example, horizontal gene transfer. Most phylogenetic trees are strictly hierarchical, nevertheless it is here for completeness.
Field Name Data Type Size Default Value Other Foreign Key
phylonode_relationship_id integer 11 PRIMARY KEY, NOT NULL
subject_id integer 10 UNIQUE, NOT NULL phylonode.phylonode_id
object_id integer 10 UNIQUE, NOT NULL phylonode.phylonode_id
type_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
rank integer 10
phylotree_id integer 10 NOT NULL phylotree.phylotree_id

Indices

Name Fields
phylonode_relationship_idx1 subject_id
phylonode_relationship_idx2 object_id
phylonode_relationship_idx3 type_id

Constraints

Type Fields
NOT NULL phylonode_relationship_id
NOT NULL subject_id
FOREIGN KEY subject_id
NOT NULL object_id
FOREIGN KEY object_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL phylotree_id
FOREIGN KEY phylotree_id
UNIQUE subject_id, object_id, type_id
Created by SQL::Translator 0.11005