TP Base de données

Auteur: Pavlovich Ivan
Git: https://gitedu.hesge.ch/ivan.pavlovic/tp_sdb

Introduction

Ma base de données est basée sur les leagues de volley-ball. Je représente les joueurs, les équipes, les clubs, les leagues et les matchs joués par les équipes.

Modèle EA

Modèle relationnel

Personne (id, nom, prenom, email, dateNaissance, tel)

Equipe (id, nom, logo, idLeague, idClub)
     idLeague → League.id
     idClub → Club.id

Participation (idPersonne, idEquipe, numero, idPosition)
     idPersonne → Personne.id
     idEquipe → Equipe.id
     idPosition → Position.id

Position (id, nom)

League (id, nom)

Club (id, nom)

Match (id, score_equipe1, idEquipe1, score_equipe2, idEquipe2)
     idEquipe1 → Equipe.id
     idEquipe2 → Equipe.id

Arbitre (idPersonne, idMatch)
     idPersonne → Personne.id
     idMatch → Match.id

Limitation

1. Une personne ne peut pas être dans une équipe plusieurs fois.
2. Une équipe ne peut pas faire un match contre elle-même.
3. Une équipe ne peut pas faire un match contre une équipe qui dans un autre league.
4. Plusieurs personnes dans la même équipe ne peuvent pas avoir le même numéro.
5. Un club ne peut pas avoir plusieurs équipes dans la même league.

Partie 1 (SQLite)

Pour cette partie, j'ai utilisé SQLite pour créer et tester ma base de données. Il faut avoir le paquet "sqlite3":

sudo apt-get update
sudo apt-get install sqlite3

Pour créer le fichier ".db" utilisable par SQLite à partir de mon ".sql", j'ai utilisé la commande suivante:

sqlite3 nomDB.db < nomSql.sql

Puis pour tester mes SELECT, il faut entrer dans le mode console de SQLite avec :

sqlite3 nom.db

Triger SQLite

Mon trigger s'active quand on insert une nouvelle personne dans la table Personne et il ajoute la date et l'heure quand la personne a été ajoutée.

CREATE TRIGGER update_date_modification
AFTER INSERT ON Personne
FOR EACH ROW
BEGIN
    UPDATE Personne
    SET date_creation = DATETIME('now', 'localtime')
    WHERE id = NEW.id;
END;

Requêtes SQL

Pour ce TP, on a dû faire 12 requêtes de sélection différentes.

Simple

Sélectionne toutes les personnes de la table Personne:

SELECT * FROM Personne;

Sélectionne la personne la plus jeune :

SELECT Personne.nom, Personne.prenom, MAX(dateNaissance) FROM Personne;

Avec plusieurs jointures

Sélectionne le nom, prénom de la personne et l'équipe et club dans lequel elle se trouve :

SELECT Personne.nom, Personne.prenom, Equipe.nom, Club.nom 
FROM Participation
JOIN Personne ON Participation.idPersonne = Personne.id
JOIN Equipe ON Participation.idEquipe = Equipe.id
JOIN Club ON Equipe.idClub = Club.id;

Sélectionne le nom de l'équipe avec le club et la league dans laquelle elle se trouve :

SELECT Equipe.nom, Club.nom, League.nom
FROM Equipe
JOIN Club ON Equipe.idClub = Club.id
JOIN League ON Equipe.idLeague = League.id;

Sélectionne les matchs joués :

SELECT Match.id, Equipe1.nom, Match.score_equipe1, Equipe2.nom, Match.score_equipe2
FROM Match
JOIN Equipe AS Equipe1 ON Match.idEquipe1 = Equipe1.id
JOIN Equipe AS Equipe2 ON Match.idEquipe2 = Equipe2.id;

Sélectionne les personnes qui ont arbitré un match et qui sont dans une équipe en league nationale :

SELECT DISTINCT Personne.nom, Personne.prenom
FROM Arbitre
JOIN Personne ON Arbitre.idPersonne = Personne.id
JOIN Participation ON Personne.id = Participation.idPersonne
JOIN Equipe ON Participation.idEquipe = Equipe.id
JOIN League ON Equipe.idLeague = League.id
WHERE League.nom = 'League Nationale';

Requête avec GROUP BY

Sélectionne les équipes avec le nombre de personnes qu'ils ont par position :

SELECT Equipe.nom, Position.nom, COUNT(Participation.idPersonne)
FROM Equipe
JOIN Participation ON Equipe.id = Participation.idEquipe
JOIN Position ON Participation.idPosition = Position.id
GROUP BY Equipe.nom, Position.nom;

Sélectionne l'équipe avec le plus de matchs joués :

SELECT Equipe.nom, COUNT(*) AS NbMatchsJoues
FROM Equipe
JOIN Match ON Equipe.id = Match.idEquipe1 OR Equipe.id = Match.idEquipe2
GROUP BY Equipe.nom
ORDER BY NbMatchsJoues DESC
LIMIT 1;

Requête avec GROUP BY + HAVING

Sélectionne les équipes qui ont au moins 6 joueurs :

SELECT Equipe.nom, COUNT(Participation.idPersonne)
FROM Participation
JOIN Equipe ON Participation.idEquipe = Equipe.id
GROUP BY Equipe.nom
HAVING COUNT(Participation.idPersonne) >= 6;

Sélectionne les équipes qui ont fait plus de 2 matchs :

