TP 1 / G2
Cours : Base de Données Relationnelles Avancées
Concepts : Vues, Procédures Stockées et Index sous
MySQL 8
Filière : ASEDS/INE01
Année universitaire : 2024 - 2025
Encadrant : Pr. Hicham BAIDOURI
1. Introduction
Ce TP a pour objectif de vous familiariser avec des concepts avancés en bases
de données relationnelles sous MySQL, à savoir les vues, les procédures
stockées et l’optimisation via les index. Le contexte utilisé est celui d’un
système de gestion de bibliothèque.
2. Installation de MySQL 8
Pour Windows :
Télécharger MySQL depuis le site officiel :
[Link]
Lancer l’installateur et choisir « Full » (version 8.x).
Suivre les étapes de configuration (définir un mot de passe root, port
3306, etc.).
Installer MySQL Workbench si souhaité.
Pour macOS et Linux :
macOS :
[Link]
[Link]
Linux :
[Link]
[Link]
3. Contexte du TP
Vous allez modéliser une base de données pour une stock qui gère des
produits, des membres et des mouvements.
4. Schéma de la base
Tables :
Produits(id, nom, fournisseur, quantite_stock)
Membres(id, nom, date_inscription)
Mouvements(id, id_produit, id_membre, date_mouvement, date_entree)
5. Création des Tables
Créez les tables suivantes dans MySQL :
CREATE TABLE Produits (
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(255),
fournisseur VARCHAR(255),
quantite_stock INT
);
CREATE TABLE Membres (
id INT PRIMARY KEY AUTO_INCREMENT,
nom VARCHAR(255),
date_inscription DATE
);
CREATE TABLE Mouvements (
id INT PRIMARY KEY AUTO_INCREMENT,
id_produit INT,
id_membre INT,
date_mouvement DATE,
date_entree DATE,
FOREIGN KEY (id_produit) REFERENCES Produits(id),
FOREIGN KEY (id_membre) REFERENCES Membres(id)
);
6. Insertion de Données via une Procédure Stockée
Créez une procédure pour insérer automatiquement 10 000 enregistrements
dans chaque table :
DELIMITER //
CREATE PROCEDURE InitialiserDonnees()
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= 10000 DO
INSERT INTO Produits(nom, fournisseur, quantite_stock)
VALUES (CONCAT('Titre ', i), CONCAT('Auteur ', i), 2000 + MOD(i,
21));
INSERT INTO Membres(nom, date_inscription)
VALUES (CONCAT('Membre ', i), CURDATE() - INTERVAL MOD(i,
1000) DAY);
INSERT INTO Mouvements(id_produit, id_membre,
date_mouvement, date_entree)
VALUES (MOD(i, 10000) + 1, MOD(i, 10000) + 1, CURDATE() -
INTERVAL MOD(i, 30) DAY, NULL);
SET i = i + 1;
END WHILE;
END;
//
DELIMITER ;
Lancez la procédure stockée pour initialiser les données et attendre quelques
minutes :
CALL InitialiserDonnees();
7. Vues et Contrôle d'Accès
Créez une vue pour visualiser les mouvements avec nom du membre et nom
du produit :
CREATE VIEW vue_mouvements AS
SELECT [Link], [Link], [Link] as membre, e.date_mouvement,
e.date_entree
FROM Mouvements e
JOIN Produits l ON e.id_produit = [Link]
JOIN Membres m ON e.id_membre = [Link];
Créez un utilisateur limité à cette vue :
CREATE USER 'mouvements_viewer'@'localhost' IDENTIFIED BY
'motdepasse';
GRANT SELECT ON vue_mouvements TO
'mouvements_viewer'@'localhost';
Déconnectez-vous du compte ‘root’ et connectez-vous par le nouvel utilisateur
‘mouvements_viewer’ et vérifiez que vous accédez uniquement à la vue
précitée.
8. Optimisation avec Index
Exécutez cette requête et visualisez son plan d’exécution et notez le cout de la
requête :
select * from Produits where nom like 'Membre 13%'
Ajoutez un index sur la colonne `nom` pour améliorer les performances de
recherche :
CREATE INDEX idx_nom ON Produits(nom);
Relancez le plan d’exécution et notez le nouveau cout d’exécution.
9. Points d’évaluation
Appelez la procédure `InitialiserDonnees()` et vérifiez que les données
sont bien insérées.
Testez la vue `vue_mouvements` avec l’utilisateur
`mouvements_viewer`. Que constatez-vous ?
Effectuez une requête sur les mouvements en filtrant par `inom` et
utilisez `EXPLAIN` pour analyser le plan d'exécution avant l'ajout de
l'index.
Ajoutez l'index et relancez la même requête avec `EXPLAIN`. Comparez
les résultats.
