Cours …

1. UE FLIN601 - BD Année 2007 - 2008 Cours …

2. Plan • Généralités • Rappels modèle relationnel • Algèbre relationnelle, SQL • Lescontraintes • Retour sur les fondements logiques • Conception • Organisation physique • Transactions

3. Conception • Un schéma relationnel de base de données peut contenir des centaines de tables, des milliers d'attributs .. • Problème : comment éviter des erreurs de conception ? • Deux solutions (complémentaires) • dériver à partir de modèles conceptuels • théorie de la normalisation

4. Conception

5. Conception • UML (rappels) nom de la classe attributs Voiture type : string marque : string couleur : string opérations Implémentations repeindre(c: Couleur) déplacer(d : longueur) Méthodes

6. Conception • UML (rappels) Multiplicité et rôles d'une association employeur employé Personne Société emploie 1..* * nom date de nais. n°SS adresse nom adresse travaille-pour chef 0..1 encadre 1..* travailleur

7. Conception • UML (rappels) Classe association Possède-des-actions Entreprise Personne 1..* * nom date de nais. adresse capital nom adresse actionnaire Ligne de portefeuille quantité

8. Conception Sémantique Collection/Élément • UML (rappels) Agrégation Pays 1 Forêt 1 1..n Région 1..n Arbre 1 1..n Site

9. Conception • UML (rappels) Composition Association particulière tout /partie L’existence du composant est assujettie à celle de l’objet composite 1 * DeptEnseignement Université

10. Conception • UML (rappels) Généralisation / Spécialisation B spécialise A ; A généralise B A Définition : L’extension de A (ses instances) contient l’extension de B B A B Conséquence : une instance de B possède les propriétés de A éventuellement sous une forme affinée

11. Conception • UML (rappels) Contraintes de généralisation / Spécialisation Employé {incomplet} {complet, disjoint} Cotisant SalariéCdi Vacataire NonCotisant

12. Conception • UML (rappels) Contraintes de généralisation / Spécialisation Employé {incomplet} {complet, disjoint} Cotisant SalariéCdi Vacataire NonCotisant

13. Personne NSS : string {unique} Age : Integer {130>value ≥0} Conception Autres contraintes * 1..* subordonné chef Société 0..1 <dirige { Personne.employeur = Personne.chef.employeur } 0..1 NSIRET: String {unique} actif : Real {value  0} passif : Real employeur contraintes sur attribut Contrainte de domaine contrainte sur 2 associations

14. Conception Traduction Règles de « passage » d. structurels --> schémas d. dynamiques --> requêtes et traitements applicatifs divers Automatisées dans les AGL (cf TP)

15. Conception

16. Conception

17. Conception Classes ADRESSE N° Rue CP Ville Schéma relationnel Attributs Ajout de l’identifiant Attribut Domaine Non Null Schéma Adresse Clé primaire Id_adresse Id_adresse Identifiant Oui N° Entier Non Rue String(30) Non …..

18. Conception Classe UML { persistence} Instances de la classe Table Departement

19. Conception Spécialisation Plusieurs choix - « aplatir vers le haut » Schéma Personne Clé primaire NSS - « aplatir vers le bas » Schémas Etudiant, Enseignant Clé primaire NSS - conserver les niveaux PERSONNE NSS .... ETUDIANT ENSEIGNANT num .... grade ....

20. Conception Associations COMPTE PERSONNE possède 0..* 1 NSS .... N° Type Schéma Compte Clé primaire Id-compte N° On choisit N° Clé étrangère NSS Attribut Domaine Non Null Id_compte Identifiant Oui N° String(35) Id Oui NSS Identifiant Oui

21. Conception

22. Conception Théorie de la normalisation

23. Conception Relation universelle RR(U, F) U ensemble d'attributs F ens de DF/DMV définies sur U Stratégie : Décomposition { R1(U1, F1), ……..Rn(Un, Fn)}

24. Conception { R1(U1, F1), ……..Rn(Un, Fn)} • Ri 1≤ i≤n • Ri(Ui, Fi) • - Fi définies sur Ui • Ri = Ui (R) conservation des données • conservation des dépendances - Fi = Ui (F) {X  Y tq XY Ui F X  Y }

