United States Environmental Protection Agency Office of Ground Water and Drinking Water Auxiliary 1 Database and Documentation Production Release 5.0 Contract No. 68 W 99 002 Task Order No. 007 Product Control No. SDC 0002 007 HB 2011B April 21, 2000 SDC 0002 007 HB 2011B April 21, 2000 AUXILIARY 1 DATABASE AND DOCUMENTATION PRODUCTION RELEASE 5.0 CONTRACT NO. 68 W 99 002 TASK ORDER NO. 007 Prepared for:

United States Environmental Protection Agency Office of Ground Water and Drinking Water 401 M Street, SW. Washington, DC 20460 Task Order Project Officer:

Edward A. Cottrill Prepared by:

Systems Development Center Science Applications International Corporation 6565 Arlington Boulevard Falls Church, VA 22042 SDC 0002 007 HB 2011B April 21, 2000 iii CONTENTS 1.0 INTRODUCTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1 1.1.1 Purpose . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.1.2 Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3 1.1.3 Identification . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.1.4 Structure of the Document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 1.2 References . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5 1.3 Naming Conventions for Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 2.0 AUXILIARY 1 DATABASE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8 2.1 General Requirements for the Auxiliary 1 Database . . . . . . . . . . . . . . . . . . . . . . 8 2.2 Detailed Requirements for the Auxiliary 1 Database . . . . . . . . . . . . . . . . . . . . . 10 2.2.1 Utility Design ( TUXUTDES) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10 2.2.2 Utility Monthly ( TUXUTMON) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 2.2.3 Wholesale Information ( TUXWPURC) . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.2.4 Plant Design ( TUXPLTDES) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 2.2.5 Water Resource Design ( TUXWRDES) . . . . . . . . . . . . . . . . . . . . . . . . 16 2.2.6 Process Train Design ( TUXPTRAIN) . . . . . . . . . . . . . . . . . . . . . . . . . . 17 2.2.7 Plant Monthly ( TUXPLTMON) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17 2.2.8 Water Resource Monthly ( TUXWRMON) . . . . . . . . . . . . . . . . . . . . . . 26 2.2.9 Hypochlorite Stock Solution ( TUXHYPO) . . . . . . . . . . . . . . . . . . . . . . 26 2.2.10 Sampling Event ( TUXSAMPLE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26 2.2.11 Sample Event/ Sample ID Associative ( SESA) ( TUXSESA) . . . . . . . . . 33 2.2.12 Sample Comments ( TUXSCMMT) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 2.2.13 Analyte Comments ( TUXACMMT) . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 2.2.14 Microbiology ( TUXMICRO) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 34 2.2.15 Water Quality Parameters ( WQP) ( TUXWQP) . . . . . . . . . . . . . . . . . . . 35 2.2.16 Disinfectant Residuals ( TUXDISFRES) . . . . . . . . . . . . . . . . . . . . . . . . 38 2.2.17 Disinfection By Products ( DBP) ( TUXDBP) . . . . . . . . . . . . . . . . . . . . 39 2.2.18 Cyanogen Chloride ( TUXCNCL) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41 2.2.19 Chlorite/ Chlorate ( TUXCHLORS) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 2.2.20 Chlorine Dioxide/ Ozone Disinfection By Products ( DBP) ( TUXCLDIOX) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 2.2.21 Assimilable Organic Carbon/ Biodegradable Organic Carbon ( AOC/ BDOC) ( TUXABDOC) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 2.2.22 Unit Process ( TUXUNPRO) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 2.2.23 Chemical Feeds ( TUXCHEMFDS) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44 2.2.24 Ozone Chamber ( TUXOZCHM) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 2.2.25 Ozone Contactor ( TUXOZCON) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 45 2.2.26 Analyte ID Reference Table ( TUXANLYT) . . . . . . . . . . . . . . . . . . . . . . 46 SDC 0002 007 HB 2011B April 21, 2000 iv 2.2.27 EPA Region Reference Table ( TUXEPAREG) . . . . . . . . . . . . . . . . . . . 46 2.2.28 Sample Quarter Reference Table ( TUXQTR) . . . . . . . . . . . . . . . . . . . . 46 2.2.29 HUC Code Reference Table ( TUXUSGS) . . . . . . . . . . . . . . . . . . . . . . 46 2.2.30 Disinfectant Addition Normalization Table ( TUXDISINF_ NORM) . . . . 47 2.2.31 Feeder Chemical Normalization Table ( TUXFDCHEM_ NORM) . . . . . 47 3. 0 THE QUERY TOOL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47 APPENDIX A ICR Auxiliary 1 Database Structure APPENDIX B Auxiliary 1 Database Fields Sorted by MS Access Alias Name APPENDIX C Data Mapping of the Auxiliary 1 Database to the ICR Federal Database APPENDIX D Primary Key Reference Tables APPENDIX E Calculated Attributes for the ICR Auxiliary 1 Database APPENDIX FNormalization Calculations for Unique Chemical Formulas for Disinfectants in the ICR Initial Sampling Plan APPENDIX G Normalization Calculations for Unique Chemical Formulas for Chemical Feeds in the ICR Initial Sampling Plan EXHIBITS Exhibit 1. ICR DMS System Concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2 Exhibit 2. Auxiliary Database System Concept . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4 Exhibit 3. Naming Convention for Requirements . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 7 Exhibit 4. EPA Region Assignments Based on State . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12 Exhibit 5. Population Size Category . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14 Exhibit 6. Calculations for the Sample Event Sequence Number for Non Blended Plants . . . . 29 Exhibit 7. Calculations for the Sample Event Sequence Number for Blended Plants . . . . . . . . 31 SDC 0002 007 HB 2011B April 21, 2000 1 1.0 INTRODUCTION 1.1 Introduction The United States Environmental Protection Agency ( EPA) Office of Ground Water and Drinking Water ( OGWDW) is responsible for implementing the Public Water System Supervision ( PWSS) Program established under the auspices of the Safe Drinking Water Act ( SDWA) of 1974 ( Public Law 93 523) and the 1996 reauthorization of the Act. Two of OGWDW's major responsibilities under the Act are to set national standards for drinking water quality and to ensure that the states that have assumed primary enforcement responsibility ( Primacy) are enforcing these standards.

In February 1994, OGWDW proposed an Information Collection Rule ( ICR) requiring certain Public Water Systems ( PWS) to generate and provide the Agency with specific microbiological contaminant and Disinfection By Product ( DBP) monitoring data, general water quality data, and treatment plant design and operating information characterizing their water systems. OGWDW began developing a Data Management System ( DMS) to support the PWSS Program Information Systems Modernization ( ISM) Effort. The purpose was to provide contractor support to complete the development of and provide support for the following automated systems:

The ICR Water Utility Database System.

The ICR Laboratory Quality Control ( QC) Database System.

The ICR Federal Database System.

ICR Data Analysis Database 1 ( i. e., Auxiliary 1 Database) and accompanying Query Tool.

These systems are described collectively as the ICR DMS. The ICR Water Utility Database System allows water utilities to report required data via software directly to EPA. The Laboratory QC Database System enables the analytical laboratories that are approved to perform sample analyses under the ICR to report data directly to the Agency. Capabilities of the ICR Federal Database include data population, or the capability to upload data from utilities and laboratories to the central database; validation of Sample Analytical Result ( SAR) data according to standards established by EPA; and support for tracking the approval status of analytical laboratories. A diagram of the ICR DMS System Concept appears as Exhibit 1 on the following page.

After data are loaded into the ICR Federal Database, the data will be analyzed by a number of researchers and analysts. The ICR DMS Team was tasked with gathering requirements for and designing seven Data Analysis Databases ( Auxiliary Databases 1 through 7), which stores subsets of data extracted from the ICR Federal Database System. With the exception of Auxiliary 1 Database, the auxiliary databases will be constructed and implemented under a separate EPA SDC 0002 007 HB 2011B April 21, 2000 2 contract. The ICR DMS Team was also tasked with gathering requirements for, designing, and developing a Query Tool, which performs complex analytical queries against Auxiliary 1 Database to support analysis of the ICR data.

Analysts will use the ICR data to develop regulations to assure public health protection from DBPs and disease causing microbes ( pathogens), including Crypotosporidium, Giardia, and Viruses.

E xh ibit 1. ICR DMS System Concept 1.1.1 Purpose This user documentation provides a comprehensive and accurate description of the structure and contents of Auxiliary 1 Database. An understanding of its structure and contents is essential to using Auxiliary 1 Database and the Query Tool effectively in the analysis of ICR data. SDC 0002 007 HB 2011B April 21, 2000 3 This document is intended to be used by Auxiliary 1 Database and Query Tool users as a reference providing: details of the structure of Auxiliary 1 Database, definitions of the data contained by the database, and delineation of the relationships between data in Auxiliary 1 Database and its source, the ICR Federal Database.

The primary users of the Auxiliary 1 Database and the Query Tool will be personnel involved with developing national standards for drinking water quality. The DBP and Microbial Technical Working Groups ( TWG), and other related interested parties will be performing the majority of the analyses of ICR data. The probable composition of the overall user community who will utilize the Auxiliary Database is expected to include:

EPA analysts.

Stakeholder interest groups involved with rule development.

Water utilities.

Environmental groups.

Public health officials.

Analysts supporting public interest groups.

Researchers.  University.  Private.

1.1.2 Scope A diagram of the Auxiliary Database System Concept appears as Exhibit 2 on the following page. The purpose of each Auxiliary Database is to provide researchers and analysts with a smaller, yet comprehensive version of the ICR Federal Database. Auxiliary 1 Database was designed to serve as the master auxiliary database from which other auxiliary databases are created. This document presents detailed descriptions of the structure and content of Auxiliary 1 Database ( also called the Primary Auxiliary Database) and the Query Tool. Similar information for Auxiliary Databases 2 though 7 will be addressed under a separate EPA contract. SDC 0002 007 HB 2011B April 21, 2000 4 Exhibit 2. Auxiliary Database System Concept 1.1.3 Identification Requirements planning, design, and database development for the analysis portion of the ICR is sponsored by OGWDW's PWSS Program ISM Effort. Requirements planning and analysis for Auxiliary 1 Database was conducted under Delivery Order ( DO) 83, Information Systems Development and User Support for the Public Water System Supervision ( PWSS) Program's Information Collection Rule ( ICR), of the Mission Oriented Systems Engineering Support ( MOSES) contract at the Systems Development Center ( SDC). EPA Contract # 68 W1 0055 DO 83 ended on December 30, 1998. Task Order ( TO) 007 under EPA Contract # 68 W 99 002 began on December 31, 1998 and will continue to support the ICR.

1.1.4 Structure of the Document This document consists primarily of the requirements for Auxiliary 1 Database and the Query Tool. A set of appendices provides supporting details necessary for complete understanding of the structure and contents of the database and their relationship to the ICR Federal Database. The information presented in this document represents a cumulative update of information contained in the Primary Auxiliary Database Chapter of the Information Collection Rule ( ICR) Data Extraction Requirements Specification, incorporating changes and corrections resulting from the design and implementation of the Auxiliary 1 Database and the Query Tool. SDC 0002 007 HB 2011B April 21, 2000 5 The following is a brief description of the appendices:

Appendix ACContains the Entity Relationship Diagram ( ERD) for the Auxiliary 1 Database. The diagram details the relationships between the tables created for the Auxiliary 1 Database.

Appendix BCLists all of the fields in the Auxiliary 1 Database sorted by their corresponding MS Access Alias.

Appendix CCContains a series of tables ( one for each physical table in the Auxiliary 1 Database) that provides data mapping for each field included in the database to its source in the ICR Federal Database. In addition, Appendix C includes for each field, the attribute name, MS Access Alias, and definition. The MS Access Alias contains the text that will be inserted into the Caption property in Microsoft ® AccessJ 97. The Caption property will be used to display unabbreviated column names for each field in the Auxiliary 1 Database.

Appendix DCLists the tables in the Auxiliary 1 Database and the primary keys in each table.

Appendix ECContains the formulas that were used to create the requirements for calculated fields in the Auxiliary 1 Database.

Note: The formulas in Appendix E were provided to the ICR DMS Team by EPA and EPA stakeholders.

Appendix FCIdentifies the algorithms for normalizing disinfectant addition formulas.

Appendix GCIdentifies the algorithms for normalizing chemical feed formulas.

1.2 References The following documents are applicable to this user documentation. They include ICR DMS project documents, as well as government and industry materials used to develop this document.

Note: The documents are listed in reverse chronological order.

Auxiliary 1 User Documentation, Version 3.0. SDC; SDC 0002 007 EB 2007; September 22, 1999.

Revised Project Plan for Information Systems Development and User Support for the Public Water System Supervision ( PWSS) Program's Information Collection Rule ( ICR). SDC; SDC 0002 007 HB 2001; August 20, 1999. SDC 0002 007 HB 2011B April 21, 2000 6

Auxiliary 1 User Documentation, Version 2.0. SDC; SDC 0002 007 EB 1026; July 28, 1999.

Revised Information Collection Rule ( ICR) Data Management System ( DMS) System Manual. SDC; SDC 0002 007 SS 1021; July 2, 1999.

Auxiliary 1 User Documentation. SDC; SDC 0002 007 EB 1005B; June 7, 1999.

Auxiliary 1 User Documentation ( Beta Version). SDC; SDC 0002 007 EB 1005A; February 28, 1999.

Technical Summary for the Information Collection Rule ( ICR) Data Extraction Joint Application Design ( JAD) Meeting, August 17 19, 1998. EPA SDC; SDC 0055 083 BN 7027; September 23, 1998.

Primary Auxiliary Database Chapter of the Information Collection Rule ( ICR) Data Extraction Design Specification, EPA SDC; SDC 0055 083 FH 7026; August 21, 1998.

Primary Auxiliary Database Chapter of the Information Collection Rule ( ICR) Data Extraction Requirements Specification. EPA SDC; SDC 0055 083 EB 7022A; July 24, 1998.

Technical Summary for the Information Collection Rule ( ICR) Data Extraction Joint Application Design ( JAD) Meeting, June 15 17, 1998. EPA SDC; SDC 0055 083 MH 7023; July 6, 1998.

Technical Summary for the Information Collection Rule ( ICR) Data Extraction Joint Requirements Planning ( JRP) Meeting, May 19 20, 1998. EPA SDC; SDC 0055 083 MH 7021; June 1, 1998.

Revised Technical Summary for the Information Collection Rule ( ICR) Data Extraction Joint Requirements Planning ( JRP) Meeting, April 14 16, 1998. EPA SDC; SDC 0055 083 EB 7019A; May 22, 1998.

Information Collection Rule ( ICR) Data Extraction Joint Requirements Planning ( JRP) Meeting, February 20, 1998. EPA SDC; SDC 0055 083 MH 7015; March 6, 1998.

IEEE Recommended Practice for Software Requirements Specifications, Institute of Electrical and Electronics Engineers, Inc., IEEE Std 830 1993; April 8, 1994. SDC 0002 007 HB 2011B April 21, 2000 8 Exhibit 3. Naming Convention for Requirements 1.3 Naming Conventions for Requirements Each requirement is identified by a unique requirement number. The naming convention for the requirement numbers is presented in Exhibit 3.

Table Name refers to the table used for data modeling. Each table name begins with the prefix " TUX," which represents " table auxiliary." However, the " TUX" prefix is dropped in the requirement number. For example, requirements for the Utility Design table begins with " UTDES," not " TUXUTDES." For Auxiliary 1 Database, requirements that do not apply to a specific table are categorized as general requirements and begin with the prefix " GR."

Aux. # refers to the auxiliary database number associated with the requirement. For example, all requirements for the Utility Design table in the Auxiliary 1 Database begin with " UTDES01." All requirements for Sludge Production, Auxiliary Database 3, begin with " SLGPRO03."

Release # refers to the release for which the requirement is planned. For example, requirements for the Utility Design table for the first release of the Auxiliary 1 Database begin with UTDES01100. Requirements for the Utility Design table for the second release of the Auxiliary 1 Database begin with UTDES01200.

Serial # refers to the number of the requirement. The first requirement for the Utility Design table in Section 2.2.1 is UTDES011000010. The second requirement for the Utility Design table is UTDES011000020. The numbering convention allows for new requirements to be added in an orderly manner. For example, if a new requirement must be added to the Utility Design table and SDC 0002 007 HB 2011B April 21, 2000 9 it logically falls between requirement UTDES011000010 and UTDES011000020, it will be inserted as UTDES011000015.

2.0 AUXILIARY 1 DATABASE 2.1 General Requirements for the Auxiliary 1 Database This section defines the general requirements for the Auxiliary 1 Database. Requirements are numbered according to the conventions enumerated in Section 1.3 of this document.

GR011000009CThe ICR DMS shall produce a reference manual for the Auxiliary 1 Database.

GR011000010CThe ICR DMS shall produce a reference manual for the Auxiliary 1 Database that shall provide information regarding the relationships between each table.

GR011000020CThe ICR DMS shall produce a reference manual for the Auxiliary 1 Database that shall provide a definition of each field, including the units of measurement.

Note: EPA shall provide the ICR DMS Team with a definition of each field, including the units of measurement to be incorporated in the reference manual.

GR011000030CThe ICR DMS shall produce a reference manual for the Auxiliary 1 Database that shall provide a data mapping that indicates where data in the Auxiliary 1 Database were extracted from the ICR Federal Database.

GR011000040CThe ICR DMS shall produce a reference manual for the Auxiliary 1 Database that shall provide a list of formulas used to calculate fields that did not exist in the Federal Database, as well as any formulas used to normalize the data.

Note: The reference manual that shall accompany the Auxiliary 1 Database shall include an updated copy of the requirements specification.

GR011000050CThe ICR DMS Team shall conduct a beta test for stakeholders.

GR011000060CThe Auxiliary 1 Database shall contain all of the data elements necessary to create Auxiliary Databases 2 through 8.

GR011000070COnly samples and Sample Analytical Results ( SAR) with EPA_ QA_ Codes of " A" and QA_ Codes of " A" or " Q" shall be extracted into the Auxiliary 1 Database. SDC 0002 007 HB 2011B April 21, 2000 10

GR011000075CAt least one sample in an Event ID shall have an EPA_ QA_ Code of " A" and a QA_ Code of " A" or " Q," and at least one analyte in the sample shall have an EPA_ QA_ Code of " A" and a QA_ Code of " A" or " Q."

GR011000080CAny table that includes analytical results shall include a field that flags whether or not an analyte result or sample in the row was associated with a comment.

GR011000090CAny table that includes analytical results shall include a field that flags whether or not an analyte result or sample in the row was associated with a QA_ Code of " Q" from the utility.

GR011000100CA Microsoft ® AccessJ 97 report shall be created to show the percentage of " Q" versus " A" QA_ Codes at the analyte level.

Note: The report shall be issued each time the Auxiliary 1Database is released.

GR011000110CThe Auxiliary 1 Database shall contain all comment data from the utility at the sample and analyte level.

GR011000120CThe Auxiliary 1 Database shall contain all questionable result ( QA_ Codes of " Q") data from the utility at the sample and analyte level.

GR011000130CThe 21 character ICR Utility Identifier shall be used in the Auxiliary 1 Database. The 9 character PWS ID shall only be added to the Utility Design table.

GR012000140CA " 999" shall be entered as the result in the Auxiliary 1 Database for any analytical result that is below the Minimum Reporting Level ( MRL).

GR011000150CThe extractions for the Auxiliary 1 Database shall be cumulative.

GR011000160CThe Sample Event ID is a unique code to identify sampling event in a plant. It consists of the Water Treatment Plant ( WTP) ID ( 2 or 3 digits shown as " I"), Sample Location ID ( 2 digits shown as " L"), and Sample Period ( 2 digits shown as " P" as follows: " III LL PP."

Note: Sample Event IDs for blended plants shall contain a 2 digit WTP ID.

GR011000170CThe Caption property in Microsoft ® AccessJ 97 shall be used to display the unabbreviated column names for the tables in the Auxiliary 1 Database. SDC 0002 007 HB 2011B April 21, 2000 11 Note: EPA shall provide the unabbreviated column names for the tables in the Auxiliary 1 Database to the ICR DMS Team.

GR011000180CThe Auxiliary 1 Database shall only contain validated utility field sample data from the ICR Water Utility Database System.

GR011000190CThe Microsoft ® AccessJ 97 column names for the tables in the Auxiliary 1 Database shall be less than or equal to eight characters.

GR011000200CInitial Sampling Plans ( ISP) shall be represented as Sampling Period " 0."

GR011000210CFinal designs shall be represented as Sampling Period " 19."

2.2 Detailed Requirements for the Auxiliary 1 Database This section defines the requirements in the Auxiliary 1 Database that are related to each functional table. The requirement number and any field names mentioned in the requirement description are italicized. Tables names are also italicized.

2.2.1 Utility Design ( TUXUTDES)

The Utility Design table contains design distribution system information such as disinfectant booster station counts and doses for Chlorine, Chloramine, Chlorine Dioxide, and Hypochlorite. Basic utility information such as PWSID, Utility Name, and EPA Region is also included in the Utility Design table. The Utility Design table contains data for Sampling Periods 0 and 19.

UTDES011000010CThe Utility Design table shall only contain information from the ISPs and the final designs.

UTDES011000019CThe EPA Region shall be a calculated field with Values 1 through 10.

UTDES011000020CThe EPA Region shall be calculated using the first two characters of the TIRPWS_ ID_ NUM, which is a state designation.

UTDES011000030CEPA Region 1 shall represent the following states: Connecticut ( CT), Maine ( ME), Massachusetts ( MA), New Hampshire ( NH), Rhode Island ( RI), and Vermont ( VT).

UTDES011000040CEPA Region 2 shall represent the following states: New Jersey ( NJ), New York ( NY), Puerto Rico ( PR), and Virgin Islands ( VI). SDC 0002 007 HB 2011B April 21, 2000 12

UTDES011000050CEPA Region 3 shall represent the following states: Delaware ( DE), District of Columbia ( DC), Maryland ( MD), Pennsylvania ( PA), Virginia ( VA), and West Virginia ( WV).

UTDES011000060CEPA Region 4 shall represent the following states: Alabama ( AL), Florida ( FL), Georgia ( GA), Kentucky ( KY), Mississippi ( MS), North Carolina ( NC), South Carolina ( SC), and Tennessee ( TN).

UTDES011000070CEPA Region 5 shall represent the following states: Illinois ( IL), Indiana ( IN), Michigan ( MI), Minnesota ( MN), Ohio ( OH), and Wisconsin ( WI).

UTDES011000080CEPA Region 6 shall represent the following states: Arkansas ( AR), Louisiana ( LA), New Mexico ( NM), Oklahoma ( OK), and Texas ( TX).

UTDES011000090CEPA Region 7 shall represent the following states: Iowa ( IA), Kansas ( KS), Missouri ( MO), and Nebraska ( NE).

UTDES011000100CEPA Region 8 shall represent the following states: Colorado ( CO), Montana ( MT), North Dakota ( ND), South Dakota ( SD), Utah ( UT), and Wyoming ( WY).

UTDES011000110CEPA Region 9 shall represent the following states: American Samoa ( AS) , Arizona ( AZ), California ( CA), Guam ( GU), Hawaii ( HI), Nevada ( NV), and North Bay Regional Water Treatment Plant ( IC).

Note: EPA Region 9 contains the state abbreviation " IC" because the TIRPWS_ ID_ NUM for the North Bay Regional Water Treatment Plant is IC0000001960613140909. The North Bay Regional Water Treatment Plant was assigned the prefix " IC" because it did not have an existing PWS ID prior to the ICR.

UTDES011000120CEPA Region 10 shall represent the following states: Alaska ( AK), Idaho ( ID), Oregon ( OR), and Washington ( WA).

Note: Exhibit 4 shows the EPA regions based on state.

Region States 1 CT, ME, MA, NH, RI, VT 2 NJ, NY, PR, VI 3 DE, DC, MD, PA, VA, WV SDC 0002 007 HB 2011B April 21, 2000 13 4 AL, FL, GA, KY, MS, NC, SC, TN 5 IL, IN, MI, MN, OH, WI 6 AR, LA, NM, OK, TX 7 IA, KS, MO, NE 8 CO, MT, ND, SD, UT, WY 9 AS, AZ, CA, GU, HI, NV, IC 10 AK, ID, OR, WA Exhibit 4. EPA Region Assignments Based on State 2.2.2 Utility Monthly ( TUXUTMON)

The Utility Monthly table contains operational distribution system information such as disinfectant booster station counts and doses for Chlorine, Chloramine, Chlorine Dioxide, and Hypochlorite. Monthly utility information such as the start and end date of each sampling period, retail and wholesale population, and retail and wholesale flow rates is also included in the Utility Monthly table. The Utility Monthly table contains data for Sampling Periods 1 through 18.

UTMON011000010COne record in the Utility Monthly table may be associated with one or more records in the Wholesale Information table.

UTMON011000020COne record in the Utility Monthly table shall be associated with one or more records in the Plant Monthly table.

UTMON011000030CThe Utility Monthly table shall contain information for Sampling Periods 1 through 18.

UTMON011000040CTotal Population Served is a calculated field that shall equal the sum of the Retail Population on Day of Sampling and the Wholesale Population on Day of Sampling.

UTMON011000049CThe Population Size Category shall be a calculated field with values 1 through 12.

UTMON011000050CPopulation Size Category is a calculated field that shall equal " 1," if the Total Population Served is greater than or equal to zero and less than or equal to 100. SDC 0002 007 HB 2011B April 21, 2000 14

UTMON011000060CPopulation Size Category is a calculated field that shall equal " 2," if the Total Population Served is greater than or equal to 101 and less than or equal to 500.

UTMON011000070CPopulation Size Category is a calculated field that shall equal " 3," if the Total Population Served is greater than or equal to 501 and less than or equal to 1,000.

UTMON011000080CPopulation Size Category is a calculated field that shall equal " 4," if the Total Population Served is greater than or equal to 1,001 and less than or equal to 2,500.

UTMON011000090CPopulation Size Category is a calculated field that shall equal " 5," if the Total Population Served is greater than or equal to 2,501 and less than or equal to 3,300.

UTMON011000100CPopulation Size Category is a calculated field that shall equal " 6," if the Total Population Served is greater than or equal to 3,301 and less than or equal to 5,000.

UTMON011000110CPopulation Size Category is a calculated field that shall equal " 7," if the Total Population Served is greater than or equal to 5,001 and less than or equal to 10,000.

UTMON011000120CPopulation Size Category is a calculated field that shall equal " 8," if the Total Population Served is greater than or equal to 10,001 and less than or equal to 50,000.

UTMON011000130CPopulation Size Category is a calculated field that shall equal " 9," if the Total Population Served is greater than equal than or equal to 50,001 and less than or equal to 75,000.

UTMON011000140CPopulation Size Category is a calculated field that shall equal " 10," if the Total Population Served is greater than or equal to 75,001 and less than or equal to 100,000.

UTMON011000150CPopulation Size Category is a calculated field that shall equal " 11," if the Total Population Served is greater than or equal to 100,001 and less than or equal to 1,000,000.

UTMON011000160CPopulation Size Category is a calculated field that shall equal " 12," if the Total Population Served is greater than or equal to 1,000,001.

Note: Exhibit 5 shows the population size categories. SDC 0002 007 HB 2011B April 21, 2000 15 Size Category Total Population Minimum Maximum 1 0 100 2 101 500 3 501 1,000 4 1,001 2,500 5 2,501 3,300 6 3,301 5,000 7 5,001 10,000 8 10,001 50,000 9 50,001 75,000 10 75,001 100,000 11 100,001 1,000,000 12 1,000,001 > 1,000,001 Exhibit 5. Population Size Category 2.2.3 Wholesale Information ( TUXWPURC)

The Wholesale Information table contains the name and PWSID of each utility that purchased finished water from a utility in the Utility Monthly table.

WPURC011000010COne or more records in the Wholesale Information table shall be associated with one record in the Utility Monthly table.

WPURC011000020CThe Wholesale Information table shall contain information associated with utilities that purchased water from the utilities in the Utility Monthly table.

WPURC011000030CThe Wholesale Information table shall contain wholesale information for Sampling Periods 1 through 18. SDC 0002 007 HB 2011B April 21, 2000 16 2.2.4 Plant Design ( TUXPLTDES)

The Plant Design table contains basic plant design information such as plant name and category. Clearwell unit process information such as liquid volume, indicator code, and baffling type is also included in the Plant Design table. The Plant Design table contains data for Sampling Periods 0 and 19.

PLTDES011000010COne record in the Plant Design table shall be associated with one or more records in the Process Train Design table.

PLTDES011000020COne record in the Plant Design table shall be associated with one or more records in the Water Resource Design table.

PLTDES011000030CThe Plant Design table shall only contain information from the ISPs and the final designs.

PLTDES011000040C# of Process Trains is a calculated field that shall equal the number of process trains associated with each plant's ISP or final design.

PLTDES011000050CAverage Percent Solids is a calculated field that shall equal the average of the Percent Solids in Sludge for the Sampling Periods available for extraction. SDC 0002 007 HB 2011B April 21, 2000 17 Note: The ICR Federal Database does not contain the Percent Solids in Sludge for plant design data. Percent Solids in Sludge is used in the normalization formulas for Sludge Handling Capacity and Sludge Solids Production. Therefore, Average Percent Solids, a calculated field in the Plant Design table, was created in order to normalize the Sludge Handling Capacity values for ISP and final design data. Average Percent Solids cannot be calculated until table TUXPLTMON is populated with data. Average Percent Solids shall be updated each time the Auxiliary 1 Database is delivered.

PLTDES011000059 C Sludge Handling Capacity shall be normalized to dry tons per day.

PLTDES011000060 C Sludge Handling Capacity shall be normalized from dry pounds per day to dry tons per day by multiplying Sludge Handling Capacity by 0.0005.

PLTDES011000070 C Sludge Handling Capacity shall be normalized from wet pounds per day to dry tons per day by multiplying Sludge Handling Capacity by Average Percent Solids by ( 5 * 10

6).

 PLTDES011000080 C Sludge Handling Capacity shall be normalized from dry tons per day to dry tons per day by multiplying Sludge Handling Capacity by one.

 PLTDES011000090 C Sludge Handling Capacity shall be normalized from wet tons per day to dry tons per day by multiplying Sludge Handling Capacity by Average Percent Solids by 0.01.

 PLTDES011000100 C Sludge Handling Capacity shall be normalized from gallons per day to dry tons per day by multiplying Sludge Handling Capacity by Average Percent Solids by ( 4.2 * 10

5).

 PLTDES011000110CThe clearwell unit process with the greatest liquid volume shall be added to the Plant Design table, if a plant has more than one clearwell unit process.

 PLTDES011000111CThe clearwell unit process with the lowest sequence number shall be added to the Plant Design table, if a plant has more than one clearwell unit process and the liquid volumes are equal.

2.2.5 Water Resource Design ( TUXWRDES)

The Water Resource Design table contains water resource and intake information associated with each plant in the Plant Design table.

 WRDES011000010COne or more records in the Water Resource Design table shall be associated with one record in the Plant Design table. SDC 0002 007 HB 2011B April 21, 2000 18  WRDES011000020CThe Water Resource Design table shall only contain information from the ISPs and the final designs.

 WRDES011000030CIntake Hydrologic Unit Code ( HUC) shall be supplied to the ICR DMS Team by EPA.

2.2.6 Process Train Design ( TUXPTRAIN)

The Process Train Design table contains the unit process name and train type associated with each plant in the Plant Design table.

 PTRAIN011000010COne or more records in the Process Train Design table shall be associated with one record in the Plant Design table.

 PTRAIN011000020CThe Process Train Design table shall only contain information from the ISPs and the final designs.

2.2.7 Plant Monthly ( TUXPLTMON)

The Plant Monthly table contains basic operational plant information for Sampling Periods 1 through 18. Plant information such as plant name, plant category, influent flow, and finished water flow is stored in the Plant Monthly table. Unit process information associated with the plant such as the unit process name, unit process type, and the number of sampled unit process trains is also stored in the Plant Monthly table. In addition, additional water source and washwater return unit process information is stored in the Plant Monthly table. The Plant Monthly table contains a number of calculated fields designed for the analysis of ICR data. A calculated chlorine demand result and associated Event ID is stored in the Plant Monthly table. The chlorine demand Event ID will have a matching Event ID in the Sampling Event table. A matching record must exist in the Sampling Event table in order to retain comment and Quality Assurance ( QA) Code information in Auxiliary 1 Database.

 PLTMON011000010COne record in the Plant Monthly table shall be associated with one or more records in the Unit Process table.

 PLTMON011000020COne record in the Plant Monthly table shall be associated with one or more records in the Sampling Event table.

 PLTMON011000030COne record in the Plant Monthly table may be associated with one record in the Hypochlorite Stock Solution table. SDC 0002 007 HB 2011B April 21, 2000 19  PLTMON011000040COne record in the Plant Monthly table shall be associated with one or more records in the Water Resource Monthly table.

 PLTMON011000050CThe Plant Monthly table shall contain information for Sampling Periods 1 through 18.

 PLTMON011000060CAluminum based Coagulant Dose is a calculated field that shall equal the sum of all normalized chemical feed dosages in the sampled process train where the chemical feed name is " ALUM."

 PLTMON011000070CIron based Coagulant Dose is a calculated field that shall equal the sum of all normalized chemical feed dosages in the sampled process train where the chemical feed name is " IRON."

 PLTMON011000079CCoagulant Type shall be a calculated field.

 PLTMON011000080CCoagulant Type is a calculated field that shall equal " AL," if the Aluminum based Coagulant Dose is greater than zero and the Iron based Coagulant Dose is equal to zero.

 PLTMON011000090CCoagulant Type is a calculated field that shall equal " IRON," if the Iron based Coagulant Dose is greater than zero and the Aluminum based Coagulant Dose is equal to zero.

 PLTMON011000100CCoagulant Type is a calculated field that shall equal " MIX," if the Aluminum based Coagulant Dose and the Iron based Coagulant Dose are greater than zero.

 PLTMON011000110CThe Number of Sampled Process Trains is a calculated field that shall equal the number of process trains sampled during a sampling period for a plant.

Note: The algorithm for this field does not accurately calculate the number of sampled process trains for plants with more than one process train that share a sampled unit process. For example, if a plant has three process trains and only one process train is sampled, the Number of Sampled Process Trains shall equal three if one of the sampled unit processes is shared by the three trains. The calculation works correctly as long as none of the process trains in a plant share a sampled unit process.

 PLTMON011000120CThe Plant Monthly table shall only contain information for the process train that was sampled. SDC 0002 007 HB 2011B April 21, 2000 20  PLTMON011000121CThe process train with the greatest number of samples shall be added to the Plant Monthly table, if more than one process train was sampled.

 PLTMON011000122CThe process train with the greatest flow ( Water Flow into Basin [ MGD]) for the first unit process with a water flow shall be added to the Plant Monthly table, if more than one process train was sampled and the number of samples are equal.

 PLTMON011000123CIf more than one process train was sampled and the number of samples are equal and the flows ( Water Flow into Basin [ MGD]) are equal, the process train shall be sorted in ascending order by process train name. The first process train in the sort shall be added to the Plant Monthly table.

 PLTMON012000129 C Water Resource Category shall be a calculated field that shall equal one of the following values:

 Surface Water ( SW).  Groundwater ( GW).  Purchased Water ( PUR).  Mixed ( MIX).  Null.

 PLTMON011000130 C Water Resource Category is a calculated field that shall equal " SW," if the percent of water flow for surface water resource categories is greater than 80 percent.

Note: The types of surface water resource categories in the ICR Federal Database are listed as follows:

 Flowing Stream ( FS).  Reservoir/ Lake ( RL).  Groundwater under the direct influence of surface water ( GI).  Non Fresh Water ( NF).

 PLTMON011000140 C Water Resource Category is a calculated field that shall equal " GW," if the percent of water flow for groundwater is greater than 80 percent.

Note: GW is the only groundwater resource category in the ICR Federal Database.

 PLTMON011000150 C Water Resource Category is a calculated field that shall equal " PUR," if a plant purchases finished water from another plant. SDC 0002 007 HB 2011B April 21, 2000 21  PLTMON012000155 C Water Resource Category is a calculated field that shall remain null, if all of the intake flows associated the plant are null and/ or equal to zero.

 PLTMON012000160 C Water Resource Category is a calculated field that shall equal " MIX," if it is not null or not assigned to " SW," " GW," or " PUR."

 PLTMON012000169 C Surface Water Resource Category shall be a calculated field that shall equal one of the following values:

 FS ( Flowing Stream).  RL ( Reservoir/ Lake).  GI ( Groundwater under the direct influence of surface water).  NF ( Non Fresh Water).  Mixed ( MIX).  Null.

 PLTMON011000170CSurface Water Resource Category is a calculated field that shall equal " FS," if the Water Resource Category equals " SW" and the percent of water flow for FS is greater than 80 percent.

 PLTMON011000180CSurface Water Resource Category is a calculated field that shall equal " RL," if the Water Resource Category equals " SW" and the percent of water flow for RL is greater than 80 percent.

 PLTMON011000190CSurface Water Resource Category is a calculated field that shall equal " GI," if the Water Resource Category equals " SW" and the percent of water flow for GI is greater than 80 percent.

 PLTMON011000200CSurface Water Resource Category is a calculated field that shall equal " NF," if the Water Resource Category equals " SW" and the percent of water flow for NF is greater than 80 percent.

 PLTMON012000205CSurface Water Resource Category is a calculated field that shall remain null, if the Water Resource Category is not equal to " SW."

 PLTMON012000210CSurface Water Resource Category is a calculated field that shall equal " MIX," if the Water Resource Category equals " SW" and the Surface Water Resource Category is not null or not assigned to " FS," " RL," " GI," or " NF."

 PLTMON011000220 C Sludge Solids Production shall be normalized from dry pounds per day to dry tons per day by multiplying Sludge Solids Production by 0.0005. SDC 0002 007 HB 2011B April 21, 2000 22  PLTMON011000230 C Sludge Solids Production shall be normalized from wet pounds per day to dry tons per day by multiplying Sludge Solids Production by Percent Solids in Sludge by ( 5 * 10

6).

 PLTMON011000240 C Sludge Solids Production shall be normalized from dry tons per day to dry tons per day by multiplying Sludge Solids Production by one.

 PLTMON011000250 C Sludge Solids Production shall be normalized from wet tons per day to dry tons per day by multiplying Sludge Solids Production by Percent Solids in Sludge by 0.01.

 PLTMON011000260 C Sludge Solids Production shall be normalized from gallons per day to dry tons per day by multiplying Sludge Solids Production by Percent Solids in Sludge by ( 4.2 * 10

5).

 PLTMON011000269 C Plant Disinfectant Type is a calculated field that shall equal one of the following values:

 O3.  CLX.  CL2.  CLM.  CL2_ CLM.  Null.

 PLTMON011000270CPlant Disinfectant Type is a calculated field that shall equal " O3," if there is at least one Ozone disinfectant addition with a dose greater than zero.

 PLTMON011000280CPlant Disinfectant Type is a calculated field that shall equal " CLX," if there are no Ozone disinfectant additions with a doses greater than zero and there is at least one Chlorine Dioxide ( CLO2) disinfectant addition with a dose greater than zero.

 PLTMON011000290CPlant Disinfectant Type is a calculated field that shall equal " CL2," if all of the disinfectant addition unit processes with doses greater than zero whose sequence numbers are less than the sequence number associated with the last unit process having a liquid volume greater than zero are equal to any combination of " CL2" or " SOY."

 PLTMON011000291CPlant Disinfectant Type is a calculated field that shall remain null, if the influent flow ( Plant Influent Flow at Time of Sampling [ MGD]) is equal to zero.

 PLTMON011000292CPlant Disinfectant Type is a calculated field that shall remain null, if all of the disinfectant addition unit processes with sequence numbers less than the sequence SDC 0002 007 HB 2011B April 21, 2000 23 number associated with the last unit process having a liquid volume greater than zero have doses equal to zero.

 PLTMON011000293CPlant Disinfectant Type is a calculated field that shall remain null, if the sampled process train does not contain any disinfectant addition unit processes.

 PLTMON011000294CPlant Disinfectant Type is a calculated field that shall remain null, if the sampled process train does not contain a unit process with a volume greater than zero.

 PLTMON011000295CPlant Disinfectant Type is a calculated field that shall remain null, if there are no disinfectant addition unit processes with sequence numbers less than the sequence number associated with the last unit process having a liquid volume greater than zero.

 PLTMON011000300CPlant Disinfectant Type is a calculated field that shall equal " CLM," if neither Ozone nor Chloride Dioxide is used as a disinfectant addition in the plant and the first disinfectant addition unit process contains a dose of Ammonia ( NH3_ N) that is greater than zero.

 PLTMON011000310CPlant Disinfectant Type is a calculated field that shall equal " CLM," if Ozone or Chloride Dioxide is not used as a disinfectant addition in the plant and the second disinfectant addition unit process contains a dose of NH3_ N that is greater than zero and none of the upstream unit processes have a volume greater than zero.

 PLTMON011000320CPlant Disinfectant Type is a calculated field that shall equal " CL2_ CLM," if it is not null or not assigned to " O3," " CLX," " CL2," or " CLM."

 PLTMON011000329CDistribution System Disinfectant Type is a calculated field that shall equal one of the following values:

 CLM.  CL2.  Null.

 PLTMON011000330CDistribution System Disinfectant Type is a calculated field that shall equal " CLM," if the plant has at least one disinfection unit process using NH3_ N with a dose greater than zero.

 PLTMON011000340CDistribution System Disinfectant Type is a calculated field that shall equal " CL2," if the plant has no disinfection unit processes using NH3_ N and has at least one disinfectant addition unit process with a dose greater than zero.

 PLTMON011000349CChlorine Demand shall be a calculated field. SDC 0002 007 HB 2011B April 21, 2000 24  PLTMON011000350CChlorine Demand is a calculated field that shall equal Chlorine Dose  Chlorine Residual, if Chlorine Residual >= 0.2 mg/ L and <= 1.5 mg/ L and the Contact Time is <= 10 minutes.

 PLTMON011000351CChlorine Demand field shall remain null, if Chlorine Residual < 0.2 mg/ L and > 1.5 mg/ L and the Contact Time is > 10 minutes.

 PLTMON011000360CThe Chlorine Demand sample that is at the influent or closest to the influent shall be entered in the Plant Monthly table, if there is more than one Chlorine Demand sample taken per month.

 PLTMON011000370CThe washwater return with the greatest water flow ( WWR Flow at Sample Time [ MGD]) shall be contained in the Plant Monthly table, if there is more than one washwater return unit process in a process train.

 PLTMON011000371CThe washwater unit process with the lowest sequence number shall be added to the Plant Monthly table, if there is more than one washwater return unit process and the water flows ( WWR Flow at Sample Time [ MGD]) are equal.

 PLTMON011000379CWWR Treatment Type is a calculated field that shall equal one of the following values:

 NONE.  SETTLE.  FILT.  CONV.  DIS.  CONV_ DIS.  OTHER.

 PLTMON011000380CWWR Treatment Type is a calculated field that shall equal " NONE," if WWR Plain Sedimentation, WWR Coagulation/ Sedimentation, WWR Filtration, and WWR Disinfection are equal to " N" and WWR Other Treatment is null.

 PLTMON011000390CWWR Treatment Type is a calculated field that shall equal " SETTLE," if WWR Plain Sedimentation or WWR Coagulation/ Sedimentation are equal to " Y."

 PLTMON011000400CWWR Treatment Type is a calculated field that shall equal " FILT," if only WWR Filtration is equal to " Y." SDC 0002 007 HB 2011B April 21, 2000 25  PLTMON011000410CWWR Treatment Type is a calculated field that shall equal " CONV," if only WWR Filtration and WWR Coagulation/ Sedimentation are equal to " Y."

 PLTMON011000420CWWR Treatment Type is a calculated field that shall equal " DIS," if only WWR Disinfection is equal to " Y."

 PLTMON011000430CWWR Treatment Type is a calculated field that shall equal " CONV_ DIS," if only WWR Filtration, WWR Coagulation/ Sedimentation, and WWR Disinfection are equal to " Y."

 PLTMON011000440CWWR Treatment Type is a calculated field that shall equal " OTHER," if it is not assigned to " NONE," " SETTLE," " FILT," " CONV," " DIS," or " CONV_ DIS."

 PLTMON011000450CThe additional water resource with the greatest water flow ( Additional Water Source Flow at Time of Sampling [ MGD]) shall be contained in the Plant Monthly table, if there is more than one additional water resource unit process in a process train.

 PLTMON011000451CThe unit process with the lowest sequence number shall be added to the Plant Monthly table, if there is more than one additional water resource unit process and the water flows ( Additional Water Flow at Time of Sampling [ MGD]) are equal.

 PLTMON011000452CAWSDAYQ shall represent the flow for the additional water resource sampling point ( ASP).

 PLTMON011000453CThe ASP that corresponds with the additional water resource unit process shall be added to the Plant Monthly table. The ASP that corresponds with the additional water resource unit process is the first ASP located after the additional water resource unit process.

 PLTMON011000460COverall Plant Baffling Factor for one plant during a given sampling period shall equal the sum of all the T10 values divided by the sum of all the T50 values.

Overall Plant Baffling Factor = Sum of T10 ( for all unit processes)

Sum of T50 ( for all unit processes)

 PLTMON012000335CDistribution System Disinfectant Type is a calculated field that shall equal " CLM," if the plant does not contain a sampled unit process train and has an Event ID at the finished water location where Total Chlorine was sampled. SDC 0002 007 HB 2011B April 21, 2000 26  PLTMON012000345CDistribution System Disinfectant Type is a calculated field that shall equal " CL2," if the plant does not contain a sampled unit process train and has an Event ID at the finished water location where Free Chlorine or Free Chlorine and Total Chlorine were sampled.

2.2.8 Water Resource Monthly ( TUXWRMON)

The Water Resource Monthly table contains water resource and intake information associated with each plant in the Plant Monthly table.

 WRMON011000010COne or more records in the Water Resource Monthly table shall be associated with one record in the Plant Monthly table.

2.2.9 Hypochlorite Stock Solution ( TUXHYPO)

The Hypochlorite Stock Solution table contains the sample analytical results associated with samples taken from the hypochlorite stock solution. The Hypochlorite Stock Solution table is related to the Plant Monthly table. The Sampling Event table does not contain Event IDs for hypochlorite samples. Therefore, no comment text or QA Code information exists in Auxiliary 1 Database for hypochlorite samples.

 HYPO011000010COne record in the Hypochlorite Stock Solution table shall be associated with one record in the Plant Monthly table.

2.2.10 Sampling Event ( TUXSAMPLE)

The Sampling Event table contains information associated with sample events such as Sample Location ID, Sample Event Sequence Number, Sample Event Name, and Sample Event Type. A sampling event represents all of the samples taken at a certain location within a plant for a specific sampling period. The Sampling Event table does not contain Event IDs for hypochlorite samples. The Sample Event ID shall be comprised of the Plant ID, Sample Location ID, and Sampling Period. The confidence level and contact time from effluent are also included for sample events associated with distribution system locations. Each record in the Sampling Event table contains a match in at least one of the analytical result tables, except for records associated with chlorine demand samples.

 SAMPLE011000010COne or more records in the Sampling Event table shall be associated with one record in the Plant Monthly table.

 SAMPLE011000020COne record in the Sampling Event table shall be associated with one or more records in the Sample Event/ Sample ID Associative ( SESA) table. SDC 0002 007 HB 2011B April 21, 2000 27  SAMPLE011000030COne record in the Sampling Event table may be associated with one record in the Microbiology table.

 SAMPLE011000040COne record in the Sampling Event table may be associated with one record in the Water Quality Parameters ( WQP) table.

 SAMPLE011000050COne record in the Sampling Event table may be associated with one record in the Disinfectant Residuals table.

 SAMPLE011000060COne record in the Sampling Event table may be associated with one record in the Disinfection By Products ( DBP) table.

 SAMPLE011000070COne record in the Sampling Event table may be associated with one record in the Cyanogen Chloride table.

 SAMPLE011000080COne record in the Sampling Event table may be associated with one record in the Chlorite/ Chlorate table.

 SAMPLE011000090COne record in the Sampling Event table may be associated with one record in the Chlorine Dioxide/ Ozone Disinfection By Products ( DBP) table.

 SAMPLE011000100COne record in the Sampling Event table may be associated with one record in the Assimilable Organic Carbon/ Biodegradable Organic Carbon ( AOC/ BDOC) table.

 SAMPLE011000110COne record in the Sampling Event table shall be associated with one record in the Unit Process table.

SAMPLE011000119CThe Sample Quarter field shall be a calculated field with Values 1 through 6.

 SAMPLE011000120CSample Quarter 1 shall represent Sampling Periods 1 through 3.

 SAMPLE011000130CSample Quarter 2 shall represent Sampling Periods 4 through 6.

 SAMPLE011000140CSample Quarter 3 shall represent Sampling Periods 7 through 9.

 SAMPLE011000150CSample Quarter 4 shall represent Sampling Periods 10 through 12.

 SAMPLE011000160CSample Quarter 5 shall represent Sampling Periods 13 through 15.

 SAMPLE011000170CSample Quarter 6 shall represent Sampling Periods 16 through 18. SDC 0002 007 HB 2011B April 21, 2000 28  SAMPLE011000179CThe Sample Location ID field shall be derived.

 SAMPLE011000180CSample Location ID shall be derived from the TIRUNPRO table in the ICR Federal Database, if the samples are taken from the unit process train.

 SAMPLE011000190CSample Location ID shall be derived from the TIRWFSP table in the ICR Federal Database, if the samples are taken from the influent.

 SAMPLE011000200CSample Location ID shall be derived from the TIRWFSP table in the ICR Federal Database, if the samples are taken from the distribution system.

 SAMPLE011000210CSample Location ID shall be derived from the TIRWFSP table in the ICR Federal Database, if the samples are taken from finished water.

 SAMPLE011000219CThe Sample Event Sequence Number field shall be derived.

 SAMPLE011000220CSample Event Sequence Number shall be derived from the TIRPTA table in the ICR Federal Database, if the samples are taken from the unit process train.

 SAMPLE011000230CSample Event Sequence Number for blended and non blended plants shall be calculated if the samples are taken from the influent, distribution system, or finished water.

 SAMPLE011000231 C Sample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from the influent ( i. e., Sample Location = Influent), as: Sample Event Sequence Number = 0.

 SAMPLE011000232 C Sample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from the finished water ( i. e., Sample Location = Finished Water), as: Sample Event Sequence Number = Maximum Unit Process Sequence Number + 1.

 SAMPLE011000233 C Sample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from the Distribution System Equivalent system location ( i. e., Sample Location = DSE) as: Sample Event Sequence Number = Finished Water Sample Event Sequence Number + 1.

 SAMPLE011000234 C Sample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from the first average detention time distribution system sample location ( i. e., Sample Location = AVG1), as: Sample Event Sequence Number = Finished Water Sequence Number + 2. SDC 0002 007 HB 2011B April 21, 2000 29  SAMPLE011000235CSample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from the second average detention time distribution system sample location ( i. e., Sample Location = AVG2), as: Sample Event Sequence Number = Finished Water Sequence Number + 3.

 SAMPLE011000236CSample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from the maximum detention time distribution system sample location ( i. e., Sample Location = MAX), as: Sample Event Sequence Number = Finished Water Sequence Number + 4.

Note: Exhibit 6 is a reference to determine the Sample Event Sequence Number for non blended plants.

Sample Location Sample Event Sequence Number Influent 0 Through the Plant Associated with the Unit Process Sequence Number Finished Water ( FW) Maximum Unit Process Sequence Number + 1 Distribution System Equivalent ( DSE) Samples Finished Water Sample Sequence Number + 1 Average Detention Time ( AVG1) Finished Water Sample Sequence Number + 2 Average Detention Time ( AVG2) Finished Water Sample Sequence Number + 3 Maximum Detention Time ( MAX) Finished Water Sample Sequence Number + 4 Simulated Distribution System ( SDS) Samples Finished Water Sample Sequence Number + 5 Near First Customer ( NFC) Finished Water Sample Sequence Number + 6 Exhibit 6. Calculations for the Sample Event Sequence Number for Non Blended Plants  SAMPLE011000237CSample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from a simulated distribution system sample location ( i. e., Sample Location = SDS), as: Sample Event Sequence Number = Finished Water Sequence Number + 5.

 SAMPLE011000238CSample Event Sequence Number shall be calculated for non blended plants, if the samples are taken from the Near First Customer distribution system sample SDC 0002 007 HB 2011B April 21, 2000 30 location ( i. e., Sample Location = NFC), as: Sample Event Sequence Number = Finished Water Sequence Number + 6.

 SAMPLE011000239 C Sample Event Sequence Number shall be calculated for blended plants, if the samples are taken from the Entry Point to the Distribution System sample location ( i. e., Sample Location = ENT), as: Sample Event Sequence Number = 200.

Note: If there is no Entry Point to the Distribution System sample, the next sample location will have a Sample Event Sequence Number = 201.

 SAMPLE011000240CThe two AVG distribution samples with the lowest sample sequence numbers shall be added to the Sampling Event table, if a non blending plant has more than two AVG distribution samples per sampling period.

 SAMPLE011000241 C Sample Event Sequence Number shall be calculated for blended plants, if the samples are taken from the Distribution System Equivalent sample location ( i. e., Sample Location = DSE), as: Sample Event Sequence Number = ENT + 1 through ENT + (# of plants).

 SAMPLE011000242 C Sample Event Sequence Number shall be calculated for blended plants, if the samples are taken from the average detention time distribution system sample location ( i. e., Sample Location = AVG1 or AVG2), as: Sample Event Sequence Number = Max DSE + 1 through Max DSE + ( 2 * [# of plants]).

 SAMPLE011000243 C Sample Event Sequence Number shall be calculated for blended plants, if the samples are taken from the maximum detention time distribution system sample location ( i. e., Sample Location = MAX), as: Sample Event Sequence Number = Max AVG + 1 through Max AVG + (# of plants).

 SAMPLE011000244CSample Event Sequence Number shall be calculated for blended plants, if the samples are taken from a Simulated Distribution System distribution system sample location ( i. e., Sample Location = SDS), as: Sample Event Sequence Number = Max MAX + 1 through Max MAX + (# of plants).

 SAMPLE011000245 C Sample Event Sequence Number shall be calculated for blended plants, if the samples are taken from the Near First Customer distribution system sample location ( i. e., Sample Location = NFC), as: Sample Event Sequence Number = Max SDS + 1 through Max SDS + (# of plants).

 SAMPLE011000246CThe number of AVG distribution samples added to the Sampling Event table shall equal two times the number of plants that are blending water in a blended distribution system. SDC 0002 007 HB 2011B April 21, 2000 31  SAMPLE011000247CThe AVG distribution samples with the lowest sample sequence numbers shall be added to the Sampling Event table, if a plant in a blended distribution system has more than two AVG distribution samples per plant.

Note: Exhibit 7 is a reference to determine the Sample Event Sequence Number for blended plants.

Note: If any of the samples in Exhibit 7 are missing, the next sample will be numbered sequentially. The only exception is Entry Point to the Distribution System samples. If the Entry Point to the Distribution System sample is missing, Sample Event Sequence Number 200 will be skipped and the next sample location will have a Sample Event Sequence Number = 201.

Sample Location Sample Event Sequence Number Entry Point to the Distribution System ( ENT) 200 Distribution System Equivalent ( DSE) Samples ENT+ 1 through ENT + (# of plants)

Average Detention Time ( AVG) Max DSE + 1 through Max DSE + 2 * (# of plants)

Maximum Detention Time ( MAX) Max AVG + 1 through Max AVG + (# of plants)

Simulated Distribution System ( SDS) Samples Max MAX + 1 through Max MAX + (# of plants)

Near First Customer ( NFC) Max SDS + 1 through Max SDS + (# of plants)

Exhibit 7. Calculations for the Sample Event Sequence Number for Blended Plants  SAMPLE011000249CSample Event Name shall be derived based upon the Sample Location Type.

 SAMPLE011000250CSample Event Name shall represent the unit process name, if the Sample Event is associated with a unit process.

 SAMPLE011000260CSample Event Name shall contain " INFLUENT," if the Sample Event is associated with the influent.

 SAMPLE011000270CSample Event Name shall contain " FINISH," if the Sample Event is associated with finished water. SDC 0002 007 HB 2011B April 21, 2000 32  SAMPLE011000280CSample Event Name shall contain the name of the distribution system sample location type, if the Sample Event is associated with the distribution system.

 SAMPLE011000281CDistribution system sample location types shall be equal to one of the following values:

 ENT = Entry Point to the Distribution System.  DSE = Distribution System Equivalent Samples.  AVG1 = First Average Detention Time Sample.  AVG2 = Second Average Detention Time Sample.  MAX = Maximum Detention Time.  SDS = Simulated Distribution System.  NFC = Near First Customer.

 SAMPLE011000290CThe Sample Event Type shall represent the unit process type, if the Sample Event is associated with a unit process.

 SAMPLE011000300CThe Sample Event Type shall contain " INFLUENT," if the Sample Event is associated with the influent.

 SAMPLE011000310CThe Sample Event Type shall contain " FINISH," if the Sample Event is associated with finished water.

 SAMPLE011000320CThe Sample Event Type shall contain the name of the distribution system sample location type for the following types of distribution samples: ENT, DSE, MAX, SDS, and NFC.

 SAMPLE011000321CThe Confidence Level shall only be populated for the following types of distribution system sample locations: DSE, AVG, MAX, and NFC. These fields will be null for all other types of sample locations.

 SAMPLE011000322CThe Contact Time from Effluent shall only be populated for the following types of distribution system sample locations: DSE, AVG1, AVG2, MAX, SDS, and NFC.

 SAMPLE011000323CThe Sample Event Type shall equal " AVG" for AVG distribution system samples that belong to blended distribution systems.

 SAMPLE011000324CThe Sample Event Type shall equal " AVG1" for the AVG distribution system sample with the lowest sequence number in a non blending plant. SDC 0002 007 HB 2011B April 21, 2000 33  SAMPLE011000325CThe Sample Event Type shall equal " AVG2" for the AVG distribution system sample with the second lowest sequence number in a non blending plant.

2.2.11 Sample Event/ Sample ID Associative ( SESA) ( TUXSESA)

The Sample Event/ Sample ID Associative ( SESA) table contains a record for each Event ID in the Sampling Event table. The corresponding Sample ID in the ICR Federal Database is stored for each Event ID. If one Event ID is associated with more than one Sample ID, there will be multiple records for the Event ID in the Sample Event/ Sample ID Associative ( SESA) table.

 SESA011000010COne or more records in the Sample Event/ Sample ID Associative ( SESA) table shall be associated with one record in the Sampling Event table.

 SESA011000020COne record in the Sample Event/ Sample ID Associative ( SESA) table may be associated with one record in the Sample Comments table.

 SESA011000030 C One record in the Sample Event/ Sample ID Associative ( SESA) table may be associated with one or more records in the Analyte Comments table.

 SESA011000040CThe Sample Event/ Sample ID Associative ( SESA) table shall contain a record for every ICR Federal Database Sample ID associated with each Sample Event ID in the Sampling Event table. SDC 0002 007 HB 2011B April 21, 2000 34 2.2.12 Sample Comments ( TUXSCMMT)

The Sample Comments table contains the comment text and QA Code associated with each Sample ID in the Sample Event/ Sample ID Associative ( SESA) table. If a Sample ID has no comment text and the QA Code is not equal to " Q," a record will not be created in the Sample Comments table.

 SCMMT011000010COne record in the Sample Comments table shall be associated with one record in the Sample Event/ Sample ID Associative ( SESA) table.

 SCMMT011000020CA record shall be created in the Sample Comments table, if a Sample ID in the Sample Event/ Sample ID Associative ( SESA) table is associated with a comment.

2.2.13 Analyte Comments ( TUXACMMT)

The Analyte Comments table contains the comment text and QA Code associated with each analyte associated with a Sample ID in the Sample Event/ Sample ID Associative ( SESA) table. If an analyte has no comment text and the QA Code is not equal to " Q," a record will not be created for that analyte in the Analyte Comments table.

 ACMMT011000010COne or more records in the Analyte Comments table shall be associated with one record in the Sample Event/ Sample ID Associative ( SESA) table.

 ACMMT011000020CA record shall be created in the Analyte Comments table if an analyte belonging to a Sample ID in the Sample Event/ Sample ID Associative ( SESA) table is associated with a comment.

2.2.14 Microbiology ( TUXMICRO)

The Microbiology table contains the sample analytical results associated with virus, coliform, and protozoan samples.

 MICRO011000010COne record in the Microbiology table shall be associated with one record in the Sampling Event table.

 MICRO012000020CA " 999" shall be entered as the result for any microbial analytical result that is below the detection limit.

 MICRO012000030CPROTO_ DL shall equal the numeric result in the first result field for either a Giardia or Cryptosporidium analyte that is below the detection limit. SDC 0002 007 HB 2011B April 21, 2000 35  MICRO013000040C PROTO_ DL shall be equal to the inverse of the Volume Assayed ([ 1/ Volume Assayed] * 100), if all of the Giardia and Cryptosporidium analytes are above the detection limit.

 MICRO012000050CTCOLIBDL, FCOLIBDL, and ECOLIBDL shall remain null, if the analytical result is above the detection limit.

 MICRO012000060CTCOLIBDL, FCOLIBDL, and ECOLIBDL shall contain the value of the analytical result, if the result is below the detection limit.

 MICRO012000070CVIR_ BDL shall contain the value of the analytical result, if the result is below the detection limit.

 MICRO013000080CVIR_ BDL shall be equal to the inverse of the Volume Assayed ([ 1/ Volume Assayed] * 100), if the result is above the detection limit.

2.2.15 Water Quality Parameters ( WQP) ( TUXWQP)

The Water Quality Parameters ( WQP) table contains the sample analytical results associated with WQP, Total Organic Halides ( TOX), Total Organic Carbon ( TOC), Bromide, Ammonia, and Ultraviolet Absorbance ( UV 254) samples.

 WQP011000010COne record in the Water Quality Parameters ( WQP) table shall be associated with one record in the Sampling Event table.

 WQP011000020CThe average of the initial and duplicate results shall be entered as the result in the Auxiliary 1 Database for TOC.

 WQP011000021CThe result for TOC shall be null, if one of the initial or duplicate TOC results is missing from the Auxiliary 1 Database.

 WQP011000030CThe MRL shall be used to calculate the average, if one or both of the results for an initial or duplicate TOC sample is below the MRL.

 WQP011000040CA " 999" shall be inserted as the result in the Auxiliary 1 Database, if at least one of the TOC results is below the MRL and the average of the initial and duplicate results for the TOC samples is equal to the MRL.

 WQP011000050CThe average of the initial and duplicate results shall be entered as the result in the Auxiliary 1 Database for TOX.

 WQP011000060CThe result for TOX shall be null, if one of the initial or duplicate TOX results is missing from the Auxiliary 1 Database. SDC 0002 007 HB 2011B April 21, 2000 36  WQP011000070CThe MRL shall be used to calculate the average, if one or both of the results for an initial or duplicate TOX sample is below the MRL.

 WQP011000080CA " 999" shall be inserted as the result in the Auxiliary 1 Database Auxiliary Database, if at least one of the TOX results is below the MRL and the average of the initial and duplicate results for the TOX samples is equal to the MRL.

 WQP011000090CThe average of the initial and duplicate results shall be entered as the result in the Auxiliary 1 Database for UV 254.

 WQP011000091CThe result for UV 254 shall be null, if one of the initial or duplicate UV 254 results is missing from the Auxiliary 1 Database.

 WQP011000100CThe MRL shall be used to calculate the average, if one or both of the results for an initial or duplicate UV 254 sample is below the MRL.

 WQP011000110CA " 999" shall be inserted as the result in the Auxiliary 1 Database, if at least one of the UV 254 results is below the MRL and the average of the initial and duplicate results for the UV 254 samples is equal to the MRL.

 WQP011000120CTOX Relative Percent Difference ( RPD) (  g Cl / L) is a calculated field that shall equal ( TOX difference/ TOX average) * 100.

 WQP011000121CTOX difference shall equal the initial TOX result minus the duplicate TOX result.

 WQP011000122CTOX average shall equal the initial TOX result plus the duplicate TOX result divided by two.

 WQP011000123 C The MRL for TOX shall be used in the TOX Relative Percent Difference ( RPD) calculations, if the initial or duplicate result is below the MRL.

 WQP011000124CTOX Relative Percent Difference ( RPD) shall only be calculated if the Auxiliary 1 Database contains both the initial and duplicate TOX results.

 WQP011000130CThe ICR DMS Team shall insert a " 333" in Ammonia, if the result value is 0.1 and there is a comment associated with the analytical result or the result value is zero. In all other cases, the result value shall be used.

 WQP011000140CExpert Result for Ammonia shall equal Ammonia prior to expert intervention. SDC 0002 007 HB 2011B April 21, 2000 37  WQP011000150CThe ICR DMS Team shall create a Microsoft ® AccessJ 97 table for Ammonia results of  333 with a minimum of three fields: Result, Comment, and Expert Result for Ammonia.

Note: Requirements WQP011000130 through WQP011000150 and DISFRES011000020 through DISFRES011000130 were created because the Water Utility Database System does not allow users to enter values less than 0.1 for Ammonia, Ozone Residual, Chlorine Dioxide Residual, Free Chlorine Residual, or Total Chlorine Residual. However, it is possible to have results less than 0.1. In addition, the Water Utility Database System allows users to enter zero for values for Ammonia, Ozone Residual, Chlorine Dioxide Residual, Free Chlorine Residual, or Total Chlorine Residual. Zero results are not valid for the five analytes.

In order to mitigate this problem, EPA Technical Support Center ( TSC) instructed users to insert a " 0.1" in the result field, set the QA_ Code to " Q," and add the numeric value result in the comment field, if the result was below 0.1. In many cases, the users did not enter the numeric value result in the comment field. Instead, the users entered text such as " less than 0.1" or "< .9" or " The result is 0.45." In addition, users entered a QA_ Code of " A" and added the numeric value result in the comment field, if the actual result was below 0.1. This, combined with the fact that users entered result values of zero, makes it difficult to extract the actual result. Therefore, it was decided that an expert must examine each record that has a result value of 0.1 and a comment or a result value of zero to determine an Expert Result value for each of the five analytes.

If one of the five analytes has a result value of 0.1 and a comment or a result value of zero, a  333 will be inserted as the result. Five expert result tables, one for each analyte, will be created and delivered to an analyst who shall examine the comment fields to determine if there is a more appropriate value. If there is a valid value for the analyte, then the expert will update the Expert Result field in the expert result table. The expert shall insert a " 999" in the Expert Result field for all records that contain a " 333" and a less than value in the comments field. Once the analyst determines the most accurate results for the five analytes and edits the Expert Result field, the tables shall be sent back to the ICR DMS Team. The data in the edited Expert Result fields for all five analytes shall be inserted into the appropriate fields in the Water Quality Parameters ( WQP) table and the Disinfectant Residuals table. SDC 0002 007 HB 2011B April 21, 2000 38 2.2.16 Disinfectant Residuals ( TUXDISFRES)

The Disinfectant Residuals table contains the sample analytical results associated with Chlorine, Chlorine Dioxide, and Ozone samples.

 DISFRES011000010COne record in the Disinfectant Residuals table shall be associated with one record in the Sampling Event table.

 DISFRES011000020CThe ICR DMS Team shall insert a " 333" in Free Chlorine Residual, if the result value is 0.1 and there is a comment associated with the analytical result or the result value is zero. In all other cases, the result value shall be used.

 DISFRES011000030CExpert Result for Free Chlorine Residual shall equal Free Chlorine Residual prior to expert intervention.

 DISFRES011000040CThe ICR DMS Team shall create a Microsoft ® AccessJ 97 table for Free Chlorine Residual results of " 333" with a minimum of three fields: Result, Comment, and Expert Result for Free Chlorine Residual.

 DISFRES011000050CThe ICR DMS Team shall insert a " 333" in Total Chlorine Residual, if the result value is 0.1 and there is a comment associated with the analytical result or the result value is zero. In all other cases, the result value shall be used.

 DISFRES011000060CExpert Result for Total Chlorine Residual shall equal Total Chlorine Residual prior to expert intervention.

 DISFRES011000070CThe ICR DMS Team shall create a Microsoft ® AccessJ 97 table for Total Chlorine Residual results of " 333" with a minimum of three fields: Result, Comment, and Expert Result for Total Chlorine Residual.

 DISFRES011000080CThe ICR DMS Team shall insert a " 333" in Chlorine Dioxide Residual, if the result value is 0.1 and there is a comment associated with the analytical result or the result value is zero. In all other cases, the result value shall be used.

 DISFRES011000090CExpert Result for Chlorine Dioxide Residual shall equal Chlorine Dioxide Residual prior to expert intervention.

 DISFRES011000100CThe ICR DMS Team shall create a Microsoft ® AccessJ 97 table for Chlorine Dioxide Residual results of " 333" with a minimum of three fields: Result, Comment, and Expert Result for Chlorine Dioxide Residual. SDC 0002 007 HB 2011B April 21, 2000 39  DISFRES011000110CThe ICR DMS Team shall insert a " 333" in the result field for Ozone Residual, if the result value is 0.1 and there is a comment associated with the analytical result or the result value is zero. In all other cases, the result value shall be used.

 DISFRES011000120CExpert Result for Ozone Residual shall equal Ozone Residual prior to expert intervention.

 DISFRES011000130CThe ICR DMS Team shall create a Microsoft ® AccessJ 97 table for Ozone Residual results of " 333" with a minimum of three fields: Result, Comment, and Expert Result for Ozone Residual.

2.2.17 Disinfection By Products ( DBP) ( TUXDBP)

The Disinfection By Products ( DBP) table contains the sample analytical results associated with the following analyte groups:

 Trihalomethanes ( THM).  Haloacetic Acids ( HAA).  Haloacetonitriles ( HAN).  Chloral Hydrate ( CH).  Chloropicrin ( CP).  Haloketones ( HK).

 DBP011000010COne record in the Disinfection By Products ( DBP) shall be associated with one record in the Sampling Event table.

 DBP011000020CTotal THM4 is a calculated field that shall represent the sum of CHCL3 + BDCM + DBCM + CHBR3.

 DBP011000030CTotal THM4 shall not be calculated if any of the four analytes ( CHCL3 + BDCM + DBCM + CHBR3) are missing a numeric value for reasons other than the less than MRL criteria.

 DBP011000040CA zero value shall be used to replace any analyte that is less than the MRL when calculating Total THM4.

 DBP011000041CA " 999" shall be the result of Total THM4, if all of the analytes are less than the MRL.

 DBP011000050CTotal HAA5 is a calculated field that shall represent the sum of MCAA + DCAA + TCAA + MBAA + DBAA.

 DBP011000060CTotal HAA5 shall not be calculated if any of the five analytes SDC 0002 007 HB 2011B April 21, 2000 40 ( MCAA + DCAA + TCAA + MBAA + DBAA) are missing a numeric value for reasons other than the less than MRL criteria.

 DBP011000070CA zero value shall be used to replace any analyte that is less than the MRL when calculating Total HAA5.

 DBP011000071CA " 999" shall be the result of Total HAA5, if all of the analytes are less than the MRL.

 DBP011000080CTotal HAA6 is a calculated field that shall represent the sum of MCAA + DCAA + TCAA + MBAA + DBAA + BCAA.

 DBP011000090CTotal HAA6 shall not be calculated if any of the six analytes ( MCAA + DCAA + TCAA + MBAA + DBAA + BCAA) are missing a numeric value for reasons other than the less than MRL criteria.

 DBP011000100CA zero value shall be used to replace any analyte that is less than the MRL when calculating Total HAA6.

 DBP011000101CA " 999" shall be the result of Total HAA6, if all of the analytes are less than the MRL.

 DBP0110000110CTotal HAA9 is a calculated field that shall equal the sum of MCAA + DCAA + TCAA + MBAA + DBAA + BCAA + BDCAA + CDBAA + TBAA.

 DBP011000120CTotal HAA9 shall not be calculated if any of the nine analytes ( MCAA + DCAA + TCAA + MBAA + DBAA + BCAA + BDCAA + CDBAA + TBAA) are missing a numeric value for reasons other than the less than MRL criteria.

 DBP011000130CA zero value shall be used to replace any analyte that is less than the MRL when calculating Total HAA9.

 DBP011000131CA " 999" shall be the result of Total HAA9, if all of the analytes are less than the MRL.

 DBP011000140CTotal HAN4 is a calculated field that shall equal the sum of DCAN + TCAN + BCAN + DBAN.

 DBP011000150CTotal HAN4 shall not be calculated if any of the four analytes ( DCAN + TCAN + BCAN + DBAN) are missing a numeric value for reasons other than the less than MRL criteria. SDC 0002 007 HB 2011B April 21, 2000 41  DBP011000160CA zero value shall be used to replace any analyte that is less than the MRL when calculating Total HAN4.

 DBP011000161CA " 999" shall be the result of Total HAN4, if all of the analytes are less than the MRL.

Note: The full names of the analytes used to calculate Total THM4, Total HAA5, Total HAA6, Total HAA9, and Total HAN4 are included in Appendix C. DBP samples are taken on a quarterly basis; therefore, Total THM4, Total HAA5, Total HAA6, Total HAA9, and Total HAN4 shall be calculated on a quarterly basis. The calculations of Total THM4, Total HAA5, Total HAA6, Total HAA9, and Total HAN4 shall be based on the assumption that all of the quarterly samples are taken at the same sample event.

2.2.18 Cyanogen Chloride ( TUXCNCL)

The Cyanogen Chloride table contains the sample analytical results associated with Cyanogen Chloride samples.

 CNCL011000010COne record in the Cyanogen Chloride table shall be associated with one record in the Sampling Event table.

2.2.19 Chlorite/ Chlorate ( TUXCHLORS)

The Chlorite/ Chlorate table contains the Chlorite and Chlorate analytical results associated with DBP ion samples.

 CHLORS011000010COne record in the Chlorate/ Chlorite table shall be associated with one record in the Sampling Event table.

2.2.20 Chlorine Dioxide/ Ozone Disinfection By Products ( DBP) ( TUXCLDIOX)

The Chlorine Dioxide/ Ozone Disinfection By Products ( DBP) table contains the sample analytical results associated with Low Level Bromate and Aldehyde samples. It also contains Bromate analytical results associated with DBP ion samples. Only the required analytes for Aldehyde samples are included in the Chlorine Dioxide/ Ozone Disinfection By Products ( DBP) table.

 CLDIOX011000010COne record in the Chlorine Dioxide/ Ozone Disinfection By Products ( DBP) table shall be associated with one record in the Sampling Event table. SDC 0002 007 HB 2011B April 21, 2000 42 2.2.21 Assimilable Organic Carbon/ Biodegradable Organic Carbon ( AOC/ BDOC) ( TUXABDOC)

The Assimilable Organic Carbon/ Biodegradable Organic Carbon ( AOC/ BDOC) table contains the analytical results associated with AOC and BDOC samples.

 ABDOC011000010COne record in the Assimilable Organic Carbon/ Biodegradable Organic Carbon ( AOC/ BDOC) table shall be associated with one record in the Sampling Event table.

2.2.22 Unit Process ( TUXUNPRO)

The Unit Process table contains information associated with each unit process such as unit process name, type, and identification number. Media type, media depth, and Granular Activated Carbon ( GAC) are included for filtration unit processes. The normalized dose rate and normalized disinfectant name are included for disinfectant addition unit processes. The Event ID will be populated in the Unit Process table for all unit process associated with an Event ID in the Sampling Event table.

 UNPRO011000010COne or more records in the Unit Process table shall be associated with one record in the Plant Monthly table.

 UNPRO011000020COne record in the Unit Process table may be associated with one record in the Ozone Chamber table.

 UNPRO011000030COne record in the Unit Process table may be associated with one record in the Sample Event table.

 UNPRO011000031CThe Sample Event ID shall be null, if the unit process is not associated with a sample.

 UNPRO011000040COne record in the Unit Process table may be associated with one or more records in the Chemical Feeds table.

 UNPRO011000050CFeeder Chemicals shall not appear in the Unit Process table.

 UNPRO011000060COnly unit processes from the process train that is sampled shall be included in the Unit Process table.

 UNPRO011000070CFilter Surface Area shall only contain data for filtration unit processes.

 UNPRO011000080CFilter Media Type shall only contain data for filtration unit processes. SDC 0002 007 HB 2011B April 21, 2000 43  UNPRO011000090CFilter Media Depth shall only contain data for filtration unit processes.

 UNPRO011000100CFilter Granular Activated Carbon ( GAC) Depth shall only contain data for filtration unit processes.

 UNPRO011000110CDisinfectant addition unit processes shall be included in the Unit Process table.

 UNPRO011000120C Disinfectant Name shall only contain data for disinfectant addition unit processes.

 UNPRO011000130CDose Rate shall only contain data for disinfectant addition unit processes.

 UNPRO011000140CDisinfectant Name shall be normalized using the reference table, Normalization Calculations for Unique Chemical Formulas for Disinfectants in the ICR Initial Sampling Plan ( Appendix F).

 UNPRO011000150CDose Rate for the Disinfectant Name shall be normalized using the reference table, Normalization Calculations for Unique Chemical Formulas for Disinfectants in the ICR Initial Sampling Plan ( Appendix F).

 UNPRO011000160CAn exception report shall be created for Disinfectant Name values that cannot be normalized using the reference table provided in Appendix F.

 UNPRO011000170CT50 is a calculated field that shall equal Liquid Volume ( gal) divided by ( Water Flow into Basin ( MGD) * 694.44 gpm/ mgd), if the T50 result is not entered by the utility.

 UNPRO011000175CUP_ V shall be normalized from cubic feet to gallons for Ozone unit processes by multiplying Liquid Volume ( gal) by 7.48 gal/ cuft.

 UNPRO011000180CT10 is a calculated field for Ozone chamber unit processes that shall equal ( T50 * 0.7), if the Ozone Gas Flow to Each Chamber equals zero.

 UNPRO011000190CT10 is a calculated field for Ozone chamber unit processes that shall equal ( T50 * 0.1), if the percent Ozone Gas Flow to Each Chamber does not equal zero.

2.2.23 Chemical Feeds ( TUXCHEMFDS) SDC 0002 007 HB 2011B April 21, 2000 44 The Chemical Feeds table contains the normalized dose rate and normalized chemical name for the feeder chemicals added to each unit process.

 CHEMFDS011000010 C One or more records in the Chemical Feeds table shall be associated with one record in the Unit Process table.

 CHEMFDS011000020 C Chemical Feed Name shall be normalized using the reference table, Normalization Calculations for Unique Chemical Formulas for Chemical Feeds in the ICR Initial Sampling Plan ( Appendix G).

 CHEMFDS011000021CDose Rate shall be normalized using the reference table, Normalization Calculations for Unique Chemical Formulas for Chemical Feeds in the ICR Initial Sampling Plan ( Appendix G).

 CHEMFDS 011000030 C An exception report shall be created for Chemical Feed Name values that cannot be normalized using the reference table, Normalization Calculations for Unique Chemical Formulas for Chemical Feeds in the ICR Initial Sampling Plan ( Appendix G).

2.2.24 Ozone Chamber ( TUXOZCHM)

The Ozone Chamber table contains Ozone unit process information such as water/ ozone flow regime and percent ozone gas flow. Other basic unit process information associated with Ozone chambers is located in the Unit Process table.

 OZCHM011000010COne or more records in the Ozone Chamber table shall be associated with one record in the Ozone Contactor table.

 OZCHM011000020COne record in the Ozone Chamber table shall be associated with one record in the Unit Process table.

2.2.25 Ozone Contactor ( TUXOZCON)

The Ozone Contactor table contains information associated with each Ozone contactor. Each Ozone contactor is associated with at least one Ozone chamber.

 OZCON011000010COne record in the Ozone Contactor table shall be associated with one or more records in the Ozone Chamber table.

 OZCON011000020CThe Ozone Contactor table shall contain information for Sampling Periods 1 through 18. SDC 0002 007 HB 2011B April 21, 2000 45  OZCON011000030CNumber of Chambers is a calculated field that shall equal the number of chambers associated with each Ozone contactor.

 OZCON011000040CTotal Ozone Applied Dose ( mg/ L) is a calculated field that shall equal the sum of all Ozone doses for all disinfectant addition unit processes associated with an Ozone contactor.

 OZCON011000041 C Disinfectant addition unit processes associated with the first Ozone contactor shall equal the disinfectant addition unit processes with sequence numbers lower than the maximum sequence number of the chamber associated with the first Ozone contactor.

 OZCON011000042 C Disinfectant addition unit processes associated with a second Ozone contactor shall equal the disinfectant addition unit processes with sequence numbers higher than the maximum sequence number of the chamber associated with the first Ozone contactor and lower than the maximum sequence number of the chamber associated with the second Ozone contactor.

2.2.26 Analyte ID Reference Table ( TUXANLYT)

The Analyte ID Reference table provides a list of the analytes in Auxiliary 1 Database. The Analyte ID in the Analyte ID Reference table is linked to the Analyte ID in the Analyte Comments table. The Analyte ID Reference table lists the analyte name, the abbreviated analyte name, the minimum reporting level, and the unit of measurement for the analyte.

2.2.27 EPA Region Reference Table ( TUXEPAREG)

The EPA Region Reference table associates each state with an EPA Region.

2.2.28 Sample Quarter Reference Table ( TUXQTR)

The Sample Quarter Reference table associates each month with a sample quarter. ICR data were collected for 18 months; therefore, there are 6 quarters of data in Auxiliary 1 Database. Each quarter represents 3 months of data.

2.2.29 HUC Code Reference Table ( TUXUSGS)

The HUC Code Reference table lists the HUC code for each intake in the ICR Federal Database. This table also includes the PWSID, intake ID, intake name, and latitude and longitude measurements. SDC 0002 007 HB 2011B April 21, 2000 46 2.2.30 Disinfectant Addition Normalization Table ( TUXDISINF_ NORM)

The Disinfectant Addition Normalization table is used to normalize the disinfectant name and dose rate in the Unit Process table. It contains a record for each disinfectant that has been normalized. The original disinfectant name and formula are stored in this table, as well as the multiplication factor used to normalize the dose rate, the normalized disinfectant name, and the unit of measurement for the normalized disinfectant.

2.2.31 Feeder Chemical Normalization Table ( TUXFDCHEM_ NORM)

The Feeder Chemical Normalization table is used to normalize the chemical name and dose rate in the Chemical Feeds table. It contains a record for each chemical feed that has been normalized. The original chemical name and formula are stored in this table, as well as the multiplication factor used to normalize the dose rate, the normalized chemical name, and the unit of measurement for the normalized chemical.

3. 0 THE QUERY TOOL The Auxiliary 1 Database is designed to allow the ICR data analysts to examine a single variable across all plants ( e. g., TTHM concentration at the finished water sample point in chlorination plants). It is more difficult, although not impossible, to examine a single variable through a single plant ( e. g., change in TOC concentration through a specific plant practicing enhanced coagulation). The Query Tool provides a means by ICR data analysts can aggregate plant data in a horizontal fashion. The Query Tool allows analysts to perform through the plant analyses without having an in depth knowledge of database execution, such as creating table joins and/ or queries in Microsoft Access.

Due to the varying degrees of technical expertise of the proposed user community, the Query Tool is designed to meet certain minimum performance requirements. At the first JAD, meeting attendees agreed on a number of general characteristics for the structure of the Query Tool that will be utilized to augment the query capabilities of Microsoft ® AccessJ 97 in selecting data from the Auxiliary 1 Database. At the second JAD, a strawman of the Query Tool was presented to EPA and EPA stakeholders by the ICR DMS Team. The following Query Tool requirements were derived from discussions that took place during the first and second JAD meetings.

 QT081000010CThe Query Tool shall be designed to work with the Auxiliary 1 Database.

 QT081000020CThe Query Tool shall provide a graphical user interface ( GUI).

 QT081000030CThe Query Tool shall allow the user to select data by ICR PWSID#. SDC 0002 007 HB 2011B April 21, 2000 47  QT081000040CThe Query Tool shall allow the user to select data by ICR Treatment Plant ID No.

 QT081000050CThe Query Tool shall allow the user to select data by Sampling Period or Sample Quarter.

 QT081000060CThe Query Tool shall provide the user with the capability to enter ranges as part of the selection criteria ( i. e., =, <, >, <=, >=) for the Sample Quarter or Sampling Period.

 QT081000070CThe Query Tool shall allow the user to select data by EPA Region.

 QT081000080CThe Query Tool shall allow the user to select data by Plant Category.

 QT081000090CThe Query Tool shall allow the user to select data by Coagulant Type.

 QT081000100CThe four categories in the pick list for Coagulant Type shall be " MIX," " AL," " IRON", and " OTHER."

 QT081000110CThe " OTHER" category for Coagulant Type shall represent the null values for Coagulant Type in the Auxiliary 1 Database.

 QT081000120CThe Query Tool shall allow the user to select data by Plant Disinfectant Type.

 QT081000130CThe Query Tool shall allow the user to select data by Distribution System Disinfectant Type.

 QT0810000140CThe Query Tool shall allow the user to select data by Water Resource Category.

 QT081000150CThe Query Tool shall allow the user to select data by Surface Water Resource Category.

 QT081000160CThe pick list for Plant Disinfectant Type, Distribution System Disinfectant Type, Water Resource Category, and Surface Water Resource Category shall contain the full category name, not abbreviations.

 QT081000170CThe Query Tool shall allow the user to select data by the presence or absence of a washwater return unit process for a plant.

 QT081000180CThe Query Tool shall allow the user to select multiple tables to create a query. SDC 0002 007 HB 2011B April 21, 2000 48  QT081000190CThe Query Tool shall allow the user to select the columns from tables to be included in a query.

 QT08100200CThe Query Tool shall rename the columns to ensure that each column contains a unique name.

 QT081000210CThe Query Tool shall allow the user to select the following sample locations to be included in a query: Influent, All Distribution System Locations, ENT, DSE, AVG1, AVG2, MAX, SDS, NFC, Finished Water.

 QT081000220CAll Distribution System Locations shall represent all of the distribution sample location types: ENT, DSE, AVG1, AVG2, MAX, SDS, NFC.

 QT081000230CThe Query Tool shall allow the user to select all of the types of sampling locations that exist in the Auxiliary 1 Database, except for the following five unit processes: ozone, additional water source, additional water source sampling point, washwater return, and washwater return sampling point.

 QT081000241CThe Query Tool shall provide the user with the capability to select multiple sampling locations. If the sampling location is associated with a unit process, then the unit process data shall also be included in the Query Tool results.

 QT081000242COnly unit processes associated with samples shall be included in the Query Tool results.

 QT081000250CThe unit process with the lowest sequence number shall be used by the Query Tool, if a plant has more than one unit process of the same type and the unit process is not a clearwell or disinfection contact basin unit process.

 QT081000260CThe clearwell or disinfection contact basin unit process with the highest sequence number shall be used by the Query Tool, if a plant has more than one clearwell or disinfection contact basin unit process.

 QT081000270CThe Query Tool shall allow the user to save the output of query as a Microsoft ® AccessJ 97 table or a Microsoft ® ExcelJ spreadsheet.

Note: Microsoft ® AccessJ 97 tables are limited to 255 columns.

 QT081000290CThe Query Tool shall include a link to the Envirofacts ICR homepage and to the OGWDW ICR homepage. SDC 0002 007 HB 2011B April 21, 2000 49  QT081000300CThe Query Tool shall include the data dictionary for the Auxiliary 1 Database.

 QT081000310CThe Query Tool shall include a time/ date stamp, User ID, Query Name and Comments in each query definition.

 QT081000340CIf a user hits the F1 key while the cursor is a selection criteria field, online help will appear to explain how to select the criteria for the current field.

 QT081000350CThe Query Tool shall contain the entity relationship diagram ( ERD) for the Auxiliary 1 Database on the Select Tables screen.

 QT081000360CThe Query Tool help shall contain the definition of each field that appears on the Select Fields screen.

 QT081000370CThe Query Tool shall not display sample analytical results for blended plants. SDC 0002 007 HB 2011B April 21, 2000 APPENDIX A ICR Auxiliary 1 Database Structure A 1 TUXWRMON ( Water Resource Monthly)

TUXPLTMON ( Plant Monthly) TUXHYPO ( Hypochlorite Stock Solution)

TUXWRDES ( Water Resource Design)

TUXSCMMT ( Sample Comments) TUXACMMT ( Analyte Comments) TUXSESA ( SESA) TUXSAMPLE ( Sampling Event) TUXABDOC ( AOC/ BDOC)

TUXCLDIOX ( Chlorine Dioxide/

Ozone DBPs)

TUXCHLORS ( Chlorite/ Chlorate)

TUXCNCL ( Cyanogen Chloride)

TUXDBP ( DBPs)

TUXDISFRES ( Disinfectant Residuals)

TUXWQP ( WQPs) TUXMICRO ( Microbiology) TUXPLTDES ( Plant Design) TUXUNPRO ( Unit Process) TUXCHEMFDS ( Chemical Feeds)

TUXOZCHM ( Ozone Chamber)

TUXOZCON ( Ozone Contactor)

TUXUTDES ( Utility Design) TUXUTMON ( Utility Monthly) TUXWPURC ( Wholesale Information)

TUXPTRAIN ( Process Train Design)

Mandatory Optional One to Many One to One Relationships: SDC 0002 007 HB 2011B April 21, 2000 SDC 0002 007 MS 2007 January 7, 2000 This page is intentionally left blank.

A 2 A 3 SDC 0002 007 HB 2011B April 21, 2000 APPENDIX B Auxiliary 1 Database Fields Sorted by MS Access Alias Name SDC 0002 007 HB 2011B April 21, 2000 B 1 MS Access Alias Name Attribute Name Table Name Abbreviated_ Name ABBREVIATED_ NAME TUXANLYT Acetald_ ppb ACETAL TUXCLDIOX Add_ Water_ Source_ Avg_ Flow_ MGD AWSAVGQ TUXPLTMON Add_ Water_ Source_ Day_ Flow_ MGD AWSDAYQ TUXPLTMON Add_ Water_ Source_ Type AWSTYPE TUXPLTMON Alk_ ppmCaCO3 ALK TUXWQP Alum_ dose_ ppmAl AL_ DOSE TUXPLTMON Amor_ Crypto_ per100L CRY_ AS TUXMICRO Amor_ Giardia_ per100L GIA_ AS TUXMICRO Analyte_ Comment ANAL_ COM TUXACMMT Analyte_ ID ANAL_ ID TUXACMMT Analyte_ ID ANAL_ ID TUXANLYT Analyte_ Name ANAL_ NAME TUXANLYT Analyte_ QA_ Flag ANAL_ QA TUXACMMT AOC BDOC_ Comment_ flag COMFLG10 TUXABDOC AOC BDOC_ QA_ Flag QAFLG10 TUXABDOC SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 2 Avg_ Residence_ Time_ days SRCAVGTM TUXWRMON BCAA_ ppb BCAA TUXDBP BCAN_ ppb BCAN TUXDBP BDCAA_ ppb BDCAA TUXDBP BDCM_ ppb BDCM TUXDBP BDOC_ Final_ DOC_ ppmC DOC_ FIN TUXABDOC BDOC_ Incubat_ Time_ days REATMDAY TUXABDOC BDOC_ Initial_ DOC_ ppmC DOC_ IN TUXABDOC BDOC_ Initial_ TOC_ ppmC TOC_ IN TUXABDOC BDOC_ Method BDOC_ MET TUXABDOC Blend_ Flag BLNDFLAG TUXPLTMON Br_ ppmBr BROMIDE TUXWQP BrO3_ ppb BRO3UTIL TUXCLDIOX Butanal_ ppb BUTANAL TUXCLDIOX Ca_ HARD_ ppmCaCO3 CA_ HARD TUXWQP CDBAA_ ppb CDBAA TUXDBP SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 3 CH_ ppb CH TUXDBP Chamber_ ID CHMB_ ID TUXOZCHM CHBR3_ ppb CHBR3 TUXDBP CHCL3_ ppb CHCL3 TUXDBP Chem_ Feed_ name NORM_ CHEMNAME TUXDISINF_ NORM Chem_ Feed_ name NORM_ CHEMNAME TUXFDCHEM_ NORM Chem_ Feed_ name CHM_ NAME TUXCHEMFDS Chemical_ Feed_ ID CHEMFDID TUXCHEMFDS Chemical_ Formula ICR_ FORMULA TUXFDCHEM_ NORM Chemical_ Formula ICR_ FORMULA TUXDISINF_ NORM Chlorate_ ppb CLO3 TUXCHLORS Chlorite_ ppb CHLORITE TUXCHLORS CL2_ Demand_ ppmCL2 CLD TUXPLTMON CLO2 3_ Comment_ Flag COMFLG08 TUXCHLORS CLO2 3_ QA_ Flag QAFLG08 TUXCHLORS Clrwell_ Baffle_ Type CWBFTYPE TUXPLTDES SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 4 Clrwell_ Cover CWCCODE TUXPLTDES Clrwell_ Min_ Vol_ gal CWMLV TUXPLTDES CLX_ Res_ ppmCL2 CLXRES TUXDISFRES CNCL_ comment_ flag COMFLG07 TUXCNCL CNCL_ ppb CNCL TUXCNCL CNCL_ QA_ flag QAFLG07 TUXCNCL Coagulant_ Type COAGTYPE TUXPLTMON Coli_ Lab_ ID COLILAB TUXMICRO Coli_ Method COLI_ MET TUXMICRO Confidence_ Level DSCFDLVL TUXSAMPLE CP_ ppb CP TUXDBP D_ Approve_ WTP_ Cap_ MGD DAPPCAPQ TUXPLTDES D_ Ave_ Percent_ Solids DAVGPSOL TUXPLTDES D_ Cl2_ boost_ high_ dose_ ppmCL2 DCL2BSDH TUXUTDES D_ Cl2_ boost_ low_ dose_ ppmCL2 DCL2BSDL TUXUTDES D_ CLM_ boost_ high_ dose_ ppmCL2 DCLMBSDH TUXUTDES SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 5 D_ CLM_ boost_ low_ dose_ ppmCL2 DCLMBSDL TUXUTDES D_ CLX_ boost_ high_ dose_ ppmCLO2 DCLXBSDH TUXUTDES D_ CLX_ boost_ low_ dose_ ppmCLO2 DCLXBSDL TUXUTDES D_ Dist_ Sys_ Avg_ Time_ days DDSAVGTM TUXUTDES D_ Dist_ Sys_ Max_ Time_ days DDSMAXTM TUXUTDES D_ Hyp_ boost_ high_ dose_ ppmCL2 DHYPBSDH TUXUTDES D_ Hyp_ boost_ low_ dose_ ppmCL2 DHYPBSDL TUXUTDES D_ No_ Cl2_ boost_ station DCL2BSN TUXUTDES D_ No_ CLM_ boost_ station DCLMBSN TUXUTDES D_ No_ CLX_ boost_ station DCLXBSN TUXUTDES D_ No_ Hyp_ boost_ station DHYPBSN TUXUTDES D_ Proc_ Train_ Name DPTNAME TUXPTRAIN D_ Proc_ Train_ Type DPT_ TYPE TUXPTRAIN D_ Solid_ Hand_ Cap_ DTD DSLDGCAP TUXPLTDES D_ Source_ Type DSRCTYPE TUXWRDES D_ Vol_ dist_ sys_ storage_ MGD DSSTOR_ V TUXUTDES SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 6 D_ WTP_ Type DWTPTYPE TUXPLTDES DBAA_ ppb DBAA TUXDBP DBAN_ ppb DBAN TUXDBP DBCM_ ppb DBCM TUXDBP DBP_ Comment_ Flag COMFLG06 TUXDBP DBP_ O3 ClO3_ QA_ Flag QAFLG09 TUXCLDIOX DBP_ O3 ClO3_ Comment_ Flag COMFLG09 TUXCLDIOX DBP_ QA_ Flag QAFLG06 TUXDBP DCAA_ ppb DCAA TUXDBP DCAN_ ppb DCAN TUXDBP DCP_ HK_ ppb DCP_ HK TUXDBP Disinf_ Dose_ ppm DIS_ DOSE TUXUNPRO Disinf_ Name DIS_ NAME TUXUNPRO Disinf_ Res_ Comment_ Flag COMFLG05 TUXDISFRES Disinf_ Res_ QA_ Flag QAFLG05 TUXDISFRES Dist_ Sys_ Disinf_ Type DS_ DIS TUXPLTMON SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 7 Dist_ Sys_ Time_ hrs DSCNT_ TM TUXSAMPLE Ecoli_ BDL_ per100mL ECOLIBDL TUXMICRO Fcoli_ BDL_ per100mL FCOLIBDL TUXMICRO Tcoli_ BDL_ per100mL TCOLIBDL TUXMICRO Dose_ ppm CHM_ DOSE TUXCHEMFDS Ecoli_ per100mL ECOLI TUXMICRO Empty_ Crypto_ per100L CRYEMPTY TUXMICRO Empty_ Giardia_ per100L GIAEMPTY TUXMICRO EPA_ region EPAREG TUXUTDES EPA_ region EPAREG TUXEPAREG Event _ ID EVENT_ ID TUXCLDIOX Event _ ID EVENT_ ID TUXABDOC Event _ ID EVENT_ ID TUXWQP Event _ ID EVENT_ ID TUXHYPO Event _ ID EVENT_ ID TUXCNCL Event _ ID EVENT_ ID TUXOZCHM SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 8 Event _ ID EVENT_ ID TUXCHLORS Event _ ID EVENT_ ID TUXMICRO Event _ ID EVENT_ ID TUXDBP Event _ ID EVENT_ ID TUXPLTMON Event _ ID EVENT_ ID TUXSESA Event _ ID EVENT_ ID TUXDISFRES Event _ ID EVENT_ ID TUXUNPRO Event _ ID EVENT_ ID TUXSAMPLE Event_ Name EVNTNAME TUXSAMPLE Event_ Seq_ No EVENTSEQ TUXSAMPLE Event_ Type EVNTTYPE TUXSAMPLE EXP_ CLX_ Res_ ppmCL2 EXCLXRES TUXDISFRES EXP_ Free_ CL2_ Res_ ppmCL2 EXFCLRES TUXDISFRES EXP_ NH3_ ppmN EXPNH3_ N TUXWQP EXP_ O3_ Res_ ppmO3 EXO3RES TUXDISFRES EXP_ Tot_ CL@_ Res_ ppmCL2 EXTCLRES TUXDISFRES SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 9 FColi_ per100mL FCOLI TUXMICRO Filt_ Media_ Type FLTMEDIA TUXUNPRO Filt_ surf_ area_ sqft UP_ SA TUXUNPRO Filt_ Total_ Media_ Depth_ inch FLTDEPTH TUXUNPRO Formald_ ppb FORMAL TUXCLDIOX Free_ CL2_ Res_ ppmCL2 FCL2RES TUXDISFRES GAC_ depth_ inch GACDEPTH TUXUNPRO Glyoxal_ ppb GLYOXAL TUXCLDIOX HAA5_ ppb HAA5 TUXDBP HAA6_ ppb HAA6 TUXDBP HAA9_ ppb HAA9 TUXDBP HAN4_ ppb HAN4 TUXDBP Historic_ Min_ Temp_ C DHSMIN_ T TUXPLTDES Hypo_ Chlorate_ gperLClO3 HYPCLO3G TUXHYPO Hypo_ Free_ gperLCL2 HYPCL2_ G TUXHYPO Hypo_ pH HYPO_ PH TUXHYPO SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 10 Hypo_ Temp_ C HYPO_ T TUXHYPO ICR_ PWSID ICRPWSID TUXUTDES ICR_ PWSID ICRPWSID TUXWPURC ICR_ PWSID ICRPWSID TUXPTRAIN ICR_ PWSID ICRPWSID TUXUTMON ICR_ PWSID ICRPWSID TUXCHEMFDS ICR_ PWSID ICRPWSID TUXUSGS ICR_ PWSID ICRPWSID TUXSAMPLE ICR_ PWSID ICRPWSID TUXWRDES ICR_ PWSID ICRPWSID TUXPLTDES ICR_ PWSID ICRPWSID TUXOZCHM ICR_ PWSID ICRPWSID TUXPLTMON ICR_ PWSID ICRPWSID TUXWRMON ICR_ PWSID ICRPWSID TUXUNPRO ICR_ WTP_ ID ICRWTPID TUXSAMPLE ICR_ WTP_ ID ICRWTPID TUXCHEMFDS SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 11 ICR_ WTP_ ID ICRWTPID TUXPLTDES ICR_ WTP_ ID ICRWTPID TUXOZCON ICR_ WTP_ ID ICRWTPID TUXPTRAIN ICR_ WTP_ ID ICRWTPID TUXWRMON ICR_ WTP_ ID ICRWTPID TUXHYPO ICR_ WTP_ ID ICRWTPID TUXUNPRO ICR_ WTP_ ID ICRWTPID TUXWRDES ICR_ WTP_ ID ICRWTPID TUXPLTMON ICR_ WTP_ ID ICRWTPID TUXOZCHM Incubate_ Temp_ C INC_ TEMP TUXABDOC Incubate_ time_ days INCTMDAY TUXABDOC Intake_ Day_ Flow_ MGD INTKDAYQ TUXWRMON Intake_ HUC INTK_ HUC TUXWRDES Intake_ HUC HUC TUXUSGS Intake_ ID_ Number WSFIDNUM TUXUSGS Intake_ Name_ D DINTKNAM TUXWRDES SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 12 Intake_ Name_ M MINTKNAM TUXWRMON Intake_ ReachCode INTKSRCD TUXWRDES Intake_ River_ Mile INTKRMN TUXWRDES Intern_ Crypto_ per100L CRYGTIS TUXMICRO Intern_ GT1_ Giardia_ per100L GIAGTIS TUXMICRO Intern1_ Giardia_ per100L GIA_ IS TUXMICRO Iron_ dose_ ppmFe FE_ DOSE TUXPLTMON Lat_ degree LATDEG TUXWRDES Lat_ Equat_ Code LATEQCD TUXWRDES Lat_ minute LATMINUT TUXWRDES Lat_ second LATSECND TUXWRDES Latitude_ deg/ min LAT_ DD TUXUSGS Long_ Code LONMERCD TUXWRDES Long_ degree LONDEG TUXWRDES Long_ minute LONMINUT TUXWRDES Long_ second LONSECND TUXWRDES SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 13 Longitude_ deg/ min LONG_ DD TUXUSGS LowLevel_ BrO3_ ppb BRO3_ EPA TUXCLDIOX M_ Avg_ Fin_ Flow_ MGD FINAVGQ TUXPLTMON M_ Avg_ Inft_ Flow_ MGD INFLAVGQ TUXPLTMON M_ Avg_ Retail_ Flow_ MGD UMAVGRQ TUXUTMON M_ Avg_ Whole_ Flow_ MGD UMAVGWQ TUXUTMON M_ Cl2_ boost_ high_ dose_ ppmCL2 MCL2BSDH TUXUTMON M_ Cl2_ boost_ low_ dose_ ppmCL2 MCL2BSDL TUXUTMON M_ CLM_ boost_ high_ dose_ ppmCL2 MCLMBSDH TUXUTMON M_ CLM_ boost_ low_ dose_ ppmCL2 MCLMBSDL TUXUTMON M_ CLX_ boost_ high_ dose_ ppmCLO2 MCLXBSDH TUXUTMON M_ CLX_ boost_ low_ dose_ ppmCLO2 MCLXBSDL TUXUTMON M_ Dist_ Sys_ Avg_ Time_ days MDSAVGTM TUXUTMON M_ Dist_ Sys_ Max_ Time_ days MDSMAXTM TUXUTMON M_ Hyp_ boost_ high_ dose_ ppmCL2 MHYPBSDH TUXUTMON M_ Hyp_ boost_ low_ dose_ ppmCL2 MHYPBSDL TUXUTMON SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 14 M_ No_ Cl2_ boost_ station MCL2BSN TUXUTMON M_ No_ CLM_ boost_ station MCLMBSN TUXUTMON M_ No_ CLX_ boost_ station MCLXBSN TUXUTMON M_ No_ Hyp_ boost_ station MHYPBSN TUXUTMON M_ Proc_ Train_ Name MPTNAME TUXPLTMON M_ Proc_ Train_ Type MPT_ TYPE TUXPLTMON M_ Source_ Cat MSRC_ CAT TUXPLTMON M_ SW_ Cat MSWCAT TUXPLTMON M_ WTP_ Comment_ Flag COMFLG01 TUXPLTMON M_ WTP_ QA_ Flag QAFLG01 TUXPLTMON M_ WTP_ Type MWTPTYPE TUXPLTMON MBAA_ ppb MBAA TUXDBP MCAA_ ppb MCAA TUXDBP Me_ Glyoxal_ ppb ME_ GLY TUXCLDIOX Mean_ NOX_ CFUperml MEAN_ NOX TUXABDOC Mean_ P17_ CFUperml MEAN_ P17 TUXABDOC SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 15 Micro_ Comment_ Flag COMFLG03 TUXMICRO Micro_ QA_ Flag QAFLG03 TUXMICRO Minimum_ Reporting_ Level MIN_ RPRTNG_ LVL_ MSR TUXANLYT Multiplication_ Factor M_ FACTOR TUXDISINF_ NORM Multiplication_ Factor M_ FACTOR TUXFDCHEM_ NORM NH3_ ppmN NH3_ N TUXWQP No_ O3_ chamber O3CHMB_ N TUXOZCON No_ Proc_ Train_ Sampled SAMPPTN TUXPLTMON No_ Process_ Trains DPRTRN_ N TUXPLTDES Norm_ Chem_ ID# TUXFDCHEM_ ID_ NUM TUXFDCHEM_ NORM Norm_ Disinf_ ID# TUXDISINF_ ID_ NUM TUXDISINF_ NORM Normalized_ Units NORM_ UNITS TUXFDCHEM_ NORM Normalized_ Units NORM_ UNITS TUXDISINF_ NORM NOX_ AOC_ ppbACETeq NOX_ AOC TUXABDOC O3_ Contactor_ ID O3CONTID TUXOZCON O3_ Contactor_ ID O3CONTID TUXOZCHM SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 16 O3_ contactor_ type O3_ TYPE TUXOZCON O3_ Feed_ gas_ type O3FDTYPE TUXOZCON O3_ Feed_ ppm_% O3FDCONC TUXOZCON O3_ gas_ flow_ scfm O3_ GASQ TUXOZCON O3_ Logs_ Giardia O3GIALOG TUXOZCON O3_ Logs_ Virus O3VIRLOG TUXOZCON O3_ Regime QREGIME TUXOZCHM O3_ Res_ ppmO3 O3RES TUXDISFRES Open_ storage_ surf_ area_ sqft DSOPENSA TUXUTDES Original_ Chemical_ Name ICR_ CHEM TUXFDCHEM_ NORM Original_ Chemical_ Name ICR_ CHEM TUXDISINF_ NORM Ozone_ CT_ ppm min O3_ CT TUXOZCON P17_ AOC_ ppbACETeq P17_ AOC TUXABDOC Pentanal_ ppb PENTANAL TUXCLDIOX Percent_ O3_ Gas_ Flow PGASQ TUXOZCHM pH PH TUXWQP SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 17 Pop_ Cat POPCAT TUXUTMON Process_ flow_ MGD UP_ Q TUXUNPRO Process_ ID UP_ ID TUXUNPRO Process_ ID UP_ ID TUXCHEMFDS Process_ Name UP_ NAME TUXUNPRO Process_ Name UP_ NAME TUXCHEMFDS Process_ Seq_ No UP_ SEQ TUXUNPRO Process_ Type UP_ TYPE TUXUNPRO Propanal_ ppb PROPANAL TUXCLDIOX Prot_ Analyst_ ID PROTANLT TUXMICRO Prot_ Lab_ ID PROTLAB TUXMICRO Prot_ Vol_ Assay_ L PROTVASS TUXMICRO Prot_ Vol_ Collect_ L PROTVCOL TUXMICRO Proto_ DL_ Cysts PROTO_ DL TUXMICRO PWSID PWSID TUXUTDES Retail_ pop RPOP TUXUTMON SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 18 Sample Quarter SAMP_ QTR TUXSAMPLE Sample Quarter SAMP_ QTR TUXQTR Sample_ Comment SAMP_ COM TUXSCMMT Sample_ Day_ Fin_ Flow_ MGD FINMAXQ TUXPLTMON Sample_ Day_ Infl_ Flow_ MGD INFLDAYQ TUXPLTMON Sample_ end_ date END_ DATE TUXUTMON Sample_ ID SAMP_ ID TUXSESA Sample_ ID SAMP_ ID TUXSCMMT Sample_ ID SAMP_ ID TUXACMMT Sample_ Loc_ ID SAMP_ LOC TUXSAMPLE Sample_ Period SAMP_ PER TUXOZCON Sample_ Period SAMP_ PER TUXPTRAIN Sample_ Period SAMP_ PER TUXCHEMFDS Sample_ Period SAMP_ PER TUXPLTMON Sample_ Period SAMP_ PER TUXUTDES Sample_ Period SAMP_ PER TUXPLTDES SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 19 Sample_ Period SAMP_ PER TUXUTMON Sample_ Period SAMP_ PER TUXOZCHM Sample_ Period SAMP_ PER TUXQTR Sample_ Period SAMP_ PER TUXHYPO Sample_ Period SAMP_ PER TUXWRMON Sample_ Period SAMP_ PER TUXWRDES Sample_ Period SAMP_ PER TUXSAMPLE Sample_ Period SAMP_ PER TUXWPURC Sample_ Period SAMP_ PER TUXUNPRO Sample_ QA_ Flag SAMP_ QA TUXSCMMT Sample_ start_ date STRTDATE TUXUTMON Sludge_ Percent_ Solids SLDGPSOL TUXPLTMON Sludge_ Prod_ DTD SLDGPROD TUXPLTMON Source_ Name MSRCNAME TUXWRMON Source_ Name DSRCNAME TUXWRDES Source_ Type MSRCTYPE TUXWRMON SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 20 State_ Abbreviation STATE_ CD TUXEPAREG T10_ min T10 TUXUNPRO T50_ min T50 TUXUNPRO TBAA_ ppb TBAA TUXDBP TCAA_ ppb TCAA TUXDBP TCAN_ ppb TCAN TUXDBP TColi_ per100mL TCOLI TUXMICRO TCP_ HK_ ppb TCP_ HK TUXDBP TEMP_ C TEMP TUXWQP TOC_ ppmC TOC TUXWQP Tot_ AOC_ ppbC TOT_ AOC TUXABDOC Tot_ CL2_ Res_ ppmCL2 TCL2RES TUXDISFRES Tot_ Crypto_ per100L TCRYPTO TUXMICRO Tot_ Giardia_ per100L TGIARDIA TUXMICRO Tot_ HARD_ ppmCaCO3 T_ HARD TUXWQP Tot_ O3_ dose_ ppm O3TDOSE TUXOZCON SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 21 Tot_ Virus_ MPNper100L VIRUS TUXMICRO Total_ pop TPOP TUXUTMON TOX_ ppbCl TOX TUXWQP TOX_ RPD_ percent TOXRPD TUXWQP TTHM_ ppb TTHM TUXDBP TURB_ NTU TURB TUXWQP Unit_ of_ Measurement CNCNTRTN_ UNIT_ CDE TUXANLYT Utility_ name NAME TUXUSGS Utility_ name UTILNAME TUXUTDES UV_ cm 1 UV_ 254 TUXWQP Virus_ 95LCL_ MPNper100L VIR_ LCL TUXMICRO Virus_ 95UCL_ MPNper100L VIR_ UCL TUXMICRO Virus_ Analyst_ ID VIR_ ANLT TUXMICRO Virus_ BDL_ MPNper100L VIR_ BDL TUXMICRO Virus_ Lab_ ID VIR_ LAB TUXMICRO Virus_ Vol_ Assay_ L VIR_ VASS TUXMICRO SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 22 Virus_ Vol_ Collect_ L VIR_ VCOL TUXMICRO Vol_ gal UP_ V TUXUNPRO Wellhead_ Protect WHEADPRT TUXWRDES Whole_ Purchase_ Flow_ MGD WPURQ TUXWPURC Whole_ Purchase_ ID WPURID TUXWPURC Whole_ Purchase_ Name WPURNAME TUXWPURC Wholesale_ pop WPOP TUXUTMON WQP_ Comment_ Flag COMFLG04 TUXWQP WQP_ QA_ Flag QAFLG04 TUXWQP WTP_ Baffling_ Factor BFCOMP TUXPLTMON WTP_ Disinf_ Type WTP_ DIS TUXPLTMON WTP_ Name DWTPNAME TUXPLTDES Wtrshed_ Control WSHDCTRL TUXWRDES WWR_ 24hr_ Avg_ Flow_ MGD WWRAVGQ TUXPLTMON WWR_ Coag WWRCOAG TUXPLTMON WWR_ Disinf WWRDIS TUXPLTMON SDC 0002 007 HB 2011B April 21, 2000 MS Access Alias Name Attribute Name Table Name B 23 WWR_ Filter WWRFILT TUXPLTMON WWR_ Other WWROTH TUXPLTMON WWR_ Sample_ Day_ Flow_ MGD WWRDAYQ TUXPLTMON WWR_ Sed WWRSED TUXPLTMON WWR_ Treat_ Type WWRTYPE TUXPLTMON SDC 0002 007 HB 2011B April 21, 2000 B 24 This page is intentionally left blank. SDC 0002 007 HB 2011B April 21, 2000 APPENDIX C Data Mapping of the Auxiliary 1 Database to the ICR Federal Database SDC 0002 007 HB 2011B April 21, 2000 APPENDIX D Primary Key Reference Tables SDC 0002 007 HB 2011B April 21, 2000 D 1 Entity Type Name: Assimilable Organic Carbon/ Biodegradable Organic Carbon ( AOC/ BDOC)

Table Name: TUXABDOC Attribute Name TUXABDOC Field Name Sample Event ID EVENT_ ID number ( 7) not null( PK)

Entity Type Name: Analyte Comments Table Name: TUXACMMT Attribute Name TUXACMMT Field Name Sample ID SAMP_ ID char ( 28) ( PK)

Analyte ID Number ANAL_ ID number ( 3) ( PK)

Entity Type Name: Analyte ID Reference Table Table Name: TUXANLYT Attribute Name TUXANLYT Field Name Analyte ID Number ANAL_ ID number ( 3) ( PK)

Entity Type Name: Chemical Feeds Table Name: TUXCHEMFDS Attribute Name TUXCHEMFDS Field Name Chemical Feed ID CHEMFDID number ( 6) not null( PK) SDC 0002 007 HB 2011B April 21, 2000 D 2 Entity Type Name: Chlorite/ Chlorate Table Name: TUXCHLORS Attribute Name TUXCHLORS Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK)

Entity Type Name: Chlorine Dioxide/ Ozone Disinfection By Products ( DBP)

Table Name: TUXCLDIOX Attribute Name TUXCLDIOX Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK)

Entity Type Name: Cyanogen Chloride Table Name: TUXCNCL Attribute Name TUXCNCL Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK)

Entity Type Name: Disinfection By Products ( DBP)

Table Name: TUXDBP Attribute Name TUXDBP Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK) SDC 0002 007 HB 2011B April 21, 2000 D 3 Entity Type Name: Disinfectant Residuals Table Name: TUXDISFRES Attribute Name TUXDISFRES Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK)

Entity Type Name: Disinfectant Addition Normalization Table Table Name: TUXDISINF_ NORM Attribute Name TUXDISINF_ NORM Field Name Disinfectant Addition Normalization ID TUXDISINF_ ID_ NUM number ( 10) not null ( PK)

Entity Type Name: EPA Region Reference Table Table Name: TUXEPAREG Attribute Name TUXEPAREG Field Name State Abbreviation ST_ CODE char ( 2) ( PK)

Entity Type Name: Feeder Chemical Normalization Table Table Name: TUXFDCHEM_ NORM Attribute Name TUXFDCHEM Field Name Chemical Normalization ID TUXCHEM_ ID_ NUM number ( 10) not null ( PK) SDC 0002 007 HB 2011B April 21, 2000 D 4 Entity Type Name: Hypochlorite Stock Solution Table Name: TUXHYPO Attribute Name TUXHYPO Field Name Sample Event ID EVENT_ ID number ( 7) not null( PK)

Entity Type Name: Microbiology Table Name TUXMICRO Attribute Name TUXMICRO Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK)

Entity Type Name: Ozone Chamber Table Name: TUXOZCHM Attribute Name TUXOZCHMB Field Name ICR Treatment Plant ID No. ICRWTPID number ( 3) not null( PK)

Chamber ID CHMB_ ID number ( 6) not null( PK)

Sampling Period SAMP_ PER number ( 2) not null( PK)

Contractor ID O3CONTID number ( 6) not null( PK) SDC 0002 007 HB 2011B April 21, 2000 D 5 Entity Type Name: Ozone Contactor Table Name: TUXOZCON Attribute Name TUXOZCON Field Name ICR Treatment Plant ID No. ICRWTPID number ( 3) not null( PK)

Contactor ID O3CONTID number ( 6) not null( PK)

Sampling Period SAMP_ PER number ( 2) not null( PK)

Entity Type Name: Plant Design Table Name: TUXPLTDES Attribute Name TUXPLTDES Field Name PWS ID Number ICRPWSID char ( 21) not null( PK)

ICR Treatment Plant ID No. ICRWTPID number ( 3) not null( PK)

Sampling Period SAMP_ PER number ( 2) not null( PK) SDC 0002 007 HB 2011B April 21, 2000 D 6 Entity Type Name: Plant Monthly Table Name: TUXPLTMON Attribute Name TUXPLTMON Field Name PWS ID Number ICRPWSID char ( 21) not null ( PK)

ICR Treatment Plant ID No. ICRWTPID number ( 3) not null ( PK)

Sampling Period SAMP_ PER number ( 2) not null ( PK)

Entity Type Name: Process Train Design Table Name: TUXPTRAIN Attribute Name TUXPTRAIN Field Name Process Train Name DPTNAME char ( 40) not null( PK)

PWS ID Number ICRPWSID char ( 21) not null( PK)

ICR Treatment Plant ID No. ICRWTPID number ( 3) not null( PK)

Sampling Period SAMP_ PER number ( 2) not null( PK)

Entity Type Name: Sample Quarter Reference Table Table Name: TUXQTR Attribute Name TUXQTR Field Name Sampling Period SAMP_ PER number ( 2) not null ( PK)

Entity Type Name: Sampling Event SDC 0002 007 HB 2011B April 21, 2000 D 7 Table Name: TUXSAMPLE Attribute Name TUXSAMPLE Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK)

Entity Type Name: Sample Comments Table Name: TUXSCMMT Attribute Name TUXSCMMT Field Name Sample ID SAMP_ ID char ( 28) ( PK)

Entity Type Name: Sample Event/ Sample ID Associative ( SESA)

Table Name: TUXSESA Attribute Name TUXSESA Field Name Sample Event/ Sample Associative ID SESA_ ID number ( 10) not null( PK)

Entity Type Name: Unit Process Table Name: TUXUNPRO Attribute Name TUXUNPRO Field Name PWS ID Number ICRPWSID char ( 21) not null( PK)

Unit Process ID UP_ ID number ( 6) not null( PK)

Sampling Period SAMP_ PER number ( 2) not null( PK) SDC 0002 007 HB 2011B April 21, 2000 D 8 Entity Type Name: HUC Code Reference Table Table Name: TUXUSGS Attribute Name TUXUSGS Field Name PWS ID Number ICRPWSID char ( 21) not null( PK)

Intake ID Number WSFIDNUM number ( 3) not null( PK)

Entity Type Name: Utility Design Table Name: TUXUTDES Data Element TUXUTDES Attribute PWS ID Number ICRPWSID char ( 21) not null( PK)

Sampling Period SAMP_ PER number ( 2) not null( PK)

Entity Type Name: Utility Monthly Table Name: TUXUTMON Attribute Name TUXUTMON Field Name PWS ID Number ICRPWSID char ( 21) not null ( PK)

Sampling Period SAMP_ PER number ( 2) not null ( PK) SDC 0002 007 HB 2011B April 21, 2000 D 9 Entity Type Name: Wholesale Information Table Name: TUXWPURC Attribute Name TUXWPURC Field Name Wholesale Purchaser PWSID# WPURID char ( 9) not null( PK)

PWS ID Number ICRPWSID char ( 21) not null ( PK)

Sampling Period SAMP_ PER number ( 2) not null ( PK)

Entity Type Name: Water Quality Parameters ( WQP)

Table Name: TUXWQP Attribute Name TUXWQP Field Name Sample Event ID EVENT_ ID number ( 7) not null ( PK) SDC 0002 007 HB 2011B April 21, 2000 D 10 Entity Type Name: Water Resource Design Table Name: TUXWRDES Attribute Name TUXWRDES Field Name Water Resource Name DSRCNAME char ( 30) not null ( PK)

PWS ID Number ICRPWSID char ( 21) not null ( PK)

ICR Treatment Plant ID No. ICRWTPID number ( 3) not null ( PK)

Sampling Period ( 0 B Initial or 19 B Final) SAMP_ PER number ( 2) not null ( PK)

Intake Name DINTKNAM char ( 40) not null ( PK)

Entity Type Name: Water Resource Monthly Table Name: TUXWRMON Attribute Name TUXWRMON Field Name Water Resource Name MSRCNAME char ( 30) not null ( PK)

PWS ID Number ICRPWSID char ( 21) not null ( PK)

ICR Treatment Plant ID No. ICRWTPID number ( 3) not null ( PK)

Sampling Period SAMP_ PER number ( 2) not null ( PK)

Intake Name MINTKNAM char ( 40) not null ( PK) SDC 0002 007 HB 2011B April 21, 2000 APPENDIX E Calculated Attributes for the ICR Auxiliary 1 Database SDC 0002 007 HB 2011B April 21, 2000 APPENDIX F Normalization Calculations for Unique Chemical Formulas for Disinfectants in the ICR Initial Sampling Plan SDC 0002 007 HB 2011B April 21, 2000 APPENDIX G Normalization Calculations for Unique Chemical Formulas for Chemical Feeds in the ICR Initial Sampling Plan