Technical Report: DEVELOPING A RELATIONAL DATABASE FOR NATURAL ATTENUATION FIELD DATA

t-L

L66ltsn8nv

,(:o1c:oqe1 Suorlsur:y puc :ry 8ur11og 3 q ' u o i S u r q s e / y \ . ' a soe3gJ o C qJJueseu JurtuarcsJo eJrJ.joacrocJIV :,(qparosuod5

AJoleJoqe-ItsuoJtsr.urv ruu:8o:4 rlJJcasoU,(11nccg JeuttunS ::o-;t:odag lcurg

-IC'eesscwllel 90EZE ,(1ts:e,tt u1-1 otutS eprJolC

otntrtsuJ scIIuuu,{qprnlg lucrsr{qdoeg J€ioqJSqJJr?eseu alBrJossv e3:i1e3'hi'H

VIVC C'IEIC NOIIVNNEIJV TVUOJVN UOCESVSVJVC'IVNOIIV-ITd V CNIIdO-IEAEC

Z-L

'sllnsal IE+uaurrJadxa ar{l Jo uoq€laJdra}ur }uarJrJJaSuqlrurJad ,{qa.raql 'sasequlep snorrel ar{} ssa)Je IIrM l€ql aseq€lpp ISSEf,JV l;oso-rrry,q e Jo luaurdolanap aL{}ur pa}ll-rsarronpapua qJrpasar sIr{J 'suoqrerorpr{q Jo uor}enua}l€ IeJnlEu ar{l o} alnqrJ}uo) }Pr{l sassaro:d ieJruraqJorq pue IP)Iuraqroa8 arli Jo uoqnlona ar{l otq }q8rsur ure8 o1 rapro ur pazdleue .,(lluanbasqns 'saseqel€p uarfi are Elpp ar{J IEJanas o}q pareld aJe sJuaruaJnseaurdroleroqei 'sadolosr uoqru) alqels pue 1uo:r snorJal 'aueqlaru 'ua8o:p,{q pue plalJ ari] tuorJ €l€C

pallosslp 'aprxorp uoqJ€) 'a1e1ns'a1rr1ru'aler1ru'ua8dxopallossrp Surpnpur sroldarre uoJlf,ala laprruorq lsuoqrerorpdq panlossrp 'arnleradrual 'gd ro; pazlleue aq uuJ lurod Suqduies qrea lu saldures raleM 'slurod Surldrues 0009 ueql arour Jo >lro,t,ttauSurldr.ues e sapr,r.ordsIr{J 'sllazvlSurldrues la^aptlniu ggg uer.ilarour Jo lrom}au Surlsrxeue q8norql alISar{l }€ palJalloJ sI El€Cl '.ra;rnbesnoauaSoralaqpa}uarunrlsur {UB1q pue pazrralJerpr{) 'gCV snqrunlo) le pa})npuor lpnls IiaM e ur pareldr-ua s€,rlrarnlxrru uoqrerorp{r{ n uollEnua+l€ Iernleu Suro8uo ue uI 'lanJ laf paJaq]EaMiapolu p Jo as€aial ]sal aq] Luo{ pauelqo sJlnsar ar{t Jo uorleia;d;alul pl€ o1 padola,rap uaaq spr{ asuq€lep Iuuorlelar V l J E J ] S O Vt v

-1{'aasser{elleI 90€zt,

l'iil:?jT,':, il LlHuo"", ",.,,,,,{' rEIOr{JS r{JreasaualPrJossv a;rrTa]'hl'H

VJVC CTEIJ NOIIVNNEJIV -iVNOIIV'IEU TVUNIVN dOC ASVSVIVC V DNIdO-IENEC

DEVELOPINGA RELATIONAL DATABASE FOR NATURAL ATTENUATION FIELD DATA H. M. Cekirge Introduction Natural attennation is rapidly becoming the most commoniy used method for remediation of groundwater contaminated with hydrocarbon fuels and solvents. In natural attenr-ration, subsurfacephysical,chemicaland biological processesact to decrease groundwater contaminant concentrations dor,tm-gradient from a contaminant source. While there is extensive empirical evidence for natural attenuation, the mechanisms and ratesof theseprocessesare not well characterized. In order to better understand the processes involved in natural attenuation, the Environics Directorate of Armstrong Laboratory is conducting a study at Columbus AFB, MS. A simtilated jet fuel spili containing Decane (75.5'/.),Naphthalene (6.2"h),p-Xylene (6.1"/"),Ethy'ibenzene(6.0"/o),Toluene (6.2'/"),Benzene (0.05%),and 2 Kg of Potassium Bromide as a conservativetracer was mixed with 30 m3 of local aquifer material to create a 16 "/,'residual phase mixture. In November 1995,the mixture was emplaced below the water table in the aquifer. Over 300 muiti-level sampling welis, representing more than 6000 discrete sampling locations, are located downgradient

from

the source.

Croundwater, driven by natural hydraulic gradients, percolates through the source and forms a plume containing the hydrocarbons and bromide tracer.

The size and

composition of the plume is monitored periodically by collection and analysis of water samples from the sampling wells.

Careful analysis of the field data is allowing an

understanding of the geochemical and biochemical processesthat contribute to natural attenuation. Scientistsat the Environics Directoratehave identified a number of chemical parameters that must be measured in order to provide information about the natural attenuation process. At most of the Coiumbus AFB sampling locations, more than a dozen parametersare measured. The results of the measurementsare collectedinto a number of databasesfor processing. The huge volume of data, coupled with the fragmented nature of these databases,makes it difficnlt to integrate all the data to give a unified view of the natural attenuation processes. This researchendeavor undertook the development of a 1-3

relational database that would allow Environics scientists an efficient way to access, query, and manipulate the data contained in the individual databases.

Structure of the Database I - Sourceof the Database An existing network of more than 300 multilevel sampling wells representing 6000+ sampling points is available for groundwater sampling. Additionally, new wells are being installed as needed. Groundwater and aquifer solids were sampled prior to source emplacement to measure background conditions in the aquifer.

Since source

emplacement, samples have been collected approximately quarter-annually. Dissolved oxygen, pH, and temperature measurements are taken in the field. Groundwater is taken to laboratories and analyzed for dissolved hydrocarbons; bromide; electron acceptors including nitrate, nitrite and sulfate; and microbial degradation reaction products including carbon dioxide, dissolved hydrogen, methane, ferrous iron; and stable carbon isotopes. Aqr-rifer solids are analyzed for aerobic microbial population and degradation activity; anaerobic microbial activity and bulk and iron mineralogy. Results from these measurements,as well as field sampling notes, are maintained in a variety of database forms. II - Path to the Database The following fiies were prepared as Microsoft EXCELZ files from the data contained in various databasefiles. Thesefiles are : 1 - The WELLMAP file presents x, y and z coordinates of location of the samples, see Table 1. 2 - The TIMER file is the record file which consists of recording time of each sample, see Table 2.

1-4

Table 1. The structure of the WELLMAP file.

Table 2. The structure of the TIMER file.

Snapshot Well

Index

1

lJOtI

Date 24-Aug-95

MO

z4-AUg-Y5

MO3

24-Aug-95

MO A

24-Aug-95

MO

24-Aug-95

MO I

24-Aug-95

7

-2

MO I

24-Aug-95

8

-z

M020

24-Aug-95

I

-2

M021

24-Aug-95

M024

24-Aug-95

3

-2

4

tt

10

Time

F B N u m b e r Order Comments 1

1

1 'I

I

1

1

.1

1

1 'I

'I

'I

1 1

1

3 - The ORGANIC file consists of benzene,toluene, ethylbenzerre,m&p xylene, o-xylene, decane,o-dichloro benzeneand naphthalenedata for samples,seeTable 3. 4 - The ANIONS file consists of fluoride, acetate,propionate, formate, bromate, chloride, nitrite, bromide, nitrate, carbonate,sulfate,phthalate and phosphate data for samples,see Table 4. 5 - The CARBON file consistsof values for DIC, del 13C (DIC), CH4 and the standard variancesof thesedata, seeTable 5. 6 - The BROMIDE file consistsof low level bromide data for samples,seeTable 6.

o E o o

a o I

d

t

o L

a

|!

t<

J GE l :

cEz o

€E

2!

"^

7F !a

:s

9E

x:a

o

;o: o65

€; a7 9*

0? c:

AJ

U

2

(/)

z

XO 6g

d5a

2;

:s

t

Z

!

:;* EX=

q)

-

t^ tsF

s4

d5

Ela

!:c uas

t-. rt

! -l

U Ft

[=

(,,

o; ov

rF-r

lr

5e

vl c

(. F d

a) (-

!a

tr< .f. q)

F

.

F

I t

)

;

i:

!t! o

q

't _6

o

E c

o

o o

o

lJq

o

orr

E E o

'=v

(Jc

5g L

t!

(o rL

(D tL

F

F

r

F

@ o A

@

o

o

m o o

t

o^ o=

F

(D I

(I)

d) L

F

F

F

F

a o

@

a

*g CJE c

o o

@

"o: .9- d

r

N

-i

o

N

zt^/.

"X

T3o (JS ^-o

q)

r

r

o;

oi

@

@

a N u'l o

_o

'e?

0.)

@ N

:

N

N N

N N

o

q o

o

^

a a

N

;o

c

':

o N

I]

z

o 01 ol

O

(,

N

N

o J

a/.

N

N

N

F.

-

r o

N

o o

n o

o

v

N o

P

o

N

Fi

U

o E

i:

-

O

o

ti ct) OJ

o tr

-.

6 o

o

n o

o o

n o

@

N

N

o o

0 O

6 o

N

N

@

N

N

N

N

N

N

Lr

i: n o o o o o G

@

u? o

J

0.)

A

N N

3

(' F .^. ul

o

frl o

o

o o

9V

N

g o= 65

L

:

:

o9

-.

ti -l l-. (J -1

N

b

A

r) OJ

q o

o @

yo

g^= 0-)

o @

f,

l

o n o o o o o o o l

l

o o o

\.o q.)

f,

6

I-r

Li

o

o

N

N

N

N

N

o o

o N

(o

o

U)

o

7-1

N

O

o

N

o

7 - The FIELD file consistsof pH, temperatureand dissolvedoxygendata for samples,see TabIe7. 8 - The IRON file consistsof ferrousiron (Fez*;datafor samples,seeTableB.

Table 7. The structure of the FIELD file.

In d e x

S n a p s h o t Order

1

-2

2

-2

Date

Time

Well

Level 'I

M 0 11

?

pH

Temperature D O Comments (c) {mo/l) 5.120 26.400 2.:zuJ

M 0 11

2

5 . 12 0

zb.4uu

2.200

MO

a

5.280

Zb.JUU

2.300

4

-2

M011

I

5.ZUU

26.300

2.300

5

-2

MO 1

14

5.420

25.500

0.800

M0.11

lo

5.4:/U

25.500

5.080

2 6 . 10 0

0.800 /uv

C.U6U

26.100

1.700

5 . 17 0

26.000

Z.dU!

ti 7

-z

8 I

MO MO J

1

-2

MO

R

TableB.The structureof the IRON file.

Index

S n a p s h o t Order

Date

Time

Well

Level

Fe2 (ms/l)

-t

I

M 0 11

-2

I

MO

2

3

I

MO

8

0.0

4

1

MO

q

0.u15

1

MO

14

0.373

MO 1

to

0.373

'I

MO J

1

0.018

1

M O3

2

0.0tu

1

M{J13

I

U.UUV

2

5

-z

6

-2

7

-t

B 9

-z

7-8

0.018 0.018 q

Comments

9 - The HYDROGEN file consistsof hydrogen data for samples,seeTable 9.

Table9. The structureof the HYDROCEN file.

lndex

S n a p s h o t Date

Time

Well

4

1 t 1/ t g b

5

1 / 1 7/ 9 6

M038 M038 M038 MO3B M040

1

1t17 /96

z

1 t 1/ / 9 6

3

1/17t96

Hydrogen Comments (nMil)

Level

nd

a

9

nd n0 nd

19 1

{-i

1

1/ 1 7 t 9 6

M040

8

7 8

1

1 t 1 7t 9 6

M043

,l

I

1 / 1/ / 9 b

M043

0.01 3.2 nd

IJ

Tables 1-9 present the recommended file format for the relational database. This file format is completely compatible with other databasesor spreadsheets. The data files were imported into the Microsoft ACCESSTdatabasesystem. An example of an ACCESSTlisting of the data tables developed for this project is shown in Figure 1. At this point, all data tables could be accessedusing relational database functions. Numerous queries were executedon the data tables to ensure that every data entry in the tables had a WELLMAP location and TIMER date/time associated with it. A listing of the queries developed to provide quality control for the data tables is shown in Figure 2. Examples of the use of these queries is given in Figures 3 & 4. A qlrery to compare entries in the ANIONS data table to the TIMER table is shown in Figure 3. In Figure 4, the ANIONS data table is checked against the WELLMAP table. Results from the queries prove whether or not each entry in the ANIONS table has a location and sampling time entry contained in WELLMAP and TIMER. Corrective actions to resolve all data inconsistencieswere performed before proceeding. This phase of the project, data validation, proved to be the most time consuming portion of the proiect.

'7

-Q

{tt\i;:ti

E entot'ts E EROlvllDE E CAREoN E--tr-rELU

El HYDeocEf'r

F rnorr E oRcenrrc

ffiliEEll

ElwELLtunp

tr#,alF}r$A

Alvi 8/91977 50.44AM Table B/9i377 50:47 1 0M 8 / 9 / 9 7i 5 1 : 0 9A l v i T a b l e B / 9 / 377: 5 1 : A B l s / s 71 1019 . 0 7 8 / S / 9 71 0 : 0 90 5. T a b l e B l 9 i s 7 10r5 . 5 .1. B l S l 9 71 10 5 : 4 6. . . T a b l e 8 / 3 i9 73 : lB : 4A 9 M B / 9 i9 73 : 1 8 . 4A9l v l T a b l e B / 9 1 517l : 0 6 : 1. 9 . B / 9 / 5 71 l : 0 61 4. T o b l e 7 1 2 6 11917: 2 5 : 4 37 l ? 6 1 5 7 1 1 2 5 4 2T. a b l e 8 / 9 / 9 I7l : 0 65 6 8 / 9 / 9 711 0 55 5 Table 7 t 7 / 9 7 1415 5 7 . 7 1 7/ t 7 1 l : 4 5 : 5 .4 T a b l e

Figure l. A IUICROSOFTACCESSTdatabase listingof datatablesdevelopedfor this project.

@ Rt'ttot,tt

# nruroruw 6 enoutorw F eRolur ff c,aaeotlr @ claBoru,,i'l @ nrror ff nrLotl,t fl Hvaocrruw @ rnol.rr F tRotlw @ unago

#EWf,

# oacLtltclv

B i9 i9 78 3 5 : 4A8 M 7/ 1 2 1 547: 4 0 1.2. . O u e r yS: A.lvl 612?197 2.5529... Ouery: B/9/977.5146 S Al'A 6120/97 2:02:39 ... Ouery: B/9/377:5453 S Alvl 7/12157 5 10.32 ... Query: B/9/97!.?B:24 S . u e r yS: 8 i 9 l 9 i 9 : 3 1 ' 2 5 A7M1 1 2 / 547: 0 4 . 3 2 . Q : 0 0 : 3.4. . O u e r yS 8 i9 i9 i i 5 53 2A M 6 l Z S l SlT 8 i9 / 9 i1 04 1 : 3.6. 7 l ? 6 / 5l7: 3 0 : 5 3 Q u e r yS: A3M O u e r yS: B i9 i9 77 5 8 : 3A7 h / 8 1 9 / 977: 5 6 : 3 8 / 9 / 9 78 : 2 3 : 5A3M 6 l ? 7l S 79 : 12 : 4 3 O u e r yS: ; B l S l S1 T0 . 4 3 . 2 3 8 1 3 1 9170 : 4 1 : 2 9 O u e r yt : S,l 1 10 4 : 5 3. O u e r yS: r i i l + 3 i9 / 9 78 2 75 1A . l v l7 1 71 9 7 4 2611Ptrt Ouery: S4! "i 8/2i378 0C56PNif B12/97

... or"'y, S:jJ:::${+rffi";..:, 8/9/979 0352AM 6121 /s723A:26 Al'/ 6121 .. Ouery: B/9i978:28:55 S'9':lj*-'lrygtfS*i lS?9:30:34

' , . :' .- ;r - - ' "' : - ' l

. " .: J1:ti,r ;,".' .1r i:ii,' * :'i

!.!r:;:",:]':|li1|':''!)|a''xi...':.;..;,r.;j;:".,']r;:|::441tr)it.l:,:.;:.|$||i1

Figure2. The list of queriesdeveloped andqualitycontrolof dataentries to provideconsistency in the MICROSOFTACCESSTdatabase.

t -10

,Bxi$i+1i;t:.!,iil.,iriLi?tt?;-::iiil$qi d-Iitwli4td l-1

-'

':'^:-

" ".'"

Sn ANIONS

Figure3. The designof the queryfor checkingtime recordsof the datain the ANIONS file.

*- "- .. ....-.1.., .j.;

.*;;.r,.',1:

Figure4. The designof the queryfor checkingthe locationrecordof the datain the ANIONS file.

1tl

III - Target of the Database Once the data tables contained in the Microsoft ACCESSTrelational databasehad been thoroughly checked for completeness,the data were ready for query development. Queries are databasefunctions enabling one to organize and filter data. A query functions as a sieve into which the data is placed. Any data not meeting the selection criteria pass through the sieve. The relational databasetakes the retained data, processesit into tables or reports, and readies the data for export to other graphical, statistical or numericai software packages. A query can be used as a powerful data processing tool to help one visualize plume behavior. Figure 5 shows the construction of a query that accessesthe IRON and the FIELD data tables. The query selectsall samples for which both IRON and FIELD data exist and then attaches the appropriate sampling time and location to each entry in the output table. The results of this query are presented in Table 10. The results shown here are in Microsoft EXCELT format; however they couid be exported in any format to accommodate the needs of other users.

Figr-rre 5. The designof the qllery which mergesthc WELLMAP, FIELD, IRON ANdTIMER files. '7 1.'

Table 10. Output table as the result of merging the WELLIvIAP, FIELD, IRON and TIMER filesin thedarabase. Inoex

Well

LCVEI

Snapshot -z

x(m)

1

MU'I

I

M 0 11

M 0 11

8

-z

A

M011

9

'z

0 1

1

0. 0

z(mJ

Y(m)

5.8

55.67 55.92

5.8

5.8 5.U

5.U 'I

Date

Fe2 (mo/l) 8t24/95 0.0 8

n l-l

(c)

5 . 12 0 5 . 12 0 5.280 5.280 5.420

0.0 I

5/.45

8t24/95

57.7

8t24/95

0.0 5 0.0 0.373 0.373 0.018

5.42t)

5.080

u.0tu

5,UdU

2 6 . 10 0 26.100 26.000 26.000 26.400 zt.5uu

M 0 11

l4

)d.y /

a/24t95

M U ' I1

to

-2

0 . 11

5.8

5Y.4U

8t24/9

7

'I

-2

3.82

2

5.76 5.76

J.62

55.83 56.08

Bl24l95 8/24t95

9

M 0 13 M 0t 3 M013

5 ./ b

J.dZ

5/.bl

8/24t95

U.UU9

5 . 17 0

10

M01

9

-z

5.76

J.dz

8/24t95

0.009

5.t/u

1l

M013 M014

14

-2

t6

3.82

57.86 59.13

8t'24/95

J. IbU

z.4v

54.44

8/24/95

0.006 0.026

12

26.400 za.4oQ

a/t4/95

6 8

Temperature DO

5.220

(mo/l)

2.200 z.zuu

Zb,JUU

Z.JUU

26.300

2.300

25.500

0.800

z5.5UU

0.800

'r./00 1.700

2.800

2.800 z.l ut.) Z.6UU

Conclusions This project resulted in the development of a working relational database that uses Microsoft ACCESSTsoftware. The databaseprovides an important tool for processing laboratory and field data generated by the Natural Attenuation Study conducted at Columbus AFB. Queries generated during this project have already helped aid visualization of the natural attenuation processes,and allow Environics scientists to more effectively plan future sampling missions. The increasedaccessibility of the data, coupled with the data query capabilities of ACCESST,make this an invaluable tool for efficientiy processing data from the natural attenuation study.

1

1a

t-t)