Cours 4: Optimisation & Normalisation

1. Cours 4: Optimisation& Normalisation Tuanloc NGUYEN Miage de Paris 12

2. Optimisation de requêtes • Algèbre relationnel • Décomposition de la requête • Optimisation

3. Architecture SGBD Schéma Vue Autorisation Intégrité Ordonnancement Élaboration/ Optimisation Méthode d’accès (hachage,arbre B+,index) ANALYSEUR CONTROLE META-BASE Traitement EXECUTABLE BD

4. Traitement d’une requête SQL Normalisation Analyse Processeur de requêtes Simplification Restructuration Optimisation Exécution des plans

5. Rappel sur l’algèbre relationnel • Normalisation • Forme conjonctive (p11 ν p12 v…v p1n) Λ (pm1 ν pm2 v…v pmn) • Forme disjonctive (p11 Λ p12 Λ … Λ p1n) v (pm1 Λ pm2 Λ…Λ pmn) (souvent disjonctive)

6. Exemple: schéma de la base de données pour les étudiants de la MIAGE Paris 12

7. Conjonctive SELECT ENSEIGNANTS.nom, ENSEIGNANTS.prenom, MATIERES.nommat FROM MATIERES INNER JOIN (ENSEIGNANTS INNER JOIN ENSEIGN_MAT ON ENSEIGNANTS.codens = ENSEIGN_MAT.codens) ON MATIERES.codemat = ENSEIGN_MAT.codemat WHERE (((ENSEIGNANTS.nom)="NGUYEN") AND ((MATIERES.nommat)="ACCESS" Or (MATIERES.nommat)="BASE DE DONNEES"));

8. Disjonctive SELECT ENSEIGNANTS.nom, ENSEIGNANTS.prenom, MATIERES.nommat FROM MATIERES INNER JOIN (ENSEIGNANTS INNER JOIN ENSEIGN_MAT ON ENSEIGNANTS.codens = ENSEIGN_MAT.codens) ON MATIERES.codemat = ENSEIGN_MAT.codemat WHERE ( ( (ENSEIGNANTS.nom)="NGUYEN" AND (MATIERES.nommat)="BASE DE DONNEES") OR ( (MATIERES.nommat)="ACCESS" AND (MATIERES.nommat)="BASE DE DONNEES"));

9. Normalisation de requête • p1 Λ p2 < => p2 Λ p1 • p1 v p2 < => p2 v p1 (commutativité) • p1Λ(p2 Λ p3) < => p1Λp2Λp3 • p1v(p2 v p3) < => p1vp2vp3 (associativité) • p1Λ(p2vp3) < =>(p1Λp2)v(p1Λp3) • p1v(p2Λp3) < =>(p1vp2) Λ(p1vp3) • !(p1 Λ p2) < =>!p1 v !p2 • !!(p) < => p

10. Exercice SELECT Title FROM Emp WHERE (Not (Title=’’linux’’) AND (Title=’’linux’’ OR Title=’’windows’’) AND Not (Title = ’’unix’’)) OR Ename = ’’Toward Linus’’; On suppose: p1 = Title=’’linux’’ p2 = Title=’’windows’’ p3 = Ename = ’’Toward Linus’’

11. Forme normale (!p1 Λ (p1 v p2) Λ !p2) v p3 • Disjonctive: [(!p1 Λ p1) v (!p1 Λp2)] Λ !p2) v p3< => (!p2 Λ [(!p1 Λ p1) v (!p1 Λp2)]) v p3< => (!p2Λ(!p1 Λ p1))v(!p2Λ(!p1Λp2)) v p3 < =>(!p2Λ!p1Λp1)v(!p2Λ!p1Λp2) v p3 < =>(!p2 Λfalse) v (!p1 Λ false) v p3 < => false v false v p3 < => p3

12. Requête finale SELECT Title From Emp WHERE Ename =’’Toward Linus’’;

13. Règle de transformation • Commutativité: R x S Ξ S x R R |x| S Ξ S |x| R R U S Ξ S U R • Associativité ( R x S ) x T = R x ( S x T) ( R |x| S ) |x| T = R |x| ( S |x| T) • Idempotence ΠA’ (ΠA’’(R) ) = ΠA’(R) (avec A’’ dans A’) …

14. Analyse • Mise de la requête en forme normale • Analyse lexical et syntaxique • Type incorrect ou inexistant (schéma de la relation)

15. Simplification • p Λ p < => p • p v p < => p • p Λ true < =>p • p v false < => p • p Λ false < => false • p v true < => true • p Λ !p < => false • p v !p < => true • p1 Λ (p1 v p2) < => p1 • P1 v (p1 Λ p2) < => p1

16. Simplification • Plus une requête est simple, plus son exécution peut être efficace

