In BioDataScience-Course/BioDataScience2: A Series of Learnr Documents for Biological Data Science 2

BioDataScience2::learnr_setup()
SciViews::R()
library(DBI)
library(dm)

# Données inspirées Benrezkallah 2022
set.seed(11)

stations <-  dtx_rows(
  ~id_station,    ~country,     ~city,               ~station,        ~localisation,
     "stat_01", "Belgique",    "Mons",   "Abbaye Saint Denis", "50.4939°N 4.1893°E",
     "stat_02", "Belgique",    "Mons", "Parc du bois de Mons", "50.4484°N 3.9906°E", 
     "stat_03", "Belgique",    "Mons",      "Jardin Suspendu", "50.4469°N 3.9510°E"
)

set.seed(2673)
captures <- dtx(
  id_station      = c(rep("stat_01", 568), rep("stat_02", 360), rep("stat_03", 294)),
  id              = paste0("sp_", 1:1222),
  species         = sample(c(rep("Bombus pascorum", 615),
    rep("Terrestribombus sp", 295), rep("Osmia bicornis", 312))),
  author          = sample(c(rep("Benrezkallah",980), rep("Santerre", 242)))
)

sleft_join(captures, stations) %>.%
  sselect(., -id_station) ->
  bees

bees_db <- dbConnect(duckdb::duckdb())
dbWriteTable(bees_db, "stations", stations)
dbWriteTable(bees_db, "captures", captures)

bees_dm <- dm_from_con(bees_db, learn_keys = FALSE) %>.%
  dm_set_colors(., red = stations, orange = captures)
bees_dm %>.%
  dm_add_pk(., captures, id) %>.%
  dm_add_pk(., stations, id_station) ->
  bees_dm1
bees_dm2 <- dm_add_fk(bees_dm1, captures, id_station, stations)

BioDataScience2::learnr_banner()

BioDataScience2::learnr_server(input, output, session)

---

Objectifs

Une base de données est un ensemble structuré de données, avec un accès facile et rapide pour des applications logicielles et des utilisateurs autorisés. Elle peut être considérée comme un grand conteneur organisé pour stocker des informations de manière structurée.

Parmi les différents types de bases de données, nous allons nous intéresser aux bases de données relationnelles. Une base de données relationnelle est une collection de tables liées entre elles par des relations. Les bases de données relationnelles sont très répandues et utilisées dans de nombreux domaines, notamment dans la gestion des entreprises, les services financiers, l'administration publique et la recherche scientifique. Elles sont conçues pour gérer des quantités massives de données de manière fiable et cohérente. Ce tutoriel vous permettra de :

• créer une base de données relationnelle
• normaliser les données dans votre base
• réaliser le schéma d'une base de données
• effectuer une requête dans la base de données

Base de données et normalisation

Ajouter des tables dans une base de données n'est pas une étape complexe. Si l'on ne s'intéresse pas à la normalisation des données, les avantages d'une base de données restent très limités. La normalisation vise à optimiser la structure de ces données en évitant la redondance en définissant des liens entre les tables. La normalisation va permettre de diminuer la taille d'une base de données, de simplifier les requêtes, et d'optimiser le traitement des requêtes.

Le tableau de données présenté est inspiré des travaux de fin d'études de Benrezkallah Effet des facteurs pédologiques sur les communautés d'abeilles sauvages dans les potagers de la commune de Mons dont voici la citation.

Benrezkallah J. 2022. Effet des facteurs pédologiques sur les communautés d'abeilles sauvages dans les potagers de la commune de Mons. Mémoire de Master 2 en Sciences biologiques, Université de Mons, Mons, 127 pp.

Cette étude a nécessité la collecte de nombreuses données que le chercheur souhaite gérer sous la forme d'une base de données relationnelle. Aidez ce dernier dans la mise en place de cette base. Les dix premières lignes du jeu de données sont présentées ci-dessous.

head(bees, 10)

Les colonnes de ce tableau sont les suivantes :

• id : l'identifiant de l'insecte collecté
• species : le nom latin (genre et espèce) de l'insecte capturé
• author : la personne qui a collecté et identifié l'insecte
• country : le pays de la collecte
• city : la ville de la collecte
• station : le lieu précis de la collecte
• localisation : les coordonnées de la station (dans le système WGS84)

Utilisez la zone de code R ci-dessous pour explorer le jeu de données bees comme bon vous semble afin de répondre aux questions plus bas (utilisez le bouton Run Code uniquement).

