Proportal: Difference between revisions
Huiming Ding (talk | contribs) |
Huiming Ding (talk | contribs) |
||
Line 130: | Line 130: | ||
===Table: data_affyprobe=== | ===Table: data_affyprobe=== | ||
A list of probes for various affychip experiments. | |||
* 89749 records, as of 07-22-2011 | * 89749 records, as of 07-22-2011 | ||
* Foreign key: probeset_id | * Foreign key: probeset_id |
Revision as of 07:35, 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
A list of probe sets for various affychip experiments.
* 9966 records, as of 07-22-2011 * Three foreign keys: o chip_id: o scaffold_id: has missing keys o feature_id: not defined
Notes
* Use "begin" and "end" to match DNA\gene\protein?
Table: data_affyprobe
A list of probes for various affychip experiments.
* 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
Field information of Gos experiments.
* 5597 records, as of 07-22-2011 * Two distinct "type": phCOG and CyCog * "gene_name" not in use * No foreign key
Table: data_protein_cluster_synonym
Empty table. Table: data_protein_cluster_xref
* 1100 records, as of 07-22-2011 * Only one "type": c * "xref": COG reference id, which may correspond to multiple cluster ids * Foreign key: data_protein_cluster.id, o Error: 880 cluster_ids missing in data_protein_cluster table.
Table: data_protein_cluster_cog
Empty table. Table: data_clusterlink
* 71 records, as of 07-22-2011 * "evidence" is not in use * Foreign key: data_protein_cluster.id, o Error: cluster_id 0 not exist in data_protein_cluster table.