Description of Schema


Tables

nd_geolocation
nd_experiment
nd_experiment_project
nd_experimentprop
nd_experiment_pub
nd_geolocationprop
nd_protocol
nd_reagent
nd_protocol_reagent
nd_protocolprop
nd_experiment_stock
nd_experiment_protocol
nd_experiment_phenotype
nd_experiment_genotype
nd_reagent_relationship
nd_reagentprop
nd_experiment_stockprop
nd_experiment_stock_dbxref
nd_experiment_dbxref
nd_experiment_contact
nd_experiment_analysis

nd_geolocation

Top
Comments:

=================================================================
Dependencies:
:import feature from sequence
:import cvterm from cv
:import pub from pub
:import phenotype from phenotype
:import organism from organism
:import genotype from genetic
:import contact from contact
:import project from project
:import stock from stock
:import synonym
=================================================================
this probably needs some work, depending on how cross-database we
want to be. In Postgres, at least, there are much better ways to
represent geo information.
================================================
TABLE: nd_geolocation
================================================
The geo-referencable location of the stock. NOTE: This entity is subject to change as a more general and possibly more OpenGIS-compliant geolocation module may be introduced into Chado.
Field Name Data Type Size Default Value Other Foreign Key
nd_geolocation_id integer 20 PRIMARY KEY, NOT NULL
description text 64000 A textual representation of the location, if this is the original georeference. Optional if the original georeference is available in lat/long coordinates.
latitude real 10 The decimal latitude coordinate of the georeference, using positive and negative sign to indicate N and S, respectively.
longitude real 10 The decimal longitude coordinate of the georeference, using positive and negative sign to indicate E and W, respectively.
geodetic_datum varchar 32 The geodetic system on which the geo-reference coordinates are based. For geo-references measured between 1984 and 2010, this will typically be WGS84.
altitude real 10 The altitude (elevation) of the location in meters. If the altitude is only known as a range, this is the average, and altitude_dev will hold half of the width of the range.

Indices

Name Fields
nd_geolocation_idx1latitude
nd_geolocation_idx2longitude
nd_geolocation_idx3altitude

Constraints

Type Fields
NOT NULLnd_geolocation_id

nd_experiment

Top
Comments:

================================================
TABLE: nd_experiment
================================================
This is the core table for the natural diversity module, representing each individual assay that is undertaken (this is usually *not* an entire experiment). Each nd_experiment should give rise to a single genotype or phenotype and be described via 1 (or more) protocols. Collections of assays that relate to each other should be linked to the same record in the project table.
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_id integer 20 PRIMARY KEY, NOT NULL
nd_geolocation_id integer 20 NOT NULL nd_geolocation.nd_geolocation_id
type_id integer 20 NOT NULL cvterm.cvterm_id

Indices

Name Fields
nd_experiment_idx1nd_geolocation_id
nd_experiment_idx2type_id

Constraints

Type Fields
NOT NULLnd_experiment_id
NOT NULLnd_geolocation_id
FOREIGN KEYnd_geolocation_id
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYnd_geolocation_id
FOREIGN KEYtype_id

nd_experiment_project

Top
Comments:

================================================
TABLE: nd_experiment_project
================================================
used to be nd_diversityexperiment_project
then was nd_assay_project
Used to group together related nd_experiment records. All nd_experiments should be linked to at least one project.
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_project_id integer 20 PRIMARY KEY, NOT NULL
project_id integer 20 UNIQUE, NOT NULL project.project_id
nd_experiment_id integer 20 UNIQUE, NOT NULL nd_experiment.nd_experiment_id

Indices

Name Fields
nd_experiment_project_idx1project_id
nd_experiment_project_idx2nd_experiment_id

Constraints

Type Fields
NOT NULLnd_experiment_project_id
NOT NULLproject_id
FOREIGN KEYproject_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
UNIQUEproject_id, nd_experiment_id
FOREIGN KEYproject_id
FOREIGN KEYnd_experiment_id

nd_experimentprop

Top
Comments:

================================================
TABLE: nd_experimentprop
================================================
An nd_experiment can have any number of slot-value property tags attached to it. This is an alternative to hardcoding a list of columns in the relational schema, and is completely extensible. There is a unique constraint, stockprop_c1, for the combination of stock_id, rank, and type_id. Multivalued property-value pairs must be differentiated by rank.
Field Name Data Type Size Default Value Other Foreign Key
nd_experimentprop_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 UNIQUE, NOT NULL nd_experiment.nd_experiment_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
nd_experimentprop_idx1nd_experiment_id
nd_experimentprop_idx2type_id

Constraints

Type Fields
NOT NULLnd_experimentprop_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEnd_experiment_id, type_id, rank
FOREIGN KEYnd_experiment_id
FOREIGN KEYtype_id

nd_experiment_pub

Top
Comments:

================================================
TABLE: nd_experiment_pub
================================================
Linking nd_experiment(s) to publication(s)
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_pub_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 UNIQUE, NOT NULL nd_experiment.nd_experiment_id
pub_id integer 20 UNIQUE, NOT NULL pub.pub_id

Indices

Name Fields
nd_experiment_pub_idx1nd_experiment_id
nd_experiment_pub_idx2pub_id

Constraints

Type Fields
NOT NULLnd_experiment_pub_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLpub_id
FOREIGN KEYpub_id
UNIQUEnd_experiment_id, pub_id

nd_geolocationprop

Top
Comments:

================================================
TABLE: nd_geolocationprop
================================================
Property/value associations for geolocations. This table can store the properties such as location and environment
Field Name Data Type Size Default Value Other Foreign Key
nd_geolocationprop_id integer 20 PRIMARY KEY, NOT NULL
nd_geolocation_id integer 20 UNIQUE, NOT NULL nd_geolocation.nd_geolocation_id
type_id integer 20 UNIQUE, NOT NULL, The name of the property as a reference to a controlled vocabulary term. cvterm.cvterm_id
value text 64000 NULL The value of the property.
rank integer 10 0 UNIQUE, NOT NULL, The rank of the property value, if the property has an array of values.

Indices

Name Fields
nd_geolocationprop_idx1nd_geolocation_id
nd_geolocationprop_idx2type_id

Constraints

Type Fields
NOT NULLnd_geolocationprop_id
NOT NULLnd_geolocation_id
FOREIGN KEYnd_geolocation_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEnd_geolocation_id, type_id, rank
FOREIGN KEYnd_geolocation_id
FOREIGN KEYtype_id

nd_protocol

Top
Comments:

================================================
TABLE: nd_protocol
================================================
A protocol can be anything that is done as part of the experiment.
Field Name Data Type Size Default Value Other Foreign Key
nd_protocol_id integer 20 PRIMARY KEY, NOT NULL
name varchar 255 UNIQUE, NOT NULL, The protocol name.
type_id integer 20 NOT NULL cvterm.cvterm_id

Indices

Name Fields
nd_protocol_idx1type_id

Constraints

Type Fields
NOT NULLnd_protocol_id
NOT NULLname
UNIQUEname
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYtype_id

nd_reagent

Top
Comments:

================================================
TABLE: nd_reagent
===============================================
A reagent such as a primer, an enzyme, an adapter oligo, a linker oligo. Reagents are used in genotyping experiments, or in any other kind of experiment.
Field Name Data Type Size Default Value Other Foreign Key
nd_reagent_id integer 20 PRIMARY KEY, NOT NULL
name varchar 80 NOT NULL, The name of the reagent. The name should be unique for a given type.
type_id integer 20 NOT NULL, The type of the reagent, for example linker oligomer, or forward primer. cvterm.cvterm_id
feature_id integer 20 NULL If the reagent is a primer, the feature that it corresponds to. More generally, the corresponding feature for any reagent that has a sequence that maps to another sequence. feature.feature_id

Indices

Name Fields
nd_reagent_idx1type_id
nd_reagent_idx2feature_id

Constraints

Type Fields
NOT NULLnd_reagent_id
NOT NULLname
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYfeature_id
FOREIGN KEYtype_id
FOREIGN KEYfeature_id

nd_protocol_reagent

Top
Comments:

================================================
TABLE: nd_protocol_reagent
================================================
Field Name Data Type Size Default Value Other Foreign Key
nd_protocol_reagent_id integer 20 PRIMARY KEY, NOT NULL
nd_protocol_id integer 20 NOT NULL nd_protocol.nd_protocol_id
reagent_id integer 20 NOT NULL nd_reagent.nd_reagent_id
type_id integer 20 NOT NULL cvterm.cvterm_id

Indices

Name Fields
nd_protocol_reagent_idx1nd_protocol_id
nd_protocol_reagent_idx2reagent_id
nd_protocol_reagent_idx3type_id

Constraints

Type Fields
NOT NULLnd_protocol_reagent_id
NOT NULLnd_protocol_id
FOREIGN KEYnd_protocol_id
NOT NULLreagent_id
FOREIGN KEYreagent_id
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYnd_protocol_id
FOREIGN KEYreagent_id
FOREIGN KEYtype_id

nd_protocolprop

Top
Comments:

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

Indices

Name Fields
nd_protocolprop_idx1nd_protocol_id
nd_protocolprop_idx2type_id

Constraints

Type Fields
NOT NULLnd_protocolprop_id
NOT NULLnd_protocol_id
FOREIGN KEYnd_protocol_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEnd_protocol_id, type_id, rank
FOREIGN KEYnd_protocol_id
FOREIGN KEYtype_id

nd_experiment_stock

Top
Comments:

================================================
TABLE: nd_experiment_stock
================================================
Part of a stock or a clone of a stock that is used in an experiment
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_stock_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 NOT NULL nd_experiment.nd_experiment_id
stock_id integer 20 NOT NULL, stock used in the extraction or the corresponding stock for the clone stock.stock_id
type_id integer 20 NOT NULL cvterm.cvterm_id

Indices

Name Fields
nd_experiment_stock_idx1nd_experiment_id
nd_experiment_stock_idx2stock_id
nd_experiment_stock_idx3type_id

Constraints

Type Fields
NOT NULLnd_experiment_stock_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLstock_id
FOREIGN KEYstock_id
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYnd_experiment_id
FOREIGN KEYstock_id
FOREIGN KEYtype_id

nd_experiment_protocol

Top
Comments:

================================================
TABLE: nd_experiment_protocol
================================================
Linking table: experiments to the protocols they involve.
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_protocol_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 NOT NULL nd_experiment.nd_experiment_id
nd_protocol_id integer 20 NOT NULL nd_protocol.nd_protocol_id

Indices

Name Fields
nd_experiment_protocol_idx1nd_experiment_id
nd_experiment_protocol_idx2nd_protocol_id

Constraints

Type Fields
NOT NULLnd_experiment_protocol_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLnd_protocol_id
FOREIGN KEYnd_protocol_id
FOREIGN KEYnd_experiment_id
FOREIGN KEYnd_protocol_id

nd_experiment_phenotype

Top
Comments:

================================================
TABLE: nd_experiment_phenotype
================================================
Linking table: experiments to the phenotypes they produce. There is a one-to-one relationship between an experiment and a phenotype since each phenotype record should point to one experiment. Add a new experiment_id for each phenotype record.
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_phenotype_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 UNIQUE, NOT NULL nd_experiment.nd_experiment_id
phenotype_id integer 20 UNIQUE, NOT NULL phenotype.phenotype_id

Indices

Name Fields
nd_experiment_phenotype_idx1nd_experiment_id
nd_experiment_phenotype_idx2phenotype_id

Constraints

Type Fields
NOT NULLnd_experiment_phenotype_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLphenotype_id
FOREIGN KEYphenotype_id
UNIQUEnd_experiment_id, phenotype_id
FOREIGN KEYnd_experiment_id
FOREIGN KEYphenotype_id

nd_experiment_genotype

Top
Comments:

================================================
TABLE: nd_experiment_genotype
================================================
Linking table: experiments to the genotypes they produce. There is a one-to-one relationship between an experiment and a genotype since each genotype record should point to one experiment. Add a new experiment_id for each genotype record.
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_genotype_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 UNIQUE, NOT NULL nd_experiment.nd_experiment_id
genotype_id integer 20 UNIQUE, NOT NULL genotype.genotype_id

Indices

Name Fields
nd_experiment_genotype_idx1nd_experiment_id
nd_experiment_genotype_idx2genotype_id

Constraints

Type Fields
NOT NULLnd_experiment_genotype_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLgenotype_id
FOREIGN KEYgenotype_id
UNIQUEnd_experiment_id, genotype_id
FOREIGN KEYnd_experiment_id
FOREIGN KEYgenotype_id

nd_reagent_relationship

Top
Comments:

================================================
TABLE: nd_reagent_relationship
================================================
Relationships between reagents. Some reagents form a group. i.e., they are used all together or not at all. Examples are adapter/linker/enzyme experiment reagents.
Field Name Data Type Size Default Value Other Foreign Key
nd_reagent_relationship_id integer 20 PRIMARY KEY, NOT NULL
subject_reagent_id integer 20 NOT NULL, The subject reagent in the relationship. In parent/child terminology, the subject is the child. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object. nd_reagent.nd_reagent_id
object_reagent_id integer 20 NOT NULL, The object reagent in the relationship. In parent/child terminology, the object is the parent. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object. nd_reagent.nd_reagent_id
type_id integer 20 NOT NULL, The type (or predicate) of the relationship. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object. cvterm.cvterm_id

Indices

Name Fields
nd_reagent_relationship_idx1subject_reagent_id
nd_reagent_relationship_idx2object_reagent_id
nd_reagent_relationship_idx3type_id

Constraints

Type Fields
NOT NULLnd_reagent_relationship_id
NOT NULLsubject_reagent_id
FOREIGN KEYsubject_reagent_id
NOT NULLobject_reagent_id
FOREIGN KEYobject_reagent_id
NOT NULLtype_id
FOREIGN KEYtype_id
FOREIGN KEYsubject_reagent_id
FOREIGN KEYobject_reagent_id
FOREIGN KEYtype_id

nd_reagentprop

Top
Comments:

================================================
TABLE: nd_reagentprop
================================================
Field Name Data Type Size Default Value Other Foreign Key
nd_reagentprop_id integer 20 PRIMARY KEY, NOT NULL
nd_reagent_id integer 20 UNIQUE, NOT NULL nd_reagent.nd_reagent_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
nd_reagentprop_idx1nd_reagent_id
nd_reagentprop_idx2type_id

Constraints

Type Fields
NOT NULLnd_reagentprop_id
NOT NULLnd_reagent_id
FOREIGN KEYnd_reagent_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEnd_reagent_id, type_id, rank
FOREIGN KEYnd_reagent_id
FOREIGN KEYtype_id

nd_experiment_stockprop

Top
Comments:

================================================
TABLE: nd_experiment_stockprop
================================================
Property/value associations for experiment_stocks. This table can store the properties such as treatment
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_stockprop_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_stock_id integer 20 UNIQUE, NOT NULL, The experiment_stock to which the property applies. nd_experiment_stock.nd_experiment_stock_id
type_id integer 20 UNIQUE, NOT NULL, The name of the property as a reference to a controlled vocabulary term. cvterm.cvterm_id
value text 64000 NULL The value of the property.
rank integer 10 0 UNIQUE, NOT NULL, The rank of the property value, if the property has an array of values.

Indices

Name Fields
nd_experiment_stockprop_idx1nd_experiment_stock_id
nd_experiment_stockprop_idx2type_id

Constraints

Type Fields
NOT NULLnd_experiment_stockprop_id
NOT NULLnd_experiment_stock_id
FOREIGN KEYnd_experiment_stock_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEnd_experiment_stock_id, type_id, rank
FOREIGN KEYnd_experiment_stock_id
FOREIGN KEYtype_id

nd_experiment_stock_dbxref

Top
Comments:

================================================
TABLE: nd_experiment_stock_dbxref
================================================
Cross-reference experiment_stock to accessions, images, etc
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_stock_dbxref_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_stock_id integer 20 NOT NULL nd_experiment_stock.nd_experiment_stock_id
dbxref_id integer 20 NOT NULL dbxref.dbxref_id

Indices

Name Fields
nd_experiment_stock_dbxref_idx1nd_experiment_stock_id
nd_experiment_stock_dbxref_idx2dbxref_id

Constraints

Type Fields
NOT NULLnd_experiment_stock_dbxref_id
NOT NULLnd_experiment_stock_id
FOREIGN KEYnd_experiment_stock_id
NOT NULLdbxref_id
FOREIGN KEYdbxref_id
FOREIGN KEYnd_experiment_stock_id
FOREIGN KEYdbxref_id

nd_experiment_dbxref

Top
Comments:

================================================
TABLE: nd_experiment_dbxref
===============================================
Cross-reference experiment to accessions, images, etc
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_dbxref_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 NOT NULL nd_experiment.nd_experiment_id
dbxref_id integer 20 NOT NULL dbxref.dbxref_id

Indices

Name Fields
nd_experiment_dbxref_idx1nd_experiment_id
nd_experiment_dbxref_idx2dbxref_id

Constraints

Type Fields
NOT NULLnd_experiment_dbxref_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLdbxref_id
FOREIGN KEYdbxref_id
FOREIGN KEYnd_experiment_id
FOREIGN KEYdbxref_id

nd_experiment_contact

Top
Comments:

================================================
TABLE: nd_experiment_contact
================================================
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_contact_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 NOT NULL nd_experiment.nd_experiment_id
contact_id integer 20 NOT NULL contact.contact_id

Indices

Name Fields
nd_experiment_contact_idx1nd_experiment_id
nd_experiment_contact_idx2contact_id

Constraints

Type Fields
NOT NULLnd_experiment_contact_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLcontact_id
FOREIGN KEYcontact_id
FOREIGN KEYnd_experiment_id
FOREIGN KEYcontact_id

nd_experiment_analysis

Top
Comments:

================================================
TABLE: nd_experiment_analysis
================================================
An analysis that is used in an experiment
Field Name Data Type Size Default Value Other Foreign Key
nd_experiment_analysis_id integer 20 PRIMARY KEY, NOT NULL
nd_experiment_id integer 20 NOT NULL nd_experiment.nd_experiment_id
analysis_id integer 20 NOT NULL analysis.analysis_id
type_id integer 20 NULL cvterm.cvterm_id

Indices

Name Fields
nd_experiment_analysis_idx1nd_experiment_id
nd_experiment_analysis_idx2analysis_id
nd_experiment_analysis_idx3type_id

Constraints

Type Fields
NOT NULLnd_experiment_analysis_id
NOT NULLnd_experiment_id
FOREIGN KEYnd_experiment_id
NOT NULLanalysis_id
FOREIGN KEYanalysis_id
FOREIGN KEYtype_id
FOREIGN KEYnd_experiment_id
FOREIGN KEYanalysis_id
FOREIGN KEYtype_id

Created by
SQL::Translator 0.11020