Chapitre 3 : Le modèle relationnel

3.1. Introduction
Un modèle de données définit un mode de représentation de l’information selon trois
composantes :

• Des structures de données.

• Des contraintes qui permettent de spécifier les règles que doit respecter une base de
données.
• Des opérations pour manipuler les données, en interrogation et en mise à jour.

Les deux premières composantes relèvent du Langage de Définition de Données (DDL)

dans un SGBD. Le DDL 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 (DML) 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épendante 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, venant après les modèles hiérarchique et réseau, 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, ce qui constitue l’essentiel du
schéma.

3.2. 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, comme sur la figure.1. Une relation a donc 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

1
R.M.MBALA 2020/2021
valeurs d’un certain domaine (chaînes de caractères, nombres). Enfin on constate que chaque
ligne (ou tuple) correspond à une entité (ici des films).

Titre Année Genre

Alien 1979 Science-Fiction
Vertigo 1958 Suspense
Volte-face 1997 Thriller
Pulp Fiction 1995 Policier
Tableau 1: Une relation

Un schéma relationnel est constitué d’un ensemble de schémas de relations qui

décrivent, à l’aide des éléments présentés informellement ci-dessus (domaines, attributs,
noms de relation) le contenu d’une relation. Le schéma de la relation de la figure 1 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 (voir section
4.3), mais nous nous contenterons pour l’instant de la description ci-dessus. 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 est interdit en relationnel de manipuler des valeurs instances de
graphes, de listes, d’enregistrements, etc. En d’autres termes le système de types est figé et
fourni par le système.
Attributs
Les attributs nomment les colonnes d’une relation. Ils 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. Le nom d’un attribut peut apparaître dans plusieurs
schémas de relations.
Schéma de relation
Un schéma de relation est simplement un nom suivi de la liste des attributs, chaque
attribut étant associé à son domaine. La syntaxe est donc :
R (A1 : D1, A2 : D2, …, An : Dn)

2
R.M.MBALA 2020/2021
où les Ai sont les noms d’attributs et les Diles domaines. L’arité d’une relation est le nombre
de ses attributs
On peut trouver dans un schéma de relation plusieurs fois le même domaine, mais
une seule fois un nom d’attribut. Le domaine peut être omis en phase de définition.
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 𝐷1 × 𝐷2 × … × 𝐷𝑛 entre des domaines D1, …, Dnest
l’ensemble de tous les tuples (v1, …, vn) où 𝑣𝑖 ∈ 𝐷𝑖 .
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 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 relation comme un ensemble (au sens mathématique) a quelques
conséquences importantes :
• l’ordre des lignes n’a pas d’importance car il n’y a pas d’ordre dans un ensemble ;
• 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 « case vide » dans la table, donc toutes les valeurs de tous les attributs
sont toujoursconnues ;
Dans la pratique les choses sont un peu différentes : nous y reviendrons.
Clé d’une relation
La clé d’une relation est le plus petit sous-ensemble des attributs qui permet
d’identifier chaque ligne de manière unique. Comme on a vu que deux lignes sont toujours
différentes, l’ensemble de tous les attributs est lui-même une clé mais on peut pratiquement
toujours trouver un sous-ensemble qui satisfait la condition. Pour distinguer la clé, nous
mettrons le (ou les) attribut(s) en gras.
Film (titre, année, genre)
Le choix de la clé 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 : nous y reviendrons.

3
R.M.MBALA 2020/2021
Tuples
Un tuple est une liste de n valeurs (v1, …, vn) où chaque valeur vi est la valeur d’un
attribut Ai de domaine Di : 𝑣𝑖 ∈ 𝐷𝑖 . Exemple. (’Cyrano’, 1992, ’Rappeneau’).
Un tuple est donc simplement une ligne dans la représentation d’une relation sous forme de
table. En théorie, on connaît les valeurs de tous les attributs du tuple.
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 relations qui constituent la base. En revanche le choix de ces relations est un
problème difficile car il détermine en grande partie les caractéristiques, qualités de la base :
performances, exactitude, exhaustivité, disponibilité des informations, etc. Un des aspects
importants de la théorie des bases de données relationnelles consiste précisément à définir
ce qu’est un « bon » schéma et propose des outils formels pour y parvenir.
En pratique, on procède d’une manière moins rigoureuse mais plus accessible, en
concevant le schéma à l’aide d’un modèle de données « conceptuel », puis en transcrivant le
schéma conceptuel obtenu 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 de la figure 1.

