Data dictionary for Ontology Schema - IntegratedBreedingPlatform/Documentation GitHub Wiki

Introduction

BMS ontology schema is based on the Controlled Vocabulary Model of Chado. A controlled vocabulary (cv) or ontology is composed of cv terms (AKA terms, classes, types, universals - relations and properties) and the relationships between them.

cv Table

Column Name	Description	Type	Size (bytes)
cv_id	Primary key. DB record id.	Integer	4
name	Name of the controlled vocabulary	Text	255
definition	Description of the controlled vocabulary	Text	255

cvterm Table

Column Name	Description	Type	Size (bytes)
cvterm_id	Primary key. DB record id.	Integer	4
cv_id	Foreign key to the cv table.	Integer	4
name	Name of the term	Text	200
definition	Definifion of the term	Text	1024
dbxref_id	Databse cross-reference to a unique, global, public, stable identifier. Not used in BMS.	Integer	11
is_obsolete	Whether the term is marked obsolete	Integer	4
is_relationshiptype	Whether the term represents a relationship.	Integer	4
is_system	Whether the term is marked system variable.	Tiny Integer	1

cvterm_relationship Table

Column Name	Description	Type	Size (bytes)
cvterm_relationship_id	Primary key. DB record id.	Integer	4
type_id	Foreign key to the cvterm that defines the type of relationship.	Integer	4
subject_id	Foreign key to the cvterm which defines subject of the relationship.	Integer	4
object_id	Foreign key to the cvterm that defines the object of relationship.	Integer	4

As example lets say we have a cvterm for the variable plant height:

+-----------+--------------+----------------------------------------------+
| cvterm_id | name         | definition                                   |
+-----------+--------------+----------------------------------------------+
|     18020 | Plant_height | Plant height - soil to tip at maturity  (cm) |
+-----------+--------------+----------------------------------------------+

The relationships this term has to other terms are obtained by running the following query:

select 
	cvtr.cvterm_relationship_id, 
	cvtr.subject_id, cvt_subject.name as subject_name, 
    cvtr.type_id as relationship_type_id,  cvt_rel_type.name as relationship_type_name, 
    cvtr.object_id, cvt_object.name as object_name 

from 

	cvterm_relationship cvtr 
    left outer join cvterm cvt_rel_type on cvtr.type_id = cvt_rel_type.cvterm_id 
    left outer join cvterm cvt_subject on cvtr.subject_id = cvt_subject.cvterm_id 
    left outer join cvterm cvt_object on cvtr.object_id = cvt_object.cvterm_id 

where cvtr.subject_id = 18020;

produces following result:

+------------------------+------------+--------------+----------------------+------------------------+-----------+-------------------------+
| cvterm_relationship_id | subject_id | subject_name | relationship_type_id | relationship_type_name | object_id | object_name             |
+------------------------+------------+--------------+----------------------+------------------------+-----------+-------------------------+
|                  18590 |      18020 | Plant_height |                 1220 | has scale              |      6085 | cm                      |
|                  18310 |      18020 | Plant_height |                 1200 | has property           |     15020 | Plant height            |
|                  18450 |      18020 | Plant_height |                 1210 | has method             |     16010 | Soil to tip at maturity |
+------------------------+------------+--------------+----------------------+------------------------+-----------+-------------------------+

which reads: The variable Plant_height measures a property called "Plant Height" has scale "cm" and the method used to measure is "Soil to tip at maturity".

cvtermprop Table

Generic name value pair style properties of a cvterm.

Column Name	Description	Type	Size (bytes)
cvtermprop_id	Primary key. DB record id.	Integer	4
cvterm_id	Foreign key to the cvterm to which this property belongs.	Integer	4
type_id	Foreign key to the cvterm that defines the "Name" of the property.	Integer	4
value	Value of the property.	Text	200
rank	Used to order properties of a single cv term.	Integer	4

cvtermsynonym Table

Synonyms of the cvterms.

Column Name	Description	Type	Size (bytes)
cvtermsynonym_id	Primary key. DB record id.	Integer	4
cvterm_id	Foreign key to the cvterm whose synonym this is.	Integer	4
synonym	The synonym.	Text	200
type_id	Foreign key to the cvterm that defines the type of synonym.	Integer	4

variable_overrides Table

Expected Minimum and/or Maximun of the cvterms.

Column Name	Description	Type	Size (bytes)
id	Primary key. DB record id.	Integer	11
program_uuid	Reference to the Workbench_project	Text	36
cvterm_id	Foreign key to the	Integer	11
alias	The alias.	Text	200
expected_min	Expected Minimun value for the cvterm	Text	200
expected_max	Expected Maximun value for the cvterm	Text	200

formula Table

Formula that is associated to a calculated variable

Column Name	Description	Type	Size (bytes)
formula_id	Primary key. DB record id.	Integer	11
target_variable_id	Foreign key to the cvterm to which this formula generates the value.	Integer	11
definition	Formula itself written in JEXL	Text	255
active	Status	Tinyint	1
name	Name of the formula	Text	255
description	Description	Text	255

formula_input Table

Variables that are input of a defined formula

Column Name	Description	Type	Size (bytes)
formula_id	Foreign key to the formula	Integer	11
variable_id	Foreign key to the cvterm	Integer	11

The Ontology Star

Given the structure described above, the Ontology variables are modelled as "star structure". Following diagram depicts the "star" structure for the Plant Height variable in BMS v3.x versus BMS v4.x: