Lire le schéma relationnel de la médiathèque

Lire le schéma relationnel de la médiathèque

Quand une information est répartie entre plusieurs tables, il faut savoir où elle est enregistrée et comment les tables sont reliées. Sans cette lecture préalable, vous risquez de chercher une donnée au mauvais endroit ou de construire une requête qui associe des enregistrements sans rapport. Dans cette leçon, vous repérez les clés et les chemins de relation du projet de médiathèque, avant d’écrire des requêtes qui combinent ces tables.

Un identifiant par table

Le schéma ci-dessous définit les cinq tables du projet : adherents, oeuvres, exemplaires, emprunts et retards. Chaque table possède une clé primaire, ici une colonne nommée id. Cette clé identifie une ligne de manière unique ; elle permet de distinguer deux adhérents portant le même nom ou deux exemplaires d’une même œuvre.

-- Schéma initial de la médiathèque — MariaDB 11
CREATE TABLE adherents (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    prenom VARCHAR(100) NOT NULL,
    nom VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE oeuvres (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    titre VARCHAR(200) NOT NULL,
    auteur VARCHAR(150) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE exemplaires (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    oeuvre_id INT UNSIGNED NOT NULL,
    cote VARCHAR(50) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_exemplaires_oeuvres
        FOREIGN KEY (oeuvre_id) REFERENCES oeuvres (id)
) ENGINE=InnoDB;

CREATE TABLE emprunts (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    adherent_id INT UNSIGNED NOT NULL,
    exemplaire_id INT UNSIGNED NOT NULL,
    date_emprunt DATE NOT NULL,
    date_echeance DATE NOT NULL,
    date_retour DATE DEFAULT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_emprunts_adherents
        FOREIGN KEY (adherent_id) REFERENCES adherents (id),
    CONSTRAINT fk_emprunts_exemplaires
        FOREIGN KEY (exemplaire_id) REFERENCES exemplaires (id)
) ENGINE=InnoDB;

CREATE TABLE retards (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    emprunt_id INT UNSIGNED NOT NULL,
    date_constat DATE NOT NULL,
    jours_retard INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT fk_retards_emprunts
        FOREIGN KEY (emprunt_id) REFERENCES emprunts (id)
) ENGINE=InnoDB;

Une clé étrangère est une colonne qui référence la clé primaire d’une autre table. Par exemple, exemplaires.oeuvre_id référence oeuvres.id : chaque exemplaire est ainsi rattaché à une œuvre existante. Les contraintes FOREIGN KEY nommées dans le schéma rendent ces relations explicites et permettent au moteur de refuser une référence vers un identifiant inexistant.

Suivre les relations un-à-plusieurs

Une relation un-à-plusieurs signifie qu’une ligne d’une table peut être référencée par plusieurs lignes d’une autre table. Une œuvre peut avoir plusieurs exemplaires, tandis que chaque exemplaire référence une seule œuvre. De même, un adhérent peut apparaître dans plusieurs emprunts, mais chaque emprunt référence un seul adhérent. Un exemplaire peut aussi être associé à plusieurs emprunts successifs au fil du temps ; chaque ligne d’emprunt désigne un exemplaire précis.

La dernière relation est également un-à-plusieurs dans ce schéma : la contrainte relie chaque ligne de retards à un emprunt, sans imposer qu’un emprunt n’ait qu’une seule ligne de retard. Cette décision permet de conserver plusieurs constats pour un emprunt, par exemple à des dates différentes. Pour produire un rapport, vous devrez tenir compte de cette possibilité : une table peut contenir plusieurs lignes liées au même emprunt.

Tracer le chemin entre emprunts et œuvres

La table emprunts ne contient pas directement le titre de l’œuvre. Elle référence un exemplaire par exemplaire_id, et cet exemplaire référence une œuvre par oeuvre_id. Le chemin conceptuel est donc : emprunts → exemplaires → oeuvres. Pour connaître l’œuvre empruntée, vous aurez besoin de suivre ces deux relations ; le titre se trouve dans oeuvres, tandis que la date de l’opération se trouve dans emprunts.

Un rapport qui associe emprunts, adhérents et œuvres suit donc deux branches à partir de l’emprunt : emprunts.adherent_id mène à adherents.id, et emprunts.exemplaire_id mène aux exemplaires, puis aux œuvres. Gardez ce parcours en tête avant de rédiger une requête. Les relations définissent les associations possibles, mais elles ne déterminent pas encore quelles lignes devront apparaître dans le résultat : ce choix dépendra du type de jointure, étudié dans la prochaine leçon.

À retenir

• Une clé primaire identifie de façon unique une ligne ; une clé étrangère référence une clé primaire dans une autre table.
• Les relations un-à-plusieurs se lisent depuis la table qui porte la clé étrangère vers la table référencée.
• Un emprunt mène à son adhérent directement, et à son œuvre en passant par un exemplaire.
• Avant de rédiger une requête multitable, repérez les colonnes et les tables qui portent les données nécessaires.