25. Conception Propriétés { R1(U1, F1), ……..Rn(Un, Fn)} • conservation des données •  instance r de R • r = (ri) • conservation des dépendances F  Fi

26. Décomposition Exemple Décomposition R1 (Titre , MES, Auteur,NomC) Titre  MES BDCINE (Titre , MES, Acteur, NomC, Adresse , Téléphone) Titre  MES NomC  Adresse Téléphone Titre  NomC Titre  NomC Speed 2 JDB S. Bullock UGC Speed 2 JDB J. Patric UGC Marion MP C. Tetard Diago Marion MP MF Pisier Diago R3 (NomC, Adresse, Téléphone) NomC  Adresse Téléphone UGC 9, Rue Boileau 04 67 41 85 67 Diago Bd Gambetta 04 67 54 32 31

27. Décomposition Exemple Décomposition R1 (Titre , MES, Acteur) Titre  MES R2 (Titre , NomC) Titre  NomC BDCINE (Titre , MES, Acteur, NomC, Adresse , Téléphone) Titre  MES NomC  Adresse Téléphone Titre  NomC Speed 2 JDB S. Bullock Speed 2 JDB J. Patric Marion MP C. Tetard Marion MP MF Pisier Speed 2 UGC Marion Diago R3 (NomC, Adresse, Téléphone) NomC  Adresse Téléphone UGC 9, Rue Boileau 04 67 41 85 67 Diago Bd Gambetta 04 67 54 32 31

28. Décomposition NAgence  ChifAff BANQUE NAgence ChifAff NClient PClient Nprêt Montant Nclient PClient Nprêt  Montant Montpellier 1 1000 Montpellier 23 2000 Béziers 13 1500 Nimes 20 5000 Béziers 18 2000 Montpellier 16 1500 ….. R1 (NAgence, Nprêt, Montant) R2 (NClient,Pclient, Montant) R3 (Nagence, ChiAff) Dupont Pierre 1000 Durand Louis 2000 Benoit Nestor 1500 Dupont Pierre 5000 Lebon Zoé 2000 Lesueur Désiré 1500 ….. Montpellier 90 Béziers 75 Nimes 120 ….. Perte de données

29. Les formes normales Trois premières formes normales Objectif : obtenir une décomposition sans perdre d'information à partir de DF Première forme normale Une relation est en 1FN si tout attribut contient une valeur atomique (On a des instances sans attributs multivalués)

30. Les formes normales Deuxième forme normale Une relation est en deuxième forme normale ssi • Elle est en 1FN • tout attribut n'appartenant pas à une clé ne dépend pas d'une partie de clé Fournisseur(Nom, Adresse, Article, Prix) Nom Article  Prix, Nom  Adresse Clé K= Nom Article Adresse  K mais Nom  Adresse

31. Les formes normales Deuxième forme normale (On élimine certaines redondances ..) Fournisseur(Nom, Adresse, Article, Prix) Nom Article  Prix, Nom  Adresse Clé K= Nom Article Adresse  K mais Nom  Adresse R1(Nom, Adresse) R2(Nom, Article, Prix)

32. Les formes normales Deuxième forme normale R K1 K2 X Y

33. Les formes normales Troisième forme normale Une relation est en troisième forme normale ssi • Elle est en 2FN • Tout attribut n'appartenant pas à une clé ne dépend pas d'un autre attribut non clé