quiz(
  question("Est-ce que le tableau présenté ci-dessus peut être ajouté comme une table dans une base de données relationnelle ?",
    answer("Oui", correct = TRUE),
    answer("Non"),
    allow_retry = TRUE,
    random_answer_order = TRUE,
    correct = "Ce tableau correspond bien à un format cas par variable. Il est donc possible de l'utiliser dans une base de données. Il n'est cependant pas normalisé."
    ),
  question("Sélectionnez parmi les variables suivantes celles présentant de la redondance optimisable par une normalisation niveau 3 (dépendance transitive).",
    answer("country", correct = TRUE, , message = "Il n'est pas utile de répliquer le nom de pays d'une station pour chaque collecte."),
    answer("localisation", correct = TRUE, message = "Il n'est pas utile de répliquer les coordonnées d'une station pour chaque collecte."),
    answer("id", message = "La variable 'id' est un identifiant unique pour chaque individu collecté. Cette variable n'est pas redondante."),
    allow_retry = TRUE,
    random_answer_order = TRUE)
  )

Base de données et schéma

Suite à votre exploration du tableau bees, vous avez pu observer que son schéma peut être optimisé. Deux tables sont réalisées pour assurer une normalisation au niveau 3 de la base. Vous pouvez observer qu'une colonne id_station a été ajoutée.

head(stations)

head(captures)

Créez une base de données au format DuckDB en mémoire et ajoutez vos deux tables que vous nommerez stations et captures, respectivement.

# Création de la base de données en mémoire
bees_db <- ___(duckdb::___)
bees_db
# Ajout des tables
___(___, "___", ___)
___(___, "___", ___)
# Noms des tables présentes dans bees_db
dbListTables(bees_db)

# Création de la base de données en mémoire
bees_db <- dbConnect(duckdb::___)
bees_db
# Ajout des tables
___(___, "___", ___)
___(___, "___", ___)
# Noms des tables présentes dans bees_db
dbListTables(bees_db)

#### ATTENTION: Hint suivant = solution !####

## Solution ##
# Création de la base de données en mémoire
bees_db <- dbConnect(duckdb::duckdb())
bees_db
# Ajout des tables
dbWriteTable(bees_db, "stations", stations)
dbWriteTable(bees_db, "captures", captures)
# Noms des tables présentes dans bees_db
dbListTables(bees_db)

grade_code("Vous venez de créer une base de données en mémoire avec deux tables.")

Il est à présent temps de définir des clés pour ces deux tables. Vous allez pour ce faire employer le package {dm}.

Créez un objet dm associé à votre base de données (bees_db) et nommez-le bees_dm. Indiquez ensuite que vous voulez utiliser la couleur rouge pour la table stations et la couleur orange pour la table captures. Enfin, visualisez le schéma de la base en l'état.

# Création d'un objet `dm`
bees_dm <- ___(___, learn_keys = FALSE)
bees_dm
# Ajout d'un couleur pour chaque table
bees_dm <- ___(bees_dm, red = ___, orange = ___)
# Graphique du schéma de la base
___(___, view_type = "all")

## Solution ##
# Création d'un objet `dm`
bees_dm <- dm_from_con(bees_db, learn_keys = FALSE)
bees_dm
# Ajout d'un couleur pour chaque table
bees_dm <- dm_set_colors(bees_dm, red = stations, orange = captures)
# Graphique du schéma de la base
dm_draw(bees_dm, view_type = "all")

grade_code("Vous avez un objet dm. La visualisation de l'objet vous permet d'observer vos deux tables. Actuellement, les clés primaires et les clés étrangères n'ont pas encore été définies.")

Afin de compléter le schéma de votre base de données (finaliser le niveau 1 de normalisation), définissez une clé primaire pour chaque table.

# Clés primaires potentielles
___(___, captures)
___(___, ____)
# Ajout des clés primaires
bees_dm %>.%
  ___(___, ___, ___) %>.%
  ___(___, ___, ___) ->
  bees_dm1
bees_dm1

# Clés primaires potentielles
dm_enum_pk_candidates(___, captures)
___(___, ___)
# Ajout des clés primaires
bees_dm %>.%
  dm_add_pk(., ___, ___) %>.%
  ___(., ___, ___) ->
  bees_dm1
bees_dm1

#### ATTENTION: Hint suivant = solution !####

## Solution ##
# Clés primaires potentielles
dm_enum_pk_candidates(bees_dm, captures)
dm_enum_pk_candidates(bees_dm, stations)
# Ajout des clés primaires
bees_dm %>.%
  dm_add_pk(., captures, id) %>.%
  dm_add_pk(., stations, id_station) ->
  bees_dm1
bees_dm1

grade_code("Votre objet bees_dm1 comprend à présent deux clés primaires, une pour chaque table.")

