Concevoir, depuis un cahier des charges, la base de données Oracle du magasin de fleurs « Le Jardin de Charlotte » : du modèle conceptuel jusqu'aux requêtes d'exploitation.
Une fleuriste lyonnaise vend des bouquets sur commande. Le client décrit la composition de chaque bouquet qu'il veut (4 roses, 3 tulipes…), le nombre d'exemplaires souhaité, et choisit entre un retrait en magasin et une livraison chez un destinataire qui n'est pas forcément lui. Le montant dépend du prix de vente des fleurs et du tarif de livraison de la commune. Une commande non réglée reste en attente et n'est jamais livrée.
Chaque matin, la fleuriste achète chez des grossistes les fleurs nécessaires aux commandes du jour, et relève leur prix d'achat pour suivre ses coûts, alors que son prix de vente, lui, ne bouge pas. C'est de cette poignée de règles qu'il a fallu tirer un modèle, puis une base réellement interrogeable.
Six entités, quatre associations. Et trois décisions qui méritent d'être expliquées, parce qu'elles conditionnent tout le reste du projet.
Les coordonnées de livraison appartiennent à COMMANDE, pas à
CLIENT : le destinataire change d'une commande à l'autre : un bouquet
offert, une livraison au bureau. C'est aussi ce qui impose une commande par point de
livraison, comme le demande le sujet.
BOUQUET est une entité faible
Les bouquets sont numérotés au sein de la commande : le bouquet 1 de la
commande 12 n'a rien à voir avec celui de la commande 13. D'où une clé composée
(numCommande, numBouquet), qui se propage à COMPOSITION.
ACHETER porte la date dans sa clé
Le même grossiste revend la même fleur d'un jour à l'autre, à un prix différent, et
c'est précisément ce que la fleuriste veut suivre. La clé est donc
(numSIREN, nomFleur, dateAchat).
NULL porteurs de sensColonne à NULL | Ce que cela signifie |
|---|---|
datePaiement | commande en attente de règlement, donc non livrable |
dateLivraisonEffective | commande pas encore soldée |
codePostalLivraison | retrait en magasin, donc aucun frais de livraison |
CHECK dans le script de création : cohérence chronologique des quatre dates
d'une commande, quantité strictement positive dans une composition (un bouquet sans
aucune tige n'a pas de sens), et tarifs jamais négatifs. La nuance est sur la livraison :
tarifLivraison >= 0 et non > 0, parce qu'une livraison
offerte reste une livraison.
Le dépôt contient les scripts dans l'ordre où ils s'exécutent, sur un schéma Oracle vide.
| # | Script | Rôle |
|---|---|---|
| 1 | 01_schema.sql | les 8 tables, leurs contraintes et leurs index |
| 2 | 03_import_donnees.sql | charge les 500 commandes des fichiers CSV |
| 3 | 04_requetes.sql | les requêtes R1 à R9, annotées de leurs résultats |
L'étape 2 se déroule en deux temps : le script crée d'abord des tables de travail
TMP_*, on y importe les CSV avec l'assistant de SQL Developer (séparateur
;, UTF-8, dates en JJ/MM/AAAA), puis la suite du script éclate
ces tables vers le schéma final. Le détour est nécessaire : aucun des six
CSV fournis n'est au format des tables cibles.
Deux scripts annexes complètent l'ensemble : 02_jeu_exemple.sql, qui répond à
la question du sujet sur la commande de Mme Juliette et s'exécute à la place
de l'import (les deux jeux de données se disputeraient les mêmes numéros de commande), et
00_drop.sql, pour repartir de zéro entre deux essais.
Cet exemple déroule un cas complet dans l'ordre imposé par les clés étrangères : la commune et les fleurs d'abord, la cliente ensuite, puis sa commande du 1er octobre à livrer le 5 chez un destinataire qui n'est pas elle, et enfin ses deux exemplaires d'un même bouquet de 10 roses et 6 tulipes.
Neuf questions métier, et les résultats obtenus sur le jeu de données du binôme, vérifiés un à un.
| Question | Résultat | |
|---|---|---|
| R1 | Nombre de commandes payées en 2021 | 127 |
| R2 | Les 3 codes postaux les plus livrés | 69230 Saint-Genis-Laval (27), 69680 Chassieu (24), 69006 Lyon 6e (21) |
| R3 | La fleur la plus utilisée en 2021 | le Tournesol, 438 fleurs |
| R4 | Nombre moyen de bouquets par client | 17,8 |
| R5 | Les fleurs jamais utilisées | Iris, Magnolia, Marguerite, Pensée |
| R6 | La fleur préférée de chaque client | 131 lignes pour 125 clients |
| R7 | Montant de chaque commande | les 500 commandes |
| R8 | Commande la plus chère en retrait magasin | Boutique Esprit Bohème, 535,00 € |
| R9 | Commande la plus chère, tous modes confondus | Moreau, 640,00 € livrés à Lyon 8e |
quantite * nbrBouquets partout où l'on totalise des fleurs ou
un montant. Oublier nbrBouquets sous-estime silencieusement le résultat, sans
jamais provoquer d'erreur.
JOIN classique sur LOCALITE ferait
disparaître ces 134 commandes du résultat. D'où le LEFT JOIN et le
NVL(l.tarifLivraison, 0) des requêtes R7 et R9.
-- La commande la plus chère, livraison ou retrait confondus.
-- Montant net : le brut, augmenté du tarif de la commune livrée.
SELECT cl.nomClient,
cmd.numCommande,
cmd.dateCommande,
NVL(l.ville, 'Retrait en magasin') AS destination,
SUM(f.tarifFleur * c.quantite * b.nbrBouquets) + NVL(l.tarifLivraison, 0) AS montant_net
FROM COMMANDE cmd
JOIN CLIENT cl ON cl.mailClient = cmd.mailClient
JOIN BOUQUET b ON b.numCommande = cmd.numCommande
JOIN COMPOSITION c ON c.numCommande = b.numCommande
AND c.numBouquet = b.numBouquet
JOIN FLEUR f ON f.nomFleur = c.nomFleur
LEFT JOIN LOCALITE l ON l.codePostal = cmd.codePostalLivraison
GROUP BY cmd.numCommande, cmd.dateCommande, cl.nomClient, l.ville, l.tarifLivraison
ORDER BY montant_net DESC
FETCH FIRST 1 ROWS ONLY;
Deux requêtes comportaient une part d'interprétation, tranchée en commentaire dans le
script : une moyenne par client et non par ligne de commande pour R4, et
RANK() plutôt que ROW_NUMBER() pour R6, afin de ne pas départager
arbitrairement deux fleurs à égalité.
Dix-sept pages rédigées à deux, qui doublent le dépôt : ce que le code fait, et pourquoi.
NULL peut être une information à part entière, à condition de l'avoir décidé et documenté.