Exercices MySQL Série 02

• Découvrir l’environnement MySQL/phpMyAdmin
• Être capable de créer une table avec MySQL ou phpMyAdmin

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é

1. 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 !

2. 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.
• L’étape suivante consiste à préciser la clé primaire de la table PRODUCT : il s’agit de ProductID.
• Avec phpMyAdmin, affectez ProductID comme clé primaire pour la table PRODUCT: Onglet Structure : cliquez sur Primaire pour l’attribut ProductID

3. 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`;

• Vérifiez que vous avez importé 189 clients.
• 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.
• 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.

4. 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)

5. 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
);

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

• Vérifiez que 350 lignes ont été importées, et que RowID est bien clé primaire de la table SALES.
• La table SALES comporte 2 clés référentielles :
• Clé référentielle 1 : ProductID clé référentielle vers PRODUCT(ProductID)
• 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…
• La table de fait et les tables de dimensions ont été importées, vous pouvez donc supprimer la table TABLE 1.

6. 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

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

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

2. Solution

• Essayez de faire l’exercice de votre côté avant de regarder la Solution !

1. Énoncé

• La BD (Base de Données) pour une bibliothèque est constituée des tables suivantes:

• Table des livres

• Table des livres (Numéro inventaire du livre, titre et auteur du livre, nombre d’exemplaires dans la bibliothèque)

NumInv	Titre Auteur	Qte
323	Bases De Données C.J. Date	3
124	Algorithmes D. Knuth	2
1110	Programmation B. Meyer	6
241	Langage C B. Kernighan	5
321	Unix K. Thompson	2
216	Systèmes Exploitations Crocus	1
312	Langage Java J. Gosling	8

• Table abonné(e)s

• Table des abonné(e)s (Numéro abonné, son nom et prénom )

NumAb	Prénom	Nom
12	Benali	Ali
10	Said	BenSaid
11	Sayf	Snoussi
24	Farida	Tej
32	Samira	Frouja
21	Taoufik	Akacha
31	Samiha	Samiha

• Table prêts

• Table des prêts (Numéro inventaire du livre emprunté, Numéro abonné de l’emprunteur et la date d’emprunt)

NumInv	NumAb
323	10	1/11/2021
124	12	2/11/2021
323	12	3/10/2021
241	32	3/10/2021
321	14	13/10/2021
323	14	10/11/2021
312	10	3/10/2021
323	32	13/09/2021

• Écrire en SQL les requêtes suivantes :
• Créer la base données bibliothèque
• Créer les tables présentées ci-desssus
• Alimenter chaque table par le données qui convient

2. Solution

• Essayez de faire l’exercice de votre côté avant de regarder la Solution !

–>