Guillaume Rivière 2017 – 2024

Le logo de la CCI Bayonne Pays Basque

Systèmes de Gestion de Bases de Données

TP2 : Écrire des requêtes avancées

BUTS PÉDAGOGIQUES

  • Écrire des requêtes avec jointures
  • Écrire des requêtes avec fonctions
  • Écrire des requêtes avec agrégations

Dans ce TP, nous utilisons une nouvelle base de données et « Plateforme IoT », puis nous revenons sur la base de données « Bureau d'étude » du TP1, afin d'en exploiter les informations. Pour ce faire, nous allons en extraire de l'information par des requêtes SELECT avec jointures, fonctions et agrégations.

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 avancées sur la base platform_iot

Le contexte du cas traité dans ce premier exercice 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 de cette base de données est le suivant :

Figure 1.1 : Schéma Entité-Association.

La traduction en schéma relationnel donne les quatre tables suivantes :

Les quatre tables de la base de données avec les trois liens des clés étrangères
Figure 1.2 : Schéma relationnel.
  1. Téléchargez le fichier platform_iot.sql sur votre disque dur.
    • Ce fichier contient les requêtes de création des tables et des requêtes d'insertion pour « nourrir » la base avec des données de démonstration.
  2. Importez la base de données platform_iot :
    1. Dans l'arborescence, cliquez sur la base de données platform_iot puis ouvrez l'onglet Importer
    2. Avec la fonction parcourir, sélectionnez le fichier platform_iot.sql sur votre disque. Cliquez sur Exécuter
    3. Observez que la base platform_iot est en place : tables, colonnes et lignes
Question 1.1 : Une première requête SELECT avec jointure entre deux tables

Quel est l'adresse email de l'utilisateur qui possède l'objet d'adresse MAC f0:de:f1:39:7f:17 ?

Pour vous aider à écrire la requête, voici quelques questions à se poser :

⇒ nous devons joindre les informations de ces deux tables de manière cohérente (c.-à-d. en respectant la contrainte de la clé étrangère)

Si besoin, commencez par réfléchir sur papier. Si vous restez bloqué et n'arrivez pas à écrire cette requête, vous pourrez cliquer pour apercevoir la solution. Mais, essayez d'abord de le faire par vous-même.

Question 1.2 : Une autre requête SELECT avec jointure entre deux tables

Quels sont les adresses MAC des objets appartenant à l'utilisateur dont l'adresse email est m.holzarte@company.fr ?

Voir le résultat de la requête.

Question 1.3 : Une requête SELECT avec jointure entre trois tables

Affichez les noms et prénoms des utilisateurs avec les noms des services auxquels ils sont abonnés.

Voir le résultat de la requête.

Question 1.4 : Requêtes SELECT avec fonctions

Combien de services sont de type smarthome ?

Voir le résultat de la requête.

Question 1.5 : Requêtes SELECT avec fonctions et agrégations

Afficher les id des propriétaires d'objets avec le nombre d'objets qu'ils possèdent.

Voir le résultat de la requête.

Si vous restez bloqué et n'arrivez pas à écrire cette requête, vous pourrez cliquer pour apercevoir la solution. Mais, essayez d'abord de le faire par vous-même.

Question 1.6 : Requêtes SELECT avec fonctions, jointure entre deux tables et agrégations

Afficher les noms et prénoms des propriétaires d'objets avec le nombre d'objets qu'ils possèdent.

Voir le résultat de la requête.

Question 1.7 : Requêtes SELECT avec fonctions, jointure entre deux tables, agrégations et clause

Afficher les noms et prénoms des propriétaires de (strictement) plus de 1 objet.

Voir le résultat de la requête.

Exercice 2 • Requêtes avancées sur la base bureau_etude

Important : Nous continuons de travailler sur la base de données bureau_etude, mais avec de nouvelles données :

  1. Supprimez votre base bureau_etude créée lors du TP1.
  2. Téléchargez le nouveau fichier bureau_etude.sql sur votre disque dur.
    • Ce fichier contient les requêtes de création des tables et des requêtes d'insertion pour « nourrir » la base avec des données de démonstration.
  3. Importez la nouvelle version de la base bureau_etude :
    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. Dans l'arborescence, cliquez sur la base de données bureau_etude puis ouvrez l'onglet Importer
    4. Avec la fonction parcourir, sélectionnez le fichier bureau_etude.sql sur votre disque. Cliquez sur Exécuter
    5. Observez que la base bureau_etude est en place : tables, colonnes et lignes

Avant d'écrire les sept requêtes à suivre, nous rappelons d'abord le schéma relationnel de cette base :

Les trois tables de la base de données avec les deux liens des clés étrangères
Figure 2.1 : Schéma relationnel de la base de données Bureau d'étude du TP1.
Question 2.1 : Première requête

Quels sont les libellés des produits qui utilisent des vis ?

Voir le résultat de la requête.

Question 2.2 : Deuxième requête

Quels sont les libellés des produits qui utilisent huit vis ?

Voir le résultat de la requête.

Question 2.3 : Troisième requête

Quels sont les libellés des composants d'une chaise ?

Voir le résultat de la requête.

Question 2.4 : Quatrième requête

Afficher le libellé des composants avec le nombre de produits dans lesquels ils sont utilisés.

Voir le résultat de la requête.

Question 2.5 : Cinquième requête

Afficher le libellé des composants qui sont utilisés dans 1 seul produit.

Voir le résultat de la requête.

Question 2.6 : Sixième requête

Afficher le libellé des composants d'un banc, avec la quantité utilisée, le coût unitaire et le coût final.

Voir le résultat de la requête.

Question 2.7 : Septième requête

Afficher le coût total d'un banc.

Voir le résultat de la requête.