+------------------------+---------------------------------------------+
| ![](m                  | **SDWIS/STATE Release 8.0 Entity            |
| edia/image1.wmf){width | Relationship Diagram**                      |
| ="2.104861111111111in" |                                             |
| height="1.1875in"}     |                                             |
|                        |                                             |
| United States          |                                             |
| Environmental          |                                             |
| Protection Agency      |                                             |
|                        |                                             |
| Office of Ground Water |                                             |
| and Drinking Water     |                                             |
|                        |                                             |
| Contract No.           |                                             |
| 68-W-99-002            |                                             |
|                        |                                             |
| Task Order No. 017     |                                             |
|                        |                                             |
| Product Control No.    |                                             |
|                        |                                             |
| SDC-0002-017-JB-5002D  |                                             |
|                        |                                             |
| September 9, 2003      |                                             |
|                        |                                             |
| This document is only  |                                             |
| an informal            |                                             |
| communication, not a   |                                             |
| formal SDC             |                                             |
| deliverable.           |                                             |
+------------------------+---------------------------------------------+

**SDWIS/STATE RELEASE 8.0**

**ENTITY RELATIONSHIP DIAGRAM**

**CONTRACT NO. 68-W-99-002**

**TASK ORDER NO. 017**

**Prepared for:**

**United States Environmental Protection Agency**

**Office of Ground Water and Drinking Water**

**Drinking Water Implementation Division**

**401 M Street, SW.**

**Washington, DC 20460**

**Task Order Project Officer:**

**Edward Cottrill**

**Prepared by:**

**Systems Development Center**

**Science Applications International Corporation**

**6565 Arlington Boulevard**

> **Falls Church, Virginia 22042-3000**

**1.0 INTRODUCTION**

The United States Environmental Protection Agency (EPA) Office of Ground
Water and Drinking Water (OGWDW) is responsible for implementing the
Public Water System Supervision (PWSS) Program established under the
auspices of the Safe Drinking Water Act of 1974 (Public Law 93-523) and
the 1996 Amendment to the Act (Public Law 104-183). EPA developed the
Safe Drinking Water Information System (SDWIS)/STATE to enable state
primacy agencies to improve implementation of the National Primary
Drinking Water Regulations (NPDWR) in their jurisdictions.

EPA's OGWDW has developed an entity relationship diagram (ERD) that
reflects the entities and relationships in the SDWIS/STATE Release 8.0
data model.

**2.0 SDWIS/STATE RELEASE 8.0 ERD**

Most of the relationships that exist between the SDWIS/STATE 8.0
entities are displayed. A "‡"symbol inside of an entity indicates that
one (or more) relationship between that entity and some other entity is
not displayed. Appendix A provides a list of subject areas and entity
types. Appendix B lists all relationships that are not displayed by
subject area and entity; almost all relationships are displayed. Users
should consult this listing to verify the existence of a relationship
that may not be shown for any entity that contains a "‡" symbol. All
SDWIS/STATE 8.0 entities are displayed except for those that fall into
the "Upload" and "Migration" subject areas. These entities, which
respectively support *Migration to SDWIS/FED* and *Migration to
SDWIS/STATE*, keep only temporary data and contain no relationships.

The attached .PDF file contains the SDWIS/STATE 8.0 ERD. You will need
to expand it to at least 800 to view the entity and relationship names.
You may wish to print the ERD out on a large 34 by 44 inch sheet using a
plotter. Please note that the term "DB2" in the name of the model that
prints out (OGWDW PWSS V08.00D DB2) is not relevant; this ERD can be
used by any SDWIS/STATE 8.0 user.

**APPENDIX A**

SDWIS/STATE Release 8.0 Entity Relationship Diagram---Data Model List

Subject Area +-OGWDW_PWSS_PROGRAM_V08.00D

Subject Area \| +-COMPLIANCES

Subject Area \| \| +-ACTIONS

Entity Type \| \| \| ACTION_TYPE \...

Entity Type \| \| \| ASSISTANCE_ACTION \...

Entity Type \| \| \| WATER_SYSTEM_ASSIST_ACTION_ASGMT \...

