#Modélisation relationnelleCopier le lien

La modélisation transforme un énoncé métier en schéma de base de données : quelles tables, quelles colonnes, quelles clés, quels liens. Un bon schéma rend les contraintes explicites pour le moteur, empêche les données contradictoires, et absorbe les évolutions sans réécriture. Un mauvais schéma, à l'inverse, se paie à chaque requête pendant des années.

#Prérequis et objectifsCopier le lien

• Prérequis : savoir lire un tableau lignes/colonnes et distinguer l'identité d'un objet de la valeur d'un attribut.
• Traduire un énoncé en entités, associations et cardinalités.
• Choisir les clés : primaires, candidates, étrangères.
• Passer du diagramme entité-association au SQL CREATE TABLE avec contraintes.

#Le vocabulaire officiel, mot pour motCopier le lien

Ces définitions sont le socle du cours : tout énoncé d'examen les utilise sans les rappeler, et toute question « vrai ou faux » s'y ramène.

• Relation : une table avec des colonnes et des lignes. En termes ensemblistes, une relation est n'importe quel sous-ensemble du produit cartésien des domaines de ses attributs.
• Attribut : une colonne nommée de la relation.
• Domaine : l'ensemble des valeurs admissibles pour un ou plusieurs attributs. C'est ce qui rend prix real différent de prix text, et c'est la contrainte la plus fondamentale du modèle.
• Tuple : une ligne de la relation. En pratique on parle aussi d'« uplet ».
• Degré : le nombre d'attributs d'une relation. users(id, name) est de degré 2.
• Cardinalité : le nombre de tuples d'une relation. C'est un nombre qui change à chaque insertion, contrairement au degré.
• Schéma de relation : une relation nommée définie par un ensemble de paires (attribut, nom de domaine). C'est la partie stable : users(id, name).
• Instance de relation : l'ensemble des tuples présents à un instant donné. C'est la partie volatile.
• Clé candidate : un ensemble minimum d'attributs qui identifie de façon unique un tuple au sein de la relation.
• Clé primaire : la clé candidate choisie pour identifier les tuples. Une relation a plusieurs clés candidates possibles mais une seule clé primaire.
• Clé étrangère : un ensemble d'attributs d'une relation qui correspond à une clé candidate d'une autre relation.

Distinguer degré et cardinalité est un piège d'examen classique, tout comme confondre clé candidate (toutes les clés minimales) et clé primaire (celle qu'on a retenue).

#Les sept propriétés d'une base relationnelleCopier le lien

Une relation n'est pas n'importe quelle table. Elle doit satisfaire :

