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 db
: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 20 PRIMARY KEY, NOT NULL
mage_package text 64000 NOT NULL
mage_ml text 64000 NOT NULL

Constraints

Type Fields
NOT NULLmageml_id
NOT NULLmage_package
NOT NULLmage_ml

magedocumentation

Top
Comments:

================================================
TABLE: magedocumentation
================================================
NULL
Field Name Data Type Size Default Value Other Foreign Key
magedocumentation_id integer 20 PRIMARY KEY, NOT NULL
mageml_id integer 20 NOT NULL mageml.mageml_id
tableinfo_id integer 20 NOT NULL tableinfo.tableinfo_id
row_id integer 10 NOT NULL
mageidentifier text 64000 NOT NULL

Indices

Name Fields
magedocumentation_idx1mageml_id
magedocumentation_idx2tableinfo_id
magedocumentation_idx3row_id

Constraints

Type Fields
NOT NULLmagedocumentation_id
NOT NULLmageml_id
FOREIGN KEYmageml_id
NOT NULLtableinfo_id
FOREIGN KEYtableinfo_id
NOT NULLrow_id
NOT NULLmageidentifier

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 20 PRIMARY KEY, NOT NULL
type_id integer 20 NOT NULL cvterm.cvterm_id
pub_id integer 20 NULL pub.pub_id
dbxref_id integer 20 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_idx1type_id
protocol_idx2pub_id
protocol_idx3dbxref_id

Constraints

Type Fields
NOT NULLprotocol_id
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYpub_id
FOREIGN KEYdbxref_id
NOT NULLname
UNIQUEname

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 20 PRIMARY KEY, NOT NULL
protocol_id integer 20 NOT NULL protocol.protocol_id
name text 64000 NOT NULL
datatype_id integer 20 NULL cvterm.cvterm_id
unittype_id integer 20 NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 NOT NULL

Indices

Name Fields
protocolparam_idx1protocol_id
protocolparam_idx2datatype_id
protocolparam_idx3unittype_id

Constraints

Type Fields
NOT NULLprotocolparam_id
NOT NULLprotocol_id
FOREIGN KEYprotocol_id
NOT NULLname
FOREIGN KEYdatatype_id
FOREIGN KEYunittype_id
NOT NULLrank

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 20 PRIMARY KEY, NOT NULL
name text 64000 UNIQUE, NOT NULL
definition text 64000 NOT NULL

Constraints

Type Fields
NOT NULLchannel_id
NOT NULLname
NOT NULLdefinition
UNIQUEname

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 20 PRIMARY KEY, NOT NULL
manufacturer_id integer 20 NOT NULL contact.contact_id
platformtype_id integer 20 NOT NULL cvterm.cvterm_id
substratetype_id integer 20 NULL cvterm.cvterm_id
protocol_id integer 20 NULL protocol.protocol_id
dbxref_id integer 20 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_idx1manufacturer_id
arraydesign_idx2platformtype_id
arraydesign_idx3substratetype_id
arraydesign_idx4protocol_id
arraydesign_idx5dbxref_id

Constraints

Type Fields
NOT NULLarraydesign_id
NOT NULLmanufacturer_id
FOREIGN KEYmanufacturer_id
NOT NULLplatformtype_id
FOREIGN KEYplatformtype_id
FOREIGN KEYsubstratetype_id
FOREIGN KEYprotocol_id
FOREIGN KEYdbxref_id
NOT NULLname
UNIQUEname

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 20 PRIMARY KEY, NOT NULL
arraydesign_id integer 20 UNIQUE, NOT NULL arraydesign.arraydesign_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
arraydesignprop_idx1arraydesign_id
arraydesignprop_idx2type_id

Constraints

Type Fields
NOT NULLarraydesignprop_id
NOT NULLarraydesign_id
FOREIGN KEYarraydesign_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEarraydesign_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 20 PRIMARY KEY, NOT NULL
arraydesign_id integer 20 NOT NULL arraydesign.arraydesign_id
protocol_id integer 20 NULL protocol.protocol_id
assaydate timestamp 0 current_timestamp
arrayidentifier text 64000 NULL
arraybatchidentifier text 64000 NULL
operator_id integer 20 NOT NULL contact.contact_id
dbxref_id integer 20 NULL dbxref.dbxref_id
name text 64000 NULL UNIQUE
description text 64000 NULL

