Aller au contenu
\( \definecolor{colordef}{RGB}{249,49,84} \definecolor{colorprop}{RGB}{18,102,241} \)
Éducation Nationale Française · ECGappliquées 2

Bases de données relationnelles et SQL

⌚ ~67 min ▢ 8 blocs ✓ 37 exercices ➣ Prérequis : Algorithmique et Python
Les administrations, les banques, les assurances et tout le secteur de la finance stockent d'énormes volumes de données structurées et les interrogent en permanence. Une base de données relationnelle organise ces données en tables reliées entre elles, et un langage dédié, le SQL (Structured Query Language), permet de les lire et de les mettre à jour.
Ce chapitre donne une description applicative des bases de données relationnelles. On met d'abord en place le modèle relationnel : ce que sont une relation, un attribut, un domaine, une clef primaire et une clef étrangère, et comment un schéma relationnel les consigne. On introduit ensuite le vocabulaire SQL et les opérateurs de comparaison, arithmétiques et logiques. Enfin chaque commande exigible est démontrée sur de petites tables d'exemple : CREATE TABLE, SELECT ... FROM, WHERE, INSERT INTO, DELETE FROM, UPDATE et la jointure INNER JOIN ... ON.
Tout au long du chapitre, deux tables servent de fil rouge : une table Customer et une table Product pour un petit fournisseur de desserts. Elles sont reliées par le produit commandé, ce qui permet d'illustrer comment joindre deux tables.
Signaler une erreur
I Le modèle relationnel
On commence par le vocabulaire qui décrit la donnée elle-même, indépendamment de tout langage de requête. On considère les éléments d'une même entité (des clients, des produits, des salles, ...) sur lesquels on enregistre plusieurs informations. Chaque information devient un attribut, l'ensemble des enregistrements devient une relation, et la relation est présentée sous la forme d'une table.
Signaler une erreur
I.1 Attributs, domaine, enregistrements, relation
Un attribut est une propriété enregistrée pour chaque élément de l'entité ; son domaine est l'ensemble des valeurs qu'il peut prendre, toutes du même type. Les types les plus courants ici sont les entiers, notés INTEGER, et les chaînes de caractères, notées TEXT. La liste des valeurs d'un élément sur tous les attributs est un enregistrement (une ligne) ; l'ensemble de tous les enregistrements est la relation, présentée sous la forme d'une table nommée.
Signaler une erreur
Définition — Attribut, domaine, descripteurs
Les propriétés dont on connaît la valeur pour chaque élément d'une entité, et qui forment les données, s'appellent les attributs ; leur ensemble forme les descripteurs de l'entité. L'ensemble des valeurs possibles d'un attribut est son domaine ; ces valeurs sont toutes du même type (ici, essentiellement INTEGER pour les entiers ou TEXT pour les chaînes de caractères).
Exemple
Un fournisseur de desserts enregistre, pour chaque client, un identifiant, un nom, la ville de livraison, le produit commandé et la quantité. Il y a cinq attributs : Id, Name, Town, Product, Quantity. Les domaines de Id et Quantity sont INTEGER ; les domaines de Name, Town et Product sont TEXT.
Exemple
Pour une bibliothèque, une entité livre pourrait porter les attributs Reference (TEXT), Title (TEXT), Year (INTEGER) et Available (TEXT, prenant les deux valeurs "yes" ou "no"). Le domaine de Year est l'ensemble des entiers ; le domaine de Available est l'ensemble à deux éléments \(\{\texttt{"yes"}, \texttt{"no"}\}\).
Définition — Enregistrement, relation, table
Pour une entité donnée, la liste des valeurs d'un élément sur tous ses attributs s'appelle un enregistrement (ou une ligne). L'ensemble des enregistrements de tous les éléments s'appelle la relation ; présentée en lignes et colonnes sous un nom, elle s'appelle une table. L'emplacement où une ligne rencontre une colonne (une case) est un champ.
En pratique les mots « relation » et « table », « attribut » et « colonne », « enregistrement » et « ligne » sont employés indifféremment ; nous ferons de même.
Signaler une erreur
Exemple
La relation Customer du fournisseur de desserts, avec quatre enregistrements, est la table :
Id Name Town Product Quantity
1 Adams Hull Far 35
2 Bell Leeds Crepe 30
3 Cole Hull KouignAmann 25
4 Dunn York Far 40

