Séance 1 — Cadrage : pourquoi une base de données relationnelle ?

1. Mise en situation : la bibliothèque sans base de données

« On veut gérer les livres d’une bibliothèque : chaque livre a un titre, un auteur, et sait s’il est disponible ou emprunté. On veut pouvoir lister les livres, en ajouter, en supprimer. »

Étape A — la version « structures en mémoire ». Exemple en pseudo-Python orienté objet en utilisant des listes :

class Auteur:
    def __init__(self, id, prenom, nom):
        self.id, self.prenom, self.nom = id, prenom, nom

class Livre:
    def __init__(self, id, titre, auteur, disponible=True):
        self.id, self.titre, self.auteur, self.disponible = id, titre, auteur, disponible

auteurs = [Auteur(1, "George", "Orwell"), ...]
livres  = [Livre(1, "1984", auteurs[0], disponible=False), ...]

Questions :

1. Que se passe-t-il si le programme s’arrête ? → tout est perdu (problème de persistance).
2. Que se passe-t-il si deux instances du programme modifient livres en même temps ? → l’une écrase l’autre, ou on obtient un état incohérent (problème de concurrence).
3. Que se passe-t-il si on supprime auteurs[0] alors qu’un livre le référence ? → le livre pointe vers un objet qui n’existe plus, ou il faut écrire du code de vérification partout (problème d’intégrité).

Étape B — la version « fichiers ». : sérialiser dans un fichier CSV ou JSON.

id,titre,auteur_id,disponible
1,1984,1,0

Cela règle partiellement la persistance, mais aggrave les deux autres problèmes :

• concurrence : deux processus qui écrivent le fichier en même temps → corruption ou perte de la dernière écriture ;

• intégrité : rien n’empêche d’écrire auteur_id=42 alors qu’aucun auteur 42 n’existe ; c’est au code applicatif de tout vérifier, à chaque lecture, dans chaque programme ;

• en prime : recherche lente (parcours linéaire), pas de types, pas de transactions.

Conclusion de la mise en situation :

D’où l’intérêt de la SGBD.

2. Vocabulaire minimal

SGBD (Système de Gestion de Base de Données) : le logiciel qui stocke, sécurise, interroge et garantit l’intégrité des données. Exemples : MariaDB/MySQL, PostgreSQL, SQLite, Oracle. On précise : le SGBD n’est pas la base ; c’est le moteur.

Base de données : ici, l’ensemble nommé bibliotheque qui regroupe les tables et leur contenu, géré par le SGBD.

Schéma : la description de la structure — quelles tables, quelles colonnes, quels types, quelles contraintes. C’est le « plan » de la base, distinct des données. deux fichiers : schema.sql (le plan) et data.sql (le contenu).

Table : un ensemble de données du même type. Ici auteur et livre. Faites le parallèle avec la classe : une table ≈ la définition d’une classe ; une ligne ≈ une instance.

Ligne (enregistrement / tuple) : un élément de la table. Exemple concret :

(1, '1984', 1, 0)

Colonne (attribut / champ) : une propriété de la table, avec un type. CREATE TABLE livre :

id         INT          NOT NULL AUTO_INCREMENT
titre      VARCHAR(200) NOT NULL
auteur_id  INT          NOT NULL
disponible TINYINT(1)   NOT NULL DEFAULT 1

INT pour les identifiants et la clé étrangère, VARCHAR(200) pour les textes de longueur variable, TINYINT(1) comme booléen (convention MySQL/MariaDB à connaître).

Clé primaire : la colonne qui identifie de façon unique une ligne. Dans auteur c’est id, dans livre c’est id. On insiste : unique (deux auteurs ne peuvent pas avoir le même id) et non nulle (pas de ligne sans identifiant). On fait le lien avec un attribut d’identité dans un objet.

