Bases de Données et Sql MOUOMENE

Bases de Données et Sql

Par :
MOUOMENE FABIEN

P a g e 1 | 21
Bases de Données et Sql MOUOMENE

Chapitre1:
Principes de Conception des Bases de Données Relationnelles
Objectifs du Chapitre

● Comprendre les dépendances fonctionnelles et leur rôle dans la structuration des

données.
● Appliquer les algorithmes de normalisation pour améliorer la qualité des schémas.
● Maîtriser les formes normales (1FN à 5FN).
● Intégrer les contraintes d’intégrité pour garantir la fiabilité des données.

I. Dépendances Fonctionnelles
Définition

Une dépendance fonctionnelle (DF) est une relation logique entre deux ensembles
d’attributs. Elle exprime que la connaissance d’un attribut (ou d’un groupe d’attributs) permet
de déterminer un autre attribut.

Notation : Si A → B, alors la valeur de A détermine la valeur de B.

Exemple

Dans une table EMPLOYÉ(ID, Nom, Service), la dépendance fonctionnelle est : ID → Nom,
Service

Cela signifie que chaque ID correspond à un seul nom et un seul service.

Types de dépendances

● Simple : A → B
● Composée : (A, B) → C
● Partielle : une partie de la clé détermine un attribut
● Transitive : A → B et B → C ⇒ A → C

II. Algorithmes de Normalisation

La normalisation est un processus de transformation des relations pour :

● Éliminer les redondances

● Éviter les anomalies de mise à jour
● Améliorer la cohérence

Étapes générales

P a g e 2 | 21
Bases de Données et Sql MOUOMENE

1. Identifier les DF
2. Déterminer la clé primaire
3. Appliquer les règles des formes normales
4. Décomposer les relations si nécessaire

Méthodologie

● Analyse des DF
● Décomposition en relations plus simples
● Vérification de la préservation des DF
● Vérification de la non-perte d’information

III. Les Formes Normales

1FN : Première Forme Normale

● Valeurs atomiques
● Suppression des groupes répétitifs

Exemple : | ID | Nom | Téléphones | ❌→ devient | ID | Nom | Téléphone | ✅

2FN : Deuxième Forme Normale

● Être en 1FN
● Éliminer les dépendances partielles

Exemple : Une table avec clé composée (ID_Commande, ID_Client) et attribut Nom_Client
→ dépendance partielle

3FN : Troisième Forme Normale

● Être en 2FN
● Éliminer les dépendances transitives

Exemple : ID_Employé → ID_Service → Nom_Service ⇒ ID_Employé → Nom_Service ⇒

4FN : Quatrième Forme Normale

● Être en 3FN
● Éliminer les dépendances multivaluées

Exemple : Un professeur enseigne plusieurs cours et parle plusieurs langues → deux

dépendances indépendantes

5FN : Cinquième Forme Normale

● Être en 4FN
● Éliminer les dépendances de jointure

P a g e 3 | 21
Bases de Données et Sql MOUOMENE

Exemple : Vendeur, Produit, Client → peut être reconstruit à partir de relations binaires

IV. Contraintes d’Intégrité

1. Contraintes Statiques

Appliquées directement sur les données stockées.

Types :

● Clé primaire
● Clé étrangère
● UNIQUE
● NOT NULL
● CHECK

2. Contraintes Dynamiques

Appliquées lors des opérations de modification.

Exemples :

● Interdire la suppression d’un client ayant des commandes

● Vérifier la validité d’un champ avant insertion

3. Contraintes liées à la Transaction

Principes ACID :

● Atomicité : tout ou rien

● Cohérence : état valide avant et après
● Isolation : transactions indépendantes
● Durabilité : données persistantes après validation

Activités Pédagogiques
1. Identifier les DF dans une base fictive
2. Normaliser une table jusqu’à la 3FN
3. Implémenter les contraintes dans MySQL
4. Simuler des transactions concurrentes

P a g e 4 | 21
Bases de Données et Sql MOUOMENE

Chapitre2 : Langage SQL

Objectifs du Chapitre
● Comprendre la structure et la syntaxe du langage SQL.
● Maîtriser les commandes de définition, manipulation, contrôle et requête des
données.
● Savoir créer, modifier et interroger des bases de données relationnelles.
● Appliquer SQL dans des environnements comme MySQL, MS Access, PostgreSQL,
SQL Server.
● Préparer les bases pour l’administration des bases de données.

