TP SQL - Base de données E-Commerce

Projet de Travaux Pratiques SQL avec une base de données ecommerce complète pour les exercices d'administration, de requêtage et d'optimisation.

Description

Ce projet contient une base de données relationnelle ecommerce avec 4 tables principales :

• clients - Informations sur les clients
• produits - Catalogue des produits
• commandes - Historique des commandes
• lignes_commande - Détails des lignes de commande

La base inclut également des données de test et des exercices d'administration PostgreSQL (rôles, privilèges, index, contraintes, sauvegardes).

---

Prérequis

• PostgreSQL 14+ (recommandé : PostgreSQL 16)
• pg_dump et pg_restore (inclus avec PostgreSQL)
• psql (client PostgreSQL en ligne de commande)
• Git Bash ou Terminal (pour les commandes)

---

Installation de PostgreSQL

Sur Windows

1. Télécharger PostgreSQL depuis https://www.postgresql.org/download/windows/
2. Exécuter l'installateur et suivre les étapes
3. Mémoriser le mot de passe de l'utilisateur postgres (superutilisateur par défaut)
4. Ajouter PostgreSQL au PATH :
   • Pendant l'installation, cocher "Add application stack builder to the start menu"
   • Vérifier que psql, pg_dump et pg_restore sont accessibles en ligne de commande

Sur macOS

# Installer via Homebrew
brew install postgresql
brew services start postgresql

Sur Linux (Ubuntu/Debian)

# Installer PostgreSQL
sudo apt update
sudo apt install postgresql postgresql-contrib

# Démarrer le service
sudo service postgresql start

---

Vérification de l'installation

# Vérifier la version de PostgreSQL
psql --version

# Vérifier que pg_dump et pg_restore sont disponibles
pg_dump --version
pg_restore --version

---

Configuration initiale

Se connecter à PostgreSQL

# Se connecter en tant qu'utilisateur postgres (superutilisateur)
psql -U postgres

Vérifier les bases de données existantes

-- Dans psql, lister les bases de données
\l

-- Quitter psql
\q

---

Chargement du Dataset

Vous avez 3 méthodes pour charger la base de données. Choisissez celle qui correspond à votre besoin.

Méthode 1 : Utiliser le script SQL (Recommandé pour la simplicité)

Le fichier create_database.sql crée la base ecommerce avec toutes les tables et données.

# Créer la base de données et charger le schéma + données
psql -U postgres -f create_database.sql

Avantages : Simple, lisible, facile à modifier.

---

Méthode 2 : Utiliser le dump SQL standard

Le fichier dump/backup_ecommerce.sql est un dump PostgreSQL complet.

# Créer la base de données
createdb -U postgres ecommerce

# Charger le dump SQL
psql -U postgres -d ecommerce -f dump/backup_ecommerce.sql

Avantages : Contient tous les objets (séquences, contraintes, etc.) dans un format lisible.

---

Méthode 3 : Utiliser le dump compressé (Format Directory)

Le dossier dump/backup_ecommerce.dump/ contient un dump au format directory (compressé).

# Créer la base de données
createdb -U postgres ecommerce

# Restaurer le dump compressé
pg_restore -U postgres -d ecommerce -F d dump/backup_ecommerce.dump/

Avantages : Plus compact, restauration plus rapide pour les grandes bases.

---

Vérification du chargement

Une fois la base chargée, vérifiez que tout est en place :

# Se connecter à la base ecommerce
psql -U postgres -d ecommerce

-- Lister les tables
\dt

-- Compter les enregistrements dans chaque table
SELECT 
    (SELECT COUNT(*) FROM clients) AS nb_clients,
    (SELECT COUNT(*) FROM produits) AS nb_produits,
    (SELECT COUNT(*) FROM commandes) AS nb_commandes,
    (SELECT COUNT(*) FROM lignes_commande) AS nb_lignes_commande;

-- Voir la structure d'une table
\d clients

-- Quitter psql
\q

Résultats attendus :

• 20 clients
• 25 produits
• 30 commandes
• 48 lignes de commande (30 si create_database.sql)

---

Structure de la Base de Données

Tables

Table	Description	Colonnes principales
clients	Informations clients	client_id, nom, email, ville, pays, date_inscription
produits	Catalogue produits	produit_id, nom, categorie, prix, stock
commandes	Historique commandes	commande_id, client_id, date_commande, statut, total
lignes_commande	Détails des commandes	ligne_id, commande_id, produit_id, quantite, prix_unitaire

