# Description of Schema

## Tables

genotype
feature_genotype
environment
environment_cvterm
phenstatement
phendesc
phenotype_comparison
phenotype_comparison_cvterm
genotypeprop

### genotype

Top

Comments:
$Id: genetic.sql,v 1.31 2008-08-25 19:53:14 scottcain Exp $
==========================================
Chado genetics module
changes 2011-05-31
added type_id to genotype (can be null for backward compatibility)
added genotypeprop table
2006-04-11
split out phenotype tables into phenotype module
redesigned 2003-10-28
changes 2003-11-10:
incorporating suggestions to make everything a gcontext; use
gcontext_relationship to make some gcontexts derivable from others. we
would incorporate environment this way - just add the environment
descriptors as properties of the child gcontext
changes 2004-06 (Documented by DE: 10-MAR-2005):
Many, including rename of gcontext to genotype, split
phenstatement into phenstatement & phenotype, created environment
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
============
DEPENDENCIES
============
:import feature from sequence
:import phenotype from phenotype
:import cvterm from cv
:import pub from pub
:import dbxref from db
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
================================================
TABLE: genotype
================================================
Genetic context. A genotype is defined by a collection of features, mutations, balancers, deficiencies, haplotype blocks, or engineered constructs.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
genotype_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
name | text | 64000 |  | Optional alternative name for a genotype, for display purposes. |
uniquename | text | 64000 |  | UNIQUE, NOT NULL, The unique name for a genotype; typically derived from the features making up the genotype. |
description | text | 64000 |  |  |
type_id | integer | 20 |  | NOT NULL | cvterm.cvterm_id

### Indices

Name | Fields
--- | ---
genotype_idx1 | uniquename
genotype_idx2 | name

### Constraints

Type | Fields
--- | ---
NOT NULL | genotype_id
NOT NULL | uniquename
NOT NULL | type_id
FOREIGN KEY | type_id
UNIQUE | uniquename

### feature_genotype

Top

Comments:
===============================================
TABLE: feature_genotype
================================================
NULL

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
feature_genotype_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
feature_id | integer | 20 |  | UNIQUE, NOT NULL | feature.feature_id
genotype_id | integer | 20 |  | UNIQUE, NOT NULL | genotype.genotype_id
chromosome_id | integer | 20 |  | UNIQUE, A feature of SO type "chromosome". | feature.feature_id
rank | integer | 10 |  | UNIQUE, NOT NULL, rank can be used for n-ploid organisms or to preserve order. |
cgroup | integer | 10 |  | UNIQUE, NOT NULL, Spatially distinguishable group. group can be used for distinguishing the chromosomal groups, for example (RNAi products and so on can be treated as different groups, as they do not fall on a particular chromosome). |
cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id

### Indices

Name | Fields
--- | ---
feature_genotype_idx1 | feature_id
feature_genotype_idx2 | genotype_id

### Constraints

Type | Fields
--- | ---
NOT NULL | feature_genotype_id
NOT NULL | feature_id
FOREIGN KEY | feature_id
NOT NULL | genotype_id
FOREIGN KEY | genotype_id
FOREIGN KEY | chromosome_id
NOT NULL | rank
NOT NULL | cgroup
NOT NULL | cvterm_id
FOREIGN KEY | cvterm_id
UNIQUE | feature_id, genotype_id, cvterm_id, chromosome_id, rank, cgroup

### environment

Top

Comments:
================================================
TABLE: environment
================================================
The environmental component of a phenotype description.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
environment_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
uniquename | text | 64000 |  | UNIQUE, NOT NULL |
description | text | 64000 |  |  |

### Indices

Name | Fields
--- | ---
environment_idx1 | uniquename

### Constraints

Type | Fields
--- | ---
NOT NULL | environment_id
NOT NULL | uniquename
UNIQUE | uniquename

### environment_cvterm

Top

Comments:
================================================
TABLE: environment_cvterm
================================================
NULL

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
environment_cvterm_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
environment_id | integer | 20 |  | UNIQUE, NOT NULL | environment.environment_id
cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id

### Indices

Name | Fields
--- | ---
environment_cvterm_idx1 | environment_id
environment_cvterm_idx2 | cvterm_id

### Constraints

Type | Fields
--- | ---
NOT NULL | environment_cvterm_id
NOT NULL | environment_id
FOREIGN KEY | environment_id
NOT NULL | cvterm_id
FOREIGN KEY | cvterm_id
UNIQUE | environment_id, cvterm_id

### phenstatement

Top

Comments:
================================================
TABLE: phenstatement
================================================
Phenotypes are things like "larval lethal". Phenstatements are things like "dpp-1 is recessive larval lethal". So essentially phenstatement is a linking table expressing the relationship between genotype, environment, and phenotype.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phenstatement_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
genotype_id | integer | 20 |  | UNIQUE, NOT NULL | genotype.genotype_id
environment_id | integer | 20 |  | UNIQUE, NOT NULL | environment.environment_id
phenotype_id | integer | 20 |  | UNIQUE, NOT NULL | phenotype.phenotype_id
type_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
phenstatement_idx1 | genotype_id
phenstatement_idx2 | phenotype_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phenstatement_id
NOT NULL | genotype_id
FOREIGN KEY | genotype_id
NOT NULL | environment_id
FOREIGN KEY | environment_id
NOT NULL | phenotype_id
FOREIGN KEY | phenotype_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | genotype_id, phenotype_id, environment_id, type_id, pub_id

