Open-File Report 01-485

Environmental Database For Water-Quality Data 

For the Penobscot River, Maine: 

Design Documentation and User Guide

Prepared in cooperation with the 

Bureau of Indian Affairs, 

Penobscot Indian Nation, Department of Natural Resources, and

U.S. Environmental Protection Agency, Region 1

U.S. Department of the Interior

U.S. Geological Survey
Open-File Report 01-485

U.S. Department of the Interior

U.S. Geological Survey

Environmental Database For Water-Quality Data 

For the Penobscot River, Maine: 

Design Documentation and User Guide

Prepared in cooperation with the 

Bureau of Indian Affairs, 

Penobscot Indian Nation, Department of Natural Resources, and

U.S. Environmental Protection Agency, Region 1

By Sarah E. Giffen

Augusta, Maine

2002
U.S. DEPARTMENT OF THE INTERIOR

GALE A. NORTON, Secretary

U.S. GEOLOGICAL SURVEY

Charles G. Groat, Director

For additional information write to: Copies of this report can be purchased

from:

U.S. Geological Survey

Branch of Information Services

Box 25286, Federal Center

Denver, CO 80225

The use of firm, trade, and brand names in this report is for identification purposes 

only and does not constitute endorsement by the U.S. Geological Survey.

District Chief

U.S. Geological Survey

26 Ganneston Drive

Augusta, Maine 04330

http://me.water.usgs.gov
Contents iii

CONTENTS

Abstract ...................................................................................................................................................................... 1

Introduction ................................................................................................................................................................ 1

Acknowledgments...................................................................................................................................................... 1

Design Documentation............................................................................................................................................... 1

Database Model Principles................................................................................................................................ 2

Database Model Design Conventions ............................................................................................................... 2

Database Model Structure......................................................................................................................... 2

Naming Standards in the Model ............................................................................................................... 3

Diagramming the Model........................................................................................................................... 4

Examination of the PINE Database Model ....................................................................................................... 5

Site Subject Area of the Model................................................................................................................. 5

Sample Subject Area of the Model........................................................................................................... 6

Main Sample Area of the Model...................................................................................................... 6

Sediment Sample Area of the Model............................................................................................... 8

Tissue Sample Area of the Model.................................................................................................... 8

SPMD Sample Area of the Model................................................................................................... 9

Water Sample Area of the Model .................................................................................................... 9

Result Subject Area of the Model............................................................................................................. 9

Main Result Area of the Model........................................................................................................ 9

Detection Limit Area of the Model.................................................................................................. 11

Toxic Equivalent Factor Area of the Model.................................................................................... 11

Customizing / Extending the Database Model .................................................................................................. 11

User Guide ................................................................................................................................................................. 12

The Access Database and User Interface .......................................................................................................... 12

Structure of the Database .................................................................................................................................. 12

Setting up the Database ..................................................................................................................................... 17

Adding Data to the Database............................................................................................................................. 17

Add a New Site ......................................................................................................................................... 17

Add a New Sample ................................................................................................................................... 17

Add a New Result for a Sediment Sample ............................................................................................... 18

Add a New Result for a Tissue Sample .................................................................................................... 18

Add a New Result for an SPMD Sample .................................................................................................. 18

Add a New Result for a Water Sample..................................................................................................... 19
iv Environmental Database For Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

User Guide—Continued

Editing Data in the Access Database................................................................................................................. 19

Edit an Existing Site ................................................................................................................................. 19

Edit General Sample Information............................................................................................................. 19

Edit Specific Sediment Sample Information ............................................................................................ 20

Edit Specific Tissue Sample Information ................................................................................................. 20

Edit Specific SPMD Sample Information................................................................................................. 20

Edit Specific Water Sample Information.................................................................................................. 20

Edit a Result.............................................................................................................................................. 20

Adding / Editing Items in the Drop Down Lists ............................................................................................... 21

Add / Edit Agency in Drop Down List..................................................................................................... 21

Add / Edit Field Method in Drop Down List............................................................................................ 21

Add / Edit Weather Condition in Drop Down List................................................................................... 21

Add / Edit River Condition in Drop Down List........................................................................................ 21

Add / Edit Water Level in Drop Down List.............................................................................................. 22

Add / Edit Foam Ranking in Drop Down List.......................................................................................... 22

Add / Edit Foam Source in Drop Down List............................................................................................ 22

Add / Edit Lab in Drop Down List........................................................................................................... 22

Add / Edit Tissue Type in Drop Down List.............................................................................................. 22

Add / Edit Constituent in Drop Down List............................................................................................... 23

Add / Edit Constituent Class in Drop Down List..................................................................................... 23

Add / Edit Result Flag in Drop Down List............................................................................................... 23

Adding / Editing Sieve, Species, and Unit Choices .......................................................................................... 23

Defining Detection Limits/ Toxic Equivalent Factors/ Constituent Classification ........................................... 23

Deleting Data from the Access Database .......................................................................................................... 24

References Cited ........................................................................................................................................................ 25

Appendix 1. Data Dictionary ..................................................................................................................................... 27

Plate 1. Diagram showing structure of the PINE Database model ............................................................................ in pocket

Figures

1. - 3. Diagrams showing:

1. General view of the site subject area of the model.................................................................................. 5

 2. General view of the sample subject area of the model............................................................................ 7

 3. General view of the result subject area of the model. ............................................................................. 10

4. - 9. Copies of screens showing:

 4. View of the tables in the back-end of the database (Penobscot_Data.mdb)............................................ 12

 5. Form view of the table tdsFieldMethod in the back-end of the database................................................ 13

 6. Design view of the table tdsFieldMethod in the back-end of the database. ............................................ 13

 7. View of linked tables in the front-end of the database............................................................................ 14

 8. View of forms in the front-end of the database....................................................................................... 15

 9. View of the switchboard in the front-end of the database....................................................................... 15

10. Diagram of the database switchboard ............................................................................................................. 16
Abstract 1

Environmental Database for Water-Quality Data 

for the Penobscot River, Maine: 

Design Documentation and User Guide

by Sarah E. Giffen

ABSTRACT

An environmental database was developed 

to store water-quality data collected during the 

1999 U.S. Geological Survey investigation of the 

occurrence and distribution of dioxins, furans, and 

PCBs in the riverbed sediment and fish tissue in 

the Penobscot River in Maine. The database can be 

used to store a wide range of detailed information 

and to perform complex queries on the data it 

contains. The database also could be used to store 

data from other historical and any future environmental studies conducted on the Penobscot River 

and surrounding regions.

INTRODUCTION

In 1999, the U.S. Geological Survey (USGS), in 

cooperation with the Bureau of Indian Affairs (BIA), 

the Penobscot Indian Nation’s Department of Natural 

Resources (PINDNR), and the U.S. Environmental 

Protection Agency (USEPA) conducted a study to 

examine the occurrence and distribution of dioxins, 

furans, and polychlorinated biphenyls (PCBs) in fish 

tissues and sediments along a 50-mile stretch of the 

Penobscot River between Old Town and Grindstone, 

Maine. As part of this project, the USGS developed a 

database that is capable of storing the data from this 

study, as well as environmental data from other (past 

and future) studies conducted on the Penobscot River. 

The database, known as the Penobscot Indian 

Nation, Department of Natural Resources, Environmental (PINE) Database, is a Microsoft Access database created using the database modeling program 

ERwinTM version 3.5.2 (Platinum Technologies). The 

database is capable of storing various types of environ 

mental data and provides an efficient way to conduct 

queries on the data that reside in the database. This 

report describes the development of the PINE Database 

and serves as its user manual. The user guide section of 

this report aids the user in populating the database but 

does not describe the processes of conducting queries 

or generating reports. To use the database, users should 

have a properly installed version of Access 2000, or 

future versions of Access that are backward compatible, on their computer.

ACKNOWLEDGMENTS

The author gratefully acknowledges personnel 

who helped develop the Penobscot Indian Nation, 

Department of Natural Resources, Environmental 

Database, with special thanks to Steven Tessler of the 

USGS who created the original database model and 

was closely involved in all phases of the database 

design. A substantial portion of the text in the Design 

Documentation section of this report was provided by 

Steven Tessler via his Data Model and Relational 

Design for the New England Water-Use Data System 

report. Thanks also to Robert Dudley and Joel Dudley 

(USGS) for their extensive technical contributions and 

support.

DESIGN DOCUMENTATION 

The PINE Database is intended as a storage and 

retrieval device for water-quality data, including the 

description of places and organizations involved in 

environmental monitoring activities, details regarding 

the procedures used to collect samples, and sample 

results. The database is designed to store a variety of 

data in one structure, allowing the user to explore and 

synthesize environmental data efficiently while 
2 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

ensuring the integrity of the data. The PINE Database 

may serve as a common location for environmental 

data on the Penobscot River, creating the potential to 

combine data and allow greater insight into waterquality issues.

Although significant analytical capabilities are 

inherent in its design, the PINE Database is not an 

analytical tool. Users of the PINE Database may 

extract data in whatever combinations and formats are 

needed. Extracted data sets can subsequently be 

analyzed with statistical or modeling applications.

Database Model Principles

The PINE Database is an Access database that 

was generated from a database model. The PINE Database model was designed with the modeling tool 

ERwinTM version 3.5.2 (Platinum Technologies). 

Database modeling tools facilitate the rapid design and 

modification of a database, handling many of the 

important details that could affect the quality of the 

data structures. 

Designing a database model is a precursor to any 

correctly structured database. Database modeling is the 

process of analyzing and reducing a set of data to its 

separate components and establishing the nature and 

direction of relations between those components. This 

structure enforces a system of data integrity and 

provides easy access to all the information stored in the 

database. A good data model also recognizes change as 

a partner in data management and allows corrections 

and extensions to be made to the database without 

affecting the purity of data already residing in the database. In addition, a database model is used to create and 

maintain a data dictionary. The data dictionary is an 

index that catalogues all of the elements in the database, and thus provides a common language and reference for all users of the database. Database users 

should be able to examine the completed data model 

and its data dictionary and confirm that it contains all 

specific, categorical, and classificatory parameters 

needed to store, describe, validate, list, summarize, and 

aggregate environmental information (Tessler, in 

press). 

Database Model Design Conventions

Several design conventions were adopted for use 

in the PINE Database model. These conventions 

provide standard rules for creating and assembling the 

various elements, and facilitate communication on how 

the different elements of the database function 

together. The following sections outline the basic 

design conventions used by the PINE database model.

Database Model Structure

The model consists of tables, each of which has 

one or more keys. Relationships join the tables by way 

of the keys. Tables, keys, and relationships are defined 

and discussed in more detail in the following sections.

Tables

Tables are the basic element of the database. The 

model contains many different tables, each of which is 

designed to hold a specific, defined, and delimited set 

of data. Data are compartmentalized into different 

tables to prevent redundancy and enable queries to be 

conducted on multiple aspects of the data. For example, 

one table may contain information on all the agencies 

involved in data collection; another table may contain 

information on the field methods used in data collection; and so on. Each table contains fields that hold a 

specific piece of information. For example, the agency 

table may have fields for the name of the agency, the 

contact person at that agency, or the address for the 

headquarters of that agency. 

The model contains several different types of 

tables. The type of table used depends on its function in 

the database. The different types of tables will be 

discussed later in the Naming Conventions section of 

this report. 

Keys

Each record in a table (row) is identified by a 

unique number, which is referred to as the primary key. 

The unique primary key in one table can be used in 

another table as a surrogate for all the information it 

represents in the first table. In this relationship, the 

primary key from the first (source) table is called a 

foreign key in the second (receiving) table. The shared 

primary key/foreign key fields provide a path to relate 

data in various tables to one another. It is in this way 

