Database, informazioni e dati
Attualmente, pressoche in qualsiasi attivita il compito della gestioneed organizzazione dei dati viene realizzata da un sistema informatico
Diffusione capillare dell’informatica a partire degli anni 80 e 90
Stefano Brocchi Basi di dati 2 / 93
Database, informazioni e dati
In molte applicazioni, e necessario avere dei dati organizzati in modopreciso seguendo degli standard universali
Puo non essere sufficiente il normale filesystem
Vengono per questo introdotte le basi di dati (database)
Stefano Brocchi Basi di dati 3 / 93
Basi di dati
Una base di dati e un insieme di dati strutturati gestibili in modoflessibile da soggetti diversi
I programmi che realizzano e gestiscono le basi di dati si chiamanoDBMS (Database Management System)
◮ Alcuni nomi dei programmi che fanno da DBMS: Oracle, MySQL,MSSQL, ...
Stefano Brocchi Basi di dati 4 / 93
Basi di dati: interfaccia
Per interfacciarsi direttamente con una base di dati, esistononumerosi programmi ad interfaccia grafica
◮ Tramite questi programmi, si possono inserire, modificare ed estrarredati, ma anche creare database e definirne la struttura
◮ Diversi dai programmi applicativi destinati all’utente finale, checonoscendo il significato dei dati nel database forniscono un contestopiu facilmente comprensibile all’utente
Per database su server, si puo accedere spesso anche tramiteun’interfaccia web
Questi programmi convertono i comandi dati tramite interfacciagrafica nel linguaggio del database
Di seguito vedremo come e regolamentato l’accesso
Stefano Brocchi Basi di dati 5 / 93
Basi di dati: esempio
Nel seguito, faremo riferimento ad un ipotetico esempio di una basedi dati utilizzata in un laboratorio scientifico
La base di dati conterra innanzitutto una serie di dati anagraficiconsultabili dal laboratorio
Ogni esperimento eseguito verra salvato permanentemente nella basedi dati
Stefano Brocchi Basi di dati 6 / 93
Basi di dati: esempio
Altri utilizzi comuni delle basi di dati che coinvolgono svariate attivitasono:
◮ Anagrafiche, per esempio di comuni◮ Basi di dati per esami universitari◮ Archivi di pagamenti per aziende o tassazioni◮ Informazioni a proposito di siti web dinamici (es. forum)◮ Prenotazioni per cinema, ristoranti, compagnie aeree, ...
Stefano Brocchi Basi di dati 7 / 93
Basi di dati: proprieta
Le basi di dati devono mettere a disposizione le seguenti proprieta:◮ Gestione grandi dimensioni di dati◮ Condivisione◮ Persistenza◮ Affidabilita◮ Riservatezza◮ Efficienza
Stefano Brocchi Basi di dati 8 / 93
Gestione grandi basi di dati
Le basi dati devono essere in grado di gestire grandi quantita di dati
Non ci devono essere limiti, se non quelli dati dalla macchina stessadove risiede la base di dati
Questo deve valere anche se non si conosce a priori la grandezzarichiesta
◮ Una base di dati inizialmente piccola puo crescere fino a grandidimensioni
Stefano Brocchi Basi di dati 9 / 93
Persistenza
Una base di dati deve essere chiaramente persistente
I dati devono rimanere salvati anche quando i programmi che ligestiscono vengono interrotti, o quando i computer vengono spenti
Necessario quindi chiaramente salvare i dati su memoria di archivio◮ Requisito necessario anche per realizzare basi di dati di grandi
dimensioni, visto che la memoria di archivio e molto piu grande diquella centrale
Stefano Brocchi Basi di dati 10 / 93
Condivisione
Un altro fattore fondamentale e la possibilita di condividere i dati conmolti utenti
Per fare in modo che tutti possano comunicare correttamente con labase di dati ed estrarne informazioni, e definito un linguaggio comuneutilizzabile da tutti
◮ Vedremo in seguito il linguaggio SQL
Tipicamente, questa funzionalita e realizzata tramite l’uso di reti dicalcolatori
Stefano Brocchi Basi di dati 11 / 93
Condivisione database in rete
Il DBMS viene eseguito su di una macchina detta server che mette adisposizione il servizio
Tramite rete, altre macchine dette client potranno inviare richieste aldatabase per inserire o estrarre dati
◮ E’ possibile che l’accesso avvenga con programmi diversi per diversefunzionalita
La connessione puo avvenire tramite internet o reti private
Stefano Brocchi Basi di dati 12 / 93
Identificazione utenti e permessi
Tipicamente solo un numero ristretto di utenti autorizzati potraaccedere alla base di dati
Per identificare i vari utenti, viene utilizzato un meccanismo diidentificazione che richiede ad ognuno un nome utente ed unapassword
Si possono imporre vincoli su quello che puo fare un determinatoutente, sia in funzione dei dati che puo modificare o cancellare sia perquanto riguarda i dati che puo estrarre
Stefano Brocchi Basi di dati 13 / 93
Permessi utenti
Nel nostro esempio, tutte le macchine del laboratorio potrannoaccedere ai dati anagrafici, ma non modificarli
Sarebbe possibile anche che ogni macchina possa accedere soloall’anagrafica delle persone che hanno dato un consenso
Dal laboratorio verosimilmente sara possibile inserire dei risultati dianalisi, ma solo a patto di attribuirli al laboratorio stesso
◮ Controllo sui dati inseriti: non si puo attribuire un esame ad un altrolaboratorio
◮ Si puo immaginare inoltre che i test inseriti nel database non possanopiu venir modificati
Stefano Brocchi Basi di dati 14 / 93
Permessi utenti
I controlli sui vincoli degli utenti vengono fatti dal server con il DBMS
Non dipendono quindi dai programmi installati sulle macchine locali:anche se una di queste tentasse di aggirare una limitazione, il serverlo impedirebbe
Questo non impedisce ai programmi sui client di imporre dei vincoliulteriori
Stefano Brocchi Basi di dati 15 / 93
Registro operazioni
Inoltre, per database particolarmente critici per problematiche disegretezza o integrita dei dati, si possono memorizzare e raccoglieretutte le operazioni eseguite dagli utenti
Se sfruttando una qualche falla del sistema qualcuno eseguemodifiche non autorizzate, si puo rintracciare l’utente responsabile
I dati vengono immagazzinati in un file detto registro o log
Stefano Brocchi Basi di dati 16 / 93
Riservatezza
I dati devono inoltre disporre di determinati requisiti di riservatezza
Questo viene realizzato innanzitutto tramite i meccanismi diautenticazione e permessi visti
Inoltre, per evitare che i dati possano essere intercettati e lettidurante la comunicazione, si possono utilizzare protocolli ditrasmissione criptati
◮ Senza disporre di una chiave di decodifica, i dati trasmessi risultano’incomprensibili’
Stefano Brocchi Basi di dati 17 / 93
Affidabilita
I database devono inoltre disporre di un’alta affidabilita per i dati chevi sono contenuti
Puo succedere che una memoria abbia un guasto e che i daticontenuti vadano persi
Una prima soluzione: utilizzare piu dischi fissi sul server, e scrivere idati su piu di un disco in modo che se uno si guasta si possanorecuperare dall’altro
◮ Tecnologia detta RAID
Stefano Brocchi Basi di dati 18 / 93
Affidabilita
Una soluzione ancora migliore consiste nel salvare i dati su piu server
Si garantisce cosı il servizio anche in caso di guasto di un’interamacchina
Nel caso di un database pesantemente utilizzato, si mantengonoinoltre le prestazioni alte grazie alla distribuzione del carico di lavoro
Stefano Brocchi Basi di dati 19 / 93
Affidabilita: problematiche
D’altra parte, nascono cosı delle problematiche di consistenza:dobbiamo essere sicuri che quando un dato viene modificato, tutti iserver siano aggiornati in modo concorde
◮ Immaginare per esempio la situazione dove due modifiche diversevengono richieste contemporaneamente a due diversi server: c’ebisogno di un meccanismo che gestisca il conflitto
Esistono meccanismi elaborati che gestiscono tali situazioni, per cuieventuali problemi vengono evitati e l’utente non se ne devepreoccupare
Stefano Brocchi Basi di dati 20 / 93
Efficienza
Un’altra caratteristica di base per la realizzazione di database el’efficienza
Le richieste di estrazione di dati o di modifiche devono essere eseguitein tempi molto brevi
◮ Anche con macchine molto veloci, per grandi quantita di dati questacaratteristica non e scontata, ma conseguenza di un’attentarealizzazione
◮ Caratteristica necessaria anche perche un database puo essere usato incontemporanea da molti utenti
Stefano Brocchi Basi di dati 21 / 93
Il modello relazionale
Attualmente, la maggior parte dei database e rappresentata tramite ilmodello relazionale
Il modello e indipendente dalla realizzazione fisica: possiamorappresentare i dati per come sono strutturati a livello logico,indipendentemente da come verranno rappresentati nella macchina
Altri modelli proposti ma meno utilizzati sono il modello gerarchico ereticolare
Stefano Brocchi Basi di dati 22 / 93
Il modello relazionale
Il modello relazionale si basa su due concetti chiave: quello di tabellae quello di relazione
Il concetto di tabella corrisponde a quello che si ha nel linguaggiocomune
Le relazioni servono a legare insieme i vari dati all’interno di unatabella o tra tabelle diverse
Stefano Brocchi Basi di dati 23 / 93
Il modello relazionale: esempio
Vediamo un esempio di una possibile tabella di una base di dati
Nome Cognome Data_nascitaSesso
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
M
M
M
F
01-02-1981
10-04-1978
12-12-1989
13-8-1985
Stefano Brocchi Basi di dati 24 / 93
Tipi
Generalmente ogni colonna di una tabella e vincolata a rappresentaredati di un unico tipo
Dei molti tipi a disposizione, alcuni dei principali sono◮ Numeri interi◮ Numeri con cifre decimali (virgola mobile)◮ Stringhe (cioe sequenze di caratteri)◮ Valori booleani (vero/falso)
Stefano Brocchi Basi di dati 25 / 93
Tipi
Altri tipi possono rappresentare informazioni piu complesse, come iseguenti
◮ Data e ora◮ Dati binari generici
L’uso di questi tipi e uno dei vincoli imposti sui dati nel database checi consente di garantire proprieta di coerenza
◮ Esempio: in un database anagrafico, che quello che compare nellacolonna ’eta’ sia un numero, mentre nomi e cognomi sono stringhe
Stefano Brocchi Basi di dati 26 / 93
Indipendenza dalla rappresentazione fisica
Le nostre richieste alla base di dati faranno riferimento a questarappresentazione a tabelle
Sul calcolatore, i dati potrebbero essere rappresentati diversamente◮ Le motivazioni sono diverse, e sono legate a diversi fattori◮ Pensare per esempio alla ricerca di una determinata riga in una tabella:
delle strutture dati particolari consentono di individuare la rigarapidamente senza scorrere tutta la tabella
Stefano Brocchi Basi di dati 27 / 93
Modello relazionale: relazioni
Una riga di una tabella mette in relazione tra loro i dati contenutinella riga stessa
Spesso questa viene chiamata n-upla o tupla e rappresentata nellanotazione matematica (valore1, valore2, valore3, ...)
Il tipo di una tupla e determinato dalla combinazione dei tipi contenuti
Per estrarre un singolo valore dalla tupla, si puo utilizzare il nomedella relativa colonna
Un valore associato ad un certo tipo di colonna e anche detto campo
Stefano Brocchi Basi di dati 28 / 93
Campi e tuple: esempio
Un esempio
Nome Cognome Data_nascitaSesso
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
M
M
M
F
01-02-1981
10-04-1978
12-12-1989
13-8-1985
Tuple
Campi
Stefano Brocchi Basi di dati 29 / 93
Modello relazionale: relazioni
Le varie tabelle di una base di dati sono solitamente legate tra loro daaltre relazioni
Nel modello relazionale, si dice che queste relazioni sono basate suvalori
In termini un po’ semplicistici, si possono individuare tuple associatetra loro tramite la corrispondenza di determinati valori
Stefano Brocchi Basi di dati 30 / 93
Modello relazionale: esempio
Vediamo un esempio in un database anagrafico
Nome Cognome Data_nascitaSesso
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
M
M
M
F
01-02-1981
10-04-1978
12-12-1989
13-8-1985
Identificativo
1
2
3
4
Numero test Tipo test Descrizione esito
1
2
3
4
Paziente
3
2
3
1
......
Stefano Brocchi Basi di dati 31 / 93
Valori nulli
Spesso in un database alcuni campi i cui valori sono noti soltanto peralcune righe
◮ In un’anagrafica, per alcune persone potremmo non conoscere data dinascita o residenza
Una soluzione potrebbe essere di specificare alcuni valori speciali perquesti campi sconosciuti, ma questo puo creare ambiguita
◮ Es. per un anno di nascita ignoto, scrivere il valore -1
La soluzione adottata e di permettere un valore special detto null (ovalore nullo) che rappresenta un valore non specificato
Stefano Brocchi Basi di dati 32 / 93
Valori nulli
Il valore null puo essere inserito in campi di qualsiasi tipo◮ Puo sostituire interi, stringhe, dati, ...
Si puo comunque specificare che alcuni campi non possano essereuguali a null
Il valore nullo puo rappresentare, a livello logico, diverse casistichecome valori sconosciuti o inesistenti
Stefano Brocchi Basi di dati 33 / 93
Vincoli di integrita
Perche i dati in un database siano ammissibili, devono venir impostiquelli che sono chiamati vincoli di integrita
Questi vincoli sono rapprentati da alcune proprieta che devono valeresempre nel database
Nel caso in cui vengano richieste alcune modifiche che violano ivincoli, queste vengono rigettate dal DBMS
Stefano Brocchi Basi di dati 34 / 93
Vincoli di integrita
Vediamo un esempio con dei voti di studenti:
Esame Data LodeVoto
03-04-2012
01-11-2011
02-02-2010
09-09-2011
Matricola stud.
98124
12984
78265
32945
Fisica
Fisica
Informatica
Statistica
27
28
30
30
null
null
Si
No
Stefano Brocchi Basi di dati 35 / 93
Vincoli di tupla
Il tipo di vincolo piu semplice imponibile e il vincolo di tupla
Questo impone determinate proprieta che si possono controllare suogni riga singolarmente
◮ Nell’esempio precedente, la colonna lode puo indicare un ’sı’ solo se ilvoto e uguale a 30
Un particolare di tipo di vincolo di tupla e il vincolo di dominio (o suvalori) che specifica che valori possono assumere i vari campi
◮ Esempio: un voto di un esame superato deve essere ≥ 18 e ≤ 30◮ Ancora: il nome di uno studente puo contenere solo lettere
dell’alfabeto, e non caratteri come ’*’, ’ !’, ’/’
Stefano Brocchi Basi di dati 36 / 93
Vincoli di integrita
Vincoli che invece coinvolgono piu righe possibilmente in tabellediverse di chiamano interrelazionali
Utilizzati per imporre proprieta di coerenza piu complesse
Nell’esempio, si puo osservare un vincolo tipico: per ogni matricolaspecificata nella tabella esami, deve esistere nella tabella studenti unamatricola corrispondente
Inoltre, studenti diversi devono avere matricole diverse◮ In altre parole, nella tabella studenti i valori nella colonna matricola
devono essere unici
Stefano Brocchi Basi di dati 37 / 93
Chiavi
Uno dei vincoli piu importanti sono i vincoli di chiave
A livello logico, una chiave e un campo di una tupla che identificaunivocamente la tupla stessa
Se un campo e chiave di una determinata tabella, i valori in talecolonna devono essere quindi tutti distinti
Stefano Brocchi Basi di dati 38 / 93
Uso di chiavi
L’uso di chiavi e talmente importante da essere spesso notabile anchedal punto di vista utente
Una chiave identificativa usata nelle anagrafiche e il codice fiscale◮ Ogni persona ha un codice fiscale diverso, e tale codice la identifica
senza ambiguita ed evitando problemi di omonimie e date di nascitacoincidenti
◮ Un altro esempio e la matricola universitaria; questo caso rispecchiamaggiormente l’uso di chiavi nei database, in quanto la matricola e unnumero assegnato dal sistema e non derivato dai dati nella tupla
Stefano Brocchi Basi di dati 39 / 93
Chiavi per vincoli tra tabelle
Per stabilire quindi legami tra le varie tabelle, e naturale sfruttare icampi chiave
Relazioniamo due tuple inserendo in una la chiave dell’altra
Si evitano quindi problemi di ambiguita nella relazione◮ Se si usasse un alto campo con valori non distinti, potremmo non
sapere a che tupla ci riferiamo
Stefano Brocchi Basi di dati 40 / 93
Chiavi per vincoli tra tabelle: esempio
Vediamo un esempio di relazione tra tabelle imposta tramite chiave
Nome Cognome Data_nascitaSesso
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
M
M
M
F
01-02-1981
10-04-1978
12-12-1989
13-8-1985
Identificativo
1
2
3
4
Identificativo Tipo test Descrizione esito
1
2
3
4
Paziente
3
2
3
1
......
Tabella PAZIENTI
Tabella ESAMI
Chiave
tabella
pazienti
Chiave tabella esami
Stefano Brocchi Basi di dati 41 / 93
Integrita referenziale
Tali legami sono regolamentati dai vincoli di integrita referenziale
Questi sono vincoli interrelazionali che impongono che determinatecolonne debbano avere valori corrispondenti a chiavi di un’altra tabella
Una colonna corrispondente ad una chiave in un’altra tabella si diceessere una chiave esterna
Stefano Brocchi Basi di dati 42 / 93
Il linguaggio SQL
Il linguaggio SQL e lo standard principale per interfacciarsi condatabase
L’esistenza di un linguaggio comune permette a tanti programmidiversi di comunicare con la base di dati
◮ Chi scrive un programma deve solo sapere come e strutturata una basedi dati
◮ Cosı come i client, anche tutti i diversi DBMS sul servercomprenderanno questo linguaggio
SQL inizialmente era la sigla per Structured Query Language, maadesso viene considerato un nome proprio
Stefano Brocchi Basi di dati 43 / 93
Tipi SQL
Vediamo innanzitutto i tipi in SQL
Ad ogni colonna di una tabella dovra essere associato un tipo chedefinisce che genere di informazione ci puo essere contenuta
Il tipo integer rappresenta un intero generico
I tipo smallint e bigint sono interi rappresentati rispettivamente conun numero minore o maggiore di bit, per consentire un risparmio dispazio o offrire la possibilita di rappresentare numeri piu grandi
◮ Il numero di bit varia spesso a seconda della macchina; quantita tipicheper questi 3 tipi possono essere 16, 32 e 64 bit
Stefano Brocchi Basi di dati 44 / 93
Tipi SQL
I tipi float e double rappresentano numeri anche non interi
Rappresentazione in virgola mobile: si possono rappresentare numerimolto grandi e molto piccoli, ma puo essere fatta un’approssimazione
Notazione scientifica a cifre significative: vengono memorizzati duenumeri interi a e b, ed il numero rappresentato e 0.a× 2b
Un ulteriore bit viene utilizzato per il segno
double ha una precisione maggiore
Stefano Brocchi Basi di dati 45 / 93
Tipi SQL
Le stringhe vengono rappresentate tramite il tipo varchar
Oltre al tipo, viene specificata una lunghezza massima per una stringa
◮ Es. varchar(20) specifica che la relativa colonna contiene stringhelunghe al piu 20 caratteri
Per evitare ambiguita, le stringhe vengono rappresentate tra apici◮ 30 e un numero, ’30’ e una stringa
Stefano Brocchi Basi di dati 46 / 93
Tipi SQL
Per tempi e date, vengono utilizzati i tipi date e time
Date rappresentate nel formato ’aaaa-mm-gg’, i tempi come’hh:mm:ss’
Date e tempi sono comparabili tramite gli operatori <,=, >
◮ Le date > ’2013− 02− 01’ sono tutte quelle posteriori al primogennaio 2013
Stefano Brocchi Basi di dati 47 / 93
Interrogazioni
Uno dei costrutti piu utilizzati in SQL e l’operatore di selezione perestrarre dati dal database
◮ L’operazione di interrogazione e anche chiamata query
Questa operazione potra restituire sia singoli valori che tabelle
La sintassi piu semplice e la seguente:
SELECT attributi FROM tabella
In questo modo, si ottiene da una tabella un’altra tabella piu piccolasolo con i campi specificati
Specificando il carattere * al posto degli attributi, si selezionano tuttigli attributi
Stefano Brocchi Basi di dati 48 / 93
Interrogazioni: esempio
Vediamo un esempio dove estraiamo nomi e cognomi da un’anagrafica
Nome Cognome Data_nascitaSesso
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
M
M
M
F
01-02-1981
10-04-1978
12-12-1989
13-8-1985
Tabella PAZIENTISELECT nome, cognome
FROM pazienti
Nome Cognome
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
Stefano Brocchi Basi di dati 49 / 93
Interrogazioni: condizioni
Si possono specifiare delle condizioni tramite la clausola WHERE:
SELECT attributi FROM tabella WHERE condizioni
Ogni tupla selezionata che non soddisfa la condizione viene scartata
Esempio: SELECT nome, cognome FROM persone WHERE eta >25 restituisce i nomi ed i cognomi delle persone con almeno 25 anni
◮ Consideriamo un campo eta per semplicita: sarebbe sensatomemorizzare nella base di dati non l’eta ma la data di nascita, che nonvaria con il tempo
◮ Nei campi del database, e bene evitare le lettere accentate che possonodare problemi
Stefano Brocchi Basi di dati 50 / 93
Operatori logici
Per combinare varie condizioni, si possono usare gli operatori logiciAND, OR e NOT
L’operatore AND specifica che entrambe le condizioni devono essereverificate
◮ Es. per cercare tutte le persone di cognome rossi e maggiorenni, laquery potrebbe essereSELECT nome, cognome FROM persone WHERE cognome =
’rossi’ AND eta >= 18
L’operatore OR specifica che almeno una delle condizioni deve essereverificate
◮ Es. per cercare tutte le persone minorenni o con piu di 65 anniSELECT nome, cognome FROM persone WHERE eta < 18 OR eta
> 65
Stefano Brocchi Basi di dati 51 / 93
Operatori logici
L’operatore NOT specifica che una condizione non deve essereverificata
◮ Es. per cercare tutti i minorenni si potrebbe scrivereSELECT nome, cognome FROM persone WHERE NOT (eta >= 18)
Si possono combinare operatori tramite l’uso di parentesi
SELECT nome, cognome FROM persone WHERE cognome =
’rossi’ AND (eta < 18 OR eta > 65)◮ Vengono cercati tutti gli utenti di cognome Rossi ed eta non compresa
tra 18 e 65
Stefano Brocchi Basi di dati 52 / 93
Operatori logici
Si possono creare condizioni su valori nulli con le espressioni IS NULL
e IS NOT NULL
Espressioni utilizzate al posto di ’= null’, per non confondere null conun valore ma ricordare che rappresenta proprio l’assenza di valori
Un esempio che cerca tutte le persone nell’anagrafica di cui l’eta none specificata:
SELECT nome, cognome FROM persone WHERE eta IS NULL
Stefano Brocchi Basi di dati 53 / 93
Clausola like
Nella ricerca di stringhe, si puo utilizzare la clausola LIKE per cercarestringhe strutturate in un determinato modo
Utile per esempio quando si effettua una ricerca e si ha un’incertezzasu alcuni caratteri
Nella stringa da usare con LIKE, il carattere ’ ’ corrisponde ad uncarattere qualsiasi, e ’%’ ad un qualsiasi numero di caratteri
◮ Es. SELECT ... WHERE nome LIKE ’paol ’ seleziona tutte lepersone con nome che inizia con ’paol’ e seguito da un altro carattere(come ’paolo’, ’paola’ ma non ’paolino’)
◮ Es. SELECT ... WHERE nome LIKE ’s%’ seleziona tutte le personecon nome che inizia per ’s’
Stefano Brocchi Basi di dati 54 / 93
Ordinamento
I dati restituiti da un’interrogazione possono essere ordinati in base aduna colonna tramite la clausola ORDER BY:
SELECT attributi FROM tabella WHERE condizioni ORDER BY attributi
Gli attributi vengono ordinati in modo crescente, aggiungendo laparola chiave DESC in modo decrescente
Specificando due (o piu) attributi si definisce l’ordinamento in caso diparita nel primo attributo
◮ Es. SELECT * FROM persone ORDER BY eta, cognome restituiscel’intera tabella persone ordinata per eta, e tra le persone con la stessaeta per cognome
Stefano Brocchi Basi di dati 55 / 93
Interrogazioni tra piu tabelle
Le interrogazioni piu complesse coinvolgono piu tabelle del database
Le informazioni nelle varie tabelle devono essere legate, tenendo inconsiderazione di come sono gestite le chiavi esterne
Il primo concetto che vedremo a riguardo e quello di prodottocartesiano tra tabelle
Stefano Brocchi Basi di dati 56 / 93
Interrogazioni tra piu tabelle: esempio
Nell’esempio sottostante, supponiamo di voler cercare nomi e cognomidi tutte le persone che hanno subito un esame dopo il 1 gennaio2013: dobbiamo combinare la tabella ’persone’ con quella ’esami’
Nome Cognome Data_nascitaSesso
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
M
M
M
F
01-02-1981
10-04-1978
12-12-1989
13-8-1985
Chiave
1
2
3
4
Id_esame Data
1
2
3
4
Paziente
3
2
3
1
...
Tabella PAZIENTI
Tabella ESAMI
...
01-02-2012
03-01-2013
07-01-2013
05-11-2012
Stefano Brocchi Basi di dati 57 / 93
Interrogazioni tra piu tabelle
Specificando nella clausola FROM piu di una tabella separata davirgole, la selezione viene fatta sul prodotto cartesiano
Questo si ottiene, a partire da due tabelle di n ed m righe, creandouna tabella di n ×m righe contenente tutte le combinazioni dellerighe delle prime due tabelle
Nel risultato, avremo righe con tutti i campi delle tabelle iniziali
Nel caso in cui due nomi di campi coincidano, si possono distinguerein base alla tabella di origine con la notazione tabella.campo
Stefano Brocchi Basi di dati 58 / 93
Interrogazioni tra piu tabelle
Nell’esempio:
Nome Cognome Data_nascitaSesso
Marco Rossi M 01-02-1981
Chiave
1
Tabella PAZIENTI X ESAMI
Marco Rossi M 01-02-19811
Marco Rossi M 01-02-19811
Marco Rossi M 01-02-19811
Luca Verdi M 10-04-19782
Luca Verdi M 10-04-19782
Luca Verdi M 10-04-19782
Luca Verdi M 10-04-19782
Andrea Neri M 12-12-19893
Andrea Neri M 12-12-19893
Andrea Neri M 12-12-19893
Andrea Neri M 12-12-19893
Maria Bianchi F 13-8-19854
Maria Bianchi F 13-8-19854
Maria Bianchi F 13-8-19854
Maria Bianchi F 13-8-19854
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
Paziente DataId
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
Stefano Brocchi Basi di dati 59 / 93
Interrogazioni tra piu tabelle
Delle righe ottenute, solo quelle dove la chiave esterna di una tabellacorrisponde alla chiave dell’altra hanno un senso
Dopo aver selezionato solo queste righe tramite clausola where, sipossono eseguire le operazioni volute sui risultati ottenuti
Nell’esempio, la query corretta poteva essere:
SELECT nome, cognome FROM persone, esami WHERE
esami.paziente = persone.chiave AND data > ’2013-01-01’
Stefano Brocchi Basi di dati 60 / 93
Interrogazioni tra piu tabelle
Vediamo come la clausola WHERE rimuove le righe che non interessano:
Nome Cognome Data_nascitaSesso
Marco Rossi M 01-02-1981
Chiave
1
Tabella PAZIENTI X ESAMI
Marco Rossi M 01-02-19811
Marco Rossi M 01-02-19811
Marco Rossi M 01-02-19811
Luca Verdi M 10-04-19782
Luca Verdi M 10-04-19782
Luca Verdi M 10-04-19782
Luca Verdi M 10-04-19782
Andrea Neri M 12-12-19893
Andrea Neri M 12-12-19893
Andrea Neri M 12-12-19893
Andrea Neri M 12-12-19893
Maria Bianchi F 13-8-19854
Maria Bianchi F 13-8-19854
Maria Bianchi F 13-8-19854
Maria Bianchi F 13-8-19854
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
Paz. DataId
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
07-01-2013 ...
05-11-2012 ...
03-01-2013 ...
01-02-2012 ...3
2
3
1
1
2
3
4
Paziente = Chiave
Paziente = Chiave
ma data < 01-01-2013
Stefano Brocchi Basi di dati 61 / 93
Interrogazioni tra piu tabelle
Infine l’operazione di selezione lascia solo i campi interessati:
Nome Cognome Data_nascitaSessoChiave
Luca Verdi M 10-04-19782
Andrea Neri M 12-12-19893
Paz. DataId
03-01-2013 ...2 2
07-01-2013 ...3 3
SELECT nome, cognome
Nome Cognome
Luca Verdi
Andrea Neri
...
Stefano Brocchi Basi di dati 62 / 93
Interrogazioni tra piu tabelle
Di fatto, un’operazione del genere viene eseguita con unprocedimento diverso, che porta pero allo stesso risultato
Motivazione: creare la tabella che rappresenta il prodotto cartesianosarebbe un’operazione lunga, e la maggior parte delle righe verrebberocomunque poi scartate
◮ Di fatto, vengono prima selezionate le righe delle due tabelle dove lacondizione di chiave esterna stabilisce una corrispondenza
◮ Operazione detta di JOIN che non vediamo nel dettaglio
Vengono utilizzate delle tecniche di ottimizzazione molto efficienti efacilmente automatizzabili
Questo porta ad un grande vantaggio: l’utente puo scrivereinterrogazioni nel modo che gli e piu comodo senza preoccuparsidell’efficienza, gestita in automatico dal DBMS
Stefano Brocchi Basi di dati 63 / 93
Modifiche
Si possono modificare le righe di una tabella tramite il comandoUPDATE:
UPDATE tabella SET campo = valore WHERE condizioni
Le righe che soddisfano le condizioni vengono modificate comerichiesto
◮ Es. impostare che per lo studente con matricola 12345 il cognome siaaggiornato a ’rossi’
◮ UPDATE studenti SET cognome = ’rossi’ WHERE matricola =
12345◮ Possibile impostare piu campi contemporaneamente:◮ UPDATE studenti SET cognome = ’rossi’, nome = ’marco’
WHERE matricola = 12345
Stefano Brocchi Basi di dati 64 / 93
Cancellazioni
Le cancellazioni in un database possono essere effettuate con lasintassi
DELETE FROM tabella WHERE condizioni
Tutte le righe che soddisfino le condizioni verranno rimosse◮ Es. per cancellare tutti gli esami anteriori al primo giugno 2008◮ DELETE FROM esami WHERE data < ’2008-06-01’
Se si desidera eliminare una singola riga, specificare la chiave nellecondizioni
Stefano Brocchi Basi di dati 65 / 93
Cancellazioni: problematiche
La cancellazione di righe puo generare delle problematiche, in quantopuo causare la rottura di vincoli di chiave esterna
◮ Consideriamo di cancellare una persona dall’anagrafica: tutti gli esamiriferiti a quella persona avranno una chiave esterna non riconducibilead una riga dell’anagrafica
Molte soluzioni possibili; una soluzione per esempio consistenell’eliminare anche le righe correlate ’a cascata’
La soluzione che esamineremo in dettaglio e quella della cancellazionelogica
Stefano Brocchi Basi di dati 66 / 93
Cancellazioni: problematiche
Nella cancellazione logica, quando si vuole eliminare una tupla non sirimuove fisicamente, ma si imposta un campo speciale che indica chela tupla non e piu valida
Si mantengono in questo modo i vari vincoli relativi alle chiavi esterne
In molte applicazioni, questo garantisce la tracciabilita di molteinformazioni
◮ Un esempio con studenti universitari ed esami: volendo mantenere unostorico degli esami anche per studenti gia laureati, quando unostudente conclude gli studi la relativa riga nel DB non verra eliminata,ma un eventuale campo ’iscrizione’ verra impostato a ’falso’
Stefano Brocchi Basi di dati 67 / 93
Cancellazioni logiche: esempio
Consideriamo un esempio piu complesso; immaginiamo un databaseper la gestione di un laboratorio biomedico
Ipotizziamo tre tabelle, una anagrafica con i dati dei pazienti, una coni risultati dei test di laboratorio ed una, gestita da medici, con lediagnosi basata sui risultati di un test
◮ Vedremo in seguito come organizzare il DB se ad una diagnosi puocorrispondere a piu test e viceversa
Ogni tabella avra una chiave identificativa delle righe
La tabella test tramite chiave esterna fara riferimento alla tabellaanagrafica; analogamente la tabella diagnosi fara riferimento allatabella test
Stefano Brocchi Basi di dati 68 / 93
Cancellazioni logiche: esempio
Graficamente:
Nome Cognome Data_nascitaSesso
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
M
M
M
F
01-02-1981
10-04-1978
12-12-1989
13-8-1985
Identificativo
1
2
3
4
Identificativo Tipo test Descrizione esito
1
2
3
4
Paziente
3
2
3
1
......
Tabella PAZIENTI
Tabella ESAMI
Identificativo Tipo test Descrizione esito
1
2
3
4
Esame
......
Tabella DIAGNOSI
4
2
3
1
Stefano Brocchi Basi di dati 69 / 93
Cancellazioni logiche: esempio
Desideriamo che i tecnici che eseguono i test possano indicarel’invalidazione di un test, se per esempio dopo aver inserito il test neldatabase si accorgono di aver sbagliato nella sperimentazione
Vogliamo pero garantire che se una diagnosi e stata fatta sulla base diun test errato, possiamo sempre rintracciare questo test perdeterminare le responsabilita dell’errore
Inseriamo un campo nella tabella test per specificare se un test evalido o no
◮ Immaginando un’applicazione che consente ad un medico divisualizzare i test eseguiti su un paziente, questa dovra visualizzare soloquelli validi (o almeno segnalare quelli non validi)
◮ Se una diagnosi e stata eseguita sulla base di un test poi invalidato, idati sul test errato sono comunque rintracciabili
Stefano Brocchi Basi di dati 70 / 93
Cancellazioni logiche: esempio
Per garantire il corretto funzionamento di questo meccanismo, ildatabase dovra impedire agli sperimentatori di cancellare i test
◮ Uno sperimentatore potra solo aggiungere e modificare righe nellatabella test
◮ Inoltre, sarebbe sensato che ognuno potesse modificare solo i propritest e solo il campo di validita
Per risalire dal test allo sperimentatore (o al laboratorio) che lo haeseguito, possiamo immaginare una tabella con gli sperimentatori acui la tabella test fa riferimento tramite chiave esterna
Infine, per motivi di sicurezza si potrebbe rintracciare i dati di ognicambiamento (l’utente che lo ha richiesto, data ed ora di modifica)tramite il registro del database
Stefano Brocchi Basi di dati 71 / 93
Tracciabilita
Nell’esempio, come si potrebbe mantenere la tracciabilita di un test ?◮ Desideriamo che ogni test sia riconducibile allo sperimentatore che lo
ha inserito nel sistema
Necessaria innanzitutto una tabella con le persone che possonoinserire test
Tramite chiave esterna, ogni riga della tabella TEST sara collegata alsuo responsabile in quest’ultima tabella
Questo campo verra reso non modificabile per evitare alterazionifraudolente o accidentali
Stefano Brocchi Basi di dati 72 / 93
Creazione tabelle
Una tabella puo essere creata tramite il comando CREATE TABLE:
CREATE TABLE nome (
campo tipo [vincoli],
campo tipo [vincoli],
...
)
I vincoli impongono delle caratteristiche sul relativo campo
Stefano Brocchi Basi di dati 73 / 93
Creazione tabelle
Alcuni dei vincoli piu comuni sono:
PRIMARY KEY che specifica che il campo e la chiave della tabella◮ I valori contenuti non possono avere duplicati, e ci puo essere una sola
chiave per tabella
NOT NULL specifica che il campo non puo avere valore nullo
UNIQUE specifica che il campo non puo avere duplicati◮ Pensare per esempio al nome utente usato per l’identificazione
REFERENCES tabella(campo) specifica che il campo e una chiaveesterna, e deve corrispondere al campo specificato della tabellaindicata
Vincoli piu complessi possono essere imposti con tecniche che nonapprofondiremo
Stefano Brocchi Basi di dati 74 / 93
Creazione tabelle
Un esempio per una semplice tabella anagrafica:
CREATE TABLE persone (
chiave INT PRIMARY KEY,
nome VARCHAR(20) NOT NULL,
cognome VARCHAR(20) NOT NULL,
nascita DATE
)
Stefano Brocchi Basi di dati 75 / 93
Relazioni
Spesso e necessario legare le tuple di piu tabelle tramite una relazione
Abbiamo gia visto questo meccanismo tramite il meccanismo dellechiavi esterne
◮ Un esame viene associato ad una persona inserendo la chiave dellapersona nella tupla rappresentante l’esame
Per distinguere i tipi di relazione, se ne analizza la cardinalita, ovveroa quante tuple di una tabella puo essere associata una tupla inun’altra
Stefano Brocchi Basi di dati 76 / 93
Relazioni: 1 a 1
La cardinalita definisce come possono essere strutturate le chiaviesterne; chiamiamo le due tabelle da relazionare T1 e T2
Quando ad una tupla di T1 puo corrispondere (al piu) una tupla diT2 e viceversa, la relazione e di tipo 1 a 1
Esempio: considerando T1 e T2 come tabelle anagrafiche diverse peruomini e donne, i matrimoni possono essere rappresentati conrelazioni 1 a 1
La chiave puo essere contenuta equivalentemente in entrambe letabelle
Per i non sposati, si puo specificare un valore NULL per questo campo
Stefano Brocchi Basi di dati 77 / 93
Relazioni 1 a 1: esempio
Esempio:
Nome Cognome
Marco
Luca
Andrea
Rossi
Neri
Neri
Nome Cognome
Paola
Laura
Marta
Bianchi
Bianchi
Verdi
Id
1
2
3
Id
1
2
3
Nome Cognome
Marco
Luca
Andrea
Rossi
Neri
Neri
Nome Cognome
Paola
Laura
Marta
Bianchi
Bianchi
Verdi
Id
1
2
3
Id
1
2
3
Sposato_con
2
3
null
Sposata_con
null
1
2
Tabella UOMINI
Tabella UOMINI Tabella DONNE
Tabella DONNE
Stefano Brocchi Basi di dati 78 / 93
Relazioni 1 a 1: esempio
In questo caso, si poteva pensare anche di mantenere un’unica tabellacon una relazione tra le sue stesse tuple:
Nome Cognome
Marco
Luca
Andrea
Rossi
Neri
Neri
Paola
Laura
Marta
Bianchi
Bianchi
Verdi
Id
1
2
3
4
5
6
Sposato_con
5
6
null
Tabella PERSONE
null
1
2
Sesso
M
M
M
F
F
F
Stefano Brocchi Basi di dati 79 / 93
Relazioni 1 a n
Si parla invece si relazioni 1 a n quando ad una tupla di T1 possonocorrispondere piu tuple di T2 ma non viceversa
Esempio: uno studente puo dare piu esami, ma un esame e relativoad un unico studente
Soluzione: mettere la chiave esterna nella tabella T2
Stefano Brocchi Basi di dati 80 / 93
Relazioni 1 a n: esempio
Una soluzione dell’esempio potrebbe essere:
Nome Cognome ...
Marco
Luca
Andrea
Maria
Rossi
Verdi
Neri
Bianchi
Id
1
2
3
4
Id ...
1
2
3
4
Paziente
1
2
1
2
...
...
Tabella PAZIENTI
Tabella TEST
...
...
...
5
6
7
8
1
3
2
4
...
Stefano Brocchi Basi di dati 81 / 93
Relazioni n a n
Infine, in una relazione n ad n, una tupla di T1 puo corrispondere atante tuple di T2 e viceversa
Consideriamo una tabella di diagnosi T1 basate su alcuni test in T2:una diagnosi puo basarsi su piu test, ed un test puo originare piudiagnosi
In questo caso la soluzione e piu complessa: occorre creare una nuovatabella con entrambe le chiavi di T1 e T2
Stefano Brocchi Basi di dati 82 / 93
Relazioni n a n: esempio
Soluzione nell’esempio:◮ Seguendo la relazione, si deduce per es. che la diagnosi 1 e basata sui
test 1 e 3; il test 3 ha contribuito alla diagnosi 1 e 4
Descrizione ...
...
...
...
...
Chiave
1
2
3
4
Chiave ...
1
2
3
4
...
Tabella TEST Tabella DIAGNOSI
...
...
...
...
Chiave
Diagnosi
1
1
2
3
Tabella RELAZIONE
Chiave
Test
1
3
2
1
3 4
Stefano Brocchi Basi di dati 83 / 93
Relazioni n a n ed interrogazioni
Eseguendo interrogazioni su tabelle con relazioni n ad n saranecessario tener presente questo doppio legame
Una query per estrarre i test su cui e basata la diagnosi numero 1498potrebbe essere:
SELECT test.chiave, test.descrizione
FROM test, relazione, diagnosi
WHERE diagnosi.chiave = 1498
AND diagnosi.chiave = relazione.chiaveDiagnosi
AND test.chiave = relazione.chiaveTest
Stefano Brocchi Basi di dati 84 / 93
Organizzazione dati in tabelle
Nella progettazione di un database, uno dei fattori di cui tener contoe l’evitare la ridondanza di dati
Un criterio da considerare e il seguente: se un campo di una tabelladiverso dalla chiave determina univocamente uno o piu altri campi, sipuo risparmiare spazio spezzando la tabella in due parti
◮ Esempio: consideriamo un’anagrafica, dove per ogni persona e indicatacitta di residenza, provincia e stato
◮ Tralasciando casi di omonimia, per ogni abitante della stessa cittareplichiamo dei dati inutilmente
Stefano Brocchi Basi di dati 85 / 93
Organizzazione dati in tabelle: esempio
Vediamo un esempio di tabella:
Nome Cognome ProvinciaCitta
Marco
Luca
Andrea
Maria
Rossi
Rossi
Neri
Neri
Firenze
Firenze
Firenze
Firenze
Firenze
Firenze
Firenze
Firenze
Stato
Italia
Italia
Italia
Italia
Laura
Andrea
Giovanni
...
Bianchi
Bianchi
Verdi
...
Firenze
Firenze
Firenze
...
Firenze
Firenze
Firenze
...
Italia
Italia
Italia
...
Stefano Brocchi Basi di dati 86 / 93
Organizzazione dati in tabelle: esempio
In questo caso, la soluzione consente nel creare una tabella di citta ecollegata tramite chiave esterna:
Nome Cognome Citta
Marco
Luca
Andrea
Maria
Rossi
Rossi
Neri
Neri
1
1
1
1
Laura
Andrea
Giovanni
...
Bianchi
Bianchi
Verdi
...
1
1
1
...
Provincia
Firenze
Firenze
Roma
Stato
Italia
Italia
Italia
... ...
Citta
Firenze
Scandicci
Roma
...
Chiave
1
2
3
...
Tabella PERSONE
Tabella CITTA
Stefano Brocchi Basi di dati 87 / 93
Rappresentazione DB tramite diagrammi ER
Un database puo essere rappresentato a livello logico tramite undiagramma ER (Entity-Relationship)
In questo diagramma, le entita, corrispondenti a tabelle, vengonorappresentate tramite rettangoli
Le relazioni tramite rombi
Gli attributi sono rappresentati tramite pallini collegati con la relativatabella con una linea
Stefano Brocchi Basi di dati 88 / 93
Rappresentazione DB tramite diagrammi ER
Vediamo un esempio con due tabelle studenti-esami e la loro relazione
Studenti EsamiSuperamento
Matricola Nome CognomeNascita
Nome Data Voto LodeChiave
Stefano Brocchi Basi di dati 89 / 93
Rappresentazione DB tramite diagrammi ER
Possiamo aggiungere vari altri dati per dare ulteriori informazioni,come il riempimento del pallino per le chiavi o l’indicazione dellecardinalita delle relazioni
Studenti EsamiSuperamento
Matricola Nome CognomeNascita
Nome Data Voto LodeChiave
(0..n) (1..1)
Stefano Brocchi Basi di dati 90 / 93
Rappresentazione DB tramite diagrammi ER
Ci aspettiamo che relazioni n a n siano rappresentate con altretabelle, mentre le altre tramite chiavi esterne nelle tabelle presenti
Un diagramma ER ci da una rappresentazione logica che ci permettedi capire facilmente quali sono i dati rappresentati nel database
Stefano Brocchi Basi di dati 91 / 93
Database e fogli di calcolo
Tramite appositi programmi, si possono importare ed esportaredatabase in fogli di calcolo
Varie opzioni a disposizione per specificare, per esempio, i nomi dellecolonne
In quest’ultimo formato, i dati sono manipolabili piu semplicemente,ma diventano piu complessi controlli sull’integrita o ricerchecomplesse
Nei fogli di calcolo, e inoltre piu difficile l’automatizzazione el’efficienza decresce notevolmente rispetto ad una base di dati
Stefano Brocchi Basi di dati 92 / 93
Domande di riepilogo
Quali sono le qualita necessarie in un database ?
Come si garantisce l’interazione di piu utenti con un database ? Comeviene regolata questa interazione ?
Come si puo descrivere un database nel modello relazionale ?
Come si realizzano i legami tra le varie tabelle ? Come vengonorealizzate le relazioni 1 a n ed n a n ?
Quali sono i principali costrutti del linguaggio SQL ? In particolare,come si possono articolare interrogazioni complesse tra piu tabelle ?
Cosa e un diagramma ER, e come puo essere utilizzato perrappresentare basi di dati ?
Stefano Brocchi Basi di dati 93 / 93
Top Related