Guillaume Rivière 2016 – 2017

Le logo de la CCI Bayonne Pays Basque

Systèmes de Gestion de Bases de Données

TP2 : Écrire les requêtes basiques

BUTS PÉDAGOGIQUES

  • Écrire des requêtes de création de bases de données
  • Écrire des requêtes de création de tables
  • Écrire des requêtes d'insertion dans les tables
  • Écrire des requêtes de consultation des tables

Lors du TP1, nous avons utilisé l'interface phpMyAdmin et ses formulaires de création de bases de données. Dans ce TP, les formulaires ne seront pas utilisés. Nous manipulerons la base de données uniquement par envoi de requêtes, comme le ferait un programme informatique. Pour écrire ces requêtes, nous utiliserons la fenêtre de requêtes :

Capture du formulaire de saisie
Figure 0.1 : Formulaire de requêtes SQL dans phpMyAdmin.

Le contexte du cas traité dans ce TP est l'internet des objets (Internet of Things, IoT). Dans ce contexte, nous souhaitons modéliser une plateforme de gestion de services. Des utilisateurs, qui possèdent des objets connectés, souscrivent à des services. Lorsque les objets envoient des données, elles sont transmises aux services souscrits par leur propriétaire. Pour savoir à qui les objets connectés appartiennent et quels sont les services souscrits par leur propriétaire, nous utilisons une base de données.

Les cardinalités entre les entités sont les suivantes :

Le schéma Entité-Association (Modèle Conceptuel des Données, MCD) de cette base de données est le suivant :

Figure 0.2 : Schéma Entité-Association (MCD).

La traduction en schéma relationnel (Modèle Logique des Données, MLD) donne les quatre tables suivantes :

Les quatre tables de la base de données avec les trois liens des clés étrangères
Figure 0.3 : Schéma relationnel (MLD).

Rappel : Comme au début du TP1, il faut d'abord exécuter WAMP pour démarrer les serveurs HTTP et MySQL avant de commencer à travailler.

Exercice 1 • Requêtes de création

L'objectif est de créer la nouvelle base de données platform_iot et ses quatre tables user, thing, service et subscribe

Ouvrir la fenêtre de requêtes avec l'onglet SQL puis cocher « Conserver la boîte de requête ».

Question 1.1 : Créer une nouvelle base de données

Avec la requête suivante, créez la base de données platform_iot :

CREATE DATABASE IF NOT EXISTS `platform_iot` ;

  1. Exécutez la requête.
    • Il faut cliquer sur le bouton Exécuter pour envoyer la requête au SGBD.
    • La réponse du SGBD sera observable dans la fenêtre principale de phpMyAdmin.
      En cas d'erreur, un message sera affiché avec une explication :

      Début du message d'erreur
  2. À gauche de la fenêtre principale de phpMyAdmin, vérifiez que la nouvelle base de données apparaît dans l'arborescence.

Consigne : avec l'éditeur de texte Notepad++, copiez toutes vos requêtes CREATE dans un fichier que vous nommerez iot_create.sql

Question 1.2 : Créer une nouvelle table

Nous allons créer la table user dans la base de données platform_iot.

Les quatre champs de la table user
Figure 1.2.1 : Structure de la table user.

Avec la requête suivante, créez la table user :

CREATE TABLE IF NOT EXISTS `platform_iot`.`user` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `firstname` VARCHAR(100) NOT NULL,
  `lastname` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE = INNODB ;

  1. Exécutez la requête.
  2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale de phpMyAdmin.
  3. Depuis la fenêtre principale de phpMyAdmin, vérifiez la structure de cette nouvelle table user.

Consigne : n'oubliez pas de copier votre requête CREATE dans le fichier iot_create.sql

Pour le nom de la table, il est possible d'éviter d'écrire platform_iot.user et d'écrire directement user, mais à condition d'avoir exécuté au préalable la requête suivante :
USE `platform_iot` ;

Question 1.3 : Créer la table service

De même, écrivez la requête pour créer la table service dans la base de données platform_iot.