17. Implémentation • Sélection • Parcours séquentiel • Parcours avec index (hachage,arbre B) • Projection • Jointure: T = R |x| S foreach tuple r Є R do foreach tuple s Є S do if r==s T = T + <r,s>

18. Restructuration • Objectif: choisir l’ordre de l’exécution des opérations algébriques (élaboration du plan logique) • Conversion en arbre algébrique • Transformation de l’arbre (optimisation) • Appliquer les règles de transformation • Estimation du coût des opérations (taille) • Ordre des jointures (coûte le plus cher)

19. SELECT ENSEIGNANTS.nom, ENSEIGNANTS.prenom, MATIERES.nommat, ENSEIGNANTS.note, ENSEIGNANTS.gentil FROM MATIERES INNER JOIN ENSEIGNANTS INNER JOIN ENSEIGN_MAT ON ENSEIGNANTS.codens=ENSEIGN_MAT.codens ON MATIERES.codemat =ENSEIGN_MAT.codemat WHERE ENSEIGNANTS.note=20 AND ENSEIGNANTS.gentil="top" AND (nommat="ADMIN BASE DE DONNEES"OR nommat="ACCESS");

20. Arbre algébrique RESULTAT Nom,Prénom,Note,gentil nommat="ADMIN BASE DE DONNEES"OR nommat="ACCESS" ENSEIGNANTS.gentil="top" ENSEIGNANTS.note=20 ENSEIGN_MAT.Codemat MATIERES.Codemat = MATIERES ENSEIGN_MAT.Codens ENSEIGNANTS.Codens = ENSEIGN_MAT ENSEIGNANTS

21. Optimisation • Elaborer des plans • Arbre algébrique, restructuration, ordre d’évalution • Estimer les coûts • Temps d’exécution • Coût I/O, CPU, poids entre I/O – CPU: • Nombre d’instructions et d’accès au disque • Choisir le meilleur plan • Algorithme de recherche: Heuristique • Coût de chaque plan est différent • Ordre des jointures est très important • Optimisation d’espace de recherche • Stratégie de recherche • Déterministe • Aléatoire : efficace avec beaucoup de relations, améliorer itinéraire

22. Optimisation • Pour optimiser, il y a une technique simple: descendre les opérateurs de sélection et projection le plus près possible des feuilles pour réduire les tables le plus possible

23. Arbre optimisé RESULTAT nommat="ADMIN BASE DE DONNEES"OR nommat="ACCESS" ENSEIGNANTS.gentil="top" ENSEIGNANTS.note=20 ENSEIGN_MAT.Codemat MATIERES.Codemat = MATIERES ENSEIGN_MAT.Codens ENSEIGNANTS.Codens = ENSEIGN_MAT Nom,Prénom,Note,gentil ENSEIGNANTS

24. Conclusion • Très important pour administrateur de base de données: améliorer les performances en réglant des paramètres pour optimiser des requêtes

25. Normalisation • 1NF • 2NF • 3NF • BCNF • 4NF Données non-normalisation 3 NF 2 NF BCNF 1 NF 5 NF 4 NF

26. Pourquoi normalisation ?

27. Première forme normale 1NF • Une relation est dite normalisée ou en première forme normale si : • aucun attribut qui la compose n'est lui-même une relation, c'est-à-dire si tout attribut est atomique (non décomposable). • Cette forme n'utilise que les structures de base d'une relation, elle ne résout pas le problème de la redondance.

28. Exemple: 1NF R(code_etudiant,nom,prénom,…code_mat1,code_mat2,code_mat3,code_mat4,code_mat5) -> ce n’est pas bien S#: code de fournisseur sname: nom de fournisseur fournisseur status: statut de fournisseur p#: produit R(s#,p#,sname,city,status,qty)

