Bases de données relationnelles

Une base de données (BDD) est une collection organisée et structurée d'informations, ou données, stockées électroniquement dans un système informatique. Imaginez-la comme un classeur numérique géant où chaque document est soigneusement rangé, indexé et facilement accessible.

Son rôle et utilité sont multiples :

• Stocker de grandes quantités de données de manière persistante.
• Organiser ces données pour les rendre cohérentes et faciles à retrouver.
• Gérer les accès, permettant à plusieurs utilisateurs de consulter ou modifier les données simultanément sans conflit.
• Sécuriser les informations contre la perte ou l'accès non autorisé.
• Faciliter la recherche, la mise à jour et la suppression des données.

• Les systèmes bancaires pour gérer les comptes clients et les transactions.
• Les sites de commerce en ligne (Amazon, Fnac) pour les catalogues de produits, les commandes et les profils utilisateurs.
• Les réseaux sociaux (Facebook, Instagram) pour les profils, messages et publications.
• Les bibliothèques pour cataloguer les livres et gérer les emprunts.
• Les hôpitaux pour les dossiers médicaux des patients.

Une base de données est le cœur de presque toutes les applications informatiques modernes.

Il existe plusieurs manières d'organiser et de stocker les données. Les deux catégories principales sont les bases de données relationnelles et les bases de données NoSQL.

Bases de données relationnelles

Les bases de données relationnelles sont le type le plus répandu. Elles sont basées sur le modèle relationnel, où les données sont organisées en tables, avec des lignes (enregistrements) et des colonnes (attributs). Ces tables sont ensuite reliées entre elles par des relations prédéfinies. Le langage SQL (Structured Query Language) est utilisé pour interagir avec elles.

• Avantages : Forte cohérence des données, intégrité garantie, langage SQL standardisé et puissant.
• Inconvénients : Moins flexibles pour des données non structurées, peuvent être plus difficiles à faire évoluer horizontalement (scaler).

Bases de données NoSQL

Le terme NoSQL (Not Only SQL) regroupe une famille de bases de données qui ne suivent pas le modèle relationnel traditionnel. Elles sont conçues pour gérer de grands volumes de données non structurées ou semi-structurées, et pour offrir une grande flexibilité et évolutivité.

Il existe plusieurs types de bases de données NoSQL :

• Orientées document : Les données sont stockées dans des documents (souvent au format JSON), comme MongoDB.
• Clé-Valeur : Chaque élément de donnée est stocké sous la forme d'une clé unique et de sa valeur associée, comme Redis.
• Orientées colonnes : Les données sont stockées par colonnes plutôt que par lignes, comme Cassandra.
• Orientées graphes : Les données sont représentées comme des nœuds et des relations entre eux, comme Neo4j.

Comparaison sommaire

Les bases de données relationnelles sont idéales pour les applications nécessitant une forte intégrité des données, tandis que les bases de données NoSQL sont préférées pour les données massives et les applications nécessitant une grande flexibilité et évolutivité.

Le modèle relationnel est la base théorique des bases de données relationnelles. Il a été introduit en 1970 par Edgar F. Codd, un chercheur chez IBM. Son objectif était de fournir un modèle mathématiquement fondé pour la gestion des données, rendant les bases de données indépendantes de la manière dont les données sont physiquement stockées.

Principes fondamentaux

Les principes clés du modèle relationnel sont :

1. Relation (Table) : Les données sont organisées en relations, qui sont représentées visuellement comme des tables. Chaque table a un nom unique.
2. Attribut (Colonne) : Chaque table est composée d'attributs, qui sont les colonnes de la table. Chaque attribut a un nom unique au sein de la table et un type de données (par exemple, entier, texte, date).
3. Tuple (Ligne/Enregistrement) : Chaque ligne d'une table représente un enregistrement ou un objet unique. C'est une instance de la relation.
4. Clé primaire (Primary Key - PK) : Un attribut ou un ensemble d'attributs qui identifie de manière unique chaque tuple (ligne) dans une relation. La clé primaire ne peut pas contenir de valeurs nulles et doit être unique pour chaque enregistrement.
5. Clé étrangère (Foreign Key - FK) : Un attribut ou un ensemble d'attributs dans une table qui fait référence à la clé primaire d'une autre table. Les clés étrangères établissent des liens (relations) entre les tables.
6. Domaine : L'ensemble de toutes les valeurs possibles qu'un attribut peut prendre. Par exemple, le domaine de l'âge pourrait être les entiers positifs.
7. Intégrité des données : Le modèle relationnel met l'accent sur la préservation de la cohérence et de la validité des données à travers des règles d'intégrité (clé primaire, clé étrangère, non-null, unique).

Avantages du modèle relationnel

• Simplicité : Le modèle est conceptuellement simple à comprendre et à utiliser pour représenter des données structurées.
• Indépendance des données : La structure logique des données est séparée de leur stockage physique, ce qui permet de modifier l'un sans affecter l'autre.
• Intégrité des données : Grâce aux clés primaires et étrangères, le modèle assure une forte cohérence et prévient les erreurs de données.
• Flexibilité des requêtes : Le langage SQL permet d'interroger les données de manière très flexible et puissante.
• Standardisation : SQL est un langage standardisé, ce qui facilite l'apprentissage et la portabilité des compétences.
• Robustesse : Le modèle a fait ses preuves et est très fiable pour les applications critiques.

Le modèle Entité-Association (EA), également appelé Modèle Conceptuel de Données (MCD) en France, est une méthode de conception de bases de données. Il permet de représenter de manière abstraite et graphique les données et leurs relations, indépendamment de toute considération technique ou de l'implémentation physique. C'est une étape cruciale pour bien comprendre et structurer les informations avant de créer la base de données.

Entités et attributs

• Une entité est un objet ou un concept du monde réel qui peut être clairement identifié et auquel on s'intéresse pour le stocker dans la base de données. C'est généralement un nom commun (personne, produit, commande, livre).
  • Dans un diagramme EA, une entité est représentée par un rectangle.
• Un attribut est une propriété ou une caractéristique qui décrit une entité. Par exemple, pour l'entité "Client", les attributs pourraient être "Nom", "Prénom", "Adresse", "Date de naissance".
  • Chaque attribut a un nom et un type de données.
  • Un attribut particulier, appelé identifiant (ou clé primaire conceptuelle), permet d'identifier de manière unique chaque occurrence d'une entité. Il est souvent souligné dans le diagramme.
  • Dans un diagramme EA, les attributs sont souvent listés sous le nom de l'entité.

Associations et cardinalités

• Une association est un lien ou une relation entre deux ou plusieurs entités. Elle décrit comment les entités interagissent ou sont connectées. Par exemple, un "Client" peut "Passer" une "Commande".
  • Une association est représentée par un losange dans un diagramme EA.
• Les cardinalités définissent le nombre minimum et maximum de fois où une occurrence d'une entité peut être liée à une occurrence de l'autre entité via l'association. Elles sont exprimées sous la forme (min, max).
  • 0,1 : Zéro ou une fois (optionnel, unique)
  • 1,1 : Exactement une fois (obligatoire, unique)
  • 0,N : Zéro ou plusieurs fois (optionnel, multiple)
  • 1,N : Une ou plusieurs fois (obligatoire, multiple)
  • Exemple : "Un Client (1,N) Passe (0,N) des Commandes." Cela signifie qu'un client doit passer au moins une commande (1,N côté Client) et qu'une commande est passée par un seul client (1,1 côté Commande).

Exemples de diagrammes EA

+------------+        +---------------+        +----------+
|   Client   |        |   Passer      |        |  Commande|
+------------+        +---------------+        +----------+
| #idClient  |<----(1,N)----(0,N)---->| #idCommande|
| Nom        |        | Date_commande |        | Date     |
| Prénom     |        +---------------+        | Montant  |
| Adresse    |                                 +----------+
+------------+

Dans cet exemple :

• Client et Commande sont des entités.
• idClient, Nom, Prénom, Adresse sont des attributs de Client. idClient est l'identifiant.
• idCommande, Date, Montant sont des attributs de Commande. idCommande est l'identifiant.
• Passer est une association entre Client et Commande.
• Les cardinalités indiquent qu'un client peut passer plusieurs commandes (1,N côté Client vers Passer) et qu'une commande est passée par un seul client (1,1 côté Commande vers Passer).

Une fois le modèle EA validé, l'étape suivante consiste à le transformer en un modèle relationnel. Ce processus, appelé mapping relationnel, consiste à traduire les entités et associations en tables, clés primaires et clés étrangères.

Règles de transformation

1. Chaque entité devient une table :

   • Le nom de l'entité devient le nom de la table.
   • Les attributs de l'entité deviennent les colonnes de la table.
   • L'identifiant de l'entité devient la clé primaire (PK) de la table.

2. Transformation des associations :

   • Association 1-1 : La clé primaire de l'une des tables est ajoutée comme clé étrangère dans l'autre table (souvent celle avec la cardinalité (0,1)).
   • Association 1-N : La clé primaire de l'entité côté "1" est ajoutée comme clé étrangère (FK) dans la table de l'entité côté "N". C'est la règle la plus courante.
   • Association N-N : Une nouvelle table est créée pour représenter l'association. Cette nouvelle table aura comme clé primaire composite les clés primaires des deux entités qu'elle relie, qui seront aussi ses clés étrangères. Les attributs propres à l'association (s'il y en a) deviennent des colonnes de cette nouvelle table.

