RDBMS: panorama attuale RDBMS: problemiINSTANCE METHOD guadagno() RETURNS DECIMAL(9,2), STATIC...
Transcript of RDBMS: panorama attuale RDBMS: problemiINSTANCE METHOD guadagno() RETURNS DECIMAL(9,2), STATIC...
0
Basi di Dati Relazionali ad Oggetti
1
RDBMS: panorama attuale
� Gestiscono e manipolano dati semplici (tabellari)
� Hanno un linguaggio di interrogazione (SQL) semplice, dichiarativo e standard
� Tool consolidati per lo sviluppo di applicazioni (Oracle Developer, Sybase Power Builder, Microsoft Visual Basic)
2
RDBMS: panorama attuale
� Portabili su diverse piattaforme� Esempi di RDBMS: IBM DB2, Oracle, Sybase,
Informix, Microsoft SQL Server� Buone prestazioni� Affidabilità, gestione transazioni� Basati su una architettura client-server supportano
efficientemente un gran numero di utenti� Forniscono meccanismi di controllo dell’accesso
3
RDBMS: panorama attuale
� Oggi il mercato mondiale dei RDBMS supera i 50 billioni di dollari all’anno, ma… i RDBMS presentano anche alcuni limiti
4
RDBMS: problemi
� Prevalentemente connessi alle caratteristiche intrinseche del modello relazionale:– SQL-92 fornisce solo un insieme limitato di tipi di
dato– le tabelle hanno una struttura flat e non forniscono
un buon supporto per strutture annidate, quali insiemi ed array
– non è possibile definire relazioni di sotto-tipo tra gli oggetti di un database
5
RDBMS: problemi
� Le associazioni tra entità vengono modellate per valore e questo nel caso di associazioni complesse può richiedere la creazione di parecchie tabelle/colonne “fittizie”
� Gli RDBMS non sfruttano gli approcci object-oriented per la progettazione e realizzazione di software che oggi stanno diventando pressochè uno standard
6
OODBMS: panorama attuale
� Permettono di modellare direttamente oggetti complessi e le loro associazioni
� Object-orientation sempre più diffuso in ambito software engineering e programmazione: unicità del paradigma
� Buone prestazioni per applicazioni navigazionali
� Limitato supporto per concorrenza, parallelismo e distribuzione
7
OODBMS: problemi
� Il progetto del database è strettamente legato al progetto delle applicazioni
� Mancanza di un modello dei dati e di un linguaggio di query standard pienamente accettati
� Mancanza di un linguaggio di query dichiarativo (SQL-like)
� Semplici modelli transazionali� Limitate funzionalità di controllo dell’accesso� Coprono un mercato di nicchia che richiede accessi
navigazionali efficienti (disegno di chip, ecc.)
8
RDBMS vs. OODBMS
� RDBMS forniscono un supporto efficiente ed efficace per applicazioni che manipolano dati semplici
� OODBMS forniscono un supporto efficiente per alcune classi di applicazioni su dati complessi, ma senza molti degli aspetti positivi dei RDBMS
9
Il modello relazionale ad oggetti
� I DBMS relazionali ad oggetti (object-relational) nascono dall’esigenza di assicurare le funzionalità dei RDBMS rispetto alla gestione di dati tradizionali, estendendo il modello dei dati con la possibilità di gestire dati complessi tipica degli OODBMS
10
ORDBMS: caratteristiche generali
� Nuovi tipi di dato:– testi, immagini, audio/video, dati geografici, ecc.– tipi di dato user-defined– tipi collezione
� Metodi per modellare le operazioni sui tipi definiti dall'utente (es. Java, C)
� Nuovi modi per modellare le associazioni� La filosofia per la gestione dei dati è però ancora
quella relazionale:– Tutti gli accessi ai dati avvengono tramite SQL– Tutti le entità di interesse sono modellate tramite tabelle
11
ORDBMS: panorama attuale
� Oggi quasi tutti i principali produttori di RDBMS (Oracle, Informix, DB2,..) hanno esteso i loro DBMS con caratteristiche object-relational
� Tali estensioni presuppongono anche una estensione del linguaggio SQL
� Allo stato attuale ogni RDBMS ha un’estensione proprietaria object-relational
12
ORDBMS: panorama attuale
� Le estensioni differiscono per:– Le funzionalità che supportano– Il modo di realizzarle– Le estensioni apportate ad SQL
� E questo nonostante SQL-99 ...
13
Lo standard SQL-99
� SQL-99 è un tentativo di standardizzazione dell’estensione object-relational del modello relazionale
� Al momento della definizione di SQL-99 i maggiori produttori di RDBMS avevano già la loro versione delle estensioni object-relational
� SQL-99 non standardizza tutte le funzionalità object-relational presenti nei DBMS commerciali
14
Lo standard SQL-99
� E’ quindi ancora presto per capire quando e in che misura lo standard sarà recepito a livello commerciale
� La sensazione è che sarà necessario un ulteriore standard che medi tra tutte le estensioni proprietarie
15
Nel seguito …
� Discutere le caratteristiche generali di un ORDBMS
� discuteremo come queste caratteristiche vengono gestite dallo standard– se non altrimenti specificato, utilizzeremo la sintassi
di SQL-99
� introdurremo le caratteristiche relazionali ad oggetti di Oracle
16
Estensione del sistema di tipi
17
Sistema dei tipi in SQL92
� In SQL-92 i tipi di un attributo in una relazione possono essere:– numerici (interi, reali, ecc.)– carattere (stringhe di lunghezza fissa o variabile,
caratteri singoli)– temporali (date, time, datetime, interval)– booleani (true, false)– non strutturati (BYTE, TEXT, BLOB, CLOB)
18
Sistema dei tipi in SQL92
� Per ogni tipo built-in esistono un insieme fisso e predefinito di operazioni che su di esso possono essere eseguite
� Queste limitazioni rendono spesso difficile la rappresentazione di dati reali
19
Estensione del sistema di tipi
� Tipi semplici � Abstract data types
– User-defined types
� Tipi riferimento� Tipi complessi:
– tipi record e tipi collezione
20
Tipi semplici
� I tipi semplici (o distinct type) sono la forma più semplice di estensione del sistema dei tipi fornita da un ORDBMS
� Consentono agli utenti di creare nuovi tipi di dati, basati su un solo tipo (built-in o user-defined)
21
Tipi semplici
� Confronti con il tipo base o con altri tipi semplici definiti sullo stesso tipo base richiedono operazioni di cast
� l’ORDBMS crea automaticamente una funzione di cast quando un nuovo tipo semplice viene creato
� Non è fornito alcun meccanismo di ereditarietà e subtyping per i tipi semplici
� Sono usati per definire tipi di dati che richiedono operazioni diverse rispetto al tipo su cui sono definiti
� I tipi semplici sono considerati dal DBMS totalmente distinti dal tipo su cui si basano
� I valori del tipo semplice non sono direttamente confrontabili con quelli del tipo su cui si basano (strong typing)
22
Esempio
� Si supponga di creare un nuovo tipo id_impiegatobasato sul tipo intero
� Come il tipo intero, id_impiegato è utilizzato per memorizzare valori numerici ma il DBMS tratterà i due tipi come tipi distinti
� Per i due tipi possono essere definite operazioni diverse (ad esempio la somma di due identificatori non ha senso, mentre potrebbe essere utile una operazione di confronto)
23
Tipi semplici in SQL-99
� SQL-99 consente di definire tipi semplici basati solo su tipi built-in
CREATE TYPE <name> AS <built-in type> FINAL
� Vedremo in seguito il significato della clausola FINAL
24
Esempio
CREATE TYPE id_impiegato AS INTEGER FINAL;
CREATE TABLE Impiegati(id id_impiegato ,nome VARCHAR(50),età INTEGER,id_manager id_impiegato );
25
Casting
� I valori dei distinct type sono considerati come distinti dai valori del tipo di base– il casting non è automatico
� le funzioni di cast (se necessarie) vanno implementate esplicitamente, eventualmente direttamente dal sistema
26
Esempio - assegnazione
SELECT nome FROM ImpiegatiWHERE id_manager = 123; errore
27
Esempio - confronto
CREATE TYPE Euro AS Decimal(8,2) FINAL;CREATE TYPE Dollaro_USA AS Decimal(8,2) FINAL;
CREATE TABLE Vendite_Europee(n_cliente INTEGER,n_ordine INTEGER,totale Euro );
CREATE TABLE Vendite_USA(n_cliente INTEGER,n_ordine INTEGER,totale Dollaro_USA );
28
Esempio: confronto
SELECT n_cliente,n_ordineFROM Vendite_Europee ERP, Vendite_USA USAWHERE ERP.n_ordine = USA.n_ordine
AND ERP.totale > USA.totale; errore!!!
29
Casting in SQL-99
� Il DBMS definisce due funzioni di casting per ogni nuovo tipo semplice:– una per passare dal distinct type al tipo built-
in– una per passare dal tipo built-in al distinct
type
30
Funzioni di casting in SQL-99
CREATE CAST (<source type> AS <target type>)WITH <segnatura funzione>[AS ASSIGNMENT]
� <source type>: tipo input� <target type>: tipo output� almeno uno tra <source type> e <target type> deve
essere un tipo definito dall’utente� l’altro può essere un tipo qualunque
31
� <segnatura funzione> è la segnatura di una qualunque funzione
� la funzione deve essere definita come segue:
FUNCTION <name> (<source type>) RETURNS <target type>… codice ...
Funzioni di casting in SQL-99
32
� Se la clausola AS ASSIGNMENT è specificata, il casting è invocato implicitamente quando necessario
� per ogni coppia di tipi può esistere una sola funzione di casting definita dall’utente
� Le funzioni di casting per i tipi semplici vengano create automaticamento dal sistema con la clausola AS ASSIGNMENT
Funzioni di casting in SQL-99
33
Casting in SQL-99
� La funzione di casting può essere invocata:– esplicitamente
CAST(<source type> as <target type>)– implicitamente, senza invocare la funzione CAST
� la stessa funzione può essere invocata per casting su tipi built-in (esempio: integer in real)
34
Esempio
SELECT nome FROM ImpiegatiWHERE id_manager = CAST(123 AS id_impiegato);
SELECT nome FROM ImpiegatiWHERE id_manager = 123;
35
Esempio
SELECT n_cliente,n_ordineFROM Vendite_Europee ERP, Vendite_USA USAWHERE ERP.n_ordine = USA.n_ordineAND CAST(ERP.totale AS Decimal(8,2) >
CAST(USA.totale AS Decimal(8,2));
36
Esempio - alternativa
� Per passare da Euro a Dollaro_USA posso anche definire una nuova funzione di cast
CREATE FUNCTION f(e Euro) RETURNS Dollaro_USABEGIN
DECLARE g DECIMAL(8,2);SET g = e;RETURN g;
END;
CREATE CAST(Euro AS Dollaro_USA)WITH FUNCTION f(Euro);
37
ADT
� Un abstract data type include:– uno o più attributi– uno o più metodi
� Gli attributi possono essere dichiarati come gli attributi di una tabella
– possono usare clausole default– non è possibile specificare vincolo NOT NULL
� il tipo può essere instanziabile oppure no– vedremo meglio dopo
38
ADT in SQL-99
� Se ci sono solo attributi (completeremo in seguito la definizione):
CREATE TYPE <nome tipo>AS <lista definizione attributi>[{INSTANTIABLE|NOT INSTANTIABLE}]{FINAL|NOT FINAL}
� INSTANTIABLE è il default
39
Esempio
� Si supponga di voler rappresentare l’indirizzo di un impiegato in un RDBMS
� Sono possibili due opzioni:– indirizzo: VARCHAR(n)– rappresentare ogni componente dell’indirizzo come
un attributo separato
40
Esempio
CREATE TYPE t_indirizzo ASnumero_civico INTEGER,via VARCHAR(50),città CHAR(20),stato CHAR(2),cap INTEGERNOT FINAL;
t_indirizzo è un tipo complesso i cui attributi hanno tipi predefiniti
41
ADT
� Gli ADT possono anche essere annidati:
CREATE TYPE t_impiegato ASid id_impiegato,nome CHAR(20),curriculum TEXT,indirizzo t_indirizzoNOT FINAL;
42
ADT
� Gli ADT possono essere usati come:
– tipi di una colonna in una relazione
– tipi di una tabella (row type)
43
ADT come tipo di colonna
� Gli ADT possono essere usati come tipi di una colonna di una relazione
CREATE TABLE Impiegati (imp# id_impiegato,nome CHAR(20),curriculum TEXT,indirizzo t_indirizzo );
44
ADT come tipo di colonna
Tabella Impiegati
nomeimp# curriculum indirizzo
numero_civico via città stato cap
45
Metodi
� Sugli ADT possono essere definiti (segnature di) metodi come parte della definizione del tipo:
CREATE TYPE t_libro AStitolo CHAR(20),prezzo_vendita DECIMAL(9,2),prezzo_acquisto DECIMAL(9,2)NOT FINALMETHOD guadagno() RETURNS
DECIMAL(9,2);
46
Metodi
� I metodi sono funzioni definite dall’utente associate ai tipi
� Possono essere scritti in linguaggi proprietari del DBMS o in linguaggi di programmazione standard (es. Java)
� La sintassi varia notevolmente a seconda del DBMS utilizzato
� definizione simile a quella delle funzioni– differenza: i metodi hanno un parametro implicito che
rappresenta l’oggetto su cui il metodo viene invocato
47
Metodi in SQL-99
� Vengono creati con il comando CREATE METHOD
CREATE METHOD <nome metodo>(lista parametri)RETURNS <output data type>FOR <nome UDT><corpo metodo>
48
Esempio
CREATE METHOD guadagno()RETURNS DECIMAL(9,2)FOR t_libroRETURN (SELF.prezzo_vendita - SELF.prezzo_acquisto);
CREATE FUNCTION guadagno(l t_libro)RETURNS DECIMAL(9,2)RETURN (l.prezzo_vendita - l.prezzo_acquisto);
49
Metodi in SQL-99
� I metodi possono essere:– metodi per le istanze (INSTANCE)
� invocabili a partire da un’istanza del tipo
– metodi di tipo (STATIC)� invocabili sul tipo
� il default è INSTANCE
50
Esempio
CREATE TYPE t_libro AStitolo CHAR(20),prezzo_vendita DECIMAL(9,2),prezzo_acquisto DECIMAL(9,2)NOT FINALINSTANCE METHOD guadagno() RETURNS DECIMAL(9,2),STATIC METHOD max_prezzo_vendita() RETURNS DECIMAL(9,2);
51
Incapsulazione
� Gli ADT possono essere incapsulati� in questo caso, la loro manipolazione può
avvenire solo mediante apposite funzioni automaticamente create dal DBMS al momento della creazione dell’ADT
52
Incapsulazione in SQL-99
� Incapsulazione stretta� Tre tipi di metodi predefiniti
– costruttore : per creare una nuova istanza di ADT– metodi observer : per formulare interrogazioni su
ADT– metodi mutator : per cambiare valori ad istanze di
ADT� TIPO = CLASSE
53
Metodo costruttore
� Ad ogni ADT è automaticamente associato un metodo (costruttore) con lo stesso nome del tipo
� Il costruttore crea un'istanza del tipo� al costruttore possono in genere essere
passati i valori da assegnare alle componenti dell’istanza creata
54
Costruttori in SQL-99
� Per ogni ADT T, esiste un costruttore T( )
t_indirizzo() � t_indirizzo
crea una nuova istanza del tipo t_indirizzo con gli attributi inizializzatiai valori di default (tali valori possono anche essere NULL)
t_indirizzo(39, ‘Comelico', 'Milano', 'IT', 20135)
crea una nuova istanza del tipo t_indirizzo con gli attributi inizializzati in base ai valori forniti
55
Costruttori in SQL-99
� Dato un ADT T con attributi attr1,…,attrn, un’istanza per T viene indicata con T(v_attr1,…,v_attrn), dove v_attr1,…,v_attrnvalori per gli attributi attr1,…,attrn
t_indirizzo(14,’Sauli’,’Milano’,’IT’,20135)
56
Esempio - inserimento 1
INSERT INTO ImpiegatiVALUES(SM123,‘Smith’,NULL,t_indirizzo(14,‘Sauli',
‘Milano’,'IT', 20135));
nomeimp# curriculum indirizzo
numero_civico via città stato cap
SmithSM123 NULL 14 Sauli Milano IT 20135
57
Metodi mutator
� Servono per modificare istanze di un ADT
numero_civico(INTEGER) � t_indirizzovia(VARCHAR(50)) � t_indirizzocittà(CHAR(20)) � t_indirizzostato(CHAR(2)) � t_indirizzocap(INTEGER) � t_indirizzo
� vale anche per SQL-99
58
Esempio
� Vogliamo inserire la tupla nella tabella impiegati:
� In due passi:– creo la tupla inizializzando il campo indirizzo– aggiorno i valori del campo indirizzo
nomeimp# curriculum indirizzo
numero_civico via città stato cap
SmithSM123 NULL 14 Sauli Milano IT 20135
59
Esempio - inserimento 2
INSERT INTO ImpiegatiVALUES(SM123,‘Smith’,NULL,t_indirizzo());
UPDATE ImpiegatiSET indirizzo = indirizzo.numero_civico(14)WHERE imp# = ‘SM123’;
UPDATE Impiegatiindirizzo = indirizzo.via(‘Sauli’)WHERE imp# = ‘SM123’;….
60
Esempio - inserimento 3
BEGINDECLARE i t_indirizzo;SET i = t_indirizzo();SET i = i.numero_civico(14);SET i = i.via(‘Sauli’);SET i = i.città(‘Milano’);SET i = i.stato(‘IT’);SET i = i.cap(20135);INSERT INTO impiegati VALUES (‘SM123’,’Smith’,NULL,i);
END;
61
Metodi observer
� Per ogni componente di un ADT è automaticamente creato dal sistema un metodo observer con lo stesso nome della componente:
numero_civico( ) ----> INTEGERvia( ) ----> VARCHAR(50)città( ) ----> CHAR(20)stato( ) ----> CHAR(2)cap( ) ----> INTEGER
� Anche in SQL-99
62
Esempi di selezione con metodo observer
SELECT nomeFROM ImpiegatiWHERE indirizzo.città( ) = ‘Milano’
OR indirizzo.città( ) = ‘Roma’;
SELECT indirizzo.città()FROM impiegatiWHERE nome = ‘Smith’;
63
Selezione
� La selezione di una colonna ADT restituisce un’istanza di quel tipo
SELECT indirizzoFROM ImpiegatiWHERE imp# = ‘SM123’
si ottiene
t_indirizzo(14,’Sauli’,’Milano’,’IT’,20135)
64
Cancellazione
DELETE FROM ImpiegatiWHERE indirizzo =
t_indirizzo(14,’Sauli’,’Milano’,’IT’,20135);
65
Update
UPDATE ImpiegatiSET indirizzo = indirizzo.n_civico(18)WHERE imp# = ‘SM123’ ;
UPDATE ImpiegatiWHERE indirizzo = t_indirizzo(18,’XX Settembre’,’Genova’,’IT’,16100);
66
Uso di metodi nelle query
CREATE TYPE t_libro AStitolo CHAR(20),prezzo_vendita DECIMAL(9,2),prezzo_acquisto DECIMAL(9,2)NOT FINALMETHOD guadagno() RETURNS DECIMAL(9,2);
CREATE TABLE biblioteca(codL# INTEGER,libro t_libro);
SELECT b.libro.guadagno( )FROM biblioteca bWHERE b.libro.titolo() = ‘La Divina Commedia’;
67
Vincoli di integrità
� Non è possibile definire vincoli di PRIMARY KEY, UNIQUE, FOREIGN KEY su un campo ADT
� Motivazione– concettualmente tutto è OK– problemi legati all’efficienza
68
Operazioni
� Casting definito dall’utente tra ADT e altro tipo� possibilità di definire funzioni di ordinamento e
di confronto– non le vediamo
69
Cancellazione e modifica tipi
DROP TYPE <nome_tipo> {CASCADE|RESTRICT};
ALTER TYPE <nome_tipo> <operazione_di_modifica>;
<operazione_di_modifica>::= ADD ATTRIBUTE <definizione_attributo>|DROP ATTRIBUTE <nome_attributo>
70
Row type
� Un ADT può anche essere usato come tipo di una intera tabella (row type)
� Le righe della tabella sono istanze del tipo mentre le colonne coincidono con gli attributi del tipo
71
Row type
� Permettono di:– definire un insieme di tabelle che condividono la stessa
struttura (typed tables)– modellare in modo intuitivo le associazioni tra dati in tabelle
diverse (referenceable tables)– definire gerarchie di tabelle
� TUPLA DI UNA TYPED TABLE = OGGETTO� ogni tupla è associata ad un identificatore, che
rappresenta un campo aggiuntivo per ogni tabella ed è unico nel sistema
� per default, gli identificatori sono generati dal sistema– esistono altre modalità, non le vediamo
72
Typed tables in SQL-99
CREATE TABLE <nome_tabella>OF <nome_tipo_complesso>[(REF IS <nome_campo_TID>)]
� la clausola REF IS indica il nome di un attributo (distinto dai precedenti) nel quale verranno inseriti gli identificatori di tupla (TID - tuple identifier)
� il campo identificatore è sempre il primo campo nello schema della tabella
� se la clausola manca, il campo contenente gli identificatori esiste, è generato dal sistema ma è trasparente all’utente (non selezionabile)
73
Esempio
� Si supponga di voler memorizzare informazioni sui progetto a cui gli impiegati lavorano
CREATE TYPE t_progetto ASprj# INTEGER,nome VARCHAR(20),descrizione VARCHAR(50),budget INTEGERNOT FINAL;
74
Esempio
CREATE TABLE Progetti OF t_progetto (REF IS my_TID);
Progetti
prj#
16454 12 Oracle ORDBMS
nome descrizione budget
10,000,000
my_TID
75
Typed tables in SQL-99
� Quando si crea una typed table è possibile aggiungere vincoli di integrità sugli attributi dell’ADT su cui si basa (purché il tipo corrispondente lo permetta)
CREATE TABLE <nome_tabella>OF <nome_tipo_complesso>[(REF IS <nome_campo_TID>,<vincoli>) ]
76
Esempio
CREATE TYPE t_progetto ASprj# INTEGER,nome VARCHAR(20),descrizione VARCHAR(50),budget INTEGERNOT FINAL;
CREATE TABLE progetti OF t_progetto OK(PRIMARY KEY (prj#));
CREATE TABLE progetti OF t_progetto NO(prj# INTEGER PRIMARY KEY);
77
Row type
� Nessun meccanismo di incapsulazione� L’incapsulazione c’e’ solo quando un ADT è
usato come tipo di una colonna� Gli attributi del row type sono visti come
colonne della tabella (inclusa la colonna TID, che può essere selezionata)
� Le interrogazioni sono eseguite nel modo standard
78
Selezione
SELECT prj#FROM ProgettiWHERE budget > 1,000,000;
SELECT my_TIDFROM ProgettiWHERE budget > 1,000,000;
79
Inserimento
INSERT INTO Progetti(Prj#,Nome,Descrizione,Budget)VALUES(14,’sviluppo DB’,’sviluppo DB in
Oracle’,’20,000,000);
nessun valore viene specificato per il campo identificatore
80
Tipi riferimento
� I row type possono essere combinati con i tipi riferimento (REF type)
� Permettono di rappresentare facilmente le associazioni tra istanze di tipi
� Tali tipi permettono ad una colonna di riferire una tupla in un'altra relazione
� Una tupla in una relazione viene identificata tramite il suo TID
81
Esempio
� Si supponga di voler memorizzare informazioni sugli impiegati ed i progetti a cui lavorano
� In un RDBMS avrei due tabelle Impiegati e Progetti
� Nella tabella Impiegati è presente una colonna che indica il progetto a cui l’impiegato lavora (chiave esterna)
82
Esempio
Impiegati
prj#
Progetti
prj#
SM123 12 12 Oracle ….
imp#
...
nome
83
Tipi riferimento
� In un ORDBMS ho due opzioni in più:– definire un ADT t_progetto e usare questo come
tipo di una colonna della relazione Impiegati (ridondanza dei dati perché lo stesso progetto può essere memorizzato molte volte in Impiegati)
– definire una tabella basata su un nuovo tipo complesso e riferire le colonne istanza di questo nuovo tipo� tipo riferimento
84
Tipi riferimento in SQL-99
REF (<tipo_ADT>)[SCOPE <nome_tabella> [<reference_scope_check>]]
� la clausola SCOPE specifica una typed table su <tipo_ADT> e indica che i valori ammessi per il tipo riferimento sono i puntatori alle tuple della type tableindicata
� se la clausola di scope non è specificata, lo scope implicito è rappresentato da tutti i puntatori a tuple con row type <tipo_ADT>
85
Tipi riferimento in SQL-99
� La clausola di SCOPE rappresenta una sorta di vincolo di chiave esterna nel modello relazionale
� problema integrità referenziale anche in questo contesto
� <reference_scope_check> è una clausola che indica come è possibile mantenere l’integrità, analogamente a quanto visto per le chiavi esterne
86
Tipi riferimento in SQL-99
<reference_scope_check> = REFERENCES ARE [NOT] CHECKED[ON DELETE
{CASCADE | SET NULL | SET DEFAULT | RESTRICT | NO ACTION}]
� significato analogo al contesto relazionale� poichè il TID è considerato immutabile, nessuna
clausola ON UPDATE� default: RESTRICT
87
Esempio
CREATE TABLE Impiegati(imp# id_impiegato,nome VARCHAR(50),indirizzo t_indirizzo,assegnamento REF(t_progetto) SCOPE Progetti
REFERENCES ARE CHECKEDON DELETE CASCADE );
Si associa un impiegato ad un progettoUno stesso progetto può essere associato a più impiegatiSe si cancella un progetto, si cancellano anche tutti gli impiegati assegnati a quel progetto
88
Esempio
CREATE TABLE Impiegati(imp# id_impiegato,nome VARCHAR(50),indirizzo t_indirizzo,assegnamento REF(t_progetto) SCOPE Progetti
REFERENCES ARE CHECKEDON DELETE RESTRICT);
Un progetto può essere cancellato sono se non ci sono impiegati assegnati a quel progetto
89
Esempio
CREATE TYPE t_impiegato ASimp# id_impiegato,nome CHAR(20),curriculum TEXT,indirizzo t_indirizzo,dipartimento REF(t_dipartimento) NOT FINAL;
CREATE TABLE Impiegati OF t_impiegato (REF IS my_tid);
90
Esempio
CREATE TYPE t_dipartimento ASdip# INTEGER,nome VARCHAR(30),manager REF (t_impiegato) NOT FINAL ;
CREATE TABLE Dipartimenti OF t_dipartimento (REF IS my_tid);
91
EsempioImpiegati
imp# nome ... dipartimento
Dipartimenti
dip# nome manager
92
Esempio
� La colonna dipartimento di Impiegati punta ad una tupla della tabella Dipartimenti (quelle corrispondente al dipartimento in cui lavora l’impiegato)
� La colonna impiegati di Dipartimenti punta ad una tupla della tabella Impiegati (quella che corrisponde al manager del dipartimento)
93
Tipi riferimento in SQL-99
� Possibilità di estendere la definizione di una typed table con ulteriori attributo con referencetype
94
Esempio
CREATE TABLE Progetti OF t_progetto(prog_ref REF(t_progetto));
Progetti
prj#
12 Oracle ORDBMS
nome descrizione budget
10,000,000
Prog_ref
95
Tipi riferimento in SQL-99 -manipolazione
� Valori di tipi riferimento possono essere confrontati solo utilizzando = e <>
� Casting può essere definito tra reference typee ADT target o tipo built-in
96
Tipi riferimento in SQL-99 -manipolazione
� Funzione di deferenziazione DEREF:– riceve in input un’espressione che restituisce un valore
(puntatore) per un tipo riferimento con scope non vuoto– restituisce il valore puntato dallo stesso (quindi la tupla
puntata)
� Funzione di riferimento �– riceve in input un’espressione che restituisce un valore di tipo
riferimento e un attributo dell’ADT a cui punta il tipo riferimento – restituisce il valore per quell’attributo per la tupla puntata
97
Esempio
SELECT managerFROM DipartimentiWHERE nome = “Dischi”;
Restituisce un puntatore ad un impiegato (cioè l’oiddell’impiegato che è manager del dipartimento Dischi)
98
Esempio
SELECT deref(manager)FROM DipartimentiWHERE nome = “Dischi”;
Restituisce informazioni sul manager del dipartimento Dischi (un’intera riga della tabella Impiegati)
99
Esempio
SELECT deref(manager).nomeFROM DipartimentiWHERE nome = “Dischi”;
Restituisce il nome del manager del dipartimento Dischi
100
Esempio
SELECT manager -> nomeFROM DipartimentiWHERE nome = “Dischi”;
Restituisce il nome del manager del dipartimento DischiEquivalente all’interrogazione precedente
101
Integrità referenziale
� Gli identificatori vengono assegnati dal sistema� l’utente non li conosce a priori� Problema:
– Come garantire l’integrità referenziale di una tabella che contiene un tipo riferimento?
� Soluzione:– si utilizzano sottoquery per determinare gli
identificatori da assegnare alle nuove tuple
102
Esempio
CREATE TABLE Impiegati(imp# id_impiegato,nome VARCHAR(50),indirizzo t_indirizzo,assegnamento REF(t_progetto) SCOPE Progetti
REFERENCES ARE CHECKEDON DELETE RESTRICT);
CREATE TABLE Progetti OF t_progetto (REF IS My_TID,prog_ref REF(t_progetto));
103
Integrità referenziale
� Quando inseriamo una tupla nella tabella impiegati, al campo assegnamento dobbiamo assegnare l’identificatore di una tupla della tabella Progetti
� Due passi:– inseriamo la tupla assegnando NULL al campo con
tipo riferimento– modifichiamo il contenuto del campo con un
UPDATE
104
Esempio
INSERT INTO ImpiegatiVALUES (2,’Mario Rossi’, t_indirizzo( ),NULL);
UPDATE ImpiegatiSET assegnamento =
(SELECT my_tidFROM ProgettiWHERE nome = ‘Oracle’)
WHERE imp# = 2;
105
Tipi riferimento in SQL-99 -restrizioni
� PRIMARY KEY, UNIQUE, FOREIGN KEY non possono essere definiti
106
Tipi collezione e tipi tupla
107
Tipi collezione
� I tipi collezione definiscono dei contenitori per oggetti con struttura simile
� Non esiste ancora una standardizzazione sull’insieme di tipi collezione supportati dai vari ORDBMS– set– bag– liste– array
108
Tipi collezione in SQL-99
� Il solo tipo collezione incluso in SQL-99 è ARRAY– <nome campo> <tipo> ARRAY[<dimensione>]
� <dimensione> è un valore intero� Costruttore:
– ARRAY[<valore_1>,…,<valore_n>]
� accesso:– <nome_campo>[i] dove i è un valore intero tra 1 e n
109
Tipi collezione in SQL-99
� Il numero di elementi in un array è un qualunque numero tra 0 (ARRAY[ ]) e il numero massimo di elementi per l’arraydichiarato
� implicitamente, esiste un parametro “lunghezza”, gestito direttamente dal sistema
110
Esempio
CREATE TABLE Impiegati(imp# id_impiegato,nome VARCHAR(50),competenze VARCHAR(20) ARRAY[3]);
111
Esempio
INSERT INTO ImpiegatiVALUES (2,’Mario Rossi’,ARRAY[‘Oracle’,’Unix’,’Java’]);
SELECT *FROM ImpiegatiWHERE competenze[2] = ‘Unix’;
112
Esempio
CREATE TYPE t_impiegato ASimp#id_impiegato,nome VARCHAR(30),indirizzo t_indirizzo,manager REF(t_impiegato),progetti REF(t_progetti) ARRAY[10],figli REF(t_persona) ARRAY[10],hobby VARCHAR(20) ARRAY[5]NOT FINAL;
CREATE TABLE Impiegati OF t_impiegato;
113
Esempio
UPDATE ImpiegatiSET competenze = ARRAY[‘Oracle’,’Unix’];
UPDATE ImpiegatiSET competenze = ARRAY[‘SQL Server’];
il nuovo array contiene un solo elemento (la lunghezza viene cambiata)
114
Tipi collezione in SQL-99 -manipolazione
� Casting– cast sul tipo degli elementi e eventuale riduzione
numero elementi
� assegnamento:– usuale– troncamento genera errore
� confronto:– =, <>
115
Tipi collezione in SQL-99 -manipolazione
� funzioni– concatenazione
� CONCATENATE (<array_expression> WITH <array_expression>
– cardinalità� CARDINALITY(<array_expression>)
116
Tipi collezione in SQL-99 -restrizioni
� Per i campi di tipo array non possono essere definiti vincoli UNIQUE, PRIMARY KEY, FOREIGN KEY
117
Tipi tupla in SQL-99
� SQL-99 mette a disposizione un nuovo dominio per la rappresentazione di tipi record
� chiamati row type� non richiedono la definizione di un ADT ma
possono essere direttamente associati al tipo
118
Tipi tupla in SQL-99
� TipoROW (<def campo_1>,…,<def campo_n>)
� esempio:ROW(numero_civico INTEGER,
via VARCHAR(50),città CHAR(20),stato CHAR(2),cap INTEGER)
119
Esempio
CREATE TABLE Impiegati (imp# id_impiegato,nome CHAR(20),curriculum TEXT,indirizzo ROW( numero_civico INTEGER,
via VARCHAR(50),città CHAR(20),stato CHAR(2),
cap INTEGER) );
120
Tipi tupla in SQL-99
� Valori:– ROW(<valore tipo_1,…,valore tipo_n>)
� Esempio:– ROW(3,’XX Settembre’,’Genova’,’IT’,16100)
� anche le tuple restituite da una query sono viste come valori del tipo tupla
� le componenti di una tupla possono essere accedure utilizzando la dot notation
121
Esempio
INSERT INTO ImpiegatiVALUES (3,’Rossi’,NULL,
ROW(3,’XX Settembre’,’Genova’,’IT’,16100))
122
Esempio
CREATE TABLE Indirizzi ( Iid INTEGER,
Via VARCHAR(20),Città VARCHAR(20),Stato VARCHAR(20),cap INTEGER);
UPDATE ImpiegatiSET Indirizzo = (SELECT t from Indirizzi t WHERE Iid = 3)WHERE nome = ‘Rossi’;
123
Esempio
SELECT NomeFROM ImpiegatiWHERE Indirizzo.città = ‘Genova’;
124
Tipi tupla in SQL-99 -manipolazione
� Assegnamento:– stesso numero di campi– tipi compatibili
� confronto:– =, <>, <, <=, >, >=– ordinamento lessicografico, basato sui tipi delle
componenti– i valori devono avere lo stesso numero di elementi– la presenza di NULL può ovviamente generare
UNKNOWN
125
Esempio
ROW(1,1,1) = ROW(1,1,1) TRUEROW(1,1,1) = ROW(1,2,1) FALSEROW(1,NULL,1) = ROW(2,2,1) FALSEROW(1,NULL,1) = ROW(1,2,1) UNKNOWNROW(1,1,1) <> ROW(1,2,1) TRUEROW(2,NULL,2) <> ROW(2,2,1) TRUEROW(2,2,1) <> ROW(2,2,1) FALSEROW(1,NULL,1) <> ROW(1,2,1) UNKNOWNROW(1,1,1) < ROW(1,2,0) TRUEROW(1,NULL,1) < ROW(2,NULL,0) TRUEROW(1,1,1) < ROW(1,1,1) FALSEROW(3,NULL,1) < ROW(2,NULL,0) FALSEROW(1,NULL,1) < ROW(1,2,0) UNKNOWNROW(NULL,1,1) < ROW(2,1,0) UNKNOWN
126
Tipi tupla in SQL-99
� Non possono essere associati a vincoli di PRIMARY KEY, UNIQUE, FOREIGN KEY
127
Ereditarietà
128
Ereditarietà
� Possibilità di definire relazioni di supertipo/sottotipo
� L’ereditarietà consente di specializzare i tipi esistenti a seconda delle esigenze dell’applicazione
129
Ereditarietà
� Un sottotipo eredita gli attributi, i metodi, ed i vincoli definiti per i suoi supertipi
� Il sottotipo può raffinare il supertipo con nuovi attributi e metodi
� Nel sottotipo è anche possibile ridefinire metodi ereditati
130
Ereditarietà
� Si possono distinguere due tipi di ereditarietà– Ereditarietà di tipi– Ereditarietà di tabelle
131
Ereditarietà di tipi
Si considerino le seguenti entità:
Camion:
modello CHAR(20),
n_licenza INTEGER,
ultima_revisione DATE,
peso INTEGER,
prox_revisione() DATE
Bus:
modello CHAR(20),
n_licenza INTEGER,
ultima_revisione DATE,
n_posti INTEGER,
prox_revisione() DATE
132
Ereditarietà di tipi
� Nel modello relazionale sono necessarie due tabelle e due procedure
� In un ORDBMS, camion e bus possono essere considerati specializzazioni di un tipo comune: Veicolo
� Si definisce quindi un tipo veicolo contenente le caratteristiche comuni di camion e bus
� Camion e bus sono definiti come sottotipi di Veicolo, con delle caratteristiche aggiuntive
133
Ereditarietà di tipi in SQL-99
� Per ADT� ereditarietà singola
CREATE TYPE <nome_tipo>UNDER <nome_supertipo>AS … altri attributi[NOT] FINAL;
� il supertipo deve essere stato dichiarato con la clausola NOT FINAL
134
Ereditarietà di tipi in SQL-99
� Clausola FINAL: – non si possono definire sottotipi
� Clausola NOT FINAL:– si possono definire sottotipi
� la clausola NOT FINAL è necessaria se la dichiarazione non specifica una superclasse
� in caso contrario si può scegliere
135
Esempio
CREATE TYPE t_veicolo ASmodello CHAR(20),n_licenza INTEGER,ultima_revisione DATE,
METHOD prox_revisione( ) RETURNS DATE
NOT FINAL;
136
Esempio
CREATE TYPE t_camion UNDER t_veicoloASpeso INTEGERNOT FINAL;
CREATE TYPE t_bus UNDER t_veicoloASn_posti INTEGERNOT FINAL;
137
Metodi & ereditarietà
CREATE TYPE t_persona ASnome CHAR(20),id INTEGER,data_di_nascita DATE,indirizzo t_indirizzo,METHOD età() RETURNS INTEGERNOT FINAL;
CREATE TYPE t_insegnante UNDER t_persona ASstipendio DECIMAL(9,2),data_assunzione DATE,corso t_corsoNOT FINAL;
138
Metodi & ereditarietà
� I metodi sono ereditati dai sottotipi allo stesso modo degli attributi:
CREATE TABLE Insegnanti OF t_insegnante;
SELECT nome, I.età( )FROM Insegnanti IWHERE stipendio > 3000;
139
Metodi & ereditarietà
� E’ possibile ridefinire un metodo ereditato� non è possibile ridefinire gli attributi� Ad esempio al tipo t_insegnante può essere associato
un metodo età che restituisce l’anzianità di servizio (overriding)CREATE TYPE t_insegnante
UNDER t_persona ASstipendio DECIMAL(9,2),data_assunzione DATE,corso t_corsoOVERRIDING METHOD età() RETURNS INTEGERNOT FINAL;
140
Tipi non instanziabili
� La dichiarazioni di un tipo specifica se il tipo può essere instanziato (quindi ha istanze proprio) oppure noCREATE TYPE <nome tipo>AS <lista definizione attributi>[{INSTANTIABLE|NOT INSTANTIABLE}]{FINAL|NOT FINAL}
� Il default è INSTANTIABLE� un tipo non instanziabile corrisponde ad una classe
astratta: serve solo per riuso di codice
141
Sostituibilità
� Negli OODBMS vale il principio della sostituibilità
� Un’istanza di un tipo può essere utilizzata ovunque ci si aspetti un’istanza del suo supertipo
� Questo principio non vale negli attuali ORDBMS
� per garantire sostituibilità:– funzione di CAST
142
Ereditarietà di tabelle
� Le typed tables possono essere organizzate in gerarchie di ereditarietà
� questo è possibile solo se i tipi su cui si basato sono in relazione d’ereditarietà
� permette di estendere operazioni SQL alle istanze di una tabella e di tutte le sue sottotabelle
143
Esempio
CREATE TABLE persone OF t_persona;CREATE TABLE insegnanti of t_insegnante UNDER
persone;
� E’ stata creata una gerarchia tra le tabelle persone e insegnanti
144
Interrogazioni
� La gerarchia d’ereditarietà definita sulle tabelle influenza i risultati delle interrogazioni
� Una interrogazione fatta su una tabella si propaga automaticamente alle sottotabelle
� Lo stesso vale per le operazioni di cancellazione e modifica mentre una operazione di inserimento coinvolge solo una specifica tabella
� se si vuole restringere l’operazione alle istanze di una certa tabella: ONLY
145
Esempio
Persone
nome id data_di_nascitaindirizzo
Smith 74 16/8/68
John 86 3/2/48
nome id data_di_nascitaindirizzo
Allen 82 9/7/67
Mark 81 3/5/58
Insegnanti
stipendio ….
30ml
60ml
146
Esempio
SELECT nomeFROM PersoneWHERE data_di_nascita > 1/1/1967;
� Il risultato sarà: Smith e Allen
SELECT nomeFROM ONLY PersoneWHERE data_di_nascita > 1/1/1967;
� Il risultato sarà: Smith
147
Esempio
DELETE FROM PersoneWHERE id > 80;
� Cancellerà John dalla tabella Persone e Allen e Markdalla tabella Insegnanti
148
Relazioni tra OODBMS e ORDBMS
� Valori complessi– array– row
� Oggetti: – tuple di typed tables– identificatore– incapsulazione
� Classi– ADT (è presente il costruttore)– metodi (overloading e overriding)– collezioni: typed table– aggregazioni: tipi REF
149
Relazioni tra OODBMS e ORDBMS
� Ereditarietà– singola– riuso di codice– no sostituibilità– su tipi e typed tables
� linguaggio– SQL con estensioni per la manipolazione dei nuovi tipi di dato– accesso navigazione e associativo– un’interrogazione restituisce sempre un insieme di tuple
150
Progettazione di ORDBMS
151
Progettazione di ORDBMS
� Non esiste ancora una metodologia di progettazione consolidata come per gli ORDBMS né tool a supporto dell’attività di progettazione
152
Approccio partendo da schemi ER
� Progettazione concettuale:– schema ER
� Ristrutturazione:– ridotta– non si eliminano attributi multivalore/compositi e
gerarchie di generalizzazione– gli identificatori esterni/misti devono essere eliminati
153
Approccio partendo da schemi ER
� Progettazione logica:– attributo composito:
� tipo tupla oppure� ADT
– Ogni attributo multivalore viene tradotto in un tipo collezione (ARRAY per SQL-99)
– Ogni entità:� se non ha metodi può essere tradotta in una tabella, o
tramite l’opzione successiva� se ha metodi viene tradotta in un tipo su cui basare una
tabella
– Le gerarchie di generalizzazione vengono tradotte mediante relazioni di sottotipo
154
Approccio partendo da schemi ER
� Associazioni– 1-1/1-n = tipo riferimento – n-n: nuove tabelle
155
Approccio partendo da uno schema OO
� Ogni tipo composito (struct)– tipo tupla o tipo complesso
� ogni tipo multivalore (set, bag, list)– tipo collezione
156
Approccio partendo da uno schema OO
� ogni classe– se non ha metodi:
� si crea direttamente la tabella� i tipi degli attributi vengono modificati in base a quanto sopra
– se ha metodi:� si crea un opportuno ADT� si crea tabella basata su quell’ADT
– per ogni attributo aggregato:� aggregazione semplice: si specifica un tipo riferimento con scope
uguale alla tabella corrispondente alla classe riferita� aggregazione complessa: si specifica un tipo collezione definito
su un tipo riferimento
157
Aspetti relazionali ad oggetti di Oracle 9i
158
Il sistema di tipi di Oracle
� Non distinct type� Tipi oggetto� tipi riferimento� tipi collezione� ereditarietà
159
Tipi oggetto
� Possibilità di definire tipi oggetto (ADT):
specifica Dichiarazione di attributiSpecifica dei metodi
corpo Body dei metodi
160
Esempio
CREATE TYPE Complesso AS OBJECT (parte_r FLOAT,parte_i FLOAT,MEMBER FUNCTION somma(x Complesso) RETURNS
Complesso,MEMBER FUNCTION sottrazione(x Complesso)
RETURNS Complesso,MEMBER FUNCTION moltiplicazione(x Complesso)
RETURNS Complesso,MEMBER FUNCTION divisione(x Complesso)
RETURNS Complesso);
161
Esempio
CREATE TYPE BODY Complesso ASMEMBER FUNCTION somma(x Complesso)
RETURN Complesso ISBEGINRETURNS Complesso(parte_r + x.parte_r,parte_i + x.parte_i);
END somma;MEMBER FUNCTION sottrazione(x Complesso)
RETURN Complesso ISBEGINRETURNS Complesso(parte_r - x.parte_r,parte_i - x.parte_i);END sottrazione;….END;
162
Tipi oggetto
� Vale tutto quello che abbiamo detto per SQL-99 con le seguenti differenze:– concetto di body– incapsulazione non stretta: accesso diretto agli
attributi tramite dot notation
163
Metodi
� Possono essere sia procedure che funzioni� due tipi:
– MEMBER� definiti sulle istanze� parametro implicito: SELF
– STATIC� definiti sul tipo
164
Esempio
CREATE TYPE Rational AS OBJECT (num INTEGER,den INTEGER,MEMBER PROCEDURE normalize,...);
CREATE TYPE BODY Rational ASMEMBER PROCEDURE normalize ISg INTEGER;BEGINg := gcd(SELF.num, SELF.den);g := gcd(num, den); -- equivalent to
previous linenum := num / g;den := den / g;END normalize;...END;
165
Metodi speciali
� Costruttori– come in SQL-99
� Metodi MAP� Metodi ORDER
166
Metodi MAP
� Permettono di confrontare istanze di ADT mappando le istanze a valori di tipi built-in(DATE, NUMBER, VARCHAR)
� rappresentano quindi un casting tra un ADT e uno dei tipi precedenti
� se esiste un metodo MAP per un ADT, i confronti su oggetti di quel tipo vengono effettuati convertendo prima le istanze nei valori del tipo built-in considerato
167
Esempio
CREATE TYPE Rectangle_typ AS OBJECT (len NUMBER,wid NUMBER,MAP MEMBER FUNCTION area RETURN NUMBER,...);CREATE TYPE BODY Rectangle_typ ASMAP MEMBER FUNCTION area RETURN NUMBER ISBEGINRETURN len * wid;END area;...END;
168
Esempio
� Se o1 e o2 sono istanze del tipo rectangle_typ:– o1 < o2 è equivalente a o1.area() < o2.area()– la relazione viene stabilita su due istanze del tipo
NUMBER
169
Metodi ORDER
� Implementano una relazione d’ordine tra le istanze di un certo tipo
� hanno sempre un parametro di tipo uguale a quello per cui il metodo viene definito
� utili per confrontare tipi di dato molto complessi che non potrebbero facilmente essere confrontati con un metodo MAP
� se esiste un metodo ORDER, il metodo viene automaticamente chiamato quando si confrontano istanze del tipo considerato
170
Metodi ORDER
� L’output è sempre un intero che vale:– - 1 : SELF < parametro– 0 : SELF = parametro– +1 : SELF > parametro
� per un ADT, può esistere al più un metodo MAP o ORDER
� se nessuno dei due viene definito, il sistema supporta solo = e <>
171
Esempio
CREATE TYPE Customer_typ AS OBJECT (id NUMBER,name VARCHAR2(20),addr VARCHAR2(30),ORDER MEMBER FUNCTION match (c Customer_typ) RETURN
INTEGER);
172
Esempio
CREATE TYPE BODY Customer_typ ASORDER MEMBER FUNCTION match (c Customer_typ) RETURN
INTEGER ISBEGINIF id < c.id THENRETURN -1; -- any negative number will doELSIF id > c.id THENRETURN 1; -- any positive number will doELSERETURN 0;END IF;END;END;
173
Tabelle tipate
� Anche in Oracle un tipo ADT può essere utilizzato secondo due modalità:– come tipo per un attributo di una tabella– come tipo di base per la definizione di una typed
table
� non può essere specificata una colonna per gli identificatori (no clausola REF IS)
174
Tipi oggetto: accesso
� Accesso tramite dot notation ad attributi e metodi
� se si usa la dot notation, è sempre necessario utilizzare un alias per la tabella acceduta
175
Esempio
CREATE TYPE person AS OBJECT (ssnoVARCHAR(20));
CREATE TABLE ptab1 OF person;CREATE TABLE ptab2 (c1 person);
SELECT ssno FROM ptab1 ; OKSELECT c1.ssno FROM ptab2 ; ErroreSELECT ptab2.c1.ssno FROM ptab2 ; ErroreSELECT p.c1.ssno FROM ptab2 p ; OK
176
Sintassi Comandi
� CREATE TYPE typename AS OBJECT(attrname datatype {, attrname datatype});
� CREATE OR REPLACE TYPE BODY typename IS metodo {metodo};� CREATE TABLE tablename OF typename
([attrname NOT NULL] {,attrname NOT NULL}[,PRIMARY KEY (attrname {,attrname })]);
� DROP TYPE typename;� DROP TABLE tablename;� ALTER TYPE typename REPLACE AS OBJECT (nuova definizione
tipo)� CREATE OR REPLACE TYPE BODY typename IS metodo {metodo};
177
Tipi riferimento
� Vale quanto visto per SQL-99 – cambia un minimo la sintassi– REF <nome tipo> SCOPE IS <nome_tabella>
178
Tipi riferimento - manipolazione
� Tre funzioni principali:– referenziazione ref( ) : dato un oggetto di un certo
tipo, restituisce l’identificatore per quell’oggetto– dereferenziazione deref( ): dato un identificatore,
restituisce l’oggetto– value ( ): prende un alias di relazione e restituisce
l’oggetto tupla associato (utilizzando il costruttore opportuno)
179
Tipi riferimento
CREATE TYPE t_persona AS OBJECT(nome VARCHAR2(10),cognome VARCHAR2(15),data_di_nascita DATE,indirizzo t_indirizzo,madre REF t_persona,padre REF t_persona);
CREATE TABLE Persone OF t_persona;
180
Tipi riferimentoPersone
nome cognome ... madre padre
181
Tipi riferimento
INSERT INTO PersoneVALUES(‘Mario’,’Rossi’, …,NULL,NULL);
INSERT INTO PersoneVALUES(t_persona(‘Maria’,’Bianchi’,…,NULL,NULL));
INSERT INTO PersoneVALUES(‘Giovanni’,’Rossi’,…,NULL,NULL);
182
Tipi riferimento
Persone
nome cognome ... madre padre
Mario Rossi NULL NULL
Maria Bianchi NULL NULL
Giovanni Rossi NULL NULL
183
Tipi riferimento
UPDATE Persone pSET p.madre = (SELECT ref(d1) FROM Persone d1
WHERE nome = 'Maria')WHERE nome = ’Giovanni';
La madre di Giovanni Rossi è Maria Bianchi
184
Tipi riferimento
Persone
nome cognome ... madre padre
Mario Rossi NULL NULL
Maria Bianchi NULL NULL
Giovanni Rossi NULL
185
Tipi riferimento
SELECT value(p) FROM Persone p
si ottiene:
t_persona(‘Mario’,’Rossi’,NULL,NULL)t_persona(‘Maria’,’Bianchi’,NULL,NULL)t_persona(‘Giovanni,’Rossi’,xxxyyywww,NULL)dove xxxyyywww è l’identificatore della tupla di Maria
Bianchi
186
SELECT deref(p.madre)FROM Persone pWHERE nome = ‘Giovanni’;
Seleziona tutte le informazioni contenute in Persone relative alla madre di Giovanni
restituisce
t_persona(‘Maria’,’Bianchi’,NULL,NULL)
Tipi riferimento
187
Tipi collezione
� Due tipi collezione:– nested table– varray
� I tipi collezione possono avere come elementi istanze di tipi oggetto
� Un tipo oggetto può avere un attributo di tipo collezione
188
Tipi collezione
� Le nested table possono essere considerate come una tabella con una sola colonna
� differenze tra varray e nested table:– gli array hanno dimensione fissa mentre le nested
table hanno dimensione variabile– gli array sono memorizzati all’interno della tabella
nella quale sono utilizzati o come BLOB mentre le nested table sono memorizzate in tabelle separate, con una colonna in più che identifica una tupla della tabella a cui appartengono
189
Tipi collezione
� Non possono essere direttamente usati nella definizione di un attributo
� è sempre necessario dare un nome al tipo collezione prima di usarlo
� per ogni tipo, esiste un costruttore� per creare un’istanza, è necessario passare al
costruttore un’insieme di elementi del tipo su cui il tipo collezione si basa
190
Varray - creazione
CREATE TYPE Progetto AS OBJECT(id INTEGER,titolo VARCHAR2(25),costo NUMBER(7,2));
CREATE TYPE Lista_Progetti AS VARRAY(50) OF Progetto;
CREATE TYPE Dipartimento AS OBJECT(id INTEGER,nome VARCHAR2(15),budget NUMBER(11,2),progetti Lista_Progetti);
CREATE TABLE Dipartimenti OF Dipartimento;
191
Varray - inserimento
INSERT INTO DipartimentiVALUES(30,’R&D’,1000000000,Lista_Progetti(Progetto(1,’DBMS’,10000000),
Progetto(3,’C++’,20000000)));
INSERT INTO DipartimentiVALUES(32,’Marketing’,1000000000,Lista_Progetti(Progetto(1,’Nuova Pubblicità’,10000000),
Progetto(3,’Incentivi Personale’,20000000)));
192
Nested table - creazione
CREATE TYPE Corso AS OBJECT(id NUMBER(4),nome VARCHAR2(25),crediti NUMBER(1));
CREATE TYPE Lista_Corsi AS TABLE of Corso;
193
Nested table - creazione
CREATE TYPE Dipartimento AS OBJECT(nome VARCHAR2(20),direttore VARCHAR2(20),corsi Lista_Corsi)NESTED TABLE corsi STORE AS corsi_tab ;
CREATE TABLE Dipartimenti OF DipartimentoNESTED TABLE corsi STORE AS corsi_tab;
194
Nested Table - creazione
� Notare la clausola NESTED TABLE nel lucido precedente
� Questa clausola è necessaria perché le colonne definite come nested table sono memorizzate come tabelle separate
� Il formato generale della clausola è
NESTED TABLE colname STORE AS tablename
� La tabella tablename si dice “child-table” della tabella al cui interno è definita (detta “parent-table”)
� Una child-table può essere acceduta solo tramite la parent-table
195
Nested table: inserimento
INSERT INTO DipartimentiVALUES(‘Informatica’,’Italiani’,
Lista_Corsi(Corso(1000,’Programmazione I’,2),Corso(1001,’Logica Matematica’,1),Corso(1002,’Basi di Dati’,2),Corso(1003,’Grafica’,1)));
196
Tipi collezione - interrogazione
� Due possibilità– selezionare la collezione annidata
� una tupla per ogni collezione
– selezionare la collezione, non annidata� una tupla per ogni elemento della collezione� funzione TABLE
� applicabili sia a VARRAY che NESTED TABLE
197
Selezione annidata - esempio
SELECT corsiFROM Dipartimenti;
il risultato è
Lista_Corsi(Corso(1000,’Programmazione I’,2),Corso(1001,’Logica Matematica’,1),Corso(1002,’Basi di Dati’,2),Corso(1003,’Grafica’,1))
198
Selezione non annidata - esempio
SELECT t.*FROM Dipartimenti d, TABLE(d.corsi) t;
il risultato è
Corso(1000,’Programmazione I’,2)Corso(1001,’Logica Matematica’,1)Corso(1002,’Basi di Dati’,2)Corso(1003,’Grafica’,1)
199
Funzione TABLE
� La funzione TABLE prende un valore di tipo collezione e permette di utilizzarlo come una tabella
� la query precedente realizza un join di ogni tupla della tabella dipartimento con ogni elemento dell’oggetto collezione
� può anche essere applicata a query che restituiscono un singolo valore di tipo collezione
200
Esempio
SELECT d.nome, t.*FROM Dipartimenti d, TABLE(d.corsi) t;
il risultato è
‘Informatica’ Corso(1000,’Programmazione I’,2)‘Informatica’ Corso(1001,’Logica Matematica’,1)‘Informatica’ Corso(1002,’Basi di Dati’,2)‘Informatica’ Corso(1003,’Grafica’,1)
201
Esempio
SELECT * FROM TABLE(SELECT corsi FROM Dipartimenti
WHERE nome = ‘Psicologia’);restituisce le informazioni sui corsi del dipartimento Psicologia
SELECT crediti FROM TABLE(SELECT corsi FROM Dipartimenti
WHERE nome = ‘Psicologia’);restituisce i crediti di tutti i corsi del dipartimento Psicologia
le interrogazioni sono corrette se le query a cui viene applicata la funzione TABLE restituiscono un solo elemento
202
Tipi collezione - DML
� Nested table:– la funzione TABLE può essere utilizzata per
modificare una collezione
� VARRAY:– A differenza delle nested table, i singoli elementi di
un varray non possono essere manipolati mediante istruzioni SQL
– Per selezionare o fare l’update di un certo elemento di un VARRAY è necessario usare PL/SQL
– supporto di funzioni specifiche per la manipolazione degli array
203
Nested tables
UPDATETABLE(SELECT corsi FROM Dipartimenti
WHERE nome = ‘Psicologia’)SET crediti = crediti + 1WHERE id IN (2200,3540);
DELETE FROM TABLE(SELECT corsi FROM DipartimentiWHERE nome = ‘Inglese’) p
WHERE p.crediti = 2;
204
Varray
DECLARE miei_progetti lista_progetti;
SELECT progetti INTO miei_progetti.FROM dipartimenti.WHERE id = 30;
IF miei_progetti(i).Titolo = ‘DBMS’ …
205
Ereditarietà
� Solo a livello di tipi� vale quanto detto per SQL-99� in più:
– sia tipi che metodi possono essere definiti FINAL/NOT FINAL� FINAL: overriding non possibile
– sostituibilità– late binding
206
Sostituibilità
� Possibilità di utilizzare istanze di un sottotipo in ogni contesto in cui può essere utilizzato un supertipo
� sostituibilità a livello di – attributo– tupla
207
Sostituibilità a livello di attributo
� Attributo con tipo REF(<tipo1>) può contenere valori di tipo REF(<tipo2>) se <tipo2> sottotipo di <tipo1>
� Attributo con tipo ADT <tipo1> può contenere valori di tipo ADT <tipo2> se <tipo2> sottotipo di <tipo1>
� Attributo con tipo collezione su tipo <tipo1> può contenere valori di tipo <tipo2> se <tipo2> sottotipo di <tipo1>
208
Sostituibilità a livello di tupla
� Una tabella tipata su tipo <tipo1> può contenere istanze del tipo <tipo2> se <tipo2> sottotipo di <tipo1>
209
Esempio
CREATE TYPE Person_typ AS OBJECT( ssn NUMBER,name VARCHAR2(30),address VARCHAR2(100)) NOT FINAL;
CREATE TYPE Student_typ UNDER Person_typ( deptid NUMBER,major VARCHAR2(30)) NOT FINAL;
CREATE TYPE PartTimeStudent_typ UNDER Student_typ( numhours NUMBER);
210
Esempio (su attributi)
CREATE TABLE Dipartimenti( Id INTEGER,
nome VARCHAR(20),manager Person_typ);
INSERT INTO dipartimentiVALUES (1,’ricerca’, Person_typ(1243, 'Bob', '121 Front St'));
INSERT INTO dipartimentiVALUES (2,’sviluppo’,Student_typ(3456, 'Joe', '34 View', 12, 'HISTORY'));
INSERT INTO dipartimentiVALUES (3, ‘testing’,PartTimeStudent_typ(5678, 'Tim', 13, 'PHYSICS', 20));
211
Esempio (su tuple)
CREATE TABLE persons OF Person_typ;
INSERT INTO personsVALUES (Person_typ(1243, 'Bob', '121 Front St'));
INSERT INTO personsVALUES (Student_typ(3456, 'Joe', '34 View', 12, 'HISTORY'));
INSERT INTO personsVALUES (PartTimeStudent_typ(5678, 'Tim', 13, 'PHYSICS', 20));
212
Limitare sostituibilità
CREATE TABLE Dipartimenti( Id INTEGER,
nome VARCHAR(20),manager Person_typ)
COLUMN manager NOT SUBSTITUTABLE AT ALL LEVELS ;
Manager può solo essere una persona (sottotipi non ammessi)
CREATE TABLE persons OF Person_typ NOT SUBSTITUTABLE AT ALL LEVELS ;
la tabella persons può contenere solo persone
213
Limitare sostituibilità
CREATE TABLE Dipartimenti( Id INTEGER,
nome VARCHAR(20),manager Person_typ)
COLUMN manager IS OF (ONLY Student_typ) ;
Si può specificare solo un sottotipo
214
Interrogazioni
� Funzioni utili:– REF– DEREF– VALUE– IS OF TYPE– ...
� REF, DEREF, VALUE già viste
215
Esempio
� Si consideri la tabella Dipartimenti presentata in precedenza (senza limitazioni di sostituibilità)
SELECT *FROM Dipartimenti pWHERE p.manager IS OF
(Student_typ,PartTimeStudent_typ)
restituisce i dipartimenti i cui manager sono studenti o studenti part-time
216
Esempio
� Si consideri la tabella Persons presentata in precedenza (senza limitazioni di sostituibilità)
SELECT *FROM Persons pWHERE VALUE(p) IS OF
(Student_typ,PartTimeStudent_typ)
restituisce le persone che sono studenti o studenti part-time
217
Differenze principali con SQL-99
� Tipi collezione– SQL-99: solo ARRAY– Oracle: VARRAY e NESTED TABLE
� Ereditarietà– SQL-99: su tipi e tabelle, no sostituibilità– Oracle: solo su tipi sostituibilità
218
Utilizzo di funzionalità OR da JDBC
� Consideriamo JDBC 3 (ma molte estensioni già presenti in JDBC 2)
� Nuove interfacce per implementare mapping di tipi object relational in tipi Java
219
Creazione nuovi tipi
� Essendo un comando DDL, si utilizza il metodo executeUpdate
String type = ‘CREATE TYPE t_indirizzo ASnumero_civico INTEGER,via VARCHAR(50),città CHAR(20),stato CHAR(2),cap INTEGER’;
Statement st = con.createStatement( );st.executeUpdate(type);
220
Manipolazione valori nuovi tipi da Java
� Nuove interfacce (solo per tipi standard):– STRUCT
� per mappare ADT
– REF� per mappare valori di tipo riferimento
– ARRAY� per mappare valori di tipo array
– SQLDATA� per semplificare mapping di ADT
221
Manipolazione valori nuovi tipi da Java
� Per ogni interfaccia, il driver prescelto specificherà una classe che implementa l’interfaccia
� questo permette di gestire le eventuali differenze esistenti tra DBMS
222
Tipi semplici
� Nessuna nuova interfaccia� si manipolano utilizzando i metodi del tipo di
base� Esempio
– CREATE TYPE id_impiegato AS INTEGER FINAL;
– Si usano i metodi getInt e setInt per leggere e scrivere campi di tipo id_impiegato
223
ADT
� Le istanze di tipi ADT vengono mappate in istanze di classe Struct
� un oggetto di tipo Struct contiene un valore per ogniattributo dell’ADT a cui si riferisce
� i metodi per poter leggere e scrivere valori di attributicon tipo ADT sono
– ResultSet - getObject(int): per accedere attributi di tipo ADT– Struct - getAttributes(): per accedere le componenti di
un’istanza– PreparedStatement - setObject(int, Struct): per settare
parametri di tipo ADT
224
Esempio
CREATE TABLE Impiegati (imp# INTEGER PRIMARY KEY,nome CHAR(20),indirizzo t_indirizzo );
vogliamo attribuire l’indirizzo dell’impiegato 12 a Verdi
225
Esempio (continua)
String query = “select indirizzo from Impiegati where imp# = 12”;Statement st = con.createStatement();ResultSet rs = st.executeQuery(query);rs.next();
Struct indirizzo = (Struct) rs.getObject(1); //si ottiene l’istanzaObject[ ] ind_attr = indirizzo.getAttributes(); //si accedono gli attributiSystem.out.print( “Via ” + ind_attr[1] + “ “ + ind_attr[0] + “ “ + ind_attr[2]
+ “ “ + ind_attr[4] + “ “ + ind_attr[3]);
226
Esempio (continua)
String up = “update Impiegati set indirizzo = ? where nome =‘Verdi’”;PreparedStatement pst = con.prepareStatement(up);pst.setObject(1, indirizzo) ;pst.executeUpdate( );
227
Tipi riferimento
� Le istanze di tipi riferimento (puntatori) vengono mappate in istanze di classe Ref
� i metodi per poter leggere e scrivere valori di attributicon tipo riferimento sono
– ResultSet - getRef(int): per accedere attributi di tipo riferimento– Ref - getObject( ): dato un valore di tipo riferimento, restituisce
l’oggetto puntato (solo JDBC 3)– Ref - setObject(Struct): il parametro diventa il nuovo oggetto
puntato (solo JDBC 3)– PreparedStatement - setRef(int, Ref): per settare parametri di
tipo riferimento
228
Esempio
CREATE TYPE t_progetto ASprj# INTEGER,nome VARCHAR(20),descrizione VARCHAR(50),budget INTEGER;
CREATE TABLE Progetti of t_progetto;
CREATE TABLE Impiegati(imp# INTEGER PRIMARY KEY,nome VARCHAR(50),indirizzo t_indirizzo,assegnamento REF(t_progetto));
229
Esempio (continua)
� Si vuole associare il progetto dell’impiegato 12 a Verdi e visualizzare le informazioni su tale progetto
230
Esempio (continua)
String query = “select assegnamento from Impiegati where imp# = 12”;
Statement st = con.createStatement();ResultSet rs = st.executeQuery(query);rs.next();Ref assegn_ref = rs.getRef(1); //si ottiene l’identificatoreString update = ‘”update impiegati set assegnamento = ? where
nome = “Verdi”;PreparedStatement pst = con.prepareStatement(update);pst.setRef(1,assegn_ref); pst.executeUpdate( );
231
Esempio (continua)
Struct progetto = (Struct) assegn_ref.getObject( );Object [ ] prog_attr = progetto. getAttributes();System.out.print(ind_attr[0] + “ “ + ind_attr[1] + “ “ + ind_attr[2] + “ “ +
+ ind_attr[3]);
232
Tipi array
� Le istanze di tipi array vengono mappate in istanze di classe Array
� rappresentano puntatori alla base di dati (il contenuto non vienecopiato)
� i metodi per poter leggere e scrivere valori di attributi di tipo array sono
– ResultSet - getArray(): per accedere attributi di tipo array (restituisceun puntatore all’istanza)
– Array - getArray( ): permette di copiare i dati contenuti nell’array in strutture locali al programma
– Array - getResultSet (): restituisce un result set che contiene unatupla per ogni elemento dell’array, con due colonne. La prima contiene l’indice (partendo da 1), la seconda il valore
– PreparedStatement - setArray(int,Array): per settare parametri di tipoArray
233
Esempio
CREATE TABLE Impiegati(imp# INTEGER PRIMARY KEY,nome VARCHAR(50),indirizzo t_indirizzo,competenze VARCHAR(20) ARRAY[10]);
si vogliono stampare tutte le competenze dell’impiegato 12
234
Esempio (continua)
String query = “select competenze from Impiegati where imp# = 12”;Statement st = con.createStatement();ResultSet rs = st.executeQuery(query);rs.next();Array comp = rs.getArray(1); //restituisce un puntatore all’arrayString [ ] comp_arr = (String [ ]) comp.getArray( ) ;for (int i = 0; i < comp_arr.length(); i++)
System.out.print(comp_arr[i] )
235
Tipi collezione
� Come abbiamo visto i sistemi non sono conformi allo standard per quanto riguarda i tipi collezione
� il driver potrà eventualmente utilizzare la classe Array anche per gestire altri tipi collezione– in JDBC le nested table di Oracle si mappano in
oggetti di tipo Array
236
SQL Data
� Interfaccia � ogni classe che implementa SQLData permette
di definire un mapping esplicito tra un tipo SQL e una classe Java– casting definito esplicitamente dall’utente
� semplifica l’accesso agli oggetti strutturati– la lettura di un oggetto restituisce un’istanza della
classe associata dal mapping– non si devono piu’ utilizzare Struct e Object
� non lo vediamo