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
stockcollection_db
stock_feature
stock_featuremap
stock_library

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 db
:import organism from organism
:import genotype from genetic
:import contact from contact
:import feature from sequence
:import featuremap from map
================================================
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 20 PRIMARY KEY, NOT NULL
dbxref_id integer 20 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 20 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 20 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_ind1name
stock_idx1dbxref_id
stock_idx2organism_id
stock_idx3type_id
stock_idx4uniquename

Constraints

Type Fields
NOT NULLstock_id
FOREIGN KEYdbxref_id
FOREIGN KEYorganism_id
NOT NULLuniquename
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLis_obsolete
UNIQUEorganism_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 20 PRIMARY KEY, NOT NULL
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id
pub_id integer 20 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
stock_pub_idx1stock_id
stock_pub_idx2pub_id

Constraints

Type Fields
NOT NULLstock_pub_id
NOT NULLstock_id
FOREIGN KEYstock_id
NOT NULLpub_id
FOREIGN KEYpub_id
UNIQUEstock_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 20 PRIMARY KEY, NOT NULL
stock_id integer 20 UNIQUE, NOT NULL stock.stock_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
stockprop_idx1stock_id
stockprop_idx2type_id

Constraints

Type Fields
NOT NULLstockprop_id
NOT NULLstock_id
FOREIGN KEYstock_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEstock_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 20 PRIMARY KEY, NOT NULL
stockprop_id integer 20 UNIQUE, NOT NULL stockprop.stockprop_id
pub_id integer 20 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
stockprop_pub_idx1stockprop_id
stockprop_pub_idx2pub_id

Constraints

Type Fields
NOT NULLstockprop_pub_id
NOT NULLstockprop_id
FOREIGN KEYstockprop_id
NOT NULLpub_id
FOREIGN KEYpub_id
UNIQUEstockprop_id, pub_id

stock_relationship

Top
Comments:

================================================
TABLE: stock_relationship
================================================
Field Name Data Type Size Default Value Other Foreign Key
stock_relationship_id integer 20 PRIMARY KEY, NOT NULL
subject_id integer 20 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 20 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 20 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_idx1subject_id
stock_relationship_idx2object_id
stock_relationship_idx3type_id

Constraints

Type Fields
NOT NULLstock_relationship_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLobject_id
FOREIGN KEYobject_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEsubject_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 20 PRIMARY KEY, NOT NULL
stock_relationship_id integer 20 NOT NULL stock_relationship.stock_relationship_id
cvterm_id integer 20 NOT NULL cvterm.cvterm_id
pub_id integer 20 pub.pub_id

Constraints

Type Fields
NOT NULLstock_relationship_cvterm_id
NOT NULLstock_relationship_id
FOREIGN KEYstock_relationship_id
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
FOREIGN KEYpub_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 20 PRIMARY KEY, NOT NULL
stock_relationship_id integer 20 UNIQUE, NOT NULL stock_relationship.stock_relationship_id
pub_id integer 20 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
stock_relationship_pub_idx1stock_relationship_id
stock_relationship_pub_idx2pub_id

Constraints

Type Fields
NOT NULLstock_relationship_pub_id
NOT NULLstock_relationship_id
FOREIGN KEYstock_relationship_id
NOT NULLpub_id
FOREIGN KEYpub_id
UNIQUEstock_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 20 PRIMARY KEY, NOT NULL
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id
dbxref_id integer 20 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_idx1stock_id
stock_dbxref_idx2dbxref_id

Constraints

Type Fields
NOT NULLstock_dbxref_id
NOT NULLstock_id
FOREIGN KEYstock_id
NOT NULLdbxref_id
FOREIGN KEYdbxref_id
NOT NULLis_current
UNIQUEstock_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 20 PRIMARY KEY, NOT NULL
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id
cvterm_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
pub_id integer 20 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_idx1stock_id
stock_cvterm_idx2cvterm_id
stock_cvterm_idx3pub_id

Constraints

Type Fields
NOT NULLstock_cvterm_id
NOT NULLstock_id
FOREIGN KEYstock_id
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
NOT NULLpub_id
FOREIGN KEYpub_id
NOT NULLis_not
NOT NULLrank
UNIQUEstock_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 20 PRIMARY KEY, NOT NULL
stock_cvterm_id integer 20 UNIQUE, NOT NULL stock_cvterm.stock_cvterm_id
type_id integer 20 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_idx1stock_cvterm_id
stock_cvtermprop_idx2type_id

Constraints

Type Fields
NOT NULLstock_cvtermprop_id
NOT NULLstock_cvterm_id
FOREIGN KEYstock_cvterm_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEstock_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 20 PRIMARY KEY, NOT NULL
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id
genotype_id integer 20 UNIQUE, NOT NULL genotype.genotype_id

Indices

Name Fields
stock_genotype_idx1stock_id
stock_genotype_idx2genotype_id

Constraints

Type Fields
NOT NULLstock_genotype_id
NOT NULLstock_id
FOREIGN KEYstock_id
NOT NULLgenotype_id
FOREIGN KEYgenotype_id
UNIQUEstock_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 20 PRIMARY KEY, NOT NULL
type_id integer 20 UNIQUE, NOT NULL, type_id is the collection type cv. cvterm.cvterm_id
contact_id integer 20 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_ind1name
stockcollection_idx1contact_id
stockcollection_idx2type_id
stockcollection_idx3uniquename

