Skip to content
Léo.
All projects
DataCompleted2024

Air quality database

Full data model for an air quality monitoring system.

CESI · Databases · Team project

Defence presentation
Database schema
Database schema
Client
Ministry of Ecology, simulated brief
Team
4 roles: project lead, analyst, developer, tester
Method
Merise, from dictionary to SQL
Duration
Two months, four milestones

Key points

  • Full Merise chain: dictionary, conceptual, logical and physical models, then SQL
  • Nine data families catalogued before a single table was written
  • Queries analysed as algebraic trees to locate their cost
  • Database populated with test data, then queried and validated

How it works

  1. 1

    The data dictionary

    Before any table, you catalogue every piece of information to keep: its name, type, size, scope and meaning. Nine broad families were identified. It is the most thankless stage and the one that prevents the most mistakes: two people calling "region" two different things find out here, not when writing joins.

  2. 2

    The conceptual model

    Data groups into nine entities (Region, City, Agency, Staff, Sensor, Gas, Business sector, Reading, Report), each with a unique identifier. The associations linking them carry an infinitive verb and, above all, cardinalities: a sensor belongs to one agency, an agency has several; a region emits several gases, and the same gas is emitted by several regions. This is where you decide, and those decisions cannot be walked back later without breaking everything.

  3. 3

    The logical model

    Moving to the relational world follows mechanical rules: each entity becomes a table, each identifier a primary key, each 1:N association a foreign key on the "many" side. N:N associations become tables in their own right. That mechanical quality is what makes the model checkable rather than a matter of taste.

  4. 4

    The physical model

    The schema becomes executable: concrete types, lengths, primary and foreign key constraints, mandatory values. This is when you decide a reading carries a ppm value and a timestamp, and when the database engine starts rejecting inconsistent data on your behalf.

  5. 5

    The queries

    The database is populated with test data, then queried. Each useful query was paired with an algebraic tree (joins, projections, selections) to see where the cost goes. The demo below covers the one finding the most polluting sector in a region.

The data schema

The delivered logical model: twelve tables, three of them born from many-to-many associations.

  • 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)join table
  • Heberger (#Id_reg, #Id_sect_act)join table
  • Alimenter (#Id_rel, #Id_rap)join table
  • IdPrimary key
  • #IdForeign key

Names taken verbatim from the deliverable, abbreviations included. Three tables carry only two foreign keys and no identifier of their own: these are the many-to-many associations turned into tables: a region emits several gases and a gas is emitted by several regions, a reading feeds several reports and a report aggregates several readings.

Try it yourself

The same query, written two ways. The result is identical; the number of rows traversed to get there is not.

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;

Filter after joining

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

Rows traversed: 0

Filter first

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

Rows traversed: 0

Each tree reads bottom to top: tables sit at the leaves, every node transforms what arrives from below, and the answer comes out at the summit. Both have exactly the same shape; only the selection, in blue, moves: at the very top on the left, once the four tables are assembled; attached to Region on the right, before any join begins.

⋈
joins two tables on their shared key
σ
filters rows, the WHERE
γ
groups and computes, the GROUP BY

Volumes come from a test dataset declared in the component, not from a measurement on the real database: the point is the gap between the two orderings, not its exact value. The bar scale is logarithmic, otherwise the small nodes would be invisible.

Context

A simulated brief: following an IPCC report, the Ministry of Ecology asks our team to design a national air quality monitoring system. It must centralise measurements sent in by several weather agencies and make them usable, so it has to handle the agencies and their staff, the sensors deployed across the country, the readings they produce, and the reports drawn from them.

Why not start by writing tables

The point of this project is not the SQL, which takes a few hours to learn, but everything before it. A database is the one part of a system you cannot refactor quietly: once real data lives in it, changing a cardinality means migrating. Merise therefore forces the structural questions to be settled while they still cost nothing.

  • An agency attaches to a city, and the city to a region, rather than the agency straight to the region. One level more, but it is what later allows counting by city without re-cutting anything
  • A region emits several gases and a gas comes from several regions: that reciprocity fits in neither table, so it becomes a table of its own
  • A reading carries four foreign keys: the gas measured, the sensor, the operator and the region. Each answers a question you would otherwise have to reconstruct afterwards
  • These decisions are made on paper. Once real data lives in the database, changing a cardinality means migrating

The algebraic tree, or why order matters

A SQL query says what you want, not how to get it. The algebraic tree shows the how: tables are the leaves, and each node is an operation: a join that combines, a selection that filters rows, a projection that keeps only some columns. The result is the same whatever the order, but the volume handled along the way is not.

  • Filtering after joining forces you to build a huge intermediate result and keep a fraction of it
  • Pushing the selection down towards the leaves shrinks what each join has to process
  • It is reasoning about volume, independent of the engine: it holds before indexes even come up
  • Modern engines often reorder on their own; reading the tree is what lets you understand their execution plan

What I took away

  • A schema is designed before it is written, because it corrects badly once populated
  • Cardinalities are business decisions dressed up as notation
  • Reading an algebraic tree gives an intuition of a query's cost before measuring it
  • Team work with separate roles, and the traceability that imposes
All projects