La normalisation des bases de données
Description du thème : Réflexion sur le besoin de normaliser les bases de données relationnelles.
Mots-clés : Normalisation, forme normale, tuple, clé primaire, base de données relationnelle
Niveau : 1ère STG
Terminale GSI
Domaine(s) : Informatique de gestion
Type(s) de ressource : Support de cours
Objectifs : La normalisation des bases de données relationnelles.
Séance(s) développée(s) :
Place de la séquence dans la progression annuelle :
Pré-requis : Les dépendances fonctionnelles.
Les bases de données relationnelles.
Outils : Microsoft Access ou Open Office.Org Base.
Un vidéoprojecteur.
Conditions de réalisation : En séance de travaux dirigés.
Evaluation : Formative
Temps approximatif de réalisation : 3 heures environ
Compétences B2i : Dossier professeur
Les bases de données normaliséeserr
Dossier élève
Contact : claude.pasqualini@ac-limoges.fr
Les bases de données normalisées (oui ou non)
Dans tous les ouvrages, il est indiqué que les bases de données doivent être normalisées, c’est à dire satisfaire à la 3ème forme normale.
Il serait bon d’abord de re-préciser les 3 formes normales, puis de réfléchir au besoin de la normalisation et enfin de se poser la question si toutes les tables d’une base de données doivent être normalisées.
Tout d’abord, nous allons préciser ce que l’on appelle une dépendance fonctionnelle (DF) : Deux rubriques R1 et R2 sont dites en Dépendance Fonctionnelle si le fait de connaître la valeur de R1 permet de connaître une valeur et une seule de R2.
R1R2. R1 et la source de la DF et R2 le but.
I -La normalisation des données I-1- La 1 Forme normale (1FN)ère Définition :
Une relation est en 1FN si :
o Tous ses attributs sont élémentaires ; o La relation possède une clé primaire.
Il faut rappeler qu’un attribut est élémentaire lorsqu’il ne peut pas se décomposer. C’est à dire que l’attribut ne peut pas être un ensemble de plusieurs éléments.
Exemple 1 : La relation LIVRE (N° Livre, Titre, Auteurs) telle que
• N° Livre -> Titre
• N° Livre ->Auteurs
est-elle en Première Forme Normale ?
LIVRE N° Livre Titre Auteurs
1 2 3 4
Astérix le Gaulois Les diaboliques Tintin en Amérique Paris brûle-t-il ?
Goscinny, Uderzo Boileau-Narcejac Hergé
Lapierre, Collins
NON, l’attribut Auteurs peut avoir deux valeurs (il pourrait y an avoir plus)…
Comment peut-on transformer la relation LIVRE pour obtenir une solution en 1ère forme normale ?
LIVRE (n° Livre, titre)
AUTEUR (n°auteur, nom auteur) ECRIRE (n° livre#, n°auteur#)
LIVRE N° Livre Titre
1 2 3 4
Astérix le Gaulois Les diaboliques Tintin en Amérique Paris brûle-t-il ?
AUTEUR N°auteur Nom auteur 1
2 3 4 5 6
Goscinny Uderzo
Boileau-Narcejac Hergé
Lapierre Collins
ECRIRE N° Livre N°auteur 1
1 2 3 4 4
1 2 3 4 5 6
I-2- La 2 forme normale(2FN)ème Définition :
Une relation est en 2FN si : o Elle est en 1FN ;
o Chacun des attributs ne faisant pas partie de la clé primaire est en Dépendance Fonctionnelle élémentaire avec la clé primaire.
Remarque : Une relation en 1FN avec un seul attribut comme clé primaire est automatiquement en 2FN. Cela signifie que le problème se pose uniquement pour les tables qui ont une clé primaire composée.
n’est en dépendance fonctionnelle avec aucune partie de la clé.
Exemple 2 : La relation ELEVE (N° Elève, N° Matière, N° Classe, Moyenne) est-elle en deuxième forme normale ?
Tout d’abord est-elle en 1FN ? OUI En 2FN ?
NON car les DF sont les suivantes :
N° Elève, N° Matière -> moyenne : DF élémentaire.
N° Elève -> N° classe : donc N°Elève, n° matière n° classe est une DF non élémentaire.
Il y a un attribut en Dépendance fonctionnelle avec une partie de la clé primaire.
Pour le rendre en 2FN on crée une relation supplémentaire telle que ELEVE (N° Elève, N° Classe)
RESULTAT (N° Elève, N° Matière, moyenne) I-3- La 3 forme normale(3FN)ème
Définition :
Une relation est en 3FN si : o Elle est en 2FN ;
o Chacun des attributs ne faisant pas partie de la clé primaire est en Dépendance Fonctionnelle élémentaire et directe avec la clé primaire et uniquement avec elle.
Rappel : un attribut est en dépendance fonctionnelle directe avec la clé primaire lorsque cette dépendance fonctionnelle n’est pas obtenue par transitivité.
Exemple 3 : La relation ELEVE (N° Elève, nom élève, N° Classe, nom classe) est-elle en deuxième forme normale ?
Tout d’abord est-elle en 1FN ? OUI En 2FN ?
OUI car la clé primaire est une propriété simple.
En 3FN ?
NON, car N° élève nom classe n’est pas une dépendance fonctionnelle directe.
En effet : N°élève N° classe et N°classe nom classe
Pour la rendre en 3FN on crée une relation supplémentaire telle que ELEVE (N° Elève, nom élève, N° Classe#)
CLASSE (N° Classe, nom classe)
II- Le besoin de normalisation
La normalisation des relations est nécessaire pour éviter des problèmes de mises à jour et de redondances. Il suffit de se baser sur les Dépendances Fonctionnelles élémentaires et directes.
II-1- Exemple de table non normalisée en 2FN
Prenons l’exemple 2 : ELEVE (N° Elève, N° Matière, N° Classe, moyenne)
Prenons les hypothèses suivantes :
Il existe 2000 élèves au sein d’un lycée. Chaque élève travaille au plus sur 10 matières. Nous sommes d’accord que le nombre de tuples de la table élève est de 20 000 au maximum.
Posons d’autres hypothèses sur la taille de chaque attribut : N° élève : 5 caractères
N° matière : 2 N° classe : 3
Moyenne : 4 dont 2 décimales.
La taille d’un tuple est de 14 caractères.
On peut déduire que le volume total de la table ELEVE est de 20 000 * 14 = 280 000 caractères.
Prenons à nouveau l’exemple 2 avec la solution normalisée : ELEVE (N° Elève, N° Classe)
RESULTAT (N° Elève, N° Matière, moyenne)
La table ELEVE possède 2000 tuples. La taille de chacun est égale à 5+3=8 caractères.
Le volume de la table ELEVE est égal à 2000 * 8 = 16 000 caractères.
La table RESULTAT possède 20 000 tuples. La taille de chacun est égale à 5+2+4=11 caractères.
Le volume de le table RESULTAT est égal à 20 000 *11 = 220 000 caractères.
Le volume total est égal à 220 000 + 16 000 = 236 000 caractères < 280 000 caractères.
On se rend compte que la solution normalisée utilise moins d’espace disque que la solution non normalisée.
En fait dans cette solution, le n° classe était redondant : répété pour chaque matière, donc il y avait perte de place.
II-2- Exemple de table non normalisée en 3FN
Prenons l’exemple 3 : ELEVE (N° Elève, nom élève, N° Classe, nom classe) Prenons les hypothèses suivantes :
Il existe 2000 élèves au sein d’un lycée. Il existe au maximum 100 classes différentes.
Posons d’autres hypothèses sur la taille de chaque attribut : N° élève : 5 caractères
Nom élève : 20 N° classe : 3 Nom classe : 50.
La taille d’un tuple est de 78 caractères.
On peut déduire que le volume total de la table ELEVE est de 2000 * 78 = 156 000 caractères.
Prenons à nouveau l’exemple 3 avec la solution normalisée : ELEVE (N° Elève, nom élève, N° Classe#)
CLASSE (N° Classe, nom classe)
La table ELEVE possède 2000 tuples. La taille de chacun est égale à 5+20+3=28 caractères.
Le volume de la table ELEVE est égal à 2000 * 28 = 56 000 caractères.
La table CLASSE possède 100 tuples. La taille de chacun est égale à 3+50=53 caractères.
Le volume de le table CLASSE est égal à 100 *53= 5 300 caractères.
Le volume total est égal à 56 000 + 5 300 = 61 300 caractères < 156 000 caractères.
On se rend compte que la solution normalisée utilise beaucoup moins d’espace disque que la solution non normalisée.
En fait dans cette solution, le nom de la classe était redondant : répété pour chaque élève, donc il y avait perte de place.
D’autre part, si un jour le nom de la classe est modifié, au lieu de le modifier n fois dans la solution non normalisée, il suffit de le modifier une seule fois dans la solution normalisée.
III- Les limites de la normalisation
Comme on vient de le voir sur les exemples ci-dessus, l’intérêt de la normalisation consiste à supprimer les redondances d’information et de résoudre certains problèmes de mise à jour.
On peut néanmoins remarquer que les problèmes de stockage se posent de moins en moins avec la capacité croissante des disques durs.
Le problème soulevé par les utilisateurs est principalement le temps de réponse des applications.
Il semble en effet inadmissible à présent que le temps de réponse d’une requête dépasse 3 ou 4 secondes. Donc l’optimisation des applications doit surtout concerner la vitesse d’exécution plutôt que la place sur disque.
Dans certains cas, pour pouvoir optimiser le temps de réponse, le développeur sera amené à effectuer des redondances nécessaires dans ses bases de données.
III-1- Exemple 1 de dénormalisation
Prenons 3 tables :
Équipe (n° équipe, …n°club#)
Match (n° match, date match, heure match, lieu match) Rencontrer (n°match#, n°équipe#, nombre de buts marqués)
Donc la table match contient les matches et la table rencontrer contient le score car en termes de dépendances fonctionnelles :
Pour un match et une équipe, il y a un nombre de buts marqués et un seul.
Ce choix est bien sûr normalisé : mais lorsque l’on veut afficher le détail de tous les matches de la table, il faut effectuer une jointure entre les 3 tables. De plus, pour un match, il faut effectuer une recherche sur les 2 tuples correspondants de Rencontrer afin de pouvoir exploiter le score.
La solution préconisée pour gérer ce type de problème est la suivante :
Sachant que le nombre de tuples de Rencontrer pour un seul match est au maximum 2, on peut regrouper Match et Rencontrer en une seule table :
Match (n° match, date match, heure match, lieu match, n°équipe locale#, nombre de buts_1, n
°équipe visiteuse#, nombre de buts_2) Équipe (n° équipe, …n°club#)
Avec quelques précisions :
nombre de buts_1 correspond au nombre de buts marqués par l’équipe locale, nombre de buts_2 correspond au nombre de buts marqués par l’équipe visiteuse,
n° équipe locale# et n° équipe visiteuse# sont des clés étrangères faisant référence au n° équipe de la table Équipe.
La table Match décrite ci-dessus n’est pas normalisée, car elle n’est pas en 1FN. En effet, n°
équipe et nombre de buts figurent plus d’une fois dans la table.
Mais la solution présentée permet de visualiser le score de façon immédiate et si l’on veut afficher le détail de chaque match de la table, on n’a besoin que d’une seule jointure avec la table Équipe, et donc le traitement sera plus rapide que dans la solution normalisée.
Il faut bien préciser que cette dénormalisation est possible car on connaît le nombre de tuples de Match dans Rencontrer et que ce nombre est peu élevé. (C’est facile à expliquer avec les cardinalités d’un MCD…)
III-2- Exemple 2 de dénormalisation
Considérons un sport donné. Un joueur appartient à un club. Ce club appartient à un comité départemental et La ligue régionale gère les comités départementaux.
Au niveau de la fédération nationale de ce sport, le schéma relationnel peut être le suivant :
Supposons à présent qu’au niveau national, les dirigeants de la fédération veulent éditer une liste des joueurs en précisant uniquement la ligue régionale à laquelle ils appartiennent.
La requête à créer doit permettre :
• une jointure entre Joueur et Club sur n° club,
• une jointure entre Club et Comité sur n° comité,
• enfin, une jointure entre Comité et Ligue sur n° ligue et cela pour chaque joueur de la table Joueur.
Le temps de réponse de cette requête peut être amélioré en dénormalisant le schéma relationnel.
Puisque dans ce contexte, on ne souhaite obtenir que les données du Joueur et de la ligue, les informations du club et du comité départemental étant inutiles, on va créer une dépendance fonctionnelle non directe entre Joueur et Ligue, donc le schéma relationnel sera en 2FN, mais plus en 3FN :
La solution obtenue n’est plus normalisée, mais le temps de réponse de la requête souhaitée est optimisé et c’est le but qu’il fallait atteindre.
Voilà deux exemples parmi d’autres sur la dénormalisation. Le but de cette démonstration est de dire aux élèves que dans un premier choix, il est conseillé de produire une base de données normalisée.
Mais si elle ne l’était pas, tout en restant absolument cohérente, ce n’est pas un problème majeur.
D’autre part, la base de données construite de façon normalisée peut ensuite être modifiée (dénormalisée) à des fins d’optimisation.
