Cours SQL pour débutant

Cours de bases de données - Modèles et langages

  Table des matières

1  Introduction   3

2  Conception d’une base de données  5

  2.1  Schéma d’une base de données  .  5

    2.1.1  Qualité d’un schéma relationnel   . . . . . 5

    2.1.2  Schémas normalisés . . .  6

    2.1.3   La notion de dépendance fonctionnelle . .  7

    2.1.4  La décomposition d’un schéma . . . . . . 8

    2.1.5  Une approche pratique  .  10

  2.2  Le modèle E/A .  12

    2.2.1  Le schéma de la base Films . . . . . . . . 12

    2.2.2  Entités, attributs et identifiants . . . . . .  14

    2.2.3  Types d’entités  . . . . .  15

    2.2.4   Associations binaires . .  16

    2.2.5  Entités faibles . . . . . .  20

    2.2.6  Associations généralisées   21

    2.2.7  Bilan  .  23

3  Le modèle relationnel 25

  3.1  Définition d’un schéma relationnel  . . . . . . . .  25

  3.2  Passage d’un schéma E/A à un schéma relationnel  28

    3.2.1  Types d’entités  . . . . .  28

    3.2.2  Associations de un à plusieurs  . . . . . . 28

    3.2.3  Associations avec type d’entité faible  . .  29

    3.2.4  Associations binaires de plusieurs à plusieurs    . . . . . . .  30

    3.2.5  Associations ternaires . .  31

    3.2.6   Retour sur le choix des identifiants . . . . 31

  3.3  Définition d’un schéma SQL . . .  32

     3.3.1  Types SQL  . . . . . . .  32

    3.3.2  Création des tables  . . .  34

    3.3.3  Contraintes  . . . . . . .  35

    3.3.4  Clés étrangères  . . . . .  36

    3.3.5  La clause check . . . .  38

    3.3.6   Modification du schéma  38

    3.3.7  Création d’index  . . . .  39

4  L’algèbre relationnelle  41

  4.1  Les opérateurs de l’algèbre . . . .  42

  4.1.1  La sélection, ?  . . . . .  42

  4.1.2  La projection, ? . . . . .  43

  4.1.3  Le produit cartésien, × .  43

  4.1.4  Renommage . . . . . . .  44

  4.1.5  L’union, ? . . . . . . . .  45

  4.1.6  La différence, ? . . . . .  45

  4.1.7  Jointure, on . . . . . . .  45

  4.2  Expression de requêtes avec l’algèbre . . . . . . .  47

  4.2.1  Sélection généralisée . .  47

  4.2.2  Requêtes conjonctives  .  48

  4.2.3  Requêtes avec ? et ? . .  49

  4.2.4  Complément d’un ensemble  . . . . . . .  50

  4.2.5  Quantification universelle   50

5  SQL  51

5.1  Principes de SQL   52

5.1.1  Forme de base et interprétation . . . . . . 52

5.1.2  L’espace de recherche : clause from . . .  53

5.1.3  Les conditions : la clause where . . . . .  55

5.1.4  Les expressions : la clause select . . .  56

5.2  Recherche avec SQL . . . . . . .  57

5.2.1  Construction d’expressions . . . . . . . .  57

5.2.2  La clause where . . . .  58

5.2.3  Valeurs nulles . . . . . .  58

5.2.4  Tri et élimination de doublons  . . . . . .  59

5.3  Jointures  . . . .  60

5.3.1  Syntaxe classique . . . .  61

5.3.2  Tables calculées dans le from . . . . . .  63

5.3.3  Opérateurs de jointure  .  64

5.3.4  Opérations ensemblistes    66

5.3.5  Requêtes imbriquées  . .  66

5.3.6  Requêtes correlées  . . .  67

5.3.7  Requêtes avec négation .  69

5.4  Agrégats  . . . .  70

5.4.1  La clause group by . .  71

5.4.2  La clause having  . . . .  72

5.5  Mises à jour  . .  72

5.5.1  Insertion 72

5.5.2  Destruction . . . . . . .  73

5.5.3  Modification  . . . . . .  73

5.6  Les vues  . . . .  73

5.6.1  Création et interrogation d’une vue . . . .  74

5.6.2  Mise à jour d’une vue . .  75

