|
|
5 squ_ce Tables
The following are listings of the tables in the squ_cedatabase, including attribute names, data types (and any rangerestrictions), and comments.
5.1 Table 1: t_events
Comment: Locationsand times of events. Events include fishing, recording ofenvironmental information and daily processing. There may beseveral events recorded on one logbook form (E.g., there may bemore than 1 trawl shot per day, as well as one processing event andone environmental event.).
| Attributes |
DataType |
Null? |
Comment |
| event_key |
decimal(12,0) |
No |
Uniqueidentifier for an event. Each environmental, fishing and processingevent for any day has a different key. |
| data_ver |
smallint |
No |
Versionnumber of data for event; e.g., 1 for first version of data asreceived from MFish. Each time data |
|
|
|
are changedthe record containing the old data remains unchanged but a newrecord, containing updated data, is inserted with data_ver having avalue of one greater than data_ver for the record containing theold data. |
| form_type |
smallint |
|
Type of form-squid jigging or trawling. This attribute is used to distinguishforms of the same number but of different types. 1 -Trawling 2-Jigging forms from day 4 -Jigging forms from 19 -Jigging formsfrom Types are: 1988 to present 1978 to 1989 1989 to 1991 Notethough that there is an overlap of jigging forms. The significanceof these form types is the distinction between jigging andtrawling. |
| form_no |
decimal(12,0) |
No |
Number ofform on which the event was recorded. Provides a link between environmental, fishing and processing events for each day. If morethan one form is used in a day, a link will also have to be madeusing a date (i.e., start_time). |
| event_type |
character(3,1) |
|
Generic type of event – e.g., O for operational event such as fishing. |
| start_time |
time(0) |
No |
Date and time at the beginning of
event. For a fishing event involving a trawl shot, this is the date and time at the beginning of the shot and for a fishing event involving jigging, this is at 0100 hr as jigging is performed overnight, Format is YYYYMMDDhhmmss –
e.g., 2:53:05 pm on 2 August 1994 is
19940802145305. |
| |
range > 19701001000000 |
|
|
| fin_time |
time(0) |
|
Date and time at end of a fishing
event. Format is YYMMDDhhmmss – e.g,.
2:53:05 pm on 2 August 1994 is
19940802145305. |
| validity |
character(7,1) |
|
Status flag for the data.
C : Data have been checked and are
ready for sending to the main
database. Editing data will cause this flag to revert to an N.
E : Error found during checking. Data
will not be sent until errors have
been cleared and data re-checked.
N : Data not yet sent to the main
database. Editing may occur.
Z : A data entry person has flagged
this record as being in error, even
though the computer may not see it as
so. This will suspend processing on
this record until the flag is changed
to something else. |
| vess_key |
longinteger |
No |
Unique identification number for a
vessel. Refer t_vessels. |
| start_lat |
decimal(6,4) |
|
Latitude of starting position for a
trawl and of position at 1 am for
jigging. Format is DD.dddd (e.g.,
45.5000 for 45 degrees 30 minutes). |
| |
range 0.0000 to 90.0000 |
|
|
| start_long |
decimal(7,4) |
|
Longitude of starting position for a
trawl and of position at 1 am for
jigging. Format is DD.dddd (e.g.,
45.5000 for 45 degrees 30 minutes). |
| |
range 0.0000 to 180.0000 |
|
|
| start_EW |
character(1,1) |
|
Hemisphere of starting position for a
trawl and of position at 1 am for
jigging - E for East and W for West. |
| |
match "[EW]" |
|
|
| fin_lat |
decimal(6,4) |
|
Latitude of finishing position for a
trawl. Format is DD.dddd (e.g.,
45.5000 for 45 degrees 30 minutes). |
| |
range 0.0000 to 90.0000 |
|
|
| fin_long |
decimal(7,4) |
|
Longitude of finishing position for a
trawl. Format is DD.dddd (e.g.,
45.5000 for 45 degrees 30 minutes). |
| |
range 0.0000 to 180.0000 |
|
|
| fin_EW |
character(1,1) |
|
Hemisphere of finishing position for a
trawl and of position at 1 am for
jigging - E for East and W for West. |
| |
match "[EW]" |
|
|
| op_ev_type |
character(3,1) |
|
Type of operational event.
E=Environmental
F=Fishing
P=Processing |
| |
match "[EFP]" |
|
|
| loc_area |
character(7,1) |
|
Although still to be derived, this
will describe the type of legal zone
(e.g., EZ for within EEZ, 12 for
within 12-mile limit). |
| fma |
character(7,1) |
|
Fisheries Management Area, although
currently unavailable. |
| stat_area |
integer |
|
Fisheries Statistical Area, although
currently unavailable. |
| Creator: |
wjt |
| Indices: |
Environmental event (event_key) DELETE t_environ (event_key)
Fishing event (event_key) DELETE t_fish_evnts (event_key)
Processing event (event_key) DELETE t_proc_evnts (event_key)
Invalid vessel name (vess_key) INSERT t_vessels (vess_key) |
| Referential: |
NORMAL (2, 15) BTREE i_ev_vess ON (vess_key)
NORMAL (2, 15) BTREE i_evkey ON (event_key)
UNIQUE BTREE i_events ON (event_key, data_ver)
NORMAL (2, 15) BTREE i_form ON (form_no, form_type)
NORMAL (2, 15) BTREE i_evtype ON (event_key, op_ev_type) |
5.2 Table 2: t_fish_evnts
Comment: Effortand overall catch for each fishing event.
| Attributes |
Data Type |
Null? |
Comment |
| event_key |
decimal(12,0) |
No |
Unique identifier for an event. Each environmental, fishing and processing event for any day has a different key. |
| data_ver |
smallint |
No |
Version number of data for event; e.g., 1 for first version of data as received from MFish. Each time data are changed the record containing the old data remains unchanged but a new record, containing updated data, is inserted with data_ver having a value of one greater than data_ver for the record containing the old data. |
| shot_no |
smallint |
|
Sequential number of a trawl shot within a day of fishing. |
| meth_code |
character(3,1) |
|
Fishing method used. Refer rdb:meth_codes |
| tar_species |
character(3,1) |
|
Target species for a fishing event. For Arrow Squid this is ASQ prior to 1 Oct 1989, and SQU thereafter. |
| Duration |
decimal(3,1) |
|
Duration of fishing event -i.e., time of finish -e.g., 1.5 means 1 hr 30 minutes. Be aware that these times are not always entered on the forms, so there can be effort and catch, but no hours given. |
| event_wt |
decimal(7,1) |
|
Estimated total catch in kilograms of combined species for a fishing event. This does not apply to squid caught by jigging as they are graded into trays. It will apply where squid are caught by other methods. |
| validity |
character(7,1) |
|
Status flag for the data
C : Data have been checked and are ready for sending to the main database. Editing data will cause this flag to revert to an N.
E : Error found during checking. Data will not be sent until errors have been cleared and data re-checked.
N : Data not yet sent to the main database. Editing may occur. Z: A data entry person has flagged this record as being in error, even though the computer may not see it as so. This will suspend processing on this record until the flag is changed to something else. |
| |
match "[CENZ]" |
|
|
| bttm_depth |
longinteger |
|
Depth of bottom in meters for trawl and jigging positions. |
| eff_depth |
longinteger |
|
For trawling the depth of the trawl. For jigging, the depth of the deepest hook for the night"s fishing. The depth may vary during the night. Depth is in meters. |
| eff_height |
decimal(3,1) |
|
Headline height for a trawl shot in meters. range 0.0 to 85.0 |
| num_single |
smallint |
|
Number of jigging machines in use as per the logbook form. It refers to the maximum in use at any one time for the fishing event as the number can change. |
| eff_width |
decimal(4,1) |
|
Gear width for a trawl shot in metres. |
| trawl_speed |
decimal(3,1) |
|
Speed of trawling in knots. range 1.5 to 7.0 |
| num_double |
smallint |
|
Number of double reel machines in use. It refers to the maximum in use at any one time for a fishing event as the number can change. |
| Creator: |
wjt |
| Referential: |
Event key not valid (event_key) INSERT t_events (event_key)
Invalid method code (meth_code) INSERT rdb : meth_codes (meth_code)
Invalid target species (tar_species) INSERT rdb : curr_spp (code) |
| Indices: |
NORMAL (2, 15) BTREE i_fishkey ON (event_key)
UNIQUE BTREE i_fishing ON (event_key, data_ver)
NORMAL (2, 15) BTREE i_fmeth ON (event_key, meth_code)
NORMAL (2, 15) BTREE i_fishver ON (data_ver) |
5.3 Table 3: t_catches
Comment: Estimatedweights for each species caught. This table is used for trawlingonly.
| Attributes |
Data Type |
Null? |
Comment |
| event_key |
decimal(12,0) |
No |
Unique identifier for an event. Each environmental, fishing and processing event for any day has a different key |
| item_seq_no |
smallint |
No |
Sequential number for each catch species. This is used to as part of the primary key in order to allow for null values of species. |
| data_ver |
smallint |
No |
Version number of data for event; e.g., 1 for first version of data as received from MFish. Each time data are changed the record containing the old data remains unchanged but a new record, containing updated data, is inserted with data_ver having a value of one greater than data_ver for the record containing the old data. |
| spp |
character(3,1) |
|
Species code of species. Refer rdb:curr_spp. |
| catch_wt |
decimal(7,1) |
|
Weight of catch for each species in kg. For trawling, these are normally estimates. Only 5 species can be recorded on one single form. |
|
validity |
character(7,1) |
|
Status flag for the data.
C : Data have been checked and are ready for sending to the main database. Editing data will cause this flag to revert to an N.
E : Error found during checking. Data will not be sent until errors have been cleared and data re-checked.
N : Data not yet sent to the main database. Editing may occur. Z:A data entry person has flagged this record as being in error, even though the computer may not see it as so. This will suspend processing on this record until the flag is changed to something else. |
| |
match "[CENZ]" |
|
|
| Creator: |
wjt |
| Referential: |
Event key not valid (event_key)
INSERT t_events (event_key) Not a valid species (spp)
INSERT rdb : curr_spp (code) |
| Indices: |
NORMAL (2, 15) BTREE i_fishkey ON (event_key)
UNIQUE BTREE i_fishing ON (event_key, data_ver)
NORMAL (2, 15) BTREE i_fmeth ON (event_key, meth_code)
NORMAL (2, 15) BTREE i_fishver ON (data_ver) |
5.4 Table 4: t_proc_evnts
Comment: Dailyprocessing details of each species. Contains processing data andcalculated greenweight.
| Attributes |
Data Type |
Null? |
Comment |
| event_key |
decimal(12,0) |
|
No Unique identifier for an event. Each environmental, fishing and processing event for any day has a different key. |
| seq_no |
smallint |
No |
Sequential number to make a unique index for the table. For trawling each number corresponds to a different line on the daily processing summary. For jigging, there is a new number for each tray class. |
| data_ver |
smallint |
No |
Version number of data for event; e.g., 1 for first version of data as received from MFish. Each time data are changed the record containing the old data remains unchanged but a new record, containing updated data, is inserted with data_ver having a value of one greater than data_ver for the record containing the old data. |
| spp |
character(3,1) |
No |
3-character species code for species being processed. Refer rdb:curr_spp. |
| act_type |
character(3,1) |
|
Type of action -differentiates between tray tallies and weighing. Codes are: ECA -Estimated Catch (No Processed available) GRE -Greenweight PRO -Processed PRT -Processed Total SIN -Single Fish TTL -Tray Tally (number of squid per tray) TTT -Tray Total (total number of trays) |
| |
match "[ECA|GRE|PRO|PRT|STN|TTL|TTT]" |
| proc_state |
character(4,1) |
No |
4-character processing state of species being processed. Refer t_proc_state. |
| unit_type |
character(3,1) |
|
Type of unit being counted. For trawling this will be a tray and for jigging theclass of tray (i.e., the range of numbers of squid per tray). Codesare: TRA -Tray T0 -Tray containing 1-10 T1 -Tray containing 11-20T2 -Tray containing 21-30 T3 -Tray containing 31-40 T4 -Traycontaining 41-50 T5 -Tray containing 51-60 T6 -Tray containing61-70 T7 -Tray containing 71-80 T8 -Tray containing 81-90 T9 -Traycontaining 91-100 T10 -Tray containing 101-150 T15 -Tray containing>150 |
| |
match "T[0-9]|T[R,1][A,0,5]" |
| unit_num |
integer |
|
Number of units of unit_type. |
| unit_wt |
decimal(5,2) |
|
Weight per unit. |
| conv_fact |
decimal(5,3) |
|
Conversion factor. |
| green_wt |
decimal(6,1) |
|
Green weight of species in kilograms. |
| green_wt_type |
character(3,1) |
|
Way in which green weight was determined. Codes are: ACT -Actual BAC -Back Calculated FBA -Fisher Back Calculated BAC is calculated by system software after logbook forms have been punched. The fisher on board a vessel calculates FBA green weight. |
| |
match "[ACT|BAC|FBA]" |
| proc_wt |
decimal(6,1) |
|
This is the final processed weight for a specific processed state of fish; e.g., hapuku as HGU weight, or as FIL weight. |
| proc_wt_type |
character(3,1) |
|
Way in which processed weight was determined. Codes are: ACT -Actual EST -Estimated BAC -Back Calculated |
| |
match "[ACT|BAC|EST]" |
| validity |
character(7,1) |
|
Status flag for the data.
C : Data have been checked and are ready for sending to the main database. Editing data will cause this flag to revert to an N.
E : Error found during checking. Data will not be sent until errors have been cleared and data re-checked.
N : Data not yet sent to the main database. Editing may occur. Z: A data entry person has flagged this record as being in error, even though the computer may not see it as so. This will suspend processing on this record until the flag is changed to something else. |
| |
match "[CENZ]" |
| stock_code |
character(7,1) |
|
Code describing fish stock -i.e., area and species code. However, this is currently unavailable. |
| Creator: |
wjt |
| Referential: |
Invalid processing eventkey (event_key) INSERT t_events (event_key)
Invalid species (spp) INSERT rdb : curr_spp (code)
No proc_state code (proc_state) INSERT t_proc_state (proc_state) |
| Indices: |
NORMAL (2, 15) BTREE i_proc_event ON (event_key)
NORMAL (2, 15) BTREE i_proc_spp ON (spp)
UNIQUE BTREE i_proc ON (event_key, seq_no, data_ver) |
5.5 Table 5: t_proc_state
Comment: Tabledescribing the codes for processing states used int_proc_evnts.
| Attributes |
Data Type |
Null? |
Comment |
| proc_state |
character(3,1) |
No |
Processing state of species being processed. |
| descrptn |
character(30,1) |
No |
Description of corresponding code for the processing state. |
| |
|
|
|
| Creator: |
wjt |
| Indices: |
UNIQUE BTREE i_proc_state ON (proc_state) |
5.6 Table 6: t_environ
Comment:Observations of environmental conditions. Time and positionfor each set of observations (each environmental event) can befound in t_events. Jigging environmental observations are made at0100 hr, and for trawling they are made before the first shot ofthe day.
| Attributes |
Data Type |
Null? |
Comment |
| event_key |
decimal(12,0) |
No |
Unique identifier for an event. Each environmental, fishing and processing event for any day has a different key. |
| data_ver |
smallint |
No |
Version number of data for event; e.g., 1 for first version of data as received from MFish. Each time data are changed the record containing the old data remains unchanged but a new record, containing updated data, is inserted with data_ver having a value of one greater than data_ver for the record containing the old data. |
| sst |
decimal(3,1) |
|
Sea surface temperature in degrees Celsius. range 3.0 to 24.0 |
| bttm_temp |
decimal(3,1) |
|
Temperature at sea bottom or at depth of trawl, in degrees Celsius. This is only recorded in trawling. range 0.5 to 24.0 |
| speed |
longinteger |
|
Wind speed in meters/second. Recorded for jigging only. range 0 to 20 |
| direction |
longinteger |
|
Wind direction in degrees. Recorded for jigging only. range 0 to 360 |
| bttm_depth |
longinteger |
|
Bottom depth in meters. Also recorded in t_fish_events. range 40 to 1500 |
| validity |
character(7,1) |
|
Status flag for the data.
C : Data have been checked and are ready for sending to the main database. Editing data will cause this flag to revert to an N.
E : Error found during checking. Data will not be sent until errors have been cleared and data re-checked.
N : Data not yet sent to the main database. Editing may occur. |
| validity |
(comments continued) |
|
Z : A data entry person has flagged
this record as being in error, even
though the computer may not see it as
so. This will suspend processing on
this record until the flag is changed
to something else. |
| |
match "[CENZ]" |
|
|
| Creator: |
wjt |
| Referential: |
Invalid event key (event_key) INSERT t_events (event_key) |
| Indices: |
NORMAL (2,15) BTREE i_envkey ON (event_key)
UNIQUE BTREE i_env ON (event_key, data_ver) |
5.7 Table 7: t_versions
Comment: Currentversion for each event. This table can be used to speed up theextraction of data where there are joins between event tables.
| Attributes |
Data Type |
Null? |
Comment |
| event_key |
decimal(12,0) |
No |
Unique identifier for an event. Each environmental, fishing and processing event for any day has a different key. |
| curr_ver |
smallint |
No |
Version number of latest version of data |
| Creator: |
wjt |
| Indices: |
UNIQUE BTREE i_curr ON(event_key) |
5.8 Table 8: t_vessels
Comment:Information on each vessel, including identification,country of origin and size.
| Attributes |
Data Type |
Null? |
Comment |
| vess_key |
longinteger |
No |
Unique identification number for a vessel. |
| vess_name |
character(30,1) |
|
Vessel name |
| reg_no |
longinteger |
|
Vessel registration number. |
| call sign |
character(8,1) |
|
Callsign of vessel |
| nation |
character(5,1) |
|
Country for fishing registration purposes. |
| reg_length |
decimal(4,1) |
|
Registered length of vessel in metres. |
| gross_ton |
decimal(5,1) |
|
Gross tonnage of a vessel. |
| sst_type |
character(1,1) |
|
Although currently unavailable, this will indicate whether sea surface temperature recorders take continuous or spot readings. |
| Creator: |
wjt |
| Referential: |
Invalid vessel key (vess_key) DELETE t_vess_reg (vess_key) |
| Indices: |
UNIQUEBTREE i_vessel ON (vess_key) |
5.9 Table 9: t_vess_reg
Comment: Type offishing vessel for each registration period.
| Attributes |
Data Type |
Null? |
Comment |
| vess_key |
vess_key |
No |
Unique identification number for a vessel. |
| reg_date |
date(0) |
No |
Date of registration. If no registration date has been entered from the registration form, this date is the date on which the registration information has been entered into the registration system. If reg_date is 19000101 then no registration date was available. This is used instead of null as reg_date is part of the primary key. |
| end_date |
date(0) |
|
Where a vessel has been registered more than once, this is the date of the last day before the vessel has been registered again. |
| vess_type |
character(3,1) |
|
Fishing type of vessel – i.e., jigger or trawler. |
| reg_type |
character(1,1) |
|
Type of registration. D = Domestic F = Foreign Licensed C = Charter (Joint Venture) |
| |
match "[CDF]" |
|
|
| Creator: |
wjt |
| Referential: |
Invalid vessel (vess_key) INSERT t_vessels (vess_key) Invalid vesseltype (vess_type) INSERT t_vess_types (vess_type) |
| Indices: |
NORMAL (2, 15) BTREE i_vreg ON (vess_key)
UNIQUE BTREE i_reg ON (vess_key, reg_date) |
5.10 Table 10: t_vess_types
Comment: Tabledescribing the codes for vessel types used in t_vess_reg.
| Attributes |
Data Type |
Null? |
Comment |
| vess_type |
character(3,1) |
|
No Code for vessel type |
| descrptn |
character(30,1) |
|
Description of the corresponding code for the vessel type |
|
|