Conception de bases de données I

Conception de bases
de donnéesI
Stéphane Crozat
NF17
Cours (Fondamentaux)
Publiée le 24 février 2016
Paternité - Partage des Conditions Initiales à l'Identique :
http://creativecommons.org/licenses/by-sa/4.0/fr/

Table des
matières
I - Principes de la conception des bases de données 6
1. BD et SGBD: vue d'ensemble ..................................................................................................... 6
1.1. Exercice : SGBDR ................................................................................................................................... 7
1.2. Qu'est ce qu'une BD ? ............................................................................................................................ 7
1.3. Qu'est ce qu'un SGBD ? ......................................................................................................................... 7
1.4. Pourquoi des SGBD ? ............................................................................................................................. 7
1.5. Caractéristiques des SGBD ...................................................................................................................... 8
2. Notions générales pour les bases de données ................................................................................ 9
2.1. Notion de données .................................................................................................................................. 9
2.2. Notion de modèle de données ................................................................................................................... 9
2.3. Notion de schéma de données (niveau logique) ......................................................................................... 10
2.4. Notion de langage de données (niveau physique) ....................................................................................... 11
2.5. Notion de dictionnaire de données .......................................................................................................... 12
2.6. Exercice : Dictionnaire de données .......................................................................................................... 14
3. Méthodes de conception de bases de données ............................................................................. 14
3.1. Exercice : Étapes de la conception d'une base de données ......................................................................... 14
3.2. Méthodologie générale de conception d'une base de données ...................................................................... 14
3.3. La méthode MERISE et le modèle E-A ................................................................................................... 16
3.4. Le langage de modélisation UML ............................................................................................................ 17
3.5. Éléments pour l'analyse de l'existant et des besoins .................................................................................. 18
4. Synthèse: Les trois niveaux de conception ................................................................................ 19
5. Bibliographie .............................................................................................................................. 20
II - Modélisation conceptuelle de bases de données 21
1. Notion de modèle et de modélisation UML ................................................................................ 21
1.1. Exercice : Entreprise .............................................................................................................................. 21
1.2. Qu'est ce qu'un modèle ? ....................................................................................................................... 22
1.3. Qu'est ce qu'un modèle en informatique ? ............................................................................................... 22
1.4. Qu'est ce qu'un bon modèle ? ................................................................................................................. 22
1.5. Présentation d'UML .............................................................................................................................. 24
2. Introduction au diagramme de classes UML: classes et associations ......................................... 24
2.1. Exercice : Lab I ..................................................................................................................................... 24
2.2. Classes ................................................................................................................................................. 25
2.3. Attributs ............................................................................................................................................... 26
2.4. Méthodes .............................................................................................................................................. 27
2.5. Associations ......................................................................................................................................... 28
2.6. Cardinalité ........................................................................................................................................... 28
3. Introduction à l'héritage ............................................................................................................. 29
3.1. Exercice : Lab II .................................................................................................................................... 29
3.2. Héritage ............................................................................................................................................... 30
3.3. Exemple : Des voitures et des conducteurs ............................................................................................... 32
3.4. Classes abstraites .................................................................................................................................. 33
3.5. Héritage complet ................................................................................................................................... 34
3.6. Héritage exclusif .................................................................................................................................... 34
3.7. Héritage multiple ................................................................................................................................... 35
3.8. Équivalence entre association d'héritage et association 1:1 ........................................................................ 35
3.9. Exercice : Gestion des défauts ................................................................................................................. 36
4. Modélisation avancée des associations ........................................................................................ 37

