EL3-2_5ST DATABASE CON EXCELAutore: __________________________________________ Data: _________Classe: ____

ATTENZIONE

Il significato dei simboli usati in questa e nelle prossime esercitazioni è spiegato dettagliatamente nella guida http://www.classiperlo.altervista.org/Materiale/Generale/Simboli.doc (scaricala e consultala in caso di dubbi).

Il simbolo della manina in colore blu indica una domanda alla quale bisogna OBBLIGATORIAMENTE rispondere scrivendo in colore blu.

Il simbolo della manina in colore blu con la scritta Cou New indica un codice che va incollato usando Courier New in colore blu.

Il simbolo della manina in nero indica una o più immagini o schermate da incollare (protette col tuo watermark, le tue iniziali di Nome e Cognome)

Il simbolo della manina con colori attenuati indica un'operazione che bisogna svolgere, senza rispondere a nessuna domanda (non vuol dire che non devi fare nulla - significa solo che non devi scrivere niente!).

Il simbolo della manina col ciak video indica un video da registrare con Gif Recorder (http://gifrecorder.com/)e da salvare in formato gif nella cartella dell'esercitazione.recupero.

Questo simbolo indica un video di esempio o di spiegazioni da guardare su YouTube

A) OPERAZIONI PRELIMINARI

A1) Crea una sottocartella di ES3 con nome uguale a quello di questa esercitazione (EL3-2_5AST Database con Excel)

A2) All'interno della sottocartella EL3-2_5AST Database con Excel salva questo file Word

B) UN DB CON EXCEL

Come abbiamo già accennato, esistono molti programmi specifici e dedicati a svolgere le funzioni di DBMS. Tuttavia per questi nostri primi semplici esempi possiamo usare un programma già noto da tempo e molto usato per diversi scopi: Microsoft Excel.

SE NON POSSIEDI EXCEL

Se non hai Excel, puoi tranquillamente usare al suo posto qualunque alternativa anche gratuita o online. Puoi trovare un elenco di alcune possibilità qui: https://www.1and1.it/digitalguide/online-marketing/vendere-online/le-alternative-gratuite-a-microsoft-excel/

B1) Avvia Excel e crea un file di nome DB.xlsx nella cartella di questa esercitazione.

B2) Rinomina il foglio principale Libri, come mostrato in figura:

B3) In Libri crea una tabella per i libri della biblioteca contenente i campi Titolo, Autore, Editore, Anno, Collocazione. Per creare una tabella non devi far altro che assegnare alla prima cella di ogni colonna il nome del rispettivo campo.

B4) Inventa un po' di valori (almeno due record) e incolla qui sotto una schermata. ATTENZIONE: i campi sono le colonne (verticali), i record sono le righe (orizzontali).

B5) Cliccando sul + crea un nuovo foglio di lavoro Utenti per gli utenti della biblioteca:

B6) In Utenti crea una tabella per gli utenti della biblioteca contenente i campi Nome, Cognome, Indirizzo, Città, Telefono, Tessera. Inventa qualche valore a caso (almeno due record) e incolla qui sotto una schermata:

C) DB RELAZIONALE

Il nostro semplice DB contiene due tabelle: Libri e Utenti. Tuttavia queste due tabelle sono in relazione fra loro (sono collegate) in quanto un utente può prendere in prestito un libro.

Come possiamo memorizzare questa informazione nel nostro DB?

Una prima soluzione che potrebbe venirci in mente è aggiungere un campo alla tabella degli Utenti, per esempio in questo modo:

Questa soluzione però presenta alcuni problemi:

1) l'utente potrebbe chiedere in prestito più libri e in questi casi bisognerebbe aggiungere altri campi (ma quanti?)2) dove memorizzare le altre informazioni relative al prestito (es. la data di riconsegna del libro)? Bisognerebbe aggiungere ancora altri campi alla tabella...3) lo stesso libro potrebbe essere presente in più copie (es. più copie de I Promessi Sposi) oppure potrebbero esserci in biblioteca due libri diversi con lo stesso titolo: come si fa a sapere quale libro esattamente è stato preso in prestito?

Consideriamo una soluzione diversa, ma che conduce agli stessi problemi. Immaginiamo di voler aggiungere le informazioni relative al prestito nella tabella dei Libri.

