Revision sheet: Introduction aux Bases de Données Relationnelles

📋 Plan du Cours

1. SGBD relationnels et SQL
2. Architecture trois-tiers
3. Schéma relationnel et clés
4. Opérations relationnelles
5. Requêtes SQL de sélection
6. Jointures et agrégations
7. Insertion, modification et suppression
8. Contraintes d'intégrité

📖 1. SGBD relationnels et SQL

🔑 Notions clés & Définitions

• Base de données : Une base de données est une structure de données accompagnée d’un langage permettant d’y accéder en lecture et en écriture.
• SGBD : Un SGBD (ou DBMS) est le logiciel qui stocke les données, traite efficacement les requêtes, gère les accès concurrents et sécurise les droits.
• SGBDR : Un SGBDR est un SGBD de type relationnel qui s’appuie le plus souvent sur le langage SQL pour interroger les données.
• SQL : Le SQL (Structured Query Language) est le langage standard permettant d’écrire des requêtes sur une base de données.
• SQLite : SQLite est un SGBD léger permettant de manipuler localement des bases de données sans passer par un serveur.

📝 Points essentiels

• Un SGBD gère notamment des accès concurrents, par exemple quand plusieurs utilisateurs se connectent en même temps pour la même opération.
• Les catégories de bases de données incluent des bases relationnelles et des bases non relationnelles.
• Les SGBDR utilisent le plus souvent SQL pour interroger et agir sur les données de manière structurée.
• SQLite correspond à une manipulation locale sans serveur et a une première version en 2000.
• MySQL a été lancé en 1995 et a été racheté par Oracle en 2008, ce qui a conduit à la création de MariaDB en 2009.

📖 2. Architecture trois-tiers

🔑 Notions clés & Définitions

• Client : Un client est un logiciel ou un ordinateur qui accède à une base de données située sur un serveur distant.
• Serveur : Un serveur est le logiciel qui héberge une base de données, sur l’ordinateur qui la contient.
• Couche présentation : La couche présentation est le niveau visible côté utilisateur, qui envoie les requêtes et reçoit les réponses.
• Couche métier : La couche métier est le niveau fonctionnel qui traite les données et communique avec les autres couches.
• Couche accès aux données : La couche accès aux données est le niveau qui gère l’accès au stockage des données et le transmet à la couche métier.

📝 Points essentiels

• Dans une architecture trois-tiers, chaque couche peut être gérée indépendamment par trois ordinateurs ou trois fonctions d’un même programme.
• Dans un système client-serveur, le client gère la présentation et une partie du métier, tandis que le serveur gère l’accès aux données et une partie du métier.
• Les trois couches sont présentation, métier, puis accès aux données, avec des échanges de requêtes et de réponses entre niveaux.

📖 3. Schéma relationnel et clés

🔑 Notions clés & Définitions

• Relation : Une relation est une table décrite par un nom et un ensemble de colonnes appelées attributs.
• Attribut : Un attribut est une colonne d’une table, définie par un nom et un type ou un domaine de valeurs possibles.
• Tuple : Un tuple est une ligne d’une relation, correspondant à un enregistrement concret.
• Schéma relationnel : Le schéma relationnel est l’ensemble des schémas de toutes les relations d’une base, précisant noms d’attributs et domaines.
• Clé primaire : Une clé primaire est un ensemble d’attributs permettant d’identifier chaque tuple de façon unique.

📝 Points essentiels

• Deux colonnes d’une même table doivent avoir des noms différents pour éviter toute ambiguïté de structure.
• Le schéma relationnel peut être vu comme une table vide à partir des noms d’attributs et des types ou domaines.
• La clé primaire est souvent un identifiant entier auto-incrémenté, mais elle peut aussi être constituée de plusieurs champs.
• Le schéma d’une relation associe chaque attribut à un domaine, par exemple id : int ou nom : varchar(30).
• Le schéma relationnel d’une base est l’ensemble des schémas des tables, donc la structure globale de la base.

📖 4. Opérations relationnelles

🔑 Notions clés & Définitions

• Projection : La projection est une opération qui produit une copie d’une relation ne contenant que certains attributs choisis.
• Sélection : La sélection est une opération qui renvoie uniquement les tuples d’une relation qui vérifient une condition.
• Produit cartésien : Le produit cartésien combine deux relations en accolant leurs colonnes et en listant toutes les concaténations de tuples possibles.
• Jointure : La jointure est l’opération qui combine un produit cartésien et une sélection pour relier deux tables sur un critère commun.

📝 Points essentiels

