Sistemi per il recupero delle informazioni

Sistemi per il recupero delle informazioni

RIASSUNTO

ESEMPIO

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)

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

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

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

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

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

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

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

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

Algebra relazionale

 Insieme di operatori

 su relazioni

 che producono relazioni (tabelle)

 e possono essere composti per svolgere operazioni più complesse

 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

Paternità

Padre Figlio

Adamo Caino

Abramo Isacco

Adamo Abele

 Genitore  Padre (Paternità)

Padre Figlio

Adamo Caino

Abramo Isacco

Adamo Abele

Genitore

RIDENOMINAZIONE

ESEMPIO DI UNIONE

ESEMPIO DI INTERSEZIONE

ESEMPIO DI DIFFERENZA

selezione

proiezione

operatori "ortogonali“

selezione:

decomposizione orizzontale proiezione:

decomposizione verticale

Cognome Filiale Stipendio Matricola

Neri Milano 64

5998

Neri Napoli 55

7309

Rossi Roma 64

5698

Rossi Roma 44

9553

 cognome e filiale di tutti gli impiegati



(Impiegati)

ESEMPIO PROIEZIONE

ESEMPIO RESTRIZIONE

Impiegati che

• guadagnano più di 50

• guadagnano più di 50 e lavorano a Milano

• hanno lo stesso nome della filiale presso cui lavorano

Operatori booleani

 Connettivi logici

  (AND),

  (OR),

  (NOT)

 Operatori di confronto

 = (uguale)  (diverso)

  (maggiore)  (minore)

  (maggiore o uguale)

  (minore o uguale)

Cognome Filiale Stipendio Matricola

Neri Milano 64

5998

Rossi Roma 55

7309

Neri Napoli 64

5698

Milano Milano 44

9553 Impiegati

Milano Milano 44

9553 Neri Napoli 64

5698

 impiegati che guadagnano più di 50



(Impiegati)

Impiegati

Cognome Filiale Stipendio Matricola

Neri Milano 64

5998

Rossi Roma 55

7309

Neri Napoli 64

5698

Milano Milano 44

9553

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

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

Rossi Roma 55

7309

Neri Napoli 64

5698

Milano Milano 44

9553

Neri Milano 64

5998

Selezione e proiezione

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

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



(Impiegati)



(  )

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

1 25

2 13

3 27

4 28

Numero Voto

1 Mario Rossi

2 Nicola Russo

3 Mario Bianchi

4 Remo Neri

Numero Candidato

25 Mario Rossi

13 Nicola Russo

27 Mario Bianchi

28 Remo Neri

Voto Candidato

1 2 3 4 Numero

PRODOTTO (Join)

ESEMPIO - I

 join completo: ogni ennupla contribuisce al risultato

ESEMPIO - II

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

ESEMPIO - III

 Join vuoto: caso limite

 potrebbe anche succedere che nessuna ennupla trovi il corrispettivo

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’

Rossi A

Neri B

Bianchi B

Impiegato Reparto Impiegati

A Mori

B Bruni

B Bruni

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

Sistemi per il recupero delle informazioni

DATABASE MANAGEMENT SYSTEM

(DBMS)

COS’E’ UNA BASE DATI

Una base di dati è una raccolta di dati permanenti suddivisi in due categorie:

 I METADATI

 I DATI

COS’E’ UNA BASE DATI

I METADATI

i metadati, ovvero lo schema della base di dati, sono una raccolta di definizioni che descrivono

 la struttura di alcuni insiemi dati,

 le restrizioni sui valori ammissibili dei dati

 le relazioni esistenti fra gli insiemi.

Lo schema va definito prima di creare i dati ed è indipendente dalle applicazioni che usano la base di dati.

COS’E’ UNA BASE DATI

I DATI

i dati, le rappresentazioni dei fatti conformi alle definizioni dello schema, con le seguenti caratteristiche:

a) sono organizzati in insiemi omogenei, fra i quali sono definite delle relazioni.

b) sono molti, in assoluto e rispetto ai metadati, e non possono essere gestiti in memoria temporanea;

