Sistemi per il recupero delle informazioni

(1)

Sistemi per il recupero delle informazioni

RIASSUNTO

(2)

PROGETTAZIONE

 Ricordiamo le fasi della progettazione di una Base di Dati

(3)

ESEMPIO

(4)

ESEMPIO

VIENE TRADOTTO NELLO SCHEMA

MUSEI (NomeM, Città, Indirizzo, Direttore) ARTISTI (NomeA, Nazionalità, DataN, DataM) OPERE (Codice, Anno, Titolo, NomeM*, NomeA*) PERSONAGGI (Personaggio, Codice*)

DIPINTI (Codice*, Tipo, Larghezza, Altezza) SCULTURE (Codice*, Materiale, Altezza, Peso)

(5)

Ennupla. È un insieme finito di coppie (Attributo, valore atomico)

Relazione. È un insieme finito (eventualmente vuoto) di ennuple con la stessa struttura.

I campi di un’ennupla sono atomici (numeri, stringhe o il valore NULL).

Un’ennupla si usa per rappresentare entità e la relazione si usa per rappresentare classi di entità.

ENNUPLA E RELAZIONE

I meccanismi per definire una base di dati con il modello relazionale sono solo due:

• l’ennupla

• la relazione.

(6)

TERMINOLOGIA - I

 attributo: corrisponde (non sempre) ad un attributo del modello E-R.

Diversamente dal modello E-R, gli attributi sono sempre univoci (ad un sol valore) ed atomici (non composti)

 L’ordine degli attributi non è significativo

 dominio (di un attributo): è l’insieme dei valori che può assumere un attributo.

 chiave primaria di una relazione: un attributo che identifica univocamente le ennuple della relazione. Gli attributi della chiave primaria vengono sottolineati

(7)

TERMINOLOGIA - II

 Una relazione si definisce dandole un nome ed elencando fra parentesi tonde il tipo delle sue ennuple. La definizione di una relazione è detta schema della relazione

R(A1:T1, A2:T2,..., An:Tn)

 R è il nome della relazione

 A1, A2,... sono gli attributi della relazione

 T1, T2,... sono i tipi degli attributi (interi, reali, booleani, stringhe)

 Per semplicità omettiamo la specifica dei tipi, per cui lo schema di relazione è R(A1, A2,...,An)

(8)

RELAZIONE E TABELLA

Orario

Insegnamento Docente Aula Ora Analisi matem. I Luigi Neri N1 8:00

Basi di dati Piero Rossi N2 9:45 Chimica Nicola Mori N1 9:45 Fisica I Mario Bruni N1 11:45 Fisica II Mario Bruni N3 9:45 Sistemi inform. Piero Rossi N3 8:00 ORARIO (Insegnamento, Docente, Aula, Ora)

Relazione e tabella sono sinonimi

(9)

SCHEMA

Orario

Insegnamento Docente Aula Ora Analisi matem. I Luigi Neri N1 8:00

Basi di dati Piero Rossi N2 9:45 Chimica Nicola Mori N1 9:45 Fisica I Mario Bruni N1 11:45 Fisica II Mario Bruni N3 9:45 Sistemi inform. Piero Rossi N3 8:00 ORARIO (Insegnamento, Docente, Aula, Ora)

Le colonne della tabella formano lo schema della relazione

Lo schema della relazione è la descrizione della struttura di una relazione

(10)

ISTANZE

Orario

Insegnamento Docente Aula Ora Analisi matem. I Luigi Neri N1 8:00

Basi di dati Piero Rossi N2 9:45 Chimica Nicola Mori N1 9:45 Fisica I Mario Bruni N1 11:45 Fisica II Mario Bruni N3 9:45 Sistemi inform. Piero Rossi N3 8:00 ORARIO (Insegnamento, Docente, Aula, Ora)

I contenuti delle righe della tabella formano le istanze della relazione

Un’istanza di uno schema di relazione è un insieme finito di ennuple.

 osserviamo che tutte le ennuple di una relazione hanno la stessa struttura

(11)

DATABASE

Corso Docente Aula

Rossi

Basi di dati DS3

Bruni

Reti N3

Neri

Sistemi N3

Bruni

Controlli G

Corsi

Nome Edificio Piano

OMI

DS3 Terra

Pincherle

G Primo

OMI

N3 Terra

Aule

CorsiSedi Corso Aula

Reti N3

Sistemi N3

Controlli G

Piano Edificio

OMI Terra

Pincherle Primo

OMI Terra

Un database e’ un insieme di tabelle

Lo schema relazionale è la descrizione della struttura di una base di dati

 insieme di schemi di relazione e insieme di vincoli

(12)

DA MODELLO A OGGETTI A MODELLO RELAZIONALE

La trasformazione di uno schema a oggetti in uno schema relazionale avviene eseguendo i seguenti passi:

1. rappresentazione delle classi

2. rappresentazione delle associazioni uno a uno e uno a molti;

3. rappresentazione delle associazioni molti a molti o non binarie;

4. rappresentazione delle gerarchie di inclusione;

5. rappresentazione degli attributi multivalore;

6. appiattimento gli attributi composti

(13)

1. Rappresentazione delle classi

Una classe C è rappresentata da una relazione R i cui attributi sono quelli di C

si traduce in

Studenti(Matricola, Nome, AnnoImmatric)

(14)

2. Rappresentazione delle associazioni uno a uno e uno a molti

Studenti(Matricola, Nome, AnnoImmatric, Codice*) CorsidiLa(Codice, Nome, Tipo)

la chiave esterna Codice* rappresenta l’associazione è_iscritto N.B. È un grave errore fare il contrario. Lo schema:

Studenti(Matricola, Nome, AnnoImmatric) CorsidiLa(Codice, Nome, Tipo, Matricola*)

