Database types, examples 274 INDEX

Multidimensional Database. A relational database in which multiple general types of data are stored, indexed, and cross-referenced, for use by several different groups. In chemistry, an example would be a database containing reactions, 2D structures, perhaps generic structures or libraries, and 3D models. Such a database would be used by synthetic, chemical informatic, and molecular modeling scientists. A data warehouse is often a multidimensional database, whereas a data mart is usually single-dimensional. 

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. 

The next step up in integration places as much information as possible in the chemical structure or reaction DBMS. For example, Molecular Design Limited s MACCS system has been used to store structures, reactions, biology, spectra, inventory, and document index information. This approach provides a good degree of data integration but suffers from poor performance for large databases. Additionally, the retrieval capabilities of the structure DBMS are inadequate for certain types of information. 

A unique index number is assigned to each biosteric transformation record. The first three letters of the ID code specify the chemical type of the starting lead fragment while the subsequent three to four numbers are sequence indicators. The first part of the ID code can be used for a quick search for replacements for a common coded functionality, such as QUI, which stands for quinazoline in the example shown in Figure 4.3. The ID codes in the current version of the database are cataloged in Table 4.1. 

---