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
