Description of Schema


Tables

stock
stock_pub
stockprop
stockprop_pub
stock_relationship
stock_relationship_cvterm
stock_relationship_pub
stock_dbxref
stock_cvterm
stock_cvtermprop
stock_genotype
stockcollection
stockcollectionprop
stockcollection_stock
stock_dbxrefprop

stock

Top
Comments:

$Id: stock.sql,v 1.7 2007-03-23 15:18:03 scottcain Exp $
==========================================
Chado stock module
DEPENDENCIES
============
:import cvterm from cv
:import pub from pub
:import dbxref from general
:import organism from organism
:import genotype from genetic
:import contact from contact
================================================
TABLE: stock
================================================
Any stock can be globally identified by the combination of organism, uniquename and stock type. A stock is the physical entities, either living or preserved, held by collections. Stocks belong to a collection; they have IDs, type, organism, description and may have a genotype.
Field Name Data Type Size Default Value Other Foreign Key
stock_id integer 11 PRIMARY KEY, NOT NULL
dbxref_id integer 10 The dbxref_id is an optional primary stable identifier for this stock. Secondary indentifiers and external dbxrefs go in table: stock_dbxref. dbxref.dbxref_id
organism_id integer 10 UNIQUE, The organism_id is the organism to which the stock belongs. This column should only be left blank if the organism cannot be determined. organism.organism_id
name varchar 255 The name is a human-readable local name for a stock.
uniquename text 64000 UNIQUE, NOT NULL
description text 64000 The description is the genetic description provided in the stock list.
type_id integer 10 UNIQUE, NOT NULL, The type_id foreign key links to a controlled vocabulary of stock types. The would include living stock, genomic DNA, preserved specimen. Secondary cvterms for stocks would go in stock_cvterm. cvterm.cvterm_id
is_obsolete boolean 0 false NOT NULL

Indices

Name Fields
stock_name_ind1 name
stock_idx1 dbxref_id
stock_idx2 organism_id
stock_idx3 type_id
stock_idx4 uniquename

Constraints

Type Fields
NOT NULL stock_id
FOREIGN KEY dbxref_id
FOREIGN KEY organism_id
NOT NULL uniquename
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL is_obsolete
UNIQUE organism_id, uniquename, type_id

stock_pub

Top
Comments:

================================================
TABLE: stock_pub
================================================
Provenance. Linking table between stocks and, for example, a stocklist computer file.
Field Name Data Type Size Default Value Other Foreign Key
stock_pub_id integer 11 PRIMARY KEY, NOT NULL
stock_id integer 10 UNIQUE, NOT NULL stock.stock_id
pub_id integer 10 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
stock_pub_idx1 stock_id
stock_pub_idx2 pub_id

Constraints

Type Fields
NOT NULL stock_pub_id
NOT NULL stock_id
FOREIGN KEY stock_id
NOT NULL pub_id
FOREIGN KEY pub_id
UNIQUE stock_id, pub_id

stockprop

Top
Comments:

================================================
TABLE: stockprop
================================================
A stock 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, stockprop_c1, for the combination of stock_id, rank, and type_id. Multivalued property-value pairs must be differentiated by rank.
Field Name Data Type Size Default Value Other Foreign Key
stockprop_id integer 11 PRIMARY KEY, NOT NULL
stock_id integer 10 UNIQUE, NOT NULL stock.stock_id
type_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
stockprop_idx1 stock_id
stockprop_idx2 type_id

Constraints

Type Fields
NOT NULL stockprop_id
NOT NULL stock_id
FOREIGN KEY stock_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL rank
UNIQUE stock_id, type_id, rank

stockprop_pub

Top
Comments:

================================================
TABLE: stockprop_pub
================================================
Provenance. Any stockprop assignment can optionally be supported by a publication.
Field Name Data Type Size Default Value Other Foreign Key
stockprop_pub_id integer 11 PRIMARY KEY, NOT NULL
stockprop_id integer 10 UNIQUE, NOT NULL stockprop.stockprop_id
pub_id integer 10 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
stockprop_pub_idx1 stockprop_id
stockprop_pub_idx2 pub_id