Indices

Name Fields
assay_idx1arraydesign_id
assay_idx2protocol_id
assay_idx3operator_id
assay_idx4dbxref_id

Constraints

Type Fields
NOT NULLassay_id
NOT NULLarraydesign_id
FOREIGN KEYarraydesign_id
FOREIGN KEYprotocol_id
NOT NULLoperator_id
FOREIGN KEYoperator_id
FOREIGN KEYdbxref_id
UNIQUEname

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 20 PRIMARY KEY, NOT NULL
assay_id integer 20 UNIQUE, NOT NULL assay.assay_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
assayprop_idx1assay_id
assayprop_idx2type_id

Constraints

Type Fields
NOT NULLassayprop_id
NOT NULLassay_id
FOREIGN KEYassay_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEassay_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 20 PRIMARY KEY, NOT NULL
assay_id integer 20 UNIQUE, NOT NULL assay.assay_id
project_id integer 20 UNIQUE, NOT NULL project.project_id

Indices

Name Fields
assay_project_idx1assay_id
assay_project_idx2project_id

Constraints

Type Fields
NOT NULLassay_project_id
NOT NULLassay_id
FOREIGN KEYassay_id
NOT NULLproject_id
FOREIGN KEYproject_id
UNIQUEassay_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 20 PRIMARY KEY, NOT NULL
taxon_id integer 20 NULL organism.organism_id
biosourceprovider_id integer 20 NULL contact.contact_id
dbxref_id integer 20 NULL dbxref.dbxref_id
name text 64000 NULL UNIQUE
description text 64000 NULL

Indices

Name Fields
biomaterial_idx1taxon_id
biomaterial_idx2biosourceprovider_id
biomaterial_idx3dbxref_id

Constraints

Type Fields
NOT NULLbiomaterial_id
FOREIGN KEYtaxon_id
FOREIGN KEYbiosourceprovider_id
FOREIGN KEYdbxref_id
UNIQUEname

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 20 PRIMARY KEY, NOT NULL
subject_id integer 20 UNIQUE, NOT NULL biomaterial.biomaterial_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
object_id integer 20 UNIQUE, NOT NULL biomaterial.biomaterial_id

Indices

Name Fields
biomaterial_relationship_idx1subject_id
biomaterial_relationship_idx2object_id
biomaterial_relationship_idx3type_id

Constraints

Type Fields
NOT NULLbiomaterial_relationship_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLobject_id
FOREIGN KEYobject_id
UNIQUEsubject_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 20 PRIMARY KEY, NOT NULL
biomaterial_id integer 20 UNIQUE, NOT NULL biomaterial.biomaterial_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
biomaterialprop_idx1biomaterial_id
biomaterialprop_idx2type_id

Constraints

Type Fields
NOT NULLbiomaterialprop_id
NOT NULLbiomaterial_id
FOREIGN KEYbiomaterial_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEbiomaterial_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 20 PRIMARY KEY, NOT NULL
biomaterial_id integer 20 UNIQUE, NOT NULL biomaterial.biomaterial_id
dbxref_id integer 20 UNIQUE, NOT NULL dbxref.dbxref_id

Indices

Name Fields
biomaterial_dbxref_idx1biomaterial_id
biomaterial_dbxref_idx2dbxref_id

Constraints

Type Fields
NOT NULLbiomaterial_dbxref_id
NOT NULLbiomaterial_id
FOREIGN KEYbiomaterial_id
NOT NULLdbxref_id
FOREIGN KEYdbxref_id
UNIQUEbiomaterial_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 20 PRIMARY KEY, NOT NULL
rank integer 10 0 NOT NULL
biomaterial_id integer 20 NOT NULL biomaterial.biomaterial_id
type_id integer 20 NOT NULL cvterm.cvterm_id
protocol_id integer 20 NULL protocol.protocol_id
name text 64000 NULL

Indices

Name Fields
treatment_idx1biomaterial_id
treatment_idx2type_id
treatment_idx3protocol_id

Constraints

