|
|
6 aer_sight business rules
6.1 Introduction to business rules
The following are a list of business rules applying to the
aer_sight database. A business rule is a written statement specifying
what the information system (i.e., any system that is designed to
handle aerial sighting data) must do or how it must be structured.
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
Flight group details (t_flight_group)
|
flt_grp
|
Flight group number must be an integer greater than zero, and
must be unique.
|
|
Date
|
Must be a valid date and should be greater than 21 Jun 1976.
|
|
pilot_code
|
Must be an integer, and be a valid code as listed in the
t_pilot_code table.
|
|
customer_code
|
Must be an integer, and must be a valid code as listed in the
t_customer_code table.
|
|
aircraft_code
|
Must be an integer greater than or equal to zero, and must be
a valid code as listed in the t_aircraft_code table.
|
|
vessel_code
|
Must be an integer greater than or equal to zero and must be a
valid code as listed in the t_vessel_code table .
|
Flight details (t_flight)
|
flt_grp
|
Flight group number must be a valid number as listed in the
t_flight_group table.
|
|
flt_num
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 1 - 25.
|
|
|
Multiple column check on flt_grp and flt_num:
The combination of flt_grp and flt_num must be unique.
|
|
flt_indx
|
Must be an integer greater than zero, and be a concatenation
of flt_grp and flt_num.
|
|
up_field
|
Must be a valid code as listed in t_airfield_code.
|
|
up_time
|
Must be a valid 24 hour time and fall with in the range of 0 -
2359.
|
|
dn_field
|
Must be a valid code as listed in the t_airfield_code table.
|
|
dn_time
|
Must be a valid 24 hour time and fall with in the range of 0 -
2359.
|
|
|
Multiple column check on up_time and dn_time:
dn_time must be greater than up_time.
|
|
hr_min
|
Flight duration in hours and minutes must be a number of 2
decimal places where the decimal can not exceed 0.59.
|
|
|
Multiple column check on up_time and dn_time and hr_min:
The hours and minutes represented by hr_min must be equal to
dn_time - up_time.
|
|
hr_dec
|
Multiple column check on up_time and dn_time and hr_dec:
hr_dec must equal dn_time - up_time in decimal hours.
|
Sightings details (t_school_sight)
f
|
lt_grp
|
Flight group number must have a value and be a valid number as
listed in the t_flight_group table.
|
|
flt_num
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 1 - 25.
|
|
flt_indx
|
Must be an integer greater than zero, be a concatenation of
flt_grp and flt_num and be a valid flt_indx number in table
t_flight.
|
|
sight_num
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 1 - 32.
|
|
|
Multiple column check on flt_grp, flt_num, and sight_num:
The combination of flt_grp, flt_num and sight_num must be
unique.
|
|
sgt_indx
|
Must be an integer greater than zero.
|
|
|
Multiple column check on flt_grp, flt_indx, and sight_num:
sgt_indx must be a conactenation of flt_grp, flt_indx, and
sight_num.
|
|
species_code
|
Must be an integer and be a valid code as listed in
t_species_code.
|
|
num_of_schools
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 0 - 1000.
|
|
ton_min
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 0 - 1000.
|
|
ton_max
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 1 - 2500.
|
|
|
Multiple column check on ton_min and ton_max: ton_min must be
less than or equal to ton_max.
|
|
ton_tot_pil
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 1 - 15000.
|
|
ton_tot_calc
|
Must be an integer greater than or equal to zero and should be
within the reasonable range of 1 - 72000.
|
|
sighting_time
|
Must be a valid 24 hour time in the range 0 - 2359.
|
|
time_after_takeoff
|
Must be a number greater than or equal to zero and should be
within the reasonable range of 0.01 - 10.
|
|
sea_cond_code
|
Must be an integer and a valid code in the range 1 - 5 as
listed in the t_sea_cond_code table.
|
|
water_temp
|
Must be a number, and should be in the range 8 - 30.
|
|
location
|
Must be a valid code as listed in the t_grid_code table.
|
|
Latitude
|
Must be an integer which represents a valid latitude in the
range 30 - 49 degrees. Should be within the range of 33 - 43
degrees.
|
|
Longitude
|
Must be an integer which represents a valid longitude in the
range 170 - 180 degrees. Should be within the range of 172 to
180.
|
|
long_ew
|
Must be equal to either an 'E' or a 'W'.
|
Set details (t_set)
|
flt_grp
|
Flight group number must have a value and be a valid number as
listed in the t_flight_group table.
|
|
sighting_time
|
Must be a valid 24 hour time in the range 0 - 2359.
|
|
set_time
|
Must be a valid 24 hour time in the range 0 - 2359.
|
|
vessel_code
|
Must be a valid code in the t_vessel_code table.
|
|
Rst
|
Must be an integer in the range 0 - 7.
|
|
sp_sighting
|
Must be a valid integer code as listed in the t_species_code
table.
|
|
est_ton_pilot
|
Must be an integer greater than zero and should be within the
reasonable range of 1 - 300.
|
|
est_sp_pilot
|
Must be a valid integer code as listed in the t_species_code
table.
|
|
land_ton
|
Must be an integer greater than zero and should be within the
reasonable range of 1 - 300.
|
|
land_sp
|
Must be a valid integer code as listed in the t_species_code
table.
|
flightpath details (t_flightpath)
f
|
lt_grp
|
Flight group number must be a valid number as listed in the
t_flight_group table.
|
|
Grid
|
Must have a value and be a valid integer code as listed in the
t_grid_code table.
|
|
Ticks
|
Must be an integer or equal to and should be in the reasonable
range of 0 -
|
|
tic_factor
|
Must be an integer greater than or equal
to zero and should be in the reasonable range of 0 - 50.
|
Pilot codes (t_pilot_code)
|
code
|
Must have a value, be unique and be an
integer greater than or equal to zero.
|
|
Pilot
|
Pilot name must have a value.
|
|
pil_start
|
Must be an integer greater than zero.
|
Customer codes (t_customer_code)
|
code
|
Must have a value, be unique and be an integer greater than or
equal to zero.
|
|
Customer
|
Customer name must have a value.
|
Aircraft codes (t_aircraft_code)
|
code
|
Must have a value, be unique and be a integer greater than or
equal to zero.
|
|
aircraft_callsign
|
Must be a multiple of 3 uppercase characters.
|
Vessel codes (t_vessel_code)
|
code
|
Must have a value, be unique and be a integer greater than or
equal to zero.
|
|
vessel_name
|
Must have a value.
|
|
num_vess
|
Must be an integer greater than or equal to zero, and should
be within the reasonable range of 1 -10.
|
Airfield codes (t_airfield_code)
|
code
|
Must have a value, be unique and be a integer greater than or
equal to zero.
|
Species codes (t_species_code)
|
code
|
Must have a value, be unique and be an integer greater than or
equal to zero.
|
|
kah_mix
|
Must be an integer and should have a value of 0 or 1.
|
|
jma_mix
|
Must be an integer and should have a value of 0 or 1.
|
|
ema_mix
|
Must be an integer and should have a value of 0 or 1.
|
|
skj_mix
|
Must be an integer and should have a value of 0 or 1.
|
|
tre_mix
|
Must be an integer and should have a value of 0 or 1.
|
|
bma_mix
|
Must be an integer and should have a value of 0 or 1.
|
|
jma_grp
|
Must be an integer and should have a value of 0 or 1.
|
Sea conditions codes (t_sea_cond_code)
|
code
|
Must have a value, be unique and be an integer in the range 1
- 4.
|
|
old_sea_cond
|
Must have a value (of type character).
|
|
old_to_new
|
Must be an integer and should be in the range 1 - 3.
|
Grid codes (t_grid_code)
|
code
|
Must be an integer, have a value, be unique and should be in
the range 0 - 445.
|
|
qmaarea
|
Must be an integer greater than zero and should be in the
range 1 - 10.
|
|
local_area
|
Must be an integer and should be in the range 1 - 6.
|
|
tod1
|
Must be an integer and should be in the range 1 - 4.
|
|
kah_ma
|
Must be an integer and should be in the range 1 - 9.
|
|
jma_ma
|
Must be an integer and should be in the range 1 - 9.
|
|
tre_ma
|
Must be an integer and should be in the range 1 - 9.
|
|
fishery
|
Must be an integer and should be in the range 1 - 2.
|
|
lat_deg
|
Must be an integer and should be in the range 33 - 42.
|
|
lat_min
|
Must be an integer and be in the range 0 - 59.
|
|
long_deg
|
Must be an integer and should be in the range 172 - 180.
|
|
long_min
|
Must be an integer and be in the range 0 - 59.
|
|
long_EW
|
Must be equal to either an 'E' or a 'W'.
|
|
rolgrnd
|
Must be an integer and should be either null or equal to 1.
|
|
overlandsq
|
Must be an integer and should be either null or equal to 1.
|
Grid descriptions (t_grid_description)
|
code
|
Must have a value, be unique and be a integer in the range 0 -
999.
|
Flight days time details (t_flt_days)
|
flt_grp
|
Flight group number must be a valid number as listed in the
t_flight_group table.
|
|
Date
|
Must have a value and be a valid date.
|
|
jul_day
|
Must be a valid Julian date.
|
|
dy_of_yr
|
Must have a value and be an integer in the range 1 - 366.
|
|
dy
|
Must be an integer and be in the range 1 - 31.
|
|
wk
|
Must be an integer and be in the range 1 - 53.
|
|
yeke
|
Must be an integer greater than or equal to 197625 and be a
combination of a valid 4 digit year and a valid week of year.
|
|
Mnth
|
Must be an integer in the range 1 - 12.
|
|
yeth
|
Must be an integer greater than or equal to 197606 and must be
a combination of a valid 4 digit year and a valid month of year.
|
|
Yr
|
Must be an integer greater than zero and should be in the
range 1976 to the current year.
|
Date and time data (t_time)
|
date
|
Must have a value and be a valid date.
|
|
dy_of_yr
|
Must have a value and be an integer in the range 1 - 366.
|
|
jul_day
|
Must be a valid Julian date.
|
|
Dy
|
Must be an integer and be in the range 1 - 31.
|
|
wk
|
Must be an integer and be in the range 1 - 53.
|
|
yeke
|
Must be an integer greater than or equal to 197625 and be a
combination of a valid 4 digit year and a valid week of year.
|
|
Mnth
|
Must be an integer in the range 1 - 12.
|
|
yeth
|
Must be an integer greater than or equal to 197606 and must be
a combination of a valid 4 digit year and a valid month of year.
|
|
Yr
|
Must be an integer greater than zero and should be in the
range 1976 to the current year.
|
|
Fishyr
|
Must be an integer greater than zero where the first 4 digits
represent the start calendar year of the fishing year followed by
the last 2 digits of the next calendar year.
|
|
Fishmnth
|
Must be an integer in the range 1 - 12.
|
|
qter
|
Must be an integer in the range 1 - 4.
|
Southern Oscillation Index data (t_soi)
|
yr
|
Must be an integer, have a value, and be in the range 1967 to
the current year.
|
|
Mnth
|
Must be an integer in the range 1 - 12.
|
|
month1
|
Must be a 3 character lowercase code for a month as listed
below: 'jan, feb, mar, apr, may, jun, jul, aug, sep, oct, nov,
dec'
|
Astronomical data (t_astro_gen)
|
jul_day
|
Must be a valid Julian date.
|
|
Yr
|
Must be an integer greater than zero and should be in the
range 1976 to the current year.
|
|
Mnth
|
Must be an integer in the range 1 - 12.
|
|
dy
|
Must be an integer and be in the range 1 - 31.
|
|
date
|
Must be a valid date.
|
|
Sr0, sr12, ss0, ss12, mr, ms, durn
|
Must be a number in the range 0 - 23.999.
|
|
durn
|
Must be a number and should be in the range 0 - 13.9.
|
|
ph
|
Moon phase must be a number in the range 0 - 1.0.
|
|
inten
|
Must be a number and should be in the range 0 - 1.0.
|
|
semi
|
Must be a number in the range 0.490 - 0.559.
|
|
decl
|
Must be a number in the range -28.68 - 28.68.
|
Moon cycle data (t_astro_moon)
|
yr
|
Must have a value, be an integer greater than zero and should
be in the range 1979 to the current year.
|
|
dy_of_yr
|
Must have a value and be an integer in the range 1 - 366.
|
|
days_since_epoch
|
Must have a value and be an integer greater than or equal to
zero.
|
|
Cycle
|
Must have a value and be an integer greater than or equal to
zero.
|
|
pcent_cycle
|
Must have a value and be in the range 0 - 0.99.
|
|
quarter
|
Must be an integer in the range 1 - 4.
|
Sun cycle data (t_astro_sun)
|
mnth
|
Must have a value and be an integer in the range 1 - 12.
|
|
dy
|
Must have a value and be an integer in the range 1 - 31.
|
|
up_hr
|
Must be an integer greater than zero and should be in the
range 3 - 9.
|
|
up_min
|
Must be an integer and be in the range 0 - 59.
|
|
up_dec
|
Must be a positive number and should be in the range 4.0 -
8.9.
|
|
merid_hr
|
Must be an integer greater than zero and should be in the
range 10 - 14.
|
|
merid_min
|
Must be an integer and be in the range 0 - 59.
|
|
mer_alt_deg
|
Must be an integer and should be in the range 18 - 76.
|
|
mer_alt_min
|
Must be an integer and be in the range 0 - 59.
|
|
dn_hr
|
Must be and integer greater than zero and should be in the
range 15 - 21.
|
|
dn_min
|
Must be an integer and be in the range 0 - 59.
|
|
dn_dec
|
Must be a positive number and should be in the range 16.0 -
20.9.
|
|
azimuth_deg
|
Must be an integer greater than zero and should be in the
range 54 - 128.
|
|
area
|
Must have a value, be an integer and should be in the range of
1- 4.
|
|
dk_hrs
|
Must be a positive number and should be in the range 8.0 -
15.9.
|
|