\| \| +-

Subject Area \| \| +-COMPLIANCE_SCHEDULES

Entity Type \| \| \| ACTIVITY_TYPE \...

Entity Type \| \| \| COMPLIANCE_SCHEDULE \...

Entity Type \| \| \| SCHEDULE_ACTIVITY \...

\| \| +-

Subject Area \| \| +-ENFORCEMENT

Entity Type \| \| \| ENFORCEMENT_ACTION \...

Entity Type \| \| \| ENFORCEMENT_CASE \...

Entity Type \| \| \| PUBLIC_NOTIFICATION_ACTIVITY \...

Entity Type \| \| \| STANDARD_RESPONSE \...

Entity Type \| \| \| STANDARD_RESP_ACTION_TYPE_ASGMT \...

Entity Type \| \| \| STANDRD_RESP_ACTIVITY_TYPE_ASGMT \...

Entity Type \| \| \| VIOLATION_ENF_ACTION_ASGMT \...

\| \| +-

Subject Area \| \| +-REVIEWS_AUDITS_AND_EVALUATIONS

Entity Type \| \| \| DEFICIENCY \...

Entity Type \| \| \| DEFICIENCY_SCHEDULE_ACTY_ASGMT \...

Entity Type \| \| \| SITE_VISIT \...

Entity Type \| \| \| SITE_VISIT_DEFICIENCY_ASGMT \...

Entity Type \| \| \| SITE_VISIT_INDIVIDUAL_ASGMT \...

\| \| +-

Subject Area \| \| +-VIOLATIONS

Entity Type \| \| \| ORIGINAL_VIOLATION_ASGMT \...

Entity Type \| \| \| SIGNIFICANT_NON_COMPLIER \...

Entity Type \| \| \| VIOLATION \...

Entity Type \| \| \| VIOLATION_CODE_OF_REG_ASGMT \...

Entity Type \| \| \| VIOLATION_GROUP \...

Entity Type \| \| \| VIOLATION_SAMPLE_RESULT_ASGMT \...

Entity Type \| \| \| VIOLATION_SAMPLING_POINT_ASGMT \...

Entity Type \| \| \| VIOLATION_STATUS \...

Entity Type \| \| \| VIOLATION_TYPE \...

Entity Type \| \| \| VIOLATION_TYPE_ANALYTE_ASGMT \...

\| \| +-

\| +-

Subject Area \| +-CONTROLLING_INSTRUMENTS

Entity Type \| \| CALC_MCL_VALUE_MP_AVG_ASGMT \...

Entity Type \| \| CCR_SCHEDULE \...

Entity Type \| \| CDS_CANDIDATE_EXCEEDENCE \...

Entity Type \| \| CDS_CANDIDATE_MNTRNG_ASSESSMENT \...

Entity Type \| \| CDS_CANDIDATE_VIOLATION \...

Entity Type \| \| CDS_FACILTY_ANALYTE_LEVEL \...

Entity Type \| \| CDS_MDBP_SUMMARY \...

Entity Type \| \| CDS_ORIGINATING_VIOLATION \...

Entity Type \| \| CDS_PN_ACTIVITY \...

Entity Type \| \| CDS_REPORT_EXECUTION_ERRORS \...

Entity Type \| \| CDS_REPORT_LOG \...

Entity Type \| \| CDS_SAMPLE_RESULT \...

Entity Type \| \| CDS_SAMPLE_SUMMARY_RESULT \...

Entity Type \| \| CDS_SAMPLING_POINT_SUBSCHEDULE \...

Entity Type \| \| CDS_SCHEDULE \...

Entity Type \| \| CDS_SETUP_EXECUTION_LOG \...

Entity Type \| \| DEVIATION \...

Entity Type \| \| DEVIATION_EVENT \...

Entity Type \| \| DEVIATION_SCHEDULE \...

Entity Type \| \| EXCEPTION_ANALYTE \...

Entity Type \| \| FACILITY_ANALYTE_LEVEL \...

Entity Type \| \| MCL_VALUE \...

Entity Type \| \| MONITORING_CONDITION \...

