Introduction aux Bases de Données Relationnelles

Ce module présente les concepts fondamentaux des bases de données relationnelles.

Prérequis

Niveau Compétence
✅ Requis Avoir suivi le module 05_python_data_processing
✅ Requis Comprendre les structures de données (listes, dictionnaires)

Objectifs du module

À la fin de ce notebook, tu seras capable de :

  • Expliquer ce qu’est une base de données
  • Comprendre le modèle relationnel (tables, colonnes, lignes)
  • Définir les clés primaires et étrangères
  • Identifier les types de relations (1-1, 1-N, N-N)
  • Comprendre les principes de normalisation
  • Expliquer les propriétés ACID
  • Différencier OLTP et OLAP
  • Expliquer ce qu’est un Data Warehouse et un Data Mart
  • Comprendre la modélisation dimensionnelle (Star/Snowflake Schema)
  • Distinguer tables de faits et tables de dimensions

💡 Note : Ce module est théorique. La pratique SQL viendra au module suivant !

1. C’est quoi une Base de Données ?

Définition

Une base de données est un système organisé pour :

Fonction Description
💾 Stocker Conserver des informations de façon permanente
🔍 Rechercher Retrouver rapidement n’importe quelle donnée
✏️ Modifier Mettre à jour les informations
🔒 Sécuriser Contrôler l’accès aux données sensibles
🔗 Relier Connecter différentes informations entre elles

En une phrase

“Une base de données, c’est comme un classeur numérique géant, ultra-organisé et intelligent, capable de retrouver n’importe quelle information parmi des milliards de données.”

Fichiers vs Base de données

Pourquoi ne pas simplement utiliser des fichiers CSV ou Excel ?

Critère Fichiers (CSV, Excel) Base de données
Accès concurrent ❌ Conflits si plusieurs utilisateurs ✅ Géré automatiquement
Volume ❌ Lent au-delà de ~100K lignes ✅ Millions/milliards de lignes
Intégrité ❌ Pas de validation ✅ Contraintes, types
Relations ❌ Difficile à gérer ✅ Jointures natives
Sécurité ❌ Tout ou rien ✅ Permissions fines
Sauvegarde ❌ Manuelle ✅ Automatique

2. Le SGBD — Système de Gestion de Base de Données

Un SGBD (ou DBMS en anglais) est le logiciel qui gère la base de données.

┌─────────────────────────────────────────────────────────────┐
│                        APPLICATION                          │
│                  (Python, Java, Web...)                     │
└───────────────────────────┬─────────────────────────────────┘
                            │ Requêtes SQL
                            ▼
┌─────────────────────────────────────────────────────────────┐
│                          SGBD                               │
│              (PostgreSQL, MySQL, Oracle...)                 │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────┐         │
│  │   Parser    │  │  Optimizer  │  │   Engine    │         │
│  │   (SQL)     │  │  (requêtes) │  │  (stockage) │         │
│  └─────────────┘  └─────────────┘  └─────────────┘         │
└───────────────────────────┬─────────────────────────────────┘
                            │
                            ▼
┌─────────────────────────────────────────────────────────────┐
│                     FICHIERS DISQUE                         │
│                   (données stockées)                        │
└─────────────────────────────────────────────────────────────┘

SGBD Relationnels populaires

SGBD Type Points forts Cas d’usage
PostgreSQL Open source Puissant, extensible, SQL avancé Production, analytics
MySQL Open source Simple, rapide, très répandu Web, startups
SQLite Embarqué Léger, fichier unique, zéro config Mobile, prototypage
Oracle Commercial Entreprise, haute disponibilité Banques, grandes entreprises
SQL Server Commercial Intégration Microsoft Entreprises Windows

💡 Pour ce cours, on utilisera PostgreSQL — le plus complet et gratuit.

3. Le Modèle Relationnel

Le modèle relationnel a été inventé par Edgar F. Codd (IBM) en 1970. C’est le modèle le plus utilisé depuis 50 ans !

Vocabulaire de base

BASE DE DONNÉES : ma_boutique
│
├── TABLE : clients
│   │
│   │    COLONNES (attributs)
│   │    ↓      ↓         ↓
│   │  ┌─────┬────────┬─────────────────┐
│   │  │ id  │  nom   │     email       │  ← EN-TÊTE
│   │  ├─────┼────────┼─────────────────┤
│   │  │  1  │ Alice  │ alice@mail.com  │  ← LIGNE (tuple)
│   │  │  2  │ Bob    │ bob@mail.com    │  ← LIGNE (tuple)
│   │  │  3  │ Charlie│ charlie@mail.com│  ← LIGNE (tuple)
│   │  └─────┴────────┴─────────────────┘
│   │    ↑
│   │    CELLULE (valeur)
│
├── TABLE : produits
│   └── ...
│
└── TABLE : commandes
    └── ...

Terminologie

