Integrity RDBMS

For a variety of appHcations such as computer-aided engineering systems, software development, or hypermedia, the relational database model is insufficient. In an RDBMS, it is difficult to model complex objects and environments the various extensive tables become complicated, the integrity is problematic to observe, and the performance of the system is reduced. This led to two sophisticated object-based models, the object-oriented and the object-relational model, which are mentioned only briefly here. For further details see Refs. [10] and [11]. 

There are many books that describe relational database management systems (RDBMS) and the structured query language (SQL) used to manipulate the data. Understanding SQL is important, and this book contains an introduction to SQL. However, the focus is on the concepts of relational data. One goal is to show how a proper integration of a new molecular structure data type yields a powerful, extended relational database for use in chemistry. For those of you new to relational databases, it is expected that the SQL introduction will suffice for your understanding of the concepts in this book. For those of you already familiar with SQL, it is hoped that you will see how the extensions described here provide a powerful, integrated way to handle molecular structures within the database. In either case, there are plenty of practical SQL examples contained in this book. 

Some of the more advanced methods described in this book require a more specific use of the RDBMS. The choice made for this book is PostgreSQL. In cases where a particular feature of PostgreSQL is used, a note is added to alert the reader. For example, the array data type in SQL2003 is implemented in PostgreSQL very differently than in Oracle. The list matches function described in a later chapter of this book returns an array of integers that denote which atoms in a structure match a substructure query. The integration of this function into SQL would be handled quite differently in PostgreSQL, Oracle and MySQL. 

The SQL domain allows one to define which values are to be allowed in a particular column of a table. A domain is created by stating the underlying built-in SQL data type used to store the domain data type. In addition, a check constraint function may be used to allow or forbid certain values. This can be used to great advantage for SMILES and canonical SMILES. Using a domain improves the ability of the RDBMS to maintain the integrity of the data contained in its tables. 

Because of the intimate relationship between data in the CDBMS and the RDBMS, there is frequently a need simultaneously to access and correlate data from the two systems. This requirement has led to a number of approaches to integration of the two databases which have been previously reviewed. Most approaches result in a duphcation of software functionahty and user training requirements, as well as restricted retrieval and reporting capabilities. Since CDBMSs and RDBMSs normally support different data models and data definition and manipulation languages, any approach to integration based on multiple DBMSs will inevitably have to address many thorny compatibility issues. 

In this paper, we advocate an approach to the integration problem in which an RDBMS based on the SQL language is extended to encompass chemical structure and other complex data types. This approach integrates all data normally stored separately in a CDBMS and RDBMS into a single extended RDBMS. The resulting system provides a single uniform data model and data manipulation language that can be applied to chemical structures and associated data simultaneously. The... 

---