Transcript SQL3 - MBDS

Introduction à SQL3
(SQL99)
SELECT quelque chose avec image
FROM quelque part ++ WHERE
condition ++
Professeur Serge Miranda
[email protected]
Master2 « MBDS »
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
www.mbds-fr.org
BD" (Part IV)
1
Contenu
I
II
Présentation des principales
caractéristiques de SQL3
niveaux 2 et 3 (définition,
manipulation, contrôle,
développement)
Présentation critique du
double paradigme : valeur et
pointeur (type REF) dans
SQL3
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
2
Standard SQL?



ANSI (American National Standard Institute) X3H2
database language committe (X3 division ANSI pour Syst.
de Traitement Information) : définition SQL3 de 1991
à 1999 avec 28 membres votants :
http://www.ansi.org
ISO : International Standard Organization comprenant
ANSI, AFNOR (France)… et
NIST (http://www.ncsl.nist.gov)
4 phases




Working draft (WD)
Committee draft (CD) pour révision ouverte (1/96)
Draft international standard (DIS) (12/98)
International standard (7/99)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
3
Standard SQL(petite histoire)



SEQUEL 1 (1974) de System R d’IBM
SEQUEL 2 (1977)
SQL 1 (Ansi : 1986 ; ISO : 1987) : 100 pages puis
révisions en 1989, « SQL89 »
Avec des systèmes de tests et de validation développés par
le NIST (National Institute of Standards and Technology) pour
éviter problèmes de Codasyl

SQL2 (1992, « SQL92 ») par X3H2 : 600 pages
avec 3 niveaux


SQL3 ( Standard en 1999, « SQL99 ») : 1500 pages


Niv. 1 : « Entry » (SQL1), 2: « Intermediate » et 3: « Full »
Niveau 1 (SQL2)…
SQL4…
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
4
Standard SQL3







Part1 : Framework
Part2 : Foundation (noyau avec « TDA »)
Part3 : SQL/CLI (interface d’appel client)
Part4 : SQL/PSM (lang. procédures stockées)
Part5 : SQL/Bindings
Part6 : SQL/XA (moniteur TP)
Part7 : SQL/Temporal
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
5
Rappels : Principales nouveautés SQL2



+ d’ « orthogonalité » (EX: Tables dérivées dans
from..)
+ de types : varchar, bit, character sets, date, time,
interval
+ d’opérateurs :Union , Cross et Natural JOIN …
Difference (EXCEPT) et Intersection (INTERSECT)
Prédicats MATCH, BETWEEN, LIKE (Exemple : ..Where
AVNOM Like ‘Airbus%’) …






Apparition partielle des domaines : create DOMAINS
NULL
+ Définition relationnelle :Clé primaire et intégrité
référentielle
Niveaux d’isolation
pour
transactions,
CASE…
Copyright
Serge MIRANDA,
"Objets et
13/04/2015 10:21
BD" (Part IV)
6
Modèle « OR » ?

Extensions RICE du modèle relationnel :
-
REUTILISATION (Héritage, polymorphisme)
IDENTITE et références
COMPLEXITE des Objets (Collections et produit)
ENCAPSULATION (types et fonctions utilisateur)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
7
Concept OBJET dans SQL3 ?

Dualité de structures de données SQL3 pour
CLASSE D’ OBJETS : « TABLES »
(Relations) et « TYPES » (Domaines) :
CREATE TABLE
CREATE TYPE
Note : un concept de trop (!) ou préparer Date et
SQL4 ? + possibilité de travailler…proprement !
Persistance automatique avec TABLE !
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
8
Classe OBJET dans SQL3 avec
TABLE
1) « TABLE » : Create table
structure de données de base (relation NF2
possible) pour le stockage en mémoire persistante et
la manipulation ensembliste avec VALEURS et
POINTEURS (double paradigme !)
2 types de TABLE :
- « Table Objet » possible (clause «CREATE
TABLE … With Identity »)
 ROWID  Type REF possible
- Table « relationnelle » sinon

13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
9
Classe OBJET dans SQL3 avec
TYPE
2) « TYPE » : Create type (classe d’objets)
2 types de …TYPE :
- (« VALUE ») ADT


Pas d’ OID, valeur pour attribut dans relation
Types de base
- (« OBJECT ») ADT (Create type with OID
<visible>)



