January 2007
DEVELOPMENT OF OPERATIONAL TOOLS FOR
MONITORING, LABORATORY AND
INFORMATION MANAGEMENT
Objective 2: Development of the ICPDR
Biological Database
Final Report
AUTHORS
PREPARED BY:
Environmental Institute, s.r.o.
AUTHORS:
Alex Hoebart
Environmental Institute, s.r.o.,
Okruzna 784/42, 972 41 Kos,
Slovak Republic
ei@ei.sk
short report title
page 1
TABLE OF CONTENTS
1.
Introduction ...................................................................................................... 3
2.
Database structure ............................................................................................. 3
3.
Database changes .............................................................................................. 5
4.
Data processing ................................................................................................. 5
5.
Status of taxa lists as of 29 January 2007.............................................................. 6
5.1.
Benthic invertebrates: ..................................................................................... 6
5.2.
Macrophytes: ................................................................................................. 6
5.3.
Phytobenthos:................................................................................................ 6
5.4.
Fish:............................................................................................................. 7
5.5.
Phytoplankton:............................................................................................... 7
5.6.
Zooplankton: ................................................................................................. 7
6.
User interface changes........................................................................................ 8
7.
General application features................................................................................. 9
8.
Suggested data collection process......................................................................... 9
8.1.
Database user roles ........................................................................................ 9
8.2.
Advantages of the suggested approach .............................................................10
9.
Recommendations .............................................................................................10
10.
Follow-up steps.................................................................................................10
Annexes:
List of sampling methods
Database documentation
UNDP/GEF DANUBE REGIONAL PROJECT
page 2
ABBREVIATIONS
DEFF
Data Exchange Format
DRB
Danube River Basin
DRP
Danube Regional Project
EG Expert
Group
EU European
Union
EU WFD
EU Water Framework Directive
GEF
Global Environment Facility
ICPDR
International Commission for the Protection of the Danube River
JDS
Joint Danube Survey
TNMN
Transnational Monitoring Network
UNDP
United Nations Development Programme
ENVIRONMENTAL INSTITUTE
DEVELOPMENT OF OPERATIONAL TOOLS FOR MONITORING, LABORATORY AND INFORMATION MANAGEMENT
Development of the ICPDR Biological Database Final Report
page 3
1. INTRODUCTION
The database development was based on the already available TNMN and JDS databases of the
ICPDR. The main aims of this development project was the upgrade of taxa coding to incorporate
current results of projects on the European level (AQEM, STAR, Euro-limpacs), improvement of
data input tools and enhancement of the query functionalities.
A survey among the members of the MA Expert Group (January 2006) had the following main
results:
>
The database should contain TNMN and Danube surveys' data.
>
The main target groups are water quality experts, members of other expert groups of the
ICPDR and biologists in general.
>
A common coding system should be established (as opposed to keeping national coding).
>
The application should facilitate the data collection process (and not only hold already
validated data).
>
Data input should be done via standard Excel sheets, which are uploaded and validated
during import.
>
The most important queries are
o to compare data over time,
o to query data of a specific location and compare data from two or several locations
o to review biological data together with chemical data.
>
The most useful format for exporting data is MS Excel spreadsheet.
2. DATABASE STRUCTURE
The diagram on the following page shows the Entity-Relationship model of the new database. The
boxes represent the entities (which are implemented as database tables). The lines show the
relations between the entities, where a dot marks the "many"-side of a one-to-many relation.
The following entities/tables were taken from TNMN/JDS databases and therefore are compatible
with the Data Exchange Format (DEFF):
> Country
>
Monpoint
>
Sampling method
>
Determinand
>
Analytical method
>
Sample
>
Analysis
Some of these tables have been extended and new tables have been added to reach the project
goals.
UNDP/GEF DANUBE REGIONAL PROJECT
page 4
ENVIRONMENTAL INSTITUTE
DEVELOPMENT OF OPERATIONAL TOOLS FOR MONITORING, LABORATORY AND INFORMATION MANAGEMENT
Development of the ICPDR Biological Database Final Report
page 5
3. DATABASE CHANGES
The database structure was adjusted to accommodate both chemical and biological data from
TNMN as well as other sources, like JDS. The changes can be summarized as follows:
>
Stations were introduced. A station consists of or groups several monitoring points. It has
attributes for the type (operational, surveillance, investigative, etc.) and location of the
station (section, water body). The stations table potentially serves as a point of connection
to the DRB GIS (template Surface Water Station) and WISE.
>
Station codes are previous monitoring point codes and other station codes. Each station
can have several codes, which is unique for a data source (or other context). This setup
allows (but does not force) several codes for the same station for different purposes (e.g.
TNMN, JDS1, JDS2, Aquaterra, EIONET, Yearbook).
>
The location in profile code list (L,M,R) has been extended to allow pooled samples (P),
suspended matter (S) and biological samples at additional locations (A-H).
>
Further minor changes in the monitoring points to allow border stations and to be
compatible with DRB GIS and WISE (coordinates in decimal degrees)
>
The sampling methods were extended with fields for biological sampling: sampling
method type, sampling instrument, mesh size, reference unit, reference quantity,
subsampling, subsampling type, remarks. Code lists for sampling instruments.
>
In the analytical methods, the field quantification limit was added.
>
The structure of the tables for the taxa lists have been completely changed, to allow a
hierarchical structure of biological quality elements higher taxonomic groups families
subfamilies genus subgenus and species. Additionally to the author of the species,
authors for first and second subunit (subspecies, variety, form, morphotype) can be
specified. Each taxa can have several codes from different origins (e.g. Ecoprof, AQEM,
EuroLimpacs) this setup allows also the use of national codes. Synonyms can also be
defined in this new structure.
>
Additional code lists for quantification units, etc. were developed.
>
The database was changed to include artifical primary and foreign keys instead of natural,
multi-field keys. This facilitates database administration and changes of coding in the
future.
4. DATA PROCESSING
>
Taxa lists have been compiled from various sources see the following chapter.
>
Templates in Excel format for upload of monitoring points, analytical methods and samples
with analysis or species lists were developed. They will be available online with the
database.
>
A list of sampling methods was drafted see annex.
UNDP/GEF DANUBE REGIONAL PROJECT
page 6
5. STATUS OF TAXA LISTS AS OF 29 JANUARY 2007
5.1.
Benthic invertebrates:
History:
The original taxa list was taken from the Austrian software Ecoprof (4425 taxa). This was amended
and coded during the AQEM project (taxa from 8 countries). It was further extended during the
STAR project (taxa from 14 countries) and during the Euro-limpacs project (22 countries) and
made available on the web (www.freshwaterecology.info).
Current status:
As of 2006-12-20 there are 12538 valid and 1479 synonym taxa available.
Next steps:
The list should be reviewed by DRB countries and additional taxa should be reported.
5.2.
Macrophytes:
History:
The taxa list in the JDS (63 taxa) was not coded. The Ecoprof taxa list has only about 40 taxa and
was therefore not used. The taxa list from the KOW database (midcc.at) has 433 taxa and was
extended for European purposes within the intercalibration exercise
(http://ec.europa.eu/environment/water/water-framework/objectives.html ).
This list is currently under final review and should be available when the project finished (May
2007). There is already an agreement with the intercalibration group to use their data.
Current status:
not available yet
Next steps:
Incorporate European taxa list when it is available.
5.3.
Phytobenthos:
History:
The taxa list from the Austrian software Ecoprof was amended and coded during the STAR project
(taxa from 14 countries). The taxa list was further extended during the Euro-limpacs project (22
countries) with the Omnidia database and is available on the web (www.freshwaterecology.info).
ENVIRONMENTAL INSTITUTE
DEVELOPMENT OF OPERATIONAL TOOLS FOR MONITORING, LABORATORY AND INFORMATION MANAGEMENT
Development of the ICPDR Biological Database Final Report
page 7
Current status:
As of 2006-11-07 there are 10271 valid taxa available.
Problem:
According to a recent meeting with phytobenthos experts, this list (merge from Ecoprof, Star and
Omnidia by Michel Coste) should not be used because of incompatibilities and differences within
the taxonomy of these three lists. According to the recommendation of the experts only one of
these three lists (preferable Omnidia) should be used. An official contact between ICPDR and
Michel Coste for the allowance to use these data should be established.
5.4.
Fish:
History:
The taxa list from the FAME project was amended during the Euro-limpacs project and is available
on the web (www.freshwaterecology.info).
Current status:
As of 2007-01-24 there are 351 valid taxa available.
Next steps:
The list should be reviewed by DRB countries and additional taxa should be reported. A responsible
person for the compilation is necessary.
5.5.
Phytoplankton:
History:
The JDS taxa list has 1737 taxa but is not coded. The Ecoprof list has only 480 taxa but is coded.
Current status:
not available yet
Next steps:
A decision should be drawn whether there is a better (more complete) taxa list available
elsewhere. If not the Ecoprof list could be used and be amended with valid JDS taxa. A responsible
person for the compilation is necessary.
5.6.
Zooplankton:
History:
The JDS taxa list has 797 taxa but is not coded. The new taxalist is compiled from Ecoprof
database.
UNDP/GEF DANUBE REGIONAL PROJECT
page 8
Current status:
As of 2006-12-20 there are 284 valid and 3 synonym taxa available.
Next steps:
The list should be reviewed by DRB countries and additional taxa should be reported. A responsible
person for the compilation is necessary.
6. USER INTERFACE CHANGES
The following features have been improved compared to the TNMN/JDS database user interfaces:
>
Taxa can be searched via different paths:
o Hierarchical selection from BQE down to Genus Species.
o String search by Genus Species or Author.
o Search by all available codes.
>
Latest web technology (AJAX) was used to improve usability:
o Hierarchical selection without page reloads.
o Search results are shown while typing in the string search function.
o Full result sets can be scrolled through without paging.
o Table columns can be hidden/shown and column widths can be adjusted.
o Details information can be accessed on the same screen as the search results.
o Advanced features can be easily shown and hidden.
>
Search parameters are shown on the same screen as the result set so they can be refined
easily.
>
Multiple sampling sites and determinants can be selected in an easy way.
>
Selection of month introduced to allow seasonal selections.
>
Interactive selection of matrix, instrument and method of sampling.
>
Results can be exported easily into comma-separated-values files (CSV) or native Excel97-
2003 format (XLS).
>
Statistics give an overview on the amount of available data in the database.
ENVIRONMENTAL INSTITUTE
DEVELOPMENT OF OPERATIONAL TOOLS FOR MONITORING, LABORATORY AND INFORMATION MANAGEMENT
Development of the ICPDR Biological Database Final Report
page 9
7. GENERAL APPLICATION FEATURES
>
Developed based on a widely used open-source framework (Drupal) which will also be used
for the ICPDR Information System Danubis in the future.
>
The application can be installed on any operating system supporting PHP and MySQL
(Windows, Linux, Mac OS X and others).
>
The application is also bundled with a lightweight server (incl. Apache, PHP and MySQL) so
it can run on any Windows PC without installation.
8. SUGGESTED DATA COLLECTION PROCESS
The application was developed to be used as a tool during the data collection process which can
take the following steps:
1. Data provider downloads code lists and templates1
2. ...prepares data according to code lists and templates
3. ...uploads filled templates and receives automatic validation result
a) if there are errors, data has to be corrected and uploaded again
b) if validation is successful, data is imported into the database
4. optional (not yet fully implemented): after import, new data is only accessible to
specific users ("data reviewer") who views/tests this data and has the options to
c) discard the new dataset
d) approve the new dataset, making it available to all users
5. data is available to all users for querying
This process can be organized decentrally (each country has a data provider) or centrally (one data
provider collects and compiles the data from all countries).
8.1. Database user roles
User role
Privilege
Viewer
can see approved data
Data provider
can upload new data for assigned data source ( + viewer's privileges)
Reviewer
can approve/discard data for assigned data source ( + viewer's privileges)
Administrator
can edit code lists ( + all other's privileges)
1 Templates define the input format (table matrix, fields) and have to be used for data input. Codelists define
allowed content of specific fields.
UNDP/GEF DANUBE REGIONAL PROJECT
page 10
8.2. Advantages of the suggested approach
>
Transparent update process: history of changes can be viewed
>
Clearly defined requirements for data: immediate response of validation
>
Clear separation of approved and unapproved data, possibility to collect and share
incomplete datasets
>
Flexible organization: review can be done by central institution or in countries
9. RECOMMENDATIONS
>
The taxa lists should be management centrally by appointed experts.
>
New taxa should be introduced in a coordinated manner with international relevant
databases and given a code from an existing system (AQEM, Star, ECOPROF, Euro-
limpacs, freshwater.info)
>
The database should be used and further improved and extended (e.g. ad-hoc data
input tool, hydromorphology) in the Joint Danube Survey 2007 and any other related
projects within the ICPDR.
10. FOLLOW-UP STEPS
>
The database will be installed on the ICPDR server and available at the address
http://www.icpdr.org/wq-db for registered users.
>
The data collection process should be tested during 2007 for one biological quality
element (Benthic-invertebrates). The experiences of this test should be used to define
more specifically the general data collection process for all data in the future.
>
The list of macrophytes will be imported once it is available.
>
All data from Bucharest Declaration, TNMN and Danube Surveys databases should be
imported into the new database.
>
All Danube countries should nominate national experts for the review of taxa lists and
data collection.
ENVIRONMENTAL INSTITUTE
Documentation for database ICPDR Water Quality DB
ICPDR Water Quality DB
Build 83
Date 2007-02-25 21:46:47
Page : 1
Documentation for database ICPDR Water Quality DB
1. Overview
Revisions
Version
Date
Description
First draft based on TNMN database extended with biological tables
0.1
2005-09-27
from Danube surveys database
0.2
working version
0.3
working version
0.4
2006-08-26
Draft including extended coding, sampling methods
0.5
2006-09-04
Added context to monpoint, changed dataset_log
0.6
2006-09-07
Added subgenus in table taxa
0.7
2006-09-23
Added additional authors for phytobenthos in table taxa
0.8
2006-11-07
Added table changelog, column lifeform in taxa
0.8.1
2006-12-20
Added subunit_code to station
0.9
2007-01-24
Substitute determin_id with quantifiation unit in species table
1.0
2007-02-25
Cosmetic changes
Other
Page : 2
Documentation for database ICPDR Water Quality DB
2. DataTypes
Constant Types
Name
Domain
DataD equiv
integer
boolean
datetime
text
Variable Types
Name
Size
Domain
DataD equiv
varchar
n/a
n/a
n/a
analytical method code
7
context
10
Field IN ('TNMN', 'JDS1', 'JDS2', 'ITR', 'ADS')
country code
2
dataset status
10
Field IN ('new', 'errors', 'validated', 'published')
determinand code
5
group code
1
Field IN ('B', 'F', 'H', 'Y', 'Z')
location code
1
Field IN ('L', 'M', 'R', 'P', 'A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'S')
log action
10
Field IN ('insert', 'update', 'delete')
long string
255
reference unit
4
sampling instrument
4
sampling method code
5
sampmeth type
2
short string
32
string
128
string code
24
subsamling type
3
Page : 3
Documentation for database ICPDR Water Quality DB
Variable Types
Name
Size
Domain
DataD equiv
taxa status
10
Field IN ('valid', 'redundant', 'deleted')
tiny string
8
decimal
n/a
n/a
n/a
coordinates
7,4
standard decimal
10,2
small integer
4
Page : 4
Documentation for database ICPDR Water Quality DB
3. ER Views
ER View
Description
The basic constructs of an entity relationshop (ER) model are:
1) Things (=entities) about which data is kept, represented by a box;
2) Relationships between those things, represented by lines
connecting the boxes; and
3) Characteristics of those things represented by attribute names
within the box.
ad 1) Round cornered boxes represent "dependent" entities -- those
whose unique identifier includes at least one relationship to another
entity. "Independent" entities, whose identifiers are not derived from
other entities, are shown with square corners.
The box is divided, with identifying attributes (the primary key) above
the division and non-identifying attributes below.
ad 2) A solid circle next to an entity means "one or more"
occurrences of that entity. Absence of a solid circle next to an entity
means that only one occurrence of that entity is involved ("one and only
one").
If a relationship is part of an entity's unique identifier, it is shown as a
solid line; if not, it is shown as a dashed line.
ad 3) Attributes are of the following types:
Primary key ("PK"): Every entity has exactly one primary key
displayed above the horizontal line in the entity box.
Key ("K"): Entities can have alternate keys that also uniquely identify
the entity.
Foreign key ("FK"): In addition to a relationship line from an entity, the
foreign key ("FK") that would implement the line in a relational database
design is shown as an attribute of that entity.
Not-Null Attribute ("N"): obligatory attribute
Attribute ("A"): optional attribute
The diagram uses the IDEF1X notation, for more information see
http://www.idef.com/IDEF1X.html.
Page : 5
Documentation for database ICPDR Water Quality DB
4. Overview of Entities and Relations
1. Country
Used tables
country
4.2. Monpoint
Used tables
monpoint
Page : 6
Documentation for database ICPDR Water Quality DB
Page : 7
Documentation for database ICPDR Water Quality DB
4.3. Station
Used tables
station
4.4. Station code
Used tables
station_code
4.5. Sampling Method
Used tables
sampmeth
Page : 8
Documentation for database ICPDR Water Quality DB
4.6. Sample
Used tables
sample
4.7. Determinand
Used tables
determin
Page : 9
Documentation for database ICPDR Water Quality DB
4.8. BQE
Used tables
bqe
4.9. HTG
Used tables
htg
4.10. Family
Used tables
family
4.11. Subfamily
Used tables
Page : 10
Documentation for database ICPDR Water Quality DB
subfamily
4.12. Taxa
Used tables
taxa
4.13. Taxa code
Used tables
taxa_code
Page : 11
Documentation for database ICPDR Water Quality DB
4.14. Species
Used tables
species
4.15. Analytical method
Used tables
anameth
4.16. Analysis
Used tables
analysis
Page : 12
Documentation for database ICPDR Water Quality DB
4.17. Dataset
Used tables
dataset
4.18. Log
Used tables
dataset_log
Page : 13
Documentation for database ICPDR Water Quality DB
5. ICPDR Water Quality DB
Introduction
This data model describes the database structure for the ICPDR Water Quality database, which is based on TNMN
(DEFF) and JDS databases and extended for biological data (species listing).
The (yellow) boxes (tables) are relevant for both chemical and biological data (general data: monitoring stations, samples
and methods).
The (gray) boxes analysis and analytical methods contain chemical as well as biological data (determinand code 8x.xx).
The (green) biological part is derived from the Joint Danube Survey (JDS) database.
The database model was changed to include artifical primary keys instead of natural, multi-field primary keys. This
facilitates database administration and changes of coding in the future.
Sub Folders
Name
Description
General data
These tables contain data of sampling sites, dates and methods.
Sampling data
Analysis data
These tables include quantitative (chemical and biological) data.
Taxa list
These tables include the taxa list of the biological quality elements.
Species data
1. General Note
This documentation is automatically generated from a database modelling tool called "Druid". A
version which is more easier to navigate will be available at http://www.icpdr.org/wq-db-docs.
Page : 14
Documentation for database ICPDR Water Quality DB
6. General data
These tables contain data of sampling sites, dates and methods.
1. country
This table defines countries.
See Also
monpoint
Field Summary
Name
Type
Description
country_code
country code
ISO 3166-alpha-2 code
country
string
country name
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
country_code
varchar(2)
x
-
-
x
DE
country
varchar(128)
-
x
-
x
6.2. station
This table defines monitoring stations.
Each station can have several monitoring points (at different positions (L,M,R,..), with different coordinates, etc).
Each station can have several codes depending on context (monitoring network, survey) (e.g. L2130 for TNMN,
DE_RV_BY11 for EIONET, D02 for the Yearbook)
See Also
station_code
monpoint
Field Summary
Name
Type
Description
id
integer
section_type
integer
Section types of the Danube (Roof Report 2004)
Page : 15
Documentation for database ICPDR Water Quality DB
waterbody_code
string code
European Water Body Code
subunit_code
string code
Code of the subunit for Water Management
operational
boolean
Operational monitoring
Surveillance monitoring of overall surface water status
surveillance
boolean
(SM1)
surveillance_sp
boolean
Surveillance monitoring of specific pressures (SM2)
investigative
boolean
Investigative monitoring
reference
boolean
Reference station
intercalibration
boolean
Is the site part of the intercalibration network
Is the site located in a protected area according to the
pa_habitat
boolean
Habitats Directive 92/43/EEC
Is the site located in a protected area according to the
pa_drinking
boolean
Drinking Water Directive 80/778/EEC
Is the site located in a protected area according to the Birds
pa_bird
boolean
Directive 79/409/EEC
Page : 16
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
section_type
integer
-
-
-
x
waterbody_code
varchar(24)
-
-
-
-
subunit_code
varchar(24)
-
-
-
-
operational
boolean
-
-
-
-
surveillance
boolean
-
-
-
-
surveillance_sp
boolean
-
-
-
-
investigative
boolean
-
-
-
-
reference
boolean
-
-
-
-
intercalibration
boolean
-
-
-
-
pa_habitat
boolean
-
-
-
-
pa_drinking
boolean
-
-
-
-
pa_bird
boolean
-
-
-
-
6.3. station_code
This tabes defines codes for stations.
Field Summary
Name
Type
Description
station_id
station(id)
The context of the station code (e.g. monitoring network,
context
context
survey)
The monitoring point code in the TNMN has the following
structure:
CXXX (C = letter, X = number)
The four-digit number reflects the river-km, in case of
station_code
string code
tributaries and arms the river-km of their mouth at the
Danube river (except at the Danube delta where there are
several arms).
This field has been extended to allow also other codes, e.g.
Page : 17
Documentation for database ICPDR Water Quality DB
European Surface Water Station Code.
Page : 18
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
station_id
integer
x
-
-
x
context
varchar(10)
x
-
x
x
station_code
varchar(24)
-
-
x
x
L2130
Foreign Keys
Name
Import
On Update
On Delete
station_id
station(id)
NO ACTION
NO ACTION
6.4. dataset
This table contains general information on datasets.
See Also
dataset_log
monpoint
sample
anameth
analysis
species
Field Summary
Name
Type
Description
vid
integer
Version ID
nid
integer
Node ID
dbstatus
dataset status
status related to database import and publication
context
context
refers to the monitoring network or survey
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
vid
integer
x
-
-
x
Page : 19
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
nid
integer
-
-
-
x
dbstatus
varchar(10)
-
-
-
x
context
varchar(10)
-
-
-
x
Page : 20
Documentation for database ICPDR Water Quality DB
6.5. dataset_log
Field Summary
Name
Type
Description
dataset_vid
dataset(vid)
uid
integer
User ID
log_date
datetime
Date and time of the log entry
log_level
small integer
Type of log entry
filename
short string
line
integer
col
short string
message
long string
Descriptive text
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
dataset_vid
integer
-
-
-
x
uid
integer
-
-
-
x
log_date
datetime
-
-
-
x
log_level
decimal(4)
-
-
-
x
filename
varchar(32)
-
-
-
x
line
integer
-
-
-
x
col
varchar(32)
-
-
-
-
message
varchar(255)
-
-
-
x
Foreign Keys
Name
Import
On Update
On Delete
dataset_vid
dataset(vid)
NO ACTION
NO ACTION
Page : 21
Documentation for database ICPDR Water Quality DB
6.6. codelist
Auxiliary table for all codelist values
Field Summary
Name
Type
Description
domain
short string
value
short string
meaning
string
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
domain
varchar(32)
x
-
x
x
value
varchar(32)
x
-
-
x
meaning
varchar(128)
-
-
x
x
6.7. changelog
Field Summary
Name
Type
Description
id
integer
uid
integer
tablename
short string
log_id
integer
log_date
datetime
log_action
log action
message
long string
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
Page : 22
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
uid
integer
-
-
-
x
tablename
varchar(32)
-
-
-
x
log_id
integer
-
-
-
x
log_date
datetime
-
-
-
x
log_action
varchar(10)
-
-
-
x
message
varchar(255)
-
-
-
-
Page : 23
Documentation for database ICPDR Water Quality DB
7. Sampling data
1. monpoint
This table has the characteristics of all monitoring points.
Changes:
Replaced monitoringpoint_code with station_id to allow multiple and longer codes depending on context.
Renamed location_in_profile to profile and extended code list.
Renamed village to location_name.
Added country_code_border for monitoring points at borders.
Added distance_to_mouth_sediments for end of range of sediment sample
See Also
sample
Field Summary
Name
Type
Description
id
integer
station_id
station(id)
The context of the station code (e.g. monitoring network,
context
context
survey)
location_code
location code
location_name
string
Name of the Sampling Site
country_code
country(country_code)
country_code_border
country(country_code)
x_coord
coordinates
Latitude (degrees)
y_coord
coordinates
Longitude (degrees)
The area in square km, from which water drains through the
catchment
standard decimal
station
Altitude. The mean surface water level in meters above sea
station_level
standard decimal
level
distance_to_mouth
standard decimal
River-km
distance_to_mouth_end
standard decimal
Page : 24
Documentation for database ICPDR Water Quality DB
Name of the river. A slash "/" in front indicates a tributary to
river
string
the Danube river
remark
long string
dataset_id
dataset(vid)
Page : 25
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
station_id
integer
-
-
x
x
context
varchar(10)
-
-
x
x
location_code
varchar(1)
-
-
x
x
M
location_name
varchar(128)
-
-
-
x
Jochenstein
country_code
varchar(2)
-
-
-
x
DE
country_code_border
varchar(2)
-
-
-
-
x_coord
decimal(7,4)
-
-
-
-
48.5611
y_coord
decimal(7,4)
-
-
-
-
13.4564
catchment
decimal(10,2)
-
-
-
-
77086
station_level
decimal(10,2)
-
-
-
-
290
distance_to_mouth
decimal(10,2)
-
-
-
-
2204
distance_to_mouth_end
decimal(10,2)
-
-
-
-
river
varchar(128)
-
-
-
-
Danube
in april 2002
remark
varchar(255)
-
-
-
-
changed longtitude,
altitude
dataset_id
integer
-
-
-
x
Foreign Keys
Name
Import
On Update
On Delete
station_id
station(id)
NO ACTION
NO ACTION
country_code
country(country_code)
NO ACTION
NO ACTION
country_code_border
country(country_code)
NO ACTION
NO ACTION
dataset_id
dataset(vid)
NO ACTION
NO ACTION
Page : 26
Documentation for database ICPDR Water Quality DB
7.2. sampmeth
This table defines the sampling methods
Changes:
Added fields for biological sampling methods: sampling method type, sampling instrument, mesh size, reference unit,
reference quantity, subsampling, subsampling type, remarks
See Also
sample
Field Summary
Name
Type
Description
id
integer
The Sampling Method Code is in the following format:
XX.WW (X, W = number)
First two symbols XX refers to sampled media/organism
group. Symbols WW are reserved for distinction between
different methods of one media.
There are the following possibilities of XX:
01 water
02 sediments
sampling_method_code
sampling method code
03 suspended matter
04 mussels
05 macrozoobenthos
06 phytobenthos
07 macrophytes
08 phytoplankton
09 zooplankton
10 pore water
11 fish
sampling_method_name
string
Name/description of the sampling method
reference_to_literature
string
Reference to standard, literature, etc.
sampling_method_type
sampmeth type
sampling_instrument
sampling instrument
mesh_size
small integer
reference_unit
reference unit
reference_quantity
standard decimal
Page : 27
Documentation for database ICPDR Water Quality DB
subsampling
boolean
only relevant for macro-invertebrates
subsampling_type
subsamling type
remarks
text
Page : 28
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
sampling_method_code
varchar(5)
-
x
-
x
01.01
sampling_method_name
varchar(128)
-
-
-
x
Bucket
reference_to_literature
varchar(128)
-
-
-
-
sampling_method_type
varchar(2)
-
-
-
x
sampling_instrument
varchar(4)
-
-
-
x
mesh_size
decimal(4)
-
-
-
-
reference_unit
varchar(4)
-
-
-
-
reference_quantity
decimal(10,2)
-
-
-
-
subsampling
boolean
-
-
-
-
subsampling_type
varchar(3)
-
-
-
-
remarks
text
-
-
-
-
7.3. sample
This table contains all samples taken.
Changes:
Removed time_of_sampling, the time is to be specified in the date_of_sampling field
See Also
analysis
species
Field Summary
Name
Type
Description
id
integer
monpoint_id
monpoint(id)
date_of_sampling
datetime
Date of sampling in the format DD.MM.YYYY
sampmeth_id
sampmeth(id)
dataset_id
dataset(vid)
Page : 29
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
monpoint_id
integer
-
-
x
x
date_of_sampling
datetime
-
-
x
x
18-JUN-02 00:00
sampmeth_id
integer
-
-
-
x
dataset_id
integer
-
-
-
x
Foreign Keys
Name
Import
On Update
On Delete
monpoint_id
monpoint(id)
NO ACTION
NO ACTION
sampmeth_id
sampmeth(id)
NO ACTION
NO ACTION
dataset_id
dataset(vid)
NO ACTION
NO ACTION
Page : 30
Documentation for database ICPDR Water Quality DB
8. Analysis data
These tables include quantitative (chemical and biological) data.
1. determingroup
Auxiliary table for names of determinand groups
Field Summary
Name
Type
Description
id
integer
group_code
determinand code
group_name
string
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
group_code
varchar(5)
-
x
-
x
81.00
Basic biological
group_name
varchar(128)
-
-
-
x
determinands
8.2. determin
This table defines all determinands.
See Also
anameth
Field Summary
Name
Type
Description
id
integer
determinand_code
determinand code
Format XX.XX (X number)
determinand_name
string
Name of the determinand.
unit
string
Page : 31
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
determinand_code
varchar(5)
-
x
-
x
81.10
Macrozoobenthos
determinand_name
varchar(128)
-
-
-
x
no. of taxa
unit
varchar(128)
-
-
-
-
8.3. anameth
This table defines all analytical methods used for analysis of the determinands.
Changes:
Added quantification limit
See Also
analysis
Field Summary
Name
Type
Description
id
integer
Analytical method code has the following structure:
LLM.SXX (L, M = letter; S,X = numbers)
First two symbols LL refers to laboratory in accordance to
table below, third symbol M to the media. The rest of the
code (symbols SXX) should be structured in order to
specify the type of the standard used for analysis in the
following way: S represents the code for the type of
standard and the last two symbols XX should be used to
differentiate one analytical method from the others (from 01
up to 99 is possible).
LABORATORY CODES (LL)
AA on-board laboratory
AB ARGE Gewässerschutz
AC CBR Frankfurt a.M.
AD GG&SBM Vienna
AE ÖFZ Seibersdorf
AF TZW Karlsruhe
Page : 32
Documentation for database ICPDR Water Quality DB
AG UBA Berlin
AH UBA Vienna
AI WRI Bratislava
AJ WRRC VITUKI
AK EI
AL ICIM
AM TUBP
AN IBISS
MEDIUM CODES (M)
W water
S sediments
P suspended matter (particles)
M mussels
B macrozoobenthos
F macrophytes
analytical_method_code
analytical method code
H phytobenthos
Y phytoplankton
Z zooplankton
O pore water
I fish
CODE FOR TYPE OF STANDARDS (S)
0 standard unspecified
1 ISO/EN standards
2 US EPA standards
3 ASTM standards
4 DIN standards
5 USGS standards
6-8 other standards which are of
international importance
9 completely national standards
determin_id
determin(id)
analytical_method_name
string
unit
string
detection_limit
standard decimal
quantification_limit
standard decimal
uncertainty
standard decimal
validation
boolean
reference_to_literature
string
dataset_id
dataset(vid)
Page : 33
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
analytical_method_code
varchar(7)
-
-
x
x
DEW.410
determin_id
integer
-
-
x
x
analytical_method_name
varchar(128)
-
-
-
-
Unspecified
unit
varchar(128)
-
-
-
-
detection_limit
decimal(10,2)
-
-
-
-
quantification_limit
decimal(10,2)
-
-
-
-
uncertainty
decimal(10,2)
-
-
-
-
validation
boolean
-
-
-
-
N
reference_to_literature
varchar(128)
-
-
-
-
DIN 38410 Part 2
dataset_id
integer
-
-
-
x
Foreign Keys
Name
Import
On Update
On Delete
determin_id
determin(id)
NO ACTION
NO ACTION
dataset_id
dataset(vid)
NO ACTION
NO ACTION
8.4. analysis
This table defines all analysis.
Changes:
Removed time_of_analysis, the time is to be specified in the date_of_analysis field
Field Summary
Name
Type
Description
id
integer
sample_id
sample(id)
anameth_id
anameth(id)
date_of_analysis
datetime
Date of analysis in the format DD.MM.YYYY
Page : 34
Documentation for database ICPDR Water Quality DB
value
standard decimal
Result of analysis
remark_code
string
analysis_text
string
dataset_id
dataset(vid)
Page : 35
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
sample_id
integer
-
-
x
x
anameth_id
integer
-
-
x
x
date_of_analysis
datetime
-
-
x
x
18-JUN-02 00:00
value
decimal(10,2)
-
-
-
x
93
remark_code
varchar(128)
-
-
-
-
analysis_text
varchar(128)
-
-
-
-
dataset_id
integer
-
-
-
x
Foreign Keys
Name
Import
On Update
On Delete
sample_id
sample(id)
NO ACTION
NO ACTION
anameth_id
anameth(id)
NO ACTION
NO ACTION
dataset_id
dataset(vid)
NO ACTION
NO ACTION
Page : 36
Documentation for database ICPDR Water Quality DB
9. Taxa list
These tables include the taxa list of the biological quality elements.
1. bqe
See Also
htg
Field Summary
Name
Type
Description
id
integer
bqe_name
short string
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
bqe_name
varchar(32)
-
-
-
x
Macrozoobenthos
9.2. htg
This table defines all higher taxonomic groups of taxa.
See Also
family
Field Summary
Name
Type
Description
id
integer
bqe_id
bqe(id)
htg_name
string
Sql Summary
Page : 37
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
bqe_id
integer
-
-
-
x
htg_name
varchar(128)
-
x
-
x
Page : 38
Documentation for database ICPDR Water Quality DB
Foreign Keys
Name
Import
On Update
On Delete
bqe_id
bqe(id)
NO ACTION
NO ACTION
9.3. family
This table defines all families of taxa
See Also
subfamily
Field Summary
Name
Type
Description
id
integer
htg_id
htg(id)
family_name
string
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
htg_id
integer
-
-
-
x
family_name
varchar(128)
-
x
-
x
CHIRONOMIDAE
Foreign Keys
Name
Import
On Update
On Delete
htg_id
htg(id)
NO ACTION
NO ACTION
9.4. subfamily
See Also
Page : 39
Documentation for database ICPDR Water Quality DB
taxa
Field Summary
Name
Type
Description
id
integer
family_id
family(id)
subfamily_name
string
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
family_id
integer
-
-
-
x
subfamily_name
varchar(128)
-
x
-
x
Foreign Keys
Name
Import
On Update
On Delete
family_id
family(id)
NO ACTION
NO ACTION
9.5. taxa
This table defines all taxa.
See Also
taxa
taxa_code
species
Field Summary
Name
Type
Description
id
integer
subfamily_id
subfamily(id)
Must be filled, except for synonyms
genus
string
Page : 40
Documentation for database ICPDR Water Quality DB
subgenus
string
species
string
author
string
Author of first subunit (subspecies, variety, form,
author_su1
string
morphotype)
Author of second subunit (subspecies, variety, form,
author_su2
string
morphotype)
Higher Taxonomic Unit (e.g. "sp." or "Gen.sp."), auxiliary
htu
boolean
column for quick selection
status
taxa status
syn_for_taxa_id
taxa(id)
Must be filled for synonyms
origin
string
origin database of this taxa
only for Phytobenthos, not used at the moment
lifeform
integer
1=benthic
2=planctic
Page : 41
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
subfamily_id
integer
-
-
x
-
genus
varchar(128)
-
-
x
x
Xenopelopia
subgenus
varchar(128)
-
-
x
-
species
varchar(128)
-
-
x
x
nigricans
author
varchar(128)
-
-
-
-
(FITTKAU, 1962)
author_su1
varchar(128)
-
-
-
-
author_su2
varchar(128)
-
-
-
-
htu
boolean
-
-
-
-
status
varchar(10)
-
-
-
x
syn_for_taxa_id
integer
-
-
-
-
origin
varchar(128)
-
-
-
-
lifeform
integer
-
-
-
-
Foreign Keys
Name
Import
On Update
On Delete
subfamily_id
subfamily(id)
NO ACTION
NO ACTION
syn_for_taxa_id
taxa(id)
NO ACTION
NO ACTION
9.6. taxa_code
This table is used to assign multiple codes to the same taxa.
Field Summary
Name
Type
Description
taxa_id
taxa(id)
code_type
short string
taxa_code
short string
Page : 42
Documentation for database ICPDR Water Quality DB
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
taxa_id
integer
x
-
-
x
123
code_type
varchar(32)
x
-
x
x
AQEM
taxa_code
varchar(32)
-
-
x
x
7175
Foreign Keys
Name
Import
On Update
On Delete
taxa_id
taxa(id)
NO ACTION
NO ACTION
Page : 43
Documentation for database ICPDR Water Quality DB
10. Species data
1. species
This table describes all sampled species
Field Summary
Name
Type
Description
id
integer
sample_id
sample(id)
date_of_determination
datetime
taxa_id
taxa(id)
quantification_unit
string code
The same codes are used as in the LL portion of the
laboratory_code
string code
Analytical Method Code
value
standard decimal
dataset_id
dataset(vid)
Sql Summary
Name
Type
PrKey
Unq
MUnq
NotN
Example
id
integer
x
-
-
x
sample_id
integer
-
-
x
x
date_of_determination
datetime
-
-
x
x
19-JUN-02
taxa_id
integer
-
-
x
x
7175
quantification_unit
varchar(24)
-
-
x
x
laboratory_code
varchar(24)
-
-
-
-
AA
value
decimal(10,2)
-
-
-
x
5
dataset_id
integer
-
-
-
x
Page : 44
Documentation for database ICPDR Water Quality DB
Foreign Keys
Name
Import
On Update
On Delete
sample_id
sample(id)
NO ACTION
NO ACTION
taxa_id
taxa(id)
NO ACTION
NO ACTION
dataset_id
dataset(vid)
NO ACTION
NO ACTION
Page : 45
Document Outline