6  Le langage PL/SQL  77

6.1  S1. Procédures stockées  . . . . .  77

6.1.1  Rôle et fonctionnement des procédures stockées . . . . . .  78

6.1.2  Introduction à PL/SQL .  79

6.1.3  Syntaxe de PL/SQL . . .  82

6.2  Les curseurs  . .  88

6.2.1  Déclaration d’un curseur   88

6.2.2  Exécution d’un curseur .  89

6.2.3  Les curseurs PL/SQL . .  90

6.3  Triggers . . . . .  93

ii

6.3.1  Principes des triggers . .  93

6.3.2  Syntaxe   94

6.3.3  Quelques exemples . . .  95

7  Indices and tables  97

Cours de bases de données - Modèles et langages, Version 1.0

Contents : Le document que vous commencez à lire fait partie de l’ensemble des supports d’apprentissage proposés sur le site . Il fait partie du cours consacré aux bases de données relationnelles, divisé en deux parties :

— La version en ligne du support “Modèles et langages” est accessible à , la version imprimable (PDF) est disponible à .

— La version en ligne du support “Aspects système” est accessible à , la version imprimable (PDF) est disponible à .

Deux autres cours, aux contenus proches, sont également disponibles :

— Un cours sur les bases de données documentaires et distribuées à .

— Un cours sur les applications avec bases de données à Reportez-vous à pour plus d’explications.

Important : Ce cours de Philippe Rigaux est mis à disposition selon les termes de la licence Creative Commons Attribution - Pas d’Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International. Cf.

Table des matières

Table des matières CHAPITRE1

Introduction

Ce support de cours s’adresse aux étudiants en premier cyle (IUT) ou second cycle universitaire (Licence, Master) et formations apparentées. Il propose un ensemble de chapitres consacrés aux principes et à la mise en œuvre de bases de données relationnelles, ainsi qu’à la pratique des Systèmes de Gestion de Bases de Données (SGBD). Il couvre plus particulièrement les modèles et langages des bases de données. Cette partie couvre tout d’abord la conception et la définition d’un schéma relationnel correct et complet, comprenant des tables, des contraintes, des vues, etc. Elle décrit ensuite l’algèbre relationnelle et SQL, ainsi que l’intégration de SQL avec un langage de programmation comme le C.

Une seconde partie, consacrée aux aspects systèmes présente les techniques internes utilisées par les SGBD relationnels pour stocker efficacement les données et évaluer des requêtes. Elle couvre la représentation physique, l’indexation, l’optimisation et comprend également une introduction aux problèmes de concurrence d’accès, dont la connaissance est nécessaire aux développeurs d’applications basées sur des SGBD. Cette seconde partie est disponible séparément sur le site le site .

La première partie est accessible aux étudiants suivant un cours d’introduction aux bases de données et ne demande que peu de pré-requis. La second partie est plus avancée et nécessite de bonnes bases en structures de données et algorithmique.

Le premier chapitre est une (rapide) présentation de tous les thèmes présentés en détails dans ce cours. On peut le lire comme une mise en perspective générale de l’ensemble du document.

Chapitre 1. Introduction CHAPITRE2

Conception d’une base de données

Ce chapitre est consacré la démarche de conception d’une base relationnelle. Elle se déroule en général selon un processus de conception comportant plusieurs étapes. Nous nous concentrons dans ce chapitre sur la modélisation, et sur la représentation de cette modélisation avec une notation très répandue, dite entité / association. Au préalable, nous allons discuter de l’objectif final, qui est d’obtenir un schéma de la base de données conforme au besoin et ne présentant par d’anomalie.

2.1 Schéma d’une base de données

Le schéma d’une base relationnelle est constitué d’un ensemble de schémas de tables. Le schéma d’une table à son tour consiste – pour l’essentiel – en un nom (de table) et un ensemble de noms d’attributs.

La conception d’un schéma relationnel ne présente pas de difficulté technique. On constate en pratique qu’elle demande une certaine expérience, notamment parce que les conseils d’usage, assez abstraits, deviennent beaucoup plus clairs quand on s’est trompé une ou deux fois et qu’on a constaté les conséquences de ses erreurs. Le schéma de données constitue en effet les fondations d’une application Le schéma n’est jamais figé : il évoluera avec l’application. Mais la partie existante d’un schéma est toujours difficile à remettre en cause sans entraîner une réécriture non négligeable.