c) sono permanenti, cioè, una volta creati, continuano ad esistere finché non sono esplicitamente rimossi;

d) sono accessibili mediante transazioni;

e) sono protetti sia da accesso da parte di utenti non autorizzati, sia da corruzione dovuta a malfunzionamenti hardware e software;

f) sono utilizzabili contemporaneamente da utenti diversi.

Le caratteristiche delle basi di dati sono garantite da un sistema per la gestione di basi di dati (DBMS, Data Base Management System), che ha il controllo dei dati e li rende accessibili agli utenti autorizzati.

Un DBMS è un sistema centralizzato o distribuito che offre opportuni linguaggi

a) per definire lo schema della base di dati,

b) per scegliere le strutture dati per la memorizzazione dei dati, c) per usare la base di dati interattivamente o da programmi.

DataBase Management System (DBMS) - I

DataBase Management System (DBMS) - II

 Un sistema di gestione di basi di dati è un sistema software in grado di gestire collezioni di dati che siano grandi, condivise e persistenti, assicurando la loro affidabilità e privatezza.

 Un DBMS deve essere efficiente ed efficace.

 Esempi di prodotti software disponibili sul mercato: Access, DB2, Oracle, Informix, Sybase, SQLServer

I DATABASE SONO...

 grandi

 dimensioni (molto) maggiori della memoria centrale dei sistemi di calcolo utilizzati

 il limite deve essere solo quello fisico dei dispositivi

 persistenti

 hanno un tempo di vita indipendente dalle singole esecuzioni dei programmi che le utilizzano

 condivisi

 ogni organizzazione è divisa in settori o comunque svolge diverse

attività . Ciascun settore/attività ha un (sotto) sistema informativo (non necessariamente disgiunto)

 Una base di dati e' una risorsa integrata, condivisa fra applicazioni

 Conseguenze:

 Attivita' diverse su dati condivisi: meccanismi di autorizzazione

 Accessi di più utenti ai dati condivisi:controllo della concorrenza