3.3. 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 structure (relations). Logiquement, entités et associations
seront donc toutes deux transformées en relations. 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 relations.
Le choix de la clé d’une relation est un problème central dans la conception de schéma.

4
R.M.MBALA 2020/2021
 Figure 1: Le schéma de la base de données Films

3.3.1. Règles générales

Nous allons considérer dans un premier temps que la clé est définie, pour chaque type
d’entité E, par un identifiant abstrait, idE.
Règles de passage : entités
Rappel : le schéma d’une relation est constitué du nom de la relation, suivi de la liste
des attributs.
Alors, pour chaque entité du schéma E/A:
1. On crée une relation de même nom que l’entité.
2. Chaque propriété de l’entité, y compris l’identifiant, devient un attribut de la relation.
3. Les attributs de l’identifiant constituent la clé de la relation.
Exemple 3.1 À partir du schéma E/A de la figure 1, à l’exception des entités concernant les
cinémas, les salles et les horaires, on obtient les relations suivantes :
• 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)
On peut remarquer que l’on a perdu pour l’instant tout lien entre les relations.

5
R.M.MBALA 2020/2021
Règles de passage : associations de un à plusieurs
Soit une association de un à plusieurs1 entre A et B Le passage au modèle logique suit les
règles suivantes :
1. On crée les relations RA et RB correspondant respectivement aux entités A et B.
2. L’identifiant de B devient un attribut de RA.
L’idée est qu’une occurrence de A « référence » l’occurrence de B qui lui est associée à
l’aide d’une clé étrangère. Cette référence se fait de manière unique et suffisante à l’aide de
l’identifiant.
Exemple 3.2 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 soulignées.
• Film (idFilm, titre, année, genre, résumé, idArtiste, codePays)
• Artiste (idArtiste, nom, prénom, annéeNaissance)
• Pays (code, nom, langue)
Le rôle précis tenu par l’artiste dans l’association disparaît. 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 n’empêche cependant de 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 la 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 idMES.
Film (idFilm, titre, année, genre, résumé, idMES)
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 si
on ne peut avoir qu’unartiste dont l’id est 2 dans la table Artiste, en revanche rien n’empêche
cet artiste 2 de figurer plusieursfois dans la colonne idMES de la table Film. On a donc bien
l’équivalent de l’association un à plusieursélaborée dans le schéma E/A.

1
Il s’agit ici des cardinalités maximales qui déterminent pour l’essentiel le schéma relationnel obtenu.

6
R.M.MBALA 2020/2021
 idFilm titre année genre idMES codePays
100 Alien 1979 Science Fiction 1 US
101 Vertigo 1958 Suspense 2 US
102 Psychose 1960 Suspense 2 US
103 Kagemusha 1980 Drame 3 JP
104 Volte-face 1997 Action 4 US
105 Van Gogh 1991 Drame 8 FR
106 Titanic 1997 Drame 6 US
107 Sacrifice 1986 Drame 7 FR
Tableau 3: La table Film

idArtiste nom prénom annéeNaiss