I. Introduction au Langage SQL

Définition

SQL (Structured Query Language) est un langage standard utilisé pour interagir avec les
bases de données relationnelles. Il permet de :

● Créer et modifier la structure des bases de données.

● Insérer, mettre à jour, supprimer et interroger les données.
● Gérer les droits d’accès et la sécurité.
● Contrôler les transactions et l’intégrité des données.

SQL est déclaratif, ce qui signifie qu’on décrit ce que l’on veut obtenir, sans spécifier
comment le système doit le faire.

II. Catégories de Commandes SQL

SQL est divisé en plusieurs sous-langages, chacun ayant un rôle spécifique :

Catégorie Nom Fonction principale

DDL Data Definition Language Définir la structure des bases

DML Data Manipulation Language Manipuler les données

DCL Data Control Language Gérer les droits d’accès

TCL Transaction Control Language Gérer les transactions

P a g e 5 | 21
Bases de Données et Sql MOUOMENE

DQL Data Query Language Interroger les données

III. DDL – Data Definition Language

 CREATE

Crée une nouvelle base ou une table.

sql
CREATE TABLE Clients (
ID_Client INT PRIMARY KEY,
Nom VARCHAR(100),
Adresse TEXT
);

 ALTER

Modifie la structure d’une table existante.

sql
ALTER TABLE Clients ADD Email VARCHAR(100);

 DROP

Supprime une table ou une base.

sql
DROP TABLE Clients;

IV. DML – Data Manipulation Language

 INSERT

Ajoute des données dans une table.

sql
INSERT INTO Clients VALUES (1, 'Nguea', 'Douala', 'nguea@[Link]');

 UPDATE

Modifie des données existantes.

P a g e 6 | 21
Bases de Données et Sql MOUOMENE

sql
UPDATE Clients SET Adresse = 'Yaoundé' WHERE ID_Client = 1;

 DELETE

Supprime des données.

sql
DELETE FROM Clients WHERE ID_Client = 2;

V. DQL – Data Query Language

 SELECT

Permet d’interroger les données.

sql
SELECT Nom, Adresse FROM Clients WHERE Ville = 'Douala';

 Clauses utiles :

● WHERE : filtre les résultats

● ORDER BY : trie les résultats
● GROUP BY : regroupe les données
● HAVING : filtre les groupes
● JOIN : combine plusieurs tables

 Exemple de jointure :
sql
SELECT [Link], [Link]
FROM Clients
JOIN Commandes ON Clients.ID_Client = Commandes.ID_Client;

VI. DCL – Data Control Language

 GRANT

Attribue des droits à un utilisateur.

sql
GRANT SELECT ON Clients TO Employe;

P a g e 7 | 21
Bases de Données et Sql MOUOMENE

 REVOKE

Retire des droits.

sql
REVOKE UPDATE ON Clients FROM Employe;

VII. TCL – Transaction Control Language

 BEGIN / COMMIT / ROLLBACK
sql
BEGIN;
UPDATE Stock SET Quantité = Quantité - 1 WHERE ID_Produit = 101;
COMMIT;

Si une erreur survient :

sql
ROLLBACK;

VIII. Fonctions SQL

● Fonctions d’agrégation : COUNT(), SUM(), AVG(), MAX(), MIN()
● Fonctions de chaîne : CONCAT(), SUBSTRING(), LENGTH()
● Fonctions de date : NOW(), DATE(), YEAR(), MONTH()

IX. Travaux Pratiques Recommandés

1. TP 1 : Création d’une base de données
○ Tables : Clients, Produits, Commandes
○ Relations : Clés primaires et étrangères
2. TP 2 : Requêtes SQL
○ Sélection simple et conditionnelle
○ Jointures internes et externes
○ Agrégats et regroupements
3. TP 3 : Administration
○ Création d’utilisateurs
○ Attribution de privilèges
○ Sauvegarde et restauration
4. TP 4 : Transactions
○ Simulation de commandes
○ Gestion des erreurs et annulations

P a g e 8 | 21
Bases de Données et Sql MOUOMENE

Chapitre3 :

Administration des Bases de Données

