Merise à l'examen : E6 et E7 SLAM

Au BTS SIO SLAM, la modélisation des données est évaluée dans deux épreuves : l'E6, où tu présentes et fais évoluer la base de tes propres réalisations, et l'E7, l'écrit de cybersécurité, où le sujet te fournit un schéma relationnel à lire, à compléter ou à faire évoluer. Les deux épreuves demandent rarement un MCD complet à partir de rien ; elles demandent surtout de comprendre un schéma existant et de le faire évoluer juste. Ce chapitre donne la méthode pour chaque situation, les pièges qui coûtent des points et une checklist finale.

Où la modélisation tombe

À l'E6 SLAM (oral, bloc 2), la modélisation apparaît à trois moments. Dans ton dossier, les éléments techniques de chaque réalisation montrent son MCD ou son MLD. Pendant l'heure de réalisation, l'expression des besoins te demande presque toujours de toucher aux données : une table à ajouter, une association à transformer, une contrainte à poser. Pendant la recette, la commission vérifie que la modélisation est mise à jour et te demande de justifier tes choix. Le déroulement complet et les évolutions types sont dans le cours Épreuve E6 SLAM.

À l'E7 SLAM (écrit de 4 heures, bloc 3), le dossier documentaire contient presque toujours un schéma relationnel. La plupart des questions s'en servent pour écrire du SQL (requêtes, vues, droits, triggers), et une question au moins demande en général de faire évoluer le schéma. Dans les sujets récents :

Ces questions portent souvent sur la sécurité : des rôles et des droits, un historique, des alertes. Jusqu'à la session 2024, cet écrit s'appelait E6 (U6) : les sujets 2022 à 2024 restent de bons entraînements. Le classement complet des questions par thème est dans le cours Épreuve E7 SLAM.

Lire un schéma relationnel d'annale

Les sujets écrivent le schéma en texte, avec les clés en toutes lettres :

Utilisateur (id, identifiant, empreinteMdp, dateCreation, idProfil)
    Clé primaire : id
    Clé étrangère : idProfil en référence à id de Profil
Profil (id, libelle)
    Clé primaire : id
Permission (id, ressource, action)
    Clé primaire : id
Accorder (idProfil, idPermission)
    Clé primaire : (idProfil, idPermission)
    Clé étrangère : idProfil en référence à id de Profil
    Clé étrangère : idPermission en référence à id de Permission

Avant de répondre à quoi que ce soit, prends deux minutes pour le lire comme un MCD :

1. Repère les tables d'association : celles dont la clé primaire est composée de clés étrangères. Ici, Accorder, qui traduit une association plusieurs à plusieurs entre Profil et Permission.
2. Traduis chaque autre clé étrangère en phrase : idProfil dans Utilisateur signifie « un utilisateur a un seul profil ; un profil est attribué à plusieurs utilisateurs ».
3. Dessine les flèches en marge du sujet, de chaque clé étrangère vers sa table. C'est la carte des jointures dont tu auras besoin pour les requêtes.
4. Note ce que le schéma interdit : un utilisateur ne peut pas avoir deux profils, un profil ne peut pas recevoir deux fois la même permission.

C'est la rétro-conception du chapitre 11, faite de tête. Elle évite la moitié des erreurs de jointure et prépare les questions d'évolution.

Faire évoluer un schéma : la méthode en cinq temps

Une question d'évolution donne un besoin nouveau, en français, et demande le schéma modifié, parfois le script SQL. Applique toujours les mêmes cinq temps.

1. Souligne la règle nouvelle et reformule-la dans les deux sens, avec des quantités : « un utilisateur peut avoir plusieurs profils ; un profil est attribué à plusieurs utilisateurs ».
2. Décide de la nature de l'évolution : une propriété de plus (une colonne), une entité de plus (une table), une association de plus ou modifiée (une clé étrangère ou une table), une historisation.
3. Pose les cardinalités, comme dans un MCD.
4. Traduis avec les règles de passage, en ne réécrivant que les tables créées ou modifiées, et en disant ce qui disparaît.
5. Vérifie : les données existantes peuvent-elles être reprises ? Les requêtes existantes marchent-elles encore ? Une contrainte du sujet reste-t-elle à garantir par un trigger ?

