Chapitre 4 la normalisation PDF

Preview

Summary

Download Chapitre 4 la normalisation PDF

Description

Cours Bases de Données

Chapitre 4: La Normalisation

Chapitre 4 : La Normalisation Objectifs :  1. Connaître la notion de dépendance fonctionnelle.  2. Comprendre les différentes formes normales  3. Concevoir une base de données en respectant les règles et les normes. Durée : 3hCI + 1.5 TD Eléments de contenu I.

Introduction

II. Dépendance fonctionnelle 1.

Définition

2.

Propriétés des dépendances fonctionnelles

3.

Dépendance fonctionnelle élémentaire

4.

Dépendance fonctionnelle transitive

5.

Clé d’une relation

III.

Les formes normales

1.

Première Forme Normale (1FN)

2.

Deuxième Forme Normale (2FN)

3.

Troisième Forme Normale (3FN)

4.

Forme Normale de Boyce-codd (BCNF)

IV. Conclusion

Mme Houneida HADDAJI

5

Cours Bases de Données

Chapitre 4: La Normalisation

Chapitre 4 : La Normalisation I- Introduction Un schéma de relation est décrit par la liste de ses attributs et de leurs contraintes d’intégrité éventuelles. La constitution de la liste d’attributs du schéma ne peut pas se faire n’importe comment pour ne pas occasionner de redondance, avec toutes ses implications (perte de place, risques d’incohérence et de perte d’informations). mécanismes pour les construire permettent

Les formes normales des relations et les

d’obtenir des relations non redondantes. Ces

mécanismes sont fondés sur les notions de clés de relations et de dépendances entre données. Ces dernières fournissent les conditions d’application d’un processus dit de normalisation qui mène à des formes « correctes » de relations qui sont les formes normales. Considérons la relation suivante : PRODUIT (Refproduit, LibelleProduit, PU, Quantité, NumService, Adresse, Capacité) qui est visiblement redondante. RefProduit

LibelleProduit PU

Quantité

NumService Adresse

Capacité

P1

CH7

23.510

300

S1

Sousse

9000

P1

CH7

23.510

500

S2

Tunis

6000

P3

VIS12

0.150

900

S4

Sousse

2000

Cette relation présente certaines anomalies : 

Redondance : un produit apparaît autant de fois qu’il sera livré par un service



Mise à jour : faute de redondance, les mises à jour conduiront à des risques d’incohérence et de non intégrité.



Insertion et suppression : l’insertion, la suppression ou le transfert d’attributs pourra faire apparaître des valeurs nulles

On peut dire qu’une Base de Données relationnelle est ‘correcte’ ou normalisée si : 

Chaque relation décrit une information élémentaire avec les seuls attributs qui lui sont directement liés



Il n'y a pas de redondance d’information qui peuvent produire des problèmes de mise à jour

Mme Houneida HADDAJI

6

Cours Bases de Données

Chapitre 4: La Normalisation

II- Dépendance fonctionnelle 1- Définition Un attribut ou une liste d’attributs Y dépend fonctionnellement d’un attribut ou d’une liste d’attributs X dans une relation R, si étant donnée une valeur de X, il ne lui est associé qu’une seule valeur de Y dans tout tuple de R. On notera une telle dépendance fonctionnelle X→Y (X détermine Y ou Y dépend fonctionnellement de X). Exemple PRODUIT (Refproduit, LibelleProduit, PU, Quantité, NumService, Adresse, Capacité) Pour cette relation, les dépendances fonctionnelles suivantes sont vérifiées : RefProduit → LibelleProduit

NumService → Adresse, Capacité

RefProduit → PU

RefProduit, NumService → Quantité

2- Propriétés des dépendances fonctionnelles Des axiomes et des règles d’inférence permettent de découvrir de nouvelles dépendances à partir d’un ensemble initial. Dans ce que suit nous considérons R une relation. Les trois premières propriétés sont connues sous le nom « Axiomes d’Armstrong » Propriété 1 : Réflexivité Y⊆X

