Programme l Introduction aux BD et aux SGBD

Programme l Introduction aux BD et aux SGBD l Le modèle relationnel l Le langage de requête SQL +La conception d’une BD relationnelle © INT l Protection des informations l Perspectives des BD Evry, 29 -31 mai 2000 1

Comment concevoir une base de données ? © INT Evry, 29 -31 mai 2000 2

Dictionnaire de données l liste des rubriques +renseignements associés – des indications comme la nature et le type, – l’entité ou l’association de rattachement, – les contrôles, – la date de création et l’auteur, – les autorisations de mise à jour, – le nombre d’occurrences de valeurs, – des exemples de valeurs, – le contexte d’utilisation, –. . © INT Evry, 29 -31 mai 2000 3

Dictionnaire de données (2) l code l libellé l nature : – rubriques non calculées (NC) : par exemple le nom d’un client – les paramètres (P) : par exemple le taux de TVA – les rubriques calculées (C) : par exemple le montant T. T. C. sur une facture – les rubriques calculées incrémentées (CI) : par exemple le numéro d’une facture l © INT commentaires Evry, 29 -31 mai 2000 4

Epuration du dictionnaire de données l Rubrique ayant plusieurs significations => remplacée par plusieurs – Date peut être remplacée par date_commande, date_livraison; l Elimination des synonymes – numéro client et code_client : idem; l Rubriques décomposées en sous rubriques => décomposition – Par exemple, une adresse peut être décomposée en un numéro de rue, un nom de rue, un code postal et une ville. La notion d’adresse disparaît alors l © INT Outils d’aide à la constitution d’un DD : AGL Evry, 29 -31 mai 2000 5

Présentation du dictionnaire de données l © INT Un dictionnaire de données se présente sous forme tabulaire: Evry, 29 -31 mai 2000 6

Théorie de la normalisation l organisations : – Bains(nn, nom, prénom, qualité, date, durée, np, nomp, type région, pollution) et – Nageur(nn, nom, prénom, qualité) Plage(np, nomp, type, région, pollution) Baignade(nn, np, date, durée) © INT Evry, 29 -31 mai 2000 7

But l Mauvaise conception => – Redondance des données – Incohérence en modification – Anomalie d’insertion – Anomalie de suppression © INT Evry, 29 -31 mai 2000 8

Dépendance fonctionnelle (DF) l Modélisation – contraintes sur les données – réalité – limites à la base l Définition – Un attribut B dépend fonctionnellement de A si, étant donné une valeur de A, il lui correspond une unique valeur de B (quel que soit l’instant t) – A détermine B l © INT Notation A B Evry, 29 -31 mai 2000 9

Dépendance fonctionnelle (2) l L ’attribut A peut être composé de plusieurs attributs. – immatriculation, date no_propriétaire l DF : propriété définie sur tous les tuples, vérifiée en permanence l DF non symétrique en général – nom nn n’est pas vérifiée. Deux nageurs différents peuvent posséder le même nom (Dupont, Martin) © INT Evry, 29 -31 mai 2000 10

Exemples © INT l nn nom ? l Région np ? l nn prénom ? l nom, région np ? l nom, prénom qualité ? l nn qualité ? l np nomp ? l np région ? l np pollution ? l np type ? Evry, 29 -31 mai 2000 11

Axiomes d’Armstrong l Réflexivité – Y X l Augmentation – X Y l XZ YZ Transitivité – X Y et Y Z © INT X Y X Z Evry, 29 -31 mai 2000 12

Propriétés déduites l Union – X Y et X Z l X YZ Pseudo-transitivité – X Y et YW Z XW Z l Décomposition – X Y et Z Y © INT X Z Evry, 29 -31 mai 2000 13

Dépendance fonctionnelle élémentaire l DF + Axiomes => toutes les DF l DF élémentaire : DF non déductibles l Une dépendance fonctionnelle X A est dite élémentaire si – A n’est pas inclus dans X – il n’existe pas X’ inclus dans X tel que X’ A l famille génératrice = couverture minimale l Exemple : – nomp, région pollution – nomp pollution ou région pollution faux © INT Evry, 29 -31 mai 2000 14

Graphe de dépendances fonctionnelles © INT l noeuds = attributs l arcs = DF Evry, 29 -31 mai 2000 15

Fermeture transitive l FT = DF + DF obtenues par transitivité l Notée F+ l Nouvelles DF : – nomp, région type – nomp, région pollution. © INT Evry, 29 -31 mai 2000 16

Couverture minimale l Définition – sous ensemble minimum de dépendances fonctionnelles élémentaires permettant de générer toutes les autres l Exemple – (nn nom, nn prénom, nn qualité, np nomp, np région, nomp, région pollution, nomp, région type, nomp, région np) l Théorème – Tout ensemble de dépendances fonctionnelles admet une couverture minimale, en général non unique © INT Evry, 29 -31 mai 2000 17

Clé d’une relation l Définition – ensemble minimum d’attributs permettant de déterminer tous les autres l Soit R(A 1, A 2, . . . , An) un schéma de relation. Soit F+ l’ensemble des dépendances fonctionnelles associées à R. Soit X un sous-ensemble d’attributs de R. X est une clé de R ssi : – X A 1, A 2, . . . , An – il n’existe pas de sous-ensemble Y de X tel que Y A 1, A 2, . . . , An © INT Evry, 29 -31 mai 2000 18

Clé d’une relation (2) l Exemple – nn est une clé de la relation nageur l Remarques – Plusieurs clés peuvent être candidates pour une même relation – Dans l’écriture du schéma, la clé est soulignée © INT Evry, 29 -31 mai 2000 19

Décomposition d’une relation © INT Evry, 29 -31 mai 2000 20

Décomposition d’une relation (2) l Décomposition sans perte l Décomposition préservant les DF l Décomposition d’un schéma de relation – La décomposition d’un schéma de relation R(A 1, A 2, . . . , An) est sa substitution par un ensemble de schéma de relations R 1, R 2, . . . , Rp telles que : v © INT schéma(R) schéma(R 1) schéma(R 2) . . . schéma(Rp) Evry, 29 -31 mai 2000 21

Décomposition sans perte l La décomposition d’un schéma de relation R(A 1, A 2, . . . , An) par un ensemble de schéma de relations R 1, R 2, . . . , Rp est sans perte ssi : – R R 1>< R 2 ><. . . >< Rp l Une décomposition d’une relation en deux relations est sans perte si l’attribut de jointure est clé d’une des deux relations © INT Evry, 29 -31 mai 2000 22

Décomposition préservant les DF l La décomposition d’un schéma de relation R(A 1, A 2, . . . , An) par un ensemble de schéma de relations R 1, R 2, . . . , Rp préserve les dépendances fonctionnelles si la fermeture transitive de R est la même que l’union des fermetures transitives de chacune des relations © INT Evry, 29 -31 mai 2000 23

Formes normales © INT l Première forme normale l Deuxième forme normale l Troisième forme normale Evry, 29 -31 mai 2000 24

1ère forme normale l Définition – Une relation est en première forme normale si tous ses attributs sont atomiques. l Un attribut atomique n’est pas : – multivalué (liste de valeurs) – composé (structuré en sous-attributs) l Pour toute relation en première forme normale, un attribut représente une donnée élémentaire du monde réel © INT Evry, 29 -31 mai 2000 25

Exemple l Bains(nn, np, date, durées) – «durées» : liste des durées de baignade - attribut multivalué – Cette relation n’est donc pas en première forme normale l Solutions : – nombre de baignades limité par jour v Bains(nn, np, date, durée 1, durée 2, durée 3) – nombre illimité © INT v Bains(nn, np, date, durée) v autant de tuples qu’il y a de baignade Evry, 29 -31 mai 2000 26

Deuxième forme normale l Définition – Une relation est en deuxième forme normale ssi : © INT v elle est en première forme normale v tout attribut non clé dépend de la totalité de la clé Evry, 29 -31 mai 2000 27

Deuxième forme normale (2) l Exemple – Plage(nomp, région, type, pollution) est-elle en deuxième forme normale ? l Remarque – Toute relation contenant une clé non composée est automatiquement en deuxième forme normale si elle est en première forme normale © INT Evry, 29 -31 mai 2000 28

Troisième forme normale l Objectif : élimination des redondances dues aux dépendances fonctionnelles déduites par transitivité l Définition – Une relation est en troisième forme normale ssi : v Elle est en deuxième forme normale v Si tout attribut non clé dépend directement de la clé, il n’existe donc aucune dépendance avec un attribut non clé © INT Evry, 29 -31 mai 2000 29

Propriétés l Toute relation R admet au moins une décomposition en troisième forme normale telle que : – elle préserve les dépendances fonctionnelles – elle est sans perte l Exemple – plage(np, région, type, pollution) – Hypothèse : région type => plage n’est pas en troisième forme normale – Transformation © INT v plage(np, région, pollution) v région (région, type) Evry, 29 -31 mai 2000 30

