Exercices MySQL Série 01

Cet exercice est tiré tu TP1 de Découverte de l’environnement MySQL/phpMyAdmin et mise en place d’un entrepôt de données sous MySQL de 2020/2021 de l’université

1. Objectifs :

• 1) Découvrir l’environnement MySQL/phpMyAdmin
• 2) Mettre en place un entrepôt de données en ROLAP en utilisant le système de gestion de bases de données relationnelles MySQL

2. Consignes pour l’écriture des instructions MySQL :

• Dans ce TP, ainsi que dans tous les autres TPs, toutes les instructions SQL devront être écrites dans un fichier texte (par exemple avec NotePad, NotePad++ ou TextEdit) afin d’en garder la trace, de très facilement faire des copier/coller dans l’onglet SQL de phpMyAdmin et de pouvoir rendre le travail éventuellement demandé en fin de séance.
• Le fichier Mini_HypermarcheSTID.csv est issu d’un traitement ETL (Extract-Transform-Load) tel que celui de Tableau Prep Builder, Il contient un sous-ensemble des données du fichier HypermarcheSTID.xls.
• Pour des raisons de performance, et afin d’éviter de « planter » le serveur, nous n’y avons conservé qu’un nombre réduit de données (300 lignes). Mais le principe que nous allons suivre serait bien sûr exactement le même avec 10000 lignes…
• Nous allons donc concevoir un entrepôt de données en MySQL pour analyser les ventes de notre hypermarché. Le modèle en étoile proposé pour cet entrepôt de données est le suivant :



• D’après ce modèle en étoile, le schéma relationnel sous MySQL devrait être le suivant :

CLIENT (IDClient, NomClient, Segment, Ville, Region, Pays,
ZoneGeographique)
PRODUIT (IDProduit, NomProduit, SousCategorie, Categorie)
DATE (IDDate, Mois, Annee)
VENTES (IDProduit, IDClient, DateDeCommande, DateExpedition, IDLigne,
IDCommande, ModeExpedition, MontantVente, Quantite, Remise, Profit)

• Comme vous allez le constater, ce schéma relationnel va légèrement évoluer car nous nous apercevrons de quelques points délicats que nous résoudrons. De plus, il sera inutile de créer la table de dimension DATE car nous pourrons manipuler les dates directement avec le langage MySQL pour en extraire les années et les mois. Au final, nous obtiendrons le schéma relationnel suivant :

CLIENT (IDC, IDClient, NomClient, Segment, Ville, Region, Pays, ZoneGeographique)
PRODUIT (IDProduit, NomProduit, SousCategorie, Categorie)
VENTES (IDLigne IDProduit, IDC, DateDeCommande, DateExpedition,
IDCommande, ModeExpedition, MontantVente, Quantite, Remise, Profit)

• A partir du fichier Mini_HypermarcheSTID, nous allons dans un premier temps construire une table contenant toutes les données, puis à partir de cette table, nous construirons des tables normalisées pour la table de fait VENTES et les tables de dimension CLIENT et PRODUIT.

3. Les étapes à suivre sont les suivantes :

• 1) Dans une nouvelle base (ex login0), utilisez la méthode 3 vue en cours (Création d’une table à partir d’un fichier avec phpMyAdmin) pour importer le fichier Mini_HypermarcheSTID.csv
• Cochez « La première ligne du fichier contient le nom des colonnes de la table », sinon les noms des colonnes de la table créée ne correspondront pas à l’entête du fichier csv
• Les séparateurs sont des « ;« 
• Vérifiez que vous avez réussi à importer 300 lignes dans la table TABLE 1.
• 2) A partir de cette table, créez la table de dimension PRODUIT grâce à la méthode 4 vue en cours (Création d’une table résultat à partir de tables existantes) :
• Complétez l’instruction SQL suivante avec les attributs attendus pour la table PRODUIT :

CREATE TABLE PRODUIT
AS SELECT DISTINCT … (à completer)
FROM `TABLE 1`;
// les ` ` dans `TABLE 1` sont importants car le nom de la table
comporte un espace

