GO Database Schema Autodoc

This is the GO Database Schema documentation, in Autodoc format. Everything on this page has been automatically generated from the schema metadata. For more background on the schema, please read GO Database documentation.

note on modules: The tables are partitioned into modules for the purposes of Autodoc. These modules are invisible in the actual instantiations of the database, so the table "go_graph.term" is just simply "term"

This documentation was generated on 2008-04-11

• go_annotation_reports
• annotated_publication_total
• annotated_publication_total_by_evidence_code_non_additive
• annotated_total_gps_by_evidence_code_non_additive
• association_contradiction
• association_contradiction_count_by_ontology
• association_contradiction_direct
• association_contradiction_direct_count_by_ontology
• association_count_by_association_qualifier
• association_total_by_evidence_code
• association_total_by_evidence_code_and_species
• avg_total_annotations_per_gp_by_db
• avg_total_nonroot_annotations_per_gp_by_db
• avg_total_nonroot_pubs_per_gp_by_db
• avg_total_nonroot_terms_per_gp_by_db
• avg_total_nonroot_transitive_terms_per_gp_by_db
• avg_total_pubs_per_gp_by_db
• avg_total_terms_per_gp_by_db
• avg_total_transitive_terms_per_gp_by_db
• evidence_dbxref_summary
• evidence_pub_dbxref_summary
• gene_product_dbxref_summary
• iea_annotated_total_gps
• iea_or_iss_annotated_total_gps
• non_iea_annotated_total_gps
• non_iea_annotated_total_gps_by_dbname
• non_iea_or_iss_annotated_total_gps
• seq_dbxref_summary
• term_association_count_by_association_qualifier
• term_dbxref_summary
• total_annotated_entities_by_dbname_and_type
• total_annotations_per_gp
• total_gps_by_dbname
• total_nonroot_annotations_per_gp
• total_nonroot_pubs_per_gp
• total_nonroot_terms_per_gp
• total_nonroot_transitive_terms_per_gp
• total_nonroot_transitive_terms_per_gp_pair
• total_pubs_per_gp
• total_terms_per_gp
• total_transitive_terms_per_gp
• go_associations
• association
• association_property
• association_qualifier
• association_species_qualifier
• evidence
• evidence_dbxref
• gene_product
• gene_product_subset
• gene_product_synonym
• species
• go_audit
• instance_data
• source_audit
• term_audit
• go_evidence_views
• association_evidence_with
• association_inference_candidate_pair
• db_evidence_summary
• ic_evidence
• iss_annotation_to_nas_direct
• iss_annotation_to_nas_direct_without
• nd_evidence
• stale_iss_annotation
• go_general
• db
• dbxref
• go_graph
• term
• term2term
• go_graph_views
• avg_max_distance_to_leaf_term_by_db
• avg_max_distance_to_leaf_term_by_db_and_ontology
• avg_max_distance_to_leaf_term_by_db_and_species
• distance_to_leaf_stats_by_term
• distance_to_root_stats_by_term
• leaf_node
• max_distance_to_leaf_by_term
• max_distance_to_root_by_term
• non_root_term
• path_to_leaf
• path_to_root
• root_term
• term_ancestor
• term_descendent
• term_having_max_delta_distance_to_root
• term_having_max_max_distance_to_root
• term_having_most_paths_to_root
• total_paths_to_root_by_term
• transitive_association
• go_homology
• gene_product_homolset
• homolset
• go_meta
• term2term_metadata
• term_dbxref
• term_definition
• term_subset
• term_synonym
• go_obd_bridge
• avg_annotation_entropy_by_annotated_entity
• avg_annotation_entropy_by_annotation_source
• avg_annotation_entropy_by_annotation_source_and_class_source
• avg_annotation_entropy_by_annotation_source_and_organism
• class_node_entropy
• count_of_annotated_entity
• count_of_annotated_entity_by_class_node_and_evidence
• implied_link
• node
• node_max_depth
• resnick_similarity
• xxxxxxxxxx_count_of_annotated_entity_by_class_node_and_evidence
• go_optimisations
• gene_product_count
• graph_path
• go_prejoined_views
• association_evidence
• association_j_evidence
• association_j_evidence_j_gene_product
• gene_product_j_dbxref
• gene_product_j_dbxref_via_seq
• gene_product_j_gene_product_synonym
• gene_product_with_term_pair_via_graph
• term_j_association
• term_j_association_j_evidence_j_gene_product
• term_j_association_j_gene_product
• term_j_association_j_gene_product_via_graph
• term_j_association_j_species_summary_via_graph
• term_j_association_j_species_via_graph
• term_j_association_via_graph
• term_j_term
• go_refgenomes_views
• avg_total_gps_by_homolset_and_ontol
• gene_product_in_refg_subset
• gene_product_with_subset
• gp_outlier_annotation
• gp_outlier_annotation_full_report
• gp_partial_outlier_annotation_nothing_above
• gp_partial_outlier_annotation_nothing_below
• homolset_annotation
• refg_total_transitive_terms
• refg_with_nd
• subsumed_by_association
• subsumed_by_noniea_association
• subsumer_of_association
• subsumer_of_noniea_association
• total_gps_by_homolset_and_term
• trusted_evidence
• go_sequence
• gene_product_seq
• seq
• seq_dbxref
• seq_property
• go_stats_views
• annotated_gp_total_by_code
• gene_product_count2
• implied_annotation
• implied_negative_annotation
• term_correlation_via_transitive_annotation
• go_taxon_views
• annotated_species
• annotated_species_id
• annotated_species_lacks_term
• species_has_term
• species_has_term_d
• species_lacks_term
• species_lacks_term_d
• public

