page 1 copyright s. mirandaconcepts de base syhnthèse sql2 (sql3 niveau 1) professeur serge miranda...

Copyright S. MIRANDA Concepts de base

DESSde l’Université de

Nice Sophia Antipolis

Syhnthèse SQL2

(SQL3 Niveau 1)

Professeur Serge MIRANDA

Directeur Dess MBDS

Université de Nice Sophia Antipolis

[email protected]

(Tiré

- du livre EYROLLES, S. Miranda, A.Ruols, « CLIENT-SERVEUR :

concepts, moteurs SQL et architectures parallèles » 3ème édition en Juin 96

-du Livre DUNOD, S.Miranda, 2001

« Des bases de données relationnelles aux bases de données objets »




Généralités

Les 4 facettes de SQL

Langage de définition

(d ’un schéma relationnel)

Langage de contrôle de la

base de données

(interface transactionnelle, …)

Langage de manipulation de

la base de données

Langage de communication

(client-serveur, Internet XML, BD réparties)

SQL

***

*

****

****

Notation : **** vaut le détour

*** mérite d ’être vu (3ème étoile en 89)

* il faut le voir mais …

SEQUEL :

« Structured English as a QUEry Language »

Prototype SYSTEM-R d ’IBM, SAN JOSE (1975)




Généralités

Les concepts structurels de SQL :

Tables

Colonnes

Lignes

PILOTE PL# PLNOM ADRESSE

1 JEAN PARIS

2 PIERRE NICE

3 PAUL PARIS

AVION AV# AVNOM CAP LOC

100 A300 300 NICE

101 B707 250 PARIS

102 A300 300 LYON

103 B727 370 LYON

VOL VOL# PL# AV# VD VA HD HA

IT100 1 100 NICE PARIS 7 8

IT101 2 100 PARIS NICE 11 12

IT103 1 103 LYON PARIS 14 15

IT104 2 102 NICE NICE 17 18




Généralités

Modèle de Codd SQL (SEQUEL*)

Français Anglais

Relation Relation Table

Domaine Domain Domain (SQL2)

Attribut Attribute Column

n-uplet (tuple) Tuple Line

Clé primaire Primary key Primary key

(ORACLE V6, DB2, V4, ….)

Clé étrangère Foreign key References

(ORACLE V6, DB2 V4, ….)

* SEQUEL : « Structured English as a QUEry Language »




Définition du schéma

Create schema

< Création de deux domaines Ville et Heure >

create domain1 Ville as char (12)

default ‘ PARIS ’

check (value in(‘ PARIS ’, ‘ NICE ’, ‘ TOULOUSE ’))

create domain Heure as hour

check (value > 7 and value < 22)

< Création des tables >

create table2 PILOTE

(PL# decimal (4),

PLNOM char (12),

ADR Ville check (value in (‘ PARIS ’, ‘ NICE ’)),

SAL decimal (5),

primary key (PL#))

create table AVION

(AV# decimal (4),

AVNOM char (12),

CAP decimal (3) check (value > 100),

LOC Ville,

primary key (AV#))





Create table VOL

(VOL# char(5),

PL# decimal (4) not null,

AV# decimal(4),

VD Ville,

VA Ville,

HD Heure,

HA Heure,

primary key (VOL#),

foreign key (PL#) references PILOTE,

initially deferred,

foreign key (AV#) references AVION,

on delete cascade, on update set null))

alter domain …. Drop constraint

Exemple :

create domain Ville as char(12)

constraint c-Ville check (value in (‘ PARIS ’, … ))





Type syntaxique

Char(n), decimal, integer, bit, float, …

date (year, month, day),

times (hour, minute, second),

timestamp,

interval

Dictionnaire … relationnel

(INFORMATION-SCHEMA CATALOG) comprenant des tables

systèmes accessibles par …. SQL.

ALTER TABLE (ADD COLUMN, … ) DROP TABLE





Dictionnaire Relationnel Intégré

Exemple : DB2 comprend un dictionnaire intégré

(« CATALOG ») qui comprend des tables systèmes.

Les PRINCIPALES

SYSTABLES NAME CREATOR COLCOUNT

Pilote Serge 4

Avion Serge 4

Vol Serge 7

SYSCOLUMNS NAME TBNAME COLTYPE

PL# Pilote SMALLINT

PLNOM Pilote CHAR

ADR Pilote CHAR

AV# Avion SMALLINT

…. …. ….

SYSINDEX NAME TBNAME CREATOR

PX Pilote Serge

AX Avion Serge

VX Vol Serge





Cluster : « Jointure dans la structure physique »

(System-R, Oracle)

Exemple : Cluster entre PILOTE et VOL sur PL#

Bloc de cluster pour chaque valeur de PL#

Exemple d ’un bloc de cluster pour la valeur pl# = 10

10 SERGE NICE

IT 100 10 PARIS 100 NICE 7 8

IT 101 10 NICE 101 PARIS 11 12

IT 105 10 PARIS 104 TOUL 15 46

Create cluster PV (PIL# number (4))

(clé du cluster)




Manipulation BD

2 Interrogation et mise à jour d ’une base

de données (noyau SQL)

2.1 SQL interactif

Interrogation : <Mapping SQL>

Select < liste attributs cible ou * >

from < tables >

where < qualification sur les lignes > avec in,

exists, any, all, and, or, not, between, like, …

group by < attributs partitionnés >

having < qualification sur les partitions >

order < tri >

union

Mise à jour :

update R

set

where

Suppression :

delete

from R

where

Insertion :

insert

into R

values




Manipulation BD

2.2 SQL « intégré » (« embedded SQL »)

Pointeur logique : « cursor » (« impedance mismatch »)

(exec sql)

declare CX cursor for < requête SQL >

(exec sql)

open / fetch / update / delete / close CX (cursor)

Current (élément pointé)

Code retour : sqlcode




Manipulation BD

Exemple 3 : Exemples de requête de partitionnement

(GROUP BY)

select * from vol ;

VOL VOL# PL# AV# VD VA

IT100 1 50 NICE PARIS

IT101 2 50 PARIS TOUL

IT103 2 50 TOUL PARIS

IT104 1 51 PARIS NICE

IT105 1 52 NICE LYON

group by PL# :

PL# = 1 VOL# AV# VD VA

IT100 50 NICE PARIS

IT104 51 PARIS NICE

IT105 52 NICE LYON

PL# = 2 VOL# AV# VD VA

IT101 50 PARIS TOUL

IT103 50 TOUL PARIS




Manipulation BD

Un peu de version …..

Select PL#, COUNT(*) …

from VOL

…

group by PL#

having count (*) 3 ;




Manipulation BD

Traduction des requêtes SQL suivantes en Français

SQL :

select PL#, count (*)

from VOL

group by PL#

having count (*) 3 ;

« Quels sont les numéros de pilotes qui assurent plus de trois vols

(avec le nombre de vols assurés) ? »

SQL :

select PL#, count (*)

from VOL

where VD = ‘ NICE ’ (1)

group by PL# (2)

having count (*) > 3 ; (3)

Requête en français :

« Quels sont les numéros des pilotes (avec le nombre de vols assurés)

qui assurent plus de 3 vols au départ de Nice ? »




Manipulation BD

INTERSECTION DIFFERENCE

SQL1 SQL2 SQL1 SQL2

select AV#

from AVNICE

(select AV#

from AVNICE)

select AV# from

AVNICE

(select AV#

from AVNICE)

where exists intersect where not exists except

(select AV#

from AVAIRBUS

where

AVAIRBUS.AV#

= AVNICE.AV#);

(select AV#

from

AVAIRBUS) ;

(select AV#

from AVAIRBUS

where

AVAIRBUS.AV#

= AVNICE.AV#);

(select AV#

from

AVAIRBUS);

D ’autres solutions

existent avec in, =

any, …...

SELECTION PROJECTION

select *

from AVION

where CAP > 200 ;

select AV#, AVNOM

from AVION.




Manipulation BD

JOINTURE DIVISION

« Qules sont les noms des pilotes en service

au départ de Nice ? »

Il existe 7 façons différentes dont les deux

principales sont :

- Solution prédicative :

select PLNOM

from PILOTE, VOL

where VOL-VD = ‘ NICE ’

and PILOTE.PL# =

VOL.PL# ;

- Solution ensembliste :

select PLNOM

from PILOTE

where PL# in

(select PL#

from VOL

where VD = ‘ NICE ’);

« Quels sont les numéros de pilotes qui

conduisent TOUS les avions ? »

select distinct VX.PL#

from VOL VX

where not exists

(select AV#

from AVION

where not exists

(select *

from VOL VY

where VY.PL# = VX.PL#

and AVION.AV# = VY.AV#));




Q27 : Quels sont les noms des avions dont la capacité

est supérieure de 10% à la moyenne des

capacités des avions ?

select *

from AVION

where CAP >

(select avg (CAP)* 1.1

from AVION) ;

(2) est évaluable séparément de (1)

Manipulation BD (exemples)

Sous-requêtes « indépendantes » :

(1)

(2)

select distinct PL#

from VOL

where AV# in

(select AV#

from VOL

where PL# = 100);

Q28 : Quels sont les numéros des pilotes qui

conduisent un avion conduit aussi par le pilote

n° 100 ?





Avec un seul mapping, on aurait dû introduire une variable de parcours :

select distinct PL#

from VOL AS VX

where VOL.AV# = VX.AV#

and VX.PL# = 100;

Une double variable de parcours pourrait être introduite :

select distinct VX. PL#

from VOL AS VX, VOL AS VY

where VX. AV# = VY.AV#

and VY.PL# = 100;





Sous-requêtes « dépendantes »:

Q29 : Quels sont les avions dont la capacité est

supérieure de 10 % à la moyenne

de capacités des avions localisés dans la

même ville ?

select *

from AVION AX

where AX.CAP >

(select avg (AY.CAP)* 1.1

from AVION AY

where AX.LOC = AY.LOC);

(1)

(2)

L’évaluration de (2) requiert la connaissance d ’une valeur AX.LOC de (1)




III Contrôle BD

Contrôle de la base de données (noyau SQL)

« TRANSACTIONS »

begin / end transaction

Commit (Work)

Rollback (work)

lock table in exclusive mode

/shared mode

« VIEWS »

create view as < requête SQL >

with check option

grant / revoke

(« public », « user », ….)




Contrôle BD

Vue (« view ») en SQL2

Table virtuelle (seule la définition est stockée)

Exemple : Création d ’une vue de sécurité pour cacher

les lignes de PILOTE correspondant à des salaires

supérieurs à 50 000 F :

create view BAS-SALAIRE as

select *

from PILOTE

where SAL 50.000;

GRANT / REVOKE :

ex :

grant update on BAS-SALAIRE to SERGE




Contrôle BD

Transaction en SQL

Une transaction SQL est un ensemble d ’opérations SQL rendu

« atomique » (« tout ou rien ») qui permet de passer d’un état cohérent

de la BD dans un autre (« A et C » de ACID)

Dans SQL2, le début d ’une transaction est implicite (pas de verbe

BEGIN TRANSACTION)

et la fin correspond au verbe COMMIT

(terminaison correcte) ou ROLLBACK (annulation et retour arrière)

état cohérent i

de la base de

données

état cohérent j

de la base

de données

T (« COMMIT » : Tout)

T (« ROLLBACK » : Rien)




3 . Contrôle BD

Propriétés ACID d’une transaction

ACID

A : Atomicité

C : Cohérence

I : Isolation (effets d’une transaction non observables par une autre)

D : Durabilité




3 . Contrôle BD (Transaction)

Transaction « bien formée » (1)

chaque action de lecture est précédée de

LOCK - Read

chaque action d ’écriture est précédée de

LOCK - Update

« Verrouillage à 2 phases » (2)

phase de croissance d ’acquisition de verrous

(« SEIZE BLOCK »)

phase de décroissance de libération de verrous

Si (1) et (2) alors il ‘ y a sérialisibilité

des transactions concurrentes





Problème d ’interblocage

T5 : Modifie la table

AVION et modifie la table

VOL.

begin T5

…..

Lock table AVION in

excusive mode

update AVION

set …..

lock table VOL in

exclusive mode

wait

wait

…..

T6 : Modifie la table VOL et modifie la

table AVION.

begin T6

….

lock table VOL in

exclusive mode

update VOL

set ….

lock table AVION in

exclusive mode

wait

wait

…..t

T5 qui a verrouillé AVION est en attente de VOOL; T6 qui a verrouillé VOL est en

attente d ’AVION. Si aucune action n ’est entreprise, c ’est l ’attente infinie ou

interblocage entre T5 et T6.

L ’interblocage peut être généralisé à n transactions, avec n>2; il résulte l ’existence

d ’un cycle dans le graphe d ’attente entre transactions.





Solution à l ’interblocage (DB2)

Construction d ’un graphe d ’attente

AVION VOL

T5

T6

T6

T5

T : verrou exclusif posé par T

T : demande de verrou par T

Détection d ’un cycle dans le graphe d ’attente, puis choix d ’une victime

(ex : T6) qui devra défaire (ROLLBACK) ses actions.

Prévoir un test de présence d ’interblocage (ex : valeur négative de

l ’indicateur SQLCODE) dans le programme d ’application.

exec sql …

if sqlcode < 0 (valeur indiquant l ’interblocage)

then do

exec sql rollback

….

end




Niveaux d’Isolation de SQL2

Les dangers d’interférence n’ont pas le même niveau d’importance d’où la définition de niveaux d’isolation

NIVEAUX d’ISOLATION de ANSI/ISO et SQL2 NIVEAU 0 READ UNCOMMITED : le plus permissif ; garantie d’absence de pertes de MAJ ; verrous Exclusifs posés avant écriture et libérés après

NIVEAU 1 READ COMMITED : + Cohérence des MAJ : libération des verrous en fin de transaction

NIVEAU 2 : READ REPEATABLE : + Cohérence des lectures par ajout d’un verrou partagé avant READ et libération en fin de lecture

NIVEAU 3 SERIALIZABLE : le plus contraignant avec libération des verrous en fin de transaction ; OK reproductibilité des lectures

Note : le niveau 3 des transactions n’interdit pas les tuples fantômes

Exemple : Oracle prend en compte par défaut le niveau READ COMMITTED et le niveau d’isolation peut être modifié par la commande ISOLATION LEVEL ‘serializable’ dans Set Transaction




VERROUILLAGE d ’INTENTION

(2 nouveaux types de verrous)

Conséquence granularité : Si T1 verrouille une table, il faut vérifier qu’il n ‘existe aucun verrou incompatible (cf matrice de compatibilité, Microsoft) sur chacun des tuples de la table (HIERARCHIE DE VERROUS)

Introduction du Verrouillage en intention de lecture et en intention d’écriture

Nouvelle Règle : avant de verrouiller le tuple RO1, la transaction T1 doit poser un verrou d’intention sur la relation R contenant RO1

Si T2 veut verrouiller R il peut y avoir incompatibilté ; par contre T2 peut verrouiller RO2 de T avec un verrouillage d’intention compatible sur T

Note : IBM a défini 6 nouveaux types de verrous avec 13 Combinaisons possibles, MICROSOFT 2 nouveaux types (Update, Schema..)…

compatibilité

VERROUSIntention Read

ReadIntention

Update

Update

Intention Read

READ

Intention

UPDATE

UPDATE

OUI

OUI

OUI OUI

OUI

OUIOUI



Nice Sophia Antipolis2PC et REPLICATION

1) Systèmes de réplication (« SNAPSHOT ») : pour DWH, Architecture multi-tier, Internet sans fil..

EX : Oracle 8 : Create SNAPSHOT Volparis as <requête SQL> avec raffraichissements réguliers des copies (complet ou « rapide ») ; réplication de tables, index, vue, trigger ou package

ARCHITECTURES DE REPLICATION :

- architectures maître esclave (DIFFUSION sites primaires, CENTRALISATION, CICULAIRE et CASCADE)

- architectures symétriques (« update anywhere) avec mises à jour asynchrônes (contrôle centralisé ou réparti) ou synchrônes (2PC et RPC)

EX SQL Server de Microsoft : Modèle « PUBLISH and SUBSCRIBE » (Editeur/ Soucripteur) avec 3 types de réplication : SNAPSHOT (read only comme DWH), TRANSACTIONAL (cohérence faible, envoi TI validee) et MERGE (cohérence forte)

2) 2PC : « Two-phase commit »: Protocole de terminaison à 2 phases pour les infostructures réparties (client serveur, réplication, BDR, Teleservice Internet) sur réseau fiable

Seule façon d’assurer une COHERENCE FORTE

EX : dans Oracle 8 : 2PC automatique lors du Commit d‘une Ti répartie




3 . Contrôle BD (Reprise sur pannes

et transactions)

Mécanismes de Reprise sur panne (DB2)

Journal de modification (« LOG ») et points de reprise

Protocole d ’écriture anticipée sur le journal

(« write -ahead log protocol »)

Paradigme faire/défaire/refaire

(« do, undo, redo »)

Reprise à chaud / à froid

Ecriture sur double (« shadow mechanism ») d’ IBM et

Oracle (« segment de ROLLBACK »)

mécanisme « Multiversion » avec

SCN (« System Change Number »)d’Oracle

Note : FALLBACK de TERADATA




JOURNAL DE MODIFICATION (LOG)

Seule Opération atomique : E/S d’un bloc

JOURNAL pour chaque T : IMAGE AVANT de chaque MAJ, IMAGE Après de chaque MAJ, Enregistrement BEGIN, Enregistrement END (Commit, Abort), Enregistrements de SAVE Point, Etat transaction

Objectifs de la reprise sur panne :

- Modifications des transactions non validées doivent être ignorées

- Modifications -//- validées doivent être prises en compte



Nice Sophia Antipolis3 . Contrôle BD (Transaction)

Protocole « Faire / défaire / refaire »

Ancien état

de l ’objet

(image avant )

FAIRE Nouvel état

de l ’objet

(image après )

JOURNAL

Nouvel état

de l ’objet DEFAIRE Ancien état

de l ’objet

JOURNAL

Ancien état REFAIRE Nouvel état

JOURNAL lecture

lecture

écriture



Nice Sophia Antipolis API SQL2

Interfaces de programmation

L ’application (API) avec SQL

- SQL dynamique,

- intégration dans langage hôte (« embedded SQL »),

- langage du module.

SQL dynamique

Trois types de verbes SQL dynamiques :

execute immediate

prepare from et execute;

(deallocate prepare pour la supprimer);

prepare from et manipulation des curseurs.

(open, fetch, close, update et delete current).

SQLDA (« SQL descriptor area »)



Nice Sophia Antipolis API SQL2

Intégration dans langage hôte

exec sql begin and declare section.

Langage du module

Le langage de module est un petit langage pour écrire des requêtes SQL.

MODULE : = ensemble de procédures

PROCEDURE : = un ensemble de définitions, de paramères

et une seule requête SQL exprimée avec ces

paramètres.



Nice Sophia Antipolis Critiques de SQL

SQL et l'algèbre relationnelle :

SQL n ’est pas un ….

« bon » langage relationnel !

SQL et les langages de programmation :

SQL n ’est pas un ….

« bon » langage de programmation !

page 1 copyright s. mirandaconcepts de base syhnthèse sql2 (sql3 niveau 1) professeur serge miranda...

Documents