PubChem Substance example

The U.S. National Institutes of Health PubChem project contains information on millions of chemical compounds.1 The data are divided into three main sections. PubChem Substance contains structures supplied by depositors. PubChem Compound contains unique structures with computed properties. PubChem BioAssay contains bioactivity assay results supplied by depositors. The data in these three sections are recorded independently, yet there are chemical relationships among these sections. For example, information available as a PubChem BioAssay is associated with a particular substance for which the data were collected. A substance may be a single compound or a mixture of several compounds. 

From the examples in the previous section, it is clear how the substance id relates pubchem.substance to biological assay data and how substance data can be selected using the substance id. How can the compound table be used to select compound data for substances appearing in one of the biological assay data tables In other words, how is the... 

The column pubchem.substance.cid associations is taken directly from the sdf files supplied by PubChem. It has all the necessary information, but it is not in a proper form for a relation between pubchem.substance and pubchem.compounds. This is because too much information has been crammed into this column. For example, the cid associations for substance id 22 contains the data "449653 1449655 2 6540406 2". This means that there are three compound ids associated with this substance id. In other words, there is a many-to-many relationship between compounds and substances. While it would be possible to parse the cid associations column when the compound id is needed, it is better to have a clear relationship between substance ids and compounds ids. It is better because it enforces and preserves the relational integrity (or referential integrity) between these data. It also makes selecting data from all three data sources quicker and easier. 

An index is a piece of informafion fied fo individual records and matched directly to a user s query in an Entrez search. Each index consists of text, numeric, or date values. Each Entrez database has its own set of indices. These indices are named according fo fhe fype of informafion fhey confain, for example, the indices "lU-PACName" or "MolecularWeight" in PubChem Substance and Compound. Some indices may have multiple values for each record. For example, the index "Synonym" corresponds to chemical or common names of a substance, any number of which may be supplied by fhe deposifor. 

When the desired data is in two different tables, a join is required. It is often helpful to begin to develop complex SQL statements by considering one table at a time. For example, data from the nci h23 table of pubchem schema was considered earlier in this chapter. The experimental data to be selected from that table was the substance id (called sid in the table), activity outcome, log gi50 M and n.log gi50 ugml. This is accomplished by the simple SQL... 

Filters are related to links in that the majority of filters in the PubChem databases are generated automatically based on the presence of links. In the above example the "pcsubstance pcassay" filter has a "true" bit for every substance for which a PubChem BioAssay link is present (e.g., in the pop-up menus of the Entrez DocSum for that substance). 

BioActivity summary provides a set of functions that allows one to revise the substance/compound and assay sets. For example, one may focus only on a subset of compounds that are active in one or more of the selected assays using the Compound I Select Active link, or explore additional screen sets where the given compounds were considered active using the Assay I Add Active link. PubChem provides multiple access points for this service. For compounds or substances tested found in Entrez, one can launch this service for each individual record using the direct "BioActivity Analysis" link, or, for all of the records from an Entrez search, through the launching point at the "Tool" area. 

---