IMPLEMENTATION D’UNE BASE DE DONNEES

## Objectifs et prérequis **Objectifs** : À l’issue de ce chapitre, l’élève sera capable de : - Définir une base de données (BD) et expliquer son rôle dans un système d’information. - Identifier 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 Système de Gestion de Bases de Données (SGBD) et son architecture à trois niveaux. - Distinguer les langages DDL, DML, DCL et TCL et savoir écrire les instructions correspondantes. - Créer une base de données en mode graphique à l’aide d’un outil courant (MySQL Workbench, phpMyAdmin ou Microsoft Access). - Écrire, exécuter et optimiser des requêtes SQL simples et complexes (SELECT avec clauses, jointures, sous‑requêtes, fonctions d’aggrégation). - Interpréter les résultats d’une requête et les mettre en relation avec le besoin fonctionnel exprimé. **Prérequis** : - Notions de base en algorithmique (variables, structures de contrôle). - Connaissance des types de données simples (entier, réel, chaîne de caractères, date). - Familiarité avec l’environnement Windows/Linux et la manipulation de fichiers. - Avoir suivi les chapitres précédents sur la représentation de l’information et les tableaux. > **Définition clé** : Une *base de données* est un ensemble organisé de données, stockées de façon permanente, permettant un accès rapide, sécurisé et partagé à l’information. ## Introduction contextualisée Dans le monde contemporain, les données constituent le cœur battant de toute organisation : établissements scolaires, entreprises commerciales, administrations publiques, structures de santé, etc. La capacité à collecter, stocker, récupérer et analyser ces données détermine l’efficacité des décisions prises. Depuis les années 1960, les premières bases de données hiérarchiques (ex. : IBM IMS) ont laissé place aux modèles réseau, puis, à partir de 1970, au modèle relationnel introduit par Edgar F. Codd, qui a révolutionné la façon de concevoir les systèmes d’information. Aujourd’hui, le SGBD relationnel demeure le pilier des applications d’entreprise, tandis que les SGBD NoSQL répondent à des besoins spécifiques de volumétrie et de variété. Au lycée, l’apprentissage des bases de données vise à donner à l’élève les outils conceptuels et pratiques nécessaires pour concevoir une petite application (gestion de notes, de bibliothèque, de stock) et pour interagir avec elle via un langage déclaratif (SQL). Cette compétence est directement évaluée à l’épreuve pratique du Baccalauréat séries A4, A1, A2, A3, A5, où le candidat doit créer une base, définir ses tables, insérer des données et exécuter des requêtes répondant à un cahier des charges. ## I. Généralités sur les bases de données ### A. Définition et rôle d’une base de données Une base de données (BD) est un ensemble structuré de données, stocké de manière permanente sur un support (disque dur, SSD, serveur distant) et géré par un logiciel spécialisé appelé SGBD. Elle permet : - **L’intégrité** : les données respectent des règles de cohérence (contraintes d’unicité, de référentiel, de domaine). - **La sécurité** : seuls les utilisateurs autorisés peuvent lire ou modifier certaines parties. - **La concurrence** : plusieurs utilisateurs peuvent accéder simultanément aux données sans corruption. - **La récupération** : en cas de panne, le SGBD peut restaurer la BD à un état cohérent. > **Propriété fondamentale** : Une BD doit satisfaire les propriétés ACID (Atomicité, Cohérence, Isolation, Durabilité) pour garantir la fiabilité des transactions. ### B. Modèles de données | Modèle | Principe | Avantages | Inconvénients | Exemple historique | |--------|----------|-----------|---------------|--------------------| | Hiérarchique | Arbre de enregistrements (parent‑enfant) | Accès rapide aux chemins prédéfinis | Difficile à modifier, redondance | IBM IMS (1968) | | Réseau | Grahe orienté (ensembles, enregistrements) | Plus de flexibilité que hiérarchique | Complexité de conception | CODASYL DBTG (1971) | | Relationnel | Tables (relations) liées par clés | Simplicité, indépendance logique, normalisation | Peut être moins performant pour certains accès | System R (IBM, 1974) | | Objet | Objets avec attributs et méthodes | Adéquation avec la POO, gestion de données complexes | Moins répandu, manque de standardisation | ObjectStore (1990) | | NoSQL | Clé‑valeur, document, colonne, graphe | Scalabilité horizontale, schéma flexible | Moins de garanties ACID (souvent BASE) | MongoDB, Cassandra, Neo4j | > **Règle de normalisation** : Une relation est en première forme normale (1FN) si chaque attribut contient une valeur atomique (indivisible). ### C. Avantages et limites des bases de données **Avantages** : partage des données, réduction de la redondance, mise à jour centralisée, recherche rapide grâce aux index, sauvegarde et restauration facilitées. **Limites** : coût d’acquisition et de maintenance d’un SGBD performant, besoin de compétences spécialisées, risque de goulot d’étranglement si la charge dépasse les capacités du serveur. ### D. Exemples concrets 1. **Gestion des notes d’un lycée** : tables *Élève*, *Matière*, *Note* (avec foreign keys vers Élève et Matière). 2. **Bibliothèque municipale** : tables *Livre*, *Auteur*, *Emprunt*, *Adhérent*. 3. **Stock d’un magasin** : tables *Produit*, *Fournisseur*, *MouvementStock*. ## II. Notion de SGBD ### A. Qu’est‑ce qu’un SGBD ? Un Système de Gestion de Bases de Données (SGBD) est un logiciel intermédiaire entre les utilisateurs/applications et les données stockées. Il offre une interface permettant de définir la structure des données, de les manipuler, de garantir leur intégrité et de contrôler les accès. > **Définition clé** : Un SGBD assure la *séparation des niveaux* (externe, conceptuel, interne) afin d’assurer l’indépendance des données. ### B. Fonctions principales | Fonction | Description | Exemple d’instruction SQL | |----------|-------------|---------------------------| | Définition de données (DDL) | Création, modification, suppression d’objets (schéma, tables, vues, index) | `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE` | | Manipulation de données (DML) | Lecture, insertion, mise à jour, suppression des enregistrements | `SELECT`, `INSERT`, `UPDATE`, `DELETE` | | Contrôle de données (DCL) | Gestion des privilèges et des rôles | `GRANT SELECT ON table TO user;`, `REVOKE` | | Contrôle de transaction (TCL) | Validation ou annulation de groupes d’opérations | `COMMIT;`, `ROLLBACK;`, `SAVEPOINT sp1;` | | Administration | Sauvegarde, restauration, monitoring, optimisation | `BACKUP DATABASE`, `RESTORE DATABASE`, `ANALYZE TABLE` | ### C. Architecture à trois niveaux 1. **Niveau externe** : vues adaptées aux besoins spécifiques des utilisateurs ou des applications (ex. : une vue *NotesParÉlève* pour le professeur). 2. **Niveau conceptuel** : schéma logique global de la BD, indépendant du matériel et des logiciels de stockage (tables, relations, contraintes). 3. **Niveau interne** : description physique de la façon dont les données sont stockées sur le disque (fichiers, index, méthodes d’accès, compression). > **Propriété** : L’indépendance logique permet de modifier le schéma conceptuel sans affecter les applications externes ; l’indépendance physique permet de changer le mode de stockage sans toucher au schéma conceptuel. ### D. Types de SGBD - **SGBD relationnel** : MySQL, PostgreSQL, Oracle, Microsoft SQL Server, MariaDB. - **SGBD objet‑relationnel** : PostgreSQL (avec extensions), Oracle Object. - **SGBD NoSQL** : MongoDB (document), Cassandra (large colonne), Redis (clé‑valeur), Neo4j (graphe). - **SGBD embarqué** : SQLite (utilisé dans les applications mobiles et les systèmes embarqués). > **Exemple** : Dans le contexte du Baccalauréat camerounais, les épreuves pratiques utilisent généralement MySQL ou MariaDB via phpMyAdmin, car ils sont libres, simples à installer et suffisamment riches pour couvrir le programme. ### E. Exemples de SGBD populaires | SGBD | Licence | Modèle | Points forts | |------|---------|--------|--------------| | MySQL | GPL / Commercial | Relationnel | Grande communauté, réplication facile | | PostgreSQL | Licence BSD | Relationnel‑objet | Conformité SQL avancée, extensibilité | | Oracle | Propriétaire | Relationnel | Performance élevée, outils d’administration | | Microsoft SQL Server | Propriétaire | Relationnel | Intégration avec .NET, Azure | | MongoDB | AGPL / Commercial | Document | Schéma flexible, scalabilité horizontale | | Redis | BSD | Clé‑valeur | Extrêmement rapide, structures de données riches | ## III. Opérations de définition et de manipulation des données ### A. Langage de définition de données (DDL) Le DDL permet de créer la structure de la base. Les commandes essentielles sont : - `CREATE TABLE nom_table (liste_de_colonnes);` - `ALTER TABLE nom_table ADD|MODIFY|DROP COLUMN colonne_def;` - `DROP TABLE nom_table;` - `TRUNCATE TABLE nom_table;` (supprime toutes les lignes mais conserve la structure) > **Règle** : Après un `DROP TABLE`, la table et toutes ses dépendances (index, déclencheurs) sont définitivement supprimées ; il n’existe pas de `ROLLBACK` sauf si la transaction n’a pas encore été validée. #### Exemple 1 – Création simple ```sql CREATE TABLE Eleve ( idEleve INT PRIMARY KEY AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, dateNaissance DATE, sexe ENUM('M','F') ); ``` *Étapes* : 1. `CREATE TABLE Eleve` indique la création d’une table nommée *Eleve*. 2. Chaque ligne définit un attribut : nom, type, contraintes (`NOT NULL`, `PRIMARY KEY`, `AUTO_INCREMENT`). 3. La clé primaire garantit l’unicité de `idEleve`. #### Exemple 2 – Modification avec contrainte de clé étrangère Supposons une table *Matière* déjà créée : ```sql CREATE TABLE Matiere ( idMatiere INT PRIMARY KEY, libelle VARCHAR(100) NOT NULL ); ``` On veut ajouter une référence dans la table *Note* : ```sql CREATE TABLE Note ( idNote INT PRIMARY KEY AUTO_INCREMENT, idEleve INT NOT NULL, idMatiere INT NOT NULL, valeur DECIMAL(4,2) NOT NULL, dateNote DATE, FOREIGN KEY (idEleve) REFERENCES Eleve(idEleve), FOREIGN KEY (idMatiere) REFERENCES Matiere(idMatiere) ); ``` *Étapes* : 1. Définition des colonnes `idEleve` et `idMatiere` comme `INT NOT NULL`. 2. Ajout de deux contraintes `FOREIGN KEY` qui lient chaque note à un élève et à une matière existants. 3. Si l’on tente d’insérer une note avec un `idEleve` inexistant, le SGBD rejette l’opération. ### B. Langage de manipulation de données (DML) Le DML agit sur les lignes des tables. #### 1. SELECT – récupération de données Syntaxe de base : ```sql SELECT liste_de_colonnes FROM nom_table [WHERE condition] [ORDER BY colonne [ASC|DESC]] [LIMIT offset, nombre]; ``` > **Règle** : Le `WHERE` filtre les lignes avant toute opération de tri ou de limitation. #### Exemple 3 – Sélection simple avec filtre ```sql SELECT nom, prenom, dateNaissance FROM Eleve WHERE sexe = 'F' ORDER BY nom ASC LIMIT 0, 10; ``` *Étapes* : - On sélectionne les colonnes `nom`, `prenom`, `dateNaissance`. - Le `WHERE` ne garde que les lignes où `sexe = 'F'`. - Le résultat est trié par ordre alphabétique du nom (`ASC`). - Enfin, on limite l’affichage aux 10 premières lignes (`LIMIT 0,10`). #### Exemple 4 – Sélection avec agrégation et groupe On veut connaître la moyenne des notes par matière : ```sql SELECT m.libelle AS Matiere, AVG(n.valeur) AS Moyenne FROM Note n JOIN Matiere m ON n.idMatiere = m.idMatiere GROUP BY m.libelle HAVING AVG(n.valeur) >= 10 ORDER BY Moyenne DESC; ``` *Étapes* : 1. `JOIN` associe chaque note à sa matière via la clé étrangère. 2. `GROUP BY m.libelle` crée un groupe pour chaque matière distincte. 3. `AVG(n.valeur)` calcule la moyenne des valeurs dans chaque groupe. 4. `HAVING` filtre les groupes dont la moyenne est inférieure à 10. 5. `ORDER BY Moyenne DESC` trie du meilleur au pire résultat. #### 2. INSERT – ajout de lignes Syntaxe : ```sql INSERT INTO nom_table (colonne1, colonne2, …) VALUES (valeur1, valeur2, …); ``` #### Exemple 5 – Insertion d’un nouvel élève ```sql INSERT INTO Eleve (nom, prenom, dateNaissance, sexe) VALUES ('Kamga', 'Aïcha', '2005-09-12', 'F'); ``` *Étapes* : - On ne précise pas `idEleve` car il est `AUTO_INCREMENT`. - Les valeurs fournies respectent les types et les contraintes (`NOT NULL`). #### 3. UPDATE – modification de lignes Syntaxe : ```sql UPDATE nom_table SET colonne1 = nouvelle_valeur1, colonne2 = nouvelle_valeur2, … WHERE condition; ``` #### Exemple 6 – Correction d’une date de naissance erronée ```sql UPDATE Eleve SET dateNaissance = '2004-07-23' WHERE idEleve = 57; ``` *Étapes* : - Le `WHERE` cible uniquement l’élève dont `idEleve = 57`. - La colonne `dateNaissance` reçoit la nouvelle valeur. #### 4. DELETE – suppression de lignes Syntaxe : ```sql DELETE FROM nom_table WHERE condition; ``` #### Exemple 7 – Suppression des notes d’un élève ayant quitté l’établissement ```sql DELETE FROM Note WHERE idEleve = 57; ``` *Attention* : Sans `WHERE`, toutes les lignes seraient supprimées. ### C. Langage de contrôle de données (DCL) Gestion des privilèges. #### Exemple 8 – Attribution de droits en lecture seule ```sql GRANT SELECT ON Eleve TO 'professeur'@'localhost' IDENTIFIED BY 'pwd123'; ``` *Étapes* : - L’utilisateur `professeur` provenant de `localhost` obtient le droit `SELECT` sur la table `Eleve`. - Un mot de passe est associé (facultatif selon la configuration du serveur). #### Exemple 9 – Révocation d’un droit ```sql REVOKE INSERT, UPDATE ON Note FROM 'professeur'@'localhost'; ``` ### D. Langage de contrôle de transaction (TCL) Assure l’atomicité d’un groupe d’opérations. > **Règle** : Une transaction débute implicitement avec la première instruction DML après un `COMMIT` ou un `ROLLBACK`, ou explicitement avec `START TRANSACTION`. #### Exemple 10 – Transfert de points entre deux évaluations (simulé) Supposons qu’on veut ajouter 2 points à la note d’un élève dans une matière et retirer 2 points dans une autre, tout en garantissant que le total reste constant. ```sql START TRANSACTION; UPDATE Note SET valeur = valeur + 2 WHERE idEleve = 23 AND idMatiere = 1; -- matière A UPDATE Note SET valeur = valeur - 2 WHERE idEleve = 23 AND idMatiere = 2; -- matière B -- Vérification de cohérence (facultatif) SELECT SUM(valeur) AS Total FROM Note WHERE idEleve = 23 AND idMatiere IN (1,2); COMMIT; -- si tout est correct -- ROLLBACK; -- en cas d’erreur détectée ``` *Étapes* : - `START TRANSACTION` ouvre le bloc. - Deux `UPDATE` modifient les notes. - Un `SELECT` de contrôle peut être exécuté pour vérifier que la somme reste inchangée. - Si tout est bon, `COMMIT` valide les modifications ; sinon, `ROLLBACK` annule les deux mises à jour. ## IV. Création d’une base de données en mode graphique ### A. Choix de l’outil Pour les épreuves du Baccalauréat, l’outil le plus répandu est **phpMyAdmin** (interface web pour MySQL/MariaDB). D’autres possibilités : MySQL Workbench (client lourd), Microsoft Access (pour les environnements Windows), ou encore pgAdmin pour PostgreSQL. ### B. Étapes de création (illustrées avec phpMyAdmin) 1. **Lancer phpMyAdmin** : ouvrir un navigateur, saisir l’adresse `http://localhost/phpmyadmin`, se connecter avec le compte root ou un compte dédié. 2. **Créer une nouvelle base** : dans l’onglet *Bases de données*, saisir le nom (ex. : `gestion_notes`) et choisir le classement `utf8_general_ci`, puis cliquer sur *Créer*. 3. **Définir les tables** : sélectionner la base créée, aller dans l’onglet *Structure*, cliquer sur *Créer une table*. - Nom de la table : `Eleve`. - Nombre de colonnes : 5 (idEleve, nom, prenom, dateNaissance, sexe). - Pour chaque colonne, spécifier le type, la longueur, les valeurs par défaut, les attributs (`NULL`/`NOT NULL`), et éventuellement l’auto‑incrément. - Cocher *Clé primaire* pour `idEleve`. - Cliquer sur *Enregistrer*. 4. **Ajouter les contraintes de clé étrangère** : après création de la table `Matiere`, retourner dans la structure de la table `Note`, choisir *Relation vue* (ou *Structure* → *Relation view*), puis définir : - Colonne `idEleve` → référence `Eleve(idEleve)`. - Colonne `idMatiere` → référence `Matiere(idMatiere)`. - Choisir l’action de mise à jour et de suppression (souvent `RESTRICT` ou `CASCADE`). 5. **Définir les index** : dans l’onglet *Structure* de chaque table, cliquer sur *Index* pour ajouter un index sur les colonnes fréquemment utilisées dans les `WHERE` (ex. : `idEleve` dans `Note`). 6. **Insérer des données de test** : utiliser l’onglet *Insérer* pour ajouter quelques lignes dans chaque table, vérifier que les contraintes sont respectées. 7. **Exporter / Importer** : pour sauvegarder, aller dans l’onglet *Exporter*, choisir le format SQL, cocher *Structure et données*, puis exécuter. L’import se fait via l’onglet *Importer*. > **À retenir** : En mode graphique, chaque action correspond à une instruction SQL sous‑jacente ; il est utile de consulter l’historique des requêtes (souvent affiché en bas de l’écran) pour comprendre ce qui est réellement exécuté. ### C. Exemple complet de création (script SQL généré) Le script suivant représente exactement ce que phpMyAdmin aurait généré pour la base `gestion_notes` : ```sql CREATE DATABASE IF NOT EXISTS gestion_notes CHARACTER SET utf8 COLLATE utf8_general_ci; USE gestion_notes; CREATE TABLE Eleve ( idEleve INT NOT NULL AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, dateNaissance DATE, sexe ENUM('M','F') NOT NULL, PRIMARY KEY (idEleve) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE Matiere ( idMatiere INT NOT NULL AUTO_INCREMENT, libelle VARCHAR(100) NOT NULL, PRIMARY KEY (idMatiere) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE Note ( idNote INT NOT NULL AUTO_INCREMENT, idEleve INT NOT NULL, idMatiere INT NOT NULL, valeur DECIMAL(4,2) NOT NULL, dateNote DATE, PRIMARY KEY (idNote), FOREIGN KEY (idEleve) REFERENCES Eleve(idEleve) ON DELETE RESTRICT ON UPDATE CASCADE, FOREIGN KEY (idMatiere) REFERENCES Matiere(idMatiere) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` *Points à remarquer* : - Le moteur de stockage choisi est `InnoDB` (supporte les clés étrangères et les transactions). - Les contraintes `ON DELETE RESTRICT` empêchent la suppression d’un élève ou d’une matière tant qu’il existe des notes associées. - `ON UPDATE CASCADE` propage la modification de la clé primaire vers les tables filles (rarement utilisé en pratique, mais montré ici à titre d’exemple). ### D. Bonnes pratiques en mode graphique - Toujours vérifier le type de moteur (`InnoDB` vs `MyISam`) avant de créer des tables nécessitant des transactions. - Utiliser des noms de tables et de colonnes en minuscules, sans accents ni espaces (préférer le souligné `_`). - Documenter chaque table grâce à la colonne *Commentaire* disponible dans phpMyAdmin. - Effectuer une sauvegarde avant toute modification structurelle importante. ## V. Ecriture/exécution des requêtes SQL ### A. La commande SELECT – révision approfondie La sélection est l’opération la plus fréquente. Nous détaillons chaque clause. #### 1. SELECT simple ```sql SELECT liste_champs FROM table WHERE condition; ``` #### 2. DISTINCT – éliminer les doublons ```sql SELECT DISTINCT sexe FROM Eleve; ``` #### 3. Alias (AS) ```sql SELECT nom AS NomFamille, prenom AS Prenom FROM Eleve; ``` #### 4. Opérateurs de comparaison `=`, `<>`, `>`, `<`, `>=`, `<=`, `BETWEEN`, `LIKE`, `IN`, `IS NULL`. #### 5. Expressions régulières (MySQL) ```sql SELECT nom FROM Eleve WHERE nom REGEXP '^A[aeiou]'; ``` #### 6. Fonctions de chaîne - `UPPER(chaine)`, `LOWER(chaine)`, `TRIM(chaine)`, `CONCAT(ch1,ch2)`, `SUBSTRING(chaine,debut,longueur)`. #### 7. Fonctions de date - `NOW()`, `CURDATE()`, `CURTIME()`, `DATE_ADD(date, INTERVAL n UNIT)`, `DATE_SUB`, `YEAR(date)`, `MONTH(date)`, `DAY(date)`. #### 8. Fonctions d’aggrégation `COUNT(*)`, `COUNT(champ)`, `SUM(champ)`, `AVG(champ)`, `MIN(champ)`, `MAX(champ)`. #### 9. GROUP BY et HAVING `GROUP BY` crée des groupes ; `HAVING` filtre les groupes après agrégation. #### 10. ORDER BY Trie le résultat final ; peut porter sur plusieurs colonnes (`ORDER BY col1 ASC, col2 DESC`). #### 11. LIMIT et OFFSET `LIMIT n` renvoie les n premières lignes ; `LIMIT offset, n` saute `offset` lignes puis prend `n`. ### B. Jointures (JOIN) Les jointures permettent de combiner des lignes de deux ou plusieurs tables selon une condition de jointure. #### 1. INNER JOIN (ou simplement JOIN) Renvoie uniquement les lignes où la condition de jointure est vraie dans les deux tables. ```sql SELECT e.nom, e.prenom, m.libelle AS Matiere, n.valeur FROM Eleve e INNER JOIN Note n ON e.idEleve = n.idEleve INNER JOIN Matiere m ON n.idMatiere = m.idMatiere; ``` #### 2. LEFT JOIN (LEFT OUTER JOIN) Renvoie toutes les lignes de la table de gauche, même si aucune correspondance n’existe dans la table de droite ; les champs de la droite sont remplis de `NULL`. ```sql SELECT e.nom, e.prenom, n.valeur FROM Eleve e LEFT JOIN Note n ON e.idEleve = n.idEleve WHERE n.idNote IS NULL; -- élèves sans aucune note ``` #### 3. RIGHT JOIN (RIGHT OUTER JOIN) Symétrique du LEFT JOIN ; rarement utilisé car on peut toujours inverser l’ordre des tables. #### 4. FULL OUTER JOIN MySQL ne le supporte pas directement ; on l’émule avec `UNION` de LEFT et RIGHT JOIN. #### 5. Jointure naturelle (NATURAL JOIN) Jointure basée sur les colonnes portant le même nom dans les deux tables ; à éviter en raison de son imprévisibilité. ### C. Sous‑requêtes Une sous‑requête est une instruction `SELECT` imbriquée dans une autre requête. #### 1. Sous‑requête scalaire (retourne une seule valeur) ```sql SELECT nom, prenom FROM Eleve WHERE idEleve = (SELECT idEleve FROM Eleve WHERE nom = 'Kamga' AND prenom = 'Aïcha' LIMIT 1); ``` #### 2. Sous‑requête retournant un ensemble (utilisée avec IN, ANY, ALL) ```sql SELECT nom, prenom FROM Eleve WHERE idEleve IN (SELECT idEleve FROM Note WHERE valeur < 8); ``` #### 3. Sous‑corrélée (fait référence à la requête externe) ```sql SELECT e.nom, e.prenom FROM Eleve e WHERE (SELECT AVG(valeur) FROM Note n WHERE n.idEleve = e.idEleve) >= 12; ``` ### D. Opérations ensemblistes `UNION`, `UNION ALL`, `INTERSECT` (non supporté directement en MySQL, émulé), `EXCEPT` (idem). #### Exemple UNION ```sql SELECT nom, prenom FROM Eleve WHERE sexe = 'M' UNION SELECT nom, prenom FROM Eleve WHERE dateNaissance > '2005-01-01'; ``` *Remarque* : `UNION` élimine les doublons ; `UNION ALL` les conserve. ### E. Requêtes de mise à jour avec jointure MySQL autorise les `UPDATE` et `DELETE` avec des jointures. #### Exemple UPDATE avec JOIN On veut augmenter de 1 point toutes les notes des élèves dont la moyenne générale est inférieure à 10. ```sql UPDATE Note n JOIN ( SELECT idEleve, AVG(valeur) AS moy FROM Note GROUP BY idEleve HAVING moy < 10 ) AS moyennes ON n.idEleve = moyennes.idEleve SET n.valeur = n.valeur + 1; ``` #### Exemple DELETE avec JOIN Supprimer les notes des élèves qui n’ont plus d’inscription active (supposons une table `Inscription`). ```sql DELETE n FROM Note n LEFT JOIN Inscription i ON n.idEleve = i.idEleve WHERE i.idEleve IS NULL; ``` ### F. Gestion des NULL Les valeurs `NULL` représentent l’absence de donnée. Elles nécessitent un traitement particulier. - Comparaison : `WHERE champ IS NULL` ou `WHERE champ IS NOT NULL`. - Fonctions : `IFNULL(champ, valeur_par_defaut)`, `COALESCE(champ1, champ2, …)`. #### Exemple COALESCE Afficher le nom complet, en utilisant le prénom si disponible, sinon un tiret. ```sql SELECT nom, COALESCE(prenom, '-') AS prenom FROM Eleve; ``` ### G. Exécution d’une requête dans phpMyAdmin 1. Sélectionner la base souhaitée dans le panneau de gauche. 2. Cliquer sur l’onglet *SQL*. 3. Saisir la requête dans la zone de texte. 4. Appuyer sur *Exécuter* (Ctrl+Enter). 5. Le résultat apparaît sous forme de tableau ; les lignes affectées sont indiquées pour les requêtes non‑SELECT. 6. Pour sauvegarder la requête, cliquer sur *Enregistrer la requête* (nécessite d’être connecté). > **À retenir** : Toujours terminer une requête par un point‑virgule (`;`) lorsqu’on en exécute plusieurs successivement dans le même onglet. ## Synthèse du chapitre - **Base de données** : ensemble organisé de données permanentes, géré par un SGBD. - **Modèle relationnel** : tables, lignes, colonnes, clés primaires/étrangères ; norme dominante pour l’enseignement. - **SGBD** : assure définition (DDL), manipulation (DML), contrôle (DCL, TCL) et administration. - **Architecture à trois niveaux** : externe (vues), conceptuel (schéma logique), interne (stockage physique). - **Langage SQL** : - DDL : `CREATE`, `ALTER`, `DROP`, `TRUNCATE`. - DML : `SELECT`, `INSERT`, `UPDATE`, `DELETE`. - DCL : `GRANT`, `REVOKE`. - TCL : `COMMIT`, `ROLLBACK`, `SAVEPOINT`. - **Contraintes** : `NOT NULL`, `UNIQUE`, `PRIMARY KEY`, `FOREIGN KEY`, `CHECK`. - **Jointures** : `INNER`, `LEFT`, `RIGHT` (et émulation de `FULL`). - **Sous‑requêtes** : scalaires, ensemblistes, corrélées. - **Fonctions d’aggrégation** : `COUNT`, `SUM`, `AVG`, `MIN`, `MAX` (souvent avec `GROUP BY`/`HAVING`). - **Transactions** : propriété ACID ; début avec `START TRANSACTION`, fin avec `COMMIT` ou `ROLLBACK`. - **Outils graphiques** : phpMyAdmin, MySQL Workbench ; permettent de créer la structure, insérer des données, exécuter des requêtes et visualiser le schéma. - **Bonnes pratiques** : nommage cohérent, utilisation d’InnoDB pour les FK, sauvegarde régulière, vérification des contraintes avant insertion. ## Glossaire | Terme | Définition | |-------|------------| | Base de données (BD) | Ensemble structuré de données permanentes, géré par un SGBD. | | SGBD | Système de Gestion de Bases de Données, logiciel intermédiaire entre utilisateurs/applications et données stockées. | | Modèle relationnel | Organisation des données en tables (relations) liées par des clés primaires et étrangères. | | Clé primaire (PK) | Attribut ou ensemble d’attributs qui identifie de façon unique chaque ligne d’une table. | | Clé étrangère (FK) | Attribut qui fait référence à la clé primaire d’une autre table, assurant l’intérimité référentielle. | | DDL | Langage de Définition de Données (CREATE, ALTER, DROP, TRUNCATE). | | DML | Langage de Manipulation de Données (SELECT, INSERT, UPDATE, DELETE). | | DCL | Langage de Contrôle de Données (GRANT, REVOKE). | | TCL | Langage de Contrôle de Transaction (COMMIT, ROLLBACK, SAVEPOINT). | | Jointure intérieure (INNER JOIN) | Retourne les lignes où la condition de jointure est vraie dans les deux tables. | | Sous‑requête | Instruction SELECT imbriquée dans une autre requête (scalaire, ensembliste, corrélée). | | Transaction | Groupe d’opérations traitées de façon atomique ; respecte les propriétés ACID. | | NULL | Valeur spéciale indiquant l’absence de donnée ; nécessite des opérateurs spécifiques (`IS NULL`). | | Index | Structure de données associée à une colonne permettant d’accélérer les recherches et les tris. | | Normalisation | Processus de décomposition d’une table pour éliminer la redondance et améliorer l’intégrité (1FN, 2FN, 3FN, BCNF). | | ACID | Atomicité, Cohérence, Isolation, Durabilité – propriétés garantissant la fiabilité des transactions. | ## Exercices d'application corrigés ### Exercice 1 – Création d’une bibliothèque **Consigne** : Créer une base de données nommée `bibliotheque` contenant les tables suivantes : - `Auteur` (`idAuteur` PK INT AUTO_INCREMENT, `nom` VARCHAR(50) NOT NULL, `prenom` VARCHAR(50) NOT NULL) - `Livre` (`idLivre` PK INT AUTO_INCREMENT, `titre` VARCHAR(150) NOT NULL, `idAuteur` FK REFERENCES Auteur(idAuteur), `annee` YEAR, `isbn` CHAR(13) UNIQUE) - `Emprunt` (`idEmprunt` PK INT AUTO_INCREMENT, `idLivre` FK REFERENCES Livre(idLivre), `idAdherent` INT NOT NULL, `dateEmprunt` DATE NOT NULL, `dateRetour` DATE) **Corrigé** : ```sql CREATE DATABASE IF NOT EXISTS bibliotheque CHARACTER SET utf8 COLLATE utf8_general_ci; USE bibliotheque; CREATE TABLE Auteur ( idAuteur INT NOT NULL AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, PRIMARY KEY (idAuteur) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE Livre ( idLivre INT NOT NULL AUTO_INCREMENT, titre VARCHAR(150) NOT NULL, idAuteur INT NOT NULL, annee YEAR, isbn CHAR(13) UNIQUE, PRIMARY KEY (idLivre), FOREIGN KEY (idAuteur) REFERENCES Auteur(idAuteur) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE Emprunt ( idEmprunt INT NOT NULL AUTO_INCREMENT, idLivre INT NOT NULL, idAdherent INT NOT NULL, dateEmprunt DATE NOT NULL, dateRetour DATE, PRIMARY KEY (idEmprunt), FOREIGN KEY (idLivre) REFERENCES Livre(idLivre) ON DELETE RESTRICT ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; ``` *Justifications* : - `ENGINE=InnoDB` pour supporter les clés étrangères. - `ON DELETE RESTRICT` empêche la suppression d’un auteur ou d’un livre tant qu’il est référencé. - `UNIQUE` sur `isbn` garantit l’unicité du code livre. --- ### Exercice 2 – Insertion et sélection simple **Consigne** : À partir de la base `bibliotheque` créée précédemment : 1. Insérer un auteur *Victor Hugo* (nom : Hugo, prenom : Victor). 2. Insérer un livre *Les Misérables* publié en 1862, ISBN : 9780141439516, lié à l’auteur venant d’être inséré. 3. Afficher la liste des livres avec le nom complet de leur auteur (nom suivi d’un espace puis le prénom). **Corrigé** : 1. Insertion de l’auteur : ```sql INSERT INTO Auteur (nom, prenom) VALUES ('Hugo', 'Victor'); ``` 2. Récupération de l’`idAuteur` généré (on peut utiliser `LAST_INSERT_ID()`) : ```sql INSERT INTO Livre (titre, idAuteur, annee, isbn) VALUES ('Les Misérables', LAST_INSERT_ID(), 1862, '9780141439516'); ``` 3. Sélection avec concaténation : ```sql SELECT L.titre, CONCAT(A.nom, ' ', A.prenom) AS Auteur FROM Livre L JOIN Auteur A ON L.idAuteur = A.idAuteur; ``` *Résultat attendu* : | titre | Auteur | |----------------|--------------| | Les Misérables | Hugo Victor | --- ### Exercice 3 – Agrégation et filtre **Consigne** : Toujours dans la base `bibliotheque`, supposons que la table `Emprunt` contient déjà plusieurs enregistrements. 1. Calculer le nombre total d’emprunts effectués. 2. Déterminer le nombre d’emprunts par livre (afficher le titre du livre et le nombre d’emprunts). 3. Lister les livres qui n’ont jamais été empruntés. **Corrigé** : 1. Nombre total d’emprunts : ```sql SELECT COUNT(*) AS TotalEmprunts FROM Emprunt; ``` 2. Emprunts par livre : ```sql SELECT L.titre, COUNT(E.idEmprunt) AS NbEmprunts FROM Livre L LEFT JOIN Emprunt E ON L.idLivre = E.idLivre GROUP BY L.idLivre, L.titre ORDER BY NbEmprunts DESC; ``` 3. Livres jamais empruntés : ```sql SELECT L.titre FROM Livre L LEFT JOIN Emprunt E ON L.idLivre = E.idLivre WHERE E.idEmprunt IS NULL; ``` *Explications* : - Le `LEFT JOIN` préserve tous les livres, même ceux sans emprunt ; les lignes où `idEmprunt` est `NULL` correspondent aux livres jamais empruntés. - Le `GROUP BY` sur `L.idLivre` garantit que chaque livre forme un groupe distinct, même si deux livres avaient le même titre (improbable mais possible). --- ### Exercice 4 – Mise à jour avec jointure **Consigne** : Dans la base `bibliotheque`, on veut augmenter de 5 jours la durée de tous les emprunts en cours (c’est‑à‑dire ceux dont `dateRetour` est `NULL`) en ajoutant 5 jours à la date d’emprunt pour obtenir une nouvelle date de retour prévue. **Corrigé** : ```sql UPDATE Emprunt SET dateRetour = DATE_ADD(dateEmprunt, INTERVAL 5 DAY) WHERE dateRetour IS NULL; ``` *Justification* : - La clause `WHERE` cible uniquement les emprunts en cours. - `DATE_ADD(dateEmprunt, INTERVAL 5 DAY)` ajoute cinq jours à la date d’emprunt. --- ### Exercice 5 – Sous‑requête corrélée **Consigne** : Afficher les auteurs qui ont au moins un livre emprunté plus de trois fois. **Corrigé** : ```sql SELECT A.nom, A.prenom FROM Auteur A WHERE ( SELECT COUNT(*) FROM Livre L JOIN Emprunt E ON L.idLivre = E.idLivre WHERE L.idAuteur = A.idAuteur ) > 3; ``` *Étapes* : - La sous‑requête compte, pour chaque auteur externe `A`, le nombre d’emprunts liés à ses livres. - Le résultat de la sous‑requête est comparé à 3 grâce au `WHERE` extérieur. --- ### Exercice 6 – Transaction (gestion d’un emprunt et d’un retour) **Consigne** : Simuler l’opération suivante dans une transaction : 1. Insérer un nouvel emprunt pour le livre d’id = 3, effectué par l’adhérent d’id = 7, à la date du jour. 2. Mettre à jour le champ `dateRetour` de l’emprunt le plus ancien (le plus petit `idEmprunt`) du même livre à la date du jour (simulant le retour). 3. Valider les deux opérations seulement si le livre n’est pas déjà emprunté (c’est‑à‑dire s’il n’existe aucun emprunt avec `dateRetour IS NULL` pour ce livre). **Corrigé** : ```sql START TRANSACTION; -- Vérification de disponibilité du livre (id = 3) SET @nbEmpruntsEnCours = ( SELECT COUNT(*) FROM Emprunt WHERE idLivre = 3 AND dateRetour IS NULL ); IF @nbEmpruntsEnCours = 0 THEN -- 1. Nouvel emprunt INSERT INTO Emprunt (idLivre, idAdherent, dateEmprunt) VALUES (3, 7, CURDATE()); -- 2. Retour de l’emprunt le plus ancien du même livre UPDATE Emprunt SET dateRetour = CURDATE() WHERE idLivre = 3 AND dateRetour IS NULL ORDER BY idEmprunt ASC LIMIT 1; COMMIT; ELSE ROLLBACK; END IF; ``` *Remarque* : La syntaxe `IF…THEN…ELSE…END IF` est valide dans les procédures stockées MySQL ; dans un script simple on pourrait remplacer par un `SELECT` suivi d’un `IF` dans le langage d’application. L’important ici est de montrer la logique de contrôle avant validation. ## Erreurs fréquentes | # | Erreur classique | Pourquoi elle se produit | Comment l’éviter | |---|------------------|--------------------------|------------------| | 1 | Oublier le `;` à la fin d’une instruction SQL lorsqu’on en exécute plusieurs dans le même onglet. | Le SGBD considère que la requête n’est pas terminée et attend la suite, provoquant une erreur de syntaxe. | Toujours terminer chaque instruction par un point‑virgule ; dans phpMyAdmin, vérifier que la zone de requête ne contient pas de retour à la ligne non terminé avant d’appuyer sur *Exécuter*. | | 2 | Confondre `DELETE FROM table` (sans `WHERE`) avec `TRUNCATE TABLE`. | Les deux suppriment toutes les lignes, mais `TRUNCATE` réinitialise les compteurs d’auto‑incrément et ne déclenche pas les déclencheurs ; l’étudiant peut perdre des données inattendues. | Toujours inclure une clause `WHERE` explicite lorsqu’on veut supprimer des lignes spécifiques ; réserver `TRUNCATE` aux cas où l’on veut vraiment vider la table et réinitialiser les compteurs. | | 3 | Omettre de préciser le moteur de stockage (`ENGINE=InnoDB`) lors de la création d’une table nécessitant des clés étrangères. | Avec le moteur par défaut `MyISam`, les déclarations de `FOREIGN KEY` sont ignorées silencieusement, entraînant une perte d’intégrité référentielle. | Spécifier systématiquement `ENGINE=InnoDB` (ou un autre moteur supportant les FK) dès la création de la table. | | 4 | Utiliser `=` pour comparer avec `NULL` (`WHERE champ = NULL`). | En SQL, `NULL` représente une valeur inconnue ; toute comparaison avec `=` ou `<>` retourne `UNKNOWN`, donc aucune ligne n’est sélectionnée. | Utiliser `IS NULL` ou `IS NOT NULL` pour tester la présence ou l’absence de valeur nulle. | | 5 | Négliger le `GROUP BY` lorsqu’on utilise une fonction d’aggrégation avec d’autres colonnes sélectionnées. | Le SGBD lève une erreur « Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column… » (selon le mode `ONLY_FULL_GROUP_BY`). | Toujours inclure dans le `GROUP BY` toutes les colonnes non agrégées présentes dans la clause `SELECT`. | | 6 | Mettre à jour ou supprimer des lignes sans vérifier d’abord l’impact (absence de `SELECT` préalable). | On peut accidentellement modifier ou effacer davantage de lignes que prévu, entraînant une perte de données. | Avant un `UPDATE` ou un `DELETE`, exécuter un `SELECT` avec la même clause `WHERE` pour visualiser les lignes concernées. | | 7 | Oublier de faire un `COMMIT` après une transaction, laissant les verrous en place et bloquant les autres utilisateurs. | La transaction reste ouverte ; les ressources restent verrouillées jusqu’à un timeout ou un `ROLLBACK` explicite. | Toujours conclure une transaction explicite par `COMMIT` (en cas de succès) ou `ROLLBACK` (en cas d’échec). | | 8 | Utiliser des noms de tables ou de colonnes contenant des espaces ou des accents sans les délimiter par des backticks (`` ` ``). | Le parseur SQL interprète l’espace comme séparateur de tokens, provoquant une erreur de syntaxe. | Encadrer les identifiants spéciaux avec des backticks, ou mieux, éviter les espaces et les accents dans les noms. | --- *Fin du cours de référence.* *Ce document respecte les exigences du programme officiel MINESEC/IGE pour la série Terminale (A4, A1, A2, A3, A5) et propose un traitement complet, détaillé et illustré du chapitre « IMPLEMENTATION D’UNE BASE DE DONNEES », adapté à une étude autonome en vue du Baccalauréat camerounais.*