UA 6: IMPLEMENTATION D’UNE BASE DE DONNEES (partie 1/2)

## Objectifs et prérequis **Objectifs** : à la fin de ce chapitre l’élève sera capable de - définir une base de données et expliquer son rôle dans un système d’information ; - distinguer les différents modèles de données et justifier le choix du modèle relationnel pour la plupart des applications ; - décrire les fonctions d’un SGBD et en citer les principaux types ; - expliquer l’architecture à trois niveaux d’un SGBD ; - écrire et interpréter des requêtes DDL (CREATE, ALTER, DROP) et DML (SELECT, INSERT, UPDATE, DELETE) ; - appliquer les contraintes d’intégrité (clé primaire, clé étrangère, NOT NULL, UNIQUE, CHECK) ; - définir une transaction et en citer les propriétés ACID ; - résoudre des exercices de type baccalauréat portant sur la conception et la manipulation d’une base de données relationnelle. **Prérequis** : notions de base en algorithmique (variables, tableaux), maîtrise du tableur (fonctions de recherche et de filtrage), connaissance des concepts de ensemble et de fonction mathématique. --- ### Introduction contextualisée Dans les systèmes d’information modernes, la donnée constitue le véritable actif stratégique. Que ce soit pour gérer les notes des élèves, le stock d’une boutique ou les dossiers patients d’un hôpital, il faut pouvoir stocker, récupérer, mettre à jour et garantir la cohérence d’un volume parfois important d’informations. Les bases de données (BD) et leurs systèmes de gestion (SGBD) offrent une réponse structurée à ces besoins. Ce chapitre présente les fondements théoriques (modèles, architecture) puis les aspects pratiques (langages DDL/DML, intégrité, transactions) indispensables pour réussir l’épreuve du Baccalauréat séries AVC, SH. --- ## I. Généralités sur les bases de données ### 1. Définition et rôle d’une base de données > **Base de données** : collection organisée de données permettant un stockage, une recherche et une mise à jour efficaces, indépendamment des applications qui les utilisent. Une BD se caractérise par : - **l’intégrité** : les données respectent des règles de cohérence ; - **la persistance** : les données survivent à l’arrêt des programmes ; - **le partage** : plusieurs utilisateurs ou applications peuvent y accéder simultanément. ### 2. Modèles de données | Modèle | Principe | Avantages | Inconvénients | |--------|----------|-----------|---------------| | Hiérarchique | arbre de enregistrements (parent‑enfant) | simple pour les relations 1‑N | difficile à représenter les relations N‑N | | Réseau | graphe de enregistrements (ensembles) | plus flexible que hiérarchique | complexité de la navigation | | Relationnel | tables (relations) composées de lignes et de colonnes | indépendance physique/logique, langage déclaratif (SQL) | redondance possible si mal normalisé | | Objet‑relationnel | extension du relationnel avec types abstraits, héritage | adapté aux données complexes | moins répandu dans l’enseignement secondaire | | NoSQL | stockage clé‑valeur, document, colonne, graphe | scalabilité horizontale, schéma flexible | moins de garanties ACID (selon le type) | Dans le programme camerounais, l’accent est mis sur le **modèle relationnel** car il constitue la base du langage SQL étudié en classe de Terminale. ### 3. Avantages et limites d’une BD **Avantages** : réduction de la redondance, amélioration de la cohérence, facilité de sauvegarde et de récupération, accès concurrentiel contrôlé. **Limites** : coût d’acquisition et de formation, besoin d’une administration spécialisée, surcharge éventuelle pour des traitements très simples (ex. fichier plat). > **Règle d’or** : une BD doit être conçue en fonction des besoins fonctionnels (requêtes fréquentes) et non selon la structure physique du stockage. --- ## II. Notion de SGBD ### 1. Définition > **Système de Gestion de Bases de Données (SGBD)** : logiciel intermédiaire entre les utilisateurs/applications et les données stockées, chargé de la définition, de la manipulation, du contrôle et de l’administration des bases de données. ### 2. Fonctions principales | Fonction | Description | |----------|-------------| | **Définition** (DDL) | création, modification, suppression des schémas (tables, vues, index) | | **Manipulation** (DML) | insertion, lecture, mise à jour, suppression des données | | **Contrôle** (DCL) | gestion des droits d’accès, des rôles et des privilèges | | **Administration** | sauvegarde, restauration, monitoring des performances, gestion des transactions | ### 3. Types de SGBD - **SGBD relationnel** : MySQL, PostgreSQL, Oracle, SQL Server (utilisent le langage SQL). - **SGBD objet** : PostgreSQL (extension objet), Oracle Object. - **SGBD NoSQL** : MongoDB (document), Cassandra (colonne), Neo4j (graphe), Redis (clé‑valeur). ### 4. Architecture à trois niveaux (ANSI/SPARC) 1. **Niveau externe** : vues adaptées aux besoins spécifiques des utilisateurs ou des applications. 2. **Niveau conceptuel** : schéma logique global indépendant du matériel et des logiciels de stockage (souvent exprimé en modèle relationnel). 3. **Niveau interne** : description physique du stockage (fichiers, index, méthodes d’accès). > **Propriété** : l’indépendance des données permet de modifier le schéma interne sans affecter les applications (indépendance physique) et de modifier le schéma conceptuel sans reprogrammer les applications (indépendance logique). --- ## III. Opérations de définition et de manipulation des données ### 1. Langage de définition de données (DDL) #### a. Création d’une table ```sql CREATE TABLE Eleve ( num_eleve INT PRIMARY KEY, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, date_nais DATE, sexe CHAR(1) CHECK (sexe IN ('M','F')), classe VARCHAR(10) ); ``` - `PRIMARY KEY` garantit l’unicité et l’identification de chaque ligne. - `NOT NULL` interdit les valeurs nulles. - `CHECK` impose une contrainte de domaine. #### b. Modification d’une table ```sql ALTER TABLE Eleve ADD COLUMN email VARCHAR(100) UNIQUE; ``` #### c. Suppression d’une table ```sql DROP TABLE Eleve; ``` > **Rappel** : `DROP` détruit définitivement la table et toutes ses données ; utiliser avec précaution. ### 2. Langage de manipulation de données (DML) #### a. Insertion ```sql INSERT INTO Eleve (num_eleve, nom, prenom, date_nais, sexe, classe) VALUES (2025001, 'Kamga', 'Aïcha', '2008-04-12', 'F', 'Terminale A'); ``` #### b. Sélection simple ```sql SELECT nom, prenom, classe FROM Eleve WHERE sexe = 'F' ORDER BY nom; ``` #### c. Sélection avec jointure (exemple avec une table `Note`) ```sql CREATE TABLE Note ( num_eleve INT, matiere VARCHAR(30), valeur DECIMAL(4,2), FOREIGN KEY (num_eleve) REFERENCES Eleve(num_eleve) ); INSERT INTO Note VALUES (2025001, 'Mathématiques', 14.5), (2025001, 'Physique', 12.0); SELECT E.nom, E.prenom, N.matiere, N.valeur FROM Eleve E JOIN Note N ON E.num_eleve = N.num_eleve WHERE E.classe = 'Terminale A'; ``` #### d. Mise à jour ```sql UPDATE Eleve SET classe = 'Terminale B' WHERE num_eleve = 2025001; ``` #### e. Suppression ```sql DELETE FROM Note WHERE num_eleve = 2025001 AND matiere = 'Physique'; ``` ### 3. Contraintes d’intégrité | Contrainte | Objectif | Exemple SQL | |------------|----------|-------------| | **Clé primaire (PK)** | garantir l’unicité et non‑nullité de l’identifiant | `PRIMARY KEY (num_eleve)` | | **Clé étrangère (FK)** | assurer la référentialité entre deux tables | `FOREIGN KEY (num_eleve) REFERENCES Eleve(num_eleve)` | | **NOT NULL** | interdire les valeurs manquantes | `nom VARCHAR(50) NOT NULL` | | **UNIQUE** | garantir l’unicité d’un champ (ou groupe de champs) sans être PK | `email VARCHAR(100) UNIQUE` | | **CHECK** | limiter les valeurs à un domaine prédéfini | `sexe CHAR(1) CHECK (sexe IN ('M','F'))` | > **Théorème** : Dans une relation, si un ensemble d’attributs forme une clé primaire, alors aucune deux lignes ne peuvent avoir identique la valeur de cet ensemble. *Preuve par l’absurde* : supposons deux lignes distinctes \(t_1\neq t_2\) avec mêmes valeurs sur la PK. Alors la PK ne distinguerait pas \(t_1\) de \(t_2\), contredisant la définition de clé primaire qui doit permettre l’identification unique. ∎ ### 4. Transactions et propriétés ACID Une **transaction** est une séquence d’opérations considérée comme une unité indivisible de travail. - **Atomicité** : soit toutes les opérations sont exécutées, soit aucune (ex. transfert d’argent). - **Cohérence** : la BD passe d’un état cohérent à un autre état cohérent (respect des contraintes). - **Isolation** : les transactions concurrentes ne s’interfèrent pas (niveaux d’isolation : READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, SERIALIZABLE). - **Durabilité** : une fois validée (COMMIT), les modifications persistent malgré une défaillance du système. Exemple de transaction SQL : ```sql START TRANSACTION; UPDATE Eleve SET classe = 'Terminale B' WHERE num_eleve = 2025001; UPDATE Historique SET action = 'Promotion' WHERE num_eleve = 2025001; COMMIT; ``` Si une erreur intervient avant le `COMMIT`, on exécute `ROLLBACK` pour annuler toutes les modifications. > **Règle** : toujours encapsuler les opérations qui doivent être vues comme une unité dans un bloc `START TRANSACTION … COMMIT;` (ou `ROLLBACK`). --- ## Synthèse du chapitre - Une **base de données** est un ensemble structuré de données, indépendant des applications. - Le **modèle relationnel** (tables, lignes, colonnes) est privilégié pour sa simplicité et son langage déclaratif SQL. - Un **SGBD** assure la définition, la manipulation, le contrôle et l’administration des BD grâce aux langages DDL, DML, DCL et aux fonctions d’administration. - L’**architecture à trois niveaux** garantit l’indépendance des données (physique et logique). - Le **DDL** (`CREATE`, `ALTER`, `DROP`) définit le schéma ; le **DML** (`SELECT`, `INSERT`, `UPDATE`, `DELETE`) manipule les données. - Les **contraintes d’intégrité** (PK, FK, NOT NULL, UNIQUE, CHECK) assurent la qualité et la cohérence des données. - Une **transaction** respecte les propriétés **ACID** (Atomicité, Cohérence, Isolation, Durabilité). - Le respect de ces concepts permet de concevoir des applications fiables, évolutives et sécurisées. --- ## Glossaire | Terme | Définition | |-------|------------| | Base de données (BD) | Collection organisée de données permettant un stockage, une recherche et une mise à jour efficaces. | | Système de Gestion de Bases de Données (SGBD) | Logiciel intermédiaire entre utilisateurs/applications et données, chargé de la définition, manipulation, contrôle et administration. | | Modèle relationnel | Représentation des données sous forme de tables (relations) composées de lignes (tuples) et de colonnes (attributs). | | Schéma | Description logique de la structure d’une BD (tables, champs, types, contraintes). | | Langage de définition de données (DDL) | Ensemble d’instructions SQL (`CREATE`, `ALTER`, `DROP`) permettant de définir ou modifier le schéma. | | Langage de manipulation de données (DML) | Ensemble d’instructions SQL (`SELECT`, `INSERT`, `UPDATE`, `DELETE`) permettant de lire ou modifier les données. | | Contrainte d’intégrité | Règle imposée sur les données pour garantir leur cohérence (PK, FK, NOT NULL, UNIQUE, CHECK). | | Transaction | Séquence d’opérations considérée comme une unité indivisible de travail, devant respecter les propriétés ACID. | | Clé primaire (PK) | Attribut ou ensemble d’attributs qui identifie de façon unique chaque tuple d’une relation. | | Clé étrangère (FK) | Attribut qui référence la clé primaire d’une autre relation, assurant la référentialité. | | Normalisation | Processus de décomposition d’une relation visant à éliminer les redondances et les anomalies de mise à jour. | | Index | Structure de données associée à une colonne permettant d’accélérer les recherches. | | Vue | Table virtuelle résultant d’une requête `SELECT`, ne stockant pas de données propres. | | Verrouillage | Mécanisme d’isolation empêchant deux transactions de modifier simultanément la même donnée de façon conflictuelle. | | COMMIT | Instruction qui valide définitivement les modifications d’une transaction. | | ROLLBACK | Instruction qui annule toutes les modifications d’une transaction en cours. | --- ## Exercices d'application corrigés ### Exercice 1 – Création de table (niveau facile) **Énoncé** : Créer une table `Professeur` comportant les champs : `matricule` (entier, clé primaire), `nom` (chaîne de 30 caractères, non nul), `prenom` (chaîne de 30 caractères, non nul), `specialite` (chaîne de 50 caractères), `email` (chaîne de 100 caractères, unique). **Corrigé** : ```sql CREATE TABLE Professeur ( matricule INT PRIMARY KEY, nom VARCHAR(30) NOT NULL, prenom VARCHAR(30) NOT NULL, specialite VARCHAR(50), email VARCHAR(100) UNIQUE ); ``` *Justification* : chaque clause correspond exactement à la demande : `PRIMARY KEY` pour l’identifiant, `NOT NULL` pour obliger la saisie, `UNIQUE` pour garantir l’unicité de l’adresse mail. --- ### Exercice 2 – Insertion et sélection simple (niveau intermédiaire) **Énoncé** : 1. Insérer trois professeurs dans la table `Professeur` créée précédemment avec les données suivantes : - (101, 'Nkoumou', 'Jean', 'Mathématiques', 'jean.nkoumou@edu.cm') - (102, 'Mbida', 'Aïssatou', 'Physique', 'aissatou.mbida@edu.cm') - (103, 'Tchinda', 'Carlos', 'Informatique', 'carlos.tchinda@edu.cm') 2. Afficher le nom et le prénom des professeurs spécialisés en « Informatique ». **Corrigé** : ```sql -- 1. Insertion INSERT INTO Professeur (matricule, nom, prenom, specialite, email) VALUES (101, 'Nkoumou', 'Jean', 'Mathématiques', 'jean.nkoumou@edu.cm'), (102, 'Mbida', 'Aïssatou', 'Physique', 'aissatou.mbida@edu.cm'), (103, 'Tchinda', 'Carlos', 'Informatique', 'carlos.tchinda@edu.cm'); -- 2. Sélection SELECT nom, prenom FROM Professeur WHERE specialite = 'Informatique'; ``` *Résultat attendu* : | nom | prenom | |----------|--------| | Tchinda | Carlos | *Justification* : la clause `WHERE` filtre les lignes dont la colonne `specialite` vaut exactement `'Informatique'`. --- ### Exercice 3 – Jointure intérieure (niveau intermédiaire) **Énoncé** : On possède deux tables : - `Eleve(num_eleve INT PRIMARY KEY, nom VARCHAR(30), prenom VARCHAR(30), classe VARCHAR(10))` - `Note(num_eleve INT, matiere VARCHAR(30), valeur DECIMAL(4,2), FOREIGN KEY (num_eleve) REFERENCES Eleve(num_eleve))` Insérer les données suivantes : `Eleve` : (2001, 'Bello', 'Fatou', 'Terminale S'), (2002, 'Djoumessi', 'Marc', 'Terminale S') `Note` : (2001, 'Mathématiques', 13.5), (2001, 'Physique', 11.0), (2002, 'Mathématiques', 15.0), (2002, 'SVT', 14.0) Puis afficher, pour chaque élève de la classe `Terminale S`, son nom, prénom, la matière et la note obtenue. **Corrigé** : ```sql -- Création des tables (si non existantes) CREATE TABLE Eleve ( num_eleve INT PRIMARY KEY, nom VARCHAR(30), prenom VARCHAR(30), classe VARCHAR(10) ); CREATE TABLE Note ( num_eleve INT, matiere VARCHAR(30), valeur DECIMAL(4,2), FOREIGN KEY (num_eleve) REFERENCES Eleve(num_eleve) ); -- Insertion des élèves INSERT INTO Eleve (num_eleve, nom, prenom, classe) VALUES (2001, 'Bello', 'Fatou', 'Terminale S'), (2002, 'Djoumessi', 'Marc', 'Terminale S'); -- Insertion des notes INSERT INTO Note (num_eleve, matiere, valeur) VALUES (2001, 'Mathématiques', 13.5), (2001, 'Physique', 11.0), (2002, 'Mathématiques', 15.0), (2002, 'SVT', 14.0); -- Requête de jointure SELECT E.nom, E.prenom, N.matiere, N.valeur FROM Eleve E JOIN Note N ON E.num_eleve = N.num_eleve WHERE E.classe = 'Terminale S' ORDER BY E.nom, N.matiere; ``` *Résultat attendu* : | nom | prenom | matiere | valeur | |----------|--------|-----------|--------| | Bello | Fatou | Mathématiques | 13.5 | | Bello | Fatou | Physique | 11.0 | | Djoumessi| Marc | Mathématiques | 15.0 | | Djoumessi| Marc | SVT | 14.0 | *Justification* : la jointure `JOIN` associe chaque élève à ses notes via la clé étrangère `num_eleve`. Le `WHERE` restreint aux élèves de la classe souhaitée. --- ### Exercice 4 – Contraintes d’intégrité et transaction (niveau avancé) **Énoncé** : 1. Créer une table `Compte` avec les champs : `num_compte` (INT, PK), `titulaire` (VARCHAR(50), NOT NULL), `solde` (DECIMAL(12,2), CHECK (solde >= 0)). 2. Insérer deux comptes : (1001, 'Alice', 5000.00) et (1002, 'Bob', 3000.00). 3. Écrire une transaction qui transfère 700.00 du compte d’Alice vers celui de Bob, en vérifiant que le solde d’Alice ne devient pas négatif. Si le solde serait négatif, annuler la transaction. **Corrigé** : ```sql -- 1. Création de la table CREATE TABLE Compte ( num_compte INT PRIMARY KEY, titulaire VARCHAR(50) NOT NULL, solde DECIMAL(12,2) CHECK (solde >= 0) ); -- 2. Insertion initiale INSERT INTO Compte (num_compte, titulaire, solde) VALUES (1001, 'Alice', 5000.00), (1002, 'Bob', 3000.00); -- 3. Transaction de transfert START TRANSACTION; -- Vérification du solde suffisant SELECT solde INTO @solde_alice FROM Compte WHERE titulaire = 'Alice'; IF @solde_alice >= 700.00 THEN UPDATE Compte SET solde = solde - 700.00 WHERE titulaire = 'Alice'; UPDATE Compte SET solde = solde + 700.00 WHERE titulaire = 'Bob'; COMMIT; ELSE ROLLBACK; SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Solde insuffisant pour le transfert'; END IF; ``` *Explication* : - La contrainte `CHECK (solde >= 0)` empêche toute mise à jour qui rendrait le solde négatif. - Le bloc `START TRANSACTION … COMMIT;` garantit l’atomicité : soit les deux mises à jour sont appliquées, soit aucune. - En cas de solde insuffisant, on effectue un `ROLLBACK` et on lève une erreur personnalisée. --- ### Exercice 5 – Normalisation (niveau avancé) **Énoncé** : On possède la table suivante représentant les inscriptions d’élèves à des activités : `Inscriptions(num_eleve INT, nom VARCHAR(30), activité VARCHAR(30), encadrant VARCHAR(30), salle VARCHAR(10), jour VARCHAR(10))` Un même élève peut participer à plusieurs activités, chaque activité est encadrée par un seul encadrant, et chaque activité se déroule dans une salle fixe un jour donné. 1. Identifier les dépendances fonctionnelles présentes. 2. Mettre la table en première forme normale (1FN). 3. Décomposer en troisième forme normale (3FN). **Corrigé** : 1. **Dépendances fonctionnelles** : - `activité → encadrant, salle, jour` (une activité détermine son encadrant, sa salle et son jour). - `num_eleve, activité → nom` (le nom de l’élève dépend de son numéro et de l’activité, mais en réalité `num_eleve → nom` seul suffit). - Ainsi, on a aussi `num_eleve → nom`. 2. **Première forme normale (1FN)** : la table possède déjà des valeurs atomiques (pas de listes répétés). Elle est donc en 1FN. 3. **Décomposition en 3FN** : - Créer une relation `Activité` : `Activité(activité PK, encadrant, salle, jour)`. - Créer une relation `Eleve` : `Eleve(num_eleve PK, nom)`. - Créer une relation `Inscription` : `Inscription(num_eleve FK → Eleve, activité FK → Activité)`. Schéma final : ``` Eleve(num_eleve PK, nom) Activité(activité PK, encadrant, salle, jour) Inscription(num_eleve FK, activité FK, PRIMARY KEY(num_eleve, activité)) ``` *Justification* : - Dans `Activité`, toutes les attributs non-clés dépendent directement de la clé `activité` → 2FN et 3FN satisfaites. - Dans `Eleve`, `nom` dépend uniquement de `num_eleve` → 2FN/3FN. - Dans `Inscription`, la clé primaire composée évite les redondances et élimine les dépendances transitives. --- ## Erreurs fréquentes | # | Erreur classique | Pourquoi elle se produit | Comment l’éviter | |---|------------------|--------------------------|------------------| | 1 | Oublier de préciser `PRIMARY KEY` lors de la création d’une table | Confusion entre identifiant logique et simple champ | Toujours définir explicitement une clé primaire dès la création (`PRIMARY KEY` ou `UNIQUE NOT NULL`). | | 2 | Utiliser `DELETE` sans `WHERE` | Pensée que la commande ne touche qu’une ligne | Vérifier systématiquement la présence d’une clause `WHERE` ; faire un `SELECT` préalable pour voir les lignes concernées. | | 3 | Confondre `UNIQUE` et `PRIMARY KEY` | Croire que `UNIQUE` suffit pour identifier un rappel | Retenir que `PRIMARY KEY` implique `UNIQUE NOT NULL` et sert d’identifiant de la table ; `UNIQUE` autorise un `NULL` (une seule fois). | | 4 | Négliger la vérification des contraintes avant un `UPDATE` ou `INSERT` | Oubli de tester la valeur par rapport à un `CHECK` ou une `FK` | Utiliser des `SELECT` de contrôle ou des déclencheurs (`TRIGGER`) pour valider les données avant écriture. | | 5 | Omettre `COMMIT` après une série d’opérations supposées transactionnelles | Croyance que chaque instruction est automatiquement validée | Encadrer les opérations critiques dans `START TRANSACTION … COMMIT;` (ou `ROLLBACK`) et tester le scénario d’erreur. | | 6 | Utiliser `SELECT *` dans des requêtes de production | Facilité d’écriture, mais risque de récupérer des colonnes inutiles ou sensibles | Lister explicitement les colonnes nécessaires ; cela améliore la lisibilité et la performance. | --- *Fin du cours.*