SELECT Club.nom, COUNT(Match.id)
FROM Club
JOIN Equipe ON Club.id = Equipe.idClub
JOIN Match ON Equipe.id = Match.idEquipe1 OR Equipe.id = Match.idEquipe2
GROUP BY Club.nom
HAVING COUNT(Match.id) > 2;

Requête avec une sous-requête

Sélectionne les équipes où "Ivan Pavlovich" joue :

SELECT Equipe.id, Equipe.nom, Equipe.logo, Equipe.idLeague, Equipe.idClub
FROM Equipe
WHERE Equipe.id IN (
        SELECT Participation.idEquipe 
        FROM Participation
        JOIN Personne ON Participation.idPersonne = Personne.id
        WHERE Personne.nom = 'Pavlovich' AND Personne.prenom = 'Ivan'
    );

Sélectionne les équipes dont tous les joueurs ont plus de 18 ans :

SELECT Equipe.nom, Equipe.logo
FROM Equipe
WHERE NOT EXISTS (
        SELECT *
        FROM Participation
        JOIN Personne ON Participation.idPersonne = Personne.id
        WHERE Participation.idEquipe = Equipe.id 
        AND (strftime('%Y', 'now') - strftime('%Y', Personne.dateNaissance)) < 18
    );

Partie 2 (MongoDB)

Pour cette partie, j'ai installé MongoDB sur ma machine pour avoir le service "mongod" et pouvoir tester mes requêtes mongo avec la console "mongosh". https://www.mongodb.com/docs/manual/tutorial/install-mongodb-on-ubuntu/

sudo apt-get install gnupg curl

curl -fsSL https://www.mongodb.org/static/pgp/server-7.0.asc | \
   sudo gpg -o /usr/share/keyrings/mongodb-server-7.0.gpg \
   --dearmor

echo "deb [ arch=amd64,arm64 signed-by=/usr/share/keyrings/mongodb-server-7.0.gpg ] https://repo.mongodb.org/apt/ubuntu jammy/mongodb-org/7.0 multiverse" | sudo tee /etc/apt/sources.list.d/mongodb-org-7.0.list

sudo apt-get update

sudo apt-get install -y mongodb-org

sudo systemctl start mongod

Exportation base de données SQLite en Mongo

Pour exporter ma base de données SQLite vers MongoDB, j'ai d'abord exporté toutes les tables vers des fichiers JSON puis importer ces fichiers dans MongoDB.

J'ai aussi utilisé le paquet "jq" pour manipuler les JSON.

sudo apt install jq

J'ai fait un script bash "exportSqliteToMongo.sh" qui m'export ma base de données volleyball.db en JSON et l'import dans MongoDB.

Pour exporter les tables du ".db" en JSON, j'ai utilisé la commande suivante:

sqlite3 -header -csv NomDBSQLite.db "SELECT * FROM NomTable;" | tail -n +2 | jq -R -s -c 'split("\n")[:-1] | map(split(",")) | map({"Champ1": .[0], "Champ2": .[1]})' > FichierJson.json

Pour importer les JSON dans ma base de données MongoDB, j'ai utilisé la commande suivante :

mongoimport --db NomDBMongo --collection NomTable --file FichierJson.json --jsonArray

Pour utiliser le scripte, il faut juste avoir la base de données "volleyball.db" dans le même dossier :

./exportSqliteToMongo.sh

Requêtes Mongo

J'ai fait les requêtes directement dans la console de MongoDB "mongosh".

mongosh

Afficher toutes les DB :

show databases

Sélectionner la base de données VolleyballDB :

use VolleyballDB

Afficher toutes les tables de la DB :

show collections

Pour faire mes requêtes, il faut sélectionner ma base de données "VolleyballDB".

Insertion

Insert une nouvelle personne dans la collection Personne :

db.Personne.insertOne({ id: "15", nom: "Mongo", prenom: "Test", email: "test.mongo@example.com", dateNaissance: "2022-06-20", tel: "0123456789", date_creation: new Date()})

Modification

Modifie les champs nom, prénom, email de la personne avec l'id 1 :

db.Personne.updateOne({ id: "1" }, { $set: { nom: "Pablo", prenom: "Yvanne", email: "yvanne.pablo@exemple.com"} })

Suppression

Supprime l'entrée avec le champs id égale à 14 :

db.Personne.deleteOne({ id: "14" })

Recherche avec projection et sélection

Sélectionne la personne avec l'index 1 et projette uniquement les champs nom et prénom en excluant le champ _id :

db.Personne.findOne({ id: "1"}, {nom: 1, prenom: 1, _id: 0})

Sélectionne l'équipe avec le nom "Equipe A1" et projette tous les champs en excluant le champ _id :

db.Equipe.findOne({nom: '"Equipe A1"'}, {_id: 0})

Aggregation

Regroupe les personnes par leur date de naissance et compte le nombre de personnes qui ont la même :

db.Personne.aggregate([{ $group: {_id: "$dateNaissance", count: { $sum: 1 } }}])

MapReduce

Regroupe les personnes par leur date de naissance et compte le nombre de personnes qui ont la même :

var mapDateNaissance = function() {
    emit(this.dateNaissance, 1);
};
  
var reduceCount = function(key, values) {
    return Array.sum(values);
};

db.Personne.mapReduce(
    mapDateNaissance,
    reduceCount,
    {
        out: "DateNaissanceCount"
    }
);

db.DateNaissanceCount.find();