29. R(s#,p#,sname,city,status,qty)

30. Problème: • Ajouter S6 pas de produit ? • Changer nom S1 a en x: • Changer tous S1 • Changer 1 enregistrement: conflit ->redondance • Supprimer S3: perde d’info S3

31. Première forme normale (1NF) Table1(Key1, aaa . . .) Table2(Key1, Key2, bbb . .) Table3(Key1, Key2, Key3, ccc. . .) Sections de données répétées imbriquées Table (Key1, . . . (Key2, . . . (Key3, . . .) ) ) Table1(Key1, . . .) TableA (Key1,Key2 . . .(Key3, . . .) ) Table2 (Key1, Key2 . . .) Table3 (Key1, Key2, Key3, . . .)

32. Deuxième forme normale 2NF • Une relation est dite en deuxième forme normale si et seulement si : • Elle est en première forme normale ; • Chaque attribut est totalement dépendant de la clé primaire. • Avec cette forme, les problèmes de redondance ne sont pas entièrement résolus.

33. Exemple: 2NF R(s#,p#,sname,city,status,qty) R1(s#,p#,sname,city,status) R2(s#,p#,qty) Information de la société Activité de la société

34. 2NF PAS DE PROBLEMES

35. Troisième forme normale 3NF • Une relation est en troisième forme normale si et seulement si : • elle est en 2NF; • et chaque attribut non-clé primaire dépend directement de la clé primaire. • La 3NF est adéquate pour la majorité des designs de BD mais elle n'élimine pas toutes les redondances et incohérences. Pour cela, Codd a pensé à BCNF qui est une forme plus stricte de 3NF.

36. Exemple 3NF R11(city, status) 3NF R1 R12(s#, sname,city) 3NF R R2 (s#,p#,qty) 3NF 3NF : T={R11, R12, R2} Solution: sname 1) s# city Enlever s# ->status X status 2) R12 R121 R122

37. Quatrième forme normale 4NF • Permet autant que possible de minimiser l'occurence d'attributs indépendants à valeur mutiple. • Une relation est de la 4NF si : • elle satisfait la 3NF • les données composant chaque attribut ne comportent aucune répétition inutile -> dans une même colonne, il faut minimiser les répétitions. • Une base qui est 4NF est des plus optimales quoiqu'il soit possible de généraliser cette dernière afin d'obtenir la 5NF.

38. Dépendances fonctionnelles cachées Exemple: Employee-Specialty(E#, Specialty, Manager) Est en 3NF. Rêgle d’entreprise – “Business rules”. Un employé peut avoir plusieurs spécialités. Chaque spécialité a plusieurs “managers”. Chaque “manager”a seulement une spécialité. Un employé a seulement 1 “manager” pour chaque spécialité. Le problème est dans la dépendance fonctionnelle caché entre “manager” et spécialité. Besoin d’une table séparée pour “manager”. But then we don’t need to repeat specialty. Dans monde réel, la duplication serait probablement acceptée (spécialité dans les 2 tables. Boyce-Codd Normal Form (BCNF) Employee-Specialty(E#,Specialty, Manager) Employee(E#, Manager) Manager(Manager, Specialty) Employee(E#, Specialty, Manager) Manager(Manager, Specialty) acceptable

39. Annexe: Normalisation

40. Exemple: BD Video Clé possible Section de données répétées

41. Clients Clé: Assignation de CustomerID Propriétés Nom Adresse Téléphone Vidéos Clé: Attribution d’un noVidéo Propriétés Titre PrixLocation Cote Description TransactionLocation Relation/Événement Clé: Attribution d’un noTransaction Propriétés noClient Date VidéosLoués Événement/Liste Clés: noTransaction + noVidéo Propriétés noCopieVidéo Objets de la BD Vidéo

42. Recueillir les formulaires de l’usager Noter les propriétés Trouver les sections de données répétées Noter les clés potentielles Identifier les propriétés calculées Résultat équivalent à un diagramme (modèle logique), mais va pouvoir être contenu sur un page ou deux. Formulaire initial BD vidéo RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) )

43. Problèmes avec les sections de données répétées RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) ) Beaucoup de problèmes seraient provoqués par cette structure. Section de données répétées Provoque des duplications TransID RentDate CustomerID LastName Phone Address VideoID Copy# Title Rent 1 4/18/02 3 Washington 502-777-7575 95 Easy Street 1 2 2001: A Space Odyssey $1.50 1 4/18/02 3 Washington 502-777-7575 95 Easy Street 6 3 Clockwork Orange $1.50 2 4/30/02 7 Lasater 615-888-4474 67 S. Ray Drive 8 1 Hopscotch $1.50 2 4/30/02 7 Lasater 615-888-4474 67 S. Ray Drive 2 1 Apocalypse Now $2.00 2 4/30/02 7 Lasater 615-888-4474 67 S. Ray Drive 6 1 Clockwork Orange $1.50 3 4/18/02 8 Jones 615-452-1162 867 Lakeside Drive 9 1 Luggage Of The Gods $2.50 3 4/18/02 8 Jones 615-452-1162 867 Lakeside Drive 15 1 Fabulous Baker Boys $2.00 3 4/18/02 8 Jones 615-452-1162 867 Lakeside Drive 4 1 Boy And His Dog $2.50 4 4/18/02 3 Washington 502-777-7575 95 Easy Street 3 1 Blues Brothers $2.00 4 4/18/02 3 Washington 502-777-7575 95 Easy Street 8 1 Hopscotch $1.50 4 4/18/02 3 Washington 502-777-7575 95 Easy Street 13 1 Surf Nazis Must Die $2.50 4 4/18/02 3 Washington 502-777-7575 95 Easy Street 17 1 Witches of Eastwick $2.00

