1.8. Interrogazione della base di dati (query) 1.8.1. Cosa è una query Le operazioni di interrogazione (Query di selezione, dette anche generalmente query) non alterano lo stato del database, nel senso che non inseriscono né aggiornano i valori dei dati, ma effettuano soltanto operazioni di lettura degli stessi. Esse hanno l'obiettivo di estrarre informazioni utili e sintetiche da tutte quelle memorizzate. Ad esempio: - in una base di dati Agenda: il numero di telefono di una persona; - in una base di dati Anagrafica: tutti gli indirizzi di una determinata categoria di individui; - nella base di dati Editrice di cui agli esempi precedenti: tutti i volumi di un determinato Autore, di un determinato genere e che costano meno di una data somma. - in una base di dati amministrativa: tutte le fatture inevase oppure tutti gli ordini emessi per i quali la merce non sia ancora pervenuta. Ogni query è un programma (in linguaggio QBE o in SQL) che può essere memorizzato e può costituire un "archivio di interrogazioni" da ripetere quando si voglia. In una base di dati relazionale ogni query equivale alla costruzione di una nuova tabella (relazione), tratta da quelle memorizzate (anche un singolo record è un caso particolare di relazione). La tabella viene esposta per la consultazione da parte dell'utente o può essere stampata come tabella o attraverso un report, ma non si aggiunge all'insieme delle tabelle che costituisce il database: lo stato del database resta invariato. Query relativamente banali sono quelle cui si risponde consultando una singola tabella, query relativamente più complesse quelle che richiedono l'accesso a più tabelle. Nel complesso, esse si possono classificare in: - selezione e proiezione, che operano su un'unica tabella, - join, che opera su più tabelle. 1.8.2. Query su una tabella: la proiezione La più semplice query è la proiezione (o selezione di colonne), che seleziona alcune colonne di una tabella. Nei linguaggi QBE o visuali si ottiene banalmente (così come in SQL) con una istruzione che identifica quali colonne si vogliono estrarre. L'esempio è riferito al database Editrice (vedi RICHIAMO). RICHIAMO: § 1.1.4 - Data base relazionale: definizione ed esempio 1.8.2.1. Proiezione in SQL (approfondimento) In SQL, la proiezione si esprime con un comando del tipo: SELECT <elenco di colonne> FROM <nome tabella> e seleziona le colonne listate dalla tabella data. Un asterisco al posto dell'elenco di colonne significa che la proiezione si estende a tutte le colonne e produce dunque esattamente la tabella data.: SELECT * FROM <nome tabella> 1.8.2.2. Proiezioni in Editrice (esempio) Esempio 1) La proiezione che produce l'estrazione delle colonne CodiceLibro e Genere dalla tabella LIBRI, si esprime in SQL come: SELECT CodiceLibro, Genere FROM LIBRI e produce la tabella che segue Esempio di proiezione (Esempio 1) CodiceLibro AAA1111 AAA2346 AAA2789 AAA2878 AAA3456 AAA7890 Genere Avventura Gialli Avventura Poesia Gialli Avventura Esempio 2) SELECT * FROM LIBRI riproduce integralmente la tabella LIBRI. L'istruzione, sintatticamente corretta, non ha senso in caso di pura proiezione, ma lo ha nel caso che la proiezione sia realizzata su una tabella risultato di selezione o di join, (vedi in seguito). 1.8.2.3. Clausola DISTINCT (approfondimento) Eliminando con la proiezione alcune colonne, ne possono scaturire righe eguali. Per eliminare i "doppioni" occorre usare la clausola DISTINCT (che significa “fornisci solo righe diverse fra loro”): SELECT DISTINCT <elenco di colonne> FROM <nome tabella> Ad esempio, la proiezione SELECT DISTINCT Genere FROM LIBRI produce l'elenco dei generi, ciascuno una sola volta. Clausola DISTINCT Genere Avventura Gialli Poesia 1.8.3. Query su una tabella: la selezione Una query leggermente più complessa della proiezione è la selezione di righe (o semplicemente selezione), che seleziona in una tabella le sole righe che verificano un criterio di selezione assegnato (p.e. i libri di costo minore di 20 euro). Il criterio di selezione è una espressioni logica costruita con gli operatori di confronto, logici e con quelli che usano caratteri Jolly (vedi RICHIAMO). Nel linguaggio SQL (vedi approfondimento) l’espressione logica viene esplicitamente scritta nell’istruzione, mentre nel linguaggio QBE essa va individuata in qualche modo attraverso apposite tabelle. Una query può comprendere una selezione di riga ed una proiezione ed allora il DBMS prima seleziona le righe in base al criterio di selezione e dalla tabella così ottenuta poi seleziona le colonne della proiezione. Per fissare le idee, si vedano gli esempi, tratti dal database Editrice (vedi RICHIAMO). RICHIAMO: § 1.4.1 - Espressioni ed operatori logici RICHIAMO: § 1.1.4 - Data base relazionale: definizione ed esempio 1.8.3.1. Selezione in SQL (approfondimento) In SQL, nella forma più semplice, la selezione si esprime con un comando del tipo: SELECT * FROM <nome tabella> WHERE <criterio di selezione> e, per una selezione con proiezione: SELECT <elenco di campi> FROM <nome tabella> WHERE <criterio di selezione> 1.8.3.2. Qualche selezione nel database Editrice (esempio) Gli esempi che seguono sono tutti riferiti al database Editrice di cui al richiamo. Esempio 1): Selezione di libri di costo contenuto La selezione: SELECT * FROM LIBRI WHERE Costo < 20,00 produce il risultato (libri di costo inferiore a 20 euro): i libri di costo minore di 50 euro CodiceLibro Titolo Costo Genere AAA3456 Il commissario Angel € 14,00 Gialli AAA2346 Squadra omicidi € 15,50 Gialli Esempio 2):Valori di un campo in un intervallo assegnato L'istruzione SELECT * FROM LIBRI WHERE Costo <= 25 AND Costo >= 15 seleziona, nella tabella LIBRI, quelli di costo compreso nell'intervallo 15-25 euro (si potrebbe anche usare la clausola "BETWEEN 15 And 25"). Esempio 2 CodiceLibro Titolo CodiceLibro Titolo AAA2346 Squadra omicidi AAA2789 Uno contro tutti Costo Genere Costo Genere € 15,50 Gialli € 20,80 Avventura Esempio 3): Selezione con operatori logici su più campi L'istruzione SELECT Titolo, Costo FROM LIBRI WHERE Genere = “Gialli” And Costo < 15 seleziona tutti i libri gialli di costo minore di 15 euro (uno solo nel nostro caso) e ne evidenzia solo titolo e costo, realizzando dunque la proiezione su alcune colonne della tabella ottenuta con una selezione di righe. Esempio 3 Titolo Costo Genere Il commissario Angel € 14,00 Gialli Esempio 4):Ricerca degli autori di un libro L'istruzione SELECT Codfisc FROM AUTORI-LIBRI WHERE CodiceLibro=”AAA3456”; seleziona, in AUTORI-LIBRI, tutti i codici fiscali degli autori del libro AAA3456. Esempio 4 CodFisc BNCGUO80B10F835W BRNBRN57A30F839W 1.8.4. Ordinamento dei risultati di una query È possibile ottenere le righe della tabella selezionata o proiettata in ordine crescente o decrescente rispetto ai valori di un attributo; a tale scopo, alla operazione di selezione occorre aggiungere appositi criteri di ordinamento. L'ordinamento, oltre che a valle di selezioni o proiezioni, può avvenire anche a valle delle altre query più complesse (vedi in seguito). 1.8.4.1. Ordinamento in SQL (approfondimento) In SQL, l'ordinamento si ottiene aggiungendo all'istruzione SELECT la clausola ORDER BY <lista dei criteri di ordinamento> [ASC | DESC] ove: - la lista comprende la notazione [ASC | DESC] che significa che nella frase può essere scritto ASC, oppure DESC, oppure NULL, il nome del campo rispetto al quale si vuole ordinare e, se questo non è chiave, quello di ulteriori campi da prendere in esame nell'ordine, in caso di eguaglianza del primo; - ASC significa ordine ascendente (crescente), DESC in ordine discendente (decrescente) e per default l'ordinamento è ascendente. Per un esempio si veda l' unità formativa della query con valore calcolato. 1.8.5. Query con valori calcolati Regole di buona tecnica nella creazione del database (vedi RICHIAMO) vietano di memorizzare in una tabella dati che siano funzioni l'uno dell'altro, ricavabili da una formula, in quanto costituirebbero inutili e dannose duplicazioni di dati. Se ad esempio un campo di una tabella fosse "imponibile" ed un altro "aliquota IVA", non sarebbe lecito inserire anche il campo "costo totale" (= Imponibile + Imponibile * Aliquota): si pensi alla necessità di aggiornare due campi in caso di correzione del solo imponibile. In ciò, le tabelle memorizzate appaiono profondamente diverse da quelle, sotto altri aspetti simili, relative ai fogli elettronici. In fase di interrogazione, viceversa, i campi funzioni di altri devono potere essere visualizzati. A tale scopo, alle tabelle generate mediante l'operazione di selezione/proiezione, si possono aggiungere campi calcolati, che vengono espressi con ovvie notazioni (vedi esempio in SQL). RICHIAMO: § 1.5.2 - Normalizzazione 1.8.5.1. Calcolo imponibile e ordinamento (esempio) Creazione di una tabella dal D.B. Editrice, con i campi, Titolo, Costo (comprensivo di IVA) e con in più il campo calcolato Imponibile, supposta l’IVA al 6%. • • Imponibile = Costo / 1.06 Avendo realizzato il campo calcolato come sopra e chiedendo di ordinare il risultato per costo crescente si ottiene la tabella: Calcolo imponibile Titolo Costo imponibile Il commissario Angel € 14,00 13,21 Squadra omicidi € 15,50 14,62 Uno contro tutti € 20,80 19,62 La montagna spaccata € 27,00 25,47 Il tramonto € 27,30 25,75 1.8.5.2. Calcolo imponibile e ordinamento in SQL (approfondimento) L’istruzione SQL per effettuare quanto descritto dall’esempio è: SELECT Titolo, Costo, Costo /1.06 AS Imponibile FROM Libri ORDER BY Costo Si noti che: • "Costo/1.06 AS Imponibile" significa "Inserisci come Imponibile (As Imponibile) un campo calcolato da Costo diviso 1.06"; • la tabella- risultato è fornita in ordine di Costo (ORDER BY Costo); l'ordinamento è quello crescente, non essendo stato specificato ed essendo crescente il default. 1.8.6. Query su più tabelle: Join Una query su due (o più) tabelle fra loro collegate attraverso relazioni consente di costruire una nuova tabella i cui record contengono alcuni dati prelevati dalla prima ed altri dalla seconda (o dalle altre). Con riferimento ad esempio al database "Editrice" è possibile costruire una query che restituisce l'elenco dei libri (tratto dalla tabella LIBRI) a ciascuno dei quali è affiancata la sua collocazione (tratta dalla tabella GENERE) oppure anche l'elenco degli autori (da AUTORE) affiancato dai libri che ha scritto (tratto da AUTORI-LIBRO) e dai relativi titoli (tratti da LIBRO). Questa operazione si chiama join e si articola in diversi possibili casi. Illustreremo qui solo i casi più semplici e fondamentali, caratterizzati dalle seguenti proprietà: • • • coinvolgono 2 sole tabelle, che diremo PRIMARIA e COLLEGATA; fra le due tabelle è presente una relazione fra il campo-chiave chiave-primaria di PRIMARIA e il campo chiave-esterna di COLLEGATA; l'operazione è interpretata come operazione di inner join (esistono altri join che non trattiamo) . L'operazione si presenta allora con un comando del tipo: "Effettua un join fra le tabelle COLLEGATA e PRIMARIA mettendone in corrispondenza i campi chiave-esterna e chiaveprimaria". Il DBMS, allora, produce una tabella con le colonne richieste di COLLEGATA, affiancandole le colonne richieste di PRIMARIA nelle righe per le quali chiave esterna e chiave primaria coincidono. In altre parole, il join "prolunga" i record della tabella collegata con i dati della tabella primaria aventi come chiave primaria un valore eguale a quello della chiave esterna. Per fissare le idee si veda l'esempio tratto dal database Editrice e dalle sue relazioni (vedi RICHIAMI). RICHIAMO: § 1.1.4 - Data base relazionale: definizione ed esempio RICHIAMO: § 1.3.2 - Relazioni “uno a molti” fra tabelle. L'integrità referenziale 1.8.6.1. Join in SQL (approfondimento) Il join semplificato di cui si è detto si presenta in SQL con una istruzione del tipo: SELECT <elenco colonne> From PRIMARIA, COLLEGATA WHERE PRIMARIA.chiave-primaria = COLLEGATA.chiave-esterna Nella clausola WHERE del SELECT va cioè posta l'eguaglianza fra le due chiavi corrispondenti della relazione che lega le due tabelle. 1.8.6.2. Join su due tabelle di Editrice (esempio) Per ogni libro nel database Editrice si voglia conoscere il responsabile del genere associato, il titolo del libro è tratto dalla tabella LIBRI, il Responsabile dalla tabella GENERE e le due tabelle sono legate da una relazione nella quale GENERE ha la chiave primaria e LIBRI quella esterna (vedi figura tratta da quella del RICHIAMO) Il problema si risolve con un join fra le due tabelle che produce la tabella che segue: Query su due tabelle: Join Titolo La montagna spaccata Uno contro tutti Il pirata Neil Il commissario Angel Squadra omicidi Il tramonto Responsabile Lippo Saverio Lippo Saverio Lippo Saverio Frangia Bruno Frangia Bruno Si noti che l’istruzione SQL è, banalmente: SELECT LIBRI.Titolo, GENERE.Responsabile From GENERE, LIBRI WHERE GENERE.Genere = LIBRI.Genere e produce i campi selezionati (nell'esempio Titolo di LIBRO e Responsabile di GENERE) allineati con i valori eguali delle due chiavi in relazione (per ogni libro di un determinato Genere, si legge in GENERE il responsabile e lo si trascrive nel record del risultato). 1.8.7. Join su più tabelle Il join è un'operazione commutativa ed associativa e può essere esteso a più di due tabelle. L'istruzione SQL è mostrata nell'approfondimento e una concreta applicazione nell'esempio, sempre riferito al RICHIAMO. RICHIAMO: § 1.1.4 - Data base relazionale: definizione ed esempio RICHIAMO: § 1.3.2 - Relazioni “uno a molti” fra tabelle. L'integrità referenziale 1.8.7.1. Join su più tabelle in SQL (approfondimento) L'istruzione SQL per il join fra più tabelle è del tutto simile a quella per due; dette ad esempio TABELLA1, TABELLA2, TABELLA3 3 tabelle e supposte TABELLA1, TABELLA2 legate dalla coppia di chiavi Coppia1-2 e TABELLA2, TABELLA3 da Coppia2-3, l'istruzione è del tipo SELECT <elenco colonne> From Tabella1, Tabella2, Tabella3 WHERE (Coppia1-2) And (Coppia2-3) 1.8.7.2. Join su 3 tabelle di Editrice (esempio) Si vuole ottenere una tabella che affianchi al titolo del libro il nome del suo autore. La tabella AUTORI-LIBRI esprime questa relazione, ma fra i codici di autore e libro e non fra i loro nomi in chiaro; essa è inoltre collegata da un lato con AUTORI, che associa al codice fiscale il nome dell'autore e dall'altro a LIBRI che pone in chiaro il codice del libro. Un join fra le 3 tabelle risolve il problema: SELECT AUTORI.Nome, LIBRI.Titolo FROM AUTORI, AUTORI-LIBRI, LIBRI WHERE (AUTORI.CF = AUTORI-LIBRI.Codfisc) And (LIBRI.CodiceLibro = AUTORI-LIBRI.CodLibro); Join su 3 tabelle Nome Titolo Bianco Ugo La montagna spaccata Rossi Carlo Il commissario Angel Bianco Ugo Squadra omicidi Bruni Bruno Squadra omicidi Esposito Gennaro Il tramonto Neri Marco Il tramonto Bruni Bruno Uno contro tutti Verde Mario Il pirata Neil 1.8.8. Considerazioni finali basate sull'esempio Editrice Un join fra le 4 tabelle del database Editrice (vedi RICHIAMO) produce il "tabellone" di figura, che sintetizza in un unico quadro tutto il database e che coincide con quello discusso in apertura (vedi RICHIAMO). A qualcuno questo tabellone potrebbe sembrare più utile delle 4 tabelle distinte e ci si potrebbe chiedere perché non si sia progettato il database con questa unica tabella. Niente di più errato! È stato infatti già anticipato in apertura che la soluzione migliore per questo database è proprio quella "normalizzata" delle 4 tabelle, dalle quali è peraltro possibile trarre ogni altra aggregazione dei dati a mezzo delle operazioni di join. - Infatti, il tabellone, al contrario dei criteri di normalizzazione segnalati, possiede tutti questi difetti: rappresenta più concetti in un'unica tabella (il libro, l'autore, il genere); contiene inutili duplicazioni di dati; non è semplice né da interpretare e né da gestire; ogni inserimento, cancellazione o aggiornamento di dati è complesso, dovendo tener conto di tutti gli aspetti del database; RICHIAMO: § 1.1.4 - Data base relazionale: definizione ed esempio RICHIAMO: § 1.3.2 - Relazioni “uno a molti” fra tabelle. L'integrità referenziale RICHIAMO: § 1.5.2 - Normalizzazione Tabellone "Casa editrice" Nome Verde Mario Bianco Ugo Bianco Ugo Bruni Bruno Bruni Bruno Esposito Gennaro Neri Marco Rossi Carlo Data 01/01/ 1978 10/02/ 1980 10/02/ 1980 30/01/ 1957 30/01/ 1957 30/01/ 1970 31/12/ 1975 27/03/ 1965 Indirizzo via Francia 27 via Roma 27 via Roma 27 via Rossi 34 via Rossi 34 via Tale 20 via Po 100 p,za Dante 27 CF VRDMRA 780101F839 BNCGUO 80B10F835W BNCGUO 80B10F835W BRNBRN 57A30F839W BRNBRN 57A30F839W GNNSPS 70A30F839X NREMRC 751231G548Z RSSCRL 55C27G984Y Cod Libro AAA 7890 AAA 1111 AAA 2346 AAA 2346 AAA 2789 AAA 2878 AAA 2878 AAA 3456 Contratto CT 009 CT 001 CT 002 CT 004 CT 005 CT 007 CT 008 Collo- Responcazione sabile Lippo Il pirata Neil Avventura a001 Saverio La montagna Lippo € 27,00 Avventura a001 spaccata Saverio Squadra Frangia € 15,50 Gialli a002 omicidi Bruno Squadra Frangia € 15,50 Gialli a002 omicidi Bruno Uno contro Lippo € 20,80 Avventura a001 tutti Saverio Titolo Costo Genere Il tramonto € 27,30 Poesia a003 Il tramonto € 27,30 Poesia a003 Il commissario Angel € 14,00 Gialli a002 Frangia Bruno 1.8.8.1. Join in SQL fra le 4 tabelle di Editrice (esempio) Si vogliono riprodurre tutti i dati del database (gli 11 campi distinti) posti fra loro in relazione attraverso un unico tabellone: di ogni autore si vuole conoscere l'anagrafico, ciascuno dei libri scritti, ed allineato nel tabellone, i dati del libro (codice, prezzo, genere, responsabile). Considerando le relazioni esistenti nel database, il tabellone si ottiene con un join che coinvolge tutte le 4 tabelle: SELECT AUTORI.*, AUTORI-LIBRI.CodLibro, AUTORI-LIBRI.Contratto, LIBRI.Titolo, LIBRI.Costo, LIBRI.Genere, GENERE.Collocazione, GENERE.Responsabile FROM AUTORI, AUTORI-LIBRI, LIBRI, GENERE WHERE (GENERE.Genere)=(IBRI.Genere) And (AUTORI.CF)=(AUTORI-LIBRI.CodFisc) And (LIBRI.CodiceLibro=AUTORI-LIBRI.CodLibro); (AUTORI.* significa tutte le colonne di AUTORI)