Unofficial Chess Federation ♕

TP4

Introduction

Important

• Écrivez des requêtes jolies !
• À la fin du TP, exportez ou copiez vos requêtes dans un fichier de votre VM ou machine personnelle

1 Lancement des services

Pour plus de détails, allez dans l’onglet Datalab.

• Connectez-vous à un Datalab
• Lancez un service PostgreSQL
• Lancez un service cloudBeaver
  • ouvrez ce service
  • vérifiez que vous êtes connectés à la base de données PostgreSQL

2 Les données

Votre mission du jour sera d’aider la nouvellement créée Fédération Française des Échecs Sincéres et Sans Subterfuges à corriger les erreurs dans sa base de données pour ensuite récompenser ses meilleures joueuses.

2.1 Chargez les données

• Copiez le contenu de ces 2 scripts sql
  • création des tables
  • insertion des données
• Collez-les dans la fenêtre SQL de CloudBeaver
• Exécutez les scripts
  • Cliquez sur la petite icone sous les triangles oranges qui ressemble à 📜
  • raccourci (ALT + X)

2.2 Description des tables

Catégorie	Description
Joueuse	Liste des joueuses
Titre	Liste des titres, certains attribués aux fortes joueuses
Club	Liste des clubs
Partie	Partie entre 2 joueuses, soit libre, soit dans le cadre d’un tournoi
Résultat_partie	Différents résultats possibles dans une partie (par exemple : victoire des blancs, match nul, etc.)
Ouverture	Désignation de l’ouverture (comment débute une partie)
Tournoi	Liste des tournois
Cadence	Rythme de jeu lors d’un tournoi (blitz, rapide, lent)

2.3 Modèle de données

Caution

Dans le modèle de données, certaines tables sont reliées 2 fois entre elles :

• joueuse et partie : car lors d’une partie une joueuse a les blancs et l’autre, les noirs
• joueuse et club :
  • id_club est une clé étrangère dans la table joueuse car une joueuse est membre d’un seul club
  • id_presidente est une clé étrangère de la table club référençant l’id_joueuse de la présidente

Quelques remarques sur ce schéma :

• 🔑 clé primaire
• # clé étrangère
• NN : NOT NULL

2.4 Notation relationnelle

Les tables sont les suivantes :

