Le modèle relationnel

Un modèle de données définit un mode de représentation des données selon trois composantes:

  • Des structures.
  • Des contraintes.
  • Des opérations.

Les deux premières relèvent du Langage de Définition de Données (LDD) dans un Système de Gestion de Bases de Données (SGBD). Le LDD est utilisé pour décrire le schéma d’une base de données. La troisième composante (opérations) est la base du Langage de Manipulation de Données (LMD) dont le représentant le plus célèbre est SQL.

Dans le contexte des bases de données, la principale qualité d’un modèle de données est d’être indépendant de la représentation physique. Cette indépendance permet de séparer totalement les tâches respectives des administrateurs de la base, chargés de l’optimisation de ses performances, et des développeurs d’application ou utilisateurs finaux qui n’ont pas à se soucier de la manière dont le système satisfait leurs demandes.

Le modèle relationnel offre une totale indépendance entre les représentations logique et physique. Ce chapitre présente la partie du modèle relative à la définition et à la création des tables.

Définition d’un schéma relationnel

Un des grands avantages du modèle relationnel est sa très grande simplicité. Il n’existe en effet qu’une seule structure, la relation. Une relation peut simplement être représentée sous forme de table (les deux termes seront employés comme des synonymes). Une table a un nom (Film) et se compose d’un ensemble de colonnes désignées par un nom d’attribut. Dans chaque colonne on trouve des valeurs d’un certain domaine (chaînes de caractères, nombres). Enfin chaque ligne (ou nuplet) correspond à une entité (ici des films).

id titre année genre
1 Alien 1979 Science-Fiction
2 Vertigo 1958 Suspense
3 Psychose 1960 Suspense
4 Kagemusha 1980 Drame
5 Volte-face 1997 Policier
6 Pulp Fiction 1995 Policier
7 Titanic 1997 Drame
8 Sacrifice 1986 Drame

Un schéma relationnel est constitué d’un ensemble de schémas de relations qui décrivent, à l’aide des élements présentés informellement ci-dessus (domaines, attributs, noms de relation) le contenu d’une relation. Le schéma de cette table est donc:

Film (titre: string, année: number, genre : string)

Il existe un langage de définition pour créer une relation dans un SGBDR, mais nous nous contenterons pour l’instant de cette description simplifiée. Voici maintenant quelques précisions sur la terminologie introduite ci-dessus.

Domaines

Un domaine de valeurs est un ensemble d’instances d’un type élémentaire. Exemple: les entiers, les réels, les chaînes de caractères, etc. La notion de type élémentaire s’oppose à celle de type structuré: il n’est pas possble en relationnel de placer dans une cellule un graphe, une liste, un enregistrement. En d’autres termes le système de types est figé et fourni par le système.

Note

C’est une limitation que l’on a cherché à lever par l’extension des systèmes à de nouveaux types. Ces SGBD extensibles ne sont pas présentés ici.

Attributs

Les attributs nomment les colonnes d’une relation. Il servent à la fois à indiquer le contenu de cette colonne, et à la référencer quand on effectue des opérations. Un attribut est toujours associé à un domaine. Un même nom d’un attribut peut apparaître dans plusieurs schémas distincts.

Schéma de relation

Un schéma est simplement un nom suivi de la liste des attributs, chaque attribut étant associé à un type. La syntaxe est donc:

\[R (A_1: D_1, A_2: D_2, \ldots, A_n: D_n)\]

où les \(A_i\) sont les noms d’attributs et les \(D_i\) les types.

On peut trouver dans un schéma de relation plusieurs fois le même type, mais une seule fois un même nom d’attribut. Le type peut être omis en phase de définition. Il est d’aileurs reltivement facile de changer le type d’un attribut sur une base existante.

Instance d’une relation

Une instance d’une relation R, ou simplement relation se définit mathématiquement comme un sous-ensemble fini du produit cartésien des domaines des attributs de \(R\). Rappelons que le produit cartésien \(D_1 \times \ldots \times D_n\) entre des domaines \(D_1, \ldots, D_n\) est l’ensemble de tous les nuplets \((v_1, \ldots, v_n), v_i \in D_i\).

Un des fondements du modèle relationnel est la théorie des ensembles et la notion de relation dans le modèle correspond strictement au concept mathématique dans cette théorie. Une (instance de) relation se représente sous forme de table, et on emploie le plus souvent ces deux termes comme des synonymes.

La définition d’une table comme un ensemble (au sens mathématique) a quelques conséquences importantes:

  • l’ordre des lignes est indifférent car il n’y a pas d’ordre dans un ensemble; conséquence pratique: le résultat d’une requête appliquée à une table ne dépend pas de l’ordre des lignes dans la table;
  • on ne peut pas trouver deux fois la même ligne car il n’y a pas de doublons dans un ensemble;
  • il n’y a pas de “cellule vide” dans la table, donc toutes les valeurs de tous les attributs sont toujours connues.

Dans la pratique les choses sont un peu différentes pour les doublons et les cellules vides, comme nous le verrons plus loin.

Clé d’une table

Une clé d’une table est un sous-ensemble des attributs qui permet d’identifier chaque ligne de manière unique (cf. le chapitre Conception d’une base de données). Comme on a vu que deux lignes sont toujours différentes, l’ensemble de tous les attributs est lui-même une clé mais il est indispensable de trouver au moins un sous-ensemble satisfaisant la condition. Le choix de la clé, qui s’effectue au moment de la conception, est très important pour la qualité du schéma. Choisir d’identifier un film par son titre comme nous l’avons envisagé dans l’exemple précédent n’est pas un très bon choix. Reportez-vous sur ce sujet dans le chapitre Conception d’une base de données consacré à la modélisation.

