Bases de données

On souhaite modéliser une médiathèque avec deux tables :

Adherents(id_adherent, nom, email)

Emprunts(id_emprunt, id_adherent, titre_livre, date_emprunt)

Un emprunt est toujours associé à un unique adhérent, mais un adhérent peut faire plusieurs emprunts.

1. Quelle est la clé primaire de la table Adherents ? Et celle de la table Emprunts ?
2. Quel attribut de la table Emprunts est une clé étrangère ? Vers quelle table et quel attribut fait-elle référence ?
3. Pourquoi est-il préférable de séparer les emprunts dans une table distincte plutôt que d'ajouter des colonnes emprunt1, emprunt2, ... directement dans la table Adherents ?

Un club de tennis enregistre les inscriptions de ses licenciés aux tournois dans une seule table :

Inscriptions(id_inscription, nom_joueur, ville_joueur, nom_tournoi, date_tournoi)

Chaque ligne correspond à l'inscription d'un joueur à un tournoi. Un même joueur, inscrit à plusieurs tournois, apparaît donc sur plusieurs lignes (avec sa ville recopiée à chaque fois) ; de même, un tournoi accueillant de nombreux joueurs voit son nom et sa date recopiés sur autant de lignes que d'inscrits.

1. Quelle anomalie ce schéma présente-t-il ? Donner un exemple concret de problème que cela peut causer lors d'une mise à jour.
2. Proposer un nouveau schéma relationnel avec trois tables Joueurs, Tournois et Inscriptions, qui corrige cette anomalie. Préciser, pour chaque table, sa clé primaire, et pour la table Inscriptions, ses éventuelles clés étrangères.
3. Avec ce nouveau schéma, un tournoi accueillant 100 joueurs n'occupe-t-il plus qu'une seule ligne dans la table qui le décrit ? Justifier.

Une petite entreprise gère actuellement la liste de ses clients et de leurs commandes dans un unique fichier tableur, stocké sur un dossier partagé. Plusieurs employés du service commercial ouvrent et modifient ce fichier en même temps depuis leurs postes. L'entreprise envisage de migrer vers une base de données relationnelle pilotée par un SGBD.

Le cours identifie quatre services rendus par un SGBD : la persistance, la gestion des accès concurrents, l'efficacité du traitement des requêtes, et la sécurisation des accès.

1. Pour chacun de ces quatre services, décrire un problème concret que peut rencontrer l'entreprise avec son fichier tableur actuel, et expliquer en quoi un SGBD résoudrait ce problème.
2. Au-delà de ces quatre services, en quoi le fait de structurer les données en tables reliées par des clés primaires et étrangères (plutôt que de tout regrouper dans un seul tableau, comme dans le fichier actuel) contribue-t-il, lui aussi, à la fiabilité de la base de données de l'entreprise ?

On reprend les deux tables du cours :

Auteurs(id_auteur, nom, nationalite)

Livres(id_livre, titre, annee, id_auteur)

1. Écrire une requête SQL qui affiche le titre et l'année de tous les livres parus après 1940, triés par année croissante.
2. Écrire une requête SQL qui affiche le titre de chaque livre accompagné du nom de son auteur (utiliser une jointure).
3. Écrire une requête SQL qui met à jour la nationalité de l'auteur d'id_auteur égal à 3 en 'Sénégalaise'.

Exercice tiré du sujet zéro officiel du bac NSI (2021), sur les bases de données relationnelles et le SQL.

Table seconde : num_eleve (INT, clé primaire), langue1, langue2, option, classe. Extrait de données :

1. Intérêt de num_eleve comme clé primaire ? Écrire l'insertion SQL de MARTIN Léa dans seconde.

2. Que renvoie SELECT num_eleve FROM seconde; ? Et SELECT COUNT(num_eleve) FROM seconde; ? Écrire la requête comptant les élèves ayant l'allemand en langue1 ou langue2.

3. On crée eleve (num_eleve clé primaire et clé étrangère vers seconde, nom, prenom, datenaissance). Qu'apporte cette clé étrangère ? Écrire la jointure listant nom/prénom/date de naissance des élèves de 2A.

4. Proposer la structure d'une table coordonnees (adresse, code postal, ville, e-mail par élève), avec sa clé primaire et sa clé étrangère.

Exercice tiré du sujet NSI 2021 dit « 2 annulé », sur les bases de données relationnelles.

Base restaurant : Plat(idPlat, nom, categorie, description, prix), Client(idClient, email, passwd, nom, avis), Reservation(idReservation, #idClient, jour, heure, numTable), Commande(#idPlat, #idReservation).

1. Parmi SELECT nom, prix FROM Plat WHERE categorie='entrée', SELECT * FROM Plat WHERE categorie='entrée' et UPDATE Plat SET categorie='entrée' WHERE 1, laquelle renvoie tous les attributs des plats de catégorie 'entrée' ?

2. Écrire les requêtes : a) noms et avis des clients ayant réservé le '2021-06-05' à '19:30:00' ; b) noms des plats 'plat principal' ou 'dessert' commandés le '2021-04-12'.

3. Que fait INSERT INTO Plat VALUES(58,'Pêche Melba','dessert','Pêches et glace vanille',6.5); ?

4. Écrire : a) suppression des commandes d'idReservation 2047 ; b) augmentation de 5% des prix strictement inférieurs à 20,00.

Exercice tiré du bac NSI 2022 (Asie/Pacifique, Jour 2), sur une base de données SQL pour un club de tennis.

joueurs(id_joueur, nom_joueur, prenom_joueur, login, mdp) : (1,Dupont,Alice,alice,1234), (2,Durand,Belina,belina,5694), (3,Caron,Camilia,camilia,9478), (4,Dupont,Dorine,dorine,1347).

matchs(id_match, date, id_creneau, id_terrain, id_joueur1, id_joueur2) : (1,2020-08-01,2,1,1,4), (2,2020-08-01,3,1,2,3), (3,2020-08-02,6,2,1,3), (4,2020-08-02,7,2,2,4), (5,2020-08-08,3,3,1,2), (6,2020-08-08,5,2,3,4).

