Aller au contenu
Léo.
Tous les projets
DonnéesTerminé2024

Base de données qualité de l'air

Modélisation complète d'un système de suivi de la qualité de l'air.

CESI · Bases de données · Projet d'équipe

Support de soutenance
Schéma de base de données
Schéma de base de données
Commanditaire
Ministère de l'écologie, mise en situation
Équipe
4 rôles : chef de projet, analyste, développeur, testeur
Méthode
Merise, du dictionnaire au SQL
Durée
Deux mois, quatre jalons

Points clés

  • Chaîne Merise complète : dictionnaire, MCD, MLD, MPD, puis SQL
  • Neuf familles de données recensées avant d'écrire la moindre table
  • Requêtes analysées en arbres algébriques pour situer le coût
  • Base peuplée de données de test, puis interrogée et validée

Comment ça marche

  1. 1

    Le dictionnaire de données

    Avant toute table, on recense chaque information à conserver : son nom, son type, sa taille, sa portée et ce qu'elle signifie. Neuf grandes familles ont été identifiées. C'est l'étape la plus ingrate et celle qui évite le plus d'erreurs : deux personnes qui appellent « région » deux choses différentes s'en aperçoivent ici, pas au moment des jointures.

  2. 2

    Le modèle conceptuel

    Les données se regroupent en neuf entités (Région, Ville, Agence, Personnel, Capteur, Gaz, Secteur d'activité, Relevé, Rapport), chacune dotée d'un identifiant unique. Les associations qui les relient portent un verbe à l'infinitif, et surtout des cardinalités : un capteur appartient à une agence, une agence en compte plusieurs ; une région émet plusieurs gaz, et un même gaz est émis par plusieurs régions. C'est ici qu'on tranche, et ces décisions ne se rattrapent pas plus tard sans tout casser.

  3. 3

    Le modèle logique

    Le passage au relationnel suit des règles mécaniques : chaque entité devient une table, chaque identifiant une clé primaire, chaque association 1:N une clé étrangère du côté « plusieurs ». Les associations N:N, elles, deviennent une table à part entière. Cette mécanique est ce qui rend le modèle vérifiable plutôt qu'affaire de goût.

  4. 4

    Le modèle physique

    Le schéma devient exécutable : types concrets, longueurs, contraintes de clé primaire et étrangère, valeurs obligatoires. C'est le moment où l'on décide qu'un relevé porte une valeur en ppm et un horodatage, et où le SGBD se met à refuser les données incohérentes à notre place.

  5. 5

    Les requêtes

    La base est peuplée de données de test, puis interrogée. Chaque requête utile a été doublée d'un arbre algébrique (jointures, projections, sélections) pour voir où passe le coût. La démonstration ci-dessous porte sur celle qui cherche le secteur le plus polluant d'une région.

Le schéma de données

Le modèle logique livré : douze tables, dont trois nées d'associations plusieurs-à-plusieurs.

  • Region (Id_reg, Region)
  • Ville (Id_vil, Ville, Code_pos, #Id_reg)
  • Agence (Id_agence, Nom_age, Adr_age, #Id_vil)
  • Personnel (Id_per, Nom_per, Prenom_per, Date_nai_per, Date_job, Type_per, Taux_pro)
  • Secteur_d_activite (Id_sect_act, Secteur_act)
  • Gaz (Id_gaz, Type_gaz, Nom_gaz, #Id_sect_act)
  • Capteur (Id_cap, Capteur_dep, #Id_gaz, #Id_agence, #Id_per)
  • Releve (Id_rel, Valeur_ppm, Date_rel, #Id_gaz, #Id_cap, #Id_per, #Id_reg)
  • Rapport (Id_rap, Titre_rap, Date_rap, #Id_per)
  • Emettre (#Id_reg, #Id_gaz)table d'association
  • Heberger (#Id_reg, #Id_sect_act)table d'association
  • Alimenter (#Id_rel, #Id_rap)table d'association
  • IdClé primaire
  • #IdClé étrangère

Noms repris tels quels du livrable, abréviations comprises. Trois tables ne portent que deux clés étrangères et aucun identifiant propre : ce sont les associations plusieurs-à-plusieurs devenues des tables : une région émet plusieurs gaz et un gaz est émis par plusieurs régions, un relevé alimente plusieurs rapports et un rapport en agrège plusieurs.

Essayez vous-même

La même requête, écrite de deux façons. Le résultat est identique ; le nombre de lignes traversées pour y arriver, non.

SELECT   s.secteur_act, MAX(r.valeur_ppm)
FROM     Releve r
JOIN     Region g       ON r.Id_reg = g.Id_reg
JOIN     Heberger h     ON g.Id_reg = h.Id_reg
JOIN     Secteur s      ON h.Id_sect_act = s.Id_sect_act
WHERE    g.region = 'Île-de-France'
GROUP BY s.secteur_act;

Filtrer après avoir joint

ReleveRegionHebergerSecteur⋈⋈⋈σγ
  1. ⋈Releve ⋈ Region-
  2. ⋈⋈ Heberger-
  3. ⋈⋈ Secteur-
  4. σσ région = Île-de-France-
  5. γγ max(ppm) par secteur-

Lignes traversées : 0

Filtrer d'abord

ReleveRegionHebergerSecteur⋈⋈⋈σγ
  1. σσ région = Île-de-France-
  2. ⋈Releve ⋈ Region-
  3. ⋈⋈ Heberger-
  4. ⋈⋈ Secteur-
  5. γγ max(ppm) par secteur-

Lignes traversées : 0

Chaque arbre se lit de bas en haut : les tables sont aux feuilles, chaque nœud transforme ce qui arrive d'en dessous, et la réponse sort au sommet. Les deux ont exactement la même forme ; seule la sélection, en bleu, change de place : tout en haut à gauche, une fois les quatre tables assemblées ; collée à Region à droite, avant que la moindre jointure ne commence.

⋈
joint deux tables par leur clé commune
σ
filtre les lignes, le WHERE
γ
regroupe et calcule, le GROUP BY

Volumes issus d'un jeu de test déclaré dans le composant, pas d'une mesure sur la base réelle : l'objet de la démonstration est l'écart entre les deux ordres, pas sa valeur exacte. L'échelle des barres est logarithmique, sinon les petits nœuds seraient invisibles.

Le contexte

Mise en situation : à la suite d'un rapport du GIEC, le ministère de l'écologie confie à notre équipe la conception d'un système national de suivi de la qualité de l'air. Il doit centraliser les mesures remontées par plusieurs agences météorologiques et permettre de les exploiter, donc gérer les agences et leur personnel, les capteurs déployés sur le territoire, les relevés qu'ils produisent, et les rapports que l'on en tire.

Pourquoi ne pas commencer par écrire des tables

L'intérêt de ce projet n'est pas le SQL, qui s'apprend en quelques heures, mais ce qui vient avant. Une base de données est la seule partie d'un système qu'on ne peut pas refactoriser tranquillement : le jour où des données réelles y vivent, changer une cardinalité veut dire migrer. La méthode Merise force donc à trancher les questions structurantes pendant qu'elles ne coûtent encore rien.

  • Une agence est rattachée à une ville, et la ville à une région, plutôt que l'agence directement à la région. Un niveau de plus, mais c'est ce qui permet plus tard de compter par ville sans avoir à redécouper
  • Une région émet plusieurs gaz et un gaz vient de plusieurs régions : cette réciprocité ne se range dans aucune des deux tables, elle devient une table à part entière
  • Un relevé porte quatre clés étrangères : le gaz mesuré, le capteur, l'opérateur et la région. Chacune répond à une question qu'on aurait sinon dû reconstituer après coup
  • Ces décisions se prennent sur le papier. Une fois des données réelles en base, changer une cardinalité veut dire migrer

L'arbre algébrique, ou pourquoi l'ordre compte

Une requête SQL dit ce qu'on veut, pas comment l'obtenir. L'arbre algébrique, lui, montre le comment : les tables sont les feuilles, et chaque nœud est une opération : une jointure qui combine, une sélection qui filtre les lignes, une projection qui ne garde que certaines colonnes. Le résultat est identique quel que soit l'ordre, mais le volume manipulé au passage ne l'est pas du tout.

  • Filtrer après avoir joint oblige à construire un résultat intermédiaire énorme pour n'en garder qu'une fraction
  • Descendre la sélection au plus près des feuilles réduit ce que chaque jointure doit traiter
  • C'est un raisonnement sur le volume, indépendant du moteur : il vaut avant même de parler d'index
  • Les moteurs modernes savent souvent réordonner seuls ; savoir lire l'arbre reste ce qui permet de comprendre leur plan d'exécution

Ce que j'en retire

  • Un schéma se conçoit avant d'être écrit, parce qu'il se corrige mal une fois peuplé
  • Les cardinalités sont des décisions métier déguisées en notation
  • Lire un arbre algébrique donne l'intuition du coût d'une requête avant de la mesurer
  • Le travail en équipe avec des rôles séparés, et la traçabilité que ça impose
Tous les projets