### Object Group Descriptions

**Group Description**  **Linked Tables** Tables that are linked from external database to bring in the source data. These tables are kept external to this database to minimize the size of this database and to protect against accidental editing of source data. **Initial Processing** A set of queries used to edit selected source data sets and get some fields in shape for further processing. Also has primary query for linking the source data together **Lookup Tables** A set of lookup tables that contain parameter translations as well as benchmark or ELG values for analysis as used herein **Benchmark Data Prep** Queries that extract the data to be analyzed for the benchmark monitoring program analysis. The results of these queries are exported to exce l for further manipulations that are beyond simple SQL operations. **Benchmark Data Analysis** After the external manipulations are performed in excel, the yearly means are brought back into access for further manipulations and reporting summaries. **ELG Data Analysis**  Series of queries used to select and analyze ELG data. Reporting summaries are included.
Object Descriptions **Group Object**  **G rou**  **p Sort Order** 

**Sort Description**  **Date Linked Tables**  PT_STATUS 1 1 PT_STATUS (n=669). Unique list of facilities. PK = PT_ID. Indicator of facility status. Will eventually screen for PT_STATUS = ACTIVE. 9/11/2012

**Linked Tables** RPT_FACILITY 1 2 RPT_FACILITY (n=5,684). Facility report table. PK = REPORT_ID. Relates back to PT_STATUS by PT_ID. 9/11/2012 **Linked Tables** RPT_MONITORING 1 3 RPT_MONITORING (n=42,351). Reported monitoring data. ERPT_DISCHARGE_LOCATION_ID appears to correspond to a unique key for OUTFALL. MONITORING_TYPES has several fields 9/11/2012(important values for this task include “QBM”, “ELG”, and “QBM, ELG”). PARAMETER_NAME needs to be unified. RESULT_DESC is either “Detected”, “Not Detected”, “Not Applicable”, or “Detected but below QL”. SAMPLE_VALUE is either a numeric value or null; however, it is not consistently null or filled in with respect to RESULT_DESC. Thus, it’s not possible to use MLE techniques to estimate the yearly mean. **Linked Tables** RPT_DISCHARGE 1 4 RPT_DISCHARGE (n=3,968) Table has lots of duplicative records. If we ignore “REPORT_ID” and “PERIOD_CHECKED_YN” we can then use ERPT_MONITOR_PERIOD_ID as a primary key (with an effective n =2,688). 9/11/2012

**Linked Tables** PT_MONITORING_REQ 1 5 N=1,154. Links permits to SIC code and sector/subsector codes. It was found that the original Sector column was not always consistent with the sector value from the Subsector column. (So later a query is developed to rectify this.) (Note, that I accidentally edited this table in some versions with a column called Sector_2; however, I’ve now added a query that computes sector_2 instead.) 9/11/2012

**Linked Tables** RPT_MONITORING_SAMPLE_NOTES

RPT_NO_REDUCT_REASON RPT_OUTFALL RPT_REASON RPT_SITE_STATUS_CHANGE 1 6 Not used 9/11/2012

**Initial Processing** RPT_DISCHARGE_r01 2 1 RPT_DISCHARGE_r01 reads in RPT_DISCHARGE to reduce down duplicate records. By ignoring “REPORT_ID” and “PERIOD_CHECKED_YN” we can then use ERPT_MONITOR_PERIOD_ID as a primary key (with an effective n =2,688). 9/11/2012

**Initial Processing** PT_MONITORING_REQ_r01 2 2 Read in data from PT_MONITORING_REQ, and create a “Sector_2” which relies on the SUBSECTOR field and when not available uses nonmissing SECTOR values. 9/11/2012
**Initial Processing** PT_MONITORING_REQ_rELG 2 3 Read in data from PT_MONITORING_REQ_r01. Creates a unique list of permit_ID (PT_ID) and Sector category. 9/11/2012 **Initial Processing** RPT_FACILITY_r01 2 4 Query creating a unique list of facility permit and facility names 9/12/2012**Initial Processing** q_jbh_01 2 5 Data from the following eNOI tables were queried for the purposes of this analysis: PT_STATUS, RPT_FACILITY RPT_MONITORING, ERPT_DISCHARGE_LOCATION_ID, and RPT_DISCHARGE_r01. N

