TP1 : Créer et peupler des tables

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.

Question 1.1 : L'environnement : démarrer le serveur web et le serveur MySQL (WAMP)

• Pour lancer les serveurs sur votre ordinateur portable, allez dans le menu Windows et écrivez « wamp » pour retrouver WampServer64.
• Une fois cette opération réalisée, une icône blanche doit apparaître en bas à droite de l'écran. Quand le sigle « W » est vert, cela signifie que les serveurs sont démarrés.
• En cas de problème de démarrage du serveur (le sigle reste orange ou rouge), vérifiez qu'aucun programme utilisant le réseau (p. ex. Skype, Tchat, Jeu vidéo, Streaming …), n'est démarré sur votre machine. Puis, faîtes un clic gauche sur le sigle « W » et cliquez sur Restart All Services.

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 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" :

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

• Cinq bases de données existent déjà : information_schema, mysql, performance_schema, phpmyadmin et test.
• Ces bases de données contiennent des informations nécessaires au fonctionnement du SGBD MySQL.
• À moins d'avoir un niveau avancé et de bonnes connaissances en administration MySQL, il est préférable de ne pas modifier ces bases de données ! (sous peine que plus rien ne fonctionne correctement par la suite).

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)

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) =
   • 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 :

      

8. Organisez visuellement cette vue en déplaçant les tables, puis conservez le placement en enregistrant le placement avec le bouton et en lui donnant un nom :

   

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.

1) Dans le répertoire d'installation de phpMyAdmin ouvrir le fichier nommé config.inc.php :
C:\wamp64\apps\phpmyadminX.X.XX\config.inc.php

2) puis ajouter les lignes suivantes juste avant la fin du fichier « ?> » :
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['designer_coords'] = 'pma__designer_coords';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';

3) Enregistrer les modifications. Depuis la racine de l'interface web de phpMyAdmin cliquer sur « Importer » et ouvrir le fichier :
C:\wamp64\apps\phpmyadminX.X.XX\examples\create_tables.sql

4) Redémarrer WAMP. Un nouvel onglet « concepteur » doit maintenant apparaître dans phpMyAdmin.

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) =
   • 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.

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.

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 ?

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.
 

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
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 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) :

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.

SELECT `libelle` FROM `produit` ;

Question 6.2

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

Cliquez pour apercevoir la solution.

SELECT `libelle` FROM `produit` WHERE `id` = 2 ;

Question 6.3

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

Cliquez pour apercevoir la solution.

SELECT `libelle`, `cout` FROM `composant` WHERE `id` = 3 ;

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.

SELECT `libelle` FROM `composant` WHERE `cout` > 15 AND `cout` <= 45 ;

Question 6.5

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

Cliquez pour apercevoir la solution.

SELECT MAX(`cout`) FROM `composant` ;

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