Sviluppo App

Lo sviluppo delle applicazioni informatiche L’analisi dei dati , l’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.

2011

Enrico Angeloni ITIS L. Trafelli - Nettuno

11/04/2011

2

Indice

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

1 Le fasi del progetto software ............................................................................................................................... 8

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

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

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

1.2.1 Prodotto del progetto informatico .......................................................................................................... 8

1.3 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

2 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

2. I modelli per la progettazione dei dati ................................................................................................................22

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

b) Modello logico ...............................................................................................................................................22

c) 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

3

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 Database relazionale .................................................................................................................................29

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

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

g) Associazione n:n ............................................................................................................................................30

3. 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

4. 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

5. 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 – Il modello fisico: il linguaggio SQL.............................................................................................................38

1. Generalità ..........................................................................................................................................................39

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

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

2. DDL (Data Definition Language) – 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

4

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

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

3. 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

4. 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

5

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

2. 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

6

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

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

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

2 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 – Ordini di un utente ............................................................................72

7

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

8

1 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

9

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.

Una delle metodologie più seguite è quella chiamata metodologia a cascata 1(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:

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

1 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.

Definizione dei requisiti

Analisi

Progettazione di dettaglio

Transizione

Realizzazione

Documentazione Collaudo Formazione

Esercizio

10

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 o in particolare conoscere gli archivi informatizzati già presenti per evitare le duplicazioni dei dati3 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).

11

o Dati o Funzioni o 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 gli accorgimenti che vengono comunemente presi

6:

4 L’insieme degli aspetti riguardanti l’interazione unomo-macchina viene chiamato ergonomia informatica. 5 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.

12

o 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 rilascio

Pianificare tutti gli aspetti necessari per la partenza della nuova applicazione. In particolare 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

formazione

La formazione comprende:

- 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


Migrazione dei dati

Con il termine di migrazione dei dati s’intende il trasferimento nei nuovi archivi 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.


installazione

Oltre alle funzioni e agli archivi, la nuova applicazione ha bisogno della infrastruttura

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

13

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

7 Per il data entry conviene utilizzare per quanto possibile i programmi di immissione dei dati previsti nell’applicazione,

senza necessità di svilupparne altri.

14

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.

E. 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.

F. 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à

15

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.

L. Prodotti

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

16

2 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 91268, 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)

8 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”.

17

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

consegnati nei tempi

previsti

Programmi consegnati entro il

termine stabilito di fine progetto,

rispetto al totale dei programmi

da realizzare

Numero 99 %

Rilievi sui documenti

di progetto

Rilievi segnalati su Specifiche

Funzionali, Manuale Utente,

Manuale di gestione, Piano dei

test

Numero Max. 1 per ogni

10 pagine di

documento

Recidività dei rilievi

sullo stesso

documento

Rilievi recidivi su uno stesso

documento

Numero 0

Aderenza Rispetto degli

standard

Rilievi riguardanti il rispetto degli

standard

Numero 0

Affidabilità Maturità Difettosità in

collaudo

Difetti emersi in fase di collaudo Numero <=5 ogni 100

programmi

collaudati

Capacità di controllo

dei difetti del codice

in collaudo

Interventi di ripristino in collaudo

sullo stesso modulo per lo stesso

malfunzionamento

Numero Max. 1

Tempestività di

ripristino durante il

collaudo

Tempo entro cui ripristinare l’uso

di un programma afflitto da

malfunzionamenti

Giorni Max. 3 gg.

lavorativi

Usabilità Operabilità Frequenza

d’immissione di un

dato

N° di volte in cui uno stesso dato

di input deve essere fornito

dall’utente

Numero 1

Apprendimento Giorni di

addestramento

Intervallo di tempo in cui viene

completato l’addestramento

degli utenti

Giorni Min. 2

Max. 5

Efficienza Velocità Durata elaborazioni

interattive

Tempo che intercorre tra la

richiesta e la risposta (response

time)

Secondi Max. 5 sec.

Durata elaborazioni

