Dumped on 2013-08-15

Index of database - wormdb

public

complexes
conserved_ppi
conserved_ppi_full
fly
homo_sapiens
mouse
omim
orthologs
sea_urchin
worm
fetch_gene_names(character varying)

Schema:

Table: complexes

Information about predicted complexes

complexes Structure
Name	Type	Description
pk_complex_id	integer	UNIQUE NOT NULL primary key
conserved_ids	text	complex members for conserved map in terms of human gene names
hs_names	text	complex members for human that come from evidence file
mm_names	text	complex members for mouse in terms of gene names
dm_names	text	complex members for fly in terms of gene names
ce_names	text	complex members for worm in terms of gene names
su_names	text	complex members for sea urchin in terms of gene names
num_of_members	integer	number of subunits in complex

Tables referencing this one via Foreign Key Constraints:

conserved_ppi

Permissions which apply to complexes
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
PUBLIC	♦	♦	♦	♦	♦	♦	♦
ekirpach	♦	♦	♦	♦	♦	♦	♦

Index - Schema public

Table: conserved_ppi

Information about protein-protein interactions contributing to conserved map

conserved_ppi Structure
F-Key	Name	Type	Description
homo_sapiens.pk_hs_gene_id	fk_hs_gene1_id	integer	foreign key to homo_sapiens.pk_hs_gene_id
homo_sapiens.pk_hs_gene_id	fk_hs_gene2_id	integer	foreign key to homo_sapiens.pk_hs_gene_id
complexes.pk_complex_id	fk_complex_id	integer	foreign key to complexes.pk_complex_id
	score	double precision	ppi score
	in_corum	boolean	Found in Corum db (t/f)
	in_bio_grid	boolean	Found in BioGrid db (t/f)
	in_iref_web	boolean	Found in IRefWeb db (t/f)
	in_bio_grid_or_iref_web	boolean	Found in BioGrid or IRefWeb db (t/f)
	in_crm_ppi	boolean	Found in Corum db (t/f)
	only_in_crm	boolean	Found only in Corum db (t/f)
	is_novel	boolean	Is interaction novel (t/f)
	evidence_type_ce	character varying(10)	type of evidence for worm (frac/net/none)
	evidence_type_dm	character varying(10)	type of evidence for fly (ext/frac/net/none)
	evidence_type_hs	character varying(10)	type of evidence for human (ext/frac/net/none)
	evidence_type_mm	character varying(10)	type of evidence for mouse (frac/none)
	evidence_type_sp	character varying(10)	type of evidence for sea urchin (frac/none)
	interaction	character varying(25)	interaction description (known/novel)

Permissions which apply to conserved_ppi
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
PUBLIC	♦	♦	♦	♦	♦	♦	♦
ekirpach	♦	♦	♦	♦	♦	♦	♦

Index - Schema public

Table: conserved_ppi_full

Information about protein-protein interactions contributing to conserved map

conserved_ppi_full Structure
F-Key	Name	Type	Description
homo_sapiens.pk_hs_gene_id	fk_hs_gene1_id	integer	foreign key to homo_sapiens.pk_hs_gene_id
homo_sapiens.pk_hs_gene_id	fk_hs_gene2_id	integer	foreign key to homo_sapiens.pk_hs_gene_id
	hs_g1	character varying(30)	gene 1 name (human)
	hs_g2	character varying(30)	gene 2 name (human)
	mm_g1	text	mouse ortholog(s) name for gene 1
	mm_g2	text	mouse ortholog(s) name for gene 2
	dm_g1	text	fly ortholog(s) name for gene 1
	dm_g2	text	fly ortholog(s) name for gene 2
	sp_g1	text	sea urchin ortholog(s) name for gene 1
	sp_g2	text	sea urchin ortholog(s) name for gene 2
	ce_g1	text	worm ortholog(s) name for gene 1
	ce_g2	text	worm ortholog(s) name for gene 2
	score	double precision	ppi score
	complex_id	character varying(30)	IDs of all complexes to which this interaction contributes separated by ;
	evidence_hs	character varying(10)	type of evidence for human (ext/frac/net/none)
	evidence_mm	character varying(10)	type of evidence for mouse (frac/none)
	evidence_dm	character varying(10)	type of evidence for fly (ext/frac/net/none)
	evidence_ce	character varying(10)	type of evidence for worm (frac/net/none)
	evidence_sp	character varying(10)	type of evidence for sea urchin (frac/none)
	in_corum	boolean	Found in Corum db (t/f)
	in_bio_grid	boolean	Found in BioGrid db (t/f)
	in_iref_web	boolean	Found in IRefWeb db (t/f)
	is_novel	boolean	Is interaction novel (t/f)