Dans ce qui suit, nous illustrons, à partir d’exemples, les obstacles à éviter dans la conception d’un schéma, l’objectif à atteindre et les méthodes disponibles.

2.1.1 Qualité d’un schéma relationnel

Voici un exemple de schéma, avec une notation très simplifiée, que nous allons utiliser pour discuter de la notion centrale de “bon” et “mauvais” schéma. On veut créer une base de données représentant des films, avec des informations comme le titre, l’année, le metteur en scène, etc. On part d’un schéma rassemblant ces informations (“MES” est l’acronyme de “Metteur en Scène”) :

Film(titre, annee, prenomMes, nomMES, anneeNaiss)

Un tel schéma permet de représenter les données de manière complète, en en évitant autant que possible les anomalies? Regardons un exemple de contenu de la table décrite par ce schéma.

Même pour une information aussi simple, il est facile d’énumérer tout un ensemble de problèmes potentiels. Tous ou presque découlent d’un grave défaut de la table ci-dessus : il est possible de représenter la même information plusieurs fois, ou, pour employer un mot que nous retrouverons souvent, il y a redondance de l’information.

Anomalies lors d’une insertion

Rien n’empêche de représenter plusieurs fois le même film. Pire : il est possible d’insérer plusieurs fois le film Vertigo en le décrivant à chaque fois de manière différente, par exemple en lui attribuant une fois comme réalisateur Alfred Hitchcock, puis une autre fois John Woo, etc.

La bonne question consiste d’ailleurs à se demander ce qui distingue deux films l’un de l’autre, et à quel moment on peut dire que la même information a été répétée. Peut-il y avoir deux films différents avec le même titre par exemple? Si la réponse est non (?), alors on devrait pouvoir assurer qu’il n’y a pas deux lignes dans la table avec la même valeur pour l’attribut titre. Si la réponse est oui (ce qui semble raisonnable), il reste à déterminer quel est l’ensemble des attributs qui permet de caractériser de manière unique un film ou, à défaut, de créer un tel identifiant artificiellement. C’est une notion centrale sur laquelle nous revenons de manière approfondie.

Anomalies lors d’une modification

La redondance d’information entraîne également des anomalies de mise à jour. Supposons que l’on modifie l’année de naissance de Hitchcock pour la ligne Vertigo et pas pour la ligne Psychose. On se retrouve alors avec des informations incohérentes. Les mêmes questions que précédemment se posent d’ailleurs. Jusqu’à quel point peut-on dire qu’il n’y a qu’un seul réalisateur nommé Hitchcock, et qu’il ne doit donc y avoir qu’une seule année de naissance pour un réalisateur de ce nom?

Anomalies lors d’une destruction

On ne peut pas supprimer un film sans supprimer du même coup son metteur en scène. Si on souhaite, par exemple, ne plus voir le film Titanic figurer dans la base de données, on va effacer du même coup les informations sur James Cameron.

2.1.2 Schémas normalisés

Que déduire de ce qui précède? Tout d’abord qu’il existe des schémas avec de bonnes propriétés, et d’autres qui souffrent de défauts de conception. Ensuite, que nous avons besoin d’aller plus loin qu’une simple énumération d’attributs et énoncer des contraintes et des règles qui nous indiquent plus précisément les liens qui caractérisent les données.

Le modèle relationnel nous propose un outil précieux pour répondre à ces questions : la normalisation. Un schéma normalisé présente des caractéristiques formelles qu’il est possible d’évaluer. La normalisation nous garantit l’absence de défaut (et notamment de redondance) tout en préservant l’intégralité de l’information représentée.

La théorie du modèle relationnel a développé une construction mathématique solide pour qualifier les propriétés d’un schéma d’une part, et décomposer un schéma dénormalisé en schéma normalisé d’autre part. Le second aspect s’appuie malheureusement sur une démarche peu utilisable en pratique pour des raisons sur lesquelles nous reviendront plus loin. Le premier en revanche donne un éclairage très précis sur ce qu’est un bon schéma relationnel. Il est intéressant de consacrer quelques instants (voire un peu plus si vous voulez vous donner tous les moyens d’éviter des ennuis ultérieurs) à la définition des schémas normalisés.