1 Scott Ridley 1943
2 Hitchcock Alfred 1899 code nom langue
3 Kurosawa Akira 1910
US Etats Unis anglais
4 Woo John 1946
FR France français
5 Cameron James 1954
JP Japon japonais
6 Tarkovski Andrei 1932 Tableau 4: Table Pays
7 Pialat Maurice 1925
Tableau 2: La table Artiste
Règles de passage : associations avec type entité faible
Une entité faible est toujours identifiée par rapport à une autre entité. C’est le cas par
exemple de l’association en Cinéma et Salle (voir chapitre 2). 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. Onutilise 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.
Exemple 3.3 Voici le schéma obtenu pour représenter l’association entre les types d’entité
Cinéma et Salle. On note que l’identifiant d’une salle est constitué de l’identifiant du cinéma
(ici on a considéré que le nom du cinéma suffisait à l’identifier), et d’un numéro
complémentaire permettant de distinguer les salles au sein d’un même cinéma. La clé
étrangère est donc une partie de la clé primaire.

7
R.M.MBALA 2020/2021
 • Cinéma (nomCinéma, numéro, rue, ville)
• Salle (nomCinéma, no, capacité)

Règles de passage : associations binaires de plusieurs à plusieurs

Soit une association binaire n-m entre A et B
1. On crée les relations RA et RB correspondant respectivement aux entités A et B.
2. On crée une relation RA-B pour l’association.
3. La clé de RA et la clé de RB deviennent des attributs de RA-B.
4. La clé de cette relation est la concaténation des clés des relations RA et RB.
5. Les propriétés de l’association deviennent des attributs de RA-B
Exemple 3.4 Toujours à partir du schéma Officiel des spectacles, on obtient la table Rôle
représentant l’association entre les films et les acteurs.
• Film (idFilm, titre, année, genre, résumé, idMES, 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é, idMES, codePays)
• Internaute (email, nom, prénom, région)
• Notation (email, idFilm, note).
Les tables suivantes montrent 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 relations. 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, ce qui n’aurait pas de sens. 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).

idFilm titre année genre idMES codePays

20 Impitoyable 1992 Western 100 US
21 Ennemi d’état 1998 Action 102 US
Tableau 5: La table Film

8
R.M.MBALA 2020/2021
idArtiste nom prénom annéeNaiss idFilm idActeur rôle
100 Eastwood Clint 1930 20 100 William Munny
101 Hackman Gene 1930 20 101 Little Bill
102 Scott Tony 1930 21 101 Bril
103 Smith Will 1968 21 103 Robert Dean
Tableau 7: La table Artiste Tableau 6: La table Role

On peut remarquer finalement 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 :
1. l’attribut idFilm fait référence à une ligne de la table Film (un film) ;
2. 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 dont un exemple est donné ci-dessous. 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.

idFilm titre année genre idMES codePays

100 Alien 1979 Science Fiction 1 US
101 Vertigo 1958 Suspense 2 US
102 Psychose 1960 Suspense 2 US
103 Kagemusha 1980 Drame 3 JP
104 Volte-face 1997 Action 4 US
105 Van Gogh 1991 Drame 8 FR
106 Titanic 1997 Drame 6 US
107 Sacrifice 1986 Drame 7 FR
Tableau 9: La table Film

email nom prénom annéeNaiss

[email protected] Doe John 1945
[email protected] Fogg Phileas 1965
Tableau 8: La table Internaute

9
R.M.MBALA 2020/2021
idFilm email note
100 [email protected] 4
104 [email protected] 3
100 [email protected] 5
107 [email protected] 2
106 [email protected] 5
Tableau 10: La table Notation

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 gérés par l’application. 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. Il est important de bien
comprendre ce mécanisme pour maîtriser la construction de bases de données qui ne
nécessiteront 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 (nomCinéma, numéro, rue, ville)
• Salle (nomCinéma, no, capacité)
• Film (idFilm, titre, année, genre, résumé, idMES, codePays)
• Horaire (idHoraire, heureDébut, heureFin)
• Séance (idFilm, nomCinéma, noSalle, idHoraire, tarif)

idCinéma no capacité
idHoraire heureDébut heureFin
Le Rex 1 200
1 14 16
Kino 1 130
2 16 18
Le Rex 2 180
Tableau 11: La table Horaire
Tableau 12: La table Salle

