Héritage, contraintes et historisation

L'héritage, appelé aussi spécialisation ou généralisation, regroupe dans une entité générique les propriétés communes à plusieurs entités, et garde dans des entités spécialisées ce qui est propre à chacune : un livre, un film et un jeu vidéo sont tous des documents. Les contraintes entre associations précisent ce que les cardinalités ne savent pas dire, comme « une animation est assurée par un bibliothécaire ou par un intervenant extérieur, jamais les deux ». L'historisation, enfin, garde la trace des valeurs passées. Ces trois outils viennent de Merise/2 ; ce chapitre les présente avec leur traduction en tables.

Spécialisation et généralisation

La RG13 de Rivelire dit qu'on connaît l'ISBN et le nombre de pages d'un livre, la durée d'un film, la plateforme et la classification PEGI d'un jeu. Ces propriétés ne concernent qu'une partie des documents. Si on les range toutes dans DOCUMENT, un film aura un ISBN vide et un livre une plateforme vide.

L'héritage règle le problème. On garde une entité générique (on dit aussi entité mère, ou surtype), DOCUMENT, avec ce qui est commun à tous, et on crée des entités spécialisées (entités filles, ou sous-types), LIVRE, FILM et JEU, avec ce qui est propre à chacune.

• DOCUMENT (num_document, titre, annee_parution, resume)
• LIVRE (isbn, nb_pages)
• FILM (duree_min)
• JEU (plateforme, pegi)

Les entités spécialisées héritent de l'identifiant et des propriétés de l'entité générique : un livre a un numéro, un titre, une année de parution, un résumé, un ISBN et un nombre de pages. Elles héritent aussi de ses associations : un livre a des exemplaires, des contributeurs, un genre, parce qu'il est un document. Une entité spécialisée n'a donc pas d'identifiant propre, et elle peut avoir ses propres associations : « un jeu est édité par un studio » ne concernerait que JEU.