1. chaque cellule contient exactement une valeur atomique (c'est la 1NF) ;
2. les noms d'attributs sont distincts les uns des autres ;
3. l'ordre des attributs est immatériel — users(id, name) et users(name, id) décrivent la même relation ;
4. l'ordre des tuples est immatériel — sans ORDER BY, aucun moteur ne garantit d'ordre ;
5. il n'existe pas de tuples en double ;
6. dans un tuple, un nul représente une valeur d'attribut inconnue à l'heure actuelle, ou qui ne s'applique pas à ce tuple.

Les points 3 et 4 expliquent pourquoi l'algèbre relationnelle peut traiter une relation comme un ensemble, alors que SQL, qui manipule des sacs, doit offrir DISTINCT et ORDER BY pour retrouver ces garanties.

#Intégrité d'entité et intégrité référentielleCopier le lien

Deux règles que le SGBD fait respecter pour vous :

• Intégrité d'entité : dans une relation de base, aucun attribut qui fait partie de la clé primaire ne peut être nul. Une clé à moitié inconnue n'identifie rien.
• Intégrité référentielle : les valeurs d'une clé étrangère doivent correspondre à une valeur d'une clé candidate d'un tuple dans la relation de référence, ou être complètement nulles. C'est le « ou » qui autorise orders.user_id à valoir NULL quand la commande n'est rattachée à personne, et qui interdit la valeur 99 si aucun utilisateur 99 n'existe.

Le rôle d'une clé étrangère est double : rendre un lien vérifiable par le moteur, et documenter le lien pour le lecteur. Un schéma sans clés étrangères ne garantit rien, il espère.

#Que faire quand la ligne parente disparaît ?Copier le lien

Une clé étrangère déclare aussi le comportement à appliquer sur UPDATE et DELETE de la ligne parente. Quatre politiques, à choisir explicitement :

NO ACTION est le comportement le plus sûr et le plus fréquent : mieux vaut une erreur explicite qu'une suppression en cascade découverte en production. SET NULL exige que la colonne autorise NULL ; CASCADE sur une chaîne de trois tables peut vider beaucoup plus que prévu.

1create table commandes (2  id integer primary key,3  client_id integer references clients(id) on delete cascade on update cascade,4  montant real not null5);

#La démarche en trois étapesCopier le lien

1. Recueillir les règles métier : des énoncés comme « un article possède exactement un auteur », « un article peut porter plusieurs tags », « un email identifie un utilisateur ». Chaque règle deviendra une contrainte ou une table.
2. Dessiner le diagramme conceptuel : entités (des noms), attributs, associations, cardinalités. Trois cas de figure : un-à-un (1-1), un-à-plusieurs (1-N), plusieurs-à-plusieurs (N-N).
3. Passer au relationnel : chaque entité devient une table avec une clé primaire ; chaque association 1-N devient une clé étrangère du côté N ; chaque association N-N devient une table d'association.

#Passer du modèle E-A au relationnel : l'algorithmeCopier le lien

Trois règles suffisent, et elles couvrent tous les cas d'un énoncé :

1. À chaque entité correspond une relation. Les attributs de l'entité deviennent les attributs de la relation, son identifiant devient la clé primaire.
2. Si dans une association A il existe une entité E pour laquelle la cardinalité de (E, A) vaut 1, ajouter dans la relation qui traduit E la clé primaire des autres entités participant à A. C'est le cas du 1-N et du 1-1 : la clé étrangère va du côté « 1 ».
3. S'il n'existe aucune entité E pour laquelle la cardinalité de (E, A) vaut 1, créer une nouvelle relation contenant l'identifiant de chaque entité participant à l'association. C'est le cas du N-N : la table d'association, dont la clé primaire est composite.

Un corollaire utile pour l'examen : une association porteuse d'attributs (une date d'affectation, un rôle) les conserve. Dans le cas 2, ces attributs migrent vers la relation du côté « 1 » ; dans le cas 3, ils deviennent des attributs de la table d'association. Un N-N porteur d'attributs doit devenir une table — c'est le signe qu'on n'a pas le choix.

#Clés : primaires et étrangèresCopier le lien

• Clé primaire (PK) : colonne ou combinaison qui identifie une ligne de façon unique et minimale. Elle ne doit jamais changer de valeur.
• Clés candidates : toutes les combinaisons capables d'identifier une ligne ; on en choisit une comme primaire et on déclare UNIQUE les autres.
• Clé étrangère (FK) : colonne qui référence la clé primaire d'une autre table ; le moteur refuse alors les valeurs orphelines (intégrité référentielle).

#Exemple fil rouge : un blogCopier le lien

Entités : users (les auteurs), posts (les articles), tags. Règles : un article a exactement un auteur (1-N) ; un article peut porter plusieurs tags et un tag s'applique à plusieurs articles (N-N).

1create table users (2  id integer primary key,3  email text unique not null,4  name text not null5);6 7create table posts (8  id integer primary key,9  author_id integer not null references users(id),10  title text not null,11  body text not null,12  published_at text13);14 

La table post_tags porte une clé primaire composite (post_id, tag_id) : elle interdit de mettre deux fois le même tag sur un article et sert d'index pour les jointures. Insérer insert into post_tags values (99, 1) échoue s'il n'existe aucun post 99 : l'intégrité référentielle travaille pour vous.

#Refuser l'impossible : les contraintes utilesCopier le lien

• not null quand l'absence de valeur est une erreur (le titre d'un article).
• unique pour les identifiants métier (email, slug).
• check pour les invariants simples : check (amount >= 0).
• references pour chaque lien entre tables.

Chaque contrainte écrite est un bug que le moteur attrapera à votre place.

#Vérifier son schémaCopier le lien

Quatre questions de contrôle, à poser dans cet ordre :

1. Chaque table a-t-elle une clé primaire ?
2. Chaque colonne qui référence une autre table porte-t-elle une FK ?
3. Une même information est-elle stockée à un seul endroit ?
4. Les cardinalités de l'énoncé sont-elles encodées (FK not null pour le « exactement un », table d'association pour le N-N) ?

#Playground : le schéma en actionCopier le lien

L'éditeur exécute du SQLite dans votre navigateur : Exécuter lance le script, Réinitialiser restaure le code, Nouvelle base repart d'une base propre. Le résultat s'affiche en JSON, une entrée par requête.

L'éditeur ci-dessous exécute du SQLite dans votre navigateur : Exécuter lance le script, Réinitialiser restaure le code, Nouvelle base repart d'une base propre. Le résultat s'affiche en JSON, une entrée par requête.

Comportement attendu : les deux premières insertions passent, la troisième est rejetée avec FOREIGN KEY constraint failed, car aucun utilisateur 99 n'existe. Vos contraintes sont vivantes, pas décoratives.

#Exercice : bibliothèqueCopier le lien

Une bibliothèque décrit son activité : des livres (titre, ISBN), des membres (nom, email), des emprunts (un livre prêté à un membre, date de prêt, date de retour nullable tant que le livre n'est pas rendu). Un livre peut être emprunté plusieurs fois, mais au plus un emprunt en cours à la fois ; un membre peut cumuler les emprunts.

1. Identifiez entités, associations et cardinalités.
2. Écrivez le CREATE TABLE complet avec contraintes.

#Exercice : deux énoncés de TDCopier le lien

Ces deux énoncés sont représentatifs de ce qui est demandé en TD de conception : peu de tables, mais des cardinalités qu'il faut lire attentivement.

Énoncé 1 — les chantiers. Une entreprise a des employés qui travaillent dans des services (maçonnerie, électricité, chauffage) et réalisent des chantiers. Chaque employé a un nom, un prénom et un salaire. Chaque service a un nom. Chaque employé travaille dans un seul service et un service emploie plusieurs employés. Chaque chantier est identifié par un numéro, a une adresse et une date de début. Chaque chantier peut être réalisé par un ou plusieurs employés et chaque employé participe à un ou plusieurs chantiers.

Énoncé 2 — les cinémas. Chaque cinéma est identifié par son nom, qui est unique, et on stocke sa ville et le nom de sa rue. Les salles de chaque cinéma sont numérotées (salle 1, 2, 3…) et on mémorise pour chacune son nombre de places. Chaque film, identifié par un numéro, a un titre, une année de production et une durée en minutes. Pour chaque projection d'un film dans une salle, on mémorise l'heure de début et le tarif de la séance. Un film peut être projeté plusieurs fois dans la même salle. Le tarif dépend du film, de la salle et de l'heure.

#QuizCopier le lien