Description of Schema

Tables

cv
cvterm
cvterm_relationship
cvtermpath
cvtermsynonym
cvterm_dbxref
cvtermprop
dbxrefprop
cvprop
chadoprop

cv

Top
Comments:

$Id: cv.sql,v 1.37 2007-02-28 15:08:48 briano Exp $
==========================================
Chado cv module
=================================================================
Dependencies:
:import dbxref from general
=================================================================
================================================
TABLE: cv
================================================
A controlled vocabulary or ontology. A cv is composed of cvterms (AKA terms, classes, types, universals - relations and properties are also stored in cvterm) and the relationships between them.
Field Name Data Type Size Default Value Other Foreign Key
cv_id integer 11 PRIMARY KEY, NOT NULL
name varchar 255 UNIQUE, NOT NULL, The name of the ontology. This corresponds to the obo-format -namespace-. cv names uniquely identify the cv. In OBO file format, the cv.name is known as the namespace.
definition text 64000 A text description of the criteria for membership of this ontology.

Constraints

Type Fields
NOT NULL cv_id
NOT NULL name
UNIQUE name

cvterm

Top
Comments:

================================================
TABLE: cvterm
================================================
A term, class, universal or type within an ontology or controlled vocabulary. This table is also used for relations and properties. cvterms constitute nodes in the graph defined by the collection of cvterms and cvterm_relationships.
Field Name Data Type Size Default Value Other Foreign Key
cvterm_id integer 11 PRIMARY KEY, NOT NULL
cv_id integer 10 UNIQUE, NOT NULL, The cv or ontology or namespace to which this cvterm belongs. cv.cv_id
name varchar 1024 UNIQUE, NOT NULL, A concise human-readable name or label for the cvterm. Uniquely identifies a cvterm within a cv.
definition text 64000 A human-readable text definition.
dbxref_id integer 10 UNIQUE, NOT NULL, Primary identifier dbxref - The unique global OBO identifier for this cvterm. Note that a cvterm may have multiple secondary dbxrefs - see also table: cvterm_dbxref. dbxref.dbxref_id
is_obsolete integer 10 0 UNIQUE, NOT NULL, Boolean 0=false,1=true; see GO documentation for details of obsoletion. Note that two terms with different primary dbxrefs may exist if one is obsolete.
is_relationshiptype integer 10 0 NOT NULL, Boolean 0=false,1=true relations or relationship types (also known as Typedefs in OBO format, or as properties or slots) form a cv/ontology in themselves. We use this flag to indicate whether this cvterm is an actual term/class/universal or a relation. Relations may be drawn from the OBO Relations ontology, but are not exclusively drawn from there.

Indices

Name Fields
cvterm_idx1 cv_id
cvterm_idx2 name
cvterm_idx3 dbxref_id

Constraints

Type Fields
NOT NULL cvterm_id
NOT NULL cv_id
FOREIGN KEY cv_id
NOT NULL name
NOT NULL dbxref_id
FOREIGN KEY dbxref_id
NOT NULL is_obsolete
NOT NULL is_relationshiptype
UNIQUE name, cv_id, is_obsolete
UNIQUE dbxref_id

cvterm_relationship

Top
Comments:

A name can mean different things in different contexts; for example "chromosome" in SO and GO. A name should be unique within an ontology or cv. A name may exist twice in a cv, in both obsolete and non-obsolete forms - these will be for different cvterms with different OBO identifiers; so GO documentation for more details on obsoletion. Note that occasionally multiple obsolete terms with the same name will exist in the same cv. If this is a possibility for the ontology under consideration (e.g. GO) then the ID should be appended to the name to ensure uniqueness.
The OBO identifier is globally unique.
================================================
TABLE: cvterm_relationship
================================================
A relationship linking two cvterms. Each cvterm_relationship constitutes an edge in the graph defined by the collection of cvterms and cvterm_relationships. The meaning of the cvterm_relationship depends on the definition of the cvterm R refered to by type_id. However, in general the definitions are such that the statement "all SUBJs REL some OBJ" is true. The cvterm_relationship statement is about the subject, not the object. For example "insect wing part_of thorax".
Field Name Data Type Size Default Value Other Foreign Key
cvterm_relationship_id integer 11 PRIMARY KEY, NOT NULL
type_id integer 10 UNIQUE, NOT NULL, The nature of the relationship between subject and object. Note that relations are also housed in the cvterm table, typically from the OBO relationship ontology, although other relationship types are allowed. cvterm.cvterm_id
subject_id integer 10 UNIQUE, NOT NULL, The subject of the subj-predicate-obj sentence. The cvterm_relationship is about the subject. In a graph, this typically corresponds to the child node. cvterm.cvterm_id
object_id integer 10 UNIQUE, NOT NULL, The object of the subj-predicate-obj sentence. The cvterm_relationship refers to the object. In a graph, this typically corresponds to the parent node. cvterm.cvterm_id

