MINI CORSO DI MICROSOFT ACCESS - parte 2°
|
MINI CORSO DI MICROSOFT ACCESS - parte 2° |
|
PROGETTAZIONE
DI UN DATABASE
Si
immagini di dover creare un archivio che gestisca i prestiti dei libri. Gli
archivi
principali sono gli utenti e i libri.
Per
registrare i prestiti un approccio potrebbe essere quello di aggiungere i campi
necessari nella scheda utenti (vedi esempio qui sotto):
Questo
metodo presenta 2 grossi limiti:
1)
risulta
difficile ricercare a chi sono stati prestati i libri (cerco nel primo o nel
secondo prestito ?)
2)
Occorre
prevedere a priori un numero massimo di prestiti ammessi. Oltre questo numero è
necessario modificare la struttura della tabella. (nel nostro esempio sono
ammessi solo 2 prestiti)
I
campi idLibro1 e idLibro2 contengono il codice dei libri prestati a
quell’utente. L’uso del codice (o campo chiave) evita di replicare
l’informazione (mi basta richiamare la scheda libro con quel codice per
ottenere tutte le informazioni!)
La
soluzione corretta è quella di creare (oltre ai libri e agli utenti) una nuova tabella
Prestiti che registri
le relazioni che intercorrono tra utenti e libri (i prestiti stessi).
Come precedentemente detto quando creo la tabella introduco il campo chiave che consente di localizzare in maniera univoca un prestito. I campi idUtente e idLibro rappresentano i campi chiave delle tabelle messe in relazione tra loro dal prestito stesso. La scelta del tipo numerico dipende dal fatto che i campi di collegamento sono di tipo contatore che è una variante del tipo numerico.
Ad
ogni prestito viene creata una nuova scheda (nella tabella prestiti) dove il
campo idUtente contiene il codice della persona che ha richiesto il prestito;
nel campo idLibro metto il codice del libro richiesto in prestito. Le date
rappresentano delle informazioni aggiuntive legate all’operazione in
questione.
Come prima l’uso dei campi chiave consente di evitare la replicazione delle informazioni relative all’utente e al libro nella scheda prestito.
Lo schema relazionale è il seguente:
Esercizio: Costruire un database che consenta di registrare tutti i propri parenti di ordine e grado di parentela (ovvero il proprio albero genealogico). Per ogni persona prevedo al massimo un solo coniuge
In
generale quando si progetta un archivio occorre identificare i soggetti (es.
libri ed utenti). Qualsiasi relazione che intercorre tra i soggetti (es.
prestiti) implica la creazione di una tabella (Tabelle SLAVE) che avrà nella sua struttura i
campi chiave delle tabelle dei soggetti (TABELLE MASTER) coinvolte nelle
relazioni.
Relazioni
1è
Partiamo considerando come esempio la gestione dell'anagrafica degli studenti di una scuola. Per ogni alunno alcune informazioni sono chiaramente di pubblico dominio, vedi il nominativo, la matricola, e la classe. Altre invece potrebbero appartenere alla cosiddetta sfera dei dati sensibili come il gruppo sanguineo, eventuali allergie alimentari, etc. Queste informazioni sono utilizzate da particolari servizi della scuola come la mensa o l'infermeria. Per implementare correttamente queste informazioni una soluzione potrebbe essere la seguente:
La
prima tabella è Alunni
|
Matricola |
Contatore |
Identifica
lo studente in maniera univoca ed è quindi il campo chiave della tabella
stessa |
|
Nominativo
|
Testo
(di 20 caratteri) |
Conviene
indicizzarlo poiché ricerche per nominativo saranno molto probabili |
|
Classe |
Testo
(di 4 caratteri) |
Questo campo è indicizzato |
|
AltreInfo |
... |
... |
La
seconda tabella è Alunni
|
Matricola |
Intero lungo |
Identifica
i dati riservati di un certo studente ed è il campo chiave della tabella
stessa |
|
Gruppo sanguineo |
Testo
(5 caratteri) |
|
|
Allergie |
Memo |
|
|
AltreInfoRiservate |
... |
... |
Lo schema relazionale è:
Per garantire la sicurezza sui dati riservati la tabella Alunni
In generale quando ho due tabelle in relazione 1è 1 l'alternativa è rappresentata da un'unica tabella i cui campi sono ottenuti mediante l'unione senza duplicati dei campi presenti nelle due tabelle.
Le motivazioni che portano a preferire il modello 1è 1 rispetto ad una singola tabella sono:
Privacy: per separare le informazioni riservate da quelle di pubblico dominio
Performance: per separare i dati meno utilizzati da quelli di uso più frequente. Si consideri una tabella composta da un centinaio di campi dove la gran parte delle singole voci è utilizzata solo in certe occasioni. Separando i dati meno usati da quelli richiesti più frequentemente ottengo una struttura più snella ed efficiente.
Relazioni
1èN
Si
consideri l’esempio della gestione delle assenze degli studenti di una scuola.
I soggetti della relazione sono gli alunni stessi e pertanto la tabella Alunni sarà
la tabella
MASTER seguente:
Le
assenze possono essere registrate in una tabella Assenze con questa struttura
dove
il campo “Matricola” contiene il campo chiave dello studente al quale
l’assenza si riferisce.
Tra la tabella Master (Alunni) e quella Slave (Assenze) si instaura una
relazione tramite il campo “MATRICOLA”.
Ad ogni (1) scheda alunno possono corrispondere infinite (N) assenze.
In
Access la definizione delle relazioni instaura meccanismi automatici che
consentono di avere in linea, quando carico una scheda della tabella master, tutte le schede delle tabelle collegate. Quindi
appena carico un alunno immediatamente ho a disposizione anche tutte le sue assenze.
La struttura presentata consente di effettuare velocemente
queste statistiche
-
Totale
assenze per istituto
-
Totale
assenze per alunno
-
Totale
assenze per classe
-
Totale
assenze per periodo
Notare che per ogni statistica ho sempre un campo nella struttura dati abbinato all’informazione richiesta. Esempio: il totale assenze per periodo richiede l'inserimento della data dell’assenza. Pertanto le statistiche che si intendono ottenere su un DB influiscono in modo determinante sulla struttura finale del nostro archivio.
Relazioni
NçèNQuando
si progetta un Database occorre porre attenzione a non creare un archivio dove
l’informazione venga replicata inutilmente (ridondanza).
Si
immagini di dover impostare un archivio dove vengano registrati tutti i voti degli studenti di un istituto.
La
prima tabella è quella degli Alunni
|
Matricola |
Contatore |
Identifica
lo studente in maniera univoca ed è quindi il campo chiave della tabella
stessa |
|
Nominativo
|
Testo
(di 20 caratteri) |
Conviene
indicizzarlo poiché ricerche per nominativo saranno molto probabili |
|
CLASSE |
Testo
(di 4 caratteri) |
Questo
campo è necessario se tra le possibili statistiche abbiamo
ad esempio la
media dei voti per classe |
La
seconda Tabella è quella delle Materie. Diventa necessaria se oltre al
nome della materia devo registrare anche una serie di informazioni aggiuntive come
ad esempio il programma studi
del corso, docente etc..
|
IdMateria |
Contatore |
Campo chiave |
|
NomeMateria |
Testo
(di 40 caratteri) |
indicizzato
senza duplicati |
|
ContenutoCorso |
MEMO |
Lungo
fino a 64 mila caratteri |
La
terza tabella è quella dei voti:
|
idVoto |
Contatore |
Campo chiave |
|
Matricola |
Intero
Lungo |
Codice
dello studente al quale il voto si riferisce (indicizzato con duplicati) |
|
IdMateria |
Intero
Lungo |
Codice
della materia associata a quel voto (indicizzato con duplicati) |
|
Voto |
Byte |
Voto
vero e proprio |
|
Data |
Data |
Data
del voto |
La struttura relazionale che otteniamo è quindi:
Ecco un esempio di come potrebbero essere compilate le tabelle appena illustrate:
Tabella
Master: Alunni
Tabella
Master: Materie
Tabella
slave: Votazioni
Obbiettivo
è progettare un archivio che consenta la registrazione delle fatture emesse.
Primo passo è identificare le tabelle MASTER ovvero quelle che rappresentano i
soggetti principali del nostro archivio.
Queste
tabelle sono: Clienti e i Prodotti.
TABELLA
CLIENTI
|
idCliente |
Contatore |
Identifica
il cliente in maniera univoca ed è quindi il campo chiave della tabella
stessa |
|
RagioneSociale |
Testo
(di 20 caratteri) |
Conviene
indicizzarlo poiché ricerche per ragione sociale od ordinamenti su questo
campo saranno molto probabili |
|
… |
|
In
questa parte abbiamo tutti i campi ulteriori, necessari a registrare tutte
le informazioni associate al cliente come indirizzo, telefono, Partita IVA
etc. |
TABELLA
PRODOTTI
|
idProdotto |
Contatore |
E’
il campo chiave della tabella stessa |
|
DescrProdotto |
Testo
(di 20 caratteri) |
Conviene
indicizzarlo poiché ricerche o
ordinamenti su questo campo saranno molto probabili. |
|
… |
|
In
questa parte abbiamo tutti i campi necessari a registrare tutte le
informazioni associate al prodotto come categoria, prezzo etc etc. |
Occorre
adesso definire le tabelle necessarie per la registrazione delle fatture (in altre
parole: una vendita di N prodotti ad un determinato cliente).
Il numero di prodotti acquistati da un
cliente non è conosciuto a priori (ovvero il numero di righe di una
fattura è noto solo al momento in cui effettuo la vendita). Tra le righe di una
fattura e la testata stessa si crea quindi una relazione di tipo 1àN
(ad 1 testata N righe associate). E’ opportuno scindere l’archivio fatture
in 2 Tabelle: TestateFatture e RigheFatture. In RigheFatture
dovremo inserire un campo idFattura che mi collegal la riga stessa con la
sua testata. Inoltre, sempre in
RigheFattura, occorre registrare il codice dell’articolo (idArticolo)
la cui vendita è descritta con quella riga. La tabella TestataFattura contiene un campo idCliente che mette in
relazione quella fattura con il cliente al quale è intestata. A grandi linee le
tabelle possono essere così progettate:
TABELLA
TESTATEFATTURE
|
idfattura |
Contatore |
Identifica
la fattura in modo univoco. Potrei in alternativa utilizzare
l’abbinamento Nr. Fattura ed Anno (es. 23/2002) poiché questo nella
realtà è univoco |
|
idCliente |
Intero lungo |
Contiene
il codice del cliente al quale abbiamo intestato la fattura |
|
… |
|
In
questa parte abbiamo tutti i campi necessari a registrare tutte le
informazioni necessarie a descrivere completamente la nostra fattura. |
TABELLA
RIGHEFATTURE
|
idRiga |
Contatore |
Campo
chiave della tabella |
|
idFattura |
Intero Lungo |
Contiene
il codice della fattura che contiene quella riga. |
|
idProdotto |
Intero Lungo |
Campo
che identifica il prodotto venduto con quella riga. |
|
… |
|
In
questa parte abbiamo i campi come Quantità, prezzo unitario, sconto etc. |
Le relazioni tra le tabelle possono essere così schematizzate
Ad
un cliente corrispondono N fatture
Ad
un prodotto corrispondono N righeFatture
Questa
struttura consente velocemente di calcolare il fatturato per cliente e per
prodotto:
-
Estraendo
dalla tabella RigheFatture tutte le
schede relative ad un determinato prodotto posso calcolare velocemente il
fatturato per quel prodotto.
-
Estraendo
dalla Tabella testateFatture tutte le
schede relative ad un determinato cliente posso sommare e ottenere il fatturato
per quel cliente.
Esercizi:
-
Costruire
un Database che consenta di gestire dei listini personalizzati per ogni
cliente ovvero consenta di attribuire ad ogni prodotto
un prezzo distinto per ogni cliente
-
Costruire
un archivio che consenta la memorizzazione di un elenco di fotografie e che le
classifichi per categorie. Tenete presente che una fotografia potrebbe avere +
categorie associate.
- Costruire un archivio che consenta di gestire le vendite di televisori. Il progetto deve consentire la produzione di queste statistiche:
numero di televisori venduti per modello
calcolo del venduto distinto per marca.
distribuzione delle vendite per comune di appartenenza dell'acquirente
Riprendiamo
l’esempio della Biblioteca
Si clicca con il tasto destro sull’area dove vengono visualizzate le tabelle
e si
seleziona la voce relazioni
Appare
il pannello per la definizione delle relazioni. Sempre con il tasto
destro richiamiamo il mostra tabelle. Selezioniamo le 3 tabelle relative
all’esempio biblioteca.
Iniziamo
a definire le relazioni dalla tabella master LIBRO a quella slave PRESTITI:
Tiriamo una linea che unisca i campi collegati (idlibro).
Access propone una finestra di dialogo dove l’utente deve definire le
proprietà della relazione. Cliccare sul checkbox “. Applica integrità
referenziale” e poi sul pulsante “CREA”
Dopo
la conferma otteniamo questa relazione
Cosa
servono le relazioni:
In
Access la definizione delle relazioni tra le tabelle instaura dei meccanismi automatici che semplificano il lavoro degli utenti alle prime armi. I vantaggi sono già visibili a livello delle query ma i benefici maggiori si ritrovano nei livelli di MASCHERE (Forms) e REPORT.In
termini pratici quando definisco delle relazioni tra le tabelle obbligo
Access a
mettere a disposizione, durante il caricamento di una qualsiasi scheda
(appartenente ad una delle tabelle coinvolte nella relazione), tutte le
schede associate a quella scheda. Quando verranno fatte le Query il discorso sarà
ripreso.
Es.
se carico la scheda di un alunno automaticamente
Access mi mette in linea tutte
le sue assenze ed i suoi voti.
Un
ultimo appunto su quel check box “Integrità Referenziale” presente nella
finestra di dialogo che appare durante la definizione delle relazioni.
Es.
-
non crea
una fattura se inserisco un codice cliente inesistente
- Non crea, nella tabella Assenze
, una scheda se si riferisce ad uno studente non definito. Al posto di creare la scheda Assenza Access fornisce un messaggio di errore come il seguente:
