5  rec_data  Tables

The following are listings of the tables in the rec_data database, including attribute names, data types (and any range restrictions), and comments.   

5.1  Boat ramp and shellfish harvest tables:

5.1.1    Table 1: t_session

Comment:   Boat ramp or shellfish harvest session.

 

 Attributes                  Data Type              Null?      Comment

 

 sess_no                    longinteger               No          Session number.

 

 ramp                         character(3,1)           No          Code for boat ramp or beach etc where the session was conducted, refer t_ramp_codes.

 

 sess_date                 d ate(4)                      No          Session date.

 

 sess_time_s              integer                                   Session time start (24 hour, HHMM format) .

 sess_time_f               integer                                   Session time finish (24 hour, HHMM format) .

 

 t_code                      character(1,1)                         Time of day code.

 

 sess_hrs                   decimal(4,2)                           Length of the time period in decimal hours covered by this session.

 

 iv_type                      character(1,1)                         Interview type: 1=Ramp, 2=Beach, 3=Roving boat, 4=Other, 5=Marina, 6=Fixed, 7=Roving.

 

 platform_type             character(1,1)                         Platform the session was conducted from: A=Aircraft, B=Boat, L=Land.

 

 day_type                   smallint                                  Day type: 1=Weekend or Public holiday, 2=Weekday, 3=Contest.

 

 trailer_s                     integer                                   Number of trailers in the car park at start of the session .

 

 trailer_m                    integer                                   Number of trailers in the car park at middle of the session.

 

 trailer_f                      integer                                   Number of trailers in the car park at finish of the session .

 

 boats_not_iv              integer                                   Number of boats not interviewed.

 

 low_tide                    integer                                   Time of low tide in 24-hour hhmm format .

 

 high_tide                   integer                                   Time of high tide in 24 hour hhmm format .

 

 swell_ht                    decimal(2,1)                           Swell height in metres.

    

 sea_state                  smallint                                  Sea conditions, refer to Appendix 3 of the database documentation for the codes.

 

 rain                           smallint                                  Rain, refer to Appendix 3 of the database documentation for the codes.

 

 overhead                   smallint                                  Overhead conditions, refer to Appendix 3 of the database documentation for the codes.

 

 wind_speed               smallint                                  Wind speed, refer to Appendix 3 of the database documentation for the codes.

 

 wind_dirn                   smallint                                  Wind direction, refer to Appendix 3 of the database documentation for the codes.

 

 wind_knots                decimal(3,1)                           Wind speed in knots.

 

 moon                        integer                                   Moon phase.

 

 region                       character(1,1)                         Survey base region: N=North ie Auckland, C=Central& South ie the rest.

 

 sess_type                 character(1,1)                         Session type code, conducted by: I=Interview, O=Observer.

 

 survey                       character(5,1)                         Survey code: 3 chars for region + 2 numerics for year, refer t_survey_codes.

 

 comments                 text(60,60,60,1)

 

 Creator:                     smdof

 Referential:                INVALID SESSION SURVEY (survey) INSERT t_survey_codes (survey)

                                 INVALID t_session ramp (ramp) INSERT t_ramp_codes (ramp)

 Indices:                     UNIQUE t_session_pk ON (sess_no)

                                 NORMAL (2, 15) sess_ramp_ndx ON (ramp)

                                 NORMAL (2, 15) sess_date_ndx ON (sess_date)      

 

5.1.2  Table 2: t_fisher_count

Comment:  Fisher count data for shellfish harvest surveys.

 

  Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger                 No          Session number.

 

 hr                             integer                        No          Hour, ie time of this count.

 

 fish_loc                     smallint                      No          Strata No, for a spatial strata (area) of the beach

 

 no_pickers                smallint                                  Number of pickers (or fishers)

 

 survey                       character(5,1)                         Survey code: 3 chars for region + 2 numerics for yr

 

  Creator:                   smdof

  Referential:              t_fish_count refer (sess_no) INSERT t_session (sess_no)

  Indices:                    UNIQUE fishercount_pk ON (sess_no, hr, fish_loc)

  

 