Permissions which apply to conserved_ppi_full
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
PUBLIC	♦	♦	♦	♦	♦	♦	♦
ekirpach	♦	♦	♦	♦	♦	♦	♦

Index - Schema public

Table: fly

Information about genes of Drosophila melanogaster

fly Structure
Name	Type	Description
pk_f_gene_id	integer	UNIQUE NOT NULL primary key
gene	character varying(50)	gene name(symbol)
alias	text	gene alias if there is any
uniprot_accession	character varying(20)	Uniprot accession
swissprot_name	character varying(20)	Swissprot name
ncbi_gene_id	text	NCBI gene ID
secondary_accession	text	Secondary uniprot accession(s)
fly_base_id	text	FlyBase ID(s)
pdb_id	text	PDB ID(s)
description	text	gene description
function	text	gene function
info_source	character varying(20)	source of information
status	character varying(60)	status of FlyBase ID

Permissions which apply to fly
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
ekirpach	♦	♦	♦	♦	♦	♦	♦
maldi	♦

Index - Schema public

Table: homo_sapiens

Information about human genes

homo_sapiens Structure
Name	Type	Description
pk_hs_gene_id	integer	UNIQUE NOT NULL primary key
gene	character varying(20)	gene name(symbol)
alias	text	alias if there is any
uniprot_accession	character varying(20)	Uniprot SPROT or TrEMBL accession
swissprot_name	character varying(20)	Swissprot name
ncbi_gene_id	text	NCBI Gene ID
secondary_accession	text	Secondary accessions from Uniprot
pdb_id	text	PDB IDs
ensmbl_id	text	Ensemble ID(s)
description	text	Gene description
disease	text	Disease associated to this gene (from OMIM)
mim_id	numeric	Associated OMIM phenotype ID
function	text	Gene function
info_source	character varying(20)	Information source
status	character varying(60)	status of Ensemble ID

Tables referencing this one via Foreign Key Constraints:

conserved_ppi

conserved_ppi_full

omim

orthologs

Permissions which apply to homo_sapiens
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
ekirpach	♦	♦	♦	♦	♦	♦	♦
maldi	♦

Index - Schema public

Table: mouse

Information about genes of Mus musculus

mouse Structure
Name	Type	Description
pk_m_gene_id	integer	UNIQUE NOT NULL primary key
gene	character varying(20)	gene name(symbol)
alias	text	gene alias if there is any
uniprot_accession	character varying(20)	Uniprot accession
swissprot_name	character varying(20)	Swissprot name
ncbi_gene_id	text	NCBI gene ID
secondary_accession	text	Secondary uniprot accession(s)
ensmbl_id	text	Ensemble ID(s)
pdb_id	text	PDB ID(s)
description	text	gene description
function	text	gene function
info_source	character varying(20)	source of information
status	character varying(60)	status of Ensemble ID

Permissions which apply to mouse
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
ekirpach	♦	♦	♦	♦	♦	♦	♦
maldi	♦

Index - Schema public

Table: omim

Information about human genes and related diseases from omim database

