Chapitre 15 : Projet MySQL

1. Projet : Système de gestion de bibliothèque

Nous allons créer une base de données complète pour gérer une bibliothèque.

2. Modélisation de la base

-- Créer la base
CREATE DATABASE bibliotheque;
USE bibliotheque;

-- Table des auteurs
CREATE TABLE auteurs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    prenom VARCHAR(100),
    pays VARCHAR(50)
);

-- Table des livres
CREATE TABLE livres (
    id INT PRIMARY KEY AUTO_INCREMENT,
    titre VARCHAR(200) NOT NULL,
    auteur_id INT,
    annee_publication INT,
    isbn VARCHAR(20) UNIQUE,
    disponible BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (auteur_id) REFERENCES auteurs(id)
);

-- Table des membres
CREATE TABLE membres (
    id INT PRIMARY KEY AUTO_INCREMENT,
    nom VARCHAR(100) NOT NULL,
    email VARCHAR(150) UNIQUE NOT NULL,
    date_inscription DATE DEFAULT (CURRENT_DATE)
);

-- Table des emprunts
CREATE TABLE emprunts (
    id INT PRIMARY KEY AUTO_INCREMENT,
    livre_id INT,
    membre_id INT,
    date_emprunt DATE NOT NULL,
    date_retour_prevue DATE NOT NULL,
    date_retour_effective DATE,
    FOREIGN KEY (livre_id) REFERENCES livres(id),
    FOREIGN KEY (membre_id) REFERENCES membres(id)
);

3. Insertion de données

-- Ajouter des auteurs
INSERT INTO auteurs (nom, prenom, pays) VALUES
('Orwell', 'George', 'Royaume-Uni'),
('Hugo', 'Victor', 'France'),
('Tolkien', 'J.R.R.', 'Royaume-Uni');

-- Ajouter des livres
INSERT INTO livres (titre, auteur_id, annee_publication, isbn) VALUES
('1984', 1, 1949, '978-0451524935'),
('Les Misérables', 2, 1862, '978-2070409228'),
('Le Seigneur des Anneaux', 3, 1954, '978-0618640157');

-- Ajouter des membres
INSERT INTO membres (nom, email) VALUES
('Alice Dupont', 'alice@example.com'),
('Bob Martin', 'bob@example.com');

4. Requêtes utiles

Livres disponibles

SELECT l.titre, a.nom AS auteur
FROM livres l
INNER JOIN auteurs a ON l.auteur_id = a.id
WHERE l.disponible = TRUE;

Emprunts en cours

SELECT
    m.nom AS membre,
    l.titre AS livre,
    e.date_emprunt,
    e.date_retour_prevue
FROM emprunts e
INNER JOIN membres m ON e.membre_id = m.id
INNER JOIN livres l ON e.livre_id = l.id
WHERE e.date_retour_effective IS NULL;

Statistiques

-- Nombre de livres par auteur
SELECT a.nom, COUNT(l.id) AS nombre_livres
FROM auteurs a
LEFT JOIN livres l ON a.id = l.auteur_id
GROUP BY a.id, a.nom;

-- Membres les plus actifs
SELECT m.nom, COUNT(e.id) AS nombre_emprunts
FROM membres m
LEFT JOIN emprunts e ON m.id = e.membre_id
GROUP BY m.id, m.nom
ORDER BY nombre_emprunts DESC;

5. Procédure d'emprunt

-- Emprunter un livre (avec transaction)
START TRANSACTION;

-- Vérifier disponibilité
SELECT disponible FROM livres WHERE id = 1;

-- Créer l'emprunt
INSERT INTO emprunts (livre_id, membre_id, date_emprunt, date_retour_prevue)
VALUES (1, 1, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY));

-- Marquer comme indisponible
UPDATE livres SET disponible = FALSE WHERE id = 1;

COMMIT;