C1) Spiegami come potresti aggiungere i prestiti alla tabella degli Libri (fammi un esempio inventando i dati):

C2) Quali sarebbero i problemi della soluzione precedente?

Una soluzione più intelligente è aggiungere al nostro DB una terza tabella per i Prestiti.

C3) Cliccando sul + crea un nuovo foglio di lavoro Prestiti per i prestiti della biblioteca:

C4) In Prestiti crea una tabella per i prestiti di libri della biblioteca contenente i campi Libro, Utente, Data Prestito, Durata Prestito (in giorni), Data Riconsegna. Inventa qualche valore a caso (almeno due record) e incolla qui sotto una schermata:

Cercando di inventare i valori per la tabella Prestiti probabilmente avrai incontrato il problema di come riempire i campi Libro e Utente. Cosa bisogna scriverci?

Infatti nel nostro DB le tre tabelle sono tutte collegate fra loro. Si tratta di un DB cosiddetto relazionale, poiché ci sono precise relazioni fra le tabelle. I campi Libro e Utente contenuti nella tabella Prestiti esprimono appunto l'esistenza di tali relazioni.

D) CHIAVE PRIMARIA E CHIAVE ESTERNA

La tabella dei Prestiti è collegata sia con la tabella dei Libri che con quella degli Utenti. Infatti in essa abbiamo messo un campo Libro e un campo Utente.

Cosa dobbiamo però scrivere in questi due campi? Abbiamo visto che nel campo Libro non possiamo mettere il titolo del libro, poiché possono esserci più copie dello stesso libro o addirittura più libri con lo stesso titolo.

Ovviamente non possiamo neppure usare l'Autore del libro, poiché questa informazione non permette di identificare un unico libro (lo stesso Autore può aver scritto molti libri).

Ragionando un po' vediamo che c'è un solo valore che permette di identificare univocamente uno e un solo libro nella biblioteca e si tratta della Collocazione.

COLLOCAZIONE

La collocazione (location) di un libro è rappresentata tramite un codice alfanumerico di solito apposto come etichetta sul dorso di ogni libro:

Ecco dunque in che modo potremmo mettere in relazione la tabella dei Prestiti con quella dei Libri:

Osserva che ho rinominato il campo Libro in Prestiti chiamandolo Collocazione, cioè con lo stesso nome del campo corrispondente nella tabella Libri. Non è obbligatorio usare gli stessi nomi, ma aiuta a evidenziare la relazione.

IN PRATICA

Se vogliamo indicare che un dato utente ha preso in prestito un certo libro, metteremo nel campo Collocazione del record corrispondete a quel determinato prestito, il valore del campo Collocazione di quel libro (nella tabella dei Libri)

D1) Provando a ragionare nello stesso modo, quale informazione useremo per identificare uno specifico utente (uno e uno solo) nella tabella Prestiti? (SUGGERIMENTO: devi cercare un campo della tabella Utenti che abbia un valore univoco per ogni utente, cioè per ogni record della tabella):

D2) Incolla qui sotto tre schermate in cui si vedano le tabelle Libri, Utenti e Prestiti della tua biblioteca in Excel modificate in base alle osservazioni precedenti, riempiendo i campi con dei valori sensati (a piacere):

In database relazionale le tabelle sono messe in relazione fra di loro attraverso coppie di campi. Tali coppie prendono il nome di chiave primaria (primary key) e chiave esterna (foreign key).

LA PAROLA "CHIAVE"

Nel gergo dei DB si usa spesso la parola chiave (key) per indicare un campo particolare di una tabella con un ruolo particolare. In pratica una chiave non è altro che un campo che svolge funzioni

speciali nel relazionare fra loro due o più tabelle.

Per esempio, nel caso della relazione fra la tabella Prestiti e la tabella Libri, la chiave primaria è il campo Collocazione della tabella Libri mentre la chiave esterna è il campo Collocazione della tabella Prestiti (abbiamo usato gli stessi nomi, ma potrebbero anche essere diversi).

In un DB relazionale ogni tabella deve possedere una e una sola chiave primaria.

D3) Leggi qui https://www.lifewire.com/primary-key-definition-1019179 la definizione di chiave primaria. Spiegami poi brevemente di cosa si tratta con parole tue:

CHIAVE PRIMARIA