that the relational model provides a way to extract data 

from two or more tables in any desired combination 

(Tessler, in press). 

Most of the data tables in the PINE Database 

have a single field (column) that serves as the primary 

key. The key itself is simply an incremental integer that 
Design Documentation 3

represents each row of data in the table. Exceptions are 

the association tables described below, where a single 

key field is not practical or desirable. 

In some tables, a complex key consisting of two 

or more fields together serves as the primary key. For 

example, the Detection Limit table uses four fields 

together to serve as the primary key: the Sample 

Type_ID, the Laboratory_ID, the Constituent_ID, and 

the Effective Date. A unique combination of these four 

fields makes a primary key that identifies a unique 

detection limit.

Relationships

Relationships between tables using keys 

(primary keys and foreign keys) are modeled and built 

into the database. Relationships define how the data in 

the various tables are connected to one another. The 

concept of relationships can be illustrated using grammatical terms. Tables in a data model can be thought of 

as nouns. The relationships between tables can be 

thought of as verb phrases, which clearly and concisely 

define the nature and restrictions of the associations 

between nouns. For example, one could say that, “A 

site can have one or more samples.” In this sentence, 

the nouns identify two tables (Site and Sample), and the 

verb phrase (can have one or more) describes a “oneto-many” relationship between them. In other words, 

each Site has associated data for one or more Samples 

(Tessler, in press).

Naming Standards in the Model

Naming standards are an important tool that 

conveys information about an item’s identity or 

contents. In the PINE Database, naming standards 

apply to both tables and fields. 

Name Construction

The three name construction standards are:

1. Names are generally constructed using whole words 

in order to provide as much intuitive meaning as 

possible. There are a few exceptions to this rule, 

including for example, “Det” which is sometimes 

used in place of “Detection” to help limit the size 

of the item.

2. When names are composed of word phrases, the 

first letter of each word in the phrase is capitalized 

in order to assist in reading the name. Examples 

include AgencySampleNumber, and ToxicEquivalentFactor.

3. Names are constructed of only alphabetic characters, with no numerals, punctuation, or other special characters (such as spaces or dashes). Primary 

keys, which consist of the table name and an _ID 

suffix, are the only exception. The _ID suffix provides visual distinction between key and non-key 

(data) fields. 

Table Functional Prefixes

In the PINE Database, the naming standards for 

tables are a modification and extension of those in 

common use by Access developers and MS Visual 

Basic programmers. Five functionally distinct types of 

tables are defined by a distinct three-letter prefix 

applied to the table name. The five table name prefixes 

used in the PINE Database and their definitions are 

given below (Tessler, in press).

tbl – simple data table, for example tblSite. 

Tables with the tbl prefix hold primary data entered in 

the database. They have a single primary key field. 

This primary key is an integer automatically incremented for each new record. Consequently, each row 

of data in the table is identified by a unique integer. 

These tables typically contain one or more fields that 

are foreign keys from other tables. Thus, the tbl tables 

acquire data elements from other tables as their own 

extended attributes.

tds – domain table, static, for example tdsHUC. 

Tables with the tds prefix are called domain tables 

because they provide specific information describing a 

particular subject domain. These tables typically hold a 

list of classification/descriptive items that are used by 

other tables. The table is described as static because it 

should be complete, although it may need to be 

extended in the future. For example the tdsHUC table 

is fully populated with records describing all the hydrologic unit codes in Maine from the 8- to 14-digit level, 

but other elements may need to be added to this table in 

the future. Tds tables have a primary key field that is a 

unique integer and exists in other tables as a foreign 

key. Tds tables can be easily extended without 

affecting the rest of the database design.

tdx – domain table, user-extendable, for example 

tdxSpecies. Tables with the tdx prefix are domain 

tables that provide a list that the user will add to as 

needed. Unlike the tds tables, the tdx tables are not 

considered complete and will most likely need to be 

added to while using the database. For example, the 

tdxSpecies table will have entries added to it whenever 

new plant or animal species are added to the database. 
4 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

The tdx tables have a primary key field that is identified 

by a unique integer. Tdx tables provide an extendable 

domain list for characterizing records in data tables.

tad – association table, for example 

tadSedimentSize. Tables with the tad prefix are used to 

resolve many-to-many relations between two other 

tables. They contain the key fields from other tables 

and store data that describe some property unique to the 

association between these tables. For example, 

tadSedimentSize combines a Sample_ID with a particular Sieve_ID to form its primary key and stores data 

for a non-key field, SedimentPct. Hence, the table 

defines the percent of sediment captured at the specified sieve size for the specified sample. TadSedimentSize is related to the other tables (tblSediment and 

tdxSieve) in a many-to-many relation. This allows individual samples to be associated with more than one 

sieve and each sieve to be associated with more than 

one sample.

tas – association table, for example tasConstituentClassification. Tables with the tas prefix are used to 

resolve many-to-many relations between two other 

tables. They contain the key fields from the other tables 

and therefore the associations between these tables. 

Unlike the tad tables, they do not store any data other 

than the association between the key fields. For 

example, tasConstituentClassification associates a 

Constituent_ID with a ConstituentClass_ID. Hence, 

the table defines the relationship between a constituent 

and a constituent class. Also note that one constituent 

can be associated with more than one constituent class.

The naming conventions described above are a 

useful feature in understanding the PINE Database. As 

the database evolves, new fields or tables should use 

these naming convention standards to ensure consistent 

communication about the functional properties of the 

new items.

Diagramming the Model

Diagrams are used to view the model and visualize database designs. These diagrams are referred to 

as E/R (Entity/Relationship) diagrams because they 

illustrate both the entities (tables) and relationships 

between these tables in the database model. The 

diagrams in this document use the “IDEF1X” (Integration DEFinition for Information Modeling) standard as 

defined in National Institute of Standards and Technology (1993) and implemented in the ERwin 

modeling software. The entire E/R diagram for the 

PINE Database model is shown in plate 1 (back of this 

report). Standards relevant to understanding the 

diagram are described below.

Tables

Boxes in the diagram are used to denote a table 

in the database, and different colors are used to identify 

the types of tables. The name of the table appears above 

the box in the diagram, and the fields in the table appear 

within the box. 

Keys

In the diagram, the primary key for each table is 

listed at the top of the box and is separated from the 

other fields by a horizontal line. The primary key has a 

‘PK’ designation in the diagrams. When a primary key 

from one table (the source table) is passed or migrated 

to another table (the receiving table) through a relation, 

the key is called a “foreign key” in the receiving table 

and has a ‘FK’ designation in the diagrams (Tessler, in 

press).

Relationships

Lines connecting tables in the diagram are used 

to represent the relation between those tables in the 

database. Each relation line has a direction and cardinality. The origin of the line is at the primary key field 

in the source table and is recognized by a single perpendicular on the relation line. The terminus of the line is 

at the acquired foreign key field in the receiving table 

and is recognized by a more complex cardinality 

symbol (usually a branch). The cardinality symbol 

defines the exact nature of the relationship between the 

tables. The most common relation in the PINE Database model is a “one-to-none-or-many.” This means 

that each source table can have none, or one or more 

receiving tables, and each receiving table must have a 

source table. The relationship is symbolized by an 

empty circle with three-branches (a crows’ foot) at the 

terminus of the relation line. If the foreign key is part of 

the primary key in the receiving table, the relation is 

said to be strong and the relation line is solid in the 

diagram (this will always be true for association 

tables). If the foreign key is a non-key (descriptive) 

attribute of the receiving table, the relation is weak and 

the line is dashed (Tessler, in press).
Design Documentation 5

Examination of the PINE Database Model

The detailed structure of the PINE Database 

model is shown in plate 1. The core of the PINE Database model consists of three basic elements or subject 

areas: site, sample, and result. These elements and their 

relationship to one another are examined in the 

following sections. More detailed table and field definitions are contained in the Data Dictionary in the 

appendix.

Site Subject Area of the Model

A site is defined as any source or place where an 

environmental sample is collected. A site can be a 

discrete point (a spot defined by a specific latitude and 

longitude) or a reach (a stretch of river defined by an 

upstream latitude/longitude and a length). Figure 1 

shows the location of site information in the PINE 

Database model for comparison to the detailed diagram 

in plate 1.

The site subject area is centered on the table 

tblSite but also includes the tables tdsSiteType, 

tdsHUC, and tdsAgency. Refer to plate 1 for details of 

the site subject area of the model.

TblSite

TblSite stores information on the locations 

where samples are collected. The table has a primary 

key, Site_ID, which is a unique number automatically 

assigned to each site entry in the database and can 

therefore be used to identify a site. It is used to represent all information associated with that particular 

Site_ID and is referenced in other tables in the database. TblSite also contains the foreign keys 

Agency_ID, SiteType_ID, and HUC_ID that are 

acquired from the tdsAgency, tdsSiteType, and 

tdsHUC tables, respectively. TblSite also contains the 

fields ParentSiteID, SiteName, SiteCode, Latitude, 

Longitude, SiteLength, SiteContact, and SiteComment 

to further describe a site.

Figure 1. General view of the site subject area of the model. (See plate 1 for details.)
6 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TdsSiteType

TdsSiteType stores a list of types of sites where 

samples are collected. The SiteType for a sediment 

sample is a point, whereas the SiteType for a tissue 

sample may be a reach. SiteType_ID is the primary key 

in this table and is a unique integer that is assigned to 

each SiteType entry in the database. It can therefore be 

used to identify a type of site. 

TdsHUC

TdsHUC stores a list of hydrologic unit codes 

(surface-water drainage basins). HUC_ID is the 

primary key in this table and is a unique integer that is 

assigned to each HUC entry in the database. TdsHUC 

contains the fields HUC08, HUC11, HUC14, 

HUCName, and HUC14SQMI.

TdsAgency

TdsAgency stores a list of agencies or parties 

that own or maintain sites or collect samples. 

Agency_ID is the primary key in this table and is a 

unique integer that is assigned to each agency entry in 

the database. It can therefore be used to identify an 

agency or party that owns or maintains sites or collects 

samples. TdsAgency contains the fields AgencyAbbrev, Agency, AgencyContact, and AgencyComment 

to further describe an agency.

Sample Subject Area of the Model

Any medium collected at a site is called a 

sample. A site is related to a sample in a one-to-many 

relationship, that is, many samples can be collected at a 

single site. Sample information includes the date and 

time that the sample was collected, the field method 

that was used to collect the sample, and the type of 

sample that was collected. Figure 2 shows the location 

of sample information in the model.

Refer to plate 1 for a detailed diagram of the 

sample subject area of the model. The database is 

configured to store information on four different types 

of samples: sediment samples, tissue samples, semipermeable membrane device (SPMD) samples, and 

water samples. General information that pertains to all 

four types of samples is stored in the main sample table, 

tblSample. Further information specific to each of the 

four types of samples is stored in their respective 

tables, tblSediment, tblTissue, tblSPMD, and tblWater. 

These tables appear under the main sample table, 

tblSample, in the model and are related to tblSample in 

a one-to-one-or-none relationship. Descriptions of the 

main sample area, the sediment sample area, the tissue 

sample area, the SPMD sample area, and the water 

sample area follow.

Main Sample Area of the Model

The general sample information for all samples 

(sediment samples, tissue samples, SPMD samples, 

and water samples) is stored in the main sample area of 

the model. The main sample area of the model includes 

the following tables: tblSample and the domain tables, 

tdsAgency, tdsFieldMethod, tdsWeatherConditions, 

tdsRiverConditions, tdsWaterLevel, tdsFoamRanking, 

tdsFoamSource, tdsSampleType, and tdsLab, which 

are described below.

TblSample

TblSample stores information on samples of 

field data or collected material at a particular site. 

