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.
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
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
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
reg_code + reg_pos.
That same index, with
wild card, will produce a report showing all the
circles that occur even partially in a given
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
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
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.
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
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
form + rel + alt_form + age + sex +
plus + q. So the secondary index will include these
seven fields, plus
lat + lon + year_no + year_key +