3 algebra relazionale - di.unito.it · Algebra relazionale 2 Accesso ai dati di un DB Aggiornamento...

Algebra relazionale

2

Accesso ai dati di un DB

Aggiornamento del DB: funzione che, data un’istanza del DB, produce un’altra istanza del DB, sullo stesso schema

� Modifica, aggiunta, rimozione tuple

Interrogazione a DB: funzione che, dato un DB, produce una relazione su un dato schema (non necessariamente uno degli schemi definiti nel DB)

3

Accesso ai dati di un DB

Aggiornamento e interrogazione vengono effettuati usando specifici linguaggi

� Per esempio: algebra relazionale

4

Algebra relazionale

Linguaggio procedurale di accesso a DB

� Si specificano operazioni complesse descrivendo il procedimento da usare per ottenere la soluzione

� Interrogazioni : espressioni complesse

5

Algebra relazionale

Algebra relazionale: basata su un insieme di operatori� Definiti su relazioni

� Producono relazioni come risultati

Operatori� Insiemistici: unione, intersezione, differenza

� Specifici: ridenominazione, selezione, proiezione, join

6

Operatori insiemistici

Relazioni: insiemi di tuple omogenee, cioèdefinite sugli stessi attributi

Insiemi: ha senso usare operatori insiemistici

Insiemi di tuple omogenee: usare operatori insiemistici solo su relazioni definite sugli stessi attributi� Altrimenti, si ottengono insiemi di tuple disomogenee, che non rappresentano relazioni

7

Unione di relazioni

Date due relazioni r1(X) e r2(X) definite sullo stesso insieme di attributi X

r1∪r2: relazione su X che contiene tuple appartenenti a r1 oppure a r2 oppure a entrambe

8

Unione di relazioni

38Verdi9824

39Neri7432

56Neri9297

EtàCognomeMatricola

38Verdi9824

39Neri7432

37Rossi7274


56Neri9297

38Verdi9824

39Neri7432

37Rossi7274


Laureati Dirigenti

Laureati ∪ Dirigenti

9

Differenza di relazioni


r1-r2: relazione su X che contiene tuple appartenenti a r1 ma non a r2

10

Differenza di relazioni

38Verdi9824

39Neri7432

56Neri9297


38Verdi9824

39Neri7432

37Rossi7274


37Rossi7274


Laureati Dirigenti

Laureati - Dirigenti

11

Intersezione di relazioni


r1∩r2: relazione su X che contiene tuple appartenenti sia a r1 sia a r2

12

Intersezione di relazioni

38Verdi9824

39Neri7432

56Neri9297


38Verdi9824

39Neri7432

37Rossi7274


38Verdi9824

39Neri7432


Laureati Dirigenti

Laureati ∩ Dirigenti

13

Operatori di manipolazione di relazioni

Specifici dell’algebra relazionale (non derivano dalle teoria degli insiemi)

Permettono di manipolare relazioni per creare nuove relazioni� Ridenominazione

� Selezione

� Proiezione

� Join

14

Ridenominazione di relazioni

Cambia il nome di un attributo di relazione lasciandone inalterata l’istanza (modifica solo intestazione di relazione)

Utile per applicare operatori insiemistici a relazioni con attributi di nome diverso

15


Per esempio:

IsmaeleAbramo

IsaccoAbramo

AbeleAdamo

CainoAdamo

FiglioPadre

IsmaeleAgar

IsaccoSara

SetEva

CainoEva

FiglioMadre

Paternità Maternità

Paternità ∪ Maternità ?16


Una ridenominazione:

IsmaeleAbramo

IsaccoAbramo

AbeleAdamo

CainoAdamo

FiglioPadre

IsmaeleAbramo

IsaccoAbramo

AbeleAdamo

CainoAdamo

FiglioGenitore

Paternità ρGenitore←Padre(Paternità)

17


Un’altra ridenominazione:

IsmaeleAgar

IsaccoSara

SetEva

CainoEva

FiglioMadre

IsmaeleAgar

IsaccoSara

SetEva

CainoEva

FiglioGenitore

Maternità ρGenitore←Madre(Maternità)

18


CainoAdamo

AbeleAdamo

IsaccoAbramo

IsmaeleAbramo

IsmaeleAgar

IsaccoSara

SetEva

CainoEva

FiglioGenitore

ρGenitore←Padre(Paternità) ∪ ρGenitore←Madre(Maternità)

Corretta applicazionedi unione direlazioni

19


Monza

Latina

Milano

Roma

Sede

45Rossi

53Neri

33Verdi

32Bruni

RetribuzioneCognome

Milano

Roma

