approfondimento db - classe quarta

a
Database “3 puntata”
Pablo Genova
[email protected]
I. I. S. “Angelo Omodeo”
Indirizzo Tecnico-Economico
Mortara
A. S. 2016 – 2017
Cosa vedremo in questo (ultimo) modulo sui database?
Tenendo presente quanto visto l'anno scorso, quest'anno approfondiamo i
seguenti aspetti, che l'anno scorso abbiamo trascurato:
Ripasso ed approfondimento query sql (vedi esercizi sqlzoo)
Ripasso ed approfondimento di concetti importanti in sql ed access
Operazioni relazionali, definizione ed esempi concreti di
Unione di relazioni
Intersezione di due relazioni
Differenza di relazioni
Giunzione naturale tra due relazioni
Proiezione di relazioni
Restrizione di relazioni
Prodotto di relazioni
in sql e in access
Pagine 156 – 165 del libro +
Brain & Computer ;-)
Per ripassare le query esercitati nello ZOO ;-)
http://sqlzoo.net
Sito web divertente che permette di esercitarsi nella creazione di query e
verifica del loro funzionamento (senza aver installato un server sql)
In particolare provare con attenzione le sezioni
http://sqlzoo.net/wiki/SELECT_basics
SELECT BASICS
http://sqlzoo.net/wiki/SELECT_names
SELECT NAMES
http://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial Tutorial DB WORLD
http://sqlzoo.net/wiki/SELECT_Reference
HOW TO SELECT
http://sqlzoo.net/wiki/CREATE_a_new_table
HOW TO CREATE a table
http://sqlzoo.net/wiki/DROP_a_table
HOW TO DROP a table
http://sqlzoo.net/wiki/INSERT_new_records
HOW TO INSERT values
http://sqlzoo.net/wiki/UPDATE_existing_records HOW TO UPDATE values
http://sqlzoo.net/wiki/DELETE_records
HOW TO DELETE values
→ Nella verifica bisognerà sapere il comando sql per ...
✔Selezionare dei dati da una tabella, con eventuali restrizioni
✔Creare una tabella
RICORDA!
✔Cancellare una tabella (comando DROP) La riga (le righe) di un database
si chiama RECORD(S),
✔Inserire dati (records) ad una tabella
la colonna (le colonne) colonne
✔Aggiornare i dati (records) di una tabella CAMPO (I) (FIELD(S))
ntupla =tupla = record
✔Cancellare dati (records) di una tabella
✔Saper usare LIKE e i simboli speciali % * _ per le scelte rapide
Assegnato un database, nella verifica vi chiederò la spiegazione di un comando sql o
viceversa di scrivere il comando che fa una determinata operazione
Esempio dato il database world cosa fanno le seguenti queries:
SELECT name FROM world WHERE population > 200000000
SELECT capital FROM world WHERE name LIKE 'M%'
→ Scrivere il comando SQL per richiedere il nome e la popolazione degli stati
con area > 100000
→ Ripassare CREATE TABLE, INSERT, DELETE UPDATE
DISCIPLINA (CodDisciplina, NomeDisciplina)
Do you remember?
CREATE TABLE DISCIPLINA
(
CodDisciplina CHAR(6)
NOT NULL , /*definisco una stringa di caratteri lunga 6 */
NomeDisciplina CHAR(30) NOT NULL , /*definisco una stringa di caratteri lunga 30 */
PRIMARY KEY(CodDisciplina)
/* CodDisciplina è la chiave primaria */
);
INSERT INTO DISCIPLINA
VALUES (“INFO01”, “Informatica”); /* CodDisciplina 6 CARATTERI */
UPDATE DISCIPLINA
SET NomeDisciplina = “Informatica Aziendale” /* nuovo nome alla disciplina */
WHERE CodDisciplina = “INFO01”;
/* che ha per codice INFO01 */
DELETE FROM DISCIPLINA
WHERE CodDisciplina = “INFO01”; /* la materia INFO01 è stata soppressa */
DROP TABLE DISCIPLINA; /* l'intera tabella disciplina è stata soppressa */
Festa grande tra gli studenti ;-)!
VEDI PUNTATE PRECEDENTI
SLIDES ANNO SCORSO E LIBRO
Relazioni ed ntuple
Definizione matematica rigorosa di relazione, dalla teoria degli insiemi:
Una relazione R su una sequenza di insiemi D1, D2,... Dn (non
necessariamente distinti) è un sottoinsieme finito del prodotto cartesiano
tra tali insiemi ovvero indicando D1 x D2 x … x Dn il prodotto cartesiano si
ha:
●
R⊆D
R
D1 x D2 x … Dn
Gli insiemi D sono detti dominî della relazione, ad ogni dominio è associato
un nome detto ATTRIBUTO ed n è il grado della relazione.
Gli elementi di una relazione R sono detti n-uple (leggi 'ennuple') o
n-tuple (leggi 'entuple' from the english word 'ntuple' plural 'ntuples') o
anche t-uple e si indicano così:
(d1, d2,..., dn)
con
In access sono i records
le righe della tabella
d 1∈D 1, d2∈D 2,... , Dn∈dn
Ricorda che il PRODOTTO CARTESIANO TRA DUE INSIEMI A x B è l'insieme delle coppie
ordinate (a,b) con a appartenente ad A, b appartenente a B
https://it.wikipedia.org/wiki/Prodotto_cartesiano
Vedi pag 146-153
Relazioni, ntuple e tabelle
Una relazione può essere rappresentata [vedi pag. 147 del libro]
1) per elencazione
2) in forma tabellare
3) in forma insiemistica
→ utile nel caricamento dati/codice sql
→ utile nella visualizzazione dei dati
→ collegamento con la matematica
Prendiamo la relazione Automobile ecco le tre possibili rappresentazioni di
una istanza della relazione AUTOMOBILE(Targa, Marca, Modello)
AUTOMOBILE(Targa, Marca, Modello) = {
(AA000ZZ, Fiat, Grande Punto),
(CC200LN,Volkswagen, Passat),
(RR100AA,Renault,Megane)
(KK200II,Lada, Kalina)
}
elencazione
insiemistica
AUTOMOBILE
TARGA
MARCA
MODELLO
AA00ZZ
Fiat
Grande Punto
CC200LN
Volkswagen
Passat
RR200AA
Renault
Megane
KK200II
Lada
Kalina
AUTOMOBILE
1)(AA000ZZ, Fiat, Grande Punto),
2)(CC200LN,Volkswagen, Passat),
3)(RR100AA,Renault,Megane)
4)(KK200II,Lada, Kalina)
tabellare
righe → records
colonne → campi
Algebra relazionale
L'algebra relazionale, concetto formulato dall'informatico E. F. Codd, è il
fondamento teorico di ogni query SQL (o in un qualunque linguaggio/sistema di
gestione di un database).
In parole semplici: una query si può sempre ricondurre ad una precisa
operazione tra insiemi e quindi si può ricondurre alla ben nota teoria degli insiemi
(parte importante della matematica).
Il termine “algebra” è legato al tipo di operazioni tra insiemi che si potranno
“sommare” “sottrarre” “moltiplicare” in modo “simile”, ma non identico, ai numeri
ordinari
Ecco i principali (non gli unici!!) tipi di operazioni relazionali
fondamentali
operazioni
binarie
derivate
Vedi pagina 156 LIBRO
binarie
∩
unarie
selezione
proiezione
unione
differenza ▬
prodotto cartesiano x
∩
intersezione
join o giunzione
Unione di due relazioni compatibili
Due relazioni qualsiasi R e S si dicono compatibili se:
1) hanno lo stesso numero di attributi
2) ogni attributo ha la stessa posizione all'interno delle due relazioni
3) ogni attributo è dello stesso tipo
Date due relazioni R e S tra di loro compatibili l'unione è la relazione
fondamentale binaria (t sta per tupla) così definita:
∩
R
S= {t|t|∈R OR
t ∈S }
Corrisponde all'unione insiemistica dei due insiemi R e S
La tabella unione come sarà fatta?
→ avrà le stesse colonne delle due tabelle R e S, ma avrà sia le righe
della tabella R sia le righe della tabella S (senza ripetizioni)
Nella verifica vi posso dare due tabelle e
chiedere di fare la loro unione
Prova a costruire
l'unione
tra due relazioni
VEDI
pagina 157 del libro
Differenza di due relazioni compatibili
Date due relazioni R e S tra di loro compatibili la differenza è la
relazione fondamentale binaria (t sta per tupla) così definita:
R
▬
S= {t|t|∈R AND t ∉S }
Corrisponde alla differenza insiemistica dei due insiemi R e S
Si noti che
R▬ S
≠S
▬
R
come nella differenza tra numeri non vale la proprietà commutativa
(essa vale invece per l'unione, come per la somma tra numeri)
La tabella differenza R ▬ S come sarà fatta?
→ avrà le stesse colonne delle due tabelle R e S, ma avrà le righe della
tabella R tolte le righe di R eventualmente presenti anche in tabella S
Nella verifica vi posso dare due tabelle e
chiedere di fare la loro differenza
Prova a costruire
la differenza
tra due relazioni
VEDI
pagina 158 del libro
Intersezione di due relazioni compatibili
Date due relazioni R e S tra di loro compatibili l'intersezione è la
relazione derivata binaria (t sta per tupla) così definita:
R ∩ S= {t | t ∈R AND t ∈S }
Corrisponde all'intersezione insiemistica dei due insiemi R e S
come per l'unione vale la
proprietà commutativa →
R ∩ S = S∩ R
La tabella intersezione come sarà fatta?
→ avrà le stesse colonne delle due tabelle R e S, ma avrà le righe
costituite da tutte e sole le tuple presenti sia in R sia in S (se ci sono)
Nella verifica vi posso dare due tabelle e
chiedere di fare la loro intersezione
Prova a costruire
l'intersezione
tra due relazioni
VEDI
pagina 161 del libro
Proiezione di una relazione
Attenzione: questa operazione è di tipo UNARIO ovvero agisce solo su una relazione
Date una relazione R e un sottoinsieme A = {A1,...,A2,...,An} dei suoi
attributi la proiezione è la relazione fondamentale unaria (t sta per
tupla) così definita:
(R )= {t [ A , A , ... A ] t∈R }
A
1
2
n
La notazione sopra indicata significa che dalla relazione iniziale R si
estraggono soltanto le colonne contenute nel sottoinsieme A, ovvero si sta
proiettando la relazione R su A.
La tabella proiezione come sarà fatta?
→ avrà le stesse righe della tabella R, ma un numero di colonne ridotto,
limitato soltanto al sottoinsieme A scelto. Il grado (= numero di
colonne) della relazione proiettata sarà minore al grado della relazione
iniziale.
Nella verifica vi posso dare una tabella e
un insieme e chiedere di fare la proiezione
prova a costruire
una proiezione
VEDI
pagina 159 del libro
Selezione o restrizione di una relazione
Attenzione: questa operazione è di tipo UNARIO ovvero agisce solo su una relazione
Date una relazione R e un predicato P la selezione o restrizione è la
relazione fondamentale unaria (t sta per tupla) così definita:
(R)={t | t∈R
P (t )=
}
AND
VERO
P
La notazione sopra indicata significa che dalla relazione iniziale R si
estraggono soltanto le righe e le colonne che soddisfano la condizione
P(t)
La tabella selezione come sarà fatta?
→ sarà una tabella ridotta alle sole tuple (= righe) che soddisfano P(t), la
condizione logica [predicato = condizione logica vera o falsa]
La si può trovare indicata anche con il simbolo
Nella verifica vi posso dare una tabella e
un predicato e chiedere di fare la
restrizione (= selezione)
prova a costruire
una restrizione
VEDI
pagina 159 del libro
A cosa corrispondono
e
in SQL?
Cosa sto facendo in questa query:
SELECT Matricola, CodDisciplina /*nota l'uso della virgola per separare */
FROM INSEGNA
/* perché voglio sia la Matricola che il CodDisciplina */
Sto proiettando la tabella INSEGNA sugli attributi Matricola, CodDisciplina
E' un'operazione relazionale di tipo
con
(R
)
A
{
R = INSEGNA
A = { Matricola, CodDisciplina}
LE QUERY
SONO
OPERAZIONI
RELAZIONALI!!
E in quest'altra query:
SELECT * /* seleziono TUTTI GLI ATTRIBUTI */
FROM INSEGNA
WHERE (NumOre=12); /* ma questo è il predicato!!! restrizione!! */
Sto restringendo la tabella INSEGNA alla condizone P(t) = “NumOre=12”
E' un'operazione relazionale di tipo
(R)
NumOre=12
(o
con R = INSEGNA
secondo un'altra notazione)
LE QUERY SONO OPERAZIONI
RELAZIONALI!!
x
relazione
Prodotto cartesiano di due relazioni
Date due relazioni R e S il prodotto cartesiano è la
fondamentale binaria (t sta per tupla) così definita:
R x S={t | t =(r , s), r ∈R AND s∈S }
La notazione sopra indicata significa che le nuove tuple sono le coppie
ordinate (r,s) ottenute a partire dalle tuple originarie r ed s, come per il
prodotto cartesiano tra due insiemi.
La tabella prodotto cartesiano come sarà fatta?
→ sarà una “tabellona”, una tabella che avrà un numero di colonne pari
alla somma del numero delle colonne delle tabelle di partenza e un
numero di righe pari al prodotto del numero delle righe delle tabelle
originarie, contenenti tutte le combinazioni possibili (senza ripetizioni).
ATTENZIONE:
il prodotto cartesiano si può fare anche tra relazioni
non compatibili, ma per evitare ambiguità i nomi degli attributi delle due
relazioni devono essere diversi, occorre ridenominare i nomi uguali!!
Nella verifica vi posso dare una tabella e
un predicato e chiedere di fare il prodotto
cartesiano
prova a costruire
una prodotto
cartesiano
VEDI
pagina 160 del libro
Giunzione naturale di due relazioni
Quando si vuole collegare le informazioni di due tabelle anche molto
diverse, spesso si procede all'operazione di giunzione ovvero join
(sinonimo inglese merge).
Attenzione: ci sono vari tipi di join, utili a seconda del caso.
Il natural join è così:
Date due relazioni R e S e un attributo A di R e un attributo B di S, aventi
lo stesso tipo, la giunzione naturale è la relazione derivata binaria (t sta
per tupla) ottenuta così:
1) Si effettua il prodotto cartesiano tra R e S
2) Sulla relazione ottenuta si effettua una restrizione sulle tuple tali che
A = B il valore di A (in R) deve essere uguale al valore di B (in S)
3) Si elimina la colonna comune ripetuta
In parole semplici stiamo unendo le due tabelle,
creando una nuova tabella composta dalle tuple
che hanno un attributo che ha lo stesso valore in
entrambe le tabelle. Unisco in base ad un attributo
comune che è lo stesso in entrambe le tabelle
R
S
VEDI
pagina 163 del libro
Esempio: siano date la relazioni IMPIEGATO e DIPARTIMENTO
IMPIEGATO(CodiceImpiegato, Nome, NomeDipartimento)
DIPARTIMENTO(NomeDipartimento,NomeDiretttore)
Com'è IMPIEGATO
DIPARTIMENTO su NomeDipartimento?
È la nuova relazione
GN(CodiceImpiegato, Nome, NomeDipartimento, NomeDirettore)
Questa relazione permette di vedere in'unica tabella il codice, il nome degli
impiegati, il nome del dipartimento E il nome del direttore del dipartimento.
Attenzione: non di un direttore qualunque, ma proprio del direttore di quel
dipartimento a cui l'impiegato appartiene! (punto numero 2 del procedimento del natural join)
Che cosa ci ricorda questo meccanismo? → la chiave esterna!!!
Per approfondire...
tanti modi per fare join ;-)
In SQL ci sono vari modi di fare il join, usando i comandi
<tabella1> INNER JOIN <tabella2> ON <condizione>
<tabella1> LEFT JOIN <tabella2> ON <condizione>
<tabella1> RIGHT JOIN <tabella2> ON <condizione>
Ci sono anche TANTE
altre
possibilità
e
sottigliezze che non
affrontiamo
pagine 186 - 187 libro
Il più usato in SQL è il comando INNER JOIN ≈ NATURAL JOIN
Conclusioni (fragili e provvisorie)
Quando interrogo un database con SQL o altri programmi, sto
facendo un insieme di operazioni relazionali
Abbiamo descritto le principali operazioni relazionali di uso
comune nei database
Abbiamo rivisto le operazioni fatte l'anno scorso, da un altro
punto di vista
E' il modo tipico di procedere della scienza, il metodo scientifico
Teoria1 → Pratica → Teoria1 → Pratica
Teoria2 → Pratica → Teoria2 → Pratica
Pratica → Teoria3 → Pratica → Teoria3
Pratica → Teoria4 → Pratica → Teoria4
… … … … … … … … … … ...
VOLTE
Non fermarti... BUON PROSEGUIMENTO ! GOOD LUCK !
MAI
by Pablo ;-)