10
R.M.MBALA 2020/2021
idFilm titre année genre idMES codePays
100 Alien 1979 Science Fiction 1 US
101 Vertigo 1958 Suspense 2 US
102 Psychose 1960 Suspense 2 US
103 Kagemusha 1980 Drame 3 JP
104 Volte-face 1997 Action 4 US
105 Van Gogh 1991 Drame 8 FR
106 Titanic 1997 Drame 6 US
107 Sacrifice 1986 Drame 7 FR
Tableau 13: La table Film

idFilm nomCinéma noSalle idHoraire tarif

103 Le Rex 2 1 23
103 Le Rex 2 2 56
100 Kino 1 2 45
102 Le Rex 2 1 50
Tableau 14: La table Séance

Donc, la relation 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. On autorise alors
une base comme celle du tableau 14.On ne peut pas trouver deux fois le même triplet
constituant la clé.
Maintenant on s’aperçoit que la même salle présente deux films différents au même
horaire. Si on souhaite éviter cette situation, la clé devient (nomCiné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. Ces
clés peuvent être spécifiées avec la clause UNIQUE du langage SQL.

3.3.2. Retour sur le choix des identifiants

Il est préférable en général de choisir un identifiant « neutre » qui ne soit pas une
propriété de l’entité. En effet :

11
R.M.MBALA 2020/2021
 1. Chaque valeur de l’identifiant doit caractériser de manière unique une occurrence.
Exemple : titre pour la relation Film ou nom pour la relation Acteur ne sont clairement
pas des bons choix.
2. Si on utilise un ensemble de propriétés comme identifiant, la référence à une
occurrence est très lourde. Exemple : la clé de Cinéma pourrait être (nom, rue, ville).
3. L’identifiant sert de référence externe et ne doit donc jamais être modifiable (il
faudrait répercuter les mises à jour).
L’inconvénient de l’identifiant « neutre » est qu’il ne donne pas d’indication sur
l’occurrence qu’il réfère. Par exemple, quand on consulte la table Séance, on ne sait pas dire
de quel film il s’agit sans aller rechercher la ligne de la table Film correspondant à l’identifiant
du film.
En admettant – pour un instant – que le titre identifie de manière unique un film, le
schéma de la tableFilm devient :
Film (titre, année, genre, résumé, idMES, codePays)
La relation Séance a alors pour schéma :
Séance (titreFilm, nomCinéma, noSalle, idHoraire, tarif)
ce qui permet d’obtenir le titre du film directement, comme le montre l’instance ci-dessous.

titreFilm nomCinéma noSalle idHoraire tarif

Kagemusha Le Rex 2 1 23
Kagemusha Le Rex 2 2 56
Alien Kino 1 2 45
Psychose Le Rex 2 1 50
Tableau 15: La table Séance, avec le titre du film

Un problème du schéma ci-dessus est que la référence à une ligne de la table Séance
devient compliquée, et donc peu performante. Il faut veiller à limiter le nombre de champs
constituant une clé car l’expression des requêtes est plus lourde, et leur exécution peut être
ralentie par la taille des index.

3.3.3. Dénormalisation du modèle logique

Le terme de dénormalisation s’applique à un non-respect des règles énoncées
précédemment, avec deux objectifs principaux :

12
R.M.MBALA 2020/2021
 1. Simplifier le schéma relationnel en réduisant le nombre d’éléments qui le composent
(fichiers ou segments ou records ou relation).
2. Faciliter l’accès aux données en introduisant un certain degré de redondance.
Ces techniques reviennent à introduire des anomalies dans le schéma. Il faut donc
systématiquement comparer le gain attendu avec les risques courus !
Suppression de relations
On peut supprimer les entités qui portent peu d’attributs en les déplaçant vers une
autre relation.
Exemple : si le schéma de Cinema est simplement Cinéma (nom, adresse), on peut supprimer
la relation et placer l’adresse dans Salle.
Salle (nomCinéma, noSalle, adresse)
L’adresse d’un cinéma est dupliquée autant de fois qu’il y a de salles. Cette option
implique une perte de place due à la redondance, un effort de saisie supplémentaire, et des
risques d’incohérences. Elle ne peut être valable que tant qu’il n’y a pas d’attributs à ajouter
pour qualifier un cinéma. Quand ce sera le cas, il faudra finalement se décider à (1) créer la
relation Cinéma et (2) supprimer les attributs mal placés dans Salle.
Autre exemple : il peut paraître inutile de créer Horaire pour gérer un couple d’heures.
On peut alors placer l’horaire dans la relation Séance. Afin de permettre l’existence de
plusieurs lignes avec le même film et la même salle, il faut introduire l’attribut heureDébut
dans la clé. On obtient le schéma suivant.
Séance (idFilm, nomCinema, noSalle, heureDébut, heureFin, tarif)
Cette variante présente peu d’inconvénients. On peut tout juste citer le fait qu’il y a
duplication de certains horaires, et que la gestion contraintes (heure début<heure fin) doit
être gérée pour plus de lignes. En fait la création d’un type d’entité Horaire ou Date, même si
elle se justifie en théorie, présente plus d’inconvénients que d’avantages. En pratique, on
place toujours un attribut horaire ou date dans le schéma de la relation.
Introduction de redondance
En principe il faut éviter les redondances dans une base de données. Donc une
information est représentée soit explicitement (elle figure une fois et une seule), soit
implicitement (elle peut être déduite ou calculée).
L’accès à une information peut cependant être long et/ou compliqué, et justifier
l’introduction de redondances.

13
R.M.MBALA 2020/2021
Par exemple :
• à partir de la table Séance, il faut consulter Salle puis Cinéma pour connaître l’adresse
du cinéma. ;
• il faut faire un calcul pour obtenir le nombre de salles d’un cinéma.
En pratique on peut être amené à introduire (prudemment) des redondances. Les
problèmes précédents pourraient ainsi se résoudre de la manière suivante :
• ajout de l’adresse du cinéma dans la table Séance.
Séance (idFilm, nomCinema, noSalle, idHoraire, adresse).
Le risque peut être considéré comme mineur car l’adresse d’un cinéma change
rarement.
• ajout du nombre de salles dans la relation Cinéma.
Cinéma (nomCinema, numéro, rue, ville, nbSalles).
Il faut mettre à jour nbSalles quand une salle est ajoutée ou supprimée d’un
cinéma. On peut considérer que cela arrive rarement, et que la redondance est donc
sans grand danger.
• ajout du nombre de rôles tenus dans la table Artiste.
Artiste (idArtiste, nom, prénom, annéeNaissance, nbRôles)
Il faut mettre à jour nbRôles quand un artiste obtient un nouveau rôle. Cela arrive
fréquemment, et le risque induit par la redondance est alors important.
L’introduction de redondances présente principalement le danger d’introduire des
incohérences dans la base. On peut utiliser le mécanisme des triggers pour effectuer
automatiquement la répercussion de la modification d’une donnée sur ses autres versions
présentes dans la base.

3.4. Le langage de définition des données

Cette partie 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 logique comprend essentiellement deux parties : d’une part la
description des tables et de leur contenu, d’autre part les contraintes qui portent sur les

14
R.M.MBALA 2020/2021
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ées 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 physique.

3.4.1. Types SQL

La norme SQL ANSI propose un ensemble de types qui sont donnés dans le tableau 4.1. 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 PRECISION Flottants double précision 8 octets
REAL Synonyme de FLOAT 4 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 ≤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
Tableau 16: Quelques Types SQL ANSI

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

15
R.M.MBALA 2020/2021
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, mais la
taille du stockage n’est pas spécifiée par la norme. Il existe deux variantes du type INTEGER:
SMALLINT et BIGINT. Ces types différents par la taille utilisée pour le stockage : voir le
tableau 16.
Le type DECIMAL (M, D) correspond à un numérique de taille maximale M, avec
un nombre de décimales fixé à D. Le type NUMERIC est un synonyme pour 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 propres à 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é. Le type REAL est un synonyme pour DOUBLE.

Caractères et chaînes de caractères

Les deux types principaux de la norme ANSI, disponibles dans la plupart des SGBD
relationnels, 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 pour le second VARCHAR(M).
La différence essentielle entre les deux types 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.

16
R.M.MBALA 2020/2021
 Quand on veut stocker des chaînes de caractères très longues (des textes, voire des
livres), 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 BLOB(pour Binary Long Object) ou LONG.

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 stocke les informations « heure », « minute » et « seconde ».
L’affichage se fait par défaut au format HH:MM:SS. Le type DATETIME permet de
combiner une date et un horaire, l’affichage se faisant au format AAAA-MM-JJ HH:MM:SS.

3.4.2. Création des tables

La commande principale est CREATE TABLE dont la syntaxe est la suivante :
CREATE TABLE nom_table (nom_colonne1
Domaine1(taille)[option1],
Nom_colonne2 Domaine2(taille)[option2],
…
Nom_colonneN DomaineN(taille)[optionN]) ;
Voici la commande de création de la table Internaute.
CREATE TABLE Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,
prenom VARCHAR (20),
motDePasse VARCHAR (60) NOT NULL,
anneeNaiss DECIMAL (4)) ;
La syntaxe se comprend aisément. La seule difficulté est de choisir correctement le
type de chaque attribut.
Le NOT NULL dans la création de table Internaute indique que l’attribut
correspondant doit toujours avoir une valeur.

