Bases de Données Relationnelles DESS CCI

Bases de Données RelationnellesDESS CCI Rafik Taouil taouil@univ-tours.fr

OBJECTIF DU COURS? OBJECTIF: Comprendre et maitriser la technologie des systèmes de bases de données relationnelle: • Modélisation des données • Interrogation des données: évaluation des requêtes • Mises-à-jour des données: concurrence et transactions • Intégration des données: entrepôts de données, …? (pas sûre)

PLAN • Introduction • Modèle Relationnel • Calcul et Algèbre Relationnel • SQL • Organisation Physique, Index • Optimisation des requêtes: l’exemple d’ORACLE.

BIBLIOGRAPHIE Ouvrages en français • Date C.J, Introduction aux Bases de Données, Vuibert, 970 Pages, Janvier 2001 Ouvrages en anglais • R. Elmasri, S.B. Navathe, Fundamentals of database systems, 3e édition, 1007 pages, 2000, Addison Wesley • Ullman J.D. and Widom J. A First Course in Database Systems, Prentice Hall, 1997 • Garcia-Molina H., Ullman J. and Widom J., Implementation of Database Systems, Prentice Hall, 1999 • Web …

Applications “SGBD” CLASSIQUES : • Données de Gestion (salaires, stocks, réservations d’avions) • Applications Transactionnelles (gestion de comptes bancaires, centrales d’achat) MOINS CLASSIQUES : • Documents électroniques, Données Multimédia, Spatiales A LA MODE : • Données du Web: HTML, XML • Entrepôts de données Logiciels (Standard SQL) : Postgres SQL, SQL Server, DB2, Oracle…

Comment Stocker et Manipuler les Données? Données vers Bases de Données (BD) • Une B.D. est un GROS ENSEMBLE d’informations STRUCTURÉES mémorisées sur un support PERMANENT. LOGICIEL : SYSTÈME de GESTION de B.D. (S.G.B.D) • Un Système de Gestion de Bases de Données (SGBD) est un logiciel de HAUT NIVEAU qui permet de manipuler ces informations.

Complexités Diversité des utilisateurs, des interfaces et des architectures: • utilisateurs : administrateurs, programmeurs, non informaticiens, . . . • interfaces : langages de programmation, saisie de données, génération de rapports,. . . • architectures : données centralisées, distribuées, hétérogènes

En Résumé, un SGBD ... ... est un OUTIL GÉNÉRIQUE qui doit répondre à des besoins très divers de gestion de un GROS VOLUME D’INFORMATIONS – persistantes (années) et fiables (protection sur pannes) – partageables (utilisateurs, programmes) – manipulées indépendamment de leur organisation physique

ARCHITECTURE d’un SGBD: Monde réel Processus de modélisation Vue 1 Schéma conceptuel Schéma physique Vue 2 Base de Données physique Vue 3 Niveau physique Niveau externe Niveau conceptuel

FONCTIONNALITÉS d’un SGBD Chaque niveau du SGBD réalise un certain nombre de fonctions : NIVEAU PHYSIQUE • Accès aux données sur mémoire secondaire (disques), index, ... • partage de données et gestion de la concurrence d’accès • reprise sur pannes (fiabilité) • distribution et interopérabilité

NIVEAU LOGIQUE – Définition de la structure de données : Langage de Description de Données (LDD) – Consultation et Mise à Jour des données : Langages de Requêtes (LR) et Langage de Manipulation de Données (LMD)

NIVEAU EXTERNE: Vues Utilisateurs 1. Vue de la planification des salles : pour chaque cours – Nom de Prof – Horaires et salles 2. Vue de la paye : un ensemble de Prof (nom, prénom, adresse, indice, nombre d’heures. . . ) 3. Vue du service de scolarité (suivi des élèves) : . . .

Intégration de ces Vues • On laisse chaque usager avec sa vision du monde • PASSAGE DU NIVEAU EXTERNE AU NIVEAU LOGIQUE: On “intègre” l’ensemble de ces vues en une description unique: SCHÉMA LOGIQUE

Modèles de données Un modèle de données est caractérisé par : – une structuration des informations et – des opérations sur ces structures

Modèles (suite) Dans un SGBD, il existe plusieurs modèles plus ou moins abstraits des mêmes objets, e.g. : – le modèle conceptuel : la description du système d’information – le modèle logique : interface avec le SGBD – le modèle physique : fichiers ces différents modèles correspondent aux niveaux dans l’architecture d’un SGBD.

Modèle Conceptuel: Exemple Entité-Relation – Modèle très abstrait, pratique pour : – l’analyse du monde réel – la conception du système d’information – la communication entre différents acteurs de l’entreprise – Mais n’est pas associé à un langage. (Une structure mais pas d’opérations)

