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_idx1uniquename
genotype_idx2name

Constraints

Type Fields
NOT NULLgenotype_id
NOT NULLuniquename
NOT NULLtype_id
FOREIGN KEYtype_id
UNIQUEuniquename

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_idx1feature_id
feature_genotype_idx2genotype_id

Constraints

Type Fields
NOT NULLfeature_genotype_id
NOT NULLfeature_id
FOREIGN KEYfeature_id
NOT NULLgenotype_id
FOREIGN KEYgenotype_id
FOREIGN KEYchromosome_id
NOT NULLrank
NOT NULLcgroup
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
UNIQUEfeature_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_idx1uniquename

Constraints

Type Fields
NOT NULLenvironment_id
NOT NULLuniquename
UNIQUEuniquename

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_idx1environment_id
environment_cvterm_idx2cvterm_id

Constraints

Type Fields
NOT NULLenvironment_cvterm_id
NOT NULLenvironment_id
FOREIGN KEYenvironment_id
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
UNIQUEenvironment_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_idx1genotype_id
phenstatement_idx2phenotype_id

Constraints

Type Fields
NOT NULLphenstatement_id
NOT NULLgenotype_id
FOREIGN KEYgenotype_id
NOT NULLenvironment_id
FOREIGN KEYenvironment_id
NOT NULLphenotype_id
FOREIGN KEYphenotype_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLpub_id
FOREIGN KEYpub_id
UNIQUEgenotype_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_idx1genotype_id
phendesc_idx2environment_id
phendesc_idx3pub_id

Constraints

Type Fields
NOT NULLphendesc_id
NOT NULLgenotype_id
FOREIGN KEYgenotype_id
NOT NULLenvironment_id
FOREIGN KEYenvironment_id
NOT NULLdescription
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLpub_id
FOREIGN KEYpub_id
UNIQUEgenotype_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_idx1genotype1_id
phenotype_comparison_idx2genotype2_id
phenotype_comparison_idx4pub_id

Constraints

Type Fields
NOT NULLphenotype_comparison_id
NOT NULLgenotype1_id
FOREIGN KEYgenotype1_id
NOT NULLenvironment1_id
FOREIGN KEYenvironment1_id
NOT NULLgenotype2_id
FOREIGN KEYgenotype2_id
NOT NULLenvironment2_id
FOREIGN KEYenvironment2_id
NOT NULLphenotype1_id
FOREIGN KEYphenotype1_id
FOREIGN KEYphenotype2_id
NOT NULLpub_id
FOREIGN KEYpub_id
NOT NULLorganism_id
FOREIGN KEYorganism_id
UNIQUEgenotype1_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_idx1phenotype_comparison_id
phenotype_comparison_cvterm_idx2cvterm_id

Constraints

Type Fields
NOT NULLphenotype_comparison_cvterm_id
NOT NULLphenotype_comparison_id
FOREIGN KEYphenotype_comparison_id
NOT NULLcvterm_id
FOREIGN KEYcvterm_id
NOT NULLpub_id
FOREIGN KEYpub_id
NOT NULLrank
UNIQUEphenotype_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_idx1genotype_id
genotypeprop_idx2type_id

Constraints

Type Fields
NOT NULLgenotypeprop_id
NOT NULLgenotype_id
FOREIGN KEYgenotype_id
NOT NULLtype_id
FOREIGN KEYtype_id
NOT NULLrank
UNIQUEgenotype_id, type_id, rank

Created by
SQL::Translator 0.11020