COURS PECA Normalisation dune relation Frdric Gava MCF

  • Slides: 24
Download presentation
 COURS PECA Normalisation d’une relation Frédéric Gava (MCF) gava@univ-paris 12. fr LACL, bâtiment

COURS PECA Normalisation d’une relation Frédéric Gava (MCF) gava@univ-paris 12. fr LACL, bâtiment P 2 du CMC, bureau 223 Université de Paris XII Val-de-Marne 61 avenue du Général de Gaulle 94010 Créteil cedex

Introduction Lors de la définition d’un schéma conceptuel dans le modèle relationnel, le choix

Introduction Lors de la définition d’un schéma conceptuel dans le modèle relationnel, le choix des relations est primordiale. Ce choix doit être guidé par les dépendances qui existent entre les données, dans la réalité et qui sont des contraintes que doivent vérifier les données effectivement dans la base. Exemple : Produit(NºP, Nom. P, Couleur, Poids) Fournisseur(NºF, Nom. F, Adresse, Tél) Livraison(NºF, NºP, Quantité, Date, Tél) DF = NºP {Nom. P, Couleur, Poids} NºF {Nom. F, Adresse, Tél} NºF, NºP, Date Quantité, Tél Problèmes : Si un fournisseur n’a pas fait de livraison, on ne connaît pas son nº de tél S’il fait plusieurs livraisons, le nº de tél sera stocké plusieurs fois S’il faut changer de nº de tél, il faut le faire autant de fois qu’il apparaît dans la table des livraisons. Ce schéma est mal défini 2

Définitions (1) Définition : Normaliser une relation consiste à la représenter sous une forme

Définitions (1) Définition : Normaliser une relation consiste à la représenter sous une forme qui respecte certains critères afin de s’assurer de l’intégrité des données La normalisation est un moyen fondamentale mis à la disposition du concepteur de la base pour éviter un bon nombre d’erreur Définition : Une dépendance est une contrainte exprimé par une règle qui doivent vérifier les données pour que la BD soit dans un état cohérent. Exemple : un salarié travail dans un et un seul service. Nous avons 3 sortes de dépendance Fonctionnelle (forme normale 1, 2 et 3) Multi-valué (forme norme 4) Jointure (forme normal 5) Définition : Une DF traduis le fait qu’à une valeur d’une donnée, on associe dans une relation, une valeur au plus d’une autre donnée à un instant considéré 3