Entity Type \| \| MONITORING_PERIOD_AVERAGE \...

Entity Type \| \| MONITORING_REQMT_CONDITION_ASGMT \...

Entity Type \| \| MONITORING_REQUIREMENT_LOCATION \...

Entity Type \| \| MONITORING_REQUIREMNT \...

Entity Type \| \| MONITORING_WAIVER \...

Entity Type \| \| SAMPLE_SCHEDULE \...

Entity Type \| \| SAMPLE_SCHEDULE_DATE \...

Entity Type \| \| SAMPLE_SCHEDULE_GROUP \...

Entity Type \| \| SAMPLING_PLAN \...

Entity Type \| \| SAMPLING_PLAN_SAMPLE_POINT_ASGMT \...

Entity Type \| \| SAMPLING_POINT_SUBSCHEDULE \...

Entity Type \| \| SAMPLING_POINT_SUB_SSMPA \...

Entity Type \| \| SAMPLING_PT_SCHED_GRP_SUBSCHED \...

Entity Type \| \| SAMP_SCHEDULE_MNTRG_PERIOD_ASGMT \...

Entity Type \| \| SCHEDULE_AOP_ASGMT \...

Entity Type \| \| SCHEDULE_PACKAGE \...

Entity Type \| \| SCHEDULE_PACKAGE_ROLE \...

Entity Type \| \| VULNERABILITY_ASSESSMENT \...

Entity Type \| \| VULNER_ASSESSMNT_GEO_AREA_ASGMT \...

Entity Type \| \| VUL_ASSESSMNT_ANALYTE_ASGMT \...

Entity Type \| \| WAIVER_SAMPLING_POINT_ASGMT \...

Entity Type \| \| WAIVER_WATER_SYSTEM_ASGMT \...

\| +-

Subject Area \| +-INVENTORIES

Subject Area \| \| +-GEOGRAPHIC_AREAS

Entity Type \| \| \| GEOGRAPHIC_AREA \...

\| \| +-

Subject Area \| \| +-POPULATION_GROUPS

Entity Type \| \| \| D_SERVICE_AREA_TYPE \...

Entity Type \| \| \| POPULATION_SERVED \...

Entity Type \| \| \| SERVICE_CONNECTION_CATEGORY \...

\| \| +-

Subject Area \| \| +-TREATMENTS

Entity Type \| \| \| D_TREATMENT_OBJECTIVE \...

Entity Type \| \| \| D_TREATMENT_PROCESS \...

Entity Type \| \| \| D_VALID_TREAT_OBJ_PROC_ASGMT \...

\| \| +-

Subject Area \| \| +-WATER_SYSTEMS

Entity Type \| \| \| ANNUAL_OPERATING_PERIOD \...

Entity Type \| \| \| MONITORING_PERIOD_DEFINITION \...

Entity Type \| \| \| REGULATING_AGENCY_ASGMT \...

Entity Type \| \| \| WATER_PURCHASE \...

Entity Type \| \| \| WATER_SYSTEM \...

Entity Type \| \| \| WATER_SYSTEM_GEO_AREA_ASGMT \...

Entity Type \| \| \| WATER_SYSTEM_GROUP \...

Entity Type \| \| \| WATER_SYSTEM_GROUP_ASGMT \...

Entity Type \| \| \| WATER_SYSTEM_HISTORY \...

Entity Type \| \| \| WATER_SYSTEM_MON_PER_ASGMT \...

Entity Type \| \| \| WATER_SYSTEM_SERVICE_AREA_ASGMT \...

Entity Type \| \| \| WATER_SYS_LEGAL_ENTITY_CONTACT \...

Entity Type \| \| \| WATER_SYS_MP_DEF_ASGMT \...

\| \| +-

Subject Area \| \| +-WATER_SYSTEM_FACILITIES

Entity Type \| \| \| CADASTRAL_LEGAL_DESCRIPTION \...

Entity Type \| \| \| FACILITY_ANNUAL_OPERATING_PERIOD \...

Entity Type \| \| \| LOCATIONAL_DETAIL \...

