Débuter avec les bases de données et langage SQL : méthodes et applications

Base de Données et langage SQL

 :

1.1 Qu’est-ce qu’une base de données?

1.1.1 Notion de base de données

Description générale

Il est di?cile de donner une dé?nition exacte de la notion de base de données. Une dé?nition très générale pourrait être :

Dé?nition 1.1

-Base de données- Un ensemble organisé d’informations avec un objectif commun.

Peu importe le support utilisé pour rassembler et stocker les données (papier, ?chiers, etc.), dès lors que des données sont rassemblées et stockées d’une manière organisée dans un but spéci?que, on parle de base de données.

Plus précisément, on appelle base de données un ensemble structuré et organisé permettant le stockage de grandes quantités d’informations a?n d’en faciliter l’exploitation (ajout, mise à jour, recherche de données). Bien entendu, dans le cadre de ce cours, nous nous intéressons aux bases de données informatisées.

Base de données informatisée

Dé?nition 1.2

–Base de données informatisée- Une base de données informatisé eest un ensemble structuré de données enregistrées sur des supports accessibles par l’ordinateur, représentant des informations du monde réel et pouvant être interrogées et mises à jour par une communauté d’utilisateurs.

Le résultat de la conception d’une base de données informatisée est une description des données.

Par des  cription on entend dé?nir les propriétés d’ensembles d’objets modélisés dans labase de données et non pas d’objets particuliers. Les objets particuliers sont créés par des programmes d’applications ou des langages de manipulation lors des insertions et des mises à jour des données.

Base de Données et langage SQL

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 TravauxDirigé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 A?nement de la solution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15

1.3.4 Que retenir de ce TD? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16

2 Conceptiondesbasesdedonnées(modèleE-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 Identi?ant 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é?exive . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 22

2.3.3 Association n-aire (n> 2) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23

2.4 TravauxDirigé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

2.7 TravauxDirigé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 Basesdedonnéesrelationnelles{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 TravauxDirigé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 Di?é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 TravauxDirigé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 LangageSQL 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é?nir une base – Langage de dé?nition de données (LDD) . . . . . . . . . . . . . . . . . 68

4.2.1 Introduction aux contraintes d’intégrité . . . . . . . . . . . . . . . . . . . . . . . . . 68

4.2.2 Créer une table :CREATETABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 68

4.2.3 Contraintes d’intégrité . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 69

4.2.4 Supprimer une table :DROPTABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

4.2.5 Modi?er une table :ALTERTABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

4.3 Modi?er une base – Langage de manipulation de données (LMD) . . . . . . . . . . . . . . 71

4.3.1 Insertion de n-uplets :INSERTINTO . . . . . . . . . . . . . . . . . . . . . . . . . . . 71

4.3.2 Modi?cation de n-uplets :UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

4.3.3 Suppression de n-uplets :DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 72

4.4 TravauxPratiques – 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 commandeSELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . 76

4.5.2 Traduction des opérateurs de l’algèbre relationnelle (1re partie) . . . . . . . . . . . 77

4.5.3 Syntaxe générale de la commandeSELECT . . . . . . . . . . . . . . . . . . . . . . . . 77

4.5.4 La clauseSELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78

4.5.5 La clauseFROM (1re partie) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

4.5.6 La clauseORDERBY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 79

4.5.7 La clauseWHERE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 80

4.5.8 Les expressions régulières . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81

4.6 TravauxPratiques – 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 clauseFROM (2e partie) : les jointures . . . . . . . . . . . . . . . . . . . . . . . . . 86

4.7.2 Les clausesGROUPBY etHAVING et les fonctions d’agrégation . . . . . . . . . . . . . 90

4.7.3 Opérateurs ensemblistes :UNION,INTERSECT etEXCEPT . . . . . . . . . . . . . . . . 92

4.7.4 Traduction des opérateurs de l’algèbre relationnelle (2e partie) . . . . . . . . . . . . 92

4.8 TravauxPratiques – SQL : Requêtes avancées{S8} . . . . . . . . . . . . . . . . . . . . . . . 95

4.8.1 Prix deGROUP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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é?nition de données (LDD){S9} . . . . . . . . . . . . . . 96

4.9.1 Séquences (CREATESEQUENCE) et typeSERIAL . . . . . . . . . . . . . . . . . . . . . 96

4.9.2 Règles (CREATERULE) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97

4.9.3 Vues (CREATEVIEW) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 98

4.9.4 Schémas (CREATESCHEMA) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 100

4.10 TravauxPratiques – 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

Base de Données et langage SQL    

<