Schéma des relations

clients (1) ----< (N) commandes
commandes (1) ----< (N) lignes_commande
produits (1) ----< (N) lignes_commande

---

Catégories de Produits

• Électronique : Smartphone, Casque Bluetooth, Chargeur, etc.
• Informatique : Laptop Stand, Souris, Clavier, Webcam, Hub USB
• Vêtements : T-shirt, Jean, Veste, Baskets, Chaussettes
• Livres : Python avancé, SQL pour débutants, Data Engineering
• Maison : Cafetière, Gourde, Lampe, Coussin, Tapis

---

Exercices Disponibles

Fichiers d'exercices

Fichier	Thème	Nombre de questions
exercices_administration.sql	Administration PostgreSQL	32 questions
rpierron_chapitre1.sql	Chapitre 1 - Requêtes de base	-
rpierron_chapitre2.sql	Chapitre 2 - Jointures	-
rpierron_chapitre3.sql	Chapitre 3 - Fonctions d'agrégation	-
rpierron_chapitre4.sql	Chapitre 4 - Sous-requêtes	-
exercices_chapitre6_window_functions.sql	Fonctions de fenêtre	-

Commandes Utiles pour PostgreSQL

Créer une nouvelle base de données

createdb -U postgres nom_de_la_base

Supprimer une base de données

# Se déconnecter de la base avant suppression
dropdb -U postgres nom_de_la_base

Exporter une base de données

# Export en SQL lisible
pg_dump -U postgres -d nom_de_la_base -f backup.sql

# Export compressé (format directory)
pg_dump -U postgres -d nom_de_la_base -Z gzip -F d -f backup.dump/

Restaurer une base de données

# Depuis un dump SQL
psql -U postgres -d nom_de_la_base -f backup.sql

# Depuis un dump compressé
pg_restore -U postgres -d nom_de_la_base -F d backup.dump/

---

Résolution des Problèmes

Erreur : "psql : command not found"

Solution : PostgreSQL n'est pas dans votre PATH.

• Windows : Ajouter C:\Program Files\PostgreSQL\16\bin à votre PATH
• macOS : export PATH="/opt/homebrew/opt/postgresql@16/bin:$PATH"
• Linux : export PATH="/usr/lib/postgresql/16/bin:$PATH"

Erreur : "role postgres does not exist"

Solution : Utiliser le nom d'utilisateur correct.

# Lister les utilisateurs PostgreSQL
psql -l

# Se connecter avec un autre utilisateur
psql -U votre_utilisateur

Erreur : "database ecommerce already exists"

Solution : Supprimer la base existante avant de la recréer.

dropdb -U postgres ecommerce
createdb -U postgres ecommerce

Erreur de mot de passe

Solution : Spécifier le mot de passe en variable d'environnement.

# Windows (PowerShell)
$env:PGPASSWORD="votre_mot_de_passe"

# Windows (CMD)
set PGPASSWORD=votre_mot_de_passe

# Linux/macOS
export PGPASSWORD="votre_mot_de_passe"

---

Bonnes Pratiques

1. Toujours sauvegarder avant de modifier la structure
2. Tester les requêtes dans une base de test avant la production
3. Utiliser des transactions pour les modifications importantes
4. Documenter vos requêtes et modifications
5. Vérifier les index avant d'optimiser les requêtes

---

Contenu du Répertoire

SQL/
├── README.md                    # Ce fichier
├── create_database.sql         # Script de création de la base
├── exercices_administration.sql # Exercices d'administration
├── exercices_chapitre6_window_functions.sql
├── rpierron_chapitre1.sql
├── rpierron_chapitre2.sql
├── rpierron_chapitre3.sql
├── rpierron_chapitre4.sql
├── bonus_chapitre4_5_questions.txt
├── exercices-administration-6a5f858081e73853525567.txt
├── schema-des-tables-exercice-6a5f85689d2ee347457002.txt
├── schema-relationnel-6a5f856d8b191804737709.png
└── dump/
    ├── backup_ecommerce.dump/   # Dump compressé (format directory)
    │   ├── toc.dat
    │   ├── 4947.dat.gz
    │   ├── 4949.dat.gz
    │   ├── 4951.dat.gz
    │   └── 4953.dat.gz
    └── backup_ecommerce.sql     # Dump SQL lisible

---

Licence

Ce projet est à usage éducatif. Les données sont fictives et générées pour les besoins des TP.

---

Dernière mise à jour : 29 juillet 2026