rappresenta Corsi di Laurea ai quali può essere iscritto un solo studente!

(15)

3. Rappresentazione delle associazioni molti a molti o non binarie

Una ennupla di Insegna rappresenta una coppia (Corso_di_Lurea, Docente) di oggetti in associazione.

CorsidiLa(Codice,Nome,Facoltà,Tipo) Docenti(CodDoc, Settore)

Insegna(Codice*, CodDoc*)

Il docente identificato dal CodDoc 1592 insegna ai corsi di laurea identificati dai codici Inf, SBC e Mat , il docente identificato dal CodDoc 3014 insegna al corso di laurea identificato dai codici Inf, ecc...

(16)

4. Rappresentazione delle gerarchie fra classi Partizionamento orizzontale

 Tre schemi indipendenti, uno per ogni classe, contenenti tutti gli attributi di ciascuna classe

Anche con questa soluzione viene ignorata la gerarchia: si perde la superclasse

Si osservi che con nessuna delle tre soluzioni è in generale possibile esprimere i vincoli strutturali della gerarchia, vale a dire i vincoli di disgiunzione e di totalità

(17)

5. Rappresentazione delle proprietà multivalore

Film(CodFilm,Titolo,Regista,Anno) Attori(CodFilm*, Attore)

(18)

Riassumendo

Le regole di traduzione

 Entità: diventano tabelle ed i loro identificatori chiavi primarie

 Associazioni 1-1: se obbligatorie si procede come per le 1-N scegliendo il lato in cui includere gli attributi e la chiave esterna; se una opzionale si

includono gli attributi e la chiave esterna sul lato“obbligatorio”; se entrambe opzionali si costruisce una tabella autonoma come per il caso N-N.

 Associazioni1-N:gli attributi dell’associazione e la chiave primaria della tabella relativa all’entità dal lato “N” sono inclusi nella tabella relativa all’entità dal lato“1”.

 Associazioni N-N: diventano tabelle con chiave primaria formata dall’unione delle chiavi delle entità coinvolte

(19)

Informazione incompleta

 ll modello relazionale impone ai dati una struttura rigida:

- le informazioni sono rappresentate per mezzo di ennuple

- solo alcuni formati di ennuple sono ammessi: quelli che corrispondono agli schemi di relazione

 I dati disponibili possono non corrispondere al formato previsto

Franklin Delano Roosevelt

Nome SecondoNome Cognome

Winston Churchill

Charles De Gaulle

Josip Stalin

Es: PERSONE(Nome, SecondoNome, Cognome)

(20)

Informazione incompleta: soluzioni?

 Non conviene (anche se spesso si fa) usare valori del dominio (0, stringa nulla, “99”, ...):

- potrebbero non esistere valori “non utilizzati”

- in fase di utilizzo (nei programmi) sarebbe necessario ogni volta tener conto del “significato” di questi valori

 Tecnica rudimentale ma efficace:

- valore nullo: denota l’assenza di un valore del dominio (e non è un valore del dominio)

- si possono (e debbono) imporre restrizioni sulla presenza di valori nulli

(21)

Vincoli, schemi e istanze

 I vincoli corrispondono a proprietà del mondo reale modellato dalla base di dati

 interessano a livello di schema (con riferimento cioè a tutte le istanze)

 Ad uno schema associamo un insieme di vincoli e consideriamo corrette (valide, ammissibili) le istanze che soddisfano tutti i vincoli

 Nel modello relazionale si possono esprimere i seguenti vincoli:

 Vincolo di chiave

 Vincolo di chiave esterna

 Tipo di un attributo

 Attributo obbligatorio

 Vincoli strutturali delle associazioni (non completamente)

(22)

Una base di dati "scorretta"

Studente Voto Lode Corso

32 01

276545

276545 30 e lode 02

787643 27 e lode 03

739430 24 04

Esami

Matricola 276545 787643 787643

Cognome Rossi

Neri Bianchi

Nome Mario Piero

Luca Studenti

787643 787643

32

27 e lode 739430

(23)

Tipi di vincoli

 Vincoli intrarelazionali: coinvolgono una singola relazione del database.

 Esempi

- vincoli di ennupla

- vincoli di chiave

- vincoli su valore

 Vincoli interrelazionali: coinvolgono diverse relazioni del database.

 Esempio: vincoli di integrità referenziale

(24)

Vincoli di ennupla, esempio

Impiegato Rossi

Neri Bruni

Stipendi Lordo

55.000 45.000 47.000

Netto 42.500 35.000 36.000 Ritenute

12.500 10.000 11.000 Lordo = (Ritenute + Netto)

Esprimono condizioni sui valori di ciascuna ennupla, indipendentemente dalle altre ennuple

Una possibile sintassi:

espressione booleana di atomi che confrontano valori di attributo o espressioni aritmetiche su di essi

(25)

Importanza delle chiavi

 L’esistenza delle chiavi garantisce l’accessibilità a ciascun dato della base di dati

 Le chiavi permettono di correlare i dati in relazioni diverse:

 il modello relazionale è basato su valori

 In presenza di valori nulli, i valori della chiave non permettono

 di identificare le ennuple

 di realizzare facilmente i riferimenti da altre relazioni

(26)

Chiave primaria

 Chiave su cui non sono ammessi nulli

 Notazione: sottolineatura

Matricola

78763 65432

Nome

Piero Mario 87654

43289

Mario Cognome

Neri Neri Rossi

Neri Mario

Corso

Ing Mecc Ing Inf Ing Civile

NULL

Nascita

86765 NULL Mario Ing Inf 5/12/78

10/7/79 NULL 3/11/76

5/12/78