Sample_ID is the primary key in this table. The key is 

a unique integer that is assigned to each sample entry in 

the database and can therefore be used to identify a 

sample. Site_ID is a foreign key in this table and is 

acquired from the table tblSite. Consequently, Site_ID 

is the field that relates a sample to a site. This is a oneto-many relationship, meaning that one site can have 

many samples, so multiple Sample_IDs may be related 

to the same Site_ID. TblSample also contains the 

foreign keys SampleType_ID, Agency_ID, 

FieldMethod_ID, Lab_ID that are acquired from the 

tdsSampleType, tdsAgency, tdsFieldMethod, and 

tdsLab tables respectively. TblSample also contains the 

fields SampleDate, SampleTime, AgencySampleNumber, QualityControlSample, LabSampleNumber, 

ExtractionDate, AnalysisDate, and SampleComment 

to further describe the sample.

TdsAgency

TdsAgency stores a list of agencies or parties 

that own or maintain sites or collect samples. 

Agency_ID is the primary key in this table and is a 

unique integer that is assigned to each agency entry in 

the database. It can therefore be used to identify an 

agency or party that owns or maintains sites or collects 

samples. TdsAgency also contains the fields AgencyAbbrev, Agency, AgencyContact, and AgencyComment to further describe an agency.
Design Documentation 7

TdsFieldMethod

TdsFieldMethod stores a list of methods and 

equipment used to collect field samples. 

FieldMethod_ID is the primary key in this table. The 

key is a unique integer that is assigned to each field 

method entry in the database and can therefore be used 

to identify a field method. TdsFieldMethod also 

contains the fields FieldMethod, Equipment, and FieldMethodComment to further describe a field method.

TdsWeatherConditions

TdsWeatherConditions stores information on the 

weather conditions at the time a sample is collected. 

WeatherConditions_ID is the primary key in this table 

and is a unique integer that is assigned to each weather 

condition entry in the database. It can therefore be used 

to identify a weather condition. TdsWeatherConditions 

also contains the fields WeatherConditions and WeatherConditionsComment.

TdsRiverConditions

TdsRiverConditions stores information on the 

river conditions at the time a sample is collected in the 

field. RiverConditions_ID is the primary key in this 

table and is a unique integer that is assigned to each 

river condition entry in the database. It can therefore be 

used to identify a river condition. TdsRiverConditions 

also contains the fields RiverConditions and RiverConditionsComment.

TdsWaterLevel

TdsWaterLevel stores information on the water 

level in a stream or river during the collection of a 

sample. WaterLevel_ID is the primary key in this table 

Figure 2. General view of the sample subject area of the model. (See plate 1 for details.)
8 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

and is a unique integer that is assigned to each water 

level entry in the database. It can therefore be used to 

identify a water level. TdsWaterLevel also contains the 

fields WaterLevel and WaterLevelComment.

TdsFoamRanking

TdsFoamRanking stores information on the 

foam ranking, based on the amount of foam in a river, 

stream, or lake, during the collection of a sample. 

FoamRanking_ID is the primary key in this table and is 

a unique integer that is assigned to each foam ranking 

entry in the database. It can therefore be used to identify a foam ranking. TdsFoamRanking also contains the 

fields FoamRanking and FoamRankingComment.

TdsFoamSource

TdsFoamSource stores information on the foam 

source during the collection of a sample. 

FoamSource_ID is the primary key in this table and is 

a unique integer that is assigned to each foam source 

entry in the database. It can therefore be used to identify a foam source. TdsFoamSource also contains the 

fields FoamSource and FoamSourceComment.

TdsSampleType

TdsSampleType stores a list of types of samples 

collected. SampleType_ID is the primary key in this 

table and is a unique integer that is assigned to each 

sample type entry in the database. It can therefore be 

used to identify a type of sample.

TdsLab

TdsLab stores a list of laboratories responsible 

for analyzing samples and reporting results. Lab_ID is 

the primary key in this table and is a unique integer that 

is assigned to each laboratory entry in the database. It 

can therefore be used to identify a laboratory. TdsLab 

also contains the fields LabAbbrev, Lab, LabContact, 

EPACertified, and LabComment to further describe a 

laboratory.

Sediment Sample Area of the Model

Four tables hold information specific to a sediment sample: tblSediment, tblGrab, tadSedimentSize, 

and tdxSieve. Refer to plate 1 for a detailed diagram of 

the sediment sample area of the model.

TblSediment

TblSediment stores information on the details of 

a sediment sample. Sample_ID is used as a foreign key 

in this table to represent all information associated with 

that particular Sample_ID. The Sample_ID is acquired 

from the table tblSample. TblSediment also contains 

the fields SedimentComposite, WetWeight, 

DryWeight, MoisturePct, OrganicContentPct, and 

SedimentComment to further describe a sediment 

sample.

 TblGrab

TblGrab is a table that stores information 

pertaining to the individual grabs in a sediment sample. 

One sediment sample may be composed of many grabs. 

Sample_ID is a foreign key in table tblGrab that is 

acquired from the table tblSediment. Consequently, 

Sample_ID is the field that links tblGrab to the main 

sediment table tblSediment. It links these two tables in 

a one-to-many relationship, meaning that one sediment 

sample can be composed of many grabs. TblGrab also 

contains the fields GrabNumber and GrabDepthofWater.

TadSedimentSize

TadSedimentSize stores information about the 

particle-size distribution of a sediment sample. It holds 

information on the percentage of the sediment sample 

captured in various sieves. Sample_ID is a foreign key 

in this table that is acquired from table tblSediment. 

Sieve_ID also is a foreign key in this table that is 

acquired from the table tdxSieve and is used to 

uniquely identify a specific sieve used for grain-size 

analysis. TadSedimentSize also contains the field SedimentPercent.

TdxSieve

TdxSieve holds information on the various 

sieves used to determine the sediment-size distribution 

of a sediment sample. Sieve_ID is the primary key in 

this table. The key is a unique integer that is assigned 

to each sieve entry in the database and can therefore be 

used to identify a sieve. TdxSieve also contains the 

fields SieveSize, SieveCode, and SedimentFraction.

Tissue Sample Area of the Model

Four tables hold information specific to a tissue 

sample: tblTissue, tdsTissueType, tblSpecimen, and 

tdxSpecies. Refer to plate 1 for a detailed diagram of 

the tissue sample area of the model.
Design Documentation 9

TblTissue

TblTissue stores information on a tissue sample. 

Sample_ID is a foreign key in this table and is acquired 

from the table tblSample. This key uniquely identifies 

a sample. TblTissue also contains the fields TissueComposite, TissueType_ID, TissueWeight, LipidPct, 

NumSpecimens, and TissueComment to further 

describe a tissue sample.

TdsTissueType

TdsTissueType stores a list of tissue types used 

for analysis. TissueType_ID is the primary key in this 

table. The key is a unique integer that is assigned to 

each tissue type entry in the database and can therefore 

be used to identify a tissue type. TdsTissueType also 

contains the field TissueType.

TblSpecimen

TblSpecimen stores information about the individual specimens that make up a tissue sample. 

Sample_ID is a foreign key in this table that is acquired 

from the table tblTissue. The Sample_ID links 

tblTissue to tblSpecimen in a one-to-many relationship. Therefore, a tissue sample can be composed of 

many specimens. TblSpecimen also contains the fields 

SpecimenNumber, Species_ID, SpecimenWeight, 

SpecimenLength, SpecimenWidth, Gender, SpecimenAge, and SpecimenComment to further describe a 

specimen.

TdxSpecies

TdxSpecies stores a list of biotic species used for 

tissue sampling. Species_ID is the primary key in this 

table and is a unique integer that is assigned to each 

species entry in the database. It can therefore be used to 

identify a species. TdxSpecies also contains the fields 

SpeciesCode and Species.

SPMD Sample Area of the Model

The SPMD area of the model consists of the 

table tblSPMD (plate 1). This table stores all information pertaining specifically to an SPMD sample. The 

fields in this table are described below.

TblSPMD

TblSPMD stores details for an SPMD sample. 

Sample_ID is a foreign key in this table that is acquired 

from the table tblSample. TblSample also contains the 

fields SolventUsed, SolventVolume, SPMDDepthOfWater, and SPMDComment.

Water Sample Area of the Model

The water sample area of the model consists of 

the table tblWater (plate 1). This table stores all information pertaining specifically to a water sample. The 

fields in this table are described below. 

TblWater

TblWater stores details pertaining to a water 

sample. Sample_ID is a foreign key in this table that is 

acquired from the table tblSample. TblWater also 

contains the fields WaterSampleVolume and 

WaterComment.

Result Subject Area of the Model

Results are the data associated with the analysis 

of a sample and commonly are the values reported by 

the analyzing laboratory. The location of the result 

information in the model is shown in figure 3. The 

result information can be broken down into three 

subject areas: the main result area, the detection limit 

area, and the toxic equivalent factor area. These three 

areas of the model will be discussed in more detail in 

the following sections.

Main Result Area of the Model

The main result area stores information reported 

by a laboratory on the analysis of a sample. The main 

result area consists of tblResult and related domain 

tables: tdsConstituent, tdsResultFlag, tasConstituentClassification, tdxConstituentClass, and tdxUnit 

(plate1). Descriptions of these tables follow.

TblResult

TblResult stores information on results for a 

specific constituent in a sample. Result_ID is the 

primary key in this table. The key is a unique integer 

that is assigned to each result entry in the database and 

can therefore be used to identify a result. TblResult also 

contains the foreign key Sample_ID, which is acquired 

from the table tblSample. Sample_ID links tblSample 

to tblResult in a one-to-many relationship. This means 

that one sample can have many results associated with 

it. TblResult also contains the foreign keys 

Constituent_ID, ResultFlag_ID, and Unit_ID that are 

acquired from the tdsConstituent, tdsResultFlag, and 

tdxUnit tables respectively. TblResult also contains the 

fields Result and ResultComment to further describe 

the result.
10 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TdsConstituent

TdsConstituent stores a list of constituents for 

which measurement or analytical results are reported. 

Constituent_ID is the primary key in this table. The key 

is a unique integer that is assigned to each constituent 

entry in the database and can therefore be used to identify a constituent. TdsConstituent also contains the 

fields ConstituentCode, ConstituentAbbrev, and 

Constituent. 

TdsResultFlag

TdsResultFlag stores a list of flags used to 

qualify individual results (<, >, non-detect, etc.). It 

should be noted that the use of the word “average” in 

the database refers specifically to the arithmetic mean. 

ResultFlag_ID is the primary key in this table. The key 

is a unique integer that is assigned to each result flag 

entry in the database and can therefore be used to identify a result flag. TdsResultFlag also contains the fields 

ResultFlag and ResultFlagDescr.

TasConstituentClassification

TasConstituentClassification stores information 

on the association of a constituent with a classification 

group. TasConstituentClassification contains the 

foreign keys Constituent_ID and ConstituentClass_ID 

that are acquired from the tdsConstituent and tdxConstituentClass tables respectively.

TdxConstituentClass

TdxConstituentClass stores a list of groups used 

to classify constituents (such as nutrients, PCBs, or 

dioxin). ConstituentClass_ID is the primary key in this 

table. The key uniquely identifies a classification group 

for individual constituents. TdxConstituentClass also 

contains the field ConstituentClass.

Figure 3. General view of the result subject area of the model. (See plate 1 for details.)
Design Documentation 11

TdxUnit

TdxUnit stores a list of measurement or 

reporting units for constituent results. Unit_ID is the 

primary key in this table. The key uniquely identifies a 

measurement or reporting unit for a result. TdxUnit 

also contains the fields Unit and UnitAbbrev.

Detection Limit Area of the Model

The detection limit subject area describes the 

