Guillaume Rivière 2016 – 2024

Le logo de la CCI Bayonne Pays Basque

Systèmes de Gestion de Bases de Données

TP1 : Créer et peupler des tables

BUTS PÉDAGOGIQUES

  • Création d'une base de données MySQL
  • Création de tables MySQL et de relations entre les tables
  • Alimenter les tables
  • Utilisation de l'interface d'administration phpMyAdmin

Pour le contexte du cas traité dans ce TP, nous nous projetons entre le bureau d'étude, l'approvisionnement et la chaîne de production d'une entreprise. Le besoin est, pour fabriquer les nombreuses gammes de produits de l'entreprise, de s'approvisionner du bon nombre de composants pour faire fonctionner la chaîne de production. Pour ce faire, une base de données sera renseignée automatiquement par les logiciels de CAO du bureau d'étude. En voici le schéma relationnel :

Les trois tables de la base de données avec les deux liens des clés étrangères
Figure 0.1 : Schéma relationnel de la base de données Bureau d'Étude.

Cette base de données avec trois tables reste très succincte comparée aux 30.000 tables de l'ERP de SAP ! (L'éditeur de logiciels SAP est l'acteur qui occupe la plus grande part du marché des logiciels de gestion depuis plus de 30 ans). Mais, le but du cas présentement traité, que nous avons minimalisé à trois tables, est avant tout de vous permettre de découvrir les outils de gestion de bases de données.

Exercice 1 • Première table

L'objectif est de créer une nouvelle base de données bureau_etude et une table produit

Pour travailler lors des TP, nous allons utiliser un client HTTP (c.-à-d. un navigateur web), un serveur HTTP, une interface de gestion et un SGBD. Ces trois derniers éléments peuvent être installés sur des machines différentes (c'est d'ordinaire le cas, au moins pour le client), mais ils peuvent également se trouver sur un seul ordinateur. Dans le cadre de cette série de TP, nous utiliserons le paquetage WAMP, installé sur vos machines, qui comprend le serveur web Apache, l'interface de gestion phpMyAdmin et le SGBD MySQL.

Les flux entre le navigateur web, le serveur web, le SGBD et le stockage des bases sur le disque dur
Figure 1.1 : Contexte de l'environnement de développement : client HTTP, serveur HTTP et serveur MySQL.
Question 1.1 : L'environnement : démarrer le serveur web et le serveur MySQL (WAMP)
L'icône W dans la barre des tâches est en vert lorsque les serveurs sont démarrés L'icône W dans la barre des tâches est en orange ou rouge lorsque le démarrage des serveurs a rencontré un problème
Figure 1.1.1 : Icône W en vert lorsque les serveurs sont démarrés, en orange ou rouge lorsque le démarrage des serveurs a rencontré un problème.

Vous devrez renouveler cette manipulation au début de chaque TP pour préparer l'environnement de travail.

Question 1.2 : L'interface de gestion de MySQL

Plusieurs moyens existent pour gérer le SGBD MySQL. Par exemple, avec le client mysql depuis une invite de commande, ou encore depuis phpMyAdmin.

  1. Ouvrez un navigateur web (nous avons testé que toutes les fonctionnalités sont compatibles avec firefox).
  2. Copiez l'une des deux adresses possibles : http://127.0.0.1/phpmyadmin/
  3. Tapez (bien entendu) sur la touche entrée pour accéder à la page
  4. Ensuite, donnez le nom d'utilisateur "root", le mot de passe "" (càd laisser vide) et le choix du serveur "MySQL" :
La page de connexion de phpMyAdmin
Figure 1.2.1 : Formulaire d'authentification de phpMyAdmin.

Si votre navigateur affiche un message :
    404 not found
    Nothing matching the URI
    Traceback [...]
    CherryPy
alors essayez avec le navigateur web Internet Explorer qui a été paramétré différemment (Proxy).

La page d'accueil de phpMyAdmin dans un navigateur web
Figure 1.2.2 : Page d'accueil de phpMyAdmin.
Question 1.3 : Créer une nouvelle base de données
  1. À gauche, dans l'arborescence des bases, cliquez sur Nouvelle base de données
  2. Donnez le nom de cette nouvelle base de données bureau_etude et cliquez sur le bouton Créer
  3. Vérifier que la nouvelle base de données apparaît dans la liste des bases de données (à gauche)
L'option de création dans le menu phpMyAdmin
Figure 1.3.1 : Menu phpMyAdmin.

ATTENTION Pour les noms des bases de données, NE JAMAIS utiliser de caractères accentués, de caractères spéciaux ou des espaces. Utilisez uniquement les caractères alphabétiques a-z, les chiffres 0-9, ou le caractère souligné _ (underscore, sur la touche 8 du clavier). Ainsi, tout se passera bien. Sinon, bon courage !