Agenzia

45Rossi

53Neri

StipendioCognome

Monza

Latina

Fabbrica

33Verdi

32Bruni

SalarioCognome

Impiegati Operai

ρSede,Retribuzione←Agenzia,Stipendio(Impiegati)

∪ρSede,Retribuzione←Fabbrica,Salario(Operai)

20


Sia r(X) lo schema di una relazione r definita sull’insieme X={A1,…,Ak}

… e sia Y un insieme di attributi Y={B1,…,Bk}

La ridenominazione ρB1,..,Bk←A1,…,Ak(r) contiene una tupla t’ per ogni tupla t in r, cosìdefinita:� t’ è una tupla su Y, e

� t’[Bi]=t[Ai] per 1≤i≤k

21


Cambiano solo i nomi degli attributi, i valori rimangono inalterati

Di solito, si indicano nelle ridenominazione solo gli attributi ridenominati (quelli per cui Ai ≠ Bi)

ρGenitore←Padre(Paternità): l’attributo Figlio viene omesso

22


Che cos’è Studenti ∪ ρMatricola←Numero(Docenti)?

56

32

Matricola

Rossi

Neri

CognomeStudenti

23

78

Numero

Bianchi

Neri

CognomeDocenti

23

Selezione

Data una relazione r su un insieme di attributi X, la selezione σF(r) produce una relazione sugli attributi X che contiene tuple di r che soddisfano la condizione di selezione F

A B C

σF(r)

Selezione secondo F

A B C

24

Selezione

F e’ una formula proposizionale: condizione di selezione formata da

� Operatori booleani: � ∧ (AND),

� ∨ (OR),

� ¬ (NOT)

25

Selezione

F e’ una formula proposizionale: condizione di selezione formata da� Operatori booleani

� Condizione atomiche: termini che possono contenere� Confronti fra attributi (per esempio, Stipendio>Tasse, dove Stipendio e Tasse sono attributi)

� Confronti fra attributi e costanti (per esempio, Età ≤ 60, dove Età è un attributo)

26

Selezione

1.900,0040MarcoRossi

2.250,0036NicoVerdi

1.500,0040LucaNeri

1.000,0025MarioRossi

StipendioEtàNomeCognomeImpiegati

2.250,0036NicoVerdi

StipendioEtàNomeCognome

σEtà>30∧Stipendio>2.000,00(Impiegati)

27

Selezione


2.250,0036NicoVerdi

1.500,0040LucaNeri



Che cos’è σEtà>30(Impiegati)?

28

Selezione


2.250,0036NicoVerdi

1.500,0040LucaNeri



Che cos’è

σCognome=“Rossi” ∨ (¬ Nome=“Nico”)(Impiegati) ?

29

Proiezione

Dati una relazione r su un insieme di attributi X e un sottoinsieme Y di X, la proiezione πY(r) è l’insieme di tuple su Y ottenute dalle tuple di r considerando solo i valori su Y

πY(r) = {t[Y] | t ∈ r}

A B C

πY(r)

Proiezione su Y

A B

30

Proiezione

Per esempio:� Impiegati(Cognome,Nome,Reparto,Capo)

� πCognome,Nome(Impiegati) ha attributi Cognome, Nome

Ricordiamo che le relazioni sono insiemi, quindi non contengono tuple ripetute:� Tuple uguali collassano in una sola

31

Proiezione

BaldiPersonaleMarcoRossi

BaldiPersonaleNicoVerdi

De RossiVenditeLucaNeri

De RossiVenditeMarioRossi

CapoRepartoNomeCognome

BaldiPersonale

De RossiVendite

CapoReparto

Impiegati

πReparto,Capo(Impiegati)

32

Proiezione





CapoRepartoNomeCognomeImpiegati

Che cos’è πCognome, Nome(Impiegati)?

33

Proiezione





CapoRepartoNomeCognomeImpiegati

Che cos’è πCapo(Impiegati)?

34

Tuple di una proiezione

Una proiezione πY(r) contiene al più tante tuple quante ne contiene r

Se Y è una superchiave di r, allora πY(r) contiene tante tuple quante r� Questo può accadere comunque anche se Y non èla chiave primaria (basta che le tuple su Y siano “casualmente” tutte diverse tra loro)

Viceversa: se πY(r) contiene tante tuple quante r, allora Y è una superchiave di r

35

Join

Operatore che permette di correlare dati contenuti in relazioni diverse confrontando i valori delle tuple (modello relazionale basato su valori)

Join Naturale

Theta-Join

36

Join Naturale

Permette di correlare dati contenuti in relazioni diverse confrontando attributi con lo stesso nome