nuplets

Un nuplet d’une relation \(R(D_1, \ldots, D_n)\) est une liste de n valeurs \((v_1, \ldots, v_n)\) où chaque \(v_i\) est la valeur d’un attribut \(A_i\) de type \(D_i\). Exemple:

('Cyrano', 1992, 'Rappeneau')

Un nuplet est représenté par une ligne dans une relation sous forme de table. En principe, on connaît les valeurs de tous les attributs du nuplet.

Note

La terminologie “nuplet” vient directement de celle utilisée en théorie des ensembles. En pratique, on utilisera couramment le terme “ligne”, et parfois “tuple” qui n’est pas reconnu par l’Académie.

Base de données

Une (instance de) base de données est un ensemble fini (d’instances) de relations. Le schéma de la base est l’ensemble des schémas des relations de cette base.

La création d’un schéma de base de données est simple une fois que l’on a déterminé toutes les tables qui constituent la base. En revanche le choix de ces tables est un problème difficile car il détermine en grande partie les caractéristiques et qualités de la base: performances, exactitude, exhaustivité, disponibilité des informations, etc. En pratique, on définit le schéma à l’aide d’un modèle de données conceptuel, puis on le transcrit en schéma relationnel. La technique la plus répandue consiste à partir d’un schéma Entité/Association. La section suivante donne les règles du processus de transformation, en s’appuyant sur l’exemple du schéma E/A, de la figure Le schéma E/A des films, discuté dans le chapitre Conception d’une base de données.

_images/films.png

Fig. 14 Le schéma E/A des films

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

On passe donc d’un modèle disposant de deux structures (entités et associations) à un modèle disposant d’une seule (tables). Logiquement, entités et associations seront donc toutes deux transformées en tables. La subtilité réside en fait dans la nécessité de préserver les liens existant explicitement dans un schéma E/A et qui semblent manquer dans le modèle relationnel. Dans ce dernier cas on utilise en fait un mécanisme de référence par valeur basé sur les clés des tables.

Types d’entités

Le schéma d’une table est constitué du nom de la table, suivi de la liste des attributs. Alors, pour chaque type d’entité du schéma E/A, on obtient le schéma d’une table :

  • La table a le même nom que l’entité.
  • Chaque propriété du type d’entité, y compris l’identifiant, devient un attribut de la table.
  • L’identition est la clé de la table.

À partir du schéma E/A de la figure Le schéma E/A des films, on obtient les schémas de table suivants. La clé primaire est en gras.

  • Film (idFilm, titre, année, genre, résumé)
  • Artiste (idArtiste, nom, prénom, annéeNaissance)
  • Internaute (email, nom, prénom, région)
  • Pays (code, nom, langue)

Chaque entité sera donc représentée par une ligne (nuplet) dans la table. On peut remarquer que l’on a perdu pour l’instant tout lien entre les tables.

Associations de un à plusieurs

Soit une association de un à plusieurs (l s’agit ici des cardinalités maximales). Le passage au modèle relationnel suit les règles suivantes :

  • On crée les tables \(R_A\) et \(R_B\) correspondant respectivement aux entités \(A\) et \(B\), appliquant la première règle.
  • L’identifiant de \(B\) devient un attribut de \(R_A\).

L’idée est qu’une ligne (entité) de \(A\) référence la ligne (entité) de \(B\) qui lui est associée à l’aide d’un attribut qui contient la valeur de la clé de \(B\). Cet attribut est la clé étrangère de \(B\) dans \(A\).

Voici le schéma obtenu pour représenter l’association entre les types d’entité Film, Artiste et Pays. Les clés étrangères sont en italiques.

  • Film (idFilm, titre, année, genre, résumé, idArtiste, codePays)
  • Artiste (idArtiste, nom, prénom, annéeNaissance)
  • Pays (code, nom, langue)

Comment nommer la clé étrangère? Ici nous avons adopté une convention simple en concaténant id et le nom de la table référencée. On peut souvent faire mieux. Par exemple, dans le schéma de la table Film, le rôle précis tenu par l’artiste référencé dans l’association n’est pas induit par le nom idArtiste. L’artiste dans Film a un rôle de metteur en scène, mais il pourrait tout aussi bien s’agir du décorateur ou de l’accessoiriste: rien dans le nom de l’attribut ne le précise

On peut donner un nom plus explicite à l’attribut. Il n’est pas du tout obligatoire en fait que les attributs constituant une clé étrangère aient le même nom que ceux de le clé primaire auxquels ils se réfèrent. Voici le schéma de la table Film, dans lequel la clé étrangère pour le metteur en scène est nommée idRéalisateur.

  • Film (idFilm, titre, année, genre, résumé, idRéalisateur)

Les tables ci-dessous montrent un exemple de la représentation des associations entre Film et Artiste d’une part, Film et Pays d’autre part (on a omis le résumé du film). Noter que l’on ne peut avoir qu’un artiste dont l’id est 102 dans la table Artiste, puisque l’attribut idArtiste ne peut prendre qu’une valeur. Cela correspond à la contrainte, identifiée pendant la conception et modélisée dans le schéma E/A de la figure Le schéma E/A des films, qu’un film n’a qu’un seul réalisateur.

En revanche rien n’empêche cet artiste 102 de figurer plusieurs fois dans la colonne idRéalisateur de la table Film puisqu’il n’y a aucune contrainte d’unicité sur cet attribut. On a donc bien l’équivalent de l’association un à plusieurs élaborée dans le schéma E/A.

id nom prénom année
101 Scott Ridley 1943
102 Hitchcock Alfred 1899
103 Kurosawa Akira 1910
104 Woo John 1946
105 Tarantino Quentin 1963
106 Cameron James 1954
107 Tarkovski Andrei 1932

Et voici la table des films. Remarquez que chaque valeur de la colonne idRéalisateur est l’identifiant d’un artiste.

id titre année genre idRéalisateur codePays
1 Alien 1979 Science-Fiction 101 USA
2 Vertigo 1958 Suspense 102 USA
3 Psychose 1960 Suspense 102 USA
4 Kagemusha 1980 Drame 103 JP
5 Volte-face 1997 Policier 104 USA
6 Pulp Fiction 1995 Policier 105 USA
7 Titanic 1997 Drame 106 USA
8 Sacrifice 1986 Drame 107 FR

Note

les valeurs des clés primaires et étrangères sont complètement indépendantes l’une de l’autre. Nous avons identifié les films en partant de 1 et les artistes en partant de 101 pour des raisons de clarté, mais en pratique rien n’empêche de trouver une ligne comme:

(63, Gravity, 2014, SF, 63, USA)

Il n’y a pas d’ambiguité: le premier ‘63’ est l’identifiant du film, le second est l’identifiant du réalisateur.

Et voici, pour compléter, la table des pays.

code nom langue
USA Etats Unis anglais
FR France français
JP Japon japonais

Associations avec type d’entité faible

Une entité faible est toujours identifiée par rapport à une autre entité. C’est le cas par exemple de l’association entre Cinéma et Salle (voir chapitre Conception d’une base de données). Cette association est de type “un à plusieurs” car l’entité faible (une salle) est liée à une seule autre entité (un cinéma) alors que, en revanche, un cinéma peut être lié à plusieurs salles.

Le passage à un schéma relationnel est donc identique à celui d’une association 1-n classique. On utilise un mécanisme de clé étrangère pour référencer l’entité forte dans l’entité faible. La seule nuance est que la clé étrangère est une partie de l’identifiant de l’entité faible.

Regardons notre exemple pour bien comprendre. Voici le schéma obtenu pour représenter l’association entre les types d’entité Cinéma et Salle.

  • Cinéma (id, nom, numéro, rue, ville)
  • Salle (idCinéma, no, capacité)

On note que l’identifiant d’une salle est constitué de l’identifiant du cinéma et d’un numéro complémentaire permettant de distinguer les salles au sein d’un même cinéma. Mais l’identifiant du cinéma dans Salle est aussi une clé étrangère référençant une ligne de la table Cinéma. En d’autres termes, la clé étrangère est une partie de la clé primaire.

Cette modélisation simplifie l’attribution de l’identifiant à une nouvelle entité Salle puisqu’il suffit de reprendre l’identifiant du composé (le cinéma) et de numéroter les composants (les salles) relativement au composé. Il ne s’agit pas d’une différence vraiment fondamentale avec les associations 1-n mais elle peut clarifier le schéma.

Associations binaires de plusieurs à plusieurs

Soit une association binaire n-n entre \(A\) et \(B\). La transformation en un schéma relationnel se fait de la manière suivante:

  • On crée les tables \(R_A*\) et \(R_B*\) correspondant respectivement aux entités \(A\) et \(B\).
  • On crée une table \(R_{AB}\) pour l’association. La clé de \(R_A*\) et la clé de \(R_B*\) deviennent des clés étrangères de \(R_{AB}\).
  • La clé de cette table est la concaténation des clés des tables \(R_A\) et \(R_B\).
  • Les propriétés de l’association deviennent des attributs de \(R_{AB}\).

Toujours à partir du schéma des films, on obtient la table Rôle représentant l’association entre les films et les acteurs. Par application des règles, on obtient:

  • Film (idFilm, titre, année, genre, résumé, idRéalisateur, codePays)
  • Artiste (idArtiste, nom, prénom, annéeNaissance)
  • Role (idFilm, idActeur, nomRôle)

De même, on obtient une table Notation pour représenter l’association entre un internaute et les films qu’il a notés.

  • Film (idFilm, titre, année, genre, résumé, idRéalisateur, codePays)
  • Internaute (email, nom, prénom, région)
  • Notation (email, idFilm, note)

Pour bien comprendre le mécanisme de representation des entités et associations grâce aux clés primaires et étrangères, examinons les tables suivantes montrant un exemple de représentation de Rôle. On peut constater le mécanisme de référence unique obtenu grâce aux clés des tables. Chaque rôle correspond à un unique acteur et à un unique film. De plus on ne peut pas trouver deux fois la même paire (idFilm, idActeur) dans cette table (c’est un choix de conception qui découle du schéma E/A sur lequel nous nous basons). En revanche un même acteur peut figurer plusieurs fois (mais pas associé au même film), ainsi qu’un même film (mais pas associé au même acteur).

Voici tout d’abord la table des films.

id titre année genre idRéalisateur codePays
20 Impitoyable 1992 Western 130 USA
21 Ennemi d’état 1998 Action 132 USA

Puis la table des artistes.

id nom prénom année
130 Eastwood Clint 1930
131 Hackman Gene 1930
132 Scott Tony 1930
133 Smith Will 1968

En voici enfin la table des rôles, qui consiste ensentiellement en identifiants établissant des liens avec les deux tables précédentes. À vous de les décrypter pour comprendre comment toute l’information est représentée, et conforme aux choix de conception issus du schéma E/A. Que peut-on dire de l’artiste 130 par exemple? Peut-on savoir dans quels films joue Gene Hackman? Qui a mis en scène Impitoyable?

idFilm idArtiste nomRôle
20 130 William Munny
20 131 Little Bill
21 131 Bril
21 133 Robert Dean

On peut donc remarquer que chaque partie de la clé de la table Rôle est elle-même une clé étrangère qui fait référence à une ligne dans une autre table:

  • l’attribut idFilm fait référence à une ligne de la table Film (un film);
  • l’attribut idActeur fait référence à une ligne de la table Artiste (un acteur);

Le même principe de référencement et d’identification des tables s’applique à la table Notation. Il faut bien noter que, par choix de conception, on a interdit qu’un internaute puisse noter plusieurs fois le même film, de même qu’un acteur ne peut pas jouer plusieurs fois dans un même film. Ces contraintes ne constituent pas des limitations, mais des décisions prises au moment de la conception sur ce qui est autorisé, et sur ce qui ne l’est pas.

Le processus de conception détaillé ci-dessus permet de décomposer toutes les informations d’une base de données en plusieurs tables dont chacune stocke un des ensembles d’entités. Les liens sont définis par un mécanisme de référencement basé sur les clés primaires et les clés étrangères. Ce mécanisme est à la fois simple et puissant, et bien le comprendre est nécessaire pour maîtriser la construction de bases de données qui ne demanderont par de réorganisation – nécessairement douloureuse – par la suite.

Associations ternaires

Dans le cas d’associations impliquant plus de deux entités, on atteint une des limites du modèle Entité/Association en matière de spécification de contraintes. En première approche, on peut appliquer la règle énoncée précédemment pour les associations binaires et la généraliser. On obtiendrait alors, pour l’association Séance:

  • Cinéma (idCinéma, nomCinéma, numéro, rue, ville)
  • Salle (idCinéma, no, capacité)
  • Film (idFilm, titre, année, genre, résumé, idRéalisateur, codePays)
  • Horaire (idHoraire, heureDébut, heureFin)
  • Séance (idFilm, idCinéma, noSalle, idHoraire, tarif)

La table Séance a pour clé la concaténation des identifiants de chacune des entités composantes, ce qui donne une clé d’une taille assez importante, avec 4 attributs. La contrainte d’unicité portant sur l’ensemble des attributs, on s’aperçoit que rien n’interdit qu’une même salle présente deux films différents au même horaire. Si on souhaite éviter cette situation, la clé devient (idCinéma, noSalle, idHoraire), et on ne respecte plus la règle de passage du schéma E/A vers le schéma relationnel.

En d’autres termes, en cas d’association entre plus de 2 entités, la clé de la table représentant l’association est un sous-ensemble de la concaténation des clés. Il faut se poser soigneusement la question de la (ou des) clé(s) au moment de la création de la table car elle(s) ne peu(ven)t plus être déduite(s) du schéma E/A. On parle parfois de clé candidate. On peut trouver plus de deux clés candidates: c’est le cas par exemple si on souhaite qu’un même cinéma ne présente pas deux fois le même film dans des salles différentes.

Ce type de contrainte est exprimable dans un schéma relationnel (voir, plus loi, la clause unique). Les exemples qui précèdent montrent que l’association de plus de deux entités introduit une liberté d’interprétation et de représentation des données qui peut se faire au dépend de leur cohérence. Il est en fait très recommandé de ne représenter dans le schéma E/A que des associations binaires, ce qui peut se faire dans le cas qui précède en transformant l’association ternaire en une entité Séance dotée de son propre identifiant. La plupart des modèles de données intégrés comme outils de conception aux SGBD ne permettent que des associations binaires.

Retour sur le choix des identifiants

Il est maintenant largement admis que la clé d’une table doit être un identifiant “neutre” contenant un entier auto-incrémenté à chaque insertion. En effet:

  • La valeur de la clé doit être connue au moment de l’insertion.
  • Chaque valeur de l’identifiant doit caractériser de manière unique une occurrence,; le titre pour la table Film ou le nom pour la table Acteur ne sont clairement pas des bons choix.
  • Si on utilise un ensemble de propriétés comme identifiant, la référence à une entité devient très lourde; par exemple, la clé de Cinéma pourraît être (nom, rue, ville).
  • L’identifiant sert de référence externe et ne doit donc jamais être modifié (il faudrait répercuter les mises à jour sur les valeurs des clés étrangères, ce qui est très compliqué à garantir).

Il s’avère, sauf cas particulier, impossible de trouver une propriété dans une entité qui satisfait toutes ces conditions. Si on a une table Personne, par exemple, on peut mener la discussion suivante:

  • Le numéro de sécurité sociale? Oui, il identifie uniquement une personne ; est-ce qu’il ne change jamais? C’est moins clair: que se passe-t-il si on a fait une erreur à la saisie par exemple. Est-il toujours connu au moment de l’insertion de l’entité? Là, il semble très aventureux de répondre “oui”: quid des étrangers, des mineurs, de cas où le no SS est temporairement inconnu? Ce n’est certainement pas un bon choix.
  • L’adresse électronique (email)? Oui c’est un identifiant unique, mais est-ce qu’il ne peut pas passer d’une personne à une autre? En tout il est clair que les personnes changent d’email, ce qui rompt une des conditions ci-dessus.

En conclusion, la seule méthode dont on peut être sûr qu’elle ne présente pas de risque est l’ajout d’un attribut id, de type entier, dans lequel on place une valeur unique générée par le programme d’insertion.

Ce choix ne régle pas le risque de redondance (la même entité présente plusieurs fois) et même, d’une certaine manière, la maximalise. En effet, puisque l’identifiant est généré, une insertion répétée d’une même entité engendre de nouveaux identifiants sans provoquer de rejet. La redondance doit être gérée, mais indépendamment du choix de l’identifiant, par des contraintes d’unicité qui sont exprimables dans le langage de création des tables.

Définition d’un schéma SQL

Cette section présente le langage de définition de données (LDD) qui permet de spécifier le schéma d’une base de données relationnelle. Ce langage correspond à une partie de la norme SQL (structured query language), l’autre partie étant relative à la manipulation des données (LMD).

La définition d’un schéma comprend essentiellement deux parties: d’une part la description des tables et de leur contenu, d’autre part les contraintes qui portent sur les données de la base. La spécification des contraintes est souvent placée au second plan bien qu’elle soit en fait très importante: elle permet d’assurer, au niveau de la base, des contrôles sur l’intégrité des donnés qui s’imposent à toutes les applications accédant à cette base. Un dernier aspect de la définition d’un schéma, rapidement survolé ici, est la description de la représentation dite “physique”, celle qui décrit l’organisation des données. Il est toujours possible de réorganiser une base, et on peut donc tout à fait adopter initialement l’organisation choisie par défaut pour le système.

Types SQL

La norme SQL ANSI propose un ensemble de types dont les principaux sont donnés dans le tableau ci-dessous. Ce tableau présente également la taille, en octets, des instances de chaque type, cette taille n’étant ici qu’à titre indicatif car elle peut varier selon les systèmes.

Type Description Taille
integer Type des entiers relatifs 4 octets
smallint idem 2 octets
bigint idem 8 octets
float Flottants simple précision 4 octets
double Flottants double précision 8 octets
real Flottant simple ou double 8 octets
numeric (M, D) Numérique avec précision fixe. M octets
decimal(M, D) Idem. M octets
char(M) Chaînes de longueur fixe M octets
varchar*(M*) Chaînes de longueur variable L+1 avec \(L \leq M\)
bit varying Chaînes d’octets Longueur de la chaîne.
date Date (jour, mois, an) env. 4 octets
time Horaire (heure, minutes, secondes) env. 4 octets
datetime Date et heure 8 octets
year Année 2 octets

Types numériques exacts

La norme SQL ANSI distingue deux catégories d’attributs numériques: les numériques exacts, et les numériques flottants. Les types de la première catégorie (essentiellement integer et decimal) permettent de spécifier la précision souhaitée pour un attribut numérique, et donc de représenter une valeur exacte. Les numériques flottants correspondent aux types couramment utilisés en programmation (float, double) et ne représentent une valeur qu’avec une précision limitée.

Le type integer permet de stocker des entiers, sur 4 octets en général. Il existe deux variantes du type integer: smallint et bigint. Ces types différent par la taille utilisée pour le stockage: voir le tableau des types SQL.

Le type decimal(M, D) correspond à un numérique de taille maximale M, avec un nombre de décimales fixé à D. numeric est un synonyme de decimal. Ces types sont surtout utiles pour manipuler des valeurs dont la précision est connue, comme les valeurs monétaires. Afin de préserver cette précision, les instances de ces types sont stockées comme des chaînes de caractères.

Types numériques flottants

Ces types s’appuient sur la représentation des numériques flottants propre à la machine, en simple ou double précision. Leur utilisation est donc analogue à celle que l’on peut en faire dans un langage de programmation comme le C.

  • Le type float correspond aux flottants en simple précision.
  • Le type double precision correspond aux flottants en double précision; le raccourci double est accepté.

Caractères et chaînes de caractères

Les deux types principaux de la norme ANSI sont char et varchar. Ces deux types permettent de stocker des chaînes de caractères d’une taille maximale fixée par le paramètre M. Les syntaxes sont identiques. Pour le premier, char(M), et varchar(M) pour le second. La différence essentielle est qu’une valeur char a une taille fixée, et se trouve donc complétée avec des blancs si sa taille est inférieure à M. En revanche une valeur varchar a une taille variable et est tronquée après le dernier caractère non blanc.

Quand on veut stocker des chaînes de caractères longues (des textes, voire des livres), dont la taille dépasse, typiquement, 255 caractères, le type varchar ne suffit plus. La norme SQL propose un type bit varying qui correspond à de très longues chaînes de caractères. Souvent les systèmes proposent des variantes de ce type sous le nom text ou blob (pour Binary Long Object).

Dates

Un attribut de type date stocke les informations jour, mois et année (sur 4 chiffres). La représentation interne n’est pas spécifiée par la norme. Tous les systèmes proposent de nombreuses opérations de conversion (non normalisées) qui permettent d’obtenir un format d’affichage quelconque.

Un attribut de type time représente un horaire avec une précision à la seconde. Le type datetime permet de combiner une date et un horaire.

Création des tables

D’une manière générale, les objects du schéma sont créés avec create, modifiés avec alter et détruits avec drop, alors que les données, instances du schéma sont créées, modifiées et détruites avec, respectivement, insert, update et delete.

Voici un premier exemple avec la commande de création de la table Internaute.

create table Internaute (email varchar (40) not null,
                        nom varchar (30) not null ,
                        prenom varchar (30) not null,
                        region varchar (30),
                        primary key (email));

La syntaxe se comprend aisément. La seule difficulté est de choisir correctement le type de chaque attribut.

Conventions: noms des tables, des attributs, mots-clé SQL

