Sistemi Informativi Insieme di strutture in grado di

Sistemi Informativi Insieme di “strutture” in grado di acquisire, elaborare, trasmettere ed archiviare informazioni in genere ad uso di un’organizzazione (azienda o istituzione).

Dato <> Informazione l l I dati sono registrazioni della descrizione di una qualsiasi caratteristica della realtà, su un supporto che ne garantisca la conservazione, la comprensibilità e la reperibilità. L’ informazione produce variazioni nel patrimonio conoscitivo di un soggetto. Proviene dai dati, ma devono essere inseriti in un contesto interpretativo

DBMS (Database Management System) l l Obiettivo: gestione strutturata di dati, organizzati in modo omogeneo. Base di dati: – Collezione di dati organizzati in modo coerente (un insieme casuale di dati non è una base di dati !) – Modella alcuni aspetti del mondo reale – Costruito con funzionalità ben precise, note fin dall’inizio della progettazione.

Esempi di basi dati: l l l l Rubrica telefonica personale anagrafe segreteria studenti dell’università banca dati centrale del ministero delle finanze archivio di una biblioteca, di un laboratorio d’analisi mediche, di un museo banca dati di una carta di credito banca dati delle prenotazioni dei voli di una compagnia aerea

Basi di dati: operazioni l l Definizione della base di dati – quali informazioni rappresentare – quali relazioni tra le informazioni Manipolazione – inserimento di dati – cancellazione di dati – aggiornamento (update) – interrogazione Gestione di operazioni da parte di utenti multipli Protezione e sicurezza dei dati

Livelli di rappresentazione l l Livello fisico: come i dati sono memorizzati e organizzati su uno o più supporti di memoria secondaria Livello logico: come i dati sono organizzati secondo il modello logico adottato (relazionale, gerarchico, ad oggetti etc. ) Livello concettuale: come i dati sono organizzati secondo uno schema concettuale Livello esterno: come i dati appaiono o vengono presentati all’utente

Livelli di rappresentazione Vista 1 Vista 2 Vista n Livello concettuale Livello logico Livello fisico

Figure professionali (1) l DB designer – schema concettuale (astratto) della base di dati – mapping su un modello dei dati • gerarchico • relazionale • object oriented • . . .

Figure professionali (2) l l l DB programmer (realizzazione della base di dati) DB administrator (gestione della base dati) Users • utenti esperti (usando un linguaggio di interrogazione) • naive users (interazioni predeterminate, forms) • programmi

Modello relazionale l l l I dati sono organizzati in relazioni le relazioni possono essere interpretate/visualizzate come tabelle un database è un insieme di relazioni

Rappresentazione tabellare dei dati: esempio Libro N. Inv Autore Titolo Anno_ed. Casa ed. Colloc.

Esempio rubrica COGNOME TEL. Tanzi Rosario 095 89. . . Federici Susanna 011 55. . . De Bernardi Silvio 06 44. . .

Modello relazionale l l l Relazione su due insiemi A e B è un sottoinsieme del prodotto cartesiano A x B Esempio sposato_con Í Persone x Persone figlio_di Í Persone x Persone vive_a Í Persone x Città Analogamente si possono considerare relazioni tra molti insiemi: A 1 x …. x. An

Esempio di relazione l l Persone= {Paolo, Luca, Mario} Città={Torino, Roma} Persone x Città Vive_a Persone Città Paolo Luca Mario Torino Roma Torino Mario Roma Persone Città Paolo Luca Torino Mario Roma

Figlio di (Persone x Persone) Persone Sono relazioni diverse! Persone Paolo Carla Anna Luca Mario Flavio Persone Anna Paolo Luca Flavio Carla Mario

Relazioni e Attributi: nomi che specificano un ruolo in una relazione, esempi: l sposato_con(Marito, Moglie) l figlio_di(Figlio, Genitore) l vive_a(Nome, Citta, Provincia) l libro(N. Inv, Autore, Titolo, Anno_edizione, Casa_editrice, Collocazione) l Gli attributi devono avere nomi differenti

ESEMPIO DATABASE ESAMI Prima idea: un’unica tabella: ESAME(Nome, Cognome, Matricola, Titolo, Docente, Voto, Lode) Contiene informazione ridondante: l per ogni esame sostenuto si ripetono tutti i dati dello stuedente l per ogni corso si ripetono titolo e docente l

