|
|
5 beach Tables
The following listings of the tables in the beach database,
including attribute names, data types (and any range restrictions),
and comments.
5.1 Table 1: t_survey
|
Comment:
|
Details of individual beach surveys. Usually, a survey is
restricted to just one beach.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
proj_code
|
Character(10,1)
|
|
Ministry Fisheries code for the project that collected the
data.
|
|
date_s
|
date(4)
|
|
Start date of the survey.
|
|
date_f
|
date(4)
|
|
Finish date of the survey.
|
|
contact_name
|
character(20,1)
|
|
Name of contact person.
|
|
areas
|
character(15,1)
|
|
List of 4-char area codes, separated by commas, for areas
where the survey takes place. Refer rdb:area_codes for code
descriptions.
|
|
mainspp
|
character(12,1)
|
No
|
List of 3-char species codes, separated by commas, for species
involved in the survey. Refer rdb:curr_spp for code descriptions.
|
|
comments
|
text(20,20,20,1)
|
|
General comments about the survey.
|
|
Creator:
|
dba
|
|
Referential:
|
Child records in t_stratum (survey)
DELETE t_stratum (survey)
|
|
Indices:
|
UNIQUE t_survey_PK ON (survey)
|
5.2 Table 2: t_stratum
|
Comment:
|
Details of strata used for individual beach surveys.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code. Unique for each stratum within an
individual survey. Default="1"
|
|
area_m2
|
longinteger
|
|
Area (sq. metres) of stratum.
|
|
descrptn
|
text(60,20,20,1)
|
|
General description of the stratum.
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid survey code (survey)
INSERT t_survey (survey) Child records in t_transect (survey,
stratum)
DELETE t_transect (survey, stratum) Child records in t_bed
(survey, stratum)
DELETE t_bed (survey, stratum)
|
|
Indices:
|
UNIQUE t_stratum_PK ON (survey, stratum)
|
5.3 Table 3: t_transect
|
Comment:
|
Details of individual transects used during a survey.
|
|
Attributes
|
Data Type
|
Null?
|
Comment s
|
|
urvey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
transect
|
character(5,1)
|
No
|
Transect number. Unique within each stratum.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code (Default="1")
|
|
area
|
character(4,1)
|
No
|
4 char area code. Refer rdb:area_codes
|
|
phase
|
integer
|
|
Phase number. Used for two-phase survey design.
|
|
locality
|
character(40,1)
|
|
General descriptive locality of the transect.
|
|
latitude
|
decimal(8,6)
|
|
Latitude (decimal degrees) of the transect.
|
|
longitude
|
decimal(9,6)
|
|
Longitude (decimal degrees) of the transect.
|
|
bed_no
|
integer
|
|
Identifier number of the bed that the transect runs through.
Refer t_bed.
|
|
comments
|
text(20,20,20,1)
|
|
General comments for the transect.
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid stratum code (survey, stratum)
INSERT t_stratum (survey, stratum)
Child records in t_quadrat (survey, stratum, transect)
DELETE t_quadrat (survey, stratum, transect)
|
|
Indices:
|
NORMAL (2, 15) t_transect_PK ON (survey, stratum, transect)
|
5.4 Table 4: t_quadrat
|
Comment:
|
Details of each quadrat used within a transect in a beach
survey.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum c
|
haracter(4,1)
|
No
|
4-character stratum code (Default="1").
|
|
transect
|
character(5,1)
|
No
|
Transect number. Unique within each stratum.
|
|
quadrat
|
decimal(4,1)
|
No
|
Quadrat number. May be distance (m) along the transect from a
fixed point (such as the mean high-tide mark).
|
|
area_m2
|
decimal(4,2)
|
|
Area (sq. metres) for each quadrat.
|
|
depth
|
decimal(4,2)
|
|
Depth (m) quadrat dug to.
|
|
x_coord
|
character(10,1)
|
|
X coordinate for each quadrat.
|
|
y_coord
|
character(10,1)
|
|
Y coordinate for each quadrat.
|
|
comments
|
text(20,20,20,1)
|
|
General comments for quadrat.
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid transect number (survey, stratum, transect)
INSERT t_transect (survey, stratum, transect) Child records in
t_replicate (survey, stratum, transect, quadrat)
DELETE t_replicate (survey, stratum, transect, quadrat)
|
|
Indices:
|
UNIQUE t_quadrat_PK ON (survey, stratum, transect, quadrat)
|
5.5 Table 5: t_replicate
|
Comment:
|
Details of replicates within a quadrat.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code (Default="1").
|
|
transect
|
character(5,1)
|
No
|
Transect number.
|
|
quadrat
|
decimal(4,1)
|
No
|
Quadrat number. May be distance (m) along the transect from a
fixed point (such as the mean high-tide mark).
|
|
replicate
|
integer
|
No
|
Unique replicate number (within a quadrat).
|
|
date_sampled
|
date(4)
|
|
Date quadrat replicate sampled.
|
|
sieve_size
|
integer
|
|
Sieve size (mm) used for replicate.
|
|
comments
|
text(20,20,20,1)
|
|
General comments for replicate.
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid quadrat number (survey, stratum, transect, quadrat)
INSERT t_quadrat (survey, stratum, transect, quadrat)
Child record in t_catch (survey, stratum, transect, quadrat,
replicate)
DELETE t_catch survey, stratum, transect, quadrat, replicate)
|
|
Indices:
|
UNIQUE t_replicate_PK ON (survey, stratum, transect, quadrat,
replicate)
|
5.6 Table 6: t_catch
|
Comment:
|
Catch details for each species caught.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code (Default="1").
|
|
transect
|
character(5,1)
|
No
|
Transect number.
|
|
quadrat
|
decimal(4,1)
|
No
|
Quadrat number.
|
|
replicate
|
integer
|
No
|
Replicate number.
|
|
species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp
|
|
no_fish
|
integer
|
|
Number of the species caught.
|
|
lf_taken
|
character(1,1)
|
|
Were length frequencies taken? Y=Yes N=No.
|
|
|
Smatch "[YN]"
|
|
|
|
samp_meth
|
character(1,1)
|
|
1-character code for sample selection method. Refer
rdb:t_samp_sel_codes
|
|
meas_meth
|
character(1,1)
|
|
1-character code for method used to measure the species. Refer
rdb:t_fish_meas_codes
|
|
weight
|
decimal(8,3)
|
|
Weight (kg) of species caught.
|
|
wt_meth
|
character(1,1)
|
|
1-character code for method used to weight catch. Refer
rdb:t_wgt_meth_codes samp_wt decimal(8,3) Weight (grams) of
sample taken from the whole catch for further measurement.
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid replicate number (survey, stratum, transect, quadrat,
replicate)
INSERT t_replicate (survey, stratum, transect, quadrat,
replicate)
Invalid catch species (species)
INSERT rdb : curr_spp (code)
Invalid sample selection method (samp_meth)
INSERT rdb : t_samp_sel_codes (samp_sel_code)
Invalid shell measurement method (meas_meth)
INSERT rdb : t_fish_meas_codes (fish_meas_code)
Invalid weight method (wt_meth)
INSERT rdb : t_wgt_meth_codes (wgt_meth_code)
Child records in t_length (survey, stratum, transect, quadrat,
replicate, species)
DELETE t_length (survey, stratum, transect, quadrat,
replicate, species)
Child records in t_lgth_wgt (survey, stratum, transect,
quadrat, replicate, species)
DELETE t_lgth_wgt (survey, stratum, transect, quadrat,
replicate, species)
|
|
Indices:
|
NORMAL (2, 15) catch_species_ndx ON (species)
UNIQUE t_catch_PK ON (survey, stratum, transect, quadrat,
replicate, species)
|
5.7 Table 7: t_length
|
Comment:
|
Length frequency measurements for specimens caught.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code (Default="1").
|
|
transect
|
character(5,1)
|
No
|
Transect number.
|
|
quadrat
|
decimal(4,1)
|
No
|
Quadrat number.
|
|
replicate
|
integer
|
No
|
Replicate number.
|
|
species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp
|
|
lgth
|
integer
|
No
|
Length (mm) class.
|
|
percent_samp
|
decimal(5,2)
|
|
Percent (%) of total catch measured.
|
|
|
Range 0.00 i 100.00 i
|
|
|
|
no_a
|
integer
|
|
Number of animals at this length.
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid catch (survey, transect, quadrat, replicate, species)
INSERT t_catch (survey, transect, quadrat, replicate, species)
Invalid length species (species)
INSERT rdb : curr_spp (code)
|
|
Indices:
|
NORMAL (2, 15) length_species_ndx ON (species)
NORMAL (2, 15) length_survey_ndx ON (survey)
|
5.8 Table 8: t_lgth_wgt
|
Comment:
|
Length and weight data for individual animals caught.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code (Default="1").
|
|
transect
|
character(5,1)
|
No
|
Transect number.
|
|
quadrat
|
decimal(4,1)
|
No
|
Quadrat number.
|
|
replicate
|
integer
|
No
|
Replicate number.
|
|
species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp
|
|
fish_no
|
integer
|
|
Unique sequential number for each specimen in a sample.
|
|
lgth
|
decimal(4,1)
|
No
|
Length (mm) class.
|
|
weight
|
decimal(6,2)
|
|
Weight (grams).
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid length species (species)
INSERT rdb: curr_spp (code)
Invalid catch (survey, transect, quadrat, replicate, species)
INSERT t_catch (survey, transect, quadrat, replicate,
species):
|
|
Indices
|
NORMAL (2, 15) BTREE lgth_wgt_species_ndx ON (species)
NORMAL (2, 15) BTREE lgth_wgt_survey_ndx ON (survey)
|
5.9 Table 9: t_bed
|
Comment:
|
Contains dimensions and location details for flora or fauna
beds along a beach e.g., ulva (sea lettuce) or toheroa beds.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code (Default="1") bed_no
integer No Unique number to identify each bed.
|
|
phase
|
integer
|
|
Phase number. Used for two-phase survey design.
|
|
locality
|
character(40,1)
|
|
General descriptive locality of the bed.
|
|
latitude
|
decimal(8,6)
|
|
Latitude (decimal degrees) of the bed.
|
|
longitude
|
decimal(9,6)
|
|
Longitude (decimal degrees) of the bed.
|
|
bed_length
|
integer
|
|
Length (metres) of the bed.
|
|
bed_width
|
integer
|
|
Width (metres) of the bed.
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid stratum code (survey, stratum)
INSERT t_stratum (survey, stratum) Child records in
t_bed_density (survey, stratum, bed_no)
DELETE t_bed_density (survey, stratum, bed_no)
|
|
Indices:
|
UNIQUE BTREE bed_PK ON (survey, stratum, bed_no)
|
5.10 Table 10: t_bed_density
|
Comment:
|
Contains data on densities of a flora or fauna species within
a bed.
|
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
survey
|
character(10,1)
|
No
|
Unique identifier for each survey.
|
|
stratum
|
character(4,1)
|
No
|
4-character stratum code (Default="1") bed_no
integer No Unique number to identify each bed.
|
|
species
|
character(3,1)
|
No
|
3-character species code. Refer rdb:curr_spp
|
|
density_area_m2
|
integer
|
|
Surface area (sq. metres) by which the species density is
calculated for.
|
|
density
|
integer
|
|
Density (number of the species per density_area_m2).
|
|
Creator:
|
dba
|
|
Referential:
|
Invalid bed number (survey, stratum, bed_no)
INSERT t_bed (survey, stratum, bed_no)
|
|
Indices:
|
UNIQUE BTREE bed_density_PK ON (survey, stratum, bed_no,
species)
|
|