Objectifs du Chapitre
● Comprendre les rôles et responsabilités d’un administrateur de base de données
(DBA).
● Maîtriser les aspects techniques liés à l’implantation physique des données.
● Gérer les accès concurrents et assurer la résistance aux pannes.
● Mettre en œuvre des stratégies de sécurité, de sauvegarde et de restauration.
● Administrer des bases réparties et optimiser les performances du système.

I. Implantation Physique des Données

Définition

L’implantation physique désigne la manière dont les données sont stockées sur les supports
physiques (disques durs, SSD, etc.) par le SGBD.

Structure de fichiers

● Fichiers de données : contiennent les tables, index, vues…

● Fichiers journaux (log) : enregistrent les transactions pour la récupération.
● Fichiers temporaires : utilisés pour les opérations intermédiaires.

Index

Les index améliorent la rapidité d’accès aux données.

Types d’index :

● Index B-tree : structure équilibrée pour les recherches rapides.

● Index bitmap : efficace pour les colonnes à faible cardinalité.
● Index composite : basé sur plusieurs colonnes.

Exemple :

sql
CREATE INDEX idx_nom ON Clients(Nom);

P a g e 9 | 21
Bases de Données et Sql MOUOMENE

II. Contrôle des Accès Concurrents

Problématique

Lorsque plusieurs utilisateurs accèdent simultanément à la base, des conflits peuvent

survenir (lecture/écriture).

Solutions

● Verrouillage (locking) :
○ Verrouillage partagé (lecture)
○ Verrouillage exclusif (écriture)
● Isolation des transactions :
○ READ UNCOMMITTED
○ READ COMMITTED
○ REPEATABLE READ
○ SERIALIZABLE

Exemple

Deux utilisateurs modifient le stock d’un produit → verrouillage nécessaire pour éviter les
incohérences.

III. Résistance aux Pannes

Types de pannes

● Panne matérielle : disque dur, alimentation…

● Panne logicielle : bug du SGBD
● Panne humaine : suppression accidentelle

Mécanismes de protection

● Journalisation : enregistrement des transactions

● Points de contrôle (checkpoints) : sauvegarde intermédiaire
● Réplication : copie des données sur plusieurs serveurs

IV. Protection et Sécurité des Données

Objectifs

● Prévenir les accès non autorisés

● Garantir la confidentialité et l’intégrité

Moyens

P a g e 10 | 21
Bases de Données et Sql MOUOMENE

● Authentification : identifiants, mots de passe

● Autorisation : rôles et privilèges
● Chiffrement : cryptage des données sensibles
● Audit : traçabilité des actions

Exemple :

sql
GRANT SELECT ON Clients TO Employe;
REVOKE DELETE ON Clients FROM Employe;

V. Paramétrage, Démarrage, Arrêt, Sauvegarde,

Restauration
Paramétrage

● Configuration du serveur (ports, mémoire, cache)

● Définition des variables système

Démarrage / Arrêt

● Commandes pour lancer ou arrêter le service SGBD

● Surveillance des logs

Sauvegarde

● Sauvegarde complète : toutes les données

● Sauvegarde incrémentale : uniquement les modifications
● Sauvegarde logique : export SQL
● Sauvegarde physique : copie des fichiers

Restauration

● Récupération après panne

● Test de validité des sauvegardes

VI. Bases de Données Réparties et Traitement Réparti

Bases réparties

Les données sont stockées sur plusieurs sites géographiques.

Avantages :

● Disponibilité accrue

P a g e 11 | 21
Bases de Données et Sql MOUOMENE

● Réduction des temps d’accès

● Tolérance aux pannes

Traitement réparti

Les requêtes sont exécutées sur plusieurs serveurs.

Exemple : Un client interroge une base à Douala, les données sont réparties entre Yaoundé
et Bafoussam.

VII. Audit et Optimisation

Audit

● Analyse des accès

● Détection des anomalies
● Suivi des modifications

Optimisation

● Analyse des requêtes lentes

● Utilisation des index
● Réorganisation des tables
● Mise en cache des résultats

Outils :

● EXPLAIN (MySQL)
● SQL Profiler (SQL Server)
● AWR (Oracle)

Activités Pédagogiques Recommandées