(27)

 Informazioni in relazioni diverse sono correlate attraverso valori comuni

 in particolare, valori delle chiavi (primarie)

 le correlazioni debbono essere "coerenti“

 Un vincolo di integrità referenziale (“foreign key”) fra gli attributi X di una relazione R₁ e un’altra relazione R₂ impone ai valori su X in R₁ di comparire come valori della chiave primaria di R2

 ES: vincoli di integrità referenziale fra: l’attributo Vigile della relazione INFRAZIONI e la relazione VIGILI

Integrità referenziale

(28)

Matricola 3987 3295 9345

Vigili Cognome

Rossi Neri Neri

Nome Luca Piero Mario

Mori Gino

7543 Infrazioni

Codice 34321

73321 64521 53524

Data 1/2/95 4/3/95 5/4/96 5/2/98

Vigile 3987 3295 3295 9345

Prov Numero

MI TO PR PR

39548K E39548 839548 839548 3295

3295 3987

3987

9345 3987

9345

3295

3295 3295

(29)

Infrazioni Codice

34321

73321 64521 53524

Data 1/2/95 4/3/95 5/4/96 5/2/98

Vigile 3987 3295 3295 9345

Prov Numero

MI TO PR PR

39548K E39548 839548 839548

Auto Prov Numero

MI TO PR

E39548 F34268 839548

Cognome Rossi Rossi Neri

Nome Mario Mario Luca

TO E39548

TO

E39548

Violazione di vincolo di integrità referenziale

(30)

Integrità referenziale e valori nulli

Impiegati Matricola 34321

64521 53524

Neri Verdi

Progetto IDEA

XYZ NULL

Progetti Codice

IDEA

BOH XYZ

Inizio 01/2000 07/2001 09/2001

Durata 36 24 24

Costo 200 120 150

73032 Bianchi IDEA

(31)

Azioni compensative

 Esempio:

 Viene eliminata una ennupla causando una violazione

 Comportamento “standard”:

 Rifiuto dell'operazione

 Azioni compensative:

 Eliminazione in cascata

 Introduzione di valori nulli

(32)

Eliminazione in cascata

64521 53524

Neri Verdi

XYZ NULL

73032 Bianchi IDEA

IDEA

BOH XYZ

Inizio 01/2000 07/2001 09/2001

Durata 36 24 24

Costo 200 120 150

XYZ 07/2001 24 120

53524 Neri XYZ

(33)

Introduzione di valori nulli

64521 53524

Neri Verdi

XYZ NULL

73032 Bianchi IDEA

IDEA

BOH XYZ

Inizio 01/2000 07/2001 09/2001

Durata 36 24 24

Costo 200 120 150

XYZ 07/2001 24 120

NULL

(34)

Esercizio

 La segreteria di un corso di laurea deve gestire alcune informazioni relative all’orario delle lezioni.

 informazioni: aule, corsi, lezioni

 le aule sono identificate da un codice, di ogni aula interessa il numero dei posti, l’edificio in cui è situata, se ha o non ha proiettore

 alcune aule sono dotate di calcolatori, in questo caso interessa sapere il numero dei pc

 i corsi sono identificati da un codice; di ogni corso interessa il nome e il docente

 le lezioni sono caratterizzate da un’ora d’inizio, un’ora di fine, un giorno della settimana, il semestre

 ogni lezione è tenuta in un’aula e si riferisce ad un corso

(35)

(36)

Sistemi per il recupero delle informazioni

ALGEBRA RELAZIONALE

(37)

Accesso ai Dati nei Sistemi Relazionali

Una base di dati può essere utilizzata con due modalità:

 interattivamente: l’utente interagisce direttamente con la base di dati presentando al sistema una richiesta di dati. Tale richiesta prende il nome di interrogazione (query). L’interrogazione viene interpretata dal sistema, che in risposta restituisce i dati richiesti. Nella richiesta devono essere specificate le proprietà dei dati che interessano. Se ad es. vogliamo l’elenco dei libri scritti da Calvino, nella richiesta deve essere specificata questa proprietà. L’interrogazione deve essere formulata per mezzo di un linguaggio formale.

 mediante programmi: questo uso è riservato ad utenti programmatori.

Le interrogazioni fanno parte di un programma applicativo che può essere eseguito dal sistema numerose volte, ed il risultato delle interrogazioni può essere utilizzato dal programma per successive elaborazioni

(38)

ESEMPI DI QUERY

(39)

Come esempio di linguaggi per l’uso interattivo di basi di dati, relazionali, vediamo

 l’algebra relazionale: insieme di operatori su relazioni che danno come risultato relazioni. Non si usa come linguaggio di interrogazione dei DBMS ma come rappresentazione interna delle interrogazioni.

 il linguaggio SQL (Structured Query Language), che offre una sintassi per l’algebra relazionale.

Il termine algebra è dovuto al fatto che sono previsti operatori (query) che agiscono su relazioni e producono altre relazioni come risultato.

Gli operatori possono essere combinati per formare espressioni complesse.

LINGUAGGI RELAZIONALI

(40)

Algebra relazionale

 Insieme di operatori

 su relazioni

 che producono relazioni (tabelle)

 e possono essere composti per svolgere operazioni più complesse

(41)

 Gli operatori fondamentali dell’algebra relazionale sono:

 Ridenominazione;

 Unione;

 Intersezione;

 Differenza;

 Proiezione;

 Restrizione (o Selezione);

 Prodotto.

 I simboli R,S,... denotano relazioni, A, B,…attributi e X,Y,…insiemi di attributi

OPERATORI FONDAMENTALI

(42)

Paternità

Padre Figlio

Adamo Caino

Abramo Isacco

Adamo Abele

 Genitore  Padre (Paternità)

Padre Figlio

Adamo Caino

Abramo Isacco

Adamo Abele

Genitore