I DBMS GARANTISCONO…

 PRIVATEZZA

 Si possono definire meccanismi di autorizzazione

 l'utente A è autorizzato a leggere tutti i dati e a modificare quelli sul ricevimento

 l'utente B è autorizzato a leggere X e a modificare Y

 AFFIDABILITA` (per le basi di dati):

 resistenza a malfunzionamenti hardware e software

 una base di dati è una risorsa pregiata e quindi deve essere conservata a lungo termine

I DBMS DEVONO ESSERE …

 EFFICIENTI

 Cercano di utilizzare al meglio le risorse di spazio di memoria (principale e secondaria) e tempo (di esecuzione e di risposta)

 I DBMS, con tante funzioni, rischiano l'inefficienza e per questo ci sono grandi investimenti e competizione

 L’efficienza è anche il risultato della qualità delle applicazioni

 EFFICACI

 Cercano di rendere produttive le attività dei loro utilizzatori, offrendo funzionalità articolate, potenti e flessibili:

 il corso è in buona parte dedicato ad illustrare come i DBMS perseguono l'efficacia

FUNZIONALITA’ DEI DBMS

Un DBMS offre specifiche funzionalità per i seguenti scopi:

– definizione di basi di dati;

– uso dei dati;

– controllo dei dati;

– amministrazione della base di dati;

– distribuzione dei dati.

ARCHITETTURA STANDARD - I

database Livello logico Livello

esterno

Livello interno Livello esterno

Livello esterno utente utente

utente utente utente

LINGUAGGI PER DATABASE

 Un altro contributo all’efficacia: disponibilità di vari linguaggi e interfacce

 linguaggi testuali interattivi (SQL)

 comandi (SQL) immersi in un linguaggio ospite (Pascal, Java, C ...)

 comandi (SQL) immersi in un linguaggio ad hoc, con anche altre funzionalità (p.es. per grafici o stampe strutturate)

 con interfacce amichevoli (senza linguaggio testuale)

Controllo dei dati

Una caratteristica molto importante dei DBMS è il tipo di meccanismi offerti per garantire le seguenti proprietà di una base di dati:

 integrità

 affidabilità

 sicurezza

TRANSAZIONI (PER L‘UTENTE)

 Esempi:

 versamento presso uno presso sportello bancario

 emissione di certificato anagrafico

 dichiarazione presso l’ufficio di stato civile

 prenotazione aerea

 Due accezioni

 Per l'utente:

 programma a disposizione, da eseguire per realizzare una funzione di interesse

 Per il sistema:

 sequenza indivisibile di operazioni

Sistemi per il recupero delle informazioni

VISTE

Viste

 Rappresentazioni diverse per gli stessi dati (schema esterno)

 Relazioni di base: contenuto autonomo

 Relazioni derivate:

 relazioni il cui contenuto è funzione del contenuto di altre relazioni (definito per mezzo di interrogazioni)

 Le relazioni derivate possono essere definite su altre derivate

 Due tipi di relazioni derivate:

 viste materializzate

 relazioni virtuali (o viste)

Viste materializzate e virtuali

 relazioni derivate memorizzate nella base di dati

 vantaggi:

 immediatamente disponibili per le interrogazioni

 svantaggi:

 ridondanti

 appesantiscono gli aggiornamenti

 sono raramente supportate dai DBMS

 relazioni virtuali (o viste):

 sono supportate dai DBMS (tutti)

 una interrogazione su una vista viene eseguita "ricalcolando" la vista (o quasi)

Viste, esempio

 una vista:

Supervisione =

PROJ Impiegato, Capo (Afferenza JOIN Direzione)

A Mori

B Bruni

Reparto Capo

Rossi A

Neri B

Bianchi B

Impiegato Reparto

Bianchi B B Bruni

Afferenza Direzione

Viste, motivazioni

 Schema esterno: ogni utente vede solo

 ciò che gli interessa e nel modo in cui gli interessa, senza essere distratto dal resto

 ciò che e' autorizzato a vedere (autorizzazioni)

 Strumento di programmazione:

 si può semplificare la scrittura di interrogazioni: espressioni complesse e sottoespressioni ripetute

 L'utilizzo di viste non influisce sull'efficienza delle interrogazioni

Viste e aggiornamenti, attenzione

 Vogliamo inserire, nella vista, il fatto che Lupi ha come capo Bruni;

oppure che Belli ha come capo Falchi; come facciamo?

Afferenza Direzione

A Mori

B Bruni

Reparto Capo

Rossi A

Neri B

Impiegato Reparto

Neri B

Neri B BBBB BruniBruniBruniBruni Verdi A BBBBC BruniBruniBruniBruniBruni

Rossi Neri Impiegato

Rossi Neri Rossi

Neri Verdi Supervisione

Mori Bruni

Capo Mori Bruni

Mori Bruni Bruni Bruni Mori

Viste e aggiornamenti

 "Aggiornare una vista":

 modificare le relazioni di base in modo che la vista, "ricalcolata"

rispecchi l'aggiornamento

 L'aggiornamento sulle relazioni di base corrispondente a quello specificato sulla vista deve essere univoco

 In generale però non è univoco!

 Ben pochi aggionamenti sono ammissibili sulle viste

52

Sistemi per il recupero delle informazioni

IL LINGUAGGIO SQL

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)

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

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.

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)

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

Domini

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

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)

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

 Matricola CHAR(6) PRIMARY KEY

 Matricola CHAR(6), …,

PRIMARY KEY (Matricola)

PRIMARY KEY

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

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.

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

Vincolo di integrità referenziale

Azioni compensative

 Esempio:

 Viene eliminata una ennupla causando una violazione

 Comportamento “standard”:

 Rifiuto dell'operazione

 Azioni compensative:

 Eliminazione in cascata

 Introduzione di valori nulli

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"

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;

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

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

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.

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

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.

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

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.

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

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

Riassumendo…

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

SELECT Attributo1,Attributo2,...

FROM Relazione1,Relazione2,...

[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

Selezione e proiezione

 Nome e reddito delle persone con meno di trenta anni

PROJNome, Reddito(SELEta<30(Persone))

SELECT Nome, Reddito FROM Persone

WHERE Eta < 30

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

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

Selezione, senza proiezione

 Nome, età e reddito delle persone con meno di trenta anni

SEL_Eta<30(Persone)

SELECT *

FROM Persone WHERE Eta < 30

Proiezione, senza selezione

 Nome e reddito di tutte le persone

PROJNome, Reddito(Persone) SELECT Nome, Reddito

FROM Persone

Condizione complessa

SELECT *

FROM Persone

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

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

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

FROM Relazione1,Relazione2,...

[ WHERE Condizione]

dove le parti racchiuse tra parentesi quadre possono mancare

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

Le parole SELECT e FROM consentono a una query di caricare dei dati.

La parola chiave DISTINCT limita l’output delle query poiché consente di escludere i valori duplicati di una colonna.

E’ possibile creare una query generica e includere tutte le colonne con l’istruzione SELECT *. E’ anche possibile selezionare solo alcune colonne e anche modificare l’ordine in cui devono essere presentate.

RIEPILOGO

Obiettivi

 ampliare la query con qualche nuovo termine

 introdurre gli operatori.

In particolare impareremo a:

 capire cos’è una espressione e come si utilizza

 capire cos’è una condizione e come si utilizza

 familiarizzare con la clausola WHERE

 imparare ad usare gli operatori aritmetici, di confronto, di caratteri, logici e di insiemi

 conoscere altri utili operatori

ESPRESSIONI, CONDIZIONI E OPERATORI

ESPRESSIONI

 La definizione di espressione è semplice: un’espressione restituisce un valore

 Nella seguente istruzione, NOME, INDIRIZZO, TELEFONO E RUBRICA sono espressioni:

SELECT NOME, INDIRIZZO, TELEFONO, RUBRICA FROM RUBRICA;

 NOME è

 La seguente espressione:

WHERE NOME = ‘ROSSI’

contiene una condizione di una espressione booleana. Questa condizione potrà essere TRUE (vera) o FALSE (falsa) rispettivamente se la colonna NOME contiene ROSSI oppure no.

CONDIZIONI - I

 Tutte le volte che si vuole trovare un particolare elemento o gruppo di elementi in un database, occorre specificare una o più condizioni.

 Le condizioni sono introdotte dalla clausola WHERE.

 nell’esempio precedente la condizione è NOME = ‘ROSSI’. Per trovare tutti gli impiegati che hanno lavorato più di 100 ore la condizione potrebbe essere: NUMERODIORE > 100

 Le condizioni consentono di effettuare query selettive. Nella forma più comune includono una variabile, una costante e un operatore di confronto.

 Variabile……….. NOME

 Costante………..’ROSSI’

 Operatore di confronto………. >

 Per scrivere una query condizionale bisogna conoscere la clausola WHERE e gli operatori. La condizione presente nella clausola WHERE può avere una struttura molto complessa

CONDIZIONI - II

 In generale le condizioni sono formate combinando predicati con gli operatori booleani and, or e not

 Predicato: è una condizione semplice del tipo E1 cfr E2 ove:

 cfr è un operatore di confronto, cioè uno degli operatori

= < > <= >= <> (diverso)

 E1 ed E2 sono espressioni, che possono essere attributi, costanti oppure espressioni formate con gli usuali operatori aritmetici. Molto spesso E1 è un attributo. E2 può essere un comando SELECT

 Esempi

 · AnnoEd > 1980 and CasaEd = “Feltrinelli”

 · AnnoEd = 2000 and (CasaEd = “Einaudi” or CasaEd = “Mondadori”)

 I predicati hanno valore true (vero) oppure false (falso).

TABELLA DI VERITA’

Gli operatori booleani rispettano le seguenti tabelle di verità:

true and true = true true or true = true

not true = false

true and false = false true or false = true not false = true

false and false = false false or false = false

LA CLAUSOLA WHERE

 La sintassi della clausola WHERE è la seguente:

WHERE <condizione di ricerca>

 La condizione presente nella clausola WHERE è ottenuta combinando predicati con gli operatori booleani. Gli attributi che compaiono nei predicati devono appartenere alle relazioni presenti nella clausola FROM

 La clausola WHERE rende selettive le query, senza questa clausola la query visualizzerebbe tutti i record della tabella

 Consideriamo il solito schema di relazione Catalogo e una sua istanza

LA CLAUSOLA WHERE

SELECT Titolo, CasaEd FROM Catalogo

WHERE Anno = 2001 and CasaEd = “Einaudi”

SELECT Titolo, CasaEd FROM Catalogo

WHERE Anno = 2001 or CasaEd = “Einaudi”

SELECT Titolo, CasaEd FROM Catalogo

WHERE Anno = 2000 and CasaEd <>

“Feltrinelli”

ESEMPIO

SELECT CasaEd, Anno FROM Catalogo

WHERE Titolo = “L’amante” and

Anno = (SELECT max(Anno) FROM Catalogo WHERE Titolo =

“L’amante” )

 Viene dapprima calcolata la SELECT tra parentesi, ed il suo risultato viene utilizzato per valutare la condizione; La SELECT esterna restituisce come risultato la CasaEd e L’Anno della più recente edizione dell’ Amante presente nel Catalogo

 In questo esempio è stato fatto uso di una struttura detta SOTTOSELECT , o SELECT annidata. Questa ha lo scopo di estrarre dal DB un valore da utilizzare in una espressione. Si osservi che la Sottoselect ha come risultato un singolo valore, altrimenti il confronto non si può effettuare

E’ un potente gruppo di strumenti a base della conoscenza del linguaggio SQL

Gli operatori sono gli elementi utilizzati all’interno delle espressioni per specificare le condizioni necessarie a caricare i dati.

Possono essere divisi nei seguenti gruppi:

 aritmetici

 di confronto

 di caratteri

 logici

 di insieme

GLI OPERATORI

Non funziona con i tipi di dati che hanno cifre decimali I primi quattro operatori si spiegano da soli.

L’operatore modulo restituisce il resto di una divisione.

Ad esempio:

5 % 2 = 1 6 % 2 = 0

1. + (somma)

2. - (sottrazione)

3. / (divisione)

4. * (moltiplicazione) 5. % (modulo o resto)

GLI OPERATORI ARITMETICI

GLI OPERATORI ARITMETICI

2 * 6 + 9 / 3

12 + 3 = 15

mentre l’espressione

2 * (6 + 9) / 3

2 * 15 / 3 = 10

Se vengono inseriti più operatori aritmetici in una espressione senza parentesi, essi vengono valutati nell’ordine: moltiplicazione, divisione, modulo, somma e sottrazione.

Ad esempio:

SQL> SELECT * FROM PREZZO

ELEMENTO PREZZOINGROSSO

Pomodori 3,40

Patate 5,10

Banane 6,70

Rape 4,50

Arance 8,90

Mele 2,30

SQL> SELECT ELEMENTO, PREZZOINGROSSO, PREZZOINGROSSO + 1.50 FROM PREZZO

ELEMENTO PREZZOINGROSSO PREZZOINGROSSO + 1.50

Pomodori 3,40 4,90

Patate 5,10 6,60

Banane 6,70 8,20

Rape 4,50 6,00

Arance 8,90 10,40

Mele 2,30 4,80

La terza colonna (PREZZOINGROSSO + 1,50) non si trova nella tabella originale (in entrambi i casi sono state selezionate con il carattere * tutte le colonne).

SQL consente di creare colonne virtuali o derivate combinando o modificando le colonne esistenti.

OPERATORI ARITMETICI: SOMMA (+)

E’ possibile assegnare una intestazione più comprensibile alla nuova colonna:

ELEMENTO PREZZOINGROSSO PREZZODETTAGLIO

Pomodori 3,40 4,90

Patate 5,10 6,60

Banane 6,70 8,20

Rape 4,50 6,00

Arance 8,90 10,40

Mele 2,30 3,80

SQL> SELECT ELEMENTO, PREZZOINGROSSO,

(PREZZOINGROSSO + 1.50) PREZZODETTAGLIO FROM PREZZO

OPERATORI ARITMETICI: SOMMA (+)

L’operatore meno svolge due funzioni, la prima è quella di cambiare segno ad un numero:

SQL> SELECT * FROM MINMAX

REGIONE TEMPMAX TEMPMIN

Piemonte -4 10

Toscana 4 13

Sicilia 10 19

Lombardia -2 9

Friuli -3 8

REGIONE TEMPMAX TEMPMIN

Piemonte 4 -10

Toscana -4 -13

Sicilia -10 -19

Lombardia 2 -9

Friuli 3 -8

SQL> SELECT REGIONE, -TEMPMAX, -TEMPMIN FROM MINMAX

OPERATORI ARITMETICI: SOTTRAZIONE (-)

OPERATORI ARITMETICI: SOTTRAZIONE (-)

REGIONE MINIME MASSIME DIFFERENZE

Piemonte -4 10 14

Toscana 4 13 9

Sicilia 10 19 9

Lombardia -2 9 11

Friuli -3 8 11

SQL> SELECT REGIONE, TEMPMAX MINIME, TEMPMIN MASSIME,

(TEMPMIN - TEMPMAX) DIFFERENZA FROM MINMAX;

Oltre che aver creato la nuova colonna questa query ha corretto (solo sullo schermo) i nomi di quelle errate.

La seconda (e ovvia) funzione dell’operatore meno è quella di sottrarre i valore di una colonna da quelli di un’altra colonna.

Ad esempio

L’operatore divisione ha un solo significato, per vedere gli effetti di una vendita a metà prezzo basta digitare la seguente istruzione:

SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO/2) PREZZOVENDITA FROM PREZZO

ELEMENTO PREZZOINGROSSO PREZZOVENDITA

Pomodori 3,40 1,70

Patate 5,10 2,55

Banane 6,70 3,35

Rape 4,50 2,25

Arance 8,90 4,45

Mele 2,30 1,15

OPERATORI ARITMETICI: DIVISIONE (/)

Anche l’’operatore moltiplicazione è semplice da usare, ad esempio questa query visualizza l’effetto di uno sconto del 10% sui prezzi di tutti i prodotti:

SQL> SELECT ELEMENTO PRODOTTO, PREZZOINGROSSO, (PREZZOINGROSSO*0.9) NUOVOPREZZO FROM PREZZO;

ELEMENTO PREZZOINGROSSO NUOVOPREZZO

Pomodori 3.40 3.06

Patate 5.10 4.59

Banane 6.70 6.03

Rape 4.50 4.05

Arance 8.90 8.01

Mele 2.30 2.07

OPERATORI ARITMETICI: MOLTIPLICAZIONE (*)

L’operatore modulo restituisce il resto intero di una operazione di divisione.

Esempio:

SQL> SELECT * FROM RESTI

NUMERATORE DENOMINATORE

10 5

8 3

23 9

1024 16

E’ possibile creare una nuova colonna, RESTO, dove registrare il resto della divisione tra NUMERATORE e DENOMINATORE

SQL> SELECT NUMERATORE, DENOMINATORE, (NUMERATORE % DENOMINATORE) RESTO

FROM RESTI ^NUMERATORE DENOMINATORE RESTO

10 5 0

8 3 2

23 9 5

1024 16 0

OPERATORI ARITMETICI: MODULO (%)

Questi operatori confrontano le espressioni e restituiscono uno di questi tre valori: TRUE, FALSE, Unkown. I primi due sono semplici da spiegare, TRUE significa vero e FALSE significa falso, il terzo, Unknow, identifica l’assenza di dati in una colonna, cioè NULL.

Molte implementazioni SQL cambiano Unknown in FALSE e forniscono un operatore speciale, IS NULL, per verificare la condizione NULL (assenza di dati).

SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO = NULL;

No row selected

SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO IS NULL; ELEMENTO PREZZOINGROSSO

Limoni

Nel database la colonna prezzoingrosso della riga Limoni non contiene dati (non è zero)

OPERATORI DI CONFRONTO

Nella clausola WHERE il segno uguale è l’operatore di confronto più utilizzato, molto comodo per selezionare un valore tra tanti.

SQL> SELECT * FROM AMICI;

COGNOME NOME CITTA DATA DI NASCITA TELEFONO

ROSSI ALE MILANO 1/1/1970 02 3425678

BIANCHI SABY TORINO 25/5/1985 011 6707221

BROWN JO PISA 12/10/1968 050 880245

NERI ALE BOLOGNA 13/11/1986 051 6711

SQL> SELECT * FROM AMICI WHERE NOME = ‘ALE’;

COGNOME NOME CITTA DATA DI

NASCITA TELEFONO

ROSSI ALE MILANO 1/1/1970 02 3425678

NERI ALE BOLOGNA 13/11/1986 051 6711

SQL> SELECT * FROM AMICI WHERE NOME = ‘Ale’;

no row selected.

OPERATORI DI CONFRONTO: =

questi operatori operano nel seguente modo modo:

SQL> SELECT * FROM PREZZO;

ELEMENTO PREZZOINGROSSO

Pomodori 3.40

Patate 5.10

Banane 6.70

Rape 4.50

Arance 8.90

Mele 2.30

SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO > 4.50;

ELEMENTO PREZZOINGROSSO

Patate 5.10

Banane 6.70

Arance 8.90

SQL> SELECT * FROM PREZZO WHERE PREZZOINGROSSO >= 4.50;

ELEMENTO PREZZOINGROSSO

Patate 5.10

Banane 6.70

Rape 4.50

Arance 8.90

Non si usano apici per racchiudere il numero 4.50

OPERATORI DI CONFRONTO: > , >=

questi operatori operano in senso inverso al precedente:

COGNOME NOME CITTA DATA DI

NASCITA TELEFONO

ROSSI ALE MILANO 1/1/1970 02 3425678

BIANCHI SABY TORINO 25/5/1985 011 6707221

BROWN JO PISA 12/10/1968 050 880245

NERI ALE BOLOGNA 13/11/1986 051 6711

COGNOME NOME CITTA DATA DI NASCITA TELEFONO

ROSSI ALE MILANO 1/1/1970 02 3425678

BROWN JO PISA 12/10/1968 050 880245

SQL> SELECT * FROM AMICI;

SQL> SELECT * FROM AMICI

WHERE CITTA <= ‘MILANO’ ;

OPERATORI DI CONFRONTO: <, <=

Operatore di disuguaglianza: consente di trovare dati escludendone altri, cioè il simbolo (<>) oppure (!=) si legge “diverso da”.

Per trovare gli amici tranne ALE (cioè con il nome diverso da ALE):

COGNOME NOME CITTA DATA DI NASCITA TELEFONO

BIANCHI SABY TORINO 25/5/1985 011 6707221

BROWN JO PISA 12/10/1968 050 880245

In molte implementazione SQL è indifferente usare la forma (<>) anzichè (!=)

SQL> SELECT * FROM AMICI WHERE NOME <> ‘ALE’;

OPERATORI DI CONFRONTO: <>, !=

NOME POSIZIONE NUMEROPARTE

FEGATO DESTRA-ADDOME 1

CUORE PETTO 2

FARINGE GOLA 3

VERTEBRE CENTRO-DORSO 4

INCUDINE ORECCHIO 5

RENE DORSO 6

OPERATORI DI CARATTERE

Consentono di manipolare il modo in cui debbono essere rappresentate le stringhe durante la preparazione delle condizioni che selezionano i dati.

Come fare a trovare tutte le parti che si trovano nella zona dorsale del corpo? Osservando la

tabella è possibile individuarne due, ma hanno nomi differenti.