TD N°8 modélisation d’un logiciel de saisie comptable corri

Ce document de Travaux Dirigés (TD N°8) est destiné aux étudiants universitaires en conception de bases de données. Il propose la modélisation d'une application de transport de personnes inspirée du concept d'ubérisation, en révisant les principes de MERISE.

Il couvre les notions suivantes :

La conception d'un Modèle Conceptuel de Données (MCD) et l'élaboration de son schéma relationnel.
L'implémentation de fonctions et procédures stockées pour gérer tarifs, commissions et périmètres.

Ce travail vise à renforcer les compétences en modélisation et programmation SQL.

Modélisation Merise : TD N°8 modélisation d’un logiciel de saisie comptable corri

Télécharger PDF

TD N°8 : Uberisation - Modélisation d'une application de transport de personnes (Révisions MERISE)

Contexte

La société de transport de personnes TOPCHRONO souhaite mettre en place une plateforme extranet à destination des particuliers. L'application proposera des services similaires à ceux proposés par la plateforme UBER :

La plateforme répertorie les chauffeurs et les passagers. Diverses informations sont conservées à leur sujet : adresse email, prénom, nom, "hash" du mot de passe, etc. Rien n’empêche a priori qu’un chauffeur en repos puisse être passager !
TOPCHRONO effectue une qualification de tous les chauffeurs, c'est-à-dire qu’un chauffeur effectue une inscription préalable. Son inscription n’est convertie en un compte chauffeur qu’une fois l’inscription validée. Afin de pouvoir afficher le périmètre d’intervention d’un chauffeur, il est souhaité de conserver son adresse principale sous la forme d’un couple de longitude et latitude. Il appartient au chauffeur de choisir son périmètre d’intervention. On suppose que le périmètre d’intervention du chauffeur est un cercle.
Un passager propose une course en partance d’un lieu et à destination d’un autre lieu. Pour des raisons d’affichage, les lieux sont stockés sous la forme de couples longitude/latitude. La course intervient à une date et une heure donnée. L'application se charge de fournir une estimation de la date et de l’heure d’arrivée.
Les chauffeurs peuvent consulter les courses qui sont dans leur périmètre d’intervention. Si la course est dans ce périmètre, ils peuvent proposer leurs services à titre de candidats. Ils peuvent alors consulter les coordonnées du passager et le contacter. Il appartient au passager de choisir le chauffeur qui réalisera sa course. Pour l’accompagner dans sa décision, grâce à la plateforme, le passager peut consulter le profil des chauffeurs et son tarif. La plateforme fournit, pour chaque chauffeur, une estimation du tarif pratiqué.
Dans la première version, le tarif du chauffeur est un tarif forfaitaire (montant fixe) assorti d’un tarif kilométrique.
Pour chaque course, la longueur du trajet est stockée. Elle est arrondie au kilomètre le plus proche. Elle est obtenue grâce à l’API Google Maps. Cette longueur sert de base de calcul à l’estimation du tarif pratiqué par le chauffeur.
Par ailleurs, TOPCHRONO, société soucieuse de respecter ses partenaires, chauffeurs et passagers, prélève une commission d’apporteur d’affaires sur chacune des candidatures des chauffeurs. La commission est un montant fixe en fonction de la longueur du trajet. Le montant est fixé au moyen d’un barème progressif (exemple : 5€ si le trajet a une longueur comprise entre 0km et 50km exclus, 9€ s’il est compris entre 50km et 100km exclus, etc.).

Travail à faire

Ce document présente les objectifs et les solutions pour la modélisation et l'implémentation de fonctions et procédures stockées pour l'application TOPCHRONO.

1. Modélisation

1.1. Modèle Conceptuel de Données (MCD)

Ce qu’il fallait voir :