⇒ X→ Y

Tout ensemble d’attributs détermine lui-même ou une partie de lui-même. Propriété 2 : Augmentation X→ Y ⇒

X,Z→ Y,Z

Si X détermine Y, les deux ensembles d’attributs peuvent être enrichis par un même troisième. Propriété 3 : Transitivité X → Y et Y → Z ⇒ X → Z. Propriété 4 : Union X → Y et X → Z ⇒ X → Y, Z Propriété 5 : Pseudo-transitivité X → Y et W, Y → Z ⇒ W,X → Z Propriété 6 : Décomposition X → Y et Z ⊆ Y ⇒ X → Z

Mme Houneida HADDAJI

7

Cours Bases de Données

Chapitre 4: La Normalisation

3- Dépendance fonctionnelle élémentaire Une Dépendance fonctionnelle X → Y est élémentaire si pour tout X’ ⊂ X la dépendance fonctionnelle X’  Y n’est pas vraie. En d’autres termes, Y ne dépend pas fonctionnellement d’une partie de X (X est la plus petite quantité d’information donnant Y). Exemple : RefProduit, LibelleProduit → PU n’est pas élémentaire car il suffit d’avoir la référence du produit pour déterminer son prix unitaire.

4- Dépendance fonctionnelle transitive Soient X, Y deux attributs. X→Y est une dépendance fonctionnelle transitive s’il existe un attribut Z tel que X→Z et Z→Y Transitivité X →Y Z

5- Clé d’une relation La clé d’une relation est l’ensemble d’attributs dont les valeurs permettent de caractériser les nuplets de la relation de manière unique. Formellement : Un attribut ou une liste d’attributs X est une clé pour la relation R(X,Y,Z) si 

Y et Z dépendent fonctionnellement de X dans R : X→ Y, Z.



et X → Y, Z est élémentaire.

Une relation peut avoir plusieurs clés. Une clé sera choisie et désignée comme clé primaire. Les autres seront appelées clés candidates. Un attribut d’une relation R est appelé attribut clé s’il appartient au moins à une clé de R. Un attribut est dit attribut non clé s’il n’appartient pas à une clé de R.

III- Les formes normales Les formes normales ont été définies pour permettre la décomposition des relations sans perte d’informations en utilisant la notion de dépendance fonctionnelle.

Dans ce cours nous

présenterons les trois premières formes normales et celle dite de Boyce Codd.

Mme Houneida HADDAJI

8

Cours Bases de Données

Chapitre 4: La Normalisation

1- Première Forme Normale (1FN) Une relation R est en première forme normale et notée 1FN si chaque attribut de R a un domaine simple, c’est à dire dont les valeurs sont atomiques et monovaluées. Cette définition permet d’exclure les relations ayant des attributs dont les valeurs seraient des ensembles ou des listes de valeurs. Exemple : Soit la base de données suivante : NumCl

NomCl

NumApp

Adr_App

DateDLoc

DateFLoc

Montant

NumProp

NomProp

C18

Ali

Ap4

12, rue de la gare

01.07.09

31.08.11

350

P40

Anis

Ap16

7,

01.09.10

01.09.11

450

P23

Sana

av

de

la

république C26

Salah

Ap4

12, rue de la gare

01.08.08

10.06.09

350

P40

Anis

Ap36

3,

de

01.04.11

31.05.12

380

P23

Sana

la

01.01.10

31.07.10

450

P23

Sana

av

l’environnement Ap16

7,

av

de

république

Cette relation n’est pas en première forme normale parce que l’attribut NumApp n’est pas atomique. Considérons les dépendances fonctionnelles qui existent: NumCL→NomCl NumApp → AdrApp, Montant, NumProp, NomProp NumCl, NumApp → DateDLoc, DateFLoc NumProp → NomProp Pour transformer cette relation en 1FN, il faut l’aplatir en considérant comme clé le couple (NumCl, NumApp). NumCl, NumApp → NomCl, AdrApp, DateDLoc, DateFLoc , Montant, NumProp, NomProp Nous aurons donc la relation suivante : AgenceImmobilière (NumCl, NumApp, NomCl, AdrApp, DateDLoc, DateFLoc, Montant, NumProp, NomProp)

