Rhode Island Water Supply System Management Plan (WSSMP) Database Project

Problem

The State of Rhode Island faces increased and competing demands for its groundwater and surface-water resources. The Rhode Island Water Resources Board (RIWRB) has the regulatory authority to allocate these limited groundwater and surface-water resources. Water use patterns and their effect on water availability are important considerations in allocation of these resources. The RIWRB needs to be able to evaluate water-use patterns and to develop approaches to determine the effect of these patterns on the hydrologic environment. Currently, the USGS is investigating and cataloging water water use and availability in the Wood-Pawcatuck, the Blackstone, the Pawtuxet and the Quinebaug River Basins.These projects are part of a series of on-going projects to evaluate water use and availability in the entire state of Rhode Island by 2004.The RIWRB will need to integrate this water use information with information about the state’s water resources infrastructure to ensure adequate supplies of water for consumers in Rhode Island.

To accomplish this mission a sound database that is built from a relational data model (the database design) is needed. Creating a model that accurately describes the structure of a set of data is a precursor to any correctly designed database (Fleming and von Halle 1989). Logical modeling is the process of analyzing and reducing a set of data to its separate components, establishing the nature and direction of relationships between those components, and thereby building a structure for the data that automatically enforces the business rules that are needed to maintain data integrity and provide easy access to all of the information stored in the database. A properly designed logical-data model provides for change in the data management process. A logical-data model should therefore represent a straightforward generic-structure that easily allows for later corrections and extensions without affecting the integrity of data already residing in the database (Granato and Tessler, 2001).In New England, all water-use data cataloged by the Federal-State water-use program will be available in the New England water-use data system (NEWUDS) (Tessler, 2001).State and local water-use agencies in Rhode Island, however, need water-supply production and distribution information that is not included in this regional water-use database.For example, the availability of water in a developing area may be limited by the water supply infrastructure (for example low capacity pumps, aging--and(or) leaking water mains, or insufficient storage capacity to meet daily peaks in demand) rather than by hydrologic supply in the local aquifer. An integrated water-use and water-supply database would help planners target resources and conservation efforts to ensure adequate water supplies. Therefore, agencies in Rhode Island need augmented version of the NEWUDS data structure.

Purpose

The purpose of this study is to create a data model and establish a relational database design that will intergrate the existing USGS NEWUDS database with a structure that will incorporate information about Rhode Island’s water-supply infrastructure:

1. Design a data model that will efficiently combine information needs.

2. Design a Microsoft Access database as a component or an attachment to the existing USGS, NEWUDS database.

3. Document the design and provide an unpopulated table structure and a data dictionary as a USGS CD-ROM publication for evaluation and use by the RIWRB and other local, state, and federal agencies.

The extended database will either be integrated into copies of NEWUDS that are distributed in Rhode Island, or (preferentially) will be in a new database that is linked to the existing NEWUDS structure in a way that will be largely transparent to the user. At this time, the prototype design will not include a graphical user interface or predefined query structures, but will include all the tables necessary to implement the extended NEWUDS data model Once the model is established, this may be used to build an interface that can be utilized on freestanding personal computers and (or) through the Internet

Relevance and Benefits

The Federal and State benefits from this project include the following:

1. The State of Rhode Island will obtain a single, easily accessible, well-organized electronic interface that will link the USGS water use database for water-use information and the water-resources infrastructure information needed by local, state and federal planners to ensure adequate supplies of water for consumers in Rhode Island.

2.Integration of the water-use information needed at the federal and state levels with infrastucture information needed at state and local levels will ensure the continued viability of a water use database in Rhode island

3. Local, State, and Federal planners can use water use and infrastructure information to most efficiently target resources needed for improving and maintaining Rhode Island's water supply infrastructure.

4. The integrated water-use (NEWUDS) and water-supply system management plan (WSSMP) database will allow planners to assess both water-use and water-supply questions in the planning process.

Products

Granato, G.E., 2004, Rhode Island Water-Supply System Management Plan Database (WSSMP—Version 1.0): U.S. Geological Survey Open-File Report 04-1231, 77 p. Report On-Line

References:

Fleming, C.C. and von Halle, Barbara, 1989, Handbook of relational database design: Reading, Mass., Addison-Wesley Publishing Company, 605 p.

Granato, G.E., and Tessler, Steven, 2001, Data model and relational database design for highway runoff water-quality metadata: U.S. Geological Survey Open-File Report 00-480, 15 p.

Tessler, Steven, 2001, New England water-use data system (NEWUDS)--Data Model and relational database design: U.S. Geological Survey Open-File Report,