Additional File 3 Examples of BioWarehouse queries, Results, and Elapsed Time. All queries were run against an Oracle BioWarehouse instance running on a 2x2.66 GHz Pentium 4 processor machine with 2 GB of memory. Queries were executed using a Common Lisp interpreter with an ODBC driver. Results are shown in list form. ----------------------------------------------------------------------------- Query warehouse for the datasets it contains: 0[1]: (ISSUE-SQL "select name,wid,loaddate,version from DataSet") 0[1]: returned (("CMR" "4" "2004-09-29 21:36:01" "2004-05-28/primary") ("NCBI Taxonomy" "2" "2004-09-29 20:00:18" "2004-08-23") ("KEGG" "62" "2004-11-02 13:03:31" "32.0") ("Enzyme" "3" "2004-09-29 00:00:00" "35.0") ("Swiss-Prot" "6" "2004-09-30 08:49:57" NIL) ("AgroCyc" "7" "2004-09-30 15:15:52" "8.5") ("BsubCyc" "8" "2004-09-30 15:20:26" "8.5") ("CauloCyc" "9" "2004-09-30 15:23:18" "8.5") ("CtraCyc" "10" "2004-09-30 15:25:53" "8.5") ("EcoCyc" "11" "2004-09-30 15:26:16" "8.5") ("Ecoo157Cyc" "12" "2004-09-30 15:29:00" "8.5") ("HinCyc" "13" "2004-09-30 15:33:27" "8.5") ("HpyCyc" "14" "2004-09-30 15:34:14" "8.5") ("HumanCyc" "15" "2004-09-30 15:34:57" "8.5") ("MetaCyc" "16" "2004-09-30 15:49:02" "8.5") ("MpneuCyc" "17" "2004-09-30 15:52:50" "8.5") ("MtbcdcCyc" "18" "2004-09-30 15:53:06" "8.5") ("MtbrvCyc" "19" "2004-09-30 15:56:03" "8.5") ("ShigellaCyc" "20" "2004-09-30 15:58:47" "8.5") ("TpalCyc" "21" "2004-09-30 16:01:49" "8.5") ("VchoCyc" "22" "2004-09-30 16:02:13" "8.5")) ("NAME" "WID" "LOADDATE" "VERSION")) ; real time 143 msec Table sizes: DataSet 32 rows ----------------------------------------------------------------------------- Query Swiss-Prot for a named protein as well as the organisms it occurs in: 0[1]: (ISSUE-SQL "select distinct Protein.Name, BioSource.Name from Protein, BioSource, BioSourceWIDProteinWID where Protein.name = 'Zyxin' and ProteinWID = Protein.WID and BioSourceWID = BioSource.WID and Protein.DataSetWID = (select WID from DataSet where Name = 'Swiss-Prot')") 0[1]: returned (("Zyxin" "Chicken") ("Zyxin" "Gallus gallus") ("Zyxin" "Homo sapiens") ("Zyxin" "Human") ("Zyxin" "Mouse") ("Zyxin" "Mus musculus")) ("NAME" "NAME")) ; real time 305 msec Table sizes: DataSet 32 rows BioSource 1351345 rows Protein 1694037 rows BioSourceWIDProteinWID 7251622 rows ----------------------------------------------------------------------------- Count the number of reactions mentioned in pathways for H.pylori [BioCyc]: 0[1]: (ISSUE-SQL "select count (Reaction.wid) from Reaction, Pathway, PathwayReaction, BioSource where PathwayReaction.reactionwid = Reaction.wid and PathwayReaction.pathwaywid = Pathway.wid and Pathway.biosourcewid = BioSource.wid and BioSource.name = 'Helicobacter pylori' and BioSource.DataSetWID=70 " :TYPES (:INT)) 0[1]: returned ((606)) ("COUNT(REACTION.WID)") ; real time 152 msec Table sizes: DataSet 32 rows BioSource 1351345 rows Reaction 27770 rows Pathway 21625 rows PathwayReaction 136694 rows ----------------------------------------------------------------------------- Count the number of reactions mentioned in pathways for H.pylori [KEGG]: 0[1]: (ISSUE-SQL "select count (Reaction.wid) from Reaction, Pathway, PathwayReaction, BioSource where PathwayReaction.reactionwid = Reaction.wid and PathwayReaction.pathwaywid = Pathway.wid and Pathway.biosourcewid = BioSource.wid and BioSource.name = 'H.pylori' and BioSource.DataSetWID=81 " :TYPES (:INT)) 0[1]: returned ((438)) ("COUNT(REACTION.WID)") ; real time 1,773 msec Table sizes: DataSet 32 rows BioSource 1351345 rows Reaction 27770 rows Pathway 21625 rows PathwayReaction 136694 rows ----------------------------------------------------------------------------- Identify the EC numbers for B.subtilis shared by all pathways of KEGG and BsubCyc: 0[1]: (ISSUE-SQL "(select distinct Reaction.ecnumber from Reaction, Pathway, PathwayReaction, BioSource where Reaction.wid = pathwayReaction.reactionwid and Pathway.wid = PathwayReaction.pathwaywid and Pathway.datasetwid = 8 and Pathway.biosourcewid = BioSource.wid and BioSource.name = 'Bacillus subtilis') intersect (select distinct Reaction.ecnumber from Reaction, Pathway, PathwayReaction, BioSource where Reaction.wid = PathwayReaction.reactionwid and Pathway.wid = PathwayReaction.pathwaywid and Pathway.datasetwid = 62 and Pathway.BioSourcewid = BioSource.wid and BioSource.name = 'B.subtilis')") 0[1]: returned (("1.1.1.100") ("1.1.1.103") ("1.1.1.158") ("1.1.1.169") ("1.1.1.17") ("1.1.1.205") ("1.1.1.22") ("1.1.1.23") ("1.1.1.25") ("1.1.1.3") ("1.1.1.35") ("1.1.1.37") ("1.1.1.38") ("1.1.1.40") ("1.1.1.42") ("1.1.1.44") ("1.1.1.49") ("1.1.1.58") ("1.1.1.85") ("1.1.1.86") ("1.1.1.94") ("1.1.1.95") ("1.1.99.5") ("1.11.1.6") ("1.2.1.11") ("1.2.1.12") ("1.2.1.16") ("1.2.1.2") ("1.2.1.38") ("1.2.1.41") ("1.2.1.8") ("1.3.1.12") ("1.3.1.26") ("1.3.1.28") ("1.3.1.9") ("1.3.3.1") ("1.3.3.4") ("1.3.99.1") ("1.5.1.12") ("1.5.1.2") ("1.5.1.3") ("1.5.1.5") ("1.8.1.2") ("2.1.1.10") ("2.1.1.14") ("2.1.1.45") ("2.1.2.1") ("2.1.2.11") ("2.1.2.2") ("2.1.2.3") ("2.1.3.2") ("2.1.3.3") ("2.2.1.1") ("2.2.1.2") ("2.3.1.1") ("2.3.1.117") ("2.3.1.16") ("2.3.1.29") ("2.3.1.30") ("2.3.1.39") ("2.3.1.41") ("2.3.1.46") ("2.3.1.47") ("2.3.1.8") ("2.3.1.9") ("2.4.1.21") ("2.4.2.10") ("2.4.2.11") ("2.4.2.17") ("2.4.2.18") ("2.4.2.19") ("2.4.2.7") ("2.4.2.9") ("2.5.1.10") ("2.5.1.15") ("2.5.1.16") ("2.5.1.19") ("2.5.1.3") ("2.5.1.6") ("2.5.1.7") ("2.5.1.9") ("2.6.1.1") ("2.6.1.11") ("2.6.1.16") ("2.6.1.17") ("2.6.1.19") ("2.6.1.42") ("2.6.1.52") ("2.6.1.62") ("2.6.1.9") ("2.7.1.11") ("2.7.1.15") ("2.7.1.16") ("2.7.1.17") ("2.7.1.2") ("2.7.1.21") ("2.7.1.23") ("2.7.1.24") ("2.7.1.25") ("2.7.1.26") ("2.7.1.30") ("2.7.1.40") ("2.7.1.45") ("2.7.1.48") ("2.7.1.5") ("2.7.1.50") ("2.7.1.71") ("2.7.2.1") ("2.7.2.11") ("2.7.2.3") ("2.7.2.4") ("2.7.2.8") ("2.7.4.14") ("2.7.4.16") ("2.7.4.3") ("2.7.4.7") ("2.7.4.8") ("2.7.4.9") ("2.7.6.1") ("2.7.6.3") ("2.7.7.18") ("2.7.7.2") ("2.7.7.23") ("2.7.7.27") ("2.7.7.3") ("2.7.7.4") ("2.7.7.41") ("2.7.7.9") ("2.7.8.5") ("2.7.8.8") ("2.7.9.2") ("2.8.1.6") ("2.8.3.5") ("3.1.3.11") ("3.1.3.15") ("3.1.3.3") ("3.2.1.93") ("3.3.2.1") ("3.5.1.10") ("3.5.1.16") ("3.5.1.18") ("3.5.1.25") ("3.5.2.3") ("3.5.2.7") ("3.5.3.11") ("3.5.3.8") ("3.5.4.10") ("3.5.4.16") ("3.5.4.19") ("3.5.4.25") ("3.5.4.5") ("3.5.4.9") ("3.6.1.23") ("3.6.1.31") ("4.1.1.11") ("4.1.1.19") ("4.1.1.20") ("4.1.1.23") ("4.1.1.36") ("4.1.1.37") ("4.1.1.48") ("4.1.1.49") ("4.1.1.50") ("4.1.1.65") ("4.1.1.71") ("4.1.2.13") ("4.1.2.14") ("4.1.2.25") ("4.1.2.4") ("4.1.3.27") ("4.1.3.36") ("4.2.1.10") ("4.2.1.11") ("4.2.1.17") ("4.2.1.19") ("4.2.1.2") ("4.2.1.24") ("4.2.1.3") ("4.2.1.33") ("4.2.1.40") ("4.2.1.46") ("4.2.1.49") ("4.2.1.51") ("4.2.1.52") ("4.2.1.7") ("4.2.1.75") ("4.2.1.8") ("4.2.1.9") ("4.3.2.1") ("4.3.2.2") ("4.4.1.1") ("4.4.1.8") ("4.99.1.1") ("5.1.1.1") ("5.1.1.3") ("5.1.1.7") ("5.1.3.2") ("5.1.3.4") ("5.3.1.1") ("5.3.1.12") ("5.3.1.14") ("5.3.1.16") ("5.3.1.24") ("5.3.1.4") ("5.3.1.5") ("5.3.1.6") ("5.3.1.8") ("5.3.1.9") ("5.4.2.1") ("5.4.2.2") ("5.4.2.7") ("5.4.2.8") ("5.4.3.8") ("5.4.99.5") ("6.1.1.17") ("6.2.1.26") ("6.2.1.3") ("6.2.1.5") ("6.3.1.2") ("6.3.2.1") ("6.3.2.13") ("6.3.2.17") ("6.3.2.4") ("6.3.2.5") ("6.3.2.6") ("6.3.2.8") ("6.3.2.9") ("6.3.3.1") ("6.3.3.3") ("6.3.4.13") ("6.3.4.14") ("6.3.4.2") ("6.3.4.4") ("6.3.4.5") ("6.3.5.1") ("6.3.5.2") ("6.3.5.3") ("6.3.5.4") ("6.3.5.5") ("6.4.1.2")) ("ECNUMBER") ; real time 741 msec Table sizes: DataSet 32 rows BioSource 1351345 rows Reaction 27770 rows Pathway 21625 rows PathwayReaction 136694 rows ----------------------------------------------------------------------------- Query the entire warehouse for a wildcarded protein name: 0[1]: (ISSUE-SQL "select Protein.Name, AASequence from Protein, SynonymTable where Protein.WID=SynonymTable.OtherWID and (syn like '%Isocitrate dehydrogenase%' or Protein.Name like '%Isocitrate dehydrogenase%')") 0[1]: returned (("Isocitrate dehydrogenase [NADP] (Oxalosuccinatedecarboxylase) (IDH) (NADP+-specific ICDH) (IDP)" "MLPKGTIRNILIGNIFREPLVIANVSLLVLSWAKKIVMVNQNFW") ("Isocitrate dehydrogenase (NAD+)" NIL) ("Isocitrate dehydrogenase (NAD+)" NIL) ("Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial" "AIEVQTVTLIPGD") ("Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial" "AIEVQTVTLIPGD") ("Isocitrate dehydrogenase [NAD] subunit alpha, mitochondrial" "AIEVQTVTLIPGD") ("Isocitrate dehydrogenase" "MPKFEKLNLPKEGELITFNQGKPNVPNNPIVPYIRGDGTGVDIWPATQLVLDEAIKKSYGDERKINW FKVYAGDEACEIYGTYNYLPQDTIEAIRHFGVAIKGPLTTPIGGGIRSLNVALRQIFDLYSCVRPCKYYSGTPSPHKN PQNLDVIVYRENTEDIYMGIEWEADDHTCIDLINHLNNVVIPNSKNLKNRSIPDGSGIGIKPVSKLGSQRHIRKAIEH AKRLSGNKKHVTLVHKGNIMKYTEGAFRDWGYELAVNEFRADCITERESWILDNIHKNPEITIENNARKIEPGYDKLT SNKKAFICEEIKEVIASISNSHGNNKWEELIMVDDRIADSIFQQIQTRPQEYSILATLNLNGDYVSDAAAAIVGGLGM APGANIGDNSAIFEATHGTAPKHAGLNKINPGSVILSGVMMLEYFGWNEAAKLVTSGISKAIEEKKVTYDLARLMEPK VAPLSCSGFAEAIISNF") ) ; real time 131,126 msec (00:02:11.126) Table sizes: DataSet 32 rows Protein 1694037 rows SynonymTable 2561710 rows