Compound schema

Follow-up testing performed after confirmation is usually to determine IC50 or EC50 values. These tests require preparation of each compound in a series of different concentrations according to twofold diluting schemas (the concentration of each consequent point is half the concentration of the previous point in the series with the half log, every other point would differ in a magnitude of concentration). The number of concentration points in a series can range from 2 to 12 (Quintero et al., 2007 Turner and Charlton, 2005). 

This method permits vinylation of aromatic compounds. In the isoflavanone synthesis, 4-chromanone (44) is converted to the enol ester 45, which is reacted with phenylpalladium acetate, formed in situ from phenylmercury chloride and Pd(OAc)2, to give — after hydrolysis - isoflavanone (46) (Schema 15). A simple synthesis of... 

Let schema S = Alt A2, , An be a finite set of attributes. Attributes can be both simple and compound, compound attributes should not necessarily be disjoint, etc. For example, an attribute in a schema of a hotel reservation Web site may be lastName and firstName. A compound attribute may be creditCardlnfo combining four other attributes, type, cardNum, securityCode, and expiry (which could also be a compound attribute, representing month and year of expiration). We define an attribute to be categorical if its domain contains a closed set of categories, e.g., a type attribute. 

This model captures the essence of schema matching, namely matching of attributes, and therefore a richer representation of data models is not needed. Therefore, if we aim at matching simple attributes (such as lastName and firstName) we need not represent their composition into a compound attribute called name. If the goal of our schema matching process is to match XML paths (see, e.g., Vinson et al. 2007), then XML paths are the elements we define as attributes in our schemata. 

In Proceedings BTW Conf., Aachen, pp 449 164 Lenzerini M (2002) Data integration A theoretical perspective. In PODS. ACM, NY, pp 233-246 Lemer BS (2000) A model for compound type changes encountered in schema evolution. TPCTC 25(1) 83-127... 

Many-to-many relationships are regularly encountered in chemical databases. In the case of many compound vendors and many compounds, the ERD in Figure 2.2 shows how three tables can be used to define a many-to-many relationship. The many-to-many type of relationship will be seen in other examples in later chapters. In Figure 2.2, the vendor table contains only as many rows as there are vendors. The compound table contains only as many rows as there are compounds. Each row in these tables contains the information available for each compound or vendor. No information is repeated in other rows or tables. The vendor compound table contains many more rows, one row for each compound offered by each vendor. This table is the largest in this schema, yet it contains only integers, which are easily indexed and efficiently stored. 

A primary key column in a table is also a unique column. There is often one central table in a schema by which the other tables in a schema are related to each other. In the EPA schema discussed here, epa.compound is the central table and the cid column is the unique, primary key. The purpose of the epa.compound table is to provide a central registry of compounds that are of interest in the schema. The key column is typically defined using a sequence to ensure uniqueness. 

When designing a schema to hold chemical information, it is crucial to first consider how the data will be used. One approach is to interview potential users of the database to determine what questions need to be answered on a regular basis. For example, users of a chemical compound tracking system will typically need to know the following ... 

Another task in selecting or designing a schema is to determine which operations need to be performed. For example, in a compound-tracking system, compounds need to be registered and samples of compounds need to be taken (checked out). For a schema to function well, it should be relatively simple to update or insert data into the tables of the schema to record these activities. 

The vast majority of chemical information consists of text or numerical data associated with a particular compound, or perhaps a mixture of compounds. Some chemical data cannot yet be associated with any particular structure, or has been measured for compounds whose structure is not yet known. It is important to consider these possibilities when designing a schema of tables to store chemical information. 

This schema can be expanded in many ways. For example, other information about the sample can be added, such as whether the sample is a liquid, crystal, solution, etc. If necessary, a table might be used to store the sample ids of toxic or radioactive compounds, or of compounds monitored by some governmental regulatory agency. Rather than trying to foresee all possibilities and add columns to the sample table, it is much simpler and more robust to add new tables as new information becomes available or necessary. 

Any number of other tables can be added to this schema. Each should be related to the structure table using the compound id. Aside from simply registering compounds, it might be required to store experimental data... 

It might be helpful to delay the creation of the indexes when the schema is first created and its tables populated. This is especially true if millions of compounds are to be entered at one time. However, if there are duplicate structures and the table contains even two rows with the same isosmi, it will not be possible to create a unique index on the isosmi column until only a unique set of isosmi values exists. The creation of a unique index does not fix nonunique values. It simply prevents nonunique values. In order to find duplicate structures in a table, the following SQL can be used. 

The first section of this chapter showed how a schema of tables could be used to create a compound registry. Using that schema, this section will show how experimental data can be integrated with compound data. A separate schema will be used to store the experimental data. In fact, several schemas will be created in order to segregate data tables for separate assays or projects. This is not essential, but is handy for browsing data tables in a large database. Schemas are analogous to folders in a file system. 

This table is intended to hold results of assays testing compounds in reg-istry.structure for activity as human immunodeficiency virus (HIV) protease inhibitors. As new assays are added, the test results can be added to newly created tables with similar definitions. For example, there might be tables for HIV reverse transcriptase inhibitors stored in a table named hiv.rt. Other assay results might be stored in new schemas, for example, fpr.htfc for high-throughput flow cytometry results for the formyl peptide receptor (FPR), or f pr.ca for FPR cell adhesion assay results. Each of these tables would have columns of data named and typed appropriately for each assay. Each table would have a column containing a compound id that references compounds in the registry, structure table. 

It is not possible to propose a schema with tables that can accommodate experimental results of any type. It is important to consider the needs of each project and assay so that appropriate tables can be created with the necessary data types and constraints. One common feature of any table of experimental data is a column containing a reference to a chemical compound or compounds involved in the experimental measurement. While the examples so far have considered only one compound for each test result row, it is important to consider how results will be handled when multiple compounds are involved in each experimental measurement, or when multiple measurements are made for the same compound with samples prepared at different time or perhaps in different ways. A common way to handle these situations is to use the concept of a sample. 

Figure 13.2 Entity relationship diagram for schema containing tables for a compound registration system.

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. 

---