# Presentation: 784168

## A SUCCESSFUL SYSTEM FOR MANAGING WATER QUALITY AND BIOLOGICAL MONTIORING DATA USING MS ACCESS; CT’S EXPERIENCE

**AKA:  Database-Smatabase:  **
**We don’t need no stinkin’ database**

- Mike Beauchene
- CT DEP
- Shadow IT Division

**Notes:**
Mention data management is something most will agree is very important and necessary and most will agree their method of data management is more than sufficient.  However, based on our experience in CT, without the structure a relational database provides, you may be in for a shock.

## Where this talk will go...

**Pros/cons of different data management systems**
**The nuts and bolts of a relational database**
** ****CT’s ambient water quality data management system**

**Notes:**
3 main componets of this talk

Pros and cons of types of data mgt

The internal workings of a relational DB

Our system and how it works

## This presentation is not ...

**Data management product.**

**...****an infomercial for a product that can be purchased on your credit**
**card at the poster session for 2 easy payments of  $19.95**

**This presentation is not ...**

- ...**to encourage you to develop a relational database so you can better organize, store, maintain, and use your water quality data. **

**This presentation is...**

**Notes:**

Really want to be advocate for starting a relational data management system if you have not one already.

## By a show of hands....

**Who collects samples and then waits patiently for the lab to send the results ?**
**Do you manage these results ?**
**In an electronic format ?**
**Do you enforce referential integrity?**
**Do you “pivot tables” ?**

**Notes:**
This talk should have a bit of something for everyone from no data management system to a DBA who may be interested in how we approached the problem.

## A Basic Data Management System

**Stuff Goes**
**IN**

**Stuff Comes**
**OUT**

**Notes:**
The simplest view of a data management system is 

Input

Output

## A Basic Data Management Slogan

**GIGO**

**Notes:**
Garbage in 

Garbage out

Database gives you exactly what is put in.  A good data management system can be built to include features to reduce inconsistencies like river names, date formats, taxonomic nominclature....

## Examples of Basic Data Management Systems

**Notes:**
The following few slides represent the most common data management systems I have encountered.  Each will have it’s own specific pros and cons. See which type your system resembles most closely.

## Institutional Knowledge

- Usually abundant
- Operates on coffee not oil

- Impossible to summarize
- Difficult to assimilate
- Easily lost due to
- 	Early Retirement
- 	Lotto
- 	Greener Pastures

- “+”

- “-”

**Institutional Knowledge**

**Notes:**
The number 1 most common data mgt system, however, each golden handshake this quality and quantity of this system decreases substaintially

## HARD COPY

**HARD COPY**

**Notes:**
A close second to institutional knowledge is managing data in hard copy printed format.

This system gives has much greater visual appeal over other systems because an entire basement full of boxes of data must mean a lot of work has been done.

## Electronic Files(spreadsheets, documents, etc.)

**Easy to use**
**Easy to distribute**
**Can make a report look good**

**False sense of security**
**Easy to shuffle your data Difficult to summarize**
**Difficult to assimilate**
**Impossible to ask “?”**

- “+”

- “-”

**Electronic Files****(spreadsheets, documents, etc.)**

**Notes:**
Due to the ease of spreadsheet use, graphing capability, and ability to attach to an email, these are very popular for the masses.

The downside is that even the most experienced spreadsheet technician can corrupt a large quantity of data with a single click of the mouse.

It is also difficult to change the name of an organism when it may occur in any number o 300 individual spreadsheets.

## Relational Database

**Moderate learning curve**
**Get what you ask for**
**Still need to know your data**

**Easy to share data**
**Stores lots of metadata**
**Answers complicated “?”**
**Keeps the data safe and secure**
**Never loses or shuffles results**
**Links to mapping software**
**Allows you to sleep at night**
**Helps you look really good**
**Stays with the agency when staff does not**

- “+”

- “-”

**Relational Database**

**Notes:**
To the Grail

We have MS Access simply due to the Agency policy.  All of our workstations have MS Office in Windows 2000.  Access is right in there with word, power-point, and excel.

It is not the be all end all of DB, but way better than previous options.

## The nuts and bolts of a Relational Database

**The nuts and bolts of a Relational Database**

**Notes:**
The next few slides are to explain some important items about a relational database.

This illustrates how our macroinvertebrate data are stored in our system.  This picture should make more sense later in the presentation.

## WHAT ARE THE NUTS?

**Tables **
- Place holders for information
- Organize the information by similarity
- Store the information
**Queries**
- make demands upon the tables
- manipulate data into ratios, indices, calculations
- Add, update and delete records in a table(s)

**Notes:**

There are 2 major items in a relational database