Organizzazione migliore: l l l STUDENTE(Nome, Cognome, Matricola, Data di Nascita) CORSI(Titolo, Docente, Codice Corso) ESAMI(Codice Corso, Voto, Lode, Matricola)

Terminologia l l DOMINIO: insieme di valori degli attributi – es. Nomi di persona, Nomi di città, Numeri interi, stringhe di al più 80 caratteri FORMATO: rappresentazione degli elementi di un dominio – es. tre cifre decimali per i reali

l SCHEMA DI RELAZIONE R(A 1, …, A_n) • R relazione • Ai attributi l ISTANZA DI RELAZIONE (o semplicemente relazione ) r(R) = {<v 1, v 2, …, vn>} t=<v 1, v 2, …, vn > : n-tupla l I singoli valori vi appartengono al dominio dell’attributo Ai

l Istanza di relazione = insieme non ordinato di tuple – Non ci possono essere tuple ripetuti –l’ordine delle tuple non conta Database = insieme di istanze di relazioni Terminologia alternativa: – tuple: records – attributi: campi

La tabella rubrica Rubrica(Cognome, Nome, Tel) schema tuple nomi degli attributi COGNOME TEL. Tanzi Rosario 095 89. . . Federici Susanna 011 55. . . De Bernardi Silvio 06 44. . .

Informazione incompleta: Valori Nulli Il valore speciale “null” aggiunto a tutti i domini, per indicare assenza di valore indica: l valore mancante, oppure l valore sconosciuto

Vincoli sulle Relazioni l l I valori contenuti nelle tabelle possono essere soggetti a vari tipi di vincoli che dipendono dalla ‘realtà’ che si vuole rappresantare: vincoli di dominio: valori dei singoli attributi vincoli di tupla: valori di attributi correlati in una tupla vincoli di integrità valori di attributi in tuple diverse (anche in relazioni diverse)

Vincoli di dominio vincoli sui valori dei singoli attributi (vincoli di dominio), es: l dato ESAMI(Studente, Voto, Lode, Corso) deve essere Voto >=18 AND Voto <=30 l Vincoli sul valore di un attributo data

Vincoli di tupla vincoli sui valori di attributi correlati, es: l Data la relazione ESAMI come prima deve essere not (Lode = Yes) OR Voto = 30 equivalente a Lode= No OR Voto = 30 l Data la relazione PAGAMENTI(Data, Importo, Ritenute, Netto) deve essere Netto = Importo - Ritenute

Connettivi booleani: tavole di verità Negazione Congiunzione Disgiunzione

Esempio Vincolo (complesso) Supponiamo che STUDENTE contenga anche gli attributi l Borsa di Studio: valori {Yes, No} l Reddito (della famiglia): euro l Residente (nella città sede univ. ): {Yes, No} Uno studente ha diritto ad una borsa se – ha una media >= 27 e un reddito <= 20000, oppure – non è residente e ha una media >= 25 e un reddito <=25000

Vincoli di chiave superchiave: sottoinsieme SK di attributi di una relazione per cui se i ¹ j allora ti[SK] ¹ tj[SK] Una superchiave identifica un’entità in modo univoco in una relazione l l CHIAVE PRIMARIA = superchiave minimale non ci possono essere righe diverse con la stessa chiave primaria (Indichiamo in modo sottolineato gli attributi che formano la chiave primaria)

VINCOLI DI INTEGRITA’ l l l sull’entità : chiave primaria ¹ null integrità referenziale: consistenza tra tuple di due relazioni integrità semantica: vincoli che dipendono dalla realtà rappresentata (es. numero di libri in prestito a ciascun utente < 5)

Vincoli di Integrità referenziali l l In un database relazionale le tabelle sono collegate tra loro tramite i valori di attributi correlati (o comuni) Esempio – Il collegamento tra la relazione ESAMI e STUDENTI puo’ essere espresso dall’attributo comune Matricola – Il collegamento tra la relazione CORSI e ESAMI puo’ essere espresso dall’attributo comune Codice Corso

l l l Esempio: Il collegamento tra la relazione LIBRI e la relazione PRESTITI può essere espresso mediante l’ attributo Numero di inventario. Un vincolo di integrità referenziale tra un insieme di attributi X di una relazione R 1 e una relazione R 2 è soddisfatto se: i valori di ciascuna tupla di R 1 per gli attributi X esistono come valori della chiave primaria di R 2 In questo caso si dice anche X è una chiave esterna di R 1

