Relational Database Fundamentals - Design and Use of Relational Databases in Chemistry - page 19

2.13.3 Third Normal Form
In third normal form, data values in a column are not intentionally repeated.
For example, the separate logP table was created to satisfy first and second
normal form. But it violates third normal form because the method values
“exp” and “theory” are used repeatedly throughout the table.
T The cor re c t way to con for m to t h i rd nor m a l for m i is to c reate yet a not her
table, here called method. Table 2.4 shows such a table. Table 2.5 shows how
the logP table would be modified to use the method id instead of the text
strings “exp” or “theory.” Notice that this can solve one remaining problem
with both Tables 2.3 and 2.5: there are two rows for compound 1 and method
2. This can arise when a new theoretical method becomes of interest in a
project. Rather than inventing a new string, for example “theory2,” it is a
simple matter to add a new method to the method table in Table 2.4. Here
it would be method 3. The logP table would be altered so that the appropri-
ate rows for compound 1 would use method 2 and method 3. Furthermore,
the method table can be made much more informative, perhaps explain-
ing something about the experimental or theoretical methods, rather than
just using cryptic handles such as “exp” and “theory” in Table 2.3. A more
complete logP method table is shown in Table 2.6.
Some authors advocate analyzing schemas of tables up to normal
form 3 and then backing off to second normal form in order to increase
efficiency. With increasing capabilities of computers and RDBMS, this

Table 2.4 logP Method Table
Containing Text Descriptions

Id Description

1 Experimental

2 Theoretical

Table 2.5 logP Table Using Method
id in Place of Text Methods
Temp cid logP Method_id

25 1 0.35 1

40 1 0.73 1

1 0.55 2

1 −0.11 2

25 5 1.47 1

25 6 0.17 1

25 7 1.15 1

7 1.2 2