Indices

Name Fields
cvterm_relationship_idx1 type_id
cvterm_relationship_idx2 subject_id
cvterm_relationship_idx3 object_id

Constraints

Type Fields
NOT NULL cvterm_relationship_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL subject_id
FOREIGN KEY subject_id
NOT NULL object_id
FOREIGN KEY object_id
UNIQUE subject_id, object_id, type_id

cvtermpath

Top
Comments:

================================================
TABLE: cvtermpath
================================================
The reflexive transitive closure of the cvterm_relationship relation.
Field Name Data Type Size Default Value Other Foreign Key
cvtermpath_id integer 11 PRIMARY KEY, NOT NULL
type_id integer 10 UNIQUE, The relationship type that this is a closure over. If null, then this is a closure over ALL relationship types. If non-null, then this references a relationship cvterm - note that the closure will apply to both this relationship AND the OBO_REL:is_a (subclass) relationship. cvterm.cvterm_id
subject_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
object_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
cv_id integer 10 NOT NULL, Closures will mostly be within one cv. If the closure of a relationship traverses a cv, then this refers to the cv of the object_id cvterm. cv.cv_id
pathdistance integer 10 UNIQUE, The number of steps required to get from the subject cvterm to the object cvterm, counting from zero (reflexive relationship).

Indices

Name Fields
cvtermpath_idx1 type_id
cvtermpath_idx2 subject_id
cvtermpath_idx3 object_id
cvtermpath_idx4 cv_id

Constraints

Type Fields
NOT NULL cvtermpath_id
FOREIGN KEY type_id
NOT NULL subject_id
FOREIGN KEY subject_id
NOT NULL object_id
FOREIGN KEY object_id
NOT NULL cv_id
FOREIGN KEY cv_id
UNIQUE subject_id, object_id, type_id, pathdistance

cvtermsynonym

Top
Comments:

================================================
TABLE: cvtermsynonym
================================================
A cvterm actually represents a distinct class or concept. A concept can be refered to by different phrases or names. In addition to the primary name (cvterm.name) there can be a number of alternative aliases or synonyms. For example, "T cell" as a synonym for "T lymphocyte".
Field Name Data Type Size Default Value Other Foreign Key
cvtermsynonym_id integer 11 PRIMARY KEY, NOT NULL
cvterm_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
synonym varchar 1024 UNIQUE, NOT NULL
type_id integer 10 A synonym can be exact, narrower, or broader than. cvterm.cvterm_id

Indices

Name Fields
cvtermsynonym_idx1 cvterm_id

Constraints

Type Fields
NOT NULL cvtermsynonym_id
NOT NULL cvterm_id
FOREIGN KEY cvterm_id
NOT NULL synonym
FOREIGN KEY type_id
UNIQUE cvterm_id, synonym

cvterm_dbxref

Top
Comments:

================================================
TABLE: cvterm_dbxref
================================================
In addition to the primary identifier (cvterm.dbxref_id) a cvterm can have zero or more secondary identifiers/dbxrefs, which may refer to records in external databases. The exact semantics of cvterm_dbxref are not fixed. For example: the dbxref could be a pubmed ID that is pertinent to the cvterm, or it could be an equivalent or similar term in another ontology. For example, GO cvterms are typically linked to InterPro IDs, even though the nature of the relationship between them is largely one of statistical association. The dbxref may be have data records attached in the same database instance, or it could be a "hanging" dbxref pointing to some external database. NOTE: If the desired objective is to link two cvterms together, and the nature of the relation is known and holds for all instances of the subject cvterm then consider instead using cvterm_relationship together with a well-defined relation.
Field Name Data Type Size Default Value Other Foreign Key
cvterm_dbxref_id integer 11 PRIMARY KEY, NOT NULL
cvterm_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
dbxref_id integer 10 UNIQUE, NOT NULL dbxref.dbxref_id
is_for_definition integer 10 0 NOT NULL, A cvterm.definition should be supported by one or more references. If this column is true, the dbxref is not for a term in an external database - it is a dbxref for provenance information for the definition.