• La projection conserve des colonnes et élimine les autres colonnes, par exemple Eleve sur {nom, prenom} garde uniquement nom et prenom.
• La sélection garde des lignes, par exemple Eleve avec atelier=3 renvoie les élèves inscrits à l’atelier 3.
• La jointure sert à relier deux tables en pratique via une égalité entre un attribut d’une table et une clé de l’autre.
• Le produit cartésien génère en pratique beaucoup de combinaisons sans sens, d’où l’intérêt d’une sélection ou d’une jointure.

📖 5. Requêtes SQL de sélection

🔑 Notions clés & Définitions

• Requête SELECT : Une requête SQL SELECT sert à interroger une ou plusieurs relations pour produire une table de résultat.
• Clause WHERE : La clause WHERE spécifie la condition de sélection appliquée avant la projection finale demandée par SELECT.
• DISTINCT : DISTINCT élimine les doublons dans le résultat lorsqu’on projette des attributs.
• Renommage de tables : Le renommage permet d’assigner un alias à une table pour simplifier la requête et éviter des ambigüités.
• Opérateurs de comparaison : Les opérateurs de comparaison et de motifs servent dans WHERE à exprimer égalités, intervalles, appartenance à une liste et tests de chaînes.

📝 Points essentiels

• L’ordre d’exécution d’une requête de forme SELECT…FROM…WHERE est FROM puis WHERE puis SELECT.
• SELECT nom, prenom FROM Eleve WHERE classe = 2 renvoie les élèves de la classe 2, et un élève inscrit plusieurs fois peut apparaître plusieurs fois.
• SELECT DISTINCT nom, prenom permet d’éviter les doublons dans le résultat d’une requête sur des élèves ayant plusieurs inscriptions.
• SQL autorise dans WHERE les conditions avec AND, OR, NOT, BETWEEN, IN, LIKE, IS NULL et IS NOT NULL.
• On n’écrit pas attribut = NULL ni attribut <> NULL, et le symbole pour différent est <> en SQL.

📖 6. Jointures et agrégations

🔑 Notions clés & Définitions

• JOIN : JOIN est une syntaxe SQL explicite pour réaliser une jointure entre tables sur un critère fourni après ON.
• Fonction d’agrégation : Une fonction d’agrégation prend une collection de valeurs et produit une seule valeur de synthèse.
• COUNT : COUNT est une fonction d’agrégation qui renvoie le nombre de lignes ou le nombre de valeurs non NULL d’une colonne.
• HAVING : HAVING est utilisé pour filtrer après agrégation, selon les cas, au même titre que SELECT après les agrégations.

📝 Points essentiels

• La syntaxe actuelle d’une jointure est SELECT … FROM Eleve JOIN Atelier ON Eleve.atelier = Atelier.id.
• JOIN peut être suivie d’une clause WHERE pour appliquer une sélection supplémentaire après la jointure.
• Les fonctions d’agrégation du cours sont AVG, COUNT, MAX, MIN, SUM.
• COUNT(*) renvoie le nombre de lignes et COUNT(A) renvoie le nombre de lignes dont A n’est pas NULL.
• Une fonction d’agrégation s’utilise après SELECT ou HAVING (les filtrages avec WHERE peuvent utiliser un sous-SELECT qui ne renvoie qu’une valeur).

📖 7. Insertion, modification et suppression

🔑 Notions clés & Définitions

• INSERT INTO : INSERT INTO est l’instruction SQL permettant d’ajouter un ou plusieurs enregistrements dans une table.
• UPDATE : UPDATE est l’instruction SQL qui modifie une ou plusieurs valeurs d’attributs d’une table selon une condition.
• DELETE FROM : DELETE FROM est l’instruction SQL qui supprime des enregistrements d’une table selon une condition.

📝 Points essentiels

• INSERT INTO table VALUES (…) ajoute des lignes, et on peut soit fournir tous les attributs dans l’ordre, soit préciser la liste des attributs.
• Avec INSERT, on peut saisir dans le désordre et ne pas entrer les colonnes prenant la valeur NULL lorsque c’est autorisé par le schéma.
• UPDATE table SET attribut = valeur WHERE condition modifie uniquement les tuples qui vérifient la condition.
• DELETE FROM table WHERE condition supprime uniquement les tuples satisfaisant la condition, comme supprimer les ateliers où nbinscrit = 0.

📖 8. Contraintes d'intégrité

🔑 Notions clés & Définitions

