Exercices MySQL Série 02

Exercice 01

1. Énoncé

   • 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é

2. Table TABLE 1

   • Dans une nouvelle base (ex login0), utilisez la méthode Création d'une table à partir d'un fichier avec phpMyAdmin pour importer le fichier Superstore_Ventes_Short
     • 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 350 lignes dans la table TABLE 1.
     • Vérifiez le nom des attributs (en particulier le nom de l'attribut Sales), et corrigez-les si besoin : onglet Structure, à partir de la TABLE 1. Ne modifiez pas les types des attributs pour le moment !

3. Table PRODUCT

   • A partir de la table TABLE 1, créez la table de dimension PRODUCT grâce à la méthode 4 vue en cours (Création d'une table résultat à partir de tables existantes) :
   • Complétez (dans un fichier texte que vous appellerez par exemple create.sql) l’instruction SQL suivante avec les attributs attendus pour la table PRODUCT :

   CREATE TABLE PRODUCT
   AS SELECT DISTINCT … // les attributs
   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 PRODUCT 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 PRODUCT a été créée, et que 314 produits ont été insérés.
4. L’étape suivante consiste à préciser la clé primaire de la table PRODUCT : il s’agit de ProductID.
5. Avec phpMyAdmin, affectez ProductID comme clé primaire pour la table PRODUCT: Onglet Structure : cliquez sur Primaire pour l’attribut ProductID

6. Table CUSTOMER

   • De la même manière que pour la table PRODUCT, créez la table CUSTOMER :

   CREATE TABLE CUSTOMER
   AS SELECT DISTINCT … // les attributs
   FROM `TABLE 1`;
   

7. Vérifiez que vous avez importé 189 clients.
8. Tentez d'affecter CustomerID comme clé primaire à cette table. Vous vous apercevrez d’un problème car plusieurs clients différents possèdent le même CustomerID : il s’agit en réalité des mêmes entreprises clientes situées dans des villes différentes : par ex, le client KB-16585 Ken Black, est localisé dans 2 villes différentes : Lafayette et Hialeah. CustomerID ne peut donc pas être seul clé primaire de la table CUSTOMER, il faut rajouter en plus la ville City.
9. Dans l’onglet Structure de la table CUSTOMER, sélectionnez les 2 attributs CustomerID et City et cliquez sur Primaire pour les affecter ensembles comme clés primaires.

10. Table SALES

    • Vous pourriez faire de même que pour les tables PRODUCT et CUSTOMER pour créer la table SALES, mais nous allons utiliser une autre méthode équivalente : la méthode 2 vue en cours (Création des tables, puis insertion des données en SQL).
    • Comme la clé primaire de la table CUSTOMER est (CustomerID, City), l’attribut City doit donc être inclus dans la table SALES.
    • De plus, en examinant les données, nous nous rendons compte que nous avons le choix entre 2 clés primaires candidates : soit le quintuplet (ProductID, ClientID, City, OrderDate, ShipDate), soit RowID. Pour plus de simplicité, nous choisissons que la clé primaire sera RowID.
    • La table SALES doit donc avoir le schéma suivant : SALES (RowID, ProductID, CustomerID, City, OrderDate, ShipDate, OrderID, ShipMode, Sales, Quantity, Profit, Discounts)

11. Table SALES

    • Utilisez la méthode 2 vue en cours (Création des tables, puis insertion des données en SQL) pour :
    • Créer la table SALES en complétant l’instruction SQL suivante (vérifiez les types des données dans la structure de TABLE 1, sauf pour OrderDate et ShipDate qui doivent pour le moment rester en VARCHAR(16) - nous corrigerons ce problème plus tard) :

    CREATE TABLE SALES
    ( RowID INT(4) PRIMARY KEY NOT NULL, // RowID clé primaire
    ProductID VARCHAR(15),
    … , // tous les autres attributs
    );
    

12. Insérer les données dans la table SALES en complétant l’instruction SQL suivante :

INSERT INTO SALES (RowID, ProductID, CustomerID, City, OrderDate,
ShipDate, OrderID, ShipMode, Sales, Quantity, Profit, Discounts)
SELECT DISTINCT … // tous les attributs
FROM `TABLE 1`

13. Vérifiez que 350 lignes ont été importées, et que RowID est bien clé primaire de la table SALES.
14. La table SALES comporte 2 clés référentielles :
15. Clé référentielle 1: ProductID clé référentielle vers PRODUCT(ProductID)
16. Clé référentielle 2: (CustomerID, City) clé référentielle vers CUSTOMER(CustomerID, City)
    • Pour préciser les clés référentielles de la table SALES : sur la table SALES, onglet Structure / Vue relationnelle.
    • Créez la clé référentielle 1 : lier la colonne ProductID à la colonne ProductID de la table PRODUCT.
    • Vous pouvez choisir l’un des 4 types d’actions suivants à effectuer ON DELETE (idem avec ON UPDATE) :
      • CASCADE permet, lorsque vous supprimez un produit dans la table PRODUCT qui existe encore dans la table SALES, de le supprimer « en cascade » dans la table SALES.
      • SET NULL permet, lorsque vous supprimez un produit dans la table PRODUCT qui existe encore dans la table SALES, de le mettre à NULL dans la table SALES.
      • NO ACTION laisse la table SALES intacte lorsque vous supprimez un produit dans la table PRODUCT alors qu’il existe encore dans la table SALES.
      • RESTRICT vous empêche de supprimer un produit dans la table PRODUCT alors qu’il existe encore dans la table SALES.
    • Il est conseillé de choisir "CASCADE" ou "RESTRICT" afin de conserver une base de données "propre".
    • Créez de la même manière la clé référentielle 2 sur les deux attributs (CustomerID, City) de la table CUSTOMER.
    • Vérifiez vos clés référentielles avec l’onglet Concepteur (il faut cliquer sur la base, par exemple front9 dans la figure suivante, et non sur une table de cette base) : vous devriez obtenir le schéma ci-dessous (afficher la liste des tables si ce n’est pas fait par défaut).
    • Sauvegardez la page des contraintes afin de pouvoir la retrouver plus tard…
17. La table de fait et les tables de dimensions ont été importées, vous pouvez donc supprimer la table TABLE 1.

18. VUE Superstore

    • Il nous reste maintenant à créer la vue simulant le cube Superstore et qui contient toutes les informations.
    • Complétez l’instruction SQL suivante (à compléter et adapter d’après le cours de SID, en particulier car la jointure entre CUSTOMER et SALES doit se faire sur CustomerID et City) :

    CREATE VIEW SuperStore
    AS SELECT … // tous les attributs
    FROM SALES AS S, CUSTOMER AS C, PRODUCT AS P
    WHERE … ; // jointures entre les tables
    

19. Vérifiez qu’une nouvelle vue a bien été créée (onglet Vues au même niveau que l’onglet Tables dans la base choisie), puis exécutez la commande suivante pour vérifier que la vue SuperStore contient bien 350 lignes :

SELECT COUNT(*) FROM Superstore ;

20. Quels sont les avantages d’utiliser une vue VIEW pour représenter le cube :
    • par rapport à l’utilisation de la TABLE 1 de départ,
    • par rapport à l’utilisation des 3 tables CUSTOMER, SALES et PRODUCT ?