Data Storage, Searching, and Manipulation - Design and Use of Relational Databases in Chemistry - page 68

Table 6.2 Amines and Acids from nci.structure Tables Selected in Groups
of 8 Amines and 12 Acids. Only three acids are shown in this truncated table.

Amines Acids

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O c1cc(oc1)C(=O)C(=O)O

c1cc(ccc1C(=O)NC2(CC2)N3CCOCC3)Cl c1cc(oc1)C(=O)C(=O)O

CCC(C)(NC)P(=O)(OCC)OCC c1cc(oc1)C(=O)C(=O)O

Cc1ccccc1N=C(C(=C)Cl)NC(C)(C)C c1cc(oc1)C(=O)C(=O)O

CNC1(CCCCC1)C(=O)O c1cc(oc1)C(=O)C(=O)O

CCOC(=O)NC(C)(C)CO c1cc(oc1)C(=O)C(=O)O

CC(C)(C)NCCS c1cc(oc1)C(=O)C(=O)O

CC(C)C(C(=O)OC)(NC(=O)C)S c1cc(oc1)C(=O)C(=O)O

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CC(CCC(=O)O)N

c1cc(ccc1C(=O)NC2(CC2)N3CCOCC3)Cl CC(CCC(=O)O)N

CCC(C)(NC)P(=O)(OCC)OCC CC(CCC(=O)O)N

Cc1ccccc1N=C(C(=C)Cl)NC(C)(C)C CC(CCC(=O)O)N

CNC1(CCCCC1)C(=O)O CC(CCC(=O)O)N

CCOC(=O)NC(C)(C)CO CC(CCC(=O)O)N

CC(C)(C)NCCS CC(CCC(=O)O)N

CC(C)C(C(=O)OC)(NC(=O)C)S CC(CCC(=O)O)N

CC(C)(C(=O)O)NC(=O)N(CCCl)N=O CCC1C2CCC3=CC(=O)CCC3C2CCC1(C)C(=O)O

c1cc(ccc1C(=O)NC2(CC2)N3CCOCC3)Cl CCC1C2CCC3=CC(=O)CCC3C2CCC1(C)C(=O)O

CCC(C)(NC)P(=O)(OCC)OCC CCC1C2CCC3=CC(=O)CCC3C2CCC1(C)C(=O)O

Select logp From properties Where md5(logp) > md5(logp+1);
Select logp From properties Where md5(logp+1) < md5(logp);

Here, the md5 function is a hash function available in PostgreSQL. It is
used as a method to partition the logp values in the properties table
into two arbitrary sets of about the same size. The less than operator
ensures exactly two sets, and the use of the md5 function ensures that
the sets are arbitrary and of about the same size. Note that using md5
results in arbitrary but not random sets. In other words, each time the
select statements above are run, exactly the same sets will result, as
long as no new rows are inserted. Rather than use this SQL statement
every time the test set is desired, a test _ set view and training _
set view can be defined as:

Create View test_set As Select smiles, logp From properties
Where md5(logp) > md5(logp+1);
Create View training_set As Select smiles, logp From properties
Where md5(logp+1) < md5(logp);

The view test _ set and training _ set can now be used as if they
were actual tables. If there are other criteria desired to define a test set or