Du schéma aux tables
Traduire le dessin en tables, clés et types de données.
Après cette fiche, vous saurez :
- transformer une entité en table
- traduire une relation 1-n par une clé étrangère
- traduire une relation n-n par une table de liaison
- choisir le type de données de chaque colonne
1Trois règles, et c'est tout
Le passage du schéma aux tables suit trois règles mécaniques. Une fois qu'on les connaît, la traduction se fait sans réfléchir.
| Sur le schéma | Devient |
|---|---|
| Une entité | Une table. L'identifiant devient la clé primaire. |
| Une relation 1-n | Une clé étrangère dans la table du côté n. |
| Une relation n-n | Une table de liaison supplémentaire. |
2Règle 1 : une entité devient une table
C'est la règle facile. Le rectangle devient une table, les attributs deviennent des colonnes, l'identifiant souligné devient la clé primaire.
À retenir
La clé primaire (en anglais primary key, abrégée PK) est la colonne qui identifie chaque ligne.
Le SGBD la surveille : il refusera d'insérer deux lignes avec la même valeur, et il refusera une ligne sans valeur.
On écrit le résultat sous forme de schéma relationnel, avec la clé primaire soulignée :
AUTEUR (num_auteur, nom, prenom, nationalite) LIVRE (num_livre, titre, annee, isbn) LECTEUR (num_lecteur, nom, prenom, adresse, telephone)
C'est le document qu'on rend à la fin de l'analyse. Il tient sur une demi-page et suffit à créer toute la base.
3Règle 2 : le 1-n devient une clé étrangère
Rappel de la fiche 3 : un auteur écrit plusieurs livres, un livre a un seul auteur.
Pour relier les deux tables, on ajoute dans LIVRE une colonne qui contient le numéro de l'auteur.
AUTEUR (num_auteur, nom, prenom, nationalite) LIVRE (num_livre, titre, annee, isbn, #num_auteur)
Le # signale une clé étrangère (en anglais foreign key,
abrégée FK) : une colonne qui pointe vers la clé primaire d'une autre table.
| num_livre | titre | #num_auteur |
|---|---|---|
| 1 | Le Petit Prince | 1 |
| 2 | Germinal | 2 |
| 3 | L'Assommoir | 2 |
| num_auteur | nom | prenom |
|---|---|---|
| 1 | Saint-Exupéry | Antoine de |
| 2 | Zola | Émile |
Les livres 2 et 3 contiennent tous deux 2. Ils sont donc tous deux de
Zola, et « Zola » n'est écrit qu'une seule fois.
Attention
Où placer la clé étrangère ? Toujours du côté du n.
C'est la question qui revient à chaque examen. La réponse est mécanique : la clé étrangère va du côté qui ne peut en avoir qu'un.
Un livre a un seul auteur → on peut donc écrire un seul numéro d'auteur dans la ligne du livre. Ça rentre.
Un auteur a plusieurs livres → on ne peut pas écrire plusieurs numéros de livre dans une seule case. Ça ne rentre pas.
Le test est là : de quel côté est-ce que ça rentre dans une seule case ?
4Règle 3 : le n-n devient une table de liaison
Rappel : un lecteur emprunte plusieurs livres, un livre est emprunté par plusieurs lecteurs.
Ici, la clé étrangère ne rentre nulle part : ni d'un côté ni de l'autre. On crée donc une troisième table, dont le seul rôle est de relier les deux.
LECTEUR (num_lecteur, nom, prenom, adresse, telephone) LIVRE (num_livre, titre, annee, isbn, #num_auteur) EMPRUNT (num_emprunt, date_emprunt, date_prevue, date_retour, #num_lecteur, #num_livre)
La table EMPRUNT contient deux clés étrangères : une vers chaque côté.
| num_emprunt | date_emprunt | #num_lecteur | #num_livre |
|---|---|---|---|
| 1 | 12/09/2026 | 1 | 1 |
| 2 | 12/09/2026 | 1 | 2 |
| 3 | 15/09/2026 | 2 | 2 |
Lecteur 1 a emprunté les livres 1 et 2. Le livre 2 a été emprunté par les lecteurs 1 et 2. Le n-n fonctionne dans les deux sens.
À retenir
Souvenez-vous de la fiche 3 : une relation n-n porte souvent des informations à elle.
C'est ici qu'elles trouvent leur place. Les trois dates n'appartiennent ni au lecteur ni au livre : elles appartiennent à l'emprunt.
C'est d'ailleurs pour cela qu'on donne un vrai nom à cette table —
EMPRUNT — plutôt qu'un nom bricolé comme
lecteur_livre.
Attention
Une table de liaison a besoin d'une clé primaire.
Deux façons de faire :
- un numéro à elle, comme
num_emprunt— le plus simple, et c'est ce que nous ferons ; - la combinaison des deux clés étrangères — possible, mais alors le même lecteur ne pourra jamais réemprunter le même livre.
Dans une bibliothèque, on veut évidemment pouvoir réemprunter. Le numéro propre s'impose.
5Choisir le type de chaque colonne
Dernière étape avant de créer la base : dire, pour chaque colonne, quelle sorte de donnée elle contient.
| Type PostgreSQL | Contient | Exemple |
|---|---|---|
INTEGER | Un nombre entier | un numéro, une quantité, une année |
VARCHAR(n) | Du texte, au maximum n caractères | VARCHAR(50) pour un nom |
TEXT | Du texte, sans limite | un résumé, un commentaire |
DATE | Une date, sans heure | une date de naissance |
TIMESTAMP | Une date et une heure | un horodatage |
NUMERIC(8,2) | Un nombre décimal exact | un prix, un montant |
BOOLEAN | Vrai ou faux | livre disponible ou non |
Attention
Trois erreurs très fréquentes.
- Un numéro de téléphone n'est pas un nombre. Il commence par un zéro, qui
disparaîtrait, et il peut contenir des espaces ou un « + ». C'est du
VARCHAR. - Un montant n'est pas un nombre à virgule flottante. Utilisez
NUMERIC, qui est exact. Nous en reparlerons. - Une date n'est pas du texte. En
VARCHAR, vous ne pourrez ni trier chronologiquement, ni calculer un retard.
Le dossier d'analyse complet
Voici ce que vous devez être capable de produire à la fin d'une analyse.
| Table | Colonne | Type | Remarque |
|---|---|---|---|
| AUTEUR | num_auteur | INTEGER | clé primaire |
| nom | VARCHAR(50) | obligatoire | |
| prenom | VARCHAR(50) | ||
| nationalite | VARCHAR(40) | ||
| LIVRE | num_livre | INTEGER | clé primaire |
| titre | VARCHAR(150) | obligatoire | |
| annee | INTEGER | ||
| isbn | VARCHAR(20) | unique | |
| num_auteur | INTEGER | clé étrangère vers AUTEUR | |
| LECTEUR | num_lecteur | INTEGER | clé primaire |
| nom | VARCHAR(50) | obligatoire | |
| prenom | VARCHAR(50) | ||
| telephone | VARCHAR(20) | texte, pas un nombre | |
| EMPRUNT | num_emprunt | INTEGER | clé primaire |
| date_emprunt | DATE | obligatoire | |
| date_prevue | DATE | ||
| date_retour | DATE | vide tant que non rendu | |
| num_lecteur | INTEGER | clé étrangère vers LECTEUR | |
| num_livre | INTEGER | clé étrangère vers LIVRE |
À retenir
Ce tableau est le dossier d'analyse. C'est lui qu'on rend, et c'est lui qu'on évalue.
À partir de là, écrire le SQL est un travail mécanique — c'est l'objet des fiches 6 et 7. Tout le travail intelligent a déjà été fait.
→Exercices
Voici un schéma de garage.
CLIENT ---- 1,1 ------ possède ------ 0,n ---- VOITURE
CLIENT a : num_client, nom, telephone
VOITURE a : num_voiture, plaque, marque, annee
- Dans quelle table faut-il mettre la clé étrangère ? Pourquoi ?
- Écrivez le schéma relationnel des deux tables.
- Donnez le type de chaque colonne.
Solution
1. Dans VOITURE.
Le test de la fiche : une voiture appartient à un seul client, donc un seul numéro de client rentre dans la ligne de la voiture. Dans l'autre sens, un client peut avoir plusieurs voitures : impossible de les faire tenir dans une seule case.
2. Le schéma relationnel
CLIENT (num_client, nom, telephone) VOITURE (num_voiture, plaque, marque, annee, #num_client)
3. Les types
| Colonne | Type | Pourquoi |
|---|---|---|
| num_client | INTEGER | un numéro |
| nom | VARCHAR(50) | du texte court |
| telephone | VARCHAR(20) | du texte : commence par 0 |
| num_voiture | INTEGER | un numéro |
| plaque | VARCHAR(15) | contient des lettres |
| marque | VARCHAR(40) | du texte |
| annee | INTEGER | un nombre entier |
| num_client | INTEGER | même type que la clé qu'elle vise |
Règle à retenir : une clé étrangère a toujours le même type que la clé primaire vers laquelle elle pointe. Sinon la liaison est impossible.
Une école organise des ateliers.
ÉLÈVE ---- 0,n ------ s'inscrit à ------ 0,n ---- ATELIER
On veut aussi garder, pour chaque inscription : la date d'inscription, et si l'élève a payé.
- Combien de tables faut-il ?
- Écrivez le schéma relationnel complet.
- Où placer « date d'inscription » et « a payé » ? Pourquoi pas ailleurs ?
Solution
1. Trois tables. Les deux entités, plus la table de liaison.
2. Le schéma relationnel
ELEVE (num_eleve, nom, prenom, telephone) ATELIER (num_atelier, libelle, jour, prix) INSCRIPTION (num_inscription, date_inscription, a_paye, #num_eleve, #num_atelier)
3. Dans INSCRIPTION. Et il n'y a pas d'autre endroit possible.
- Pas dans ELEVE : un élève inscrit à trois ateliers aurait trois dates différentes. Impossible dans une seule case.
- Pas dans ATELIER : même problème dans l'autre sens. Un atelier a autant de dates d'inscription que d'inscrits.
La date d'inscription ne caractérise ni l'élève, ni l'atelier : elle caractérise le lien entre les deux. Sa place est donc dans la table de liaison.
C'est le test à retenir pour toute information dont vous ne savez pas où la mettre : de quoi parle-t-elle exactement ?
Types : date_inscription en DATE,
a_paye en BOOLEAN, prix en
NUMERIC(6,2).
Reprenez votre schéma de la fiche 3, celui avec les cardinalités.
- Appliquez les trois règles et écrivez le schéma relationnel complet.
- Construisez le tableau du dossier d'analyse : table, colonne, type, remarque.
- Vérifiez que chaque clé étrangère a bien le même type que la clé qu'elle vise.
Ce document sera votre plan de travail pour toutes les fiches suivantes. Soignez-le.
Solution
Exemple avec le cabinet médical, dont la relation était un n-n.
PATIENT (num_patient, nom, prenom, date_naissance, telephone) MEDECIN (num_medecin, nom, prenom, specialite, numero_inami) CONSULTATION (num_consultation, date_consult, heure, motif, montant, #num_patient, #num_medecin)
| Table | Colonne | Type | Remarque |
|---|---|---|---|
| PATIENT | num_patient | INTEGER | clé primaire |
| nom | VARCHAR(50) | obligatoire | |
| date_naissance | DATE | pas du texte | |
| telephone | VARCHAR(20) | pas un nombre | |
| MEDECIN | num_medecin | INTEGER | clé primaire |
| specialite | VARCHAR(60) | ||
| numero_inami | VARCHAR(20) | unique | |
| CONSULTATION | num_consultation | INTEGER | clé primaire |
| date_consult | DATE | obligatoire | |
| montant | NUMERIC(6,2) | de l'argent | |
| num_patient | INTEGER | clé étrangère vers PATIENT | |
| num_medecin | INTEGER | clé étrangère vers MEDECIN |
Les quatre points à vérifier dans votre travail :
- Chaque table a une clé primaire soulignée.
- Chaque 1-n a produit une clé étrangère, du côté du n.
- Chaque n-n a produit une table supplémentaire, avec deux clés étrangères.
- Chaque clé étrangère a le même type que sa clé primaire.
Un piège fréquent : avoir mis specialite dans une table à part.
Ce n'est pas faux — c'est même mieux si l'on veut une liste fermée de
spécialités. Mais ça ajoute une table et une relation 1-n. À vous de juger si le jeu
en vaut la chandelle.
Je sais faire
- Je sais transformer une entité en table.
- Je sais où placer la clé étrangère dans une relation 1-n.
- Je sais créer une table de liaison pour une relation n-n.
- Je sais choisir le type de chaque colonne.
- Je sais écrire un schéma relationnel.
Une case qui coince ? Relisez la partie concernée avant de passer à la fiche suivante.