1. TP 1 : Création d’index et analyse de performance
2. TP 2 : Simulation de pannes et restauration
3. TP 3 : Paramétrage d’un serveur MySQL
4. TP 4 : Mise en place d’un système de réplication
5. TP 5 : Audit des accès et des modifications

P a g e 12 | 21
Bases de Données et Sql MOUOMENE

Chapitre4 : Projet

🎯 Objectif

Concevoir une base de données relationnelle permettant de gérer les opérations courantes
d’une banque : gestion des clients, des comptes, des transactions, des prêts et des employés.
L’objectif est de garantir la cohérence, la sécurité et la traçabilité des données, tout en
facilitant les requêtes analytiques et opérationnelles.

Figure : Interface utilisateur de Fabien Bank : tableau de bord bancaire

Explication de l’image
L’image représente l’interface graphique d’une application bancaire moderne nommée
Fabien Bank, conçue pour faciliter la gestion des opérations courantes par les clients et les
employés. Elle est divisée en deux zones principales : une barre latérale sombre à gauche et
une zone de contenu claire à droite.

P a g e 13 | 21
Bases de Données et Sql MOUOMENE

Barre latérale (navigation)

 Logo Fabien Bank en haut, avec une icône stylisée et le nom en blanc.
 Menu vertical avec cinq sections :
o Tableau de bord : vue d’ensemble des opérations.
o Comptes : accès aux comptes bancaires du client.
o Transactions : historique des opérations (dépôts, retraits).
o Prêts : gestion des crédits contractés.
o Employés : suivi des agents bancaires.

Chaque élément est accompagné d’une icône intuitive (grille, portefeuille, carte, maison,
badge), facilitant la navigation.

Zone principale (contenu dynamique)

Salutation personnalisée

 Message d’accueil : “Salut, Jean Dup 👋”, renforçant l’aspect humain et

personnalisé de l’interface.