Constraints

Type Fields
NOT NULL stockprop_pub_id
NOT NULL stockprop_id
FOREIGN KEY stockprop_id
NOT NULL pub_id
FOREIGN KEY pub_id
UNIQUE stockprop_id, pub_id

stock_relationship

Top
Comments:

================================================
TABLE: stock_relationship
================================================
Field Name Data Type Size Default Value Other Foreign Key
stock_relationship_id integer 11 PRIMARY KEY, NOT NULL
subject_id integer 10 UNIQUE, NOT NULL, stock_relationship.subject_id is the subject of the subj-predicate-obj sentence. This is typically the substock. stock.stock_id
object_id integer 10 UNIQUE, NOT NULL, stock_relationship.object_id is the object of the subj-predicate-obj sentence. This is typically the container stock. stock.stock_id
type_id integer 10 UNIQUE, NOT NULL, stock_relationship.type_id is relationship type between subject and object. This is a cvterm, typically from the OBO relationship ontology, although other relationship types are allowed. cvterm.cvterm_id
value text 64000 NULL stock_relationship.value is for additional notes or comments.
rank integer 10 0 UNIQUE, NOT NULL, stock_relationship.rank is the ordering of subject stocks with respect to the object stock may be important where rank is used to order these; starts from zero.

Indices

Name Fields
stock_relationship_idx1 subject_id
stock_relationship_idx2 object_id
stock_relationship_idx3 type_id

Constraints

Type Fields
NOT NULL stock_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 rank
UNIQUE subject_id, object_id, type_id, rank

stock_relationship_cvterm

Top
Comments:

================================================
TABLE: stock_relationship_cvterm
================================================
For germplasm maintenance and pedigree data, stock_relationship. type_id will record cvterms such as "is a female parent of", "a parent for mutation", "is a group_id of", "is a source_id of", etc The cvterms for higher categories such as "generative", "derivative" or "maintenance" can be stored in table stock_relationship_cvterm
Field Name Data Type Size Default Value Other Foreign Key
stock_relationship_cvterm_id integer 11 PRIMARY KEY, NOT NULL
stock_relationship_id integer 10 NOT NULL stock_relationship.stock_relationship_id
cvterm_id integer 10 NOT NULL cvterm.cvterm_id
pub_id integer 10 pub.pub_id

Constraints

Type Fields
NOT NULL stock_relationship_cvterm_id
NOT NULL stock_relationship_id
FOREIGN KEY stock_relationship_id
NOT NULL cvterm_id
FOREIGN KEY cvterm_id
FOREIGN KEY pub_id

stock_relationship_pub

Top
Comments:

================================================
TABLE: stock_relationship_pub
================================================
Provenance. Attach optional evidence to a stock_relationship in the form of a publication.
Field Name Data Type Size Default Value Other Foreign Key
stock_relationship_pub_id integer 11 PRIMARY KEY, NOT NULL
stock_relationship_id integer 10 UNIQUE, NOT NULL stock_relationship.stock_relationship_id
pub_id integer 10 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
stock_relationship_pub_idx1 stock_relationship_id
stock_relationship_pub_idx2 pub_id

Constraints

Type Fields
NOT NULL stock_relationship_pub_id
NOT NULL stock_relationship_id
FOREIGN KEY stock_relationship_id
NOT NULL pub_id
FOREIGN KEY pub_id
UNIQUE stock_relationship_id, pub_id

stock_dbxref

Top
Comments:

================================================
TABLE: stock_dbxref
================================================
stock_dbxref links a stock to dbxrefs. This is for secondary identifiers; primary identifiers should use stock.dbxref_id.
Field Name Data Type Size Default Value Other Foreign Key
stock_dbxref_id integer 11 PRIMARY KEY, NOT NULL
stock_id integer 10 UNIQUE, NOT NULL stock.stock_id
dbxref_id integer 10 UNIQUE, NOT NULL dbxref.dbxref_id
is_current boolean 0 true NOT NULL, The is_current boolean indicates whether the linked dbxref is the current -official- dbxref for the linked stock.

