Algebra relazionale (III)
Esempi di Interrogazioni: 3
Trovare matricola e nome dei capi i cui
impiegati guadagnano tutti più di 2.000
1. Tutti gli impiegati? Selezionare capi che
hanno impiegati con stipendio < 2.000
Capo(Supervisione Matr=Impiegato(Stip<2.000(Impiegati)))
Esempi di Interrogazioni: 3
2. Sottrarre tali capi all’insieme di tutti i capi
Capo(Supervisione) –
Capo(Supervisione Matr=Impiegato(Stip<2.000(Impiegati)))
Esempi di Interrogazioni: 3
Trovare matricola e nome dei capi i cui
impiegati guadagnano tutti più di 2.000
Matr,Nome(Impiegati Matr=Impiegato
(Capo(Supervisione) –
Capo(Supervisione Matr=Impiegato(Stip<2.000(Impiegati)))))
Algebra con valori nulli
Come applicare espressioni di algebra
relazionale in presenza di tuple con valori
nulli?
Per esempio: Età>30(Impiegati)

Se in relazione Impiegati non si conosce età di alcune
persone: tuple 104 e 219 devono essere selezionate?
Matr Nome
Età
Stip
101
Mario Rossi
34
2.000
103
Mario
Bianchi
23
1.750
104
Luigi Neri
NULL 3.050
105
Nico Bini
44
210
Marco Celli
NULL 3.000
1.700
Algebra con valori nulli
Logica a 3 valori per il trattamento di valori veri,
falsi, sconosciuti: T, F, U (unknown)
Un predicato può assume valore U quando uno dei
termini del confronto ha valore nullo
Tabelle di verità dei connettivi: AND, OR, NOT
AND T U F
OR T U F
NOT
T
T U F
T
T T T
T
F
U
U U F
U
T U U
U
U
F
F F F
F
T U F
F
T
Algebra con valori nulli
Poiché ragionare su valori nulli è complesso,
adottiamo approccio semplificato al
trattamento del valore nullo nelle espressioni
dell’algebra relazionale
Definiamo due nuove condizioni atomiche di
selezione: dato attributo A


A IS NULL: vera su tupla t se il valore di t su A è
nullo; falsa se valore specificato
A IS NOT NULL: vera su t se valore di t su A
specificato, falsa altrimenti
Algebra con valori nulli
Interpretiamo le condizioni di selezione in
modo restrittivo, escludendo da selezione le
tuple con valore U, a meno che non sia
espicitamente incluso nella selezione
Algebra con valori nulli
Per esempio:


Età>30(Impiegati) – le tuple con Età null
non vengono selezionate (su di esse la
condizione Età>30 assume valore U)
Età>30  Età IS NULL(Impiegati) – si includono
anche le tuple con Età sconosciuta (104,
210 in relazione Impiegati)
Viste
Relazioni derivate definite su relazioni di
schema logico


Viste materializzate (con tuple memorizzate
in DB)
Relazioni virtuali, o viste (memorizzate in
DB mediante espressioni del linguaggio di
interrogazione, senza memorizzazione di
tuple)
Viste
DBMS offrono solo relazioni virtuali (no
ridondanza dati)
Interrogazioni che utilizzano viste sono
risolte sostituendo la definizione delle
viste alle loro occorrenze
Viste
Per esempio:



R1(A,B,C), R2(C,D,E), R3(E,G)
Vista: R = A>D(R1  R2)
Interrogazione: B=G(R  R3) risolta così:
B=G(A>D(R1  R2)  R3)
Viste
Viste utile per:


Permettere ad applicazioni di utilizzare
relazioni che contengono solo le
informazioni di interesse
Se schema di DB viene ristrutturato,
ricreare relazioni eliminate per evitare di
modificare le applicazioni che le usavano
 Per esempio: R(A,B,C) sostituita in DB da
R1(A,B), R2(B,C), e definiamo vista R= R1R2
SQL
Interrogazioni
Interrogazioni in SQL
Non esiste un SQL standard (vari
dialetti)
Formulazione di interrogazioni (query) è
parte del Data Manipulation Language,
DML
Anche usato nel Data Declaration
Language, DDL (per esempio, per
dichiarare vincoli di integrità)
Interrogazioni in SQL
Paradigma dichiarativo: si specifica la
descrizione dell’obiettivo e non il
modo con cui ottenerlo

