Attachment_15._CASTNET_SCHEMA_DDL.SQL

-- ASSUMED_FLOW (Table) -- Row count:114

CREATE TABLE ASSUMED_FLOW

SITE_ID VARCHAR2(6 BYTE) NOT NULL,

FLOW NUMBER(12,8),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_ASSUMED_FLOW

PRIMARY KEY

(SITE_ID)

LOGGING

MONITORING;

COMMENT ON TABLE ASSUMED_FLOW IS 'Assumed hourly flow rates for each site. Used by CASTNet Data Management Application.';

COMMENT ON COLUMN ASSUMED_FLOW.SITE_ID IS 'Site identification code.';

COMMENT ON COLUMN ASSUMED_FLOW.FLOW IS 'Assumed flow rate used to calculate estimated atmospheric concentrations; lpm.';

COMMENT ON COLUMN ASSUMED_FLOW.UPDATE_DATE IS 'Date and time of last record update';

-- CALIBRATION_CRITERIA (Table) -- Row count:16

CREATE TABLE CALIBRATION_CRITERIA

PARAMETER VARCHAR2(30 BYTE) NOT NULL,

CAL_MIN NUMBER(12,8),

CAL_MAX NUMBER(12,8),

VAL_MIN NUMBER(12,8),

VAL_MAX NUMBER(12,8),

CAL_CRITERIA_STRING VARCHAR2(50 BYTE),

VAL_CRITERIA_STRING VARCHAR2(50 BYTE),

PERCENT_DIFFERENCE NUMBER(1) NOT NULL,

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_CALIBRATION_CRITERIA

PRIMARY KEY

(PARAMETER)

LOGGING

MONITORING;

COMMENT ON TABLE CALIBRATION_CRITERIA IS 'Calibration and validation criteria for continuous data. Used by CASTNet Data Management Application.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.PARAMETER IS 'Parameter with which minimum and maximum calibration and validation values are associated.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.CAL_MIN IS 'Minimum calibration value according to CASTNet field calibration criteria.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.CAL_MAX IS 'Maximum calibration value according to CASTNet field calibration criteria.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.VAL_MIN IS 'Minimum calibration value according to CASTNet measurement criteria.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.VAL_MAX IS 'Maximum calibration value according to CASTNet measurement criteria.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.CAL_CRITERIA_STRING IS 'Calibration criteria text for report.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.VAL_CRITERIA_STRING IS 'Validation criteria text for report.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.PERCENT_DIFFERENCE IS 'Indicates that comparisons for the parameter are percent differences instead of actual differences.';

COMMENT ON COLUMN CALIBRATION_CRITERIA.UPDATE_DATE IS 'Date and time of last record update';

-- CALIB_UACODE_CD (Table) -- Row count:2

CREATE TABLE CALIB_UACODE_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_CALIB_UACODE_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE CALIB_UACODE_CD IS 'Foreign key table for UACODE in CALIBRATION_SUMMARY';

COMMENT ON COLUMN CALIB_UACODE_CD.CODE IS 'Code';

COMMENT ON COLUMN CALIB_UACODE_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN CALIB_UACODE_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN CALIB_UACODE_CD.UPDATE_DATE IS 'Date and time of last record update';

-- CLOUD_EVENT (Table) -- Row count:744

CREATE TABLE CLOUD_EVENT

SITE_ID VARCHAR2(6 BYTE),

DATEOFF DATE,

YEAR NUMBER,

SAMPLE_NUM NUMBER(10),

DURATION FLOAT(126),

WS FLOAT(126),

LWC FLOAT(126),

TURBFLUX FLOAT(126),

SEDFLUX FLOAT(126),

TOTFLUX FLOAT(126),

TURBVD FLOAT(126),

SEDVD FLOAT(126),

TOTVD FLOAT(126),

VOLUME FLOAT(126),

PHLAB FLOAT(126),

PHFIELD FLOAT(126),

CONDLAB FLOAT(126),

CONDFLD FLOAT(126),

HLAB FLOAT(126),

HFIELD FLOAT(126),

CA FLOAT(126),

MG FLOAT(126),

K FLOAT(126),

NA FLOAT(126),

NH4 FLOAT(126),

NO3 FLOAT(126),

SO4 FLOAT(126),

CL FLOAT(126),

HUSED FLOAT(126),

HFLUX FLOAT(126),

NH4FLUX FLOAT(126),

CAFLUX FLOAT(126),

MGFLUX FLOAT(126),

NAFLUX FLOAT(126),

KFLUX FLOAT(126),

SO4FLUX FLOAT(126),

NO3FLUX FLOAT(126),

CLFLUX FLOAT(126),

UPDATE_DATE DATE,

CONSTRAINT CLOUD_EVENT_PK

PRIMARY KEY

(SAMPLE_NUM, DATEOFF, SITE_ID)

LOGGING

MONITORING;

-- CLOUD_MONTH (Table) -- Row count:49

CREATE TABLE CLOUD_MONTH

SITE_ID VARCHAR2(6 BYTE),

YEAR NUMBER(10),

MONTH NUMBER(10),

SAMP_FREQ NUMBER(10),

DURATION NUMBER(16,4),

VOLUME NUMBER(16,4),

WS NUMBER(16,4),

LWC NUMBER(16,4),

TURBFLUX NUMBER(16,4),

SEDFLUX NUMBER(16,4),

TOTFLUX NUMBER(16,4),

TURBVD NUMBER(16,4),

SEDVD NUMBER(16,4),

TOTVD NUMBER(16,4),

H NUMBER(16,4),

CA NUMBER(16,4),

MG NUMBER(16,4),

K NUMBER(16,4),

NA NUMBER(16,4),

NH4 NUMBER(16,4),

SO4 NUMBER(16,4),

NO3 NUMBER(16,4),

CL NUMBER(16,4),

HFLUX NUMBER(16,4),

CAFLUX NUMBER(16,4),

MGFLUX NUMBER(16,4),

KFLUX NUMBER(16,4),

NAFLUX NUMBER(16,4),

NH4FLUX NUMBER(16,4),

SO4FLUX NUMBER(16,4),

NO3FLUX NUMBER(16,4),

CLFLUX NUMBER(16,4),

CLDFRAC NUMBER(16,4),

TOTHOURS NUMBER(16,4),

CLOUDHRS NUMBER(16,4),

HDEP NUMBER(16,4),

KDEP NUMBER(16,4),

NADEP NUMBER(16,4),

CADEP NUMBER(16,4),

MGDEP NUMBER(16,4),

NH4DEP NUMBER(16,4),

SO4DEP NUMBER(16,4),

NO3DEP NUMBER(16,4),

CLDEP NUMBER(16,4),

H2ODEP NUMBER(16,4),

UPDATE_DATE DATE,

CONSTRAINT CLOUD_MONTH_PK

PRIMARY KEY

(MONTH, YEAR, SITE_ID)

LOGGING

MONITORING;

COMMENT ON TABLE CLOUD_MONTH IS 'Monthly aggregated output from the CLOUD cloud water deposition model.';

COMMENT ON COLUMN CLOUD_MONTH.SITE_ID IS 'Site identification code.';

