PubChem Substance table

Figure 6.3 shows the relationship between the pubhcem.nci h23 and pubchem.substance tables in the form of an entity-relationship diagram (ERD). The primary key substance.substance id and the foreign key nci h23.sid are indicated and imply their use in an On clause when these two tables are joined. 

Notice the use of the Join keyword and the additional table name pubchem.substance in the From clause. This is necessary because data from this table is being selected. The additional columns selected are ext datasource name and substance.ext datasource regid in the Select clause. Any columns of interest in the substance table could be selected. Note that since there is a column named ext datasource id in both tables, it is necessary to specify that the column substance.ext datasource regid is desired. Finally, the clause On nci h23.sid= substance, substance id indicates that these columns are related to each other and must be used in the Join. 

Figure 6.3 Entity-relationship diagram for pubchem.substance and pubhchem. nci h23 tables.

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... 

There are several approaches to creating the relation between compound and substance. One is to create an integer column, say, pubchem. substance.cid that would contain only the primary compound id from the column cid associations. This column becomes a foreign key related to the pubchem. compound. c id column. This would form a proper relation between the tables, but would neglect the secondary cid associations. If those are of no interest, this approach is an excellent choice. 

The proper way to create a relation between pubchem.substance, substance id and pubchem.compound.cid is to create a new table that acts as an intermediary. This is a typical approach to handling many-to-many relationships. This table must include a column for the compound id and a column for the substance id. There can be as many rows... 

The substances in PubChem are available as a set of sdf files. The data in these files can be read by a wide variety of programs.5 The one most directly useful here produces a file of SQL commands to create a table and copy data into it. This sdf2sql program is available online.6 Using the PubChem file Substance 00000001 00025000.sdf.gz, the output of sdf2sql produces the following ... 

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... 

Beyond a summary description, one would like to view, analyze, and display the actual bioassay data. PubChem provides an integrated suite of tools, each presented as an individual tab, for this purpose. One would use the bioactivity summary tool to, at a glance, be able to examine an overview of the bioassays tested for a list of substances or compounds. To be able to subset and analyze substances or compounds tested in a set of bioassays, one would use the structure-activity analysis tool. To view the actual bioassay outcomes, one would use the data table tool. 

---