17
R.M.MBALA 2020/2021
 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’option NOT NULL oblige à toujours indiquer une valeur. 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. Si les valeurs à NULL sont autorisées, il faudra en tenir compte
quand on interroge la base. Cela peut compliquer les choses, voire donner des résultats
surprenants : il est préférable de forcer les attributs important à avoir une valeur.
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 Cinéma (nom VARCHAR (50) NOT NULL,
adresse VARCHAR (50) DEFAULT ’Inconnue’) ;
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 qui peut indiquer la date du jour).

3.4.3. Les 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:
1. Un attribut doit toujours avoir une valeur. C’est la contrainte NOT NULL vue
précédemment.
2. Un attribut (ou un ensemble d’attributs) constitue(nt) la clé de la relation.

18
R.M.MBALA 2020/2021
 3. Un attribut dans une table est lié à la clé primaire d’une autre table (intégrité
référentielle).
4. La valeur d’un attribut doit être unique au sein de la relation.
5. 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.

Clés d’une table

Une clé est un attribut (ou un ensemble d’attributs) qui identifie(nt) de manière
unique un tuple d’une relation. 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 assez 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 Internaute (email VARCHAR (50) NOT NULL,
nom VARCHAR (20) NOT NULL,
prenom VARCHAR (20),
motDePasse VARCHAR (60) NOT NULL,
anneeNaiss INTEGER,
PRIMARY KEY (email)) ;
Il devrait toujours y avoir une PRIMARY KEY dans une table pour ne pas risquer
d’insérer involontairement deux lignes strictement identiques. Une clé peut être constituée
de plusieurs attributs :
CREATE TABLE Notation (idFilm INTEGER NOT NULL,
email VARCHAR (50) NOT NULL,
note INTEGER DEFAULT 0,
PRIMARY KEY (titre, 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.
On peut également spécifier que la valeur d’un attribut est unique pour l’ensemble de
la colonne. Cela permet d’indiquer des clés secondaires. On peut par exemple indiquer que
deux artistes ne peuvent avoir les mêmes nom et prénom avec l’option UNIQUE.

19
R.M.MBALA 2020/2021
CREATE TABLE Artiste(id INTEGER NOT NULL,
nom VARCHAR (30) NOT NULL,
prenom VARCHAR (30) NOT NULL,
anneeNaiss INTEGER,
PRIMARY KEY (id),
UNIQUE (nom, prenom));
Il est facile de supprimer cette contrainte 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.
Voici un autre exemple d’utilisation d’une clé secondaire : on indique ci-dessous qu’on ne
peut pas trouver deux cinémas à la même adresse. Ce deuxième exemple montre que l’on
peut placer une contrainte comme UNIQUE sur la ligne de l’attribut auquel elle se rapporte.
Ce n’est bien entendu possible que quand cette contrainte ne concerne qu’un seul attribut.
CREATE TABLE Cinema(nom VARCHAR (30) NOT NULL,
adresse VARCHAR(50) UNIQUE,
PRIMARY KEY (nom)) ;
La clause UNIQUE ne s’applique pas aux valeurs NULL: il peut y avoir plusieurs cinémas
d’adresse inconnue. En revanche le nom du cinéma est obligatoire (clause NOT NULL) et il
est unique (clause PRIMARY KEY).

Clés étrangères
La norme SQL ANSI 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,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (idFilm),
FOREIGN KEY (idMES) REFERENCES Artiste,
FOREIGN KEY (codePays) REFERENCES Pays);
La commande
FOREIGN KEY (idMES) REFERENCES Artiste

20
R.M.MBALA 2020/2021
indique que idMES 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 idMES
correspond bien à une ligne de Artiste. Ces modifications sont :
• l’insertion dans Film avec une valeur inconnue pour idMES;
• la destruction d’un artiste ;
• la modification de id dans Artiste ou de idMES dans Film.
En d’autres termes le lien entre Film et Artiste est toujours valide. Cette contrainte est
importante pour garantir 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 idMES n’est pas déclaré NOT NULL, ce qui signifie que l’on
s’autorise à ne pas connaître le metteur en scène d’un film. Quand un attribut est à NULL, la
contrainte d’intégrité référentielle ne s’applique pas.
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 metteur en scène déclenche la mise à
NULL de la clé étrangère idMES pour tous les films qu’il a réalisé.
CREATE TABLE Film (titre VARCHAR (50) NOT NULL,
annee INTEGER NOT NULL,
idMES INTEGER,
codePays INTEGER,
PRIMARY KEY (titre),
FOREIGN KEY (idMES) REFERENCES Artiste
ON DELETE SET NULL,
FOREIGN KEY (codePays) REFERENCES Pays);

21
R.M.MBALA 2020/2021
 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.
CREATE TABLE Salle (nomCinema VARCHAR (30) NOT NULL,
no INTEGER NOT NULL,
capacite INTEGER,
PRIMAR KEY (nomCinema, no),
FOREIGN KEY (nomCinema) REFERENCES Cinema
ON DELETE CASCADE
ON UPDATE CASCADE) ;
Il est important de noter que nomCinema 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 considérablement 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.

Énumération des valeurs possibles avec CHECK

La norme SQL ANSI comprend une option CHECK (condition) pour exprimer 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 relations. 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 (titre VARCHAR (50) NOT NULL,
annee INTEGER
CHECK (annee BETWEEN 1890 AND 2000) NOT NULL,
genre VARCHAR (10)
CHECK (genre IN(’Histoire’,’Western’,’Drame’)),
idMES INTEGER,

22
R.M.MBALA 2020/2021
 codePays INTEGER,
PRIMARY KEY (titre),
FOREIGN KEY (idMES) REFERENCES Artiste,
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) DEFAULT 0 NOT NULL,
nom VARCHAR (30) NOT NULL,
langue VARCHAR (30) NOT NULL,
PRIMARY KEY (code)) ;
INSERT INTO Pays VALUES (0, ’Inconnu’, ’Inconnue’);
INSERT INTO Pays VALUES (1, ’France’, ’Français’);
INSERT INTO Pays VALUES (2, ’USA’, ’Anglais’);
INSERT INTO Pays VALUES (3, ’Allemagne’, ’Allemand’);
INSERT INTO Pays VALUES (4, ’Angleterre’, ’Anglais’);
…
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.

