Ce sujet s’inspire du sujet 9 du bac de NSI 2022

Thèmes abordés : bases de données et langage SQL.

On souhaite gérer un club de tennis en ligne avec la possibilité de réserver un terrain à un créneau horaire. Le site ne gère que des réservations pour des matchs en simple. Voici la structure de la base de données :

• Relation joueurs contenant l’ensemble des joueurs du club avec leurs identifiants.

• Relation matchs précisant les matchs joués.

• Relation terrains précisant les différents terrains.

• Relation creneaux précisant les créneaux réservables.

1. Clés primaires/étrangères :
   

• Donner la clé primaire de la relation matchs.

• La relation matchs a-t-elle une ou des clés étrangères ? Si oui quelles sont-elles ?

3. Requêtes en langage SQL (On pourra s’aider de l’annexe 2)

• Ecrire une requête qui renvoie les prénoms des joueurs dont le nom est ‘Dupont’.

• Ecrire une requête qui modifie le mot de passe de Dorine Dupont, son nouveau mot de passe étant 1976.

4. Ecrire une requête permettant d’ajouter le nouveau membre « Zora MAGID » dont le login est « zora » et le mot de passe 2021.

SET @id_max = (... requête qui recherche le plus grand id_joueur);
SELECT @id_max;
INSERT joueurs VALUES (@id_max+1,....

5. Ecrire une requête qui renvoie les jours où Alice joue.

2.. Par lecture et analyse des relations de la base de donnée.

• Déterminer le jour et la plage horaire du match entre Durand Belina et Caron Camilia.
• Déterminer le nom des deux joueurs qui sont les seuls à avoir joué dans le “hangar”.

INSERT INTO matchs VALUES
(7 ,'2020-08-08', 6 ,2 ,2 ,1),
(8 ,'2020-08-08', 7 ,3 ,2 ,1),
(9 ,'2020-08-08', 7 ,2 ,3 ,2);

+------------+------------+------------+------------+
| date_match | id_creneau | nom_joueur | nom_joueur |
+------------+------------+------------+------------+
| 2020-08-01 |          3 | Durand     | Caron      |
| 2020-08-08 |          7 | Caron      | Durand     |
+------------+------------+------------+------------+

INNER JOIN joueurs AS J1 ON J1.id_joueur=matchs.id_joueur1
INNER JOIN joueurs AS J2 ON J2.id_joueur=matchs.id_joueur2

+---------------+------------+
| prenom_joueur | nom_joueur |
+---------------+------------+
| Alice         | Dupont     |
| Belina        | Durand     |
+---------------+------------+

INNER JOIN joueurs ON joueurs.id_joueur=matchs.id_joueur1 OR joueurs.id_joueur=matchs.id_joueur2

Annexe 1

DROP TABLE IF EXISTS xxxx;
DROP TABLE IF EXISTS xxxx;
DROP TABLE IF EXISTS xxxx;
DROP TABLE IF EXISTS xxxx;

CREATE TABLE joueurs (
id_joueur INT,
nom_joueur VARCHAR(255),
prenom_joueur VARCHAR(255),
login VARCHAR(255),
mdp VARCHAR(255)
);

INSERT INTO joueurs VALUES
(1,'Dupont','Alice','alice','1234'),
(2,'Durand','Belina','belina','5694'),
(3,'Caron','Camilia','camilia','9478'),
(4,'Dupont','Dorine','dorine','1347');

CREATE TABLE matchs (
id_match INT,
date_match DATE,
id_creneau INT,
id_terrain INT,
id_joueur1 INT,
id_joueur2 INT
);

INSERT INTO matchs VALUES
(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);

CREATE TABLE terrains (
id_terrain INT,  
nom_terrain VARCHAR(255),
surface     VARCHAR(255)
);

INSERT INTO terrains VALUES
(1,'stade','terre battue'),
(2,'gymnase','synthétique'),
(3,'hangar','terre battue');

CREATE TABLE creneaux (
id_creneau INT,
plage_horaire VARCHAR(255)
);

INSERT INTO creneaux (id_creneau,plage_horaire) values
(1,'8h-9h'),
(2,'9h-10h'),
(3,'10h-11h'),
(4,'11h-12h'),
(5,'12h-13h'),
(6,'13h-14h'),
(7,'14h-15h'),
(8,'15h-16h'),
(9,'16h-17h'),
(10,'17h-18h'),
(11,'18h-19h'),
(12,'19h-20h');

Réflexion sur le schéma

Utiliser le fichier looping ci-joint et modifier le pour obtenir le bon MLD

• Comment a été modifié le sujet de bac ?
• Que doit on ajouter dans le schéma pour que la solution proposée dans le sujet de bac présente les mémes qualités ? (normalisation et dépendance fonctionnelle).

• SQL pour garantir l’intégrité des données, vous pouvez appliquer une contrainte d’unicité sur les colonnes correspondant aux clés étrangères pour éviter la présence de doublons. :

CONSTRAINT uk_table  UNIQUE (col1, col2, col);

doc unique key - doc index key

ANNEXE :

https://formations.imt-atlantique.fr/bd_ihm/fr/normalisation/normal_forms/