Data model and relational database design for the New Jersey Water-Transfer Data System (NJWaTr)

by Steven Tessler

Open-File Report 03-197

Prepared in cooperation with the
NEW JERSEY DEPARTMENT OF ENVIRONMENTAL PROTECTION

ABSTRACT

The New Jersey Water-Transfer Data System (NJWaTr) is a database design for the storage and retrieval of water-use data. NJWaTr can manage data encompassing many facets of water use, including (1) the tracking of various types of water-use activities (withdrawals, returns, transfers, distributions, consumptive-use, wastewater collection, and treatment); (2) the storage of descriptions, classifications and locations of places and organizations involved in water-use activities; (3) the storage of details about measured or estimated volumes of water associated with water-use activities; and (4) the storage of information about data sources and water resources associated with water use. In NJWaTr, each water transfer occurs unidirectionally between two site objects, and the sites and conveyances form a water network. The core entities in the NJWaTr model are site, conveyance, transfer/volume, location, and owner. Other important entities include water resource (used for withdrawals and returns), data source, permit, and alias. Multiple water-exchange estimates based on different methods or data sources can be stored for individual transfers. Storage of user-defined details is accommodated for several of the main entities. Many tables contain classification terms to facilitate the detailed description of data items and can be used for routine or custom data summarization. NJWaTr accommodates single-user and aggregate-user water-use data, can be used for large or small water-network projects, and is available as a stand-alone Microsoft® Access database. Data stored in the NJWaTr structure can be retrieved in user-defined combinations to serve visualization and analytical applications. Users can customize and extend the database, link it to other databases, or implement the design in other relational database applications.

CONTENTS

Abstract

Introduction
		Purpose and scope
		Acknowledgments

Development of the NJWaTr data model
		NJWaTr requirements
		The modeling process
		Modeling software

NJWaTr design principles
		Entity/relationship diagramming conventions
		Normalization
		Keys and relationships
		Domain tables
		Naming standards
				Table- and field-name construction
				Table Functional Prefixes

NJWaTr Data Model
		Definition of the NJWaTr core entities
		Core data model
		Site subject area
		Conveyance subject area
		Transfer/volume subject area
		Location subject area
		Owner subject area
		Resource subject area
		System subject area
		Address subject area
		DataSource subject area
		Permit subject area
		Alias subject area
		User-defined details

Operational issues and procedures
		Indexes
		Table loading order
		Maintenance update queries
				Volume unit updates
				Volume conversion to common units and updating transfers
				Conveyance action phrase updates
		Construction and use of views
				Example of view construction
				Standardized and optimized views

Customizing and extending the data architecture

Summary

References cited

AccessibilityFOIAPrivacyPolicies and Notices
U.S. Department of the Interior, U.S. Geological Survey Persistent URL: http://pubsdata.usgs.gov/pubs/of/2003/ofr03197/ Page Contact Information: USGS Publishing Network Last modified: Saturday, January 12 2013, 11:41:06 PM

---