LEZ11database

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


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)


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:







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




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




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)

DB designer
– schema concettuale (astratto) della base di
dati
– mapping su un modello dei dati
• gerarchico
• relazionale
• object oriented
• ...
Figure professionali (2)



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



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
Esempio rubrica
COGNOME
NOME
TEL.
Tanzi
Rosario
095 89 ...
Federici
Susanna
011 55 ...
De Bernardi
Silvio
06 44 ...
Modello relazionale





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: A1 x ….xAn
Esempio di relazione


Persone= {Paolo, Luca,Mario}
Città={Torino,Roma}
Persone x Città
Vive_a
Persone
Città
Paolo
Paolo
Luca
Luca
Mario
Torino
Roma
Torino
Roma
Torino
Mario
Roma
Persone
Città
Paolo
Luca
Mario
Torino
Torino
Roma
Figlio di (Persone x Persone)
Persone
Sono
relazioni
diverse!
Paolo
Carla
Mario
Persone
Persone
Anna
Luca
Flavio
Persone
Anna
Paolo
Luca
Flavio
Carla
Mario
Relazioni e Attributi
Attributi: nomi che specificano un ruolo in una
relazione, esempi:
 sposato_con(Marito, Moglie)
 figlio_di(Figlio, Genitore)
 vive_a(Nome,Citta,Provincia)
 libro(N.Inv, Autore, Titolo, Anno_edizione,
Casa_editrice, Collocazione)
 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:
 per ogni esame sostenuto si ripetono tutti i
dati dello stuedente
 per ogni corso si ripetono titolo e docente

Organizzazione migliore:



STUDENTE(Nome,Cognome,Matricola,Data
di Nascita)
CORSI(Titolo,Docente,Codice Corso)
ESAMI(Codice Corso,Voto,Lode,Matricola)
Terminologia

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

SCHEMA DI RELAZIONE R(A1, …, A_n)
• R relazione
• Ai attributi

ISTANZA DI RELAZIONE (o semplicemente
relazione )
r(R) = {<v1, v2, …, vn>}
t=<v1, v2, …, vn > : n-tupla

I singoli valori vi appartengono al dominio dell’attributo Ai

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
NOME
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:
 valore mancante, oppure
 valore sconosciuto
Vincoli sulle Relazioni




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:
 dato ESAMI(Studente,Voto, Lode,Corso)
deve essere
Voto >=18 AND Voto <=30
 Vincoli sul valore di un attributo data
Vincoli di tupla
vincoli sui valori di attributi correlati, es:
 Data la relazione ESAMI come prima
deve essere
not (Lode = Yes) OR Voto = 30
equivalente a
Lode= No OR Voto = 30
 Data la relazione
PAGAMENTI(Data,Importo,Ritenute,Netto)
deve essere
Netto = Importo - Ritenute
Connettivi booleani: tavole di verità
Negazione
A Not A
Vero Falso
Falso Vero
Congiunzione
A
Vero
Vero
Falso
Falso
B
Vero
Falso
Vero
Falso
A And B
Vero
Falso
Falso
Falso
Disgiunzione
A
Vero
Vero
Falso
Falso
B
Vero
Falso
Vero
Falso
A Or B
Vero
Vero
Vero
Falso
Esempio Vincolo (complesso)
Supponiamo che STUDENTE contenga anche
gli attributi
 Borsa di Studio: valori {Yes, No}
 Reddito (della famiglia): euro
 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


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’



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


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



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 R1 e una
relazione R2 è soddisfatto se: i valori di ciascuna
tupla di R1 per gli attributi X esistono come
valori della chiave primaria di R2
In questo caso si dice anche che X è una chiave
esterna di R1
Funzionalità del DBMS relazionale

Funzioni per
– definizione della base di dati
– inserimento / rimozione /aggiornamento di
informazioni
• deve soddisfare i vincoli!
– Interrogazione
Interrogazione



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)




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



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’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*”

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] & "*"


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

UNION, INTERSECT, DIFFERENCE

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)

COUNT, SUM, MAX, MIN, AVG

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


Raggruppare le tuple in base a qualche
attributo
applicare le funzioni aggregate a ciascun
raggruppamento
Raggruppamento


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)

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)

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


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



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


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
Esempio Seleziona gli studenti e gli esami che
hanno sostenuto con i rispettivi titoli

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:





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:
 seleziona il libro con il prezzo massimo
 seleziona gli autori i cui autori di libri
compaiono in una tabella ma non in un’altra
 Seleziona i libri (autore, titolo pubblicati da
una casa editrice ma non da un’altra)

Select annidate

SELECT INTERNA produce tabella

SELECT ESTERNA usa tale tabella come condizione

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 LibriBompiani
SELECT Autore, Titolo
FROM libri
WHERE Casa_ed = "Garzanti" and Autore In (select
Autore from LibriBompiani);
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 [casa1] e da [casa2]
SELECT Autore, Titolo
FROM libri
WHERE Casa_ed = [casa1] and Autore in
(select Autore from libri where Casa_ed =
[casa2]);