Produce una relazione definita sull’unione di insiemi di attributi delle due relazioni su cui opera

Tuple del risultato: ottenute combinando le tuple dei due operandi con valore uguale su attributi comuni

37

Join Naturale

Date r1(X) e r2(Y), r1><r2 è una relazione su X∪Y:

r1><r2={t su X∪Y | t[X]∈r1 e t[Y]∈r2}

38

Join Naturale

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

De Rossi

Mori

Capo

Produzione

Vendite

Reparto

Rel1 Rel2

Rel1(Impiegato,Reparto) Rel2(Reparto,Capo)

X Y

39

Join Naturale

Produzione

Produzione

Vendite

Reparto

De RossiRossi

MoriNeri

MoriBianchi

CapoImpiegato

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

De Rossi

Mori

Capo

Produzione

Vendite

Reparto

Rel1 Rel2

Rel1 >< Rel2

40

Join Naturale

Se le relazioni da combinare sono definite sullo stesso insieme di attributi, r1(X), r2(X)

… r1><r2 = r1∩r2

� Cioè il join coincide con l’intersezione: combina le tuple con gli stessi valori di attributi su r1 e r2

41

Join Naturale

Produzione

Vendite

Reparto

De Rossi

Mori

Capo

MoriProduzione

De RossiVendite

Baldi

Capo

Acquisti

Reparto

TilliContabilità

De Rossi

Mori

Capo

Produzione

Vendite

Reparto

Rel1 Rel2

Rel1 >< Rel2

42

Join Naturale

Se le relazioni da combinare sono definite su insiemi di attributi disgiunti, r1(X), r2(Y) con X∩Y=∅… la condizione di corrispondenza tra tuple è sempre vera

… r1><r2=r1xr2 (prodotto cartesiano)

Combina tutte le tuple di r1 con tutte quelle di r2

43

Join Naturale

P123MoriMoriProduzione

R124BaldiBaldiAcquisti

Bianchi

Mori

Impiegato

R124

P123

Progetto

Mori

Baldi

Capo

Produzione

Acquisti

Reparto

MoriProduzione

Baldi

Capo

Acquisti

Reparto

R124

P123

Progetto

Mori

Baldi

Impiegato

Rel1 Rel2

Rel1 >< Rel2

44

Join Naturale

Numero degli attributi di r1><r2 ≤ somma dei numeri degli attributi di r1 e r2

Spesso il join viene fatto sulla chiave di una relazione:� Infrazioni(Codice,Data,Agent,Art,Prov,Numero) Auto(Prov,Numero,Proprietario,Indirizzo)

Spesso viene imposto il vincolo di integritàreferenziale tra attributi di join (per evitare che r1 faccia riferimento a valori inesistenti in r2)

45

Join Naturale

v. AceriBini Luca4E5432RM

v. TigliVerdi Piero1A2396RM

Luci Gino

Verdi Piero

Proprietario

v. Aceri

v. Tigli

Indirizzo

2F7643

2F7643

Numero

MI

RM

Prov

Auto

2F7643MI5345615/10/03630876

539856

987557

987554

143256

Codice

12/10/03

26/10/03

26/10/03

25/10/03

Data

2F7643RM34456

4E5432RM34456

MI

RM

Prov

2F7643

4E5432

Numero

44

44

Art

567

567

Agente

Infrazioni

46

Join Naturale

2F7643

2F7643

2F7643

4E5432

4E5432

Numero

Luci Gino

Luci Gino

Verdi Piero

Bini Luca

Bini Luca

Proprietario

v. Aceri

v. Aceri

v. Tigli

v. Aceri

v. Aceri

Indirizzo

MI5345615/10/03630876

539856

987557

987554

143256

Codice

12/10/03

26/10/03

26/10/03

25/10/03

Data

RM34456

RM34456

MI

RM

Prov

44

44

Art

567

567

Agente

Infrazioni >< Auto

• Tra Infrazioni e Auto esiste un vincolo di integrità referenziale• {Prov,Numero} è chiave di Auto• Quindi, ogni tupla di Infrazioni è combinata esattamente conuna tupla di Auto

47

Join Naturale

IsmaeleAbramo

IsaccoAbramo

AbeleAdamo

CainoAdamo

FiglioPadre

Paternità

IsmaeleAgar

IsaccoSara

AbeleEva

CainoEva

FiglioMadre

Maternità

Ismaele

Isacco

Abele

Caino

Figlio

Agar

Sara

Eva

Eva

Madre

Abramo

Abramo

Adamo

Adamo

Padre

Paternità >< Maternità

48

Join completi

