Vous êtes un nouvel employé de la société DVD Rental, une entreprise florissante spécialisée dans la location de films (une entreprise de location de DVD était un truc hyperbranché avant 2010 !). En tant qu'analyste de données, votre mission est de tirer le meilleur parti de la base de données de l’entreprise pour effectuer des analyses avancées qui guideront les décisions stratégiques de l’équipe de direction.
DVD Rental vous donne accès à l’intégralité de sa base de données, vous offrant ainsi l'opportunité d'explorer les informations sur les clients, les films, les locations et bien plus encore.
Votre objectif ?
Extraire des insights précieux, répondre aux problématiques de l’entreprise, et améliorer le fonctionnement de la base.
Au cours de votre parcours, vous serez régulièrement en contact avec deux personnages clés :
Sofia, la CEO de DVD Rental, qui compte sur vous pour répondre à des questions stratégiques : quels sont les films les plus loués ? Quel est le profil de nos clients les plus fidèles ?
Rodolphe, le DBA (Database Administrator), qui vous demandera d’optimiser la base de données, de garantir sa performance et de mettre en place des améliorations pour mieux gérer les données volumineuses.
À chaque étape, vous devrez utiliser vos compétences en SQL et PostgreSQL pour accomplir ces tâches et fournir des réponses concrètes aux besoins de l’entreprise. Préparez-vous à relever des défis concrets tout en maîtrisant des concepts SQL avancés !
Découvrez votre environnement SQL
Avant toute chose, vous allez commencer par installer votre environnement SQL.
Un environnement SQL ça comporte quoi ?
L'environnement SQL est l'ensemble des outils, interfaces et configurations qui permettent de créer, gérer, interroger et manipuler des bases de données relationnelles. Comprendre cet environnement est crucial pour bien utiliser PostgreSQL ou tout autre système de gestion de bases de données (SGBD). Il se compose généralement :
d’un SGBD qui gère l'accès, la manipulation, et le stockage des données (PostgreSQL ou MySQL par exemple) ;
d’une interface de commande (CLI) : comme psql pour PostgreSQL, qui permet d'exécuter des commandes SQL directement sur le terminal ;
d’interfaces graphiques (GUI) comme pgAdmin, DBeaver, qui offrent une approche plus visuelle pour interagir avec les bases de données ;
de bibliothèques et APIs qui permettent de connecter des applications à la base de données via du code (ex: JDBC, ODBC, psycopg2 pour Python…)
Et voilà ce que cela nous donne de manière illustrée :
Avant de pouvoir se lancer, il est nécessaire d'installer deux éléments : le système de gestion de base de données (PostgreSQL) et les outils pour interagir avec (dans notre cas pgAdmin ou psql).
Installez Postgresql
Avant de pouvoir utiliser PostgreSQL, il faut installer la dernière version sur votre ordinateur. Pour cela, suivez le tutoriel ci-dessous correspondant à votre système d’exploitation (Windows, MacOS ou Linux Ubuntu) ou utilisez Docker Compose.
PostgreSQL pour Windows
Téléchargez le programme d’installation de PostgreSQL en vous rendant à l’adresse suivante et lancez-le.
1/ Setup - PostgreSQL : cliquez sur Next.
2/ Installation Directory : choisissez le dossier d’installation de votre ordinateur, par défaut PostgreSQL s'installe dans le C:\Program Files\PostgreSQL\SQL\16. Je vous conseille de le laisser ici, cliquez sur Next.
3/ Select Components : décochez l’installation de Stack Builder, nous n’en aurons pas besoin, gardez les autres composants et cliquez sur Next.
4/ Data Directory : choisissez le dossier où vos bases de données SQL seront enregistrées, par défaut PostgreSQL les installe dans le C:\Program Files\PostgreSQL\SQL\16\data. Je vous conseille de les laisser ici, cliquez sur Next.
5/ Password : fournissez un mot de passe qui vous sera utile pour vous connecter par la suite à votre SGBD (vous définissez ici le mot de passe du compte super-admin PostgreSQL), cliquez sur Next.
6/ Port : définissez le port sur lequel PostgreSQL émettra, je vous conseille de garder le port par défaut, le 5434. Cliquez sur Next.
7/ Advanced Options : gardez la valeur par défaut pour la base de données locale et cliquez sur Next.
Lancez l’installation PostgreSQL.
Félicitations, vous venez d’installer PostgreSQL, ainsi que la CLI psql et l’interface graphique pgAdmin, qui sont inclus dans votre installation.
PostgreSQL pour Mac
Pour installer PostgreSQL sur Mac, vous avez deux options :
Téléchargez le programme d’installation de PostgreSQL en vous rendant à l’adresse suivante. Lancez l’exécution et suivez les mêmes étapes que pour l’installation Windows.
Utilisez le package installer Homebrew pour PostgreSQL et pgAdmin.
Pour installer les différents outils via le package installer Homebrew, exécutez les installations suivantes :
Mettez à jour Homebrew.
brew update
Installez PostgreSQL (l’exécutable psql sera installé avec automatiquement).
brew install postgresql@16
Installez pgAdmin.
brew install --cask pgadmin4
Félicitations, vous venez d’installer PostgreSQL, incluant la CLI psql, et l’interface graphique pgAdmin.
Paramétrez votre environnement SQL
Maintenant que tout est installé, il est temps de vérifier la disponibilité de notre nouveau SGBD en s’y connectant.
Pour ce faire nous allons utiliser les deux outils présentés précédemment, psql et pgAdmin.
Connexion par psql
Ouvrez un terminal de commande (peu importe votre système d’exploitation) et lancez la commande suivante :
psql -U postgres
La commande vous invite à saisir le mot de passe de l'utilisateur postgres (que vous avez renseigné lors de l’installation) :
Password for user postgres:
Après avoir saisi le mot de passe correctement, vous serez connecté au serveur PostgreSQL. L'invite de commande se transforme en quelque chose comme ceci :
postgres=#
Lorsque vous vous connectez au serveur PostgreSQL, vous démarrez une session. Cette session, d'une durée limitée, vous permet d'exécuter diverses requêtes, comme l'exécution de commandes, avant de vous déconnecter.
Pour afficher la base de données actuelle, vous pouvez utiliser la commande suivante :
postgres=# SELECT current_database();
Vous devriez voir l’affichage suivant :
Connexion par pgAdmin
Une autre façon, peut-être plus agréable, de vous connecter à PostgreSQL est d’utiliser l’interface graphique pgAdmin. Commencez par ouvrir votre navigateur et connectez-vous au http://localhost:8080, vous devriez découvrir l’interface ci-dessous :
Une fois sur l’interface, vous pouvez vous connecter au serveur PostgreSQL :
1/ Faites un clic droit sur l’onglet Servers, puis sélectionnez Register, puis Server.
2/ Dans l’onglet General, mettez le nom que vous souhaitez à votre connexion (par exemple dvdrental_server).
3/ Dans l’onglet Connection, remplissez les informations suivantes :
Pour Windows / Mac / Linux :
Host name / address: localhost
Port: 5432 (si vous n’avez pas changé la valeur par défaut)
Maintenance database: postgres
Username: root
Password: celui choisi par vos soins lors de l’installation
Votre SGBD est désormais connecté, vous devriez voir apparaître votre serveur dvdrental_server dans la rubrique Server, tel que précisé ci-dessous.
Découvrez la base de données du cours
Maintenant que votre environnement est prêt, vous recevez un message de Rodolphe, le DBA de DVD Rental. Il vous annonce qu’il a mis à votre disposition une copie de la base de données complète de l’entreprise.
Cette base contient toutes les informations dont vous aurez besoin pour vos analyses : les films disponibles à la location, les clients, les transactions passées, ainsi que les détails de chaque emprunt.
Pour mener à bien vos missions au sein de DVD Rental, vous allez utiliser cette base de données tout au long du cours. Il s'agit de la célèbre base de données DVD Rental, disponible sur le site PostgreSQL Tutorial, que l’entreprise utilise pour gérer ses opérations.
Votre première tâche ? Installer cette base de données dans votre environnement. Cela vous permettra de commencer à explorer les données et de répondre aux questions de la CEO et du DBA, qui comptent sur vous pour améliorer la performance de l’entreprise grâce à vos analyses.
Cette base modélise les opérations commerciales d'un magasin de location de DVD. Elle comprend divers objets, tels que : 15 tables, 1 trigger, 7 vues, 8 fonctions, 1 domaine et 13 séquences.
Vous pouvez retrouver le diagramme entité relation (ER) ci-dessous :
À vous de jouer
Maintenant que vous avez compris le modèle, il est temps de l’installer sur votre serveur fraîchement créé.
Pour commencer, veillez à télécharger la base de données au format zip à l’adresse suivante.
Le fichier téléchargé est au format zip, vous devez l’extraire afin de récupérer le format .tar qui sera utilisé pour la restauration de la base.
Pour l’importer, vous avez deux manières de faire (comme d’ailleurs à chaque fois), via l’interface graphique pgAdmin ou la CLI (psql).
Installer DVD Rental avec psql :
Pour charger la base de données, lancer la commande suivante :
pg_restore -h localhost -U postgres -d dvdrental /path/to/downloads/dvdrental.tar
Les paramètres que vous venez d’utiliser sont les suivants:
-h: hôte de la base de données, ici en local (localhost ou 127.0.0.1).-U: nom de l’utilisateur, c’est via son identité que le chargement sera effectué, ici postgres.-d: nom de la base de données qui apparaîtra sur votre serveur PostgreSQL. Pour le reste du cours nous partirons du principe que cette base s’appelle dvdrental, mais vous pouvez la nommer comme vous le désirez.le dernier paramètre est le chemin du fichier tar à importer.
La commande vous invite à saisir le mot de passe de l'utilisateur postgres, que vous avez renseigné lors de l’installation :
Password for user postgres:
Une fois le script lancé, vous devriez voir apparaître le message sur votre console, indiquant le bon déroulement du chargement.
Installer DVD Rental avec pgAdmin :
Pour charger la base via l’interface, retournez sur votre navigateur à l’adresse http://localhost:8080 et suivez ces étapes:
1/ Cliquez avec le bouton droit sur votre serveur dvdrental_server et sélectionnez Create > Database, pour ouvrir une boîte de dialogue permettant de créer une nouvelle base de données.
2/ Remplissez le champ Database par le nom de la base de données qui apparaîtra sur votre serveur PostgreSQL. Pour le reste du cours nous partirons du principe que cette base s’appelle dvdrental, mais vous pouvez la nommer comme vous le désirez. Laissez la valeur du champ “Owner” à postgres et cliquez sur Save.
3/ Faîtes un clic droit sur la base de données fraîchement créée “dvdrental” et sélectionnez Restore.
4/ Chargez votre fichier tar, préalablement téléchargé et décompressé. Sélectionnez postgres comme Role name. Appuyez sur Restore.
5/ Vous devriez voir apparaître deux pop-up vertes, indiquant que votre chargement s’est bien passé.
Votre base de données est désormais prête à être exploitée.
En résumé
Vous avez installé PostgreSQL et deux outils indispensables pour s’y connecter.
Vous avez configuré psql et pgAdmin pour être capable de communiquer avec PostgreSQL.
Enfin, vous avez restauré la base de données dvdrental, qui sera notre fil rouge pendant tout le cours.
Maintenant que tout est en place, il est temps de jouer avec vos nouvelles données. Dans le prochain chapitre vous allez apprendre à utiliser des fonctions de dates et de tableaux afin de maîtriser tous les types d’objets de votre base.
