Fiche de révision : Introduction à la modélisation relationnelle et SQL

📋 Plan du Cours

1. Modélisation relationnelle et tables
2. SGBDR et langage SQL
3. Clés primaires et étrangères
4. Sélection et filtrage avec SELECT
5. Jointures et tables associatives
6. Clés primaires des résultats
7. Agrégation avec GROUP BY et HAVING
8. Tri recherche et UNION

📖 1. Modélisation relationnelle et tables

🔑 Notions clés & Définitions

• Modélisation relationnelle : Approche de structuration des données en tables pour représenter des objets et leurs caractéristiques sous forme de lignes et de colonnes.
• Table : Structure en base de données où une ligne correspond à un objet et une colonne correspond à une caractéristique, en conservant une cohérence de nature entre toutes les lignes.
• Relation : Nom alternatif d’une table en théorie des bases relationnelles, correspondant à l’ensemble des lignes décrivant des objets de même nature.
• Attribut : Équivalent d’une colonne dans une table, représentant une caractéristique d’un objet décrite à travers toutes les lignes.

📝 Points essentiels

• La BDD et le programme sont complémentaires car le programme dépend des données stockées dans la base pour produire des résultats persistants.
• Une table est organisée avec 1 ligne par objet et 1 colonne par caractéristique, sans signification dans l’ordre des lignes ni des colonnes.
• Les bases relationnelles servent pour des données structurées en tableaux, tandis que les bases non relationnelles sont utilisées quand les données sont moins structurées ou en contexte Big Data.
• Dans le vocabulaire de l’algèbre relationnelle, la table peut être appelée relation et une ligne est un tuple, tandis que les colonnes sont des attributs.
• Le schéma d’une table correspond à l’ensemble de ses colonnes.

💡 Astuce mémo

Ligne = objet, Colonne = caractéristique : pense “L-C” comme “Ligne-Caractéristique”.

📖 2. SGBDR et langage SQL

🔑 Notions clés & Définitions

• SGBD : Système chargé de gérer un accès aux données, en servant d’intermédiaire entre les applications et le stockage.
• SGBDR : SGBD qui gère des bases de données relationnelles, donc des tables structurées selon le modèle relationnel.
• SQL : Langage standard utilisé pour formuler des requêtes adressées à un SGBDR afin de créer, contrôler et manipuler les données.
• LDD : Langage de définition de données servant à créer ou supprimer des objets dans la base.

📝 Points essentiels

• Plus la quantité de données augmente, plus l’accès ou la modification est long, ce qui justifie la création d’un SGBD.
• Le SGBDR utilise un langage pour communiquer avec les applications qui veulent accéder aux données de la base.
• SQL regroupe des requêtes en trois familles : LDD pour définir, LCD pour contrôler, et LMD pour manipuler les lignes.
• Les opérations du LMD couvrent la création, la lecture, l’actualisation et la suppression de lignes.
• Des SGBDR peuvent avoir des fonctions de même rôle mais des noms différents, comme pour les dates entre environnements SQL.

💡 Astuce mémo

LDD–LCD–LMD : Définir–Contrôler–Manipuler.

📖 3. Clés primaires et étrangères

🔑 Notions clés & Définitions

• Clé primaire : Identifiant d’une ligne dans une table, dont la valeur est unique et permet d’accéder à une ligne sans ambiguïté.
• Contrainte d’unicité : Propriété d’une clé primaire qui garantit qu’une valeur (ou combinaison) n’apparaît jamais deux fois dans la table.
• Clé artificielle : Colonne ajoutée à une table pour créer un identifiant unique sans signification métier obligatoire.
• Clé étrangère : Colonne(s) d’une table qui référence(nt) la clé primaire d’une autre table pour modéliser des liens entre lignes.

📝 Points essentiels

• Dans la plupart des SGBDR, chaque table doit avoir une clé primaire définie par au moins une combinaison de colonnes identifiant sûrement une ligne.
• Si plusieurs clés primaires sont possibles, on en choisit une, souvent la plus simple.
• Quand aucune colonne naturelle ne suffit, on peut utiliser une clé artificielle, dont le but est uniquement d’identifier de façon unique.
• Une clé étrangère référence une clé primaire et doit donc correspondre aussi en nombre de colonnes si la clé primaire est composée.
• Dans SQL, la clé primaire se définit avec PRIMARY KEY (PK) et la clé étrangère est marquée FK dans la logique de modélisation.

💡 Astuce mémo

PK pour “Propre et Unique”, FK pour “Fait Référence”.

📖 4. Sélection et filtrage avec SELECT

🔑 Notions clés & Définitions