Question 1.4 : Créer une nouvelle table
  1. Toujours dans l'arborescence des bases (à gauche), sélectionnez la base de données bureau_etude
  2. Donnez le nom de cette nouvelle table produit, le nombre de 2 colonnes et cliquez sur le bouton Créer
  3. Choisissez le moteur de stockage InnoDB
  4. Saisissez les paramètres des deux colonnes :
    • Colonne 1 : Nom = id, type = INT, index = PRIMARY, A_I (AUTO_INCREMENT) = checked
    • Colonne 2 : Nom = libelle, type = VARCHAR, Taille/Valeurs=50
  5. Cliquez sur le bouton Sauvegarder
  6. Vérifiez dans l'arborescence des bases que la nouvelle table produit apparaît au-dessous de la base de données bureau_etude
  7. Toujours dans l'arborescence, cliquez sur la base de données bureau_etude, puis sur l'onglet Concepteur et visualisez graphiquement la table que vous venez de créer :
    Accès immédiat à l'onglet Concepteur     Accès à l'onglet Concepteur après avoir déroulé le menu « plus »
    Figure 1.4.1 : Accès immédiat à l'onglet Concepteur, ou en déroulant le menu « plus ».
  8. Organisez visuellement cette vue en déplaçant les tables, puis conservez le placement en enregistrant le placement avec le bouton Enregistrer et en lui donnant un nom :
    Boîte de dialogue pour donner un nom à la vue
    Figure 1.4.2 : La boîte de dialogue pour donner un nom à la vue.

ATTENTION (ON NE LE RÉPÉTERA JAMAIS ASSEZ) Pour les noms des tables, des colonnes, comme pour les noms des bases de données, NE JAMAIS utiliser de caractères accentués, de caractères spéciaux ou des espaces. Utilisez uniquement les caractères alphabétiques a-z, les chiffres 0-9, ou le caractère souligné _ (underscore, sur la touche 8 du clavier). Ainsi, tout se passera bien. Sinon, bon courage !

En fait, cette remarque est valable pour tous les noms que les programmeurs donnent aux variables, fichiers, répertoires, serveurs, programmes, …. Vous devrez donc prolonger cette bonne habitude tout au long de vos enseignements en informatique à l'ESTIA.

L'onglet concepteur est absent ? Le concepteur est rarement installé par défaut. Mais il a déjà été installé sur vos configurations ESTIA. Si tel n'est pas le cas, il est possible de l'installer sur votre serveur WAMP en suivant cette procédure.

Exercice 2 • Deuxième table

L'objectif est d'ajouter une nouvelle table composant dans la base de données bureau_etude

  1. Toujours dans l'arborescence des bases (à gauche), sélectionnez la base de données bureau_etude
  2. En dessous du nom de la base bureau_etude cliquez sur Nouvelle table
  3. Choisissez le moteur de stockage InnoDB
  4. Donnez le nom de cette nouvelle table composant et saisissez les paramètres des trois colonnes :
    • Colonne 1 : Nom = id, type = INT, index = PRIMARY, A_I (AUTO_INCREMENT) = checked
    • Colonne 2 : Nom = libelle, type = VARCHAR, Taille/Valeurs= 50
    • Colonne 3 : Nom = cout, type = DECIMAL, Taille/Valeurs=10,3
    • Remarque : Inutile de saisir la quatrième colonne, laissez-la en l'état, elles seront ignorées
  5. Cliquez sur le bouton Sauvegarder
  6. Vérifiez dans l'arborescence des bases que la nouvelle table composant apparaît au-dessous de la base de données bureau_etude
    • Vérifiez également la structure de cette nouvelle table composant :
    • Si jamais le DECIMAL a mal fonctionné (et que vous obtenez par exemple 10,0), exécutez la requête suivante (onglet SQL) pour rectifier :
      ALTER TABLE `composant` CHANGE `cout` `cout` DECIMAL(10,3) NOT NULL;
  7. Toujours dans l'arborescence, cliquez sur la base de données bureau_etude, puis sur l'onglet Concepteur et visualisez graphiquement les tables que vous venez de créer.

Exercice 3 • Troisième table

L'objectif est d'ajouter une nouvelle table nomenclature dans la base de données bureau_etude, mais en le faisant cette fois depuis une requête SQL.

  1. Créer une nouvelle table nomenclature en exécutant la requête suivante depuis l'onglet SQL :
    • CREATE TABLE IF NOT EXISTS `bureau_etude`.`nomenclature` (
        `id_produit` INTEGER NOT NULL,
        `id_composant` INTEGER NOT NULL,
        `nombre` INTEGER NOT NULL,
        PRIMARY KEY (`id_produit`, `id_composant`)
      ) ENGINE = INNODB ;
  2. Cliquez sur la base de données bureau_etude, puis sur l'onglet Concepteur et vérifiez la présence des trois tables.

