Untitled - Aracne editrice

A01
87
Salvatore Sessa
Ferdinando Di Martino
Michele Giordano
Databases relazionali
e architetture dei RDBMS
Introduzione ai databases relazionali
e all’uso di Access
Copyright © MMVI
ARACNE editrice S.r.l.
www.aracneeditrice.it
[email protected]
via Raffaele Garofalo, 133 A/B
00173 Roma
(06) 93781065
ISBN
978 –88–548–0583–5
I diritti di traduzione, di memorizzazione elettronica,
di riproduzione e di adattamento anche parziale,
con qualsiasi mezzo, sono riservati per tutti i Paesi.
Non sono assolutamente consentite le fotocopie
senza il permesso scritto dell’Editore.
I edizione: maggio 2006
Indice
1
Le basi di dati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.1 Le basi di dati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2 Le basi di dati relazionali . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.1 Algebra relazionale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.2 Unione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.3 Intersezione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.4 Differenza . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.5 Prodotto . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.6 Selezione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.7 Proiezione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.8 Join naturale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.9 Viste . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1.2.10 Indici . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1
1
3
6
6
8
9
9
10
11
11
12
12
2
Relational Database Management System (RDBMS) . . . . . . . . . . . .
2.1 RDBMS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1.1 Architettura . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1.2 Le funzionalità del DBA . . . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1.3 Architetture client/server . . . . . . . . . . . . . . . . . . . . . . . . . .
2.1.4 Funzioni di Data Warehouse . . . . . . . . . . . . . . . . . . . . . . .
15
15
15
15
17
21
3
Modelli concettuali e logici. Riduzioni in forma normale . . . . . . .
3.1 Modelli concettuali e logici . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.2 Il diagramma Entità/Relazioni . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.3 Organizzazione dei dati . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.4 Le forme normali . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.4.1 Prima Forma normale . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.4.2 Seconda Forma normale . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.4.3 Terza forma normale . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.4.4 Quarta forma normale . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
3.5 La progettazione logica dei dati . . . . . . . . . . . . . . . . . . . . . . . . . . .
23
23
24
30
31
32
33
35
37
40
VI
Indice
4
Il linguaggio SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.1 Il linguaggio SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2 DDL (Data Definition Language) . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2.1 CREATE TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2.2 ALTER TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2.3 DROP TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.2.4 Altri comandi DDL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3 DML (Data Manipulation Language) . . . . . . . . . . . . . . . . . . . . . .
4.3.1 Il comando SELECT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3.2 Il comando INSERT . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3.3 Il comando UPDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
4.3.4 Il comando DELETE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
43
43
44
44
45
46
46
47
47
57
58
59
5
Uso del RDBMS ACCESS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.1 Il tool MICROSOFT ACCESS . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.2 Le tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.2.1 La creazione di tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.3 Le forme normali . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.4 Relazioni tra tabelle . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
5.4.1 Le relazioni tra tabelle in MICROSOFT ACCESS . . . . . .
5.5 La gestione dei records in MICROSOFT ACCESS . . . . . . . . . . . .
5.6 Uso delle Query in MICROFOST ACCESS . . . . . . . . . . . . . . . . . .
61
61
62
62
68
74
74
80
85
6
Creare Query con QBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.1 Le Query in MICROSOFT ACCESS . . . . . . . . . . . . . . . . . . . . . . . . .
6.2 Esempi d’uso di griglie QBE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2.1 Query di selezione . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2.2 Query a campi incrociati . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2.3 Query di aggiornamento . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2.4 Query di accodamento . . . . . . . . . . . . . . . . . . . . . . . . . . . .
6.2.5 Query di eliminazione . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
91
91
91
91
97
99
99
100
7
Maschere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.1 Creazione e modifica delle maschere . . . . . . . . . . . . . . . . . . . . . .
7.1.1 Autocomposizione creazione maschere . . . . . . . . . . . . .
7.1.2 Creare una maschera in visualizzazione struttura . . . .
7.1.3 I controlli delle maschere . . . . . . . . . . . . . . . . . . . . . . . . . .
7.1.4 Intestazioni, sezioni dei dettagli, piè di pagina . . . . . . .
7.1.5 Altri tipi di maschere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.2 Impostazioni delle proprietà . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
7.2.1 Proprietà della formattazione . . . . . . . . . . . . . . . . . . . . . .
7.2.2 Proprietà barre di scorrimento . . . . . . . . . . . . . . . . . . . . .
7.2.3 Proprietà abilitazione e blocco dei controlli . . . . . . . . . .
7.3 Ricerca ed ordinamento dei dati . . . . . . . . . . . . . . . . . . . . . . . . . .
7.3.1 Ordinamento rapido nel campo di una maschera . . . .
105
106
106
109
110
112
113
116
116
117
117
118
118
Indice
VII
7.3.2 Utilizzo dei filtri in base a maschera . . . . . . . . . . . . . . . . . 118
7.4 Stampa delle maschere . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
8
Report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.1 Impieghi dei report . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
8.1.1 Nuovo report utilizzando Autocomposizione . . . . . . . .
8.1.2 Nuovo report in Visualizzazione Struttura . . . . . . . . . . .
121
121
121
124
9
Criteri di protezione del database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
9.1 Protezioni di accesso . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 127
9.2 Utenti e gruppi . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128
Riferimenti bibliografici . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137
Introduzione
Questo libro deve essere visto come una introduzione ai RDBMS (Relational Data Base Management System) che è un tool software corredato
da funzionalità che permettono la gestione corretta e completa di database
relazionali. Il libro è organizzato in nove capitoli. I primi quattro Capitoli
sono dedicati, nell’ordine, ai concetti matematici fondamentali di basi di
dati, alle architetture dei RDBMS, alla progettazione logica dei dati ed al linguaggio SQL. I secondi cinque Capitoli sono dedicati ad una introduzione
all’uso di MICROSOFT ACCESS e trattano, nell’ordine, delle tabelle e loro
relazioni, della creazione di diversi tipi di query, delle maschere, dei report
e dei criteri di protezione.
Napoli, aprile 2006
Gli Autori
Salvatore Sessa
Ferdinando Di Martino
Michele Giordano
1
Le basi di dati
1.1 Le basi di dati
Per sistema informativo si intende quel sistema che permette la disponibilità e la gestione delle informazioni. L’esistenza di un sistema informativo è indipendente dalla sua automazione; lo dimostra il fatto che archivi
e servizi anagrafici esistono da vari secoli. Le informazioni possono essere scambiate in varie forme e modalità. Per gestire un complesso corposo e integrato di informazioni, è nata l’esigenza di individuare opportune
codifiche per la memorizzazione dei dati. Nei sistemi informativi le informazioni vengono rappresentate per mezzo di dati che hanno bisogno di
essere interpretate.
Una base di dati consiste genericamente in una collezione di dati utilizzati per rappresentare le informazioni di interesse per un sistema informativo. Ogni struttura (ad esempio una banca, una compagnia d’assicurazioni, un’azienda) possiede una propria base di dati che aggiorna con
una certa frequenza. Inoltre, quando viene introdotta una nuova procedura occorre anzitutto “ereditare” (= importare) i dati dalla vecchia procedura
con le necessarie trasformazioni. Questa caratteristica di stabilità porta ad
affermare che i dati costituiscono una “risorsa” per l’organizzazione che li
gestisce, un patrimonio significativo da sfruttare e proteggere. Le normative
attuali in fatto di privacy e tutela delle basi di dati lo dimostrano.
Sebbene la gestione dei dati abbia catalizzato, fin dalla nascita dell’informatica, l’attenzione delle applicazioni, solo negli anni settanta nascono
linguaggi specificatamente dedicati alla gestione dei dati. L’approccio “convenzionale” alla gestione dei dati consiste nello sfruttare la presenza di
archivi o file da memorizzare in modo persistente sulla memoria di massa. Tale approccio presenta due seri problemi: la ricerca dei dati e la loro
condivisione. Infatti con una simile metodologia di lavoro, ogni utente lavora con la propria copia “locale” dei dati e quindi con i relativi problemi di
ridondanza e di incoerenza. Le basi di dati sono state concepite per ovviare
ad inconvenienti di questo tipo.
2
1 Le basi di dati
Un sistema di gestione di basi di dati, detto DBMS (Data Base Management System) è un sistema software in grado di:
• gestire grandi quantità di dati, permetterne la condivisione e assicurarne la persistenza;
• assicurare la loro affidabilità e privatezza.
Una base di dati è una collezione di dati gestita da un DBMS. Quindi, un
DBMS si occupa di gestire basi di dati:
• di grandi dimensioni: un DBMS deve essere in grado di gestire memorie
secondarie;
• condivise: un DBMS deve permettere a più utenti di accedere contemporaneamente ai dati comuni. Per controllare l’accesso condiviso di più
utenti il DBMS dispone di un meccanismo apposito, detto controllo di
concorrenza;
• affidabili: un DBMS deve garantire l’integrità dei dati anche in caso
di malfunzionamento hardware e software, prevedendo almeno procedure di recupero dei dati. I DBMS forniscono, per tali scopi, procedure
di salvataggio e ripristino della base di dati (backup e recovery);
• con criteri di privatezza: i DBMS gestiscono un sistema di autorizzazioni
che definisce i diritti di ciascun utente ( amministratore, lettura, scrittura su archivi, ecc.).
Un modello di dati è un approccio formale utilizzato per organizzare i
dati e descriverne la struttura in modo che essa risulti comprensibile ad un
elaboratore.
Ogni modello di dati fornisce meccanismi di strutturazione, analoghi
ai costruttori di tipo dei linguaggi di programmazione, che permettono di
definire nuovi tipi sulla base di tipi elementari predefiniti.
Il modello relazionale dei dati permette di definire tipi per mezzo del
costruttore di relazione, che consente di organizzare i dati in insiemi di record a struttura fissa o righe. Una relazione viene spesso rappresentata mediante una tabella in cui le righe rappresentano specifici record e le colonne
corrispondono ai campi dei record.
Esistono, oltre al modello relazionale, altri modelli di database quali:
• il modello gerarchico in cui i dati sono strutturati su base gerarchica;
• il modello reticolare in cui i dati sono strutturati in forma reticolare;
• il modello ad oggetti che utilizza i principi del paradigma “object oriented”.
Tutti i modelli di basi di dati sono costituiti da una parte che rimane
invariata nel tempo, detta schema, e da una parte, costituita dai valori
effettivi, detta istanza.
Esiste una proposta di struttura standardizzata per i DBMS articolata
su tre livelli, detti esterno, logico e interno; per ciascun livello esiste uno
schema:
1.2 Le basi di dati relazionali
3
• lo schema logico (o concettuale), che costituisce la descrizione dell’intera base di dati secondo il modello logico adottato (relazionale o gerarchico o reticolare od a oggetti);
• lo schema interno (o fisico), che costituisce la rappresentazione dello
schema logico per mezzo di strutture fisiche di memorizzazione;
• lo schema esterno, che costituisce la descrizione di una porzione della
base di dati di interesse utilizzando viste (views), in base alle esigenze di
visualizzazione dell’utente.
L’architettura, così definita, garantisce l’indipendenza della progettazione logica dei dati da quella fisica ed esterna. In particolare:
• l’indipendenza fisica consente di interagire con il DBMS in modo indipendente dalla struttura fisica dei dati. In base a questa proprietà, è
possibile modificare le strutture fisiche senza influire sulle descrizioni
dei dati ad alto livello e quindi sui programmi che utilizzano i dati stessi;
• l’indipendenza logica consente di interagire con il livello esterno della base di dati in modo indipendente dal livello logico, in modo che
l’utente possa non avere conoscenza dello schema logico del database.
1.2 Le basi di dati relazionali
Il modello relazionale pone i suoi fondamenti nell’algebra relazionale.
Il modello relazionale risponde al requisito dell’indipendenza del modello logico da quello fisico. Gli utenti di database relazionale interagiscono solo col livello logico e quindi non è necessario che essi conoscano le
strutture fisiche della base di dati.
Questo requisito è responsabile del successo del modello relazionale
rispetto ai DBMS reticolare e gerarchico che obbligavano gli utilizzatori a
conoscerne, almeno a grandi linee, la struttura realizzativa.
In insiemistica una relazione è legata al concetto di prodotto cartesiano
tra due insiemi.
Il prodotto cartesiano
D1 × D2
di due insiemi D1 e D2 è l’insieme delle coppie ordinate (v1 , v2 ) tale che
v1 è un elemento di D1 e v2 è un elemento di D2 . Una relazione binaria è
un sottoinsieme del prodotto cartesiano di due insiemi, detti domini della
relazione. Estendendo il concetto di prodotto cartesiano D1 × D2 × . . . × Dn
di n insiemi, una relazione è costituita da un sottoinsieme di D1 × D2 × . . . ×
Dn .
Ad esempio, dati due insiemi A, B dove A = {1, 2, 3} e B = {h, k}, il
prodotto cartesiano è uguale all’insieme
A × B = {(1, h), (2, h), (3, h), (1, k), (2, k), (3, k)}
mentre una relazione possibile è {(1, h), (1, k), (3, h)}.
4
1 Le basi di dati
Come ulteriore esempio,consideriamo tre insiemi A, B, C dove A =
{1, 2, 3}, B = {h, k}, C = {SI,NO} una relazione possibile è:
{(1, h, SI), (1, k, N O), (2, k, N O), (3, h, SI)}
che costituisce un sottoinsieme del prodotto cartesiano A × B × C, formato
da tre elementi o 3-ple ordinate.
Il numero n degli insiemi che compongono il prodotto cartesiano è detto grado del prodotto cartesiano e della relazione. Il numero delle n-ple
della relazione è detta cardinalità della relazione.
Una relazione è un insieme di n-ple ordinate in quanto l’i-esimo valore di ogni n-pla appartiene all’esimo insieme (o dominio) del prodotto
cartesiano.
Adesso si consideri la relazione LAUREATI relativa agli studenti di un
corso di laurea che hanno superato l’esame di laurea.
LAUREATI=
(Rossi, Giulio, 1033, 10/10/1970, 11/04/1995),
(Bianchi, Laura, 1034, 30/12/1970,23/11/1995),
(Verdi, Ernesto, 1037, 04/08/1970,18/05/1994)
con LAUREATI ⊆ COGNOME × NOME × MATRICOLA × DATA × DATA. Il
dominio “data” compare due volte nel prodotto cartesiano indicando la data di nascita e la data di laurea. Per facilitare il riferimento ai domini della
relazione, è necessario definire un’applicazione dom: A → D tra la qualifica del dominio o attributo e il dominio stesso. Considerando l’esempio
precedente, si ha:
dom(Cognome) = Cognome
dom(Nome) = Nome
dom(Numero di Matricola) = Matricola
dom(Data Nascita) = Data
dom(Data Laurea) = Data
Mediante tale funzione, è possibile associare a ogni occorrenza del dominio della relazione un attributo univoco che qualifica il ruolo del dominio. Una n-pla può allora essere definita come un insieme di n-ple di
coppie (attributo, valore) e non più come una lista ordinata di valori.
Ad esempio, la prima n-pla della relazione LAUREATI può essere definita come: (Cognome,Rossi), (Nome,Giulio),(Matricola, 1033), (Data Nascita,
10/10/1970), (Data Laurea, 11/04/1995). Le relazioni possono, quindi, essere identificate da n-ple, in cui l’elemento è individuato tramite posizione
oppure tramite i suoi attributi. Le tabelle nascono dall’esigenza di rappresentare visivamente le relazioni presentandole in una forma più facilmente
comprensibile.
Le righe della tabella rappresentano le n-ple mentre le colonne ne rappresentano gli attributi o i campi. É importante chiarire che in una relazione
1.2 Le basi di dati relazionali
5
non vi è alcun ordinamento fra le n-ple che la compongono; nelle tabelle
che la rappresentano, l’ordine c’è per necessità, ma è occasionale in quanto due tabelle con le stesse righe, ma in ordine diverso, rappresentano la
stessa relazione. Inoltre le n-ple di una relazione sono distinte l’una dall’altra, in quanto tra gli elementi di un insieme non possono essere presenti
due elementi uguali; da cui si deduce che una tabella può rappresentare
una relazione solo se le righe che la formano sono diverse l’una dall’altra.
Un’importante caratteristica dei sistemi relazionali è la gestione dei valori nulli o ignoti; è possibile assegnare un valore nullo ad un attributo
di una riga di una tabella. Tale valore è detto null. Ad esempio, si prenda
la Tabella 1.1 (STUDENTI), i cui campi rappresentano, oltre al numero di
matricola, i voti ottenuti relativamente ai singoli esami.
Matricola
Inglese
Francese
Spagnolo
0015
0016
0017
0018
NULL
30
25
24
28
30
NULL
22
NULL
28
NULL
27
Le quattro istanze della relazione sono rappresentate come quattro record della tabella STUDENTI. I valori NULL, presenti in alcuni campi, stanno a significare o che lo studente non ha ancora effettuato l’esame oppure
che lo studente ha terminato i corsi senza superare l’esame. In ogni caso,
un dato NULL indica un valore non noto oppure inesistente. In un sistema
relazionale è anche necessario prevedere se un attributo di una tabella può
assumere un valore nullo oppure sapere che tale valore debba essere certo.
Una ulteriore caratteristica dei database relazionali sono i vincoli di
integrità, che permettono di rispettare rigidamente le regole di integrità
logica del database. I vincoli di integrità si dividono in:
• vincoli intra–relazionali, che impongono vincoli interni alle relazioni
(ad esempio, il sesso di un impiegato può assumere solo i valori “M”
ed “F”, il cognome non può assumere valore nullo, ecc.)
• vincoli extra–relazionali, che impongono vincoli tra le relazioni (ad esempio, il valore del campo dipartimento della tabella IMPIEGATI deve
essere compreso nell’insieme dei valori del campo dipartimento della
tabella DIPARTIMENTI).
Un vincolo intra–relazionale, al contrario di quello extra–relazionale,
trova il suo soddisfacimento rispetto alle singole relazioni del DBMS; esso
può essere suddiviso in:
• vincolo di n-pla: esso impone condizioni sui valori della singola n-pla
indipendentemente da quelli assunti dalle altre n-ple (ad esempio, il
campo lode della tabella ESAME può essere pari a “SI” solo se il campo
voto è pari a 30).