EQuIS AQS Schema

Apply the AQS Schema (schema.AQS.xme) to the EQuIS database according to the steps outlined in the Update EQuIS Database and Apply Additional Modules/Schemas to a Database articles.

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_DURATION – Used to translate AQS duration codes to actual duration value and EQuIS units, pre-populated from EPA reference values

•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_QA_ASSESSMENT – Stores AQS QA assessment reference values for use in the DT_EQUIPMENT_PARAMETER table.

•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_EQUIPMENT.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. Formerly stored in table AT_EQUIPMENT_LOCATION; those values automatically move to DT_EQUIPMENT when upgrading to AQS 7.22.2 or higher.

•DT_EQUIPMENT_PARAMETER.AQS_ASSESSMENT_CODE – Has Foreign Key referencing the RT_AQS_QA_ASSESSMENT table.

•DT_EQUIPMENT_PARAMETER.AQS_ASSESSMENT_NUMBER

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

•DT_EQUIPMENT_PARAMETER.AQS_PARAMETER

•DT_EQUIPMENT_PARAMETER.AQS_METHOD_CODE

•DT_RESULT.AQS_PARAMETER_CODE

•DT_RESULT.AQS_METHOD_CODE

•DT_RESULT.AQS_QUALIFIERS

•DT_RESULT.AQS_UNIT_CODE

•DT_SAMPLE.AQS_DURATION_CODE – Has Foreign Key referencing the RT_AQS_DURATION table.

•DT_SAMPLE.AQS_POC – Parameter Occurrence Code.

•AT_EQUIPMENT_LOCATION.AQS_POC – Parameter Occurrence Code.

•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.

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.