Modèle logique 1. Langage de définition de données (LDD) pour décrire la structure. 2. Langage de manipulation de données (LMD) pour appliquer des opérations aux données. Ces langages sont abstraits : 1. Le LDD est indépendant de la représentation physique des données. 2. Le LMD est indépendant de l’implantation des opérations.

Les avantages de l’abstraction 1. Simplicité d’accès: les structures et les langages sont plus simples, donc plus faciles pour l’usager non expert. 2. INDÉPENDANCE PHYSIQUE: on peut modifier l’implantation physique (index, stockage, ...) sans modifier les programmes d’application 3. INDÉPENDANCE LOGIQUE: on peut modifier les programmes d’application sans toucher à l’implantation.

HISTORIQUE DES SGBD À chaque génération correspond un modèle logique Les premiers étaient peu abstraits (navigationnels) 60 S.G.F. (e.g. COBOL) mi-60 HIÉRARCHIQUE IMS (IBM) navigationnel RÉSEAU (CODASYL) navigationnel 73-80 RELATIONNEL déclaratif mi-80 RELATIONNEL explosion sur micro Fin 80 RELATIONNEL ETENDU nouvelles applications DATALOG (SGBD déductifs) pas encore de marché ORIENTÉ-OBJET navig. + déclaratif Fin 90 HIÉRARCHIQUE (XML) nouvelles applications Web

Opérations sur les données

Exemples d’opérations Insérer les informations pour l’employé Jean Augmenter le salaire de Jean de 10% Supprimer les informations de Jean Chercher les employés cadres Chercher les employés du département comptabilité Chercher le salaire moyen des employés comptables, avec deux enfants, nés avant 1960 et travaillant à Paris

Quels types d’opérations ? 4 types d’opérations classiques dans un SGBD: 1. La création (ou insertion) de données. 2. La modification de données. 3. La destruction de données. 4. La recherche de données. Ces opérations correspondent à des manipulations de données (LMD) et sont appelés des requêtes. La plus complexe est la recherche en raison de la variété des critères.

Le Traitement d’une Requête – ANALYSE SYNTAXIQUE – OPTIMISATION Génération (par le SGBD) d’un programme optimisé à partir de la connaissance de la structure des données, de l’existence d’index, de statistiques sur les données. – EXÉCUTION POUR OBTENIR LE RÉSULTAT. NB: on doit tenir compte du fait que d’autres utilisateurs sont peut-être en train de modifier les données qu’on interroge !

Concurrence d’accès Architecture Client-Serveur: plusieurs clients (utilisateurs, applications) doivent pouvoir accéder en même temps aux mêmes données. Le SGBD doit savoir : • Gérer les conflits si les deux font des mises-à-jour sur les mêmes données. • Offrir un mécanisme de retour en arrière si on décide d’annuler des modifications en cours. • Donner une image cohérente des données si l’un fait des requêtes et l’autre des mises-à-jour. • Le but : éviter les blocages, tout en empêchant des modifications “anarchiques”.

Revenons aux Utilisateurs d’un SGBD L’administrateur de la base Rôle de l’administrateur/concepteur – discute avec les différents utilisateurs – conception d’un schéma logique (et des différentes vues) – conception du schéma physique – installation de la base et réglages fins (tuning) – gère l’évolution de la base (nouveaux besoins, utilisateurs) Outils à sa disposition fournis par l’éditeur du SGBD

Utilisateur expert: informaticien connaissant langages programmation et langages BD – Concepteur et programmeur d’application écrit les applications pour des utilisateurs “naïfs” – Utilisateur naïf: du non-spécialiste des SGBD au non-informaticien.

LE MODÈLE RELATIONNEL Présentation Générale

Exemple de Relation Nom de la Relation Nom d’Attribut Voiture n-uplet

Un exemple du modèle relationnel PRIX FNOM PNOM FOURNISSEURS FOURNIT PRODUITS C_ADRESSE FADRESSE COMMANDE NUM_COMDE CLIENTS NOM QUANTITE BALANCE Schéma Entités –Relations (ER)

FOURNISSEUR FNOM FADRESSE Abounayan 92190 Meudon Cima 75010 Paris Preblocs 92230 Gennevilliers Sarnaco 75116 Paris FOURNITURE FNOM PNOM PRIX Abounayan sable 300 Abounayan briques 1500 Preblocs parpaing 1200 Sarnaco parpaing 1150 Sarnaco ciment 125

COMMANDES NUM_COMDE NOM PNOM QUANTITE 1 Jean briques 5 1 Jean ciment 10 3 Paul briques 3 4 Paul parpaing 9 5 Vincent parpaing 7 CLIENTS NOM CADRESSE BALANCE Jean 75006 Paris -12000 Paul 75003 Paris 0 Vincent 94200 Ivry 3000 Pierre 92400 Courbe 7000

