Sviluppo App by piolo trafelli

2011

Lo sviluppo delle applicazioni informatiche Lâ&#x20AC;&#x2122;analisi dei dati , lâ&#x20AC;&#x2122;analisi delle funzioni, la scelta della infrastruttura hw e sw Il testo Ă¨ orientato al programma del V anno del corso di Informatica degli Istituti Tecnici Industriali. Per la progettazione dei dati vengono trattati il modello E/R per la rappresentazione concettuale, il modello relazionale per la rappresentazione logica ed il database relazionale per la memorizzazione fisica. Per la progettazione delle funzioni vengono illustrati il modello gerarchico funzionale e lo schema delle risorse di sistema.

Enrico Angeloni ITIS L. Trafelli - Nettuno 11/04/2011

Indice Modulo 1 - Il progetto delle applicazioni informatiche ................................................................................................. 7 1

Le fasi del progetto software............................................................................................................................... 8 1.1

Il progetto .................................................................................................................................................. 8

1.1.1 1.2

Il progetto informatico................................................................................................................................ 8

1.2.1 1.3

Il controllo di qualità del prodotto .......................................................................................................... 8

Prodotto del progetto informatico .......................................................................................................... 8 La metodologia di sviluppo di un progetto informatico................................................................................ 9

1.3.1

Definizione dei requisiti .........................................................................................................................10

1.3.2

Analisi ...................................................................................................................................................10

1.3.3

Progettazione di dettaglio .....................................................................................................................11

1.3.4

Transizione ............................................................................................................................................12

1.3.5

Realizzazione .........................................................................................................................................12

1.3.6

Documentazione ...................................................................................................................................13

1.3.7

Collaudo (Sytstem testing) .....................................................................................................................13

1.3.8

Formazione ...........................................................................................................................................14

1.3.9

Esercizio (o Produzione) ........................................................................................................................14

Il controllo di qualità del prodotto nei progetti informatici .................................................................................16 2.1

Definizione delle caratteristiche (qualità) del prodotto...............................................................................16

2.2

Definizione delle proprietà di ogni caratteristica ........................................................................................16

Modulo 2 - L’analisi dei dati ........................................................................................................................................18 1.

Il disegno (analisi) dei dati ..................................................................................................................................19 1.1

Tipi di archivi .............................................................................................................................................19

I modelli per la progettazione dei dati ................................................................................................................22 a)

Modello concettuale entità / associazioni (E/R) ..............................................................................................22

Modello logico...............................................................................................................................................22

Modello fisico – DBMS ...................................................................................................................................22

2.1

Modello E/R ..............................................................................................................................................22

2.1.1

Definizione ............................................................................................................................................22

2.1.2

Entità ....................................................................................................................................................22

2.1.3

Associazione..........................................................................................................................................22

2.1.4

Attributi ................................................................................................................................................24

2.1.5

Un particolare tipo di associazione: la gerarchia IS A (È UN) ...................................................................25

Modulo 3 - Il modello logico relazionale .....................................................................................................................27 1.

Gli oggetti del modello relazionale .....................................................................................................................28 2

1.1

Relazione...................................................................................................................................................28

1.2

Tabella ......................................................................................................................................................28

1.2.1

Definizione ............................................................................................................................................28

1.2.2

Rappresentazione..................................................................................................................................28

1.2.3

Chiave della tabella ...............................................................................................................................29

1.3

1.3.1

Requisiti fondamentali delle tabelle di un database relazionale..............................................................29

1.3.2

Accesso al singolo dato elementare in un database relazionale ..............................................................29

Passaggio dal Modello concettuale E/R al Modello logico relazionale.................................................................30 g)

Database relazionale .................................................................................................................................29

Associazione n:n ............................................................................................................................................30 La normalizzazione delle relazioni ......................................................................................................................33

3.1

Prima forma normale (1FN in inglese 1NF) .................................................................................................33

3.2

Seconda forma normale (2FN in inglese 2NF) .............................................................................................34

3.3

Terza forma normale (3FN in inglese 3NF)..................................................................................................34

Le operazioni relazionali.....................................................................................................................................35 4.1

Selezione ...................................................................................................................................................35

4.2

Proiezione .................................................................................................................................................35

4.3

Congiunzione (join)....................................................................................................................................35

4.3.1

Join naturale .........................................................................................................................................35

4.3.2

Equi-Join ...............................................................................................................................................35

4.3.3

Join interno ...........................................................................................................................................36

4.3.4

Join esterno (outer join) ........................................................................................................................36

4.3.5

Prodotto cartesiano...............................................................................................................................36

4.3.6

Theta join ..............................................................................................................................................36

Le operazioni insiemistiche ................................................................................................................................37 5.1

Unione ......................................................................................................................................................37

5.2

Intersezione ..............................................................................................................................................37

5.3

Differenza..................................................................................................................................................37

5.4

Utilizzo di operazioni relazionali ed insiemistiche nelle interrogazioni ........................................................37

Modulo 4 â&#x20AC;&#x201C; Il modello fisico: il linguaggio SQL.............................................................................................................38 1.

GeneralitĂ ..........................................................................................................................................................39 1.1

Storia di SQL ..............................................................................................................................................39

1.2

Sezioni del linguaggio SQL..........................................................................................................................39

DDL (Data Definition Language) â&#x20AC;&#x201C; Definizione della struttura delle tabelle ..........................................................40 2.1

Creazione struttura tabelle ........................................................................................................................40

2.1.1

Tipi di dati standard...............................................................................................................................40

2.1.2

Esempio di creazione tabelle .................................................................................................................40 3

2.2

Aggiornamento struttura tabelle...............................................................................................................41

2.3

Cancellazione tabella ................................................................................................................................41

DML (Data Manipulation Language) – Manipolazione dei dati delle tabelle.........................................................42 3.1

Inserimento di una riga ..............................................................................................................................42

3.2

Aggiornamento di una o più righe di una tabella ........................................................................................42

3.3

Aggiornamento di una o più righe di più tabelle .........................................................................................42

3.4

Cancellazione di una o più righe di una tabella ...........................................................................................42

QL(Query Language) – Interrogazione dei dati delle tabelle ................................................................................43 4.1

Formato base del comando SELECT............................................................................................................43

4.1.1

Clausole ALL e DISTINCT del comando SELECT ........................................................................................43

4.1.2

Intestazione delle colonne della tabella risultato ...................................................................................43

4.1.3

Inclusione di campi calcolati nella tabella risultato .................................................................................43

4.1.4

Parametrizzazione dei valori delle query in ACCESS................................................................................43

4.2

Implementazione in SQL delle operazioni dell’algebra relazionale ..............................................................44

4.2.1

Proiezione .............................................................................................................................................44

4.2.2

Selezione...............................................................................................................................................44

4.2.3

Congiunzione (Join) ...............................................................................................................................44

4.3

Funzioni di aggregazione............................................................................................................................44

4.3.1

Conteggio (COUNT) ...............................................................................................................................44

4.3.2

Conteggio dei valori distinti (COUNT DISTINCT) ......................................................................................45

4.3.3

Somma (SUM) .......................................................................................................................................45

4.3.4

Media dei valori (AVG)...........................................................................................................................45

4.3.5

Valore minimo (MIN) e valore massimo (MAX).......................................................................................45

4.4

Ordinamento del risultato (ORDER BY) .......................................................................................................45

4.5

Raggruppamento dei risultati per chiavi di ricerca (GROUP BY)...................................................................45

4.6

Condizioni sui raggruppamenti (HAVING) ...................................................................................................46

4.7

Formato generalizzato del comando SELECT ..............................................................................................46

4.7.1

Regole per la scrittura del comando SELECT ...........................................................................................47

4.8

Esempio di SQL con INNER JOIN a cascata ..................................................................................................47

4.9

Condizioni di ricerca ..................................................................................................................................47

4.9.1

Operatori di confronto ..........................................................................................................................47

4.9.2

Operatori logici .....................................................................................................................................47

4.9.3

BETWEEN ..............................................................................................................................................47

4.9.4

IN ..........................................................................................................................................................48

4.9.5

LIKE .......................................................................................................................................................48

4.9.6

IS NULL..................................................................................................................................................48

4.10

Interrogazioni nidificate .............................................................................................................................48 4

4.10.1

Sottoquery che restituiscono un solo valore ......................................................................................48

4.10.2

Sottoquery che restituiscono più di un valore ....................................................................................48

4.10.3

Assegnazione di un nome alla tabella derivata dalla sottoquery .........................................................51

4.11

Interrogazioni in cascata ............................................................................................................................51

4.12

Operazioni insiemistiche tra tabelle ...........................................................................................................51

4.12.1

Unione tra due tabelle (UNION).........................................................................................................52

4.12.2

Intersezione tra due tabelle (INTERSECT) ...........................................................................................52

4.12.3

Differenza tra due tabelle (MINUS) ....................................................................................................52

4.13

Funzioni SQL avanzate ...............................................................................................................................53

4.13.1

Funzioni carattere .............................................................................................................................53

4.13.2

Funzioni matematiche .......................................................................................................................54

4.13.3

Funzioni di data e ora ........................................................................................................................55

4.14

L’espressione CASE nel comando Select .....................................................................................................56

4.14.1

Espressione CASE semplice ................................................................................................................56

4.14.2

Espressione CASE cercata ..................................................................................................................56

Modulo 5 – Le caratteristiche dell’RDMS Relational DataBase Management System ...................................................58 1.

Generalità..........................................................................................................................................................59 1.1

Definizione di Data Base (DB) .....................................................................................................................59

1.2

Definizione di Data Base Management System (DBMS) ..............................................................................59

1.3

Schema di funzionamento di un rdbms ......................................................................................................59

Caratteristiche generali di un rdbms...................................................................................................................60 2.1

Facilità e velocità di accesso.......................................................................................................................60

2.2

Indipendenza dalla struttura logica dei dati ................................................................................................60

2.3

Indipendenza dalla struttura fisica dei dati .................................................................................................60

2.4

Eliminazione della ridondanza....................................................................................................................60

2.5

Integrità dei dati ........................................................................................................................................60

2.6

Integrità logica...........................................................................................................................................60

2.6.1

trigger ...................................................................................................................................................61

2.7

Integrità fisica............................................................................................................................................62

2.8

Controllo della concorrenza degli accessi ...................................................................................................62

2.9

Sicurezza dei dati .......................................................................................................................................62

2.10

Riservatezza dei dati ..................................................................................................................................62

2.11

Protezione contro la perdita accidentale dei dati (persistenza dei dati) ......................................................65

Modulo 6 – L’analisi delle funzioni ..............................................................................................................................66 1.

Individuare le funzioni: il modello gerarchico-funzionale ....................................................................................67 1.1

Scomposizione gerarchica funzionale .........................................................................................................67

1.2

Regole per la definizione del FHD...............................................................................................................67 5

1.3

Esempi FHD ...............................................................................................................................................67

2.2.1

FHD Applicazione Vendita Libri On-Line .................................................................................................68

2.2.2

FHD Prestazioni Servizio Sanitario Nazionale ..........................................................................................68

Schema delle risorse di sistema ..........................................................................................................................69 2.1

Esempio 1: scarico degli articoli dal magazzino...........................................................................................69

2.2

Esempio 2: prelievo bancomat ...................................................................................................................69

2.3

Esempio 3: prenotazione volo................................................................................................................69

2.4

Esempio 4: composizione ordini di un utente registrato .........................................................................70

2.5

Esempio 5: prestiti di una biblioteca ......................................................................................................70

2.6

Esempio 6: Vendita libri on-line - Visualizzazione catalogo libri ..............................................................71

2.7

Esempio 7: Vendita libri on-line â&#x20AC;&#x201C; Ordini di un utente ............................................................................72

Modulo 1 - Il progetto delle applicazioni informatiche UnitĂ Didattiche 1. Le fasi del progetto software 2. Il controllo di qualitĂ del prodotto nei progetti informatici

LE FASI DEL PROGETTO SOFTWARE

1.1 Il progetto La realizzazione di un qualsiasi prodotto, quali un’applicazione informatica gestionale, un edificio, un centro commerciale, un modello di autovettura, costituisce un PROGETTO. Un progetto è quindi un insieme complesso di attività tese a ottenere un risultato chiamato prodotto (l’applicazione informatica, l’edificio, l’autovettura). Le attività di un progetto si possono classificare in: Attività di studio Studia l’area di competenza del progetto per conoscere in modo approfondito tutti gli aspetti coinvolti. Attività di progettazione Definisce le caratteristiche del prodotto, descrivendolo tramite disegni, modelli, prototipi. Attività di realizzazione Realizza il prodotto Attività di esercizio Il prodotto viene rilasciato ed il progetto di realizzazione termina. 1.1.1 Il controllo di qualità del prodotto Un aspetto fondamentale di tutto il progetto è che il prodotto consegnato sia un prodotto di qualità. La qualità di un prodotto misura il grado di aderenza dei risultati rispetto alle necessità del cliente. Per verificare la qualità di un prodotto è necessario determinare quali sono le caratteristiche importanti che il prodotto debba possedere (chiamate qualità o capacità, in inglese capability); ciascuna capacità viene poi scomposta in una o più caratteristiche di dettaglio (chiamate proprietà, in inglese property) e sono queste ad essere oggetto di misurazione in modo diretto ed oggettivo. Quando la misurazione di tutte le proprietà di una qualità soddisfa i requisiti stabiliti, allora quella qualità si considera verificata. Il procedimento per il controllo di qualità di un prodotto è quindi il seguente:  si determinano le qualità e le relative proprietà che si possono misurare e verificare  per ogni proprietà si identifica l’unità di misura che consente di eseguire la misurazione della proprietà stessa  si definiscono i valori minimo e massimo entro i quali la qualità è soddisfatta  si eseguono le misurazioni che, se contenute nell’intervallo previsto, certificano la qualità del prodotto. 1.2 Il progetto informatico Il progetto informatico è un progetto che ha come obiettivo la realizzazione di un’applicazione informatica. Nel seguito si fa riferimento a un particolare sottoinsieme delle applicazioni informatiche, quello delle applicazioni gestionali, che riguardano cioè la gestione di informazioni (nelle aziende, negli studi professionali o negli enti pubblici). Esempi di applicazioni gestionali sono:  studio dentistico  contabilità aziendale  scuola  prestiti di una biblioteca ad alunni e studenti  utenti di un acquedotto e relativi consumi  abbonati alle riviste di una casa editrice  gestione dei conti correnti di una banca  sito di e-commerce 1.2.1 Prodotto del progetto informatico Il prodotto del progetto è ovviamente l’applicazione informatica stessa che può essere vista come l’insieme degli elementi seguenti:

a) Le informazioni da gestire Le informazioni sono raccolte in quelli che vengono comunemente chiamati archivi dei dati.

b) Le funzioni necessarie In particolare le funzioni possono essere di due tipi:  creazione, aggiornamento e cancellazione delle informazioni  ricerca e messa a disposizione delle informazioni

c) I flussi dei dati 8

Specificano come i dati sono utilizzati dalle funzioni, distinguendo in particolare tra dati di input e di output di ogni funzione.

d) Le interfaccia dell’utente finale con il sistema   

le maschere per la manipolazione dei dati (data entry screen) le maschere di visualizzazione sul monitor le stampe (report) o elenchi su carta normale (piano dei conti, listino prezzi, elenco alunni) o prestampati (bollette telefoniche, modelli fiscali)

e) Le infrastrutture hw e sw necessarie   

macchine (computer, stampanti, etc.) sistemi operativi e software di base (web server, DBMS server, application server) componenti di rete

1.3 La metodologia di sviluppo di un progetto informatico La metodologia è un approccio strutturato per l’esecuzione dell’intera attività di sviluppo dei progetti e dei relativi prodotti, coerentemente con gli obiettivi e le necessità da soddisfare. 1 Una delle metodologie più seguite è quella chiamata metodologia a cascata (in inglese waterfall) che prevede di suddividere il progetto in una serie di attività, chiamate passi o fasi, che vengono eseguite in sequenza in quanto ogni fase necessita dei risultati della fase precedente2. Le fasi di un progetto di sviluppo di un’applicazione informatica gestionale con la metodologia a cascata sono le seguenti: Definizione dei requisiti

Analisi

Transizione

Progettazione di dettaglio

Realizzazione

Documentazione

Collaudo

Formazione

Esercizio

Ogni fase è costituita da:  le attività proprie della fase, individuate dal nome della fase stessa  i risultati da raggiungere che costituiscono il prodotto della fase

