6 beach business rules
6.1 Introduction to business rules
The following are a list of business rules applying to the beach
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 beach sampling 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
Beach survey details (t_survey)
|
survey
|
Survey code must be present and be an unique value. Survey
codes are of the format: up to a 6-character alphanumeric to
denote the location; followed by a twodigit year ranging from
70-99 and 00 on for years since 1970; and a 2-digit sequential
number to denote the number of surveys carried out at that
location for that year.
|
|
date_s
|
Start date must be a valid date.
|
|
date_f
|
Finish date must be a valid date.
Multiple column check on start date and finish date:
Finish date must be equal to, or after, the survey start date.
|
|
areas
|
Each area code within the areas list must be a valid area code
as listed in the area_codes table of the rdb database. A comma
must separate each area code.
|
|
mainspp
|
Each species code within the main species list must be a valid
species code as listed in the curr_spp table of the rdb database.
A comma must separate each species code.
|
Stratum details (t_stratum)
|
survey
|
Must be valid survey code as listed in the t_survey table.
|
|
stratum
|
Must have a value entered and be an unique value within a
survey. Stratum code must equal "1" or any combination
of up to 4 alphanumeric characters.
|
|
area_m2
|
Must be an integer greater than zero.
|
Transect line details (t_transect)
|
|
Multiple column checks on survey and stratum:
The combination of survey code and stratum code must exist in
the t_stratum table.
|
|
transect
|
Transect number must be present and be an unique value within
a stratum. Can be any combination of up to 5 alphanumeric
characters.
|
|
area
|
Must be a valid area code as listed in the area_codes table in
the rdb database.
|
|
phase
|
Must be an integer greater than zero and should be within the
reasonable range of 1 to 2.
|
|
latitude
|
Must be a valid latitude within the range of 90 to -90.
|
|
longitude
|
Must be a valid longitude within the range of 0 to 360.
|
|
bed_no
|
Must be a valid bed number as listed in the t_bed table.
|
Quadrat details (t_quadrat)
|
|
Multiple column checks on survey, stratum and transect
number:
The combination of survey code stratum code and transect
number must exist in the t_transect table.
|
|
quadrat
|
Quadrat number must be entered, greater than zero, and be an
unique value within a transect line.
|
|
area_m2
|
Must be a number greater than zero and should be within the
reasonable range of 0.06 to 1.00.
|
|
depth
|
Must be a number greater than zero and should be within the
reasonable range of 0.10 to 0.30
|
|
x_coord
|
Should be either:
an integer between the range of 0 to 1600 for metres
along an x-axis
an integer between the range of 1660000 to 1790000
representing a valid longitude in degrees and decimal minutes
a short text describing an azimuth and a distance.
|
|
y_coord
|
Should be either:
an integer between the range of -45 to 500 for metres up
and down an y-axis
an integer between the range of 340000 to 470000
representing a valid latitude in degrees and decimal minutes
|
|
in_out_bed
|
Must be a valid value as listed in Appendix 1.
|
Replicate details (t_replicate)
|
|
Multiple column checks on survey, stratum, transect and
quadrat number:
The combination of survey code stratum code, transect and
quadrat number must exist in the t_quadrat table.
|
|
replicate
|
Replicate number must be entered and be an unique integer,
greater than zero, for each quadrat. Should be within the
reasonable range of 1 to 2.
|
|
date_sampled
|
Must be a valid date.
Multiple column check on date sampled, survey start date
and survey finish date:
Date sampled must be on or after the survey start date and on
or before the survey finish date.
|
|
sieve_size
|
Must be a integer greater than zero and should be within the
reasonable range of 2 to 5.
|
Catch details (t_catch)
|
|
Multiple column checks on survey, stratum, transect,
quadrat and replicate number:
The combination of survey code stratum code, transect, quadrat
and replicate number must exist in the t_replicate table.
|
|
species
|
Must be a valid species code as listed in the curr_spp table
in the rdb database.
|
|
no_fish
|
Must be an integer greater than zero and should be within the
reasonable range of 1 to 1000.
Multiple column checks on species and number of fish:
The number of fish per species should be less than the
reasonable maximum as listed in Appendix 1.
|
|
lf_taken
|
Length frequency taken flag must be equal to either an "Y"
or an "N".
|
|
samp_meth
|
Must be a valid sample selection method code as listed in the
t_samp_sel_codes table in the rdb database.
|
|
meas_meth
|
Must be a valid fish measurement method code as listed in the
t_fish_meas_codes table in the rdb database.
|
|
weight
|
Must be a number greater zero and should be within the
reasonable range of 0.02 to
|
|
wt_meth
|
Must be a valid weighing method code as
listed in the t_wt_meth_codes table in the rdb database
|
|
samp_wt
|
Must be a number greater or equal to zero and should be within
the reasonable range of 0.02 to 0.80
Multiple column checks on sample weight and catch weight:
Sample weight must be less than or equal to catch weight.
|
|
|
Multiple column checks on sample weight and sample
selection method:
Sample weight must be greater than zero when there is a sample
selection method.
|
Length frequency details table (t_length)
|
|
Multiple column checks on survey, stratum, transect,
quadrat, replicate number and species code:
The combination of survey code stratum code, transect,
quadrat, replicate number and species code must exist in the
t_catch table.
|
|
lgth
|
The fish length must be an integer greater than zero and
should be within the reasonable range of 2 to 155.
|
|
|
Multiple columns check on species and length:
The fish length should be within a reasonable range for the
species. Minimum and maximum lengths by species are listed in
Appendix 1.
|
|
percent_samp
|
Must be a number greater than 0 and less than or equal to 100.
|
|
no_a
|
Must be a number greater than or equal to zero and within the
reasonable range of 0 to 140.
|
|
|
Length and weight details for individual shellfish table
(t_lgth_wgt)
Multiple column checks on survey, stratum, transect,
quadrat, replicate number and species code:
The combination of survey code stratum code, transect,
quadrat, replicate number and species code must exist in the
t_catch table.
|
|
fish_no
|
Must be number greater than zero and be unique within a catch.
|
|
lgth
|
The fish length must be an number greater than zero and should
be within the reasonable range of 2 to 155.
|
|
|
Multiple columns check on species and length:
The fish length should be within a reasonable range for the
species. Minimum and maximum lengths by species are listed in
Appendix 1.
|
|
weight
|
The weight of the fish must be a number greater than zero and
should be within the reasonable range of 0.01 to 125.00
Multiple columns check on species, length and weight:
The fish weight should be less than a reasonable maximum fish
weight for the species and be reasonable for the fish length.
Minimum and maximum fish weights by species as listed in Appendix
1.
|
Shellfish bed table (t_bed)
|
|
Multiple column checks on survey and stratum:
The combination of survey code and stratum code must exist in
the t_stratum table.
|
|
bed_no
|
Must be an integer greater than one and should be within the
reasonable range of 1 to 200.
|
|
phase
|
Must be an integer greater than zero and should be within the
reasonable range of 1 to 2.
|
|
locality
|
Can be any combination of up to 40 ASCII characters.
|
|
latitude
|
Must be a valid latitude within the range of 90 to -90.
|
|
longitude
|
Must be a valid longitude within the range of 0 to 360.
|
|
bed_length
|
Must be an integer greater than zero and should be within the
reasonable range of 20 to 300.
|
|
bed_width
|
Must be an integer greater than zero and should be within the
reasonable range of 5 to 40
|
Shellfish bed density table (t_bed_density)
|
|
Multiple column checks on survey, stratum and bed number:
The combination of survey code, stratum code and bed number
must exist in the t_bed table.
|
|
species
|
Must be a valid species code as listed in the curr_spp table
in the rdb database.
|
|
density_area_m2
|
Must be an integer greater than to zero and should be within
the range of 10 to
|
|
density
|
Must be an integer greater than or equal
to zero and should be within the range of 80 to 3800.
|