4.1. Exercice : Lab III ................................................................................................................................... 37
4.2. Composition ......................................................................................................................................... 38
4.3. Agrégation ............................................................................................................................................ 39
4.4. Explicitation des associations ................................................................................................................. 40
4.5. Classe d'association ............................................................................................................................... 40
4.6. Associations ternaires ............................................................................................................................ 41
4.7. Exercice : Armoires secrètes ................................................................................................................... 42
5. Diagramme de classes avancé ..................................................................................................... 42
5.1. Exercice : Modéliser un site Web ............................................................................................................. 42
5.2. Contraintes ........................................................................................................................................... 43
5.3. Exemple de contraintes standardisées sur les associations ......................................................................... 44
5.4. Exemple de contraintes standardisées sur les attributs .............................................................................. 45
5.5. Paquetages ............................................................................................................................................ 45
5.6. Stéréotype ............................................................................................................................................ 46
5.7. Énumération : stéréotype <<enumeration>> .......................................................................................... 47
5.8. Type utilisateurs : stéréotype <<dataType>> ......................................................................................... 47
6. Quelques éléments de stylistique UML ....................................................................................... 48
7. Synthèse: Les diagrammes de modélisation conceptuelle .......................................................... 48
8. Bibliographie commentée sur la modélisation UML ................................................................... 49
III - Modélisation logique de bases de données relationnelles 51
1. Description du modèle relationnel .............................................................................................. 51
1.1. Niveau logique ....................................................................................................................................... 51
1.2. Définition du modèle relationnel ............................................................................................................. 52
1.3. Domaine .............................................................................................................................................. 52
1.4. Exercice ............................................................................................................................................... 53
1.5. Produit cartésien ................................................................................................................................... 53
1.6. Relation ............................................................................................................................................... 53
1.7. Attribut et enregistrement ...................................................................................................................... 54
1.8. Exemple : La relation "Vol" ................................................................................................................... 54
2. Clés et les clés étrangères dans le modèle relationnel ................................................................. 55
2.1. Clé ....................................................................................................................................................... 55
2.2. Déterminer les clés ................................................................................................................................ 55
2.3. Clé primaire et clés candidates ............................................................................................................... 56
2.4. Clé artificielle ....................................................................................................................................... 56
2.5. Clé étrangère ........................................................................................................................................ 57
2.6. Référence entre relations ....................................................................................................................... 57
2.7. Schéma relationnel ................................................................................................................................ 58
2.8. Exemple de schéma relationnel pour la géographie .................................................................................... 59
3. Passage UML-Relationnel: Classes et associations .................................................................... 60
3.1. Exercice ................................................................................................................................................ 61
3.2. Exercice ................................................................................................................................................ 62
3.3. Transformation des classes .................................................................................................................... 62
3.4. Transformation des attributs .................................................................................................................. 62
3.5. Transformation des attributs dérivés et méthodes ..................................................................................... 64
3.6. Transformation des associations 1:N ....................................................................................................... 64
3.7. Transformation des associations N:M ..................................................................................................... 65
3.8. Transformation des associations 1:1 (approche simplifiée) ........................................................................ 65
4. Passage UML-Relationnel: Héritage .......................................................................................... 66
4.1. Exercice ................................................................................................................................................ 67
4.2. Transformation de la relation d'héritage .................................................................................................. 69
4.3. Transformation de la relation d'héritage par référence ............................................................................. 69
4.4. Transformation de la relation d'héritage par les classes filles ..................................................................... 70
4.5. Transformation de la relation d'héritage par la classe mère ...................................................................... 71
4.6. Éléments de choix ................................................................................................................................. 72
4.7. Cas simples .......................................................................................................................................... 73
4.8. Cas problématiques ................................................................................................................................ 75
4.9. Exemple de transformation d'une relation d'héritage ................................................................................. 76
5. Passage UML-Relationnel: Associations avancées ..................................................................... 78
5.1. Exercice ................................................................................................................................................ 79
5.2. Exercice ................................................................................................................................................ 80
5.3. Transformation des classes d'association ................................................................................................. 80
5.4. Transformation des agrégations .............................................................................................................. 81

5.5. Transformation des compositions ............................................................................................................ 82
5.6. Correspondance entre UML et relationnel ............................................................................................... 83
5.7. Exercice ................................................................................................................................................ 83
5.8. Exercice ................................................................................................................................................ 84
6. Synthèse: Schéma relationnel .................................................................................................... 85
7. Bibliographie commentée sur le modèle relationnel .................................................................... 86
IV - Création et alimentation de bases de données SQL 87
1. Qu'est ce que le SQL ? ............................................................................................................... 87
2. Le Langage de Définition de Données de SQL ........................................................................... 88
2.1. Exercice : Lab I++ ................................................................................................................................ 88
2.2. Création de tables ................................................................................................................................. 89
2.3. Domaines de données ............................................................................................................................ 90
2.4. La valeur NULL .................................................................................................................................... 91
2.5. Contraintes d'intégrité ........................................................................................................................... 91
2.6. Exemple de contraintes d'intégrité .......................................................................................................... 92
2.7. Exercice ................................................................................................................................................ 93
2.8. Création de vues ................................................................................................................................... 94
2.9. Exercice : Problème de vue ...................................................................................................................... 95
2.10. Suppression d'objets ............................................................................................................................. 95
2.11. Modification de tables .......................................................................................................................... 95
2.12. Exemple de modifications de tables ........................................................................................................ 96
3. Gestion de données avec le Langage de Manipulation de Données de SQL ............................... 97
3.1. Exercice : La dernière valeur ................................................................................................................. 97
3.2. Insertion de données .............................................................................................................................. 97
3.3. Mise à jour de données .......................................................................................................................... 98
3.4. Suppression de données ......................................................................................................................... 99
4. Le Langage de Contrôle de Données de SQL ............................................................................. 99
4.1. Exercice : Les droits Lambda .................................................................................................................. 99
4.2. Attribution de droits ............................................................................................................................ 100
4.3. Révocation de droits ............................................................................................................................ 100
4.4. Création d'utilisateurs .......................................................................................................................... 101
5. Exercice : Du producteur au consommateur .............................................................................. 101
V - Interrogation de bases de données SQL 103
1. Tutoriel SQL ............................................................................................................................ 103
2. Algèbre relationnelle: opérateurs fondamentaux ...................................................................... 103
2.1. Concepts manipulatoires ...................................................................................................................... 103
2.2. Exercice : Gestion du personnel ............................................................................................................. 104
2.3. Projection ........................................................................................................................................... 105
2.4. Restriction .......................................................................................................................................... 105
2.5. Produit ............................................................................................................................................... 106
2.6. Jointure .............................................................................................................................................. 106
2.7. Exercice ............................................................................................................................................... 107
3. Algèbre relationnelle: opérateurs complémentaires .................................................................. 107
3.1. Jointure naturelle ................................................................................................................................ 107
3.2. Jointure externe .................................................................................................................................. 108
3.3. Opérateurs ensemblistes ....................................................................................................................... 108
3.4. Division .............................................................................................................................................. 109
3.5. Proposition de notations ...................................................................................................................... 110
3.6. Exercice : Opérateurs de base et additionnels .......................................................................................... 110
3.7. Exercice ............................................................................................................................................. 111
4. Questions simples avec le Langage de Manipulation de Données de SQL ................................ 111
4.1. Exercice : Représentation de représentants ............................................................................................. 111
4.2. Sélection ............................................................................................................................................. 113
4.3. Opérateurs de comparaisons et opérateurs logiques ................................................................................. 115
4.4. Expression du produit cartésien ............................................................................................................ 115
4.5. Expression d'une projection .................................................................................................................. 116
4.6. Expression d'une restriction ................................................................................................................. 117
4.7. Expression d'une jointure ..................................................................................................................... 117
4.8. Exercice : Tableau final ......................................................................................................................... 118
4.9. Expression d'une jointure externe ......................................................................................................... 120
4.10. Exercice : Photos à gauche ................................................................................................................. 122
4.11. Opérateurs ensemblistes ...................................................................................................................... 123
4.12. Tri ................................................................................................................................................... 123
4.13. Commentaires en SQL ....................................................................................................................... 123