Schema go_annotation_reports

View: go_annotation_reports.annotated_publication_total

ungrouped: single number

SELECT count
(DISTINCT evidence.dbxref_id) AS total 
FROM go_associations.evidence;

Index - Schema go_annotation_reports

View: go_annotation_reports.annotated_publication_total_by_evidence_code_non_additive

non-additive: summing the totals does not give the total number of publications; this is because a publication may describe two or more distinct pieces of evidence

SELECT evidence.code
, count
(DISTINCT evidence.dbxref_id) AS total 
FROM go_associations.evidence 
GROUP BY evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.annotated_total_gps_by_evidence_code_non_additive

non-additive: summing the totals does not give the total number of gene products; this is because they are double counted through distinct annotations using different codes

SELECT evidence.code
, count
(DISTINCT association.gene_product_id) AS total 
FROM (go_associations.association 
  JOIN go_associations.evidence 
    ON (
           (evidence.association_id = association.id)
     )
)
GROUP BY evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction

SELECT term_pos.id AS term_pos_id
, term_pos.acc AS term_pos_acc
, term_pos.name AS term_pos_name
, term_pos.term_type AS term_pos_term_type
, term_neg.id AS term_neg_id
, term_neg.acc AS term_neg_acc
, term_neg.name AS term_neg_name
, term_neg.term_type AS term_neg_term_type
, a_pos.id AS a_pos_id
, gp.id AS gp_id
, gp.symbol AS gp_symbol
, gp.dbxref_id AS gp_dbxref_id
, gp.species_id AS gp_species_id
, a_neg.id AS a_neg_id 
FROM (
     (
           (
                 (
                       (go_graph.term term_pos 
                          JOIN go_associations.association a_pos 
                            ON (
                                   (term_pos.id = a_pos.term_id)
                             )
                       )
                    JOIN go_optimisations.graph_path 
                      ON (
                             (term_pos.id = graph_path.term2_id)
                       )
                 )
              JOIN go_associations.association a_neg 
                ON (
                       (graph_path.term1_id = a_neg.term_id)
                 )
           )
        JOIN go_associations.gene_product gp 
          ON (
                 (
                       (a_neg.gene_product_id = gp.id)
                     AND (a_pos.gene_product_id = gp.id)
                 )
           )
     )
  JOIN go_graph.term term_neg 
    ON (
           (term_neg.id = a_neg.term_id)
     )
)
WHERE (
     (a_pos.is_not = 0)
   AND (a_neg.is_not = 1)
);

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction_count_by_ontology

summary of association_contradiction, grouped by ontology

SELECT association_contradiction.term_pos_term_type
, count
(DISTINCT association_contradiction.gp_id) AS count 
FROM go_annotation_reports.association_contradiction 
GROUP BY association_contradiction.term_pos_term_type;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction_direct

APPARENT contradictions in associations, based on the NOT column. note that these do not genuinely contradict as annotation is context-specific