A differenza dell’algebra relazionale, che è
procedurale
Cenni sull’implementazione
Interrogazione in SQL viene tradotta in
linguaggio procedurale
Sulla traduzione si fanno ottimizzazioni
algebriche (ecco a cosa serve
l’algebra…) …
… e non (queste ultime dipendono dalle
strutture sottostanti al DBMS in
questione)
Sintassi
Esistono, in generale, più modi per effettuare
un’interrogazione: scelte basate sulla
leggibilità (più che sull’efficienza…)
Struttura essenziale (introdurremo le
variazioni di volta in volta):
select ListaAttributi (target list)
from ListaTabelle (clausola “from”)
[where Condizione] (clausola “where”)
Significato dell’interrogazione
Si considera il prodotto cartesiano fra le
tabelle della clausola “from”
Si selezionano quelle tuple che
soddisfano la condizione della clausola
“where” (opzionale)
Si danno in ouput i valori di quegli
attributi che sono elencati nella target
list (la lista dopo la clausola “select”)
Tabella “Impiegato”
Impiegato
Nome
Cognome Dipart
Ufficio Stipendio Città
Mario
Rossi
Amministr 10
15
Milano
Carlo
Bianchi
Prod
20
12
Torino
Giuseppe Verdi
Amministr 20
13
Roma
Franco
Neri
Distrib
16
15
Napoli
Carlo
Rossi
Direzione
14
27
Milano
Lorenzo
Lanzi
Direzione
7
21
Genova
Paola
Burroni
Ammistr
75
13
Venezia
Marco
Franco
Prod
20
14
Roma
Interrogazione 1
select Stipendio as Salario
from Impiegato
where Cognome = ‘Rossi’
Salario
15
27
Interrogazione 1
select Stipendio as Salario
from Impiegato
where Cognome = ‘Rossi’
Salario
15
27
alia
s
Interrogazione 2
select *
from Impiegato
where Cognome =
‘Rossi’
Nome Cognome Dipart
Ufficio Stipendio Città
Mario Rossi
Amministr 10
15
Milan
o
Carlo Rossi
Direzione 14
27
Milan
o
Interrogazione 2
select *
from Impiegato
where Cognome =
‘Rossi’
tutti
Nome Cognome Dipart
Ufficio Stipendio Città
Mario Rossi
Amministr 10
15
Milan
o
Carlo Rossi
Direzione 14
27
Milan
o
Interrogazione 3
select Stipendio/12 as StipMens
from Impiegato
where Cognome = ‘Bianchi’
StipMens
1
Interrogazione 3
select Stipendio/12 as StipMens
from Impiegato
where Cognome = ‘Bianchi’
espressioni
StipMens
1
Join in SQL (primo modo)
Per formulare interrogazioni che
coinvolgono più tabelle occorre fare un
join
In SQL un modo è:


elencare le tabelle di interesse nella “from”
mettere nella “where” le condizioni
necessarie per mettere in relazione fra loro
gli attributi di interesse
Tabella “Dipartimento”
Dipartimento
Nome
Indirizzo
Città
Amministr Via Tito Livio
27
Milan
o
Prod
P.le Lavater 3
Torino
Distrib
Via Segre 9
Roma
Direzione
Via Tito Livio
27
Milan
o
Ricerca
Via Morone 6
Milan
o
Interrogazione 4
Restituire nome e cognome degli impiegati e delle
città in cui lavorano
select
Impiegato.Nome,Cognome,
Dipartimento.Città
from
Impiegato,Dipartimento
where
Dipart = Dipartimento.Nome
Interrogazione 4
Restituire nome e cognome degli impiegati e delle
città in cui lavorano
La notazione punto ( )
select
Impiegato.Nome,Cognome, serve per disambiguare
Dipartimento.Città
Suggerimento: “from”,
from
“where”, target list
Impiegato,Dipartimento
where
Dipart = Dipartimento.Nome
Risultato interrogazione 4
Impiegato.Nome
Cognome Dipartimento.Città
Mario
Rossi
Milano
Carlo
Bianchi
Torino
Giuseppe
Verdi
Milano
Franco
Neri
Roma
Carlo
Rossi
Milano
Lorenzo
Lanzi
Milano
Paola
Burroni
Milano
Marco
Franco
Torino
Interrogazione 5
select
I.Nome, Cognome, D.Città
from
Impiegato [as] I, Dipartimento [as] D
where
Dipart = D.Nome
L’aliasing per le tabelle serve a disambiguare,
ma non solo (vedremo…)
Sulla clausola “where”
Ammette come argomento un’espressione
booleana
Predicati semplici combinati con not, and,
or (not ha la precedenza, consigliato l’uso
di parantesi(,))
Ciascun predicato usa operatori: =, <>, <, >,
<=, >=
Confronto tra valori di attributi, costanti,
espressioni
Interrogazione 6
select Nome,Cognome
from
Nome
Cognome
Impiegato
Giuseppe Verdi
where
Ufficio = 20 and
Dipart =‘Amministr’
Interrogazione 7 e 8
1
2
select Nome, Cognome
from Impiegato
where
Dipart=‘Prod’ or
Dipart=‘Amministr’
select Nome, Cognome
from Impiegato
where
Cognome=‘Rossi’ and
(Dipart=‘Prod’ or
Dipart=‘Amministr’)
Nome
Cognome
Mario
Carlo
Paola
Marco
Rossi
Bianchi
Burroni
Franco
1
Giuseppe Verdi
Nome
Mario
2
Operatore like
_ = carattere arbitrario
% = stringa di lunghezza arbitraria
(anche 0) di caratteri arbitrari
Esempi:

like ab%ba_ = tutte le stringhe che
cominciano con “ab” e che hanno “ba”
come coppia di caratteri prima dell’ultima
posizione (es. abjjhhdhdbak,abbap)
Interrogazione 9
select *
from Impiegato
where Cognome like ‘_o%i’
or Cognome like ‘_u%i’
Nome Cognome Dipart
Ufficio Stipendio Città
Mario Rossi
Amministr 10
45
Milano
Carlo Rossi
Direzioni 14
80
Milano
Paolo
Burroni
Amministr 75
40
Venezia
Gestione dei valori nulli
Attributo con valore nullo = non
applicabile a una certa tupla, o valore
sconosciuto, o non si sa nulla
SQL offre il predicato “is null”:
Attributo is [not] null
Gestione dei valori nulli
Stipendio>13: cosa succede se l’attributo
Stipendio è nullo? Scelte:

Logica a 2 valori e controllo esplicito. Per esempio:
(Stipendio > 13) or (Stipendio <= 13)
or (Stipendio is null)

Usare un terzo valore di verità unknown: soluzione
che crea problemi nei casi complessi (valutazione
“globale” delle formule logiche)
Uso delle variabili di alias
Non solo per disambiguare la notazione
Ci sono casi in cui una stessa tabella
serve più di una volta
Caso speciale: quando si deve
confrontare una tabella con se stessa
(il modello relazionale è relationoriented, non tuple-oriented )
Interrogazione 10
• Estrarre nome e cognome degli impiegati che
hanno lo stesso cognome (ma nome diverso)
di impiegati che lavorano nel dipartimento
Produzione
Interrogazione 10
• Estrarre nome e cognome degli impiegati che
hanno lo stesso cognome (ma nome diverso)
di impiegati che lavorano nel dipartimento
Produzione
select I1.Cognome, I1.Nome
from Impiegato I1, Impiegato I2
where I1.Cognome = I2.Cognome
and I1.Nome <> I2.Nome
and I2.Dipart = ‘Prod’
Interrogazione 10
• Estrarre nome e cognome degli impiegati che
I2 usata
per cognome (maper
evitarediverso)
hanno lo
stesso
nome
trovare tuple
output di
di impiegati
assoc. ache lavorano nel dipartimento
tupla con
‘Prod’
se stessa
Produzione
select I1.Cognome, I1.Nome
from Impiegato I1, Impiegato I2
where I1.Cognome = I2.Cognome
and I1.Nome <> I2.Nome
and I2.Dipart = ‘Prod’
Interrogazione 11
• Estrarre il nome e lo stipendio dei capi degli
impiegati che guadagnano più dei loro capi,
date:
Impiegati(Matricola, Nome, Età, Stipendio)
Supervisione(Capo, Impiegato)
dove Capo e Impiegato sono chiavi esterne di
Impiegati (e.g., sono dei numeri di matricola)
Interrogazione 11 (sol.)
select
I1.Nome, I1.Stipendio
from
Impiegato I1, Impiegato I2,
Supervisione
where
I1.Matricola = Capo
and I2.Matricola = Impiegato
and I2.Stipendio > I1.Stipendio
Interrogazione 11 (sol.)
select
I1.Nome, I1.Stipendio
from
Impiegato I1, Impiegato I2,
Supervisione
where
I1.Matricola = Capo
and I2.Matricola = Impiegato
and I2.Stipendio > I1.Stipendio
I1 per i capi,
I2 per gli
impiegati
Interpretazione algebrica delle
interrogazioni SQL
select T1.Attrib_11,…,Th.Attrib_hm
from Tabella1 as T1,…,Tabellan as Tn
where condizione
T1.Attrib_11,…,Th.Attrib_hm(
condizione(Tabella1 X … X Tabellan))
Algebra->calcolo, SQL->linguaggio
SQL gestisce i duplicati (select e select
distinct), algebra no (basata su insiemi)
select *
from Impiegati
ordered by [asc|desc] Matricola
Operatori aggregati
Interrogazioni nidificate
per
ordinare
le tuple
Operatori aggregati
Algebra relazionale: tutte le condizioni
sono valutate su una tupla alla volta, in
modo indipendente
SQL offre degli operatori che lavorano
su più di una tupla alla volta:
count,sum,max,min,avg
Interrogazione 12
select count(*)
from Impiegato
where Dipart = ‘Prod’
count(<*|[distinct|all|]ListaAttributi>)
valori diversi tra
loro
non null