Esercitazione n. 9: Creazione di un database relazionale

+
Strumenti digitali per la comunicazione A.A 2013/14
Esercitazione n. 9: Creazione di un database relazionale
Scopo: Scopo di questa esercitazione è la creazione di una base dati relazionale per la gestione della biblioteca universitaria;
il database dovrà includere due tabelle, Autori e Libri, collegate tra loro con relazione uno-a-molti.
A- Avvio di Access e creazione di un file .accdb vuoto
1.
Accendi il computer e accedi al sistema operativo digitando il nome utente e password. Al termine della fase di
avvio, sarà visualizzata la finestra principale del sistema operativo.
2.
Creare una cartella Esercitazione9 all’interno della cartella Strumenti Digitali sul desktop.
3.
Recupera dal sito del corso il file compresso contenente il materiale necessario per l’esercitazione e salvalo
all’interno della cartella Esercitazione9: Elenco_Autori.xlsx e Elenco_Libri.xlsx (file Excel contenenti i dati da
importare nel database Access, quando richiesto)
4.
Avvia il programma Microsoft Office Access 2010 (clic sul menù Start > Tutti i Programmi > Microsoft Office >
Microsoft Office Access 2010) e crea un nuovo database vuoto; salva il file con un nome a piacere (p.es biblioteca)
nella cartella Esercitazione9. Nota che l’estensione del file è automaticamente .accdb.
B- Importazione delle tabelle e modifica delle loro strutture.
5.
In MS Access, accedi alla scheda Dati esterni della barra multifunzione, quindi clic sul bottone Excel.
6.
Nella finestra di dialogo, con il pulsante Sfoglia seleziona come origine dei dati il file Elenco_Autori.xlsx, quindi
scegli l’opzione Importa dati di origine in una nuova tabella nel database corrente. Clic su OK per avviare
l’importazione guidata; nella prima schermata della procedura, conferma la casella Intestazioni di colonne nella prima
riga. Fai clic su Avanti per passare alle schermate successive. Accettare l’impostazione dei campi prevista cliccando
su Avanti. Nella schermata di richiesta della chiave primaria seleziona Nessuna chiave primaria.
7.
Salva la tabella con il nome Autori. Ripeti la procedura importando i dati del file Elenco_Libri.xlsx nella nuova
tabella Libri.
Nota: Considera le differenze rispetto a quanto svolto nella precedente esercitazione. In entrambi i
casi si è fatto ricorso ad una procedura di importazione dati da un file Excel a un database Access.
Nella precedente esercitazione si è prima costruita la struttura della tabella, inserendo i campi e le
loro proprietà, e poi sono stati importati i dati nei campi corrispondenti (questa procedura funziona
solo se il file Excel corrisponde alla struttura della tabella Access che è stata realizzata). In questa
esercitazione, invece, sono stati importati direttamente i file Excel, generando due tabelle Access
aventi la struttura ed i dati corrispondenti ai file Excel; nei passi successivi, si procederà ad impostare
le proprietà dei campi, secondo quanto desiderato.
8.
Apri la tabella Autori in modalità Struttura, e modifica le proprietà dei campi Nome, Cognome e Nazione, come
specificato nella tabella successiva.
Nome campo
Tipo Dati e Proprietà
Descrizione
Nome
Testo, dimensione: 20, indicizzato: No
Nome
Cognome
Testo, dimensione: 20, indicizzato: No
Cognome
Nazione
Testo, dimensione: 20, indicizzato: No
Nazione
ATTENZIONE: Al salvataggio della tabella Autori, appare una finestra di messaggio che segnala la
possibile perdita di dati: questo messaggio è causato dal fatto che si è ridotta la dimensione dei campi
(da 255 caratteri, valore impostato automaticamente al momento della creazione della tabella, a 20
caratteri).
9.
Apri la tabella Libri in modalità Struttura, e modifica le proprietà dei campi, come specificato nella tabella
successiva.
Nome campo
Tipo Dati e Proprietà
Titolo
Testo, dimensione: 50, indicizzato: No
Genere
Testo, dimensione: 20, indicizzato: No
Link_autore
Numerico, intero lungo, Indicizzato: Sì
(duplicati ammessi)
Prezzo
Valuta, formato Euro
Data Pubblicazione
Testo, dimensione: 4, indicizzato: No
Descrizione
Campo necessario per il
collegamento tra le 2 tabelle
Anno di pubblicazione
10. Aggiungi alla tabella Autori un campo di tipo Numerazione Automatica (nome campo ID_Autore) e impostalo come
chiave primaria (torna alla visualizzazione Struttura, aggiungi il campo di tipo Numerazione Automatica, selezionalo e
poi clic sul pulsante Chiave primaria).
Nota: un campo di tipo Numerazione Automatica permette di assegnare automaticamente un numero
univoco a ogni record di una tabella. Per questa sua proprietà, viene quasi sempre definito come
chiave primaria delle tabelle ed utilizzato per impostare le relazioni tra tabelle.
11. Aggiungi alla tabella Libri un campo di tipo Numerazione Automatica (nome campo ID_Libro) e impostalo come
chiave primaria.
C- Impostazione delle relazioni tra tabelle
12. Le due tabelle, Libri ed Autori, devono essere collegate tra loro, in modo che ogni record della tabella Libri sia
collegato ad un solo record della tabella Autori, mentre ad
ogni record della tabella Autori possano corrispondere più
record della tabella Libri (facendo l’ipotesi che ogni libro
possa essere stato scritto da un solo autore, mentre ovviamente
ogni autore può aver scritto più libri).
Come mostrato nello schema logico sottostante, si tratta di una
relazione uno-a-molti: la tabella Autori è il lato “uno” della
relazione, la tabella Libri costituisce il lato “molti”. Per creare
la relazione tra due tabelle, è necessario predisporre una
coppia di chiavi (una chiave primaria - PK – per il lato “uno”
della relazione e una chiave esterna o secondaria - FK – per il lato “molti”). Due record risulteranno collegati tra loro
quando il contenuto dei rispettivi campi chiave sarà identico.
ATTENZIONE: I due campi che costituiscono la coppia di chiavi devono avere lo stesso tipo dati e la
stessa dimensione:
1) testo-testo, stessa dimensione;
2) numerico-numerico, stessa dimensione;
Se una delle due chiavi è un campo di tipo Numerazione Automatica, l’altra chiave deve essere un
campo numerico con dimensione intero lungo.
Nel database in esame, per creare la relazione tra le due tabelle, si potranno usare i campi già presenti: il
campo ID_Autore come chiave primaria ed il campo Link_Autore come chiave esterna. Prima di
continuare, verifica che i due campi scelti soddisfino tale regola, in caso contrario modificane le proprietà.
13. Per porre in relazione le due chiavi scelte (campi ID_Autore e Link_Autore), clic sul pulsante Relazioni (barra
multifunzione-scheda Strumenti Database > gruppo Relazioni oppure scheda Tabella se ci si trova in
visualizzazione Foglio Dati di una delle due tabelle). Quindi nella finestra di dialogo Mostra tabella (visualizzabile in
caso di necessità con il pulsante Mostra tabella della barra multifunzione > scheda Strumenti relazione), aggiungi
Strumenti digitali per la comunicazione A.A 2012/13
2
entrambe le tabelle Autori e Libri (clic sulla tabella > Aggiungi, al termine clic Chiudi per chiudere la finestra
Mostra tabella ed accedere alla finestra Relazioni).
14. Metti in relazione il campo Link_Autore della tabella Libri con il campo
ID_Autore della tabella Autori: posiziona il mouse sul campo Link_Autore e
trascinalo sul campo ID_Autore (o viceversa). Appare una finestra di
impostazione della relazione (nota che il sistema imposta automaticamente la
relazione come uno-a-molti. Fai clic sul bottone Crea; verifica che la relazione
venga visualizzata correttamente, poi chiudi la finestra delle relazioni.
15. Apri ora la tabella Autori in visualizzazione Foglio dati ed inserisci un nuovo
record (a piacere). Mentre si inseriscono i dati degli autori, è possibile
visualizzare ed inserire i dati dei libri scritti da ciascun autore: clicca
sull’indicatore di espansione
nella prima cella di ogni record; appare la
tabella correlata Libri (inserisci nella tabella Libri due record con dati a
piacere per l’autore aggiunto). Chiudi le tabelle
Record della tabella Libri
correlati al record 8 della
tabella Autori
D- Inserimento integrità referenziale
16. Modifica la relazione tra le due tabelle: richiama la finestra Relazioni con il pulsante Relazioni (barra multifunzionescheda Strumenti Database > gruppo Relazioni oppure scheda Tabella se ci si trova in visualizzazione Foglio Dati
di una delle due tabelle); fai doppio clic sulla linea raffigurante la relazione ed applica l’integrità referenziale:
seleziona la check box Applica Integrità referenziale e le due check box sottostanti (cfr. figura precedente). Le
opzioni consentono di non poter creare un libro senza un autore e, qualora dovesse cambiare l’identificativo si un
autore nel campo ID_Autore, si aggiornerebbe di conseguenza il campo Link_Autore dei record correlati. Inoltre
cancellando un record di autore verrebbero eliminati tutti i record ad esso correlati.
17. Apri la tabella Libri in visualizzazione Foglio dati e verifica il contenuto del campo Link_Autore: esso contiene il
valore del campo ID_Autore del record della tabella Autori collegato al corrispondente record della tabella Libri.
18. Prova a modificare il contenuto del campo Link_Autore, inserendo nel campo ID_Autore un valore inesistente (es.
100) e tenta di salvare i dati (fai clic su un altro record qualsiasi): appare il messaggio di errore Impossibile
aggiungere o modificare il record. Nella tabella Autori è necessario un record correlato.
Questo messaggio è causato dal fatto che si è violata la regola di integrità referenziale impostata, per cui non ci
possono essere record non correlati tra le due tabelle.
Nota: L’integrità referenziale, fattore di assoluta rilevanza nei database relazionali, mira ad evitare che
vi siano record “orfani” nelle tabelle correlate. Nel nostro esempio, non può esistere un record nella
tabella Libri, che sia correlato ad un record inesistente della tabella Autori.
E- Creazione di una maschera associata alle due tabelle.
19. Crea una nuova maschera tramite la creazione guidata (sulla barra multifunzione > scheda Crea > gruppo Maschere
> clic sul pulsante Creazione guidata Maschera); specifica per ciascuna delle due tabelle Autori e Libri, i campi da
includere nella maschera (tutti ad eccezione dei campi a numerazione automatica- contatori- e dei campi chiave).
Strumenti digitali per la comunicazione A.A 2012/13
3
Scelta della/e tabella/e
Campi selezionati
ATTENZIONE: I campi da includere nella maschera provengono da due tabelle. Procedi come
segue: scegli una delle due tabelle, agendo sul menù a tendina Tabelle/query, poi scegli i campi
da includere relativi alla tabella selezionata (clic sul nome del campo nella lista a sinistra quindi
premi il pulsante con il simbolo di maggiore >). Successivamente, scegli la seconda tabella e ripeti
l’operazione. Al termine clic su Avanti per passare al secondo passo della creazione guidata
maschera.
20. Nel passo successivo della procedura guidata, scegli di visualizzare i dati in base a Autori, ed imposta il modello
Maschera con sotto-maschera.
21. Nei passi successivi della procedura, scegli il layout Foglio Dati per la sottomaschera e uno stile a piacere per la
maschera. Nell’ultimo passaggio, salva le due maschere (maschera principale e sottomaschera) con nomi a piacere.
22. Apri la maschera principale in Visualizzazione Maschera e verifica che essa contenga al suo interno la sottomaschera
impostata. Tramite la maschera, modifica i dati nelle due tabelle collegate:
–
aggiungi un autore (ossia aggiungi un record nella maschera principale);
–
aggiungi due libri all’autore (aggiungi due record nella sottomaschera Libri, visualizzata all’interno della
maschera principale);
–
cancella un autore (elimina un record dalla maschera principale): verifica che- grazie all’integrità
referenziale- vengano cancellati anche i record collegati nella sottomaschera (ossia i record sono eliminati
dalla visualizzazione della sottomaschera e sono contemporaneamente cancellati dalla tabella Libri);
Strumenti digitali per la comunicazione A.A 2012/13
4
–
cancella un libro (ossia elimina un record a piacere nella sottomaschera).
F- Modifica della struttura della maschera principale.
23. Apri la maschera principale in Visualizzazione Struttura: sposta i campi Nome e Cognome e disponili affiancati in
orizzontale (sulla stessa riga): posiziona il mouse sul bordo esterno dei controlli, quindi fai clic con il tasto sinistro e
trascina.
24. Aggiungi nella maschera principale il campo ID_Autore: con la
maschera in Visualizzazione Struttura, barra multifunzione >
scheda Struttura > gruppo Strumenti > clic su pulsante
Aggiungi campi esistenti; appare l’elenco dei campi della
tabella Autori (collegata alla maschera principale): fai doppio
clic sul campo ID_Autore oppure trascinalo nell’area della
maschera principale, quindi sposta il controllo del campo e la
sua etichetta nella posizione desiderata. Salva e chiudi.
G- Creazione di una query di selezione (query semplice).
25. Crea una nuova query semplice tramite la creazione guidata sulla barra multifunzione (accedi alla scheda Crea > clic
Creazione guidata query >seleziona Creazione guidata query semplice); specifica per ciascuna delle due tabelle,
i campi da includere (tutti ad eccezione del campo Prezzo). Nella finestra successiva della procedura guidata,
seleziona l’opzione Dettaglio (mostra tutti i campi di tutti i record). Salva la query con il nome Autori-Libri: verifica
che vengano visualizzati i record correlati delle due tabelle.
26. Torna alla visualizzazione struttura della query: elimina i campi contatore
(campi a numerazione automatica) e i campi chiave (seleziona la colonna
corrispondente al campo spostando il mouse sulla parte superiore della
colonna fino a fargli assumere la forma di una piccola freccia nera rivolta
verso il basso, quindi clic con il tasto sinistro, poi premi il tasto Canc sulla
tastiera).
27. Aggiungi nella query il campo Prezzo (posizionati sulla prima colonna
libera sulla destra e seleziona il campo Prezzo della tabella Libri sul
menù a tendina della prima cella in alto).
28. Riesegui la query con il bottone Esegui (icona raffigurante un punto
esclamativo).
29. Riapri la query in visualizzazione struttura e imposta degli opportuni
criteri per visualizzare solamente i libri scritti da un autore di
nazionalità non italiana (usando il corrispondente operatore logico Not)
ed il cui prezzo sia compreso tra 14 e 25 € estremi compresi (usando gli operatori di tipo matematico).
Per impostare i criteri richiesti dalla query procedi come segue:
–
nella cella della riga Criteri sottostante il campo Nazione digita Not Italia oppure Not “Italia”,
–
nella cella della riga Criteri sottostante il campo Prezzo digita >=14 And <=25
Esegui la query e verifica che vengano selezionati solo i record che soddisfano i criteri di ricerca impostati.
Alla fine, rimuovi i criteri.
H- Creazione di query con campi calcolati - query di calcolo.
30. Riapri la query Autori-Libri in visualizzazione struttura. Aggiungi un campo espressione per calcolare l’aumento del
12% del prezzo di ciascun libro: digita nella riga Campo di una qualsiasi colonna vuota della Query, l’espressione
Prezzo aumentato:=[Prezzo]*1,12. Esegui la query e verifica che la query presenti un campo aggiuntivo
contenente gli stessi valori del campo Prezzo aumentati del 12%.
I- Creazione di un report con totali e sub-totali.
Strumenti digitali per la comunicazione A.A 2012/13
5
31. Crea un nuovo report tramite la creazione guidata (barra multifunzione > scheda Crea > clic sul bottone per la
creazione guidata report, pulsante con la bacchetta magica); nella prima schermata della procedura guidata, scegli
la query Autori-Libri per creare il report e seleziona i campi Titolo, Data Pubblicazione, Cognome, Prezzo,
Genere.
32. Nelle successive finestre della procedura guidata, procedi come segue:
–
Seleziona l’opzione In base a Libri per visualizzare i dati;
–
Imposta il raggruppamento dati in base al campo Genere (seleziona il nome del campo nella lista a sinistra
quindi premi il pulsante con il simbolo di maggiore >);
–
Nella finestra successiva, ordina i dati in base al campo Titolo e poi clic sul pulsante Opzioni di riepilogo.
33. Nella finestra delle Opzioni di riepilogo, seleziona il valore di riepilogo Somma per il campo Prezzo, poi seleziona
l’opzione Dettaglio e riepilogo, quindi clic su OK. Con queste impostazioni, il report conterrà una riga di riepilogo
per ciascun genere letterario, in cui sarà riportata la somma dei prezzi dei libri di quel genere. Procedi alla
schermata successiva della creazione guidata.
Strumenti digitali per la comunicazione A.A 2012/13
6
34. Scegli il layout con rientri ed orientamento orizzontale per
la pagina, quindi seleziona l’opzione Regola la larghezza
dei campi per includerli tutti in una pagina.
35. Salva il report con un nome a piacere e visualizza il report
in anteprima. Qualora i dati del report non siano
visualizzati correttamente, richiama la visualizzazione
struttura ed adegua la larghezza dei controlli dei vari
campi, per far sì che vengano mostrati tutti i dati (come
mostrato nella pagina dei risultati, fig. 4).
ATTENZIONE: qualora non vengano impostate le
opzioni di riepilogo durante la procedura di
creazione guidata, apri il report in visualizzazione
struttura ed inserisci nel piè di pagina del gruppo Genere e del report, due caselle di testo:
(utilizza il controllo contrassegnato da ab|- nella scheda Struttura della barra multifunzione,
sezione Controlli). Visualizza la finestra delle proprietà di ciascuna casella di testo ed inserisci nel
campo Origine Controllo l’espressione =Sum([Prezzo])
Al termine, chiudi il report.
J- Creazione di un report con dati filtrati
36. Con la creazione guidata, realizza un nuovo report esattamente uguale (a parte lo stile grafico) a quello mostrato in
figura 5 nel documento dei risultati e fai in modo che il report visualizzi solamente i libri la cui data di pubblicazione
sia successiva all’anno 1990 8incluso).
SUGGERIMENTO: Procedi come segue:
a)
Crea prima una nuova query inserendo i campi necessari (almeno i campi presenti nel
report e i campi su cui inserire criteri di ricerca)
b) Immetti nella query il criterio per filtrare i dati in base alla data di pubblicazione >= 1990
c)
Crea il report basato sulla query, impostando la visualizzazione dati per i campi Cognome
e Nome ed ordinando in senso crescente in base al campo Data pubblicazione.
37. Chiudi MS Access (scheda File > Esci).
Strumenti digitali per la comunicazione A.A 2012/13
7