Terme technique Terme courant Description
Relation Table Ensemble de données du même type
Tuple Ligne / Enregistrement Une entrée (ex: un client)
Attribut Colonne / Champ Une propriété (ex: nom, email)
Domaine Type Valeurs possibles (INTEGER, VARCHAR…)
Schéma Structure Définition des colonnes et types

4. Types de données

Chaque colonne a un type qui définit les valeurs autorisées.

Types courants (PostgreSQL)

Catégorie Type Description Exemple
Entiers INTEGER Nombre entier 42
BIGINT Grand entier 9223372036854775807
SMALLINT Petit entier 32767 max
Décimaux DECIMAL(p,s) Précision exacte 19.99
FLOAT Approximatif 3.14159
Texte VARCHAR(n) Texte variable (max n) 'Alice'
TEXT Texte illimité 'Long texte...'
CHAR(n) Texte fixe (n caractères) 'FR'
Booléen BOOLEAN Vrai/Faux TRUE, FALSE
Date/Heure DATE Date seule '2024-01-15'
TIMESTAMP Date + heure '2024-01-15 14:30:00'
TIME Heure seule '14:30:00'
Autres UUID Identifiant unique 'a0eebc99-9c0b...'
JSON Données JSON '{"key": "value"}'

💡 Bonnes pratiques

✅ Faire ❌ Éviter
DECIMAL pour l’argent FLOAT pour l’argent (imprécis)
VARCHAR(255) pour emails TEXT partout (pas de limite)
DATE pour les dates VARCHAR pour les dates
Types les plus petits possibles Types trop grands “au cas où”

5. Clé Primaire (Primary Key)

La clé primaire (PK) identifie de façon unique chaque ligne d’une table.

Règles

Règle Description
Unique Deux lignes ne peuvent pas avoir la même valeur
Non NULL La valeur doit toujours être présente
Immuable Ne devrait jamais changer

Exemple

Table : clients
┌─────────┬──────────┬─────────────────┐
│ id (PK) │   nom    │      email      │
├─────────┼──────────┼─────────────────┤
│    1    │  Alice   │ alice@mail.com  │
│    2    │  Bob     │ bob@mail.com    │
│    3    │  Charlie │ charlie@mail.com│
└─────────┴──────────┴─────────────────┘
     ↑
     Clé primaire : garantit l'unicité

Types de clés primaires

Type Description Exemple
Auto-incrémentée Générée automatiquement (1, 2, 3…) SERIAL en PostgreSQL
UUID Identifiant universel unique a0eebc99-9c0b-4ef8...
Naturelle Donnée existante unique Numéro de sécurité sociale
Composite Plusieurs colonnes combinées (pays, code_postal)

💡 Recommandation : Utiliser SERIAL (auto-increment) ou UUID plutôt qu’une clé naturelle.

6. Clé Étrangère (Foreign Key)

La clé étrangère (FK) crée un lien entre deux tables.

Principe

Table : clients                    Table : commandes
┌─────────┬──────────┐            ┌─────────┬─────────────┬─────────┐
│ id (PK) │   nom    │            │ id (PK) │ client_id(FK)│ produit │
├─────────┼──────────┤            ├─────────┼─────────────┼─────────┤
│    1    │  Alice   │◄───────────│    1    │      1      │ Clavier │
│    2    │  Bob     │◄───────────│    2    │      2      │ Souris  │
│    3    │  Charlie │            │    3    │      1      │ Écran   │
└─────────┴──────────┘            └─────────┴─────────────┴─────────┘
                                              ↑
                                    Clé étrangère : référence clients.id

Ce que garantit la FK

Garantie Description
Intégrité référentielle Impossible de référencer un client inexistant
Cohérence Si on supprime un client, que faire des commandes ?

Actions en cascade

Que se passe-t-il si on supprime ou modifie la ligne référencée ?

Action Comportement
CASCADE Supprime/modifie aussi les lignes liées
SET NULL Met la FK à NULL
SET DEFAULT Met une valeur par défaut
RESTRICT Interdit la suppression/modification
NO ACTION Comme RESTRICT (vérification différée)

7. Types de Relations

1️⃣ Relation Un-à-Un (1:1)

Une ligne dans A correspond à exactement une ligne dans B.

utilisateurs              profils
┌────┬─────────┐         ┌────┬─────────┬───────────┐
│ id │  email  │         │ id │ user_id │   bio     │
├────┼─────────┤         ├────┼─────────┼───────────┤
│ 1  │ a@m.com │◄────────│ 1  │    1    │ Dev...    │
│ 2  │ b@m.com │◄────────│ 2  │    2    │ Designer..│
└────┴─────────┘         └────┴─────────┴───────────┘

Cas d’usage : Séparer des données rarement utilisées (optimisation).

1️⃣➡️🔢 Relation Un-à-Plusieurs (1:N)

Une ligne dans A peut correspondre à plusieurs lignes dans B.