RIDENOMINAZIONE

 Operatore unario

 Modifica il nome di un attributo senza cambiarne il valore: restituisce la relazione ottenuta sostituendo in R gli attributi A, B,… con gli attributi A’, B’,…

 DEFINIZIONE OPERATORIALE: _A__A’(R)

(43)

OPERATORI INSIEMISTICI

 Le relazioni sono degli insiemi, quindi possiamo applicare gli operatori sugli insiemi

 I risultati debbono essere relazioni

 E’ possibile applicare unione, intersezione, differenza solo a relazioni definite sugli stessi attributi

(44)

 Siano R ed S relazioni dello stesso tipo allora

 L’unione di R con S restituisce una relazione dello stesso tipo con le ennuple che stanno in R in S, o in entrambe.

 L’intersezione di R con S restituisce una relazione dello stesso tipo con le ennuple che stanno contemporaneamente sia in R sia in S.

 La differenza di R con S restituisce una relazione dello stesso tipo con le ennuple che stanno in R ma non in S.

 DEFINIZIONE OPERATORIALE

 RS

 RS

 R - S

UNIONE, INTERSEZIONE, DIFFERENZA

(45)

ESEMPIO DI UNIONE

(46)

ESEMPIO DI INTERSEZIONE

(47)

ESEMPIO DI DIFFERENZA

(48)

 “Paternita’” e “Maternità” sono attributi con nomi diversi ma entrambi sono

“Genitori”

 Soluzione: ridenominare gli attributi

ESEMPIO: UNIONE?

Maternità

Madre

???

(49)

ESEMPIO: RIDENOMINAZIONE E UNIONE

(50)

selezione

proiezione

operatori "ortogonali“

selezione:

decomposizione orizzontale proiezione:

decomposizione verticale

(51)

 Produce risultati:

 su un sottoinsieme degli attributi dell’operando

 con valori da tutte le n-uple della relazione

 Data la relazione R su insieme di attributi X={A1,A2,…An} e un sottoinsieme Y di X, la proiezione di R su Y è la relazione ottenuta da R considerando solo i valori sugli attributi di Y

 DEFINIZIONE OPERATORIALE: Y(R)

 La cardinalità di _Y(R), cioè il numero degli elementi che lo compongono, puo’ essere minore di R nel caso di duplicati

PROIEZIONE

(52)

Cognome Filiale Stipendio Matricola

Neri Milano 64

5998

Neri Napoli 55

7309

Rossi Roma 64

5698

9553

 cognome e filiale di tutti gli impiegati



Cognome, Filiale

(Impiegati)

ESEMPIO PROIEZIONE

 Si riduce la cardinalita’ del risultato rispetto all’operando

(53)

 Produce risultati:

 con lo stesso schema dell’operando

 con un sottoinsieme delle ennuple dell’operando: quelle che soddisfano la condizione

 Data la relazione R la restrizione di R alla condizione C restituisce una relazione dello stesso tipo di R avente per valori gli elementi di R che soddisfano la condizione C.

 La condizione di selezione è formata da

 operatori booleani (AND, OR, NOT)

 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)

 DEFINIZIONE OPERATORIALE: Condizione(R)

SELEZIONE (o RESTRIZIONE)

(54)

Operatori booleani

 sintassi:  Condizione (Operando)

 Condizione: espressione booleana (come quelle dei vincoli di ennupla)

 semantica: il risultato contiene le ennuple dell'operando che soddisfano la condizione

 Connettivi logici

  (AND),

  (OR),

  (NOT)

 Operatori di confronto

 = (uguale)  (diverso)

  (maggiore)  (minore)

  (maggiore o uguale)

  (minore o uguale)

(55)

5998

7309

5698

Milano Milano 44

9553 Impiegati

9553 Neri Napoli 64

5698

 impiegati che guadagnano più di 50



Stipendio > 50

(Impiegati)

ESEMPIO RESTRIZIONE

(56)

Impiegati

5998

7309

5698

9553

 impiegati che guadagnano più di 50 e lavorano a Milano

Stipendio > 50 AND Filiale = 'Milano' (Impiegati)

7309

5698

9553

5998

(57)

Impiegati

5998

7309

5698

9553

 impiegati che hanno lo stesso nome della filiale presso cui lavorano

 _Cognome _{= Filiale}(Impiegati)

5998

7309

5698

9553

(58)

Selezione e proiezione

 Combinando selezione e proiezione, possiamo estrarre interessanti informazioni da una relazione

 matricola e cognome degli impiegati che guadagnano più di 50



Stipendio > 50

(Impiegati)



Matricola,Cognome

(  )

Cognome Filiale Stipendio Matricola

Neri Milano 64 5998

Rossi Roma 55

7309

Neri Napoli 64 5698

Milano Milano 44 9553 Milano Milano 44

9553 Neri Napoli 64

5698

(59)

Selezione con valori nulli

Cognome Filiale Età Matricola

Neri Milano 45 5998

Rossi Roma 32

7309

Bruni Milano

^NULL

9553

Impiegati

SEL

Età > 40 (Impiegati)

• la condizione atomica è vera solo per valori non nulli

(60)

Selezione con valori nulli: soluzione

SEL Età > 40 (Impiegati)

 la condizione atomica è vera solo per valori non nulli

 per riferirsi ai valori nulli esistono forme apposite di condizioni:

IS NULL IS NOT NULL

 si potrebbe usare (ma non serve) una "logica a tre valori" (vero, falso, sconosciuto)

 Quindi:

SEL

Età>30 (Persone) 

SEL

Età30 (Persone) 

SEL

Età IS NULL (Persone)

=

SEL

Età>30  Età30  Età IS NULL(Persone)

= Persone

(61)

Cognome Filiale Età Matricola

