DBMS e Access - 1
ARCHIVI E BASI DI DATI
Un file (archivio) è una raccolta di record logici. La gestione dei file è un servizio reso dal Sistema Operativo.
Ogni record logico contiene una sequenza di bit ed è suddiviso in campi
Ogni campo contiene una differente informazione.
Esempio:
ROSSINI GIORGIO Via Nappi 6 - Milano 2372445
campo1:
nome
campo2:
indirizzo
campo3:
telefono
Meccanismi per l’inserimento, la cancellazione, la modifica e la ricerca dei dati.
Un database (base di dati) è una raccolta di dati che vengono organizzati e gestiti da un sistema software specifico detto DBMS (Data Base Management System) che opera al di sopra del Sistema Operativo.
DBMS e Access - 2
Organizzazioni dei file:
• sequenziale (eventualmente ordinata) I record sono letti o scritti uno dopo l'altro.
Inserimento, cancellazione e modifica di un file sequenziale presentano problemi, in quanto richiedono di norma lo spostamento di tutti i record che seguono il punto di intervento.
• ad accesso diretto
L’allocazione dei record in memoria di massa è definita da un algoritmo che genera un indirizzo a partire dal valore assunto da un campo del record detto chiave.
Il vantaggio delle strutture ad accesso diretto è la rapidità della ricerca: in assenza di conflitti ogni ricerca richiede una sola operazione di ingresso/uscita.
• a indice
In queste strutture, come in quelle ad accesso diretto, è il valore di un campo chiave che determina il record a cui accedere. La corrispondenza fra la chiave e l’indirizzo non è mantenuta esplicitamente mediante un file ausiliario detto file indice.
Su uno stesso archivio è possibile avere diversi file indice relativi a chiavi diverse.
BASI DI DATI
Le basi di dati sono sorte alla fine degli anni ’60.
Prima i sistemi informativi si basavano sull’uso di files separati.
I programmi applicativi accedevano ai files individualmente, utilizzando procedure del Sistema Operativo.
Problemi:
1) Inconsistenza e ridondanza dei dati. I dati sono duplicati in file diversi e quindi le modifiche possono generare discordanze;
2) Privatezza dei dati. È limitata perché gestita solo dal sistema operativo (controllo sull’accesso al file, non al singolo record o campo);
3) Integrità dei dati. Si devono poter imporre vincoli di consistenza.
4) Problemi di concorrenza. Più programmi possono cercare di accedere nello stesso momento allo stesso dato: si possono generare situazioni scorrette.
Questi inconvenienti si possono superare se tutti i dati vengono organizzati e gestiti da un DBMS (Data Base Management System).
SISTEMI PER LA GESTIONE DI BASI DI DATI
Base di Dati (DB):
Raccolta di dati che:
- può essere utilizzata da utenti o programmi applicativi diversi;
- è integrata;
- fornisce all’utente una rappresentazione logica (e non fisica) dei dati;
- accesso in locale o in remoto.
Data Base Management Systems (DBMS):
Sistema che gestisce una base di dati (in particolare gestisce gli accessi da parte di più utenti o programmi applicativi).
Descrizione della struttura dei dati unica (SCHEMA).
Data Manipulation Language (linguaggio di definizione e di interrogazione).
DBMS e Access - 5
OBIETTIVI delle BASI DI DATI
Rendere il più possibile indipendenti le applicazioni che agiscono sui dati dalla struttura fisica di memorizzazione (file e loro organizzazione).
Inoltre:
Coerenza dei dati (per evitare, ad esempio, duplicazioni discordanti, etc.)
Integrità dei dati: il DB deve contenere solo dati corretti (ad esempio un dato MESE può assumere solo valori 1-12).
Ripristino: procedure automatiche di ripristino dei dati in caso di errori.
Privatezza: in caso di sistemi utilizzati da più utenti, occorrono controlli di autorizzazione per agire sui dati.
DBMS e Access - 6
IL MODELLO RELAZIONALE DEI DATI
È uno dei modelli dei dati utilizzati nei DBMS.
Si basa sul concetto di relazione, nel suo senso matematico originale.
Dati gli insiemi D1, D2, ..., Dn (non necessariamente distinti), R(D1, D2, ..., Dn) è una relazione su questi insiemi se è un insieme di n-ple ordinate (d1, d2, ..., dn) tali che d1 appartiene a D1, d2 appartiene a D2, e così via:
R(D1, D2, ..., Dn) ∏D1xD2x...xDn
R
D1 D2 ... Dn
val1val2... valn
D1, D2, ..., Dn sono detti domini della relazione.
Il grado della relazione è n (numero di colonne).
Cardinalità della relazione, numero di righe (record)
UN ESEMPIO DI RELAZIONE
Agenda telefonica Domini:
nomi propri di persona cognomi
numeri telefonici Agenda
Nome Cognome Numero
Mario Rossi 212347
Ivo Bianchi 355221
Lucia Bianchi 212347
Le tuple della relazione sono le varie righe.
I dati di una relazione sono organizzati in tabelle.
Un database completo può essere composto da più tabelle/relazioni correlate fra loro
ACCESS Si basa sul modello relazionale dei dati Consente di:
- definire lo schema (struttura) di una relazione specificando nome, tipo, vincoli (valore unico, non nullo, default e integrità) e dimensione dei campi;
- introdurre i dati in una relazione ed eventualmente correggere i dati introdotti;
- utilizzare un database e interrogarlo;
- cancellare o aggiungere record in modo selettivo (ad esempio tutti i record con un campo = ...);
- correlare due tabelle.
- organizzare i dati di una tabella ordinandoli su uno o più campi;
- creare indici per una tabella su uno o più campi;
Tutte queste operazioni possono essere effettuate sfruttando interfaccie grafiche, menù e finestre. Ogni operazione (effettuata tramite finestre) è l’equivalente grafico di uno statement SQL (Structured Query Language).
DBMS e Access - 9
SQL
• Linguaggio di creazione, interrogazione, modifica di database e tabelle.
• Basato sugli operatori dell’algebra relazionale:
- Proiezione: selezione di alcune colonne
- Selezione: selezione di alcune righe sulla base di un certo criterio
Esempio: relazione Agenda
Nome Cognome Numero
Mario Rossi 212347
Ivo Bianchi 355221
Mario Neri 644532
DBMS e Access - 10
Esempio: proietta la relazione agenda sulle sole colonne Nome e Cognome:
Nome Cognome
Mario Rossi
Ivo Bianchi
Mario Neri
Esempio: seleziona i record che soddisfano la relazione Nome=‘Mario’
Nome Cognome Numero
Mario Rossi 212347
Mario Neri 644532
- Unione: Unisce due relazioni R e S con lo stesso numero di campi (R U S ==> unione di insiemi) - Differenza: R - S
R:
Nome Cognome
Mario Rossi
Ivo Bianchi
Mario Neri
S:
Nome Cognome
Mario Rossi
Ivo Bianchi
Maria Verdi
R U S =
Nome Cognome
Mario Rossi
Ivo Bianchi
Mario Neri
Maria Verdi
R - S:
Nome Cognome
Mario Neri
DBMS e Access - 13
SQL: REAZIONE DI UN DATABASE E DELLE TABELLE
• Creazione di un Database:
CREATE DATABASE <Nome-Database>
Esempio: CREATE DATABASE Clienti.mdb Dipendente dal data base (molte opzioni)
• Creazione di una Tabella:
CREATE TABLE <Nome-Tabella>
(<Nome-Col> < Tipo> <Vincolo>)...
Esempio: CREATE TABLE Ordini (Numero_ordine Numero)
Crea la struttura delle tabelle che compongono il database: nome, tipo, dimensione dei campi, definizione delle chiavi, etc.
• Popolazione di una tabella (inserimento dati) INSERT INTO <Tabella> (<Nomi Colonne>)
VALUES (<Valori Corrispondenti>)
Esempio: INSERT INTO Ordini (Numero, Importo) VALUES (8, 1500000)
VALUES ecc. può essere una query
DBMS e Access - 14
ACCESS: CREAZIONE DI DB E TABELLE
DEFINIZIONE DELLA STRUTTURA DI UNA TABELLA
CHIAVE DI UNA RELAZIONE
È un sottoinsieme degli attributi della relazione tale che ogni record è identificato univocamente dal valore assunto dalla chiave.
Prodotti
Codice Descrizione Qty
X2134 Vite destrorsa 1300 F4533 Chiodo legno 10000
C5436 Chiodo muro 1200
Codice è la chiave della relazione Prodotti (identifica univocamente ciascun prodotto).
Agenda
Nome Cognome Numero
Mario Rossi 212347
Ivo Bianchi 355221
Lucia Bianchi 212347
L’insieme degli attributi NOME + COGNOME costituisce la chiave della relazione AGENDA
DBMS e Access - 17
INSERIMENTO DATI
DBMS e Access - 18
ALCUNE PROPRIETÀ DEL MODELLO RELAZIONALE
Relazioni normalizzate (1aforma normale)
Gli elementi dei domini che compongono la relazione devono essere valori atomici (non divisibili ulteriormente in componenti) ==> Struttura “piatta”
della relazione
(Eccezione: tipo Data, composto da Giorno, Mese, Anno, che però può essere visto come una stringa) Record omogenei
Tutti i record di una relazione devono avere la medesima struttura
Non duplicazione
Ciascun record deve essere unico all’interno di una relazione
Indipendenza dall’ordine
L’ordine con cui compaiono i record in una relazione non ha importanza (la capacità di ordinare è intrinseca nei valori dei capi dei record stessi)
FORME NORMALI
2aforma normale: eliminazione dipendenze funzionali
Ordini
Cod_P Qty N_Ord Cod_CL Ind_CL X2134 1300 1725 ALFA32 Bologna F4533 1000 1726 ALFA32 Bologna
23111 2000 1727 BETA Modena
Il campo Ind_CL (indirizzo cliente) dipende funzionalmente dal campo Cod_CL (codice cliente).
Se c'e' un certo Cod_CL è ovvio che vi sarà associato sempre lo stesso Ind_CL.
È quindi possibile eliminare dalla relazione ordini il campo Ind_CL e creare una seconda relazione che descrive la dipendenza dell'indirizzo dal codice di un cliente.
In pratica, si devono creare più tabelle correlate fra loro (nell'esempio, correlate dal campo Ind_CL)
Ordini
Cod_P Qty N_Ord Cod_CL
X2134 1300 1725 ALFA32
F4533 1000 1726 ALFA32
23111 2000 1727 BETA
Clienti
Cod_CL Ind_CL ALFA32 Bologna
BETA Modena
Eliminazione di dipendenze indesiderate via via più accentuata dalla 2a forma normale in poi.
DBMS e Access - 21
INTERROGAZIONE DEL DB
Una interrogazione viene espressa in un linguaggio opportuno di interrogazione (ad esempio SQL) e specifica una parte dei dati contenuti in una base di dati. Pertanto, una interrogazione è una richiesta per trovare dati nel DB.
LINGUAGGI DI INTERROGAZIONE
- basati sull’algebra relazionale. Le interrogazioni sono espresse mediante (combinazione di) operatori applicati a relazioni
SELEZIONE, PROIEZIONE, JOIN
- basati sul calcolo relazionale (base logica). Le interrogazioni “descrivono” un insieme di record specificando una condizione logica (predicato) che questi record devono soddisfare.
Linguaggio “dichiarativo”
DBMS e Access - 22
SQL: SELEZIONE
- La selezione permette di estrarre dal database alcuni record che soddisfano una determinata condizione.
In SQL:
SELECT * FROM <Nome-Tabella> <Alias>
WHERE <Condizione>
- L’alias è un nome alternativo (generalmente più corto) per riferirsi alla tabella <Nome-Tabella>. È opzionale quando la query coinvolge una sola tabella, mentre è obbligatorio quando la query coinvolge più di una tabella.
SELECT *, SELECT ALL, SELECT DISTINCT <Campo>
Prodotti
Codice Descrizione Qty
X2134 Vite destrorsa 1300
F4533 Chiodo legno 10000
C5436 Chiodo muro 1200
Seleziona i prodotti per cui si ha una quantità minore di 2000
In SQL:
SELECT * FROM Prodotti P WHERE P.QTY<=2000
X2134 Vite destrorsa 1300
C5436 Chiodo muro 1200
In generale, la selezione consente di estrarre i record che soddisfano una certa condizione logica F:
CONDIZIONI LOGICHE
Le formule logiche in una selezione sono del tipo:
C1 rel C2 dove:
C1 e C2 sono attributi o valori costanti
rel è un operatore relazionale (di confronto), come ad esempio <, >, <=, >=, =, !=
Impiegati
Cognome Nome Stip_Prec Stipendio Data_N
Rossi Mario2.800.000 2.500.000 1958
Bianchi Ivo 2.400.000 2.500.000 1938
Neri Luigi 1.900.000 1.800.000 1962
Seleziona gli impiegati che hanno avuto una riduzione dello stipendio.
SELECT * FROM Impiegati Imp
WHERE Imp.Stip_Prec > Imp.Stipendio
Rossi Mario2.800.000 2.500.000 1958
DBMS e Access - 25
CONDIZIONI LOGICHE (Cont)
• <Campo> BETWEEN A AND B
Stipendio BETWEEN 1000000 AND 2000000
Neri Luigi 1.900.000 1.800.000 1962
• <Campo> IN (a1, a2, ....an) Data_N IN (1938, 1966, 1969)
Bianchi Ivo 2.400.000 2.500.000 1938
• <Campo> LIKE <Stringa>
Nome LIKE ‘%ss%’
%: stringa di lunghezza qualunque
Rossi Mario2.800.000 2.500.000 1958
Impiegati
Cognome Nome Stip_Prec Stipendio Data_N
Rossi Mario2.800.000 2.500.000 1958
Bianchi Ivo 2.400.000 2.500.000 1938
Neri Luigi 1.900.000 1.800.000 1962
DBMS e Access - 26
Le formule logiche di una selezione possono essere anche composte attraverso connettivi logici
AND, OR, NOT
Ad esempio, seleziona i prodotti per cui si ha una quantità minore di 2000 o maggiore di 8000
SELECT * FROM Prodotti P
WHERE (QTY<=2000) OR (QTY>=8000)
X2134 Vite destrorsa 1300
F4533 Chiodo legno 10000
C5436 Chiodo muro 1200
Seleziona gli impiegati giovani (Data_N>=1962) che hanno avuto una riduzione dello stipendio:
SELECT * FROM Impiegati Imp WHERE (Stip_Prec > Stipendio) AND
(Data_N >=1962)
Neri Luigi 1.900.000 1.800.000 1962
PROIEZIONE
La proiezione consente di proiettare i record solo su certi campi (colonne). In pratica si estrae una porzione verticale della relazione.
SELECT CAMPO1,CAMPO2,...
Ad esempio:
Prodotti
Codice Descrizione Qty
X2134 Vite destrorsa 1300
F4533 Chiodo legno 10000
C5436 Chiodo muro 1200
visualizza il codice di tutti i prodotti SELECT Codice FROM Prodotti
X2134 F4533 C5436
Selezione e proiezione possono essere combinate Ad esempio:
Prodotti
Codice Descrizione Qty
X2134 Vite destrorsa 1300 F4533 Chiodo legno 10000
C5436 Chiodo muro 1200
Visualizza il codice e la descrizione dei prodotti per cui si ha una quantità minore di 2000:
SELECT P.Codice, P.Descrizione FROM Prodotti P WHERE P.QTY <= 2000
X2134 Vite destrorsa C5436 Chiodo muro
DBMS e Access - 29
Un altro esempio:
Agenda
Nome Cognome Numero
Mario Rossi 212347
Ivo Bianchi 355221
Lucia Bianchi 212347
Qual è il numero di telefono di Mario Rossi?
SELECT A.Numero FROM Agenda A
WHERE A.Nome = ‘Mario’ AND A.Cognome=‘Rossi’
DBMS e Access - 30
JOIN
Consente di correlare due relazioni sulla base di valori di certi campi: Data una formula di confronto F tra attributi di relazioni (R e S, ad esempio), indichiamo con:
R jF S il join di R ed S sulla base di F.
Ordini
Cod_P Qty N_Ord Cod_CL
X2134 1300 1725 ALFA32
F4533 1000 1726 ALFA32
23111 2000 1727 BETA
Clienti Cod_CL Ind_CL ALFA32 Bologna
BETA Modena
A partire dalle relazioni Ordini e Clienti, produci una relazione che contenga il numero d’ordine, il codice e l’indirizzo del cliente.
Ordini j(ORDINI.COD_CL=CLIENTI.COD_CL) Clienti
Ordini j(ORDINI.COD_CL=CLIENTI.COD_CL) Clienti Cod_P Qty N_Ord Cod_CL Ind_CL X2134 1300 1725 ALFA32 Bologna F4533 1000 1726 ALFA32 Bologna
23111 2000 1727 BETA Modena
Proiezione su numero d’ordine, il codice e l’indirizzo del cliente:
1725 ALFA32 Bologna 1726 ALFA32 Bologna
1727 BETA Modena
In pratica, il Join serve per ricostruire in una unica tabella i dati che erano stati separati su più tabelle per rispettare la seconda regola di normalizzazione.
JOIN : RELAZIONI IN ACCESS
DBMS e Access - 33
AUTOCOMPOSIZIONE
QUERY SEMPLICE SU UNA RELAZIONE
DBMS e Access - 34
RISULTATO
ESERCIZIO:
Costruire un tabella rubrica a partire dai dati della propria rubrica, includendo ad esempio nome, cognome, indirizzo, numero di telefono dei propri amici.
Costruire un tabella agenda che memorizza gli appuntamenti passati e futuri con i propri amici, includendo ad esempio data, luogo e scopo dell'appuntamento, nonché amico con cui si aveva appuntamento. Correlare quindi i dati tra questa tabella e la tabella rubrica, in modo che la tabella agenda rispetti la forma normale (e quindi che la tabella agenda non contenga tutti i dati relativi alla persona con cui si aveva appuntamento, ma solo i dati essenziali a partire dai quali si possano poi recuperare in modo automatico tutti i dati rimanente dalla tabella rubrica).
Provare a stampare, ordinare, effettuare query e proiezioni (anche con join) sulle tabelle così realizzate.