base de données 2 julie dugdale julie.dugdale@upmf-grenoble.fr material/sources: daniel bardou,...

Base de Données 2

Julie Dugdale

Julie.dugdale@upmf-grenoble.fr

Material/Sources: Daniel Bardou, Julie Dugdale &

Vanda Luengo

Cours 5 partie 2

Sommaire

La normalisation Buts de la normalisation Formes normales

Première forme normale Deuxième forme normale Troisième forme normale Forme normale de Boyce-Codd

Algorithme de Normalisation Algorithme de Normalisation – Exemple Entre Fonctionnel et Opérationnel Dénormalisation

Normalisation - Résumé

Définition:

Le processus de restructurer le modèle de données logiques pour : Eliminer les redondances, Organiser les données efficacement, Réduire le potentiel d'anomalies pendant les

opérations sur les données.

Normalisation - Résumé

Chaque nouvelle forme normale marque une étape supplémentaire de progression vers des relations présentant de moins en moins de redondance

Le but.. est d'obtenir une représentation des données présentant un minimum de redondance à l'intérieur de chaque relation et un maximum d'indépendance entre les différentes relations

Normalisation : 3FN - Résumé

Définition : troisième forme normale: Une relation est en troisième forme normale

si : - elle est en deuxième forme normale ; - tout attribut n'appartenant pas à une clé ne dépend pas d'un attribut non clé. - cad toute DF dont la partie droite n’est pas une clé doit avoir une partie gauche qui est une clé :

A, B, CB, C OK

Algorithme de Normalisation

Algorithme de Normalisation

L’algorithme de conception de schéma relationnel en 3NF

Théorème:Tout schéma relationnel peut se décomposer en schémas sous 3ième forme normale avec préservation des Dépendances Fonctionnelles

Il existe divers algorithmes de décomposition (nous n’en étudierons qu’un « classique » dû à Bernstein

Algorithme de Normalisation

4 étapes1. Calculer DF-, une Couverture Irredondante Minimale

(une CIM) de DF, en enlevant: A) les redondances des attributs de partie gauche

Est-ce que AB → D a des attributs redondants dans la partie gauche? Est-ce que cela pourrait être remplacé par B → D ou A → D?

B) les dépendances redondantes (transitivité)Si A dépend de B et B dépend de C, alors A dépend aussi de C

2. Regrouper en DFi- les dépendances fonctionnelles de

DF- ayant même partie gauche.

Algorithme de Normalisation

3. Pour chaque DFi-, construire un schéma

relationnel Ri avec l’union des attributs de DFi

-

4. Trouver les clés de DF-. Si aucune de ces clés n’est contenue dans au moins un des Ri, alors il faut rajouter un schéma relationnel Rk = {Ki K->K} avec K un clé candidate.

Résultat : Toutes les Ri sont 3NF!

Algorithme de Normalisation: Exemple On considère la relation R(A, B, C, D, E) et

l’ensemble de dépendances fonctionnelles suivant DF= {A -> BC, CD -> E, B -> D, A -> D, BE -> D}

D'abord mettre DF sous forme canonique:

(partie droite atomique)…

DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D}

Algorithme de Normalisation: Exemple Souvent utile de dessiner un graphe des

dépendances fonctionnelles de DF:

DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D}

AB

C D

E

Algorithme de Normalisation: Exemple

1. Calculer un couverture irredondante DF- (une CIM) de DF, en enlevant: A) les redondances des attributs de partie gauche B) les dépendances redondantes (transitivité)

DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D} A) Elimine BE -> D (car on a déjà B->D)

DF = {A -> B, A -> C, CD -> E, B -> D, A -> D, BE -> D}B) Elimine A -> D (car on a déjà A->B et B->D)

Donc, DF- = {A -> B, A -> C, CD -> E, B -> D}

Algorithme de Normalisation: Exemple Utile: graphe des dépendances fonctionnelles

de DF:A) Elimine BE -> D (car on a déjà B->D)B) Elimine A -> D (car A->B et B->D)

