6 rocklob business rules

6.1 Introduction to business rules

The following are a list of business rules pertaining to the rocklob database. A business rule is a written statement specifying what the information system (i.e., any system that is designed to handle rock lobster life cycle 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. Referential constraints, range checks, and algorithms both in the database and during data validation implement the formula and validation type rules.

6.2 Summary of rules

Puerulus collector locations (t_locations)

location	Location code, must be unique.
deploy_meth	Puerulus collector deployment method code, must be a valid code as listed in Appendix 1.

Puerulus catch from a collector (t_catch)

location	Must be equal to a location code held in the t_locations table.
date_checked	The date a collector is checked must be a legitimate date.
collector_no	Must be a unique number within all collectors checked at a location on a date.

Puerulus length frequencies (t_puer_lfreq)

location	Must be equal to a location code held in the t_locations table.
date_checked	The date a collector is checked must be a legitimate date. The date must also equal a date checked in the t_catch table for a matching location code.
collector_no	Must be a unique number within all collectors checked at a location on a date. The collector number must also equal a collector number in the t_catch table for a matching location code and date checked.
m_date	Date of measurement of pueruli and/or post-pueruli. Must be a legitimate date on or after date_checked.
len_p	Carapace length (mm), should fall within the reasonable range of 5-25.
no_p	Number of pueruli measured at the length specified by the len_p attribute. The value of len_p should not exceed 14.5.
no_pp	Number of post-pueruli measured at the length specified by the len_p attribute. The value of len_p should not be less than 14.5.

Puerulus length frequencies (t_puer_stage)

location	Must be equal to a location code held in the t_locations table.
date_checked	The date a collector is checked must be a legitimate date. The date must also equal a date checked in the t_catch table for a matching location code.
collector_no	Must be a unique number within all collectors checked at a location on a date. The collector number must also equal a collector number in the t_catch table for a matching location code and date checked.
stage	Puerulus life cycle stage number, must be a valid code as listed in Appendix 1.
no_a	Number of pueruli at the stage number, should fall within the reasonable range of 1-50.

Phyllosoma survey trip details (t_trip)

trip_code	Trip code, must be unique. Trip codes are in the following format: 3 character vessel code (see the vessels table in the rdb database for available codes); 2 digit year (e.g., 99 = 1999, 00 = 2000); 2 digit sequential trip number for each vessel each year.
proj_code	Project code must be a valid code within the NIWA project management system.
date_s	The start date of the trip must be a legitimate date.
date_f	The start date of the trip must be a legitimate date. Multiple column checks on date: The start date must not be later than the finish date.
areas	Each of the listed area codes must be a valid code as listed in the area_codes table in the rdb database.
mainspp	Each of the listed species codes must be a valid code as listed in the curr_spp table in the rdb database.

Phyllosoma survey station details (t_station)

trip_code	Must be equal to a trip code as listed in the t_trip table.
station_no	Must be a unique number within a single trip.
area	Area code must be a valid code as listed in the area_codes table in the rdb database.
course	Course must be within the range of 0 – 359 degrees.
date_s	The date at the start of a station must be a legitimate date.
time_s	Start time of the station must be a valid 24-hour time and fall within the range of 0 – 2359 hours.
fix_s } fix_f }	The method of position fix code must be valid code as listed in Appendix 1.
lat_s	Must be a valid latitude
NorS_s	Northern or Southern Hemisphere at station start, must be equal to either “N” or “S”.
long_s	Must be a valid longitude.
EorW_s	Longitude east or west at station start, must be equal to either “E” or “W”.
bot_gs	Depth of sea bottom must not be less than depth of gear
date_f	The date at the finish of a station must be a legitimate date. Multiple column checks on finish date: The date must fall within the range of the range of the trip start and finish dates.
time_f	Finish time of the station must be a valid 24-hour time and fall within the range of 0 – 2359. Multiple columns checks on date and time: The start date must not be later than the finish date and within a reasonable time period.
lat_f	Must be a valid latitude
NorS_f	Northern or Southern Hemisphere at station finish, must be equalto either “N” or “S”.
long_f	Must be a valid longitude.
EorW_f	Longitude east or west at station finish, must be equal to either “E” or “W”. Multiple columns checks on position: The finish position should be within a reasonable distance from the start position.
bot_gf	Depth of sea bottom must not be less than depth of gear
min_gdepth	Minimum gear depth must be less than or equal to the depth of gear at the start and finish of the station.
max_gdepth	Maximum gear depth must be greater than or equal to the minimum gear depth and the depth of gear at the start and finish of the station
gear_meth	Gear method code must be a valid code as listed in the meth_codes table in the rdb database.
gear_code	Must within the range 1 – 3 to relate to gear1, gear2, and gear3 respectively in the t_trip table.
gear_perf	The gear performance code must be valid code as listed in Appendix 1.
path	The path code must be valid code as listed in Appendix 1.
speed	The vessel’s recorded speed during the station should be within the range 0 – 5 knots and be reasonable for the gear method.
distance	The distance traveled during the station should be reasonable for the type of gear method.
	Multiple columns check on: distance; start and finish positions; and speed and start/finish times: The distance traveled during a station as calculated by (1) the difference between start and finish positions; (2) speed * elapsed time; and (3) recorded distance should be in approximate agreement.