Date r1(X) e r2(Y), r1><r2 è completo se e solo se, per ogni tupla t1 di r1 esiste tupla t in r1><r2 tale che t[X]=t1 e analogamente per r2

49

Join completi

Cardinalità di un insieme: il numero di elementi che appartengono all’insieme

� Cardinalità di A: scritto |A|

Per esempio: |{a,b,d}|=3, |∅|=0

50

Join completi

Cardinalità di join:

� Se r1><r2 è completo:

max(|r1|,|r2|) ≤ |r1><r2| ≤ |r1|x|r2|

51

Join completi

MoriP124Bianchi

BruniP124Rossi

BruniP124Neri

BruniP124Bianchi

P124

P124

Progetto

MoriRossi

MoriNeri

CapoImpiegato

P124Neri

P124Bianchi

P124

Progetto

Rossi

Impiegato

BruniP124

Mori

Capo

P124

Progetto

Rel1 Rel2

Rel1 >< Rel2

Un esempio di join con |r1|x|r2| tuple

52

Join incompleti

Join incompleti lasciano tuple senza corrispondenza, dette dangling tuples

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

BruniAcquisti

Mori

Capo

Produzione

Reparto

Rel1 Rel2

Produzione

Produzione

Reparto

Mori

Mori

Capo

Bianchi

Neri

ImpiegatoRel1 >< Rel2

53

Join incompletiCaso limite (solo tuple dangling): se non ci sono tuple combinabili, il risultato del join è la relazione vuota(senza tuple)

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

BruniAcquisti

Mori

Capo

Acquisti

Reparto

Rel1 Rel2

Reparto CapoImpiegatoRel1 >< Rel2

54

Cardinalità di join

Date r1(X) e r2(Y):

0 ≤ |r1><r2| ≤ |r1|x|r2|

Se r1 ><r2 completo:

|r1><r2| ≥ max(|r1|,|r2|)

Ogni tupla di r1 viene combinata con almeno 1 tupla di r2 e viceversa

55


Se X∩Y contiene una chiave per r2

|r1><r2| ≤ |r1|

Ogni tupla di r1 viene combinata con al più una tupla di r2

56


Se X∩Y contiene una chiave per r2 e c’èun vincolo di integrità referenziale fra X∩Y in r1 e la chiave di r2

|r1><r2| = |r1|

Ogni tupla di r1 viene combinata con esattamente una tupla di r2

57

Join esterni

Per combinare sempre le tuple di due relazioni, anche quando non ci sono corrispondenze tra i valori degli attributi comuni, si inseriscono dei valori NULL in assenza di controparti

Non tralasciano tuple di operandi nel risultato

58

Join esterni

Join esterno sinistro: estende solo le tuple del primo operando� Aggiunge tuple di r1 senza corrispettivo in r2

Join esterno destro:estende solo le tuple del secondo operando� Aggiunge tuple di r2 senza corrispettivo in r1

Join esterno completo: estende tuple di entrambi gli operandi� Bilaterale

59

Join esterni

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

BruniAcquisti

Mori

Capo

Produzione

Reparto

Rel1 Rel2

MoriProduzioneNeri

MoriProduzioneBianchi

Vendite

Reparto

NULL

Capo

Rossi

ImpiegatoRel1 ><LEFT Rel2

60

Join esterni

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

BruniAcquisti

Mori

Capo

Produzione

Reparto

Rel1 Rel2


BruniAcquistiNULL

MoriProduzioneNeri

Reparto CapoImpiegatoRel1 ><RIGHT Rel2

61

Join esterni

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

BruniAcquisti

Mori

Capo

Produzione

Reparto

Rel1 Rel2


BruniAcquistiNULL

MoriProduzioneNeri

Vendite

Reparto

NULL

Capo

Rossi

ImpiegatoRel1 ><FULL Rel2

62

Theta-Join

Serve per fare Join su relazioni senza attributi omonimi

Ricordiamo che il Join naturale su insiemi di attributi disgiunti degenera nel prodottocartesiano delle relazioni

Il Theta-Join e’ un operatore derivato: si ottiene come prodotto cartesiano seguito dalla selezione delle tuple che verificano una formula proposizionale F tra valori di attributi

r1 ><F r2 = σF(r1 >< r2)

63

Theta-Join

ProduzioneNeri

ProduzioneBianchi

Vendite

Reparto

Rossi

Impiegato

BruniVendite

BaldiAcquisti

Mori

Capo

Produzione

Divisione

Rel1 Rel2

Produzione

Produzione

Vendite

Reparto

Produzione

Produzione

Vendite

Divisione

MoriBianchi

MoriNeri

