Strutture dati e database: differenze tra le versioni

Wikibooks, manuali e libri di testo liberi.
Contenuto cancellato Contenuto aggiunto
Riga 394: Riga 394:
{, ''Nome_Colonna'' =
{, ''Nome_Colonna'' =
< ''Espressione'' | ''Select_di_SQL'' | null | default >}
< ''Espressione'' | ''Select_di_SQL'' | null | default >}
[WHERE <I>Condizione</i>]aetjhueajhuaejaehjuet5
[WHERE <I>Condizione</i>]


=== Sicurezza: l'uso dei privilegi ===
=== Sicurezza: l'uso dei privilegi ===

Versione delle 12:26, 26 gen 2007

Wikiversità
Questo libro può essere utile per
il corso di Basi di dati dell'area di Informatica

Si definiscono strutture dati i modi di organizzare secondo regole precise una certa quantità, detta anche "base", di informazioni; tali regole possono definirsi sia in forma teorica, sia in forma pratica, riferendo quest'ultima all'effettivo ordinamento fisico dei dati sulla memoria di un elaboratore elettronico, la parte di memoria di un elaboratore elettronico destinato a contenere le informazioni così organizzate prende il nome di database.


Tipologie di strutture dati

Principi base per la costruzione e la gestione di un database

Il miglior modo per comprendere come impostare una corretta struttura di un database è provare a crearne una. Un esempio classico di una base di dati può essere un elenco di persone di cui vogliamo memorizzarne il nome, il cognome, l'indirizzo, il telefono, l'età e il numero di computer posseduti.

Visualizzazione tabulare dei dati: campi e record

Il più diffuso modo di visualizzare una base di dati è inserirli in una griglia di questo tipo:

Nome Cognome Indirizzo Telefono Età Num. Computer
Mario Rossi Via Dante Alighieri, 12 - Roma 06 66555444 42 0
Luigi Bianchi Viale Alessandro Manzoni, 34 - Milano 02 46802468 34 2
Dario Verdi Corso Cesare Pavese, 56 - Torino 011 223344 38 1

Gli aspetti che interessa memorizzare di ogni persona vengono chiamati campi e sono nella tabella d'esempio le intestazioni delle colonne, le righe invece rappresentano i valori di questi aspetti per ciascuna persona, ovvero per ciascun record. Abbiamo quindi visualizzato i dati in una struttura a tabella dove in ogni cella è memorizzato un valore corrispondente ad un aspetto (campo) di una persona (record).

Capire il tipo di dati che si sta organizzando

Per strutturare correttamente dei dati, il primo passo è capire di che tipo sono, specialmente in relazione all'uso che se ne dovrà fare.

I campi "Nome" e "Cognome" conterranno dati di tipo testuale; possiamo immaginare di cercare i dati di una persona partendo dal suo cognome ed in caso di omonimia di questo, raffinando la ricerca scegliendo il nome corretto tra i vari omonimi, ecco quindi perché non teniamo uniti il nome ed il cognome in un unico campo ad esempio chiamato "Nominativo".

Il campo "Indirizzo", anch'esso di tipo testuale, invece contiene al proprio interno due aspetti che potrebbe avere senso tenere separati; immaginando di voler filtrare, tra tutte le persone solo quelle residenti a Milano e non avendo per ora nessuna capacità di trovare una specifica sequenza di caratteri all'interno di un campo, dovremo quindi scorporare l'aspetto "Città" dal campo "Indirizzo".

Il campo "Telefono" potrebbe a prima vista contenere solamente cifre, ma sarebbe un errore considerarlo di tipo numerico per svariati motivi, si potrebbe scrivere un punto o uno spazio dopo il cosiddetto prefisso, oppure indicare frasi come "ore pasti", ma soprattutto eviteremo operazioni sgradite come errori di arrotondamento automatici o la mancata visualizzazione degli zeri all'inizio del numero (dovrebbero essere considerati campi numerici solo quelli contenenti valori su cui possiamo fare delle operazioni aritmetiche, non faremo in questo caso somme o sottrazioni dei numeri di telefono delle persone nel database), questo campo sarà perciò di tipo testuale.

Il campo "Età" può essere tranquillamente di tipo numerico, ha senso ad esempio calcorare l'età media delle persone nel database, ma si è incappati in un errore: nel momento in cui si considera la vita del database nel tempo, immaginando un inserimento di dati distribuito su più di un anno, l'età delle persone inserite dovrà essere aggiornata di anno in anno; molto più sensato è memorizzare la data di nascita, o almeno l'anno di nascita. Un campo di tipo data-ora consente di solito anche di sfruttare le funzioni integrate dell'elaboratore elettronico per calcorare ad esempio il numero di giorni mancanti ad una determinata scadenza o sapere in che giorno della settimana cadeva una certa data.

