Modélisation et schémas

Contenu

Modélisation et schémas#

De l’idée à la table : les niveaux de modélisation#

Concevoir une base de données ne commence pas par écrire du SQL. La démarche professionnelle distingue trois niveaux d’abstraction progressifs, du plus conceptuel au plus physique :

Définition 40 (Les trois niveaux de modélisation)

MCD (Modèle Conceptuel des Données) : niveau sémantique. On décrit les entités du domaine métier et leurs associations, indépendamment de toute technique. Outil : diagramme Entité-Association (E/A ou E-R).
MLD (Modèle Logique des Données) : niveau logique. On traduit le MCD en tables, colonnes et clés étrangères, en tenant compte du modèle relationnel mais pas du SGBDR cible.
MPD (Modèle Physique des Données) : niveau physique. On adapte le MLD au SGBDR choisi : types exacts, index, partitionnement, espaces de stockage.

Remarque 13

En France, la méthode MERISE (Michel Matheron, 1978) formalise cette démarche avec le triptyque MCD/MLD/MPD. Dans le monde anglosaxon, on parle de modélisation conceptuelle / logique / physique et de diagrammes ER (Entity-Relationship, Peter Chen, 1976).

Le Modèle Conceptuel des Données (MCD)#

Entités#

Définition 41 (Entité)

Une entité est un objet du monde réel, distinguable et pertinent pour le système d’information. Elle est caractérisée par des propriétés (attributs). Dans les diagrammes, elle est représentée par un rectangle.

Exemples : LIVRE, AUTEUR, ADHERENT, EMPRUNT.

Associations#

Définition 42 (Association)

Une association (ou relation) exprime un lien sémantique entre deux ou plusieurs entités. Elle peut aussi posséder des propriétés propres. Dans les diagrammes E/A, elle est représentée par un losange ou une ligne étiquetée.

Cardinalités#

Définition 43 (Cardinalités)

Les cardinalités (ou multiplicités) précisent combien d’occurrences d’une entité peuvent participer à une association. On note la cardinalité minimale et maximale : (min, max).

(0, 1) : zéro ou un (optionnel côté « un »).
(1, 1) : exactement un (obligatoire côté « un »).
(0, N) ou (*, *) : zéro ou plusieurs.
(1, N) : au moins un.

Exemple 11 (Types de cardinalités)

Quatre configurations fondamentales selon les maxima :

1-1 (un-à-un) : un EMPLOYE occupe au plus un BUREAU, et chaque bureau est occupé par au plus un employé.
1-N (un-à-plusieurs) : un DEPARTEMENT emploie plusieurs EMPLOYEs, mais chaque employé appartient à un seul département.
N-N (plusieurs-à-plusieurs) : un ETUDIANT suit plusieurs COURS, et chaque cours accueille plusieurs étudiants.
Association ternaire : INTERVENTION(MEDECIN, PATIENT, HOPITAL) implique trois entités.

La notation Crow’s Foot#

Définition 44 (Notation Crow’s Foot)

La notation Crow’s Foot (patte de corbeau) est une représentation graphique des cardinalités dans les diagrammes ER. Les symboles utilisés sont :

Un trait simple (|) : exactement un.
Un cercle (○) : zéro.
Une patte de corbeau (>|) : plusieurs.

La notation se lit des deux côtés de la relation. Ainsi |o---<| se lit « zéro ou un — à — un ou plusieurs » (1-N optionnel côté un).

Remarque 14

Il existe plusieurs notations pour les diagrammes ER : la notation de Peter Chen (entités en rectangles, associations en losanges), la notation Crow’s Foot popularisée par les outils comme MySQL Workbench et dbdiagram.io, et la notation UML (diagrammes de classes avec multiplicités). Elles expriment les mêmes concepts avec des graphismes différents.

Du MCD au MLD : règles de transformation#

Définition 45 (Règles de passage MCD → MLD)

Entité → table avec ses propriétés comme colonnes ; l’identifiant devient la clé primaire.
Association 1-N → la table côté « N » reçoit une clé étrangère pointant vers la table côté « 1 ».
Association 1-1 → l’une des deux tables reçoit une clé étrangère (en général la moins peuplée ou la moins centrale).
Association N-N → création d’une table de jonction (ou table d’association) contenant les deux clés étrangères (qui forment souvent la clé primaire composite).
Propriétés de l’association → colonnes de la table de jonction.

Cas pratique : la bibliothèque#

Analyse du domaine#

