4. General design notes

The first step in redesigning the older database is to represent it as an entity-relationship model.

circle

Describes one 15-mile-diameter count circle by the latitude and longitude of its center.

region

One political region: a U.S. state, a Canadian province, or the only other nation represented in this database, the circle located on the French islands of Saint Pierre and Miquelon.

nation

One country.

physio

A physiographic region stratum code as defined by the U.S. Fish & Wildlife Service's Bird Banding Lab. For the authority file, see Section 2, “Downloadable files”. This code is useful for grouping circles by their biogeographic similarity. Many circle records have not been coded for physiographic strata. Circles may have up to two physiographic strata codes, and for those that have two, the first code is the major stratum and the second the minor stratum, so the ordering of the two codes is important.

effort

This entity describes one year in which there is a published census of the circle.

kind of bird

This entity represents a specific kind of bird seen in one year, and the number of individuals of that kind that the counters saw. Note that there may be many records for a given species within one effort entity, differing by several details: age; sex; whether seen count day or only during count week; or whether the identification is in question.

Note also that on a few occasions an effort has resulted in zero birds (mainly in remote parts of Alaska and Canada), but this is still considered a valid count.

4.1. Attributes of the principal entities

The tables that represent the entities described above will carry the names of those entities, as plurals. Here are the attributes of these tables, and some discussion of how they are derived from the old database.

4.1.1. Attributes of the `nations` table

For the script that loads this table, see Section 7, “The staticloader script: Populate the static tables”.

nation_code: Three-character code for the country.
nation_name: Full name of the country.

4.1.2. Attributes of the `regions` table

For the script that loads this table, see Section 7, “The staticloader script: Populate the static tables”.

reg_nation: National code for this region, defined in Section 4.1.1, “Attributes of the nations table”.
reg_code: Two-character postal code, e.g., HI or YT.
reg_name: Conventional name of the region, e.g., “West Virginia” or “Province Quebec”.

4.1.3. Attributes of the `circles` table

lat

North latitude of the circle's center in degrees and minutes as ddmm.

lon

West longitude of the circle's center in degrees and minutes as dddmm.

water

Describes whether salt water occurs in the circle. This attribute is not always properly encoded, so the lack of a code does not imply a lack of salt water. Codes are:

(blank)	Unknown or no salt water.
`p`	Pelagic: the entire circle is in open ocean.
`o`	Some open ocean is included in the circle.
`e`	Some ocean estuary is included in the circle, but no open ocean.

odd

Code to indicate an area that is not the standard 15-mile-diameter circle. As with the water attribute, not all circles were properly encoded. Code values may be any of:

(blank)	Standard circle or unknown shape.
`p`	Pelagic-only transect.
`x`	Not a pelagic-only transect, and not a standard circle.

cir_name

The published name of the circle. Many circles have changed their names; generally the attribute in the circles table is the last name used for that particular center. A few standard abbreviations are used:

M.A.	Management Area
N.M.	National Monument
N.P.	National Park
N.W.R.	National Wildlife Refuge
P.P.	Provincial Park
S.P.	State Park
W.M.A.	Wildlife Management Area

4.1.4. Attributes of the `physios` table

physio_code: Two-digit code for the physiographic stratum, with left zero fill.
physio_name: Description of this physiographic stratum, e.g., “Southern Rockies”.

4.1.5. Attributes of the `cir_reg` table

This table represents the many-to-many relation between circles and regions.

lat, lon: Link to the circles table.
reg_pos: Position of this region within the list of regions for the circle. This value is necessary because the regions are ordered. Values are 0 for the first or only region; 1 for the second region; 2 for the third region.
reg_code: Link to the regions table.

4.1.6. Attributes of the `cir_physio` table

This table represents the many-to-many relation between circles and physiographic strata.

lat, lon: Link to the circles table.
physio_pos: Position of this stratum code: 0 for the first or only stratum, 1 for the second.