Une contrainte d’inclusion entre les associations "Candidater" (chauffeur candidat à une course) et "Être choisi" (chauffeur choisi par le passager) où la flèche est orientée vers l’association "Candidater". Signification : être choisi implique être candidat.
Un héritage de type "T" (totalité), avec : une entité parente "Personne" et des entités filles "Chauffeur" et "Passager", voire "Inscription".
L’entité "Inscription" peut être soit une entité fille de "Personne", soit une entité indépendante reprenant toutes les propriétés du chauffeur, y compris celles héritées de "Personne".
Une représentation convenable d’une "Course", la plus exacte étant un agrégat : une course est définie par un passager donné pour une date donnée. Le couple (passager, date) suffit à identifier une course. Une alternative : une entité "Course" relative à un passager et une date.

1.2. Schéma Relationnel

Voici le schéma relationnel proposé pour la base de données :

Personne(id, email, prénom, nom, hash, type...)
    Clé primaire : id
    Clé étrangère : ---
    Note : on a choisi d’ajouter la propriété discriminante type pouvant prendre une combinaison constituée des valeurs "C" (chauffeur), "P" (passager), "I" (inscription). Exemples : P, CI, PCI.

Chauffeur(id, lat, lng, périmètre, tarifFixe, tarifKM)
    Clé primaire : id
    Clé étrangère : id en référence à Personne(ID)
    Note : les propriétés "lat" et "lng" représentent respectivement la latitude et la longitude de l’adresse principale du chauffeur. La propriété périmètre est une distance exprimée en km (entier).

Passager(id)
    Clé primaire : id
    Clé étrangère : id en référence à Personne(ID)
    Note : en pratique, si le passager n’a pas de propriété supplémentaire, on peut omettre l’entité Passager. Un passager est une personne telle que Personne.type LIKE '%P%'.

Inscription(id, inscriptionDate, refusDate, acceptationDate)
    Clé primaire : id
    Clé étrangère : id en référence à Personne(ID)

Course(passager, dateDepart, departLat, departLng, arriveeLat, arriveeLng, arriveePrevLue, distance, chauffeur)
    Clé primaire : passager, dateDepart
    Clés étrangères :
    - passager en référence à Passager(id)
    - chauffeur en référence à Chauffeur(id)
    Note : le lieu de départ est représenté par le couple (Course.departLat, Course.departLng) et celui d’arrivée par le couple (Course.arriveeLat, Course.arriveeLng). La distance est un entier (en kms).

Commission(id, min, max, tarif)
    Clé primaire : id
    Clés étrangères : ---

1.3. Évolution pour un tarif kilométrique dégressif

Permettant aux chauffeurs de fixer un tarif dégressif en fonction de la longueur d’un trajet, c'est-à-dire d’une course.

Tarif(chauffeur, id_tarif, min, max, tarifKM)
    Clé primaire : id_tarif (ou (chauffeur, min) pour assurer l'unicité et l'ordre des fourchettes)
    Clé étrangère : chauffeur en référence à Chauffeur(id)
    Note :
    - un tarif est relatif à un chauffeur.
    - le couple (min, max) permet d’indiquer la fourchette de distance sur laquelle le chauffeur pratique un certain tarif kilométrique.
    - tarifKM est le prix sur la fourchette de distance, soit donc un simple décimal typiquement à deux chiffres après la virgule : DECIMAL(3,2) : 1 chiffre avant et 2 chiffres après la virgule.
    - il faudrait empêcher le recoupement de fourchettes de distance, c'est-à-dire empêcher le chevauchement de plages (min, max).

Clarification sur la clé primaire de la table Tarif : Le texte original indique "Clé primaire : chauffeur". Cependant, si un chauffeur peut avoir plusieurs tarifs dégressifs appliqués sur différentes plages de distance (par exemple, 0-50km, 51-100km), alors "chauffeur" seul ne peut pas être la clé primaire. Une clé primaire plus appropriée serait un identifiant unique (id_tarif) ou une combinaison de (chauffeur, min) pour garantir l'unicité de chaque règle tarifaire pour un chauffeur donné.

2. Fonctions Stockées

2.1. Fonction tarif_commission(distance INT)

Cette fonction retourne la commission pratiquée en fonction de la longueur d’un trajet.