Les trois champs de la table service
Figure 1.3.1 : Structure de la table service.
  1. Exécutez la requête.
  2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
  3. Depuis la fenêtre principale, vérifiez la structure de cette nouvelle table service.

Consigne : ajoutez votre requête CREATE au fichier iot_create.sql

Question 1.4 : Créer la table thing

En tenant compte des remarques ci-dessous, écrivez la requête pour créer la table thing dans la base de données platform_iot. Remarques :

Les quatre champs de la table thing
Figure 1.4.1 : Structure de la table thing.
  1. Exécutez la requête.
  2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
  3. Depuis la fenêtre principale de phpMyAdmin, vérifiez la structure de cette nouvelle table thing.

Consigne : ajoutez votre requête CREATE au fichier iot_create.sql

Question 1.5 : Créer la table subscribe

De même, écrivez la requête pour créer la table subscribe dans la base de données platform_iot. Remarques :

Les deux champs de la table subscribe
Figure 1.5.1 : Structure de la table subscribe.
  1. Exécutez la requête.
  2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
  3. Depuis la fenêtre principale de phpMyAdmin, vérifiez la structure de cette nouvelle table subscribe.

Consigne : ajoutez votre requête CREATE au le fichier iot_create.sql

Question 1.6 : Visualiser avec le concepteur

Vous avez créé une nouvelle base, quatre tables et trois relations de clés étrangères. Visualisez la structure résultante avec la vue « concepteur » :

  1. Dans l'arborescence à gauche de la page principale de phpMyAdmin, cliquez sur la base de données platform_iot
  2. Puis cliquez sur l'onglet Concepteur (ou retrouvez-le en déroulant l'onglet « plus »)
  3. Vérifiez que les quatre tables, leurs champs et les relations sont corrects
Les quatre table reliées par les relations des trois clés étrangères
Figure 1.6.1 : Capture de la vue « concepteur » dans phpMyAdmin.

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 • Requêtes d'insertion

La structure de la base de données est en place. L'objectif est maintenant de peupler la base avec des données.

Consigne : toujours avec l'éditeur de texte Notepad++, copiez toutes vos requêtes INSERT dans un nouveau fichier que vous nommerez iot_feed.sql

Question 2.1 : Les utilisateurs

a) Pour insérer un premier utilisateur :

Tableau 2.1.1
firstnamelastnameemail
AmayaURSUYAamaya@domain.com
  1. Exécutez la requête INSERT suivante :
    INSERT INTO `platform_iot`.`user` (`firstname`, `lastname`, `email`) VALUES ('Amaya', 'URSUYA', 'amaya@domain.com') ;
  2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
  3. Vérifier qu'une nouvelle ligne a bien été ajoutée à la table user en exécutant la requête suivante :
    SELECT * FROM `platform_iot`.`user` ;
  4. Quelle valeur a été attribuée pour le id ?

b) Pour insérer encore huit autres utilisateurs :

Tableau 2.1.2
firstnamelastnameemail
MaialenHOLZARTEm.holzarte@company.fr
PantxikaCENITZpantxi@sjdl.eus
MaylisACOTZmayli40@sjdl.eus
ElorriIBARDINEelo.iba@lepoa.fr
MaikaIROULEGUYmaika@irouleguy.eu
MaiderJAIZKIBELmai64@olatu.fr
ArantxaIPARLAa.iparla@mendia.fr
MayanaURCURAYm.urcuray@mendia.fr
  1. Exécutez la requête INSERT suivante :
    INSERT INTO `platform_iot`.`user` (`firstname`, `lastname`, `email`) VALUES
    ('Maialen', 'HOLZARTE', 'm.holzarte@company.fr'),
    ('Pantxika', 'CENITZ', 'pantxi@sjdl.eus'),
    ('Maylis', 'ACOTZ', 'mayli40@sjdl.eus'),
    ('Elorri', 'IBARDINE', 'elo.iba@lepoa.fr'),
    ('Maika', 'IROULEGUY', 'maika@irouleguy.eu'),
    ('Maider', 'JAIZKIBEL', 'mai64@olatu.fr'),
    ('Arantxa', 'IPARLA', 'a.iparla@mendia.fr'),
    ('Mayana', 'URCURAY', 'm.urcuray@mendia.fr') ;
  2. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
  3. Vérifier qu'une nouvelle ligne a bien été ajoutée à la table user en exécutant la requête suivante :
    SELECT * FROM `platform_iot`.`user` ;
  4. Quelles valeurs ont été attribuées pour les id ?

