PROPOSAL FOR UNIFORM PFIESTERIA MONITORING DATABASE: Version 1.0
December 1998
Prepared for:
NOAA Chesapeake Bay Office
410 Severn Avenue, Suite 107
Annapolis, Maryland 21403
Background
In 1996, the Chesapeake Executive Counsel adopted the "Strategy for Increasing Basin-wide Public Access to Chesapeake Bay Information". This strategy calls for the Chesapeake Bay Program Partners to develop the Chesapeake Information Management System (CIMS). The Goal of CIMS is to electronically link a variety of information sources about Chesapeake Bay and its Watershed and make this information available to anyone–government agencies, scientists, and the public–electronically through the Internet and World Wide Web. The information targeted by CIMS includes technical and public information, educational material, environmental indicators, policy documents and scientific data.
As a result of the CIMS initiative, the Chesapeake Bay Program is working to establish a system of distributed databases. In the ideal system, all databases would be created and managed by the data originator, reside with the data originator, and published over the Internet from the data originator’s institution on an Internet server. This system has several advantages over the traditional single data repository. Primarily, quality is increased since the people with the most expertise and knowledge about the data, the originators, manage the data. Additional advantages include reduced cost due to elimination of intermediate data handling at a central repository, and decreased time between collection and release of the data.
The key to the success of a distributed data management system lies in the willingness of data generators to take responsibility for the quality and maintenance for their data as well as and in their adherence to established data standards. As part of the implementation of CIMS, the Chesapeake Bay Program has designed a series of relational database structures for managing various types of environmental monitoring data. The use of identical database structures by multiple agencies will facilitate implementing search engines and combining data from multiple sources. It is believed that the various Pfiesteria monitoring programs could greatly benefit from taking a similar approach to their data management needs.
The current design has been implemented in Microsoft ACCESS due to its relatively low cost and ease of use by most people. The design can be implemented in other relational database programs with little modification, if desired. The primary benefit of adopting this database design, is that it incorporates the data dictionaries adopted by the Chesapeake Bay Program and adheres to CIMS compliant policies and guidelines and meets CBP Grant requirements.
The actual ACCESS database (Version 1) includes the capability to manage:
- Water quality parameters (physical, lab, nutrients, metals)
- Biological (fish, plankton, benthic, species, taxonomy, life stage, counts, weight, volume)
- Media (water, sediment, air)
- Methods (biological, chemical, lab, method code, gear, tows)
- Location (site, station, lat/long/datum, depth, date/time, FIPS, HUC)
- Other (program, project, weather, problem code)
This current design does not have customized data entry screens or reporting. Those capabilities can be added for a modest investment if this database design is adopted for use.
This document is not intended to provide a complete discussion of the concepts of a relational database. Instead, this document describes a proposed database structure, which may be usable as a uniform database design for the nation-wide Pfiesteria Monitoring efforts.
Introduction
- Pfiesteria Monitoring Data
This database was designed to provide a common-format database for management of the environmental data derived from the multi-state Pfiesteria monitoring efforts. The Pfiesteria monitoring programs in the various jurisdictions are diverse multi-component programs with water quality, plankton, fisheries and human health components. Version 1.0 of this database was designed based on current Chesapeake Bay Program water quality, plankton and fisheries relational databases. There were no actual field or laboratory Pfiesteria monitoring data available from the states for design testing. Therefore version 1.0 of this database is a DRAFT and will need to be modified to accommodate actual data, to provide data entry screens, and develop standardized reports. The design does follow the CIMS policies and guidelines and meets Chesapeake Bay Program grant requirements.
- Relational Data Structure
In a relational database, data is stored in tables, which are linked to one another by common fields. Most tables are related to one another in a series of one to many relationships. In this type of arrangement one record in the "Parent" table is related to many records in the "child" table. Common fields are set as primary and/or foreign keys. The creation of relationships between tables using key fields allows for the enforcement of referential integrity. Referential integrity protects the database from being corrupted by prohibiting the user from entering records into a child table for which there is not a corresponding key field in the parent table.
This database also employs the use of auto-generated key fields. An auto generated key field cannot be edited; it is a unique, sequential or random number automatically assigned to each new record added to the table. In the case of this database, auto-generated keys are assigned to unique records based on a combination of fields in the parent table. The auto-generated key is then added to a child table as part of its primary key. This serves to increase the efficiency of the database and decrease data recovery time.
The following relational data structure for the Pfiesteria monitoring data contains descriptions of the primary data tables as well as the numerous lookup tables required to define in detail the codes contained in the primary tables. The table columns in this document used to describe the fields in the database tables are described below.
PRIMARY TABLE - This table contains the bulk of the data in the database and are generally related to one another by multiple key fields.
LOOKUP TABLE- Lookup tables are a class of tables which hold code definitions and/or lists of accepted values. These tables are usually one table in one to many relationships.
FIELD - This column contains the field name in the database table as well as the designation of the field as either a primary key (PK), a foreign key (FK), a not null (NN) field, a unique field (U) or a auto-generated key field (AK). Primary, foreign and auto-generated key fields, by definition, are not null fields. However primary and foreign keys may contain zero length value fields. Fields which are neither primary nor foreign key fields, but which have been designated as not null or unique are those fields deemed essential to certain applications of the database.
DESCRIPTION - This column contains a definition of the database table field.
TYPE (FORMAT) - This column specifies the field type as text, number, small integer, or date/time; it also includes the format of the field and the precision of the numeric value where appropriate (primary tables only).
LENGTH (BYTES) - This column specifies the maximum character or numeric length of a field as well as the internal database storage requirement (primary tables only).
AUTO-GENERATED FIELD- By definition these are primary or foreign key fields. As mentioned above a unique record for an auto-key is based a combined primary key field. The fields that make up the combined primary key in child tables have been maintained in this version of the database for ease of properly assigning auto- keys. These fields may be dropped in subsequent versions of the database since they are unnecessary once the appropriate auto-key is assigned to a data record.
Pfiesteria Monitoring Database Structure
- PRIMARY DATA TABLES
- SECONDARY LOOK-UP TABLES
Within the current design, the primary tables are as follows: SURVEY TABLE, CHEMICAL_ANALYSIS TABLE, FISH_EVENT TABLE, INDIVDUAL_FISH TABLE, TAXONOMIC_IDS TABLE and WEATHER_HABITAT TABLE. The SURVEY TABLE contains all sampling event data for all types of sample collection events. The FISH_EVENT TABLE contains all sampling event data specific to fish collection events. The remaining table all store data of the type designated in the table name.
SURVEY_TABLE
GENERAL: Every event for which there were sample taken of any kind must have a record in this table. Event records must be loaded into the database first and all unique records are assigned a SURVEY_ID number. The SURVEY_ID must then be merged onto all other data based on the key fields before data may be loaded into any other primary data tables. The SOURCE, PROJECT and PROGRAM fields have been included to provide identification of who generated a sample (SOURCE), as part of which program (PROJECT), and which component of a given program (PROGRAM). The fields LATITUDE, LONGITUDE and LL_DATUM provide the ability to geographically locate a STATION or sampling site. The FIPS and HUC_8 codes provide additional common geographic referencing tools. The SITE_TYPE field
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SURVEY_ID (AK,PK,FK,U) |
Primary_Id Key- (Station+Sample_Date+Sample_Time+ Source+Project) |
Number (Long) | 4 |
|
SOURCE (PK,NN,FK) | Data Generating Agency- Code identifying data generator (i.e. VIMS) | Text | 6 |
|
PROJECT (PK,NN,FK) | State Monitoring Project- Code identifying State Monitoring Project (i.e. VADEQ) | Text | 10 |
|
STATION (PK,NN) | Sampling Station- Sampling station identifier (i.e. POK0012) |
Text | 15 |
| SAMPLE_DATE (PK,NN) |
Sampling Date- Date of sample collection (YYYYMMDD) |
Date/ Time | 8 |
| SAMPLE_TIME (PK,NN) |
SAMPLING TIME- Sample Collection Time (24HH:MM) | Date/ Time |
8 |
| LATITUDE (NN) |
Station Latitude- Station Latitude in decimal degrees (i.e. 36.0000) | Number (Single) |
4 |
| LONGITUDE (NN) |
Station Longitude- Station Latitude in decimal degrees (i.e. —72.0000) |
Number (Single) | 4 |
|
LL_DATUM (FK,NN) | Geographic Datum Code- Associated with Latitude and Longitude Values (i.e. NAD27 or NAD83) |
Text |
5 |
| FIPS (FK,NN) | FIPS Code- Federal Information Processing System Code. ( i.e. 00000) |
Text |
5 |
| HUC_8 (FK,NN) |
8 Digit USGS Hydrologic Unit Code (i.e. 08080808) | Text | 8 |
|
TOTAL_DEPTH | Total Station Depth- Total Station Depth in Meters (i.e. 99.99) |
Number (Single) | 4 |
|
SITETYPE (FK,NN) | Sampling Site Type- Station Sampling Site Type Code (i.e. F-Fixed, R-Random) | Text | 3 |
|
PROGRAM (FK,NN) | State Monitoring Program- Code identifying State Monitoring Program ( i.e. PHYTO) | Text | 20 |
|
R_DATE (NN) | Data Version Date- Date denoting when data records were entered in to database (yyyymmdd) | Date/ Time |
8 |
Notes:
SURVEY_ID: The actual primary key for this table is a composite key base on the following fields: SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME. An Auto-Key number is generated for each unique combination of these fields.
SAMPLE_TIME: Sampling events where sample collection time is missing, SAMPLE_TIME has been set to 00:00 (Mid-Night).
CHEMICAL_ANALYSIS
GENERAL: This table stores information relating to measurements of water quality and general environmental parameters. Typical parameters in this table would include nitrogen, ammonia, phosphorous, salinity, water temperature, Secci depth and air temperature. Currently this table assumes that all measurements are associated with a discrete sampling depth, minor modifications would need to be made for composite or non specific depth samples. If no chemical analysis was performed on a sample, there should be no records present for that sample in this table. The tables are linked or related by their five common fields (i.e. SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME).
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SURVEY_ID (PK,NN,FK) | Primary Id Key- (Station, Date, Time, Source, Program) |
Number (Long) | 4 |
| SAMPLE_TYPE (PK,NN,FK) |
Code identifying type of sample collected (i.e. D= discrete, C= composite,) |
Text | 7 |
| SAMPLE_MEDIA (PK,NN,FK) |
Code identifying media from which sample was taken (i.e. W= water, S= sediment, A= air) |
Text | 2 |
| SAMPLE_NUMBER (PK,NN) |
Sample number (i.e. 1=first of 3 samples) | Number (Single) |
4 |
| SAMPLE_ID (PK,NN,FK) |
Code identifying the sample description (i.e. FS- field split sample) |
Text | 7 |
| SAMPLE_DEPTH (PK,NN) |
Depth at which the sample was collected (i.e. 99.99) | Number (Single) |
4 |
| PARAMETER (PK,NN,FK) |
Code identifying parameter name (i.e. DO, SALINITY) | Text |
15 |
| VALUE (NN) | Parameter value (i.e. 99.99) | Number (Single) | 4 |
|
UNITS (FK,NN) | Abbreviation for units of parameter value (i.e. PPT) |
Text | 15 |
| QUALIFIER (FK,NN) |
Parameter Detection Limit qualifier code (i.e. > = less than) | Text | 5 |
| METHOD (FK,NN) |
Method code identifying field/laboratory test procedure (i.e. 101) |
Text | 8 |
| PROBLEM_CODE (FK,NN) |
Problem code associated with parameter value (i.e. QQ) |
Text |
2 |
| LABORATORY (FK,NN) |
Code designating laboratory or Source of sample analysis (i.e. VIMS) |
Text | 10 |
Notes:
SURVEY_ID: The actual primary key for this table is a composite key base on the following fields: SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME. An Auto-Key number is generated for each unique combination of these fields.
FISH_EVENT
GENERAL: This table stores information relating to sampling events where fish data is collected. Every fish collection event must have a record in this table in addition to a record in the Survey_Table. If a sampling event occurred but no fish were collected, there should be no records present for that sample in this table. It may appear that geographic locational information is being repeated. However, in order to accommodate trawl survey type sampling, this was necessary. The tables are linked or related by their five common fields (i.e. SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME).
| FIELD |
DESCRIPTION | TYPE | LENGTH |
| SURVEY_ID (PK,NN,FK) | Primary Id Key-(Station, Date, Time, Source, Program) | Number (Long) |
4 |
|
SAMPLE_NUMBER (PK,NN,FK) | Sample Number (i.e. 1=first of 3 Hauls or tows) | Number (Single) | 4 |
|
GMETHOD (FK,NN) | Sampling Gear Code (i.e. TRWL- Otter Trawl) |
Text | 3 |
| DURATION (NN) |
Duration of Sampling Event (in minutes) (30 ) | Number (Single) |
8 |
| TOW_DISTANCE | Distance of Sampling Tow or Haul (in meters) (i.e. 200) | Number (Single) |
4 |
|
COUNT_TYPE (FK,NN) | Code Designating Type of Count (i.e. All species, Blue Crab Only) | Text | 3 |
|
LATITUDE_START (NN) | Starting Latitude of Haul or Tow (i.e. 36.0000) |
Number (Double) | 8 |
|
LONGITUDE_START (NN) | Starting Longitude of Haul or Tow (i.e. —72.0000) | Number (Double) | 8 |
|
LATITUDE_END | Ending Latitude of Haul or Tow (Optional) (i.e. 36.0010) |
Number (Double) | 8 |
|
LONGITUDE_END | Ending Longitude of Haul or Tow (Optional) (i.e. -72.0000) |
Number (Double) | 8 |
|
LL_DATUM (FK,NN) | Geographic Datum Code Associated with Latitude and Longitude Values (i.e. NAD27 or NAD83) | Text | 50 |
|
TOW_DIRECTION _STREAM | Direction of Tow or Haul Relative to Stream Direction (i.e. Upstream or Downstream) | Text | 10 |
|
TOW_DIRECTION _CURRENT | Direction of Tow or Haul Relative to Current Direction (i.e. WITH or AGAINST) | Text | 10 |
Notes:
SURVEY_ID: The actual primary key for this table is a composite key base on the following fields: SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME. An Auto-Key number is generated for each unique combination of these fields.
FISH_INDIV
GENERAL: This table stores information relating to measurements made on individual fish caught during a sampling event. Sample parameters in this table would include individual fish lengths, weights, and number of abnormalities. This database uses the Interagency Taxonomic Identification System (ITIS) Taxon Serial Numbers (TSN) for species identification within the database. For species with no TSN values temporary TSN’s will need to be generated until a species can be submitted to ITIS for recognition. If no taxonomic analysis was performed on a sample, there should be no records present for that sample in this table. This table was designed for general fisheries survey use, due to the nature of the Pfiesteria Monitoring for fish the table may require modifications or the addition of another child table. The LAB field is meant to identify who performed analysis for selected parameters by groups other than the field collector. The tables are linked or related by their five common fields (i.e. SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME).
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SURVEY_ID (PK,NN,FK) |
Primary Id Key-(Station, Date, Time, Source, Program) | Number (Long) |
4 |
| SAMPLE_NUMBER (PK,NN,FK) |
Sample number (e.g. 1=first of 3 Hauls or tows) | Number (Single) |
4 |
| TSN (PK,NN,FK) |
ITIS Taxon Serial Number (i.e. 0000000) | Text | 7 |
|
SPECNUM (PK,NN,FK) | Sample Specimen Number Individual fish tracking number (i.e. 6) | Number (Double) |
8 |
|
PARAMETER (PK,NN,FK) | Code Identifying Parameter Name (i.e. Fork Length) | Text | 15 |
|
VALUE |
Parameter Value (i.e. 222) | Number (Double) |
8 |
| UNITS (NN) | Parameter Reporting Units (i.e. Millimeters) | Text | 8 |
|
METHOD (FK,NN) | Method Code- Method code identifying field/laboratory analysis procedure (i.e. FM-Field Measurement) | Text |
8 |
|
LABORATORY (FK,NN) | Code designating laboratory or Source of sample analysis (i.e. NR= Not Required) | Text |
10 |
Notes:
SURVEY_ID: The actual primary key for this table is a composite key base on the following fields: SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME. An Auto-Key number is generated for each unique combination of these fields.
TAXONOMIC_IDS
GENERAL: This table stores information relating to measurements of biological parameters such as phytoplankton and zooplankton organism counts. This database uses the Interagency Taxonomic Identification System (ITIS) Taxon Serial Numbers (TSN) for species identification within the database. For species with no TSN values temporary TSN’s will need to be generated until a species can be submitted to ITIS for recognition. With the addition of a media field sediment analysis parameters could also be measured. Currently this table assumes that all measurements are associated with a discrete sampling depth, minor modifications would need to be made for composite depth samples. The LAB field is meant to identify who performed analysis for selected parameters by groups other than the field collector. If no species counts performed on a sample, there should be no records present for that sample in this table. The tables are linked or related by their five common fields (i.e. SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME).
|
FIELD | DESCRIPTION | TYPE |
LENGTH |
| SURVEY_ID (PK,NN,FK) |
Primary_Id Key- (Station+Sample_Date+Sample_Time+ Source+Project) |
Number (Long) | 4 |
|
DATA_TYPE (PK,NN,FK) | Sample Type Code- Denotes type of biological sample collected (i.e. PH= Phytoplankton) | Text |
2 |
|
SAMPLE_DEPTH (PK,NN) | Sampling Depth- Denotes depth at which biological sample collected in meters (i.e. 0.5) | Number (Long) |
4 |
| SAMPLE_NUMBER (PK,NN) |
Sample Number- Number of sample collected at Station or replicate number (i.e. 1) |
Number (Single) | 4 |
|
PARAMETER (PK,NN,FK) | Parameter- Name identifying parameter (i.e. Count ) |
Text | 15 |
| TSN (PK,NN,FK) |
ITIS Taxon Serial Number- ITIS Serial Number for Species Identification (i.e. 0000000) | Text | 7 |
|
SIZE/LIFE_STAGE (PK,NN) | Species Size Fraction or Life Stage- Additional species identifier (i.e. larval or < 100 microns) | Text |
30 |
| VALUE | Parameter Value (i.e. 99999.99) |
Number (Double) | 8 |
|
UNITS |
Reporting Units Of Parameter (i.e. Number per Liter) | Text |
15 |
| METHOD (FK,NN) |
Method Code- Method code identifying field/laboratory analysis procedure (i.e. PH101) |
Text | 8 |
| SER_NUM |
Source Sample Serial Number Any internal source sample tracking number | Text |
12 |
| LABORATORY (FK,NN) |
Code designating laboratory or Source of sample analysis (i.e. NR= Not Required) |
Text | 10 |
SURVEY_ID: The actual primary key for this table is a composite key base on the following fields: SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME. An Auto-Key number is generated for each unique combination of these fields.
WEATHER_HABITAT
GENERAL: This table stores information relating to Habitat, Weather and other environmental conditions characterized by ranking or coding systems. These are also typically measurements of ambient conditions. Parameters in this table might include wind speed, wind direction, bottom type, percent SAV coverage. Currently this table assumes that all measurements are associated with either surface or bottom conditions apparent from the parameter. If no observations of this nature are made, there should be no records present for that sample in this table. The tables are linked or related by their five common fields (i.e. SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME).
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SURVEY_ID (PK,NN,FK) |
PRIMARY_ID KEY- (STATION+SAMPLE_DATE+SAMPLE_TIME+ SOURCE+PROJECT) |
Number (Long) | 4 |
|
SAMPLE_NUMBER (PK,NN,FK) | Sample Number- Number of sample collected at Station or replicate number (i.e. 1) | Number (Single) |
4 |
| PARAMETER (PK,NN,FK) |
Parameter- Name identifying parameter (i.e. ATEMP) |
Text |
15 |
| VALUE (FK,NN,FK) |
Parameter Value (i.e. 10) | Text | 12 |
Notes:
SURVEY_ID: The actual primary key for this table is a composite key base on the following fields: SOURCE, PROJECT, STATION, SAMPLE_DATE, and SAMPLE_TIME. An Auto-Key number is generated for each unique combination of these fields.
II. PRINCIPAL LOOK-UP TABLES
The primary tables also contain many fields containing codes that are described or defined in detail in related lookup tables. By creating one-to-many relationships between lookup tables and the primary data tables and enforcing referential integrity, data managers are restricted to entering only valid lookup table values into the primary data tables. Again, this provides an automatic layer of quality assurance that will improve the utility of the database for all users.
BIO_METHODS<
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| METHOD (PK,PF,NN) |
Analytical Method Code |
Text |
8 |
|
DATA_TYPE (PK,PF,NN) | Data Type Code | Text | 2 |
| CODE (NN) |
Analytical Method Code Number |
Text | 8 |
|
TITLE (NN) | Method Title or Other Identifier | Text | 50 |
| DESCRIPTION (NN) |
Method description |
Memo |
- |
|
DETAILS |
Detailed Method Description | Memo | - |
General: This table stores information related exclusively to METHOD codes in the TAXONOMIC_ID TABLE and FISH_INDIV. This table contains descriptions of the field and laboratory methods for parameter determination. The METHOD code is used to define field collection and laboratory analysis protocol.
BIO_PARAMETERS
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| PARAMETER (PK,PF,NN) |
Biological Parameter Codes | Text | 10 |
|
DESCRIPTION (NN) | Biological Parameter Descriptions/Definitions |
Text | 60 |
| UNITS (NN) |
Biological Parameter Reporting Units | Text |
15 |
General: This table stores information related exclusively to PARAMETER codes in codes in the TAXONOMIC_ID TABLE and FISH_INDIV. This table contains information to parameter names and standard reporting units. The following list of parameter represent those parameters that are either directly measured in the field or analyzed in the laboratory. Additional codes may be added as needed. Sample PARAMETER, DESCRIPTION and UNITS designations are as follows:
PARAMETER DESCRIPTION AND UNITS
ASH_FRWT ASH FREE DRY WEIGHT (MG/M**3)
ASH_WT TOTAL ASH WEIGHT (MG/M**3)
ASHFREWT ASH FREE DRY WEIGHT (G/SAMPLE)
ASHWT TOTAL ASH WEIGHT (G/SAMPLE)
BIOVOLUME BIOVOLUME (ML/SAMPLE)
COUNT NUMBER PER UNIT MEASURE
DRY_WT TOTAL DRY WEIGHT (MG/M**3)
DRYWT TOTAL DRY WEIGHT (G/SAMPLE)
SET_VOL SETTLED VOLUME ZOOPLANKTON AND DETRITUS (ML/M**3)
SET_VOLZ SETTLED VOLUME OF ZOOPLANKTON (ML/M**3)
SETVOL SETTLED VOLUME ZOOPLANKTON AND DETRITUS (ML/SAMPLE)
SETVOLZ SETTLED VOLUME OF ZOOPLANKTON (ML/SAMPLE)
CHEMICAL_METHODS
| FIELD |
DESCRIPTION | TYPE | LENGTH |
| PARAMETER (PK,PF,NN) | Code identifying parameter name | Text | 10 |
|
METHOD (PK,PF,NN) | Method code identifying field/laboratory test procedure |
Text | 5 |
| DESCRIPTION (NN) |
Analytical Method Description for Parameter | Memo |
- |
|
REFERENCE1 | Literature Reference for Method |
Memo |
- |
| REFERENCE2 | Literature Reference for Method |
Memo | - |
|
REFERENCE3 |
Literature Reference for Method | Memo |
- |
General: This table stores information related exclusively to PARAMETER codes in codes in the CHEMICAL_ANALYSIS TABLE. The METHOD code is used to define the field or lab procedure used to obtain the parameter value. Protocol for naming parameters could be based on EPA Storet, NOAA NODC or other standard naming conventions. Users will be able to add parameters themselves as needed/appropriate.
CHEMICAL_PARAMETERS
|
FIELD | DESCRIPTION | TYPE | LENGTH |
| PARAMETER (PK,PF,NN) | Code Identifying Parameter Name | Text | 8 |
|
STORET_CODE | Code Identifying EPA Storet Parameter Name |
Text |
5 |
| DESCRIPTION (NN) |
Detailed Description of Parameter | Text | 100 |
|
UNITS |
Standard Parameter Reporting Units | Text |
15 |
General: This table stores information related exclusively to PARAMETER codes in codes in the CHEMICAL_ANALYSIS TABLE. Calculated parameters will not be included in the database. Users will be able to calculate such parameters themselves or they will be provided through the user interface to the database. The following list of parameters represents a potential list of parameters that may be either directly measured in the field or analyzed in the laboratory.
Physical parameters
COND CONDUCTIVITY CORRECTED FOR TEMPERATURE (25 DEG C) AND SALINITY UMHOS/CM
DO DISSOLVED OXYGEN CORRECTED FOR TEMPERATURE (25 DEG C) AND SALINITY MG/L
FLOW_AVG STREAM FLOW, MEAN DAILY CFS
FLOW_INS STREAM FLOW, INSTANTANEOUS CFS
PH PH CORRECTED FOR TEMPERATURE (25 DEG C) SU
SALINITY SALINITY MG/L
SECCHI SECCHI DEPTH M
WTEMP WATER TEMPERATURE DEG C
Laboratory parameters
BIOSI BIOGENIC SILICA MG/L
BOD20F 20-DAY BIOCHEMICAL OXYGEN DEMAND (FILTERED SAMPLE) MG/L
BOD20W 20-DAY BIOCHEMICAL OXYGEN DEMAND (WHOLE SAMPLE) MG/L
BOD5F 5-DAY BIOCHEMICAL OXYGEN DEMAND (FILTERED SAMPLE) MG/L
BOD5W WHOLE 5-DAY BIOCHEMICAL OXYGEN DEMAND MG/L
CHL_A TRICHROMATIC CHLOROPHYLL-A UG/L
CHL_B TRICHROMATIC CHLOROPHYLL-B UG/L
CHL_C TRICHROMATIC CHLOROPHYLL-C UG/L
CHLA ACTIVE CHLOROPHYLL-A UG/L
CHLF FLOUROMETRIC CHLOROPHYLL UG/L
COD CHEMICAL OXYGEN DEMAND MG/L
DOC DISSOLVED ORGANIC CARBON MG/L
DON DISSOLVED ORGANIC NITROGEN MG/L
DOP DISSOLVED ORGANIC PHOSPHORUS MG/L
FCOLI FECAL COLIFORMS MPN/100 ML
FSS FIXED SUSPENDED SOLIDS MG/L
HARDNESS HARDNESS AS CACO3 MG/L
IBOD5F INHIBITED 5-DAY BIOCHEMICAL OXYGEN DEMAND (FILTERED SAMPLE) MG/L
IBOD5W INHIBITED 5-DAY BIOCHEMICAL OXYGEN DEMAND (WHOLE SAMPLE) MG/L
NH4F AMMONIUM NITROGEN AS N (FILTERED SAMPLE) MG/L
NH4W AMMONIUM NITROGEN AS N (WHOLE SAMPLE) MG/L
NO23F NITRITE+NITRATE NITROGEN AS N (FILTERED SAMPLE) MG/L
NO23W NITRITE+NITRATE NITROGEN AS N (WHOLE SAMPLE) MG/L
NO2F NITRITE NITROGEN AS N (FILTERED SAMPLE) MG/L
NO2W NITRITE NITROGEN AS N (WHOLE SAMPLE) MG/L
NO3F NITRATE NITROGEN AS N (FILTERED SAMPLE) MG/L
NO3W NITRATE NITROGEN AS N (WHOLE SAMPLE) MG/L
ORP OXIDATION REDUCTION POTENTIAL MG/L
PC PARTICULATE CARBON MG/L
PHEO PHEOPHYTIN UG/L
PIP PARTICULATE INORGANIC PHOSPHORUS MG/L
PN PARTICULATE NITROGEN MG/L
PO4F ORTHOPHOSPHATE PHOSPHORUS AS P (FILTERED SAMPLE) MG/L
PO4W ORTHOPHOSPHATE PHOSPHORUS AS P (WHOLE SAMPLE) MG/L
PP PARTICULATE PHOSPHORUS MG/L
SIF SILICA AS SI (FILTERED SAMPLE) MG/L
SIW SILICA AS SI (WHOLE SAMPLE) MG/L
SO3 TOTAL SULFITE AS SO3 MG/L
SO4 TOTAL SULFATE AS SO4 MG/L
SOD SEDIMENT OXYGEN DEMAND
TALK TOTAL ALKALINITY AS CACO3 MG/L
TCOLI TOTAL COLIFORMS MPN/100 ML
TDN TOTAL DISSOLVED NITROGEN MG/L
TDP TOTAL DISSOLVED PHOSPHORUS MG/L
TIN TOTAL INORGANIC NITROGEN MG/L
TKNF TOTAL KJELDAHL NITROGEN (FILTERED SAMPLE) MG/L
TKNW TOTAL KJELDAHL NITROGEN (WHOLE SAMPLE) MG/L
TOC TOTAL ORGANIC CARBON MG/L
TON TOTAL ORGANIC NITROGEN MG/L
TOP TOTAL ORGANIC PHOSPHORUS MG/L
TP TOTAL PHOSPHORUS MG/L
TSS TOTAL SUSPENDED SOLIDS MG/L
TSSED TOTAL SUSPENDED SEDIMENT MG/L
TURB_FTU TURBIDTY, TURBIDIMETER FTU
TURB_JTU TURBIDITY, JACKSON CANDLE METHOD (FORWARD SCATTER) JTU
TURB_NTU TURBIDITY, NEPHELOMETRIC METHOD NTU
VSS VOLATILE SUSPENDED SOLIDS MG/L
Metals
AG TOTAL SILVER UG/L
AS TOTAL ARSENIC UG/L
CD TOTAL CADMIUM UG/L
CR TOTAL CHROMIUM UG/L
CU TOTAL COPPER UG/L
FE TOTAL IRON UG/L
MN TOTAL MANGANESE UG/L
NI TOTAL NICKEL UG/L
PB TOTAL LEAD UG/L
SI TOTAL SILICON AS SI UG/L
SN TOTAL STRONTIUM MG/L
ZN TOTAL ZINC UG/L
DATA_TYPE
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| DATA_TYPE (PK,PF,NN) |
Biological Data Type Code | Text | 2 |
|
DESCRIPTION (NN) | Data Type Code Description |
Text |
50 |
GENERAL: This table stores information related exclusively to DATA_TYPE codes in the TAXONIMIC_ID TABLE. This table contains information to type of sample collected during an event. The following list of data types represents those either directly measured in the field or analyzed in the laboratory. Additional codes may be added as needed. Potential DATA_TYPE and DESCRIPTION designations are as follows:
DATA_TYPE DESCRIPTION
BE BENTHIC
ME MEROPLANKTOM
MI MICROZOOPLANKTON
MZ MESOZOOPLANKTON
PH PHYTOPLANKTON
PP PICOPLANKTON
FIPS
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| FIPS (PK,PF,NN) |
FIPS CODE-Federal Information Processing System Code |
Text |
5 |
| STATE (NN) | State Designation-Two Letter State Postal Abbreviation | Text | 2 |
|
NAME (NN) | County Designation-County Name |
Text |
30 |
GENERAL: This table stores information related exclusively to FIPS codes in the SURVEY TABLE. The (FIPS) Federal Information Processing System codes identifying state and county type of field samples taken at given site.
HUCS_8
|
FIELD | DESCRIPTION | TYPE | LENGTH |
| HUC_8 (PK,PF,NN) |
8 DIGIT HUC CODE 8-digit USGS hydrologic unit code |
Text |
8 |
| REGION (NN) |
2 DIGIT HUC CODE Region associated with the first two digits of HUC8 |
Text | 2 |
| SUBREGION (NN) |
4 DIGIT HUC CODE Sub-region associated with the first four digits of HUC8 |
Text | 4 |
| ACCOUNTING_UNIT (NN) |
6 DIGIT HUC CODE Accounting unit associated with the first six digits of HUC8 |
Text | 6 |
| REGION_ DESCRIPTION (NN) |
REGION DESIGNATION Detailed Description of Region described by first two digits of HUC code |
Text | 15 |
| SUBREGION_ DESCRIPTION (NN) |
SUBREGION DESIGNATION Detailed Description of Region described by first four digits of HUC code | Text | 20 |
|
ACCOUNTING_UNIT_DESCRIPTION (NN) | ACCOUNTING DESIGNATION Detailed Description of Region described by first six digits of HUC code |
Text |
30 |
| CATALOGING_UNIT_ DESCRIPTION (NN) |
CATALOGING DESIGNATION Detailed Description of Region described by first eight digits of HUC code | Text | 35 |
GENERAL: The HUCS8 TABLE contains 8-digit USGS hydrologic unit codes and descriptions related to the HUC8 codes in the SURVEY TABLE. The HUC8 code is the 8-digit USGS hydrologic unit code in which the station is located. The list that follows contains only the HUC and the associated cataloging unit description. Additional lookup tables related to this table may or may not be included in the final database design. These tables contain specific information related to the REGION, SUBREGION, ACCOUNTING_UNIT, and CATALOGING_UNIT fields (i.e. detailed description, states covered, and area in square miles).
LABS AND BIO_LABORATORY
|
FIELD | DESCRIPTION | TYPE | LENGTH |
| LAB (PK,PF,NN) | Code identifying the laboratory at which the sample was analyzed | Text | 15 |
|
DESCRIPTION (NN) | Full description of lab code |
Text |
100 |
| DIRECTOR (NN) |
Name of contact for Laboratory | Text | 50 |
|
ADDRESS (NN) | Physical Street Address of Laboratory |
Text |
100 |
| CITY (NN) | City Where Analysis Laboratory is Located | Text | 50 |
|
STATE (NN) | State Where Analysis Laboratory is Located |
Text |
2 |
| ZIP (NN) | Zip or Postal Code for Analysis Laboratory | Text | 9 |
| PHONE (NN) |
Phone Number for Laboratory | Text | 10 |
General: These table stores information related exclusively to LAB codes in the CHEMICAL_ANALYSIS TABLE, TAXONOMIC_IDS OR FISH_INDIV tables. This field was found to be necessary because samples collected during a single sampling event are frequently processed by multiple laboratories. It also stores point of contact information about each Laboratory.
LL_DATUM
| FIELD |
DESCRIPTION | TYPE | LENGTH |
| LL_DATUM (PK,PF,NN) | Geographic Datum Code Associated with Latitude and Longitude Values (NAD27 or NAD83) |
Text |
5 |
| DESCRIPTION (NN) |
Description of Datum Code Values (NAD27 or NAD83) | Text |
50 |
GENERAL- the LL_DATUM TABLE contains latitude/longitude datum codes and descriptions. The LL_DATUM codes are found in the SURVEY TABLE and FISH_EVENT TABLE. The LL_DATUM code defines the datum under which the latitude and longitude measurements for a particular station were calculated. The typical LL_DATUM and DESCRIPTIONS are as follows:
NAD27 1927 North American DATUM
NAD83 1983 North American DATUM
UNID UNKNOWN DATUM
MEDIA_TYPE
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SAMPLE_MEDIA (PK) |
SAMPLE COLLECTION TYPE CODE | Text |
4 |
|
DESCRIPTION (NN) | CODE DEFINITION |
Text | 50 |
GENERAL: This table stores information relating to the type of field samples taken at given site. This code is used in all the primary data tables. Additional codes may be added as needed. Proposed SAMPLE_MEDIA designations are as follows:
SAMPLE_MEDIA DESCRIPTION
W Water
S Sediment
A Air
PROBLEMS
|
FIELD | DESCRIPTION | TYPE | LENGTH |
| PROBLEM (PK,PF,NN) | Analysis problem code |
Text | 2 |
| DESCRIPTION (NN) |
Description/definition of PROBLEM | Text |
100 |
General: The PROBLEM codes are found in the CHEMICAL_ANALYSIS TABLE. The PROBLEM code is used to define any lab analysis problems that were encountered during measurement of the parameter value. The potential codes for PROBLEMS and DESCRIPTIONS are as follows:
A LABORATORY ACCIDENT
B CHEMICAL MATRIX INTERFERENCE
C INSTRUMENT FAILURE
D INSUFFICIENT SAMPLE
E SAMPLE RECEIVED AFTER HOLDING TIME
H ANALYSIS RUN BY ANOTHER LAB
J INCORRECT SAMPLE FRACTION FOR ANALYSIS
R SAMPLE CONTAMINATED
U INCONSISTENT RELATIONSHIP BETWEEN VARIABLES
V SAMPLE RESULTS REJECTED DUE TO QC CRITERIA
X SAMPLE NOT PRESERVED PROPERLY
Z ANALYZED BY METHOD OF STANDARD ADDITIONS
BB TORN FILTER PAD
DD SAMPLE SIZE NOT REPORTED
FF POOR REPLICATION BETWEEN PADS, MEAN REPORTED
GG SAMPLE ANALYZED AFTER HOLDING TIME
HH SAMPLE NOT COLLECTED
JJ AMOUNT FILTERED NOT RECORDED
MM OVER 20% OF SAMPLE ADHERED TO POUCH AND OUTSIDE OF PAD
NN PARTICULATES FOUND IN FILTERED SAMPLE
QQ VALUE EXCEEDS A THEORETICAL EQUIVALENT YET WITHIN ANALYTICAL PRECISION
SS SAMPLE REJECTED, HIGH SUSPENDED SEDIMENT CONCENTRATION
VV STATION WAS NOT SAMPLED DUE TO BAD FIELD CONDITIONS
WW HIGH OPTICAL DENSITY (750 NM); ACTUAL VALUE RECORDED
PROGRAMS
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| PROGRAM (PK,PF,NN) |
Agency monitoring program code |
Text |
15 |
| DESCRIPTION (NN) |
Description/definition of PROGRAM | Text | 100 |
|
DIRECTOR (NN) | Program manger |
Text |
50 |
| ADDRESS (NN) | Physical Street Address of Program Contact | Text | 100 |
| CITY (NN) |
City Where Program Contact is Located | Text |
50 |
| STATE (NN) | State Where Contact Program is Located |
Text | 2 |
| ZIP (NN) |
Zip or Postal Code | Text | 9 |
|
PHONE (NN) | Phone Number for Contact | Text |
10 |
General: The PROGRAM codes are found in the SURVEY TABLE. The PROGRAM code serves an identifier of data originator in a database that may hold data from multiple programs from multiple states. It also stores point of contact information about each monitoring program.
PROJECTS
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| PROJECT (PK) | Agency monitoring project code |
Text | 15 |
| DESCRIPTION (NN) | Description/definition of PROJECT | Text | 100 |
General: The PROJECT codes are found in the SURVEY TABLE. The PROGRAM code serves an identifier of the individual monitoring project providing originator in a database that may hold data from multiple programs from multiple states.
QUALIFIERS
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| QUALIFIER (PK) | Parameter value qualifier code | Text | 5 |
| DESCRIPTION (NN) | Description/definition of QUALIFIER | Text | 50 |
General: This table stores information related exclusively to PARAMETER codes in the CHEMICAL_ANALYSIS table. The QUALIFIER code is used to describe the parameter value as less than or greater than the method detection limit or as a calculated value which has been calculated using a method detection limit. Examples of QUALIFIERS and DESCRIPTION designations are as follows:
QUALIFIERS DESCRIPTION
<0 LESS THAN THE LOWER METHOD DETECTION LIMIT (MDL)
>0 GREATER THAN THE UPPER METHOD DETECTION LIMIT (MDL)
# TRACE (LESS THAN AN UNKNOWN DETECTABLE VALUE)
J ESTIMATED VALUE
N NOT DETECTED
NA NOT RECORDED/NOT APPLICABLE/PARAMETER VALUE ACCEPTABLE
SAMPLE_IDS
| FIELD |
DESCRIPTION | TYPE | LENGTH |
| SAMPLE_ID (PK) |
Sample ID code | Text |
7 |
|
DESCRIPTION (NN) |
Description/definition of SAMPLE_ID | Text |
100 |
General: This table stores information related exclusively to SAMPLE_ID codes in the CHEMICAL_ANALYSIS table. The SAMPLE_ID code is used to further define the sample collected so that the user will be better able to manipulate the data correctly. Examples of codes and descriptions are as follows:
LS LABORATORY SAMPLE
LSS LABORATORY SPLIT SAMPLE
FS FIELD SAMPLE
FSS FIELD SPLIT SAMPLE
FS_AVG VALUE REPRESENTS THE AVERAGE OF TWO FIELD SPLIT SAMPLES
SAMPLE_TYPES
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SAMPLE_TYPE (PK) |
SAMPLE COLLECTION TYPE CODE | Text |
4 |
|
DESCRIPTION (NN) | CODE DEFINITION |
Text | 50 |
GENERAL: This table stores information relating to the type of field samples taken at given site. This code is used in all the primary data tables. Additional codes may be added as needed. Potential SAMPLE_TYPE designations are as follows:
C = Composite Sample (May be composite of multiple samples from a site or multiple depths)
D = Discrete (GRAB) Sample (Single sample from site or depth)
ISM_H = In-Situ Measurement, Collected as part of a Horizontal Transect
ISM_V = In-Situ Measurement, Collected as part of a Vertical Profile
SAMPLING_GEAR_TABLE
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| GMETHOD (PK,NN,FK) |
Sampling Gear Code | Text | 3 |
|
DESCRIPTION (NN) | Sampling Gear Description |
Text |
40 |
GENERAL: This table stores information relating to the type of gear used to collect samples for all analysis. This table stores the identification codes for sampling gear used primary in the FISH_EVENT TABLE. The primary key in this table is defined by G_METHOD.
SITETYPE
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SITETYPE (PK,NN,FK) |
Sample Site Type Code | Text | 3 |
|
DEFINITION (NN) | Sampling Site Type Code Description |
Text |
10 |
General: This table stores information relating to the criteria for sampling site selection. This code is used in the SURVEY_TABLE. Typical SITE_TYPE designations are as follows:
F Fixed Location Sampling Site
R Random selected Sampling Site within a habitat or other defined strata.
K Fish Kill Site
SOURCES
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| SOURCE (PK) |
Data Source Code | Text | 15 |
|
DESCRIPTION (NN) | Description of Data Source Definition of SOURCE |
Text | 100 |
| COMMENTS |
Comments |
Text | 255 |
General: This table stores information relating to the actual generator of a sample. This code is used in the SURVEY_TABLE.
CBP_MSTR TABLE
| FIELD |
DESCRIPTION | TYPE |
LENGTH |
| TSN_NUM |
TAXON SERIAL NUMBER- ITIS Serial Number for Species Identification (defined as a numeric value) |
Number (Double) | 8 |
| TSN (PK,FK) |
TAXON SERIAL NUMBER- ITIS Serial Number for Species Identification (defined as a fixed 7 character value with leading zeros) | Text | 7 |
|
NODCCODE |
NATIONAL OCEANOGRAPHIC DATA CENTER TAXONOMIC CODES |
Text | 12 |
| SYN | SYNONYM FLAG- Chesapeake Bay Program flag denoting species with synonymous name |
Text |
1 |
| LATIN_NAME (NN) |
SPECIES LATIN NAME- Species Latin/Scientific Name | Text |
45 |
| LEVEL | PHYLOGENIC CLASSIFICATION- Denotes Phylogenic Level (phylum, class, order, etc) | Text | 6 |
|
COMMON_NAM | COMMON NAME- Species Common Name |
Text | 40 |
| R_DATE (NN) (yyyymmdd) |
DATA VERSION DATE- Date denoting when data records were entered in to database |
Date/ Time | 8 |
General: This table stores information in relating to the identification of species in the TAXONIMIC_ID TABLE and the FISH_INDIV TABLE. The list includes listings for all types of organisms including phytoplankton, zooplankton, fish and benthos. This database uses the Interagency Taxonomic Identification System (ITIS) Taxon Serial Numbers (TSN) for species identification within the database. For species with no TSN values temporary TSN's will need to be generated until a species can be submitted to ITIS for recognition.
TSN: Each species has been given its ITIS Taxonomic Serial Number (TSN). The ITIS (Interagency Taxonomic Information System) is a partnership of federal agencies working together to improve the organization of, and access to, standardized nomenclature. As part of this system a national, easily accessible database with reliable information on species names and their hierarchical classification has been established. The database is reviewed periodically to ensure high quality with valid classifications, revisions, and additions of newly described species. As part of this effort all Federal agencies have been asked to adopt the use of TSN code which assigns each recognized species a permanent number. The TSN allows a species to be tracked over time regardless of changes in name and taxonomic classification. TSN also provides a uniform key field for database development and species identification across multiple organizations. When used in conjunction with the NODC, the TSN overcomes the problem of numeric changes in the NODC code whenever species are reclassified.
NODCCODE: All species on the list have been assigned at least partial National Oceanographic Data Center (NODC) Taxon Codes (Version 8.0). The NODC Taxon Code is a hierarchical system of numerical codes used to represent the scientific names and phylogeny of organisms. The code links the Linnean system of biological nomenclature to a numerical schema that facilitates modern methods of computerized data storage and retrieval. An NODC code contains a maximum of 12 digits partitioned into 2-digit couplets. Each couplet represents one or more levels of the taxonomic hierarchy. For example,
Digit Represents
1-2 Phylum
3-4 Class and/or Order
5-6 Family
7-8 Genus
9-10 Species
11-12 Subspecies
One drawback of the NODC code is it changes over time to reflect current changes in taxonomic classifications. However, it provides data analysts with a very useful tool for sorting organisms into taxonomic groups.
WEATHER_HABITAT_LOOKUP_TABLE
| FIELD |
DESCRIPTION |
TYPE | LENGTH |
| PARAMETER |
Weather/Habitat Parameter Code |
Text |
15 |
|
VALUE |
Accepted Parameter Values | Text | 12 |
| DEFINITIONS | Weather/Habitat Parameter and value definitions |
Text |
50 |
General: This table stores information related exclusively to PARAMETER and VALUE codes in the WEATHER_HABITAT table. Examples of PARAMETERS AND VALUES designations are as follows:
PARAMETER VALUE DEFINITIONS
CLOUD_COVER 0 CLEAR (0-10%)
CLOUD_COVER 1 SCATTERED TO PARTLY CLOUDY (10-50%)
CLOUD_COVER 2 PARTLY TO BROKEN (50-90%)
CLOUD_COVER 3 OVERCAST (>90%)
CLOUD_COVER 4 FOGGY
CLOUD_COVER 5 HAZY
CLOUD_COVER 6 CLOUD (NO PERCENTAGE)
PRECIP_TYPE NOT RECORDED
PRECIP_TYPE 10 NONE
PRECIP_TYPE 11 DRIZZLE
PRECIP_TYPE 12 RAIN
PRECIP_TYPE 13 RAIN, HEAVY
PRECIP_TYPE 14 SQUALLY
PRECIP_TYPE 15 FROZEN PRECIPITATION
PRECIP_TYPE 16 RAIN, SNOW
WAVE_HEIGHT 0 0.0 TO <0.1M
WAVE_HEIGHT 1 0.1 TO <0.3M
WAVE_HEIGHT 2 0.3 TO <0.6M
WAVE_HEIGHT 3 0.6 TO <1.0M
WAVE_HEIGHT 4 1.0 TO <1.3M
WAVE_HEIGHT 5 > 1.3M
WIND_DIR E FROM THE EAST (90 DEGREES)
WIND_DIR ENE FROM THE EAST NORTHEAST (67.5 DEGREES)
WIND_DIR ESE FROM THE EAST SOUTHEAST (112.5 DEGREES)
WIND_DIR N FROM THE NORTH (0 DEGREES)
WIND_DIR NE FROM THE NORTH EAST (45 DEGREES)
WIND_DIR NNE FROM THE NORTH NORTHEAST (22.5 DEGREES)
WIND_DIR NNW FROM THE NORTH NORTHWEST (337.5 DEGREES)
WIND_DIR NW FROM THE NORTHWEST (315 DEGREES)
WIND_DIR S FROM THE SOUTH (180 DEGREES)
WIND_DIR SE FROM THE SOUTH EAST (135 DEGREES)
WIND_DIR SSE FROM THE SOUTH SOUTHEAST (157.5 DEGREES)
WIND_DIR SSW FROM THE SOUTH SOUTHWEST (202.5 DEGREES)
WIND_DIR SW FROM THE SOUTH WEST (225 DEGREES)
WIND_DIR W FROM THE WEST (270 DEGREES)
WIND_DIR WNW FROM THE WEST NORTHWEST (292.5 DEGREES)
WIND_DIR WSW FROM THE WEST SOUTHWEST (247.5 DEGREES)
WIND_SPEED 0 0 TO 1 KNOT
WIND_SPEED 1 >1 TO 10 KNOTS
WIND_SPEED 2 >10 TO 20 KNOTS
WIND_SPEED 3 >20 TO 30 KNOTS
WIND_SPEED 4 >30 TO 40 KNOTS
WIND_SPEED 5 >40 KNOTS
The following lookup tables are present in the database but are not linked to the main or lookup tables of the database. They can be used in queries to add additional fields exclusively to the FISH_INDIV and TAXONOMIC_ID tables. They include codes related to parameter names, sampling methods, and laboratory analysis of water quality samples.
PI_SPECIES_LIST TABLE
|
Field | Description | Type | Length |
|
SPEC_CODE (PK,FK,NN) |
SOURCE IN-HOUSE SPECIES CODE | Text | 14 |
| SOURCE (PK,FK,NN) |
DATA GENERATING AGENCY- Code identifying data generator | Text |
6 |
|
DATA_TYPE (PK,FK,NN) |
SAMPLE TYPE CODE- Denotes type of sample collected see DATA_TYPE TABLE for codes |
Text |
2 |
| SOURCE_LBL | SOURCE IN-HOUSE SPECIES LATIN NAME |
Text |
45 |
| LBL | FULL SPECIES LABEL- Latin Name Corrected to IT IS accepted spelling |
Text |
45 |
| TSN (PK,PK,NN) |
TAXON SERIAL NUMBER- ITIS Serial Number for Species Identification |
Text |
7 |
| R_DATE | VERSION DATE- Date denoting when data records were entered in to database | Date/ Time | 8 |
|
VOLUME |
BIOMASS CONVERSION VALUE Conversion factor to estimate biomass from a organism count |
Number (Double) | 8 |
| SIZE | SPECIES CELL SIZE DESCRIPTOR- Additional species identifier | Text | 30 |
|
LIFE_STAGE |
SPECIES LIFESTAGE- Additional species identifier | Text |
3 |
General: This table stores information related to the Source or data generators in house Species codes or SPEC_CODE. This database uses the Interagency Taxonomic Identification System (ITIS) Taxon Serial Numbers (TSN) for species identification within the database. Most data generators had developed and implemented internal species coding systems prior to the development of the ITIS standard. This table is provided as a conversion table from Source in-house species codes to ITIS TSN’s prior to loading data to either the TAXONOMIC_ID TABLE or the FISH_INDIV TABLE.
UNIFORM PFIESTERIA MONITORING DATABASE: VERSION 1.0 - MAJOR TABLES ONLY