Indices

Name Fields
cvterm_dbxref_idx1 cvterm_id
cvterm_dbxref_idx2 dbxref_id

Constraints

Type Fields
NOT NULL cvterm_dbxref_id
NOT NULL cvterm_id
FOREIGN KEY cvterm_id
NOT NULL dbxref_id
FOREIGN KEY dbxref_id
NOT NULL is_for_definition
UNIQUE cvterm_id, dbxref_id

cvtermprop

Top
Comments:

================================================
TABLE: cvtermprop
================================================
Additional extensible properties can be attached to a cvterm using this table. Corresponds to -AnnotationProperty- in W3C OWL format.
Field Name Data Type Size Default Value Other Foreign Key
cvtermprop_id integer 11 PRIMARY KEY, NOT NULL
cvterm_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
type_id integer 10 UNIQUE, NOT NULL, The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm. cvterm.cvterm_id
value text 64000 UNIQUE, NOT NULL, The value of the property, represented as text. Numeric values are converted to their text representation.
rank integer 10 0 UNIQUE, NOT NULL, Property-Value ordering. Any cvterm can have multiple values for any particular property type - these are ordered in a list using rank, counting from zero. For properties that are single-valued rather than multi-valued, the default 0 value should be used.

Indices

Name Fields
cvtermprop_idx1 cvterm_id
cvtermprop_idx2 type_id

Constraints

Type Fields
NOT NULL cvtermprop_id
NOT NULL cvterm_id
FOREIGN KEY cvterm_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL value
NOT NULL rank
UNIQUE cvterm_id, type_id, value, rank

dbxrefprop

Top
Comments:

================================================
TABLE: dbxrefprop
================================================
Metadata about a dbxref. Note that this is not defined in the dbxref module, as it depends on the cvterm table. This table has a structure analagous to cvtermprop.
Field Name Data Type Size Default Value Other Foreign Key
dbxrefprop_id integer 11 PRIMARY KEY, NOT NULL
dbxref_id integer 10 UNIQUE, NOT NULL dbxref.dbxref_id
type_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NOT NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
dbxrefprop_idx1 dbxref_id
dbxrefprop_idx2 type_id

Constraints

Type Fields
NOT NULL dbxrefprop_id
NOT NULL dbxref_id
FOREIGN KEY dbxref_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL value
NOT NULL rank
UNIQUE dbxref_id, type_id, rank

cvprop

Top
Comments:

================================================
TABLE: cvprop
================================================
Additional extensible properties can be attached to a cv using this table. A notable example would be the cv version
Field Name Data Type Size Default Value Other Foreign Key
cvprop_id integer 11 PRIMARY KEY, NOT NULL
cv_id integer 10 UNIQUE, NOT NULL cv.cv_id
type_id integer 10 UNIQUE, NOT NULL, The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm. cvterm.cvterm_id
value text 64000 The value of the property, represented as text. Numeric values are converted to their text representation.
rank integer 10 0 UNIQUE, NOT NULL, Property-Value ordering. Any cv can have multiple values for any particular property type - these are ordered in a list using rank, counting from zero. For properties that are single-valued rather than multi-valued, the default 0 value should be used.

Constraints

Type Fields
NOT NULL cvprop_id
NOT NULL cv_id
FOREIGN KEY cv_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL rank
UNIQUE cv_id, type_id, rank

chadoprop

Top
Comments:

================================================
TABLE: chadoprop
================================================
This table is different from other prop tables in the database, as it is for storing information about the database itself, like schema version
Field Name Data Type Size Default Value Other Foreign Key
chadoprop_id integer 11 PRIMARY KEY, NOT NULL
type_id integer 10 UNIQUE, NOT NULL, The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm. cvterm.cvterm_id
value text 64000 The value of the property, represented as text. Numeric values are converted to their text representation.
rank integer 10 0 UNIQUE, NOT NULL, Property-Value ordering. Any cv can have multiple values for any particular property type - these are ordered in a list using rank, counting from zero. For properties that are single-valued rather than multi-valued, the default 0 value should be used.

Constraints

Type Fields
NOT NULL chadoprop_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL rank
UNIQUE type_id, rank
Created by SQL::Translator 0.11003