4.1.7. Attributes of the `efforts` table

Each row of this table represents the censusing of one circle in a given year number.

lat

Latitude, encoded as in Section 4.1.3, “Attributes of the circles table”.

lon

Longitude, encoded as in Section 4.1.3, “Attributes of the circles table”.

year_no

The year number, three digits, with left zero fill. For example, “043” for the Forty-third CBC (December 1942 and January 1943).

year_key

A five-character key that uniquely identifies an effort within a year. In particular, this field can help a researcher rapidly find the published data in the original periodical. See Section 3.5, “Year key” for a discussion of what actually appeared in the published data.

For year numbers 1–90, this column has format NNNNX, where NNNN is the serial number within the year, and X is either blank or a lowercase letter.

For years 91 through the present, this column's format always has the four-character SSKK form.

See Section 8, “Conversion from the old MySQL database” for more information about the origin of this field.

yyyymmdd

The date of the count, if known. For many records this is the date of Christmas because the true date was not recorded in the old database.

as_lat

Published latitude, if known. May be null.

as_lon

Published longitude, if known. May be null.

as_name

Circle name as published. The old database also tracked the region codes as published, but there is no strong reason to retain these data.

n_obs

Number of observers; an integer, one or greater.

ph_tot

Note

This attribute and all the remaining attributes in this table may be null.

Total party-hours, to tenths.

ph_foot

Party-hours on foot.

ph_car

Party-hours by car.

ph_other

Party-hours by means other than foot or car.

h_fd

Hours (not party-hours) by feeder-watchers.

h_owl

Hours “owling” or, as it was later known, nocturnal birding.

pm_tot

Total party-miles, to tenths.

pm_other

Party-miles by means other than foot or car.

m_owl

Miles owling or other nocturnal birding.

4.1.8. Attributes of the `censuses` table

lat

Latitude, encoded as in Section 4.1.3, “Attributes of the circles table”.

lon

Longitude, encoded as in Section 4.1.3, “Attributes of the circles table”.

year_no

The year number, encoded as in Section 4.1.7, “Attributes of the efforts table”.

seq_no

Sequence number of this record within the circle and year. Audubon chose to discard this field in their database, but it is vital to the Christmas Bird Count Database corrections project, because the records must be in the original order to be proofread efficiently against the original publication.

form

First or only form code describing the type of bird. Example: AMEROB for American Robin. These codes are defined in the nomenclature system specification.

rel

(blank)	Not a species pair or hybrid.
`/`	Species pair; the code for the second alternative is in the `alt_form` attribute.
`x`	Hybrid; the code for the second assumed parent form is in the `alt_form` attribute.

alt_form

Second form code when the rel attribute is not blank; null when rel is blank.

Because, for example, “Downy Woodpecker/Hairy Woodpecker” is the same kind of bird as “Hairy Woodpecker/Downy Woodpecker”, the form codes are always ordered such that, lexically, the form code is less than the alt_form code so that a given species pair or hybrid will always have the same representation.

age

Age code.

(blank)	Unknown age class.
`a`	Adult.
`i`	Immature, subadult, or juvenal plumage.
`p`	Female or immature. Yes, female is a sex and not an age class, but we arbitrary place this category under age.

sex

Sex code.

(blank)	Sex unknown.
`m`	Male.
`f`	Female.

plus

Count-week indicator (see Section 3.7, “Count week birds”). Normally blank; contains “+” for count-week birds that were not seen count day.

q

Questionable ID flag. Normally blank; contains “q” if the editor indicated some doubt as to whether this species occurred in the circle at all. Records that are in questions due to abnormally high numbers are not flagged here.

census

Number of individuals. This is often encoded as “-1” when the number is unknown. Audubon's version of the database uses zero when the number is unknown. For count week birds, this attribute is generally -1, but a few such records contain actual numbers.

pycbc: A Python interface to the Christmas Bird Count database