Informatica II – Basi di Dati (07/08) – Parte 2

1. Informatica II – Basi di Dati (07/08) – Parte 2 Gianluca Torta Dipartimento di Informatica dell’Università di Torino torta@di.unito.it, 0116706782

2. 4 – Accesso ai dati di un DB SQL

3. Accesso ai dati di un DB • Aggiornamento di DB: funzione che, data istanza di DB, produce altra istanza di DB, sullo stesso schema • Modifica, aggiunta, rimozione tuple • Interrogazione a DB: funzione che, dato un DB, produce una relazione su un dato schema (non necessariamente uno degli schemi definiti nel DB)

4. Accesso ai dati di un DB • Aggiornamento e interrogazione vengono effettuati usando specifici linguaggi • Esempio di linguaggio di interrogazione e aggiornamento: SQL • Esempio di calcolo di interrogazione: algebra relazionale

5. Interrogazioni in SQL • SQL ha uno standard (ultima versione 2008) implementato in modo più o meno completo e aggiornato nei vari DBMS • DML (Data Manipulation Language) + DDL (Data Declaration Language) • Formulazione di interrogazioni (query) è parte del DML • Anche usato nel DDL (per esempio, per dichiarare vincoli di integrità)

6. Interrogazioni in SQL • Paradigma dichiarativo: si specifica la descrizione dell’obiettivo e non il modo con cui ottenerlo • A differenza dell’algebra relazionale, che è procedurale

7. Cenni sull’implementazione • Interrogazione in SQL viene tradotta in linguaggio procedurale • Sulla traduzione si fanno ottimizzazioni algebriche (ecco a cosa serve l’algebra…) … • … e non (queste ultime dipendono dalle strutture sottostanti al DBMS in questione)

8. Sintassi • Esistono, in generale, più modi per effettuare un’interrogazione: scelte basate sulla leggibilità (più che sull’efficienza…) • Struttura essenziale (introdurremo le variazioni di volta in volta): select ListaAttributi (target list) from ListaTabelle (clausola “from”) [where Condizione] (clausola “where”)

9. Significato dell’interrogazione • Si considera il prodotto cartesiano fra le tabelle della clausola “from” • Si selezionano quelle tuple che soddisfano la condizione della clausola “where” (opzionale) • Si danno in ouput i valori di quegli attributi che sono elencati nella target list (la lista dopo la clausola “select”)

10. Tabella “Impiegato” Impiegato

11. select Stipendio as Salario from Impiegato where Cognome = ‘Rossi’ Interrogazione 1 alias

12. select * from Impiegato where Cognome = ‘Rossi’ Interrogazione 2 tutti

13. select Stipendio/12 as StipMens from Impiegato where Cognome = ‘Bianchi’ Interrogazione 3

14. Sulla clausola “where” • Ammette come argomento un’espressione logica (condizione) • Predicati semplici combinati con not, and, or (not ha la precedenza, consigliato l’uso di parantesi(,)) • Ciascun predicato usa operatori: =, <>, <, >, <=, >= • Confronto tra valori di attributi, costanti, espressioni

15. Tabella “Dipartimento’’ Dipartimento

16. Restituire nome e cognome degli impiegati e delle città in cui lavorano select * from Impiegato,Dipartimento where Dipart = Dipartimento.Nome Interrogazione 4 (join) collega tra di loro tuple tramite i valori uguali degli attributi NOTA: usa .

17. Risultato interrogazione 4 da Impiegato da Dipartimento

18. Restituire nome e cognome degli impiegati e delle città in cui lavorano select Impiegato.Nome,Cognome, Dipartimento.Città from Impiegato,Dipartimento where Dipart = Dipartimento.Nome Interrogazione 4’ La notazione punto (.) serve per disambiguare Suggerimento: “from”, “where”, target list

19. Risultato interrogazione 4’

20. Interrogazione 5 (come 4’, ancora join…) select I.Nome, Cognome, D.Città from Impiegato [as] I, Dipartimento [as] D where Dipart = D.Nome L’aliasing per le tabelle serve a disambiguare, (oltre che ad altri scopi)

21. select Nome,Cognome from Impiegato where Ufficio = 20 and Dipart =‘Amministr’ Interrogazione 6

22. select Nome, Cognome from Impiegato where Dipart=‘Prod’ or Dipart=‘Amministr’ select Nome, Cognome from Impiegato where Cognome=‘Rossi’ and (Dipart=‘Prod’ or Dipart=‘Amministr’) Interrogazione 7 e 8 1 1 2 2

23. Operatore like • _ = carattere arbitrario • % = stringa di lunghezza arbitraria (anche 0) di caratteri arbitrari • Esempi: • like ab%ba_ = tutte le stringhe che cominciano con “ab” e che hanno “ba” come coppia di caratteri prima dell’ultima posizione (es. abjjhhdhdbak,abbap)

