Introduction
Si tu es jeune, tu te poses des questions existentielles qui ramènent Spinoza aux conversations de pochtrons qui se terminent à l’alcool de bois à 5 h du mat’ au Macumba. Si tu es vieux (comme moi), tu as vu tellement d’horreurs qui feraient passer l’Enfer de Dante pour le dernier film de Bob l’Eponge.
Alors, comme faire ? Rassure-toi, point n’est besoin de se se farcir l’Algèbre Relationnelle d’Edgar Codd (1). Pas plus que des diagrammes UML (2), mais ça viendra après.
Les formes normales (mais pas trop)
On a dû vous parler des Formes Normales, surtout les 3 premières, mais, connaissez-vous celle de Boyce-Codd ? la 4ème ? la 5ème ? Les définitions formelles sont assez obscures. Alors, je vais vous parler plutôt des Dépendances Fonctionnelles (3) (4), et surtout de graphes de dépendances fonctionnelles, car c’est une approche simple et sûre pour modéliser une base de données.
Car arriver à une base de données normalisée à partir d’un schéma UML me semble franchement difficile.
Il est bien sûr très intéressant de raisonner de manière abstraite dans un premier temps, et un schéma d’objet est tout à fait approprié. Quand on arrive à la phase plus concrète d’implémentation de schéma base de données, on se retrouve vite avec des redondances vicieuses bien cachées qui sont de véritables bombes à retardement.
Il vaut mieux partir d’une base de données normalisées et introduire des redondances après les avoir mûrement réfléchies que se retrouver des années plus tard avec une BdD complètement pourrie et bricoler du code pour corriger les données…
Les dépendances fonctionnelles
Qu’est-ce qu’une dépendance fonctionnelle ?
Avec des mots simples que peuvent comprendre les gens normaux que nous sommes qui se promènent avec des Santiag ou des Doc Marten’s et qui écoutent de la musique avec des vrais instruments, c’est ceci : “Si une caractéristique A permet de déterminer une autre caractéristique B, alors il y a une dépendance fonctionnelle entre A et B que l’on notera A –> B.”
L’exemple de la plaque d’immatriculation et d’un véhicule
Qui de la poule et l’œuf était avant ?… le dinosaure ! Euh… est-ce que c’est le véhicule qui permet de connaître la plaque ? Si je vous dis “Fiat 500 Abarth rouge”, arriverez-vous à trouver la plaque d’immatriculation, sachant qu’il y a une foule de Fiat 500 de ce modèle et cette couleur ? Ne serait-ce pas plutôt la plaque d’immatriculation qui me permet d’identifier, de retrouver “LA Fiat 500 Abarth rouge” bien particulière que je recherche… ?
Plaque Immatriculation –> Véhicule
L’exemple du N° INSEE (ou n° de sécurité sociale) et Personne.
Est-ce le fait de connaître Pierre Martin me permet de déterminer facilement son n° INSEE ?
J’en doute… Par contre, si j’ai le n° INSEE “1 65 05 13 155 666”, je retrouve bien LE Pierre Martin que je recherche.
N° INSEE -> Personne
Précisons maintenant la limite des exemples précédents, il s’agissait de vulgariser le concept, et dans les faits, on va réfléchir un peu plus finement, car dans la théorie des Bases de Données Relationnelles que je vous dispense de dévorer pour justement vous simplifier la vie la définition des “caractéristiques” est très précise.
Et maintenant, on modélise
Je vais donc vous proposer en apéro (ouais !!!) sans alcool (…ooooh…) un petit exercice de modélisation qui permettra d’arriver à un graphe de dépendances fonctionnelles, et de là, la définition d’une entité de base de donnée coulera de source.
Voici l’exercice : “Comment modéliser une personne ?”, soit plus simplement :
- Comment représenter abstraitement une personne ?
- Quelles sont les informations qui caractérisent une personne ?
- Et en particulier, quelles sont les informations qui m’intéressent pour définir une personne ? Car je ne vais pas avoir besoin des mêmes infos si je gère une salle de sport, ou un club de musique. C’est un point important à garder dans le coin de la tête, mais…
Restons très simple. Une personne a un prénom, un nom, est née quelque part à une date bien précise. Contentons nous de ceci:
- PRENOM,
- NOM,
- DATE DE NAISSANCE,
- LIEU DE NAISSANCE,
Est-ce que ceci est suffisant pour définir précisément une personne ? D’un point de vue abstrait bien, n’entrons pas dans des considérations philosophiques, complexes !
Voici les 5 premiers noms de famille les plus répandus en France :
- Martin : 250 013
- Bernard : 131 330
- Thomas : 118 331
- Petit : 115 217
- Robert : 112 998
Et voici les prénoms :
- Marie 2 231 347
- Jeanne 564 247
- Françoise 401 427
- Monique 399 616
- Catherine 394 699
- Jean 1 911 457
- Pierre 892 384
- Michel 820 560
- André 712 248
- Philippe 538 712
Il risque d’y avoir de fortes probabilités pour trouver plus d’une “Marie Bernard” et plus d’un “Pierre Martin”… C’est bien pour ça qu’a été inventé le n° INSEE qui est unique et attribué à une seule et unique personne. Il faut rajouter cette information à notre liste:
- N° INSEE,
- PRENOM,
- NOM,
- DATE DE NAISSANCE,
- LIEU DE NAISSANCE,
En reprenant la notation des dépendances fonctionnelles “A –> B”, on a ceci :
- N° INSEE –> PRENOM,
- N° INSEE –> NOM,
- N° INSEE –> DATE DE NAISSANCE,
- N° INSEE –> LIEU DE NAISSANCE,
Regardez donc votre compte Ameli et celui de quelqu’un d’autre. Votre n° INSEE détermine de manière UNIQUE votre nom, votre prénom, votre date de naissance, votre lieu de naissance, pas les informations de l’autre personne. Si l’on poursuit la réflexion sur ce petit exemple, qu’est-ce qu’un lieu de naissance ? C’est une commune. Oui, mais il y a :
- 13 Saint-Sauveur
- 12 Sainte-Colombe
- 11 Beaulieu
Donc la commune ne suffit pas, il faut préciser le département pour faire la différence entre Mars dans la Loire (42) et Mars dans l’Ardèche (07). Oui, en France, on peut aller sur Mars. Deux fois… Elon Musk en rêve… Voici notre liste mise à jour :
- N° INSEE –> PRENOM,
- N° INSEE –> NOM,
- N° INSEE –> DATE DE NAISSANCE,
- N° INSEE –> COMMUNE DE NAISSANCE,
- N° INSEE –> DEPARTEMENT DE NAISSANCE,
- N° INSEE –> PAYS DE NAISSANCE
Et on peut ajouter le pays de naissance.
Représentation graphique avec le Graphe des D.F
Le graphe des dépendances fonctionnelles se dessine comme suit :
Et… de ce graphe à la définition d’une entité “Personne”, il n’y a qu’un pas !
PERSONNE( N° INSEE ,PRENOM,NOM, DATE DE NAISSANCE,COMMUNE DE NAISSANCE, DEPARTEMENT DE NAISSANCE,PAYS DE NAISSANCE)
Voici l’entité “PERSONNE”, une conceptualisation, une définition abstraite d’une personne dans une système d’information, et plus particulièrement, dans une base de données. L’identifiant est, vous vous en doutez, le N° INSEE. Si vous en voyez pas encore l’intérêt de passer par les Dépendances Fonctionnelles et le Graphe qui le représente, ça va être peut être plus clair avec l’exemple suivant.
L’exemple des commandes
On continue ? On continue ! 😺 On va manipuler maintenant des clients, des commandes, des articles, des fournisseurs.
Définition du client
Un nom de société, un nom de contact, un numéro de téléphone principal, un numéro de téléphone mobile, une adresse mail, une adresse principale, plusieurs adresses de livraison
Définition des commandes
Un client passe une commande à une date bien précise d’un ou plusieurs articles. La commande sera livrée à une adresse de livraison.
Définition des articles
Un article a un nom court mais clair, une description plus détaillée, un prix à une certaine date, il est fabriqué par un fournisseur.
Définition des fournisseurs
Un nom de société, un nom de contact, un numéro de téléphone principal, un numéro de téléphone mobile, une adresse mail, une adresse principale, plusieurs adresses d’approvisionnement
Ça va ? Pas trop les chocottes ?
Bien ! Alors… ON ATTAQUE !
La modélisation du client.
- un nom de société,
- un nom de contact,
- un numéro de téléphone principal,
- un numéro de téléphone mobile,
- une adresse mail,
- une adresse principale,
- plusieurs adresses de livraison
La question de l’identification du client se pose comme celle de la personne dans le petit exercice précédent. Donc, sans plus attendre, on va parler de “code client”. Ça, c’est fait.
On a les D.F. suivantes :
- CODE_CLIENT –> NOM_SOCIETE
- CODE_CLIENT –> NOM_CONTACT
On peut se poser des questions sur les numéros de téléphones.
- Est-ce qu’il y aura seulement une numéro principal et un numéro de mobile ?
- Est-ce qu’il n’y aurait pas plus de numéros que ça ?
Nous allons choisir l’hypothèse simple de 2 numéros et rien de plus. Voici les D.F. qui suivent :
- CODE_CLIENT –> TELEPHONE_PRINCIPAL
- CODE_CLIENT –> TELEPHONE_MOBILE
- CODE_CLIENT –> MAIL
Que faire avec l’adresse principale et les adresses de livraison ?
On pourrait penser à une dépendance fonctionnelle entre le CODE_CLIENT et les éléments qui constituent l’adresse principale. Il y a plusieurs adresses de livraison. Plusieurs, cela sous-entend un ensemble possible de 1 à “n” adresses de livraison, “n” pouvant être 2, 3, 4, 5, …
On comprend vite qu’il ne peut y avoir de D.F. directes entre le CODE_CLIENT et les adresses de livraison. Et comme modéliser, c’est rationaliser et prévoir, on se doute de l’émergence d’une entité “Adresses du client”.
Voici le graphe des D.F. du client :
La modélisation des adresses
Comment déterminer une adresse client ? Avec le CODE_CLIENT, bien sûr. Et comme il y en a plusieurs, il y a aussi un numéro d’adresse. Donc CODE_CLIENT et NUMERO_ADRESSE déterminent une adresse d’un client. Qu’est-ce qui définit une adresse en plus de ces notions ?
- le TYPE_ADRESSE (principale, livraison), et selon l’AFNOR :
- DENOMINATION
- DESTINATAIRE
- BATIMENT
- VOIE
- MENTION_SPECIALE
- CODE_POSTAL
- COMMUNE
- CEDEX
- LIBELLE_CEDEX
On a donc les D.F. suivantes :
- CODE_CLIENT, NUMERO_ADRESSE –> TYPE_ADRESSE
- CODE_CLIENT, NUMERO_ADRESSE –> DENOMINATION
- CODE_CLIENT, NUMERO_ADRESSE –> DESTINATAIRE
- CODE_CLIENT, NUMERO_ADRESSE –> BATIMENT
- CODE_CLIENT, NUMERO_ADRESSE –> VOIE
- CODE_CLIENT, NUMERO_ADRESSE –> MENTION_SPECIALE
- CODE_CLIENT, NUMERO_ADRESSE –> CODE_POSTAL
- CODE_CLIENT, NUMERO_ADRESSE –> COMMUNE
- CODE_CLIENT, NUMERO_ADRESSE –> CEDEX
- CODE_CLIENT, NUMERO_ADRESSE –> LIBELLE_CEDEX
Jusqu’à présent, je vous ai épargné les aigreurs d’estomac avec les formes normales, ce n’est pas pour autant que je vous accorde un permis de tuer les Bases de Données ! Un peu de bon sens, même sans parler de la forme de Boyce-Codd, nous permet de trouver des petits grains de sable.
La présence de “COMMUNE” dans l’entité ADRESSE_CLIENT n’est-elle pas une magnifique redondance ? Comment reconnaît-on une commune ? Grâce à son code postal. On trouve donc la D.F. : CODE_POSTAL –> COMMUNE,
Ce qui donne finalement :
- CODE_CLIENT, NUMERO_ADRESSE –> TYPE_ADRESSE
- CODE_CLIENT, NUMERO_ADRESSE –> DENOMINATION
- CODE_CLIENT, NUMERO_ADRESSE –> DESTINATAIRE
- CODE_CLIENT, NUMERO_ADRESSE –> BATIMENT
- CODE_CLIENT, NUMERO_ADRESSE –> VOIE
- CODE_CLIENT, NUMERO_ADRESSE –> MENTION_SPECIALE
- CODE_CLIENT, NUMERO_ADRESSE –> CODE_POSTAL
- CODE_CLIENT, NUMERO_ADRESSE –> CEDEX
- CODE_CLIENT, NUMERO_ADRESSE –> LIBELLE_CEDEX
et
- CODE_POSTAL –> COMMUNE
La modélisation des commandes
Un client passe une commande à une date donnée, et elle contient un à plusieurs articles en précisant la quantité, par exemple 3 jeux de cordes D’addario 9-42 pure nickel. Le prix figure sur la fiche de l’Article, certes, mais dans la commande, le prix peut être négocié, donc différent de celui de l’article. Ce qui sous-entend qu’il y aura plus tard la modélisation des articles ???
Si, si, c’est vrai, alors, autant s’y atteler de suite.
L’interlude des Articles
Un article, c’est un nom court, un code pas trop ésotérique, compréhensif juste ce qu’il faut. Et ensuite une description un peu plus étoffée. Un prix à une date donnée. Hé oui, les prix changent au cours du temps. il faut pouvoir connaitre le prix valable à la date d’une commande. Et n’a-t-on pas parlé au début de cette étude de cas de la notion de “fournisseur” ?
Si, si, retournez voir un peu en haut…
Le méta-interlude des Fournisseurs…
Ami lecteur, tu commences à avoir l’habitude, ou bien ? Un fournisseur, c’est un nom court, on l’espère assez compréhensible, puis un texte plus développé. Et pour ne pas aller trop loin, une description libre encore plus grande pour noter l’adresse ou des infos diverses saisies librement. C’est une étude de cas, pas un projet de la Vraie Vie de la Réalité Réelle.
Allez, on décrit les D.F. des Articles et des Fournisseurs :
- CODE_ARTICLE –> DESCRIPTION_ARTICLE
- CODE_ARTICLE –> CODE_FOURNISSEUR
- CODE_ARTICLE, DATE_TARIF –> TARIF_ARTICLE
- CODE_FOURNISSEUR –> DESCRIPTION_FOURNISSEUR
Voici le Graphe de ces derniers éléments :
Revenons à nos moutons …
Euh… aux commandes !
Donc, un client peut commander à une date donnée un ou plusieurs articles, on précise dans la commande la quantité de chaque article, et le prix négocié. Le N° de commande est unique dans le système.
- NUMERO_COMMANDE –> CODE_CLIENT
- NUMERO_COMMANDE –> DATE_COMMANDE
Comment faire pour l’ensemble des articles d’une commande ?
Ce sont des lignes de commande…
- NUMERO_COMMANDE, NUMERO_LIGNE –> CODE_ARTICLE
- NUMERO_COMMANDE, NUMERO_LIGNE –> QUANTITE_ARTICLE
- NUMERO_COMMANDE, NUMERO_LIGNE –> TARIF_NEGOCIE
Voici le graphe des commandes :
Et sous vos applaudissements, voici le graphe complet des D.F.
Et tout ça pour quoi ?
De ce graphe des D.F. se déduit immédiatement les entités.
Vous objecterez en me faisant remarquer que la définition formelle des D.F. suffisait et que point n’était besoin de faire des petits schémas de ce genre…
En êtes-vous si sûrs ?
Cas : Modélisation du vin
Voici un cas un peu taquin. On va modéliser du vin avec les informations suivantes :
- Le CRU, soit la zone de production du vin.
- La REGION, une zone un peu plus large.
- Le PAYS, zone encore plus large.
- La QUALITE, appréciation selon les œnologues
Le Chablis est un CRU dans la REGION de Bourgogne, en France, de QUALITE qui peut être Excellente. Le Brouilly est un CRU dans la REGION du Beaujolais, en France, de Bonne QUALITE. Sauf que… On trouve du Chablis et du Brouilly californien. Et aussi du Cabernet Sauvignon.
| CRU | REGION | PAYS | QUALITE |
| Brouilly | Beaujolais | France | Bon |
| Brouilly | Californie | USA | Excellente |
| Cabernet Sauvignon | Bordeaux | France | Excellente |
| Cabernet Sauvignon | Californie | USA | Excellente |
| Chablis | Bourgogne | France | Excellente |
| Chablis | Californie | USA | Bon |
Quelles sont les D.F. ?
- CRU, PAYS –> REGION
- CRU, PAYS –> QUALITE
- REGION –> PAYS
Ce qui nous fait un graphe… étonnant
L’exemple est simple, avec seulement 3 D.F., on pourrait repérer l’étrangeté. Mais dans une liste plus longue, cela passerait inaperçu. Vous voyez un schéma qui ne respecte pas la forme de Boyce-Codd.
On a une clé (REGION) qui a pour attribut PAYS qui est une partie de la clé (CRU, PAYS) qui détermine REGION. On en revient à “qui de la poule ou l’œuf était avant”. En fait, le CRU ne détermine pas la REGION puisqu’on a du Cabernet aussi bien en Californie qu’à Bordeaux. Mais la REGION détermine bien le PAYS.
Voici le vrai graphe :
Cas : élève, cours et instrument
La table suivante respecte la 3ème forme normale. La clé de cette table est (ELEVE, COURS, INSTRUMENT).
| ELEVE | COURS | INSTRUMENT |
| Jimmy Page | Solfège | Guitare |
| Jimmy Page | Théorie | Guitare |
| John Bonham | Solfège | Batterie |
| John Bonham | Théorie | Batterie |
- F.N. = les champs sont atomiques, càd, il n’existe pas d’information complexe rassemblant plusieurs notions, comme par exemple ADRESSE qui serait composé de NUMERO, VOIE, CODE POSTAL, VILLE, …
- F.N. = tout attribut n’appartenant pas à une clé ne dépend d’une partie de la clé
- F.N. = pas de transitivité, soit : tout attribut n’appartenant pas à une clé ne dépend pas d’un autre attribut non clé.
On y est en plein. Et pourtant, on voit bien des redondances.
L’analyste a défini la BdD en ne réfléchissant que sur les définitions des 3 premières Formes Normales, et quand l’application est finie, on a une table … moyenne…
Et c’est devenu pire quand 2 petits génies sont arrivés dans cette école :
| ELEVE | COURS | INSTRUMENT |
| Jimmy Page | Solfège | Guitare |
| Jimmy Page | Théorie | Guitare |
| John Bonham | Solfège | Batterie |
| John Bonham | Théorie | Batterie |
| Prince | Solfège | Batterie |
| Prince | Solfège | Guitare |
| Prince | Solfège | Piano |
| Prince | Théorie | Batterie |
| Prince | Théorie | Guitare |
| Prince | Théorie | Piano |
| Stevie Wonder | Solfège | Piano |
| Stevie Wonder | Solfège | Batterie |
| Stevie Wonder | Solfège | Harmonica |
| Stevie Wonder | Théorie | Piano |
| Stevie Wonder | Théorie | Batterie |
| Stevie Wonder | Théorie | Harmonica |
Ce que l’on vient de voir est le cas des dépendances multivaluées. Soit le non respect de : La 4ème Forme Normale. Pour respecter la 4FN, on aurait du avoir 2 tables :
COURS_SUIVIS(ELEVE, COURS)
| ELEVE | COURS |
| Jimmy Page | Solfège |
| Jimmy Page | Théorie |
| John Bonham | Solfège |
| John Bonham | Théorie |
| Prince | Solfège |
| Prince | Théorie |
| Stevie Wonder | Solfège |
| Stevie Wonder | Théorie |
et
INSTRUMENTS_APPRIS(ELEVE, INSTRUMENT)
| ELEVE | INSTRUMENT |
| Jimmy Page | Guitare |
| John Bonham | Batterie |
| Prince | Batterie |
| Prince | Guitare |
| Prince | Piano |
| Stevie Wonder | Piano |
| Stevie Wonder | Batterie |
| Stevie Wonder | Harmonica |
Les clés sont en GRAS et SOULIGNEES. NON, ce n’est pas fini ! Poursuivons avec nos élèves :
| MUSICIEN | INSTRUMENT | MARQUE |
| Eric Clapton | ES-335 | Gibson |
| Eric Clapton | Firebird | Gibson |
| Eric Clapton | LesPaul | Gibson |
| Eric Clapton | SG | Gibson |
| Eric Clapton | Stratocaster | Fender |
| Eric Clapton | Telecaster | Fender |
| Jeff Beck | LesPaul | Gibson |
| Jeff Beck | Stratocaster | Fender |
| Jeff Beck | Telecaster | Fender |
| Jimmy Page | 59DC | Danelectro |
| Jimmy Page | LesPaul | Gibson |
| Jimmy Page | Stratocaster | Fender |
| Jimmy Page | Telecaster | Fender |
Les redondances sautent aux yeux, non ?
On est dans un cas de non respect de : La 5ème Forme Normale
La définition pique un peu les yeux :
“Pour toute relation de dimension n (avec n supérieur à 2) en quatrième forme normale, il ne doit pas être possible de retrouver l’ensemble de ses occurrences par jointure sur les occurrences des relations partielles prises deux à deux. Cette normalisation conduit parfois à décomposer une relation complexe en plusieurs relations plus simples.”
Georges Gardarin l’appelle “Forme normale de projection jointure”. Je vous fait grâce des détails qui viennent juste après, mais pour les plus matheux d’entre vous maîtrisant la Théorie des Ensembles, l’Algèbre Relationnel sera une promenade de santé, et vous pouvez aller jeter un œil sur le site du professeur Gardarin (5).
Avec un peu de bon sens, on peut déduire assez facilement comment éliminer les redondances de la table. Il suffit de la décomposer en 3.
| MUSICIEN | INSTRUMENT |
| Eric Clapton | ES-335 |
| Eric Clapton | Firebird |
| Eric Clapton | LesPaul |
| Eric Clapton | SG |
| Eric Clapton | Stratocaster |
| Eric Clapton | Telecaster |
| Jeff Beck | LesPaul |
| Jeff Beck | Stratocaster |
| Jeff Beck | Telecaster |
| Jimmy Page | 59DC |
| Jimmy Page | LesPaul |
| Jimmy Page | Stratocaster |
| Jimmy Page | Telecaster |
| INSTRUMENT | MARQUE |
| ES-335 | Gibson |
| Firebird | Gibson |
| LesPaul | Gibson |
| SG | Gibson |
| Stratocaster | Fender |
| Telecaster | Fender |
| 59DC | Danelectro |
| MUSICIEN | MARQUE |
| Eric Clapton | Gibson |
| Eric Clapton | Fender |
| Jeff Beck | Gibson |
| Jeff Beck | Fender |
| Jimmy Page | Danelectro |
| Jimmy Page | Gibson |
| Jimmy Page | Fender |
Conclusion
Je vous ai amené au plus profond de l’Enfer de Dante.
Mais, progressivement. L’intérêt d’aller au bout de la normalisation d’une Base de Données est de pouvoir maîtriser la dénormalisation plutôt que de la subir par la suite. En fin de modélisation, on a un schéma idéal. S’il est performant, on a tout gagné.
S’il ne l’est pas, on dénormalise progressivement pour atteindre le niveau de performances souhaité et cela se fait en connaissances de causes. Il ne faut pas être dogmatique, mais pragmatique.
