Cours …

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

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

3. Plan • Généralités • Rappels modèle relationnel • Algèbre relationnelle, SQL • Les contraintes • Conception • Organisation physique • Transactions

4. Généralités Une BD c'est quoi ? • Ensemble de données apparentées (même thématique) • Facilement interrogeable et modifiable par un langage de haut niveau (proche langue naturelle) • Stocké sur mémoire secondaire (disques)

5. Généralités Une BD cela sert à quoi ? • Mémoriser des données • Structurées • Documents (texte, images, films …) • Volume de données de plus en plus grands • Giga, Terra, Péta bases (10**15 octets) • Numériques, Textuelles, Multimédia (images, films,...) • De plus en plus de données archivées • Retrouver en ligne la bonne donnée au bon moment • Mettre à jour les données variant dans le temps

6. Généralités Un peu d'histoire • Années 60 • Récipients logique de données  fichiers sur disque • Accès séquentiel puis sur clé • Lire (Nomf, Article), Ecrire (Nomf, Article) • Lire (Nomf, Article, Clé), Ecrire (Nomf, article, Clé) • Années 70 • Avènement des Bases de Données Réseaux (issues d’Apollo) • Ensemble de fichiers reliés par des pointeurs • Langage d'interrogation par navigation • Années 80 • Avènement des Bases de Données Relationnelles (BDR) • Relations entre ensemble de données • Langage d'interrogation par assertion logique Modèle Objet , Web

7. Généralités Un peu d'histoire • Années 90 … Influence de l'approche Objet Web Modèle semi-structuré

8. Qu'étudie t'on ? Généralités Les modèles de données Les méthodes de stockage Les langages de requêtes Les algorithmes d’optimisation de requêtes Les algorithmes de contrôles Les méthodes de publication de données Les architectures de systèmes La répartition des données La prise en compte du web

9. Réel Modèle conceptuel Indépendant du modèle de données Indépendant du SGBD effectue Visite Médecin Modèle logique Dépendant du modèle de données Indépendant du SGBD Codasyl Relationnel Objet XML Modèle Physique Dépendant du modèle de données Dépendant du SGBD Organisation physique des données Structures de stockage des données Structures accélératrices (index) Généralités Qu'étudie t'on ?

10. Plan • Généralités • Rappels modèle relationnel • Algèbre relationnelle, SQL • Les contraintes • Conception • Organisation physique • Transactions

11. Modèle relationnel Hypothèse de base une relation est souvent une meilleure modélisation qu’une fonction

12. Modèle relationnel Mais en Informatique on sait représenter un ensemble par une application répétée (liste): suivant(e), suivant(suivant(e)),… donc attacher à x, R{x} par une liste

13. Modèle relationnel => les relations ne sont pas indispensables En fait si ! sinon les opérations sont trop complexes On part donc des ensembles et relations

14. Modèle relationnel En BD modèle relationnel Le modèle de données • Simple description d'une "table" • Formel théorie des ensembles logique

15. Modèle relationnel En BD modèle relationnel Un petit exemple VINS(CRU,MILL,REGION,COULEUR) VINS CRU MILL REGION COULEUR CHENAS 1983 BEAUJOLAIS ROUGE TOKAY 1980 ALSACE BLANC -------- TAVEL 1986 RHONE ROSE CHABLIS 1986 BOURGOGNE BLANC ST-EMILION 1987 BORDELAIS ROUGE

