SOP_15_Data_Management_05-10.pdf

SOP NO. 15

Standard Operating Procedure For

DATA MANAGEMENT

Denver Federal Center Site-wide RCRA Facility Investigation

Prepared for:

General Services Administration

Rocky Mountain Region Denver, Colorado

Prepared by:

URS Corporation Denver, Colorado

May 2010

Revision 7.0

Denver Federal Center SOPs Revision No. 7.0

May 2010 Page i of iii

SOP NUMBER 15 DATA MANAGEMENT

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-i

1.0 PURPOSE AND SCOPE............................................................................................... 15-1

2.0 PERSONNEL QUALIFICATIONS .............................................................................. 15-1

3.0 PROCEDURES.............................................................................................................. 15-1

3.1 DATA MANAGEMENT COMPONENTS....................................................... 15-1

3.1.1 Site Wide RFI Database......................................................................... 15-2

3.1.2 Project RFI Databases............................................................................ 15-2

3.2 PROJECT COLLABORATION........................................................................ 15-3

3.3 FIELD/LAB ELECTRONIC ANALYTICAL DATA DELIVERABLE.......... 15-4

3.3.1 Non-Asbestos Data ................................................................................ 15-4

3.3.2 Asbestos Data ........................................................................................ 15-5

3.4 DATABASE STRUCTURE.............................................................................. 15-5

3.5 DATABASE MANAGEMENT SYSTEM........................................................ 15-8

3.5.1 Data Input .............................................................................................. 15-9

3.5.2 Queries ................................................................................................. 15-13

3.5.3 Statistics ............................................................................................... 15-14

3.5.4 Map .................................................................................................. 15-14

3.5.5 Release Evaluation............................................................................... 15-14

3.5.6 Reporting ............................................................................................. 15-16

3.6 DATABASE CHANGES ................................................................................ 15-17

3.7 DATA PACKAGE DELIVERY...................................................................... 15-17

3.8 DATABASE DISTRIBUTION ....................................................................... 15-19

List of Tables Table 1a Laboratory EDD Table Structure (non-asbestos data)

Table 1b Electronic Data Deliverable Structure (asbestos data)

Table 2 Site Table Structure

Table 3 Chemistry Samples Table Structure

Table 4 Chemistry Results Table Structure

Table 5 Chemistry TICs Table Structure

Table 6 Laboratory Quality Assurance Results

May 2010 Page ii of iii

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-ii

Table 7 Elevation Table Structure

Table 8 Field Measurements Table Structure

Table 9 Well Information Table Structure

Table 10 Lookup Table Structures

List of Figures Figure 1 Database Relationships

Figure 2 Database Management System

Figure 3 Example Preliminary Contaminants of Concern Report

Figure 4 Example Data Report Format

List of Attachments Attachment A Lookup Tables

May 2010 Page iii of iii

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-iii

REVISION LOG

Revision Number Description Date

0.0 Original SOP No. 15 May 2000

3.0 SOP No. 15 April 2004

4.0 SOP No. 15 February 2006

5.0 SOP No. 15 March 2010

6.0 SOP No. 15 April 2010

7.0 SOP No. 15 May 2010

May 2010 Page 15-1 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-1

1.0 PURPOSE AND SCOPE

This Standard Operating Procedure (SOP) provides technical guidance and methods that will be used for management of environmental data after sample collection and/or submittal to the analytical laboratory. This SOP serves as a supplement to the site-wide and investigation area specific workplans and field sampling plans, and is intended to be used in conjunction with the other SOPs in this volume.

2.0 PERSONNEL QUALIFICATIONS

Data management will be performed by personnel knowledgeable and experienced in environmental data management for large RFI projects, or who will work under the direct supervision of knowledgeable and experienced personnel. The RFI contractor data manager will have a bachelor’s degree in information management or a related field and will be familiar with the operation and requirements of the existing DFC environmental data management system (EDMS).

The GSA data manager (or designee) is responsible for administering the DFC environmental data management system and for coordinating with and granting appropriate access to the RFI contractor data manager and personnel. The RFI contractor project manager, project chemist, field sample manager, data manager, and analytical laboratory personnel are responsible for implementing this SOP on a project-specific basis. The RFI contractor project manager has overall responsibility for the data management program for each project. The project chemist has the responsibility for providing the data manager with the validation report and hard copies of laboratory reports. The field sample manager is responsible for providing the data manager with copies of all field records for data entry and quality assurance purposes. The RFI contractor data manager has direct responsibility for implementing the project-specific data management program, for ensuring the integrity of information uploaded into the DFC environmental data management system, for oversight of the analytical laboratory with respect to data management issues, and for delivery of the required data deliverables to GSA. Laboratory personnel are responsible for providing the RFI contractor data manager with the appropriate electronic data in the prescribed format and for ensuring that these electronic data match the official hard copy laboratory report.

