Corrigé bac NSI 2025 Métropole jour 2 — Exercice 2 : Ludothèque municipale : requêtes SQL, clés étrangères et podium des jeux empruntés

1. Une clé primaire doit identifier de façon unique chaque n-uplet de la relation : deux lignes ne peuvent jamais porter la même valeur de clé, et cette valeur ne peut pas être NULL (contrainte d'intégrité d'entité). Or rien n'empêche deux adhérents d'être homonymes : deux personnes nommées « VOYANT » (voire deux « Claire VOYANT ») peuvent être inscrites à la ludothèque, et l'attribut nom ne permettrait pas de les distinguer, par exemple pour savoir laquelle a emprunté « Catan ». Un nom peut de plus changer au cours du temps. On utilise donc un identifiant artificiel, idAdherent, entier unique attribué à l'inscription et qui ne change jamais.

2. La requête projette deux colonnes de la table jeu : elle renvoie, pour chaque jeu de la ludothèque (il n'y a pas de clause WHERE), son nom et son éditeur, une ligne par jeu, les lignes étant triées par ordre alphabétique croissant du nom du jeu (ORDER BY nomJeu, l'ordre croissant étant celui par défaut). Comme nomJeu est la clé primaire, chaque jeu apparaît exactement une fois ; les jeux jamais empruntés y figurent aussi. Le sujet ne donne le contenu d'aucune table ; les résultats montrés ici et aux questions 4, 8 et 9 ont été obtenus sur une petite base sqlite3 construite pour la démonstration d'après le schéma de la Figure 1 (quatre jeux, trois adhérents, sept emprunts). Sur cette base, le résultat a la forme :

3. Un jeu est en cours d'emprunt si sa ligne dans emprunt n'a pas encore de date de retour :

SELECT nomJeu
FROM emprunt
WHERE dateRendu IS NULL;

Il est inutile de joindre la table jeu : le nom du jeu est déjà dans emprunt (clé étrangère nomJeu).

Ce que le correcteur attend : le test d'une valeur indéfinie s'écrit IS NULL, jamais = NULL : en SQL, la comparaison dateRendu = NULL n'est ni vraie ni fausse et ne sélectionne aucune ligne.

4. Le nom et le prénom sont dans adherent, le jeu emprunté dans emprunt : il faut une jointure sur la clé étrangère idAdherent :

SELECT DISTINCT adherent.nom, adherent.prenom
FROM adherent
JOIN emprunt ON emprunt.idAdherent = adherent.idAdherent
WHERE emprunt.nomJeu = 'Catan';

La jointure associe chaque emprunt à l'adhérent qui l'a réalisé (la condition ON égalise la clé étrangère et la clé primaire), puis WHERE ne garde que les emprunts de « Catan ». Le mot-clé DISTINCT évite d'afficher plusieurs fois un adhérent qui aurait emprunté « Catan » à plusieurs reprises ; sans lui, la requête est acceptée mais Jean DUPONT, qui l'a emprunté deux fois dans la base de test, apparaît deux fois. Une jointure supplémentaire avec jeu n'est pas nécessaire, le nom du jeu étant déjà dans emprunt.

5. On modifie la ligne de l'emprunt concerné, repérée par sa clé primaire, en renseignant la date de retour au format 'AAAA-MM-JJ' :

UPDATE emprunt
SET dateRendu = '2025-06-03'
WHERE idEmprunt = 1538;

Le sujet nomme cet attribut id_emprunt dans la question ; dans le schéma de la Figure 1 il s'appelle idEmprunt, c'est ce nom qui figure dans la base. Comme la clé primaire identifie une ligne unique, il est inutile de préciser le jeu ou l'adhérente.

Ce que le correcteur attend : la clause WHERE est indispensable : sans elle, UPDATE affecterait la date du 3 juin 2025 à tous les emprunts de la table, y compris ceux en cours.

6. Deux conditions sur la table jeu, combinées par AND : « sortis à partir de 2010 » se traduit par anneeSortie >= 2010 (2010 inclus) et « âge minimum strictement inférieur à 10 ans » par ageMinimum < 10 :

SELECT nomJeu, categorie
FROM jeu
WHERE anneeSortie >= 2010 AND ageMinimum < 10;

7. La table participation relie un événement à un adhérent : chaque ligne signifie « cet adhérent a participé à cet événement ». Outre sa clé primaire idParticipation, elle contient donc deux clés étrangères :