Infine il campo "Numero di computer" è il tipico campo numerico, con l'unica specifica di contenere solo numeri interi, considerando assurdi dati su frazioni di computer posseduti.

Un organizzazione maggiormente corretta dei dati sopra riportati è la seguente:

Nome Cognome Indirizzo Città Telefono Anno di nascita Num. Computer
Mario Rossi Via Dante Alighieri, 12 Roma 06 66555444 1963 0
Luigi Bianchi Viale Alessandro Manzoni, 34 Milano 02 46802468 1971 2
Dario Verdi Corso Cesare Pavese, 56 Torino 011 223344 1967 1

Definire regole di convalida, maschere di input e campi obbligatori

Esiste la possibilità di bloccare l'inserimento di dati che non rispettino regole predefite, come ad esempio impedire l'inserimento di date di nascita superiori a quelle del giorno corrente o anche superiori a quelle di esattamente 18 anni fa, volendo per esempio dati di sole persone maggiorenni; per ogni regola di convalida è consigliabile, se si è in grado, far comparire un messaggio per l'operatore che inserisce il dato con la spiegazione dell'errore di inserimento eventualmente commesso.

Un'ulteriore raffinatezza è data dalle maschere di input che prevengono errori di battitura, pensiamo ad esempio il caso della partita IVA, questa deve essere scritta con esattamente 11 cifre, mettere lettere alfabetiche o una quantità non esatta di caratteri sarebbe un errore, che possiamo prevenire, consentendo la memorizzazione del dato solo se composto esattamente da 11 cifre, non una di più ne una di meno. La maschera di input può anche essere in grado di controllare la forma maiuscola o minuscola di ogni carattere digitato ed eventualmente modificarlo, si potrà quindi sempre scrivere in minuscolo avendo la certezza della correzione automatica delle iniziali di nomi e cognomi in maiuscolo.

Un'altra esigenza spesso fondamentale è rendere obbligatorio l'inserimento del valore in un determinato campo, prima di memorizzare un nuovo record; pensiamo ad un database indirizzario, usato per stampare etichette per fini postali: il campo o i campi che formano l'indirizzo completo devono essere obbligatori, in quanto non indicare nessun indirizzo equivarebbe a rendere inutile la stampa di un'etichetta.

Chiavi per identificare univocamente ogni record

