Viste

Le viste sono tabelle virtuali contenenti dati provenienti da altre tabelle della base di dati. Le viste hanno due principali funzioni:

• definire una porzione della base di dati accessibili da un particolare gruppo di utenti. Questa caratteristica contribuisce a realizzare la privatezza dei dati, una delle qualità offerte da un DBMS;
• estendere il potere espressivo di SQL permettendo un annidamento delle interrogazioni più sofisticato rispetto a quello già discusso oppure la realizzazione di attributi calcolati che non dipendono totalmente dai dati presenti nella base.

Per quanto riguarda la prima funzione, consideriamo il caso di studio della rete teatrale. Si consideri una vista che mostra, per ogni spettacolo, i dati relativi al numero di paganti, all'affluenza e all'incasso. Tale vista sfrutta una porzione limitata della base e deve essere accessibile solo da un gruppo di utenti interni alla rete e non, ad esempio, da uno spettatore. Possiamo definire tale vista in due passi: prima definiamo una vista StatScena che calcola le statistiche per una messa in scena e poi usiamo tale vista nella definizione della vista statSpettacolo che mostra le statistiche per uno spettacolo.

  create view statScena(spettacolo, paganti, affluenza, incasso) as
  select Sce.spettacolo, count(*), count(*) / Spa.capienza, sum(P.prezzo)
  from messaInScena Sce, spazio Spa, prenotazione P
  where (Sce.spazio = Spa.nome) and
        (P.dataSpettacolo = Sce.data) and
        (P.oraSpettacolo = Sce.ora) and
        (P.spazioSpettacolo = Sce.spazio)
  group by Sce.data, Sce.ora, Sce.spazio

  create view statSpettacolo(spettacolo, paganti, affluenza, incasso) as
  select Spe.titolo, avg(Sce.paganti), avg(Sce.affluenza), avg(Sce.incasso)
  from spettacolo Spe, statScena Sce
  where (Spe.titolo = Sce.spettacolo)
  group by Spe.titolo

Naturalmente, gli attributi della vista devono essere compatibili con gli attributi dell'interrogazione che la definisce. Le viste possono essere usate per la definizione di altre viste o nelle interrogazioni come se fossero tabelle fisiche.

Vediamo ora come si possono utilizzare le viste per formulare interrogazioni che altrimenti non sarebbero definibili in SQL. L'idea è la seguente: dato che una vista è una tabella (virtuale), posso usarla come tale, dunque anche nella clausola from di una query SQL. Questo meccanismo permette una nuova forma di annidamento di interrogazioni oltre a quella già vista che consente di nidificare query nei predicati della clausola where. Vediamo qualche esempio:

Vogliamo calcolare, per ogni stagione teatrale, la media degli spettatori paganti, la media dell'affluenza e la media degli incassi riferite agli spettacoli della stagione. Possiamo sfruttare la vista statSpettacolo sopra definita nella seguente interrogazione:

select nomeStagione, biennioStagione, avg(paganti) as mediaPaganti,
       avg(affluenza) as mediaAffluenza, avg(incasso) as mediaIncasso
from statSpettacolo SS, proposta P
where SS.spettacolo = P.spettacolo
group by nomeStagione, biennioStagione

La seguente ordina gli spettacoli di una certa stagione per incasso:

select SS.spettacolo, incasso
from statSpettacolo SS, proposta P
where (SS.spettacolo = P.spettacolo) and
      (nomeStagione = 'Contatto') and
      (biennioStagione = '2006/2007')
order by incasso desc 

Supponiamo ora di lavorare sul seguente schema:

teatro(nome, indirizzo)

dipendente(cf, nome, cognome)

lavoro(teatro, dipendente, ruolo)

Vogliamo identificare il teatro con il numero massimo di dipendenti. Intuitivamente, vogliamo prima contare il numero di dipendenti di ogni teatro e poi prendere il massimo. Si potrebbe pensare di scrivere in questo modo:

select max(count(*))
from lavoro
group by teatro