Une bibliothèque municipale souhaite informatiser ses emprunts. Le domaine comprend :

Des livres, identifiés par leur ISBN, avec un titre, un genre et une date de publication.
Des auteurs, reliés aux livres par une relation N-N (un livre peut avoir plusieurs auteurs, un auteur peut écrire plusieurs livres).
Des adhérents, identifiés par un numéro, avec nom, prénom, email et date d’adhésion.
Des emprunts : un adhérent emprunte un exemplaire d’un livre à une date donnée, avec une date de retour prévue et réelle.

Exemple 12 (MLD de la bibliothèque)

Après transformation du MCD :

AUTEURS(auteur_id PK, nom, prenom, nationalite)
LIVRES(livre_id PK, isbn UNIQUE, titre, genre, annee_publication)
LIVRE_AUTEUR(livre_id FK, auteur_id FK) — table de jonction N-N
ADHERENTS(adherent_id PK, nom, prenom, email UNIQUE, date_adhesion)
EMPRUNTS(emprunt_id PK, adherent_id FK, livre_id FK, date_emprunt, date_retour_prevue, date_retour_reelle)

Création du schéma en SQLite#

conn = sqlite3.connect(":memory:")
conn.execute("PRAGMA foreign_keys = ON")

conn.executescript("""
-- Auteurs
CREATE TABLE auteurs (
    auteur_id    INTEGER PRIMARY KEY AUTOINCREMENT,
    nom          TEXT    NOT NULL,
    prenom       TEXT    NOT NULL,
    nationalite  TEXT
);

-- Livres
CREATE TABLE livres (
    livre_id          INTEGER PRIMARY KEY AUTOINCREMENT,
    isbn              TEXT    NOT NULL UNIQUE,
    titre             TEXT    NOT NULL,
    genre             TEXT,
    annee_publication INTEGER CHECK(annee_publication > 0)
);

-- Table de jonction N-N livres <-> auteurs
CREATE TABLE livre_auteur (
    livre_id   INTEGER NOT NULL REFERENCES livres(livre_id)  ON DELETE CASCADE,
    auteur_id  INTEGER NOT NULL REFERENCES auteurs(auteur_id) ON DELETE CASCADE,
    PRIMARY KEY (livre_id, auteur_id)
);

-- Adhérents
CREATE TABLE adherents (
    adherent_id   INTEGER PRIMARY KEY AUTOINCREMENT,
    nom           TEXT    NOT NULL,
    prenom        TEXT    NOT NULL,
    email         TEXT    UNIQUE,
    date_adhesion TEXT    NOT NULL DEFAULT (date('now'))
);

-- Emprunts
CREATE TABLE emprunts (
    emprunt_id          INTEGER PRIMARY KEY AUTOINCREMENT,
    adherent_id         INTEGER NOT NULL REFERENCES adherents(adherent_id),
    livre_id            INTEGER NOT NULL REFERENCES livres(livre_id),
    date_emprunt        TEXT    NOT NULL DEFAULT (date('now')),
    date_retour_prevue  TEXT    NOT NULL,
    date_retour_reelle  TEXT
);
""")
print("Schéma bibliothèque créé.")

Schéma bibliothèque créé.

# Insertion des données de démonstration
conn.executescript("""
INSERT INTO auteurs (nom, prenom, nationalite) VALUES
    ('Knuth',    'Donald',   'Américain'),
    ('Codd',     'Edgar',    'Britannique'),
    ('Date',     'Chris',    'Britannique'),
    ('Abiteboul','Serge',    'Français'),
    ('Martin',   'Robert C.','Américain');

INSERT INTO livres (isbn, titre, genre, annee_publication) VALUES
    ('978-0-201-89683-1', 'The Art of Computer Programming Vol.1', 'Informatique', 1968),
    ('978-0-201-14165-5', 'An Introduction to Database Systems', 'Base de données', 1975),
    ('978-0-201-53771-8', 'Foundations of Databases', 'Base de données', 1995),
    ('978-0-13-235088-4', 'Clean Code', 'Génie logiciel', 2008);

INSERT INTO livre_auteur VALUES (1,1), (2,3), (2,2), (3,4), (4,5);

INSERT INTO adherents (nom, prenom, email, date_adhesion) VALUES
    ('Dupont',   'Marie',    'marie@lib.fr',   '2022-01-10'),
    ('Martin',   'Paul',     'paul@lib.fr',    '2021-06-15'),
    ('Ahmed',    'Sonia',    'sonia@lib.fr',   '2023-03-01'),
    ('Leroy',    'Thomas',   'thomas@lib.fr',  '2020-09-20');

INSERT INTO emprunts (adherent_id, livre_id, date_emprunt, date_retour_prevue, date_retour_reelle) VALUES
    (1, 1, '2024-01-05', '2024-01-19', '2024-01-17'),
    (2, 3, '2024-01-10', '2024-01-24', NULL),
    (3, 4, '2024-01-12', '2024-01-26', '2024-01-25'),
    (1, 2, '2024-01-20', '2024-02-03', NULL),
    (4, 1, '2024-01-22', '2024-02-05', '2024-02-01');
""")