Donc, DF- = {A -> B, A -> C, CD -> E, B -> D}A

B

C D

E

Algorithme de Normalisation: Exemple2. Regrouper les dépendances fonctionnelles de DF-

ayant même partie gauche, en DFi-

DF- = {A -> B, A -> C, CD -> E, B -> D}

DF1- =

{A -> B, A -> C}

DF2- =

{CD -> E}

DF3- =

{B -> D}

Algorithme de Normalisation: Exemple3. Pour chaque DFi

-, construire un schéma relationnel Ri avec l’union des attributs de DFi

-

DF1- = {A -> B, A -> C}

DF2- = {CD -> E}

DF3- = {B -> D}

R1 = {A, B, C}

R2 = {C, D, E}

R3 = {B, D}

Algorithme de Normalisation: Exemple4. Trouver les clés de DF-. Si aucune de ces clés

n’est contenue dans au moins un des Ri, alors il faut rajouter un schéma relationnel Rk = {Ki K->K} avec K un clé candidate.

Clés = seule candidate = A

A appartient à R1 => OK

R1 = {A, B, C}

R2 = {C, D, E}

R3 = {B, D}

Résultat: Toutes les Ri sont 3NF!

Processus global de conception de base de données

On a supposé que le schéma R est donné R pourrait avoir été généré pendant la

conversion du diagramme E-A en un ensemble de tableaux.

R pourrait avoir été une seule relation contenant tous les attributs ayant un intérêt.

La normalisation découpe R en des relations plus petites.

R pourrait avoir été le résultat d'une conception ad hoc de relations, qu'on teste/convertit alors vers une forme normale.

Modèle ER et normalisation

Quand un diagramme E-A est conçu avec soin, identifiant toutes les entités correctement, les tableaux générés à partir du diagramme E-A ne devraient pas nécessiter de plus de normalisation.

Cependant, dans une conception réelle (imparfaite), il peut y avoir des dépendances fonctionnelles d'attributs non-clé d'une entité vers d'autres attributs de l'entité

Exemple : une entité employé avec les attributs numéro_département et adresse_département, et une dépendance fonctionnelle numéro_département adresse_département

Une bonne conception aurait fait du département une entité

Entre Fonctionnel et Opérationnel

Quelques conseils  Il ne faut jamais oublier qu’il y aura des utilisateurs de

la base. Il faut par conséquent être fonctionnel et opérationnel.

Fonctionnel : une base avec beaucoup de tables est difficile à gérer. Il faut optimiser les tables dans leur structure (recherche de clefs étrangères).

Opérationnel : pour les traitements, les relations de type CIF (contrainte d'intégrité référentielle) sont à privilégier.

Dénormalisation

Dénormalisation

On appelle dénormalisation l'introduction volontaire de redondance répondant principalement à des conditions d'efficacité.

Exemple; Soit le schéma relationnel suivant:Article(NumArt, desigArt, prixArt)

Commande(NumCom,DateCom, NumCli)

Ligne_Commande(NumCom,NumArt,Qtite)

Client(NumCli,NomCli,AdrCli)

Dénormalisation

Supposons que le marketing (pour l’émission de mailing) soumet très fréquemment à la base des requêtes de la forme:

Quels sont les adresses des clients ayant passé commande de tel(s) article(s)

-> On doit à chaque fois vérifier dans 4 tables pour donner la réponse* Article(NumeroArticle2, designationArticle1, prixArticle)

* Commande(NumeroCommande5,DateCommande, NumeroClient6)

* Ligne_Commande(NumeroCommande4,NumeroArticle3,Qtite)

* Client(NumeroClient7,NomClient,AdresseClient8)

Dénormalisation

Si on met l'attribut designationArt dans Commande on a besoin de deux tables

..Redondance mais efficacité

Article(NumeroArticle, designationArticle, prixArticle)* Commande(NumeroCommande,DateCommande, NumeroClient2, designationArticle1) Ligne_Commande(NumeroCommande,NumeroArticle,Qtite)* Client(NumeroClient3,NomClient,AdresseClient4)