A relational database for the monitoring and analysis of watershed hydrologic functions: I. Database design and pertinent queries

Abstract

The need to monitor water quantity and quality has increased dramatically in recent years due to total maximum daily load requirements that address non-point source pollutants in our nation's water bodies. This has resulted in the need for data management techniques and tools to manage the vast amount of new hydrologic data being collected. Data must be stored, checked for errors, manipulated, retrieved for analysis, and shared within the hydrologic community. The Watershed Monitoring and Analysis Database is a relational database application developed as a data management tool to efficiently and accurately address the needs of individuals and groups responsible for maintaining hydrologic data sets. Stream flow, water quality, and meteorological data can be stored and manipulated within the database. Both remedial and advanced tasks can be simplified with the help of the user interface application, such as quality assurance/quality control (QA/QC) calculations, application of correction and conversion factors, retrieval of desired data for advanced analysis, and data comparisons among multiple study sites. Web integration and local area network (LAN) database synchronization can be supported depending upon the database engine used. The objectives of this paper are to: (1) present in detail the database architecture, including table structures and overall database design, and (2) provide useful queries to retrieve data that involve calculations, comparisons, and basic QA/QC protocols. Developed using Microsoft Access, the concepts and strategies covered in this paper may be applied to any commercially available relational database.