Neri Milano 45 5998

Rossi Roma 32

7309

Bruni Milano

^NULL

9553

Impiegati

Neri Milano 45 5998

Bruni Milano

^NULL

9553

SEL

(Età > 40) OR (Età IS NULL) (Impiegati)

Neri Milano 45 5998

Bruni Milano

^NULL

9553

(62)

JOIN NATURALE

 operatore binario (generalizzabile)

 produce un risultato

 sull'unione degli attributi degli operandi

 con ennuple costruite ciascuna a partire da una ennupla di ognuno degli operandi

 Permette di combinare ennuple da relazioni diverse basandosi sui valori degli attributi

 Sia R con attributi XY ed S con attributi YZ. Il join naturale produce una relazione di attributi XYZ; ennuple del risultato sono ottenute combinando le ennuple di R e S che hanno gli stessi valori negli attributi con lo stesso nome

(63)

ESEMPIO - I

 join completo: ogni ennupla contribuisce al risultato

(64)

ESEMPIO - II

 Join non completo: alcuni valori tra gli attributi comuni non coincidono, quindi, alcune ennuple non partecipano al JOIN

(65)

ESEMPIO - III

 Join vuoto: caso limite

 potrebbe anche succedere che nessuna ennupla trovi il corrispettivo

(66)

ESEMPIO - IV

 L’altro caso estremo del JOIN

 ogni ennupla di R1 si combina con ogni ennupla di R2

 la cardinalita’ del risultato e’ il prodotto delle cardinalita’

(67)

Cardinalità del join

 Il join di R1 e R2 contiene un numero di ennuple compreso fra zero e il prodotto di | R1| e |R2|

 se il join coinvolge una chiave di R2, allora il numero di ennuple è compreso fra zero e |R1|

 se il join coinvolge una chiave di R2 e un vincolo di integrità referenziale, allora il numero di ennuple è pari a |R₁|

 R1(A,B) , R2 (B,C) in generale

0  |R1 JOIN R2|  |R1|  |R2|

 se B è chiave in R₂

0  |R1 JOIN R2|  |R1|

 se B è chiave in R2 ed esiste vincolo di integrità referenziale fra B (in R1) e R2:

|R₁ JOIN R₂|= |R₁|

(68)

Impiegato Reparto

Rossi A

Neri B

Bianchi B

Reparto Capo

B Mori

C Bruni

Neri B Mori

Impiegato Reparto Capo

Bianchi B Mori

A

C

Join, una difficoltà

 alcune ennuple non contribuiscono al risultato: vengono "tagliate fuori"

(69)

Join esterno

 Il join esterno estende, con valori nulli, le ennuple che verrebbero tagliate fuori da un join (interno)

 esiste in tre versioni:

 sinistro, destro, completo

(70)

Join esterno

 sinistro: mantiene tutte le tuple del primo operando, estendendole con valori nulli, se necessario

 destro: ... del secondo operando ...

 completo: … di entrambi gli operandi ...

(71)

Impiegato Reparto

Rossi A

Neri B

Bianchi B Impiegati

Reparto Capo

B Mori

C Bruni

Reparti

Neri B Mori

Impiegato Reparto Capo

Bianchi B Mori

Impiegati ^JOIN

_LEFT

Reparti C

Rossi A ^NULL

A

Rossi

(72)

Impiegato Reparto

Rossi A

Neri B

Bianchi B Impiegati

Reparto Capo

B Mori

C Bruni

Reparti

Neri B Mori

Impiegato Reparto Capo

Bianchi B Mori

Impiegati ^JOIN

_RIGHT

Reparti A

NULL C Bruni

C Bruni

(73)

Impiegato Reparto

Rossi A

Neri B

Bianchi B Impiegati

Reparto Capo

B Mori

C Bruni

Reparti

Neri B Mori

Impiegato Reparto Capo

Bianchi B Mori

Impiegati ^JOIN

_FULL

Reparti

NULL C Bruni

C Bruni

A Rossi

Rossi A ^NULL

(74)

Join e proiezioni Impiegato Reparto

Rossi A

Neri B

Bianchi B

Reparto Capo

B Mori

C Bruni

Neri B Mori

Impiegato Reparto Capo

Bianchi B Mori

Impiegato Reparto

Neri B

Bianchi B

Reparto Capo

B Mori

(75)

Proiezioni e join

Neri B Mori

Impiegato Reparto Capo

Bianchi B Bruni

Verdi A Bini

Neri B

Impiegato Reparto

Bianchi B

Verdi A

B Mori

Reparto Capo

B Bruni

A Bini

Verdi A Bini

Neri B Mori

Impiegato Reparto Capo

Bianchi B Bruni

Neri B Bruni

Bianchi B Mori

(76)

Join e proiezioni

 R 1(X1), R 2(X2)

PROJ_X1(R 1 JOIN R2 )  R 1

 R(X), X = X1  X2

(PROJ_X1(R))JOIN (PROJ_X2(R)) R

(77)

Prodotto cartesiano

 un join naturale su relazioni senza attributi in comune

 contiene sempre un numero di ennuple pari al prodotto delle cardinalità degli operandi (le ennuple sono tutte combinabili )

(78)

Rossi A

Neri B

Bianchi B

Impiegato Reparto Impiegati

A Mori

B Bruni

Codice Capo

Reparti Impiegati JOIN Reparti

Impiegato Reparto Codice Capo

Rossi A AAA Mori

Rossi A B Bruni

Neri B A Mori

Neri B B Bruni

Bianchi B A Mori

Bianchi B B Bruni

Prodotto cartesiano

(79)

 Il prodotto cartesiano, in pratica, ha senso (quasi) solo se seguito da selezione:

SEL

Condizione (R1

JOIN

R2)

 L'operazione viene chiamata theta-join e indicata con R₁