• Cette instruction crée une nouvelle table PRODUIT dont le schéma est défini par les attributs de la clause SELECT et dont les n-uplets correspondent au résultat de la requête. Le mot DISTINCT est important pour éviter les doublons.
• Exécutez-la dans l’onglet SQL et vérifiez que la table PRODUIT a été créée, et que 276 produits ont été insérés.
• L’étape suivante consiste à préciser la clé primaire de la table PRODUIT : il s’agit de IDProduit.
• Avec phpMyAdmin, essayez d’affecter IDProduit comme clé primaire pour la table PRODUIT : Onglet Structure : cliquez sur CléPrimaire pour l’attribut IDProduit
• En exécutant, phpMyAdmin nous dit qu’il ne peut pas créer la clé primaire car le produit FOU-CL-10001059 est en doublon. Effectivement il y a eu une erreur et 2 produits différents (‘Cardinal Machine à relier, Clair’ et ‘Acco Couverture de classeur Recycle’ ont le même IDProduit. Vous pouvez le vérifier avec l’onglet Afficher sur la table PRODUIT.
• FOU-CL-10001059 Cardinal Machine a relier, Clair
• FOU-CL-10001059 Acco Couverture de classeur, Recycle
• Nous allons donc modifier l’un des IDProduit avec l’instruction suivante :

UPDATE PRODUIT SET IDProduit = 'FOU-CL-10001060’
WHERE NomProduit = 'Cardinal Machine a relier, Clair';

• Vérifiez maintenant que vous pouvez affecter IDProduit comme clé primaire pour la table PRODUIT :
• Onglet Structure : cliquez sur CléPrimaire pour l’attribut IDProduit.
• Écrivez la même instruction pour modifier également cet IDProduit dans la table TABLE 1 (car nous en aurons besoin pour créer la table VENTES) :

UPDATE `TABLE 1` SET IDProduit = 'FOU-CL-10001060’
WHERE NomProduit = 'Cardinal Machine a relier, Clair';

• 3) Créez la table CLIENT
• Si nous essayons de créer de la même manière la table de dimension CLIENT, nous nous apercevrons que le problème de clé primaire est identique : plusieurs clients différents possèdent le même IDClient : il s’agit en réalité des mêmes entreprises clientes situées dans des villes différentes : par ex, le client HF-15715 Hortense Fongemie, est localisé dans 3 villes différentes : Brest, Viroflay et Aix-la-Chapelle.
• L’IDClient ne peut donc pas être clé primaire de la table CLIENT, de même qu’aucun autre des attributs de la table CLIENT.
• Nous allons donc créer une clé primaire auto-incrémentée pour la table CLIENT.
• Utilisez la méthode 2 vue en cours (Création des tables, puis insertion des données en SQL) pour :
• Créer la table CLIENT avec une clé primaire auto-incrémentée en complétant l’instruction SQL suivante (vérifiez les types des données (ex VARCHAR(8) pour IDClient) dans la structure de TABLE 1) :

CREATE TABLE CLIENT
(IDC INT PRIMARY KEY AUTO_INCREMENT, // la nouvelle clé primaire
IDClient VARCHAR(8) NOT NULL,
… // tous les autres attributs
);

• Une fois la table créée, vérifiez sa structure : Onglet Structure. En particulier vérifiez que la clé primaire est bien l’attribut IDC.
• Insérez des données dans la table Client en complétant l’instruction SQL suivante :

INSERT INTO CLIENT (IDclient, NomClient, Segment, Ville, Region,
ZoneGeographique) SELECT DISTINCT …
FROM `TABLE 1`;
// l’attribut IDC va s’auto-incrémenter tout seul, les autres attributs doivent être retrouvés par la requête

• Vérifiez que 138 clients ont été correctement insérés dans la table CLIENT : Onglet Afficher.
• 4) Créez la table VENTES
• Il nous reste à créer la table de faits VENTES à partir de TABLE 1.
• La clé primaire de la table CLIENT ayant changé, la table VENTES doit donc avoir le schéma suivant :

VENTES (IDProduit, IDC, DateDeCommande, DateExpedition, IDLigne, IDCommande, ModeExpedition, MontantVente, Quantite, Remise, Profit)

• où IDC REFERENCES CLIENT(IDC) et IDProduit REFERENCES PRODUIT(IDProduit) et où la clé primaire est (IDProduit, IDC, DateDeCommande, DateExpedition) d’après le modèle en étoile.
• Utilisez de nouveau la méthode 4 vue en cours (Création d’une table résultat à partir de tables existantes) pour créer la table VENTES en complétant l’instruction SQL suivante :

CREATE TABLE VENTES
AS SELECT DISTINCT …
FROM `TABLE 1` AS T, CLIENT AS C
WHERE …;
// Attention : IDClient n’étant pas clé primaire de la table
CLIENT, la jointure entre TABLE 1 et CLIENT doit se faire sur les
attributs IDClient, NomClient et Ville