On dispose, comme dans un langage de programmation, d’une certaine liberté. La seule recommandation est d’être cohérent pour des raisons de lisibilité. D’une manière générale, SQL n’est pas sensible à la casse. Quelques propositions:

  • Le nom des tables devrait commencer par une majuscule, le nom des attributs par une minuscule;
  • quand un nom d’attribut est constitué de plusieurs mots, on peut soit les séparer par des caractères ‘_’, soit employer la convention CamelCase: minuscule au premier mot, majuscule aux suivants. Exemple: mot_de_passe ou motDePasse.
  • Majuscule ou minuscule pour les mots-clé SQL? Quand on inclut une commande SQL dans un langage de programmation, il est sans doute plus lisible d’utiliser des majuscules pour les mots-clé.

Les accents et caractères diacritiques sont-ils acceptés? En principe oui, cela dépend des systèmes. Mais en pratique le codage au-delà de l’ASCII est toujours susceptible de soulever des problèmes (par exemple pour interroger une base dont le schéma contient des lettres accentuées, avec un clavier QWERTY). Il vaut sans doute mieux malheureusement l’éviter.

Le not null dans la création de table Internaute indique que l’attribut correspondant doit toujours avoir une valeur. Il s’agit d’une différence importante entre la pratique et la théorie: on admet que certains attributs peuvent ne pas avoir de valeur, ce qui est très différent d’une chaîne vide ou de 0. Quand on parle de valeur null en SQL, il s’agit en fait d’une absence de valeur. En conséquence:

  • on ne peut pas faire d’opération incluant un null;
  • on ne peut pas faire de comparaison avec un null.

L’absence de valeur a des conséquences parfois déroutantes. Il est préférable d’ajouter la contrainte not null quand c’est pertinent: cela renforce la qualité de la base et facilite le travail des applications par la suite. L’option suivante permet ainsi de garantir que tout internaute a un mot de passe.

motDePasse  varchar(60) not null

Le SGBD rejettera alors toute tentative d’insérer une ligne dans Internaute sans donner de mot de passe.

Important

La clé primaire doit toujours être déclarée not null.

Une autre manière de forcer un attribut à toujours prendre une valeur est de spécifier une valeur par défaut avec l’option default.

create table Cinema (id integer not null,
                     nom varchar (30) not null ,
                     adresse varchar(255) default 'Inconnue'
                     primary key (id));

Quand on insérera une ligne dans la table Cinéma sans indiquer d’adresse, le système affectera automatiquement la valeur 'Inconnue' à cet attribut. En général on utilise comme valeur par défaut une constante, sauf pour quelques variables fournies par le système (par exemple sysdate pour indiquer la date courante).

Contraintes

La création d’une table telle qu’on l’a vue précédemment est extrêmement sommaire car elle n’indique que le contenu de la table sans spécifier les contraintes que doit respecter ce contenu. Or il y a toujours des contraintes et il est indispensable de les inclure dans le schéma pour assurer (dans la mesure du possible) l’intégrité de la base.

Voici les règles (ou contraintes d’intégrité) que l’on peut demander au système de garantir:

  • La valeur d’un attribut doit être unique au sein de la table.
  • Un attribut doit toujours avoir une valeur. C’est la contrainte not null vue précédemment.
  • Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la table.
  • Un attribut dans une table est liée à la clé primaire d’une autre table (intégrité référentielle).
  • Enfin toute règle s’appliquant à la valeur d’un attribut (min et max par exemple).

Les contraintes sur les clés doivent être systématiquement spécifiées. La dernière (clause check) s’appuie en grande partie sur la connaissance du langage d’interrogation de SQL et sera vue ultérieurement.

Clés d’une table

Une clé est un attribut (ou un ensemble d’attributs) qui identifie(nt) de manière unique un nuplet d’une table. Il peut y avoir plusieurs clés mais l’une d’entre elles doit être choisie comme clé primaire. Ce choix est important: la clé primaire est la clé utilisée pour référencer une ligne et une seule à partir d’autres tables. Il est donc très délicat de la remettre en cause après coup. En revanche les clés secondaires peuvent être créées ou supprimées beaucoup plus facilement.

La clé primaire est spécifiée avec l’option primary key.

create table Pays (code varchar(4) not null,
                   nom  varchar (30) default 'Inconnu' not null,
                   langue varchar (30) not null,
                   primary key (code));

Il doit toujours y avoir une clé primaire dans une table afin de pouvoir désigner une ligne de manière univoque. Une clé peut être constituée de plusieurs attributs:

create table Notation (idFilm integer not null,
                       email  varchar (40) not null,
                       note  integer not null,
                       primary key (idFilm, email));

Tous les attributs figurant dans une clé doivent être déclarés not null. Cela n’a pas vraiment de sens en effet d’identifier des lignes par des valeurs absentes.

Comme nous l’avons déjà expliqué à plusieurs reprises, la méthode recommandée pour gérer la clé primaire est d’utiliser un attribut id, sans aucune signification particulière autre que celle de contenir la valeur unique identifiant une ligne. Voici un exemple typique:

create table Artiste (id integer not null,
                      nom varchar (30) not null,
                      prenom varchar (30) not null,
                      anneeNaiss integer,
                      primary key (id))

La valeur de cet identifiant peut même est automatiquement engendrée à chaque insertion, ce qui soulage d’avoir à implanter un mécanisme de génération d’identifiant. La méthode varie d’un système à l’autre, et repose de manière générale sur la notion de séquence. Voici la syntaxe MySQL pour indiquer qu’une clé est auto-incrémentée.