table tadDetectionLimit (plate 1). Data on detection 

limits helps to qualify the value of results that are 

obtained.

Each detection limit is identified by a unique 

combination of a sample type, a specific laboratory, a 

specific constituent, and a date. These are the four 

elements that determine the value of the detection limit. 

Therefore, they are the four fields that compose the 

primary key value of the table tadDetectionLimit. If 

any one of these elements changes, the detection limit 

may change as well. The only table that will be 

described in detail here is the table tadDetectionLimit. 

The other tables related to tadDetectionLimit have 

already been described in previous sections of this 

document.

TadDetectionLimit

TadDetectionLimit stores information on the 

associations of a sample type, laboratory, and constituent to record the detection limit in effect during a 

certain period of time. TadDetectionLimit contains the 

foreign keys SampleType_ID, Lab_ID, 

Constituent_ID, and Unit_ID that are acquired from the 

tdsSampleType, tdsLab, tdsConstituent, and tdxUnit 

tables respectively. TadDetectionLimit also contains 

the fields DetEffectiveDate, DetectionLimit, and 

DetectionLimitComment.

Toxic Equivalent Factor Area of the Model

The toxic equivalent factor area describes the 

table tadToxicEquivalentFactor (plate 1). Each toxic 

equivalent factor is identified by a unique combination 

of a constituent and a date. Therefore, they are the two 

fields that compose the primary key value of the table 

tadToxicEquivalentFactor. If either of these elements 

changes, the toxic equivalent factor may change as 

well.

TadToxicEquivalentFactor

TadToxicEquivalentFactor stores information 

on the associations of a constituent with a toxic equivalency factor weighting in effect over a period of time. 

The table tadToxicEquivalentFactor contains the 

foreign key Constituent_ID that is acquired from the 

table tdsConstituent. The table tadToxicEquivalentFactor also contains the fields TEFEffectiveDate, 

ToxicEquivalentFactor, and TEFComment.

Customizing / Extending the Database 

Model

The PINE database was designed to be fully 

extendable and customizable for changing needs. The 

two basic ways to extend the database to hold new data 

are to add fields and add tables. In either case, any 

extensions to or customizations of the data model 

should respect the existing design conventions. The 

data model also can be customized by reducing the 

number of fields and tables. That decision should be 

based on the value of the data to be stored, not on the 

difficulty of obtaining or entering it (Tessler, in press).

 A field can be added to any table; however, 

fields should not be added indiscriminately to a 

working copy of the database to solve short-term 

needs, such as to translate outside data into the PINE 

Database format, or to accommodate an unusual data 

inquiry. In those cases, additional accessory tables can 

be created to temporarily hold data in a one-to-one relationship with the parent table. The full power of PINE 

Database can then be applied to the added data without 

changing the basic model structure. Accessory tables 

allow new data to be entered for those records in the 

parent table that need them without altering existing 

structures. Adding tables to the structure is the easiest 

way to extend the data model. Adding tables provides 

a means to experiment with new data elements without 

affecting the data already residing in the database. 

Relating new tables to existing tables in a one-to-noneor-one relationship is the equivalent of adding fields to 

an existing table (Tessler, in press).
12 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

USER GUIDE

The Access Database and User Interface

The PINE Microsoft Access database was generated from the database model, discussed in the previous 

sections, using the ERwinTM version 3.5.2 (Platinum 

Technologies) software. A user interface was then 

added to the database. The interface consists of a set of 

forms that simplify the process of adding data to and 

editing data in the database. The following sections 

describe the basic structure of the database and the 

process of adding and editing data. They guide the user 

in populating the database but do not describe the 

processes of creating queries or reports. An Access 

user manual may be consulted for more information on 

how to conduct queries and generate reports.

Structure of the Database

The PINE database consists of two files: 

Penobscot_Data.mdb (which is the back-end of the 

database) and Penobscot_Forms.mdb (which is the 

front-end of the database). The back-end of the database stores the tables and the data that resides in those 

tables. The front-end of the database is linked to the 

back-end of the database. The front-end of the database 

does not contain actual tables; it contains only copies of 

the tables, and therefore does not hold any of the data 

that resides in the database. The front-end of the database acts as a user interface to the back-end of the database. This front-end, back-end design enables multiple 

users to interact with a single back-end via multiple 

front-end files on different computers. The following 

sections will examine the back-end and front-end of the 

database in more detail.

The back-end of the database 

(Penobscot_Data.mdb) consists of the tables that were 

generated from the database model and any data that 

was entered into those tables. The relationships 

between these tables, which were defined during the 

development of the database model, function behind 

the scenes in the database. All the tables that reside in 

the back-end of the database under the Tables tab are 

shown in figure 4. 

Figure 4. View of the tables in the back-end of the PINE database (Penobscot_Data.mdb).
User Guide 13

There are two ways to view the tables in the 

back-end of the database: form view and design view. 

Form view is the default view and allows the database 

user to see the data that has been entered into the table. 

The table tdsFieldMethod in form view is shown in 

figure 5.

Tables in the back-end of the database also can 

be viewed in design view by highlighting the table and 

then clicking on ‘Design’ instead of ‘Open’. Design 

view allows the user to see the field properties in the 

table as they were defined during the development of 

the database model. The table tdsFieldMethod in 

design view is shown in figure 6. When the row 

containing the primary key for the table is selected, a 

key icon appears in the left-most column. 

The back-end of the database is composed solely 

of tables and the data they hold. There are no elements 

under the Forms, Report, and Queries tabs. Forms, 

reports, and queries are all part of the front-end of the 

database. 

The front-end of the database serves as the database user interface. The tables in the front-end of the 

database are linked to their corresponding tables in the 

back-end of the database. Consequently, data entered 

into the tables in the front-end of the database are 

stored in the tables in the back-end of the database. 

Figure 7 shows the tables under the Tables tab in the 

front-end of the database. The linkage between the 

front-end of the database and the back-end of the database is represented by the arrows next to the tables in 

the front-end of the database.

Figure 5. Form view of the table tdsFieldMethod in the back-end of the database.

Figure 6. Design view of the table tdsFieldMethod in the back-end of the database.
14 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

The front-end of the database also contains dataentry forms under the Forms tab. These forms are used 

for entering data into the database. Due to the 

complexity of the database structure, entering data 

directly into the tables would be confusing and prone to 

error. The forms simplify the process of both entering 

and editing data. The forms under the Forms tab in the 

front-end of the database are shown in figure 8. 

Because of the large number of forms, a master 

form, known as the switchboard, was created. All dataentry forms (such as those shown in figure 8 and identified by the frm prefix) are accessed through the 

switchboard. The switchboard automates the process of 

opening and closing the forms necessary to perform 

various tasks. The switchboard is itself a form and 

exists under the Forms tab in the front-end of the database. Unlike the other forms, however, the switchboard 

has been programmed to open automatically when the 

user opens the front-end of the database. Figure 9 

shows the switchboard as it appears when the front-end 

of the database is opened.

The switchboard consists of various options and 

menus that the user can navigate through to perform 

different tasks and will automatically open when the 

front-end of the database, Penobscot_Forms.mdb, is 

opened. The switchboard opens the forms displayed in 

figure 8 to perform these tasks. To decrease the likelihood of error and ensure the integrity of the data in the 

database, the user should add or edit data in the database via the switchboard. The forms also contain an exit 

button that will return the user to the appropriate form 

or switchboard. It must be noted, however, that in some 

cases the exit button on the forms may be inaccessible. 

For example, the fields and buttons, including the exit 

button, on the form to edit a site will be inaccessible if 

there are no site entries in the database. In this case, the 

user can manually close the form by clicking on the X 

in the upper right-hand corner of the form. It must also 

be noted that the switchboard does not contain a function to exit the database itself. To exit the database, go 

to ‘file’ in the upper left-hand corner of the Access 

window and select ‘exit’ or click on the X in the upper 

right-hand corner of the Access window. A diagram of 

the various menus and options in the switchboard is 

shown in figure 10.

Figure 7. View of linked tables in the front-end of the database.
User Guide 15

Figure 8. View of forms in the front-end of the database. 

Figure 9. View of the switchboard in the front-end of the PINE database. 
16 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

Figure 10. Diagram of the database switchboard.

Main Menu

Add Site

Add New Sample

Add Results for Sediment Sample

Add Results for a Tissue Sample

Add Results for a SPMD Sample

Add Results for a Water Sample

Edit Existing Site

Edit General Sample Information

Edit Sediment Sample Information

Edit Tissue Sample Information

Edit Water Sample Information

Edit Existing Results

Add / Edit Agency in Drop Down List

Add / Edit Field Method in Drop Down List

Add / Edit Lab in Drop Down List

Add / Edit Tissue Type in Drop Down List

Add / Edit Constituent in Drop Down List

Add / Edit Sieve in Drop Down List

Add / Edit Species in Drop Down List

Add / Edit Unit in Drop Down List

Define Detection Limit

Define Toxic Equivalent Factor

Add Data

Edit Data

Add / Edit Items in Drop Down Lists

Add / Edit Sieve, Species, and Unit Choices

Define Detection Limits / Toxic Equivalent 

Factors/ Constituent Classifications

Edit SPMD Sample Information

Define a Constituent Classification

Add/Edit Constituent Class in Drop Down List

Add/Edit Result Flag in Drop Down List

Add / Edit Sampling Conditions Choices

Add / Edit Weather Condition in Drop Down List

Add / Edit River Condition in Drop Down List

Add / Edit Water Level in Drop Down List

Add / Edit Foam Ranking in Drop Down List

Add / Edit Foam Source in Drop Down List

Main Menu

Add New Sample

Add Results for Sediment Sample

Add Results for a Tissue Sample

Add Results for a SPMD Sample

Add Results for a Water Sample

Edit Existing Site

Edit General Sample Information

Edit Sediment Sample Information

Edit Tissue Sample Information

Edit Water Sample Information

Edit Existing Results

Add / Edit Agency in Drop Down List

Add / Edit Field Method in Drop Down List

Add / Edit Lab in Drop Down List

Add / Edit Tissue Type in Drop Down List

Add / Edit Constituent in Drop Down List

Add / Edit Sieve in Drop Down List

Add / Edit Species in Drop Down List

Add / Edit Unit in Drop Down List

Define Detection Limit

Define Toxic Equivalent Factor

Add Data

Edit Data

Add / Edit Items in Drop Down Lists

Add / Edit Sieve, Species, and Unit Choices

Define Detection Limits / Toxic Equivalent 

Factors/ Constituent Classifications

Edit SPMD Sample Information

Main Menu

Add New Sample

Add Results for Sediment Sample

Add Results for a Tissue Sample

Add Results for a SPMD Sample

Add Results for a Water Sample

Edit Existing Site

Edit General Sample Information

Edit Sediment Sample Information

Edit Tissue Sample Information

Edit Water Sample Information

Edit Existing Results

Add / Edit Agency in Drop Down List

Add / Edit Field Method in Drop Down List

Add / Edit Lab in Drop Down List

Add / Edit Tissue Type in Drop Down List

Add / Edit Constituent in Drop Down List

Add / Edit Sieve in Drop Down List

Add / Edit Species in Drop Down List

Add / Edit Unit in Drop Down List

Define Detection Limit

Define Toxic Equivalent Factor

Add Data

Edit Data

Add / Edit Items in Drop Down Lists

Add / Edit Sieve, Species, and Unit Choices

Define Detection Limits / Toxic Equivalent 

Factors/ Constituent Classifications

Edit SPMD Sample Information

Define a Constituent Classification

Add / Edit Constituent Class in Drop Down 

List Add / Edit Result Flag in Drop Down 

List

Add / Edit Sampling Conditions Choices