In realtà questa query è scorretta perchè in SQL gli operatori di aggregazione non possono essere annidati. Possiamo provare la seguente soluzione:

select teatro, count(*)
from lavoro
group by teatro
having count(*) >= all (select count(*)
                        from lavoro
                        group by teatro)

Questa soluzione è formalmente corretta però non potrebbe essere riconosciuta da qualche interprete SQL in quanto fa una di un annidamento di query nella clausola having. Una soluzione alternativa che fa uso delle viste è la seguente:

create view numeroDipendenti(teatro, numero) as
select teatro, count(*) 
from lavoro
group by teatro

select teatro, numero
from numeroDipendenti
where numero = (select max(numero)
                from numeroDipendenti)

L'esempio che segue seleziona il numero medio di dipendenti dei teatri della rete. Si noti che vengono inclusi nel conteggio anche i teatri con nessun dipendente.

create view numeroDipendenti(teatro, numero) as
select nome, count(all dipendente) 
from teatro left join lavoro on (teatro.nome = lavoro.teatro)
group by nome

select avg(numero) as numeroMedioDipendenti
from numeroDipendenti

Una vista, creata con il comando create, è una componente facente parte dello schema della base di dati così come i domini, le tabelle, le asserzioni e i trigger. In particolare, una vista, una volta definita, può essere rimossa mediante il comando drop. In realtà, quando una vista viene usata per esprimere una interrogazione, la sua natura è temporanea. Una vista temporanea è una vista definita nel contesto di una interrogazione. Essa è accessibile dall'interrogazione nella quale appare ma non appartiene allo schema della base di dati. In particolare, non può essere usata da altre interrogazioni oltre a quella a cui appartiene e non può essere rimossa dallo schema. E' possibile definire viste temporanee con la clausola with. In particolare, l'ultima interrogazione può essere riscritta come segue:

with numeroDipendenti(teatro, numero) as
select nome, count(all dipendente) 
from teatro left join lavoro on (teatro.nome = lavoro.teatro)
group by nome

select avg(numero) as numeroMedioDipendenti
from numeroDipendenti

Un altro modo in cui le viste aumentano l'espressività di SQL è mediante le viste ricorsive, introdotte in SQL-3. Una vista ricorsiva è una vista V definita usando V stessa oppure usando un'altra vista V' che usa, direttamente o indirettamente, la vista V. Vediamo un esempio. Prendiamo lo schema di relazione che segue:

dipendente(cf, nome, cognome, capo)

dove capo è una chiave esterna di dipendente. Supponiamo che un dipendente senza capi abbia come capo sè stesso. Vogliamo recuperare tutti i superiori, diretti o indiretti, di un certo dipendente dato. Si noti che non è possibile farlo in SQL senza la ricorsione, perchè avremmo bisogno di un numero non prevedibile a priori di join della tabella dipendente con sè stessa. Vediamo una soluzione che prima definisce una vista ricorsiva temporanea e poi la usa per recuperare i capi del dipendente con codice 'ELSDLL72'.

with recursive superiore(cf, nome, cognome, capo) as
(
(select *
 from dipendente)
   union
(select dipendente.cf, dipendente.nome, dipendente.cognome, superiore.capo
 from dipendente, superiore
 where dipendente.capo = superiore.cf)
)

select capo
from superiore
where cf = 'ELSDLL72'

La vista ricorsiva superiore contiene i dipendenti (identificati da cf, nome e cognome) e i loro capi diretti o indiretti. Si noti che per ogni dipendente ci possono essere più capi, dunque cf non identifica le tuple della vista superiore. In generale non è definito il concetto di chiave per le viste. La definizione ricorsiva di superiore specifica un caso base (i capi diretti) e un caso ricorsivo (i capi indiretti) in cui la vista superirore viene riusata. Infine l'interrogazione di partenza si risolve semplicemente selezionando i capi del dipendente con codice 'ELSDLL72'.