3.0 PROCEDURES

The DFC environmental data management system will be employed to facilitate effective management of investigation data for each project. This system provides for efficient upload of field parameters and laboratory analytical data, quality assurance, routine data analysis, and reporting. The system automates many of the routine tasks involved in data management. The details of data management activities are described below.

3.1 DATA MANAGEMENT COMPONENTS

There are currently two versions of the DFC EDMS. These systems will run only on the Microsoft® Windows® platform. They have been thoroughly tested in the Microsoft® Windows

May 2010 Page 15-2 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-2

XP® environment, although they may run satisfactorily on later Windows® versions. The first, called the Site-Wide or Hosted system is used by GSA to manage Site-Wide RFI data and consists of a hosted Microsoft SQL Server 2000 database and a Microsoft® Windows XP® user interface application (UIA) called the hosted Database Management System (DBMS). The hosted DBMS runs on a Microsoft® Terminal Services® server and is accessed via the internet using the Microsoft® Windows Remote Desktop Connection® (RDC) component. The second version of the EDMS, called the desktop or project-specific system consists of a Microsoft® Access 2000® database and a UIA called the desktop DBMS. The structure of the database tables for these systems is identical, and the user interface applications provide similar functionality. The UIA for each system provides the basic user interface for working with either the Site-Wide or Desktop database.

3.1.1 Site Wide RFI Database

The Site-Wide system is maintained by GSA and may be accessed by designated users via the internet using the RDC. Activities that may be performed by each individual granted access to the database depend on the rights (e.g. external, read only, read/write, administrator) granted to that user at login. Access to the Site-Wide database via the RDC is restricted to read-only or external to all users with the exception of the GSA data manager (and designees). The GSA data manager will be responsible for adding users to the system on an as-needed basis and for assigning access rights to all users. RFI managers and staff with the need to access the Site-Wide database will be assigned read-only rights. When the database is made available outside of GSA and its contractors, users will be assigned “external” access rights. This level of security is equivalent to read-only access with the additional limitation that query-by-example tools are not available. Staff responsible for uploading contractor deliverables to the Site-Wide database will be assigned full read/write access to the database. In addition to all of the capabilities included in read-only access, this security level includes the ability to upload new data to the database, run ad-hoc queries in updateable mode. However, this level does not include access to administrator tools. Normally, only the GSA data manager will be assigned administrator rights.

This includes all of the capabilities of full read/write access and the ability to manage DBMS users (add and delete users and manage user security access). For the Site-Wide system, users and access rights persist regardless of the workstation from where the user accesses the system.

3.1.2 Project RFI Databases

At the beginning of each project, the GSA data manager will make available to the RFI contractor data manager the latest desktop DBMS and Access® database containing all of the necessary data structures (unpopulated), lookup tables, templates, and queries required by the desktop DBMS. The RFI data manager will be responsible for installing the desktop DBMS and database on local computers and/or a Local Area Network for use within their project(s). With respect to individual projects, the RFI Contractor data manager for that project will maintain the security and integrity of the project database. Personnel with responsibility for uploading laboratory data and entering field data into the database will be assigned read/write access.

Similar to the Site-Wide database, other users will typically be assigned read-only access via the

May 2010 Page 15-3 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-3

DBMS. The RFI data manager will also be responsible for providing access to the database directly using MS Access®, if necessary, to perform specialized operations that may not be available from the desktop DBMS. Users to be granted direct access to the local database will be selected by the RFI data manager on a case by case basis. For the desktop system, users and access rights are workstation specific, and therefore, must be established on each workstation where the DBMS is installed.

3.2 PROJECT COLLABORATION

GSA has established a web-based project collaboration tool for secure management and sharing of electronic RFI documents. The GSA data manager will assign access to users as directed by the GSA program manager. The project collaboration tool provides a familiar Windows®-like environment for sharing documents among members of the RFI program team at diverse locations using any web connection. For example, the GSA data manager will periodically upload a copy of the latest site-wide Access® database and Access® database template to the appropriate folder on the project collaboration site for download and use by RFI team members.

The folder structure of the Project Collaboration Site is as follows:

Folder Description User Access Level

Database Contains a compressed copy of the latest site-wide database in Microsoft® Access® format for download and use by RFI Contractors. This database is generated from the Site-Wide SQL Server database that is available through the hosted DBMS via the RDC. Also includes files containing the compressed Report template files and Map files used by the desktop DBMS. Finally, this folder includes a template database file that may be used as a starting point for new RFI projects.

