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 1.700

210 Marco Celli NULL

3.000

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 nulloTabelle di verità dei connettivi: AND, OR, NOT

AND

T U F

T T U F

U U U F

F F F F

OR

T U F

T T T T

U T U U

F T U F

NOT

T F

U U

F T

Algebra con valori nulli

Poiché ragionare su valori nulli è complesso, adottiamo approccio semplificato al trattamento del valore nullo nelle espressioni dell’algebra relazionaleDefiniamo 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= R1R2

SQL

Interrogazioni

Interrogazioni in SQL

Non esiste un SQL standard (vari dialetti)Formulazione di interrogazioni (query) è parte del Data Manipulation Language, DMLAnche 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 proceduraleSulla 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”

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

Impiegato

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

alias

Interrogazione 2

select *

from Impiegato

where Cognome = ‘Rossi’

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Rossi Direzione

14 27 Milano

Interrogazione 2

select *

from Impiegato

where Cognome = ‘Rossi’

tutti

Nome

Cognome

Dipart Ufficio

Stipendio

Città

Mario Rossi Amministr

10 15 Milano

Carlo Rossi Direzione

14 27 Milano

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 joinIn 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”

Nome Indirizzo Città

Amministr

Via Tito Livio 27

Milano

Prod P.le Lavater 3 Torino

Distrib Via Segre 9 Roma

Direzione

Via Tito Livio 27

Milano

Ricerca Via Morone 6 Milano

Dipartimento

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

select

Impiegato.Nome,Cognome,

Dipartimento.Città

from

Impiegato,Dipartimento

where

Dipart = Dipartimento.Nome

La notazione punto ( ) serve per disambiguare

Suggerimento: “from”, “where”, target list

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] Dwhere Dipart = D.Nome

L’aliasing per le tabelle serve a disambiguare, ma non solo (vedremo…)

Sulla clausola “where”

Ammette come argomento un’espressione booleanaPredicati 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,Cognomefrom Impiegato

where Ufficio = 20 and Dipart =‘Amministr’

Nome Cognome

Giuseppe

Verdi

Interrogazione 7 e 8

select Nome, Cognomefrom Impiegatowhere Dipart=‘Prod’ or Dipart=‘Amministr’

select Nome, Cognomefrom Impiegatowhere Cognome=‘Rossi’ and(Dipart=‘Prod’ or Dipart=‘Amministr’)

1

2

1

Nome

Mario

2

Nome Cognome

Mario Rossi

Carlo Bianchi

Paola Burroni

Marco Franco

Giuseppe

Verdi

Operatore like

_ = carattere arbitrario% = stringa di lunghezza arbitraria (anche 0) di caratteri arbitrariEsempi: 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 Impiegatowhere 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 nulliAttributo con valore nullo = non applicabile a una certa tupla, o valore sconosciuto, o non si sa nullaSQL offre il predicato “is null”:

Attributo is [not] null

Gestione dei valori nulliStipendio>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 notazioneCi sono casi in cui una stessa tabella serve più di una voltaCaso speciale: quando si deve confrontare una tabella con se stessa

(il modello relazionale è relation-oriented, 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.Nomefrom Impiegato I1, Impiegato I2where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

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.Nomefrom Impiegato I1, Impiegato I2where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

per evitare output di tupla con se stessa

I2 usata per trovare tuple assoc. a ‘Prod’

Interrogazione 11

• Estrarre il nome e lo stipendio dei capi degliimpiegati che guadagnano più dei loro capi,date:

Impiegati(Matricola, Nome, Età, Stipendio)Supervisione(Capo, Impiegato)

dove Capo e Impiegato sono chiavi esterne diImpiegati (e.g., sono dei numeri di matricola)

Interrogazione 11 (sol.)select

I1.Nome, I1.Stipendiofrom

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.Stipendiofrom

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_hmfrom Tabella1 as T1,…,Tabellan as Tnwhere 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 aggregatiInterrogazioni nidificate

per ordinare le tuple

Operatori aggregati

Algebra relazionale: tutte le condizioni sono valutate su una tupla alla volta, in modo indipendenteSQL offre degli operatori che lavorano su più di una tupla alla volta:

count,sum,max,min,avg

Interrogazione 12select count(*)from Impiegatowhere Dipart = ‘Prod’

count(<*|[distinct|all|]ListaAttributi>)

valori diversi tra loro

non null