SELECT term.id
, term.name
, term.term_type
, term.acc
, term.is_obsolete
, term.is_root
, a_pos.id AS a_pos_id
, gp.id AS gp_id
, gp.symbol AS gp_symbol
, gp.dbxref_id AS gp_dbxref_id
, gp.species_id AS gp_species_id
, a_neg.id AS a_neg_id 
FROM (
     (
           (go_graph.term 
              JOIN go_associations.association a_pos 
                ON (
                       (term.id = a_pos.term_id)
                 )
           )
        JOIN go_associations.gene_product gp 
          ON (
                 (a_pos.gene_product_id = gp.id)
           )
     )
  JOIN go_associations.association a_neg 
    ON (
           (
                 (term.id = a_neg.term_id)
               AND (a_neg.gene_product_id = gp.id)
           )
     )
)
WHERE (
     (a_pos.is_not = 0)
   AND (a_neg.is_not = 1)
);

Index - Schema go_annotation_reports

View: go_annotation_reports.association_contradiction_direct_count_by_ontology

SELECT association_contradiction_direct.term_type
, count
(DISTINCT association_contradiction_direct.gp_id) AS count 
FROM go_annotation_reports.association_contradiction_direct 
GROUP BY association_contradiction_direct.term_type;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_count_by_association_qualifier

SELECT qterm.acc AS qualifier
, count
(DISTINCT aq.association_id) AS n_associations 
FROM (go_associations.association_qualifier aq 
  JOIN go_graph.term qterm 
    ON (
           (aq.term_id = qterm.id)
     )
)
GROUP BY qterm.acc;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_total_by_evidence_code

this total IS additive

SELECT evidence.code
, count
(DISTINCT evidence.association_id) AS total 
FROM go_associations.evidence 
GROUP BY evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.association_total_by_evidence_code_and_species

SELECT species.ncbi_taxa_id
, species.genus
, species.species
, species.common_name
, evidence.code
, count
(DISTINCT evidence.association_id) AS total_associations 
FROM (
     (
           (go_associations.evidence 
              JOIN go_associations.association 
                ON (
                       (evidence.association_id = association.id)
                 )
           )
        JOIN go_associations.gene_product 
          ON (
                 (association.gene_product_id = gene_product.id)
           )
     )
  JOIN go_associations.species 
    ON (
           (gene_product.species_id = species.id)
     )
)
GROUP BY species.ncbi_taxa_id
, species.genus
, species.species
, species.common_name
, evidence.code;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_annotations_per_gp_by_db

SELECT dbxref.xref_dbname
, avg
(aa.total_annotations) AS avg_total_annotations 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_annotations_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_annotations_per_gp_by_db

SELECT dbxref.xref_dbname
, avg
(aa.total_annotations) AS avg_total_annotations 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_nonroot_annotations_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_pubs_per_gp_by_db

SELECT dbxref.xref_dbname
, avg
(aa.total_pubs) AS avg_total_pubs 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_nonroot_pubs_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_terms_per_gp_by_db

as avg_total_terms_per_gp_by_db, excluding direct annotations to root

SELECT dbxref.xref_dbname
, avg
(aa.total_terms) AS avg_total_terms 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_nonroot_terms_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_nonroot_transitive_terms_per_gp_by_db

SELECT dbxref.xref_dbname
, avg
(aa.total_transitive_terms) AS avg_total_transitive_terms 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_nonroot_transitive_terms_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_pubs_per_gp_by_db

SELECT dbxref.xref_dbname
, avg
(aa.total_pubs) AS avg_total_pubs 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_pubs_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_terms_per_gp_by_db

average term coverage broken down by annotation DB here, term coverage is the DIRECT term count for that gene product see also: avg_total_transitive_terms_per_gp_by_db

SELECT dbxref.xref_dbname
, avg
(aa.total_terms) AS avg_total_terms 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_terms_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.avg_total_transitive_terms_per_gp_by_db