Read-Only to all users except the Site Administrator (GSA Data Manager)

DBMSSetup Contains the latest desktop DBMS setup application.

This file may be downloaded and run to install the desktop DBMS on a local computer to access a local RFI project database. To use the hosted DBMS via the RDC, you DO NOT need to install the desktop DBMS locally.

Read-Only to all users except the Site Administrator

DwgsDatabase Contains a copy of the latest GSA drawing database application. This application may be used to create new data sets for the GSA Drawing Database. To access the hosted version of the GSA Drawing Database, you do not need a local copy of this application.

Read-Only to all users except the Site Administrator

Documents Used by GSA to post Site-Wide RFI documents (such as the QAPP and SOPs) for download and use by RFI Contractors.

Read-Only to RFI Contractors. Read/Write to GSA employees and their representatives and the Site Administrator

Shared This folder may be used by individual RFI contractors to share files to any other GSA RFI team member, but only

Read/Write to all users. All RFI team members have

May 2010 Page 15-4 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-4

Folder Description User Access Level

AS DIRECTED BY GSA. Data stored in the Shared folder will be purged on a monthly basis, and therefore, this folder can not be used for permanent data storage.

access to this folder.

<Contractor Folder> This folder, which will typically be given a name that is an abbreviation for the RFI Contractor, will be used primarily to upload GSA Data Deliverables created by RFI Contractors for delivery to GSA for upload to the Site-Wide Database. May also be used to transfer correspondence and other documents between contractors and GSA. Finally, the latest security and deliverables spreadsheets for the contractor should be maintained in subfolders within this folder. With the exception of these Security and Deliverables subfolders, all data stored in the contractor folder will be purged on a monthly basis, and therefore, this folder can not be used for permanent data storage.

Read/Write to all users.

Individual contractors will only see the folder to be used by their firm.

3.3 FIELD/LAB ELECTRONIC ANALYTICAL DATA DELIVERABLE

3.3.1 Non-Asbestos Data

In addition to the hard-copy analytical data package, the analytical laboratory will prepare an Electronic Data Deliverable (EDD) containing all field sample, field quality assurance, and laboratory quality assurance analytical results. The EDD format will be specified in the laboratory bid package and contract and should be produced as shown on Table 1a. With the exception of tentatively identified compounds (TICs), all records in the EDD should be uniquely identified by the Lab_ID, SDG_ID, LABLOT_NUM, RUN_NUM, Method_ID, CAS_NO, and Lab_QC_ID fields. Because the CAS_NO of TICs may not be known, a unique key for these records can not be established in advance. Therefore, the laboratory will exercise extra care to ensure that duplicate TIC results are not reported in the EDD. The laboratory should ensure that samples used for laboratory QC analysis (e.g. Matrix Spike, Matrix Spike Duplicate, etc.) are each assigned a unique Lab_ID, so that these can be uniquely identified in the EDD. The Samp_ID for Laboratory QC samples that use a field matrix for the analysis such as MS/MSDs should be populated with the same Samp_ID used with the primary sample. The Samp_ID for all other Laboratory QC samples should contain the same value used in the Lab_ID field in that record. The EDD should be output directly by the Laboratory Information Management System (LIMS) with additional electronic processing as necessary to produce the format shown on Table 1a. Each EDD should be provided to the RFI contractor data manager as an MS Access 2000 database with the laboratory’s sample delivery group as the file name. This database should contain one table called “Lab_Results” which contains the EDD for that sample delivery group.

Lookup tables containing Valid Values Lists for those fields that require them (Table 1a) will be included in the laboratory bid package and the final contract. The EDD shall contain records for

May 2010 Page 15-5 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-5 field sample results, field QC results, and laboratory QC results. The specific laboratory QC results to be included in the EDD will be specified in each individual laboratory contract. The laboratory will put into place procedures to ensure compliance with the format requirements in Table 1a.

It will be the responsibility of the laboratory to ensure that the EDD matches the official hard copy laboratory report, and the RFI contractor data manager will perform QC checks of the EDD to verify laboratory compliance with this requirement. The EDD may be transferred from the laboratory to the RFI contractor data manager via email attachment. Alternatively, the laboratory may establish a secure FTP site for posting and download of project EDDs.

3.3.2 Asbestos Data

For analytical results associated with asbestos data, the EDD for loading into the project database will be prepared manually by the RFI contractor from hard copy laboratory reports.

With the exceptions noted in this section and on Table 1b, the EDD should be identical to that prepared by the laboratory for other types of analytical data. For asbestos data, laboratory QC data will not be included in the EDD.