terrains(id_terrain, nom_terrain, surface) : (1,stade,terre battue), (2,gymnase,synthétique), (3,hangar,terre battue). creneaux(id_creneau, plage_horaire) : 12 créneaux d'une heure, de 8h à 20h (id_creneau=3 → 10h-11h).

1. Clé primaire de matchs ? Ses clés étrangères ?

2. Jour et créneau du match entre Durand Belina et Caron Camilia ? Quels sont les deux seuls joueurs à avoir joué dans le hangar (terrain 3) ?

3. Requête donnant les prénoms des joueurs de nom 'Dupont'. Requête mettant à jour le mot de passe de Dorine Dupont à '1976'.

4. Requête ajoutant Zora MAGID (login 'zora', mot de passe '2021').

5. Requête donnant les jours où Alice joue.

Exercice tiré du bac NSI La Réunion 2022 (Jour 1), sur les bases de données relationnelles et SQL (QCM en ligne).

Base QCM_NSI : eleves(ideleve, nom, prenom), qcm(idqcm, titre, date), questions(idquestion, #idqcm, question, bonnereponse), lien_eleve_qcm(#ideleve, #idqcm, note) de clé primaire (ideleve, idqcm).

eleves : (2,Dubois,Thomas), (3,Dupont,Cassandra), (4,Marty,Mael), (5,Bikila,Abebe). qcm : (1,"Base de données",2021-09-20), (2,"POO",2022-04-08), (3,"Arbre Binaire",2022-01-09), (4,"Arbre Parcours",2022-02-15), (5,"Piles-Files",2021-12-05). lien_eleve_qcm : (2,1,12), (2,3,18), (2,4,13), (2,5,15), (3,1,20), (3,2,9), (3,3,18), (3,5,13), (4,4,15), (4,5,20), (5,4,15).

1.a. Que renvoie SELECT titre FROM qcm WHERE date > '2022-01-10'; ? 1.b. Requête donnant les notes de l'élève d'identifiant 4.

2.a. La clé primaire de lien_eleve_qcm est (ideleve, idqcm). Pourquoi un élève ne peut-il pas faire deux fois le même QCM ? 2.b. Marty Mael fait le QCM POO, note 18 : comment la base est-elle modifiée (sans SQL) ? 2.c. Nouvel élève (Lefèvre, Kevin) : requête d'enregistrement. 2.d. Dubois Thomas (ideleve=2) part : requête supprimant ses lignes de lien_eleve_qcm.

3.a. Compléter pour afficher noms/prénoms des élèves ayant fait le QCM d'idqcm=4 :

SELECT .............................. FROM eleves
JOIN lien_eleve_qcm ON eleves.ideleve = ..............................
WHERE .............................. ;

3.b. Résultat de cette requête.

4. Requête (3 tables) affichant nom, prénom, note des élèves ayant fait "Arbre Binaire".

Exercice tiré du bac NSI Métropole 2022 (Jour 1), sur les bases de données relationnelles (cinéma).

Schéma : individu(id_ind, nom, prenom, naissance), realisation(id_rea, titre, annee, type), emploi(id_emp, description, #id_ind, #id_rea).

individu : (105,Hulka,Daniel,01-06-1968), (403,Travis,Daniel,10-03-1968), (688,Crog,Daniel,07-07-1968), (695,Pollock,Daniel,24-08-1968). realisation : (105,"Casino Imperial",2006,action), (325,"Ciel tombant",2012,action), (655,"Fantôme",2015,action), (950,"Mourir pour attendre",2021,action).

1.a. Que renvoie SELECT nom, prenom, naissance FROM individu WHERE nom = 'Crog'; ? 1.b. Requête donnant titre et id_rea de chaque film sorti strictement après 2020.

2.a. Pour corriger la naissance de Daniel Crog, UPDATE ou INSERT ? Justifier :

UPDATE individu SET naissance = '02-03-1968'
WHERE id_ind = 688 AND nom = 'Crog' AND prenom = 'Daniel';
-- ou --
INSERT INTO individu VALUES (688, 'Crog', 'Daniel', '02-03-1968');

2.b. individu peut-elle accepter deux lignes de même nom, prénom et naissance ?

3.a. Compléter pour ajouter les rôles de Daniel Crog (James Bond) dans "Casino Impérial"(105) puis "Ciel tombant"(325) :

INSERT INTO emploi VALUES (5400, 'Acteur(James Bond)', ...);
INSERT INTO emploi VALUES (5401, 'Acteur(James Bond)', ...);

3.b. Nouveau rôle dans "Docteur Yes" (film pas encore enregistré) : créer d'abord le film ou le rôle ? Pourquoi ?

4.a. Compléter pour obtenir nom, titre, année de chaque rôle 'Acteur(James Bond)' :

SELECT ...
FROM emploi
JOIN individu ON ...
JOIN realisation ON ...
WHERE emploi.description = 'Acteur(James Bond)';

4.b. Requête donnant les descriptions des emplois de Denis Johnson (seulement les descriptions).

Exercice tiré du bac NSI Métropole (session de remplacement) 2022, sur les bases de données relationnelles (catalogue Gaia).

Table Gaia(Num_Objet: Int, Num_Systeme: Int, Nom_Systeme: String, #Type_Objet: String, Nom_Objet: String, Ascension_Droite: Real, Declinaison: Real, Parallaxe: Real, Nom_SIMBAD: String). Extrait de 14 objets (Proxima Cen, alf Cen A/B, Barnard's Star, Luhman 16 A/B, Wolf 359, HD 95735, Lalande 21185 b, alf CMa A/B, G 272-61 A/B, Ross 154, Ross 248), de types LM, Planet, *, BD, WD, avec parallaxes de 316 à 768 mas.

1. Pourquoi Num_Objet peut-il être clé primaire de Gaia ?

Table Type(Type_Objet, Libelle_Objet) : LM→"Etoile de faible masse", Planet→"Planète", *→"Etoile", BD→"Naine Brune", WD→"Naine Blanche".

2. Proposer le schéma relationnel de Type, clé primaire soulignée.

3. Laquelle de ces requêtes ne provoque pas d'erreur ? a. INSERT INTO Gaia VALUES ('8', 4, 'WISEA J085510', 'Naine Brune', 'WISEA J085510', 133.781,-7.244, 439.000, 'WISEA J085510'); b. INSERT INTO Gaia VALUES (8, 4, 'WISEA J085510', 'Naine Brune', 'WISEA J085510', 133.781,-7.244, 439.000, 'WISEA J085510'); c. INSERT INTO Gaia VALUES (8, 4, WISEA J085510, 'Naine Brune', WISEA J085510, 133.781,-7.244, 439.000, WISEA J085510); d. INSERT INTO Gaia VALUES (8, 4, 'WISEA J085510', 'Naine Brune', 'WISEA J085510', '133.781',-7.244, 439.000, 'WISEA J085510')

4. Pourquoi INSERT INTO Type VALUES ('BD', 'Trou Noir'); échoue ? 5. Résultat de SELECT Nom_Objet, Parallaxe FROM Gaia WHERE Type_Objet = 'Planet'; ? 6. Requête donnant nom du système, nom de l'objet, libellé du type pour parallaxe > 400 mas et type '*'.

7.a. Requête insérant un type 'ST' de libellé "Etoile". 7.b. Démarche complète pour remplacer '*' par 'ST'.

Exercice tiré du bac NSI Nouvelle-Calédonie 2022 (jour 2), sur les bases de données relationnelles et SQL : les chevaliers de la table ronde.

Table Personnage(Idperso, nom, frere_de, points, Idqualite), table Qualite(Idqualite, nom_qualite) (1 apprenti chevalier, 2 magicien, 3 preux chevalier, 4 espion, 5 seigneur, 6 sage, 7 grand chevalier). Extrait de Personnage : Merlin (NC, 40, qualité 2), Galehaut (NC, 40, 5), Lancelot (Hector, 50, 1), Perceval (Lamorak, 35, 3), Keud (Arthur, 80, 7), etc.

1. Écrire une requête affichant le nom et les points de tous les personnages. 2. Le frère d'Arthur ne s'appelle pas Keud mais Antor : écrire la requête de correction. 3. Écrire une requête ajoutant le nom_qualite « roi » à Qualite, d'Idqualite 8. 4. Écrire une requête créant un quatorzième personnage : le roi Arthur, 100 points. 5. Écrire une requête affichant le nom et le nom de qualité des personnages ayant 40 points. 6. On applique UPDATE Personnage SET points = points + 10 WHERE points < 40;. Indiquer les points d'Arthur, Perceval et Merlin après cette mise à jour.

Exercice tiré d'un bac NSI de la session 2021 (centre d'examen non confirmé), sur les bases de données relationnelles et SQL : la gestion d'une gare.

Schéma : Train(numT, provenance, destination, horaireArrivee, horaireDepart), Reservation(numR, nomClient, prenomClient, prix, #numT) (numT clé étrangère vers Train.numT).

1. Nom générique des logiciels assurant la persistance des données, l'efficacité des requêtes et la sécurisation des accès ?

2. a) DELETE FROM Train WHERE numT = 1241; puis DELETE FROM Reservation WHERE numT = 1241; : pourquoi la première instruction échoue-t-elle si des réservations existent pour ce train ? b) Citer un cas où l'insertion dans Reservation est impossible.

3. Écrire les requêtes : a) tous les numéros de train de destination « Lyon » ; b) réservation n°1307, 33 €, M. Alan Turing, train n°654 ; c) mise à jour de l'horaire d'arrivée du train n°7869 à 08h11.

