Module Bases de donnes Avances

Prsents la bibliothque UVSQ

Elments de bibliographie (1re partie)

Rappels des concepts de base (et perspectives)

Gardarin G., "Bases de donnes", Ed. Eyrolles, 5me dition, 2003.

Modles de stockage et dindexation

Evaluation et optimisation de requtes

Modles et protocoles transactionnels

Support dobjets complexes

Ozsu, T., Valduriez P., Principles of Distributed Database Systems, Prentice-Hall, 2nd edition, 1999. Nouvelle dition 2011 (Springer) J. Besancenot, M. Cart, J. Ferri, R. Guerraoui, P. Pucheral, B. Traverson, "Les systmes transactionnels : concepts, normes et produits", Ed. Herms, 1997.

XML Schema

XQuery et optimisation

Disponible sur le Web

Fundamentals of Database Systems, 6th Ed, Elmasri and Navathe, Addison Wesley, 2011

Indexation XML

Mdiation XML

Systmes Pair--Pair
2 3

Rappels des concepts fondamentaux

Un systme dinformation sans SGBD (1)

Dupont Dupond
Turlututusqjsk Symptom: yyyy Analyses xxxx Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx Turlututudhjsd Analyses :xx

Plusieurs applications
ComptaSoft ChiruSoft

plusieurs formats et langages difficult de maintenance manque dinteroprabilit

Objectifs des SGBD

Principales fonctions dun SGBD

Comptabilit

Chirurgie

Architecture fonctionnelle de rfrence

Redondance des donnes

incohrence

Le modle relationnel
Duhpon
Symptomes : yy Analyses : xxxx Symptomes : yy

Le langage SQL

Duipont
Turlututu : sq Symptomyyyy Analysesxxxx Turlututudhjsd

Pas de facilit dinterrogation

PsychiaSoft ConsultSoft

Architectures physiques de SGBD

1. 2. 3. 4. 5. 6. 7.

Orientations de recherche

toute question doit tre prvue et programme

Consultations

Psychiatrie

cot de dveloppement lev redondance de code difficult dvolution

5

Un systme dinformation sans SGBD (2) Lapproche Bases de donnes

Dupont

Dupond

Turlututusqjsk Symptom: yyyy Analyses xxxx

Comptabilit
9 - Tolrance aux pannes 8 - Concurrence daccs

PsychiaSoft

Consultations
6

O1: Description canonique des donnes

Rel

Description cohrente, unique et centralise des donnes manipules par lensemble des applications constituant le systme dinformation.

Perception globale du systme d'information

=> augmentation du niveau dinformatisation

=> nouveaux traitements (aide la dcision, analyse de donnes, )

Factorisation de la description des donnes et de leur comportement (contraintes dintgrit )

Elimination de la redondance

=> redondance coteuse en place et source dincohrence

=> redondance systme reste ncessaire pour : fiabilit, performance de consultation, disponibilit en environnement rparti ou mobile
8

ComptaSoft

ChiruSoft

Symptomes : y Turlututu : sqj Symptomes : y Turlututu : sdd Analyses : xxx

Turlututudhjsd Analyses :xx

Gestion simpliste des droits

violation de la confidentialit
10 - Standards 2- Indpendance Physique 3- Indpendance Logique 4 Langage de manipulation 1- Description canonique

Chirurgie

Gestion simpliste du paralllisme

blocage du systme ou bien incohrence en cas d accs simultans

BD

Duhpon

Duipont

Turlututu : sq

Symptomes : yy Analyses : xxxx

Symptomyyyy Analysesxxxx

Pas de tolrance aux fautes

7 - Confidentialit des donnes

5 - Optimisation de requtes 6 - Intgrit des donnes

ConsultSoft

Symptomes : yy

Turlututudhjsd

incohrence en cas de panne

Psychiatrie
7

Modle conceptuel

Indpendant du
modle de donnes

Indpendant du
SGBD

Modle conceptuel Modle logique

Mdecin

effectue

Visite

Dpendant du
modle de donnes

Indpendant du
SGBD

Codasyl

Relationnel

Objet

XML

Dpendant du
modle de donnes

Modle Physique

Dpendant du SGBD

Organisation physique des donnes Structures de stockage des donnes Structures acclratrices (index)
9

O2: indpendance physique Modle physique

Rel
Indpendant du
modle de donnes

Indpendance des programmes d'applications vis vis des structures de stockage des fichiers.

Indpendant du
SGBD
Mdecin

Description logique des donnes, en termes d'entits et dassociation, donnant une vision conceptuelle des donnes (sparation claire du monde rel et du monde informatique)

