# 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 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 | 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_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 | 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_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 | 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_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 | 20 |  | 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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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 | 20 |  | PRIMARY KEY, NOT NULL |
study_id | integer | 20 |  | 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 | 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_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 | 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_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 | 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_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 | 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_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 | 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_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.11020