At the beginning of each project that includes collection of samples to be analyzed for asbestos, the GSA project manager will provide an electronic copy of a Microsoft® Access® 2000 database containing the latest EDD specification to the RFI contractor project manager. The EDD specification will be included in a database table called Lab_Results within the database. All of the appropriate lookup tables will also be included in the database, and relationships will be established that enforce referential integrity within the Lab_Results table. The RFI contractor will then use Microsoft® Access® 2000 to populate the Lab_Results table in the EDD. No data entry forms or other data entry aids (other than appropriate lookup tables) will be provided by GSA to the contractor, and therefore, the contractor must be familiar with the use of Microsoft® Access® to either create the appropriate data entry forms or enter data directly into the Lab_Results table. Once the EDD is created, the desktop DBMS will be used to load the EDD into the project database as described in Section 3.5.1.1.

3.4 DATABASE STRUCTURE

Tables 2 through 9 list the structure, data types, and field descriptions for the primary tables of the database. Additionally, Table 10 provides the structures of the various lookup tables used in the database, and the lookup table values, current as of the date of this SOP revision, are included in Attachment A. Figure 1 displays the primary relationships that will be employed for field samples. For all data submitted to GSA, each database field must be completed to the extent possible. With the exception of certain fields in analytical data that are rejected during data validation (see Section 3.5.1.3), the QAQC_Flag field in Chemistry_Results, and comment fields in various tables, there should be no Null fields in any table of the deliverable. In certain cases where values are not applicable, an “NA” can be entered into text fields (e.g. Alias_ID for Sites where none exists) or –999 for numeric fields (i.e. top of screen in a soil boring) may be

May 2010 Page 15-6 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-6 entered. However, wherever data are applicable to a given result, the appropriate value should be entered.

Each sampling point for which data are included in the database will be described in the Sites table (Table 2). This table will include available information that is unique to that site such as site name, horizontal and vertical location, screened interval, etc. The primary key for this table is the Site_ID field. It is imperative that, to the extent that the data are applicable, all fields in this table be completed with the appropriate information. For new sites, the RFI contractor project manager is responsible for providing to the RFI contractor data manager the information required to populate the Sites table.

Analytical results for field samples will be contained in three related tables. Sample-specific information such as site identification, sample date, analytical method, etc. will be included in the Chemistry_Samples table (Table 3). Data for non-TIC analytes that are unique to each analytical result such as analyte name, CAS Number, and concentration are included in the Chemistry_Results table (Table 4). The Chemistry_TICs table, which has an identical field structure to the Chemistry_Results table, will be used to store analytical results for TICs (Table 5). The Chemistry_Samples table is related to the Chemistry_Results and Chemistry_TICs tables by Sample ID (which is unique to the sample), the Method ID, and analytical Run Number. It is necessary to include the method identification in the relationship because each sample can be analyzed using multiple methods. Further, Run Number is included because each sample may be analyzed multiple times by the same method (exp. sample dilution). These fields also make up the primary key for the Chemistry_Samples table. The primary key for the Chemistry_Results table is based on these same fields with the addition of CAS_NO. Because CAS_NO provides the means of uniquely identifying individual records for a given sample and analytical method, this field must be populated for each record in the Chemistry_Results table. The RFI contractor data manager will provide the laboratory a list of valid values for each analyte and CAS Number to be quantified. For analytes that do not have an assigned CAS Number and have not been previously assigned a CAS_NO in the Parameter_Lookup table, the RFI contractor data manager will coordinate with the GSA data manager to obtain the appropriate value with which to populate the CAS_NO field for that analyte. Because the CAS_NO for TICs may not be known, there will be no unique key for the Chemistry TICs table.

Referential integrity will be maintained between the Chemistry_Samples table and the Chemistry_Results and Chemistry_TICs tables based on a one-to-many relationships. This means that each record in the Chemistry_Results and Chemistry_TICs tables must have a related record in the Chemistry_Samples table. Further, cascading updates and deletes of related records will occur automatically. For example, if the Method_ID in the Chemistry_Samples table for a specific sample were modified, then the related fields in the Chemistry_Results and Chemistry_TICs tables for that sample would be automatically updated. Further, when a record in the Chemistry_Samples table is deleted, all related records in the Chemistry_Results and Chemistry_TICs tables will be deleted.

May 2010 Page 15-7 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-7

Laboratory quality assurance results such as surrogate, matrix spike, matrix spike duplicate, and laboratory control spike recoveries will be maintained in the Lab_QC_Results table (Table 6).

The primary key for this table will include the Lab_ID, SDG_ID, LABLOT_NUM, RUN_NUM, Method_ID, CAS_NO, and Lab_QC_ID fields. The Samp_ID field will be populated with the field sample identification if a field sample is used for the analysis (for example, a matrix spike).