Clé étrangère : une colonne qui référence la clé primaire d’une autre table. Dans livre, auteur_id référence auteur.id. C’est ce qui matérialise la relation « un livre a un auteur » et permet au SGBD de refuser un livre dont l’auteur n’existe pas.

On peut faire un mini-schéma :

auteur                          livre
┌────┬────────┬──────────┐      ┌────┬─────────────────┬───────────┬────────────┐
│ id │ prenom │ nom      │      │ id │ titre           │ auteur_id │ disponible │
├────┼────────┼──────────┤      ├────┼─────────────────┼───────────┼────────────┤
│ 1  │ George │ Orwell   │◄─────│ 1  │ 1984            │ 1         │ 0          │
│ 4  │ Jules  │ Verne    │◄─────│ 4  │ Vingt mille...  │ 4         │ 1          │
│ 11 │ Erskine│ Caldwell │      │ 6  │ Voyage au...    │ 4         │ 1          │
└────┴────────┴──────────┘      └────┴─────────────────┴───────────┴────────────┘
       PK: id                              PK: id    FK: auteur_id → auteur.id

On fait remarquer deux choses sur les données :

• Jules Verne apparaît une fois dans auteur mais est référencé par deux livres → c’est tout l’intérêt de la séparation : on ne duplique pas « Jules Verne » dans chaque ligne de livre ;
• Erskine Caldwell (id 11) n’a aucun livre → la relation n’est pas obligatoire dans les deux sens. On garde ce cas pour la séance 4 (jointures externes).

SQL : le langage pour interroger et manipuler. Nous verrons bientôt : SELECT (séances 3–4), INSERT/UPDATE/DELETE (séance 5), sous-requêtes et vues (6), droits (7), puis usage depuis le code (8).

3. Lecture guidée du modèle fourni

Analyse et compréhension ligne par ligne :

