6. iki Database Business Rules

6.1 Introduction to business rules

The following are a list of business rules pertaining to the iki database. A business rule is a written statement specifying what the information system (i.e., any system that is designed to handle longline data) must do or how it must be structured.

There are three recognized 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 data 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

Trip record (t_trip)

trip_no	Must contain a value that is unique. Should be either an integer greater than zero, or a 4-character alphabetic code followed by an integer greater than zero.
date_s	The start date of the trip must be a legitimate date within the specified period the data set covers and should be after 1^st October 1998.
date_f	The finish date of the trip must be a legitimate date within the specified period the data set covers and should be after 1^st October 1998.
	Multiple column checks on date: The start date must not be later than the finish date and within a reasonable time period.
bb_type	The backbone type, must be a valid code as listed in Appendix 1.
bb_break	The backbone breaking strain should fall within the reasonable range 50 to 500 (kg).
snood_break	The snood line breaking strain should fall within the reasonable range 10 to 100 (kg).
clip_type	The clip type, must be a valid code as listed in Appendix 1.
hook_type	The hook type, must be a valid code as listed in Appendix 1.
hook_size	The hook size should fall within the reasonable range of 12 to 18.
snood_lgth	The snood length should be within the reasonable range of up to 2.5 (m).
snood_dist	The snood distance should be within the reasonable range of up to 4.0 (m).
gear_meth	The gear method code, must be a valid code as listed in Appendix 1.
no_effort	The total number of units of effort for the trip must be an integer greater than one, should fall within the reasonable range of 1 to 200.
target_sp	The target species code must be a valid species code as listed in the curr_spp table in the rdb database.
project_code	Project code must be a valid code within the NIWA project system.

Trip catch weight table (t_trip_catch)

trip_no	Must be equal to a trip code held in the t_trip table.
species	Must be a valid species code as listed in the curr_spp table in the rdb database.
weight	Must be a number greater than zero and should be within the reasonable range of 5 to 50,000.

Effort record table (t_effort)

trip_no	Must be equal to a trip code held in the t_trip table.
effort_no	Must be a unique integer greater than zero, within all the effort records with the same trip number.
stat_area	Must be one of the valid statistic area codes for the New Zealand Exclusive Economic Zone (EEZ) as listed in the area_codes table of the rdb database and shown in Appendix 3.
date_s	The start date of the set must be a legitimate date and should be after 1^st October 1998.
	Multiple column checks on trip start and finish date, and the effort start date: The date must fall within the range of the trip start and finish dates.
time_s	Time set must be a valid time, within the range of 0 to 2359, and be New Zealand Daylight Time.
fix_s	Start fix method, must be a valid fix method code as listed in Appendix 1.
lat_s	Must be a valid latitude and degrees should fall within the reasonable range of 33 to 38 South.
long_s	Must be a valid longitude and degrees should fall within the reasonable range of 164 East to 170 West.
EW_s	Longitude East or West at start, must be equal to either "E" or "W".
gear_s	Gear depth at start should fall within the reasonable range of 5 to 120.
depth_s	Bottom depth at start, should fall within the reasonable range of 5 to 120.
date_f	The finish date of the set must be a legitimate date and should be after 1^st October 1998.
	Multiple column checks on trip start and finish date, and the effort finish date: The date must fall within the range of the trip start and finish dates.
time_f	Time finish of set must be a valid time, within the range of 0 to 2359, and be New Zealand Daylight Time.
	Multiple column checks on start and finish date and time: The finish time, finish date must not be before set start time, start date and within a reasonable time period of 5 to 600 minutes.
fix_f	Finish fix method, must be a valid fix method code as listed in Appendix 1.
lat_f	Latitude degree at finish, must be a valid latitude and degrees should fall within the reasonable range of 33 - 38 South.
long_f	Longitude degree finish, must be a valid longitude and degrees should fall within the reasonable range of 164 East to 170 West.
EW_f	Longitude East or West at finish, must be equal to either "E" or "W".
	Multiple column checks on position: The finish position must be within a reasonable distance from the start position. The finish position must fall within the given statistical area for the set (where position given).
gear_f	Gear depth at end of set, should fall within the reasonable range of 5 to 120.
depth_f	Bottom depth at end of set, should fall within the reasonable range of 5 to 120.
min_gear	Minimum depth of gear (at any point), should fall within the reasonable range of 5 to 120.
max_gear	Maximum depth of gear (at any point), should fall within the reasonable range of 5 to 120.
	Multiple column checks on start & finish gear depths and minimum & maximum gear depths:
	Start gear depth must be within the range of min_gear and max_gear. Finish gear depth must be within the range of min_gear and max_gear.
bb_length	Backbone length must be a number greater than zero and should fall within the reasonable range of 1.0 to 10.0 (nm) hooks Number of hooks must be an integer greater than zero and should fall within the reasonable range of 100 to 2500.
hook_type	The hook type, must be a valid code as listed in Appendix 1 hook_size Hook size must be a number greater than zero and should fall within the reasonable range of 12 to 18.
snood_lgth	Snood length must be a number greater than zero and should be within the range of 0.3 to 2.5.
snood_dist	Snood distance must be a number greater than zero and should be within the range of 0.5 to 4.0.
gear_meth	The gear method code, must be a valid code as listed in Appendix 1.
bait_s1}	Bait species must be a valid code as listed in Appendix 1.
bait_s2}
bait_s3}
bait_s4}
bait1}	The percentage of hooks using the corresponding bait species must be an integer bait2} within the range of 0 " 100.
bait3}
bait4}
	Multiple column checks on bait: The sum of the bait1 .. bait4 must equal 100.
