EQuIS AQS Schema

<< Click to Display Table of Contents >>

Navigation:  AQS Module > Installation and Configuration >

EQuIS AQS Schema

Apply the AQS Schema (schema.AQS.xme) to the EQuIS database according to the steps outlined in the Update EQuIS Database article.

 

Note: The EQuIS database must already be updated to EQuIS 7.

 

 

AQS Schema Changes

 

The AQS Schema changes account for the one-to-many relationships between EQuIS and AQS reference values and are necessary for data import and export in EDP.

 

The AQS Schema stores data in the following new tables:

RT_AQS_METHOD – An AQS Parameter-Method combination corresponds to an EQuIS analytic method; rules for data import and export.

RT_AQS_PARAMETER – Relates an AQS parameter code to an EQuIS analyte (CAS number).

RT_AQS_QUALIFIER – Allows storage of AQS qualifier codes separate from EQuIS qualifier codes, as they are typically unrelated to one another.

RT_AQS_UNIT – Relates an AQS unit code to an EQuIS unit code.

RT_COUNTY – This table is also added by the WQX Schema and will already be present if the WQX Schema has been applied to the EQuIS database.

 

The following fields are also added with the AQS Schema:

RT_COUNTY.AQS_COUNTY_CODE

RT_COUNTY.FIPS_CODE (Federal Information Processing Standards)

RT_STATE.AQS_STATE_CODE (Also added by the WQX Schema)

RT_STATE.FIPS_CODE (Also added by the WQX Schema)

DT_RESULT.AQS_PARAMETER_CODE

DT_RESULT.AQS_METHOD_CODE

DT_RESULT.AQS_QUALIFIERS

DT_RESULT.AQS_UNIT_CODE

AT_EQUIPMENT_LOCATION.AQS_POC – Parameter Occurrence Code: One or two digit number identifying a specific monitor for a parameter as specified by EPA's AQS system.

AT_EQUIPMENT_LOCATION.AQS_PARAMETER_CODE – Five-digit number identifying a specific parameter (used in conjunction with the AQS_METHOD_CODE) specified by EPA's AQS system.

AT_EQUIPMENT_LOCATION.AQS_METHOD_CODE – Three-digit number identifying a specific method (used in conjunction with the AQS_PARAMETER_CODE) as specified by EPA's AQS system.

AT_EQUIPMENT_LOCATION.G_VALUE – Value between 0 and 1 used in High Volume Flow field calculations (can change over time for a single piece of equipment) implemented in the EQuIS AQS Collect Templates.

 

The AQS Schema also adds a new database trigger TRG_DT_RESULT_AQS_VALID to help maintain data integrity and accuracy regardless of which EDD Format is used to load AQS data into the EQuIS database. This trigger validates that any data loaded to EQuIS matches the values entered in the EQUIS_* columns (listed below) in the RT_AQS_METHOD table.

EQUIS_ANALYTIC_METHOD

EQUIS_TEST_TYPE

EQUIS_FRACTION

EQUIS_COLUMN_NUMBER

 

The trigger will protect against users making unintentional changes to existing datasets in any of the fields specified above and in the RT_AQS_METHOD table itself. If values do need to be updated, new record(s) with the new changes will need to be added to the RT_AQS_METHOD table first. After this is added, users will be able to edit existing datasets. After existing datasets are updated, if the old RT_AQS_METHOD record is no longer needed, it can be removed.

 

 

Standard EQuIS Schema

 

The following tables and fields exist in the standard EarthSoft Schema, but are important to implement the AQS Module with SPM and/or EDGE.

 

Tables

AT_EQUIPMENT_LOCATION – Track movement of equipment between locations over time. Contains fields to indicate data that changes when equipment is moved to another location (e.g., POC, G Value, Parameter Code, Method Code).

DT_EQUIPMENT – Stores the equipment-related information, including serial numbers, equipment type, deployed location, manufacturer, service dates, and more. Equipment includes monitors, calibrators, cylinders, and samplers.

DT_EQUIPMENT_PARAMETER – The EQuIS AQS Collect Templates have all been setup to import data into this table, which has a specific PARAM_CODE for each field.

RT_EQUIPMENT_PARAM_TYPE – Used by EDGE.

RT_GROUP – Used by EDGE.

RT_GROUP_MEMBER – Create activity group types that allow users to organize field activities in SPM (e.g., 1 Point QC Annual PE, Annual Maintenance, Monthly Maintenance, Flow Rate Verification).

 

Fields

DT_SPM_PLANNED_ACTIVITY.EQUIPMENT_CODE – Added to allow users to assign activities to specific equipment.

DT_SPM_SCHEDULED_ACTIVITY.EQUIPMENT_CODE – Added to allow users to assign activities to specific equipment.