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 :

  1. Ajouter les attributs totalHTLigne, totalTVALigne et totalTTCLigne à l’entité ligneCommande. Ces propriétés doivent être calculées.
  2. Ajouter les attribut montantHT, montantTVA et montantTTC à l’entité Commande. Ces propriétés doivent être calculées automatiquement.

Quelle solution possible ?

  1. 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;
  2. 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.

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

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

  1. 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
  2. tester les contraintes : quels tests faites-vous ?
  3. 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

  1. proposer le schéma relationnel des 3 tables
  2. 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
  3. tester les contraintes : quels tests faites-vous ?
  4. 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.

A vous de jouer : testez chacune des 2 solutions

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 ?

  1. 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;
  2. 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 :

  1. Proposer une évolution de la base de données pour gérer les livraisons
  2. Modifier la base de données
  3. Créer et tester le trigger SQL qui va comparer la quantité livrée à la quantité commandée.

Trigger pour vérifier une contrainte d’exclusion (gestion de classes)

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

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.

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 ;