Description of Schema

Field Name	Data Type	Size	Default Value	Other
tableinfo_id	integer	11		PRIMARY KEY, NOT NULL
name	varchar	30		UNIQUE, NOT NULL
primary_key_column	varchar	30	NULL
is_view	integer	10	0	NOT NULL
view_on_table_id	integer	10	NULL
superclass_table_id	integer	10	NULL
is_updateable	integer	10	1	NOT NULL
modification_date	date	0	now()	NOT NULL

Constraints

Type	Fields
NOT NULL	tableinfo_id
NOT NULL	name
NOT NULL	is_view
NOT NULL	is_updateable
NOT NULL	modification_date
UNIQUE	name

db

Top

Comments:

================================================
TABLE: db
================================================
A database authority. Typical databases in bioinformatics are FlyBase, GO, UniProt, NCBI, MGI, etc. The authority is generally known by this shortened form, which is unique within the bioinformatics and biomedical realm. To Do - add support for URIs, URNs (e.g. LSIDs). We can do this by treating the URL as a URI - however, some applications may expect this to be resolvable - to be decided.

Field Name	Data Type	Size	Default Value	Other
db_id	integer	11		PRIMARY KEY, NOT NULL
name	varchar	255		UNIQUE, NOT NULL
description	varchar	255	NULL	contact_id int,
urlprefix	varchar	255	NULL
url	varchar	255	NULL

Constraints

Type	Fields
NOT NULL	db_id
NOT NULL	name
UNIQUE	name

dbxref

Top

Comments:

================================================
TABLE: dbxref
================================================
A unique, global, public, stable identifier. Not necessarily an external reference - can reference data items inside the particular chado instance being used. Typically a row in a table can be uniquely identified with a primary identifier (called dbxref_id); a table may also have secondary identifiers (in a linking table _dbxref). A dbxref is generally written as : or as ::.

Field Name	Data Type	Size	Other	Foreign Key
dbxref_id	integer	11	PRIMARY KEY, NOT NULL
db_id	integer	10	UNIQUE, NOT NULL	db.db_id
accession	varchar	255	UNIQUE, NOT NULL, The local part of the identifier. Guaranteed by the db authority to be unique for that db.
version	varchar	255	UNIQUE, NOT NULL
description	text	64000

Indices

Name	Fields
dbxref_idx1	db_id
dbxref_idx2	accession
dbxref_idx3	version

Constraints

Type	Fields
NOT NULL	dbxref_id
NOT NULL	db_id
FOREIGN KEY	db_id
NOT NULL	accession
NOT NULL	version
UNIQUE	db_id, accession, version

cv

Top

Field Name	Data Type	Size	Other
cv_id	integer	11	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	11		PRIMARY KEY, NOT NULL
cv_id	integer	10		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	10		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	Other	Foreign Key
cvterm_relationship_id	integer	11	PRIMARY KEY, NOT NULL
type_id	integer	10	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	10	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	10	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	Other	Foreign Key
cvtermpath_id	integer	11	PRIMARY KEY, NOT NULL
type_id	integer	10	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	10	UNIQUE, NOT NULL	cvterm.cvterm_id
object_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
cv_id	integer	10	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	Other	Foreign Key
cvtermsynonym_id	integer	11	PRIMARY KEY, NOT NULL
cvterm_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
synonym	varchar	1024	UNIQUE, NOT NULL
type_id	integer	10	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	11		PRIMARY KEY, NOT NULL
cvterm_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
dbxref_id	integer	10		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	11		PRIMARY KEY, NOT NULL
cvterm_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
type_id	integer	10		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	11		PRIMARY KEY, NOT NULL
dbxref_id	integer	10		UNIQUE, NOT NULL	dbxref.dbxref_id
type_id	integer	10		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	11		PRIMARY KEY, NOT NULL
cv_id	integer	10		UNIQUE, NOT NULL	cv.cv_id
type_id	integer	10		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	11		PRIMARY KEY, NOT NULL
type_id	integer	10		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

pub

Top

Field Name	Data Type	Size	Default Value	Other	Foreign Key
pub_id	integer	11		PRIMARY KEY, NOT NULL
title	text	64000		Descriptive general heading.
volumetitle	text	64000		Title of part if one of a series.
volume	varchar	255
series_name	varchar	255		Full name of (journal) series.
issue	varchar	255
pyear	varchar	255
pages	varchar	255		Page number range[s], e.g. 457--459, viii + 664pp, lv--lvii.
miniref	varchar	255
uniquename	text	64000		UNIQUE, NOT NULL
type_id	integer	10		NOT NULL, The type of the publication (book, journal, poem, graffiti, etc). Uses pub cv.	cvterm.cvterm_id
is_obsolete	boolean	0	false
publisher	varchar	255
pubplace	varchar	255

Indices

Name	Fields
pub_idx1	type_id

Constraints

Type	Fields
NOT NULL	pub_id
NOT NULL	uniquename
NOT NULL	type_id
FOREIGN KEY	type_id
UNIQUE	uniquename

pub_relationship

Top

Comments:

================================================
TABLE: pub_relationship
================================================
Handle relationships between publications, e.g. when one publication makes others obsolete, when one publication contains errata with respect to other publication(s), or when one publication also appears in another pub.

Field Name	Data Type	Size	Other	Foreign Key
pub_relationship_id	integer	11	PRIMARY KEY, NOT NULL
subject_id	integer	10	UNIQUE, NOT NULL	pub.pub_id
object_id	integer	10	UNIQUE, NOT NULL	pub.pub_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id

Indices

Name	Fields
pub_relationship_idx1	subject_id
pub_relationship_idx2	object_id
pub_relationship_idx3	type_id

Constraints

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

pub_dbxref

Top

Comments:

================================================
TABLE: pub_dbxref
================================================
Handle links to repositories, e.g. Pubmed, Biosis, zoorec, OCLC, Medline, ISSN, coden...

Field Name	Data Type	Size	Default Value	Other	Foreign Key
pub_dbxref_id	integer	11		PRIMARY KEY, NOT NULL
pub_id	integer	10		UNIQUE, NOT NULL	pub.pub_id
dbxref_id	integer	10		UNIQUE, NOT NULL	dbxref.dbxref_id
is_current	boolean	0	true	NOT NULL

Indices

Name	Fields
pub_dbxref_idx1	pub_id
pub_dbxref_idx2	dbxref_id

Constraints

Type	Fields
NOT NULL	pub_dbxref_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
NOT NULL	is_current
UNIQUE	pub_id, dbxref_id

pubauthor

Top

Comments:

================================================
TABLE: pubauthor
================================================
An author for a publication. Note the denormalisation (hence lack of _ in table name) - this is deliberate as it is in general too hard to assign IDs to authors.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
pubauthor_id	integer	11		PRIMARY KEY, NOT NULL
pub_id	integer	10		UNIQUE, NOT NULL	pub.pub_id
rank	integer	10		UNIQUE, NOT NULL, Order of author in author list for this pub - order is important.
editor	boolean	0	false	Indicates whether the author is an editor for linked publication. Note: this is a boolean field but does not follow the normal chado convention for naming booleans.
surname	varchar	100		NOT NULL
givennames	varchar	100		First name, initials
suffix	varchar	100		Jr., Sr., etc

Indices

Name	Fields
pubauthor_idx2	pub_id

Constraints

Type	Fields
NOT NULL	pubauthor_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	rank
NOT NULL	surname
UNIQUE	pub_id, rank

pubprop

Top

Comments:

================================================
TABLE: pubprop
================================================
Property-value pairs for a pub. Follows standard chado pattern.

Field Name	Data Type	Size	Other	Foreign Key
pubprop_id	integer	11	PRIMARY KEY, NOT NULL
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000	NOT NULL
rank	integer	10	UNIQUE

Indices

Name	Fields
pubprop_idx1	pub_id
pubprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	pubprop_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	value
UNIQUE	pub_id, type_id, rank

organism

Top

Comments:

$Id: organism.sql,v 1.19 2007-04-01 18:45:41 briano Exp $
==========================================
Chado organism module
============
DEPENDENCIES
============
:import cvterm from cv
:import dbxref from general
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: organism
================================================
The organismal taxonomic classification. Note that phylogenies are represented using the phylogeny module, and taxonomies can be represented using the cvterm module or the phylogeny module.

Field Name	Data Type	Size	Default Value	Other
organism_id	integer	11		PRIMARY KEY, NOT NULL
abbreviation	varchar	255	NULL
genus	varchar	255		UNIQUE, NOT NULL
species	varchar	255		UNIQUE, NOT NULL, A type of organism is always uniquely identified by genus and species. When mapping from the NCBI taxonomy names.dmp file, this column must be used where it is present, as the common_name column is not always unique (e.g. environmental samples). If a particular strain or subspecies is to be represented, this is appended onto the species name. Follows standard NCBI taxonomy pattern.
common_name	varchar	255	NULL
comment	text	64000	NULL

Constraints

Type	Fields
NOT NULL	organism_id
NOT NULL	genus
NOT NULL	species
UNIQUE	genus, species

organism_dbxref

Top

Comments:

================================================
TABLE: organism_dbxref
================================================

Field Name	Data Type	Size	Other	Foreign Key
organism_dbxref_id	integer	11	PRIMARY KEY, NOT NULL
organism_id	integer	10	UNIQUE, NOT NULL	organism.organism_id
dbxref_id	integer	10	UNIQUE, NOT NULL	dbxref.dbxref_id

Indices

Name	Fields
organism_dbxref_idx1	organism_id
organism_dbxref_idx2	dbxref_id

Constraints

Type	Fields
NOT NULL	organism_dbxref_id
NOT NULL	organism_id
FOREIGN KEY	organism_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
UNIQUE	organism_id, dbxref_id

organismprop

Top

Comments:

================================================
TABLE: organismprop
================================================
Tag-value properties - follows standard chado model.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
organismprop_id	integer	11		PRIMARY KEY, NOT NULL
organism_id	integer	10		UNIQUE, NOT NULL	organism.organism_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
organismprop_idx1	organism_id
organismprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	organismprop_id
NOT NULL	organism_id
FOREIGN KEY	organism_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	organism_id, type_id, rank

feature

Top

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_id	integer	11		PRIMARY KEY, NOT NULL
dbxref_id	integer	10		An optional primary public stable identifier for this feature. Secondary identifiers and external dbxrefs go in the table feature_dbxref.	dbxref.dbxref_id
organism_id	integer	10		UNIQUE, NOT NULL, The organism to which this feature belongs. This column is mandatory.	organism.organism_id
name	varchar	255		The optional human-readable common name for a feature, for display purposes.
uniquename	text	64000		UNIQUE, NOT NULL, The unique name for a feature; may not be necessarily be particularly human-readable, although this is preferred. This name must be unique for this type of feature within this organism.
residues	text	64000		A sequence of alphabetic characters representing biological residues (nucleic acids, amino acids). This column does not need to be manifested for all features; it is optional for features such as exons where the residues can be derived from the featureloc. It is recommended that the value for this column be manifested for features which may may non-contiguous sublocations (e.g. transcripts), since derivation at query time is non-trivial. For expressed sequence, the DNA sequence should be used rather than the RNA sequence. The default storage method for the residues column is EXTERNAL, which will store it uncompressed to make substring operations faster.
seqlen	integer	10		The length of the residue feature. See column:residues. This column is partially redundant with the residues column, and also with featureloc. This column is required because the location may be unknown and the residue sequence may not be manifested, yet it may be desirable to store and query the length of the feature. The seqlen should always be manifested where the length of the sequence is known.
md5checksum	char	32		The 32-character checksum of the sequence, calculated using the MD5 algorithm. This is practically guaranteed to be unique for any feature. This column thus acts as a unique identifier on the mathematical sequence.
type_id	integer	10		UNIQUE, NOT NULL, A required reference to a table:cvterm giving the feature type. This will typically be a Sequence Ontology identifier. This column is thus used to subclass the feature table.	cvterm.cvterm_id
is_analysis	boolean	0	false	NOT NULL, Boolean indicating whether this feature is annotated or the result of an automated analysis. Analysis results also use the companalysis module. Note that the dividing line between analysis and annotation may be fuzzy, this should be determined on a per-project basis in a consistent manner. One requirement is that there should only be one non-analysis version of each wild-type gene feature in a genome, whereas the same gene feature can be predicted multiple times in different analyses.
is_obsolete	boolean	0	false	NOT NULL, Boolean indicating whether this feature has been obsoleted. Some chado instances may choose to simply remove the feature altogether, others may choose to keep an obsolete row in the table.
timeaccessioned	timestamp	0	current_timestamp	NOT NULL, For handling object accession or modification timestamps (as opposed to database auditing data, handled elsewhere). The expectation is that these fields would be available to software interacting with chado.
timelastmodified	timestamp	0	current_timestamp	NOT NULL, For handling object accession or modification timestamps (as opposed to database auditing data, handled elsewhere). The expectation is that these fields would be available to software interacting with chado.

Indices

Name	Fields
feature_name_ind1	name
feature_idx1	dbxref_id
feature_idx2	organism_id
feature_idx3	type_id
feature_idx4	uniquename

Constraints

Type	Fields
NOT NULL	feature_id
FOREIGN KEY	dbxref_id
NOT NULL	organism_id
FOREIGN KEY	organism_id
NOT NULL	uniquename
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	is_analysis
NOT NULL	is_obsolete
NOT NULL	timeaccessioned
NOT NULL	timelastmodified
UNIQUE	organism_id, uniquename, type_id

featureloc

Top

Comments:

COMMENT ON INDEX feature_c1 IS 'Any feature can be globally identified
by the combination of organism, uniquename and feature type';
================================================
TABLE: featureloc
================================================
The location of a feature relative to another feature. Important: interbase coordinates are used. This is vital as it allows us to represent zero-length features e.g. splice sites, insertion points without an awkward fuzzy system. Features typically have exactly ONE location, but this need not be the case. Some features may not be localized (e.g. a gene that has been characterized genetically but no sequence or molecular information is available). Note on multiple locations: Each feature can have 0 or more locations. Multiple locations do NOT indicate non-contiguous locations (if a feature such as a transcript has a non-contiguous location, then the subfeatures such as exons should always be manifested). Instead, multiple featurelocs for a feature designate alternate locations or grouped locations; for instance, a feature designating a blast hit or hsp will have two locations, one on the query feature, one on the subject feature. Features representing sequence variation could have alternate locations instantiated on a feature on the mutant strain. The column:rank is used to differentiate these different locations. Reflexive locations should never be stored - this is for -proper- (i.e. non-self) locations only; nothing should be located relative to itself.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
featureloc_id	integer	11		PRIMARY KEY, NOT NULL
feature_id	integer	10		UNIQUE, NOT NULL, The feature that is being located. Any feature can have zero or more featurelocs.	feature.feature_id
srcfeature_id	integer	10		The source feature which this location is relative to. Every location is relative to another feature (however, this column is nullable, because the srcfeature may not be known). All locations are -proper- that is, nothing should be located relative to itself. No cycles are allowed in the featureloc graph.	feature.feature_id
fmin	integer	10		The leftmost/minimal boundary in the linear range represented by the featureloc. Sometimes (e.g. in Bioperl) this is called -start- although this is confusing because it does not necessarily represent the 5-prime coordinate. Important: This is space-based (interbase) coordinates, counting from zero. To convert this to the leftmost position in a base-oriented system (eg GFF, Bioperl), add 1 to fmin.
is_fmin_partial	boolean	0	false	NOT NULL, This is typically false, but may be true if the value for column:fmin is inaccurate or the leftmost part of the range is unknown/unbounded.
fmax	integer	10		The rightmost/maximal boundary in the linear range represented by the featureloc. Sometimes (e.g. in bioperl) this is called -end- although this is confusing because it does not necessarily represent the 3-prime coordinate. Important: This is space-based (interbase) coordinates, counting from zero. No conversion is required to go from fmax to the rightmost coordinate in a base-oriented system that counts from 1 (e.g. GFF, Bioperl).
is_fmax_partial	boolean	0	false	NOT NULL, This is typically false, but may be true if the value for column:fmax is inaccurate or the rightmost part of the range is unknown/unbounded.
strand	integer	5		The orientation/directionality of the location. Should be 0, -1 or +1.
phase	integer	10		Phase of translation with respect to srcfeature_id. Values are 0, 1, 2. It may not be possible to manifest this column for some features such as exons, because the phase is dependant on the spliceform (the same exon can appear in multiple spliceforms). This column is mostly useful for predicted exons and CDSs.
residue_info	text	64000		Alternative residues, when these differ from feature.residues. For instance, a SNP feature located on a wild and mutant protein would have different alternative residues. for alignment/similarity features, the alternative residues is used to represent the alignment string (CIGAR format). Note on variation features; even if we do not want to instantiate a mutant chromosome/contig feature, we can still represent a SNP etc with 2 locations, one (rank 0) on the genome, the other (rank 1) would have most fields null, except for alternative residues.
locgroup	integer	10	0	UNIQUE, NOT NULL, This is used to manifest redundant, derivable extra locations for a feature. The default locgroup=0 is used for the DIRECT location of a feature. Important: most Chado users may never use featurelocs WITH logroup > 0. Transitively derived locations are indicated with locgroup > 0. For example, the position of an exon on a BAC and in global chromosome coordinates. This column is used to differentiate these groupings of locations. The default locgroup 0 is used for the main or primary location, from which the others can be derived via coordinate transformations. Another example of redundant locations is storing ORF coordinates relative to both transcript and genome. Redundant locations open the possibility of the database getting into inconsistent states; this schema gives us the flexibility of both warehouse instantiations with redundant locations (easier for querying) and management instantiations with no redundant locations. An example of using both locgroup and rank: imagine a feature indicating a conserved region between the chromosomes of two different species. We may want to keep redundant locations on both contigs and chromosomes. We would thus have 4 locations for the single conserved region feature - two distinct locgroups (contig level and chromosome level) and two distinct ranks (for the two species).
rank	integer	10	0	UNIQUE, NOT NULL, Used when a feature has >1 location, otherwise the default rank 0 is used. Some features (e.g. blast hits and HSPs) have two locations - one on the query and one on the subject. Rank is used to differentiate these. Rank=0 is always used for the query, Rank=1 for the subject. For multiple alignments, assignment of rank is arbitrary. Rank is also used for sequence_variant features, such as SNPs. Rank=0 indicates the wildtype (or baseline) feature, Rank=1 indicates the mutant (or compared) feature.

