Post on 08-Jun-2015
description
APPUNTI SUBASI DI DATI
Prof. G. Caprioli(marzo 2008)
APPUNTI SU BASI DI DATI
progettazione concettuale e logica
SQL
implementazioni
APPUNTI SU BASI DI DATI
PROGETTAZIONE
CONCETTUALE
E
LOGICA
PROGETTAZIONE
1. Raccolta delle richieste dell’utente• studio preliminare su convenienza, riflessi e settori coinvolti per l’organizzazione• analisi del sistema informativo esistente• definizione dei requisiti del nuovo sistema• classificazione dei dati• individuazione dei vincoli sui dati• descrizione delle procedure da automatizzare• volume iniziale e tasso di crescita dei dati• sicurezza e grado di riservatezza dei dati
2. Progetto concettuale• schema dei dati e delle relazioni tra essi (diagramma ER)• operazioni da implementare• vincoli impliciti ed espliciti su dati, procedure, tempi di risposta (vincoli di integrità, di dominio, ecc.)
3. Progetto logico• strutturazione dei dati (approccio relazionale)• stesura delle procedure
4. Progetto fisico• descrizione dell’organizzazione fisica degli archivi (tabelle, indici ecc.)• codifica delle operazioni e delle interfacce (procedure, menu ecc.)
• FASI DEL PROGETTO
PROGETTO CONCETTUALE
• PROGETTO CONCETTUALE
ENTITÀDato da memorizzare → identificatore (sostantivo)
→ proprietà (attributi)
ENTITÀ• intensione, estensione• entità ISA
ASSOCIAZIONE Relazione matematica tra entità → identificatore (verbo)
→ cardinalità
ASSOCIAZIONE• intensione, estensione• diretta, inversa• totale, parziale• univoca, multipla• suriettiva, non suriettiva
CARDINALITÀ• 1:1• 1:N oppure N:1• N:M
ATTRIBUTO• identificatore + dominio• semplice, composto, multiplo• chiave• obbligatorio, opzionale• costante, variabile• derivato
ENTITÀ + ASSOCIAZIONI = DIAGRAMMI E-R (Entity-Relationship)
• PROGETTO CONCETTUALE(*)
1/2
PROGETTO CONCETTUALE
idLibro copia titolo numPagine posizionecasaEd
PRENOTA
LETTORE LIBRO
idLettore cognome nome indirizzo telefonotipo
PRENDE
data
data
NM
1N
OPERAZIONI DA REALIZZARE:
Q1. visualizzazione dell’elenco di tutti i lettori che hanno preso in prestito libri con i
relativi titoli
Q2. visualizzazione del cognome e nome dei lettori che hanno preso in prestito un
libro in una data assegnata
Q3. visualizzazione dell’elenco dei libri prenotati con titolo, data, cognome e
telefono dei prenotanti
Q4. visualizzazione dei dati dei lettori che hanno prenotato almeno un libro
(*) Porzione del progetto concettuale della base di dati BIBLIOTECA
• PROGETTO CONCETTUALE(*)
2/2
PROGETTO CONCETTUALE
RELAZIONE R D1X D2 X … DN Di : dominirJ : tuple
DEFINIZIONI
tra relazioni compatibili
– RIDENOMINAZIONE ρB←A(R)
– UNIONE R1 R2
– DIFFERENZA R1 - R2
– INTERSEZIONE R1 R2
tra relazioni non compatibili
– PRODOTTO CARTESIANO R1 X R2
– PROIEZIONE πA(R)
– RESTRIZIONE σP(R)
– GIUNZIONE R1 R▷◁ 2
– DIVISIONE R1 ÷ R2
OPERAZIONI DELL’ALGEBRA RELAZIONALE
PROGETTO LOGICO
• PROGETTO LOGICO
SCHEMA DI RELAZIONEnome + lista attributi
IMPLEMENTAZIONE DEI DIAGRAMMI E-R
– entità → schema di relazione– associazione 1:1 → chiave esterna su uno degli schemi di relazione– associazione 1:N → chiave esterna su schema di relazione N– associazione N:M → schema di relazione con chiavi esterne
ATTENZIONE AI VINCOLI DI
INEGRITÀ
DEFINIZIONE DEGLI SCHEMI DI RELAZIONE
LETTORE(IdLettore: INTERO LUNGO, Cognome: CHAR(20), Nome: CHAR(20), Indirizzo: CHAR(30), Telefono: CHAR(15), Tipo: CHAR)
LIBRO(IdLibro: INTERO LUNGO, #Copia: INTERO, Titolo: CHAR(30), NumPagine: INTERO, Posizione: CHAR(8), CasaEd:CHA(30), IdLettore: INTERO LUNGO, Data: DATA)
PRENOTA(IdPrenota: INTERO LUNGO, IdLettore: INTERO LUNGO, IdLibro: INTERO LUNGO,Data: DATA)
NOTE:
I campi sottolineati sono campi chiave primariaI campi in corsivo sono campi chiave esterna che implementano una relazione tra entità di tipo 1:NI campi in carattere non grassetto sono campi esterni all'entità (appartenenti alla relazione 1:N)LA RELAZIONE PRENOTA IMPLEMENTA LA CORRISPONDENTE RELAZIONE TRA ENTITÀ
• ciascuno schema di relazione è indicizzato sulla chiave primaria• lo schema di relazione LIBRO contiene due chiavi esterne per le associazioni PRENDE e PRENOTA• per brevità vengono omessi i vincoli impliciti ed espliciti
(*) Porzione del progetto logico della base di dati BIBLIOTECA
• PROGETTO LOGICO(*)
1/2
PROGETTO LOGICO
(*) Porzione del progetto logico della base di dati BIBLIOTECA
• PROGETTO LOGICO(*)
2/2
PROGETTO LOGICO
ESEMPI DI ESPRESSIONI DELL’ALGEBRA RELAZIONALE RELATIVE ALLE OPERAZIONI INDIVIDUATE:
Q1. LETTORE ▷◁ LIBRO
Q2. πcognome, nome( σdata=[parametro_data](LETTORE ▷◁ LIBRO))
Q3. LIBRO ▷◁ PRENOTA ▷◁ LETTORE
Q4. LETTORE ▷◁ ( πidLettore (PRENOTA))
PROGETTO LOGICO
• ANOMALIE
• inserimento – l’inserimento di un nuovo lettore obbliga ad inserire anche un libro preso in prestito
• cancellazione – la cancellazione della tupla (U3, L2) ha un effetto diverso dalla cancellazione della tupla (U2, L3)
• aggiornamento – dovendo modificare l’indirizzo di un lettore si è obbligati ad aggiornare tutte le tuple
Le cause vanno ricercate nella indipendenza e ridondanza di alcuni dati
Il rimedio consiste nella NORMALIZZAZIONE
processo di trasformazione degli schemi di relazione in altri più piccoli aventi lo stesso contenuto informativo ed esenti da un certo tipi (livelli) prestabiliti di anomalie
PRENOTAidLettore indirizzo cap localita idLibro titolo data
U1 I1 C1 L1 L1 T1 D1U1 I1 C1 L1 L2 T2 D1U1 I1 C1 L1 L3 T3 D4U2 I2 C2 L2 L3 T3 D2U3 I3 C3 L3 L2 T2 D3U3 I3 C3 L3 L4 T4 D5
PROGETTO LOGICO
1FNuno schema di relazione si dice in prima forma normale quando tutti gli attributi sono di tipo elementare
soluzione
attributo composto → si scompone in più attributi semplici
attributo multiplp → si trasforma in più righe, una per ogni molteplicità degli attributi multipli, ripetendo la parte non multipla
• NORMALIZZAZIONE1/3
PRENOTAidLettore indirizzo cap localita idLibro titolo data
U1 I1 C1 L1 L1 T1 D1U1 I1 C1 L1 L2 T2 D1U1 I1 C1 L1 L3 T3 D4U2 I2 C2 L2 L3 T3 D2U3 I3 C3 L3 L2 T2 D3U3 I3 C3 L3 L4 T4 D5
PRENOTAidLettore indirizzo cap localita idLibro titolo data idLibro titolo data idLibro titolo data
U1 I1 C1 L1 L1 T1 D1 L2 T2 D1 L3 T3 D4U2 I2 C2 L2 L3 T3 D2U3 I3 C3 L3 L2 T2 D3 L4 T4 D5
ES. Si abbia il seguente insieme di dati riguardante le prenotazioni in una biblioteca
Eliminando gli attributi composti e multipli si ottiene:
PROGETTO LOGICO
2FNuno schema di relazione si dice in seconda forma normale quando
– è 1FN
– non esistono attributi che dipendono solo da una parte della chiave
soluzione
si estraggono gli attributi che dipendono da una stessa parte di chiave in uno schema di relazione a sé stante ed associato allo schema rimanente che avrà cardinalità N (la parte di chiave diventa chiave esterna)
• NORMALIZZAZIONE2/3
ES. Nello schema di relazione Prenota, portato in 1FN nell’esempio precente, si ha:
idLettore → indirizzoidLibro → titoloidLettore → indirizzo
PRENOTAidLettore idLibro data
U1 L1 D1U2 L3 D2U3 L2 D3U1 L2 D1U1 L3 D4U3 L4 D5
LIBROidLibro titolo
L1 T1L2 T2L3 T3L4 T4
LETTOREidLettore indirizzo cap localita
U1 I1 C1 L1U2 I2 C2 L2U3 I3 C3 L3
quindi estraendo ad esempio titolo ed editore, si avrà:
PROGETTO LOGICO
3FNuno schema di relazione si dice in terza forma normale quando
– è 2FN
– non esistono attributi non-chiave che dipendono transitivamente dalla chiave
soluzione
si estraggono gli attributi che dipendono da altri attributi non chiave in uno o più schemi di relazione associati allo schema originale (che avrà cardinalità N); i campi di “transizione” diventano rispettivamente chiave esterna nello schema originale e chiave primaria nello schema derivato
• NORMALIZZAZIONE3/3
ES. Nello schema di relazione Lettore, ricavato dalla normalizzazione in 2FN dell’esempio precente, si ha:
idLettore → localita → cap (il cap non dipende direttamente da idLettore ma transitivamente attraverso localita)
quindi:
PRENOTAidLettore idLibro data
U1 L1 D1U2 L3 D2U3 L2 D3U1 L2 D1U1 L3 D4U3 L4 D5
LIBROidLibro titolo
L1 T1L2 T2L3 T3L4 T4
LETTOREidLettore indirizzo localita
U1 I1 L1U2 I2 L2U3 I3 L3
LOCALITAlocalita cap
L1 C1L2 C2L3 C3
ESERCIZIO 1
sportello dello studente (1/2)
INSEGNANTE
EFFETTUA
DISPONIBILITA
LEZIONE RIFERITA
OFFRE
ora inizioallievo
ora fine
idInsegnantecognomenomemateria
giornoorainizioorafineclassemateria
idDisponibilita
N
1
1
N
N
1
dataidLezione
• Dato il seguente schema:
costruire gli schemi di relazione del relativo progetto logico
• Utilizzando gli schemi di relazione costruiti dal precedente diagramma ER, scrivere le espressioni dell’algebra relazionale relative alle seguenti operazioni:
Q1: visualizzazione di un insegnante
Q2: visualizzazione di una disponibilità
Q3: visualizzazione di una lezione
Q4: elencare le disponibilità per insegnante
Q5: elencare le disponibilità per materia
Q6: elencare le disponibilità per classe
ESERCIZIO 1
sportello dello studente (2/2)
ESERCIZIO 2
campionato di calcio (1/2)
• Dato il seguente schema:
costruire gli schemi di relazione del relativo progetto logico
SQUADRA
INCONTRA
STADIOGIOCA
idSquadranomeallenatoregirone
idStadionomecittacapienza
N
N 1
NM
datagiornatagolSquadra1golSquadra2
• Utilizzando gli schemi di relazione costruiti dal precedente diagramma ER, scrivere le espressioni dell’algebra relazionale relative alle seguenti operazioni:
Q1: elencare le squadre che hanno sede in città nelle quali lo stadio ha capienza superiore a 60000 persone
Q2: elencare le città nelle quali c’è più di uno stadio
Q3: elencare le quadre che hanno perso almeno unna volta con una difefrenza reti superiore a 3
Q4: elencare gli stadi nei quali nessun incontro è terminato con un pareggio
Q5: elencare le squadre che hanno vinto tutti i derby disputati
ESERCIZIO 2
campionato di calcio (2/2)
APPUNTI SU BASI DI DATI
SQL Structured Query Language
SQL
Linguaggio per basi di dati che assolve alle funzioni di definizione, manipolazione e memorizzazione dei dati
Può essere utilizzato interattivamente mediante opportune interfacce utente oppure essere ospite di un linguaggio di programmazione, in genere mediante apposite librerie
In SQL gli schemi di relazione vengono implementati in TABELLE in cui ciascuna tupla diviene una RIGA; a differenza delle relazioni, le righe possono essere duplicate
SQL consente una buona indipendenza logica e fisica
Le specifiche attualmente standardizzate sono l’ANSI X3.135 e l’ISO 9075 del 1992
PRINCIPALI TIPI
SMALLINT
INTEGER
FLOAT
DOUBLE PRECISION
DECIMAL | NUMERIC [(precision [, scale])]
DATE
CHAR | VARCHAR[(int)]
BLOB
CREATE TABLE LETTORE(idLettore INTEGER NOT NULL, cognome VARCHAR(20) , nome VARCHAR(20), indirizzo VARCHAR(30), telefono CHAR(15), tipo CHAR, PRIMARY KEY(idLettore));
• CREAZIONE DI UNA TABELLA
CREATE TABLE tabella (attributo tipo [clausole] [, ...][,clausole])
PAROLE CHIAVE
EXTERNAL
FILE
COMPUTED BY
PRIMARY KEY
DEFAULT
NULL| NOT NULL
USER
UNIQUE
COLLATE
CONSTRAINT
CREATE TABLE
CREATE UNIQUE INDEX Alfabetico ON Lettore (cognome, nome);
• CREAZIONE DI UN INDICE
CREATE INDEX indice ON tabella (attributo [, attributo])
CREATE INDEX
PAROLE CHIAVE
EXTERNAL
FILE
COMPUTED BY
PRIMARY KEY
DEFAULT
NULL| NOT NULL
USER
UNIQUE
COLLATE
CONSTRAINT
DROP
• CANCELLAZIONE DI UNA TABELLA
DROP TABLE tabella
• CANCELLAZIONE DI UN INDICE
DROP INDEX indice
DROP TABLE Lettore;
DROP INDEX Alfabetico;
• UN INDICE PUÓ ESSERE ELIMINATO SOLO DAL PROPRIETARIO O DALL’AMMINISTRATORE
• NON SI POSSONO ELIMINARE INDICI DI SISTEMA O CORRENTEMENTE APERTI
• UNA TABELLA PUÓ ESSERE ELIMINATA SOLO DAL PROPRIETARIO O DALL’AMMINISTRATORE
• NON SI POSSONO ELIMINARE TABELLE CORRENTEMENTE APERTE O UTILIZZATE DA ALTRI OGGETTI
GRANT e REVOKE
operazione operazione da consentire: ALTER|DELETE|INDEX|INSERT|SELECT|UPDATE|ALL
oggetto nome dell’oggetto su cui consentire l’operazione: ad esempio una tabella, un indice ecc.
utente identificatore dell’utente cui consentire l’operazione
• SICUREZZA ED INTEGRITÀ DEI DATI
GRANT operazione ON oggetto TO utente
REVOKE operazione ON oggetto FROM utente
GRANT UPDATE ON Libro TO Segreteria
operazione operazione da consentire: ALTER|DELETE|INDEX|INSERT|SELECT|UPDATE|ALL
oggetto nome dell’oggetto su cui consentire l’operazione: ad esempio una tabella, un indice ecc.
utente identificatore dell’utente cui consentire l’operazione
REVOKE UPDATE ON Libro FROM Segreteria
SELECT
predicato ALL, DISTINCT, DISTINCTROW, TOP
elenco campi * | tabella.* | [tabella.]campo1 [AS alias1] [, ...]
tabella Nome della tabella contenente i dati che si desidera recuperare
clausole [IN databaseesterno]
[WHERE... ]
[GROUP BY... ]
[HAVING... ]
[ORDER BY... ]
[WITH OWNERACCESS OPTION]
• ESTRAZIONE DI DATI
SELECT 1/9
SELECT [predicato] elencocampi FROM tabella [clausole]
SELECT DISTINCT cognome, nome FROM Lettore WHERE cognome<“D”;
SELECT cognome, titolo FROM Lettore, Libro WHERE Lettore.idLettore=Libro.idLettore;
SELECT * FROM Lettore;
SELECT TOP 25 cognome, nome FROM Lettore WHERE tipo=“s” OR tipo=“S” ORDER BY idLettore;
PREDICATO:
ALL Impostazione predefinita. seleziona tutti i record che soddisfano le condizioni dell'istruzione SQL
DISTINCT Omette i record che contengono dati duplicati nei campi selezionati. Per essere inclusi nei risultati della query, i valori di ogni campo elencato nell'istruzione SELECT devono essere univoci. Numerosi impiegati elencati in una tabella Impiegati, ad esempio, potrebbero avere lo stesso cognome.
DISTINCTROW Omette i dati basati su interi record duplicati e non solo su campi duplicati.
TOP Restituisce un certo numero di record che rientrano all'inizio o alla fine di un intervallo specificato tramite una clausola ORDER BY. Se non si include la clausola ORDER BY, la query restituirà dalla tabella Studenti un set arbitrario di record che soddisfano la clausola WHERE. Il predicato TOP non effettua una scelta tra valori uguali. È possibile anche utilizzare la parola riservata PERCENT per ottenere una certa percentuale di record che rientrino all'inizio o alla fine di un intervallo specificato tramite una clausola ORDER BY.
SELECT• ESTRAZIONE DI DATI
SELECT 2/9
ELENCO CAMPI:
Nome del campo o dei campi da recuperare insieme agli alias dei nomi di campo, alle funzioni di aggregazione SQL, ai predicati di selezione ALL, DISTINCT, DISTINCTROW, TOP o ad altre opzioni delle istruzioni SELECT.
[tabella].* tutti i campi;[tabella.]campo un campo specifico‘costante’ stesso valore per tute le righe risultatofunzione di aggregazione funzioni che calcolano varie statistiche su set di valori (Avg,Count,Min, Max,StDev,
StDevP,Sum,Var, VarP)
SELECT• ESTRAZIONE DI DATI
SELECT 3/9
SELECT DISTINCTROW “Lettore:”, nome, cognome FROM Lettore WHERE tipo=“S” OR tipo=“s” ORDER BY cognome DESC;
ESPRESSIONETABELLA
Espressione che identifica una o più tabelle da cui vengono recuperati i dati. L'espressione può essere:
un singolo nome di tabellaun elenco di più tabelle; in questo caso viene fatto il prodotto cartesiano delle due tabelleun nome di query memorizzata un nome composto risultante da una JOIN (INNER, LEFT, RIGHT)
• ESTRAZIONE DI DATI
SELECT 4/9
SELECT
JOIN
Istruzione corispondente alla JOIN dell’algebra relazionale. Può essere INNER, LEFT, RIGHT in base alla eventuale suriettività del risultato e della tabella di riferimento.
Combina in un campo comune i record di due tabelle se questi contengono valori corrispondenti: è quindi la base delle relazioni 1:N
La sua sintassi è:
SELECT elencocampi FROM tabella1 INNER JOIN tabella2 ON tabella1.campo1 operatorediconfronto tabella2.campo2
SELECT Lettore.cognome, Lettore.nome, Libro.titolo FROM Lettore INNER JOIN Libro ON Lettore.idLettore = Libro.idLettore;
CLAUSOLE
[IN databaseesterno] Specifica il database che contiene la tabella nel caso che questa sia contenuta in un altro database
[WHERE espressione ] Specifica quali record delle tabelle elencate nella clausola FROM sono influenzati dalla SELECT; espressione deve assumere un valore logico e può essere composta con variabili, campi ed operatori di confronto, operstori logici
[GROUP BY campiraggruppamento] Combina in un record singolo tutti i record con valori identici nell'elenco di campi specificato. Se si include una funzione di aggregazione SQL, come Sum o Count, per ciascun record viene creato un valore di riepilogo.
[HAVING criteriraggruppamento] Specifica i record che vengono visualizzati partendo dal risultato di GROUP BY. Dopo che GROUP BY combina i record, la clausola HAVING visualizza tutti i record raggruppati tramite la clausola GROUP BY che soddisfano le condizioni della clausola HAVING
[ORDER BY elencocampi ASC|DESC] Ordina i record risultanti da una query in base a uno o più campi specifici in ordine crescente o decrescente
[WITH OWNERACCESS OPTION], [UNION...], [PLAN...] clausole specifiche delle varie versioni
SELECT• ESTRAZIONE DI DATI
SELECT 5/9
FUNZIONI DI AGGREGAZIONE
Permettono di calcolare varie statistiche su set di valori. È possibile utilizzare queste funzioni in una query ed in particolare nella SELECT
Avg(espressione)Count(espressione)Min, Max(espressione)StDev(espressione), StDevP(espressione)Sum(espressione)Var(espressione), VarP(espressione)
ALTRE PAROLE CHIAVE/OPERATORI
IS [NOT] NULLLIKE[NOT] NULLAND, OR, NOTASCOLLATE
SELECT• ESTRAZIONE DI DATI
SELECT 6/9
espressione rappresenta un'espressione stringa che identifica il campo che contiene i dati numerici di cui si vuole calcolare la media o un'espressione che esegue un calcolo utilizzando i dati del campo
SELECT Lettore.*, Libro.titoloFROM Lettore
INNER JOIN Libro ON Lettore.idLettore = Libro.idLettoreORDER BY Lettore.cognome;
ESEMPI DI VISUALIZZAZIONE DI UNA RELAZIONE 1:N
SELECT• ESTRAZIONE DI DATI
SELECT 7/9
1. visualizzazione dell’elenco di tutti i lettori che hanno preso in prestito libri con i relativi titoli; il tutto ordinato in base al cognome del lettore
2. visualizzazione dell’elenco di tutti i lettori che hanno preso in prestito libri con i relativi titoli; il tutto ordinato in base al cognome del lettore (con INNER JOIN)
SELECT Lettore.*, Libro.titolo FROM Lettore, LibroWHERE Lettore.IdLettore=Libro.idLettore ORDER BY Lettore.Cognome;
SELECT Libro.titolo, Prenota.data, Lettore.cognome, Lettore.indirizzo, Lettore.telefonoFROM (Libro
INNER JOIN Prenota ON Libro.idLibro = Prenota.idLibro) INNER JOIN Lettore ON Prenota.idLettore = Lettore.idLettore
ORDER BY Libro.titolo;
ESEMPI DI VISUALIZZAZIONE DI UNA RELAZIONE N:N
SELECT• ESTRAZIONE DI DATI
SELECT 8/9
3. visualizzazione dell’elenco dei libri prenotati con titolo, data, cognome e telefono dei prenotanti; il tutto ordinato in base al cognome del lettore
4. visualizzazione dell’elenco dei libri prenotati con titolo, data, cognome e telefono dei prenotanti; il tutto ordinato in base al cognome del lettore (con INNER JOIN)
SELECT Libro.titolo, Prenota.data, Lettore.cognome, Lettore.indirizzo, Lettore.telefonoFROM Libro, Prenota, LettoreWHERE (Libro.idLibro = Prenota.idLibro) AND (Prenota.idLettore = Lettore.idLettore)ORDER BY Libro.titolo;
ALTRI ESEMPI DI OPERAZIONI DI SELEZIONE
SELECT• ESTRAZIONE DI DATI
SELECT 9/9
5. visualizzazione dei dati dei lettori che hanno prenotato almeno un libro; si utilizza la clausola IN anziché la JOIN
6. visualizzazione dell’elenco dei lettori e del numero di libri che hanno preso in prestito solo per chi ha preso più di un libro
SELECT Lettore.* FROM LettoreWHERE Lettore.IdLettore IN (SELECT DISTINCT Prenota.IdLettore FROM Prenota)ORDER BY Lettore.Cognome;
SELECT DISTINCTROW Lettore.Cognome, Lettore.nome, count(Titolo) FROM Lettore, Libro WHERE Lettore.IdLettore=Libro.IdLettoreGROUP BY Lettore.Cognome, Lettore.Nome HAVING count(Titolo)>=2 ORDER BY Lettore.Cognome;
identificatore nome da assegnare alla VISTAselezione istruzione SELECT che popola di dati la VISTA
• in questo modo si assegna un identificatore ad una operazione di selezione che può essere usata all’interno di altre selezione
• in questo modo è possibile inoltre fornire e revocare diritti di accesso alla selezione• analogamente agli altri oggetti una vista si elimina con il comando DROP
CREATE VIEW• ESTRAZIONE DI DATI
CREATE VIEW identificatore AS selezione
CREATE VIEW Prenotazioni ASSELECT DISTINCTROW Lettore.*, Prenota.*, Libro.*FROM Libro INNER JOIN (Lettore
INNER JOIN Prenota ON Lettore.idLettore = Prenota.idLettore) ON Libro.idLibro = Prenota.idLibro;
JOIN• ESTRAZIONE DI DATI
JOIN 1/2
INNER, LEFT E RIGHT JOIN: elenco di lettori che hanno preso in prestito libri; il tutto ordinato in base al cognome del lettore e prendendo come tabella di riferimento Lettore
7. solo il lettori che hanno preso in prestito almeno un libro
SELECT Lettore.cognome, Lettore.nome, Libro.titoloFROM Lettore INNER JOIN Libro ON Lettore.idLettore = Libro.idLettoreORDER BY Lettore.cognome;
8. tutti i lettori (anche chi non ha preso libri in prestito) ed i relativi libri
SELECT Lettore.cognome, Lettore.nome, Libro.titoloFROM Lettore LEFT JOIN Libro ON Lettore.idLettore = Libro.idLettoreORDER BY Lettore.cognome;
9. solo il lettori che hanno preso in prestito un libro e tutti i libri (anche quelli non presi in prestito)
SELECT Lettore.cognome, Lettore.nome, Libro.titoloFROM Lettore RIGHT JOIN Libro ON Lettore.idLettore = Libro.idLettoreORDER BY Lettore.cognome;
JOIN• ESTRAZIONE DI DATI
JOIN 2/2
INNER, LEFT E RIGHT JOIN: elenco di lettori che hanno preso in prestito libri; il tutto ordinato in base al cognome del lettore e prendendo come tabella di riferimento Libro
10. solo il lettori che hanno preso in prestito almeno un libro
SELECT Lettore.cognome, Lettore.nome, Libro.titoloFROM Libro INNER JOIN Lettore ON Libro.idLettore = Lettore.idLettoreORDER BY Lettore.cognome;
11. tutti i lettori (anche chi non ha preso libri in prestito) ed i relativi libri
SELECT Lettore.cognome, Lettore.nome, Libro.titoloFROM Libro RIGHT JOIN Lettore ON Libro.idLettore = Lettore.idLettoreORDER BY Lettore.cognome;
12. solo il lettori che hanno preso in prestito un libro e tutti i libri (anche quelli non presi in prestito)
SELECT Lettore.cognome, Lettore.nome, Libro.titoloFROM Libro LEFT JOIN Lettore ON Libro.idLettore = Lettore.idLettoreORDER BY Lettore.cognome;
destinazione tabella, query o view in cui si vogliono inserire i datielencoattributi elenco dei nomi dei campi ai quali si riferiscono i datielencovalori elenco dei dat da inserire
• NORMALMENTE L’ISTRUZIONE INSERT PERMETTE DI INSERIRE DATI IN TUTTI I CAMPI DELLA DESTINAZIONE• L’ELENCO DEI VALORI DEVE RISPETTARE COME NUMERO E TIPO DI DATI L’ORDINE DELLE COLONNE DELLA
TABELLA; IN QUESTO CASO NON È NECESSARIO SPECIFICARE elencoattributi; SE INVECE SI VOGLIONO INSERIRE I DATI SOLO IN ALCUNI DEI CAMPI SI DEVONO SPECIFICARE TALI CAMPI IN elencoattributi; IN QUESTO CASO L’ELENCO DEI VALORI DEVE RISPETTARE COME NUMERO E TIPO DI DATI L’ORDINE INDICATO IN elencoattributi
INSERT
• INSERIMENTO DI DATI
INSERT INTO destinazione [(elencoattributi)] VALUES (elencovalori)
INSERT INTO Lettore (idLettore, cognome, nome, telefono, tipo) VALUES (1234, "Rossi", "Mario", "0773123456", "s");
destinazione tabella, query o view in cui si vogliono inserire i datielenco1attr elenco dei nomi dei campi ai quali si riferiscono i dati da inserireelenco2attr elenco dei nomi dei campi ai quali si riferiscono i dati che vengono estrattitabella tabella contenente i dati che vengono estratti
• I DUE ELENCHI DI ATTRIBUTI DEVONO CONTENERE, IN MANIERA ORDINATA, LO STESSO NUMERO E TIPO DI ATTRIBUTI
INSERT
• INSERIMENTO DI DATI
INSERT INTO destinazione [(elenco1attr)] SELECT elenco2attr FROM tabella
INSERT INTO Lettore (idLettore, cognome, nome, telefono, tipo) SELECT Idlettore,Cognome,Nome, Telefono, Tipo FROM CopiaLettore
DELETE FROM Lettore WHERE cognome=“Rossi”
• CANCELLAZIONE DI DATI
DELETE FROM tabella WHERE espressione
DELETE
tabella tabella da cui eliminare la riga
espressione espressione logica che determina la riga da cancellare
• LA CANCELLAZIONE DEI DATI PUÒ PORTARE AD UNA PERDITA DI CONSISTENZA DEGLI STESSI QUANDO NELLA CANCELLAZIONE SONO COINVOLTE CHIAVI ESTERNE DI ASSOCIAZIONI
• IL CONTROLLO DI TALE FENOMENO PRENDE IL NOME DI INTEGRITÀ REFERENZIALE)• IN GENERE I DBMS FORNISCONO DEI MECCANISMI DI IMPOSTAZIONE E CONTROLLO DELL’INTEGRITÀ
REFERENZIALE (IMPEDIMENTO DELLE CANCELLAZIONI, AGGIORNAMENTO A CATENA ECC.)• LA CANCELLAZIONE PUÒ OVVIAMENTE AGIRE SU PIÙ RIGHE CONTEMPORANEAMENTE
UPDATE Libro SET posizione=“S2R3” WHERE idLibro=12 34
• AGGIORNAMENTO DI DATI
UPDATE tabella SET attributo=esp [, attributo=espressione1] WHERE esp2
UPDATE
attributo nomi dei campi della riga da aggiornare
esp1 nuovi valori da dare ai campi
esp2 espressione logica che individua la riga da aggiornare
• IL VALORE DI UNA CHIAVE PRIMARIA NON PUÒ ESSERE AGGIORNATO
ESERCIZIO 3
• Scrivere le interrogazioni SQL relative ai questiti degli esercizi 1 e 2 formulando, se necessario, delle opportune ipotesi aggiuntive riguardo vincoli, campi da visualizzare, ordinamenti
ESERCIZIO 4
• Date le tabelle relative ai seguenti schemi di relazione, in cui gli identificatori sottolineati sono chiavi primarie e quelli in corsivo sono chiavi esterne delle rispettive tabelle:
LETTORE(idLettore, cognome, nome)
LIBRO(idLibro, titolo, autore)
PRELEVA(idPrenota, idLettore, idLibro, data)
scrivere le interrogazioni SQL per:
Q1: ottenere i codici dei lettori che hanno preso in prestito almeno un libro
Q2: ottenere i nomi ed i cognomi delle persone che hanno preso in prestito almeno
un libro
Q3: ottenere i nomi ed i cognomi delle persone che hanno preso in prestito almeno
un libro a partire dal 20/02/2002
Q4: ottenere i titoli dei libri presi in prestito da almeno una persona
Q5: ottenere tutti i titoli dei libri disponbili
Q6: ottenere i titoli dei libri con le rispettive persone che li hanno presi in prestito
Q7: ottenere i titoli dei libri presi in prestito in un dato giorno
Q8: ottenere gli autori dei libri presi in prestito dal sig. Mario Rossi
Q9: ottenere i nomi dei lettori che hanno preso in prestito libri di Svevo ma non di
Manzoni
Q10: ottenere quanti libri diversi di un autore ha preso in prestito un dato lettore
IMPLEMENTAZIONI
MS Access
MySql(questa sezione è ancora solo una bozza)
APPUNTI SU BASI DI DATI
In Microsoft ACCESS la creazione di una tabela può avvenire in più modi:
1. In maniera visuale; selezionare la cartella Tabelle; selezionare i tasti: Nuovo, Visualizzazione Struttura; a questo punto inserire i nomi dei campi ed i relativi tipi, uno per riga;Dopo aver selezionato il tasto Nuovo, sarebbe anche possibile optare per altre modalità di creazione (Visualizzazione Foglio dati, Autocomposizione Tabella ecc.)
2. In maniera manuale usando SQL (istruzione CREATE TABLE); per inserire l’istruzione selezionare la cartella Query; selezionare: Nuovo, Visualizzazione Struttura, Chiudi (per chiudere la finestra delle Tabelle); selezionare poi le voci di menu: Query|Specifica SQL|Definizione dati; nella finestra che appare si digita e si esegue l’istruzione. La sintassi è:
Access
• CREAZIONE DI UNA TABELLA
1/2
CREATE TABLE tabella (attributo tipo [,clausole] [, ...])
La clausola per la definizione della chave primaria ha la sintassi:
CONSTRAINT nomedelvincolo PRIMARY KEY(campo [,campo])
La clausola per la definizione di un campo indicizzato ha la sintassi:
CONSTRAINT nomedelvincolo UNIQUE (campo [,campo])
I principali tipi utilizzabili sono: INTEGER, LONG, COUNTER, TEXT/STRING, DATA, FLOAT, OBJECT, ecc.
CREATE TABLE LETTORE(IdLettore COUNTER , Cognome STRING(20), Nome TEXT(20), Indirizzo TEXT(30), Telefono TEXT(15), Tipo TEXT(1), CONSTRAINT Chiave PRIMARY KEY(Idlettore));
In Microsoft ACCESS la creazione di una tabela può avvenire in più modi:
3. Attraverso una istruzione SELECT; si prelevano i record da una tabella o query esistente e si inseriscono in una nuova tabella che verrà definita in base alle righe ottenute.
Access
• CREAZIONE DI UNA TABELLA
2/2
SELECT elencoattributi INTO nuovatabella FROM origine
elencoattributi elenco dei campi da estrarre
nuovatabella nome della tabella da creare
origine tabella o query origine
SELECT Lettore.* INTO Copialet FROM Lettore;
Access
Quando una maschera viene creata su una query LEFT JOIN, automaticamente si basa sulla tabella di sinistra in quanto è la tabella principale; la tabella di destra va eventualmente in una sottomaschera tramite un’associazione campo master/secondario.
Per consentire l’aggiunta in una maschera con sottomaschere solo nella tabella della sottomaschera (tabella N) e non nella tabella principale (tabella 1) è necessario impostare le seguenti proprietà nella maschera principale:
NO consenti aggiunte
NO consenti eliminazioni
SI consenti modifiche
BLOCCA tutti i campi
• CREAZIONE DI UNA MASCHERA
1/1
Per procedere alla creazione di una query pass-trough è necessario:avere accesso ad un Server SQL avere installato la libreria ODBC
PASSO 1: creazione di una fonte dati ODBC
1. Aprire il Pannello di Controllo di Windows2. Aprire ODBC3. Dalla cartella DSN utente selezionare Aggiungi4. Selezionare il driver relativo al Sever SQL che contiene il database da interrogare5. Selezionare Fine6. Inserire nella maschera i dati richiesti:
Data Source Name Identificatore della sorgente di dati (deciso dall’utente)Description Descrizione simbolica ad uso dell’utenteNetwork Protocol Eventuale protocollo di rete da utilizzare (se il Server SQL è remoto)Database Identificatore del database da interrogareServer Identificatore di rete del Server SQL Username Nome utente assegnato per l’accesso al Server SQLPassword Password Nome utente assegnato per l’accesso al Server SQL
7. Selezionare il pulsante OK
N.B prima di selezionare OK è possibile effettuare un test della connessione (pulsante Test Connect)
Access
• CREAZIONE DI UNA QUERY PASS-THROUGH
1/2
PASSO 2: creazione della query pass-through
1 Nella finestra del database, scegliere la cartella Query, quindi il pulsante Nuovo2 Selezionare Nuova query, Visualizzazione Struttura, OK, Chiudi nella finestra di dialogo Mostra tabella3. Selezionare Query|Specifica SQL|Pass-Through4. Selezionare le voci di menu Visualizza|Proprietà5. Nella tavola delle proprietà della query, impostare la proprietà StringaConnessODBC per specificare informazioni sul database
al quale si desidera collegarsi; ad es.: “ODBC; DSN=biblioteca; UID=SYSDBA; PWD=masterkey; Hostname=\\LISERDB; NetworkProt=TCP/IP; Database=c:\interbase\didattico;”NOTA: queste informazioni devono corrispondere a quelle inserite nella fonte dati ODBC creata nel passo 1; tali informazioni possono essere recuperate anche attraverso il Generatore di stringhe di connessione ODBC (selezionando ... nella proprietà StringaConnessODBC)
6. Impostare se necessario le altre proprietà (ad es. RestituisciRecord a No se la query non è del tipo che restituisce record)7. Nella finestra Query SQL pass-through, digitare la query
NOTA: la sintassi della query deve essere conforme alle convenzioni adottate dal server di database SQL al quale si sta inviando la query
8. Selezionare le voci di menu Visualizza|Foglio Dati per eseguire la query, oppure in alternativa utilizzare gli appositi pulsanti (punto esclamativo e/o foglio dati)
9. Chiudere la query e salvare
Access
• CREAZIONE DI UNA QUERY PASS-THROUGH
2/2
NOTE: Da questo momento la query può essere utilizzata come fonte di dati per maschere, report, inserimenti, modifiche ecc.
Può succedere, inoltre, che in fase di esecuzione della query vengano visualizzate richieste di informazioni sul database del server SQL (ad es. la password di accesso).
MySQL
Vedere il tutorial MySQL contenuto nel capitolo 3 del Reference Manual Mysql 6.0 (
http://www.mysql.com sezione Documentation)