Du MLD au MPD : le script SQL CREATE TABLE

-- Base du réseau de médiathèques Rivelire (cas fictif du cours Merise)
-- MySQL 8.4. Rejouable : la base est supprimée puis recréée.
DROP DATABASE IF EXISTS rivelire;
CREATE DATABASE rivelire CHARACTER SET utf8mb4;
USE rivelire;

CREATE TABLE site (
  code_site    CHAR(3)      PRIMARY KEY,
  nom_site     VARCHAR(50)  NOT NULL,
  adresse_site VARCHAR(100) NOT NULL,
  ville_site   VARCHAR(50)  NOT NULL
);

CREATE TABLE genre (
  code_genre    VARCHAR(5)  PRIMARY KEY,
  libelle_genre VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE document (
  num_document   INT AUTO_INCREMENT PRIMARY KEY,
  titre          VARCHAR(200)  NOT NULL,
  annee_parution SMALLINT      NOT NULL,
  resume         VARCHAR(2000),
  code_genre     VARCHAR(5)    NOT NULL,          -- RG3 : un seul genre (1,1)
  CONSTRAINT fk_document_genre FOREIGN KEY (code_genre)
    REFERENCES genre (code_genre)
);

CREATE TABLE livre (                               -- héritage, solution 1
  num_document INT      PRIMARY KEY,               -- même valeur que dans document
  isbn         CHAR(13) NOT NULL UNIQUE,
  nb_pages     SMALLINT,
  CONSTRAINT fk_livre_document FOREIGN KEY (num_document)
    REFERENCES document (num_document) ON DELETE CASCADE
);

CREATE TABLE film (
  num_document INT      PRIMARY KEY,
  duree_min    SMALLINT NOT NULL,
  CONSTRAINT fk_film_document FOREIGN KEY (num_document)
    REFERENCES document (num_document) ON DELETE CASCADE
);

CREATE TABLE jeu (
  num_document INT         PRIMARY KEY,
  plateforme   VARCHAR(30) NOT NULL,
  pegi         TINYINT     NOT NULL,
  CONSTRAINT chk_pegi CHECK (pegi IN (3, 7, 12, 16, 18)),
  CONSTRAINT fk_jeu_document FOREIGN KEY (num_document)
    REFERENCES document (num_document) ON DELETE CASCADE
);

CREATE TABLE contributeur (
  num_contributeur    INT AUTO_INCREMENT PRIMARY KEY,
  nom_contributeur    VARCHAR(80) NOT NULL,
  prenom_contributeur VARCHAR(50)
);

CREATE TABLE contribuer (
  num_contributeur INT         NOT NULL,
  num_document     INT         NOT NULL,
  role             VARCHAR(20) NOT NULL,
  PRIMARY KEY (num_contributeur, num_document),     -- association plusieurs à plusieurs
  CONSTRAINT chk_role CHECK (role IN ('auteur', 'illustrateur', 'traducteur', 'réalisateur', 'studio')),
  CONSTRAINT fk_contribuer_contributeur FOREIGN KEY (num_contributeur)
    REFERENCES contributeur (num_contributeur),
  CONSTRAINT fk_contribuer_document FOREIGN KEY (num_document)
    REFERENCES document (num_document) ON DELETE CASCADE
);

CREATE TABLE exemplaire (
  code_barre       CHAR(13)    PRIMARY KEY,
  date_acquisition DATE        NOT NULL,
  etat             VARCHAR(10) NOT NULL DEFAULT 'neuf',
  num_document     INT         NOT NULL,
  code_site        CHAR(3)     NOT NULL,             -- RG6 : un seul site (1,1)
  CONSTRAINT chk_etat CHECK (etat IN ('neuf', 'bon', 'usé', 'abîmé')),
  CONSTRAINT fk_exemplaire_document FOREIGN KEY (num_document)
    REFERENCES document (num_document),
  CONSTRAINT fk_exemplaire_site FOREIGN KEY (code_site)
    REFERENCES site (code_site)
);

CREATE TABLE adherent (
  num_adherent     INT AUTO_INCREMENT PRIMARY KEY,
  nom_adherent     VARCHAR(50)  NOT NULL,
  prenom_adherent  VARCHAR(50)  NOT NULL,
  date_naissance   DATE         NOT NULL,
  email            VARCHAR(100) UNIQUE,              -- facultative, mais unique (RG7)
  date_inscription DATE         NOT NULL DEFAULT (CURRENT_DATE),
  num_responsable  INT,                              -- réflexive 0,1 : facultative (RG8)
  CONSTRAINT fk_adherent_responsable FOREIGN KEY (num_responsable)
    REFERENCES adherent (num_adherent)
);

CREATE TABLE emprunt (
  num_emprunt        INT AUTO_INCREMENT PRIMARY KEY,
  date_emprunt       DATE     NOT NULL DEFAULT (CURRENT_DATE),
  date_retour_prevue DATE     NOT NULL,
  date_retour        DATE,                         -- vide tant que non rendu (RG9)
  num_adherent       INT      NOT NULL,
  code_barre         CHAR(13) NOT NULL,
  CONSTRAINT chk_dates_emprunt CHECK (date_retour_prevue > date_emprunt
    AND (date_retour IS NULL OR date_retour >= date_emprunt)),
  CONSTRAINT fk_emprunt_adherent FOREIGN KEY (num_adherent)
    REFERENCES adherent (num_adherent),
  CONSTRAINT fk_emprunt_exemplaire FOREIGN KEY (code_barre)
    REFERENCES exemplaire (code_barre)
);

CREATE TABLE reserver (
  num_adherent     INT  NOT NULL,
  num_document     INT  NOT NULL,
  date_reservation DATE NOT NULL DEFAULT (CURRENT_DATE),
  PRIMARY KEY (num_adherent, num_document),
  CONSTRAINT fk_reserver_adherent FOREIGN KEY (num_adherent)
    REFERENCES adherent (num_adherent) ON DELETE CASCADE,
  CONSTRAINT fk_reserver_document FOREIGN KEY (num_document)
    REFERENCES document (num_document) ON DELETE CASCADE
);

SHOW TABLES;                 -- 12 tables attendues
DESCRIBE exemplaire;         -- colonnes, types, NULL autorisé ou non, clés
SHOW CREATE TABLE emprunt;   -- le CREATE TABLE complet, contraintes comprises

-- Des données valides : ces quatre insertions doivent réussir
INSERT INTO site VALUES ('CEN', 'Médiathèque centrale', '12 rue des Tanneurs', 'Valbourg');
INSERT INTO genre VALUES ('ROM', 'Roman');
INSERT INTO document (titre, annee_parution, code_genre)
VALUES ('La Ville sous la neige', 2019, 'ROM');          -- reçoit le numéro 1
INSERT INTO exemplaire (code_barre, date_acquisition, num_document, code_site)
VALUES ('3700001234567', '2026-11-02', 1, 'CEN');

-- Un exemplaire rattaché à un site qui n'existe pas
INSERT INTO exemplaire (code_barre, date_acquisition, num_document, code_site)
VALUES ('3700000000001', '2026-11-02', 1, 'XXX');
-- ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (...)

-- Un état qui n'est pas dans la liste
INSERT INTO exemplaire (code_barre, date_acquisition, etat, num_document, code_site)
VALUES ('3700000000002', '2026-11-02', 'perdu', 1, 'CEN');
-- ERROR 3819 (HY000): Check constraint 'chk_etat' is violated.