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_statistic_sta        tr_category_cat        tr_destination_dest        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

DATA AND REFERENTIALS

GENERAL STRUCTURE OF Metric DB

Series

  • developped to store recruitment data

  • t_series_ser

  • t_dataseries_das

  • group metrics

  • Individual metrics

Diagram for series

Sampling

  • DCF DATA (sampling, electrofishing, other data not series)

  • t_samplinginfo_sai

  • (No annual data)

  • group metrics

  • Individual metrics

Figure 2: Diagram for sampling

Proposed simplified data structure

schema vocab ref.vocab        ser_id (uuid)        ser_code (text)   stationDictionary ref.StationDictionary        sta_code (integer)          sta_activefromdate date        sta_activeuntildate date ts dat.series        ser_id        ...     ts->vocab 1:1 metts dat.metadata-series       ser_id         ...      metts->stationDictionary 1:1 metts->ts 1:1 ann dat.annual-series       ser_id         year           ...    ann->ts n:1 groupmetrics dat.groupmetrics       ser_id         year          mean size       ... groupmetrics->ann n:1 fish dat.fish       ser_id         fi_id          x          y          date       ... fish->ts n:1 indmetrics dat.individualmetrics       fi_id         metric_id         value   indmetrics->fish n:1 metrics ref.metric            metric_id (length, weight..)           indmetrics->metrics tseel dateel.series tseel->vocab 1:1 tseel->ts inherits mettseel dateel.metadata-series mettseel->metts inherits mettseel->tseel 1:1 anneel dat.annual-series anneel->ann inherits anneel->tseel n:1 groupmetricseel dateel.groupmetrics groupmetricseel->groupmetrics inherits metanneel metanneel groupmetricseel->metanneel n:1 indmetricseel dateel.indmetrics indmetricseel->indmetrics inherits fisheel dateel.fish indmetricseel->fisheel n:1 fisheel->fish inherits fisheel->tseel n:1 tsnas datnas.series tsnas->vocab 1:1 tsnas->ts inherits mettsnas datnas.metadata-series mettsnas->metts inherits mettsnas->tsnas 1:1 indmetricsnas datnas.indmetrics indmetricsnas->indmetrics inherits fishnas datnas.fish indmetricsnas->fishnas n:1 fishnas->fish inherits fishnas->tsnas n:1

Series Vocab

svc_id svc_code svc_description

Station

Definition HeaderRecord Station_Code Station_Country Station_Name Station_LongName Station_ActiveFromDate Station_ActiveUntilDate Station_ProgramGovernance Station_StationGovernance Station_PURPM

Station_Latitude Station_LatitudeRange Station_Longitude Station_LongitudeRange Station_Geometry Station_DataType Station_WLTYP Station_MSTAT Station_Notes Station_Deprecated

Relation

  • Definiton
  • HeaderRecord
  • Relation_Code
  • Relation_Country
  • Relation_Name
  • Relation_ActiveFromDate
  • Relation_RelatedCode
  • Relation_RelatedCountry
  • Relation_RelatedName
  • Relation_RelatedActiveFromDate
  • Relation_RelationType

Series

  • ser_svc_id
  • ser_code
  • ser_station_code
  • ser_name
  • ser_spe_code
  • ser_lfs_code
  • ser_are_code
  • ser_wkg_code
  • ser_ver_code
  • geom

ser_description ser_locationdescription ser_hty_code ser_gea_code ser_ccm_wso_id ser_distanceseakm ser_stocking ser_stockingcomment ser_annual_effort_uni_code ser_protocol ser_samplingstrategy ser_datelastupdate

Annual

  • san_svc_id
  • san_id
  • san_value
  • san_year
  • san_comment
  • san_effort
  • san_datelastupdate
  • san_qal_id
  • san_qal_comment
  • san_wkg_code
  • san_ver_code

Conclusion

flowchart LR
    A[Time series]:::initclass
    %% Short Path
    A --> B([Landings \n reconstruction]):::initclass
    B --> C[(Habitat \n  Electrofishing \n hydropower \n & dams)]:::initclass
    C --> D{Regional models}:::initclass
    D --> E{Global stock \n spatial model}:::initclass
    classDef initclass fill:#f96