Bases de données 2

Principes d'utilisation des systèmes de gestion de bases de données

D'après le cours d'Emmanuel Waller

Introduction

Quoi ? Qui ? Quand ? Pourquoi ? Comment ?

SGBD : Qu'est-ce que c'est ?
Utilisation :
- Qui peut l'utiliser ? Dans quelles situations est-ce utile ?
- Pourquoi ? (quels problèmes cela résout-il ?)
- Comment l'utiliser ? Quels sont les modes d'utilisation disponibles ?
Principes : Approche raisonnée et méthodique
Indépendant d'un SGBD particulier : concepts généraux

Les problèmes de base de données

Pourquoi avons-nous besoin des SGBD ?

Contexte réel : les programmeurs écrivent des applications
Rencontrent des problèmes (algorithmique, réseau, persistance des données)
Exemple concret : Réservation de places SNCF
Problèmes à gérer : traitement des données, gestion des transactions, etc.

Exemple SNCF

Illustration : Gestion des réservations SNCF

Clients effectuent des réservations (client, date, train, etc.)
Employés gèrent les trains (horaires, départs, arrivées, etc.)
Comment un SGBD permet de gérer ces interactions ?

Persistance

Situation : Client réserve un billet, puis se déconnecte
Il faut :
- Conserver sa réservation (stockage sécurisé)
- Effacer les informations sensibles comme son numéro de carte bancaire

Que doit-on persister ? Que doit-on supprimer ?

Grandes quantités de données

Situation : Quantité d'informations supérieure à la mémoire vive
Accès disque : Lent et coûteux
Problème : Comment garantir un temps de réponse raisonnable ?
Optimisation : Techniques pour accélérer l'accès aux données

Imaginez des solutions d'optimisation pour améliorer l'accès disque.

Reprise sur panne

Situation : Client a réservé et payé mais panne avant stockage des données
Problème : La transaction doit être atomique (tout ou rien)
Il faut : Garantir que paiement et réservation soient liés

Comment garantir qu'une transaction ne soit pas partiellement exécutée ?

Concurrence

Situation : Deux clients réservent la dernière place dans un train
Problème : Gestion des accès concurrents
Il faut : Garantir que seule une réservation est validée pour cette place

Quels mécanismes utiliser pour gérer la concurrence ? (verrouillage, isolation)

Confidentialité

Situation : Accès non autorisé à des données sensibles
Problème : Protéger les données des utilisateurs et limiter les accès
Il faut : Garantir que seuls les utilisateurs autorisés puissent accéder aux données sensibles

Contraintes d'intégrité

Situation : Une erreur introduit un kilométrage négatif
Il faut : Imposer des règles d'intégrité sur les données (ex : kilométrage non négatif)
Exemple : Contraintes sur les réservations de sièges

Répartition des données

Situation : Réservation pour un trajet international (Paris-Venise)
Problème : Communication entre bases de données dans différents pays
Il faut : Coordonner les systèmes entre différentes bases de données

Indépendance des niveaux

Situation : Modification de l'organisation des données sur disque
Problème : Modularité entre niveaux physique et logique
Il faut : Éviter de devoir réécrire toute l'application à chaque changement de structure

Modèle, conception, mise à jour, interrogation

Représenter les données : Modèle de données
Organiser : Conception des bases de données
Manipuler : Création, modification, suppression des données
Interroger : Langage de requête (SQL)

Vocabulaire (1/3)

Information = Donnée (ex : Train Paris-Saint Tropez)
Informations = Base de données
12 problèmes de base de données
Domaine des bases de données = Résolution de ces problèmes

Vocabulaire (2/3)

SGBD = Logiciel pour gérer les problèmes de base de données
SGBD fournit des outils conceptuels et pratiques pour résoudre ces problèmes

Vocabulaire (3/3)

SQL = Langage de base de données
Mode interactif : Ordres SQL tapés directement
Mode programme : Ordres SQL intégrés dans un programme

But du cours (1/2)

Objectif : Savoir utiliser le SGBD pour résoudre les 12 problèmes courants en bases de données

But du cours (2/2)

Objectif : Savoir utiliser le mode programme pour gérer les 9 problèmes spécifiques liés aux accès aux bases de données

Vue d'ensemble et architecture (1/2)

Base de données : Contient les données
SGBD : Exécute des programmes qui gèrent la base de données
Serveur : Tourne en continu, unique
Clients : Se connectent et se déconnectent à volonté

Vue d'ensemble et architecture (2/2)

Mode interactif : Gestion manuelle des ordres SQL
Mode programme : Ordres SQL exécutés via un programme (Java, PHP, etc.)

Structure du cours

Étudier les problèmes de base de données un par un
Pour chaque problème : comprendre et utiliser les outils fournis par le SGBD

Compétences à acquérir

Comprendre les mécanismes des problèmes de base de données
Détecter les occurrences de ces problèmes dans une application donnée
Programmer la meilleure solution pour chacune des occurrences

Pourquoi il ne suffit pas de lire la documentation

Les concepts en bases de données sont souvent plus complexes qu'ils n'y paraissent
Exemple : Comme en programmation Java, il ne suffit pas de lire un manuel pour maîtriser les subtilités
Apprendre les bases de données nécessite une compréhension approfondie des concepts et des pratiques

Quelles sont les difficultés à apprendre seul un langage comme SQL ?

Construction d'un SGBD et théorie (1/4)

Pour chaque problème de base de données, un SGBD doit fournir des outils pour le résoudre
Étude théorique et systèmes d'implantation pour répondre aux besoins des programmeurs

Construction d'un SGBD et théorie (2/4)

Exemple : Modèle de données
- Un bon modèle de données permet une représentation efficace des informations
- Différents modèles : Hiérarchique, relationnel, orienté objet, XML

Construction d'un SGBD et théorie (3/4)

Exemple : Langage d'interrogation
- Un bon SGBD doit fournir un langage performant pour formuler des requêtes
- Exemples : SQL, algèbre relationnelle, datalog
- Études théoriques : Propriétés des langages d'interrogation (monotonie, complexité, expressivité)

Construction d'un SGBD et théorie (4/4)

Exemple : Gestion de la concurrence
- Le SGBD doit implémenter des mécanismes robustes de gestion des verrous pour gérer les accès concurrents

Autres SGBD célèbres

Oracle (propriétaire, très coûteux)
SQL Server (Microsoft), DB2 (IBM), Ingres
SGBD libres : MySQL, PostgreSQL
Microsoft Access (plus basique, pour petites applications)

Avantages et inconvénients des solutions libres versus propriétaires

Bibliographie

SQL pour Oracle, Christian Soutou, Eyrolles, 2010
Maîtriser MySQL 5, Yves Darmaillac et Philippe Rigaux, O'Reilly, 2005
Pratique de MySQL et PHP, Philippe Rigaux, O'Reilly, 2005
Comprendre XSLT, Bernd Amann et Philippe Rigaux, O'Reilly, 2002

Note : Voir page de Philippe Rigaux pour des chapitres disponibles gratuitement

But du cours

À la fin de ce cours, vous saurez :

Utiliser un SGBD pour gérer les 12 problèmes communs de bases de données
Intégrer le mode programme pour résoudre les 9 problèmes d'accès au serveur de base de données

Modèle de données

Rappel du problème

Ce rappel couvre des concepts essentiels pour la suite du cours.
Situation : Il y a des informations à manipuler par l'application (ex: billets de train).
Il faut les représenter, organiser, créer, modifier, détruire et interroger.

Analyse du problème et outils SGBD

Il faut un modèle de données.

Outil fourni par le SGBD : le modèle relationnel.

Principe

Les informations de l'application sont représentées par des « tables » :

Table = nom + colonnes
Colonne = nom + type (integer, varchar2(10), etc.)
Contenu de la table = lignes (ou tuples)
Base de données = ensemble de tables liées
Relation entre les tables : clé primaire et clé étrangère

Exemple

Trois billets : Jules va à St Tropez (850 km), Jim à Rio de Janeiro (6000 km), Jeanne à New York (5000 km).

Client	Destination	KM
Jules	St Tropez	850
Jim	Rio de Janeiro	6000
Jeanne	New York	5000

Comment ajouter une nouvelle colonne 'Prix' pour chaque billet ?

Remarque

Paraît simple et évident ? Avant le relationnel : 20 ans de difficultés avec des modèles de « spaghettis »...

Ted Codd : prix Turing en 1986 (modèle + interrogation par algèbre et calcul).

Remarque (suite)

Ce problème ne se gère pas par des ordres de base de données par le programmeur.