Exemple 1 : interdire la réutilisation des anciens mots de passe. « L'application doit refuser un nouveau mot de passe identique à l'un des cinq précédents de l'utilisateur. »

1. Règle : un utilisateur a plusieurs anciens mots de passe ; un ancien mot de passe appartient à un seul utilisateur.
2. Nature : une nouvelle entité, l'ancien mot de passe, qui n'existe pas sans son utilisateur : une entité faible, historisée par la date de changement.
3. Cardinalités : ANCIEN_MDP (1,1) ── APPARTENIR ── 0,n UTILISATEUR.
4. Traduction :

HistoriqueMdp (idUtilisateur, dateChangement, empreinte)
    Clé primaire : (idUtilisateur, dateChangement)
    Clé étrangère : idUtilisateur en référence à id de Utilisateur

5. Vérification : on stocke l'empreinte, jamais le mot de passe en clair ; la limite de cinq se gère dans le code ou par un trigger qui supprime les plus anciennes lignes. dateChangement doit être une date et une heure, pour qu'un utilisateur puisse changer deux fois de mot de passe dans la journée.

Exemple 2 : plusieurs profils par utilisateur, sur une période. « Un utilisateur peut désormais cumuler plusieurs profils, chacun attribué pour une période, avec une date de début et une date de fin facultative. »

1. Règle : un utilisateur a zéro, un ou plusieurs profils au fil du temps ; un profil est attribué à zéro ou plusieurs utilisateurs ; un même profil peut être réattribué au même utilisateur plus tard.
2. Nature : l'association un à plusieurs devient plusieurs à plusieurs, et historisée.
3. Cardinalités : UTILISATEUR 0,n ── ATTRIBUER (date_fin) ── 0,n PROFIL, avec une date de début dans l'identification.
4. Traduction :

Attribution (idUtilisateur, idProfil, dateDebut, dateFin)
    Clé primaire : (idUtilisateur, idProfil, dateDebut)
    Clé étrangère : idUtilisateur en référence à id de Utilisateur
    Clé étrangère : idProfil en référence à id de Profil
Utilisateur : suppression de la colonne idProfil

5. Vérification : les attributions actuelles doivent être reprises avant de supprimer la colonne. En SQL, l'ordre compte :

CREATE TABLE Attribution (
  idUtilisateur INT  NOT NULL,
  idProfil      INT  NOT NULL,
  dateDebut     DATE NOT NULL,
  dateFin       DATE,
  PRIMARY KEY (idUtilisateur, idProfil, dateDebut),
  FOREIGN KEY (idUtilisateur) REFERENCES Utilisateur (id),
  FOREIGN KEY (idProfil) REFERENCES Profil (id)
);

-- Reprendre le profil actuel de chaque utilisateur
INSERT INTO Attribution (idUtilisateur, idProfil, dateDebut)
SELECT id, idProfil, DATE(dateCreation) FROM Utilisateur;

-- Supprimer l'ancienne clé étrangère, puis la colonne (nom de contrainte donné par SHOW CREATE TABLE)
ALTER TABLE Utilisateur DROP FOREIGN KEY fk_utilisateur_profil;
ALTER TABLE Utilisateur DROP COLUMN idProfil;

DATE() garde la date seule si dateCreation contient aussi une heure. Toutes les requêtes qui joignaient Utilisateur et Profil par idProfil doivent maintenant passer par Attribution, en filtrant les attributions en cours (dateFin IS NULL OR dateFin >= CURRENT_DATE). C'est le genre de conséquence qu'une commission d'E6 aime te voir anticiper.

Rédiger la réponse sur papier