Ogni tabella deve avere obbligatoriamente una chiave primaria cioè un campo che contenga valori unici, cioè che non possa essere uguale fra due record (righe) della tabella.

Per esempio, considerando un elenco anagrafico di persone, il Cognome di una persona non è una chiave primaria valida, poiché possono esistere due persone (record della tabella) con lo stesso cognome (es. entrambi si chiamano Parodi). Invece il Codice Fiscale è una buona chiave primaria per la tabella, in quanto non possono esistere due persone con lo stesso codice fiscale.

Se una tabella non possiede nessuna chiave primaria, si possono seguire due strade:

si usa una coppia di campi (o talvolta anche più di due) per formare la chiave primaria. Per esempio, sempre con riferimento all'archivio di persone, potremmo considerare come chiave primaria la combinazione di Nome e Cognome della persona (supponendo che tale combinazione sia unica e non ripetuta).

si inserisce un nuovo campo nella tabella e gli si assegna un numero progressivo partendo da 0 (primo record della tabella) e andando avanti con 1,2 etc. In questo modo due righe della tabella sono distinte perché hanno un numero progressivo diverso.

D4) Le nostre tabelle della Biblioteca soddisfano tutte al requisito di avere una chiave primaria? Quale tabella non è stata progettata correttamente e non possiede nessuna chiave primaria?

D5) Inventati una chiave primaria per la tabella che non ne ha una e spiegami il suo significato e funzionamento:

D6) Spiegami perché la combinazione di Nome e Cognome non rappresenta una buona chiave primaria per la tabella dell'anagrafe:

Per quanto riguarda invece la chiave esterna, in una tabella può esserci anche più di una chiave esterna (oppure nessuna).

D7) Osserva la struttura del seguente DB per catalogare una collezione di Film. ATTENZIONE: le tabelle sono rappresentate con rettangoli a bordo azzurro e dentro a ciascuna tabella sono indicati i nomi dei campi (non vengono invece indicati, in questa rappresentazione, i record e i valori assunti dai campi).

D8) Scrivi l'elenco delle tabelle del precedente DB e per ogni tabella identifica qual è la chiave primaria (SUGGERIMENTO: le chiavi primarie sono indicate con sfondo giallo):

D9) Spiegami con parole tue il collegamento fra la tabella Movies e la tabella Directors:

D10) Spiegami con parole tue qual è lo scopo della tabella MovieActor:

E) CARDINALITA' E RELAZIONE FRA TABELLE

Consideriamo due tabelle A e B all'interno di un DB e le loro possibili relazioni. Abbiamo tre casi possibili:

ogni record di A corrisponde a uno e un solo record di B e viceversa (relazione 1:1) ogni record di A corrisponde a molti possibili record di B, mentre ogni record di B

corrisponde a un solo record di A (relazione 1:molti) ogni record di A corrisponde a molti possibili record di B e viceversa (relazione molti:molti)

Il numero di relazioni che collegano fra loro due tabelle si dice cardinalità.

1) RELAZIONE 1:1 (si legge "uno a uno")

Consideriamo una tabella con gli Istituti Scolastici di una città e una tabella con i dati dei presidi (dirigenti scolastici). Poiché ogni scuola ha uno e un solo preside e poiché ogni preside dirige una e una sola scuola, ne consegue che ogni riga (record) della tabella Scuole corrisponde a una e una sola riga (record) della tabella Presidi (e viceversa). Viene mostrato un solo esempio:

TABELLA SCUOLECodice Scuola Nome Scuola Indirizzo Scuola Codice Fiscale PresideCM2344 Liceo

ParmigianiVia Formaggio 3 PTRGRF23A55H

TABELLA PRESIDICodice Fiscale Nome Cognome TelefonoPTRGRF23A55H Pietro Grifoni 010876767

Si osservi che il Codice Fiscale del preside è la chiave primaria della Tabella Presidi e viene usato come chiave esterna nella tabella Scuole. In questo caso avremmo potuto anche fare il contrario, cioè usare il Codice Scuola come chiave esterna nella Tabella Presidi e non sarebbe cambiato nulla.

E1) Inventa un esempio di due tabelle legate fra di loro con una cardinalità 1:1

E2) Spiega perché secondo te la relazione fra le due precedenti tabelle è 1:1

2) RELAZIONE 1:∞ (si legge "uno a infinito" o "uno a molti")

