Sujet de projet

Base de données d’un logiciel de gestion des dépenses entre amis

Contexte de la mission

Votre équipe a pour mission de développer la base de données d’un logiciel permettant de gérer les dépenses entre amis.

Vous allez donc devoir réfléchir à la manière de créer une base de données relationnelle pour ce type de logiciel. Attention, les besoins exprimés concernent le logiciel. Vous devez uniquement réfléchir à la gestion des données de l’application.

Livrables attendus

Dans le cadre de cette étude de cas, vous aurez à livrer :

  • Le schéma conceptuel de votre base de données.
  • Le schéma logique de votre base de données.
  • Un notebook permettant de créer votre base de données et de la peupler.
  • Un notebook permettant de tester votre base de données.
  • Un notebook permettant d’interroger votre base de données.

Les notebooks devront être agréables à lire :

  • Créez des champs titre pour structurer votre notebook.
  • Insérez des champs de texte pour expliquer ce que vous faites et argumenter vos choix.
  • Utilisez les champs de code pour vos requêtes en prenant soin qu’elles fonctionnent correctement.

Avertissement

Vous devez créer vos tables dans un espace spécifique de la base de données, que l’on appelle schéma afin de permettre aux enseignants de corriger de multiples projets avec potentiellement des tables qui ont le même nom.

Pour cela, vous devez créer un schéma avant la création des tables avec la commande :

%%sql
CREATE schema login

où login est le plus court des identifiants des membres du groupe.

Pour créer et interroger les tables (dans toutes les requêtes SQL) vous devrez préfixer de votre identifiant le nom des tables. Par exemple : login.MESSAGE.

Expression du besoin

L’objectif est de réaliser d’un logiciel de gestion des dépenses entre amis. Le besoin est divisé en plusieurs modules, avec un module de base qui est obligatoire pour permettre de valider une compétence au niveau Atteint. Le module optionnel est proposé aux étudiants désirant valider les compétences au-delà des attentes.

Afin de simplifier la première version du prototype, les fonctionnalités suivantes ne seront pas traitées :

  • Gestion des droits
  • Gestion de la balance pour maintenir l’information de la balance débit/crédit pour chaque utilisateur
  • Gestion des équilibres pour maintenir l’information de qui doit payer combien à qui
  • Gestion des remboursements pour maintenir l’information de qui a payé combien à qui
  • Vérification qu’un membre participant à une dépense pour un compte donné participe bien également à ce compte

Module de base (obligatoire)

Le module de base doit permettre de gérer un compte, ses membres et leurs dépenses. Chaque dépense est effectuée par un seul et unique membre et doit être remboursée en parts égales par un ou plusieurs membres. Une dépense peut être associée à une unique catégorie. La liste des catégories est définie à l’avance sans possibilité de modification dans cette première version du logiciel. Un compte est créé par un membre initial.

  • Gérer un compte : création, modification du nom du compte. Un compte possède un nom.
  • Gestion des membres d’un compte : ajout/suppression d’un membre, création/modification d’un membre (nom, email).
  • Gestion des dépenses : ajout/modification/suppression d’une dépense qui est décrite avec les informations suivantes :
    • Qui a payé la dépense
    • Qui doit rembourser la dépense
    • Montant de la dépense
    • Date de la dépense
    • Catégorie de dépense

Module de gestion des commentaires (optionnel)

Le module de gestion des commentaires doit permettre d’associer des commentaires à une dépense donnée. N’importe quel membre du compte peut réagir à un commentaire par l’intermédiaire d’un smiley. N’importe quel membre du compte doit pouvoir regarder un smiley pour identifier quels autres membres l’ont utilisé.

  • Gestion des commentaires : création, modification, suppression.
  • Un commentaire doit être associé aux informations suivantes :
    • Auteur du commentaire
    • Date du commentaire
    • Message
    • Liste de smileys (chacun décrit par un nom et url de l’image) avec pour chaque smiley les utilisateurs l’ayant sélectionné

Livrables à produire

Le travail est à réaliser en binômes. Un seul monôme est autorisé par groupe de TD.

Premier livrable pour le dimanche 14 avril 2024 à 23h59

Tous les fichiers devront être réunis dans une archive nommée de la manière suivante nomX_nomY.zip (nomX étant le 1er nom du binôme par ordre alphabétique) et déposée sur Moodle avant le le dimanche 14 avril 2024 à 23h59.