clients                   commandes
┌────┬─────────┐         ┌────┬───────────┬─────────┐
│ id │   nom   │         │ id │ client_id │ produit │
├────┼─────────┤         ├────┼───────────┼─────────┤
│ 1  │  Alice  │◄────┬───│ 1  │     1     │ Clavier │
│ 2  │  Bob    │◄──┐ └───│ 2  │     1     │ Souris  │
└────┴─────────┘   └─────│ 3  │     2     │ Écran   │
                         └────┴───────────┴─────────┘

Cas d’usage : Client → Commandes, Auteur → Articles, Pays → Villes.

🔢↔︎️🔢 Relation Plusieurs-à-Plusieurs (N:N)

Plusieurs lignes dans A correspondent à plusieurs lignes dans B.

Nécessite une table de jonction !

etudiants          inscriptions         cours
┌────┬───────┐    ┌────────────┬──────────┐    ┌────┬─────────┐
│ id │  nom  │    │ etudiant_id│ cours_id │    │ id │   nom   │
├────┼───────┤    ├────────────┼──────────┤    ├────┼─────────┤
│ 1  │ Alice │◄───│     1      │    1     │───►│ 1  │  Maths  │
│ 2  │ Bob   │◄─┬─│     1      │    2     │─┬─►│ 2  │ Python  │
└────┴───────┘  │ │     2      │    1     │ │  └────┴─────────┘
                └─│     2      │    2     │─┘
                  └────────────┴──────────┘
                       Table de jonction

Cas d’usage : Étudiants ↔︎ Cours, Produits ↔︎ Tags, Acteurs ↔︎ Films.

8. Normalisation

La normalisation consiste à organiser les données pour :

  • Éviter la redondance (données dupliquées)
  • Éviter les anomalies (incohérences lors de modifications)
  • Garantir l’intégrité des données

Exemple NON normalisé

Table : commandes (MAUVAIS)
┌────┬─────────┬───────────────┬─────────┬──────────────┐
│ id │ client  │ client_email  │ produit │    ville     │
├────┼─────────┼───────────────┼─────────┼──────────────┤
│ 1  │ Alice   │ alice@m.com   │ Clavier │    Paris     │
│ 2  │ Alice   │ alice@m.com   │ Souris  │    Paris     │  ← Redondance !
│ 3  │ Bob     │ bob@m.com     │ Écran   │    Lyon      │
└────┴─────────┴───────────────┴─────────┴──────────────┘

❌ Problèmes :
  - Si Alice change d'email → modifier TOUTES les lignes
  - Risque d'incohérence si on oublie une ligne
  - Espace gaspillé

✅ Exemple normalisé

Table : clients               Table : commandes
┌────┬─────────┬─────────────┐   ┌────┬───────────┬─────────┐
│ id │   nom   │    email    │   │ id │ client_id │ produit │
├────┼─────────┼─────────────┤   ├────┼───────────┼─────────┤
│ 1  │ Alice   │ alice@m.com │   │ 1  │     1     │ Clavier │
│ 2  │ Bob     │ bob@m.com   │   │ 2  │     1     │ Souris  │
└────┴─────────┴─────────────┘   │ 3  │     2     │ Écran   │
                                 └────┴───────────┴─────────┘

✅ Avantages :
  - Email modifié une seule fois
  - Pas de redondance
  - Données cohérentes

Formes normales (résumé)

Forme Règle principale
1NF Chaque cellule contient une seule valeur (pas de listes)
2NF 1NF + chaque colonne dépend de TOUTE la clé primaire
3NF 2NF + pas de dépendance entre colonnes non-clés

💡 En pratique, la 3NF est généralement suffisante pour les bases OLTP.

9. Propriétés ACID

ACID garantit la fiabilité des transactions dans une base relationnelle.

Les 4 propriétés

                    ┌─────────────────┐
                    │   TRANSACTION   │
                    │   (ex: virement │
                    │    bancaire)    │
                    └────────┬────────┘
                             │
         ┌───────────────────┼───────────────────┐
         │                   │                   │
         ▼                   ▼                   ▼
   ┌───────────┐      ┌───────────┐      ┌───────────┐
   │ ATOMICITÉ │      │ COHÉRENCE │      │ ISOLATION │
   │  Tout ou  │      │   État    │      │Transactions│
   │   rien    │      │  valide   │      │ séparées  │
   └───────────┘      └───────────┘      └───────────┘
                             │
                             ▼
                      ┌───────────┐
                      │ DURABILITÉ│
                      │ Permanent │
                      └───────────┘

Détail de chaque propriété

Lettre Propriété Description Exemple
A Atomicité Tout ou rien — si une partie échoue, tout est annulé Virement : débit ET crédit réussissent ou rien
C Cohérence La base reste dans un état valide Le solde ne peut pas être négatif
I Isolation Les transactions concurrentes ne s’interfèrent pas Deux virements simultanés ne se mélangent pas
D Durabilité Une fois validée, la transaction est permanente Même après un crash, le virement est enregistré

Exemple : Virement bancaire

TRANSACTION : Virer 100€ de Alice vers Bob

  1. Débiter 100€ du compte Alice
  2. Créditer 100€ sur le compte Bob