Altre metodologie sono la metodologia a spirale (che prevede la riesecuzione o il riciclo delle fasi con l’intento di raffinare, in modo crescente, i risultati di ogni fase) e l’approccio prototipale (che prevede la realizzazione in tempi brevi di un prototipo dell’applicazione che viene poi progressivamente ampliato e migliorato fino ad arrivare alla soluzione finale. 2 Come si vede dalla figura, ci sono alcune fasi (progettazione e transizione come anche documentazione, collaudo e formazione) che possono essere eseguite in parallelo, poiché non necessitano l’una dei risultati dell’altra. 9



i controlli da eseguire per verificare l’andamento della fase (ad es. il controllo del rispetto dei tempi di avanzamento previsti). E’ inoltre importante stabilire per ogni fase:  la sua durata temporale, come somma dei tempi di esecuzione delle attività che la compongono  il suo costo in termini di risorse necessarie per il suo svolgimento (persone, mezzi hw e sw, viaggi etc.) Dalla durata delle fasi e dalla loro disposizione (in sequenza o in parallelo) si stabilisce la durata dell’intero progetto mentre sommando i costi di tutte le fasi si ottiene la stima dei costi del progetto. 1.3.1 Definizione dei requisiti Questa fase è anche chiamata analisi preliminare. A. Obiettivi  Definire che cosa si deve sviluppare, cioè che cosa l’applicazione informatica deve fare  Conoscere l’ambiente esistente nel quale si deve integrare la nuova applicazione 3 o in particolare conoscere gli archivi informatizzati già presenti per evitare le duplicazioni dei dati e per definire come integrare i dati esistenti nella nuova applicazione  Evidenziare gli eventuali vincoli da tenere presente, come ad es. l’architettura hw e sw in cui l’applicazione deve andare ad operare.  Stabilire i parametri per il controllo di qualità del prodotto (ved. successivo punto 2.4)  Definire le specifiche di sicurezza per la protezione dei dati da accessi non autorizzati (ad es. accedere alle funzioni dell’applicazione tramite un sistema di password)  Definire le specifiche di riservatezza per la protezione dei dati sensibili (ad es. memorizzare i dati sensibili in forma crittografata)  Definizione delle specifiche di ergonomia ed usabilità del sw per favorire il lavoro degli utenti finali (ad es. i colori da evitare sulle maschere per gli utenti daltonici). B. Attività  Eseguire le interviste conoscitive. Scopo delle interviste è conoscere, comprendere e documentare i processi (attività) che si svolgono nell’area che deve essere automatizzata. Gli interlocutori delle interviste sono le persone esperte che svolgono le attività che interessano l’area del progetto. Le interviste si distinguono in: o interviste aperte, basate su un colloquio libero, senza uno schema prefissato o interviste chiuse, basate su questionari contenenti domande specifiche Normalmente è più efficace iniziare un progetto con le interviste aperte alle persone che hanno un’ampia visione della materia. Ciò consente di delimitare i confini del progetto, cioè di conoscere che cosa si deve progettare, ottenendo l’elenco dei processi da automatizzare; l’analisi di dettaglio di ogni processo può essere eseguita con il supporto di appositi questionari. Oltre che dalle interviste, la conoscenza dei processi può essere ricavata dai documenti e i testi esistenti che vanno quindi raccolti e studiati. C. Prodotti  Specifiche dei requisiti dell’applicazione da realizzare, con indicazione di tutto ciò che l’applicazione deve fare. In particolare devono essere indicate: o le funzioni principali che andranno sviluppate o i dati di maggiore importanza che devono essere gestiti. Un esempio di specifiche è costituito dai testi che vengono proposti per la prova di Informatica agli esami di Stato.  Piano di progettazione dell’applicazione con i tempi di consegna, le risorse ed i costi necessari 1.3.2 Analisi Questa fase è anche chiamata progettazione architetturale. A. Obiettivi  Determinare e descrivere tutte le componenti dell’applicazione, in particolare: 3

La presenza dei dati duplicati pone dei gravi problemi, quali: la maggiore occupazione di memoria (la stessa informazione viene memorizzata più volte in punti diversi); il maggior tempo di esecuzione dei programmi di aggiornamento (lo stesso aggiornamento deve essere ripetuto su tutti gli archivi in cui l’informazione è presente), ma soprattutto il possibile disallineamento delle informazioni (l’informazione viene aggiornata solo su alcuni archivi e quindi, quando la si ricerca, si ottengono valori differenti e non si capisce più quale sia quello giusto). 10

o o o

Dati Funzioni Flusso dei dati

B. Attività  Esame delle specifiche dei requisiti per l’individuazione e la descrizione degli oggetti dell’applicazione. C. Prodotti Documento di analisi, contenente:  la documentazione dei dati, cioè delle informazioni che l’applicazione deve gestire.  la documentazione delle funzioni, cioè delle funzionalità richieste all’applicazione.  la documentazione del flusso dei dati, cioè delle modalità di ingresso (input) e di uscita (output) dei dati dalle funzioni. Un tipo di strumenti che è possibile utilizzare per l’individuazione delle funzioni e per la descrizione del flusso di dati è riportato nella dispensa “1 – Analisi delle funzioni”. 1.3.3 Progettazione di dettaglio A. Obiettivi  Definire e descrivere in modo dettagliato le caratteristiche dell’applicazione da realizzare. B. Attività  Sulla base del documento di analisi, arrivare a una definizione dettagliata di tutti gli oggetti che devono essere realizzati e di tutte le procedure che devono essere adottate per garantire la sicurezza della applicazione.  Presentazione e discussione con il committente dei prodotti della progettazione di dettaglio. Questa attività è molto importante perché rappresenta l’ultima opportunità di modificare le scelte di progetto senza costi troppo elevati. Infatti ogni variazione delle specifiche di progetto decisa nella fase di realizzazione ha un costo di gran lunga maggiore in quanto comporta anche la revisione di quanto realizzato fino a quel momento (programmi e archivi). C. Prodotti  Specifiche per la creazione degli archivi elettronici, comunemente strutturati con la tecnologia dei database. Un modo di procedere per la progettazione dei dati, valida nel caso si utilizzino i database come struttura di archivi, è riportata nella dispensa “2 – Analisi dei dati”. In essa si considera il processo di progettazione dei dati e dei relativi archivi come una fase unica, senza fare distinzione tra fase di analisi e fase di progettazione di dettaglio.  Specifiche per lo sviluppo dei moduli software dell’applicazione. Partendo dalle funzioni descritte nella precedente fase di analisi, si determinano i moduli software (programmi) che devono realizzare l’insieme delle funzioni dell’applicazione. Un singolo modulo può soddisfare anche più di una funzione, come di solito accade nel caso di funzioni che utilizzano gli stessi dati. Ogni modulo è descritto con l’elaborazione che deve svolgere e deve, inoltre, essere documentato con il linguaggio di programmazione da utilizzare e gli archivi elettronici da elaborare.  Formato delle interfacce tra l’utilizzatore e l’applicazione4: menu, maschere, report5. E’ importante definire gli standard d’interfaccia che facilitino il lavoro dell’utente-operatore, quali: o barre sempre nella stessa posizione (ad es. la barra dei menu) o campi che si chiamano sempre nello stesso modo (ad es. alunno, studente, studioso) o maschere sempre dello stesso colore o logo sempre lo stesso  Controlli da implementare per garantire la riservatezza (privacy) del sistema, in particolare: o protezione dei moduli sw dell’applicazione con delle password di accesso o protezione ed autorizzazione di accesso agli archivi (ad es. l’archivio elettronico degli stipendi deve essere protetto e consultabile solo dagli addetti ai lavori per il calcolo delle retribuzioni).  Misure di protezione da adottare contro il rischio di perdita accidentale dei dati. In particolare due sono 6 gli accorgimenti che vengono comunemente presi : 4

L’insieme degli aspetti riguardanti l’interazione unomo-macchina viene chiamato ergonomia informatica. Il report è una situazione (su carta o su schermo) prodotta (output) da un modulo software. 6 L’insieme delle avvertenze, atte a garantire il sistema, sono denominate in inglese disaster recovery. 11 5

Copiare periodicamente gli archivi su supporti diversi da quelli originali (ad es. su dischi removibili o su nastri), conservando le copie in luoghi diversi da quelli dove risiedono i dati in linea e protetti contro i rischi d’incendio (ad es. si può utilizzare un armadio ignifugo) e di allagamento. o Prevedere la memorizzazione, in ordine cronologico, degli aggiornamenti intervenuti tra una copia e la successiva al fine di evitare di perdere tali aggiornamenti nel malaugurato che gli archivi in linea vengano persi (ad es. per uno scratch del disco) e debbano essere ripristinati dalla copia. Il file su cui vengono registrati tali aggiornamenti viene chiamato log o journal. Le specifiche devono indicare esattamente sia le procedure di copia degli archivi e di memorizzazione degli aggiornamenti, sia quelle di ripristino, in modo da garantire l’allineamento di tutti gli archivi dell’applicazione. 1.3.4 Transizione A. Obiettivi  Definire le modalità di passaggio dalla vecchia alla nuova applicazione. Gli obiettivi principali da raggiungere sono tre: e riguardano: o La conversione del sistema esistente nel nuovo sistema (migrazione) o La chiusura del sistema esistente nel momento in cui il nuovo sistema è pronto per entrare in produzione, cioè quando tutte le funzioni necessarie sono state realizzate e gli archivi popolati con i dati iniziali o L’attivazione del nuovo sistema (start-up). B. Attività Definizione del piano di Pianificare tutti gli aspetti necessari per la partenza della nuova applicazione. In particolare rilascio devono essere: - individuate le attività e la sequenza in cui devono essere svolte - stabilite le risorse necessarie per lo svolgimento di tali attività - previsti i tempi di svolgimento di ciascuna attività. Definizione del piano di La formazione comprende: formazione - i corsi di formazione da tenere agli utenti finali - i corsi di formazione per il personale tecnico-operativo del CED (Centro Elaborazione dati) - Il manuale che indica come eseguire le funzioni del sistema Definizione del piano di Con il termine di migrazione dei dati s’intende il trasferimento nei nuovi archivi dei dati Migrazione dei dati necessari per la partenza della nuova applicazione. Tali dati possono derivare: - dai dati presenti in formato elettronico nelle vecchie applicazioni che vengono sostituite dal nuovo sistema (nel caso in cui la nuova applicazione ne sostituisce altre preesistenti). Per migrare questi dati nei nuovi archivi devono essere previsti e realizzati opportuni programmi di migrazione; - dai dati memorizzati nei supporti cartacei che vanno inseriti nei nuovi archivi tramite funzioni di acquisizione realizzate ad hoc. Il piano di migrazione dei dati deve stabilire quali funzioni realizzare per eseguire il trasferimento dei dati nei nuovi archivi e pianificare le attività la sequenza delle attività necessarie per l’esecuzione di tali funzioni. Definizione del piano di Oltre alle funzioni e agli archivi, la nuova applicazione ha bisogno della infrastruttura installazione hardware e software per essere messa in marcia. Fanno parte di questa infrastruttura: - le macchine (server, client, stampanti etc.) - l’architettura di rete (LAN, VPN etc.) - i sistemi operativi - il server Web, il DataBase server, l’Application server Il piano di installazione deve prevedere la sequenza di attività necessarie per mettere in piedi l’infrastruttura appena descritta ed individuare le persone che a qualunque titolo devono attivarsi per contribuire all’installazione. C. Prodotti I prodotti della fase di Transizione sono i piani descritti in precedenza. La massima attenzione deve essere dedicata alla determinazione delle date in cui inizieranno e termineranno i piani. 1.3.5 Realizzazione A. Obiettivi  Realizzazione degli oggetti del progetto che, trattandosi di un progetto informatico, sono costituiti dai moduli software (chiamati comunemente programmi) e dagli archivi utilizzati dai programmi (quasi 12



sempre le tabelle di un database relazionale). I moduli software sono sia quelli che costituiscono l’applicazione e che sono stati definiti nelle fasi di analisi e progettazione, sia quelli da utilizzare una tantum per la fase di transizione (i moduli per la conversione degli archivi e quelli per l’immissione manuale dei dati, in inglese data entry7). Prova dei moduli sviluppati, per verificare la loro rispondenza alle specifiche. Ogni modulo deve essere verificato dalla stessa persona che lo ha realizzato (unit test), utilizzando pochi dati appositamente creati per simulare tutte le casistiche necessarie per provare il funzionamento di tutte le parti del modulo. Non basta verificare i risultati che il modulo produce quando lavora con i dati corretti, ma anche come si comporta in caso di dati errati.

B. Attività  Realizzazione dei moduli software e degli archivi. È ipotizzabile un piano di realizzazione parallelo su due direttici: i moduli dell’applicazione ed i moduli per la transizione. Ciascuna di queste due attività può poi essere parallelizzata in tante sottoattività in base al numero delle persone addette allo sviluppo.  Prova dei moduli realizzati con archivi di test appositamente predisposti. C. Prodotti  Moduli software dell’applicazione e della transizione testati.  Archivi dei dati. In caso si utilizzino i database relazionali, deve essere preparato lo schema fisico del database con tutti gli oggetti previsti dall’applicazione (tabelle, viste, trigger, database procedure, utenti, privilegi). 1.3.6 Documentazione A. Obiettivi  Fornire a tutti gli utenti del nuovo sistema, il materiale documentativo da consultare nel corso della loro attività lavorativa. B. Attività  Sulla base del documento di analisi, arrivare a una definizione dettagliata di tutti gli oggetti che devono essere realizzati e di tutte le procedure che devono essere adottate per garantire la sicurezza della applicazione.  Presentazione e discussione con il committente dei prodotti della progettazione di dettaglio. Questa attività è molto importante perché rappresenta l’ultima opportunità di modificare le scelte di progetto senza costi troppo elevati. Infatti ogni variazione delle specifiche di progetto decisa nella fase di realizzazione ha un costo di gran lunga maggiore in quanto comporta anche la revisione di quanto realizzato fino a quel momento (programmi e archivi). C. Prodotti  Manuale utente. Descrive completamente tutte le funzionalità della nuova applicazione con un opportuno indice. L’operatore che volesse eseguire una funzione del proprio lavoro, troverà facilmente quale sia il modulo software che deve attivare.  Documentazione interattiva incorporata nelle interfacce video (help-on-line). Questo manuale elettronico in linea fornisce il significato dei campi presenti nell’interfaccia utilizzando le definizioni registrate in fase di analisi. La documentazione funziona a richiesta: quando l’utente chiede di vedere la descrizione del campo su cui è posizionato, premendo il tasto di help (tipicamente il tasto F1), appare a video una finestra con la descrizione del dato. Questo secondo tipo di manuale è focalizzato sui dati, al contrario del manuale per l’utente che è focalizzato sulle funzioni.  Manuale operativo. Si rivolge ai gestori del sistema e riguarda le modalità di esecuzione di attività di amministrazione del sistema, quali: o Le elaborazioni da attivare senza l’intervento degli utenti (batch processing) o L’archiviazione (backup) degli archivi e la conservazione delle loro copie o Il ripristino (restore) degli archivi o L’invio degli elaborati cartacei ai vari destinatari 1.3.7 Collaudo (Sytstem testing) D. Obiettivi

Per il data entry conviene utilizzare per quanto possibile i programmi di immissione dei dati previsti nell’applicazione, senza necessità di svilupparne altri. 13



Verificare il grado di aderenza del progetto alle necessità dell’utente-commitente e di rimuovere eventuali errori presenti nel codice software, con lo scopo fondamentale di trovare il massimo numero di errori presenti nei prodotti da rilasciare, prima della loro consegna agli utenti finali. Al collaudo partecipa un gruppo di rappresentanti dell’utente in rappresentanza di tutti i settori dell’organizzazione interessata dal progetto. Ad es., per un sistema informatico amministrativo partecipano al collaudo i rappresentanti degli uffici Acquisti, Vendite, Contabilità, Magazzino etc.

Attività  Preparazione di un ambiente di prova che simuli il più esattamente possibile l’ambiente di produzione del progetto. Il sistema di prova deve essere nettamente distinto da quello effettivo in quanto durante le prove si introducono dati inventati, si possono alterare o perdere i dati esistenti a causa di errori nei programmi etc. Nell’ambiente di prova vengono copiati gli archivi di produzione ed installate le versioni dei programmi già testati nella fase di produzione. Per le prove può essere necessario predisporre appositi programmi per la verifica dei degli archivi elettronici (per es. programmi che calcolano i totali e producono dati statistici sulla popolazione degli archivi; programmi che verificano la correttezza dei dati introdotti etc.). Deve inoltre essere disponibile il manuale utente sia per supportare gli utenti nelle prove sia per verificarne la correttezza e completezza.  Pianificazione dell’insieme delle prove in modo da non tralasciare nessuna funzione implementata.  Conduzione delle prove da parte degli utilizzatori finali, con il supporto del personale tecnico che ha realizzato l’applicazione. Le prove consistono nel considerare ogni oggetto da collaudare come una scatola nera (black-box) di cui non si conosce la struttura interna ma soltanto le sue funzionalità: in presenza di determinati input deve produrre determinati dati di output. Prodotti  Verbali di collaudo. I verbali riportano le prove che sono state eseguite e i risultati delle stesse. Nel caso si riscontrino malfunzionamenti nei programmi e negli archivi (in inglese bug, cioè bachi), questi vanno evidenziati per poter essere corretti e va pianificata la loro prova a valle delle correzioni e dell’eventuale ripristino degli archivi se risultassero danneggiati dai malfunzionamenti. Si fa notare che il numero di errori riscontrati in fase di collaudo può essere assunto come uno degli indici di qualità del software e deve quindi essere inferiore ad un valore prefissato.  Certificazione del progetto. Una volta terminate, con esito positivo, tutte le prove pianificate, viene redatto ed approvato il documento che certifica l’accettazione del progetto da parte dell’utente.

1.3.8 Formazione G. Obiettivi  Mettere in condizione tutti gli utenti del nuovo sistema di operare conoscendone le caratteristiche. H. Attività  Pianificazione dei corsi da tenere. I corsi potrebbero essere tenuti una tantum prima dell’avvio in produzione del nuovo sistema e poi, a richiesta, per la formazione di nuovi assunti. È opportuno distinguere diversi tipi di corso in base ai diversi ruoli che gli utenti rivestono. Ad es., un corso per gli addetti alle vendite, uno per i magazzinieri, uno per i contabili etc.  Preparazione del materiale didattico necessario per il corso: testi, manuali, presentazioni, esercizi. È anche possibile prevedere di tenere una parte dei corsi in modalità di e-learning. Per l’addestramento pratico degli utenti sul sistema può essere utilizzato l’ambiente di prova utilizzato per il collaudo.  Erogazione dei corsi secondo il piano che è stato preparato. I. Prodotti  Piano dei corsi con indicazione di date, luoghi, strumenti utilizzati, risorse da impiegare per l’erogazione, utenti partecipanti. 1.3.9 Esercizio (o Produzione) J. Obiettivi  Attivare il progetto presso l’organizzazione utente. Il rilascio in esercizio (in inglese start-up) deve essere accompagnato da una fase detta di follow-up in cui viene controllato che i processi elaborativi producano i risultati attesi, viene verificato il corretto funzionamento dell’intero sistema rimuovendo eventuali situazioni anomale che dovessero verificarsi, vengono fornite attività di consulenza su casi pratici. K. Attività 14



 L.

Pianificare l’istallazione del nuovo sistema, concordando le date con gli utenti in base alle loro esigenze lavorative. In particolare deve essere tenuto conto del piano predisposto nella fase di transizione, fissando le date di inizio e fine di ogni attività in modo da garantire il rispetto della data prevista per l’attivazione. Eseguire le operazioni di transizione, controllando la corretta esecuzione delle stesse ed il rispetto dei tempi.

Prodotti  Certificazione da parte dell’utente che il sistema è stato rilasciato.

IL CONTROLLO DI QUALITÀ DEL PRODOTTO NEI PROGETTI INFORMATICI

2.1 Definizione delle caratteristiche (qualità) del prodotto Le caratteristiche principali di un prodotto informatico sono enunciate nella norma ISO 9126 8, riguardante i fattori di qualità del software e la loro misurazione: Caratteristica Descrizione Funzionalità Il prodotto sw fa esattamente quello che è stato stabilito nei requisiti prefissati Affidabilità I programmi gestiscono tutte le situazioni particolari e non si bloccano per motivi banali Usabilità L’utilizzo è semplice per tutti i tipi di utente Efficienza Il sw è veloce e usa in modo ottimale le risorse disponibili Manutenibilità La documentazione è adeguata e consente di intervenire rapidamente con modifiche ai programmi Portabilità È possibile trasferire il sw su altre piattaforme hw e/o su altri sistemi operativi 2.2 Definizione delle proprietà di ogni caratteristica Ognuna delle caratteristiche precedenti è ulteriormente dettagliata in sottocaratteristiche che specificano le proprietà che il prodotto sw deve possedere: Caratteristica Proprietà Descrizione Funzionalità Adeguatezza Presenza di funzioni appropriate per compiti specifici Accuratezza Fornitura di risultati giusti o concordati Interoperabilità Capacità di interagire con altri sistemi Aderenza Conformità a norme, leggi, regolamenti, standard o altre prescrizioni Sicurezza Capacità di evitare accessi non autorizzati a programmi o dati Affidabilità Maturità Frequenza dei malfunzionamenti (fine anomale dei processi) Tolleranza ai guasti Capacità di mantenere livelli di prestazioni predeterminati in caso di fallimenti Recuperabilità Capacità di ripristinare livelli di prestazione predeterminati e di recuperare i dati, a seguito di fallimenti e misura del tempo e dell’impegno richiesto per completare tali azioni Usabilità Comprensibilità Impegno richiesto agli utenti per comprendere il funzionamento del prodotto Apprendimento Impegno richiesto agli utenti per imparare ad usare il prodotto Operabilità Impegno richiesto agli utenti per utilizzare il prodotto Efficienza Velocità Tempi di elaborazione e di risposta necessari per eseguire le funzioni richieste Risorse utilizzate Quantità di risorse utilizzate e tempi del loro utilizzo, per eseguire le funzioni richieste Manutenibilità Analizzabilità Impegno richiesto per diagnosticare carenze o cause di malfunzionamenti o per identificare parti da modificare Modificabilità Impegno richiesto per modificare, rimuovere errori o sostituire elementi Stabilità Rischio di comportamenti inaspettati a seguito di cambiamenti apportati Verificabilità Impegno richiesto per validare le modifiche apportate al prodotto Portabilità Adattabilità Capacità del prodotto di adattarsi a nuovi ambienti operativi (ad es. da Windows a Linux), utilizzando le sole azioni disponibili nel prodotto stesso per eseguire quest’operazione Installabilità Impegno richiesto per installare il prodotto in un altro ambiente Conformità Aderenza a standard degli strumenti utilizzati (ad es. utilizzo di strumenti open source) Sostituibilità Possibilità ed impegno richiesto per usare un componente sw al posto di un altro (ad es. un RDBMS Oracle al posto di MySql)

L’ISO (International Organization for Standardization) è un organismo internazionale che si occupa della definizione di norme standard internazionali. In particolare, il documento di riferimento per la qualità del software è la norma ISO/IEC 9126 (Information Technology – Software Product evaluation – Quality characteristics and giudelines in their use”.

Di seguito viene riportato un esempio di misurazione della qualità di un prodotto informatico, con gli indicatori utilizzati per ogni proprietà, le unità di misura ed i valori ammessi. Caratteristica Proprietà Indicatore Descrizione Unità misura Valori ammessi Funzionalità Adeguatezza Tasso di programmi Programmi consegnati entro il Numero 99 % consegnati nei tempi termine stabilito di fine progetto, previsti rispetto al totale dei programmi da realizzare Rilievi sui documenti Rilievi segnalati su Specifiche Numero Max. 1 per ogni di progetto Funzionali, Manuale Utente, 10 pagine di Manuale di gestione, Piano dei documento test Recidività dei rilievi Rilievi recidivi su uno stesso Numero 0 sullo stesso documento documento Aderenza Rispetto degli Rilievi riguardanti il rispetto degli Numero 0 standard standard Affidabilità Maturità Difettosità in Difetti emersi in fase di collaudo Numero <=5 ogni 100 collaudo programmi collaudati Capacità di controllo Interventi di ripristino in collaudo Numero Max. 1 dei difetti del codice sullo stesso modulo per lo stesso in collaudo malfunzionamento Tempestività di Tempo entro cui ripristinare l’uso Giorni Max. 3 gg. ripristino durante il di un programma afflitto da lavorativi collaudo malfunzionamenti Usabilità Operabilità Frequenza N° di volte in cui uno stesso dato Numero 1 d’immissione di un di input deve essere fornito dato dall’utente Apprendimento Giorni di Intervallo di tempo in cui viene Giorni Min. 2 addestramento completato l’addestramento Max. 5 degli utenti Efficienza Velocità Durata elaborazioni Tempo che intercorre tra la Secondi Max. 5 sec. interattive richiesta e la risposta (response time) Durata elaborazioni Tempo che intercorre tra la Secondi Max. 3600 sec. batch richiesta e la risposta (response time) Risorse Ram Memoria centrale richiesta Gigagabyte Max. 4 utilizzate Disco Memoria su disco richiesta Gigabyte Max. 500 Portabilità Installabilità Tempi installazione Giorni di lavoro per installare il Giorni / 5 prodotto in un nuovo ambiente persona operativo

Modulo 2 - Lâ&#x20AC;&#x2122;analisi dei dati UnitĂ Didattiche 1. Il disegni (analisi dei dati) 2. I modelli per la progettazione dei dati

1. IL DISEGNO (ANALISI) DEI DATI L’attività di determinazione dei dati consiste nel decidere quali siano le informazioni (dati) che necessitano all’applicazione perché essa funzioni. Nelle applicazioni informatiche gestionali i dati sono organizzati in archivi e quindi l’analisi dei dati deve portare alla definizione degli archivi di interesse. In generale un archivio è un insieme organizzato di informazioni caratterizzato da alcune proprietà fondamentali:  tra esse esiste un collegamento logico (cioè sono in qualche modo inerenti allo stesso argomento)  sono riportate secondo un formato che ne rende possibile l’interpretazione  sono registrate su un supporto su cui è possibile scrivere o rileggere le informazioni anche a distanza di tempo  sono organizzate in modo da permettere una facile consultazione Un esempio di archivio è l’elenco telefonico degli abbonati di una provincia in cui:  il collegamento logico è rappresentato dal fatto che le informazioni sono relative a persone che possiedono tutte un apparecchio telefonico in quella provincia  le informazioni sono raggruppate secondo una unità logica (soggetto) che in questo caso coincide con l’abbonato. Per ogni abbonato vengono riportati sempre il cognome e nome, l’indirizzo ed il numero telefonico e sempre in quest’ordine. Questa disposizione delle informazioni costituisce quindi il formato dell’archivio  l’elenco è registrato su un supporto quale la carta che dura nel tempo  le informazioni sono riportate seguendo l’ordine alfabetico dei cognomi, all’interno della suddivisione per comune, per permettere un veloce reperimento del numero di telefono della persona cercata (organizzazione delle informazioni). In generale per definire un archivio, occorre specificare:  il nome, che coincide con il soggetto memorizzato nell’archivio, scritto al plurale (ad es. abbonati, studenti, docenti, fatture, pagamenti, prodotti). Il soggetto viene indicato col nome di record (in italiano registrazione)  le informazioni riportate per ogni soggetto (nome, cognome, indirizzo, cap, città, provincia, numero telefonico) che costituiscono i campi (attributi) del record. L’insieme dei campi viene indicato col nome di tracciato record. Per ogni attributo vanno indicati: o Nome attributo o Descrizione o Formato (numerico, alfanumerico, data) o Lunghezza o Obbligatorietà o Valori minimi e massimi accettabili oppure lista dei valori consentiti Tra tutti gli attributi vanno individuati quelli che permettono di individuare univocamente ogni record dell’archivio e che costituiscono quindi l’identificatore o chiave dell’archivio. Si fa notare che l’insieme degli attributi che formano l’identificatore deve essere minimale, cioè deve essere costituito dai soli attributi indispensabili per risalire univocamente al record. Ad es., se cognome, nome e città permettono di individuare univocamente un abbonato, l’identificatore è costituito da questi e solo questi tre attributi. Qualunque aggiunta di un altro attributo all’identificatore, ad es. l’indirizzo, va evitata in quanto complica inutilmente il processo di identificazione del record.  il supporto dove archiviare i dati (carta, disco magnetico, disco ottico)  il n° massimo di soggetti (chiamate istanze o occorrenze) che l’archivio potrà contenere (ad es. n° massimo di abbonati di una provincia, oppure n° massimo di studenti di una scuola)  l’organizzazione dell’archivio, cioè il modo con cui i dati sono memorizzati e ricercati. L’organizzazione oggi utilizzata è quasi esclusivamente quella dei database. 1.1 Tipi di archivi  anagrafici: contengono le informazioni sui soggetti, le persone, i prodotti  movimenti: registrano gli eventi prodotti dai soggetti descritti negli archivi anagrafici  parametri: contengono i dati che rimangono costanti per un certo periodo di tempo. Esempio di progettazione di un archivio di dati Fase di analisi – Tavola dei metadati Nome Descrizione Formato Lunghezza Numero Numero della c. d’i. alfanumerico 9 Comune Comune che rilascia la carta alfanumerico 40 Cognome alfanumerico 30 19

Obbligatorio si si si

Nome Data nascita N° atto Comune nascita Cittadinanza Comune residenza Provincia residenza Via Stato civile Professione Statura Capelli Occhi Segni particolari Foto Data rilascio Data scadenza

alfanumerico data numerico alfanumerico alfanumerico alfanumerico alfanumerico alfanumerico alfanumerico alfanumerico numerico alfanumerico alfanumerico alfanumerico immagine data data

N° registrazione atto di nascita

Fase di progettazione – Definizione degli archivi NOME ARCHIVIO Carta Identità ORGANIZZAZIONE Indicizzato sul campo NumCdi Nome campo Tipo Lunghezza Obbligatorietà NumCdi carattere 9 si CodComuneRes intero 4 si Cognome carattere 30 si Nome carattere 30 si DataNascita data (ggmmaaaa) 8 si AttoNascita intero 10 si CodStato carattere 2 si CodComuneNasc intero 4 no ComuneNascita carattere 40 si

Via CodStatoCivile Professione Statura Capelli Occhi Segni particolari Foto DataRilascio DataScadenza

carattere carattere carattere decimale carattere carattere carattere immagine data (ggmmaaaa) data (ggmmaaaa)

40 1 20 1,2 20 20 160 8 8

si si si si si si no si si si

30 gg/mm/aaaa 10 40 40 40 2 40 20 20 3 20 20 100 gg/mm/aaaa gg/mm/aaaa

si si si si si si si si si si si si si no si si si

Note

presente solo se CodStato = “IT” ricavato automaticamente da ComuniItaliani se CodStato = “IT”, fornito da input per gli altri stati

Calcolata automaticamente da DataRilascio

NOME ARCHIVIO StatiEsteri ORGANIZZAZIONE Indicizzato sul campo CodStato Nome campo Tipo Lunghezza CodStato carattere 2 Stato carattere 40 Nazionalità carattere 40 NOME ARCHIVIO ComuniItaliani ORGANIZZAZIONE Indicizzato sul campo CodComune 20

Obbligatorietà si si si

Note

Nome campo CodComune Comune CodProvincia

Tipo intero carattere carattere

Lunghezza 4 40 2

Obbligatorietà si si si

Note

NOME ARCHIVIO ProvinceItaliane ORGANIZZAZIONE Indicizzato sul campo CodProvincia Nome campo Tipo Lunghezza Obbligatorietà CodProvincia carattere 2 si Provincia carattere 40 si

Note

NOME ARCHIVIO StatoCivile ORGANIZZAZIONE Indicizzato sul campo CodStatoCivile Nome campo Tipo Lunghezza Obbligatorietà CodStatoCivile carattere 1 si StatoCivile carattere 40 si

Note

2. I MODELLI PER LA PROGETTAZIONE DEI DATI La progettazione dei dati viene effettuata a tre livelli successivi, come illustrato nella seguente tabella: Livello Descrizione Strumenti utilizzati Concettuale Costruzione dello schema dei dati Modello E/R Logico Definizione degli archivi e delle relazioni tra gli archivi Algebra relazionale Fisico Implementazione degli archivi sul computer DBMS relazionale Nella colonna degli strumenti utilizzati sono riportati gli strumenti che sono oggi maggiormente usati per la progettazione e la realizzazione della parte dati di un’applicazione informatica. Ciascuno di tali modelli verrà presentato in dettaglio nei paragrafi successivi. Qui di seguito viene fornita una breve descrizione di ciascuno di essi. a) Modello concettuale entità / associazioni (E/R) Utilizza tre tipi di oggetti per la costruzione del modello:  Entità  Attributi  Associazioni

Il Formalismo grafico per la rappresentazione del modello E/R adottato è quello basato sull’ UML (Unified Modelling Language - Linguaggio Unificato di Modellazione)9. b) Modello logico  si basa sul concetto matematico di relazione  poiché le relazioni hanno un’immediata rappresentazione nelle tabelle, utilizza la visione tabellare dei dati, naturale ed intuitiva  è un modello basato sui valori c) Modello fisico – DBMS Costituisce lo schema integrato dei dati (database o base di dati), nel formato che può essere interpretato e gestito da un sofware chiamato DBMS (DataBase Management System). Nel caso in cui si utilizza il modello logico relazionale, il software che gestisce i dati è chiamato RDBMS (Relational DataBase Management System). L’RDBMS utilizza un linguaggio chiamato SQL (Structured Query Language), con il quale è in grado di interagire con l’utente per:  Creare una nuova base di dati con la struttura delle tabelle  linguaggio DDL  Manipolare i dati (inserimento, variazione, cancellazione)  linguaggio DML  Definire i privilegi di accesso ai dati  linguaggio DCL  Estrarre le informazioni mediante interrogazione della base dati  linguaggio QL (Query Language) 2.1 Modello E/R 2.1.1 Definizione Strumento per analizzare le caratteristiche di una realtà in modo indipendente dagli eventi che in essa accadono, al fine di individuare gli elementi di interesse (oggetti, cose, persone, fatti) ed i legami intercorrenti tra loro. 2.1.2 Entità Definizione Oggetto (concreto o astratto) che ha significato anche quando viene considerato in modo isolato ed è di interesse per la realtà che si vuole modellare. Esempi Uno studente, una automobile, una prova sostenuta da uno studente, un movimento contabile. Identificazione L’entità è identificata da un nome (ad es. Studente, Automobile). Istanze Ciascun elemento (esemplare) appartenente all’entità rappresenta un’istanza (ad es. Fiat Punto targata RM806040) Rappresentazione Nome entità 2.1.3 Associazione Definizione Legame che stabilisce un’interazione tra due (o più) entità. 99

