Skip to content

1 4 6 1 normalisation

Conception d'une base de données ⅕ : la normalisation

Ce chapitre est consacré la démarche de conception d'une base relationnelle. L'objectif de cette conception est de parvenir à un schéma normalisé représentant correctement le domaine applicatif à conserver en base de données.

La notion de normalisation a été introduite dans le chapitre Le Modèle relationnel. Elle s'appuie sur les notions de dépendances fonctionnelles et de clés. On peut, à l'aide de ces notions, caractériser des formes dites "normales". Peut-on aller plus loin et déterminer comment obtenir une forme normale en partant d'un ensemble global d'attributs liés par des dépendances fonctionnelles ? La première session étudie cette question. Comprendre la normalisation est essentiel pour produire des schémas corrects, viables sur le long terme.

La détermination des clés, des attributs, de leurs dépendances, relève d'une phase de conception. La méthode pratique la plus utilisée est de produire une notation entité / association. Elle ne présente pas de difficulté technique mais on constate en pratique qu'elle demande une certaine expérience parce qu'on est confronté à un besoin applicatif pas toujours bien défini, qu'il est difficile de transcrire dans un modèle formel. Les sessions suivantes présentent cette approche et des exemples commentés.

La normalisation

Supports complémentaires :

Étant donnés un schéma et ses dépendances fonctionnelles, nous savons déterminer s'il est normalisé. Peut-on aller plus loin et produire automatiquement un schéma normalisé à partir de l'ensemble des attributs et de leurs contraintes (les DFs) ?

La décomposition d'un schéma

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

Appart(idAppart, surface, idImmeuble, nbEtages, dateConstruction)

Voici les dépendances fonctionnelles. La première montre que la clé est idAppart : tous les autres attributs en dépendent.

\[idAppart \to surface, idImmeuble, nbEtages, dateConstruction\]

La seconde représente le fait que l'identifiant de l'immeuble détermine fonctionnellement le nombre d'étages et la date de construction.

\[idImmeuble \to nbEtages, dateConstruction\]

Cette relation est-elle normalisée ? Non, car la seconde DF montre une dépendance dont la partie gauche n'est pas la clé, idAppart. En pratique, une telle relation dupliquerait le nombre d'étages et la date de construction autant de fois qu'il y a d'appartements dans un immeuble.

Une idée naturelle est de prendre les dépendances fonctionnelles minimales et directes :

\[idAppart \to surface, idImmeuble\]

et

\[idImmeuble \to nbEtages, dateConstruction\]

On peut alors créer une table pour chacune. On obtient une décomposition en deux relations :

Appart(idAppart, surface, idImmeuble)
Immeuble (idImmeuble, nbEtages, dateConstruction)

On n'a pas perdu d'information : connaissant idAppart, je connais idImmeuble, et connaissant idImmeuble je connais les attributs de l'immeuble : je suis donc en mesure de reconstituer l'information initiale. En revanche, j'ai bien éliminé les redondances : les propriétés de l'immeuble ne seront énoncées qu'une seule fois.

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

Proprietaire(idAppart, idPersonne, quotePart)

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

\[idAppart, idPersonne \to quotePart\]

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

Propriétaire(idAppart, idPersonne, prénom, nom, quotePart)

La dépendance fonctionnelle \(idPersonne \to prénom, nom\) indique que cette relation n'est pas normalisée. En appliquant la même décomposition que précédemment, on obtient le bon schéma :

Propriétaire(idAppart, idPersonne, quotePart)
Personne(idPersonne, prénom, nom)

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

Occupant(idPersonne, nom, prénom, idAppart, surface)

On mélange clairement des informations sur les personnes, et d'autres sur les appartements. Plus précisément, la clé est la paire (idPersonne, idAppart), mais on a les dépendances suivantes :

  • \(idPersonne \to prénom, nom\)
  • \(idAppart \to surface\)

Un premier réflexe pourrait être de décomposer en deux relations Personne(idPersonne, prénom, nom) et Appart (idAppart, surface). Toutes deux sont normalisées, mais on perd alors une information importante, et même essentielle : le fait que telle personne occupe tel appartement. Cette information est représentée par la clé (idPersonne, idAppart). On la préserve en créant une relation Occupant (idPersonne, idAppart). D'où le schéma final :

Immeuble (idImmeuble, nbEtages, dateConstruction)
Proprietaire(idAppart, idPersonne, quotePart)
Personne (idPersonne, prénom, nom)
Appart (idAppart, surface, idImmeuble)
Occupant (idPersonne, idAppart)

Ce schéma, obtenu par décompositions successives, présente la double propriété

  • de ne pas avoir perdu d'information par rapport à la version initiale ;
  • de ne contenir que des relations normalisées.

Important L'absence de perte d'information est une notion qui est survolée ici mais qui est de fait essentielle. Maintenant que nous connaissons SQL, elle est facile à comprendre : l'opération inverse de la décomposition est la jointure, effectuée entre la clé primaire d'une table et la clé étrangère référençant cette table. Cette opération reconstitue les données avant décomposition, et elle est tellement naturelle qu'il existe un opérateur algébrique de ce nom, par exemple :

select *
from Appart natural join Immeuble

La décomposition d'une table \(T\) en plusieurs tables \(T_1, T_2, \cdots, T_n\) est sans perte d'information quand on peut reconstituer \(T\) avec des jointures \(T_1 \Join T_2 \Join \cdots \Join T_n\).

Et voilà. C'est cohérent, simple et élégant.

Algorithme de normalisation

