Database Information

Constraints

SANITARY_DATA_SEQ is the primary key - this will get generated on the WI Beach Health side, but you probably want to have a field for an artificial primary key to identify the record with. Perhaps a sequence concatenated with the device name.

SAMPLER_SEQ must be in the list of users. This is not always the same person who is entering the data. We have a sequence number associated with the userid. You could send the userid here and our process will convert it into SAMPLER_SEQ.

ANALYZER_ZEQ must be in the list of users. This is not always the same person who is entering the data. We have a sequence number associated with the userid. You could send the userid here and our process will convert it into ANAYLYZER_SEQ.

DATA_ENTRY_SEQ must be in thelist of users. This is the person who is entering the data. We have a sequence number associated with the userid. You could send the userid here and our process will convert it into DATA_ENTRY_SEQ.

MONITOR_SITE_SEQ and SAMPLE_DATE_TIME make up a Unique Key

Cannot be null:

Select Database Schema

COLUMN NAMEDATA TYPEField in Monitoring Form
Part 1
BEACH_SEQNUMBER (8)*Beach (entered as beach name, saved as beach seq)
MONITOR_SITE_SEQNUMBER (8)*Monitoring Site (entered as name, saved as sequence number)
SAMPLE_DATE_TIMEDATE*Sample date time
SAMPLER_SEQNUMBER (10)*Sampler (entered as userid, saved as user seq)
AIR_TEMPNUMBER (8,4)
AIR_UNITSVARCHAR2 (1)
WIND_SPEEDNUMBER (8,4)
WIND_SPEED_UNITSVARCHAR2 (30)
WIND_DIR_DEGREESNUMBER
WIND_DIR_DESCVARCHAR2 (30)
WEATHER_DESCVARCHAR2 (30)*Weather Conditions
RAINFALL_LAST_EVENTVARCHAR2 (5)
RAINFALLNUMBER (8,4)
RAINFALL_UNITSVARCHAR2 (30)
RAINFALL_STN_DESCVARCHAR2 (60)
WAVE_HEIGHTNUMBER (8,4)*Wave Height (ft)
WAVE_HEIGHT_UNITSVARCHAR2 (30)*
WAVE_DIRECTIONVARCHAR2 (10)Wave Direction(E,N,NE,NW,S,SE,SW,W)
WAVE_CONDITIONSVARCHAR2 (30)
EST_ACT_FLAGVARCHAR2 (1)*Estimated/Actual (comes after Wave Height)
CURRENT_SPEEDNUMBER (8)Longshore Current Speed
LONGSHORE_CURRENT_UNITSVARCHAR2 (30)Units(cm/sec;ft/sec)
SHORELINE_CURRENT_DIRVARCHAR2 (30)Longshore Direction ((E,N,NE,NW,S,SE,SW,W))
PART_1_COMMENTSVARCHAR2 (1000)Field conditions comments
Part 2
ECOLI_SAMPLE_TYPEVARCHAR2 (30)Composite Sample(Y/N)
AVG_WATER_TEMPNUMBER (7,2)*Water Temperature
AVG_WATER_TEMP_UNITSVARCHAR2 (30)*Water Temperature Units
PHNUMBER (5,2)pH
COLOR_CHANGEVARCHAR2 (1)
COLOR_DESCRIPTIONVARCHAR2 (50)
ODOR_DESCRIPTIONVARCHAR2 (50)Odor ( None;Septic;Algae;Sulfur;Other)
ODOR_OTHER_DESCRIPTIONVARCHAR2 (50)If Other, describe
CLARITY_DESCVARCHAR2 (30)*Water Clarity
NTUNUMBER (10,2)Turbidity Measurements
SECCHI_TUBE_CMNUMBER (8)Secchi Tube (cm)
PART_2_COMMENTSVARCHAR2 (1000)Comments/Observations (Part2)
Part 3
NO_IN_WATERNUMBER (8)# In Water
NUM_OUT_OF_WATERNUMBER (8)# Out of Water
NO_BATHERSNUMBER (8)# at Beach
NO_PEOPLE_BOATINGNUMBER (8)# Boating
NO_PEOPLE_FISHINGNUMBER (8)# Fishing
NO_PEOPLE_SURFINGNUMBER (8)# Surfing
NO_PEOPLE_WINDSURFINGNUMBER (8)# Windsurfing
NO_PEOPLE_DIVINGNUMBER (8)# Diving
NO_PEOPLE_CLAMMINGNUMBER (8)# Clamming
NO_PEOPLE_OTHERNUMBER (8)# Other Activity
NO_PEOPLE_OTHER_DESCVARCHAR2 (50)Describe Other Activity
PART_3_COMMENTSVARCHAR2 (1000)Comments/Observations (Part3)
Part 4
FLOATABLES_FLAGVARCHAR2 (1)Floatables present
Floatables Radio Boxes
FLOAT_STREET_LITTERVARCHAR2 (1)Types of Floatables - Street Litter checkbox
FLOAT_FOODVARCHAR2 (1)Types of Floatables - Food checkbox
FLOAT_MEDICALVARCHAR2 (1)Types of Floatables - Medical checkbox
FLOAT_SEWAGEVARCHAR2 (1)Types of Floatables - Sewage checkbox
FLOAT_BLDG_MATERIALSVARCHAR2 (1)Types of Floatables - Bldg Materials checkbox
FLOAT_FISHINGVARCHAR2 (1)Types of Floatables - Fishing checkbox
FLOAT_OTHERVARCHAR2 (1)Types of Floatables - Other checkbox
FLOAT_OTHER_DESCVARCHAR2 (50)Describe Other Floatables
DEBRIS_AMOUNTVARCHAR2 (30)Amount of Beach Debris/Litter on Beach (None, Low 1-20%, Moderate 21-50%, Hight >50%)
Debris Radio Boxes
DEBRIS_STREET_LITTERVARCHAR2 (1)Types of Debris/Litter - Street Litter checkbox
DEBRIS_FOODVARCHAR2 (1)Types of Debris/Litter - Food checkbox
DEBRIS_MEDICALVARCHAR2 (1)Types of Debris/Litter - Medical Waste checkbox
DEBRIS_SEWAGEVARCHAR2 (1)Types of Debris/Litter - Sewage checkbox
DEBRIS_BLDG_MATERIALSVARCHAR2 (1)Types of Debris/Litter - Bldg Materials checkbox
DEBRIS_FISHINGVARCHAR2 (1)Types of Debris/Litter - Fishing checkbox
DEBRIS_HOUSEHOLDVARCHAR2 (1)Types of Debris/Litter - Household checkbox
DEBRIS_TARVARCHAR2 (1)Types of Debris/Litter - Tar checkbox
DEBRIS_OILVARCHAR2 (1)Types of Debris/Litter - Oil checkbox
DEBRIS_OTHERVARCHAR2 (1)Types of Debris/Litter - Other checkbox
DEBRIS_OTHER_DESCVARCHAR2 (255)Describe Other Debris
ALGAE_NEARSHOREVARCHAR2 (30)*Amount of Algae in Nearshore Water (NONE, LOW, MODERATE, HIGH)
ALGAE_ON_BEACHVARCHAR2 (30)*Amount of Algae On Beach (NONE, LOW, MODERATE, HIGH)
Algae Type
ALGAE_TYPE_PERIPHYTONVARCHAR2 (1)Algae Type - Periphyton
ALGAE_TYPE_GLOBULARVARCHAR2 (1)Algae Type - Globular
ALGAE_TYPE_FREEFLOATINGVARCHAR2 (1)Algae Type - Free Floating
ALGAE_TYPE_OTHERVARCHAR2 (1)Algae Type - Other
ALGAE_TYPE_OTHER_DESCVARCHAR2 (50)Describe Other Algae
Algae Color
ALGAE_COLOR_LT_GREENVARCHAR2 (1)Algae Color - Light Green
ALGAE_COLOR_BRIGHT_GREENVARCHAR2 (1)Algae Color - Bright Green
ALGAE_COLOR_DRK_GREENVARCHAR2 (1)Algae Color - Dark Green
ALGAE_COLOR_YELLOWVARCHAR2 (1)Algae Color - Yellow
ALGAE_COLOR_BROWNVARCHAR2 (1)Algae Color - Brown
ALGAE_COLOR_OTHERVARCHAR2 (1)Algae Color - Other
ALGAE_COLOR_OTHER_DESCVARCHAR2 (50)Describe Other Algae Color
Presence of Wildlife and Domestic Animals
NO_GEESENUMBER (8)(I think we want to add a field for Number of Geese?)
NO_GULLSNUMBER (8)*Number of Gulls
NO_DOGSNUMBER (8)Number of Dogs
NO_ANIMALS_OTHERNUMBER (8)# Other Animals
ANIMALS_OTHER_DESCVARCHAR2 (50)Other Animals Description
Presence of Dead Birds and Fish
NUM_LOONSNUMBER (8)Number of Dead Loons
NUM_HERR_GULLSNUMBER (8)Number of Dead Herr Gulls
NUM_RING_GULLSNUMBER (8)Number of Dead Ring Gulls
NUM_CORMORANTSNUMBER (8)Number of Dead Cormorants
NUM_LONGTAIL_DUCKSNUMBER (8)Number of Dead Loongtail Ducks
NUM_SCOTERNUMBER (8)Number of Dead Scoter
NUM_HORN_GREBENUMBER (8)Number of Dead Horn Grebe
NUM_REDNECKED_GREBENUMBER (8)Number of Dead Rednecked Grebe
NUM_OTHERNUMBER (8)Number of Dead Other Birds
NUM_OTHER_DESCVARCHAR2 (50)Describe Other Dead Birds
PART_4_COMMENTSVARCHAR2 (1000)Comments/Observations (Part 4)
DATE_ENTEREDDATE(Current date/time)
DATA_ENTRY_SEQNUMBER (10)Logged in by (default is the person who is logged on)
DATE_UPDATEDDATE(date/time when an update was made)
UPDATE_ENTRY_SEQNUMBER (10)(defaults to the person who was logged in when an update was made)
MISSING_REQUIRED_FLAGVARCHAR2 (1)"Not all Required(*) data collected" -In the desktop version, this is a flag that is used to let users submit data even if not all of the required fields are filled in. We will need to discuss what the required fields are and if we want this option in the mobile version)

