Schema PubChem

This section will show how these files can be used to populate a schema of tables designed for PubChem data. While your chemical information may not correspond exactly to this schema, it should be instructive to see how the PubChem schema is designed and used. 

All of the other xml and csv data files from PubChem BioAssay can be used in a similar way to define other tables in the pubchem schema. Many of the assays use the same column names and descriptions as the above... 

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

The pubtest schema was created using the first 25,000 substances from pubchem, namely Substance 00000001 00025000.sdf available from the PubChem project.5... 

Today, I have turned my habit around. When I have a set of chemical structures or data files, my first task is to organize them in a relational database. After all, the tools I now use are designed to read and write tables in a database. Rather than creating folders to keep project files, I create a schema of tables with rows holding chemical structures and data imported from the files. For example, the PubChem project provides information on millions of compounds in the form of hundreds of chemical structure files and associated experimental data files. While PubChem provides excellent Web tools to search this data, for local use I developed a schema to hold the structures and data in related tables. One possible schema for this is shown in Chapter 6 of this book. 

A number of formafs are available for data export. These formats include SDF, image, small image, SMILES, InChl, XML, and either text or binary ASN.l. The PubChem native archive data format is ASN.l all other formats are converted from the original ASN.l. The XML formatted data is exactly equivalent to the ASN.l in content. SDF format is the industry standard for conveyance of chemical sfructure information and is readily imported into a large number of chemistry programs. Unfortunately, the SDF format is unable to handle all aspects of the ASN.l data and may not contain all archived information. The PubChem ASN.l specification, XML schema, and a description of PubChem SDF sfrucfure dafa (SD) fags are all found on fhe PubChem FTP sife in the "specifications" directory. 

---