Voici en résumé la procédure de normalisation par décomposition.

Algorithme de normalisation On part d'un schéma de relation \(R\), et on suppose donné l'ensemble des dépendances fonctionnelles minimales et directes sur \(R\). On détermine alors les clés de \(R\), et on applique la décomposition : - Pour chaque DF minimale et directe \(X \to A_1, \cdots A_n\) on crée une relation \((X, A_1, \cdots A_n)\) de clé \(X\). - Pour chaque clé \(C\) non représentée dans une des relations précédentes, on crée une relation \((C)\) de clé \(C\).

On obtient un schéma de base de données normalisé et sans perte d'information.

Nous disposons donc d'une approche algorithmique pour obtenir un schéma normalisé à partir d'un ensemble initial d'attributs. Cette approche est fondamentalement instructive sur l'objectif à atteindre et la méthode conceptuelle pour y parvenir.

Elle est malheureusement difficilement utilisable telle quelle à cause d'une difficulté rencontrée en pratique : l'absence ou la rareté de dépendances fonctionnelles "naturelles". Celles présentes dans notre schéma ont été artificiellement créées par ajout d'identifiants pour les immeubles, les occupants et les appartements. Dans la vraie vie, de tels identifiants n'existent pas si l'on n'a pas au préalable déterminé les "entités" présentes dans le schéma : Immeuble, Occupant, et Appartement. En d'autres termes, l'exemple qui précède s'appuie sur une forme de connaissance préalable qui guide à l'avance la décomposition.

La normalisation doit donc être intégrée à une approche plus globale qui "injecte" des dépendances fonctionnelles dans un schéma par identification préalable des entités (les appartements, les immeubles) et des contraintes qu'elles imposent. Le schéma est alors obtenu par application de l'algorithme.

Une approche globale

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

(titre, année, prénomMes, nomMES, annéeNaiss)

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

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

Comment trouve-t-on ces identifiants ? Par une démarche consistant à :

  • déterminer les "entités" (immeuble, personne, appartement, ou film et réalisateur) pertinents pour l'application ;
  • définir une méthode d'identification de chaque entité ; en pratique on recourt à la définition d'un identifiant artificiel (il n'a aucun rôle descriptif) qui permet d'une part de s'assurer qu'une même "entité" est représentée une seule fois, d'autre part de référencer une entité par son identifiant ;
  • définir les liens entre les entités.

Voici une illustration informelle de la méthode, que nous reprendrons ensuite de manière plus détaillée avec la notation Entité/association.

Commençons par les deux premières étapes. Quelles sont nos entités ? On va décider (il y a dans le processus de conception une part de choix, c'est sa fragilité) que nous avons des entités Film et des entités Réalisateur. Cela revient à ajouter des identifiants idFilm et idRéalisateur dans le schéma.

(idFilm, titre, année, idRéalisateur, prénom, nom, annéeNaiss)

avec les dépendances directes et minimales suivantes :

\[idFilm \to titre, année, idRéalisateur\]

et

\[idRéalisateur \to prénom, nom, annéeNaiss\]

Important. Le choix de l'identifiant est un sujet délicat. On peut arguer en effet que l'identifiant devrait être recherché dans les attributs existants, au lieu d'en créer un artificiellement. Pour des raisons qui tiennent à la rareté/fragilité des DFs "naturelles", la création de l'identifiant artificiel est la seule réellement applicable et satisfaisante dans tous les cas.

À partir de là, il reste à appliquer l'algorithme de normalisation. On obtient une table Film (idFilm, titre, année, idRéalisateur) avec une clé primaire et une clé étrangère, et une table Réalisateur (idRéalisateur, nom, prénom, annéeNaiss).

Important. Il faut veiller à ce que les schémas obtenus soient normalisés. C'est le cas ici puisque les seules DF sont celles issues de l'identifiant.

Voici un exemple pour la table des réalisateurs :

idRéalisateur nom prénom annéeNaiss
101 Scott Ridley 1937
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 pour la table des Films :

idFilm titre année idRéalisateur
1 Alien 1979 101
2 Vertigo 1958 102
3 Psychose 1960 102
4 Kagemusha 1980 103
5 Volte-face 1997 104
6 Pulp Fiction 1995 105
7 Titanic 1997 106
8 Sacrifice 1986 107

Note La valeur d'un identifiant est locale à une table. On ne peut pas trouver deux fois la même valeur d'identifiant dans une même table, mais rien n'interdit qu'elle soit présente dans deux tables différentes. On aurait donc pu "numéroter" les réalisateurs 1, 2, 3, ..., comme pour les films. Ici, nous leur avons donné des identifiants 101, 102, ..., pour clarifier les explications.

Cette représentation est correcte. Il n'y a pas de redondance des attributs descriptifs, donc toute mise à jour affecte l'unique occurrence de la donnée à modifier. D'autre part, on peut détruire un film sans affecter les informations sur le réalisateur. La décomposition n'a pas pour contrepartie une perte d'information puisque l'information initiale (autrement dit, avant la décomposition en deux tables) peut être reconstituée intégralement. En prenant un film, on obtient l'identifiant de son metteur en scène, et cette identifiant permet de trouver l'unique ligne dans la table des réalisateurs qui contient toutes les informations sur ce metteur en scène. Ce processus de reconstruction de l'information, dispersée dans plusieurs tables, peut s'exprimer avec la jointure.

Tout est dit. En maîtrisant la normalisation relationnelle et l'interrogation relationnelle, vous maîtrisez les deux méthodes fondamentales pour la création de bases de données.