5.1.3  Table 3: t_group

Comment:  Boat ramp survey, boat or shellfish harvest group details.

 

  Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger               No          Session number.

 

 group_no                   integer                     No          Group number for shellfish harvest or boat number for boat ramp survey.

 

 group_type                character(1,1)                         Group type, ie boat type, refer t_boat_codes.

 

 outcome                    character(1,1)                         I ntercept outcome I=Interviewed, N=Not interviewed, R=Refused, O=Other, Z=Incomplete, X=Interviewed but invalid for CPUE analysis

 

 time_i                       integer                                   Time of intercept of fishing group (24 hour, HHMM format) .

 

 no_fishers                 smallint                                  Number of fishers in the group.

 

 no_male                    smallint                                  Number of male fishers in the group.

 

 no_female                 smallint                                  Number of female fishers in the group.

 

 region                       character(1,1)           No          Survey base region: N=North, C=Central & South.

 

 survey                       character(5,1)                         Survey code: 3 chars for region + 2 numerics for yr

 

 comments                 text(60,60,60,1)

 

  Creator:                    smdof

  Referential:              t_group refer (sess_no) INSERT t_session (sess_no)

  Indices:                     NORMAL (2, 15) group_survey_ndx ON (survey)

                                 UNIQUE t_group_pk ON (sess_no, group_no)

 

 

5.1.4    Table 4: t_interview

