Download - Appunti database

Transcript

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

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

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

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 1

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

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

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).