Files e DataBases per la gestione aziendale

Databases (relazionali)
Cos’è un database
I dati sono utili se li sappiamo organizzare in insiemi omogenei e
strutturati detti file. Un file contiene un certo numero di record,
analoghi alle righe di un elenco scritto su un foglio di carta. Ciascun
record rispetta un tracciato record, unico per tutti i record del file, che
è la sequenza dei campi che costituiscono il record stesso, ciascuno con le
sue caratteristiche. Un file è omogeneo, cioè tutti i suoi record si
riferiscono a uno stesso “oggetto”. Il file LIBRI, ad es., conterrà
solamente dati che si riferiscono a un insieme di libri; il file
CONTRIBUENTI conterrà solo dati che si riferiscono a un insieme di
contribuenti; il file FORNITORI conterrà solo dati che si riferiscono a un
insieme di fornitori, e così via. Un file traduce in ambito informatico ciò
che nella realtà si chiama entità. Un’entità è una parte della realtà che
possiede una sua specificità e autonomia. Un’entità può essere un
insieme di studenti, un’altra un insieme di contribuenti, un’altra un insieme
di articoli di cancelleria, un’altra un insieme di abitazioni, un’altra un
insieme di terreni, un’altra un insieme di fatture di acquisto, un’altra un
insieme di fatture di vendita, un’altra ancora un insieme di animali in una
fattoria, e così via. Ogni entità può essere opportunamente memorizzata
in un file, decidendo quali caratteristiche comuni è opportuno considerare
per ciascun esemplare appartenente all’entità stessa; queste
caratteristiche comuni a tutti gli esponenti dell’entità danno luogo al
tracciato record, a cui si dovranno uniformare tutti i componenti
dell’entità per poter essere degnamente rappresentati dal file che
traduce in forma digitale e stabilmente memorizzata l’entità stessa.
Consideriamo ora una qualsiasi forma di lavoro organizzato: un’azienda di
produzione, un negozio, uno studio professionale, un Comune, un'USL, una
cooperativa, e così via. Ciascuna situazione di lavoro organizzato contiene
in sé numerose entità. Consideriamo ad es. un caso da manuale: un’azienda
di produzione. In essa esistono sicuramente le entità clienti, fornitori,
1
dipendenti, terzisti, agenti di commercio, consiglio di amministrazione,
materie prime, semilavorati, prodotti finiti, disegni tecnici, macchinari,
automezzi, statistiche di vendita, statistiche di acquisto, fatture di
vendita, fatture di acquisto, magazzini, costi, spedizioni, ordini dei
clienti, ordini ai fornitori, ordini ai terzisti, rapporti con le banche,
rapporti con le assicurazioni, rapporti con la pubblica amministrazione,
e altre. Ciascuna di queste entità, per poter essere gestita
opportunamente, deve essere descritta da uno o più file. Le entità
costituenti di un’azienda devono interagire, non possono procedere
ciascuna per proprio conto, devono essere collegate e interfacciate le
une con le altre. Ciò significa che anche i file che descrivono e
conservano al passare del tempo i dati relativi alle varie entità devono
interagire tra loro, devono essere collegati e interfacciati gli uni con
gli altri.
L’insieme di tutti i file aziendali, che contengono e mantengono
stabilmente i dati relativi a tutte le entità aziendali, e l’insieme di tutti i
collegamenti tra i file stessi, costituiscono un database.
Così come un file è un insieme di dati omogenei e organizzati, in modo
analogo un database è un insieme di file collegati e coordinati, in grado
di gestire con precisione, tempestività, flessibilità, completezza, l’intera
attività di lavoro organizzato di cui è espressione e memoria.
Il sistema dei file, cioé il database aziendale, permette di coordinare e di
far collaborare al meglio le quattro M che, per consolidata e motivata
tradizione, esprimono sinteticamente l'essenza di ogni attività di lavoro
organizzato: Money, Men, Machines, Management.
Modello relazionale di database (relational database)
Cos’è, in pratica, un database relazionale? Un database relazionale è un
insieme di file, tra loro collegati per mezzo di relazioni 1 a N o,
meno frequentemente, 1 a 1. L’insieme dei file che si riuniscono e si
collegano per dar vita al database relazionale permette di custodire e di
gestire tutti i dati relativi a una situazione di lavoro organizzato o, più in
generale, a un sistema, cioè a un insieme di parti tra loro collegate in
modo da ottenere un insieme complessivo funzionante e con
2
caratteristiche globali di qualità e complessità più elevate di quelle dei
singoli moduli componenti.
DB e DBMS
E’ necessario a questo punto dedicare un breve spazio a due sigle molto
importanti e tra loro correlate: DB e DBMS. DB è l’acronimo per
DataBase (letteralmente base di dati). DBMS è l’acronimo per DataBase
Management System (letteralmente sistema di gestione della base di
dati). Che differenza c’è tra le due sigle? Enorme, anche se si riferiscono
allo stesso contesto. DB indica i dati, organizzati, contenuti nel
database. DBMS indica i programmi, tra loro coordinati, che servono a
gestire al meglio i dati, cioè il DB. Attenzione, però: nel DBMS non sono
contenuti i programmi applicativi, cioè quelli che i programmatori scrivono
su misura di ogni specifica realtà aziendale, e che risentono di tale realtà
specifica (ad es. i programmi applicativi per gestire un’azienda conciaria
sono ben diversi da quelli che servono a gestire un’azienda di mobili); del
DBMS fanno parte i programmi che servono a gestire le caratteristiche
generali del database, e in particolare servono a garantirne l’integrità e la
consistenza. Se, in particolare, stiamo considerando dei database
relazionali, come avviene oggi nella grande maggioranza dei casi, le due
sigle appena viste diventano RDB (Relational DataBase) e RDBMS
(Relational DataBase Management System). Gli RDBMS sono prodotti
software di grande complessità, e vengono realizzati e commercializzati
solo da alcune grandi aziende a livello mondiale; le principali tra esse sono
(tra parentesi il nome dell’RDBMS): Oracle (Oracle), IBM (DB2 e IDS,
che sta per Informix Dynamic Server), Microsoft (Access ed
SQL/Server), Sybase (Sybase Adaptive Server Enterprise). Esistono
però anche degli RDBMS free, cioè liberamente scaricabili da Internet e
utilizzabili; tra essi ricordiamo MySQL (dal 2010 di proprietà della
Oracle), PostgreSQL, MariaDB, DB2 Express – C (è la versione gratuita
di DB2 dell’IBM). Gli RDBMS gratuiti più importanti e diffusi sono
MySQL e PostgreSQL.
3
Logo di MySQL
Logo di PostgreSQL
Logo di MariaDB
4
Schema e sottoschemi
L’insieme dei file che costituiscono il database relazionale, ciascuno
descritto in tutte le sue caratteristiche e con le sue relazioni, si chiama
schema (in inglese schema o scheme) del database relazionale stesso.
Lo schema viene poi reso disponibile agli utenti non tutto intero, ma
suddiviso in sottoschemi. Cos’è un sottoschema (subschema in inglese)? E’
un sottoinsieme dei file dell’intero schema, e delle relazioni tra di essi.
A ciascun utente, secondo il suo ruolo e le sue mansioni all’interno di una
certa realtà di lavoro organizzato, viene affidato un sottoschema. Più
utenti diversi possono avere lo stesso sottoschema (ad es. due impiegati
che si occupano entrambi dell’emissione degli ordini ai fornitori, oppure
due o tre magazzinieri. ecc.), così come un unico utente potrebbe dover
gestire più di un sottoschema, se ciascuno di tali sottoschemi non è
sufficiente a occupare l’intero tempo di lavoro del dipendente. Bisogna
capire bene cosa sono i sottoschemi: in realtà lo schema non viene
suddiviso, rimane unico anche in presenza di numerosi sottoschemi; è solo
che a ciascun utente si fa vedere, si dà la possibilità (e l’obbligo!) di agire
solamente su una parte ristretta dell’intero database, quella che gli
compete. Questo fatto di far vedere a un utente solo una parte dello
schema viene espresso anche nell’altro modo con cui si indica un
sottoschema, cioè vista (view in inglese). Sottoschema e vista sono
sinonimi, anche se, a mio parere, sottoschema rende meglio l’idea.
In realtà, un utente non riceve in gestione solo il sottoschema in senso
stretto come l’abbiamo definito poco sopra, ma gli viene affidato un kit
più ampio. Oltre al sottoschema vero e proprio, gli viene data anche
un’applicazione, e un insieme di permessi e divieti sul sottoschema.
Cos’è un’appplicazione?
E’ un insieme di programmi collegati, coordinati e integrati tra loro, che
permettono di gestire al meglio l’attività di un ufficio o di un reparto. E’
logico e necessario che a un utente non assegniamo solo i dati (il
sottoschema), ma anche i programmi (l’applicazione) per elaborare tali
dati! E i permessi e i divieti, cosa sono? Riguardano i file contenuti nel
sottoschema affidato a un certo utente. Questi file sono sì affidati a un
utente, ma egli non ne può fare cio che vuole! Ciò serve per esprimere il
5
fatto che un file può essere utilizzato, potremmo dire, a livelli diversi di
intensità o, in altre parole, ha un suo livello di accessibilità. Il livello più
basso di utilizzo è se ci viene permesso solamente di visualizzare e/o di
stampare (visualizzazione e stampa, da un punto di vista sostanziale, sono
la stessa cosa) il contenuto del file; a un livello più elevato ci viene data la
possibilità di inserire nuovi record e di variare il contenuto di record
esistenti (inserimento e variazione sono allo stesso livello di importanza);
a un livello ancora più alto si dà la possibilità a un utente di cancellare,
eliminare record dal file; a un livello ancora superiore sta la possibilità di
variare il tracciato record del file, ad es. aggiungendo o togliendo un
campo, o aggiungendo o togliendo una chiave secondaria; il livello più alto
di utilizzo è il fatto di poter creare un nuovo file, e il fatto di poter
distruggere un file esistente, in poche parole, un diritto di vita e di morte
sui file.
Livello
0
1
2
3
4
5
6
Azioni permesse sul file
nessuna
visualizzazione/stampa
inserimento/variazione di record
eliminazione di record
variazione del tracciato record
creazione di un nuovo file
distruzione di un file
Livelli di accessibilità a un file.
Ciascun livello successivo, naturalmente, comprende le possibilità dei
livelli inferiori. A ogni utente, quindi, oltre ai file contenuti nel
sottoschema, vengono dati dei diritti che riguardano ciò che può fare con
questi file; è ovvio che le azioni che non gli sono permesse diventano dei
divieti nell’utilizzo di tali file. Ciascun file contenuto in un sottoschema ha
un suo livello di accessibilità, che può essere uguale o diverso dai livelli di
accessibilità degli altri file. Inoltre, un certo file che in un sottoschema
ha ad es. solo il diritto di visualizzazione/stampa, in un altro sottoschema
6
può avere il diritto di inserimento/variazione, e in un altro ancora il
diritto di cancellazione. Questa osservazione fa capire che il livello di
accessibilità di un file non è una proprietà intrinseca del file, ma una
proprietà di ciascun sottoschema di cui tale file fa parte.
Ma l’insieme dei diritti e dei divieti richiede un’ulteriore osservazione.
Non è detto che tutti i campi che compongono il tracciato record di un
file abbiano lo stesso livello di accessibilità. Per un certo utente si può
stabilire che alcuni campi di un certo file non devano essere nemmeno
visibili (livello di accessibilità zero), altri campi si possano solo
visualizzare/stampare, altri ancora variare. I diritti e i divieti,
naturalmente, sono legati strettamente al tipo di lavoro che un utente
deve svolgere, e al livello di minore o maggiore riservatezza che i dati
devono possedere nei confronti di un utente oppure di un altro.
In sintesi, un utente, per poter svolgere al meglio il suo lavoro, riceve
dall’azienda un insieme di “oggetti”:
1)
2)
3)
un sottoschema;
un’applicazione;
un insieme di permessi e di divieti (sui file del sottoschema).
Spesso, nella letteratura tecnica si continua a indicare tutto ciò con
sottoschema, inteso in senso più ampio del suo significato stretto; è
meglio però, in omaggio alla chiarezza e alla comprensione, chiamare tutto
ciò profilo utente.
I livelli di intervento sui file più elevati (il 4, il 5, il 6, vedi tabella sopra)
non vengono mai affidati a un utente, non entrano cioè in nessun profilo
utente; il livello più elevato che può essere affidato a un utente è il 3
(eliminazione di record). I livelli 4, 5, 6 sono riservati al DBA
(DataBase Administrator).
In ogni realtà di lavoro organizzato, esiste un solo schema; da esso si
ricavano numerosi sottoschemi, cioè tutti quelli che servono per una buona
gestione. Si noti anche il titolo di questo paragrafo: Schema (al singolare)
e sottoschemi (al plurale).
7
Tipi di file in un database relazionale
Un database aziendale può contenere anche centinaia di file, tra loro
collegati. Ciò può dare una sensazione di vertigine e di grande inquietudine
se si ha la necessità di capire e valutare il database nel suo insieme, e se
si deve anche lavorarci e apportarvi modifiche e/o aggiunte. Una
preoccupazione ancora maggiore può sorgere se dobbiamo progettare un
database da zero, per una nuova attività di lavoro organizzato. Da dove
cominciare? Quanti e quali file usare? Quali campi inserire nel
tracciato record di ciascuno di questi file? Come determinare i
collegamenti tra i file? A una persona inesperta (un beginner, direbbero
gli anglosassoni, uno che inizia) questa progettazione può dare l’idea di un
lavoro vago e indistinto, che può essere svolto in tantissimi modi diversi, e
nel quale è facile sbagliare o andare fuori tema. D’accordo, l’informatica
non è matematica, e quindi il modo con cui si possono fare le cose bene
non è unico. Questo, però, non deve essere fonte di apprensione, ma un
grado di libertà in più lasciato all’inventiva e all’iniziativa personali. Per
fortuna, non si può far quello che si vuole. Per fortuna, esistono delle
regole, dei modi di comportamento collaudati, maturati nel corso di anni,
anzi di decenni, ormai, di lavoro e di esperienza di tanti informatici di
azienda, di trincea, come si dice a volte.
Un dato di fatto fondamentale, che serve a dare chiarezza e tranquillità
nella progettazione, è il seguente:
Tutti i file si possono suddividere in tre categorie; in particolare, ogni file
contenuto in un database può essere classificata come anagrafico, o di
movimento, o di lookup.
1)
Una file anagrafico contiene i dati fondamentali dei soggetti
(persone o cose) sui quali viene costruita un’applicazione. E’ un tipo di
tabella che non viene aggiornata frequentemente.
Esempi:
alunni di una scuola, utenti di un servizio, clienti di una
palestra, contribuenti dotati di Partita IVA, articoli
nell’assortimento di un negozio, articoli prodotti da un’azienda,
clienti, fornitori, dipendenti di un’azienda, ecc.
8
2)
Un file di movimento contiene la successione, di solito cronologica,
dei fatti caratterizzanti l’attività che si vuole informatizzare con
l’applicazione. E’ un tipo di file che viene aggiornato spesso,
soprattutto con l’aggiunta di nuovi record, perché riflette momento
per momento ciò che avviene effettivamente in un ufficio, in un
reparto, in un negozio, ecc.
Esempi:
3)
vendite di un negozio, fatture emesse da un’azienda, cedolini
paga per i dipendenti, movimenti di carico e scarico di un
magazzino, movimenti di versamento e prelievo in un conto
corrente bancario, ecc.
Un file di lookup, che si può chiamare anche file dizionario, contiene
la decodifica, cioè la descrizione in chiaro, delle sigle usate nelle
altre tabelle del database.
Esempi:
province, comuni, regioni, tipi di pagamento, vie, titoli di studio,
ecc.
Si possono considerare file di lookup anche quelli che contengono
parametri di riferimento (es. costo di un’ora di parcheggio, costo di un
pasto, aliquota IMU per i terreni in un certo comune, aliquota IMU per le
seconde case in un certo comune, costo della tassa rifiuti al metro
quadrato in un certo comune, limite di reddito in un certo anno per aver
diritto a una certa detrazione, ecc.) e quelli che contengono contatori
che vengono aggiornati automaticamente da qualche programma della
procedura (es. il contatore del numero di fattura emessa, il contatore del
numero di protocollo, il contatore del numero di scontrino, che devono
essere rigorosamente progressivi e consecutivi, ecc.)
In qualsiasi database (tranne che in casi molto semplici, e quindi molto
rari), o meglio in ciascun sottoschema di un database relazionale,
esistono di solito un file di movimento (in numerosi casi i file di movimento
sono due, ma dal punto di vista logico e pratico si comportano come
fossero un unico file), uno o più file anagrafici, e da zero a un numero
imprecisato (dipende dalle caratteristiche della procedura) di file di
lookup.
9
Come definire i file
Prima di inserire i dati in un file o, come si dice a volte, prima di popolarlo
o, come si dice altre volte, prima di istanziarlo, il file stesso deve essere
definito; gli dobbiamo cioè assegnare una struttura, dei metadati, cioè
dati che servono a organizzare, controllare, contenere altri dati, quelli
che saranno appunto inseriti nel file. La definizione dei file che, nel loro
insieme, costituiranno il database aziendale, è un’attività impegnativa,
delicata, complessa, naturalmente interessantissima. Da una buona
definizione dei file dipende il successo del database aziendale, che
diventa autentico motore, solido e flessibile al tempo stesso, dell’attività
aziendale e del suo sviluppo; da una cattiva definizione dipende
l’insuccesso del database aziendale, che diventa autentico freno, palla al
piede, collo di bottiglia, dell’attività aziendale. La definizione dei file
richiede sia una profonda conoscenza dell’azienda, sia una collaudata
competenza tecnica. Va affrontata con umiltà e con un solido background
da parte del responsabile informatico. L’impegno, la concentrazione, le
conoscenze, l’aggiornamento continuo richiesti a chi ne è responsabile
giustificano l’elevato riconoscimento gerarchico ed economico previsti per
questo tipo di lavoro.
Prima di passare al dettaglio della definizione di un file, parliamo delle
azioni che possiamo eseguire, per mezzo di programmi, sui/con i file.
Su ciascun file si possono eseguire un certo numero di azioni; alcune di
esse si riferiscono al file in quanto tale, nella sua globalità, le altre
agiscono sui record.
F) Azioni che si riferiscono a un file nella sua globalità
(fra parentesi il nome in inglese)
F1) Creazione (Create)
F2) Trasmissione (Transmission)
F3) Compressione-Decompressione (Compression-Decompression;
Zip-Unzip)
F4) Filtraggio (Where)
F5) Ordinamento (Sort)
F6) Fusione di due o più file (Merge)
10
F7) Svuotamento (Clear)
F8) Distruzione (Scratch)
F9) Copia (Backup; Copy)
R) Azioni che si riferiscono a uno o più record (ev. anche a tutti i
record del file) (fra parentesi il nome in inglese)
R1)
R2)
R3)
R4)
R5)
Lettura (Read)
Scrittura o Aggiunta (Write)
Modifica (Update; Rewrite)
Eliminazione (Delete)
Visualizzazione – Stampa (Display – Print).
Torniamo alla definizione di un file. Tale definizione consta di tre passi
fondamentali:
1)
2)
3)
assegnargli un nome;
definirne il tracciato record ;
definirne le chiavi (quella primaria e le (eventuali) secondarie).
Un file è caratterizzato dal suo nome (che è meglio scrivere tutto
MAIUSCOLO) e dal tracciato record. Il tracciato record è l’insieme
ordinato dei campi di un record.
Per ciascun campo bisogna specificare:
a)
b)
c)
d)
e)
f)
g)
h)
numero progressivo;
nome (nome di fantasia);
tipo (numerico, alfanumerico, booleano);
lunghezza (se il campo è numerico, bisogna specificvare anche se ha
il segno, il numero di cifre intere e il numero di cifre dopo la
virgola);
descrizione (breve, e non necessaria se il nome del campo è
autoesplicativo, se cioè si mostra da solo senza pieghe,
se quindi è chiaro di per se stesso);
formato (eventuale);
range;
chiavi.
11
Nella descrizione dei tracciati record usiamo le seguenti convenzioni: nei
limiti del possibile, facciamo in modo che i nomi di tutti i campi inizino con
la lettera con cui inizia il nome del file; indichiamo il tipo numerico con N,
quello alfanumerico con AN, quello booleano con B; per i campi numerici,
indichiamo il segno con S, la parte intera con I, la parte dopo la virgola
con D.
Esempio: S2I3D descrive un campo numerico con segno, e costituito da 2
cifre intere e 3 decimali. I numeri che esso può contenere sono compresi
tra -99,999 e 99,999.
Altro esempio: 5I descrive un campo numerico intero e maggiore uguale a
0. I numeri che esso può contenere sono compresi tra 0 e 99999.
Altro esempio: S0I4D descrive un campo numerico con segno, e costituito
da 0 cifre intere e 4 decimali. I numeri che esso può contenere sono
compresi tra -0,9999 e 0,9999.
Per i campi che hanno una struttura fissa, è utile descrivere il formato,
cioè la successione di lettere e/o cifre e/o altri caratteri che
caratterizzano ciascuna occorrenza (cioè ciascun singolo, specifico valore)
che potrà essere inserito in tali campi. Il formato viene scritto
simbolicamente carattere per carattere, facendo attenzione a usare le
seguenti convenzioni: per i campi alfanumerici, A significa un carattere
alfabetico, N un carattere numerico; per una data, A (oppure a) significa
una cifra dell’anno, M (oppure m) una del mese, G (oppure g) una del
giorno; per un orario, H (oppure h) significa una cifra delle ore, M (oppure
m) una cifra dei minuti, S (oppure s) una cifra dei secondi.
Esempi:
1)
Un codice fiscale sarà descritto sinteticamente da AN 16 (cioè
alfanumerico
di
lunghezza
16),
con
formato
AAAAAANNANNANNNA;
2a)
una data, intesa come campo da memorizzare, sarà descritta
sinteticamente da N 8I (cioè campo numerico intero di 8 cifre),
con formato AAAAMMGG (oppure aaaammgg);
12
2b) una data, intesa come campo da visualizzare, e volendo presentare
l’anno con 4 cifre, sarà descritta sinteticamente da AN 10 (cioè
campo alfanumerico di lunghezza 10), con formato (in Italia)
GG/MM/AAAA (oppure gg/mm/aaaa); se si vuole presentare l’anno
con due sole cifre, il campo sarà descritto sinteticamente da AN 8
(cioè campo alfanumerico di lunghezza 8), con formato (in Italia)
GG/MM/AA (oppure gg/mm/aa);
3a)
un orario, inteso come campo da memorizzare, sarà descritto
sinteticamente da N 6I (cioè campo numerico intero di 6 cifre), con
formato HHMMSS (oppure hhmmss), se vogliamo che contenga ore
minuti secondi; sarà invece descritto sinteticamente da N 4I (cioè
campo numerico intero di 4 cifre), con formato HHMM (oppure
hhmm), se vogliamo che contenga solo ore e minuti;
3b) un orario, inteso come campo da visualizzare, sarà descritto
sinteticamente da AN 7 (cioè campo alfanumerico di lunghezza 7),
con formato HH:MM:SS (oppure hh:mm:ss), se contiene ore minuti
secondi; sarà descritto invece sinteticamente da AN 5 (cioè campo
alfanumerico di lunghezza 5), con formato HH:MM (oppure hh:mm),
se basta visualizzare ore e minuti.
Range di un campo
In genere, per la maggior parte dei campi di un certo record è possibile
sapere a priori che dovranno essere compresi fra un minimo e un massimo,
o che dovranno soddisfare a certe regole. Nel descrivere il tracciato
record di un file, quindi, bisognerà porre grande cura nel definire i range
dei vari campi.
Esempi:
1)
un campo che contiene un prezzo deve essere > 0;
2)
se un campo deve contenere un cognome e nome, o un indirizzo, o
una località, in esso di solito ci deve essere scritto qualcosa;
3)
se un campo deve contenere il codice fiscale, è necessario un
controllo sulla validità di tale codice:
13
a)
i primi 6 caratteri devono essere letterali;
b)
il settimo e ottavo carattere devono essere numerici;
c)
il nono carattere deve essere una
nell'apposita tabella di codifica dei mesi:
gennaio
febbraio
marzo
aprile
maggio
giugno
luglio
agosto
settembre
ottobre
novembre
dicembre
4)
lettera
compresa
A
B
C
D
E
H
L
M
P
R
S
T
d)
il decimo e undicesimo carattere devono essere numerici e
compresi, in generale, tra 01 e 31 per i maschi, tra 41 e 71 per
le femmine, con precisazioni ulteriori legate al mese di nascita,
cioè al nono carattere;
e)
il dodicesimo carattere deve essere letterale;
f)
i caratteri dal tredicesimo al quindicesimo devono essere
numerici, e complessivamente i caratteri dal dodicesimo al
quindicesimo devono costituire una sigla contenuta nella tabella
di codifica ufficiale dei Comuni italiani;
g)
il sedicesimo, e ultimo, carattere deve essere una lettera che
viene ricavata, con un algoritmo opportuno, dalla combinazione
pesata dei primi 15 caratteri;
se un campo deve contenere una Partita IVA, esso deve essere
numerico, cioè un N 11I, e le cifre dall'ottava alla decima devono
contenere il codice della provincia in cui ha sede legale l'attività,
codice memorizzato, insieme con la descrizione in chiaro, in
14
un'apposita tabella ufficiale. Es. per la provincia di Vicenza questo
codice è 024.
L'undicesima, e ultima, cifra della Partita IVA si ricava, mediante un
algoritmo apposito, dalla combinazione pesata delle altre 10 cifre.
Oltre a range rigidi (o obbligatori) (es. un prezzo DEVE essere > 0), si
verificano molti casi di range "elastici" (o consigliati), in corrispondenza
dei quali un programma scritto bene dovrebbe fornire un messaggio di
attenzione, ma non impedire l'inserimento di un dato.
Es. un campo che contiene una temperatura dovrà essere numerico con
segno (e da questo punto di vista è un campo a range rigido), e i suoi valori
saranno contenuti quasi sempre nell'intervallo -30 +40 °C. Se quindi la
temperatura inserita è compresa in questo range, il programma prosegue
regolarmente; se invece è all'esterno di questo range, il programma dovrà
fornirci un messaggio di attenzione, ma non bloccherà l'inserimento del
dato fuori range.
In sintesi, possiamo suddividere i tipi di range in obbligatori e consigliati.
I range obbligatori si possono suddividere ulteriormente in:
intrinseci
(dipendono solo dal campo in oggetto);
esterni
(dipendono da un altro campo, o da più campi);
di esistenza (il valore di un campo è valido se detto valore è presente in
un altro file).
Un certo campo avrà, nella maggior parte dei casi, un range obbligatorio, e
basta; ma potrebbe anche avere un range obbligatorio e uno consigliato,
oppure solo un range consigliato o, in qualche caso, nessun range.
NB Quando dobbiamo dimensionare i campi di un record, e dichiarare di
conseguenza le variabili di lavoro a essi collegate, è necessario prestare
grande attenzione ai valori minimi e massimi che potranno essere
contenuti nei campi stessi, in modo da evitare il pericolo di dichiarare
campi troppo piccoli (che poi non ci permetterebbero di inserire la
maggior parte dei dati) o troppo grandi (occupando così una grande
quantità di spazio su disco che resterebbe sistematicamente vuoto).
15
Importanza dei range
E’ importante definire con grande cura e attenzione il range di ogni campo
di ciascun file contenuto in un database relazionale. I range fanno sì che i
dati di input, quelli che alimentano il database, siano “puliti”, corretti,
coerenti con la realtà che devono descrivere; sono una garanzia di solidità
e di qualità del database. Se si permette che entrino nei file dati con
controllati, anche l’elaborazione su questi dati non sarà affidabile, e
nemmeno i dati di output lo saranno. Un po’ alla volta il database si
rovinerà, non potremo più fidarci dei suoi dati e, invece che motore dello
sviluppo per la nostra attività, ne diventerà un ostacolo, una palla al piede.
In più, definire i range già quando si definiscono i tracciati record dei file
offre un altro grande vantaggio, e cioè che i programmi applicativi (cioè
quelli che vengono scritti dai programmatori per gestire al meglio
l’attività aziendale) non devono preoccuparsi di controllare i dati di input,
perchè il controllo è già stato scritto, una volta per tutte, a livello di
range. Inoltre, programmatori diversi che lavorano per la stessa azienda
non decideranno controlli diversi sugli stessi tipi di campo, come potrebbe
succedere se non ci fosse un controllo centralizzato a livello di range.
Consideriamo ad es. il campo APREZZO, contenuto nel tracciato record
del file ARTICOLI. Ci sono due programmatori aziendali, John Hardware
e Michael Software. Entrambi devono scrivere programmi che utilizzano,
tra gli altri, il campo APREZZO. Se non è stato definito il range di tale
campo, John potrebbe stabilire, in base alla propria conoscenza parziale
della realtà aziendale, che il prezzo deve essere compreso tra 10 e 200
euro, mentre Michael potrebbe invece stabilire, in base alla propria
conoscenza parziale della realtà aziendale, che il prezzo deve essere
compreso tra 15 e 300 euro. Siamo già nel caos. Invece, poichè il range
del campo APREZZO è già stato definito, mettiamo tra 8 e 250, nè John
nè Michael dovranno scrivere nei loro programmi un controllo per il campo
APREZZO; chiunque userà i loro programmi, potrà inserire nel campo
APREZZO solo un valore compreso tra 8 e 250 euro. Quindi l’utilizzo dei
range rende la gestione aziendale uniforme e omogenea, e questo giorno
dopo giorno, a tempo indeterminato, e in modo indipendente da eventuali
libere iniziative di qualche programmatore o di qualche utente, libere
iniziative che non possono verificarsi.
16
Codici e Chiavi
Codici
Il codice è un valore, numerico o alfanumerico, che identifica
univocamente (cioè senza possibilità di confusione) una persona o un
oggetto.
Un insieme di codici ha senso solo conoscendo il contesto o l’ambiente in
cui viene usato. Lo stesso insieme di codici, cioè, può indicare oggetti o
persone diversi fra loro se passiamo da un ambiente a un altro.
Es. 1
All’interno della classe 4AFM, la successione dei numeri da 1 a 26
identifica 26 persone ben precise; all’interno di un’altra classe di 26
studenti lo stesso insieme di codici precedenti, cioè i numeri da 1 a 26,
identifica 26 persone completamente diverse dalle precedenti.
Se ogni persona, od ogni oggetto, all’interno di un certo contesto,
avessero nomi tutti diversi tra loro, non ci sarebbe alcuna necessità di
usare codici per identificarli; molto spesso, invece, si verificano casi di
omonimia sia fra persone che oggetti. Inoltre, anche se non ci fossero
casi di omonimia, nella pratica sarebbe troppo lungo e fonte di errori
verificare un oggetto (o una persona) con la sua intera descrizione, che
può arrivare anche a 100 caratteri.
NB Usare la descrizione al posto del codice può essere fonte di errori
perché una descrizione si presta a essere scritta in molti modi diversi. Es.
Via Alessandro Manzoni potrebbe essere scritta esattamente come
l’abbiamo scritta, ma anche Via A. Manzoni, Via A.Manzoni, Via Manzoni,
Via Manzoni Alessandro, Via Manzoni A., Via Alessandro Manzoni, Via
Alessandro
Manzoni, ecc. E’ sempre la stessa via, ma il computer la
considera come 8 vie diverse! Se decidiamo, una volta per tutte, che Via
Alessandro Manzoni ha il codice V007, ogni volta che ci serve indicare
questa via useremo V007 e non la sua descrizione, perché la sua
descrizione è stata inserita una e una sola volta nel file VIE a fianco del
codice V007. Ogni volta che scriviamo V007, il computer visualizza o
stampa l’unica descrizione esatta, cioè Via Alessandro Manzoni.
17
Un codice può essere numerico o alfanumerico, e in qualche caso solo
alfabetico; a volte può contenere caratteri speciali, ad esempio (-, _, /, .).
Un esempio di codice alfanumerico sono le targhe automobilistiche.
Un codice, per essere efficace, non deve essere né troppo breve né
troppo lungo: se ad esempio abbiamo deciso di assegnare un codice di una
sola lettera maiuscola a un insieme di 30 tipi di matite, abbiamo fatto una
scelta inadeguata, perché ci permette di distinguere solo 26 oggetti; per
classificare i 30 tipi di matite posso quindi usare un codice di un solo
carattere considerando, oltre alle lettere, anche le cifre da 0 a 9; potrei
anche usare solo lettere ma maiuscole e minuscole, o usare un codice di 2
caratteri.
E’ sempre sconsigliabile usare in un codice lettere maiuscole e minuscole,
perché questa scelta potrebbe dar luogo a numerosi errori ed equivoci.
Abbiamo quindi visto che un codice non può essere troppo breve, perché
le combinazioni diverse sarebbero molto limitate; ma un codice non deve
essere nemmeno troppo lungo, perché in tal caso è difficile digitarlo.
Probabilmente uno dei codici più lunghi in assoluto è il codice fiscale, che
occupa 16 caratteri; nelle aziende non si arriva mai a codici di questa
lunghezza.
E’ buona regola non alternare, in un codice, parti numeriche e
alfanumeriche; da questo punto di vista le targhe delle automobili sono un
brutto esempio. Infatti le lettere vengono scritte con la tastiera vera e
propria, i numeri, invece, con il numeric pad, che è una zona della tastiera
ben distinta dalle lettere.
Per decidere la lunghezza di un codice bisogna conoscere il numero
attuale di oggetti che si deve codificare, e la probabile evoluzione
quantitativa di questi oggetti.
Es.
Calcoliamo quante targhe diverse possiamo ottenere con la codifica
attuale (due lettere, tre cifre, altre due lettere, cioè col formato
AANNNAA): 26² * 1000 * 26² = 456.976.000. In realtà, il numero di
targhe diverse possibili è più piccolo, dato che si è deciso di non usare le
lettere I, O, Q, U, che si potrebbero confondere con 1 e 0. Poichè le
18
lettere utilizzabili sono solo 22, il numero di targhe diverse vale 22² *
1000 * 22² = 234.256.000. A essere precisi, il numero di targhe possibili
va suddiviso in due sottoinsiemi, perchè si è deciso di riservare la lettera
iniziale Z per le targhe di forma quadrata (le cosiddette quadrotte); il
numero di tali targhe è 22 * 1000 * 22² = 10.648.000. Il numero di
targhe di forma rettangolare è 21 * 22 * 1000 * 22² = 223.608.000. Il
totale delle targhe possibili si ottiene sommando il numero di quelle
quadrate a quelle rettangolari: 10.648.000 + 223.608.000 = 234.256.000,
come avevamo già ottenuto in precedenza. Un po’ meno di 235 milioni.
In generale, un codice può essere “parlante”, “semiparlante”, o “muto”.
Si dice che un codice è parlante se ci permette di individuare l’oggetto
cui si riferisce anche senza bisogno della descrizione dell’oggetto.
Naturalmente un codice è parlante solo all’interno di un certo contesto.
Ad esempio il codice MATSTAEHB potrebbe identificare una matita di
durezza HB della Staedtler.
Un codice semiparlante fornisce un’idea generale del tipo di oggetto cui si
riferisce. Ad esempio MATIT001. Quindi il codice semiparlante ci offre
un primo orientamento nell’individuazione di un oggetto, che può essere
definito completamente solo con la descrizione. Un tipo molto importante
di codice semiparlante è il codice fiscale.
Un codice muto è semplicemente un insieme di caratteri, lettere e/o
cifre, diverso da tutti gli altri codici presenti in un dato contesto. Esso
non ci dà un’idea neppure vaga del prodotto cui si riferisce. Ad esempio,
se gestiamo un negozio in cui vendiamo 1.000 tipi di articoli diversi, e li
numeriamo dall’ 1 al 1.000, abbiamo attribuito ai nostri 1.000 prodotti un
codice muto. Un tipo molto importante di codice muto o quasi muto è la
partita IVA.
Di solito i codici più efficaci, e di fatto i più usati, sono quelli
semiparlanti.
Quando viene avviata una nuova attività industriale o commerciale è
necessario attribuire un codice a tutti i particolari (materie prime,
semilavorati, prodotti di acquisto, sottoinsiemi finiti, prodotti finiti, pezzi
di ricambio, materiali di consumo, accessori, utensili e strumenti, ecc.)
19
che si pensa saranno usati nell’esercizio dell’attività. Si deve innanzitutto
fare una stima del numero di particolari, del numero di grandi classi in cui
verranno suddivisi, del tipo e lunghezza del codice da usare, dei metodi da
usare per assegnare concretamente a ogni particolare un codice univoco.
L’insieme di queste attività prende il nome di piano di codifica. Il tipo e la
lunghezza del codice scelto devono permettere di assegnare un codice a
molti più particolari di quelli inizialmente previsti, almeno al doppio o al
triplo. Questo al fine di poter gestire senza problemi un auspicabile
sviluppo dell’attività.
Consideriamo un’industria metalmeccanica che desidera individuare, per
tutti i particolari che pensa di usare, un codice semiparlante, alfabetico
nella prima parte, numerico nella seconda; in grado di gestire, in
prospettiva, almeno 100.000 codici diversi.
Il codice semiparlante potrebbe essere costituito da una prima parte
alfabetica di 5 caratteri, a sua volta suddivisa in due parti, e da una
seconda parte numerica di 4 cifre. La lunghezza totale di questo codice è
quindi di 9 caratteri. Per motivi di praticità, in un codice misto non
conviene usare le lettere “I” e “O”, perché si confonderebbero facilmente
con le cifre 1 e 0. La prima parte della parte alfabetica del codice, di due
caratteri, sarà la sigla del grande gruppo a cui appartiene l’oggetto
considerato.
Es.
MP
SL
AC
US
per materie prime
per semilavorato
per un prodotto di acquisto
per utensili e strumenti
La seconda parte della parte alfabetica del codice, di tre caratteri, sarà
una sigla che, all’interno di un grande gruppo, individua un sottoinsieme più
piccolo.
Es.
MPLAM
ACMOT
USCAL
per materie prime – lamiera d’acciaio
per acquisto – motore
per utensili – calibro
20
La parte numerica del codice sarà un numero progressivo che, all’interno
di ogni sottogruppo (es. all’interno dei motori o dei fogli di lamiera)
distingue un particolare da un altro.
Chiavi
Una chiave, come dice la parola stessa, è un mezzo per “entrare” in un
file, per poter cioè accedere ai dati da cui è costituito.
Distinguiamo 2 tipi fondamentali di chiave: la chiave primaria e la chiave
secondaria.
Chiave primaria (o principale; primary key)
E’ costituita da uno (caso più frequente) o più campi (chiave “splittata”,
splitted key) del record, in modo da assicurarne l’univocità. Serve ad
accedere velocemente (alcuni millisecondi, massimo qualche decina di
millisecondi) a un determinato record di un file, con un accesso del tipo
“c’è o non c’è”. L’utente digita una particolare chiave: se nel file esiste un
record con quella chiave, verranno resi disponibili tutti i campi del record
(“c’è”), se non esiste un record con quella chiave, il programma emette un
messaggio (“non c’è”). Molto spesso la chiave primaria è un codice, ma non
sempre, soprattutto nel caso di chiave splittata. Ogni file di un database
relazionale deve avere una chiave primaria, e una sola.
Chiave secondaria (o esterna; foreign key)
Una chiave secondaria è un campo di un file che serve a collegare il
file in cui si trova a un altro file nel quale lo stesso tipo di campo è
chiave primaria. E’ assai utile perché ci permette di scrivere nel file di
cui è chiave secondaria solo la chiave secondaria stessa, e di avere
comunque la disponibilità di tutti i dati associati a essa nel file in cui è
chiave primaria. In questo modo si evita la ridondanza nel database, cioè il
riportare gli stessi dati in più file. Si evita ovviamente anche uno spreco
di spazio di memorizzazione. In un certo file ci possono essere più chiavi
secondarie, o anche nessuna. Fra la chiave secondaria di un file e la chiave
primaria di un altro si stabilisce un collegamento (più precisamente una
relazione): fra i 2 file si instaura una relazione 1 a N, dove l’1 si
21
riferisce alla chiave primaria e l’N alla chiave secondaria. La chiave
secondaria in generale non è univoca, ci possono cioè essere più record
dello stesso file con lo stesso valore di chiave secondaria. Se in nessuno
dei 2 file collegati esiste una chiave secondaria relativa alla chiave
primaria dell’altro, i 2 file si collegano attraverso le rispettive chiavi
primarie, e si parla in tal caso di relazione 1 a 1. Le chiavi oggetto di un
collegamento, siano esse primarie o secondarie, devono avere le stesse
proprietà, cioè lo stesso tipo, la stessa lunghezza, le stesse
caratteristiche di segno, numero di cifre intere, numero di cifre decimali
nel caso siano campi numerici.
Una chiave primaria non splittata non può mai essere anche chiave
secondaria, mentre un singolo campo di una chiave primaria splittata può
essere anche chiave secondaria, anzi tale situazione è abbastanza
frequente.
Il DataBase Administrator (DBA)
Il DataBase Administrator (DBA) è la figura professionale più importante
per ciò che riguarda la progettazione e il buon funzionamento di un
database relazionale. Spesso il DBA è anche il responsabile informatico
tout court dell’intera azienda. All’interno dell’organizzazione, di solito il
DBA è una figura apicale, spesso è un dirigente. Deve conoscere a fondo
la realtà aziendale in cui opera, deve essere aggiornato su tutte le
procedure di lavoro, di tutti gli uffici e reparti. Ovviamente deve
possedere una preparazione tecnica di prim’ordine, integrata da una
visione chiara degli effetti economici e organizzativi delle scelte che
compie e delle decisioni che prende. Gli vengono richieste anche capacità
di dialogo con tutte le componenti aziendali, e anche abilità nel
coinvolgere il personale nelle soluzioni tecniche e organizzative indotte da
nuove applicazioni.
In pratica, di cosa si occupa il DBA?
Il DBA è il responsabile dello schema e dei sottoschemi, dei profili
utente e, in generale, della buona salute del database aziendale. In
particolare, deve fissare i range dei vari campi, e naturalmente variarli
22
quando ne vede la necessità o l’opportunità; a lui spetta anche stabilire i
livelli di accessibilità ai vari file per i dipendenti che utilizzano
l’informatica nel loro lavoro (cioè quasi tutti). Deve agire in modo che i
dati possiedano un livello di riservatezza proporzionale alla loro
importanza per la vita e lo sviluppo dell’azienda. Deve anche assicurare un
ripristino veloce e affidabile del database aziendale nel caso di guasti
hardware o malfunzionamenti; in particolare, quindi, deve fare in modo
che esistano copie di backup aggiornate e multiple dei dati e dei
programmi (a proposito, rispristinare qualcosa significa riportarla a
com’era prima).
Il lavoro del DBA, all’interno di una realtà di lavoro organizzato, è
eccezionalmente delicato, impegnativo, interessante, importante. E’ quindi
logico che vi corrispondano un livello gerarchico elevato all’interno
dell’organigramma, e una retribuzione e dei benefit di tutto rispetto.
Tipi di linguaggi che si utilizzano in un database
relazionale
Abbiamo parlato un po’ di pagine fa dell’RDBMS, cioè dell’insieme dei
programmi che permettono la gestione delle caratteristiche generali dei
database relazionali. L’RDBMS, oltre al resto, mette a disposizione del
personale informatico due tipi di linguaggio:
1)
DDL (Data Definition Language, letteralmente linguaggio per la
definizione dei dati);
2)
SQL (Structured Query Language, letteralmente linguaggio
strutturato per interrogazioni).
DDL
DDL non è il nome di un linguaggio di programmazione, come ad es. C++ o
Javascript; è un tipo di linguaggio, presente in ogni RDBMS, che serve a
organizzare un database relazionale prima che si cominci a inserirvi i dati.
A cosa serve il DDL? A definire i dati, naturalmente, lo dice il suo stesso
nome! Ma cosa vuol dire in pratica?
Il DDL serve a definire lo schema del database, cioè serve a:
23
definire ogni singolo file del database, che significa a sua volta:
dare un nome al file e descrivere il suo tracciato record,
che significa a sua volta:
per ciascun campo del tracciato record, assegnargli un numero
progressivo, un nome, il tipo e la lunghezza, una breve
descrizione, un eventuale formato, il range.
Per completare la definizione dello schema, sempre col DDL bisogna
indicare, per ciascun file, qual è la sua chiave primaria (eventualmente
splittata) e quali le sue (eventuali) chiavi secondarie;
bisogna infine stabilire le relazioni, 1 a N o 1 a 1, tra i vari file. Definire
lo schema del database relazionale significa tutto ciò. Tutto ciò serve a
organizzare un database relazionale prima che si cominci a inserirvi i dati.
Il DDL serve anche a individuare i profili utente necessari per una
gestione ottimale del database e, quindi, della realtà aziendale.
Ricordiamo che un profilo utente comprende:
1)
2)
3)
un sottoschema;
un’applicazione;
un insieme di permessi e di divieti (sui file del sottoschema).
Ricordiamo anche che, spesso, invece di profilo utente si indica tutto ciò
con sottoschema, anche se sottoschema sarebbe solo una parte del
profilo utente.
Da chi viene utilizzato, nell’ambiente di lavoro, il DDL?
Sempre e solo dal DBA.
SQL
Dell’SQL si parla molto ampiamente dal paragrafo successivo, data la sua
enorme importanza e diffusione nel mondo dei database relazionali.
Qui rispondiamo solo alla domanda: da chi viene utilizzato, nell’ambiente di
lavoro, l’SQL?
24
Dal personale informatico, ma anche dagli utenti “svegli”, che possono con
tale strumento ottenere risultati veloci, spesso non previsti dalla
dotazione dei programmi applicativi, ricavandone anche una giusta e
meritata soddisfazione professionale.
Oltre ai due tipi di linguaggio appena descritti (DDL ed SQL), in un
database relazionale si usano uno o più DML (Data Manipulation Language,
letteralmente linguaggio per la manipolazione dei dati). DML non è il nome
di un linguaggio specifico; un DML è uno qualsiasi dei numerosi linguaggi di
programmazione che si usano per scrivere le applicazioni, cioè i programmi
che permettono di gestire al meglio gli uffici e i reparti aziendali, es.
l’ufficio acquisti, l’ufficio vendite (chiamato spesso ufficio commerciale, o
semplicemente
commerciale),
l’ufficio
del
personale,
l’ufficio
amministrativo, i magazzini, la progettazione, la programmazione della
produzione, la produzione, le spedizioni, l’assistenza post vendita, i
terzisti, la manutenzione, ecc. Esempi di DML molto usati sono Visual
Basic, C++, PHP, Java, COBOL, RPG.
SQL (Structured Query Language) - Introduzione
L’SQL è il linguaggio presente in ogni gestore di database relazionali, cioè
in ogni RDBMS (Relational DataBase Management System): l’IBM ha il suo
SQL, Oracle ha il suo SQL, MySQL(Nota) ha il suo SQL, Access il suo,
SQL/Server il suo, PostgreSQL il suo, ecc. Ma, per fortuna, pur avendo
ciascun produttore il suo SQL nel suo RDBMS, i vari SQL sono
sorprendentemente simili tra loro, differiscono solo per dettagli che un
utente può imparare in breve tempo e senza fatica se deve passare da un
RDBMS a un altro: un vero miracolo. L’SQL realizza il sogno di Codd, il
fatto cioè di essere un linguaggio universal per i dati.
Nota: MySQL è un gestore di database relazionali free, molto usato
anche in ambito universitario.
L’SQL si può usare in due modalità: stand alone o embedded.
L’SQL usato stand alone (cioè per conto suo) si presenta come una
successione di singole istruzioni. Ogni istruzione realizza una query, cioè
25
un’estrazione di dati dalle tabelle del database relazionale secondo certi
criteri di scelta e di ordinamento.
L’SQL si può usare anche embedded, cioè incorporato in un programma
scritto con un altro linguaggio di programmazione. Se chi sta scrivendo un
programma si rende conto che, per estrarre dati da uno o più file del
database, dovrebbe scrivere numerose istruzioni nel linguaggio con cui sta
scrivendo il programma, mentre usando l’SQL gli basterebbe una sola
istruzione, ha la possibilità di usare una sola istruzione SQL invece di
tante istruzioni ad es. in Visual Basic o C++ o Java o PHP.
SQL è quindi uno strumento flessibile e potente per due ragioni: primo
perché si può usare senza conoscere come i file sono effettivamente
memorizzati dal punto di vista fisico, secondo perché, se serve, funziona
bene dentro a programmi “tradizionali” scritti con un linguaggio del tutto
diverso.
L’insieme dei vari comandi SQL si può suddividere in tre sottoinsiemi:
1)
comandi che servono a creare e modificare la struttura dei file, e a
definirne le chiavi, principali e secondarie. Qui l’SQL funziona da
linguaggio di tipo DDL (Data Definition Language, linguaggio per la
definizione dei dati);
2)
comandi che servono a estrarre dati dal database, a realizzare cioè
il data retrieval and sort (Nota), e anche a creare, modificare e
cancellare record. Qui l’SQL funziona da linguaggio di tipo DML
(Data Manipulation Language, linguaggio per la manipolazione dei
dati);
3)
comandi che servono a concedere o revocare autorizzazioni per
accedere ai file del database relazionale soprattutto dove si lavora
in multiutenza e in rete, per motivi di sicurezza, di riservatezza, di
esatta definizione del lavoro di ciascun utente. Qui l’SQL funziona
da linguaggio di tipo DCL (Data Control Language, linguaggio per il
controllo dei dati), che può essere considerato un sottoinsieme del
DDL.
Nota: cos’è il data retrieval and sort (letteralmente recupero dati e
ordinamento)? E’ l’estrazione di dati da un database relazionale in
26
modo mirato, utilizzando criteri e vincoli specifici che restringano e
specializzino la richiesta; l’elenco di dati così ottenuto può essere
eventualmente ordinato secondo uno o più campi (es. ordine alfabetico,
per prezzo crescente, per valore decrescente, per sesso e ordine
alfabetico, per comune di residenza, via e numero civico, ecc.).
In generale, il data retrieval and sort agisce su una o più tabelle del
database. Non bisogna confonderlo con la navigazione in Rete, in quanto la
navigazione può essere anche casuale, dettata da un impulso del momento,
non pianificata, mentre il data retrieval and sort serve a raggiungere e
colpire un obiettivo previsto e prederminato.
Esempi di richieste di data retrieval and sort:
1)
voglio ottenere l’elenco dei miei clienti francesi che nell’ultimo
esercizio hanno emesso ordini alla mia azienda per almeno 5.000
euro;
2)
voglio ottenere l’elenco dei contribuenti femmine, laureate o
diplomate, di età compresa tra 20 e 35 anni, in ordine alfabetico;
3)
voglio ottenere l’elenco degli articoli prodotti dalla mia azienda per i
quali il prezzo di vendita è almeno doppio del costo di produzione, in
ordine da quello che ha il margine percentuale più elevato e, a parità
di margine percentuale, in ordine alfabetico;
4)
voglio vedere il/i prodotto/i della mia azienda più venduto/i negli
Stati Uniti negli ultimi 6 mesi, e così via.
Il data retrieval and sort può fornire informazione anche se il suo output
è vuoto. Se ad es. chiedo di vedere l’elenco dei miei clienti argentini che
negli ultimi 6 mesi hanno emesso ordini, e l’elenco è vuoto, capisco subito
che il mercato argentino ha dei problemi, e posso quindi dedicarmi a
studiare la questione e a ricercarne le soluzioni possibili.
Sinonimo di data retrieval and sort è query, che però è una parola molto
meno espressiva.
Lo strumento fondamentale che realizza il data retrieval and sort è
l’SQL.
27
Nel paragrafo successivo vengono presentati i comandi principali dell’SQL
(non tutti, anzi molto meno di tutti), con la loro struttura, le loro parole
chiave, e numerosi esempi. Chi desidera approfondire e completare le sue
conoscenze in questo interessantissimo ambito lo può fare navigando,
anche se con fatica e grande attenzione, in Internet o, ancora meglio,
all’università o, ancora meglio, in un luogo di lavoro in cui si utilizza l’SQL a
scopo professionale.
SQL (Structured Query Language)
Parole chiave e struttura dei comandi principali
NB
Il simbolo “|” significa “oppure”
SELECT
L’istruzione più importante e più frequentemente utilizzata in SQL è
SELECT, usata per la ricerca, il filtraggio, l’ordinamento dei dati
contenuti in una o più tabelle, cioè per il data retrieval and sort.
Il risultato restituito da questa istruzione è sempre una tabella
(eventualmente di una sola riga o, a volte, vuota) che, se viene
memorizzata, successivamente può essere ancora elaborata.
Il formato completo dell’istruzione SELECT è:
SELECT {TOP k} nome-campo1{ AS nome-di-fantasia1}{, nome-campo2
{AS nome-di-fantasia2}, … ,
nome-campoN {AS nome-di-fantasiaN}} | * | funzione di sintesi
FROM nome-tabella1{, nome-tabella2,…, nome-tabellaN}
{WHERE condizione/i}
{GROUP BY [nome-campo]…}
{HAVING condizione/i}
{ORDER BY nome-campo1{ DESC}{, nome-campo2, { DESC}…}
Essa permette di selezionare dalla/e tabella/e specificata/e nella
clausola FROM tutte le righe che soddisfano alla condizione specificata
nella clausola WHERE.
28
La clausola WHERE definisce la condizione a cui devono sottostare le
righe da ricercare. Una condizione può essere di confronto semplice
(usando gli operatori >, >=, <,<=, =,<>, MID, LIKE), oppure condizioni
composte con AND, OR, NOT. Si possono usare anche le opzioni
BETWEEN … AND … e IN(elenco di valori).
La clausola GROUP BY raggruppa le righe di una tabella in base ai valori
uguali delle colonne specificate. Questa opzione produce una riga di
risultato per ogni rggruppamento.
Si utilizza la clausola HAVING per specificare quali record raggruppati
con la clausola GROUP BY devono essere visualizzati.
FUNZIONI DI SINTESI
SQL mette a disposizione alcune funzioni di sintesi che agiscono sui
valori di insieme di alcuni campi di una tabella:
COUNT
MIN
MAX
SUM
AVG
conta il numero di righe di una tabella;
trova il valore minimo contenuto in una colonna;
trova il valore massimo contenuto in una colonna;
calcola la somma dei valori contenuti in una colonna;
calcola la media aritmetica dei valori di una colonna.
Tre osservazioni importanti
1)
Quando si utilizzano più tabelle è necessario scrivere nel WHERE,
oltre alle altre eventuali condizioni, anche il collegamento (inner
join) tra ciascuna coppia di tabelle considerate.
2)
Nelle condizioni di confronto contenute nel WHERE, la parte a
destra della condizione, invece di un valore definito, può essere la
richiesta di inserimento dinamico di un valore quando la query viene
eseguita.
Es. invece di ... WHERE PREZZO > 80, si può scrivere ... WHERE
PREZZO > [Inserisci un prezzo]. Al momento dell’esecuzione, l’SQL
si pone in attesa che l’utente inserisca al momento il valore di
prezzo desiderato. Ciò rende la query molto flessibile o, come si
dice, parametrica.
29
3)
I comandi SELECT possono essere nidificati (nested), specificando
un’interrogazione all’interno di un’altra interrogazione.
UPDATE
I valori contenuti in una tabella possono essere modificati con l’istruzione
UPDATE:
UPDATE nome-tabella
SET nome-campo1 = espressione1{, nome-campo2 = espressione2,…,
nome-campoN = espressioneN }
{WHERE condizione}
DELETE
E’ possibile eliminare righe di una tabella con l’istruzione DELETE:
DELETE
FROM nome-tabella
{WHERE condizione}
Attenzione che l’istruzione DELETE FROM nome-tabella elimina tutte
le riga di una tabella, conservandone solo la struttura.
SQL (Structured Query Language) - Esempi
File ARTICOLI (stand alone)
Consideriamo il file ARTICOLI, che contiene i dati anagrafici degli
articoli venduti in un negozio di generi alimentari. Facciamo l’ipotesi che il
negozio venda solo merce confezionata, cioè che ciascun articolo abbia
una quantità prefissata, non variabile. Facciamo anche l’ipotesi che i campi
9) e 10) vengano azzerati a ogni inizio anno.
30
Il suo tracciato record è
Prog.Nome
Tipo Lungh. Descrizione
Range
Chiavi
(ed ev. formato)
1)
ACODICE
AN
8
2)
ADESCR
AN
30
3)
AQTA
4)
AUM
AN
2
Unità di misura
G, HG, KG,
ML, L (1)
5)
ACATMERC
AN
3
Categoria merc.
PAN, PAR,
SAL, FOR,
SPE, OLA,
CAR, FRU,
VER, LAT,
ACQ, VBL (2)
6)
ACOSTO
N
3I2D Costo
il costo al Kg
o al litro deve
essere compreso
tra 0,07 e 35
7)
APREZZO
N
3I2D Prezzo
il prezzo al Kg
o al litro deve
essere compreso
tra 0,08 e 70
N 4I2D
Codice dell’articolo <> “ “
AANNNNNN
Descrizione
KP
<> “ “
Quantità in una
>0e
confezione (espressa <= 1000 se
nell’unità di misura AUM = “G “
seguente)
o “ML”;
< 10 se
AUM = HG;
<= 5 se
AUM = “KG”
o “L “
31
8)
AQNEGOZ
N
4I
Quantità presente
in negozio
campo automatico
9)
AQVEN
N
5I
Quantità venduta
da inizio anno alla
data odierna
campo automatico
10)
APROGUV
N
3I
Progressivo dell’anno campo automatico
in cui è stata
effettuata
la vendita più
recente
__________________________
(1)
G = grammi
HG = ettogrammi
KG = chilogrammi
ML = millilitri
L = litri
(2)
PAN = pane, crackers, grissini, fette biscottate
PAR = pasta e riso
SAL = salumi
FOR = formaggi
SPE = spezie
OLA = olio e aceto
CAR = carne
FRU = frutta
VER = verdura
LAT = latte, burro, yogurt
ACQ = acqua
VBL = vini, birre e liquori
32
Istruzioni SQL relative al file ARTICOLI di cui si è appena
descritto il tracciato record
SELECT con campi dei record e campi calcolati
1)
Visualizzare tutti i record dell’intero file, con tutti i campi,
nell’ordine con cui i record sono stati inseriti nel file.
SELECT * FROM ARTICOLI
2)
Visualizzare tutti i record dell’intero file, con tutti i campi, in ordine
alfabetico.
SELECT * FROM ARTICOLI
ORDER BY ADESCR
3)
Visualizzare, per ciascun record, solo codice, descrizione, prezzo, in
ordine dal prezzo più alto al più basso e, a parità di prezzo, in ordine
alfabetico.
SELECT ACODICE, ADESCR, APREZZO
FROM ARTICOLI
ORDER BY APREZZO DESC, ADESCR
4)
Visualizzare, per ciascun record, i campi codice, descrizione, costo,
prezzo, differenza tra prezzo e costo, aumento percentuale del
prezzo rispetto al costo (presenza di campi calcolati ).
SELECT ACODICE, ADESCR, ACOSTO, APREZZO, APREZZO – ACOSTO
AS [Differenza tra prezzo e costo], (APREZZO – ACOSTO)/ACOSTO *
100 AS [Aumento percentuale del prezzo rispetto al costo]
FROM ARTICOLI
5)
Visualizzare tutti i record degli articoli dei quali quest'anno non ne é
stato venduto nessuno, in ordine da quello che ha il margine (prezzo
- costo) più alto e, a parità di margine, in ordine alfabetico
(presenza di un campo calcolato nell'ORDER BY).
SELECT * FROM ARTICOLI
33
WHERE APROGUV = 0
ORDER BY APREZZO – ACOSTO DESC, ADESCR
6)
Visualizzare i primi 7 articoli in ordine alfabetico presenti nel file.
SELECT TOP 7 * FROM ARTICOLI
ORDER BY ADESCR
7)
Visualizzare i 10 articoli più costosi (per i clienti) presenti nel file.
SELECT TOP 10 * FROM ARTICOLI
ORDER BY APREZZO DESC
8)
Visualizzare descrizione e prezzo dei 16 articoli meno costosi (per i
clienti) presenti nel file.
SELECT TOP 16 ADESCR, APREZZO FROM ARTICOLI
ORDER BY APREZZO
9)
Visualizzare tutti i campi di tutti i record di frutta e verdura, prima
tutta la verdura, poi tutta la frutta e, all'interno di ciascuno dei due
raggruppamenti, in ordine dall'articolo più costoso (per i clienti) e, a
parità di costo, in ordine alfabetico.
SELECT * FROM ARTICOLI
WHERE ACATMERC = "FRU" OR ACATMERC = "VER"
in alternativa: ACATMERC IN ("FRU","VER")
ORDER BY ACATMERC DESC, APREZZO DESC, ADESCR
10)
Visualizzare tutti i campi di tutti i record degli articoli che non
vengono venduti da almeno 20 giorni, facendo l'ipotesi che il
progressivo della data odierna sia 73.
SELECT * FROM ARTICOLI
WHERE 73 – APROGUV >= 20
11)
Visualizzare, per ciascun record di pane, crackers, grissini, fette
biscottate, codice, descrizione, prezzo, quantità venduta dall'inizio
34
dell'anno a oggi, ricavo dall'inizio dell'anno a oggi, in ordine dal
ricavo minore al maggiore.
SELECT ACODICE, ADESCR, APREZZO, AQVEN, AQVEN * APREZZO
AS [Ricavo dall'inizio dell'anno a oggi]
FROM ARTICOLI
WHERE ACATMERC = "PAN"
ORDER BY AQVEN * APREZZO
12)
Visualizzare i record che contengono nella descrizione la parola
"riso".
SELECT * FROM ARTICOLI
WHERE ADESCR LIKE "*riso*"
13)
Visualizzare i record che la cui descrizione inizia con "Riso".
SELECT * FROM ARTICOLI
WHERE ADESCR LIKE "Riso*"
in alternativa: WHERE MID(ADESCR, 1, 4= = “Riso”
14)
Visualizzare i record degli articoli non presenti in negozio, in ordine
da quello che ha venduto di più (e che quindi ha maggiore urgenza di
essere riordinato al fornitore).
SELECT * FROM ARTICOLI
WHERE AQNEGOZ = 0
ORDER BY AQVEN DESC
SELECT con campi di sintesi
15)
Visualizzare quanti sono i record del file ARTICOLI.
SELECT COUNT(*) AS [Numero di record del file ARTICOLI]
FROM ARTICOLI
16)
Visualizzare quanti sono gli articoli mai venduti quest’anno.
SELECT COUNT(*) AS [Numero di articoli mai venduti quest’anno]
35
FROM ARTICOLI
WHERE AQVEN = 0
17)
Visualizzare quante confezioni di pasta e riso sono state vendute
fino a oggi nell’anno in corso.
SELECT SUM(AQVEN) AS [Numero di confezioni di pasta e riso vendute
fino a oggi nell’anno in corso]
FROM ARTICOLI
WHERE ACATMERC = “PAR”
18)
Visualizzare il ricavo totale nell’anno in corso fino a oggi proveniente
dalla vendita di vino, birra e liquori.
SELECT SUM(APREZZO * AQVEN) AS [Ricavo totale nell’anno in corso
fino a oggi proveniente dalla vendita di vino, birra e liquori]
FROM ARTICOLI
WHERE ACATMERC = “VBL”
19)
Visualizzare il prezzo massimo degli articoli della categoria “spezie”.
SELECT MAX(APREZZO) AS [Prezzo massimo degli articoli della
categoria “spezie”]
FROM ARTICOLI
WHERE ACATMERC = “SPE”
20) Visualizzare il margine minimo degli articoli venduti nell’ultima
settimana, facendo l’ipotesi che il progressivo della data odierna sia
107.
SELECT MIN(APREZZO - ACOSTO) AS [Margine minimo degli articoli
venduti nell’ultima settimana]
FROM ARTICOLI
WHERE 107 – APROGUV <= 7
21)
Visualizzare il prezzo medio dei vari tipi di olio e aceto.
SELECT AVG(APREZZO) AS [Prezzo medio dei vari tipi di olio e aceto]
FROM ARTICOLI
36
WHERE ACATMERC = “OLA”
22) Visualizzare il margine netto totale nell’anno in corso fino a oggi
ipotizzando una tassazione del 25% sul margine lordo.
SELECT SUM((APREZZO – ACOSTO) * AQVEN) * 0,75 AS [Margine
netto totale nell’anno in corso fino a oggi ipotizzando una tassazione del
25% sul margine lordo]
FROM ARTICOLI
23) Visualizzare il numero medio di confezioni di carne vendute nell’anno
in corso fino a oggi.
SELECT AVG(AQVEN) AS [Numero medio di confezioni di carne vendute
nell’anno in corso fino a oggi]
FROM ARTICOLI
WHERE ACATMERC = “CAR”
SELECT con raggruppamento (GROUP BY)
24) Visualizzare l’elenco delle categorie merceologiche in ordine
alfabetico, indicando per ciascuna categoria quanti articoli ne fanno
parte.
SELECT ACATMERC, COUNT(*) AS [Numero di articoli che fanno parte
di ciascuna categoria merceologica]
FROM ARTICOLI
GROUP BY ACATMERC
ORDER BY ACATMERC
25) Visualizzare l’elenco delle categorie merceologiche, indicando per
ciascuna categoria quanti articoli ne fanno parte, in ordine dalla
categoria merceologica che contiene più articoli e, a parità di
articoli, in ordine alfabetico.
SELECT ACATMERC, COUNT(*) AS [Numero di articoli che fanno parte
di ciascuna categoria merceologica]
FROM ARTICOLI
GROUP BY ACATMERC
37
ORDER BY COUNT(*) DESC, ACATMERC
26) Visualizzare l’elenco delle categorie merceologiche che contengono
almeno 80 articoli, indicando per ciascuna categoria quanti articoli
ne fanno parte, in ordine dalla categoria merceologica che contiene
meno articoli.
SELECT ACATMERC, COUNT(*) AS [Numero di articoli che fanno parte
di ciascuna categoria merceologica con almeno 80 articoli]
FROM ARTICOLI
GROUP BY ACATMERC
HAVING COUNT(*) >= 80
ORDER BY COUNT(*)
27) Visualizzare l’elenco delle unità di misura, indicando per ciascuna
unità di misura quanti articoli ne fanno parte e qual è stato il ricavo
degli articoli che hanno quell’unità di misura, in ordine dall’unità di
misura con il ricavo maggiore.
SELECT AUM, COUNT(*) AS [Numero di articoli che hanno una certa
unità di misura], SUM(APREZZO * AQVEN) AS [Ricavo degli articoli che
hanno una certa unità di misura]
FROM ARTICOLI
GROUP BY AUM
ORDER BY SUM(APREZZO * AQVEN) DESC
SELECT nidificate
28) Visualizzare, per l’articolo (o gli articoli, perché possono essere più
di uno) che ha (hanno) il prezzo più alto, descrizione e prezzo.
SELECT ADESCR, APREZZO AS [Articolo col prezzo più alto]
FROM ARTICOLI
WHERE APREZZO = (SELECT MAX(APREZZO) FROM ARTICOLI)
38
29) Visualizzare, per gli articoli che hanno margine maggiore del margine
medio, descrizione, costo, prezzo, margine, margine medio, in ordine
alfabetico.
SELECT ADESCR, ACOSTO, APREZZO, APREZZO - ACOSTO AS
[Margine], (SELECT AVG(APREZZO – ACOSTO) FROM ARTICOLI)
FROM ARTICOLI AS [Margine medio]
WHERE APREZZO – ACOSTO > (SELECT AVG(APREZZO - ACOSTO)
FROM ARTICOLI)
30) Visualizzare tutti i campi degli articoli che hanno prezzo almeno
doppio del costo medio di tutti gli articoli, in ordine di prezzo
crescente.
SELECT * FROM ARTICOLI
WHERE APREZZO >= (SELECT AVG(ACOSTO) FROM ARTICOLI) * 2
ORDER BY APREZZO
31)
Visualizzare, per gli articoli venduti nelle ultime quattro settimane,
descrizione, costo, prezzo, quanti giorni fa è stata effettuata la
vendita più recente (ipotizzando che il progressivo della data
odierna sia 127), considerando solo gli articoli con prezzo compreso
fra il 10% in meno e il 10% in più del prezzo medio di tutti gli articoli
che sono stati venduti nell’anno in corso.
SELECT ADESCR, ACOSTO, APREZZO, 127 – APROGUV AS [quanti giorni
fa è stata effettuata la vendita più recente]
FROM ARTICOLI
WHERE 127 – APROGUV <= 28 AND APREZZO >= (SELECT
AVG(APREZZO) FROM ARTICOLI WHERE AQVEN > 0) * 0,90 AND
APREZZO <= (SELECT AVG(APREZZO) FROM ARTICOLI WHERE
AQVEN > 0) * 1,10
39
UPDATE
32) Diminuire del 7% il prezzo degli articoli in cui non ci sono state
vendite nell’anno in corso.
UPDATE ARTICOLI
SET APREZZO = APREZZO * 0,93
WHERE AQVEN = 0
33) Aumentare del 3,5% il costo e del 5,5% il prezzo della frutta e
verdura di cui sono state vendute nel corso dell’anno almeno 120
confezioni per ciascun articolo.
UPDATE ARTICOLI
SET ACOSTO = ACOSTO * 1,035, APREZZO = APREZZO * 1,055
WHERE (ACATMERC = “FRU” OR ACATMERC = “VER”) AND AQVEN >=
120
34) Diminuire dell’1% il prezzo degli articoli che hanno prezzo almeno
triplo del prezzo medio di tutti gli articoli.
UPDATE ARTICOLI
SET APREZZO = APREZZO * 0,99
WHERE APREZZO >= (SELECT AVG(APREZZO) FROM ARTICOLI) * 3
DELETE
35) Svuotare l’intero file ARTICOLI. (Da non usare assolutamente
MAI).
DELETE FROM ARTICOLI
36) Eliminare dal file ARTICOLI gli articoli che non hanno avuto vendite
nell’anno in corso e di cui non esiste nemmeno una confezione in
negozio.
DELETE FROM ARTICOLI
WHERE AQVEN = 0 AND AQNEGOZ = 0
40
37) Eliminare dal file ARTICOLI gli articoli che non hanno avuto vendite
nell’anno in corso e il cui margine è inferiore al 10% del margine
medio.
DELETE FROM ARTICOLI
WHERE AQVEN = 0 AND APREZZO – ACOSTO < (SELECT
AVG(APREZZO – ACOSTO) FROM ARTICOLI) * 0,10
File VENDITE (prima stand alone, poi collegato ad ARTICOLI)
Consideriamo il file VENDITE, che contiene i dati relativi alle vendite
nell’anno 2013 del negozio i cui articoli sono contenuti nel file ARTICOLI,
descritto e utilizzato stand alone, cioè da solo, nel paragrafo precedente.
Nel file VENDITE ciascun record rappresenta la riga di uno scontrino,
emesso da una delle 4 casse del negozio. Facciamo l’ipotesi che la quantità
venduta di qualsiasi articolo sia compresa tra 1 e 100. Non sono previsti
sconti né offerte speciali, perciò il prezzo unitario è sempre quello che si
trova in ARTICOLI. Facciamo anche l’ipotesi che il numero di scontrino
parta da 1 per ciascuna cassa in ciascun giorno di apertura. In ogni caso la
data e l’ora della vendita, e il numero dello scontrino, sono campi
automatici, che cioè non vengono inseriti nel record da un operatore
umano, e nemmeno da un operatore umano che usa il lettore di barcode,
ma vengono calcolati automaticamente da un programma.
Il tracciato record del file VENDITE è
Prog. Nome
Tipo Lungh. Descrizione
(ed ev. formato)
Range
Chiavi
1)
VNUSCO
N
3I
Numero dello
scontrino
campo
automatico
KP(3)
2)
VDASCO
N
8I
Data dello
scontrino
aaaammgg
campo
automatico
KP(2)
3)
VORMISCO
N
4I
Ora e minuti dello
campo
41
scontrino
hhmm
automatico
4)
VNUCAS
N
1I
Numero della cassa campo
automatico
KP(1)
5)
VCODICE
AN
8
Codice dell’articolo deve esistere KP(4)
in ARTICOLI e KS
6)
VQTA
N
3I
Quantità venduta
>= 1 e <= 100
Istruzioni SQL relative al file VENDITE (stand alone) di cui
si è appena descritto il tracciato record
SELECT con campi dei record e campi calcolati
1)
Visualizzare tutti i record dell’intero file, con tutti i campi,
nell’ordine con cui i record sono stati inseriti nel file
SELECT * FROM VENDITE
2)
Visualizzare i record, con tutti i campi, con quantità venduta uguale
a 100, dalla vendita meno recente alla più recente.
SELECT * FROM VENDITE
WHERE VQTA = 100
ORDER BY VDASCO
3)
Visualizzare i record, con tutti i campi, delle vendite effettuate nel
gennaio 2013, in ordine di data e, a parità di data, in ordine di codice
e, a parità di codice, in ordine di quantità decrescente.
SELECT * FROM VENDITE
WHERE VDASCO >= 20130101 AND VDASCO <= 20130131
in alternativa: WHERE VDASCO BETWEEN 20130101 AND 20130131
ORDER BY VDASCO, VCODICE, VQTA DESC
42
4)
Visualizzare i record, con tutti i campi, degli scontrini emessi dalla
cassa n. 3 dalle 9:30 alle 11:30.
SELECT * FROM VENDITE
WHERE VNUCAS = 3 AND VORMISCO >= 930 AND VORMISCO <= 1130
in alternativa: VORMISCO BETWEEN 930 AND 1130
5)
Visualizzare, per le vendite effettuate nel febbraio 2013, codice,
quantità, numero della cassa, numero dello scontrino, progressivo del
giorno, in ordine di numero di cassa e, a parità di numero di cassa, in
ordine di data e, a parità di data, in ordine di numero di scontrino e,
a parità di numero di scontrino, in ordine di codice.
SELECT VCODICE, VQTA, VNUCAS, VNUSCO, 31 + MID(VDASCO, 7, 2)
AS [progressivo del giorno]
FROM VENDITE
WHERE VDASCO BETWEEN 20130201 AND 20130228
ORDER BY NUCAS, VDASCO, VNUSCO, VCODICE
6)
Visualizzare le prime 10 righe di scontrino emesse nel marzo 2013.
SELECT TOP 10 *
FROM VENDITE
WHERE VDASCO BETWEEN 20130301 AND 20130331
ORDER BY VDASCO, VORMISCO
SELECT con campi di sintesi
7)
Visualizzare quanti sono i record del file VENDITE.
SELECT COUNT(*) AS [Numero di record del file VENDITE]
FROM VENDITE
43
8)
Visualizzare quante confezioni sono state vendute nei primi 3 mesi
del 2013.
SELECT SUM(VQTA) AS [Numero di confezioni vendute nei primi 3 mesi
del 2013]
FROM VENDITE
WHERE VDASCO >= 20130101 AND VDASCO <= 20130331
9)
Visualizzare quante righe di scontrino sono state prodotte dalle
casse 1, 3, 4.
SELECT COUNT(*) AS [Numero di righe di scontrino prodotte dalle
casse 1, 3, 4]
FROM VENDITE
WHERE VNUCAS IN(1, 3, 4)
in alternativa: VNUCAS <> 2
SELECT con raggruppamento (GROUP BY)
10)
Visualizzare l’elenco delle casse, ordinato per numero di cassa,
indicando per ciascuna di esse quante righe di scontrino ha emesso
nell’anno in corso.
SELECT VNUCAS, COUNT(*) AS [Righe di scontrino emesse da ciascuna
cassa nell’anno in corso]
FROM VENDITE
GROUP BY VNUCAS
ORDER BY VNUCAS
L’output sarà simile a questo:
VNUCAS Righe di scontrino emesse da ciascuna cassa nell’anno in corso
1
2
3
4
16.544
14.303
11.790
12.237
44
11)
Visualizzare l’elenco delle casse, ordinato per numero di prodotti
decrescente, indicando per ciascuna di esse quante confezioni di
prodotti sono passate per ciascuna di esse nell’anno in corso.
SELECT VNUCAS, SUM(VQTA) AS [Numero di confezioni passate per
ciascuna cassa nell’anno in corso]
FROM VENDITE
GROUP BY VNUCAS
ORDER BY SUM(VQTA) DESC
L’output sarà simile a questo:
VNUCAS
Numero di confezioni passate per ciascuna cassa nell’anno in corso
1
4
2
3
12)
49.632
45.276
38.618
34.191
Visualizzare l’elenco delle casse, ordinato per numero di prodotti
decrescente, indicando per ciascuna di esse quante confezioni di
prodotti sono passate per ciascuna di esse nell’anno in corso.
SELECT VNUCAS, SUM(VQTA) AS [Numero di confezioni passate per
ciascuna cassa nell’anno in corso]
FROM VENDITE
GROUP BY VNUCAS
ORDER BY SUM(VQTA) DESC
L’output sarà simile a questo:
VNUCAS
1
4
2
3
Numero di confezioni passate per ciascuna cassa nell’anno in corso
49.632
45.276
38.618
34.191
45
13)
Visualizzare l’elenco delle date, ordinato per data, indicando per
ciascuna di esse quante confezioni di prodotti sono state vendute
nell’anno in corso. Le date devono essere visualizzate nella forma
gg/mm/aaaa.
SELECT MID(VDASCO, 7, 2) AS [giorno], “/”, MID(VDASCO, 5, 2) AS
[mese], “/”, MID(VDASCO, 1, 4) AS [anno], SUM(VQTA) AS [Numero di
confezioni vendute in ciascuna data di apertura nell’anno in corso]
FROM VENDITE
GROUP BY VDASCO
ORDER BY VDASCO
L’output sarà simile a questo:
giorno mese anno
02 / 01 / 2013
03 / 01 / 2013
04 / 01 / 2013
Numero di confezioni vendute in ciascuna data di
apertura nell’anno in corso
754
1.640
1.329
ecc.
14)
Visualizzare l’elenco dei codici articolo, ordinato per quantità
decrescenti, indicando per ciascuno di essi quante confezioni ne sono
state vendute nell’anno in corso.
SELECT VCODICE, SUM(VCODICE) AS [Numero di confezioni di ciascun
articolo vendute nell’anno in corso]
FROM VENDITE
GROUP BY VCODICE
ORDER BY SUM(VCODICE) DESC
46
SELECT nidificate
15)
Visualizzare i campi data, numero cassa, numero scontrino, codice
articolo, quantità, per gli articoli con quantità massima.
SELECT VDASCO, VNUCAS, VNUSCO, VCODICE, VQTA AS [Articolo
con la quantità massima venduta nell’anno in corso]
FROM VENDITE
WHERE VQTA = (SELECT MAX(VQTA) FROM VENDITE)
16)
Visualizzare i campi data, numero cassa, numero scontrino, codice
articolo, quantità, quantità media per riga di scontrino, per gli
articoli con quantità minore della metà della quantità media per riga
di scontrino.
SELECT VDASCO, VNUCAS, VNUSCO, VCODICE, VQTA AS [Quantità
venduta, che è meno della metà della quantità media per riga di
scontrino], (SELECT AVG(VQTA) FROM VENDITE) AS [Quantità media
per riga di scontrino]
FROM VENDITE
WHERE VQTA < (SELECT AVG(VQTA) FROM VENDITE) / 2
Istruzioni SQL relative al file VENDITE e al file ARTICOLI
(tra loro in relazione 1 a N), esposte alla rinfusa, per
rafforzare la capacità personale di comprensione e di
autoorientamento nell’argomento data retrieval and sort.
I due file ARTICOLI e VENDITE sono collegati da una relazione 1 a N, e
costituiscono perciò il più piccolo schema possibile (teoricamente, uno
schema, cioè un database relazionale, potrebbe essere costituito anche
da un solo file, ma questo caso è di scarsissima importanza pratica). Si
può dire, ancora meglio, che costituiscono un sottoschema dello schema
complessivo, schema che, pur piccolo, sarà presentato successivamente.
In forma grafica, il sottoschema si presenta così:
47
ARTICOLI
VENDITE
ACODICE KP
1
N
1)
VNUCAS KP(1)
VDASCO KP(2)
VNUSCO KP(3)
VCODICE KP(4) e KS
Visualizzare tutte le righe di scontrino emesse dal 2 al 7 gennaio
2013, visualizzando data, numero cassa, numero scontrino, codice
articolo, descrizione dell’articolo, codice della categoria
merceologica, quantità della confezione, unità di misura, quantità
venduta, prezzo unitario, valore della riga di scontrino, in ordine di
data, numero di cassa, numero di scontrino, valore di riga
decrescente.
SELECT VDASCO, VNUCAS, VNUSCO, VCODICE, ADESCR, ACATMERC,
AQTA, AUM, VQTA, APREZZO, APREZZO * VQTA AS [valore della riga
di scontrino]
FROM ARTICOLI, VENDITE
WHERE VDASCO BETWEEN 20130102 AND 20130107
AND ACODICE = VCODICE
ORDER BY VDASCO, VNUCAS, VNUSCO, APREZZO * VQTA DESC
Si noti che la condizione AND ACODICE = VCODICE descrive la relazione
1 a N esistente tra i due file, e realizza in questo modo l’inner join tra
essi, permettendo di riferirsi a ciascun campo dell’uno o dell’altro file
come se tutti i campi appartenessero a un solo file. In realtà non
appartengono a un unico file, ma a un unico schema.
48
2)
Visualizzare quante confezioni di pane, crackers, grissini, fette
biscottate sono state vendute nel primo trimestre 2013.
SELECT SUM(VQTA) AS [Numero di confezioni di pane, crackers,
grissini, fette biscottate vendute nel primo trimestre 2013]
FROM ARTICOLI, VENDITE
WHERE ACATMERC = “PAN” AND ACODICE = VCODICE
3) Visualizzare tutte le righe di scontrino relative alla vendita di pane,
crackers, grissini, fette biscottate, ipotizzando che il peso di tutti
questi prodotti sia espresso in grammi, visualizzando per ciascuna
riga data, numero cassa, numero scontrino, codice articolo,
descrizione dell’articolo, quantità della confezione, unità di misura,
quantità venduta, prezzo unitario, prezzo al Kg, in ordine di data,
numero di cassa, numero di scontrino, valore di riga decrescente.
SELECT VDASCO, VNUCAS, VNUSCO, VCODICE, ADESCR, AQTA, AUM,
VQTA, APREZZO, APREZZO / AQTA * 1000 AS [Prezzo al Kg per pane,
crackers, grissini, fette biscottate]
FROM ARTICOLI, VENDITE
WHERE ACATMERC = “PAN” AND ACODICE = VCODICE
ORDER BY VDASCO, VNUCAS, VNUSCO, APREZZO * VQTA DESC
4)
Visualizzare la (le) riga (righe) di scontrino di maggior valore,
visualizzando per ciascuna di queste righe data, ora, codice articolo,
descrizione dell’articolo, quantità venduta, valore, in ordine di data e
descrizione dell’articolo.
SELECT VDASCO, VORMISCO, VCODICE, ADESCR, VQTA, APREZZO *
VQTA AS [Valore della riga di scontrino, che è il massimo valore di riga
nell’anno corrente]
FROM ARTICOLI, VENDITE
WHERE APREZZO * VQTA = (SELECT MAX(APREZZO * VQTA) FROM
ARTICOLI, VENDITE WHERE ACODICE = VCODICE)
AND ACODICE = VCODICE ORDER BY VDASCO, ADESCR
49
5)
Visualizzare il guadagno lordo del negozio nei primi 6 mesi del 2013.
SELECT SUM((APREZZO – ACOSTO) * VQTA) AS [Guadagno lordo del
negozio nei primi 6 mesi del 2013]
FROM ARTICOLI, VENDITE
WHERE VDASCO BETWEEN 20130101 AND 20130630
AND ACODICE = VCODICE
Schema costituito dai tre file ARTICOLI, VENDITE,
CATMERC
Nel file ARTICOLI è presente il campo ACATMERC (il numero 5)), il cui
range è un elenco delle sigle delle categorie merceologiche previste. Se
una sigla appartiene a questo elenco è valida, altrimenti no. Questo modo
di procedere, anche se funziona, non è tuttavia il migliore, e non è
nemmeno quello tipico e caratteristico in un database relazionale. Può
andar bene se i valori possibili sono pochi (es. per un campo SESSO, M ed
F), e soprattutto se non è possibile, o è improbabile, che in futuro se ne
aggiungeranno altri. Ma per le categorie merceologiche non è così: intanto
queste categorie sono numerose, ed è probabile che se ne aggiungeranno
altre in futuro, perché il gestore del negozio vorrà raggruppare più
categorie in una sola più ampia o, caso più probabile, vorrà creare più
categorie da una categoria esistente (ad es. dall’unica categoria “PAN”
(Pane, crackers, grissini, fette biscottate) potrebbe volerne creare 4 (es.
“PA “, “CRA”, “GRI”, “FEB”) per controllare con maggior dettaglio e
precisione l’andamento delle vendite o, ancora, gliene serviranno altre
perché decide a un certo punto di aumentare l’assortimento aggiungendo
nuove linee di prodotti (es. “CPS”, cibo per struzzi, o “CPI”, cibi
espressamente prodotti per far ingrassare, o “CTP”, cestini da viaggio
tutto pronto, che contengono varie combinazioni di primi, secondi,
dessert, bevande, e così via). Insomma, sarebbe più pratico e flessibile,
invece di elencare le sigle possibili nel range del campo ACATMERC,
creare un file specifico, diciamo CATMERC, che contiene l’elenco delle
categorie merceologiche, con la possibilità in ogni momento di
aggiungerne, toglierne, variarne la descrizione. E, in più, nel file
50
CATMERC ci metteremmo anche la descrizione in chiaro di ciascuna
categoria, cosa non possibile se lasciamo le sigle solo nel campo
ACATMERC del file ARTICOLI. Ma, razza di prof, se funziona meglio con
un file specifico, perché finora non l’hai utilizzato, proponendoci una
soluzione rigida e non molto in linea con lo stile dei database relazionali?
Beati studenti, è perché in questo modo ho attirato fortemente (spero) la
vostra attenzione su questo fatto essenziale, e sulla funzione e l’utilità
dei cosiddetti file di lookup, detti anche file dizionario. E poi perché
all’inizio volevo che concentraste la vostra attenzione su un solo file, o su
due, in modo da imparare con chiarezza, e senza distrazioni, i fatti
essenziali dell’SQL. Ma adesso sì è ora di considerare lo schema completo
del nostro database, che potremmo chiamare ARTVEN. E’ comunque uno
schema piccolo, costituito da soli tre file, ma uno schema vero, che
potrebbe gestire, non nei minimi particolari, ma in modo funzionale, le
vendite di un negozio vero. Prima però bisogna definire il tracciato record
del file CATMERC, che è:
Prog. Nome
Tipo Lungh. Descrizione
(ed ev. formato)
1)
CCATMERC
AN
3
Sigla della
categoria
merceologica
2)
CDESCR
AN
30
Descrizione della
categoria
merceologica
Range
Chiavi
KP
<> “ “
Dobbiamo anche variare il range del campo 5) del file ARTICOLI, da così:
5)
ACATMERC
AN
3
Categoria merc.
PAN, PAR,
SAL, FOR,
SPE, OLA,
CAR, FRU,
VER, LAT,
ACQ, VBL
51
a così:
5)
ACATMERC
AN
3
Categoria merc.
deve esistere
in CATMERC
KS
Ora siamo in grado di disegnare lo schema del database relazionale che
abbiamo deciso di chiamare ARTVEN. Eccolo:
ARTICOLI
ACODICE KP
ACATMERC KS
VENDITE
1
N
VNUCAS KP(1)
VDASCO KP(2)
N VNUSCO KP(3)
VCODICE KP(4) e KS
N
CATMERC
1
CCATMERC KP
Schema del database relazionale ARTVEN
52
Istruzioni SQL relative al database relazionale ARTVEN,
costituito dai tre file ARTICOLI, VENDITE, CATMERC, e
dalle relazioni tra di essi.
1)
Visualizzare l’elenco di tutte le categorie merceologiche in chiaro, in
ordine alfabetico, indicando per ciascuna di esse il numero di
confezioni vendute e il valore del venduto prodotto dagli articoli che
ne fanno parte.
SELECT CDESCR, SUM(VQTA) AS [Numero di confezioni vendute],
SUM(APREZZO * VQTA) AS [Valore del venduto]
FROM ARTICOLI, VENDITE, CATMERC
WHERE ACODICE = VCODICE AND CCATMERC = ACATMERC
GROUP BY CCATMERC
ORDER BY CDESCR
2)
Visualizzare l’elenco delle categorie merceologiche in chiaro,
indicando per ciascuna di esse il numero di confezioni vendute e il
valore del venduto prodotto dagli articoli che ne fanno parte,
visualizzando solo quelle per le quali il numero di confezioni è almeno
1200 e il valore del venduto almeno 5000, in ordine dal valore del
venduto maggiore.
SELECT CDESCR, SUM(VQTA) AS [Numero di confezioni vendute],
SUM(APREZZO * VQTA) AS [Valore del venduto]
FROM ARTICOLI, VENDITE, CATMERC
WHERE ACODICE = VCODICE AND CCATMERC = ACATMERC
GROUP BY CCATMERC
HAVING SUM(VQTA) >= 1200 AND SUM(APREZZO * VQTA) >= 5000
ORDER BY SUM(APREZZO * VQTA) DESC
3)
Visualizzare l’elenco dei mesi (01, 02, ecc.) in ordine crescente,
indicando per ciascun mese il numero di righe di scontrino e il
margine lordo.
53
SELECT MID(VDASCO, 5, 2) AS [mese], COUNT(*) AS [numero di righe
di scontrino], SUM((APREZZO – ACOSTO) * VQTA) AS [margine lordo]
FROM ARTICOLI, VENDITE
WHERE ACODICE = VCODICE
GROUP BY MID(VDASCO, 5, 2)
ORDER BY MID(VDASCO, 5, 2)
Presentazione di schemi di database relazionali,
e di istruzioni SQL a partire da essi
Di seguito vengono presentati altri due schemi (cioè parti della realtà,
aziendale e non, organizzate in files e relazioni tra questi files) di
database relazionali, corredati da istruzioni SQL opportune e realistiche.
Database ARTIGIANOCHELAVORA
Consideriamo la tabella LAVORI (file di movimento), che contiene i dati
relativi ai lavori eseguiti da un artigiano presso vari clienti “privati”,
quindi non dotati di Partita IVA, nel 2012 e 2013.
Ipotesi: l’artigiano lavora con 3 tipi di materiali diversi, ma in un certo
lavoro può usarli tutti e 3, o due, o solo uno. Il prezzo unitario di ciascun
tipo di materiale può andare da 8 a 24 euro, e il costo, cioè quanto
l’artigiano paga al suo fornitore per comprare il materiale, può andare dal
30% al 78% del relativo prezzo. Ogni lavoro viene completato in giornata.
Secondo la difficoltà, il prezzo di un’ora di lavoro può variare tra 27 e 40
euro. Per le quantità di materiale, prevedere una cifra decimale. Un lavoro
può durare da mezz’ora a 11 ore, con intervalli di mezz’ora.
1)
Scrivi il tracciato record della tabella LAVORI e i tracciati
record delle altre tabelle necessarie.
2)
Disegna lo schema del database ARTIGIANOCHELAVORA.
3)
Scrivi le istruzioni SQL che realizzano le richieste seguenti:
54
A)
Visualizza i lavori eseguiti nel secondo trimestre 2012.
B)
Visualizza, per i lavori eseguiti nel 2013, il numero del lavoro, la sigla
del tipo di lavoro, il tipo di lavoro in chiaro, il codice fiscale del
cliente, il nominativo del cliente, la data di esecuzione, in ordine per
tipo di lavoro e, a parità di tipo di lavoro, in ordine dal lavoro più
recente.
C)
Visualizza, per ogni lavoro, il numero del lavoro, il nominativo del
cliente, l’importo dei materiali, l’importo della manodopera, l’importo
totale.
D)
Visualizza, per i lavori eseguiti in ottobre 2012 e che sono stati
pagati, il numero del lavoro, il codice fiscale del cliente, il nominativo
del cliente, l’importo totale, l’importo pagato dal cliente, la data di
pagamento.
E)
Visualizza i lavori che non sono stati ancora pagati dai clienti, in
ordine da quello eseguito da più tempo a quello più recente. Per ogni
lavoro visualizza numero del lavoro, data, tempo impiegato, importo
totale.
F)
Visualizza i lavori in cui sono stati utilizzati tre tipi di materiali.
G)
Visualizza i lavori nei quali l’importo dei materiali supera 500 euro.
Per ogni lavoro visualizza numero del lavoro, data, importo dei
materiali.
H)
Visualizza i lavori per i quali il cliente ha usufruito di uno sconto, in
ordine dallo sconto più alto al più basso. In ogni riga visualizza il
numero del lavoro, il nominativo del cliente, l’importo del lavoro,
l’importo effettivamente pagato dal cliente, lo sconto in euro.
I)
Visualizza i lavori eseguiti nel settembre 2012 e che sono stati
pagati più di un mese dopo. Per ciascun lavoro visualizza numero del
lavoro, nominativo del cliente, data di esecuzione, data di
pagamento.
55
Realizzazione del database proposto
Punto 1)
Scrivi il tracciato record della tabella LAVORI e i tracciati record
delle altre tabelle necessarie.
Si riporta intanto il tracciato record del file LAVORI:
Nome
Tipo e Descrizione
Lunghezza
Range
(ed ev. formato)
Chiavi
1) LNUMLAV N 4I
Numero progressivo campo automatico
KP
del lavoro (parte da
1 ogni anno)
2) LCFCLI AN 16 Codice fiscale del deve esistere in
KS
cliente
CLIENTI
3) LDATA
N 8I Data di esecuzione deve essere una
del lavoro
data (Nota 1)
4) LMATA AN 5
Codice di un tipo
deve esistere
KS
di materiale
in MATERIALI
5) LQMATA N 3I1D Quantità di un tipo
>0
di materiale
utilizzato
6) LPREZZA N 2I2D Prezzo unitario del campo automatico
materiale ev. indicato
nel campo 4)
7) LMATB AN 5
Codice di un secondo “ “ o deve esistere
KS
tipo di materiale
in MATERIALI e
deve essere <> da 4)
8) LQMATB N 3I1D Eventuale quantità 0 se 7) = “ “,
di un secondo tipo di altrimenti > 0
materiale utilizzato
9) LPREZZB N 2I2D Prezzo unitario del 0 se 8) = 0, altrimenti
materiale ev. indicato campo automatico
nel campo 7)
10) LMATC AN 5
Codice di un terzo “ “ o deve esistere
KS
tipo di materiale
in MATERIALI e
56
deve essere <> da 4)
e da 7)
0 se 10) = “ “,
altrimenti > 0
11) LQMATC N 3I1D Eventuale quantità
di un terzo tipo di
materiale utilizzato
12) LPREZZC N 2I2D Prezzo unitario del 0 se 11) = 0, altrimenti
materiale ev. indicato campo automatico
nel campo 10)
13) LORELAV N 2I1D Ore impiegate per >= 0,5 e <= 11, con
eseguire il lavoro
la cifra decimale
=a0oa5
14) LPRELAV N 2I2D Prezzo di un’ora di >= 27 e <= 40
lavoro
15) LTIPLAV AN 4 Sigla del tipo di
deve esistere in
KS
lavoro realizzato
TIPILAVORO
16) LDATAPAG N 8I Data in cui il cliente 0 o deve essere
ha pagato il lavoro una data (Nota 1)
e >= 3)
17) LIMPPAG N 5I2D Importo pagato dal 0 se 16) = 0,
cliente (possono
altrimenti > 0
esserci sconti,
e <= dell’importo
non rincari)
determinato
analiticamente
(Nota 1)
Il formato deve essere aaaammgg.
Su aaaa non si possono imporre vincoli troppo stringenti: si può dire che
dev’essere >= dell’anno di inizio attività dell’artigiano e <= dell’anno della
data di sistema.
mm dev’essere >= 1 e <= 12.
gg dev’essere:
>= 1 e <= 31 se mm = a uno dei valori 1, 3, 5, 7, 8, 10, 12;
>= 1 e <= 30 se mm = a uno dei valori 4, 6, 9, 11;
>= 1 e <= 28 se mm = 2 e aaaa non bisestile;
>= 1 e <= 29 se mm = 2 e aaaa bisestile.
57
Osservando le chiavi secondarie nel tracciato record del file LAVORI si
capisce che servono altri tre file per il database, e precisamente
CLIENTI, MATERIALI, TIPILAVORO, di cui dobbiamo definire i
tracciati record.
Tabella CLIENTI (anagrafica)
Nome
1) CCFCLI
Tipo e Descrizione
Lunghezza
AN 16
Codice fiscale del
cliente
2) CNOMIN AN 35 Cognome e nome
del cliente
3) CINDIR AN 25 Indirizzo
del cliente
4) CCAP
AN 5I CAP del cliente
Range
(ed ev. formato)
Chiavi
deve essere un
codice fiscale (Nota 2)
<> “ “
KP
<> “ “
5) CNUTELF AN 14
Numero di telefono se 6) è <> “ “ può
fisso
essere “ “, altrimenti
deve essere <> “ “
6) CNUCELL AN 10I Numero di telefonino se 5) è <> “ “ può
essere “ “, altrimenti
deve essere <> “ “
7) CMAIL
AN 40 Indirizzo email
“ “ o deve contenere
un solo carattere @
8) CDATAIN N 8I Data di inserimento deve essere una data
(vedi Nota1 del file
precedente)
9) CDATAUL N 8I Data del lavoro più campo automatico
recente effettuato
presso il cliente
10) CNUMLAV N 3I Numero di lavori
campo automatico
effettuati presso
il cliente
11) CIMPOTOT N 6I2DImporto totale
campo automatico
pagato dal cliente
58
per lavori eseguiti
12) CDEBITO N 4I2D Importo totale
campo automatico
non ancora pagato
dal cliente per lavori
eseguiti
(Nota 2)
Il formato deve essere AAAAAANNANNANNNA.
1
6 7 8 9 10 1112
1516
I primi 6 caratteri, alfabetici, si ricavano tre dal cognome e tre dal nome
del contribuente. I caratteri in posizione 7 e 8, numerici, contengono le
ultime due cifre dell’anno di nascita. Il carattere in posizione 9 è una
delle 12 lettere maiuscole A B C D E H L M P R S T, abbinate
ordinatamente ai 12 mesi dell’anno. I 4 caratteri dal 12 al 15, di cui il
primo numerico e gli altri alfabetici, rappresentano la sigla (più
precisamente, il codice catastale) del comune di nascita, e si trovano in
un file ufficiale del Ministero dell’Interno. Il carattere in posizione 16,
detto carattere di controllo, è una delle 26 lettere dell’alfabeto inglese,
e si ricava applicando un algoritmo opportuno ai 15 caratteri precedenti,
attribuendo a ciascuno di essi un peso (cioè un valore numerico) diverso,
che dipende anche dalla posizione pari o dispari. Tutti i pesi vengono
sommati; sul totale così ottenuto viene applicata l’operazione MOD 26,
che produce il resto della divisione intera tra la somma dei pesi dei
caratteri dal primo al quindicesimo e 26. Tale resto, come noto, può
andare da 0 a 25. Se il resto è 0, il carattere di controllo è A; se il resto
è 1, il carattere di controllo è B, e così via; se il resto è 25, il carattere di
controllo è Z.
Tabella MATERIALI (anagrafica e di lookup)
Nome
Tipo e Descrizione
Lunghezza
1) MCOMAT AN
5 Codice del materiale
2) MDESMAT AN 35 Descrizione del
Range
(ed ev. formato)
Chiavi
<> “ “
KP
<> “ “
59
3) MPREZZ
4) MCOSTO
materiale
N 2I2D Prezzo unitario
del materiale
N 2I2D Quanto il materiale
costa all’artigiano
>= 8 e <= 24
>= MPREZZ * 0,3 e
<= MPREZZ * 0,78
Tabella TIPILAVORO (di lookup)
Nome
Tipo e Descrizione
Lunghezza
Range
(ed ev. formato)
1) TTIPLAV AN 4 Sigla del tipo di lavoro
2) TDESLAV AN 30 Descrizione del lavoro
<> “ “
<> “ “
Chiavi
KP
Punto 2)
Disegna lo schema del database ARTIGIANOCHELAVORA.
Ora possiamo disegnare lo schema del database:
60
LAVORI
LNUMLAV
LCFCLI
N LMATA
N LMATB
LMATC
LTIPLAV
N
CLIENTI
1 CCFCLI
KP
KS
KS
N
KS
KS
KS
N
MATERIALI
1
MCOMAT
KP
KP
TIPILAVORO
1
TTIPLAV
KP
1
Schema del database relazionale ARTIGIANOCHELAVORA
Punto 3)
Scrivi le istruzioni SQL che realizzano le richieste seguenti:
A)
Visualizza i lavori eseguiti nel secondo trimestre 2012.
SELECT * FROM LAVORI
WHERE LDATA BETWEEN 20120401 AND 20120630
B)
Visualizza, per i lavori eseguiti nel 2013, il numero del lavoro, la sigla
del tipo di lavoro, il tipo di lavoro in chiaro, il codice fiscale del
cliente, il nominativo del cliente, la data di esecuzione, in ordine per
61
tipo di lavoro e, a parità di tipo di lavoro, in ordine dal lavoro più
recente.
SELECT LNUMLAV AS [Lavori eseguiti nel 2013], LTIPLAV, TDESLAV,
LCFCLI, CNOMIN, LDATA
FROM LAVORI, CLIENTI, TIPILAVORO
WHERE LDATA BETWEEN 20130101 AND 20131231 AND LCFCLI =
CCFCLI AND LTIPLAV = TTIPLAV
ORDER BY LTIPLAV, LDATA DESC
C)
Visualizza, per ogni lavoro, il numero del lavoro, il nominativo del
cliente, l’importo dei materiali, l’importo della manodopera, l’importo
totale.
SELECT LNUMLAV, CNOMIN AS [Nominativo del cliente], LQMATA *
LPREZZA + LQMATB * LPREZZB + LQMATC * LPREZZC AS [Importo dei
materiali], LORELAV * LPRELAV AS [Importo della manodopera],
LQMATA * LPREZZA + LQMATB * LPREZZB + LQMATC * LPREZZC +
LORELAV * LPRELAV AS [Importo totale]
FROM LAVORI, CLIENTI
WHERE LCFCLI = CCFCLI
D)
Visualizza, per i lavori eseguiti in ottobre 2012 e che sono stati
pagati, il numero del lavoro, il codice fiscale del cliente, il nominativo
del cliente, l’importo totale, l’importo pagato dal cliente, la data di
pagamento.
SELECT LNUMLAV, CCFCLI, CNOMIN, LQMATA * LPREZZA + LQMATB
* LPREZZB + LQMATC * LPREZZC + LORELAV * LPRELAV AS [Importo
totale del lavoro], LIMPPAG AS [Importo pagato dal cliente], LDATAPAG
AS [Data di pagamento]
FROM LAVORI, CLIENTI
WHERE LDATA BETWEEN 20121001 AND 20121031 AND LIMPPAG > 0
AND LCFCLI = CCFCLI
E)
Visualizza i lavori che non sono stati ancora pagati dai clienti, in
ordine da quello eseguito da più tempo a quello più recente. Per ogni
62
lavoro visualizza numero del lavoro, data, tempo impiegato, importo
totale, numero di telefono e numero di telefonino del cliente.
SELECT LNUMLAV, LDATA, LORELAV, LQMATA * LPREZZA + LQMATB
* LPREZZB + LQMATC * LPREZZC + LORELAV * LPRELAV AS [Importo
totale del lavoro], CNUTELF AS [Numero di telefono fisso], CNUCELL AS
[Numero di telefonino]
FROM LAVORI, CLIENTI,
WHERE LDATAPAG = 0 AND LCFCLI = CCFCLI
ORDER BY LDATA
F)
Visualizza i lavori in cui sono stati utilizzati tre tipi di materiali.
SELECT * FROM LAVORI
WHERE LQMATB > 0 AND LQMATC > 0
G)
Visualizza i lavori nei quali l’importo dei materiali supera 500 euro.
Per ogni lavoro visualizza numero del lavoro, data, importo dei
materiali.
SELECT LNUMLAV, LDATA, LQMATA * LPREZZA + LQMATB *
LPREZZB + LQMATC * LPREZZC AS [Importo dei materiali, superiore a
500 euro]
FROM LAVORI
WHERE LQMATA * LPREZZA + LQMATB * LPREZZB + LQMATC *
LPREZZC > 500
H)
Visualizza i lavori per i quali il cliente ha usufruito di uno sconto, in
ordine dallo sconto più alto al più basso. In ogni riga visualizza il
numero del lavoro, il nominativo del cliente, l’importo del lavoro,
l’importo effettivamente pagato dal cliente, lo sconto in euro.
SELECT LNUMLAV, CNOMIN, LQMATA * LPREZZA + LQMATB *
LPREZZB + LQMATC * LPREZZC + LORELAV * LPRELAV AS [Importo
totale del lavoro], LIMPPAG AS [Importo pagato dal cliente], LQMATA *
LPREZZA + LQMATB * LPREZZB + LQMATC * LPREZZC + LORELAV *
LPRELAV – LIMPPAG AS [Sconto in euro]
FROM LAVORI, CLIENTI
63
WHERE LDATAPAG > 0 AND LQMATA * LPREZZA + LQMATB *
LPREZZB + LQMATC * LPREZZC + LORELAV * LPRELAV > LIMPPAG AND
LCFCLI = CCFCLI
I)
Visualizza i lavori eseguiti nel settembre 2012 e che sono stati
pagati più di un mese dopo. Per ciascun lavoro visualizza numero del
lavoro, nominativo del cliente, data di esecuzione, data di
pagamento.
SELECT LNUMLAV, CNOMIN, LDATA AS [Data di esecuzione del
lavoro], LDATAPAG AS [Data di pagamento, successiva di più di un mese
alla data di esecuzione]
FROM LAVORI, CLIENTI
WHERE LDATAPAG – LDATA > 100
Database CASE
Il database CASE, che gestisce i dati di un insieme di unità abitative e
dei relativi proprietari, è costituito dai file ABITAZ, PROPRIETARI,
COMUNI, PROVINCE, REGIONI.
Il file
Italia.
Il file
Il file
Il file
Il file
ABITAZ contiene i dati relativi a varie abitazioni situate in tutta
PROPRIETARI contiene i dati dei proprietari delle abitazioni.
COMUNI contiene l’elenco dei comuni italiani.
PROVINCE contiene l’elenco delle province italiane.
REGIONI contiene l’elenco delle regioni italiane.
Nel database sono
DESTINAZIONI.
inoltre
presenti
i
file
ZONE,
TIPOLOGIE,
Ipotizziamo che ogni abitazione abbia un solo proprietario, che possa
avere al massimo 30 stanze e 5 garage, che la superficie complessiva
dell’abitazione possa essere al massimo 1000 metri quadri, e che la
superficie del giardino possa essere al massimo 20 volte la superficie
dell’abitazione.
64
NB
Il Codice della zona in cui si trova l’abitazione può essere: C
(Centro), I (Zona intermedia), P (Periferia), A (Campagna).
La Tipologia dell’abitazione può essere: A (Appartamento), CS (Casa
a schiera), C (Casa singola), V (Villa), VA (Villa antica), CT
(Castello).
La Destinazione dell’abitazione può essere: P (abitazione principale),
A (abitazione non principale data in affitto), S (abitazione non
principale non data in affitto, cioè abitualmente vuota), I
(abitazione inagibile).
Il valore di mercato dell’abitazione deve essere compreso tra 500 e
5.000 euro per metro quadrato della superficie dell’abitazione + la
superficie del giardino.
Rispondi alle seguenti domande:
1)
Scrivi il tracciato record dei file necessari per il database
CASE.
2)
Rappresenta lo schema del database, scrivendo vicino al nome di
ogni file, tra parentesi, se è anagrafico (A), di movimento (M),
o di lookup (L).
3)
Descrivi cosa fa la seguente istruzione SQL (completando anche
l’AS ...):
SELECT AVG(AVALORE) AS ...
FROM ABITAZ, PROPRIETARI
WHERE MID(APROP, 10, 2) > 40 AND AZONA = “C” AND ATIPO =
“A ” AND PDATANASC BETWEEN 19800520 AND 19900520 AND
APROP = PCF
Scrivi le istruzioni SQL che realizzano le richieste seguenti:
4)
Inizialmente il campo ASUPMED non è valorizzato. Valorizzalo, con
un decimale.
65
5)
Visualizza quante sono le case singole situate in periferia che hanno
un numero di garage maggiore del numero di stanze.
6)
Visualizza l’elenco delle case singole di superficie massima.
7)
Visualizza l’elenco delle 10 abitazioni con il valore più alto.
8)
Visualizza l’elenco delle 7 abitazioni con il valore più basso.
9)
Visualizza l’elenco delle abitazioni con superficie complessiva (casa +
giardino) massima. In ogni riga visualizza il comune dove si trova la
casa (in chiaro), l’indirizzo, la superficie dellabitazione, la superficie
del giardino, la superficie totale.
10)
Visualizza l’elenco delle abitazioni con valore medio per vano (vani
sono le stanze e i garage) minimo. In ogni riga visualizza il comune
dove si trova l’abitazione (in chiaro), l’indirizzo, il valore medio per
vano.
11)
Visualizza l’elenco delle case abitate dal proprietario, in ordine dal
proprietario più giovane. In ogni riga visualizza la zona in chiaro, la
tipologia in chiaro, l’indirizzo, il nominativo del proprietario, la sua
data di nascita, il comune in cui si trova la casa in chiaro.
12)
Visualizza l’elenco dei comuni “piccoli” del Veneto, cioè dei comuni
del Veneto con numero di abitanti inferiore di più del 20% del
numero di abitanti medio dei comuni del Veneto.
13)
Visualizza quanti sono gli abitanti in Italia che abitano in comuni
“grandi”, che cioè hanno più del triplo di abitanti del numero medio
di abitanti per comune.
66
Realizzazione del database proposto
Punto 1)
Scrivi il tracciato record dei file necessari per il database CASE.
Tracciato record del file ABITAZ:
Nome
Tipo e Descrizione
Lunghezza
1) ACOMUNE AN 4
2) AINDIR
AN 35
3) AZONA
AN 1
4) ATIPO
AN 2
5) ASUP
N 4I
6) ASUPGIAR N 5I
Range
(ed ev. formato)
Codice del comune
in cui si trova
l’abitazione
Via e numero civico
in cui si trova
l’abitazione
Codice della zona
in cui si trova
l’abitazione
Tipologia dell’abitazione
Superficie
dell’abitazione
(in metri quadri)
Superficie del giardino
deve esistere
in COMUNI
<> “ “
KP(2)
KS
deve esistere
in TIPOLOGIE
>= 30 e <= 1000
KS
>= 0 e <= 5) * 20
>= 1 e <= 30
8) ANUMGARA N 1I
>= 0 e <= 5
9) AVALORE N 8I2D Valore di mercato
dell’abitazione
(in euro)
10) ADESTINAZ AN 1 Destinazione
dell’abitazione
11) APROP
AN 16 Codice fiscale
KP(1) /
KS
deve esistere
in ZONE
7) ANUMSTAN N 2I Numero di stanze
Numero di garage
Chiavi
>= 500 * (5) + 6))
e <= 5000 * (5) + 6))
Deve esistere
KS
in DESTINAZIONI
Deve esistere
KS
67
del proprietario
12) AQUANTE N 2I Numero di persone
che risiedono
nell’abitazione
13) ASUPMED N 3I1D Superficie media
delle stanze
in PROPRIETARI
campo automatico
Tracciato record del file PROPRIETARI:
Nome
1) PCF
Tipo e Descrizione
Lunghezza
AN 16
Codice fiscale
del proprietario
2) PNOMIN AN 30
Cognome e nome
del proprietario
3) PINDIR AN 35 Indirizzo
del proprietario
4) PCOMUNE AN 4 Codice del comune
di residenza
del proprietario
5) PDATANASC N 8I Data di nascita
del proprietario
Range
(ed ev. formato)
Chiavi
Deve essere un
KP
codice fiscale (vedi
Nota 2 del database
ARTIGIANOCHELAVORA)
<> “ “
<> “ “
Deve esistere
in COMUNI
KS
Deve essere una data
(vedi Nota 1
nel database
ARTIGIANOCHELAVORA)
Tracciato record del file COMUNI:
Nome
Tipo e Descrizione
Lunghezza
1) CCOMUNE AN 4
Range
(ed ev. formato)
Codice del comune deve esistere nel
file ufficiale
del ministero
Chiavi
KP
68
2) CDESCR
AN 30
3) CPV
AN 2
4) CNUMAB
N 8I
Descrizione del
<> “ “
comune
Sigla della
deve esistere
provincia
in PROVINCE
Numero di abitanti
>0
del comune
KS
Tracciato record del file PROVINCE:
Nome
Tipo e Descrizione
Lunghezza
1) PVPV
AN 2
Sigla della provincia
2) PVDESCR
AN 30
3) PVREG
AN 2
Descrizione
della provincia
Sigla della regione
di appartenenza
Range
(ed ev. formato)
AA
Chiavi
KP
<> “ “
deve esistere
in REGIONI
KS
Tracciato record del file REGIONI:
Nome
Tipo e Descrizione
Lunghezza
1) RREG
AN 2I Sigla della regione
2) RDESCR
AN 30 Descrizione
della regione
Range
(ed ev. formato)
Chiavi
>= 01 e <= 21
KP
<> “ “
Tracciato record del file ZONE:
Nome
1) ZZONA
Tipo e Descrizione
Lunghezza
AN 1
Sigla della zona
Range
(ed ev. formato)
Chiavi
<> “ “
KP
69
2) ZDESCR AN 30
Descrizione
della zona
<> “ “
Tracciato record del file TIPOLOGIE:
Nome
Tipo e Descrizione
Lunghezza
1) TTIPO
AN 2
2) TDESCR
AN 30 Descrizione
della tipologia
Range
(ed ev. formato)
Sigla della tipologia
<> “ “
Chiavi
KP
<> “ “
Tracciato record del file DESTINAZIONI:
Nome
Tipo e Descrizione
Lunghezza
Range
(ed ev. formato)
Chiavi
<> “ “
KP
1) DDESTINAZ AN 1 Sigla della
destinazione
2) DDESCR AN 30 Descrizione
della destinazione
<> “ “
Punto 2)
Rappresenta lo schema del database, scrivendo vicino al nome di ogni
file, tra parentesi, se è anagrafico (A), di movimento (M), o di lookup
(L).
Ora possiamo disegnare lo schema del database:
70
ABITAZ (M)
APROP
ACOMUNE
PROPRIETARI (A)
N
N
KS
1 PCF
PCOMUNE
KP
KS
KP(1) / KS
AINDIR KP(2)
N
AZONA
KS
N
ATIPO
KS
ADESTINAZ KS
COMUNI (L)
1
CCOMUNE
CPV
N
KP
DDESTINAZ
N
1 TTIPO
PVPV
PVREG
KP
KP
KS
1
N
REGIONI (L)
RREG
ZONE (L)
1 ZZONA
PROVINCE (L)
KP
TIPOLOGIE (L)
1
KS
DESTINAZIONI (L)
1
N
KP
1
KP
Schema del database relazionale CASE
71
Punto 3)
Descrivi cosa fa la seguente istruzione SQL
(completando anche l’AS ...):
SELECT AVG(AVALORE) AS ...
FROM ABITAZ, PROPRIETARI
WHERE MID(APROP, 10, 2) > 40 AND AZONA = “C” AND ATIPO =
“A ” AND PDATANASC BETWEEN 19800520 AND 19900520 AND
APROP = PCF
Visualizza il valore medio di mercato degli appartamenti situati in centro
di cui sono proprietarie donne nate tra il 20 maggio 1980 e il 20 maggio
1990.
AS [valore medio di mercato degli appartamenti situati in centro di cui
sono proprietarie donne nate tra il 20 maggio 1980 e il 20 maggio 1990]
Scrivi le istruzioni SQL che realizzano le richieste seguenti:
4) Inizialmente il campo ASUPMED non è valorizzato. Valorizzalo, con
un decimale.
UPDATE ABITAZ
SET ASUPMED = ROUND(ASUP / (ANUMSTAN + ANUMGARA), 1)
5) Visualizza quante sono le case singole situate in periferia che hanno
un numero di garage maggiore del numero di stanze.
SELECT COUNT(*) AS [Numero di case singole situate in periferia che
hanno un numero di garage maggiore del numero di stanze]
FROM ABITAZ
WHERE ATIPO = “C “ AND AZONA = “P” AND ANUMGARA >
ANUMSTAN
72
6)
Visualizza l’elenco delle case singole di superficie massima.
SELECT *
FROM ABITAZ
WHERE ATIPO = “C “ AND ASUP = (SELECT MAX(ASUP) FROM ABITAZ
WHERE ATIPO = “C “)
7)
Visualizza l’elenco delle 10 abitazioni con il valore più alto.
SELECT TOP 10 *
FROM ABITAZ
ORDER BY AVALORE DESC
8)
Visualizza l’elenco delle 7 abitazioni con il valore più basso.
SELECT TOP 7 *
FROM ABITAZ
ORDER BY AVALORE
9)
Visualizza l’elenco delle abitazioni con superficie complessiva (casa +
giardino) massima. In ogni riga visualizza il comune dove si trova la
casa (in chiaro), l’indirizzo, la superficie dell’abitazione, la superficie
del giardino, la superficie totale.
SELECT CDESCR AS [Comune in cui si trova l’abitazione], AINDIR AS
[Indirizzo dell’abitazione], ASUP AS [Superficie dell’abitazione],
ASUPGIAR AS [Superficie del giardino], ASUP + ASUPGIAR AS
[Superficie totale (abitazione + giardino) massima]
FROM ABITAZ, COMUNI
WHERE ACOMUNE = CCOMUNE AND ASUP + ASUPGIAR = (SELECT
MAX(ASUP + ASUPGIAR) FROM ABITAZ)
10)
Visualizza l’elenco delle abitazioni con valore medio per vano (vani
sono le stanze e i garage) minimo. In ogni riga visualizza il comune
dove si trova l’abitazione (in chiaro), l’indirizzo, il valore medio per
vano.
73
SELECT CDESCR AS [Comune in cui si trova l’abitazione], AINDIR AS
[Indirizzo dell’abitazione], AVALORE / (ANUMSTAN + ANUMGARA) AS
[Valore medio per vano (stanze e garage) minimo]
FROM ABITAZ, COMUNI
WHERE ACOMUNE = CCOMUNE AND AVALORE / (ANUMSTAN +
ANUMGARA) = (SELECT MIN(AVALORE / (ANUMSTAN + ANUMGARA))
FROM ABITAZ)
11)
Visualizza l’elenco delle case abitate dal proprietario, in ordine dal
proprietario più giovane. In ogni riga visualizza la zona in chiaro, la
tipologia in chiaro, l’indirizzo, il nominativo del proprietario, la sua
data di nascita, il comune in cui si trova la casa in chiaro.
SELECT ZDESCR AS [Zona in cui si trova l’abitazione], TDESCR AS
[Tipologia dell’abitazione], AINDIR AS [Indirizzo dell’abitazione],
PNOMIN AS [Nominativo della persona che abita nella casa di cui è
proprietario, in ordine dal più giovane al meno giovane], PDATANASC AS
[Data di nascita del proprietario], CDESCR AS [Comune in cui si trova
l’abitazione]
FROM ABITAZ, PROPRIETARI, ZONE, TIPOLOGIE, COMUNI
WHERE APROP = PCF AND ACOMUNE = CCOMUNE AND AZONA =
ZZONA AND ATIPO = TTIPO AND ACOMUNE = PCOMUNE AND
AINDIR = PINDIR
ORDER BY PDATANASC DESC
12)
Visualizza l’elenco dei comuni “piccoli” del Veneto, cioè dei comuni
del Veneto con numero di abitanti inferiore di più del 20% del
numero di abitanti medio dei comuni del Veneto.
SELECT CDESCR AS [Nome del comune], CNUMAB [Numero di abitanti
dei comuni del Veneto con numero di abitanti inferiore di più del 20% del
numero medio di abitanti dei comuni del Veneto]
FROM COMUNI, PROVINCE
WHERE CPV = PVPV AND PVREG = “21” AND CNUMAB < (SELECT
AVG(CNUMAB) FROM COMUNI, PROVINCE WHERE CPV = PVPV AND
PVREG = “21”) * 0,8
74
13)
Visualizza quanti sono gli abitanti in Italia che abitano in comuni
“grandi”, che cioè hanno più del triplo di abitanti del numero medio
di abitanti per comune.
SELECT SUM(CNUMAB) AS [Numero di abitanti che in Italia abitano in
comuni che hanno più del triplo di abitanti del numero medio di abitanti
per comune]
FROM COMUNI
WHERE CNUMAB > (SELECT AVG(CNUMAB) FROM COMUNI) * 3
75
Indice
Databases (relazionali)
Cos’è un database
Modello relazionale di database (relational database)
DB e DBMS
Schema e sottoschemi
Tipi di file in un database relazionale
Come definire i file
Range di un campo
Importanza dei range
Codici e Chiavi
Codici
Chiavi
Chiave primaria (o principale; primary key)
Chiave secondaria (o esterna; foreign key)
Il Database Administrator (DBA)
Tipi di linguaggi che si utilizzano in un database relazionale
DDL
SQL
SQL (Structured Query Language) – Introduzione
Data retrieval and sort
SQL - Parole chiave e struttura dei comandi principali
SELECT
FUNZIONI DI SINTESI
Tre osservazioni importanti
UPDATE
DELETE
SQL – Esempi
File ARTICOLI (stand alone)
Istruzioni SQL relative al file ARTICOLI di cui si è
appena descritto il tracciato record
SELECT con campi dei record e campi calcolati
SELECT con campi di sintesi
1
2
3
5
8
10
13
16
17
17
21
21
21
22
23
23
24
25
26
28
28
29
29
30
30
30
30
33
33
35
76
SELECT con raggruppamento (GROUP BY)
SELECT nidificate
UPDATE
DELETE
File VENDITE (prima stand alone, e poi collegata ad ARTICOLI)
Istruzioni SQL relative al file VENDITE (stand alone)
di cui si è appena descritto il tracciato record
SELECT con campi dei record e campi calcolati
SELECT con campi di sintesi
SELECT con raggruppamento (GROUP BY)
SELECT nidificate
Istruzioni SQL relative al file VENDITE e al file
ARTICOLI (tra loro in relazione 1 a N), esposte
alla rinfusa, per rafforzare la capacità personale di
comprensione e di autoorientamento nell’argomento
data retrieval and sort
Schema costituito dai tre file ARTICOLI, VENDITE, CATMERC
Istruzioni SQL relative al database relazionale ARTVEN,
costituito dai tre file ARTICOLI, VENDITE, CATMERC,
e dalle relazioni tra di essi
Presentazione di schemi di database relazionali, e di istruzioni SQL
a partire da essi
Database ARTIGIANOCHELAVORA
Tracciati record
Schema del database
Istruzioni SQL
Database CASE
Tracciati record
Schema del database
Istruzioni SQL
37
38
40
40
41
42
42
43
44
47
47
50
53
54
54
56
61
61
64
67
71
72
77