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

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

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

Pour la gestion des réservations clients, on dispose d’une base de données nommée gare dont le schéma relationnel est le suivant :

• Train(numT, provenance, destination, horaireArrivee, horaireDepart)

• Reservation(numR, nomClient, prenomClient, prix, #numT)

Les attributs soulignés sont des clés primaires. L’attribut précédé de # est une clé étrangère. La clé étrangère Reservation.numT fait référence à la clé primaire Train.numT.

Les attributs horaireDepart et horaireArrivee sont de type TIME et s’écrivent selon le format "hh:mm", où "hh" représente les heures et "mm" les minutes.

1. Quel nom générique donne-t-on aux logiciels qui assurent, entre autres, la persistance des données, l’efficacité de traitement des requêtes et la sécurisation des accès pour les bases de données ?

2. 1. On considère les requêtes SQL suivantes :

      DELETE FROM Train WHERE numT = 1241 ;
      DELETE FROM Reservation WHERE numT = 1241 ;

      Sachant que le train n°1241 a été enregistré dans la table Train et que des réservations pour ce train ont été enregistrées dans la table Reservation, expliquer pourquoi cette suite d’instructions renvoie une erreur.

   2. Citer un cas pour lequel l’insertion d’un enregistrement dans la table Reservation n’est pas possible.

Code de déblocage de la correction :

3. Écrire des requêtes SQL correspondant à chacune des instructions suivantes :

   1. Donner tous les numéros des trains dont la destination est « Lyon ».

   2. Ajouter une réservation n°1307 de 33 € pour M. Alan Turing dans le train n°654.

   3. Suite à un changement, l’horaire d’arrivée du train n°7869 est programmé à 08 h 11.
      Mettre à jour la base de données en conséquence.

Code de déblocage de la correction :

4. Que permet de déterminer la requête suivante ?

   SELECT COUNT(*) FROM Reservation
   WHERE nomClient = "Hopper" AND prenomClient = "Grace";

5. Écrire la requête qui renvoie les destinations et les prix des réservations effectuées par Grace Hopper.

Cet exercice traite principalement du thème « traitement de données en tables et bases de données ». Nous nous interrogerons dans cet exercice sur la modélisation et l'utilisation des données nécessaires aux fonctionnements de sites de vente en ligne

Partie A : Modèle relationnel

Voici le modèle relationnel qui sera utilisé dans cet exercice :

Lecture :

• un symbole identifie une clé primaire,

• un symbole entre deux attributs indique qu’ils doivent partager les mêmes valeurs. Ces valeurs sont prises de manière unique dans la relation du côté et zéro, une ou plusieurs fois du côté . à condition d’être présente du côté .

1. Donner les clés primaires des relations Clients et Articles.

2. Les commandes ci-dessous ont été utilisées pour créer ces deux relations.
   Donner le domaine (c'est-à-dire le type) des attributs Email et Quantite.

   CREATE TABLE Clients (
   IdClient INT PRIMARY KEY,
   Nom VARCHAR(50),
   Prenom VARCHAR(50),
   Email VARCHAR(50)
   );
   
   CREATE TABLE ArticlesCommande (
   IdCmd INT,
   IdArticle INT,
   Quantite INT,
   PRIMARY KEY (IdCmd,IdArticle)
   FOREIGN KEY(IdArticle) REFERENCES Articles(IdArticle)
   );

3. En vous inspirant des commandes ci-dessus, recopier et compléter la commande suivante qui permet de créer la relation Commandes en précisant sa clé étrangère.

   CREATE TABLE Commandes (
   IdCmd INT PRIMARY KEY,
   IdClient INT,
   Date DATE,
   AdresseLivraison VARCHAR(90),
   PaiementValide BOOLEAN,
   LivraisonFaite BOOLEAN,
   FOREIGN KEY(........................) REFERENCES ................. ........
   (...................................)
   );

Partie B : Site web

La plateforme de vente en ligne possède un site web pour ses clients qui passent des commandes en remplissant un formulaire.

1. Expliquer pourquoi, lorsqu'un formulaire contient une quantité importante de données, il est préférable d'utiliser la méthode POST plutôt que GET.

2. Pour la validation du paiement, est-il préférable d'utiliser le protocole HTTP ou HTTPS ? Pourquoi ?

3. Expliquer l'intérêt de vérifier, avant la validation du formulaire, le format des informations saisies (par exemple qu'il n'y a pas de chiffre dans le nom ou qu'il y a bien un @ dans l’adresse de courriel).

Partie C : Requêtes SQL

1. Écrire une requête SQL permettant de récupérer l'identifiant et le libellé de tous les articles coûtant moins de 15 euros.

2. Expliquer ce que fait la requête SQL suivante.

   SELECT u.IdClient, u.Email, v.IdCmd, v.AdresseLivraison
   FROM Clients as u JOIN Commandes as v
   ON u.IdClient = v.IdClient
   WHERE v.PaiementValide = False;

3. Écrire une requête SQL permettant de récupérer le libellé des articles de la commande 1345.

4. On suppose que l’attribut IdArticle de la table Articles est auto-incrémenté et ne doit donc pas être précisé lors de l'ajout d'un nouvel article.
   Écrire une requête SQL permettant d'ajouter dans la base ce nouvel article.

Partie D : Adaptation du modèle relationnel

Le propriétaire du site souhaite une adaptation du modèle relationnel afin de :

• comptabiliser le stock pour chaque article,

• pouvoir mémoriser, pour chaque client, une adresse de livraison par défaut de ses commandes.

1. Préciser, pour chaque relation que vous jugez nécessaire de modifier, les attributs ajoutés ainsi que leurs domaines.

2. À l’arrivée d’une nouvelle commande d’un client, l’algorithme de mise à jour de la base de données ci-dessous est exécuté.
   Indiquer l’erreur présente dans cet algorithme.

   Algorithme nouvelle_commande (commande)
   Début
       Pour chaque article de la commande Faire
           Si Quantité ≤ Stock Alors
               Stock ← Stock – 1
           Sinon
               annuler l’achat de cet article
           FinSi
       FinPour
   Fin

Dans cette partie, on se place dans le cadre d’applications Web de jeux accessibles en ligne via un navigateur. L’éditeur commercialise plusieurs jeux et fait donc appel à un Système de Gestion de Base de Données (SGBD) qui utilise le langage SQL.

On pourra utiliser les mots clés suivants du langage SQL :

SELECT, FROM, WHERE, JOIN, ON, INSERT, INTO, VALUES, UPDATE, SET, DELETE, ORDER, BY, ASC, DESC, AND et OR.

Le schéma relationnel de la base Jeux_WEB est constitué de trois relations (ou tables) :

• joueurs(id_joueur, pseudo, mail, mot_de_passe)

• jeux(id_jeu, nom_jeu, type_jeu)

• scores(id_jeu, id_joueur, score_joueur)

Dans ce schéma relationnel, les clés primaires sont en gras et les clés étrangères n'y sont pas représentées.

Tous les attributs dont le nom est préfixé par id sont des nombres entiers ainsi que l’attribut score_joueur. Les autres attributs sont des chaînes de caractères.

1. Donner des services rendus par un système de gestion de bases de données relationnelles.

2. Indiquer, pour chacun des trois enregistrements ci-dessous, la table à laquelle ils appartiennent.

   Enregistement 1 : 344OsmondiaAventure

   Enregistement 2 : 34410343

   Enregistement 3 : 1034THEBESTthebest@meilleur.frtheBest24

3. Indiquer un problème que peut poser le fait de stocker les mots de passe en clair des joueurs dans une table.

4. Indiquer si, avec le schéma relationnel proposé, deux joueurs peuvent ou non avoir le même pseudonyme.

5. Identifier les clés étrangères de la table scores et pour chaque clé étrangère, indiquer la table et l’attribut auxquels la clé étrangère fait référence.

6. Justifier s’il est possible ou non que dans ce schéma relationnel un joueur puisse jouer à plusieurs jeux.

7. Expliquer ce que l’on obtient si on exécute successivement les deux requêtes suivantes :

   INSERT INTO joueurs
       VALUES (1035, 'PAS2BOL', 'titi@mail.com', 'LoB2SaP');

   INSERT INTO joueurs
       VALUES (1035, 'SPECTRUS', 'titi@mail.com', 'LoB2SaP');

8. Écrire une requête SQL permettant d’ajouter un enregistrement à la table scores pour le joueur d’identifiant 1042 à un jeu d’identifiant 24 avec un score initial de 0 point.

9. Ce joueur d’identifiant 1042 venant de terminer une partie au jeu d’identifiant 24 avec un score de 8 points, écrire une requête SQL permettant de mettre à jour son score.

10. Écrire une requête SQL qui permet d'obtenir l'adresse mail de tous les joueurs ayant obtenu plus de 1000 points au jeu nommé SuperQuestLeague.

11. Le joueur Test2024, identifié par le numéro 618524036, a joué seulement à une partie du jeu SuperQuestLeague.
    Peu convaincu par les jeux en ligne proposés par l'éditeur, ce joueur décide de supprimer son compte.
    Donner l’ordre dans lequel le gestionnaire de la base de données doit supprimer les références liées à ce joueur en expliquant pourquoi, puis écrire la ou les requêtes permettant cette suppression du joueur en s'assurant que la base reste cohérente.