-> 42,351 Requirements include: PARAMETER_NAME <> null PT_STATUS = “Active” RESULT_DESC<>"Not Applicable" MONITORING TYPES = "QBM" Or "ELG" Or "QBM, ELG" 9/11/2012

**Lookup Tables** tbluk_parameter_monitoring 3 1 N=518. List of all chemical names from the monitoring table (Parameter). These values are simplified to Parameter_I and Parameter_II. Parameter_I gets rid of inconsistencies in spelling while Parameter_II gets rid of differences in ‘total’ vs. ‘total recoverable’. Based on an analysis decision we focus on the Parameter_II synthesis 9/11/2012

**Lookup Tables** tbluk_QBM 3 2 N=19. List of benchmarks that are constant (i.e., not a function of hardness). Includes link back to Parameter_II naming convention. ‘Total’ and ‘total recoverable’ aluminum and iron on in benchmarks, but have same concentration value. Therefore total recoverable aluminum and iron are recoded in Parameter_II to ‘not used’ to avoid record duplication. 9/11/2012

**Looku p Tables**  tbluk_QBM_w_Hardness 3 3 N=6. List of benchmarks for 6 metals with hardness

-specific benchmarks. Includes link back to Parameter_II naming convention. 9/11/2012
**Lookup Tables** tb_QBM_required_monitoring 3 4 Identifies the required monitoring parameters for each subsector (n=1583). Data derived from extraction from MSGP permit. 9/11/2012
**Lookup Tables** tbluk_ELG_01 3 5.1 Detailed list of ELG values by SIC, Sector, and Subsector. Includes additional columns simplifying the parameter name (derived from tbluk_ELG_02. 9/11/2012
**Lookup Tables** tbluk_ELG_02 3 5.2 Listing of the conversion of ELG parameter to the simplified parameter name. 9/11/2012 **Lookup Tables** tbluk_ELG_03 3 5.3 Reduction of tbluk_ELG_01 to the Sector

- Parameter

-Statistical basis level. New field, “Apply” added to indicate “no” when statistical basis was for a monthly or 30

-day average. Usually a corresponding concentration was supplied for a daily maximum 9/11/2012for which apply was set to “Yes”

**Lookup Tables** tbluk_ELG_03_Yes 3 5.4 Trimmed down version Tbluk_ELG_03 where Apply = “Yes” 9/11/2012 **Benchmark Data Prep**  q_jbh_02 QBM

**(Exported to Excel for further processing: See QBM Analysis, rev**  **01.xlsx, tab “QBM data”)**  4 1 Links preliminary data pull (from q_jbh_01) with tbluk_parameter_monitoring and to tbluk_QBM in order to link measurements to the benchmarks after correcting for parameter name. (For parameters with constant benchmarks.) Data requirements:
 Non standard reporting periods were removed
 "QBM" Or "QBM, ELG" were kept
 Records with missing or zero concentrations reported as ‘detected’ or as ‘detected but below QL’ were eliminated
 Concentrations for ‘non

-detects’ was recoded to zero an d concentrations for ‘detected but below QL’ was recoded to one-half the quantitation limit. 9/11/2012

**Benchmark Data Prep**  q_jbh_02 QBM Hardness

**(Exported to Excel for further processing: See QBM Analysis, rev**  **01.xlsx, tab “QBM Metals data”)** 4 2 Links preliminary data pull (from q_jbh_01) with tbluk_parameter_monitoring and to tbluk_QBM in order to link measurements to the benchmarks after correcting for parameter name. Same data requirements as q_jbh_02 QBM, but also includes that hardness >0 9/11/2012

**Benchmark Data Prep**  q_jbh_02 QBM_Crosstab 4 3 Frequency table of q_jbh_02 QBM . Provides number of observations by season for each permit/outfall/parameter 9/11/2012 **Benchmark Data Prep**  q_jbh_02 QBM Hardness_Crosstab 4 4 Frequency table of q_jbh_02 QBM Hardness. Provides number of observations by season for each permit/outfall/parameter 9/11/2012

**Benchmark Data Analysis**  tb_QBM_results_01

**(Imported from Excel: See QBM Analysis, rev 01.xlsx, tab “Yearly**  **Avg”) (with records marked with**  **sector =”Z_Overall”).** 5 1 Includes yearly averages derived from spreadsheet “manual” processing Other records are added to this table (from tb_QBM_results_01_amend) in a later step to add sector

specific results. 9/11/2012

**Benchmark Data Analysis**  q_jbh_05a_qbm_fac01

q_jbh_05a_qbm_fac02 5 3.1 Reads in tb_QBM_results_01 and computes the Number of Permits, Number of Annual Averages, and Number of Annual Averages Exceeding BM by Sector

- Parameter. 9/11/2012

**Benchmark Data** q_jbh_05b_qbm_out01 5 3.2 Reads in tb_QBM_results_01 and computes the Number of 9/11/2012**Analysis** q_jbh_05b_qbm_out02 Outfalls.

**Benchmark Data Analysis**  q_jbh_05c_qbm_uni_fac01

q_jbh_05c_qbm_uni_fac02 5 3.3 Reads in tb_QBM_results_01 and computes the Number of Permits with One or More Yearly Average Exceeding BM

. 9/11/2012 **Benchmark Data Analysis**  q_jbh_05c_qbm_uni_out01

q_jbh_05c_qbm_uni_out02 5 3.4 Reads in tb_QBM_results_01 and computes the Number of Outfalls with One or More Yearly Average Exceeding BM

. 9/11/2012 **Benchmark Data**  **Analysis** q_jbh_05d_qbm_combine (key

result) 5 4 Combines the results from (5) 3.1

-3.4 into one table 9/11/2012 **Benchmark Data Analysis**  q_jbh_06a_Unique_Facility_Subsect

or This is the first step in replicating the yearly averages into sector specific results. 5 5.1 Reads from q_jbh_06a_Unique_Facility_Subsector and computes a unique list of permits and subsectors. (One permit may be on multiple rows for multiple subsectors.) n=877 **q_jbh_06a_Unique_Facility_Subsector PT_ID**  **SUBSECTOR** 104142 C.1 104142 C.2 9/11/2012

**Benchmark Data Analysis**  q_jbh_06b_Unique_Subsector_Para

meter 5 5.2 Reads in tb_QBM_required_monitoring and computes a unique list of subsector and monitoring parameter requirements. N=104 **q_jbh_06b_Unique_Subsector_Parameter Subsector**  **Parameter_II CountOfID**  A.1 Chemical Oxygen Demand (COD) 1 A.1 Total Suspended Solids 1 9/11/2012

**Benchmark Data Analysis**  q_jbh_06c_Unique_Fac_Subsec_Par

m 5 5.3 Links 5.1 and 5.2 together creating of permits

– sectors
-- subsectors

– parameter monitoring requirement. N=1641 **q_jbh_06c_Unique_Fac_Subsec_Parm PT_ID**  **Sector SUBSECTOR**  **Parameter_II** 104142 C C.2 Aluminum 104142 C C.1 Iron 9/11/2012

**Benchmark Data Analysis**  q_jbh_06d_Unique_Fac_Sec_Parm 5 5.4 Reduces 5.3 to a unique list of permits

– sectors – parameters. N=1532. **q_jbh_06d_Unique_Fac_Sec_Parm PT_ID**  **Sector Parameter_II**  104142 C Aluminum 104142 C Iron 9/11/2012

**Benchmark Data Analysis**  q_jbh_07a_results_amend 5 5.5 Links tb_QBM_results_01 (when first imported from the Excel spreadsheet) to q_jbh_06d_Unique_Fac_Sec_Parm; and creates tb_QBM_results_01_amend

. 9/11/2012Note: This created table is manually appended to tb_QBM_results_01 ... so the table has more records. Be sure that we only do this once, as successive application would replicate results. **Benchmark Data Analysis**  tb_QBM_results_01_amend 5 5.6 Created by q_jbh_07a_results_amend . It is a table that is the result of reading tb_QBM_results_01 and matching up with monitoring requirements. These records are manually added to tb_QBM_results_01 to create a table that has data for an overall analysis as well as a sector

-specific analysis. (This is a once off task.) 9/11/2012

**Benchmark Data Analysis**  q_jbh_08a_qbm_output_yrly_details 5 7.1 Links table tb_QBM_results_01 back to a unique list of facility names . 9/12/2012 **Benchmark Data Analysis**  q_jbh_08a_qbm_output_yrly_details

_Crosstab 5 7.2 Presents the number of exceedances by facility and parameter. **(Exported to Excel for further processing: See QBM Analysis, rev 01.xlsx, tab “** **q_jbh_08a ”)**  9/12/2012

**Benchmark Data Analysis**  q_jbh_08a_qbm_output_yrly_details

_Crosstab_Count 5 7.3 Presents the number of yearly averages that were evaluated. **(Exported to Excel for further processing: See QBM Analysis, rev 01.xlsx, tab “** **q_jbh_08a ”)**  9/12/2012

**ELG Data Analysis** q_jbh_03 ELG Data 6 1 Links preliminary data pull (from q_jbh_01) with tbluk_parameter_monitoring and to tbluk_ELG_02 in order to link measurements to the ELGs after correcting for parameter name. (For parameters with constant benchmarks.) Data requirements:
 " ELG " Or "QBM, ELG" were kept 9/11/2012

**ELG Data Analysis** q_jbh_03 ELG Data 02 6 2 Links q_jbh_03 ELG Data to PT_MONITORING_REQ_rELG and tbluk_ELG_03_Yes. Linking to PT_MONITORING_REQ_rELG replicates measurements if the facility belongs to more than one sector. Linking to ELG_03_Yes links the measurements to the ELG concentrations for comparison. An exceedance is flagged if a reported “detected” measurement is greater than the ELG concentration. (For pH there is a special calculation to check for outside range of 6

-9.) Nondetects, <QL, and detects with no reported concentration are not flagged. 9/11/2012

**ELG Data Analysis** q_jbh_03a facility01

q_jbh_03a facility02 6 3.1 Read q_jbh_03 ELG Data 02 and compute the number of facilities evaluated for each sector and parameter. 9/11/2012**ELG Data Analysis** q_jbh_03b outfall01 q_jbh_03b outfall02 6 3.2 Read q_jbh_03 ELG Data 02 and compute the number of outfalls evaluated for each sector and parameter. 9/11/2012 **ELG Data Analysis** q_jbh_03c num_detects01 6 3.3 Read q_jbh_03 ELG Data 02 and compute the number of detected results by sector and parameter. 9/11/2012 **ELG Data Analysis** q_jbh_03d num_gt_elg01 6 3.4 Read q_jbh_03 ELG Data 02 and compute the number of detected results greater than the ELG by sector and parameter. 9/11/2012 **ELG Data Analysis**  q_jbh_03e num_obs 6 3.5 Read q_jbh_03 ELG Data 02 and compute the number of observations by sector and parameter. 9/11/2012 **ELG Data Analysis** q_jbh_03f combine 6 3.6 Combine for output (6) 3.1

-3.6 9/11/2012 **ELG Data Analysis** q_jbh_03h_outp ut 6 4.1 Links table q_jbh_03 ELG Data 02 back to a unique list of facility names . 9/12/2012 **ELG Data Analysis** q_jbh_03h_output_Crosstab 6 4.2 Presents the number of ELG exceedances by facility and parameter. **(Exported to Excel for further processing: See QBM Analysis,**  **rev 01.xlsx, tab “ q_jbh_03a** **”)** 9/12/2012

**ELG Data Analysis** q_jbh_03h_output_Crosstab_Count 6 4.3 Presents the number of measurements that were evaluated. **(Exported to Excel for further processing: See QBM Analysis, rev 01.xlsx, tab “** **q_jbh_03a ”)**  9/12/2012