• Contrainte de domaine : Une contrainte de domaine impose que les valeurs d’un attribut appartiennent à un ensemble ou intervalle autorisé.
• CHECK : CHECK est une contrainte permettant d’exprimer une condition logique sur une valeur d’attribut lors de la création ou via ALTER TABLE.
• NOT NULL : NOT NULL impose qu’un attribut soit obligatoirement renseigné et interdit les valeurs NULL.
• Clé étrangère : Une clé étrangère est un attribut (ou ensemble d’attributs) faisant référence à la clé primaire d’une autre table pour assurer la cohérence.
• Intégrité référentielle : L’intégrité référentielle garantit que les références entre tables restent valides lors des insertions, suppressions et modifications.

📝 Points essentiels

• Une contrainte CHECK peut encadrer un attribut, par exemple nbplace entre 10 et 20, soit dans la création soit via ALTER TABLE.
• En MySQL, la suppression et certains ajouts de contraintes CHECK se font avec ALTER TABLE, et le système peut nécessiter le nom explicite.
• NOT NULL empêche qu’un champ prenne la valeur NULL et force la présence d’une valeur pour l’attribut concerné.
• Le SGBD refuse l’insertion si la clé primaire est dupliquée, car chaque tuple doit respecter l’unicité de la clé primaire.
• Avec une clé étrangère, le SGBD empêche d’ajouter une ligne de référence inexistante, et ON DELETE CASCADE peut supprimer automatiquement les lignes dépendantes.

📅 Repères chronologiques

Date	Événement
1995	Lancement de MySQL
2008	Oracle achète MySQL
2009	Création de MariaDB
1979	Première version du SGBD Oracle sortie
1977	Création de la société Oracle Corporation
1985	Initiation du projet Postgres par Michael Stonebraker
1996	Première version publiée sous le nom PostgreSQL
2000	Première version de SQLite

📊 Tableaux de synthèse

SGBD ouverts vs propriétaires

Catégorie	Exemples	Caractéristique
Open-source	MySQL, MariaDB, PostgreSQL, SQLite	Logiciels accessibles et utilisés dans divers contextes (dont SQLite en local).
Propriétaires	Oracle, Microsoft SQL Server, Microsoft Access	Produits édités par des sociétés (Oracle, Microsoft) et souvent fournis sous licence.

⚠️ Pièges & confusions fréquents

1. Confondre la projection et la sélection : la projection garde des colonnes alors que la sélection filtre des lignes.
2. Écrire une condition de NULL comme attribut = NULL ou attribut <> NULL au lieu d’utiliser IS NULL ou IS NOT NULL.
3. Oublier DISTINCT quand une jointure peut produire plusieurs lignes identiques pour la même paire nom/prenom.
4. Penser que COUNT(A) compte aussi les NULL : COUNT(A) ne compte que les valeurs non NULL.
5. Confondre la jointure et le produit cartésien : le produit cartésien seul ne “relie” pas, il génère juste toutes les combinaisons.
6. Croire que la clé primaire n’a d’intérêt que pour l’affichage : le SGBD l’utilise pour refuser les doublons lors des insertions.

✅ Checklist Examen

1. Définir ce qu’est une base de données et ce que fait un SGBD (stockage, traitement des requêtes, accès concurrents, sécurité par droits).
2. Citer plusieurs SGBD relationnels et donner au moins l’idée de ce que change un SGBD léger comme SQLite.
3. Expliquer le rôle des trois couches d’une architecture trois-tiers et dire ce qui est géré côté client vs côté serveur.
4. Décrire la différence entre attribut, tuple, schéma relationnel et schéma relationnel de la base.
5. Donner ce qu’est une clé primaire et expliquer le cas typique d’un id entier auto-incrémenté.
6. Savoir quand utiliser projection, sélection, produit cartésien et jointure, et reconnaître la jointure comme produit cartésien + sélection.
7. Reconnaître la structure générale d’une requête SELECT…FROM…WHERE et l’ordre FROM puis WHERE puis SELECT.
8. Construire une requête de sélection avec WHERE comprenant au moins AND, OR, NOT et l’un des opérateurs BETWEEN, IN, LIKE, IS NULL.
9. Éviter les erreurs sur NULL et savoir écrire correctement IS (NOT) NULL en SQL.
10. Réaliser une jointure avec la syntaxe JOIN … ON … et comprendre que WHERE peut filtrer en plus de la jointure.
11. Utiliser correctement les fonctions AVG, COUNT, MAX, MIN, SUM, notamment les différences entre COUNT(*) et COUNT(A).
12. Écrire une insertion avec INSERT INTO avec ou sans liste d’attributs, une modification avec UPDATE … SET … WHERE … et une suppression avec DELETE FROM … WHERE ….
13. Décrire les contraintes d’intégrité : CHECK (bornes), NOT NULL, contrainte de clé primaire (unicité) et contrainte de clé étrangère (références valides, éventuellement ON DELETE CASCADE).