Tables

Queries

## WHAT ARE THE NUTS?

**Forms **
- User friendly version of a table(s)
- Can be a more convient way to enter data
- Main form sub form
- Can use features to help data entry
- Pick lists
**Reports**
- User friendly version of a query
- Print out of data
- Make labels for sample containers
- Send data to the public

**Notes:**
2 other items based on these 2 objects are

Forms

Reports

## WHAT ARE THE BOLTS?

**Referential Integrity**
- Rules that allow your tables to play nice together
**Primary Key**
- A field(s) that makes each row unique
**USE A NON-INTELLIGENT CODE**

**Notes:**
Referential integrity is a set of rules you develop and enforce on your data

For example:  when getting dressed in the morning you must put your pants on before your shoes.

A primary Key or key field is something in the table that will make each record unique.  The type of key is a non-intelligent number, however, sometimes, the combo of several fields may be better.

For example, think of your set of keys, car and home may be different enough, however, the dead bolt and the knob lock may not, so you add a feature to differentiate each. 

## WHAT ARE THE BOLTS?

**Input Mask / Validation Rule**
**Templates for data entry**
**Dates/times**
**Appropriate values (between 1.0-14.0)**
**Cascading Updates & Deletes**
**Global changes to a dataset**
**Change a name, sample number, station name**
**Remove an entire set of data for a sample**
- .

**Notes:**
Extra added bonuses are templates you can put on any particular field. These are great to help reduce inconsisencies with problematic info like a date format, time format, abbrevations like north, west, as well as double checking any typing input such as a pH value of 75.0.

Cascading update or delete is very powerful in that the large sets of records can be changed or deleted in one click of the mouse.  For example, you realize the cut and pasting of the name of an organism has been missing an extra “I” in the species name, you can correct the spelling once at the first occurance and all subsequent occurances will also change.

## HOW DO THE NUTS AND BOLTS GO TOGETHER?

**“****Raw Data” = “Result”**
**Dissolved oxygen = 8.5 ppm**
***Pteronarycs spp*****. = 12 individuals**
***Fragilaria leptostauron***** = 5 cells**
**Instantaneous discharge = 152 cfs**
**“****Metadata” = “Attributes or info to describe a result”**

**Notes:**
So all well and good with the structural mumbo jumbo..

What about the data

Data or “results” is the most important for water quality decisions, but more important before the decisions should be made is to ask questions of the data, are all from the same site, are all the same collection method, are all the same analytical lab, analytial method, unit of measure... 

## MORE ON METADATA

**You can never have too much!!!**
- Provides info to a secondary data user
- Establishes data quality
- Used in queries
- Manipulate data
- Restrict or define data limits
- Describe data
- Jogs your memory when some asks:
-  Where
- When
- What
- Why

**Notes:**
Metadata or info about the data is extremely valuable when you start to have gaps in time from when the data was collected to when it is going to be used.

It can help to insure data are included or excluded based on different goals objectives and user requirements.

I

## “We do not care as much about the accuracy of a result contained within as we do about not having enough information about the result....

 ...the metadata allows the secondary user to make the appropriate decision as to whether or not the data will be meaningful for their application.”

Bob King and Lee Manning
 STORET Architects and founders.

*** ...******the metadata allows the secondary user to make the appropriate decision as to whether or not the data will be meaningful for their application.”***
***Bob King and Lee Manning***
*** ******STORET Architects and founders.***

**Notes:**
In 1998 I attended a STORET training session in Boston.

The 2 founders of the national wq db did an outstanding job of demonstrating their db and how to use it.
They blew me away with this comment.  
Accuracy of the result was not as important nor did it matter as much as the associated metadata.  

In order to capture even minimal amounts of metadata it was quickly apparent my spreadsheet based system of storing data was woefully inadequate and more importantly inappropriate

## Connecticut’s Data Management System

**Notes:**
Now that you are chomping at the bit to go back to work and create a relational database, the next few slides will focus on the system I developed for our ambient water quality monitoring program.

## Then (Pre 1998)

**Then (Pre 1998)**

**NO AGENCY IT SUPPORT OR VISION   (only Ernie’s)**
**Existed as**
**Institutional knowledge**
**Hard copy**
**Lotus/SAS/word perfect format**
**STORET as an option?**

**Notes:**
The pre relational data management system era is a recent as my hire in july of 1998.  Prior to this, Ernie et al used all of the previously described systems except for a relational database.  STORET was always on the radar screen as a potential option to manage our data.  The limitation was STORET data entry was beastly and the batch update function was equally daunting.

## Between Then & Now

**Between Then & Now**