CREATE TABLE auteur (
  id     INT          NOT NULL AUTO_INCREMENT,
  prenom VARCHAR(100) NOT NULL,
  nom    VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

Questions guidées :

• que signifie NOT NULL sur prenom ? → interdit une valeur absente ;
• que signifie AUTO_INCREMENT ? → le SGBD attribue l’id automatiquement (on reverra les implications en séance 5 avec LAST_INSERT_ID()) ;
• PRIMARY KEY (id) → identifiant unique et non nul.

CREATE TABLE livre (
  id         INT          NOT NULL AUTO_INCREMENT,
  titre      VARCHAR(200) NOT NULL,
  auteur_id  INT          NOT NULL,
  disponible TINYINT(1)   NOT NULL DEFAULT 1,
  PRIMARY KEY (id),
  KEY idx_livre_auteur (auteur_id),
  CONSTRAINT fk_livre_auteur
    FOREIGN KEY (auteur_id) REFERENCES auteur (id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT
);

Questions guidées :

• DEFAULT 1 sur disponible → si on n’indique rien à l’insertion, le livre est considéré disponible ;
• FOREIGN KEY (auteur_id) REFERENCES auteur(id) → un auteur_id saisi doit exister dans auteur ;
• ON DELETE RESTRICT → on ne peut pas supprimer un auteur tant qu’il a des livres (on le testera en séance 5, c’est volontaire) ;
• KEY idx_livre_auteur (auteur_id) → on annonce simplement qu’il s’agit d’une structure d’accélération (index), qu’on étudiera en séance 9. Pas besoin d’expliquer le B-tree maintenant.

Synthèse :

Message clé : le schéma porte une partie des règles métier (unicité, existence de l’auteur, valeur par défaut). C’est du code déclaratif que le SGBD fait respecter pour tous les programmes, contrairement au code applicatif qui ne protège que l’application qui l’exécute.

TP (1 h 30) — Installation, première connexion, exploration

Livrable : un fichier compte_rendu_seance1.txt contenant les réponses aux questions numérotées et les captures de sortie demandées. Le TP est conçu pour être faisable sans connaissance SQL préalable ; toutes les commandes sont fournies, vous devez les exécuter, observer et commenter.

Étape 0 — Vérifier l’installation (10 min)

Selon l’environnement :

• poste local : mariadb --version (ou mysql --version) ; c’est notre cas!
• machine virtuelle / conteneur : on fournit l’image, on aurait juste à vérifier que le service tourne ;
• accès distant : il est indispensable que les identifiants soient fournis mais l’idée est de vous montrer que le travail fait ici pourrait tout aussi bien être conduit sur un site hebergé.

Commande de connexion :

sudo mariadb

Une fois dans le client (avec le prompt MariaDB [(none)]> ), on vérifie le serveur :

MariaDB [(none)]> SELECT VERSION();
+--------------------------------------+
| VERSION()                            |
+--------------------------------------+
| **.*.*-MariaDB-0+deb13u1 from Debian |
+--------------------------------------+
1 row in set (0,001 sec)
MariaDB [(none)]> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| phpmyadmin         |
| sys                |
+--------------------+

Q0 : quelle version du SGBD utilisez-vous ? Combien de bases sont déjà présentes sur le serveur ?

Étape 1 — Importer le schéma et les données (15 min)

Comme nous allons le voir il est possible d’envoyer des requêtes SQL en ligne de commande. Vérifier que vous avez bien les fichiers schema.sql et data.sql demandés puis tapez les commandes suivantes dans une Konsole (pas dans le client mariadb) :

sudo mariadb -e "CREATE DATABASE bibliotheque;"
sudo mariadb bibliotheque < schema.sql
sudo mariadb bibliotheque < data.sql

Vous avez créé une nouvelle base de données bibliotheque ; importé dans cette base la structure décrite par schema.sql, puis rempli cette structure avec les données fournies par data.sql. Ces fichiers sont lisibles, vous pouvez les ouvrir avec kate pour visualiser leur contenu. Puis, dans le client, selectionnez la base bibliotheque et demandez à voir les Tables associées :

MariaDB [(none)]> USE bibliotheque;
MariaDB [bibliotheque]> SHOW TABLES;
+------------------------+
| Tables_in_bibliotheque |
+------------------------+
| auteur                 |
| livre                  |
+------------------------+
2 rows in set (0,000 sec)

MariaDB [bibliotheque]> 

Q1 : combien de tables contient la base bibliotheque ? Listez-les.

Q2 : que se passerait-il si vous relanciez schema.sql une deuxième fois ? (indice : regardez la première ligne du fichier)

Étape 2 — Explorer le schéma (25 min)

a) Description des tables.

Réflechissez à l’endroit où vous devez vous placer pour exécuter les commandes suivantes :

DESCRIBE auteur;
DESCRIBE livre;
-- variante équivalente :
SHOW COLUMNS FROM livre;

Q3 : recopiez la sortie de DESCRIBE livre. Identifiez pour chaque colonne : type, nullabilité, valeur par défaut, rôle (clé primaire, clé étrangère, index).

Q4 : DESCRIBE vous montre-t-il la contrainte de clé étrangère ? Sinon, quelle commande utilisez-vous ? (indice : SHOW CREATE TABLE livre;)

b) Exploration des métadonnées via information_schema.

information_schema est une base « système » qui décrit les autres bases. On peut donc l’interroger en SQL comme n’importe quelle table. On l’a vue suite à la commande SQL ‘SHOW DATABASES;’

-- Toutes les tables de la base bibliotheque
SELECT table_name, table_rows
FROM information_schema.tables
WHERE table_schema = 'bibliotheque';

-- Toutes les colonnes de toutes les tables de la base
SELECT table_name, column_name, data_type, is_nullable, column_default, column_key
FROM information_schema.columns
WHERE table_schema = 'bibliotheque'
ORDER BY table_name, ordinal_position;

-- Les clés primaires et étrangères
SELECT table_name, constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_schema = 'bibliotheque';

Q5 : quelle table de bibliotheque a le plus de colonnes ? Lesquelles ?