omim Structure
F-Key	Name	Type	Description
homo_sapiens.pk_hs_gene_id	fk_hs_gene_id	integer	foreign key to homo_sapiens.pk_hs_gene_id
	ncbi_gene_id	character varying(100)	NCBI Gene ID
	ensemble_id	text	Ensemble ID
	disease	text	Disease associated to the gene
	mim_gene	character varying(100)	OMIM gene ID
	mim_phenotype	character varying(150)	OMIM phenotype ID

Permissions which apply to omim
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
PUBLIC	♦	♦	♦	♦	♦	♦	♦
ekirpach	♦	♦	♦	♦	♦	♦	♦

Index - Schema public

Table: orthologs

Otholog genes for 5 species

orthologs Structure
F-Key	Name	Type	Description
homo_sapiens.pk_hs_gene_id	fk_hs_gene_id	integer	foreign key to homo_sapiens.pk_hs_gene_id
	ce_ortholog	text	List of worm orthologs in terms of WormBase accessions separated by :
	dm_ortholog	text	List of fly orthologs in terms of FlyBase IDs separated by :
	mm_ortholog	text	List of mouse orthologs in terms of Ensemble IDs separated by :
	sp_ortholog	text	List of sea urchin orthologs in terms of SpBase IDs separated by :

Permissions which apply to orthologs
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
PUBLIC	♦	♦	♦	♦	♦	♦	♦
ekirpach	♦	♦	♦	♦	♦	♦	♦

Index - Schema public

Table: sea_urchin

Information about genes of Strongylocentrotus purpuratus

sea_urchin Structure
Name	Type	Description
pk_s_gene_id	integer	UNIQUE NOT NULL primary key
gene	character varying(50)	gene name(symbol)
alias	text	gene alias if there is any
uniprot_accession	character varying(20)	Uniprot accession
swissprot_name	character varying(20)	Swissprot name
ncbi_gene_id	text	NCBI gene ID
secondary_accession	text	Secondary uniprot accession(s)
sp_base_gene_id	text	SpBase ID(s)
pdb_id	text	PDB ID(s)
description	text	gene description
function	text	gene function
info_source	character varying(20)	source of information
status	character varying(60)	status of sea_urchinBase ID

Permissions which apply to sea_urchin
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
ekirpach	♦	♦	♦	♦	♦	♦	♦
maldi	♦

Index - Schema public

Table: worm

Information about genes of Caenorhabditis elegans

worm Structure
Name	Type	Description
pk_w_gene_id	integer	UNIQUE NOT NULL primary key
gene	character varying(20)	gene name(symbol)
alias	text	gene alias if there is any
uniprot_accession	character varying(20)	Uniprot accession
swissprot_name	character varying(20)	Swissprot name
ncbi_gene_id	text	NCBI gene ID
secondary_accessions	text	Secondary uniprot accession(s)
pdb_id	text	PDB ID(s)
wb_gene_id	character varying(25)	Worm Base gene ID
sequence_id	character varying(20)	Worm Base accession
protein_name	character varying(20)	Worm Base protein name
description	text	gene description
function	text	gene function
info_source	character varying(20)	source of information
status	character varying(60)	status of WormBase accession

Permissions which apply to worm
User	Select	Insert	Update	Delete	Reference	Rule	Trigger
ekirpach	♦	♦	♦	♦	♦	♦	♦
maldi	♦

Index - Schema public

Function: fetch_gene_names(character varying)

Returns: SET OF all_names

Language: PLPGSQL

declare
	rec all_names;
	cur_id alias for $1;
	query varchar;
begin
	--query the homo_sapiens table for the gene name by matching the ensemble_id
	query := 'select gene from homo_sapiens where ensmbl_id  = \''||cur_id||'\'';
	for rec in execute query loop
		return next rec;
	end loop;
end;

Generated by PostgreSQL Autodoc

W3C HTML 4.01 Strict