3.4.4. 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
où ACTION peut être principalement ADD, MODIFY, DROP ou RENAME, et
descriptionest 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

23
R.M.MBALA 2020/2021
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 NULLpar 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.
L’option ALTER TABLEpermet d’ajouter une valeur par défaut.
ALTER TABLE Internaute ALTER 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 SGBDL créent
systématiquement un index sur la clé primaire de chaque table. Il y a deux raisons à cela ;
1. l’index permet de vérifier rapidement, au moment d’une insertion, que la clé n’existe
pas déjà ;
2. beaucoup de requêtes SQL, notamment celles qui impliquent plusieurs tables
(jointures), 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.

24
R.M.MBALA 2020/2021
 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 [,
...]) ;
La clause UNIQUE indique qu’on ne peut pas trouver deux fois la même clé. 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.

25
R.M.MBALA 2020/2021
 3.5. Exercices

Exercice 3.1 La relation du tableau 17 est-elle conforme à la définition ? Si non, citez les
anomalies.

titre année metteurEnScène acteur

‘Cyrano’ 1992 ’Rappeneau’ ’Depardieu’, ’Perez’
‘Les oiseaux’ 1963 ’Hitchcock’ ’Taylor’
’Titanic’ ’Cameron’ ’DiCaprio’
’Les oiseaux’ 1963 ’Hitchcock’ ’Taylor’
Tableau 17: Une relation