Add / Edit Weather Condition in Drop Down List

Add / Edit River Condition in Drop Down List

Add / Edit Water Level in Drop Down List

Add / Edit Foam Ranking in Drop Down List

Add / Edit Foam Source in Drop Down List
User Guide 17

Setting up the Database

To use the database, users should have a comparable, properly installed version of Access on their 

computer. When the Access Database is placed in a 

new location, the front-end of the database will not be 

linked to the back-end of the database. The user must 

manually link these two files by following the steps 

listed below. This linking process must be performed 

on every front-end file installed on each computer. One 

back-end file will exist on a single computer network, 

and all front-end files will be linked to this back-end 

file. 

1. Put the Penobscot_Data.mdb file and the 

Penobscot_Forms.mdb file on your computer or 

network. The Penobscot_Data.mdb and 

Penobscot_Forms.mdb files do not have to reside 

on the same computer to be linked, if they are 

being used on a connected system network. 

2. Open the Penobscot_Forms.mdb (front-end) file. 

3. Go to tools, database utilities, linked table manager. 

4. Click on ‘Select all’.

5. Check the ‘always prompt for new location’ button 

in the bottom left corner and click ok. 

6. Navigate to the Penobscot_Data.mdb (back-end) 

file and click ok. 

7. The notice ‘all selected linked tables were successfully refreshed’ will be displayed.

8. Close the linked table manager.

Adding Data to the Database

Data in the database are added and edited 

through the switchboard in the Penobscot_Forms.mdb 

(front-end) file. Although the switchboard narrates the 

process of data entry, some basic principles must be 

understood to use the switchboard successfully. Three 

levels of information are held in the database: site 

information, sample information, and result information. The order of data entry is very important. Site 

information must be entered into the database before all 

other information. Once a site has been entered into the 

database, then a sample corresponding to that site can 

be entered. Finally, when a sample has been entered 

into the database, a result can be entered for that 

sample.

When entering data via the switchboard into the 

data-entry forms, it is important to fill out all required 

fields. These fields are underlined on the forms. Failure 

to complete required fields will result in unsaved 

records.

Add a New Site

To enter information for a new site:

1. Click on ‘Add Data’ in the main menu of the 

switchboard.

2. Click on ‘Add Site’. This opens the form frmSiteSave.

3. Enter the information for the new site in the appropriate fields.

4. Click ‘Save Record’ to save the new site in the database and return to the switchboard or Click ‘Cancel’ to abort. If you cancel, none of the data will 

be entered into the database and you will be 

returned to the switchboard.

Add a New Sample

To enter information for a new sample:

1. Click on ‘Add Data’ in the main menu of the 

switchboard. 

2. Click on ‘Add New Sample’. This opens the form 

frmSite and allows the user to see all sites that 

have been entered into the database.

3. Use the record navigation bar at the bottom of the 

form to scroll to the appropriate site in the database.

4. Click on ‘Add Samples’ to add a sample for the site 

you have selected (this opens the form frmSampleSave) or click ‘Cancel’ to abort. If you cancel, 

none of the sample information will be entered 

into the database and you will be returned to the 

form frmSite where you will click ‘Exit Site’ to 

exit the application and return to the switchboard.

5. In the form frmSampleSave, enter general sample 

information in the appropriate fields.

6. Click on ‘AddSedimentInfo’, ‘AddTissueInfo’, 

‘AddSPMDInfo’, or ‘AddWaterInfo’ (depending 

on what type of sample you are entering) to add 

more detailed information about the sample.

7. Enter the information in the appropriate fields.

8. Click ‘Save Sample’ to save the information in the 

database or click ‘Cancel’ to abort. You will be 
18 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

returned to the form frmSite. Click on ‘exit Site’ 

to exit the application. The ‘Cancel’ button cancels only the additions/changes that have been 

made in the main form if no data have been 

entered into the subforms. If the subform has been 

accessed, hitting the ‘Cancel’ button will close the 

main form but will not actually cancel any 

changes or additions that have been made. 

Add a New Result for a Sediment Sample

To enter information for a new sediment sample 

result:

1. Click ‘Add Data’ in the main menu of the switchboard.

2. Click ‘Add Results for Sediment Sample’. This 

opens the form frmSediment and allows the user 

to see all sediment samples that have been entered 

into the database.

3. Use the record navigation bar at the bottom of the 

form to scroll to the sediment sample for which 

you want to add a result.

4. Click on ‘Add Results’. This opens the form frmResult.

5. Enter the result information in the appropriate 

fields. If the results pertain to a profile, leave the 

‘Result’ field itself blank and complete the subform, frmProfile subform, at the bottom of the 

Result form.

6. Click on ‘Save Results’ to save the result information in the database and return to the form frmSediment or click ‘Cancel’ to abort. If you cancel, you 

will be returned to the form frmSediment. The 

‘Cancel’ button cancels only the additions/ 

changes that have been made in the main form, if 

no data have been entered into the subform. If the 

subform has been accessed, then hitting the ‘Cancel’ button will close the main form but will not 

actually cancel any changes or additions that have 

been made.

7. Click on ‘Add Results’ to add another result for this 

sediment sample or click ‘Exit Sample’ to exit the 

form frmSediment and return to the switchboard.

Add a New Result for a Tissue Sample

To enter information for a new tissue sample 

result:

1. Click ‘Add Data’ in the main menu of the switchboard.

2. Click ‘Add Results for Tissue Sample’. This opens 

the form frmTissue.

3. Use the record navigation bar at the bottom of the 

form to scroll to the tissue sample for which you 

want to add a result.

4. Click on ‘Add Results’. This opens the form frmResult.

5. Enter the result information in the appropriate 

fields. If the results pertain to a profile, leave the 

‘Result’ field itself blank and complete the subform, frmProfile subform, at the bottom of the 

Result form.

6. Click on ‘Save Results’ to save the result information in the database and return to the form frmTissue or click ‘Cancel’ to abort. If you cancel, you 

will be returned to the form frmTissue. The ‘Cancel’ button cancels only the additions/changes 

that have been made in the main form if no data 

have been entered into the subform. If the subform has been accessed, then hitting the ‘Cancel’ 

button will close the main form but will not actually cancel any changes or additions that have 

been made.

7. Click on ‘Add Results’ to add another result for this 

tissue sample or click ‘Exit Sample’ to exit the 

form frmTissue and return to the switchboard.

Add a New Result for an SPMD Sample

To enter information for a new SPMD sample 

result:

1. Click ‘Add Data’ in the main menu of the switchboard.

2. Click ‘Add Results for SPMD Sample’. This opens 

the form frmSPMD.

3. Use the record navigation bar at the bottom of the 

form to scroll to the SPMD sample for which you 

want to add a result.

4. Click on ‘Add Results’. This opens the form frmResult.
User Guide 19

5. Enter the result information in the appropriate 

fields. If the results pertain to a profile, leave the 

‘Result’ field itself blank and complete the subform, frmProfile subform, at the bottom of the 

Result form.

6. Click on ‘Save Results’ to save the result information in the database and return to the form frmSPMD or click ‘Cancel’ to abort. If you cancel, 

you will be returned to the form frmSPMD. The 

‘Cancel’ button cancels only the additions/ 

changes that have been made in the main form, if 

no data have been entered into the subform. If the 

subform has been accessed, then hitting the ‘Cancel’ button will close the main from but will not 

actually cancel and changes or addition that have 

been made.

7. Click on ‘Add Results’ to add another result for this 

SPMD sample or click ‘Exit Sample’ to exit the 

form frmSPMD and return to the switchboard.

Add a New Result for a Water Sample

To enter information for a new water sample 

result:

1. Click ‘Add Data’ in the main menu of the switchboard.

2. Click ‘Add Results for Water Sample’. This opens 

the form frmWater.

3. Use the record navigation bar at the bottom of the 

form to scroll to the water sample for which you 

want to add a result.

4. Click on ‘Add Results’. This opens the form frmResult.

5. Enter the result information in the appropriate 

fields. If the results pertain to a profile, leave the 

‘Result’ field itself blank and complete the subform, frmProfile subform, at the bottom of the 

Result form.

6. Click on ‘Save Results’ to save the result information in the database and return to the form frmWater or click ‘Cancel’ to abort. If you cancel, you 

will be returned to the form frmWater. The ‘Cancel’ button cancels only the additions/changes 

that have been made in the main form, if no data 

have been entered into the subform. If the subform has been accessed, then hitting the ‘Cancel’ 

button will close the main form but will not actually cancel any changes or additions that have 

been made.

7. Click on ‘Add Results’ to add another result for this 

water sample or click ‘Exit Sample’ to exit the 

form frmWater and return to the switchboard.

Editing Data in the Access Database

Editing existing data in the access database is the 

second option in the main menu of the switchboard. 

The process of editing site, sample, and result information is explained below.

Edit an Existing Site

To edit information for an existing site:

1. Click ‘Edit Data’ in the main menu of the switchboard.

2. Click ‘Edit Existing Site’. This opens the form frmSiteEdit.

3. Use the record navigation bar at the bottom of this 

form to scroll to the appropriate site.

4. Make any necessary changes to the site information.

5. Click ‘Save Changes’ to save the changes you have 

made and return to the switchboard or click ‘Cancel’ to abort. If you cancel, none of the changes 

you made to the site will be saved in the database 

and you will be returned to the switchboard.

Edit General Sample Information

To edit information for an existing sample:

1. Click ‘Edit Data’ in the main menu of the switchboard. 

2. Click ‘Edit General Sample Information’. This 

opens the form frmSampleEdit.

3. Use the record navigation bar at the bottom of this 

form to scroll to the appropriate sample.

4. Make any necessary changes to the general sample 

information.

5. Click ‘Save Changes’ to save the changes you have 

made and return to the switchboard or click ‘Cancel’ to abort. If you cancel, none of the changes 

you made to the sample will be saved in the database and you will be returned to the switchboard.
20 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

Edit Specific Sediment Sample Information

To edit information for an existing sediment 

sample:

1. Click on ‘Edit Data’ in the main menu of the switchboard.

2. Click ‘Edit Sediment Sample Information’. This 

opens the form frmSedimentEdit.

3. Use the record navigation bar at the bottom of this 

form to scroll to the appropriate sediment sample.

4. Make any necessary changes to the sediment sample information.

5. Click ‘Save Changes’ to save the changes you have 

made and return to the switchboard or click ‘Cancel’ to abort. If you cancel, you will be returned to 

the switchboard. The ‘Cancel’ button cancels 

only the changes that have been made in the main 

form, if no data have been changed in the subforms, frmGrab subform and frmSedimentSize 

subform. If the subforms have been accessed, 

then hitting the ‘Cancel’ button will close the 

main form but will not actually cancel any 

changes that have been made.

Edit Specific Tissue Sample Information

To edit information for an existing tissue sample:

1. Click ‘Edit Data’ in the main menu of the switchboard.

2. Click ‘Edit Tissue Sample Information’. This opens 

the form frmTissueEdit.

3. Use the record navigation bar at the bottom of this 

form to scroll to the appropriate tissue sample.

4. Make any necessary changes to the tissue sample 

information.

5. Click ‘Save Changes’ to save the changes you have 

made and return to the switchboard or click ‘Cancel’ to abort. If you cancel, you will be returned to 

the switchboard. The ‘Cancel’ button cancels 

only the changes that have been made in the main 

form, if no data have been entered into the subform, frmSpecimen subform. If the subform has 

been accessed, then hitting the ‘Cancel’ button 

will close the main form but will not actually cancel any changes that have been made.

Edit Specific SPMD Sample Information

To edit information for an existing SPMD 

sample:

1. Click on ‘Edit Data’ in the main menu of the switchboard.

