Chemistry Reference
In-Depth Information
multiple simultaneous users, even when tables are being updated. When
necessary, particular tables or rows can be locked temporarily while being
updated to prevent any accidental overwriting by two users. There are
also many ownership and privilege options available in an RDBMS. These
allow only some users to select data or to update data in the tables. These
features are absent from spreadsheet programs.
2.5 Relationships among Tables
In a spreadsheet program, each table is essentially independent from
other tables. This encourages the user to grow one table by tacking on new
data columns as new data becomes available. This approach is also pos-
sible in a relational database and might even be more efficient than using
multiple tables. However, it is better to organize information in separate
tables and define relationships among the tables. A set of tables, with its
associated rows and columns along with a definition of the relationships
among them, is called a schema.
2.5.1 One-to-Many Relationships
Consider the EPA Table 2.1. There is no need to use separate tables to store
this information. But, suppose that the need to store data on the water-
octanol partition coefficient (logP) grew. For example, multiple measure-
ments of logP might become important. These could be values measured
at different temperatures or theoretical estimates of logP. It might be
tempting to add other columns, named, say, logP1, Temp1, logP2, Temp2,
clogP1, clogP2, and so forth. When additional columns such as these are
added to a table, the table is said to violate normal form. Normal form is
discussed more fully later in this chapter. It is better to create a new table
to contain only the logP data.
This new logP table could in principle contain columns Temp1, logP2,
Temp2, logP2, clogP1, clogP2, and so forth, but this is still not “the rela-
tional way” to store the data. Instead, consider the nature of the informa-
tion to be stored in order to define which column the table will contain. Of
course, the logP value itself is essential and must be one of the columns.
The temperature is another important piece of information. Finally, the
method used to measure or compute the value must be recorded. So the
logP table would consist of three columns, logP as a numeric value, tem-
perature as a numeric value, and method as text. Using these three col-
umns and multiple rows, it is possible to store any number of values for
logP along with the temperature and method. Notice that there may be
multiple rows for any one compound.
The original table can now have the logP column removed, but how
will the data in the logP table stay associated with the proper rows of the
Search WWH ::
Custom Search