MS Access Problema Soluzione: archivi Progettazione di un archivio

Problema
MS Access
Paolo Manghi
ISTI-CNR
E-mail: [email protected]
Gestione delle informazioni relative
ad un’Organizzazione
 Inserimento, modifica,
cancellazione ed elaborazione delle
processi interni di
informazioni

aggiornamento e analisi
input
esterni
Organizzazione
richiesta
di servizi e
risposta
Paolo Manghi
Soluzione: archivi




Trovare il modo di conservare e
organizzare le informazioni interessanti
di una realtà per poterle recuperare ed
elaborare
Anni ’70: archivi cartacei
Oggi: archivi elettronici, database
Gli archivi sono un modello della parte
della realtà che si ritiene interessante
Paolo Manghi
Progettazione di un archivio



È improponibile progettare un archivio sulla
base del solo universo del discorso e senza
l’adozione di metodologie di progettazione
Un modello (es. un database) è il risultato
di un processo di interpretazione, guidato
dalle idee e conoscenze possedute dal
soggetto che interpreta
Definire un modello è un’operazione
complessa (intuizione e metodo)
Paolo Manghi
1
Modellazione e
progettazione
Metodologie


Esistono metodologie di modellazione che forniscono gli
strumenti e linguaggi per:
 individuare e rappresentare le componenti interessanti
dell’universo del discorso, al fine di realizzarne un
modello concettuale;
 per successivamente convertire il modello concettuale
in un database relazionale
Il modello concettuale descrive la realtà in maniera
intuitiva (“poco” informatica) ed è allo stesso tempo
facilmente trasformabile in un database, cioè in un
modello della realtà interpretabile da un computer
Organizzazione
A
Modello
concettuale
Descrizione
della natura della realtà
in termini di classi e associazioni
modello ER di un universo del
discorso è detto schema
A
B
Mappatura
in modello
relazionale
equivalente
B
Soluzione
anni ‘70
Creazione DB
Archivio
cartaceo
Database
Paolo Manghi
Linguaggio per la definizione
di modelli: formalismo grafico ER
ER
Paolo Manghi
ER: classi e attributi
Si modellano le classi, cioè collezioni di entità dalla
struttura omogenea
lingueParlate
Persone
Il
telefono
nome
La classe Persone: una collezione di entità persone con
proprietà lingueParlate, telefono, nome
Attributi
totale
strutturato
multivalore
Paolo Manghi
parziale
unione
Paolo Manghi
2
Aspetto linguistico
Es. Associazioni (1:1)
Aspetto linguistico
Es. Associazioni (1:n)
(1:1) associazione CorsiTenuti-TenutoDa
tra Professori e Corsi: ogni professore deve
tenere un corso (totale) e ogni Corso è
tenuto da un professore (totale)
corsoTenuto
Professori
(1:n) associazione Tesista-Relatore tra
Professori e Studenti: ogni professore può
avere uno, nessuno o più tesisti (parziale),
mentre uno studente può avere nessuno o
un relatore (parziale)
tenutoDa
relatoreDi
Corsi
Professori
Paolo Manghi
tesistaDi
Studenti
Paolo Manghi
Aspetto linguistico
Es. Associazioni (n:n)
(n:n) associazione CorsiSeguitiStudentiIscritti tra Studenti e Corsi: ogni
studente segue almeno un corso (totale) e
ogni corso è seguito da almeno uno
studente (totale)
studentiIscritti
Corsi
Esempio:
Segreteria dell’Univ. di Pisa


Gestione degli studenti e degli esami da
essi passati
Le classi di entità in gioco sono

corsiSeguiti
Studenti
Paolo Manghi

Classe degli Studenti: tutti gli studenti iscritti
all’università, con proprietà Nome, Matricola,
Provincia, Data di nascita
Classe degli Esami: tutti gli statini (prove
ufficiali) reative agli esami passati dagli
studenti, con proprietà Corso, Voto, Lode,
Data, Candidato.
Paolo Manghi
3
Modello concettuale
Soluzione anni ’70 (1/2)

Uno schedario di studenti

• nome, matricola, provincia, data di nascita
esamiSuperati passatoDa
Studenti
Esami
Ad ogni studente corrispondeva una
scheda con tutti i suoi dati:

Uno schedario degli esami

Ad ogni esame sostenuto
corrispondeva uno statino:
• candidato, voto, lode (si/no), data, corso
Paolo Manghi
Soluzione anni ’70 (2/2)

Per cercare gli esami passati dagli
studenti di Pisa, l’impiegato doveva:
Cercare nello schedario studenti tutte
le schede relative a studenti di Pisa
 Per ogni scheda trovata:
Paolo Manghi
Soluzione con Database Relazionale

Un database relazionale