2.1.3 La notion de dépendance fonctionnelle

Le principal concept est celui de dépendance fonctionnelle, qui fournit une construction de base pour élaborer les contraintes dont nous avons besoin pour caractériser nos données et leurs liens. Il s’énonce comme suit. Prenons un schéma de relation R, S un sous-ensemble d’attributs de R, et A un attribut quelconque de R.

Définition : dépendance fonctionnelle

On dit que A dépend fonctionnellement de S (ce que l’on note S ? A) quand, pour toute paire (l₁,l) de lignes de R, l’égalité de l₁ et de l₂ sur S implique l’égalité sur A.

Informellement, on peut raisonner ainsi : “Si je connais S, alors je connais A”. Par, exemple, si je prends l’ensemble des attributs suivants :

(nom, prenom, noSS, dateNaissance, adresse, email)

je peux chercher les dépendances fonctionnelles et trouver celles-ci :

— email ? nom,prenom,noSS,dateNaissance,adresse

— noSS ? email,nom,prenom,dateNaissance,adresse

J’ai donc reconnu que la connaisance d’un email détermine la connaissance des autres attributs, et de même pour le numéro de sécurité sociale. On peut avoir des dépendances fonctionnelles où la partie gauche comprend plusieurs attributs. Par exemple, pour les attributs suivants :

noEtudiant, noCours, annee, note

on trouvera la dépendance fonctionnelle suivante : — noEtudiant,noCours,annee ? note

La connaissance d’un étudiant, d’un cours et d’une année détermine la note obtenue.

Les dépendances fonctionnelles fournissent un outil pour analyser la qualité d’un schéma relationnel. Prenons le cas d’un système permettant d’évaluer des manuscrits soumis à un éditeur. Voici deux schémas possibles pour représenter les rapports d’évaluation d’un manuscrit (on va supposer qu’il existe un seul expert par manuscrit).

— Schéma 1

— Manuscrit (id_manuscrit, titre, id_expert, nom, adresse, commentaire)}

— Schéma 2

— Manuscrit (id_manuscrit, titre, id_expert, commentaire)

— Expert (id_expert, nom, adresse)

Il est très instructif de réfléchir à ces deux schémas, à leurs différences et à leurs avantages et inconvénients respectifs.

Si l’on raisonne en termes de dépendance fonctionnelle (que nous noterons DF à partir de maintenant), on constate que dans le schéma de la relation Expert(id_expert, adresse), il existe une dépendance fonctionnelle entre id_expert et adresse. En d’autre termes, si on trouve dans la base deux lignes avec l’identifiant d’expert 128, l’adresse pour ces deux lignes doit être la même. Une DF énonce donc une contrainte sur le contenu de la base de données : la préservation des DFs garantit l’absence d’incohérence (le même expert, mais deux adresses différentes et donc contradictoires).

La question suivante est bien sûr : pourquoi devrait-on représenter plusieurs fois la même information et s’imposer la charge de vérifier la cohérence? Là encore, en nous appuyant sur les dépendances fonctionnelles, nous allons pouvoir définir la notion essentielle de clé qui nous permettra d’éviter les redondances.

Définition : clé

Une clé (d’une relation R) est un sous-ensemble minimal C des attributs tel que tout attribut de R dépend fonctionnellement de C.

L’attribut id_expert est une clé de la relation Expert dans le schéma 2. Dans le schéma 1, l’attribut id_manuscrit est une clé de Manuscrit. Notez que tout attribut de la relation dépend aussi de la paire (id_manuscrit,id_expert), sans que cette paire soit une clé puisqu’elle n’est pas minimale (il existe un sous-ensemble strict qui est lui-même clé).

Et maintenant nous pouvons définir ce qu’est un schéma de relation normalisé.

Définition : schéma normalisé

Un schéma de relation R est normalisé quand, dans toute dépendance fonctionnelle S ? A sur les attributs de R, S est une clé.

Considérons à nouveau notre schéma 1 :

Manuscrit (id_manuscrit, id_expert, nom, commentaire, adresse_expert)