34. Les formes normales Troisième forme normale (On élimine certaines redondances dues aux dépendances transitives) Voiture(NV, Marque, Type, Puissance, Couleur) Type  Marque Puissance NV  Marque Type Puissance Couleur NV  Type Marque NV  Type Puissance Clé NV Voiture(NV, Type, Couleur) Modele(Type, Marque, Puissance

35. Les formes normales Troisième forme normale R K Z X Y

36. Les formes normales Décomposition en Troisième forme normale Toute relation a au moins une décomposition en troisième forme normale telle que la décomposition préserve les DF est sans perte (de données)

37. Les formes normales Algorithme de décomposition en Troisième forme normale Données : Un schéma de relation R(U,F), F est un ensemble de dépendances élémentaires Résultat : Un schéma de base de données en forme normale3NF Trouver une couverture minimale irredondante (CIM) de F Conserver les DF élémentaires Partitionner la CIM en groupes Fi tels que les DF dans chaque Fi ait le même ensemble d'attributs en partie gauche Chaque groupe produit un schéma de relation en 3FN Si aucune clé de R(U,F)n'est incluse dans l'un des schémas produits, rajouter un schéma R(K)

38. Les formes normales Calcul d'une couverture minimale irredondante F ensemble de dépendances fonctionnelles Transformer les DF en DF élémentaires (un seul attribut en partie droite) Tester l'irredondance des DF Tester l'irredondance des parties gauches de DF Itérer jusqu'à stabilité

39. Les formes normales DF redondante Soit F un ensemble de DF Une DF f : X  Y de F est redondante si F-f F cad (F-f)+ = F+ Ce qui revient à vérifier que Y  X+ / F-f

40. Les formes normales Attribut redondant en partie gauche de DF Soit X  Y une DF d'un ensemble F de dépendances fonctionnelles Un attribut A  X est redondant si F- (X  Y)  (X-A Y)  F cad (F- (X  Y)  (X-A Y) )+ = F+ Ce qui revient à vérifier que Y  (X-A)+ / F

41. Les formes normales Algorithme de calcul de clé Donnée : R(U, F) et une superclé K de R Résultat : une clé de R X=K; pour chaque A  X faire si A  (K- A)+/ F alors K = K-A fsi fpour Retourner K Rque : il existe un algo retournant l'ensemble des clés

42. Les formes normales Un exemple R (ABCDEG) et F = { AB  C, D  EG C A, BE  G BC  D, CG  BD ACD B , CE AG }

43. Les formes normales Forme normale de BOYCE-CODD Il peut rester des anomalies dues à des redondances entraînées par des dépendances entre parties de clés ou entre attributs non clés et parties de clé … Une relation est en BCNF ssi Les seules dépendances fonctionnelles élémentaires sont celles dans lesquelles une clé entière détermine un attribut Pas de dépendances autres que K A (K clé et A attribut non clé)

44. Les formes normales Forme normale de BOYCE-CODD R K1 K2 X Y R(K1K2XY) est en 3 FN R1(K1K2X) et R2(YK1) en 3 BCNF

45. Les formes normales Du point de vue logique la normalisation est essentielle Du point de vue performance Pas toujours optimal • dénormalisation (implémentation de la jointure) • groupement (clustering)

46. Conception Intégrité des données Définition des contraintes Vérification des contraintes Triggers (cf TP)

47. Conception Définition des contraintes Objectif Détecter et prévenir saisies et mises à jour erronées. Hypothèse - un langage de définition de contraintes d'intégrité - la vérification automatique de ces contraintes Avantages simplification du code des applications sécurité renforcée par l'automatisation mise en commun et cohérence globale des contraintes

48. Conception Définition des contraintes • Contraintes de DOMAINE ex: le nom d'un employé est de type chaîne de caractères le degré d'un vin ne peut être inférieur à 8 • Contraintes d'ENTITE (unicité et non nullité) toute relation doit posséder au moins une clé et cette clé ne peut pas prendre de valeurs nulles • Dépendance fonctionnelle Ex : CRU, ANNEE -------> DEGRE dans la relation VINS

49. Conception • Dépendance d'inclusion Concept de généralisation {valeurs d'un {groupe d'attributs x}} inclus dans {valeurs d'un {groupe d'attributs y}}  EXEMPLE : ENSEIGNANT.NOM inclus dans PERSONNE.NOM ENSEIGNANT ----g----> PERSONNE • Contraintes REFERENTIELLE La dépendance référentielle est un cas particulier de dépendance d'inclusion ex: l'ensemble des valeurs de l'attribut Emp.N_Dept doit être inclus dans l'ensemble des valeurs de l'attribut Dept.N_Dept Définition des contraintes

50. Conception Définition des contraintes • Contraintes multi-attributs (horizontales) Ex: le prix de vente d'un produit doit être supérieur à son coût de production • Contraintes temporelles Ex : le degré d'un vin ne peut pas décroître • Contraintes agrégatives (verticales) Ex : la somme des quantités bues d'un vin doit être inférieure a la quantité produite de ce même vin