Type Fields
NOT NULLtreatment_id
NOT NULLrank
NOT NULLbiomaterial_id
FOREIGN KEYbiomaterial_id
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYprotocol_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 20 PRIMARY KEY, NOT NULL
biomaterial_id integer 20 UNIQUE, NOT NULL biomaterial.biomaterial_id
treatment_id integer 20 UNIQUE, NOT NULL treatment.treatment_id
unittype_id integer 20 NULL cvterm.cvterm_id
value float 15 NULL
rank integer 10 0 NOT NULL

Indices

Name Fields
biomaterial_treatment_idx1biomaterial_id
biomaterial_treatment_idx2treatment_id
biomaterial_treatment_idx3unittype_id

Constraints

Type Fields
NOT NULLbiomaterial_treatment_id
NOT NULLbiomaterial_id
FOREIGN KEYbiomaterial_id
NOT NULLtreatment_id
FOREIGN KEYtreatment_id
FOREIGN KEYunittype_id
NOT NULLrank
UNIQUEbiomaterial_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 20 PRIMARY KEY, NOT NULL
assay_id integer 20 UNIQUE, NOT NULL assay.assay_id
biomaterial_id integer 20 UNIQUE, NOT NULL biomaterial.biomaterial_id
channel_id integer 20 NULL UNIQUE channel.channel_id
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
assay_biomaterial_idx1assay_id
assay_biomaterial_idx2biomaterial_id
assay_biomaterial_idx3channel_id

Constraints

Type Fields
NOT NULLassay_biomaterial_id
NOT NULLassay_id
FOREIGN KEYassay_id
NOT NULLbiomaterial_id
FOREIGN KEYbiomaterial_id
FOREIGN KEYchannel_id
NOT NULLrank
UNIQUEassay_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 20 PRIMARY KEY, NOT NULL
assay_id integer 20 NOT NULL assay.assay_id
protocol_id integer 20 NULL protocol.protocol_id
channel_id integer 20 NULL channel.channel_id
acquisitiondate timestamp 0 current_timestamp
name text 64000 NULL UNIQUE
uri text 64000 NULL

Indices

Name Fields
acquisition_idx1assay_id
acquisition_idx2protocol_id
acquisition_idx3channel_id

Constraints

Type Fields
NOT NULLacquisition_id
NOT NULLassay_id
FOREIGN KEYassay_id
FOREIGN KEYprotocol_id
FOREIGN KEYchannel_id
UNIQUEname

acquisitionprop

Top
Comments:

================================================
TABLE: acquisitionprop
================================================
Parameters associated with image acquisition.
Field Name Data Type Size Default Value Other Foreign Key
acquisitionprop_id integer 20 PRIMARY KEY, NOT NULL
acquisition_id integer 20 UNIQUE, NOT NULL acquisition.acquisition_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
acquisitionprop_idx1acquisition_id
acquisitionprop_idx2type_id

Constraints

Type Fields
NOT NULLacquisitionprop_id
NOT NULLacquisition_id
FOREIGN KEYacquisition_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEacquisition_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 20 PRIMARY KEY, NOT NULL
subject_id integer 20 UNIQUE, NOT NULL acquisition.acquisition_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
object_id integer 20 UNIQUE, NOT NULL acquisition.acquisition_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
acquisition_relationship_idx1subject_id
acquisition_relationship_idx2type_id
acquisition_relationship_idx3object_id

Constraints

Type Fields
NOT NULLacquisition_relationship_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLobject_id
FOREIGN KEYobject_id
NOT NULLrank
UNIQUEsubject_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 20 PRIMARY KEY, NOT NULL
acquisition_id integer 20 NOT NULL acquisition.acquisition_id
operator_id integer 20 NULL contact.contact_id
protocol_id integer 20 NULL protocol.protocol_id
analysis_id integer 20 UNIQUE, NOT NULL analysis.analysis_id
quantificationdate timestamp 0 current_timestamp
name text 64000 NULL UNIQUE
uri text 64000 NULL

Indices

Name Fields
quantification_idx1acquisition_id
quantification_idx2operator_id
quantification_idx3protocol_id
quantification_idx4analysis_id

Constraints

Type Fields
NOT NULLquantification_id
NOT NULLacquisition_id
FOREIGN KEYacquisition_id
FOREIGN KEYoperator_id
FOREIGN KEYprotocol_id
NOT NULLanalysis_id
FOREIGN KEYanalysis_id
UNIQUEname, 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 20 PRIMARY KEY, NOT NULL
quantification_id integer 20 UNIQUE, NOT NULL quantification.quantification_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
quantificationprop_idx1quantification_id
quantificationprop_idx2type_id

