Description of Schema

Tables

mageml
magedocumentation
protocol
protocolparam
channel
arraydesign
arraydesignprop
assay
assayprop
assay_project
biomaterial
biomaterial_relationship
biomaterialprop
biomaterial_dbxref
treatment
biomaterial_treatment
assay_biomaterial
acquisition
acquisitionprop
acquisition_relationship
quantification
quantificationprop
quantification_relationship
control
element
elementresult
element_relationship
elementresult_relationship
study
study_assay
studydesign
studydesignprop
studyfactor
studyfactorvalue
studyprop
studyprop_feature

mageml

Top
Comments:

$Id: mage.sql,v 1.3 2008-03-19 18:32:51 scottcain Exp $
==========================================
Chado mage module
=================================================================
Dependencies:
:import feature from sequence
:import cvterm from cv
:import pub from pub
:import organism from organism
:import contact from contact
:import dbxref from general
:import tableinfo from general
:import project from project
:import analysis from companalysis
=================================================================
================================================
TABLE: mageml
================================================
This table is for storing extra bits of MAGEml in a denormalized form. More normalization would require many more tables.
Field Name Data Type Size Default Value Other Foreign Key
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 Default Value 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 Default Value Other Foreign Key
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 Default Value 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 Default Value 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 Default Value 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 Default Value 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 Default Value 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 Default Value 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 Default Value 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
Created by SQL::Translator 0.11003