2. Click ‘Edit SPMD Sample Information’. This opens 

the form frmSPMDEdit.

3. Use the record navigation bar at the bottom of this 

form to scroll to the appropriate SPMD sample.

4. Make any necessary changes to the SPMD sample 

information.

5. Click ‘Save Changes’ to save the changes you have 

made and return to the switchboard or click ‘Cancel’ to abort. If you cancel, none of the changes 

you made to the sample will be saved in the database and you will be returned to the switchboard.

Edit Specific Water Sample Information

To edit information for an existing water sample:

1. Click ‘Edit Data’ in the main menu of the switchboard.

2. Click ‘Edit Water Sample Information’. This opens 

the form frmWaterEdit.

3. Use the record navigation bar at the bottom of this 

form to scroll to the appropriate water sample.

4. Make any necessary changes to the water sample 

information.

5. Click ‘Save Changes’ to save the changes you have 

made and return to the switchboard or click ‘Cancel’ to abort. If you cancel, none of the changes 

you made to the sample will be saved in the database and you will be returned to the switchboard.

Edit a Result

To edit information for an existing result:

1. Click ‘Edit Data’ in the main menu of the switchboard.

2. Click ‘Edit Existing Results’. This opens the form 

frmResultEdit.

3. Use the record navigation bar at the bottom of this 

form to scroll to the appropriate result.

4. Make any necessary changes to the result information.

5. Click ‘Save Changes’ to save the changes you have 

made and return to the switchboard or click ‘Can-
User Guide 21

cel’ to abort. If you cancel, you will be returned to 

the switchboard. The ‘Cancel’ button cancels 

only the changes that have been made in the main 

form, if no data have been added/changed in the 

subform frmProfile. If the subform has been 

accessed, then hitting the ‘Cancel’ button will 

close the main form but will not actually cancel 

any changes or additions that have been made.

Adding / Editing Items in the Drop Down 

Lists

Adding or editing items to a drop down list or 

domain table is done through the switchboard. This 

includes adding or editing agencies, field methods, 

weather conditions, river conditions, water levels, 

foam rankings, foam sources, laboratories, tissue types, 

and constituents in the database.

Add / Edit Agency in Drop Down List

To enter or edit information for a new or existing 

agency:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Agency in Drop Down List’. This 

opens the form frmAgency.

3. Use the record navigation bar to scroll to the 

Agency you want to edit or advance to a blank 

copy of the form to add a new agency.