wind_dir	Wind direction must fall within the range of 0 to 359 or equal 999.
wind_knots	Wind speed in knots must be an integer greater than zero and should fall within the reasonable range of 0 to 40.
cloud	Cloud cover in eighths, must fall within the range of 0 to 8.
sea_cond	Sea condition using the beaufort scale, must be a valid code as listed in Appendix 1. Sea condition should fall within the reasonable range of 0 - 6.
sea_col	Sea colour, must be a valid code as listed in Appendix 1.
swell_ht	Swell height, must be a valid code as listed in Appendix 1.
swell_dir	Swell direction must fall within the range of 0 to359 or equal 999.
bot_type	Bottom type, must be a valid code as listed in Appendix 1.
bot_cont	Bottom contour, must be a valid code as listed in Appendix 1.

Fish Characterisation page table (t_page)

	Multiple column check on trip number and effort number: The combination of trip_no and effort_no must exist in the t_effort table.
page_no	Page must be an integer greater than zero and should fall within the reasonable range of 1 to 30.
species	Must be a valid species code as listed in the curr_spp table in the rdb database.
page_s	Start time of this page must be a valid time, within the range of 0 to 2359, and be New Zealand Daylight Time.
page_f	Finish time of this page must be a valid time, within the range of 0 to 2359, and be New Zealand Daylight Time.
	Multiple column checks on page start and finish times: The finish time must not be before set start time and should be within a reasonable time period of 10 to 300 minutes.

Individual fish table (t_fish)

	Multiple column check on trip number, effort number and page number: The combination of trip_no, effort_no, and page_no must exist in the t_page table.
species	Must be a valid species code as listed in the curr_spp table in the rdb database.
lgth	Fish length must be an integer greater than zero and should fall within the reasonable range of 10 to 120 (cm).
	Multiple column check on species and length: The fish length should be within the reasonable range for the species as listed in Appendix 1.
sex	Sex , must be a valid code as listed in Appendix 1
alive	Alive, must be a valid code as listed in Appendix 1
bait	Bait, must be a valid bait code as listed in Appendix 1
hook_p	Hook position, must be a valid code as listed in Appendix 1
wound	Wounding, must be a valid code as listed in Appendix 1
rupture	Rupture, must be a valid code as listed in Appendix 1
state	State must be a valid code as listed in Appendix 1

Effort catch table (t_catch)

	Multiple column check on trip number and effort number: The combination of trip_no and effort_no must exist in the t_effort table.
species	Must be a valid species code as listed in the curr_spp table in the rdb database.
grade	Must be a valid quality grade code as listed in the t_grade_code table.
no_bins	Must be a number greater than zero and should be within the reasonable range of 1 to 200.
bin_wgt	Must be a number greater than zero and should be within the reasonable range of 12 to 30.
no_sampled	Must be a number greater or equal to zero and should be within the reasonable range of 0 to 25.

Fish quality grade code table (t_grade_code)

grade	Must have a value entered and be a 3-character alphabetic code..
description	Must have a value entered and can any combination of up to 30 ASCII characters.

Hook appendage experiment table (t_hook_shot)

shot_no	Must have a value entered and be an unique integer greater than zero.
project_code	Project code must be a valid code within the NIWA project system.
date_s	The start date of the shot must be a legitimate date and should be after 1^st October 1998.
vessel	Can be any combination of up to 30 ASCII characters.
bb_type	The backbone type, must be a valid code as listed in Appendix 1.
bb_break	The backbone breaking strain should fall within the reasonable range 50 to 500 (kg).
hook_type	The hook type, must be a valid code as listed in Appendix 1.
snood_lgth	The snood length should be within the reasonable range of up to 2.5 (m).
snood_dist	The snood distance should be within the reasonable range of up to 4.0 (m).
snood_break	The snood line breaking strain should fall within the reasonable range 10 to 100 (kg).

Hook appendage experiment table (t_hook_append)

shot_no	Must have a value entered and be a valid shot number as listed in the t_hook_shot table..
block	Must have a value entered and be an integer greater than 0. Should be within the reasonable range of 1 to 3.
treat	Must have a value entered and be an integer within the reasonable range of 1 to 9.Valid treatments are list in Appendix 1.
result	Must be a valid result code as listed in Appendix 1.
species	Must be a valid species code as listed in the curr_spp table in the rdb database.
lgth	Fish length must be an integer greater than zero and should fall within the reasonable range of 10 to 120 (cm).
	Multiple column check on species and length: The fish length should be less than the maximum-recorded fish length for the species as recorded in the curr_spp table in the rdb database. Maximum lengths for some of the more common species are listed in Appendix 1.
hooked	Must be a valid hooked code as listed in Appendix 1.
	Multiple column check on species and hooked code: Hooked must equal "WING" if species is a bird. Conversely, if a bird species is caught, the hooked code must equal "WING".
season	Must be equal to 's" or "W"..

Previous | Next

Updated : 16 November 2007