72
Bases de Données Relationnelles DESS CCI Rafik Taouil [email protected]

Bases de Données Relationnelles DESS CCI

  • Upload
    lamar

  • View
    67

  • Download
    1

Embed Size (px)

DESCRIPTION

Bases de Données Relationnelles DESS CCI. Rafik Taouil [email protected]. OBJECTIF DU COURS?. OBJECTI F: 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 - PowerPoint PPT Presentation

Citation preview

Page 1: Bases de Données Relationnelles DESS CCI

Bases de Données RelationnellesDESS CCI

Rafik Taouil

[email protected]

Page 2: Bases de Données Relationnelles DESS CCI

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)

Page 3: Bases de Données Relationnelles DESS CCI

PLAN

1. Introduction

2. Modèle Relationnel

3. Calcul et Algèbre Relationnel

4. SQL

5. Organisation Physique, Index

6. Optimisation des requêtes: l’exemple d’ORACLE.

Page 4: Bases de Données Relationnelles DESS CCI

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 …

Page 5: Bases de Données Relationnelles DESS CCI

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…

Page 6: Bases de Données Relationnelles DESS CCI

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.

Page 7: Bases de Données Relationnelles DESS CCI

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

Page 8: Bases de Données Relationnelles DESS CCI

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

Page 9: Bases de Données Relationnelles DESS CCI

ARCHITECTURE d’un SGBD:

Vue 1

Vue 2

Vue 3

Schémaconceptuel

Monderéel

Processus demodélisation

Schémaphysique

Base de Donnéesphysique

Niveau externe Niveau conceptuel Niveau physique

Page 10: Bases de Données Relationnelles DESS CCI

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é

Page 11: Bases de Données Relationnelles DESS CCI

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)

Page 12: Bases de Données Relationnelles DESS CCI

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) : . . .

Page 13: Bases de Données Relationnelles DESS CCI

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

Page 14: Bases de Données Relationnelles DESS CCI

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

Page 15: Bases de Données Relationnelles DESS CCI

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.

Page 16: Bases de Données Relationnelles DESS CCI

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)

Page 17: Bases de Données Relationnelles DESS CCI

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.

Page 18: Bases de Données Relationnelles DESS CCI

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.

Page 19: Bases de Données Relationnelles DESS CCI

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

Page 20: Bases de Données Relationnelles DESS CCI

Opérations sur les données

Page 21: Bases de Données Relationnelles DESS CCI

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

Page 22: Bases de Données Relationnelles DESS CCI

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.

Page 23: Bases de Données Relationnelles DESS CCI

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 !

Page 24: Bases de Données Relationnelles DESS CCI

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”.

Page 25: Bases de Données Relationnelles DESS CCI

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

Page 26: Bases de Données Relationnelles DESS CCI

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.

Page 27: Bases de Données Relationnelles DESS CCI

LE MODÈLE RELATIONNEL

Présentation Générale

Page 28: Bases de Données Relationnelles DESS CCI

Exemple de Relation

Propriétaire Type Année

Loic Espace 1988

Nadia Espace 1989

Loic R5 1978

Julien R25 1989

Marie ZX 1993

Voiture

Nom de la Relation Nom d’Attribut

n-uplet

Page 29: Bases de Données Relationnelles DESS CCI

FOURNISSEURS FOURNITPRODUITS

CLIENTSCOMMANDE

FNOM

NOM

BALANCE

C_ADRESSE

PNOM

PRIX

QUANTITE

NUM_COMDE

FADRESSE

Un exemple du modèle relationnel

Schéma Entités –Relations (ER)

Page 30: Bases de Données Relationnelles DESS CCI

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

Page 31: Bases de Données Relationnelles DESS CCI

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

Page 32: Bases de Données Relationnelles DESS CCI

Algèbre relationnelle(formalisation)

Page 33: Bases de Données Relationnelles DESS CCI

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

Page 34: Bases de Données Relationnelles DESS CCI

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)

Page 35: Bases de Données Relationnelles DESS CCI

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

Page 36: Bases de Données Relationnelles DESS CCI

ExempleBD = {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]

Page 37: Bases de Données Relationnelles DESS CCI

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

Page 38: Bases de Données Relationnelles DESS CCI

Opérations de baseOpérations ensemblistes :

union, intersection, différence

Opérations relationnelles :

projection

sélection

jointure

renommage

Page 39: Bases de Données Relationnelles DESS CCI

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}

Page 40: Bases de Données Relationnelles DESS CCI

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

Page 41: Bases de Données Relationnelles DESS CCI

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

