Ch. 1 — Rappels : des bases de données au SQL

Cette page est le chapitre de rappels de la ressource R3.07. Elle reprend l'ensemble des notions sur lesquelles le reste du semestre s'appuie : ce qu'est une base de données, comment on la dessine avant de la construire, et comment on lui parle en SQL.

1. Le problème que résolvent les bases de données

Trois mots à ne pas confondre

Avant de parler de « base de données », il faut poser trois mots que l'on emploie souvent l'un pour l'autre à tort.

À ces trois mots s'ajoute un quatrième, celui qui fait le travail :

• SGBD (système de gestion de base de données) : le logiciel qui range, retrouve, vérifie et protège les données. PostgreSQL en est un. C'est lui qui refuse une donnée incohérente, et c'est lui qui répond aux requêtes.
• Système d'information (SI) : l'ensemble formé par les personnes, les procédures, les applications et les données d'une organisation. La base de données n'en est qu'un morceau — mais c'est le morceau qui survit le plus longtemps.

Ce qui se passe quand on n'en a pas

L'exemple le plus simple est un tableur. Une association note ses réservations dans une seule feuille de calcul : une ligne par réservation, avec le nom du joueur, le sport, le terrain et la date.

Le nom du joueur, le nom du terrain et le sport sont répétés à chaque ligne. Cette répétition s'appelle une redondance. Elle n'est pas seulement laide : elle rend le fichier fragile.

Une anomalie est un dysfonctionnement de la base causé par sa mauvaise organisation. On en distingue trois, et elles suffisent à justifier toute la théorie qui suit :

• Anomalie d'insertion : on ne peut pas ajouter une information seule, parce qu'il faut remplir d'autres colonnes au passage.
• Anomalie de mise à jour : la même information existe à plusieurs endroits, et il suffit d'en oublier un pour que la base se contredise.
• Anomalie de suppression : effacer une ligne fait disparaître une information qui n'avait rien à voir avec elle.

Toute la conception des bases relationnelles consiste à rendre ces trois anomalies impossibles par construction, et non pas à les corriger après coup.

2. Cinq familles de bases de données

Une base de données n'a pas toujours ressemblé à un tableau. Depuis les années 1960, cinq grandes familles se sont succédé, chacune avec sa façon de représenter les données et sa façon de les interroger. Le mot important est ce « et » : changer de représentation oblige à changer de langage.

Le vocabulaire de ce schéma mérite d'être posé :

3. Où se situe la base de données

Quand on dit « le site est en panne », cela ne veut rien dire tant qu'on ne sait pas quelle couche est en panne. Un site web se décompose toujours à peu près de la même façon.

• Client : le programme qui demande. Un navigateur, une application mobile.
• Serveur : la machine qui répond. Elle héberge l'application et le SGBD.
• Instance : une installation de SGBD en train de tourner sur une machine. « L'instance de psqlserv » désigne le PostgreSQL installé sur cette machine-là.
• Transaction : un groupe d'opérations qui réussit ou échoue en bloc. Si l'une échoue, tout est annulé, comme si rien ne s'était passé.

L'idée à retenir est celle du schéma : le web dynamique, c'est d'abord une base de données. Refaire TikTok, ce n'est pas choisir la couleur du fil d'actualité. C'est répondre à quatre questions, toutes des questions de données :

1. Comment stocker les amis, les « likes », les différents types de messages ?
2. Quelles requêtes permettent de récupérer les données voulues ?
3. Sur quoi s'appuyer pour calculer une recommandation ?
4. Comment calculer efficacement des statistiques ?

4. Modéliser avant de coder

Voici la règle de méthode la plus importante de la ressource : on ne commence jamais par écrire du SQL. On commence par décrire le problème, puis on descend par étapes.

Les trois niveaux, et leur vocabulaire :

Et les briques du niveau conceptuel :

• Entité : une « chose » dont on parle et qui a une existence propre. Un étudiant, un contrôle. On la dessine dans un rectangle.
• Attribut : une propriété d'une entité. Le nom d'un étudiant. On l'écrit à l'intérieur du rectangle.
• Identifiant : l'attribut (ou le groupe d'attributs) qui permet de reconnaître une entité sans ambiguïté. Deux étudiants peuvent s'appeler Paul Durand ; un seul a le numéro 42.
• Association : un lien entre deux entités. « Un contrôle porte sur une matière. »
• Cardinalité : le nombre de fois qu'une entité participe à une association. Elle s'écrit sous la forme 0,n, 1,1 ou 0,1, une valeur de chaque côté du trait.

