Cours PostgreSQL de conception de bases de données et SQL

Leçon 1 • Introduction aux diagrammes entité-relation

Enseigne la notation des diagrammes ER pour visualiser les modèles de données avant la mise en œuvre. Les diagrammes créés ici sont affinés tout au long du cours.

Leçon 2 • Relations entre les tables

Couvre les relations un-à-un, un-à-plusieurs et plusieurs-à-plusieurs. Fournit le vocabulaire nécessaire à la modélisation entité-relation.

Leçon 3 • Qu'est-ce qu'une base de données relationnelle

Définit les bases de données relationnelles, les oppose aux fichiers plats et au NoSQL. Établit la base conceptuelle pour tout le travail de conception subséquent.

Leçon 4 • Clés primaires et clés étrangères

Présente les contraintes de clé qui appliquent l'intégrité d'entité et l'intégrité référentielle. Les étudiants appliquent ces concepts lors de la liaison des tables dans les chapitres suivants.

Leçon 1 • Création de votre première base de données

Guide la création d'une base de données et d'un schéma avec des paramètres de propriété appropriés. Cette base de données pratique est utilisée dans tous les chapitres restants.

Leçon 2 • Installation locale de PostgreSQL

Guide l'installation sur les principaux systèmes d'exploitation et vérifie une configuration réussie. Une instance locale fonctionnelle est requise pour tous les exercices en laboratoire.

Leçon 3 • Utilisation de l'outil en ligne de commande psql

Présente l'interface psql pour exécuter des commandes SQL et des méta-commandes. La maîtrise de la ligne de commande accélère tous les exercices de requête subséquents.

Leçon 4 • Utilisation de l'interface graphique pgAdmin

Couvre la navigation dans pgAdmin, l'outil de requête et le navigateur d'objets. Fournit une alternative visuelle pour les étudiants moins à l'aise avec la ligne de commande.

Leçon 1 • Types de données textuelles et de caractères

Explique CHAR, VARCHAR et TEXT avec leurs implications sur le rendement. Le choix du bon type de texte affecte l'efficacité de stockage et l'indexation.

Leçon 2 • CREATE TABLE et contraintes de colonne

Enseigne la syntaxe DDL pour la création de tables avec les contraintes NOT NULL, UNIQUE et CHECK. Les contraintes appliquent la qualité des données au niveau de la base de données.

Leçon 3 • Types Date, Heure et Timestamp

Présente les types temporels, y compris les variantes tenant compte du fuseau horaire. Un stockage temporel approprié est essentiel pour des rapports et une planification précis.

Leçon 4 • Types de données numériques et booléens

Couvre les types entier, décimal et booléen avec leurs compromis de stockage et de précision. Une sélection correcte du type évite la perte de données et les erreurs de requête.

Leçon 1 • Troisième forme normale

Explique les dépendances transitives et comment la 3FN les élimine. La plupart des schémas de production ciblent la 3FN comme norme de conception pratique.

Leçon 2 • Conception d'un schéma multi-tables

Applique la normalisation à un scénario d'affaires réaliste de bout en bout. Les étudiants produisent un diagramme ER complet et un script DDL correspondant.

Leçon 3 • Première et deuxième formes normales

Définit l'atomicité de la 1FN et la dépendance fonctionnelle totale de la 2FN. Les étudiants s'exercent à décomposer les tables pour satisfaire à chaque forme.

Leçon 4 • Problèmes liés aux données non normalisées

Démontre les anomalies d'insertion, de mise à jour et de suppression dans les tables plates. Justifie la normalisation comme solution aux problèmes structurels des données.

Leçon 1 • Tri des résultats avec ORDER BY

Enseigne le tri ascendant et descendant sur une ou plusieurs colonnes. Une sortie ordonnée est requise pour les rapports et la pagination.

Leçon 2 • Fonctions d'agrégat et GROUP BY

Présente COUNT, SUM, AVG, MIN et MAX avec le regroupement GROUP BY. L'agrégation permet la création de rapports récapitulatifs par catégorie.

Leçon 3 • Filtrage des lignes avec WHERE

Couvre les opérateurs de comparaison, logiques et de motif dans les clauses WHERE. Le filtrage est la compétence la plus fréquemment utilisée dans le travail SQL quotidien.

Leçon 4 • Fondamentaux de SELECT et FROM

Présente la syntaxe SELECT, les alias de colonne et la clause FROM. Ce sont les éléments de base de chaque requête rédigée dans le cours.

Leçon 1 • Fondamentaux de INNER JOIN

Explique comment INNER JOIN fait correspondre les lignes sur une condition de jointure. C'est le type de jointure le plus courant utilisé dans le travail de requête relationnelle.

Leçon 2 • Sous-requêtes dans SELECT et WHERE

Enseigne les sous-requêtes scalaires et corrélées intégrées dans SELECT et WHERE. Les sous-requêtes permettent un filtrage complexe sans tables temporaires.

Leçon 3 • CROSS JOIN et auto-jointure

Présente les produits cartésiens et les jointures de table auto-référencées. Ces modèles résolvent des problèmes de données hiérarchiques et combinatoires.

Leçon 4 • Expressions de table communes

Présente les clauses WITH pour nommer et réutiliser des ensembles de résultats intermédiaires. Les CTE améliorent la lisibilité et permettent des modèles de requêtes récursifs.

Leçon 1 • Insertion de données avec INSERT

Couvre la syntaxe INSERT sur une ou plusieurs lignes, y compris le retour de valeurs. Des modèles d'insertion corrects évitent les violations de contrainte.

Leçon 2 • Mise à jour de lignes avec UPDATE

Enseigne les instructions UPDATE ciblées avec des conditions WHERE et des jointures. Les mises à jour non protégées sont une cause majeure de corruption des données.

Leçon 3 • Upsert avec INSERT ON CONFLICT

Couvre la clause ON CONFLICT pour les opérations d'insertion ou mise à jour idempotentes. Les modèles upsert sont essentiels pour les pipelines de données et les flux de synchronisation.

Leçon 4 • Suppression de lignes avec DELETE

Explique DELETE avec des filtres WHERE et le comportement en cascade des clés étrangères. Des pratiques de suppression sécurisées protègent l'intégrité référentielle.

Leçon 1 • Types d'index spécialisés

Présente les index Hash, GIN, GiST et partiels pour des charges de travail spécifiques. Choisir le bon type d'index évite une surcharge inutile.

Leçon 2 • Comment PostgreSQL exécute les requêtes

Explique le planificateur de requêtes, les statistiques et l'estimation des coûts. Comprendre le flux d'exécution est un prérequis à une optimisation significative.

Leçon 3 • Techniques d'optimisation des requêtes

Applique des stratégies de réécriture pour réduire le coût du plan et améliorer le débit. Les étudiants optimisent des requêtes réelles en utilisant les commentaires d'EXPLAIN de manière itérative.

Leçon 4 • Lecture de EXPLAIN et EXPLAIN ANALYZE

Enseigne comment interpréter les nœuds de sortie EXPLAIN et le minutage réel. La lecture de plan est la principale compétence de diagnostic pour les requêtes lentes.