 |
Rhode Island Water Supply System Management Plan Database (WSSMP—Version 1.0)By Gregory E. Granato
|
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.
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
[On CD-Rom]
1. Selected data structures of the Rhode Island Water Supply System Management Plan database (WSSMP—version 1.0).
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
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
This report is presented in Portable Document Format (PDF).
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.
| AccessibilityFOIAPrivacyPolicies and Notices | |
  |
|