4. Que détermine SELECT COUNT(*) FROM Reservation WHERE nomClient = "Hopper" AND prenomClient = "Grace"; ?

5. Écrire la requête renvoyant les destinations et les prix des réservations de Grace Hopper.

Exercice tiré du bac NSI Amérique du Nord 2024 (jour 2), sur SQL : la base de données d'un pharmacien.

client(id_client, nom_client, prenom_client, num_secu_sociale) : (1, Martin, Sophie, 202103812326129), (2, Dufour, Marc, 105073817009595).

1. Résultat de SELECT nom_client, prenom_client FROM client ORDER BY nom_client; ?

medicament(id_medic, nom_medic, categorie, conditionnement, quantite, prix) : (1, Paracétamol 1g CP, antalgique, 8, 50, 3.50), (2, Acide acétylsalicylique, antalgique, 8, 20, 2.30), (3, Gel hydroalcoolique 100ml, désinfectant, 1, 300, 2.30), (4, Acide ascorbique, vitamine, 10, 450, 5.50).

2. Requête affichant les noms des médicaments de prix strictement inférieur à 3 €.

Ordonnance de Sophie Martin : Paracétamol 1g CP (boîte de 8), max 3cp/jour pendant 2 jours ; Acide ascorbique (boîte de 10), 1cp/jour pendant 4 semaines. ordonnance(id_ordo, id_client, date_ordo, id_medic, nb_boites) : (6, 2, 2023-11-29, 2, 2), (7, 1, 2023-12-13, 1, ?), (8, 1, 2023-12-13, 4, ?).

3. Requête ajoutant la cliente Nathalie Durand (id_client=3, carte Vitale 2 69 05 49 588 157 80).

4. Attributs clés étrangères de ordonnance, et leur utilité.

5. Nombre de boîtes des lignes 7 et 8, en justifiant à partir du conditionnement et de la posologie.

6. Requête mettant à jour le stock d'Acide ascorbique.

7. Coût total des médicaments fournis à Mme Martin (calcul justifié, sans requête).

8. Requête affichant le nom du médicament de l'ordonnance n°6.

Exercice tiré du bac NSI Amérique du Nord 2024 (jour 1), sur les flashcards et la base de données des boîtes de Leitner.

Partie A. Une étudiante stocke ses flashcards dans flashcards.csv (colonnes discipline, chapitre, question, réponse), avec un séparateur qui n'est pas la virgule.

1. Donner ce séparateur. 2. Justifier ce choix (indice : certaines réponses contiennent elles-mêmes des virgules).

Code (voir le corrigé pour le détail) : charger(nom_fichier) lit le CSV via csv.DictReader et renvoie une liste de dictionnaires ; choix_discipline/choix_chapitre proposent un choix interactif ; entrainement affiche question puis, après une pause, réponse.