batch

Tempo che intercorre tra la

richiesta e la risposta (response

time)

Secondi Max. 3600 sec.

Risorse

utilizzate

Ram Memoria centrale richiesta Gigagabyte Max. 4

Disco Memoria su disco richiesta Gigabyte Max. 500

Portabilità Installabilità Tempi installazione Giorni di lavoro per installare il

prodotto in un nuovo ambiente

operativo

Giorni /

persona

5

18

Modulo 2 - L’analisi dei dati

Unità Didattiche

1. Il disegni (analisi dei dati)

2. I modelli per la progettazione dei dati

19

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 Obbligatorio

Numero Numero della c. d’i. alfanumerico 9 si

Comune Comune che rilascia la carta alfanumerico 40 si

Cognome alfanumerico 30 si

20

Nome alfanumerico 30 si

Data nascita data gg/mm/aaaa si

N° atto N° registrazione atto di nascita numerico 10 si

Comune nascita alfanumerico 40 si

Cittadinanza alfanumerico 40 si

Comune residenza alfanumerico 40 si

Provincia residenza alfanumerico 2 si

Via alfanumerico 40 si

Stato civile alfanumerico 20 si

Professione alfanumerico 20 si

Statura numerico 3 si

Capelli alfanumerico 20 si

Occhi alfanumerico 20 si

Segni particolari alfanumerico 100 no

Foto immagine si

Data rilascio data gg/mm/aaaa si

Data scadenza data gg/mm/aaaa si

Fase di progettazione – Definizione degli archivi

NOME ARCHIVIO Carta Identità

ORGANIZZAZIONE Indicizzato sul campo NumCdi

Nome campo Tipo Lunghezza Obbligatorietà Note

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 presente solo se CodStato = “IT”

ComuneNascita carattere 40 si ricavato automaticamente da

ComuniItaliani se CodStato = “IT”,

fornito da input per gli altri stati

Via carattere 40 si

CodStatoCivile carattere 1 si

Professione carattere 20 si

Statura decimale 1,2 si

Capelli carattere 20 si

Occhi carattere 20 si

Segni particolari carattere 160 no

Foto immagine si

DataRilascio data (ggmmaaaa) 8 si

DataScadenza data (ggmmaaaa) 8 si Calcolata automaticamente da DataRilascio

NOME ARCHIVIO StatiEsteri

ORGANIZZAZIONE Indicizzato sul campo CodStato


CodStato carattere 2 si

Stato carattere 40 si

Nazionalità carattere 40 si

NOME ARCHIVIO ComuniItaliani

ORGANIZZAZIONE Indicizzato sul campo CodComune

21


CodComune intero 4 si

Comune carattere 40 si

CodProvincia carattere 2 si

NOME ARCHIVIO ProvinceItaliane

ORGANIZZAZIONE Indicizzato sul campo CodProvincia


CodProvincia carattere 2 si

Provincia carattere 40 si

NOME ARCHIVIO StatoCivile

ORGANIZZAZIONE Indicizzato sul campo CodStatoCivile


CodStatoCivile carattere 1 si

StatoCivile carattere 40 si

22

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

2.1.3 Associazione Definizione Legame che stabilisce un’interazione tra due (o più) entità.

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

software.

Nome entità

23

PERSONA AUTOMOBILE

possedere

Rappresentazione

possedere

2.1.3.1 Grado di un’associazione N° delle entità che partecipano ad un’associazione.

Grado associazione Descrizione Esempio

1 Associazione ricorsiva: un’unica entità in

associazione con se stessa 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

2 Associazione tra due entità

3 o più 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

collaboratore figlio

due o più associazioni tra le stesse due entità:

insegnante

insegnare

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. Docente coordinare Classe

Studente conseguire Diploma

Associazione 1:N (uno a molti) o semplice Es. ContoCorrente effettuare Movimento

Arbitro arbitrare Partita

Calciatore giocare Squadra

persona automobile

P1

P2

P3

A1

A2

A3

