algebra relazionale (iii)
DESCRIPTION
Algebra relazionale (III). Esempi di Interrogazioni: 3. Trovare matricola e nome dei capi i cui impiegati guadagnano tutti più di 2.000 Tutti gli impiegati? Selezionare capi che hanno impiegati con stipendio < 2.000 Capo (Supervisione Matr=Impiegato ( StipTRANSCRIPT
![Page 1: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/1.jpg)
Algebra relazionale (III)
![Page 2: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/2.jpg)
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)))
![Page 3: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/3.jpg)
Esempi di Interrogazioni: 3
2. Sottrarre tali capi all’insieme di tutti i capi
Capo(Supervisione) –
Capo(Supervisione Matr=Impiegato(Stip<2.000(Impiegati)))
![Page 4: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/4.jpg)
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)))))
![Page 5: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/5.jpg)
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
![Page 6: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/6.jpg)
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
![Page 7: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/7.jpg)
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
![Page 8: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/8.jpg)
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
![Page 9: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/9.jpg)
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)
![Page 10: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/10.jpg)
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)
![Page 11: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/11.jpg)
Viste
DBMS offrono solo relazioni virtuali (no ridondanza dati)Interrogazioni che utilizzano viste sono risolte sostituendo la definizione delle viste alle loro occorrenze
![Page 12: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/12.jpg)
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)
![Page 13: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/13.jpg)
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
![Page 14: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/14.jpg)
SQL
Interrogazioni
![Page 15: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/15.jpg)
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à)
![Page 16: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/16.jpg)
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
![Page 17: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/17.jpg)
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)
![Page 18: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/18.jpg)
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”)
![Page 19: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/19.jpg)
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”)
![Page 20: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/20.jpg)
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
![Page 21: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/21.jpg)
Interrogazione 1
select Stipendio as Salario
from Impiegato
where Cognome = ‘Rossi’
Salario
15
27
![Page 22: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/22.jpg)
Interrogazione 1
select Stipendio as Salario
from Impiegato
where Cognome = ‘Rossi’
Salario
15
27
alias
![Page 23: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/23.jpg)
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
![Page 24: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/24.jpg)
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
![Page 25: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/25.jpg)
Interrogazione 3
select Stipendio/12 as StipMens
from Impiegato
where Cognome = ‘Bianchi’
StipMens
1
![Page 26: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/26.jpg)
Interrogazione 3
select Stipendio/12 as StipMens
from Impiegato
where Cognome = ‘Bianchi’
espressioni
StipMens
1
![Page 27: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/27.jpg)
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
![Page 28: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/28.jpg)
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
![Page 29: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/29.jpg)
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
![Page 30: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/30.jpg)
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
![Page 31: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/31.jpg)
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
![Page 32: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/32.jpg)
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…)
![Page 33: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/33.jpg)
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
![Page 34: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/34.jpg)
Interrogazione 6
select Nome,Cognomefrom Impiegato
where Ufficio = 20 and Dipart =‘Amministr’
Nome Cognome
Giuseppe
Verdi
![Page 35: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/35.jpg)
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
![Page 36: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/36.jpg)
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)
![Page 37: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/37.jpg)
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
![Page 38: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/38.jpg)
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
![Page 39: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/39.jpg)
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)
![Page 40: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/40.jpg)
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 )
![Page 41: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/41.jpg)
Interrogazione 10• Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione
![Page 42: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/42.jpg)
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’
![Page 43: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/43.jpg)
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’
![Page 44: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/44.jpg)
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)
![Page 45: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/45.jpg)
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
![Page 46: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/46.jpg)
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
![Page 47: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/47.jpg)
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))
![Page 48: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/48.jpg)
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
![Page 49: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/49.jpg)
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
![Page 50: Algebra relazionale (III)](https://reader035.vdocumenti.com/reader035/viewer/2022062222/56815025550346895dbe0e38/html5/thumbnails/50.jpg)
Interrogazione 12select count(*)from Impiegatowhere Dipart = ‘Prod’
count(<*|[distinct|all|]ListaAttributi>)
valori diversi tra loro
non null