ATOMICITÉ :
  ✅ Les deux opérations réussissent → COMMIT
  ❌ Une opération échoue → ROLLBACK (rien ne change)

COHÉRENCE :
  ✅ Alice : 500€ → 400€
  ✅ Bob   : 200€ → 300€
  ✅ Total : 700€ → 700€ (inchangé)

ISOLATION :
  Un autre virement simultané ne voit pas l'état intermédiaire

DURABILITÉ :
  Même si le serveur crash juste après le COMMIT,
  le virement sera toujours là au redémarrage

10. OLTP vs OLAP — Deux mondes différents

Les bases de données relationnelles peuvent servir à deux usages très différents. Comprendre cette distinction est fondamental en Data Engineering.

10.1 OLTP — Online Transaction Processing

Les bases OLTP gèrent les opérations quotidiennes d’une entreprise.

Caractéristique Description
But Gérer les transactions courantes
Opérations INSERT, UPDATE, DELETE fréquents
Requêtes Simples, sur peu de lignes
Utilisateurs Applications, employés
Volume par requête Quelques lignes
Priorité Rapidité, disponibilité
Schéma Normalisé (3NF)

Exemples : - Application e-commerce (commandes, paiements) - Système bancaire (virements, retraits) - Gestion de stock (entrées, sorties) - Réservation de billets

10.2 OLAP — Online Analytical Processing

Les bases OLAP sont conçues pour l’analyse de données et le reporting.

Caractéristique Description
But Analyser les données historiques
Opérations SELECT complexes (agrégations, jointures)
Requêtes Complexes, sur des millions de lignes
Utilisateurs Analystes, Data Scientists, dirigeants
Volume par requête Des millions/milliards de lignes
Priorité Performance analytique
Schéma Dénormalisé (Star Schema, Snowflake)

Exemples : - Rapport des ventes par région/mois - Analyse du comportement client - Tableaux de bord (dashboards) - Prévisions et tendances

10.3 Comparaison OLTP vs OLAP

           OLTP                              OLAP
    (Transactionnel)                    (Analytique)
          │                                  │
    ┌─────▼─────┐                      ┌─────▼─────┐
    │  INSERT   │                      │  SELECT   │
    │  UPDATE   │                      │  GROUP BY │
    │  DELETE   │                      │  JOIN     │
    │  (CRUD)   │                      │  (Analyse)│
    └─────┬─────┘                      └─────┬─────┘
          │                                  │
    ┌─────▼─────┐                      ┌─────▼─────┐
    │ Quelques  │                      │ Millions  │
    │  lignes   │                      │ de lignes │
    └─────┬─────┘                      └─────┬─────┘
          │                                  │
    ┌─────▼─────┐                      ┌─────▼─────┐
    │   App     │                      │  Rapport  │
    │   Web     │                      │ Dashboard │
    └───────────┘                      └───────────┘
Critère OLTP OLAP
Objectif Opérations quotidiennes Analyse, décision
Données Actuelles Historiques
Requêtes Simples, fréquentes Complexes, ponctuelles
Temps de réponse Millisecondes Secondes à minutes
Utilisateurs Milliers (applications) Dizaines (analystes)
Schéma Normalisé Dénormalisé
Exemples PostgreSQL, MySQL Snowflake, BigQuery, Redshift

10.4 Data Warehouse — L’entrepôt de données

Un Data Warehouse (DWH) est une base de données OLAP centralisée qui stocke les données de toute l’entreprise pour l’analyse.

Définition

“Un Data Warehouse est une copie des données transactionnelles, structurée spécifiquement pour l’analyse et le reporting.” — Bill Inmon

Caractéristiques

Caractéristique Description
Orienté sujet Organisé par domaine métier (ventes, clients, produits)
Intégré Données de sources multiples, harmonisées
Historisé Conserve l’historique (pas de suppression)
Non volatile Données en lecture seule (pas de UPDATE)

Exemples de Data Warehouses

Produit Type Particularité
Snowflake Cloud Séparation stockage/calcul, très scalable
Amazon Redshift Cloud AWS Intégration AWS native
Google BigQuery Cloud GCP Serverless, pay-per-query
Azure Synapse Cloud Azure Intégration Microsoft
Teradata On-premise Historique, grandes entreprises

10.5 Data Mart — Le magasin spécialisé

Un Data Mart est un sous-ensemble du Data Warehouse, focalisé sur un domaine métier spécifique.

Définition

“Un Data Mart est une vue spécialisée du Data Warehouse, optimisée pour les besoins d’un département ou d’une fonction métier.”

Exemples de Data Marts

Data Mart Données Utilisateurs
Marketing Campagnes, conversions, segments Équipe Marketing
Finance Revenus, coûts, budgets Direction financière
RH Employés, salaires, turnover Ressources Humaines
Ventes Commandes, clients, produits Équipe commerciale

10.6 Architecture globale

