Revision as of 07:32, 25 July 2011

Proportal DB Schema

User Module

Project Module

Table: data_project

A list of projects

   * 72 projects, as of 07-21-2011
   * Last updated: 2010-12-10
   * No foreign key

Notes

   * "type": cpm, cpp, cps, ma, mt, p, pb, s
   * "tax_id": the link for "tax_id" is defined in data_url table.

For instance,

   * type_id = 59919
   * source = tax
   * url = http://www.ncbi.nlm.nih.gov/Taxonomy/Browser/wwwtax.cgi?id=59919

Table: data_projectpub

A list of publications from various projects

   * 32 publications as of 07-21-2011
   * No publication in this table is listed in data_publication table
   * Foreign key: data_project

Table: data_genepub

This table is empty. Consider to use data_publication table instead?

   * Foreign key: data_project

Table: data_publication

A list of publications related to Prochlorococcus, Cyanophage, and Synechococcus.

   * 2526 publications listed as of 07-21-2011
   * Not refered by any other table
   * pubmed_id can be used as a foreign key.
   * "year": last updated 2010

Table: data_url_map

This table is empty.

Table: data_url

The list of data links or data folders.

Meta Data Module

Table data_bats_ts

Information about field investigation.

   * No foreign key

Table: data_meta_data

Information about field investigation for each project

   * 66 meta data sets, as of 07-22-2011
   * Foreign key: data_project.id, to be fixed.

Error

   * One project_id 26 is missing in data_project table.
   * Six projects defined in data_project table (all in year 2008) do not have meta data defined in  this table.

Genome Data Module

Table: data_scaffold

A list of strains/genomes used in various project.

   * Last updated: 12-10-2010
   * 213 strains, as of 07-22-2011
   * Fireigh key: data_project.id

Questions

   * "refseg_id" not defined
   * "seq" field can be removed because its content is further defined in data_dna and data_protein tables.

Table: data_position

List of start and end positions of gene/DNA for each strain defined in Table data_scaffold.

   * 67516 pair of positions, as of 07-22-2011
   * 9 types of sequences are defined: 16s, 23s, 5s, as, m, n, orf, ps, t
   * Foreign key: data_scaffold.id.

Table: data_dna

A list of DNA sequesnces in correspondence to sequence postion information defined in data_position table.

   * 67516 pieces of DNA sequences stored, as of 07-22-2011
   * Three foreign keys: data_position.id, data_scaffold.id and data_protein.id

Error

   * Foreign key  pos_id has error:
         o Two position ids in data_position table: 37163 and 46814 are missing in this table
         o Two pos_id: 36978 and 37113 do not exist in data_position table.

Table: data_protein

A list of protein sequences.

   * 65909 proteins defined, as of 07-22-2011 (1607 DNA sequences are not present in this table)
   * Two foreign keys: data_scaffold.id and data_protein.id

Notes

   * "cluster_id" should be removed from this table

Table: data_ortholog

Protein orthologs.

   * 830944 orthology pairs defined, as of 07-22-2011
   * Foreign keys: protein_id and ortholog_id

Table: data_protein_xref

Definition: ?

   * 36774 records stored, as of 07-22-2011
   * Foreign key: data_protein.id, to be fixed,

Error

   * Two records have missing protein_id: 36950 and 45482 in data_protein table

Affychip Expression Module

Table: data_affychip

Information about each affychip used.

   * 1 chip defined, as of 07-22-2011
   * No foreign key

Table: data_affyexp

A list of affychip experiments.

   * 20 affychip experiments, as of 07-22-2011
   * Foreign key: project_id, only three projects involving affychip experiments.

Table: data_affyprobeset

   * 9966 records, as of 07-22-2011
   * Three foreign keys:
         o chip_id:
         o scaffold_id: has missing keys
         o feature_id: not defined
   * Use "begin" and "end "end" to match DNA\gene\protein?

Table: data_affyprobe

   * 89749 records, as of 07-22-2011
   * Foreign key: probeset_id

Table: data_affydata

Affychip experimental results.

   * 110848 records, as of 07-22-2011
   * Foreign keys,
         o exp_id
         o probeset_id
   * No DNA\gene\protein info, use probeset_id?

Table: data_diel

Enrichment results of Affychip experiemnts.

   * 1695 records, as of 07-22-2011
   * Foreign keys,
         o probeset_id
         o protein_id
         o gene_id: not defined

Table: data_dieltimepoint

Time courses of Affychip experiemnts.

   * 42375 records, as of 07-22-2011
   * Foreign key: diel_id

Cog Module

Table: data_cog_fun

Cog gene functions.

   * 24 funtion categoriess, as of 07-22-2011
   * No foreign key

Table: data_cog

Cog genome

   * 4874 records, as of 07-22-2011
   * Foreign key: data_cog_fun.funcode can't be regarded as a foreign key becuase some of funcode in this table are missing in data_cog_fun table.

Table: data_protein_cog

Cog protein mapping.

   * 18498 records, as of 07-22-2011
   * Foreign keys: data_protein.id and data_cog.id

Gos Module

Table: data_gos_site

Field information of Gos experiments.

   * 78 records, as of 07-22-2011
   * No foreign key

Table: data_gos_read

Field reads of Gos experiments.

   * 9893120 records, as of 07-22-2011
   * Foreign key: data_gos_site.id

Table: data_gos_to_protein

Gos protein mapping.

   * 926072 records, as of 07-22-2011
   * Foreign keys: data_protein.id and data_gos_read.id

Table: data_gos_blastn

Gos sequence blast.

   * 8666847 records, as of 07-22-2011
   * Foreign keys: data_scaffold.id and data_gos_read.id

Cluster Module

Table: data_protein_cluster