Consigne : n'oubliez pas d'ajoutez la requête INSERT au fichier iot_feed.sql

Question 2.2 : Les services

Utilisez une requête INSERT pour insérer les six services suivants :

Tableau 2.2.3
nametype
myKWHomesmarthome
FridgAlertsmarthome
RUNstatsquantifiedself
traCAREquantifiedself
dogWATCHNULL
CarUseNULL

Vérifier que six nouvelles lignes ont bien été ajoutées à la table service en exécutant la requête suivante :

SELECT * FROM `platform_iot`.`service` ;

Depuis la fenêtre principale de phpMyAdmin, vérifiez ce qui a été inséré en affichant les lignes de la table service.

Quelles valeurs ont été attribuées pour les id ?

Consigne : ajoutez la requête INSERT au fichier iot_feed.sql

Question 2.3 : Les objets connectés

Utilisez une requête INSERT pour insérer les objets connectés suivants :

Tableau 2.3.4
macid_usertypeparam
f0:de:f1:39:7f:171NULLNULL
f0:de:f1:39:7f:182NULLNULL
f0:de:f1:39:7f:192thingtempo60

Depuis la fenêtre principale de phpMyAdmin, vérifiez ce qui a été inséré en affichant les lignes de la table thing.

Consigne : ajoutez la requête INSERT au fichier iot_feed.sql

Question 2.4 : Les souscriptions

Utilisez une requête INSERT pour insérer les souscriptions suivantes :

Tableau 2.4.5
id_userid_service
21
22
13

Depuis la fenêtre principale de phpMyAdmin, vérifiez ce qui a été inséré en affichant les lignes de la table subscribe.

Consigne : ajoutez la requête INSERT au fichier iot_feed.sql

Exercice 3 • Requêtes de consultation

L'objectif est de définir un ensemble de requêtes utiles exploiter la base de données et extraire de l'information.

Consigne : toujours avec l'éditeur de texte Notepad++, copiez toutes vos requêtes SELECT dans un nouveau fichier que vous nommerez iot_select.sql

Question 3.1 : Choisir les colonnes

Utilisez trois requêtes SELECT pour consulter les informations suivantes :

  1. Lister les noms des services
  2. Lister les prénoms et les noms des utilisateurs
  3. Lister les adresses e-mails des utilisateurs
Question 3.2 : La clause WHERE

Utilisez quatre requêtes SELECT avec la clause WHERE pour consulter les informations suivantes :

  1. Lister l'utilisateur avec le id de valeur 5
  2. Lister les utilisateurs avec le nom 'IPARLA'
  3. Lister les adresses MAC des objets connectés que possède l'utilisateur qui a le id de valeur 2
  4. Lister les id des utilisateurs qui possèdent des objets connectés
    • Si des id apparaissent en doublons, faîtes en sorte que chaque id n'apparaissent qu'une fois
Question 3.3 : Combinaison avec AND et OR

Utilisez trois requêtes SELECT avec la clause WHERE pour consulter les informations suivantes :

  1. Lister les services de type 'quantifiedself' ou 'smarthome'
  2. Lister les prénoms des utilisateurs avec les noms 'IPARLA' ou 'CENITZ'
  3. Lister les adresses MAC des objets de type 'thingtempo' de l'utilisateur qui a le id de valeur 2
Question 3.4 : Trier les résultats avec ASC et DESC