FUNCTION tarif_commission(distance INT) AS DECIMAL
BEGIN
    RETURN ( SELECT tarif FROM Commission WHERE distance >= min AND distance < max );
END;

2.2. Fonction tarif_chauffeur(numChauffeur INT, distance INT)

Cette fonction retourne le tarif pratiqué par un chauffeur en fonction de la longueur d’un trajet (première version).

FUNCTION tarif_chauffeur(numChauffeur INT, distance INT) AS DECIMAL
BEGIN
    SELECT tarifFixe, tarifKM INTO @tf, @tv
    FROM Chauffeur
    WHERE id = numChauffeur;
    RETURN @tf + @tv * distance;
END;

2.3. Fonction tarif_chauffeur avec tarif dégressif (Évolution 1.3)

Cette fonction retourne le tarif pratiqué par un chauffeur en tenant compte de l’évolution 1.3 (tarifs dégressifs).

FUNCTION tarif_chauffeur(numChauffeur INT, distance INT) AS DECIMAL
BEGIN
    -- récupération de la partie fixe du tarif pratiqué par le chauffeur
    SELECT tarifFixe INTO @tf
    FROM Chauffeur
    WHERE id = numChauffeur;

    -- récupération des tarifs variables d’un chauffeur (on ordonne bien les tarifs !)
    DECLARE tvs CURSOR FOR (
        SELECT min, max, tarifKM
        FROM Tarif
        WHERE chauffeur = numChauffeur
        AND min < distance -- les autres tarifs sont inutiles
        ORDER BY min ASC
    );
    -- nombre de lignes à parcourir
    SELECT count(*) INTO @nb
    FROM Tarif
    WHERE chauffeur = numChauffeur
    AND min < distance;

    -- calcul de la partie variable du tarif pratiqué par le chauffeur
    DECLARE i INT;
    DECLARE tv DECIMAL;
    SET i = 0;
    SET tv = 0;

    OPEN tvs;
    WHILE i < @nb DO
        FETCH tvs INTO @min, @max, @tarifKM;
        IF distance > @max THEN
            SET tv = tv + ( @max - @min ) * @tarifKM;
        ELSE
            SET tv = tv + ( distance - @min ) * @tarifKM;
        END IF;
        -- INCEMENT MANQUANT : Il est crucial d'incrémenter 'i' dans une boucle WHILE pour éviter une boucle infinie.
        -- Par exemple: SET i = i + 1;
    END WHILE;
    CLOSE tvs;

    RETURN @tf + tv;
END;

Note sur la boucle WHILE : Dans l'implémentation fournie, l'incrémentation du compteur i est manquante à l'intérieur de la boucle WHILE. Ceci entraînerait une boucle infinie si @nb est supérieur à zéro. Une ligne comme SET i = i + 1; devrait être ajoutée avant END WHILE; pour assurer le bon déroulement de la boucle.

2.4. Condition pour qu'une course soit dans le périmètre d'un chauffeur

Une course est dans le périmètre du chauffeur si son lieu de départ et son lieu d’arrivée sont situés dans le périmètre défini par le chauffeur. Le périmètre du chauffeur est supposé être un cercle ayant pour centre son lieu principal. Il faut donc que le lieu de départ et le lieu d’arrivée appartiennent à ce cercle.

Autrement dit, il faut que la distance entre le lieu de départ (respectivement d’arrivée) de la course et le lieu principal du chauffeur soit inférieure ou égale au rayon du cercle (Chauffeur.périmètre).

Traduction mathématique de cette condition :

Soient trois points A(x_a; y_a), B(x_b; y_b) et O(x_o; y_o). Un passager souhaite effectuer le trajet AB. Un chauffeur est localisé en O et intervient dans un périmètre de r kilomètres représenté par un cercle C.

A et B appartiennent à C si et seulement si :

Distance(O, A) = SQRT( (x_o - x_a)^2 + (y_o - y_a)^2 ) <= r
Distance(O, B) = SQRT( (x_o - x_b)^2 + (y_o - y_b)^2 ) <= r