Algèbre relationnelle (formalisation)

Eléments de base • Univers : ensemble fini d ’attributs, U • Attribut : associé à un ensemble de valeurs appelé domaine, dom(A) • Schéma relationnel : sous-ensemble non vide de l ’univers • n-uplet sur le schéma R : application de R dans l ’union des domaines des attributs de R

Eléments de base (exemple) • U = {num_et, nom_et, adr_et, num_p, nom_p, adr_p, num_c, nom_c} • R= {num_et, num_c} • t : R  dom(num_et)  dom(num_c) num_et  n1 num_c  n2 • Notation : t = (n1, n2)

Eléments de base (suite) • Relation sur R : ensemble fini de n-uplets définis sur R • Base de données sur U : ensemble de relations définies sur des schémas de U • Remarque : plusieurs relations d ’une même base peuvent avoir le même schéma

Exemple • BD = {etud, prof, cours, inscrit} • Avec : • etud[num_et, nom_et, adr_et] • prof[num_p, nom_p, adr_p] • cours[num_c, nom_c, num_p] • inscrit[num_et, num_c] • Remarque : on peut ajouter • anc_etud[num_et, nom_et, adr_et]

Algèbre relationnelle • Combine les relations pour exprimer les requêtes • Formalisme rigoureux dont le pouvoir d ’expression est « fort » : ~ logique du 1er ordre • Propriétés formelles pour optimiser le calcul des réponses

Opérations de base • Opérations ensemblistes : • union, intersection, différence • Opérations relationnelles : • projection • sélection • jointure • renommage

Projection • r[R] et X sous-ensemble de R • Projection de r sur X : • relation définie sur X contenant les restrictions sur X des n-uplets de r • notation : x(r) • x(r) = {u |  t dans r tel que u=t.X} • Remarque : attention à l ’écriture • x(r) = {t.X | t dans r}

Projection - Exemple • etud[num_et, nom_et, adr_et] • X = nom_et, adr_et • x(etud) • liste des noms et adresses des étudiants • Attention : si Y = nom_et, adr_p • y(etud) n ’est pas défini

Sélection • r[R] relation définie sur R • C condition de sélection de la forme • C = (A comp a), ou C = (A comp B) • C combinaison par les connecteurs logiques • Sélection de r selon C • relation définie sur R • notation : C(r) • C(r) est l ’ensemble des n-uplets de r qui satisfont C

Sélection - Exemple • etud[num_et, nom_et, adr_et] • C = (adr_et = Casa ou num_et > 100) • C(etud) • liste des étudiants dont l ’adresse est Casa ou dont le numéro est supérieur à 100 • nom_et(C(etud)) • liste des noms de ces étudiants • Attention : si C = (nom_p = nom_et) • C(etud)n ’est pas défini

Jointure • r[R] et s[S] deux relations • Jointure de r par s • relation définie sur R  S • notation : r s • r s = {t | t.R  r et t.S  s} • Remarques • une jointure est toujours définie • opération coûteuse à calculer

Jointure - Exemple • etud[num_et, nom_et, adr_et] inscrit[num_et, num_c] • etud inscrit • définie sur num_et, nom_et, adr_et, num_c • liste des inscriptions des étudiants • nom_et,num_c(etud inscrit) • liste des noms des étudiants et des numéros des cours auxquels ils sont inscrits

Jointure - Exemple (suite) • etud prof • définie? Sur quel schéma? • etud etud • définie? Sur quel schéma?

Jointure - Propriétés • R(r s) = r ? • R(r s) S(r s) = r s ? • r[RS], R(r) S(r) = r ? • C(r s) = C(r) s ?

Renommage • r[R] , A attribut de R , A1 nouveau nom pour A • Renommage de A en A1 dans r • relation définie sur (R - {A})  {A1} • notation : A1A(r) • A1A(r) contient les mêmes n-uplets que r • Utilisation : avoir deux versions distinctes d ’une même relation

Renommage - Exemple • Numéros des étudiants inscrits à plus d ’un cours ? • inscrit[num_et, num_c] (1ère version) • nnum_c(inscrit) (2ème version) • nnum_c(inscrit) inscrit (jointure) • num_cn(nnum_c(inscrit) inscrit) (sélection) • num_et(num_cn(nnum_c(inscrit) inscrit))

Opérations ensemblistes • r[R] et s[R] définies sur le même schéma R • r  s , r  s , r - s • définies sur R • sémantique habituelle • Remarque : r  s = r s

Opérations ensemblistes - Exemples

Bases de Données Relationnelles DESS CCI