Una collezione di dati elettronici che
rappresentano/modellano una realtà di
interesse
Ad ogni collezione di entità nella realtà di
interesse corrisponde una tabella nel database
• leggere il numero di matricola
• Cercare nello schedario degli esami, tutti
gli statini relativi al numero di matricola
dello studente
Paolo Manghi
Paolo Manghi
4
Tabella Studenti
Tabella Esami
Struttura tabella:
Struttura tabella:
Nome: Text
Matricola: Number
Provincia: Text(2)
DataNascita: Date
Corso: Text
Candidato: Text
Data: Date
Voto: Number(2)
Lode: Si/No
Record
Campo
Nome
Matricola
Provincia DataNascita
Materia
Candidato
Data
Voto
Isaia
171523
PI
01/01/1980
BD
Isaia
12/01/01
28
Lode
Rossi
167459
LU
03/10/1975
BD
Rossi
15/01/01
30
Si
Bianchi
179856
LI
12/07/1978
FP
Bianchi
13/02/01
30
No
Bonini
175649
PI
25/12/1980
BD
Bonini
15/01/01
25
No
LMM
Isaia
11/02/01
18
No
No
Valore
Paolo Manghi
Struttura Tabella

Una tabella è una collezione di dati caratterizzati
dalla stessa struttura:
 Insieme di coppie (NomeCampo,TipoCampo)
 Un tipo qualifica quali valori possono essere
inseriti in un campo e di conseguenza quali
operazioni sono consentite su di esso:
• Nome in Studenti è un campo di tipo Text: una
sequenza di caratteri di lunghezza arbitraria; due
valori Text possono essere concatenati per
ottenerne uno solo che li mette insieme
• DataNascita è un campo di tipo Date: un valore di
tipo gg/mm/aaaa; un valore Date può essere
passato a funzioni che ritornano l’anno, il giorno o il
mese del valore.
Paolo Manghi
Paolo Manghi
Chiave Primaria (1/3)


Definizione: se un campo di una tabella è
definito come chiave primaria, non
esistono due record nella tabella che
hanno gli stessi valori per quel campo
Uso: la chiave primaria di ogni tabella è
definita da chi definisce il database, per
avere modo di distinguere univocamente
i record di una tabella
Paolo Manghi
5
Chiave Primaria (2/3)

Nella realtà degli studenti non esistono
due studenti con la stessa matricola


Chiave Primaria (3/3)

Nella realtà degli Esami non possono esistere due
esami con lo stesso candidato e la stessa materia:
 la chiave primaria dichiarata è la coppia di campi
(materia, candidato)
 in alternativa un nuovo campo, codiceEsame: a
volte può essere conveniente definire una
proprietà fittizia, non presente nella realtà di
interesse, che compia la funzione di chiave
primaria (ad esempio quando la chiave reale è
composta da troppi campi)
Nella tabella Studenti la chiave primaria dichiarata è il
numero di matricola
Ogni tentativo di inserire nella tabella il record di uno
studente che ha lo stesso valore del campo matricola
di un altro già presente nella tabella, viene impedito e
causa un messaggio di errore
Paolo Manghi
Chiave Esterna (1/3)


Ultima nozione fondamentale è quella di
Chiave Esterna di una tabella
Una chiave esterna è un campo che
contiene il valore di una chiave primaria:

Questo mette in relazione il record che
contiene un certo valore per la chiave esterna
con il record, in un’altra tabella, che contiene
quel valore per la chiave primaria
Paolo Manghi
Nella realtà degli Esami?
Paolo Manghi
Chiave Esterna (2/3)

Per i nostri scopi, in quale tabella
dell’esempio deve essere definita
una chiave esterna?
Nella tabella Esami

Quale campo potrebbe essere
chiave esterna?
Nessuno: nessun campo della tabella ha
valori nella chiave primaria della tabella
Studenti
Paolo Manghi
6
Chiave Esterna (3/3)

Se si definisse il campo Candidato
come chiave esterna, quali problemi
potremmo avere?
Tabella Esami corretta
La tabella Esami deve essere definita
Struttura tabella:
come:
Corso: Text
Candidato: Number
Data: Date
Voto: Number(2)
Lode: Si/No
Studenti omonimi: in una
ricerca, gli esami passati da
studenti diversi con lo stesso
nome verrebbero associati ad
entrambi gli studenti!!!
Materia
Candidato
Data
Voto
BD
171523
12/01/01
28
BD
167459
15/01/01
30
Si
FP
179856
13/02/01
30
No
BD
175649
15/01/01
25
No
LMM
171523
11/02/01
18
No
Paolo Manghi
Lode
No
Paolo Manghi
Relazione tra tabelle
Schema relazionale
Studenti
Nome
Matricola
Provincia
DataNascita
Isaia
171523
PI
01/01/1980
esamiSuperati passatoDa
Esami
Studenti
Esami
Corso
Candidato
Data
Voto
BD
171523
12/01/01
29
Il campo Candidato è chiave esterna di Esami.
Contiene valori della chiave primaria Matricola di
Studenti. Il valore di Candidato mette in relazione
il relativo record con il record dello studente che
ha passato l’esame. Paolo Manghi
Nome
Provincia
Matricola
DataNascita
Studenti
Esami
Candidato*
Voto
Corso
Lode
Data
Paolo Manghi
7
Ricerca dei dati