L’UML è un linguaggio grafico per visualizzare, specificare, costruire e documentare tutte le costruzioni di sistemi software. 22

PERSONA

AUTOMOBILE possedere

A1 A2

P2 P3

Rappresentazione possedere persona

automobile

2.1.3.1 Grado di un’associazione

N° delle entità che partecipano ad un’associazione. Grado associazione Descrizione 1 Associazione ricorsiva: un’unica entità in associazione con se stessa

2 3 o più

Esempio  Entità Persone con associazione essere padre tra un padre ed i propri figli  Entità Impiegati con associazione coordinare tra un dirigente ed i propri collaboratori

Associazione tra due entità Associazione tra 3 o più entità

Associazione tra le entità Automobile, CasaAutomobilistica e SegmentoAuto

2.1.3.2 Ruolo di un’entità in un’associazione Il ruolo è un sinonimo del nome dell’entità (è il nome che l’entità assume nel giocare quel ruolo nella relazione). Ha senso nel caso di:  associazioni di grado 1: coordinare

essere padre dirigente

padre

Impiegato

Persona

collaboratore 

figlio

due o più associazioni tra le stesse due entità: insegnante insegnare Docente

Classe

coordinare coordinatore

2.1.3.3 Cardinalità (molteplicità) di un’entità nella associazione N° massimo di volte in cui un’istanza di un’entità compare nella relazione (1 or N). Ad es. un Docente insegna in N classi diverse  l’entità Docente ha cardinalità N (una istanza di Docente, ad es. Rossi Mario, compare più volte nell’associazione, ogni volta associato ad una classe diversa).

2.1.3.4 Classificazione delle associazioni in base alla molteplicità delle entità coinvolte  Associazione 1:1 o biunivoca Es.

 Es.

Docente coordinare Classe Studente conseguire Diploma

Associazione 1:N (uno a molti) o semplice ContoCorrente effettuare Movimento Arbitro arbitrare Partita Calciatore giocare Squadra 23

L’entità di sinistra, che compare con molteplicità N, è detta anche entità di partenza o entità madre o entità master; l’entità di destra, che compare con molteplicità 1 è detta anche entità di arrivo o entità figlio o entità detail.

 Es.

Associazione N:N (molti a molti) o complessa Docente insegnare Classe

2.1.3.5 Opzionalità di un’entità nella associazione N° minimo di volte in cui un’istanza di un’entità compare nella relazione (0 or 1)  obbligatorietà / opzionalità dell’entità a partecipare all’associazione La partecipazione opzionale si rappresenta tratteggiando la linea nelle vicinanze dell’entità. L’obbligatorietà / opzionalità è importante per l’entità figlia in una relazione 1:N, in quanto comporta l’obbligatorietà o meno della FK ereditata dalla tabella in cui viene tradotta l’entità figlia. Non è invece importante, ai fini dei vincoli di integrità gestiti dall’RDBMS, l’opzionalità dell’entità madre di una relazione 1:N, che però condiziona il tipo di join da usare, in quanto per selezionare anche le istanze prive di figli, occorre utilizzare l’outer join invece dell’inner join.

2.1.3.6 Trasformazione di un’associazione N:N in due associazioni 1:N Studente

è valutato in N

Num_matricola Cognome Nome

Materia

voto

Sigla Descrizione

Subisce

Studente 1

Valutazione N

riguarda N

Num_matricola Cognome Nome

Materia 1

voto

sigla descrizione

2.1.3.7 Regole di lettura di un’associazione <nome entità di partenza> { deve / può } <nome associazione> { uno solo / uno o più } <nome entità di arrivo> Es.: 1 N Fornitore Prodotto fornire Un fornitore può fornire più prodotti Un prodotto deve essere fornito da un solo fornitore. 2.1.4 Attributi Stabiliscono le proprietà delle entità e delle associazioni. Es. ACQUISTARE PRODOTTO codiceProdotto descrizione prezzoListino

DataAcquisto QuantitàAcquistata ScontoPraticato

Le caratteristiche di ogni attributo sono:  il nome  la descrizione  il formato (ad es. numero, stringa, data etc.)  la dimensione (numero massimo di caratteri inseribili)  l’opzionalità (ved. successivo punto 2.4.2) 24

CLIENTE codiceFiscale nome cognome indirizzo

2.1.4.1 Dominio di un attributo Insieme dei possibili valori assumibili da un attributo (ad es.: il dominio dell’attributo Età è costituito dai numeri naturali compresi tra 0 e 130).

2.1.4.2 Opzionalità di un attributo: valore null Rappresenta una informazione  inapplicabile in quanto, per certe istanze dell’entità, non ha senso un suo valore (ad es. il numero di certificato elettorale per una persona con meno di 18 anni, il titolo di studio di uno studente non ancora diplomato, il cognome da nubile per una persona di sesso maschile etc.)  mancante in quanto è una informazione poco importante che può anche essere omessa (tipico è il caso dei form di raccolta dati presenti su internet)  sconosciuta (la data di consegna di una merce spedita ma non ancora arrivata al magazzino etc.).

2.1.4.3 Attributi derivati Gli attributi derivati sono quelli che si possono ottenere con un’elaborazione (ad es. l’età di una persona, il n° di prodotti acquistati, il saldo di un c/c etc.). Gli attributi derivati non vanno riportati nel modello.

2.1.4.4 Atomicità degli attributi Un attributo deve essere atomico, cioè deve assumere un solo valore (al limite il valore null) per ogni istanza di un’entità. Attributi non atomici, devono essere portati fuori dall’entità, a costituire una nuova entità collegata all’entità di partenza da un’associazione. Ad es., l’attributo proprietari di un immobile, nel caso l’immobile possa avere più di un proprietario, deve essere portato fuori a costituire l’entità Proprietario collegata ad Immobile dall’associazione Possedere.

2.1.4.5 Identificatore o chiave primaria di un’entità DEFINIZIONE

Insieme minimale di uno o più attributi che consentono di distinguere tra loro le istanze di una stessa entità. Nel caso nessuno degli attributi di un’entità può fungere da chiave primaria, si aggiunge un attributo fittizio, un contatore progressivo (che in Access viene incrementato automaticamente) con funzioni di chiave primaria. RAPPRESENTAZIONE La chiave primaria di un’entità viene riconosciuta dalla presenza dell’acronimo { pk } accanto agli attributi chiave oppure dalla sottolineatura degli attributi che ne fanno parte.. 2.1.5 Un particolare tipo di associazione: la gerarchia IS A (È UN) La gerarchia IS A permette di specializzare un’entità, chiamata super-type, in due o più entità, chiamate sub-type, ciascuna delle quali ne rappresenta un aspetto particolare. Le entità sub-type sono alternative, nel senso che a un’occorrenza dell’entità super-type corrisponde un’occorrenza di una sola delle entità sub-type. Ogni entità sub-type a tutti gli attributi dell’entità super-type10, oltre a possedere dei propri attributi specifici. Le associazioni con le altre entità del modello E/R possono essere fatte sull’entità super-type o su una specifica entità sub-type, a seconda di quanto richiesto dalle specifiche. Un esempio è il seguente: matricola codice segue nome Corso Studente descrizione N N cognome 1 IS A 1 Interno

1 Esterno

Attestato (si/no)

N tiene tipo (recupero, sostegno etc.)

Organizzazione

partita_iva ragione_sociale

Si dice che l’entità sub-type eredita gli attributi dell’entità super-type 25

Altri esempi di gerarchia IS A sono i seguenti: Esempio 1: Acquisti acquista

Prodotto

Cod_cliente

Cliente 1 IS A

1 SocietĂ

1 Persona

Cod_fiscale nome cognome

Partita_iva Ragione_sociale Esempio 2: Agenzia Turistica Viaggio

avviene con Mezzo N 1

costo_biglietto ore_durata IS A

Aereo

Treno

Nave

compagnia stazione_partenza num_volo stazione arrivo aeroporto_partenza aeroporto arrivo

porto_partenza porto_arrivo

Modulo 3 - Il modello logico relazionale UnitĂ Didattiche 1. 2. 3. 4. 5.

Gli oggetti del modello relazionale Passaggio dal modello concettuale E/R al modello logico relazionale La normalizzazione delle relazioni Le operazioni relazionali Le operazioni insiemistiche