COMMENT ON COLUMN CLOUD_MONTH.YEAR IS 'Year sampling occurred.';

COMMENT ON COLUMN CLOUD_MONTH.MONTH IS 'Month sampling occurred.';

COMMENT ON COLUMN CLOUD_MONTH.SAMP_FREQ IS 'Sample frequency.';

COMMENT ON COLUMN CLOUD_MONTH.DURATION IS 'Sample duration; hours.';

COMMENT ON COLUMN CLOUD_MONTH.VOLUME IS 'Sample volume; mL.';

COMMENT ON COLUMN CLOUD_MONTH.WS IS 'Wind speed; m/s.';

COMMENT ON COLUMN CLOUD_MONTH.LWC IS 'Liquid water content; g/m^3.';

COMMENT ON COLUMN CLOUD_MONTH.TURBFLUX IS 'Turbulence flux; g/cm^2/min.';

COMMENT ON COLUMN CLOUD_MONTH.SEDFLUX IS 'Sedimentary flux; g/cm^2/min.';

COMMENT ON COLUMN CLOUD_MONTH.TOTFLUX IS 'Total flux; g/cm^2/min.';

COMMENT ON COLUMN CLOUD_MONTH.TURBVD IS 'Turbulence deposition velocity; cm/s.';

COMMENT ON COLUMN CLOUD_MONTH.SEDVD IS 'Sedimentary deposition velocity; cm/s.';

COMMENT ON COLUMN CLOUD_MONTH.TOTVD IS 'Total deposition velocity; cm/s.';

COMMENT ON COLUMN CLOUD_MONTH.H IS 'Hydrogen ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.CA IS 'Calcium ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.MG IS 'Magnesium ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.K IS 'Potassium ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.NA IS 'Sodium ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.NH4 IS 'Ammonium ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.SO4 IS 'Sulfate ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.NO3 IS 'Nitrate ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.CL IS 'Chloride ion concentration; ueq/L.';

COMMENT ON COLUMN CLOUD_MONTH.HFLUX IS 'Average hydrogen ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.CAFLUX IS 'Average calcium ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.MGFLUX IS 'Average magnesium ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.KFLUX IS 'Average potassium ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.NAFLUX IS 'Average sodium ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.NH4FLUX IS 'Average ammonium ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.SO4FLUX IS 'Average sulfate ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.NO3FLUX IS 'Average nitrate ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.CLFLUX IS 'Average chloride ion deposition flux per hour; ueq/m^2/hr.';

COMMENT ON COLUMN CLOUD_MONTH.CLDFRAC IS 'Fraction of the month with clouds.';

COMMENT ON COLUMN CLOUD_MONTH.TOTHOURS IS 'Total number of hours in the month; hrs.';

COMMENT ON COLUMN CLOUD_MONTH.CLOUDHRS IS 'Total number of hours in the month with clouds; hr.';

COMMENT ON COLUMN CLOUD_MONTH.HDEP IS 'Hydrogen ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.KDEP IS 'Potassium ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.NADEP IS 'Sodium ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.CADEP IS 'Calcium ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.MGDEP IS 'Magnesium ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.NH4DEP IS 'Ammonium ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.SO4DEP IS 'Sulfate ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.NO3DEP IS 'Nirate ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.CLDEP IS 'Chloride ion deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.H2ODEP IS 'Water deposition per month; ueq/m^2/month.';

COMMENT ON COLUMN CLOUD_MONTH.UPDATE_DATE IS 'Date and time of last record update.';

-- CODES_OTHER_CD (Table) -- Row count:315

CREATE TABLE CODES_OTHER_CD

TABLE_NAME VARCHAR2(25 BYTE) NOT NULL,

COLUMN_NAME VARCHAR2(25 BYTE) NOT NULL,

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_CODES_OTHER_CD

PRIMARY KEY

(TABLE_NAME, COLUMN_NAME, CODE)

LOGGING

MONITORING;

COMMENT ON TABLE CODES_OTHER_CD IS 'Data quality codes used in other tables which are not used in foreign key enforcement';

COMMENT ON COLUMN CODES_OTHER_CD.TABLE_NAME IS 'Table name.';

COMMENT ON COLUMN CODES_OTHER_CD.COLUMN_NAME IS 'Column name.';

COMMENT ON COLUMN CODES_OTHER_CD.CODE IS 'Code';

COMMENT ON COLUMN CODES_OTHER_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN CODES_OTHER_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN CODES_OTHER_CD.UPDATE_DATE IS 'Date and time of last record update';

-- CONVERSION_FACTORS (Table) -- Row count:12

CREATE TABLE CONVERSION_FACTORS

PARAMETER VARCHAR2(50 BYTE),

ELEMENT VARCHAR2(10 BYTE),

FACTOR NUMBER,

UNIT_FROM VARCHAR2(20 BYTE),

UNIT_TO VARCHAR2(20 BYTE),

PRIMARY KEY

(PARAMETER)

LOGGING

MONITORING;

COMMENT ON TABLE CONVERSION_FACTORS IS 'Conversion factors used for converting ions or compounds to their elemental equivalent.';

COMMENT ON COLUMN CONVERSION_FACTORS.PARAMETER IS 'Compound or ion measured. See REPORT_PARAMETERS for description of parameters.';

COMMENT ON COLUMN CONVERSION_FACTORS.ELEMENT IS 'Element of concern in compound or ion. ';

COMMENT ON COLUMN CONVERSION_FACTORS.FACTOR IS 'Multiplication factor for calculating flux or concentrations from units in terms of measurement parameter to units in terms of element. For example, to calculate kg-S/ha from kg-SO4/ha, multiply kg-SO4/ha by the conversion factor.';

COMMENT ON COLUMN CONVERSION_FACTORS.UNIT_FROM IS 'Unit of measure to convert from.';

COMMENT ON COLUMN CONVERSION_FACTORS.UNIT_TO IS 'Unit of measure to convert to.';

-- COUNTY (Table) -- Row count:3220

CREATE TABLE COUNTY

FIPS NUMBER(5),

STATE VARCHAR2(2 BYTE),

COUNTY_NAME VARCHAR2(90 BYTE),

STATE_CODE VARCHAR2(2 BYTE),

COUNTY_CODE VARCHAR2(3 BYTE),

NBP VARCHAR2(1 BYTE),

AREA NUMBER(12,4),

TYPE VARCHAR2(20 BYTE),

PRIMARY KEY

(FIPS)

LOGGING

MONITORING;

COMMENT ON TABLE COUNTY IS 'Area in km^2';

COMMENT ON COLUMN COUNTY.AREA IS 'Area in km^2';

-- DATETIME_KEY (Table) -- Row count:289272

CREATE TABLE DATETIME_KEY

DATE_TIME DATE,

YEAR NUMBER(4),

QUARTER NUMBER(2),

MONTH NUMBER(2),

WEEK NUMBER(2),

HOUR NUMBER(2),

SYEAR NUMBER(4),

SEASON VARCHAR2(2 BYTE),

JDATE NUMBER(3),

DOW NUMBER(2),

PRIMARY KEY

(DATE_TIME)

