cours base de données et SQL 18.00 / 20

Table des matières
1 Introduction aux bases de données {S1} 9
1.1 Qu’est-ce qu’une base de données ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.1.1 Notion de base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.1.2 Modèle de base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.2 Système de gestion de base de données (SGBD) . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.2.1 Principes de fonctionnement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.2.2 Objectifs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.2.3 Niveaux de description des données ANSI/SPARC . . . . . . . . . . . . . . . . . . 12
1.2.4 Quelques SGBD connus et utilisés . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
1.3 Travaux Dirigés – Sensibilisation à la problématique des bases de données {S1} . . . . . . 14
1.3.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.3.2 Approche naïve . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
1.3.3 Affinement de la solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
1.3.4 Que retenir de ce TD? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2 Conception des bases de données (modèle E-A) {S2-3} 17
2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
2.1.1 Pourquoi une modélisation préalable ? . . . . . . . . . . . . . . . . . . . . . . . . . 17
2.1.2 Merise . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
2.2 Éléments constitutifs du modèle entités-associations . . . . . . . . . . . . . . . . . . . . . . 18
2.2.1 Entité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
2.2.2 Attribut ou propriété, valeur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2.3 Identifiant ou clé . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
2.2.4 Association ou relation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
2.2.5 Cardinalité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
2.3 Compléments sur les associations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.3.1 Associations plurielles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.3.2 Association réflexive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22
2.3.3 Association n-aire (n > 2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
2.4 Travaux Dirigés – Modèle entités-associations {S2} . . . . . . . . . . . . . . . . . . . . . . . 27
2.4.1 Attention aux attributs multiples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.4.2 Étudiants, cours, enseignants, salles, . . . . . . . . . . . . . . . . . . . . . . . . . . . . 27
2.4.3 Deux associations ne peuvent lier un même ensemble d’entités . . . . . . . . . . . 27
2.4.4 Comprenez-vous les type-associations n-aire ? . . . . . . . . . . . . . . . . . . . . . 28
2.4.5 Cas d’une bibliothèque (1re partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 29
2.5 Règles de bonne formation d’un modèle entités-associations . . . . . . . . . . . . . . . . . 30
2.5.1 Règles portant sur les noms . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
2.5.2 Règles de normalisation des attributs . . . . . . . . . . . . . . . . . . . . . . . . . . 31
2.5.3 Règles de fusion/suppression d’entités/associations . . . . . . . . . . . . . . . . . . 32
2.5.4 Normalisation des type-entités et type-associations . . . . . . . . . . . . . . . . . . 36
2.6 Élaboration d’un modèle entités-associations . . . . . . . . . . . . . . . . . . . . . . . . . . 38
2.6.1 Étapes de conceptions d’un modèle entités-associations . . . . . . . . . . . . . . . . 38
2.6.2 Conseils divers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 39
5
6 TABLE DES MATIÈRES
2.7 Travaux Dirigés – Modèle entités-associations {S3} . . . . . . . . . . . . . . . . . . . . . . . 41
2.7.1 Mais qui a fait cette modélisation ? . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
2.7.2 Cas d’une bibliothèque (2e partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . 41
2.7.3 Cas d’une entreprise de dépannage . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
3 Bases de données relationnelles {S4-5} 43
3.1 Introduction au modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.1.1 Présentation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.1.2 Éléments du modèle relationnel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.1.3 Passage du modèle entités-associations au modèle relationnel . . . . . . . . . . . . 45
3.2 Normalisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.2.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.2.2 Dépendance fonctionnelle (DF) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 47
3.2.3 Première et deuxième forme normale . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.2.4 Troisième forme normale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.2.5 Forme normale de BOYCE-CODD . . . . . . . . . . . . . . . . . . . . . . . . . . . . 49
3.2.6 Quatrième et cinquième forme normale . . . . . . . . . . . . . . . . . . . . . . . . . 50
3.2.7 Remarques au sujet de la normalisation . . . . . . . . . . . . . . . . . . . . . . . . . 53
3.3 Travaux Dirigés – Modèle relationnel {S4} . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.3.1 Passage du modèle entités-associations au modèle relationnel . . . . . . . . . . . . 54
3.3.2 Normalisation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
3.4 Algèbre relationnelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.2 Sélection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.3 Projection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
3.4.4 Union . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
3.4.5 Intersection . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
3.4.6 Différence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
3.4.7 Produit cartésien . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
3.4.8 Jointure, theta-jointure, equi-jointure, jointure naturelle . . . . . . . . . . . . . . . . 60
3.4.9 Division . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
3.5 Travaux Dirigés – Algèbre relationnelle {S5} . . . . . . . . . . . . . . . . . . . . . . . . . . 62
3.5.1 Exercices de compréhension de requêtes . . . . . . . . . . . . . . . . . . . . . . . . 62
3.5.2 Trouver la bonne requête . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
4 Langage SQL 65
4.1 Introduction {S6} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
4.1.1 Présentation générale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 65
4.1.2 Catégories d’instructions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 66
4.1.3 PostgreSQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 67
4.2 Définir une base – Langage de définition de données (LDD) . . . . . . . . . . . . . . . . . 68
4.2.1 Introduction aux contraintes d’intégrité . . . . . . . . . . . . . . . . . . . . . . . . . 68
4.2.2 Créer une table : CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68
4.2.3 Contraintes d’intégrité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69
4.2.4 Supprimer une table : DROP TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.2.5 Modifier une table : ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.3 Modifier une base – Langage de manipulation de données (LMD) . . . . . . . . . . . . . . 71
4.3.1 Insertion de n-uplets : INSERT INTO . . . . . . . . . . . . . . . . . . . . . . . . . . . 71
4.3.2 Modification de n-uplets : UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
4.3.3 Suppression de n-uplets : DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72
4.4 Travaux Pratiques – SQL : Première base de données {S6} . . . . . . . . . . . . . . . . . . . 73
4.4.1 Informations pratiques concernant PostgreSQL . . . . . . . . . . . . . . . . . . . . 73
4.4.2 Première base de données . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
4.5 Interroger une base – Langage de manipulation de données : SELECT (1re partie) {S7} . . . 76
4.5.1 Introduction à la commande SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . 76
TABLE DES MATIÈRES 7
4.5.2 Traduction des opérateurs de l’algèbre relationnelle (1re partie) . . . . . . . . . . . 77
4.5.3 Syntaxe générale de la commande SELECT . . . . . . . . . . . . . . . . . . . . . . . . 77
4.5.4 La clause SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
4.5.5 La clause FROM (1re partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
4.5.6 La clause ORDER BY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79
4.5.7 La clause WHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80
4.5.8 Les expressions régulières . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
4.6 Travaux Pratiques – SQL : Premières requêtes {S7} . . . . . . . . . . . . . . . . . . . . . . . 84
4.6.1 Premières requêtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 84
4.6.2 Requêtes déjà résolues en utilisant l’algèbre relationnelle . . . . . . . . . . . . . . . 84
4.6.3 Utilisation des expressions régulières . . . . . . . . . . . . . . . . . . . . . . . . . . 85
4.7 Interroger une base – Langage de manipulation de données : SELECT (2e partie) {S8} . . . 86
4.7.1 La clause FROM (2e partie) : les jointures . . . . . . . . . . . . . . . . . . . . . . . . . 86
4.7.2 Les clauses GROUP BY et HAVING et les fonctions d’agrégation . . . . . . . . . . . . . 90
4.7.3 Opérateurs ensemblistes : UNION, INTERSECT et EXCEPT . . . . . . . . . . . . . . . . 92
4.7.4 Traduction des opérateurs de l’algèbre relationnelle (2e partie) . . . . . . . . . . . . 92
4.8 Travaux Pratiques – SQL : Requêtes avancées {S8} . . . . . . . . . . . . . . . . . . . . . . . 95
4.8.1 Prix de GROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
4.8.2 Requêtes déjà résolues en utilisant l’algèbre relationnelle . . . . . . . . . . . . . . . 95
4.8.3 GROUP toujours ! . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
4.9 Nouveaux objets – Langage de définition de données (LDD) {S9} . . . . . . . . . . . . . . 96
4.9.1 Séquences (CREATE SEQUENCE) et type SERIAL . . . . . . . . . . . . . . . . . . . . . 96
4.9.2 Règles (CREATE RULE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
4.9.3 Vues (CREATE VIEW) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98
4.9.4 Schémas (CREATE SCHEMA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100
4.10 Travaux Pratiques – SQL : Nouveaux objets {S9} . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.1 Séquences . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.2 Schéma et vues . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.3 Règles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.10.4 Toujours des requêtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 101
4.11 SQL intégré {S10} . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
4.11.1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 102
4.11.2 Connexion au serveur de bases de données . . . . . . . . . . . . . . . . . . . . . . . 102
4.11.3 Exécuter des commandes SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.11.4 Les variables hôtes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
4.11.5 Variables indicateur . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
4.11.6 Gestion des erreurs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 106
4.11.7 Curseurs pour résultats à lignes multiples . . . . . . . . . . . . . . . . . . . . . . . 107
4.11.8 Précompilation et compilation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 108
4.11.9 Exemple complet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
5 Corrections 111
Bibliographie 113