Programme

1. Programme • Introduction aux BD et aux SGBD • Le modèle relationnel • Le langage de requête SQL • La conception d’une BD relationnelle • Protection des informations • Perspectives des BD

2. Comment concevoirune base de données ?

3. Dictionnaire de données • 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, • ....

4. Dictionnaire de données (2) • code • libellé • 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 • commentaires

5. Epuration du dictionnaire de données • Rubrique ayant plusieurs significations => remplacée par plusieurs • Date peut être remplacée par date_commande, date_livraison; • Elimination des synonymes • numéro client et code_client : idem; • 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 • Outils d’aide à la constitution d’un DD : AGL

6. Présentation du dictionnaire de données • Un dictionnaire de données se présente sous forme tabulaire:

7. Théorie de la normalisation • 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)

8. But • Mauvaise conception • Redondance des données • Incohérence en modification • Anomalie d’insertion • Anomalie de suppression

9. Dépendance fonctionnelle (DF) • Modélisation • contraintes sur les données • réalité • limites à la base • 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 • Notation A  B

10. Dépendance fonctionnelle (2) • L ’attribut A peut être composé de plusieurs attributs. • immatriculation, date  no_propriétaire • DF : propriété définie sur tous les tuples, vérifiée en permanence • 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)

11. nn  nom ? nn  prénom ? nom, prénom  qualité ? nn  qualité ? np  nomp ? np  région ? np  pollution ? np  type ? Région  np ? nom, région  np ? Exemples

12. Axiomes d’Armstrong • Réflexivité • Y  X  X  Y • Augmentation • X  Y  XZ  YZ • Transitivité • X  Y et Y  Z  X  Z

13. Propriétés déduites • Union • X  Y et X  Z  X  YZ • Pseudo-transitivité • X  Y et YW  Z  XW  Z • Décomposition • X  Y et Z  Y  X  Z

14. Dépendance fonctionnelle élémentaire • DF + Axiomes => toutes les DF • DF élémentaire : DF non déductibles • 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 • famille génératrice = couverture minimale • Exemple : • nomp, région  pollution • nomp  pollution ou région  pollution faux

15. Graphe de dépendances fonctionnelles • noeuds = attributs • arcs = DF

16. Fermeture transitive • FT = DF + DF obtenues par transitivité • Notée F+ • Nouvelles DF : • nomp, région  type • nomp, région  pollution.

17. Couverture minimale • Définition • sous ensemble minimum de dépendances fonctionnelles élémentaires permettant de générer toutes les autres • 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) • Théorème • Tout ensemble de dépendances fonctionnelles admet une couverture minimale, en général non unique

18. Clé d’une relation • Définition • ensemble minimum d’attributs permettant de déterminer tous les autres • Soit R(A1, A2, ..., 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  A1, A2, ..., An • il n’existe pas de sous-ensemble Y de X tel que Y  A1, A2, ..., An

19. Clé d’une relation (2) • Exemple • nn est une clé de la relation nageur • Remarques • Plusieurs clés peuvent être candidates pour une même relation • Dans l’écriture du schéma, la clé est soulignée

20. Décomposition d’une relation

21. Décomposition d’une relation (2) • Décomposition sans perte • Décomposition préservant les DF • Décomposition d’un schéma de relation • La décomposition d’un schéma de relation R(A1, A2, ..., An) est sa substitution par un ensemble de schéma de relations R1, R2, ..., Rp telles que : • schéma(R)  schéma(R1)  schéma(R2)  ... schéma(Rp)

22. Décomposition sans perte • La décomposition d’un schéma de relation R(A1, A2, ..., An) par un ensemble de schéma de relations R1, R2, ..., Rp est sans perte ssi : • R  R1>< R2 ><...>< Rp • Une décomposition d’une relation en deux relations est sans perte si l’attribut de jointure est clé d’une des deux relations

23. Décomposition préservant les DF • La décomposition d’un schéma de relation R(A1, A2, ..., An) par un ensemble de schéma de relations R1, R2, ..., 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

24. Formes normales • Première forme normale • Deuxième forme normale • Troisième forme normale

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

26. Exemple • 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 • Solutions : • nombre de baignades limité par jour • Bains(nn, np, date, durée1, durée2, durée3) • nombre illimité • Bains(nn, np, date, durée) • autant de tuples qu’il y a de baignade

27. Deuxième forme normale • Définition • Une relation est en deuxième forme normale ssi: • elle est en première forme normale • tout attribut non clé dépend de la totalité de la clé R(A,B,C,D) interdit ok

28. Deuxième forme normale (2) • Exemple • Plage(nomp, région, type, pollution) est-elle en deuxième forme normale ? • Remarque • Toute relation contenant une clé non composée est automatiquement en deuxième forme normale si elle est en première forme normale

29. Troisième forme normale • Objectif : élimination des redondances dues aux dépendances fonctionnelles déduites par transitivité • Définition • Une relation est en troisième forme normale ssi: • Elle est en deuxième forme normale • Si tout attribut non clé dépend directement de la clé, il n’existe donc aucune dépendance avec un attribut non clé R(A,B,C,D,E) interdit ok

30. Propriétés • 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 • Exemple • plage(np, région, type, pollution) • Hypothèse : région  type => plage n’est pas en troisième forme normale • Transformation • plage(np, région, pollution) • région (région, type)

31. Algorithme de décomposition en troisième forme normale • Pré : connaissance du contenu de la relation universelle + DF • 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, A1, ..., An) • Supprimer les DF X  A1, .., 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

32. Conclusions • Importance de la normalisation des relations • Autres formes normales

33. Modèle entité-association • Concepts et définitions • Entité • Type d’entité • Domaine • Attribut • Clé • Association • Type d’association

34. Traduction d’un modèle entité-association vers un modèle relationnel • Exemple

35. Entité • Entité = objet (concret ou abstrait) • Existe en tant que tel, sans être soumis à l’existence d’une autre entité • Exemples : • Produit X • Module d’enseignement de mathématiques • Lion

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

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

38. Attribut • Attribut : propriété d’un type d’entité • Pour chaque entité d’un type d’entité une valeur est associée à cet attribut • Exemple : • Type d’entité animal, Entité éléphant : • Taille = 2,30 m • Poids = 1000 kg • Alimentation = 'végétarien'

39. Clé • Attribut (ou groupe d’attributs) particulier d’un type d’entité • Différencie toutes les entités d’un même type d’entité • Exemple : • Numéro d’étudiant • Plaque d’immatriculation

40. Représentation graphique

41. Association • Association d’entités : regroupement d’entités • Traduit plutôt une action entre les entités • 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

42. Type d ’association • Regroupement des associations en type d’association • Peuvent posséder des attributs • Attribut d’un type d’association: propriété dépendant de tous les types d’entités intervenant dans l’association • Exemple: la date de prise en charge d’un animal par un gardien dépend de gardien + animal

43. Représentation graphique • Attention : une association ne possède pas de clé!!!!

44. Exemples • Un enseignant est responsable d’un module ....

45. Exemples (2) • Une personne peut être le parent (père ou mère) d’une autre ...

46. Exemples (3) • 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, ..)

47. Remarques • On ne crée jamais de type d’entité pour représenter un ensemble d’entité réduit à un seul élément • 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! • Toutes les rubriques du DD ne deviennent pas attributs du modèle. Attribut : rubrique non paramètre, non calculée

48. Différentes configurations d’associations • Associations binaires • Associations n-aires

49. Associations binaires de type 1:1 • À une entité E peut correspondre par l’association A au plus une entité de F • À une entité F peut correspondre par l’association A au plus une entité de E

50. Exemple