BD-BBases de données et SQL

Schéma relationnel — représentation textuelle, PK et FK

Idée directrice

Passage classique des bases de données SI : à partir d’une représentation textuelle (tables, # pour FK), repérer clés primaires, clés étrangères et relations. Ancré sur le schéma bibliothèque de i-s1-04.

Ce qui aide à reconnaître la démarche

« représentation textuelle d’une base de données »

« LIVRES (NumLV, Titre, … NumED#) »

« clés primaires ainsi que les clés étrangères »

Besoin d’un point de départ ? Voir la méthode

Lire d’abord les # : ce sont les FK.
La PK est en général le premier attribut non # (ou le couple des # pour une table d’association).
Écrire Table (… ) puis lister PK / FK séparément pour la copie.

Les pièges à vérifier dans ta démarche

Prendre NumED pour PK de LIVRES.
Oublier que PRET porte deux FK (livre et lecteur).
Confondre association N-N et héritage.

Sujet principal

Exercice — dans l'esprit des sujets du bac

7 questionsCorrigé masqué

Soit la représentation textuelle d’une base de données d’une bibliothèque :

LIVRES (NumLV, Titre, NomAuteur, ValeurLV, NumED#)
EDITEURS (NumED, NomED, AdrED, TelED)
LECTEURS (NumLE, NomLE, AdrLE, VilleLE)
PRET (NumLV#, NumLE#, DateP, DateRet)

La notation # marque une clé étrangère.

Donner, pour chaque table, la clé primaire et, le cas échéant, les clés étrangères (en précisant la table référencée).
Exprimer en français le lien sémantique entre LIVRES et EDITEURS, puis entre PRET, LIVRES et LECTEURS.
Un même livre peut-il être prêté plusieurs fois à des lecteurs différents ? Justifier à partir de la clé de PRET.

Voir la correction commentéeAprès avoir posé votre démarche

1.
- LIVRES : PK = NumLV ; FK = NumED → EDITEURS(NumED).
- EDITEURS : PK = NumED ; pas de FK.
- LECTEURS : PK = NumLE ; pas de FK.
- PRET : PK composite = (NumLV, NumLE, DateP) : un même livre peut être prêté plusieurs fois au même lecteur, seule la date distingue les prêts ; FK NumLV → LIVRES, FK NumLE → LECTEURS (les deux colonnes marquée #).

2. Chaque livre a un éditeur (NumED dans LIVRES). Un prêt associe un livre et un lecteur à des dates données : relation N-N entre livres et lecteurs matérialisée par PRET.

3. Oui : plusieurs lignes de PRET peuvent partager le même NumLV avec des NumLE (ou des dates) différents, tant que la clé primaire de PRET reste unique. $\boxed{\text{PK/FK lues sur la représentation textuelle}}$

Changer d’énoncé, garder le raisonnement

Choisis un seul transfert après le sujet principal. Qu’est-ce qui change ? Quel résultat ou quelle méthode peux-tu réutiliser ? Les autres exercices restent disponibles pour une prochaine séance.

Exercices d'entraînement — une nuance à la fois

1 Drill BD-B.1 — représentation textuelle d’un site de téléchargement

Entraînement 01 / 06

Drill BD-B.1 — représentation textuelle d’un site de téléchargement

2 questionsCorrigé masqué

Une base gère des fichiers téléchargeables. Chaque fichier a un code unique CodeF, un type TypeF (référence à une table Types), une taille, une date de création. Un téléchargement relie un internaute (Login) et un fichier.

Proposer une représentation textuelle minimale à trois tables Types, Fichiers, Telecharger en marquant les FK par #.
Indiquer les clés primaires et les intégrités référentielles à maintenir.

Voir la correction commentéeAprès avoir posé votre démarche

1. Représentation textuelle :
- Types (Type, Designat)
- Fichiers (CodeF, NomF, Extension, TypeF#, Taille, Datcreat)
- Telecharger (NumTel, Login#, CodeF#, …)

2. PK : Types.Type, Fichiers.CodeF, Telecharger.NumTel. FK : Fichiers.TypeF → Types.Type ; Telecharger.CodeF → Fichiers.CodeF (et Login → table internautes si présente). Les intégrités référentielles interdisent un type/fichier inexistant. $\boxed{\text{représentation textuelle + PK/FK}}$

2 Drill BD-B.2 — tables déduites d’une liste de colonnes

Entraînement 02 / 06

Drill BD-B.2 — tables déduites d’une liste de colonnes

2 questionsCorrigé masqué

On dispose d’une liste de colonnes regroupées par entité pour une base de données de médiathèque scolaire : élève (num_ele, nom_pre, classe), livre (code_liv, titre, auteur), emprunt (num_emp, num_ele, code_liv, date_emp).

Déduire la liste des tables du schéma relationnel.
Donner une représentation textuelle de la base ( clé primaire notée, clé étrangère en # ) et indiquer les liens d’intégrité référentielle.

Voir la correction commentéeAprès avoir posé votre démarche

1. Trois tables : Eleve, Livre, Emprunt — une par entité de la liste de colonnes.

2. Représentation textuelle :
- Eleve (num_ele, nom_pre, classe, …) avec clé primaire num_ele ;
- Livre (code_liv, titre, auteur, …) PK code_liv ;
- Emprunt (num_emp, num_ele#, code_liv#, date_emp, …) avec clés étrangères num_ele → Eleve et code_liv → Livre.
Les contraintes d’intégrité référentielle interdisent un emprunt vers un élève ou un livre inexistant. $\boxed{\text{3 tables ; 2 FK dans Emprunt}}$

3 Drill BD-B.3 — compléter la représentation graphique à partir de la représentation textuelle : clés, liens 1 —— ∞, date ou date et heure dans la clé

Entraînement 03 / 06

Drill BD-B.3 — compléter la représentation graphique à partir de la représentation textuelle : clés, liens 1 —— ∞, date ou date et heure dans la clé

7 questionsFigure fournieCorrigé masqué

Un groupement de développement agricole de Jendouba installe des stations météorologiques automatiques dans plusieurs délégations. Chaque station enregistre des relevés (température, pluie) et reçoit des opérations de maintenance assurées par des techniciens. La base de données BD_Meteo a la représentation textuelle suivante (clés primaires soulignées, clés étrangères suivies de #) :

Delegation ( codeDel , nomDel )
StationMeteo ( idStation , nomStation , altitude , codeDel# )
Releve ( dateHeureRel , idStation# , temperature , pluie )
Technicien ( matTech , nomTech , telTech )
Maintenance ( dateMaint , idStation# , operation , matTech# )

Champ	Description
codeDel / nomDel	Code et nom d'une délégation.
idStation / nomStation	Identifiant et nom d'une station.
altitude	Altitude d'une station, en mètres.
dateHeureRel	Date et heure d'un relevé.
temperature / pluie	Température (°C) et hauteur de pluie (mm) relevées.
matTech / nomTech / telTech	Matricule, nom et téléphone d'un technicien.
dateMaint	Date (sans heure) d'une maintenance.
operation	Description de l'opération de maintenance.

Les cadres des tables sont donnés ci-dessous :

Compléter la représentation graphique de la base de données BD_Meteo en indiquant les clés primaires, les clés étrangères et les cardinalités (types de relations).
Répondre par V (vrai) ou F (faux) et justifier la réponse :
1. Une station peut enregistrer plusieurs relevés au cours d'une même journée.
2. Une station peut recevoir deux maintenances le même jour, assurées par deux techniciens différents.
3. Un technicien peut assurer la maintenance de plusieurs stations.

Voir la correction commentéeAprès avoir posé votre démarche

1. Idée : chaque clé étrangère crée un lien ; le côté 1 est la table où le champ est clé primaire (table mère), le côté ∞ celle où il est clé étrangère (table fille).

Delegation 1 —— ∞ StationMeteo, par codeDel, clé étrangère de StationMeteo ;
StationMeteo 1 —— ∞ Releve, par idStation, clé étrangère de Releve ;
StationMeteo 1 —— ∞ Maintenance, par idStation, clé étrangère de Maintenance ;
Technicien 1 —— ∞ Maintenance, par matTech, clé étrangère de Maintenance.

Releve et Maintenance ont une clé primaire composée dont idStation fait partie : ce champ est à la fois souligné et suivi de #.

2.a V : dateHeureRel contient la date et l'heure ; deux relevés du même jour, à des heures différentes, forment deux couples (dateHeureRel, idStation) distincts.

2.b F : la clé (dateMaint, idStation) ne contient qu'une date ; une seconde maintenance de la même station le même jour répéterait la clé, quel que soit le technicien, car matTech ne fait pas partie de la clé.

2.c V : matTech# est dans Maintenance, côté ∞ du lien Technicien 1 —— ∞ Maintenance, et ne fait pas partie de sa clé : un même technicien peut figurer dans plusieurs maintenances, de stations différentes.

4 Drill BD-B.4 — six affirmations Vrai/Faux sur la représentation graphique : place de la clé étrangère, clé primaire composée, champ propre au couple

Entraînement 04 / 06

Drill BD-B.4 — six affirmations Vrai/Faux sur la représentation graphique : place de la clé étrangère, clé primaire composée, champ propre au couple

6 questionsFigure fournieCorrigé masqué

Un centre de formation professionnelle de Gabès organise des sessions de formation (soudure, électricité du bâtiment, froid et climatisation…). Chaque session porte sur une formation, est animée par un formateur et se déroule dans une salle ; les stagiaires s'y inscrivent et reçoivent une note finale. La base de données du centre a la représentation graphique suivante :

En se référant à la représentation graphique de la base de données, répondre par « Vrai » ou « Faux » à chacune des affirmations suivantes, puis justifier la réponse par la place d'une clé ou d'un champ :

Une session de formation peut se dérouler dans plusieurs salles.
Un stagiaire peut s'inscrire à plusieurs sessions.
Un stagiaire peut s'inscrire deux fois à la même session.
Un formateur anime toujours des sessions de la même formation.
Tous les stagiaires d'une même session obtiennent la même note finale.
Une même formation peut être programmée dans plusieurs sessions.

Voir la correction commentéeAprès avoir posé votre démarche

Idée : une clé étrangère simple ne porte qu'une valeur par ligne ; une clé primaire composée interdit la répétition du couple, pas celle de chacun de ses champs ; un champ placé dans une table d'association dépend du couple.

N°	Réponse	Justification
1	Faux	codeSalle# est une clé étrangère simple de Session : une session référence une seule salle.
2	Vrai	numStag peut se répéter dans Inscription avec des codeSess différents : seul le couple est unique.
3	Faux	le couple (numStag, codeSess) est la clé primaire d'Inscription : il ne peut pas figurer deux fois.
4	Faux	codeForm# et matForm# sont deux clés étrangères indépendantes de Session : un formateur peut animer des sessions de formations différentes.
5	Faux	noteFinale est un champ d'Inscription : elle est propre à chaque couple (stagiaire, session).
6	Vrai	Formation 1 —— ∞ Session : plusieurs sessions peuvent porter le même codeForm.

5 Drill BD-B.5 — des requêtes CREATE TABLE à la représentation textuelle ; une clé auto-incrémentée qui laisse passer un doublon

Entraînement 05 / 06

Drill BD-B.5 — des requêtes CREATE TABLE à la représentation textuelle ; une clé auto-incrémentée qui laisse passer un doublon

2 questionsCorrigé masqué

Pour son tournoi de handball inter-lycées, le commissariat régional du sport scolaire de Monastir implémente une base de données par les requêtes SQL suivantes :

CREATE TABLE Equipe (
  codeEq CHAR(4) PRIMARY KEY,
  nomEq VARCHAR(30) NOT NULL UNIQUE,
  lycee VARCHAR(40) NOT NULL
);
CREATE TABLE Joueur (
  numLic INT PRIMARY KEY,
  nomJ VARCHAR(40) NOT NULL,
  dateNaiss DATE,
  codeEq CHAR(4) NOT NULL
    REFERENCES Equipe (codeEq)
);
CREATE TABLE Rencontre (
  idRenc INT PRIMARY KEY AUTO_INCREMENT,
  dateRenc DATE NOT NULL,
  eqLocale CHAR(4) NOT NULL
    REFERENCES Equipe (codeEq),
  eqVisiteuse CHAR(4) NOT NULL
    REFERENCES Equipe (codeEq)
);
CREATE TABLE Participation (
  idPart INT PRIMARY KEY AUTO_INCREMENT,
  idRenc INT NOT NULL,
  numLic INT NOT NULL,
  nbButs INT DEFAULT 0
    CHECK (nbButs >= 0),
  FOREIGN KEY (idRenc)
    REFERENCES Rencontre (idRenc),
  FOREIGN KEY (numLic)
    REFERENCES Joueur (numLic)
);

Important : à chaque représentation textuelle demandée, indiquer la clé primaire et la (les) clé(s) étrangère(s) de chaque table.

En se basant sur les requêtes d'implémentation ci-dessus, donner la représentation textuelle des tables Equipe, Joueur, Rencontre et Participation.
On considère les règles de gestion suivantes : R1 : un joueur participe à plusieurs rencontres ; R2 : une rencontre fait participer plusieurs joueurs ; R3 : la participation d'un joueur à une rencontre n'est enregistrée qu'une seule fois. Expliquer pourquoi l'implémentation de la table Participation ne respecte pas R3, puis donner une nouvelle représentation textuelle de cette table respectant R1, R2 et R3.

Voir la correction commentéeAprès avoir posé votre démarche

1. Idée : PRIMARY KEY, dans la ligne du champ ou en fin de table, désigne la clé primaire ; chaque REFERENCES ou FOREIGN KEY … REFERENCES désigne une clé étrangère, marquée #.

Equipe ( codeEq , nomEq , lycee )
Joueur ( numLic , nomJ , dateNaiss , codeEq# )
Rencontre ( idRenc , dateRenc , eqLocale# , eqVisiteuse# )
Participation ( idPart , idRenc# , numLic# , nbButs )

Rencontre porte deux clés étrangères vers la même table Equipe (eqLocale et eqVisiteuse) : deux liens distincts, chacun marqué #. AUTO_INCREMENT, NOT NULL, UNIQUE, DEFAULT et CHECK ne changent pas la représentation textuelle.

2. Idée : un identifiant auto-incrémenté prend une valeur nouvelle à chaque insertion, il n'empêche donc jamais un doublon métier ; quand la règle dit « une seule fois » par couple, c'est le couple qui doit être la clé.

Avec la clé idPart, les insertions (idRenc = 1, numLic = 1203, 4 buts) puis (idRenc = 1, numLic = 1203, 6 buts) sont toutes deux acceptées, avec idPart = 1 et idPart = 2 : le joueur 1203 serait enregistré deux fois pour la rencontre 1. Nouvelle représentation :

Participation ( idRenc# , numLic# , nbButs )

R1 et R2 restent vérifiées : le même numLic peut figurer avec des rencontres différentes et la même rencontre avec des joueurs différents ; R3 est garantie par l'unicité du couple (idRenc, numLic).

6 Drill BD-B.6 — liens entre les tables (table mère, table fille, clé primaire, clé étrangère) et clé étrangère composée

Entraînement 06 / 06

Drill BD-B.6 — liens entre les tables (table mère, table fille, clé primaire, clé étrangère) et clé étrangère composée

2 questionsCorrigé masqué

L'office régional des œuvres universitaires gère l'hébergement des étudiants dans ses résidences. Sa base de données est décrite par les requêtes suivantes :

CREATE TABLE Residence (
  codeRes CHAR(3) PRIMARY KEY,
  nomRes VARCHAR(40) NOT NULL,
  ville VARCHAR(20) NOT NULL
);
CREATE TABLE Chambre (
  codeRes CHAR(3),
  numCh INT,
  nbLits INT
    CHECK (nbLits BETWEEN 1 AND 3),
  PRIMARY KEY (codeRes, numCh),
  FOREIGN KEY (codeRes)
    REFERENCES Residence (codeRes)
);
CREATE TABLE Etudiant (
  cinEt CHAR(8) PRIMARY KEY,
  nomEt VARCHAR(40) NOT NULL,
  etablissement VARCHAR(50)
);
CREATE TABLE Hebergement (
  cinEt CHAR(8),
  anneeUniv CHAR(9),
  codeRes CHAR(3) NOT NULL,
  numCh INT NOT NULL,
  PRIMARY KEY (cinEt, anneeUniv),
  FOREIGN KEY (cinEt)
    REFERENCES Etudiant (cinEt),
  FOREIGN KEY (codeRes, numCh)
    REFERENCES Chambre (codeRes, numCh)
);

À partir des requêtes ci-dessus, déterminer la liste des liens entre les tables en précisant, pour chaque lien, la table mère, la table fille, la clé primaire et la clé étrangère.
Donner la représentation textuelle correspondant à la description des tables ci-dessus.

Voir la correction commentéeAprès avoir posé votre démarche

1. Idée : chaque clause FOREIGN KEY … REFERENCES est un lien ; la table qui la contient est la table fille, la table citée après REFERENCES est la table mère ; la clé étrangère compte autant de champs que la clé primaire qu'elle référence.

Table mère	Table fille	Clé primaire	Clé étrangère
Residence	Chambre	codeRes	codeRes
Etudiant	Hebergement	cinEt	cinEt
Chambre	Hebergement	(codeRes, numCh)	(codeRes, numCh)

Hebergement n'est pas reliée directement à Residence : son champ codeRes n'est qu'une partie de la clé étrangère composée (codeRes, numCh), qui désigne une chambre précise. Ainsi l'hébergement dans la chambre (R01, 13) est refusé si cette chambre n'existe pas, même lorsque la résidence R01 existe.

2. Idée : souligner les champs de PRIMARY KEY et marquer # chaque champ d'une clé étrangère ; un champ qui appartient aux deux est souligné et suivi de #.

Residence ( codeRes , nomRes , ville )
Chambre ( codeRes# , numCh , nbLits )
Etudiant ( cinEt , nomEt , etablissement )
Hebergement ( cinEt# , anneeUniv , codeRes# , numCh# )

Relier ce raisonnement au bac

Choisis une filière pour voir les sujets qui étayent ce scénario.

Tu écris. Tu continues.

Où veux-tu aller ?

Schéma relationnel — représentation textuelle, PK et FK

Exercice — dans l'esprit des sujets du bac

Changer d’énoncé, garder le raisonnement

Exercices d'entraînement — une nuance à la fois

Drill BD-B.1 — représentation textuelle d’un site de téléchargement

Drill BD-B.2 — tables déduites d’une liste de colonnes

Drill BD-B.3 — compléter la représentation graphique à partir de la représentation textuelle : clés, liens 1 —— ∞, date ou date et heure dans la clé

Drill BD-B.4 — six affirmations Vrai/Faux sur la représentation graphique : place de la clé étrangère, clé primaire composée, champ propre au couple

Drill BD-B.5 — des requêtes CREATE TABLE à la représentation textuelle ; une clé auto-incrémentée qui laisse passer un doublon

Drill BD-B.6 — liens entre les tables (table mère, table fille, clé primaire, clé étrangère) et clé étrangère composée

Relier ce raisonnement au bac