• SELECT : Commande SQL qui indique les colonnes à renvoyer depuis une ou plusieurs tables.
• FROM : Clause SQL qui précise la ou les tables utilisées pour construire le résultat.
• WHERE : Clause SQL qui filtre les lignes pour ne garder que celles qui satisfont un critère.
• Fonction scalaire : Fonction SQL appliquée de façon identique à chaque valeur d’une colonne, produisant un résultat par ligne.

📝 Points essentiels

• Une requête SQL qui commence par SELECT renvoie toujours un résultat sous forme d’un tableau unique.
• Les mots clés essentiels d’une requête de base suivent SELECT pour choisir, FROM pour choisir les tables, et WHERE pour filtrer les lignes.
• Le symbole * dans SELECT signifie “toutes les colonnes disponibles” de la table.
• Les opérateurs de comparaison renvoient VRAI ou FAUX par ligne, et WHERE conserve uniquement les lignes évaluées à VRAI.
• Les fonctions peuvent être nommées différemment selon les SGBDR, donc on adapte l’écriture à l’outil utilisé.

💡 Astuce mémo

SELECT-FROM-WHERE = “choisir quoi”, “d’où”, “garder quoi”.

📖 5. Jointures et tables associatives

🔑 Notions clés & Définitions

• Jointure interne : Opération qui combine deux tables en ne gardant que les paires de lignes satisfaisant la condition de jointure.
• Jointure externe : Jointure qui peut conserver des lignes sans correspondance dans l’autre table selon le côté retenu.
• Jointure naturelle : Jointure qui peut simplifier l’écriture quand les clés jointes portent le même nom entre les tables.
• Table d’association : Table supplémentaire utilisée pour représenter une relation plusieurs-à-plusieurs entre deux tables.

📝 Points essentiels

• Une jointure interne correspond à un produit cartésien suivi d’un filtrage selon la condition de jointure.
• Quand on joint une clé étrangère d’une table A vers la clé primaire d’une table B, le résultat contient au plus autant de lignes que la table A.
• Une jointure se fait souvent avec une condition reliant une clé étrangère à une clé primaire, et si plusieurs colonnes interviennent on les relie avec AND.
• Pour une relation plusieurs-à-plusieurs, on crée une table d’association dont la clé primaire combine au moins les deux clés étrangères référentes.
• En jointure externe à gauche, on garde toutes les lignes de la table citée en premier, même sans correspondance dans la seconde table.

💡 Astuce mémo

1-*, la clé FK vit du côté “plusieurs”, et - exige une table d’association.

📖 6. Clés primaires des résultats

🔑 Notions clés & Définitions

• Clé primaire potentielle : Enjeu d’identification d’une clé d’un résultat intermédiaire, même si on ne la définit pas dans la base lors de la requête.
• Clé simplifiable : Clé potentielle dont une partie peut être réduite après filtrage, parce que les valeurs restantes déterminent encore de façon unique.
• Produit cartésien : Combinaison de lignes de deux tables qui produit un résultat dont la clé potentielle regroupe les clés des deux tables.

📝 Points essentiels

• Lors de requêtes avec SELECT puis des opérations comme jointures ou filtrages, il faut se demander quelle clé primaire pourrait exister si on stockait le résultat.
• Après un SELECT, si les colonnes sélectionnées contiennent une clé potentielle, alors le résultat peut conserver une clé primaire possible, sinon des doublons sont possibles.
• Le WHERE conserve la clé primaire dans la plupart des cas, mais peut la simplifier après filtrage quand une clé composée devient réductible.
• Le produit cartésien via FROM peut donner une clé potentielle composée du regroupement des clés des deux tables.
• Après une jointure, compter le nombre de lignes aide à détecter une clé supposée incorrecte qui propagerait ensuite une erreur.

💡 Astuce mémo

Clés potentielles : “si je stocke, quelle clé ?” puis “compte les lignes après jointure”.

📖 7. Agrégation avec GROUP BY et HAVING

🔑 Notions clés & Définitions

• Agrégation : Calcul qui produit un résultat basé sur plusieurs lignes, en résumant les valeurs de groupes.
• GROUP BY : Clause SQL qui forme des groupes de lignes partageant les mêmes valeurs sur des colonnes de partitionnement.
• HAVING : Clause SQL qui filtre des groupes après agrégation en utilisant les résultats de fonctions d’agrégation.
• Fonction d’agrégation : Fonction SQL qui prend en entrée une liste de valeurs d’un groupe et renvoie une valeur unique par groupe.

📝 Points essentiels

