Basi di dati (database) Scopo

Basi di dati (database)
Laboratorio di Informatica
AA 2006/2007
1
Scopo
• Gestione dell’informazione
• Informazione: difficile da definire ma a tutti
è chiara l’importanza della sua gestione in
diversi tipi di attività
• Esempi: aziende, banche, anagrafi,
università, compagnie aeree, ...
Laboratorio di Informatica
AA 2006/2007
2
1
Gestione dei dati
•
•
•
•
•
Aggiunta nuovi dati;
Aggiornamento dati esistenti;
Cancellazione;
Richiesta di informazioni relative ad un utente;
Elaborazioni statistiche…
• Per eseguire tali operazioni, devono essere
allocate delle risorse.
Laboratorio di Informatica
AA 2006/2007
3
Sistema informativo
• Insieme delle risorse di un’organizzazione
dedicate alla gestione dell’informazione
• Gestione: acquisizione, elaborazione,
conservazione, produzione
• Il concetto di sistema informativo esiste da
secoli (es.: anagrafi)
Laboratorio di Informatica
AA 2006/2007
4
2
Sistema informatico
• Parte del sistema informativo che gestisce
l’informazione automaticamente
(calcolatori, reti, software, ...)
vo
ati
m
r
nfo
ai
Sistema
m
te
Sis
informatico
Laboratorio di Informatica
AA 2006/2007
5
Dati e informazione
• Dati: stringhe di caratteri, numeri,
immagini, suoni, ...
• Informazione: veicolata dai dati
opportunamente interpretati
• Esempio:
dati
“Paolo”, “Rossi”, 1100
nome, cognome del direttore, stipendio
interpretazione
Laboratorio di Informatica
AA 2006/2007
6
3
L’informazione nei
sistemi informatici
Sistema informatico
Informazione
Informazione
DATI
Rappresentazione
Interpretazione
• Basi di dati: collezione di dati che
rappresentano informazioni di interesse per un
certo sistema informativo
Laboratorio di Informatica
AA 2006/2007
7
Problemi con la gestione dei
dati
• I programmi tradizionali operano
indipendentemente su copie di dati
Prog1
Prog2
Prog3
copia1
copia2
copia3
• Problemi:
inconsistenza
ridondanza
Laboratorio di Informatica
AA 2006/2007
8
4
Inconsistenza e ridondanza
• sia D un database che contiene i nomi degli N studenti
Unimi al tempo t;
• l’utente A usa il programma A per aggiungere un nuovo
studente a D;
• nel frattempo, l’utente B usa il programma B per
aggiungere due nuovi studenti a D;
• Quanti studenti ha Unimi al tempo t+1?
• Secondo A ne ha N+1;
• Secondo B ne ha N+2;
• In realtà ne ha N+3!
• Informazioni ridondanti (N studenti replicati) sui PC di A
e di B.
Laboratorio di Informatica
AA 2006/2007
9
Soluzione ottimale
• Unica risorsa dati accessibile a più
programmi
Prog1
Prog2
Prog3
Laboratorio di Informatica
AA 2006/2007
10
5
DBMS
Data Base Management System
• Sistemi software per la gestione di
collezioni di dati che siano: grandi,
condivise, persistenti
assicurando: affidabilità e privatezza
• BASE DI DATI = collezione di dati gestita
da un DBMS
Laboratorio di Informatica
AA 2006/2007
11
Modello logico e fisico dei dati
BASE DI DATI
=
DATI + DBMS
DATI
• Modello logico: regole per strutturare i dati
secondo certe proprietà + operazioni sui
dati
• Modello fisico: rappresentazione dello
schema logico mediante strutture fisiche di
memorizzazione.
Es.:
file, liste, alberi, ... 12
Laboratorio
di Informatica
AA 2006/2007
6
DBMS: Architettura
ANSI/SPARC
utente
Schema esterno
utente
Schema esterno
utente
Schema esterno
Schema logico
Schema fisico
DATI
Laboratorio di Informatica
AA 2006/2007
13
I linguaggi nei DBMS
• DDL: data definition language
definisce gli schemi esterni, logici, fisici e
le autorizzazioni d’accesso
• DML: data manipulation language
consente di interrogare e di aggiornare la
base di dati
• Nelle basi di dati relazionali (che
vedremo), SQL contiene entrambe le
funzionalità
Laboratorio di Informatica
AA 2006/2007
14
7
Progettazione di basi di dati
• Problema: quali informazioni inserire in
una base di dati e quali legami esistono tra
di esse?
• Schema concettuale di una base di dati da
cui ricavare la struttura dei dati secondo il
modello logico del DBMS (schema logico)
• Uno strumento per esprimere schemi
concettuali: i diagrammi Entità-Relazione
(E-R)
Laboratorio di Informatica
AA 2006/2007
15
I diagrammi E-R
• Entità: classi di oggetti rilevanti
ENTITA’
• Relazioni: legami tra entità
RELAZIONE
• Attributi: descrivono proprietà
rilevanti di entità e relazioni
attributo
Laboratorio di Informatica
AA 2006/2007
16
8
Uno schema concettuale
• E-R per una base di dati in cui archiviare
studenti, materie e relativi esami
data
voto
nome
matricola
nome
STUDENTE
MATERIA
ESAME
cognome
titolare
Laboratorio di Informatica
AA 2006/2007
17
Identificatori
• Gruppi di attributi che identificano
univocamente le occorrenze di un’entità
identificatore
singolo
identificatore
multiplo
data
voto
nome
matricola
nome
STUDENTE
ESAME
cognome
MATERIA
titolare
Laboratorio di Informatica
AA 2006/2007
18
9
Cardinalità delle relazioni
• molti a molti (N-N):
N
STUDENTE
N
MATERIA
ESAME
• Ogni studente può aver sostenuto più esami
• Ogni esame può essere stato dato da più
studenti
Laboratorio di Informatica
AA 2006/2007
19
Cardinalità delle relazioni
• uno a molti (1-N):
1
STUDENTE
N
ISCR
CdL
• Ogni studente è iscritto ad un corso di
laurea
• Ogni corso di laurea può avere più iscritti
Laboratorio di Informatica
AA 2006/2007
20
10
Cardinalità delle relazioni
• uno a uno (1-1):
1
DIRETTORE
1
DIPART
DIRIGE
• Ogni direttore dirige un dipartimento
• Ogni dipartimento ha un direttore
Laboratorio di Informatica
AA 2006/2007
21
Il modello relazionale
• I dati vengono strutturati in tabelle
Tabella: STUDENTI(matr, nome, cognome)
STUDENTI
matr
305011
356433
345553
345434
nome cognome
Carlo
Rossi
Mario Verdi
Franco Verdi
Daniele Rossi
Campi: colonne della tabella
Record: righe della tabella
Laboratorio di Informatica
AA 2006/2007
22
11
Schema e istanze
STUDENTE(Matricola, Cognome, Nome, Data di Nascita),
CORSO(Codice, Titolo, Docente),
Schema
ESAME(Studente, Voto, Corso)
STUDENTE
ESAME
Matricola Cognome
6554
Rossi
8765
Neri
9283
Verdi
3456
Rossi
Nome
Mario
Paolo
Luisa
Maria
Data di Nascita
5/12/1978
3/11/1976
12/11/1979
1/2/1978
Studente
3456
3456
9283
6554
Voto
30
24
28
26
Corso
04
02
01
01
MATERIA
Codice
01
02
04
Titolo
Analisi
Chimica
Chimica
Docente
Neri
Bruni
Verdi
Istanza
Laboratorio di Informatica
AA 2006/2007
23
I legami tra i dati
• Il modello relazionale è basato sui valori
STUDENTE
Matricola Cognome
6554
Rossi
8765
Neri
9283
Verdi
3456
Rossi
Nome
Mario
Paolo
Luisa
Maria
Data di Nascita
5/12/1978
3/11/1976
12/11/1979
1/2/1978
Studente
3456
3456
9283
6554
MATERIA
Codice
01
02
04
Titolo
Analisi
Chimica
Chimica
ESAME
Docente
Neri
Bruni
Verdi
Laboratorio di Informatica
AA 2006/2007
Voto
30
24
28
26
Corso
04
02
01
01
24
12
Vincoli di integrità
• Proprietà che le istanze devono soddisfare
• Tipi di vincoli
intrarelazionali: su campi, su record, su
tabella
interrelazionali: su più tabelle
(integrità referenziale)
Laboratorio di Informatica
AA 2006/2007
25
Esempi di vincoli
Studenti
Matricola Cognome
276545
Rossi
276545
Neri
788854
Verdi
Esami
Con tabella
Studenti
Corsi
Vincoli Interrelazionali
Nome
Maria
Anna
Fabio
Nascita
23/04/1968
23/04/1972
12/02/1972
Lode Corso
e lode
01
02
03
e lode
03
Studente
276545
276545
788854
200768
Voto
28
32
23
30
Codice
01
03
02
Titolo
Analisi
Docente
Giani
NULL
NULL
Chimica
Belli
Laboratorio di Informatica
AA 2006/2007
Vincolo su
tabella
Vincolo su
record
Vincolo su
campo
Vincoli Intrarelazionali
26
13
Chiave
• Insieme minimale di attributi che identifica
univocamente i record di una tabella
Matr Nome Cognome
301 Carlo
Rossi
302 Marco
Neri
311 Guido Mauro
STUDENTE
ESAME
Matricola Cognome
6554
Rossi
6554
Rossi
6554
Rossi
Nome
Mario
Mario
Mario
Esame
Informatica
Matematica
Fisica
Data
5/12/2000
3/2/2001
3/2/2001
Voto
24
27
30
Laboratorio di Informatica
AA 2006/2007
27
Chiave esterna e
integrità referenziale
• Attributo/i che costituiscono la chiave di
un’altra tabella
Studenti
Vincolo di
integrità
referenziale
Esami
Matricola Cognome
276545
Rossi
276543
Neri
788854
Verdi
Studente
276545
276545
788854
788854
Voto
28
18
23
30
Laboratorio di Informatica
AA 2006/2007
Nome
Maria
Anna
Fabio
Nascita
23/04/1968
23/04/1972
12/02/1972
Lode
Corso
Fisica
Analisi
Analisi
e lode Algebra
28
14
Dai diagrammi E-R alle tabelle
• molti a molti (N-N):
voto
matr
N
nome
id
N
STUDENTE
MATERIA
ESAME
nome
cognome
STUDENTE(matr, nome, cognome)
MATERIA(id, nome)
ESAME(matr, id, voto)
Laboratorio di Informatica
AA 2006/2007
29
Dai diagrammi E-R alle tabelle
• uno a molti (1-N):
anno
matr
1
nome
STUDENTE
id
N
ISCR
CdL
nome
cognome
STUDENTE(matr, nome, cognome, cdl, anno)
CdL(id, nome)
• A volte è preferibile tradurre come nel caso
N-N
Laboratorio di Informatica
30
AA 2006/2007
15
Dai diagrammi E-R alle tabelle
• uno a uno (1-1):
anno
matr
1
nome
DIRETTORE
id
1
DIRIGE
DIPART
nome
cognome
DIRETTORE(matr, nome, cognome, id, nome-dip, anno)
• A volte è preferibile tradurre come nel caso
1-N
Laboratorio di Informatica
AA 2006/2007
31
Query (interrogazioni)
• Query: estrazione da un data base di
informazioni che soddisfa certe proprietà
• Come esprimere le query:
SQL: Structured Query Language
QBE: Query by Example di Access
Algebra relazionale: un linguaggio formale
...
• In comune hanno un insieme di operatori
Laboratorio di Informatica
AA 2006/2007
32
16
Operatori su tabelle
• Operatori insiemistici:
unione
intersezione
differenza
• Operatori relazionali:
proiezione
selezione
join (naturale)
binari
unari
binario
Laboratorio di Informatica
AA 2006/2007
33
Operatori insiemistici: unione
• L’unione di due tabelle con attributi
compatibili è una tabella con lo stesso
schema contenente l’unione dei record
CICLISMO ∪ CALCIO
CICLISMO
CF
RSSX
NRXY
VRDX
Cognome
Rossi
Neri
Verdi
Eta`
20
21
20
Cognom e
Rossi
Neri
Verdi
Eta`
20
21
20
CALCIO
CF
RSSY
NRXY
VRDX
CF
RSSX
NRXY
VRDX
RSSY
Cognome
Rossi
Neri
Verdi
Rossi
Eta`
20
21
20
20
Appassionati di
ciclismo o di calcio
Laboratorio di Informatica
AA 2006/2007
34
17
Operatori insiemistici:
intersezione
• L’intersezione di due tabelle con attributi
compatibili è una tabella con lo stesso
schema contenente l’intersezione dei
record
CICLISMO
CF
RSSX
NRXY
VRDX
Cognome
Rossi
Neri
Verdi
Eta`
20
21
20
Cognom e
Rossi
Neri
Verdi
Eta`
20
21
20
CICLISMO ∩ CALCIO
CF
NRXY
VRDX
Eta`
21
20
Appassionati di
ciclismo e di calcio
CALCIO
CF
RSSY
NRXY
VRDX
Cognome
Neri
Verdi
Laboratorio di Informatica
AA 2006/2007
35
Operatori insiemistici: differenza
• La differenza di due tabelle con attributi
compatibili è una tabella con lo stesso schema
contenente i record presenti nella prima ma non
nella seconda
CICLISMO - CALCIO
CICLISMO
CF
RSSX
NRXY
VRDX
Cognome
Rossi
Neri
Verdi
Eta`
20
21
20
Cognom e
Rossi
Neri
Verdi
Eta`
20
21
20
CALCIO
CF
RSSY
NRXY
VRDX
CF
RSSX
Cognome
Rossi
Eta`
20
Appassionati di
ciclismo ma non di calcio
CALCIO - CICLISMO
CF
RSSY
Cognome
Rossi
Eta`
20
Appassionati di
calcio ma non di ciclismo
Laboratorio di Informatica
AA 2006/2007
36
18
Operatori relazionali: proiezione
• La proiezione su una tabella produce una
tabella con gli attributi specificati e
contenente gli stessi record “ristretti”
Matricola
40445
43555
43566
55655
Nome
Paolo
Piero
Piero
Marco
Cognome
Rossi
Bianchi
Verdi
Rossi
CdL
STUDENTI
Fisica
Matematica
Informatica
Lettere
πCognome,Nome(STUDENTI)
Elencare cognomi e
nomi degli studenti
Cognome
Rossi
Bianchi
Verdi
Rossi
Nome
Paolo
Piero
Piero
Marco
Laboratorio di Informatica
AA 2006/2007
37
Operatori relazionali: selezione
• La selezione su una tabella produce una
tabella con gli stessi attributi contenente
record che soddisfano un certo predicato
Matricola
40445
43555
43566
55655
Nome
Paolo
Piero
Piero
Marco
Quali sono gli studenti
con nome ‘Piero’ o
iscritti a ‘Fisica’?
Cognome
Rossi
Bianchi
Verdi
Rossi
CdL
STUDENTI
Fisica
Matematica
Informatica
Lettere
σ Nome = ‘Piero’ or CdL = ‘Fisica’ (STUDENTI)
Matricola
Nome
Cognome
CdL
40445
Paolo
Rossi
Fisica
43555
Piero
Bianchi
Matematica
43566
Piero
Verdi
Informatica
Laboratorio di Informatica
AA 2006/2007
38
19
Operatori relazionali:
prodotto cartesiano
• Il prodotto di due tabelle produce una
tabella con l’unione degli attributi
contenente le concatenazioni dei record
STUDENTI
Cognome
Corso
Rossi
Bianchi
Fisica
Fisica
Nome
Materia
CdL
Fisica
Meccanica
Matematica
Algebra
STUDENTI × CdL
Tutte le possibili
concatenazioni
tra record
Cognome
Corso
Rossi
Rossi
Bianchi
Bianchi
Fisica
Fisica
Fisica
Fisica
Nome
Materia
Fisica
Meccanica
Matematica
Algebra
Fisica
Meccanica
Matematica
Algebra
Laboratorio di Informatica
AA 2006/2007
39
Operatori relazionali: join
• Il join tra due tabella produce una tabella con
l’unione degli attributi contenente le
concatenazioni dei record concordanti sugli
attributi comuni
STUDENTE Matricola Cognome
6554
8765
9283
3456
Rossi
Neri
Verdi
Rossi
Nome
Mario
Paolo
Luisa
Maria
Matricola
3456
3456
9283
Voto
30
24
28
STUDENTI
Situazione esami
degli studenti
Matricola
3456
3456
9283
Cognome Nome Voto
Rossi
Maria
30
Rossi
Maria
24
Verdi
Luisa
28
Laboratorio di Informatica
AA 2006/2007
Corso
Analisi
Fisica
Fisica
ESAME
ESAME
Corso
Analisi
Fisica
Fisica
40
20
Esercitazione
• Consideriamo la seguente basi di dati per
l’archiviazione di noleggi di CD
CLIENTE
NOLEGGIO
Cognome
Nome
ID-Cliente
ID-Cliente
ID-CD
data
Rossi
Bianchi
Verdi
Paolo
Maria
Carlo
10
11
9
10
9
11
10
1
1
3
2
23/7/2002
11/9/2002
15/2/2003
30/3/2003
ID -C D
T itolo
A rtista
1
2
3
Up
O cto ber
S yncronicity
REM
U2
P olice
CD
CLIENTE(Cognome, Nome, ID-Cliente)
CD(ID-CD, Titlo, Artista)
NOLEGGIO(ID-Cliente, ID-CD, data)
Laboratorio di Informatica
AA 2006/2007
41
Esempi di query
• Eseguire le seguenti query:
a) Artista e titolo dei cd noleggiati dal signor
Paolo Rossi;
b) Nome e cognome dei clienti che hanno
noleggiato cd dei REM;
c) Titolo dei cd che sono stati noleggiati dal
cliente avente codice 10 oppure dal cliente
avente codice 11.
Laboratorio di Informatica
AA 2006/2007
42
21