Ce document est un travail pratique (TP) conçu pour les étudiants universitaires du BTS SIO SLAM3, se concentrant sur la conception de bases de données. Il vise à une compréhension approfondie des mécanismes internes du SQL par l'implémentation en PHP.
Ce TP aborde les concepts clés suivants :
- Les clauses de sélection et de filtrage (SELECT, WHERE) ;
- Les opérations de jointure (JOIN) et de regroupement (GROUP BY) ;
- L'intégration des fonctions d'agrégat (COUNT, SUM, AVG) ;
- Les commandes de manipulation de données (INSERT, DELETE).
L'objectif est de consolider les compétences pratiques en gestion de bases de données.
Modélisation Merise : TP N°3 le fonctionnement des clauses SQL
Télécharger PDFBTS SIO SLAM3 : Conception de Bases de Données et Maîtrise des Clauses SQL
TP N°3 : Pause SQL - Approfondissement des clauses SQL et révisions MERISE
Ce travail pratique (TP) vise à solidifier votre compréhension du fonctionnement des principales fonctionnalités du langage SQL standard, telles que les clauses SELECT, WHERE, et les jointures. Au-delà de la simple rédaction de requêtes SQL pour l'examen, ce TP vous propose d'implémenter certaines des opérations effectuées par un interpréteur SQL. Cette démarche par l'implémentation, notamment en PHP, vous permettra de saisir pleinement le fonctionnement du SQL et de comprendre ce que l'on peut en attendre. C'est également une opportunité de coder et de varier les approches en matière de conception de base de données, en explorant SQL, MCD, MLD, UML, etc.
Exercice : Implémentation des clauses SQL en PHP
L'objectif de ce TP est d'implémenter (en PHP) et de tester la classe Table dont un squelette de code vous est fourni. Vous effectuerez vos tests dans une simple page PHP, "test.php", dont le squelette vous est également fourni.
Missions et Questions d'Implémentation
- Implémenter et tester la méthode
Table::getHeader($index).Cette méthode a pour objectif de récupérer le nom de l'en-tête (la colonne) à un index spécifique, simulant l'accès aux métadonnées d'une table.
- Implémenter et tester la méthode
Table::html().Cette fonction est essentielle pour l'affichage des résultats. Elle doit permettre de présenter le contenu de la table sous forme de tableau HTML, facilitant la vérification visuelle des opérations effectuées par les autres méthodes.
- Implémenter et tester la méthode
Table::select($fields).La clause SQL
SELECTest utilisée pour choisir les colonnes que vous souhaitez inclure dans le jeu de résultats d'une requête. Cette méthode doit filtrer les colonnes de la table en fonction des champs spécifiés. - Implémenter et tester la méthode
Table::where($field, $operator, $value).La clause SQL
WHEREest fondamentale pour filtrer les lignes d'un jeu de résultats en fonction d'une condition donnée. Votre implémentation doit appliquer cette logique de filtrage sur les données de la table. - Implémenter et tester la méthode
Table::product($table).Le produit cartésien, bien que souvent implicite dans les jointures modernes, est une opération fondamentale qui combine chaque ligne de la première table avec chaque ligne de la seconde table. Cela constitue la base des opérations de jointure.
- Implémenter et tester la méthode
Table::innerJoin($table, $field1, $field2).La jointure interne (
INNER JOIN) permet de combiner les lignes de deux tables ou plus en se basant sur une condition de correspondance commune. Seules les lignes pour lesquelles une correspondance est trouvée dans les deux tables seront incluses dans le résultat. - Implémenter et tester la méthode
Table::orderBy($fields). La fonction PHPusort($tableau, $fonction)devrait vous être utile.La clause SQL
ORDER BYpermet de trier le jeu de résultats d'une requête dans un ordre spécifié (ascendant ou descendant) en fonction d'une ou plusieurs colonnes, améliorant la lisibilité des données. - Implémenter et tester la méthode
Table::leftJoin($table, $field1, $field2)en vous inspirant de la méthodeTable::innerJoin($table, $field1, $field2).La jointure gauche (
LEFT JOINouLEFT OUTER JOIN) retourne toutes les lignes de la table de gauche, et les lignes correspondantes de la table de droite. Si aucune correspondance n'est trouvée dans la table de droite, des valeurs NULL sont utilisées pour les colonnes de la table de droite. - Implémenter et tester la méthode
Table::rightJoin($table, $field1, $field2)en vous inspirant et/ou en vous aidant de la méthodeTable::leftJoin($table, $field1, $field2).La jointure droite (
RIGHT JOINouRIGHT OUTER JOIN) est le complément de la jointure gauche : elle retourne toutes les lignes de la table de droite, et les lignes correspondantes de la table de gauche. Si aucune correspondance n'est trouvée dans la table de gauche, des valeurs NULL sont utilisées pour les colonnes de la table de gauche. - Implémenter et tester la méthode
Table::groupBy($fields). Compléter la méthodeTable::html()afin de rendre l’affichage du résultat deTable::groupBy($fields). En effet, en l’absence de levée d’exception, cette méthode retourne un tableau à 3 dimensions ! À savoir que la Table résultat duGROUP BYest très normalement un tableau contenant des lignes où chaque ligne contient la valeur de chaque colonne (=cellule). Mais chaque colonne (=cellule) contient potentiellement plusieurs valeurs ! C’est bien cela la notion de « regroupement », deGROUP BY.La clause SQL
GROUP BYregroupe les lignes qui ont les mêmes valeurs dans des colonnes spécifiées en une seule ligne de résumé. Elle est fréquemment utilisée avec des fonctions d'agrégation (telles que COUNT, SUM, AVG) pour effectuer des calculs sur chaque groupe de données. - Compléter la méthode
Table::select(...)afin qu’elle permette de prendre en charge les agrégatsCOUNT,SUMetAVG.Les fonctions d'agrégation permettent de calculer une valeur unique à partir d'un ensemble de valeurs.
COUNTcompte le nombre de lignes,SUMcalcule la somme des valeurs numériques, etAVGcalcule la moyenne des valeurs numériques. - Compléter et tester la méthode
Table::where($field, $operator, $value)afin qu’elle puisse se comporter comme une clauseHAVING, à savoir, de sorte qu’on puisse, après utilisation duGROUP BY, comparer un agrégat à une valeur.La clause SQL
HAVINGest similaire àWHERE, mais elle est spécifiquement utilisée pour filtrer des groupes de lignes après qu'ils ont été regroupés par la clauseGROUP BY, souvent en appliquant des conditions sur les résultats des fonctions d'agrégation. - Compléter et tester la méthode
Table::where($field, $operator, $value)afin qu’elle puisse se comporter comme une clauseHAVING, à savoir, de sorte qu’on puisse, après utilisation duGROUP BY, comparer un agrégat à une valeur.Cette tâche est une répétition de la précédente, soulignant l'importance de bien maîtriser le filtrage post-agrégation et la distinction entre
WHEREetHAVING. - Compléter et tester la méthode
Table::insert($values).La commande SQL
INSERTest utilisée pour ajouter de nouvelles lignes (enregistrements) à une table existante dans une base de données, permettant ainsi d'enrichir le jeu de données. - Compléter et tester la méthode
Table::delete($field, $operator, $value).La commande SQL
DELETEest utilisée pour supprimer des lignes existantes d'une table, en fonction de conditions spécifiées par une clauseWHERE(similaire à cette méthode), permettant la suppression ciblée de données.
Organisation du Projet
Ce projet sera réalisé par groupes de deux étudiants. Les questions 7 à 15 devront être implémentées et testées par chaque binôme. Les questions 1 à 6 seront corrigées collectivement en cours, servant de fondation et de base commune.
Modalités de Rendu et Présentation
- Les réalisations seront présentées par groupe de deux.
- Chaque présentation aura une durée maximale de 10 minutes, suivie d'une période de 10 minutes maximum dédiée aux questions.
- Les codes sources complets devront être remis à l'issue de la présentation.
Objectifs Pédagogiques du Projet
- Assurer que les deux membres du binôme comprennent pleinement le travail effectué et les concepts implémentés.
- Encourager la pratique du "pair programming" (programmation en binôme) pour favoriser le partage de connaissances, la collaboration efficace et la résolution conjointe de problèmes.
Critères de Notation
- Compréhension technique du travail réalisé.
- Qualité de la réalisation technique du projet (fonctionnalité, robustesse du code).
- Clarté et pertinence de la présentation, ainsi que des réponses apportées aux questions posées.
- Capacité à expliquer et justifier les choix techniques et la logique de ses codes sources à un interlocuteur (professeur ou autre binôme).
Informations Additionnelles
Choix du binôme : Le choix de votre binôme vous est entièrement libre.
Date de rendu : Le mardi 8 novembre, en classe.
Suite du projet : Ce TP constitue la première phase d'un projet plus vaste. Il donnera lieu à un second projet axé sur l'élaboration d'un petit Système de Gestion de Base de Données (SGBD) accompagné d'un éditeur de requêtes SQL. Ce second projet sera également réalisé en binôme, mais les équipes pourront être imposées cette fois-ci pour diversifier les collaborations.
Foire Aux Questions (FAQ)
- Qu'est-ce que le SQL standard et pourquoi est-il important ?
- Le SQL (Structured Query Language) est un langage informatique standardisé utilisé pour interroger et manipuler les bases de données relationnelles. Le "SQL standard" fait référence aux spécifications établies par l'ANSI et l'ISO. Son importance réside dans le fait qu'il assure une certaine portabilité et compatibilité des requêtes et des opérations entre différents systèmes de gestion de bases de données (SGBD).
- Quelle est la différence fondamentale entre les clauses WHERE et HAVING en SQL ?
- La clause
WHEREest utilisée pour filtrer les lignes individuelles d'une table ou d'un jeu de résultats avant que toute agrégation ne soit effectuée. Elle s'applique aux colonnes brutes. En revanche, la clauseHAVINGest utilisée pour filtrer des groupes de lignes après que la clauseGROUP BYa été appliquée et que les fonctions d'agrégation (comme SUM, COUNT, AVG) ont calculé leurs résultats. Elle filtre donc sur les valeurs agrégées des groupes. - Pourquoi implémenter des clauses SQL en PHP plutôt que de simplement les utiliser directement avec une base de données ?
- Implémenter les mécanismes des clauses SQL en PHP offre une compréhension approfondie de leur fonctionnement interne. Cela permet de déconstruire et de comprendre les logiques complexes de filtrage, de tri, de jointure et d'agrégation, ainsi que les défis et mécanismes sous-jacents qu'un interpréteur SQL gère automatiquement. C'est un excellent exercice pratique pour les futurs développeurs et experts en bases de données afin de solidifier leurs connaissances théoriques et pratiques.