create table Artiste (id integer not null auto increment,
                     ...,
                      primary key (id))

L’utilisation d’un identifiant artificiel n’apporte rien pour le contrôle des redondances. Il est possible d’insérer des centaines de lignes dans la table Artiste ci-dessus ayant toutes exactement les mêmes valeurs, et ne différant que par la clé.

Les contraintes empêchant la redondance (et plus généralement assurant la cohérence d’une base) sont spécifiées indépendamment de la clé par la clause unique. On peut par exemple indiquer que deux artistes distincts ne peuvent avoir les mêmes nom et prénom.

create table Artiste  (idArtiste integer not null auto increment,
                      nom varchar (30) not null,
                      prenom varchar (30) not null,
                      anneeNaiss integer,
                      primary key (idArtiste),
                      unique (nom, prenom))

Il est facile de supprimer cette contrainte (dite de “clé secondaire”) par la suite. Ce serait beaucoup plus difficile si on avait utilisé la paire (nom, prenom) comme clé primaire puisqu’elle serait alors utilisée pour référencer un artiste dans d’autres tables.

La clause unique ne s’applique pas aux valeurs null.

Clés étrangères

SQL permet d’indiquer quelles sont les clés étrangères dans une table, autrement dit, quels sont les attributs qui font référence à une ligne dans une autre table. On peut spécifier les clés étrangères avec l’option foreign key.

create table Film  (idFilm integer not null,
                   titre    varchar (50) not null,
                   annee    integer not null,
                   idRealisateur    integer not null,
                   genre varchar (20) not null,
                   resume      varchar(255),
                   codePays    varchar (4),
                   primary key (idFilm),
                   foreign key (idRealisateur) references Artiste,
                   foreign key (codePays) references Pays);

La commande

foreign key (idRealisateur) references Artiste,

indique que idRealisateur référence la clé primaire de la table Artiste. Le SGBD vérifiera alors, pour toute modification pouvant affecter le lien entre les deux tables, que la valeur de idRealisateur correspond bien à une ligne de Artiste. Ces modifications sont:

  • l’insertion dans Film avec une valeur inconnue pour idRealisateur;
  • la destruction d’un artiste;
  • la modification de id dans Artiste ou de idRealisateur dans Film.

En d’autres termes on a la garantie que le lien entre Film et Artiste est toujours valide. Cette contrainte est importante pour s’assurer qu’il n’y a pas de fausse référence dans la base, par exemple qu’un film ne fait pas référence à un artiste qui n’existe pas. Il est beaucoup plus confortable d’écrire une application par la suite quand on sait que les informations sont bien là où elles doivent être.

