Le modèle relationnel
Partie 2 sur 4
3Les clés et l’intégrité
Une table seule est peu utile ; ce qui fait la puissance du modèle, ce sont les liens entre les tables. Ces liens ne sont pas des pointeurs cachés : ce sont des valeurs, écrites dans les cases, que l’on peut lire, comparer et copier. Pour cela, il faut savoir identifier une ligne : c’est le rôle des clés.
3.1La clé candidate et la clé primaire
Une clé candidate est un attribut, ou un groupe d’attributs, dont la valeur permet de désigner de façon unique un tuple, et qui est minimal (on ne peut retirer aucun attribut sans perdre cette propriété). Dans la table des voitures, la plaque d’immatriculation est une clé candidate : connaître la plaque suffit à connaître la voiture. Le couple (marque, modèle) n’en est pas une : plusieurs voitures peuvent être des Renault Clio.
Quand il y a plusieurs clés candidates, on en choisit une, la clé principale (ou clé primaire, en anglais primary key), qui sert de référence officielle. On la définit comme « l’attribut, ou l’ensemble d’attributs, permettant de désigner de façon unique un tuple ». Deux règles s’appliquent à une clé principale : sa valeur est unique (pas deux lignes avec la même plaque) et obligatoire (pas de valeur absente).
Une clé peut être naturelle (une donnée qui existe déjà dans le monde réel : la plaque, le numéro de sécurité sociale) ou artificielle (un numéro inventé par la base, le plus souvent un entier qui augmente à chaque ligne, ou un identifiant long généré au hasard). Les clés naturelles ont un piège : elles changent (une voiture peut changer de plaque, une personne de nom), et il est pénible de modifier la valeur partout où elle est copiée. On préfère donc souvent une clé artificielle, qui n’a aucun sens pour l’utilisateur et ne change jamais. Autre précaution : pour des données personnelles, un identifiant artificiel est plus discret qu’un numéro officiel (voir la partie « Le coin cyber » plus bas).
3.2La clé étrangère
Une clé étrangère est un attribut (ou un groupe d’attributs) d’une table dont les valeurs renvoient à la clé principale d’une autre table. Exemple : la table « commande » contient une colonne client_id dont chaque valeur est l’id d’un client de la table « client » (voir la figure 3). Pour trouver le nom du client d’une commande, on suit la valeur de client_id jusqu’à la ligne du client portant cet id.
Une rectification : on définit parfois la clé étrangère comme « une clé (donc un attribut permettant d’identifier de façon unique un tuple) faisant référence à une clé appartenant à une autre table ». Dans la table qui porte la clé étrangère, ses valeurs ne sont pas uniques : le même client peut passer dix commandes, et client_id se répète donc dix fois dans la table « commande ». Ce n’est pas une clé de cette table ; c’est une référence vers la clé d’une autre. Ce détail est la clé de la notion de lien « un à plusieurs » : un client, plusieurs commandes.
Pour les liens « plusieurs à plusieurs » (un étudiant suit plusieurs cours, un cours compte plusieurs étudiants), on crée une table de jonction qui contient deux clés étrangères, une vers chaque table : chaque ligne représente un couple (étudiant, cours).
3.3Les contraintes d’intégrité
L’intégrité d’une base est le fait que ses données sont cohérentes et conformes aux règles qu’on s’est données. On la déclare par des contraintes, que le système vérifie à chaque modification et qu’il refuse de violer. On en distingue trois sortes :
- l’intégrité de domaine : chaque valeur appartient au domaine de son attribut (un entier dans une colonne d’entiers, un âge compris entre 0 et 150 si l’on a posé une règle de vérification) ;
- l’intégrité d’entité : chaque tuple est identifiable, donc la clé principale est unique et non vide ;
- l’intégrité référentielle : chaque valeur d’une clé étrangère existe bien comme clé principale dans la table visée. Une commande ne peut pas viser un client inexistant, et l’on ne peut pas supprimer un client qui a encore des commandes, à moins d’avoir prévu ce qui doit arriver à ces commandes (les supprimer aussi, ou mettre leur référence à « absente »).
Pourquoi déclarer ces règles dans la base plutôt que dans chaque programme ? Parce que plusieurs programmes (le site web, l’application mobile, un script de nuit) écrivent dans la même base. Si la règle n’existe que dans l’un d’eux, un autre peut la violer. La base est le dernier rempart, le seul endroit où la règle est vérifiée quoi qu’il arrive.
3.4Les valeurs absentes : NULL
Dans la vie réelle, il manque parfois une information : la date de décès d’une personne vivante, le deuxième prénom d’une personne qui n’en a pas, le numéro de téléphone qu’on ne connaît pas. Les bases réelles utilisent pour cela une marque spéciale, NULL (« rien », « valeur absente »). Ce n’est ni zéro, ni un texte vide : c’est « on ne sait pas » ou « ça ne s’applique pas ».
NULL introduit une logique à trois valeurs (vrai, faux, inconnu). La comparaison « âge = 30 » est inconnue pour une ligne dont l’âge est NULL, ni vraie ni fausse, et la ligne n’est donc ni retenue ni rejetée par le test « âge = 30 » ni par son contraire « âge différent de 30 ». Codd lui-même a proposé NULL (ainsi que ses variantes) pour le modèle ; c’est l’une des parties les plus débattues, car elle rend certaines questions délicates. Voir le langage SQL. La bonne pratique est d’interdire NULL pour toutes les colonnes qui n’en ont pas besoin.
4L’algèbre relationnelle : les opérations de base
Une base qui ne ferait que ranger des tables serait un classeur. Ce qui la rend utile, c’est de pouvoir poser des questions. Codd a défini un petit ensemble d’opérations, l’algèbre relationnelle (« algèbre » comme en mathématiques : des opérations qui se combinent). Une propriété essentielle : chaque opération prend une ou deux relations et produit une relation. Le résultat est donc lui-même une table, sur laquelle on peut appliquer une nouvelle opération. Cette propriété, la fermeture, permet d’enchaîner des opérations comme on enchaîne des calculs : le résultat d’une question sert d’entrée à la suivante.
Une remarque d’histoire : l’article de 1970 pose le modèle ; la formulation de l’algèbre et la démonstration de ce qu’elle suffit à exprimer tout ce qu’une certaine logique (le calcul relationnel) permet d’exprimer datent de 1972 (article de Codd sur la « complétude relationnelle »).
On compte cinq opérations de base, « une opération de base étant définie par le fait qu’elle ne peut être réalisée par combinaison d’autres opérations ». Elles se rangent en deux catégories : deux opérations unaires (qui portent sur une table), trois opérations ensemblistes (qui combinent deux tables).
4.1La projection : garder des colonnes
La projection crée une table à partir d’une autre « en ne gardant que les colonnes spécifiées ». Elle se note avec la lettre grecque π (« pi ») : π nom, ville (client), ou, en notation texte, proj nom, ville (client). Sur la table de la figure 2, on obtient une table à deux colonnes. Le mot « projection » vient de la géométrie : on laisse tomber une dimension, comme l’ombre d’un objet sur un mur.
Un point que les débutants ratent : après projection, des lignes différentes peuvent devenir identiques. Si l’on ne garde que la colonne « ville » de la table de la figure 2, « Tours » apparaît deux fois. Comme une relation est un ensemble, l’algèbre élimine le doublon : on obtient {Tours, Lyon}. SQL, lui, ne le fait que si on le demande (mot DISTINCT).
4.2La restriction : garder des lignes
La restriction (on dit aussi sélection) garde « les lignes pour lesquelles une colonne vérifie certaines propriétés ». On la note avec la lettre σ (« sigma ») : σ ville = 'Tours' (client), et en texte select ville = Tours (client). La condition, qu’on appelle la qualification, peut comparer une colonne à une valeur (âge > 30) ou deux colonnes entre elles (prix_vente > prix_achat), et se combiner avec « et », « ou », « non ». Le résultat a les mêmes colonnes que la table de départ, mais moins de lignes.
Attention à une confusion de vocabulaire fréquente : le select de l’algèbre (garder des lignes) n’est pas le SELECT de SQL, qui est un mot plus large (partie 7). Dans le langage SQL, garder des lignes se fait avec la clause WHERE, et choisir des colonnes avec la liste qui suit SELECT.
Projection et restriction se combinent : π nom (σ ville = 'Tours' (client)) donne « les noms des clients de Tours ». On lit de l’intérieur vers l’extérieur : d’abord les lignes, puis la colonne.
4.3L’union : réunir deux tables
L’union de deux tables R1 et R2, notée R1 ∪ R2 ou union(R1, R2), est la table qui contient les tuples appartenant à l’une, à l’autre, ou aux deux. Une condition à rappeler : les deux tables doivent avoir le même schéma, c’est-à-dire les mêmes colonnes avec des domaines compatibles. On ne peut pas réunir une liste de clients et une liste de voitures. Les tuples présents dans les deux tables ne sont comptés qu’une fois.
Usage typique : une entreprise a un fichier de clients par agence ; l’union donne le fichier de tous les clients.
4.4La différence : soustraire
La différence R1 − R2 (en texte, minus(R1, R2)) est la table des tuples qui sont dans R1 mais pas dans R2. Mêmes exigences de schéma que l’union. Contrairement à l’union, l’ordre compte : R1 − R2 n’est pas R2 − R1. Usage typique : « les clients qui n’ont jamais commandé », en soustrayant l’ensemble des clients ayant commandé à l’ensemble de tous les clients. C’est la seule opération de base qui permet d’exprimer une négation (« ceux qui ne… pas »).
4.5Le produit cartésien : croiser
Le produit cartésien de deux tables R1 et R2, noté R1 × R2 (ou product(R1, R2), ou R1 * R2 en texte), associe chaque ligne de la première à chaque ligne de la seconde. On parle de « concaténation » des tuples, c’est-à-dire de mise bout à bout. Les deux tables n’ont pas besoin d’avoir le même schéma. Si R1 compte 3 lignes et 2 colonnes, et R2 4 lignes et 3 colonnes, le produit compte 3 × 4 = 12 lignes et 2 + 3 = 5 colonnes.
Le produit cartésien produit très vite de grandes tables : 1 000 clients croisés avec 1 000 produits donnent un million de lignes. Il est rarement utile seul ; son rôle est d’être l’ingrédient de la jointure (partie 5) : on croise, puis on ne garde que les couples qui ont un sens. Les bases de données réelles ne calculent jamais le produit complet dans ce cas : elles utilisent des méthodes bien plus économes (voir la partie 7).
Une subtilité technique : si les deux tables ont une colonne du même nom (par exemple id), le résultat aurait deux colonnes id. On les distingue en renommant l’une d’elles (opération de renommage, notée ρ, « rhô »), ou en préfixant par le nom de la table. Certains auteurs comptent le renommage comme une sixième opération de base ; c’est un détail d’écriture, qui ne change pas ce que l’on peut exprimer.
4.6Pourquoi cinq suffisent
Ces cinq opérations sont indépendantes : aucune ne peut être obtenue en combinant les quatre autres. Et elles suffisent à exprimer toutes les questions que l’on peut poser, au sens de « tout ce que l’on peut dire avec les connecteurs logiques et, ou, non, et avec les expressions il existe… tel que… ». Les mathématiciens disent que l’algèbre est relationnellement complète. C’est la garantie qu’un langage comme SQL, s’il sait faire ces opérations, ne sera jamais coincé par une question « normale ». Cette phrase demande une précaution : il existe des questions que l’algèbre de base ne sait pas poser, notamment celles qui demandent de parcourir une arborescence sur un nombre inconnu de niveaux (« tous les ancêtres d’une personne ») ou de calculer des totaux. On y revient dans les parties 6 et 7.
Pour s’exercer
Exercice 2 : taille du produit cartésien
La table « couleur » compte 8 lignes et la table « taille » 5. Combien de lignes et de colonnes a leur produit cartésien, si la première a 2 colonnes et la seconde 3 ?
Voir la réponse
8 × 5 = 40 lignes ; 2 + 3 = 5 colonnes. Chaque couleur est associée à chaque taille. Ce type de calcul sert à estimer le coût d’une jointure oubliée par mégarde : si l’on joint par erreur deux tables de 100 000 lignes sans condition, on obtient dix milliards de lignes.
Exercice 3 : écrire une requête en algèbre
Avec client(id, nom, ville) et commande(id, client_id, total), exprimez : « les noms des clients de Tours qui ont passé une commande de plus de 40 euros ».
Voir la réponse
π nom (σ ville = 'Tours' ET total > 40 (client ⋈ client.id = client_id commande)). On joint les deux tables sur la clé étrangère, on garde les lignes qui satisfont les deux conditions, puis on ne garde que la colonne « nom ». Variante plus efficace : restreindre chaque table avant de les joindre. En SQL : SELECT DISTINCT client.nom FROM client JOIN commande ON client.id = commande.client_id WHERE client.ville = 'Tours' AND commande.total > 40; (DISTINCT évite d’avoir deux fois le même nom si le client a passé plusieurs grosses commandes).
Exercice 4 : intersection et différence
R1 = {A, B, C, D} et R2 = {C, D, E}, deux relations de même schéma. Calculez R1 ∪ R2, R1 ∩ R2, R1 − R2, R2 − R1. Vérifiez la formule R1 ∩ R2 = R1 − (R1 − R2).
Voir la réponse
Union : {A, B, C, D, E}. Intersection : {C, D}. R1 − R2 = {A, B}. R2 − R1 = {E} (la différence n’est pas symétrique). Vérification : R1 − (R1 − R2) = {A, B, C, D} − {A, B} = {C, D}, qui est bien l’intersection.
Exercice 5 : les clients sans commande
Comment obtenir la liste des clients qui n’ont jamais commandé, d’abord avec les opérations de l’algèbre, ensuite avec une jointure externe ?
Voir la réponse
Avec l’algèbre : les identifiants de tous les clients, moins les identifiants qui apparaissent comme client_id dans « commande » : π id (client) − π client_id (commande), puis on récupère les noms par une jointure avec « client ». Avec une jointure externe : on joint « client » à « commande » en gardant tous les clients ; ceux sans commande ont NULL dans les colonnes de « commande » ; on retient ceux-là, par le test commande.id IS NULL. Les deux méthodes donnent le même résultat. La première s’appuie sur la différence, seule opération qui sait exprimer « ne… pas ».