head_code	Headline height code must be a valid code as listed in the t_headline_codes table in the rdb database.
distwing_code	Distance between trawl wings code must be a valid code as listed in the t_wing_dist_codes table in the rdb database.
distdoor_code	Distance between trawl doors code must be a valid code as listed in the t_door_dist_codes table in the rdb database.
wind_dir	Wind direction must fall within the range of 0-359, 999.
wind_force	Wind force must fall within the range of 0 – 12.
air_temp	Air temperature should fall within the reasonable range of 5 – 30.
air_press	Air pressure should fall within the reasonable range of 960 to 1040.
cloud_cov	Cloud cover must fall within the range of 0-8.
sea_cond	The sea condition code must be valid code as listed in Appendix 1.
sea_col	The sea colour code must be valid code as listed in Appendix 1.
swell_ht	The swell height code must be valid code as listed in Appendix 1.
swell_dir	Wind direction must fall within the range of 0-359, or equal 999.
bot_type	The bottom type code must be valid code as listed in Appendix 1.
bot_cont	The bottom contour code must be valid code as listed in Appendix 1.
surf_temp	Sea surface temperature should fall within the reasonable range of 5 – 28.
bot_temp	Sea bottom temperature should fall within the reasonable range of 3 – 25.
wind_spd	Wind speed should fall within the reasonable range of 0 - 30.
secchi	Secchi disc distance should fall within the reasonable range of 0 – 40.
moon	Moon phase must fall within the range of 1 - 4.

Phyllosoma survey station comments (t_stat_comm)

trip_code	Must be equal to a trip code as listed in the t_trip table.
station_no	Must be a unique number within a single trip. Multiple columns check trip code and station number: The combination of trip code and station number must exist in the t_station table.

Phyllosoma survey stage data (t_phy_stage)

trip_code	Must be equal to a trip code as listed in the t_trip table.
station_no	Must be a unique number within a single trip. Multiple columns check trip code and station number:The combination of trip code and station number must exist in the t_station table.
species	The species code must be valid code as listed in Appendix 1.
stage	The phyllosoma life history stage code must be valid code as listed in Appendix 1.

Phyllosoma survey DAS envelope summary (t_evp_summ)

trip_code	Must be equal to a trip code as listed in the t_trip table.
station_no	Must be a unique number within a single trip. Multiple columns check trip code and station number: The combination of trip code and station number must exist in the t_station table.
evp_no	Must be a unique number within a single trip.
lat_s	Must be a valid latitude.
long_s	Must be a valid longitude
time_s	Must be a valid date and time.
time_f	Must be a valid date and time.

Phyllosoma survey DAS data (t_dasdata)

trip_code	Must be equal to a trip code as listed in the t_trip table.
station_no	Must be a unique number within a single trip. Multiple columns check trip code and station number:The combination of trip code and station number must exist in the t_station table.
evp_no	Must be a unique number within a single trip.
tally_id	The DAS data tally id code must be valid code as listed in Appendix 1.
timestamp	Must be a valid date and time.

Puerulus development stage codes (t_stage_codes)

species	Species code must be a valid species code as listed in Appendix 1.
type	Must be equal to either “PHYLLOSOMA” or “PUERULUS”.
stage	Puerulus development stage code must contain a value. Multiple columns check on species, type, and stage: The values in the species, type, and stage attributes must be a unique combination.

Previous | Next

Updated : 16 November 2007