Exercice 4 • Peupler la base

Nous allons alimenter la base de données bureau_etude avec des produits, avec des composants, renseigner la nomenclature, puis faire quelques observations.

Question 4.1 : Les produits
  1. Dans l'arborescence, cliquez sur la table produit puis ouvrez l'onglet Insérer
  2. Donnez les libellés des deux produits (inutile de saisir le champ id) :
    • libelle = Chaise
    • libelle = Banc
  3. Cliquez sur le dernier bouton Exécuter tout en bas du formulaire
  4. Ouvrez maintenant l'onglet Parcourir
  5. Observez les lignes de la table. Quelles sont les valeurs des id des deux produits ?
Question 4.2 : Les composants
  1. Dans l'arborescence, cliquez sur la table composant puis ouvrez l'onglet Insérer
  2. Donnez les libellés des six composants (inutile de saisir le champ id) :
    • libelle = Vis, cout = 0.05
    • libelle = Pied court, cout = 3.5
    • libelle = Dossier court, cout = 15
    • libelle = Dossier long, cout = 45
    • libelle = Assise courte, cout = 20
    • libelle = Assise longue, cout = 50
  3. Cliquez sur le dernier bouton Exécuter tout en bas du formulaire
  4. Ouvrez maintenant l'onglet Parcourir
  5. Vérifiez que les six composants sont bien présents et observez la valeur des id
Question 4.3 : Les nomenclatures
  1. Il est aussi possible de faire des insertions avec des requêtes SQL : ouvrez l'onglet SQL
  2. Copiez la requête suivante :
    • INSERT INTO `bureau_etude`.`nomenclature` (`id_produit`, `id_composant`, `nombre`) VALUES
      (1, 1, 6),
      (1, 2, 4),
      (1, 3, 1),
      (1, 5, 1),
      (2, 1, 8),
      (2, 2, 6),
      (2, 4, 1),
      (2, 6, 1) ;
  3. Cliquez sur le dernier bouton Exécuter tout en bas du formulaire
  4. Ouvrez maintenant l'onglet Parcourir
    • Vérifiez que les huit lignes de nomenclatures sont présentes dans la table. Que cela représente-t-il ?
    • Nous savons ainsi que le produit chaise de l'entreprise a 4 pieds courts et que le produit banc en a 6. Combien de vis sont nécessaires pour assembler une chaise ? Et un banc ?

Exercice 5 • Dépeupler

Question 5.1 : Dépeuplement et incohérence
  1. Dans l'arborescence, cliquez sur la table composant (l'onglet Parcourir apparaît par défaut du moment que des lignes existent)
  2. Avec le bouton Effacer, supprimez le composant Assise longue
  3. Cliquez sur l'onglet Parcourir et vérifiez que les seuls composants restant sont Vis, Pied court, Dossier court, Dossier long et Assise courte.
  4. Dans l'arborescence, cliquez sur la table nomenclature et observez que les nomenclatures du banc (id_produit = 2) sont toujours présentes ! Nous disons alors que la base de données est dans un état incohérent. En effet, la clé étrangère id_composant de deux lignes de la table nomenclature désigne le id d'un composant qui n'existe plus.

Comment remédier à cela ? Tout simplement, en définissant des relations …

  1. Tout d'abord, remettons en place le composant Assise longue (mais en faisant attention d'avoir son id = 6)
    1. Dans l'arborescence, cliquez sur la table composant puis ouvrez l'onglet Insérer
    2. Donnez id = 6, libelle = Assise longue et cout = 50
    3. Cliquez sur le bouton Exécuter
  2. Ouvrez maintenant l'onglet Parcourir
  3. Vérifiez que les six composants sont bien tous de nouveaux présents et observez la valeur des id
  4. À votre avis, que ce serait-il passé si nous n'avions pas précisé que id = 6 lors de cette nouvelle insertion ?

Pour se prémunir d'un état incohérent de la base de données :

• Soit supprimer d'abord toutes les lignes dans toutes les tables qui font référence à la clé primaire de la donnée à supprimer. Puis la supprimer. Historiquement c'est ce que faisaient les programmeurs MySQL;

• Soit nous pouvons gérer avec MySQL les relations entre les clés primaires et les clés étrangères. Cela est notamment possible avec le moteur InnoDB avec lequel vos tables ont été créées. Si jamais ce n'était pas le cas, vous pouvez le corriger avec les requêtes suivantes :
ALTER TABLE `composant` ENGINE=INNODB;
ALTER TABLE `produit` ENGINE=INNODB;
ALTER TABLE `nomenclature` ENGINE=INNODB;

Maintenant, définissons une relation entre les deux tables nomenclature et composant.