Un database relazionale consente la
ricerca dei dati attraverso un particolare
linguaggio di interrogazione detto SQL
In SQL è possibile formulare richieste del
tipo:

MS Access (1/2)

Singola tabella:
Un Sistema per la Gestione di Basi
di Dati (SGBD), altrimenti detto
DataBase Management System
(DBMS)
• Dammi i nomi degli studenti nati prima del 1980
• Dammi tutti gli esami passati prima del 12/31/2001

Tabelle multiple:
• Dammi tutti i nomi e i voti dei corsi che sono stati
passati da studenti di Pisa
• Dammi il nome degli studenti e il numero di esami
da essi passati tra il 2000 e il 2001
Paolo Manghi
MS Access (2/2)

Un insieme di strumenti per la

Gestione automatica ed efficiente del
database:
• Definizione delle tabelle (campi e tipi) e delle
relazioni tra di esse
• Query di inserimento, cancellazione, modifica e
ricerca dei record nelle tabelle

Definizione di applicazioni con interfacce
utente per la gestione del database

Paolo Manghi
Esercizio

Definire un database Access che consenta la
gestione delle informazioni relative agli Agenti
dell’FBI. Di ogni agente interessa il nome, il
codice e la data di assunzione. Ogni agente
lavora per un dipartimento, il quale è
caratterizzato da una specializzazione (stragi,
cospirazioni, guerre, etc.). Ci interessa fare
ricerche


Maschere e Report

Paolo Manghi
sugli agenti in base a continente, stato e indirizzo dei
dipartimenti per cui lavorano;
sui dipartimenti in base alle date di assunzione degli
agenti;
altro...
Paolo Manghi
8
Definizione del database

Creazione delle tabelle


Attributi e tipi


Creazione delle associazioni
Integrità referenziale
Cancellazione e modifica a cascata
 Caselle combinate



Definizione delle query
Inserimento dati: rispetto
dell’integrità referenziale
Selezione della tabella coinvolta
Selezione degli attributi coinvolti



Query a più tabelle

Una query è a tutti gli effetti una tabella



Paolo Manghi
Query: tipologie

Query per la ricerca

Query per il calcolo


Concetto di giunzione
I record vengono calcolati dinamicamente al
momento della esecuzione
Può essere utilizzata per effettuare altre query
Paolo Manghi
Esercizio: il database

Selezione e visualizzazione di record
Max, min, media, somma, etc.

Catena di agenzie immobiliari. Degli acquirenti
ci interessa il nome il telefono, la città in cui
intendono comprare, i mq desiderati e la cifra
che sono disposti a spendere. Dei proprietari ci
interessa sapere il nome, il telefono e le case in
vendita. Delle case ci interessano i mq, il
prezzo, l’indirizzo e i proprietari. Delle agenzie ci
interessa sapere il nome, il comune, la
provincia, il telefono, i clienti e i venditori.
Sappiamo che:


Paolo Manghi
Da visualizzare
Da sottoporre a vincoli (And e Or)
ogni venditore può riferirsi solo all’agenzia del comune
in cui vende
Ogni città ha una sola agenzia
Paolo Manghi
9
Esercizio:le query






Trovare il nome, il telefono e i mq di chi vende a Milano
una casa per meno di 150.000 Euro
Trovare il nome, il telefono e gli Euro di chi intende
acquistare una casa di 80mq a Roma
Trovare il prezzo medio delle case in vendita a Parma
Trovare i prezzo massimo tra tutte le case in vendita a
Pontedera (PI) e Empoli (FI)
Trovare il nome, il comune, la privincia e il numero di
telefono delle agenzie che hanno acquirenti per la
provincia di Pisa
Trovare tutti i nomi e i telefoni dei venditori e degli
acquirenti le cui richieste in mq e Euro hanno uno scarto
di 20mq e 10.000 Euro rispettivamente.
Paolo Manghi
Esercizio: il database

Ci interessa trattare i dati delle sale
cinematografiche italiani.



Di ogni cinema interessa sapere il nome, la città e le
sale.
Le sale sono caratterizzate da un nome, da un film
in proiezione, dal prezzo del biglietto, data di inizio e
di fine proiezione del film e dal numero di posti a
sedere.
[Dei film interessa sapere il titolo, il regista e l’anno
di uscita.]
Paolo Manghi
10