If a laboratory matrix is used (such as a laboratory control sample), then the Sample ID field will be populated with the laboratory identification (same value as Lab_ID).

The analytical laboratory is responsible for providing the EDD to the RFI contractor data manager in the format prescribed in Table 1a for upload to the chemistry tables in the database.

It is the responsibility of the field sample manager to provide copies of all completed chain of custody forms to the RFI contractor data manager and to ensure the accuracy of these forms.

Chain of Custody forms will be used to verify receipt and completeness of laboratory EDDs.

The Elevation table (Table 7) will be used to store data related to groundwater elevation measurements. Each record in this table represents one groundwater elevation measurement and is identified uniquely by the Site_ID and the Samp_Date of measurement. Because it is possible for the measurement point elevation at a particular well to change over time, this value is extracted from the Sites table at the time of data entry and included in the Elevation table along with the measurement. It is the responsibility of the field sample manager to provide appropriate water-level measurement records to the RFI contractor data manager and to ensure that these records are accurate. The RFI contractor data manager is responsible for ensuring that water-level measurements are accurately entered into the database.

The Field_Measurements table (Table 8) will be used to store values associated with a specific sampling event. Unlike the Chemistry and Elevation tables, each record in the Field_Measurements table includes values for multiple field parameters in a cross-tabulated format. This is done for the sake of compactness because the same group of parameters (usually pH, Temperature, Specific Conductance, Dissolved Oxygen, Redox Potential, and Turbidity) are normally measured during each groundwater monitoring event at each well. It is the responsibility of the field sample manager to provide appropriate field parameter measurement records (in the correct units) to the RFI contractor data manager and to ensure that these records are accurate. The RFI contractor data manager is responsible for ensuring that field measurements are accurately entered into the database.

The Well_Info table (Table 9) provides a means for RFI contractors to provide a written description of changes in well construction details (such as measuring point elevation) that are implemented as part of their RFI projects. Changes to well construction details described in this table must also be documented in the appropriate field(s) of the Sites table. This table is also used by RFI contractors to document samples/measurements that were required by the project work plan but were not collected. RFI contractors should include a written description of the reason(s) why the sample or measurement was not collected. Each record in the Well_Info table is uniquely identified by the Site_ID and Samp_Date.

May 2010 Page 15-8 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-8

In addition to the primary tables described above, a variety of lookup tables are employed in the database to provide lists of valid values for the fields in the primary tables. The lookup table structures are displayed in Table 10, and the lookup table values, current as of the date of this SOP revision, are included in Attachment A. These tables reduce data entry time and provide a measure of quality assurance by limiting the choices for data entry and upload to valid values.

Further, a number of template tables in the database are used by the DBMS for data entry and import as well as for reporting purposes.

3.5 DATABASE MANAGEMENT SYSTEM

The DBMS is a completely self-contained Windows XP® application (Figure 2). Microsoft® Access® need not be installed on the workstation in order to execute the DBMS. Once the initial setup is performed and the application has been run for the first time, the connection to the database is automatic requiring no user intervention. Detailed user instructions are not provided in this SOP as the DBMS includes extensive context-sensitive online help. For the DFC RFI, access to the DBMS is available in two modes:

• Hosted DBMS. The hosted DBMS is used to access the Site-Wide RFI database via the internet. In this mode, the user accesses the DBMS which is hosted on a Windows® Terminal Services® server using the RDC. No local installation of the DBMS or database is required. The purpose of this mode is to allow RFI contractors to query the database and download subsets of data, to perform basic spatial and statistical analysis, and to produce reports from the latest Site-Wide database. The data input options are not available in this mode.

• Desktop DBMS. The desktop DBMS is used to access a local RFI project database hosted on a local PC or a Local Area Network. The desktop DBMS application must be installed on one or more local client PCs. The database and supporting report and map files may be loaded on a local PC (single user) or a Local Area Network (multiple users). The setup application, template database, and supporting files are available for download from the GSA Project Collaboration site as described in Section 3.2. With the desktop DBMS, users may enter field data, load laboratory analytical data, perform data queries, perform basic statistical and spatial analyses, create GSA Data Deliverables, and print data reports.

Because of the prohibitive size of the Site-Wide database (> 500,000 analytical results), GSA recommends that users employ a project-specific database locally to access, analyze, and report their project-specific data. There is no licensing required for use of the desktop DBMS, so users may install as many copies of the DBMS on various workstations as desired.

However, it is highly recommended that all workstations access the same local database which would normally be loaded into a common folder on the contractor’s local area network.

RFI contractors should use the desktop DBMS for upload, management, and delivery of their project-specific RFI data. The DBMS provides tools for creating the required database deliverable in the correct format. However, because the primary data deliverable to GSA is an industry standard Microsoft® Access® database file in the format prescribed in this SOP, GSA