Définissez maintenant une clé étrangère qui fait le lien entre vos deux tables (captures et stations) selon une relation un à plusieurs en partant de l'objet bees_dm1 et placez le résultat dans bees_dm2. Enfin, visualisez le schéma de la base depuis bees_dm2.

# Clés étrangères potentielles
___(___, ___, ___)
# Ajout de la clé étrangère
bees_dm2 <- ___(___, ___, ___, ___)
# Graphique du schéma de la base
dm_draw(___, view_type = "all")

## Solution ##
# Clés étrangères potentielles
dm_enum_fk_candidates(bees_dm1, captures, stations)
# Ajout de la clé étrangère
bees_dm2 <- dm_add_fk(bees_dm1, captures, id_station, stations)
# Graphique du schéma de la base
dm_draw(bees_dm2, view_type = "all")

grade_code("Votre objet bees_dm2 comprend à présent deux clés primaires et une clé étrangère qui lie vos deux tables. La séparation des données en deux tables liées entre elles vous permet d'obtenir une base de données 3NF. Le schéma (data model) vous permet de bien visualiser la structure de votre base. Cet objet dm permet ensuite de réaliser des requêtes facilement.")

Requête sur la base de données

Répondez à la question suivante à l'aide d'un tableau résumé : Combien d'individus par espèce ont été collectés dans la station "Abbaye Saint Denis" par l'auteur "Benrezkallah" ?

Le schéma de la base de données est repris ci-dessous afin de vous aider à répondre à cette question.

dm_draw(bees_dm2, view_type = "all")

# Filtre 
bees_dm2 %>.%
  ___(___, ___ = ___, # filtre sur `stations`
      ___, ___ = ___) %>.% # filtre sur `captures`
  ___(., ___) -> # combinaison des tables
  bees_request
# Résumé des données
___ %>.% 
  ___(., ___) %>.%
  ___(.) ->
  request2

# Collecte de la requête
(___ <- collect_dtx(___))

# Filtre 
bees_dm2 %>.%
  dm_filter(., stations = station == "Abbaye Saint Denis", # filtre sur `stations`
               ___ = ___) %>.% # filtre sur `captures`
  dm_flatten_to_tbl(., captures) ->  # combinaison des tables
  bees_request
# Résumé des données par espèce
bees_request %>.% 
  ___(., ___) %>.%
  ___ ->
  request2

# Collecte de la requête
(bee_sum <- collect_dtx(___))

#### ATTENTION: Hint suivant = solution !####

## Solution ##
# Filtre 
bees_dm2 %>.%
  dm_filter(., stations = station == "Abbaye Saint Denis", # filtre sur `stations`
               captures = author == "Benrezkallah") %>.% # filtre sur `captures`
  dm_flatten_to_tbl(., captures) ->  # combinaison des tables
  bees_request

# Résumé des données
bees_request %>.% 
  group_by(., species) %>.%
  count(.) -> 
  request2

# Collecte de la requête
(bee_sum <- collect_dtx(request2))

grade_code("L'espèce la plus présente est *Bombus pascorum*. La mise en place d'un schéma avec {dm} a permit de réaliser une requête sur deux tables et de les combiner facilement. Vous avez ensuite résumé ces observations. Vous avez réalisé tout cela directement dans votre base de données et sans vous préoccuper de l'écriture de la requête. En effet {dbplyr} que vous avez utilisé ici vous permet d'écrire du code Tidyverse classique et il vous le traduit en requête SQL lorsque vous collectez le résultat avec `collect_dtx()`.")

Conclusion

Félicitation ! Vous avez créé une base de données relationnelle avec DuckDB. Ensuite, vous l'avez retravaillée en deux tables et défini les clés primaires et secondaires pour arriver à une normalisation des données au niveau 3 en limitant la redondance. Vous avez ensuite défini un schéma clair de la base avec le package {dm}. Enfin, vous avez réalisé une requête multitable avec les fonctions du package {dm} et de {dbplyr}.

Maintenant que vous avez compris les grands principes des bases de données relationnelles et que vous êtes capable d'écrire le code permettant de réaliser une requête, vous pouvez appliquer cette technique par vous-même.

question_text(
  "Laissez-nous vos impressions sur cet outil pédagogique",
  answer("", TRUE, message = "Pas de commentaires... C'est bien aussi."),
  incorrect = "Vos commentaires sont enregistrés.",
  placeholder = "Entrez vos commentaires ici...",
  allow_retry = TRUE
)

BioDataScience-Course/BioDataScience2 documentation built on April 12, 2025, 9:45 a.m.