Chess Unofficial Federation

TP4 et TP5
Author

Ludovic Deneuville

Introduction

Important
  • Écrivez des requêtes jolies !

Ce sujet vous occupera pour les TP4 et 5.

1 Lancement des services

Connectez-vous à l’une des 2 instances suivantes d’Onyxia (elles sont quasiment identiques) :

Et ensuite lancez les services suivants (voir TP2 si besoin de plus de détails)

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

    • Cliquez sur la petite icone sous les triangles oranges qui ressemble à 📜
    • raccourci (ALT + X)
Reprise TP5

Après avoir chargé les données pour le TP5, veuillez appliquer ces corrections vues lors d’un exercice du TP4 :

UPDATE echecs.joueuse
   SET elo = elo - 1200
 WHERE elo > 2600
   AND code_titre IS NULL;

UPDATE echecs.joueuse
   SET date_naissance = date_naissance + INTERVAL '100 years'
 WHERE id_joueuse IN (111, 122, 133, 144);

UPDATE echecs.joueuse
   SET date_naissance = date_naissance - INTERVAL '100 years'
 WHERE id_joueuse IN (155, 166, 177, 188);

UPDATE echecs.joueuse
   SET nom = REPLACE(REPLACE(REPLACE(nom, 'EUH', 'E'), 'AO', 'A'), 'OU', 'U')
 WHERE code_titre IS NULL;

DELETE FROM echecs.partie
 WHERE id_blanc = id_noir;

2.2 Description

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)
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

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 :

    • Notez leurs id_joueuse

Une autre erreur concernerait les dates de naissances des joueuses

Toujours sur la table des joueuses, vous avez peut-être remarqué qu’une puissante technique d’anonymisation a été utilisée.

  • Vous pouvez utiliser la fonction REPLACE()
    • et même les enchainer : REPLACE(REPLACE(REPLACE()))
  • Attention : les noms des joueuses titrées n’ont pas été anonymisées

Si vous souhaitez utiliser des techniques d’anonymisation un peu plus efficaces, rendez-vous en 3A pour les Master ID et STD.

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

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.

    • affichez le nom du club, ainsi que tout le contenu de la table joueuse
    • en cas d’égalité, prenez ensuite le plus petit id_joueuse

5 Remise en route TP5

Quelques questions faciles pour se remettre en route.

    • Combien avez-vous de lignes ?
    • Combien de lignes compte la table joueuse ?

6 Quelques statistiques

Regardons maintenant quelques statistiques sur ces données.

    • affichez le nom du tournoi
    • incluez les parties jouées hors tournoi
  • 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

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

Ouvertures

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.

    • Trouvez et supprimez cette ouverture
    • nombre de parties jouées
    • taux de victoire des blancs
    • taux de victoire des noirs
    • taux de matchs nuls

7 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.

Enfin nous souhaitons récompenser la meilleure performance.

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

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.

8 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

    • en y insérant les données de la dernière requête

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
    • id_tournoi
    • le elo de l’adversaire
  • Vous pouvez utiliser une structure CASE WHEN THEN
SELECT <?>,
       CASE
           WHEN <?> THEN <?> 
           WHEN <?> THEN <?>
           ELSE <?>          
       END AS score
  FROM <?>

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

    • Plus la joueuse a affronté d’autres joueuses fortes, mieux elle sera classée

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;
    • 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)

9 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

    • classez par ratio de points par partie déscroissant

Arrêtez votre service

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

    • par exemple dans P:/Cours1A/UE3-Bases-de-donnees-relationnelles/TP4/tp4.sql
    • ou en téléchargeant depuis CloudBeaver le fichier sql (petit bouton avec un dossier et une flèche vers le bas)