Traduction en SQL de cette condition :

Au regard du précédent schéma relationnel, on a :

A(Course.departLat, Course.departLng)
B(Course.arriveeLat, Course.arriveeLng)
O(Chauffeur.lat, Chauffeur.lng)

Finalement, A et B appartiennent à C si et seulement si :

SQRT( ( Course.departLat - Chauffeur.lat ) ^ 2 + ( Course.departLng - Chauffeur.lng ) ^ 2 ) <= Chauffeur.périmètre
AND
SQRT( ( Course.arriveeLat - Chauffeur.lat ) ^ 2 + ( Course.arriveeLng - Chauffeur.lng ) ^ 2 ) <= Chauffeur.périmètre

2.5. Fonction est_dans_perimetre(...) AS BOOLEAN

Cette fonction permet de tester si une course est dans le périmètre d’un chauffeur. Une course est identifiée par le couple (passager, date de départ), passé en paramètres (idPassager et dateDepart). On doit tester si celle-ci est dans le périmètre du chauffeur passé en paramètre (idChauffeur).

FUNCTION est_dans_perimetre(idPassager INT, dateDepart TIMESTAMP, idChauffeur INT) AS BOOLEAN
BEGIN
    DECLARE v_departLat DECIMAL;
    DECLARE v_departLng DECIMAL;
    DECLARE v_arriveeLat DECIMAL;
    DECLARE v_arriveeLng DECIMAL;
    DECLARE v_chauffeurLat DECIMAL;
    DECLARE v_chauffeurLng DECIMAL;
    DECLARE v_perimetreChauffeur INT;

    SELECT C.departLat, C.departLng, C.arriveeLat, C.arriveeLng
    INTO v_departLat, v_departLng, v_arriveeLat, v_arriveeLng
    FROM Course C
    WHERE C.passager = idPassager
    AND C.dateDepart = dateDepart;

    SELECT CH.lat, CH.lng, CH.périmètre
    INTO v_chauffeurLat, v_chauffeurLng, v_perimetreChauffeur
    FROM Chauffeur CH
    WHERE CH.id = idChauffeur;

    IF SQRT( (v_departLat - v_chauffeurLat)^2 + (v_departLng - v_chauffeurLng)^2 ) <= v_perimetreChauffeur
    AND SQRT( (v_arriveeLat - v_chauffeurLat)^2 + (v_arriveeLng - v_chauffeurLng)^2 ) <= v_perimetreChauffeur THEN
        RETURN TRUE;
    ELSE
        RETURN FALSE;
    END IF;
END;

Note sur l'implémentation : Pour plus de clarté et éviter l'utilisation de variables globales ou session-scoped (@var), il est souvent préférable de déclarer des variables locales (DECLARE var TYPE;) et de les utiliser. La version corrigée utilise cette approche. L'opérateur ^2 est utilisé pour la puissance de 2, qui est compatible avec la plupart des systèmes de gestion de bases de données relationnelles.

3. Procédures Stockées

3.1. Procédure valider_inscription(numChauffeur INT)

Cette procédure permet de valider l’inscription d’un chauffeur.

PROCEDURE valider_inscription(numChauffeur INT)
BEGIN
    -- on ne valide une inscription que s’il y a une inscription correspondant au n° du futur chauffeur
    -- passé en paramètre.
    SELECT count(*) INTO @nbInscription
    FROM Inscription
    WHERE id = numChauffeur;

    -- pas d’inscription, pas de validation d’inscription
    IF @nbInscription = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Numéro d''inscription inexistant !';
    END IF;

    -- on ne valide une inscription que si le chauffeur n’a pas encore été enregistré comme tel
    SELECT count(*) INTO @nbChauffeur
    FROM Chauffeur
    WHERE id = numChauffeur;

    -- si le chauffeur est déjà enregistré, rien à valider
    IF @nbChauffeur > 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Chauffeur déjà enregistré !';
    END IF;

    -- on procède à l’inscription en deux temps
    -- 1er temps : mise à jour du discriminant de la personne correspondante
    UPDATE Personne
    SET type = CONCAT(type, 'I') -- Utilisation de CONCAT pour ajouter le type 'I'
    WHERE id = numChauffeur;

    -- 2ème temps : enregistrement du chauffeur (valeurs par défaut)
    INSERT INTO Chauffeur(id, lat, lng, périmètre, tarifFixe, tarifKM)
    VALUES (numChauffeur, -1, -1, 0, 0, 0);