NOLOGGING

MONITORING;

COMMENT ON TABLE DATETIME_KEY IS 'Lookup table for DATE_TIMEs to find hour, weeks, and other aggregations.

INSERT INTO datetime_key SELECT date_time , TO_NUMBER (period (date_time, ''Y''), ''9999'') year , TO_NUMBER (period (date_time, ''Q''), ''99'') quarter , TO_NUMBER (period (date_time, ''M''), ''99'') month , TO_NUMBER (period (date_time, ''W''), ''99'') week , TO_NUMBER (TO_CHAR(date_time, ''Hh24''), ''99'') hour , TO_NUMBER (period (date_time, ''YS''), ''9999'') syear , period (date_time, ''S'') season , TO_NUMBER (TO_CHAR(date_time, ''ddd'')) jdate , TO_NUMBER(TO_CHAR(date_time-9/24-2,''D'')) dow FROM (SELECT TO_DATE (''01011987 00'', ''MMDDYYYY hh24'') + NUMTODSINTERVAL (LEVEL - 1, ''hour'') AS date_time

FROM DUAL

CONNECT BY LEVEL <=

(TO_DATE (''01012020'', ''MMDDYYYY'')

- TO_DATE (''01011987 00'', ''MMDDYYYY hh24'') )

* 24)

COMMENT ON COLUMN DATETIME_KEY.DATE_TIME IS 'Date and time of observation.';

COMMENT ON COLUMN DATETIME_KEY.YEAR IS 'Year based on Tuesday-Tuesday sampling periods; yyyy.';

COMMENT ON COLUMN DATETIME_KEY.QUARTER IS 'Quarter based on Tuesday-Tuesday sampling periods; 1=January-March, 2=April-June, 3=July-September, 4=October-December.';

COMMENT ON COLUMN DATETIME_KEY.MONTH IS 'Month based on Tuesday-Tuesday sampling periods; 1-12.';

COMMENT ON COLUMN DATETIME_KEY.WEEK IS 'Week based on Tuesday-Tuesday sampling periods; 1-53.';

COMMENT ON COLUMN DATETIME_KEY.HOUR IS 'Hour of day; 0-23.';

COMMENT ON COLUMN DATETIME_KEY.SYEAR IS 'Seasonal year based on Tuesday-Tuesday sampling periods; yyyy.';

COMMENT ON COLUMN DATETIME_KEY.SEASON IS 'Season based on Tuesday-Tuesday sampling periods; WI=December-February, SP=March-May, SU=June-August, FA=September-November.';

COMMENT ON COLUMN DATETIME_KEY.JDATE IS 'Julian day; ddd.';

COMMENT ON COLUMN DATETIME_KEY.DOW IS 'Day of Tuesday 0900 - Tuesday 0800 week; 1=Tuesday, 2=Wednesday, 3=Thursday, 4=Friday, 5=Saturday, 6=Sunday, 7=Monday.';

-- DRYCH_F_CD (Table) -- Row count:10

CREATE TABLE DRYCH_F_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_DRYCH_F_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE DRYCH_F_CD IS 'Foreign key table for all data quality codes for columns ending in %_F in DRYCHEM';

COMMENT ON COLUMN DRYCH_F_CD.CODE IS 'Code';

COMMENT ON COLUMN DRYCH_F_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN DRYCH_F_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN DRYCH_F_CD.UPDATE_DATE IS 'Date and time of last record update';

-- DRYCH_TEMPSRCE_CD (Table) -- Row count:5

CREATE TABLE DRYCH_TEMPSRCE_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_DRYCH_TEMPSRCE_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE DRYCH_TEMPSRCE_CD IS 'Foreign key table for TEMP_SOURCE in DRYCHEM';

COMMENT ON COLUMN DRYCH_TEMPSRCE_CD.CODE IS 'Code';

COMMENT ON COLUMN DRYCH_TEMPSRCE_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN DRYCH_TEMPSRCE_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN DRYCH_TEMPSRCE_CD.UPDATE_DATE IS 'Date and time of last record update';

-- DRYCH_TYPE_CD (Table) -- Row count:6

CREATE TABLE DRYCH_TYPE_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_DRYCH_TYPE_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE DRYCH_TYPE_CD IS 'Foreign key table for TYPE in DRYCHEM';

COMMENT ON COLUMN DRYCH_TYPE_CD.CODE IS 'Code';

COMMENT ON COLUMN DRYCH_TYPE_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN DRYCH_TYPE_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN DRYCH_TYPE_CD.UPDATE_DATE IS 'Date and time of last record update';

-- EQUIP_BEGINST_CD (Table) -- Row count:2

CREATE TABLE EQUIP_BEGINST_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_EQUIP_BEGINST_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE EQUIP_BEGINST_CD IS 'Foreign key table for BEGINNING_STATUS in EQUIPMENT_INVENTORY';

COMMENT ON COLUMN EQUIP_BEGINST_CD.CODE IS 'Code';

COMMENT ON COLUMN EQUIP_BEGINST_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN EQUIP_BEGINST_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN EQUIP_BEGINST_CD.UPDATE_DATE IS 'Date and time of last record update';

-- EQUIP_COND_CD (Table) -- Row count:11

CREATE TABLE EQUIP_COND_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_EQUIP_COND_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE EQUIP_COND_CD IS 'Foreign key table for CONDITION in EQUIPMENT_INVENTORY';

COMMENT ON COLUMN EQUIP_COND_CD.CODE IS 'Code';

COMMENT ON COLUMN EQUIP_COND_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN EQUIP_COND_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN EQUIP_COND_CD.UPDATE_DATE IS 'Date and time of last record update';

-- EQUIP_CURRST_CD (Table) -- Row count:4

CREATE TABLE EQUIP_CURRST_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_EQUIP_CURRST_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE EQUIP_CURRST_CD IS 'Foreign key table for CURRENT_STATUS in EQUIPMENT_INVENTORY';

COMMENT ON COLUMN EQUIP_CURRST_CD.CODE IS 'Code';

COMMENT ON COLUMN EQUIP_CURRST_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN EQUIP_CURRST_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN EQUIP_CURRST_CD.UPDATE_DATE IS 'Date and time of last record update';

-- FILTER_PACK (Table) -- Row count:95642

CREATE TABLE FILTER_PACK

LAB_KEY VARCHAR2(15 BYTE) NOT NULL,

SITE_ID VARCHAR2(6 BYTE),

DATEON DATE NOT NULL,

DATEOFF DATE NOT NULL,

FLOWCHECK_DATEON NUMBER(12,8),

FLOWCHECK_DATEOFF NUMBER(12,8),

LEAKCHECK_DATEON NUMBER(12,8),

LEAKCHECK_DATEOFF NUMBER(12,8),

DAS_FLOW_DATEON NUMBER(12,8),

DAS_FLOW_DATEOFF NUMBER(12,8),

ROTAMETER_DATEON NUMBER(12,8),

ROTAMETER_DATEOFF NUMBER(12,8),

ELAPSED_TIME NUMBER(12,4),

SHIPPED_BY VARCHAR2(25 BYTE),

SHIPPING_DATE DATE,