• joueuse(id_joueuse, code_titre, nom, prenom, elo, date_naissance, #id_club, arbitre)
• titre(code, nom, description)
• club(id_club, nom, ville, #id_presidente)
• partie(id_partie, #id_blanc, #id_noir, date_partie, #id_resultat, #id_ouverture, #id_tournoi)
• resultat_partie(id_resultat, resultat, description)
• ouverture(id_ouverture, eco, nom, coups)
• tournoi(id_tournoi, nom, ville, date_debut, date_fin, #id_cadence, nb_rondes, nb_participantes, #id_arbitre)
• cadence(id_cadence, nom, description)

3 Recherche des irrégularités

À la fédération, il y a malheureusement de nombreuses rumeurs de corruption, et même de dopage !

Votre première mission sera de vérifier l’intégrité des données de la base.

Il paraîtrait que certaines joueuses aient truquées leurs classements elo. Vérifiez et corrigez cela :

• Listez la table des joueuses
• Classez par elo décroissant
• Écrivez une requête qui permet d’afficher les joueuses avec un elo suspect (elo > 2600 et non titrée)
  • Notez leurs id_joueuse
• Pour chacune de ces joueuses, diminuez leur elo de 1200
• Vérifier à partir de leurs id_joueuse que la mise à jour a bien été effectuée

Une autre erreur concernerait les dates de naissances des joueuses

• Affichez l’année de naissance des joueuses
• Regroupez par année et comptez le nombre d’occurences
• Écrivez une requête pour ne garder que les joueuses avec des années de naissances étranges
• Apportez une correction adaptée

Passons maintenant à la table des parties. À votre avis, est-ce normal de jouer des parties contre soi-même ?

• Détectez les parties suspectes
• À partir de ces données, créez une table tricheuse pour pouvoir surveiller leurs agissements par la suite
• Supprimez les parties suspectes

4 Élection des présidentes

Vous avez peut-être remarqué que les clubs n’ont pas de présidentes, nous allons donc organiser des élections sans triche !

Mais pour faire plus simple, la fédé a décidé de nommer comme présidente de chaque club, sa joueuse ayant le plus faible elo.

• Listez pour chaque club, ses joueuses
  • affichez le nom du club, ainsi que tout le contenu de la table joueuse
• Pour chaque club, trouvez le elo minimum
• Stockez dans une table temporaire tous les couples (id_club, elo_minimum)
• Trouvez pour chaque club, la joueuse avec le plus petit elo
  • en cas d’égalité, prenez ensuite le plus petit id_joueuse
• Stockez dans une table temporaire les couples (id_club, id_joueuse de la future présidente)
• Mettez à jour la table club avec leurs nouvelles présidentes
• Listez les clubs et leurs présidentes

5 Quelques statistiques

Regardons maintenant quelques statistiques sur ces données.

• Comptez le nombre de parties jouées
• Affichez le nombre de parties jouées par tournoi
  • affichez le nom du tournoi
  • incluez les parties jouées hors tournoi
• Pour chaque tournoi, calculez également les taux de victoire des blancs et des noirs, ainsi que celui de matchs nuls

Tip

• Commencez par exemple par calculer le nombre de victoires des blancs
• Divisez par le bon nombre
• Pour avoir un float, vous pouvez multiplier par 1.0
• Utilisez ROUND() pour arrondir à 2 chiffres significatifs

• Complétez pour ne conserver que les tournois avec 20 % ou moins de matchs nuls

Le but des questions suivantes va être d’afficher le nombre de parties jouées en tournoi et hors tournoi.

Le résultat doit être l’un des deux ci-dessous :

tournoi	nb_parties
false	10000
true	3610

nb_parties_en_tournoi	nb_parties_hors_tournoi
3610	10000

Affichez ce résultat avec une requête utilisant :

• UNION
• COALESCE(id_tournoi IS NOT NULL, false)
• CASE WHEN
• FILTER

Jetons maintenant un oeil aux ouvertures qui permettent de gagner plus souvent.

NoteOuvertures

En début de partie, le nombre de coups possibles et corrects reste assez limité. Les différentes possibilités de commencer une partie sont catégorisées et appelées Ouvertures.

Vient ensuite le milieu de jeu lorsque toutes les pièces sont développées.

Et enfin (si personne ne s’est pris un échec et mat) la finale, i.e. lorsque les Dames ont été échangées et qu’il reste peu de pièces.

• Une des ouvertures n’existe pas, elle n’a été jouée dans aucune partie
  • Trouvez et supprimez cette ouverture
• Comme fait précédemment pour les tournois, calculez pour chaque ouverture :
  • nombre de parties jouées
  • taux de victoire des blancs
  • taux de victoire des noirs
  • taux de matchs nuls
• Affichez les joueuses ayant joué plus de 5 fois avec les pièces noires la Défense Sicilienne

6 Afficher les résultats de parties

Les résultats de parties sont généralement affichés sous ce format :

Aix-les-Bains 2006

Blancs		Rés	Noirs
PRZYMUSINSKI Nicolas	2043	0 - 1	f GREGOIRE Sylvain	2268
MEYNARD Thibault	2256	1 - 0	ZERMICHE Cherif	2035
HOUARD Yannick	2010	X - X	PEREZ Flavio	2230
COULON Guillaume	2001	0 - 1	JOLLY Jean-Francois	2211
FISCHER Marc	2185	X - X	DENEUVILLE Ludovic	2003
MATHIS Julien	1951	0 - 1	PIERONI Guillaume	2170
BOIZANTE Yvain	2148	X - X	SAEZ Nicolas	1998

L’objectif de cette partie est d’aboutir à ce résultat.

Vous noterez le f à la première ligne qui est l’éventuel titre de la joueuse en minuscule.

• Commencez par joindre les tables contenant les éléments à afficher
• Ensuite, gardez uniquement les colonnes nécessaires
• Concaténez titre, nom et prénom, et renommez les colonnes pour obtenir l’affichage demandé
• Affichez les résultat du Blitz à Rennes (tournoi)

Enfin nous souhaitons récompenser la meilleure performance.

Une performance est une victoire contre une joueuse ayant au moins 400 points elo de plus.

• Affichez toutes les performances
• Classez-les par différence de elo décroissante

Un paquet de 🍪 est à retirer dans le bureau du professeur pour la joueuse détentrice de la meilleure perf !

Et pour l’autrice de la plus belle contre-perf, notre partenaire premium, la Fédération Française de la Lose offre un magnifique tee-shirt floqué Reine des Mazettes, J’ai raté un mat en 1, Je suis passé de +10 à -10 ou encore J’ai fait pat avec Roi et Dame contre Roi.

7 Gagnantes des tournois

Via la table partie, il est possible de retrouver quelle joueuse a participé à quel tournoi.

Cependant ce n’est pas très intuitif, ni pratique.

Nous allons donc commencer par créer une vue des participantes

• Listez les différents tournois auquels a participé HOU Yifan
• Listez tous les différents couples (id_joueuse, id_tournoi) possibles et existants dans la table partie
• Créez la vue participante(id_tournoi, id_joueuse)
  • en y insérant les données de la dernière requête
• Vérifiez que cette vue contient 980 enregistrements

Pour trouver les gagnantes des tournois, il faut trouver leurs nombres de points marqués.

Une célébre citation dit « Je ne perds jamais, soit je gagne, soit j’apprends ».

Aux échecs, la victoire rapporte 1 point, le match nul 0.5 et la défaite 0.

Cependant un problème se pose : une seule ligne de la partie contient le nombre de points marqués de 2 joueuses.

id_partie	id_blanc	id_noir	resultat
1	11	22	1 - 0
2	123	99	X - X

Pour calculer les points de chaque joueuse, ce serait plus pratique de dupliquer chaque ligne pour avoir :

id_partie	id_joueuse	score
1	11	1
1	22	0
2	123	0.5
2	99	0.5

• Listez pour toutes les parties, le score des blancs (comme sur le tableau ci-dessus)
• Incluez également pour la suite, les colonnes :
  • id_tournoi
  • le elo de l’adversaire
• Faites de même pour les noirs et stockez le tout dans une vue partie_score

Tip

• Vous pouvez utiliser une structure CASE WHEN THEN

SELECT <?>,
       CASE
           WHEN <?> THEN <?> 
           WHEN <?> THEN <?>
           ELSE <?>          
       END AS score
  FROM <?>

• Vérifiez que pour toutes les parties, le score cumulé des deux joueuses vaut 1
• Affichez le classement par points de l’Open de Guingamp

Vous remarquez qu’il y a des égalités au nombre de points.

• Utilisez la moyenne elo des adversaires pour départager les ex æquo
  • Plus la joueuse a affronté d’autres joueuses fortes, mieux elle sera classée

NoteVérification

Une petite requête basique pour vérifier vos résultats :

WITH resultat_score AS (
    SELECT rp.*,
           CASE
               WHEN rp.resultat IN ('1 - 0', '1 - F') THEN 1 
               WHEN rp.resultat = 'X - X'             THEN 0.5
               ELSE 0
           END AS score_blanc,
           CASE
               WHEN rp.resultat IN ('0 - 1', 'F - 1') THEN 1 
               WHEN rp.resultat = 'X - X'             THEN 0.5
               ELSE 0
           END AS score_noir
      FROM echecs.resultat_partie rp
    ),
partie_score AS (
    SELECT p.id_partie,
           p.id_tournoi,
           UNNEST(ARRAY[id_blanc, id_noir]) AS id_joueuse,
           UNNEST(ARRAY[score_blanc, score_noir]) AS score,
           UNNEST(ARRAY[jn.elo, jb.elo]) AS elo_adversaire
      FROM echecs.partie p
      JOIN resultat_score USING(id_resultat)
      JOIN echecs.joueuse jb ON (jb.id_joueuse = p.id_blanc)
      JOIN echecs.joueuse jn ON (jn.id_joueuse = p.id_noir)
    )
SELECT CONCAT_WS(' ', LOWER(j.code_titre), j.nom, j.prenom) AS joueuse,
       j.elo,
       SUM(ps.score) AS points,
       ROUND(AVG(ps.elo_adversaire)) AS moyenne_elo_adversaire
  FROM echecs.joueuse j
  JOIN partie_score ps USING(id_joueuse)
  JOIN echecs.tournoi t USING(id_tournoi)
 WHERE t.nom = 'Open de Guingamp'
 GROUP BY j.id_joueuse,
          joueuse,
          j.elo
 ORDER BY points DESC,
          moyenne_elo_adversaire DESC;

• Créez une vue contenant le classement de chaque joueuse dans chaque tournoi
  • incluez le nom du tournoi, de la joueuse, son nombre de points, la moyenne elo des adversaire
  • calculez son classement dans le tournoi (💡 voir RANK OVER PARTITION BY)
• Affichez la liste de toutes les gagnantes des tournois

8 Qui est la meilleure ?

Nous souhaitons calculer le pourcentage de points marqués par chaque joueuse, ainsi que ses taux de victoires, matchs nuls et défaites.

Choisissez une joueuse, par exemple Judith POLGAR

• En utilisant une vue déjà créée, calculez son nombre de parties jouées et son nombre de points marqués
• Calculez le ratio pour avoir son ratio de points par partie
• Calculez ses taux de victoires, matchs nuls et défaites
• Adaptez la requête pour faire ces calculs pour toutes les joueuses
  • classez par ratio de points par partie déscroissant

TipSolutions

• Correction TP4

Arrêtez vos services

C’est la fin du TP, vous pouvez maintenant sauvegarder votre travail et libérer les ressources réservées :

• Copiez-collez vos scripts SQL sur votre machine
• Arrêter les services du Datalab