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 :

Un objet appartient à un et un seul utilisateur.
Un utilisateur peut posséder plusieurs objets.
Un utilisateur peut souscrire à plusieurs services.
Un service peut être souscrit par plusieurs utilisateurs.

Le schéma Entité-Association de cette base de données est le suivant :

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.

Chaque objet connecté (décrit par la table thing) est identifié par son adresse réseau physique unique (adresse MAC) sur 17 caractères. Le champ type permet d'indiquer si l'objet est particulier (sinon, le champ n'est pas spécifié). Le champ param permet de fournir des paramètres optionnels concernant l'objet.
Le champ type de la table service permet d'indiquer si le service est particulier (sinon, le champ n'est pas spécifié).

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

Dans quelle table je trouve l'information de « qui possède quel objet » ? réponse : dans la table thing
Dans quelle table je trouve l'information de « l'email » ? réponse : dans la table user

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.

SELECT `email` FROM `platform_iot`.`user` JOIN `platform_iot`.`thing` ON `id_user` = `id` WHERE `mac` = 'f0:de:f1:39:7f:17' ;

Remarque : c'est après le ON que nous mettons en relation la clé étrangère de la deuxième table avec la clé primaire de la première table.

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.

SELECT `id_user`, COUNT(`mac`) FROM `platform_iot`.`thing` GROUP BY `id_user` ;

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 :

Supprimez votre base bureau_etude créée lors du TP1.
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.
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