bases de données concepts et programmation

i
BASES DE DONNÉES
CONCEPTS ET PROGRAMMATION
Antoine Cornuéjols
AgroParisTech, Spécialité Informatique (2009-2010)
Version du 19 octobre 2009

ii

Table des mati` eres
Table des matières iii
1 Concepts fondamentaux 1
1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.1 Un vieux problème : la bibliothèque de Leibniz . . . . . . . . . . . . . . . . . . . . 2
1.2 Données, bases de données et SGBD . . . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 Un exemple : BD sur l’INA-PG . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 3
2 Quelles compétences pour utiliser un SGBD ? . . . . . . . . . . . . . . . . . . . . . . . . . 3
2.1 Définition du schéma de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.2 Les opérations sur les données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 4
2.3 Optimisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
2.4 Concurrence d’accès . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
3 Caractéristiques de l’approche base de données . . . . . . . . . . . . . . . . . . . . . . . . 5
3.1 Architecture des SGBD et indépendance des données . . . . . . . . . . . . . . . . . 5
3.2 Persistance des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.3 Langage de requête . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.4 Partage des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.5 Fiabilité des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
3.6 Sécurité des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4 Lecontexte ............................................ 6
4.1 Pourquoi une Base de Données ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4.2 Où trouve-ton des BD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4.3 Applications récentes et à venir . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
4.4 Avantages liés à l’utilisation d’un SGBD . . . . . . . . . . . . . . . . . . . . . . . . 6
4.5 Les acteurs en jeu dans l’usage de SGBD . . . . . . . . . . . . . . . . . . . . . . . 6
4.6 Historique des SGBD et types de SGBD . . . . . . . . . . . . . . . . . . . . . . . . 6
5 Objectifs du cours . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 8
2 Modèle abstrait des bases de données 9
1 Introduction : modélisation des données . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.1 Généralités sur la conception d’une Base de Données . . . . . . . . . . . . . . . . . 9
1.2 Problèmes d’une conception sans précautions . . . . . . . . . . . . . . . . . . . . . 10
1.3 Introduction à une bonne méthode . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
2 Le modèle entité-association (E/A) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
2.1 Présentation informelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
2.2 Le modèle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
2.3 Avantages et inconvénients du modèle E/A . . . . . . . . . . . . . . . . . . . . . . 13
3 Langages de bases de données 15
1 Le modèle relationnel et ses règles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.1 Un peu d’histoire . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
1.2 Les notions de base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2 Bases théoriques des langages relationnels : l’algèbre relationnelle . . . . . . . . . . . . . . 18

iv Table des matières
2.1 Une manipulation ensembliste des données . . . . . . . . . . . . . . . . . . . . . . 18
2.2 Les opérateurs de l’algèbre relationnelle . . . . . . . . . . . . . . . . . . . . . . . . 19
2.3 Expressions de requêtes avec l’algèbre . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.4 Conclusions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
3 Bien concevoir une base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
3.1 Les dépendances fonctionnelles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 25
3.2 Décomposition en formes normales . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
3.3 Les contraintes d’intégrité structurelles . . . . . . . . . . . . . . . . . . . . . . . . . 31
3.4 Passage d’un schéma E/A à un schéma relationnel . . . . . . . . . . . . . . . . . . 31
4 Le langage relationnel SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
4.1 Les langages relationnels complets . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
4.2 Le langage SQL : historique . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
4.3 Le DML : Data Manipulation Language en SQL . . . . . . . . . . . . . . . . . . . 33
4.4 Le DDL (Data Description Language) en SQL . . . . . . . . . . . . . . . . . . . . 48
4 Pratique des SGBD 53
1 Exemples de SGBD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
1.1 Perspectives historiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
1.2 ORACLE ......................................... 54
1.3 Microsoft Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
2 Techniques d’optimisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3 Techniques d’accès partagés : le contrôle de concurrence . . . . . . . . . . . . . . . . . . . 54
3.1 Problèmes potentiels et notions de base . . . . . . . . . . . . . . . . . . . . . . . . 54
3.2 Techniques de contrôle de concurrence . . . . . . . . . . . . . . . . . . . . . . . . . 62
3.3 La gestion des transactions en SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
3.4 Le contrôle de concurrence sous Oracle . . . . . . . . . . . . . . . . . . . . . . . . . 67
4 Techniques de récupération d’erreur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
4.1 Les mécanismes utilisés . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
5 Les bancs de mesure des performances ("benchmarks") . . . . . . . . . . . . . . . . . . . . 71
6 Sécurité et autorisations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
7 L’interface C/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
7.1 Un exemple complet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
7.2 Autres commandes SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
8 Les environnements de programmation d’applications sur les bases de données . . . . . . . 79
8.1 La "programmation visuelle" . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
8.2 Langages de quatrième génération . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
8.3 Les interfaces au standard SQL et la portabilité . . . . . . . . . . . . . . . . . . . . 80
8.4 Les ateliers de génie logiciel (AGL / CASE) . . . . . . . . . . . . . . . . . . . . . . 81
5 Réalisation physique des bases de données 83
1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
2 Techniques de stockage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
2.1 Stockage de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
2.2 Organisation des fichiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 85
2.3 Organisation primaire des fichiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
2.4 L’exemple du SGBD Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
3 Structures de données pour optimiser les accès . . . . . . . . . . . . . . . . . . . . . . . . 87
3.1 Indexation de fichiers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 87
3.2 Hachage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 93
3.3 L’arbre-B . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
3.4 Autres méthodes d’indexation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
3.5 Bilan............................................ 101
3.6 Comparaison . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
3.7 Quelques règles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
3.8 Indexation dans Oracle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
3.9 Index en SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
3.10 Structures de données multidimensionnelles . . . . . . . . . . . . . . . . . . . . . . 105
4 Évaluation des requêtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
4.1 Introduction à l’optimisation des performances . . . . . . . . . . . . . . . . . . . . 105

Table des matières v
4.2 Algorithmes de base . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
4.3 Algorithmes de jointure . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
4.4 Compilation d’une requête et optimisation . . . . . . . . . . . . . . . . . . . . . . . 106
4.5 Oracle : optimisation et évaluation des requêtes . . . . . . . . . . . . . . . . . . . . 106
5 Vision globale sur l’exécution d’une requête SQL . . . . . . . . . . . . . . . . . . . . . . . 106
6 Optimisation de l’accès à une table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
6 Perspectives des SGBD 107
1 Nouveaux modèles de données pour nouvelles applications . . . . . . . . . . . . . . . . . . 107
2 Bases de données réparties . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 107
2.1 Motivations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
2.2 Fragmentation d’une BD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
2.3 Les problèmes spécifiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
2.4 Transaction répartie . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
3 Bases de données déductives . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
4 Entrepôts de données et fouille de données . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
4.1 Perspectives historiques . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
Bibliographie 111
Index 113