Consideriamo il DB di un'unica scuola, nella quale sia definita una tabella per gli Alunni e una per le Classi:

TABELLA ALUNNICodice Fiscale Nome Cognome Telefono Nome ClasseLBRRSS88A34D Alberto Rossi 0122232233 5MT

TABELLA CLASSINome Classe Piano Numero banchi5MT 12 23

Osserva come in questo caso la chiave primaria della Tabella Classi (cioè il Nome Classe, es. 5MT) sia usata come chiave esterna nella Tabella Alunni.

E3) Spiegami perché il Nome della classe può essere una buona chiave primaria della Tabella Classi:

Osserva la linea di collegamento fra le due tabelle: da una parte è indicato il numero 1 e dall'altra il valore ∞. Questo significa che ad ogni classe possono corrispondere molti (in teoria infiniti) alunni, mentre (al contrario) ogni alunno corrisponde a una sola classe.

In caso di relazione 1:∞ la chiave primaria della tabella che sta dalla parte dell'1 (NomeClasse) diventa la chiave esterna della tabella che sta dalla parte di ∞.

E4) Nel db dei film, quali tabelle stanno in relazione 1:∞? Indica per ciascuna relazione quale tabella è 1 e quale ∞ (per esempio scrivi così ALUNNI ∞ - CLASSI 1):

3) RELAZIONE ∞:∞ (si legge "molti a molti" o "infinito a infinito")

Se nella nostra scuola consideriamo la relazione fra Professori e Classi, in questo caso ci troviamo di fronte a una relazione ∞:∞ o molti a molti poiché:

ogni professore può insegnare in molte classi ogni classe ha molti professori

TABELLA CLASSINome Classe Piano Numero banchi5MT 12 23

TABELLA PROFCodice Fiscale Nome Cognome TelefonoPRLGCR88A22F Giancarlo Perlo 007010101

Nel caso di relazione molti a molti non è possibile mettere in relazione diretta le due tabelle:

Per risolvere il problema occorre creare una terza tabella intermedia, che funge da ponte fra la tabella Prof e la tabella Classi. Nel nostro caso potremmo creare una Tabella Corsi, così definita:

TABELLA CORSICodice Corso Nome Codice Prof Classe0 Patologia

InformaticaPRLGCR88A22F 5MT

La situazione è quella mostrata in figura:

Osserva che la tabella corsi è collegata dalla parte di 1 in due relazioni del tipo 1:∞:

1. la relazione fra CORSI e PROF è 1:∞ poiché ogni corso (es. Informatica in 5AT) è tenuto da un solo prof, mentre ogni prof può avere più corsi

2. la relazione fra CORSI e CLASSI è 1:∞ poiché ogni Corso corrisponde a una sola Classe, mentre ogni Classe ha più Corsi

E5) Nel db dei film, quali tabelle stanno in relazione ∞:∞? Quali sono le tabelle

intermedie che sono state create per risolvere la relazione? (scrivi così, per esempio PROF ∞ - CORSI 1 – CLASSI ∞).

Riassumendo...

CARDINALITA' ESEMPIO SPIEGAZIONI1:1 Preside-Scuola La chiave primaria di una delle due

tabelle (indifferentemente) diventa la chiave esterna dell'altra (o viceversa)

1:∞ Classi-Alunni La chiave primaria della tabella 1 diventa la chiave esterna nella tabella ∞

∞:∞ Prof-Classi Bisogna creare una tabella intermedia di collegamento, che contiene due chiavi esterne collegate alle due chiavi primarie

SE NON TI È CHIARO

Se non ti è tutto chiaro, non ti disperare! Si tratta di argomenti difficili, che devono essere digeriti lentamente.

Assicurati solo di aver prestato attenzione a tutte le domande e di aver risposto nel modo migliore possibile. Non svolgere questa parte in modo affrettato o approssimativo (anche se sei in modalità rapida, prova a rispondere a tutte le domande).

Se hai bisogno di aiuto, chiedi al prof!

F) PERCHÈ EXCEL NON È UN BUON DBMS

In questi nostri primi esempi abbiamo usato Excel per costruire un DB. Tuttavia Excel è un programma per la gestione di Fogli Elettronici e non è adatto alla gestione di un DB minimamente complesso.