Bruni

Capo

Rossi

Impiegato

σReparto=Divisione(Rel1 >< Rel2)

64

Theta-Join ed Equi-Join

Theta-Join: r1 ><F r2 = σF(r1 >< r2)

La condizione di selezione F è una formula proposizionale come descritto per l’operatore di selezione

Se F è congiunzione di uguaglianze tra attributi di r1 e attributi di r2, il theta-join viene detto equi-join

65


Per esempio:

� Rel1(Impiegato,Reparto), Rel2(Divisione,Capo)

σReparto=Divisione(Rel1 >< Rel2)

� Infrazioni(Codice,Data,Ag,Art,Prov,Num), Auto(Provincia,Targa,Prop,Indirizzo)

σProv=Provincia ∧ Num=Targa(Infrazioni ><Auto)

66


Theta-join e equi-join sono più utili del join naturale

� Permettono di operare su relazioni senza attributi in comune

� Join naturale viene simulato mediante ridenominazione, equi-join e proiezione

67


Per esempio: R1(A,B,C), R2(B,C,D)

R1><R2 =

πA,B,C,D(R1><B=B’∧C=C’(ρB’,C’←B,C(R2)))

68



R1><R2 =


Join naturale Equi-join

69



R1><R2 =


Si ridenomina R2 affinchè abbia attributi diversi da quelli di R1

70



………

DC’B’

R1 ρB’,C’←B,C(R2)

………

CBA

71



R1><R2 =


Equi-join tra R1 e R2 per selezionare tuple in corrispondenza

72



………

DC’B’

R1 ρB’,C’←B,C (R2)

………

CBA

R1><B=B’∧C=C’(ρB’,C’←B,C(R2))…

C

…

B’

…

C’

………

DBA

73



R1><R2 =


Proiezione del risultato per eliminare attributi ridondanti

74



………

DC’B’

R1 ρB’,C’←B,C (R2)

………

CBA

R1><B=B’∧C=C’(ρB’,C’←B,C(R2))…

C

…

B’

…

C’

………

DBA

πA,B,C,D(R1><B=B’∧C=C’(ρB’,C’←B,C(R2)))…

C

………

DBA

75

Esercizi

Che cos’è Studenti∪Lavoratori?

Reale

Neri

Bruni

Cognome

Carla456123

Dario654321

Andrea123456

NomeMatricola

Reale

Neri

Bianco

Cognome

Carla456123

Dario654321

Giovanni321654

NomeMatricola

Studenti

Lavoratori

76

Esercizi

Che cos’è Studenti∩Lavoratori?

Reale

Neri

Bruni

Cognome

Carla456123

Dario654321

Andrea123456

NomeMatricola

Reale

Neri

Bianco

Cognome

Carla456123

Dario654321

Giovanni321654

NomeMatricola

Studenti

Lavoratori

77

Esercizi

Che cos’è Studenti - Lavoratori?

Reale

Neri

Bruni

Cognome

Carla456123

Dario654321

Andrea123456

NomeMatricola

Reale

Neri

Bianco

Cognome

Carla456123

Dario654321

Giovanni321654

NomeMatricola

Studenti

Lavoratori

78

Esercizi

Che cos’è ρNumero←Matricola(Studenti)?

Reale

Neri

Bruni

Cognome

Carla456123

Dario654321

Andrea123456

NomeMatricolaStudenti

79

Esercizi

Che cos’è σVoto>25(Studenti)?

Carla

Dario

Dario

Nome

20

23

21

Età

22

29

29

Voto

Reale

Neri

Bruni

CognomeStudenti

80

Esercizi

Che cos’è σVoto>25 ∧ Eta<23(Studenti)?

Carla

Dario

Dario

Nome

20

23

21

Età

22

29

29

Voto

Reale

Neri

Bruni

CognomeStudenti

81

Esercizi

Che cos’è σVoto>25 ∨ Eta<23(Studenti)?

Carla

Dario

Dario

Nome

20

23

21

Età

22

29

29

Voto

Reale

Neri

Bruni

CognomeStudenti

82

Esercizi

Che cos’è πCognome,Nome(Studenti)?

Carla

Dario

Dario

Nome

20

23

21

Età

22

29

29

Voto

Reale

Neri

Bruni

CognomeStudenti

83

Esercizi

Che cos’è πVoto(Studenti)?

Carla

Dario

Dario

Nome

20

23

21

Età

22

29

29

Voto

Reale

Neri

Bruni

CognomeStudenti

84

Esercizi

Che cos’è πNome,Voto(Studenti)?

Carla

Dario

Dario

Nome

20

23

21

Età

