DB Schema

A proposed database design based on ICES-DOME Data

The page shows the database schema diagram along with table definitions based on the ICES-DOME dataset.

DB Schema Diagram

The proposed database design shown in the ER (Entity Relationship) diagram below contains nine tables including three meta (entity) tables, five variable (look-up or reference) tables, and one fact (measurement) table.

The columns with PK in the diagram indicate primary keys of the table, which guarantees unique identifications.

Project Table

The project table captures the unique combinations of monitoring programme, purpose, country, and responsible institute that generated the data.

Table Columns

Name Data Type PK FK NA Allowed Description
project_id INTEGER ✓ Primary Key. Auto-generated surrogate key.
project TEXT Monitoring programme code(s), e.g. CEMP or CEMP~NATL.
purpose TEXT ✓ Purpose code(s), e.g. T~S (trend and surveillance).
country TEXT ✓ Country name of the responsible institute.
institute TEXT ✓ Code for the reporting laboratory / institute (maps to RLABO in code_lookup).

Site Table

The site table holds the geographical location of each sampling station.

Table Columns

Name Data Type PK NA Allowed Description
site_id TEXT ✓ Primary Key. Auto-generated surrogate key.
station TEXT Station identifier code.
latitude REAL Latitude coordinate (decimal degrees).
longitude REAL Longitude coordinate (decimal degrees).
dist_to_coast REAL ✓ Distance to the coast.
country TEXT ✓ Country name.
country_code TEXT ✓ Country code (e.g., NO).
municipality TEXT ✓ Municipality name.
sea_name TEXT ✓ Name of the sea or coastal water body.

Sample Table

The sample table records one physical sampling event, linking a project, site, and date together.

Table Columns

Name Data Type PK FK NA Allowed Description
sample_id INTEGER ✓ Primary Key. Auto-generated surrogate key.
project_id INTEGER project (project_id) Foreign Key to the project table.
site_id INTEGER site (site_id) Foreign Key to the site table.
year INTEGER ✓ Year of sampling.
date TEXT ✓ Full date of sampling (text, format DD/MM/YYYY).
sample_type TEXT ✓ Sample type code, e.g. GC (grab/core) or DA (dab).
sample_type_description TEXT ✓ Sample type description.

Code Lookup Table

The code_lookup table is a common lookup resource that maps every coded field in the database to its human-readable description. It also handles multi-code values (e.g. T~S) by storing one row per individual component code.

Table Columns

Name Data Type PK FK NA Allowed Description
data_col TEXT ✓ Primary Key. Column name in the database that contains this code (e.g. purpose, qflag).
code_type TEXT ✓ Primary Key. Code type / domain from the ICES vocabulary (e.g. PURPM, QFLAG).
raw_code TEXT ✓ Primary Key. The original raw value as it appears in the data (e.g. T~S).
code TEXT ✓ Primary Key. Individual split code derived from raw_code (e.g. T or S).
description TEXT ✓ Human-readable description of the individual code.

Parameter Table

The parameter table defines each chemical or physical property that is measured, together with its parent parameter group.

Table Columns

Name Data Type PK FK NA Allowed Description
param TEXT ✓ Primary Key. ICES parameter code, e.g. HG, CD, GSMF2000.
param_description TEXT ✓ Full name or description of the parameter.
group_code TEXT ✓ Parameter group code, e.g. I-MET, P-PHY.
group_description TEXT ✓ Full name of the parameter group.

LLD Table

The lld table stores the Limit of Detection (LOD) and Limit of Quantification (LOQ) values associated with specific parameter measurements.

Table Columns

Name Data Type PK FK NA Allowed Description
lld_id INTEGER ✓ Primary Key. Auto-generated surrogate key.
param TEXT parameter (param) Foreign Key to the parameter table.
lod REAL ✓ Limit of Detection value.
loq REAL ✓ Limit of Quantification value.

Analysis Method Table

The analysis_method table catalogues the combination of laboratory, storage, pre-treatment, extraction, clean-up, and analytical technique used for each parameter measurement.

Table Columns

Name Data Type PK FK NA Allowed Description
analysis_id INTEGER ✓ Primary Key. Auto-generated surrogate key.
param TEXT parameter (param) ✓ Foreign Key to the parameter table.
labo TEXT ✓ Analysing laboratory code.
metst TEXT ✓ Sample storage method code.
metpt TEXT ✓ Pre-treatment method code.
metps TEXT ✓ Sub-sampling method code.
metcx TEXT ✓ Extraction / clean-up method code.
metoa TEXT ✓ Analytical method code, e.g. ICP-MS, AAS-GF.

Reference Table

The reference table holds bibliographic or standard-method references cited for specific analyses.

Table Columns

Name Data Type PK FK NA Allowed Description
ref_id INTEGER ✓ Primary Key. Auto-generated surrogate key.
ref TEXT Short reference code, e.g. EPA 6020 or ISO11885:2007.
ref_description TEXT ✓ Full title or description of the reference document.

Sediment Table

The sediment table stores the actual measurement values. Each row represents one parameter measurement from one physical sub-sample (sediment layer) within a sampling event.

Table Columns

Name Data Type PK FK NA Allowed Description
project_id INTEGER ✓ project (project_id) Primary Key. Foreign Key to the project table.
site_id INTEGER ✓ site (site_id) Primary Key. Foreign Key to the site table.
sample_id INTEGER ✓ sample (sample_id) Primary Key. Foreign Key to the sample table.
param TEXT ✓ parameter (param) Primary Key. Foreign Key to the parameter table.
sediment_no INTEGER ✓ Primary Key. Sequential sub-sample index within a sample–parameter combination.
depth_from REAL ✓ Upper depth of the sediment layer (m).
depth_to REAL ✓ Lower depth of the sediment layer (m).
value REAL ✓ Measured concentration or numerical result.
unit TEXT ✓ Unit of measurement, e.g. ug/kg or %.
basis TEXT ✓ Basis of measurement code, e.g. D (dry weight).
qflag TEXT ✓ Data quality flag code (may be multi-value, e.g. <).
vflag TEXT ✓ Validity flag code.
uncrt REAL ✓ Measurement uncertainty.
metcu TEXT ✓ Method of measurement uncertainty.
sub_no TEXT ✓ Sub-sample number within the physical core.
dcflag TEXT ✓ Data completeness flag code(s).
lld_id INTEGER lld (lld_id) ✓ Foreign Key to the lld table.
analysis_id INTEGER analysis_method (analysis_id) ✓ Foreign Key to the analysis_method table.
ref_id INTEGER reference (ref_id) ✓ Foreign Key to the reference table.