Database - Web server per gli utenti dell`Università degli Studi di

Basi di dati
teoria … e pratica con Microsoft Access
Basi di dati
Sono una delle applicazioni informatiche che
hanno avuto il maggiore utilizzo in uffici, aziende,
servizi (e oggi anche sul web)
Avete già interagito (magari
inconsapevolmente) con dei
sistemi di gestione di basi di dati:
all’anagrafe, in segreteria studenti,
in biblioteca, …
2
Basi di dati
L’obiettivo è quello di memorizzare grandi quantità
di informazioni, rendendone disponibili anche le
operazioni di modifica e di reperimento
Una base di dati è solo software?
No!
Ad esempio, gli archivi genealogici esistono
da diversi decenni. Noi ci occuperemo
di sistemi informativi informatizzati
3
Basi di dati e DBMS
Base di dati: collezione di dati omogenei
DBMS (Database Management System): software
in grado di gestire collezioni di dati che siano grandi,
condivise e persistenti, garantendo affidabilità e
privatezza, in modo efficiente ed efficace
Grandi: ordine dei giga- o tera-byte
Condivise: più utenti devono potervi accedere simultaneamente
Persistenti: i dati vengono mantenuti, la loro esistenza non è limitata al periodo d’uso
Affidabili: i dati devono essere mantenuti anche in caso di malfunzionamento
Privatezza: i dati devono essere protetti
Efficiente: tutte le operazioni devono essere svolte in tempi accettabili per l’utente
Efficace: capacità di rendere produttiva l’attività dell’utente
4
DBMS
 Permettono di definire in modo semplice la
struttura della base di dati e forniscono dei
comandi per l’accesso alle informazioni.
In genere si usano per




Inserire i dati
Rimuovere i dati
Aggiornare i dati
Effettuare operazioni di ricerca
 I moderni DBMS forniscono la possibilità di
accesso simultaneo ai dati garantendone la
consistenza
5
DBMS
DBMS
Inserimento nuovo
allevamento
inserisce nuovo
animale
Trasferimento animali
amministratore
Macellazione
Morte animali
base di dati
(es. anagrafe centralizzata animali)
6
DBMS
Utente
dati
dati
dati
dati
DBMS
Programma
applicativo
Non ci occuperemo dell’organizzazione e della gestione di
DBMS ma della progettazione e dell’utilizzo del programma
7
applicativo…
Problema affrontato
raccogliere, organizzare, conservare
e gestire dati omogenei e strutturati
ANIMALE
ANIMALI
Avrà un nome, un azienda di
appartenenza,
una matricola, ecc.
Ognuno avrà il proprio
nome, matricola, ecc.
singolo
molti
8
Problema affrontato
Richiesta di interventi legati alla condizione nella carriera produttiva
Avrà una data, delle caratteristiche associate (quantità, qualità)
… diventa interessante mantenere informazioni su
quali animali sono stati fecondati, hanno partorito,
sono stati visitati dal veterinario e con quale risultato
… quindi mettere in relazione le informazioni relative
agli animali e quelle relative agli alla loro carriera
produttiva e agli interventi connessi
9
Problema affrontato
 Data una realtà da modellare (es. animali e fase
produttiva, operatori che intervengono, veterinario)
 Capire quali informazioni sono utili (es. “matricola” è
utile per rappresentare gli animali, nome e cognome per
operatori e veterinari)
 Capire come le informazioni utili sono correlate
(es. chi è stato fecondato, chi ha partorito, chi era responsabile
di svolgere e seguire il lavoro)
 Sapere chi può accedere a quali informazioni
per eseguire quali azioni
 Avere strumenti per lavorare sui dati (es. quante
fecondazioni sono state fatte alla vacca Rosina nel 2006? Con
quale risultato medio?)
10
Progettazione di una base di dati
1. Analisi dei requisiti
individuare e studiare le funzionalità che il
sistema dovrà fornire
2. Progettazione
(a) concettuale
(b) logica
(c) fisica
3. Collaudo
verifica del corretto funzionamento del
sistema
11
Progettazione di una base di dati
Requisiti della base di dati
Progettazione
Progettazione
concettuale
Progettazione
logica
Progettazione
fisica
Prodotto della progettazione
12
Ontologia-Analisi dei requisiti
Raccolta e studio delle funzionalità che il sistema
dovrà avere. Comporta l’interazione con gli utenti
del sistema e si conclude in una descrizione
informale dei suoi requisiti
Descrizione
informale
13
Progettazione concettuale
Ha lo scopo di rappresentare la realtà di
interesse in termini di una descrizione precisa e
completa ma indipendente dai criteri di
rappresentazione usati dal sistema informatico
scelto per gestire la base di dati
(rappresentazione astratta)
Schema
concettuale
14
Progettazione logica
Ha lo scopo di rappresentare la realtà di
interesse in termini di una descrizione ancora
indipendente dai dettagli fisici ma concreta, in
quanto presente nei sistemi di gestioni delle basi
di dati. Lo schema concettuale definito nella fase
precedente viene tradotto nello schema logico
Schema
logico
15
Progettazione fisica
Lo schema logico viene completato con le
specifica dei parametri fisici di memorizzazione
dei dati (organizzazione dei file e degli indici). Si
definisce lo schema fisico dei dati che dipende
dal sistema di gestione di basi di dati scelto
Schema
fisico
16
Progettazione concettuale
Schema
concettuale
17
Il modello Entità-Relazioni (E-R)

Consente di rappresentare la realtà di interesse
tramite un insieme di costrutti

Ogni costrutto ha una rappresentazione grafica
corrispondente. Ad esempio:
entità
relazione
attributo semplice
attributo composto
……..
18
Entità
Sono classi di oggetti, che hanno tutti le
stesse proprietà ed esistono in modo
autonomo; ogni entità è quindi un insieme di
oggetti, detti anche istanze o occorrenze
OPERATORI
TIPI LAVORI DI
STALLA – FASE
RIPRODUTTIVA
ANIMALI
VETERINARI
19
Relazioni (anche dette associazioni)
Sono legami logici fra due o più entità. Anche
un’associazione è un insieme, è l’insieme
delle correlazioni fra i singoli elementi delle
entità coinvolte
e1
e2
e3
Anim.1
Fecondazione
e4
Anim2
e5
Anim3
Anim4
e6
Controllo calore
Parto
20
Relazioni
In uno schema E-R ogni relazione ha un
nome che la identifica in modo univoco ed è
rappresentata mediante un rombo
Animale
Evento
LAVORO DI
STALLA - FASE
RIPRODUTTIVA
21
Esempio
Entità: Animali
Istanze: rosina, bruna, stella, mora, …
Entità: tipi di lavori
Istanze: parto, messa in mungitura, separazione colostro,
fecondazione, controllo calore, iniezione ormoni,
Evento Lavoro: relazione (anche detta associazione) fra le
entità ANIMALE e TIPO DI LAVORO
VENDITA: relazione fra le entità AZIENDA e ANIMALE
MACELLAZIONE: relazione fra le entità MACELLO e
ANIMALE
22
Attributi
Descrivono le proprietà elementari di Entità
e Relazioni. Ogni attributo assume dei valori
all’interno di un insieme di valori ammissibili
detto dominio
Nome
Giorno
Data
Mese
MATRICOLA
Anno
Attributi semplici
Attributi composti
23
Esempio
Data carico
NOME
ANIMALE
posseso
AZIENDA
Numero civico
CAP
Nome
MATRICOLA Data nascita
24
Esempio
nome
mansione
telefono
OPERATORE
Data
Nome matricola
TIPO LAVORO
EVENTO
LAVORO
ANIMALE
Nascita
Messa
in mungitura
parto
fecondazione
Anche le relazioni possono avere degli attributi che vengono rappresentati come nel
caso delle entità, ma associati ai rombi che le descrivono
25
Cardinalità delle relazioni
Per ogni entità che partecipa a una relazione è
possibile indicare il num. min e max di legami
che le sue istanze possono avere con istanze
delle altre entità partecipanti alla medesima
relazione
ANIMALE
(0,1)
(0,5000)
MACELLAZIONE
MACELLO
Un ANIMALE può essere macellato (1) oppure non essere macellato (0)
Una macello può avere non macellato (0) o averne al massimo 5000 animali macellat
26
Cardinalità delle relazioni
Se la cardinalità minima è 0 si dice che la
partecipazione dell’entità relativa è opzionale, se
la cardinalità minima è maggiore o uguale a 1, la
partecipazione è obbligatoria
(0,3)
GESTIONE
REPARTI
OPERATORI
(1,1)
Ogni reparto (fecondazione; gestazione; ingrasso) è gestito da un (1) e un solo
(1) operatore. Alcuni operatori non gestiscono alcun reparto (0) ma un
operatore può gestirne fino a tre (3)
27
Cardinalità delle relazioni
Nella maggior parte dei casi si usano solo tre
valori: zero, uno, e il simbolo N (ovvero >=1)
Se la cardinalità massima è 1 la partecipazione
all’entità può essere vista come una funzione che
associa ad una occorrenza di una entità una sola
occorrenza dell’altra entità
Se la cardinalità massima è N esiste una
associazione con un numero arbitrario di occorrenze
dell’altra entità
28
Tipi di relazioni
Osservando le cardinalità massime si ottiene la classificazione seguente
1
1
1:N
(uno a molti)
1
N
N:M
(molti a molti)
N
M
1:1
29
Esempio
CAVALLO
1
STALLO
ASSEGNATO
1
STALLO
A ogni CAVALLO è assegnato al più uno STALLO e a ogni STALLO è assegnato al più un CAVALLO
ANIMALE
N
CONTROLLO
SANITARIO
M
VETERINARIO
Ogni ANIMALE può avere DIVERSI CONTROLLI SANITARI EFFETTUATI DA DIVERSI
VETEINARI. OGNI VERINARIO PUO’ CONTROLLARE DIVERSI ANIMALI
30
Esempio
PERSONE
N
NUM ASSEGNATO
1
NUM_TELEFONO
Ogni persona può avere associati più numeri di telefono, ogni numero di telefono può essere associato al
più ad una persona

Che tipo di relazione si può stabilire tra
1. ANIMALI e CODICE ANGRAFE
2. ANIMALI e CARRIERA RIPRODUTTIVA
3. ANIMALI e AZIENDE
31
Associazioni a molte entità
Le associazioni possono collegare più di due entità,
per esempio il concetto di CONTROLLO SANITARIO,
inteso come Lavoro di stalla di un certo
VETERINARIO e un certo OPERATORE rispetto a
un certo ANIMALE, potrebbe essere rappresentato
come
AZIENDE
ANIMALI
CONTROLLI
SANITARI
VETERINARI
32
Identificatori (chiavi)
Ogni entità è un insieme di oggetti aventi le
stesse proprietà. È necessario poter identificare
in modo univoco ciascuna istanza di un’entità
Identificatore interno: sottoinsieme di attributi che
costituiscono una chiave per l’entità
Identificatore esterno: quando non è sufficiente utilizzare
un sottoinsieme di attributi ma l’entità partecipa a una relazione con
cardinalità (1,1), i suoi elementi possono essere identificati tramite tale
relazione
33
Esempio: identificatore interno
Propritario
Indirizzo
AZIENDA
Cod. fiscale
(identificatore interno)
Nome
ANIMALE
Sesso
…
Matricola

(identificatore interno)
Vi vengono in mente altri esempi?
34
Esempio: identificatore esterno
Matricola
cane
(1,1)
ISCRIZIONE
(1,N)
Libro genealogico
madre
nome
Nome
Indirizzo Città
sesso
Quando gli attributi interni non sono sufficienti si possono considerare attributi di più entità.
35
Esempio: identificatore esterno
 Ad esempio, nel caso precedente che considera tutti I cani iscritti
a tutti i libri razza, non c’è garanzia che i numeri di matricola siano
univoci
 Per identificare in modo univoco un cane servirà quindi, oltre al
suo numero di matricola, anche il nome del libro razza a cui è
iscritto
 Quindi un identificatore corretto per l’entità CANE è dato dal suo
attributo Matricola e dall’entità LIBRI RAZZA, in particolare
dall’attributo Nome di LIBRO RAZZA, che è un identificatore esterno
 Naturalmente questo funziona perchè ad ogni CANE è associata
uno e un solo LIBRO RAZZA
36
Generalizzazioni
Rappresentano legami logici tra una entità E
detta padre e più entità E1, E2, …, En, dette
entità figlie. L’entità E è più generale e
comprende le entità figlie
… ritorna una struttura gerarchica,
ad albero. Questo tipo di struttura
è fondamentale nell’informatica …
37
Generalizzazioni
matricola anagrafe
Nome azienda
Attributi
Indirizzo
comuni
Nascita
caprini
capre
becchi
reparto
reparto
Sotto categorie
Condizione riproduttiva
Livello produttivo
38
Generalizzazioni
Ogni proprietà dell’entità padre è anche una
proprietà delle entità figlie (ereditarietà)
Es. I becchi hanno un indentificativo anagrafico e un indirizzo
aziendale
Ogni occorrenza di una entità figlia è anche
occorrenza dell’entità padre
Es. I becchi sono caprini
39
Semantica di uno schema concettuale




Una entita’ E e’ un insieme variabile Et
(in funzione del tempo t)
Un attributo a (di E) e’ una funzione
(parziale) variabile at:Et
Da a valori in
un insieme costante Da (il dominio di a)
Una relazione R tra E1 … En e’ un
sottoinsieme variabile Rt di E1t x … x Ent
una istanza di E (al tempo t) e’ un
elemento e di Et
40
Semantica di uno schema concettuale



Vincolo (m,n) per la partecipazione di E
nella relazione R tra E e E’ e’ vero se
m<={x’|(x,x’) in Rt}<=n per ogni t e x in Et
Attributo a e’ identificatore (chiave) di E
sse at:Et
Da e’ totale ed iniettiva
E generalizzazione di E’ (ovvero E’
specializzazione di E) sse E’t e’ un
sottoinsieme di Et
41
Per il progetto GESTIONE CANILE
Pensate alla realtà dell’ modellare e
costruite lo schema E-R, rispondendo
alle seguenti domande:
1.
2.
3.
4.
Quali sono le entità coinvolte?
Quali le relazioni?
Che attributi servono?
Quali sono gli attributi univoci (chiavi) che
si devono usare?
42
Progettazione logica
Schema
logico
43
Modello Relazionale
Si basa sul concetto matematico di
Relazione e sul concetto intuitivo di Tabella
e permette di costruire un modello dei dati
Esistono diversi approcci alla
modellazione dei dati: modello
gerarchico, modello reticolare,
modello a oggetti, noi studieremo
il modello relazionale
44
Base di dati come insieme di tabelle
I dati che formano una base di dati relazionale
sono contenuti in un insieme di tabelle Ti. Ogni
tabella è una relazione, in senso matematico
Cosa vuol dire?
45
Relazione
Siano
Dnomi = { bruna, bionda, stella }
Dlavori = {parto, fecondazione, messa in asciutta,
messa in mungitura}
Prodotto cartesiano
Tutti i nomi combinati con tutti i gruppi
Dnomi x Dlavori =
{
(bruna, parto), (bruna, fec.), (bruna, asciutta),(bruna, mungitura),
(bionda, parto), (bionda, fec.), …,
…, (stella, mungitura)
}
3 x 4 = 12 elementi
46
Relazione
Il prodotto cartesiano, associando tutti con tutti,
non porta molta informazione
In generale ci interessa solo un sottoinsieme
delle possibili associazioni, ovvero una relazione
Es. { (bionda, manza), (bruna, lattazione), (stella, asciutta) }
Una relazione può essere rappresentata come una
tabella
bionda
bruna
stella
parto
mungitura
asciutta
47
Caso generale
Di , i [1, n] dominio di una data caratteristica
(dominio = insieme di tutti i valori possibili per tale caratteristica)
Prodotto cartesiano
D1 x D2 x … x Dn = { (v1, v2, …, vn):
v1  D1,
v2  D2,
…,
vn  Dn
}
Gli elementi del prodotto cartesiano sono anche detti
ennuple o tuple
48
… continua
Relazione
R  D1 x D2 x … x Dn
È un sottoinsieme del prodotto cartesiano
n è la cardinalità della relazione
49
… continua
nome = { bionda, bruna, moro }
nascita = { 2000, 2004, 2003 }
sesso = { femmina, maschio }
nome x nascita x sesso =
{ (bionda,2000, femmina), (bionda,2000,maschio),
(bionda,2004, femmina), (bionda,2004, maschio),
(bionda,2003, femmina), (bionda,2003, maschio),
(bruna, 2000, femmina), (bruna, 2000, maschio),
(bruna, 2004, femmina), (bruna, 2004, maschio),
(bruna, 2003, femmina), (bruna, 2003, maschio),
…}
50
… continua
La relazione contiene i legami tra i valori e ci dice
chi è nato in quale anno, ed a quale sesso
appartiene
Nome
Nascita
bionda
bruna
moro
2000
2000
2003
Sesso
femmina
femmina
maschio
Potrebbero sorgere dei problemi nella relazione precedente?
51
Struttura di una tabella
attributi
n1
vi1
n2
nn
vi2
vin
record ri
 Una tabella è un insieme di oggetti detti record
 Ogni record corrisponde ad una riga della tabella
 I record di una tabella hanno la stessa struttura
52
Attributi e valori
 Ogni colonna della tabella corrisponde ad un
attributo
 Ogni attributo assume valori su di un dominio
(es. numeri interi, sequenza di caratteri, l’insieme {lun, mar, merc, giov, ven}, …)
 I dati contenuti in una colonna sono omogenei
53
Relazioni fra tabelle

Supponiamo ora di avere due tabelle
T1
T2
Nome
Nascita
Città
bionda
bruna
stella
moro
settembre
agosto
dicembre
aprile
Pinerolo
Trino
Bra
Novi
Provincia
TO
VC
CN
AL
Possiamo fare il prodotto cartesiano T1 x T2 delle due tabelle?
Sì!
54
Attenzione …
 In questo caso ogni record è costituito da più colonne
T1
Nome
Nascita
bionda
bruna
stella
moro
settembre
agosto
dicembre
aprile
 Nel fare il prodotto cartesiano i record non vanno spezzati!!
T1 x T2 = { (r1, r2) : r1  T1 e r2  T2}
(bionda,settembre)
(Pinerolo,TO)
55
Risultato …
T1 x T2 = { ( ( bionda, settembre ) , ( Pinerolo,TO ) ),
( ( bionda, settembre ) , ( Trino,VC ) ),
( ( bionda, settembre ) , ( Bra,CN ) ),
( ( bionda, settembre ) , ( Novi,AL ) ),
…}
In pratica si compone ogni record di T1
con ogni record di T2
56
Risultato in forma tabellare
Nome
Nascita
Città
bionda
bionda
bionda
bionda
bruna
bruna
bruna
bruna
stella
stella
stella
stella
moro
moro
moro
moro
settembre
settembre
settembre
settembre
agosto
agosto
agosto
agosto
dicembre
dicembre
dicembre
dicembre
aprile
aprile
aprile
aprile
Pinerolo
Trino
Bra
Novi
Trino
Pinerolo
Bra
Novi
Bra
Pinerolo
Trino
Novi
Novi
Pinerolo
Trino
Bra
Provincia
TO
VC
CN
AL
VC
TO
CN
AL
CN
TO
VC
AL
AL
TO
VC
CN
57
Risultato in forma tabellare
Il numero di record della tabella
risultato è il prodotto del num. di
record di T1 per il num. di record di
T2 mentre il numero di colonne della
tabella risultato è il num. delle
colonne di T1 più il numero di
colonne di T2
58
Relazioni tra tabelle
 Anche il prodotto cartesiano fra tabelle non porta
molta informazione
 Ci fa vedere però come sia possibile definire
delle relazioni fra le tabelle e quindi come i dati
contenuti in una tabella possano essere combinati
con i dati contenuti nelle altre
 Più avanti vedremo come sia possibile combinare
tali dati per ottenere informazioni significative
59
Perchè relazionale?
 I dati sono contenuti in tabelle
 Le tabelle sono delle relazioni in senso
matematico
 È possibile definire nuove relazioni che combinano
i dati contenuti in più tabelle
 Esiste un supporto matematico formale che
consente di realizzare sistemi per l’elaborazione
dei dati rappresentati secondo il modello
relazionale
60
Osservazione
 I dati sono correlati
animale
Nome Matricola fiera
?
MANIFESTAZIONI
categoria fiera
Categoria concorso
valutatore categoria indirizzo
61
Osservazione
 I dati devono essere coerenti
Nome
bionda
…
bionda
n. anagrafe
FR 48 0201 7285
FR 48 0201 7200
Non DEVE essere possibile associare due CODICI ANAGRAFE diversi alla stessa
bovina
62
Osservazione
 I dati devono essere consistenti
Matr. nome
200
bionda
altre info …
…..
matr
nome
200
?bionda
reparto
asciutta
Se la bovina bionda abbandona l’allevamento e viene quindi cancellata dall’elenco degli
animali presenti iscritti, non devono rimanere riferimenti alla medesima bovina bionda nelle
altre tabelle della base dati
63
Osservazione
 Il modello permette di specificare informazione
incompleta
 Per rappresentare la mancanza di alcuni valori il
concetto di relazione viene esteso permettendo
l’introduzione del valore nullo (NULL)
64
Vincoli
Molti di questi controlli e/o aggiornamenti
possono essere eseguiti in modo
automatico dal sistema, a patto che i
progettisti della base di dati esprimano delle
regole (dette vincoli) che indicano quali
controlli il sistema deve effettuare
65
Vincoli
 I vincoli sono delle proprietà che devono essere
soddisfatte dalle tuple e possono coinvolgere una
o più relazioni
1. Vincoli di dominio
2. Vincoli di chiave
3. Vincoli di integrità referenziale
66
Vincoli di dominio
 I vincoli di dominio riguardano gli attributi: i
valori che i record assumono in corrispondenza
dei vari attributi sono definiti nei loro domini
 Per il sistema “asciutta” e “bionda” sono due sequenze di
caratteri, quindi, se non ci fossero vincoli, potrebbero
appartenere alla stessa colonna di una tabella
 Sul numero capretti per parto si può imporre un vincolo: deve
essere compreso 1 e 3
67
Vincoli di chiave
 Come abbiamo già visto nel modello E-R, è importante
poter identificare gli elementi, in questo caso i record,
in modo univoco
 L’identificazione viene fatta in base al contenuto dei
record medesimi, innanzi tutto definendo un insieme di
attributi che combinati insieme assumono valori diversi
per ogni record (vincolo di chiave)
 Un tale insieme è detto superchiave
68
Esempio
azienda
nome
breccia
casone
casone
breccia
contina
zucco
zucco
contina
bionda
bruna
bionda
bruna
moro
bionda
bruna
bionda
N. anagrafe vaccinazione sesso
11111
22222
33333
44444
55555
66666
77777
88888
….
….
….
….
….
….
….
….
femmina
femmina
femmina
femmina
maschio
femmina
femmina
femmina
Superchiave = { N. anagrafe}
Una superchiave minima è detta chiave primaria
69
Vincoli di integrità referenziale
 Usano il concetto di chiave esterna
 Una chiave esterna è un attributo o un insieme di
attributi di una relazione, i cui valori devono corrispondere
ai valori di una chiave primaria di un’altra relazione
 Si dice che una chiave esterna fa riferimento alla sua
chiave primaria
 Le chiavi esterne sono un meccanismo che consente di
mantenere l’integrità dei dati
70
Esempio
Condizione riproduttiva
Condiz,
nome
Asciutta bionda
Parto
stella
Parto
bionda
…
…
Dataevento
10/7/04
10/7/04
12/9/04
…
Cod anagrafe
111
222
111
…
animale
Cod.ana Nome
nome madre
111
222
333
…
caleffa
papaverina
sorda
…
bionda
stella
bruina
…
azienda origine
Interna
boschetto
Interna
…
71
Esempio
animale
Cod.anagr.
Titolo
Autore
Controllo sanitario
Cod.anagr.
CodVete
Data
veterinario
CodVete
Nome
Indirizzo
Telefono
72
Cosa significa?
1. Non si possono far visitare animali che non compaiono
nel libro stalla
2. Non si possono far visitare animali a veterinari privi di
iscrizione all’albo
3. Se si elimina un animale dal libro stalla, si eliminano
anche le informazioni ad esso correlate in modo
automatico
4. Se si modificano i codici dei veterinari secondo un nuovo
criterio di assegnazione, la tabella dei controlli verrà
aggiornata automaticamente
Mantenimento della coerenza dei dati contenuti nella base di dati
73
Traduzione da E-R a Relazionale
Costruito lo schema concettuale (modello E-R)
occorre tradurlo in uno schema logico ad esso
equivalente, allo scopo di rappresentare la realtà di
interesse in termini di una descrizione ancora
indipendente dai dettagli fisici ma vicina al modello
dei dati usato dal DBMS scelto
74
Entità
 Ad ogni entità corrisponde una relazione con lo
stesso nome e gli stessi attributi. L’identificatore
dell’entità è la chiave della relazione
Nome
Veterinario
Cognome
Codfiscale
Indirizzo
Veteinario (Nome, Cognome, Codfiscale, Indirizzo)
75
Attributi composti
 Gli attributi composti possono essere tradotti come
una relazione a parte oppure essere appiattiti nella
relazione corrispondente all’entità in questione
Via
Indirizzo
Numero civico
CAP
INDIRIZZO (Via, Numero civico, CAP, Id)
VETERINARIO (…, Via, Numero civico, CAP)
76
Associazioni molti a molti
La traduzione avviene per passi
1. Per ogni entità si costruisce la relazione
corrispondente (con gli stessi attributi)
2. Anche l’associazione viene tradotta in una
relazione in cui
i.
gli attributi dell’associazione diventano attributi della
relazione
ii.
si devono anche introdurre gli identificatori delle
entità coinvolte per mantenere il legame (Id e Codice
nella relazione PARTECIPAZIONE dell’esempio che segue)
77
Associazioni molti a molti
 Prima si traducono le entità
Titolo
(0,N)
animali
nome
PARTECIPAZIONE
(0,N)
Id
sesso
Datainizio
Concorsi fiere
Codice
premio
animale (nome, qualifica, Id)
Concorsi fiere (Titolo, codice)
78
Associazioni molti a molti
 Poi si crea una relazione per l’associazione
Titolo
(0,N)
animale
PARTECIPAZIONE
(0,N)
concorso
Codice
nome
Id
Datainizio
sesso
PARTECIPAZIONE (Datainizio, Id, Codice)
79
Associazioni molti a molti
Devono inoltre essere aggiunti opportuni vincoli
di integrità referenziale fra gli attributi della
relazione che traduce l’associazione e gli attributi
delle entità con cui si vuole mantenere il legame
Nell’esempio precedente occorre introdurre due vincoli:
uno fra “Id” di ANIMALI e “Id” di PARTECIPAZIONE
e uno fra “Codice” di CONCORSI FIERE e “Codice” di
PARTECIPAZIONE
80
Associazioni uno a molti
In questo caso ci sono due possibilità
•
Si procede come nel caso precedente delle
associazioni molti a molti
•
Si osserva che alcune relazioni condividono la
stessa chiave primaria (quelle per cui la
cardinalità dell’associazione è 1:1). Queste
relazioni possono essere essere riunite in una
sola, aggiungendo però degli attributi per
mantenere il legame con le altre entità
81
Associazioni uno a molti (1)
ANIMALE
codice
(1,1)
CONTRATTO
sesso
Nascita
ANIMALE (…)
prezzo
(0,N)
MACELLO
Nome
indirizzo
MACELLO (…)
CONTRATTO (codice, Nascita, Nome, prezzo)
Stessa traduzione del caso di associazioni molti a molti
82
Associazioni uno a molti (2)
ANIMALE
n. anagr.
(1,1)
CONTRATTO
sesso
Nascita
ANIMALE
prezzo
(0,N)
MACELLO
Nome
Città
(n.anagr., Nascita, Sesso, prezzo, Nome)
La relazione ANIMALE contiene anche le
informazioni sull’associazione CONTRATTO;
Nome serve per mantenere il legame con MACELLO
MACELLO (Nome, Città)
83
Associazioni uno a uno
In questo caso ci sono più possibilità
•
Si procede come nel caso precedente delle
associazioni uno a molti, con un numero
maggiore di possibilità di scelta della relazione
cui associare l’associazione
•
Nel caso di associazioni con partecipazione
opzionale (ovvero cardinalità minima uguale a
zero) si costruisce una nuova relazione per
l’associazione
84
Associazioni uno a uno (1)
(1,1)
ANIMALE
Matricola
(1,1)
STABULATO
sesso
ANIMALE
DataInizio
REPARTO
Numero
MetriQuadri
(Matricola, Qualifica, DataInizio, Numero)
REPARTO (Numero, MetriQuadri)
La relazione ANIMALE contiene anche le
informazioni sull’associazione STABULATO;
Numero serve per mantenere il legame con REPARTO
85
Associazioni uno a uno (2)
ANIMALE
Matricola
(1,1)
Sesso
STABULATO
DataInizio
(1,1)
REPARTO
Numero
MetriQuadri
REPARTO (Numero, MetriQuadri, Matricola, DataInizio)
ANIMALE (Matricola, Sesso)
La relazione REPARTO contiene anche le
informazioni sull’associazione STABULATO;
Matricola mantiene il legame con ANIMALE
86
Associazioni uno a uno (opzionali)
ANIMALE
Matricola
(0,1)
STABULATO
Sesso
DataInizio
(0,1)
REPARTO
Numero
MetriQuadri
REPARTO (Numero, MetriQuadri)
ANIMALE (Matricola, Qualifica)
STABULATO (Matricola, Numero, DataInizio)
Stessa traduzione del caso di associazioni molti a molti
87
Progettazione fisica
Schema
fisico
Cos’è l’SQL?
88
Le Istruzioni Fondamentali

DDL (Data Definition
Language)



creazione della base
di dati
CREATE DATABASE
creazione delle
tabelle
CREATE TABLE
DML (Data
Manipulation
Language)

inserimento delle
ennuple
INSERT INTO



interrogazioni
SELECT
eliminazione delle ennuple
DELETE
modifica della ennuple
89
UPDATE
Riepilogando …

Nelle basi di dati esiste
1. una parte invariante nel tempo , lo schema,
costituita dalle caratteristiche dei dati (nomi
degli attributi, domini, …)
1. una parte variabile, detta istanza, costituita
dai valori effettivi
90
Riepilogando …

In Access abbiamo visto come definire lo schema di
una base di dati usando il pulsante
(in inglese Design)
… che permette la definizione delle tabelle

E abbiamo anche visto come popolare la base di
dati per creare l’istanza usando il pulsante
(in inglese Open)
… che permette di “riempire le righe” delle tabelle
91
Riepilogando …

A questo punto siamo in grado di rappresentare il
“mondo” che vogliamo modellare e di raccogliere le
informazioni di interesse

Ma questo non basta … vogliamo anche poter
modificare le informazioni e, soprattutto, estrarre
dalla totalità dei dati delle informazioni specifiche

… in altre parole, abbiamo bisogno di un linguaggio
per l’interrogazione e la manipolazione dei dati …
92
SQL
SQL (Structured Query Language)
è il linguaggio che vedremo.
Permette di definire i dati (Data
Definition Language) e di
manipolarli (Data Manipulation
Language)
93
Quali operazioni?
 Proiezione
estrazione di attributi (colonne) da una
tabella
 Selezione
estrazione di tuple (righe) che verificano
un certo criterio da una tabella
 Join
estrazione di informazioni correlate,
contenute in tabelle diverse
94
Esempio di proiezione
T1
Nome
Cognome
Anna
Gigi
Iris
Rossi
Bianchi
Bianchi
Nato il
Nato a
2/2/71
23/4/80
15/9/45
TO
Ivrea
CN
La proiezione di T1 sugli attributi Nome e Cognome restituisce
T2
Nome
Cognome
Anna
Gigi
Iris
Rossi
Bianchi
Bianchi
95
Esempio di selezione
T1
Nome
Cognome
Anna
Gigi
Iris
Rossi
Bianchi
Bianchi
Nato il
2/2/71
23/4/80
15/9/45
Nato a
TO
Ivrea
CN
La selezione dei record di T1 tali che “Nato il >= 1/1/1960” restituisce
T2
Nome
Cognome
Nato il
Anna
Gigi
Rossi
Bianchi
2/2/71
23/4/80
Nato a
TO
Ivrea
96
Esempio di join
T1
Titolo
Poesie
Prosa
Elegie
Autore
Codice
T2
Utente
Rossi
Verdi
Verdi
111
222
333
Pippo
Pippo
Pluto
Cod libro
111
222
111
Il join fra le due tabelle restituisce
T3
Titolo
Autore
Poesie Rossi
Poesie Rossi
Prosa Verdi
Codice Utente
111
111
222
Pippo
Pluto
Pippo
Cod libro
111
111
222
97
Casi estremi
Nome
Ugo
Oreste
Reparto
segreteria
produzione
Tel
111111
222222
Reparto
centralino
direzione
join vuoto
Nome
Ugo
Oreste
Reparto
segreteria
segreteria
Nome
Ugo
Ugo
Oreste
Oreste
Tel
111111
222222
Reparto
segreteria
segreteria
segreteria
segreteria
Reparto
segreteria
segreteria
Tel
Reparto
111111 segreteria
222222 segreteria
111111 segreteria
222222 segreteria
prodotto
cartesiano
98
Natural join
Correla i dati sulla base di valori uguali su
attributi con lo stesso nome ed evita le ripetizioni
Titolo
Autore
Poesie Rossi
Poesie Rossi
Prosa Verdi
Titolo
Codice Nome
111
111
222
Autore
Poesie Rossi
Poesie Rossi
Prosa Verdi
Bianchi
Neri
Bianchi
Codice
111
111
222
Ripetizione!
Codice Nome
111
111
222
Bianchi
Neri
Bianchi
Esistono altre varianti dell’operatore di join che non vedremo
99
Il linguaggio SQL
Creato negli anni ’70 presso IBM
Inizialmente era un linguaggio di interrogazione
Ora è diventato il linguaggio di riferimento per le
basi di dati relazionali
Standardizzato grazie al lavoro di ISO (International
Standard Organization) e ANSI (American National Standard Institute)
100
Il linguaggio SQL
Fornisce le funzionalità per la definizione dello
schema di una base di data (Data Definition Language)
Fornisce le funzionalità per la modifica e
l’interrogazione dell’istanza di una base di dati (Data
Manipulation Language)
Vedremo in linguaggio mediante esempi
101
Access e il linguaggio SQL
Access (come vedremo) permette di operare
sui dati in modalità grafica, ovvero tramite i
pulsanti e i menu della sua interfaccia.
Tuttavia, ad ogni operazione sui dati
corrisponde del codice SQL che viene
generato in modo automatico. Si può vedere
questo codice mediante il menu Visualizza
102
Il linguaggio SQL
Permette di creare nuove tabelle, modificare le
tabelle esistenti, inserire, aggiornare, cancellare
i dati dalle tabelle
Una tabella è una collezione ordinata di attributi,
ciascuno con il proprio dominio, e con eventuali
vincoli
103
Creazione di una tabella
create table nometabella
(
descrizione della tabella
)
Per ogni attributo si deve definire un nome, un
dominio ed eventualmente un insieme di vincoli
Ricordate i passi di Access per la definizione della struttura di una tabella?
104
Creazione di una tabella: esempio
LIBRI (Titolo, Autore, Genere, ISBN)
Comando SQL
Nome tabella
create table
Titolo
Autore
Genere
ISBN
)
LIBRI (
char(100),
Domini degli attributi
char(100),
char(100),
char(50) primary key
Vincoli
105
Domini elementari
Indicano i valori che un attributo può assumere
e le operazioni che possono essere compiute
su di esso
per le stringhe di caratteri
per i numeri interi
per i numeri con virgola
per le date
character(n)
varchar(n)
integer
float
date
Ricordate i domini di Access?
106
Vincoli intrarelazionali
Sono associati agli attributi
default valore
indica il valore che un attributo deve avere
quando viene inserito un nuovo record in cui
non viene specificato alcun valore per
quell’attributo
not null
il valore nullo non è ammesso per
quell’attributo
unique
il valore può comparire una volta sola
primary key
chiave primaria
Ricordate i vincoli di Access?
107
Esempio
create table LIBRI
(
Titolo
varchar(200)
Autore
varchar(200)
ISBN
varchar(13)
Collocazione varchar(10)
primary key (ISBN)
)
not null,
default ‘anonimo’,
not null,
unique,
create table PRODOTTI
(
Codbarre
varchar(15) not null,
Marca
varchar(100),
Costo
float default 0,
Datacquisto
date,
Scadenza
date,
primary key (Codbarre)
)
108
Vincoli interrelazionali
Sono sostanzialmente vincoli di integrità
referenziale, definiti tramite la definizione di
chiavi esterne (foreign key)
STUDENTI(Matricola, Nome, Cognome, Indirizzo, …)
chiave
ESAMI(Studente, Corso, Voto, Data)
chiave
esterna
109
Vincoli interrelazionali
In SQL si utilizza la parola chiave references
oppure foreign key
PRODOTTI (Codbarre, Marca, Costo, … , …)
create table PRODOTTI (
Codbarre varchar(15) not null,
Marca
varchar(100),
Costo
float default 0,
Datacquisto date,
Scadenza
date,
primary key (Codbarre)
)
110
Vincoli interrelazionali
In SQL si utilizza la parola chiave references
oppure foreign key
FORNITURA (Prodotto, Fornitore, Data, … )
create table PRODOTTI (
Codbarre varchar(15) not null,
create table FORNITURA (
Marca
varchar(100),
Prodotto
varchar(15)
Costo
float default 0,
references PRODOTTI(Codbarre),
Datacquisto date,
Fornitore varchar(18)
Scadenza
date,
references FORNITORI(Iva),
primary key (Codbarre)
Data
date,
)
primary key (Prodotto,Fornitore,Data)
)
111
Vincoli interrelazionali
chiave
PRODOTTI (Codbarre, Marca, Costo, … , …)
FORNITURA (Prodotto, Fornitore, Data, … )
chiave esterna
FORNITORI (Iva, Nome, Indirizzo, … )
chiave
112
Violazioni ed azioni
È possibile specificare come reagire alle
violazioni dei vincoli di integrità referenziale
cascade
delete
set default
on
update
set null
no action
Ricordate i vincoli di integrità referenziale di Access?
113
Interrogazione: select
SQL permette di specificare cosa si vuole ottenere
e non come ottenerlo (linguaggio dichiarativo)
target list
select
from
where
elenco di attributi
elenco di tabelle
clausola from
condizione
clausola where
(opzionale)
114
Esempio
PERSONALE
Nome
Cognome
Andrea
Renzo
Orestina
Giulia
Giulio
Renzo
Rossi
Verdi
Verdi
Bianchi
Neri
Neri
Qualifica
Tel
dirigente
segretario
segretario
tecnico
tecnico
consulente
338
331
331
332
332
332
select Cognome, Qualifica from PERSONALE
Cognome
Qualifica
Rossi
Verdi
Verdi
Bianchi
Neri
Neri
dirigente
segretario
segretario
tecnico
tecnico
consulente
(proiezione)
115
Esempio
PERSONALE
Nome
Andrea
Renzo
Orestina
Giulia
Giulio
Renzo
Cognome
Rossi
Verdi
Verdi
Bianchi
Neri
Neri
Qualifica
Tel
dirigente
segretario
segretario
tecnico
tecnico
consulente
338
331
331
332
332
332
select * from PERSONALE where Tel=331
Nome
Renzo
Orestina
Cognome
Verdi
Verdi
Qualifica
Tel
segretario
segretario
331
331
(selezione)
116
Esempio
OPERE
Titolo
Poesie
Poesie
Storia
Italiano
Autore
Bianchi
Rossi
Rossi
Verdi
AUTORI
Cognome
Nascita
Residenza
Bianchi
Rossi
Verdi
…..
…..
…..
Verona
Padova
Genova
select OPERE.Titolo, OPERE.Autore, AUTORI.nascita
from
OPERE,AUTORI
where ( OPERE.Autore = AUTORI.Cognome and
AUTORI.Residenza = ‘Padova’ )
Titolo
Autore
Poesie
Storia
Rossi
Rossi
Nascita
…
117
Clausola where
La clausola where ammette come argomento una
espressione booleana costruita combinando
semplici predicati con gli operatori and, or, not
Ogni predicato usa gli operatori =, <, >, <=, >=, <>
per confrontare i valori degli attributi con delle
espressioni
Nel caso più semplice si confronta il valore di un
solo attributo, ma ci possono essere casi più
complessi
118
Clausola where: esempio
select * from IMPIEGATO where
(Dipartimento = ‘amministrazione’ or
Dipartimento = ‘produzione’ )
select * from DISCHI where
(Genere = ‘rock’ and Durata > 60 )
select * from PERSONE where
(Nascita < #1/1/1942# and Sesso=‘F’)
119
Interrogazione: join
AUTORI ( Cognome, Nome, Nascita, …)
OPERE ( Titolo, Autore, Anno, Genere, …)
select *
from AUTORI join OPERE
on (AUTORI.Cognome=OPERE.Autore)
tabelle
attributi da correlare
Ne riparleremo in Access
120
Funzioni di aggregazione
A volte si desidera estrarre informazioni elaborando i dati
calcolare il numero medio di libri letti da ciascun utente
della biblioteca
calcolare il profitto massimo relativo alle vendite nell’anno
1998
calcolare l’età dei partecipanti a un corso in base all’anno
di nascita
calcolare i prezzi + IVA dei prodotti su di un catalogo …
Esistono delle funzioni che si possono usare, per es. min, max,
somma, conteggio, media … le vedremo in Access
121
Altre operazioni
Select e join non consentono di modificare il contenuto
della base di dati
Inserimento di nuovi dati
Azioni
desiderate
Aggiornamento di dati
Cancellazione di dati
122
Inserimento di nuovi dati
insert into nometabella
(
elenco attributi
)
values
(
elenco valori
)
Attributi e valori vanno separati da una virgola
Per i valori ci sono delle convenzioni: le stringhe di
testo vanno scritte tra apici mentre le date vanno
scritte tra # … #
123
Inserimento di nuovi dati: esempio
insert into PERSONALE
(Nome,Cognome,Qualifica,Nascita)
values
('Marco','Pautasso','tecnico',#3/7/1975#)
insert into UTENTI
(Cognome, Tel, Codice)
values
('Vassallo', 453334, 'U004')
124
Inserimento di nuovi dati: esempio
UTENTI
Cognome
Indirizzo
Tel
Email
Codice
insert into UTENTI
(Cognome, Tel, Codice)
values
('Vassallo', 453334, 'U004')
Cognome
Indirizzo
Tel
Vassallo R.
null
453334
Email
Codice
null
U004
In corrispondenza dei campi non indicati verrà inserito valore null
125
Modifica di dati
update nometabella set
...
attributoi = expri
...
where condizione
Se non specificate nessuna condizione le modifiche verranno effettuate su
tutta la tabella!
126
Modifica di dati: esempio
PRODOTTI
Nome
Costo
torcetti
canestrelli
focaccia
margherita
Meringhe
2
2.5
0.9
2
1.8
Genere
dolce
dolce
salato
salato
dolce
update PRODOTTI set
Costo = Costo * 1.2
where (Genere=‘dolce’)
In un colpo solo si incrementa il costo di tutti i dolci di
un certo valore percentuale
127
Cancellazione di dati
Cancellazione di una tabella
drop table nometabella
drop table PRODOTTI
Cancellazione di record
delete from nometabella where
condizione
delete from PRODOTTI
where (Costo > 1.9)
Se non specificate nessuna condizione si cancellano tutti i record della
tabella!
128
SQL
Anche per SQL ci sono altri comandi più
complicati che non vedremo. Descrizioni
dettagliate si trovano sui libri di basi da dati.
Ad esempio potete consultare il cap. 4 del
libro Basi di Dati di Atzeni, Ceri, Paraboschi,
Torlone, casa editrice McGraw-Hill
129
DBMS – architettura client/server
query
DBMS
utente
risultato
130
DBMS – molti utenti!
biblioteca
bibliotecario
DBMS
bibliotecario
bibliotecario
bibliotecario
131
DBMS – molti utenti!
 I vari utenti non si accorgono gli uni degli altri. Ciascuno si
interfaccia con il DBMS come se fosse il solo utente del sistema
 Le elaborazioni sono eseguite dal processore e in molti sistemi il
processore è unico!
 Il DBMS gestisce le varie interazioni eseguendo un parallelismo
virtuale, ovvero eseguendo alcune istruzioni relative all’interazione
con il primo utente, alcune di interazione con il secondo e così via
Non entreremo nei dettagli di come viene gestito l’accesso concorrente ai dati …
ma questo parallelismo virtuale non vi ricorda nulla?
132
Basi di dati e il web
internet
server
browser
DBMS
Esempi
banche on line, negozi virtuali, motori di ricerca, banche dati aziendali,
riviste on-line …
133
Basi di dati e il web
 l’utente dichiara la propria identità (login e password)
 l’utente esegue delle operazioni
 estrazione di informazioni (es. contenuto del conto corrente)
 modifica di dati (es. emissione di un bonifico)
 l’utente lavora in parallelo con molti altri utenti
 i dati dell’utente sono riservati e devono essere persistenti
 l’interfaccia è costituita da un browser web
 i dati sono conservati in una base di dati
134
Esempio: motori di ricerca
Motori di Ricerca
Altavista
Yahoo
Google
Ask.com
Lycos
Virgilio
…
Gestiscono informazioni sui siti web
e sono accessibili via web
Ricerca di siti
Aggiornamento
del DB
DB
135
Esempio: interrogazione
Cerca le pagine che contengono
riferimenti a film di fantascienza
Non importa a quale nazione
appartenga il sito!
136
Esempio: aggiornamento
Uso di spider che seguono i
link, elaborazione automatica
Deep search
Categorie
Manca un vero motore di ricerca
ma i siti suddivisi per categorie
Directory standard
I dati sono estratti ed inseriti nel
database da esseri umani
137