May 2018
Beginner to intermediate
452 pages
11h 26m
English
Plot checks are the actual data records collected at individual plots. These are part of a lab check, and so must refer back to an existing lab check.
We'll begin with the primary key columns:
CREATE TABLE plot_checks(date DATE NOT NULL, time TIME NOT NULL,lab_id CHAR(1) NOT NULL REFERENCES labs(id), plot SMALLINT NOT NULL,
This is the primary key of a lab_check table plus a plot number; its key constraints look like this:
PRIMARY KEY(date, time, lab_id, plot),
FOREIGN KEY(date, time, lab_id)
REFERENCES lab_checks(date, time, lab_id),
FOREIGN KEY(lab_id, plot) REFERENCES plots(lab_id, plot),
Now we can add the attribute columns:
seed_sample CHAR(6) NOT NULL, humidity NUMERIC(4, 2) CHECK (humidity BETWEEN 0.5 AND 52.0), ...