[Article] Bases de données relationnelles - Normalisation [News]

**fsmrel** · 23/05/2009, 22h22

Mise à jour du 14/07/2011

Bonsoir,

Voici la 3e partie de mon article sur la normalisation des bases de données, partie qui traite des quatrième, cinquième et sixième formes normales.

Comme il n’y a pas de normalisation en 7NF par projection/jointure (sauf chez les farfelus), l’article est achevé, aux quelques modifications près d’usage que je peux toujours être amené à effectuer suite à vos observations.

Bonne lecture !

Mise à jour du 12/10/10

Bonjour,

Voici la 2e partie de mon article sur la normalisation des bases de données, partie qui a trait à la 2NF, la 3NF et la BCNF. Concernant la 4NF, la 5NF et la 6NF, c’est pratiquement au point et j’espère que tout sera bouclé d’ici la fin de l’année.

J’ai aussi complété la 1re partie et adopté parfois un ton un peu polémique, par exemple en ce qui concerne l’optimisation : il peut donc y avoir des réactions, aussi ai-je mon casque lourd à portée de main...

Bonne lecture !

fsmrel

23/05/2009

Bonjour,

De la normalisation des bases de données.

La connaissance de la théorie de la normalisation est incontournable dès qu’il s’agit de structurer les données de façon rationnelle (modélisation conceptuelle et logique des données). Nous présentons ici http://fsmrel.developpez.com/basesre...normalisation/ une étude relativement approfondie mais non hermétique de ce sujet, abordé bien souvent de façon superficielle voire approximative.
Cet article traite en détail des formes normales, de la première à la cinquième. Il a été découpé en quatre parties qui seront publiées progressivement.

Merci d'avance pour vos commentaires !

fsmrel

**Philippe Vialatte** · 25/05/2009, 11h57

Bel article

(il va falloir que je me l'imprimes et que je me le lise a tête reposée...)

Tiens, ça fait plaisir, cet article va me permettre de taper encore plus fort sur les doigts de mes petits camarades

Ca me rappelle mes cours de sgbd avec Miranda il y'a quelques années (vive la fac a Sophia, le soleil, les palmiers, et les bases de données

)

Vivement la fin de relecture des 4nf et 5nf

**fsmrel** · 25/05/2009, 15h02

Bonjour,

Merci Philippe pour ce premier commentaire et pour votre compliment.

Au sujet de Serge Miranda : Le 29 septembre 1987, au Palais des congrès à Paris, Ted Codd, Chris Date animent un séminaire dont je vous laisse deviner le thème. La foule des grands jours est présente (a priori vous étiez un peu trop jeune pour vous joindre à la noble compagnie...) Serge Miranda était assis bien sagement au fond de la salle (immense est bondée). Codd s’adresse à nous à peu près en ces termes : « Hommage à Serge Miranda, dont le SGBD Campus est le seul que nous avons retenu comme étant totalement relationnel ».

**ypicot** · 26/05/2009, 21h53

Excellent papier, un des meilleurs (à mon sens) sur le sujet.

Ca ne se lit pas aussi facilement qu'un polar de la série noire, mais l'effort vaut largement le coup.

Une question bête : pourquoi ne pas aller jusqu'à la 6NF ? Est-ce parce que son explication est franchement mathématique ?

Yvan

**fsmrel** · 27/05/2009, 01h59

Merci à vous Yvan,

La 6NF ? J’ai hésité. En tous cas, les mathématiques ne sont pas partie prenante dans cette affaire. Quoi qu'il en soit, our le moment, je préfère m’abstenir. En effet :