Impiegato Persona

Docente Classe

24

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.

Associazione N:N (molti a molti) o complessa Es. 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

è valutato in N N

Num_matricola voto Sigla

Cognome Descrizione

Nome

Subisce riguarda

1 N N 1

Num_matricola voto sigla

Cognome descrizione

Nome

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 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

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)

Fornitore Prodotto

PRODOTTO CLIENTE

codiceProdotto

descrizione

prezzoListino

codiceFiscale

nome

cognome

indirizzo

DataAcquisto

QuantitàAcquistata

ScontoPraticato

Studente Materia

Studente Materia Valutazione

25

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

descrizione N N cognome

1 IS A

1 1

Attestato (si/no)

N

tiene

tipo (recupero, sostegno etc.) 1

partita_iva

ragione_sociale

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

Corso

Interno Esterno

Studente

Organizzazione

26

Altri esempi di gerarchia IS A sono i seguenti:

Esempio 1: Acquisti

acquista Cod_cliente

N N

1

IS A

1 1

Cod_fiscale

nome

cognome

Partita_iva

Ragione_sociale

Esempio 2: Agenzia Turistica

avviene con costo_biglietto

N 1 ore_durata

IS A

compagnia stazione_partenza porto_partenza

num_volo stazione arrivo porto_arrivo

aeroporto_partenza

aeroporto arrivo

Prodotto Cliente

Società Persona

Viaggio Mezzo

Treno Aereo Nave

27

Modulo 3 - Il modello logico relazionale

Unità Didattiche

1. Gli oggetti del modello relazionale

2. Passaggio dal modello concettuale E/R al modello logico relazionale

3. La normalizzazione delle relazioni

4. Le operazioni relazionali

5. Le operazioni insiemistiche

28

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 n-

ple 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

cardinalità (t)

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

1.2.2 Rappresentazione

La tabella viene rappresentata tramite il suo schema:

AUTOMOBILI (Costruttore, Modello, segmento, Porte, N° posti)

a11 a21 an1

a12 a22 an2

…….. …….. …………. ………..

a1t a2t …………. ant

Costruttore Modello Segmento Porte N° posti

Fiat Panda B 5 4

Ford Focus C 3/5 5

Mercedes A C 3/5 5

Fiat Panda B 3 4

Citroen C3 C 5 5

Citroen C2 B 3 4

Fiat Punto B 3/5 5

CARDINALITÀ

(n° righe)

GRADO (n° colonne)

29

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

30

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) Attributo dell’entità colonna della tabella c) Caratteristiche attributo dell’entità caratteristiche attributo della tabella d) Chiave primaria (identificatore) dell’entità chiave primaria della tabella e) 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 è nata 1

risiede

N 1

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.

Persona

Comune

31

Esempio 1

insegna n sigla classe

n codice materia

codice docente 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

valuta codice materia

N N

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:

segue

matricola cod_corso

nome N N 1 titolo

cognome IS A tiene

tipo 1 1

N N

attestato 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.

docente classe

studente

materia

corso

interno esterno organizzatore

studente

32

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.

33

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:

34

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)

35

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


Π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


R 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


Ra1, a2,.. Sc1, c2, …

oppure con:

R S a1 = c1 and a2 = c2 ……

36

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.


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).


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

R S = σ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”.

37

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 = R U 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)

38

Modulo 4 – Il modello fisico: il linguaggio SQL

Unità Didattiche

1. Generalità

2. DDL – Definizione struttura tabelle

3. DML – Manipolazione dei dati delle tabelle

4. QL – Interrogazione dei dati delle tabelle

39

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 Descrizione

DDL Definire la struttura delle tabelle

DML Modificare i dati contenuti nelle tabelle con le operazioni di:

inserimento, variazione e cancellazione

QL (SELECT) Porre interrogazioni al data base

DCL Garantire la riservatezza dei dati (concessione dei privilegi di

accesso agli utenti)

40

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 Tipo equivalente in Access

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