1. GLI OGGETTI DEL MODELLO RELAZIONALE 1.1 Relazione Il modello relazionale è basato sul concetto matematico di relazione tra insiemi di oggetti, definito nell’algebra relazionale. Consideriamo, ad esempio, i due insiemi: A1 = 4, 9, 16 A2= 2,3 Su di essi è possibile definire la relazione di prodotto cartesiano, che si indica con A1xA2, come l’insieme delle coppie (x, y), dove x appartiene ad A1 ed y appartiene ad A2, cioè: A1xA2 = (x, y) | x ϵ A1, y ϵ A2 = (4,2), (4,3), (9,2), (9,3), (16,2), (16,3) Tra tutte le n-ple della relazione, si possono individuare quelle in cui il secondo termine è il quadrato del primo. Tali nple formano un sottoinsieme Q definito come: Q = (4,2), (9,3) < A1xA2 Esso rappresenta la relazione tra coppie (x, y) che può essere descritta con la frase: “x è il quadrato di y”. Si può quindi definire la relazione su due insiemi A1, A2 come un sottoinsieme del prodotto cartesiano di A1xA2. Più in generale: una relazione R, su n insiemi A1, A2, …, An è un sottoinsieme di tutte le n-ple a1, a2, …, an che si possono costruire prendendo nell’ordine un elemento a1 dal primo insieme A1, a2 dal secondo insieme A2, …., an dall’n-esimo insieme An. La relazione viene rappresentata come una tabella, avente tante colonne quanti sono i domini (grado della relazione) e tante righe quante sono le n-ple (cardinalità della relazione): DOMINI A1 A2 ………….. An a11 a21 an1 a12 a22 an2 cardinalità (t) …….. …….. …………. ……….. a1t

a2t

………….

ant

grado (n) 1.2 Tabella Per la corrispondenza che c’è tra relazione e tabella, si può descrivere il modello relazionale in termini di tabelle. 1.2.1 Definizione La tabella è un oggetto costituito da:  un numero n di colonne (dette anche attributi o campi), ciascuna definita in un certo dominio di valori  un numero r di righe (dette anche t-uple o n-uple o record), ciascuna contenente una combinazione valida di valori per ciascun attributo. Es.: Tabella: AUTOMOBILI Costruttore Modello Segmento Porte N° posti Fiat Panda B 5 4 Ford Focus C 3/5 5 CARDINALITÀ Mercedes A C 3/5 5 (n° righe) Fiat Panda B 3 4 Citroen Citroen Fiat

C3 C2 Punto

C B B

5 3 3/5

GRADO (n° colonne)

1.2.2 Rappresentazione La tabella viene rappresentata tramite il suo schema: AUTOMOBILI (Costruttore, Modello, segmento, Porte, N° posti) 28

5 4 5

1.2.3 Chiave della tabella Un attributo o un insieme minimale di attributi che identificano univocamente ciascuna riga della tabella. Minimale significa che deve essere costituito dal numero minimo di attributi necessario e sufficiente a individuare univocamente una riga della tabella. Ad es., nel caso della tabella Automobili l’insieme minimale dei campi che formano la chiave primaria è costituito dalla terna (Costruttore,Modello, Porte). L’aggiunta di qualsiasi altro attributo a questo insieme costituisce ancora un insieme che individua univocamente ciascuna riga, ma non è più un insieme minimale. 1.3 Database relazionale Un database è un insieme di tabelle, sulle quali si possono:  effettuare operazioni  stabilire associazioni 1.3.1 Requisiti fondamentali delle tabelle di un database relazionale a) Stesso numero di colonne su ogni riga b) Gli attributi contengono informazioni elementari (o atomiche), non scomponibili ulteriormente c) I valori di un attributo appartengono al dominio dei valori possibili per quell’attributo d) Ogni riga deve essere individuata univocamente  chiave primaria e) Non è importante l’ordine con cui le righe compaiono nella tabella. 1.3.2 Accesso al singolo dato elementare in un database relazionale Per accedere al singolo dato elementare, occorre specificare:  Nome del database  Nome della tabella  Nome della colonna  Numero della riga  nome e valore degli attributi che formano la chiave primaria

2. PASSAGGIO DAL MODELLO CONCETTUALE E/R AL MODELLO LOGICO RELAZIONALE Le regole per passare dagli oggetti del modello E/R a quelli del modello relazionale sono le seguenti.

a) Entità  tabella (il nome dell’entità al singolare diventa, al plurale, il nome della tabella)

b) c) d) e)

Attributo dell’entità  colonna della tabella Caratteristiche attributo dell’entità  caratteristiche attributo della tabella Chiave primaria (identificatore) dell’entità  chiave primaria della tabella Associazione 1:n 

la chiave primaria dell’entità “a molti” (cioè dell’entità che compare con cardinalità N) si aggiunge agli attributi della tabella associata all’entità “a uno” e prende il nome di chiave esterna (foreign key). Questa regola può anche enunciarsi in quest’altro modo: “l’entità che compare con cardinalità 1 eredita la chiave primaria dell’entità che compare con cardinalità N. La chiave ereditata diventa una chiave esterna nella tabella che l’eredita”. Att.ne: tra due entità possono esserci più di un’associazione, come ad es.: N

Persona



è nata

risiede N

Comune

lavora N 1 In questo caso, la chiave primaria dell’entità Comune viene aggiunta tre volte alla tabella Persone derivata dall’entità Persona, che quindi conterrà tre chiavi esterne alla stessa tabella. Per motivi di chiarezza le tre chiavi esterne aggiunte alla tabella Persone verranno chiamate in modo diverso (ad es. ComuneNascita, ComuneResidenza, ComuneLavoro). eventuali attributi dell’associazione, vengono inseriti nella tabella associata alla entità “a uno” della relazione Es.: Persona acquistare Automobile  Il CodiceFiscale di Persona diventa chiave esterna di Automobile e la Data acquisto diventa un attributo della tabella Automobile.

f) Associazione 1:1 

Se le due entità non rappresentano degli oggetti distinti, ma sono quasi delle raccolte di attributi diversi dello stesso oggetto (ad es. una persona)  unica tabella contenente gli attributi di entrambe le entità e chiave primaria pari alla chiave primaria dell’una o dell’altra tabella, indifferentemente Es. Cittadino possedere CodiceFiscale  Se le due entità rappresentano due oggetti distinti che è opportuno lasciare separati in due tabelle  due tabelle distinte, come se si trattasse di un’associazione 1:n. L’entità che partecipa in modo opzionale all’associazione viene assimilata all’entità “a uno” dell’associazione 1:n (eredita tra i suoi attributi quelli della chiave primaria dell’entità “a molti”). Es.: Docente coordinare Classe Il docente, che è l’entità che partecipa in modo opzionale (ci sono infatti dei docenti che non coordinano alcuna classe), eredita la chiave primaria dell’entità classe11. Nel caso di partecipazione opzionale di entrambe le entità (ad es. nel caso che possano esistere classi senza coordinatore), si può considerare come entità “a uno” che fornisce la chiave esterna, una qualsiasi delle due entità collegate. g) Associazione n:n L’associazione diventa una terza entità, che eredita gli eventuali attributi dell’associazione e gli attributi delle chiavi primarie delle due entità associate. Att.ne ad identificare la chiave primaria della nuova relazione. 11

Questa regola facilita la ricerca dei docenti che non sono coordinatori: basta selezionare i docenti che presentano l’attributo classe = null. Nel caso si rovesciasse la regola (l’entità che compare obbligatoriamente eredita la chiave primaria dell’altra entità) non otterremmo nessuna facilitazione in quanto non ha senso cercare le classi senza coordinatori in quanto ogni classe partecipa all’associazione e quindi ogni classe ha un coordinatore. 30

Esempio 1 insegna

docente

codice docente

sigla classe codice materia

classe

n codice materia

La chiave primaria della tabella Insegnamenti è data dall’unione degli attributi delle chiavi primarie delle due entità collegate: PRIMARY KEY = (Codice docente + Sigla classe) Ciò è vero solo se si ammette che un docente non possa insegnare due materie diverse nella stessa classe (se ciò non è vero, occorre comprendere anche il codice materia nella chiave primaria). Esempio 2 materia valuta

studente

codice materia

matricola data valutazione Poiché uno studente può essere valutato più volte nella stessa materia nel corso dell’anno, la chiave primaria della tabella Valutazioni deve comprendere un ulteriore attributo (la Data valutazione), in aggiunta a quelli delle chiavi primarie ereditate dalle due entità collegate: PRIMARY KEY = (Matricola + Codice materia + Data valutazione).

h) Gerarchia IS A Ci sono diversi modi di tradurre una gerarchia IS A. Il più utilizzato consiste nel sostituire tutte le entità della gerarchia con un’unica entità comprendente sia gli attributi dell’entità super-type sia quelli di ciascuna entità sub-type, con l’aggiunta di un ulteriore attributo che specifica a quale delle entità sub-type appartiene ogni istanza dell’entità unica che viene creata (si ricorda che le entità sub-type sono mutuamente esclusive, per cui un’istanza dell’entità super-type è associata a un’istanza di una sola delle entità sub-type). Gli attributi della nuova entità, derivati dalle entità sub-type devono essere dichiarati opzionali (null) in quanto per ogni istanza sono valorizzati solo quelli dell’entità sub-type a cui l’istanza si riferisce. Alla nuova entità fanno capo tutte le associazioni cui partecipano le entità della gerarchia. La nuova entità così creata si traduce in una tabella dello schema relazionale. Ad, esempio, il seguente modello E/R:

matricola nome cognome

studente

segue N

tipo

interno

corso N 1

cod_corso titolo

1 IS A

tiene 1

esterno

organizzatore N attestato

N partita_iva ragione_sociale

da luogo alle tabelle seguenti: Studenti (matricola, cognome, nome) Organizzatori (partita_iva, ragione_sociale) fk (Organizzatori) Corsi (cod_corso, tipo, attestato, partita_iva) fk (Studenti) fk(Corsi) Frequenze (matricola, cod_corso) in cui i campi tipo, attestato, partita_iva della tabella Corsi devono essere dichiarati null. 31

Nota: Importanza delle chiavi esterne (foreign key) nel modello relazionale Le FK sono importanti perché permettono: a) di mettere in collegamento due tabelle tra loro (cosa comunque possibile anche con campi che non sono delle foreign key) b) di implementare l’integrità referenziale, termine con il quale s’intende il mantenimento dell’allineamento dei dati tra due tabelle. La tabella su cui viene inserito il vincolo di foreign key viene detta tabella derivata (o tabella detail) mentre la tabella riferita dal vincolo di foreign key viene detta tabella esterna (o tabella master). In particolare il vincolo di foreign key comporta che:  non si può cancellare una riga della tabella master se esiste una riga nella tabella detail che contiene nel campo foreign key il valore della chiave primaria della riga da cancellare  non si può cambiare il valore della chiave primaria di una riga della tabella master se esiste una riga nella tabella detail che contiene nel campo foreign key il valore della chiave primaria che si vuole cambiare.  non si può inserire nella tabella detail una riga che contiene nel campo foreign key un valore che non è presente come chiave primaria in nessuna riga della tabella master.

3. LA NORMALIZZAZIONE DELLE RELAZIONI La normalizzazione completa il processo di progettazione dello schema logico relazionale. Dopo aver progettato lo schema concettuale mediante la costruzione del modello E/R dei dati e aver derivato le conseguenti relazioni (tabelle) in base alle regole viste in precedenza, la normalizzazione esamina le tabelle alla ricerca di possibili incongruenze nella loro definizione. L’incongruenza più grave che deve essere evitata riguarda la ridondanza dei dati che consiste nella duplicazione dello stesso dato quando invece può essere definito in un solo punto. Consideriamo ad es. la seguente tabella PRODOTTI: codice_prodotto codice_magazzino quantità indirizzo_macazzino 545 Ca1 80 Via tonale, 1 545 Pa2 70 Via mazzini, 25 100 Ca1 200 Via tonale, 1 200 Pa1 140 Via garibaldi, 38 545 Pa1 37 Via garibaldi, 38 100 Pa2 300 Via mazzini, 25 100 Ve1 70 P.za cavour, 1 La chiave della tabella è formata dagli attributi codice_prodotto e codice_magazzino, in quanto il solo codice del prodotto non identifica univocamente una riga, in quanto lo stesso prodotto può essere presente in diversi magazzini. Questa tabella non è ben organizzata: infatti l’indirizzo di un certo magazzino si ripete ogni volta che quel magazzino viene riferito tramite il codice e, quindi, i dati nella colonna indirizzo_magazzino sono ridondanti. La ridondanza va evitata perché : 1. Spreca spazio su disco 2. Provoca le seguenti anomalie quando i dati vengono aggiornati: a. Anomalia di variazione: se un magazzino, ad es. Ca1, cambia indirizzo, bisogna cambiare il suo indirizzo su tutte le righe della tabella PRODOTTI in cui quel magazzino compare. Se, per qualsiasi ragione, la variazione viene fatta solo su alcune delle righe interessate, i dati memorizzati diventano inconsistenti (il magazzino compare con due indirizzi diversi: quale è quello giusto?) b. Anomalia di cancellazione: se un magazzino si svuota, cioè se vengono cancellate tutte le righe dei prodotti che si trovano in quel magazzino, viene perso l’indirizzo del magazzino c. Anomalia di inserimento: quando viene aperto un nuovo magazzino non si può memorizzare il suo indirizzo finché non viene inserito un prodotto di quel magazzino. Per evitare la ridondanza del campo indirizzo_magazzino, si deve sostituire la tabella PRODOTTI con una coppia di tabelle, PRODOTTI e MAGAZZINI, descritte dagli schemi seguenti: PRODOTTI (codice_prodotto, codice_magazzino, quantità) MAGAZZINI (codice_magazzino, Indirizzo_magazzino) Per evitare la ridondanza dei dati e le possibili anomalie che ne conseguono, sono stati definiti opportuni criteri che le tabelle devono soddisfare, criteri che vanno sotto il nome di forme normali. Se la tabella non soddisfa una forma normale, deve essere trasformata secondo una certa regola in un insieme di tabelle che soddisfano la forma violata. Il processo di trasformazione prende il nome di normalizzazione. Le forme normali che le tabelle devono rispettare sono tre e sono di livello crescente, nel senso che per rispettare una forma, devono essere rispettate tutte le precedenti. 3.1 Prima forma normale (1FN in inglese 1NF) Una tabella è in 1NF quando rispetta i requisiti fondamentali delle tabelle relazionali definiti nel punto 3.1. In particolare gli attributi devono essere informazioni non ulteriormente scomponibili, cioè non devono avere sottoattributi, né costituire un gruppo di attributi ripetuti. Ad es., nella tabella: SCUOLE (città, nome_scuola, preside, indirizzo_scuola, alunni) L’attributo alunni non è elementare poiché è costituito da un insieme di attributi ripetuti (i nomi degli alunni). La tabella non è quindi in 1FN. Per portarla in 1FN occorre estrarre l’attributo ripetuto e inserirlo in una nuova tabella in cui riportare anche i campi che costituiscono la chiave primaria della tabella originaria: 33

SCUOLE (città, nome_scuola, preside, indirizzo) ALUNNI (codice_fiscale, nome, cognome, città, nome_scuola) 3.2 Seconda forma normale (2FN in inglese 2NF) Una tabella è in 2FN se e in 1FN e tutti i suoi attributi non-chiave dipendono dall’intera chiave, cioè non possiede attributi che dipendono solo da una parte della chiave. La 2FN riguarda solo tabelle la cui chiave primaria è composta, cioè è formata da due o più attributi. Ad es, la tabella: PRODOTTI (codice_prodotto, codice_magazzino, quantità, indirizzo_magazzino) Non è in 2FN in quanto il campo indirizzo_magazzino dipende da codice_magazzino che rappresenta una porzione di chiave e non l’intera chiave. Per portarla in 2FN, occorre estrarre tutti i campi che dipendono da una parte della chiave e formare una nuova tabella, la cui chiave primaria è proprio costituita dai campi di quella parte di chiave da cui dipendono: PRODOTTI (codice_prodotto, codice_magazzino, quantità) MAGAZZINI (codice_magazzino, indirizzo_magazzino) 3.3 Terza forma normale (3FN in inglese 3NF) Una tabella è in 3FN se è in 2FN e tutti gli attributi non chiave dipendono direttamente dalla chiave, cioè non ci sono attributi che dipendono da altri attributi non-chiave. La 3FN elimina la dipendenza transitiva dagli attributi della chiave. Ad es. la tabella: STUDENTI (codice_fiscale_studente, nome_scuola, telefono_scuola, preside) Non è in 3FN perché i campi telefono_scuola e preside dipendono dall’attributo non-chiave nome_scuola. Si ha quindi una ridondanza dei dati, in quanto il telefono della scuola ed il preside vengono ripetuti per ogni studente di quella scuola e ciò porta alle anomalie ed all’inconsistenza dei dati di cui si è già parlato. La normalizzazione in 3FN si ottiene estraendo i campi che non dipendono direttamente dalla chiave primaria e formando una nuova tabella la cui chiave primaria è proprio il campo non-chiave da cui dipendono: STUDENTI (codice_fiscale_studente, nome_scuola) SCUOLE (nome_scuola, telefono_scuola, preside)

4. LE OPERAZIONI RELAZIONALI 



sono specificate tramite gli operatori relazionali di: o selezione o proiezione o congiunzione (join) permettono di effettuare le interrogazioni alla base di dati per ottenere le informazioni desiderate: o estraendo, da una tabella, una sottotabella o combinando tra loro due o più tabelle, formando così nuove tabelle

4.1 Selezione  selezione delle righe di una tabella che soddisfano a una certa condizione  operatore unario  grado tabella risultato = grado tabella origine  cardinalità tabella risultato <= cardinalità tabella origine  in linguaggio di pseudocodifica, viene indicata con:

σcondizione tabella 4.2 Proiezione  genera una nuova tabella estraendo solo alcune colonne dalla tabella originaria  operatore unario  grado tabella risultato <= grado tabella origine  cardinalità tabella risultato <= cardinalità tabella origine (infatti, a differenza dell’SQL, le tuple duplicate della tabella risultato vengono scartate) o problema della unicità delle righe di una tabella (accorpaggio delle righe uguali  cardinalità tabella risultato < cardinalità tabella origine  in linguaggio di pseudocodifica, viene indicata con:

Πcol1, col2, … tabella 4.3 Congiunzione (join) 4.3.1 Join naturale  combina due tabelle R e S, aventi uno o più attributi comuni (attributi con lo stesso nome e definiti sullo stesso dominio), generando una nuova tabella  operatore binario  le colonne della tabella risultato comprendono sia quelle di R sia quelle di S, ma gli attributi comuni sono presenti una sola volta  le righe della tabella risultato sono ottenute abbinando le sole righe di R e S con valori identici negli attributi comuni  se R ha grado N1, S ha grado N2 ed il numero di attributi comuni è pari a K, la tabella risultato ha grado N1 + N2 – K  la cardinalità non è prevedibile a priori  in linguaggio di pseudocodifica, viene indicata con:

S a1, a2, …..

4.3.2  

Equi-Join è un join con caratteristiche più generali in quanto permette congiungere tabelle confrontando attributi che hanno nomi diversi in linguaggio di pseudocodifica, viene indicata con:

Ra1, a2,..

Sc1, c2, …

oppure con:

S a1 = c1 and a2 = c2 …… 35



le colonne della tabella risultato comprendono sia quelle di R sia quelle di S. Infatti, le colonne su cui viene fatto il join hanno nomi differenti nelle due tabelle e, quindi, compaiono tutte. 4.3.3 Join interno Congiunge solo le righe per le quali i valori degli attributi della prima tabella trovano un uguale valore nei corrispondenti attributi della seconda tabella. Sono di questo tipo il join naturale e l’equi-join. 4.3.4 Join esterno (outer join) Restituisce le righe dell’una o dell’altra tabella, anche se non sono presenti valori uguali per gli attributi comuni. Si suddivide in:

4.3.4.1 Left join Elenca tutte le righe della prima tabella congiungendo, tra le righe della seconda, solo quelle per le quali si trovano valori uguali per gli attributi comuni. Gli attributi della seconda tabella, nelle righe in cui non c’è accoppiamento, vengono impostati a null. In linguaggio di pseudocodifica viene indicato con: Left

Ra1, a2,..

Sc1, c2, …

4.3.4.2 Right join Elenca tutte le righe della seconda tabella congiungendo, tra le righe della prima, solo quelle per le quali si trovano valori uguali per gli attributi comuni. Gli attributi della prima tabella, nelle righe in cui non c’è accoppiamento, vengono impostati a null. In linguaggio di pseudocodifica viene indicato con: Right

Ra1, a2,..

Sc1, c2, …

4.3.4.3 Full join È l’unione del left join e del right join: elenca sia le righe che si congiungono, sia le righe della tabella di sinistra che non si accoppiano (queste righe riportano i valori null negli attributi della tabella di destra), sia le righe della tabella di destra che non si accoppiano (queste righe riportano i valori null negli attributi della tabella di sinistra). In linguaggio di pseudocodifica viene indicato con: Full

Ra1, a2,..

Sc1, c2, …

4.3.5 Prodotto cartesiano R x S = tabella ottenuta combinando ciascuna riga di R con tutte le righe di S.  grado tabella risultato = grado (R) + grado (S)  cardinalità tabella risultato = cardinalità (R) * cardinalità (S) 4.3.6

Theta join

σcondizione (R x S)

Condizione Il modo con cui si costruiscono le congiunzioni nel linguaggio SQL segue questa strada:  Si effettua il prodotto cartesiano delle tabelle da congiungere  Si operano opportune selezioni e proiezioni sulle righe della tabella “prodotto cartesiano”.

5. LE OPERAZIONI INSIEMISTICHE Si applicano a tabelle con struttura omogenea, cioè con:  stesso numero di colonne  colonne dello stesso tipo e nello stesso ordine 5.1 Unione T=RU S  

T contiene le righe di R + le righe di S, con eliminazione delle righe ripetute

grado tabella unione = grado (R) = grado (S) cardinalità tabella unione <= cardinalità (R) + cardinalità (S)

5.2 Intersezione T=RΩ S  

T contiene le righe di R che sono presenti anche in S (righe comuni)

grado tabella intersezione = grado (R) = grado (S) cardinalità tabella intersezione <= MIN (cardinalità (R), cardinalità (S))

5.3 Differenza T = R - S T contiene le righe di R che non sono contenute in S (non vale la proprietà commutativa)  

grado tabella unione = grado (R) = grado (S) cardinalità tabella unione <= cardinalità (R)

5.4 Utilizzo di operazioni relazionali ed insiemistiche nelle interrogazioni Selezionare il cognome degli abbonati senza abbonamenti. 1) Modo left

Πcognome (σnumabb is null (abbonaticodiceFiscale

abbonamenticodiceFiscale))

2) Modo

Πcognome(abbonaticodiceFiscale (ΠcodFiscale abbonati - ΠcodFiscale abbonamenti) codiceFiscale)

Modulo 4 – Il modello fisico: il linguaggio SQL Unità Didattiche 1. 2. 3. 4.

Generalità DDL – Definizione struttura tabelle DML – Manipolazione dei dati delle tabelle QL – Interrogazione dei dati delle tabelle

1. GENERALITÀ 1.1 Storia di SQL 1° standard SQL 1986 2° revisione (SQL2) 1992 3° revisione (SQL3) 1999: implementa nuove funzionalità per il trattamento della ricorsione e degli oggetti 1.2 Sezioni del linguaggio SQL A seconda del tipo di funzione svolta, i comandi del linguaggio SQL possono essere raggruppati in quattro sezioni: Sezione DDL

Descrizione Definire la struttura delle tabelle

DML

Modificare i dati contenuti nelle tabelle con le operazioni di: inserimento, variazione e cancellazione Porre interrogazioni al data base Garantire la riservatezza dei dati (concessione dei privilegi di accesso agli utenti)

QL (SELECT) DCL

2. DDL (DATA DEFINITION LANGUAGE) – DEFINIZIONE DELLA STRUTTURA DELLE TABELLE 2.1 Creazione struttura tabelle CREATE TABLE nome_tabella (col1

tipo

[not null],

col2

tipo

[not null],

…………………………………………. coln

tipo

[not null],

PRIMARY KEY (col1, col2….), FOREIGN KEY (col1, col2, …) REFERENCES tabella-estena (col1, col2, …..)) [ON UPDATE CASCADE ON DELETE CASCADE12]; Nel caso la chiave primaria di tabella-esterna è costituita da una sola colonna, si può omettere di indicare il nome della colonna dopo tabella-esterna: FOREIGN KEY (col1) REFERENCES tabella-esterna Nel caso si specifichi la clausola ON DELETE CASCADE, quando viene cancellata la riga nella tabella esterna, il DB manager cancella tutte le righe delle tabelle vincolate tramite foreign key che riferiscono la riga cancellata. Analogamente se si specifica la clausola ON UPDATE CASCADE, quando viene cambiato il valore della chiave primaria di una riga, la variazione viene propagata su tutte le tabelle vincolate. Ad es.: FOREIGN KEY (manager) REFERENCES impiegati (matricola) ON DELETE CASCADE ON UPDATE CASCADE; 2.1.1 Tipi di dati standard Tipo

Descrizione

CHAR (n)

Stringa di lunghezza fissa di n caratteri

VARCHAR (n)

Stringa di lunghezza variabile di massimo n caratteri. Gli spazi finali non vengono memorizzati Numero intero con precisione 10 (10 cifre) Numero decimale con precisione p (p cifre complessive) e s cifre decimali. Ad es. DECIMAL(5,2) Per esempio, con DECIMAL(5,2) si possono rappresentare valori da +999,99 a -999,99. In MySql se non viene specificato niente vengono assunte 10 cifre intere senza decimali, cioè DECIMAL(10,0). Data nella forma GG/MM/AAAA

INTEGER o INT DEC(p, s) o DECIMAL(p,s)

DATE TIME

2.1.2

NUMERIC

In MySql il formato è: AAAA-MM-GG

Ora nella forma HH:MM:SS

Esempio di creazione tabelle impiegati matricola nome cognome citta stipendio

Tipo equivalente in Access

comprende

1 dirige

dipartimenti

codice descrizione indirizzo citta

Invece di CASCADE si può specificare SET NULL (solo se la colonna su cui si applica la foreign key è di tipo NULL) oppure SET DEFAULT. Nel primo caso la colonna della tabella vincolata viene impostata a NULL, nel secondo caso al valore di default (0 o blank) quando la riga della tabella esterna viene cancellata o ne viene cambiato il valore della chiave. 40

CREATE TABLE impiegati (matricola integer not null, nome varchar(30) not null, cognome varchar(30) not null, citta varchar(30), stipendio decimal(9,2), codice integer not null, PRIMARY KEY (matricola), FOREIGN KEY (codice) REFERENCES dipartimenti (codice)); CREATE TABLE dipartimenti (codice integer not null, descrizione varchar(30) not null, indirizzo varchar(30) not null, citta varchar(30), manager integer not null, PRIMARY KEY (codice), FOREIGN KEY (manager) REFERENCES impiegati (matricola));13 2.2 Aggiornamento struttura tabelle ALTER TABLE nome_tabella ADD nome-colonna tipo [not null]; DROP nome-colonna; ADD FOREIGN KEY (colonna) REFERENCES tabella_esterna (chiave); 2.3 Cancellazione tabella DROP TABLE nome_tabella;

I nomi delle colonne è opportune che non contengano spazi intermedi (se un nome è composto, si può sostituire lo spazio con il carattere underscore “_”). Questo perché alcuni DB non accettano gli spazi intermedi nei nomi. In ACCESS è possibile utilizzare gli spazi, a patto di riferire, nei comandi SELECT, i nomi che contengono spazi racchiudendoli tra parentesi quadre. 41

3. DML (DATA MANIPULATION LANGUAGE) – MANIPOLAZIONE DEI DATI DELLE TABELLE 3.1 Inserimento di una riga INSERT INTO nome_tabella (col1, col2, …., coln) VALUES (val1, val2, …., valn); oppure INSERT INTO nome_tabella VALUES (val1, val2, …., valn); La differenza tra i due formati è che nel primo è possibile specificare un ordine qualsiasi delle colonne della tabella, mentre nel secondo i valori devono corrispondere alle colonne nell’ordine come sono state definite in fase di creazione della struttura della tabella. N.B. se un campo è dichiarato NULL e non deve assumere nessun valore, può essere omesso nel comando INSERT oppure può essere presente indicando NULL come valore da assegnargli. 3.2 Aggiornamento di una o più righe di una tabella UPDATE nome_tabella SET col1 = valore, col2 = valore …., coln = valore WHERE condizione; 3.3 Aggiornamento di una o più righe di più tabelle È possibile cancellare le righe da due o più tabelle, facendo una join (inner o left) fra le tabelle e cancellando le righe che si congiungono. Ad es.: DELETE FROM artisti, soggiorni USING artisti inner join soggiorni ON artisti.nome = soggiorni.nome and artisti.cognome=soggiorni.cognome WHERE artisti.nome = 'pietro' and artisti.cognome = 'boccioni' 3.4 Cancellazione di una o più righe di una tabella DELETE FROM nome_tabella WHERE condizione; La condizione può essere composta da join tra più tabelle. Ad es., date le seguenti tabelle: persone (nome, anno_nascita) fk (persone) fk (persone) coniugi (marito, moglie, anno_matrimonio) si vogliono cancellare i matrimoni in cui o il marito o la moglie abbiano 30 o più anni quando si sono sposati: DELETE * FROM coniugi WHERE marito in (select p.nome from coniugi h inner join persone p on h.marito=p.nome where anno_matrimonio – anno_nascita >= 30) or moglie in (select p.nome from coniugi h inner join persone p on h.moglie=p.nome where anno_matrimonio – anno_nascita >= 30);

4. QL(QUERY LANGUAGE) – INTERROGAZIONE DEI DATI DELLE TABELLE 4.1 Formato base del comando SELECT SELECT lista-colonne FROM lista-tabelle WHERE condizione; dove: lista-colonne elenco delle colonne separato da virgole. Se la tabella provvisoria risultato della SELECT contiene colonne uguali, occorre specificare la colonna desiderata come: nome-tabella.nome-colonna Per selezionare tutte le colonne della tabella risultato, basta specificare * (asterisco). In caso di più tabelle in join tra loro, per selezionare le colonne di una sola tabella, si dovrà scrivere: nome_tabella.* lista-tabelle elenco di tutte le tabelle interessate dall’interrogazione. L’elenco è composto: in SQL dai nomi delle tabelle separate da virgole in SQL2 dalle tabelle e dai join (inner o outer) che le congiungono nell’interrogazione condizione specifica le condizione di selezione delle righe delle tabelle. La condizione è espressa secondo le modalità abituali dell’informatica, componendo i confronti con gli operatori AND, OR, NOT. 4.1.1 Clausole ALL e DISTINCT del comando SELECT ALL elenca tutte le righe ottenute dalla selezione, anche se duplicate. Viene assunto per default DISTINCT le righe duplicate della tabella risultato vengono ridotte ad una 4.1.2 Intestazione delle colonne della tabella risultato Per cambiare il nome della colonna: SELECT colonna-DB as nuovo-nome Se il nuovo-nome contiene spazi intermedi, in MySql va racchiuso tra virgolette, in Access va racchiuso tra parentesi quadre. 4.1.3 Inclusione di campi calcolati nella tabella risultato SELECT espressione as nome-colonna in cui: espressione espressione algebrica che lavora sulle colonne delle tabelle interrogate Ad es.: SELECT matricola, stipendio as “stipendio attuale”, stipendio * 1,1 as “stipendio futuro” FROM impiegati; Nel caso si voglia limitare il numero delle cifre decimali del campo calcolato, si deve utilizzare la clausola ROUND, nel modo seguente: SELECT round(espressione, n°cifre_decimali) as nome_ribattezzato ….. Ad es.: SELECT ordini.idordini, data, righeordine.codisbn as "codice isbn", titolo, quantita, righeordine.prezzo, righeordine.sconto, ROUND (righeordine.prezzo * (1 - righeordine.sconto) *quantita, 2) as "importo da pagare" FROM (ordini INNER JOIN righeordine on ordini.idordini = righeordine.idordini) WHERE userid = "pippo" ORDER BY ordini.idordini 4.1.4 Parametrizzazione dei valori delle query in ACCESS Anche se non fa parte dello standard ANSI, si può far precedere la query dalla seguente dichiarazione dei parametri che verranno richiesti all’atto della esecuzione della query stessa: PARAMETERS parametro1 tipo1, parametro2 tipo2, ….., parametron tipon; 43

Ad es.: parameters cogno varchar(30), stip numeric; select * from impiegati where cognome = cogno and stipendio = stip; All’atto della esecuzione verrà richiesto prima il valore di cogno e poi quello di stip. Lo stesso risultato si ottiene più semplicemente indicando tra parentesi quadre il valore parametrico da passare alla query. Ad es.: SELECT cognome, nome, città FROM impiegati WHERE stipendio > [Retribuzione annuale minima?]; 4.2 Implementazione in SQL delle operazioni dell’algebra relazionale 4.2.1 Proiezione Si realizza facendo seguire, al comando SELECT, l’elenco degli attributi richiesti 4.2.2 Selezione Si realizza utilizzando la clausola WHERE del comando SELECT. 4.2.3 Congiunzione (Join) Il join, in accordo alle specifiche SQL2, viene indicato come inner join e la condizione di congiunzione viene scritta nella clausola FROM del comando SELECT, dopo la parola ON14. ES.: selezionare il cognome e il nome degli impiegati che lavorano nei dipartimenti che si trovano a “Roma” SELECT cognome, nome, descrizione FROM dipartimenti INNER JOIN impiegati ON dipartimenti.codice = impiegati.codice WHERE dipartimenti.citta = “Roma”;

4.2.3.1 Utilizzo degli alias per i nomi delle tabelle L’alias è un nome temporaneo con cui si ribattezza una tabella ai soli fini dell’interrogazione in cui è utilizzato. ES 1.: selezionare nome, cognome e dipartimento di appartenenza degli impiegati dei dipartimenti della città di “Roma”: SELECT cognome, nome, descrizione FROM dipartimenti AS d INNER JOIN impiegati AS i ON d.codice = i.codice WHERE d.citta = “Roma”; La scrittura della parola AS è opzionale. ES. 2: selezionare i dipartimenti, indicando, per ciascuno, il nome, il cognome e la matricola del manager che lo dirige SELECT d.codice,descrizione,matricola,cognome,nome FROM dipartimenti d inner join impiegati i ON d.codice = i.codice WHERE manager = matricola N.B. In Access, nella clausola ON è obbligatorio specificare la tabella cui il campo appartiene, anche se non ci sono pericoli di omonimia.

4.2.3.2 Left-join, right-join, full-join Il left-join, il right-join ed il full-join vengono realizzati sostituendo INNER JOIN con, rispettivamente: LEFT JOIN, RIGHT JOIN e FULL JOIN.. N.B. Il full-join non è supportato da Access. 4.3 Funzioni di aggregazione Sono funzioni che eseguono un’operazione sui valori contenuti in una singola colonna di una tabella e restituiscono un solo valore come risultato dell’operazione. 4.3.1 Conteggio (COUNT) Conta il numero di righe selezionate da un’interrogazione. 14

La condizione può essere formata da più condizioni semplici unite dagli operatori AND ed OR. Ad es.: SELECT corsi.codclasse, giorno, corsi.codmateria, orada, oraa, nome, cognome FROM (docenti INNER JOIN corsi ON docenti.iddocente = corsi.iddocente) INNER JOIN lezioni ON (corsi.codclasse = lezioni.codclasse) AND (corsi.codmateria = lezioni.codmateria) ORDER BY giorno, corsi.codclasse; 44

Può avere due formati: 1) SELECT COUNT(*) FROM nome-tabella WHERE condizione; conta tutte le righe che soddisfano la condizione, incluse quelle che hanno campi a null 2) SELECT COUNT(nome-campo) FROM nome-tabella WHERE condizione; conta le sole righe che non presentano il valore null nel campo specificato nella proiezione N.B. usare la clausola AS per assegnare un nome significativo alla colonna del risultato (che, altrimenti, verrebbe chiamata con un’intestazione del tipo count(*)). 4.3.2 Conteggio dei valori distinti (COUNT DISTINCT) Conta quanti valori distinti sono presenti nel campo, senza considerare le eventuali ripetizioni. ES.: proiettare le città di residenza degli impiegati che lavorano nei dipartimenti di “Chimica” SELECT COUNT(DISTINCT impiegati.città) as Città FROM impiegati INNER JOIN dipartimenti ON impiegati.codice = dipartimenti.codice WHERE descrizione = “Chimica”; N.B. In Access la COUNT DISTINCT non funziona. 4.3.3 Somma (SUM) Esegue la somma dei valori presenti nel campo specificato. Il campo deve essere numerico. Dalla somma vengono esclusi i valori null. L’argomento della funzione SUM può anche essere un’espressione numerica contenente i nomi di attributi di tipo numerico. ES.: selezionare lo stipendio complessivo percepito dagli impiegati che risiedono ad “ascoli piceno” SELECT SUM(stipendio) AS StipendioTotale FROM impiegati WHERE città = “ascoli piceno”; 4.3.4 Media dei valori (AVG) La funzione calcola la media aritmetica dei valori di una colonna, cioè la somma dei valori diviso il numero dei valori. Il campo deve essere numerico. Dalla somma vengono esclusi i valori null. L’argomento della funzione AVG può anche essere un’espressione numerica contenente i nomi di attributi di tipo numerico. 4.3.5 Valore minimo (MIN) e valore massimo (MAX) Restituiscono rispettivamente il valore minimo ed il valore massimo presente nella colonna specificata. Possono essere usate anche per campi di tipo alfanumerico. Le funzioni MIN e MAX ignorano i valori null. L’argomento può anche essere un’espressione anziché il nome di un attributo. Ad es.: SELECT MIN(cognome) as PrimoCognome, MAX(stipendio) as StipendioMassimo FROM impiegati; 4.4 Ordinamento del risultato (ORDER BY) Mostra le righe ottenute dall’interrogazione, ordinate secondo i valori contenuti in una o più colonne, tra quelle elencate nella lista-colonne da proiettare. L’ordinamento può essere, nell’ambito di ciascuna colonna:  ascendente (default) ASC  discendente DESC Il valore null compare:  all’inizio delle sequenze crescenti  alla fine delle sequenze decrescenti La clausola ORDER BY deve essere l’ultima di un comando SELECT (dopo anche la GROUP BY). Il formato della clausola è: … ORDER BY col1 *DESC+, col2 *DESC+, …., coln *DESC+; 4.5 Raggruppamento dei risultati per chiavi di ricerca (GROUP BY) Permette di raggruppare un insieme di righe aventi gli stessi valori nelle colonne indicate dalla clausola di raggruppamento. 45