Mme Houneida HADDAJI

9

Cours Bases de Données

NumCl

NumApp

NomCl

C18

Ap4

Ali

C18

Ap16

Ali

C26

Ap4

Salah

C26

Ap36

Salah

C26

Ap16

Salah

Chapitre 4: La Normalisation

Adr_App

DateDLoc

DateFLoc

Montant

NumProp

NomProp

12, rue de la gare

01.07.09

31.08.11

350

P40

Anis

01.09.10

01.09.11

450

P23

Sana

01.08.08

10.06.09

350

P40

Anis

01.04.11

31.05.12

380

P23

Sana

01.01.10

31.07.10

450

P23

Sana

7, av de la république 12, rue de la gare 3, av de l’environnement 7, av de la république

2- Deuxième Forme Normale (2FN) Une relation R est en deuxième forme normale (2FN) si et seulement si 

elle est en première forme normale,



et que tout attribut n’appartenant pas à une clé ne dépendra d’aucun sous-ensemble de clé (ne dépend pas d’une partie d’une clé).

Formellement, la définition peut être formulée de cette façon : Soit R une relation, soit Ω la clé de R, R est en deuxième forme normale si : 

R soit en première forme normale.



∀ x attribut de R qui n’appartient pas à Ω, Ω → x est une dépendance fonctionnelle élémentaire.

Exemple : Reprenons l’exemple de l’agence immobilière : Supposons qu’on veuille modifier ou corriger l’adresse d’un appartement apparaissant plusieurs fois dans la table (différents locataires à différentes périodes), il faut modifier plusieurs n-uplets. Ceci est dû au fait que certains attributs ne dépendent pas pleinement de la clé, et qu’il existe donc des dépendances partielles. Elle subit de ce fait des anomalies de fonctionnement qui sont :  Une redondance d’informations susceptible d’induire des incohérences;  En cas de mise à jour du Montant, l’obligation serait de balayer toute la table pour modifier cette dernière partout où elle peut être présente;  Lors de la suppression de la dernière ligne de la base, on perd toute trace de l’appartement. Mme Houneida HADDAJI

10

Cours Bases de Données

Chapitre 4: La Normalisation