Constraints

Type Fields
NOT NULLquantificationprop_id
NOT NULLquantification_id
FOREIGN KEYquantification_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEquantification_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 20 PRIMARY KEY, NOT NULL
subject_id integer 20 UNIQUE, NOT NULL quantification.quantification_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
object_id integer 20 UNIQUE, NOT NULL quantification.quantification_id

Indices

Name Fields
quantification_relationship_idx1subject_id
quantification_relationship_idx2type_id
quantification_relationship_idx3object_id

Constraints

Type Fields
NOT NULLquantification_relationship_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLobject_id
FOREIGN KEYobject_id
UNIQUEsubject_id, object_id, type_id

control

Top
Comments:

================================================
TABLE: control
================================================
NULL
Field Name Data Type Size Default Value Other Foreign Key
control_id integer 20 PRIMARY KEY, NOT NULL
type_id integer 20 NOT NULL cvterm.cvterm_id
assay_id integer 20 NOT NULL assay.assay_id
tableinfo_id integer 20 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_idx1type_id
control_idx2assay_id
control_idx3tableinfo_id
control_idx4row_id

Constraints

Type Fields
NOT NULLcontrol_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLassay_id
FOREIGN KEYassay_id
NOT NULLtableinfo_id
FOREIGN KEYtableinfo_id
NOT NULLrow_id
NOT NULLrank

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 20 PRIMARY KEY, NOT NULL
feature_id integer 20 NULL UNIQUE feature.feature_id
arraydesign_id integer 20 UNIQUE, NOT NULL arraydesign.arraydesign_id
type_id integer 20 NULL cvterm.cvterm_id
dbxref_id integer 20 NULL dbxref.dbxref_id

Indices

Name Fields
element_idx1feature_id
element_idx2arraydesign_id
element_idx3type_id
element_idx4dbxref_id

Constraints

Type Fields
NOT NULLelement_id
FOREIGN KEYfeature_id
NOT NULLarraydesign_id
FOREIGN KEYarraydesign_id
FOREIGN KEYtype_id
FOREIGN KEYdbxref_id
UNIQUEfeature_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 20 PRIMARY KEY, NOT NULL
element_id integer 20 UNIQUE, NOT NULL element.element_id
quantification_id integer 20 UNIQUE, NOT NULL quantification.quantification_id
signal float 20 NOT NULL

Indices

Name Fields
elementresult_idx1element_id
elementresult_idx2quantification_id
elementresult_idx3signal

Constraints

Type Fields
NOT NULLelementresult_id
NOT NULLelement_id
FOREIGN KEYelement_id
NOT NULLquantification_id
FOREIGN KEYquantification_id
NOT NULLsignal
UNIQUEelement_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 20 PRIMARY KEY, NOT NULL
subject_id integer 20 UNIQUE, NOT NULL element.element_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
object_id integer 20 UNIQUE, NOT NULL element.element_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
element_relationship_idx1subject_id
element_relationship_idx2type_id
element_relationship_idx3object_id
element_relationship_idx4value

Constraints

Type Fields
NOT NULLelement_relationship_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLobject_id
FOREIGN KEYobject_id
NOT NULLrank
UNIQUEsubject_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 20 PRIMARY KEY, NOT NULL
subject_id integer 20 UNIQUE, NOT NULL elementresult.elementresult_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
object_id integer 20 UNIQUE, NOT NULL elementresult.elementresult_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
elementresult_relationship_idx1subject_id
elementresult_relationship_idx2type_id
elementresult_relationship_idx3object_id
elementresult_relationship_idx4value

Constraints

Type Fields
NOT NULLelementresult_relationship_id
NOT NULLsubject_id
FOREIGN KEYsubject_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLobject_id
FOREIGN KEYobject_id
NOT NULLrank
UNIQUEsubject_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 20 PRIMARY KEY, NOT NULL
contact_id integer 20 NOT NULL contact.contact_id
pub_id integer 20 NULL pub.pub_id
dbxref_id integer 20 NULL dbxref.dbxref_id
name text 64000 UNIQUE, NOT NULL
description text 64000 NULL