JOIN

Condizione R₂

 La condizione C è spesso una congiunzione (AND) di atomi di

confronto A1 A2 dove  è uno degli operatori di confronto (=, >, <, …)

 se l'operatore è sempre l'uguaglianza (=) allora si parla di equi-join

(80)

Rossi A

Neri B

Bianchi B

Impiegato Reparto Impiegati

A Mori

B Bruni

Codice Capo Reparti

Impiegati ^JOIN

Reparto=Codice

Reparti

Impiegato Reparto Codice Capo

Rossi A A A A Mori

Rossi A B Bruni

Neri B A Mori

Neri B B Bruni

Bianchi B A Mori

Bianchi B B Bruni

Rossi A A A A Mori

Neri B B Bruni

Bianchi B B Bruni

(81)

Esempi

Impiegati Matricola Nome Età Stipendio Bianchi 37 38

5998

Rossi 34 45

7309

Bruni 43 42

5698

Neri 42 35

9553

Mori 45 50

4076

Lupi 46 60

8123

Supervisione ^Impiegato ^Capo

5998 7309

5698 9553 4076

5698 5698

4076

8123

(82)

Nome Età Stipendio Matricola

Bianchi 37 38 5998

Rossi 34 45

7309

Bruni 43 42

5698

Neri 42 35

9553

Mori 45 50

4076

Lupi 46 60

8123

SELStipendio>40(Impiegati)

Bianchi 37 38 5998

Neri 42 35

9553

Rossi 34 45

7309

Bruni 43 42

5698

Mori 45 50

4076

Lupi 46 60

8123

Rossi 34 45

7309

Bruni 43 42

5698

Mori 45 50

4076

Lupi 46 60

8123

 Trovare matricola, nome, età e stipendio degli impiegati che guadagnano più di 40 milioni

(83)

Nome Età Stipendio Matricola

Bianchi 37 38 5998

Rossi 34 45

7309

Bruni 43 42

5698

Neri 42 35

9553

Mori 45 50

4076

Lupi 46 60

8123

Bianchi 37 38 5998

Neri 42 35

9553

Rossi 34 45

7309

Bruni 43 42

5698

Mori 45 50

4076

Lupi 46 60

8123

Rossi 34 45

7309

Bruni 43 42

5698

Mori 45 50

4076

Lupi 46 60

8123

_PROJ

Matricola, Nome, Età (

SEL

Stipendio>40(Impiegati))

 Trovare matricola, nome ed età degli impiegati che guadagnano più di 40 milioni

(84)

Sistemi per il recupero delle informazioni

IL LINGUAGGIO SQL

(85)

INTRODUZIONE

 Le interrogazioni devono essere scritte in un linguaggio formale con caratteristiche tali da renderlo adatto ad esprimere interrogazioni sulla BD, e da essere facilmente interpretato dal sistema. Il linguaggio generalmente usato si chiama SQL (Structured Query Language)

 È un linguaggio di interrogazione e manipolazione della base dati e delle informazioni in essa contenute

 Creato negli anni ’70 presso IBM , inizialmente solo come linguaggio di interrogazione. Ora è linguaggio di riferimento per DataBase relazionali.

Standardizzato grazie al lavoro di ISO (international standard organization) e ANSI (american national standard institute)

(86)

SQL

 originariamente "Structured Query Language", ora "nome proprio"

 linguaggio con varie funzionalità:

 contiene sia il DDL (schema) sia il DML(istanza)

 ne esistono varie versioni

 vediamo gli aspetti essenziali, non i dettagli

 prima proposta SEQUEL (1974);

 prime implementazioni in SQL/DS e Oracle (1981)

 dal 1983 ca. "standard di fatto"

 standard (1986, poi 1989 e infine 1992, 1999) - ISO, ANSI

 standard per i software che usano il modello relazionale

 recepito solo in parte

(87)

DDL, DML, DCL

 Data Definition Language (DDL)

 permette di creare e cancellare DB o di modificarne la struttura. Sono i comandi DDL a definire la struttura del DB e quindi i dati in esso contenuti. Ma non fornisce gli strumenti per modificare i dati stessi: per tale scopo si usa il DML. L’utente deve avere i permessi necessari per agire sulla struttura del DB che vengono dati tramite il DCL

 Data Manipulation Language (DML)

 permette di inserire, cancellare, modificare e leggere i dati all’interno delle tabelle di un DB. La struttura di questi dati deve essere già stata definita tramite il DDL. Il permesso di accedere ai dati deve essere assegnato all’utente tramite il DCL.

 Data Controlo Language (DCL)

 serve a fornire o revocare agli utenti i permessi per poter usare i comandi DML e DDL oltre agli stessi comandi DCL.

(88)

CREAZIONE DI TABELLE

Per definire una relazione (detta tabella nella terminologia SQL), si usa il comando “create table”: definisce uno schema di relazione e ne crea un’istanza vuota; specifica attributi, domini e vincoli; ad esempio

Libri(titolo, autore, codice_isbn)

(89)

CREATE TABLE, esempio

CREATE TABLE Impiegato(

Matricola CHAR(6) PRIMARY KEY, Nome CHAR(20) NOT NULL,

Cognome CHAR(20) NOT NULL, Dipart CHAR(15),

Stipendio NUMERIC(9) DEFAULT 0, FOREIGN KEY(Dipart) REFERENCES Dipartimento(NomeDip),

UNIQUE (Cognome,Nome) )

(90)