Question 5.2 : Relations entre clés étrangères et clés primaires
  1. Allez sur l'onglet Structure de la table composant et activez l'indexation de la colonne id en cliquant sur .
    Faîtes de même pour les deux clés étrangères de la table nomenclature, ainsi que que pour la clé primaire de la table produit.
  2. Puis, allez sur l'onglet Concepteur de la base de données bureau_etude (Rappel : il faut d'abord cliquer sur la base de données bureau_etude dans l'arborescence)
  3. Cliquez sur le bouton Nouvelle relation , puis :
    • Cliquez d'abord sur la clé primaire id de la table composant
    • Cliquez ensuite sur la clé étrangère id_composant de la table nomenclature
    • Cliquez sur OK dans la boîte de dialogue
    • Une nouvelle relation apparaît entre les deux tables composant et nomenclature

Comment supprimer une relation ? Vous pouvez le faire graphiquement en cliquant sur l'extrémité arrondie de la relation.

Le choix par défaut est vide pour les règles de clé étrangère ON DELETE et ON UPDATE
Figure 5.2.1 : Boîte de dialogue pour les règles de clé étangère.

Maintenant, retournez sur l'affichage des lignes de la table composant et essayez de nouveau de supprimer le composant Assise longue. Que se passe-t-il ?

Nous allons maintenant mettre en place un autre comportement possible …

Question 5.3 : Suppressions en cascade
  1. Dans l'arborescence, cliquez sur la table nomenclature puis ouvrez l'onglet Structure
  2. Cliquez sur  Vue relationnelle
  3. Modifiez les deux paramètres de la relation qui porte sur la clé étrangère id_composant
    • ON DELETE : RESTRICT devient CASCADE
    • ON UPDATE : RESTRICT devient CASCADE
  4. Cliquez sur le bouton Sauvegarder
  5. Dans l'arborescence, cliquez sur la table composant puis effacez avec le bouton Effacer le composant Assise longue
  6. Dans l'arborescence, cliquez maintenant sur la table nomenclature et observez les lignes de nomenclatures. Combien de lignes de nomenclatures sont encore présentes ? À quels composants correspondent ces lignes de nomenclatures ?
  7. Pourquoi les lignes qui comportaient id_composant = 6 ont disparues ? La base de données bureau_etude est-elle dans un état cohérent ?

Depuis la vue Concepteur, créez de même une nouvelle relation entre la clé étrangère id_produit de la table nomenclature et la clé primaire id de la table produit. Sauf que cette fois, vous choisirez l'option CASCADE pour définir le comportement à adopter en cas de suppression (on delete) ou de mise à jour (on update) :

Choisir CASCADE pour les règles de clé étrangère ON DELETE et ON UPDATE
Figure 5.3.1 : Boîte de dialogue pour les règles de clé étangère.

Exercice 6 • Requêtes basiques

Vous devez maintenant utiliser la base de données bureau_etude que vous avez créée et peuplée, en l'interrogeant avec des requêtes SELECT pour en extraire des informations.

  1. Tout d'abord, peuplez la base avec un nouveau composant « Plateau table » (de coût 100) et trois produits (Chaise, Banc et Table basse) et ajouter les nomenclatures correspondantes.
  2. Ouvrir la fenêtre de requêtes avec l'onglet SQL puis cocher « Conserver la boîte de requête ».
    1. Copiez une première requête : SELECT * FROM `produit` ;
    2. Cliquez sur le bouton Exécuter en bas de la fenêtre
    3. Observez le résultat dans la fenêtre principale
  3. Ou encore, successivement, les requêtes SQL :
    • SELECT * FROM `produit` WHERE `id` = 1 ;
    • SELECT * FROM `composant` WHERE `id` = 2 ;
    • SELECT * FROM `composant` WHERE `cout` > 10 ;
    • SELECT * FROM `produit` JOIN `nomenclature` ON `id` = `id_produit` WHERE `nombre` > 1 ;
  4. En vous appuyant sur les cinq requêtes SELECT que vous venez de faire, et en vous appuyant sur le cours (chapitre 6), essayez maintenant d'écrire les requêtes pour répondre aux questions suivantes :
Question 6.1

Lister les libellés de tous les produits.

Cliquez pour apercevoir la solution.

Question 6.2

Récupérer le libellé du produit № 2.

Cliquez pour apercevoir la solution.

Question 6.3

Récupérer le libellé et le coût du composant № 3.

Cliquez pour apercevoir la solution.

Question 6.4

Lister les libellés des composants dont le coût est supérieur à 15 et inférieur ou égal à 45.

Cliquez pour apercevoir la solution.

Question 6.5

Récupérer le coût du composant le plus cher.

Cliquez pour apercevoir la solution.

Si vous avez terminé, vous pouvez essayer de commencer à traiter le TP suivant : TP2.