Comment:   Boat ramp or shellfish harvest survey - interview.

 

  Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger               No          Session number.

 

 group_no                   integer                     No          Group number or boat number.

 

 fisher_no                   integer                     No          Fisher number, -1 where the interview is for the combined group

                                                                              eg for the shellfish harvest survey.

 

 combo                      character(1,1)           No          A sequential number for each combination of target species, fishing method & location.

 

 fish_zone                  smallint                                  Fishing zone, refer t_zonef_codes and Appendix 4 of the database documentation.

 

 fish_loc                     character(4,1)                         Fishing location code, refer t_locality_codes.

 

 sub_region                character(2,1)                         Sub region - similar to fishing zone, refer to Appendix 3 of the database documentation.

 

 locality                      character(40,1)                        Fishing locality description.

 

 fish_meth                  character(2,1)                         Fishing method code, refer t_fishmeth_codes.

 

 meth_type                 smallint                                  Code to identify which fishing method coding used in attr fish_meth

 

 no_rods                     integer                                   Number of hand lines and/or rods used.

 

 no_ll_hooks               integer                                   Number of Hooks for multi hook line methods.

 

 burley                       character(1,1)                         Was burley or groundbait used to catch their fish, Y=Yes N=No.

 

 fish_finder                  character(1,1)                         Did they use a fishfinder to catch their fish, Y=Yes N=No.

 

 target_spp                 character(15,1)                        List of 3-char codes (separated by commas) for the species targetted , see rdb:curr_spp.

 

 fish_time_s                integer                                   Fishing time start , in 24-hour hhmm format .

 

 fish_time_f                 integer                                   Fishing time finish , in 24-hour hhmm format .

 

 not_fish_t                  integer                                   Time not fishing , in hhmm format .

 

 hrs_fished                 decimal(4,1)                           Time spent fishing in decimal hours.

 

 days_per_period        integer                                    How many days fished in the period in attr f_hist_period, eg days per year.

    

  f_hist_period              integer                                   Number of days in the time period asked in the question for attr days_per_period.

 

 yearsfished                integer                                   Approximate number of years they have been fishing for target species and methods stated.

 

 dist                           decimal(4,1)                           Distance offshore from the mainland of the fishing activity in km.

 

 sex                           character(1,1)                         Sex of the fisher, M/F.

 

 race                          character(1,1)                         Racial group of the fisher, codes : E   European, M  Maori, P  Polynesian, A  Asian, N  Negroid.

 

 int_before                   character(1,1)                        Has the fisher been interviewed by a boat ramp interviewer before.

 

 age_gp                      character(1,1)                         Age group,  1=15-20, 2=21-30, 3=31-40, 4=41-50, 5=51-60, 6=61 yrs & over.

 

 fish_diary                  character(1,1)                         Do they keep a fishing diary, Y or N.

 

 maf_diary                  character(1,1)                         Do they keep a MAF survey fishing diary, Y or N.

 

 phone                       character(1,1)                         If the fisher lives in a house that has a telephone

 

 resp_locn                  character(30,1)                       Where the fishers residence is located.

 

 area                          character(4,1)                         Area code, refer rdb:area_codes.

 

 region                       character(1,1)                         Survey base region: N=North, C=Central & South.

 

 survey                       character(5,1)                         Survey code: 3 chars for region + 2 numerics for yr

 

 comments                 text(60,60,60,1)

 

 id                              character(15,1)                       Concatenation of : sess_no*boat_no*fisher_no*combo to use as key to join to t_length.

 

  Creator:                   smdof

  Referential:              invalid fish_loc (fish_loc) INSERT t_locality_codes (fish_loc)

                                 interview pk refer (sess_no, group_no) INSERT t_group (sess_no, group_no)

                                 invalid interview fish_meth (fish_meth, meth_type)

                                 INSERT t_fishmeth_codes (fish_meth, meth_type)

                                 invalid interview fish_zone (fish_zone, survey) INSERT

                                 t_zonef_codes (fish_zone, survey)

 Indices:                    NORMAL (2, 15) iv_boat_ndx ON (group_no)

                                 NORMAL (2, 15) iv_fisher_ndx ON (fisher_no)

                                 NORMAL (2, 15) iv_combo_ndx ON (combo)

                                 NORMAL (2, 15) iv_zone_ndx ON (fish_zone)

                                 NORMAL (2, 15) iv_meth_ndx ON (fish_meth)

                                 NORMAL (2, 15) iv_target_ndx ON (target_spp)

                                 NORMAL (2, 15) ON (id)

                                 UNIQUE t_interview_pk ON (sess_no, group_no, fisher_no, combo) 

 

5.1.5    Table 5: t_weight

Comment:   Table containing weights of species caught by shellfish harvesters on a beach

 

Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger               No          Session number

 

 group_no                   integer                     No          Group number or boat number.

 

 fisher_no                   smallint                    No          Fisher number.

 

 combo                      character(1,1)           No          A sequential number for each combination of target species, fishing method & location.

 

 species                     character(3,1)           No          3-char species code, refer rdb:curr_spp.

 

 no_fish                      integer                                   Fish count.

 

 est_wt                       decimal(3,1)                           Estimated weight (in decimal kg).

 

 agreed_wt                 decimal(3,1)                           Agreed weight obtained by weighing the catch (in decimal kg).

 

 survey                       character(5,1)                         Survey code, 3 chars for region + 2 numerics for yr

 

 id                              character(15,1)                       Concatenation of : sess_no*boat_no*fisher_no*combo to use as a key to join tables.

 

  Creator:                   smdof

  Indices:                    NORMAL (2, 15) weight_sess_ndx ON (sess_no)

                                 UNIQUE weight_pk ON (sess_no, group_no, fisher_no, combo, species)

 

 

5.1.6    Table 6: t_length

