Document ID: EPA-HQ-OW-2002-0043-0216
Agency: epa
Document Type: Supporting & Related Material
Title: 
Posted Date: 2003-08-18T04:00Z

United
States
Environmental
Protection
Agency
Office
of
Ground
Water
and
Drinking
Water
Auxiliary
1
Database
and
Documentation
Production
Release
5.0.1
Contract
No.
68­
W­
99­
002
Task
Order
No.
007
Product
Control
No.
SDC­
0002­
007­
SK­
2011C
April
24,
2000
SDC­
0002­
07­
SK­
2011C
April
24,
2000
AUXILIARY
1
DATABASE
AND
DOCUMENTATION
PRODUCTION
RELEASE
5.0.1
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
St.,
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­
07­
SK­
2011C
April
24,
2000
iii
CONTENTS
1.0
INTRODUCTION
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
1.1
Purpose
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
1
1.2
Scope
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
2
1.3
References
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
3
2.0
SUMMARY
OF
FUNCTIONALITY
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
5
3.0
DEGREE
OF
FUNCTIONALITY
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
5
3.1
Software
Limitations
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
5
3.2
Requirements
Met
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
6
3.3
Special
Software
Considerations
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
6
4.0
CAUTIONARY
NOTES
REGARDING
THE
AUXILIARY
1
DATABASE
.
.
.
.
.
.
6
4.1
Giardia
and
Cryptosporidium
Measurements
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
7
4.2
Total
Trihalomethane
(
TTHM)
Distribution
System
Measurements
.
.
.
.
.
.
.
.
.
.
7
5.0
INSTALLATION
INSTRUCTIONS
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
7
5.1
Microsoft
®
AccessJ
Version
of
the
Auxiliary
1
Database
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
7
APPENDIX
A
List
of
Files
and
Creation
Dates
SDC­
0002­
07­
SK­
2011C
April
24,
2000
1
1.0
INTRODUCTION
1.1
Purpose
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
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
analytical
laboratories
that
are
approved
to
perform
sample
analyses
under
the
ICR
report
data
directly
to
the
Agency.
Capabilities
of
the
ICR
Federal
Database
System
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.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
2
After
data
are
loaded
into
the
ICR
Federal
Database
System
and
validated,
the
data
must
be
analyzed
by
a
number
of
researchers
and
analysts.
The
analysts
use
the
ICR
data
to
develop
regulations
to
assure
public
health
protection
from
disease­
causing
microbes
(
pathogens),
including
cryptosporidium,
virus,
giardia,
and
DBPs.
The
ICR
DMS
Team
was
tasked
with
developing
the
Auxiliary
1
Database,
which
stores
a
subset
of
data
extracted
from
the
ICR
Federal
Database
System.
The
Auxiliary
1
Database
provides
researchers
and
analysts
with
a
smaller,
yet
still
comprehensive,
version
of
the
ICR
Federal
Database
System.

This
document
presents
the
release
notes
for
Production
Release
5.0.1
of
the
Auxiliary
1
Database.

1.2
Scope
The
Task
Order
Project
Officer
(
TOPO)
tasked
the
ICR
DMS
Team
with
delivering
the
Auxiliary
1
Database
(
a
Microsoft
®
AccessJ
database
containing
all
of
the
Auxiliary
1
Database
tables).

The
data
contained
in
the
Auxiliary
1
Database
represents
data
that
were
extracted
from
the
ICR
Federal
Database
System
using
the
Auxiliary
1
Database
software.
The
release
notes
for
the
Auxiliary
1
Database
reflect
the
amount
and
scope
of
the
development
effort
that
has
completed
successful
Integration/
Acceptance
Testing
(
I/
AT).

To
support
the
release
of
the
Auxiliary
1
Database,
the
Auxiliary
1
Database
software
was
run
against
the
ICR
Federal
Database
System
to
create
an
Oracle
®
version
of
the
Auxiliary
1
Database.
Each
table
in
the
Oracle
®
Auxiliary
1
Database
was
exported
as
an
American
Standard
Code
for
Information
Interchange
(
ASCII)
text
file.
Each
ASCII
text
file
was
imported
into
a
corresponding
Microsoft
®
AccessJ
table
to
create
the
Microsoft
®
AccessJ
version
of
the
Auxiliary
1
Database.

The
following
data
types
are
stored
in
the
Auxiliary
1
Database:

EntitiesCPersons,
places,
things,
or
concepts
that
have
characteristics
of
interest
to
the
ICR
stakeholders
(
e.
g.,
TUXSAMPLE).
An
entity
is
represented
by
a
table.
Each
column
in
the
table
consists
of
an
attribute,
while
each
row
consists
of
a
record
or
instance.

AttributesCCharacteristics
or
properties
that
can
be
described
in
terms
of
a
value
that
entities
of
a
given
type
possess
(
e.
g.,
EVENT_
ID).
An
attribute
is
usually
a
data
item
containing
a
single
piece
of
information
about
an
entity
instance.
All
attributes
of
a
given
type
have
the
same
format,
interpretation,
and
range
of
acceptance
values.
An
attribute
is
represented
by
a
column
in
a
table,
or
entity.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
3

