Bases de données

La bradgote ressemble à la fois à la belote, au bridge et au tarot.

La bradgote se joue à 4 joueurs appelés respectivement Nord, Est, Sud et Ouest.

La bradgote se joue avec un jeu de 52 cartes, que l'on distribue au début du jeu, 5 par 5, aux joueurs. Les 5 cartes d'un joueur s'appellent sa main. Les joueurs jouent par camp de 2 joueurs. Nord et Sud constituent un camp, Est et Ouest l'autre camp. Nord commence et Sud met ses cartes sur la table, face en l'air, avant de jouer. Nord pose une carte sur la table, puis Est en pose une (si possible de la même couleur) et ainsi de suite jusqu'à ce que 4 cartes soient sur la table. Celui qui a posé la carte de la plus haute valeur (à condition que ce soit dans la bonne couleur) remporte la levée (les 4 cartes) et pose la première carte de la levée suivante. Les cartes rapportent des points ainsi :

• Chaque roi (King) rapporte 5 points.
• Chaque dame (Queen) rapporte 4 points.
• Chaque valet (Jack) rapporte 2 points.
• Toutes les autres cartes rapportent 1 point chacune.

Pour connaître les points rapportés à un camp par la levée, on additionne les points des 4 cartes. La carte gagnante de la levée est celle qui rapporte le plus de points, tant qu'elle est de la bonne couleur. On suppose que Nord pose la première carte. Si Est n'a pas la couleur demandée, il a intérêt à poser une carte rapportant le moins de points possible, alors que si Sud n'a pas la couleur demandée, il a au contraire intérêt à poser une carte rapportant beaucoup de points puisque Nord est dans son camp.

On appelle valeur d'une carte le nombre qui est inscrit dessus, ou le mot roi, dame, valet ou as. La couleur d'une carte est l'un des mots carreau, coeur, pique ou trefle (SQL n'aime pas trop les accents).

I/ Construction de la base de données

On utilisera SQlite3. Python possède un module permettant de rédiger des Queries pour SQlite3 mais on peut aussi ouvrir un terminal et y écrire

♟️ sqlite3 cartes.db
sqlite> .headers ON
sqlite> .mode column

1) Création d'une table

La main du joueur nord sera une table appelée nord et construite ainsi :

sqlite> CREATE TABLE IF NOT EXISTS nord (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT, 
   ...> valeur TEXT, 
   ...> couleur TEXT, 
   ...> points INTEGER);

Voici le schéma de la base de données :

La clé primaire est soulignée.

2) Insertion d'une carte

On distribue des cartes. Nord reçoit d'abord 🂨. Comme c'est la première carte qu'il reçoit, son identifiant est 1. Sa valeur est 8, sa couleur est pique et ce n'est pas une figure, elle ne vaut donc qu'1 point. On écrit alors

sqlite> INSERT INTO nord VALUES 
   ...> (1,'8','pique',1);

Pour vérifier que nord a une carte on peut maintenant faire

sqlite> SELECT * FROM nord;

ce qui donne

id          valeur      couleur     points    
----------  ----------  ----------  ----------
1           8           pique       1         

3) Insertion de plusieurs cartes

Nord reçoit ensuite les cartes 🃋, puis 🂡, puis 🂫 et enfin 🃔. On peut comme précédemment insérer chaque carte dans une ligne (terminée par un point-virgule) mais aussi les insérer toutes en même temps dans la table.

sqlite> INSERT INTO nord VALUES 
   ...> (2,'valet','carreau',2),
   ...> (3,'as','pique',1),
   ...> (4,'valet','pique',2),
   ...> (5,'4','trefle',1);

La table nord contient alors les 5 cartes suivantes 

4) Contraintes d'intégrité

Les contraintes d'intégrité permettent de signaler des erreurs de saisie ou modification des données.

a) Domaine

Les points d'une carte ne peuvent être égaux qu'à 1, 2, 4 ou 5. Par exemple il n'y a pas de points négatifs.

b) Relation

La clé primaire ne peut prendre deux fois la même valeur dans une même table. Par exemple la clé id est un entier tel que deux cartes différentes n'ont pas le même id. Dans le DOM il en est de même : on peut donner le même nom à plusieurs éléments html mais pas le même identifiant.

c) Référence

Une clé étrangère se refère à une clé d'une autre table. Il faut évidemment que cette clé existe dans l'autre table.

II/ Requêtes

1) Nord a-t-il un as ?

sqlite> SELECT * FROM nord WHERE valeur='as';

La réponse est oui :

id          valeur      couleur     points    
----------  ----------  ----------  ----------
3           as          pique       1            

Remarque : comme on sait déjà que l'as n'est pas une figure et qu'on ne s'intéresse pas forcément à son identifiant on peut simplement faire

sqlite> SELECT valeur,couleur FROM nord WHERE valeur='as';

2) Combien Nord a-t-il de valets ?

sqlite> SELECT COUNT(*) FROM nord WHERE valeur='valet';

Nord a 2 valets.

3) Combien Nord a-t-il de figures ?

Comme les figures valent 2 points (valet), 4 points (dame) ou 5 points (roi), on reconnaît une figure à ce que ses points valent plus que 1 :

sqlite> SELECT COUNT(*) FROM nord WHERE points>1;

Nord a deux figures dans son jeu (les deux valets). On ne peut donc pas utiliser l'attribut valeur comme clé primaire puisque deux cartes peuvent avoir la même valeur. Idem pour la couleur et pour les points. C'est pour cela qu'on utilise aussi l'attribut id qui permet de repérer une carte dans la main de Nord.

