|
|
5 hmetal Tables
The following are listings of the tables in the hmetal database,
including attribute names, data types (and any range restrictions),
and comments.
5.1 Table 1: sites
Comment: Table of heavy metal sample location site names
and their area codes.
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
samp_location
|
character(30,1)
|
No
|
Brief description of the sample site location.
|
|
loc_code
|
integer
|
No
|
Unique site number.
|
|
Area
|
character(10,1)
|
|
Area code. Used to link to latitude and longitude positions
in the areas table.
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
Invalid area code (area) INSERT areas (area)
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (area)
UNIQUE BTREE sites_PK ON (samp_location)
|
5.2 Table 2: areas
Comment: Table of all area codes used in the sites table
and their spatial coordinates expressed as latitude and longitude.
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
area
|
character(10,1)
|
|
Area code
|
|
lat
|
decimal(8,6)
|
|
Latitude (decimal degrees; 90 to "90 -ve for southern
hemisphere)
|
|
lon
|
decimal(9,6)
|
|
Longitude (decimal degrees 0 to 360)
|
|
Creator:
|
dba
|
|
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (lat)
NORMAL (2, 15) BTREE ON (lon)
UNIQUE BTREE areas_PK ON (area)
|
5.3 Table 3: fish_samples
Comment: Table of individual fish details including species
code, fish length, weight, sex, and amounts of various organochlorine
insecticides and heavy metals such as DDT, zinc, arsenic, lead, and
mercury.
|
Attributes
|
Data Type
|
Null?
|
Comment
|
|
samp_date
|
character(11,1)
|
No
|
Date that the fish was sampled. Generally recorded in DD MON
YYYY format but when dates were uncertain, just the month or even
just the year was entered.
|
|
samp_location
|
character(30,1)
|
No
|
Location from which the sampled fish was caught. Refer to the
sites table.
|
|
trip_code
|
character(7,1)
|
No
|
7-character trip code from which the sampled fish was caught.
Some trip codes may link to the trawl database. Trips form
unknown sources are prefixed by "xxx".
|
|
station_no
|
integer
|
|
Station number of a trip from which the sampled fish was
caught. Can be used to link to the trawl database.
|
|
species
|
character(3,1)
|
No
|
3-character species code. Refer to the curr_spp table.
|
|
samp_type
|
character(1,1)
|
No
|
1-character sample type code: B=belly D=digestive gland
F=flesh G=gonad M=mantle T=tentacle or tail W=whole.
|
|
|
Smatch "[BDFGKLMPTW]"
|
|
|
fish_no
|
integer
|
|
Individual fish number. May relate to records in
trawl:t_fish_bio.
|
|
lgth
|
decimal(4,1)
|
|
Fish length (cm).
|
|
weight
|
decimal(6,1)
|
|
Fish weight (grams).
|
|
sex
|
character(1,1)
|
|
1-character code for the sex of the samples fish: M=male
F=female U=unsexed.
|
|
|
Smatch "[FMU]"
|
|
|
|
age
|
integer
|
|
Age (years) of the sampled fish.
|
|
HCB
|
decimal(5,2)
|
|
Amount of Hexachlorobenzene measured in the sample (mg.kg-1).
|
|
LIN
|
decimal(5,2)
|
|
Amount of Lindane measured in the sample (mg.kg-1).
|
|
DDE
|
decimal(5,2)
|
|
Amount of DDE measured in the sample (mg.kg-1).
|
|
DDD
|
decimal(5,2)
|
|
Amount of DDD measured in the sample (mg.kg-1).
|
|
DDT
|
decimal(5,2)
|
|
Amount of DDT measured in the sample (mg.kg-1).
|
|
DDT1
|
decimal(5,2)
|
|
Amount of total DDT (i.e. DDT, DDD, and DDE) measured in the
sample (mg.kg-1).
|
|
PCB
|
decimal(5,2)
|
|
Amount of PCB measured in the sample (mg.kg-1).
|
|
Hg1
|
decimal(5,2)
|
|
Amount of Mercury (1st sample) measured in the
sample (mg.kg-1).
|
|
CH3Hg
|
decimal(5,2)
|
|
Amount of CH3Hg measured in the sample (mg.kg-1).
|
|
Cd
|
decimal(5,2)
|
|
Amount of Cadmium measured in the sample (mg.kg-1).
|
|
Cu
|
decimal(5,2)
|
|
Amount of Copper measured in the sample (mg.kg-1).
|
|
Zn
|
decimal(5,2)
|
|
Amount of Zinc measured in the sample (mg.kg-1).
|
|
Pb
|
decimal(5,2)
|
|
Amount of Lead measured in the sample (mg.kg-1).
|
|
As
|
decimal(5,2)
|
|
Amount of Arsenic measured in the sample (mg.kg-1).
|
|
Se
|
decimal(5,2)
|
|
Amount of Selenium measured in the sample (mg.kg-1).
|
|
Hg2
|
decimal(5,2)
|
|
Amount of Mercury (2nd sample) measured in the
sample (mg.kg-1).
|
|
Creator:
|
dba
|
|
|
|
Referential:
|
Invalid sample location (samp_location) INSERT sites
(samp_location) Invalid species code (species) INSERT curr_spp
(code)
|
|
|
|
Indices:
|
NORMAL (2, 15) BTREE ON (samp_location)
|
|
|
The following listing is a view on the union of the tables sties,
areas, and fish_samples.
5.3.1 v_main
|
Comment:
|
View on union of of all the tables to provide a denormalised
view of the hmetal database.
|
|
View:
|
select attr 'samp_date', attr 'samp_location', attr 'area',
attr 'lat', attr 'lon', attr 'trip_code', attr 'station_no', attr
'species', attr 'samp_type', attr 'fish_no', attr 'lgth', attr
'weight', attr 'sex', attr 'age', attr 'HCB', attr 'LIN', attr
'DDE', attr 'DDD', attr 'DDT', attr 'DDT1', attr 'PCB', attr
'Hg1', attr 'CH3Hg', attr 'Cd', attr 'Cu', attr 'Zn', attr 'Pb',
attr 'Se', attr 'Hg2' from 'fish_samples' 'f', 'areas' 'a',
'sites' 's' where ((attr 'f'.'samp_location' = attr
's'.'samp_location' and attr 's'.'area' = attr 'a'.'area'))
|
|
Attributes
|
Data Type
|
Null?
|
|
samp_date
|
character(11,1)
|
No
|
|
samp_location
|
character(30,1)
|
No
|
|
area
|
character(10,1)
|
|
|
lat
|
decimal(8,6)
|
|
|
lon
|
decimal(9,6)
|
|
|
trip_code
|
character(7,1)
|
No
|
|
station_no
|
integer
|
|
|
species
|
character(3,1)
|
No
|
|
samp_type
|
character(1,1)
|
No
|
|
fish_no
|
integer
|
|
|
lgth
|
decimal(4,1)
|
|
|
weight
|
decimal(6,1)
|
|
|
sex
|
character(1,1)
|
|
|
age
|
integer
|
|
|
HCB
|
decimal(5,2)
|
|
|
LIN
|
decimal(5,2)
|
|
|
DDE
|
decimal(5,2)
|
|
|
DDD
|
decimal(5,2)
|
|
|
DDT
|
decimal(5,2)
|
|
|
DDT1
|
decimal(5,2)
|
|
|
PCB
|
decimal(5,2)
|
|
|
Hg1
|
decimal(5,2)
|
|
|
CH3Hg
|
decimal(5,2)
|
|
|
Cd
|
decimal(5,2)
|
|
|
Cu
|
decimal(5,2)
|
|
|
Zn
|
decimal(5,2)
|
|
|
Pb
|
decimal(5,2)
|
|
|
Se
|
decimal(5,2)
|
|
|
Hg2
|
decimal(5,2)
|
|
|
Creator:
|
dba
|
|
|