# 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 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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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 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 | 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 NULL | chadoprop_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | type_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_idx1 | db_id
dbprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | dbprop_id
NOT NULL | db_id
NOT NULL | type_id
NOT NULL | rank
FOREIGN KEY | type_id
FOREIGN KEY | db_id
UNIQUE | db_id, type_id, rank

Created by SQL::Translator 0.11020