16. Terminologie Nom de relation R VINS Att (ensemble d'attributs) Att ( R ) ensemble fini d'attributs de R Att(VINS) ={CRU,MILL,REGION,COULEUR} Pour chaque attribut un domaine, ensemble de valeurs Dom (REGION) = chaînes de caractères longueur 30 R(A,B,C) Arité ( R ) = Attr ( R )  Arite(VINS) = 4 Schéma de base de donnée ensemble fini de schémas de relation

17. Terminologie Schéma de relation (INTENTION) nom de relation R défini sur U={A1,A2, …, Ak) Contenu du schéma de relation (EXTENSION) Instance du schéma de relation Une ligne de la table = un tuple ou n-uplet sur (A1,A2, …, Ak) (a1,a2,…,ak) telles que pour tout i=1..k, ai  Dom(Ai) N-uplet = fonction, élt du produit cartésien Notation u[Ai] = ai = composante de u sur Ai

18. Terminologie Une instance r de R = • ensemble fini de tuples ou n-uplets sur (A1,A2, …, Ak) • sous-ensemble fini du produit cartésien DOM (A1) x DOM (A2) x ….. DOM (Ak) Instance = un des contenus possibles de la table !!

19. Terminologie Instance d'une base de données ensemble d'instances de schéma de relation r1,r2, …rn (une pour chaque Ri) Telles que ri soit une instance de Ri

20. Opérateurs Nécessité de langages pour extraire les données • Formels Langage algébrique Théorie des ensembles Calculs Logique

21. Algèbre relationnelle • Opérations ensemblistes pour des relations de même schéma UNION notée  INTERSECTION notée  DIFFERENCE notée — A  B = A - (A - B) ou B - (B-A) (A  B)  B = B

22. Algèbre relationnelle • Renommage - peut servir à donner le même nom à des attributs distincts - donner des noms distincts Utiliser pour réaliser opérations ensemblistes sur des relations d'attributs différents R(A1, A2)  ren B1->A1 S(B1,A2)

23. Projection opération unaire opération verticale (supprimer des colonnes) Elimination des attributs non désirés et suppression des tuples en double Relation -> Relation notée :  A1,A2,...Ak (R) Sémantique R(U) soit W  U w [R] (r ) = {u[W]  u  r} Algèbre relationnelle

24. Algèbre relationnelle VINS Cru Mill Région Qualité VOLNAY 1983 BOURGOGNE A VOLNAY 1979 BOURGOGNE B CHENAS 1983 BEAUJOLAIS A JULIENAS 1986 BEAUJOLAIS C Cru,Région (VINS) Cru Région VOLNAY BOURGOGNE CHENAS BEAUJOLAIS JULIENAS BEAUJOLAIS

25. Sélection opération unaire opération horizontale (supprimer des lignes en utilisant certains critères) Soit V un ensemble d'attributs Une condition élémentaire sur V est A comp a ou A comp B Avec A, B  V, a  Dom(A), Dom(A) = Dom(B) et Comp est un comparateur (=,<, >, …) Une condition sur V peut aussi être C1  C2, C1  C2, C1 Sélection (Restriction)

26. Relation -> Relation notée : C (R) Sémantique R(U) C [R] (r ) = {u u  r et u satisfait C} Sélection (Restriction)

27. Sélection (Restriction) VINS Cru Mill Région Qualité VOLNAY 1983 BOURGOGNE A VOLNAY 1979 BOURGOGNE B CHENAS 1983 BEAUJOLAIS A JULIENAS 1986 BEAUJOLAIS C  (MILL>1983) VINS VINS Cru Mill Région Qualité JULIENAS 1986 BEAUJOLAIS C

28. Produit de relations (RxS) R S R x S E <-> F F <-> G E <->G e1 f1 f2 g1 e1 g1 e1 f2 f2 g2 e1 g2 e1 f3 f3 g2 e1 g3 e2 f4 f3 g3 e2 g3 e3 f5 f4 g3 f6 g4 Opération binaire

29. Produit de relations (RxS) R x S = {(e,g) | f (e,f) R et (f,g) S}

30. Jointures 3 types de jointure • Théta-jointure R1 (U1), R2 (U2)  est une formule booléenne entre attributs de U1 U U2 exemple  : A1 =(B2+C2) R1 R2  = {t: U1 U U2 | t[U1]  r1  t[U2]  r2  (t)}

31. Jointures • Equijointure R1 (U1), R2 (U2) C’est un théta-jointure de la forme  : A1 = A2 avec A1 U1 et A2 U2 R1 R2 (A1 = A2) = {t: U1 U U2 | t[U1]  r1  t[U2]  r2 t[A1]=t[A2]}

32. Jointures R S R S E <-> F F <-> G E F G e1 f1 f2 g1 e1 f2 g1 e1 f2 f2 g2 e1 f2 g2 e1 f3 f3 g2 e1 f3 g2 e2 f4 f3 g3 e1 f3 g3 e3 f5 f4 g3 e2 f4 g3 f6 g4 Jointure naturelle

33. Jointures R S = {(e,f,g) | f (e,f) R et (f,g) S} Combiner le contenu de deux instances en se servant des valeurs dans les colonnes communes

34. Jointures Jointure naturelle c’est une opération “intelligente” (égalité des noms) chaque attribut commun n’apparaît qu’une fois

35. Jointures Exemple VINS Cru Mill Qualité VOLNAY 1983 A VOLNAY 1979 B CHABLIS 1983 A JULIENAS 1986 C LOCALISATION Cru Région QualMoy VOLNAY Bourgogne A CHABLIS Bourgogne A CHABLIS Californie B VINSREG Cru Mill Qualité Région QualMoy VOLNAY 1983 A Bourgogne A VOLNAY 1979 B Bourgogne A CHABLIS 1983 A Bourgogne A CHABLIS 1983 A Californie B

36. Complétude les 5 opérations Union, Différence Jointure (naturelle) Sélection Projection Langage complet = capable d’exprimer ces 5 opérations

37. Complétude • Remarque Quotient R(X,Y)  S(Y) = Q(X) Q(X) est la division de R (X, Y) par S (Y) ssi Q contient tous les tuples ( x ) tels que (y)  S, ( x, y )  R

38. Complétude • Remarque Quotient R(X,Y)  S(Y) = Q(X) F S1 F P S1 P1 S1 P2 S2 P1 S2 P3 P P1 P2 Quels sont les fournisseurs de toutes les pièces Q S R

39. Complétude • Remarque Quotient R(X,Y)  S(Y) =  x ( R) -  x (( x ( R) x S) - R)

40. Complétude • L'algèbre relationnelle est complète Les cinq opérations de base permettent de formaliser sous forme d'expressions toutes les questions que l'on peut poser avec la logique du premier ordre (sans fonction) • Exemple BUVEURS (NB, NOM, PRENOM, TYPE) VINS (NV, CRU, MILL, DEGRE) ABUS (NB, NV, DATE, QUANTITE) Nom et prénom des buveurs de volnay 1988 ?

41. Conclusion Langage relationnel complet (avec modifications) comparable à la machine de Türing ? non, il manque l’itération ( ou la récursion) problème type : fermeture transitive d’une relation Il faut calculer Rn, avec n variable

42. Vision logique Une relation R d'arité n a pour instance un ensemble fini de faits ou atome de base R(a1, …,ak) Une requête Q peut être exprimée sous forme de règle conjonctive Q : Rep(u) <- R1(u1), R2(u2), … Rp(up)

43. Vision logique Rep(u) est la tête de Q R1(u1), R2(u2), … Rp(up) le corps de Q La régle est un outil de déduction Relation intentionnelle Rep(u) , Relations extensionnelles R1, R2 .. Rp

44. Plan • Généralités • Rappels modèle relationnel • Algèbre relationnelle, SQL • Les contraintes • Conception • Organisation physique • Transactions

45. SQL • Une requête SQL est un paraphrasage d'une expression de l'algèbre relationnelle en anglais • Requête élémentaire : SELECT A1, A2, …Ap FROM R1, R2, …Rk WHERE Q [{UNION |INTERSECT | EXCEPT } … ] • Sémantique du bloc select : PROJECT A1,A2,…Ap ( RESTRICT Q ( PRODUIT ( R1, R2, …, Rk) ) )

46. SQL • Ensemble de concepts pour : • Etendre les fonctionnalités de manipulation • Décrire les règles d'évolution des données • Supporter des objets complexes (SQL3) • Introduits progressivement dans le modèle : • Complique parfois le modèle • Standardisés au niveau de SQL3 (1999) • Des extensions multiples …

47. SQL • FONCTION • Fonction de calcul en ligne appliquée sur un ou plusieurs attributs • Exemple : DEGRE * QUANTITE / 100 • AGREGAT • Partitionnement horizontal d'une relation selon les valeurs d'un groupe d'attributs, suivi d'un regroupement par une fonction de calcul en colonne (SUM, MIN, MAX, AVG, COUNT, …)

48. VINS CRU DEGRE QUANTITE MILL 1977 10.9 CHABLIS 100 1987 11.9 CHABLIS 250 1977 10.8 VOLNAY 400 1986 11.2 VOLNAY 300 1985 11.2 MEDOC 200 AVG DEGRE 11.2 SQL • SELECT AVG(DEGRE) FROM VINS; • SELECT CRU, SUM(QUANTITE) • FROM VINS • GROUP BY CRU; SUM CRU QUANTITE CHABLIS 350 VOLNAY 700 MEDOC 200

49. SQL SELECT <liste de projection> FROM <liste de tables> [WHERE <critère de jointure> AND <critère de restriction>] [GROUP BY <attributs de partitionnement>] [HAVING <citère de restriction>] • Restriction : • arithmétique (=, <, >, ) • textuelle (LIKE) • sur intervalle (BETWEEN) • sur liste (IN) • Possibilité de blocs imbriqués par : • IN, EXISTS, NOT EXISTS, ALL, SOME, ANY

50. SQL <search condition> ::= [NOT] <nom_colonne> qconstante½ <nom_colonne> <nom_colonne> LIKE <modèle_de_chaîne> <nom_colonne> IN <liste_de_valeurs> <nom_colonne> q(ALL ½ ANY½ SOME) <liste_de_valeurs> EXISTS <liste_de_valeurs> UNIQUE <liste_de_valeurs> <tuple> MATCH [UNIQUE] <liste_de_tuples> <nom_colonne> BETWEEN constante AND constante <search condition> AND ½ OR <search condition> avec q ::= < ½ = ½ > ½ ³½£½ <> Remarque: <liste_de_valeurs> peut être dynamiquement déterminée par une requête