Normalisation : « Aux pays des bières »

Aucune forme normale

En partant de notre première structuration des informations dans une seule table nous souhaiterions obtenir la représentation suivante des informations :

Avec cette représentation, quel serait le résultat de la requête SQL suivante :

SELECT * FROM barsdumonde
WHERE bar='Bar du Coin';

Est-il possible de retrouver facilement les bières servies au “Bar du Coin” ?

Cette modélisation ne permet donc pas de manipuler facilement les informations.

Cette représentation de l’information contredit la première forme normale (1NF) :

• tous les attributs de la relation doivent avoir des valeurs simples (contiennent une valeur atomique)

On doit donc dupliquer toutes les informations regroupées par bar.

NB : Ce genre de regroupement pourra toujours être réalisé ultérieurement (GROUP BY)

Première forme normale

La représentation de notre premier modèle de données en 1NF sera donc :

Cependant du fait de la duplication de l’information concernant les bars à chaque bière qu’ils servent, cette modélisation présente de nombreux inconvénients :

• duplication de l’information concernant les bars pour chaque bière qu’ils servent.

• anomalie d’insertion :

  • si on veut insérer une nouvelle bière il faudra l’associer à un bar (id_bar).

    INSERT INTO barsdumonde VALUES(4,8,NULL,NULL,NULL,'Pilsner Urquell','Blonde','Tchéquie',NULL);
    -- ou
    INSERT INTO barsdumonde VALUES(4,8,'Bar des Amis','France','Europe','Pilsner Urquell','Blonde','Tchéquie',NULL);
     -- ou
    INSERT INTO barsdumonde VALUES(1,8,'Bar du Coin','France','Europe','Pilsner Urquell','Blonde','Tchéquie',1250);
    

• anomalie de mises à jour :

  • rebaptiser la 'Budweiser' produite en ''Tchéquie' en 'Budwar' implique de parcourir tous les éléments de l’ensemble pour faire la mise à jour :

    UPDATE barsdumonde SET biere='Budwar' WHERE biere='Budweiser' AND origine='Tchéquie'
    -- ou
    UPDATE barsdumonde SET biere='Budwar'
    WHERE  id_biere IN (
                        SELECT id_biere
                        FROM barsdumonde
                        WHERE biere='Budweiser' AND origine='Tchéquie'
                       );
    

• anomalie de suppression :

  • enlever le 'Bar du Coin' de l’ensemble implique de perdre toute l’information sur les bières qu’il servait.

    DELETE FROM barsdumonde WHERE bar='Bar du Coin';
    

Tests d’anomalies sur la base en 1NF :

Il faut donc structurer la base de données de manière à répartir les informations concernant les bars et les bières dans différents ensembles.

Cependant à partir de la répartition de l’information dans ces nouveaux ensembles, il faudra pouvoir reconstituer les informations présentes dans le modèle initial.

Deuxième forme normale

Pour mieux structurer notre modèle en 1NF on doit vérifier s’il respecte la deuxième forme normale (2NF) :

• il n’existe pas de partie de clé (sous-clé) qui soit une clé pour un attribut non-clé

Autrement dit : Toute la clé est nécessaire

Dans notre modèle d’origine :

• \(barsdumonde(\underline{id\_bar,id\_biere},bar,pays,continent,biere,couleur,origine,stock)\)

La clé de la relation est constitué des attributs : id_bar,id_biere

Or le numéro du bar suffit à retrouver les informations sur le bar.

• \(id\_bar \rightarrow bar,pays,continent\)

On peut donc décomposer notre relation de départ en appliquant le théorème de Heath :

• \(barsdumonde(\underline{id\_bar},bar,pays,continent)\)
• \(bieresdumonde(\underline{id\_bar,id\_biere},biere,couleur,origine,stock)\)

Cependant notre structuration n’est pas encore en 2NF.

En effet il n’y a pas besoin de connaître le numéro du bar pour retrouver les informations sur une bière.

• \(id\_biere \rightarrow biere,couleur,origine\)

On peut donc à nouveau décomposer notre relation bieresdumonde en appliquant le théorème de Heath :

• \(bieresdumonde(\underline{id\_biere},biere,couleur,origine)\)
• \(services(\underline{id\_bar,id\_biere},stock)\)

Cette structuration permet d’éliminer de la redondance d’information et de retrouver les informations initiales par jointure naturelle :

• \(\Join_{[services.id\_biere=bieresdumonde.id\_biere]}(bieresdumonde,\Join_{[services.id\_bar=barsdumonde.id\_bar]}(services,bars))\)

Troisième forme normale

Il subsiste toujours de la redondance d’information dans notre modèle de données en 2NF du fait qu’à chaque fois que l’on insère un bar dans un pays il est nécessaire de renseigner l’information sur le continent où se trouve ce pays. Or, il existe une dépendance fonctionnelle entre les informations pays,continent :

• \(pays \rightarrow continent\)

qui représente donc une dépendance transitive entre ces attributs non-primitifs.

On doit respecter la troisème forme normale (3F) :

• la relation est en deuxième forme normale (2NF) et tous les attributs non-primitifs sont directement dépendant d’une clé

Autrement dit :

• il n’existe pas de dépendances fonctionnelles entre les attributs non-clé.

On peut donc à nouveau décomposer notre relation barsdumonde en appliquant le théorème de Heath :

• \(bars(\underline{id\_bar},nom,pays)\)
• \(monde(\underline{pays},continent)\)

On obtient au final une structuration de notre base de donnée en 3NF :

• \(monde(\underline{pays},continent)\)
• \(bars(\underline{id\_bar},nom,pays)\)
• \(bieres(\underline{id\_biere},biere,couleur,origine)\)
• \(services(\underline{id\_bar,id\_biere},stock)\)

On obtient bien par jointure naturelle :

• \(\Join(monde,\Join(bars,\Join(services,bieres)))\)

les informations présentes dans la base de données initiale en 1NF :