Q6 : d’après la requête sur table_constraints, quelles contraintes existe-t-il dans la base ? Classez-les par type (PRIMARY KEY, FOREIGN KEY, UNIQUE…).

Q7 : la colonne column_key dans information_schema.columns vaut PRI pour la clé primaire et MUL pour une colonne indexée. Vérifiez-le sur livre. Que constatez-vous pour auteur_id ?

c) Visualisation du schéma. Si un client graphique est disponible (DBeaver, phpMyAdmin), demander une capture du diagramme relationnel généré. Sinon, redessiner au propre le schéma (deux boîtes, une flèche auteur_id → auteur.id). Il est toujours bon de savoir que des outils graphiques existent mais ils sont peu pratiques ; il est tellement plus rapide de relire, modifier et lancer une commande ou un script!

Q8 : joignez le diagramme (capture ou dessin). Combien de relations y voyez-vous ? De quel type (1-1, 1-N, N-N) ?

Étape 3 — Premières lectures : SELECT (25 min)

On ne demande aucune écriture : uniquement de l’observation, pour rester dans l’objectif de la séance.

SELECT * FROM auteur;
SELECT * FROM livre;

Q9 : combien y a-t-il de lignes dans chaque table ? Quelle commande avez-vous utilisée pour le vérifier autrement que « à l’œil » ? (réponse : SELECT COUNT(*) FROM ...)

Q10 : identifiez, dans la table auteur, un auteur qui n’apparaît dans aucun livre. Quelle est la valeur de son id ? Expliquez, en une phrase, pourquoi le SGBD autorise ce cas.

Q11 : identifiez un livre indisponible. Quelle colonne et quelle valeur permettent de le savoir ?

Q12 : dans la table livre, combien de livres sont associés à l’auteur d’id = 4 ? Quelle commande avez-vous écrite pour le savoir ? (indice : WHERE auteur_id = 4 — même si WHERE sera vu formellement en séance 3, on l’autorise ici comme lecture)

Étape 4 — Tester l’intégrité (15 min) — démonstration guidée

C’est le moment de voir le SGBD refuser une opération incohérente. On prévient : l’objectif n’est pas d’apprendre INSERT/DELETE (séance 5), mais d’observer le comportement.

-- 1. Tenter d'insérer un livre avec un auteur inexistant (id 999)
INSERT INTO livre (titre, auteur_id) VALUES ('Livre fantôme', 999);

-- 2. Tenter de supprimer un auteur qui a des livres (Jules Verne, id 4)
DELETE FROM auteur WHERE id = 4;

-- 3. Tenter d'insérer un auteur sans nom
INSERT INTO auteur (prenom) VALUES ('Anonyme');

Q13 : pour chaque commande, recopiez le message d’erreur renvoyé et expliquez quelle contrainte du schéma l’a provoqué (clé étrangère ? NOT NULL ? autre ?).

Q14 : après ces trois tentatives, vérifiez avec SELECT COUNT(*) que la base n’a pas été modifiée. Conclusion : que garantit le SGBD même quand le code applicatif se trompe ?

Étape 5 — Synthèse (10 min)

Q15 : en 5 à 8 lignes, répondez à la question « pourquoi une base de données relationnelle plutôt que des fichiers ou des structures en mémoire ? », en vous appuyant sur au moins trois observations concrètes faites pendant ce TP (persistance, refus d’une donnée incohérente, métadonnées interrogeables, séparation schéma/données…).

À préparer pour la séance 2

• Relire le CREATE TABLE de livre et repérer les mots-clés qu’on n’a pas encore expliqués (CONSTRAINT, ON UPDATE CASCADE) — on les traitera en séance 2.
• Réfléchir à la question : « si on voulait enregistrer les emprunts dans cette base, quelles tables faudrait-il ajouter ? » — amorce du projet intégrateur, sans attendre de réponse formelle.

Auto-évaluation