REPRÉSENTATION UML

REPRÉSENTATION UML

On part du modèle entité-association final, celui auquel on a ajouté l’entité « tarification ». La partie d’UML qui est utilisée est le diagramme de classe ; celui-ci comprend le nom de la classe, la description des attributs et les méthodes associées à la classe. Dans ce cas, on n’utilise pas la partie objet et donc il n’y a pas de méthodes associées aux classes. Une entité est représentée par un diagramme de classe. Avec UML, il est possible d’intégrer les notions de contraintes d’intégrité au niveau du schéma. On peut par exemple spécifier que l’attribut ‘retard’ de l’entité ‘commande’ ne pourra prendre comme valeurs que « O » et « N ». Les associations utilisées dans ce message n’ont pas d’attributs. Une différence majeure est que les cardinalités seront positionnées de manière inversée par rapport au modèle entité-association (voir figure 5.4). Figure 5.4 Modèle UML ‘Livraisons de pizzas’ avec cardinalités.

CALCULS PAR EXPRESSION SQL

Pour calculer le nombre de pizzas livrées en retard, il faut se souvenir d’un point important que l’on a fixé comme « axiome » lors de l’élaboration du modèle : une pizza est associée à une commande et une seule. Une commande ne contient qu’une seule pizza. C’est important : si ce n’était pas le cas, la requête serait beaucoup plus complexe. Représentez le modèle entité-association en utilisant le formalisme UML. Combien de pizzas ont-elles été livrées en retard ? Quelle est la perte occasionnée par ces retards ? Commande Livreur Pizza Véhicule Ingrédient Client # NumCommande DateCom Retard # NumClient NomClient Adresse Compte PointsRapizz # NomPizza Prix # CodeLivreur NomLivreur Téléphone # NumIngre NomIngre # NumImmat Marque Type Livre Transporte Passe Compose 1..1 1..n Tarification #Taille Coefficient Utilise 0..n 1..1 1..1 1..n 0..n 0..n 1..1 1..1 1..n 1..n Constitue Du langage parlé à SQL 149 Exercices 5 Chapitre Le type de question qui inclut le mot « combien » suggère que l’on va effectuer un comptage sur la table. Il nous suffit de compter le nombre d’enregistrements dont le champ retard est positionné à « Oui ». On a vu lors de la définition de la table ‘commande’ que le contenu du champ retard a été normalisé et restreint par une contrainte d’intégrité aux valeurs « N » et « O ». Ces réflexions conduisent à la simple requête suivante : SELECT COUNT(*) AS NombreRetard FROM commande WHERE retard=‘O’; On compte le nombre de lignes de la table commande dont le champ commande est égal à « O ». On ne spécifie pas de nom de champ dans la fonction « COUNT » de SQL, car il s’agit d’un calcul indépendant d’un champ donné. Pour connaître la perte occasionnée, on calcule la somme du prix de chaque commande. Le modèle a été modifié pour pouvoir calculer directement cette information. On a besoin du prix de base de la pizza qui se trouve dans la table ‘pizza’ et du coefficient qui se trouve dans la table ‘tarification’. On effectue une jointure sur ces trois tables. SELECT SUM(pizza.prix*tarification.coefficient) AS PerteRetard FROM commande JOIN pizza JOIN tarification ON commande.NomPizza=pizza.NomPizza AND commande.Taille=tarification.Taille WHERE commande.retard=‘O’; La fonction SQL SUM a besoin du contenu précis du champ concerné par le calcul. Ici, il s’agit d’une expression constituée à partir du prix et du coefficient. Il est préférable de préfixer les noms de champ par le nom de la table d’où ils proviennent pour éviter les ambiguïtés et faciliter la lecture ultérieure de la requête. Les ambiguïtés sont signalées par le SGBD qui refuse d’exécuter la requête. On aurait pu écrire les jointures en utilisant classiquement un produit cartésien et une sélection. Dans ce cas, les expressions qui servent à la jointure sont mélangées à celles qui servent à faire la sélection dans la clause WHERE. De plus, la requête est effectuée de manière beaucoup moins efficace par le SGBD ; cela est nettement perceptible lorsque l’on dispose de tables de taille importante. SELECT SUM(pizza.prix*tarification.coefficient) AS PerteRetard FROM commande, pizza, tarification WHERE commande.NomPizza=pizza.NomPizza AND commande.Taille=tarification.Taille AND commande.retard=‘O’; Les deux tables « résultat » sont des tables qui possèdent une seule ligne et une seule colonne. On peut utiliser ces valeurs par exemple pour faire une comparaison (voir l’un des exercices suivants) en les associant par un produit cartésien à la table sur laquelle on veut effectuer la comparaison.

CODE SQL ET SIGNIFICATION