Indices

Name	Fields
featureloc_idx1	feature_id
featureloc_idx2	srcfeature_id
featureloc_idx3	srcfeature_id, fmin, fmax

Constraints

Type	Fields
NOT NULL	featureloc_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
FOREIGN KEY	srcfeature_id
NOT NULL	is_fmin_partial
NOT NULL	is_fmax_partial
NOT NULL	locgroup
NOT NULL	rank
UNIQUE	feature_id, locgroup, rank
CHECK

featureloc_pub

Top

Comments:

COMMENT ON INDEX featureloc_c1 IS 'locgroup and rank serve to uniquely
partition locations for any one feature';
================================================
TABLE: featureloc_pub
================================================
Provenance of featureloc. Linking table between featurelocs and publications that mention them.

Field Name	Data Type	Size	Other	Foreign Key
featureloc_pub_id	integer	11	PRIMARY KEY, NOT NULL
featureloc_id	integer	10	UNIQUE, NOT NULL	featureloc.featureloc_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
featureloc_pub_idx1	featureloc_id
featureloc_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	featureloc_pub_id
NOT NULL	featureloc_id
FOREIGN KEY	featureloc_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	featureloc_id, pub_id

feature_pub

Top

Comments:

================================================
TABLE: feature_pub
================================================
Provenance. Linking table between features and publications that mention them.

Field Name	Data Type	Size	Other	Foreign Key
feature_pub_id	integer	11	PRIMARY KEY, NOT NULL
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
feature_pub_idx1	feature_id
feature_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	feature_pub_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	feature_id, pub_id

feature_pubprop

Top

Comments:

================================================
TABLE: feature_pubprop
================================================
Property or attribute of a feature_pub link.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_pubprop_id	integer	11		PRIMARY KEY, NOT NULL
feature_pub_id	integer	10		UNIQUE, NOT NULL	feature_pub.feature_pub_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
feature_pubprop_idx1	feature_pub_id

Constraints

Type	Fields
NOT NULL	feature_pubprop_id
NOT NULL	feature_pub_id
FOREIGN KEY	feature_pub_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	feature_pub_id, type_id, rank

featureprop

Top

Comments:

================================================
TABLE: featureprop
================================================
A feature 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.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
featureprop_id	integer	11		PRIMARY KEY, NOT NULL
feature_id	integer	10		UNIQUE, NOT NULL	feature.feature_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. Certain property types will only apply to certain feature types (e.g. the anticodon property will only apply to tRNA features) ; the types here come from the sequence feature property ontology.	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 feature 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
featureprop_idx1	feature_id
featureprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	featureprop_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	feature_id, type_id, rank

featureprop_pub

Top

Comments:

For any one feature, multivalued property-value pairs must be differentiated by rank.
================================================
TABLE: featureprop_pub
================================================
Provenance. Any featureprop assignment can optionally be supported by a publication.

Field Name	Data Type	Size	Other	Foreign Key
featureprop_pub_id	integer	11	PRIMARY KEY, NOT NULL
featureprop_id	integer	10	UNIQUE, NOT NULL	featureprop.featureprop_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
featureprop_pub_idx1	featureprop_id
featureprop_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	featureprop_pub_id
NOT NULL	featureprop_id
FOREIGN KEY	featureprop_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	featureprop_id, pub_id

feature_dbxref

Top

Comments:

================================================
TABLE: feature_dbxref
================================================
Links a feature to dbxrefs. This is for secondary identifiers; primary identifiers should use feature.dbxref_id.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_dbxref_id	integer	11		PRIMARY KEY, NOT NULL
feature_id	integer	10		UNIQUE, NOT NULL	feature.feature_id
dbxref_id	integer	10		UNIQUE, NOT NULL	dbxref.dbxref_id
is_current	boolean	0	true	NOT NULL, True if this secondary dbxref is the most up to date accession in the corresponding db. Retired accessions should set this field to false

Indices

Name	Fields
feature_dbxref_idx1	feature_id
feature_dbxref_idx2	dbxref_id

Constraints

Type	Fields
NOT NULL	feature_dbxref_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
NOT NULL	is_current
UNIQUE	feature_id, dbxref_id

feature_relationship

Top

Comments:

================================================
TABLE: feature_relationship
================================================
Features can be arranged in graphs, e.g. "exon part_of transcript part_of gene"; If type is thought of as a verb, the each arc or edge makes a statement [Subject Verb Object]. The object can also be thought of as parent (containing feature), and subject as child (contained feature or subfeature). We include the relationship rank/order, because even though most of the time we can order things implicitly by sequence coordinates, we can not always do this - e.g. transpliced genes. It is also useful for quickly getting implicit introns.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_relationship_id	integer	11		PRIMARY KEY, NOT NULL
subject_id	integer	10		UNIQUE, NOT NULL, The subject of the subj-predicate-obj sentence. This is typically the subfeature.	feature.feature_id
object_id	integer	10		UNIQUE, NOT NULL, The object of the subj-predicate-obj sentence. This is typically the container feature.	feature.feature_id
type_id	integer	10		UNIQUE, NOT NULL, Relationship type between subject and object. This is a cvterm, typically from the OBO relationship ontology, although other relationship types are allowed. The most common relationship type is OBO_REL:part_of. Valid relationship types are constrained by the Sequence Ontology.	cvterm.cvterm_id
value	text	64000	NULL	Additional notes or comments.
rank	integer	10	0	UNIQUE, NOT NULL, The ordering of subject features with respect to the object feature may be important (for example, exon ordering on a transcript - not always derivable if you take trans spliced genes into consideration). Rank is used to order these; starts from zero.

Indices

Name	Fields
feature_relationship_idx1	subject_id
feature_relationship_idx2	object_id
feature_relationship_idx3	type_id

Constraints

Type	Fields
NOT NULL	feature_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

feature_relationship_pub

Top

Comments:

================================================
TABLE: feature_relationship_pub
================================================
Provenance. Attach optional evidence to a feature_relationship in the form of a publication.

Field Name	Data Type	Size	Other	Foreign Key
feature_relationship_pub_id	integer	11	PRIMARY KEY, NOT NULL
feature_relationship_id	integer	10	UNIQUE, NOT NULL	feature_relationship.feature_relationship_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
feature_relationship_pub_idx1	feature_relationship_id
feature_relationship_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	feature_relationship_pub_id
NOT NULL	feature_relationship_id
FOREIGN KEY	feature_relationship_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	feature_relationship_id, pub_id

feature_relationshipprop

Top

Comments:

================================================
TABLE: feature_relationshipprop
================================================
Extensible properties for feature_relationships. Analagous structure to featureprop. This table is largely optional and not used with a high frequency. Typical scenarios may be if one wishes to attach additional data to a feature_relationship - for example to say that the feature_relationship is only true in certain contexts.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_relationshipprop_id	integer	11		PRIMARY KEY, NOT NULL
feature_relationship_id	integer	10		UNIQUE, NOT NULL	feature_relationship.feature_relationship_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. Currently there is no standard ontology for feature_relationship property types.	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 feature_relationship 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
feature_relationshipprop_idx1	feature_relationship_id
feature_relationshipprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	feature_relationshipprop_id
NOT NULL	feature_relationship_id
FOREIGN KEY	feature_relationship_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	feature_relationship_id, type_id, rank

feature_relationshipprop_pub

Top

Comments:

================================================
TABLE: feature_relationshipprop_pub
================================================
Provenance for feature_relationshipprop.

Field Name	Data Type	Size	Other	Foreign Key
feature_relationshipprop_pub_id	integer	11	PRIMARY KEY, NOT NULL
feature_relationshipprop_id	integer	10	UNIQUE, NOT NULL	feature_relationshipprop.feature_relationshipprop_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
feature_relationshipprop_pub_idx1	feature_relationshipprop_id
feature_relationshipprop_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	feature_relationshipprop_pub_id
NOT NULL	feature_relationshipprop_id
FOREIGN KEY	feature_relationshipprop_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	feature_relationshipprop_id, pub_id

feature_cvterm

Top

Comments:

================================================
TABLE: feature_cvterm
================================================
Associate a term from a cv with a feature, for example, GO annotation.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_cvterm_id	integer	11		PRIMARY KEY, NOT NULL
feature_id	integer	10		UNIQUE, NOT NULL	feature.feature_id
cvterm_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
pub_id	integer	10		UNIQUE, NOT NULL, Provenance for the annotation. Each annotation should have a single primary publication (which may be of the appropriate type for computational analyses) where more details can be found. Additional provenance dbxrefs can be attached using feature_cvterm_dbxref.	pub.pub_id
is_not	boolean	0	false	NOT NULL, If this is set to true, then this annotation is interpreted as a NEGATIVE annotation - i.e. the feature does NOT have the specified function, process, component, part, etc. See GO docs for more details.
rank	integer	10	0	UNIQUE, NOT NULL

Indices

Name	Fields
feature_cvterm_idx1	feature_id
feature_cvterm_idx2	cvterm_id
feature_cvterm_idx3	pub_id

Constraints

Type	Fields
NOT NULL	feature_cvterm_id
NOT NULL	feature_id
FOREIGN KEY	feature_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	feature_id, cvterm_id, pub_id, rank

feature_cvtermprop

Top

Comments:

================================================
TABLE: feature_cvtermprop
================================================
Extensible properties for feature 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 featureprop table for meanings of type_id, value and rank.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_cvtermprop_id	integer	11		PRIMARY KEY, NOT NULL
feature_cvterm_id	integer	10		UNIQUE, NOT NULL	feature_cvterm.feature_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 feature_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
feature_cvtermprop_idx1	feature_cvterm_id
feature_cvtermprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	feature_cvtermprop_id
NOT NULL	feature_cvterm_id
FOREIGN KEY	feature_cvterm_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	feature_cvterm_id, type_id, rank

feature_cvterm_dbxref

Top

Comments:

================================================
TABLE: feature_cvterm_dbxref
================================================
Additional dbxrefs for an association. Rows in the feature_cvterm table may be backed up by dbxrefs. For example, a feature_cvterm association that was inferred via a protein-protein interaction may be backed by by refering to the dbxref for the alternate protein. Corresponds to the WITH column in a GO gene association file (but can also be used for other analagous associations). See http://www.geneontology.org/doc/GO.annotation.shtml#file for more details.

Field Name	Data Type	Size	Other	Foreign Key
feature_cvterm_dbxref_id	integer	11	PRIMARY KEY, NOT NULL
feature_cvterm_id	integer	10	UNIQUE, NOT NULL	feature_cvterm.feature_cvterm_id
dbxref_id	integer	10	UNIQUE, NOT NULL	dbxref.dbxref_id

Indices

Name	Fields
feature_cvterm_dbxref_idx1	feature_cvterm_id
feature_cvterm_dbxref_idx2	dbxref_id

Constraints

Type	Fields
NOT NULL	feature_cvterm_dbxref_id
NOT NULL	feature_cvterm_id
FOREIGN KEY	feature_cvterm_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
UNIQUE	feature_cvterm_id, dbxref_id

feature_cvterm_pub

Top

Comments:

================================================
TABLE: feature_cvterm_pub
================================================
Secondary pubs for an association. Each feature_cvterm association is supported by a single primary publication. Additional secondary pubs can be added using this linking table (in a GO gene association file, these corresponding to any IDs after the pipe symbol in the publications column.

Field Name	Data Type	Size	Other	Foreign Key
feature_cvterm_pub_id	integer	11	PRIMARY KEY, NOT NULL
feature_cvterm_id	integer	10	UNIQUE, NOT NULL	feature_cvterm.feature_cvterm_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
feature_cvterm_pub_idx1	feature_cvterm_id
feature_cvterm_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	feature_cvterm_pub_id
NOT NULL	feature_cvterm_id
FOREIGN KEY	feature_cvterm_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	feature_cvterm_id, pub_id

synonym

Top

Comments:

================================================
TABLE: synonym
================================================
A synonym for a feature. One feature can have multiple synonyms, and the same synonym can apply to multiple features.

Field Name	Data Type	Size	Other	Foreign Key
synonym_id	integer	11	PRIMARY KEY, NOT NULL
name	varchar	255	UNIQUE, NOT NULL, The synonym itself. Should be human-readable machine-searchable ascii text.
type_id	integer	10	UNIQUE, NOT NULL, Types would be symbol and fullname for now.	cvterm.cvterm_id
synonym_sgml	varchar	255	NOT NULL, The fully specified synonym, with any non-ascii characters encoded in SGML.

Indices

Name	Fields
synonym_idx1	type_id

Constraints

Type	Fields
NOT NULL	synonym_id
NOT NULL	name
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	synonym_sgml
UNIQUE	name, type_id

feature_synonym

Top

Comments:

================================================
TABLE: feature_synonym
================================================
Linking table between feature and synonym.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_synonym_id	integer	11		PRIMARY KEY, NOT NULL
synonym_id	integer	10		UNIQUE, NOT NULL	synonym.synonym_id
feature_id	integer	10		UNIQUE, NOT NULL	feature.feature_id
pub_id	integer	10		UNIQUE, NOT NULL, The pub_id link is for relating the usage of a given synonym to the publication in which it was used.	pub.pub_id
is_current	boolean	0	false	NOT NULL, The is_current boolean indicates whether the linked synonym is the current -official- symbol for the linked feature.
is_internal	boolean	0	false	NOT NULL, Typically a synonym exists so that somebody querying the db with an obsolete name can find the object theyre looking for (under its current name. If the synonym has been used publicly and deliberately (e.g. in a paper), it may also be listed in reports as a synonym. If the synonym was not used deliberately (e.g. there was a typo which went public), then the is_internal boolean may be set to -true- so that it is known that the synonym is -internal- and should be queryable but should not be listed in reports as a valid synonym.

Indices

Name	Fields
feature_synonym_idx1	synonym_id
feature_synonym_idx2	feature_id
feature_synonym_idx3	pub_id

Constraints

Type	Fields
NOT NULL	feature_synonym_id
NOT NULL	synonym_id
FOREIGN KEY	synonym_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	is_current
NOT NULL	is_internal
UNIQUE	synonym_id, feature_id, pub_id

analysis

Top

Field Name	Data Type	Size	Default Value	Other
analysis_id	integer	11		PRIMARY KEY, NOT NULL
name	varchar	255		A way of grouping analyses. This should be a handy short identifier that can help people find an analysis they want. For instance "tRNAscan", "cDNA", "FlyPep", "SwissProt", and it should not be assumed to be unique. For instance, there may be lots of separate analyses done against a cDNA database.
description	text	64000
program	varchar	255		UNIQUE, NOT NULL, Program name, e.g. blastx, blastp, sim4, genscan.
programversion	varchar	255		UNIQUE, NOT NULL, Version description, e.g. TBLASTX 2.0MP-WashU [09-Nov-2000].
algorithm	varchar	255		Algorithm name, e.g. blast.
sourcename	varchar	255		UNIQUE, Source name, e.g. cDNA, SwissProt.
sourceversion	varchar	255
sourceuri	text	64000		This is an optional, permanent URL or URI for the source of the analysis. The idea is that someone could recreate the analysis directly by going to this URI and fetching the source data (e.g. the blast database, or the training model).
timeexecuted	timestamp	0	current_timestamp	NOT NULL

Constraints

Type	Fields
NOT NULL	analysis_id
NOT NULL	program
NOT NULL	programversion
NOT NULL	timeexecuted
UNIQUE	program, programversion, sourcename

analysisprop

Top

Comments:

================================================
TABLE: analysisprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
analysisprop_id	integer	11		PRIMARY KEY, NOT NULL
analysis_id	integer	10		UNIQUE, NOT NULL	analysis.analysis_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000
rank	integer	10	0	UNIQUE, NOT NULL

Indices

Name	Fields
analysisprop_idx1	analysis_id
analysisprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	analysisprop_id
NOT NULL	analysis_id
FOREIGN KEY	analysis_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	analysis_id, type_id, rank

analysisfeature

Top

Comments:

================================================
TABLE: analysisfeature
================================================
Computational analyses generate features (e.g. Genscan generates transcripts and exons; sim4 alignments generate similarity/match features). analysisfeatures are stored using the feature table from the sequence module. The analysisfeature table is used to decorate these features, with analysis specific attributes. A feature is an analysisfeature if and only if there is a corresponding entry in the analysisfeature table. analysisfeatures will have two or more featureloc entries, with rank indicating query/subject

Field Name	Data Type	Size	Other	Foreign Key
analysisfeature_id	integer	11	PRIMARY KEY, NOT NULL
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id
analysis_id	integer	10	UNIQUE, NOT NULL	analysis.analysis_id
rawscore	float	20	This is the native score generated by the program; for example, the bitscore generated by blast, sim4 or genscan scores. One should not assume that high is necessarily better than low.
normscore	float	20	This is the rawscore but semi-normalized. Complete normalization to allow comparison of features generated by different programs would be nice but too difficult. Instead the normalization should strive to enforce the following semantics: * normscores are floating point numbers >= 0, * high normscores are better than low one. For most programs, it would be sufficient to make the normscore the same as this rawscore, providing these semantics are satisfied.
significance	float	20	This is some kind of expectation or probability metric, representing the probability that the analysis would appear randomly given the model. As such, any program or person querying this table can assume the following semantics: * 0 <= significance <= n, where n is a positive number, theoretically unbounded but unlikely to be more than 10 * low numbers are better than high numbers.
identity	float	20	Percent identity between the locations compared. Note that these 4 metrics do not cover the full range of scores possible; it would be undesirable to list every score possible, as this should be kept extensible. instead, for non-standard scores, use the analysisprop table.

Indices

Name	Fields
analysisfeature_idx1	feature_id
analysisfeature_idx2	analysis_id

Constraints

Type	Fields
NOT NULL	analysisfeature_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	analysis_id
FOREIGN KEY	analysis_id
UNIQUE	feature_id, analysis_id

analysisfeatureprop

Top

Field Name	Data Type	Size	Other	Foreign Key
analysisfeatureprop_id	integer	11	PRIMARY KEY, NOT NULL
analysisfeature_id	integer	10	UNIQUE, NOT NULL	analysisfeature.analysisfeature_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000
rank	integer	10	UNIQUE, NOT NULL

Constraints

Type	Fields
NOT NULL	analysisfeature_id
FOREIGN KEY	analysisfeature_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	analysisfeature_id, type_id, rank
FOREIGN KEY	analysisfeature_id
FOREIGN KEY	type_id

phenotype

Top

Field Name	Data Type	Size	Default Value	Other	Foreign Key
phenotype_id	integer	11		PRIMARY KEY, NOT NULL
uniquename	text	64000		UNIQUE, NOT NULL
name	text	64000	null
observable_id	integer	10		The entity: e.g. anatomy_part, biological_process.	cvterm.cvterm_id
attr_id	integer	10		Phenotypic attribute (quality, property, attribute, character) - drawn from PATO.	cvterm.cvterm_id
value	text	64000		Value of attribute - unconstrained free text. Used only if cvalue_id is not appropriate.
cvalue_id	integer	10		Phenotype attribute value (state).	cvterm.cvterm_id
assay_id	integer	10		Evidence type.	cvterm.cvterm_id

Indices

Name	Fields
phenotype_idx1	cvalue_id
phenotype_idx2	observable_id
phenotype_idx3	attr_id

Constraints

Type	Fields
NOT NULL	phenotype_id
NOT NULL	uniquename
FOREIGN KEY	observable_id
FOREIGN KEY	attr_id
FOREIGN KEY	cvalue_id
FOREIGN KEY	assay_id
UNIQUE	uniquename

phenotype_cvterm

Top

Comments:

================================================
TABLE: phenotype_cvterm
================================================
NULL

Field Name	Data Type	Size	Default Value	Other	Foreign Key
phenotype_cvterm_id	integer	11		PRIMARY KEY, NOT NULL
phenotype_id	integer	10		UNIQUE, NOT NULL	phenotype.phenotype_id
cvterm_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
rank	integer	10	0	UNIQUE, NOT NULL

Indices

Name	Fields
phenotype_cvterm_idx1	phenotype_id
phenotype_cvterm_idx2	cvterm_id

Constraints

Type	Fields
NOT NULL	phenotype_cvterm_id
NOT NULL	phenotype_id
FOREIGN KEY	phenotype_id
NOT NULL	cvterm_id
FOREIGN KEY	cvterm_id
NOT NULL	rank
UNIQUE	phenotype_id, cvterm_id, rank

feature_phenotype

Top

Comments:

================================================
TABLE: feature_phenotype
================================================
NULL

Field Name	Data Type	Size	Other	Foreign Key
feature_phenotype_id	integer	11	PRIMARY KEY, NOT NULL
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id
phenotype_id	integer	10	UNIQUE, NOT NULL	phenotype.phenotype_id

Indices

Name	Fields
feature_phenotype_idx1	feature_id
feature_phenotype_idx2	phenotype_id

Constraints

Type	Fields
NOT NULL	feature_phenotype_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	phenotype_id
FOREIGN KEY	phenotype_id
UNIQUE	feature_id, phenotype_id

genotype

Top

Comments:

$Id: genetic.sql,v 1.31 2008-08-25 19:53:14 scottcain Exp $
==========================================
Chado genetics module
changes 2011-05-31
added type_id to genotype (can be null for backward compatibility)
added genotypeprop table
2006-04-11
split out phenotype tables into phenotype module
redesigned 2003-10-28
changes 2003-11-10:
incorporating suggestions to make everything a gcontext; use
gcontext_relationship to make some gcontexts derivable from others. we
would incorporate environment this way - just add the environment
descriptors as properties of the child gcontext
changes 2004-06 (Documented by DE: 10-MAR-2005):
Many, including rename of gcontext to genotype, split
phenstatement into phenstatement & phenotype, created environment
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
============
DEPENDENCIES
============
:import feature from sequence
:import phenotype from phenotype
:import cvterm from cv
:import pub from pub
:import dbxref from general
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: genotype
================================================
Genetic context. A genotype is defined by a collection of features, mutations, balancers, deficiencies, haplotype blocks, or engineered constructs.

Field Name	Data Type	Size	Other	Foreign Key
genotype_id	integer	11	PRIMARY KEY, NOT NULL
name	text	64000	Optional alternative name for a genotype, for display purposes.
uniquename	text	64000	UNIQUE, NOT NULL, The unique name for a genotype; typically derived from the features making up the genotype.
description	varchar	255
type_id	integer	10	NOT NULL	cvterm.cvterm_id

Indices

Name	Fields
genotype_idx1	uniquename
genotype_idx2	name

Constraints

Type	Fields
NOT NULL	genotype_id
NOT NULL	uniquename
NOT NULL	type_id
FOREIGN KEY	type_id
UNIQUE	uniquename

feature_genotype

Top

Comments:

===============================================
TABLE: feature_genotype
================================================
NULL

Field Name	Data Type	Size	Other	Foreign Key
feature_genotype_id	integer	11	PRIMARY KEY, NOT NULL
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id
genotype_id	integer	10	UNIQUE, NOT NULL	genotype.genotype_id
chromosome_id	integer	10	UNIQUE, A feature of SO type "chromosome".	feature.feature_id
rank	integer	10	UNIQUE, NOT NULL, rank can be used for n-ploid organisms or to preserve order.
cgroup	integer	10	UNIQUE, NOT NULL, Spatially distinguishable group. group can be used for distinguishing the chromosomal groups, for example (RNAi products and so on can be treated as different groups, as they do not fall on a particular chromosome).
cvterm_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id

Indices

Name	Fields
feature_genotype_idx1	feature_id
feature_genotype_idx2	genotype_id

Constraints

Type	Fields
NOT NULL	feature_genotype_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	genotype_id
FOREIGN KEY	genotype_id
FOREIGN KEY	chromosome_id
NOT NULL	rank
NOT NULL	cgroup
NOT NULL	cvterm_id
FOREIGN KEY	cvterm_id
UNIQUE	feature_id, genotype_id, cvterm_id, chromosome_id, rank, cgroup

environment

Top

Comments:

================================================
TABLE: environment
================================================
The environmental component of a phenotype description.

Field Name	Data Type	Size	Other
environment_id	integer	11	PRIMARY KEY, NOT NULL
uniquename	text	64000	UNIQUE, NOT NULL
description	text	64000

Indices

Name	Fields
environment_idx1	uniquename

Constraints

Type	Fields
NOT NULL	environment_id
NOT NULL	uniquename
UNIQUE	uniquename

environment_cvterm

Top

Comments:

================================================
TABLE: environment_cvterm
================================================
NULL

Field Name	Data Type	Size	Other	Foreign Key
environment_cvterm_id	integer	11	PRIMARY KEY, NOT NULL
environment_id	integer	10	UNIQUE, NOT NULL	environment.environment_id
cvterm_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id

Indices

Name	Fields
environment_cvterm_idx1	environment_id
environment_cvterm_idx2	cvterm_id

Constraints

Type	Fields
NOT NULL	environment_cvterm_id
NOT NULL	environment_id
FOREIGN KEY	environment_id
NOT NULL	cvterm_id
FOREIGN KEY	cvterm_id
UNIQUE	environment_id, cvterm_id

phenstatement

Top

Comments:

================================================
TABLE: phenstatement
================================================
Phenotypes are things like "larval lethal". Phenstatements are things like "dpp-1 is recessive larval lethal". So essentially phenstatement is a linking table expressing the relationship between genotype, environment, and phenotype.

Field Name	Data Type	Size	Other	Foreign Key
phenstatement_id	integer	11	PRIMARY KEY, NOT NULL
genotype_id	integer	10	UNIQUE, NOT NULL	genotype.genotype_id
environment_id	integer	10	UNIQUE, NOT NULL	environment.environment_id
phenotype_id	integer	10	UNIQUE, NOT NULL	phenotype.phenotype_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
phenstatement_idx1	genotype_id
phenstatement_idx2	phenotype_id

Constraints

Type	Fields
NOT NULL	phenstatement_id
NOT NULL	genotype_id
FOREIGN KEY	genotype_id
NOT NULL	environment_id
FOREIGN KEY	environment_id
NOT NULL	phenotype_id
FOREIGN KEY	phenotype_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	genotype_id, phenotype_id, environment_id, type_id, pub_id

phendesc

Top

Comments:

================================================
TABLE: phendesc
================================================
A summary of a _set_ of phenotypic statements for any one gcontext made in any one publication.

Field Name	Data Type	Size	Other	Foreign Key
phendesc_id	integer	11	PRIMARY KEY, NOT NULL
genotype_id	integer	10	UNIQUE, NOT NULL	genotype.genotype_id
environment_id	integer	10	UNIQUE, NOT NULL	environment.environment_id
description	text	64000	NOT NULL
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
phendesc_idx1	genotype_id
phendesc_idx2	environment_id
phendesc_idx3	pub_id

Constraints

Type	Fields
NOT NULL	phendesc_id
NOT NULL	genotype_id
FOREIGN KEY	genotype_id
NOT NULL	environment_id
FOREIGN KEY	environment_id
NOT NULL	description
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	genotype_id, environment_id, type_id, pub_id

phenotype_comparison

Top

Comments:

================================================
TABLE: phenotype_comparison
================================================
Comparison of phenotypes e.g., genotype1/environment1/phenotype1 "non-suppressible" with respect to genotype2/environment2/phenotype2.

Field Name	Data Type	Size	Other	Foreign Key
phenotype_comparison_id	integer	11	PRIMARY KEY, NOT NULL
genotype1_id	integer	10	UNIQUE, NOT NULL	genotype.genotype_id
environment1_id	integer	10	UNIQUE, NOT NULL	environment.environment_id
genotype2_id	integer	10	UNIQUE, NOT NULL	genotype.genotype_id
environment2_id	integer	10	UNIQUE, NOT NULL	environment.environment_id
phenotype1_id	integer	10	UNIQUE, NOT NULL	phenotype.phenotype_id
phenotype2_id	integer	10		phenotype.phenotype_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id
organism_id	integer	10	NOT NULL	organism.organism_id

Indices

Name	Fields
phenotype_comparison_idx1	genotype1_id
phenotype_comparison_idx2	genotype2_id
phenotype_comparison_idx4	pub_id

Constraints

Type	Fields
NOT NULL	phenotype_comparison_id
NOT NULL	genotype1_id
FOREIGN KEY	genotype1_id
NOT NULL	environment1_id
FOREIGN KEY	environment1_id
NOT NULL	genotype2_id
FOREIGN KEY	genotype2_id
NOT NULL	environment2_id
FOREIGN KEY	environment2_id
NOT NULL	phenotype1_id
FOREIGN KEY	phenotype1_id
FOREIGN KEY	phenotype2_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	organism_id
FOREIGN KEY	organism_id
UNIQUE	genotype1_id, environment1_id, genotype2_id, environment2_id, phenotype1_id, pub_id

phenotype_comparison_cvterm

Top

Comments:

================================================
TABLE: phenotype_comparison_cvterm
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
phenotype_comparison_cvterm_id	integer	11		PRIMARY KEY, NOT NULL
phenotype_comparison_id	integer	10		UNIQUE, NOT NULL	phenotype_comparison.phenotype_comparison_id
cvterm_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
pub_id	integer	10		NOT NULL	pub.pub_id
rank	integer	10	0	NOT NULL

Indices

Name	Fields
phenotype_comparison_cvterm_idx1	phenotype_comparison_id
phenotype_comparison_cvterm_idx2	cvterm_id

Constraints

Type	Fields
NOT NULL	phenotype_comparison_cvterm_id
NOT NULL	phenotype_comparison_id
FOREIGN KEY	phenotype_comparison_id
NOT NULL	cvterm_id
FOREIGN KEY	cvterm_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	rank
UNIQUE	phenotype_comparison_id, cvterm_id

genotypeprop

Top

Comments:

================================================
TABLE: genotypeprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
genotypeprop_id	integer	11		PRIMARY KEY, NOT NULL
genotype_id	integer	10		UNIQUE, NOT NULL	genotype.genotype_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
genotypeprop_idx1	genotype_id
genotypeprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	genotypeprop_id
NOT NULL	genotype_id
FOREIGN KEY	genotype_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	genotype_id, type_id, rank

featuremap

Top

Comments:

$Id: map.sql,v 1.14 2007-03-23 15:18:02 scottcain Exp $
==========================================
Chado map module
=================================================================
Dependencies:
:import feature from sequence
:import cvterm from cv
:import pub from pub
=================================================================
================================================
TABLE: featuremap
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
featuremap_id	integer	11		PRIMARY KEY, NOT NULL
name	varchar	255		UNIQUE
description	text	64000
unittype_id	integer	10	NULL		cvterm.cvterm_id

Constraints

Type	Fields
NOT NULL	featuremap_id
FOREIGN KEY	unittype_id
UNIQUE	name

featurerange

Top

Comments:

================================================
TABLE: featurerange
================================================
In cases where the start and end of a mapped feature is a range, leftendf and rightstartf are populated. leftstartf_id, leftendf_id, rightstartf_id, rightendf_id are the ids of features with respect to which the feature is being mapped. These may be cytological bands.

Field Name	Data Type	Size	Other	Foreign Key
featurerange_id	integer	11	PRIMARY KEY, NOT NULL
featuremap_id	integer	10	NOT NULL, featuremap_id is the id of the feature being mapped.	featuremap.featuremap_id
feature_id	integer	10	NOT NULL	feature.feature_id
leftstartf_id	integer	10	NOT NULL	feature.feature_id
leftendf_id	integer	10		feature.feature_id
rightstartf_id	integer	10		feature.feature_id
rightendf_id	integer	10	NOT NULL	feature.feature_id
rangestr	varchar	255

Indices

Name	Fields
featurerange_idx1	featuremap_id
featurerange_idx2	feature_id
featurerange_idx3	leftstartf_id
featurerange_idx4	leftendf_id
featurerange_idx5	rightstartf_id
featurerange_idx6	rightendf_id

Constraints

Type	Fields
NOT NULL	featurerange_id
NOT NULL	featuremap_id
FOREIGN KEY	featuremap_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	leftstartf_id
FOREIGN KEY	leftstartf_id
FOREIGN KEY	leftendf_id
FOREIGN KEY	rightstartf_id
NOT NULL	rightendf_id
FOREIGN KEY	rightendf_id

featurepos

Top

Comments:

================================================
TABLE: featurepos
================================================

Field Name	Data Type	Size	Other	Foreign Key
featurepos_id	integer	11	PRIMARY KEY, NOT NULL
featuremap_id	integer	11	NOT NULL	featuremap.featuremap_id
feature_id	integer	10	NOT NULL	feature.feature_id
map_feature_id	integer	10	NOT NULL, map_feature_id links to the feature (map) upon which the feature is being localized.	feature.feature_id
mappos	float	20	NOT NULL

Indices

Name	Fields
featurepos_idx1	featuremap_id
featurepos_idx2	feature_id
featurepos_idx3	map_feature_id

Constraints

Type	Fields
NOT NULL	featurepos_id
NOT NULL	featuremap_id
FOREIGN KEY	featuremap_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	map_feature_id
FOREIGN KEY	map_feature_id
NOT NULL	mappos

featuremap_pub

Top

Comments:

================================================
TABLE: featuremap_pub
================================================

Field Name	Data Type	Size	Other	Foreign Key
featuremap_pub_id	integer	11	PRIMARY KEY, NOT NULL
featuremap_id	integer	10	NOT NULL	featuremap.featuremap_id
pub_id	integer	10	NOT NULL	pub.pub_id

Indices

Name	Fields
featuremap_pub_idx1	featuremap_id
featuremap_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	featuremap_pub_id
NOT NULL	featuremap_id
FOREIGN KEY	featuremap_id
NOT NULL	pub_id
FOREIGN KEY	pub_id

phylotree

Top

Comments:

$Id: phylogeny.sql,v 1.11 2007-04-12 17:00:30 briano Exp $
==========================================
Chado phylogenetics module
Richard Bruskiewich
Chris Mungall
Initial design: 2004-05-27
============
DEPENDENCIES
============
:import feature from sequence
:import cvterm from cv
:import pub from pub
:import organism from organism
:import dbxref from general
:import analysis from companalysis
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: phylotree
================================================
Global anchor for phylogenetic tree.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
phylotree_id	integer	11		PRIMARY KEY, UNIQUE, NOT NULL
dbxref_id	integer	10		NOT NULL	dbxref.dbxref_id
name	varchar	255	NULL
type_id	integer	10		Type: protein, nucleotide, taxonomy, for example. The type should be any SO type, or "taxonomy".	cvterm.cvterm_id
analysis_id	integer	10	NULL		analysis.analysis_id
comment	text	64000	NULL

Indices

Name	Fields
phylotree_idx1	phylotree_id

Constraints

Type	Fields
NOT NULL	phylotree_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
FOREIGN KEY	type_id
FOREIGN KEY	analysis_id
UNIQUE	phylotree_id

phylotree_pub

Top

Comments:

================================================
TABLE: phylotree_pub
================================================
Tracks citations global to the tree e.g. multiple sequence alignment supporting tree construction.

Field Name	Data Type	Size	Other	Foreign Key
phylotree_pub_id	integer	11	PRIMARY KEY, NOT NULL
phylotree_id	integer	10	UNIQUE, NOT NULL	phylotree.phylotree_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
phylotree_pub_idx1	phylotree_id
phylotree_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	phylotree_pub_id
NOT NULL	phylotree_id
FOREIGN KEY	phylotree_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	phylotree_id, pub_id

phylonode

Top

Comments:

================================================
TABLE: phylonode
================================================
Bootstrap float null.
This is the most pervasive element in the phylogeny module, cataloging the "phylonodes" of tree graphs. Edges are implied by the parent_phylonode_id reflexive closure. For all nodes in a nested set implementation the left and right index will be *between* the parents left and right indexes.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
phylonode_id	integer	11		PRIMARY KEY, NOT NULL
phylotree_id	integer	10		UNIQUE, NOT NULL	phylotree.phylotree_id
parent_phylonode_id	integer	10	NULL	Root phylonode can have null parent_phylonode_id value.	phylonode.phylonode_id
left_idx	integer	10		UNIQUE, NOT NULL
right_idx	integer	10		UNIQUE, NOT NULL
type_id	integer	10		Type: e.g. root, interior, leaf.	cvterm.cvterm_id
feature_id	integer	10		Phylonodes can have optional features attached to them e.g. a protein or nucleotide sequence usually attached to a leaf of the phylotree for non-leaf nodes, the feature may be a feature that is an instance of SO:match; this feature is the alignment of all leaf features beneath it.	feature.feature_id
label	varchar	255	NULL
distance	float	20	NULL

Constraints

Type	Fields
NOT NULL	phylonode_id
NOT NULL	phylotree_id
FOREIGN KEY	phylotree_id
FOREIGN KEY	parent_phylonode_id
NOT NULL	left_idx
NOT NULL	right_idx
FOREIGN KEY	type_id
FOREIGN KEY	feature_id
UNIQUE	phylotree_id, left_idx
UNIQUE	phylotree_id, right_idx

phylonode_dbxref

Top

Comments:

================================================
TABLE: phylonode_dbxref
================================================
For example, for orthology, paralogy group identifiers; could also be used for NCBI taxonomy; for sequences, refer to phylonode_feature, feature associated dbxrefs.

Field Name	Data Type	Size	Other	Foreign Key
phylonode_dbxref_id	integer	11	PRIMARY KEY, NOT NULL
phylonode_id	integer	10	UNIQUE, NOT NULL	phylonode.phylonode_id
dbxref_id	integer	10	UNIQUE, NOT NULL	dbxref.dbxref_id

Indices

Name	Fields
phylonode_dbxref_idx1	phylonode_id
phylonode_dbxref_idx2	dbxref_id

Constraints

Type	Fields
NOT NULL	phylonode_dbxref_id
NOT NULL	phylonode_id
FOREIGN KEY	phylonode_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
UNIQUE	phylonode_id, dbxref_id

phylonode_pub

Top

Comments:

================================================
TABLE: phylonode_pub
================================================

Field Name	Data Type	Size	Other	Foreign Key
phylonode_pub_id	integer	11	PRIMARY KEY, NOT NULL
phylonode_id	integer	10	UNIQUE, NOT NULL	phylonode.phylonode_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
phylonode_pub_idx1	phylonode_id
phylonode_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	phylonode_pub_id
NOT NULL	phylonode_id
FOREIGN KEY	phylonode_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	phylonode_id, pub_id

phylonode_organism

Top

Comments:

================================================
TABLE: phylonode_organism
================================================
This linking table should only be used for nodes in taxonomy trees; it provides a mapping between the node and an organism. One node can have zero or one organisms, one organism can have zero or more nodes (although typically it should only have one in the standard NCBI taxonomy tree).

Field Name	Data Type	Size	Other	Foreign Key
phylonode_organism_id	integer	11	PRIMARY KEY, NOT NULL
phylonode_id	integer	10	UNIQUE, NOT NULL, One phylonode cannot refer to >1 organism.	phylonode.phylonode_id
organism_id	integer	10	NOT NULL	organism.organism_id

Indices

Name	Fields
phylonode_organism_idx1	phylonode_id
phylonode_organism_idx2	organism_id

Constraints

Type	Fields
NOT NULL	phylonode_organism_id
NOT NULL	phylonode_id
FOREIGN KEY	phylonode_id
NOT NULL	organism_id
FOREIGN KEY	organism_id
UNIQUE	phylonode_id

phylonodeprop

Top

Comments:

================================================
TABLE: phylonodeprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
phylonodeprop_id	integer	11		PRIMARY KEY, NOT NULL
phylonode_id	integer	10		UNIQUE, NOT NULL	phylonode.phylonode_id
type_id	integer	10		UNIQUE, NOT NULL, type_id could designate phylonode hierarchy relationships, for example: species taxonomy (kingdom, order, family, genus, species), "ortholog/paralog", "fold/superfold", etc.	cvterm.cvterm_id
value	text	64000		UNIQUE, NOT NULL
rank	integer	10	0	UNIQUE, NOT NULL, It is not clear how useful the rank concept is here, leave it in for now.

Indices

Name	Fields
phylonodeprop_idx1	phylonode_id
phylonodeprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	phylonodeprop_id
NOT NULL	phylonode_id
FOREIGN KEY	phylonode_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	value
NOT NULL	rank
UNIQUE	phylonode_id, type_id, value, rank

phylonode_relationship

Top

Comments:

================================================
TABLE: phylonode_relationship
================================================
This is for relationships that are not strictly hierarchical; for example, horizontal gene transfer. Most phylogenetic trees are strictly hierarchical, nevertheless it is here for completeness.

Field Name	Data Type	Size	Other	Foreign Key
phylonode_relationship_id	integer	11	PRIMARY KEY, NOT NULL
subject_id	integer	10	UNIQUE, NOT NULL	phylonode.phylonode_id
object_id	integer	10	UNIQUE, NOT NULL	phylonode.phylonode_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
rank	integer	10
phylotree_id	integer	10	NOT NULL	phylotree.phylotree_id

Indices

Name	Fields
phylonode_relationship_idx1	subject_id
phylonode_relationship_idx2	object_id
phylonode_relationship_idx3	type_id

Constraints

Type	Fields
NOT NULL	phylonode_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	phylotree_id
FOREIGN KEY	phylotree_id
UNIQUE	subject_id, object_id, type_id

contact

Top

Comments:

$Id: contact.sql,v 1.5 2007-02-25 17:00:17 briano Exp $
==========================================
Chado contact module
=================================================================
Dependencies:
:import cvterm from cv
=================================================================
================================================
TABLE: contact
================================================
Model persons, institutes, groups, organizations, etc.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
contact_id	integer	11		PRIMARY KEY, NOT NULL
type_id	integer	10	NULL	What type of contact is this? E.g. "person", "lab".	cvterm.cvterm_id
name	varchar	255		UNIQUE, NOT NULL
description	varchar	255	NULL

Constraints

Type	Fields
NOT NULL	contact_id
FOREIGN KEY	type_id
NOT NULL	name
UNIQUE	name

contact_relationship

Top

Comments:

================================================
TABLE: contact_relationship
================================================
Model relationships between contacts

Field Name	Data Type	Size	Other	Foreign Key
contact_relationship_id	integer	11	PRIMARY KEY, NOT NULL
type_id	integer	10	UNIQUE, NOT NULL, 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
subject_id	integer	10	UNIQUE, NOT NULL, The subject of the subj-predicate-obj sentence. In a DAG, this corresponds to the child node.	contact.contact_id
object_id	integer	10	UNIQUE, NOT NULL, The object of the subj-predicate-obj sentence. In a DAG, this corresponds to the parent node.	contact.contact_id

Indices

Name	Fields
contact_relationship_idx1	type_id
contact_relationship_idx2	subject_id
contact_relationship_idx3	object_id

Constraints

Type	Fields
NOT NULL	contact_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

expression

Top

Field Name	Data Type	Size	Other
expression_id	integer	11	PRIMARY KEY, NOT NULL
uniquename	text	64000	UNIQUE, NOT NULL
md5checksum	char	32
description	text	64000

Constraints

Type	Fields
NOT NULL	expression_id
NOT NULL	uniquename
UNIQUE	uniquename

expression_cvterm

Top

Comments:

================================================
TABLE: expression_cvterm
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
expression_cvterm_id	integer	11		PRIMARY KEY, NOT NULL
expression_id	integer	10		UNIQUE, NOT NULL	expression.expression_id
cvterm_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
rank	integer	10	0	NOT NULL
cvterm_type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id

Indices

Name	Fields
expression_cvterm_idx1	expression_id
expression_cvterm_idx2	cvterm_id
expression_cvterm_idx3	cvterm_type_id

Constraints

Type	Fields
NOT NULL	expression_cvterm_id
NOT NULL	expression_id
FOREIGN KEY	expression_id
NOT NULL	cvterm_id
FOREIGN KEY	cvterm_id
NOT NULL	rank
NOT NULL	cvterm_type_id
FOREIGN KEY	cvterm_type_id
UNIQUE	expression_id, cvterm_id, cvterm_type_id

expression_cvtermprop

Top

Comments:

================================================
TABLE: expression_cvtermprop
================================================
Extensible properties for expression to cvterm associations. Examples: qualifiers.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
expression_cvtermprop_id	integer	11		PRIMARY KEY, NOT NULL
expression_cvterm_id	integer	10		UNIQUE, NOT NULL	expression_cvterm.expression_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. For example, cvterms may come from the FlyBase miscellaneous 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 expression_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
expression_cvtermprop_idx1	expression_cvterm_id
expression_cvtermprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	expression_cvtermprop_id
NOT NULL	expression_cvterm_id
FOREIGN KEY	expression_cvterm_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	expression_cvterm_id, type_id, rank

expressionprop

Top

Comments:

================================================
TABLE: expressionprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
expressionprop_id	integer	11		PRIMARY KEY, NOT NULL
expression_id	integer	10		UNIQUE, NOT NULL	expression.expression_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
expressionprop_idx1	expression_id
expressionprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	expressionprop_id
NOT NULL	expression_id
FOREIGN KEY	expression_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	expression_id, type_id, rank

expression_pub

Top

Comments:

================================================
TABLE: expression_pub
================================================

Field Name	Data Type	Size	Other	Foreign Key
expression_pub_id	integer	11	PRIMARY KEY, NOT NULL
expression_id	integer	10	UNIQUE, NOT NULL	expression.expression_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
expression_pub_idx1	expression_id
expression_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	expression_pub_id
NOT NULL	expression_id
FOREIGN KEY	expression_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	expression_id, pub_id

feature_expression

Top

Comments:

================================================
TABLE: feature_expression
================================================

Field Name	Data Type	Size	Other	Foreign Key
feature_expression_id	integer	11	PRIMARY KEY, NOT NULL
expression_id	integer	10	UNIQUE, NOT NULL	expression.expression_id
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
feature_expression_idx1	expression_id
feature_expression_idx2	feature_id
feature_expression_idx3	pub_id

Constraints

Type	Fields
NOT NULL	feature_expression_id
NOT NULL	expression_id
FOREIGN KEY	expression_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	expression_id, feature_id, pub_id

feature_expressionprop

Top

Comments:

================================================
TABLE: feature_expressionprop
================================================
Extensible properties for feature_expression (comments, for example). Modeled on feature_cvtermprop.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
feature_expressionprop_id	integer	11		PRIMARY KEY, NOT NULL
feature_expression_id	integer	10		UNIQUE, NOT NULL	feature_expression.feature_expression_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
feature_expressionprop_idx1	feature_expression_id
feature_expressionprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	feature_expressionprop_id
NOT NULL	feature_expression_id
FOREIGN KEY	feature_expression_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	feature_expression_id, type_id, rank

eimage

Top

Comments:

================================================
TABLE: eimage
================================================

Field Name	Data Type	Size	Other
eimage_id	integer	11	PRIMARY KEY, NOT NULL
eimage_data	text	64000	We expect images in eimage_data (e.g. JPEGs) to be uuencoded.
eimage_type	varchar	255	NOT NULL, Describes the type of data in eimage_data.
image_uri	varchar	255

Constraints

Type	Fields
NOT NULL	eimage_id
NOT NULL	eimage_type

expression_image

Top

Comments:

================================================
TABLE: expression_image
================================================

Field Name	Data Type	Size	Other	Foreign Key
expression_image_id	integer	11	PRIMARY KEY, NOT NULL
expression_id	integer	10	UNIQUE, NOT NULL	expression.expression_id
eimage_id	integer	10	UNIQUE, NOT NULL	eimage.eimage_id

Indices

Name	Fields
expression_image_idx1	expression_id
expression_image_idx2	eimage_id

Constraints

Type	Fields
NOT NULL	expression_image_id
NOT NULL	expression_id
FOREIGN KEY	expression_id
NOT NULL	eimage_id
FOREIGN KEY	eimage_id
UNIQUE	expression_id, eimage_id

project

Top

Comments:

=================================================================
Dependencies:
:import cvterm from cv
:import pub from pub
:import contact from contact
=================================================================
================================================
TABLE: project
================================================
NULL

Field Name	Data Type	Size	Other
project_id	integer	11	PRIMARY KEY, NOT NULL
name	varchar	255	UNIQUE, NOT NULL
description	varchar	255	NOT NULL

Constraints

Type	Fields
NOT NULL	project_id
NOT NULL	name
NOT NULL	description
UNIQUE	name

projectprop

Top

Comments:

================================================
TABLE: projectprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
projectprop_id	integer	11		PRIMARY KEY, NOT NULL
project_id	integer	10		UNIQUE, NOT NULL	project.project_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000
rank	integer	10	0	UNIQUE, NOT NULL

Constraints

Type	Fields
NOT NULL	projectprop_id
NOT NULL	project_id
FOREIGN KEY	project_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	project_id, type_id, rank

project_relationship

Top

Comments:

================================================
TABLE: project_relationship
================================================
A project can be composed of several smaller scale projects

Field Name	Data Type	Size	Other	Foreign Key
project_relationship_id	integer	11	PRIMARY KEY, NOT NULL
subject_project_id	integer	10	UNIQUE, NOT NULL	project.project_id
object_project_id	integer	10	UNIQUE, NOT NULL	project.project_id
type_id	integer	10	UNIQUE, NOT NULL, The type of relationship being stated, such as "is part of".	cvterm.cvterm_id

Constraints

Type	Fields
NOT NULL	project_relationship_id
NOT NULL	subject_project_id
FOREIGN KEY	subject_project_id
NOT NULL	object_project_id
FOREIGN KEY	object_project_id
NOT NULL	type_id
FOREIGN KEY	type_id
UNIQUE	subject_project_id, object_project_id, type_id

project_pub

Top

Comments:

Linking project(s) to publication(s)

Field Name	Data Type	Size	Other	Foreign Key
project_pub_id	integer	11	PRIMARY KEY, NOT NULL
project_id	integer	10	UNIQUE, NOT NULL	project.project_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
project_pub_idx1	project_id
project_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	project_pub_id
NOT NULL	project_id
FOREIGN KEY	project_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	project_id, pub_id

project_contact

Top

Comments:

Linking project(s) to contact(s)

Field Name	Data Type	Size	Other	Foreign Key
project_contact_id	integer	11	PRIMARY KEY, NOT NULL
project_id	integer	10	UNIQUE, NOT NULL	project.project_id
contact_id	integer	10	UNIQUE, NOT NULL	contact.contact_id

Indices

Name	Fields
project_contact_idx1	project_id
project_contact_idx2	contact_id

Constraints

Type	Fields
NOT NULL	project_contact_id
NOT NULL	project_id
FOREIGN KEY	project_id
NOT NULL	contact_id
FOREIGN KEY	contact_id
UNIQUE	project_id, contact_id

mageml

Top

Field Name	Data Type	Size	Other
mageml_id	integer	11	PRIMARY KEY, NOT NULL
mage_package	text	64000	NOT NULL
mage_ml	text	64000	NOT NULL

Constraints

Type	Fields
NOT NULL	mageml_id
NOT NULL	mage_package
NOT NULL	mage_ml

magedocumentation

Top

Comments:

================================================
TABLE: magedocumentation
================================================
NULL

Field Name	Data Type	Size	Other	Foreign Key
magedocumentation_id	integer	11	PRIMARY KEY, NOT NULL
mageml_id	integer	10	NOT NULL	mageml.mageml_id
tableinfo_id	integer	10	NOT NULL	tableinfo.tableinfo_id
row_id	integer	10	NOT NULL
mageidentifier	text	64000	NOT NULL

Indices

Name	Fields
magedocumentation_idx1	mageml_id
magedocumentation_idx2	tableinfo_id
magedocumentation_idx3	row_id

Constraints

Type	Fields
NOT NULL	magedocumentation_id
NOT NULL	mageml_id
FOREIGN KEY	mageml_id
NOT NULL	tableinfo_id
FOREIGN KEY	tableinfo_id
NOT NULL	row_id
NOT NULL	mageidentifier

protocol

Top

Comments:

================================================
TABLE: protocol
================================================
Procedural notes on how data was prepared and processed.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
protocol_id	integer	11		PRIMARY KEY, NOT NULL
type_id	integer	10		NOT NULL	cvterm.cvterm_id
pub_id	integer	10	NULL		pub.pub_id
dbxref_id	integer	10	NULL		dbxref.dbxref_id
name	text	64000		UNIQUE, NOT NULL
uri	text	64000	NULL
protocoldescription	text	64000	NULL
hardwaredescription	text	64000	NULL
softwaredescription	text	64000	NULL

Indices

Name	Fields
protocol_idx1	type_id
protocol_idx2	pub_id
protocol_idx3	dbxref_id

Constraints

Type	Fields
NOT NULL	protocol_id
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	pub_id
FOREIGN KEY	dbxref_id
NOT NULL	name
UNIQUE	name

protocolparam

Top

Comments:

================================================
TABLE: protocolparam
================================================
Parameters related to a protocol. For example, if the protocol is a soak, this might include attributes of bath temperature and duration.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
protocolparam_id	integer	11		PRIMARY KEY, NOT NULL
protocol_id	integer	10		NOT NULL	protocol.protocol_id
name	text	64000		NOT NULL
datatype_id	integer	10	NULL		cvterm.cvterm_id
unittype_id	integer	10	NULL		cvterm.cvterm_id
value	text	64000	NULL
rank	integer	10	0	NOT NULL

Indices

Name	Fields
protocolparam_idx1	protocol_id
protocolparam_idx2	datatype_id
protocolparam_idx3	unittype_id

Constraints

Type	Fields
NOT NULL	protocolparam_id
NOT NULL	protocol_id
FOREIGN KEY	protocol_id
NOT NULL	name
FOREIGN KEY	datatype_id
FOREIGN KEY	unittype_id
NOT NULL	rank

channel

Top

Comments:

================================================
TABLE: channel
================================================
Different array platforms can record signals from one or more channels (cDNA arrays typically use two CCD, but Affymetrix uses only one).

Field Name	Data Type	Size	Other
channel_id	integer	11	PRIMARY KEY, NOT NULL
name	text	64000	UNIQUE, NOT NULL
definition	text	64000	NOT NULL

Constraints

Type	Fields
NOT NULL	channel_id
NOT NULL	name
NOT NULL	definition
UNIQUE	name

arraydesign

Top

Comments:

================================================
TABLE: arraydesign
================================================
General properties about an array. An array is a template used to generate physical slides, etc. It contains layout information, as well as global array properties, such as material (glass, nylon) and spot dimensions (in rows/columns).

Field Name	Data Type	Size	Default Value	Other	Foreign Key
arraydesign_id	integer	11		PRIMARY KEY, NOT NULL
manufacturer_id	integer	10		NOT NULL	contact.contact_id
platformtype_id	integer	10		NOT NULL	cvterm.cvterm_id
substratetype_id	integer	10	NULL		cvterm.cvterm_id
protocol_id	integer	10	NULL		protocol.protocol_id
dbxref_id	integer	10	NULL		dbxref.dbxref_id
name	text	64000		UNIQUE, NOT NULL
version	text	64000	NULL
description	text	64000	NULL
array_dimensions	text	64000	NULL
element_dimensions	text	64000	NULL
num_of_elements	integer	10	NULL
num_array_columns	integer	10	NULL
num_array_rows	integer	10	NULL
num_grid_columns	integer	10	NULL
num_grid_rows	integer	10	NULL
num_sub_columns	integer	10	NULL
num_sub_rows	integer	10	NULL

Indices

Name	Fields
arraydesign_idx1	manufacturer_id
arraydesign_idx2	platformtype_id
arraydesign_idx3	substratetype_id
arraydesign_idx4	protocol_id
arraydesign_idx5	dbxref_id

Constraints

Type	Fields
NOT NULL	arraydesign_id
NOT NULL	manufacturer_id
FOREIGN KEY	manufacturer_id
NOT NULL	platformtype_id
FOREIGN KEY	platformtype_id
FOREIGN KEY	substratetype_id
FOREIGN KEY	protocol_id
FOREIGN KEY	dbxref_id
NOT NULL	name
UNIQUE	name

arraydesignprop

Top

Comments:

================================================
TABLE: arraydesignprop
================================================
Extra array design properties that are not accounted for in arraydesign.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
arraydesignprop_id	integer	11		PRIMARY KEY, NOT NULL
arraydesign_id	integer	10		UNIQUE, NOT NULL	arraydesign.arraydesign_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
arraydesignprop_idx1	arraydesign_id
arraydesignprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	arraydesignprop_id
NOT NULL	arraydesign_id
FOREIGN KEY	arraydesign_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	arraydesign_id, type_id, rank

assay

Top

Comments:

================================================
TABLE: assay
================================================
An assay consists of a physical instance of an array, combined with the conditions used to create the array (protocols, technician information). The assay can be thought of as a hybridization.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
assay_id	integer	11		PRIMARY KEY, NOT NULL
arraydesign_id	integer	10		NOT NULL	arraydesign.arraydesign_id
protocol_id	integer	10	NULL		protocol.protocol_id
assaydate	timestamp	0	current_timestamp
arrayidentifier	text	64000	NULL
arraybatchidentifier	text	64000	NULL
operator_id	integer	10		NOT NULL	contact.contact_id
dbxref_id	integer	10	NULL		dbxref.dbxref_id
name	text	64000	NULL	UNIQUE
description	text	64000	NULL

Indices

Name	Fields
assay_idx1	arraydesign_id
assay_idx2	protocol_id
assay_idx3	operator_id
assay_idx4	dbxref_id

Constraints

Type	Fields
NOT NULL	assay_id
NOT NULL	arraydesign_id
FOREIGN KEY	arraydesign_id
FOREIGN KEY	protocol_id
NOT NULL	operator_id
FOREIGN KEY	operator_id
FOREIGN KEY	dbxref_id
UNIQUE	name

assayprop

Top

Comments:

================================================
TABLE: assayprop
================================================
Extra assay properties that are not accounted for in assay.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
assayprop_id	integer	11		PRIMARY KEY, NOT NULL
assay_id	integer	10		UNIQUE, NOT NULL	assay.assay_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
assayprop_idx1	assay_id
assayprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	assayprop_id
NOT NULL	assay_id
FOREIGN KEY	assay_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	assay_id, type_id, rank

assay_project

Top

Comments:

================================================
TABLE: assay_project
================================================
Link assays to projects.

Field Name	Data Type	Size	Other	Foreign Key
assay_project_id	integer	11	PRIMARY KEY, NOT NULL
assay_id	integer	10	UNIQUE, NOT NULL	assay.assay_id
project_id	integer	10	UNIQUE, NOT NULL	project.project_id

Indices

Name	Fields
assay_project_idx1	assay_id
assay_project_idx2	project_id

Constraints

Type	Fields
NOT NULL	assay_project_id
NOT NULL	assay_id
FOREIGN KEY	assay_id
NOT NULL	project_id
FOREIGN KEY	project_id
UNIQUE	assay_id, project_id

biomaterial

Top

Comments:

================================================
TABLE: biomaterial
================================================
A biomaterial represents the MAGE concept of BioSource, BioSample, and LabeledExtract. It is essentially some biological material (tissue, cells, serum) that may have been processed. Processed biomaterials should be traceable back to raw biomaterials via the biomaterialrelationship table.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
biomaterial_id	integer	11		PRIMARY KEY, NOT NULL
taxon_id	integer	10	NULL		organism.organism_id
biosourceprovider_id	integer	10	NULL		contact.contact_id
dbxref_id	integer	10	NULL		dbxref.dbxref_id
name	text	64000	NULL	UNIQUE
description	text	64000	NULL

Indices

Name	Fields
biomaterial_idx1	taxon_id
biomaterial_idx2	biosourceprovider_id
biomaterial_idx3	dbxref_id

Constraints

Type	Fields
NOT NULL	biomaterial_id
FOREIGN KEY	taxon_id
FOREIGN KEY	biosourceprovider_id
FOREIGN KEY	dbxref_id
UNIQUE	name

biomaterial_relationship

Top

Comments:

================================================
TABLE: biomaterial_relationship
================================================
Relate biomaterials to one another. This is a way to track a series of treatments or material splits/merges, for instance.

Field Name	Data Type	Size	Other	Foreign Key
biomaterial_relationship_id	integer	11	PRIMARY KEY, NOT NULL
subject_id	integer	10	UNIQUE, NOT NULL	biomaterial.biomaterial_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
object_id	integer	10	UNIQUE, NOT NULL	biomaterial.biomaterial_id

Indices

Name	Fields
biomaterial_relationship_idx1	subject_id
biomaterial_relationship_idx2	object_id
biomaterial_relationship_idx3	type_id

Constraints

Type	Fields
NOT NULL	biomaterial_relationship_id
NOT NULL	subject_id
FOREIGN KEY	subject_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	object_id
FOREIGN KEY	object_id
UNIQUE	subject_id, object_id, type_id

biomaterialprop

Top

Comments:

================================================
TABLE: biomaterialprop
================================================
Extra biomaterial properties that are not accounted for in biomaterial.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
biomaterialprop_id	integer	11		PRIMARY KEY, NOT NULL
biomaterial_id	integer	10		UNIQUE, NOT NULL	biomaterial.biomaterial_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
biomaterialprop_idx1	biomaterial_id
biomaterialprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	biomaterialprop_id
NOT NULL	biomaterial_id
FOREIGN KEY	biomaterial_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	biomaterial_id, type_id, rank

biomaterial_dbxref

Top

Comments:

================================================
TABLE: biomaterial_dbxref
================================================

Field Name	Data Type	Size	Other	Foreign Key
biomaterial_dbxref_id	integer	11	PRIMARY KEY, NOT NULL
biomaterial_id	integer	10	UNIQUE, NOT NULL	biomaterial.biomaterial_id
dbxref_id	integer	10	UNIQUE, NOT NULL	dbxref.dbxref_id

Indices

Name	Fields
biomaterial_dbxref_idx1	biomaterial_id
biomaterial_dbxref_idx2	dbxref_id

Constraints

Type	Fields
NOT NULL	biomaterial_dbxref_id
NOT NULL	biomaterial_id
FOREIGN KEY	biomaterial_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
UNIQUE	biomaterial_id, dbxref_id

treatment

Top

Comments:

================================================
TABLE: treatment
================================================
A biomaterial may undergo multiple treatments. Examples of treatments: apoxia, fluorophore and biotin labeling.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
treatment_id	integer	11		PRIMARY KEY, NOT NULL
rank	integer	10	0	NOT NULL
biomaterial_id	integer	10		NOT NULL	biomaterial.biomaterial_id
type_id	integer	10		NOT NULL	cvterm.cvterm_id
protocol_id	integer	10	NULL		protocol.protocol_id
name	text	64000	NULL

Indices

Name	Fields
treatment_idx1	biomaterial_id
treatment_idx2	type_id
treatment_idx3	protocol_id

Constraints

Type	Fields
NOT NULL	treatment_id
NOT NULL	rank
NOT NULL	biomaterial_id
FOREIGN KEY	biomaterial_id
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	protocol_id

biomaterial_treatment

Top

Comments:

================================================
TABLE: biomaterial_treatment
================================================
Link biomaterials to treatments. Treatments have an order of operations (rank), and associated measurements (unittype_id, value).

Field Name	Data Type	Size	Default Value	Other	Foreign Key
biomaterial_treatment_id	integer	11		PRIMARY KEY, NOT NULL
biomaterial_id	integer	10		UNIQUE, NOT NULL	biomaterial.biomaterial_id
treatment_id	integer	10		UNIQUE, NOT NULL	treatment.treatment_id
unittype_id	integer	10	NULL		cvterm.cvterm_id
value	float	15	NULL
rank	integer	10	0	NOT NULL

Indices

Name	Fields
biomaterial_treatment_idx1	biomaterial_id
biomaterial_treatment_idx2	treatment_id
biomaterial_treatment_idx3	unittype_id

Constraints

Type	Fields
NOT NULL	biomaterial_treatment_id
NOT NULL	biomaterial_id
FOREIGN KEY	biomaterial_id
NOT NULL	treatment_id
FOREIGN KEY	treatment_id
FOREIGN KEY	unittype_id
NOT NULL	rank
UNIQUE	biomaterial_id, treatment_id

assay_biomaterial

Top

Comments:

================================================
TABLE: assay_biomaterial
================================================
A biomaterial can be hybridized many times (technical replicates), or combined with other biomaterials in a single hybridization (for two-channel arrays).

Field Name	Data Type	Size	Default Value	Other	Foreign Key
assay_biomaterial_id	integer	11		PRIMARY KEY, NOT NULL
assay_id	integer	10		UNIQUE, NOT NULL	assay.assay_id
biomaterial_id	integer	10		UNIQUE, NOT NULL	biomaterial.biomaterial_id
channel_id	integer	10	NULL	UNIQUE	channel.channel_id
rank	integer	10	0	UNIQUE, NOT NULL

Indices

Name	Fields
assay_biomaterial_idx1	assay_id
assay_biomaterial_idx2	biomaterial_id
assay_biomaterial_idx3	channel_id

Constraints

Type	Fields
NOT NULL	assay_biomaterial_id
NOT NULL	assay_id
FOREIGN KEY	assay_id
NOT NULL	biomaterial_id
FOREIGN KEY	biomaterial_id
FOREIGN KEY	channel_id
NOT NULL	rank
UNIQUE	assay_id, biomaterial_id, channel_id, rank

acquisition

Top

Comments:

================================================
TABLE: acquisition
================================================
This represents the scanning of hybridized material. The output of this process is typically a digital image of an array.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
acquisition_id	integer	11		PRIMARY KEY, NOT NULL
assay_id	integer	10		NOT NULL	assay.assay_id
protocol_id	integer	10	NULL		protocol.protocol_id
channel_id	integer	10	NULL		channel.channel_id
acquisitiondate	timestamp	0	current_timestamp
name	text	64000	NULL	UNIQUE
uri	text	64000	NULL

Indices

Name	Fields
acquisition_idx1	assay_id
acquisition_idx2	protocol_id
acquisition_idx3	channel_id

Constraints

Type	Fields
NOT NULL	acquisition_id
NOT NULL	assay_id
FOREIGN KEY	assay_id
FOREIGN KEY	protocol_id
FOREIGN KEY	channel_id
UNIQUE	name

acquisitionprop

Top

Comments:

================================================
TABLE: acquisitionprop
================================================
Parameters associated with image acquisition.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
acquisitionprop_id	integer	11		PRIMARY KEY, NOT NULL
acquisition_id	integer	10		UNIQUE, NOT NULL	acquisition.acquisition_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
acquisitionprop_idx1	acquisition_id
acquisitionprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	acquisitionprop_id
NOT NULL	acquisition_id
FOREIGN KEY	acquisition_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	acquisition_id, type_id, rank

acquisition_relationship

Top

Comments:

================================================
TABLE: acquisition_relationship
================================================
Multiple monochrome images may be merged to form a multi-color image. Red-green images of 2-channel hybridizations are an example of this.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
acquisition_relationship_id	integer	11		PRIMARY KEY, NOT NULL
subject_id	integer	10		UNIQUE, NOT NULL	acquisition.acquisition_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
object_id	integer	10		UNIQUE, NOT NULL	acquisition.acquisition_id
value	text	64000	NULL
rank	integer	10	0	UNIQUE, NOT NULL

Indices

Name	Fields
acquisition_relationship_idx1	subject_id
acquisition_relationship_idx2	type_id
acquisition_relationship_idx3	object_id

Constraints

Type	Fields
NOT NULL	acquisition_relationship_id
NOT NULL	subject_id
FOREIGN KEY	subject_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	object_id
FOREIGN KEY	object_id
NOT NULL	rank
UNIQUE	subject_id, object_id, type_id, rank

quantification

Top

Comments:

================================================
TABLE: quantification
================================================
Quantification is the transformation of an image acquisition to numeric data. This typically involves statistical procedures.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
quantification_id	integer	11		PRIMARY KEY, NOT NULL
acquisition_id	integer	10		NOT NULL	acquisition.acquisition_id
operator_id	integer	10	NULL		contact.contact_id
protocol_id	integer	10	NULL		protocol.protocol_id
analysis_id	integer	10		UNIQUE, NOT NULL	analysis.analysis_id
quantificationdate	timestamp	0	current_timestamp
name	text	64000	NULL	UNIQUE
uri	text	64000	NULL

Indices

Name	Fields
quantification_idx1	acquisition_id
quantification_idx2	operator_id
quantification_idx3	protocol_id
quantification_idx4	analysis_id

Constraints

Type	Fields
NOT NULL	quantification_id
NOT NULL	acquisition_id
FOREIGN KEY	acquisition_id
FOREIGN KEY	operator_id
FOREIGN KEY	protocol_id
NOT NULL	analysis_id
FOREIGN KEY	analysis_id
UNIQUE	name, analysis_id

quantificationprop

Top

Comments:

================================================
TABLE: quantificationprop
================================================
Extra quantification properties that are not accounted for in quantification.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
quantificationprop_id	integer	11		PRIMARY KEY, NOT NULL
quantification_id	integer	10		UNIQUE, NOT NULL	quantification.quantification_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
quantificationprop_idx1	quantification_id
quantificationprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	quantificationprop_id
NOT NULL	quantification_id
FOREIGN KEY	quantification_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	quantification_id, type_id, rank

quantification_relationship

Top

Comments:

================================================
TABLE: quantification_relationship
================================================
There may be multiple rounds of quantification, this allows us to keep an audit trail of what values went where.

Field Name	Data Type	Size	Other	Foreign Key
quantification_relationship_id	integer	11	PRIMARY KEY, NOT NULL
subject_id	integer	10	UNIQUE, NOT NULL	quantification.quantification_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
object_id	integer	10	UNIQUE, NOT NULL	quantification.quantification_id

Indices

Name	Fields
quantification_relationship_idx1	subject_id
quantification_relationship_idx2	type_id
quantification_relationship_idx3	object_id

Constraints

Type	Fields
NOT NULL	quantification_relationship_id
NOT NULL	subject_id
FOREIGN KEY	subject_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	object_id
FOREIGN KEY	object_id
UNIQUE	subject_id, object_id, type_id

control

Top

Comments:

================================================
TABLE: control
================================================
NULL

Field Name	Data Type	Size	Default Value	Other	Foreign Key
control_id	integer	11		PRIMARY KEY, NOT NULL
type_id	integer	10		NOT NULL	cvterm.cvterm_id
assay_id	integer	10		NOT NULL	assay.assay_id
tableinfo_id	integer	10		NOT NULL	tableinfo.tableinfo_id
row_id	integer	10		NOT NULL
name	text	64000	NULL
value	text	64000	NULL
rank	integer	10	0	NOT NULL

Indices

Name	Fields
control_idx1	type_id
control_idx2	assay_id
control_idx3	tableinfo_id
control_idx4	row_id

Constraints

Type	Fields
NOT NULL	control_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	assay_id
FOREIGN KEY	assay_id
NOT NULL	tableinfo_id
FOREIGN KEY	tableinfo_id
NOT NULL	row_id
NOT NULL	rank

element

Top

Comments:

================================================
TABLE: element
================================================
Represents a feature of the array. This is typically a region of the array coated or bound to DNA.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
element_id	integer	11		PRIMARY KEY, NOT NULL
feature_id	integer	10	NULL	UNIQUE	feature.feature_id
arraydesign_id	integer	10		UNIQUE, NOT NULL	arraydesign.arraydesign_id
type_id	integer	10	NULL		cvterm.cvterm_id
dbxref_id	integer	10	NULL		dbxref.dbxref_id

Indices

Name	Fields
element_idx1	feature_id
element_idx2	arraydesign_id
element_idx3	type_id
element_idx4	dbxref_id

Constraints

Type	Fields
NOT NULL	element_id
FOREIGN KEY	feature_id
NOT NULL	arraydesign_id
FOREIGN KEY	arraydesign_id
FOREIGN KEY	type_id
FOREIGN KEY	dbxref_id
UNIQUE	feature_id, arraydesign_id

elementresult

Top

Comments:

================================================
TABLE: element_result
================================================
An element on an array produces a measurement when hybridized to a biomaterial (traceable through quantification_id). This is the base data from which tables that actually contain data inherit.

Field Name	Data Type	Size	Other	Foreign Key
elementresult_id	integer	11	PRIMARY KEY, NOT NULL
element_id	integer	10	UNIQUE, NOT NULL	element.element_id
quantification_id	integer	10	UNIQUE, NOT NULL	quantification.quantification_id
signal	float	20	NOT NULL

Indices

Name	Fields
elementresult_idx1	element_id
elementresult_idx2	quantification_id
elementresult_idx3	signal

Constraints

Type	Fields
NOT NULL	elementresult_id
NOT NULL	element_id
FOREIGN KEY	element_id
NOT NULL	quantification_id
FOREIGN KEY	quantification_id
NOT NULL	signal
UNIQUE	element_id, quantification_id

element_relationship

Top

Comments:

================================================
TABLE: element_relationship
================================================
Sometimes we want to combine measurements from multiple elements to get a composite value. Affymetrix combines many probes to form a probeset measurement, for instance.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
element_relationship_id	integer	11		PRIMARY KEY, NOT NULL
subject_id	integer	10		UNIQUE, NOT NULL	element.element_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
object_id	integer	10		UNIQUE, NOT NULL	element.element_id
value	text	64000	NULL
rank	integer	10	0	UNIQUE, NOT NULL

Indices

Name	Fields
element_relationship_idx1	subject_id
element_relationship_idx2	type_id
element_relationship_idx3	object_id
element_relationship_idx4	value

Constraints

Type	Fields
NOT NULL	element_relationship_id
NOT NULL	subject_id
FOREIGN KEY	subject_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	object_id
FOREIGN KEY	object_id
NOT NULL	rank
UNIQUE	subject_id, object_id, type_id, rank

elementresult_relationship

Top

Comments:

================================================
TABLE: elementresult_relationship
================================================
Sometimes we want to combine measurements from multiple elements to get a composite value. Affymetrix combines many probes to form a probeset measurement, for instance.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
elementresult_relationship_id	integer	11		PRIMARY KEY, NOT NULL
subject_id	integer	10		UNIQUE, NOT NULL	elementresult.elementresult_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
object_id	integer	10		UNIQUE, NOT NULL	elementresult.elementresult_id
value	text	64000	NULL
rank	integer	10	0	UNIQUE, NOT NULL

Indices

Name	Fields
elementresult_relationship_idx1	subject_id
elementresult_relationship_idx2	type_id
elementresult_relationship_idx3	object_id
elementresult_relationship_idx4	value

Constraints

Type	Fields
NOT NULL	elementresult_relationship_id
NOT NULL	subject_id
FOREIGN KEY	subject_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	object_id
FOREIGN KEY	object_id
NOT NULL	rank
UNIQUE	subject_id, object_id, type_id, rank

study

Top

Comments:

================================================
TABLE: study
================================================
NULL

Field Name	Data Type	Size	Default Value	Other	Foreign Key
study_id	integer	11		PRIMARY KEY, NOT NULL
contact_id	integer	10		NOT NULL	contact.contact_id
pub_id	integer	10	NULL		pub.pub_id
dbxref_id	integer	10	NULL		dbxref.dbxref_id
name	text	64000		UNIQUE, NOT NULL
description	text	64000	NULL

Indices

Name	Fields
study_idx1	contact_id
study_idx2	pub_id
study_idx3	dbxref_id

Constraints

Type	Fields
NOT NULL	study_id
NOT NULL	contact_id
FOREIGN KEY	contact_id
FOREIGN KEY	pub_id
FOREIGN KEY	dbxref_id
NOT NULL	name
UNIQUE	name

study_assay

Top

Comments:

================================================
TABLE: study_assay
================================================
NULL

Field Name	Data Type	Size	Other	Foreign Key
study_assay_id	integer	11	PRIMARY KEY, NOT NULL
study_id	integer	10	UNIQUE, NOT NULL	study.study_id
assay_id	integer	10	UNIQUE, NOT NULL	assay.assay_id

Indices

Name	Fields
study_assay_idx1	study_id
study_assay_idx2	assay_id

Constraints

Type	Fields
NOT NULL	study_assay_id
NOT NULL	study_id
FOREIGN KEY	study_id
NOT NULL	assay_id
FOREIGN KEY	assay_id
UNIQUE	study_id, assay_id

studydesign

Top

Comments:

================================================
TABLE: studydesign
================================================
NULL

Field Name	Data Type	Size	Default Value	Other	Foreign Key
studydesign_id	integer	11		PRIMARY KEY, NOT NULL
study_id	integer	10		NOT NULL	study.study_id
description	text	64000	NULL

Indices

Name	Fields
studydesign_idx1	study_id

Constraints

Type	Fields
NOT NULL	studydesign_id
NOT NULL	study_id
FOREIGN KEY	study_id

studydesignprop

Top

Comments:

================================================
TABLE: studydesignprop
================================================
NULL

Field Name	Data Type	Size	Default Value	Other	Foreign Key
studydesignprop_id	integer	11		PRIMARY KEY, NOT NULL
studydesign_id	integer	10		UNIQUE, NOT NULL	studydesign.studydesign_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
studydesignprop_idx1	studydesign_id
studydesignprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	studydesignprop_id
NOT NULL	studydesign_id
FOREIGN KEY	studydesign_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	studydesign_id, type_id, rank

studyfactor

Top

Comments:

================================================
TABLE: studyfactor
================================================
NULL

Field Name	Data Type	Size	Default Value	Other	Foreign Key
studyfactor_id	integer	11		PRIMARY KEY, NOT NULL
studydesign_id	integer	10		NOT NULL	studydesign.studydesign_id
type_id	integer	10	NULL		cvterm.cvterm_id
name	text	64000		NOT NULL
description	text	64000	NULL

Indices

Name	Fields
studyfactor_idx1	studydesign_id
studyfactor_idx2	type_id

Constraints

Type	Fields
NOT NULL	studyfactor_id
NOT NULL	studydesign_id
FOREIGN KEY	studydesign_id
FOREIGN KEY	type_id
NOT NULL	name

studyfactorvalue

Top

Comments:

================================================
TABLE: studyfactorvalue
================================================
NULL

Field Name	Data Type	Size	Default Value	Other	Foreign Key
studyfactorvalue_id	integer	11		PRIMARY KEY, NOT NULL
studyfactor_id	integer	10		NOT NULL	studyfactor.studyfactor_id
assay_id	integer	10		NOT NULL	assay.assay_id
factorvalue	text	64000	NULL
name	text	64000	NULL
rank	integer	10	0	NOT NULL

Indices

Name	Fields
studyfactorvalue_idx1	studyfactor_id
studyfactorvalue_idx2	assay_id

Constraints

Type	Fields
NOT NULL	studyfactorvalue_id
NOT NULL	studyfactor_id
FOREIGN KEY	studyfactor_id
NOT NULL	assay_id
FOREIGN KEY	assay_id
NOT NULL	rank

studyprop

Top

Comments:

studyprop and studyprop_feature added for Kara Dolinski's group
Here is her description of it:
Both of the tables are used for our YFGdb project
(http://yfgdb.princeton.edu/), which uses chado.
Here is how we use those tables, using the following example:
http://yfgdb.princeton.edu/cgi-bin/display.cgi?db=pmid&id=15575969
The above data set is represented as a row in the STUDY table. We have
lots of attributes that we want to store about each STUDY (status, etc)
and in the official schema, the only prop table we could use was the
STUDYDESIGN_PROP table. This forced us to go through the STUDYDESIGN
table when we often have no real data to store in that table (small
percent of our collection use MAGE-ML unfortunately, and even fewer
provide all the data in the MAGE model, of which STUDYDESIGN is a vestige).
So, we created a STUDYPROP table. I'd think this table would be
generally useful to people storing various types of data sets via the
STUDY table.
The other new table is STUDYPROP_FEATURE. This basically allows us to
group features together per study. For example, we can store microarray
clustering results by saying that the STUDYPROP type is 'cluster' (via
type_id -> CVTERM of course), the value is 'cluster id 123', and then
that cluster would be associated with all the features that are in that
cluster via STUDYPROP_FEATURE. Adding type_id to STUDYPROP_FEATURE is
fine by us!
studyprop

Field Name	Data Type	Size	Default Value	Other	Foreign Key
studyprop_id	integer	11		PRIMARY KEY, NOT NULL
study_id	integer	10		UNIQUE, NOT NULL	study.study_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
studyprop_idx1	study_id
studyprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	studyprop_id
NOT NULL	study_id
FOREIGN KEY	study_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	study_id, type_id, rank

studyprop_feature

Top

Comments:

studyprop_feature

Field Name	Data Type	Size	Other	Foreign Key
studyprop_feature_id	integer	11	PRIMARY KEY, NOT NULL
studyprop_id	integer	10	UNIQUE, NOT NULL	studyprop.studyprop_id
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id
type_id	integer	10		cvterm.cvterm_id

Indices

Name	Fields
studyprop_feature_idx1	studyprop_id
studyprop_feature_idx2	feature_id

Constraints

Type	Fields
NOT NULL	studyprop_feature_id
NOT NULL	studyprop_id
FOREIGN KEY	studyprop_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
FOREIGN KEY	type_id
UNIQUE	studyprop_id, feature_id

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	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	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	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	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	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	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

library

Top

Comments:

$Id: library.sql,v 1.10 2008-03-25 16:00:43 emmert Exp $
=================================================================
Dependencies:
:import feature from sequence
:import synonym from sequence
:import cvterm from cv
:import pub from pub
:import organism from organism
=================================================================
================================================
TABLE: library
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
library_id	integer	11		PRIMARY KEY, NOT NULL
organism_id	integer	10		UNIQUE, NOT NULL	organism.organism_id
name	varchar	255
uniquename	text	64000		UNIQUE, NOT NULL
type_id	integer	10		UNIQUE, NOT NULL, The type_id foreign key links to a controlled vocabulary of library types. Examples of this would be: "cDNA_library" or "genomic_library"	cvterm.cvterm_id
is_obsolete	integer	10	0	NOT NULL
timeaccessioned	timestamp	0	current_timestamp	NOT NULL
timelastmodified	timestamp	0	current_timestamp	NOT NULL

Indices

Name	Fields
library_name_ind1	name
library_idx1	organism_id
library_idx2	type_id
library_idx3	uniquename

Constraints

Type	Fields
NOT NULL	library_id
NOT NULL	organism_id
FOREIGN KEY	organism_id
NOT NULL	uniquename
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	is_obsolete
NOT NULL	timeaccessioned
NOT NULL	timelastmodified
UNIQUE	organism_id, uniquename, type_id

library_synonym

Top

Comments:

================================================
TABLE: library_synonym
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
library_synonym_id	integer	11		PRIMARY KEY, NOT NULL
synonym_id	integer	10		UNIQUE, NOT NULL	synonym.synonym_id
library_id	integer	10		UNIQUE, NOT NULL	library.library_id
pub_id	integer	10		UNIQUE, NOT NULL, The pub_id link is for relating the usage of a given synonym to the publication in which it was used.	pub.pub_id
is_current	boolean	0	true	NOT NULL, The is_current bit indicates whether the linked synonym is the current -official- symbol for the linked library.
is_internal	boolean	0	false	NOT NULL, Typically a synonym exists so that somebody querying the database with an obsolete name can find the object they are looking for under its current name. If the synonym has been used publicly and deliberately (e.g. in a paper), it my also be listed in reports as a synonym. If the synonym was not used deliberately (e.g., there was a typo which went public), then the is_internal bit may be set to "true" so that it is known that the synonym is "internal" and should be queryable but should not be listed in reports as a valid synonym.

Indices

Name	Fields
library_synonym_idx1	synonym_id
library_synonym_idx2	library_id
library_synonym_idx3	pub_id

Constraints

Type	Fields
NOT NULL	library_synonym_id
NOT NULL	synonym_id
FOREIGN KEY	synonym_id
NOT NULL	library_id
FOREIGN KEY	library_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	is_current
NOT NULL	is_internal
UNIQUE	synonym_id, library_id, pub_id

library_pub

Top

Comments:

================================================
TABLE: library_pub
================================================

Field Name	Data Type	Size	Other	Foreign Key
library_pub_id	integer	11	PRIMARY KEY, NOT NULL
library_id	integer	10	UNIQUE, NOT NULL	library.library_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
library_pub_idx1	library_id
library_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	library_pub_id
NOT NULL	library_id
FOREIGN KEY	library_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	library_id, pub_id

libraryprop

Top

Comments:

================================================
TABLE: libraryprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
libraryprop_id	integer	11		PRIMARY KEY, NOT NULL
library_id	integer	10		UNIQUE, NOT NULL	library.library_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
libraryprop_idx1	library_id
libraryprop_idx2	type_id

Constraints

Type	Fields
NOT NULL	libraryprop_id
NOT NULL	library_id
FOREIGN KEY	library_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	library_id, type_id, rank

libraryprop_pub

Top

Comments:

================================================
TABLE: libraryprop_pub
================================================

Field Name	Data Type	Size	Other	Foreign Key
libraryprop_pub_id	integer	11	PRIMARY KEY, NOT NULL
libraryprop_id	integer	10	UNIQUE, NOT NULL	libraryprop.libraryprop_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
libraryprop_pub_idx1	libraryprop_id
libraryprop_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	libraryprop_pub_id
NOT NULL	libraryprop_id
FOREIGN KEY	libraryprop_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	libraryprop_id, pub_id

library_cvterm

Top

Comments:

================================================
TABLE: library_cvterm
================================================
The table library_cvterm links a library to controlled vocabularies which describe the library. For instance, there might be a link to the anatomy cv for "head" or "testes" for a head or testes library.

Field Name	Data Type	Size	Other	Foreign Key
library_cvterm_id	integer	11	PRIMARY KEY, NOT NULL
library_id	integer	10	UNIQUE, NOT NULL	library.library_id
cvterm_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
library_cvterm_idx1	library_id
library_cvterm_idx2	cvterm_id
library_cvterm_idx3	pub_id

Constraints

Type	Fields
NOT NULL	library_cvterm_id
NOT NULL	library_id
FOREIGN KEY	library_id
NOT NULL	cvterm_id
FOREIGN KEY	cvterm_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	library_id, cvterm_id, pub_id

library_feature

Top

Comments:

================================================
TABLE: library_feature
================================================
library_feature links a library to the clones which are contained in the library. Examples of such linked features might be "cDNA_clone" or "genomic_clone".

Field Name	Data Type	Size	Other	Foreign Key
library_feature_id	integer	11	PRIMARY KEY, NOT NULL
library_id	integer	10	UNIQUE, NOT NULL	library.library_id
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id

Indices

Name	Fields
library_feature_idx1	library_id
library_feature_idx2	feature_id

Constraints

Type	Fields
NOT NULL	library_feature_id
NOT NULL	library_id
FOREIGN KEY	library_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
UNIQUE	library_id, feature_id

library_dbxref

Top

Comments:

================================================
TABLE: library_dbxref
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
library_dbxref_id	integer	11		PRIMARY KEY, NOT NULL
library_id	integer	10		UNIQUE, NOT NULL	library.library_id
dbxref_id	integer	10		UNIQUE, NOT NULL	dbxref.dbxref_id
is_current	boolean	0	true	NOT NULL

Indices

Name	Fields
library_dbxref_idx1	library_id
library_dbxref_idx2	dbxref_id

Constraints

Type	Fields
NOT NULL	library_dbxref_id
NOT NULL	library_id
FOREIGN KEY	library_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
NOT NULL	is_current
UNIQUE	library_id, dbxref_id

cell_line

Top

Comments:

==========================================
Chado cell line module
============
DEPENDENCIES
============
:import feature from sequence
:import synonym from sequence
:import library from library
:import cvterm from cv
:import dbxref from general
:import pub from pub
:import organism from organism
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: cell_line
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
cell_line_id	integer	11		PRIMARY KEY, NOT NULL
name	varchar	255	NULL
uniquename	varchar	255		UNIQUE, NOT NULL
organism_id	integer	10		UNIQUE, NOT NULL	organism.organism_id
timeaccessioned	timestamp	0	current_timestamp	NOT NULL
timelastmodified	timestamp	0	current_timestamp	NOT NULL

Constraints

Type	Fields
NOT NULL	cell_line_id
NOT NULL	uniquename
NOT NULL	organism_id
FOREIGN KEY	organism_id
NOT NULL	timeaccessioned
NOT NULL	timelastmodified
UNIQUE	uniquename, organism_id

cell_line_relationship

Top

Comments:

================================================
TABLE: cell_line_relationship
================================================

Field Name	Data Type	Size	Other	Foreign Key
cell_line_relationship_id	integer	11	PRIMARY KEY, NOT NULL
subject_id	integer	10	UNIQUE, NOT NULL	cell_line.cell_line_id
object_id	integer	10	UNIQUE, NOT NULL	cell_line.cell_line_id
type_id	integer	10	UNIQUE, NOT NULL	cvterm.cvterm_id

Constraints

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

cell_line_synonym

Top

Comments:

================================================
TABLE: cell_line_synonym
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
cell_line_synonym_id	integer	11		PRIMARY KEY, NOT NULL
cell_line_id	integer	10		UNIQUE, NOT NULL	cell_line.cell_line_id
synonym_id	integer	10		UNIQUE, NOT NULL	synonym.synonym_id
pub_id	integer	10		UNIQUE, NOT NULL	pub.pub_id
is_current	boolean	0	false	NOT NULL
is_internal	boolean	0	false	NOT NULL

Constraints

Type	Fields
NOT NULL	cell_line_synonym_id
NOT NULL	cell_line_id
FOREIGN KEY	cell_line_id
NOT NULL	synonym_id
FOREIGN KEY	synonym_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	is_current
NOT NULL	is_internal
UNIQUE	synonym_id, cell_line_id, pub_id

cell_line_cvterm

Top

Comments:

================================================
TABLE: cell_line_cvterm
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
cell_line_cvterm_id	integer	11		PRIMARY KEY, NOT NULL
cell_line_id	integer	10		UNIQUE, NOT NULL	cell_line.cell_line_id
cvterm_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
pub_id	integer	10		UNIQUE, NOT NULL	pub.pub_id
rank	integer	10	0	UNIQUE, NOT NULL

Constraints

Type	Fields
NOT NULL	cell_line_cvterm_id
NOT NULL	cell_line_id
FOREIGN KEY	cell_line_id
NOT NULL	cvterm_id
FOREIGN KEY	cvterm_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
NOT NULL	rank
UNIQUE	cell_line_id, cvterm_id, pub_id, rank

cell_line_dbxref

Top

Comments:

================================================
TABLE: cell_line_dbxref
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
cell_line_dbxref_id	integer	11		PRIMARY KEY, NOT NULL
cell_line_id	integer	10		UNIQUE, NOT NULL	cell_line.cell_line_id
dbxref_id	integer	10		UNIQUE, NOT NULL	dbxref.dbxref_id
is_current	boolean	0	true	NOT NULL

Constraints

Type	Fields
NOT NULL	cell_line_dbxref_id
NOT NULL	cell_line_id
FOREIGN KEY	cell_line_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
NOT NULL	is_current
UNIQUE	cell_line_id, dbxref_id

cell_lineprop

Top

Comments:

================================================
TABLE: cell_lineprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
cell_lineprop_id	integer	11		PRIMARY KEY, NOT NULL
cell_line_id	integer	10		UNIQUE, NOT NULL	cell_line.cell_line_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000	NULL
rank	integer	10	0	UNIQUE, NOT NULL

Constraints

Type	Fields
NOT NULL	cell_lineprop_id
NOT NULL	cell_line_id
FOREIGN KEY	cell_line_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	cell_line_id, type_id, rank

cell_lineprop_pub

Top

Comments:

================================================
TABLE: cell_lineprop_pub
================================================

Field Name	Data Type	Size	Other	Foreign Key
cell_lineprop_pub_id	integer	11	PRIMARY KEY, NOT NULL
cell_lineprop_id	integer	10	UNIQUE, NOT NULL	cell_lineprop.cell_lineprop_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Constraints

Type	Fields
NOT NULL	cell_lineprop_pub_id
NOT NULL	cell_lineprop_id
FOREIGN KEY	cell_lineprop_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	cell_lineprop_id, pub_id

cell_line_feature

Top

Comments:

================================================
TABLE: cell_line_feature
================================================

Field Name	Data Type	Size	Other	Foreign Key
cell_line_feature_id	integer	11	PRIMARY KEY, NOT NULL
cell_line_id	integer	10	UNIQUE, NOT NULL	cell_line.cell_line_id
feature_id	integer	10	UNIQUE, NOT NULL	feature.feature_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Constraints

Type	Fields
NOT NULL	cell_line_feature_id
NOT NULL	cell_line_id
FOREIGN KEY	cell_line_id
NOT NULL	feature_id
FOREIGN KEY	feature_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	cell_line_id, feature_id, pub_id

cell_line_cvtermprop

Top

Comments:

================================================
TABLE: cell_line_cvtermprop
================================================

Field Name	Data Type	Size	Default Value	Other	Foreign Key
cell_line_cvtermprop_id	integer	11		PRIMARY KEY, NOT NULL
cell_line_cvterm_id	integer	10		UNIQUE, NOT NULL	cell_line_cvterm.cell_line_cvterm_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000	NULL
rank	integer	10	0	UNIQUE, NOT NULL

Constraints

Type	Fields
NOT NULL	cell_line_cvtermprop_id
NOT NULL	cell_line_cvterm_id
FOREIGN KEY	cell_line_cvterm_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	cell_line_cvterm_id, type_id, rank

cell_line_pub

Top

Comments:

================================================
TABLE: cell_line_pub
================================================

Field Name	Data Type	Size	Other	Foreign Key
cell_line_pub_id	integer	11	PRIMARY KEY, NOT NULL
cell_line_id	integer	10	UNIQUE, NOT NULL	cell_line.cell_line_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Constraints

Type	Fields
NOT NULL	cell_line_pub_id
NOT NULL	cell_line_id
FOREIGN KEY	cell_line_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	cell_line_id, pub_id

cell_line_library

Top

Comments:

================================================
TABLE: cell_line_library
================================================

Field Name	Data Type	Size	Other	Foreign Key
cell_line_library_id	integer	11	PRIMARY KEY, NOT NULL
cell_line_id	integer	10	UNIQUE, NOT NULL	cell_line.cell_line_id
library_id	integer	10	UNIQUE, NOT NULL	library.library_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Constraints

Type	Fields
NOT NULL	cell_line_library_id
NOT NULL	cell_line_id
FOREIGN KEY	cell_line_id
NOT NULL	library_id
FOREIGN KEY	library_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	cell_line_id, library_id, pub_id

nd_geolocation

Top

Comments:

=================================================================
Dependencies:
:import feature from sequence
:import cvterm from cv
:import pub from pub
:import phenotype from phenotype
:import organism from organism
:import genotype from genetic
:import contact from contact
:import project from project
:import stock from stock
:import synonym
=================================================================
this probably needs some work, depending on how cross-database we
want to be. In Postgres, at least, there are much better ways to
represent geo information.
The geo-referencable location of the stock. NOTE: This entity is subject to change as a more general and possibly more OpenGIS-compliant geolocation module may be introduced into Chado.

Field Name	Data Type	Size	Other
nd_geolocation_id	integer	11	PRIMARY KEY, NOT NULL
description	varchar	255	A textual representation of the location, if this is the original georeference. Optional if the original georeference is available in lat/long coordinates.
latitude	real	10	The decimal latitude coordinate of the georeference, using positive and negative sign to indicate N and S, respectively.
longitude	real	10	The decimal longitude coordinate of the georeference, using positive and negative sign to indicate E and W, respectively.
geodetic_datum	varchar	32	The geodetic system on which the geo-reference coordinates are based. For geo-references measured between 1984 and 2010, this will typically be WGS84.
altitude	real	10	The altitude (elevation) of the location in meters. If the altitude is only known as a range, this is the average, and altitude_dev will hold half of the width of the range.

Constraints

Type	Fields
NOT NULL	nd_geolocation_id

nd_experiment

Top

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_id	integer	11	PRIMARY KEY, NOT NULL
nd_geolocation_id	integer	10	NOT NULL	nd_geolocation.nd_geolocation_id
type_id	integer	10	NOT NULL	cvterm.cvterm_id

Constraints

Type	Fields
NOT NULL	nd_experiment_id
NOT NULL	nd_geolocation_id
FOREIGN KEY	nd_geolocation_id
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	nd_geolocation_id
FOREIGN KEY	type_id

nd_experiment_project

Top

Comments:

used to be nd_diversityexperiment_project
then was nd_assay_project

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_project_id	integer	11	PRIMARY KEY, NOT NULL
project_id	integer	10	NOT NULL	project.project_id
nd_experiment_id	integer	10	NOT NULL	nd_experiment.nd_experiment_id

Constraints

Type	Fields
NOT NULL	nd_experiment_project_id
NOT NULL	project_id
FOREIGN KEY	project_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	project_id
FOREIGN KEY	nd_experiment_id

nd_experimentprop

Top

Field Name	Data Type	Size	Default Value	Other	Foreign Key
nd_experimentprop_id	integer	11		PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10		UNIQUE, NOT NULL	nd_experiment.nd_experiment_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000	NULL
rank	integer	10	0	UNIQUE, NOT NULL

Constraints

Type	Fields
NOT NULL	nd_experimentprop_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	nd_experiment_id, type_id, rank
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	type_id

nd_experiment_pub

Top

Comments:

Linking nd_experiment(s) to publication(s)

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_pub_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10	UNIQUE, NOT NULL	nd_experiment.nd_experiment_id
pub_id	integer	10	UNIQUE, NOT NULL	pub.pub_id

Indices

Name	Fields
nd_experiment_pub_idx1	nd_experiment_id
nd_experiment_pub_idx2	pub_id

Constraints

Type	Fields
NOT NULL	nd_experiment_pub_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	pub_id
FOREIGN KEY	pub_id
UNIQUE	nd_experiment_id, pub_id

nd_geolocationprop

Top

Comments:

Property/value associations for geolocations. This table can store the properties such as location and environment

Field Name	Data Type	Size	Default Value	Other	Foreign Key
nd_geolocationprop_id	integer	11		PRIMARY KEY, NOT NULL
nd_geolocation_id	integer	10		UNIQUE, NOT NULL	nd_geolocation.nd_geolocation_id
type_id	integer	10		UNIQUE, NOT NULL, The name of the property as a reference to a controlled vocabulary term.	cvterm.cvterm_id
value	text	64000	NULL	The value of the property.
rank	integer	10	0	UNIQUE, NOT NULL, The rank of the property value, if the property has an array of values.

Constraints

Type	Fields
NOT NULL	nd_geolocationprop_id
NOT NULL	nd_geolocation_id
FOREIGN KEY	nd_geolocation_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	nd_geolocation_id, type_id, rank
FOREIGN KEY	nd_geolocation_id
FOREIGN KEY	type_id

nd_protocol

Top

Comments:

A protocol can be anything that is done as part of the experiment.

Field Name	Data Type	Size	Other	Foreign Key
nd_protocol_id	integer	11	PRIMARY KEY, NOT NULL
name	varchar	255	UNIQUE, NOT NULL, The protocol name.
type_id	integer	10	NOT NULL	cvterm.cvterm_id

Constraints

Type	Fields
NOT NULL	nd_protocol_id
NOT NULL	name
UNIQUE	name
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	type_id

nd_reagent

Top

Comments:

A reagent such as a primer, an enzyme, an adapter oligo, a linker oligo. Reagents are used in genotyping experiments, or in any other kind of experiment.

Field Name	Data Type	Size	Other	Foreign Key
nd_reagent_id	integer	11	PRIMARY KEY, NOT NULL
name	varchar	80	NOT NULL, The name of the reagent. The name should be unique for a given type.
type_id	integer	10	NOT NULL, The type of the reagent, for example linker oligomer, or forward primer.	cvterm.cvterm_id
feature_id	integer	10	If the reagent is a primer, the feature that it corresponds to. More generally, the corresponding feature for any reagent that has a sequence that maps to another sequence.

Constraints

Type	Fields
NOT NULL	nd_reagent_id
NOT NULL	name
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	type_id

nd_protocol_reagent

Top

Field Name	Data Type	Size	Other	Foreign Key
nd_protocol_reagent_id	integer	11	PRIMARY KEY, NOT NULL
nd_protocol_id	integer	10	NOT NULL	nd_protocol.nd_protocol_id
reagent_id	integer	10	NOT NULL	nd_reagent.nd_reagent_id
type_id	integer	10	NOT NULL	cvterm.cvterm_id

Constraints

Type	Fields
NOT NULL	nd_protocol_reagent_id
NOT NULL	nd_protocol_id
FOREIGN KEY	nd_protocol_id
NOT NULL	reagent_id
FOREIGN KEY	reagent_id
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	nd_protocol_id
FOREIGN KEY	reagent_id
FOREIGN KEY	type_id

nd_protocolprop

Top

Comments:

Property/value associations for protocol.

Field Name	Data Type	Size	Default Value	Other	Foreign Key
nd_protocolprop_id	integer	11		PRIMARY KEY, NOT NULL
nd_protocol_id	integer	10		UNIQUE, NOT NULL, The protocol to which the property applies.	nd_protocol.nd_protocol_id
type_id	integer	10		UNIQUE, NOT NULL, The name of the property as a reference to a controlled vocabulary term.	cvterm.cvterm_id
value	text	64000	NULL	The value of the property.
rank	integer	10	0	UNIQUE, NOT NULL, The rank of the property value, if the property has an array of values.

Constraints

Type	Fields
NOT NULL	nd_protocolprop_id
NOT NULL	nd_protocol_id
FOREIGN KEY	nd_protocol_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	nd_protocol_id, type_id, rank
FOREIGN KEY	nd_protocol_id
FOREIGN KEY	type_id

nd_experiment_stock

Top

Comments:

Part of a stock or a clone of a stock that is used in an experiment

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_stock_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10	NOT NULL	nd_experiment.nd_experiment_id
stock_id	integer	10	NOT NULL, stock used in the extraction or the corresponding stock for the clone	stock.stock_id
type_id	integer	10	NOT NULL	cvterm.cvterm_id

Constraints

Type	Fields
NOT NULL	nd_experiment_stock_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	stock_id
FOREIGN KEY	stock_id
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	stock_id
FOREIGN KEY	type_id

nd_experiment_protocol

Top

Comments:

Linking table: experiments to the protocols they involve.

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_protocol_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10	NOT NULL	nd_experiment.nd_experiment_id
nd_protocol_id	integer	10	NOT NULL	nd_protocol.nd_protocol_id

Constraints

Type	Fields
NOT NULL	nd_experiment_protocol_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	nd_protocol_id
FOREIGN KEY	nd_protocol_id
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	nd_protocol_id

nd_experiment_phenotype

Top

Comments:

Linking table: experiments to the phenotypes they produce. There is a one-to-one relationship between an experiment and a phenotype since each phenotype record should point to one experiment. Add a new experiment_id for each phenotype record.

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_phenotype_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10	UNIQUE, NOT NULL	nd_experiment.nd_experiment_id
phenotype_id	integer	10	UNIQUE, NOT NULL	phenotype.phenotype_id

Constraints

Type	Fields
NOT NULL	nd_experiment_phenotype_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	phenotype_id
FOREIGN KEY	phenotype_id
UNIQUE	nd_experiment_id, phenotype_id
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	phenotype_id

nd_experiment_genotype

Top

Comments:

Linking table: experiments to the genotypes they produce. There is a one-to-one relationship between an experiment and a genotype since each genotype record should point to one experiment. Add a new experiment_id for each genotype record.

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_genotype_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10	UNIQUE, NOT NULL	nd_experiment.nd_experiment_id
genotype_id	integer	10	UNIQUE, NOT NULL	genotype.genotype_id

Constraints

Type	Fields
NOT NULL	nd_experiment_genotype_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	genotype_id
FOREIGN KEY	genotype_id
UNIQUE	nd_experiment_id, genotype_id
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	genotype_id

nd_reagent_relationship

Top

Comments:

Relationships between reagents. Some reagents form a group. i.e., they are used all together or not at all. Examples are adapter/linker/enzyme experiment reagents.

Field Name	Data Type	Size	Other	Foreign Key
nd_reagent_relationship_id	integer	11	PRIMARY KEY, NOT NULL
subject_reagent_id	integer	10	NOT NULL, The subject reagent in the relationship. In parent/child terminology, the subject is the child. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.	nd_reagent.nd_reagent_id
object_reagent_id	integer	10	NOT NULL, The object reagent in the relationship. In parent/child terminology, the object is the parent. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.	nd_reagent.nd_reagent_id
type_id	integer	10	NOT NULL, The type (or predicate) of the relationship. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.	cvterm.cvterm_id

Constraints

Type	Fields
NOT NULL	nd_reagent_relationship_id
NOT NULL	subject_reagent_id
FOREIGN KEY	subject_reagent_id
NOT NULL	object_reagent_id
FOREIGN KEY	object_reagent_id
NOT NULL	type_id
FOREIGN KEY	type_id
FOREIGN KEY	subject_reagent_id
FOREIGN KEY	object_reagent_id
FOREIGN KEY	type_id

nd_reagentprop

Top

Field Name	Data Type	Size	Default Value	Other	Foreign Key
nd_reagentprop_id	integer	11		PRIMARY KEY, NOT NULL
nd_reagent_id	integer	10		UNIQUE, NOT NULL	nd_reagent.nd_reagent_id
type_id	integer	10		UNIQUE, NOT NULL	cvterm.cvterm_id
value	text	64000	NULL
rank	integer	10	0	UNIQUE, NOT NULL

Constraints

Type	Fields
NOT NULL	nd_reagentprop_id
NOT NULL	nd_reagent_id
FOREIGN KEY	nd_reagent_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	nd_reagent_id, type_id, rank
FOREIGN KEY	nd_reagent_id
FOREIGN KEY	type_id

nd_experiment_stockprop

Top

Comments:

Property/value associations for experiment_stocks. This table can store the properties such as treatment

Field Name	Data Type	Size	Default Value	Other	Foreign Key
nd_experiment_stockprop_id	integer	11		PRIMARY KEY, NOT NULL
nd_experiment_stock_id	integer	10		UNIQUE, NOT NULL, The experiment_stock to which the property applies.	nd_experiment_stock.nd_experiment_stock_id
type_id	integer	10		UNIQUE, NOT NULL, The name of the property as a reference to a controlled vocabulary term.	cvterm.cvterm_id
value	text	64000	NULL	The value of the property.
rank	integer	10	0	UNIQUE, NOT NULL, The rank of the property value, if the property has an array of values.

Constraints

Type	Fields
NOT NULL	nd_experiment_stockprop_id
NOT NULL	nd_experiment_stock_id
FOREIGN KEY	nd_experiment_stock_id
NOT NULL	type_id
FOREIGN KEY	type_id
NOT NULL	rank
UNIQUE	nd_experiment_stock_id, type_id, rank
FOREIGN KEY	nd_experiment_stock_id
FOREIGN KEY	type_id

nd_experiment_stock_dbxref

Top

Comments:

Cross-reference experiment_stock to accessions, images, etc

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_stock_dbxref_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_stock_id	integer	10	NOT NULL	nd_experiment_stock.nd_experiment_stock_id
dbxref_id	integer	10	NOT NULL	dbxref.dbxref_id

Constraints

Type	Fields
NOT NULL	nd_experiment_stock_dbxref_id
NOT NULL	nd_experiment_stock_id
FOREIGN KEY	nd_experiment_stock_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
FOREIGN KEY	nd_experiment_stock_id
FOREIGN KEY	dbxref_id

nd_experiment_dbxref

Top

Comments:

Cross-reference experiment to accessions, images, etc

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_dbxref_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10	NOT NULL	nd_experiment.nd_experiment_id
dbxref_id	integer	10	NOT NULL	dbxref.dbxref_id

Constraints

Type	Fields
NOT NULL	nd_experiment_dbxref_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	dbxref_id
FOREIGN KEY	dbxref_id
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	dbxref_id

nd_experiment_contact

Top

Field Name	Data Type	Size	Other	Foreign Key
nd_experiment_contact_id	integer	11	PRIMARY KEY, NOT NULL
nd_experiment_id	integer	10	NOT NULL	nd_experiment.nd_experiment_id
contact_id	integer	10	NOT NULL	contact.contact_id

Constraints

Type	Fields
NOT NULL	nd_experiment_contact_id
NOT NULL	nd_experiment_id
FOREIGN KEY	nd_experiment_id
NOT NULL	contact_id
FOREIGN KEY	contact_id
FOREIGN KEY	nd_experiment_id
FOREIGN KEY	contact_id

Created by SQL::Translator 0.11003