INTEGER o INT Numero intero con precisione 10 (10 cifre)

DEC(p, s) o

DECIMAL(p,s)

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).

NUMERIC

DATE Data nella forma GG/MM/AAAA In MySql il formato è:

AAAA-MM-GG

TIME Ora nella forma HH:MM:SS

2.1.2 Esempio di creazione tabelle

n comprende 1

1 N

dirige

12 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.

impiegati dipartimenti

matricola

nome

cognome

citta

stipendio

codice

descrizione

indirizzo

citta

41

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;

13

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.

42

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);

43

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;

44

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;

45

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.

46

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.

47

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

48

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.


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 …)

in cui OP è un qualsiasi operatore

di confronto (<, >, etc.)

Il predicato ANY è vero se il confronto è vero per almeno uno dei valori

dell’elenco, mentre è falso se il confronto è falso per tutti i valori

dell’elenco oppure se l’elenco restituito dalla sottoquery è vuoto (non

contiene nessun valore).

49

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);

50

oppure:


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:


WHERE matricola NOT IN (SELECT DISTINCT(manager) FROM dipartimenti);

Si fa notare che l’interrogazione:


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


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


WHERE d.descrizione = "chimica"

AND stipendio > ALL (SELECT stipendio


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


WHERE descrizione = "informatica"

AND EXISTS (SELECT *


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

51

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).

52

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à

a1 mario albano roma

a2 giulio cremona milano

a3 franco pucci roma

a4 sergio sola napoli

53

Tabella Riviste

codice_riv descrizione

r1 panorama

r2 oggi

r3 il tennis italiano

r4 quattroruote

Tabella Abbonamenti

codice_abb Codice_riv data prezzo

a1 r2 2000-01-05 80

a1 r4 2000-01-07 120

a3 r3 2000-01-08 100

a2 r3 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 è:

54

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)

55

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


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


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”


56

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;

57

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.

Ad es., l’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


titolo anno letteratura

le mie prigioni 1859 antica

amico robot 1969 moderna

la solitudine dei numeri primi 2007 contemporanea

il viaggio dell'elefante 2008 contemporanea

58

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

Unità Didattiche

1. Generalità

2. Caratteristiche generali di un RDBMS

59

1. GENERALITÀ

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 – 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

Bae Dati

RDBMS

Sistema Operativo

(File System)

DDL

(Create, Alter, Drop)

DML

(Insert, Update, Delete)

QL

(Select)

Comandi manuali

Programmi applicativi

Interfacce grafiche

Fornite dall’RDBMS

Utenti

DCL

(Grant, Revoke, Create View)

60

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

con il dominio di definizione della colonna stessa

tipo-dato del comando CREATE

(ad es. INTEGER)

I valori che una colonna assume possono appartenere ad un

insieme discreto di valori

in MySql tramite il tipo-dato ENUM (“val1”, “val2”, …..)

in ORACLE tramite la clausola CHECK

Univocità delle

righe di una

tabella

Ogni riga di una tabella deve essere unica PRIMARY KEY del comando

CREATE

Univocità dei

valori di una

colonna

Non è possibile memorizzare lo stesso valore di una colonna per

righe diverse

UNIQUE del comando CREATE

oppure CREATE UNIQUE INDEX

Obbligatorietà

del valore di una

colonna

Non è possibile omettere il valore di una colonna NOT NULL

Referenziale Il valore presente nella colonna della tabella figlia (child), deve

esistere come primary key o come unique key della tabella padre

(parent). Se la tabella padre coincide con la tabella figlia, si parla

di integrità autoreferenziale.

FOREIGN KEY del comando

CREATE

Derivante dalle

regole di business

I valori ammissibili e le operazioni consentite, sono derivate dalle

regole seguite nell’ambiente in cui l’applicazione deve operare

Trigger e Stored-procedure

61

(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)

62

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

tutta confermata o tutta annullata17. 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.

63

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

64

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.

65

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.

18 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.

66

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

67

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 DGF-

Diagramma 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.

68