1) Chris Date traite de la 5NF en 5 pages et demies (An Introduction to Database Systems, 8th edition, pages 386-391) or le sujet n’a pas la réputation d’être simple. En ce qui concerne la 6NF, si l’on tient compte des étapes préparatoires, Date a besoin de 47 pages (c'est-à-dire tout le chapitre 23 de l’ouvrage cité) pour faire le tour complet de la question, mais sans l'approfondir...

2) La 6NF présente essentiellement de l’intérêt dans le contexte des bases de données dans lesquelles le temps joue un rôle majeur (date de début, date de fin, donnée par donnée pour prendre un très grand raccourci). Mais déjà, nombre de problèmes sont résolus si au niveau du MCD on isole les données « datées » pour lesquelles la date de fin est connue (ce que Date appelle les données intervallaires de type « durant » (during) par opposition aux données pour lesquelles la date de fin n’est pas connue, dites de type « depuis » (since). Ainsi, dans votre entreprise vous avez pu faire partie de tel et tel services de telle date à telle date, mais vous êtes actuellement affecté à tel service depuis telle date, sans limitation dans le temps.

Voyez par exemple la réponse que je fais à Fayred (Historiser deux entités et leurs relations) : la date dernière date d’embauche n’est pas mêlée avec l’historique, ce qui sémantiquement parlant est sain. Au minimum, j’évacue le problème des dates marquées à NULL ou valorisées de façon vaseuse, dans le genre 31/12/9999 et dont il faut tenir compte dans les requêtes : dans les banques, assurances, organismes de retraite, etc., les développeurs SQL sont régulièrement confrontés à de tels impedimenta.

3) Traiter les données temporelles (et plus généralement intervallaires) conduit à étendre l’algèbre relationnelle, de façon significative (nouveaux opérateurs, entre autres PACK et UNPACK), généraliser tous les opérateurs classiques, Projection, Jointure, Union, Intersection, Différence, etc. Ainsi, la jointure généralisée U_JOIN de deux relations r1 et r2 est un raccourci de l’expression hermétique (si l’on n’a pas lu les 30 premières pages du chapitre 23 mentionné) :

PACK (( UNPACK r1 ON (ACL)) JOIN (UNPACK r2 ON (ACL))) ON (ACL)

Et je n’ai guère envie d’entreprendre ici la rédaction d’un paquet de pages d’explication. J’espère que vous me comprenez et compatissez.

Bref, le mieux est de se focaliser sur la modélisation des données dans leur dimension temporelle, en ayant à l'esprit les mots magiques during et since, auquel cas on peut parvenir à la 6NF (à condition de respecter la 5NF), comme M. Jourdain est parvenu à la prose sans le savoir. Et pour s’en assurer, lire et relire le fameux chapitre 23. Les plus courageux étudieront l’ouvrage commis par Date, Darwen et Lorentzos : Temporal Data and the Relational Model, lequel va encore plus en profondeur.

**ypicot** · 27/05/2009, 08h19

Merci pour cette réponse.

Elle pourrait figurer -au moins partiellement- en intro ou en conclusion dans le tuto, pour ne pas laisser le lecteur avec un sentiment d'incomplétude.

Au lieu d'un manque, cela devient "la 6NF ne sera pas traitée ici, et voici pourquoi".

Mais ce n'est qu'une suggestion.

Yvan

**fsmrel** · 27/05/2009, 18h07

D'accord Yvan, à l’occasion de la publication du prochain chapitre, j’ajouterai un petit couplet sur la 6NF, reprenant en gros mon précédent message.

**fsmrel** · 11/10/2010, 20h59

Bonjour,

Voici la 2e partie de mon article sur la normalisation des bases de données, partie qui a trait à la 2NF, la 3NF et la BCNF. Concernant la 4NF, la 5NF et la 6NF, c’est pratiquement au point et j’espère que tout sera bouclé d’ici la fin de l’année.

J’ai aussi complété la 1re partie et adopté parfois un ton un peu polémique, par exemple en ce qui concerne l’optimisation : il peut donc y avoir des réactions, aussi ai-je mon casque lourd à portée de main...

Bonne lecture !

fsmrel

Invité · 14/10/2010, 15h40

