Des tables qui se répondent : modèle relationnel et requêtes SQL

Tes notes en ligne, le catalogue du CDI, l'historique de ton appli de musique : derrière chacun se cache presque toujours une base de données relationnelle. On ne la parcourt pas à la main : on lui pose des questions précises dans un langage fait pour ça, SQL. Le logiciel qui les reçoit, le SGBD (système de gestion de bases de données), stocke les données, contrôle les accès et garde tout cohérent, même avec des milliers d'utilisateurs simultanés.

1. Le modèle relationnel

Les données sont rangées dans des relations, ou tables. Chaque colonne est un attribut, avec son domaine (entier, texte, date…) ; chaque ligne est un enregistrement (un n-uplet). Voici notre base, décrite par son schéma : artiste(id_artiste, nom, pays) et morceau(id_morceau, titre, duree, annee, id_artiste).

id_artiste	nom	pays
1	Nova	France
2	Kelyo	Belgique
3	Amani	Sénégal

id_morceau	titre	duree	annee	id_artiste
1	Lune rousse	214	2021	1
2	Minuit	187	2023	1
3	Sable	241	2022	3
4	Écho	198	2023	2
5	Plein phare	176	2024	3

La clé primaire identifie chaque ligne de façon unique : id_artiste pour artiste, id_morceau pour morceau. Un nom ne suffirait pas, deux artistes peuvent le partager. L'attribut id_artiste de morceau est une clé étrangère : il renvoie à la clé primaire de la table artiste. Ainsi, les informations d'un artiste ne sont écrites qu'une fois.

Un SGBD fait respecter trois contraintes d'intégrité : de domaine (une durée est un entier), d'entité (clé primaire unique et toujours renseignée) et de référence (une clé étrangère désigne une ligne qui existe).

2. Interroger avec SELECT

Une requête choisit les colonnes (SELECT), la table (FROM), les lignes (WHERE), puis trie le résultat (ORDER BY, avec ASC ou DESC).

SELECT titre, duree FROM morceau
WHERE annee >= 2023
ORDER BY duree DESC;

Résultat : Écho (198), Minuit (187), Plein phare (176). On combine les conditions avec AND et OR. DISTINCT supprime les doublons : SELECT DISTINCT annee FROM morceau; renvoie 4 lignes et non 5, car 2023 n'apparaît qu'une fois.

Les fonctions d'agrégation COUNT, SUM, AVG, MIN et MAX résument toute une colonne en une valeur :

SELECT COUNT(*), AVG(duree) FROM morceau WHERE id_artiste = 3;

Résultat : 2 morceaux, pour une durée moyenne de 208,5 secondes.

3. Croiser deux tables : la jointure

Pour afficher le nom de l'artiste à côté de chaque titre, on joint les tables en recollant les lignes dont les identifiants correspondent :

SELECT morceau.titre, artiste.nom
FROM morceau
JOIN artiste ON morceau.id_artiste = artiste.id_artiste
WHERE artiste.pays = 'Sénégal';

Résultat : Sable et Plein phare, tous deux d'Amani. Préfixer les attributs par le nom de la table lève toute ambiguïté : id_artiste existe dans les deux tables.

4. Modifier la base

INSERT INTO morceau VALUES (6, 'Aube', 205, 2024, 2);
UPDATE morceau SET duree = 180 WHERE id_morceau = 2;
DELETE FROM morceau WHERE annee < 2022;

La première ligne ajoute un morceau de Kelyo, la deuxième corrige une durée, la troisième supprime Lune rousse. Attention : sans WHERE, UPDATE modifie toutes les lignes et DELETE vide la table ! Le SGBD refuse un INSERT qui réutilise un id_morceau existant, ou qui désigne un artiste inexistant (SQLite, plus souple, ne vérifie les clés étrangères qu'après PRAGMA foreign_keys = ON; et contrôle peu les domaines).

Pour essayer, Python intègre SQLite (module sqlite3) :

import sqlite3

base = sqlite3.connect(":memory:") # une base en mémoire
base.executescript("""
CREATE TABLE artiste (id_artiste INTEGER PRIMARY KEY, nom TEXT, pays TEXT);
CREATE TABLE morceau (id_morceau INTEGER PRIMARY KEY, titre TEXT, duree INTEGER,
 annee INTEGER, id_artiste INTEGER REFERENCES artiste(id_artiste));
INSERT INTO artiste VALUES (1, 'Nova', 'France'), (2, 'Kelyo', 'Belgique'),
 (3, 'Amani', 'Sénégal');
INSERT INTO morceau VALUES (1, 'Lune rousse', 214, 2021, 1), (2, 'Minuit', 187, 2023, 1),
 (3, 'Sable', 241, 2022, 3), (4, 'Écho', 198, 2023, 2), (5, 'Plein phare', 176, 2024, 3);
""")
requete = "SELECT titre, duree FROM morceau WHERE annee >= 2023 ORDER BY duree DESC"
for ligne in base.execute(requete):
 print(ligne)

À retenir

• Une relation (table) a des attributs (colonnes), chacun avec son domaine, et des enregistrements (lignes).
• La clé primaire identifie chaque ligne ; une clé étrangère renvoie à la clé primaire d'une autre table.
• SELECT … FROM … WHERE … ORDER BY interroge ; DISTINCT, COUNT, SUM, AVG, MIN, MAX résument ; JOIN … ON croise deux tables.
• INSERT INTO, UPDATE … SET et DELETE FROM modifient la base : n'oublie jamais le WHERE !

★ Pour aller plus loin

Le modèle relationnel a été proposé en 1970 par Edgar F. Codd, chercheur chez IBM. SQL est né peu après chez IBM, sous le nom de SEQUEL. SQLite, écrit par D. Richard Hipp en 2000, range toute une base dans un seul fichier ; il est présent dans chaque smartphone.

Danger célèbre : l'injection SQL. Si un site colle tel quel le texte tapé par un visiteur dans sa requête, un pirate peut y glisser ses propres commandes. La parade : des requêtes paramétrées, avec des ? que le SGBD remplit lui-même. Hors programme, GROUP BY calcule un agrégat par groupe, par exemple le nombre de morceaux par artiste.

Sources pour creuser

• Numérique et sciences informatiques, Terminale : 24 leçons avec exercices corrigés, Thibaut Balabonski, Sylvain Conchon, Jean-Christophe Filliâtre et Kim Nguyen (Ellipses).
• Sur lumni.fr, cherche « base de données » ; sur YouTube, cherche « SQL » sur la chaîne Grafikart.fr.
• sqlite.org (en anglais) : la documentation officielle de SQLite.
• cnil.fr : ce que la loi impose à ceux qui stockent des données personnelles.