# Requête de vérification : emprunts en cours
query = """
SELECT
    a.prenom || ' ' || a.nom  AS adherent,
    l.titre,
    e.date_emprunt,
    e.date_retour_prevue,
    CASE WHEN e.date_retour_reelle IS NULL THEN 'En cours' ELSE 'Retourné' END AS statut
FROM emprunts e
JOIN adherents a ON a.adherent_id = e.adherent_id
JOIN livres    l ON l.livre_id    = e.livre_id
ORDER BY e.date_emprunt
"""
df = pd.read_sql(query, conn)
print(df.to_string(index=False))

    adherent                                 titre date_emprunt date_retour_prevue   statut
Marie Dupont The Art of Computer Programming Vol.1   2024-01-05         2024-01-19 Retourné
 Paul Martin              Foundations of Databases   2024-01-10         2024-01-24 En cours
 Sonia Ahmed                            Clean Code   2024-01-12         2024-01-26 Retourné
Marie Dupont   An Introduction to Database Systems   2024-01-20         2024-02-03 En cours
Thomas Leroy The Art of Computer Programming Vol.1   2024-01-22         2024-02-05 Retourné

Visualisation du schéma#

Show code cell source

Hide code cell source

fig, ax = plt.subplots(figsize=(14, 9))
ax.set_xlim(0, 14)
ax.set_ylim(0, 10)
ax.axis("off")
ax.set_facecolor("#fafafa")

tables = {
    "AUTEURS":      (1.0,  8.5, ["auteur_id (PK)", "nom", "prenom", "nationalite"]),
    "LIVRE_AUTEUR": (5.0,  8.5, ["livre_id (FK)", "auteur_id (FK)"]),
    "LIVRES":       (9.0,  8.5, ["livre_id (PK)", "isbn", "titre", "genre", "annee_pub"]),
    "EMPRUNTS":     (9.0,  4.0, ["emprunt_id (PK)", "adherent_id (FK)", "livre_id (FK)",
                                  "date_emprunt", "date_retour_prevue", "date_retour_reelle"]),
    "ADHERENTS":    (1.0,  4.0, ["adherent_id (PK)", "nom", "prenom", "email", "date_adhesion"]),
}

box_w = 3.5
row_h = 0.38
header_h = 0.50
box_centers = {}

for name, (x, y, attrs) in tables.items():
    total_h = header_h + len(attrs) * row_h
    # En-tête
    header_rect = mpatches.FancyBboxPatch((x, y - header_h), box_w, header_h,
        boxstyle="round,pad=0.05", facecolor="#1565c0", edgecolor="#0d47a1", linewidth=1.5)
    ax.add_patch(header_rect)
    ax.text(x + box_w / 2, y - header_h / 2, name,
            ha="center", va="center", color="white", fontsize=10, fontweight="bold")
    # Corps
    body_rect = mpatches.FancyBboxPatch((x, y - total_h), box_w, total_h - header_h,
        boxstyle="round,pad=0.05", facecolor="#e3f2fd", edgecolor="#90caf9", linewidth=1.2)
    ax.add_patch(body_rect)
    for i, attr in enumerate(attrs):
        row_y = y - header_h - (i + 0.5) * row_h
        is_pk = "(PK)" in attr
        is_fk = "(FK)" in attr
        color = "#fff9c4" if is_pk else ("#f3e5f5" if is_fk else "white")
        r = mpatches.FancyBboxPatch((x + 0.08, row_y - row_h / 2 + 0.04),
            box_w - 0.16, row_h - 0.08,
            boxstyle="round,pad=0.02", facecolor=color, edgecolor="#bbdefb", linewidth=0.6)
        ax.add_patch(r)
        ax.text(x + 0.18, row_y, attr, ha="left", va="center", fontsize=8.5)
    # Centre de la table
    box_centers[name] = (x + box_w / 2, y - total_h / 2)