22

29

29

Voto

Reale

Neri

Bruni

CognomeStudenti

85

Esercizi

Che cos’è πCognome,Nome,Età,Voto(Studenti)?

Carla

Dario

Dario

Nome

20

23

21

Età

22

29

29

Voto

Reale

Neri

Bruni

CognomeStudenti

86

Esercizi

Che cos’è Studenti >< Esami ?

456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia123456

456123

654321

123456

Matricola

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

87

Esercizi


456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia123456

456123

654321

123456

Numero

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

88

Esercizi


456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia654123

876987

901234

789456

Matricola

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

89

Esercizi

Che cos’è Studenti >< Lavoratori ?

Reale

Neri

Bruni

Cognome

Carla456123

Dario654321

Andrea123456

NomeMatricola

Reale

Neri

Bianco

Cognome

Carla456123

Dario654321

Giovanni321654

NomeMatricola

Studenti Lavoratori

90

Esercizi

Studenti >< Esami è un join completo?

456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia123456

456123

654321

123456

Matricola

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

91

Esercizi

Che cos’è Studenti ><LEFT Esami?

456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia987654

456123

654321

987654

Matricola

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

92

Esercizi

Che cos’è Studenti ><FULL Esami?

456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia987654

456123

654321

987654

Matricola

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

93

Esercizi

Che cos’è Studenti ><Matricola=Studente Esami?

456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia987654

456123

654321

987654

Studente

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

94

Esercizi

Che cos’è Studenti ><Voto>25 Esami?

456123

654321

123456

Matricola

Carla

Dario

Dario

Nome

Reale

Neri

Bruni

Cognome

Studenti

23Psicologia987654

456123

654321

987654

Matricola

22

29

29

Voto

Psicologia

Psicologia

Informatica

Corso

Esami

95

Interrogazioni con Algebra Relazionale

Dato uno schema R(Y), un’interrogazione è una funzione che, per ogni istanza r di R(Y), produce una relazione su un dato insieme di attributi X

Le espressioni di un linguaggio di interrogazione (per esempio, algebra relazionale), permettono di realizzare interrogazioni su un DB

E(r): risultato dell’applicazione dell’espressione E al DB r

E(r) è una relazione

96

Esempi di Interrogazioni: 1

3.25050Mario Rossi375

3.50034Sergio Rossi301

3.50044Nico Bini252

3.00050Siro Bisi231

3.00049Marco Celli210

1.70044Nico Bini105

3.05038Luigi Neri104

1.75023Mario Bianchi103


StipEtàNomeMatr

252375

231301

210301

105231

104210

103210

101210

ImpiegatoCapo

Impiegati Supervisione

Trovare matricola, nome ed età degli impiegati che guadagnano più di 2.000

97



πMatr,Nome,Età(σStip>2.000(Impiegati))

98



50Mario Rossi375

34Sergio Rossi301

44Nico Bini252

50Siro Bisi231

49Marco Celli210

38Luigi Neri104

34Mario Rossi101

EtàNomeMatr

99


Trovare gli impiegati che guadagnano piùdel loro capo, mostrando matricola, nome e stipendio di ciascuno di essi e del capo

1. Definire relazione R che lega (join) la descrizione degli impiegati alla descrizione del capo

Per non confondere gli attributi dell’impiegato e del capo bisogna ridenominare una delle due relazioni

100



2. Selezionare le tuple di R nelle quali lo stipendio dell’impiegato è superiore a quello del capo

101



3. Proiettare risultato su attributo Matr, Nome e Stip di impiegato e sui corrispettivi (ridenominati) di capo

102


1. Definire relazione R che lega la descrizione di impiegati a descrizione di capo

a) Definire relazione R1 che descrive gli impiegati di ciascun capo

� Schema: R1(Matr,Nome,Età,Stip,Capo,Impiegato)

R1 = Impiegati ><Matr=Impiegato Supervisione

103


R1 = Impiegati ><Matr=Impiegato Supervisione

3.500

3.000

3.000

1.700

3.050

1.750

2.000

Stip

375

301

301

231

210

210

210

Capo

252

231

210

105

104

103

101

Impiegato

44Nico Bini252

50Siro Bisi231

49Marco Celli210

44Nico Bini105

38Luigi Neri104

23Mario Bianchi103

34Mario Rossi101

EtàNomeMatr

R1

104


1.b) Definire relazione R che descrive impiegati e

capo: per non confondere gli attributi dell’impiegato e del capo ridenominareimpiegati

R2=ρMatrC,NomeC,EtàC,StipC←Matr,Nome,Età,Stip(Impiegati)