• # nomEvenement, qui fait référence à l'attribut nom, clé primaire de la relation evenement ;
• # idAdherent, qui fait référence à l'attribut idAdherent, clé primaire de la relation adherent.

Schéma : participation(<u>idParticipation</u>, #nomEvenement, #idAdherent). La contrainte d'intégrité référentielle impose que chaque valeur de nomEvenement existe dans evenement.nom et chaque idAdherent dans adherent : on ne peut pas enregistrer la participation d'un adhérent inconnu ou à un événement inexistant. Comme un même adhérent peut participer à plusieurs événements et un événement réunir plusieurs adhérents, cette table de liaison est la façon relationnelle de représenter une association « plusieurs à plusieurs ».

8. On reprend le programme du sujet : la requête renvoie une ligne par emprunt, sous forme de tuple (nomJeu,) ; il suffit de compter les occurrences de chaque nom dans un dictionnaire, comme à la question 8 de l'exercice précédent :

import sqlite3

# Connexion à la base de données
connection = sqlite3.connect("ludotheque.db")
curseur = connection.cursor()

# Exécution de la requête : une ligne par emprunt
curseur.execute("SELECT nomJeu FROM emprunt")
jeux = curseur.fetchall()

# Comptage des emprunts de chaque jeu
dict_emprunts = {}
for jeu in jeux:
    nom = jeu[0]
    if nom in dict_emprunts:
        dict_emprunts[nom] = dict_emprunts[nom] + 1
    else:
        dict_emprunts[nom] = 1

# Fermeture de la connexion
curseur.close()
connection.close()

Exécuté sur une base de test contenant sept emprunts (trois de « Catan », trois de « Dixit », un d'« Azul »), le script construit \{'Catan': 3, 'Dixit': 3, 'Azul': 1\}. Un jeu jamais emprunté n'apparaît pas dans le dictionnaire, conformément à l'énoncé (« à chaque jeu emprunté »). On peut aussi partir de la liste liste déjà construite par le programme du sujet et écrire for nom in liste: avec le même corps de boucle.

9. Le podium est défini par les valeurs du dictionnaire : la première place revient à tous les jeux ayant le plus grand nombre d'emprunts, la deuxième à ceux ayant le deuxième plus grand nombre distinct, etc. On détermine donc d'abord les nombres d'emprunts distincts, triés par ordre décroissant, puis on range chaque jeu sur la marche qui correspond à sa valeur. Dans l'exemple du sujet, la liste attendue commence par la troisième place et finit par la première : podium[0] est la troisième marche, podium[2] la première.

# 1. Les nombres d'emprunts distincts, du plus grand au plus petit
valeurs = []
for nb in dict_emprunts.values():
    if nb not in valeurs:
        valeurs.append(nb)
valeurs.sort(reverse=True)        # exemple : [25, 22, 18, 12]

# 2. Les trois marches : podium[2] = 1re place, podium[1] = 2e,
#    podium[0] = 3e (ordre de la liste donnée dans le sujet)
podium = [[], [], []]
for jeu in dict_emprunts:
    nb = dict_emprunts[jeu]
    for place in range(3):        # 0 : 1re, 1 : 2e, 2 : 3e place
        if place < len(valeurs) and nb == valeurs[place]:
            podium[2 - place].append(jeu)

print(podium)

Sur le dictionnaire du sujet, valeurs vaut [25, 22, 18, 12] ; les jeux à $18$ emprunts vont dans podium[0], ceux à $22$ dans podium[1], celui à $25$ dans podium[2], et le script affiche [['Agricola', 'Puerto Rico', 'Caylus'], ['Codenames', 'Dominion'], ['Terraforming Mars']] : c'est le podium attendu (l'ordre des jeux à l'intérieur d'une marche suit l'ordre du dictionnaire et n'a pas d'importance, ce sont des ex æquo). « Dixit », quatrième valeur, est bien écarté. Le test place < len(valeurs) évite une erreur d'indice si la ludothèque compte moins de trois nombres d'emprunts distincts : avec \{'Catan': 3, 'Dixit': 3, 'Azul': 1\} le script renvoie [[], ['Azul'], ['Catan', 'Dixit']], et [[], [], []] sur un dictionnaire vide.

Ce que le correcteur attend : un tri des valeurs distinctes ; trier les jeux par nombre d'emprunts et prendre les trois premiers oublierait les ex æquo (ici six jeux occupent les trois places). Une version qui range podium[0] en première place est acceptée si elle le dit explicitement, mais la liste donnée dans le sujet va de la troisième place à la première.