Funzionalità del DBMS relazionale l Funzioni per – definizione della base di dati – inserimento / rimozione /aggiornamento di informazioni • deve soddisfare i vincoli! – Interrogazione

Interrogazione l l l Linguaggio SQL (Structured Query Language) Produce come risultato una tabella Clausola Base –SELECT “Lista di attributi” FROM “Elenco relazioni” WHERE “Condizione”

SQL (Structured Query Language) l l SQL: Linguaggio standard per creazione e interrogazione di DB Vediamo solamente come formulare interrogazioni con SQL un’interrogazione produce come risultato una tabella Clausola Base – SELECT “Lista di attributi” FROM “Elenco relazioni” WHERE “Condizione”

Esempi – SELECT Autore, Titolo FROM Libri WHERE Casa_Editrice = ‘Feltrinelli’ – SELECT Autore, Titolo FROM Libri WHERE (Casa_Editrice = ‘Feltrinelli’) and (Anno_edizione = 1990) – SELECT * FROM Libri WHERE (Casa_Editrice = ‘Feltrinelli’) or (Casa_Editrice = ‘Einaudi’)

Provare l l l Trova i libri editi da Einaudi o Bompiani, pubblicati dopo 1980 (Autore, Titolo, Casa_ed, Anno_ed) Trova i libri editi da Einaudi o editi da Bompiani dopo 1980 Trova i libri editi da Garzanti ma non da Bompiani

Matching approssimato l l l L’operatore like permette di confrontare il valore di un attributo con un valore specificato in modo incompleto Si usa con gli operatori _e %, in ACCESS ? e * ? indica un carattere qualsiasi *una sequenza di caratteri qualunque

Esempio – SELECT Autore, Titolo FROM Libri WHERE Autore like “*Man*” l Seleziona tutti i libri in cui il nome dell’autore contiene “Man”

Esempi: query parametriche – Trova tutti i libri presenti in biblioteca, dato il nome dell’autore, Il nome dell’autore viene introdotto dall’utente – SELECT * FROM Libri WHERE Autore = [dimmi il nome dell’autore] – In esecuzione: > dimmi il nome dell’ autore Alessandro Manzoni – Risultato: ….