Viene prodotta una riga di risultati per ogni insieme di valori delle colonne di raggruppamento (tale insieme di colonne viene chiamato argomento). Se nel comando SELECT viene inclusa una funzione di aggregazione (COUNT, SUM, AVG, MIN, MAX), per ciascuna riga della tabella risultante (cioè per ogni insieme distinto dei valori assunti dalle colonne dell’argomento), viene prodotto un valore di raggruppamento. I valori null vengono considerati ai fini della individuazione dei raggruppamenti del risultato, ma non vengono valutati da nessuna delle funzioni di aggregazione, ad eccezione della COUNT(*). Quando si utilizza la clausola GROUP BY, tutti gli attributi che vengono proiettati, devono:  o essere inclusi nella clausola GROUP BY (devono cioè far parte dell’argomento)  oppure essere delle funzioni di aggregazione ES proiettare i dipartimenti, con indicazione, per ciascuno di essi, di quanti sono gli impiegati e qual è il totale degli stipendi pagati: SELECT descrizione AS dipartimento, COUNT(matricola), SUM(stipendio) FROM dipartimenti INNER JOIN impiegati on dipartimenti.codice = impiegati.codice GROUP BY descrizione ORDER BY descrizione;15 Si fa notare che l’interrogazione non considera gli eventuali impiegati che non appartengono a nessun dipartimento. Per poterli includere occorre eseguire il RIGHT JOIN al posto dell’INNER JOIN: in tal caso verrebbe prodotta anche una riga relativa al dipartimento con descrizione = null. 4.6 Condizioni sui raggruppamenti (HAVING) La clausola HAVING permette di selezionare solo alcuni dei raggruppamenti ottenuti con la clausola GROUP BY  la clausola HAVING viene sempre usata con la clausola GROUP BY: dopo che GROUP BY ha formato i raggruppamenti di righe, HAVING visualizza le sole righe di raggruppamento che soddisfano le condizioni scritte nella HAVING, condizioni che, in genere, riguardano i valori restituiti dalle funzioni di aggregazioni COUNT, SUM, AVG, MIN, MAX. La clausola HAVING presenta caratteristiche analoghe alla clausola WHERE:  WHERE pone condizioni di selezione sulle singole righe  HAVING pone condizioni di selezione sui raggruppamenti ES selezionare il numero degli impiegati e lo stipendio medio per i soli dipartimenti con sede a “Roma” e con un numero di impiegati > 1: SELECT descrizione, count(matricola), avg(stipendio) FROM dipartimenti d inner join impiegati i on d.codice = i.codice WHERE dipartimenti.città = “Roma” GROUP BY descrizione HAVING count(matricola) > 1 ORDER BY descrizione; N.B. E’ possibile utilizzare le clausole GROUP BY ed HAVING, anche se non si proietta nessuna funzione di aggregazione. In tal caso i raggruppamenti vengono comunque formati e le condizioni HAVING applicate, ottenendo in uscita le righe formate dai soli argomenti, senza i valori corrispondenti. ES proiettare nome e cognome dei client con un ordinato complessivo maggiore di 20: select nome, cognome from (utenti inner join ordini on utenti.userid = ordini.userid) inner join righeordine on ordini.idordini = righeordine.idordini group by nome, cognome having sum(prezzo) >20; 4.7 Formato generalizzato del comando SELECT SELECT elenco-colonne-da-proiettare FROM elenco-tabelle-e-congiunzioni WHERE condizioni-sulle-righe-estratte GROUP BY colonne-da-considerare-nei-raggruppamenti 15

Invece che count(matricola) si sarebbe potuto scrivere count(*), in quanto contare sul campo PK matricola equivale a contare il n° di righe. 46

HAVING condizioni-sui-raggruppamenti ORDER BY ordinamenti-sulle-colonne-selezionate; Il DBMS esegue il comando elaborando le clausole nel seguente ordine: FROM (prodotto cartesiano)  WHERE  GROUP BY  HAVING  SELECT  ORDER BY 4.7.1 Regole per la scrittura del comando SELECT 1) Dopo la SELECT elencare le sole colonne, funzioni di aggregazione, espressioni che si vogliono riportare nelle colonne della tabella risultato, ridefinendo, con gli opportuni alias, le intestazioni delle colonne ed indicando la tabella di riferimento (o il relativo alias definito nella clausola FROM) per le colonne presenti su più tabelle con lo stesso nome 2) Riportare la clausola GROUP BY sempre e solo nel caso si abbiano delle funzioni di aggregazione. Se non si hanno funzioni di aggregazione, non utilizzare la clausola GROUP BY 3) Nelle clausole GROUP BY ed ORDER BY riportare solo colonne elencate dopo la SELECT. Nella clausola FROM si può invece far riferimento a qualsiasi colonna che è stata riportata dal motore SQL nella tabella temporanea ottenuta applicando la FROM (tutte le colonne della tabella riferita nella FROM o tutte le colonne di tutte le tabelle che vengono congiunte nella FROM). 4) Nella clausola HAVING, si possono imporre condizioni sia sulle funzioni di aggregazione, sia sulle colonne di raggruppamento 5) Nella clausola FROM non si possono imporre condizioni sulle funzioni di aggregazione (ed è quindi necessario ricorrere alle interrogazioni nidificate) 4.8 Esempio di SQL con INNER JOIN a cascata Si vuole trovare il nome e cognome del manager dell’impiegato “Rossi” “Mario”. Per fare ciò si deve ricorrere al seguente doppio JOIN, espresso in linguaggio relazionale:

(σcognome = “Rossi” and nome = “Mario” impiegati

dipartimenti) impiegati.codice = dipartimenti.codice

impiegati dipartimenti.manager = impiegati.matricola La query in linguaggio SQL diventa: SELECT i.cognome, i.nome FROM ( dipartimenti INNER JOIN impiegati ON dipartimenti.codice = impiegati.codice ) INNER JOIN impiegati i ON dipartimenti.manager = i.matricola WHERE impiegati.cognome = "Rossi" AND impiegati.nome = "Mario"

Si noti come, per evitare ambiguità dovute alla presenza ripetuta della tabella impiegati, la seconda ripetizione di impiegati (che è quella da cui vengono proiettati cognome e nome del manager) viene chiamata con l’alias i. 4.9 Condizioni di ricerca Le condizioni di ricerca sono utilizzate nelle clausole WHERE ed HAVING, rispettivamente per selezionare le righe ed i raggruppamenti. Nelle condizioni di ricerca possono essere utilizzati gli operatori di seguito elencati. 4.9.1 Operatori di confronto >, <, =, <>, >=, <= 4.9.2 Operatori logici AND, OR, NOT 4.9.3 BETWEEN Controlla se un campo numerico è compreso in un intervallo di valori, estremi inclusi: campo BETWEEN val1 AND val2 La selezione inversa (campo esterno all’intervallo specificato), si ottiene con: campo NOT BETWEEN val1 AND val2 ES.: selezionare tutti gli stipendi minori di 1000 o maggiori di 1500 SELECT stipendio FROM `impiegati` WHERE stipendio not between 1000 and 1500 47

4.9.4 IN Controlla se un valore è compreso nella lista valori specificata dopo la parola IN. Per indicare la condizione opposta (campo non appartenente all’insieme dei valori della lista) si scriverà NOT IN. ES.: selezionare cognome e nome degli impiegati residenti a “Roma” o “Napoli”: SELECT cognome, nome FROM impiegati WHERE città IN (“Roma”, “Napoli”); 4.9.5 LIKE Confronta il valore di una colonna con un modello di stringa che può contenere i metacaratteri (o caratteri jolly): Metacarattere Significato Metacarattere in MS Access _ (underline) un carattere qualsiasi in quella posizione nella stringa ? (punto interrogativo) % (percento) zero, uno o più caratteri in quella posizione della stringa * (asterisco) L’operatore LIKE utilizzato con un modello di stringa che non contiene i metacaratteri, è equivalente all’operatore “=”. Per indicare criteri di ricerca opposti si utilizza il costrutto NOT LIKE. Nel caso il metacarattere debba essere utilizzato come carattere di ricerca e non come un metacarattere, si deve far precedere da uno speciale carattere, detto carattere di ESCAPE. ES.: ricercare i dipartimenti che conengono il carattere “_” nella loro descrizione SELECT descrizione FROM dipartimenti WHERE descrizione LIKE “%$_%” ESCAPE “$”; 4.9.6 IS NULL Permette di selezionare le righe che presentano valori nulli in una colonna: WHERE colonna IS NULL Se, al contrario, si vogliono selezionare le righe che non presentano valori nulli in una colonna: WHERE colonna IS NOT NULL 4.10 Interrogazioni nidificate Le interrogazioni nidificate o sottoquery, permettono di inserire un comando SELECT all’interno della struttura di un altro comando SELECT, ponendo un’interrogazione all’interno di un’altra interrogazione  questo spiega la presenza del termine structured nella sigla SQL, per indicare un linguaggio che consente di costruire interrogazioni complesse e ben strutturate. Le interrogazioni nidificate si differenziano a seconda del  numero di valori che viene restituito dalla sottoquery  il tipo di condizione posta sui valori restituiti dalla sottoquery 4.10.1 Sottoquery che restituiscono un solo valore La sottoquery restituisce un solo valore che viene utilizzato in una operazione di confronto (<, >, etc.). ES.1: si vuole trovare il nome e cognome del manager dell’impiegato “Rossi” “Mario”. SELECT nome, cognome FROM impiegati WHERE matricola = (SELECT manager FROM dipartimenti WHERE codice = (SELECT codice FROM impiegati WHERE cognome = “Rossi” and nome = “Mario”)); ES.2: elencare nome e cognome dei dipendenti che hanno lo stipendio inferiore allo stipendio medio di tutti i dipendenti. SELECT nome, cognome FROM impiegati WHERE stipendio < (SELECT avg(stipendio) FROM impiegati); 4.10.2 Sottoquery che restituiscono più di un valore La sottoquery restituisce un elenco di valori che possono essere usati in quattro diversi predicati: Predicato Formato Descrizione IN where x IN (SELECT ….) Il predicato IN è vero se x appartiene all’elenco dei valori estratti dalla SELECT, mentre è falso se x non compare nell’elenco dei valori estratti ANY where x OP ANY (SELECT …) Il predicato ANY è vero se il confronto è vero per almeno uno dei valori in cui OP è un qualsiasi operatore dell’elenco, mentre è falso se il confronto è falso per tutti i valori di confronto (<, >, etc.) dell’elenco oppure se l’elenco restituito dalla sottoquery è vuoto (non contiene nessun valore). 48

ALL

where x OP ALL (SELECT ….) in cui OP è un qualsiasi operatore di confronto (<, >, etc.)

Il predicato ALL è vero se il confronto è vero per tutti i valori dell’elenco oppure se l’elenco restituito dalla sottoquery è vuoto (non contiene nessun valore), mentre è falso se il confronto è falso per almeno un valore dell’elenco EXISTS where EXISTS (SELECT …..) Il predicato EXISTS controlla se vengono restituite righe dalla sottoquery: la condizione di ricerca è vera se la SELECT nidificata restituisce una o più righe come risultato, è falsa se il risultato è un insieme vuoto . Il predicato EXISTS è il solo che non confronta un valore con uno o più altri valori. Le colonne selezionate nella sottoquery di una clausola EXISTS sono irrilevanti, quindi, per brevità, si può utilizzare la forma SELECT * nella sottoquery. I predicati IN ed EXISTS possono essere utilizzati nelle forme negate:  NOT IN  NOT EXISTS Ciò non vale per i predicati ANY ed ALL in quanto sono già l’uno il negato dell’altro. Inoltre, valgono le seguenti equivalenze tra i predicati:  il predicato IN (SELECT …) equivale a = ANY (SELECT ….)  il predicato NOT IN (SELECT …) equivale a <> ALL (SELECT ….)

4.10.2.1 Operando costituito da più colonne L’operando x che si confronta con la sottoquery deve essere costituito da una sola colonna. Non si può cioè scrivere una query nidificata del tipo: Select artisti.nome, artisti.cognome from artisti where nome, cognome not in (select artisti.nome,artisti.cognome from (artisti inner join opere on artisti.nome = opere.nome and artisti.cognome = opere.cognome) inner join musei on opere.museo = musei.codice where musei.città = 'roma') Nel caso l’operando sia costituito da due o più colonne si deve ricondurre ad un operando di un solo campo, usando la funzione di concatenazione concat. La query precedente si dovrà quindi scrivere: Select artisti.nome, artisti.cognome from artisti where concat(nome, cognome) not in (select concat(artisti.nome, artisti.cognome) from (artisti inner join opere on artisti.nome = opere.nome and artisti.cognome = opere.cognome) inner join musei on opere.museo = musei.codice where musei.città = 'roma')

4.10.2.2 Sottoquery composte tramite operatori logici AND e OR Le sottoquery possono essere unite a formare condizioni complesse, utilizzando gli operatori logici AND e OR. Ad es., si vogliono selezionare tutte le persone che sono anche genitori (padri o madri): SELECT nome FROM persone AS p WHERE exists (select * from paternità q where p.nome = q.padre) or exists (select * from maternità m where p.nome = m.madre); ESEMPI 1) Selezionare nome e cognome degli impiegati che sono anche manager SELECT nome, cognome FROM impiegati WHERE matricola = ANY (SELECT DISTINCT(manager) FROM dipartimenti); 49

oppure: SELECT cognome, nome FROM impiegati WHERE matricola IN (SELECT DISTINCT(manager) FROM dipartimenti); 2) Selezionare nome e cognome degli impiegati che non sono dei manager SELECT nome, cognome FROM impiegati WHERE matricola <> ALL (SELECT DISTINCT(manager) FROM dipartimenti); oppure: SELECT cognome, nome FROM impiegati WHERE matricola NOT IN (SELECT DISTINCT(manager) FROM dipartimenti); Si fa notare che l’interrogazione: SELECT nome, cognome FROM impiegati WHERE matricola <> ANY (SELECT DISTINCT(manager) FROM dipartimenti); non va bene, in quanto seleziona sempre tutti gli impiegati (perché una matricola è sempre diversa da un manager che non è lui stesso). 3) Selezionare nome, cognome e stipendio degli impiegati del dipartimento di “chimica” con lo stipendio superiore ad almeno un impiegato del dipartimento di “informatica”. SELECT cognome, nome, stipendio FROM dipartimenti d INNER JOIN impiegati i ON d.codice = i.codice WHERE d.descrizione = "chimica" AND stipendio > ANY(SELECT stipendio FROM dipartimenti d INNER JOIN impiegati i ON d.codice = i.codice WHERE d.descrizione = "informatica"); 4) Selezionare nome, cognome e stipendio degli impiegati del dipartimento di “chimica” con lo stipendio superiore a quello di tutti gli impiegati del dipartimento di “informatica”. SELECT cognome, nome, stipendio FROM dipartimenti d INNER JOIN impiegati i ON d.codice = i.codice WHERE d.descrizione = "chimica" AND stipendio > ALL (SELECT stipendio FROM dipartimenti d INNER JOIN impiegati i ON d.codice = i.codice WHERE d.descrizione = "informatica"); 5) Selezionare nome, cognome e scostamento dello stipendio16, degli impiegati del dipartimento di “informatica”, solo se esiste almeno un impiegato di quel dipartimento. SELECT nome, cognome, stipendio - (SELECT avg( stipendio ) FROM impiegati ) AS scostamento FROM dipartimenti d INNER JOIN impiegati i ON d.codice = i.codice WHERE descrizione = "informatica" AND EXISTS (SELECT * FROM dipartimenti d INNER JOIN impiegati i ON d.codice = i.codice WHERE descrizione = "informatica"); 6) Selezionare il nome, cognome e descrizione del dipartimento diretto, per i soli manager che dirigono più di un dipartimento. SELECT nome, cognome, descrizione FROM dipartimenti d INNER JOIN impiegati i ON d.manager = i.matricola WHERE manager IN (SELECT manager FROM dipartimenti GROUP BY manager HAVING count( * ) >1); 7) Dato il seguente schema relazionale: fiumi (fiume) province (provincia, regione) 16

Lo scostamento è dato dalla differenza tra lo stipendio dell’impiegato e lo stipendio medio di tutti gli impiegati presenti nel DB 50

attraversa (fiume, provincia) scrivere i comandi SELECT per rispondere alle seguenti interrogazioni:  Selezionare i fiumi, indicando per ciascuno il numero di regioni attraversate. SELECT a.fiume, count( DISTINCT ( regione) ) as n°regioni FROM attraversa a INNER JOIN province p ON a.provincia = p.provincia GROUP BY fiume ORDER BY n°regioni DESC;  Selezionare i fiumi che non attraversano la provincia di “roma”. SELECT fiume FROM fiumi WHERE fiume NOT IN (SELECT f.fiume FROM (fiumi f INNER JOIN attraversa a ON f.fiume = a.fiume) WHERE a.provincia = "roma") 4.10.3 Assegnazione di un nome alla tabella derivata dalla sottoquery In una query con sottoquery è possibile utilizzare la tabella derivata dalla sottoquery, richiamandola con un alias assegnato con la clausola AS. Ciò può tornare utile quando si vuole mettere in join una tabella del DB con la tabella derivata da una sottoquery. Ad es., si vogliono proiettare il codice classe e la descrizione della materia di tutti i corsi tenuti dal docente identificato dal codice 1: SELECT codclasse, materia FROM materie INNER JOIN (select codmateria, codclasse from corsi where iddocente = 1) as c ON materie.codmateria = c.codmateria; 4.11 Interrogazioni in cascata Ci sono interrogazioni che non possono essere risolte con un’unica SELECT comunque complessa e nidificata, ma richiedono più SELECT in cascata, ciascuna delle quali si appoggia sulla tabella creata dalla SELECT precedente. Ciò viene realizzato in ACCESS basando le query successive non su tabelle del DB ma sulla query precedente. ESEMPIO: si vuole visualizzare la descrizione ed il totale degli stipendi del dipartimento che paga il più alto valore di stipendi. A tale scopo si crea una prima query, di nome Vista totalizzatori dipartimenti che seleziona i dipartimenti e lo stipendio totale pagato da ciascun dipartimento: SELECT dipartimenti.codice, dipartimenti.descrizione, Sum(impiegati.stipendio) AS totstipendi FROM dipartimenti INNER JOIN impiegati ON dipartimenti.codice=impiegati.dipartimento GROUP BY dipartimenti.codice, dipartimenti.descrizione; Basandosi su tale query, si seleziona quindi la riga con il valore massimo del totale degli stipendi: SELECT [Vista totalizzatori dipartimenti].codice, [Vista totalizzatori dipartimenti].descrizione, [Vista totalizzatori dipartimenti].totstipendi FROM [Vista totalizzatori dipartimenti] WHERE ((([Vista totalizzatori dipartimenti].totstipendi)=(select max(totstipendi) from [vista totalizzatori dipartimenti]))); In MySql il modo di procedere è simile, con la differenza di creare una view col comando: CREATE VIEW Vista_totalizzatori_dipartimenti AS SELECT dipartimenti.codice, dipartimenti.descrizione, Sum(impiegati.stipendio) AS totstipendi FROM dipartimenti INNER JOIN impiegati ON dipartimenti.codice=impiegati.dipartimento GROUP BY dipartimenti.codice, dipartimenti.descrizione; 4.12 Operazioni insiemistiche tra tabelle Le operazioni insiemistiche tra due tabelle (UNION, INTERSECT, MINUS) sono consentite solo se le due tabelle hanno lo stesso numero di colonne e nello stesso ordine. Le colonne è bene che siano anche dello stesso tipo, anche se il motore SQL tenta comunque di eseguire l’operazione (ad es. se una colonna è di tipo decimal e la corrispondente sulla seconda tabella è di tipo integer, la UNION viene eseguita producendo una colonna di tipo decimal).

Nel caso i nomi di due colonne corrispondenti siano diversi, la colonna nella tabella risultato viene chiamata col nome della prima tabella. 4.12.1 Unione tra due tabelle (UNION) La sintassi dell’operazione è la seguente: (SELECT lista-colonne FROM tabella1) UNION (SELECT lista-colonne FROM tabella2) Ad es.: (SELECT * FROM mov_clifor) UNION (SELECT * FROM mov_banca) Le righe uguali nella tabella risultato vengono riportate una sola volta. Se si vogliono avere tutte le righe, compresi tutti i “doppioni” si dovrà usare il formato UNION ALL. 4.12.2 Intersezione tra due tabelle (INTERSECT) La sintassi è la seguente: (SELECT lista-colonne FROM tabella1) INTERSECT (SELECT lista-colonne FROM tabella2) Ad es., supponiamo di avere le seguenti due tabelle: Tabella Vendite_negozio nome_magazzino valore_vendite data Roma

1500 €

2000-01-05

Napoli

250 €

2000-01-07

Roma

300 €

2000-01-08

Milano

700 €

2000-01-08

Tabella Vendite_internet data

valore_vendite

