Bases de données relationnelles et SQL
Modèle relationnel (relation, attribut, domaine, clé primaire, clé étrangère, schéma), contraintes d'intégrité, systèmes de gestion de bases de données, langage SQL : SELECT, WHERE, ORDER BY, fonctions d'agrégation, jointures, INSERT, UPDATE, DELETE.
1. Le modèle relationnel
Définition
Une base de données relationnelle est un ensemble de relations (tables). Chaque relation a des attributs (colonnes) prenant leurs valeurs dans un domaine (entier, texte, date). La clé primaire identifie chaque ligne de façon unique ; une clé étrangère fait référence à la clé primaire d'une autre table. Le schéma décrit les tables, leurs attributs et leurs clés.
Propriété · Contraintes d'intégrité
Contrainte de domaine (bon type), d'entité (clé primaire unique et non vide), de référence (une clé étrangère doit correspondre à une ligne existante). Le SGBD (SQLite, MySQL, PostgreSQL) les fait respecter et gère l'accès simultané de nombreux utilisateurs, la sécurité et la persistance des données.
2. Interroger avec SQL
Exemple · Requêtes de sélection
SELECT titre, annee FROM Livre WHERE annee >= 2000 ORDER BY annee DESC;
SELECT COUNT(*) FROM Livre;
SELECT AVG(annee) FROM Livre WHERE id_auteur = 3;
SELECT DISTINCT annee FROM Livre;Propriété · Jointure
Pour combiner deux tables, on les joint sur la clé étrangère :
SELECT Livre.titre, Auteur.nom
FROM Livre JOIN Auteur ON Livre.id_auteur = Auteur.id
WHERE Auteur.nom = 'Hugo';3. Modifier les données
Exemple · Insertion, mise à jour, suppression
INSERT INTO Auteur (id, nom, naissance) VALUES (7, 'Sand', 1804);
UPDATE Livre SET annee = 1862 WHERE id = 12;
DELETE FROM Livre WHERE annee < 1800;Attention · Prudence
Un UPDATE ou un DELETE sans WHERE s'applique à toutes les lignes. Supprimer un auteur encore référencé par des livres viole la contrainte de référence.
Activité · Interroger une base SQLite
sqlite3, base bibliotheque.db.SELECT Auteur.nom, COUNT(*) AS nb
FROM Livre JOIN Auteur ON Livre.id_auteur = Auteur.id
GROUP BY Auteur.nom ORDER BY nb DESC;⚠️ Une erreur dans ce cours ? Signalez-la
S'entraîner
Exercices corrigés
🎓 20 exercices corrigés et un quiz vous attendent dans ce chapitre.
Créez votre compte gratuit pour voir les corrections, faire les quiz et suivre votre progression.
Vocabulaire
★☆☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Dans la table Livre, citer les attributs, la clé primaire et la clé étrangère.
Clé primaire
★☆☆Pourquoi le nom d'un auteur n'est-il pas une bonne clé primaire ?
Domaine
★☆☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Donner un domaine adapté pour annee et pour titre.
Sélection simple
★☆☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui affiche tous les titres.
Condition
★☆☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui affiche les titres parus avant $1900$.
Tri
★☆☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui affiche les noms des auteurs par ordre alphabétique.
Comptage
★☆☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui compte les livres parus en $2010$.
Valeurs distinctes
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui affiche les années de parution sans doublon.
Jointure
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui affiche chaque titre avec le nom de son auteur.
Jointure avec condition
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui affiche les titres des livres d'auteurs nés après $1900$.
Agrégation
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui donne l'année du livre le plus ancien.
Insertion
★☆☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui ajoute le livre d'identifiant $20$, « Les Misérables », $1862$, auteur $3$.
Mise à jour
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Le livre d'identifiant $20$ a été enregistré avec l'année $1826$ au lieu de $1862$ : écrire la requête qui corrige son année.
Suppression dangereuse
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Que fait DELETE FROM Livre; ?
Intégrité de référence
★★★On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Pourquoi le SGBD refuse-t-il INSERT INTO Livre VALUES (21, 'X', 2020, 99) si aucun auteur n'a l'identifiant $99$ ?
Doublon de clé
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. La colonne id est la clé primaire de Livre. Que se passe-t-il si on insère un livre avec un id déjà utilisé ?
Regroupement
★★★On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Écrire la requête qui affiche le nombre de livres par année.
Redondance
★★☆On considère une base de données de bibliothèque formée de deux tables : Auteur(id, nom, naissance) et Livre(id, titre, annee, id_auteur) ; la colonne id_auteur d'un livre contient l'id de son auteur. Pourquoi ne pas stocker le nom de l'auteur directement dans chaque ligne de Livre ?
Rôle du SGBD
★☆☆Citer trois services rendus par un SGBD.
Vrai ou faux
★★☆a) Une clé primaire peut être vide. b) SQL est un langage déclaratif. c) ORDER BY trie les résultats.
Vérifier
Avez-vous bien compris ?
Répondez au quiz : la correction s'affiche immédiatement.