Algorithme de décomposition en troisième forme normale l Pré : connaissance du contenu de la relation universelle + DF l Principe de l’algorithme : – A partir du graphe G des DF, calculer une couverture minimale C – Editer l’ensemble des attributs isolés dans une même relation (tous sont clés) – Recherche le plus grand ensemble X d’attributs qui détermine d’autres attributs – Editer la relation R(X, A 1, . . . , An) – Supprimer les DF X A 1, . . , X An du graphe de couverture minimale C – Supprimer les attributs isolés de C – Reprendre l’opération à partir de l’étape 3 jusqu’à ce que C soit vide © INT Evry, 29 -31 mai 2000 31

Conclusions © INT l Importance de la normalisation des relations l Autres formes normales Evry, 29 -31 mai 2000 32

Modèle entité-association l Concepts et définitions – Entité – Type d’entité – Domaine – Attribut – Clé – Association – Type d’association © INT Evry, 29 -31 mai 2000 33

Traduction d’un modèle entitéassociation vers un modèle relationnel l © INT Exemple Evry, 29 -31 mai 2000 34

Entité l Entité = objet (concret ou abstrait) l Existe en tant que tel, sans être soumis à l’existence d’une autre entité l Exemples : – Produit X – Module d’enseignement de mathématiques – Lion © INT Evry, 29 -31 mai 2000 35

Type d’entité l Regroupement des entités en fonction de leurs caractéristiques communes => Type d’entité l Exemple : – Regrouper éléphant, tortue et zèbre en un type d’entité : « animal » – Informations en commun (par leur structure) : poids, taille, alimentation, . . © INT Evry, 29 -31 mai 2000 36

Domaine l Ensemble de valeur l A partir de type communs : entier, chaîne de caractères, . . . l Plus finement : – par extension en listant les valeurs possibles – en définissant des plages de valeurs l Exemples : – âge : entier compris entre 0 et 130 – situation familiale : ensemble de valeurs (‘célibataire’, ‘marié’, ‘veuf’, ‘divorcé’) © INT Evry, 29 -31 mai 2000 37

Attribut l Attribut : propriété d’un type d’entité l Pour chaque entité d’un type d’entité une valeur est associée à cet attribut l Exemple : – Type d’entité animal, Entité éléphant : © INT v Taille = 2, 30 m v Poids = 1000 kg v Alimentation = 'végétarien' Evry, 29 -31 mai 2000 38

Clé l Attribut (ou groupe d’attributs) particulier d’un type d’entité l Différencie toutes les entités d’un même type d’entité l Exemple : – Numéro d’étudiant – Plaque d’immatriculation © INT Evry, 29 -31 mai 2000 39

Représentation graphique © INT Evry, 29 -31 mai 2000 40

Association l Association d’entités : regroupement d’entités l Traduit plutôt une action entre les entités l Exemple : – Alimenter : association entre un le lion Clarence et le gardien John – Se baigner: association entre le nageur Gaël et la plage de Binic © INT Evry, 29 -31 mai 2000 41

Type d ’association l Regroupement des associations en type d’association l Peuvent posséder des attributs l Attribut d’un type d’association: propriété dépendant de tous les types d’entités intervenant dans l’association l Exemple: la date de prise en charge d’un animal par un gardien dépend de v gardien + v © INT animal Evry, 29 -31 mai 2000 42

Représentation graphique l © INT Attention : une association ne possède pas de clé!!!! Evry, 29 -31 mai 2000 43

Exemples l © INT Un enseignant est responsable d’un module. . Evry, 29 -31 mai 2000 44

Exemples (2) l Une personne peut être le parent (père ou mère) d’une autre . . . © INT Evry, 29 -31 mai 2000 45

Exemples (3) l A vous de jouer : le type d’association "inscrit" défini entre les types d’entités "étudiant" et "module" a pour attribut le numéro d’ordre d’inscription pour ce module (l’étudiant s’inscrit pour la 1ère, la 2ème fois à ce module, . . ) © INT Evry, 29 -31 mai 2000 46

Remarques l On ne crée jamais de type d’entité pour représenter un ensemble d’entité réduit à un seul élément l Un attribut n’apparaît qu’à une seule place s’il n’y a pas de polysèmes (mots ayant plusieurs sens). Pas de duplication de l’information! l Toutes les rubriques du DD ne deviennent pas attributs du modèle. Attribut : rubrique non paramètre, non calculée © INT Evry, 29 -31 mai 2000 47