Un'esigenza primaria in un database è consentire l'identificazione univoca di un record, il fisco italiano ad esempio per non confondere la dichiarazione dei redditi di due persone con nome e cognome uguale ha ideato il codice fiscale (un codice di tipo posizionale, dove cioè ogni carattere prende senso a seconda della posizione che occupa; in grado di memorizzare 3 lettere del nome, 3 lettere del cognome, le ultime 2 cifre dell'anno di nascita, un codice legato alla città di nascita e il genere maschile o femminile) confidando nel fatto che non esistano due persone con lo stesso nome, cognome, data e luogo di nascita.

Nel momento in cui anche per noi diventa importante garantire l'univocità di ogni record, aggiungeremo un campo, che ovviamente sara obbligatorio, che possiamo chiamare "ID" e che sarà la chiave univoca della tabella; questo campo potrà contenere codici personalizzati o anche più semplicemente numeri interi, progressivi con l'inserimento dei vari record, l'importante è che non esistano due record che ne abbiano lo stesso valore; il nostro database d'esempio diventa quindi:

ID Nome Cognome Indirizzo Città Telefono Anno di nascita Num. Computer
1 Mario Rossi Via Dante Alighieri, 12 Roma 06 66555444 1963 0
2 Luigi Bianchi Viale Alessandro Manzoni, 34 Milano 02 46802468 1971 2
3 Dario Verdi Corso Cesare Pavese, 56 Torino 011 223344 1967 1

I database relazionali

Si definiscono database realazionali i database che consentono di creare relazioni tra i record di più tabelle, tali relazioni saranno definite per due tabelle alla volta, utilizzando la chiave univoca della prima tabella per riferire i dati del record così identificato ad uno o più record della seconda tabella; vediamo con un esempio pratico come realizzare una relazione e quali vantaggi danno questi tipi di database.

Volendo aggiungere alla nostra tabella di esempio la ragione sociale, l'indirizzo, la città ed il numero di telefono delle ditte per cui lavora ogni persona potrebbe capitare di avere dati ridondanti (escludiamo per praticità i campi "Anno di nascita" e "Num. Computer":

ID Nome Cognome Indirizzo Città Telefono Nome ditta Indirizzo ditta Città ditta Telefono ditta
1 Mario Rossi Via Dante Alighieri, 12 Roma 06 66555444 ABC S.r.l. Largo del Lavoro, 78 Roma 06 789012345
2 Luigi Bianchi Viale Alessandro Manzoni, 34 Milano 02 46802468 DeF S.p.a. Piazza Disoccupati, 90 Milano 02 345678901
3 Dario Verdi Corso Cesare Pavese, 56 Torino 011 223344 DeF S.p.a. Piazza Disoccupati, 90 Milano 02 345678901

Può essere conveniente scrivere i dati delle ditte in una tabella separata dove ogni record è contraddistinto da una chiave univoca:

ID ditta Nome ditta Indirizzo ditta Città ditta Telefono ditta
1 ABC S.r.l. Largo del Lavoro, 78 Roma 06 78901234
2 DeF S.p.a. Piazza Disoccupati, 90 Milano 02 34567890

e trasformare la tabella delle persone nel seguente modo:

ID Nome Cognome Indirizzo Città Telefono ID ditta
1 Mario Rossi Via Dante Alighieri, 12 Roma 06 66555444 1
2 Luigi Bianchi Viale Alessandro Manzoni, 34 Milano 02 46802468 2
3 Dario Verdi Corso Cesare Pavese, 56 Torino 011 223344 2

il vantaggio più evidente è quello di dover scrivere una volta sola i dati delle ditte, risparmiando tempo ed evitando errori di battitura; basterà predisporre, nella tabella delle persone, un campo destinato a contenere solo le chiavi univoche dei record della tabella delle ditte; la possibilità di inserire in questo campo solo il valore di una precisa chiave univoca di una ditta garantisce il corretto riferimento di ogni persona all'esatta ditta per cui lavora.

Tipi di relazioni e strutture che ne conseguono

Esistono due tipi di relazioni: le relazioni uno a uno e le relazioni uno a molti; vediamo che differenza c'è con due esempi: riferiamo ad ogni persona i dati del suo reddito e riferiamo ad ogni ditta i dati dei suoi dipendenti.

Immaginando di voler memorizzare la situazione dei redditi di ciascuna persona, potremmo aggiungere alla tabella delle persone quattro campi "Reddito da lavoro dipendente", "Redditto da rendite immobiliari", "Reddito da rendite finanziarie", "Tasse da pagare" (questi campi saranno di tipo valuta):

ID persona Nome Cognome Indirizzo Città Telefono Redd. Lav. Dip. Redd. Rend. Imm. Redd. Rend. Fin. Tasse da pagare
1 Mario Rossi Via Dante Alighieri, 12 Roma 06 66555444        
2 Luigi Bianchi Viale Alessandro Manzoni, 34 Milano 02 46802468 10.000 Eur. 2.000 Eur. 1.000 Eur. 750 Eur.
3 Dario Verdi Corso Cesare Pavese, 56 Torino 011 223344 15.000 Eur. 5.000 Eur. 20 Eur. 1.200 Eur.
4 Carlo Marroni Vicolo Luigi Pirandello, 10 Palermo 09 90990099        
5 Marco Celesti Strada Nino Costa, 34 Moncalieri 011 1001100 500 Eur. 0 Eur. 0 Eur. 20 Eur.

(da notare che il fatto di non conoscere un dato non vuol dire che questo sia uguale a zero, sebbene così pensino gli evasori fiscali)
utilizzando il campo "ID persona", possiamo scorporare i dati sul reddito, indicandoli solo per le persone di cui lo sappiamo, in una nuova tabella dei redditi:

ID persona Redd. Lav. Dip. Redd. Rend. Imm. Redd. Rend. Fin. Tasse da pagare
2 10.000 Eur. 2.000 Eur. 1.000 Eur. 750 Eur.
3 15.000 Eur. 5.000 Eur. 20 Eur. 1.200 Eur.
5 500 Eur. 0 Eur. 0 Eur. 20 Eur.

la tabella delle persone tornerà ad essere più "leggera" non contenendo campi che spesso possono risultare vuoti:

ID persona Nome Cognome Indirizzo Città Telefono
1 Mario Rossi Via Dante Alighieri, 12 Roma 06 66555444
2 Luigi Bianchi Viale Alessandro Manzoni, 34 Milano 02 46802468
3 Dario Verdi Corso Cesare Pavese, 56 Torino 011 223344
4 Carlo Marroni Vicolo Luigi Pirandello, 10 Palermo 09 90990099
5 Marco Celesti Strada Nino Costa, 34 Moncalieri 011 1001100

il fatto che ogni record della tabella dei redditi è in relazione con un unico record della tabella delle persone, definisce che le due tabelle siano in relazione di tipo uno a uno.
Mentre ogni record della tabella dei redditi deve essere obbligatoriamente riferito ad un record della tabella delle persone (avere dati sul reddito di qualcuno che non si può individuare non ha alcun valore), non necessariamente dobbiamo sempre associare un reddito ad ogni persona, quindi possiamo non avere un record nella tabella dei redditi per ogni record della tabella persone; ciò rappresenta un vantaggio in termini di occupazione della memoria dell'elaboratore elettronico (in quanto memorizzare record con valori vuoti nei campi, occupa più spazio che non memorizzare affatto alcun record); ne consegue anche che il numero di record nella tabella dei redditi sarà minore o al massimo uguale a quello della tabella delle persone.

Immaginando di voler memorizzare i dati dei dipendenti di alcune ditte, possiamo riciclare i dati di un esempio precedente, stavolta considendolo dal punto di vista delle ditte:

ID ditta Nome ditta Indirizzo ditta Città ditta Telefono ditta
1 ABC S.r.l. Largo del Lavoro, 78 Roma 06 78901234
2 DeF S.p.a. Piazza Disoccupati, 90 Milano 02 34567890
3 GHi S.a.s. Piazza Sindacato, 12 Torino 011 2343211

la tabella delle persone diventa la tabella dei dipendenti e conterrà solo i dati dei lavoratori presso qualcuna delle ditte sopra citate:

ID ditta Nome Cognome Indirizzo Città Telefono
1 Mario Rossi Via Dante Alighieri, 12 Roma 06 66555444
2 Luigi Bianchi Viale Alessandro Manzoni, 34 Milano 02 46802468
2 Dario Verdi Corso Cesare Pavese, 56 Torino 011 223344
3 Carlo Marroni Vicolo Luigi Pirandello, 10 Palermo 09 90990099
1 Marco Celesti Strada Nino Costa, 34 Moncalieri 011 1001100

il fatto che ogni record della tabella delle ditte è il relazione con uno o più record della tabella dei dipendenti, definisce che le due tabelle siano in relazione di tipo uno a molti.


La relazione "molti a molti"

La relazione molti a molti tra due tabelle si può realizzare ma in maniera non diretta, creando una tabella in grado associare con molteplici combinazioni le chiavi univoche di due tabelle; per fare un esempio pratico e capire l'utilizzo di questa relazione possiamo immaginare una tabella per memorizzare film e attori, ovviamente un film viene interpretato da più attori ed un attore nella sua carriera può aver partecipato a più film; creeremo quindi una tabella degli attori:

ID attore Nome Cognome
1 Antonio Albanese
2 Gerard Depardieu
3 Clint Eastwood
4 Al Pacino
5 Luca Zingaretti

una tabella dei film:

ID film Titolo Anno
1 Pitch 1997
2 Tu ridi 1998
3 Amici miei '400 2004
4 Epreuves d'artistes 2004

e una che realizza la relazione molti a molti:

ID attore ID film
1 2
2 1
2 3
2 4
3 4
4 1
5 2
5 3

Notiamo che nel caso estremo si arriva ad associare tutti i record di una tabella A con tutti i record di una tabella B, in una tabella C con un numero di record pari a quelli della tabella A moltiplicati per il numero di record della tabella B.

Per memorizzare gli stessi dati senza relazioni avremmo dovuto creare questa tabella:

Nome attore Cognome attore Titolo film Anno film
Antonio Albanese Tu ridi 1998
Gerard Depardieu Pitch 1997
Gerard Depardieu Amici miei '400 2004
Gerard Depardieu Epreuves d'artistes 2004
Clint Eastwood Epreuves d'artistes 2004
Al Pacino Pitch 1997
Luca Zingaretti Tu ridi 1998
Luca Zingaretti Amici miei '400 2004

Un evoluzione interessante della tabella che realizza la relazione molti a molti è quella di aggiungere dati relativi alla specifica interazione tra i due aspetti associati, immaginiamo di voler indicare i minuti per cui un attore compare in un film in un campo "minuti":

ID attore ID film minuti di recitazione
1 2 26
2 1 14
2 3 22
2 4 9
3 4 62
4 1 18
5 2 31
5 3 27

(i minuti indicati sono stati indicati casualmente)

Questo è senza dubbio il miglior modo per realizzare la relazione molti a molti, ma può essere piuttosto complessa da implementare per un utente alle prime armi, esiste anche un'altra possibilità più semplice basata sul concetto di "molte relazioni uno a molti" dove però il "molte" è un numero ben definito di relazioni; immaginando un numero massimo di 4 film che vogliamo memorizzare per ogni attore:

ID attore Nome Cognome ID film A ID film B ID film C ID film D
1 Antonio Albanese 2      
2 Gerard Depardieu 1 3 4  
3 Clint Eastwood 4      
4 Al Pacino 1      
5 Luca Zingaretti 2 3    

Incappiamo però nell'inconveniente già precedentemente analizzato della creazione di campi che saranno spesso vuoti o peggio ancora della non possibilità di inserire un quinto film altrettanto importante quanto i primi quattro inseriti.

L'integrità referenziale

Possiamo immaginare l'integrità referenziale come un insieme di regole che garantiscono l'integrità dei dati quando si hanno relazioni associate tra loro attraverso una chiave esterna: queste regole sevono per rendere valide le associazioni tra le tabelle e per eliminare gli errori di inserimento, cancellazione o modifica di dati collegati tra loro.

Il linguaggio SQL

Premessa

La sigla SQL sta per Structured Query Language ovvero si tratta di un linguaggio strutturato per interrogare i database. In origine era chiamato SEQUEL (Structured English QUEry Language).


Definizione dei dati

Definizioni di schemi, tabelle, vincoli e primary key

Creazione database e tabelle

Interrogare un database: istruzione SELECT

La sintassi del comando SELECT è definita in questo modo:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Nome_Tabella [[as] alias]}
[WHERE Condizione]