• L’agrégation se construit avec un partitionnement (GROUP BY) puis une ou plusieurs fonctions d’agrégation qui renvoient une valeur par groupe.
• La clé primaire du résultat d’une agrégation est composée des colonnes de partitionnement, parfois simplifiable.
• En SELECT, on peut utiliser une fonction d’agrégation seulement si toutes les autres colonnes sont soit agrégées, soit listées dans le GROUP BY.
• Le filtrage après agrégation se fait avec HAVING et non avec WHERE, car WHERE concerne les lignes avant agrégation.
• HAVING peut contenir une condition sur une fonction d’agrégation même si elle n’apparaît pas comme colonne calculée dans le SELECT.

💡 Astuce mémo

WHERE = avant, HAVING = après (après agrégation).

📖 8. Tri recherche et UNION

🔑 Notions clés & Définitions

• ORDER BY : Clause SQL qui ordonne les lignes d’un résultat selon une ou plusieurs colonnes.
• LIKE : Opérateur SQL permettant d’effectuer une recherche sur une chaîne avec des motifs.
• LOWER : Fonction scalaire qui convertit une chaîne de caractères en minuscules pour insensibiliser le tri ou la recherche à la casse.
• UNION : Opération SQL qui concatène les résultats de deux requêtes en ajoutant leurs lignes, sous contrainte de schéma compatible.

📝 Points essentiels

• ORDER BY trie par défaut dans l’ordre ascendant, et on peut forcer l’ordre descendant avec DESC.
• Pour trier sur plusieurs colonnes, on les sépare par des virgules dans la clause ORDER BY.
• LIKE utilise % pour 0, 1 ou plusieurs caractères inconnus et _ pour exactement un caractère inconnu.
• Pour éviter l’erreur de casse dans une recherche, on peut appliquer LOWER à la chaîne et comparer avec un motif en minuscules.
• Avec UNION, les deux requêtes doivent avoir le même schéma (mêmes colonnes et types) pour concaténer correctement les résultats.

💡 Astuce mémo

% = beaucoup, _ = un seul, ORDER BY = “trie par ordre”.

📊 Tableaux de synthèse

Jointures : objectif et conservation

⚠️ Pièges & confusions fréquents

1. Confondre la condition de jointure d’une jointure interne avec un simple WHERE : la jointure combine d’abord puis filtre selon la condition de jointure.
2. Oublier qu’un SELECT sans clé potentielle dans les colonnes choisies peut produire des doublons dans le résultat final.
3. Croire que HAVING se remplace par WHERE pour filtrer après agrégation : WHERE ne s’applique pas aux groupes produits par GROUP BY.
4. Se tromper de SGBDR sur les fonctions : une fonction comme date() peut avoir un autre nom ailleurs.
5. Faire une UNON avec des schémas différents : les colonnes et types doivent correspondre pour que l’union soit valide.
6. Utiliser une jointure naturelle sans vérifier les noms : si d’autres colonnes portent le même nom, elles peuvent être incluses à la condition de jointure.

✅ Checklist Examen

1. Définir à quoi sert la modélisation relationnelle et reconnaître une table (lignes=objets, colonnes=caractéristiques).
2. Expliquer pourquoi SGBD/SGBDR sont nécessaires et relier le rôle du SGBDR à l’accès aux données.
3. Citer les trois familles SQL (LDD, LCD, LMD) et associer chacune à sa finalité.
4. Déterminer ce qu’est une clé primaire, sa contrainte d’unicité, et expliquer le cas d’une clé artificielle.
5. Justifier ce qu’est une clé étrangère et préciser la règle de correspondance quand la clé primaire est composée de plusieurs colonnes.
6. Choisir la bonne structure pour une association un-à-un, un-à-plusieurs et plusieurs-à-plusieurs, incluant l’idée de table d’association.
7. Reconnaître la structure d’une requête SELECT-FROM-WHERE et interpréter le rôle de WHERE via VRAI/FAUX.
8. Expliquer la différence de logique entre jointure interne, jointure externe gauche et jointure externe droite, en lien avec les lignes conservées.
9. Déterminer la clé primaire potentielle d’un résultat après SELECT, WHERE et jointure, et relier cela au risque de doublons.
10. Construire une agrégation avec GROUP BY et une fonction d’agrégation, puis appliquer un filtrage de groupes avec HAVING.
11. Expliquer les règles d’usage : fonctions d’agrégation dans SELECT uniquement si les autres colonnes sont agrégées ou dans GROUP BY.
12. Maîtriser ORDER BY (ASC/DESC, tri sur plusieurs colonnes) et LIKE (% et _), puis utiliser LOWER pour traiter la casse.
13. Conclure sur UNION : concaténation des résultats en exigeant le même schéma entre les requêtes.