External representation data type

A data type is necessary to allow the RDBMS to accurately convert the data from an external representation, most often text in a file, to an internal representation of the data. For example, the external representation of a numeric value is a text string containing at least one numeral, and possibly a plus or minus sign or a decimal point. A text value may contain any valid text character, usually only printable characters from the ASCII set. The internal representation of the data is dependent upon the particular RDBMS and hardware being used. It is not necessary to know the exact internal representation of the data. The important thing to consider is which data type accurately represents the data for your purposes. 

As with all data in an RDBMS, there is an external and internal representation of data. This was discussed in an earlier chapter for standard data types, such as text and numeric. For molecular structures, there is of course no SQL standard. When building a database containing molecular structures, a decision should first be made which internal representation will be used and which external representation. 

When using a composite data type, the external representation of the value is different than the basic SQL data types. The components are represented as usual for number and text data types, but parentheses are used to associate the component values. For example, (1.74,nM) is the external representation of the cone value 1.74 nanoMolar. The following SQL produces sample output for an arbitrary compound id. 

The external representation of this data type uses parentheses. This can be awkward, so the following input and output functions are defined. 

It is always possible to use the ordinary external representation of the range data type using parentheses, but using range format and range text conforms to more common representations of data like these. It is possible to automate the range to text conversion even more, using the create cast SQL command, as follows. 

The molfile or sdf file format is a very common way to store molecular structures. This can be considered as an external representation of a molecular structure data type. There are many other common file formats in use and only the essential features common to all of them will be considered here. The essential aspects of molecular structure contained in these files are atomic number or atomic symbol, formal atomic charge, bonded atom pairs, and bond orders. These are the minimum attributes necessary to define an unambiguous valence bond molecular structure. Other atom properties, such as atom types might also occur in these files, but these are specific to particular modeling programs and will not be discussed here. Sometimes molecular properties are also stored in these files. A way to store these properties in relational tables is discussed. 

Structure and data storage is shown on the right. A structure table contains the structures, their internal identifiers, and their external identifiers, if any. The structures are stored in a compact binary representation that includes the connection table, the coordinates, the ring information, and any stereochemical, valence, isomer, isotope, or bond information. Certain types of structure-specific information such as polymer or component designations are stored here, whereas other types of structure-specific information (atom- or bond-specific data, and more verbose text data) are stored in their own tables, referenced by the internal identifier, and the atom or bond numbers to which the data correspond. A formula table contains the molecular formula and various atom and atom-type indexes to enhance formula searching and sorting. 

There are three generically different types of data model representations and several variants within these three representations. Each of these is applicable as an internal, external, or conceptual model. We will be primarily concerned with these three modeling representations as they affect the... 

---