**IWCBM Program**

**Data Management System**

Jonathan Bart

1 March 2006

(*NOTE: The permanent IWCBM web site address is given in this document
but at present \[4 Mar 2006\] the web site is still under development;
it may be found at http://iwcbm.dev3.fsr.com*/)

**Contents**

[Introduction 1](#introduction)

[Data Entry, Storage, and Retrieval
2](#data-entry-storage-and-retrieval)

[Information needed when the survey is designed
2](#information-needed-when-the-survey-is-designed)

[Restricting distribution or presentation of results
2](#restricting-distribution-or-presentation-of-results)

[Data entry 2](#data-entry)

[Retrieving data 4](#retrieving-data)

[Design of the Database 4](#design-of-the-database)

[Sampling Plan tables 5](#sampling-plan-tables)

[Surveys and Counts tables 6](#surveys-and-counts-tables)

[Master tables 7](#master-tables)

[Schema 7](#schema)

# Introduction

This report describes how data will be obtained, stored, and distributed
in the IWCBM program. This Introduction and the first section, "Data
Entry, Storage, and Retrieval", are for anyone using or considering
using the IWCBM data base. The sub-section on data retrieval describes
how, and under what conditions, the detailed data can be retrieved. A
separate report, "IWCBM -- Analytic Tools", available at the IWCBM web
site, <http://greatbasin.nbii.gov/iwcbm/>, describes the analytic tools
we intend to create. The second part of this report, "Design of the
Database", is for database programmers and others wishing technical
information about the database.

We anticipate using a "distributed, federated" approach to data
management. In this approach, those responsible for data collection own
the data and make all decisions about how the data are distributed. They
are responsible for initial entry, error checking, and deciding what
information, if any, is contributed to the IWCBM program. This is the
"distributed" part of the approach.

The federated part of the approach is that the community of data users
decides on certain core variables which will be provided by all data
owners. The Avian Knowledge Network, being developed by the Laboratory
of Ornithology at Cornell University and many collaborators, will
provide software for "harvesting" these core data from data providers
and storing them in a central warehouse where they will be available to
everyone (but subject to restrictions established by the data owners).
It is unclear when this system will be fully operational, so, as an
interim measure, we are creating a central repository on USGS servers in
Boise. Data providers will be able to submit data on spreadsheets (which
we will provide) or in other digital forms for entry into this
repository. They can thus maintain their own databases and periodically
upload data to the central repository or they can store their data
directly to the central repository using our customized spreadsheets.
Some experts in data management, particularly at Patuxent, have
cautioned us that bulk uploading can become very labor intensive. We
will use this approach initially, because we anticipate that the volume
will be low, but may have to switch to internet-based data entry -- or
ideally to the automatic harvesting system Cornell is developing --
within a year or two.

# Data Entry, Storage, and Retrieval 

## Information needed when the survey is designed

Most sampling plans in the IWCBM program involve multiple stages. In the
Aquatic Bird Survey, the stages may be BCS, designated area,
sub-division of the area (e.g., impoundment), plot, year, and day (if
\>1 survey is conducted per day). Using the data requires aggregating
results from different surveys, plots, sites, and so on, and this, in
turn, requires appropriate weights. The number of sub-units into which
any given unit (e.g., a designated area) is sub-divided must also be
recorded. This information is provided once, in a "Plots" table, when
the survey is designed or re-designed. IWCBM staff are available to help
encode this information.

## Restricting distribution or presentation of results

Data stored in the IWCBM database is owned by the people or agency that
provides the data to the IWCBM program. Accordingly, if the data owners
wish to restrict distribution of their data, they may do so. A series of
options will be available. Some data (e.g., from the BBS) are freely
available over the internet. Other data may be used in analyses but only
reported in combination with other data or only at a user-specified
level of resolution. Still other data will be available solely by
passwords chosen by the data owners. This option will presumably be used
by data owners who do not have a repository of their own and wish to use
the USGS server as their primary storage location. Please contact the
IWCBM staff to discuss these options.

## Data entry

Uploading existing files from other repositories will be handled one
provider at a time. Please contact the IWCBM staff at
<iwbirds@gmail.com> if you would be willing to consider contributing
data to the central repository.

We will provide a series of spreadsheets for those who wish to enter
data and store it on our servers. These spreadsheets will be customized
for each location so that they request only needed information and use
terminology (e.g., as column headings) that are meaningful to people
familiar with the site. The spreadsheet will contain the Sampling Plan
worksheet described above, a Surveys worksheet on which information
about when surveys were run, weather, etc. is recorded; and a Counts
worksheet on which observations are entered. IWCBM staff will design the
spreadsheet and upload data from it to the central repository. The
Surveys and Counts worksheets are described below. If you are interested
in possibly using this data entry facility, please contact us at
<iwbirds@gmail.com>.

The "Surveys" worksheet is used to record the information about each
survey.  Users complete the following variables (location is
pre-recorded on the worksheet):

 

Start date (required)

End date (default = Start date)

            Distribution (optional)

Start time (required)

End time (optional but recommended)

Observer ID (optional but recommended)

Number of observers (optional but recommended)

Weather variables (optional, *to be defined*)

Additional variable 1 (optional; user-defined)

Additional variable 2 (optional; user-defined)

Additional variable 3 (optional; user-defined)

Additional variable 4 (optional; user-defined)

Additional variable 5 (optional; user-defined)

 

It is assumed that within a location the date and start time uniquely
identify a survey. If two surveys start at the same time, the starting
time should be changed by one minute on one of the surveys. In many
surveys, the Counts worksheet will have only date, start and end times,
observer, and possibly some weather variables.

 

The observations are entered on a "Counts" worksheet. Users enter the
following variables (location is pre-recorded on the worksheet):

 

Plot ID (required)

Distribution (optional)

Date (required)

Start time (required)

Species (required, a drop-down list with common names)

Age (optional, dropdown list)

Sex (optional, drop-down list)

Other variable -- 1 (optional, e.g., territorial/floater, dark
morph/light morph)

Other variable -- 2 (optional)

Sub-period (optional; used with some point count methods)

Distance (optional; used with some point count methods)

Number recorded (required)

 

The Plot ID uniquely identifies the location at which the data were
collected. The Distribution field is similar to the Distribution field
in the Surveys table but is used to restrict access to data for
particular (e.g., T&E) species.  In most surveys, most of the variables
above are not used. Frequently, the Counts worksheet will have only
plot, species, and number.  

## Retrieving data

As noted above, the data owners will control distribution of their data.
Some programs (e.g., BBS, Avian Science Center) have stated that their
data may be distributed to anyone via the internet. Other program
managers have indicated that they may be able to make their data
available in the future. Other managers will place restrictions on some
of their data (e.g., information about Endangered Species, data
collected on private land). Data owners who restrict access to their
data may permit their data to be used in analyses where results will not
be reported at a level of resolution finer than some threshold level
such as a BCS.

To download detailed data, users will use a FilterTool that lets them
identify any variables in the database, specify desired values for them
(e.g., single values, ranges), construct SQL queries, and download all
the data matching their specification. The FilterTool will return three
tables: the Surveys and Counts table and a table similar to the Plots
table but with additional information the user selects such elevation or
State the plot is in. Fields to be included in the downloaded Counts and
Surveys tables will be selected by the user. Downloading detailed data
will be most useful to users with programming -- or at least database --
experience.

A series of analytic tools will be produced to assist users in studying
their data. See "IWCBM -- Analytic tools", available on the IWCBM
website (http://greatbasin.nbii. gov/iwcbm/) for a description of these
tools..

# Design of the Database

This section was written for database programmers and for IWCBM staff
who will help participants in the IWCBM program create the information
needed to use the IWCBM data management system. We assume the reader is
familiar both with the IWCBM program and with basic survey sampling
terminology.

The database is designed to accommodate up to ten-stage sampling.
Sampling units at all stages, including the final one, may vary in size.
The population unit is defined as the final sampling unit thus
population units may vary in size (in contrast to the usual definition
in survey sampling). Also, a "stage" includes stratification and thus
means partitioning some or all population units into groups some *or
all* of which will then be selected. Population units are typically
defined in space and time, e.g., a plot at a specific time. Stages thus
may involve sub-dividing either space or time. Note that in this
treatment, surveys of a given plot at different times are viewed as
obtaining data from different population units because survey time is
part of the definition of the sampling plan. Four basic tables, plus
additional tables for house keeping, are defined:

1\. Sampling Plan tables

2\. Surveys tables

3\. Counts table

4\. Master tables

Each of these is discussed below.

## Sampling Plan tables

One Sampling Plan table is created for each of the ten levels (Plan1,
Plan2, etc). These tables store the information needed to aggregate
results across different spatial scales. The variables, for the Level
*k* table, are:

Program -- The survey (e.g., BBS, Aquatic Bird Survey)

Level1 -- ID for level 1

Level2 -- ID for level 2

... - additional rows for each lower level

Level*k* -- ID for level *k*

Date1 -- the first date to which this row of information corresponds

> Date2 -- the last date to which this row of information corresponds

Type - Description (e.g., State, NWR); used as a link with Master Tables

Size - "size" of the unit (see below)

capN - number of units the focal unit is sub-divided into (see below)

The "level" fields provide the "pedigree" of the sampling unit. The "not
applicable" code is -1. For example, 50-13-1986, followed by -1s, means
the level 1 ID was 50, the level 2 ID was 13, and the level 3 ID was
2006. In the Aquatic Bird Survey, these might correspond to BCS,
designated area, and year. The pedigree 50-13-3-1986-1, followed by -1s,
means BCS 50, designated area 13, sub-division (e.g., impoundment) 3,
year 1986, period 1 (e.g., spring migration). Sampling plans may change
through time. The Date1 and Date2 provide the range of data for which
the remaining information is valid. Date2 = "current" means "use until
further notice".

The Type field identifies a "Master Table" that provides additional
information about the sampling unit (see "Master Tables" below). Size
serves two purposes. It is used as a weighting factor in aggregating
means/plot. For example, suppose the level refers to areas and the mean
per plot was the same for two areas, but one area was twice as large as
the other. Then, in estimating overall density, we would want to weight
the mean from the larger area twice as heavily as the mean from the
smaller area. In the same way, if the level referred to periods of time,
but one period was twice as long as the other, then we would want to
weight means/survey differently. For this purpose, the size variable
only needs to be proportional to area or time.

The other purpose of size is to estimate population totals (i.e., the
sum of the means/unit for all units in the target population). For
example, if the mean/unit is 2 and there are 100 units in the target
population at this level, than an obvious estimate of the total is 200.
For this purpose, actual areas must be known. For levels defined in
time, however, the "actual size" is often not well-defined (because the
sampling plan involves a survey at "an instant in time"). In such cases,
only relative sizes are needed. Years are usually given weights of 1.
Survey periods within years are usually given weights that are fractions
(focal period/entire survey season). One exception, however, is in
estimating "bird-use days", where one bird spending any part of one day
in the focal area is defined as one bird-day. When this quantity is of
interest, then the size for a year or a period within a year is number
of days. Periods within days are also sometimes defined. For surveys
during the breeding season, periods are usually thought of as
"opportunities to determine how many birds are present", the assumption
being that the same (average) number are present during each period.
Consequently, the size variable is set to a constant (usually 1) so that
means per survey from different periods are given equal weight. In other
cases, especially outside of the breeding season, the objective may be
to estimate the mean number present, assuming that it may vary with time
of day. In such cases the size variable is proportional to the length of
the period. Decisions about how to code the size variable are made when
the sampling plan is developed. The IWCBM staff will discuss these
options with potential data providers.

The capN variable is the number of units into which the focal unit is
sub-divided. For example, if the focal unit is a NWR, then capN would be
the number of impoundments, or other defined areas, into which the NWR
is partitioned. The capN are used mainly to calculation finite
population corrections. If the sampled population is smaller than the
population of interest (i.e., some areas are inaccessible), then capN
should be calculated so that it is approximately the number of sub-units
that would exist if the entire population of interest was sampled. IWCBM
staff can provide advice on how to define capN.

## Surveys and Counts tables

These tables are described above under "Data entry". Columns giving the
rest of the pedigree information (e.g., program, BCS, etc) for each
location will be created when the spreadsheet is entered. They may be
hidden, and filled in by IWCBM staff, or users may be asked to fill them
in.

## Master tables

The Master tables contain information about areas or periods. For
example, tblYears might contain information on annual weather variables
so that trend data could be analyzed in relation to these variables. A
tblNWRs might contain information about National Wildlife Refuges. A
tblPlots might contain information (e.g., elevation, habitat) on the
survey plots. Definition of the Master Tables, and the process for
linking their information to specific Surveys and Counts, is under
development.

## Schema

To be completed.