Comment:  Fish lengths from the boat ramp or shellfish harvest survey

 

Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger               No          Session number.

 

 group_no                   integer                     No          Group number or boat number.

 

 fisher_no                   integer                     No          Fisher number.

 

 combo                      character(1,1)           No          A sequential number for each combination of target species, fishing method & location.

 

 species                     character(3,1)           No          3-char species code, refer rdb:curr_spp.

 

 lgth                           decimal(4,1)                           Length of the fish (cm) - except for rock lobster(CRA) which is tail width in mm, or shellfish TL in mm

 

 lgth_code                  character(1,1)                         Precision of length measurement,

                                                                              R = Rounded down to nearest cm,

                                                                              E = Exact to 1 decimal place.

 

 width_meas               integer                                   Width of the fish (cm) or mm for shellfish. Currently width of OYS (mm)

 

 weight                       decimal(7,2)                           Fish weight, in decimal kg.

 

 no_fish                      integer                                   Fish count.

 

 observd                     character(1,1)                         Observed, refer t_observd_codes

 

 meas_meth               character(1,1)                         Fish measurement method, refer rdb:t_fish_meas_codes

 

 cpue                         character(1,1)                         Include in CPUE analysis ? A=Yes, X=No

 

 region                       character(1,1)                         Survey base region: N=North, C=Central & South.

 

 survey                       character(5,1)                         Survey code, 3 chars for region + 2 numerics for yr .

 

 id                              character(15,1)                       Concatenation of : sess_no*boat_no*fisher_no*combo   to use as a key to join tables.

 

  Creator:                   smdof

  Referential:              invalid species (species) INSERT {/data/db2/rdb,neptune.niwa.cri.nz,rdb} :species_master (code)

                                 length pk refer (sess_no, group_no, fisher_no, combo)

                                 INSERT t_interview (sess_no, group_no, fisher_no, combo)

  Indices:                     NORMAL (2, 15) lgth_survey_ndx ON (survey)

                                 NORMAL (2, 15) lgth_sess_ndx ON (sess_no)

                                 NORMAL (2, 15) lgth_boat_ndx ON (group_no)

                                 NORMAL (2, 15) lgth_fisher_ndx ON (fisher_no)

                                 NORMAL (2, 15) lgth_combo_ndx ON (combo)

                                 NORMAL (2, 15) lgth_spp_ndx ON (species)

                                 NORMAL (2, 15) lgth_id_ndx ON (id)

     

 

5.1.7    Table 7: t_observd_codes

Comment: Descriptions for codes for attr observd in table t_length

 

Attributes                 Data Type               Null?      Comment

 

 observd                     character(1,1)           No          Code for the observed status of fish.

 

 descrptn                    character(80,1)         No          Description of the observd code .

 

  Creator:                   dba

  Indices:                     UNIQUE observd_pk ON (observd)

 

    

5.1.8    Table 8: t_observer

Comment: Description of the observations of fishing activity.

 

Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger               No          Session number.

 

 run_no                      smallint                    No          Run number which identifies a bunch of observations.

 

 group_no                   integer                     No          Group number or boat number – identifies a group of fishers.

 

 activity_no                 smallint                    No          Activity number identifies different fishing activities, e.g. different fishing methods.

 

 start_time                  integer                                   Start time of the run or observation.

 

 run_end_t                  integer                                   Run end time.

 

 rotation                     character(1,1)                         Direction in which the run of the area was made, C = Clockwise (North to South), A =Anti-clockwise (South to North)

 

 fish_zone                  smallint                                  Fishing zone, refer t_zonef_codes.

 

 fish_loc                     character(3,1)                         Fishing location code, refer t_locality_codes.

 

 fish_meth                  character(2,1)                         Fishing method code, refer t_fish_meth_codes.

 

 meth_type                 smallint                                  Code to identify which fishing method  coding used in attr fish_meth.

 

 survey                       character(5,1)                         Survey code: 3 chars for region + 2  numerics  for yr

 

 comments                 character(80,1)

 

  Creator:                   dba

 Referential:              t_observer refer (sess_no) INSERT t_session (sess_no)

  Indices:                    UNIQUE BTREE t_observer_pk ON (sess_no, run_no, group_no, activity_no)

 