May 2010 Page 15-9 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-9 does not require that the DBMS be used to generate this data deliverable. Nonetheless, should the contractor data deliverable not correctly upload into the site-wide database, GSA will reduce the associated contract by the entire data management budget for the project. As described in Section 3.7 of this SOP, contractors are required to perform a test upload of their project data using the Desktop DBMS prior to delivery of the data to GSA.

The core components of the DMBS are described briefly here. Additional details regarding the DBMS operation may be found in the application’s extensive context-sensitive online help by pressing F1 at any time from within the application. Unless otherwise noted, the features described below pertain to both the hosted and desktop DBMS applications.

3.5.1 Data Input

The data input module of the desktop DBMS includes tools for importing a laboratory Electronic Data Deliverable (EDD) and templates for entering and calculating groundwater elevation data and field parameters. The required laboratory EDD structure is shown in Table 1a.

Some contractors choose to perform data input into their own database structures using Microsoft® Access® or other tools, then bridge the data into the DFC database structure.

Although GSA does not consider this to be the most efficient method of data processing, it is an acceptable process if the contractor has a thorough understanding of the database structure documented in this SOP, each of the fields in the database, and the associated valid values. If the contractor chooses to input data using a mechanism other than the desktop DBMS described below, any additional cost to correct the data structure (if it does not pass verification prior to loading into the Site-Wide database) will be covered by the contractor. The desktop DBMS automatically adds a seven digit date string representing the date of sample collection (-mmyydd) to the field sample identification for each field sample in the analytical chemistry tables to make that record distinguishable from previous or future samples collected from the same location. If data input is performed outside of the desktop DBMS environment, these suffixes must be added manually to the field sample ID.

3.5.1.1 Environmental Chemistry Data Loading

The process of loading environmental chemistry data into the database consists of a series of steps. The first step is for the RFI contractor to manually enter metadata regarding the sample into the database. These data include information about the samples being loaded that are not included in the EDD from the laboratory such as sample matrix, sampling method, depth of sample collection, and type of sample. In order to later join these metadata with data in the EDD, the contractor must concurrently enter the field sample identification into the system.

The next step is to load the EDD from the laboratory into the database. The EDD is initially uploaded into a template table for review and quality checks. No data are uploaded to the primary tables in the database until each field in the EDD that is based on a valid values list is checked for valid entries. During import, the desktop DBMS performs a series of integrity checks on the EDD to ensure that key violations will not occur when the flat file structure of the EDD is converted to the relational structure of the database. If errors are noted during this

May 2010 Page 15-10 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-10 process, the desktop DBMS provides the option of viewing the problem records, but the required changes must be made to the EDD outside of the desktop DBMS environment prior to importing to the database. This would normally be accomplished by requiring the laboratory to submit a revised EDD.

After the data are successfully imported to the template, the third step is to select the option that instructs the DBMS to check the EDD. During this process, the desktop DBMS checks character fields in the EDD against the valid values list in the corresponding lookup table (Attachment A).

If a value is encountered in the EDD that is not valid, a message is displayed asking the user to:

• Select a new value from a list of valid values,

• Continue data verification without changes, or

• To delete records that contain the invalid value from the EDD.

Normally, the first or third option would be chosen to either modify the syntax of the value or to abort import of all records containing the invalid value. The second option could be chosen if the user wants to complete the check so that an import error log file will be created. However, if this option is chosen, the data can not be uploaded to the primary database tables until the invalid data are corrected. The DBMS also checks to ensure that numeric and date data fall within a site-wide range of valid values which are defined in the Chem_Range table in the database. If data are encountered during import that do not fall within these global ranges, an appropriate entry is added to the import log, and the data can not be appended to the database until the value is corrected or the range of valid values in the Chem_Range table is modified to include the new value.

If errors occur during the import process, an import error log file is created describing each error and action taken by the user to correct the error, if any. At the completion of the verification step, the user is prompted to view this file, if desired. Because these files are saved as ordinary text files in the data directory, they can easily be emailed to the laboratory so that the necessary changes can be made to the EDD and resubmitted to the RFI contractor.

Once the EDD has been successfully imported to the laboratory template and verified, the fourth step is to review the EDD for quality assurance purposes prior to appending to the permanent chemistry tables. This may be performed either on-line using a spreadsheet-like grid provided by the desktop DBMS, by exporting the grid to another electronic format such as Microsoft® Excel® for review in an external application, or the user can print the grid from the desktop DBMS, and a check can be performed on paper. The contractor is required to perform a 10 percent check of all records to be uploaded from an EDD against the official laboratory report.