Page 42: Bases de Données Relationnelles DESS CCI

Sélection - Exempleetud[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

Page 43: Bases de Données Relationnelles DESS CCI

Jointurer[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

Page 44: Bases de Données Relationnelles DESS CCI

Jointure - Exempleetud[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

Page 45: Bases de Données Relationnelles DESS CCI

Jointure - Exemple (suite)etud prof

définie? Sur quel schéma?

etud etud

définie? Sur quel schéma?

Page 46: Bases de Données Relationnelles DESS CCI

Jointure - PropriétésR(r s) = r ?

R(r s) S(r s) = r s ?

r[RS], R(r) S(r) = r ?

C(r s) = C(r) s ?

Page 47: Bases de Données Relationnelles DESS CCI

Renommager[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 : A1A(r)

A1A(r) contient les mêmes n-uplets que r

Utilisation : avoir deux versions distinctes d ’une même relation

Page 48: Bases de Données Relationnelles DESS CCI

Renommage - ExempleNuméros des étudiants inscrits à plus d ’un cours ?

inscrit[num_et, num_c] (1ère version)

nnum_c(inscrit) (2ème version)

nnum_c(inscrit) inscrit (jointure)

num_cn(nnum_c(inscrit) inscrit)(sélection)

num_et(num_cn(nnum_c(inscrit) inscrit))

Page 49: Bases de Données Relationnelles DESS CCI

Opérations ensemblistesr[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

Page 50: Bases de Données Relationnelles DESS CCI

Opérations ensemblistes - Exemples

Page 51: Bases de Données Relationnelles DESS CCI
Page 52: Bases de Données Relationnelles DESS CCI

* une Projection consiste à enlever des colonnes à une relation 

 Res_Proj = nom (Etudiant)

    * une Sélection caractérise un sous-ensemble de tuples d'une relation au moyen d'un prédicat 

 

Res_Sel = age>21 (Etudiant)

Page 53: Bases de Données Relationnelles DESS CCI

* l'opérateur d'Union réalise la fusion des n-uplets de 2 relations compatibles 

 Res_Uni = Etudiant Stagiaire

    •la Différence de 2 relations (compatibles) contient les tuples de la 1ère ne figurant pas dans la 2nde

Res_Dif = Etudiant - Stagiaire

 

Page 54: Bases de Données Relationnelles DESS CCI

* le résultat d’un Produit cartésien se compose d'un n-uplet de chacune des 2 relations arguments 

 Res_PrC = Stagiaire x Société

Page 55: Bases de Données Relationnelles DESS CCI

* l'Intersection de 2 relations compatibles retourne les tuples communs aux 2 relations 

Page 56: Bases de Données Relationnelles DESS CCI

* Jointure  Soient R et S 2 relations construites sur des schémas ayant A1 ... An en commun  La (-)jointure de R et S est construite sur l'union des attributs de R et de S, et contient tous les tuples composés d'un n-uplet de R et d'un n-uplet de S qui coïncident au niveau du comparateur sur A1 ... An    

Res_Join = Stagiaire Convention (Stagiaire.nom = Convention.nom_s)

Page 57: Bases de Données Relationnelles DESS CCI

* Division  Soient R (A1 ... An B1 ... Bm), etS (B1 ... Bm)Le quotient R / S construit sur A1 ... An se compose des n-uplets

a1 ... an tels que pour tout n-uplet b1 ... bm de S, le n-uplet a1 ... an b1 ... bm appartient à R

  

 

Res_Div = R_Etud / S_Pren (prenom)

Page 58: Bases de Données Relationnelles DESS CCI

L'extraction des données recherchées peut se faire en plusieurs étapes Ex

T1 = age=21 (Etudiant)T2 = nom (T1)

C'est pour permettre l'écriture de requêtes de manière déclarative que le calcul relationnel a été introduit

Page 59: Bases de Données Relationnelles DESS CCI

Calcul relationnel

On suppose donné un ensemble infini de variables liées aux valeurs des domaines; les formules du calcul sont construites ainsi :  

   les valeurs des domaines et les variables sont des termes

si x1 ... xn sont des termes, alors les expressions R (x1 ... xn) et xi xj sont des formules ( étant un comparateur usuel) 

  si F1 et F2 sont des formules, alors F1 F2 , F1  F2 , F1 F2 , et F2 sont des formulessi F est une formule dans laquelle la variable x est libre, x F et x F sont des formules

Page 60: Bases de Données Relationnelles DESS CCI

Interprétation des formules  Une formule R (x1 ... xn) est vraie pour une valuation donnée des variables ssi le n-uplet correspondant appartient à la relation R   Une requête dans le calcul relationnel est une expression de la forme 

{ (xi ... xj) / F (x1 ... xn) }, où les xi ... xj sont des variables libres

   et la réponse l'ensemble des valeurs de (xi ... xj) pour lesquelles F est vraie

Page 61: Bases de Données Relationnelles DESS CCI

Ex : { x / y Etudiant (x, 21, y) }   Correspondance calcul / algèbre * Projection de R (X, Y) sur l’ensemble X :  

X (R) = { x / y R (x, y) }   

* Sélection sur R à partir de F, formule constituée à partir de termes utilisés dans la construction de R 

F (R) = { x / R (x) F (x) }

Page 62: Bases de Données Relationnelles DESS CCI

* Union R S = { x / R (x) S (x) }

* Différence R - S = { x / R (x) S (x) }  * Produit cartésienR x S = { x = (r, s) / R (r) S (s) }

Page 63: Bases de Données Relationnelles DESS CCI

* IntersectionR S = { x / R (x) S (x) }

* (-) Jointure de R (X, Y) et S (X, Z) sur X : R S = { t = (x, y, z) /

w R (x, y) S (w, z) (x w) } * Division de R (X, Y) par S (Y) : 

R / S = { x / y S (y) R (x, y) }

Page 64: Bases de Données Relationnelles DESS CCI

l'algèbre et le calcul relationnels sont des langages équivalents

Page 65: Bases de Données Relationnelles DESS CCI

Contraintes d'intégrité

La déclaration d'un schéma relationnel peut comporter la définition de contraintes d'intégrité Ces règles précisent les instances des relations cohérentes par rapport au monde modélisé 

Page 66: Bases de Données Relationnelles DESS CCI

Les dépendances expriment des liens entre groupes d'attributs des schémas

Dépendance fonctionnelle (df) Il existe une df d'un groupe d'attributs X de R vers un groupe

d'attributs Y de R si à une valeur de X correspond une et une seule valeur de Y

La théorie des df est à la base du processus de "normalisation" d'un schéma relationnel. Avec

 Schéma1

Etudiant (nom, age, ville)Ville (nom, pays, nbh)

 Schéma2

Etudiant (nom, age, ville, pays, nbh) la théorie indique que c'est Schéma1 qu'il faut utiliser pour éviter de

la redondance « logique »

Page 67: Bases de Données Relationnelles DESS CCI

Clés d'une relation X est une clé pour R s’il n’existe pas 2 tuples distincts de R ayant même valeur sur X, et si aucun sous-ensemble de X ne vérifie cette propriété La clé d'une relation R est en dépendance fonctionnelle avec tous les attributs de R  Si R possède plusieurs clés potentielles, on appelle clé primaire celle choisie pour clé

Page 68: Bases de Données Relationnelles DESS CCI

Un groupe d'attributs Y de R est une clé étrangère de R si Y est la clé primaire d'au moins une autre relation distincte de R

ExEtudiant (nome, age, nomville)Ville (nomville, pays, population)

 

Page 69: Bases de Données Relationnelles DESS CCI

Valeurs nulles De l'information inconnue ou incomplète peut être introduite dans une base via des valeurs nulles : 

Page 70: Bases de Données Relationnelles DESS CCI

Intégrité d'entité (ou de relation) L'intégrité d'entité correspond à la propriété d'un ensemble de ne pas contenir de doublons  Ceci passe par l'attribution à chaque relation du schéma d'une clé primaire. Chaque valeur de clé primaire doit être unique et non nulle

Page 71: Bases de Données Relationnelles DESS CCI

Intégrité de référence Un schéma relationnel contient  --> des relations statiques (entités autonomes) --> des relations dépendantes (leur existence est liée à celle de relations statiques) Ex

Etudiant (Ide, nome, prenom, age)Société (Ids, noms, activite, adresses)Convention (Ide, Ids, date, duree)

 Les dépendances se caractérisent par des liaisons de type N : M entre les entités, transformées lors du passage au schéma relationnel en clés étrangères L'intégrité de référence est la règle qui régit les valeurs des clés étrangères (valeurs nulles ou existantes dans la table liée)

Page 72: Bases de Données Relationnelles DESS CCI

Intégrité liée à l'application  Chaque application modélisée peut faire apparaître d'autres contraintes spécifiques  Ex : un étudiant ne réalise qu’un stage à la fois   Ces contraintes, dites d'intégrité sémantique, ne sont pas (toutes) directement modélisables au niveau du schéma relationnel