Link to USGS home page
Thumbnail of cover and link to the cover page

Rhode Island Water Supply System Management Plan Database (WSSMP—Version 1.0)

By Gregory E. Granato

ONLINE ONLY

Open-File Report 2004-1231

ABSTRACT

In Rhode Island, the availability of water of sufficient quality and quantity to meet current and future environmental and economic needs is vital to life and the State's economy. Water suppliers, the Rhode Island Water Resources Board (RIWRB), and other State agencies responsible for water resources in Rhode Island need information about available resources, the water-supply infrastructure, and water use patterns. These decision makers need historical, current, and future water-resource information. In 1997, the State of Rhode Island formalized a system of Water Supply System Management Plans (WSSMPs) to characterize and document relevant water-supply information. All major water suppliers (those that obtain, transport, purchase, or sell more than 50 million gallons of water per year) are required to prepare, maintain, and carry out WSSMPs. An electronic database for this WSSMP information has been deemed necessary by the RIWRB for water suppliers and State agencies to consistently document, maintain, and interpret the information in these plans. Availability of WSSMP data in standard formats will allow water suppliers and State agencies to improve the understanding of water-supply systems and to plan for future needs or water-supply emergencies. In 2002, however, the Rhode Island General Assembly passed a law that classifies some of the WSSMP information as confidential to protect the water-supply infrastructure from potential terrorist threats. Therefore the WSSMP database was designed for an implementation method that will balance security concerns with the information needs of the RIWRB, suppliers, other State agencies, and the public.

A WSSMP database was developed by the U.S. Geological Survey in cooperation with the RIWRB. The database was designed to catalog WSSMP information in a format that would accommodate synthesis of current and future information about Rhode Island's water-supply infrastructure. This report documents the design and implementation of the WSSMP database. All WSSMP information in the database is, ultimately, linked to the individual water suppliers and to a WSSMP "cycle" (which is currently a 5-year planning cycle for compiling WSSMP information). The database file contains 172 tables—47 data tables, 61 association tables, 61 domain tables, and 3 example import-link tables. This database is currently implemented in the Microsoft Access database software because it is widely used within and outside of government and is familiar to many existing and potential customers.

Design documentation facilitates current use and potential modification for future use of the database. Information within the structure of the WSSMP database file (WSSMPv01.mdb), a data dictionary file (WSSMPDD1.pdf), a detailed database-design diagram (WSSMPPL1.pdf), and this database-design report (OFR2004-1231.pdf) documents the design of the database. This report includes a discussion of each WSSMP data structure with an accompanying database-design diagram. Appendix 1 of this report is an index of the diagrams in the report and on the plate; this index is organized by table name in alphabetical order. Each of these products is included in digital format on the enclosed CD-ROM to facilitate use or modification of the database.

CONTENTS

Preface

Abstract

Introduction

Database Specifications

Data-Model Specifications

Software Specifications

Database-Design Concepts

Table Design in a Relational Database

Naming Conventions

General Naming Conventions

Table-Name Prefix Conventions

Table-and Field-Definition Conventions

Entity/Relationship Diagramming Conventions

Database-Design Documentation

Database Contents

Plan-Summary and -Tracking Data Structure

Surface-Water-Supply Data Structure

Ground-Water-Supply Data Structure

Treatment-Facility Data Structure

Storage-Facility Data Structure

Pump-Station Data Structure

Transmission-Facility Data Structure

Interconnection-Facility Data Structure

Service-Area Data Structure

Master-Meter Data Structure

System-Production Data Structure

Water-Use Data Structures

Daily Demand

Water Use by Category

Water Use by Major User

Water-Quality-Protection Data Structures

Surface-Water-Quality Data Structure

Ground-Water-Quality Data Structure

Projected Water-Use Data Structures

Projected Water Use by Category

Projected Water Use by Major User

Water-Availability Data Structures

Surface-Water-Availability Data Structure

Ground-Water-Availability Data Structure

Residential-Retrofit-Program Data Structure

Leak-Detection and -Repair Data Structure

Emergency-Management Data Structures

Contact-Information Data Structure

Emergency-Response Data Structure

Financial-Management Data Structure

RIGIS Data Structure

Import-Table Data Structure

Operational Issues and Procedures

Key Assignments and Control

Table Loading Order

Customizing and Extending the Data Architecture

Simplification of Multi-Table Structures

Calculated Fields

Conclusions

References

Appendix 1. Table Index by Figure

PLATE

[On CD-Rom]

1. Selected data structures of the Rhode Island Water Supply System Management Plan database (WSSMP—version 1.0).

FIGURES

1–33. Charts showing:

1. Examples of tables with functional prefixes and their diagram display formats

2. Entities, attributes, and relationships by an entity/relationship (E/R) diagram

3. Plan-summary and tracking data structure tables, fields, and relationships

4. Surface-water-supply data structure tables, fields, and relationships

5. Ground-water-supply data structure tables, fields, and relationships

6. Treatment-facility data structure tables, fields, and relationships

7. Treatment facilities, processes, and water-supply sources tables, fields, and relationships

8. Storage-facility data structure tables, fields, and relationships

9. Pump-station data structure tables, fields, and relationships

10. Transmission-facility data structure tables, fields, and relationships

11. Transmission-line-node data structure tables, fields, and relationships

12. Interconnection-facility data structure tables, fields, and relationships

13. Interconnection-valve data structure tables, fields, and relationships

14. Service-area data structure tables, fields, and relationships

15. Master-meter data structure tables, fields, and relationships

16. System-production data structure tables, fields, and relationships

17. Daily-demand tables, fields, and relationships

18. Water use by category tables, fields, and relationships

19. Water use by major user tables, fields, and relationships

20. Major-user data structure tables, fields, and relationships

21. Surface-water-quality data structure tables, fields, and relationships

22. Ground-water-quality data structure tables, fields, and relationships

23. Projected water use by category tables, fields, and relationships

24. Projected water use by major user tables, fields, and relationships

25. Surface-water-availability data structure tables, fields, and relationships

26. Ground-water-availability data structure tables, fields, and relationships

27. Residential-retrofit-program data structure tables, fields, and relationships

28. Leak-detection and -repair data structure tables, fields, and relationships

29. Contact-information data structure tables, fields, and relationships

30. Emergency-response data structure tables, fields, and relationships

31. Financial-management data structure tables, fields, and relationships

32. Rhode Island Geographic Information System data structure tables, fields, and relationships

33. Import-table data structure tables, fields, and relationships

TABLES

1. Rhode Island Water Resources Board Water Supply System Management Plan contents, legislative mandate, Rhode Island Department of Environmental Management worksheet designations, and current database status

2. List of example queries that demonstrate consolidation of many tables into a single view, queries that determine water rates from data inputs, and queries that can be used to link the Water Supply System Management Plan database to online USGS data



AVAILABILITY

This report is presented in Portable Document Format (PDF).

To view and print report you will need to use PDF Viewer or Adobe Acrobat Reader (available as freeware)

Users with visual disabilities can visit this site for conversion tools and information to help make PDF files more accessible


Printable CD jewel case (1.15 MB)--1 page

Printable tabloid cover (1.14 MB)--1 page

Report (1.17 MB)--86 pages

Download zip file containing data dictionary and RIWSSMP database

Download Plate, 36 by 48 inches, ARCH E, 18.25 MB


The citation for this report, in USGS format, is as follows:

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


 For more information about USGS activities in Massachusetts-Rhode Island District, visit the USGS Massachusetts-Rhode Island Home Page.




FirstGov button  Take Pride in America button