On parle de spécialisation quand on part de l'entité générique pour la découper (les documents, dont certains sont des livres), et de généralisation quand on part d'entités semblables pour les regrouper (les médecins et les infirmiers ont en commun un matricule, un nom, une date d'embauche : on crée PERSONNEL). Le résultat est le même.

Totalité, exclusion, partition

Une spécialisation porte presque toujours une contrainte, qui dit comment les occurrences de l'entité générique se répartissent entre les entités spécialisées.

À Rivelire, la contrainte est une partition : tout document est soit un livre, soit un film, soit un jeu.

Traduire l'héritage dans le MLD : trois solutions

Le modèle relationnel ne connaît pas l'héritage. Il existe trois façons de le traduire, chacune avec ses avantages ; le choix se justifie par l'usage des données.

Solution 1 : une table par entité. La table générique garde les colonnes communes ; chaque table spécialisée a pour clé primaire une clé étrangère vers la table générique.

document (num_document, titre, annee_parution, resume, #code_genre)

livre (#num_document, isbn, nb_pages)

film (#num_document, duree_min)

jeu (#num_document, plateforme, pegi)

C'est la traduction la plus fidèle : aucune colonne vide, chaque association garde sa cible (EXEMPLAIRE pointe vers document, une éventuelle association propre aux jeux pointe vers jeu). Son défaut : il faut une jointure pour afficher un livre complet, et rien n'empêche d'enregistrer le même document à la fois dans livre et dans film. Pour faire respecter l'exclusion, il faut un trigger, ou une colonne type_document dans document que l'application consulte avant d'insérer.

En SQL, la clé primaire de la table spécialisée n'est pas auto-incrémentée : elle reprend le numéro du document.

CREATE TABLE livre (
  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
);

Solution 2 : une seule table. Toutes les colonnes remontent dans la table générique, avec une colonne qui indique le type.

document (num_document, titre, annee_parution, resume, type_document, isbn, nb_pages, duree_min, plateforme, pegi, #code_genre)

Les requêtes sont simples (pas de jointure) et la partition est facile à imposer : type_document est obligatoire et ne prend qu'une valeur. En contrepartie, la table contient beaucoup de valeurs vides, les colonnes propres à un type ne peuvent pas être déclarées NOT NULL, et la cohérence (pas d'ISBN pour un film) se vérifie par des contraintes CHECK :

type_document VARCHAR(5) NOT NULL,
CONSTRAINT chk_type CHECK (type_document IN ('livre', 'film', 'jeu')),
CONSTRAINT chk_livre CHECK (type_document = 'livre' OR (isbn IS NULL AND nb_pages IS NULL))

Cette solution convient quand les entités spécialisées ont peu de propriétés propres et aucune association propre.

Solution 3 : les tables spécialisées seules. Les colonnes communes descendent dans chaque table spécialisée, et la table générique disparaît.

livre (num_document, titre, annee_parution, resume, isbn, nb_pages, #code_genre)

film (num_document, titre, annee_parution, resume, duree_min, #code_genre)

jeu (num_document, titre, annee_parution, resume, plateforme, pegi, #code_genre)

Elle n'est possible qu'avec une contrainte de totalité : un document qui ne serait ni livre, ni film, ni jeu n'aurait plus de table. Et elle pose un problème dès qu'une association vise l'entité générique : la table exemplaire devrait référencer trois tables à la fois, ce qu'une clé étrangère ne sait pas faire. Elle est donc exclue à Rivelire, où exemplaires, contributions et réservations concernent tous les documents.

Rivelire retient la solution 1 : les trois types de documents ont leurs propriétés, et les associations visent tous les documents.

Les contraintes entre associations

Les cardinalités décrivent chaque association séparément. Certaines règles portent sur plusieurs associations à la fois, autour d'une même entité, appelée le pivot. Merise/2 les note par un petit cercle contenant une lettre, relié par des pointillés aux associations concernées.

Prenons les animations de Rivelire (ateliers, rencontres d'auteurs) : « une animation est assurée soit par un bibliothécaire du réseau, soit par un intervenant extérieur, jamais par les deux ».

ANIMATION 0,1 ──── ASSURER_BIBLIO ──── 0,n BIBLIOTHECAIRE
ANIMATION 0,1 ──── ASSURER_EXTERNE ──── 0,n INTERVENANT
contrainte XT entre ASSURER_BIBLIO et ASSURER_EXTERNE, pivot ANIMATION

Les cardinalités seules (0,1 et 0,1) autoriseraient une animation sans animateur, ou avec les deux. La contrainte XT impose exactement un des deux liens.

Dans la base. Quand les deux associations donnent deux clés étrangères facultatives dans la même table, une contrainte CHECK suffit. Pour la partition des animations :

CONSTRAINT chk_animateur CHECK (
  (num_bibliothecaire IS NOT NULL AND num_intervenant IS NULL)
  OR (num_bibliothecaire IS NULL AND num_intervenant IS NOT NULL)
)

L'inclusion, quand les deux associations deviennent des tables, se traduit élégamment par une clé étrangère composée : la table noter référence le couple (num_etudiant, code_matiere) de la table suivre, au lieu de référencer séparément etudiant et matiere. Les autres cas (contraintes entre tables différentes) demandent un trigger ou un contrôle dans l'application.

Historiser les données

Historiser, c'est garder la trace des valeurs passées d'une information au lieu de les écraser : les emprunts successifs d'un exemplaire, les tarifs successifs d'un abonnement, les services successifs d'un salarié. C'est indispensable pour les statistiques, pour la facturation (on facture au prix de l'époque), pour l'audit et la sécurité (qui a modifié quoi, et quand), et parfois imposé par la loi.

Le piège : une association binaire ne peut pas contenir deux fois le même couple d'occurrences (chapitre 4). Dès qu'un même couple peut se répéter dans le temps, il faut une autre construction. Il en existe trois.

1. Une entité avec son propre identifiant. C'est le choix de Rivelire pour les emprunts : EMPRUNT (num_emprunt, date_emprunt, date_retour_prevue, date_retour), reliée à ADHERENT et à EXEMPLAIRE par deux associations 1,1 — 0,n. Un même adhérent peut emprunter dix fois le même exemplaire : ce sont dix emprunts, avec dix numéros. C'est la solution la plus simple et la plus souple.

2. Une association avec une entité DATE. On ajoute une entité DATE, réduite à son identifiant, et l'association devient ternaire : ADHERENT, EXEMPLAIRE, DATE. Une occurrence est alors un triplet (adhérent, exemplaire, jour), et la même paire peut se répéter à des jours différents. Par convention, l'entité DATE ne devient pas une table : la date entre directement dans la clé primaire de la table d'association.

emprunter (#num_adherent, #code_barre, date_emprunt, date_retour_prevue, date_retour)

Cette solution interdit deux emprunts du même exemplaire par le même adhérent le même jour, ce qui est acceptable ici, mais elle alourdit la clé primaire. On la rencontre dans beaucoup de sujets et de corrigés : sache la lire.

3. Une table d'historique pour une propriété. Quand une seule propriété évolue (le prix d'un abonnement, l'adresse d'un client), on la sort dans une entité identifiée relativement à l'entité concernée et à une date de début :

TARIF (1,1) ──── CONCERNER ──── 1,n ABONNEMENT
TARIF (date_debut, prix_mensuel), identifié relativement à ABONNEMENT

tarif (#code_abonnement, date_debut, prix_mensuel)

Le prix en vigueur est celui de la ligne dont la date_debut est la plus récente sans dépasser la date du jour. Certaines notations de Merise/2 marquent une propriété historisée par la lettre (H) dans le MCD ; au MLD, elle donne une table de ce type.

Dans les sujets d'E7 SLAM, l'historisation apparaît souvent côté sécurité : garder les anciennes empreintes de mot de passe pour interdire leur réutilisation, tracer chaque modification d'une donnée sensible dans une table de journal alimentée par un trigger (chapitre Triggers du cours Procédures stockées et triggers, et cours épreuve E7 SLAM).

Exercices

CONSTRAINT chk_payeur CHECK (num_adherent IS NULL OR num_collectivite IS NULL)