2. Définir au besoin une vue, appelons-la FACT_RGLT, pour effectivement « faciliter la communication », c'est-à-dire tout savoir sur les factures réglées.
Dans le style SQL, cette vue (parmi d'autres, selon les besoin) pourrait être :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE VIEW FACT_RGLT 
       (IdFacture, DateFacture, MontantFacture, IdClient, 
        IdReglement, DateReglement, MontantReglement)
 AS
   SELECT   x.IdFacture, x.DateFacture, x.MontantFacture, x.IdClient,
            y.IdReglement, y.DateReglement, y.MontantReglement
   FROM     FACTURE AS x JOIN REGLEMENT AS y 
              ON x.IdFacture = y.IdFacture ;

Pour des raisons de symétrie, définir aussi une vue pour tout savoir sur les factures non réglées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
CREATE VIEW FACT_NON_RGLT 
       (IdFacture, DateFacture, MontantFacture, IdClient)
  AS
   SELECT   x.IdFacture, x.DateFacture, x.MontantFacture, x.IdClient
   FROM     FACTURE AS x 
   WHERE    NOT EXISTS 
            (SELECT  ''
             FROM    REGLEMENT AS y 
             WHERE   x.IdFacture = y.IdFacture) ;

Et tant qu'à faire, pour disposer d'un jeu complet, définir une vue supplémentaire qui permette de présenter toutes les factures, qu'elles soient notées réglées ou non réglées :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
CREATE VIEW FACT_STATUT 
        (IdFacture, DateFacture, MontantFacture, IdClient, 
         IdReglement, DateReglement, MontantReglement, Statut)
  AS
    SELECT  IdFacture, DateFacture, MontantFacture, IdClient,
            IdReglement, DateReglement, MontantReglement, 'Réglé'
    FROM    FACT_RGLT  
   UNION
    SELECT  IdFacture, DateFacture, MontantFacture, IdClient,
            ' ', ' ', ' ', 'Non Réglé'
    FROM    FACT_NON_RGLT ;

Il ne s’agit certes pas du cœur de l’article, mais je me demandais s’il ne serait pas plus pertinent d’utiliser une jointure externe.

Pour la vue sur les factures non réglées, je veux bien que vous utilisiez not exists de préférence à une jointure externe, après tout, chacun ses petites manies, mais la vue permettant de présenter toutes les factures tourne au vice.
Cette vue fait l’union de 2 recherche des factures avec une jointure (ou son équivalent en not exists). Il me semble plus naturel de faire une jointure externe. Cette vue permet même de répondre aux trois problématiques (si IdReglement est null c’est qu’aucun règlement n’est associé à cette facture et donc que la facture n’a pas été réglée), il n’est pas nécessaire de multiplier les vues.
Et si cela vous semble un peut trop détourné, il est même possible de créer un champ dédié, par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

decode(y.IdReglement,null, 'Non Réglé', 'Réglé') as Regle

La vue donnerai donc :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
CREATE VIEW FACT_STATUT 
        (IdFacture, DateFacture, MontantFacture, IdClient, 
         IdReglement, DateReglement, MontantReglement, Statut)
  AS
   SELECT   x.IdFacture, x.DateFacture, x.MontantFacture, x.IdClient,
            y.IdReglement, y.DateReglement, y.MontantReglement, decode(y.IdReglement, null, 'Non Réglé', 'Réglé') as Regle
   FROM     FACTURE AS x LEFT JOIN REGLEMENT AS y 
              ON x.IdFacture = y.IdFacture ;

Je critique un peu, mais c'est juste pour alimenter le débat.
Bravo pour l’article.

**fsmrel** · 15/10/2010, 03h39

Bonsoir Gorwen, et merci pour vos commentaires.

Envoyé par Gorwen

Il ne s’agit certes pas du cœur de l’article, mais je me demandais s’il ne serait pas plus pertinent d’utiliser une jointure externe.

Du strict point des facilités offertes par le langage SQL, votre remarque est bien sûr recevable. Mais dans cet article, je tiens à rester le plus possible dans les clous du Modèle Relationnel de Données, lequel tient la jointure externe à l’écart parce que celle-ci a pour objet la production de résultats dans lesquels se manifeste le bonhomme NULL (contrairement à ce qui se passe avec la jointure naturelle classique). Du point de vue théorique, le résultat d’une opération relationnelle dont les opérandes sont des relvars (variables relationnelles, voyez l’annexe A.2) est une relvar qui à son tour doit pouvoir servir comme opérande d’une autre opération, c’est le principe de fermeture que le Modèle Relationnel de Données se refuse de violer. Si NULL apparaissait dans le résultat, comme NULL n’est pas une valeur, la relation correspondante (cf. annexe A.2) perdrait sa propriété d’ensemble (au sens de la théorie des ensembles), et le principe de fermeture ne s’appliquerait plus (horresco referens...)

Envoyé par Gorwen

Pour la vue sur les factures non réglées, je veux bien que vous utilisiez not exists de préférence à une jointure externe.

Puisque j’essaie de ne pas m’écarter de la théorie relationnelle, j’aurais dû en toute rigueur ne pas utiliser NOT EXISTS, mais une opération de DIFFERENCE (opérateur MINUS ou EXCEPT selon les dialectes), donc coder ainsi la vue utilisée pour les factures non réglées :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE VIEW FACT_NON_RGLT 
            (IdFacture, DateFacture, MontantFacture, IdClient)
 AS
  SELECT  x.IdFacture, x.DateFacture, x.MontantFacture, x.IdClient
  FROM    FACTURE AS x
          JOIN   
              (SELECT IdFacture
               FROM   FACTURE
               EXCEPT 
               SELECT IdFacture
               FROM   REGLEMENT
              ) AS y   
          ON  x.IdFacture = y.IdFacture ;

Si le SGBD ne propose pas l’opérateur EXCEPT (MINUS, ...) alors, toujours dans le cadre du Modèle Relationnel de Données, je préfère recommander l’emploi de NOT EXISTS.

Dans la pratique, le choix final se fera en réalité sur la base d’un prototypage des performances (déjà un EXPLAIN (DB2, ORACLE, TERADATA), ou un SHOWPLAN (SQL Server), etc.) devrait déjà permettre de fournir de bonnes indications). (Notez que la performance des applications est un sujet auquel je suis sensible, voyez par exemple le paragraphe 3.8 et l’annexe F.3).