Tables, clés primaires et étrangères

Reprenons l'exemple Client-Commande (association 1-N) :

• Entité Client → Table CLIENT

  • idClient (PK)
  • Nom
  • Prénom
  • Adresse

• Entité Commande → Table COMMANDE

  • idCommande (PK)
  • Date
  • Montant
  • idClient (FK, fait référence à CLIENT.idClient)

Exemple avec une association N-N (Étudiant - Cours, via l'association "Inscrit") :

• Entité Étudiant → Table ETUDIANT (idEtudiant PK, Nom, Prénom)
• Entité Cours → Table COURS (idCours PK, Titre, Crédits)
• Association Inscrit (N-N) avec un attribut DateInscription → Table INSCRIPTION
  • idEtudiant (PK, FK vers ETUDIANT)
  • idCours (PK, FK vers COURS)
  • DateInscription (attribut de l'association)
  • La clé primaire de INSCRIPTION est (idEtudiant, idCours).

Intégrité référentielle

L'intégrité référentielle est une règle fondamentale du modèle relationnel qui assure que les relations entre les tables restent valides. Elle stipule que chaque valeur de clé étrangère doit correspondre à une valeur existante de la clé primaire à laquelle elle fait référence, ou être NULL (si l'attribut FK n'est pas obligatoire).

• Exemple : Si idClient est une clé étrangère dans la table COMMANDE faisant référence à CLIENT.idClient, alors il est impossible d'insérer une commande avec un idClient qui n'existe pas dans la table CLIENT.
• Elle empêche également de supprimer un client si des commandes lui sont associées, ou de modifier l'idClient d'un client s'il y a des commandes qui y font référence (sauf si des actions en cascade sont définies).
• Cette intégrité est gérée par le SGBD et est cruciale pour la cohérence des données.

La normalisation est un processus de conception de bases de données relationnelles qui vise à organiser les colonnes et les tables pour minimiser la redondance des données et améliorer l'intégrité des données. Elle se base sur un ensemble de règles appelées formes normales (FN).

Anomalies de mise à jour

Sans normalisation, une base de données peut souffrir de plusieurs types d'anomalies :

• Anomalie d'insertion : Impossible d'ajouter certaines informations sans en ajouter d'autres. Ex: ajouter un cours sans aucun étudiant inscrit.
• Anomalie de suppression : La suppression d'un enregistrement entraîne la perte involontaire d'autres informations. Ex: supprimer le dernier étudiant inscrit à un cours efface toutes les informations sur le cours.
• Anomalie de modification : La modification d'une information nécessite de la mettre à jour à plusieurs endroits, ce qui peut entraîner des incohérences si une occurrence est oubliée. Ex: modifier le titre d'un cours qui apparaît dans plusieurs enregistrements étudiants.

La normalisation vise à éliminer ces anomalies.

Formes normales (1FN, 2FN, 3FN)

Les formes normales sont des niveaux de structuration des tables. Plus la forme normale est élevée, plus la base de données est "normalisée". Les plus courantes sont 1FN, 2FN et 3FN.

• Première Forme Normale (1FN) :

  • Chaque attribut doit contenir des valeurs atomiques (indivisibles). Pas de listes ou de groupes répétitifs dans une seule colonne.
  • Chaque colonne doit avoir un nom unique.
  • L'ordre des lignes et des colonnes n'a pas d'importance.
  • Exemple : Au lieu d'avoir une colonne "Numéros de téléphone" avec "0123456789, 0987654321", on aura deux lignes distinctes ou une table séparée pour les numéros.

• Deuxième Forme Normale (2FN) :

  • La table doit être en 1FN.
  • Tous les attributs non clés (non inclus dans la clé primaire) doivent dépendre entièrement de la clé primaire complète.
  • Ceci est pertinent pour les tables avec des clés primaires composites (composées de plusieurs attributs). Si un attribut non clé dépend seulement d'une partie de la clé primaire, il y a une violation de la 2FN.

• Troisième Forme Normale (3FN) :

  • La table doit être en 2FN.
  • Il ne doit pas y avoir de dépendances transitives entre les attributs non clés. Autrement dit, un attribut non clé ne doit pas dépendre d'un autre attribut non clé.
  • Exemple : Si une table COMMANDE a idCommande (PK), idClient, NomClient, AdresseClient, alors NomClient et AdresseClient dépendent de idClient, qui lui-même dépend de idCommande. Pour la 3FN, NomClient et AdresseClient devraient être dans une table CLIENT séparée.

Objectifs de la normalisation

• Réduire la redondance des données : Éviter de stocker la même information plusieurs fois, ce qui économise de l'espace de stockage.
• Améliorer l'intégrité des données : Minimiser les risques d'incohérences et d'anomalies lors des insertions, modifications et suppressions.
• Faciliter la maintenance : Les mises à jour sont plus simples et moins sujettes aux erreurs car les données ne sont stockées qu'une seule fois.
• Optimiser les requêtes (parfois) : Bien que la normalisation puisse entraîner plus de jointures, elle peut aussi rendre les requêtes plus efficaces en réduisant la taille des tables et en améliorant la gestion des index.
• Clarté du modèle : Un modèle normalisé est généralement plus clair et plus facile à comprendre.

Il est important de noter que la normalisation jusqu'à la 3FN est souvent considérée comme un bon compromis pour la plupart des applications. Aller au-delà (4FN, 5FN) est rarement nécessaire et peut même introduire des complexités ou des pénalités de performance pour certaines applications.

L'instruction SELECT est l'une des commandes SQL les plus utilisées. Elle permet de récupérer des données à partir d'une ou plusieurs tables.

SELECT colonne1, colonne2, ...
FROM nom_de_la_table
WHERE condition
ORDER BY colonne DESC/ASC
GROUP BY colonne
HAVING condition_de_groupe;

Clause FROM et WHERE

• FROM nom_de_la_table : Indique la ou les tables d'où proviennent les données.

  SELECT Nom, Prenom FROM Clients;
  

• WHERE condition : Permet de filtrer les lignes qui satisfont une condition spécifique.

  SELECT * FROM Produits WHERE Prix > 50;
  

  * signifie "toutes les colonnes".

Opérateurs logiques et de comparaison

• Opérateurs de comparaison : =, != (ou <>), >, <, >=, <=

  SELECT Nom, Age FROM Employes WHERE Age >= 30;
  

• Opérateurs logiques : AND, OR, NOT

  SELECT * FROM Commandes WHERE Montant > 100 AND DateCommande < '2023-01-01';
  SELECT * FROM Clients WHERE NOT Ville = 'Paris';
  

• Autres opérateurs :

  • BETWEEN valeur1 AND valeur2 : Pour une plage de valeurs.
  • IN (valeur1, valeur2, ...) : Pour une liste de valeurs.
  • LIKE 'motif' : Pour la recherche de motifs (avec % pour zéro ou plusieurs caractères, _ pour un seul caractère).
  • IS NULL / IS NOT NULL : Pour vérifier si une valeur est nulle.

  SELECT * FROM Produits WHERE Prix BETWEEN 20 AND 50;
  SELECT * FROM Clients WHERE Ville IN ('Paris', 'Lyon', 'Marseille');
  SELECT * FROM Articles WHERE NomArticle LIKE 'D%'; -- Commence par 'D'
  SELECT * FROM Clients WHERE Email IS NULL;
  

Tri (ORDER BY) et regroupement (GROUP BY)

• ORDER BY colonne DESC/ASC : Trie les résultats. ASC pour ascendant (par défaut), DESC pour descendant.

  SELECT Nom, Prix FROM Produits ORDER BY Prix DESC; -- Produits du plus cher au moins cher
  

• GROUP BY colonne : Regroupe les lignes ayant les mêmes valeurs dans une ou plusieurs colonnes. Souvent utilisé avec des fonctions d'agrégation.

  SELECT Ville, COUNT(*) AS NombreClients FROM Clients GROUP BY Ville;
  

  Ceci compte le nombre de clients par ville. COUNT(*) est une fonction d'agrégation.

Les jointures (JOIN) sont utilisées pour combiner des lignes de deux ou plusieurs tables basées sur une colonne liée entre elles. C'est le cœur du modèle relationnel.

Jointure interne (INNER JOIN)

La jointure interne (INNER JOIN) est la plus courante. Elle retourne toutes les lignes lorsque la condition de jointure est vraie, c'est-à-dire uniquement les lignes qui ont une correspondance dans les deux tables.

SELECT C.Nom, C.Prenom, Co.DateCommande, Co.Montant
FROM Clients AS C
INNER JOIN Commandes AS Co ON C.idClient = Co.idClient;

Ici, AS C et AS Co sont des alias de tables, rendant la requête plus lisible. La jointure est faite sur idClient qui est la clé étrangère dans Commandes et la clé primaire dans Clients.

Jointures externes (LEFT/RIGHT JOIN)

• LEFT JOIN (ou LEFT OUTER JOIN) : Retourne toutes les lignes de la table de gauche (première table dans le FROM), et les lignes correspondantes de la table de droite. S'il n'y a pas de correspondance à droite, des valeurs NULL sont retournées pour les colonnes de la table de droite.

  SELECT C.Nom, Co.idCommande
  FROM Clients AS C
  LEFT JOIN Commandes AS Co ON C.idClient = Co.idClient;
  

  Ceci listerait tous les clients, y compris ceux qui n'ont pas passé de commande (leurs idCommande seraient NULL).

• RIGHT JOIN (ou RIGHT OUTER JOIN) : Retourne toutes les lignes de la table de droite, et les lignes correspondantes de la table de gauche. S'il n'y a pas de correspondance à gauche, des valeurs NULL sont retournées pour les colonnes de la table de gauche. Moins courant que LEFT JOIN car on peut souvent l'inverser en LEFT JOIN.

Alias de tables

Les alias de tables (AS) sont des noms temporaires donnés aux tables dans une requête. Ils sont très utiles pour :

• Rendre les requêtes plus courtes et plus lisibles.
• Différencier les colonnes lorsque plusieurs tables ont des noms de colonnes identiques.
• Permettre des auto-jointures (joindre une table avec elle-même).

SELECT E.Nom AS NomEmploye, M.Nom AS NomManager
FROM Employes AS E
JOIN Employes AS M ON E.idManager = M.idEmploye;

Ces instructions SQL permettent de modifier le contenu des tables.

Instruction INSERT INTO

Utilisée pour ajouter de nouvelles lignes (enregistrements) dans une table.

-- Insérer toutes les colonnes (l'ordre doit correspondre à celui de la table)
INSERT INTO Clients VALUES (1, 'Dupont', 'Jean', 'Paris');

-- Insérer des colonnes spécifiques (recommandé)
INSERT INTO Produits (NomProduit, Prix, QuantiteStock)
VALUES ('Ordinateur Portable', 1200.00, 50);

Instruction UPDATE

Utilisée pour modifier des données existantes dans une ou plusieurs lignes d'une table. La clause WHERE est cruciale pour cibler les lignes à modifier ; sans elle, TOUTES les lignes de la table seraient mises à jour.

UPDATE Produits
SET Prix = 1250.00
WHERE NomProduit = 'Ordinateur Portable';

UPDATE Clients
SET Adresse = 'Lyon', Ville = 'Lyon'
WHERE idClient = 2;

Instruction DELETE FROM

Utilisée pour supprimer des lignes existantes d'une table. Comme avec UPDATE, la clause WHERE est essentielle. Sans WHERE, TOUTES les lignes de la table seraient supprimées.

DELETE FROM Commandes
WHERE idCommande = 101;

-- Supprimer tous les produits dont le stock est à zéro
DELETE FROM Produits
WHERE QuantiteStock = 0;

Attention : DELETE FROM nom_de_la_table; sans WHERE vide toute la table.

Les fonctions d'agrégation effectuent un calcul sur un ensemble de lignes et retournent une seule valeur. Elles sont souvent utilisées avec GROUP BY.

• COUNT() : Compte le nombre de lignes.

  • COUNT(*) : Compte toutes les lignes, y compris celles avec des valeurs NULL.
  • COUNT(colonne) : Compte les lignes où colonne n'est pas NULL.
  • COUNT(DISTINCT colonne) : Compte le nombre de valeurs uniques dans colonne.

  SELECT COUNT(*) FROM Clients; -- Nombre total de clients
  SELECT COUNT(DISTINCT Ville) FROM Clients; -- Nombre de villes uniques
  

• SUM(colonne) : Calcule la somme des valeurs d'une colonne numérique.

  SELECT SUM(Montant) FROM Commandes; -- Montant total de toutes les commandes
  

• AVG(colonne) : Calcule la moyenne des valeurs d'une colonne numérique.

  SELECT AVG(Prix) FROM Produits; -- Prix moyen des produits
  

• MIN(colonne) : Retourne la valeur minimale d'une colonne.

  SELECT MIN(DateCommande) FROM Commandes; -- Date de la première commande
  

• MAX(colonne) : Retourne la valeur maximale d'une colonne.

  SELECT MAX(Montant) FROM Commandes; -- Montant de la commande la plus élevée
  

Utilisation avec GROUP BY

Les fonctions d'agrégation sont très puissantes lorsqu'elles sont combinées avec la clause GROUP BY. GROUP BY divise le jeu de résultats en groupes, et la fonction d'agrégation est appliquée à chaque groupe indépendamment.

SELECT idClient, COUNT(*) AS NombreCommandes, SUM(Montant) AS TotalDepense
FROM Commandes
GROUP BY idClient;
-- Cela donne le nombre de commandes et la dépense totale pour chaque client.

Clause HAVING

La clause HAVING est similaire à WHERE, mais elle est utilisée pour filtrer les groupes créés par GROUP BY, après que les fonctions d'agrégation ont été appliquées. WHERE filtre les lignes avant le regroupement, tandis que HAVING filtre les groupes après.

SELECT idClient, COUNT(*) AS NombreCommandes
FROM Commandes
GROUP BY idClient
HAVING COUNT(*) > 2;
-- Sélectionne uniquement les clients qui ont passé plus de 2 commandes.

Instruction CREATE TABLE

L'instruction CREATE TABLE est utilisée pour créer une nouvelle table dans la base de données. Il faut spécifier le nom de la table, ainsi que les noms et types de données de chaque colonne.

CREATE TABLE Clients (
    idClient INT PRIMARY KEY AUTO_INCREMENT, -- Clé primaire auto-incrémentée
    Nom VARCHAR(50) NOT NULL,
    Prenom VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE, -- Chaque email doit être unique
    DateInscription DATE DEFAULT CURRENT_DATE, -- Valeur par défaut
    CONSTRAINT CHK_Age CHECK (Age >= 18) -- Contrainte de vérification
);

Types de données SQL

Le choix du type de données est crucial pour l'optimisation du stockage et la validité des données. Voici quelques types courants :

• Numériques : INT (entier), BIGINT, SMALLINT, DECIMAL(p,s) (nombre avec précision et échelle), FLOAT, DOUBLE.
• Chaînes de caractères : VARCHAR(taille) (taille variable), CHAR(taille) (taille fixe), TEXT (grand texte).
• Dates et heures : DATE (date seule), TIME (heure seule), DATETIME ou TIMESTAMP (date et heure).
• Booléens : BOOLEAN ou TINYINT(1).

Contraintes (PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE)

Les contraintes sont des règles appliquées aux données dans une table pour maintenir l'intégrité et la cohérence.

• PRIMARY KEY : Identifie de manière unique chaque ligne d'une table.
  • Une seule clé primaire par table.
  • Les valeurs doivent être uniques et non nulles.
  • Peut être auto-incrémentée (AUTO_INCREMENT en MySQL, SERIAL en PostgreSQL).
• FOREIGN KEY : Établit une relation entre deux tables. Elle référence la clé primaire d'une autre table.

  CREATE TABLE Commandes (
      idCommande INT PRIMARY KEY AUTO_INCREMENT,
      idClient INT,
      DateCommande DATE,
      Montant DECIMAL(10, 2),
      FOREIGN KEY (idClient) REFERENCES Clients(idClient)
      -- ON DELETE CASCADE : Supprime les commandes si le client est supprimé
      -- ON UPDATE CASCADE : Met à jour l'idClient dans les commandes si l'idClient du client change
  );
  

• NOT NULL : Assure qu'une colonne ne peut pas contenir de valeurs NULL.
• UNIQUE : Assure que toutes les valeurs dans une colonne sont différentes. Une table peut avoir plusieurs contraintes UNIQUE.
• DEFAULT valeur : Spécifie une valeur par défaut pour une colonne si aucune valeur n'est fournie lors de l'insertion.
• CHECK (condition) : Permet de définir une condition que les données de la colonne doivent satisfaire.

Instruction ALTER TABLE

L'instruction ALTER TABLE est utilisée pour modifier la structure d'une table existante.

-- Ajouter une nouvelle colonne
ALTER TABLE Clients
ADD COLUMN Telephone VARCHAR(15);

-- Modifier le type de données d'une colonne
ALTER TABLE Produits
MODIFY COLUMN Prix DECIMAL(12,2); -- Ou ALTER COLUMN en PostgreSQL

-- Supprimer une colonne
ALTER TABLE Clients
DROP COLUMN Telephone;

-- Ajouter une contrainte (par exemple, une clé étrangère après la création de la table)
ALTER TABLE Commandes
ADD CONSTRAINT FK_ClientCommande
FOREIGN KEY (idClient) REFERENCES Clients(idClient);

Instruction DROP TABLE

L'instruction DROP TABLE est utilisée pour supprimer une table existante de la base de données, y compris toutes ses données, ses index et ses contraintes. C'est une opération irréversible.

DROP TABLE AncienneTable;

Attention : Si la table à supprimer est référencée par une clé étrangère dans une autre table, l'opération peut échouer sauf si la contrainte de clé étrangère est définie avec ON DELETE CASCADE ou si elle est d'abord supprimée.

Impact sur les données

Toute modification de la structure d'une table a un impact potentiel sur les données existantes :

• Ajouter une colonne : Les lignes existantes auront NULL (ou la valeur par défaut si spécifiée) pour la nouvelle colonne.
• Modifier le type d'une colonne : Peut entraîner une perte de données si le nouveau type ne peut pas contenir les anciennes valeurs (ex: passer de VARCHAR à INT).
• Supprimer une colonne : Les données de cette colonne sont définitivement perdues.
• Supprimer une table : Toutes les données de la table sont perdues. Il est crucial de toujours faire des sauvegardes avant d'effectuer des opérations DDL importantes sur une base de données en production.

Le DCL (Data Control Language) concerne la gestion des permissions et des droits d'accès aux données de la base.

Instruction GRANT

L'instruction GRANT est utilisée pour accorder des privilèges (permissions) à un utilisateur ou à un rôle sur des objets de la base de données (tables, vues, etc.).

-- Accorder le droit de sélectionner sur la table Clients à l'utilisateur 'UtilisateurLecteur'
GRANT SELECT ON Clients TO 'UtilisateurLecteur'@'localhost';

-- Accorder tous les droits sur la table Produits à l'utilisateur 'UtilisateurAdmin'
GRANT ALL PRIVILEGES ON Produits TO 'UtilisateurAdmin'@'localhost';

-- Accorder des droits d'insertion et de mise à jour sur toutes les tables
GRANT INSERT, UPDATE ON ma_base_de_donnees.* TO 'UtilisateurApp'@'%'; -- '%' pour tous les hôtes

Les privilèges courants incluent SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, etc.

Instruction REVOKE

L'instruction REVOKE est utilisée pour révoquer des privilèges qui ont été précédemment accordés.

-- Révoquer le droit de suppression sur la table Commandes à l'utilisateur 'UtilisateurApp'
REVOKE DELETE ON Commandes FROM 'UtilisateurApp'@'%';

-- Révoquer tous les privilèges sur la table Clients pour 'UtilisateurLecteur'
REVOKE ALL PRIVILEGES ON Clients FROM 'UtilisateurLecteur'@'localhost';

Principes de sécurité

La gestion des droits est un aspect fondamental de la sécurité des bases de données :

• Principe du moindre privilège : Chaque utilisateur ou application ne devrait avoir que les droits strictement nécessaires à l'exécution de ses tâches. Cela minimise les risques en cas de compromission.
• Séparation des rôles : Différents rôles (administrateur, développeur, utilisateur final) devraient avoir des jeux de permissions distincts.
• Authentification forte : Utiliser des mots de passe complexes et, si possible, l'authentification multi-facteurs.
• Audit : Surveiller les accès et les modifications des données pour détecter d'éventuelles activités suspectes.
• Chiffrement : Chiffrer les données sensibles, à la fois au repos (dans la base) et en transit (lors de la communication).

Un Système de Gestion de Bases de Données (SGBD) est un logiciel qui permet de créer, gérer et interagir avec des bases de données. Il sert d'interface entre l'utilisateur (ou l'application) et les données stockées.

Rôle d'un SGBD

Le SGBD a plusieurs fonctions essentielles :

• Définition des données (DDL) : Permet de créer, modifier et supprimer la structure des bases de données (tables, index, vues, etc.).
• Manipulation des données (DML) : Gère l'insertion, la mise à jour, la suppression et la récupération des données.
• Contrôle des données (DCL) : Gère les droits d'accès et la sécurité des données.
• Gestion de l'intégrité : Applique les contraintes définies (clés primaires, étrangères, NOT NULL, UNIQUE, CHECK) pour maintenir la cohérence des données.
• Gestion de la concurrence : Permet à plusieurs utilisateurs d'accéder et de modifier les données simultanément sans provoquer d'incohérences (verrouillage, transactions).
• Récupération et sauvegarde : Assure la persistance des données et la capacité à restaurer la base de données après une panne.
• Optimisation des requêtes : Analyse les requêtes SQL et détermine la manière la plus efficace de les exécuter.

Exemples de SGBD (MySQL, PostgreSQL, SQLite)

• MySQL : L'un des SGBD open source les plus populaires au monde. Très utilisé pour les applications web. Il est réputé pour sa simplicité d'utilisation, sa performance et sa robustesse.
• PostgreSQL : Un SGBD open source réputé pour sa conformité aux standards SQL, sa fiabilité, sa robustesse et ses fonctionnalités avancées (support de types de données complexes, fonctions géospatiales, etc.). Souvent considéré comme plus puissant et riche en fonctionnalités que MySQL pour les applications critiques.
• SQLite : Un SGBD léger, sans serveur, qui stocke la base de données dans un simple fichier sur le disque. Idéal pour les applications embarquées, les applications mobiles, les tests ou les petits projets où une base de données complète n'est pas nécessaire. Il ne gère pas la concurrence de manière aussi robuste que MySQL ou PostgreSQL.

Interface utilisateur et outils

Les SGBD sont souvent accompagnés d'outils graphiques ou en ligne de commande pour faciliter leur administration et leur utilisation :

• MySQL Workbench, phpMyAdmin (pour MySQL)
• pgAdmin (pour PostgreSQL)
• DB Browser for SQLite (pour SQLite) Ces outils permettent de visualiser les schémas, exécuter des requêtes, importer/exporter des données, gérer les utilisateurs, etc.

Pour qu'une application puisse utiliser une base de données, elle doit établir une connexion avec le SGBD.

Client/Serveur

La plupart des SGBD fonctionnent sur un modèle client/serveur :

• Le serveur de base de données (le SGBD lui-même) s'exécute sur une machine et écoute les requêtes.
• Le client (votre application, un outil d'administration) se connecte au serveur, envoie des requêtes SQL et reçoit les résultats.

SQLite est une exception notable car il est embarqué : le "serveur" est intégré directement à l'application cliente, et la base de données est un fichier local.

Paramètres de connexion

Pour se connecter, le client a besoin de plusieurs informations :

• Hôte (Host) : L'adresse IP ou le nom de l'ordinateur où le SGBD est en cours d'exécution (souvent localhost si c'est sur la même machine).
• Port : Le numéro de port sur lequel le SGBD écoute (ex: 3306 pour MySQL, 5432 pour PostgreSQL).
• Nom de la base de données (Database Name) : Le nom de la base de données spécifique à laquelle se connecter.
• Nom d'utilisateur (Username) : Le compte utilisateur pour se connecter.
• Mot de passe (Password) : Le mot de passe associé à l'utilisateur.

Exemple de connexion (Python/SQLite)

Voici un exemple simple de connexion à une base de données SQLite en Python :

import sqlite3

# Connexion à la base de données (crée le fichier si n'existe pas)
conn = sqlite3.connect('ma_base_de_donnees.db')

# Création d'un curseur pour exécuter des requêtes SQL
cursor = conn.cursor()

# Exécution d'une requête DDL (création de table)
cursor.execute('''
    CREATE TABLE IF NOT EXISTS articles (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        nom TEXT NOT NULL,
        prix REAL
    )
''')

# Exécution d'une requête DML (insertion de données)
cursor.execute("INSERT INTO articles (nom, prix) VALUES (?, ?)", ('Livre', 19.99))
cursor.execute("INSERT INTO articles (nom, prix) VALUES (?, ?)", ('Stylo', 2.50))

# Sauvegarder les changements (commit)
conn.commit()

# Exécution d'une requête DML (sélection de données)
cursor.execute("SELECT * FROM articles WHERE prix > ?", (10,))
for row in cursor.fetchall():
    print(row)

# Fermeture de la connexion
conn.close()

Cet exemple montre les étapes typiques : connexion, création d'un curseur, exécution de requêtes, validation des changements (commit) et fermeture de la connexion.

Indexation

Un index est une structure de données spéciale qui améliore la vitesse de récupération des données sur une table. Il fonctionne un peu comme l'index d'un livre : au lieu de parcourir toutes les pages (lignes), on peut directement aller à la page (ligne) pertinente.

• Avantages : Accélère considérablement les opérations de SELECT et les jointures, surtout sur de grandes tables.
• Inconvénients : Prend de l'espace de stockage, et ralentit les opérations d'INSERT, UPDATE, DELETE car l'index doit être mis à jour à chaque modification.
• Quand l'utiliser : Sur les colonnes utilisées fréquemment dans les clauses WHERE, JOIN, ORDER BY, et GROUP BY. Les clés primaires et étrangères sont généralement indexées automatiquement.

CREATE INDEX idx_nom_client ON Clients (Nom);

Optimisation des requêtes

L'optimisation des requêtes est l'art de rendre les requêtes SQL plus rapides et efficaces.

• Utiliser WHERE pour filtrer tôt : Réduire le nombre de lignes traitées le plus tôt possible.
• Éviter SELECT * : Ne sélectionnez que les colonnes dont vous avez réellement besoin.
• Limiter les résultats : Utilisez LIMIT si vous n'avez besoin que d'un sous-ensemble des résultats.
• Comprendre les jointures : Assurez-vous que les conditions de jointure sont efficaces et utilisent des colonnes indexées.
• Éviter les sous-requêtes corrélées : Elles sont souvent moins performantes que les jointures.
• Utiliser EXPLAIN : La plupart des SGBD offrent une commande EXPLAIN (ou EXPLAIN ANALYZE) qui montre comment le moteur de base de données va exécuter une requête, permettant d'identifier les goulets d'étranglement.

Sauvegarde et restauration

La sauvegarde régulière des bases de données est une pratique essentielle pour la survie des données en cas de panne matérielle, de corruption de données, d'erreurs humaines ou d'attaques malveillantes.

• Types de sauvegarde : Complète, incrémentielle, différentielle.
• Fréquence : Dépend de la criticité des données et de la fréquence des modifications.
• Lieu de stockage : Les sauvegardes doivent être stockées dans un endroit différent de la base de données elle-même (et idéalement hors site).

La restauration est le processus de récupération des données à partir d'une sauvegarde. Il est crucial de tester régulièrement les procédures de restauration pour s'assurer qu'elles fonctionnent correctement.

Ces aspects (indexation, optimisation, sauvegarde) font partie de l'administration des bases de données et sont essentiels pour maintenir des systèmes performants et fiables.