24. Interrogazione 9 select * from Impiegato where Cognome like ‘_o%i’ or Cognome like ‘_u%i’

25. Gestione dei valori nulli • Attributo con valore nullo = non applicabile a una certa tupla, o valore sconosciuto, o non si sa nulla • SQL offre il predicato “is null”: Attributo is [not] null

26. Gestione dei valori nulli • Stipendio>13: cosa succede se l’attributo Stipendio è nullo? Scelte: • Logica a 2 valori e controllo esplicito. Per esempio: (Stipendio > 13) or (Stipendio <= 13) or (Stipendio is null) • Usare un terzo valore di verità unknown: soluzione che crea problemi nei casi complessi (valutazione “globale” delle formule logiche)

27. Uso delle variabili di alias • Non solo per disambiguare la notazione • Ci sono casi in cui una stessa tabella serve più di una volta • Caso speciale: quando si deve confrontare una tabella con se stessa (il calcolo relazionale è relation-oriented, non tuple-oriented )

28. Interrogazione 10 • Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione

29. Interrogazione 10 • Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione select I1.Cognome, I1.Nome from Impiegato I1, Impiegato I2 where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

30. Interrogazione 10 • Estrarre nome e cognome degli impiegati che hanno lo stesso cognome (ma nome diverso) di impiegati che lavorano nel dipartimento Produzione I2 usata per trovare tuple assoc. a ‘Prod’ per evitare output di tupla con se stessa select I1.Cognome, I1.Nome from Impiegato I1, Impiegato I2 where I1.Cognome = I2.Cognome and I1.Nome <> I2.Nome and I2.Dipart = ‘Prod’

31. Interrogazione 11 • Estrarre il nome e lo stipendio dei capi degli • impiegati che guadagnano più dei loro capi, • date: • Impiegati(Matricola, Nome, Età, Stipendio) • Supervisione(Capo, Impiegato) • dove Capo e Impiegato sono chiavi esterne di • Impiegati(e.g., sono dei numeri di matricola)

32. Interrogazione 11 (sol.) select I1.Nome, I1.Stipendio from Impiegato I1, Impiegato I2, Supervisione where I1.Matricola = Capo and I2.Matricola = Impiegato and I2.Stipendio > I1.Stipendio I1 per i capi, I2 per gli impiegati

33. Join esplicito • Abbiamo visto un modo di fare il join mettendo le condizioni di join nella clausola where • Si può utilizzare esplicitamente un operatore di join

34. Interrogazione 26 (join esplicito) select Impiegato.Nome, Cognome, Dipartimento.Città from Impiegato,Dipartimento where Dipart = Dipartimento.Nome select Impiegato.Nome,Cognome, Dipartimento.Città from Impiegato join Dipartimento On (Dipart =Dipartimento.Nome)

35. Interrogazione 27 (join esplicito, riprende la 11) Estrarre il nome e lo stipendio dei capi degli impiegati che guadagnano piú dei loro capi select I1.Nome, I1.Stipendio from Impiegato I1, Impiegato I2, Supervisione where I1.Matricola = Capo and I2.Matricola = Impiegato and I2.Stipendio > I1.Stipendio select I1.Nome, I1.Stipendio from (Impiegato I1 join Supervisione on (I1.Matricola = Capo)) join Impiegato I2 on (I2.Matricola = Impiegato) where I2.Stipendio > I1.Stipendio

36. Outer join • Fino ad adesso abbiamo visto inner join • Parliamo adesso di outer join • Serve quando il join non è completo … • Completo: dato R1 join R2on (…), per ciascuna tupla di R1 esiste almeno una tupla di R2 che si combina con essa, e viceversa per R2 • … se si vuole mantenere l’informazione anche per quelle tuple che non partecipano al join

37. Tabelle “Guidatore” e “Automobile”

38. Inner join (“normale”) select Nome,Cognome,Guidatore.NumPatente, Targa from Guidatore join Automobile on (Guidatore.NumPatente=Automobile.NumPatente) Join non completo

39. Outer left join (interrogazione 28) select Nome,Cognome,Guidatore.NumPatente, Targa from Guidatore left join Automobile on (Guidatore.NumPatente=Automobile.NumPatente)

40. Outer right join (interrogazione 29) select Nome,Cognome,Guidatore.NumPatente, Targa from Guidatore right join Automobile on (Guidatore.NumPatente=Automobile.NumPatente)

41. Outer full join (interrogazione 30) select Nome,Cognome,Guidatore.NumPatente, Targa from Guidatore full join Automobile on (Guidatore.NumPatente=Automobile.NumPatente)

42. Join naturale (interrogazione 31) select Nome,Cognome,Guidatore.NumPatente, Targa from Guidatore natural join Automobile Attributo comune: NumPatente