Exercice 3.2 Donnez le schéma relationnel de la base de données « Centre médical » décrite
par un schéma E/A dans le précédent TD. Pour chaque table, il faut indiquer précisément, à
l’aide de la syntaxe vue en cours :
• La clé primaire.
• Les clés étrangères.

Exercice 3.3 Même exercice que précédemment, pour l’application “Tournoi de tennis” et pour
le « système d’information d’un quotidien ».

Exercice 3.4 Même exercice, portant sur les schémas SOCIETE, DIRECTEUR,
ORDINATEUR, UTILISATEUR, ORDINATEUR, DISQUE DUR que vous avez étudié
dans la séance consacrée au schéma E/A.
Cette fois, il est demandé de spécifier, pour chaque clé étrangère, la stratégie en cas de mise-
à-jour ou de destruction de la ligne référencée (clauses ON UPDATEet ON DELETEvues en
cours).

Exercice 3.5 Même exercice, pour le schéma “Cours”. Donner les spécifications complètes (clés
primaires et étrangères, NOT NULL, clauses UNIQUE, etc).

Exercice 3.6 Des éditeurs se réunissent pour créer une Base de Données sur leurs publications
scientifiques.

26
R.M.MBALA 2020/2021
Dans de telles publications, plusieurs auteurs se regroupent pour écrire un livre en se
répartissant les chapitres à rédiger. Après discussion, voici le schéma obtenu :
1. Livre (titreLivre, année, éditeur, chiffreAffaire),
2. Chapitre (titreLivre, titreChapitre, nbPages),
3. Auteur (nom, prénom, annéeNaissance),
4. Redaction (nomAuteur, titreLivre, titreChapitre)
Les clés primaires sont en gras, mais les clés étrangères ne sont pas signalées.
1. Donnez le schéma Entité/Association correspondant au schéma relationnel.
2. Donnez les ordres CREATE TABLEpour le schéma, en spécifiant soigneusement clés
primaires et étrangères avec la syntaxe SQL. Le type des données est secondaire:
choisissez ce qui vous semble logique.
3. Sur quelles clés étrangères devrait-on mettre l’option ON DELETE CASCADE?

Exercice 3.7 On trouve dans un SGBD relationnel les relations ci-dessous. Les clés primaires
sont en gras, les clés étrangères ne sont pas signalées.
• Immeuble (nom, adresse, nbEtages, annéeConstruction, nomGérant)
• Appart (nomImm, noApp, type, superficie, étage)
• Personne (nom, prenom, age, codeProfession)
• Occupant (nomImm, noApp, nomOccupant, annéeArrivée)
• Propriété (nomImm, nomPropriétaire, quotePart)
• TypeAppart (code, libellé)
• Profession (code, libellé)
Identifier les clés étrangères dans chaque relation, et reconstruire le schéma E/A.

27
R.M.MBALA 2020/2021