Il faut noter que l’attribut codePays n’est pas déclaré ``not null`, ce qui signifie que l’on s’autorise à ne pas connaître le pays de production d’un film. Quand un attribut est à null, la contrainte d’intégrité référentielle ne s’applique pas. En revanche, on impose de connaître le réalisateur d’un film. C’est une contrainte forte, qui d’un côté améliore la richesse et la cohérence de la base, mais de l’autre empêche toute insertion, même provisoire, d’un film dont le metteur en scène est inconnu. Ces deux situations correspondent respectivement aux associations 0..* et 1..* dans la modélisation entité/association.

Note

On peut facilement passer un attribut de not null à null. L’inverse n’est pas vrai s’il existe déjà des valeurs à null dans la base.

Que se passe-t-il quand la violation d’une contrainte d’intégrité est détectée par le système? Par défaut, la mise à jour est rejetée, mais il est possible de demander la répercussion de cette mise à jour de manière à ce que la contrainte soit respectée. Les événements que l’on peut répercuter sont la modification ou la destruction de la ligne référencée, et on les désigne par on update et on delete respectivement. La répercussion elle-même consiste soit à mettre la clé étrangère à null (option set null), soit à appliquer la même opération aux lignes de l’entité composante (option cascade).

Voici comment on indique que la destruction d’un pays déclenche la mise à null de la clé étrangère codePays pour tous les films de ce pays.

create table Film  (idFilm integer not null,
                   titre    varchar (50) not null,
                   annee    integer not null,
                   idRealisateur    integer not null,
                   genre varchar (20) not null,
                   resume      varchar(255),
                   codePays    varchar (4),
                   primary key (idFilm),
                   foreign key (idRealisateur) references Artiste,
                   foreign key (codePays) references Pays
                      on delete set null)

Dans le cas d’une entité faible, on décide en général de détruire le composant quand on détruit le composé. Par exemple, quand on détruit un cinéma, on veut également détruire les salles; quand on modifie la clé d’un cinéma, on veut répercuter la modification sur ses salles. Dans ce cas c’est l’option cascade qui s’impose.

create table Salle  (idCinema integer not null,
                     no    integer not null,
                     capacite    integer not null,
                     primary key (idCinema, noSalle),
                     foreign key (idCinema) references Cinema
                       on delete cascade,
                       on update cascade)

L’attribut idCinema fait partie de la clé et ne peut donc pas être null. On ne pourrait donc pas spécifier ici on delete set null.

La spécification des actions on delete et on update simplifie la gestion de la base par la suite: on n’a plus par exemple à se soucier de détruire les salles quand on détruit un cinéma.

La clause check

La clause check exprime des contraintes portant soit sur un attribut, soit sur une ligne. La condition elle-même peut être toute expression suivant la clause where dans une requête SQL. Les contraintes les plus courantes sont celles consistant à restreindre un attribut à un ensemble de valeurs, comme expliqué ci-dessous. On peut trouver des contraintes arbitrairement complexes, faisant référence à d’autres tables. Nous reviendrons sur cet aspect après avoir étudié le langage d’interrogation SQL.

Voici un exemple simple qui restreint les valeurs possibles des attributs annee et genre dans la table Film.

create table Film  (idFilm integer not null,
                   titre    varchar (50) not null,
                   annee    integer not null
                    check (annee between 1890 and 2063) not null,
                   idRealisateur    integer,
                   genre varchar (20) not null
                     check (genre in ('Histoire','Western','Drame')),
                   resume      varchar(255),
                   codePays    varchar (4),
                   primary key (idFilm),
                   foreign key (idRealisateur) references Artiste
                       on delete set null,
                   foreign key (codePays) references Pays)

Au moment d’une insertion dans la table Film, ou d’une modification de l’attribut annee ou genre, le SGBD vérifie que la valeur insérée dans genre appartient à l’ensemble énuméré défini par la clause check.

Une autre manière de définir, dans la base, l’ensemble des valeurs autorisées pour un attribut – en d’autres termes, une codification imposée – consiste à placer ces valeurs dans une table et la lier à l’attribut par une contrainte de clé étrangère. C’est ce que nous pouvons faire par exemple pour la table Pays.

create table Pays (code    varchar(4) not null,
                   nom  varchar (30) default 'Inconnu' not null,
                  langue varchar (30) not null,
                  primary key (code));
 insert into Pays (code, nom, langue) values ('FR', 'France', 'Français');
 insert into Pays (code, nom, langue) values ('USA', 'Etats Unis', 'Anglais');
 insert into Pays (code, nom, langue) values ('IT', 'Italie', 'Italien');
 insert into Pays (code, nom, langue) values ('GB', 'Royaume-Uni', 'Anglais');
 insert into Pays (code, nom, langue) values ('DE', 'Allemagne', 'Allemand');
 insert into Pays (code, nom, langue) values ('JP', 'Japon', 'Japonais');

Si on ne fait pas de vérification automatique, soit avec check, soit avec la commande foreign key, il faut faire cette vérification dans l’application, ce qui est plus lourd à gérer.

Modification du schéma

La création d’un schéma n’est qu’une première étape dans la vie d’une base de données. On est toujours amené par la suite à créer de nouvelles tables, à ajouter des attributs ou à en modifier la définition. La forme générale de la commande permettant de modifier une table est:

alter table <nomTable> <action> <description>

action peut être principalement add, modify, drop ou rename et description est la commande de modification associée à action. La modification d’une table peut poser des problèmes si elle est incompatible avec le contenu existant. Par exemple passer un attribut à not null implique que cet attribut a déjà des valeurs pour toutes les lignes de la table.

Modification des attributs

Voici quelques exemples d’ajout et de modification d’attributs. On peut ajouter un attribut region à la table Internaute avec la commande:

alter table Internaute add region varchar(10)

S’il existe déjà des données dans la table, la valeur sera à null ou à la valeur par défaut. La taille de region``étant certainement insuffisante, on peut l'agrandir avec ``modify, et la déclarer not null par la même occasion:

alter table Internaute modify region varchar(30) not null

Il est également possible de diminuer la taille d’une colonne, avec le risque d’une perte d’information pour les données existantes. On peut même changer son type, pour passer par exemple de varchar à integer, avec un résultat imprévisible.

La commande alter table permet d’ajouter une valeur par défaut.

alter table Internaute add region set default 'PACA'

Enfin, on peut détruire un attribut avec drop.

alter table Internaute drop region

Création d’index

Pour compléter le schéma d’une table, on peut définir des index. Un index offre un chemin d’accès aux lignes d’une table qui est considérablement plus rapide que le balayage de cette table – du moins quand le nombre de lignes est très élevé. Les SGBD créent systématiquement un index sur la clé primaire de chaque table. Il y a deux raisons à cela;

  • l’index permet de vérifier rapidement, au moment d’une insertion, que la clé n’existe pas déjà;
  • beaucoup de requêtes SQL, notamment celles qui impliquent plusieurs tables (jointure), se basent sur les clés des tables pour reconstruire les liens. L’index peut alors être utilisé pour améliorer les temps de réponse.

Un index est également créé pour chaque clause unique utilisée dans la création de la table. On peut de plus créer d’autres index, sur un ou plusieurs attributs, si l’application utilise des critères de recherche autres que les clés primaire ou secondaires.

La commande pour créer un index est la suivante:

create [unique] index <nomIndex>  on <nomTable> (<attribut1> [, ...])

L’option unique indique qu’on ne peut pas trouver deux fois la même clé dans l’index. La commande ci-dessous crée un index de nom idxNom sur les attributs nom et prenom``de la table *Artiste*. Cet index a donc une fonction équivalente à la clause ``unique déjà utilisée dans la création de la table.

create unique index idxNom  on Artiste (nom, prenom)

On peut créer un index, cette fois non unique, sur l’attribut genre de la table Film.

create index idxGenre on Film (genre)

Cet index permettra d’exécuter très rapidement des requêtes SQL ayant comme critère de recherche le genre d’un film.

select * from Film where genre = 'Western'

Cela dit il ne faut pas créer des index à tort et à travers, car ils ont un impact négatif sur les commandes d’insertion et de destruction. À chaque fois, il faut en effet mettre à jour tous les index portant sur la table, ce qui représente un coût certain.

Pour en savoir plus sur les index, et en général sur la gestion de l’organisation des données, je vous renvoie à la seconde partie du cours disponible à http://sys.bdpedia.fr.