4. Make any necessary changes or additions. (Note: If 

you are adding a new agency, you have to assign 

an Agency_ID. Make sure the number you assign 

is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Field Method in Drop Down List

To enter or edit information for a new or existing 

field method:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Field Method in Drop Down List’. 

This opens the form frmFieldMethod.

3. Use the record navigation bar to scroll to the field 

method you want to edit or advance to a blank 

copy of the form to add a new field method.

4. Make any necessary changes or additions. (Note: If 

you are adding a new field method, you have to 

assign a FieldMethod_ID to uniquely identify that 

field method. Make sure the number you assign is 

not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Weather Condition in Drop Down List

To enter or edit information for a new or existing 

weather condition:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Weather Condition in Drop Down 

List’. This opens the form frmWeatherCondition.

3. Use the record navigation bar to scroll to the 

weather condition you want to edit or advance to 

a blank copy of the form to add a new weather 

condition.

4. Make any necessary changes or additions. (Note: If 

you are adding a new weather condition, you have 

to assign a WeatherConditions_ID. Make sure the 

number you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit River Condition in Drop Down List

To enter or edit information for a new or existing 

river condition:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit River Condition in Drop Down 

List’. This opens the form frmRiverCondition.

3. Use the record navigation bar to scroll to the river 

condition you want to edit or advance to a blank 

copy of the form to add a new river condition.

4. Make any necessary changes or additions. (Note: If 

you are adding a new river condition, you have to 

assign a RiverConditions_ID. Make sure the 

number you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.
22 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

Add / Edit Water Level in Drop Down List

To enter or edit information for a new or existing 

water level:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Water Level in Drop Down List’. 

This opens the form frmWaterLevel.

3. Use the record navigation bar to scroll to the water 

level you want to edit or advance to a blank copy 

of the form to add a new water level.

4. Make any necessary changes or additions. (Note: If 

you are adding a new water level, you have to 

assign a WaterLevel_ID. Make sure the number 

you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Foam Ranking in Drop Down List

To enter or edit information for a new or existing 

foam ranking:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Foam Ranking in Drop Down 

List’. This opens the form frmFoamRanking.

3. Use the record navigation bar to scroll to the foam 

ranking you want to edit or advance to a blank 

copy of the form to add a new foam ranking.

4. Make any necessary changes or additions. (Note: If 

you are adding a new foam ranking, you have to 

assign a FoamRanking_ID. Make sure the number you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Foam Source in Drop Down List

To enter or edit information for a new or existing 

foam source:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Foam Source in Drop Down List’. 

This opens the form frmFoamSource.

3. Use the record navigation bar to scroll to the foam 

source you want to edit or advance to a blank copy 

of the form to add a new foam source.

4. Make any necessary changes or additions. (Note: If 

you are adding a new foam source, you have to 

assign a FoamSource_ID. Make sure the number 

you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Lab in Drop Down List

To enter or edit information for a new or existing 

laboratory:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Lab in Drop Down List’. This 

opens the form frmLab.

3. Use the record navigation bar to scroll to the laboratory you want to edit or advance to a blank copy 

of the form to add a new lab.

4. Make any necessary changes or additions. (Note: If 

you are adding a new laboratory, you have to 

assign a Lab_ID. Make sure the number you 

assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Tissue Type in Drop Down List

To enter or edit information for a new or existing 

tissue type:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Tissue Type in Drop Down List’. 

This opens the form frmTissueType.

3. Use the record navigation bar to scroll to the tissue 

type you want to edit or advance to a blank copy 

of the form to add a new tissue type.

4. Make any necessary changes or additions. (Note: If 

you are adding a new tissue type, you have to 

assign a TissueType_ID. Make sure the number 

you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.
User Guide 23

Add / Edit Constituent in Drop Down List

To enter or edit information for a new or existing 

constituent:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Constituent in Drop Down List’. 

This opens the form frmConstituent.

3. Use the record navigation bar to scroll to the constituent you want to edit or advance to a blank copy 

of the form to add a new constituent.

4. Make any necessary changes or additions. (Note: If 

you are adding a new constituent, you have to 

assign a Constituent_ID. Make sure the number 

you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Constituent Class in Drop Down List

To enter or edit information for a new or existing 

constituent class:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Constituent Class in Drop Down 

List’. This opens the form frmConstituentClass.

3. Use the record navigation bar to scroll to the constituent class you want to edit or advance to a blank 

copy of the form to add a new constituent class.

4. Make any necessary changes or additions.

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Add / Edit Result Flag in Drop Down List

To enter or edit information for a new or existing 

result flag:

1. Click ‘Add / Edit Items in Drop Down List’ in the 

main menu of the switchboard. 

2. Click ‘Add / Edit Result Flag in Drop Down List’. 

This opens the form frmResultFlag.

3. Use the record navigation bar to scroll to the result 

flag you want to edit or advance to a blank copy 

of the form to add a new result flag.

4. Make any necessary changes or additions. (Note: If 

you are adding a new result flag, you have to 

assign a ResultFlag_ID. Make sure the number 

you assign is not already in use.

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Adding /Editing Sieve, Species, and Unit 

Choices

Adding or editing choices to the tdx tables also is 

done through the switchboard. To add or edit sieve, 

species, or units in the database:

1. Click ‘Add / Edit Sieve, Species, and Unit Choices’ 

in the main menu of the switchboard. 

2. Click ‘Add / Edit Sieve, Species, or Unit in Drop 

Down List’. This opens the form frmSieve, frmSpecies, or frmUnit.

3. Use the record navigation bar to scroll to the record 

you want to edit or advance to a blank copy of the 

form to add a new record.

4. Make any necessary changes or additions. (Note: If 

you are adding a new record, you have to assign a 

Sieve_ID, Species_ID, or Unit_ID. Make sure the 

number you assign is not already in use.)

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.

Defining Detection Limits/ Toxic 

Equivalent Factors/ Constituent 

Classification

Adding or editing choices to the tad and tas 

tables also is done through the switchboard. To add or 

edit Detection Limits, Toxic Equivalent Factors, or 

Constituent Classifications:

1. Click ‘Define Detection Limits/ Toxic Equivalent 

Factors/ Constituent Classification’ in the main 

menu of the switchboard. 

2. Click ‘Define Detection Limits, Toxic Equivalent 

Factors, or Constituent Classification in Drop 

Down List’. This opens the form frmDefineDetectionLimits, frmDefineToxicEquivalentFactors, or frmDefineConstituentClassification.

3. Use the record navigation bar to scroll to the record 

you want to edit or advance to a blank copy of the 

form to add a new record.

4. Make any necessary changes or additions. 

5. Click ‘Save’ to save the changes or additions you 

have made or click ‘Cancel’ to cancel any 

changes or additions you have made.
24 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

Deleting Data from the Access Database

Deleting data from the database can compromise 

the integrity of the database if it is not done properly 

and should be done only by a database administrator. 

Once data have been deleted from the database, it 

cannot be recovered. 

Data are not deleted through the switchboard. To 

delete a record, you have to manually delete all information pertaining to that record in all tables. Deletions 

are made only from the tas, tbl, and tad tables because 

these are the only tables that store primary data about a 

record. The other tables (tds and tdx) are domain tables 

that provide a list of choices but do not actually store 

any of the primary data for the record.

Due to the system of primary and foreign keys, 

and the various dependencies of the data on one 

another, a specific order must be followed to delete 

records from the database. You cannot delete data from 

one table if those data are referenced in another table. 

For example, you cannot delete a site if a number of 

samples refer to it. If data are not deleted from the 

tables in the correct order, fatal errors can occur and the 

database can be rendered completely unusable. 

Follow these steps to delete record(s) from the 

database. If the record being deleted does not have data 

in one of the tables listed, skip that table and go on to 

the next one that contains data for that record. If you get 

a message that tells you that the entry that you are 

trying to delete is referenced in another table, DO NOT 

PROCEED. Find the table(s) that the entry is referenced in and delete the entry from those tables first.

1. Open the back-end of the database, by selecting the 

Penobscot_Data.mdb file, and click on the Tables 

tab.

2. TblProfile: Open the table tblProfile. Choose the 

row(s) that contains the Result_ID you want to 

remove from the database and hit the delete key 

on your keyboard.

3. TblResult: Open the table tblResult. Choose the 

row that contains the Result_ID you want to 

remove from the database and hit the delete key 

on your keyboard.

4. TadDetectionLimit: Open the table tadDetectionLimit. Choose the row(s) that contain the DetectionLimit(s) you want to remove from the 

database and hit the delete key on your keyboard.

5. TadToxicEquivalentFactor: Open the table tadToxicEquivalentFactor. Choose the ToxicEquivalentFactor(s) you want to remove from the 

database and hit the delete key on your keyboard.

6. TasConstituentClassification: Open the table tasConstituentClassification. Choose the ConstituentClassification(s) you want to remove from the 

database and hit the delete key on your keyboard.

7. TadSedimentSize: Open the table 

tadSedimentSize. Choose the SedimentPct(s) you 

want to remove from the database and hit the 

delete key on your keyboard.

8. TblGrab: Open tblGrab. Select the row that contains the Sample_ID you want to remove from the 

database and hit the delete key on your keyboard.

9. TblSpecimen: Open tblSpecimen. Select the row 

that contains the Sample_ID you want to remove 

from the database and hit the delete key on your 

keyboard. 

10. TblSediment: Open tblSediment. Select the row 

that contains the Sample_ID you want to remove 

from the database and hit the delete key on your 

keyboard.

11. TblTissue: Open tblTissue. Select the row that contains the Sample_ID you want to remove from the 

database and hit the delete key on your keyboard.

12. TblSPMD: Open tblSPMD. Select the row that 

contains the Sample_ID you want to remove from 

the database and hit the delete key on your keyboard.

13. TblWater: Open tblWater. Select the row that contains the Sample_ID that you wish to remove 

from the database and hit delete.

14. TblSample. Open tblSample. Select the row that 

contains the Sample_ID you want to remove from 

the database and hit the delete key on your keyboard.

15. TblSite. Open tblSite. Because this table is referenced to itself (Site_ID is referenced to 

ParentSiteID), the deletion process is more complicated. First, delete all ParentSiteID values that 

match the Site_ID value you want to delete. For 

example, if you want to delete Site_ID number 

one, you must first delete the number one everywhere in the ParentSiteID column. Second, select 

the row that contains the Sample_ID you want to 

remove from the database and hit delete.
References Cited 25

If you want to delete a record that has data in all 

of these tables, you will have to go through the 15 steps 

in the deletion process. You also can delete parts of a 

record, as long as you follow the order outlined above. 

For example, if you want to delete the results that have 

been entered for a sample but not the sample itself, 

follow steps 1 to 3. If you want to delete all samples 

collected at a given site, follow steps 1 through 14 for 

all of the samples that were collected at that site. Do not 

delete data from one table that are referenced in another 

table. 

REFERENCES CITED

National Institute of Standards and Technology, 1993, 

Standard for integration definition for information 

modeling (IDEF1X): Federal Information Processing 

Standards Publication 184, 155 p.

Tessler, Steven, in press, Data model and relational design 

for the New England water-use data system: U.S. Geological Survey Open-File Report 01-359.

Data Dictionary 27

APPENDIX 1- Data Dictionary
28 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

Data Dictionary Table of Contents

Table and Field Listings ............................................................................................................................................. 29

TadDetectionLimit ............................................................................................................................................. 29

TadSedimentSize ............................................................................................................................................... 30

TadToxicEquivalentFactor................................................................................................................................. 30

TasConstituentClassification ............................................................................................................................. 30

TblGrab .............................................................................................................................................................. 31

TblProfile ........................................................................................................................................................... 31

TblResult ............................................................................................................................................................ 31

TblSample .......................................................................................................................................................... 32

TblSediment....................................................................................................................................................... 33

TblSite................................................................................................................................................................ 34

TblSpecimen ...................................................................................................................................................... 35

TblSPMD ........................................................................................................................................................... 36

TblTissue............................................................................................................................................................ 36

TblWater ............................................................................................................................................................ 37

TdsAgency ......................................................................................................................................................... 37

TdsConstituent ................................................................................................................................................... 38

TdsFieldMethod................................................................................................................................................. 38

TdsFoamRanking ............................................................................................................................................... 39

TdsFoamSource ................................................................................................................................................. 39

TdsHUC ............................................................................................................................................................. 39

TdsLab ............................................................................................................................................................... 40

TdsResultFlag .................................................................................................................................................... 40

TdsRiverConditions ........................................................................................................................................... 40

TdsSampleType ................................................................................................................................................. 41

TdsSiteType ....................................................................................................................................................... 41

TdsTissueType ................................................................................................................................................... 41

TdsWaterLevel................................................................................................................................................... 42

TdsWeatherConditions....................................................................................................................................... 42

TdxConstituentClass .......................................................................................................................................... 42

TdxSieve ............................................................................................................................................................ 43

TdxSpecies......................................................................................................................................................... 43

TdxUnit .............................................................................................................................................................. 43
Data Dictionary 29

DATA DICTIONARY

The data dictionary contains detailed information on the tables in the PINE database. It describes the tables 

and provides information on the fields in those tables. In the following sections, PK denotes primary key and FK 

denotes foreign key. The tables are organized in alphabetical order.

Table and Field Listings

TadDetectionLimit

[TadDetectionLimit stores information on the associations of a sample type, lab, and constituent to record the detection 

limit in effect during a certain period of time]

Field Name PK FK Required Type Default Description

SampleType_ID X X X Number Key that uniquely identifies a type 

of sample (sediment, tissue, 

SPMD, water)

Lab_ID X X X Number Key that uniquely identifies a lab 

that processes samples and 

reports results

Constituent_ID X X X Number Key that uniquely identifies a 

specific constituent

DetLimitEffectiveDate X X Date/Time Date from which a specific 

detection limit takes effect 

(is adopted); the limit remains 

in effect until another date and 

associated limit are defined

DetectionLimit X Number Numerical value of a detection 

limit defined by a combination 

of a specific sample type, 

analyzing lab, constituent, and 

effective date

Unit_ID X X Number Key that uniquely identifies a 

measurement or reporting unit 

for a result

DetectionLimitComment Memo Comment about a detection limit 

(e.g., reason for a new value or 

a change from a previous value)
30 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TadSedimentSize

[TadSedimentSize stores information about the grain-size distribution of a sediment sample. It contains information on the 

percentage of the sediment sample captured in various sieves]

Field Name PK FK Required Type Default Description

Sample_ID X X X Number Key that uniquely identifies a sample

Sieve_ID X X X Number Key that uniquely identifies a specific sieve used for 

sediment grain-size analysis

SedimentPct X Number Percent of sediment sample captured at a specific 

sieve size

TadToxicEquivalentFactor

[TadToxicEquivalentFactor stores information on the associations of a constituent with a toxic equivalent factor weighting 

in effect over a period of time]

Field Name PK FK Required Type Default Description

Constituent_ID X X X Number Key that uniquely identifies a 

specific constituent

TEFEffectiveDate X X Date/Time Date from which a specific toxic 

equivalent factor takes effect 

(is adopted)

ToxicEquivalentFactor X Number Numerical value of a toxic 

equivalent factor pertaining to 

a specific constituent and 

effective date

TEFComment Memo Comment associated with a 

toxic equivalent factor

TasConstituentClassification

[TasConstituentClassification stores a list of groups used to classify constituents into groups (dioxins, furans, 

nutrients, etc.)]

Field Name PK FK Required Type Default Description

Constituent_ID X X X Number Key that uniquely identifies a specific 

constituent

ConstituentClass_ID X X X Number Key that uniquely identifies a 

classification group (dioxins, furans, 

etc.) for individual constituents
Data Dictionary 31

TblGrab

[TblGrab stores information pertaining to the individual grabs in a sediment sample. One sediment sample may be composed of one grab or of many grabs]

Field Name PK FK Required Type Default Description

Sample_ID X X X Number Key that uniquely identifies a sample

GrabNumber X X Text Number assigned by the collecting agency or 

organization to an individual grab in a 

sediment composite 

GrabDepthOfWater X Number Depth of water (in feet) at the point where an 

individual sediment sample grab is collected

TblProfile

[TblProfile stores the result values for water samples collected at the various depths belonging to a profile]

Field Name PK FK Required Type Default Description

Result_ID X X X Number Key that uniquely identifies a result from a sample

ProfileDepth X X Number Depth of water where a sample was collected in a 

profile

ProfileValue Number Result value at a given depth of water in the profile

TblResult

[TblResult stores information on results for a specific constituent in a sample]

Field Name PK FK Required Type Default Description

Result_ID X X Number Key that uniquely identifies a result from a 

sample

Sample_ID X X Number Key that uniquely identifies a sample

Constituent_ID X X Number Key that uniquely identifies a specific 

constituent

ResultFlag_ID X X Number 0 Key that uniquely identifies a result flag used 

to qualify a result value; the default of 0 

indicates no result flag (values accepted as 

reported)

Result Number Result reported for a constituent from an 

analysis of a sample (the field may be left as 

null if it is a ‘non-detect’)

Unit_ID X X Number Key that uniquely identifies a measurement or 

reporting unit for a result

ResultComment Memo Comment about a result
32 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TblSample

[TblSample stores information on samples of field data or collected material at a particular site]

Field Name PK FK Required Type Default Description

Sample_ID X X Number Key that uniquely identifies a sample

Site_ID X X Number Key that uniquely identifies a site or 

place where samples are taken or 

collected

SampleDate X Date/Time Date when a sample was collected in 

the field

SampleTime Date/Time Time when a sample was collected in 

the field

SampleType_ID X X Number Key that uniquely identifies a type of 

sample (e.g., sediment, tissue, 

SPMD, water)

Agency_ID X X Number Key that uniquely identifies an 

agency or organization that has 

primary responsibility for a given 

site, or collects samples

Collector Text Initials or name(s) of person(s) 

collecting a sample

AgencySampleNumber X Text Number or code assigned to a sample 

in the field by the collecting 

agency (WDSS008 for Weldon 

Dam Sediment Sample #8)

FieldMethod_ID X X Number 0 Key that uniquely identifies a field 

method and equipment used in 

collecting a sample; the default of 

0 indicates field method is 

unknown

QualityControlSample X Yes/No Key that identifies whether or not a 

sample is a quality-control sample

SampleDepthOfWater Number Depth of water where a sample is 

collected

Lab_ID X X Number 0 Key that uniquely identifies a lab that 

processes samples and reports 

results; the default of 0 indicates a 

field measurement

LabSampleNumber Text Number assigned to a sample by the 

analyzing lab

ExtractionDate Date/Time Date that an extraction is performed 

on a sample

AnalysisDate Date/Time Date that an analysis is performed on 

a sample

WeatherConditions_ID X Number Key that uniquely identifies a 

weather condition

SamplingAirTemp Number Air temperature (in degrees Celsius) 

when a sample is collected in the 

field

RiverConditions_ID X Number Key that uniquely identifies a river 

condition
Data Dictionary 33

WaterLevel_ID X Number Key that uniquely identifies a water 

level

FoamRanking_ID X Number Key that uniquely identifies a foam 

ranking

FoamSource_ID X Number Key that uniquely identifies a foam 

source

SampleComment Memo Comment about a sample; can 

include field collection and lab 

handling notes

TblSediment

[TblSediment stores information on the details of a sediment sample]

Field Name PK FK Required Type Default Description

Sample_ID X X X Number Key that uniquely identifies a sample

SedimentComposite Text Composite number assigned to a sediment sample in 

the field (code for composite sample)

WetWeight Number Weight of sediment sample (in grams) before drying

DryWeight Number Weight of sediment sample (in grams) after drying

MoisturePct Number Percent moisture content of a sediment sample

OrganicContentPct Number Percent organic content of a sediment sample

SedimentComment Memo Comment about a sediment sample

TblSample—Continued

[TblSample stores information on samples of field data or collected material at a particular site]

Field Name PK FK Required Type Default Description
34 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TblSite

[TblSite stores detailed information on the site or place where samples are collected]

Field Name PK FK Required Type Default Description

Site_ID X X Number Key that uniquely identifies a site or place where 

samples are taken

ParentSiteID Number ParentSiteID is used to group sites together spatially; 

the ParentSiteID of a reach is the same as the 

Site_ID for that reach; the ParentSite of a point is 

the Site_ID of the reach that the point falls within

Agency_ID X X Number Key that uniquely identifies an agency or party that 

owns or maintains sites, or collects samples

SiteType_ID X X Number Key that uniquely identifies a type of site

SiteName X Text Common name of a site (Weldon Dam 

Impoundment)

SiteCode X Text Code or abbreviation for the name of a site

HUC_ID X Number Key that uniquely identifies a hydrologic unit

Latitude Number Latitude of a site in decimal degrees; for a reach this 

pertains to the upstream end of that reach

Longitude Number Longitude of a site in decimal degrees; for a reach, 

this pertains to the upstream end of that reach

SiteLength X Number -1 Length of a reach; the length is the distance (in feet) 

from the location defined by the latitude and 

longitude of the upstream end of the reach to the 

downstream end of the reach; if the site is a point, 

the site length for this mandatory entry is 0; the 

default of –1 indicates that the length is not 

determined

SiteContact Memo Name of the organization to contact for information 

about the site

SiteComment Memo Comment about a site
Data Dictionary 35

TblSpecimen

[TblSpecimen stores details about individual specimens of a tissue sample]

Field Name PK FK Required Type Default Description

Sample_ID X X X Number Key that uniquely identifies a sample

SpecimenNumber X X Text Identification number assigned to a 

specimen when it is collected in the field

Species_ID X X Number Key that uniquely identifies a species 

name

SpecimenWeight Number Weight (in grams) of a specimen; the 

weight for a turtle specimen is a rough 

estimate

SpecimenLength Number Length (in millimeters) of a specimen; the 

length of a turtle specimen is defined as 

the upper carapace length; the length of 

a muskrat specimen is defines as the 

length from the nose to the base of the 

tail

SpecimenWidth Number Width (in millimeters) of a specimen; the 

width of a turtle is defined as the width 

of the upper carapace

Gender Text Gender of a specimen (male, female, 

juvenile, unknown)

SpecimenAge Text Age of a specimen (adult, immature)

SpecimenComment Memo Comment about a specimen, including 

parasites, injuries, or deformities
36 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TblSPMD

[TblSPMD stores details pertaining to an SPMD (semi-permeable membrane device) sample]

Field Name PK FK Required Type Default Description

Sample_ID X X X Number Key that uniquely identifies a sample

SolventUsed Text Name of the solvent used in an SPMD sample

SolventVolume Number Volume of the solvent (in milliliters) used 

inside the SPMD to collect the sample

SPMDDepthOf Water Number Depth of the water (in feet) over an SPMD 

sampling point

SPMDComment Memo Comment about an SPMD sample

TblTissue

[TblTissue stores details pertaining to a tissue sample]

Field Name PK FK Required Type Default Description

Sample_ID X X X Number Key that uniquely identifies a sample

TissueComposite Text Composite number/code assigned to a tissue 

sample in the field (WDSMB01 for 

Weldon Dam Smallmouth Bass #01)

TissueType_ID X X Number Key that uniquely identifies a type of tissue

TissueWeight Number Weight (in grams) of a tissue sample used for 

analysis

LipidPct Number Percent lipid composition of a tissue sample 

used for analysis

NumSpecimens X Number 0 Total number of specimens used in a tissue 

sample; a number greater than 1 identifies 

composite samples (see specimen data); 

default of 0 indicates ‘not reported’

TissueComment Memo Comment about a tissue sample
Data Dictionary 37

TblWater

[TblWater stores detailed information for a water sample]

Field Name PK FK Required Type Default Description

Sample_ID X X X Number Key that uniquely identifies a sample

WaterSampleVolume Number 0 Volume of water sample in milliliters; default 

of 0 indicates ‘not reported’

WaterComment Memo Comment about a water sample

TdsAgency

[TdsAgency stores a list of agencies or organizations that have primary responsibility for sites, or collect samples]

Field Name PK FK Required Type Default Description

Agency_ID X X Number Key that uniquely identifies an agency or 

organization that maintains sites, or 

collects samples

AgencyAbbrev X Text Abbreviation (USGS) for an agency or 

organization that has primary 

responsibility for a given site, or collects 

samples

Agency X Text Full name (U.S. Geological Survey) of an 

agency or organization that has primary 

responsibility for a given site, or collects 

samples

AgencyContact Memo Contact information for an agency or 

organization (name of person to contact at 

the given agency or organization, phone 

number, address)

AgencyComment Memo Comment about an agency
38 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TdsConstituent

[TdsConstituent stores a list of constituents for which measurement or analytical results are reported]

Field Name PK FK Required Type Default Description

Constituent_ID X X Number Key that uniquely identifies a specific 

constituent

CASNumber Text CAS (Chemical Abstract Service) registry 

number corresponding to the given substance 

name in the Chemical Substance Index

ConstituentAbbrev X Text Abbreviation for a constituent (2,3,7,8-TCDD)

Constituent X Text Full name of a constituent (2,3,7,8, 

tetrachlordibenzo-p-dioxin)

IUPACNumber Text IUPAC (International Union of Pure and 

Applied Chemistry) Code Number

TdsFieldMethod

[TdsFieldMethod stores a list of methods and equipment used to collect field samples]

Field Name P K FK Required Type Default Description

FieldMethod_ID X X Number Key that uniquely identifies a field method and 

equipment used in collecting a sample

FieldMethod X Text Description of the field method used in collecting a 

sample

Equipment Text Description of the equipment used in collecting a 

sample using a particular field method

FieldMethodComment Memo Comment about a field method and equipment used 

in collecting a sample
Data Dictionary 39

TdsFoamRanking

[TdsFoamRanking stores a list of foam ranking classifications used to describe the presence and amount of foam at a site

Field Name PK FK Required Type Default Description

FoamRanking_ID X X Number Key that uniquely identifies a foam ranking

FoamRanking X Text Description of a foam ranking in a lake, river, or 

stream during the collection of a sample

FoamRankingComment Memo Comment on a foam ranking

TdsFoamSource

[TdsFoamSource stores a list of possible sources of foam at a site]

Field Name PK FK Required Type Default Description

FoamSource_ID X X Number Key that uniquely identifies a foam source

FoamSource X Text Description of the source of foam on a lake, river, or 

stream

FoamSourceComment Memo Comment on the source of foam

TdsHUC

[TdsHUC stores a list of Hydrologic Unit Codes (basins)]

Field Name PK FK Required Type Default Description

HUC_ID X X Number Key that uniquely identifies a Hydrologic Unit Code 

(basin)

HUC08 Text Hydrologic Unit Code (basin) at the 08-digit level

HUC11 Text Hydrologic Unit Code (basin) at the 11-digit level

HUC14 Text Hydrologic Unit Code (basin) at the 14-digit level 

HUC14Name Text Name of the Hydrologic Unit Code (basin) at the 

14-digit level 

HUC14SQMI Text Area in square miles of the Hydrologic Unit Code 

(basin) at the 14-digit level 
40 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TdsLab

[TdsLab stores a list of labs responsible for analyzing samples and reporting results]

Field Name PK FK Required Type Default Description

Lab_ID X X Number Key that uniquely identifies a lab that processes 

samples and report results

Lab X Text Name of lab analyzing samples and reporting results

LabAbbrev X Text Abbreviation used to refer to a lab

LabContact X Text Contact information for a lab

EPACertified X Yes/No Identifies whether or not a lab is USEPA-certified 

for the types of analyses required by the samples

LabComment Memo Comment about a lab

TdsResultFlag

[TdsResultFlag stores a list of flags used to qualify individual Results (<, estimated, non-detect, etc.)]

Field Name P K FK Required Type Default Description

ResultFlag_ID X X Number Key that uniquely identifies a result flag used to 

qualify a result value

ResultFlag X Text Flag that qualifies a result value (<, est, avg, 

ND); ‘null’ applied as a flag indicates that a 

value is acceptable as reported; ‘average’ is 

defined as the ‘mean’

ResultFlagDescr X Text Description of a result flag

TdsRiverConditions

[TdsRiverConditions stores a list of river conditions that may exist during the collection of samples]

Field Name P K FK Required Type Default Description

RiverConditions_ID X X Number Key that uniquely identifies a river condition 

RiverConditions X Text Description of a river condition that exists 

when a sample is collected in the field 

RiverConditionsComment Memo Comment on a river condition
Data Dictionary 41

TdsSampleType

[TdsSampleType stores a list of types of samples]

Field Name PK FK Required Type Default Description

SampleType_I

D

X X Number Key that uniquely identifies a type of sample

(sediment, tissue, SPMD, or water)

SampleType X Text Type of sample collected (sediment, tissue, SPMD, 

or water)

TdsSiteType

[TdsSiteType stores a list of types of sites where samples are taken or collected]

Field Name PK FK Required Type Default Description

SiteType_ID X X Number Key that uniquely identifies a type of site

SiteType X Text Type of site (point or reach) where a sample is 

collected; the SiteType for a sediment sample is a 

point, whereas the SiteType for a tissue sample 

may be a reach

TdsTissueType

[TdsTissueType stores a list of tissue types which are analyzed for various constituents]

Field Name P K FK Required Type Default Description

TissueType_ID X X Number Key that uniquely identifies a type of tissue

TissueType X Text Type of tissue (fish liver, whole fish, fish fillet) used 

to analyze a sample for various constituents
42 Environmental Database for Water-Quality Data for the Penobscot River, Maine: 

Design Documentation and User Guide

TdsWaterLevel

[TdsWaterLevel stores a list of descriptions of water levels in a lake, river or stream]

Field Name PK FK Required Type Default Description

WaterLevel_ID X X Number Key that uniquely identifies the water level in a lake, 

river, or stream during the collection of a sample

WaterLevel X Text Description of the water level in a lake, river, or 

stream during the collection of a sample

WaterLevelComment Memo Comment associated with the water level in a lake, 

river, or stream

TdsWeatherConditions

[TdsWeatherConditions stores a list of weather conditions that may exist during the collection of a sample in the field]

Field Name PK FK Required Type Default Description

WeatherConditions_ID X X Number Key that uniquely identifies a weather 

condition 

WeatherConditions X Text Description of a weather condition that 

may exist in the field during the 

collection of a sample

WeatherConditionsCommen

t

Memo Comment about a weather condition 

TdxConstituentClass

[TdxConstituentClass stores a list of groups used to classify constituents]

Field Name PK FK Required Type Default Description

ConstituentClass_ID X X Number Key that uniquely identifies a constituent 

class 

ConstituentClass X Text Constituent class or classification (e.g. 

dioxin, furan, PCB) used to group 

constituents 

ConstituentClassComment Memo Comment about a constituent class
Data Dictionary 43

TdxSieve

[TdxSieve stores a list of sieves used to characterize the grain-size distribution of sediment samples]

Field Name P K FK Required Type Default Description

Sieve_ID X X Number Key that uniquely identifies a specific sieve used for 

sediment grain-size analysis

SieveSize X Number Numerical value (in millimeters) describing the 

mesh size of a sieve (0.425)

SieveCode Text Code used to refer to a specific sieve

SedimentFraction X Text Category of sediment that is captured in the specified 

sieve size (fine sand, coarse sand, cobble)

TdxSpecies

[TdxSpecies stores a list of biotic species]

Field Name PK FK Required Type Default Description

Species_ID X X Number Key that uniquely identifies a species name

SpeciesCode X Text Code used to refer to a species name (‘smb’ for 

Smallmouth Bass)

Species X Text Common name of a species (Smallmouth Bass)

TdxUnit

[TdxUnit stores a list of measurement or reporting units]

Field Name PK FK Required Type Default Description

Unit_ID X X Number Key that uniquely identifies a measurement or 

reporting unit 

UnitAbbrev X Text Abbreviation for a measurement or reporting unit 

(‘pg/g’ for picogram per gram)

Unit X Text Measurement or reporting unit (picogram per gram)

Giffen, S.E., 2002—Environmental Database For Water-Quality Data for the Penobscot River, Maine: Design Documenta

U.S. Geological Survey OFR 01-485

District C

hief

M

ain

e

District

U.S. G

e

olo

gic

al S

urv

e

y

W

ater Reso

urc

es Divisio

n

2

6

G

a

n

n

esto

n

Driv

e

A

u

g

usta, M

ain

e

0

4

3

3

0