Full Database Schema

COLUMN NAMECOLUMN IDPRIMARY KEYDATA TYPEField in Monitoring Data Form
SANITARY_DATA_SEQ11NUMBER (8)
BEACH_SEQ2NUMBER (8)Beach
MONITOR_SITE_SEQ3NUMBER (8)Monitoring Site
SAMPLE_DATE_TIME4DATESample date time
SAMPLER_SEQ5NUMBER (10)"Sampler (entered as userid saved as user seq)
AIR_TEMP6"NUMBER (84)"
AIR_UNITS7VARCHAR2 (1 Byte)
WIND_SPEED8"NUMBER (84)"
WIND_SPEED_UNITS9VARCHAR2 (30 Byte)
WIND_SPEED_DESC10VARCHAR2 (30 Byte)
WIND_DIR_DEGREES11NUMBER
WIND_DIR_DESC12VARCHAR2 (30 Byte)
WEATHER_DESC13VARCHAR2 (30 Byte)Weather Conditions
SHORELINE_CURRENT_DIR14VARCHAR2 (30 Byte)
WAVE_HEIGHT15"NUMBER (84)"Wave Height (ft)
WAVE_HEIGHT_UNITS16VARCHAR2 (30 Byte)
EST_ACT_FLAG17VARCHAR2 (1 Byte)Estimated/Actual (comes after Wave Height)
RAINFALL_LAST_EVENT18VARCHAR2 (5 Byte)
RAINFALL19"NUMBER (84)"
RAINFALL_UNITS20VARCHAR2 (30 Byte)
RAINFALL_DURATION_HRS21"NUMBER (42)"
RAINFALL_STN_DESC22VARCHAR2 (60 Byte)
RAINFALL_STN_DIST23"NUMBER (84)"
RAINFALL_STN_DIST_UNITS24VARCHAR2 (30 Byte)
PART_1_COMMENTS25VARCHAR2 (1000 Byte)Field conditions comments
ENTEROCICCUS_REMARK26VARCHAR2 (6 Byte)
ENTEROCICCUS_VALUE27"NUMBER (102)"
OTHER_REMARK28VARCHAR2 (6 Byte)
OTHER_MEAS_NAME29VARCHAR2 (50 Byte)
OTHER_VALUE30"NUMBER (102)"
LAB_ANALYSIS_COMMENTS31VARCHAR2 (255 Byte)Lab analysis comments
ANALYSIS_DATE32DATEAnalysis date
ANALYZER_SEQ33NUMBER (10)"Analyzer (entered as userid saved as user seq)
AVG_WATER_TEMP34"NUMBER (72)"Water Temperature
AVG_WATER_TEMP_UNITS35VARCHAR2 (30 Byte)Water Temperature Units
COLOR_CHANGE36VARCHAR2 (1 Byte)
COLOR_DESCRIPTION37VARCHAR2 (50 Byte)
ODOR_DESCRIPTION38VARCHAR2 (50 Byte)
ODOR_OTHER_DESCRIPTION39VARCHAR2 (50 Byte)
CLARITY_DESC40VARCHAR2 (30 Byte)Water Clarity
NTU41"NUMBER (102)"Turbidity Measurements
PART2_COMMENTS42VARCHAR2 (1000 Byte)
NO_BATHERS43NUMBER (8)
NO_IN_WATER44NUMBER (8)
NO_PEOPLE_BOATING45NUMBER (8)
NO_PEOPLE_FISHING46NUMBER (8)
NO_PEOPLE_SURFING47NUMBER (8)
NO_PEOPLE_WINDSURFING48NUMBER (8)
NO_PEOPLE_DIVING49NUMBER (8)
NO_PEOPLE_CLAMMING50NUMBER (8)
NO_PEOPLE_OTHER51NUMBER (8)
NO_PEOPLE_OTHER_DESC52VARCHAR2 (50 Byte)
PART3_COMMENTS53VARCHAR2 (1000 Byte)
FLOATABLES_FLAG54VARCHAR2 (1 Byte)
FLOATABLE_DESC55VARCHAR2 (255 Byte)
DEBRIS_AMOUNT56VARCHAR2 (30 Byte)
DEBRIS_TAR57VARCHAR2 (1 Byte)
DEBRIS_OIL58VARCHAR2 (1 Byte)
DEBRIS_TRASH59VARCHAR2 (1 Byte)
DEBRIS_PLASTIC60VARCHAR2 (1 Byte)
DEBRIS_MEDICAL61VARCHAR2 (1 Byte)
DEBRIS_OTHER62VARCHAR2 (1 Byte)
DEBRIS_OTHER_DESC63VARCHAR2 (255 Byte)
ALGAE_NEARSHORE64VARCHAR2 (30 Byte)"Amount of Algae in Nearshore Water (NONE LOW MODERATE HIGH)
ALGAE_ON_BEACH65VARCHAR2 (30 Byte)"Amount of Algae On Beach (NONE LOW MODERATE HIGH)
NO_GEESE66NUMBER (8)(I think we want to add a field for Number of Geese?)
NO_GULLS67NUMBER (8)Number of Gulls
NO_DOGS68NUMBER (8)
NO_ANIMALS_OTHER69NUMBER (8)
ANIMALS_OTHER_DESC70VARCHAR2 (50 Byte)
PART4_COMMENTS71VARCHAR2 (1000 Byte)
CURRENT_SPEED72NUMBER (8)
OTHER_REMARK273VARCHAR2 (6 Byte)
OTHER_MEAS_NAME274VARCHAR2 (50 Byte)
OTHER_VALUE275"NUMBER (102)"
LONGSHORE_CURRENT_UNITS76VARCHAR2 (30 Byte)
PH77"NUMBER (52)"
SWIM_AREAS_CHANNEL78VARCHAR2 (1 Byte)
WATER_LEVEL_DESC79VARCHAR2 (30 Byte)
DRIFT_DIRECTION80VARCHAR2 (10 Byte)
WAVE_DIRECTION81VARCHAR2 (10 Byte)
WAVE_CONDITIONS82VARCHAR2 (30 Byte)
NATURAL_DEBRIS_AMT83VARCHAR2 (30 Byte)
NATURAL_DEBRIS_TYPES84VARCHAR2 (255 Byte)
ALGAE_BOYANCY85VARCHAR2 (30 Byte)
ALGAE_COLOR86VARCHAR2 (30 Byte)
ALGAE_IN_SAMPLE87VARCHAR2 (1 Byte)
WIND_CONDITIONS88VARCHAR2 (30 Byte)
WILDLIFE_ONSHORE89VARCHAR2 (255 Byte)
WILDLIFE_IN_WATER90VARCHAR2 (255 Byte)
SWIM_AREAS_EAST91VARCHAR2 (1 Byte)
SWIM_AREAS_WEST92VARCHAR2 (1 Byte)
RAIN_INTENSITY93VARCHAR2 (30 Byte)
WAVE_INTENSITY94VARCHAR2 (15 Byte)
NUM_OUT_OF_WATER95NUMBER (8)
FLOAT_STREET_LITTER96VARCHAR2 (1 Byte)
FLOAT_FOOD97VARCHAR2 (1 Byte)
FLOAT_MEDICAL98VARCHAR2 (1 Byte)
FLOAT_SEWAGE99VARCHAR2 (1 Byte)
FLOAT_BLDG_MATERIALS100VARCHAR2 (1 Byte)
FLOAT_FISHING101VARCHAR2 (1 Byte)
FLOAT_HOUSEHOLD102VARCHAR2 (1 Byte)
FLOAT_OTHER103VARCHAR2 (1 Byte)
FLOAT_OTHER_DESC104VARCHAR2 (50 Byte)
DEBRIS_STREET_LITTER105VARCHAR2 (1 Byte)
DEBRIS_FOOD106VARCHAR2 (1 Byte)
DEBRIS_SEWAGE107VARCHAR2 (1 Byte)
DEBRIS_BLDG_MATERIALS108VARCHAR2 (1 Byte)
DEBRIS_FISHING109VARCHAR2 (1 Byte)
DEBRIS_HOUSEHOLD110VARCHAR2 (1 Byte)
ALGAE_TYPE_PERIPHYTON111VARCHAR2 (1 Byte)
ALGAE_TYPE_GLOBULAR112VARCHAR2 (1 Byte)
ALGAE_TYPE_FREEFLOATING113VARCHAR2 (1 Byte)
ALGAE_TYPE_OTHER114VARCHAR2 (1 Byte)
ALGAE_TYPE_OTHER_DESC115VARCHAR2 (50 Byte)
ALGAE_COLOR_LT_GREEN116VARCHAR2 (1 Byte)
ALGAE_COLOR_BRGHT_GREEN117VARCHAR2 (1 Byte)
ALGAE_COLOR_DRK_GREEN118VARCHAR2 (1 Byte)
ALGAE_COLOR_YELLOW119VARCHAR2 (1 Byte)
ALGAE_COLOR_BROWN120VARCHAR2 (1 Byte)
ALGAE_COLOR_OTHER121VARCHAR2 (1 Byte)
ALGAE_COLOR_OTHER_DESC122VARCHAR2 (50 Byte)
NUM_LOONS123NUMBER (8)
NUM_HERR_GULLS124NUMBER (8)
NUM_RING_GULLS125NUMBER (8)
NUM_CORMORANTS126NUMBER (8)
NUM_LONGTAIL_DUCKS127NUMBER (8)
NUM_SCOTER128NUMBER (8)
NUM_HORN_GREBE129NUMBER (8)
NUM_REDNECKED_GREBE130NUMBER (8)
NUM_OTHER131NUMBER (8)
NUM_OTHER_DESC132VARCHAR2 (50 Byte)
NUM_DEAD_FISH133NUMBER (8)
ECOLI_SAMPLE_TYPE134VARCHAR2 (30 Byte)Composite Sample? (N/Y)
WATER_TEST1_SAMPLE_TYPE135VARCHAR2 (30 Byte)
WATER_TEST2_SAMPLE_TYPE136VARCHAR2 (30 Byte)
ENTEROCICCUS_SAMPLE_TYPE137VARCHAR2 (30 Byte)
ENTEROCICCUS_UNITS138VARCHAR2 (30 Byte)
OTHER_MEAS_UNITS139VARCHAR2 (30 Byte)
OTHER_MEAS2_UNITS140VARCHAR2 (30 Byte)
DATE_ENTERED141DATE(Current date/time)
DATA_ENTRY_SEQ142NUMBER (10)Logged in by (default is the person who is logged on)
DATE_UPDATED143DATE(date/time when an update was made)
UPDATE_ENTRY_SEQ144NUMBER (10)(defaults to the person who was logged in when an update was made)
E_COLI_REMARK145VARCHAR2 (6 Byte)"E. coli remark (> < E NONE)
E_COLI_VALUE146"NUMBER (102)"E Coli Value
GLRI_SURVEY_ID147INTEGER
SURVEY_TIME148NUMBER
ALGAE_CATEGORY149NUMBER (1)
FECAL_MATTER_CATEGORY150NUMBER (1)
DEBRIS_CATEGORY151NUMBER (1)
WAVE_DIR_DEG152NUMBER
SC153NUMBER
TOTAL_NO_OF_BIRDS154NUMBER (8)
NOWCAST_ID155VARCHAR2 (30 Byte)
DAY_OF_SEASON156NUMBER (3)
STAGE_OF_SEASON157NUMBER (1)
DEW_POINT158"NUMBER (41)"
DEW_POINT_UNITS159VARCHAR2 (10 Byte)
LAKE_LEVEL160"NUMBER (63)"
LAKE_LEVEL_UNITS161VARCHAR2 (10 Byte)
NOAA_SURF_CURR_SPD162"NUMBER (64)"
NOAA_SURF_CURR_SPD_UNITS163VARCHAR2 (10 Byte)
NOAA_SURF_CURR_DIR164"NUMBER (74)"
EST_WATERSHED_E_COLI_LOADING165"NUMBER (122)"
EST_WATERSHED_E_COLI_LD_UNITS166VARCHAR2 (10 Byte)
NOWCAST_E_COLI_CONC167"NUMBER (102)"
NOWCAST_E_COLI_CONC_UNITS168VARCHAR2 (10 Byte)
NOWCAST_PROB_EXCEEDING_235169"NUMBER (52)"
NOWCAST_PROB_EXCEEDING_1000170"NUMBER (52)"
EASTWARD_WATER_VELOCITY_UC171"NUMBER (64)"
UC_UNITS172VARCHAR2 (10 Byte)
NORTH_WATER_VELOCITY_VC173"NUMBER (64)"
VC_UNITS174VARCHAR2 (10 Byte)
RAINFALL_24HR175"NUMBER (32)"
RAINFALL_24HR_UNITS176VARCHAR2 (10 Byte)
RAINFALL_48HR177"NUMBER (32)"
RAINFALL_48HR_UNITS178VARCHAR2 (10 Byte)
NOAA_WAVE_HEIGHT179"NUMBER (64)"
NOAA_WAVE_HEIGHT_UNITS180VARCHAR2 (10 Byte)
SC_UNITS181VARCHAR2 (10 Byte)
E_COLI_UNITS182VARCHAR2 (10 Byte)E Coli Units (the possible values are pulled from the beach information)
GLRI_E_COLI_BEACH_ACT183VARCHAR2 (1 Byte)
E_COLI_PROC184VARCHAR2 (200 Byte)
MONITOR_CATEGORY185VARCHAR2 (60 Byte)
SECCHI_TUBE_CM186NUMBER (8)Secchi Tube (cm)
MONITOR_FORM_FLAG187VARCHAR2 (1 Byte)
MISSING_REQUIRED_FLAG188VARCHAR2 (1 Byte)"In the desktop version this is a flag that is used to let users submit data even if not all of the required fields are filled in. We will need to discuss what the required fields are and if we want this option in the mobile version)
MISSING_REQUIRED_FLAG2189VARCHAR2 (1 Byte)%