2000-01-07 250 € 2000-01-10 535 € 2000-01-11 320 € 2000-01-12 750 € Si vogliono proiettare tutte le date in cui ci sono state sia vendite da negozio sia vendite internet. Il comando è il seguente: (SELECT data FROM Vendite_negozio) INTERSECT (SELECT data FROM Vendite_internet) Il risultato ottenuto è la tabella seguente: Data 2000-01-07 Lo stesso risultato lo si sarebbe potuto ottenere con il comando: SELECT distinct i.data FROM Vendite_internet i inner join Vendite_negozio v on i.data = v.data 4.12.3 Differenza tra due tabelle (MINUS) La sintassi è la seguente: (SELECT lista-colonne FROM tabella1) MINUS (SELECT lista-colonne FROM tabella2) Ad es., si abbiano le seguenti tre tabelle: Tabella Abbonati codice_abb nome

città

mario albano

roma

giulio cremona milano

franco pucci

roma

sergio sola

napoli 52

Tabella Riviste codice_riv descrizione r1

panorama

oggi

il tennis italiano

quattroruote

Tabella Abbonamenti codice_abb Codice_riv data

prezzo

2000-01-05 80

2000-01-07 120

2000-01-08 100

2000-01-08 90

Si vuole conoscere il codice e il nome degli abbonati che non hanno sottoscritto nessun abbonamento. Il comando è il seguente: (SELECT codice_abb, nome FROM abbonati) MINUS (SELECT codice_abb, nome FROM abbonamenti) La tabella risultato è la seguente: codice_abb nome a4

sergio sola

Lo stesso risultato lo si sarebbe potuto ottenere con il comando: SELECT codice_abb FROM abbonati WHERE codice_abb NOT IN (SELECT cod-abb FROM 4.13 Funzioni SQL avanzate Gli argomenti seguenti descrivono alcune utili funzioni: oltre alle funzioni carattere e matematiche, sono comprese alcune funzioni per data e ora. Si sottolinea che tutte le funzioni SQL hanno una caratteristica in comune: restituiscono un singolo valore, quindi sono utili in numerose dichiarazioni SQL, tra cui la proiezione delle colonne e la clausola WHERE. 4.13.1 Funzioni carattere Le funzioni carattere operano su dati carattere. Tutte le funzioni riportate sono supportate dall’RDBMS MySql. a) REPLACE La funzione REPLACE cerca una stringa di caratteri e sostituisce i caratteri trovati nella stringa di ricerca con i caratteri elencati in un stringa di sostituzione. Di seguito la sintassi generale: REPLACE(stinga_caratteri, stringa_ricerca, stringa_sostituzione)

in cui: � stringa _ caratteri: è la stringa in cui effettuare la ricerca e molto spesso è un nome di colonna di tabella (di tipo stringa e non di tipo numero), ma può essere qualsiasi espressione che generi una stringa di caratteri; � stringa _ ricerca: è la stringa di uno o più caratteri da trovare in stringa_ caratteri; � stringa _ sostituzione: è la stringa che sostituisce tutte le occorrenze di stringa_ricerca trovate in stringa _ caratteri. Di seguito un esempio che sostituisce con il carattere sottolineatura (_) tutti gli spazi trovati nel titolo di un libro: select titolo, replace(titolo, ' ', '_') as nuovo_titolo from libri La tabella risultato è: 53

titolo nuovo_titolo il viaggio dell'elefante il_viaggio_dell'elefante le mie prigioni le_mie_prigioni amico robot amico_robot la solitudine dei numeri primi la_solitudine_dei_numeri_primi b) LTRIM La funzione LTRIM elimina gli spazi iniziali (a sinistra) in una stringa di caratteri. Si noti che vengono eliminati solo gli spazi iniziali: gli spazi interni e gli spazi finali vengono lasciati nella stringa. Ad esempio: LTRIM (‘ Stringa con spazi iniziali e finali restituisce questa stringa: ‘Stringa con spazi iniziali e finali ‘

‘)

c) RTRIM La funzione RTRIM funziona come LTRIM, ma elimina gli spazi finali. Se è necessario eliminare gli spazi iniziali e finali, è possibile annidare LTRIM ed RTRIM, come di seguito: RTRIM(LTRIM (‘ Stringa con spazi iniziali e finali ‘)) restituisce questa stringa: ‘Stringa con spazi iniziali e finali’

d) Funzione di valore null (IFNULL) La funzione sostituisce i valori null con un valore selezionato. Ad es.: SELECT titolo, IFNULL(collana, 'No collana') AS collana from libri restituisce la tabella seguente: titolo collana il viaggio dell'elefante I grandissimi le mie prigioni Grandi eroi amico robot No collana la solitudine dei numeri primi No collana 4.13.2 Funzioni matematiche Come si può immaginare dal nome, le funzioni matematiche restituiscono il risultato di un’operazione matematica e solitamente richiedono come parametro di input un’espressione numerica, che può essere un valore letterale, un valore numerico di colonna di tabella o qualsiasi espressione (compreso l’output di un’altra funzione) che generi un valore numerico. a) SIGN La funzione SIGN esamina un’espressione numerica e restituisce uno dei seguenti valori, in base al segno del numero in input: Valore restituito Significato -1 Il numero in input è negativo 0 Il numero in input è zero 1 Il numero in input è positivo null Il numero in input è null Ad es.: SELECT titolo, sconto, sign(sconto) from libri restituisce la seguente tabella: titolo sconto sign(sconto) il viaggio dell'elefante null null le mie prigioni 50.0 1 amico robot 25.0 1 la solitudine dei numeri primi 20.0 1 b) SQRT La funzione SQRT prende una singola espressione numerica e restituisce la sua radice quadrata. La sintassi generale è la seguente: SQRT (espressione_numerica)

Il risultato non ha alcun significato, ma a scopo illustrativo si seleziona la radice quadrata dello sconto dei libri: Select sconto, sqrt(sconto) as “radquad sconto” from libri La tabella ottenuta è la seguente: sconto radquad sconto null null 50.0 7.07106781186548 25.0 5 20.0 4.47213595499958 c) CEILING (CEIL) La funzione CEILING restituisce l’intero minimo, maggiore o uguale al valore dell’espressione numerica fornita come parametro di input. In altre parole, arrotonda al numero intero superiore. d) FLOOR La funzione FLOOR è l’opposto logico della funzione CEILING: restituisce l’intero minore o uguale al valore dell’espressione numerica fornita come parametro di input. In altre parole, tronca al numero intero inferiore. Ad es., il comando: select prezzo, ceil(prezzo) as arrotonda, floor(prezzo) as tronca from libri where prezzo = 7.75 restituisce la tabella: prezzo arrotonda tronca 7.75 8 7 4.13.3 Funzioni di data e ora MySQL ha oltre 30 funzioni di data e ora. Le funzioni più utilizzate sono le seguenti:  ADDDATE: somma due espressioni di data o data/ora, generando una nuova data  ADDTIME: somma due espressioni di ora, generando una nuova ora  CURDATE: restituisce la data corrente, nel formato AAAA-MM-GG. Ad es.: Select curdate() as data_di_oggi restituisce la tabella: data_di_oggi 2010-12-27  

    

  

DATE: restituisce la parte di data di un’espressione di data/ora DATEDIFF: restituisce il numero di giorni tra due date. Ad es, l’istruzione: select data, curdate() as "data di oggi", datediff(curdate(), data) as "differenza giorni" from ordini restituisce la seguente tabella: data data di oggi differenza giorni 2010-07-28 2010-12-27 152 DAYNAME: restituisce il nome inglese del giorno della settimana contenuto in una data DAYOFMONTH: restituisce il giorno del mese, nell’intervallo tra 1 e 31 DAYOFWEEK: restituisce un indice numerico per il giorno della settimana contenuto in una data (1 per domenica, 2 per lunedì etc.) DAYOFYEAR: restituisce il progressivo nell’anno del giorno contenuto in una data, con un intervallo tra 1 e 366 LAST_DAY: restituisce una data, modificando il giorno nell’ultimo giorno del mese. Ad es., il comando: select data, last_day(data) as ultimo_giorno from ordini restituisce la seguente tabella: Data Ultimo_giorno 2010-07-28 2010-07-31 MONTH: restituisce il mese contenuto in una data, con un intervallo tra 1 e 12 MONTHNAME: restituisce il nome inglese del mese contenuto in una data NOW: restituisce la data e l’ora corrente. Ad es., il comando: select now() as “data e ora corrente” restituisce la seguente tabella: 55

  

data e ora corrente 2010-12-27 14:22:30 TIME: restituisce la parte oraria di una data/ora TIMEDIFF: restituisce la differenza oraria tra due parametri data/ora o espressioni di ora WEEKOFYEAR: restituisce la settimana dell’anno, nell’intervallo tra 1 e 54

4.14 L’espressione CASE nel comando Select L’espressione CASE è un’aggiunta recente ma importante allo standard SQL: per la prima volta, parti di dichiarazioni SQL possono essere eseguite in modo condizionale. Per esempio, una colonna nei risultati di query può essere formattata in base ai valori contenuti in un’altra colonna. L’espressione CASE ammette due forme generali:  Espressione CASE semplice  Espressione CASE cercata 4.14.1 Espressione CASE semplice CASE espressione_input WHEN espressione_confronto THEN espressione_risultato [WHEN espressione_confronto THEN espressione_risultato ...] [ELSE espressione_risultato] END in cui:



ogni condizione WHEN viene valutata come espressione _ input = espressione_ confronto, se il risultato è un TRUE (vero) logico, viene restituita espressione _ risultato e non viene valutata alcuna altra condizione WHEN;  se nessuna delle condizioni WHEN viene valutata come TRUE (vero), ed esiste una condizione ELSE, viene restituita espressione _ risultato associata alla condizione ELSE;  se nessuna delle condizioni WHEN viene valutata come TRUE (vero), e non esiste una condizione ELSE, viene restituito un valore null. Un’applicazione tipica del costrutto CASE è nella decodifica del valore di un campo come, ad es., nel comando seguente in cui si vuole decodificare lo stato di un ordine: select idordine, stato, case stato when "e" then "emesso" when "c" then "consegnato" when "p" then "in preparazione" end as "decodifica stato" from ordini Il risultato è dato dalla seguente tabella: idordini stato decodifica stato 1 e emesso 2 p in preparazione 3 c consegnato 4.14.2 Espressione CASE cercata La cosiddetta espressione CASE cercata consente di avere condizioni di confronto più flessibili, perché ciascuna di esse è scritta come condizione completa, compreso l’operatore di confronto. Di seguito la sintassi generale: CASE WHEN condizione THEN espressione_risultato [WHEN condizione THEN espressione_risultato ...] [ELSE espressione_risultato] END in cui:

 

ciascuna condizione può essere qualsiasi espressione SQL che dia come risultato TRUE o FALSE; ogni condizione WHEN viene valutata in sequenza, se una di esse viene calcolata come TRUE (vero), viene restituita espressione _ risultato associata e non viene valutata alcuna altra condizione WHEN; 56

ď&#x201A;ˇ

se nessuna delle condizioni WHEN viene valutata come TRUE (vero), ed esiste una condizione ELSE, viene restituita espressione _ risultato associata alla condizione ELSE; ď&#x201A;ˇ se nessuna delle condizioni WHEN viene valutata come TRUE (vero), e non esiste una condizione ELSE, viene restituito un valore null. Ad es., lâ&#x20AC;&#x2122;istruzione: SELECT titolo, anno, CASE when anno < 1900 then "antica" when anno >= 1900 and anno < 2000 then "moderna" when anno >= 2000 then "contemporanea" end AS letteratura FROM libri ORDER BY anno restituisce la seguente tabella: titolo le mie prigioni amico robot la solitudine dei numeri primi il viaggio dell'elefante

anno 1859 1969 2007 2008

letteratura antica moderna contemporanea contemporanea

Modulo 5 – Le caratteristiche dell’RDMS Relational DataBase Management System Unità Didattiche 1. Generalità 2. Caratteristiche generali di un RDBMS

1. GENERALITĂ&#x20AC; 1.1 Definizione di Data Base (DB) Collezione di archivi di dati strutturati, in modo che possano costituire una base di lavoro per utenti con programmi diversi. Nel caso gli archivi sono strutturati sotto forma di tabelle, si parla di Data Base Relazionali (RDB â&#x20AC;&#x201C; Relational Data Base). Gli archivi possono risiedere su un unico computer (server) oppure essere distribuiti sulle memorie di massa di computer diversi, facenti parte di una rete aziendale, i cui nodi possono essere anche fisicamente distanti: in questo caso si parla di database distribuiti. 1.2 Definizione di Data Base Management System (DBMS) Software di gestione delle basi di dati, che si colloca tra i programmi applicativi e gli archivi e si occupa di gestire i dati, inserendoli, aggiornandoli ed andandoli a prelevare (su richiesta dei programmi stessi) dal DB. Nel caso di basi dati relazionali, il software di gestione prende il nome di RDBMS (Relational Data Base Management System). 1.3 Schema di funzionamento di un rdbms

Utenti

Comandi manuali

Interfacce grafiche Fornite dallâ&#x20AC;&#x2122;RDBMS Programmi applicativi

DDL (Create, Alter, Drop)

DML (Insert, Update, Delete)

QL (Select)

DCL (Grant, Revoke, Create View)

RDBMS

Sistema Operativo (File System)

Bae Dati

2. CARATTERISTICHE GENERALI DI UN RDBMS 2.1 Facilità e velocità di accesso Il ritrovamento delle informazioni è facilitato dal fatto che è sufficiente specificare che cosa si vuole trovare e non come lo si deve cercare, e viene svolto con grande velocità, anche nel caso di DB molto grandi e con richieste provenienti da più utenti in contemporanea 2.2 Indipendenza dalla struttura logica dei dati I programmi sono indipendenti dalla struttura logica con cui i dati sono organizzati negli archivi è possibile apportare modifiche alla definizione delle strutture della base dati (aggiungere una tabella, aggiungere un campo, modificare la lunghezza di un campo etc.) senza modificare i programmi esistenti 2.3 Indipendenza dalla struttura fisica dei dati E’ possibile modificare i supporti su cui i dati sono registrati e le modalità di accesso alla memoria di massa (ad es. spostando il DB su un altro server o cambiando il tipo di dischi utilizzato) senza dover modificare le applicazioni 2.4 Eliminazione della ridondanza Per ridondanza si intende la duplicazione dello stesso dato (ad es. l’indirizzo del cliente) su più archivi diversi con conseguenti problemi di incoerenza dell’informazione (che potrebbe essere diversa da archivio ad archivio) e di complessità dell’aggiornamento (che deve aggiornare l’informazione su molti archivi). Gli stessi dati non sono ripetuti su archivi diversi, in quanto il DB è costituito da archivi integrati di dati  tutti i dati sono disponibili per ciascun programma 2.5 Integrità dei dati Integrità dei dati significa garantire che le operazioni effettuate sul DB da utenti autorizzati non provochino una perdita di consistenza (disallineamento) tra i dati rendendo i dati stessi poco significativi. L’integrità di un DB presenta due aspetti: l’integrità logica e quella fisica. 2.6 Integrità logica Consiste nella necessità di preservare la struttura logica del DB, ovvero le relazioni esistenti tra i dati. Il DBMS possiede dei meccanismi per impedire che l’inserimento di nuovi dati o la cancellazione di quelli esistenti alteri la congruenza dei dati. I meccanismi utilizzati sono quelli dell’integrità di dominio, dell’integrità referenziale, delle chiavi primarie, degli indici unique, dei trigger etc.: Tipo integrità Significato Modalità d’implementazione Di dominio I valori contenuti in ciascuna colonna devono essere compatibili tipo-dato del comando CREATE con il dominio di definizione della colonna stessa (ad es. INTEGER) I valori che una colonna assume possono appartenere ad un  in MySql tramite il tipo-dato ENUM (“val1”, “val2”, …..) insieme discreto di valori  in ORACLE tramite la clausola CHECK Univocità delle Ogni riga di una tabella deve essere unica PRIMARY KEY del comando righe di una CREATE tabella Univocità dei Non è possibile memorizzare lo stesso valore di una colonna per UNIQUE del comando CREATE valori di una righe diverse oppure CREATE UNIQUE INDEX colonna Obbligatorietà Non è possibile omettere il valore di una colonna NOT NULL del valore di una colonna Referenziale Il valore presente nella colonna della tabella figlia (child), deve FOREIGN KEY del comando esistere come primary key o come unique key della tabella padre CREATE (parent). Se la tabella padre coincide con la tabella figlia, si parla di integrità autoreferenziale. Derivante dalle I valori ammissibili e le operazioni consentite, sono derivate dalle Trigger e Stored-procedure regole di business regole seguite nell’ambiente in cui l’applicazione deve operare 60

(regole di business). Ad es.: impedire gli aggiornamenti di una tabella al di fuori del normale orario di lavoro; impedire che lo stipendio di un impiegato possa essere diminuito; registrare l’ora e l’autore dell’ultima variazione di una riga; inserire automaticamente in una mailing list i nuovi assunti, all’atto del loro inserimento in anagrafica. 2.6.1 trigger Un trigger è un oggetto del database identificato da un nome e associato con una tabella, che viene attivato quando sulla tabella si verifica un certo evento. Nel trigger è possibile eseguire specifici comandi (ad esempio una procedura personalizzata) nel momento in cui vengono aggiornati (comando UPDATE), inseriti (comando INSERT) oppure eliminati (comando DELETE) i dati della tabella associata al trigger. I trigger possono essere utilizzati per:  Eseguire il controllo dei valori che vengono inseriti nella tabella. Ad es., si possono mantenere i valori di un determinato campo sempre all'interno di un intervallo di valori (esempio [0, 100]). Ogni volta che quel campo viene aggiornato si attiva un trigger che controlla il nuovo valore e, nel caso in cui esca dal range accettato lo sostituisce con un valore di default. In questo modo ci si trova sempre con un database efficiente e con dei dati "accettabili" senza dover replicare i controlli dal linguaggio di programmazione per tutte le query eseguite.  Eseguire dei calcoli a partire dai valori coinvolti nell’aggiornamento. Un trigger è formato dalle seguenti parti:  il nome  il momento in cui il trigger si attiva e che può essere: o before, cioè prima di che venga eseguita l’operazione sulla tabella su cui il trigger agisce o after, cioè dopo che viene eseguita l’operazione sulla tabella su cui il trigger agisce  l’evento che attiva il trigger, cioè il tipo di operazione eseguita sulla tabella, che può essere: o insert o update o delete  la tabella su cui il trigger agisce  il corpo del trigger, cioè i comandi che devono essere eseguiti quando il trigger viene attivato. Se vengono eseguiti più comandi, devono essere racchiusi tra i delimitatori BEGIN … END. Un esempio di trigger per il RDBMS MySql è il seguente: CREATE TRIGGER upd_check BEFORE UPDATE ON account FOR EACH ROW BEGIN IF NEW.importo < 0 THEN SET NEW.importo = 0; ELSEIF NEW.importo > 100 THEN SET NEW.importo = 100; END IF; END; Questo codice si attiva prima di ogni update sulla tabella account: su ognuna delle righe da modificare viene controllato il valore che sta per essere assegnato al campo amount, per verificare che sia compreso fra 0 e 100; in caso contrario viene riportato entro tali limiti. Come potete vedere, quindi, attraverso il trigger siamo in grado di modificare il valore che sta per essere aggiornato sulla tabella. Il qualificatore NEW indica proprio che il nome di colonna che stiamo utilizzando si riferisce al nuovo valore della riga che sta per essere aggiornata. NEW si può utilizzare in caso di INSERT e UPDATE. Analogamente è disponibile il qualificatore OLD che fa riferimento ai valori precedenti la modifica, e si può utilizzare in caso di UPDATE e DELETE. La modifica attraverso l'istruzione SET è possibile solo per i valori NEW e solo nei trigger di tipo BEFORE. Un altro esempio di trigger è quello che consente di tenere aggiornato il saldo di un conto corrente, quando viene inserito un nuovo movimento su quel conto. Le tabelle interessate sono: CONTOCOR (codconto, codcliente, saldo) MOVIMENTI (codmov, data, importo, codconto) FK (CONTOCOR)