# Flèches FK
links = [
    ("LIVRE_AUTEUR", "AUTEURS",   "auteur_id"),
    ("LIVRE_AUTEUR", "LIVRES",    "livre_id"),
    ("EMPRUNTS",     "LIVRES",    "livre_id"),
    ("EMPRUNTS",     "ADHERENTS", "adherent_id"),
]
arrow_style = dict(arrowstyle="-|>", color="#e53935", lw=1.6,
                   connectionstyle="arc3,rad=0.1")
for src, dst, label in links:
    sx, sy = box_centers[src]
    dx, dy = box_centers[dst]
    ax.annotate("", xy=(dx, dy), xytext=(sx, sy),
                arrowprops=dict(arrowstyle="-|>", color="#e53935", lw=1.8,
                                connectionstyle="arc3,rad=0.05"))

ax.set_title("Schéma relationnel — Bibliothèque", fontsize=13, fontweight="bold", pad=15)

# Légende
legend_items = [
    mpatches.Patch(facecolor="#fff9c4", edgecolor="#aaa", label="Clé primaire (PK)"),
    mpatches.Patch(facecolor="#f3e5f5", edgecolor="#aaa", label="Clé étrangère (FK)"),
    mlines.Line2D([0], [0], color="#e53935", lw=2, marker=">", label="Référence FK"),
]
ax.legend(handles=legend_items, loc="lower left", fontsize=9, framealpha=0.9)

plt.savefig("_static/ch04_schema_bibliotheque.png", dpi=120, bbox_inches="tight")
plt.show()

_images/4b02594101e3379fed2571e8599e6b7e5205a0f9d93d460e82adaecdf69cd17f.png

Bonnes pratiques de naming#

Définition 46 (Conventions de nommage)

Les conventions suivantes sont largement adoptées dans les projets professionnels :

snake_case pour tout : tables, colonnes, index, contraintes (employe_id, date_embauche).
Noms de tables au pluriel : employes, commandes, livres — une table est un ensemble de lignes.
Clés primaires : table_id (ex. livre_id, adherent_id) ou simplement id selon les conventions de l’équipe.
Clés étrangères : même nom que la colonne référencée dans la table parente (livre_id dans emprunts pointe vers livre_id dans livres).
Préfixes ou suffixes informatifs : date_, nb_, est_ pour les booléens (est_actif), montant_ pour les valeurs financières.
Éviter les mots réservés SQL : ne pas appeler une colonne order, user, date, group — préférer commande, utilisateur, date_creation, groupe.

Remarque 15

La cohérence est plus importante que la convention choisie. Une équipe qui utilise systématiquement id comme PK dans toutes ses tables vaut mieux qu’une équipe qui mélange id, ID, table_id et pk_table au gré des développeurs.

Exemple 13 (Bonnes et mauvaises pratiques de nommage)

À éviter :

SELECT comme nom de table, Order (conflit avec le mot-clé), tbl_employe (préfixe redondant).
e ou emp comme nom de colonne sans contexte.
val1, val2, data — non descriptifs.

À préférer :

Tables : employes, lignes_commande, categories_produit.
Colonnes : salaire_brut_mensuel, date_creation, est_archive.
Contraintes : pk_employes, fk_employes_dept, uq_employes_email, chk_employes_salaire.

Résumé#

Ce chapitre a introduit la démarche de modélisation en trois niveaux (MCD, MLD, MPD) et les concepts clés de la modélisation entité-association :

Entités : objets du domaine métier, transformés en tables.
Associations et cardinalités : 1-1, 1-N, N-N — dictent la structure des clés étrangères et des tables de jonction.
Notation Crow’s Foot : représentation graphique des cardinalités dans les diagrammes ER.
Règles MCD → MLD : entité → table, 1-N → FK, N-N → table de jonction.
Cas pratique : schéma complet d’une bibliothèque (auteurs, livres, adhérents, emprunts) avec tables de jonction et contraintes d’intégrité.
Conventions de nommage : snake_case, pluriel pour les tables, clés étrangères nommées comme les colonnes référencées.

Remarque 16

Des outils comme dbdiagram.io, ERDPlus, MySQL Workbench ou pgAdmin permettent de dessiner des diagrammes ER interactifs et de générer le DDL SQL correspondant. L’extension PlantUML permet d’écrire des diagrammes en texte (as code) et de les versionner avec le code source.