┌─────────────────────────────────────────────────────────────────────────┐
│                         SOURCES DE DONNÉES                              │
│  ┌──────────┐  ┌──────────┐  ┌──────────┐  ┌──────────┐               │
│  │   ERP    │  │   CRM    │  │  E-comm  │  │   Logs   │  ...          │
│  │  (OLTP)  │  │  (OLTP)  │  │  (OLTP)  │  │          │               │
│  └────┬─────┘  └────┬─────┘  └────┬─────┘  └────┬─────┘               │
└───────┼─────────────┼─────────────┼─────────────┼────────────────────-─┘
        │             │             │             │
        └─────────────┴──────┬──────┴─────────────┘
                             │
                    ┌────────▼────────┐
                    │   ETL / ELT     │
                    │  (Extraction,   │
                    │  Transformation,│
                    │  Chargement)    │
                    └────────┬────────┘
                             │
                    ┌────────▼────────┐
                    │  DATA WAREHOUSE │
                    │     (OLAP)      │
                    │                 │
                    │ ┌─────────────┐ │
                    │ │  Données    │ │
                    │ │  intégrées  │ │
                    │ │  historisées│ │
                    │ └─────────────┘ │
                    └────────┬────────┘
                             │
         ┌───────────────────┼───────────────────┐
         │                   │                   │
         ▼                   ▼                   ▼
  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐
  │  DATA MART  │     │  DATA MART  │     │  DATA MART  │
  │   Ventes    │     │  Marketing  │     │   Finance   │
  └──────┬──────┘     └──────┬──────┘     └──────┬──────┘
         │                   │                   │
         ▼                   ▼                   ▼
  ┌─────────────┐     ┌─────────────┐     ┌─────────────┐
  │  Dashboard  │     │  Rapports   │     │    KPIs     │
  │   Power BI  │     │  Campaigns  │     │  Financiers │
  └─────────────┘     └─────────────┘     └─────────────┘

💡 Résumé OLTP / OLAP / DWH / Data Mart

Concept Rôle Analogie
OLTP Opérations quotidiennes La caisse enregistreuse
OLAP Analyse des données Le bureau de l’analyste
Data Warehouse Entrepôt centralisé Le grand entrepôt
Data Mart Vue métier spécialisée Le rayon d’un magasin

11. Modélisation Dimensionnelle — Star & Snowflake Schema

La modélisation dimensionnelle est la technique utilisée pour structurer les données dans un Data Warehouse. Elle est optimisée pour l’analyse (OLAP), pas pour les transactions (OLTP).

💡 Cette approche a été popularisée par Ralph Kimball dans les années 1990.

11.1 Tables de Faits (Fact Tables)

Une table de faits contient les mesures (métriques) que l’on veut analyser.

┌─────────────────────────────────────────────────────────────────┐
│                      TABLE DE FAITS                             │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│    Contient :                                                  │
│     • Les MESURES (chiffres à analyser)                        │
│     • Les CLÉS ÉTRANGÈRES vers les dimensions                  │
│                                                                 │
│    Exemples de mesures :                                       │
│     • Montant de la vente                                      │
│     • Quantité vendue                                          │
│     • Coût                                                     │
│     • Nombre de clics                                          │
│                                                                 │
│    Caractéristiques :                                          │
│     • Très grande (millions/milliards de lignes)               │
│     • Une ligne = un ÉVÉNEMENT (une vente, un clic, etc.)      │
│     • Granularité fine (niveau de détail)                      │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Exemple : Table de faits fact_sales

fact_sales
┌────────────┬─────────────┬────────────────┬────────────────┬──────────┬──────────┐
│ sale_id(PK)│ date_id(FK) │ product_id(FK) │ customer_id(FK)│ quantity │  amount  │
├────────────┼─────────────┼────────────────┼────────────────┼──────────┼──────────┤
│     1      │  20240115   │      101       │      501       │    2     │  199.98  │
│     2      │  20240115   │      102       │      502       │    1     │   49.99  │
│     3      │  20240116   │      101       │      501       │    1     │   99.99  │
│    ...     │    ...      │      ...       │      ...       │   ...    │   ...    │
└────────────┴─────────────┴────────────────┴────────────────┴──────────┴──────────┘
                   │              │                │
                FK vers        FK vers          FK vers
               dim_date     dim_product      dim_customer

11.2 Tables de Dimensions (Dimension Tables)

Une table de dimension contient le contexte descriptif des mesures.

┌─────────────────────────────────────────────────────────────────┐
│                    TABLE DE DIMENSION                           │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│    Contient :                                                  │
│     • Les ATTRIBUTS descriptifs (le "quoi", "qui", "où", "quand") │
│     • La clé primaire référencée par la table de faits         │
│                                                                 │
│    Exemples de dimensions :                                    │
│     • dim_date : année, mois, jour, trimestre, jour_semaine    │
│     • dim_product : nom, catégorie, marque, prix_liste         │
│     • dim_customer : nom, segment, ville, pays                 │
│     • dim_store : nom, région, type, surface                   │
│                                                                 │
│    Caractéristiques :                                          │
│     • Relativement petite (milliers à millions de lignes)      │
│     • Dénormalisée (toutes les infos dans une seule table)     │
│     • Permet le "slicing & dicing" (filtres et regroupements)  │
│                                                                 │
└─────────────────────────────────────────────────────────────────┘

