Modèle relationnel et langage SQL

Le modèle relationnel

Une base de données relationnelle range l'information dans des tables. Chaque table ressemble à un tableau : les colonnes décrivent des caractéristiques, les lignes décrivent des objets.

Pourquoi id plutôt que nom ? Deux élèves peuvent s'appeler Martin. Un numéro attribué à chacun ne peut pas être partagé : il joue le rôle d'une plaque d'immatriculation.

Il faut distinguer la structure (le schéma : noms des attributs et domaines) du contenu (les lignes à un instant donné). Le contenu change tous les jours, la structure change rarement.

Interroger une table : SELECT

Le langage SQL (Structured Query Language) permet de questionner la base. Une requête de lecture a cette forme :

SELECT attributs

FROM table

WHERE condition

ORDER BY attribut;

• SELECT : les colonnes à afficher (* pour toutes) ;

• FROM : la table interrogée ;

• WHERE : la condition que doivent vérifier les lignes (=, <>, <, >=, AND, OR, NOT) ;

• ORDER BY : le tri (ASC croissant par défaut, DESC décroissant) ;

• DISTINCT après SELECT : supprime les doublons.

Les textes s'écrivent entre apostrophes 'Zola'. En SQL, les nombres décimaux s'écrivent avec un point : 7.50.

Prenons Livre(id, titre, auteur, annee, prix) :

Modifier les données : INSERT, UPDATE, DELETE

Trois instructions modifient le contenu de la base.

INSERT INTO Livre VALUES (5, 'Thérèse Raquin', 'Zola', 1867, 6.00);

UPDATE Livre SET prix = 6.50 WHERE id = 1;

DELETE FROM Livre WHERE annee < 1800;

• INSERT INTO … VALUES (…) ajoute une ligne (les valeurs suivent l'ordre des attributs) ;

• UPDATE … SET … WHERE … modifie des valeurs ;

• DELETE FROM … WHERE … supprime des lignes.

Le SGBD (système de gestion de bases de données) refuse d'insérer une ligne dont la clef primaire existe déjà : c'est lui qui garantit l'unicité.

Agréger : COUNT, SUM, AVG, GROUP BY

Parfois, on ne veut pas les lignes mais un résumé : combien ? quelle somme ? quelle moyenne ? Les fonctions d'agrégation COUNT, SUM, AVG, MIN, MAX résument toutes les lignes sélectionnées en une seule valeur.

SELECT COUNT(*) FROM Livre WHERE auteur = 'Zola';

SELECT AVG(prix) FROM Livre WHERE auteur = 'Zola';

Sur notre table de trois livres, ces requêtes donnent 2, puis 7,75 (la moyenne de 7,50 et 8,00). On peut agréger une expression : SUM(quantite * prix_unitaire).

Pour obtenir un résumé par groupe, on ajoute GROUP BY :

SELECT auteur, COUNT(*) FROM Livre GROUP BY auteur;

Les lignes sont regroupées par valeur de auteur, puis on agrège chaque groupe : on obtient Zola → 2 et Voltaire → 1.

Pour garder seulement certains groupes, on utilise HAVING, qui s'écrit après GROUP BY :

SELECT auteur, COUNT(*) FROM Livre GROUP BY auteur HAVING COUNT(*) >= 2;

Pour ne garder que les premières lignes d'un classement, on trie puis on limite : ORDER BY quantite DESC LIMIT 3 renvoie les trois plus grandes quantités.

Relier les tables : clef étrangère et jointure

Pour ne pas recopier les informations d'un adhérent à chaque emprunt, on les range dans une table Adherent et on renvoie vers elle depuis Emprunt.

Avec Adherent (1, Durand, Nantes), (2, Morel, Rezé) et Emprunt (1, 1, « Germinal »), (2, 1, « Nana »), (3, 2, « Candide »), on rassemble les informations avec JOIN :

SELECT Adherent.nom, Emprunt.titre

FROM Adherent

JOIN Emprunt ON Adherent.num = Emprunt.num_adherent;

Le ON indique quelles lignes s'associent : celles dont la clef étrangère est égale à la clef primaire. Résultat : (Durand, Germinal), (Durand, Nana), (Morel, Candide). Le préfixe Table.attribut lève toute ambiguïté.

On combine ensuite jointure et agrégation :

SELECT Adherent.num, COUNT(*)

FROM Adherent JOIN Emprunt ON Adherent.num = Emprunt.num_adherent

GROUP BY Adherent.num;

Ici : 1 → 2 et 2 → 1. Pour relier trois tables, on enchaîne deux jointures : … JOIN B ON … JOIN C ON ….

Pour trouver les lignes qui n'ont aucune correspondance, on utilise une sous-requête avec NOT IN :

SELECT nom FROM Adherent

WHERE num NOT IN (SELECT num_adherent FROM Emprunt);

Cette requête renvoie les adhérents qui n'ont jamais emprunté (aucun ici). Pour compter des valeurs différentes, on écrit COUNT(DISTINCT attribut).

Redondance, anomalies et décomposition

Reprenons la question du début : peut-on tout mettre dans une seule table ? Regardons Pret(num_pret, nom_adherent, ville_adherent, titre) :

La ville de Durand est écrite deux fois : c'est une redondance. Elle provoque trois anomalies :

• mise à jour : si Durand déménage, il faut modifier toutes ses lignes, sinon la base devient incohérente ;

• insertion : on ne peut pas enregistrer un nouvel adhérent tant qu'il n'a rien emprunté ;

• suppression : en supprimant le prêt n° 3, on perd aussi toute trace de Morel.

Ici : Adherent(num, nom, ville) et Pret(num_pret, num_adherent, titre), avec num_adherent clef étrangère vers Adherent. On retrouve la table d'origine par une jointure.

On crée les tables avec CREATE TABLE, en déclarant les clefs :

CREATE TABLE Adherent (

    num INTEGER PRIMARY KEY,

    nom TEXT,

    ville TEXT

);

CREATE TABLE Pret (

    num_pret INTEGER PRIMARY KEY,

    num_adherent INTEGER,

    titre TEXT,

    FOREIGN KEY (num_adherent) REFERENCES Adherent(num)

);

Pour une clef primaire composée de deux attributs, on écrit PRIMARY KEY (a, b) en fin de déclaration. Les domaines usuels sont INTEGER, REAL, TEXT.

L'essentiel

• Une relation (table) a des attributs (colonnes) munis d'un domaine ; la clef primaire identifie chaque ligne de façon unique ; une clef étrangère renvoie à la clef primaire d'une autre table.

• Lecture : SELECT … FROM … WHERE … ORDER BY … (DISTINCT supprime les doublons, LIMIT garde les premières lignes).

• Mise à jour : INSERT INTO, UPDATE … SET … WHERE, DELETE FROM … WHERE : ne jamais oublier le WHERE.

• Agrégation : COUNT, SUM, AVG, MIN, MAX ; GROUP BY regroupe, HAVING filtre les groupes (WHERE filtre les lignes).

• JOIN … ON clef_étrangère = clef_primaire rassemble les informations de plusieurs tables.

• Une table qui répète des informations souffre d'anomalies de mise à jour, d'insertion et de suppression : on la décompose, et CREATE TABLE déclare clefs primaires et étrangères.