SISMO

For many monitoring projects, data management is often considered a nuisance and of less importance than sampling design, objective setting, and data collec-tion and analysis. Yet a proper database management system is a critical com-ponent of any monitoring plan and should be considered early in the planning process. In many ways, such a system serves as the ship’s log of a monitoring mission and should detail every step of data collection, storage, and dissemina-tion. Sound data management is so vital because a monitoring project adapts and changes over time and as such, so might the data. Furthermore, because most monitoring projects are conducted over many years and include the inevi-table changes in staff, data collection and methodologies, land ownership and accessibility, and shifting technologies, improper data management can fail to document these changes and undermine the entire monitoring initiative. In addition, because online data dissemination and digital archives are becoming increasingly popular (if not necessary), data management serves as a much needed blueprint of instructions for future users of the data who might not have been involved in any aspect of the original monitoring plan.

THe BASiCS OF DATABASe MANAgeMeNT

The data generated from monitoring programs are often complex and the proto-cols used to generate these data can change and adapt over time. Consequently, the system used to describe these data, and the methods used to collect them, must be comprehensive, detailed, and flexible to changes. In a perfect world, monitoring data are collected and often entered into a database (as opposed to being stored in a filing cabinet). A comprehensive database should include six basic descriptors of the data that detail how they were collected, measured, estimated, and managed. Ultimately, these basic descriptors ensure the long-term success of a monitoring effort because they describe the details of data collection and storage.

The six essential descriptors are: what (the type of organism), how many (units of observation for individual organisms or colonies, presence/absence, detection/non-detection, relative abundance, distance measurements), where (the geographic loca-tion at which the organism was recorded and what coordinate system was referenced), when (the date and time of the recording event), how (what sort of record is repre-sented and other details of data-collection protocols; e.g., 5-minute point counts, mist-netting, clover trap, etc.), and who (the person responsible for collecting the data). Each of these components represents an important aspect of data collection that facilitates future use. For example, information on how a recording event was made allows someone separate from the data collection to properly account for vari-ation in effort and detection probability, deal with data from multiple protocols, and determine whether the data are from multiple species or single-taxon records.

THe geNerAL STruCTure OF A MONiTOriNg DATABASe

Unfortunately, there is no “one size fits all” solution to the basic structure of a moni-toring database. Monimoni-toring programs are diverse and so are the data they collect.

There are, however, several basic and standardized templates that can be used when creating a monitoring database (Huettmann 2005; Jan 2006). As an example, the Darwin Core is a simple data standard that is commonly used for occurrence data (specimens, observations, etc., of living organisms) (Bisby 2000). The Darwin Core standard specifies several database components including record-level elements (e.g., record identifier), taxonomic elements (e.g., scientific name), locality elements (e.g., place name), and biological elements (e.g., life stage). Jan (2006) provided another excellent example of a functional structure for an observational database.

Using the terminology of Jan (2006), biological sampling information relates to field site visits, and each of these visits is considered a Gathering. Each Gathering event is to be described by the occurrence and/or abundance of a species and additional site information including site name, the period of time, the name of the collector, the method of collection, and geography. The geography field could indicate coun-try codes using the International Organization for Standardization (ISO) standards (www.iso.org), and it should have an attribute detailing whether this information is currently valid because political boundaries and names change over time (e.g., new countries form, their names can change) (Jan 2006). Geospatial data are stored under the heading of GatheringSite and include coordinate data (e.g., latitude and longitude , altitude), gazetteer data (e.g., political or administrative units), and geoecological classifications (e.g., geomorphological types). It is important that this field allows for high-resolution georeferencing for subsequent integration with a GIS (e.g., using five significant digits for latitude and longitude coordinates). The Unit field includes organisms observed in the field, herbarium specimens, field data, taxonomic iden-tifications, or descriptive data. An Identifications field details the species’ common name, species’ scientific name, and a species code (using the Integrated Taxonomic Information System [ITIS; www.itis.gov]) to a Unit (specimen, observation, etc.).

Identifications can then be connected to a taxon database using a TaxIdRef field. The organization of any monitoring database should have these necessary information fields (although field names may vary) and will likely require the use of a digital database for storage and manipulation.

DigiTAL DATABASeS

