The metric and stock DB with example of their format and use for WNAS, WGEEL and WGBAST

Cédric Briand, Jules Oliviéro, Jani Helminen

Refresh from the previous presentations

The DIADROMOUS DATABASE

Figure 1: Structure of the schema in diaspara. Dashed arrow indicate inheritance, pink or red colors indicate work in progress, blue color refers to the stock database, green color to the metric database.

The DIADROMOUS DATABASE

Figure 2: Structure of the schema in diaspara. Dashed arrow indicate inheritance.

So if we ignore the content of referential tables, we can layout the structure. Remember from yesterday presentation, the DIADROMOUS database is made of two main components, the metric DB and the stockDB. Both structures make use of the same referential tables, while data are provided in specific shcema to each working group.

The DB uses inheritance. An inherited table is a table containing all the columns from a mother table. A query in the mother table with show the content of daughter tables. In practise we have different shema with both referentials (dictionaries) and data. All the tables are created in the child schema (here refnas, datanas …). This ensures that the DB has the same structure. Additional columns exist in some of the daughter tables, e.g. the datnas.t_stock_sto has the same columns as dat. t_stock_sto but it has some more. Constraints foreign keys are created in the working group schemas, so they might differ from one shema to the rest The structure of the DB will need to rely on views (instead of inheritance) when transposed to SQL server.

The DIADROMOUS DATABASE

Figure 3: Referential only split in three parts.

Species referential

The DIADROMOUS DATABASE

Figure 4: Structure of the schema in diaspara. Dashed arrow indicate inheritance, pink or red colors indicate work in progress, blue color refers to the stock database, green color to the metric database.

STOCK db

STOCK db (WGNAS)

DONE
- Metadata table
- Stock table

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS)

DONE
- Metadata table
- Stock table

TODO
- Include the archive db
- Import in ICES DB
- Adapt shiny app
- (1) R package for common tools
- (2) Adapt shiny

STOCK db (WGEEL)

DONE (test)
- Metadata table
- Stock table

TODO
- Latest import from WGEEL

STOCK db (WBAST)

Change from several values column to one
Calendar data < year
Data source
Qualifying the data

STOCK db (WBAST)

Habitat structure (Illustrate in detail the use of recursive queries to get habitat)

Example: River Daugava

Example: River Daugava & Gulf of Riga

SELECT * FROM ref.get_parent_area(310, ‘WGBAST’);

Parents from Gulf of Riga

Example: Gulf of Riga

SELECT * FROM ref.get_parent_area(7025, ‘WGBAST’);

Parents from Gulf of Riga

STOCK db (WBAST)

DONE (test)
- Metadata table (fisheries)
- Stock table (fisheries)
- Trutta dataset - Exchange on the variables

TODO
- Young fish
- Mark recapture data
- ICES db coding
- Data integration (fisheries)
- Data integration (Young Fish)

METRIC db

METRIC db (WGEEL)

DONE (test)
- Referentials (metrics)
- Merging time series

TODO
- Latest import from WGEEL
- Work with data center

METRIC db (WGNAS)

TODO
- Import WP2 (adapt to salmon)
- There is an opportunity there

Yellow eel time series (source WGEEL 2022)

METRIC db (WGBAST)

METRIC db (WGTRUTTA)

source

The metric and stock DB with example of their format and use for WNAS, WGEEL and WGBAST

Refresh from the previous presentations

Refresh from the previous presentations

The DIADROMOUS DATABASE

The DIADROMOUS DATABASE

The DIADROMOUS DATABASE

The DIADROMOUS DATABASE

STOCK db

STOCK db (WGNAS)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS - shiny tools- PY Hernvann)

STOCK db (WGNAS)

STOCK db (WGEEL)

STOCK db (WGEEL)

STOCK db (WBAST)

STOCK db (WBAST)

STOCK db (WBAST)

STOCK db (WBAST)

STOCK db (WBAST)

Example: River Daugava

Example: River Daugava & Gulf of Riga

Example: Gulf of Riga

STOCK db (WBAST)

METRIC db

METRIC db (WGEEL)

METRIC db (WGNAS)

METRIC db (WGBAST)

METRIC db (WGTRUTTA)

Thank you !