Utilisez trois requêtes SELECT avec le tri ORDER BY pour consulter les informations suivantes :

  1. Lister les utilisateurs par ordre croissant de nom
  2. Lister les utilisateurs par ordre décroissant de id
  3. Lister le type et le nom des services par ordre croissant de type, puis par ordre croissant de nom
Question 3.5 : Inclusion avec IN et NOT IN

Nous allons utiliser trois requêtes SELECT avec l'opérateur d'appartenance IN pour rechercher les id dans une autre requête :

  1. Pour lister les noms des services qui ont été souscrits par au moins un utilisateur :
    1. Commençons par construire une première requête qui liste les id des services qui ont été souscris :
      SELECT DISTINCT `id_service` FROM `platform_iot`.`subscribe` ;
    2. Puis, listons les types et noms des services dont le id apparaît dans le résultat de la première requête :
      SELECT `type`, `name` FROM `platform_iot`.`service` WHERE `id` IN (SELECT DISTINCT `id_service` FROM `platform_iot`.`subscribe`) ;
  2. Pour lister les id et les noms des utilisateurs qui ne possèdent pas d'objets :
    1. Commençons par construire une première requête qui liste les id des utilisateurs qui possèdent des objets :
      SELECT DISTINCT `id_user` FROM `platform_iot`.`thing` ;
    2. Puis, listons les id et les noms des utilisateurs dont le id n'apparaît pas dans le résultat de la première requête :
      SELECT `id`, `lastname` FROM `platform_iot`.`user` WHERE `id` NOT IN (SELECT DISTINCT `id_user` FROM `platform_iot`.`thing`) ;
  3. Pour lister les id et les noms des utilisateurs qui possèdent des objets :
    SELECT `id`, `lastname` FROM `platform_iot`.`user` WHERE `id` IN (SELECT DISTINCT `id_user` FROM `platform_iot`.`thing`) ;

Exercice 4 • Requêtes de modification

L'objectif est de modifier des données déjà insérées dans la base de données, voire de modifier la structure de la base de données.

Question 4.1 : Modifier une ligne

Pour chacune des trois requêtes à suivre  :

  1. Lisez et comprenez la requête.
  2. Observez les lignes de la table concernée.
  3. Exécutez la requête.
  4. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
  5. Vérifiez les modifications en affichant les lignes de la table concernée.

Les trois requêtes UPDATE suivantes permettent de modifier des lignes (enregistrements, tuples) :

  1. Mettre la valeur NULL pour toute la colonne type de la table service :
    UPDATE `platform_iot`.`service` SET `type` = NULL ;
  2. Mettre le nom 'WatchDOG2' pour la ligne qui a le id de valeur 5 :
    UPDATE `platform_iot`.`service` SET `name` = 'dogWATCH2' WHERE `id` = 5 ;
  3. Multipler par une fois et demi les valeurs de la colonne param des objets connectés de type 'thingtempo' de la table thing :
    UPDATE `platform_iot`.`thing` SET `param` = `param` * 1.5 WHERE `type` = 'thingtempo' ;
Question 4.2 : Modifier la structure des tables

Pour chacune des trois requêtes à suivre  :

  1. Lisez et comprenez la requête.
  2. Observez la structure de la table concernée.
  3. Exécutez la requête.
  4. Vérifiez qu'il n'y a pas de message d'erreur dans la fenêtre principale.
  5. Depuis la fenêtre principale de phpMyAdmin, vérifiez la nouvelle structure.

Les trois requêtes ALTER TABLE suivantes permettent de modifier les schémas des tables :

  1. Modifier le type de donnée du champ email de la table user pour pouvoir contenir 200 caractères :
    ALTER TABLE `platform_iot`.`user` MODIFY `email` VARCHAR (200) ;
  2. Changer le nom du champ param de la table thing pour devenir parameter :
    ALTER TABLE `platform_iot`.`thing` CHANGE `param` `parameter` VARCHAR (100) ;
  3. Ajouter un nouveau champ password à la table user (le type de donnée sera DATE et la valeur devra obligatoirement être renseignée) :
    ALTER TABLE `platform_iot`.`user` ADD `password` VARCHAR (100) NOT NULL ;

