|
|
5 ikiTables
The following are listings of the tables in the iki database,
including attribute names, data types (and any restrictions), and
comments.
5.1 Table 1: t_trip
Comment: Details about a longline fishing trip.
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
trip_no
|
character(6,1)
|
No
|
Trip number. A unique sequential integer, which maybe prefixed
by a 4- character area code for regionalspecific sampling
programmes.
|
|
Vessel
|
character(30,1)
|
|
Vessel name.
|
|
reg_no
|
longinteger
|
|
Registration number.
|
|
date_s
|
date(4)
|
|
Trip start date.
|
|
date_f
|
date(4)
|
|
Trip end date.
|
|
master
|
character(40,1)
|
|
Master's name.
|
|
LFR
|
character(40,1)
|
|
Licensed Fish Receiver: i.e., company name of for whom the
vessel fished for.
|
|
Mfish_no
|
character(25,1)
|
|
Number of matching Mfish TCEPR/CELR form.
|
|
bb_type
|
character(4,1)
|
|
Longline backbone type.
|
|
bb_break i
|
nteger
|
|
Longline backbone breaking strain.
|
|
snood_break
|
integer
|
|
Snood line breaking strain.
|
|
clip_type
|
character(4,1)
|
|
Clip type.
|
|
hook_type
|
character(3,1)
|
|
Hook type default.
|
|
hook_size
|
smallint
|
|
Hook size default.
|
|
snood_lgth
|
decimal(4,1)
|
|
Snood length default (m).
|
|
snood_dist
|
decimal(4,1)
|
|
Snood distance default (m).
|
|
gear_meth
|
character(2,1)
|
|
Default gear method code. Refer rdb:meth_codes.
|
|
no_effort
|
integer
|
|
Total number of effort during the trip (e.g., sets, trawls,
seines).
|
|
target_sp
|
character(12,1)
|
|
Comma-separated list of target species codes. For individual
species codes, refer rdb:curr_spp.
|
|
|
Smatch "[A-Z,]"
|
|
|
|
staff_1
|
character(30,1)
|
|
Staff names.
|
|
Staff_2
|
character(30,1)
|
|
Staff names 2.
|
|
comments
|
text(20,20,20,1)
|
|
Comments.
|
|
project_code c
|
haracter(6,1)
|
|
Project code.
|
|
Creator:
|
sma
|
|
|
|
Referential:
|
Invalid gear method code (gear_meth) INSERT rdb : meth_codes
(code)
|
|
Indices:
|
UNIQUE t_trip_PK ON (trip_no)
|
5.2 Table 2: t_trip_catch
Comment: Details of the total catch by species for the
trip.
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
trip_no
|
character(6,1)
|
No
|
Unique code identifying each trip. Refer t_trip.
|
|
species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp.
|
|
weight
|
decimal(6,1)
|
|
Landed weight (kg) of species for the entire trip.
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
No such trip number (trip_no) INSERT t_trip (trip_no) Invalid
species code (species) INSERT rdb : curr_spp (code)
|
|
Indices:
|
UNIQUE BTREE t_trip_catch_pk ON (trip_no, species)
|
5.3 Table 3: t_effort
Comment: Details about an individual unit of effort; e.g.,
one longline set, trawl, or danish seine.
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
trip_no
|
character(6,1)
|
No
|
Unique code identifying each trip.Refer t_trip.
|
|
effort_no
|
integer
|
No
|
Sequential number for each unit of effort deployed (e.g., set,
trawl, seine) deployed during a trip.
|
|
stat_area
|
character(3,1)
|
|
3-character Statistical area code. Refer rdb:area_codes.
|
|
date_s
|
date(4)
|
|
Start date of the unit of effort.
|
|
time_s
|
integer
|
|
Start time (24 hour, NZDT) of the unit of effort.
|
|
fix_s
|
character(2,1)
|
|
2-character code for the method of fixing the position at
start of the unit of effort. Refer rdb:t_fix_meth_codes.
|
|
lat_s
|
decimal(6,1)
|
|
Latitude at start of the unit of effort in DDMM.m format. For
example, 43 degrees 34.5 minutes is stored as 3434.5
|
|
long_s
|
decimal(7,1)
|
|
Longitude at start of the unit of effort in DDDMM.m format.
For example, 174 degrees 58.6 minutes is stored as 17458.6
|
|
EW_s
|
character(1,1)
|
|
East or West meridian at the start of the unit of effort.
|
|
|
Smatch "[EW]"
|
|
|
|
gear_s
|
integer
|
|
Gear depth (m) at the start of the unit of effort.
|
|
depth_s
|
integer
|
|
Seabed depth (m) at the start of the unit of effort.
|
|
date_f
|
date(4)
|
|
End date of the unit of effort.
|
|
time_f
|
integer
|
|
End time (24 hour, NZDT) of the unit of effort.
|
|
fix_f
|
character(2,1)
|
|
2-character code for the method of fixing the position at
finish of the unit of effort. Refer rdb:t_fix_meth_codes.
|
|
lat_f
|
decimal(6,1)
|
|
Latitude at end of the unit of effort in DDMM.m format. For
example, 43 degrees 34.5 minutes is stored as 3434.5
|
|
long_f
|
decimal(7,1)
|
|
Longitude at end of the unit of effort in DDDMM.m format. For
example, 174 degrees 58.6 minutes is stored as 17458.6
|
|
EW_f
|
character(1,1)
|
|
East or West meridian at the finish of the unit of effort.
|
|
|
Smatch "[EW]"
|
|
|
|
gear_f
|
integer
|
|
Gear depth (m) at the finish of the unit of effort.
|
|
depth_f
|
integer
|
|
Seabed depth (m) at finish of the unit of effort.
|
|
min_gear
|
integer
|
|
Minimum depth of fishing gear (m).
|
|
max_gear
|
integer
|
|
Maximum depth of fishing gear (m).
|
|
bb_length
|
decimal(4,1)
|
|
Total length of the longline backbone (nautical miles)
|
|
hooks
|
integer
|
|
Total number of hooks on the longline.
|
|
hook_type
|
character(3,1)
|
|
Hook type code.
|
|
hook_size
|
integer
|
|
Hook size.
|
|
snood_lgth
|
decimal(4,1)
|
|
Snood length (m).
|
|
snood_dist
|
decimal(4,1)
|
|
Snood distance (m).
|
|
gear_meth
|
character(2,1)
|
|
Gear method code. Refer to rdb:meth_codes.
|
|
bait_s1
|
character(3,1)
|
|
Bait species code 1. Refer rdb:curr_spp.
|
|
Bait1
|
smallint
|
|
Bait type 1, percentage of bait used range 0.00 to 100.00
|
|
bait_s2
|
character(3,1)
|
|
Bait species code 2. Refer rdb:curr_spp.
|
|
Bait2
|
smallint
|
|
Bait type 2, percentage of bait used range 0.00 to 100.00
|
|
bait_s3
|
character(3,1)
|
|
Bait species code 3. Refer rdb:curr_spp.
|
|
Bait3
|
smallint
|
|
Bait type 3, percentage of bait used range 0.00 to 100.00
|
|
bait_s4
|
character(3,1)
|
|
Bait species code 4. Refer rdb:curr_spp.
|
|
bait4
|
smallint
|
|
Bait type 4, percentage of bait used range 0.00 to 100.00
|
|
wind_dir
|
integer
|
|
Wind direction (degrees true).
|
|
wind_knots
|
integer
|
|
Wind speed (knots).
|
|
Cloud
|
smallint
|
|
Cloud cover code (eighths).
|
|
|
range 0 to 8
|
|
|
|
sea_cond
|
smallint
|
|
Sea condition code (Beaufort scale).
|
|
sea_col
|
smallint
|
|
Sea colour code.
|
|
swell_ht
|
smallint
|
|
Swell height code.
|
|
swell_dir
|
integer
|
|
Swell direction (degrees true).
|
|
bot_type
|
smallint
|
|
Bottom type code.
|
|
bot_cont
|
smallint
|
|
Bottom contour code.
|
|
Comments
|
text(20,20,20,1)
|
|
Comments
|
|
Creator:
|
sma
|
|
|
|
Referential:
|
No such trip number (trip_no) INSERT t_trip (trip_no)
Invalid bait_s1 code (bait_s1) INSERT rdb : curr_spp (code)
Invalid bait_s2 code (bait_s2) INSERT rdb : curr_spp (code)
Invalid bait_s3 code (bait_s3) INSERT rdb : curr_spp (code)
Invalid bait_s4 code (bait_s4) INSERT rdb : curr_spp (code)
Invalid fix_s code (fix_s) INSERT rdb.: t_fix_meth_codes
(fix_meth_code)
|
|
|
Invalid fix_f code (fix_f) INSERT rdb : t_fix_meth_codes
(fix_meth_code)
|
|
|
Invalid gear method code (gear_meth) INSERT rdb : meth_codes
(code)
|
|
Indices:
|
NORMAL (2, 15) datesindex ON (date_s)
NORMAL (2, 15) datefindex ON (date_f)
NORMAL (2, 15) gearsindex ON (gear_s)
NORMAL (2, 15) gearfindex ON (gear_f)
NORMAL (2, 15) depthsindex ON (depth_s)
NORMAL (2, 15) depthfindex ON (depth_f)
NORMAL (2, 15) mingearindex ON (min_gear)
NORMAL (2, 15) maxgearindex ON (max_gear)
UNIQUE t_set_PK ON (trip_no, effort_no)
|
5.4 Table 4: t_page
|
Comment:
|
Header information from a page of fish measurements. Used when
fish detail data for a unit of effort is recorded on more than
one page. Default page number is 1.
|
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
trip_no
|
character(6,1)
|
No
|
Unique code identifying each trip. Refer t_trip.
|
|
effort_no
|
integer
|
No
|
Sequential number for each unit of effort deployed (e.g., set,
trawl, seine) deployed during a trip.
|
|
Species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp.
|
|
page_no
|
smallint
|
No
|
Page number of the recorded fish detail data for one unit of
effort in a trip. Default page number is 1.
|
|
page_s
|
integer
|
|
Start time of recording the page.
|
|
page_f
|
integer
|
|
End time of recording the page.
|
|
Creator:
|
sma
|
|
|
|
Referential:
|
No such trip and effort number (trip_no, effort_no) INSERT
t_effort (trip_no, effort_no)
Invalid species code (species) INSERT rdb : curr_spp (code)
|
|
Indices:
|
UNIQUE t_page_PK ON (trip_no, effort_no, page_no)
|
5.5 Table 5: t_fish
Comment: Individual fish measurements.
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
trip_no
|
character(6,1)
|
No
|
Unique code identifying each trip. Refer t_trip.
|
|
effort_no
|
integer
|
No
|
Sequential number for each unit of effort deployed (e.g., set,
trawl, seine) deployed during a trip.
|
|
Species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp.
|
|
page_no
|
smallint
|
No
|
Page number of fish characterisation for the set.
|
|
lgth
|
integer
|
|
Length (cm) of fish.
|
|
sex
|
smallint
|
|
Sex code for the fish (sex not taken for iki fishery).
|
|
alive
|
character(1,1)
|
|
Alive on landing status code: Y=yes, N=no, M=moribund.
|
|
|
Smatch "[MNY]"
|
|
|
|
bait
|
character(3,1)
|
|
3-character bait type code. Refer rdb:curr_spp.
|
|
hook_p
|
character(1,1)
|
|
Hook position code: L=lip, G=Gut, F=Foul.
|
|
|
Smatch "[FGL]"
|
|
|
|
wound
|
smallint
|
|
Other wounding: 1=none, 2=gear, 3=predator, 4=bleeding or
lesions, source unknown.
|
|
|
Range 1 to 4
|
|
|
|
rupture
|
character(1,1)
|
|
External rupture flag: Y=Yes, N=No.
|
|
|
Smatch "[YN]"
|
|
|
|
state
|
smallint
|
|
Baratrauma state code: 1=not distended 2=air in body, 3=gut
protrusion, 4=extreme.
|
|
|
Range 1 to 4
|
|
|
|
Creator:
|
sma
|
|
|
|
Referential:
|
No such trip, effort and page number (trip_no, effort_no,
page_no) INSERT t_page (trip_no, effort_no, page_no)
Invalid species code (species) INSERT rdb : curr_spp (code)
|
|
Indices:
|
NORMAL (2, 15) fishindex ON (trip_no, set_no, page_no)
NORMAL (2, 15) BTREE fishsppindex ON (species)
|
5.6 Table 6: t_catch
Comment: Details of catches of species for an unit of
effort.
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
trip_no
|
character(6,1)
|
No
|
Unique code identifying each trip. Refer t_trip.
|
|
effort_no
|
integer
|
No
|
Sequential number for each unit of effort deployed (e.g., set,
trawl, seine) deployed during a trip.
|
|
Species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp
|
|
grade
|
character(3,1)
|
No
|
3-character fish quality grade code: IKI=Iki and/or slurry
GRE=Green or whole ungraded fish.
|
|
no_bins
|
decimal(5,2)
|
|
Number of bins of fish for the species.
|
|
bin_wgt
|
decimal(6,1)
|
|
Average weight (kg) of a bin of the species.
|
|
no_sampled
|
decimal(5,2)
|
|
Number of bins of fish for the species that were sampled
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
No such effort number (trip_no, effort_no) INSERT t_effort
(trip_no, effort_no)
|
|
|
No such species code (species) INSERT rdb : curr_spp (code)
|
5.7 Table 7: t_grade_code
|
Comment:
|
Lists the codes and their descriptions for quality grades of
fish.
Used primarily for the snapper iki fishery where fish are
grade on quality upon catching.
|
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
grade
|
character(3,1)
|
No
|
3-character fish quality grade code.
|
|
description
|
character(30,1)
|
No
|
Description of grade code.
|
|
Creator:
|
dba
|
|
|
|
Indices:
|
UNIQUE BTREE t_grade_code_pk ON (grade)
|
5.8 Table 8: t_hook_shot
Comment: Details about the shots of longline sets used
during the SNA9802 hook appendage experiment.
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
shot_no
|
integer
|
No
|
Sequential number for each longline set shot during a trip.
|
|
project_code
|
character(10,1)
|
|
Project code.
|
|
date_s
|
date(5)
|
|
Date that the longline set was shot.
|
|
Vessel
|
character(30,1)
|
|
Vessel name.
|
|
bb_type
|
character(4,1)
|
|
Longline backbone type code.
|
|
bb_break
|
integer
|
|
Longline backbone breaking strain (kg).
|
|
hook_type
|
character(3,1)
|
|
Hook type code.
|
|
snood_lgth
|
decimal(4,1)
|
|
Snood length (cm).
|
|
snood_dist
|
decimal(4,1)
|
|
Distance (m) between the snoods.
|
|
snood_break
|
integer
|
|
Breaking strain (kg) of the snoods.
|
|
Creator:
|
dba
|
|
|
|
Indices:
|
UNIQUE BTREE t_hook_shot_pk ON (shot_no)
|
5.8 Table 8: t_hook_append
|
Comment:
|
Details about experiments to determine the effects of longline
hook appendages on incidental mortality and catch rates of
snapper.
|
|
Attribute
|
Data Type
|
Null?
|
Comment
|
|
shot_no
|
integer
|
No
|
Sequential number for each longline set shot during a trip.
Refer t_hook_shot.
|
|
Block
|
smallint
|
No
|
Sequential number for each replicate block of 50 hooks.
|
|
Treat
|
smallint
|
No
|
Sequential number for each hook treatment.
|
|
Result
|
character(2,1)
|
|
Up to 2-character code for the result of the hook.
|
|
Species
|
character(3,1)
|
|
3-character species code. Refer rdb:curr_spp.
|
|
Lgth
|
integer
|
|
Length (cm) of the species caught.
|
|
Hooked
|
character(4,1)
|
|
Up to 4-character code for where the animal caught was hooked
and what condition it was in when boated.
|
|
Season
|
character(1,1)
|
|
1-character code for the season: S=summer, W=winter.
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
No such shot (shot_no) INSERT t_hook_shot (shot_no)
No such species code (species) INSERT rdb : curr_spp (code)
|
|
Indices:
|
NORMAL (2, 15) BTREE shotindex ON (shot_no)
|
|