R = R1 ><Capo=MatrC R2

105




3.50034Sergio Rossi301

3.50044Nico Bini252

3.00050Siro Bisi231

3.00049Marco Celli210

1.70044Nico Bini105

3.05038Luigi Neri104



StipCEtàCNomeCMatrC

R2

106



252

231

210

105

104

103

101

Impiegato

375

301

301

231

210

210

210

MatrC

Mario Rossi

Sergio Rossi

Sergio Rossi

Siro Bisi

Marco Celli

Marco Celli

Marco Celli

NomeC

50

34

34

50

49

49

49

EtàC

3.250

3.500

3.500

3.000

3.000

3.000

3.000

StipC

3.500

3.000

3.000

1.700

3.050

1.750

2.000

Stip

375

301

301

231

210

210

210

Capo

44Nico Bini252

50Siro Bisi231

49Marco Celli210

44Nico Bini105

38Luigi Neri104

23Mario Bianchi103

34Mario Rossi101

EtàNomeMatr

R

107


1.b) Definire relazione R che descrive impiegati e

capo: per non confondere gli attributi dell’impiegato e del capo ridenominareimpiegati



(Impiegati ><Matr=Impiegato Supervisione) ><Capo=MatrC

ρMatrC,NomeC,EtàC,StipC←Matr,Nome,Età,Stip(Impiegati)

108


2. Selezionare le tuple in R in cui lo stipendio dell’impiegato è superiore a quello del capo:

σStip>StipC(R)

252

104

Impiegato

375

210

MatrC

Mario Rossi

Marco Celli

NomeC

50

49

EtàC

3.250

3.000

StipC

3.500

3.050

Stip

375

210

Capo

44Nico Bini252

38Luigi Neri104

EtàNomeMatr

109


2. Selezionare tuple in R in cui lo stipendio dell’impiegato è superiore a quello del capo:

σStip>StipC(R)

σStip>StipC((Impiegati ><Matr=ImpiegatoSupervisione) ><Capo=MatrC

ρMatrC,NomeC,EtàC,StipC←Matr,Nome,Età,Stip(Impiegati))

110


3. Proiettare σStip>StipC(R) su attributi richiesti:

πMatr,Nome,Stip,MatrC,NomeC,StipC(σStip>StipC(R))

375

210

MatrC

Mario Rossi

Marco Celli

NomeC

3.250

3.000

StipC

3.500

3.050

Stip

Nico Bini252

Luigi Neri104

NomeMatr

111


πMatr,Nome,Stip,MatrC,NomeC,StipC(σStip>StipC((Impiegati ><Matr=ImpiegatoSupervisione)

><Capo=MatrC

ρMatrC,NomeC,EtàC,StipC←Matr,Nome,Età,Stip(Impiegati)))


112


Trovare matricola e nome dei capi i cui impiegati guadagnano tutti più di 2.000

Quantificazione universale non e’ presentetra gli operatori dell’algebra relazionale

1. Selezionare capi che hanno impiegati con stipendio < 2.000

πCapo(Supervisione ><Matr=Impiegato(σStip<2.000(Impiegati)))

113



1.70044Nico Bini105


StipEtàNomeMatr

1.700

1.750

Stip

231

210

Capo

105

103

Impiegato

44Nico Bini105

23Mario Bianchi103

EtàNomeMatr

231

210

Capo

σStip<2.000(Impiegati)

Supervisione ><Matr=Impiegato(σStip<2.000(Impiegati))

πCapo(Supervisione ><Matr=Impiegato

(σStip<2.000(Impiegati)))

Capi che hannoimpiegati con stipendio<2.000

114


2. Sottrarre tali capi all’insieme di tutti i capi

πCapo(Supervisione) –


115


πCapo(Supervisione) –


375

301

231

210

Capo πCapo(Supervisione) πCapo(Supervisione) –

πCapo(Supervisione

><Matr=Impiegato

(σStip<2.000(Impiegati)))

375

301

Capo

116


3.250

3.500

Stip

375

301

Capo

50Mario Rossi375

34Sergio Rossi301

EtàNomeMatr

3. Recuperare le informazioni relative ai capi dalla relazione Impiegati

Impiegati ><Matr=Capo

(πCapo(Supervisione) –

πCapo(Supervisione ><Matr=Impiegato(σStip<2.000(Impiegati))))

117


Mario Rossi375

Sergio Rossi301

NomeMatr

4. Proiettare sulle informazioni richieste dall’interrogazione (numero di matricola e nome del capo)

πMatr,Nome(Impiegati ><Matr=Capo


πCapo(Supervisione ><Matr=Impiegato(σStip<2.000(Impiegati)))))