Pour la dernière requête, quelles valeurs ont été attribuées pour les enregistrements déjà présents ? Donnez une date de naissance à Amaya en cliquant sur Modifier.

Exercice 5 • Requêtes de suppression

L'objectif est de supprimer des lignes, des tables, voire la base de données elle-même !

Question 5.1 : Supprimer une ligne

Utilisez trois requêtes DELETE pour supprimer des lignes dans les tables :

  1. Supprimer l'utilisateur avec le id de valeur 1.
    • Exécutez la requête suivante :
      DELETE FROM `platform_iot`.`user` WHERE `id` = 1 ;
    • Vérifiez que la ligne a bien été effacée de la table.
  2. Supprimer la souscription, de l'utilisateur de id de valeur 2, au service de id de valeur 1.
    • Exécutez la requêtesuivante :
      DELETE FROM `platform_iot`.`subscribe` WHERE `id_user` = 1 AND `id_service` = 3 ;
    • Vérifiez que la ligne a bien été effacée de la table.
  3. Supprimer toutes les lignes de la table thing
    • Exécutez la requêtesuivante :
      DELETE FROM `platform_iot`.`thing` ;
    • Vérifiez que les lignes ont bien été effacées de la table.
Question 5.2 : Supprimer une colonne ou une table
  1. Utiliser une requête ALTER TABLE pour supprimer la colonne birthday (ou la colonne password selon ce que vous aviez créé) du schéma de la table user.
    • Exécutez la requête suivante :
      ALTER TABLE `platform_iot`.`user` DROP `birthday` ;
      ou bien la requête suivante :
      ALTER TABLE `platform_iot`.`user` DROP `password` ;
    • Vérifiez que la colonne a disparu du la structure de la table user.
  2. Utiliser une requête DROP pour supprimer la table thing.
    • Exécutez la requête suivante :
      DROP TABLE `platform_iot`.`thing` ;
    • Cliquez sur la base platform_iot à gauche de la fenêtre principale de phpMyAdmin
    • Vérifiez que de la table thing a bien été détruite.
  3. Utiliser une requête DROP pour supprimer la table subscribe.
    • Exécutez la requête suivante :
      DROP TABLE `platform_iot`.`subscribe` ;
    • Cliquez sur la base platform_iot à gauche de la fenêtre principale de phpMyAdmin
    • Vérifiez que de la table subscribe a bien été détruite.
Question 5.3 : Supprimer la base de données platform_iot

Supprimez la base de données avec la requête suivante :

DROP DATABASE `platform_iot` ;

Question 5.4 : Mince alors !

Zut, tout a été effacé ! Vérifiez qu'il n'y plus trace de la base de données.

Comment remettre en place la base de données alors que nous avons tout effacé sans avoir sauvegardé au préalable ?

  1. Cliquez sur Accueil sous le logo phpMyAdmin en haut à gauche.
  2. Puis, ouvrez l'onglet Importer (si jamais il n'apparaît pas, déroulez avec le menu « plus »).
  3. Utilisez le bouton Parcourir (ou Browse) pour sélectionner sur votre disque le fichier iot_create.sql que vous avez créé lors de l'exercice 1.
  4. Cliquez sur le bouton Exécuter en bas du formulaire

Vérifiez que la base a bien été créé, que les tables sont en place, que leurs schémas sont corrects et surtout que les tables sont vides ! Comment remettre en place les données ?

  1. De nouveau, cliquez sur Accueil sous le logo phpMyAdmin en haut à gauche.
  2. Puis, ouvrez l'onglet Importer (si jamais il n'apparaît pas, déroulez avec le menu « plus »).
  3. Utilisez le bouton Parcourir (ou Browse) pour sélectionner sur votre disque le fichier iot_feed.sql que vous avez créé lors de l'exercice 2.
  4. Cliquez sur le bouton Exécuter en bas du formulaire

Pour vérifier que tout est en place, testez une ou deux de vos requêtes SELECT de l'exercice 3.