Exemples de tables de dimensions :

dim_date                                    dim_product
┌──────────┬──────┬─────┬─────┬──────┐    ┌────────────┬──────────┬───────────┬─────────┐
│ date_id  │ year │month│ day │quarter│   │ product_id │   name   │  category │  brand  │
├──────────┼──────┼─────┼─────┼──────┤    ├────────────┼──────────┼───────────┼─────────┤
│ 20240115 │ 2024 │  1  │ 15  │  Q1  │    │    101     │ iPhone 15│ Smartphone│  Apple  │
│ 20240116 │ 2024 │  1  │ 16  │  Q1  │    │    102     │Galaxy S24│ Smartphone│ Samsung │
└──────────┴──────┴─────┴─────┴──────┘    └────────────┴──────────┴───────────┴─────────┘

dim_customer
┌─────────────┬──────────┬──────────┬─────────┬─────────┐
│ customer_id │   name   │ segment  │  city   │ country │
├─────────────┼──────────┼──────────┼─────────┼─────────┤
│    501      │  Alice   │ Premium  │  Paris  │ France  │
│    502      │   Bob    │ Standard │  Lyon   │ France  │
└─────────────┴──────────┴──────────┴─────────┴─────────┘

11.3 Star Schema (Schéma en étoile)

Le Star Schema est le modèle dimensionnel le plus simple et le plus utilisé.

                            ┌─────────────────┐
                            │    dim_date     │
                            │─────────────────│
                            │ date_id (PK)    │
                            │ year            │
                            │ month           │
                            │ quarter         │
                            └────────┬────────┘
                                     │
┌─────────────────┐         ┌────────▼────────┐         ┌─────────────────┐
│   dim_customer  │         │   fact_sales    │         │   dim_product   │
│─────────────────│         │─────────────────│         │─────────────────│
│ customer_id(PK) │◄────────│ sale_id (PK)    │────────►│ product_id (PK) │
│ name            │         │ date_id (FK)    │         │ name            │
│ segment         │         │ product_id (FK) │         │ category        │
│ city            │         │ customer_id(FK) │         │ brand           │
│ country         │         │ store_id (FK)   │         │ price           │
└─────────────────┘         │ quantity        │         └─────────────────┘
                            │ amount          │
                            └────────┬────────┘
                                     │
                            ┌────────▼────────┐
                            │   dim_store     │
                            │─────────────────│
                            │ store_id (PK)   │
                            │ name            │
                            │ region          │
                            │ type            │
                            └─────────────────┘

Pourquoi “étoile” ? La table de faits est au centre, entourée des dimensions — comme une étoile !

✅ Avantages ❌ Inconvénients
Simple à comprendre Redondance dans les dimensions
Requêtes rapides (peu de jointures) Dimensions peuvent être grandes
Facile à maintenir Pas adapté aux hiérarchies très complexes

11.4 Snowflake Schema (Schéma en flocon)

Le Snowflake Schema est une variante où les dimensions sont normalisées (découpées en sous-tables).

┌───────────────┐     ┌─────────────────┐
│   dim_year    │     │   dim_month     │
│───────────────│     │─────────────────│
│ year_id (PK)  │◄────│ month_id (PK)   │
│ year          │     │ year_id (FK)    │
└───────────────┘     │ month           │
                      │ quarter         │
                      └────────┬────────┘
                               │
                      ┌────────▼────────┐         ┌─────────────────┐
                      │    dim_date     │         │  dim_category   │
                      │─────────────────│         │─────────────────│
                      │ date_id (PK)    │         │ category_id(PK) │
                      │ month_id (FK)   │         │ category_name   │
                      │ day             │         └────────┬────────┘
                      └────────┬────────┘                  │
                               │                           │
                      ┌────────▼────────┐         ┌────────▼────────┐
                      │   fact_sales    │         │   dim_product   │
                      │─────────────────│         │─────────────────│
                      │ date_id (FK)    │────────►│ product_id (PK) │
                      │ product_id (FK) │         │ category_id(FK) │
                      │ quantity        │         │ name            │
                      │ amount          │         │ brand           │
                      └─────────────────┘         └─────────────────┘

Pourquoi “flocon” ? Les branches se subdivisent comme un flocon de neige.

✅ Avantages ❌ Inconvénients
Moins de redondance Plus de jointures (plus lent)
Économie d’espace Plus complexe à comprendre
Meilleur pour hiérarchies Maintenance plus difficile

11.5 Star vs Snowflake — Quand utiliser quoi ?