All fields in a given record shall be checked when reviewing that record. If more than 5 percent of the checked records (of the 10 percent reviewed) are found to contain errors, then the remaining 90 percent of the results will be checked against the official laboratory reports. After quality checks have been completed and any errors are corrected and checked, the final step is to append the EDD to the permanent chemistry database tables. After all data for a given sampling

May 2010 Page 15-11 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-11 event are loaded, a check of loaded data against those anticipated will be performed to assure completeness of the data set.

The original EDD for each delivery group will be archived on the server and permanently backed up to an external media using an automated backup system. Copies of all EDDs and official laboratory reports (in portable document format (pdf)) must also be delivered to the GSA Data Manager at the conclusion of each project or when requested by the GSA Project Manager.

3.5.1.2 Field Data Input

Groundwater elevation and field parameter data are normally input manually from field records using data entry templates provided by the desktop DBMS. Prior to opening the appropriate data entry template, the user is given the opportunity to specify default entries for text (such as measurement units) and date fields to minimize data entry errors and to limit repetitive data entry tasks. Other non-repetitive text fields may be selected from drop-down lists of valid values.

Further, the desktop DBMS checks each numeric value against a valid site-wide range (from the Elevation_Range and Field_Data_Range tables) for a given parameter. Data entry for field parameters must be performed using the units specified in Table 8. Any unit conversions must be performed prior to input to the database. To aid in this process, the desktop DBMS provides a dropdown calculator in the each numeric field of the data entry templates.

After data are entered into the appropriate template, the user should perform quality checks of the data using the on-line grid provided by the desktop DBMS which can optionally be printed out for paper comparison. 100 percent of the data entry will be checked against the appropriate field records. After quality checks are performed and any corrections are made and checked, field parameters may be appended to the permanent field parameters table. An additional step, calculation of groundwater elevation, is necessary before appending water-level data. This step will be performed after quality checks are completed and any required changes are made and checked. If the calculation step is performed before data-entry errors are corrected, the calculated groundwater elevations will not be properly updated. Once groundwater elevations have been calculated, the contractor will appended these data to the elevation table in the database using the desktop DBMS. Field parameter data are appended to the field measurements table in the database.

As an alternative to manual data input, RFI contractors may use handheld devices (e.g. Pocket PC, Palm) to collect measurements in the field, then use the “Contractor Data” source option under “Input New Data” to upload the data to the database. If this option is employed, RFI contractors must ensure that the data are in the exact format of the required deliverable as described in Tables 7 and 8.

3.5.1.3 Data Validation Entry

This section describes procedures for adding data validation qualifiers that have been assigned as part of the validation process to the project database.

Once laboratory EDDs have been uploaded to the project database and data validation has been completed, the project chemist will provide the RFI contractor data manager with the Data

May 2010 Page 15-12 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-12

Validation Report. Two fields in the Chemistry_Results table are designated specifically for data validation. These are Data_Flag which is used to hold the validation flag and Flag_Reason which contains a code indicating the reason for the flag assignment in Data_Flag. A third field, QC_Validation, in the Chemistry_Samples table is used to assign the level of validation performed with respect to a given SDG. All results that undergo validation in accordance with the QAPP are to be assigned a QC_Validation value of “4”.

The QAPP provides details of the assignment of the validation flags and reason codes during data validation. QAPP Tables 9-1 and 9-2 present the data validation qualifier definitions and Flag_Reason codes that may be used by the project chemist during the validation of the data packages. The Flag_Reason codes listed and described therein are strongly recommended for consistency of use in the database, but are not required. The magnitude of potential bias (e.g.

spike recovery) or imprecision (e.g. duplicate relative percent difference) must be specified in either the validation report or in the Flag_Reason code entered into the database. Bias direction codes include “I” for potential indeterminate bias, “L” for low bias or “H” for high bias. If qualification of a given result is required for more than one reason, and if the overall magnitude of a potential high or low bias can be easily determined, then the value of the overall potential bias shall either be indicated in the data validation report or in the database Flag_Reason code by a suffix of a hyphen followed by an “I” for indeterminate bias, “L” for low bias or “H” for high bias followed by the value of the overall bias or imprecision. For example, if the associated surrogate spike recovery for acetone was 12% and the calibration verification percent difference was 26% the overall potential low bias is –88% which would be indicated by “-L88%” as a suffix to the reason codes. If the magnitude or direction of bias can’t be easily determined, both reason codes are listed, separated by a comma, and followed by an “-I” to indicate indeterminate bias. The magnitude of potential bias or imprecision for each of the QC measures outside acceptance limits must be specified in either the validation report or in the Flag_Reason code entered into the database.

