|
|
5 rlcs Catch Sampling Tables
The following listings of the tables in the catch sampling schema of the rlcs database, including attribute names, data types (and any range restrictions), and comments.
5.1 Table 1: t_sample
Comment: This table contains the details of individual red rock lobster samples for a variety of sampling programs, including re rock lobster catch sampling, market sampling, tagging, MLS, packhorse rock lobster exploratory, and morphometric sampling.
| Attributes |
Data Type |
Null? |
Comment |
| |
|
|
|
| sample_no |
character(10,1) |
No |
Unique sample no: 3 character sampleno., 2 digit year, 5chardatasource (since 1 Jan 98). |
| date_s |
date(4) |
|
Date sampled. |
| species |
character(3,1) |
No |
3 char species code. Only three valid codes: Red Rock Lobster CRA,Packhorse rock lobster PHC and mixed RLM. |
| |
smatch 'CRA|PHC|RLM' |
|
|
| CFA |
character(3,1) |
|
Controlled Fishing Areas. Only used during MLS samples (seet_mls_sample). |
| area |
integer |
|
Rock lobster statistical areas 901-943, followed by subarea used by fishers in Voluntary Logbook Program. Note area 900 is used where STATISTICAL area is undetermined,e.g., factory samples for length/weight data. |
| |
range 900 i 943 i |
|
|
| vessel_no |
longinteger |
|
Registration number of the vessel from which sampling was carried outon. |
| calipers |
decimal(2,1) |
|
Distance (decimal mm) that has beenadded to every tail widthforthesample. |
| recorder |
character(12,1) |
|
Name of the recorder. |
| sampler |
character(12,1) |
|
Name of the sampler. |
| wind1_dir |
character(3,1) |
|
Prevalent wind direction during the soak time of the pots. |
| |
match '[NSEW]{[NSEW]}{[NESW]}'
match 'VAR' |
| beau1_scale |
shortinteger |
Average Beaufort scale wind strengthduring the soak time of the pots. |
| |
range 0 i 12 i |
|
|
| |
|
|
|
| wind2_dir |
character(3,1) |
Direction to which the prevailing wind changed to during the soak timeof the pot. |
| |
match '[NSEW]{[NSEW]}{[NESW]}'
match 'VAR' |
|
| beau2_scale |
shortinteger |
Beaufort scale which the prevailingwind changed to (if it changed). |
| |
|
| |
range 0 i 12 i |
|
|
| swell1_dir |
character(3,1) |
Directionof the prevailing swellover the soak time of the pots. |
| |
|
| |
match '[NSEW]{[NSEW]}{[NESW]}' |
|
| |
|
|
|
| swell1_code |
shortinteger |
1 digit code for swell height:
1=<2m; 2=2-4m; 3=>4m |
| |
|
| |
range 1 i 3 i |
|
|
| swell2_dir |
character(3,1) |
Direction to which the prevailing swell has changed to during the soak time of the pots. |
| |
|
| |
|
| |
match '[NSEW]{[NSEW]}{[NESW]}' |
|
| swell2_code |
shortinteger |
1 digit code for swell height:
1=<2m; 2=2-4m; 3=>4m |
| |
|
| |
range 1 i 3 i |
|
|
| method |
character(1,1) |
1 char fishing method code:
1=potting; 2=diving |
| |
match ‘[12]’ |
|
|
| GPS |
character(1,1) |
1 char GPS code:
0=no GPS or presentbut not used;
1=GPS used |
| |
match ‘[01]’ |
|
|
| vessel |
character(18,1) |
|
Vessel name. |
| skipper |
character(20,1) |
|
Skipper’s name. |
| divers |
character(29,1) |
|
Divers names. |
| remarks |
character(35,1) |
|
Comments about the sample. |
| season |
smallint |
|
1 digit code to say whether thesample took place during an open orclosed season: 1=openseason;0=closed season |
| |
range 0 i 1 i |
|
|
| keepers |
integer |
|
Actual number of rock lobsterslanded to an LFR. |
| kg_est |
integer |
|
Estimate landed catch weight (kg). |
| kg_act |
decimal(4,1) |
|
Actual landed catch weight (kg). |
| sample_type |
character(2,1) |
No |
Flag to denote which program(s) |
| |
|
|
The sample is from: CS=catch |
| |
|
|
sampling; TG=tagging only; CT=catch |
| |
|
|
sampling and tagging; |
| |
|
|
WS=market/morphometric sample; |
| |
|
|
PH=packhorse exploratory; LS=MLS |
| |
|
|
sampling. |
| |
smatch ‘CS|CT|LS|PH|TG|WS’ |
| |
|
|
|
| Creator: |
dba |
|
|
| Indices: |
UNIQUE sample_pk ON (sample_no) |
| |
NORMAL (2, 15) sample_sample_type_ndx ON (sample_type) |
| |
NORMAL (2, 15) sample_area_ndx ON (area) | 5.2 Table 2: t_effort Comment: Details of each pot (or each set of pots) used in a catch sample, including pot size, materials used in pot construction, depth, soak time, and bait used.
| Attributes |
Data Type |
Null? |
Comment |
| |
|
|
|
| sample_no |
character(10,1) |
No |
Unique sample no: 3 character sampleno., 2 digit year, 5 chardatasource (since 1 Jan 98). |
| season |
smallint |
|
1 digit code to say whether the sample took place during an open or closed season:
1=openseason; 0=closed season |
| |
range 0 i 1 i |
| effort_no |
integer |
No |
A sequential number given to each effort (a pot or a set of pots) lifted during a sample. |
| num_of_effort |
integer |
|
Number of pots used in the set(effort). |
| pot_type |
character(2,1) |
No |
2 char code for pot type: first denotes the materials used in construction; the second denotes mesh size. Refer to Rock Lobster Catch Sampling Manual or Appendix 2 of database documentation for codes. |
| |
smatch '[A-Z0-9][0-9]' |
| es_gaps |
smallint |
|
1 digit code for escape gaps in thepot: 0=escape gaps wired closed;1=normal escape gaps. |
| |
range 0 i 1 i |
| depth |
integer |
|
Depth in metres that the pot/set. |
| soak |
integer |
|
Soak time (hours). |
| bait |
integer |
|
Code for bait used. Refert_bait_codes |
| caught |
integer |
|
Number of live rock lobsters caught. |
| octopus |
integer |
|
1 digit code for octopus attacks on potted rock lobster: 0-8=no. of dead, limp lobsters, or empty shel las a result of octopus; 9=octopusonly |
| no_legal |
integer |
|
Number of legal rock lobsters. |
| legal_wt |
decimal(7,4) |
|
Weight (kg) of legal rock lobsters. |
| no_berried |
integer |
|
Number of berried female rock lobsters. |
| caught_sp2 |
integer |
|
Number of non-target rock lobsters caught e.g., no. of CRA caught when target species is PHC. |
| latitude |
decimal(5,1) |
|
Latitude (degrees & decimal minutes)of the pot/set (DDMM.m). |
| |
match '[3-6][0-9][0-5][0-9].[0-9]' |
| longitude |
decimal(6,1) |
|
Longitude (degrees & decimalminutes) of the pot/set (DDDMM.m). |
| |
|
|
| |
match '1[7-8][0-9][0-5][0-9].[0-9]'
= 18000.0 |
| EorW |
character(1,1) |
|
Meridian of pot/set: E=east; W=west |
| |
match '[EW]' |
|
|
| sample_type |
character(2,1) |
No |
Flag to denote which program(s)The sample is from: CS=catchsampling; PH=packhorse exploratory. |
| |
|
|
| |
|
|
| |
smatch ‘CS|PH’ |
| |
|
|
|
| Creator: |
dba |
|
|
| Referential: |
Invalid bait code (bait) INSERT t_bait_codes (bait_code) |
| |
cra pots loaded after cover (sample_no) INSERT |
| |
t_sample (sample_no) |
| Indices: |
UNIQUE effort_pk ON (sample_no, pot_no) |
| |
NORMAL (2, 15) effort_sample_type_ndx ON (sample_type) | 5.3 Table 3: t_crayfish Comment: Details of individual rock lobsters, such as sex, tail width, and weight.
| Attributes |
Data Type |
Null? |
Comment |
| |
|
|
|
| sample_no |
character(10,1) |
No |
Unique sample no: 3 character sample no., 2 digit year, 5 char data source (since 1 Jan 98). |
| season |
smallint |
|
1 digit code to say whether the sample took place during an open or closed season: 1=open season;0=closed season |
| |
range 0 i 1 i |
|
|
| species |
character(3,1) |
No |
3 char species code: CRA=red rock lobster; PHC=packhorse rock lobster. |
| |
smatch 'CRA|PHC' |
|
|
| effort_no |
integer |
|
A sequential number given to each effort (a pot or a set) lifted. Provides a link to t_effort. |
| sex |
smallint |
|
1 digit sex code: 1=male; 2-9=female of varying maturity; 0 =hermaphrodite. Refer to Rock Lobster Catch Sampling Manual or Appendix 2 of database documentation for codes. |
| |
range 0 i 7 i = 9 |
|
|
| carapace |
decimal(4,1) |
|
Carapace length (mm). |
| tail_width |
decimal(4,1) |
|
Width of tail (decimal mm) between the primary spines of the second abdominal segment. |
| twf1 |
decimal(4,1) |
|
Width of tail (decimal mm) across the first calcified bar. |
| injury |
smallint |
|
1 digit code of rock lobster injury: 0-8=number of missing appendages; 9=body or tail damage. |
| |
range 0 i 9 i |
|
|
| moult |
smallint |
|
1 digit code for moult stage: 1=hard shell; 0 = old shell;5 = soft shell. |
| |
match ‘[015]’ |
|
|
| resident |
smallint |
|
1 digit code to denote if lobster is resident (=0), or run (=1). |
| |
range 0 i 1 i |
|
|
| status |
smallint |
|
1 digit code for landing status:0 = not kept; 1 = kept for landing. |
| |
range 0 i 1 i |
|
|
| sample_type |
character(2,1) |
No |
Flag to denote which program(s)The sample is from: CS=catchsampling; WS=market/morphometricsampling; PH=packhorse exploratory;LS=MLS sampling. |
| |
smatch ‘CS|LS|PH|WS’ |
|
| |
|
|
|
| Creator: |
dba |
| Referential: |
cra lfs loaded after cover (sample_no) INSERT |
| |
t_sample (sample_no) |
| Indices: |
NORMAL (2, 15) crayfish_species_ndx ON (species) |
| |
NORMAL (2, 15) crayfish_sample_no_ndx ON (sample_no) |
| |
NORMAL (2, 15) crayfish_sample_type_ndx ON (sample_type) |
| |
NORMAL (2, 15) crayfish_sex_ndx ON (sex) | 5.4 Table 4: t_bait_codesComment: This table contains all valid bait codes.
| Attributes |
Data Type |
Null? |
Comment |
| |
|
|
|
| bait_code |
integer |
No |
3 digit numeric code for species andprocessed state of bait used. |
| description |
character(60,1) |
No |
Description of the bait code. |
| |
|
|
|
| Creator: |
sma |
|
|
| Indices: |
UNIQUE bait_codes_pk ON (bait_code) |
|
|