Constraints

Type Fields
NOT NULLstockcollection_id
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYcontact_id
NOT NULLuniquename
UNIQUEuniquename, 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 20 PRIMARY KEY, NOT NULL
stockcollection_id integer 20 UNIQUE, NOT NULL stockcollection.stockcollection_id
type_id integer 20 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_idx1stockcollection_id
stockcollectionprop_idx2type_id

Constraints

Type Fields
NOT NULLstockcollectionprop_id
NOT NULLstockcollection_id
FOREIGN KEYstockcollection_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEstockcollection_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 20 PRIMARY KEY, NOT NULL
stockcollection_id integer 20 UNIQUE, NOT NULL stockcollection.stockcollection_id
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id

Indices

Name Fields
stockcollection_stock_idx1stockcollection_id
stockcollection_stock_idx2stock_id

Constraints

Type Fields
NOT NULLstockcollection_stock_id
NOT NULLstockcollection_id
FOREIGN KEYstockcollection_id
NOT NULLstock_id
FOREIGN KEYstock_id
UNIQUEstockcollection_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 20 PRIMARY KEY, NOT NULL
stock_dbxref_id integer 20 UNIQUE, NOT NULL stock_dbxref.stock_dbxref_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
stock_dbxrefprop_idx1stock_dbxref_id
stock_dbxrefprop_idx2type_id

Constraints

Type Fields
NOT NULLstock_dbxrefprop_id
NOT NULLstock_dbxref_id
FOREIGN KEYstock_dbxref_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEstock_dbxref_id, type_id, rank

stockcollection_db

Top
Comments:

================================================
TABLE: stockcollection_db
================================================
Stock collections may be respresented by an external online database. This table associates a stock collection with a database where its member stocks can be found. Individual stock that are part of this collction should have entries in the stock_dbxref table with the same db_id record
Field Name Data Type Size Default Value Other Foreign Key
stockcollection_db_id integer 20 PRIMARY KEY, NOT NULL
stockcollection_id integer 20 UNIQUE, NOT NULL stockcollection.stockcollection_id
db_id integer 20 UNIQUE, NOT NULL db.db_id

Indices

Name Fields
stockcollection_db_idx1stockcollection_id
stockcollection_db_idx2db_id

Constraints

Type Fields
NOT NULLstockcollection_db_id
NOT NULLstockcollection_id
NOT NULLdb_id
UNIQUEstockcollection_id, db_id
FOREIGN KEYdb_id
FOREIGN KEYstockcollection_id

stock_feature

Top
Comments:

================================================
TABLE: stock_feature
================================================
Links a stock to a feature.
Field Name Data Type Size Default Value Other Foreign Key
stock_feature_id integer 20 PRIMARY KEY, NOT NULL
feature_id integer 20 UNIQUE, NOT NULL feature.feature_id
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
stock_feature_idx1stock_feature_id
stock_feature_idx2feature_id
stock_feature_idx3stock_id
stock_feature_idx4type_id

Constraints

Type Fields
NOT NULLstock_feature_id
NOT NULLfeature_id
NOT NULLstock_id
NOT NULLtype_id
NOT NULLrank
FOREIGN KEYfeature_id
FOREIGN KEYstock_id
FOREIGN KEYtype_id
UNIQUEfeature_id, stock_id, type_id, rank

stock_featuremap

Top
Comments:

================================================
TABLE: stock_featuremap
================================================
Links a featuremap to a stock.
Field Name Data Type Size Default Value Other Foreign Key
stock_featuremap_id integer 20 PRIMARY KEY, NOT NULL
featuremap_id integer 20 UNIQUE, NOT NULL featuremap.featuremap_id
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id
type_id integer 20 UNIQUE cvterm.cvterm_id

Indices

Name Fields
stock_featuremap_idx1featuremap_id
stock_featuremap_idx2stock_id
stock_featuremap_idx3type_id

Constraints

Type Fields
NOT NULLstock_featuremap_id
NOT NULLfeaturemap_id
NOT NULLstock_id
FOREIGN KEYfeaturemap_id
FOREIGN KEYstock_id
FOREIGN KEYtype_id
UNIQUEfeaturemap_id, stock_id, type_id

stock_library

Top
Comments:

================================================
TABLE: stock_library
================================================
Links a stock with a library.
Field Name Data Type Size Default Value Other Foreign Key
stock_library_id integer 20 PRIMARY KEY, NOT NULL
library_id integer 20 UNIQUE, NOT NULL library.library_id
stock_id integer 20 UNIQUE, NOT NULL stock.stock_id

Indices

Name Fields
stock_library_idx1library_id
stock_library_idx2stock_id

Constraints

Type Fields
NOT NULLstock_library_id
NOT NULLlibrary_id
NOT NULLstock_id
UNIQUElibrary_id, stock_id
FOREIGN KEYlibrary_id
FOREIGN KEYstock_id

Created by
SQL::Translator 0.11020