5.1.9    Table 9: t_obs_count

Comment:     Contains counts of observations of fishing activity from the table t_observer.

 
Attributes                  Data Type               Null?      Comment

 

sess_no                     longinteger               No          Session number.

 

run_no                       smallint                    No          Run number which identifies a bunch of observations.

 

group_no                    integer                     No          Group number or boat number – identifies a group of fishers.

 

activity_no                  smallint                    No          Activity number identifies different fishing activities, e.g. different fishing methods.

 

tally                           integer                                   Count for a particular fishing activity.

 

count_code                character(3,1)           No          Code to identify what was counted in attribute tally.

 

survey                        character(5,1)                         Survey code: 3 chars for region + 2  numerics  for yr

 

 

 

  Creator:                   dba

 Indices:                     UNIQUE count_codes_pk ON ( count_code )

 

 

5.1.10  Table 10: t_count_codes

Comment:    This table contains the codes and descriptions for the types of count_code used in observation surveys.

 

Attributes                  Data Type               Null?      Comment

 

 count_code               character(3,1)           No          3 character code for the type of fishing activity in an observation of fishing activity, as used in t_obs_count.count_code.

 

 descrptn                    character(80,1)                       Description of the count_code.

 

 Creator:                   dba

  Indices:                     UNIQUE BTREE t_count_codes pk ON (count_code)

 

5.1.11 Table 11: t_ctch_cen93

Comment:  Catch details by species from the 1992/92 Central Region boat ramp survey. Data were collected in a different format to other surveys and are therefore no in the main t_length table (see also t_len_cen93)

Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger               No          Session number

 

 group_no                   integer                     No          Fishing group number

 

 fisher_no                   integer                     No          Fisher number

 

 combo                      character(1,1)           No          Combo

 

 species                     character(3,1)           No          Species code, refer rdb:species_master

 

 no_fish                      integer                                   Fish count

 

 weight                       decimal(7,2)                           Fish weight (kg)

 

 observd                     character(1,1)                         Observed code, refer t_observd_codes

 

 region                       character(1,1)                         1 char code for survey region. Always C=central

 

 survey                       character(5,1)                         5 char code for survey. Always CEN93

 

 id                              character(15,1)                       Concatenation of : sess_no*boat_no*fisher*combo to use as a key to join tables.

 

  Creator:                   dba

  Indices:                     NORMAL (2, 15) ctch_cen93_species_ndx ON (species)

    

 

5.1.12  Table  12 : t_len_cen93

Comment:    Fish lengths from the 1992/93 Central Region boat ramp survey. Data were collected in a different format and are therefore not in the main t_length table (see also t_ctch_cen93)

Attributes                 Data Type               Null?      Comment

 

 sess_no                    longinteger               No          Session number

 

 group_no                   integer                     No          Fishing group number

 

 fisher_no                   integer                     No          Fisher number

 

 combo                      character(1,1)           No          Combo

 

 species                     character(3,1)           No          Species code, refer rdb:species_master

 

 lgth                           integer                                   Length of the fish (cm) - except for rock lobster (CRA) which is tail width in mm

 

 weight                       decimal(7,2)                           Fish weight (not used, see t_ctch_cen93)

 

 no_fish                      integer                                   Fish count

 

 observd                     character(1,1)                         Observed code, refer t_observd_codes

 

 meas_meth               character(1,1)                         Fish measurement method, refer rdb:t_fish_meas_codes

 

 cpue                         character(1,1)                         Include in CPUE analysis ? Always X=no

 

 region                       character(1,1)                         1 char code for survey region. Always C=central

 

 survey                       character(5,1)                         5 char code for survey. Always CEN93

 

 id                              character(15,1)                       Concatenation of : sess_no*boat_no*fisher*combo to use as a key to join tables.

 

  Creator:                   dba

  Indices:                     NORMAL (2, 15) len_cen93_species_ndx ON (species)