Vediamo rapidamente alcuni dei motivi con riferimento al DB della nostra Biblioteca.

1) ERRORI DI INSERIMENTO

Considera la situazione seguente. Il bibliotecario deve inserire i dati relativi a un nuovo prestito nella tabella Prestiti. A tale scopo deve compilare i campi Collocazione con la collocazione del libro e Tessera col numero di tessera dell'utente. Tuttavia nella fretta si sbaglia a scrivere per esempio il numero di Tessera (scambiando una lettera con un'altra).

Il programma Excel (che non è un DBMS) non controlla che siano verificati i vincoli di integrità, cioè che la chiave esterna (nella tabella Prestiti) assuma valori corretti rispetto alla chiave primaria (nella tabella Utenti).

F1) Che conseguenze possono esserci? Supponi per esempio che l'utente non restituisca

il libro entro i termini prescritti.

2) CAMPI LASCIATI VUOTI

Supponi ora che il bibliotecario si dimentichi di inserire la data di prestito di un libro.

F2) Excel effettua qualche controllo sulla presenza di un valore in un campo? Quali conseguenze potrebbero esserci?

3) VALORI NON TIPIZZATI

Le celle di Excel possono contenere qualsiasi tipo di valore, numerico, letterale o altro e non viene fatto nessun controllo sul tipo dei valori inseriti.

F3) In quali modi diversi il bibliotecario potrebbe compilare il campo Data Prestito di un libro? Perché questo potrebbe comportare dei problemi?

4) EFFETTUARE RICERCHE

I DB servono tipicamente per effettuare ricerche e ricavare di conseguenza i dati. Per esempio potrei voler cercare tutti i libri presi in prestito da un dato utente in un certo intervallo di tempo (es, in un mese o in un anno). Oppure potrei essere interessato a sapere quante volte è stato dato in prestito un certo libro.

F4) Quali difficoltà ci sono in questo caso?

5) PROTEZIONE DEL DB

Excel permette di proteggere un foglio di lavoro (o parti di esso) da eventuali modifiche, inserendo una password di protezione. Tuttavia questa protezione è del tutto inadeguata per un DB condiviso in rete (per esempio contenente le credenziali di accesso a un sito).

F5) Spiegami che cosa non si può proteggere con Excel:

6) CONDIVISIONE DEL DB

Nelle applicazioni web il DB viene condiviso fra più utenti contemporaneamente. Pensa ad esempio a un sito di prenotazioni di posti al cinema (o in aereo).

F6) Cosa può succedere se due persone cercano di prenotare contemporaneamente lo stesso posto?

F7) Quali meccanismi devono essere implementati su un DB per assicurarsi che sia possibile condividerlo fra più utenti, mantenendo intatta la sua integrità?

BONUS DOMANDA D'ESAME +1 PC – EL3-2_5ST – DOMANDA1

Rispondere alla seguente domanda sul foglio a righe in 8-12 righe (http://classiperlo.altervista.org/File%20comuni/Foglio%20righe.docx). Leggi prima le modalità di compilazione su (http://classiperlo.altervista.org/Materiale/Generale/Simboli.doc).

DOMANDA: Spiegami come funziona il meccanismo di relazione chiave primaria-chiave esterna in un DB e quali sono gli scopi.

Invia il bonus separatamente su Classiperlo in un file Word di nome EL3-2_5ST – DOMANDA1.

Salva tutte le risposte alle domande in una cartella a parte, in modo da poterle facilmente ritrovare a fine anno (servono per la preparazione all'esame orale di maturità).

G) OPERAZIONI FINALI

G1) Controlla di aver risposto a tutte le domande e incollato tutte le schermate. Tutte le

caselline dovrebbero avere un segno X, per indicare che hai risposto

G2) Comprimi le immagini contenute in questo file Word (seleziona un'immagine, scheda Formato e poi Comprimi immagini e infine Applica a tutte le immagini del documento) in modo da ridurne le dimensioni.

G3) Controlla che la cartella di questa esercitazione contenga i seguenti file con i nomi qui indicati:

Nome del file Tipo del file DescrizioneEL3-2_5AST Database con Excel Word Il file di questa esercitazioneDB.xlsx Excel

G4) Chiudi tutti i file, zippa la cartella di questa esercitazione e inviala all'insegnante su Classiperlo.