**NO AGENCY IT SUPPORT OR VISION   (only Ernie’s)**
**The relational ambient monitoring database started in July of 1998 using MS Access 2.0**
**It was based upon the STORET model**
**It would function as our day-to-day working database with periodic uploads to STORET**
**Staff begin to create innovative nick-names for the DBA (me).**

- Then

- NOW

**Notes:**
One of Ernie’s first orders was to use the recently aquired MS Access (DEP agency standard) to develop a simple relational database based upon required fields in the STORET database.  A line was drawn in the sand that as of fall 1995 all water quality data would be stored in this database.

I was responsible for getting all of the data from hard copy or electronic copy into this database.

With a bunch of new hires in 2002-03, and an intense summer time sampling schedule, I was quickly swamped.

Just entering metadata and results rapidly became 1 FTE or more, and this was only supposed to be 1⁄2 my time.

I developed a series of forms and with managements blessing shifted the task of entering metadata to the project manager.  The name calling begins.

## NOW (2006)

**NOW (2006)**

**Our Agency IT calls us “SHADOW IT”**
**WE Have a data management policy**
**reduce reliance on all other data mgt systems**
**MS Access 2000 **
**Front end for staff **
**Data input forms**
**Generic buttons for query options **
**STORET  has...**
**Monitoring stations**
**Beach monitoring data**
**Lots more to do**
**The DBA (me) has been removed from staff Christmas card lists**

**Notes:**
I have been tagged as “Shadow IT” and aptly so I am not a DBA just an EA-Biology.  Only a DBA out of complete and utter desperation.

The key function of our system is security and staff data in staff data out.

My colleagues have many bad nick-names for me most of which are completely inappropriate and offensive

## CT’s Data Management System

**Stuff Goes**
**IN**

**Stuff Comes**
**OUT**

- Trip Info

- Site Info

- Sample Info

- Results

- Raw Data

- Overdue results

- WQS Exceedances

- Project $$

- Summary Calculations

- QA

**Notes:**
In our system metadata and results are entered and stored. The main categories of meta data are trip info, site info, sample info. Results are on their own and come in to the db from both internal and external labs.

The stuff that can come out is the data filtered by many different options, summary stats, cost of sample events, samples collected but not processed or not reported, on and on...

## CT’s Relationships

**Site Info**

**Stuff Going IN**

**Notes:**
Date, collectors, rationale, flow, comment

Name, location, lat long, town, watershed, ...

Sample info stuff like depth, where in the channel, container type, field method, gear used, lab sent to, sent for, lab number 

Results, nutrients, metals, macroinverts, fish, field measures, etc.

## CT’s  Relational Database Is..

**Just Like A Pizza!!!!**

## Hierarchal Relationships

**Hierarchal Relationships**

**Notes:**
In order to have a successful pizza there is an appropriate sequence of events.  Dough, toppings, cheese, cook.  No pizza if the all componets are there and assembled in the appropriate sequence

Back in database speak
No copper data unless a sample has been collected and analyzed.  No sample if you did not go to a sample location. And no sample location if you did not leave the office.

## Data Management In CT- Now

- Our database has....
- Station information and lat. & long. (1800 sites)
- Physical/Chemical (175,000 data points)
- Macroinvertebrate (32,000 names & counts)
- Fish (267 samples 12,000 records)
- HOBO water temp. (lots and lots)
- Lots of other stuff
- Our system....
- Is an electronic log book of all samples collected
- Is linked to ADB for 305(b) assessment updates
- Is linked to ArcGIS/ArcView for mapping
- Can be linked to SIM for uploads to STORET
- Needs IT support to go become a real data management system

**Notes:**
System is still growing

We need real IT 

Access has limits

Want a web based application

Better ability to upload to STORET

## Slide 29

**Notes:**
Staff have a shortcut on their desktop

Options to put info in 

Options to get info out

## Slide 30

**Notes:**
Step 1 is to log a trip.  Uses pick lists and input masks to keep all of un on the same page.

## Slide 31

## Slide 32

**Notes:**
Part 2 is to log each container used to hold a sample.

The user can scroll, filter, or search for their sample trip and then click the add sample metadata button.

## Slide 33

**Notes:**
The user enters appropriate info for each container from each site sampled on that trip.

This sets up the placeholder for 1 or more results.   Completes their work.

When they come in from the lab, I compare the lab numbers and when there is an exact match the results go in.  If there are results but no samples, nasty email reminders.

## Slide 34

**Notes:**
Once the results are in there are options to take data out.

## Slide 35

## IS NOT A Relational Database

**A SERIES OF WORKSHEETS IN A SPREADSHEET OR A SERIES OF SPREADSHEETS ORGANIZED IN A FOLDER**

