DESCRIPTION DES CONCEPTS DES BASE DE DONNEES

## Objectifs et prérequis **Objectifs** : À la fin de ce chapitre, l’élève sera capable de : 1. Définir précisément ce qu’est une base de données (BD) et en distinguer les différents types (hiérarchique, réseau, relationnelle, objet, NoSQL). 2. Expliquer la notion de table dans le modèle relationnel, identifier ses composantes (attributs, tuples, clé primaire, clé étrangère) et justifier l’importance de l’intégrité référentielle. 3. Décrire le rôle d’un Système de Gestion de Bases de Données (SGBD), ses fonctions principales (définition, manipulation, contrôle, transaction) et son architecture à trois niveaux. 4. Manipuler le langage SQL de base (DDL : CREATE, ALTER, DROP ; DML : SELECT, INSERT, UPDATE, DELETE) pour créer une table, y insérer des données et effectuer des requêtes simples. 5. Résoudre des exercices de type baccalauréat camerounais portant sur la modélisation de données simples et l’interprétation de schémas relationnels. **Prérequis** : - Notions élémentaires de logique propositionnelle et de ensembles (union, intersection, différence). - Maîtrise de la lecture et de l’écriture d’algorithmes simples (pseudo‑code). - Familiarité avec l’utilisation d’un tableur (notion de lignes, colonnes, cellule). --- ## Introduction contextualisée Dans le monde contemporain, l’information constitue une ressource stratégique. Que ce soit pour gérer les inscriptions scolaires au lycée, suivre les stocks d’une entreprise commerciale ou conserver les dossiers médicaux d’un hôpital, il faut pouvoir stocker, récupérer et mettre à jour de grandes quantités de données de façon fiable et rapide. Avant l’avènement des bases de données informatisées, les informations étaient consignées sur des fichiers papier ou des fichiers plats (texte simple). Cette approche présentait de nombreux inconvénients : redondance des données, difficulté à assurer la cohérence, temps de recherche élevé, absence de mécanismes de sécurité et de concurrence. Le modèle relationnel, introduit par Edgar F. Codd en 1970, a révolutionné la gestion de l’information en proposant une représentation structurée sous forme de tables liées par des clés. Ce modèle a donné naissance aux SGBD relationnels (Oracle, MySQL, PostgreSQL, SQL Server, etc.) qui sont aujourd’hui les outils les plus répandus dans l’enseignement secondaire et supérieur. Ce chapitre vise à fournir à l’élève une compréhension profonde et opérationnelle des trois concepts fondamentaux du programme : la base de données, la table et le SGBD. Chaque notion sera définie, illustrée par des exemples concrets, puis approfondie par des démonstrations et des exercices corrigés. --- ## I. Notion de base de données ### A. Définition > **Une base de données (BD) est un ensemble organisé de données liées, stockées de manière permanente sur un support informatique, permettant un accès efficace, une mise à jour cohérente et une gestion centralisée de l’information.** Cette définition implique plusieurs propriétés essentielles : 1. **Organisation** : les données ne sont pas disposées au hasard ; elles suivent un schéma (structure logique) précisé par le concepteur. 2. **Permanence** : les données restent stockées même après l’arrêt de l’application qui les a produites. 3. **Accès efficace** : des mécanismes d’indexation et d’optimisation permettent de retrouver rapidement les enregistrements recherchés. 4. **Cohérence** : des contraintes d’intégrité (unicité, nullité, référentiel) empêchent l’insertion de données contradictoires. 5. **Gestion centralisée** : un seul référent (le SGBD) assure la sécurité, la sauvegarde et la restauration. ### B. Caractéristiques fondamentales | Caractéristique | Description | Exemple | |-----------------|-------------|---------| | **Indépendance des données** | Séparation entre le niveau logique (schéma) et le niveau physique (stockage). Modifier le stockage n’affecte pas les applications. | Passage d’un disque magnétique à un SSD sans changer les requêtes SQL. | | **Intégrité** | Garantie que les données respectent les règles définies (clé primaire, contraintes de vérification). | Empêcher qu’un élève ait deux numéros d’inscription différents. | | **Sécurité** | Contrôle d’accès (authentification, autorisation) et chiffrement. | Seul le professeur peut modifier les notes. | | **Concurrence** | Plusieurs utilisateurs peuvent accéder simultanément sans corrompre les données. | Deux secrétaires inscrivent des élèves en même temps. | | **Sauvegarde et récupération** | Possibilité de restaurer la BD à un état antérieur en cas de panne. | Restauration après une coupure d’électricité. | ### C. Types de bases de données 1. **Base de données hiérarchique** : structure en arbre, chaque nœud a un seul parent (ex. : système de fichiers classiques). 2. **Base de données réseau** : graphe où un nœud peut avoir plusieurs parents (ex. : modèle CODASYL). 3. **Base de données relationnelle** (la plus utilisée au lycée) : données organisées en tables (relations) liées par des clés. 4. **Base de données orientée objet** : les données sont encapsulées dans des objets possédant attributs et méthodes. 5. **Base de données NoSQL** : modèles variés (clé‑valeur, document, colonne large, graphe) adaptés au big data et aux applications web scalables. > **À retenir** : Au niveau de la Première (séries C, D, E), le programme se concentre sur le **modèle relationnel** ; les autres modèles sont présentés uniquement à titre de comparaison. ### D. Démonstration : pourquoi le modèle relationnel évite la redondance Considérons un fichier plat contenant les informations suivantes pour chaque élève : ``` Nom, Prénom, DateNaissance, Classe, ProfesseurPrincipal, TéléphoneProf ``` Si deux élèves appartiennent à la même classe et ont le même professeur principal, les champs *Classe*, *ProfesseurPrincipal* et *TéléphoneProf* sont répétés pour chaque ligne. Cette redondance entraîne : - **Consommation d’espace** inutile. - **Risque d’incohérence** : si le téléphone du professeur change, il faut mettre à jour toutes les lignes où il apparaît. Dans le modèle relationnel, on décompose l’information en trois tables : - **Eleve** (NumEleve, Nom, Prénom, DateNaissance, CodeClasse) - **Classe** (CodeClasse, Libellé, CodeProf) - **Professeur** (CodeProf, Nom, Prénom, Téléphone) Les tables sont liées par les clés étrangères *CodeClasse* et *CodeProf*. Ainsi, chaque donnée n’est stockée qu’une seule fois ; la mise à jour du téléphone du professeur nécessite une seule modification dans la table *Professeur*. ### E. Exemples entièrement résolus #### Exemple 1 – Identification d’une base de données **Énoncé** : Un lycée souhaite gérer les informations suivantes : les élèves (nom, prénom, date de naissance, classe), les professeurs (nom, prénom, spécialité, téléphone) et les matières (nom, coefficient). Déterminez si cet ensemble constitue une base de données au sens du cours. **Résolution** : 1. **Ensemble organisé** : les informations sont regroupées par entité (élève, professeur, matière) et chaque entité possède un ensemble d’attributs bien défini. 2. **Stockage permanent** : supposons que les données seront enregistrées sur un serveur scolaire. 3. **Accès efficace** : grâce à un SGBD, on pourra rechercher rapidement tous les élèves d’une classe donnée ou le téléphone d’un professeur. 4. **Cohérence** : on pourra imposer des contraintes (ex. : la date de naissance doit être antérieure à la date d’inscription, le téléphone doit contenir exactement 9 chiffres). 5. **Gestion centralisée** : un seul SGBD administre l’ensemble. Puisque toutes les propriétés de la définition sont satisfaites, **oui**, cet ensemble constitue une base de données. #### Exemple 2 – Détection d’une non‑BD **Énoncé** : Un professeur conserve les notes de ses élèves dans un cahier papier, où chaque page correspond à un contrôle et contient la liste des notes dans l’ordre alphabétique des noms. Peut‑on considérer ce cahier comme une base de données ? **Résolution** : - Le cahier stocke des données de façon permanente (oui). - Cependant, l’accès n’est pas efficace : pour trouver la note d’un élève à un contrôle précis, il faut feuilleter le cahier jusqu’à la bonne page, puis parcourir la liste jusqu’au nom recherché (complexité linéaire). - Il n’existe pas de mécanisme d’intégrité automatisé : rien n’empêche d’insérer deux notes pour le même élève au même contrôle ou d’oublier une note. - Il n’y a pas de gestion centralisée ni de sécurité (le cahier peut être perdu, lu par n’importe qui). Puisque plusieurs propriétés essentielles font défaut, **ce cahier ne constitue pas une base de données** au sens informatique du terme. --- ## II. Notion de table ### A. Définition > **Dans le modèle relationnel, une table (ou relation) est un ensemble non ordonné de tuples (lignes) partageant le même ensemble d’attributs (colonnes), chaque attribut étant associé à un type de donnée et éventuellement à des contraintes d’intégrité.** Une table se définit donc par : - Son **nom** (ex. : `Eleve`). - Son **schéma** : la liste ordonnée de ses attributs avec leurs types (ex. : `NumEleve INTEGER`, `Nom VARCHAR(30)`). - Son **extension** : l’ensemble actuel des tuples qui peuplent la table. ### B. Structure d’une table | Élément | Description | Notation usuelle | |---------|-------------|------------------| | **Attribut (colonne)** | Caractéristique décrivant les entités (ex. : `DateNaissance`). | `A_i` | | **Tuple (ligne)** | Enregistrement représentant une instance d’entité (ex. : un élève particulier). | `t` | | **Degré** | Nombre d’attributs de la table. | `deg(R) = n` | | **Cardinalité** | Nombre de tuples actuellement présents. | `card(R) = m` | | **Clé primaire (PK)** | Attribut ou ensemble d’attributs qui identifie de façon unique chaque tuple. | `PK(R) = {A_j}` | | **Clé étrangère (FK)** | Attribut qui fait référence à la clé primaire d’une autre table, assurant l’intégrité référentielle. | `FK(R) → PK(S)` | | **Contrainte d’unicité** | Aucun deux tuples ne peuvent avoir la même valeur pour l’attribut ou l’ensemble d’attributs concerné. | `UNIQUE(A_k)` | | **Contrainte de non‑null (NOT NULL)** | L’attribut ne peut pas prendre la valeur NULL. | `NOT NULL(A_l)` | | **Contrainte de vérification (CHECK)** | Condition logique que la valeur doit satisfaire. | `CHECK(A_m > 0)` | > **À retenir** : La clé primaire doit être **minimale** (aucun sous‑ensemble ne peut également identifier unique­ment les tuples) et **stable** (sa valeur ne devrait pas changer au cours de la vie du tuple). ### C. Types de données usuels en SQL | Type SQL | Description | Exemple de valeurs | |----------|-------------|--------------------| | `INTEGER` | Nombre entier signé (généralement 32 bits). | `42`, `-7` | | `DECIMAL(p,s)` ou `NUMERIC(p,s)` | Nombre à virgule fixe, `p` chiffres au total, `s` après la virgule. | `DECIMAL(5,2)` → `123.45` | | `VARCHAR(n)` | Chaîne de caractères de longueur variable, max `n`. | `VARCHAR(20)` → `'Cameroun'` | | `CHAR(n)` | Chaîne de longueur fixe `n` (remplie d’espaces si nécessaire). | `CHAR(5)` → `'AB '` | | `DATE` | Date calendaire (année, mois, jour). | `'2005-08-15'` | | `TIME` | Heure de la journée (heure, minute, seconde). | `'14:30:00'` | | `TIMESTAMP` | Date et heure combinées. | `'2023-09-20 08:15:00'` | | `BOOLEAN` | Valeur logique vraie/fausse. | `TRUE`, `FALSE` | | `TEXT` | Chaîne de longueur potentiellement très grande (pas de limite prédéfinie). | Un paragraphe de description. | ### D. Contraintes d’intégrité 1. **Intégrité d’entité** : assurée par la clé primaire (pas de tuples dupliqués, pas de NULL dans les attributs de la PK). 2. **Intégrité référentielle** : assurée par les clés étrangères ; chaque valeur de FK doit soit être NULL, soit correspondre à une valeur existante de la PK référencée. 3. **Intégrité de domaine** : chaque attribut doit respecter son type et ses éventuelles contraintes CHECK. 4. **Intégrité définie par l’utilisateur** : règles spécifiques au métier (ex. : la date d’inscription doit être postérieure à la date de naissance). ### E. Exemples entièrement résolus #### Exemple 1 – Création d’une table simple **Énoncé** : Créer une table `Eleve` comportant les attributs suivants : - `NumEleve` (entier, clé primaire) - `Nom` (chaîne de 30 caractères, non nul) - `Prenom` (chaîne de 30 caractères, non nul) - `DateNaissance` (date, non nul) - `Sexe` (caractère, limité à `'M'` ou `'F'`) **Résolution** : ```sql CREATE TABLE Eleve ( NumEleve INTEGER PRIMARY KEY, Nom VARCHAR(30) NOT NULL, Prenom VARCHAR(30) NOT NULL, DateNaissance DATE NOT NULL, Sexe CHAR(1) CHECK (Sexe IN ('M','F')) ); ``` **Justification** : - `PRIMARY KEY` sur `NumEleve` garantit l’unicité et l’absence de NULL. - `NOT NULL` sur `Nom` et `Prenom` interdit les valeurs vides. - `CHECK` sur `Sexe` restreint le domaine aux deux valeurs autorisées. #### Exemple 2 – Table avec clé étrangère **Énoncé** : On possède déjà la table `Classe` définie comme : ```sql CREATE TABLE Classe ( CodeClasse CHAR(3) PRIMARY KEY, Libelle VARCHAR(20) NOT NULL, Niveau VARCHAR(10) NOT NULL ); ``` Créer une table `Inscription` qui relie un élève à une classe pour une année scolaire donnée, avec les attributs : - `NumEleve` (clé étrangère vers `Eleve.NumEleve`) - `CodeClasse` (clé étrangère vers `Classe.CodeClasse`) - `AnneeScolaire` (exemple `'2023-2024'`, non nul) - `DateInscription` (date, non nul) La combinaison `(NumEleve, CodeClasse, AnneeScolaire)` doit être unique. **Résolution** : ```sql CREATE TABLE Inscription ( NumEleve INTEGER NOT NULL, CodeClasse CHAR(3) NOT NULL, AnneeScolaire VARCHAR(9) NOT NULL, DateInscription DATE NOT NULL, PRIMARY KEY (NumEleve, CodeClasse, AnneeScolaire), FOREIGN KEY (NumEleve) REFERENCES Eleve(NumEleve), FOREIGN KEY (CodeClasse) REFERENCES Classe(CodeClasse) ); ``` **Justification** : - La **clé primaire composée** garantit qu’un élève ne peut être inscrit deux fois dans la même classe pour la même année scolaire. - Les deux `FOREIGN KEY` assurent l’intégrité référentielle : on ne peut inscrire un élève qui n’existe pas dans `Eleve`, ni une classe qui n’existe pas dans `Classe`. - Tous les attributs sont déclarés `NOT NULL` afin d’éviter les valeurs manquantes qui rendraient la relation ambiguë. #### Exemple 3 – Insertion et sélection **Énoncé** : Insérer les enregistrements suivants dans la table `Eleve` : | NumEleve | Nom | Prenom | DateNaissance | Sexe | |----------|-----------|----------|---------------|------| | 1 | Kamga | Jean | 2005-03-12 | M | | 2 | Mbappe | Alice | 2006-07-24 | F | | 3 | Nkoumou | Paul | 2005-11-05 | M | Puis effectuer une requête qui retourne le nom et le prénom des élèves nés après le 1er janvier 2006, triés par nom décroissant. **Résolution** : ```sql -- Insertion INSERT INTO Eleve (NumEleve, Nom, Prenom, DateNaissance, Sexe) VALUES (1, 'Kamga', 'Jean', '2005-03-12', 'M'), (2, 'Mbappe', 'Alice', '2006-07-24', 'F'), (3, 'Nkoumou', 'Paul', '2005-11-05', 'M'); -- Sélection SELECT Nom, Prenom FROM Eleve WHERE DateNaissance > '2006-01-01' ORDER BY Nom DESC; ``` **Résultat attendu** : | Nom | Prenom | |--------|--------| | Mbappe | Alice | **Explication** : - La clause `WHERE` filtre uniquement les tuples dont `DateNaissance` est strictement postérieure au 1er janvier 2006. Seule la ligne de Alice Mbappe satisfait cette condition. - `ORDER BY Nom DESC` trie le résultat par ordre alphabétique inverse ; comme il n’y a qu’un seul résultat, l’ordre reste inchangé. --- ## III. Notion de SGBD ### A. Définition > **Un Système de Gestion de Bases de Données (SGBD) est un logiciel intermédiaire qui permet de définir, manipuler, sécuriser et administrer une ou plusieurs bases de données, tout en offrant une abstraction indépendante du matériel de stockage.** Le SGBD assure ainsi la séparation entre : - Le **niveau externe** (vue des utilisateurs ou des applications). - Le **niveau conceptuel** (schéma logique global, indépendant des préoccupations de performance). - Le **niveau interne** (organisation physique des données sur le disque : fichiers, index, méthodes d’accès). ### B. Fonctions principales d’un SGBD | Fonction | Description | Exemple d’opération SQL | |----------|-------------|--------------------------| | **Définition de données (DDL)** | Création, modification, suppression des structures (schémas, tables, vues, index). | `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE` | | **Manipulation de données (DML)** | Lecture, insertion, mise à jour, suppression des tuples. | `SELECT`, `INSERT`, `UPDATE`, `DELETE` | | **Contrôle des données (DCL)** | Gestion des droits d’accès et des rôles. | `GRANT SELECT ON Eleve TO professeur;` | | **Gestion des transactions (TCL)** | Définition de points de sauvegarde, validation ou annulation de groupes d’opérations. | `BEGIN TRANSACTION; …; COMMIT;` ou `ROLLBACK;` | | **Utilitaire** | Sauvegarde, restauration, monitoring, optimisation. | `BACKUP DATABASE`, `ANALYZE TABLE` | > **À retenir** : Le SGBD garantit les propriétés **ACID** (Atomicité, Cohérence, Isolation, Durabilité) des transactions. ### C. Architecture à trois niveaux (ANSI/SPARC) 1. **Niveau externe** : - Vue personnalisée pour chaque catégorie d’utilisateur (élève, professeur, administrateur). - Définit quelles données sont visibles et sous quelle forme. - Exemple : une vue `VueEleveDeTerminale` qui ne montre que les élèves de la classe `Terminale`. 2. **Niveau conceptuel** : - Schéma logique global de la base de données, indépendant des préoccupations de performance. - Contient toutes les tables, leurs attributs, les clés primaires/étrangères, les contraintes. - Exemple : le schéma comprenant les tables `Eleve`, `Classe`, `Inscription`. 3. **Niveau interne** : - Description de la façon dont les données sont stockées physiquement (fichiers, pages, index, méthodes d’accès comme B‑tree, hash). - Gère l’allocation d’espace, la compression, le chiffrement. - Exemple : un index B‑tree sur l’attribut `NumEleve` pour accélérer les recherches. > **À retenir** : L’indépendance des données provient du fait qu’une modification au niveau interne (ex. : passage d’un fichier séquentiel à un index B‑tree) n’affecte ni les vues externes ni le schéma conceptuel. ### D. Langages du SGBD | Langage | Objectif | Exemple | |---------|----------|---------| | **DDL (Data Definition Language)** | Définir la structure. | `CREATE TABLE Eleve (…);` | | **DML (Data Manipulation Language)** | Lire et modifier les données. | `SELECT Nom FROM Eleve WHERE Sexe='F';` | | **DCL (Data Control Language)** | Gérer les privilèges. | `REVOKE INSERT ON Eleve FROM stagiaire;` | | **TCL (Transaction Control Language)** | Contrôler les transactions. | `SAVEPOINT sp1; …; ROLLBACK TO sp1;` | | **DQL (Data Query Language)** – souvent considéré comme partie du DML – | Spécifique aux requêtes de sélection. | `SELECT * FROM Eleve ORDER BY Nom;` | ### E. Exemples entièrement résolus #### Exemple 1 – Création d’une vue (niveau externe) **Énoncé** : À partir des tables `Eleve` (NumEleve, Nom, Prenom, DateNaissance, Sexe) et `Inscription` (NumEleve, CodeClasse, AnneeScolaire, DateInscription), créer une vue nommée `VueElevesDe2023` qui affiche le nom, le prénom et la date de naissance des élèves inscrits pour l’année scolaire 2023‑2024. **Résolution** : ```sql CREATE VIEW VueElevesDe2023 AS SELECT e.Nom, e.Prenom, e.DateNaissance FROM Eleve e JOIN Inscription i ON e.NumEleve = i.NumEleve WHERE i.AnneeScolaire = '2023-2024'; ``` **Justification** : - La vue définit une **requête stockée** ; son résultat n’est pas matérialisé sauf si le SGBD décide de le faire (vue matérialisée). - Elle appartient au **niveau externe** : elle présente une sous‑partie du schéma conceptuel adaptée à un besoin spécifique (liste des inscrits 2023‑2024). - Toute modification des tables de base (`Eleve` ou `Inscription`) sera immédiatement reflétée dans la vue, garantissant la cohérence. #### Exemple 2 – Gestion d’une transaction avec sauvegarde **Énoncé** : Un professeur veut ajouter un nouvel élève (`NumEleve=4`, `Nom='Tchinda'`, `Prenom='Laura'`, `DateNaissance='2005-09-01'`, `Sexe='F'`) et l’inscrire dans la classe `CodeClasse='T01'` pour l’année `2023-2024`. Si l’insertion dans la table `Inscription` échoue (par exemple parce que la classe n’existe pas), il souhaite annuler uniquement l’insertion dans `Eleve` mais conserver les éventuelles autres opérations effectuées auparavant dans la même transaction. **Résolution** : ```sql BEGIN TRANSACTION; -- 1. Insertion de l'élève INSERT INTO Eleve (NumEleve, Nom, Prenom, DateNaissance, Sexe) VALUES (4, 'Tchinda', 'Laura', '2005-09-01', 'F'); -- Création d'un point de sauvegarde après l'insertion élève SAVEPOINT after_eleve_insert; -- 2. Tentative d'inscription INSERT INTO Inscription (NumEleve, CodeClasse, AnneeScolaire, DateInscription) VALUES (4, 'T01', '2023-2024', CURRENT_DATE); -- Si l'insertion ci-dessus échoue (contrainte de clé étrangère violée), -- on revient au point de sauvegarde, annulant seulement l'insertion élève. -- Sinon, on valide tout. COMMIT; ``` **Explication pas à pas** : 1. `BEGIN TRANSACTION;` démarre une unité de travail où toutes les opérations seront soit validées (`COMMIT`) soit annulées (`ROLLBACK`). 2. Après l’insertion dans `Eleve`, on crée un `SAVEPOINT` nommé `after_eleve_insert`. Ce point permet de revenir à cet état précis sans annuler les opérations éventuellement antérieures à la transaction (si elles existaient). 3. L’`INSERT` dans `Inscription` peut lever une erreur si la clé étrangère `CodeClasse='T01'` n’existe pas dans la table `Classe`. Dans la plupart des SGBD, l’erreur interrompt l’exécution du bloc suivant sauf si on la capture. Ici, on suppose que l’erreur est détectée et que l’on exécute `ROLLBACK TO after_eleve_insert;` (non montré explicitement, mais implicite dans la logique de gestion d’erreur). 4. Si aucune erreur ne survient, on atteint `COMMIT;` qui rend permanentes les deux insertions. > **À retenir** : Les points de sauvegarde permettent une **annulation partielle** au sein d’une même transaction, très utile lorsqu’on veut traiter plusieurs étapes indépendantes mais liées. #### Exemple 3 – Création d’un index pour améliorer les performances **Énoncé** : La requête suivante est fréquemment exécutée dans le système de gestion scolaire : ```sql SELECT Nom, Prenom FROM Eleve WHERE DateNaissance BETWEEN '2005-01-01' AND '2005-12-31'; ``` Proposer une structure d’index qui accélère cette recherche et expliquer pourquoi. **Résolution** : ```sql CREATE INDEX idx_Eleve_DateNaissance ON Eleve (DateNaissance); ``` **Justification** : - Sans index, le SGBD doit effectuer un **parcours séquentiel** (full table scan) de toutes les lignes de `Eleve` pour vérifier la condition sur `DateNaissance`. - Avec un index B‑tree sur `DateNaissance`, le SGBD peut localiser rapidement le premier enregistrement correspondant à la borne inférieure (`'2005-01-01'`) puis parcourir l’index en ordre jusqu’à la borne supérieure (`'2005-12-31'`), en accédant uniquement aux pages contenant les tuples pertinents. - La complexité passe de **O(N)** (linéaire en nombre de tuples) à **O(log N + k)** où `k` est le nombre de résultats retournés, ce qui est nettement plus efficace pour de grandes tables. --- ## Synthèse du chapitre - **Base de données** : ensemble organisé, permanent, cohérent et sécurisé de données liées, géré par un SGBD. - **Table (relation)** : ensemble non ordonné de tuples partageant le même schéma d’attributs ; définie par son nom, ses attributs (type, contraintes) et son extension. - Clé primaire → unicité et identification. - Clé étrangère → intégrité référentielle. - Contraintes (NOT NULL, UNIQUE, CHECK, FOREIGN KEY) garantissent la qualité des données. - **SGBD** : logiciel intermédiaire offrant les fonctions DDL, DML, DCL, TCL ; assure l’indépendance des données grâce à l’architecture à trois niveaux (externe, conceptuel, interne) et garantit les propriétés ACID des transactions. - **Langages SQL** : - DDL : `CREATE`, `ALTER`, `DROP`. - DML : `SELECT`, `INSERT`, `UPDATE`, `DELETE`. - DCL : `GRANT`, `REVOKE`. - TCL : `BEGIN TRANSACTION`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`. - **Optimisation** : index (B‑tree, hash, etc.) réduisent le coût d’accès aux données ; les vues offrent des niveaux externes personnalisés. - **Intégrité** : entité (PK), référentielle (FK), de domaine (type, CHECK), définie par l’utilisateur (règles métier). --- ## Glossaire | Terme | Définition | |-------|------------| | **Base de données** | Ensemble organisé de données liées, stocké de façon permanente, permettant un accès efficace et une gestion centralisée. | | **Table (relation)** | Structure composée de lignes (tuples) et de colonnes (attributs) dans le modèle relationnel. | | **Attribut** | Caractéristique décrivant une entité, associée à un type de donnée et éventuellement à des contraintes. | | **Tuple** | Ligne d’une table représentant une instance d’entité. | | **Clé primaire** | Attribut ou ensemble d’attributs qui identifie de façon unique chaque tuple d’une table. | | **Clé étrangère** | Attribut qui fait référence à la clé primaire d’une autre table, assurant l’intégrité référentielle. | | **SGBD** | Logiciel qui permet de définir, manipuler, sécuriser et administrer une ou plusieurs bases de données. | | **DDL** | Langage de définition des données (CREATE, ALTER, DROP). | | **DML** | Langage de manipulation des données (SELECT, INSERT, UPDATE, DELETE). | | **DCL** | Langage de contrôle des données (GRANT, REVOKE). | | **TCL** | Langage de contrôle des transactions (BEGIN, COMMIT, ROLLBACK, SAVEPOINT). | | **Index** | Structure de données (souvent B‑tree) qui accélère la recherche sur une ou plusieurs colonnes d’une table. | | **Vue** | Table virtuelle définie par une requête stockée, appartenant au niveau externe du SGBD. | | **Transaction** | Série d’opérations considérées comme une unité de travail, devant respecter les propriétés ACID. | | **Contrainte d’intégrité** | Règle qui limite les valeurs pouvant apparaître dans une base de données afin d’assurer sa cohérence. | --- ## Exercices d'application corrigés ### Exercice 1 – Modélisation simple **Énoncé** : Un petit bibliothèque souhaite gérer les livres (ISBN, titre, auteur, année de publication) et les emprunts (numero d’emprunt, date d’emprunt, date de retour prévue, ISBN du livre, numéro d’adhérent). 1. Dessinez le schéma conceptuel (liste des tables avec leurs attributs et indiquez les clés primaires et étrangères). 2. Écrivez les commandes SQL `CREATE TABLE` correspondantes. **Corrigé** : 1. **Schéma conceptuel** | Table | Attributs | Clé primaire | Clé(s) étrangère(s) | |-------|-----------|--------------|---------------------| | `Livre` | ISBN (CHAR(13)), Titre (VARCHAR(100)), Auteur (VARCHAR(50)), AnneePub (INTEGER) | ISBN | – | | `Adherent` | NumAdherent (INTEGER), Nom (VARCHAR(30)), Prenom (VARCHAR(30)), Adresse (VARCHAR(100)) | NumAdherent | – | | `Emprunt` | NumEmprunt (INTEGER), DateEmprunt (DATE), DateRetourPrev (DATE), DateRetourReelle DATE, ISBN (CHAR(13)), NumAdherent (INTEGER) | NumEmprunt | ISBN → `Livre.ISBN`, NumAdherent → `Adherent.NumAdherent` | 2. **SQL** ```sql CREATE TABLE Livre ( ISBN CHAR(13) PRIMARY KEY, Titre VARCHAR(100) NOT NULL, Auteur VARCHAR(50) NOT NULL, AnneePub INTEGER CHECK (AnneePub > 0) ); CREATE TABLE Adherent ( NumAdherent INTEGER PRIMARY KEY, Nom VARCHAR(30) NOT NULL, Prenom VARCHAR(30) NOT NULL, Adresse VARCHAR(100) ); CREATE TABLE Emprunt ( NumEmprunt INTEGER PRIMARY KEY, DateEmprunt DATE NOT NULL, DateRetourPrev DATE NOT NULL, DateRetourReelle DATE, ISBN CHAR(13) NOT NULL, NumAdherent INTEGER NOT NULL, FOREIGN KEY (ISBN) REFERENCES Livre(ISBN), FOREIGN KEY (NumAdherent) REFERENCES Adherent(NumAdherent), CHECK (DateRetourPrev >= DateEmprunt) ); ``` --- ### Exercice 2 – Requêtes de sélection **Énoncé** : À partir des tables créées dans l’exercice 1, écrire les requêtes SQL suivantes : a) Lister les titres des livres publiés après 2010, triés par année de publication décroissante. b) Afficher le nom et le prénom des adhérents qui ont emprunté au moins un livre en 2023. c) Donner le nombre total d’emprunts en cours (c’est‑à‑dire dont la date de retour réelle est encore nulle, supposons une colonne `DateRetourReelle` DATE nullable dans `Emprunt`). **Corrigé** : a) ```sql SELECT Titre FROM Livre WHERE AnneePub > 2010 ORDER BY AnneePub DESC; ``` b) ```sql SELECT DISTINCT a.Nom, a.Prenom FROM Adherent a JOIN Emprunt e ON a.NumAdherent = e.NumAdherent WHERE YEAR(e.DateEmprunt) = 2023; ``` *(Si le SGBD ne possède pas la fonction `YEAR`, on peut utiliser `e.DateEmprunt BETWEEN '2023-01-01' AND '2023-12-31'`)* c) ```sql SELECT COUNT(*) AS EmpruntsEnCours FROM Emprunt WHERE DateRetourReelle IS NULL; ``` --- ### Exercice 3 – Insertion et mise à jour **Énoncé** : 1. Insérer un nouveau livre : ISBN `978-2-07-061287-8`, titre *« L’Étranger »*, auteur *« Albert Camus »*, année de publication `1942`. 2. Un adhérent numéro `5` vient d’emprunter ce livre aujourd’hui (date d’emprunt = date du jour) pour une durée de 14 jours. Insérer l’enregistrement d’emprunt correspondant. 3. Quelques jours plus tard, l’adhérent retourne le livre. Mettre à jour la ligne d’emprunt pour indiquer la date de retour réelle (supposons la date du jour + 10 jours). **Corrigé** : ```sql -- 1. Insertion du livre INSERT INTO Livre (ISBN, Titre, Auteur, AnneePub) VALUES ('978-2-07-061287-8', 'L''Étranger', 'Albert Camus', 1942); -- 2. Insertion de l'emprunt (on suppose que l'adhérent 5 existe déjà) INSERT INTO Emprunt (NumEmprunt, DateEmprunt, DateRetourPrev, ISBN, NumAdherent) VALUES ( (SELECT COALESCE(MAX(NumEmprunt),0)+1 FROM Emprunt), -- génération simple d'un nouvel identifiant CURRENT_DATE, DATE_ADD(CURRENT_DATE, INTERVAL 14 DAY), '978-2-07-061287-8', 5 ); -- 3. Mise à jour de la date de retour réelle (on suppose que la colonne DateRetourReelle existe) UPDATE Emprunt SET DateRetourReelle = DATE_ADD(CURRENT_DATE, INTERVAL 10 DAY) WHERE NumEmprunt = ( SELECT MAX(NumEmprunt) FROM Emprunt WHERE ISBN = '978-2-07-061287-8' AND NumAdherent = 5 AND DateRetourReelle IS NULL ); ``` **Explications** : - La génération de `NumEmprunt` utilise `MAX(NumEmprunt)+1` ; en pratique, on préférerait une colonne `AUTO_INCREMENT` ou une séquence, mais l’exemple montre la logique. - `DATE_ADD` ajoute un intervalle de jours à la date courante. - La clause `WHERE` de l’`UPDATE` s’assure de ne modifier que l’emprunt concerné et encore non retourné. --- ### Exercice 4 – Gestion de transaction **Énoncé** : Un professeur veut ajouter une nouvelle classe `CodeClasse='S02'`, `Libelle='Sciences Physiques'`, `Niveau='Seconde'` et, dans la même transaction, inscrire l’élève numéro `3` dans cette classe pour l’année `2023-2024`. Si l’insertion de la classe échoue (par exemple parce que le code `S02` existe déjà), il veut annuler uniquement l’insertion de la classe mais conserver l’inscription de l’élève dans la classe existante (au cas où elle serait déjà présente). **Corrigé** : ```sql BEGIN TRANSACTION; -- Tentative d'insertion de la classe INSERT INTO Classe (CodeClasse, Libelle, Niveau) VALUES ('S02', 'Sciences Physiques', 'Seconde') ON CONFLICT (CodeClasse) DO NOTHING; -- syntaxe PostgreSQL ; pour MySQL utiliser INSERT IGNORE -- Création d'un point de sauvegarde après la tentative d'insertion classe SAVEPOINT after_classe_insert; -- Insertion de l'inscription (on suppose que la classe existe maintenant, soit parce qu'elle venait d'être créée, -- soit parce qu'elle existait déjà) INSERT INTO Inscription (NumEleve, CodeClasse, AnneeScolaire, DateInscription) VALUES (3, 'S02', '2023-2024', CURRENT_DATE) ON CONFLICT (NumEleve, CodeClasse, AnneeScolaire) DO NOTHING; -- Si l'insertion de la classe a échoué (détectée par le nombre de lignes affectées = 0), -- on revient au point de sauvegarde pour annuler seulement cette insertion. -- Dans un vrai code, on testerait le résultat de la première INSERT et ferait: -- IF (ROW_COUNT() = 0) THEN ROLLBACK TO after_classe_insert; END IF; -- Ici, on montre la logique conceptuelle. COMMIT; ``` **Notes** : - La clause `ON CONFLICT … DO NOTHING` évite de lever une erreur lorsqu’une contrainte d’unicité est violée ; elle permet de poursuivre la transaction. - Le `SAVEPOINT` permet d’annuler uniquement l’insertion de la classe si elle s’est révélée redondante, tout en conservant l’inscription qui a pu être effectuée avec la classe déjà existante. - Dans un environnement purement SQL standard, on gérerait cela avec un bloc `IF … THEN …` dans une procédure stockée ; l’exemple illustre le principe attendu au niveau de la Première. --- ### Exercice 5 – Création d’un index et mesure de performance **Énoncé** : La requête suivante est fréquemment utilisée pour obtenir la liste des emprunts en retard (date de retour réelle dépassant la date de retour prévue) : ```sql SELECT e.NumEmprunt, e.DateEmprunt, e.DateRetourPrev, e.DateRetourReelle FROM Emprunt e WHERE e.DateRetourReelle > e.DateRetourPrev; ``` Proposer un index qui pourrait améliorer l’exécution de cette requête et justifier votre choix. **Corrigé** : ```sql CREATE INDEX idx_Emprunt_Retard ON Emprunt (DateRetourReelle, DateRetourPrev); ``` **Justification** : - La condition de la clause `WHERE` compare deux colonnes de la même table. Un index composé sur `(DateRetourReelle, DateRetourPrev)` permet au SGBD de parcourir l’index en ordre lexicographique : d’abord toutes les lignes où `DateRetourReelle` est petite, puis celles où elle est grande. - Lors de l’évaluation de la prédicat `DateRetourReelle > DateRetourPrev`, le SGBD peut ignorer les blocs d’index où la première colonne (`DateRetourReelle`) est inférieure ou égale à la seconde colonne (`DateRetourPrev`) pour toute valeur possible de la seconde, réduisant ainsi le nombre de pages à lire. - Bien que cet index ne soit pas aussi efficace qu’un index sur une expression directe (`DateRetourReelle - DateRetourPrev > 0`), il reste nettement meilleur qu’un parcours séquentiel complet de la table, surtout lorsque la table `Emprunt` contient plusieurs dizaines de milliers de lignes. --- ### Exercice 6 – Vue et sécurité **Énoncé** : Créer une vue nommée `VueEmpruntsActifs` qui montre, pour chaque emprunt en cours (date de retour réelle nulle), le numéro d’emprunt, le titre du livre emprunté, le nom et le prénom de l’adhérent, ainsi que la date d’emprunt. Ensuite, révoquer le droit d’insertion sur la table `Emprunt` à un rôle nommé `stagiaire`, tout en lui permettant de sélectionner sur la vue `VueEmpruntsActifs`. **Corrigé** : ```sql -- 1. Création de la vue CREATE VIEW VueEmpruntsActifs AS SELECT e.NumEmprunt, l.Titre, a.Nom, a.Prenom, e.DateEmprunt FROM Emprunt e JOIN Livre l ON e.ISBN = l.ISBN JOIN Adherent a ON e.NumAdherent = a.NumAdherent WHERE e.DateRetourReelle IS NULL; -- 2. Gestion des privilèges -- On suppose que le rôle 'stagiaire' existe déjà. REVOKE INSERT ON Emprunt FROM stagiaire; GRANT SELECT ON VueEmpruntsActifs TO stagiaire; ``` **Explication** : - La vue joint les trois tables nécessaires et filtre uniquement les emprunts dont la date de retour réelle est encore `NULL` (emprunt en cours). - Le `REVOKE` retire le droit d’insertion sur la table de base `Emprunt` au rôle `stagiaire`. - Le `GRANT` autorise ce même rôle à lire les données via la vue, ce qui lui permet de consulter les emprunts actifs sans pouvoir modifier directement la table sous‑jacente. --- ## Erreurs fréquentes | # | Erreur classique | Pourquoi elle se produit | Comment l’éviter | |---|------------------|--------------------------|------------------| | 1 | **Confondre clé primaire et clé étrangère** | L’élève pense que toute colonne référençant une autre table est forcément une clé primaire. | Rappeler que la PK identifie unique­ment les tuples *dans sa propre table* ; la FK fait référence à une PK *dans une autre table*. | | 2 | **Oublier NOT NULL sur des attributs essentiels** | On autorise des NULL dans des champs qui ne devraient jamais être vides (ex. : nom d’élève). | Toujours analyser le domaine métier : si l’information est obligatoire, ajouter `NOT NULL`. | | 3 | **Utiliser VARCHAR sans spécifier une longueur raisonnable** | On définit `VARCHAR(MAX)` ou une très grande longueur, gaspillant de l’espace et rendant les index moins efficaces. | Choisir une longueur basée sur les besoins réels (ex. : `VARCHAR(50)` pour un nom, `VARCHAR(200)` pour une adresse). | | 4 | **Ne pas mettre d’index sur les colonnes utilisées dans les jointures ou les filtres** | Les requêtes deviennent lentes car le SGBD effectue des scans complets. | Identifier les colonnes présentes dans les `WHERE`, `JOIN`, `ORDER BY` et créer un index adapté (souvent B‑tree). | | 5 | **Confondre COMMIT et ROLLBACK dans une transaction** | Après une erreur, l’élève appelle `COMMIT` au lieu de `ROLLBACK`, validant des données partielles. | Toujours prévoir un bloc de gestion d’erreur : `BEGIN …; IF erreur THEN ROLLBACK; ELSE COMMIT; END IF;`. | | 6 | **Omettre la clause ON DELETE / ON UPDATE sur les clés étrangères** | Lorsqu’on supprime ou modifie une ligne référencée, la base peut rejeter l’opération ou laisser des orphelins. | Déterminer la politique souhaitée (ex. : `ON DELETE SET NULL`, `ON DELETE CASCADE`) et l’expliciter dans la définition de la FK. | | 7 | **Utiliser SELECT * dans une vue ou une requête de production** | Cela rend la vue fragile : si la structure de la table sous‑jacente change, la vue peut renvoyer des colonnes inattendues ou provoquer des erreurs. | Lister explicitement les colonnes nécessaires dans le `SELECT` de la vue. | | 8 | **Ne pas respecter la casse des identificateurs dans certains SGBD** | Sous MySQL en mode insensible à la casse, `SELECT * FROM Eleve` fonctionne, mais sous PostgreSQL, `Eleve` et `eleve` sont différents. | Toujours utiliser la même casse que celle définie lors de la création (souvent en minuscules ou en majuscules selon la convention adoptée). | | 9 | **Penser qu’un index accélère toujours toutes les requêtes** | Un index sur une colonne peu sélective (ex. : sexe avec seulement deux valeurs) peut être inutile voire coûteux. | Évaluer la sélectivité (cardinalité) de la colonne ; créer un index seulement si elle réduit significativement le nombre de lignes à lire. | | 10 | **Oublier de gérer les valeurs NULL dans les comparaisons** | Une requête comme `WHERE colonne = NULL` ne retourne jamais rien, même si des NULL existent. | Utiliser `IS NULL` ou `IS NOT NULL` pour tester la nullité. | --- > **Fin du cours**. Vous disposez maintenant d’une vue complète et approfondie des notions de base de données, de table et de SGBD, ainsi que des outils nécessaires pour réussir les épreuves du Probatoire en informatique. Bon travail et bonne révision !