Différentes configurations d’associations © INT l Associations binaires l Associations n-aires Evry, 29 -31 mai 2000 48

Associations binaires de type 1 : 1 l A une entité E peut correspondre par l’association A au plus une entité de F l A une entité F peut correspondre par l’association A au plus une entité de E © INT Evry, 29 -31 mai 2000 49

Exemple © INT Evry, 29 -31 mai 2000 50

Associations binaires de type 1: n l A une entité E peut correspondre par l’association A plusieurs entités de F l A une entité F peut correspondre par l’association A au plus une entité de E © INT Evry, 29 -31 mai 2000 51

Exemple © INT Evry, 29 -31 mai 2000 52

Associations binaires de type n: m l A une entité E peut correspondre par l’association A plusieurs entités de F l A une entité F peut correspondre par l’association A plusieurs entités de E © INT Evry, 29 -31 mai 2000 53

Exemple © INT Evry, 29 -31 mai 2000 54

Association n-aires l Plus de deux entités l Par exemple, type d’association ternaires. l Case d’emploi du temps déterminée que si l’on a – l’horaire – le jour – la matière – la classe Type d’association quaternaire © INT Evry, 29 -31 mai 2000 55

Cardinalités d’un couple entitéassociation l La cardinalité d’un couple entité-association E, A est un couple de nombre m, n où : – m définit le nombre minimum d’association de type A pouvant exister pour une entité E – n définit le nombre maximum d’association de type A pouvant exister pour une entité E © INT Evry, 29 -31 mai 2000 56

Valeurs caractéristiques l Valeurs caractéristiques du minimum : – 0 – 1 – Entier plus grand que 1 l Valeurs caractéristiques du maximum: – 1 – n – Entier plus grand que 1 © INT Evry, 29 -31 mai 2000 57

Représentation graphique © INT Evry, 29 -31 mai 2000 58

Spécialisation/généralisation d’entités l Spécialisation d’un type d’entité – Définir des sous-types d’entités – Affiner la représentation des données l Généralisation – Créer un sur-type d’entité – Mettre en facteur les attributs communs à plusieurs types d’entités © INT Evry, 29 -31 mai 2000 61

Exemple Personne Femme © INT Homme Effectuer Service Evry, 29 -31 mai 2000 62

Intervention de la théorie de la normalisation © INT l Types d’entités l Cardinalités l Attributs des types d’association Evry, 29 -31 mai 2000 63

Type d’entité l Clé d’un type d’entité E : ensemble d’attributs qui détermine fonctionnellement tous les autres attributs de ce type d’entité l © INT Un type d'entité doit vérifier les trois formes normales Evry, 29 -31 mai 2000 64

Cardinalités l Association ? , 1 – Si la cardinalité du couple (E, A) où E est un type d’entité et A est un type d’association est (? , 1) => une occurrence de E participe au plus à une association de A – Cette occurrence détermine donc les autres entités participant à cette association – Toute clé de E détermine fonctionnellement les clés des autres types d’entité participants © INT Evry, 29 -31 mai 2000 65

Attributs de type d’association l Si T est un attribut d’un type d’association A => T dépend fonctionnellement de l’ensemble des clés de chacun des types d’entité participants l Exemple: – date_affect du type d’association diriger défini entre les deux types d’entités Enseignant et département: num_ens, num_dept date_affect l © INT => aucun type d’association ne peut posséder de clé Evry, 29 -31 mai 2000 66

Construction du schéma entitéassociation © INT l Méthode descendante l Méthode ascendante Evry, 29

Méthode descendante l Point de départ : univers du discours l Etapes: – DD + règles de gestion – Recensement des entités et des associations (intuitif) – Placement des attributs au sein des types d’entités et d’association – Calcul des cardinalités (DF) © INT Evry, 29 -31 mai 2000 68

Méthode descendante (2) l Etapes (suite): – Vérification : v Respects des règles du formalisme E/A – Absence de répétition d’attribut – Une clé par type d’entité – Vérifier les DF – Vérifier les cardinalités /règles de gestion © INT Evry, 29 -31 mai 2000 69

Méthode descendante (3) l Etapes (suite): – Vérification : v Normalisation : – Vérifier la 3 FN pour tous les types d’entité – Vérifier les types d’association n-aire : décomposition en plusieurs types d’association de degré plus faible – Supprimer les types d’associations qui se déduisent par transitivité d’autres types d’associations – Validation : © INT v Par l’utilisateur v Par l’analyse des traitements Evry, 29 -31 mai 2000 70