Indices

Name Fields
stock_dbxref_idx1 stock_id
stock_dbxref_idx2 dbxref_id

Constraints

Type Fields
NOT NULL stock_dbxref_id
NOT NULL stock_id
FOREIGN KEY stock_id
NOT NULL dbxref_id
FOREIGN KEY dbxref_id
NOT NULL is_current
UNIQUE stock_id, dbxref_id

stock_cvterm

Top
Comments:

================================================
TABLE: stock_cvterm
================================================
stock_cvterm links a stock to cvterms. This is for secondary cvterms; primary cvterms should use stock.type_id.
Field Name Data Type Size Default Value Other Foreign Key
stock_cvterm_id integer 11 PRIMARY KEY, NOT NULL
stock_id integer 10 UNIQUE, NOT NULL stock.stock_id
cvterm_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
pub_id integer 10 UNIQUE, NOT NULL pub.pub_id
is_not boolean 0 false NOT NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
stock_cvterm_idx1 stock_id
stock_cvterm_idx2 cvterm_id
stock_cvterm_idx3 pub_id

Constraints

Type Fields
NOT NULL stock_cvterm_id
NOT NULL stock_id
FOREIGN KEY stock_id
NOT NULL cvterm_id
FOREIGN KEY cvterm_id
NOT NULL pub_id
FOREIGN KEY pub_id
NOT NULL is_not
NOT NULL rank
UNIQUE stock_id, cvterm_id, pub_id, rank

stock_cvtermprop

Top
Comments:

================================================
TABLE: stock_cvtermprop
================================================
Extensible properties for stock to cvterm associations. Examples: GO evidence codes; qualifiers; metadata such as the date on which the entry was curated and the source of the association. See the stockprop table for meanings of type_id, value and rank.
Field Name Data Type Size Default Value Other Foreign Key
stock_cvtermprop_id integer 11 PRIMARY KEY, NOT NULL
stock_cvterm_id integer 10 UNIQUE, NOT NULL stock_cvterm.stock_cvterm_id
type_id integer 10 UNIQUE, NOT NULL, The name of the property/slot is a cvterm. The meaning of the property is defined in that cvterm. cvterms may come from the OBO evidence code cv. cvterm.cvterm_id
value text 64000 NULL The value of the property, represented as text. Numeric values are converted to their text representation. This is less efficient than using native database types, but is easier to query.
rank integer 10 0 UNIQUE, NOT NULL, Property-Value ordering. Any stock_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
stock_cvtermprop_idx1 stock_cvterm_id
stock_cvtermprop_idx2 type_id

Constraints

Type Fields
NOT NULL stock_cvtermprop_id
NOT NULL stock_cvterm_id
FOREIGN KEY stock_cvterm_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL rank
UNIQUE stock_cvterm_id, type_id, rank

stock_genotype

Top
Comments:

================================================
TABLE: stock_genotype
================================================
Simple table linking a stock to a genotype. Features with genotypes can be linked to stocks thru feature_genotype -> genotype -> stock_genotype -> stock.
Field Name Data Type Size Default Value Other Foreign Key
stock_genotype_id integer 11 PRIMARY KEY, NOT NULL
stock_id integer 10 UNIQUE, NOT NULL stock.stock_id
genotype_id integer 10 UNIQUE, NOT NULL genotype.genotype_id

Indices

Name Fields
stock_genotype_idx1 stock_id
stock_genotype_idx2 genotype_id

Constraints

Type Fields
NOT NULL stock_genotype_id
NOT NULL stock_id
FOREIGN KEY stock_id
NOT NULL genotype_id
FOREIGN KEY genotype_id
UNIQUE stock_id, genotype_id

stockcollection

Top
Comments:

================================================
TABLE: stockcollection
================================================
The lab or stock center distributing the stocks in their collection.
Field Name Data Type Size Default Value Other Foreign Key
stockcollection_id integer 11 PRIMARY KEY, NOT NULL
type_id integer 10 UNIQUE, NOT NULL, type_id is the collection type cv. cvterm.cvterm_id
contact_id integer 10 NULL contact_id links to the contact information for the collection. contact.contact_id
name varchar 255 name is the collection.
uniquename text 64000 UNIQUE, NOT NULL, uniqename is the value of the collection cv.

Indices

Name Fields
stockcollection_name_ind1 name
stockcollection_idx1 contact_id
stockcollection_idx2 type_id
stockcollection_idx3 uniquename

Constraints

Type Fields
NOT NULL stockcollection_id
NOT NULL type_id
FOREIGN KEY type_id
FOREIGN KEY contact_id
NOT NULL uniquename
UNIQUE uniquename, type_id

stockcollectionprop

Top
Comments:

================================================
TABLE: stockcollectionprop
================================================
The table stockcollectionprop contains the value of the stock collection such as website/email URLs; the value of the stock collection order URLs.
Field Name Data Type Size Default Value Other Foreign Key
stockcollectionprop_id integer 11 PRIMARY KEY, NOT NULL
stockcollection_id integer 10 UNIQUE, NOT NULL stockcollection.stockcollection_id
type_id integer 10 UNIQUE, NOT NULL, The cv for the type_id is "stockcollection property type". cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
stockcollectionprop_idx1 stockcollection_id
stockcollectionprop_idx2 type_id

Constraints

Type Fields
NOT NULL stockcollectionprop_id
NOT NULL stockcollection_id
FOREIGN KEY stockcollection_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL rank
UNIQUE stockcollection_id, type_id, rank

stockcollection_stock

Top
Comments:

================================================
TABLE: stockcollection_stock
================================================
stockcollection_stock links a stock collection to the stocks which are contained in the collection.
Field Name Data Type Size Default Value Other Foreign Key
stockcollection_stock_id integer 11 PRIMARY KEY, NOT NULL
stockcollection_id integer 10 UNIQUE, NOT NULL stockcollection.stockcollection_id
stock_id integer 10 UNIQUE, NOT NULL stock.stock_id

Indices

Name Fields
stockcollection_stock_idx1 stockcollection_id
stockcollection_stock_idx2 stock_id

Constraints

Type Fields
NOT NULL stockcollection_stock_id
NOT NULL stockcollection_id
FOREIGN KEY stockcollection_id
NOT NULL stock_id
FOREIGN KEY stock_id
UNIQUE stockcollection_id, stock_id

stock_dbxrefprop

Top
Comments:

================================================
TABLE: stock_dbxrefprop
================================================
A stock_dbxref can have any number of slot-value property tags attached to it. This is useful for storing properties related to dbxref annotations of stocks, such as evidence codes, and references, and metadata, such as create/modify dates. This is an alternative to hardcoding a list of columns in the relational schema, and is completely extensible. There is a unique constraint, stock_dbxrefprop_c1, for the combination of stock_dbxref_id, rank, and type_id. Multivalued property-value pairs must be differentiated by rank.
Field Name Data Type Size Default Value Other Foreign Key
stock_dbxrefprop_id integer 11 PRIMARY KEY, NOT NULL
stock_dbxref_id integer 10 UNIQUE, NOT NULL stock_dbxref.stock_dbxref_id
type_id integer 10 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
stock_dbxrefprop_idx1 stock_dbxref_id
stock_dbxrefprop_idx2 type_id

Constraints

Type Fields
NOT NULL stock_dbxrefprop_id
NOT NULL stock_dbxref_id
FOREIGN KEY stock_dbxref_id
NOT NULL type_id
FOREIGN KEY type_id
NOT NULL rank
UNIQUE stock_dbxref_id, type_id, rank

Created by
SQL::Translator 0.11003