Ce document de Travaux Dirigés (TD N°7) et sa correction sont conçus pour les étudiants universitaires souhaitant approfondir leurs compétences en conception et gestion de bases de données. À travers l'étude d'un logiciel de saisie comptable et des révisions du modèle MERISE, ce support pédagogique aborde les aspects clés de l'implémentation d'un système d'information métier. Il couvre notamment :
- La modélisation des données (MCD et schéma relationnel).
- La définition et l'application des contraintes d'intégrité.
- L'élaboration de requêtes SQL complexes et d'utilitaires (triggers, procédures stockées) pour la fiabilité et la performance.
Il vise à développer une approche pratique de la conception de bases de données robustes.
Modélisation Merise : TD N°7 modélisation d’un logiciel de saisie comptable corri
Télécharger PDFModélisation d’un Logiciel de Saisie Comptable (Révisions MERISE)
Contexte
Le service informatique de la société d’assurances ASSUTOUT est actuellement chargé de développer en interne un logiciel destiné à remplacer le progiciel de comptabilité que le service comptable utilise actuellement. Les futurs utilisateurs du logiciel ont décidé de le surnommer Compta+. Ce choix stratégique a été effectué par la DSI, en accord avec la direction comptable et les autres divisions de l’entreprise, afin de pallier à terme certaines problématiques rencontrées avec le précédent progiciel.
En particulier, grâce à ce nouveau logiciel, la DSI espère pouvoir parfaitement interconnecter son SI métier au logiciel de comptabilité. Ainsi, la direction espère obtenir des gains de temps grâce à la mise en place de passerelles avec Compta+ et grâce à l’automatisation de certains traitements. Elle espère qu’il en résultera également de nombreuses économies : coût de formation, coût de maintenance, coût de développement de la part de l’éditeur, licences d’utilisation, etc.
Cependant, afin de disposer de ce logiciel dans un délai bref, il a été convenu de réaliser une application dont les fonctionnalités seront limitées. Ainsi, dans un premier temps, le logiciel permettra essentiellement la saisie et le suivi comptable. Dans un second temps, l’on envisagera de faire évoluer le logiciel et d’y adjoindre de nouvelles fonctionnalités.
Afin d’assurer cette transition, ASSUTOUT a décidé, de façon plutôt originale, d’externaliser temporairement une partie de sa comptabilité auprès d’un cabinet d’expertise comptable dès lors que Compta+ sera mis en service. En effet, ASSUTOUT sous-traitera momentanément la production des documents comptables annuels, encore appelés « liasse fiscale », du fait que Compta+ n’automatisera pas initialement la production des documents en question.
D’un point de vue technologique, l’application sera développée :
- Pour la partie serveur : à l’aide d’une couche de web services développée en Java/JEE à l’aide du framework Spring. La partie serveur comportera une couche métier, mise en œuvre grâce à l’ORM Hibernate. Cette couche métier et les web services seront interconnectés à une base de données relationnelles MySQL.
- Pour la partie client : à l’aide d’un front office AngularJS, HTML5 et CSS3.
Dans ce contexte, ASSUTOUT fait appel à un expert en base de données afin d’implémenter la base de données et les requêtes nécessaires au logiciel.
Modélisation
Modèle Conceptuel de Données (MCD)
Éléments clés à considérer pour la modélisation :
- Une contrainte d’inclusion entre les associations « Pièce de référence » et « Pièces de l’écriture »
- Une écriture comptable est relative à un exercice : identifiant relatif (1,1) ou 1,1 (R)
- Une ligne d’écriture comptable est relative à une écriture comptable.
Schéma Relationnel
Exercice(Debut, Fin)
Clef primaire : Debut
Journal(Code, Libelle)
Clef primaire : Code
Compte(Num, Libelle)
Clef primaire : Num
Piece(Num)
Clef primaire : Num
Ecriture(Exercice, Num, Journal, Piece, Creation, Libelle, Valide)
Clef primaire : Exercice, Num
Clefs étrangères :
- Exercice en référence à Exercice(Debut)
- Journal en référence à Journal(Code)
- (Exercice, Num, Piece) en référence à EcriturePiece(Exercice, Ecriture, Piece)
Notes :
- Creation représente la date de passation de l’écriture comptable
- Piece correspond à la pièce de référence de l’écriture comptable
- Valide indique si l’écriture a été validée ou si elle ne l’a toujours pas été
EcriturePiece(Exercice, Ecriture, Piece)
Clef primaire : Exercice, Ecriture, Piece
Clefs étrangères :
- (Exercice, Ecriture) en référence à Ecriture(Exercice, Num)
- Piece en référence à Piece(Num)
EcritureLigne(Exercice, Ecriture, Num, Compte, Debit, Credit)
Clef primaire : Exercice, Ecriture, Num
Clefs étrangères :
- (Exercice, Ecriture) en référence à Ecriture(Exercice, Num)
- Compte en référence à Compte(Num)
Identification des Contraintes d'Intégrité
La société ASSUTOUT souhaite disposer d’un logiciel fiable et robuste, dont les données doivent rester intègres.
Sens de la Contrainte d'Inclusion
La contrainte d’inclusion figurant entre les associations « Pièce de référence » et « Pièces de l’écriture » signifie que, pour qu’une pièce soit « pièce de référence », il faut déjà qu’elle fasse partie des pièces de l’écriture. Autrement dit : être pièce de référence d’une écriture implique être une pièce de l’écriture.
Vérification de la Contrainte d'Inclusion
On pourrait mettre en place un trigger qui, lors de l’insertion ou de la modification d’une écriture, vérifierait que la pièce de référence spécifiée (triplet Ecriture.Exercice, Ecriture.Num, Ecriture.Piece) figure bien dans la table EcriturePiece.
Cependant, une simple clef étrangère suffit ici à valider la contrainte d’inclusion. En effet, la clef étrangère assure que la pièce de référence fasse bien référence à une pièce de EcriturePiece. En dehors de la mise en place de cette clef étrangère, aucune autre opération n'est nécessaire pour cette contrainte.
Autres Contraintes d'Intégrité non Modélisées
En ce qui concerne les écritures et leurs lignes :
- Les champs EcritureLigne(Debit) et EcritureLigne(Credit) doivent être positifs ou nuls ;
- Une fois une écriture validée, ses lignes ne sont plus modifiables (elles deviennent « stables ») ;
- La date de passation de l’écriture, à savoir la valeur du champ Ecriture(Creation), doit être comprise entre les dates de début et de fin de l’exercice auquel l’écriture est attachée.
En ce qui concerne les exercices, ils se suivent, et on a les contraintes suivantes :
- Date de début d’exercice <= Date de fin d’exercice ;
- Pas de chevauchement : les dates de début et de fin d’un exercice ne peuvent être comprises entre les dates de début et de fin d’un autre exercice.
Opérations de Vérification des Contraintes
- Vérifier que Debit >= 0 et Crédit >= 0 grâce à deux contraintes de domaine sur EcritureLigne :
CHECK Debit >= 0etCHECK Credit >= 0. - Les lignes d’une écriture peuvent être modifiées à l’occasion d’un INSERT, d’un UPDATE ou d’un DELETE sur table EcritureLigne. À ces moments, grâce à un trigger, il convient de rejeter l’insertion, la modification ou la suppression si la condition Ecriture.Valide = TRUE est vérifiée pour l’écriture à laquelle la ligne d’écriture est associée.
- Grâce à un trigger, lors de l’insertion ou de la modification d’une écriture, il convient de vérifier que la date de l’écriture soit comprise entre les dates de début et de fin de l’exercice auquel elle est attachée. Si la vérification échoue, on rejette l’insertion ou la modification.
- Il convient d’ajouter la contrainte de domaine
CHECK Debut <= Finsur la table Exercice. - Il convient de mettre un trigger en place lors de l’insertion et la modification d’un exercice : l’insertion ou la modification de l’exercice doit échouer s’il existe un exercice E tel que :
NEW.Debut BETWEEN E.Debut AND E.FIN OR NEW.FIN BETWEEN E.Debut AND E.FIN.
Requêtage des Données Comptables
Afin de faciliter la conception des web services du logiciel, plusieurs requêtes SQL ont été rédigées.
Liste des Écritures du Mois en Cours
SELECT Ecriture.*, EcritureLigne.*, Compte.Libelle
FROM Ecriture E
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
INNER JOIN Compte C ON L.Compte = C.Num
INNER JOIN EcriturePiece EP ON E.Exercice = EP.Exercice AND E.Num = EP.Ecriture AND E.Piece = EP.Piece
INNER JOIN Piece P ON EP.Piece = P.Num
WHERE MONTH(E.Creation) = MONTH(CURDATE())
AND YEAR (E.Creation) = YEAR(CURDATE());
Liste des Écritures de l'Exercice en Cours
SELECT Ecriture.*, EcritureLigne.*, Compte.Libelle
FROM Exercice EX
INNER JOIN Ecriture E ON EX.Debut = E.Exercice
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
INNER JOIN Compte C ON L.Compte = C.Num
INNER JOIN EcriturePiece EP ON E.Exercice = EP.Exercice AND E.Num = EP.Ecriture AND E.Piece = EP.Piece
INNER JOIN Piece P ON EP.Piece = P.Num
WHERE CURDATE() BETWEEN EX.Debut AND EX.Fin;
Création de Vues pour les Écritures
CREATE VIEW ecritures_mois AS
SELECT Ecriture.*, EcritureLigne.*, Compte.Libelle
FROM Ecriture E
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
INNER JOIN Compte C ON L.Compte = C.Num
INNER JOIN EcriturePiece EP ON E.Exercice = EP.Exercice AND E.Num = EP.Ecriture AND E.Piece = EP.Piece
INNER JOIN Piece P ON EP.Piece = P.Num
WHERE MONTH(E.Creation) = MONTH(CURDATE())
AND YEAR (E.Creation) = YEAR(CURDATE());
CREATE VIEW ecritures_exercice AS
SELECT Ecriture.*, EcritureLigne.*, Compte.Libelle
FROM Exercice EX
INNER JOIN Ecriture E ON EX.Debut = E.Exercice
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
INNER JOIN Compte C ON L.Compte = C.Num
INNER JOIN EcriturePiece EP ON E.Exercice = EP.Exercice AND E.Num = EP.Ecriture AND E.Piece = EP.Piece
INNER JOIN Piece P ON EP.Piece = P.Num
WHERE CURDATE() BETWEEN EX.Debut AND EX.Fin;
Écritures Comptables du Journal "BANQ"
SELECT Ecriture.*, EcritureLigne.*, Compte.Libelle
FROM Exercice EX
INNER JOIN Ecriture E ON EX.Debut = E.Exercice
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
INNER JOIN Compte C ON L.Compte = C.Num
INNER JOIN EcriturePiece EP ON E.Exercice = EP.Exercice AND E.Num = EP.Ecriture AND E.Piece = EP.Piece
INNER JOIN Piece P ON EP.Piece = P.Num
WHERE CURDATE() BETWEEN EX.Debut AND EX.Fin
AND E.Journal = 'BANQ';
Une écriture comptable est dite « équilibrée » si la somme de ses débits est égale à la somme de ses crédits.
Somme des Débits et Crédits par Écriture
SELECT E.Num, E.Libelle, SUM(L.Debit) AS 'Débits', SUM(L.Credit) AS 'Crédits'
FROM Exercice EX
INNER JOIN Ecriture E ON EX.Debut = E.Exercice
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
WHERE CURDATE() BETWEEN EX.Debut AND EX.Fin
GROUP BY E.Num, E.Libelle;
Écritures avec Indicateur d'Équilibre
SELECT
E.Num, E.Libelle,
SUM(L.Debit) AS 'Débits', SUM(L.Credit) AS 'Crédits',
IF(SUM(L.Debit) = SUM(L.Credit), 'Équilibré', 'Non équilibré') AS 'Statut Équilibre'
FROM Exercice EX
INNER JOIN Ecriture E ON EX.Debut = E.Exercice
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
WHERE CURDATE() BETWEEN EX.Debut AND EX.Fin
GROUP BY E.Num, E.Libelle;
Somme des Débits et Crédits par Exercice
SELECT EX.Debut, EX.Fin, SUM(L.Debit) AS 'Débits', SUM(L.Credit) AS 'Crédits'
FROM Exercice EX
INNER JOIN Ecriture E ON EX.Debut = E.Exercice
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
GROUP BY EX.Debut, EX.Fin;
En comptabilité :
- Résultat = Produits - Charges
- Les produits sont répertoriés dans les comptes de classe 7, c’est-à-dire les comptes commençant par la « lettre » 7. Les charges sont répertoriées quant à elles dans ceux commençant par la « lettre » 6.
- Produits = Crédits comptes 7... - Débits comptes 7... (on dit que les produits augmentent au crédit)
- Charges = Débits compte 6... - Crédits comptes 6... (on dit que les charges augmentent au débit).
Calcul du Résultat de l'Exercice en Cours
SELECT P.Exercice, P.Produits, C.Charges, P.Produits - C.Charges AS 'Résultat'
FROM (
SELECT L.Exercice AS 'Exercice', SUM(L.Credit) - SUM(L.Debit) AS 'Produits'
FROM EcritureLigne L
WHERE L.Compte LIKE '7%'
GROUP BY L.Exercice
) P INNER JOIN (
SELECT L.Exercice AS 'Exercice', SUM(L.Debit) - SUM(L.Credit) AS 'Charges'
FROM EcritureLigne L
WHERE L.Compte LIKE '6%'
GROUP BY L.Exercice
) C ON P.Exercice = C.Exercice
INNER JOIN Exercice E ON C.Exercice = E.Debut
WHERE CURDATE() BETWEEN E.Debut AND E.Fin;
Contrôle de l'Intégrité des Données
Trigger pour la Contrainte d'Inclusion (Optionnel)
Aucun trigger n’est strictement nécessaire pour vérifier cette contrainte d’inclusion si une clef étrangère est correctement mise en place, car celle-ci assure déjà que la pièce de référence d'une écriture existe bien dans la table des pièces associées à cette écriture.
Triggers pour les Contraintes Spécifiques
a) Vérifier que Debit >= 0 et Crédit >= 0 :
ALTER TABLE EcritureLigne ADD CONSTRAINT dc_positif CHECK (Debit >= 0 AND Credit >= 0);
b) Empêcher la modification des lignes d'une écriture validée :
DELIMITER $$
CREATE TRIGGER ecriture_stable BEFORE UPDATE ON Ecriture
FOR EACH ROW
BEGIN
IF OLD.Valide = TRUE AND NEW.Valide = FALSE THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Impossible de dévalider une écriture déjà validée.';
END IF;
END $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER ecriture_ligne_stable_insert BEFORE INSERT ON EcritureLigne
FOR EACH ROW
BEGIN
DECLARE valide_ecriture BOOLEAN;
SELECT Valide INTO valide_ecriture
FROM Ecriture
WHERE Exercice = NEW.Exercice AND Num = NEW.Ecriture;
IF valide_ecriture THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Écriture validée ! Insertion de ligne interdite !';
END IF;
END $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER ecriture_ligne_stable_update BEFORE UPDATE ON EcritureLigne
FOR EACH ROW
BEGIN
DECLARE valide_ecriture BOOLEAN;
SELECT Valide INTO valide_ecriture
FROM Ecriture
WHERE Exercice = NEW.Exercice AND Num = NEW.Ecriture;
IF valide_ecriture THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Écriture validée ! Modification de ligne interdite !';
END IF;
END $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER ecriture_ligne_stable_delete BEFORE DELETE ON EcritureLigne
FOR EACH ROW
BEGIN
DECLARE valide_ecriture BOOLEAN;
SELECT Valide INTO valide_ecriture
FROM Ecriture
WHERE Exercice = OLD.Exercice AND Num = OLD.Ecriture;
IF valide_ecriture THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Écriture validée ! Suppression de ligne interdite !';
END IF;
END $$
DELIMITER ;
c) Vérifier que la date de l'écriture est comprise dans l'exercice :
DELIMITER $$
CREATE TRIGGER verif_date_ecriture BEFORE INSERT ON Ecriture
FOR EACH ROW
BEGIN
DECLARE dateDebutExercice DATE;
DECLARE dateFinExercice DATE;
SELECT Debut, Fin INTO dateDebutExercice, dateFinExercice
FROM Exercice
WHERE Debut = NEW.Exercice;
IF NEW.Creation < dateDebutExercice OR NEW.Creation > dateFinExercice THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Date d\'écriture invalide !';
END IF;
END $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER verif_date_ecriture_update BEFORE UPDATE ON Ecriture
FOR EACH ROW
BEGIN
DECLARE dateDebutExercice DATE;
DECLARE dateFinExercice DATE;
SELECT Debut, Fin INTO dateDebutExercice, dateFinExercice
FROM Exercice
WHERE Debut = NEW.Exercice;
IF NEW.Creation < dateDebutExercice OR NEW.Creation > dateFinExercice THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Date d\'écriture invalide !';
END IF;
END $$
DELIMITER ;
d) Ajouter la contrainte de domaine sur l'exercice :
ALTER TABLE Exercice ADD CHECK (Debut <= Fin);
e) Empêcher le chevauchement des exercices :
DELIMITER $$
CREATE TRIGGER verif_chevauchement BEFORE INSERT ON Exercice
FOR EACH ROW
BEGIN
DECLARE nb_chevauchements INT;
SELECT COUNT(*) INTO nb_chevauchements
FROM Exercice E
WHERE (NEW.Debut BETWEEN E.Debut AND E.Fin) OR (NEW.Fin BETWEEN E.Debut AND E.Fin);
IF nb_chevauchements > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Chevauchement ! Dates de l\'exercice invalides !';
END IF;
END $$
DELIMITER ;
DELIMITER $$
CREATE TRIGGER verif_chevauchement_update BEFORE UPDATE ON Exercice
FOR EACH ROW
BEGIN
DECLARE nb_chevauchements INT;
SELECT COUNT(*) INTO nb_chevauchements
FROM Exercice E
WHERE ((NEW.Debut BETWEEN E.Debut AND E.Fin) OR (NEW.Fin BETWEEN E.Debut AND E.Fin))
AND E.Debut <> OLD.Debut;
IF nb_chevauchements > 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Chevauchement ! Dates de l\'exercice invalides !';
END IF;
END $$
DELIMITER ;
Trigger pour l'Équilibre des Écritures lors de la Validation
DELIMITER $$
CREATE TRIGGER valid_equilibre BEFORE UPDATE ON Ecriture
FOR EACH ROW
BEGIN
IF NEW.Valide = 1 AND OLD.Valide = 0 THEN
DECLARE totalDebits DECIMAL(10,2);
DECLARE totalCredits DECIMAL(10,2);
SELECT SUM(Debit), SUM(Credit) INTO totalDebits, totalCredits
FROM EcritureLigne
WHERE Exercice = NEW.Exercice AND Ecriture = NEW.Num;
IF totalDebits <> totalCredits THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Écriture non équilibrée ! Échec validation !';
END IF;
END IF;
END $$
DELIMITER ;
Procédures Stockées (Utilitaires)
Pour faciliter la conception des web services de l'application, les procédures stockées suivantes sont proposées.
Procédure "ecritures_journal"
DELIMITER $$
CREATE PROCEDURE ecritures_journal(IN unCode VARCHAR(255))
BEGIN
SELECT Ecriture.*, EcritureLigne.*, Compte.Libelle
FROM Exercice EX
INNER JOIN Ecriture E ON EX.Debut = E.Exercice
INNER JOIN EcritureLigne L ON E.Exercice = L.Exercice AND E.Num = L.Ecriture
INNER JOIN Compte C ON L.Compte = C.Num
INNER JOIN EcriturePiece EP ON E.Exercice = EP.Exercice AND E.Num = EP.Ecriture AND E.Piece = EP.Piece
INNER JOIN Piece P ON EP.Piece = P.Num
WHERE CURDATE() BETWEEN EX.Debut AND EX.Fin
AND E.Journal = unCode;
END $$
DELIMITER ;
Procédure "exercice_resultat"
DELIMITER $$
CREATE PROCEDURE exercice_resultat(OUT resultat DECIMAL(10,2))
BEGIN
SELECT P.Produits - C.Charges INTO resultat
FROM (
SELECT L.Exercice AS 'Exercice', SUM(L.Credit) - SUM(L.Debit) AS 'Produits'
FROM EcritureLigne L
WHERE L.Compte LIKE '7%'
GROUP BY L.Exercice
) P INNER JOIN (
SELECT L.Exercice AS 'Exercice', SUM(L.Debit) - SUM(L.Credit) AS 'Charges'
FROM EcritureLigne L
WHERE L.Compte LIKE '6%'
GROUP BY L.Exercice
) C ON P.Exercice = C.Exercice
INNER JOIN Exercice E ON C.Exercice = E.Debut
WHERE CURDATE() BETWEEN E.Debut AND E.Fin;
END $$
DELIMITER ;
Concepts de la Saisie Comptable
Principes Fondamentaux
Voici une explication de la logique comptable et plus particulièrement de la saisie comptable :
- Une comptabilité porte sur un exercice comptable ayant une date de début et une date de fin.
- Les exercices comptables se suivent. Par exemple, un exercice comptable peut aller du 01/01/2017 au 31/12/2017, l’exercice suivant du 01/01/2018 au 31/12/2018, et ainsi de suite.
- Le métier de comptable consiste à saisir des écritures comptables. Une écriture comptable est toujours datée et on lui attribue un libellé.
- Chaque écriture est reliée à un exercice.
- La comptabilité est divisée en journaux. Un journal a un code (exemple : « BANQ », « PAIE », « EXTO ») et un libellé, ce qui permet de classer les écritures. Chaque écriture est rattachée à un journal.
- On peut attacher des pièces comptables (documents justificatifs) à une écriture. Parmi ces pièces, l’écriture se verra attribuer une pièce de référence.
- Une écriture est constituée de lignes. Chaque ligne de l’écriture peut avoir un montant au débit ou au crédit.
FAQ (Foire Aux Questions)
Qu'est-ce que le logiciel Compta+ et quel est son objectif principal ?
Compta+ est un nouveau logiciel de comptabilité développé en interne par ASSUTOUT, une société d'assurances. Son objectif est de remplacer un ancien progiciel, d'améliorer l'interconnexion avec le système d'information métier d'ASSUTOUT, de gagner du temps grâce à l'automatisation et de réduire les coûts liés à la formation, maintenance et licences.
Quelles sont les principales contraintes d'intégrité à respecter pour les données comptables ?
Les principales contraintes incluent l'équilibre des écritures (somme des débits égale à la somme des crédits), la non-modification des lignes d'écritures une fois validées, la validité des dates d'écriture (doivent être comprises dans l'exercice), et l'absence de chevauchement entre les dates des exercices comptables.
Comment le résultat de l'exercice comptable est-il calculé ?
Le résultat est calculé comme la différence entre les produits et les charges. Les produits sont déterminés à partir des comptes de classe 7 (crédits - débits), et les charges à partir des comptes de classe 6 (débits - crédits), le tout pour l'exercice en cours.