Il n’est pas normalisé car il existe une dépendance fonctionelle id_expert ? nom,adresse_expert, alors que l’attribut id_expert n’est pas une clé. Il existe une version intuitive de cette constatation abstraite : la table Manuscrit contient des informations qui ne sont pas directement liées à la notion de manuscrit. La présence d’informations indirectes est une source de redondance et donc d’anomalies.

L’essentiel de ce qu’il faut comprendre est énoncé (certes de manière très condensée) dans ce qui précède. On veut obtenir des relations normalisées car il et facile de montrer que la dénormalisation entraîne toutes sortes d’anomalies au moment où la base est mise à jour. De plus, si R est une relation de clé C, deux lignes de R ayant les même valeurs pour C auront par définition les mêmes valeurs pour les autres attributs et seront donc parfaitement identiques. Il est donc inutile (et nuisible) d’autoriser cette situation : on fera en sorte que la valeur d’une clé soit unique pour l’ensemble des lignes d’une relation. En résumé on veut des schémas de relation normalisés et dotés d’une clé unique bien identifiée. Cette combinaison interdit toute redondance.

Note : Les théoriciens qui ont étudié ces notions ont défini plusieurs formes de normalisation. Celle présentée ici est dite “troisième forme normale” (3FN).

2.1.4 La décomposition d’un schéma

Etant donné un schéma et ses dépendances fonctionnelles, nous savons déterminer s’il est normalisé. Peut-on aller plus loin et produire automatiquement un schéma normalisé à partir de l’ensemble des attributs et de leurs contraintes (les DFs)? En principe oui, en pratique pas vraiment.

Regardons d’abord le principe avec un exemple illustrant la normalisation d’un schéma relationnel par un processus de décomposition progressif. On veut représenter l’organisation d’un ensemble d’immeubles locatifs en appartements, et décrire les informations relatives aux propriétaires des immeubles et aux occupants de chaque appartement. Voici un premier schéma de relation :

Appart(id_appart, surface, id_immeuble, nb_etages, date_construction)

La clé est id_appart. Cette relation est-elle normalisée? Non, car l’identifiant de l’immeuble détermine fonctionnellement le nombre d’étages et la date de construction. On a donc une dépendance id_immeuble ? nb_tages,date_construction dont la partie gauche n’est pas la clé id_appart. En pratique, une telle relation dupliquerait le nombre d’étages et la date de construction autant de fois qu’il y a d’appartements dans un immeuble. Le bon schéma en l’occurrence serait la décomposition en deux relations :

Appart(id_appart, surface, id_immeuble)

Immeuble (id_immeuble, nb_etages, date_construction)

Supposons qu’un immeuble puisse être détenu par plusieurs propriétaires, et considérons la seconde relation suivante, :

Proprietaire(id_appart, id_personne, quote_part)

Est-elle normalisée? Oui car l’unique dépendance fonctionnelle est

id_appart,id_personne ? quote_part

Un peu de réflexion suffit à se convaincre que ni l’appartement, ni le propriétaire ne déterminent à eux seuls la quote-part. Seule l’association des deux permet de donner un sens à cette information, et la clé est donc le couple (id_appart,id_personne). Maintenant considérons l’ajout du nom et du prénom du propriétaire dans la relation.

Proprietaire(id_appart, id_personne, prenom, nom, quote_part)

La dépendance fonctionnelle id_personne ? prnom,nom indique que cette relation n’est pas normalisée. Le bon schéma est :

Proprietaire(id_appart, id_personne, quote_part)

Personne(id_personne, prenom, nom)

Si, en revanche, on décide qu’il ne peut y avoir qu’un propriétaire pour un appartement et inversement, la quotepart devient inutile, une nouvelle dépendance fonctionnelle id_personne ? id_appart apparaît, et la relation avant décomposition est bien normalisée, avec pour clé id_personne.

Voyons pour finir le cas des occupants d’un appartement, avec la relation suivante.

Occupant(id_personne, nom, prenom, id_appart, surface)

On mélange clairement des informations sur les personnes, et d’autres sur les appartements. Plus précisément, la clé est le couple (id_personne,id_appart), mais on a les dépendances suivantes :

— id_personne ? prnom,nom

— id_appart ? surface

