Difference between revisions of "Chado Phenotype Module at FlyBase"

From GMOD
Jump to: navigation, search
m (Tables)
m (Text replace - "<sql>" to "<syntaxhighlight lang="sql">")
Line 105: Line 105:
  
  
<sql>select count(*), c.name
+
<syntaxhighlight lang="sql">select count(*), c.name
 
   from phenotype p, cvterm t, cv c
 
   from phenotype p, cvterm t, cv c
 
  where t.cvterm_id = p.observable_id
 
  where t.cvterm_id = p.observable_id
Line 237: Line 237:
 
Find rows that use rank in the unique constraint.
 
Find rows that use rank in the unique constraint.
  
<sql>
+
<syntaxhighlight lang="sql">
 
select * from phenotype_cvterm pcv1
 
select * from phenotype_cvterm pcv1
 
  where exists (
 
  where exists (
Line 318: Line 318:
 
==== type_id ====
 
==== type_id ====
  
<sql>
+
<syntaxhighlight lang="sql">
 
select count(*), t.name, c.name
 
select count(*), t.name, c.name
 
   from phendesc p, cvterm t, cv c
 
   from phendesc p, cvterm t, cv c

Revision as of 23:32, 8 October 2012

Under Construction

This page or section is under construction.

Should be done before Jan 1, 2011

The Chado Phenotype module was developed at FlyBase.

To try and understand the existing phenotype module during the GMOD Evo Hackathon, participants spent some time exploring the phenotype tables at FlyBase. The notes from that exploration are on this page.

Phenotypes are not cleanly implemented in a single module. The tables are spread across the Phenotype and Genetic modules. This page discusses the tables from both modules. It also covers some related tables.

These notes reflect the state of the FlyBase Chado database during the first two weeks on November 2010.

Tables

Table rows at FlyBase
feature_phenotype 0
phenotype 10,925
phenotype_comparison 100,235
phenotype_cvterm 7,714
phenstatement 20,673
phendesc 112,390

And those are the only tables that have "phen" in their name.

Important Linked Tables

And that's it?

phenotype

<protect>
Table: phenotype
Module: Phenotype

A phenotypic statement, or a single atomic phenotypic observation, is a controlled sentence describing observable effects of non-wild type function. E.g. Obs=eye, attribute=color, cvalue=red.

phenotype columns
FK Name Type Description
phenotype_id serial PRIMARY KEY
uniquename text UNIQUE

NOT NULL

cvterm observable_id integer The entity: e.g. anatomy_part, biological_process.
cvterm attr_id integer Phenotypic attribute (quality, property, attribute, character) - drawn from PATO.
value text Value of attribute - unconstrained free text. Used only if cvalue_id is not appropriate.
cvterm cvalue_id integer Phenotype attribute value (state).
cvterm assay_id integer Evidence type.

Tables referencing phenotype via foreign key constraints:

</protect>

What is a Phenotype?

From conversation with Jim Balhoff and Matt Yoder:

A phenotype is what you observe.

This means that stage or time since conception is not part of the phenotype. It is part of the experiment. (Unless you are observing development rate, in which case, stage would be the phenotype.)

PATO at FlyBase?

That table description in the schema sounds like the old PATO, not the new EQ model.

Does this table implement PATO at FlyBase?

No

  • observable_id is the Entity. Usually points to anatomy or cellular_component CVs.
  • But attr_id is not used and cvalue_id points to compound terms in the FlyBase Miscellaneous CV.

phenotype @ Flybase

Counts
Table Rows 10925
observable_id is null 1
attr_id is null 1
cvalue_id is null 1
assay_id is null 1
value is null 10925

All the CVterm FKs that are null have the uniquename of "unspecified"

uniquename and observable_id

Most uniquenames are observable_id -> cvterm.name + blank or integer, but not when name is "unspecified", uniquename can be anything. Some examples:

  • viable
  • long lived | dominant
  • fertile
  • mitotic cell cycle defective | recessive | somatic clone
  • lethal | larval stage

There are 1473 of these (every one of them unspecified).


SELECT COUNT(*), c.name
  FROM phenotype p, cvterm t, cv c
 WHERE t.cvterm_id = p.observable_id
   AND t.cv_id = c.cv_id
 GROUP BY 2
 ORDER BY 1 DESC
 LIMIT 50;</sql>
 
 COUNT |           name
 ------+--------------------------
  8914 | FlyBase anatomy CV
  1473 | FlyBase miscellaneous CV
   436 | cellular_component
    91 | biological_process
     9 | property TYPE
     1 | SO
 (6 ROWS)
 
 
==== attr_id AND assay_id ====
 
attr_id IS supposed TO be "Phenotypic attribute (quality, property, attribute, character) - drawn from PATO." but IS NOT.
 
assay_id IS supposed TO be "Evidence type" but it IS NOT.
 
 
At FlyBase, IN ALL ROWS (EXCEPT the NULL one) BOTH OF these point TO cvterm "unspecified"
 
So, this IS NOT used FOR PATO at Flybase.  I'm not sure why it isn't NULL.
 
==== cvalue_id ====
 
Schema doc says "Phenotype attribute value (state)."
 
There are ONLY 118 DISTINCT VALUES.  SOME OF them:
 
* DNA repair defective
* MINUTE
* aging defective
* auditory perception defective
* auxotroph
* bang sensitive
* behavior defective
 
ALL FROM: FlyBase miscellaneous CV
 
==== VALUE ====
 
VALUE IS NULL IN every ROW IN FlyBase.
 
== {{ChadoModuleTableLink|Genetic|phenotype_comparison}} ==
 
<div class="quotebox">{{ChadoTable_phenotype_comparison}}</div>
 
This TABLE IS good FOR BINARY comparisons.  How broadly useful IS that?
 
=== {{ChadoModuleTableLink|Genetic|phenotype_comparison}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! TABLE ROWS
| 100,235
|-
| phenotype2_id IS NULL
| 0
|-
| phenotype1_id = phenotype2_id
| 42,510
|-
| genotype1_id = genotype2_id
| 0
|-
| environment1_id = environment2_id
| 99,293
|}
 
Every COLUMN but phenotype2_id IS NOT NULLABLE, AND that COLUMN has no NULLS IN it either.
 
==== environment[12]_id ====
 
The above counts imply a lot comparisons USING different genotypes IN the same environment.   '''No it does not.'''  IN every one OF those 99K comparisons, environment IS "unspecified".
 
So, FlyBase has known environments FOR 1% OF its comparisons.
 
==== phenotype[12]_id AND genotype[12]_id ====
 
The 42K comparisons WHERE the phenotypes are the same mean that the genotype did NOT CHANGE this phenotype.
 
==== type_id ====
 
type_id IS a phantom COLUMN.  It does NOT exist IN CURRENT Chado OR at FlyBase.
 
How do we say how they compare WITHOUT this COLUMN?
 
What makes two G/E/P pairs comparable?
 
== {{ChadoModuleTableLink|Phenotype|phenotype_cvterm}} ==
 
<div class="quotebox">{{ChadoTable_phenotype_cvterm}}</div>
 
Can imagine it just attaches a bag OF terms TO the phenotype.  There IS no semantics ON how the terms relate TO each other OR TO the phenotype.
 
=== {{ChadoModuleTableLink|Phenotype|phenotype_cvterm}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! TABLE ROWS
| 7714
|}
 
==== rank ====
Flybase's table def has a rank column, which is > 0 for about 2000 rows.  The rank column is part of standard Chado, but not listed on the web site.
 
There is a unique constraint on (phenotype_id, cvterm_id, rank).  Why?
 
==== cvterm_id ====
 
Links to terms in 5 different CVs
* FlyBase miscellaneous CV (75%)
* FlyBase anatomy CV
* SO
* FlyBase development CV (25%)
* biological_process
 
==== Semantics? ====
 
Find rows that use rank in the unique constraint.
 
<syntaxhighlight lang="sql">
select * from phenotype_cvterm pcv1
 where exists (
    select * from phenotype_cvterm pcv2
     where pcv1.phenotype_cvterm_id <> pcv2.phenotype_cvterm_id
       and pcv1.phenotype_id = pcv2.phenotype_id
       and pcv1.cvterm_id = pcv2.cvterm_id) limit 10;
</sql>
 phenotype_cvterm_id | phenotype_id | cvterm_id | rank
 --------------------+--------------+-----------+------
                8763 |        15842 |     60843 |    0
                8764 |        15842 |     60843 |    1
               12006 |        19433 |     60843 |    1
               12007 |        19433 |     60843 |    2
 (4 rows)
 
So, what does that mean?
 
Rank is used for some sort of code that is not stored in the database?
 
== {{ChadoModuleTableLink|Genetic|phenstatement}} ==
 
<div class="quotebox">{{ChadoTable_phenstatement}}</div>
 
=== {{ChadoModuleTableLink|Genetic|phenstatement}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! Table Rows
| 203673
|}
 
All columns are not null; all except PK are FKs.  Therefore phenstatements require
* genotype
* environment
* phenotype
* cvterm (type_id ?)
* pub
 
Well, sort of.
* type_id is "unspecified" for every row.
* 7% of pubs link to one with a blank title
* 97% of environments point to "unspecified"
 
There are 72K linked genotypes.  They use a custom markup language in the uniquename.
 
== {{ChadoModuleTableLink|Genetic|phendesc}} ==
 
<div class="quotebox">{{ChadoTable_phendesc}}</div>
 
You can't link directly FROM a {{ChadoModuleTableLink|Phenotype|phenotype}} TO {{ChadoTableName|phendesc}}.  You can link directly FROM a {{ChadoModuleTableLink|Genetic|phenstatement}} TO a {[ChadoTableName|phendesc}} USING (genotype_id, environment_id, AND pub_id).  Note that this does NOT take FULL advantage OF the UNIQUE INDEX ON those 3 COLUMNS plus type_id.
 
This TABLE establishes two things:
* A general comment about a SET OF phenstatement's.
* A general type for the overall phenotype.  See below for options at FlyBase.
 
This table means there can only be one phendesc for each environment/genotype/pub publication.  I think that makes sense.
 
It does mean we wont link to phenstatements with the wrong genotype/environment, but it also means we can orphan phendesc rows that don't JOIN WITH any phenstatements.
 
=== {{ChadoModuleTableLink|Genetic|phendesc}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! TABLE ROWS
| 112,390
|-
| description IS NULL
| 0
|}
 
No COLUMNS are NULLABLE.  There IS description, 4 FKs (genotype_id, environment_id, type_id, pub_id), AND the PK.
 
Description IS NOT UNIQUE WITH "Homozygous lethal." AND "homozygous lethal" making up ~1% OF records.
 
==== type_id ====
 
<syntaxhighlight lang="sql">
SELECT COUNT(*), t.name, c.name
  FROM phendesc p, cvterm t, cv c
 WHERE p.type_id = t.cvterm_id
   AND t.cv_id = c.cv_id
 GROUP BY 2,3
 ORDER BY 1 DESC
 LIMIT 50;
</sql>
 COUNT |           name            |     name
 ------+---------------------------+---------------
 71688 | single_mutant_pheno       | phendesc TYPE
 18648 | genetic_interaction_pheno | phendesc TYPE
 12809 | aberr_pheno               | phendesc TYPE
  7662 | interallele_comp          | phendesc TYPE
  1583 | xeno_interaction_pheno    | phendesc TYPE
 (5 ROWS)
 
==== pub_id ====
 
11,930 ROWS (about 10% OF ROWS) link TO the pub WITH no title.
 
==== environment_id ====
 
101,746 ROWS (OVER 90%) link TO "unspecified"
 
==== genotype_id ====
 
There IS wide distribution OF genotypes.  The top genotype has 122 phendesc records.
 
==== Description ====
 
Free form description OF phenotype.  Almost ALL entries are informative, AND NONE are NULL.
 
== {{ChadoModuleTableLink|Genetic|environment}} ==
 
Definition:
<div class="quotebox">{{ChadoTable_environment}}</div>
 
=== {{ChadoModuleTableLink|Genetic|environment}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! TABLE ROWS
|  15
|}
 
description IS NULL IN ALL ROWS.
 
Uniquename choice are:
 unspecified
 conditional qualifier
 temperature conditional
 drug conditional
 conditional tetracycline
 conditional RU486
 Drosophila cell culture
 IN transgenic Drosophila (intraspecific)
 IN transgenic Drosophila (allele OF one drosophilid species IN genome OF another drosophilid)
 IN transgenic Drosophila (allele OF FOREIGN species IN genome OF drosophilid)
 Whole-organism transient assay (intraspecific)
 Whole-organism transient assay (allele FROM one drosophilid species assayed IN another drosophilid)
 Whole-organism transient assay (allele OF FOREIGN species assayed IN drosophilid)
 conditional - heat sensitive
 conditional - cold sensitive
 
== {{ChadoModuleTableLink|Genetic|environment_cvterm}} ==
 
Definition:
<div class="quotebox">{{ChadoTable_environment_cvterm}}</div>
 
No semantics here.  My guess IS associates a list OF CV terms WITH an environment.
 
=== {{ChadoModuleTableLink|Genetic|environment_cvterm}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! TABLE ROWS
|  ?
|}
 
== {{ChadoModuleTableLink|Genetic|genotype}} ==
 
Definition:
<div class="quotebox">{{ChadoTable_genotype}}</div>
 
=== {{ChadoModuleTableLink|Genetic|genotype}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! TABLE ROWS
|  263,153
|-
| description IS NULL
| 263,153
|-
| name IS NULL
| 165,210
|-
| description AND name are NULL
| 165,210
|}
 
There IS no {{ChadoTableName|genotype_cvterm}} TABLE.
 
==== uniquename ====
 
Uniquename IS the ONLY informative COLUMN IS 2/3 OF the ROWS.  It uses a custom FlyBase encoding OF information.
 
==== name ====
 
IN the cases WHERE this IS NOT NULL it uses a custom FlyBase nomenclature that often includes one OR more FlyBase IDs.
 
== {{ChadoModuleTableLink|Genetic|feature_genotype}} ==
 
Definition:
<div class="quotebox">{{ChadoTable_feature_genotype}}</div>
 
This IS the TABLE that connects DATA TO features, finally.  The rank AND GROUP have SOME semantics.
 
=== {{ChadoModuleTableLink|Genetic|feature_genotype}} @ Flybase ===
 
{| class="wikitable"
!
! Counts
|-
! TABLE ROWS
|  551,551
|-
| chromosome_id IS NULL
| 0
|}
 
==== cvterm_id ====
 
ALL cvterm_ids are unspecified.  Every one OF them.  No doc ON this.
 
==== cgroup ====
 
: 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).
 
455K are 0;  After that it diminishes up TO 6, which ONLY has 5 records.
 
What does this mean?  It's not an FK to anywhere, just an integer that must be unique in combination with feature_id, genotype_id, chromosome, rank, cgroup, and cvterm_id (every non-PK column).
 
==== chromosome_id ====
 
Every record points to the same feature_id, which has the name "unspecified".  Is this supposed to be a denormalization optimization that they don't USE at FlyBase?
 
==== rank ====
: rank can be used FOR n-ploid organisms OR TO preserve ORDER.
 
[[Category:Phenotypes]]
[[Category:FlyBase]]
[[Category:Chado]]
[[Category:GMOD Evo Hackathon]]