Critère Star Schema ⭐ Snowflake Schema ❄️
Performance ⚡ Plus rapide 🐢 Plus de jointures
Simplicité ✅ Simple ⚠️ Complexe
Redondance ⚠️ Plus de duplication ✅ Moins de duplication
Cas d’usage Reporting, dashboards Hiérarchies complexes

💡 En pratique : Le Star Schema est recommandé dans 90% des cas. Utilise Snowflake uniquement si tu as des contraintes spécifiques de stockage ou des hiérarchies très profondes.

11.6 Exemple de requête analytique

Avec un Star Schema, les requêtes analytiques sont simples et intuitives :

-- Ventes par catégorie de produit et par trimestre
SELECT 
    d.year,
    d.quarter,
    p.category,
    SUM(f.amount) as total_sales,
    COUNT(*) as num_transactions
FROM fact_sales f
JOIN dim_date d ON f.date_id = d.date_id
JOIN dim_product p ON f.product_id = p.product_id
WHERE d.year = 2024
GROUP BY d.year, d.quarter, p.category
ORDER BY d.quarter, total_sales DESC;
year quarter category total_sales num_transactions
2024 Q1 Smartphone 1,250,000 8,500
2024 Q1 Laptop 980,000 3,200
2024 Q1 Accessoires 450,000 15,000

🔮 Preview : Dans le module 08, tu découvriras les concepts de Data Lake, Data Lakehouse, Medallion Architecture (Bronze/Silver/Gold) et les architectures Lambda/Kappa pour le Big Data !

12. Exemple complet : Schéma d’une boutique en ligne (OLTP)

Voici un exemple de schéma OLTP normalisé pour les opérations quotidiennes :

┌─────────────────────────────────────────────────────────────────────────┐
│                        BASE DE DONNÉES : ma_boutique                    │
├─────────────────────────────────────────────────────────────────────────┤
│                                                                         │
│  ┌─────────────────┐       ┌─────────────────┐       ┌───────────────┐ │
│  │    CLIENTS      │       │    COMMANDES    │       │   PRODUITS    │ │
│  ├─────────────────┤       ├─────────────────┤       ├───────────────┤ │
│  │ 🔑 id (PK)      │       │ 🔑 id (PK)      │       │ 🔑 id (PK)    │ │
│  │ nom             │◄──────│ 🔗 client_id(FK)│       │ nom           │ │
│  │ email           │       │ date            │       │ prix          │ │
│  │ telephone       │       │ statut          │       │ stock         │ │
│  │ created_at      │       │ total           │       │ categorie     │ │
│  └─────────────────┘       └────────┬────────┘       └───────┬───────┘ │
│                                     │                        │         │
│                                     │    ┌───────────────────┘         │
│                                     │    │                             │
│                                     ▼    ▼                             │
│                            ┌─────────────────────┐                     │
│                            │   LIGNES_COMMANDE   │                     │
│                            ├─────────────────────┤                     │
│                            │ 🔑 id (PK)          │                     │
│                            │ 🔗 commande_id (FK) │                     │
│                            │ 🔗 produit_id (FK)  │                     │
│                            │ quantite            │                     │
│                            │ prix_unitaire       │                     │
│                            └─────────────────────┘                     │
│                                                                         │
└─────────────────────────────────────────────────────────────────────────┘

RELATIONS :
  • clients (1) ───► (N) commandes        : Un client a plusieurs commandes
  • commandes (1) ───► (N) lignes_commande : Une commande a plusieurs lignes
  • produits (1) ───► (N) lignes_commande  : Un produit dans plusieurs lignes

Ce schéma est normalisé (3NF) car c’est un système OLTP pour les opérations quotidiennes.

Résumé

Vocabulaire relationnel

Terme Description
Table Collection de données structurées
Colonne Attribut (nom, email, prix…)
Ligne Un enregistrement (un client, une commande…)
Clé Primaire (PK) Identifiant unique d’une ligne
Clé Étrangère (FK) Référence vers une autre table
SGBD Logiciel de gestion (PostgreSQL, MySQL…)

Relations

Type Notation Exemple
Un-à-Un 1:1 Utilisateur ↔︎ Profil
Un-à-Plusieurs 1:N Client → Commandes
Plusieurs-à-Plusieurs N:N Étudiants ↔︎ Cours

ACID

Lettre Propriété
A Atomicité — Tout ou rien
C Cohérence — État valide
I Isolation — Transactions séparées
D Durabilité — Permanent

OLTP vs OLAP

Concept OLTP OLAP
Usage Transactions Analyse
Requêtes Simples, rapides Complexes, agrégées
Schéma Normalisé Dénormalisé

Data Warehouse & Data Mart

Concept Description
Data Warehouse Entrepôt centralisé pour l’analyse
Data Mart Sous-ensemble orienté métier

Modélisation dimensionnelle

Concept Description
Table de faits Mesures numériques (ventes, quantités)
Table de dimension Contexte descriptif (date, produit, client)
Star Schema Faits au centre, dimensions autour
Snowflake Schema Star avec dimensions normalisées

Quiz