Esempio con like e parametri – SELECT libri. * FROM libri WHERE Autore like "*"&[dimmi l'autore] & "*" l l Seleziona tutti i libri in cui il nome dell’autore contiene la stringa introdotta dall’utente & e’ l’operatore di concatenazione tra stringhe

Operatori insiemistici l UNION, INTERSECT, DIFFERENCE l ESEMPIO – SELECT Autore, Titolo FROM Libri WHERE Casa_Editrice = ‘Feltrinelli’ INTERSECT SELECT Autore, Titolo FROM Libri WHERE Casa_Editrice = ‘Einaudi’

Funzioni aggregate (i cui valori dipendono da più righe) l COUNT, SUM, MAX, MIN, AVG l ESEMPI: – Contare i libri presenti in biblioteca editi da Feltrinelli. SELECT Count(*) FROM Libri WHERE Casa_Editrice = ‘Feltrinelli’

Funzioni aggregate (2) – Calcolare il costo totale dei libri presenti in biblioteca, scritti da Umberto Eco SELECT Sum (Prezzo) FROM Libri WHERE Autore = ‘Umberto Eco’ – Qual è il prezzo del più costoso libro presente in biblioteca? SELECT Max(Prezzo) FROM Libri

Raggruppamento e operatori aggregati l l Raggruppare le tuple in base a qualche attributo applicare le funzioni aggregate a ciascun raggruppamento

Raggruppamento l l GROUP BY ESEMPI Per ogni editore, indicare il costo totale dei libri presenti in biblioteca SELECT Casa_ed, Sum (Prezzo) as Valori FROM Libri GROUP BY Casa_ed

Raggruppamento (2) l ESEMPI – Per ogni autore, contare i libri presenti in biblioteca editi da Einaudi, ed indicarne il costo totale SELECT Autore, Count(*) as Numero, Sum (Prezzo) as Valori FROM Libri WHERE Casa_Ed = “Einaudi” GROUP BY Autore

Raggruppamento (3) l La clausola HAVING consente di imporre una condizione sul risultato di una funzione aggregata – Per ogni autore di almeno 3 libri editi da Einaudi presenti in biblioteca, indicarne il costo totale SELECT Autore, Sum (Prezzo) as Valori FROM Libri WHERE Casa_Ed = “Einaudi” GROUP BY Autore HAVING Count(*) >=3

Ordinamento dei risultati l l Si può chiedere che le tuple del risultato siano ordinate in base ai valori dei campi: ORDER BY Es. Restituire l’elenco dei libri in catalogo, secondo l’ordine alfabetico degli autori, per anno di edizione decrescente – SELECT (*) FROM LIBRI ORDER BY Autore ASC, Anno_ed DESC

Valori Unici l l l SQL restituisce una tabella che contiene tutte le righe che soddisfano una certa condizione, puo’ contenere duplicati Per eliminare i duplicati si premette la parola chiave distinct Esempio elenca i libri per autore e titolo senza ripetizioni (ignorando copie multiple e differenti edizioni) SELECT DISTINCT libri. Autore, libri. Titolo FROM libri ORDER BY Autore DESC;

Query con piu’ tabelle - join l l Join: combinare le tuple di più tabelle i cui valori per attributi correlati soddisfano una condizione di confronto (caso più semplice: sono uguali) Il join di due relazioni è il sottoinsieme del loro prodotto cartesiano specificato dalla condizione di selezione

Relazioni tra tabelle e Join Le relazioni tra tabelle sono espresse da valori comuni di attributi correlati l. Esempio Seleziona gli studenti e gli esami che hanno sostenuto con i rispettivi titoli l SELECT Studenti. Nome, Studenti. Cognome, Corsi. Titolo, Corsi. Codice , Studenti. Matricola FROM Corsi, Studenti, Esami WHERE Corsi. Codice =Esami. Codice_Corso AND Studenti. Matricola = Esami. Matricola ORDER BY Studenti. Cognome;

Formulazione Alternativa (1) SELECT Studenti. Nome, Studenti. Cognome, Corsi. Titolo, Corsi. Codice, Studenti. Matricola FROM Corsi, Studenti INNER JOIN Esami ON Studenti. Matricola = Esami. Matricola WHERE Corsi. Codice=Esami. Codice_Corso

Formulazione Alternativa (2) SELECT Studenti. Nome, Studenti. Cognome, Corsi. Titolo, Corsi. Codice, Studenti. Matricola FROM Studenti INNER JOIN (Corsi INNER JOIN Esami ON Corsi. Codice = Esami. Codice_Corso) ON Studenti. Matricola = Esami. Matricola

Join e Aggregati esempi: l l l Per ogni studente determina quanti esami ha sostenuto Per ogni corso (titolo) determina il numero di studenti che ne hanno sostenuto l’esame Per ogni studente determina la media dei voti Elenca gli studenti che hanno una media >= 27 Elenca gli studenti che hanno sostenuto più di un esame

Per ogni studente determina quanti esami ha sostenuto (elenca nome, cognome matricola) SELECT Studenti. Nome, Studenti. Cognome, Studenti. Matricola, Count(*) AS Esami_sostenuti FROM Studenti INNER JOIN Esami ON Studenti. Matricola = Esami. Matricola GROUP BY Studenti. Nome, Studenti. Cognome, Studenti. Matricola ORDER BY Studenti. Cognome;

Select annidate E’possibile utilizzare la tabella risultante da una query come condizione di selezione per un’altra query Esempi: l seleziona il libro con il prezzo massimo l seleziona gli autori i cui autori di libri compaiono in una tabella ma non in un’altra l Seleziona i libri (autore, titolo pubblicati da una casa editrice ma non da un’altra) l

Select annidate l SELECT INTERNA produce tabella l SELECT ESTERNA usa tale tabella come condizione l RISULTATO è solo la tabella prodotta dalla select esterna

Libri pubblicati da Garzanti e Bompiani SELECT Autore FROM libri WHERE Casa_ed = ”Bompiani"; Produce una tabella, chiamiamola Libri. Bompiani SELECT Autore, Titolo FROM libri WHERE Casa_ed = "Garzanti" and Autore In (select Autore from Libri. Bompiani);

Esempio differenza Seleziona Autore e Titolo, pubblicati da [Casa che pubblica], ma non da [Casa che non pubblica] SELECT Autore, Titolo FROM libri WHERE Casa_ed = [Casa che pubblica] and Autore not in (select Autore from libri where Casa_ed = [Casa che non pubblica]);

Esempio intersezione Seleziona Autore e Titolo dei libri pubblicati sia da [casa 1] e da [casa 2] SELECT Autore, Titolo FROM libri WHERE Casa_ed = [casa 1] and Autore in (select Autore from libri where Casa_ed = [casa 2]);