## Slide 37

**Notes:**
The biggest visual difference between a spreadsheet of results and a relational database table of results is the layout.

A spreadsheet often has the variables as column headers and each row is a sample observation.  The downside to this is that if parameters are added or not sampled, holes in the spreadsheet start to pop up.

## Slide 38

**Notes:**
In the relational table of results the variables are in the row with the column simply name “chem parameter name”

Data is stored only when there was a result reported.  (ND) is a result

## Slide 39

**Notes:**
The visual of a DB table is cumbersome and not very attractive.  However MS access and MS excel work very well together with a PIVOT TABLE.

An excellent workshop would be Pivot Table 101 but if you have not heard nor used this function please try it out.

## Export to Microsoft Excel & use the Pivot Table tool

**Export to Microsoft Excel & use the ****Pivot Table tool**

**Out**

**Notes:**
A much more attractive and usable output from a relational database result table.

## Take the plunge.....

**Notes:**
So when NEAEB is over 

## START SIMPLE !!!

- TABLE #
**Trips ****	**(date, who, why, what)
**Sites ****	**(id, location, drainage, lat & long)
**Samples ****	**(lab number, field methods, gear,)
**Results** 	(lab number, value, unit, method)

- 1

- 2

- 3

- 4

**Notes:**
Don’t worry about building the ultimate system all at once.  Start small... important tables are

## Define your KEY FIELDS 
The combination of which are will be unique for that record.  USE NON-INTELLIGENT CODING AMAP!

Develop strong RELATIONSHIPS
Enforce REFERENTIAL INTEGRITY
Encourage CASCADING UPDATES

Use validation rules and input masks 
Restricts entry to appropriate values

- Define your KEY FIELDS
- The combination of which are will be unique for that record.  USE NON-INTELLIGENT CODING AMAP!
- Develop strong RELATIONSHIPS
- Enforce REFERENTIAL INTEGRITY
- Encourage CASCADING UPDATES
- Use validation rules and input masks
- Restricts entry to appropriate values

**Notes:**
Make sure each table has Key fields and has referential integrity enforced!!

## Build based on your needs !!!

- Lookup Tables (use in pick lists and queries)
- Staff info
- Method info
- Equipment specs
- Ecological attribute stuff

**Notes:**
Add fields and lookup tables as needed.

## USE YOUR DATA !!!

- Queries and reports
- Quality control/Quality assurance (DQO’s)
- Summary Reports
- Water quality assessments
- Taxonomic distributions
- TMDL development and implementation
- Find where or where not to go fishing
- Share with others
- Budget review and or planning
- Staff performance evaluation

**Notes:**
Use the data to do as much as possible. Tasks that used to take days/weeks or were thought to difficult to accomplish may be a few clicks away.

## Data Management Tools Database vs. Spreadsheet vs. Hardcopy

|  | Database | Spreadsheet | Hardcopy |
| --- | --- | --- | --- |
| Designed to store information | Yes and very efficiently | No, better to use to present and analyze data. | No, One page at at time |
| Stores Metadata | Yes and very efficiently | No Not really | No, One page at a time |
| Prevents mistakes | Yes, If told to do so | No Encourages mistakes | No, Does not speak |
| Query capability 
(Can ask questions) | Yes many often complicated | No Somewhat but limited mostly to calculations | No, does not speak |
| Shareable | Yes everyone has the most recent data at their desktop | Somewhat- must either setup shared folder or email specific files based on request | No, waste of photocopy toner and paper.  Not enough file cabinets.  Can get lost. |
| Makes our job easier | Yes, but can make more work because we use the data
 | Yes if used in conjunction with a database (pivot table, graphs, etc)
 | No, waste of time and effort unless used to QA data in the database.
 |

## TAKE BACK TO WORK MESSAGES

**TAKE THE LEAP!!!!  IT IS EASIER THAN ONE WOULD THINK**
**YOU WILL BE SURPRISED AT HOW MANY INCONSISTENCIES YOU ACTUALLY FIND**
**YOU WON’T BE ABLE TO LIVE WITHOUT ONE**
**EVERYTHING IN THE WORLD TURNS INTO EITHER A “1” OR A “0”**

## The Last Word

- STORET: _[http://www.epa.gov/storet/](http://www.epa.gov/storet/)_
- National Data Standards:   _[http://wi.water.usgs.gov/methods/tools/wqde/index.htm](http://wi.water.usgs.gov/methods/tools/wqde/index.htm)_

**Notes:**
I wanted to do this presentation to excite and empower you to manage your data in a relational database, if not already.  I am not a true DBA so please go to these sources for the best advice

Excellent gold standard information for data management

## Slide 49