Domini

 Il “Dominio” indica i valori che un attributo può assumere e le operazioni che possono essere compiute su di esso.

 Domini elementari (predefiniti)

 Carattere: singoli caratteri o stringhe, anche di lunghezza variabile

 Bit: singoli booleani (flag) o stringhe di bit

 Numerici:

 esatti (es: numeric, decimal)

 approssimati (es: float)

 Data, ora, intervalli di tempo – UTC (Universal time coordinate)

 Introdotti in SQL:1999:

 Boolean

 BLOB, CLOB (Binary/Character large object): per grandi immagini e testi

 Domini definiti dall'utente (semplici, ma riutilizzabili)

(91)

VINCOLI

A ogni attributo possono essere associati dei vincoli

■ default: indica il valore che un attributo deve avere quando viene inserito un record che, in corrispondenza di quell’attributo non ha assegnato alcun valore

■ not null: i valori inseriti in quel campo devono essere diversi non nulli

■ Es: Cognome CHAR(20) not null

■ unique: il valore può comparire una volta sola

■ primary key: chiave primaria, (una sola, implica NOT NULL)

(92)

VINCOLI

■ Il valore di un attributo dichiarato NOT NULL va obbligatoriamente specificato quando si aggiunge un’ennupla alla relazione.

■ Un altro vincolo è l’eventuale chiave primaria dichiarata con l’opzione primary key. Gli attributi della chiave primaria non possono assumere valori NULL.

Quando nella definizione di una tabella sono dichiarati dei vincoli il sistema che gestisce la BD controlla che le operazioni che modificano la tabella inserendo nuove ennuple o modificando i valori di attributi non violino i vincoli dichiarati. Se un vincolo può essere violato l’operazione non viene eseguita e viene segnalata una condizione di errore

(93)

 Matricola CHAR(6) PRIMARY KEY

 Matricola CHAR(6), …,

PRIMARY KEY (Matricola)

PRIMARY KEY

(94)

CHIAVI ESTERNE

Vediamo come introdurre una chiave esterna attraverso il comando Foreign Key

create table studenti ( nome char(20),

matricola char(8) not null, provincia char(2),

anno_nascita smallint, primary key (matricola)

foreign key (codice) references CDL,

on delete no action, )

create table CDL ( facoltà char(20),

nome char(20) not null, primary key (codice), )

(95)

CHIAVI ESTERNE

Quando si dichiara un vincolo di chiave esterna, il sistema fa i seguenti controlli:

1. quando si inserisce un’ennupla nella tabella Studenti, o quando si modifica il campo chiave esterna, il valore della chiave esterna deve essere presente in un’ennupla della tabella CDL;

2. quando si elimina un’ennupla dalla tabella CDL, se il valore della sua chiave primaria è usato come valore di una chiave esterna di un’ennupla della tabella Studenti, allora sono possibili tre scelte:

a. on delete no action: per proibire la cancellazione dell’ennupla da CDL.

Questa opzione vale anche quando si modifica il valore della chiave primaria di CDL;

b. on delete cascade, per eliminare sia l’ennupla da CDL che tutte le ennuple di Studenti che usano il valore della chiave primaria dell’ennupla che si elimina;

c. on delete set null, per eliminare l’ennupla da CDL e porre a null il valore della chiave esterna di tutte le ennuple di Studenti che usano il valore della chiave primaria dell’ennupla che si elimina.

(96)

MODIFICA DEI DATI

Nuovi dati si inseriscono nella tabella con il comando INSERT.

Ad esempio, per aggiungere una nuova ennupla alla relazione Studenti si dà il comando

INSERT INTO Studenti VALUES ("Tizio", "081575", "MI", “1985”)

Per cambiare invece l’attributo Provincia da “MI” a “TO” per lo studente con Matricola "081575", si dà il comando:

UPDATE Studenti SET Provincia = “TO”

WHERE Matricola = "081575"

Per eliminare invece l’ennupla dello studente con matricola "081575", si dà il comando:

DELETE Studenti WHERE Matricola = "081575"

(97)

Transazioni in SQL

 Istruzioni fondamentali

 begin transaction: specifica l'inizio della transazione (le operazioni non vengono eseguite sulla base di dati)

 commit work: le operazioni specificate a partire dal begin transaction vengono eseguite

 rollback work: si rinuncia all'esecuzione delle operazioni specificate dopo l'ultimo begin transaction . Tutte le modifiche effettuate sui dati in precedenza (a partire dall’inizio della transazione) sono cancellate. Annulla la transazione

begin transaction;

update ContoCorrente set Saldo = Saldo – 10

where NumeroConto = 12345 ; update ContoCorrente

set Saldo = Saldo + 10

where NumeroConto = 55555 ; commit work;

(98)

RECUPERO DEI DATI:

IL COMANDO SELECT

 OBIETTIVI: Scrivere una query in linguaggio SQL

 selezionare ed elencare tutte le righe e le colonne di una tabella

 selezionare ed elencare determinate colonne di una tabella

 selezionare ed elencare le colonne di più tabelle

 Anche se la parola query può essere tradotta in interrogazione o domanda, una query SQL non è necessariamente una domanda, può essere un comando per svolgere una delle seguenti operazioni:

 creare o cancellare una tabella

 inserire, modificare o cancellare campi

 ricercare informazioni specifiche in più tabelle e restituire i risultati in un particolare ordine

 modificare i parametri di protezione di un database

(99)

ESEMPIO - I

 Si consideri il seguente schema relazionale

Catalogo ( ISBN, Titolo, CasaEd, AnnoEd)

 Supponiamo che interessi conoscere il titolo e la casa editrice dei libri pubblicati nel 2001. Occorre:

1. consultare la relazione Catalogo

(100)

ESEMPIO - II

2. considerare solo le ennuple in cui AnnoEd = 2001

3. prelevare da queste ennuple i valori degli attributi Titolo e CasaEd

 Questa sequenza di operazioni viene eseguita dal DBMS, purché gli venga trasmesso un opportuno comando (interrogazione) nel linguaggio SQL:

SELECT Titolo, CasaEd FROM Catalogo WHERE AnnoEd = 2001

dove SELECT, FROM e WHERE sono parole riservate del linguaggio SQL.

(101)

STRUTTURA DEL COMANDO

 Una interrogazione (query) SQL agisce sulle relazioni definite nella base di dati, e restituisce come risultato una relazione.

 questa viene in generale visualizzata sul monitor, oppure stampata; può anche essere memorizzata nella base di dati o può essere utilizzata in altre interrogazioni.

 Nei casi più semplici una interrogazione SQL deve specificare

 quali sono le informazioni che interessano

 in quali relazioni si trovano

 quali proprietà devono avere

(102)

SELECT

Quali sono le informazioni che interessano

SELECT Attributo1,Attributo2,...

è presente in ogni interrogazione e definisce lo schema della relazione risultato. Più avanti vedremo che può avere una forma più complessa.

Esempio:

SELECT Titolo, CasaEd significa che ci interessano il titolo e la casa editrice

Le singole colonne verranno elencate nello stesso ordine indicato.

Il comando Select in SQL equivale all’operazione di proiezione dell’algebra relazionale.

(103)

FROM

In quali relazioni si trovano

FROM Relazione1,Relazione2,...

è presente in ogni interrogazione e specifica quali relazioni occorre visitare per ottenere il risultato.

Esempio:

FROM Catalogo

significa che per estrarre le informazioni che interessano occorre prendere in esame la relazione Catalogo.

Per selezionare dati da un’altra tabella è sufficiente modificare la clausola FROM

(104)

WHERE

Quali proprietà devono essere soddisfatte

WHERE Condizione

La condizione è espressa sugli attributi delle relazioni specificate nella clausola FROM.

Può non essere presente, quando non si vogliono specificare condizioni.

Esempio:

WHERE AnnoEd = 2001

significa che interessano informazioni relative ai libri editi nel 2001.

(105)

ESEMPIO - I

 Abbiamo visto che l’interrogazione

SELECT Titolo, CasaED FROM Catalogo

WHERE AnnoEd = 2001 restituisce la relazione

contenente titolo e casa editrice dei libri editi nel 2001

(106)

ESEMPIO - II

Invece l’interrogazione

SELECT Titolo, CasaED FROM Catalogo

restituisce la relazione

contenente titolo e casa editrice di tutti i libri presenti nel catalogo

(107)

Riassumendo…

 Riepilogando la forma generale di un interrogazione SQL è, nei casi più semplici, la seguente:

SELECT Attributo1,Attributo2,...

[WHERE Condizione]

 Le parole in maiuscolo sono parole riservate del linguaggio SQL, sono fisse e specificano le clausole dell’ interrogazione; la clausola WHERE può mancare

 Le parole in minuscolo sono variabili, e rappresentano le relazioni, gli attributi, le condizioni che riguardano la specifica interrogazione

(108)

Nome Età Persone

Reddito

Andrea 27 21

Maria 55 42

Anna 50 35

Filippo 26 30

Luigi 50 40

Franco 60 20

Olga 30 41

Sergio 85 35

Luisa 75 87

Aldo 25 15

Andrea 27 21

Aldo 25 15

Filippo 26 30

Andrea 27 21

Aldo 25 15

Filippo 26 30

21 15 30 Reddito Nome Età

Persone

Reddito Andrea 27

Maria 55

Anna 50

Filippo 26 Luigi 50 Franco 60 Olga 30 Sergio 85 Luisa 75 Aldo 25

21

42 35 30 40 20 41 35 87 15

Nome e reddito delle persone con meno di trenta anni PROJNome, Reddito(SEL_Eta<30(Persone))

SELECT Nome, Reddito FROM Persone

WHERE Eta < 30

(109)

SELECT, abbreviazioni (alias)

SELECT Nome, Reddito FROM Persone

WHERE Eta < 30

SELECT p.nome as nome, p.reddito as reddito

FROM persone as p WHERE p.eta < 30

(110)

Selezione, senza proiezione

 Nome, età e reddito delle persone con meno di trenta anni SEL_Eta<30(Persone)

SELECT *

FROM Persone WHERE Eta < 30

(111)

Proiezione, senza selezione

 Nome e reddito di tutte le persone

PROJNome, Reddito(Persone) SELECT Nome, Reddito

FROM Persone

(112)

Condizione complessa

SELECT *

FROM Persone

WHERE reddito > 25 and (eta < 30 or eta > 60)

(113)

EVITARE I DUPLICATI

 Si consideri la seguente interrogazione

SELECT CasaEd FROM Catalogo

Se una casa editrice è presente nel catalogo con 1000 libri, il suo nome comparirà 1000 volte nel risultato

 Se vogliamo evitare che ciò avvenga, scriveremo SELECT DISTINCT CasaEd

FROM Catalogo

che ha come risultato le case editrici presenti nel catalogo, rappresentate una sola volta

(114)

DISTINCT

 In generale la specifica DISTINCT nella clausola SELECT elimina i duplicati dal risultato

 La forma generale di un interrogazione SQL che abbiamo visto fin qui è quindi la seguente:

SELECT [DISTINCT] Attributo1,Attributo2,...

[ WHERE Condizione]

dove le parti racchiuse tra parentesi quadre possono mancare

(115)

L’uso di *

 Nella clausola SELECT si può specificare * in luogo della lista di attributi; in tal caso il risultato contiene tutti gli attributi delle relazioni specificate nella clausola FROM.

 L’asterisco (*) di select * indica al database di fornire TUTTE le colonne associate alla tabella

SELECT * FROM Catalogo

WHERE CasaEd = “Feltrinelli”

 Restituisce come risultato