C'est le constructeur du SGBD qui choisit le modèle. Cependant, le programmeur doit comprendre ce modèle pour l'utiliser efficacement lors de la conception.

Compétences à acquérir

Utilisation lors de la résolution du problème de la conception.

Comprendre le modèle relationnel
Concevoir une base de données en respectant les relations entre les tables

Mises à jour

Rappel du problème

Ce rappel couvre des concepts essentiels pour la gestion des données dans une base.
Situation : Il y a des informations à manipuler par l'application.
Elles sont représentées dans des tables.
Il faut pouvoir les créer, modifier et détruire selon les besoins de l'application.

Outils fournis par le SGBD

Le langage de mise à jour fait partie intégrante de SQL. Il permet de gérer les modifications des données dans une base.

Principaux ordres SQL pour les mises à jour :

INSERT : Ajouter de nouvelles lignes dans une table
UPDATE : Modifier les données existantes
DELETE : Supprimer des lignes
ALTER : Modifier la structure de la table (ajouter/supprimer des colonnes ou des contraintes)
DROP : Supprimer complètement une table

Principe

Les mises à jour permettent de manipuler non seulement les données mais aussi la structure des tables. Voici les principales actions :

Créer, renommer, supprimer une table
Ajouter, modifier, supprimer une colonne ou une contrainte
Insérer, modifier, supprimer des lignes (une ou plusieurs)
Attention : certaines opérations affectent seulement la structure, d'autres impactent le contenu

Pourquoi serait-il dangereux de supprimer une contrainte d'intégrité sur une colonne ?

Exemple

Voici quelques exemples d'ordres SQL pour manipuler une table :

create table piece (nom varchar2(10), prix real);
insert into piece values ('clou', 3.0);
update piece set prix = prix + 3.0 where nom = 'vis';
delete from piece where nom = 'vis';
insert into piece values ('ecrou', null);
alter table piece add (num integer);
rename piece to catalogue;
create table cheap(name, price) as 
    select nom, prix from piece where prix <= 4.0;
drop table piece;

Que se passe-t-il si une ligne avec un null est insérée dans une colonne sans contrainte ?

Explication détaillée des commandes

CREATE TABLE : Crée une nouvelle table avec les colonnes spécifiées.
INSERT INTO : Insère une nouvelle ligne dans la table.
UPDATE : Modifie les lignes existantes selon une condition.
DELETE : Supprime les lignes répondant à une condition.
ALTER TABLE : Modifie la structure de la table (ajout de colonne ici).
RENAME : Renomme la table.
CREATE TABLE AS SELECT : Crée une nouvelle table basée sur le résultat d'une requête SELECT.
DROP TABLE : Supprime la table de la base de données.

Dans quelles situations utiliseriez-vous CREATE TABLE AS SELECT ?

Compétences à acquérir

À la fin de cette section, vous devez être capable de :

Analyser : Savoir expliquer ce qu'une séquence d'ordres SQL fait sur une table donnée.
Construire : Savoir écrire des séquences d'ordres SQL pour résoudre des problèmes concrets dans une application.

Proposez une séquence d'ordres SQL pour ajouter une nouvelle colonne 'quantité' à la table catalogue et mettre à jour cette colonne avec des valeurs.

Persistance

Rappel du problème

La persistance des données est essentielle pour les applications en bases de données.
Situation : Un client effectue une réservation puis se déconnecte.
Problème : Il faut que certaines données (ex. la réservation) persistent même après la terminaison du processus client.

Outils fournis par le SGBD

Le SGBD garantit la persistance des données à la demande du programmeur.

Les données sont automatiquement stockées de manière persistante dans les tables.
Le programmeur n'a pas besoin de gérer manuellement la lecture et l'écriture dans des fichiers comme en Java.
Le SGBD se charge de conserver les données après la fin des sessions utilisateurs.

Principe

Le principe de base est simple : une donnée persiste en fin de session si, et seulement si, elle est dans une table.

Tout ordre SQL exécuté sur les données (insertion, modification, suppression) a un effet persistant sur la base de données.

Important : La persistance est assurée automatiquement par le SGBD, contrairement aux fichiers gérés manuellement en programmation classique.

Motivation

Comparaison avec Java :

