II 1 Thorie de la normalisation n Rsolution

II. 1 Théorie de la normalisation n Résolution de problèmes de redondance de données

Exemple de mauvaise conception n Redondance de données – 27/02/2021 problèmes de mise à

Solution : décomposition 27/02/2021 © Robert Godin. Tous droits réservés. 3

II. 1. 1 Décomposition sans perte n Une décomposition binaire d'une table T en

Exemple de décomposition avec perte n Vente Client Vente. Mal. Foutue 27/02/2021 © Robert

II. 1. 2 Dépendance fonctionnelle n A 1, A 2, …, An T B

Exemples pour Vente no. Client nom. Client, no. Téléphone no. Commande date. Commande no.

II. 1. 2. 1 Inférence et choix des dépendances fonctionnelles Considérer toutes les dépendances

Dépendance élémentaire n Dépendance fonctionnelle complètement non triviale no. Client, nom. Client n Dépendance

Dépendances superflues (redondantes) no. Client nom. Client, no. Téléphone no. Commande date. Commande no.

II. 1. 2. 2 Axiomes d'Armstrong n A 1. Réflexivité – – n A

II. 1. 2. 3 Représentation par un diagramme à bulles n D = {no.

Fermeture de X (X+) X+ = {x | X x} n Exemples n 27/02/2021

Fonction Fermeture sur diagramme à bulles n Exemple : Fermeture(B, no. Commande) 27/02/2021 ©

Fonction Fermeture. . . 27/02/2021 © Robert Godin. Tous droits réservés. 15

Fonction Fermeture. . . n { no. Commande }+ = {no. Commande, no. Client,

Fonction Dérivable n D |= X Y si et seulement si Y X+ n

II. 1. 2. 4 Couverture minimale n Une couverture minimale, notée min(D), satisfait :

Fonction Min(D) n Eliminer colonnes de droites superflues D = { no. Client, nom.

Fonction Min(D). . . n Réduire les déterminants (utiliser Dérivable) D = { no.

Fonction Min(D). . . n Eliminer les dépendances superflues (utiliser Dérivable) D = {

Fonction Min(D). . . n Union des dépendances de même déterminant D = {

Fonction Min(D). . . n Résultat final Min(D) = { no. Client nom. Client,

II. 1. 2. 5 Dépendances fonctionnelles et clés candidates n X est une clé

II. 1. 3 Normalisation 27/02/2021 © Robert Godin. Tous droits réservés. 25

Patron général 27/02/2021 © Robert Godin. Tous droits réservés. 26

II. 1. 3. 1 Forme normale Boyce-Codd (FNBC) n Table en FNBC – Pour

II. 1. 3. 1. 1 Algorithme de décomposition en FNBC 27/02/2021 © Robert Godin.

Exemple avec Vente S = {Vente} 27/02/2021 S = {Vente. Reste, Client} © Robert

Suite S = {Vente. Reste, Client} 27/02/2021 S = {Client, Commande, Vente. Reste 2}

Suite S = {Client, Commande Vente. Reste 2} 27/02/2021 S = {Client, Commande, Article,

Schéma final (S) n Client (no. Client, nom. Client, no. Téléphone) n Commande(no. Commande,

Résultat en UML 27/02/2021 © Robert Godin. Tous droits réservés. 33

II. 1. 3. 2 Troisième forme normale n FNBC ne préserve pas toujours DF

Troisième forme normale (3 FN, third normal form) n Pour toute dépendance fonctionnelle élémentaire,

Adresse est en 3 FN n ville est première – 27/02/2021 fait partie de

3 FN n Proposition. Il y a toujours une décomposition en 3 FN qui

II. 1. 3. 2. 1 Algorithme de synthèse pour la 3 FN 27/02/2021 ©

Exemple avec Ventes D = { no. Client, nom. Client, no. Téléphone ; no.

Exemple (suite) Idem décomposition en FNBC dans cet exemple 27/02/2021 © Robert Godin. Tous

Exemple avec Adresse Résultat en 3 FN mais pas en FNBC 27/02/2021 © Robert

II. 1. 3. 3 Vue d'ensemble des formes normales n 2 FN : interdit

1 FN : valeurs atomiques 27/02/2021 © Robert Godin. Tous droits réservés. 43

Modèle relationnel étendu ou NF 2 (Non First Normal Form) n Colonnes à valeurs

Extensions objet n Table d ’objets – n Oracle 8 : CREATE TABLE nom

Restrictions de plus en plus fortes 27/02/2021 © Robert Godin. Tous droits réservés. 46

II. 1. 3. 4 Quatrième forme normale et dépendances multi-valuées n n Film est

Dépendance multivaluée (multivalued dependency) n A 1, A 2, …, An T B 1,

Définition formelle n (a 1, a 2, …, an, b 1, b 2, …,

Dépendances multivaluées n Proposition (règle du complément) – 27/02/2021 © Robert Godin. Tous droits

Généralisation directe du patron de décomposition 27/02/2021 © Robert Godin. Tous droits réservés. 51

Exemple : Film 27/02/2021 © Robert Godin. Tous droits réservés. 52

Exemple (suite) 27/02/2021 © Robert Godin. Tous droits réservés. 53

Dépendance multivaluée élémentaire n Une dépendance multivaluée A 1, A 2, …, An T

Quatrième forme normale (4 FN) n Pour toute dépendance multivaluée élémentaire, X T Y,

II. 1. 3. 4. 2 Algorithme de décomposition en 4 FN 27/02/2021 © Robert

II. 1. 3. 4. 3 Dépendances multivaluées emboîtées n sigle. Cours id. Etudiant ne

II. 1. 3. 5 Cinquième forme normale n Décomposition n-aire (n > 2) 27/02/2021

Dépendance de jointure ou dépendance produit (join dependancy) n * {X 1, X 2,

Cinquième forme normale (5 FN) n Pour toute dépendance de jointure non triviale *{X

II. 1. 3. 6 Dépendance généralisée n Sous forme de tableau : 27/02/2021 ©

II. 1. 3. 7 Formale normale domaine-clé (FNDC) n Toutes les contraintes déductibles de

II. 1. 3. 8 Remarques au sujet de la normalisation Normalisé : pas toujours

Slides: 63

Download presentation

II. 1 Théorie de la normalisation n Résolution de problèmes de redondance de données n Par décomposition n Caractérisation des problèmes – dépendances fonctionnelles, multivaluées, . . . n Contexte relationnel n Transposable à d ’autres contextes 27/02/2021 © Robert Godin. Tous droits réservés. 1

II. 1. 1 Décomposition sans perte n Une décomposition binaire d'une table T en deux tables T 1 et T 2 est sans perte si : –T n = T 1 T 2 Exemple : – 27/02/2021 Vente = Client Vente. Reste © Robert Godin. Tous droits réservés. 4

II. 1. 2 Dépendance fonctionnelle n A 1, A 2, …, An T B 1, B 2, …, Bm – pour toute instance de T – mêmes valeurs pour les colonnes A 1, A 2, …, An mêmes valeurs pour les colonnes B 1, B 2, …, Bm n Déterminant : partie gauche A 1, A 2, …, An n Notation… Signifie réellement : 27/02/2021 – T {B , …, B } {A 1, A 2, …, A } © Robert Godin. Tous droits réservés. n 1 2 m 6

Exemples pour Vente no. Client nom. Client, no. Téléphone no. Commande date. Commande no. Client, no. Commande, no. Article quantité no. Article description, prix. Unitaire 27/02/2021 © Robert Godin. Tous droits réservés. 7

II. 1. 2. 1 Inférence et choix des dépendances fonctionnelles Considérer toutes les dépendances ? n Sinon lesquelles ? n Équivalences logiques éclatement n no. Client nom. Client, no. Téléphone no. Client nom. Client union no. Commande, no. Article quantité no. Client no. Téléphone no. Commande quantité no. Article quantité 27/02/2021 © Robert Godin. Tous droits réservés. 8

Dépendance élémentaire n Dépendance fonctionnelle complètement non triviale no. Client, nom. Client n Dépendance fonctionnelle pleine ( « full » ) no. Client, no. Commande nom. Client n Élémentaire = complètement non triviale & pleine 27/02/2021 © Robert Godin. Tous droits réservés. 9

Dépendances superflues (redondantes) no. Client nom. Client, no. Téléphone no. Commande date. Commande no. Client, no. Commande nom. Client Par transitivité 27/02/2021 © Robert Godin. Tous droits réservés. 10

II. 1. 2. 2 Axiomes d'Armstrong n A 1. Réflexivité – – n A 2. Augmentation – – – n Si {B 1, B 2, …, Bm} {A 1, A 2, …, An } alors A 1, A 2, …, An B 1, B 2, …, Bm Si A 1, A 2, …, An B 1, B 2, …, Bm et {D 1, D 2, …, Dp} {C 1, C 2, …, Cr} alors A 1, A 2, …, An, C 1, C 2, …, Cr B 1, B 2, …, Bm, D 1, D 2, …, Dp A 3. Transitivité – – – 27/02/2021 Si A 1, A 2, …, An B 1, B 2, …, Bm et B 1, B 2, …, Bm C 1, C 2, …, Cr alors A 1, A 2, …, An C 1, C 2, …, Cr © Robert Godin. Tous droits réservés. 11

II. 1. 2. 3 Représentation par un diagramme à bulles n D = {no. Client nom. Client, no. Téléphone ; no. Commande no. Client, date. Commande ; no. Commande, no. Article quantité ; no. Article description, prix. Unitaire} 27/02/2021 © Robert Godin. Tous droits réservés. 12

Fermeture de X (X+) X+ = {x | X x} n Exemples n 27/02/2021 – {no. Client}+ = {no. Client, nom. Client, no. Téléphone} – {no. Article}+ = {no. Article, description, prix. Unitaire} – {prix. Unitaire}+ = {prix. Unitaire} – { no. Commande }+ = {no. Commande, no. Client, date. Commande, nom. Client, no. Téléphone} – {no. Commande, no. Article}+ = {no. Client, nom. Client, no. Téléphone, no. Commande, date. Commande, no. Article, description, prix. Unitaire, quantité} © Robert Godin. Tous droits réservés. 13

Fonction Dérivable n D |= X Y si et seulement si Y X+ n D |= no. Commande nom. Client ? – – 27/02/2021 { no. Commande }+ = {no. Commande, no. Client, date. Commande, nom. Client, no. Téléphone} {nom. Client} {no. Commande, no. Client, date. Commande, nom. Client, no. Téléphone} © Robert Godin. Tous droits réservés. 17

II. 1. 2. 4 Couverture minimale n Une couverture minimale, notée min(D), satisfait : 27/02/2021 – 1) Min(D) équivalent à D – 2) Dépendances élémentaires et non superflues – 3) Pas deux dépendances avec même déterminant © Robert Godin. Tous droits réservés. 18

Fonction Min(D) n Eliminer colonnes de droites superflues D = { no. Client, nom. Client, no. Téléphone ; no. Commande no. Client ; no. Commande date. Commande ; no. Commande, no. Article, no. Client quantité ; no. Article description, prix. Unitaire ; no. Commande nom. Client } 27/02/2021 © Robert Godin. Tous droits réservés. 19

Fonction Min(D). . . n Réduire les déterminants (utiliser Dérivable) D = { no. Client nom. Client, no. Téléphone ; no. Commande no. Client ; no. Commande date. Commande ; no. Commande, no. Article, no. Client quantité ; no. Article description, prix. Unitaire ; no. Commande nom. Client } 27/02/2021 © Robert Godin. Tous droits réservés. 20

Fonction Min(D). . . n Eliminer les dépendances superflues (utiliser Dérivable) D = { no. Client no. Téléphone ; nom. Client, no. Commande no. Client ; no. Commande date. Commande ; no. Commande, quantité ; 27/02/2021 no. Article description, © Robert Godin. Tous droits réservés. prix. Unitaire ; 21

Fonction Min(D). . . n Union des dépendances de même déterminant D = { no. Client nom. Client, no. Téléphone ; no. Commande no. Client ; no. Commande date. Commande ; no. Commande, no. Article quantité ; no. Article description, prix. Unitaire } 27/02/2021 © Robert Godin. Tous droits réservés. 22

Fonction Min(D). . . n Résultat final Min(D) = { no. Client nom. Client, no. Téléphone ; no. Commande date. Commande ; no. Client , no. Commande, no. Article quantité ; no. Article description, prix. Unitaire } 27/02/2021 © Robert Godin. Tous droits réservés. 23

II. 1. 2. 5 Dépendances fonctionnelles et clés candidates n X est une clé candidate de T (U) – U = X+ et il n'y a pas de Y X tel que U = Y+ n Pas d ’algorithmes efficaces (Maier, 83) n Heuristique – n partir des déterminants Visualiser par diagramme à bulles 27/02/2021 © Robert Godin. Tous droits réservés. 24

II. 1. 3. 1 Forme normale Boyce-Codd (FNBC) n Table en FNBC – Pour toute dépendance fonctionnelle élémentaire, X T Y, le déterminant X est une clé candidate de T – Pour toute dépendance fonctionnelle non triviale, X T Y, le déterminant X est une superclé de T n Rappel : clé unique (unique key)ou superclé (superkey) – n X est une clé unique (ou superclé) de T si deux lignes de T ne peuvent avoir les mêmes valeurs pour toutes les colonnes de X Schéma relationnel est en FNBC – 27/02/2021 toutes les tables du schéma sont en FNBC © Robert Godin. Tous droits réservés. 27

Schéma final (S) n Client (no. Client, nom. Client, no. Téléphone) n Commande(no. Commande, date. Commande, no. Client) n Ligne. Commande(no. Commande, no. Article, quantité) n Article(no. Article, description, prix. Unitaire) 27/02/2021 © Robert Godin. Tous droits réservés. 32

Troisième forme normale (3 FN, third normal form) n Pour toute dépendance fonctionnelle élémentaire, X T Y, le déterminant X est une clé candidate de T ou Y fait partie d ’une clé candidate n Colonne première – 27/02/2021 fait partie d'une clé candidate © Robert Godin. Tous droits réservés. 35

Exemple avec Ventes D = { no. Client, nom. Client, no. Téléphone ; no. Commande no. Client ; no. Commande date. Commande ; no. Commande, no. Article, no. Client quantité ; no. Article description, prix. Unitaire ; no. Commande nom. Client } Min(D) = { no. Client nom. Client, no. Téléphone ; no. Commande no. Client , date. Commande ; no. Commande, no. Article quantité ; no. Article description, prix. Unitaire } 27/02/2021 © Robert Godin. Tous droits réservés. 39

Modèle relationnel étendu ou NF 2 (Non First Normal Form) n Colonnes à valeurs complexes n Table enchâssée n – valeur = une table – Oracle 8 : NESTED TABLE Collection de valeurs – n Oracle 8 : VARRAY (Varying Array) Valeur composée – 27/02/2021 Oracle 8 : CREATE TYPE nom. Type AS. . . © Robert Godin. Tous droits réservés. 44

II. 1. 3. 4 Quatrième forme normale et dépendances multi-valuées n n Film est en FNBC mais pas en 4 FN Redondance due à dépendance multivaluée 27/02/2021 – titre nom. Producteur – titre nom. Acteur © Robert Godin. Tous droits réservés. 47

Dépendance multivaluée (multivalued dependency) n A 1, A 2, …, An T B 1, B 2, …, Bm – 27/02/2021 à chacune de valeurs de A 1, A 2, …, An est associé un ensemble de valeurs de B 1, B 2, …, Bm sans relation directe avec les autres colonnes C 1, C 2, …, Cp de la table © Robert Godin. Tous droits réservés. 48

Définition formelle n (a 1, a 2, …, an, b 1, b 2, …, bm, c 1, c 2, …, cp) et (a 1, a 2, …, an, b'1, b'2, …, b'm, c'1, c'2, …, c'p) T (a 1, a 2, …, an, b 1, b 2, …, bm, c'1, c'2, …, c'p) T n ('La vie est belle', 'Elda. Ferri', 'Robert Benigni') et ('La vie est belle', 'Gianluigi Braschi', 'Nicoletta Braschi') Film ('La vie est belle', 'Elda. Ferri', 'Nicoletta Braschi') Film 27/02/2021 © Robert Godin. Tous droits réservés. 49

Dépendance multivaluée élémentaire n Une dépendance multivaluée A 1, A 2, …, An T B 1, B 2, …, Bm est élémentaire si : ¨ il y a d'autres colonnes dans la table ¨ {A 1, ¨ A 2, …, An} { B 1, B 2, …, Bm } = Ø complètement non triviale ¨ la partie gauche est minimale ¨ 27/02/2021 dépendance pleine © Robert Godin. Tous droits réservés. 54

Quatrième forme normale (4 FN) n Pour toute dépendance multivaluée élémentaire, X T Y, le déterminant X est une clé candidate de T n Proposition – 27/02/2021 4 FN FNBC © Robert Godin. Tous droits réservés. 55

II. 1. 3. 4. 3 Dépendances multivaluées emboîtées n sigle. Cours id. Etudiant ne tient pas dans Cours mais « surgit » dans Cours 2 ! id. Etudiant, code. Prof nb. Cours 27/02/2021 © Robert Godin. Tous droits réservés. 57

Dépendance de jointure ou dépendance produit (join dependancy) n * {X 1, X 2, . . . , Xn} dans T si – n Dépendance de jointure triviale – n T = X 1 (T) X 2 (T) … Xn (T) si une des parties, Xi, est l'ensemble de toutes les colonnes de T Proposition (DMV cas particulier d ’une DJ) – 27/02/2021 X T Y *{(X Y), (X, U-X-Y)} © Robert Godin. Tous droits réservés. 59

Cinquième forme normale (5 FN) n Pour toute dépendance de jointure non triviale *{X 1, X 2, . . . , Xn} dans T , chacun des Xi contient une clé candidate de T n Proposition – 27/02/2021 5 FN 4 FN © Robert Godin. Tous droits réservés. 60

II. 1. 3. 7 Formale normale domaine-clé (FNDC) n Toutes les contraintes déductibles de – – n domaines clés candidates Considère aussi fragmentation horizontale 27/02/2021 © Robert Godin. Tous droits réservés. 62

II. 1. 3. 8 Remarques au sujet de la normalisation Normalisé : pas toujours satisfaisant n Autres considérations de conception n – – – valeurs nulles colonnes dérivées choix des colonnes codage performance en lecture n dénormalisation – 27/02/2021 . . . © Robert Godin. Tous droits réservés. 63

Related presentations