❓ Q1. Qu’est-ce qu’une clé primaire ?

  1. Une clé de chiffrement
  2. Un identifiant unique pour chaque ligne
  3. Le nom de la première colonne
  4. Un mot de passe
💡 Réponse ✅ b — La clé primaire identifie de façon unique chaque ligne d’une table.

❓ Q2. À quoi sert une clé étrangère ?

  1. À chiffrer les données
  2. À créer un lien entre deux tables
  3. À indexer les colonnes
  4. À supprimer des lignes
💡 Réponse ✅ b — La clé étrangère référence la clé primaire d’une autre table pour créer une relation.

❓ Q3. Quelle relation nécessite une table de jonction ?

  1. Un-à-Un (1:1)
  2. Un-à-Plusieurs (1:N)
  3. Plusieurs-à-Plusieurs (N:N)
  4. Aucune
💡 Réponse ✅ c — Les relations N:N nécessitent une table intermédiaire (jonction) pour stocker les associations.

❓ Q4. Que signifie le A de ACID ?

  1. Authentification
  2. Atomicité
  3. Automatisation
  4. Archivage
💡 Réponse ✅ b — Atomicité : une transaction est indivisible (tout réussit ou tout échoue).

❓ Q5. Pourquoi normaliser une base de données ?

  1. Pour la rendre plus rapide
  2. Pour éviter la redondance et les incohérences
  3. Pour ajouter du chiffrement
  4. Pour compresser les données
💡 Réponse ✅ b — La normalisation évite les données dupliquées et garantit la cohérence.

❓ Q6. Quel SGBD est open source ET très complet ?

  1. Oracle
  2. SQL Server
  3. PostgreSQL
  4. Access
💡 Réponse ✅ c — PostgreSQL est open source, gratuit et très complet (le plus recommandé).

❓ Q7. Quel type utiliser pour stocker des montants en euros ?

  1. FLOAT
  2. INTEGER
  3. DECIMAL
  4. VARCHAR
💡 Réponse ✅ c — DECIMAL garantit une précision exacte pour les montants financiers (FLOAT est imprécis).

❓ Q8. Quelle est la différence principale entre OLTP et OLAP ?

  1. OLTP est plus récent
  2. OLTP gère les transactions, OLAP l’analyse
  3. OLAP est plus rapide
  4. Aucune différence
💡 Réponse ✅ b — OLTP (Online Transaction Processing) gère les opérations quotidiennes, OLAP (Online Analytical Processing) est optimisé pour l’analyse et le reporting.

❓ Q9. Qu’est-ce qu’un Data Warehouse ?

  1. Un serveur très puissant
  2. Un entrepôt centralisé pour l’analyse des données
  3. Un type de base de données NoSQL
  4. Un logiciel de visualisation
💡 Réponse ✅ b — Un Data Warehouse est une base de données OLAP centralisée qui stocke les données de l’entreprise pour l’analyse et le reporting.

❓ Q10. Qu’est-ce qu’un Data Mart ?

  1. Un magasin de données brutes
  2. Un sous-ensemble du Data Warehouse orienté métier
  3. Une base de données transactionnelle
  4. Un outil ETL
💡 Réponse ✅ b — Un Data Mart est une vue spécialisée du Data Warehouse, focalisée sur un domaine métier (Marketing, Finance, Ventes…).

❓ Q11. Dans un Star Schema, que contient la table de faits ?

  1. Les descriptions des produits
  2. Les mesures (métriques) à analyser
  3. Les informations clients
  4. Les dates uniquement
💡 Réponse ✅ b — La table de faits contient les mesures numériques (montants, quantités, clics…) et les clés étrangères vers les dimensions.

❓ Q12. Quelle est la différence entre Star Schema et Snowflake Schema ?

  1. Star Schema est plus récent
  2. Dans Snowflake, les dimensions sont normalisées (découpées)
  3. Snowflake n’a pas de table de faits
  4. Ils sont identiques
💡 Réponse ✅ b — Dans un Snowflake Schema, les tables de dimensions sont normalisées (découpées en sous-tables), ce qui ressemble à un flocon de neige.

❓ Q13. Une table de dimension contient…

  1. Les chiffres à analyser
  2. Le contexte descriptif (qui, quoi, où, quand)
  3. Les clés étrangères uniquement
  4. Les données en temps réel
💡 Réponse ✅ b — Une table de dimension contient les attributs descriptifs qui donnent du contexte aux mesures (produit, client, date, magasin…).

📚 Ressources

Bases de données relationnelles

OLTP, OLAP et Data Warehousing

Modélisation dimensionnelle

➡️ Prochaine étape

Tu connais maintenant la théorie des bases de données relationnelles, OLTP/OLAP, Data Warehouse, et la modélisation dimensionnelle. Passons à la pratique !

👉 Module suivant : 07_sql_for_data_engineers — Écrire des requêtes SQL

🎉 Félicitations ! Tu maîtrises les concepts fondamentaux des bases relationnelles et de l’architecture Data.

Retour au sommet