Entity Type \| \| \| SCREEN \...

Entity Type \| \| \| STORAGE \...

Entity Type \| \| \| TREATMENT_OBJ_PROCESS_ASGMT \...

Entity Type \| \| \| TREATMENT_PLANT \...

Entity Type \| \| \| UNIT_PROCESS \...

Entity Type \| \| \| UNIT_PROCESS_FLOW \...

Entity Type \| \| \| WATER_SYSTEM_FACILITY \...

Entity Type \| \| \| WATER_SYSTEM_FACILITY_CONTACT \...

Entity Type \| \| \| WATER_SYSTEM_FACILITY_HISTORY \...

Entity Type \| \| \| WATER_SYS_FACILITY_FLOW \...

Entity Type \| \| \| WELL \...

\| \| +-

\| +-

Subject Area \| +-LEGAL_ENTITIES

Entity Type \| \| D_USER \...

Entity Type \| \| GOVERNMENT_AGENCY \...

Entity Type \| \| INDIVIDUAL \...

Entity Type \| \| INDIVIDUAL_GOVT_AGENCY_ASGMT \...

Entity Type \| \| LEGAL_ENTITY \...

\| +-

Subject Area \| +-LOOKUP_TABLES

Entity Type \| \| CODE \...

Entity Type \| \| D_PWS_RULE_TABLE \...

Entity Type \| \| ENTITY_INTERNAL_SYSTEM_NUMBER \...

Entity Type \| \| EXTERNAL_SYSTEM_NUMBER \...

Entity Type \| \| OS2_USERID \...

Entity Type \| \| PERMITTED_VALUE \...

Entity Type \| \| SDWISFED_BATCH \...

\| +-

Subject Area \| +-PROGRAMS_AND_PLANS

Entity Type \| \| ANALYTE_LEVEL_RULE_ASGMT \...

Entity Type \| \| CODE_OF_REGULATION \...

Entity Type \| \| GOVERNMENT_AGENCY_PROGRAM_ASGMT \...

Entity Type \| \| LEAD_AND_COPPER_WQP \...

Entity Type \| \| MONITORING_PERIOD \...

Entity Type \| \| PROGRAM \...

Entity Type \| \| PROGRAM_LEGAL_ENTITY_CONTACT \...

Entity Type \| \| PROGRAM_PROJECT_ASSIGNMENT \...

Entity Type \| \| PROJECT \...

Entity Type \| \| PROJECT_LEGAL_ENTITY_CONTACT \...

Entity Type \| \| RULE \...

Entity Type \| \| RULE_GOVERNMENT_AGENCY_ASGMT \...

Entity Type \| \| RULE_SUPERCESSION_ASGMT \...

Entity Type \| \| RULE_WS_MP_ASGMT \...

Entity Type \| \| STATUTE \...

Entity Type \| \| STATUTE_AMENDMENT_ASGMT \...

Entity Type \| \| VIOLATION_TYPE_RULE_ASGMT \...

\| +-

Subject Area \| +-SAMPLING

Subject Area \| \| +-ANALYTES

Entity Type \| \| \| ANALYTE \...

Entity Type \| \| \| ANALYTE_GROUP \...

Entity Type \| \| \| ANALYTE_GROUP_ASGMT \...

\| \| +-

Subject Area \| \| +-LABORATORIES

Entity Type \| \| \| LABORATORY \...

Entity Type \| \| \| LAB_ANALYTE_METHOD_ASGMT \...

Entity Type \| \| \| LAB_LEGAL_ENTITY_ASGMT \...

\| \| +-

Subject Area \| \| +-SAMPLES

Entity Type \| \| \| COLLECTOR \...

Entity Type \| \| \| MICROBIOLOGICAL_SAMPLE \...

Entity Type \| \| \| MILESTONE_EVENT \...

Entity Type \| \| \| SAMPLE \...

Entity Type \| \| \| SAMPLING_POINT \...

Entity Type \| \| \| SAMPLING_POINT_HISTORY \...

\| \| +-

Subject Area \| \| +-SAMPLE_ANALYTICAL_RESULTS