3. Compléter charger(nom_fichier). 4. Quelle méthode du module time est utilisée ? 5. Type de donnees[i] ? 6. Compléter les lignes finales (chargement puis appel des trois fonctions).

Partie B. Boîtes de Leitner : 5 boîtes, fréquences 1, 2, 4, 8, 15 jours. Base à 4 tables discipline, chapitre, boite(id, lib, frequence), flashcard(id, id_ch, id_boite, question, reponse, date_interro). La table boite contient déjà les boîtes 1 à 4.

7. Requête ajoutant la boîte 5 ('tous les quinze jours', fréquence 15).

Une requête sur flashcard affiche : 5, 2, 1, Pearl Harbor - date, 6 decembre 1941 (id, id_ch, id_boite, question, reponse) — la réponse comporte une erreur, la bonne date étant le 7 décembre 1941.

8. Requête corrigeant cette réponse. 9. Requête donnant les libellés des disciplines. 10. Requête donnant les libellés des chapitres de la discipline 'histoire'. 11. Requête donnant les identifiants des flashcards de la discipline 'histoire'. 12. Requête supprimant toutes les flashcards de la boîte d'identifiant 3.

Exercice 3 (8 points, partie base de données) du sujet de bac NSI Amérique du Nord 2025, jour 1.

Une association d'enfants (0-18 ans) veut former des groupes d'enfants qui s'entendent durant les activités, à partir d'une base de données à trois tables :

• parent : nom, tel (numéro de téléphone), codep (code postal) ;
• enfant : id, prenom, num_parent (téléphone du parent référent, un seul par enfant), annee (année de naissance) ;
• mesentente : enfant1, enfant2 — deux identifiants d'enfants qui ne peuvent pas sortir ensemble.

num_parent de enfant est une clé étrangère référençant tel de parent.

Extrait de la table enfant :

1. Donner le type de l'attribut annee.
2. Quelle contrainte de domaine supplémentaire serait pertinente pour annee ?
3. Donner un attribut de enfant qui suit une contrainte de référence.
4. Proposer, en justifiant, une clé primaire pour parent.

Suite à une erreur de saisie, le vrai téléphone d'un parent (33619782812) a été transformé en 33600782812. On tente de corriger avec UPDATE parent SET tel = 33619782812 WHERE tel = 33600782812; — cette requête lève une erreur.

5. Expliquer pourquoi.
6. Recopier et compléter cette suite de commandes, qui corrige le téléphone du parent nommé 'Bauges' (code postal 73340, téléphone erroné 33600782812, vrai téléphone 33619782812) :

INSERT INTO parent VALUES ('Bauges', 33619782812, 73340);
UPDATE enfant SET num_parent = ... WHERE num_parent = ...;
DELETE FROM parent WHERE tel = ...;

7. D'après la table enfant fournie, donner le résultat de SELECT prenom FROM enfant WHERE annee < 2014 ORDER BY annee;
8. Proposer une requête donnant, par ordre alphabétique, les prénoms des enfants du parent de téléphone 3619861122.
9. Proposer une requête donnant les identifiants et prénoms des enfants dont le parent habite au code postal 38520.

Exercice 3 (8 points, partie base de données) du sujet de bac NSI 25-NSIPE2, session 2025.

Les visiteurs volontaires reçoivent un bracelet magnétique permettant de les identifier et de les photographier à des points clés du parc ; les photos leur sont proposées à la vente. Les données personnelles sont stockées en France, avec droit de consultation, retrait et rectification.