• Écris le schéma en texte, au format du sujet, avec les clés primaires et étrangères en toutes lettres. Si le sujet dit « dans le formalisme de votre choix », c'est la forme la plus sûre.
• Ne réécris que ce qui change : les tables créées, les tables modifiées (avec les colonnes ajoutées ou supprimées), comme le demandent les énoncés.
• Justifie chaque choix en une phrase, en citant la règle de gestion : « la clé primaire inclut dateDebut parce qu'un même profil peut être réattribué au même utilisateur ».
• Garde les noms du sujet : sa casse (idProfil, pas id_profil), ses noms de tables. Un correcteur doit pouvoir comparer ta réponse au schéma d'origine sans effort.
• Signale ce que le schéma ne garantit pas (« la limite de cinq mots de passe sera vérifiée par l'application »). C'est une remarque que les correcteurs valorisent.

Présenter ta modélisation à l'E6

• Mets dans ton dossier un MCD ou un MLD lisible (exporté d'un logiciel, pas une photo), et le dictionnaire des données s'il est utile.
• Prépare la justification de chaque cardinalité et de chaque table d'association. La question « pourquoi une table plutôt qu'une colonne ? » revient souvent : la réponse est presque toujours une cardinalité (« une note de frais peut être renvoyée plusieurs fois, il faut une ligne par renvoi ») ou un besoin d'historique.
• Pendant l'heure de réalisation, modifie d'abord le modèle, puis le script, puis le code. Cinq minutes de MCD évitent trente minutes de code à refaire.
• Garde un script de migration (le CREATE TABLE, la reprise des données, l'ALTER TABLE) plutôt que des clics dans phpMyAdmin : il se montre, se rejoue et se versionne avec Git.

Les pièges qui coûtent des points

1. La clé étrangère du mauvais côté d'une association un à plusieurs.
2. Une association plusieurs à plusieurs traduite par une simple clé étrangère, qui ne permet qu'un seul lien.
3. Une clé primaire de table d'association trop courte, qui interdit l'historique (oublier dateDebut), ou trop longue, qui autorise des doublons absurdes.
4. Des cardinalités inversées, ou lues à l'envers à partir d'un diagramme UML.
5. Une donnée calculée stockée (un total, un âge, un compteur) sans justification.
6. Une propriété rangée au mauvais endroit : le libellé du genre dans le document, la quantité dans le produit.
7. Un mot de passe stocké en clair, ou chiffré de façon réversible, dans une table d'authentification : à l'E7, une telle réponse ne peut pas rapporter les points de la question.
8. Un schéma réécrit entièrement alors que seules deux tables changeaient : du temps perdu et des risques d'erreurs de recopie.
9. Une évolution qui ignore les données existantes : une colonne NOT NULL ajoutée à une table pleine, sans valeur par défaut, échoue.
10. Des noms qui changent entre le schéma, le SQL et le code.

La checklist finale

Pour un MCD : chaque entité a un identifiant souligné ; chaque propriété est élémentaire, atomique, unique dans le schéma ; chaque association a un verbe et deux cardinalités justifiées par une phrase ; les associations porteuses relient des entités par des maximums n ; les réflexives ont des rôles ; aucune clé étrangère n'apparaît.

Pour un MLD : une table par entité ; une clé étrangère du côté du maximum 1 de chaque association un à plusieurs ; une table par association plusieurs à plusieurs ou ternaire, avec une clé primaire composée ; des noms de rôle pour les clés étrangères multiples vers une même table ; les tables en 3FN.

Pour le SQL : les tables référencées créées en premier ; des types cohérents (DECIMAL pour les montants, CHAR pour les codes, DATE pour les dates) ; NOT NULL pour chaque cardinalité 1,1 et chaque propriété obligatoire ; UNIQUE pour les identifiants naturels et les associations un à un ; les CHECK des listes de valeurs ; les actions ON DELETE choisies d'après les règles de gestion.

Exercices

Compte (id, identifiant, empreinteMdp, actif)
    Clé primaire : id
Connexion (id, dateHeure, adresseIp, reussie, idCompte)
    Clé primaire : id
    Clé étrangère : idCompte en référence à id de Compte