Entity Type \| \| \| MDBP_SUMMARY \...

Entity Type \| \| \| MICROBIAL_ANALYTICAL_RESULT \...

Entity Type \| \| \| SAMPLE_ANALYTICAL_RESULT \...

Entity Type \| \| \| SAMPLE_SUMMARY \...

Entity Type \| \| \| SAMPLE_SUMMARY_RESULT \...

\| \| +-

\| +-

Subject Area \| +-TECHNOLOGIES

Entity Type \| \| STANDARD_METHOD \...

Entity Type \| \| STANDARD_METHOD_ANALYTE_ASGMT \...

Entity Type \| \| STANDARD_METHOD_NUMBER \...

\| +-

**APPENDIX B**

SDWIS/STATE Release 8.0 Entity Relationship

Diagram---Non-Displayed Relationships

+---------+------------+---------------+--------------+--------------+
| **SDWI  |            |               |              |              |
| S/STATE |            |               |              |              |
| RELEASE |            |               |              |              |
| 8.0     |            |               |              |              |
| ENTITY  |            |               |              |              |
| RELAT   |            |               |              |              |
| IONSHIP |            |               |              |              |
| DI      |            |               |              |              |
| AGRAM-- |            |               |              |              |
| -NON-DI |            |               |              |              |
| SPLAYED |            |               |              |              |
| R       |            |               |              |              |
| ELATION |            |               |              |              |
| SHIPS** |            |               |              |              |
+---------+------------+---------------+--------------+--------------+
| **      |            | **FROM ENTITY | **RE         | **TO ENTITY  |
| SUBJECT |            | TYPE**        | LATIONSHIP** | TYPE**       |
| AREA**  |            |               |              |              |
+---------+------------+---------------+--------------+--------------+
| IN      | WATER      | WATER_SYSTEM  | SAMPLES_     | SAM          |
| VENTORY | SYSTEM     |               | ACCORDING_TO | PLE_SCHEDULE |
+---------+------------+---------------+--------------+--------------+
| COMP    | VIOLATIONS | VIOLATION     | IS_ISSUED_BY | GOVER        |
| LIANCES |            |               |              | NMENT_AGENCY |
+---------+------------+---------------+--------------+--------------+
|         | ENFORCMENT | ENFO          | IS_TAKEN_BY  | GOVER        |
|         |            | RCMENT_ACTION |              | NMENT_AGENCY |
+---------+------------+---------------+--------------+--------------+
|         | ENFORCMENT | STAN          | I            | GOVER        |
|         |            | DARD_RESPONSE | S_APPLIED_BY | NMENT_AGENCY |
+---------+------------+---------------+--------------+--------------+
| A       | SAMPLING   | ANALYTE_GROUP | IS_          | MONITORING\_ |
| NALYTES |            |               | SPECIFIED_BY |              |
|         |            |               |              | REQUIRMENT   |
+---------+------------+---------------+--------------+--------------+
| LEGAL   |            | GOVE          | IS_ISSUED_BY | VIOLATIONS   |
| E       |            | RNMENT_AGENCY |              |              |
| NTITIES |            |               |              |              |
+---------+------------+---------------+--------------+--------------+
|         |            | GOVE          | IS_TAKEN_BY  | ENFOR        |
|         |            | RNMENT_AGENCY |              | CMENT_ACTION |
+---------+------------+---------------+--------------+--------------+
|         |            | GOVE          | I            | STAND        |
|         |            | RNMENT_AGENCY | S_APPLIED_BY | ARD_RESPONSE |
+---------+------------+---------------+--------------+--------------+
| CONT    |            | MONITORING    | IS_          | A            |
| ROLLING |            |               | SPECIFIED_BY | NALYTE_GROUP |
| INST    |            | REQUIRMENT    |              |              |
| RUMENTS |            |               |              |              |
+---------+------------+---------------+--------------+--------------+
|         |            | SA            | SAMPLES_     | WATER_SYSTEM |
|         |            | MPLE_SCHEDULE | ACCORDING_TO |              |
+---------+------------+---------------+--------------+--------------+