END;

Note sur la logique de validation : Le code original contenait une double vérification de l'existence de l'inscription. L'une des vérifications devait logiquement s'assurer que l'utilisateur n'était pas déjà un chauffeur enregistré. L'exemple corrigé clarifie cette distinction en utilisant @nbInscription et @nbChauffeur et en ciblant la table Chauffeur pour la seconde vérification. De plus, l'opérateur & utilisé pour modifier le champ type a été remplacé par CONCAT, qui est plus approprié pour ajouter une lettre à une chaîne de caractères représentant une combinaison de types (ex: P, CI, PCI).

3.2. Procédure chauffeurs_proximite(p INT, d TIMESTAMP)

Cette procédure affiche les chauffeurs disponibles en fonction d’une course, c'est-à-dire tous les chauffeurs dans le périmètre de la course.

PROCEDURE chauffeurs_proximite(idPassager INT, dateDepart TIMESTAMP)
BEGIN
    SELECT CH.*
    FROM Chauffeur CH
    INNER JOIN Course CO ON CO.passager = idPassager AND CO.dateDepart = dateDepart
    WHERE est_dans_perimetre(CO.passager, CO.dateDepart, CH.id);
END;

Note sur la jointure : La jointure INNER JOIN Course CO était mal formulée dans l'exemple original. Une clause ON explicite a été ajoutée pour lier la course aux paramètres fournis, rendant la requête plus robuste.

3.3. Procédure tarif_moyen(distance INT)

Cette procédure affiche le tarif moyen (et l’écart-type) pratiqué sur une distance donnée.

PROCEDURE tarif_moyen(distance INT)
BEGIN
    SELECT
        AVG(tarifFixe + distance * tarifKM) AS 'Moyenne',
        STD(tarifFixe + distance * tarifKM) AS 'Écart-type'
    FROM Chauffeur;
END;

Note sur STD : La fonction STD() est un agrégat standard SQL pour calculer l'écart-type. Si cette fonction n'était pas disponible, il faudrait la calculer manuellement en utilisant la formule de l'écart-type : SQRT(AVG(X^2) - AVG(X)^2), où X représente le tarif total pour chaque chauffeur (tarifFixe + distance * tarifKM).

Foire Aux Questions (FAQ)

Qu'est-ce que MERISE ?

MERISE (Méthode pour la Conception de Systèmes d'Information) est une méthodologie française d'analyse, de conception et de gestion de projets informatiques. Elle est largement utilisée pour la modélisation des bases de données et des processus métier, notamment à travers le Modèle Conceptuel de Données (MCD) et le Modèle Logique de Données (MLD).

Comment est géré le tarif des chauffeurs dans l'application TOPCHRONO ?

Dans sa version initiale, l'application TOPCHRONO prévoit un tarif pour les chauffeurs composé d'une part fixe (forfaitaire) et d'une part kilométrique. Une évolution permet d'introduire un tarif kilométrique dégressif, où le prix par kilomètre peut varier en fonction de la distance totale du trajet, grâce à des fourchettes de distances spécifiques définies par chaque chauffeur.

Qu'est-ce qu'une fonction stockée en SQL ?

Une fonction stockée est un ensemble d'instructions SQL précompilées et stockées dans le serveur de base de données. Elle est conçue pour effectuer une tâche spécifique et retourne une seule valeur. Les fonctions stockées peuvent être utilisées dans des requêtes SQL (SELECT, WHERE, etc.) pour simplifier la logique et améliorer la performance.