2.2.1 FHD Applicazione Vendita Libri On-Line

Vendita libri

Formalizzare

acquisto

Aggiornare

carrello

Aggiungere a

carrello

Rimuovere da

carrello

Ricerca libro

Svuotare carrello

Pagare merce

Confermare ordine

Ordinare carrello

Pagare con

bonifico

Pagare con ccp

Pagare con carta

di credito

Visionare catalogo

libri

Visualizzare libro

Visualizzare

carrello

Acquisire

parametri

Scegliere libro

Gestire

cliente

Registrare cliente

Login cliente

Cancellare cliente

Aggiornare cliente

Inviare conferma

Emettere fattura

Gestire catalogo

Gestire tipologie

Gestire libri

Gestirereparti

Inserire reparto

Aggiornare reparto

Cancellare reparto

Inserire tipologia

Aggiornare

tipologia

Cancellare

tipologia

Aggiornare libro

Cancellare libro

Inserire libro

2.2.2 FHD Prestazioni Servizio Sanitario Nazionale

Prestazioni

specialistiche SSN

Gestire visite

specialistiche

Censire visite

specialistiche

Censire medici

Prenotare visite

Controllare

disponibilità

Censire pazienti

Aggiornare

prenotazione

Cancellare

prenotazione

Inserire

prenotazione

Condurre visita

Stampare referto

Pagare visita

Inserire visita

specialistica

Aggiornare visita

specialistica

Inserire dati

visita

Inserire orario

visita

Assegnare

medici

Aggiornare

medico

Inserire medico

Cancellare

medico

Aggiornare dati

visita

Aggiornare

orario visita

Inserire paziente

Cancellare

paziente

Aggiornare

paziente

Aggiornare

medici

visita

69

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

2.2 Esempio 2: prelievo bancomat

2.3 Esempio 3: prenotazione volo

scarico CodArticolo,

Quantità Articoli

Bolla di

scarico

Articoli in

sottoscorta

Bancomat

Numero

tessera

Codice

segreto

Cifra richiesta

Conti

correnti

Denaro Ricevuta

Prenotazione

volo

Voli

destinazione

orario

numero

passeggeri

Biglietto

70

2.4 Esempio 4: composizione ordini di un utente registrato

2.5 Esempio 5: prestiti di una biblioteca

Prestito

Libri

Restituzione

CodISBN,

MatrStudente

CodISBN,

MatrStudente

Elenco prestiti

scaduti

Prestiti scadutiElenco prestiti in

essere

DataScadenza

Prestiti in corso

Scheda prestito

DataRiferimento

Utenti Ordini Righe

ordini Libri

Ordini di un utente

Elenco

ordini

Userid,

password

71

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.

cod_reparto

cod_reparto, cod_tipologia

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;

Reparti.php

Scelta reparto

Totale_libri.php

Elenco numero

libri per tipologia

Libreria on-line: Tuttilibri

Elenco Reparti

Codice Descrizione Scelta

--------- ----------------

--------- ----------------

--------- ----------------

reparti

reparto

tipologie

libri

Libri.php

Elenco libri di un

reparto / tipologia


Libri del Reparto xxxxxxxx Tipologia yyyyyy

Titolo Autore Prezzo Disponibilità

--------- --------- -------- ----------------

--------- --------- -------- ----------------

--------- --------- -------- ----------------

--------- --------- -------- ----------------

tipologia


Reparto xxxxxxxx

Tipologia N°Libri Scelta

--------- -------

--------- -------

--------- -------

libri

72

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.

userid, password

userid

Utente.html

Accettazione

utente

Accettazione.php

Controllo utente /

password

Ordini.php

Ordini

dell’utente

utenti

Userid,

password


Collegati per vedere gli ordini

Userid: _______________

Password: ____________


ATTENZIONE!

Userid o password errata

Riprovare

libri

ordini

Righe_ordini


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 1 49

L’ipnotista 2 70 50%

Sviluppo App

Documents

Transcript of Sviluppo App