5. Un exemple complet : la gestion des notes

Prenons un cas que tout le monde connaît : des étudiants, des contrôles, des matières, des notes.

Comment lire ce schéma, dans l'ordre :

1. Etudiant, Controle et Matiere sont des entités : trois rectangles, trois « choses » dont on parle.
2. Note n'est pas un rectangle ordinaire : c'est une association porteuse. Une note n'existe pas toute seule — elle est la note d'un étudiant à un contrôle. On la dessine donc attachée au trait, avec son propre attribut valeur.
3. Les cardinalités se lisent dans les deux sens. Entre Etudiant et Controle : « un étudiant a de 0 à n notes » (il peut n'avoir encore rien passé), et « un contrôle a de 0 à n notes » (personne ne l'a encore passé, ou tout le monde l'a passé).
4. Entre Controle et Matiere : 1,1 côté contrôle (« un contrôle porte sur exactement une matière ») et 0,n côté matière (« une matière peut être évaluée par plusieurs contrôles »).

Du MCD au MLD : trois règles suffisent

Le passage du conceptuel au logique n'est pas une affaire d'intuition : c'est un ensemble de règles mécaniques.

En appliquant ces trois règles à l'exemple, on obtient quatre tables.

Quelques points de vigilance sur ce résultat :

• La table Note a une clé primaire composée : le couple (eno, cno). Cela veut dire exactement ce qu'on veut dire : « un étudiant n'a qu'une seule note par contrôle ».
• Controle porte la clé étrangère mno, et non l'inverse. C'est la conséquence directe de la règle 2 : on met la clé du côté « 1 » dans la table du côté « n ».
• Etudiant et Matiere ne portent aucune clé étrangère : elles sont indépendantes. Ce sont les tables « racines » du modèle.

6. Pourquoi le relationnel a gagné

Quatre objectifs

Le modèle relationnel poursuit quatre buts, et tout le reste en découle :

1. Éviter les redondances. Une information ne doit exister qu'à un seul endroit.
2. Faciliter les mises à jour. Modifier une information doit se faire en un seul geste.
3. Permettre l'accès multi-utilisateurs. Plusieurs personnes travaillent en même temps sur les mêmes données, sans se marcher dessus.
4. Permettre d'exprimer de nombreuses contraintes. Le SGBD doit pouvoir refuser une donnée absurde — et non se contenter de l'enregistrer.

Redondance et formes normales

La normalisation est la démarche qui consiste à faire disparaître les redondances d'un modèle. Elle s'appuie sur la notion de dépendance fonctionnelle : on dit que A → B (« A détermine B ») si, connaissant A, on connaît forcément B. Dans Etudiant(eno, nom, prenom), on a eno → nom : connaître le numéro suffit à connaître le nom.

Les formes normales sont des paliers numérotés (1NF, 2NF, 3NF… jusqu'à la 7NF dans la littérature) qui indiquent à quel point un schéma est débarrassé de ses redondances. Les trois premières suffisent dans la quasi-totalité des cas pratiques :

7. SQL : quatre familles d'ordres

SQL (Structured Query Language) est le langage normalisé pour dialoguer avec une base relationnelle. Ce n'est pas un langage unique : c'est une famille, organisée en quatre groupes.

Cette ressource ne travaille que les deux premiers. Le reste viendra plus tard dans le cursus — mais il faut savoir qu'ils existent, ne serait-ce que pour comprendre un message d'erreur.

8. Le DDL : décrire la structure

Un ordre DDL ne touche pas aux données : il crée, modifie ou détruit des objets (tables, vues, index, séquences). Voici un CREATE TABLE typique, découpé en cinq mécanismes.

Les types de colonnes à connaître

Les contraintes

Une contrainte est une règle que le SGBD vérifie à chaque écriture, et qui lui permet de refuser une donnée incohérente.

Les actions référentielles

Une clé étrangère crée une dépendance : la ligne enfant ne peut pas exister sans la ligne parente. Que faire alors quand on modifie ou supprime le parent ? C'est la question à laquelle répondent les actions référentielles.

9. Le DML : lire et écrire les données

Les quatre ordres d'écriture

TRUNCATE est un ordre à part : il vide une table entière d'un coup, sans passer par le WHERE, et de façon beaucoup plus rapide qu'un DELETE sans condition. Il appartient au DDL parce qu'il réinitialise la structure, pas parce qu'il efface des lignes.

La requête SELECT complète

Voici une requête qui utilise toutes les clauses :

SELECT fno, nom
FROM fournisseurs LEFT JOIN commandes USING (fno)
GROUP BY fno, nom
HAVING AVG(qute) > (SELECT AVG(qute) FROM commandes)
ORDER BY fno DESC;

Elle se lit de la façon suivante : « prends les fournisseurs, rapproche-les de leurs commandes, regroupe le tout par fournisseur, ne garde que ceux dont la quantité moyenne dépasse la moyenne générale, et trie le résultat par numéro décroissant ».

Ce qui compte, c'est que l'ordre des clauses est imposé, et qu'il ne correspond pas à l'ordre d'exécution.

WHERE et HAVING ne filtrent pas la même chose

La distinction tient en une phrase : le WHERE compare des valeurs d'une ligne, le HAVING compare le résultat d'un agrégat.

Un agrégat est une fonction qui résume plusieurs lignes en une seule valeur :

SELECT fno, AVG(qute) FROM commandes WHERE AVG(qute) > 10 GROUP BY fno;

Les sous-requêtes

Une sous-requête est une requête placée à l'intérieur d'une autre. Il en existe deux formes, et les confondre est une source d'erreur :

• Une sous-requête scalaire renvoie une ligne et une colonne — une seule valeur. Elle s'utilise avec un opérateur de comparaison classique : =, >, <. Exemple : WHERE qute > (SELECT AVG(qute) FROM commandes).
• Une sous-requête ensembliste renvoie une colonne, mais autant de lignes qu'elle veut. Elle s'utilise avec un opérateur ensembliste : IN, NOT IN, EXISTS, ANY, ALL. Exemple : WHERE fno IN (SELECT fno FROM commandes).

Les jointures

Une jointure rapproche deux tables en les faisant correspondre sur une condition — le plus souvent une égalité entre une clé étrangère et la clé primaire qu'elle vise.

Deux écritures équivalentes, à connaître toutes les deux :

-- Avec ON : la condition est explicite, on peut comparer des colonnes de noms différents
SELECT * FROM fournisseurs f JOIN commandes c ON f.fno = c.fno;

-- Avec USING : plus court, quand la colonne porte le même nom des deux côtés
SELECT * FROM fournisseurs JOIN commandes USING (fno);

Le mot-clé devant JOIN change ce qu'on garde des lignes sans correspondance :

NULL mérite une définition : ce n'est pas zéro, ce n'est pas une chaîne vide. C'est l'absence de valeur — « on ne sait pas » ou « il n'y a rien ». C'est pour cela qu'on ne peut pas écrire = NULL en SQL : il faut écrire IS NULL ou IS NOT NULL.

Trier et limiter

ORDER BY trie le résultat final (ASC croissant par défaut, DESC décroissant). LIMIT n ne garde que les n premières lignes. Les deux se combinent pour répondre à des questions du type « les trois meilleures ventes ».

10. Les règles d'écriture à retenir

Ces cinq règles sont énoncées dans le support de cours. Elles méritent d'être développées, parce que chacune correspond à une erreur réellement fréquente.

11. Travailler avec psql

psql est le client en ligne de commande de PostgreSQL. On s'y connecte avec une commande toujours construite de la même façon :

psql -h psqlserv -U prenomnometu but2

Une fois connecté, l'invite change de forme : elle devient but2=>. On distingue alors deux familles de commandes :

Les méta-commandes les plus utilisées :

Le TP 3.01 détaille la prise en main complète : connexion, méta-commandes, écriture d'un script SQL dans un fichier, et exécution avec \i.

12. Où ce cours s'inscrit

Ce chapitre n'arrive pas par hasard : il est le troisième maillon d'un fil rouge qui traverse les cinq semestres du BUT.

Le plan annoncé pour la suite de la ressource :

1. Intégration de SQL dans les langages de programmation
2. L'API JDBC
3. Les systèmes transactionnels
4. Les architectures client-serveur
5. Les bases de Java EE
6. La sécurité des sites web
7. Le design pattern MVC pour le web
8. Les architectures REST

Autrement dit : après avoir appris à interroger une base, on apprendra à la faire dialoguer avec un programme — et c'est là que les rappels de cette page deviennent indispensables.

Auto-évaluation