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

## Objectifs et prérequis **Objectifs** : À la fin de ce chapitre, l’élève sera capable de - concevoir et créer une base de données relationnelle à l’aide d’un outil graphique (schéma, tables, champs, types, clés primaires/étrangères, contraintes) ; - écrire, exécuter et interpréter des requêtes SQL de sélection, de modification et d’agrégation, en maîtrisant les jointures et les sous‑requêtes ; - justifier chaque étape de la conception ou de la requête par référence aux concepts du modèle relationnel. **Prérequis** : - notions de base du modèle relationnel (table, ligne, colonne, clé primaire, clé étrangère) ; - familiarité avec l’environnement Windows/Linux et la manipulation de fichiers ; - connaissance des types de données simples (entier, réel, chaîne, date, booléen). --- ## Introduction contextualisée Dans le monde professionnel camerounais, la gestion de l’information s’appuie de plus en plus sur des systèmes de bases de données : suivi des dossiers élèves dans les établissements scolaires, gestion des stocks dans les commerces, ou encore traçabilité des produits agricoles. Le programme MINESEC/IGE insiste sur une approche par compétences : l’élève ne doit pas seulement savoir utiliser un SGBD, mais être capable de **concevoir** une structure adaptée aux besoins réels et d’interroger celle‑ci avec pertinence. Ce chapitre constitue la deuxième partie de l’UA 6 : après avoir étudié les fondements théoriques (modèle relationnel, algèbre relationnelle), nous passons à la mise en œuvre pratique, d’abord en mode graphique puis par l’écriture directe de requêtes SQL. --- ## I. Création d’une base de données en mode graphique ### 1. Principes généraux de la conception graphique Un SGBD graphique (MySQL Workbench, phpMyAdmin, Microsoft Access, etc.) permet de représenter visuellement le schéma relationnel : chaque tableau correspond à une entité, chaque colonne à un attribut, et les lignes de liaison représentent les relations (clés étrangères). > **> Définition** : Une **clé primaire** est un attribut (ou un ensemble d’attributs) qui identifie de façon unique chaque ligne d’une table. Elle doit être **non nulle** et **unique**. > **> Définition** : Une **clé étrangère** est un attribut d’une table qui fait référence à la clé primaire d’une autre table, assurant ainsi l’intégrité référentielle. ### 2. Étapes de création dans un outil graphique 1. **Création du schéma** (ou « database ») : nom du SGBD, jeu de caractères (UTF‑8 recommandé). 2. **Définition des tables** : nom de la table, liste des attributs avec leur type (INT, VARCHAR(50), DATE, etc.). 3. **Attribution de la clé primaire** : clic droit → « Set Primary Key ». 4. **Ajout des contraintes** : NOT NULL, UNIQUE, CHECK, DEFAULT. 5. **Création des relations** : glisser‑déposer la clé étrangère vers la clé primaire référencée ; choisir le type d’action en cas de suppression/mise à jour (CASCADE, SET NULL, RESTRICT). 6. **Génération du script SQL** (optionnelle) pour vérifier le code produit. ### 3. Exemple 1 – Table simple « Étudiant » **Objectif** : créer une table contenant les informations d’identification des élèves. | Étape | Action dans l’interface | Résultat SQL (extrait) | |------|--------------------------|------------------------| | 1 | Créer la base `GestionScolaire` | `CREATE DATABASE GestionScolaire CHARACTER SET utf8mb4;` | | 2 | Ajouter la table `Etudiant` | `CREATE TABLE Etudiant ( ... );` | | 3 | Définir les champs | `id INT NOT NULL AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, date_naissance DATE, sexe ENUM('M','F'),` | | 4 | Définir la clé primaire | `PRIMARY KEY (id)` | | 5 | Ajouter une contrainte UNIQUE sur le couple (nom, prenom) | `UNIQUE KEY uk_nom_prenom (nom, prenom)` | | 6 | Valider | — | **Script complet** : ```sql CREATE DATABASE GestionScolaire CHARACTER SET utf8mb4; USE GestionScolaire; CREATE TABLE Etudiant ( id INT NOT NULL AUTO_INCREMENT, nom VARCHAR(50) NOT NULL, prenom VARCHAR(50) NOT NULL, date_naissance DATE, sexe ENUM('M','F'), PRIMARY KEY (id), UNIQUE KEY uk_nom_prenom (nom, prenom) ); ``` ### 4. Exemple 2 – Schéma complet avec relations **Contexte** : gérer les inscriptions des élèves à des cours. Trois tables : `Etudiant`, `Cours`, `Inscription`. > **> Règle** : Dans une relation **N‑N** (ici Etudiant ↔ Cours), on introduit une table d’association dont la clé primaire est composée des deux clés étrangères. **Étapes** : 1. **Table Etudiant** (comme ci‑dessus). 2. **Table Cours** : ```sql CREATE TABLE Cours ( code CHAR(6) NOT NULL, libelle VARCHAR(100) NOT NULL, volume_horaire INT NOT NULL CHECK (volume_horaire > 0), PRIMARY KEY (code) ); ``` 3. **Table Inscription** : ```sql CREATE TABLE Inscription ( id_etudiant INT NOT NULL, code_cours CHAR(6) NOT NULL, date_inscription DATE NOT NULL, note DECIMAL(4,2), PRIMARY KEY (id_etudiant, code_cours), FOREIGN KEY (id_etudiant) REFERENCES Etudiant(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (code_cours) REFERENCES Cours(code) ON DELETE RESTRICT ON UPDATE CASCADE ); ``` **Vérification** : l’outil graphique affichera deux lignes de liaison provenant d’`Inscription` vers `Etudiant` et `Cours`. Les icônes de clé montrent bien la composition de la clé primaire d’`Inscription`. > **> Rappel** : La contrainte `ON DELETE CASCADE` signifie que la suppression d’un étudiant entraîne automatiquement la suppression de ses inscriptions, préservant ainsi l’intégrité référentielle. --- ## II. Ecriture/exécution des requêtes SQL ### 1. Rappel du langage SQL SQL se répartit en quatre sous‑langages : | Sous‑langage | Fonction | Exemples d’instructions | |--------------|----------|--------------------------| | DDL (Data Definition Language) | Définition/ modification du schéma | `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE` | | DML (Data Manipulation Language) | Manipulation des données | `INSERT`, `UPDATE`, `DELETE` | | DQL (Data Query Language) | Interrogation | `SELECT` | | DCL (Data Control Language) | Gestion des droits | `GRANT`, `REVOKE` | ### 2. Requêtes de sélection (DQL) La clause de base est `SELECT`. On peut ajouter : - `WHERE` : condition de filtrage. - `ORDER BY` : tri (ASC/DESC). - `LIMIT` : limitation du nombre de lignes. - `GROUP BY` : regroupement pour agrégation. - `HAVING` : condition sur les groupes. > **> Formule** : Le nombre de lignes retournées après un `WHERE` est donné par > $$N_{\text{résultat}} = \sum_{i=1}^{N_{\text{table}}} \mathbf{1}_{\text{condition}(i)}$$ > où $\mathbf{1}_{\text{condition}}$ vaut 1 si la condition est vraie, 0 sinon. #### Exemple 1 – Sélection simple **Question** : Lister les noms et prénoms des étudiants nés après le 01/01/2005, triés par nom. ```sql SELECT nom, prenom FROM Etudiant WHERE date_naissance > '2005-01-01' ORDER BY nom ASC; ``` **Étapes** : 1. `FROM Etudiant` désigne la table source. 2. `WHERE date_naissance > '2005-01-01'` filtre les lignes. 3. `SELECT nom, prenom` projette les deux colonnes. 4. `ORDER BY nom ASC` trie le résultat. #### Exemple 2 – Agrégation avec GROUP BY et HAVING **Question** : Pour chaque cours ayant au moins 5 inscrits, afficher le code du cours, son libellé et la moyenne des notes (arrondie à 2 décimales). ```sql SELECT C.code AS code_cours, C.libelle AS libelle_cours, ROUND(AVG(I.note), 2) AS moyenne_note FROM Cours C JOIN Inscription I ON C.code = I.code_cours WHERE I.note IS NOT NULL GROUP BY C.code, C.libelle HAVING COUNT(I.id_etudiant) >= 5 ORDER BY moyenne_note DESC; ``` **Étapes détaillées** : 1. `JOIN` (inner jointure) associe chaque inscription à son cours. 2. `WHERE I.note IS NOT NULL` exclut les notes nulles du calcul de la moyenne. 3. `GROUP BY C.code, C.libelle` crée un groupe par cours. 4. `AVG(I.note)` calcule la moyenne des notes dans chaque groupe. 5. `ROUND(...,2)` arrondit à deux décimales. 6. `HAVING COUNT(I.id_etudiant) >= 5` ne garde que les groupes avec au moins cinq inscriptions. 7. `ORDER BY moyenne_note DESC` trie du meilleur au pire résultat. ### 3. Requêtes de modification (DML) #### INSERT ```sql INSERT INTO Etudiant (nom, prenom, date_naissance, sexe) VALUES ('Kamga', 'Aïcha', '2006-09-12', 'F'); ``` #### UPDATE ```sql UPDATE Etudiant SET sexe = 'M' WHERE id = 42; ``` #### DELETE ```sql DELETE FROM Inscription WHERE code_cours = 'MATH101' AND date_inscription < '2023-01-01'; ``` > **> Astuce** : Toujours accompagner un `UPDATE` ou un `DELETE` d’une clause `WHERE` précise ; sinon la modification s’applique à toute la table. ### 4. Jointures - **INNER JOIN** : ne garde que les lignes où la condition de jointure est vraie dans les deux tables. - **LEFT JOIN (LEFT OUTER JOIN)** : garde toutes les lignes de la table de gauche, même si aucune correspondance n’existe à droite (les colonnes de la table droite sont alors NULL). - **RIGHT JOIN** : symétrique du LEFT JOIN. #### Exemple de LEFT JOIN **Question** : Lister tous les étudiants et, s’ils existent, le cours auquel ils sont inscrits ainsi que leur note ; afficher aussi les étudiants sans inscription. ```sql SELECT E.nom, E.prenom, C.libelle AS cours, I.note FROM Etudiant E LEFT JOIN Inscription I ON E.id = I.id_etudiant LEFT JOIN Cours C ON I.code_cours = C.code ORDER BY E.nom, E.prenom; ``` **Résultat attendu** : les étudiants sans inscription affichent `NULL` pour `cours` et `note`. ### 5. Sous‑requêtes Une sous‑requête est une instruction `SELECT` imbriquée dans une autre requête. Elle peut apparaître dans `WHERE`, `FROM` ou `HAVING`. #### Exemple – Sous‑requête dans WHERE **Question** : Trouver les étudiants qui ont une note supérieure à la moyenne générale de toutes les notes. ```sql SELECT nom, prenom FROM Etudiant WHERE id IN ( SELECT id_etudiant FROM Inscription WHERE note > (SELECT AVG(note) FROM Inscription) ); ``` **Étapes** : 1. La sous‑requête la plus interne `(SELECT AVG(note) FROM Inscription)` calcule la moyenne globale. 2. La sous‑requête intermédiaire sélectionne les `id_etudiant` dont la note dépasse cette moyenne. 3. La requête externe récupère le nom et le prénom des étudiants dont l’identifiant figure dans ce ensemble. > **> Rappel** : Une sous‑requête renvoyant plusieurs lignes doit être utilisée avec les opérateurs `IN`, `ANY`, `ALL` ou `EXISTS`. --- ## Synthèse du chapitre - **Conception graphique** : créer une base, définir tables, types, clés primaires/étrangères, contraintes ; visualiser les relations. - **SQL DDL** : `CREATE DATABASE`, `CREATE TABLE`, `ALTER TABLE`, `DROP TABLE`. - **SQL DML** : `INSERT`, `UPDATE`, `DELETE` (toujours avec `WHERE` précis). - **SQL DQL** : `SELECT … FROM … [WHERE] [ORDER BY] [LIMIT] [GROUP BY] [HAVING]`. - **Jointures** : `INNER JOIN` (défaut), `LEFT JOIN`, `RIGHT JOIN`. - **Sous‑requêtes** : imbriquées dans `WHERE`, `FROM`, `HAVING` ; opérateurs `IN`, `EXISTS`, `ANY`, `ALL`. - **Intégrité référentielle** : garantie par les clés étrangères et les actions `ON DELETE/UPDATE` (`CASCADE`, `SET NULL`, `RESTRICT`). - **Bonnes pratiques** : nommer les objets de façon explicite, utiliser des types adaptés, commenter les scripts, sauvegarder avant toute modification massive. --- ## Glossaire | Terme | Définition | |-------|------------| | Clé primaire | Attribut (ou ensemble) qui identifie de façon unique chaque ligne 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. | | Contrainte | Règle imposée sur une colonne ou une table (NOT NULL, UNIQUE, CHECK, DEFAULT). | | Schéma | Ensemble des définitions de tables, de leurs attributs et de leurs relations dans une base de données. | | Jointure intérieure (INNER JOIN) | Opération qui ne retourne que les lignes où la condition de jointure est vraie dans les deux tables. | | Jointure extérieure gauche (LEFT JOIN) | Retourne toutes les lignes de la table de gauche, même sans correspondance à droite. | | Sous‑requête | Requête `SELECT` imbriquée dans une autre requête SQL. | | Agrégation | Opération qui regroupe des lignes et applique une fonction (SUM, AVG, COUNT, MIN, MAX) sur chaque groupe. | | Transaction | Suite d’opérations SQL exécutées comme une unité indivisible (ACID). | | Normalisation | Processus d’organisation des données pour réduire la redondance et améliorer l’intégrité. | | DDL | Data Definition Language – commandes de définition du schéma (CREATE, ALTER, DROP). | | DML | Data Manipulation Language – commandes de manipulation des données (INSERT, UPDATE, DELETE). | | DQL | Data Query Language – commande d’interrogation (SELECT). | | DCL | Data Control Language – commandes de gestion des droits (GRANT, REVOKE). | | ACID | Propriétés d’une transaction : Atomicité, Cohérence, Isolation, Durabilité. | --- ## Exercices d'application corrigés ### Exercice 1 – Création de table **Consigne** : À l’aide de l’interface graphique de votre SGBD, créez une table `Employe` comportant les champs : `matricule` (CHAR(8), clé primaire), `nom` (VARCHAR(30), NOT NULL), `prenom` (VARCHAR(30)), `date_embauche` (DATE NOT NULL), `salaire` (DECIMAL(10,2) CHECK (salaire > 0)), `service` (VARCHAR(20)). **Corrigé** : 1. Ouvrir le SGBD → créer une nouvelle base `Entreprise`. 2. Cliquer droit → *New Table* → nommer `Employe`. 3. Ajouter les colonnes avec les types indiqués. 4. Sélectionner `matricule` → clic droit → *Set Primary Key*. 5. Pour `date_embauche` cocher *NOT NULL*. 6. Pour `salaire` ajouter une *Check Constraint* : `salaire > 0`. 7. Valider → le SGBD génère le script suivant : ```sql CREATE DATABASE Entreprise CHARACTER SET utf8mb4; USE Entreprise; CREATE TABLE Employe ( matricule CHAR(8) NOT NULL, nom VARCHAR(30) NOT NULL, prenom VARCHAR(30), date_embauche DATE NOT NULL, salaire DECIMAL(10,2) CHECK (salaire > 0), service VARCHAR(20), PRIMARY KEY (matricule) ); ``` --- ### Exercice 2 – Requête de sélection simple **Consigne** : Dans la table `Employe` de l’exercice précédent, afficher le nom, le prénom et le salaire des employés embauchés après le 01/01/2020, triés par salaire décroissant. **Corrigé** : ```sql SELECT nom, prenom, salaire FROM Employe WHERE date_embauche > '2020-01-01' ORDER BY salaire DESC; ``` **Explication** : - `FROM Employe` désigne la source. - `WHERE date_embauche > '2020-01-01'` filtre les lignes. - `SELECT nom, prenom, salaire` projette les colonnes demandées. - `ORDER BY salaire DESC` trie du salaire le plus élevé au plus bas. --- ### Exercice 3 – Jointure intérieure **Consigne** : Deux tables existent : `Projet` (`code_proj` CHAR(6) PK, `intitule` VARCHAR(100), `budget` DECIMAL(12,2)) et `Participation` (`matricule` CHAR(8) FK → `Employe.matricule`, `code_proj` CHAR(6) FK → `Projet.code_proj`, `role` VARCHAR(30)). Afficher l’intitulé du projet, le nom et le prénom de chaque employé participant ainsi que son rôle. **Corrigé** : ```sql SELECT P.intitule AS projet, E.nom, E.prenom, Part.role FROM Participation Part JOIN Employe E ON Part.matricule = E.matricule JOIN Projet P ON Part.code_proj = P.code_proj ORDER BY P.intitule, E.nom; ``` **Explication** : - Deux `JOIN` successifs relient `Participation` aux tables `Employe` et `Projet` via leurs clés étrangères. - Le `SELECT` projette les champs demandés. - Le `ORDER BY` classe le résultat par projet puis par nom d’employé. --- ### Exercice 4 – Agrégation avec GROUP BY et HAVING **Consigne** : Pour chaque service ayant au moins 3 employés, afficher le libellé du service, le nombre d’employés et le salaire moyen (arrondi à 2 décimales). **Corrigé** : ```sql SELECT service, COUNT(*) AS nb_employes, ROUND(AVG(salaire), 2) AS salaire_moyen FROM Employe GROUP BY service HAVING COUNT(*) >= 5 ORDER BY salaire_moyen DESC; ``` **Explication** : - `GROUP BY service` crée un groupe par valeur du champ `service`. - `COUNT(*)` compte les lignes de chaque groupe. - `AVG(salaire)` calcule la moyenne du salaire dans le groupe. - `ROUND(...,2)` arrondit à deux décimales. - `HAVING COUNT(*) >= 5` ne conserve que les groupes avec cinq employés ou plus (on a modifié le seuil pour montrer l’usage de HAVING). - `ORDER BY salaire_moyen DESC` trie du salaire moyen le plus élevé au plus bas. --- ### Exercice 5 – Sous‑requête avec EXISTS **Consigne** : Lister les employés qui participent à au moins un projet dont le budget dépasse 10 000 000 FCFA. **Corrigé** : ```sql SELECT DISTINCT E.nom, E.prenom FROM Employe E WHERE EXISTS ( SELECT 1 FROM Participation P JOIN Projet Pr ON P.code_proj = Pr.code_proj WHERE P.matricule = E.matricule AND Pr.budget > 10000000 ); ``` **Explication** : - La sous‑requête corrélée (`WHERE P.matricule = E.matricule`) vérifie, pour chaque employé `E`, l’existence d’une ligne dans `Participation` liée à un projet `Pr` dont le budget > 10 000 000. - `SELECT 1` est une convention : on ne renvoie aucune colonne utile, juste un indicateur d’existence. - `DISTINCT` évite les doublons si un employé participe à plusieurs tels projets. --- ### Exercice 6 – Modification de données avec transaction **Consigne** : Augmenter de 8 % le salaire de tous les employés du service « Comptabilité », puis supprimer les employés dont le salaire dépasse 1 500 000 FCFA après augmentation. Effectuer ces deux opérations dans une même transaction afin de garantir l’atomicité. **Corrigé** : ```sql START TRANSACTION; -- Augmentation de salaire UPDATE Employe SET salaire = salaire * 1.08 WHERE service = 'Comptabilité'; -- Suppression des salariés trop bien rémunérés DELETE FROM Employe WHERE salaire > 1500000 AND service = 'Comptabilité'; COMMIT; ``` **Explication** : - `START TRANSACTION` ouvre une séquence d’opérations qui seront validées ensemble. - La première `UPDATE` augmente le salaire de 8 % pour le service visé. - La seconde `DELETE` retire les employés dont le nouveau salaire dépasse le seuil. - `COMMIT` rend les modifications permanentes. En cas d’erreur, on pourrait exécuter `ROLLBACK` pour annuler les deux étapes. --- ## Erreurs fréquentes | Erreur fréquente | Pourquoi elle se produit | Comment l’éviter | |------------------|--------------------------|------------------| | Oublier la clause `WHERE` dans un `UPDATE` ou un `DELETE` | La modification s’applique à toute la table, entraînant une perte de données massive. | Toujours rédiger la condition `WHERE` en premier, puis exécuter la requête en mode *SELECT* identique pour vérifier les lignes concernées. | | Confondre `INNER JOIN` et `LEFT JOIN` | On obtient moins de lignes que prévu (avec `INNER`) ou des `NULL` inattendus (avec `LEFT`). | Dessiner mentalement le schéma des tables et se demander si l’on veut conserver les lignes sans correspondance (`LEFT`) ou uniquement les correspondances (`INNER`). | | Utiliser `=` au lieu de `IN` ou `EXISTS` avec une sous‑requête renvoyant plusieurs lignes | L’opérateur `=` attend une seule valeur ; l’erreur « single-row subquery returns more than one row » apparaît. | Lorsque la sous‑requête peut retourner plusieurs valeurs, privilégier `IN`, `EXISTS`, `ANY` ou `ALL`. | | Omettre de spécifier le type ou la longueur d’un `VARCHAR` | Le SGBD peut attribuer une longueur par défaut inadéquate, provoquant des troncatures ou des erreurs d’insertion. | Toujours déclarer explicitement la longueur (`VARCHAR(50)`, `VARCHAR(255)`, etc.) selon le besoin métier. | | Négliger les contraintes d’intégrité référentielle lors de la suppression d’une ligne référencée | Le SGBD rejette l’opération (`Cannot delete or update a parent row`) ou, si on utilise `ON DELETE CASCADE`, supprime involontairement des données liées. | Vérifier les actions `ON DELETE/UPDATE` définies sur les clés étrangères ; utiliser `SELECT` préalable pour voir l’impact d’une suppression. | | Mettre une virgule après le dernier champ d’une liste de colonnes dans un `INSERT` | Erreur de syntaxe : « You have an error in your SQL syntax… ». | Relire la ligne avant d’exécuter ; certains éditeurs signalent la virgule superflue en rouge. | --- *Fin du cours.*