Bases de données et systèmes d'information
Modèle relationnel SQL, optimisation des requêtes, transactions, modélisation des données
Saïd Benaïchou
ellipses
Partie 1 Le modèle relationnel19
Chapitre 1 Les bases de données21
1 Introduction21
2 La notion de base de données22
3 Le SGBD24
4 Modèle de données et type de SGBD26
5 Les modèles de la première génération27
5.1 Le modèle hiérarchique27
5.2 Le modèle réseau30
6 L'architecture ANSI/X3/SPARC34
7 Les objectifs d'un SGBD36
8 Architectures matérielles pour les bases de données37
8.1 L'architecture de première génération37
8.2 L'architecture client/serveur à deux niveaux38
8.3 L'architecture client/serveur à trois niveaux39
8.4 L'architecture distribuée40
9 La base de données support41
Exercices42
Chapitre 2 Notions de base sur les bases de données relationnelles51
1 Introduction 51
2 Le modèle relationnel 52
2.1 Schéma de relation, attribut et domaine52
2.2 Tuple et relation53
2.3 Clés candidates et clé primaire54
2.4 Les clés étrangères55
3 La notion de base de données relationnelle56
3.1 Le schéma de base de données relationnelle56
3.2 La base de données relationnelle57
4 Les contraintes d'intégrité58
4.1 Contrainte sur un attribut59
4.2 Contrainte avec plusieurs attributs59
4.3 La contrainte d'entité60
4.4 La contrainte d'intégrité référentielle60
4.5 Contraintes dynamiques62
5 L'algèbre relationnelle62
5.1 Les opérateurs classiques62
a) L'union, l'intersection et la différence
62
b) Le produit cartésien
64
5.2 Les opérateurs unaires65
a) La projection
65
b) La restriction
65
5.3 La jointure66
5.4 La jointure naturelle67
5.5 Jointure de plusieurs relations68
5.6 Jointure externe69
5.7 Semi-jointure70
5.8 La division71
6 Formulation des requêtes avec l'algèbre relationnelle71
6.1 Requêtes avec simple extraction de données71
6.2 Requêtes avec calculs72
a) Calculs en lignes
73
b) Calculs en colonnes
73
c) Combinaison des calculs en lignes et en colonnes
74
d) Calculs par groupe de lignes
75
7 Le calcul relationnel76
7.1 Le calcul des prédicats du premier ordre (rappel)76
7.2 Le calcul de domaines78
7.3 Le calcul des tuples79
7.4 Le langage QBE80
Exercices83
Chapitre 3 Les dépendances87
1 Introduction87
2 Les dépendances fonctionnelles88
2.1 La notion de dépendance fonctionnelle88
2.2 Fermeture d'un ensemble de dépendances fonctionnelles90
2.3 Règles d'inférence91
2.4 Fermeture d'un sous-ensemble d'attributs94
2.5 La recherche des clés candidates95
a) Caractérisation des clés
95
b) Recherche d'une clé candidate
96
c) Recherche de toutes les clés candidates
97
2.6 Couverture minimale98
2.7 Graphe des dépendances fonctionnelles100
3 Les dépendances multivaluées100
3.1 La notion de dépendance multivaluée100
3.2 Règles d'inférence et fermeture103
3.3 Dépendance multivaluée imbriquée104
4 Dépendance de jointure106
5 Fermeture d'un ensemble de dépendances109
Exercices112
Chapitre 4 La normalisation117
1 Introduction117
2 Décomposition sans perte d'information118
2.1 Décomposition d'un schéma de relation119
2.2 La notion de décomposition sans perte119
3 Méthodes de décomposition d'un schéma de relation121
3.1 Décomposition avec une dépendance fonctionnelle122
3.2 Décomposition avec une dépendance multivaluée123
3.3 Décomposition avec une dépendance de jointure124
4 Objectif de la décomposition126
5 Décomposition avec préservation des dépendances127
6 Formes normales et algorithmes de décomposition130
6.1 La première forme normale131
6.2 Deuxième et troisième forme normale132
6.3 Forme normale de Boyce/Codd134
6.4 Les algorithmes de décomposition en BCNF et en 3NF avec préservation des dépendances136
a) L'algorithme de décomposition
137
b) L'algorithme de synthèse
138
6.5 Quatrième forme normale140
6.6 Cinquième forme normale142
Exercices144
Partie 2 Le langage SQL147
Chapitre 5 Notions de base du langage SQL2149
1 Introduction149
2 Les types de données150
2.1 Types pour les chaînes de caractères151
2.2 Types numériques151
2.3 Types pour les données temporelles152
a) Les dates et heures
152
b) Les durées
153
c) Opérations autorisées
154
2.4 Les chaînes de bits154
2.5 Les domaines155
2.6 Types de données supplémentaires156
3 Création d'une table156
3.1 L'instruction de base157
3.2 Contraintes sur les colonnes159
a) Valeur NULL et contrainte NOT NULL
159
b) Valeur par défaut
161
c) La clé primaire
161
3.3 Modification de la structure d'une table162
4 Les requêtes163
4.1 Requêtes élémentaires163
4.2 Requêtes avec critère de sélection165
5 Les prédicats IN, BETWEEN, LIKE et IS NULL168
6 Les noms de corrélation171
7 Le tri172
8 Traitement des expressions173
8.1 Expressions dans la liste de projection173
8.2 Evaluation des expressions en présence des valeurs NULL175
a) Expressions de type prédéfini
175
b) Expressions logiques
176
c) Les prédicats IS [NOT] TRUE, IS [NOT] FALSE et IS [NOT] UNKNOWN
177
9 Fonctions SQL2178
9.1 Fonctions pour les chaînes de caractères178
9.2 Les données temporelles180
9.3 Fonctions pour les valeurs NULL182
10 Les opérateurs de conversion et de choix183
10.1 L'opérateur CAST184
10.2 L'opérateur CASE185
11 Comparaison de lignes186
11.1 Les opérateurs relationnels186
11.2 Le prédicat IS NULL188
12 Les vues189
13 La pratique avec le système190
13.1 Le schéma190
13.2 La connexion191
13.3 Catalogue et dictionnaire des données192
Exercices194
Chapitre 6 Requêtes élaborées201
1 Introduction201
2 Requêtes avec jointure202
2.1 Jointure SQL/89202
a) Jointure de deux tables
202
b) Jointure de plusieurs tables
204
c) Autojointure
205
d) Le produit cartésien
206
e) La jointure naturelle
206
2.2 Jointure dans SQL2207
a) La θ-jointure
207
b) La jointure naturelle
208
c) La jointure externe
209
3 Les opérateurs union, intersection et différence210
4 Calcul statistique212
4.1 Fonctions d'agrégat213
4.2 GROUP BY, HAVING et SELECT complet214
4.3 Fonctionnalités OLAP218
5 Sous-requête221
5.1 Sous-requête retournant une seule valeur221
5.2 Sous-requête retournant une table222
5.3 Sous-requête corrélée222
5.4 Imbrication des sous-requêtes224
6 Les prédicats ALL, ANY, SOME, IN, MATCH, EXISTS et UNIQUE224
7 Formulation avancée228
8 Requête intermédiaire et récursivité229
Exercices230
Chapitre 7 Mises à jour, intégrité et sécurité des données237
1 Introduction237
2 Les mises à jour238
2.1 L'insertion238
a) Option VALUES
238
b) Option VALUES pour plusieurs lignes
241
c) Option requête
241
2.2 La modification242
2.3 La suppression243
2.4 La mise à jour à travers les vues244
3 Les contraintes d'intégrité246
3.1 Contraintes dans les tables246
a) La contrainte d'entité
247
b) Les clés candidates
248
c) La contrainte d'intégrité référentielle
248
d) Contrainte sur les lignes de tables
251
e) Cas d'une table existante
252
3.2 Les assertions253
3.3 Contraintes de domaines255
4 La sécurité des données257
4.1 Concepts de base258
a) Techniques disponibles
258
b) Contrôle d'accès au système
258
4.2 Privilèges dans SQL2259
4.3 Octroi de privilèges260
4.4 Révocation des privilèges262
Exercices263
Partie 3 Techniques avancées267
Chapitre 8 Méthodes pour une évaluation optimisée des requêtes269
1 Introduction269
2 Arbre de requête271
3 Principe d'évaluation du coût d'un arbre272
4 Coût en écriture d'un opérateur274
5 Implémentation des opérateurs276
5.1 La jointure276
a) Algorithme des boucles imbriquées
277
b) Algorithme de tri/fusion
277
c) Algorithme de tri/fusion avec clé candidate
279
d) Jointure avec index
279
5.2 La restriction280
a) Restriction sans index
281
b) Restriction avec index
282
5.3 La projection282
6 Méthodes pour l'optimisation283
7 Optimisation algébrique284
8 L'ordonnancement des jointures287
9 Exemple de recherche d'un plan optimal289
10 Techniques pour la performance294
10.1 Augmentation de la mémoire tampon294
10.2 La méthode pipeline294
10.3 Exécution en parallèle des opérateurs295
11 Optimisation dans les systèmes distribués296
11.1 La fragmentation des données297
a) La fragmentation verticale
297
b) La fragmentation horizontale
298
c) La fragmentation mixte
299
11.2 La réplication des données299
Exercices300
Chapitre 9 Les transactions307
1 Introduction307
2 La notion de transaction308
3 Le problème des transactions concurrentes309
3.1 La perte d'une mise à jour310
3.2 La lecture non reproductible310
3.3 La lecture impropre311
3.4 Le problème de la contrainte déjouée311
4 Mise en oeuvre des transactions312
5 Implémentation de l'atomicité et la durabilité : le journal313
6 Gestion des pannes314
7 La procédure de reprise à chaud315
8 Le protocole de validation à deux phases317
9 Gestion de la concurrence318
10 La technique du verrouillage319
11 Le problème de l'interblocage320
12 Verrouillage à granularité fixe et variable322
13 La sérialisabilité avec l'estampillage324
14 Niveaux d'isolation326
15 SQL et les transactions327
15.1 Types d'interférence327
15.2 Les instructions SQL327
a) Validation, annulation et initialisation
328
b) Paramètres d'une transaction
329
c) Initialisation explicite et points de sauvegarde d'une transaction
330
15.3 Contrainte immédiate et différée330
Exercices333
Chapitre 10 Programmation SQL337
1 Introduction337
2 SQL intégré338
2.1 Les instructions338
2.2 Les variables hôtes339
2.3 La gestion des exceptions et des erreurs341
a) SQLCODE et SQLSTATE
341
b) L'instruction déclarative WHENEVER
343
2.4 L'instruction SELECT344
2.5 La notion de curseur347
a) Principe de fonctionnement
347
b) Options d'un curseur
350
3 SQL Dynamique353
3.1 Evaluation d'instructions autre que SELECT et non paramétrées353
3.2 Evaluation d'instructions autre que SELECT avec paramètres connus355
3.3 SELECT avec liste de projection et paramètres connus356
3.4 Evaluation d'instructions quelconques357
a) Les DESCRIPTOR AREA
358
b) Manipulation des DESCRIPTOR AREA
358
c) Exécution des instructions SQL
360
4 Le langage PSM362
4.1 Concepts de base363
a) Variable et affectation
363
b) Instructions de branchement conditionnel
363
c) Les boucles
364
4.2 Fonctions et procédures stockées368
5 Les déclencheurs371
Exercices374
Partie la modélisation des données381
Chapitre 11 La modélisation conceptuelle des données avec UML383
1 Introduction333
2 Concepts de base335
2.1 La classe335
2.2 Les attributs386
2.3 L'identifiant333
2.4 L'association339
a) Le concept d'association
389
b) Les associations réflexives
392
c) Les associations n-aires
394
d) Les attributs d'une association
396
2.5 La multiplicité399
3 Autres relations401
3.1 L'agrégation et la composition401
3.2 La généralisation403
a) Le concept de généralisation
403
b) Recherche des généralisations
405
c) L'héritage multiple
409
4 Les contraintes d'intégrité 410
5 Contraintes sur les relations412
5.1 Contraintes entre associations412
a) La contrainte de totalité
413
b) La contrainte d'exclusion
413
c) La contrainte de partition
414
d) La contrainte de simultanéité
414
e) La contrainte d'inclusion
415
5.2 Contraintes sur la généralisation415
a) La contrainte de totalité
415
b) La contrainte d'exclusion
416
c) La contrainte de partition
416
6 Expression des contraintes d'intégrité avec le langage OCL418
6.1 Notions de base419
6.2 Types de données et opérateurs420
6.3 Expression de plusieurs contraintes dans un contexte422
6.4 Navigation à travers les associations423
6.5 Accès aux attributs425
6.6 Opérations sur les collections426
Exercices429
Chapitre 12 Le modèle Entité/Association433
1 Introduction433
2 Concepts de base434
2.1 L'entité, les propriétés et l'identifiant434
2.2 L'association435
a) Le concept d'association
435
b) Les associations réflexives
437
c) Les associations n-aires
438
d) Les propriétés d'une association
439
2.3 Les cardinalités440
3 Identifiant relatif441
4 La généralisation442
4.1 Le concept de généralisation443
4.2 Extension à l'héritage multiple445
5 Contraintes sur les relations446
5.1 Contraintes entre associations446
a) La contrainte de totalité
446
b) La contrainte d'exclusion
447
c) La contrainte de partition
447
d) La contrainte de simultanéité
447
e) La contrainte d'inclusion
448
5.2 Contraintes sur la généralisation449
a) La contrainte de totalité
450
b) La contrainte d'exclusion
450
c) La contrainte de partition
450
6 Les dépendances fonctionnelles450
6.1 Les dépendances fonctionnelles entre propriétés451
6.2 Les dépendances fonctionnelles inter-entités452
7 La normalisation454
7.1 La normalisation des entités456
a) La première forme normale (1NF)
456
b) Les formes normales supérieures (2NF, 3NF et BCNF)
457
7.2 La normalisation des associations458
a) Association non en 2NF
458
b) Association non en BCNF
460
Exercices463
Chapitre 13 La modélisation logique et physique des données469
1 Introduction469
2 La modélisation logique des données470
2.1 La classe et l'entité470
2.2 Association sans multiplicité l ou 0..1 (resp. sans cardinalité maximale 1)470
2.3 Association binaire avec multiplicité 1 ou 0..1 (resp. avec cardinalité maximale 1)472
a) Cas de la multiplicité 1 (resp. des cardinalités - 1,1 -)
472
b) Cas de la multiplicité 0..1 (resp. des cardinalités - 0,1 -)
473
2.4 L'association réflexive475
2.5 L'agrégation et la composition476
2.6 La généralisation477
2.7 La généralisation multiple478
2.8 L'identification relative (modèle E/A)478
3 La modélisation physique des données479
3.1 Description de l'étape479
3.2 Transformation des schémas de relation482
3.3 Expression des contraintes non graphiques482
3.4 Contraintes entre associations484
a) La contrainte de totalité
485
b) La contrainte d'exclusion
486
c) La contrainte de partition
486
d) La contrainte de simultanéité
487
e) La contrainte d'inclusion
488
3.5 Contraintes sur la généralisation489
a) La contrainte de totalité
489
b) La contrainte d'exclusion
490
c) La contrainte de partition
490
3.6 Dépendance fonctionnelle inter-entités (modèle E/A)490
Exercices491
References
495
Index
497