Applications - Design and Use of Relational Databases in Chemistry - page 164

information about how the measurements were made. In order to accom-
modate the use of samples, it is necessary to create a sample table and use
sample ids in the experimental data tables in place of compound ids. A
sample table could be created as follows.
Create Sequence uniq_samples;
Create Table sample (id Integer Default Nextval('uniq_samples'),
cid Integer References registry.structure (id),
prepared Timestamp(0) Default current_timestamp,
Unique (id, cid));
This introduces a new way in which sequences are used within a database.
In previous chapters, the Serial data type was used to create a column of
unique integers. When the Serial data type is used, a sequence is auto-
matically created and the default value of the column is set to be the next
value in the sequence. In this way, a unique set of integers is ensured. In the
above example, more control is needed over the use of sequence values in the
sample.id column. This is because this table may contain several rows with
the same sample id , each with a different compound id . The sample con-
sists then of all compounds in the sample table having the same sample id .
When a new sample is inserted into the sample table, only the
sample.cid need be specified. The default value for id will cause a new
sample.id to be taken from the uniq _ samples sequence. The default
value for sample.prepared will cause the current time to be inserted
into that column. If a further specification of the same sample needs to be
made, the following SQL will suffice to add compound id 55.
Insert into sample (id, cid) Values (currval('uniq_samples'), 55);
The currval function uses the current value of the sequence. This can be
repeated as many times as necessary to complete the definition of the
sample with that sample.id .
The final clause in the create statement above demands that the combi-
nation of sample.id and sample.cid be unique. This allows many rows
with the same sample.id . Each sample can contain many compounds. It
also allows many rows with the same sample.cid . Each compound can
be a part of many samples. It forbids the same sample.id from contain-
ing the same compound ( sample.cid ) more than once.
13.4 Data from External Sources
When creating a schema of tables for projects underway at your company
or research institution, there is complete freedom to define the tables as
necessary to accommodate the data correctly. Sometimes it is necessary
or desirable to import data from an external source. In this case, a careful