Méthode ascendante l Point de départ : sorties l Méthode de Flory l Etapes : – Recensement des rubriques (existantes ou souhaitées) => DD – Recher les DF (matrice carrée) – Couverture minimale – Elimination des circuits © INT Evry, 29 -31 mai 2000 71

Méthode ascendante (2) l Etapes (suite) : – Construction du schéma : v Recherche des clés primaires : – S : ensemble des attributs correspondant à des sommets du graphe des dépendances fonctionnelles G – B : ensemble des attributs étant le but d’au moins une dépendance fonctionnelle et origine d’au moins une dépendance fonctionnelle – Chaque attribut de S ou de B est une clé primaire d’un des types d’entité du modèle entité-association © INT Evry, 29 -31 mai 2000 72

Méthode ascendante (2) l Etapes (suite) : – Construction du schéma : v Recherche des types d’entité : – Chaque attribut de S et de B devient clé d ’un type d’entité E – Si s est dans S, les attributs déterminés par s n’étant ni dans S ni dans B sont attributs de E; v Recherche des types d’associations : – Deux clés définies liées par un arc dans G induisent une association de type 1, n – La cardinalité (? , 1) est rattachée à l’entité dont la clé est source de la dépendance fonctionnelle a b © INT a ? , 1 1, ? Evry, 29 -31 mai 2000 b 73

Méthode ascendante (2) l Etapes (suite) : – Construction du schéma : v Recherche des types d’associations : – un arc de G provenant de plusieurs sommets (n sommets) vers un attributs non clé se traduit par un type d’association n-aire A de type n: m associant les types d’entités correspondant aux sommets © INT Evry, 29 -31 mai 2000 74

Méthode ascendante (2) l Etapes (suite) : – Construction du schéma : v Recherche des types d’associations : – un arc G provenant de plusieurs sommets (p sommets) et allant vers un attribut clé constitue un type d’association (p+1)-aire; – Ajouter les associations sans attribut (non déductibles des DF) – Validation © INT Evry, 29 -31 mai 2000 75

Remarques l Peu de types d’association ternaire ou plus l Abus de langage – Entité à la place de type d’entité – Association à la place de type d’association – Entité = objet ; Entité = occurrence d’entité – Type d’entité = concept abstrait détaché de la réalité © INT l Modèle entité-association = modèle entité-relation l Représentation graphique variable l Rédiger des hypothèses! Evry, 29 -31 mai 2000 76

Règle 1 l Type d'entité -> relation l Attribut atomique -> constituant (attribut) l Attributs composites -> n constituants l Attribut(s) clé(s) -> clé candidate l Exemple – Etudiant(No_etu, Nom_etu, Adr_etu) – Cours(Code, Libellé) – Prof(Nom_prof, Prénom) © INT Evry, 29 -31 mai 2000 78

Règle 2 l Clé associée à E 1 (ou E 2) -> attribut de E 2 (ou E 1) l Attributs de R -> attributs de E 2 (ou E 1) l Exemple: – Etudiant(No_etu, Nom_etu, Adr_etu, No_plaque) © INT Evry, 29 -31 mai 2000 79

Règle 3 l Clé associée à E 1 -> attribut de E 2 l Attributs de R -> attributs de E 2 l Exemple – Cours(Code, Libelle, Prof_resp) © INT Evry, 29 -31 mai 2000 80

Règle 4 l Création d'une nouvelle relation R l Clé de E 1 + Clé de E 2 -> clé de R l Attributs de R-> Attributs de R l Exemple – Inscription(No_étudiant, No_cours, Date_inscription) © INT Evry, 29 -31 mai 2000 81

Règle 5 l Création d'une nouvelle relation R l Attribut multivalué -> constituant l Clé du type d'entité associé -> constituant l Clé de la relation : tout le schéma l Exemple: – Fonctions(Nom_enseig, Fonction) © INT Evry, 29 -31 mai 2000 82

Résultat © INT l Prof(Nom_prof, Prénom) l Etudiant(No_etu, Nom_etu, Adr_etu, No_plaque) l Cours(Code, Libelle, Prof_resp) l Inscription(No_étudiant, No_cours, Date_inscription) l Fonctions(Nom_enseig, Fonction) Evry, 29 -31 mai 2000 83

Remarques © INT l Règles de traduction => relations en 3ème FN l Bon schéma E/A => bon schéma relationnel Evry, 29 -31 mai 2000 84