13/04/2015 10:21
OID pour chaque instance (pas d’ OID par défaut)
Types possibles pour attributs ou autres ADT, variables SQL…
Type REF (avec OID) possible associé
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
10
SQL3
R:
* HERITAGE MULTIPLE hybride
(clause"UNDER" pour les TABLES et les
TYPES)
* POLYMORPHISME (Surcharge) et
TEMPLATES (types génériques/ paramétrés)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
11
SQL3
I:
(dualité de structure, dualité d’OID) :
Pour les tables : "ROW ID" (explicite"with identity") pour encapsulation de tuple et
type REF
Pour les TYPES : "Object ID" (« with
OID visible » ) pour les classes d’objets
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
12
SQL3
C
: TYPES (ADT) et TABLES
ADT : types de données complexe défini par
l'utilisateur :
"OBJECT ADT" (avec OID) : classe d’objets
" VALUE ADT » (sans OID) : type de base
Support d’objets complexes (tables ou ADT) :
- Constructeur de Collections : "SET",
"LIST", "MULTISET" (agrégation de tables)
- Constructeur tuple/produit cartésien (ROW)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
13
SQL3
C
: TYPES (ADT) et TABLES
TABLES avec
- « With identity » (ROWID) : classe d’objets
- sans ROWID : table relationnelle
des attributs "stored" ou "virtual"
("updatable", "read-only", "constant")
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
14
SQL3
E
* PSM (Persistent Stored Module) : regroupement de
routines dans schéma objet/procédures STOCKEES
* "Routines"(SQL ou externe) associées aux
tables/ADT/base :
-"Functions"("Actor » , « Constructor » ou
"destructor")
-"Procedures"
* 3 Niveaux d'encapsulation pour un ADT (idem
C++)
("Private","Protected« (sous type), "Public")
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
15
SQL3 ?
Un langage de définition de types/tables
 Un langage de programmation (objets)
 Un langage de requêtes (OR)
 Un langage de communication
 Un langage temporel

13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
16
SQL3 (Exemple Définition)
DEUX possibilités pour une entité conceptuelle
/CLASSE OBJET : table ou type (ADT) ayant
chacune un OID système possible
1- CREATE TABLE
Exemple :
Create Table Personne
(Nom char (20),
Sexe char (1),
Age integer
Conjoint REF Personne WITH IDENTITY)
notes :
- WITH IDENTITY ( ROWID ) : Des Routines peuvent être
associées aux tables pour implanter des opérations encapsulant les
lignes + type REF possible sur la table Personne
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
17
SQL3 (Exemple)
2-
CREATE <OBJECT> TYPE (With
OID…)
Exemple:
Create TYPE personne_type with OID
(nom varchar not null,
sexe constant,
age virtual...
date-naissance date,
ACTOR FUNCTION age
(:P personne_type) return real ...
DESTRUCTOR FUNCTION enlever_personne..;
End function)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
18
SQL3 (Exemple)
(suite create personne_type )
CREATE TABLE Personnes
(donnees_personne : personne_type)

La seule façon qu'une instance de TDA soit stockée de
manière persistante dans la BD, c’ est de la stocker
comme valeur de colonne d'une table
(seule différence importante entre TABLE OBJET et
ADT OBJET !)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
19
SQL3 (Exemple)
A partir d’ Oracle 9
- TABLE « OBJET » : Create table T of type t
Exemple :
Create type personne_type as object (P#,…)
Create table personnes of type personne_type
(avec OID, faisant de la table une table objet)
- distinction OID (16 Octets, immuable, non indexable) et
ROWID (10 octets, non immuable, indexable)
- PKOID (pseudo OID sur les vues : « Primary key
OID »)/PKREF : une vue peut servir à créer un OID sur
une table relationnelle (vision objet..de TABLES)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
20
SQL3 (Exemple) : héritage
3- <à partir de -1- Create Table Personne>
CREATE TABLE Pilote UNDER Personne
(NbreHeures integer)
<UNDER pour héritage structurel et opérationnel>
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
21
SQL3 (Exemple) : type REF
CREATE TABLE Vol
(Vol# Char(4),
PL# REFERENCE Pilote (PL#) <clé étrangère :
valeur clé primaire avec manipulation relationnelle>
REFPIL REF Pilote <type REF : ROWID, pointeur
ligne avec opérateurs spécifiques!>
...)


La valeur de l’attribut REFPIL est un RowID/pointeurligne et non plus une valeur de clé primaire ; opérateur
DEREF associé aux pointeurs (pas SQL !)
La clé étrangère et le type REF peuvent cohabiter dans
une même table
(cf Oracle 9)
Copyright Serge MIRANDA, "Objets et
13/04/2015 10:21
BD" (Part IV)
22
SQL3 (Exemple 2D )
CREATE (OBJECT) TYPE RECTANGLE
with OID (x1, y1, x2, y2 real)
Actor function MAKE_RECT...
return RECTANGLE ;
<Point dans rectangle ?>
Actor function IS_IN (x,y real, R RECTANGLE)
if (R.x1<=x and x<=R.x2) and (R.y1<=y and
y<=R.y2) then return TRUE else FALSE ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
23
SQL3 (Exemple 2D )
<côté dans rectangle ?>
Actor function ONE_SIDE_IS_IN (R0, R
RECTANGLE) if (IS_IN (R.x1, R.y1, R0) or
IS_IN(R.x2, R.y1, R0) or IS_IN (R.x2, R.y2, R0)
or IS_IN (R.x1, R.y2, R0) )
then return TRUE else FALSE;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
24
SQL3 Définition
(Exemple 2D-suite)
<intersection non vide de deux rectangles ?>
Actor function OVERLAP (R1, R2
RECTANGLE) if ONE_SIDE_IS_IN (R1 R2)
then return TRUE else if ONE_SIDE_IS_IN
(R2,R1) then return TRUE else return FALSE;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
25
SQL3 Manipulation
(Exemple 2D-suite)
Create table RECTANGLES
(datarectangle RECTANGLE)
requête SQL3
SELECT *
FROM RECTANGLES R
WHERE OVERLAP (R,
MAKE_RECT (0,1,0,1)) = TRUE ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
26
SQL3
(Présentation des nouveautés,
Niveaux 2 et 3)
Définition
Manipulation
Contrôle
Développement/Programmation
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
27
SQL3 (Définition) : Types de
base
Types de Base

NOMBRES :
•
•




13/04/2015 10:21
Integer, Smallint, Numeric, Numeric (P) et Numeric (P,S),
Decimal (P) et Decimal (P,S) (Precision, Scale)
Real, Double Precision et Float
Chaînes de caractères : Character, character varying, national character
Chaîne de bits : Bit, Bit Varying
Date : date, time, timestamp et Intervalle temporel : Interval
day/month/year
Indéfini (NULL) et logique (boolean)
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
28
SQL3 (Définition) : Nouveaux
types
Enumération,
booléen,
Character LOB (CLOB) , binary LOB (BLOB) ,
Constructeur tuple/produit cartésien : row,
Constructeur de collection : set, multiset et list,
Distinct (ud) types


13/04/2015 10:21
Create distinct type monnaieeuro as integer (9)
Create distinct type monnaieusa as integer (9)
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
29
SQL3 (Définition)
Constructeur ROW (produit
cartésien)
Constructeur ROW (expression) soit au niveau des
domaines soit directement au niveau des attributs d’une table ou
d’un type



Create domain adresse ROW (numero.., rue.., ville,.. Zip..)
Create ROW type adresse (numero, rue, ville, zip)
Create table/type Pilote
(…,
Adresse ROW( numero varchar (3), rue varchar (12),..)
ROW à utiliser dans INSERT avec opérateur « .. » pour accès à sous partie
(Note : « . » dans Oracle 9)
ROW permet d’avoir des tables imbriquées dans des tables (NF2) idem attribut
de type table/ADT
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
30
SQL3 (Définition) : ADT
Rappel : ADT (type de
données « abstrait »/Abstract data type)
1- Déclaration (attributs, méthodes) : « type specification » avec « member » pour
méthodes dans Oracle 9
2- Implantation (méthodes) : « type body » dans Oracle

Concept de classe d’objets avec le concept d’ ADT avec OID
(create type with OID ) qui comprend 2 parties visibles :
 Les
spécifications des attributs
 les méthodes/routines associées
(base, type ou table) : « functions » (Actor/
Constructor/ Destructor) ou « procedures »
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
31
SQL3 (Définition) : ADT
• CREATE TYPE <nom ADT> <corps de l’ADT>
• <corps de l’ADT>
– <OID option> ::= WITH OID (VISIBLE)
– <subtype clause> ::= UNDER <supertype clause>
• possibilité d’héritage multiple avec résolution explicite
– <member list>
• <column definition> : attributs publics ou privés
• <function declaration> : opérations publiques
• <operator name list> : opérateurs surchargés
• <equals clause>, <less-than clause> : définition des ordres
• <cast clause> : fonction de conversion de types
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
32
SQL3 (Définition) : ADT et
CONSTRUCTOR
Création instance de TDA par une fonction CONSTRUCTOR
Exemple pour TDA Adresse :
CONSTRUCTOR FUNCTION adresse-t () RETURNS adresse-t
Declare : a adresse-t
Begin
New : a ;
Set : a.numero = null ;
Set : a.ville = null ;
…return a ;
END;
END Function
<CONSTRUCTOR : Fonction spéciale ayant le même nom que le type>
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
33
SQL3 (Définition) : ADT et
CONSTRUCTOR
Exemple :
Create table pilote of type pilote-t
Insert into pilote values
(pilote-t (1, Serge, adresse-t (MBDS, Sophia, 06 060606))
Update pilote p set p.adresse = adresse-t (MBDS, Sophia Antipolis,
0606060606) where p.nom = ‘Serge’ ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
34
SQL3 (Définition) : ADT et
fonctions
Suppression instance d’un TDA par fonction
DESTRUCTOR
Exemple :
DESTRUCTOR FUNCTION sup-adresse (:a adresse) RETURNS adresse
BEGIN Destroy : a ; Return : a; END; END Function
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
35
SQL3 (Définition) : ADT

Accès aux attributs d’un TDA :

fonctions OBSERVER (lecture) , MUTATOR (mise à jour)
<génération automatique fonction OBSERVER par déclaration de
variable>
Exemple :
Declare a adresse
Declare V varchar (12)
V = ville (a) ; <fonction OBSERVER « ville »>
<mutator> : ville (a, ‘Valbonne’) ;
Note : ces fonctions sont généralement intégrées dans SQL3 (Update..)
Tout attribut « A1 du type t du TDA nomADT » a une fonction
OBSERVER implicite : « (Function) A1 (arg1/nomADT) Returns
t»
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
36
SQL3 (Définition) : ADT

Accès aux attributs d’un TDA :
Notation « .. » du type : <(nom variable ADT)..Fonction>
Exemples :
-
a..ville pour invoquer la fonction ville du TDA Adresse
-
Pour un TDA Pilote (variable P) ayant un attribut « domicile » défini sur
le TDA « Adresse », on peut écrire avec les déclarations ci dessus :
(set ) v= P..domicile..ville
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
37
SQL3 (Définition) : ADT

Fonction CAST sur TDA pour modifier type
Exemple:
WHERE VOL..HA>>
(CAST (VOL..HD AS Date) + 4 Hours)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
38
SQL3 (Définition) : ADT

Utilisation d’un ADT ?

Type d’un Attribut d’un autre ADT
Exemple :
remplacer le type DECIMAL du champ SALAIRE par un
TDA SALAIRE pour des raisons de sécurité..


Sous –type d’un autre ADT (Clause UNDER)


Exemple : Create type pilote under employe
Types d’un paramètre dans une routine

Exemple : Create function salaire (p pilote) returns Decimal (6,2) Return(..) ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
39
SQL3 (Définition) : ADT

Utilisation d’un ADT ?

Type de variable SQL

Exemple : declare vp pilote puis Select * Into vp From Pilote Where..

Type source d’un type DISTINCT

Type d’un attribut d’une table ou d’un domaine (sémantique
additionnelle avec contraintes Check )

Exemple : Create domain Paris-adresse as adresse Check (value (a.ville) = ‘Paris’) default null;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
40
SQL3 (Définition) : Déclencheur

Définition de déclencheur :
CREATE TRIGGER
BEFORE/AFTER <événement> of <Att> on
<Table> REFERENCING OLD/NEW as <x>
<action>




« BEFORE/AFTER : possibilité de déclencher avant ou
après l'événement
<événement> = INSERT, UPDATE, DELETE
possibilité de référencer les valeurs avant ou après mise à
jour (« REFERENCING OLD/NEW as »)
<action> = opération sur table (INSERT..) avec
condition possible (« WHEN » « WHERE »)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
41
SQL3 (Définition) : Déclencheur
(trigger)

Exemple1 :
PILOTE (ID int, salaire float)
CUMUL (ID int, Augmentation float)
Définition d’un déclencheur après toute mise à jour du champ « salaire » de
PILOTE qui met à jour l’attribut « augmentation » de la relation
CUMUL
CREATE TRIGGER AFTER UPDATE OF salaire ON pilote
REFERENCING OLD AS ancien_salaire, NEW AS
nouveau_salaire
UPDATE CUMUL SET Augmentation = Augmentation +
nouveau_salaire - ancien_salaire WHERE ID = pilote.ID
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
42
SQL3 (Définition) : Déclencheur
(trigger)

EXEMPLE 2 : Définition d’un déclencheur après toute mise à jour
de l’attribut HA de Vol avec mise à jour de la vue
VOLINDIRECT…
Create Trigger T1 after update of HA on Vol
Referencing old as ancienneHA new as nouvelleHA
Update volindirect set ….(nouvelleHA- ancienneHA)…
;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
43
SQL3 (Définition) : TABLES

CREATE TABLE avec clauses

with identity ( RowID et Type REF dessus),
on commit,
 global/local/temporary,
 le type REF et les Objets lignes (RowID),
 les collections pour les attributs multivalués
(peut être dans SQL4) (ARRAY, SET, MULTISET et LIST),
 constant/updatable
 clauses under (héritage) with ,


constructeur de table avec VALUES (expression)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
44
SQL3 (Définition) : type REF

Type REF pour localiser une instance de TDA
ou un tuple via leur ROWID (type et table
« objets »)
- valeur de Référence obtenue par l’ opérateur de
référencement REF («  », « . » dans Oracle)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
45
SQL3 (Définition) : TABLES

Une TABLE peut avoir des attributs …




définis sur un TDA
Complexes : Multivalués (SET, LIST) ou produit (ROW)
de type REF
SOUS-TABLE : Une TABLE peut être obtenue par
héritage ( héritage multiple)
EX : Create type PILOTE_EN_FORMATION under PILOTE,
FORMATION (..)
Note : le sous type Pilote_en_formation hérite des attributs de Pilote et de
Formation (création automatique type ROW de Pilote)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
46
SQL3 (Définition) : TABLES

• Possibilité
d'utiliser un type prédéfini pour la table (Vision
tabulaire d’une classe ; persistance des objets d’une classe)
EX : CREATE TABLE pilotes OF t-pilote ;
• Possibilité de définir un nouveau type
– Le type est celui des tuples de la table
EX : CREATE TABLE Constructeurs OF NEW TYPE
Constructeur (nom VARCHAR, total MONEY) ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
47
SQL3 (Définition) : TABLES
Intégrité de domaine (NULL, NOT NULL, Check)
Intégrité référentielle
- Restrict, Cascade, Set Null, Set Default +
- « NO ACTION » : Idem Restrict mais test à la fin du module SQL
Tables de base et temporaires
CREATE TABLE



Persistante (table de base) : Create/drop Table
Global temporary : une référence dans SQL crée une instance distincte;
dans session SQL ayant plusieurs modules, transactions…
Local temporary
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
48
SQL3 (Définition) : TABLES
Les instances d’un TDA deviennent persistantes par
association avec une table de base
(en attendant idem « table temporaire »)
Exemple :
Create type Pilotedessai (..)
Create table Pilote ( pil Pilotedessai, salaire Decimal
(6,2), ..)
Création instance par :
13/04/2015 10:21
Declare : p Pilotedessai ;
Begin
new : p
set : p.numero = ‘100’
set : p.nom = ‘paul’
… Return :p;
Copyright Serge MIRANDA, "Objets et
END
BD" (Part IV)
49
SQL3 (Définition)
Exemple :
Create table AVION <ou CreateType AVION>
( AV# Integer,
AVNOM varchar (12),
AV-PHOTO blob (600 K),
CONTRAT clob (60K),
REFPILOTE REF Pilote <Pilote attitré>
Private PRIX Decimal (9,2)
CAP SET Integer, <ensemble de capacités>
LOC SET adresse, <Ensemble d’adresses ; insertion avec SET (ROW(‘..’))>
REVISION boolean with identity)
Exercice : « Représenter en schéma ODMG cette table SQL3 (With Identitry dans
AVIONpermet de représenter le pointeur INVERSE)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
50
SQL3 (Définition)
CREATE TABLE Vol
(Vol# Char(4),
...
PL# REFERENCE Pilote (PL#) <clé étrangère :
valeur clé primaire>
REFPIL REF Pilote <type REF : ROWID>
...)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
51
SQL3 (Manipulation) : Accès
TDA
Exemple :
Quels sont les numéros d’avion qui sont en révision avec
leur contrat et leurs villes de localisation ?
Select AV#, CONTRAT, LOC..VILLE
From AVION
Where REVISION = true ;
Rappel : Notation « .. » utilisée aussi pour obtenir le
champ/fonction d’ un ADT
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
52
SQL3 (Manipulation) : Accès
TDA
Exemples avec ADT Pilote :
Pilotes habitant Nice ?
Select * From Pilote P Where P..ADR..ville =
‘Nice’;
Update pilote Set pilote..adr..ville = ville
(‘Toulouse’)
Where pil# = ‘100’ ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
53
SQL3 (Manipulation) : JOIN

Clause from avec produit cartésien (Jointure
CROSS ) et orthogonalité
EX :
Select *
From (Select * From (((T1 Union T2) INTERSECT T3) EXCEPT T4))

Join NATURAL sur attributs ayant le même nom

Jointure (T1 join T2) avec clauses using foreign key,
using primary key, using constraint et recursive union

Quantificateurs EXISTENTIEL et UNIVERSEL :
for some/all table-res (condition)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
54
Exemples
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
55
SQL3 (Manipulation) : Récursion

Requêtes récursives pour parcours hiérarchique avec possibilités
de limiter la profondeur (et les cycles avec la clause CYCLE) avec
type de recherche comme DEPTH FIRST ou BREADTH FIRST

EX : Sélectionner tous les vols indirects au départ de Nice
Create view RECURSIVE Vol-IND (VD, VA) AS
(Select VD, VA
from Vol
union
Select IN.VD, OUT.VA
From Vol IN, Vol OUT
Where IN.VA = OUT.VD and IN.HA < OUT.HD and
IN.Connections <3 )ou
Search Breadth First by VD set …)
Select * From VOL-IND Where VD = ‘Nice’;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
56
SQL3 (Manipulation) :
appel de fonctions et opérateurs
• Appel de fonctions
EX : Selectionner les pilotes qui habitent à moins de 1
KM de Jean ?
SELECT r.nom
FROM pilote j, pilote r
WHERE j.nom = 'Jean' and distance(j.adr,r.adr) < 1;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
57
SQL3 (Manipulation) :
appel de fonctions et opérateurs
• Appel d'opérateurs :
EX : Sélectionner les noms des avions localisés à Nice avec
leurs photos (zoom de 50%) ?
SELECT a.avnom, Zoom (a.photo, 50)
FROM avion a
WHERE a.loc..ville = « Nice » ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
58
SQL3 (Contrôle) :
TRANSACTIONS





Début et fin explicites pour transactions :
START/COMMIT TRANSACTION avec
ISOLATION LEVEL (Read uncommitted, read committed,
repeatable read, serializable) et 2 modes d’accès (read only, read
write)
SET LOCAL TRANSACTION (pour transactions
réparties)
SET CONSTRAINTS (deferred, immediate)
transactions en mode asynchrone avec async, savepoint,
rollback to savepoint ..
Spécification SQL/XA défini par X/Open
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
59
SQL3 (Contrôle) : Sécurité des
données
- create assertion avec clauses after et before
-
intégrité référentielle (restrict…)
définition de rôles (create role) avec GRANT/REVOKE
possibles
-
13/04/2015 10:21
Privileges : Select, Delete, Update, Insert, References (vues), Usage
(modif. Schéma), Trigger, Under, Execute, ALL
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
60
SQL3 (Manipulation) : Langage
HOTE
Langage hôte intégré « complet » :
- Contrôle de boucle avec WHILE, IF THEN ELSE,
REPEAT, FOR et LOOP (LEAVE), CASE
- déclaration de variables et assignations
- « Routines » définies dans le langage hôte :
« Functions » ou « Procédures » :
SPECIFIC FUNCTION/ PROCEDURE
- Récursion linéaire possible entre procédures
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
61
SQL3 (Manipulation) : Langage
HOTE
Exemple : Envois de mails aux étudiants du Master Miage et du Master
MBDS sur le programme de DB Forum
IF (Select Diplome From Etudiants Where Club= « DB Forum »)
= MBDS
Then insert into send_mail (Master…)
Elseif (Select Diplôme…)
= MIAGE
Then Insert into send_mail (Master..)
CASE
When ‘valeur1’ Then..
When ‘valeur2’ Then
Else <SQL> End Case
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
62
SQL3 (Manipulation) : langage
hôte
Langage d’Exception vis à vis du code SQLSTATE avec

:

- 3 possibilités d’exécution :
CONTINUE, EXIT et UNDO
- la définition de conditions d’exception avec
SIGNAL/RESIGNAL
Possibilité de structuration en MODULES :
« modules » :{procedures}
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
63
SQL3 (Manipulation) : SQL3
PSM
Langage de programmation de procédures : « Procédures
stockées » de SQL3 pour ADT en plus des fonctions et
des procédures : PSM : « Persistent Stored Modules »
•
•
•
•
déclaration de variables et Assignation
conditions CASE, IF
boucles LOOP, FOR
exceptions SIGNAL, RESIGNAL
• possibilité de procédures et fonctions externes et de
structuration en modules
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
64
SQL3 (Manipulation) : SQL3
PSM
Procédures stockées de SQL3 PSM
- Fonctions (monovaluées) : « actor » ou « destructor »
- Procédures qui ne retournent pas de valeurs
- PSM qui peut contenir des fonctions et des procédures
Exemple
Create module actions_pilote
Procedure Affectation (..) Begin.. End
Procedure Vacances (…) Begin…End
Procedure Formation (…) Begin… End
Appel de procédure par CALL
EXEC SQL
CALL actions_pilotes.affectation (‘IT100’, ‘Paris’, ‘Nice’,..)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
65
II Présentation
critique du double
paradigme :
valeur et pointeur (type
REF) dans SQL3 !
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
66
SQL3 et 3ième Manifeste de Date
(96,99)
" It seems clear
that the correct mapping of
encapsulated objects
into the relational data model
is that classes of objects
correspond to
DOMAINS (not to rows) «
DON CHAMBERLIN,
membre SQL3 , IBM, Avril 97
(InfoDB, Vol 10, numéro 6, p.25)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
67
Modèle OR de Date
Modèle V1 de T. Codd Modèle OR de C.Date
Structure
Opérateurs Structures
Opérateurs
de données
de données
RELATIONS
DOMAINES
SQL2
/
RELATIONS
SQL3
CLASSES (D) Signature
RICE dans « domaines »
Hiérarchie de Domaines..
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
Opérateurs spécifiques
dans SELECT ou
WHERE
68
Rappels programmation :
VALEURS vs VARIABLES vs POINTEURS
• VALEUR ? « constante non modifiable (au sens
philosophique) »
• VARIABLE ? Toute variable possède un NOM,
une VALEUR et une ADR mémoire ;
VARIABLE := ( NOM, VALEUR, ADR)
• POINTEUR ? type de variable qui contient l’
ADR d’une autre variable comme valeur
(« indirection »)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
69
Retour sur le modèle Relationnel :
TABLES vs RELVARS
1-Relations/TABLES (« valeurs »)
vs Relations/VARIABLES ( « RELVARS » )
(DATE95, 99)
« RELATION » : double signification :
« variable » et « valeur » ! :
1-"En-tête"(ensemble de paires <noms de colonne, noms
de domaines>)
<--> PREDICAT : le « RELVAR/RELATION »
2-"Corps" (ensemble de tuples de valeurs pas de
pointeurs, conformes à l'en tête)
<-->TABLE DE VALEURS avec une volatilité
importante : la « TABLE/RELATION »
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
70
Retour sur le modèle Relationnel
"Declare N Integer" ?
N est une variable entière dont les valeurs sont des entiers
"CREATE TABLE T " ?
T est une variable relation (RELVAR)
dont les valeurs sont des tables (tuples) (TABLES/RELATIONS)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
71
Retour sur le modèle relationnel
DOMAINES
2- "DOMAINES" !
• "DOMAINE" (CODD70) : TYPE de données
("sémantique") défini par le système ou par
l'utilisateur
==> opérateurs pouvant être associés
ENCAPSULATION de facto
==> TYPAGE « FORT »
(toute valeur possède un domaine/type)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
72
Retour sur le modèle relationnel
« DOMAINES »
NOTEs : 1) Aucune restriction sur les valeurs des
Domaines dans leur définition
(==> domaines de graphes de sons, videos,..) !
Exemple : Domaine des buts (séquences Vidéo)
des meilleurs joueurs de football du monde (avec typologie
forte)
2) Aucune interdiction d’associer un ensemble
d’opérateurs spécifiques
3 ) Aucune interdiction de construire une
hiérarchie de domaines
DOMAINES = Naturellement, Classes d' OBJETS dans la
terminologie OO ! ( Propriétés RICE au niveau des
domaines)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
73
« RELATIONS » (TABLES ou RELVARS)
vs « DOMAINES »
1) Ni les « TABLES » ni les « RELVARS » ne
peuvent remplacer/Emuler le concept de «
DOMAINES »
2) « Domaine » : seul niveau d’abstraction
SEMANTIQUE incontournable entre les «
valeurs » et les « relations » candidat
NATUREL au support de CLASSES d’
OBJETS (propriété RICE)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
74
Double CONSTAT !
•
Les Relations/TABLES ne sont pas des
« Domaines » !
•
Les Relations/RELVARS ne sont pas des
« Domaines » !
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
75
Retour sur le modèle Relationnel
TABLES vs DOMAINES
3- RELATIONS/TABLES et DOMAINES : DISTINCTS
Domaine : valeurs potentielles (" type") vs
Relation /table (Attributs): valeurs réelles
Les RELATIONS/TABLES sont
dépendantes du temps vs
les valeurs de DOMAINE sont "éternelles"
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
76
Retour sur le modèle Relationnel
TABLES vs DOMAINES
domaines statiques vs relations/Tables
dynamiques
Une « relation/table » A un type (le RELVAR)
alors qu'un « domaine » EST un type
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
77
Retour sur le modèle Relationnel
RELVAR vs DOMAINES
Un domaine comprend des valeurs de complexité
arbitraire
alors qu'un RELVAR contient des tuples
EX : Domaine DVille contient la valeur " Paris "
alors que le Relvar RVille contient le tuple
<Dville : PARIS>
Un RELVAR n'encapsule pas des valeurs à la
différence des domaines
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
78
Retour sur le modèle Relationnel
RELVAR vs DOMAINES
4- Les RELVARS ne sont pas candidats naturels à
être des CLASSES d'OBJETS
Exemple de RICE au niveau des RELVARS :
CREATE TABLE PERSONNE
(SS# CHAR(9),
DATE-NAISS DATE,
ADDRESSE CHAR (50));
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
79
Retour sur le modèle Relationnel
(con't)
CREATE TABLE EMP UNDER PERSONNE
( ENOM CHAR(20),
SAL NUMERIC,
LOISIRS SET_OF (LOISIR)
TRAVAILLE_POUR (COMPAGNIE)
METHODES
RETRAITE (..): NUMERIC ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
80
Retour sur le modèle Relationnel
(con't)
CREATE TABLE LOISIR
(NOM...…);
CREATE TABLE COMPAGNIE
(NOM CHAR(20),
LOCATION CHAR (12));
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
81
Besoins d'extensions
du Langage de requête
- expression de chemin
EX.: EMP.TRAVAILLE_POUR.LOCATION
- expression imbriquée
EX.: EMP.LOISIRS.NOM
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
82
Retour sur le modèle Relationnel
(con't)
- Opérateurs de comparaison de relations
EX.: SUBSET,..
- Opérateurs de cheminement dans un graphe
- Appel de méthodes dans les expressions
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
83
Retour sur le modèle Relationnel
(con't)
PROBLEMES ?
- Certaines colonnes ont des vrais TYPES de
DONNEES (CHAR(20) : valeurs potentielles..)
d'autres non, valeurs réelles (COMPAGNIE…)
- Classe RELVAR avec des variables PUBLIC sans
nécessairement des méthodes (l'inverse dans les
classes « pures » OO )
-
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
84
Retour sur le modèle Relationnel
(con't)
Table incorrecte d’un point de vue structure
informatique (cf 3NF) et mauvaise structure
sémantique
- les objets lignes peuvent... contenir d'autres objets
(EX.: les objets EMP contiennent des objets
COMPAGNIE) en fait des POINTEURS vers
ces objets (==> anomalie de mise à jour des
lignes )
- Mise à jour des objets contenus : cascade ?
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
85
Retour sur le modèle Relationnel
(con't)
- PB : une "relation" contient non seulement des
valeurs mais des ...POINTEURS
==> ré-interprétation de l'ALGEBRE pour des objets
imbriqués !
- héritage des méthodes pour les relations dérivées
via les opérateurs de l'algèbre ? (EX: PROJ
(SALAIRE, COMPAGNIE) avec RETRAITE ?)
Où sont les domaines ?
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
86
Type REF (Rappels)
VALEURS vs VARIABLES ?
(cf PB Relation/TABLES vs Relations/RELVAR)
VARIABLE := (NOM, V, ADR)
VALEURS vs POINTEURS ?
(Modèle Relationnel vs Modèle de Données Codasyl/OO)
VALEUR : éternelle !
(EX: "Amour") vs
VARIABLE : modifiable et dépendante du temps
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
87
Type REF
(con't)
Les Variables ont des adresses (pas les valeurs) :
 TYPE « ADRESSE » avec 2 opérateurs de base :
* Référencement/adressage ("referencing") : vadr
en C : ptr = &v; (avec char v; et char *ptr ; alors v=6; ou *ptr=6;)
en PL/1
DECLARE N INTEGER
DECLARE P POINTER
P= ADDR (N)
* Déréférencement ("dereferencing") : adrv
en C : *A ; en PL/1 : A--> V
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
88
Etude du Type REF de SQL3
Trois extensions de SQL2 (pour les tables) :
ROWID, type REF, « --> »
1- Identifiant (système) de tuple (ROW ID)
L'opérateur associé de référencement est "&ROW"
En Oracle 9, cet opérateur s’appelle ref
Exemple :
Select ref (p) from pilote p where pnom = ‘serge’ ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
89
Type REF de SQL3
2- Type "REF" : les valeurs d'une colonne
peuvent être des POINTEURS ("adresses de
tuples" /ROWID) d'une autre relation.
Exemple : Dans la table pilote, un attribut "refavion"
est introduit pour traduire le lien N:1 entre Pilote
et Avion correspondant à "l'avion préféré d'un
pilote"
refavion REF (avion_type)
SCOPE FOR refavion IS avion
refavion contient pour un pilote donné, le ROWID
de l'avion préféré
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
90
Type REF de SQL3
Type "REF" dès Oracle 9 :
- le type REF est introduit dans les TABLES ou
les TYPES (qui peuvent exister avec ou sans OID)
- opérateurs associés : « ref » et « deref »
- OID, ROWID, PKID/PKREF
Note : en dehors du stockage persistant des valeurs de
TABLES, aucune différence entre TYPE et CLASSE !
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
91
Type REF de SQL3
3- Opérateur de déréférencement noté "--> »
Exemple : "refavion --> av#, avnom"
forme abrégée pour :
SELECT av#, avnom
FROM avion A
WHERE A.&ROW = refavion ;
Dans Oracle 9 :
….Where ref (A) = refavion ;
Notation ‘.’ possible dans Oracle 9 pour «  »
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
92
Exemples avec type de données REF de
SQL3
pilote (pl#, plnom, adr, refavion )
avion (av#, avnom, cap, loc)
<refavion de type REF : "refavion" contient des
valeurs d'adresses de tuples (ROWID) d'avions
pour traduire l ’avion préféré d'un pilote>
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
93
Exemples avec type de données REF de
SQL3
Q1 : Quels sont les numéros des avions préférés
par les pilotes habitant 'Nice' ?
SELECT
p.refavion-->av#
<opérateur de déréférencement>
FROM
WHERE
13/04/2015 10:21
pilote p
p.adr = 'Nice' ;
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
94
Exemples avec type de données REF de
SQL3
Q2 (symétrique) : Quels sont les noms des pilotes
préférant des Airbus A300 ?
SELECT
FROM
WHERE
13/04/2015 10:21
p.plnom
pilote p
p.refavion--> avnom='A300';
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
95
Exemple de jointure externe avec "-->"
Q : "pour chaque pilote habitant Nice, donner le
nom du pilote avec le numéro et le nom de l ’avion
préféré (pour ceux qui en ont un) "
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
96
Exemple de jointure externe avec "-->"
SELECT
FROM
WHERE
13/04/2015 10:21
p.Plnom,
p.refavion-->(Av#, Avnom)
Pilote p
p.Adr = 'Nice' ;
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
97
Exemple1
Nous supposons que chaque avion a un pilote attitré
unique (nous rajoutons l'attribut refpilote dans la
relation Avion)
"Quels sont les noms des pilotes qui préfèrent un
avion dont le pilote attitré habite Nice ?"
La requête SQL3 s'écrit alors :
SELECT p.Plnom
FROM Pilote p
WHERE p.refavion -->refpilote
-->Adr = 'Nice' ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
98
Exemple double jointure
• Attributs de type REF à la place (ou en plus) des
clés étrangères : REFpil et REFav dans la relation
VOL
• Quels sont les noms des pilotes qui conduisent un
Airbus ?
SELECT
v.REFpil-->pilnom
FROM
WHERE
13/04/2015 10:21
vol v
v.REFav-->avnom = ‘ Airbus ’;
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
99
Exemple autojointure
• Rajout attribut REFavbis (pour avion de
rechange) dans la relation avion
• Quels sont les noms des pilotes qui conduisent un
avion dont l’avion de rechange est localisé à Nice
?
SELECT v.REFpil  plnom
FROM
vol v
WHERE v.REFav-->REFavbis-->LOC = ‘ Nice ’ ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
100
Exemple en SQL2
En SQL2, en remplaçant refavion et refpilote par des
clés étrangères AV# et PL#et refavbis par RA :
SELECT P.PNOM
FROM Pilote P, Avion A1 A2 , Vol V
WHERE P.PL#= V.PL# AND
V.AV#= A1.AV# AND
A1.RA= A2.AV# AND
A2.Loc= 'Nice' ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
101
Exemple complet
Pilote (PL#, PLNOM, ADR, REFAVION)
<REFAVION : Avion préféré d’un pilote>
Avion (AV#, AVNOM, LOC, REFAVBIS,
REFPILOTE) <REFPILOTE Pilote attitré d’un avion ;
REFAVBIS : Référence avion de rechange>
Vol (Vol#, PL#, AV#, REFPIL, REFAV, VD, VA,
HD, HA)
Quels sont les noms des pilotes préférant un B747
qui conduisent un Airbus dont l’avion de rechange
est l’ avion attitré de Jean?
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
102
Quels sont les noms des pilotes préférant un B747
qui conduisent un Airbus dont l’avion de rechange
est l’ avion attitré de Jean?
SELECT v.REFPIL plnom
FROM vol v
WHERE
(v.REFAV  Avnom = ‘Airbus’, REFAVBIS 
REFPILOTE Plnom = ‘Jean’)
and
(v.REFPILREFAVION Avnom = ‘B747’) ;
Traitez la en partie avec jointure de SQL2
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
103
Exemple (bis)
ou
SELECT
v.REFpil plnom ?, refavion AVNOM = ‘B747’
FROM vol v
WHERE v.REFav  Avnom – ‘Airbus’ and
REFavbis  Refpilote Plnom = ‘Jean’ ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
104
Exemple + complet !
Pilote (PL#, PLNOM, ADR, REFAVION,
REFCOPIL)
<REFAVION : Avion préféré d’un pilote>
<REFCOPIL : Référence Copilote>
Avion (AV#, AVNOM, LOC, REFAVBIS,
REFPILOTE) <REFPILOTE Pilote attitré d’un avion ;
REFAVBIS : Référence avion de rechange>
Vol (Vol#, PL#, AV#, REFPIL, REFAV, VD, VA,
HD, HA)
FORMATION (PL#, REFPILF, AVNOM, DATE)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
105
Question avec le MAX de 


Nom des pilotes qui préfèrent un avion dont
l’avion de rechange est localisé à Nice et dont le
pilote attitré a un copilote habitant Nice qui a
reçu en 2000 une formation pour conduire un
A300 qui est l’avion préféré dont le pilote attitré
est un pilote Parisien ?
Inventer une Requête utilisant 4 Fois de suite la
navigation  ?
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
106
Q4 ?
Select refPilNom
From vol
Where refAvrefPiloterefAvionrefAVbis
AVNom=‘Airbus’;

En FR ?
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
107
Réponse ?
Select P.PLNOM
From Pilote P , Formation F
Where
P.refavion refavbis A.loc = « ‘Nice »,
Refpilote refcopil Adr = « Nice »
P.Pl# = F.PL# And F.date= « 2000 » and
F.Avnom= « A300 », RefavionRefpilote
Adr = ‘Paris »
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
108
Exercice

Porter le schéma SQL3 précédent en schéma
ODMG et répondez à une requête mettant en
jeu 4 «  »

Commentez les principales différences entre les 2
approches
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
109
Exemple 2
(donné par Don Chamberlin d’ IBM du comité
SQL3 dans Info DB correspondant à un
exemple classique)
La relation Employe contient les attributs Salaire et
REFdept (de type REF sur Departement)
La relation Departement contient l'attribut REFmngr
(de type REF sur Employe)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
110
Exemple 2
Q : noms et salaires des employés gagnant plus que
leur managers ?
SELECT
FROM
WHERE
13/04/2015 10:21
E.NOM, E.SALAIRE
EMP E
E.Salaire > E.REFdept -->
REFmngr-->Salaire ;
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
111
Exemple 2
en SQL2 (en remplaçant les types REF par des clés
étrangères) :
SELECT
FROM
WHERE
13/04/2015 10:21
E.Nom, E.Salaire
Employe E, Employe F,
Departement D
E.D# = D.D# AND
D.Manager = F.Emp# AND
E.Salaire>F.Salaire ;
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
112
Discussion sur le type REF
1- Nature d'un résultat de projection sur le type
REF?
SELECT refavion
FROM pilote p
WHERE p.pnom = 'serge' ;
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
113
Discussion sur le type REF
2- l'attribut de type REF contient des "adresses de variables
lignes (POINTEURS) " !
Seul le concept de « variable relation » (RELVAR) existe
dans le modèle relationnel !
3- Distinction entre le type REF et le concept de "clé
étrangère" de SQL2 ?
- CE : Valeurs de CP vs Attribut REF : valeurs de
ROWID
- manipulation différente !
Note : les valeurs de ROWID peuvent ne pas (encore) ou
ne plus exister (« dangling references »)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
114
Type REF et ROWID :
initialisation ?
CREATE TABLE Vol
(Vol# Char(4),
PL# reference Pilote
REFPIL REF Pilote
...)



La valeur du lien « REFPIL" est un RowID inaccessible par l’utilisateur
(accès avec opérateur ref/ deref) et non plus une valeur de clé étrangère
(PL#) manipulable par l’utilisateur
La clé étrangère et le type REF peuvent cohabiter dans une même table
Le mot clé SCOPE FOR permet de limiter la visibilité des références à une
table particulière (Alter table vol add (scope for REFPIL is pilote1));
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
115
Initialisation ROWID ?
Insert into VOL
( IT100, 1, Nice, Paris, 7,8,
(Select &ROW (p) From Pilote p where p.nom =
‘Serge’));
Rappel : &ROW (ref en Oracle) : « Opérateur de référencement »
associé à un ROWID
Note : En Oracle 9, la clause « returning REF into » permet de récupérer le
ROWID d’un tuple
Exemple :
Insert into pilote values (1,Serge, Toulouse, Returning Ref (pilote) into pil-ref)
insert into Vol values (IT100, 1, Nice,Paris, 7,8, pil-ref)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
116
Initialisation ROWID ?
Retour du RAT (« Record at-a-time ») dans le SAT
(« Set at-a-time ») !
« Chasser le RAT dans SQL2 , il revient au galop
avec le type REF dans SQL3 ! »
Un lien multivalué (1:N ou N:M ) se traduit par un type REF permettant
d’avoir des jointures « cablées » au niveau TABLE ou TYPE
La gestion d’initialisation et de manipulation des pointeurs associés est
affaire de développeurs RAT
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
117
Remarques sur le type REF de SQL3 et le
pointeur REF de l'ODMG
Dans ODMG, REF est un pointeur persistant qui enrichit le
concept de pointeur C++ avec son pointeur symétrique
INVERSE;
Dans SQL3
1- REF est un pointeur entre variables lignes
2- REF permet de simplifier des requêtes complexes (en
SQL2 ) mettant en jeu des auto jointures et des jointures
externes exploitant l’opérateur de déréférencement
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
118
Remarques sur le type REF de SQL3 et le
pointeur REF de l'ODMG
Dans SQL3 deux paradigmes cohabitent dans la TABLE :
- « valeur » (algèbre/SQL)
- « pointeur » (Opérateurs REF/ DEREF)
Pourquoi ne pas avoir clairement séparé les deux mondes
(cf Manifeste de Date?)
Note : « Spéciation » en biologie avec une vie faite de paliers…
COHERENCE? COMPLETUDE ? FERMETURE?
SQL3 (comme SQL2) n’est pas un bon standard
(qui évolue…)!
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
119
Remarques sur les TIPS des Bases de
Données
Structures de données
• VALEURS vs
• POINTEURS (variables)
Opérateurs associés
• ALGEBRE vs
• Opérateurs REF et DEREF
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
120
Remarques sur les paradigmes de
développement et SQL
Développement d’application
Nouveauté SQL2
Pointeur
CURSOR
Paradigme RAT
Paradigme SAT
(Record at a time)
(Set at a time)
Pointeur
ligne ROWID
(ref,deref) »
Nouveauté SQL3
Paradigme POINTEUR
Paradigme VALEUR
(C++, CODASYL/DBTG,
ODMG…)
13/04/2015 10:21
(Modèle Relationnel,
SQL2/3)
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
121
Remarques sur les paradigmes de
développement et SQL
Développement d’application
Nouveauté SQL2
Paradigme RAT
Pointeur
CURSOR
(Record at a time)
Paradigme SAT
(Set at a time)
Pointeur
ligne ROWID
(ref,deref) »
Nouveauté SQL3
Paradigme OAT (Object at a time)
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
122
Remarques sur les architectures « n-tier »
Applications réparties sur
Architectures « n-tier »
Couplage
Middleware
Couplage Middleware
LACHE
FORT
Approche « COMPOSANTS »
Sstandards: :CORBA,
CORBA, COM+,
COM+, RMI
Standards
RMI
Approche « SERVICES
WEB »
Sstandards : SOAP, UDDI, WSDL
Environnements
HOMOGENES
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
Environnements
HETEROGENES
123
Select e.QUESTIONS
From Etudiants e
Where e.esprit = ‘positif’
13/04/2015 10:21
Copyright Serge MIRANDA, "Objets et
BD" (Part IV)
124