• Vérifiez que la table VENTES a été créée avec 300 n-uplets.
• Constatez les problèmes suivants :
• Les attributs DateDeCommande, DateExpedition, MontantVente, Remise et Profit ont été créés avec des types inadéquats (cela était déjà le cas dans TABLE 1) : DateDeCommande et DateExpedition auraient dû être de type DATE et MontantVente, Remise et Profit auraient dû être de type NUMERIC ou FLOAT. Nous corrigerons ces problèmes plus tard.
• D’après le cours de SID, la clé primaire de la table VENTES devrait être la composition des clés primaires des tables de dimension (ici IDC, IDProduit, DateDeCommande, DateExpedition). Or ce n’est pas le cas : par exemple, le produit FOU-CL-10002485 a été commandé 2 fois par le client 74 le 14/06/2017 et expédié le 21/06/2017, mais sur deux lignes de commandes différentes (lignes 152 et 155) ! En réalité, la clé primaire de la table VENTES est IDLigne.
• Affectez donc IDLigne comme clé primaire de la table VENTES (onglet Structure).
• 5) supprimer la table TABLE 1
• Toutes vos données ont été importées, vous pouvez donc supprimer la table TABLE 1.
• 6) La table VENTES comporte 2 clés référentielles :
Clé référentielle 1 : IDC clé référentielle vers CLIENT(IDC)
Clé référentielle 2 : IDProduit clé référentielle vers PRODUIT(IDProduit)
• Pour préciser les clés référentielles de la table VENTES : sur la table VENTES, onglet Structure/Vue relationnelle.
• Créez la clé référentielle 1 : lier la colonne IDC à la colonne IDC de la table CLIENT.
• Vous pouvez choisir l’un des 4 types d’actions suivants à effectuer ON DELETE (idem avec ON UPDATE) :
• CASCADE permet, lorsque vous supprimez un client dans la table CLIENT qui existe encore dans la table VENTES, de le supprimer « en cascade » dans la table CLIENT.
• SET NULL permet, lorsque vous supprimez un client dans la table CLIENT qui existe encore dans la table VENTES, de le mettre à NULL dans la table VENTES.
• NO ACTION laisse la table VENTES intacte lorsque vous supprimez un client dans la table CLIENT alors qu’il existe encore dans la table VENTES.
• RESTRICT vous empêche de supprimer un client dans la table CLIENT alors qu’il existe encore dans la table VENTES.
• Il est conseillé de choisir CASCADE ou RESTRICT afin de conserver une base de données « propre ».
• Ajoutez une contrainte pour créer de la même manière la clé référentielle 2.
• Vérifiez vos clés référentielles avec l’onglet Concepteur (il faut cliquer sur la base, par exemple front4 dans la figure suivante, et non sur une table de cette base) : vous devriez obtenir le schéma suivant :

Cet exercice est tiré tu TP2 de Découverte de l’environnement MySQL/phpMyAdmin et mise en place d’un entrepôt de données sous MySQL de 2020/2021 de l’université

AVIS (id, IDC, IDProduit, dateAvis, note, commentaire)

• Les attributs doivent être définis de la manière suivante :
• id : de type INT, auto-incrémenté et clé primaire ;
• IDC : clé référentielle vers CLIENT(IDC), donc du même type, et obligatoire ;
• IDProduit : clé référentielle vers PRODUIT(IDProduit), donc du même type, et obligatoire ;
• dateAvis : obligatoire, et dont la valeur par défaut est la date du jour ;
• note : la note est obligatoirement égale à 1, 2, 3, 4 ou 5 ;
• commentaire : de type TEXT, et optionnel.

• Indice : vous devez utiliser l’instruction SQL suivante qui permet de rajouter une contrainte à la définition de la table AVIS :

  ALTER TABLE AVIS ADD CONSTRAINT …

5) Ecrivez une instruction SQL pour donner un avis du client 76 sur le produit FOU-AR- 10000080. Vérifiez avec phpMyAdmin.

• Écrivez une instruction SQL pour augmenter de 1 la note du client 78 à tous les produits.
• Vérifier avec phpMyAdmin.
• Indice : vous devez utiliser l’instruction UPDATE

• Écrivez une instruction SQL pour augmenter encore de 1 la note du client 78 à tous les produits de la sous-catégorie Classeurs. Vérifiez avec phpMyAdmin.
• Indice : vous devez compléter la clause WHERE de l’instruction de la question 9) avec une requête SELECT permettant de retrouver dans la table PRODUIT les IDProduit dont SousCategorie = ‘Classeurs’.

• Dans la table CLIENT, la ville Irun (ville du Pays Basque espagnol) a été mal saisie : elle est écrite Ir ?n. Or cette ville peut être celle de plusieurs clients et on ne les connait pas tous. Il est donc préférable, plutôt que de passer par l’interface phpMyAdmin pour modifier le nom de cette ville, d’écrire une instruction SQL qui le fasse pour tous les clients. Ecrivez cette instruction, puis vérifiez avec phpMyAdmin.
• Indice : vous pouvez utiliser le mot clé LIKE avec le caractère spécial _ pour remplacer le ? de la manière suivante : Ville LIKE ‘Ir_n’

• Écrivez une instruction SQL pour supprimer les avis du client 78. Vérifier avec phpMyAdmin.
• Indice : vous devez utiliser l’instruction DELETE