Modèle relationnel et langage SQL

Tables, attributs et clefs

Une base de données relationnelle range l'information dans des relations, que l'on représente par des tables. Chaque ligne décrit un objet (un élève, un livre…), chaque colonne décrit une de ses caractéristiques.

Exemple de schéma, la clef primaire étant soulignée en pratique, ici écrite en premier : Adherent(num, nom, ville) et Emprunt(id, num_adherent, titre, date_emprunt).

La clef étrangère garantit la cohérence : on ne peut pas enregistrer un emprunt pour un numéro d'adhérent qui n'existe pas. C'est le SGBD (système de gestion de bases de données) qui le vérifie. Il assure aussi la persistance des données, la gestion des accès simultanés, l'efficacité des requêtes et la sécurisation des accès.

Interroger : SELECT, FROM, WHERE

SQL est un langage déclaratif : on décrit ce qu'on veut obtenir, pas comment le calculer. Une requête de sélection a la forme :

SELECT attributs

FROM table

WHERE condition

ORDER BY attribut;

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

(Dans les exemples de cette page, on écrit les nombres décimaux avec la virgule, comme en français : 7,50.)

Quelques outils utiles :

• Comparaisons : =, <>, <, <=, >, >= ; connecteurs AND, OR, NOT.

• ORDER BY prix trie par prix croissant ; ORDER BY prix DESC par prix décroissant.

• DISTINCT supprime les doublons : SELECT DISTINCT auteur FROM Livre;

• LIMIT 3 ne garde que les trois premières lignes du résultat.

• SELECT * renvoie toutes les colonnes.

Modifier : INSERT, UPDATE, DELETE

Les requêtes de mise à jour changent le contenu de la base.

INSERT INTO Livre (id, titre, auteur, annee, prix)

VALUES (5, 'Bel-Ami', 'Maupassant', 1885, 6.50);

UPDATE Livre SET prix = 5.50 WHERE id = 2;



DELETE FROM Livre WHERE annee < 1700;

Agréger et regrouper

Pour faire des calculs sur plusieurs lignes, on utilise des fonctions d'agrégation : COUNT(*) (nombre de lignes), SUM(x) (somme), AVG(x) (moyenne), MIN(x), MAX(x). Elles renvoient une seule valeur.

Table Vente(id, produit, magasin, quantite, prix_unitaire) :

SELECT SUM(quantite) FROM Vente; renvoie 95. On peut aussi calculer sur une expression : SUM(quantite * prix_unitaire).

GROUP BY forme des groupes de lignes qui ont la même valeur, et l'agrégation s'applique à chaque groupe.

WHERE filtre les lignes avant le regroupement. HAVING filtre les groupes après, donc c'est le seul à pouvoir porter sur un résultat d'agrégation.

SELECT magasin, SUM(quantite)

FROM Vente

GROUP BY magasin

HAVING SUM(quantite) > 50;

Cette requête ne renvoie que Lyon. Écrire WHERE SUM(quantite) > 50 provoquerait une erreur : à ce stade, les sommes ne sont pas encore calculées.

Pour obtenir les plus grandes valeurs, on trie en ordre décroissant puis on limite : ORDER BY quantite DESC LIMIT 3.

Relier les tables : les jointures

Les informations sont réparties dans plusieurs tables ; une jointure les recombine en suivant les clefs étrangères.

SELECT Adherent.nom, Emprunt.titre

FROM Adherent

JOIN Emprunt ON Adherent.num = Emprunt.num_adherent;

JOIN … ON … associe chaque ligne de Adherent aux lignes de Emprunt pour lesquelles la condition est vraie. On préfixe les attributs par le nom de la table (Adherent.nom) pour lever toute ambiguïté.

On peut combiner jointure, WHERE, AVG, HAVING… en suivant toujours le même ordre d'écriture : SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY.

Pour trouver les éléments sans correspondance (par exemple les adhérents qui n'ont rien emprunté), une jointure ne suffit pas, car elle ne garde que les lignes appariées. On utilise une sous-requête avec NOT IN :

SELECT nom FROM Adherent

WHERE num NOT IN (SELECT num_adherent FROM Emprunt);

Bien concevoir : redondance et anomalies

Reprenons notre médiathèque, avec une seule table :

La ville de Léa est écrite deux fois : c'est de la redondance. Elle provoque trois anomalies :

• mise à jour : si Léa 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 qui n'a encore rien emprunté ;

• suppression : si on supprime le seul prêt de Hugo, on perd aussi les informations sur Hugo.

Remède : séparer les informations en relations, chacune décrivant un seul type d'objet, reliées par des clefs étrangères :

• Adherent(num, nom, ville), clef primaire num ;

• Livre(code, titre), clef primaire code ;

• Pret(num_pret, num_adherent, code_livre), clef primaire num_pret, clefs étrangères num_adherent → Adherent(num) et code_livre → Livre(code).

Chaque information n'est plus écrite qu'une seule fois. Les tables se créent avec CREATE TABLE :

CREATE TABLE Adherent (

    num INTEGER PRIMARY KEY,

    nom TEXT,

    ville TEXT

);

CREATE TABLE Pret (

    num_pret INTEGER PRIMARY KEY,

    num_adherent INTEGER,

    code_livre INTEGER,

    FOREIGN KEY (num_adherent) REFERENCES Adherent(num),

    FOREIGN KEY (code_livre) REFERENCES Livre(code)

);

Quand la clef primaire est formée de deux attributs, on l'écrit PRIMARY KEY (a, b). Le SGBD refuse alors toute insertion qui ferait doublon sur le couple, ou qui référencerait une clef inexistante : c'est la contrainte d'intégrité référentielle.

Retour à notre médiathèque : quand Léa déménage, il suffit maintenant de modifier une seule ligne de Adherent, et tous ses prêts restent cohérents.

L'essentiel

• Une base relationnelle est un ensemble de relations (tables) ; leurs colonnes sont les attributs, chacun avec son domaine.

• La clef primaire identifie chaque enregistrement ; une clef étrangère référence la clef primaire d'une autre table et garantit la cohérence.

• Requête de sélection : SELECT … FROM … WHERE … ORDER BY … ; DISTINCT supprime les doublons.

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

• Agrégation : COUNT, SUM, AVG, MIN, MAX ; avec GROUP BY (hors programme) on calcule par groupe, et HAVING filtre les groupes.

• Jointure : JOIN … ON clef_étrangère = clef_primaire.

• Une table qui répète des informations provoque des anomalies de mise à jour, d'insertion et de suppression : on décompose en plusieurs relations reliées par des clefs.