LAB_TECH_RECEIPT VARCHAR2(25 BYTE),

RECEIPT_DATE DATE,

OBSERVATION VARCHAR2(5 BYTE),

LEAVES_DROPPED VARCHAR2(6 BYTE),

LEAVES_FALL_COLOR VARCHAR2(6 BYTE),

LEAVES_GREEN VARCHAR2(6 BYTE),

COMMENT_LAB VARCHAR2(500 BYTE),

COMMENT_FIELD VARCHAR2(500 BYTE),

UPDATE_DATE DATE DEFAULT sysdate NOT NULL, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_FILTER_PACK

PRIMARY KEY

(LAB_KEY)

LOGGING

MONITORING;

COMMENT ON TABLE FILTER_PACK IS 'Dry chemistry filter pack data. Data input from SSRF.';

COMMENT ON COLUMN FILTER_PACK.LAB_KEY IS 'Laboratory sample identification number.';

COMMENT ON COLUMN FILTER_PACK.SITE_ID IS 'Site identification code.';

COMMENT ON COLUMN FILTER_PACK.DATEON IS 'Date and time sample collection began, Local Standard Time; mm/dd/yyyy hh:mm:ss.';

COMMENT ON COLUMN FILTER_PACK.DATEOFF IS 'Date and time sample collection ended, Local Standard Time; mm/dd/yyyy hh:mm:ss.';

COMMENT ON COLUMN FILTER_PACK.FLOWCHECK_DATEON IS 'Flow controller display with pump off at dateon.';

COMMENT ON COLUMN FILTER_PACK.FLOWCHECK_DATEOFF IS 'Flow controller display with pump off at dateoff.';

COMMENT ON COLUMN FILTER_PACK.LEAKCHECK_DATEON IS 'Flow controller display with pump on and filter removed at dateon.';

COMMENT ON COLUMN FILTER_PACK.LEAKCHECK_DATEOFF IS 'Flow controller display with pump on and filter removed at dateoff.';

COMMENT ON COLUMN FILTER_PACK.DAS_FLOW_DATEON IS 'Output in units from datalogger with pump on at dateon; units.';

COMMENT ON COLUMN FILTER_PACK.DAS_FLOW_DATEOFF IS 'Output in units from datalogger with pump on at dateoff; units.';

COMMENT ON COLUMN FILTER_PACK.ROTAMETER_DATEON IS 'Reading on the coarse flow indicator at dateon.';

COMMENT ON COLUMN FILTER_PACK.ROTAMETER_DATEOFF IS 'Reading on the coarse flow indicator at dateoff.';

COMMENT ON COLUMN FILTER_PACK.ELAPSED_TIME IS 'Total on hourmeter when filter is removed.';

COMMENT ON COLUMN FILTER_PACK.SHIPPED_BY IS 'Site operator that prepared filter for shipment.';

COMMENT ON COLUMN FILTER_PACK.SHIPPING_DATE IS 'Date the filter is expected to ship; mm/dd/yyyy.';

COMMENT ON COLUMN FILTER_PACK.LAB_TECH_RECEIPT IS 'Lab tech that received the filter.';

COMMENT ON COLUMN FILTER_PACK.RECEIPT_DATE IS 'Date the filter is received by the lab; mm/dd/yyyy.';

COMMENT ON COLUMN FILTER_PACK.OBSERVATION IS 'Climate observation.';

COMMENT ON COLUMN FILTER_PACK.LEAVES_DROPPED IS 'Percentage of leafed that have dropped from the canopy.';

COMMENT ON COLUMN FILTER_PACK.LEAVES_FALL_COLOR IS 'Percentage of the leaves that are fall color.';

COMMENT ON COLUMN FILTER_PACK.LEAVES_GREEN IS 'Percentage of the leaves that are green.';

COMMENT ON COLUMN FILTER_PACK.COMMENT_LAB IS 'Lab comments.';

COMMENT ON COLUMN FILTER_PACK.COMMENT_FIELD IS 'Field comments.';

COMMENT ON COLUMN FILTER_PACK.UPDATE_DATE IS 'Date and time of last record update.';

-- GAS_CALIBRATION (Table) -- Row count:309191

CREATE TABLE GAS_CALIBRATION

SITE_ID VARCHAR2(6 BYTE) NOT NULL,

START_TIME DATE NOT NULL,

END_TIME DATE NOT NULL,

PARAMETER VARCHAR2(5 BYTE) NOT NULL,

UNITS VARCHAR2(5 BYTE),

CALIBRATION_VALUE NUMBER(12,4),

EXPECTED_VALUE NUMBER(12,4),

