Enrico Cavalli - UniBG · Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo...
Transcript of Enrico Cavalli - UniBG · Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo...
Elaborazione Automatica dei Dati
ALGEBRA RELAZIONALE
Enrico Cavalli
Anno Accademico 2012-2013
Operazioni nel Modello Relazionale
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 3
Algebra Relazionale
• Le operazioni dell’algebra relazionale permettono di manipolare tabelle per estrarre informazioni. Un’operazione relazionale agisce su una o più tabelle e genera una nuova tabella
– Operazioni che estraggono righe o colonne di una tabella:
Selezione, Proiezione
– Operazioni che combinano righe di differenti tabelle:
Prodotto Cartesiano, Join
– Operazioni insiemistiche:
Unione, Intersezione, Differenza
– Operazioni che modificano il nome di una o più colonne: Ridenominazione
Proiezione e Selezione
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 5
Proiezione e Selezione
• Proiezione e Selezione agiscono “ortogonalmente” sulle tabelle
– Proiezione: estrae colonne da una tabella – Selezione: estrae righe da una tabella
Matricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38297 Gialli 56432 Neri 39824 Verdi 38
Matricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38297 Gialli 56432 Neri 39824 Verdi 38
Cognome Età
Rossi 37Bianchi 35
Rosa 38
Gialli 56
Neri 39Verdi 38
Cognome Età
Rossi 37Bianchi 35
Rosa 38
Gialli 56
Neri 39Verdi 38
Matricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38824 Verdi 38
Matricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38824 Verdi 38
ππππLR
R σP R
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 6
Proiezione (1)
πElencoAttributi R
Matricola Cognome Nome Facoltà
545 Rossi Maria Eco653 Neri Anna Eco768 Verdi Giuseppe Ing834 Rossi Maria Mat314 Cavour Benso Ling
Matricola Cognome Nome Facoltà
545 Rossi Maria Eco653 Neri Anna Eco768 Verdi Giuseppe Ing834 Rossi Maria Mat314 Cavour Benso Ling
Cognome Facoltà
Rossi EcoNeri Eco
Verdi IngRossi Mat
Cavour Ling
Cognome Facoltà
Rossi EcoNeri Eco
Verdi IngRossi Mat
Cavour Ling
Cognome
RossiNeri
VerdiCavour
Cognome
RossiNeri
VerdiCavour
πCognome, Facoltà R πCognome R
R
Una proiezione puòcambiare la cardinalità
di una relazione
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 7
Proiezione (2)
Matricola Cognome Nome Facoltà
545 Rossi Maria Eco653 Neri Anna Eco768 Verdi Giuseppe Ing834 Rossi Maria Mat314 Cavour Benso Ling
Matricola Cognome Nome Facoltà
545 Rossi Maria Eco653 Neri Anna Eco768 Verdi Giuseppe Ing834 Rossi Maria Mat314 Cavour Benso Ling
πNome,Cognome, Matricola Facoltà R
R
Con le proiezioni è possibile
modificare l’ordine delle colonne
πElencoAttributi R
Nome Cognome Matricola Facoltà
Maria Rossi 545 EcoAnna Neri 653 Eco
Giuseppe Verdi 768 IngMaria Rossi 834 MatBenso Cavour 314 Ling
Nome Cognome Matricola Facoltà
Maria Rossi 545 EcoAnna Neri 653 Eco
Giuseppe Verdi 768 IngMaria Rossi 834 MatBenso Cavour 314 Ling
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 8
Selezione
σCondizione di Selezione R
Matricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38297 Gialli 56432 Neri 39824 Verdi 38
Matricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38297 Gialli 56432 Neri 39824 Verdi 38
R σEtà < 39 RMatricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38824 Verdi 38
Matricola Cognome Età
274 Rossi 37357 Bianchi 35444 Rosa 38824 Verdi 38
Cognome Matricola
Rossi 274Bianchi 297
Rosa 432
Verdi 824
Cognome Matricola
Rossi 274Bianchi 297
Rosa 432
Verdi 824
πCognome, Matricola(σEtà < 39 R)
• Le operazioni possono essere combinate per costruire espressioni complesse
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 9
Osservazioni
• Cardinalità (Numerosità delle righe)
– Proiezione
– Selezione
• Grado (Numerosità delle colonne)
– Proiezione
– Selezione
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 10
Esercizi
Costruire le seguenti interrogazioni:
• In relazione alla tabella: Infrazioni ( Codice, Agente, Data, Articolo, Prov, Numero ), elencare le contravvenzioni elevate dall’agente con matricola 457. L’elenco conterrà Data, Articolo violato e targa dell’auto multata.
• In relazione alla tabella: Infrazioni ( Codice, Agente, Data, Articolo, Prov, Numero ), elencare le contravvenzioni elevate nei confronti dei milanesi nel corso del 2004. L’elenco conterrà Articolo violato e Agente che ha elevato la contravvenzione.
• Data la relazione: Studenti ( Matricola, Cognome, Nome, DataNascita, CodiceFiscale ), elencare gli studenti che hanno più di 23 anni (Si ipotizza che: DataNascita contenga il solo anno e che l’interrogazione venga effettuata nel 2011).
• Data la relazione: Prestiti ( NomeAgenzia, NumeroPrestito, NomeCliente, Importo ), elencare le agenzie che hanno concesso prestiti di importo superiore a 50000 euro.
• Data la relazione: Depositi ( NomeAgenzia, NumeroDeposito, NomeCliente, Importo ), elencare i clienti dell’agenzia “Città Alta” che hanno un deposito superiore a 100.000 euro.
• Data la relazione: Dettaglio ( Num, Qta, Descrizione, Importo ), che descrive le voci che compaiono nel conto di un ristorante, elencare le righe dove compaiono dei caffe’.
Prodotto Cartesiano e Join
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 12
Prodotto Cartesiano
R x S combina le righe di R con le righe di S in tutti i modi possibili
Impiegato Reparto
Rossi VenditeNeri Produzione
Bianchi Produzione
Impiegato Reparto
Rossi VenditeNeri Produzione
Bianchi Produzione
Reparto Capo
Produzione MoriVendite ChiariAcquisti Bruni
Reparto Capo
Produzione MoriVendite ChiariAcquisti Bruni
Impiegato R.Reparto S.Reparto Capo
Rossi Vendite Produzione MoriRossi Vendite Vendite ChiariRossi Vendite Acquisti BruniNeri Produzione Produzione MoriNeri Produzione Vendite ChiariNeri Produzione Acquisti Bruni
Bianchi Produzione Produzione MoriBianchi Produzione Vendite ChiariBianchi Produzione Acquisti Bruni
Impiegato R.Reparto S.Reparto Capo
Rossi Vendite Produzione MoriRossi Vendite Vendite ChiariRossi Vendite Acquisti BruniNeri Produzione Produzione MoriNeri Produzione Vendite ChiariNeri Produzione Acquisti Bruni
Bianchi Produzione Produzione MoriBianchi Produzione Vendite ChiariBianchi Produzione Acquisti Bruni
R x S
SR
Il Prodotto Cartesiano genera
tabelle prive di valore informativo
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 13
Theta Join (1)
R P S ≡ σP ( R x S )
Impiegato Reparto
Rossi Vendite
Neri Produzione
Bianchi Produzione
Verdi Ricerca
Impiegato Reparto
Rossi Vendite
Neri Produzione
Bianchi Produzione
Verdi Ricerca
Reparto Capo
Produzione MoriVendite ChiariAcquisti Bruni
Reparto Capo
Produzione MoriVendite ChiariAcquisti Bruni
SR
R R.Reparto = S.Reparto S
Impiegato R.Reparto S.Reparto Capo
Rossi Vendite Vendite ChiariNeri Produzione Produzione Mori
Bianchi Produzione Produzione Mori
Impiegato R.Reparto S.Reparto Capo
Rossi Vendite Vendite ChiariNeri Produzione Produzione Mori
Bianchi Produzione Produzione Mori
Un Theta Join dove P è l’uguaglianza
tra attributi di R ed S si chiama EQUI -
JOIN
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 14
Theta Join (2)
R P S ≡ σP ( R x S )
SR
R A < D S
A B C
1 2 3
6 7 8
9 7 8
B C D
2 3 4
2 3 5
7 8 10
A R.B R.C S.B S.C D
1 2 3 2 3 4
1 2 3 2 3 5
1 2 3 7 8 10
6 7 8 7 8 10
9 7 8 7 8 10
A R.B R.C S.B S.C D
1 2 3 2 3 4
6 7 8 2 3 4
9 7 8 2 3 4
1 2 3 2 3 5
6 7 8 2 3 5
9 7 8 2 3 5
1 2 3 7 8 10
6 7 8 7 8 10
9 7 8 7 8 10
R ×××× S
In un Theta Join può comparire qualsiasi tipo
di condizione
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 15
Equi Join
SR
R R.B = S.B AND R.C = S.C S
A B C
1 2 3
6 7 8
9 7 8
B C D
2 3 4
2 3 5
7 8 10 A R.B R.C S.B S.C D
1 2 3 2 3 4
6 7 8 2 3 4
9 7 8 2 3 4
1 2 3 2 3 5
6 7 8 2 3 5
9 7 8 2 3 5
1 2 3 7 8 10
6 7 8 7 8 10
9 7 8 7 8 10
R ×××× S
A R.B R.C S.B S.C D
1 2 3 2 3 4
1 2 3 2 3 5
9 7 8 7 8 10
6 7 8 7 8 10
R P S ≡ σP ( R x S ) Gli Equi Join sono la quasi totalità dei join
effettivamente utilizzati
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 16
Join Naturale (1)
R S
R S
SR A B C
1 2 3
6 7 8
9 7 8
B C D
2 3 4
2 3 5
7 8 10
A B C D
1 2 3 4
1 2 3 5
9 7 8 10
6 7 8 10
R R.B = S.B AND R.C = S.C SA R.B R.C S.B S.C D
1 2 3 2 3 4
1 2 3 2 3 5
9 7 8 7 8 10
6 7 8 7 8 10
Il Join Naturale è un Equi Join su tutti gli attributi comuni di R ed S, con eliminazione delle
colonne duplicate. Dal punto di vista informativo Equi Join e Join
Naturale si equivalgono
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 17
Impiegato Reparto
Rossi VenditeNeri Produzione
Bianchi Produzione
Impiegato Reparto
Rossi VenditeNeri Produzione
Bianchi Produzione
Join Naturale (2)
R S• Il Join Naturale tra R ed S è un Equi Join sull’insieme degli attributi
comuni di R ed S con una proiezione che elimina le colonne duplicate
Reparto Capo
Produzione MoriVendite ChiariAcquisti Bruni
Reparto Capo
Produzione MoriVendite ChiariAcquisti Bruni
SR
RImpiegato Reparto Capo
Rossi Vendite ChiariNeri Produzione Mori
Bianchi Produzione Mori
Impiegato Reparto Capo
Rossi Vendite ChiariNeri Produzione Mori
Bianchi Produzione Mori
Il Join Naturale (come l’Equi Join) tra R ed S estende
alcune righe di R con attributi prelevati da S.
Nell’estensione si perdonoinformazioni: vedi ultima riga
di R e di S
Impiegato Reparto
Rossi Vendite
Neri Produzione
Bianchi Produzione
Verdi Ricerca
Impiegato Reparto
Rossi Vendite
Neri Produzione
Bianchi Produzione
Verdi Ricerca
S
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 18
Osservazioni
• Se R ed S non hanno attributi con lo stesso nome la condizione di uguaglianza tra attributi è banalmente soddisfatta per ogni coppia di righe ed il Join Naturale degenera nel Prodotto Cartesiano
• Se R ed S hanno le medesime colonne il Join Naturale diventa l’intersezione: R ∩∩∩∩ S
• Se nel Join Naturale tra R ed S compaiono tutte le tuple di R e tutte le tuple di S esso si dice completo
• T =
– grado(T) ≤ grado(R) + grado(S)
– 0 ≤ cardinalità(T) ≤ cardinalità(R) x cardinalità(S)
R S
Altre operazioni
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 20
Operazioni insiemistiche (1)
• L’ Unione di due relazioni, R ∪∪∪∪ S, contiene le righe presenti in Roppure in S:– R ∪∪∪∪ S = { t | t ∈∈∈∈ R ∨∨∨∨ t ∈∈∈∈ S}
• L’Intersezione di due relazioni, R ∩∩∩∩ S, contiene le righe presenti sia in R che in S:
– R ∩∩∩∩ S = { t | t ∈∈∈∈ R ∧∧∧∧ t ∈∈∈∈ S}
• La differenza di due relazioni, R - S, contiene le righe di R che non sono presenti in S:
– R - S = { t | t ∈∈∈∈ R ∧∧∧∧ t ∉∉∉∉ S}
– R - S ≠≠≠≠ S - R
– R ∩∩∩∩ S = R - ( R - S )
Queste operazioni non sono illimitatamente possibili: le
tabelle devono poter essere componibili con operazioni
insiemistiche
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 21
Operazioni insiemistiche (2)
• Le due tabelle devono avere schemi con attributi identici e nel medesimo ordine. In altre parole: le righe delle tabelle devono essere omogenee, per contenuti, in modo che le operazioni abbiano senso
Matricola Cognome Età
274 Rossi 37432 Neri 39824 Verdi 38
Matricola Cognome Età
274 Rossi 37432 Neri 39824 Verdi 38
Matricola Cognome Età
297 Gialli 56432 Neri 39824 Verdi 38
Matricola Cognome Età
297 Gialli 56432 Neri 39824 Verdi 38
Matricola Cognome Età
274 Rossi 37297 Gialli 56432 Neri 39824 Verdi 38
Matricola Cognome Età
274 Rossi 37297 Gialli 56432 Neri 39824 Verdi 38
Matricola Cognome Età
432 Neri 39824 Verdi 38
Matricola Cognome Età
432 Neri 39824 Verdi 38
Matricola Cognome Età
274 Rossi 37
Matricola Cognome Età
274 Rossi 37
Laureati ∪∪∪∪ Dirigenti
DirigentiLaureati
Laureati ∩∩∩∩ Dirigenti
Laureati - Dirigenti
Matricola Cognome Età
297 Gialli 56
Matricola Cognome Età
297 Gialli 56
Dirigenti - Laureati
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 22
Ridenominazione
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Genitore Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Genitore Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Genitore Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Genitore Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
ρNew ���� Old,.., New ���� Old R
Paternità Maternità
T1 = ρGenitore ���� PadrePaternità T2 = ρGenitore ���� MadreMaternità
T1 e T2 possono essere manipolate con le operazioni
di unione, intersezione e
differenza
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 23
Esercizi
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
• Date le tabelle Maternità e Paternità:
Paternità Maternità
Costruire e interpretare le seguenti espressioni:
FA = πFiglio σPadre = “Adamo” Paternità
FE = πFiglio σMadre = “Eva” Maternità
FE – FA; FA – FE
FA ∩∩∩∩ FE; FA ∪∪∪∪ FE
Interrogazioni
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 25
Esempi di interrogazioni (1)
Studenti ( Matricola, Cognome, Nome, DataNascita )Esami ( Matricola, Voto, Data, CodiceCorso )Corsi ( CodiceCorso, NomeCorso, Docente )
1. Elencare gli esami in archivio con tutti i campi di Esami integrati con NomeCorso e Docente.
2. Elencare gli esami in archivio con tutti i campi di Esami integrati con Cognome, Nome e DataNascita dello studente.
Esami Corsi
Esami Studenti
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 26
Esempi di interrogazioni (2)
3. Elencare gli esami in archivio con tutti i campi di Esami integrati con NomeCorso, Docente e Cognome, Nome e DataNascita dello studente.
4. Elenco degli esami in archivio con tutti i campi di Esami integrati con Nome Corso e Docente, per i soli corsi che hanno come docente “Cavalli”.
5. Elencare gli esami in archivio con tutti i campi di Esami integrati con Cognome, Nome e DataNascita dello studente per studenti con numero di matricola superiore a 2500.
Studenti Esami Corsi
Esami Esami.CodiceCorso = Corsi.CodiceCorso (σ Docente = “Cavalli” Corsi)
Esami Esami.Matricola = Studenti.Matricola (σ Matricola > 2500 Studenti)
Studenti ( Matricola, Cognome, Nome, DataNascita )
Esami ( Matricola, Voto, Data, CodiceCorso )Corsi ( CodiceCorso, NomeCorso, Docente )
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 27
Esempi di interrogazioni (3)
6. Elencare gli studenti (con: Matricola, Cognome, e Nome ) che hanno superato esami nel corso del 2011
7. Elencare gli esami (con: Voto, Data e NomeCorso ) superati da Gianni Brambilla nel corso del 2012
πMatricola,Cognome,Nome ((σData>=1-1-2011 AND Data<.. AND Voto>17 Esami ) Studenti )
πVoto, Data, NomeCorso ( (σData >=1-1-2012 AND . . .AND Voto>=18 Esami ) Corsi
(σNome = “Gianni” AND Cognome = “Brambilla” Studenti ) )
Studenti ( Matricola, Cognome, Nome, DataNascita )
Esami ( Matricola, Voto, Data, CodiceCorso )Corsi ( CodiceCorso, NomeCorso, Docente )
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 28
Esempi di interrogazioni (4)
8. Elencare gli studenti (con Nome, Cognome, Matricola) che hanno superato uno o più esami con il professor Lorenzi con votazione superiore a 26
9. Elencare gli esami (con: Voto, Data e NomeCorso ) superati da Giuseppe Garibaldi
πNome, Cognome, Matricola ( ( σVoto>26Esami ) Studenti
(σDocente=“Lorenzi” Corsi ) )
πVoto, Data, NomeCorso ( ( σNome=“Giuseppe” AND Cognome = “Garibaldi” Studenti )
( σVoto>=18Esami) Corsi ) )
Studenti ( Matricola, Cognome, Nome, DataNascita )
Esami ( Matricola, Voto, Data, CodiceCorso )Corsi ( CodiceCorso, NomeCorso, Docente )
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 29
Esempi di interrogazioni (5)
10.Elencare i corsi che hanno come docente il professor Lucca
11.Elencare gli studenti che hanno superato uno o più esami con voto = 30
12.Elencare gli esami non superati (con NomeCorso, Matricola, Nome e Cognome dello studente )
πCodiceCorso, NomeCorso (σDocente = “Lucca” Corsi )
πMatricola,Cognome,Nome ( ( σVoto = 30 Esami ) Studenti )
πNomeCorso, Matricola, Cognome, Nome ( ( σVoto < 18 Esami ) Corsi Studenti )
Studenti ( Matricola, Cognome, Nome, DataNascita )Esami ( Matricola, Voto, Data, CodiceCorso )
Corsi ( CodiceCorso, NomeCorso, Docente )
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 30
Albero di espressione
πCognome, Matricola((σEtà < 39 R) (σSede = ‘Bergamo’S ))
ππππCognome, Matricola
σσσσEtà < 39 σσσσSede = ‘Bergamo’
R S
R ( Matricola, Cognome, Nome, Età )S ( Matricola, Sede )
Elencare i dipendenti minori di 39 anni che lavorano nella sede di Bergamo
Prima di scrivere un’espressione
relazionale può essere utile rappresentarla
mediante il suo albero di espressione
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 31
• Un modo comodo per costruire l’espressione relazionale consiste nel rappresentare l’interrogazione come un insieme di passi. Ad esempio:
R( Matricola, Cognome, Nome, Età )S( Matricola, Sede )
Elencare i dipendenti minori di 39 anni che lavorano a Bergamo
Rappresentazione “a passi”
πCognome, Matricola((σEtà < 39 R) (σSede = ‘Bergamo’S ))
1. σP R T1 = Selezione di R per Età < 39
2. σP S T2 = Selezione di S per Sede = ‘Bergamo’
3. T1 T2 T3 = Congiunzione di T1 e T2 su Matricola
4. πLT3 Proiezione di T3 su Cognome e Matricola
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 32
Esercizi (1)
• In relazione alle tabelle ( vedi diapositiva 13 ): R = ( Impiegato, Reparto )S = ( Reparto, Capo )
– Estrarre il nome del capo del reparto “Produzione”
– Elencare i dipendenti con nome del dipendente e nome del rispettivo capo:
• facendo uso del Prodotto Cartesiano e di quant’altro necessario
• facendo uso del Theta Join e di quant’altro …
• facendo uso del Join Naturale e di quant’altro …
– Elencare gli impiegati del reparto che ha Bianchi per capo– Elencare gli impiegati che non hanno capo
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 33
Esercizi (2)
• In riferimento alle tabelle Maternità e Paternità della diapositiva 22
– Elencare i genitori di: Caino, Abele, Isacco, Set, Ismaele
– Elencare i figli di Adamo ed Eva
– Elencare i figli di Adamo od Eva
– Elencare i figli di Eva di cui non è riportato il Padre
– Elencare i figli di Adamo di cui non è riportata la madre
I precedenti esercizi vanno svolti facendo uso delle operazioni insiemistiche
Join Esterni
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 35
Diverse forme di Join
• Consideriamo il Join Naturale tra Paternità e Maternità:
Padre Figlio MadreAdamo Caino EvaAbramo Isacco SaraAbramo Ismaele Agar
Padre Figlio MadreAdamo Caino EvaAbramo Isacco SaraAbramo Ismaele Agar
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Paternità Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Maternità
Nel Join Naturalecompaiono solo terne di
valori “bilanciate” per padre e madre
Le informazioni sulla maternità o paternità di alcuni figli sono perse
Paternità Maternità
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 36
Full Join Esterno
• FULL JOIN: include tutte le righe di entrambe le tabelle
Paternità FULL Maternità
Padre Figlio MadreAdamo Caino EvaAdamo AbeleAbramo Isacco SaraAbramo Ismaele Agar
Set Eva
Padre Figlio MadreAdamo Caino EvaAdamo AbeleAbramo Isacco SaraAbramo Ismaele Agar
Set Eva
Nel FULL JOIN sono considerate tutte le righe di Paternità e di Maternitàanche in assenza di valori
corrispondenti nell’altra tabella
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Paternità Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Maternità
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 37
Left Join Esterno
• LEFT JOIN : include tutte le righe della tabella a sinistra
Padre Figlio MadreAdamo Caino EvaAdamo AbeleAbramo Isacco SaraAbramo Ismaele Agar
Padre Figlio MadreAdamo Caino EvaAdamo AbeleAbramo Isacco SaraAbramo Ismaele Agar
Madre Figlio PadreEva Caino AdamoEva SetSara Isacco AbramoAgar Ismaele Abramo
Madre Figlio PadreEva Caino AdamoEva SetSara Isacco AbramoAgar Ismaele Abramo
I due LEFT JOIN differiscono per i
contenuti delle righe oltre che, ovviamente, per l’ordine delle colonne
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Paternità Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Maternità
Paternità LEFT Maternità Maternità LEFT Paternità
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 38
Right Join Esterno
Padre Figlio MadreAdamo Caino Eva
Set EvaAbramo Isacco SaraAbramo Ismaele Agar
Padre Figlio MadreAdamo Caino Eva
Set EvaAbramo Isacco SaraAbramo Ismaele Agar
• RIGHT JOIN: include tutte le righe della tabella di destra
Madre Figlio PadreEva Caino Adamo
Abele AdamoSara Isacco AbramoAgar Ismaele Abramo
Madre Figlio PadreEva Caino Adamo
Abele AdamoSara Isacco AbramoAgar Ismaele Abramo
Confrontando RIGHT JOIN con LEFT JOIN . . .
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Padre Figlio
Adamo CainoAdamo AbeleAbramo IsaccoAbramo Ismaele
Paternità Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Madre Figlio
Eva CainoEva SetSara IsaccoAgar Ismaele
Maternità
Paternità RIGHT Maternità Maternità RIGHT Paternità
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 39
Esempi (1)
• I Join INTERNI servono per collegare tabelle in base ai valori delle colonne. I Join ESTERNI servono per identificare situazioni di valori mancanti e violazioni di vincoli di integrità
– Elenco con i genitori di: Caino, Abele, Isacco, Set, Ismaele
– Elenco dei figli di Adamo ed Eva
– Elenco dei figli di Adamo od Eva
Paternità FULL Maternità
πFiglio ( σPadre = ‘Adamo’ AND Madre = ‘Eva’ ( Paternità Maternità ) )
πFiglio (σPadre = ‘Adamo’ OR Madre = ‘Eva’ ( Paternità FULL Maternità ) )
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 40
Esempi (2)
– Elenco di tutti i figli di Eva, anche se non è indicato il padre
– Elenco dei figli di Eva per i quali non è indicato il padre
– Elenco di tutti i figli di Adamo, anche se non è indicata la madre
– Elenco dei figli di Adamo per i quali non è indicata la madre
πFiglio (σMadre = ‘Eva’ Maternità )
πFiglio (σPadre = ‘Adamo’ Paternità )
πFiglio (σMadre = ‘Eva’ AND Padre Null ( Paternità RIGHT Maternità ) )
πFiglio (σPadre = ‘Adamo’ AND Madre Null ( Paternità LEFT Maternità ) )
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 41
Ricerca di dati non corrispondenti (1)
• I dati senza corrispondenti possono essere riconosciuti ricercando valori nulli in opportuni Join Esterni. Ad esempio: – Anagrafica ( Nome, Cognome , Dipart, Ufficio, Stipendio, Città )
– Dipartimenti ( Dipart , Indirizzo, Città )
Nome Cognome Dipart Ufficio Stipendio Città
Carlo Bianchi Produzione 20 36 TorinoCarlo Rossi Direzione 80 MilanoFranco Neri Distribuzione 45 NapoliGiuseppe Verdi 20 40 RomaLorenzo Lanzi Direzione 7 73 GenovaMarco Franco Produzione 20 46 RomaMario Rossi Amministrazione 10 45 MilanoPaola Borroni Marketing 75 40 Venezia
Giuseppe Verdi non ha dipar-timento; Marketing è inesistente;
il dipartimento Ricercanon ha dipendenti
Dipart Indirizzo Città
Amministrazione Via Tito Livio, 27 MilanoDirezione Via Tito Livio, 27 MilanoDistribuzione Via Segre, 9 RomaProduzione Piazza Lavater, 3 TorinoRicerca Via Morone, 6 Milano
Dipart Indirizzo Città
Amministrazione Via Tito Livio, 27 MilanoDirezione Via Tito Livio, 27 MilanoDistribuzione Via Segre, 9 RomaProduzione Piazza Lavater, 3 TorinoRicerca Via Morone, 6 Milano
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 42
Ricerca di dati non corrispondenti (2)
Per riconoscere cheGiuseppe Verdi non ha dipar-
timento, non è necessario ricorrere al JOIN ESTERNO
πNome, Cognome (σD.Dipart Null ( Anagrafica LEFT Dipartimenti ) )
πDipartimenti.Dipart (σCognome Null ( Anagrafica RIGHT Dipartimenti ) )
– Anagrafica ( Nome, Cognome , Dipart, Ufficio, Stipendio, Città )
– Dipartimenti ( Dipart , Indirizzo, Città )
• Dipendenti di un dipartimento inesistente o senza dipartimento
• Dipartimenti senza impiegati
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 43
Esercizi (1)
Date le tabelle: Impiegati ( Matricola, Nome, Età, Stipendio )Supervisione ( Capo, Impiegato )
Matricola Nome Età Stipendio
101 M.Rossi 34 40103 M.Bianchi 23 35104 L.Neri 38 61105 N.Bini 44 38210 M.Celli 49 60231 S.Bisi 50 60252 N.Bini 44 70301 S.Rossi 34 70375 M.Rossi 50 65
Matricola Nome Età Stipendio
101 M.Rossi 34 40103 M.Bianchi 23 35104 L.Neri 38 61105 N.Bini 44 38210 M.Celli 49 60231 S.Bisi 50 60252 N.Bini 44 70301 S.Rossi 34 70375 M.Rossi 50 65
Capo Impiegato
210 101210 103210 104231 105301 210301 231375 252
Capo Impiegato
210 101210 103210 104231 105301 210301 231375 252
1. Nome, Matricola, Età dei dipendenti con stipendio > 40
2. Matricola dei capi degli impiegati che hanno stipendio > 40
3. Nome e Stipendio dei capi degli impiegati che hanno stipendio > 40
4. L’elenco degli impiegati che guadagnano più del proprio capo, mostrando Matricola, Nome e Stipendio di ogni impiegato e del capo
5. Chi sono i dipendenti che non hanno capo, con Nome e Matricola
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 44
Esercizi (2)
• Considerando la base di dati definita dallo schema:
Film ( Titolo, Anno, Lunghezza, aColori, NomeStudio )
Attori ( Titolo, Anno, NomeAttore )
• Costruire le interrogazioni per ottenere:
– Titolo ed Anno dei film della Fox lunghi almeno 100 minuti
– L’elenco degli attori del film: Moulin Rouge del 2002
– L’elenco degli studi che hanno prodotto film con Nicole Kidman
– I protagonisti dei film che durano almeno 100 minuti– Elenco dei film a colori prodotti nel 2010
– Elenco dei film per i quali non si hanno informazioni sugli attori
– Elenco dei film con Marlon Brando posteriori al 1980
– Elenco degli attori che compaiono in Attori in relazione a Film per i quali non si hanno ulteriori informazioni
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 45
Valori nulli
• I Join Esterni sono una possibile sorgente di valori nulli
• Operando con valori nulli si possono ottenere risultati inattesi per effetto delle seguenti regole:
– Un’operazione aritmetica che coinvolge un valore NULL ha come risultato NULL
– Un confronto con NULL ha come risultato UNKNOWN
– UNKNOWN è un valore logico intermedio tra TRUE e FALSE
• Ne segue che, se X vale NULL:
– X - X � NULL invece di 0
– X × 0 � NULL invece di 0
– X ≥ X � Unknown invece di TRUE
Elaborazione automatica dei dati - Enrico Cavalli -Università di Bergamo 46
Il valore logico UNKNOWN
• Il valore di verità di espressioni logiche nelle quali compare UNKNOWsi costruisce con la tabella:
A B A AND B A OR B NOT A
True True True True False
True Unknown Unknown True False
True False False True False
Unknown True Unknown True UnknownUnknown Unknown Unknown Unknown Unknown
Unknown False False Unknown Unknown
False True False True True
False Unknown False Unknown TrueFalse False False False True
A B A AND B A OR B NOT A
True True True True False
True Unknown Unknown True False
True False False True False
Unknown True Unknown True UnknownUnknown Unknown Unknown Unknown Unknown
Unknown False False Unknown Unknown
False True False True True
False Unknown False Unknown TrueFalse False False False True
• Se uno dei termini in AND vale False il risultato è False• Se uno dei termini in OR vale True il risultato è True• In tutti gli altri casi la presenza di UNKNOW genera un risultato UNKNOWN