Il faut donc décomposer AgenceImmobilière pour obtenir un schéma en 2èmeforme normale. AgenceImmobilière est en 1FN. Nous avons les dépendances fonctionnelles suivantes : (1) NumCl, NumApp → NomCl (2) NumCl, NumApp → AdrApp, Montant, NumProp, NomProp (3) NumCl, NumApp → DateDLoc, DateFLoc et (4) NumCL→NomCl (5) NumApp → AdrApp, Montant, NumProp, NomProp (1) et (4) donnent (1) n’est pas une dépendance fonctionnelle élémentaire. De même, (2) et (5) montrent que (2) n’est pas une dépendance fonctionnelle élémentaire. Pour aboutir à un schéma relationnel en 2FN, il faut éliminer les dépendances fonctionnelles non élémentaires puis regrouper les dépendances fonctionnelles qui ont la même origine pour former une relation. Donc on obtient les dépendances suivantes : (1) NumCl, NumApp → DateDLoc, DateFLoc (2) NumCL→NomCl (3) NumApp → AdrApp, Montant, NumProp, NomProp D’où les relations en 2ème forme normale : Client (NumCl, NomCl) Appartement (NumApp, AdrApp, Montant, NumProp, NomProp) Location(NumCl#, NumApp#, DateDLoc, DateFLoc)

3- Troisième Forme Normale (3FN) Une relation R est en troisième forme normale (3FN) si et seulement si 

elle est en deuxième forme normale,



et que tout attribut n’appartenant pas à une clé ne dépendra pas d’un attribut non clé (R ne contient aucune dépendance fonctionnelle transitive).

La troisième forme normale permettra d’éliminer les redondances dues aux dépendances transitives.

Mme Houneida HADDAJI

11

Cours Bases de Données

Chapitre 4: La Normalisation

La décomposition en 3FN est sans perte d’informations et préserve les dépendances fonctionnelles. Exemple : Reprenons le schéma relationnel de l’exemple précédent : Client (NumCl, NomCl) Appartement (NumApp, AdrApp, Montant, NumProp, NomProp) Location (NumCl#, NumApp#, DateDLoc, DateFLoc) Les relations Client et Location sont en 3FN, puisqu’elles sont déjà en 2 ème forme normale et qu’il n’existe aucun attribut non clé qui dépend d’un autre attribut non clé. On s’intéresse maintenant à la relation Appartement. Elle est en 2FN et présente les dépendances suivantes : (1) NumApp → AdrApp, Montant, NumProp, NomProp (2) NumProp → NomProp NumApp → NomProp NumProp Ici, nous avons une transitivité, donc il faut éliminer la dépendance fonctionnelle transitive, regrouper les dépendances ayant la même racine et reconstruire les relations. (1) NumApp → AdrApp, Montant, NumProp (2) NumProp → NomProp D’où Client (NumCl, NomCl) Appartement (NumApp, AdrApp, Montant, NumProp#) Propriétaire (NumProp, NomProp) Location (NumCl#, NumApp#, DateDLoc, DateFLoc)

4- Forme Normale de Boyce-Codd (BCNF) Une relation R est en BCNF si et seulement si les seules dépendances fonctionnelles élémentaires qu’elle comporte sont celles dans lesquelles une clé détermine un attribut. En d’autres termes une relation est en BCNF, si elle est en 3FN et qu’aucun attribut membre de la clé ne dépend fonctionnellement d’un attribut non membre de la clé. Mme Houneida HADDAJI

12

Cours Bases de Données

Chapitre 4: La Normalisation

Exemple 1: ADRESSE (Ville, Rue, CodePostal) Cette relation présente les DFs suivantes : Ville, Rue → CodePostal CodePostal → Ville Elle est en 3FN (car elle est en deuxième forme normale, et tout attribut n’appartenant pas à une clé ne dépendra pas d’un attribut non clé). Cette relation

n’est pas en BCNF car l’attribut ‘’Ville’’ (qui fait partie de la clé) dépend

fonctionnellement de CodePostal (qui est un attribut non membre de la clé). Exemple 2: Si on reprend l’exemple de la section précédente : Client (NumCl, NomCl) Appartement (NumApp, AdrApp, Montant, NumProp#) Propriétaire (NumProp, NomProp) Location (NumCl#, NumApp#, DateDLoc, DateFLoc) Dans ces relations, nous n’avons aucun attribut clé qui dépend d’un attribut non clé. Donc elles sont en BCNF.

IV- Conclusion Ce chapitre a été consacré à l’étude du processus de normalisation et à la présentation des différents types de dépendances fonctionnelles et de leurs types. Ces dépendances fonctionnelles sont vues comme des contraintes d’intégrité. Elles servent à réduire la redondance des données et à éviter des risques d’anomalies à travers des schémas relationnels « corrects ». Généralement, la démarche de la mise en place d’une base de données relationnelle est la suivante: 

Concevoir un schéma de données exprimé à l’aide d’un modèle de représentation « plus abstrait» que le modèle relationnel, tel que le modèle entité association



Transformer ce schéma en un schéma relationnel



S’assurer que chaque relation est au moins en troisième forme normale



Normaliser les relations qui ne le seraient pas.

Mme Houneida HADDAJI

13

Cours Bases de Données

Chapitre 4: La Normalisation

Dans le chapitre suivant nous allons étudier les possibilités d’interrogation et de création de schémas relationnels normalisés à travers le langage SQL.

Mme Houneida HADDAJI

14...