En Java, la persistance des données nécessite des opérations explicites telles que l'écriture dans des fichiers, la gestion des flux d'entrée/sortie, des boucles, etc.
Avec un SGBD, la persistance est automatique, car il est dédié à cette tâche.
Cela simplifie grandement la gestion des données par rapport à la programmation traditionnelle.

Quelles seraient les étapes en Java pour persister une réservation ? Comparez avec l'utilisation d'un SGBD.

Exemple

Démonstration pratique :

Insérez une réservation dans une table et déconnectez-vous.
Après la reconnexion, les données de réservation sont toujours présentes dans la base.

Comment savoir si une donnée a bien été persistée après une déconnexion ?

Compétences à acquérir

Analyse : Savoir déterminer quelles données persistent après l'exécution d'une séquence d'ordres SQL donnée.
Construction : Savoir écrire les ordres SQL nécessaires pour assurer la persistance des données dans une application.
Cas général : Introduction aux transactions (abordé plus tard), qui permettent de garantir la persistance et la cohérence des données même en cas de défaillance.

Interrogation

Rappel du problème

L'application a besoin d'extraire des informations spécifiques des données.
Situation : Les données sont stockées dans des tables, mais il faut pouvoir les interroger pour les exploiter efficacement.
Problème : Comment récupérer les informations nécessaires en fonction de critères précis ?

Analyse du problème et outils SGBD

Pour interroger les données, il faut utiliser des « programmes » sous forme de requêtes SQL.
Le SGBD fournit un outil clé : le langage d'interrogation, qui fait partie de SQL.
Vocabulaire : une interrogation dans SQL est appelée une « requête ».
Une requête permet de sélectionner des informations selon des critères spécifiques.

Principe

Une requête SQL permet d'extraire des informations sous certaines conditions :

On sélectionne certains champs (colonnes).
On filtre les lignes d'une table qui satisfont une condition.
Le résultat est un ensemble de lignes correspondant aux critères donnés.

Quels champs et conditions utiliseriez-vous pour récupérer les clients ayant une réservation supérieure à 5000 km ?

Exemple

Donner les clients ayant un billet pour une destination située à plus de 3000 km :

select client
from billet
where km > 3000;

Résultat :

Jim
Jeanne

Que se passerait-il si aucune ligne ne satisfait la condition ?

Une vision très simple

Interaction avec la base de données :

Le client envoie une requête SQL au serveur (ex: SELECT, INSERT, UPDATE).
Le serveur exécute la requête et retourne les résultats au client.

Les requêtes SQL permettent d'extraire les données ou de les manipuler facilement.

Exemple

Donner tous les billets dans la table :

select *
from billet;

Donner toutes les destinations distinctes :

select distinct destination
from billet;

Pourquoi utiliser distinct dans cette requête ?

Motivation : langage de haut niveau

SQL est un langage proche de la pensée humaine, plus abstrait que l'assembleur ou les langages de bas niveau.
Les requêtes sont concises mais très puissantes : un seul ordre SQL peut accomplir beaucoup de choses.
Exemple : Comparez la taille d'une requête SQL pour extraire des données avec le nombre de lignes de code nécessaires en Java pour faire la même chose.
Simplicité : écrire une requête est généralement plus simple que d'écrire un programme complet.
Optimisation : Les SGBD sont capables d'optimiser l'exécution des requêtes pour améliorer les performances.

Imaginez écrire en Java une fonction pour récupérer tous les clients avec une destination unique. Comparez-la à l'équivalent SQL.

Remarque

Les fondements théoriques de SQL reposent sur des langages mathématiques :

Algèbre relationnelle : utilisée pour décrire les opérations sur les relations (tables).
Calcul des prédicats : utilisé pour exprimer des conditions logiques sur les données.
Fixpoint et PL/SQL : utilisés pour des opérations récursives et des procédures complexes.

Pourquoi est-il important que SQL ait des fondements théoriques solides ?

Compétences à acquérir

Analyse : Savoir expliquer ce que fait une requête SQL donnée.
Construction : Savoir écrire des requêtes SQL pour extraire ou manipuler des données. Exemples : jointures, différences, regroupements.
Introduire la notion de jointure et comment combiner les données de plusieurs tables pour obtenir des résultats complexes.

Écrivez une requête SQL qui combine deux tables (billet et client) pour afficher les clients et leur destination pour un trajet de plus de 3000 km.

Pour imprimer ou exporter ces slides, cliquez ici.
Puis imprimez la page dans votre navigateur.