Digital databases are now considered an invaluable and commonly used tool for stor-ing data generated from monitorstor-ing programs. Even in remote field sites, researchers are using mobile Global Positioning System (GPS) and Personal Digital Assistant (PDA) units to record georeferenced census tracks and species observations (Traviani et al. 2007) (Figure 10.1). Using any laptop computer, these data can then be quickly integrated into database management software such as CyberTracker (cybertracker.

org), Microsoft^® Excel (office.microsoft.com/en-us/excel), or Microsoft^® Access (office.microsoft.com/en-us/access). By using a digital database, researchers gain the ability to georeference census points for later integration into a GIS, such as ArcGIS

Database Management 181

(esri.com/software/arcgis/), allowing for additional analytical options such as pre-dictive species distribution modeling (Figure 10.1). Traviani et al. (2007) provided an excellent review and application of a field-based database framework for using digitally stored data to subsequently map animal distributions in remote regions.

A key advantage to recording data into a digital database during the collection event itself is the ability to develop and maintain multiple databases. Digital databases also increase the capacity to integrate data into online data management programs and thereby to access data at later dates.

In addition to this, database managers often use online and digital databases because they can be readily linked to other databases for greater functionality. Connecting multiple databases results in a relational database management system (RDMS) (Figure 10.2), which allows for queries to be made among multiple databases. In light of these developments, the structure and framework of many large monitoring data-bases are increasingly sophisticated and data on demographic rates, abundance, and species occurrences can be linked with other geographic information stored in ancil-lary databases. For example, a standard relational database can consist of subtables of data that are connected through a common record ID number (Figure 10.2). A Main table normally contains information on the sampling units, the units used for data

Objectives

Select sample sites Locate sample sites (GPS) Locations of animals

Figure 10.1 A flowchart of an integrated framework for using GPS and PDA technol-ogy for collecting monitoring information. By collecting monitoring data in a real-time, digital format these data can be used for more sophisticated purposes such as species dis-tribution mapping. Tasks highlighted in bold indicate the places in which advanced methods can provide increased accuracy. (Redrafted from Traviani, A., et al. 2007. Diversity and Distributions, 13.)

presentation, the years of the study, and notes on sampling design. Other frequently used tables include a Taxon table (information about the organism sampled in each data set; see ITIS [www.itis.org] for globally accepted species names), a Biotope table (habitat of the organism), a Location table (geographical details of the monitoring site), a Datasource table (reference to the original source of the data), and the actual Observation table (original population data). In this case, a relational database and a common record identifier enable the user to perform multiple queries based on species, taxonomic group, habitats, areas, latitudes, or countries. This is particularly powerful because a user can query a unique identifier that refers to a specific study site and then extract data on that site from multiple data tables. In practice, developing, main-taining, and retrieving data from an RDMS often requires knowledge of Structured Query Language (SQL; http://en.wikipedia.org/wiki/SQL), a widely used database filter language that is specifically designed for management, query, and use of RDMS.

SQL is a standardized language with a huge user community that is recognized both by the American National Standards Institute (ANSI) and International Organization for Standardization (ISO; iso.org/iso/home.htm). It is implemented in many popular relational database management systems including Informix (ibm.com/software/data/

informix), Oracle (oracle.com/index.html), SQL Server (microsoft.com/sql/default.

mspx), MySQL (mysql.com), and PostreSQL (postresql.org).

DATA FOrMS

All data collectors should use a standard data form that is approved by stakeholders in the monitoring program (Table 10.1). Copies of these data forms should be included as an appendix to the planning document. The appendix should also provide a data

Main Table

Figure 10.2 A relational database management system combines data from several dif-ferent databases. These databases are typically linked by a standard unique identifier—in this case, a site identity number—which allows a user to extract data from multiple data sets.

Database Management 183 TABLe 10.1

example of a Field Data Sheet used in Association With a Project Designed to Monitor the Occurrence and Number of Detections of Birds in Agricultural Lands in the Willamette Valley of Oregon

Date _________________ Sample Point_________________ Landscape____________________ Observer __________________

Weather __________________________ Time Begin___________________ Time End __________________

Obs. Num. Species Number Distance (m) repeat (Y/N) Behavior Patch Type

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Note: The inset photograph is used not only to locate sample points but also to plot locations of bird observations.

Species: Use 4-letter code.

Number: Number of individuals.

Repeat: Enter “Y” only for repeat observations of the same bird.

Behavior: F(eeding), R(esting), O (flyover).

Patch type: P(lowed), G(rass), W(Grassed waterway), S(hrubby), T(reed).