If the laboratory includes a “J” flag in the QAQC_Flag field of the EDD, and the reason for the laboratory “J” is a detected concentration between the SMDL and the RL, then “F” should be entered into the Data_Flag field. For results that are reported as detected by the laboratory but are changed to non-detect during data validation, the Is_Detect field should be used to reflect the non-detect rather than a “U” in the Data_Flag field. For example, if the laboratory reports a detection above the reporting limit, but during data validation this result is determined to be the result of method blank contamination, the reporting limit would be raised to the reported concentration, Is_Detect would be set to False, Data_Flag would be set to “:”, and Flag_Reason would be assigned a code of “MB”.

For analytes that are assigned an “R” or rejected validation code, the numeric fields containing concentration and detection limit information are assigned a Null value and the Is_Detect field is set to False. This is to ensure that rejected data are not inadvertently used during future data analyses. The QAQC_Flag field which contains laboratory data qualifiers, if any, is also set to Null for rejected records.

May 2010 Page 15-13 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-13

At the completion of data validation, the Data_Flag and Flag_Reason fields should contain a value assigned during data validation as described in the QAPP, or a “:” character to indicate that the data were reviewed during data validation, and no qualifier was assigned. There will be no Null values in either of these fields when the data are delivered to GSA. See Tables 9-1 and 9-2 of the QAPP for the appropriate data validation qualifiers and codes.

The desktop DBMS provides a Data Validation Query to facilitate manual entry of data validation codes. The RFI contractor data manager is responsible for ensuring that validation codes are entered into the database per the Data Validation Report. Additionally, after the codes are entered, the data validation query will be printed and 100 percent of the entered codes will be quality checked against the Data Validation Report. After any changes to the database that are required as a result of data validation are completed, 100 percent of the changes will be verified.

For each unique value of SAMP_ID, METHOD_ID, and CAS_NO, one and only one result will be selected and reported as useable by setting the USEABLE_DATA field in Chemistry_Results to True. For analytical results with more than one value of RUN_NUM, the project chemist (or designee) must select, during data validation, the most appropriate result for the intended use(s).

If all runs are equally valid, then the highest concentration for detected results or the result with the lowest reporting limit for non-detect results will be selected as useable. This information will then be provided to the RFI data manager who will update the USEABLE_DATA field in the Chemistry_Results table based on these selections. For analytical results with only one value of RUN_NUM, the USEABLE_DATA field will be set to True. Please note that if all results for a given SAMP_ID, METHOD_ID, and CAS_NO are rejected during data validation, then the USEABLE_DATA field for one unique combination of each SAMP_ID, METHOD_ID, and CAS_NO within the rejected data must be set to True. This is because there must be one useable result for each parameter analyzed regardless of whether any non-rejected data exist for that analysis. Rejected records that are marked as useable can be differentiated from other useable results by the presence of a Null value in the concentration and reporting limit fields.

After data validation entry for a given sample delivery group is completed in accordance with the data validation report as described above, the Is_Final field in the Chemistry_Results table will be set to True for each record in the group. This is performed to indicate that the results contained in the record are final and may be used for their intended purpose, subject to restrictions based on any assigned data validation qualifiers. This includes all records, including rejected data and records with USEABLE_DATA set to False. The desktop DBMS provides a tool that allows the user to perform a global update of the Is_Final field for a given SDG, Laboratory ID, and Method Type. Changes made to the database subsequent to this point must be performed and documented in accordance with Section 3.6.

3.5.2 Queries

The Queries module of the DBMS provides a variety of pre-defined data queries and tools for performing ad-hoc database queries. For pre-defined queries, users make a few simple selections (e.g. date range, sampling event, etc.) to narrow the criteria used to execute the query and the results are displayed in a browse window. For ad-hoc queries, users are given complete control

May 2010 Page 15-14 of 15-45

W:\Projects\22240312_GSA_Rev_Mod_Negot\8.0_Working_Files\Revised SOPs\SOP_15_Data Management_05-10.doc 5/7/2010 9:34 AM 15-14 using tools provided by the DBMS to perform complex query operations. The Queries module of the desktop DBMS is also used to create GSA data deliverables in the prescribed format for delivery of RFI project data to GSA. This process is described in more detail in Section 3.7

3.5.3 Statistics

The Statistics module of the DBMS includes tools for performing basic statistical analyses including sampling event statistics, temporal statistics, and comparisons between sampling events. The Event and Temporal Statistics procedures may be used to quickly provide summary statistics for all results associated with an individual sampling event or for all results at a single point or group of points over time. The results of all of the statistics options may be printed directly from the DBMS for inclusion in reports. At a minimum, statistical reports that form the basis of calculations included in report text or tables will be printed and included in the project file.

3.5.4 Map

The Map module…