SELECT dbxref.xref_dbname
, avg
(aa.total_transitive_terms) AS avg_total_transitive_terms 
FROM (
     (go_general.dbxref 
        JOIN go_associations.gene_product 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_annotation_reports.total_transitive_terms_per_gp aa 
    ON (
           (aa.gene_product_id = gene_product.id)
     )
)
GROUP BY dbxref.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.evidence_dbxref_summary

what kind of dbxrefs links to evidences on with WITH field: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

SELECT d.xref_dbname
, count
(*) AS num_evidence_dbxrefs 
FROM (go_associations.evidence_dbxref x 
  JOIN go_general.dbxref d 
    ON (
           (x.dbxref_id = d.id)
     )
)
GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.evidence_pub_dbxref_summary

what kind of dbxrefs links to evidence as the primary publication: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

SELECT d.xref_dbname
, count
(*) AS num_evidence_dbxrefs 
FROM (go_associations.evidence x 
  JOIN go_general.dbxref d 
    ON (
           (x.dbxref_id = d.id)
     )
)
GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.gene_product_dbxref_summary

what kind of dbxrefs links to gene_products: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

SELECT d.xref_dbname
, count
(*) AS num_gene_product_dbxrefs 
FROM (go_associations.gene_product x 
  JOIN go_general.dbxref d 
    ON (
           (x.dbxref_id = d.id)
     )
)
GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.iea_annotated_total_gps

ungrouped: single number

SELECT count
(DISTINCT association.gene_product_id) AS total 
FROM (go_associations.association 
  JOIN go_associations.evidence 
    ON (
           (evidence.association_id = association.id)
     )
)
WHERE (
     (evidence.code)::text = 'IEA'::text
);

Index - Schema go_annotation_reports

View: go_annotation_reports.iea_or_iss_annotated_total_gps

ungrouped: single number

SELECT count
(DISTINCT association.gene_product_id) AS total 
FROM (go_associations.association 
  JOIN go_associations.evidence 
    ON (
           (evidence.association_id = association.id)
     )
)
WHERE (
     (
           (evidence.code)::text = 'IEA'::text
     )
    OR (
           (evidence.code)::text = 'ISS'::text
     )
);

Index - Schema go_annotation_reports

View: go_annotation_reports.non_iea_annotated_total_gps

ungrouped: single number

SELECT count
(DISTINCT association.gene_product_id) AS total 
FROM (go_associations.association 
  JOIN go_associations.evidence 
    ON (
           (evidence.association_id = association.id)
     )
)
WHERE (
     (evidence.code)::text <> 'IEA'::text
);

Index - Schema go_annotation_reports

View: go_annotation_reports.non_iea_annotated_total_gps_by_dbname

slow in mysql5.0

SELECT dbxref.xref_dbname
, count
(DISTINCT gene_product.id) AS total 
FROM (
     (
           (go_associations.gene_product 
              JOIN go_general.dbxref 
                ON (
                       (dbxref.id = gene_product.dbxref_id)
                 )
           )
        JOIN go_associations.association 
          ON (
                 (gene_product.id = association.gene_product_id)
           )
     )
  JOIN go_associations.evidence 
    ON (
           (evidence.association_id = association.id)
     )
)
WHERE (
     (evidence.code)::text <> 'IEA'::text
)
GROUP BY dbxref.xref_dbname 
ORDER BY count
(DISTINCT gene_product.id);

Index - Schema go_annotation_reports

View: go_annotation_reports.non_iea_or_iss_annotated_total_gps

ungrouped: single number

SELECT count
(DISTINCT association.gene_product_id) AS total 
FROM (go_associations.association 
  JOIN go_associations.evidence 
    ON (
           (evidence.association_id = association.id)
     )
)
WHERE (
     (
           (evidence.code)::text <> 'IEA'::text
     )
   AND (
           (evidence.code)::text <> 'ISS'::text
     )
);

Index - Schema go_annotation_reports

View: go_annotation_reports.seq_dbxref_summary

what kind of dbxrefs links to seqs: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

SELECT d.xref_dbname
, count
(*) AS num_seq_dbxrefs 
FROM (go_sequence.seq_dbxref x 
  JOIN go_general.dbxref d 
    ON (
           (x.dbxref_id = d.id)
     )
)
GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.term_association_count_by_association_qualifier

SELECT term.acc
, term.name
, term.term_type
, qterm.acc AS qualifier
, count
(DISTINCT aq.association_id) AS n_associations 
FROM (
     (
           (go_associations.association_qualifier aq 
              JOIN go_graph.term qterm 
                ON (
                       (aq.term_id = qterm.id)
                 )
           )
        JOIN go_associations.association 
          ON (
                 (aq.association_id = association.id)
           )
     )
  JOIN go_graph.term 
    ON (
           (association.term_id = term.id)
     )
)
GROUP BY term.acc
, term.name
, term.term_type
, qterm.acc;

Index - Schema go_annotation_reports

View: go_annotation_reports.term_dbxref_summary

what kind of dbxrefs links to terms: grouped by database type Many different tables link to the general purpose dbxref table. This summarises one kind of link

SELECT d.xref_dbname
, count
(*) AS num_term_dbxrefs 
FROM (go_meta.term_dbxref x 
  JOIN go_general.dbxref d 
    ON (
           (x.dbxref_id = d.id)
     )
)
GROUP BY d.xref_dbname;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_annotated_entities_by_dbname_and_type

SELECT dbxref.xref_dbname
, tt.acc
, count
(*) AS total_gps 
FROM (
     (go_associations.gene_product 
        JOIN go_general.dbxref 
          ON (
                 (dbxref.id = gene_product.dbxref_id)
           )
     )
  JOIN go_graph.term tt 
    ON (
           (tt.id = gene_product.type_id)
     )
)
GROUP BY dbxref.xref_dbname
, tt.acc 
ORDER BY dbxref.xref_dbname
, tt.acc;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_annotations_per_gp

count of distinct direct annotations broken down by gene product note: does not correspnd to lines in gene_association file

SELECT association.gene_product_id
, count
(DISTINCT association.id) AS total_annotations 
FROM go_associations.association 
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_gps_by_dbname

total number of GPs in the database instance grouped by contributing database (eg FlyBase, UniProt, ..)

SELECT dbxref.xref_dbname
, count
(*) AS total_gps 
FROM (go_associations.gene_product 
  JOIN go_general.dbxref 
    ON (
           (dbxref.id = gene_product.dbxref_id)
     )
)
GROUP BY dbxref.xref_dbname 
ORDER BY count
(*);

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_annotations_per_gp

as total_annotations_per_gp, excluding direct annotations to root

SELECT association.gene_product_id
, count
(DISTINCT association.id) AS total_annotations 
FROM go_associations.association 
WHERE (NOT 
     (EXISTS 
           (
            SELECT root_term.id
                 , root_term.name
                 , root_term.term_type
                 , root_term.acc
                 , root_term.is_obsolete
                 , root_term.is_root 
              FROM go_graph_views.root_term 
             WHERE (root_term.id = association.term_id)
           )
     )
)
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_pubs_per_gp

SELECT association.gene_product_id
, count
(DISTINCT evidence.dbxref_id) AS total_pubs 
FROM (go_associations.association 
  JOIN go_associations.evidence 
    ON (
           (association.id = evidence.association_id)
     )
)
WHERE (NOT 
     (EXISTS 
           (
            SELECT root_term.id
                 , root_term.name
                 , root_term.term_type
                 , root_term.acc
                 , root_term.is_obsolete
                 , root_term.is_root 
              FROM go_graph_views.root_term 
             WHERE (root_term.id = association.term_id)
           )
     )
)
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_terms_per_gp

SELECT association.gene_product_id
, count
(DISTINCT association.term_id) AS total_terms 
FROM go_associations.association 
WHERE (NOT 
     (EXISTS 
           (
            SELECT root_term.id
                 , root_term.name
                 , root_term.term_type
                 , root_term.acc
                 , root_term.is_obsolete
                 , root_term.is_root 
              FROM go_graph_views.root_term 
             WHERE (root_term.id = association.term_id)
           )
     )
)
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_transitive_terms_per_gp

SELECT association.gene_product_id
, count
(DISTINCT graph_path.term1_id) AS total_transitive_terms 
FROM (go_associations.association 
  JOIN go_optimisations.graph_path 
    ON (
           (graph_path.term2_id = association.term_id)
     )
)
WHERE (NOT 
     (EXISTS 
           (
            SELECT root_term.id
                 , root_term.name
                 , root_term.term_type
                 , root_term.acc
                 , root_term.is_obsolete
                 , root_term.is_root 
              FROM go_graph_views.root_term 
             WHERE (root_term.id = association.term_id)
           )
     )
)
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_nonroot_transitive_terms_per_gp_pair

SELECT a1.gene_product_id AS gp1_id
, a2.gene_product_id AS gp2_id
, count
(DISTINCT tc1.term1_id) AS total_transitive_terms 
FROM go_associations.association a1
, go_optimisations.graph_path tc1
, go_optimisations.graph_path tc2
, go_associations.association a2 
WHERE (
     (
           (
                 (tc1.term2_id = a1.term_id)
               AND (tc2.term2_id = a2.term_id)
           )
         AND (tc1.term1_id = tc2.term1_id)
     )
   AND (NOT 
           (EXISTS 
                 (
                  SELECT root_term.id
                       , root_term.name
                       , root_term.term_type
                       , root_term.acc
                       , root_term.is_obsolete
                       , root_term.is_root 
                    FROM go_graph_views.root_term 
                   WHERE (
                             (root_term.id = a1.term_id)
                            OR (root_term.id = a1.term_id)
                       )
                 )
           )
     )
)
GROUP BY a1.gene_product_id
, a2.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_pubs_per_gp

SELECT association.gene_product_id
, count
(DISTINCT evidence.dbxref_id) AS total_pubs 
FROM (go_associations.association 
  JOIN go_associations.evidence 
    ON (
           (association.id = evidence.association_id)
     )
)
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_terms_per_gp

SELECT association.gene_product_id
, count
(DISTINCT association.term_id) AS total_terms 
FROM go_associations.association 
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

View: go_annotation_reports.total_transitive_terms_per_gp

SELECT association.gene_product_id
, count
(DISTINCT graph_path.term1_id) AS total_transitive_terms 
FROM (go_associations.association 
  JOIN go_optimisations.graph_path 
    ON (
           (graph_path.term2_id = association.term_id)
     )
)
GROUP BY association.gene_product_id;

Index - Schema go_annotation_reports

Schema go_associations

Table: go_associations.association

Annotation model: An association is a link between a gene product record and an ontology term, with one or more pieces of evidence *** IMPORTANT: NOT all associations are positive: some posit negative links. THESE SHOULD TYPICALLY BE FILTERED OUT FOR MOST ANALYSIS PURPOSES. See the is_not column ***

Tables referencing this one via Foreign Key Constraints:

• go_associations.association_property

• go_associations.association_qualifier

• go_associations.association_species_qualifier

• go_associations.evidence

Index - Schema go_associations

Table: go_associations.association_property

(column 16 in the gene-association file)

Index - Schema go_associations

Table: go_associations.association_qualifier

associations can have a number of qualifiers. These include, but are not limited to the NOT qualifier (which technically is not a qualifier at all as it fundamentally changes the semantics of an association) note that this table IS redundant with association.is_not (column 4 in the gene-association file)

Index - Schema go_associations

Table: go_associations.association_species_qualifier

(see http://www.geneontology.org/GO.annotation.shtml#manySpp) (column 13 in gene_association file, ONLY WHEN card>1, this is the next entry) to be used only in conjunction with terms that have the term 'interaction between organisms' as an ancestor. gene_product.species_id is for the organism type encoding the gene or gene product, association_species_qualifier.species_id should be that of the other organism in the interaction.

Index - Schema go_associations

Table: go_associations.evidence

each association can have one or more pieces of evidence attached to it (the schema actually allows zero or more, but with GO all annotation have at least one piece of evidence) (doc: http://www.geneontology.org/GO.evidence.shtml)

Tables referencing this one via Foreign Key Constraints:

• go_associations.evidence_dbxref

Index - Schema go_associations

Table: go_associations.evidence_dbxref

each piece of evidence can have multiple dbxrefs associated with it; this is the *normalised* version of the "With" or "From" field of the evidence (column 8 in the gene-association file, normalized)

Index - Schema go_associations

Table: go_associations.gene_product

Represents a gene or gene_product, typically at the species level. GO allows for annotation of genes OR gene products. Annotation of a gene is understood to be "proxy" for annotation of the corresponding gene products. (docs: http://www.geneontology.org/GO.annotation.fields.shtml) (docs: http://www.geneontology.org/GO.annotation.shtml#file) (Example: human p53-gene) (Example: human p53-protein)

Tables referencing this one via Foreign Key Constraints:

• go_associations.association

• go_associations.gene_product_subset

• go_associations.gene_product_synonym

• go_homology.gene_product_homolset

• go_homology.homolset

• go_sequence.gene_product_seq

Index - Schema go_associations

Table: go_associations.gene_product_subset

(aka goslims). Each subsetdef (slim) is stored as a gene_product in the database (with term_type = 'subset') The subset_id links to this term (OBO-Format: *subset* tag. term_id references a term housing the *subsetdef*)

Index - Schema go_associations

Table: go_associations.gene_product_synonym

alternate label for the gene or gene product (column 11 in the gene-association file)

Index - Schema go_associations

Table: go_associations.species

Linnaean taxonomic information for an organism type. Modeled after NCBI Taxonomy (Note: the name of the table is misleading, as it can model ANY node in Linnaen taxonomy) (The table should be better called "taxon")