Exercice 2 · Bac Numérique et Sciences Informatiques Polynésie 2023 (jour 2)

Exercice 2 (4 points)

Cet exercice traite du thème base de données, et principalement du modèle relationnel et du langage SQL.

L'énoncé de cet exercice peut utiliser les mots du langage SQL suivants:

SELECT, FROM, WHERE, JOIN ON, INSERT INTO, VALUES, UPDATE, SET, DELETE, COUNT, AND, OR

Un site permet à ses membres de proposer à la location du matériel et de louer du matériel. Ceci permet de mutualiser du matériel entre membres et au propriétaire de rentabiliser cet achat. Le temps d'utilisation du matériel s'en trouve ainsi augmenté et le nombre d'appareils diminué.

Le modèle relationnel est donné par le schéma ci-dessous.

La table Membre contient les informations de chaque utilisateur du site (nom, prénom et code postal). La table Objet décrit le type d'objet à la location ainsi que son tarif de location journalier.

La table Reservation répertorie toutes les réservations effectuées par les membres du site avec notamment leur date de début et de fin de location. La table Possede permet de lier les tables Membre et Objet.

On donne ci-dessous le contenu de ces tables à un instant donné:

Membre

id_membre

nom

prenom

cp

1 · "Ali" · "Mohamed" · "69110"

2 · "Alonso" · "Fernando" · "69005"

3 · "Dupont" · "Antoine" · "69003"

4 · "Ferrand" · "Pauline" · "69160"

5 · "Kane" · "Harry" · "69003"

Possede

id_membre

id_objet

1 · 4

1 · 6

2 · 4

3 · 3

3 · 5

4 · 1

4 · 2

Objet

id_objet

description

tarif

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

id_reservation

id_objet

id_membre

date_location

date_retour

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. Dans cette partie, on ne demande pas de requête SQL. En étudiant le contenu des tables ci-dessus:

a.

Indiquer quels sont les prénoms et noms du ou des membres du site qui proposent la location d'un appareil à raclette;

b.

Donner le prénom et le nom du membre qui ne propose pas d'objet à la location.

2.

a.

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

SELECT nom, prenom FROM Membre WHERE cp = "69003";

b.

Écrire une requête permettant de connaître le tarif de location d'une scie circulaire.

c.

Écrire une requête permettant de modifier le tarif de location d'un nettoyeur à haute pression pour le passer à 15 € par jour au lieu de 20 € par jour.

d.

Écrire une requête SQL permettant d'ajouter Wendie Renard habitant à Villeurbanne (code postal 69100) dans la table Membre, avec un id_membre de 6.

3.

a.

Expliquer la limitation importante d'utilisation du service offert par le site si l'on utilisait le couple de clés étrangères ( id_objet, id_membre) en tant que clé primaire de la relation Reservation.

b.

Mohamed Ali décide de ne plus être membre du site. Il faut donc le supprimer de la table Membre à l'aide de la requête:

DELETE FROM Membre

WHERE nom = "Ali" AND prenom = "Mohamed";

Expliquer pourquoi cette requête produit une erreur.

c.

Proposer une suite de requêtes utilisant le mot clé DELETE, précédant la requête ci-dessus pour supprimer correctement Mohamed Ali, dont l' id_membre est 1, de la base de données.

Rappel: la relation Objet décrit le type d'objet à la location. Il n'y a pas d'objet à supprimer dans cette table lors du départ d'un membre.

4. Dans cette partie, les requêtes utilisent des jointures entre tables. On supposera donc les numéros id_membre et id_objet non connus.

a.

Écrire une requête permettant de compter le nombre de réservations réalisées par Fernando Alonso.

b.

Écrire une requête permettant de connaître les noms et prénoms des membres possédant un appareil à raclette.