Join

La sintassi di interrogazione prevista per implementare al suo interno le condizioni di join è la seguente:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Tipo_di_Join join Nome_Tabella [[as] alias] on
    Condizione_di_Join}
[WHERE Condizione]

Ordinamento

Durante un'interrogazione dei dati presenti nel nostro database, utilizzando la clasula Order by riusciamo a stabilire dei particolari criteri di ordinamento:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Nome_Tabella [[as] alias]}
[WHERE Condizione]
[ORDER BY Nome_Colonna [asc|desc]
            {, Nome_Colonna [asc|desc]}]

Operatori aggregati

Interrogazioni con raggruppamento

Interrogazioni di tipo insiemistico

Interrogazioni nidificate

Sintassi completa del comando SELECT

Possiamo, ora, scrivere una sintassi più completa per l'interrogazione del nostro database tramite il comando SELECT:

SELECT Espressione_su_Colonna [[as] Nuovo_Nome_Colonna]
       {, Espressione_su_Colonna [[as] Nuovo_Nome Colonna]}
FROM Nome_Tabella [[as] alias]
       {, Tipo_di_Join join Nome_Tabella [[as] alias] on
    Condizione_di_Join}
[WHERE Condizione]
[ORDER BY Nome_Colonna [asc|desc]
            {, Nome_Colonna [asc|desc]}]