5. Agrégats avec le Langage de Manipulation de Données de SQL .............................................. 124
5.1. Exercice : Re-représentation de représentants ......................................................................................... 124
5.2. Fonctions de regroupement ................................................................................................................... 126
5.3. Exercice : La somme finale .................................................................................................................. 126
5.4. Agrégats ............................................................................................................................................. 127
6. Synthèse: SQL ......................................................................................................................... 128
7. Bibliographie commentée sur le SQL ........................................................................................ 128
VI - Théorie de la normalisation relationnelle 129
1. Redondance et normalisation .................................................................................................... 129
1.1. Exercice : Introduction à la redondance .................................................................................................. 129
1.2. Les problèmes soulevés par une mauvaise modélisation ............................................................................ 130
1.3. Principes de la normalisation ............................................................................................................... 131
2. Les dépendances fonctionnelles ................................................................................................. 131
2.1. Exercice : A1, dans l'eau ! ..................................................................................................................... 132
2.2. Dépendance fonctionnelle ..................................................................................................................... 132
2.3. Les axiomes d'Armstrong ..................................................................................................................... 133
2.4. Autres propriétés déduites des axiomes d'Armstrong ............................................................................... 133
2.5. DF élémentaire ................................................................................................................................... 134
2.6. Notion de fermeture transitive des DFE ................................................................................................. 134
2.7. Notion de couverture minimale des DFE ............................................................................................... 135
2.8. Notion de graphe des DFE ................................................................................................................... 135
2.9. Définition formelle d'une clé ................................................................................................................. 136
2.10. Exercice : A1, touché ! ........................................................................................................................ 136
3. Les formes normales ................................................................................................................. 137
3.1. Formes normales ................................................................................................................................. 137
3.2. Principe de la décomposition ................................................................................................................ 137
3.3. Première forme normale ...................................................................................................................... 138
3.4. Deuxième forme normale ...................................................................................................................... 139
3.5. Troisième forme normale ..................................................................................................................... 139
3.6. Forme normale de Boyce-Codd ............................................................................................................. 140
3.7. Exercice : A1, coulé ! ............................................................................................................................ 141
4. Conception de bases de données normalisées ............................................................................ 142
4.1. Processus de conception d'une base de données normalisée ..................................................................... 142
4.2. Algorithme de décomposition ................................................................................................................ 142
4.3. Exemple de décomposition .................................................................................................................... 143
5. Exemple de synthèse: MCD-Relationnel-Normalisation-SQL .................................................. 144
5.1. Première étape : Modélisation conceptuelle ............................................................................................ 144
5.2. Deuxième étape : Modèle relationnel ..................................................................................................... 146
5.3. Troisième étape : Dépendances fonctionnelles ........................................................................................ 146
5.4. Quatrième étape : Formes normales ...................................................................................................... 146
5.5. Cinquième étape : Décomposition .......................................................................................................... 146
5.6. Sixième étape : Implémentation SQL LDD ............................................................................................. 147
6. Bibliographie commentée sur la normalisation ......................................................................... 147
Glossaire 148
Abréviations 149
Bibliographie 150
Webographie 151