RelationshipsCReasons
(
of
relevance
to
the
ICR
stakeholders)
why
entities
from
two
entity
types
are
associated
(
e.
g.,
one
record
in
TUXSAMPLE
may
be
associated
with
one
record
in
TUXDBP).
A
relationship
is
represented
by
a
named
connection
or
association
between
entity
types
that
embodies
some
relevant
information
of
value
to
an
ICR
stakeholder
and
generally
characterizes
a
business
rule.

1.3
References
The
following
documents
are
applicable
to
this
release.
They
include
government,
industry,
and
ICR
project
documents,
standards,
specifications,
plans,
regulations,
manuals,
and
other
materials
used
to
develop
this
document.

Note:
The
documents
are
listed
in
reverse
chronological
order.

Auxiliary
1
Database
and
Documentation,
Production
Release
5.0
Systems
Development
Center
(
SDC);
SDC­
0002­
007­
HB­
2011B;
April
21,
2000

Auxiliary
1
Database
and
Documentation,
Production
Release
4.0.
SDC;
SDC­
0002­
007­
MS­
2007;
January
7,
2000.

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

Year
2000
Calendar
Date
Standard
Waiver,
ICR,
Contract:
68­
W­
99­
002,
Task
Order
007,
Mod:
09,
August
12,
1999.

Auxiliary
1
User
Documentation,
Version
2.0.
SDC;
SDC­
0002­
007­
EB­
1026;
July
28,
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.

Project
Plan
for
Information
Systems
Development
and
User
Support
for
the
Public
Water
System
Supervision
(
PWSS)
Program's
Information
Collection
Rule
(
ICR).
EPA
SDC;
SDC­
0002­
007­
HB­
1001A;
December
22,
1998.

Information
Collection
Rule
(
ICR)
Auxiliary
Database
2
Design
Documentation.
EPA
SDC;
SDC­
0055­
083­
MS­
8008;
December
18,
1998.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
4

Information
Collection
Rule
(
ICR)
Auxiliary
Database
6
Design
Documentation.
EPA
SDC;
SDC­
0055­
083­
MS­
8012;
December
18,
1998.

Information
Collection
Rule
(
ICR)
Auxiliary
Database
3
Design
Documentation.
EPA
SDC;
SDC­
0055­
083­
MS­
8009;
December
11,
1998.

Information
Collection
Rule
(
ICR)
Auxiliary
Database
4
Design
Documentation.
EPA
SDC;
SDC­
0055­
083­
MS­
8010;
December
11,
1998.

Information
Collection
Rule
(
ICR)
Auxiliary
Database
5
Design
Documentation.
EPA
SDC;
SDC­
0055­
083­
MS­
8011;
December
11,
1998.

Information
Collection
Rule
(
ICR)
Auxiliary
Database
7
Design
Documentation.
EPA
SDC;
SDC­
0055­
083­
MS­
8013;
December
11,
1998.

Final
Information
Collection
Rule
(
ICR)
Data
Management
System
(
DMS)
System
Manual.
EPA
SDC;
SDC­
0055­
083­
MH­
7035;
September
30,
1998.

Chapters
3
through
9
of
the
Information
Collection
Rule
(
ICR)
Data
Analysis
Requirements
Specification.
EPA
SDC;
SDC­
0055­
083­
EB­
7031;
September
29,
1998.

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.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
5

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.

Project
Plan
for
Information
Systems
Development
and
User
Support
for
the
Public
Water
System
Supervision
(
PWSS)
Program's
Information
Collection
Rule
(
ICR).
EPA
SDC;
SDC­
0055­
083­
MC­
6043A;
August
27,
1997.

IEEE
Recommended
Practice
for
Software
Requirements
Specifications,
Institute
of
Electrical
and
Electronics
Engineers,
Inc.,
IEEE
Std
830­
1993;
April
8,
1994.

2.0
SUMMARY
OF
FUNCTIONALITY
The
release
of
the
Auxiliary
1
Database
software
incorporates
all
functions
listed
as
requirements
in
the
document,
Auxiliary
1
Database
and
Documentation,
Production
Release
5.0
SDC;
SDC­
0002­
007­
HB­
2011B;
April
21,
2000.

The
files
comprising
this
release
of
the
Auxiliary
1
Database
are
listed
and
defined
in
Appendix
A.

3.0
DEGREE
OF
FUNCTIONALITY
This
section
presents
the
software
limitations
noted
as
a
result
of
the
I/
AT
efforts
of
the
ICR
DMS
Team,
in
response
to
software
changes
coordinated
and
approved
through
the
Change
Control
Board
(
CCB)
process.
No
changes
have
been
made
to
the
Auxiliary
1
Database
extraction
software
since
the
completion
of
testing
for
Release
4.0
on
January
7,
2000.

3.1
Software
Limitations
There
are
no
outstanding
SIRs
associated
with
the
Auxiliary
1
Database.

