pycbc: A Python interface to the Christmas Bird Count database |  |
pycbc: A Python interface to the Christmas Bird Count database | |
Each of the major tables shown in the diagram in Section 4, “General design notes” will become a table in the SQL representation.
Two more tables are required to manage many-to-many relationships.
Table cir_reg will represent the
many-to-many relation between circles and region
codes. The left-hand key, linking to a circle, is
lat + lon; the right-hand key, linking
to a region, is reg_code.
Furthermore, the relation includes ordering information: for a circle that overlaps multiple regions, there is a primary region, a secondary region, and possibly a tertiary region.
Hence, the intermediate table that defines this
relation must carry an additional column: 0 for the
primary region, 1 for secondary region, and 2 for the
tertiary region. If anyone ever censuses a circle
that overlaps four or more states, we can use the
numbers 3 or more. We'll call this column reg_pos, the region's position.
The primary key for this table will be the
concatenation of lat + lon + reg_pos,
so that retrieval will produce the region codes in
the correct order.
We'll need to support two other queries on this table.
In order to produce a report showing all the
circles that are listed primarily under a given state, we'll
index on reg_code + reg_pos.
That same index, with reg_pos a
wild card, will produce a report showing all the
circles that occur even partially in a given
state.
Table cir_physio will represent the
many-to-many relation between circles and
physiographic strata. The left-hand key is lat + lon and the right-hand key is physio_code.
Again, there is an ordering: some circles have two
stratum codes, but the first one is the principal
stratum code. We will add a column named physio_pos to indicate the position of the
physiographic stratum code for a given circle, with
values of 0 or 1. The primary key for this table
will be lat + lon + physio_pos, which
produces the physiographic stratum codes with the
primary code first.
Here is a revision of our original entity-relationship model
showing the final tables and their relations. Primary key
columns are indicated with an asterisk “*”, and the arrows show the foreign key relations.
For the censuses table we have a choice of two
unique keys. The concatenation of lat + lon + year_no
+ year_key + seq_no is the unique primary key.
It would be nice if every circle were counted exactly once
in a given year number, but there are hundreds of exceptions. Audubon stipulated
starting in the mid-1930s that counts within one year number
should not overlap, but exceptions persisted until the
55th year. This is why the year_key must
be part of the primary key.
We'll need another index to search for kinds of birds. Within
a given circle-year, we must concatenate seven columns to
insure uniqueness: form + rel + alt_form + age + sex +
plus + q. So the secondary index will include these
seven fields, plus lat + lon + year_no + year_key +
seq_no.