4) Combien Nord a-t-il de points ?

sqlite> SELECT SUM(points) FROM nord;

La main de Nord vaut 7 points au total.

5) Nord veut ranger ses cartes

sqlite> SELECT * FROM nord ORDER BY couleur;

On y voit mieux comme ça :

id          valeur      couleur     points    
----------  ----------  ----------  ----------
2           valet       carreau     2         
1           8           pique       1         
3           as          pique       1         
4           valet       pique       2         
5           4           trefle      1 

III/ Un autre joueur

Pendant que Nord analyse ou range son jeu, on continue à distribuer les cartes. Par exemple pour le joueur Sud.

1) Distribution d'une autre main

sqlite> CREATE TABLE IF NOT EXISTS sud (
   ...> id INTEGER PRIMARY KEY AUTOINCREMENT, 
   ...> valeur TEXT, 
   ...> couleur TEXT, 
   ...> points INTEGER);

Sud reçoit successivement les cartes 🃚, 🂮, 🂻, 🃈 et 🃑 :

sqlite> INSERT INTO sud VALUES 
   ...> (1,'10','trefle',1),
   ...> (2,'roi','pique',5),
   ...> (3,'valet','coeur',2),
   ...> (4,'8','carreau',1),
   ...> (5,'as','trefle',1);

Cela a pour effet de créer une nouvelle table :

2) Base de données relationnelle

Maintenant il y a deux tables, comme on s'en rend compte en entrant

sqlite> .tables

nord  sud

Le nouveau schéma est :

3) Cartes jouables

Nord a besoin de jouer une carte qui permette à Sud de jouer aussi. Il faut donc que Sud possède une carte de la même couleur que celle que vient de jouer Nord. On cherche donc des cartes de Nord dont la couleur existe aussi dans la main de Sud.

sqlite> SELECT * FROM nord JOIN sud ON nord.couleur=sud.couleur;

On obtient alors

Ce tableau comporte 6 lignes, cela ne veut pas dire que Nord a 6 choix de cartes à jouer (il n'a que 5 cartes dans sa main). On obtient un tableau plus lisible avec :

sqlite> SELECT nord.valeur,nord.couleur,sud.valeur 
   ...> FROM nord JOIN sud 
   ...> ON nord.couleur=sud.couleur
   ...> ORDER BY nord.couleur;

Nord peut donc jouer carreau, pique ou trèfle. Dans les trois cas Sud peut suivre car Sud a du carreau, du pique et du trèfle. Les 6 couples de cartes jouables par le camp Nord-Sud sont (🃋,🃈) (🂨,🂮) (🂡,🂮) (🂫,🂮) (🃔,🃚) et (🃔,🃑). Parmi ces 6 choix, lequel maximise la valeur de la levée ?

4) Jeu optimal

Comme Nord et Sud sont dans le même camp, Nord a intérêt à jouer une carte telle que les points de sa carte et de celle jouée par Sud totalisent un maximum. Quel est ce maximum ?

sqlite> SELECT MAX(nord.points+sud.points) FROM nord JOIN sud ON nord.couleur=sud.couleur;

L'équipe Nord-Sud peut donc gagner 7 points. Comment ?

sqlite> SELECT nord.couleur,nord.valeur,sud.valeur 
   ...> FROM nord JOIN sud 
   ...> ON nord.couleur=sud.couleur
   ...> WHERE nord.points+sud.points=7;

couleur     valeur      valeur    
----------  ----------  ----------
pique       valet       roi     

Ainsi, Nord a intérêt à jouer la carte 🂫 qui vaut 2 points, et Sud (qui doit donc jouer pique) va jouer la carte 🂮 qui vaut 5 points, ce qui fait bien un total de 7 points.

5) Après avoir joué

Une fois que Nord et Sud ont joué 🂫 et 🂮, ces deux cartes sont retirées de leurs mains respectives.

sqlite> SELECT nord.id,sud.id 
   ...> FROM nord JOIN sud 
   ...> ON nord.couleur=sud.couleur
   ...> WHERE nord.points+sud.points=7;

permet de repérer les cartes à retirer du jeu. Puis

sqlite> DELETE FROM nord WHERE id=4;
sqlite> DELETE FROM sud WHERE id=2;

6) Clé étrangère

Le nombre de points que rapporte une carte ne dépend pas de la main dans laquelle se trouve la carte. On peut donc mettre les nombres de points dans une table qui leur est dédiée et qui associe les nombres de points aux valeurs des cartes :

La clé valeur est maintenant dite étrangère parce qu'elle se refère à une clé primaire qui est dans une autre table. Les clés étrangères sont désignées par le symbole # qui représente un point d'ancrage où placer le départ d'une flèche allant vers la clé primaire associée.

Les clés étrangères servent souvent à faire des jointures. La table dans laquelle on effectue des requêtes est obtenue en joignant les deux tables avec la condition que la clé étrangère et la clé primaire à laquelle elle se refère prennent la même valeur :

nord JOIN valeur ON nord.valeur=valeur.valeur

Avec les deux joueurs nord et sud le schéma devient alors :

IV/ Syntaxe de SQL

1) Conditions

Dans la suite, attribut désignera un attribut, mais condition peut désigner tout trajet parmi

La lettre R désigne une relation parmi <, <=, =, >= ou >.

2) Tables

Le mot table peut désigner une simple table mais aussi une jointure :

3) Sélection

Avec les notations précédentes, la syntaxe de SELECT est :

4) Mise à jour

5) Insertion