USGS Home
Contact USGS
Search USGS

Data Model and Relational Database Design for Highway Runoff Water-Quality Metadata

By Gregory E. Granato and Steven Tessler

U.S. Geological Survey Open-File Report 00–480

Prepared in cooperation with the Federal Highway Administration. A Contribution to the National Highway Runoff Data and Methodology Synthesis.

ONLINE ONLY

OFR 00–480 (441 KB, 34 pages) - PDF file

Database Design plate (564 KB, 1 page) - PDF file

Database Design file (2.36 MB) - Microsoft Access file

Data dictionary (508 KB) - Microsoft Access file

Abstract

A National highway and urban runoff waterquality metadatabase was developed by the U.S. Geological Survey in cooperation with the Federal Highway Administration as part of the National Highway Runoff Water-Quality Data and Methodology Synthesis (NDAMS). The database was designed to catalog available literature and to document results of the synthesis in a format that would facilitate current and future research on highway and urban runoff. This report documents the design and implementation of the NDAMS relational database, which was designed to provide a catalog of available information and the results of an assessment of the available data.

All the citations and the metadata collected during the review process are presented in a stratified metadatabase that contains citations for relevant publications, abstracts (or previa), and reportreview metadata for a sample of selected reports that document results of runoff quality investigations. The database is referred to as a metadatabase because it contains information about available data sets rather than a record of the original data. The database contains the metadata needed to evaluate and characterize how valid, current, complete, comparable, and technically defensible published and available information may be when evaluated for application to the different dataquality objectives as defined by decision makers. This database is a relational database, in that all information is ultimately linked to a given citation in the catalog of available reports. The main database file contains 86 tables consisting of 29 data tables, 11 association tables, and 46 domain tables. The data tables all link to a particular citation, and each data table is focused on one aspect of the information collected in the literature search and the evaluation of available information.

This database is implemented in the Microsoft (MS) Access database software because it is widely used within and outside of government and is familiar to many existing and potential customers. The stratified metadatabase design for the NDAMS program is presented in the MS Access file DBDESIGN.mdb and documented with a data dictionary in the NDAMS_DD.mdb file recorded on the CD-ROM. The data dictionary file includes complete documentation of the table names, table descriptions, and information about each of the 419 fields in the database.

Contents

Abstract

Introduction

Database Design and Implementation

Model Specifications

Software Specifications

The Modeling Process

Design Conventions

Normalization

Keys and Relationships

Definition of the NDAMS Core Logical Entities

Naming Standards

Name Construction

Table Functional Prefixes

Entity/Relationship Diagramming Conventions

Design Documentation

Summary

References

Appendix 1: Use of the Data Dictionary

Appendix 2: Multiuse Domain Table Implementation

This report is available online in Portable Document Format (PDF). If you do not have the Adobe Acrobat PDF Reader, it is available for free download from Adobe Systems Incorporated.

Document Accessibility: Adobe Systems Incorporated has information about PDFs and the visually impaired. This information provides tools to help make PDF files accessible. These tools convert Adobe PDF documents into HTML or ASCII text, which then can be read by a number of common screen-reading programs that synthesize text as audible speech. In addition, an accessible version of Acrobat Reader 8.0 for Windows (English only), which contains support for screen readers, is available. These tools and the accessible reader may be obtained free from Adobe at Adobe Access.

Suggested Citation:
Granato, G.E., Tessler, S., 2001, Data Model and Relational Database Design for Highway Runoff Water-Quality Metadata: U.S. Geological Survey Open-File Report 00-480, 27 p.

For additional information write to:

Director,
USGS Massachusetts–Rhode Island Water Science Center
10 Bearfoot Road
Northborough, MA 01532

or visit our Web site at:
http://ma.water.usgs.gov