Exercice 1

Proposez une modélisation conceptuelle de la base de données répondant aux besoins exprimés en justifiant et argumentant les choix qui pourraient être ambigus. Vous proposerez :

  • Un schéma conceptuel sous forme d’image (produite par un logiciel) dans un fichier nommé schema_conceptuel. L’image fournie doit être orientée dans le bon sens de lecture.
  • Votre argumentation/justification sous forme d’un fichier PDF nommé schema_conceptuel_argumentation.pdf.

Exercice 2

Proposez une dérivation en schéma logique du schéma conceptuel que vous avez proposé. Vous proposerez :

  • Un schema logique sous forme d’image (produite par un logiciel) dans un fichier nommé schema_logique. L’image fournie doit être orientée dans le bon sens de lecture.
  • Votre argumentation/justification du niveau de 3ème forme normale sous forme d’un fichier PDF nommé schema_logique_argumentation.pdf.

Deuxième livrable avant le dimanche 21 avril 2024 23h59

Tous les fichiers devront être réunis dans une archive nommée de la manière suivante nomX_nomY.zip (nomX étant le 1er nom du binôme par ordre alphabétique) et déposée sur Moodle avant le dimanche 21 avril 2024 23h59.

Exercices 1 & 2

Joignez à votre livrable final les schémas (conceptuel et logique) revus et corrigés.

Exercice 3

Créez un notebook nommé creation.ipynb dans lequel vous allez écrire le script nécessaire pour :

  • Détruire proprement toutes les tables créées (attention à l’ordre de destruction, regardez également l’option CASCADE du DROP)
  • Créer les tables de votre base de données.
  • Peupler votre base de données avec des exemples.

Respectez l’ordre indiqué précédemment en commençant par les destructions de tables.

Peupler votre base de données consiste à la remplir en simulant de l’activité. Vous êtes libres d’ajouter les informations que vous souhaitez afin de faire en sorte que vos requêtes retourne des résultats illmustrant leur bon fonctionnement.

Exercice 4

Créez un notebook nommé test.ipynb contenant 3 requêtes prouvant que vous avez géré correctement les contraintes d’intégrité. Vous devez écrire les requêtes SQL nécessaires pour :

  • Tester les contraintes d’intégrité d’entité (les clefs primaires).
  • Tester les contraintes d’intégrité référentielle (les clefs étrangères).
  • Tester toutes autres contraintes que vous auriez ajoutées.

Pensez à ajouter des explications pour expliquer la nature des contraintes que vous testez. Si vous avez ajouté d’autres types de contraintes (unicité ou non-nullité par exemple), vous êtes encouragés à les tester de façon à valoriser votre travail (et tenter d’avoir une évaluation “Au-delà des attentes”).

Exercice 5

Créez un notebook nommé query.ipynb contenant des requêtes pour afficher les informations suivantes :

  • Module de base (obligatoire)
    • Afficher la liste des membres du compte ayant pour identifiant 1.
    • Afficher la liste des dépenses (avec leur nom, date, montant et catégorie) du compte ayant pour identifiant 1.
    • Afficher les membres (nom et email) du compte ayant pour identifiant 1 qui doivent rembourser une dépense de la catégorie Cadeaux.
    • Afficher, pour le mois d’avril 2023, la liste des dépenses pour lesquelles le membre ayant pour identifiant 1 a avancé l’argent.
    • Afficher par catégorie de dépense, le montant dépensé.
  • Module de gestion des commentaires (optionnel)
    • Afficher les commentaires par ordre chronologique pour la dépense ayant pour identifiant 1.
    • Par commentaire et par smiley, affichez le nombre d’utilisateurs l’ayant utilisé si celui-ci est au moins égal à 3.
    • Par dépense supérieure à 100€, affichez le nombre de commentaires si celui-ci est au moins égal à 3.
    • Afficher sans doublon, la liste des membres du compte ayant pour identifiant 1, n’ayant jamais eu à rembourser une dépense associée à ce même compte.

Attention ! Vous devez avoir peuplé correctement la base de données, ce n’est pas au correcteur d’insérer de nouvelles données pour tester vos requêtes.

Avertissement

Pour les requêtes SQL utilisez la documentation suivante et aucune autre : https://docs.postgresql.fr/10/index.html (il s’agit de la bonne version pour la base de données installée sur JupyterHub)