Cette requête SQL donne-t-elle un résultat ? Si oui, que signifie-t-il ? SELECT client.NomClient, livreur.NomLivreur FROM livreur JOIN client ON livreur.CodeLivreur=client.NumClient ; Imaginez à quel type de question a voulu répondre la personne qui a fait cette requête. 150 Création de bases de données La requête retourne un résultat, car la syntaxe est correcte et le type des champs sur lesquels on a effectué la jointure sont de type compatible et contiennent des valeurs communes. Bien évidemment, le résultat n’a aucun sens d’un point de vue la réalité. Le schéma entité-association montre que les liens entre les tables ‘client’ et ‘livreur’ passent par la table ‘commande’ et les associations ‘livre’ et ‘passe’. Il s’agit du cas typique qui illustre le fait qu’une requête donne toujours un résultat, même s’il n’a aucun sens. En aucun cas, cette requête ne permettrait d’effectuer des recherches de corrélation entre le livreur et le client. Si l’on cherche à afficher le nom du client et le nom du livreur correspondant par commande, on doit utiliser la table ‘commande’ même si l’on ne projette aucun champ de la table ‘commande’. Pour faciliter la lecture et repérer d’éventuelles corrélations, on ordonne par noms de clients. SELECT client.NomClient, livreur.NomLivreur FROM livreur JOIN commande JOIN client ON livreur.CodeLivreur=commande.CodeLivreur AND client.NumClient= commande.NumClient ORDER BY client.NomClient; Pour savoir quels clients sont toujours livrés par le même livreur, la démarche serait plus complexe. 

AGRÉGATS ET SÉLECTION

La méthode conseillée dans ce chapitre est d’aborder la question en décomposant le problème. Pour calculer le chiffre d’affaires, il faut d’abord regrouper les commandes par pizza et ensuite effectuer le calcul. La notion de regroupement suggère l’emploi des agrégats. Le nom de la pizza est directement accessible dans la table ‘commande’. Pour vérifier combien de commandes ont été passées par pizza, on les compte. SELECT commande.NomPizza, COUNT(*) AS NombreCommande FROM commande GROUP BY commande.NomPizza; Pour calculer le prix, on a besoin du prix de base qui se trouve dans la table ‘pizza’ et du coefficient qui se trouve dans la table ‘tarification’. SELECT commande.NomPizza, COUNT(*) AS NombreCommande, SUM(pizza.prix*tarification.coefficient) AS TotalCommande FROM commande JOIN pizza JOIN tarification ON commande.Taille=tarification.Taille AND commande.NomPizza=pizza.NomPizza GROUP BY commande.NomPizza ORDER BY TotalCommande; On trie le résultat en utilisant le champ calculé ‘TotalCommande’. L’opération de tri se fait donc après les opérations de jointures, d’agrégation et de calculs. Il est possible de réaliser une sélection sur le résultat final de cette opération en ne considérant que les pizzas dont le chiffre d’affaires dépasse le nombre « 200 ». On n’emploie pas dans ce cas le mot clé WHERE qui sert à réaliser les sélections sur une table standard. On utilise le mot clé HAVING qui fait une sélection sur le résultat des calculs sur les agrégats. Cette sélection se fait, de même que le tri, à la fin de l’opération sur la table « résultat » finale. SELECT commande.NomPizza, COUNT(*) AS NombreCommande, SUM(pizza.prix*tarification.coefficient) AS TotalCommande Donnez le chiffre d’affaires par pizza vendue. On ne tient pas compte à ce niveau des pizzas gratuites obtenues grâce aux points de fidélité ou en raison d’un retard de livraison. Du langage parlé à SQL 151 Exercices 5 Chapitre FROM commande JOIN pizza JOIN tarification ON commande.Taille=tarification.Taille AND commande.NomPizza=pizza.NomPizza GROUP BY commande.NomPizza HAVING TotalCommande > 200 ORDER BY TotalCommande; En revanche, si l’on avait voulu éliminer du résultat les pizzas dont le prix de vente n’est pas assez élevé considérant que cela fausse le résultat, le mode de sélection ne serait pas le même. Il faudrait réaliser une sélection sur l’ensemble de départ avant d’effectuer les agrégats et les calculs ; dans ce cas, on utiliserait le mot clé WHERE. SELECT commande.NomPizza, COUNT(*) AS NombreCommande, SUM(pizza.prix*tarification.coefficient) AS TotalCommande FROM commande JOIN pizza JOIN tarification ON commande.Taille=tarification.Taille AND commande.NomPizza=pizza.NomPizza WHERE pizza.prix > 10 GROUP BY commande.NomPizza ORDER BY TotalCommande; Dans le premier cas, la sélection est faite a fortiori ; dans le second, a priori.