118


Trovare matricola e nome dei capi i cui impiegati guadagnano tutti più di 2.000

πMatr,Nome(Impiegati ><Matr=Capo


πCapo(Supervisione ><Matr=Impiegato(σStip<2.000(Impiegati)))))

119

Algebra con valori nulli

Come applicare espressioni di algebra relazionale in presenza di tuple con valori nulli?

Per esempio: σEtà>30(Impiegati)� Quando nella relazione Impiegati non si conosce l’età di

alcune persone: le tuple 104 e 219 devono essere selezionate?

3.000NULLMarco Celli210

1.70044Nico Bini105

3.050NULLLuigi Neri104



StipEtàNomeMatr

120


Logica a 3 valori per il trattamento di valori veri, falsi, sconosciuti: V, F, U (unknown)

Un predicato assume valore U quando uno dei termini del confronto ha valore nullo

Tabelle di verità dei connettivi: AND, OR, NOT

FFFF

FUUU

FUVV

FUVAND

FUVF

UUVU

VVVV

FUVOR

VF

UU

FV

NOT

121


Poiché ragionare su valori nulli è complesso, adottiamo un approccio semplificato al trattamento del valore nullo nelle espressioni dell’algebra relazionale

Definiamo due nuove condizioni atomiche di selezione: dato un attributo A� A IS NULL: vera su tupla t se il valore di t su A ènullo; falsa se il valore è specificato

� A IS NOT NULL: vera su t se valore di t su A èspecificato, falsa altrimenti

122


Interpretiamo le condizioni di selezione in modo restrittivo, escludendo dalla selezione le tuple con valore U, a meno che non sia esplicitamente incluso nella selezione

123


Per esempio:

� σEtà>30(Impiegati) – le tuple con Età nullnon 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)

124

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)

125

Viste

DBMS offrono solo relazioni virtuali (no ridondanza dei dati)

Interrogazioni che utilizzano viste sono risolte sostituendo la definizione delle viste alle loro occorrenze

126

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)

127

Viste

Viste utili per:� Permettere ad applicazioni di utilizzare relazioni che contengono solo le informazioni di interesse

� Se lo schema del 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

128

Esercizi1. Considerare lo schema di base di dati contenente le relazioni:

FILM(CodFilm,Titolo,Regista,Anno,CostoNoleggio)ARTISTI(CodAttore,Cognome,Nome,Sesso,DataNascita,Nazionalita)INTERPRETAZIONI(CodFilm,CodAttore,Personaggio)

Formulare in algebra relazionale le interrogazioni che trovino:

1) I titoli dei film nei quali Henry Fonda sia stato interprete;2) I titoli dei film per i quali il regista sia stato anche interprete;3) I titoli dei film in cui recitano attori italiani;4) I titoli di tutti i film in cui Robert Redford è attore specificando

che personaggio interpreta;5) I titoli dei film in cui gli attori siano tutti dello stesso sesso;6) Nome e cognome degli attori che hanno recitato insieme (in

coppia) in almeno un film e specificare il titolo del film.

129


DEPUTATI(Codice,Cognome,Nome,Commissione,Provincia,Collegio)COLLEGI(Provincia,Numero,Nome)

PROVINCE(Sigla,Nome,Regione) REGIONI(Codice,Nome)COMMISSIONI(Numero,Nome,Presidente)


1) Nome e cognome dei presidenti di commissioni cui partecipaalmeno un deputato eletto in una provincia della Sicilia;

2) Nome e cognome dei deputati della commissione Bilancio;3) Nome, cognome, provincia e regione di elezione dei deputati

della commissione Bilancio;4) Le regioni in cui vi sia un solo collegio, indicando il nome e

cognome del deputato ivi eletto.130


CORSO(Codice,Denominazione,Professore)STUDENTI(Matricola,Cognome,Nome)

PROFESSORI(Matricola,Cognome,Nome)ESAMI(Studente,Corso,Voto,Data)


1) Gli esami superati dallo studente Pico Della Mirandola(supposto unico), con indicazione della denominazione del corso, del voto e del cognome del professore;

2) Nome e cognome degli studenti che hanno riportato in almeno un esame un voto pari a 30 dopo il 01/01/2006;

3) Nome e cognome dei professori che non hanno maiassegnato un voto maggiore di 28.

3 algebra relazionale - di.unito.it · Algebra relazionale 2 Accesso ai dati di un DB Aggiornamento...

Documents

Transcript of 3 algebra relazionale - di.unito.it · Algebra relazionale 2 Accesso ai dati di un DB Aggiornamento...