Il trigger è il seguente: CREATE TRIGGER agg_saldo AFTER INSERT ON movimenti FOR EACH ROW BEGIN UPDATE CONTOCOR SET saldo = saldo + new.importo WHERE contocor.codconto = new.codconto; END; 2.7 Integrità fisica Consiste nel preservare la congruenza dei dati presenti nel DB, a seguito di malfunzionamenti nei programmi di aggiornamento. In questo caso il DBMS deve garantire che le operazioni sui dati richieste dagli utenti siano eseguite fino al loro completamento per assicurare la consistenza dei dati. Un’operazione logica che interessa più tabelle, deve essere o 17 tutta confermata o tutta annullata . Si pensi ad es. ad una vendita di un prodotto che comporta lo scarico dalla tabella giacenze del prodotto e la creazione di un nuovo documento nella tabella fatture oppure ad una operazione di bonifico bancario che trasferisce un importo da un conto ad un altro, che deve essere considerata conclusa solo quando entrambi i conti sono stati movimentati. A tale scopo il DBMS mette a disposizione un meccanismo di consolidamento delle operazioni effettuate basate sull’uso del comando COMMIT, eseguendo automaticamente il ripristino della situazione consolidata all’ultimo commit, in caso di malfunzionamenti durante gli aggiornamenti (azione indicata con il nome di ROLLBACK). 2.8 Controllo della concorrenza degli accessi Il DBMS garantisce che le operazioni svolte da utenti diversi in modo concorrente non interferiscano una con l’altra. Il meccanismo utilizzato è quello del lock: su ogni riga di una tabella è presente un bit che viene impostato ad 1 quando un utente deve modificare un campo della riga (write-lock); tutti gli altri utenti che richiedono di accedere alla stessa riga, rimangono in attesa finché questo bit non viene azzerato nel momento in cui l’utente termina il suo aggiornamento. Il lock, anziché sulla singola riga, può essere piazzato a livello di intera tabella o di tutto il DB. Inoltre esistono anche dei lock in lettura (read-lock) che consentono a più utenti di accedere in lettura ad una riga o ad un’intera tabella, impedendo ad altri utenti di cambiare i dati mentre qualche altro utente li sta leggendo. Ricapitolando:  un programma di aggiornamento della riga rimane in attesa se è impostato il wite-lock (che vuol dire che qualcun altro sta aggiornando la riga) oppure se è impostato il read-lock (che vuol dire che qualcun altro sta leggendo la riga)  un programma di lettura della riga rimane in attesa se è impostato il wite-lock (che vuol dire che qualcun altro sta aggiornando la riga) mentre non è influenzato dal read-lock in quanto più programmi possono leggere contemporaneamente la stessa riga. 2.9 Sicurezza dei dati Con il termine sicurezza si intende sia la riservatezza (detta anche privatezza o, in inglese, privacy) delle informazioni (gli utenti dei dati sono solo le persone autorizzate a farlo, nei limiti loro concessi), sia la difesa contro la perdita accidentale dei dati per effetti di malfunzionamenti dell’hw e del sw o di interventi dolosi. 2.10 Riservatezza dei dati Il DBMS controlla gli accessi attraverso delle regole, definite dall’amministratore del sistema, che associano a ciascun utente determinati permessi di lettura o modifica sulle diverse categorie di dati. Ad es., solo alcuni utenti di un’azienda possono modificare gli stipendi del personale e solo certi utenti possono vedere e stampare i cedolini delle retribuzioni. a) Comandi DCL (Data Control Language) per la riservatezza dei dati Il comando GRANT concede i privilegi specificando il tipo azione permesso, le tabelle su cui è consentito l’accesso e l’elenco degli utenti ai quali è consentito accedere: 17

In informatica, una transazione è una sequenza di operazioni, che può concludersi con un successo o un insuccesso; in caso di successo, il risultato delle operazioni deve essere reso permanente sul database, mentre in caso di insuccesso si deve tornare allo stato precedente all'inizio della transazione. 62

GRANT [SELECT [(colonna1, colonna2, ……)], INSERT, UPDATE [(colonna1, colonna2, ……)], DELETE, ALTER, ALL] ON nome-tabella1, nome-tabella2, …… TO [nome-utente1, nome-utente2, …] | PUBLIC; in cui la clausola PUBLIC, se presente, estende i privilegi a tutti gli utenti della base dati. Esempi di comandi GRANT GRANT UPDATE ON impiegati TO “user1”,” user2”; GRANT SELECT, INSERT, UPDATE, DELETE ON Cinema.* TO 'cinema_admin' IDENTIFIED BY 'admin_pass'; In questo secondo esempio, Cinema è il nome del database e Cinema.* indica tutte le tabelle del database Cinema. Inoltre all’utente “cinema_admin” oltre ai privilegi di accesso viene assegnata anche la password di accesso “admin_pass”. Al momento della connessione al database, viene specificato l’utente che si collega (ed eventualmente la password). L’RDBMS permette all’utente collegato di eseguire le sole operazioni e sulle sole tabelle per cui possiede i privilegi. La revoca dei permessi, con l’annullamento dei diritti concessi viene effettuato con il commando REVOKE che ha una sintassi analoga a quella del commando GRANT: REVOKE SELECT [(colonna1, colonna2, ……)] INSERT UPDATE [(colonna1, colonna2, ……)] DELETE ALTER ALL] ON nome-tabella1, nome-tabella2, …… TO [nome-utente1, nome-utente2, …] | PUBLIC; b) Riservatezza tramite viste dei dati Un secondo meccanismo per garantire la riservatezza dei dati è di definire dei sottoschemi (chiamati viste o views), cioè delle visioni parziali del DB che consentono ai singoli utenti di accedere ai soli dati che gli sono necessari, nascondendogli il resto dei dati contenuti nel DB. Le viste, dette anche tabelle virtuali, sono finestre dinamiche sulle tabelle del DB, poiché ogni modifica dei dati sulla tabella reale si riflette sulla vista e viceversa. Le viste, una volta create, possono essere interrogate come le tabelle. Di fatto, l’utente non deve sapere di utilizzare una vista in luogo di una tabella reale. Le viste offrono numerosi vantaggi:  nascondere colonne a un utente che non ha necessità di vederle o non deve vederle;  nascondere righe a un utente che non ha necessità di vederle o non deve vederle;  nascondere operazioni complesse, quali le unioni;  migliorare le prestazioni della query (in alcuni RDBMS, per esempio Microsoft SQL Server). Per creare una vista si ricorre al comando SELECT all’interno del comando CREATE VIEW: CREATE VIEW nome-vista (col1, col2, …., colN) AS SELECT elenco-colonne FROM elenco-tabelle-e-congiunzioni 63

WHERE condizioni-sulle-righe-estratte GROUP BY colonne-da-considerare-nei-raggruppamenti HAVING condizioni-sui-raggruppamenti ORDER BY ordinamenti-sulle-colonne-selezionate; La vista, una volta creata, può essere utilizzata in altri comandi SELECT. Ad es., supponiamo di avere una tabella con i prodotti di un punto vendita: PRODOTTI (codpro, descrizione, prezzo_acquisto, prezzo_vendita, quantità) Una vista molto utile per il responsabile degli approvvigionamenti è quella che consente di selezionare i prodotti che devono essere riordinati perché sono terminati: CREATE VIEW daordinare AS SELECT * FROM prodotti WHERE quantità = 0; Come secondo esempio consideriamo i dipendenti di un’azienda suddivisa in dipartimenti. Una volta creata la vista con i dati di sintesi di ogni dipartimento: CREATE VIEW sintesidipartimenti (dipart, numimpiegati, totstipendi) AS SELECT (coddip, count(matricola), sum(stipendio) FROM impiegati GROUP BY coddip; è possibile trovare il dipartimento che spende di più in stipendi: SELECT dipart FROM sintesidipartimenti WHERE totstipendi = (SELECT MAX(totstipendi) FROM sintesidipartimenti); o il numero medio di dipendenti per dipartimento: SELECT AVG(numimpiegati) as [n°medio impiegati] from sintesidipartimenti; Come terzo esempio, consideriamo un negozio che debba mantenere strettamente confidenziali le informazioni per le imposte (n° di iscrizione INPS e paghe dei dipendenti), mentre deve rendere disponibili alle applicazioni web alcune informazioni dei dipendenti, quali il nome del dipendente e quello del suo responsabile (se esiste). In tali casi è necessaria una vista che possa essere utilizzata da tali applicazioni Web, senza che chi accede ai dati possa rivelare accidentalmente informazioni confidenziali. La tabella con i dati dei dipendenti del negozio è la seguente: dipendenti(id_inps, nome, cognome, stipendio, num_figli, ritenute, id_responsabile) Il comando per la creazione della vista per le applicazioni Web è il seguente: CREATE VIEW personale AS SELECT a.nome, a.cognome, b.nome as “nome del responsabile, b.cognome as “cognome del responsabile” FROM dipendenti a LEFT JOIN dipendenti b ON a.id_responsabile = b.id_inps Una volta create la vista, è possible scrivere le query su di essa come si fa per le tabelle. Ad es. una semplice query sulla vista è la seguente: SELECT cognome as “cognome dipendente”, “cognome del responsabile” FROM personale ORDER BY cognome E’ possibile basare una vista non su una tabella ma su un’altra vista. Per eliminare una vista si usa il comando: DROP VIEW nome-vista; Sulle viste è possibile usare i comandi GRANT e REVOKE.

2.11 Protezione contro la perdita accidentale dei dati (persistenza dei dati) In caso di perdita accidentale dei dati18, è previsto un meccanismo per il recupero dei dati preesistenti, in modo da riportare la base dati in uno stato consistente. A tale scopo, è necessario effettuare delle copie di salvataggio (backup) del DB su dispositivi quali nastri o dischi, da eseguirsi periodicamente, in momenti in cui il DB stesso è interdetto alle normali operazioni degli utenti (generalmente nelle ore notturne). Il DBMS registra in uno o più file di sistema, chiamati journal, tutti gli aggiornamenti effettuati sul DB successivamente all’ultimo backup. In caso di danneggiamento del DB in linea, ad es. per un guasto del disco su cui è memorizzato, è sempre possibile fare in modo che il DBMS ricostruisca la situazione del DB eseguendo il ripristino (restart) dell’ultima situazione di backup e ripassando tutti gli aggiornamenti presenti nei journal files.

Essendo costituiti anche da parti meccaniche in movimento, i dispositivi di memoria di massa contenenti gli archivi del DB relazionale possono essere soggetti a guasti o a errori di scrittura dovuti a improvvisi sbalzi o perdite di tensione, con conseguente perdita di tutti o parte dei dati. 65

Modulo 6 – L’analisi delle funzioni Unità Didattiche 1. Individuare le funzioni: il modello gerarchico funzionale 2. Descrivere le funzioni: lo schema delle risorse di sistema

1. INDIVIDUARE LE FUNZIONI: IL MODELLO GERARCHICO-FUNZIONALE Oltre ai dati, l’altra componente di un’applicazione informatica è costituita dalle funzioni. L’attività di determinazione delle funzioni consiste nel decidere quali siano le cose che l’applicazione deve fare. La definizione delle funzioni viene svolta nelle prime due fasi della progettazione dell’applicazione:  Nella fase di definizione dei requisiti si individuano le funzioni più importanti che andranno sviluppate  Nella successiva fase di analisi ogni funzione viene scomposta e dettagliata sino al livello minimo necessario per definire e documentare tutte le componenti della funzione. Quest’attività si chiama scomposizione gerarchica funzionale. 1.1 Scomposizione gerarchica funzionale La scomposizione gerarchica delle funzioni consiste nel disegnare una struttura ad albero rovesciato, chiamata DGFDiagramma Gerarchico Funzionale (in inglese FHD-Functional Hierarchical Diagram), in cui:  Ogni nodo contiene la descrizione sintetica di una funzione  Le funzioni che rappresentano attività complesse (macrofunzioni) vengono suddivise in funzioni di dettaglio che indicano con maggiore precisione le operazioni da compiere per realizzare la macrofuzione.  Le funzioni che vengono scomposte in sottofunzioni sono dette funzioni madre e le sottofunzioni prendono il nome di funzioni figlie  Una funzione che non ha funzioni figlie è indicata col nome di foglia  La funzione che compare a livello più alto dell’albero gerarchico è detta funzione radice e contiene il nome dell’applicazione. La radice ovviamente non ha funzioni madre. 1.2 Regole per la definizione del FHD  Descrizione funzione = verbo indicante l’operazione + oggetto su cui operare + eventuali altre specificazioni. Esempi di verbi sono: definire, organizzare, scegliere, aggiornare, inserire; esempi di oggetti su cui operare sono: contenuti, relazioni, testi, immagini.  Una funzione madre  almeno due funzioni figlie (sottofunzioni)  Una funzione madre  non più di sei funzioni figlie. Nel caso in cui le funzioni figlie siano superiori a sei, si inserisce un nuovo livello di scomposizione.  Il risultato ottenuto dall’insieme delle funzioni figlie è descritto compiutamente dalla funzione madre  le funzioni figlie sono tutte quelle che servono a realizzare la funzione madre (nessuna può essere tolta). Ciò garantisce che tra le funzioni figlie non è presente alcuna funzione che serve per realizzare un’attività diversa da quella che la funzione madre deve svolgere  le funzioni figlie sono tutte quelle necessarie (non serve inserirne delle altre). Ciò garantisce che la funzione madre è stata scomposta in tutti i suoi dettagli e che nessuna sottofunzione è stata dimenticata.  Non esiste nessuna relazione tra le funzioni figlie dello stesso livello.  la disposizione delle funzioni figlie non indica che debbano essere eseguite in quell’ordine (da destra verso sinistra o dall’alto verso il basso)  alcune funzioni figlie possono essere in alternativa tra loro.  non è importante se una funzione figlia è opzionale (ad es. l’emissione della fattura che può o meno essere richiesta) oppure quante volte può essere eseguita. 1.3 Esempi FHD Nella pagina successiva sono riportati due esempi di costruzione del diagramma gerarchico funzionale di due applicazioni informatiche gestionali. Gli esempi sono puramente indicativi e non comprendono tutte le funzioni e tutti i livelli di dettaglio che le due applicazioni otrebbero richiedere.

2.2.1

FHD Applicazione Vendita Libri On-Line

Vendita libri

Aggiornare carrello

Visionare catalogo libri

Gestire cliente

Formalizzare acquisto

Gestire catalogo

Ordinare carrello

Visualizzare carrello

Ricerca libro

Visualizzare libro

Registrare cliente

Acquisire parametri

Aggiungere a carrello

Rimuovere da carrello

Svuotare carrello Scegliere libro

Pagare merce

Confermare ordine

Pagare con bonifico

Inviare conferma

Pagare con carta di credito

Emettere fattura

Aggiornare cliente Pagare con ccp Cancellare cliente

Gestirereparti

2.2.2

Gestire tipologie

Inserire reparto

Inserire tipologia

Aggiornare reparto

Aggiornare tipologia

Cancellare reparto

Cancellare tipologia

Gestire libri

Inserire libro

Aggiornare libro

Cancellare libro

FHD Prestazioni Servizio Sanitario Nazionale

Prestazioni specialistiche SSN

Censire visite specialistiche

Censire medici

Inserire visita specialistica

Gestire visite specialistiche

Aggiornare visita specialistica Censire pazienti

Prenotare visite

Condurre visita

Inserire medico Inserire dati visita Aggiornare medico

Assegnare medici Stampare referto

Pagare visita

Inserire orario visita

Cancellare medico

Controllare disponibilitĂ

Aggiornare dati visita

Aggiornare orario visita

Aggiornare medici visita

Inserire paziente

Aggiornare paziente

Inserire prenotazione

Cancellare paziente

Cancellare prenotazione

Aggiornare prenotazione

2 SCHEMA DELLE RISORSE DI SISTEMA Una volta definiti i dati e le funzioni, si può passare alla definizione di come i dati vengono utilizzati nelle funzioni: si tratta cioè di abbinare a ciascuna funzione, quali dati prende in input e quali dati produce in output. A tale scopo si utilizza lo schema delle risorse di sistema (system resources chart) che è un modello grafico che illustra, per una funzione, gli archivi d’interesse ed il flusso delle informazioni (input, output o input/output). I simboli grafici più usati negli schemi delle risorse di sistema sono:

tastiera

stampante

funzione

archivio su disco

videoterminale archivio su nastro

2.1 Esempio 1: scarico degli articoli dal magazzino

scarico

CodArticolo, Quantità Bolla di scarico

Articoli

Articoli in sottoscorta

2.2 Esempio 2: prelievo bancomat Numero tessera

Cifra richiesta

Conti correnti

Bancomat

Codice segreto

Denaro

Ricevuta

2.3 Esempio 3: prenotazione volo destinazione

Prenotazione volo orario

numero passeggeri

Biglietto

Voli

2.4 Esempio 4: composizione ordini di un utente registrato Utenti

Ordini

Righe ordini

Libri

Ordini di un utente

Userid, password

Elenco ordini

2.5 Esempio 5: prestiti di una biblioteca

CodISBN, MatrStudente

Scheda prestito

Prestiti in corso

Elenco prestiti in essere

DataRiferimento

Prestito

Libri

Restituzione

CodISBN, MatrStudente

DataScadenza

Elenco prestiti scaduti

Prestiti scaduti

2.6 Esempio 6: Vendita libri on-line - Visualizzazione catalogo libri Con accesso libero, la pagina utile a visualizzare i Reparti in cui è diviso il catalogo dei libri (Narrativa italiana, Narrativa straniera, Gialli, Fantascienza etc.) e, scelto il reparto, la pagina che visualizza il numero di libri presenti nel catalogo per ciascuna tipologia (“Da non perdere”, “I più venduti”, etc.), con i relativi dettagli. tipologie reparti

Reparti.php Scelta reparto

cod_reparto

Totale_libri.php Elenco numero libri per tipologia

libri

tipologia

reparto

Libreria on-line: Tuttilibri Elenco Reparti Codice Descrizione Scelta ----------------------------------------------------------------------

cod_reparto, cod_tipologia Libreria on-line: Tuttilibri Reparto xxxxxxxx Tipologia N°Libri Scelta -------------------------------------------

Libri.php Elenco libri di un reparto / tipologia

Libreria on-line: Tuttilibri Libri del Reparto xxxxxxxx Tipologia yyyyyy Titolo Autore Prezzo Disponibilità --------- --------- -------- ------------------------ --------- -------- ------------------------ --------- -------- ------------------------ --------- -------- ----------------

libri

Pagina Reparti.php Comando SQL: SELECT * FROM reparti ORDER BY codreparto; Pagina Reparti.php Comando SQL: SELECT tipologie.codtipo, count(codisbn) as N°libri FROM tipologie INNER JOIN libri on tipologie.tipo = libri.tipo WHERE libri.codreparto = “reparto scelto” GROUP BY tipologie.tipo ORDER BY tipologie.tipo; Pagina libri.php Comando SQL: SELECT titolo, autore, prezzo FROM libri WHERE codreparto = “reparto scelto” AND codtipo = “tipo scelto” ORDER BY titolo;

2.7 Esempio 7: Vendita libri on-line – Ordini di un utente Con accesso riservato agli utenti registrati, scrivere la funzione che mostri la composizione degli ordini in corso.

Utente.html Accettazione utente

userid, password

utenti

Accettazione.php Controllo utente / password

Libreria on-line: Tuttilibri Userid, password

ATTENZIONE! Userid o password errata Riprovare

userid

Libreria on-line: Tuttilibri

libri

Collegati per vedere gli ordini Userid: _______________ Password: ____________

Ordini.php Ordini dell’utente

ordini

Righe_ordini

Libreria on-line: Tuttilibri Cliente: Mario Draghi Userid: Drgmrxxx Situazione Ordini Numero DataEmiss TitoloLibro Quantità Prezzo(€) Sconto 75 11/01/11 Il bue 1 12 45% Il verde prato 1 30,5 14% 24

06/12/09

Il buon vino L’ipnotista

1 2

49 70

50%