L'enregistrement du client d'identifiant \(1\) est la liste \((1, \texttt{Adams}, \texttt{Hull}, \texttt{Far}, 35)\). Cette relation a quatre enregistrements et cinq attributs.
Définition — Schéma de relation
Le schéma d'une relation est le descriptif de cette relation : un nom (souvent celui de l'entité), suivi de la liste de ses attributs avec leur domaine.
Exemple
Le schéma de la relation Customer ci-dessus est
Customer = ((Id : INTEGER), (Name : TEXT), (Town : TEXT), (Product : TEXT), (Quantity : INTEGER)).
Exemple
Une relation Room d'attributs Number, Capacity, Computer, Access a pour schéma
Room = ((Number : INTEGER), (Capacity : INTEGER), (Computer : TEXT), (Access : TEXT)).
Compétences à pratiquer
  • Lire une relation
I.2 Clef primaire et clef étrangère
Pour identifier chaque ligne sans ambiguïté, on distingue un attribut (ou un groupe minimal d'attributs) comme clef primaire. Lorsqu'une colonne d'une table référence la clef primaire d'une autre table, c'est une clef étrangère ; les clefs étrangères sont ce qui relie deux tables. L'ensemble, schémas plus clefs, est le schéma relationnel.
Signaler une erreur
Définition — Clef primaire
Une clef primaire d'une table est un attribut (ou un groupe minimal d'attributs) qui identifie de manière unique chaque ligne de la table : deux lignes distinctes doivent porter deux valeurs distinctes de la clef.
Exemple
Dans la table Customer, Id est une clef primaire : deux clients ont toujours deux identifiants distincts. L'attribut Town n'est pas une clef primaire, car Adams et Cole partagent la ville Hull.
Exemple
Choisir Name comme clef primaire de Customer est une mauvaise idée : si l'on ajoute plus tard un client de même nom, le nom ne distingue plus les deux lignes. C'est pourquoi on préfère un Id artificiel, incrémenté à chaque nouvelle ligne. Une clef primaire est de plus choisie aussi simple que possible.
Définition — Clef étrangère
Une clef étrangère d'une table est une colonne qui référence la clef primaire d'une autre table. Une fois déclarée, la base garantit la cohérence : la clef étrangère ne peut pas prendre une valeur absente de la colonne qu'elle référence.
Exemple
Ajoutons une table Product dont la clef primaire est Product :
Product Price Stock Egg
Crepe 2 200 yes
Far 4 160 yes
KouignAmann 6 140 no

