Corrigé : Entraînement sur les Schémas Relationnels

1. Tables initiales (Sandwicherie)

Question 1 : Analyse des clés

Clés primaires :

Table	Clé primaire	Justification
Sandwichs	`Nom_Sandwich`	Identifie de manière unique chaque sandwich
Clients	`ID_Client`	Identifiant unique pour chaque client
Commandes	`ID_Commande`	Identifiant unique pour chaque commande

Clés étrangères :

Table	Clé étrangère	Référence
Commandes	`ID_Client`	→ Clients(ID_Client)
Commandes	`Nom_Sandwich`	→ Sandwichs(Nom_Sandwich)

Question 2 : Problèmes de modélisation

Problèmes identifiés :

Clé primaire de Sandwichs : Utiliser Nom_Sandwich comme clé primaire pose problème si deux sandwichs ont le même nom ou si on renomme un sandwich. Il vaudrait mieux ajouter un ID_Sandwich.
Redondance potentielle : Si un client commande plusieurs fois le même sandwich, le nom du sandwich est répété.
Pas de gestion des quantités multiples : Une commande ne peut contenir qu'un seul type de sandwich. Si un client veut commander 2 Cheeseburgers ET 1 Italien, il faut 2 commandes.

Schéma amélioré :

Sandwichs(#ID_Sandwich, Nom_Sandwich, Type, Prix)
Clients(#ID_Client, Nom, Prénom, Adresse)
Commandes(#ID_Commande, Date, ID_Client*)
Lignes_Commande(#ID_Commande*, #ID_Sandwich*, Quantité)

Avec cette structure : - Une commande peut contenir plusieurs sandwichs différents - On utilise des identifiants numériques comme clés primaires

3. Modélisation d'une base pour un forum

Question 1 : Schéma de la table Users

Users(#ID_User, Pseudonyme, Email, Role, Date_Inscription)

ID_User : clé primaire (entier auto-incrémenté)
Pseudonyme : chaîne de caractères, UNIQUE
Email : chaîne de caractères, UNIQUE
Role : chaîne de caractères (ex: "membre", "modérateur", "admin")
Date_Inscription : date

Question 2 : Schéma de la table Posts

Posts(#ID_Post, Titre, Contenu, Date_Publication, ID_User*)

ID_Post : clé primaire
Titre : chaîne de caractères
Contenu : texte long
Date_Publication : date et heure
ID_User : clé étrangère vers Users

Question 3 : Clés primaires et étrangères

Table	Clé primaire	Clé(s) étrangère(s)
Users	`ID_User`	Aucune
Posts	`ID_Post`	`ID_User` → Users(ID_User)

Question 4 : Gestion des modifications de pseudonymes

Problème : Si on utilise le pseudonyme comme référence dans d'autres tables, tout changement de pseudo nécessiterait de modifier toutes les références.

Solution : Utiliser ID_User (clé primaire numérique) comme référence dans les autres tables. Ainsi, le pseudonyme peut être modifié librement dans la table Users sans impacter les autres tables.

C'est pourquoi on préfère toujours utiliser un identifiant numérique comme clé primaire plutôt qu'un attribut "métier" comme le pseudonyme.

4. Extension : Albums sur le forum

Question 1 : Modèle Entité-Association

    ┌─────────────────┐                           ┌─────────────────┐
    │      USER       │                           │      POST       │
    ├─────────────────┤                           ├─────────────────┤
    │ #ID_User (PK)   │                           │ #ID_Post (PK)   │
    │  Pseudonyme     │         1,n               │  Titre          │
    │  Email          │◄──────────────────────────┤  Contenu        │
    └────────┬────────┘        écrit              │  Date           │
             │                                    └────────┬────────┘
             │ 1,n                                         │ 0,n
             │                                             │
             │         ┌─────────────────┐                 │
             └────────►│     ALBUM       │                 │
                       ├─────────────────┤                 │
            possède    │ #ID_Album (PK)  │     contient    │
                       │  Nom_Album      │◄────────────────┘
                       │  Date_Creation  │       0,n
                       └─────────────────┘

Cardinalités : - Un utilisateur possède 0 à n albums (0,n) - Un album appartient à 1 et 1 seul utilisateur (1,1) - Un album contient 0 à n posts (0,n) - Un post peut être dans 0 à n albums (0,n)

Question 2 : Schéma Relationnel

Users(#ID_User, Pseudonyme, Email, Role)
Posts(#ID_Post, Titre, Contenu, Date, ID_User*)
Albums(#ID_Album, Nom_Album, Date_Creation, ID_User*)
Album_Posts(#ID_Album*, #ID_Post*)

La table Album_Posts est une table de liaison nécessaire pour la relation n-n entre Albums et Posts.

Question 3 : Exemple d'enregistrements

Table Users :

ID_User	Pseudonyme	Email	Role
1	GameMaster42	gm42@mail.com	membre

Table Posts :

ID_Post	Titre	Contenu	Date	ID_User
101	Mon avis sur Zelda	Super jeu...	2026-01-15	1
102	Guide débutant	Voici mes conseils...	2026-01-16	1

Table Albums :

ID_Album	Nom_Album	Date_Creation	ID_User
1	Mes meilleurs posts gaming	2026-01-17	1

Table Album_Posts :

ID_Album	ID_Post
1	101
1	102

5. Normalisation : Exemple pour un lycée

Question 1 : Schéma relationnel actuel

Eleves(Nom, Prénom, Date_Naissance, Classe, Option1, Option2, Option3)

Question 2 : Clé primaire et clés étrangères

Clé primaire : Il n'y a pas de clé primaire clairement définie. On pourrait utiliser (Nom, Prénom, Date_Naissance) mais ce n'est pas idéal (deux élèves peuvent avoir le même nom et être nés le même jour).

Clés étrangères : Aucune. La table est isolée.

Question 3 : Défauts de conception

Pas de clé primaire fiable : Le couple (Nom, Prénom) n'est pas unique (deux "Michel" existent).
Violation de 1NF : Les colonnes Option1, Option2, Option3 représentent la même information (une option). C'est une répétition de groupe.
Valeurs NULL : Beaucoup de valeurs NULL pour les options non choisies.
Pas de normalisation : La classe est répétée pour chaque élève de la même classe.
Rigidité : Si un élève prend 4 options, il faut modifier la structure de la table.

Amélioration : Schéma normalisé

Eleves(#ID_Eleve, Nom, Prénom, Date_Naissance, ID_Classe*)
Classes(#ID_Classe, Nom_Classe)
Options(#ID_Option, Nom_Option)
Eleves_Options(#ID_Eleve*, #ID_Option*)

Tables résultantes :

Table Eleves :

ID_Eleve	Nom	Prénom	Date_Naissance	ID_Classe
1	Alan	Michel	12/12/2005	1
2	Bergue	John	13/01/2006	1
3	Zidane	Michel	12/12/2005	2
4	Bergue	Inès	06/04/2004	3

Table Classes :

ID_Classe	Nom_Classe
1	2de1
2	1S2
3	T-STL

Table Options :

ID_Option	Nom_Option
1	CIT
2	Chinois
3	Latin
4	Maths
5	Physique
6	NSI

Table Eleves_Options :

ID_Eleve	ID_Option
1	1
1	2
2	1
2	2
2	3
3	4
3	5
3	6

Avantages du nouveau schéma : - Chaque élève a un identifiant unique - Plus de colonnes Option1/2/3 : on peut avoir n'importe quel nombre d'options - Les noms de classes ne sont plus répétés - Conforme aux formes normales 1NF, 2NF et 3NF

Auteur : Florian Mathieu

Licence CC BY NC

Ce cours est mis à disposition selon les termes de la Licence Creative Commons Attribution - Pas d'Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International.