Description of Schema


Tables

cv
cvterm
cvterm_relationship
cvtermpath
cvtermsynonym
cvterm_dbxref
cvtermprop
dbxrefprop
cvprop
chadoprop
dbprop

cv

Top
Comments:

$Id: cv.sql,v 1.37 2007-02-28 15:08:48 briano Exp $
==========================================
Chado cv module
=================================================================
Dependencies:
:import dbxref from db
=================================================================
================================================
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 20 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 NULLcv_id
NOT NULLname
UNIQUEname

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 20 PRIMARY KEY, NOT NULL
cv_id integer 20 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 20 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_idx1cv_id
cvterm_idx2name
cvterm_idx3dbxref_id

Constraints

Type Fields
NOT NULLcvterm_id
NOT NULLcv_id
FOREIGN KEYcv_id
NOT NULLname
NOT NULLdbxref_id
FOREIGN KEYdbxref_id
NOT NULLis_obsolete
NOT NULLis_relationshiptype
UNIQUEname, cv_id, is_obsolete
UNIQUEdbxref_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 20 PRIMARY KEY, NOT NULL
type_id integer 20 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 20 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 20 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_idx1type_id
cvterm_relationship_idx2subject_id
cvterm_relationship_idx3object_id

Constraints

Type Fields
NOT NULLcvterm_relationship_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLobject_id
FOREIGN KEYobject_id
UNIQUEsubject_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 20 PRIMARY KEY, NOT NULL
type_id integer 20 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 20 UNIQUE, NOT NULL cvterm.cvterm_id
object_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
cv_id integer 20 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_idx1type_id
cvtermpath_idx2subject_id
cvtermpath_idx3object_id
cvtermpath_idx4cv_id

Constraints

Type Fields
NOT NULLcvtermpath_id
FOREIGN KEYtype_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLobject_id
FOREIGN KEYobject_id
NOT NULLcv_id
FOREIGN KEYcv_id
UNIQUEsubject_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 20 PRIMARY KEY, NOT NULL
cvterm_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
synonym varchar 1024 UNIQUE, NOT NULL
type_id integer 20 A synonym can be exact, narrower, or broader than. cvterm.cvterm_id

Indices

Name Fields
cvtermsynonym_idx1cvterm_id

Constraints

Type Fields
NOT NULLcvtermsynonym_id
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
NOT NULLsynonym
FOREIGN KEYtype_id
UNIQUEcvterm_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 20 PRIMARY KEY, NOT NULL
cvterm_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
dbxref_id integer 20 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_idx1cvterm_id
cvterm_dbxref_idx2dbxref_id

Constraints

Type Fields
NOT NULLcvterm_dbxref_id
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
NOT NULLdbxref_id
FOREIGN KEYdbxref_id
NOT NULLis_for_definition
UNIQUEcvterm_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 20 PRIMARY KEY, NOT NULL
cvterm_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
type_id integer 20 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_idx1cvterm_id
cvtermprop_idx2type_id

Constraints

Type Fields
NOT NULLcvtermprop_id
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLvalue
NOT NULLrank
UNIQUEcvterm_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 20 PRIMARY KEY, NOT NULL
dbxref_id integer 20 UNIQUE, NOT NULL dbxref.dbxref_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NOT NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
dbxrefprop_idx1dbxref_id
dbxrefprop_idx2type_id

Constraints

Type Fields
NOT NULLdbxrefprop_id
NOT NULLdbxref_id
FOREIGN KEYdbxref_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLvalue
NOT NULLrank
UNIQUEdbxref_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 20 PRIMARY KEY, NOT NULL
cv_id integer 20 UNIQUE, NOT NULL cv.cv_id
type_id integer 20 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 NULLcvprop_id
NOT NULLcv_id
FOREIGN KEYcv_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEcv_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 20 PRIMARY KEY, NOT NULL
type_id integer 20 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 NULLchadoprop_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEtype_id, rank

dbprop

Top
Comments:

================================================
TABLE: dbprop
================================================
An external database can have any number of slot-value property tags attached to it. This is an alternative to hardcoding a list of columns in the relational schema, and is completely extensible. There is a unique constraint, dbprop_c1, for the combination of db_id, rank, and type_id. Multivalued property-value pairs must be differentiated by rank.
Field Name Data Type Size Default Value Other Foreign Key
dbprop_id integer 20 PRIMARY KEY, NOT NULL
db_id integer 20 UNIQUE, NOT NULL db.db_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
dbprop_idx1db_id
dbprop_idx2type_id

Constraints

Type Fields
NOT NULLdbprop_id
NOT NULLdb_id
NOT NULLtype_id
NOT NULLrank
FOREIGN KEYtype_id
FOREIGN KEYdb_id
UNIQUEdb_id, type_id, rank

Created by
SQL::Translator 0.11020