~~SLIDESHOW~~ ====== 1) Utiliser des champs calculés ====== Bien que les champs calculés constituent une forme de redondance de données, il est parfois commode d’en utiliser afin d’optimiser les performances de certaines requêtes. En effet, l’usage de champs calculés peut servir aux fins par exemple d’éviter des jointures. ===== Exemple : ===== On imagine une base de données de gestion de commandes, afin de faciliter les calculs concernant les ventes réalisées, on souhaite : - Ajouter les attributs totalHTLigne, totalTVALigne et totalTTCLigne à l’entité ligneCommande. Ces propriétés doivent être calculées. - Ajouter les attribut montantHT, montantTVA et montantTTC à l’entité Commande. Ces propriétés doivent être calculées automatiquement. **Quelle solution possible ?** - A l’insertion ainsi qu’à la modification d'une ligne de commande, calculer ou recalculer automatiquement les champs totalHTLigne , totalTVALigne et totalTTCLigne de la ligne de commande; - A l’insertion ainsi qu’à la modification et à la suppression d'une ligne de commande, calculer ou recalculer automatiquement les champs MontantHT, MontantTVA et MontantTTC de la commande. [[https://drive.google.com/file/d/1w8lGRdPTS7Fz8xx2cV6XRS0fyu5imazi/view?usp=drive_link|script de création de la bd]] **Trigger1 : Quand se déclenche t'il ? Que fait-il ? Testez-le. Contrôlez son exécution** DROP TRIGGER IF EXISTS calculLCBI; DELIMITER $$ CREATE TRIGGER calculLCBI BEFORE INSERT ON ligneCommande FOR EACH ROW BEGIN -- à compléter SELECT prixHT, tauxTVA INTO @prix, @taux FROM produit WHERE idproduit = NEW.idproduit ; -- à compléter SET NEW.totalHTLigne = @prix * NEW.quantite ; SET NEW.totalTVALigne= NEW.totalHTLigne* @taux/100 ; SET NEW.totalTTCLigne= NEW.totalHTLigne + NEW.totalTVALigne; END$$ DELIMITER ; A vous de jouer : Ecrire et tester les autres déclencheurs ====== 2) Vérifier une contrainte d'entités ====== Les mécanismes d’héritage ou de spécialisation peuvent être modélisés. Pour autant, si aucun traitement spécifique n’est fait nous ne sommes pas capables de nous assurer qu’une telle contrainte soit vérifiée. ===== Exemple : ===== Dans notre gestion commerciale, un produit est soit un produit alimentaire - et on veut connaître sa date limite de consommation, soit un produit électroménager - et on veut connaitre sa classe énergétique. Si aucun traitement spécifique n’est fait : * Rien n’empêche pourtant qu’on ait un produit ne figurant ni parmi les produits alimentaires, ni parmi les produits électroménagers. * Rien n’empêche non plus qu’on ait un produit figurant tout à la fois parmi les produits alimentaires et les produits électroménagers ==== Quelles solutions possibles ? (dépend des tables créés) ==== === 1ère solution : 1 table = utiliser les contraintes de domaine === -- Table des produits CREATE TABLE produit ( idProduit INT AUTO_INCREMENT PRIMARY KEY, nomProduit VARCHAR(255) NOT NULL, prixHt DECIMAL(10, 2), -- Prix hors taxe tauxTva DECIMAL(5, 2), -- Taux de TVA en pourcentage typeProduit ENUM('alimentaire', 'electromenager') NOT NULL, -- Type de produit dateLimiteConsommation DATE, -- Date limite pour produits alimentaires classeEnergetique VARCHAR(5), -- Classe énergétique pour électroménager CHECK ( (typeProduit = 'alimentaire' AND dateLimiteConsommation IS NOT NULL AND classeEnergetique IS NULL) OR (typeProduit = 'electromenager' AND classeEnergetique IS NOT NULL AND dateLimiteConsommation IS NULL) ) -- Vérification pour garantir la cohérence des types ); A vous de jouer - réimporter le script de création de la bd de gestion des commandes en changeant le nom de la base de données(gestionCommandeExo2) et changer la table produit pour la remplacer avec celle ci-dessus - tester les contraintes : quels tests faites-vous ? - analyser les messages d'erreurs, comment les rendre plus clairs ? === 2ème solution : 3 tables = gérer les relations d'héritage, avec la spécialisation === * 1 Table de base (produit) : Contient les attributs communs à tous les produits, comme le nom, le prix hors taxe et le taux de TVA. * 2 Tables spécialisées (alimentaire, electromenager) : Contiennent les attributs spécifiques à chaque type de produit avec une clé étrangère (idProduit) pointant vers la table produit. * Contrainte d'intégrité : On utilise des clés étrangères dans les tables spécialisées pour faire référence à la table principale produit, garantissant qu'un produit dans une table spécialisée existe toujours dans la table de base. A vous de jouer - proposer le schéma relationnel des 3 tables - réimporter le script de création de la bd de gestion des commandes en changeant le nom de la base de données(gestionCommandeExo3) et changer la table produit pour la remplacer par les 3 tables - tester les contraintes : quels tests faites-vous ? - analyser les messages d'erreurs, comment les rendre plus clairs ? **Explication des contraintes d'intégrité :** * __Intégrité référentielle__ : Chaque produit alimentaire ou électroménager doit avoir une entrée correspondante dans la table produit. Cette intégrité est garantie par les clés étrangères (idProduit) dans les tables alimentaire et electromenager, qui pointent vers la table produit. * __Clé primaire partagée__ : Les tables spécialisées alimentaire et electromenager partagent la même clé primaire que la table produit, ce qui garantit qu'un produit ne peut être à la fois dans alimentaire et electromenager (grâce à l'unicité de la clé primaire idProduit). * __Suppression en cascade__ : Lorsqu'un produit est supprimé de la table produit, toutes les entrées associées dans les tables alimentaire et electromenager seront également supprimées automatiquement grâce à l'option ON DELETE CASCADE. Conclusion : ici besoin de déclencheur ! A vous de jouer : testez chacune des 2 solutions CONSEIL : Travailler sur des bases de données différentes pour réaliser vos tests ====== 3) Vérifier une contrainte d’associations ====== ===== Trigger pour vérifier une contrainte d'inclusion ===== On ajoute à notre gestion commerciale la gestion des livraisons des commandes : un produit ne doit pouvoir être livré que si il a été commandé et sa quantité livrée ne doit pas dépasser la quantité commandée bien sûr. On simplifie le cas en autorisant les livraisons des produits en une seule fois. ===== Quels problèmes ? ===== * Rien ne nous assure que les livraisons portent sur des produits commandés ; * Rien ne nous assure que les quantités livrées ne soient pas supérieures à celles commandées. ===== Quelle solution possible ? ===== - A l’insertion comme à la modification d’une livraison, on vérifie à la livraison que le produit livré figure bien dans la commande qui concerne la livraison; - Toujours à l’insertion et à la modification d’une livraison, on vérifie que la quantité totale livrée du produit qu’on souhaite livrer soit au plus égal à la quantité commandée concernant ce même produit. Le 1er cas est réalisable avec des contraintes d'intégrité référentielle. Comment ? Pour le 2nd cas il faudra des triggers. A vous de jouer : - Proposer une évolution de la base de données pour gérer les livraisons - Modifier la base de données - Créer et tester le trigger SQL qui va comparer la quantité livrée à la quantité commandée. ====== Autres exemples ====== ===== Trigger pour vérifier une contrainte d’exclusion (gestion de classes) ===== {{:promo_2025:slam:contraintesexclusion.png?800|}} Le couple (Année scolaire, Classe) constitue une promotion. C’est un agrégat. Une telle promotion dispose de plusieurs professeurs et élèves. En l’espèce, la contrainte d’exclusion figurant ci-dessus traduit une règle de gestion des plus simples : pour une classe et une année scolaire données, l’on ne peut être à la fois professeur et élève. ==== Quels problèmes ? ==== En absence de traitement spécifique, rien ne nous assure qu’un professeur d’une promotion ne participe pas également à la relation élève et réciproquement. ==== Quelle solution possible ? ==== Lorsque l’on ajoute un professeur, il convient qu’il ne figure pas parmi les élèves. Inversement, lorsque l’on ajoute un élève, il convient qu’il ne soit pas professeur. S’il participe aux deux relations il importe d’interdire l’ajout, à savoir l’insertion, voire la modification également. ==== Quelle implémentation ? ==== **1er trigger : aucun professeur ne peut être élève.** DROP TRIGGER IF EXISTS check_exclusion_prof ; DELIMITER $$ CREATE TRIGGER check_exclusion_prof BEFORE INSERT, UPDATE ON Professeur FOR EACH ROW BEGIN – DECLARE QTE INT ; -- si le professeur à ajouter/modifier existe en tant qu’élève, c’est mal ! IF ( SELECT COUNT(*) FROM Eleve E WHERE E.NumPersonne = NEW.NumPersonne ) > 0 THEN SIGNAL sqlstate '45000' SET message_text = 'Un professeur d’une promo ne saurait en être un élève' ; END IF ; END $$ DELIMITER ; **2ème trigger : aucun élève ne peut être professeur. ** L’on crée le trigger check_exclusion_eleve dont la principale différence avec le précédent trigger est la requête SQL, laquelle devient : SELECT COUNT(*) FROM Professeur P WHERE P.NumPersonne = NEW.NumPersonne ====== 4) Historiser des données ====== L’historisation consiste à conserver, au travers du temps, l’historique des différentes modifications effectuées sur tout ou partie des champs d’une entité. __Exemple__ : historisation de l’état d’une tâche. {{:promo_2025:slam:historisation.png?800|}} ===== Quelle solution possible ? ===== A l’insertion ainsi qu’à la modification d’une tâche, on historise son état, à savoir qu’on crée une ligne d’historique. ===== Quelle implémentation ? ===== DROP TRIGGER IF EXISTS historise_tache ; DELIMITER $$ CREATE TRIGGER historise_tache BEFORE INSERT, UPDATE ON Tache FOR EACH ROW BEGIN -- crée une nouvelle ligne d’historique INSERT INTO Historique (NumTache, Num, Creation, NumEtat) VALUES ( NEW.Num, SELECT COUNT(*) + 1 FROM Historique WHERE NumTache = NEW.NUM, NOW(), NEW.NumEtat ) ; END $$ DELIMITER ;