CALIBRATION_TYPE VARCHAR2(2 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT GAS_CALIBRATION_PK

PRIMARY KEY

(SITE_ID, START_TIME, PARAMETER)

LOGGING

MONITORING;

COMMENT ON TABLE GAS_CALIBRATION IS 'Gas calibration data as polled from sites; this includes ozone, SO2, NOy, and CO.';

COMMENT ON COLUMN GAS_CALIBRATION.SITE_ID IS 'Site identification code.';

COMMENT ON COLUMN GAS_CALIBRATION.START_TIME IS 'Start time for calibration, Local Standard Time.';

COMMENT ON COLUMN GAS_CALIBRATION.END_TIME IS 'End time for calibration, Local Standard Time.';

COMMENT ON COLUMN GAS_CALIBRATION.PARAMETER IS 'Name of parameter being calibrated.';

COMMENT ON COLUMN GAS_CALIBRATION.UNITS IS 'Units of parameter being calibrated.';

COMMENT ON COLUMN GAS_CALIBRATION.CALIBRATION_VALUE IS 'Value measured by site sensor.';

COMMENT ON COLUMN GAS_CALIBRATION.EXPECTED_VALUE IS 'Expected value.';

COMMENT ON COLUMN GAS_CALIBRATION.CALIBRATION_TYPE IS 'Type of calibration performed.';

COMMENT ON COLUMN GAS_CALIBRATION.UPDATE_DATE IS 'Date and time record was last modified.';

-- HOURLY_GAS_CD (Table) -- Row count:12

CREATE TABLE HOURLY_GAS_CD

CODE VARCHAR2(10 BYTE),

DESCRIPTION VARCHAR2(100 BYTE),

VALID VARCHAR2(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_HOURLY_GAS_CD

PRIMARY KEY

(CODE)

NOLOGGING

MONITORING;

COMMENT ON TABLE HOURLY_GAS_CD IS 'Foreign key table for VALUE_F in HOURLY_GAS';

COMMENT ON COLUMN HOURLY_GAS_CD.CODE IS 'Code';

COMMENT ON COLUMN HOURLY_GAS_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN HOURLY_GAS_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN HOURLY_GAS_CD.UPDATE_DATE IS 'Date and time of last record update';

-- HOURLY_GAS_PARAMETER (Table) -- Row count:19

CREATE TABLE HOURLY_GAS_PARAMETER

CODE VARCHAR2(10 BYTE),

DESCRIPTION VARCHAR2(100 BYTE),

VALID VARCHAR2(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_HOURLY_GAS_PARAMETER

PRIMARY KEY

(CODE)

NOLOGGING

MONITORING;

COMMENT ON TABLE HOURLY_GAS_PARAMETER IS 'Foreign key table for PARAMETER in HOURLY_GAS';

COMMENT ON COLUMN HOURLY_GAS_PARAMETER.CODE IS 'Code';

COMMENT ON COLUMN HOURLY_GAS_PARAMETER.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN HOURLY_GAS_PARAMETER.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN HOURLY_GAS_PARAMETER.UPDATE_DATE IS 'Date and time of last record update';

-- LABDATA_ANALYTE (Table) -- Row count:85

CREATE TABLE LABDATA_ANALYTE

STORET_KEY VARCHAR2(15 BYTE) NOT NULL,

ANALYTE VARCHAR2(41 BYTE),

CAS VARCHAR2(10 BYTE),

MDL NUMBER(13),

MRL NUMBER(13),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_LABDATA_ANALYTE

PRIMARY KEY

(STORET_KEY)

LOGGING

MONITORING;

COMMENT ON TABLE LABDATA_ANALYTE IS 'Analyte detection and reporting limits used for laboratory analyses';

COMMENT ON COLUMN LABDATA_ANALYTE.STORET_KEY IS 'ESE assigned storet key.';

COMMENT ON COLUMN LABDATA_ANALYTE.ANALYTE IS 'Analyte name.';

COMMENT ON COLUMN LABDATA_ANALYTE.CAS IS 'Chemical Abstract Service Number';

COMMENT ON COLUMN LABDATA_ANALYTE.MDL IS 'Minimum Detection Limit.';

COMMENT ON COLUMN LABDATA_ANALYTE.MRL IS 'Minimum Reporting Limit.';

COMMENT ON COLUMN LABDATA_ANALYTE.UPDATE_DATE IS 'Date and time of last record update';

-- LABDA_DMCQUAL_CD (Table) -- Row count:2

CREATE TABLE LABDA_DMCQUAL_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_LABDA_DMCQUAL_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE LABDA_DMCQUAL_CD IS 'Foreign key table for DMC_QUALIFIER in LABDATA';

COMMENT ON COLUMN LABDA_DMCQUAL_CD.CODE IS 'Code';

COMMENT ON COLUMN LABDA_DMCQUAL_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN LABDA_DMCQUAL_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN LABDA_DMCQUAL_CD.UPDATE_DATE IS 'Date and time of last record update';

-- LABDA_METHOD_CD (Table) -- Row count:10

CREATE TABLE LABDA_METHOD_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_LABDA_METHOD_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE LABDA_METHOD_CD IS 'Foreign key table for METHOD in LABDATA';

COMMENT ON COLUMN LABDA_METHOD_CD.CODE IS 'Code';

COMMENT ON COLUMN LABDA_METHOD_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN LABDA_METHOD_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN LABDA_METHOD_CD.UPDATE_DATE IS 'Date and time of last record update';

-- LABDA_QUALIF_CD (Table) -- Row count:5

CREATE TABLE LABDA_QUALIF_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_LABDA_QUALIF_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE LABDA_QUALIF_CD IS 'Foreign key table for QUALIFIER in LABDATA';

COMMENT ON COLUMN LABDA_QUALIF_CD.CODE IS 'Code';

COMMENT ON COLUMN LABDA_QUALIF_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN LABDA_QUALIF_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN LABDA_QUALIF_CD.UPDATE_DATE IS 'Date and time of last record update';

-- LEAF_ANGLE (Table) -- Row count:9

CREATE TABLE LEAF_ANGLE

LEAF_CLASS NUMBER NOT NULL,

LEAF_FRACTION NUMBER(12,4),

LEAF_ANGLE_COSINE NUMBER(12,4),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_LEAF_ANGLE

PRIMARY KEY

(LEAF_CLASS)

LOGGING

MONITORING;

COMMENT ON TABLE LEAF_ANGLE IS 'Leaf angle data used as input to the MLM model.';

COMMENT ON COLUMN LEAF_ANGLE.LEAF_CLASS IS 'Used by Multi-Layer Model (MLM) - index used to order the leaf fraction and leaf angle cosine values.';

COMMENT ON COLUMN LEAF_ANGLE.LEAF_FRACTION IS 'Used by Multi-Layer Model (MLM) - fraction of leaves at each angle.';

COMMENT ON COLUMN LEAF_ANGLE.LEAF_ANGLE_COSINE IS 'Used by Multi-Layer Model (MLM) - cosine of leaf normal vector to the zenith.';

COMMENT ON COLUMN LEAF_ANGLE.UPDATE_DATE IS 'Date and time of last record update';

-- METDA_F_CD (Table) -- Row count:13

CREATE TABLE METDA_F_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, CONSTRAINT PK_METDA_F_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE METDA_F_CD IS 'Foreign key table for data quality codes used in all %_F columns in METDATA';

COMMENT ON COLUMN METDA_F_CD.CODE IS 'Code';

COMMENT ON COLUMN METDA_F_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN METDA_F_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN METDA_F_CD.UPDATE_DATE IS 'Date and time of last record update';

-- METDA_QACODE_CD (Table) -- Row count:7

CREATE TABLE METDA_QACODE_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, CONSTRAINT PK_METDA_QACODE_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE METDA_QACODE_CD IS 'Foreign key table for QA_CODE in METDATA';

COMMENT ON COLUMN METDA_QACODE_CD.CODE IS 'Code';

COMMENT ON COLUMN METDA_QACODE_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN METDA_QACODE_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN METDA_QACODE_CD.UPDATE_DATE IS 'Date and time of last record update';

-- MODEL_QA_CODE_CD (Table) -- Row count:6

CREATE TABLE MODEL_QA_CODE_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, CONSTRAINT PK_MODEL_QA_CODE_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE MODEL_QA_CODE_CD IS 'Foreign key table for QA_CODE in MODEL_OUTPUT';

COMMENT ON COLUMN MODEL_QA_CODE_CD.CODE IS 'Code';

COMMENT ON COLUMN MODEL_QA_CODE_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN MODEL_QA_CODE_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN MODEL_QA_CODE_CD.UPDATE_DATE IS 'Date and time of last record update';

-- MODEL_SOIL_MOISTURE_CONVERGE (Table) -- Row count:12104094

CREATE TABLE MODEL_SOIL_MOISTURE_CONVERGE

SITE_ID VARCHAR2(6 BYTE) NOT NULL,

DATE_TIME DATE NOT NULL,

SOIL_MOISTURE_120 NUMBER(16,4),

SOIL_MOISTURE_20 NUMBER(16,4),

UPDATE_DATE DATE DEFAULT sysdate, CONSTRAINT SOIL_MOISTURE_PK

PRIMARY KEY

(SITE_ID, DATE_TIME)

LOGGING

MONITORING;

COMMENT ON TABLE MODEL_SOIL_MOISTURE_CONVERGE IS 'Soil moisture output of two MLM runs to determine convergence of soil moisture with two different seed values, 20 and 120. Model runs were performed 9/18/2008. The model version used is now obsolete.';

COMMENT ON COLUMN MODEL_SOIL_MOISTURE_CONVERGE.SITE_ID IS 'Site ID code used in data files';

COMMENT ON COLUMN MODEL_SOIL_MOISTURE_CONVERGE.DATE_TIME IS 'Date and time sample collection began, Local Standard Time; mm/dd/yyyy hh:mm:ss.';

COMMENT ON COLUMN MODEL_SOIL_MOISTURE_CONVERGE.SOIL_MOISTURE_120 IS '??';

COMMENT ON COLUMN MODEL_SOIL_MOISTURE_CONVERGE.SOIL_MOISTURE_20 IS '??';

COMMENT ON COLUMN MODEL_SOIL_MOISTURE_CONVERGE.UPDATE_DATE IS 'Date of last update for record';

-- MODEL_VERSION (Table) -- Row count:17

CREATE TABLE MODEL_VERSION

MODEL_NAME VARCHAR2(10 BYTE) NOT NULL,

VERSION_NUMBER VARCHAR2(10 BYTE) NOT NULL,

CHANGE_NUMBER NUMBER(4) NOT NULL,

DATE_RELEASED DATE,

DESCRIPTION_OF_CHANGE VARCHAR2(4000 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_MODEL_VERSION

PRIMARY KEY

(VERSION_NUMBER, CHANGE_NUMBER)

LOGGING

MONITORING;

COMMENT ON TABLE MODEL_VERSION IS 'Multi Layer Model output version descriptions';

COMMENT ON COLUMN MODEL_VERSION.MODEL_NAME IS 'Name of the deposition model.';

COMMENT ON COLUMN MODEL_VERSION.VERSION_NUMBER IS 'Version number of the deposition model, as described in the MODEL_VERSION table.';

COMMENT ON COLUMN MODEL_VERSION.CHANGE_NUMBER IS 'Incremental number assigned to model modification.';

COMMENT ON COLUMN MODEL_VERSION.DATE_RELEASED IS 'Date version was released as final.';

COMMENT ON COLUMN MODEL_VERSION.DESCRIPTION_OF_CHANGE IS 'Description of the modification to the model.';

COMMENT ON COLUMN MODEL_VERSION.UPDATE_DATE IS 'Date and time of last record update.';

-- NETWORK_CHANGE (Table) -- Row count:400

CREATE TABLE NETWORK_CHANGE

SITE_ID VARCHAR2(6 BYTE) NOT NULL,

CHANGE_NUM INTEGER NOT NULL,

CHANGE_DATE DATE NOT NULL,

PARAMETER VARCHAR2(30 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(300 BYTE),

COMMENTS VARCHAR2(300 BYTE),

UPDATE_DATE DATE,

PRIMARY KEY

(SITE_ID, CHANGE_NUM, CHANGE_DATE, PARAMETER)

LOGGING

MONITORING;

-- OZCAL_CALITYPE_CD (Table) -- Row count:5

CREATE TABLE OZCAL_CALITYPE_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_OZCAL_CALITYPE_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE OZCAL_CALITYPE_CD IS 'Foreign key table for CALIBRATION_TYPE in OZONE_CALIBRATION';

COMMENT ON COLUMN OZCAL_CALITYPE_CD.CODE IS 'Code';

COMMENT ON COLUMN OZCAL_CALITYPE_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN OZCAL_CALITYPE_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN OZCAL_CALITYPE_CD.UPDATE_DATE IS 'Date and time of last record update';

-- OZONE_F_CD (Table) -- Row count:13

CREATE TABLE OZONE_F_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_OZONE_F_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE OZONE_F_CD IS 'Foreign key table for OZONE_F in OZONE';

COMMENT ON COLUMN OZONE_F_CD.CODE IS 'Code';

COMMENT ON COLUMN OZONE_F_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN OZONE_F_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN OZONE_F_CD.UPDATE_DATE IS 'Date and time of last record update';

-- PLANT (Table) -- Row count:84

CREATE TABLE PLANT

PLANT_ID NUMBER(11) NOT NULL,

PLANT_TYPE VARCHAR2(50 BYTE),

MIN_STOM NUMBER(8,2),

LIGHT_RES_COEFF NUMBER(8,2),

OPT_TEMP NUMBER(8,2),

MAX_NO_STRESS_TEMP NUMBER(8,2),

MIN_NO_STRESS_TEMP NUMBER(8,2),

PROFILE_INDEX NUMBER(8,2),

CANOPY_HEIGHT NUMBER(8,2),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_PLANT

PRIMARY KEY

(PLANT_ID)

LOGGING

MONITORING;

COMMENT ON TABLE PLANT IS 'Plant types and coefficients used as input to the MLM model.';

COMMENT ON COLUMN PLANT.PLANT_ID IS 'Plant identification code.';

COMMENT ON COLUMN PLANT.PLANT_TYPE IS 'Description of the plant type.';

COMMENT ON COLUMN PLANT.MIN_STOM IS 'Minimum stomatal resistance.';

COMMENT ON COLUMN PLANT.LIGHT_RES_COEFF IS 'Light response coefficient.';

COMMENT ON COLUMN PLANT.OPT_TEMP IS 'Optimum temperature; degrees C.';

COMMENT ON COLUMN PLANT.MAX_NO_STRESS_TEMP IS 'Maximum no stress temperature; degrees C.';

COMMENT ON COLUMN PLANT.MIN_NO_STRESS_TEMP IS 'Minimum no stress temperature; degrees C.';

COMMENT ON COLUMN PLANT.PROFILE_INDEX IS 'Plant profile index.';

COMMENT ON COLUMN PLANT.CANOPY_HEIGHT IS 'Plant canopy height; m.';

COMMENT ON COLUMN PLANT.UPDATE_DATE IS 'Date and time of last record update.';

-- PLANT_PROFILE (Table) -- Row count:63

CREATE TABLE PLANT_PROFILE

PROFILE_TYPE NUMBER(6) NOT NULL,

PROFILE_LEVEL NUMBER(4) NOT NULL,

PERCENT_LEAF NUMBER(12,4),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_PLANT_PROFILE

PRIMARY KEY

(PROFILE_TYPE, PROFILE_LEVEL)

LOGGING

MONITORING;

COMMENT ON TABLE PLANT_PROFILE IS 'Canopy data used as input to the MLM model.';

COMMENT ON COLUMN PLANT_PROFILE.PROFILE_TYPE IS 'Used by MLM - plant profile index used to group the plant profile level and percent leaf values.';

COMMENT ON COLUMN PLANT_PROFILE.PROFILE_LEVEL IS 'Used by MLM - counter for levels in the canopy.';

COMMENT ON COLUMN PLANT_PROFILE.PERCENT_LEAF IS 'Used by MLM - fraction of total leaves for each level in the canopy.';

COMMENT ON COLUMN PLANT_PROFILE.UPDATE_DATE IS 'Date and time of last record update.';

-- SAMPLE_REFERENCE (Table) -- Row count:184723

CREATE TABLE SAMPLE_REFERENCE

LAB_KEY VARCHAR2(15 BYTE) NOT NULL,

RECEIPT_DATE DATE,

COMMENT_CODES VARCHAR2(50 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SAMPLE_REFERENCE

PRIMARY KEY

(LAB_KEY)

LOGGING

MONITORING;

COMMENT ON TABLE SAMPLE_REFERENCE IS 'Laboratory sample comments and notes. References LABDATA and LABDATA_QC.';

COMMENT ON COLUMN SAMPLE_REFERENCE.LAB_KEY IS 'Laboratory sample identification number.';

COMMENT ON COLUMN SAMPLE_REFERENCE.RECEIPT_DATE IS 'Date the sample is received by the lab; mm/dd/yyyy.';

COMMENT ON COLUMN SAMPLE_REFERENCE.COMMENT_CODES IS 'Comment codes separated by spaces.';

COMMENT ON COLUMN SAMPLE_REFERENCE.UPDATE_DATE IS 'Date and time of last record update.';

-- SITEOPS_CONTIN_CD (Table) -- Row count:1

CREATE TABLE SITEOPS_CONTIN_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITEOPS_CONTIN_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE SITEOPS_CONTIN_CD IS 'Foreign key table for CONTINUOUS in SITE_OPS';

COMMENT ON COLUMN SITEOPS_CONTIN_CD.CODE IS 'Code';

COMMENT ON COLUMN SITEOPS_CONTIN_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN SITEOPS_CONTIN_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN SITEOPS_CONTIN_CD.UPDATE_DATE IS 'Date and time of last record update.';

-- SITEOPS_GROUPID_CD (Table) -- Row count:10

CREATE TABLE SITEOPS_GROUPID_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITEOPS_GROUPID_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE SITEOPS_GROUPID_CD IS 'Foreign key table for GROUP_ID in SITE_OPS';

COMMENT ON COLUMN SITEOPS_GROUPID_CD.CODE IS 'Code';

COMMENT ON COLUMN SITEOPS_GROUPID_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN SITEOPS_GROUPID_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN SITEOPS_GROUPID_CD.UPDATE_DATE IS 'Date and time of last record update.';

-- SITEOPS_MLMTZ_CD (Table) -- Row count:7

CREATE TABLE SITEOPS_MLMTZ_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITEOPS_MLMTZ_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE SITEOPS_MLMTZ_CD IS 'Foreign key table for MLM_TIME_ZONE in SITE_OPS';

COMMENT ON COLUMN SITEOPS_MLMTZ_CD.CODE IS 'Code';

COMMENT ON COLUMN SITEOPS_MLMTZ_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN SITEOPS_MLMTZ_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN SITEOPS_MLMTZ_CD.UPDATE_DATE IS 'Date and time of last record update.';

-- SITES_RENAMED (Table) -- Row count:12

CREATE TABLE SITES_RENAMED

SITE_ID VARCHAR2(6 BYTE) NOT NULL,

NEW_SITE_ID VARCHAR2(6 BYTE) NOT NULL,

DATE_RENAMED DATE NOT NULL,

COMMENTS VARCHAR2(80 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITES_RENAMED

PRIMARY KEY

(SITE_ID)

LOGGING

MONITORING;

COMMENT ON TABLE SITES_RENAMED IS 'Documentation of sites that have been renamed during the history of the network but have remained at the same location.';

COMMENT ON COLUMN SITES_RENAMED.SITE_ID IS 'Site identification code.';

COMMENT ON COLUMN SITES_RENAMED.NEW_SITE_ID IS 'New Site ID code used for all database records.';

COMMENT ON COLUMN SITES_RENAMED.DATE_RENAMED IS 'Date Site ID was changed; all hard copy and lab records prior to date remain labeled by original ID.';

COMMENT ON COLUMN SITES_RENAMED.COMMENTS IS 'Documentation of reason for Site ID change.';

COMMENT ON COLUMN SITES_RENAMED.UPDATE_DATE IS 'Date and time of last record update.';

-- SITE_AGENCY_CD (Table) -- Row count:2

CREATE TABLE SITE_AGENCY_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITE_AGENCY_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE SITE_AGENCY_CD IS 'Foreign key table for AGENCY in SITE';

COMMENT ON COLUMN SITE_AGENCY_CD.CODE IS 'Code';

COMMENT ON COLUMN SITE_AGENCY_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN SITE_AGENCY_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN SITE_AGENCY_CD.UPDATE_DATE IS 'Date and time of last record update.';

-- SITE_MLM_CD (Table) -- Row count:3

CREATE TABLE SITE_MLM_CD

CODE VARCHAR2(10 BYTE) NOT NULL,

DESCRIPTION VARCHAR2(255 BYTE),

VALID CHAR(1 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITE_MLM_CD

PRIMARY KEY

(CODE)

LOGGING

MONITORING;

COMMENT ON TABLE SITE_MLM_CD IS 'Foreign key table for MLM in SITE';

COMMENT ON COLUMN SITE_MLM_CD.CODE IS 'Code';

COMMENT ON COLUMN SITE_MLM_CD.DESCRIPTION IS 'Description of code.';

COMMENT ON COLUMN SITE_MLM_CD.VALID IS 'Does this code indicate a valid (V) or invalid (I) data record?';

COMMENT ON COLUMN SITE_MLM_CD.UPDATE_DATE IS 'Date and time of last record update.';

-- SITE_OPERATOR (Table) -- Row count:107

CREATE TABLE SITE_OPERATOR

SITE_ID VARCHAR2(6 BYTE),

PRIMARY_CONTACT VARCHAR2(65 BYTE),

PC_AFFILIATION VARCHAR2(65 BYTE),

PC_ADDRESS VARCHAR2(65 BYTE),

PC_CITY VARCHAR2(30 BYTE),

PC_STATE VARCHAR2(4 BYTE),

PC_ZIP VARCHAR2(10 BYTE),

PC_PHONE_HOME VARCHAR2(18 BYTE),

PC_PHONE_WORK VARCHAR2(18 BYTE),

PC_FAX VARCHAR2(18 BYTE),

SITE_OPERATOR VARCHAR2(65 BYTE),

SO_AFFILIATION VARCHAR2(65 BYTE),

SO_ADDRESS VARCHAR2(65 BYTE),

SO_CITY VARCHAR2(30 BYTE),

SO_STATE VARCHAR2(4 BYTE),

SO_ZIP VARCHAR2(10 BYTE),

SO_PHONE_HOME VARCHAR2(18 BYTE),

SO_PHONE_WORK VARCHAR2(18 BYTE),

SO_FAX VARCHAR2(18 BYTE),

SO_OTHER VARCHAR2(100 BYTE),

SO_STARTED INTEGER,

SO_CONTRACTED_THRU VARCHAR2(20 BYTE),

BACKUP_SO VARCHAR2(65 BYTE),

BSO_AFFILIATION VARCHAR2(65 BYTE),

BSO_ADDRESS VARCHAR2(65 BYTE),

BSO_CITY VARCHAR2(30 BYTE),

BSO_STATE VARCHAR2(4 BYTE),

BSO_ZIP VARCHAR2(10 BYTE),

BSO_PHONE_HOME VARCHAR2(18 BYTE),

BSO_PHONE_WORK VARCHAR2(18 BYTE),

BSO_FAX VARCHAR2(18 BYTE),

UPS_ACCOUNT_NUMBER VARCHAR2(30 BYTE),

SHIPPING_ADDRESS1 VARCHAR2(65 BYTE),

SHIPPING_ADDRESS2 VARCHAR2(65 BYTE),

SHIPPING_CITY VARCHAR2(30 BYTE),

SHIPPING_STATE VARCHAR2(4 BYTE),

SHIPPING_ZIP VARCHAR2(10 BYTE),

SHELTER_PHONE VARCHAR2(18 BYTE),

SHELTER_DIRECTIONS VARCHAR2(4000 BYTE),

SHELTER_HAZARDS VARCHAR2(65 BYTE),

EMERGENCY_CONTACT VARCHAR2(65 BYTE),

EMERGENCY_PHONE VARCHAR2(18 BYTE),

EMERGENCY_DIRECTIONS VARCHAR2(4000 BYTE),

ELECTRIC_UTILITY VARCHAR2(65 BYTE),

ELECTRIC_UTIL_PHONE VARCHAR2(18 BYTE),

TELEPHONE_COMPANY VARCHAR2(65 BYTE),

TELEPHONE_CO_PHONE VARCHAR2(18 BYTE),

DATE_UPDATED DATE,

COMMENTS VARCHAR2(4000 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITE_OPERATOR

PRIMARY KEY

(SITE_ID)

LOGGING

MONITORING;

COMMENT ON TABLE SITE_OPERATOR IS 'Site operator information for sites.';

COMMENT ON COLUMN SITE_OPERATOR.SITE_ID IS 'Site identification code.';

COMMENT ON COLUMN SITE_OPERATOR.PRIMARY_CONTACT IS 'Primary contact for site';

COMMENT ON COLUMN SITE_OPERATOR.PC_AFFILIATION IS 'Employer or organizational affilation for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.PC_ADDRESS IS 'Address for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.PC_CITY IS 'City for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.PC_STATE IS 'State for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.PC_ZIP IS 'Zip for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.PC_PHONE_HOME IS 'Home phone number for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.PC_PHONE_WORK IS 'Work phone number for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.PC_FAX IS 'Fax number for primary contact.';

COMMENT ON COLUMN SITE_OPERATOR.SITE_OPERATOR IS 'Site operator for site';

COMMENT ON COLUMN SITE_OPERATOR.SO_AFFILIATION IS 'Employer or organizational affilation for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_ADDRESS IS 'Address for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_CITY IS 'City for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_STATE IS 'State for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_ZIP IS 'Zip for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_PHONE_HOME IS 'Home phone number for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_PHONE_WORK IS 'Work phone number for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_FAX IS 'Fax number for site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_OTHER IS 'Other information pertaining to the site operator.';

COMMENT ON COLUMN SITE_OPERATOR.SO_STARTED IS 'Year site operator started working with CASTNet.';

COMMENT ON COLUMN SITE_OPERATOR.SO_CONTRACTED_THRU IS 'Year site operator is contracted through.';

COMMENT ON COLUMN SITE_OPERATOR.BACKUP_SO IS 'Backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_AFFILIATION IS 'Employer or organizational affilation for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_ADDRESS IS 'Address for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_CITY IS 'City for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_STATE IS 'State for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_ZIP IS 'Zip for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_PHONE_HOME IS 'Home phone number for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_PHONE_WORK IS 'Work phone number for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.BSO_FAX IS 'Fax number for backup site operator.';

COMMENT ON COLUMN SITE_OPERATOR.UPS_ACCOUNT_NUMBER IS 'UPS account number (if applicable).';

COMMENT ON COLUMN SITE_OPERATOR.SHIPPING_ADDRESS1 IS 'Line 1 of shipping address for site.';

COMMENT ON COLUMN SITE_OPERATOR.SHIPPING_ADDRESS2 IS 'Line 2 of shipping address for site.';

COMMENT ON COLUMN SITE_OPERATOR.SHIPPING_CITY IS 'City for shipping address.';

COMMENT ON COLUMN SITE_OPERATOR.SHIPPING_STATE IS 'State for shipping address.';

COMMENT ON COLUMN SITE_OPERATOR.SHIPPING_ZIP IS 'Zip for shipping address.';

COMMENT ON COLUMN SITE_OPERATOR.SHELTER_PHONE IS 'Phone number at site shelter.';

COMMENT ON COLUMN SITE_OPERATOR.SHELTER_DIRECTIONS IS 'Directions to site shelter.';

COMMENT ON COLUMN SITE_OPERATOR.SHELTER_HAZARDS IS 'Hazards at site location.';

COMMENT ON COLUMN SITE_OPERATOR.EMERGENCY_CONTACT IS 'Emergency contact at site.';

COMMENT ON COLUMN SITE_OPERATOR.EMERGENCY_PHONE IS 'Phone number for emergency contact.';

COMMENT ON COLUMN SITE_OPERATOR.EMERGENCY_DIRECTIONS IS 'Directions to nearest emergency site.';

COMMENT ON COLUMN SITE_OPERATOR.ELECTRIC_UTILITY IS 'Electric utility servicing site.';

COMMENT ON COLUMN SITE_OPERATOR.ELECTRIC_UTIL_PHONE IS 'Phone number for electric utility.';

COMMENT ON COLUMN SITE_OPERATOR.TELEPHONE_COMPANY IS 'Telephone company servicing site.';

COMMENT ON COLUMN SITE_OPERATOR.TELEPHONE_CO_PHONE IS 'Phone number for telephone company.';

COMMENT ON COLUMN SITE_OPERATOR.DATE_UPDATED IS 'Date of last manual entry update.';

COMMENT ON COLUMN SITE_OPERATOR.COMMENTS IS 'Comments concerning other site contacts or pertinent information.';

COMMENT ON COLUMN SITE_OPERATOR.UPDATE_DATE IS 'Date and time of last record update.';

-- SITE_OPS (Table) -- Dependencies:

-- SITEOPS_CONTIN_CD (Table) -- SITEOPS_GROUPID_CD (Table) -- SITEOPS_MLMTZ_CD (Table) -- Row count:132

CREATE TABLE SITE_OPS

SITE_ID VARCHAR2(6 BYTE),

CONTINUOUS VARCHAR2(1 BYTE),

MLM_TIME_ZONE VARCHAR2(15 BYTE),

NEARSITE1 VARCHAR2(6 BYTE),

NEARSITE2 VARCHAR2(6 BYTE),

NEARSITE3 VARCHAR2(6 BYTE),

LAB_SEQ VARCHAR2(3 BYTE),

GROUP_ID VARCHAR2(3 BYTE),

COMMENTS VARCHAR2(120 BYTE),

UPDATE_DATE DATE DEFAULT sysdate, SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS, SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS, SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS, CONSTRAINT PK_SITE_OPS

PRIMARY KEY

(SITE_ID),

CONSTRAINT FK_SITEOP_CONT

FOREIGN KEY (CONTINUOUS)

REFERENCES SITEOPS_CONTIN_CD (CODE),

CONSTRAINT…