Database Schema
Overview
Click here to see the schema and its description. Click here to see the diagram of the database schema.
"_brenda*" tables contain enzyme information from BRENDA. "Chebi_*" contain information from ChEBI. "Pubchem_*" contain information from PubChem. "Tair_*" contain information from TAIR ftp. "Blockunit*" store information of blockunit (including entitywithlocation, interaction and pathway). "Entity*" store information of entity. "Code*" store information of constants. "Interaction" and "interactionproperties" store additional information of interactions. "Interactionparts" and "pathwayparts" store relation among entitywithlocations, interactions and pathways.
To get the interaction list from pathways or vice versa, query "pathwayparts" table. To get the substrates and products from interactions or vice versa, query "interactionparts" table. To get the relations between protein complexes and their components, use interaction type "Composition-AND" or "Composition-OR" to query "interactionparts" table. Use interaction type "transcription" and "translation" can get the relation among genes, RNAs and polypeptides. Based on those primitive queries, more complicated queries like "get all genes of a pathway" or "get all pathways the gene is involved" can be constructed.
For instance, given the pathway name ‘Acetyl-CoA pathway’, the following SQL statement can get all genes in it
select e.name
from pathwayparts as pp, interactionparts as ip, entitywithcontext as ec,
entity as e, blockunit as b
where pp.blockid = b.blockid
and pp.part = ip.blockid
and ip.part = ec.blockid
and ec.entityid = e.entityid
and e.type = 'gene'
and b.name = 'Acetyl-CoA pathway'
By changing the SQL statement a little, we can query all pathways in which a gene like 'AT1G12210' is involved.
select b.name
from pathwayparts as pp, interactionparts as ip, entitywithcontext as ec,
entity as e, blockunit as b
where pp.blockid = b.blockid
and pp.part = ip.blockid
and ip.part = ec.blockid
and ec.entityid = e.entityid
and e.type = 'gene'
and e.name = 'AT1G12210'