Bases de Donnes Relationnelles Normalisation dune base de
Bases de Données Relationnelles Normalisation d’une base de données relationnelle (Chapitre 5)
Qu’est-ce qu’une BD relationnelle ‘correcte’ ? Un ensemble de relations tel que: n n n chaque relation décrit un fait élémentaire avec les seuls attributs qui lui sont directement liés il n'y a pas de redondance d’information, génératrices de problèmes lors des MAJ il n'y a pas de perte d ’information F Personne (nom, prénom), Adresse (no, rue, ville) Qui habite oú ? Impossible de répondre !
Qu’est-ce qu’une BD relationnelle ‘incorrecte’ ? Une relation n’est pas correcte si: n n n elle implique des répétitions au niveau de sa population elle pose des problèmes lors des MAJ insertions / modifications / suppressions Les conditions pour qu'une relation soit correcte peuvent être définies formellement: => règles de normalisation
Normalisation n Processus de transformation d'une relation posant des problèmes lors des MAJ en relations ne posant pas de problèmes On mesure la qualité d'une relation par son degré de normalisation 1 NF, 2 NF, 3 NF, BCNF, 4 NF, etc.
Formes normales: 1 FN n n Une relation est en 1 FN si chaque valeur de chaque attribut de chaque tuple est une valeur simple (tous les attributs sont simples et monovalués). Exemple: Livraison (N°fourn, adr. f, N°prod, prix. p, qté)
Exemple de mauvaise relation en 1 FN Livraison (N°fourn, adr. f, N°prod, prix-p, qté) 3 22 22 Lausanne 52 Bienne 10 15 Bienne 25 10 3 Lausanne 65 10 5 12 25 10 10 5 15 20 n L’adresse du fournisseur ne dépend pas du produit. n Le prix du produit ne dépend pas du fournisseur REDONDANCES n Anomalies de mise à jour
Exemple de mauvaise relation en 1 FN Livraison (N°fourn, adr. f, N°prod, prix-p, qté) 3 22 22 n n Lausanne 52 Bienne 10 15 Bienne 25 10 3 Lausanne 65 10 5 12 25 10 10 5 15 20 Si un fournisseur change d’adresse et qu’un seul tuple (une seule ligne) est mis à jour: incohérence Si une nouvelle ligne est insérée pour un fournisseur connu, mais avec une adresse différente: incohérence
Exemple de mauvaise relation en 1 FN Livraison (N°fourn, adr. f, N°prod, prix-p, qté) 3 22 22 n n Geneve 52 Bienne 10 15 Bienne 25 10 3 Lausanne 65 10 5 12 25 10 10 5 15 20 Si un fournisseur change d’adresse et qu’un seul tuple (une seule ligne) est mis à jour: incohérence Si une nouvelle ligne est insérée pour un fournisseur connu, mais avec une adresse différente: incohérence
Normalisation ou traduction EA Schéma EA Niveau conceptuel (EA) VALIDATION Règles Schéma EA valide Niveau logique (Relationnel) Schéma Relationnel TRADUCTION EA - R NORMALISATION Schéma relationnel normalisé
Exemple Fournisseur (NF, Nom, Adr) Produit (NP, Nom, Couleur) Livraison (NP, NF, Date, Qté, Tél) Décomposition des relations non satisfaisantes NORMALISA TION 0 -n Nom Adr. Livraison Date Qté Tel 0 -n Produit Nom Coul. VALIDATION Règles Fournisseur 0 -n Nom Adr. Tel Livraison Qté Date TRADUCTION Produit (NP, Nom, Couleur) Fournisseur (NF, Nom, Adr, Tél) Livraison (NP, NF, Date, Qté) 0 -n Produit Nom Coul.
Formalisation du problème n L’adresse d’un fournisseur ne dépend que du fournisseur, . . … DÉPENDANCE FONCTIONNELLE n n (DF) soit une table T (x, y, z) il existe une DF: x y si et seulement si dans T à une même valeur de x correspond toujours une même valeur de y
Formalisation n T: X Y Z x 1 y 1 z 1. . . x 1 y 1 z 2. . . n X Y: X détermine Y n X: source de la DF, Y: cible de la DF n la source peut être un ensemble d’attributs: (nom, prénom) Y dépend de X adresse
Propriétés des DF n Transitivité: si X Y et Y Z u n X Z (DF déduite) quelque soit A (DF non élémentaire) Reflexivité: si Y X alors u (DF n alors Augmentation: si X Y alors (A, X) Y u n (Armstrong's Axioms) X Y triviale) On ne s’intéresse qu’aux DF élémentaires non déduites et non triviale.
Graphe des DF n Pour chaque table il faut connaître les DF intéressantes. Il est facile alors de les représenter sous forme de graphe: graphe minimum des DF (orienté) n Exemple: T (A, B, C, D, E) E A C D E B (º E A, B, C) E C E A B C D
Exemple de graphe des DF Livraison (N°fourn, adr-f, N°prod, prix-p, qté) n N°fourn adr-f u n N°prod prix-p u n l ’adresse d ’un fournisseur ne dépend que du fournisseur le prix d ’un produit ne dépend que du produit (N°fourn, N°prod) qté u la quantité livrée dépend du produit et du fournisseur [faux: N°fourn qté , N°prod qté ] N°fourn adr-f qté N°prod prix-p
DFs et identifiants n n n Le graphe minimum des DF permet de trouver les identifiants de la table L’identifiant d ’une table est l ’ensemble (minimal) des nœuds du graphe minimum à partir desquels on peut atteindre tous les autres nœuds (via les DF) Pour que ce soit faux il faudrait qu’il y ait deux lignes avec la même valeur de l’ « identifiant » et des valeurs différentes pour les autres attributs, ce qui est en contradiction avec les DF. Exemple: T 1 (A, B, C, D, E) E A B C D
DFs et identifiants n Autre exemple: T 2 (A, B, C, D, E, X, Y) X A Y B C E D T 2 (A, B, C, D, E, X, Y)
DFs et identifiants n Autre exemple: T 3 (X, Y, Z) n DF: X YZ, YZ X Y Z X
Normalisation n Que faire si une table n ’est pas « normalisée » ? DECOMPOSITION n La table doit être remplacée par un ensemble de tables (plus petites: moins d’attributs)
Décomposition d'une relation n n Soit une relation non satisfaisante, trouver un ensemble de relations satisfaisantes qui décrive les mêmes informations Pour vérifier qu’une relation est décomposable sans perte d’information: Ensemble de sous. P Relation relations obtenues par projection * n (jointure) Nombreux algorithmes de décomposition Mais attention: les relations sont normalisées, mais peuvent être sémantiquement non significatives.
Projection u Opération unaire u la syntaxe : A 1, A 2, …An(R) (avec A 1, … An des attributs de R) u la sémantique : tous les n-uplets de R, mais avec seulement les attributs A 1, … An u le schéma : schéma( A 1, A 2, …An(R)) = A 1, … An u d’éventuelles remarques : | A 1, A 2, …An(R)| <= |R| u un exemple : S B b a a C c a a D d b c B, D(S) B b a a D d b c
Jointure u Opération binaire u la syntaxe : R S u la sémantique : Si schéma(R) schéma(S) = A 1, …, An, Si t 1 R et t 2 S sont égaux sur A 1, . . An, on les « recolle » (avec t 1 et t 2 des n-uplets) Si schéma(R) schéma(S) = , c’est le produit cartésien u le schéma : schéma(R S) = schéma(R) schéma(S)
Exemple R A a a b B b c b S B b a a C c a a D d b c R S A B C D a b c d b b c d
Méthode formelle de décomposition n Théorème de Heath T (x, y, z) est décomposable sans perte d’information en T 1 (x, y) et si x y T 2 (x, z)
Application de Heath Livraison (N°fourn, adr. f, N°prod, prix. p, qté) N°fourn N°prod Adr. f. n n n qté prix. p N°fourn adr. f. (N°fourn, adr. f. ) ok (N°fourn, N°prod, prix. p. , qté) N°prod prix. p (N°prod, prix. p) ok (N°prod, N°fourn, qté) N°prod, N° fourn qté ok
Qualité d’une décomposition n Une « bonne » décomposition est une décomposition 1) sans perte d ’information 2) sans perte de DF n Sans perte de DF: toute DF doit être dans l’une des tables obtenues par décomposition
Sans perte d ’information n n La « recomposition » de la table à partir des « morceaux » doit redonner la table initiale soit T (x, y, z) décomposée en T 1 ( x, y ) T 2 (x, z ) tel que: T 1 = PROJECTION [x, y] T T 2 = PROJECTION [x, z ] T n la décomposition est sans perte d ’informations si et seulement si : T = JOINTURE (T 1, T 2)
Exemple: bonne décomposition n T (Nom. Emp, adresse, poste, age) Zoé Lausanne secrétaire 27 Armand Genève secrétaire 32 Marie Bienne directeur 38 T 1 (Nom. Emp, adresse, poste) T 2 (Nom. Emp, age) Zoé Lausanne secrétaire Zoé 27 Armand Genève secrétaire Armand 32 Marie Bienne directeur Marie 38 T = JOINTURE (T 1, T 2)
Exemple: mauvaise décomposition T 12 (Nom. Emp, adresse, poste) T 22 (poste, age) Zoé Lausanne secrétaire 27 Armand Genève secrétaire 32 Marie Bienne directeur 38 JOINTURE (T 12, T 22) = Zoé Lausanne secrétaire 27 Zoé Lausanne secrétaire 32 Armand Genève secrétaire 27 Armand Genève secrétaire 32 Marie Bienne directeur 38 ¹T
Exemple: perte de DF n T (Nom. Emp, adresse, CP) Zoé Lausanne 1016 Armand Genève 1020 Marie Bienne 1030 adresse Nom. Emp CP T 1 (Nom. Emp, CP) T 2 (Nom. Emp, adresse) Zoé 1016 Zoé Lausanne Armand 1020 Armand Genève Marie 1030 Marie Bienne
Exemple: perte de DF n n JOINTURE (T 1, T 2) =T u sans perte d ’information MAJ de adresse en T 2 u DF CP adresse u incohérence entre T 1 et T 2 si pas de contrôle par application
Formes normales: 1 FN n n Une relation est en 1 FN si chaque valeur de chaque attribut de chaque tuple est une valeur simple (tous les attributs sont simples et monovalués). Exemple: Livraison (N°fourn, adr. f, N°prod, prix. p, qté)
2ème forme normale: 2 FN n Permet d ’éliminer les attributs qui ne décrivent pas l ’ « objet » traduit par la relation Livraison (N°fourn, adr. f, N°prod, prix. p, qté) N°fourn N°prod n mélange la description: Adr. f. qté prix. p - de la livraison ( ≠fourn. , ≠produit, quantité-livrée) - du fournisseur ( ≠fourn. , adr. -fourn) - du produit (≠produit, prix)
Exemple de mauvaise relation en 1 FN Livraison (N°fourn, adr. f, N°prod, prix-p, qté) 3 22 22 Lausanne 52 Bienne 10 Bienne 25 3 Lausanne 65 10 15 5 10 12 25 10 15 20
2ème forme normale: définition Livraison (N°fourn, adr. f, N°prod, prix. p, qté) N°fourn adr. f n n N°prod qté prix. p des DF partent de composants de l ’identifiant: Livraison n’est pas en 2 FN une table est en 2 FN si - elle est en 1 FN, et - chaque attribut qui ne fait pas partie de l’identifiant dépend d’un identifiant entier
Application de Heath Livraison (N°fourn, adr. f, N°prod, prix. p, qté) N°fourn N°prod Adr. f. n n n qté prix. p N°fourn adr. f. (N°fourn, adr. f. ) ok (N°fourn, N°prod, prix. p. , qté) N°prod prix. p (N°prod, prix. p) ok (N°prod, N°fourn, qté) N°prod, N° fourn qté ok
3 FN: 3ème forme normale n n Permet d’éliminer des sous-relations incluses dans une relation Exemple: Fournisseur (N°fourn, ville, pays) N°fourn ville n pays doit être décomposée en F (N° fourn, ville) G (ville, pays)
Exemple n Fournisseur (N°fourn. , ville, pays) 21 22 3 Lausanne Paris France 4 Lausanne 5 Lausanne Suisse
3ème forme normale: définition n Fournisseur (N°fourn. , ville, pays) N°fourn ville n n pays Profondeur de l ’arbre des DF > 1 : pas en 3 FN: si T est en 2 FN et chaque attribut qui ne fait pas partie de l’identifiant dépend directement d’un identifiant entier
3ème forme normale: définition n Plusiers identifiants: 3 FN: chaque attribut qui ne fait pas partie de un des plusiers identifiants dépend directement du identifiant entier Fournisseur (N°fourn, nom-fourn, N°produit, prix) N°fourn N°produit nom-fourn prix
FNBC Forme normale de Boyce-Codd (BCNF) n Généralise la 3 FN aux relations avec plusieurs identifiants Fournisseur (N°fourn, nom-fourn, N°produit, prix) Une table est en FNBC si elle est en 3 FN et si toute source complète de DF (élémentaire) est un identifiant entier n
FNBC: example Fournisseur n Fournisseur (N°fourn, nom-fourn, N°produit, prix) N°fourn N°produit nom-fourn prix est en 3 FN mais pas en FNBC n le passage en FNBC n’est pas toujours possible sans perte de dépendances
Décomposition Fournisseur (N°fourn, nom-fourn, N°produit, prix) n N°fourn N°produit nom-fourn prix § F 1 (N°fourn, nom-fourn) n F 2 (N°fourn, N°produit, prix) F 2’ (nom-fourn, N°produit, prix) ou
FNBC: example Place n Place (N°Etud, Matière, Rang) N°Etud Matière Rang n Identifiants : 1) N°Etud + Matière 2) Rang + Matière n Place est en 3 FN et est en FNBC
FNBC contre-example n Enseignement (Nom. Etud, Matière, Prof) Nom. Etud Matière Prof n Identifiants : 1) Nom. Etud + Matière 2) Nom. Etud + Prof n Enseignement est en 3 FN mais n’est pas en FNBC
Exemple n Problème avec suppression u Perte d'information Rossier RDB Aberer Rossier Programm. Odersky Blanc RDB Blanc Perret Spaccapietra Programm. Guerraoui RDB Guerraoui
Décomposition Enseignement (Nom. Etud, Matière, Prof) n Soit la décomposition: T 1 (Prof, Matière) n T 2 (Nom. Etud, Prof) T 1 + T 2 : sans perte d ’information mais avec perte de la DF: (Nom. Etud, Matière) Prof n il faut ajouter la C. I. : un étudiant suit une matière donnée avec un seul professeur
Exemple Rossier Aberer RDB Aberer Rossier Odersky Programm. Odersky Blanc Spaccapietra RDB Blanc Guerraoui Blanc Aberer mauvais ! Spaccapietra Programm. Guerraoui
3 FN: définition plus general n Plusiers identifiants et DF elementaire A->B u Cond 1: A est un identifiant u Cond 2: B fait part d'un identifiant n FNBC si Cond 1 n 3 NF (plusiers identifiants) si Cond 1 OR Cond 2 n Fournisseur (N°fourn, nom-fourn, N°produit, prix) N°fourn N°produit nom-fourn prix
Quatrième Forme Normale (4 FN) n Dépendance multivaluée (DM) A B a { b 1, … b 1+x} A B x=1: A C A B B A B A C B A necessary "trivial"
4 FN: exemple et décomposition Catalogue (N°Article, Couleur, Taille) N°Article Couleur N°Article Taille n Décomposition : A C 1 T 1 A C 2 T 2 A C 2 T 1 A C 1 T 2 R 1 (N°Article, Couleur) R 2 (N°Article, Taille) (stronger version of Heath)
Méthode formelle de décomposition n Théorème de Heath (stronger) T (x, y, z) est décomposable sans perte d’information en T 1 (x, y) et si x T 2 (x, z) y (and therefore also x z)
4 FN: sémantique n n permet de séparer des fait indépendants qui auraient été réunis dans une même relation exemple: u- il existe plusieurs cours u - tout cours peut être assuré par plusieurs professeurs u - tout cours possède un ensemble de livres de référence n alors cours (intitulé, nom-prof. , titre-livre) n donne une représentation très redondante, qu ’il convient de remplacer par décomposition
4 FN: exemple Cours intitulé RDB n nom-prof titre-livre {Aberer, Spaccapietra} {Ullman, Date} cours (intitulé, nom-prof. , titre-livre) RDB Aberer Ullman RDB Aberer Date RDB Spaccapietra Ullman RDB Spaccapietra Date
4 FN: définition n cours (intitulé, nom-prof. , titre-livre) n décomposition: u cours-p (intitulé, nom-prof. ) u cours-1 (intitulé, titre-livre) n n Formalisation: dans cours (intitulé, nom-prof. , titrelivre) il existe une dépendance multivaluée intitulé nom-prof, titre-livre Une relation est en 4 FN s ’il n ’existe pas de dépendance multivaluée qui ne soit pas une dépendance fonctionnelle
4 FN: définition formelle n Soit une relation R (x, y, z) z pouvant être vide n Il y a Dépendance multivaluée (DM) x ---->> y n si à toute valeur de x correspond un ensemble de valeurs de y qui est totalement indépendant de z u Remarque: n DF est un cas particulier de DM R est en 4 FN si elle est en 1ère FN et si toute DF ou DM de R a pour source un identifiant entier de R u Remarque: 4 FN implique BCNF (FNBC)
Cinquième Forme Normale (5 FN) n Permet de décomposer une relation en faits élémentaires n exemple: n Peut être décomposable en u V 1 u V 2 u V 3 Vins (buveur, cru, producteur) (buveur, cru) (buveur, producteur) (cru, producteur) Avec Vins = V 1 |X| V 2 |X| V 3 n On dit qu’il y a dépendance de jointure
Conception d’une BD relationnelle (niveau conceptuel) n Méthode "classique" n Objectif: arriver à un ensemble de relations tel que - chaque relation décrit un type d’ « objet » avec les seuls attributs qui lui sont directement liés - les redondances d ’information (génératrices de problèmes lors des mises à jour) sont éliminées
Conception d’une BD relationnelle n Méthode: Décomposition par projections sans perte d ’information et sans perte de dépendances n n n Outils: - dépendances (fonctionnelles, multivaluées, de jointure) - règles de normalisation Limites: - ne garantit pas que le schéma soit celui qu’il faut - ne garantit pas que le schéma soit efficace ( « overnormalization » )
Conception d’une BD relationnelle n n Méthode "moderne" Conception d’un schéma entité-association, puis traduction de celui-ci en schéma relationnel
- Slides: 60