Indices

Name Fields
study_idx1contact_id
study_idx2pub_id
study_idx3dbxref_id

Constraints

Type Fields
NOT NULLstudy_id
NOT NULLcontact_id
FOREIGN KEYcontact_id
FOREIGN KEYpub_id
FOREIGN KEYdbxref_id
NOT NULLname
UNIQUEname

study_assay

Top
Comments:

================================================
TABLE: study_assay
================================================
NULL
Field Name Data Type Size Default Value Other Foreign Key
study_assay_id integer 20 PRIMARY KEY, NOT NULL
study_id integer 20 UNIQUE, NOT NULL study.study_id
assay_id integer 20 UNIQUE, NOT NULL assay.assay_id

Indices

Name Fields
study_assay_idx1study_id
study_assay_idx2assay_id

Constraints

Type Fields
NOT NULLstudy_assay_id
NOT NULLstudy_id
FOREIGN KEYstudy_id
NOT NULLassay_id
FOREIGN KEYassay_id
UNIQUEstudy_id, assay_id

studydesign

Top
Comments:

================================================
TABLE: studydesign
================================================
NULL
Field Name Data Type Size Default Value Other Foreign Key
studydesign_id integer 20 PRIMARY KEY, NOT NULL
study_id integer 20 NOT NULL study.study_id
description text 64000 NULL

Indices

Name Fields
studydesign_idx1study_id

Constraints

Type Fields
NOT NULLstudydesign_id
NOT NULLstudy_id
FOREIGN KEYstudy_id

studydesignprop

Top
Comments:

================================================
TABLE: studydesignprop
================================================
NULL
Field Name Data Type Size Default Value Other Foreign Key
studydesignprop_id integer 20 PRIMARY KEY, NOT NULL
studydesign_id integer 20 UNIQUE, NOT NULL studydesign.studydesign_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
studydesignprop_idx1studydesign_id
studydesignprop_idx2type_id

Constraints

Type Fields
NOT NULLstudydesignprop_id
NOT NULLstudydesign_id
FOREIGN KEYstudydesign_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEstudydesign_id, type_id, rank

studyfactor

Top
Comments:

================================================
TABLE: studyfactor
================================================
NULL
Field Name Data Type Size Default Value Other Foreign Key
studyfactor_id integer 20 PRIMARY KEY, NOT NULL
studydesign_id integer 20 NOT NULL studydesign.studydesign_id
type_id integer 20 NULL cvterm.cvterm_id
name text 64000 NOT NULL
description text 64000 NULL

Indices

Name Fields
studyfactor_idx1studydesign_id
studyfactor_idx2type_id

Constraints

Type Fields
NOT NULLstudyfactor_id
NOT NULLstudydesign_id
FOREIGN KEYstudydesign_id
FOREIGN KEYtype_id
NOT NULLname

studyfactorvalue

Top
Comments:

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

Indices

Name Fields
studyfactorvalue_idx1studyfactor_id
studyfactorvalue_idx2assay_id

Constraints

Type Fields
NOT NULLstudyfactorvalue_id
NOT NULLstudyfactor_id
FOREIGN KEYstudyfactor_id
NOT NULLassay_id
FOREIGN KEYassay_id
NOT NULLrank

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 20 PRIMARY KEY, NOT NULL
study_id integer 20 UNIQUE, NOT NULL study.study_id
type_id integer 20 UNIQUE, NOT NULL cvterm.cvterm_id
value text 64000 NULL
rank integer 10 0 UNIQUE, NOT NULL

Indices

Name Fields
studyprop_idx1study_id
studyprop_idx2type_id

Constraints

Type Fields
NOT NULLstudyprop_id
NOT NULLstudy_id
FOREIGN KEYstudy_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEstudy_id, type_id, rank

studyprop_feature

Top
Comments:

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

Indices

Name Fields
studyprop_feature_idx1studyprop_id
studyprop_feature_idx2feature_id

Constraints

Type Fields
NOT NULLstudyprop_feature_id
NOT NULLstudyprop_id
FOREIGN KEYstudyprop_id
NOT NULLfeature_id
FOREIGN KEYfeature_id
FOREIGN KEYtype_id
UNIQUEstudyprop_id, feature_id

Created by
SQL::Translator 0.11020