|
|
5 age Tables
The following are listings of the tables in the age database, including attribute names, data types (and any range restrictions), and comments.
5.1 Table 1: t_trip
Comment: |
Table of location information about ageing material from species caught on a trip. |
Attributes |
Data Type |
Null? |
Comment |
origin |
character(3,1) |
No |
3 character code for the origin of the ageing material. Refer:t_origin. |
Yr |
integer |
No |
Year fish was sampled. |
trip_code |
character(8,1) |
No |
Up to 8 char trip identification code. |
Area |
character(4,1) |
|
Up to 4 character area code. Codes depend on the species sampled. |
Species |
character(3,1) |
No |
Species code of fish. Refer rdb:curr_spp. |
Room |
character(50,1) |
|
Room number which the ageing material can be found. |
sub_loc |
character(50,1) |
|
Location within the room.g., file cabinet number, draw number. |
Status |
character(25,1) |
|
Current status of the ageing material. |
status_date |
date(5) |
|
Date when status was updated. |
| comments |
character(240,1) |
|
|
Creator: |
dba |
Referential: |
invalid origin code (origin) INSERT t_origin (code) invalid species code (species) INSERT rdb:curr_spp (code) |
Indices: |
NORMAL (2, 15) BTREE ON (origin) NORMAL (2, 15) BTREE ON (yr) NORMAL (2, 15) BTREE ON (species) UNIQUE BTREE ON (trip_code, species) |
5.2 Table 2: t_fish
Comment: |
Table of biological information of fish aged. |
Attributes |
Data Type |
Null? |
Comment |
origin |
character(3,1) |
No |
3 character code for the origin of the ageing material. Refer:t_origin. |
Yr |
integer |
No |
Year fish was sampled. |
trip_code |
character(8,1) |
No |
Up to 8 char trip identification code. |
sample_no |
integer |
No |
Number of sample within the trip. This will be station number for Research, stratum number for SMP, and tow number for SOP. |
sub_sample_no |
character(4,1) |
No |
Number of sub sample. This will be subcatch number for Research trawls, cluster (box) number for SMP. SOP do not use this. -1 = Not Used. |
Area |
character(4,1) |
|
Up to 4 character area code. Codes depend on the species sampled. |
Species |
character(3,1) |
No |
Species code of fish. Refer rdb:curr_spp. |
fish_no |
longinteger |
No |
Sequential identifying number of fish for any one trip, sample, sub sample, species. |
Lgth |
decimal(4,1) |
No |
Length (cm) of the fish. See lgth_code for precision of measurement. |
lgth_code |
character(1,1) |
|
Precision of length measurement, R = Rounded down to nearest cm, E = Exact to 1 decimal place.Range: smatch 'R|E' |
sex |
character(1,1) |
|
Sex code. Ref rdb:t_sex_codes. |
| gonad_stage |
character(1,1) |
|
Numeric code for stage of gonad maturity. |
Weight |
integer |
|
Weight (grams) of the fish. |
otolith_weight |
decimal(6,4) |
|
Weight (grams) of a otolith. |
otolith_weight2 |
decimal(6,4) |
|
Weight (grams) of a second otolith. |
otolith_lgth |
decimal(4,1) |
|
Length (mm) of a otolith. |
otolith_width |
decimal(3,1) |
|
Width (mm) of a otolith. |
Material1 |
smallint |
|
1 digit code for the material that was taken for ageing e.g., otolith, spine, scale. Refer t_material. |
Material2 |
smallint 1 |
|
digit code for the second material taken for ageing (if at all). Refer t_material |
fish_select_meth |
smallint |
|
Code for how the fish was selected for ageing: 1 = random 2 = every ith fish 3 = size class |
| proj_code |
character(12,1) |
|
Project code for otilith collection.
|
comments |
character(240,1) |
|
Contains information on fish sampled |
Creator: |
dba |
Referential: |
invalid origin code (origin) INSERT t_origin (code) invalid ageing material1 code (material1) INSERT t_material (code) invalid ageing material2 code (material2) INSERT t_material (code) invalid sex code (sex) INSERT rdb:t_sex_codes (sex_code) invalid species code (species) INSERT rdb:curr_spp (code) invalid trip (trip_code) INSERT t_trip (trip_code) |
Indices: |
NORMAL (2, 15) BTREE ON (origin) NORMAL (2, 15) BTREE ON (yr) NORMAL (2, 15) BTREE ON (trip_code) NORMAL (2, 15) BTREE ON (species) NORMAL (2, 15) BTREE ON (area) UNIQUE BTREE uniq_fish ON (origin, yr, trip_code, sample_no, sub_sample_no, species, fish_no) |
5.3 Table 3: t_reading
Comment: |
Table describing the (sometimes many) results generated from each reading to estimate the age of a fish. The result and error fields are generic to handle the numerous types of reading and measurement techniques. Explanations for what result and error is stored in what attribute is recorded in the matching reading method record in t_age_meth. |
Attributes |
Data Type |
Null? |
Comment |
origin |
character(3,1) |
No |
3 character code for the origin of the ageing material. Refer:t_origin. |
Yr |
integer |
No |
Year fish was sampled. |
trip_code |
character(8,1) |
No |
Up to 8 char trip identification code. |
sample_no |
integer |
No |
Number of sample within the trip. This will be station number for Research, stratum number for SMP, and tow number for SOP. |
sub_sample_no |
character(4,1) |
No |
Number of sub sample. This will be subcatch number for Research trawls, cluster (box) number for SMP. SOP do not use this. -1 = Not Used. |
Area |
character(4,1) |
|
Up to 4 character area code. Codes depend on the species sampled. |
Species |
character(3,1) |
No |
Species code of fish. Refer rdb:curr_spp. |
fish_no |
longinteger |
No |
Sequential identifying number of and individual fish for any one trip, sample, sub sample, and species. |
prep_no |
character(10,1) |
|
Number to identify ageing material within a prepared sample |
reading_no |
smallint |
No |
Sequential number for each reading for each fish. |
reading_date |
date(5) |
|
Date the reading was made on. |
Material |
smallint |
No |
1 digit code for the type of material used for reading e.g., otolith, scales, spines. |
Method |
smallint |
No |
Up to 2 digit code for the method used to read ageing material. |
| reader |
smallint |
No |
Numeric code to identify the reader. Refer t_reader. |
Result1 |
decimal(6,3) |
|
The result of the reading. |
result2 |
character(5,1) |
|
Up to 5 character code for the result |
result3 |
decimal(6,3) |
|
Additional result of the reading. |
Result4 |
decimal(6,3) |
|
Additional result of the reading. |
Result5 |
decimal(6,3) |
|
Additional result of the reading. |
Result6 |
decimal(6,3) |
|
Additional result of the reading. |
Error1 |
decimal(4,2) |
|
The error for result1 i.e. Result1 + or - error1. |
Error2 |
character(3,1) |
|
Additional character code for the error. |
Error3 |
character(3,1) |
|
Additional character code for the error. |
Error4 |
character(3,1) |
|
Additional character code for the error. |
Error5 |
character(3,1) |
|
Additional character code for the error. |
Error6 |
character(3,1) |
|
Additional character code for the error. |
Error7 |
character(3,1) |
|
Additional character code for the error. |
Age |
decimal(4,1) |
|
Agreed age (in years) for the reading. |
| proj_code |
character(12,1) |
|
Project code for the reading.
|
| Comments |
text(240,240,240,1) |
|
|
Creator: |
dba |
Referential: |
invalid material code (material) INSERT t_material (code) invalid reader (reader) INSERT t_reader (code) invalid reading method (method) INSERT t_age_meth (code) invalid origin code (origin) INSERT t_origin (code) invalid species code (species) INSERT rdb:curr_spp (code) insert t_fish before t_reading (trip_code, sample_no, sub_sample_no, species, fish_no) INSERT t_fish (trip_code, sample_no, sub_sample_no, species, fish_no) |
Indices: |
NORMAL (2, 15) BTREE ON (species) NORMAL (2, 15) BTREE ON (origin) NORMAL (2, 15) BTREE ON (yr) NORMAL (2, 15) BTREE ON (trip_code) NORMAL (2, 15) BTREE ON (area) UNIQUE BTREE uniq_reading ON (trip_code, sample_no, sub_sample_no, species, fish_no, reading_no) |
5.4 Table 4: t_catalog
Comment: |
Catalog of all ageing material, their storage location, and current ageing status. |
Attributes |
Data Type |
Null? |
Comment |
origin |
character(3,1) |
No |
3 character code for the origin of the ageing material. Refer:t_origin. |
Yr |
integer |
No |
Year fish was sampled. |
trip_code |
character(8,1) |
No |
Up to 8 char trip identification code. |
sample_no integer |
No |
|
Number of sample within the trip. This will be station number for Research, stratum number for SMP, and tow number for SOP. |
sub_sample_no |
character(4,1) |
No |
Number of sub sample. This will be subcatch number for Research trawls, cluster (box) number for SMP. SOP do not use this. -1 = Not Used. |
Area |
character(4,1) |
|
Up to 4 character area code. Codes depend on the species sampled. |
Species |
character(3,1) |
No |
Species code of fish. Refer rdb:curr_spp. |
fish_no longinteger |
No |
|
Sequential identifying number of an individual fish for any one trip, sample, sub sample, and species. |
Material |
smallint |
|
1 digit code for the type of material used for ageing e.g., otolith, scales, and spines. |
Room |
character(50,1) |
|
Room number which the ageing material can be found. |
sub_loc |
character(50,1) |
|
Location within the room e.g., file cabinet number, draw number. |
Status |
character(25,1) |
|
Current status of the ageing material. |
| status_date |
date (5) |
|
Date when status was last updated. |
| Creator |
dba |
| Referential |
invalid origin code (origin) INSERT t_origin (code)
invalid species code (species) INSERT rdb:curr_spp (code)
invalid ageing meterial code (material) INSERT t_material (code) |
| Indices |
NORMAL (2,15) BTREE ON (origin)
NORMAL (2,15) BTREE ON (yr)
NORMAL (2,15) BTREE ON (trip_code)
NORMAL (2,15) BTREE ON (area)
NORMAL (2,15) BTREE ON (species)
UNIQUE BTREE uniq_catalog ON (origin, yr, trip_code, sample_no, sub_sample_no, species, fish_no) | 5.5 Table 5: t_age
Comment: |
Table listing the agreed age assessment for each fish aged. |
Attributes |
Data Type |
Null? |
Comment |
origin |
character(3,1) |
No |
3 character code for the origin of the ageing material. Refer:t_origin. |
Yr |
integer |
No |
Year fish was sampled. |
trip_code |
character(8,1) |
No |
Up to 8 char trip identification code. |
sample_no |
integer |
No |
Number of sample within the trip. This will be station number for Research, stratum number for SMP, and tow number for SOP. |
sub_sample_no |
character(4,1) |
No |
Number of sub sample. This will be subcatch number for Research trawls, cluster (box) number for SMP. SOP do not use this. -1 = Not Used. |
Area |
character(4,1) |
|
Up to 4 character area code. Codes depend on the species sampled. |
Species |
character(3,1) |
No |
Species code of fish. Refer rdb:curr_spp. |
fish_no |
longinteger |
No |
Sequential identifying number of an individual fish for any one trip, sample, sub sample, and species. |
prep_no |
character(10,1) |
|
Number to identify ageing material within a prepared sample agreed_result1 decimal(6,2) |
| agreed_result1 |
decimal(6,2) |
|
The agreed result of the reading. |
agreed_result2 |
character(5,1) |
|
Up to 5 character code for the result. |
Method |
smallint |
No |
Up to 2 digit code for the method used to read ageing material. |
agreed_age |
decimal(4,1) |
|
Agreed age (years) of the fish. |
agreed_error1 |
decimal(4,2) |
|
The error for result1 i.e. Result1 + or - error1. |
agreed_error2 |
character(3,1) |
|
Additional character code for the agreed error. |
| proj_code |
character(12,1) |
|
Project Code. |
comments |
text(240,240,240,1) |
|
Contains information on materials and methods used to arrive at the agreed age of the fish. |
Creator: |
dba |
Referential: |
invalid species code (species) INSERT rdb:curr_spp (code) invalid reading method (method) INSERT t_age_meth (code) |
Indices: |
NORMAL (2, 15) BTREE ON (trip_code) NORMAL (2, 15) BTREE ON (species) NORMAL (2, 15) BTREE ON (origin) NORMAL (2, 15) BTREE ON (yr) NORMAL (2, 15) BTREE ON (area) UNIQUE BTREE uniq_age ON (trip_code, sample_no, sub_sample_no, species, fish_no, method) |
5.6 Table 6: t_origin
Comment: |
Table describing the origin codes of the ageing material. |
Attributes |
Data Type |
Null? |
Comment |
code |
character(3,1) |
No |
3 character code for the origin of the ageing material. |
Description |
character(60,1) |
No |
Brief description of the origin of the ageing material. |
Creator: |
dba |
Indices: |
UNIQUE BTREE origin_pk ON (code) |
5.7 Table 7: t_material
Comment: |
List of materials used for ageing e.g., otoliths, vertebrae, scales. |
Attributes |
Data Type |
Null? |
Comment |
code |
smallint |
No |
1 digit code for material used for reading. |
Description |
character(60,1) |
No |
Brief description of the ageing material. |
Creator: |
dba |
Indices: |
UNIQUE BTREE ON (code) |
5.8 Table 8: t_reader
Comment: |
List of names of persons who have carried out the ageing analysis. |
Attributes |
Data Type |
Null? |
Comment |
code |
smallint |
No |
Numeric code identifying the reader. |
Name |
character(30,1) |
No |
Name of the reader associated with the code. |
Creator: |
dba |
Indices: |
UNIQUE BTREE ON (code) |
5.9 Table 9: t_age_meth
Comment: |
Table listing the methods used for estimating ages. |
Attributes |
Data Type |
Null? |
Comment |
code |
smallint |
No |
Numeric code for ageing method used. |
Description |
text(240,24,24,1) |
No |
Details on method used to prepare and read ageing material. |
Creator: |
dba |
Indices: |
UNIQUE BTREE ON (code) |
|