Informations de compte

 Affichage du numéro de compte (#3250987) et du solde actuel : $31,024.40, mis en

évidence en haut à droite.
 Lien “Voir tout” pour accéder à l’historique complet du compte.

Opérations bancaires

Deux encadrés côte à côte permettent d’effectuer des actions :

➕ Dépôt

 Icône flèche vers le bas.

 Champs :
o Montant (pré-rempli à 0,00)
o Compte (champ libre)
 Bouton bleu “Dépôt”

➖ Retrait

 Icône flèche vers le haut.

 Champs :
o Montant
o Compte
 Bouton bleu “Retrait”

Ces formulaires sont simples, épurés et conçus pour une saisie rapide.

P a g e 14 | 21
Bases de Données et Sql MOUOMENE

Section analytique : Solde Clients

 Titre : “Solde Clients”

 Menu déroulant : “Sélection…” pour filtrer les données.
 Tableau des transactions :
o Colonnes : ID transaction, Compte #, Type, Montant
o Types d’opérations : Dépôt, Retrait
o Montants affichés en gras pour plus de lisibilité

Design et ergonomie

 Palette de couleurs : gris foncé, blanc, bleu → moderne et professionnelle.

 Typographie claire, boutons bien espacés, icônes cohérentes.
 Interface responsive et intuitive, adaptée à une utilisation web ou mobile.

Solution
1. Modèle conceptuel (MCD)

Entités principales :

 Client : informations personnelles

 Compte : type, solde, date d’ouverture
 Transaction : montant, type, date
 Prêt : montant, taux, échéance
 Employé : gestion des clients et des prêts
 Agence : rattachement des comptes et employés

Relations clés :

 Un client peut avoir plusieurs comptes.

 Un compte peut avoir plusieurs transactions.
 Un client peut contracter plusieurs prêts.
 Un employé peut gérer plusieurs clients.
 Une agence regroupe plusieurs comptes et employés.

2. Modèle logique (tables SQL)

a. Table Client
sql
CREATE TABLE Client (
id_client INT PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
date_naissance DATE,
adresse VARCHAR(100),

P a g e 15 | 21
Bases de Données et Sql MOUOMENE

telephone VARCHAR(15),
email VARCHAR(100)
);

b. Table Compte
sql
CREATE TABLE Compte (
id_compte INT PRIMARY KEY,
type_compte VARCHAR(20),
solde DECIMAL(10,2),
date_ouverture DATE,
id_client INT,
id_agence INT,
FOREIGN KEY (id_client) REFERENCES Client(id_client),
FOREIGN KEY (id_agence) REFERENCES Agence(id_agence)
);

c. Table Transaction
sql
CREATE TABLE Transaction (
id_transaction INT PRIMARY KEY,
id_compte INT,
type_transaction VARCHAR(20),
montant DECIMAL(10,2),
date_transaction DATE,
FOREIGN KEY (id_compte) REFERENCES Compte(id_compte)
);

d. Table Pret
sql
CREATE TABLE Pret (
id_pret INT PRIMARY KEY,
montant DECIMAL(10,2),
taux FLOAT,
duree_mois INT,
id_client INT,
id_employe INT,
FOREIGN KEY (id_client) REFERENCES Client(id_client),
FOREIGN KEY (id_employe) REFERENCES Employe(id_employe)
);

e. Table Employe
sql
CREATE TABLE Employe (
id_employe INT PRIMARY KEY,
nom VARCHAR(50),
prenom VARCHAR(50),
poste VARCHAR(30),
id_agence INT,
FOREIGN KEY (id_agence) REFERENCES Agence(id_agence)
);

P a g e 16 | 21
Bases de Données et Sql MOUOMENE

f. Table Agence
sql
CREATE TABLE Agence (
id_agence INT PRIMARY KEY,
nom_agence VARCHAR(50),
adresse VARCHAR(100),
ville VARCHAR(50)
);

🔍 3. Requêtes SQL utiles

a. Liste des comptes d’un client

sql
SELECT nom, prenom, type_compte, solde
FROM Client
JOIN Compte ON Client.id_client = Compte.id_client
WHERE Client.id_client = 101;

b. Historique des transactions d’un compte

sql
SELECT type_transaction, montant, date_transaction
FROM Transaction
WHERE id_compte = 2001
ORDER BY date_transaction DESC;

c. Total des prêts par client

sql
SELECT [Link], [Link], SUM([Link]) AS total_pret
FROM Client
JOIN Pret ON Client.id_client = Pret.id_client
GROUP BY Client.id_client;

4. Sécurité et intégrité

 Utilisation de contraintes (PRIMARY KEY, FOREIGN KEY, CHECK).

 Gestion des droits d’accès via SQL GRANT/REVOKE.
 Sauvegarde régulière de la base.
 Journalisation des transactions sensibles.

5. Extensions possibles

 Ajout d’un module de gestion des cartes bancaires.

 Intégration d’un système de notifications par email/SMS.
 Création d’une interface web avec PHP/[Link] pour interagir avec la base.
 Analyse prédictive des comportements clients via SQL + Python.

P a g e 17 | 21
Bases de Données et Sql MOUOMENE

Requêtes SQL les plus couramment utilisées

dans une base de données bancaire

1. Requête : Liste des comptes d’un client

sql
SELECT c.id_compte, c.type_compte, [Link]
FROM Compte c
JOIN Client cl ON c.id_client = cl.id_client
WHERE cl.id_client = 101;

Explication : Cette requête affiche tous les comptes associés à un client donné (ici, client
n°101), avec le type de compte et le solde. Utile pour les conseillers bancaires ou les
interfaces client.

2. Requête : Historique des transactions d’un compte

sql
SELECT t.id_transaction, t.type_transaction, [Link], t.date_transaction
FROM Transaction t
WHERE t.id_compte = 2001
ORDER BY t.date_transaction DESC;

Explication : Affiche toutes les opérations (dépôts, retraits, virements) effectuées sur le
compte n°2001, triées par date décroissante. Indispensable pour les relevés bancaires.

3. Requête : Solde total par client

sql
SELECT cl.id_client, [Link], [Link], SUM([Link]) AS solde_total
FROM Client cl
JOIN Compte c ON cl.id_client = c.id_client
GROUP BY cl.id_client, [Link], [Link];

🔍 Explication : Calcule le solde cumulé de tous les comptes d’un client. Utile pour les
analyses de patrimoine ou les évaluations de solvabilité.

4. Requête : Total des prêts par client

sql
SELECT cl.id_client, [Link], [Link], SUM([Link]) AS total_pret
FROM Client cl
JOIN Pret p ON cl.id_client = p.id_client
GROUP BY cl.id_client, [Link], [Link];

P a g e 18 | 21
Bases de Données et Sql MOUOMENE

🔍 Explication : Permet de connaître le montant total emprunté par chaque client. Utile pour
le suivi du risque crédit.

5. Requête : Nombre de clients par agence

sql
SELECT a.nom_agence, COUNT(DISTINCT cl.id_client) AS nb_clients
FROM Agence a
JOIN Compte c ON a.id_agence = c.id_agence
JOIN Client cl ON c.id_client = cl.id_client
GROUP BY a.nom_agence;

Explication : Donne une vue du nombre de clients par agence, en se basant sur les comptes
ouverts. Utile pour le pilotage commercial.

6. Requête : Employés gérant des prêts

sql
SELECT [Link], [Link], COUNT(p.id_pret) AS nb_prets
FROM Employe e
JOIN Pret p ON e.id_employe = p.id_employe
GROUP BY e.id_employe, [Link], [Link];

Explication : Affiche le nombre de prêts gérés par chaque employé. Utile pour la répartition
des tâches ou les évaluations de performance.

7. Requête : Vérification de dépassement de solde

sql
SELECT id_compte, solde
FROM Compte
WHERE solde < 0;

🔍 Explication : Identifie les comptes en découvert. Crucial pour les alertes automatiques et
les politiques de gestion du risque.

8. Requête : Prêts arrivant à échéance ce mois-ci

sql
SELECT id_pret, montant, duree_mois, taux
FROM Pret
WHERE DATE_ADD(date_emission, INTERVAL duree_mois MONTH) BETWEEN CURDATE()
AND LAST_DAY(CURDATE());

🔍 Explication : Liste les prêts dont la date d’échéance tombe dans le mois en cours. Utile
pour les relances et les prévisions de remboursement.

P a g e 19 | 21
Bases de Données et Sql MOUOMENE

Création de vues SQL

a. Vue : Solde total par client
sql
CREATE VIEW vue_solde_total_client AS
SELECT cl.id_client, [Link], [Link], SUM([Link]) AS solde_total
FROM Client cl
JOIN Compte c ON cl.id_client = c.id_client
GROUP BY cl.id_client, [Link], [Link];

➡️ Utilisation :

sql
SELECT * FROM vue_solde_total_client WHERE solde_total > 10000;

b. Vue : Historique des transactions

sql
CREATE VIEW vue_transactions_compte AS
SELECT t.id_transaction, t.id_compte, t.type_transaction, [Link],
t.date_transaction
FROM Transaction t
ORDER BY t.date_transaction DESC;

➡️ Utilisation :

sql
SELECT * FROM vue_transactions_compte WHERE id_compte = 2001;

2. Procédures stockées

a. Procédure : Dépôt sur un compte

sql
DELIMITER //

CREATE PROCEDURE deposer_sur_compte(

IN compte_id INT,
IN montant DECIMAL(10,2)
)
BEGIN
UPDATE Compte
SET solde = solde + montant
WHERE id_compte = compte_id;

INSERT INTO Transaction(id_compte, type_transaction, montant,

date_transaction)
VALUES (compte_id, 'Dépôt', montant, CURDATE());
END //

DELIMITER ;

P a g e 20 | 21
Bases de Données et Sql MOUOMENE

➡️ Utilisation :

sql
CALL deposer_sur_compte(2001, 500.00);

b. Procédure : Retrait avec vérification de solde

sql
DELIMITER //

CREATE PROCEDURE retirer_du_compte(

IN compte_id INT,
IN montant DECIMAL(10,2)
)
BEGIN
DECLARE solde_actuel DECIMAL(10,2);

SELECT solde INTO solde_actuel FROM Compte WHERE id_compte = compte_id;

IF solde_actuel >= montant THEN

UPDATE Compte
SET solde = solde - montant
WHERE id_compte = compte_id;

INSERT INTO Transaction(id_compte, type_transaction, montant,

date_transaction)
VALUES (compte_id, 'Retrait', montant, CURDATE());
ELSE
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = 'Solde insuffisant pour effectuer le retrait.';
END IF;
END //

DELIMITER ;

➡️ Utilisation :

sql
CALL retirer_du_compte(2001, 300.00);

P a g e 21 | 21