3.2
Requirements
Met
The
following
activities
were
conducted
to
ensure
that
the
Auxiliary
1
Database
conforms
to
all
requirements
specified
by
EPA
and
selected
members
of
the
user
community:
SDC­
0002­
07­
SK­
2011C
April
24,
2000
6

Representatives
of
the
development
team,
EPA,
the
water
utility
industry,
and
the
analytical
laboratories
supporting
the
industry
previously
refined
the
requirements
in
three
Joint
Requirements
Planning
(
JRP)
sessions
and
two
Joint
Application
Design
(
JAD)
sessions.

Requirements
continued
to
be
refined
and
changed
through
on­
going
CCB
activities.

The
software
development
team
and
Product
Assurance
(
PA)
reviewed
each
procedure
during
a
formal
peer
review
to
ensure
that
each
procedure
met
the
designated
requirements.

Detailed
test
procedures
were
created,
run,
and
evaluated
to
verify
each
testable
requirement.
Refer
to
the
document,
Auxiliary
1
Database
and
Documentation,
Production
Release
5.0,
SDC;
SDC­
0002­
007­
HB­
2011B;
April
21,
2000
for
a
more
detailed
description
of
test
procedures.

3.3
Special
Software
Considerations
There
are
no
special
software
considerations
mandated
by
the
databases.

4.0
CAUTIONARY
NOTES
REGARDING
THE
AUXILIARY
1
DATABASE
The
ICR
data
have
been
collected
for
research
purposes
and
should
not
be
used
to
make
site­
specific
compliance
or
health
assessments.
EPA
will
use
this
data
in
aggregate
to
develop
national
compliance
assessments;
therefore,
EPA
will
not
emphasize
plant­
by­
plant
analysis.
It
should
also
be
noted
that
data
content
may
change
with
updated
releases
due
to
continual
modifications
and
improvements
to
the
data
structures.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
7
4.1
Giardia
and
Cryptosporidium
Measurements
A
measurement
result
of
zero
means
that
no
Giardia
or
Cryptosporidium
was
found
in
the
sample
volume
that
was
analyzed.
Zero
results
do
not
indicate
the
absence
of
Giardia
or
Cryptosporidium
in
the
source
water
because
the
method
recovery
is
low
and
the
amount
of
sample
analyzed
is
small.
In
other
words,
Giardia
or
Cryptosporidium
may
be
present
in
source
water
even
if
no
cysts
or
oocysts
are
counted
for
the
sample
volume
analyzed.

4.2
Total
Trihalomethane
(
TTHM)
Distribution
System
Measurements
Comparisons
of
ICR
data
with
compliance
calculations
based
on
the
existing
TTHM
standard
in
Code
of
Federal
Regulations
(
CFR)
Part
141.30
may
not
be
warranted
since
the
average
concentration
in
the
distribution
system
calculated
for
the
ICR
data
is
calculated
differently
than
the
compliance
average
for
the
current
standard.
The
standard
is
based
on
an
annual
average
of
public
water
system
quarterly
averages
and
not
on
treatment
plant
results.

5.0
INSTALLATION
INSTRUCTIONS
The
following
installation
instructions
are
provided
to
facilitate
the
installation
of
the
database:

The
Auxiliary
1
Database.

The
installation
instructions
are
enumerated
by
section,
corresponding
to
each
database.

5.1
Microsoft
®
AccessJVersion
of
the
Auxiliary
1
Database
This
software
is
designed
to
be
run
under
Microsoft
®
AccessJ
97
on
a
Windows
95J
Personal
Computer
(
PC)
platform.
The
Auxiliary
1
Database
is
delivered
on
a
Compact
Diskette­
Read
Only
Memory
(
CD­
ROM).
The
CD­
ROM
is
read
only;
therefore,
the
Auxiliary
1
Database
is
also
read
only.
Users
will
be
able
to
browse
the
tables
on
the
CD­
ROM;
however,
users
will
not
be
able
to
create
queries
against
the
Auxiliary
1
Database.
The
user
must
copy
the
Auxiliary
1
Database
to
a
directory
on
the
PC
in
order
to
have
write
access.
To
install
the
database:

Decompress
the
AUX1.
ZIP
file
using
WINZIP.

Copy
the
AUX1.
MDB
file
to
any
sub­
directory
on
the
C:\
drive.

Start
Microsoft
®
AccessJ,
open
AUX1.
MDB.

Under
the
Tables
menu,
you
will
see
all
of
the
tables
contained
in
the
database.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
8
Note:
For
the
installation
to
work
properly,
Microsoft
®
AccessJ
97
must
be
installed.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
APPENDIX
A
List
of
Files
and
Creation
Dates
SDC­
0002­
07­
SK­
2011C
April
24,
2000
A­
1
Microsoft
®
AccessJ
Version
AUX1.
MDB
04­
22­
2000
This
Microsoft
®
AccessJ
Database
contains
the
Auxiliary
1
tables
and
reports.
SDC­
0002­
07­
SK­
2011C
April
24,
2000
A­
2
This
page
is
intentionally
left
blank.