Modélisation BDD MERISE & UML

  Analyse du besoin
         │
         ▼
  ┌─────────────────────────────────────────┐
  │  MCD — Modèle Conceptuel de Données     │  ← Entités, associations, cardinalités
  │  "Quoi ?" — vision métier               │
  └────────────────────┬────────────────────┘
                       │  Application des règles de passage
                       ▼
  ┌─────────────────────────────────────────┐
  │  MLD — Modèle Logique de Données        │  ← Tables, PK, FK (notation relationnelle)
  │  "Comment ?" — vision relationnelle     │
  └────────────────────┬────────────────────┘
                       │  Choix du SGBD + types de données
                       ▼
  ┌─────────────────────────────────────────┐
  │  MPD — Modèle Physique de Données       │  ← CREATE TABLE, INDEX, contraintes SQL
  │  "Avec quoi ?" — vision technique       │
  └─────────────────────────────────────────┘

  ┌──────────────────────┐
  │       CLIENT         │  ← Nom de l'entité (toujours au singulier, en majuscules)
  ├──────────────────────┤
  │ #id_client           │  ← Identifiant (clé primaire) — noté avec #
  │  nom                 │  ← Attribut simple
  │  prenom              │
  │  email               │
  │  telephone           │
  └──────────────────────┘

  ┌──────────┐                              ┌───────────┐
  │  CLIENT  │                              │ COMMANDE  │
  └────┬─────┘                              └─────┬─────┘
       │  1,n                              1,1   │
       └──────────────── PASSER ───────────────────┘
                        (association)

  ┌──────────┐                              ┌───────────┐
  │ COMMANDE │                              │  PRODUIT  │
  └────┬─────┘                              └─────┬─────┘
       │  1,n    quantite, prix_unitaire    0,n   │
       └────────────── CONTENIR ─────────────────┘
                     (avec attributs)

  ┌──────────────────────┐          ┌──────────────────────┐
  │       CLIENT         │          │      CATEGORIE       │
  ├──────────────────────┤          ├──────────────────────┤
  │ #id_client           │          │ #id_categorie        │
  │  nom                 │          │  libelle             │
  │  prenom              │          │  description         │
  │  email               │          └──────────┬───────────┘
  │  telephone           │                     │ 1,n
  │  adresse             │               APPARTENIR
  └──────────┬───────────┘                     │ 1,1
             │ 1,n                   ┌──────────┴───────────┐
           PASSER                    │       PRODUIT        │
             │ 1,1                   ├──────────────────────┤
  ┌──────────┴───────────┐           │ #id_produit          │
  │       COMMANDE       │           │  designation         │
  ├──────────────────────┤           │  prix_unitaire       │
  │ #id_commande         │           │  stock               │
  │  date_commande       │           └──────────┬───────────┘
  │  statut              │                      │ 0,n
  │  adresse_livraison   │                 CONTENIR
  └──────────┬───────────┘           (quantite, remise)
             │ 1,n                          │ 1,n
             └──────────────────────────────┘

  NOM_TABLE (clé_primaire, attribut1, attribut2, #clé_etrangere)

  Conventions :
  • Clé primaire     : soulignée ou en MAJUSCULES
  • Clé étrangère    : précédée de # ou suffixe _id, en italique
  • Clé primaire composée : les deux colonnes soulignées

  CLIENT (id_client, nom, prenom, email, telephone, adresse)
  ‾‾‾‾‾‾‾‾‾‾‾‾

  CATEGORIE (id_categorie, libelle, description)
  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾

  PRODUIT (id_produit, designation, prix_unitaire, stock, #id_categorie)
  ‾‾‾‾‾‾‾‾‾‾‾‾
    └─ #id_categorie référence CATEGORIE(id_categorie)

  COMMANDE (id_commande, date_commande, statut, adresse_livraison, #id_client)
  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾
    └─ #id_client référence CLIENT(id_client)

  LIGNE_COMMANDE (id_commande, id_produit, quantite, remise)
  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
    PK composée : (id_commande, id_produit)
    └─ #id_commande référence COMMANDE(id_commande)
    └─ #id_produit  référence PRODUIT(id_produit)

-- Table CLIENT
CREATE TABLE client (
  id_client     INT          PRIMARY KEY AUTO_INCREMENT,
  nom           VARCHAR(100) NOT NULL,
  prenom        VARCHAR(100) NOT NULL,
  email         VARCHAR(255) NOT NULL UNIQUE,
  telephone     VARCHAR(20),
  adresse       TEXT
);

-- Table CATEGORIE
CREATE TABLE categorie (
  id_categorie  INT          PRIMARY KEY AUTO_INCREMENT,
  libelle       VARCHAR(100) NOT NULL UNIQUE,
  description   TEXT
);

-- Table PRODUIT (FK vers categorie)
CREATE TABLE produit (
  id_produit    INT            PRIMARY KEY AUTO_INCREMENT,
  designation   VARCHAR(255)   NOT NULL,
  prix_unitaire DECIMAL(10,2)  NOT NULL CHECK (prix_unitaire >= 0),
  stock         INT            NOT NULL DEFAULT 0 CHECK (stock >= 0),
  id_categorie  INT            NOT NULL,
  FOREIGN KEY (id_categorie) REFERENCES categorie(id_categorie)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Table COMMANDE (FK vers client)
CREATE TABLE commande (
  id_commande        INT         PRIMARY KEY AUTO_INCREMENT,
  date_commande      DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
  statut             ENUM('en_attente','validee','expediee','livree','annulee')
                                 NOT NULL DEFAULT 'en_attente',
  adresse_livraison  TEXT        NOT NULL,
  id_client          INT         NOT NULL,
  FOREIGN KEY (id_client) REFERENCES client(id_client)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Table LIGNE_COMMANDE (table de jonction N:N avec attributs)
CREATE TABLE ligne_commande (
  id_commande    INT           NOT NULL,
  id_produit     INT           NOT NULL,
  quantite       INT           NOT NULL CHECK (quantite > 0),
  remise         DECIMAL(5,2)  NOT NULL DEFAULT 0.00,
  PRIMARY KEY (id_commande, id_produit),
  FOREIGN KEY (id_commande) REFERENCES commande(id_commande)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (id_produit) REFERENCES produit(id_produit)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

-- Index pour les colonnes fréquemment filtrées
CREATE INDEX idx_produit_categorie ON produit(id_categorie);
CREATE INDEX idx_commande_client   ON commande(id_client);
CREATE INDEX idx_commande_statut   ON commande(statut);

  COMMANDE (id_commande, client, produits)
  ┌─────────────┬──────────────┬─────────────────────────────────┐
  │ id_commande │ client       │ produits                        │
  ├─────────────┼──────────────┼─────────────────────────────────┤
  │ 1           │ Jean Dupont  │ "Stylo x2, Cahier x1, Règle x3" │  ← non atomique !
  │ 2           │ Marie Martin │ "Livre x1"                      │
  └─────────────┴──────────────┴─────────────────────────────────┘

  COMMANDE (id_commande, id_client)
  LIGNE_COMMANDE (id_commande, id_produit, quantite)

  ┌─────────────┬────────────┐   ┌─────────────┬────────────┬──────────┐
  │ id_commande │ id_client  │   │ id_commande │ id_produit │ quantite │
  ├─────────────┼────────────┤   ├─────────────┼────────────┼──────────┤
  │ 1           │ 1          │   │ 1           │ 101        │ 2        │
  │ 2           │ 2          │   │ 1           │ 102        │ 1        │
  └─────────────┴────────────┘   │ 1           │ 103        │ 3        │
                                 │ 2           │ 104        │ 1        │
                                 └─────────────┴────────────┴──────────┘

  LIGNE_COMMANDE (id_commande, id_produit, quantite, nom_produit, prix_produit)
  PK = (id_commande, id_produit)

  Problème :
  • quantite    → dépend de (id_commande + id_produit) ✓
  • nom_produit → dépend uniquement de id_produit       ✗ dépendance partielle !

  LIGNE_COMMANDE (id_commande, id_produit, quantite)
                  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
  PRODUIT (id_produit, nom_produit, prix_produit, ...)
           ‾‾‾‾‾‾‾‾‾‾

  nom_produit et prix_produit ont été déplacés dans leur propre entité PRODUIT.

  EMPLOYE (id_employe, nom, id_departement, nom_departement, localisation)
  PK = id_employe

  Problème :
  • id_departement  → dépend de id_employe           ✓
  • nom_departement → dépend de id_departement        ✗ dépendance transitive !
  • localisation    → dépend de id_departement        ✗ dépendance transitive !

  EMPLOYE (id_employe, nom, id_departement)
           ‾‾‾‾‾‾‾‾‾‾

  DEPARTEMENT (id_departement, nom_departement, localisation)
               ‾‾‾‾‾‾‾‾‾‾‾‾‾‾

  Les infos du département sont isolées dans leur propre table.

  ┌────────────────────────┐         ┌────────────────────────┐
  │        Client          │         │       Commande         │
  ├────────────────────────┤         ├────────────────────────┤
  │ «PK» id : int          │1      * │ «PK» id : int          │
  │ nom : string           ├─────────┤ dateCommande : date    │
  │ prenom : string        │         │ statut : string        │
  │ email : string         │         │ montantTotal : decimal │
  │ telephone : string     │         └───────────┬────────────┘
  └────────────────────────┘                     │ *
                                             (contient)
                                                 │ 1..*
  ┌────────────────────────┐         ┌───────────┴────────────┐
  │       Categorie        │         │        Produit         │
  ├────────────────────────┤         ├────────────────────────┤
  │ «PK» id : int          │1      * │ «PK» id : int          │
  │ libelle : string       ├─────────┤ designation : string   │
  │ description : string   │         │ prixUnitaire : decimal │
  └────────────────────────┘         │ stock : int            │
                                     └────────────────────────┘

  Relation Commande ↔ Produit (N:N) :
  → Classe d'association LigneCommande { quantite, remise }

  ┌─────────────────────┐          ┌─────────────────────┐
  │      ADHERENT       │          │      CATEGORIE      │
  ├─────────────────────┤          ├─────────────────────┤
  │ #id_adherent        │          │ #id_categorie       │
  │  nom                │          │  libelle            │
  │  prenom             │          └──────────┬──────────┘
  │  email              │                     │ 0,n
  │  date_inscription   │               CLASSIFIER
  └──────────┬──────────┘                     │ 1,n
             │ 0,n                   ┌─────────┴──────────┐
           EMPRUNTER                 │        LIVRE        │
        (date_emprunt,               ├─────────────────────┤
         date_retour_prevue,         │ #isbn               │
         date_retour_reelle)         │  titre              │
             │ 0,1                   │  auteur             │
  ┌──────────┴──────────┐            │  annee_publication  │
  │       EXEMPLAIRE    │            │  disponible         │
  ├─────────────────────┤            └──────────┬──────────┘
  │ #id_exemplaire      │ 1,n  AVOIR  1,1        │
  │  etat               ├───────────────────────┘
  └─────────────────────┘

  ADHERENT (id_adherent, nom, prenom, email, date_inscription)
  ‾‾‾‾‾‾‾‾‾‾‾‾‾

  CATEGORIE (id_categorie, libelle)
  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾

  LIVRE (isbn, titre, auteur, annee_publication, disponible)
  ‾‾‾‾‾‾‾‾

  LIVRE_CATEGORIE (isbn, id_categorie)         ← table de jonction N:N
  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
    └─ #isbn          référence LIVRE(isbn)
    └─ #id_categorie  référence CATEGORIE(id_categorie)

  EXEMPLAIRE (id_exemplaire, etat, #isbn)
  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾
    └─ #isbn référence LIVRE(isbn)

  EMPRUNT (id_exemplaire, id_adherent, date_emprunt,
  ‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾‾
           date_retour_prevue, date_retour_reelle)
    PK composée : (id_exemplaire, id_adherent, date_emprunt)
    └─ #id_exemplaire référence EXEMPLAIRE(id_exemplaire)
    └─ #id_adherent   référence ADHERENT(id_adherent)

CREATE TABLE adherent (
  id_adherent      INT          PRIMARY KEY AUTO_INCREMENT,
  nom              VARCHAR(100) NOT NULL,
  prenom           VARCHAR(100) NOT NULL,
  email            VARCHAR(255) NOT NULL UNIQUE,
  date_inscription DATE         NOT NULL DEFAULT (CURRENT_DATE)
);

CREATE TABLE categorie (
  id_categorie  INT          PRIMARY KEY AUTO_INCREMENT,
  libelle       VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE livre (
  isbn               CHAR(13)     PRIMARY KEY,
  titre              VARCHAR(255) NOT NULL,
  auteur             VARCHAR(255) NOT NULL,
  annee_publication  YEAR,
  disponible         TINYINT(1)   NOT NULL DEFAULT 1
);

CREATE TABLE livre_categorie (
  isbn           CHAR(13) NOT NULL,
  id_categorie   INT      NOT NULL,
  PRIMARY KEY (isbn, id_categorie),
  FOREIGN KEY (isbn)          REFERENCES livre(isbn)
    ON DELETE CASCADE ON UPDATE CASCADE,
  FOREIGN KEY (id_categorie)  REFERENCES categorie(id_categorie)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE exemplaire (
  id_exemplaire  INT         PRIMARY KEY AUTO_INCREMENT,
  etat           ENUM('bon','use','abime') NOT NULL DEFAULT 'bon',
  isbn           CHAR(13)    NOT NULL,
  FOREIGN KEY (isbn) REFERENCES livre(isbn)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

CREATE TABLE emprunt (
  id_exemplaire       INT  NOT NULL,
  id_adherent         INT  NOT NULL,
  date_emprunt        DATE NOT NULL,
  date_retour_prevue  DATE NOT NULL,
  date_retour_reelle  DATE,
  PRIMARY KEY (id_exemplaire, id_adherent, date_emprunt),
  FOREIGN KEY (id_exemplaire) REFERENCES exemplaire(id_exemplaire)
    ON DELETE RESTRICT ON UPDATE CASCADE,
  FOREIGN KEY (id_adherent)   REFERENCES adherent(id_adherent)
    ON DELETE RESTRICT ON UPDATE CASCADE
);