Exercice 1 · Bac Numérique et Sciences Informatiques Amérique du Nord 2022 (jour 1)

Cet exercice porte sur les bases de données relationnelles et le langage SQL.

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

SELECT, FROM, WHERE, JOIN, INSERT INTO, VALUES, UPDATE, SET, COUNT

On rappelle qu'en SQL, la fonction d'agrégation COUNT permet de compter le nombre d'enregistrements dans une table.

L'entreprise « Vacances autrement » propose des séjours d'une durée d'une semaine dans différentes stations situées en France. Les séjours sont organisés autour d'une seule activité sportive. Par exemple, la station « La Tramontane Catalane » située à Leucate propose une formule autour de la planche à voile. Les clients pourront durant toute la durée du séjour pratiquer ce sport: le matériel est fourni et deux sessions encadrées par des moniteurs diplômés sont organisées chaque journée. Cette même station propose aussi des séjours pour les amateurs de kitesurf.

Les réservations se font par l'intermédiaire d'un site internet: chaque client complète un formulaire avec ses données personnelles (nom, prénom...) et choisit le séjour souhaité.

Ce site s'appuie sur une base de données relationnelle. Elle contient notamment les relations Station, Sport, Client et Sejour.

Voici le schéma relationnel où les clefs primaires sont soulignées et les clefs étrangères sont suivies du symbole #:

Station ( nomStation, ville, region)

Sport ( nomSport, nomStation#, prix)

Client ( cID, nom, prenom, mail)

Sejour ( cID, semaine, annee, nomStation#, nomSport#)

Les différentes stations sont stockées dans la relation Station. Chaque station est identifiée par un nom caractéristique ( nomStation). Les attributs ville et region permettent de décrire la localisation de chacune des stations.

La relation Sport fait le lien entre les stations et les activités sportives qui y sont proposées. Les trois attributs sont nomSport, nomStation et prix. L'attribut prix est un nombre entier: il correspond au montant plein tarif (en euros) du séjour d'une semaine dans la station avec le sport associé.

La relation Client caractérise les clients de l'entreprise: ils sont identifiés par un nombre entier ( cID) et décrits par leur nom, prénom et adresse mail.

La relation Sejour permet de lister les séjours auxquels les clients ont participé. Les deux attributs semaine et annee sont des nombres entiers: ils permettent d'identifier le moment où a été effectué chaque séjour ( semaine est le numéro de la semaine pendant laquelle le séjour a été réalisé).

Exemple: un séjour ayant été effectué la semaine numéro 12 de l'année 2020 correspond à semaine = 12 et à annee = 2020.

On donne aussi un extrait des trois premières lignes de chacune de ces relations:

Station

nomStation

ville

region

La tramontane catalane

Leucate

Occitanie

La baie sauvage

La Torche

Bretagne

La pinède

Calvi

Corse

Sport

nomSport

nomStation

prix

planche à voile

La tramontane catalane

1200

kitesurf

La tramontane catalane

1100

plongée

La baie sauvage

950

Client

cID

nom

prenom

mail

1

GENEREUX

Eric

eric.genereux@mail.fr

2

PIERRE

Daniel

daniel.pierre@mail.fr

3

JOLY

Emilie

emilie.joly@mail.fr

Sejour

cID

semaine

annee

nomStation

nomSport

1

26

2020

La tramontane catalane

planche à voile

1

38

2020

La tramontane catalane

planche à voile

2

33

2020

La baie sauvage

plongée

1. (a)

Donner la clé primaire et les éventuelles clés étrangères de la relation Sport.

1. (b)

Citer une contrainte d'intégrité de domaine puis une contrainte d'intégrité de relation et enfin une contrainte d'intégrité de référence que doivent respecter les données de la relation Sport.

2. (a)

Le tarif du séjour à la station « La tramontane catalane » basé sur la planche à voile passe de 1 200 euros à 1 350 euros.

La requête SQL suivante a été utilisée pour la mise à jour du tarif:

INSERT INTO Sport VALUES ("planche à voile", "La tramontane catalane", 1350);

Cette requête a été rejetée et la mise à jour n'a pas été effectuée. Après avoir expliqué pourquoi cette requête a été refusée, proposer une requête SQL qui permettra de modifier le tarif de ce séjour.

2. (b)

Une nouvelle station vient d'être référencée. Son nom est « Soleil Rouge ». Elle est située à Bastia en Corse. Des séjours d'une semaine y seront organisés. Le sport pratiqué sera la plongée au tarif de 900 euros.

Écrire les requêtes SQL permettant d'insérer ces nouvelles données dans la base.

3. (a)

L'agence souhaite envoyer un mail d'information à ses clients pour leur présenter cette nouvelle possibilité de séjour mise en place à Bastia. Écrire une requête SQL permettant d'obtenir l'adresse mail de tous les clients.

3. (b)

Un client qui pratique la plongée souhaite réserver un séjour. Écrire une requête SQL permettant d'obtenir le nom de toutes les stations où l'on peut pratiquer la plongée.

4. (a)

Pour faire son choix, le client souhaiterait connaître les villes où sont situées les stations dans lesquelles il pourra pratiquer la plongée.

Écrire une requête SQL permettant d'obtenir le nom des villes ainsi que le nom des stations où l'on peut pratiquer la plongée.

4. (b)

Écrire une requête SQL permettant de déterminer le nombre total de séjours effectués en Corse durant l'année 2020.