Trois relations : visiteur(id : int, nom : text, prenom : text, date : text) — date au format 'AAAA-MM-JJ' ; photo(id : int, #id_visiteur : int, #id_attraction : int, heure : text, prix : float) — heure au format 'HH:MM' ; attraction(id : int, nom : text, duree : int).

Les opérateurs de comparaison classiques s'appliquent aussi aux chaînes de caractères (ex. '2025-01-01' > '2024-01-01' est vrai). SUM(prix) renvoie la somme des valeurs de prix.

1. Expliquer ce qu'est une clé primaire, puis ce qu'est une clé étrangère.
2. Écrire une requête donnant les noms et prénoms (sans doublons) des visiteurs présents le 11 janvier 2025.

Un visiteur, Alan TURING, est venu plusieurs fois en 2024 et a, à chaque fois, acheté toutes les photos proposées.

3. Écrire une requête donnant la somme totale payée par Alan TURING pour des photos au parc en 2024.

Suite à un problème technique, les gérants ont utilisé :

SELECT visiteur.nom, prenom
FROM visiteur JOIN photo ON visiteur.id = photo.id_visiteur
JOIN attraction ON attraction.id = photo.id_attraction
WHERE attraction.nom = 'Grande roue'
  AND heure = '12:34'
  AND date = '2024-07-26';

4. Expliquer ce qu'ils voulaient savoir.
5. Le parc veut proposer, pour un même cliché, plusieurs formats et supports (A5, A6, poster, porte-clé…). Proposer des modifications de la base de données pour prendre en charge cette nouvelle offre.

Exercice 3 (8 points, partie base de données) du sujet de bac NSI Centres étrangers (groupe 1) 2024, jour 1.

L'objectif est de faciliter la gestion du système d'information d'un camping municipal, dont les informations sont stockées dans une base de données relationnelle à trois relations.

Client(id_client, nom, prenom, adresse, ville, pays, telephone)

Reservation(id_reservation, #id_client, #id_emplacement, nombre_personne, date_arrivee, date_depart)

La troisième relation, Emplacement, contient tous les emplacements du camping. Extrait :

1. Citer deux avantages à utiliser une base de données relationnelle plutôt qu'un fichier texte ou tableur.
2. Quelle doit être la caractéristique d'un attribut pour pouvoir être utilisé comme clé primaire ?
3. Dans Reservation, quel est le rôle des clés étrangères id_client et id_emplacement ?
4. Donner le schéma relationnel de Emplacement, en précisant la clé primaire et le type de chaque attribut.
5. À partir de l'extrait donné, donner le résultat de :

SELECT id_emplacement, nom, localisation
FROM Emplacement
WHERE tarif_journalier = 25;

6. Écrire une requête donnant le nom et le prénom de tous les clients habitant à 'Strasbourg'.
7. Écrire une requête ajoutant un nouveau client : id_client 42, nom 'CODD', prénom 'Edgar', adresse '28 rue des Capucines', ville 'Lyon', pays 'France', téléphone '0555555555'.
8. Écrire une requête SQL récupérant, pour la réservation id_reservation = 18 : Client.nom, Client.prenom, Reservation.nombre_personne, Reservation.date_arrivee, Reservation.date_depart, Emplacement.tarif_journalier.

Exercice 2 (4 points) du sujet de bac NSI Polynésie 2023, jour 2.

Un site permet à ses membres de proposer et de louer du matériel. Modèle relationnel : Membre(id_membre, nom, prenom, cp), Objet(id_objet, description, tarif), Reservation(id_reservation, id_objet, id_membre, date_location, date_retour), Possede(id_membre, id_objet) — clé primaire de Possede : le couple (id_membre, id_objet).

Contenu : Membre : (1, "Ali", "Mohamed", "69110") ; (2, "Alonso", "Fernando", "69005") ; (3, "Dupont", "Antoine", "69003") ; (4, "Ferrand", "Pauline", "69160") ; (5, "Kane", "Harry", "69003"). Possede : (1,4), (1,6), (2,4), (3,3), (3,5), (4,1), (4,2). Objet : (1, "Nettoyeur haute pression", 20) ; (2, "Taille-haie", 15) ; (3, "Perforatrice", 15) ; (4, "Appareil à raclette", 10) ; (5, "Scie circulaire", 15) ; (6, "Appareil à gaufre", 10). Reservation : (1,4,5,2022-02-18,2022-02-19) ; (2,1,2,2022-05-05,2022-05-06) ; (3,3,1,2022-07-10,2022-07-12) ; (4,3,1,2022-08-12,2022-08-14) ; (5,2,2,2022-10-20,2022-10-22) ; (6,2,2,2022-10-20,2022-10-22).

1. Sans écrire de requête SQL, en étudiant les tables : a. Indiquer les prénoms et noms du ou des membres qui proposent la location d'un appareil à raclette. b. Donner le prénom et le nom du membre qui ne propose aucun objet à la location.
2. a. Donner le résultat de SELECT nom, prenom FROM Membre WHERE cp = "69003"; b. Écrire une requête donnant le tarif de location d'une scie circulaire. c. Écrire une requête modifiant le tarif de location d'un nettoyeur à haute pression, désormais 15 € par jour. d. Écrire une requête ajoutant Wendie Renard, habitant à Villeurbanne (code postal 69100), dans Membre, avec id_membre 6.
3. a. Expliquer la limitation importante que poserait le couple (id_objet, id_membre) comme clé primaire de Reservation. b. Mohamed Ali quitte le site. On tente DELETE FROM Membre WHERE nom = "Ali" AND prenom ="Mohamed"; : cette requête produit une erreur. Expliquer pourquoi. c. Proposer une suite de requêtes utilisant DELETE, précédant la requête ci-dessus, pour supprimer correctement Mohamed Ali (id_membre 1) de la base.
4. Ces requêtes utilisent des jointures (id_membre et id_objet supposés non connus) : a. Écrire une requête comptant le nombre de réservations réalisées par Fernando Alonso. b. Écrire une requête donnant les noms et prénoms des membres possédant un appareil à raclette.

Extrait partiel du sujet de bac NSI Polynésie 2023, jour 1 (23-NSIJ1PO1) — Exercice 1 uniquement (bases de données relationnelles et SQL), reconstitué à partir de 4 photos de téléphone couvrant les pages 2 à 4 sur 11 du sujet complet. Les 4 autres exercices du sujet ne sont pas couverts par ces photos et ne sont donc pas retranscrits ici.

La ligue féminine de basket-ball publie les données de chaque saison sur son site web : équipes participantes, calendriers, résultats des matchs, statistiques des joueuses. On s'intéresse à la base relationnelle LFB_2021_2022, qui stocke les données de la saison régulière 2021-2022.

1. La table Equipe

Contenu intégral de la table Equipe (extrait) :

(la colonne adresse, également présente dans la table, est omise ici par souci de concision — elle n'intervient dans aucune des questions ci-dessous)

Schéma relationnel de Equipe (attribut souligné = clé primaire) : Equipe(id_equipe INT, nom VARCHAR(50), adresse VARCHAR(100), telephone VARCHAR(20)), clé primaire id_equipe.

a. Après le chargement de la table Equipe, expliquer pourquoi la requête suivante produit une erreur :

INSERT INTO Equipe
VALUES (11, "Toulouse", "2 rue du Nord,40100 Dax", "05 04 03 02 01");

b. Expliquer le choix du domaine (type) pour l'attribut telephone.

c. Donner le résultat de la requête suivante :

SELECT nom, adresse, telephone FROM Equipe WHERE id_equipe = 5;

d. Donner et expliquer le résultat de la requête suivante :

SELECT COUNT(*) FROM Equipe;

e. Écrire la requête SQL permettant d'afficher les noms des équipes par ordre alphabétique.

f. Écrire la requête SQL permettant de corriger le nom de l'équipe dont l'id_equipe est égal à 4. Le nom correct est "Tarbes".

2. La table Joueuse

Sur le site web de la fédération, une page « Fiche Joueuse » présente, pour chaque joueuse, son nom, sa date de naissance, sa taille et son poste. Extrait de la table Joueuse :

(colonnes date_naissance, taille, poste omises ici, non nécessaires aux questions ci-dessous)

Schéma relationnel de Joueuse (# = clé étrangère) : Joueuse(id_joueuse INT, nom VARCHAR(50), prenom VARCHAR(50), date_naissance DATE, taille INT, poste INT, #id_equipe INT), clé primaire id_joueuse. La clé étrangère Joueuse.id_equipe référence la clé primaire Equipe.id_equipe.

a. Expliquer pourquoi l'attribut id_equipe a été déclaré clé étrangère.

b. On souhaite supprimer toutes les informations relatives à une équipe. Expliquer pourquoi on ne peut pas directement supprimer cette équipe dans la table Equipe.

c. Écrire la requête SQL qui permet d'afficher les noms et prénoms des joueuses de l'équipe d'Angers, par ordre alphabétique des noms. On suppose que l'utilisateur qui écrit cette requête ne connaît pas l'identifiant de l'équipe d'Angers.

3. La table Match

Les résultats des matchs sont aussi publiés sur le site de la ligue. Exemple, pour le match n°10, qui a opposé Villeneuve d'Ascq à Bourges le 23/10/2021 : Villeneuve d'Ascq (à domicile) 73 points, Bourges (en déplacement) 78 points.

a. À partir de cet exemple, proposer un schéma relationnel pour la table Match. Si des clés étrangères sont définies, préciser quelles tables et quels attributs elles référencent.

b. Écrire la requête SQL qui permet l'insertion dans la table Match de l'enregistrement correspondant à cet exemple.

4. Statistiques des joueuses par match

En plus du score final, la page web affiche des statistiques par joueuse pour chaque match, restreintes à 3 critères : points marqués, rebonds, passes décisives. Extrait pour le match n°53 (Landerneau 56 − 64 Charleville-Mézières, le 16/04/2022) :

a. Proposer un schéma relationnel pour stocker, dans la base de données, les informations de statistiques des joueuses telles que présentées ci-dessus.

b. Écrire la requête SQL qui a été utilisée pour afficher la partie « Extrait des statistiques » de l'exemple ci-dessus.

D'après une fiche d'exercices de NSI Terminale.

Un service hospitalier gère sa base avec trois tables :

• Patients (id INT, nom TEXT, prenom TEXT, genre TEXT, annee_naissance INT) ;
• Medecins (matricule INT, nom_prenom TEXT, specialite TEXT, telephone TEXT) ;
• Ordonnances (code INT, id_patient INT, matricule_medecin INT, date_ord TEXT, medicaments TEXT).

Une ordonnance est rédigée par un médecin pour un patient. Les dates sont écrites sous la forme jj-mm-aaaa.

1. Écrire le schéma relationnel de la table Ordonnances, en soulignant la clé primaire et en marquant les clés étrangères d'un #.
2. Écrire les requêtes de création des trois tables.
3. Enregistrer la patiente Inès Benali, née en 2005, sous le numéro 1. L'accueil voudrait aussi enregistrer son adresse : est-ce possible ?
4. Le patient numéro 100 a changé de prénom : il s'appelle désormais Alix. Écrire la requête correspondante.
5. Le service se sépare des médecins spécialisés en épidémiologie. Écrire la requête qui supprime leurs fiches. Que risque-t-il de se passer, et comment y remédier ?
6. Donner la requête qui liste les patients (nom et prénom, sans doublon) qui ont reçu une ordonnance d'un psychiatre en avril 2020.

D'après une fiche d'exercices de NSI Terminale.

Le stock d'un supermarché est géré avec trois tables :

• Fournisseurs (id, nom, adresse, cp, ville, telephone, courriel, responsable) ;
• Produits (id, nom_court, nom, prix_achat, prix_vente, fournisseur), où fournisseur fait référence à Fournisseurs.id ;
• Stocks (id, produit, quantite, date_peremption), où produit fait référence à Produits.id ; les dates sont au format aaaa-mm-jj.

Écrire les requêtes qui donnent :

1. le prix d'achat du produit dont le nom court est Liq_Vaiss_1L ;
2. l'adresse, le code postal et la ville du fournisseur nommé Avenir_confiseur ;
3. le nom des produits en rupture de stock ;
4. le nom de toutes les ampoules vendues (leur nom contient le mot « ampoule ») ;
5. le prix de vente moyen de ces ampoules ;
6. le nom du produit le plus cher du magasin ;
7. le nom des produits dont la date de péremption est dépassée.

Banque nationale de sujets 2026 de l'épreuve pratique, sujet n°15 (situation d'évaluation d'une heure).

Rappels de vaccination d'un cabinet vétérinaire

Cadre. Un cabinet vétérinaire utilise une base de données pour assurer le suivi et la gestion des clients et animaux qui y sont suivis, dans le but d'améliorer la qualité des soins et de fournir des services utiles, que ce soit pour le vétérinaire ou pour ses clients.

Objectif. La vaccination régulière des chats (une fois par an) étant importante mais souvent oubliée par les propriétaires, le vétérinaire souhaiterait pouvoir envoyer des messages de rappel de vaccination par SMS, deux mois avant l'échéance (la date à laquelle la vaccination devrait avoir lieu). Il dispose pour cela d'une plateforme d'envoi de SMS, mais il est nécessaire de préparer les données et les messages à envoyer.

Description de la base de données

Le logiciel mis en œuvre par le cabinet utilise 3 tables pour enregistrer les animaux, leur propriétaire et les consultations. Schéma de la base de données :

• consultation (id : int, id_animal : int, date : str (AAAAMMJJ), motif : str) — clé primaire id, clé étrangère id_animal ;
• animal (id : int, espece : str, nom : str, date_naissance : str (AAAAMMJJ), id_proprietaire : int) — clé primaire id, clé étrangère id_proprietaire ;
• proprietaire (id : int, nom : str, prenom : str, telephone : str) — clé primaire id.

consultation.id_animal fait référence à animal.id, et animal.id_proprietaire à proprietaire.id.

Extraits de la base de données :

Le système de gestion de base de données utilisé est SQLite, et il est possible d'interagir avec la base de données en utilisant le module sqlite3, disponible dans la bibliothèque standard Python.

Rappels techniques

La connexion à la base de données se fait en précisant l'emplacement du fichier de la base de données. Par exemple, si le fichier cabinet.sqlite se trouve dans le répertoire courant, on écrit :

DB_PATH = "cabinet.sqlite"
conn = sqlite3.connect(DB_PATH)

Une fois que l'on dispose d'une connexion (variable conn), on utilise un curseur (variable cursor) pour exécuter des requêtes sur la base de données et éventuellement récupérer les résultats de la requête.

cursor = conn.cursor()
resultats = cursor.execute(
    "SELECT id, nom FROM animal ORDER BY nom LIMIT 10"
)
# resultats permet de récupérer les lignes de manière
# progressive, sous forme d'une liste itérable de tuples
# correspondant aux champs demandés
# Pour accéder aux champs sélectionnés
for r in resultats:
    # les valeurs renvoyées par le SELECT sont
    # accessibles par leur position
    print("id:", r[0], ", nom:", r[1])

Lorsqu'un paramètre doit être donné dans une requête SQL, il est fourni sous forme d'un ? dans le texte de la requête SQL, et passé en argument à la fonction execute sous forme d'un tuple (chaque ? correspond à un élément du tuple, dans l'ordre). Par exemple, pour récupérer les chats du propriétaire d'id 2 :

resultat = cursor.execute(
    """SELECT nom FROM animal
    WHERE espece = 'chat' AND id_proprietaire = ?""",
    (2,)
)

Les clauses du langage SQL à utiliser sont : SELECT, FROM, JOIN, ON, WHERE, ORDER BY.

Numéros de téléphone

La plateforme d'envoi de SMS requiert un numéro de téléphone au format 06XXXXXXXX ou 07XXXXXXXX, 10 chiffres au total, sans espace, ni point, ni autre caractère. Malheureusement, les téléphones ont été enregistrés dans la base sans distinguer les téléphones mobiles des téléphones fixes, et en utilisant différents styles comme « (0)6 12 34 56 78 » ou « 01.23.45.67.89 ».

Rappel : on peut utiliser la méthode isdigit de la classe str pour déterminer si un caractère est un chiffre.

>>> '2'.isdigit()
True
>>> 'C'.isdigit()
False

Question 1. Écrire une fonction Python normalisation_tel qui prend un numéro de téléphone tel de type str en argument et qui renvoie le numéro de téléphone nettoyé en ne gardant que les chiffres. Exemples :

normalisation_tel("(0)2 12 99 90 12")  # renvoie "0212999012"
normalisation_tel("0.6.12.99.90.12")   # renvoie "0612999012"
normalisation_tel("0-6-12-9-90-1")     # renvoie "06129901"

Une fonction test_normalisation_tel est fournie.

Appel professeur — Appeler le professeur pour lui présenter votre réponse ou en cas de difficulté.

Validation des numéros de téléphone

La fonction validation_tel(tel) prend comme argument un numéro de téléphone nettoyé, et renvoie un booléen selon que ce numéro est un numéro de téléphone portable valide ou non.

Question 2. Écrire un jeu de tests permettant de vérifier le bon fonctionnement de la fonction.

Appel professeur — Appeler le professeur pour lui présenter votre réponse ou en cas de difficulté.

Détermination de la liste des chats à vacciner

La vaccination se faisant tous les ans, le vétérinaire souhaite envoyer trois rappels aux propriétaires. Chaque rappel sera donc envoyé aux propriétaires des chats ayant été vaccinés pour la dernière fois il y a entre 10 et 13 mois. Pour cela on commencera par déterminer quels chats ont été vaccinés il y a moins de 13 mois, à l'aide de la fonction consultation_vaccination_chat(date). Puis à l'aide de la fonction derniere_vaccination(consultations), on déterminera la date de dernière vaccination de ces chats. Enfin, dans la dernière étape du projet, se fera le filtrage des chats ayant été vaccinés pour la dernière fois il y a plus de 10 mois.

Interrogation de la base de données. Pour interroger la base de données, on utilise la fonction consultation_vaccination_chat(date) qui doit récupérer toutes les consultations de motif vaccination des chats (espèce : chat) du cabinet, pour des dates supérieures à une date date exprimée au format AAAAMMJJ (ce format permet de comparer des dates en utilisant l'ordre alphabétique). On souhaite récupérer les champs suivants : id de l'animal, nom de l'animal, téléphone du propriétaire, date de la consultation, triés par le champ id d'animal puis date de consultation croissants.

Question 3. En vous inspirant de la fonction proprietaires_animaux_nes_apres(date), écrire la fonction consultation_vaccination_chat(date). Des tests sont fournis dans la fonction test_consultation_vaccination_chat ; votre fonction doit les passer.

Appel professeur — Appeler le professeur pour lui présenter votre réponse ou en cas de difficulté.

Détermination de la date de dernière vaccination. Pour déterminer la date de dernière vaccination, on utilise la fonction derniere_vaccination qui prend en paramètre une liste de consultations avec les champs précédents (id de l'animal, nom de l'animal, téléphone du propriétaire, date de la consultation). Elle renvoie un dictionnaire associant comme clé l'id de chaque chat avec les informations de sa dernière (plus récente) consultation.

Exemple : pour Plume et Gollum,

(16, 'Plume', '0.6.36.96.89.83', '20241024'),
(16, 'Plume', '0.6.36.96.89.83', '20251125'),
(17, 'Gollum', '0.6.36.96.89.83', '20250113'),

la dernière consultation de Plume est le 20251125, et pour Gollum, c'est le 20250113 : derniere_vaccination doit donc renvoyer le dictionnaire

{
    16: (16, 'Plume', '0.6.36.96.89.83', '20251125'),
    17: (17, 'Gollum', '0.6.36.96.89.83', '20250113')
}

Cependant, la fonction ne produit pas les résultats attendus : les tests fournis dans la fonction test_derniere_vaccination échouent.

Question 4. Expliquer le problème rencontré, puis comment corriger cette fonction pour qu'elle passe les tests avec succès.

Appel professeur — Appeler le professeur pour lui présenter votre réponse ou en cas de difficulté.

Fichiers fournis

Le dossier comporte une version PDF de l'énoncé, le code source de départ veto.py et la base de données cabinet.sqlite (400 propriétaires, 411 animaux, 4 457 consultations). Le module sqlite3 doit être disponible.

veto.py

# -----------------------------------------------------------------------------
# Numéros de téléphones
# Question 1 : nettoyage des numéros de téléphone
 
# Écrire la fonction normalisation_tel ici
 
 
import sqlite3
 
 
def test_normalisation_tel():
    """
    Tous les tests doivent passer...
    """
    assert normalisation_tel("06 12 99 90 12") == "0612999012"
    assert normalisation_tel("02 12 99 90 12") == "0212999012"
    assert normalisation_tel("02.12.99.90.12") == "0212999012"
    assert normalisation_tel("0.6.12.99.90.12") == "0612999012"
    assert normalisation_tel("06-12-99-90-12") == "0612999012"
    assert normalisation_tel("(0)6.12.99-90-12 gilbert") == "0612999012"
    assert normalisation_tel("061299901") == "061299901"
    assert normalisation_tel("06129990123") == "06129990123"
    print('Les tests de la fonction normalisation_tel sont passés')
 
 
# -----------------------------------------------------------------------------
# Question 2 : validation des numéros de téléphone
 
 
def validation_tel(tel):
    """
    Validation des numéros de téléphone portable français
    selon les conditions spécifiées.
    :param tel: numéro de téléphone
    :return: True si le numéro est valide, False sinon
    """
    if len(tel) != 10:
        return False
    if tel[0] != "0":
        return False
    if tel[1] != "6" and tel[1] != "7":
        return False
    return True
 
 
# Ecrire votre jeu de tests permettant
# de vérifier le bon fonctionnement de la fonction.
 
 
# -----------------------------------------------------------------------------
# Détermination de la liste des chats à vacciner
# Question 3 : interrogation de la base de données
 
DB_PATH = "cabinet.sqlite"
 
 
def proprietaires_animaux_nes_apres(date):
    """
    Renvoie les noms et prénoms des propriétaires d'animaux nés après la date `date`,
    triés par ordre alphabétique de noms puis prénoms
 
    :param: date: date de naissance minimale
    :return: liste [(nom_proprietaire, prenom_ proprietaire)]
    """
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    resultat = cursor.execute(
        """
    SELECT proprietaire.nom, proprietaire.prenom
    FROM proprietaire
        JOIN  animal ON proprietaire.id = animal.id_proprietaire 
    WHERE animal.date_naissance > ?
    ORDER BY proprietaire.nom, proprietaire.prenom;
    """,
        (date,),
    )
    return list(resultat)
 
 
def consultation_vaccination_chat(date):
    """
    Renvoie les consultations de vaccination de chats dont la date
    est supérieure à la date `date`.
 
    :param: date: date minimale
    :return: liste [(id_animal, nom_animal, tel_proprietaire, date_consultation)]
    """
    pass
 
 
def test_consultation_vaccination_chat():
    vaccinations = consultation_vaccination_chat("20240923")
    assert len(vaccinations) == 118
    assert vaccinations[0] == (16, "Plume", "0.6.36.96.89.83", "20241024")
    assert vaccinations[1] == (16, "Plume", "0.6.36.96.89.83", "20251125")
    assert vaccinations[2] == (17, "Gollum", "0.6.36.96.89.83", "20250113")
    assert vaccinations[3] == (26, "Olympe", "(0)4 73 98 01 23", "20250109")
    assert vaccinations[4] == (32, "Chopin", "06.37.97.66.64", "20241201")
    assert vaccinations[5] == (32, "Chopin", "06.37.97.66.64", "20251119")
    assert vaccinations[6] == (34, "Jazz", "0.6.37.51.65.52", "20250801")
    assert vaccinations[7] == (35, "Tango", "0324182", "20250706")
    assert vaccinations[8] == (38, "Loulou", "05-35-95-87-54", "20250209")
 
    # Vérification stricte du tri (ORDER BY id_animal, date_consultation)
    assert vaccinations == sorted(vaccinations, key=lambda x: (x[0], x[3]))
 
    print('Les tests de la fonction consultation_vaccination_chat sont passés')
 
# test_consultation_vaccination_chat()
 
# -----------------------------------------------------------------------------
# Question 4 : détermination de la date de dernière vaccination
 
 
def derniere_vaccination(consultations):
    """
    Renvoie un dictionnaire ayant pour clef l'identifiant de l'animal,
    et dont la valeur associée est la dernière consultation de cet animal.
 
    Chaque consultation est un tuple :
 
    (id_animal, nom_animal, tel_proprietaire, date_consultation)
    """
    derniere = {}
    for consult in consultations:
        id_animal = consult[0]
        date = consult[3]
        if id_animal not in derniere:
            derniere[id_animal] = consult
        elif date < derniere[id_animal][3]:
            derniere[id_animal] = consult
    return derniere
 
 
def test_derniere_vaccination():
    consultations_pour_test = [
        (16, "Plume", "0.6.36.96.89.83", "20241024"),
        (16, "Plume", "0.6.36.96.89.83", "20251125"),
        (17, "Gollum", "0.6.36.96.89.83", "20250113"),
        (26, "Olympe", "(0)4 73 98 01 23", "20250109"),
        (32, "Chopin", "06.37.97.66.64", "20241201"),
        (32, "Chopin", "06.37.97.66.64", "20251119"),
        (34, "Jazz", "0.6.37.51.65.52", "20250801"),
        (35, "Tango", "0324182", "20250706"),
        (38, "Loulou", "05-35-95-87-54", "20250209"),
        (39, "Tango", "07.45.48.02.42", "20250329"),
        (40, "Sésame", "07.45.48.02.42", "20250228"),
        (41, "Pixel", "0130709285", "20241204"),
        (41, "Pixel", "0130709285", "20251222"),
    ]
 
    resultat = derniere_vaccination(consultations_pour_test)
    # Affichage des résultats, décommenter si besoin
    # for a,r in resultat.items():
    #     print(a, ":", r)
 
    assert derniere_vaccination(consultations_pour_test) == {
        16: (16, "Plume", "0.6.36.96.89.83", "20251125"),
        17: (17, "Gollum", "0.6.36.96.89.83", "20250113"),
        26: (26, "Olympe", "(0)4 73 98 01 23", "20250109"),
        32: (32, "Chopin", "06.37.97.66.64", "20251119"),
        34: (34, "Jazz", "0.6.37.51.65.52", "20250801"),
        35: (35, "Tango", "0324182", "20250706"),
        38: (38, "Loulou", "05-35-95-87-54", "20250209"),
        39: (39, "Tango", "07.45.48.02.42", "20250329"),
        40: (40, "Sésame", "07.45.48.02.42", "20250228"),
        41: (41, "Pixel", "0130709285", "20251222"),
    }
    print('Les tests de la fonction derniere_vaccination sont passés')