INTRODUCTION

Why would we need another another database ?

  • WGEEL, WGBAST and WGNAS (+WGTRUTTA) have each developed their own databases.
  • These need to be maintained in the long term.

  • Integrating each database in ICES databases would take a lot of resources.

  • FAIR (Findability, Accessibility, Interoperability, Reusability).

  • Use of TAF (Transparent assessment Framework).

  • Use of DATSU (quality insurance and checking procedures for data call).

OBJECTIVES

We intend to build a common database structure (same referentials several DB to store)

  • Data from models at the scale of the management unit (WGEEL, WGNAS, WGBAST)

  • Continental habitat in support of ICES (e.g. landings in continental habitats) (WGEEL, WGNAS, WGBAST, WGTRUTTA)

  • Individual and group metrics, series of data

  • DB for dams/migration obstacles

  • DB of Electrofishing

General structure of the DB

DatabaseRelations ref ref RDBES RDBES RDBES->ref Model_DB Model_DB Model_DB->ref Metrics_Series Metrics_Series Metrics_Series->ref Metrics_Series->Model_DB TAF E_fishing E_fishing Regional_models Regional_models E_fishing->Regional_models model Dam_DB Dam_DB Dam_DB->Regional_models model Regional_models->Model_DB TAF

DATA AND REFERENTIALS

schema ref ref        tr_species_spe        tr_country_cou        tr_icworkinggroup_wkg        tr_version_ver        tr_metric_mtr        tr_category_cat        tr_destination_dest        tr_metadata_met        tr_area_are refeel refeel                               refeel->ref dateel dateel                               dateel->ref dateel->refeel refbast refbast                               refbast->ref datbast datbast                               datbast->ref datbast->refbast refnas refnas                               refnas->ref datnas datnas                               datnas->ref datnas->refnas reftrutta reftrutta                               reftrutta->ref dattrutta dattrutta                               dattrutta->ref dattrutta->reftrutta refwgeel refwgeel refwgeel->ref refwgeel->refeel salmoglob salmoglob salmoglob->ref salmoglob->refnas
Figure 1: Structure of the schema in diaspara. Dashed arrow indicate an import of data from existing

MODEL DB

The database structure could be simplified to the following structure

Report on DB build

Group and individual metric DB

  • DIASPARA WP2 data collection.

  • Based on our analysis of the three DB’s: a common database structure for all species is possible

  • This structure will work on the same referential structure.

Excel file for the DB

GENERAL STRUCTURE OF THE HABITAT DB

clusterA Hydroshed, EU-Hydro clusterB Country, regions, EMU, ICES divisions, Stock Units clusterC A Celtic Seas B Greater North Sea E ES B->E C Baltic D FR G Hydroshed | Topo | Habitat | Eel | Salmon | Trout E->G H EU-Hydro | Topo | Habitat | Eel | Salmon | Trout E->H F SE L National | Topo | Habitat | Eel | Salmon | Trout I Salmon River / Stock units J Fishing areas K EMU (Eel Managment Unit) K->A

Splitting per ICES Ecoregion