### phendesc

Top

Comments:
================================================
TABLE: phendesc
================================================
A summary of a _set_ of phenotypic statements for any one gcontext made in any one publication.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phendesc_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
genotype_id | integer | 20 |  | UNIQUE, NOT NULL | genotype.genotype_id
environment_id | integer | 20 |  | UNIQUE, NOT NULL | environment.environment_id
description | text | 64000 |  | NOT NULL |
type_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id

### Indices

Name | Fields
--- | ---
phendesc_idx1 | genotype_id
phendesc_idx2 | environment_id
phendesc_idx3 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phendesc_id
NOT NULL | genotype_id
FOREIGN KEY | genotype_id
NOT NULL | environment_id
FOREIGN KEY | environment_id
NOT NULL | description
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
UNIQUE | genotype_id, environment_id, type_id, pub_id

### phenotype_comparison

Top

Comments:
================================================
TABLE: phenotype_comparison
================================================
Comparison of phenotypes e.g., genotype1/environment1/phenotype1 "non-suppressible" with respect to genotype2/environment2/phenotype2.

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phenotype_comparison_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
genotype1_id | integer | 20 |  | UNIQUE, NOT NULL | genotype.genotype_id
environment1_id | integer | 20 |  | UNIQUE, NOT NULL | environment.environment_id
genotype2_id | integer | 20 |  | UNIQUE, NOT NULL | genotype.genotype_id
environment2_id | integer | 20 |  | UNIQUE, NOT NULL | environment.environment_id
phenotype1_id | integer | 20 |  | UNIQUE, NOT NULL | phenotype.phenotype_id
phenotype2_id | integer | 20 |  |  | phenotype.phenotype_id
pub_id | integer | 20 |  | UNIQUE, NOT NULL | pub.pub_id
organism_id | integer | 20 |  | NOT NULL | organism.organism_id

### Indices

Name | Fields
--- | ---
phenotype_comparison_idx1 | genotype1_id
phenotype_comparison_idx2 | genotype2_id
phenotype_comparison_idx4 | pub_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phenotype_comparison_id
NOT NULL | genotype1_id
FOREIGN KEY | genotype1_id
NOT NULL | environment1_id
FOREIGN KEY | environment1_id
NOT NULL | genotype2_id
FOREIGN KEY | genotype2_id
NOT NULL | environment2_id
FOREIGN KEY | environment2_id
NOT NULL | phenotype1_id
FOREIGN KEY | phenotype1_id
FOREIGN KEY | phenotype2_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
NOT NULL | organism_id
FOREIGN KEY | organism_id
UNIQUE | genotype1_id, environment1_id, genotype2_id, environment2_id, phenotype1_id, pub_id

### phenotype_comparison_cvterm

Top

Comments:
================================================
TABLE: phenotype_comparison_cvterm
================================================

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
phenotype_comparison_cvterm_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
phenotype_comparison_id | integer | 20 |  | UNIQUE, NOT NULL | phenotype_comparison.phenotype_comparison_id
cvterm_id | integer | 20 |  | UNIQUE, NOT NULL | cvterm.cvterm_id
pub_id | integer | 20 |  | NOT NULL | pub.pub_id
rank | integer | 10 | 0 | NOT NULL |

### Indices

Name | Fields
--- | ---
phenotype_comparison_cvterm_idx1 | phenotype_comparison_id
phenotype_comparison_cvterm_idx2 | cvterm_id

### Constraints

Type | Fields
--- | ---
NOT NULL | phenotype_comparison_cvterm_id
NOT NULL | phenotype_comparison_id
FOREIGN KEY | phenotype_comparison_id
NOT NULL | cvterm_id
FOREIGN KEY | cvterm_id
NOT NULL | pub_id
FOREIGN KEY | pub_id
NOT NULL | rank
UNIQUE | phenotype_comparison_id, cvterm_id

### genotypeprop

Top

Comments:
================================================
TABLE: genotypeprop
================================================

Field Name | Data Type | Size | Default Value | Other | Foreign Key
--- | --- | --- | --- | --- | ---
genotypeprop_id | integer | 20 |  | PRIMARY KEY, NOT NULL |
genotype_id | integer | 20 |  | UNIQUE, NOT NULL | genotype.genotype_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
--- | ---
genotypeprop_idx1 | genotype_id
genotypeprop_idx2 | type_id

### Constraints

Type | Fields
--- | ---
NOT NULL | genotypeprop_id
NOT NULL | genotype_id
FOREIGN KEY | genotype_id
NOT NULL | type_id
FOREIGN KEY | type_id
NOT NULL | rank
UNIQUE | genotype_id, type_id, rank

Created by SQL::Translator 0.11020