Décomposer une relation (1) Étant donnée une relation non satisfaisante (répétitions, pbs d’intégrités etc.

Décomposer une relation (1) Étant donnée une relation non satisfaisante (répétitions, pbs d’intégrités etc. ) on peut trouver un sous-ensemble de sous-relation satisfaisant et qui décrivent les mêmes informations NOM STATUT AGE Exemple : GAVA NOM STATUT AGE COURS GAVA MCF 26 Parallélisme HAINS Pr 40 Sécurité HAINS Pr 40 Sémantique MCF HAINS Pr 26 40 NOM COURS GAVA Parallélisme HAINS Sécurité HAINS Sémantique 4

Décomposer une relation (2) On dira qu’une décomposition est « bonne » si on

Décomposer une relation (2) On dira qu’une décomposition est « bonne » si on peut retrouver les informations de la relation initiale à partir des sous-relations Pour retrouver les informations d’une relation à partir des relations « filles » , on utilisera l’opérateur de jointure naturelle 5

Jointure naturelle L’opération de jointure naturelle permet de composer des sous-relations pour en obtenir

Jointure naturelle L’opération de jointure naturelle permet de composer des sous-relations pour en obtenir une plus grosse relation : R 1(X, Y) R 2(Y, Z)=R(X, Y, Z) C’est donc un lien entre plusieurs tables disposant de colonnes commune sémantiquement Chaque ligne de R est fabriquée avec une ligne de R 1 et une ligne de R 2 de telle sorte que les valeurs sur les attributs de Y soient identiques dans les ligne de R 1 et de celle de R 2 Elle permet donc de recomposer une relation qui avait été préalablement décomposée (voir forme normale) Une décomposition est dite « sans perte d’informations » si les jointures naturelles des sous-relations redonne la relation initiale 6

Jointure et produit cartésien Produit cartésien : concaténation de toutes lignes de la première

Jointure et produit cartésien Produit cartésien : concaténation de toutes lignes de la première table avec toutes lignes de la seconde table. Table 1 Table 2 Produit a x a, x Exemple b y c a, y b, x b, y c, x Jointure : c, y lien entre 2 tables disposant d’au moins une colonne commune (sémantiquement). On associe a chaque ligne de la première table toutes lignes de la seconde table Exemple Table 1 Table 2 Jointure a 1, b 1, c 1 a 2, b 1 b 2, c 2 a 2, b 1, c 1 a 3, b 2 b 3, c 3 a 3, b 2, c 2 7

Exemple Jointure Nom Prénom Numéro avocat R 1 = Collard Bebert 1 Vergès Jacquy

Exemple Jointure Nom Prénom Numéro avocat R 1 = Collard Bebert 1 Vergès Jacquy 2 Numéro avocat Procès Résultat HLM Perdu 2 Serial Killer Gagné 1 Outreau Perdu 2 Du siècle Gagné R 2 = 1 R 2 = Nom Prénom Numéro avocat Procès Résultat Collard Bebert 1 HLM Perdu Vergès Jacquy 2 Serial Killer Gagné Collard Bebert 1 Outreau Perdu Vergès Jacquy 2 Du siècle Gagné 8

Différentes jointures Définition : une équi-jointure est une jointure dont la condition est une

Différentes jointures Définition : une équi-jointure est une jointure dont la condition est une comparaison d’égalité de 2 colonnes appartenant aux 2 tables Définition : une theta-jointure est une jointure dont la condition est une comparaison autres que l’égalité de 2 colonnes appartenant aux 2 tables Définition : une jointure multiple met en relation plus de 2 tables. Définition : une auto-jointure est une jointure d’une table avec elle-même 9

Définitions (2) Définition : Une DF est élémentaire si un sous-ensemble de la clé

Définitions (2) Définition : Une DF est élémentaire si un sous-ensemble de la clé n’est pas source de la DF. Exemple : A, B, C D est élémentaire s’il existe pas les DF A, B D ; A, C D ; B, C D et aussi A D ; B D ; C D Définition : Une DF A B est directe (non transitive) s’il n’existe pas un autre attribut C tel que A C et C B Définition : Une relation est normalisée si aucun attribut n’est présent plus d’une fois dans la relation et si aucun attribut n’est luimême décomposable en d’autres attributs et qu’il n’est pas luimême une relation Théorème de Heath : Une relation R(X, Y, Z) est décomposable SPI en 2 sous-relations R 1(X, Y) et R 2(X, Z) s’il existe pour R 1 une DF X Y ou pour R 2 une DF X Z (c’est le cas si X est une clé 10 de R 1 ou de R 2)

Formes normales 1 Toute relation normalisée est en FN 1 R(N°_Client, N°_Article, Nom_Client, Nom_Article)

Formes normales 1 Toute relation normalisée est en FN 1 R(N°_Client, N°_Article, Nom_Client, Nom_Article) N°_Client Nom_Client ; N°_Article Nom_Article ; N°_Client, N°_Article Nom_Article Table suivante : 23 23 26 A 5 A 6 A 5 Fred Bob Chaise Table Chaise Il faudrait plutôt les relations suivantes : Client(N°, Nom) Article(N°, Nom) Commande(N°_Article, N°_Client) 11

Forme normal 2 Une relation est en FN 2 si Elle est en FN

Forme normal 2 Une relation est en FN 2 si Elle est en FN 1 si tout attribut n’appartenant pas à une clé dépend élémentairement d’une clé Suffisant ? R(N°_Client, Nom_Client, N°_Repr, Nom_Repr) DF= N°_Client Nom_Client, N°_Repr, Nom_Repr ; N°_Repr Nom_Repr 25 26 Fred R 1 Patrick R 1 Nicole Repr(N°_Repr, Nom) Client(N°_Client, Nom, N°_Repr) 12

Exemple Fournisseur(Nom, Adresse, Tél, Nom. Produit, Prix) Nom, Nom. Produit Prix, Adresse, Tél Nom

Exemple Fournisseur(Nom, Adresse, Tél, Nom. Produit, Prix) Nom, Nom. Produit Prix, Adresse, Tél Nom Adresse FN 2 ? ok pour Nom, Nom. Produit Prix par contre Nom, Nom. Produit Adresse n’est pas élémentaire car Nom Adresse donc que FN 1 Décomposition possible et SPI d’après théorème de Heath Fournisseur(Nom, Adresse, Tél) Catalogue(Nom_Fournisseur, Nom. Produit, Prix) 13

Forme normal 3 Une relation est en FN 3 si Elle est en FN

Forme normal 3 Une relation est en FN 3 si Elle est en FN 2 (donc en FN 1) Tout attribut n’appartenant pas à une clé dépend de façon direct d’une clé Définition : un ensemble d’attribut X est une clé candidate d’une relation R si et seulement si {X}+=R Remarque : lorsqu’une relation est en FN 3 aucune DF n’est issue d’un sous-ensemble de la clé et aucune DF n’est issue d’un attribut non clé vers un autre 14

Exemple Fournisseur(Nom, Pays, Ville) Nom Ville, Pays Ville Pays (pas de ville homonyme dans

Exemple Fournisseur(Nom, Pays, Ville) Nom Ville, Pays Ville Pays (pas de ville homonyme dans des pays différents) FN 2 ? Oui car l’attribut Nom est la seule clé FN 3 ? Non car Nom Pays n’est pas direct (on peut passer par Ville). Il y a donc des répétitions de noms de pays. Décomposition SPI Fournisseur(Nom, Ville) Géographie(Ville, Pays) 15

Forme normal 4 Aussi appelé Forme normal de Boyse-Codd Une relation R est en

Forme normal 4 Aussi appelé Forme normal de Boyse-Codd Une relation R est en en FNBC si tout attribut dépend de façon élémentaire et direct d’une clé Remarque : quand une relation n’a qu’une clé candidate (donc qu’une unique clé appelé atomique), alors si elle est en FN 3 cela implique qu’elle est aussi en FNBC Remarque : FNBC FN 3 FN 2 FN 1 Remarque : Il existe une FN 5 mais nous la verrons pas dans ce cours (trop rare) 16

Exemple Catalogue(N°F, Nom. P, Prix) 2 clés (N°F, Nom. P) et (Nom. F, Nom.

Exemple Catalogue(N°F, Nom. P, Prix) 2 clés (N°F, Nom. P) et (Nom. F, Nom. P) DF : N°F, Nom. P Nom. F, Prix Nom. F, Nom. P N°F, Prix N°F Nom. F N°F FN 3 ? Oui car seul Prix n’appartient pas à une clé et dépend élémentairement et directement de 2 clés FNBC ? Non car Nom. F ne dépend pas élémentairement d’une clé : N°F, Nom. P Nom. F n’est pas élémentaire car il existe et N°F Nom. F et Nom. F ne dépend pas de l’autre clé Décomposition possible Fournisseur(N°F, Nom. F) avec N°F Nom. F et si l’on veut Nom. F N°F Catalogue(N°F, Nom. P, Prix) avec N°F, Nom. P Prix 17

Exemple complet La relation Place(N°Etu, Matière, Rang) représente le rang de chaque étudiant par

Exemple complet La relation Place(N°Etu, Matière, Rang) représente le rang de chaque étudiant par matière. On suppose qu’il n’y a pas d’ex-aequo. Nous avons les DF suivantes : Rang, Matière N°Etu (pas d’ex-aequo) N°Etu, Matière Rang 2 clés candidates : (Rang, Matière) et (N°Etu, Matière) Quelles sont les formes normales vérifiées ? Tout attribut appartient à une clé donc c’est en FN 3 Les 2 DF sont directes et élémentaires avec des membres gauches (de la DF) qui sont des clés donc la relation Place est en FNBC 18

Version simplifiée (pratique) On ne touche pas à la définition de FN 1 Quand

Version simplifiée (pratique) On ne touche pas à la définition de FN 1 Quand on n’a qu’une clé (clé primaire ou atomique) Définition d’une relation R en FN 2 : La relation R est en FN 1 Toutes les DF issues de la clé sont élémentaires Définition d’une relation R en FN 3 : La relation R est en FN 2 Toutes les DF issues de la clé sont directes Pas de FNBC 19

Atelier Pour la BD suivante : R 1(N°_Client, N°_Produit, Qt. Comm, Nom_Produit) R 2(N°_Commande,

Atelier Pour la BD suivante : R 1(N°_Client, N°_Produit, Qt. Comm, Nom_Produit) R 2(N°_Commande, N°_Produit, Qt. Comm) R 3(N°_Client, Nom_Repr) R 4(N°_Produit, Nom_Produit, N°_Atelier, Nom_Chef_Atelier) R 5(N°_Client, Nom_Client, N°_Repr) R 6(N°_Produit, N°_Fournisseur, Prix, Nom_Fournisseur) Trouvez les clés externe et déduisez les DF Trouvez les formes normales des relations suivantes 20

Correction Atelier 1. N°_Produit Nom_Produit donc pas élémentaire donc FN 1 2. N°_Commande, N°_Produit

Correction Atelier 1. N°_Produit Nom_Produit donc pas élémentaire donc FN 1 2. N°_Commande, N°_Produit Qt_Commandé donc FN 3 3. N°_Client Nom_Client, Nom_Repr donc FN 3 4. N°_Produit Nom_Produit et N°_Atelier Nom_Chef_Atelier donc indirect donc FN 2 5. N°_Client Nom_Client, Nom_Repr et N°_Repr Nom_Repr donc indirect donc FN 2 6. N°_Produit Prix et N°_Fournisseur Nom_Four donc pas élémentaire donc FN 1 21

Société de Tourisme (1) Une société de tourisme possède une vingtaine de centres sur

Société de Tourisme (1) Une société de tourisme possède une vingtaine de centres sur le territoire français et une douzaine dans d’autres pays. Cette société a implanté une BD dont vous trouverez la structure ci-dessous : Cette BD a, entre autre, pour but de mieux cibler les activités pratiquées par les clients dans les centres Un centre est ouvert pendant un certain nombre de semaines de l’année suivant son implantation Un animateur est en général un saisonnier, il n’anime qu’une seule activité et est rattaché à un seul centre Un responsable d’activité est un permanent de la société. Il peutêtre responsable de plusieurs activités et travaille suivant les saisons dans différents centres. Cependant, un responsable d’une activité est responsable de cette activité dans tous les centres où il travaille (lorsque cette activité existe) Donner et justifier les formes normales des relations 22

Société de Tourisme (2) 1. 2. Client(N°, Nom, Adresse, Age) Inscription(N°_client, N°_Centre, N°_Semaine, N°_activité,

Société de Tourisme (2) 1. 2. Client(N°, Nom, Adresse, Age) Inscription(N°_client, N°_Centre, N°_Semaine, N°_activité, N°_animateur) 3. Animateur(N°_Animateur, N°_Activité, N°_Responsable, Nom_Responsable, Adresse_Responsable, N°_Centre, N°_SS, Nom) 4. Centre(N°, Nom, Adresse) 5. Responsable(N°, N°_SS, Ancienneté, Nom, Adresse) 6. Employé(N°_SS, Nom, Adresse, Date_Naissance) 7. Activité(N°, Nom) 8. Fonctionnement(N°_Centre, Type_Appart, N°_Semaine) 9. Séjour(N°_Client, N°_Centre, N°_Semaine, Type_Appart) 10. Responsabilité(N°_Responsable, N°_Centre, N°_Activité) 11. Statistique(N°_Responsable, N°_Centre, N°_Activité, Nombre_Heures_Effectués) 23

Correction Tourisme 1. FN 3 2. FN 3 3. FN 2 4. FN 3

Correction Tourisme 1. FN 3 2. FN 3 3. FN 2 4. FN 3 5. FN 1 6. FN 3 7. FN 3 8. FN 3 9. FN 1 10. FN 3 11. FN 3 24