E' istruttivo analizzare il metodo di calcolo di una vista ricorsiva. L'algoritmo è simile a quello che risolve il problema di raggiungibilità su un grafo: a partire da un insieme iniziale di nodi, trovare l'insieme dei nodi raggiungibili da questi attraverso un cammino arbitrario. Come primo passo si applica da definizione base e si ottiene un primo risultato X (i dipendenti e i loro capi diretti nel nostro esempio). A questo punto si applica la definizione ricorsiva in cui la tabella superiore contiene tutte e sole le tuple in X. In questo modo si ottiene un secondo risultato Y (i dipendenti e i capi dei loro capi). Se Y contiene qualche tupla non presente in X, allora si applica la definizione ricorsiva in cui la tabella superiore contiene tutte e sole le tuple in Y meno X (le tuple nuove), ottenendo un nuovo risultato Z (i dipendenti e i capi dei capi dei loro capi). Se Z contiene qualche tupla non presente in X unito Y, allora si procede similmente finchè si ottiene un risultato che non aggiunge nessuna riga rispetto a ciò che è stato calcolato fino a quel momento. Il risultato della valutazione ricorsiva è l'unione insiemistica di tutti i risultati parziali ottenuti (X unito Y unito Z e così via).

Ad esempio, se A ha come capo B, B ha come capo C, C ha come capo D, e D non ha capi (cioè ha come capo sè stesso), allora la prima iterazione produce l'insieme di coppie (dipendente, capo) pari a X = {(A,B), (B,C), (C,D), (D,D)}. La seconda iterazione produce l'insieme Y = {(A,C), (B,D), (C,D)}. Solo le prime due coppie sono nuove. La terza iterazione produce Z = {(A,D)}. La successiva iterazione non produce nulla e dunque il risultato finale è l'unione di X, Y e Z, cioè {(A,B), (B,C), (C,D), (D,D), (A,C), (B,D), (A,D)}

Infine, le viste sono utili per modellare attributi, come l'età di una persona, il cui valore non dipende completamente dai dati della base. In tal caso, il valore di età dipende anche dalla data corrente. Vediamo un esempio di vista che, per ogni teatro, seleziona i rispettivi dipendenti calcolando, tra l'altro, la loro età e il loro stipendio corrente:

create view 
dipendenti(teatro, cognome, nome, cf, età, telefonoFisso, telefonoMobile, email,
           stipendio, dataDiAssunzione, ruolo, cda) as
  select L.teatro, D.cognome, D.nome, D.cf, 
    case 
      when ((month(current_date) > month(D.dataDiNascita)) or
            (month(current_date) = month(D.dataDiNascita) and 
            day(current_date) >= day(D.dataDiNascita)))
      then year(current_date) - year(D.dataDiNascita)
      else year(current_date) - year(D.dataDiNascita) - 1
    end,  
    D.telefonoFisso, D.telefonoMobile, D.email, S.stipendio, 
    L.dataDiAssunzione, L.ruolo, L.cda
  from lavoro L, dipendente D, stipendio S
  where (L.dipendente = D.cf) and 
        (S.dipendente = D.cf) and
        (S.inizio = (select max(S2.inizio)
                     from stipendio S2
                     where S2.dipendente = D.cf))
  order by L.teatro, D.cognome, D.nome    

Vi sono due approcci complementari per implementare le viste. Il primo consiste nel riscrivere le interrogazioni che usano viste in termini di interrogazioni che usano tabelle di base. La seconda soluzione consiste nel materializzare le viste, cioè nel calcolare le relative tabelle virtuali, e usare queste tabelle per risolvere le interrogazioni che le usano. Il primo approccio ha il vantaggio di non dover salvare i dati delle tabelle virtuali associate alle viste. D'altronde tale approccio risulta inefficiente quando vi sono molte query che si riferiscono alla medesima vista. Il secondo approccio risolve questi problemi di efficienza ma ha lo svantaggio di dover mantenere la materializzazione della vista e aggiornala quando le tabelle di base su cui la vista è definita vengono modificate.