Description of Schema


Tables

analysis
analysisprop
analysisfeature
analysisfeatureprop
analysis_dbxref
analysis_cvterm
analysis_relationship
analysis_pub

analysis

Top
Comments:

$Id: companalysis.sql,v 1.37 2007-03-23 15:18:02 scottcain Exp $
==========================================
Chado companalysis module
=================================================================
Dependencies:
:import feature from sequence
:import cvterm from cv
:import dbxref from db
:import pub from pub
=================================================================
================================================
TABLE: analysis
================================================
An analysis is a particular type of a computational analysis; it may be a blast of one sequence against another, or an all by all blast, or a different kind of analysis altogether. It is a single unit of computation.
Field Name Data Type Size Default Value Other Foreign Key
analysis_id integer 20 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 NULLanalysis_id
NOT NULLprogram
NOT NULLprogramversion
NOT NULLtimeexecuted
UNIQUEprogram, programversion, sourcename

analysisprop

Top
Comments:

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

Indices

Name Fields
analysisprop_idx1analysis_id
analysisprop_idx2type_id

Constraints

Type Fields
NOT NULLanalysisprop_id
NOT NULLanalysis_id
FOREIGN KEYanalysis_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEanalysis_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 Default Value Other Foreign Key
analysisfeature_id integer 20 PRIMARY KEY, NOT NULL
feature_id integer 20 UNIQUE, NOT NULL feature.feature_id
analysis_id integer 20 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_idx1feature_id
analysisfeature_idx2analysis_id

Constraints

Type Fields
NOT NULLanalysisfeature_id
NOT NULLfeature_id
FOREIGN KEYfeature_id
NOT NULLanalysis_id
FOREIGN KEYanalysis_id
UNIQUEfeature_id, analysis_id

analysisfeatureprop

Top
Comments:

================================================
TABLE: analysisfeatureprop
================================================
Field Name Data Type Size Default Value Other Foreign Key
analysisfeatureprop_id integer 20 PRIMARY KEY, NOT NULL
analysisfeature_id integer 20 UNIQUE, NOT NULL analysisfeature.analysisfeature_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000
rank integer 10 UNIQUE, NOT NULL

Indices

Name Fields
analysisfeatureprop_idx1analysisfeature_id
analysisfeatureprop_idx2type_id

Constraints

Type Fields
NOT NULLanalysisfeature_id
FOREIGN KEYanalysisfeature_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEanalysisfeature_id, type_id, rank
FOREIGN KEYanalysisfeature_id
FOREIGN KEYtype_id

analysis_dbxref

Top
Comments:

================================================
TABLE: analysis_dbxref
================================================
Links an analysis to dbxrefs.
Field Name Data Type Size Default Value Other Foreign Key
analysis_dbxref_id integer 20 PRIMARY KEY, NOT NULL
analysis_id integer 20 UNIQUE, NOT NULL analysis.analysis_id
dbxref_id integer 20 UNIQUE, NOT NULL dbxref.dbxref_id
is_current boolean 0 true NOT NULL, True if this dbxref is the most up to date accession in the corresponding db. Retired accessions should set this field to false

Indices

Name Fields
analysis_dbxref_idx1analysis_id
analysis_dbxref_idx2dbxref_id

Constraints

Type Fields
NOT NULLanalysis_dbxref_id
NOT NULLanalysis_id
NOT NULLdbxref_id
NOT NULLis_current
FOREIGN KEYanalysis_id
FOREIGN KEYdbxref_id
UNIQUEanalysis_id, dbxref_id

analysis_cvterm

Top
Comments:

================================================
TABLE: analysis_cvterm
================================================
Associate a term from a cv with an analysis.
Field Name Data Type Size Default Value Other Foreign Key
analysis_cvterm_id integer 20 PRIMARY KEY, NOT NULL
analysis_id integer 20 UNIQUE, NOT NULL analysis.analysis_id
cvterm_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_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 analysis does NOT have the specified term.
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
analysis_cvterm_idx1analysis_id
analysis_cvterm_idx2cvterm_id

Constraints

Type Fields
NOT NULLanalysis_cvterm_id
NOT NULLanalysis_id
NOT NULLcvterm_id
NOT NULLis_not
NOT NULLrank
FOREIGN KEYanalysis_id
FOREIGN KEYcvterm_id
UNIQUEanalysis_id, cvterm_id, rank

analysis_relationship

Top
Comments:

================================================
TABLE: analysis_relationship
================================================
Field Name Data Type Size Default Value Other Foreign Key
analysis_relationship_id integer 20 PRIMARY KEY, NOT NULL
subject_id integer 20 UNIQUE, NOT NULL, analysis_relationship.subject_id i s the subject of the subj-predicate-obj sentence. analysis.analysis_id
object_id integer 20 UNIQUE, NOT NULL, analysis_relationship.object_id is the object of the subj-predicate-obj sentence. analysis.analysis_id
type_id integer 20 UNIQUE, NOT NULL, analysis_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 analysis_relationship.value is for additional notes or comments.
rank integer 10 0 UNIQUE, NOT NULL, analysis_relationship.rank is the ordering of subject analysiss with respect to the object analysis may be important where rank is used to order these; starts from zero.

Indices

Name Fields
analysis_relationship_idx1subject_id
analysis_relationship_idx2object_id
analysis_relationship_idx3type_id

Constraints

Type Fields
NOT NULLanalysis_relationship_id
NOT NULLsubject_id
NOT NULLobject_id
NOT NULLtype_id
NOT NULLrank
FOREIGN KEYsubject_id
FOREIGN KEYobject_id
FOREIGN KEYtype_id
UNIQUEsubject_id, object_id, type_id, rank

analysis_pub

Top
Comments:

================================================
TABLE: analysis_pub
================================================
Provenance. Linking table between analyses and the publications that mention them.
Field Name Data Type Size Default Value Other Foreign Key
analysis_pub_id integer 20 PRIMARY KEY, NOT NULL
analysis_id integer 20 UNIQUE, NOT NULL analysis.analysis_id
pub_id integer 20 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
analysis_pub_idx1analysis_id
analysis_pub_idx2pub_id

Constraints

Type Fields
NOT NULLanalysis_pub_id
NOT NULLanalysis_id
FOREIGN KEYanalysis_id
NOT NULLpub_id
FOREIGN KEYpub_id
UNIQUEanalysis_id, pub_id

Created by
SQL::Translator 0.11020