44. Autre idée: Mémoriser les données sur la largeur (…) Allocation d’espace Combien? Ne peut être petit Perte d’espace e.g., Combien de vidéo seront loué ? Une meilleure définition élimine ces problèmes. Problèmes avec les sections de données répétées Name Phone Address City State ZipCode Customer Rentals VideoID Copy# Title Rent 1. 6 1 Clockwork Orange 1.50 2. 8 2 Hopscotch 1.50 3. 4. 5. {Unused Space} Pas en première forme normale

45. Enlever les sections de données répétées Divisé en 2 tables Transmettre les clés de la table principale à la nouvelle table RentalLine(TransID, VideoID, Copy#, . . .) Chaque transaction peut avoir plusieurs vidéos (clé VideoID) Chaque vidéo peut être loué dans plusieurs transactions Première forme normale RentalForm(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode, (VideoID, Copy#, Title, Rent ) ) RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode) RentalLine(TransID, VideoID, Copy#, Title, Rent )

46. Première forme normale (1NF) Table1(Key1, aaa . . .) Table2(Key1, Key2, bbb . .) Table3(Key1, Key2, Key3, ccc. . .) Sections de données répétées imbriquées Table (Key1, . . . (Key2, . . . (Key3, . . .) ) ) Table1(Key1, . . .) TableA (Key1,Key2 . . .(Key3, . . .) ) Table2 (Key1, Key2 . . .) Table3 (Key1, Key2, Key3, . . .)

47. 1NF division en groupe Encore des problèmes Redondance Dépendance fct cachée: Si un vidéo n’a pas été loué, quel est son titre? Problèmes de la 1NF TransID RentDate CustID Phone LastName FirstName Address City State ZipCode 1 4/18/02 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171 2 4/30/02 7 615-888-4474 Lasater Les 67 S. Ray Drive Portland TN 37148 3 4/18/02 8 615-452-1162 Jones Charlie 867 Lakeside Drive Castalian Springs TN 37031 4 4/18/02 3 502-777-7575 Washington Elroy 95 Easy Street Smith's Grove KY 42171 TransID VideoID Copy# Title Rent 1 1 2 2001: A Space Odyssey $1.50 1 6 3 Clockwork Orange $1.50 2 8 1 Hopscotch $1.50 2 2 1 Apocalypse Now $2.00 2 6 1 Clockwork Orange $1.50 3 9 1 Luggage Of The Gods $2.50 3 15 1 Fabulous Baker Boys $2.00 3 4 1 Boy And His Dog $2.50 4 3 1 Blues Brothers $2.00 4 8 1 Hopscotch $1.50 4 13 1 Surf Nazis Must Die $2.50 4 17 1 Witches of Eastwick $2.00

48. Chaque champ non clé doit être fonctionnellement dépendant de la clé entière. S’applique que sur les clés à plusieurs champs Diviser et créer une nouvelle table avec ces champs. Dépendance fonctionnelle (définition) Si, pour une certaine valeur de clé X, on peut toujours déterminer la valeur du champ Y alors le champ Y est dit fonctionnellement dépendant de X. Définition de la 2ième forme normale Dépend sur TransID ET VideoID RentalLine(TransID, VideoID, Copy#, Title, Rent) Dépend seulement sur VideoID

49. Tître dépend seulement du VideoID Chaque VideoID ne peut avoir qu’un seul tître Le champ Rent est dépendant de VideoID Rêgle d’entreprise. Pourrait être différent dans un autre club vidéo. Certain club vidéo pourrait charger un loyé différent dépendant de la journée. Chaque champ non clé est fonctionnellement dépendant de la clé et entièrement de la clé. Exemple: 2NF RentalLine(TransID, VideoID, Copy#, Title, Rent) VideosRented(TransID, VideoID, Copy#) Videos(VideoID, Title, Rent)

50. Exemple 2NF (Données) VideosRented(TransID, VideoID, Copy#) TransID VideoID Copy# 1 1 2 1 6 3 2 2 1 2 6 1 2 8 1 3 4 1 3 9 1 3 15 1 4 3 1 4 8 1 4 13 1 4 17 1 Videos(VideoID, Title, Rent) VideoID Title Rent 1 2001: A Space Odyssey $1.50 2 Apocalypse Now $2.00 3 Blues Brothers $2.00 4 Boy And His Dog $2.50 5 Brother From Another Planet $2.00 6 Clockwork Orange $1.50 7 Gods Must Be Crazy $2.00 8 Hopscotch $1.50 (non modifié) RentalForm2(TransID, RentDate, CustomerID, Phone, Name, Address, City, State, ZipCode)