Un premier réflexe pourrait être de décomposer en deux relations Personne(id_personne,prénom,nom) et Appart (id_appart,surface). Toutes deux sont normalisées, mais on perd alors une information importante, et même essentielle : le fait que telle personne occupe tel appartement. Il faut donc impérativement conserver une relation correspondant à la clé initiale :Occupant (id_personne,id_appart). D’où le schéma final :

Immeuble (id_immeuble, nb_etages, date_construction)

Proprietaire(id_appart, id_personne, quote_part)

Personne (id_personne, prenom, nom)

Appart (id_appart, surface)

Occupant (id_personne, id_appart)

Ce schéma, obtenu par décompositions successives, présente la double propriété — de ne pas avoir perdu d’information par rapport à la version initiale; — de ne contenir que des relations normalisées.

Important : L’absence de perte d’information est une notion qui est survolée ici mais qui est de fait essentielle. Une décomposition est sans perte d’information s’il est possible, par des opérations, de reconstruire la relation initiale, avant décomposition. Ces opérations, et notamment la jointure, seront présentés dans les chapitres suivants.

Cet exemple est une démonstration d’une approche quasiment algorithmique pour obtenir un schéma normalisé à partir d’un ensemble d’attributs initial. Cette approche est malheureusement inutilisable en pratique à cause d’une difficulté que nous avons cachée : les dépendances fonctionnelles présentes dans notre schéma ont été artificiellement créées par ajout d’identifiants pour les immeubles, les occupants et les appartements. En pratique, de tels identifiants n’existent

2.1. Schéma d’une base de données

pas si on n’a pas au préalable déterminé les “entités” présentes dans le schéma : Immeuble, Occupant, et Appartement. En d’autres termes, l’exemple qui précède s’appuie sur une forme de tricherie : on a normalisé un schéma dans lequel on avait guidé à l’avance la décomposition.

2.1.5 Une approche pratique

Reprenons notre table des films pour nous confronter à une situation réaliste. Rappelons les quelques attributs considérés.

(titre, annee, prenomMes, nomMES, anneeNaiss)

La triste réalité est qu’on ne trouve aucune dépendance fonctionnelle dans cet ensemble d’attribut. Le titre d’un film ne détermine rien puisqu’il y a évidemment des films différents avec le même titre, Eventuellement, la paire (titre, année) pourrait déterminer de manière univoque un film, mais un peu de réflexion suffit à se convaincre qu’il est très possible de trouver deux films différents avec le même titre la même année. Et ainsi de suite : le nom du réalisateur ou même la paire (prénom, nom) sont des candidats très fragiles pour définir des dépendances fonctionnelles. En fait, on constate qu’il est très rare en pratique de trouver des DFs “naturelles” sur lesquelles on peut solidement s’appuyer pour définir un schéma.

Il nous faut donc une démarche différente consistant à créer artificiellement des DFs parmi les ensembles d’attributs. La connaissance des identifiants d’appartement, d’immeuble et de personne dans notre exemple précédent correspondait à une telle création artificielle : tous les attributs de, respectivement, Immeuble, Appartement et Personne dépendent fonctionnellement, par construction, de leurs identifiants respectifs, ajoutés au schéma.

Comment trouve-t-on ces identifiants? Par une démarche essentiellement différente de celle partant “de bas en haut”, consistant à prendre un tas d’attributs et à les regrouper en relations. Au contraire, on part de groupes d’attributs appartenant à une même entité, à laquelle on ajoute un attribut-identifiant. La démarche consiste à :

— déterminer les “entités” (immeuble, personne, appartement, ou film et réalisateur) pertinents pour l’application;

— définir une méthode d’identification de chaque entité; en pratique on recourt à la définition d’un identifiant artificiel (il n’a aucun rôle descriptif) qui permet d’une part de s’assurer qu’une même “entité” est représentée une seule fois, d’autre part de référencer une entité par son identifiant.

— préserver le lien entre les entités sans introduire de redondance, par un mécanisme de référence basé sur la clé. Voici une illustration informelle de la méthode, que nous reprendrons ensuite de manière plus détailée avec la notation Entité/association.

Commençons par les deux premières étapes. On va d’abord distinguer la table des films et la table des réalisateurs. Ensuite, on va ajouter à chaque table un attribut spécial, l’identifiant, désigné par id.