La colonne Product de la table Customer référence cette clef primaire, donc Product est une clef étrangère de Customer ; les deux tables sont ainsi reliées.
Définition — Schéma relationnel
Un schéma relationnel est la donnée des schémas de toutes les tables ainsi que de leurs clefs primaires et étrangères. L'usage est de souligner les clefs primaires et de placer un dièse \(\#\) devant les clefs étrangères.
Exemple
Le schéma relationnel reliant Customer et Product, avec Id et Product comme clefs primaires et Product comme clef étrangère de Customer, est
Customer = ((\underline{Id} : INTEGER), (Name : TEXT), (Town : TEXT), (#Product : TEXT), (Quantity : INTEGER))
Product = ((\underline{Product} : TEXT), (Price : INTEGER), (Stock : INTEGER), (Egg : TEXT))
où les attributs soulignés Id et Product sont les clefs primaires.
Méthode — Lire et décrire un modèle relationnel
Face à une ou plusieurs tables, on procède dans cet ordre :
  1. lister les attributs de chaque table et donner le domaine (INTEGER ou TEXT) de chacun ;
  2. compter les enregistrements (les lignes) ;
  3. choisir une clef primaire : un attribut qui distingue les lignes, reste distinctif après des insertions, et est aussi simple que possible (introduire un Id artificiel si aucun attribut ne convient) ;
  4. repérer les clefs étrangères : une colonne d'une table qui reproduit la clef primaire d'une autre ;
  5. écrire le schéma relationnel, en soulignant les clefs primaires et en préfixant les clefs étrangères par \(\#\).
Compétences à pratiquer
  • Choisir des clefs et écrire un schéma relationnel
II Vocabulaire et opérateurs SQL
Un système de gestion de base de données lit et met à jour une base de données relationnelle au moyen de requêtes écrites en SQL. Avant de lister les commandes, on fixe les opérateurs qu'une requête peut utiliser pour construire une condition : opérateurs arithmétiques, de comparaison et logiques.
Signaler une erreur
II.1 Opérateurs arithmétiques, de comparaison et logiques
Par convention, les mots-clefs du SQL sont écrits en majuscules et le reste en minuscules ; le SQL lui-même ignore la casse. Les requêtes successives sont séparées par des points-virgules. Les valeurs de type texte sont entourées de guillemets droits. Une condition est une expression booléenne construite à partir des opérateurs ci-dessous.
Signaler une erreur
Définition — Opérateurs d'une requête
Une condition utilise les opérateurs suivants :
  • opérateurs arithmétiques \(+\), \(-\), \(*\) (addition, soustraction, multiplication) ;
  • opérateurs de comparaison \(=\), <> (différent de), \(<\), <=, \(>\), >= (attention : l'égalité utilise un seul =, non doublé) ;
  • opérateurs logiques AND, OR, NOT, qui combinent deux conditions, ou en nient une.
Exemple
Sur la table Customer, la condition Quantity > 30 est vraie pour les lignes d'Adams (\(35\)) et de Dunn (\(40\)). La condition Product = "Far" AND NOT Town = "York" n'est vraie que pour Adams (Dunn a commandé un Far mais habite York). La condition Quantity * 2 >= 70 est vraie pour les mêmes lignes que Quantity >= 35.
Exemple
Deux conditions peuvent décrire les mêmes lignes. Sur une table Room, (Width < 10) AND (Length < 10) et NOT ((Width >= 10) OR (Length >= 10)) sélectionnent exactement les mêmes lignes, par la règle selon laquelle la négation de « au moins une est \(\geq 10\) » est « les deux sont \(< 10\) ».
Compétences à pratiquer
  • Traduire une condition
III Créer une table
On crée une table avec la commande CREATE TABLE, qui liste les attributs avec leur domaine. La clef primaire est marquée par les mots-clefs PRIMARY KEY après l'attribut qui la définit, et une clef étrangère est déclarée par FOREIGN KEY ... REFERENCES.
Signaler une erreur
III.1 La commande CREATE TABLE
La commande nomme la table, puis liste, entre parenthèses et séparés par des virgules, chaque attribut suivi de son domaine. PRIMARY KEY est ajouté à l'attribut clef ; une clef étrangère est ajoutée sur une dernière ligne.
Signaler une erreur
Définition — CREATE TABLE
La requête
CREATE TABLE entity
(attribute1 domain1,
...
attributeN domainN)
crée la table entity avec les attributs et domaines listés. Le mot-clef PRIMARY KEY après un attribut le déclare clef primaire ; une clause FOREIGN KEY (a) REFERENCES other(b) déclare l'attribut a comme clef étrangère référençant l'attribut b de la table other.
Exemple
La table Customer est créée par
CREATE TABLE Customer
(Id INTEGER PRIMARY KEY,
Name TEXT,
Town TEXT,
Product TEXT,
Quantity INTEGER,
FOREIGN KEY (Product) REFERENCES Product(Product)). La table référencée Product doit déjà exister au moment où cette commande s'exécute, on crée donc Product en premier.
Exemple
Une table Product de clef primaire Product est créée par
CREATE TABLE Product
(Product TEXT PRIMARY KEY,
Price INTEGER,
Stock INTEGER,
Egg TEXT).
Compétences à pratiquer
  • Écrire une requête CREATE TABLE
IV Interroger une table : SELECT, FROM, WHERE
La requête de base lit des colonnes d'une table. SELECT ... FROM choisit les colonnes à afficher, et un WHERE facultatif ne garde que les lignes vérifiant une condition. Sélectionner des colonnes est une projection ; ne garder que certaines lignes est une sélection (ou restriction).
Signaler une erreur
IV.1 La commande SELECT ... FROM ... WHERE
SELECT a FROM entity affiche la colonne a de la table entity ; plusieurs colonnes sont séparées par des virgules, et SELECT * les affiche toutes. Ajouter WHERE condition ne garde que les lignes où la condition booléenne est vraie.
Signaler une erreur
Définition — SELECT ... FROM ... WHERE
La requête SELECT a1, ..., ak FROM entity affiche les colonnes a1, ..., ak de la table entity (une projection). La requête
SELECT a1, ..., ak FROM entity WHERE condition
affiche ces colonnes en ne gardant que les lignes où condition est vraie (une sélection, ou restriction). Écrire SELECT * garde toutes les colonnes.
Exemple
Sur la table Customer, SELECT Name, Town FROM Customer renvoie :
Name Town
Adams Hull
Bell Leeds
Cole Hull
Dunn York
Exemple
Pour obtenir les noms et villes des clients ayant commandé un Far :
SELECT Name, Town FROM Customer WHERE Product = "Far"
renvoie les lignes d'Adams (Hull) et de Dunn (York). En combinant les conditions,
SELECT Name FROM Customer WHERE (Product = "Far") AND (NOT Town = "York")
renvoie le seul nom Adams.
Méthode — Sélectionner des lignes et des colonnes
Pour répondre à « afficher les attributs ... pour les lignes telles que ... » :
  1. écrire les colonnes à afficher après SELECT (ou * pour toutes) ;
  2. écrire la table après FROM ;
  3. traduire la condition sur les lignes en un booléen après WHERE, à l'aide des opérateurs de comparaison et en combinant plusieurs clauses par AND, OR, NOT, avec des parenthèses pour rendre le groupement non ambigu ;
  4. se rappeler le simple = pour l'égalité et les guillemets droits autour des valeurs TEXT.
Compétences à pratiquer
  • Écrire une requête SELECT
V Modifier une table : INSERT, DELETE, UPDATE
Trois commandes modifient le contenu d'une table : INSERT INTO ajoute une ligne, DELETE FROM supprime des lignes, et UPDATE change la valeur d'un champ. Les deux dernières utilisent la même condition WHERE que SELECT pour désigner les lignes concernées.
Signaler une erreur
V.1 Insérer, supprimer et mettre à jour des lignes
INSERT INTO entity VALUES(...) ajoute une ligne dont les valeurs sont listées dans l'ordre des colonnes. DELETE FROM entity WHERE condition supprime les lignes vérifiant la condition. UPDATE entity SET attribute = value WHERE condition écrase un champ. Les valeurs sont données dans l'ordre des colonnes, avec des guillemets droits autour des valeurs TEXT.
Signaler une erreur
Définition — INSERT INTO, DELETE FROM, UPDATE
  • INSERT INTO entity VALUES(v1, v2, ...) insère une ligne dont les valeurs de champs sont v1, v2, ..., prises dans l'ordre des colonnes.
  • DELETE FROM entity WHERE condition supprime les lignes de entity vérifiant condition (sans WHERE, elle vide toute la table).
  • UPDATE entity SET attribute = value WHERE condition remplace, pour les lignes vérifiant condition, le champ de la colonne attribute par value.
Exemple
Ajout d'un cinquième client à Customer :
INSERT INTO Customer VALUES(5, "Evans", "Hull", "Crepe", 20).
Suppression des clients dont la commande ne dépasse pas \(30\) unités :
DELETE FROM Customer WHERE Quantity <= 30
supprime les lignes de Bell (\(30\)), Cole (\(25\)) et d'Evans (\(20\)) que l'on vient d'ajouter. Chaque démonstration agit ici sur les tables Customer et Product d'origine, de façon indépendante — elles ne s'enchaînent pas comme un script cumulatif, de sorte que les exemples de jointure qui suivent repartent des tables d'origine.
Exemple
Si le client Adams déplace la livraison à Leeds :
UPDATE Customer SET Town = "Leeds" WHERE Name = "Adams"
change le champ Town de la ligne Adams de Hull à Leeds, sans toucher aux autres champs.
Méthode — Modifier le contenu d'une table
On associe le verbe de l'énoncé à la commande :
  • « ajouter une ligne » \(\to\) INSERT INTO ... VALUES(...) avec les valeurs dans l'ordre des colonnes, guillemets droits sur le TEXT ;
  • « supprimer les lignes telles que ... » \(\to\) DELETE FROM ... WHERE condition ;
  • « changer la valeur de ... pour les lignes telles que ... » \(\to\) UPDATE ... SET attribute = value WHERE condition.
La condition WHERE s'écrit exactement comme pour un SELECT.
Compétences à pratiquer
  • Écrire une requête de modification
VI Joindre deux tables : INNER JOIN ... ON
Lorsque deux tables sont reliées par une colonne commune (une clef étrangère référençant une clef primaire), on peut faire apparaître les données des deux tables côte à côte dans un seul résultat en réalisant une jointure. La forme exigible est INNER JOIN ... ON, la condition après ON étant une égalité (ou une conjonction d'égalités) entre les colonnes de liaison. C'est la seule jointure du programme.
Signaler une erreur
VI.1 La commande INNER JOIN ... ON
La requête place côte à côte les lignes des deux tables, en ne gardant que les paires de lignes dont les champs de liaison sont égaux. Comme pour une seule table, on peut projeter sur des colonnes choisies et restreindre avec WHERE.
Signaler une erreur
Définition — INNER JOIN ... ON
Pour deux tables entity1 et entity2 partageant une valeur entre une colonne a de la première et une colonne b de la seconde, la requête
SELECT * FROM entity1
INNER JOIN entity2 ON entity1.a = entity2.b
affiche, côte à côte, les lignes de entity1 et de entity2 pour lesquelles les champs de la colonne a sont égaux à ceux de la colonne b (une jointure). On peut remplacer * par des colonnes choisies et ajouter une condition WHERE. La notation pointée entity1.a désigne la colonne a de entity1.
Exemple
Les colonnes Product de Customer et de Product portent des valeurs communes, donc
SELECT * FROM Customer
INNER JOIN Product ON Customer.Product = Product.Product
affiche chaque ligne client suivie de la ligne produit correspondante (prix, stock, oeuf). Lorsqu'un nom de colonne apparaît dans les deux tables, on le désigne en le préfixant du nom de sa table (Customer.Product, Product.Product) pour lever l'ambiguïté.
Exemple
Pour obtenir le prix du dessert commandé par Cole en une seule requête, on projette sur Price et on restreint avec WHERE :
SELECT Price FROM Customer
INNER JOIN Product ON Customer.Product = Product.Product
WHERE Name = "Cole".
Cole a commandé un KouignAmann, donc la requête renvoie le prix \(6\). L'intérêt de la jointure est de répondre en une seule requête plutôt que deux (d'abord le produit commandé par Cole, puis son prix).
Méthode — Réaliser une jointure
Lorsqu'une question nécessite des données réparties sur deux tables reliées :
  1. identifier les colonnes de liaison (une clef étrangère d'un côté, la clef primaire qu'elle référence de l'autre) ;
  2. écrire SELECT * FROM entity1 INNER JOIN entity2 ON entity1.a = entity2.b, en utilisant la notation pointée pour lever toute ambiguïté ;
  3. remplacer * par les colonnes réellement voulues (projection) et ajouter une clause WHERE si l'on ne veut que certaines lignes (restriction).
Au-delà de ces commandes exigibles, le SQL offre des opérateurs ensemblistes (UNION, INTERSECT, EXCEPT), des fonctions d'agrégation (MIN, MAX, SUM, AVG, COUNT) et des raffinements comme DISTINCT (pour éliminer les doublons) et ORDER BY (pour trier le résultat). On peut les utiliser par commodité mais ils ne sont pas exigibles, et nous ne les développons pas ici.
Signaler une erreur
Compétences à pratiquer
  • Écrire une requête de jointure
VII Pour aller plus loin
Compétences à pratiquer
  • Combiner les commandes sur un schéma complet