Créer le schéma et charger un jeu de données
Objectif : Construire les tables de la médiathèque et les alimenter avec des données permettant des mesures utiles.
Une requête qui paraît rapide sur quelques lignes peut devenir coûteuse quand l’historique s’allonge. Pour diagnostiquer les recherches d’emprunts et de retards, vous avez besoin d’un schéma cohérent et d’un jeu de données assez fourni pour représenter des accès réalistes.
Vous allez remplir sql/schema.sql et sql/donnees.sql. Le schéma reprend les cinq tables du projet ; le chargement crée des adhérents, des œuvres, des exemplaires et 5 000 emprunts, dont certains sont encore actifs ou associés à un retard enregistré.
Définir les tables dans l’ordre des dépendances
Les tables parentes doivent exister avant celles qui les référencent : créez d’abord adherents et oeuvres, puis exemplaires, emprunts et retards. Les moteurs InnoDB permettent à MariaDB de faire respecter les références et de gérer les transactions. Les retards sont conservés dans une table distincte ; ici, une ligne décrit un retard constaté pour un emprunt, et jours_retard est calculé lors du chargement à partir des dates.
-- Schéma de la médiathèque — MariaDB 11
CREATE TABLE adherents (
adherent_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
nom VARCHAR(120) NOT NULL,
email VARCHAR(254) NOT NULL,
date_inscription DATE NOT NULL,
PRIMARY KEY (adherent_id),
CONSTRAINT uq_adherents_email UNIQUE (email)
) ENGINE = InnoDB;
CREATE TABLE oeuvres (
oeuvre_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
titre VARCHAR(200) NOT NULL,
auteur VARCHAR(160) NOT NULL,
annee_publication SMALLINT UNSIGNED NULL,
PRIMARY KEY (oeuvre_id)
) ENGINE = InnoDB;
CREATE TABLE exemplaires (
exemplaire_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
oeuvre_id BIGINT UNSIGNED NOT NULL,
code_barres VARCHAR(32) NOT NULL,
PRIMARY KEY (exemplaire_id),
CONSTRAINT uq_exemplaires_code_barres UNIQUE (code_barres),
CONSTRAINT fk_exemplaires_oeuvres
FOREIGN KEY (oeuvre_id) REFERENCES oeuvres (oeuvre_id)
ON UPDATE RESTRICT ON DELETE RESTRICT
) ENGINE = InnoDB;
CREATE TABLE emprunts (
emprunt_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
adherent_id BIGINT UNSIGNED NOT NULL,
exemplaire_id BIGINT UNSIGNED NOT NULL,
date_emprunt DATE NOT NULL,
date_retour_prevue DATE NOT NULL,
date_retour DATE NULL,
PRIMARY KEY (emprunt_id),
CONSTRAINT fk_emprunts_adherents
FOREIGN KEY (adherent_id) REFERENCES adherents (adherent_id)
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT fk_emprunts_exemplaires
FOREIGN KEY (exemplaire_id) REFERENCES exemplaires (exemplaire_id)
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT chk_emprunts_dates CHECK (date_retour_prevue >= date_emprunt),
CONSTRAINT chk_emprunts_retour CHECK (
date_retour IS NULL OR date_retour >= date_emprunt
)
) ENGINE = InnoDB;
CREATE TABLE retards (
retard_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
emprunt_id BIGINT UNSIGNED NOT NULL,
date_constat DATE NOT NULL,
jours_retard INT UNSIGNED NOT NULL,
PRIMARY KEY (retard_id),
CONSTRAINT uq_retards_emprunt UNIQUE (emprunt_id),
CONSTRAINT fk_retards_emprunts
FOREIGN KEY (emprunt_id) REFERENCES emprunts (emprunt_id)
ON UPDATE RESTRICT ON DELETE RESTRICT,
CONSTRAINT chk_retards_jours CHECK (jours_retard > 0)
) ENGINE = InnoDB;Les index des clés primaires et des contraintes d’unicité sont créés avec ces définitions. Les index non primaires correspondant aux recherches étudiées restent dans sql/index.sql, conformément à l’organisation du projet. Les règles RESTRICT empêchent de supprimer une entité encore référencée ; les emprunts et retards historiques restent ainsi protégés.
Charger un jeu de données reproductible
Le script suivant crée les mêmes volumes à chaque exécution, à condition de partir des tables vides. Les dates sont calculées relativement à la date courante : vous obtenez ainsi des emprunts actifs et des retards récents, même si vous exécutez le script à une autre période. La procédure n’utilise aucune valeur aléatoire ; les associations entre emprunteurs, exemplaires et œuvres restent donc prévisibles.
-- À exécuter après sql/schema.sql, sur des tables vides.
DELIMITER //
CREATE PROCEDURE charger_donnees_mediatheque()
BEGIN
DECLARE i INT DEFAULT 1;
DECLARE v_date_emprunt DATE;
DECLARE v_date_retour_prevue DATE;
DECLARE v_date_retour DATE;
DECLARE v_emprunt_id BIGINT UNSIGNED;
-- Créez 200 adhérents avec des adresses distinctes.
WHILE i <= 200 DO
INSERT INTO adherents (nom, email, date_inscription)
VALUES (
CONCAT('Adherent ', i),
CONCAT('adherent', i, '@exemple.test'),
DATE_SUB(CURRENT_DATE, INTERVAL (i MOD 700) DAY)
);
SET i = i + 1;
END WHILE;
-- Créez 100 œuvres et 1 000 exemplaires.
SET i = 1;
WHILE i <= 100 DO
INSERT INTO oeuvres (titre, auteur, annee_publication)
VALUES (CONCAT('Oeuvre ', i), CONCAT('Auteur ', i), 1950 + (i MOD 75));
SET i = i + 1;
END WHILE;
SET i = 1;
WHILE i <= 1000 DO
INSERT INTO exemplaires (oeuvre_id, code_barres)
VALUES (((i - 1) MOD 100) + 1, CONCAT('MED-', LPAD(i, 6, '0')));
SET i = i + 1;
END WHILE;
-- Créez 5 000 emprunts ; les 100 derniers restent actifs.
SET i = 1;
WHILE i <= 5000 DO
SET v_date_emprunt = DATE_SUB(CURRENT_DATE, INTERVAL (i MOD 900) DAY);
SET v_date_retour_prevue = DATE_ADD(v_date_emprunt, INTERVAL 21 DAY);
SET v_date_retour = NULL;
IF i > 100 THEN
SET v_date_retour = DATE_ADD(
v_date_retour_prevue,
INTERVAL (i MOD 12) DAY
);
END IF;
INSERT INTO emprunts (
adherent_id,
exemplaire_id,
date_emprunt,
date_retour_prevue,
date_retour
)
VALUES (
((i - 1) MOD 200) + 1,
((i - 1) MOD 1000) + 1,
v_date_emprunt,
v_date_retour_prevue,
v_date_retour
);
SET v_emprunt_id = LAST_INSERT_ID();
-- Enregistrez les retours effectués après la date prévue.
IF v_date_retour IS NOT NULL
AND v_date_retour > v_date_retour_prevue THEN
INSERT INTO retards (emprunt_id, date_constat, jours_retard)
VALUES (
v_emprunt_id,
v_date_retour,
DATEDIFF(v_date_retour, v_date_retour_prevue)
);
END IF;
SET i = i + 1;
END WHILE;
END//
DELIMITER ;
CALL charger_donnees_mediatheque();
DROP PROCEDURE charger_donnees_mediatheque;
-- Vérifiez les volumes et les emprunts encore actifs.
SELECT 'adherents' AS table_nom, COUNT(*) AS nombre FROM adherents
UNION ALL
SELECT 'oeuvres', COUNT(*) FROM oeuvres
UNION ALL
SELECT 'exemplaires', COUNT(*) FROM exemplaires
UNION ALL
SELECT 'emprunts', COUNT(*) FROM emprunts
UNION ALL
SELECT 'retards', COUNT(*) FROM retards;
SELECT COUNT(*) AS emprunts_actifs
FROM emprunts
WHERE date_retour IS NULL;Le premier contrôle doit afficher 200 adhérents, 100 œuvres, 1 000 exemplaires et 5 000 emprunts. Le nombre de retards varie selon la date d’exécution, mais reste supérieur à zéro ; 100 emprunts ont une date_retour NULL.La procédure insère les lignes dans le respect des références : les emprunts ne sont créés qu’après leurs adhérents et exemplaires, et les retards après leurs emprunts. Une même copie peut apparaître plusieurs fois dans l’historique ; les lignes générées ici ne prétendent donc pas représenter un calendrier réel sans chevauchement. Le scénario de disponibilité concurrente sera traité séparément dans les leçons consacrées aux transactions.
À retenir
- Créez les tables parentes avant les tables qui portent leurs clés étrangères.
- Conservez les retards enregistrés dans une table liée aux emprunts et documentez leur calcul.
- Chargez les tables dans l’ordre des dépendances, avec des valeurs reproductibles.
- Contrôlez les volumes et le nombre d’emprunts actifs avant de lancer vos diagnostics.
Dans la prochaine leçon, vous utiliserez ces données pour optimiser la recherche des emprunts en retard et comparer les plans avant et après indexation.