Modle conceptuel
Dpendant du
modle de donnes

effectue

Visite

Possibilit de modifier les structures de stockage (fichiers, index, chemins d'accs, ) sans modifier les programmes;
Indpendant du
SGBD

Ecriture des applications par des non-spcialistes des fichiers et des structures de stockage;
Dpendant du
modle de donnes

Modle logique
Codasyl

Relationnel

Objet

XML

Meilleure portabilit des applications et indpendance vis vis du matriel.

10

Modle Physique
Dpendant du SGBD

Organisation physique des donnes Structures de stockage des donnes Structures acclratrices (index)
11

O3: indpendance logique

O3 Exemples dindpendance Logique

Les applications peuvent dfinir des vues logiques de la BD
Gestion des mdicaments
Nombre_Mdicaments
Id-M 1 2 3 . Nom Aspegic 1000 Fluisdal Mucomyst .. Description .. .. .. .. Nombre 30 20 230 ..
1 2

Possibilit de modifier la structure conceptuelle globale de la base sans modifier les vues particulires de chaque groupe d'utilisateurs.

Cabinet du Dr. Masse

Prescription Visites
Id - D Id -P 2 3 Id - V 1 4 Date 15 juin 1 mars Prix 250 250 Id -V 1 1 . Ligne 1 2 . Id -M 12 5 . Posologie 1 par jour 10 gouttes

Patients
Id - P 1 2 . Nom Lebeau Troger . Prnom Jacques Zoe . Id -M 1 2 3 . Nom

Mdicament
Description Aspegic 1000 Fluisdal Mucomyst .. .. .. .. ..

Chaque application ou groupe d'applications dsire utiliser ses propres structures logiques de donnes en fonction de ses propres besoins;

Chaque application tablit une description des donnes qu'elle utilise:

Les donnes dcrites restent virtuelles (vues);

Le SGBD se charge de leur faire correspondre des donnes relles.

D o cteur
Id-D 1 2 3 . N om D upont D urand M asse .. P rn om P ierre P aul Jean Id-V

P rescrip tion
L ign e Id -M P osologie

V isites
I d-D 1 1 2 2 Id -P 2 1 2 3 Id -V 1 2 3 4 D ate 15 juin 12 aot 13 juillet 1 m ars P rix 250 180 350 250

1 1 2 2 2 .

1 2 1 2 3 .

12 5 8 12 3 .

1 par jour 10 gouttes 2 par jour 1 par jour 2 gouttes

P a tien ts
I d-P 1 2 3 4 . N om L ebeau T roger D oe P erry . Prn om Jacques Z oe John P aule . Id -M 1 2 3 . N om

M dica m ent
D escrip tion A s pegic 1000 F luisdal M ucom yst .. .. .. .. ..

12

13

O4: Langage de manipulation

Traduction automatique des requtes dclaratives en
programmes procduraux (composition doprateurs lmentaires)

O5: Optimisation de requtes

Les non-informaticiens doivent pouvoir manipuler les donnes partir de la seule connaissance du monde rel et de la modlisation qui en est faite

Mais les non-informaticiens font-ils des requtes SQL ?

La manipulation se fait via un langage dclaratif

Optimisation automatique de ces programmes

Exploitation des proprits des oprateurs lmentaires Gestion centralise des chemins d'accs (index, hachages, )

La question dclare lobjectif sans dcrire la mthode Le langage suit une norme commune tous les SGBD SQL : Structured Query Langage

Economie de l'astuce des programmeurs

Exemple de requte SQL Retrouver le nom et le n de tlphone de tous les pdiatres

milliers d'heures d'criture et de maintenance de logiciels.

Select Nom, Tel From Docteur Where Specialite = Pdiatre

14

Course aux performances mesures en transactions par

seconde (TPS) sur des "benchmark" standardiss (TPC).

15

O6: Intgrit smantique des donnes

O7: Confidentialit des donnes

Objectif : garantir la confidentialit de certaines informations et les protger contre la dgradation

Objectif : Dtection automatique des mises jour

errones

Contrle sur les donnes lmentaires

Dossier mdical, procd de fabrication, salaire des employs ...

Contrle de types: Nom alphabtique

Contrle de valeurs: Salaire mensuel entre 1 et 10k

Plusieurs niveaux :

Contrle sur les relations entre les donnes

Authentification des usagers Privilges d'accs aux objets de la base Chiffrement et hachage crytographique des donnes

Relations entre donnes lmentaires : Prix de vente > Prix d'achat

Relations entre objets : Un lecteur est inscrit sur une seule liste lectorale

Avantages

Usagers : utilisateurs, rles Objets : objet rel ou virtuel, procdure ...

16 17

simplification du code des applications scurit renforce par l'automatisation mise en commun des contraintes

Confidentialit des donnes O8: Accs concurrents aux donnes

Objectif : assurer lIsolation des transaction, c..d que diffrentes applications partageant les mmes donnes doivent pouvoir s'ignorer et travailler de manire asynchrone.

Service des ressources humaines

Employs (intranet)

Public (internet)

Id-E

Nom

Prnom

Poste

Ricks

Jim

5485

Trock

Jack

1254

Le SGBD garantit la srialisabilit des accs: l'effet d'une excution simultane de transactions doit tre le mme que celui d'une excution squentielle. < T1 || T2 || Tn > < T1; T2; Tn > Les transactions excutes en parallle ne doivent pas entrer en conflit lecture-criture ou criture-criture, afin dviter : des pertes de mises jour des introductions dincohrence
18

Lerich

Zoe

5489

Doe

Joe

4049

Nombre demploys 4

Masse Salariale 890

Id-E 230 120 380 160

Nom

Prnom

Poste

Adresse

Ville

Salaire

Ricks

Jim

5485

Paris

Trock

Jack

1254

Versailles

Lerich

Zoe

5489

Chartres

Doe

Joe

4049

Paris

des lectures non reproductibles

19

O9: Tolrance aux pannes

10 Respect des standards

Lapproche bases de donnes est base sur plusieurs
standards

Le SGBD doit assurer la prennit et la cohrence des donnes en prsence de pannes multiples:

Transaction Failure : Contraintes d'intgrit, Annulation

Langage de manipulation (SQL1, SQL2, SQL3) Communication SQL CLI (ODBC / JDBC) Transactions (X/Open DTP, OSI-TP)

System Failure : Panne de courant, Crash serveur

Media Failure : Perte du disque

Communication Failure : Dfaillance du rseau

Force des standards

Portabilit des applications Interoprabilit des systmes

Ceci implique lAtomicit des transactions de mises jour qui doivent tre totalement excutes ou pas du tout.

Ceci implique galement des mcanismes de reprise assurant la Durabilit des effets des transactions valides.
20 21

3. Architecture de rfrence
Schma Externe 1 Schma Externe i Schma Externe n

Niveaux de schma
vision spcifique une application

De nombreuses architectures fonctionnelles ont t proposes

Ces architectures dpendent souvent du modle de donnes

SCHEMA CONCEPTUEL

utilis

ANSI/X3/SPARC est une architecture de rfrence mais sa

vision canonique globale exprime en terme d'entits et d'associations

normalisation a choue.
SCHEMA INTERNE

L'architecture ANSI/X3/SPARC repose sur un concept

fondamental: la distinction de 3 niveaux de schmas

description physique des fichiers, des modes de stockage (squentiel, tri, hach) et des index

22

23

ANSI/X3/SPARC : principes

Architecture fonctionnelle d'un SGBD

ANALYSEUR Analyse syntaxique Analyse smantique Gestion des schmas

Admin. Entreprise Admin. Application

Admin. BD

Processeur de schma Conceptuel

Construction de la BD
META-BASE

TRADUCTEUR

Modification de requtes Contrle d'intgrit Contrle d'autorisation

Processeur de schma Interne

DICTIONNAIRE

Processeur de schma Externe OPTIMISEUR Transformateur Externe Conceptuel Programme dapplication

Ordonnancement Optimisation Elaboration d'un plan

Transformateur Conceptuel Interne

Systme

Exploitation de la BD

EXECUTEUR

Excution du plan Mthodes d'accs Gestion de transactions

dE/S Programmeur. dapplication

BD

Dictionnaire et Mta-base sont synonymes

24

25

4. Le modle relationnel
Docteurs
Id-D Id-V Ligne 1 2 1 2 2 . 3 . 1 Dupont Pierre Nom Prnom Id-M 12 5 8 12 3 .

Base de donnes relationnelle

Prescriptions
Posologie 1 par jour 10 gouttes 2 par jour 1 par jour 2 gouttes

Visites
1 Id-V 1 2 3 4 1 mars 250 13 juillet 350 12 aot 180 2 15 juin 250 2 Date Prix 1 Id-D 1 1 2 2 3 2 1 2 Id-P

2 3 . .. Masse Jean

Concepts descriptifs Domaine : caractrise un ensemble de valeurs Relation : sous-ensemble du produit cartsien d'une liste de domaines Tuple : ligne d'une relation Attribut : colonne d'une relation
Durand Paul

Exemple de relation (ou Table)

Attribut variant sur le domaine Ville

Patients
Id-P Nom Lebeau Jacques Zoe John Paule . . Valenton Paris Evry Troger Doe Perry . Paris Prnom Ville Id-M 1 2 3 . Nom 1 2 3 4

Patients
Ville Paris Evry Paris Valenton .

Mdicaments
Description Aspegic 1000 Fluisdal Mucomyst .. .. .. .. ..

Id-P

Nom

Prnom

Lebeau

Jacques

Tuple
26
.

Troger

Zoe

Doe

John

Perry

Paule

27

Cl, cl trangre et schma

Concepts manipulatoires
UN ENSEMBLE D'OPERATIONS DE BASE EST
FORMALISE : L'ALGEBRE RELATIONNELLE

Cl : groupe d'attributs minimum qui dtermine un tuple unique dans

une relation

PATIENTS (IdP, NOM, PRENOM, VILLE)

Cl trangre : groupe d'attributs apparaissant comme cl dans un autre

CES OPERATIONS PERMETTENT D'EXPRIMER

TOUTES LES REQUETES SOUS FORME D'EXPRESSIONS ALGEBRIQUES

relation

VISITES (IdV, IdD, IdP, DATE, PRIX)

VISITES.IdD rfrence DOCTEURS.IdD VISITES.IdP rfrence PATIENTS.IdP

ELLES SONT LA BASE DU LANGAGE SQL

28

29

Restriction
Patients
Id-P 1 2 3 John Paule Valenton 4 Paule Perry Valenton 4 4 Perry Doe Paris 3 John 3 Doe Paris Troger Zoe Evry 2 Zoe 2 Troger Evry Lebeau Jacques Paris 1 Jacques Lebeau Paris 1 Lebeau Troger Doe Perry Nom Prnom Ville Id-P Nom Prnom Ville Id-P Nom

Projection
Patients Patients
Prnom Jacques Zoe John Paule Ville Paris Evry Paris Valenton

Patients

Id-P

Nom

Prnom

Ville

Lebeau

Jacques

Paris

Troger

Zoe

Evry

Doe

John

Paris

Nom, Prnom (Patients)

30

Perry

Paule

Valenton

Patients de la ville de Paris, not en algbre:

Nom et prnom des patients, not en algbre:

Ville=Paris (Patients)

31

Jointure
Visites
Id-D 1 1 2 2 3 4 1 mars 250 2 3 13 juillet 350 1 2 12 aot 180 2 1 15 juin 250 Id-P Id-V Date Prix

Union, Intersection, Diffrence

Patients

Id-P

Nom

Prnom

Ville

Lebeau

Jacques

Paris

Troger

Zoe

Evry

Doe

John

Paris

Relation X Relation --> Relation (notes: , , -) OPERATIONS ENSEMBLISTES S'APPLIQUANT A DES

RELATIONS DE MEME SCHEMA

Perry

Paule

Valenton

Id-P 2 1 3 4 1 mars 250 13 juillet 350 15 juin 250 12 aot 180

Nom

Prnom

Ville

Id-D

Id-P

Id-V

Date

Prix

Lebeau

Jacques

Paris

Troger

Zoe

Evry

Troger

Zoe

Evry

EXTENSION: Union externe (OUTER UNION)

Doe

John

Paris

s'applique des relations de schmas diffrents on ramne les deux relations au mme schma en ajoutant des valeurs nulles
32 33

Notation algbrique:

Patients

Id-P=Id-P

Visites

Au fait, quappelle-t-on Jointure externe gauche (Left-Outer-Join) ?

Exemple de plan dexcution 5. Le langage SQL

Patients Visites

Select

Patients.Nom, Patients.Prnom

Le langage SQL (Structured Query Language) comprend trois parties :

From

Patients, Visites

Where

Patients.Id-P = Visites.Id-P

and

Patients.Ville = Paris

and

Visites.Date = 15 juin

1. Le langage de dfinition de donnes (Tables, Vues, Droits, Intgrit) 2. Le langage de manipulation de donnes (Slections, Modifications, Insertions, Suppressions) 3. L'intgration aux langages de programmation

Optimisation

Patients Visites
34

35

Le standard SQL

EXEMPLE DE BASE DE DONNEES

SQL est driv de l'algbre relationnelle et de SEQUEL (SystemR 74).

Le standard SQL volue en permanence : - SQL1 86 version minimale - SQL1 89 addendum (intgrit) - SQL2 92 version tendue trois niveaux de conformit (entry, intermediate, full) - SQL2 95 addendum (CLI : Call Level Interface) - SQL2 96 addendum (PSM : Persistent Stored Modules) - SQL3 extension aux objets complexes - SQL 99 contient les bases de SQL3 - SQL 2006 intgration de XML
36 37

Cration de table : syntaxe

CREATE TABLE RDV( NumRdv Integer PRIMARY KEY, DateRDV Date, NumDoc Integer, NumPat Integer, Motif Varchar(200), CONSTRAINT Rfrence_DOC FOREIGN KEY (NumDoc) REFERENCES DOC (NumDoc), CONSTRAINT Rfrence_PAT FOREIGN KEY (NumPat) REFERENCES PAT (NumPat); L'association d'un nom une contrainte est optionnelle. Ce nom peut tre utilis pour rfrencer la contrainte (ex: messages d'erreurs).

Cration de table : exemple

CREATE TABLE <nom_table> (<def_colonne> * [<def_contrainte_table>*]) ;

< def_colonne > ::= <nom_colonne> < type > [CONSTRAINT nom_contrainte < NOT NULL | UNIQUE | PRIMARY KEY | CHECK (condition) | REFERENCES nom_table (colonne) > ]

< def_contrainte_table > ::= CONSTRAINT nom_contrainte < UNIQUE (liste_colonnes) | PRIMARY KEY (liste_colonnes) | CHECK (condition) | FOREIGN KEY (liste_colonnes) REFERENCES nom_table (liste_colonnes)>

38

39

Insertion de donnes

Suppression de donnes
SYNTAXE : DELETE FROM <relation_name> [WHERE <search_condition>] EXEMPLES : Supprimer tous les docteurs DELETE FROM DOC Supprimer le docteur numro 20 DELETE FROM DOC WHERE NumDoc = 20 Et celle-ci ? DELETE FROM DOC WHERE NumDoc NOT IN (SELECT NumDoc FROM ORD)

Syntaxe :

INSERT INTO < nom_table >

[( attribute [,attribute] )]

{VALUES (<value spec.> [, <value spec.>] ) |

<query specification>} ;

Exemples :

INSERT INTO DOC VALUES (1, Dupont, Paris);

INSERT INTO DOC (NumDoc, NomDoc) VALUES (2, Toto);

INSERT INTO PAT (NumPat, NomPat, VillePat) SELECT NumDoc, NomDoc, VilleDoc FROM DOC;

40

41

Modification de donnes
SYNTAXE ELEMENTAIRE SELECT <liste des attributs projets Ai> FROM <liste des relations impliques Rj> WHERE <critres de recherche Ci> SEMANTIQUE PROJECTION Ai ( RESTRICTION Ci ( PRODUIT ( Rj ) )
42 43

Manipulation de donnes

SYNTAXE :

UPDATE SET

[WHERE

<relation_name> <attribute> = value_expression [, <attribute> = value_expression ] <search condition> ];

EXEMPLES :

Modifier ladresse du docteur nro 20

UPDATE DOC SET VilleDoc = Valenton WHERE NumDoc = 20

Augmenter de 10% le prix de tous les mdicaments dont le nom contient Antibio

UPDATE MED SET PRIX = PRIX*1.1 WHERE NomMed = %Antibio%

SELECT: forme gnrale

Forme de la condition de recherche

SELECT [DISTINCT| ALL] { * | <value exp.> [, <value exp.>]...}

FROM relation [variable], relation [ variable]

[WHERE <search condition>]

[GROUP BY <attribute> [,<attribute>]...]

[HAVING <search condition>]

[ORDER BY <attribute.>[.{ASC | DESC}] [,<attribute.>[.{ASC | DESC}] ]...]

<search condition> ::= [NOT] <nom_colonne> constante <nom_colonne> <nom_colonne> LIKE <modle_de_chane> <nom_colonne> IN <liste_de_valeurs> <nom_colonne> (ALL ANY SOME) <liste_de_valeurs> EXISTS <liste_de_valeurs> UNIQUE <liste_de_valeurs> <nom_colonne> MATCH [UNIQUE] <liste_de_valeurs> <nom_colonne> BETWEEN constante AND constante <search condition> AND OR <search condition> avec ::= < = > <>
44

Remarque: <liste_de_valeurs> peut tre dynamiquement dtermine par une requte

45

Projections et restrictions simples Restrictions complexes et jointures

Liste des patients ayant un RDV avec le docteur Dupont SELECT FROM WHERE PAT.NomPat PAT, RDV, DOC PAT.NumPat = RDV.NumPat and RDV.NumDoc = DOC.NumDoc and DOC.NomDoc like Dupont; NomMed NomPat

Liste des mdicaments de plus de 50 NomMed SELECT NomMed FROM MED WHERE Prix> 50 ;

Liste des mdicaments de plus de 50 NomMed (prix stock en FF) SELECT NomMed FROM MED WHERE Prix/6,55957 > 50 ;
Mdicaments commenant par ASPI et prescrits en aot 2008 SELECT FROM WHERE DISTINCT M.NomMed MED M, DET D, ORD O M.NumMed = D.NumMed and D.NumOrd = O.NumOrd and NomMed like ASPI% and O.Date BETWEEN 01/08/2008 and 31/08/2008; NomDoc

Nom des docteurs habitant Florac NomDoc SELECT NomDoc FROM DOC WHERE VilleDoc = Florac

Liste des motifs des consultations du 25/12/2006 Motif sans doublons SELECT DISTINCT Motif FROM RDV WHERE DateRDV = 25/12/2006
Docteurs ayant le mme nom quun patient SELECT

D.NomDoc FROM PAT P, DOC D WHERE P.NomPat = D.NomDoc;

46

47

Requtes imbriques : IN et EXIST

NomPat

Calculs d'agrgats
Les fonctions dagrgation (Count, Sum, Avg, Min, Max) permettent de raliser des calculs sur des ensembles de donnes

Liste des patients ayant un RDV avec le docteur Dupont

SELECT DISTINCT P.NomPat FROM PAT P, RDV R, DOC D WHERE P.NumPat = R.NumPat and R.NumDoc = D.NumDoc and D.NomDoc = Dupont;

Calcul de statistiques globaux

Et celle-ci ?

Nombre de patients : SELECT count(*) FROM PAT Prix moyen des mdicaments : SELECT avg(Prix) FROM MED

SELECT P.NomPat FROM PAT P WHERE P.NumPat in (SELECT R.NumPat FROM RDV R WHERE R.NumDoc in (SELECT D.NumDoc FROM DOC WHERE D.NomDoc = Dupont));

Calcul de statistiques par groupe

Nombre de patients par ville : SELECT VillePat, count(*) NbPatient FROM PAT GROUP BY VillePat

Et celle-l ?

SELECT P.NomPat FROM PAT P WHERE EXISTS (SELECT * FROM RDV R WHERE P.NumPat = R.NumPat and EXISTS (SELECT * FROM DOC D WHERE R.NumDoc=D.NumDoc and D.NomDoc = Dupont));

Et celle-ci ? SELECT VillePat FROM PAT, RDV WHERE PAT.NumPat = RDV.NumPat and Motif = mal de tte GROUP BY VillePat HAVING count(DISTINCT(NumPat)) > 10

Limbrication est un moyen de dcomposer une requte complexe en sous-requtes plus simples
48 49

Union/Inter/Diff. : exemples
valuation smantique dune requte SQL
1. FROM
Ralise le produit cartsien des relations

Ensemble des personnes de la base mdicale

SELECT NomMed NomPers FROM MED UNION SELECT NomPat NomPers FROM PAT

2. WHERE
Ralise restriction et jointures

Patients qui sont aussi mdecins

3. GROUP BY
XXX XXX YYY ZZZ

SELECT NomPat PatMed FROM PAT INTERSECT SELECT NomMed PatMed FROM MED

Constitue les partitions (e.g., tri sur lintitul du groupe)

Patients qui ne sont pas mdecins

Restreint aux partitions dsires

4. HAVING 5. SELECT
Raliser les projections/calculs finaux
AGG1

XXX XXX YYY ZZZ

AGG1

AGG2

SELECT NomPat Patient FROM PAT EXCEPT SELECT NomMed Patient FROM MED

AGG3

XXX
AGG3

6. ORDER BY
Trier les tuples rsultat
50

ZZZ

AGG1 AGG3

ZZZ XXX

51

Vues relationnelles

Les vues : des relations virtuelles !

Le SGBD transforme la question sur les vues en question sur les relations de base
Question Q sur des vues

Les vues permettent dimplmenter lindpendance logique

en crant des objets virtuels

Vue = Question SQL stocke Le SGBD stocke la dfinition et non le rsultat Exemple : la vue des patients parisiens

Create View Patients_Parisiens as (

Select

Nom, Prnom
Dfinition des vues

Gestionnaire de Vues
Question Q sur les relations de base

From

Patients

Where

Patients.Ville = Paris )
52

53

Dclencheurs : Dfinition
Dclencheurs : Syntaxe (dans Oracle)

Dfinition : Dclencheurs ou Triggers

Rgle E C A : vnement Condition Action

Lorsque lvnement se produit

Insert / Update / Delete pour une relation donne

si la condition est remplie

Prdicat SQL optionnel

alors excuter laction

Code excuter (ex. PL/SQL sous Oracle)

Create trigger <nom de trigger> before | after permet dindiquer quand le trigger va tre excut insert | delete | update [of <attributs>] Quel est lvnement dclencheur on <relation> indique le nom de la table qui doit tre surveille [referencing old as <var>, new as <var>] en SQL3, Oracle utilise :new et :old for each row Prcise si laction est excute 1 fois par tuple concern ou pour toute la table [when <condition>] permet dindiquer une condition pour lexcution du trigger DECLARE Dclaration de variables pour le bloc PL/SQL BEGIN Bloc PL/SQL contenant le code de laction excuter <PL/SQL bloc> Dans SLQ3, on peut indiquer une suite de commande SQL END

Pour chaque tuple concern ou une fois par vnement

La syntaxe diffre lgrement suivant le SGBD

54 55

PROGRAMMATION SQL

Exemple Embedded SQL

EXEC SQL DECLARE C1 CURSOR FOR SELECT NumDoc, NomDoc FROM DOC WHERE VilleDoc = Versailles; main() { EXEC SQL BEGIN DECLARE SECTION

SQL nest pas un langage complet

Problmatique de la connexion langage hte/SQL

dclaratif/ensembliste vs. procdural

conversion de type

char sqlstate[6], NomDoc[21], VilleDoc[31]; int NumDoc;

EXEC SQL END DECLARE SECTION EXEC SQL OPEN C1 ; while (notEndofResult(sqlstate) { EXEC SQL FETCH C1 INTO :NumDoc, :NomDoc; ... } EXEC SQL CLOSE C1 ;

3 approches

Embedded SQL (PRO*C, SQLJ )

API de bas niveau (SQL-CLI, ODBC, JDBC )

Langage ddi (PL/SQL, SQL/PSM )

56

57

Exemple JDBC
Procdure Anonyme
Client BD PL/SQL Moteur Serveur PL/SQL SQL

Exemple : Oracle PL/SQL

import java.sql.*; ... class JdbcTest { static String BD_URL = "jdbc:oracle:@nomBDRoute";

Procdure stocke (invocation directe)

Client
RPC

Moteur Serveur PL/SQL SQL

BD
PL/SQL

Procdure stocke (invocation indirecte)

Mise jour SQL
trigger

Client

Moteur Serveur PL/SQL SQL

BD
PL/SQL

public static void main (String args []) throws SQLException, ClassNotFoundException, IOException { Class.forName ("oracle.jdbc.OracleDriver"); Connection BDRoute; BDRoute =DriverManager.getConnection(BD_URL, "MonNom", "MonPass"); Statement Trajet = BDRoute.createStatement (); ResultSet ResTrajet = Trajet.executeQuery ("SELECT * FROM Route WHERE Depart = Paris"); while ( ResTrajet.next() ) { System.out.println (ResTrajet.getString ("Arrivee")); } ResTrajet.close(); Trajet.close(); BDRoute.close(); } }
58

59

Curseurs PL/SQL : exemple

Architecture physique des SGBD

Une floraison de vocabulaire et darchitectures

DECLARE

CURSOR Compta IS

SELECT nom, salaire FROM Employe WHERE service = comptabilit;

Emp Compta%ROWTYPE;

BD centralise BD client/serveur BD 3-tiers BD rpartie BD htrogne BD sur le Cloud

BEGIN

OPEN Compta;

FETCH Compta INTO Emp;

WHILE Compta%FOUND LOOP

IF Emp.salaire IS NOT NULL AND Emp.Salaire < 30.000 THEN

UPDATE Employe SET salaire = salaire * 1,05 WHERE nom = Emp.nom

END IF;

FETCH Compta INTO Emp;

END LOOP;
60 61

CLOSE C;

END;

Architecture centralise
Des clients intelligents

Architecture client-serveur
Font tourner les applications

Terminaux passifs Appli 2

Des terminaux clients passifs Un rseau Un serveur central

Appli 1 Maintient la base Clients intelligents rseau serveur Appli n

Un rseau Un serveur de donnes

grande puissance (mainframe)

rseau

Maintient la base et les applications

Appli 1

Appli 2

Appli n

SGBD Mainframe code donnes

SGBD

donnes

Exemple dinstance de cette architecture?

le minitel, mais aussi Google 62

Exemple dinstance de cette architecture?

Client messagerie

63

Architecture 3-tiers

Architecture rpartie
Des clients intelligents

Des clients

Concentrs sur la prsentation

Font tourner lapplication Interagissent avec 1 SGBD

(lapplication ne voit pas que sa requte est rachemine)

Un rseau Un serveur dapplication

Appli 1 Appli 2 Appli n

Excute le code applicatif

rseau

Un serveur de donnes

Maintient la base

Un rseau Des serveurs

Sur la mme machine ou des machines diffrentes

Une mme base Grent chacun une partition

Serveur dapplication Appli 1

Appli 2

Appli n

Serveur de donnes

SGBD SGBD 1.1 code donnes

SGBD 1.2 code donnes

code

donnes

Exemple dinstance de cette architecture?

Appli Web

64

Exemple dinstance de cette architecture?

Agences dune socit

65

Architecture htrogne
Virtualisation de lapproche
Terminaux rseau

Architecture en Cloud

Des clients intelligents

Interagissent avec 1 mdiateur

Un mdiateur

Interroge les sources

Appli 1

Appli 2

Appli n

Nettoyage, intgration, etc.

centralise avec mutualisation des ressources matrielles et logicielles

Des sources de donnes

Type, schma, etc.

Donnes htrognes

Objectif = lasticit Pay as you go DaaS: Database as a Service

Appli n

Mdiateur Appli 1 Appli 2

Gestion des donnes diffrente

Source 1 : SGBD donnes

Source 2 : serveur Web SGBD

Mainframe

code

donnes

code

donnes

Exemple dinstance de cette architecture?

66

Kelkoo

Exemple dinstance de cette architecture?

Amazon EC2

67

Applications traditionnelles des SGBD

Applications nouvelles
DB in the (very) large: slections/mj simples sur tables gantes
ex: Google (BigTable), Facebook (Cassandra), Amazon (Dynamo) NoSQL databases (key-value stores) Performance/scalabilit au dtriment de la cohrence

OLTP (On Line Transaction Processing)

Cible des SGBD depuis leur existence

Banques, rservation en ligne ...

DB in the (very) small: requtes sur

Trs grand nombre de transactions en parallle

Transactions simples

OLAP (On Line Analytical Processing)

Entrepts de donnes, DataCube, Data Mining

Nouvelles formes dacquisition de donnes

Rseaux de capteurs : calcul dagrgats Vhicules : recherche places libres, prdiction de traffic, alertes BD personnelles : tudes pidmiologiques, enqutes

Faible nombre de transactions

Transactions trs complexes

Intelligence ambiante : acquisition automatique de flux de donnes, prservation de la vie prive BD participatives : (ex: Wikipedia), administration distribue, data provenance, versions DB Crowdsourcing : multitude dacteurs humains capturant des donnes cognitives (critres subjectifs) ou difficiles accumuler de faon automatique
68

69

Orientations de recherche (1)

Mobilit

Orientations de recherche (2)

Gestion de donnes complexes

Masses de donnes distribues

Semi-structures : stockage, indexation, interrogation de docs XML Non structures : recherche par le contenu, index multidimensionnels, relations spatio-temporelles Intgrer lensemble de ces donnes dans un dataspace cohrent et en extraire de la connaissance

Architecture et performance (encore et toujours)

Requtes spatio-temporelles, BD embarques, cohrence des traitements dconnects, rseaux de capteurs, rseaux de vhicules

Entrepts de donnes et fouille

70

Mdiation de donnes : intgration de schmas, requtes de mdiation Mdiation de programmes : construction de workflows, optimisation des flux, composition de services Web interrogation de masses de donnes : requtes continues, personnalisation, critres approximatifs, publish/subscribe, etc BD en P2P, Content Delivery Networks : localisation des ressources/acheminement

volutions matrielles : grandes mmoires, nouvelles mmoires persistantes, nouveaux processeurs Paralllisme massif, Cloud computing SGBD auto-administrables conomie dnergie Donc : indexation, optimisation de requtes, rplication, transactions, benchmarks, etc

Recherche de rgles associatives, de dpendances fonctionnelles, fouille de donnes spatio-temporelles

Et bien sr Scurit des bases de donnes

Cf. transparent suivant
71

(liste non exhaustive)

Orientations de recherche (3) : scurit des BD

9- Lgislation 8- Anonymisation de donnes

2- Protection des communications

3- Autorisations

MS er DB rv e S
Extraction Requtes

User 6- Contrle dusage 5- Audit 4- Chiffrement des donnes

1- Authentification

7- Rtention limite des donnes

72