[GROUP BY Nome_colonna]
[HAVING Condizione_su_colonne_aggregate]

Modifiche di schemi di tabelle: istruzione ALTER TABLE

Manipolazione dei dati: INSERT, DELETE, UPDATE

Inserimento righe

Il comando che permette di popolare il database è INSERT. La sua sintassi più utilizzata è la seguente:

INSERT INTO Nome_Tabella [( Lista_Colonne )]
  VALUES ( Lista_Valori

In alternativa si può anche usare:

INSERT INTO Nome_Tabella [( Lista_Colonne )]
   ( Select_di_SQL )

La quale permette di inserire nella tabella tutte quelle righe che sono il risultato del costrutto SELECT.

Eliminazione righe

Per eliminare righe da una tabella si usa il comando DELETE:

DELETE FROM Nome_Tabella [WHERE Condizione]

Modificare le righe

Con il comando UPDATE riusciamo a modificare i valori presenti nel database. La sintassi è la seguente:

UPDATE Nome_Tabella
    SET Nome_Colonna = 
    < Espressione | Select_di_SQL | null | default >
    {, Nome_Colonna = 
    < Espressione | Select_di_SQL | null | default >}
[WHERE Condizione]

Sicurezza: l'uso dei privilegi

Manuali d'utilizzo dei software di gestione dei database

Microsoft Access (versioni 97 e superiori)

Le tabelle

Le query

Le maschere

I report

Le macro

I moduli

OpenOffice.org Base

MySQL

PostgreSQL

Link correlati