6 rec_data business rules

6.1 Introduction to business rules

The following are a list of business rules applying to the rec_data database. A business rule is a written statement specifying what the information system must do or how it must be structured.

In this instance the information system is any system that is designed to handle recreational fishing data.

There are three recognised types of business rules:

Fact Certainty or an existence in the information system.
Formula Calculation employed in the information system.
Validation Constraint on a value in the information system.

Fact rules are shown on the ERD by the cardinality; e.g., one-to-many, of table relationships.

Formula and Validation rules are implemented by referential constraints, range checks, and algorithms both in the database and during validation.

Validation rules may be part of the preloading checks on the data as opposed to constraints or checks imposed by the database. These rules sometimes state that a value should be within a certain range. All such rules containing the word 'should" are conducted by preloading software.

The use of the word 'should" in relation to these validation checks means that a warning message is generated when a value falls outside this range and the data are then checked further in relation to this value.

6.2 Summary of rules

Boat ramp session details (t_session)

sess_no	Session number must be unique.
ramp	Code for a ramp or beach. Must be a valid code as listed in t_ramp_codes.
sess_date	Session date must be a valid date, within a reasonable range for the survey, as listed in Appendix 3.
sess_time_s	Session start time must be a valid 24-hour time and fall within the range of 0 - 2359 hours.
sess_time_f	Session finish time must be a valid 24-hour time and fall within the range of 0 - 2359 hours.
t_code	Must be a valid code, as listed in Appendix 3.
iv_type	Interview type must be a valid code as listed in Appendix 3.
platform_type `	Must be a valid code as listed in Appendix 3.
day_type	Must be a valid code, as listed in Appendix 3.
trailer_s	The number of trailers should fall within the reasonable range of 0 - 200.
trailer_m	The number of trailers should fall within the reasonable range of 0 - 200.
trailer_f	The number of trailers should fall within the reasonable range of 0 - 200.
boat_not_iv	The number of boats not interviewed should fall within the reasonable range of 0 "
low_tide	The time of low tide must be a valid 24 hour time and fall within the range of 0 - 2359 hours.
high_tide	The time of high tide must be a valid 24 hour time and fall within the range of 0 - 2359 hours.
swell_ht	The swell height must be a number greater than or equal to zero.
sea_state	Must be a valid code as listed in Appendix 3.
rain	Must be a valid code as listed in Appendix 3.
overhead	Overhead conditions must be a valid code as listed in Appendix 3.
wind_speed	Must be a valid code as listed in Appendix 3.
wind_dirn	Must be a valid code as listed in Appendix 3.
wind_knots	Wind speed in knots should be within a reasonable range of 0 to 59.
moon	The moon phase code must be an integer greater than zero.
region	Survey base region must be a valid code of N or C sess_type The session type must be a valid code as listed in Appendix 3.
sess_type	The session type must be a valid code a listed in Appendix 3.
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Count of shellfish harvest fishers (t_fisher_count)

sess_no	Session number must be equal to a session number in t_session.
hr	Time of count must have a value and be a valid 24-hour time and fall within the range of 0 - 2359 hours.
fish_loc	Must have a value and be an integer greater than zero.
	Multiple column checks on session number, time and spatial strata: The values in the sess_no, hr and fish_loc attributes must be a unique combination.
no_pickers	The number of fishers should fall within the reasonable range of 0 - 99.
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Boat or shellfish harvest group details (t_group)

sess_no	Session number must be equal to a session number in t_session.
group_no	Must be a unique number within a single session.
group_type	Should be a valid code as listed in the t_boat_codes table.
outcome	Interviewed or not etc code must be a valid code as listed in Appendix 3.
time_i	Time of intercept must be a valid 24-hour time and fall within the range of 0 - 2359.
	Multiple column checks on time of intercept, session start time and session finish time: The time of intercept must be between the session start and session finish times.
no_fishers	The number of fishers must be an integer greater than or equal to zero.
no_male	The number of male fishers must be an integer greater than or equal to zero.
no_female	The number of female fishers must be an integer greater than or equal to zero.
	Multiple column checks on no_fishers, no_male and no_female: The number of male and female fishers must not exceed no_fishers
region	Survey base region must be a valid code of N or C
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Shore side interview eg boat ramp (t_interview)

	Multiple column checks on session number and group number: The combination of session number and group number must exist in the t_group table.
fisher_no	The fisher number must have a number either 1 or an integer greater than zero.
combo	The combo attribute must have a value.
	Multiple column checks on session number, group number, fisher number and combo: The values in the sess_no, group_no, fisher_no and combo attributes must be a unique combination.
fish_zone	The fishing zone should be a valid code as listed in t_zonef_codes for that survey.
sub_region	Must be a valid sub_region as listed in Appendix 3.
fish_loc	The fishing locality code must be a valid code as listed in t_locality_codes.
fish_meth	The fishing method code must be in the table t_fishmeth_codes for that meth_type .
no_rods	The number of lines used must be an integer greater than zero and should be within a reasonable range of 1 - 9.
no_ll_hooks	The number of hooks recorded must be an integer greater than zero and should be within a reasonable range of 5 - 99.
burley	The code indicating if ground bait was used should be either "Y" or "N".
fish_finder	The code indicating if a fish-finder was used should be either "Y" or "N".
target_spp	Each of the listed species codes must be a valid code as listed in the curr_spp table in the rdb database.
fish_time_s	Fishing start time must be a valid 24-hour time and fall within the range of 0 - 2359.
fish_time_f	Fishing finish time must be a valid 24-hour time and fall within the range of 0 - 2359.
not_fish_t	Time not fishing must be a valid 24-hour time and should fall within the reasonable range of 0 - 1200.
	Multiple column checks on fishing time: The fishing start time must not be greater than the fishing finish time. The not fishing time must be less than the difference between the fishing finish time and the fishing start time.
hrs_fished	Hours spent fishing must be a positive value and should be within a reasonable range of 0.1 - 24.0.
days_per_period	The days fished in the period must be an integer greater than zero.
f_hist_period	The number of days must be an integer greater than zero.
	Multiple column checks on days fished in the fishing history time period: The number of days_per_period must not be greater than the f_hist_period.
yearsfished	The number of years fishing must be a positive value and fall within a reasonable range of 0 to 90.
sex	The value for sex must equal "M" or "F".
race	The code for race must be a valid value: i.e., one of : E, M, P, A, N.
int_before	The value for int_before must equal 'Y' or 'N'
age_gp	The age group code must be a valid code as listed in Appendix 3.
fish_diary	The code indicating if they kept a fishing diary must be either "Y" or "N".
maf_diary	The code indicating if they kept a MAF fishing diary must be either "Y" or "N".
phone	Code to indicate if their house has a telephone, must be either "Y" or "N".
area	The area code must be a valid code as listed in the area_codes table of the rdb database.
region	The survey base region code must be a valid code of either "N" or "C".
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Catch weights for shellfish harvest (t_weight)

	Multiple column checks on session number, group number, fisher number and combo: The combination of session number, group number, fisher number and combo must exist in the t_interview table, and must be a unique combination.
species	Must be a valid species code as listed in the curr_spp table of the rdb database.
no_fish	The number of fish must be an integer greater than zero and should fall within the reasonable range of 1 - 400.
est_wt	The estimated weight must a positive value within reasonable limits (0 - 99 kg)
agreed_wt	The value obtained by weighing the catch must a positive value within reasonable limits (0 - 99 kg)
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Fish length from boat ramp type surveys (t_length)

	Multiple column checks on session number, group number, fisher number and combo: The combination of session number, group number, fisher number and combo must exist in the t_interview table, and must be a unique combination.
species	Must be a valid species code as listed in the curr_spp table of the rdb database.
lgth	The fish length should fall within reasonable limits of 5 - 350.
weight	The fish weight should fall within the reasonable range of 0.05 to 150 kg.
no_fish	The number of fish must be an integer greater than zero and should fall within the reasonable range of 1 - 400.
observd	The observed code must be a valid code as listed in t_observd_codes.
meas_meth	The fish measurement method must be a valid code as listed in the table t_fish_meas_codes of the rdb database.
cpue	Flag to include in CPUE analysis must be either "A" or "X".
region	The survey base region code must be a valid code of either "N" or "C".
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Observed status codes (t_observd_codes)

observd	The observed code must be a valid code as listed in Appendix 3.

Observer details (t_observer)

sess_no	Session number must be equal to a session number in t_session.
run_no	The run number must be an integer greater that zero and be unique within a session.
group_no	The group number should be an integer greater than zero.
	Multiple column checks on session number, run number, group number, and activity number: The values in the sess_no, run_no, group_no and activity_no attributes must be a unique combination.
start_time	The run start time must be a valid 24-hour time and fall within the range of 0 - 2359.
run_end_t	The run end time must be a valid 24-hour time and fall within the range of 0 - 2359.
	Multiple column checks on start_time and run_end_t : The run end time should be greater than the start time rotation
rotation	Code for the direction of the run must be a valid code of A or C, for Anticlockwise or Clockwise respectively.
fish_zone	The fishing zone should be a valid code as listed in t_zonef_codes for that survey.
fish_loc	The fishing locality code must be a valid code as listed in t_locality_codes.
fish_meth	The fishing method code must be in the table t_fishmeth_codes for that meth_type .
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Observer counts (t_obs_count)

sess_no	Session number must be equal to a session number in t_session.
run_no	The run number must be an integer greater that zero and be unique within a session.
group_no	The group number should be an integer greater than zero.
	Multiple column checks on session number, run number, group number, and activity number: The values in the sess_no, run_no, group_no and activity_no attributes must be a unique combination.
activity_no	Must be an integer, and should be greater than zero
tally	The counts of fishing activity must be an integer greater than zero.
count-code	The codes to identify what was counted must be a valid code as listed in t_count_codes.
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Diary survey intercept (t_phone)

zonel	Zone lived in must be a valid code as listed in Appendix 3.
resp	Respondent number must be unique within each survey.
fishers	If there are fishers in the household must be a valid code in the range 1 - 2.
not15	If the fisher(s) are LE 14 the valid code is 1 (for Yes).
no_fishers	The number of fishers should fall within the reasonable range of 0 - 10.
no_LE14	The number of fishers less than or equal to 14 years old should fall within the reasonable range of 0 - 8.
no_commercial	The number of commercial fishers should fall within the reasonable range of 0 - 10.
commercial	Code to indicate if they are a commercial fisher must be an integer in the range 1 - 2.
trips	The number of fishing trips must be a valid code in the range 1 - 5.
intend	Intend going fishing code must be a valid code as listed in Appendix 3.
SNA10	Snapper catch code must be a valid code in the range 1 - 3.
KAH10	Kahawai catch code must be a valid code in the range 1 - 3.
BCO10	Blue cod catch code must be a valid code in the range 1 - 3.
meas	Willing to measure fish lengths code must be a valid code in the range 1 - 2.
gp_size	Fishing group size must be a valid code in the range 1 - 8.
fish_meths	The fishing method codes must be valid codes as listed in the t_fishmeth_codes table.
meth_type	Fishing method type code must be valid codes as listed in the t_fishmeth_codes table.
sex	Sex code must be a valid code in the range 1 - 2.
age_gp	The age group code must be a valid code as listed in Appendix 3.
ethnic	The code for ethnic group must be a valid code as listed in Appendix 3.
fish_club	Belong to a fishing club code must be a valid code in the range 1 to 2 (for Y or N).
boat_club	Belong to a boating club code must be a valid code in the range 1 to 2 (for Y or N).
dive_club	Belong to a diving club code must be a valid code in the range 1 to 2 (for Y or N).
sca_fish	Fished for scallops code must be a valid code in the range 1 to 2 (for Y or N).
sca_dive	Number of scallop dive trips should fall within the reasonable range of 0 - 20.
sca_dredge	Number of scallop dredge trips should fall within the reasonable range of 0 - 30.
diarist_yn	Prepared to keep a diary must be a valid code in the range 1 to 2 (for Y or N).
survey	Survey code must be a valid code as listed in the t_survey_codes table.

Central region scallop fishers survey (t_sca)

resp	The respondent number must be equal to a resp number in t_phone.
area_sca	Must be a valid scallop area as listed in Appendix 4.
	Multiple column checks on respondent and scallop area: The scallop area must be a unique code for a single respondent.
no_trips	The number of trips should fall within the reasonable range of 1 - 39.
limit	The number of trips should fall within the reasonable range of 0 - 39.
avg_ctch	The average catch should fall within the reasonable range of 0 - 100.
survey	Survey code must be a valid code as listed in the t_survey_codes table.
	Multiple column checks on respondent number, scallop area and survey: The values in the resp, SCAarea and survey attributes must be a unique combination.

Diarists response status (t_response)

key	The key must be a unique number.
zonel	Zone lived in must be a valid code as listed in Appendix 3.
resp	Respondent number must be unique within each survey.
year_s	Year start must be a valid year and should fall within a reasonable range for the survey as listed in Appendix 3.
month_s	Month start must be an integer representing a valid month (1-12).
day_s	Day start must be an integer representing a valid day.
quarter	Must be an integer in the range 1-4.
response	The response status must be a valid code as listed in Appendix 3.
survey	Survey code must be a valid code as listed in the t_survey_codes table.
	Multiple column checks on respondent number, year start, month start and survey: The values in the survey, resp, year_s and month_s attributes should be a unique combination.

Diarist's fishing effort (t_effort)

trip_no	Trip number, must be unique.
resp	The diarist respondent number must be an integer greater than zero.
resp_status	Must be in the range of 1 - 6.
trip_date	The date of the trip must be a valid date within reasonable bounds for the survey as listed in Appendix 3.
zonel	The zone lived in code must be a valid code as listed in Appendix 3.
fish_zone	The fishing zone code must be a valid code as listed in the t_zonef_codes table.
CRAarea	The rock lobster area code must be in the range A - H.
SCAarea	The scallop area code must be in the range A - I.
fish_loc	The fishing locality code must be a valid code as listed in the t_locality_codes table.
qma	The quota management area fished must be within the range of 1-5, 7-9.
hrs	The fishing time should be within a reasonable range of 0.1 - 120.
ramp	The code for the boat departure point must be a valid code as listed in the t_ramp_codes table.
target_sp	Each of the listed species codes must be a valid code as listed in the curr_spp table in the rdb database.
fish_meth	The fishing method code must be a valid code as listed in the t_fishmeth_codes table.
meth_type	The fishing method type must be a valid code as listed in the t_fishmeth_codes table.
boat	The boat type code must be a valid code and fall within the range of 1 - 5 as listed in Appendix 3
group_size	The number of people in the fishing group should fall within the reasonable range of 1 - 40.
survey	Survey code must be a valid code as listed in the t_survey_codes table.
	Multiple column checks on survey and respondent number: The combination of respondent and survey must exist in the table t_phone.

Diarists catch details (t_catch)

trip_no	The trip number must be equal to a trip number as listed in the t_effort table.
species	Must be a valid species code as listed in the curr_spp table of the rdb database.
no_caught	The number of fish caught should fall within the reasonable range of 1 - 750.
weight	The weight of fish caught should fall within the reasonable range of 0.1 - 150.
survey	Survey code must be a valid code as listed in the t_survey_codes table.
	Multiple column checks on trip number and species: The values in the trip_no and species attributes must be a unique combination.

Diarists fish lengths (t_lgth)

trip_no	The trip number should be equal to a trip number as listed in the t_catch table.
resp	The respondent number must be equal to a respondent number in the t_effort table.
trip_date	The date of the fishing trip must be a valid date.
species	Must be a valid species code as listed in the curr_spp table of the rdb database.
lgth	The length of the fish should fall within the reasonable range of 5 - 200.
no_fish	The number of fish should fall within the reasonable range of 1 - 50.

Previous | Next

Updated : 19 February 2008