Envoyé par Gorwen

la vue permettant de présenter toutes les factures tourne au vice

Peu importe la teneur du code encapsulé dan l’instruction CREATE VIEW. En effet, on peut le modifier à volonté, cela reste transparent pour ceux qui se servent de la vue (utilisateurs, applications). Cela dit, la vue FACT_STATUT est à considérer ici dans son acception mathématique, c'est-à-dire l’UNION de deux ensembles, celui des factures réglées et celui des factures non réglées.

J’aurais pu aussi coder directement la vue FACT_STATUT sans y faire mention des deux autres vues. Évidemment, l’utilisation d’EXCEPT et UNION fait que le code est un peu plus verbeux qu’avec un OUTER JOIN, mais comme je l’ai dit, quand on en arrive au côté pratique des choses, si d’après le prototypage des performances la jointure externe s’avère plus efficace, on pourra bien sûr la mettre en œuvre, et cela restera transparent pour les utilisateurs, le code étant encapsulé dans la vue.

Une observation quand même :

La vue que vous proposez est source de NULL pour les attributs IdReglement, DateReglement, MontantReglement : l'utilisation de la fonction COALESCE permettrait d’éviter cela et de garantir que le résultat soit un ensemble.

Un point de détail : DECODE n’est pas une fonction reconnue par la norme, il serait préférable d’utiliser par exemple CASE ou COALESCE.

De mon côté, je compléterai probablement ainsi cette partie de l’article :

Les habitués de SQL préfèreront sans doute directement coder ainsi la vue FACT_STATUT :

Code SQL :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
SELECT  x.IdFacture, x.DateFacture, x.MontantFacture, x.IdClient
      , COALESCE (CAST (y.IdReglement AS Varchar(10)), 'Néant') AS IdReglement
      , COALESCE (CAST (y.DateReglement AS Varchar(10)), 'Néant') AS DateReglement
      , COALESCE (CAST (y.MontantReglement AS Varchar(10)), 'Néant') AS MontantReglement 
      , CASE WHEN  y.IdReglement IS NULL THEN 'Non réglé' ELSE 'Réglé' END AS Statut 
FROM    FACTURE AS x LEFT JOIN REGLEMENT AS y 
          ON  x.IdFacture = y.IdFacture ;

Invité · 15/10/2010, 13h30

Du strict point des facilités offertes par le langage SQL, votre remarque est bien sûr recevable. Mais dans cet article, je tiens à rester le plus possible dans les clous du Modèle Relationnel de Données

Je m'attendais un peu à ce que vous me répondiez quelque chose comme ça, mais j'ai bien fait de poser quand même la question puisque vous avez développé votre réponse de manière fort intéressante.

Du point de vue théorique, le résultat d’une opération relationnelle dont les opérandes sont des relvars (variables relationnelles, voyez l’annexe A.2) est une relvar qui à son tour doit pouvoir servir comme opérande d’une autre opération, c’est le principe de fermeture que le Modèle Relationnel de Données se refuse de violer. Si NULL apparaissait dans le résultat, comme NULL n’est pas une valeur, la relation correspondante (cf. annexe A.2) perdrait sa propriété d’ensemble (au sens de la théorie des ensembles), et le principe de fermeture ne s’appliquerait plus (horresco referens...)

Merci pour cette explication. Donc en théorie, on fait tout ce qu'il faut pour ne pas avoir de NULL, mais en pratique le null est une notion très pratique

Un point de détail : DECODE n’est pas une fonction reconnue par la norme, il serait préférable d’utiliser par exemple CASE ou COALESCE.

Effectivement, la fonction COALESCE est ici bien plus appropriée et votre remarque sur la norme est tout à fait judicieuse.

Merci encore pour votre réponse. J'attends la suite de votre article avec impatience.