Andrea De Lorenzo, University of Trieste
Giorno | Orario | Aula |
---|---|---|
Lunedì | 16:10 - 18:40 | Aula A C2 |
Martedì | 14:15 - 16:45 | Aula A C9 |
Mercoledì | 9:30 - 11:00 | Aula 2 C5 |
Progetto va consegnato 3 giorni lavorativi prima dell’appello Se l’esame è mercoledì, venerdì è troppo tardi per inviare il progetto!
L’essere umano genera e gestisce tante informazioni:
... salvate in tanti modi diversi
Anche le organizzazioni generano informazioni:
Le informazioni vanno codificate
Informazione: notizia, dato o elemento che consente di avere conoscenza più o meno esatta di fatti, situazioni, modi di essere
Dato: elemento di informazione costituito da simboli che debbono essere elaborati
Lun - Ven
Sabato
Festivi
Come codifico i numeri?
10010011110011001111
separare i dati dalle applicazioni che operano su essi
Collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione.
Software in grado di gestire collezioni di dati che siano:
Collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione
Collezione di dati gestita da un DBMS
Un DBMS deve garantire:
FS | DBMS | |
---|---|---|
Grandi moli di dati | ✓ | ✓ |
Persistenti | ✓ | ✓ |
Condivisi | ✓ | ✓ |
Affidabile | ✓ | ✓ |
Privatezza | ✓ | ✓ |
Efficienza | ? | ? |
Efficacia | X | ✓ |
Descrizione dei dati contenuta nell'applicazione
Descrizione dei dati contenuta nell'applicazione
Descrizione dei dati contenuta nell'applicazione
Analisi del problema
Modello astratto
Non dipende dallo strumento utilizzato
Come rappresentare i dati individuati con il modello concettuale
Nei vecchi sistemi il modo in cui venivano organizzati i dati e le tecniche per accedervi facevano parte della logica e del codice del programma.
In ogni base di dati esistono:
Schemi
Permettono di rappresentare i dati in modo indipendente da ogni sistema:
Modello più diffuso: Entity-Relationship
Rappresentazione dello schema logico per mezzo di strutture di memorizzazione
Come è organizzato il DB. Diverse soluzioni:
Lo schema logico è INDIPENDENTE da quello fisico
Es: una tabella è utilizzata sempre allo stesso modo qualunque sia la sua realizzazione fisica (che può variare nel tempo)
L'amministratore del DB può modificare la struttura interna dei dati senza toccarne la visibilità esterna
IMMUNITÀ DELLE APPLICAZIONI A MODIFICHE DI STRUTTURA
Corso | Docente | Aula |
---|---|---|
Reti | Bartoli | N3 |
Programmazione | Medvet | N3 |
ML | Medvet | G |
Nome | Edificio | Piano |
---|---|---|
DS1 | H3 | 3 |
N3 | C2 | 2 |
G | Principale | PT |
Corso | Docente | Aula | Edificio | Piano |
---|---|---|---|---|
Reti | Bartoli | N3 | C2 | 2 |
Programmazione | Medvet | N3 | C2 | 2 |
ML | Medvet | G | Principale | PT |
Nome | Cognome | Matricola | Media | ISEE |
---|---|---|---|---|
Tizio | Caio | IN0001 | 30 | 5000€ |
Bubba | Gump | IN0003 | 27 | 1000000e |
Jean Luc | Picard | IN0004 | 19 | 40000€ |
Nome | Cognome | Matricola | ISEE |
---|---|---|---|
Tizio | Caio | IN0001 | 5000€ |
Bubba | Gump | IN0003 | 1000000e |
Jean Luc | Picard | IN0004 | 40000€ |
Nome | Cognome | Matricola | Media |
---|---|---|---|
Tizio | Caio | IN0001 | 30 |
Bubba | Gump | IN0003 | 27 |
Jean Luc | Picard | IN0004 | 19 |
Problemi:
Liberarsi dei puntatori fisici
Tabelle: organizzazione rettangolare di dati
Tutte le informazioni in un DBR sono rappresentate esplicitamente da valori in tabelle (DEFINIZIONE)
Ciascun valore deve essere raggiunto univocamente da un nome di tabella, chiave primaria e nome di colonna (CHIAVI PRIMARIE)
Sono supportati per rappresentare informazioni mancanti indipendentemente dal tipo di dato
Un data base relazionale deve essere strutturato logicamente come i dati e gestibile con lo stesso linguaggio
Un DBR può supportare diversi linguaggi, ma deve supportare un linguaggio “English like” dove sia possibile (DEFINIZIONE DI SQL):
Le viste che sono modificabili teoricamente dall’utente lo devono essere anche dal sistema (cruciale per campi calcolati);
Affinché una vista sia modificabile, il DBMS deve essere in grado di tracciare ciascuna colonna e ciascuna riga UNIVOCAMENTE fino alle tabelle origine
Corso | Docente | Aula |
---|---|---|
Reti | Bartoli | N3 |
Programmazione | Medvet | N3 |
ML | Medvet | G |
Nome | Edificio | Piano |
---|---|---|
DS1 | H3 | 3 |
N3 | C2 | 2 |
G | Principale | PT |
Corso | Docente | Aula | Edificio | Piano |
---|---|---|---|---|
Reti | Bartoli | N3 | C2 | 2 |
Programmazione | Medvet | N3 | C2 | 2 |
ML | Medvet | G | Principale | PT |
Studente | Esame | Voto |
---|---|---|
Scaini | Reti | 30 |
Scaini | ML | 28 |
Bassi | ML | 30 |
Bassi | Reti | 30 |
Studente | Media |
---|---|
Scaini | 29 |
Bassi | 30 |
Inserire e aggiornare devono avere la stessa logica “a righe” dell’estrazione (SET ORIENTED)
I programmi applicativi non devono sentire alcuna modifica fatta sul metodo e la locazione fisica dei dati
Le modifiche al livello logico non devono richiedere cambiamenti non giustificati alle applicazioni che utilizzano il database (VISTE)
Vincoli di integrità devono essere implementabili sul motore (cruciale)
La distribuzione di porzioni del database su una o più allocazione fisiche o geografiche deve essere invisibile agli utenti del sistema
Garantisce l’impossibilità di bypassare le regole di integrità
Data Base dove tutti i dati visibili all’utente sono organizzati strettamente in tabelle di valori, e dove tutte le operazioni vengono eseguite su tabelle e danno come risultato tabelle.
Relazione matematica (teoria degli insiemi)
Associazione nel modello Entity-Relationship
a | x |
a | y |
a | z |
b | x |
b | y |
b | z |
a | x |
a | z |
b | y |
Juve | Lazio | 3 | 1 |
Lazio | Milan | 2 | 0 |
Juve | Roma | 2 | 0 |
Roma | Milan | 0 | 1 |
A ciascun dominio si associa un nome (attributo), che ne descrive il "ruolo"
Casa | Fuori | RetiCasa | RetiFuori |
---|---|---|---|
Juve | Lazio | 3 | 1 |
Lazio | Milan | 2 | 0 |
Juve | Roma | 2 | 0 |
Roma | Milan | 0 | 1 |
I riferimento fra dati in relazioni diverse sono rappresentati per mezzo di valori dei domini che compaiono nelle $n$-uple
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | DataDiNascita |
6554 | Rossi | Mario | 05/12/1978 |
8765 | Neri | Paolo | 03/11/1976 |
9283 | Verdi | Luisa | 12/11/1978 |
3456 | Rossi | Maria | 01/02/1978 |
Esami | ||
---|---|---|
Studente | Voto | Corso |
3456 | 30 | 04 |
3456 | 24 | 02 |
9283 | 28 | 01 |
6554 | 26 | 01 |
Corsi | ||
---|---|---|
Codice | Titolo | Docente |
01 | Analisi | Mario |
02 | Chimica | Bruni |
04 | Chimica | Verdi |
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | DataDiNascita |
6554 | Rossi | Mario | 05/12/1978 |
8765 | Neri | Paolo | 03/11/1976 |
9283 | Verdi | Luisa | 12/11/1978 |
3456 | Rossi | Maria | 01/02/1978 |
Studenti Lavoratori | |||
---|---|---|---|
Matricola | |||
6554 | |||
3456 |
Da Filippo Via Roma 2, Roma | ||
Ricevuta Fiscale 1235 del 12/01/2020 | ||
3 | Coperti | 3,00 |
2 | Antipasti | 6,20 |
3 | Primi | 12,00 |
2 | Bistecche | 18,00 |
- | ||
- | ||
Totale | 39,20 |
Da Filippo Via Roma 2, Roma | ||
Ricevuta Fiscale 1240 del 13/10/2020 | ||
2 | Coperti | 2,00 |
2 | Antipasti | 7,00 |
2 | Primi | 8,00 |
2 | Orate | 20,00 |
2 | Caffè | 2,00 |
- | ||
Totale | 39,00 |
Ricevute | |||||
---|---|---|---|---|---|
Numero | Data | Qtà | Descrizione | Importo | Totale |
1235 | 12/10/2020 | 3 | Coperti | 3,00 | 39,20 |
2 | Antipasti | 6,20 | |||
3 | Primi | 12,00 | |||
2 | Bistecche | 18,00 | |||
1240 | 12/10/2020 | 2 | Coperti | 2,00 | 39,00 |
... | ... | ... |
I valori devono essere semplici
Ricevute | ||
---|---|---|
Numero | Data | Totale |
1235 | 12/10/2020 | 39,20 |
1240 | 12/10/2020 | 39,00 |
Dettaglio | |||
---|---|---|---|
Numero | Qtà | Descrizione | Importo |
1235 | 3 | Coperti | 3,00 |
1235 | 2 | Antipasti | 6,20 |
1235 | 3 | Primi | 12,00 |
1235 | 2 | Bistecche | 18,00 |
1240 | 2 | Coperti | 2,00 |
... | ... | ... | ... |
Dipende da cosa ci interessa:
Ricevute | ||
---|---|---|
Numero | Data | Totale |
1235 | 12/10/2020 | 39,20 |
1240 | 12/10/2020 | 39,00 |
Dettaglio | ||||
---|---|---|---|---|
Numero | Riga | Qtà | Descrizione | Importo |
1235 | 1 | 3 | Coperti | 3,00 |
1235 | 2 | 2 | Antipasti | 6,20 |
1235 | 3 | 3 | Primi | 12,00 |
1235 | 4 | 2 | Bistecche | 18,00 |
1240 | 1 | 2 | Coperti | 2,00 |
... | ... | ... | ... | ... |
Ogni elemento in una tabella può essere o un valore del dominio oppure il valore nullo NULL
Capi di Stato | ||
---|---|---|
Nome | SecondoNome | Cognome |
Franklin | Delano | Roosvelt |
Winston | Churchill | |
Charles | De Gaulle | |
Josip | Stalin |
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | DataDiNascita |
6554 | Rossi | Mario | 05/12/1978 |
9283 | Verdi | Luisa | 12/11/1978 |
NULL | Rossi | Maria | 01/02/1978 |
Esami | ||
---|---|---|
Studente | Voto | Corso |
NULL | 30 | NULL |
NULL | 24 | 02 |
9283 | 28 | 01 |
Corsi | ||
---|---|---|
Codice | Titolo | Docente |
01 | Analisi | Mario |
02 | NULL | NULL |
04 | Chimica | Verdi |
Esistono istanze di basi di dati che, pur sintatticamente corrette, non rappresentano informazioni possibili per l’applicazione di interesse.
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Verdi | Luisa | |
7876463 | Rossi | Maria |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
739430 | 24 | 04 |
Un vincolo è una funzione booleana (un predicato): associa ad ogni istanza il valore vero o falso
Alcuni vincoli (ma non tutti) sono supportati dai DBMS
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Verdi | Luisa | |
7876463 | Rossi | Maria |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
739430 | 24 | 04 |
Voto ≥ 18 && Voto ≤ 30
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Verdi | Luisa | |
7876463 | Rossi | Maria |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
739430 | 24 | 04 |
Lode solo se Voto == 30
Stipendi | |||
---|---|---|---|
Impiegato | Lordo | Ritenute | Netto |
Rossi | 55.000 | 12.500 | 42.500 |
Verdi | 45.000 | 10.000 | 35.000 |
Bruni | 47.000 | 11.000 | 36.000 |
Lordo = (Ritenute + Netto)
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Verdi | Luisa | |
7876463 | Rossi | Maria |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
739430 | 24 | 04 |
Matricola | Cognome | Nome | Corso | Nascita |
---|---|---|---|---|
27655 | Rossi | Mario | Ing inf | 5/12/78 |
78763 | Rossi | Mario | Ing inf | 3/11/76 |
65432 | Neri | Piero | Ing mecc | 10/7/79 |
87654 | Neri | Mario | Ing inf | 3/11/76 |
67653 | Rossi | Piero | Ing mecc | 5/12/78 |
Matricola | Cognome | Nome | Corso | Nascita |
---|---|---|---|---|
27655 | Rossi | Mario | Ing inf | 5/12/78 |
78763 | Rossi | Mario | Ing inf | 3/11/76 |
65432 | Neri | Piero | Ing mecc | 10/7/79 |
87654 | Neri | Mario | Ing inf | 3/11/76 |
67653 | Rossi | Piero | Ing mecc | 5/12/78 |
Matricola è una chiave:
Matricola | Cognome | Nome | Corso | Nascita |
---|---|---|---|---|
27655 | Rossi | Mario | Ing inf | 5/12/78 |
78763 | Rossi | Mario | Ing inf | 3/11/76 |
65432 | Neri | Piero | Ing mecc | 10/7/79 |
87654 | Neri | Mario | Ing inf | 3/11/76 |
67653 | Rossi | Piero | Ing mecc | 5/12/78 |
Cognome, nome, nascita è un'altra chiave:
Matricola | Cognome | Nome | Corso | Nascita |
---|---|---|---|---|
27655 | Rossi | Mario | Ing inf | 5/12/78 |
78763 | Rossi | Mario | Ing Civile | 3/11/76 |
65432 | Neri | Piero | Ing mecc | 10/7/79 |
87654 | Neri | Mario | Ing inf | 3/11/76 |
67653 | Rossi | Piero | Ing mecc | 5/12/78 |
Non ci sono $n$-uple uguali su Cognome e Corso:
Matricola | Cognome | Nome | Corso | Nascita |
---|---|---|---|---|
NULL | NULL | Mario | Ing inf | 5/12/78 |
78763 | Rossi | Mario | Ing inf | 3/11/76 |
65432 | Neri | Piero | Ing mecc | 10/7/79 |
87654 | Neri | Mario | Ing inf | NULL |
NULL | Rossi | Piero | NULL | 5/12/78 |
Matricola | Cognome | Nome | Corso | Nascita |
---|---|---|---|---|
27655 | NULL | Mario | Ing inf | 5/12/78 |
78763 | Rossi | Mario | Ing inf | 3/11/76 |
65432 | Neri | Piero | Ing mecc | 10/7/79 |
87654 | Neri | Mario | Ing inf | NULL |
67653 | Rossi | Piero | NULL | 5/12/78 |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
787643 | 27 | e lode | 03 |
787643 | 24 | 04 |
Studenti | ||
---|---|---|
Matricola | Cognome | Nome |
276545 | Rossi | Mario |
787643 | Neri | Piero |
787642 | Bianchi | Luca |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
787643 | 27 | e lode | 03 |
787647 | 24 | 04 |
Studenti | ||
---|---|---|
Matricola | Cognome | Nome |
276545 | Rossi | Mario |
787643 | Neri | Piero |
787642 | Bianchi | Luca |
Infrazioni | ||||
---|---|---|---|---|
Codice | Data | Vigile | Prov | Targa |
34321 | 1/2/95 | 3987 | MI | 39548K |
53524 | 4/3/95 | 3295 | TO | E39548 |
64521 | 5/4/96 | 3295 | PR | 839548 |
73321 | 5/2/98 | 9345 | PR | 839548 |
Vigili | ||
---|---|---|
Matricola | Cognome | Nome |
3987 | Rossi | Luca |
3295 | Neri | Piero |
9345 | Neri | Mario |
7543 | Mori | Gino |
Infrazioni | ||||
---|---|---|---|---|
Codice | Data | Vigile | Prov | Targa |
34321 | 1/2/95 | 3987 | MI | 39548K |
53524 | 4/3/95 | 3295 | TO | E39548 |
64521 | 5/4/96 | 3295 | PR | 839548 |
73321 | 5/2/98 | 9345 | PR | 839548 |
Auto | |||
---|---|---|---|
Prov | Targa | Cognome | Nome |
MI | 39548K | Rossi | Mario |
TO | E39548 | Rossi | Mario |
PR | 839548 | Neri | Luca |
Un vincolo di integrità referenziale (“foreing key”) fra attributi $X$ di una relazione $r_1$ e un’altra relazione $r_2$ impone ai valori su $X$ in $r_1$ di comparire come valori della chiave primaria di $r_2$
Vigile
della relazione Infrazioni
e la relazione Vigili
Prov
e Numero di Infrazioni
e la relazione Auto
Infrazioni | ||||
---|---|---|---|---|
Codice | Data | Vigile | Prov | Targa |
34321 | 1/2/95 | 3987 | MI | 39548K |
53524 | 4/3/95 | 3295 | TO | E39548 |
64521 | 5/4/96 | 3295 | PR | 839548 |
73321 | 5/2/98 | 9345 | PR | 839548 |
Auto | |||
---|---|---|---|
Prov | Targa | Cognome | Nome |
MI | E39548 | Rossi | Mario |
TO | F34268 | Rossi | Mario |
PR | 839548 | Neri | Luca |
Impiegati | ||
---|---|---|
Matricola | Cognome | Progetto |
34321 | Rossi | IDEA |
53524 | Neri | XYZ |
64521 | Verdi | NULL |
73321 | Bianchi | IDEA |
Progetti | |||
---|---|---|---|
Codice | Inizio | Durata | Costo |
IDEA | 01/2000 | 36 | 200 |
XYZ | 07/2001 | 24 | 120 |
BOH | 09/2001 | 24 | 150 |
Impiegati | ||
---|---|---|
Matricola | Cognome | Progetto |
34321 | Rossi | IDEA |
53524 | Neri | XYZ |
64521 | Verdi | NULL |
73321 | Bianchi | IDEA |
Progetti | |||
---|---|---|---|
Codice | Inizio | Durata | Costo |
IDEA | 01/2000 | 36 | 200 |
XYZ | 07/2001 | 24 | 120 |
BOH | 09/2001 | 24 | 150 |
Impiegati | ||
---|---|---|
Matricola | Cognome | Progetto |
34321 | Rossi | IDEA |
53524 | Neri | NULL |
64521 | Verdi | NULL |
73321 | Bianchi | IDEA |
Progetti | |||
---|---|---|---|
Codice | Inizio | Durata | Costo |
IDEA | 01/2000 | 36 | 200 |
XYZ | 07/2001 | 24 | 120 |
BOH | 09/2001 | 24 | 150 |
Auto | |||
---|---|---|---|
Prov | Targa | Cognome | Nome |
MI | 39548K | Rossi | Mario |
TO | E39548 | Rossi | Mario |
PR | 839548 | Neri | Luca |
Incidenti | |||||
---|---|---|---|---|---|
Codice | Data | ProvA | TargaA | ProvB | TargaB |
34321 | 1/2/95 | TO | E39548 | MI | 39548K |
64521 | 5/4/96 | PR | 839548 | TO | E39548 |
CREATE/DROP/ALTER TABLE/VIEW/INDEX
SELECT
- INSERT
- DELETE
- UPDATE
GRANT
- REVOKE
COMMIT
- ROLLBACK
DECLARE
- OPEN
- FETCH
- CLOSE
SHOW DATABASES;
Ritorna l’elenco dei Database presenti nel DBMS
I comandi possono occupare anche più righe e terminano con il ;
CREATE DATABASE nomeDataBase;
Crea un nuovo DataBase con il nome specificato e lo rende accessibile all’utente ROOT.
CREATE DATABASE IF NOT EXISTS nomeDataBase;
Crea il DB solo se non esiste già
DROP DATABASE [IF EXISTS] nomeDataBase;
usiamo [...]
per indicare le parti opzionali dei
comandi.
USE nomeDataBase;
Tutti i comandi ora saranno riferiti a questo DB.
Istruzione CREATE TABLE
;
specifica attributi, domini e vincoli
CREATE TABLE [IF NOT EXISTS] nomeTabella(
tipo nomeAttributo1,
tipo attributo2,
...
tipo attributoN
)
Tipo | Byte | Minimo | Massimo |
---|---|---|---|
TINYINT | 1 | $-128$ | $127$ |
SMALLINT | 2 | $-32768$ | $32767$ |
MEDIUMINT | 3 | $-8388608$ | $8388607$ |
INT | 4 | $-2147483648$ | $2147483647$ |
BIGINT | 8 | $-2^{63}$ | $2^{63}-1$ |
INT(N)
: suggeriamo al motore di usare N
caratteri per mostrare il dato; es: INT(11)
numeric(i,n)
salva esattamente n
cifre decimalidecimal(i,n)
salva almeno n
cifre decimaliTipo | Descrizione |
---|---|
CHAR | Stringa di lunghezza fissa non binaria |
VARCHAR | Stringa di lunghezza variabile non binaria |
BINARY | Sequenza binaria a lunghezza fissa |
VARBINARY | Sequenza binaria a lunghezza variabile |
Tipo | Descrizione |
---|---|
TINYBLOB | Binary Large OBject piccolo |
BLOB | Binary Large OBject |
MEDIUMBLOB | Binary Large OBject medio |
LONGBLOB | Binary Large OBject grande |
Tipo | Descrizione |
---|---|
TINYTEXT | Stringa non binaria piccola |
TEXT | Stringa non binaria |
MEDIUMTEXT | Stringa non binaria medio |
LONGTEXT | Stringa non binaria grande |
YEAR
- anno nel formato YYYY
DATE
- data nel formato YYYY-MM-DD
TIME
- tempo nel formato hh:mm:ss
DATETIME
- tempo nel formato YYYY-MM-DD hh:mm:ss
TIMESTAMP
- come DATETIME
, ma si aggiorna da soloTipo | Descrizione |
---|---|
GEOMETRY | Valore spaziale di qualsiasi tipo |
POINT | Coordinate X, Y |
LINESTRING | Curva (uno o più POINT) |
POLYGON | Un poligono |
… e molti altri
lunghezza + 1
; posso indicare una lunghezza massimavalore | CHAR(4) | spazio | VARCHAR(4) | spazio |
---|---|---|---|---|
'' |
'____' |
4 byte | '' |
1 byte |
'ab' |
'ab__' |
4 byte | 'ab' |
3 byte |
'abcd' |
'abcd' |
4 byte | 'abcd' |
5 byte |
'abcdef' |
'abcd' |
4 byte | 'abcd' |
5 byte |
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Neri | Piero | |
7876462 | Bianchi | Luca |
Corsi | ||
---|---|---|
Codice | Titolo | Docente |
01 | Analisi | Mario |
02 | Chimica | Bruni |
04 | Chimica | Verdi |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
7876463 | 24 | 04 |
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Neri | Piero | |
7876462 | Bianchi | Luca |
CREATE TABLE Studenti(
matricola int(11),
cognome varchar(45),
nome varchar(45)
);
DROP TABLE [IF EXISTS] nomeTabella;
… intuitivo
DROP TABLE [IF EXISTS] nomeTabella1;
nomeTabella2,
nomeTabella3,
...;
Posso definire dei vincoli:
PRIMARY KEY
- chiave primaria (una sola, implica NOT NULL
)NOT NULL
UNIQUE
- definisce chiaviCHECK
- vedremo più avantiStudenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Neri | Piero | |
7876462 | Bianchi | Luca |
CREATE TABLE Studenti(
matricola int(11) PRIMARY KEY,
cognome varchar(45),
nome varchar(45)
);
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Neri | Piero | |
7876462 | Bianchi | Luca |
CREATE TABLE Studenti(
matricola int(11),
cognome varchar(45),
nome varchar(45),
PRIMARY KEY (matricola)
);
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Neri | Piero | |
7876462 | Bianchi | Luca |
CREATE TABLE Studenti(
matricola int(11) PRIMARY KEY,
cognome varchar(45) NOT NULL,
nome varchar(45) NOT NULL
);
Corsi | ||
---|---|---|
Codice | Titolo | Docente |
01 | Analisi | Mario |
02 | Chimica | Bruni |
04 | Chimica | Verdi |
CREATE TABLE Corsi(
codice int(11) PRIMARY KEY,
titolo varchar(45) NOT NULL,
docente varchar(45)
);
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
7876463 | 24 | 04 |
CREATE TABLE Esami(
studente int(11) PRIMARY KEY,
voto smallint NOT NULL,
lode bool,
corso int(11) PRIMARY KEY
);
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
7876463 | 24 | 04 |
CREATE TABLE Esami(
studente int(11),
voto smallint NOT NULL,
lode bool,
corso int(11),
PRIMARY KEY (studente, corso)
);
CREATE TABLE Corsi(
codice int(11) NOT NULL UNIQUE,
titolo varchar(45) NOT NULL,
docente varchar(45)
);
CREATE TABLE Esami(
studente int(11) NOT NULL UNIQUE,
voto smallint NOT NULL,
lode bool,
corso int(11) NOT NULL UNIQUE
);
In general, a unique constraint is violated when there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are not considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns. This behavior conforms to the SQL standard, but we have heard that other SQL databases might not follow this rule. So be careful when developing applications that are intended to be portable.
PostgreSQL Manual
CREATE TABLE nomeTabella (
id int(11) PRIMARY KEY,
campo1 int(19),
campo2 int(12),
CONSTRAINT [nome] UNIQUE(campo1, campo2)
);
ottimo come chiave primaria!
CREATE TABLE Studenti(
matricola int(11) PRIMARY KEY AUTO_INCREMENT,
cognome varchar(45),
nome varchar(45)
);
CREATE TABLE nome(
nomeAttributo tipo DEFAULT valore
);
CREATE TABLE Corsi(
codice int(11) PRIMARY KEY,
titolo varchar(45) NOT NULL DEFAULT "nuovo",
docente varchar(45)
);
CREATE TABLE nome(
nomeAttributo tipo COMMENT "commento"
);
CREATE TABLE Corsi(
codice int(11) PRIMARY KEY,
titolo varchar(45) NOT NULL
COMMENT "Titolo del corso",
docente varchar(45)
);
Software | |||
---|---|---|---|
Modulo | Versione | Tipo | Data |
Esse3 | 1.00 | alfa | 10/10/2014 |
Esse3 | 1.00 | beta | 10/10/2014 |
Esse3 | 1.00 | NULL | 16/11/2014 |
Esse3 | 1.02 | alfa | 18/12/2014 |
Esse4 | 1.00 | alfa | 12/01/2015 |
Modulo, Versione e Tipo sono una PK?
Software | |||
---|---|---|---|
Modulo | Versione | Tipo | Data |
Esse3 | 1.00 | alfa | 10/10/2014 |
Esse3 | 1.00 | beta | 14/11/2014 |
Esse3 | 1.00 | NULL | 16/11/2014 |
Esse3 | 1.02 | alfa | 18/12/2014 |
Esse4 | 1.00 | alfa | 12/01/2015 |
Software | |||
---|---|---|---|
Modulo | Versione | Tipo | Data |
Esse3 | 1.00 | alfa | 10/10/2014 |
Esse3 | 1.00 | beta | 14/11/2014 |
Esse3 | 1.00 | '' | 16/11/2014 |
Esse3 | 1.02 | alfa | 18/12/2014 |
Esse4 | 1.00 | alfa | 12/01/2015 |
FOREIGN KEY
e REFERENCES
e permettono
di definire vincoli di integrità referenzialecolonne che sono FK
colonne nella relazione (tabella) esterna
Studenti | |||
---|---|---|---|
Matricola | Cognome | Nome | |
276545 | Rossi | Mario | |
7876463 | Neri | Piero | |
7876462 | Bianchi | Luca |
Corsi | ||
---|---|---|
Codice | Titolo | Docente |
01 | Analisi | Mario |
02 | Chimica | Bruni |
04 | Chimica | Verdi |
Esami | |||
---|---|---|---|
Studente | Voto | Lode | Corso |
276545 | 32 | 01 | |
276545 | 30 | e lode | 02 |
7876463 | 27 | e lode | 03 |
7876463 | 24 | 04 |
CREATE TABLE Esami(
studente int(11),
voto smallint NOT NULL,
lode bool,
corso int(11),
PRIMARY KEY (studente, corso),
FOREIGN KEY (studente) REFERENCES Studenti(matricola)
);
Non funziona ovunque (MySql)
CREATE TABLE Esami(
studente int(11) REFERENCES Studenti(matricola),
voto smallint NOT NULL,
lode bool,
corso int(11),
PRIMARY KEY (studente, corso)
);
CREATE TABLE Esami(
studente int(11),
voto smallint NOT NULL,
lode bool,
corso int(11),
PRIMARY KEY (studente, corso),
FOREIGN KEY (studente) REFERENCES Studenti(matricola)
FOREIGN KEY (corso) REFERENCES Corsi(codice)
);
CREATE TABLE Esami(
studente int(11),
voto smallint NOT NULL,
lode bool,
corso int(11),
PRIMARY KEY (studente, corso),
CONSTRAINT FK_Studente FOREIGN KEY (studente) REFERENCES Studenti(matricola)
CONSTRAINT FK_Corso FOREIGN KEY (corso) REFERENCES Corsi(codice)
);
SET foreign_key_checks = 0
SET foreign_key_checks = 1
Infrazioni | ||||
---|---|---|---|---|
Codice | Data | Vigile | Prov | Targa |
34321 | 1/2/95 | 3987 | MI | 39548K |
53524 | 4/3/95 | 3295 | TO | E39548 |
64521 | 5/4/96 | 3295 | PR | 839548 |
73321 | 5/2/98 | 9345 | PR | 839548 |
Automobile | |||
---|---|---|---|
Prov | Targa | Cognome | Nome |
MI | 39548K | Rossi | Mario |
TO | E39548 | Rossi | Mario |
PR | 839548 | Neri | Luca |
Vigili | ||
---|---|---|
Matricola | Cognome | Nome |
3987 | Rossi | Luca |
3295 | Neri | Piero |
9345 | Neri | Mario |
7543 | Mori | Gino |
Vigili | ||
---|---|---|
Matricola | Cognome | Nome |
3987 | Rossi | Luca |
3295 | Neri | Piero |
9345 | Neri | Mario |
7543 | Mori | Gino |
CREATE TABLE Vigili(
matricola int(11) PRIMARY KEY AUTO_INCREMENT,
cognome varchar(45) NOT NULL,
nome varchar(45) NOT NULL
);
Automobile | |||
---|---|---|---|
Prov | Targa | Cognome | Nome |
MI | 39548K | Rossi | Mario |
TO | E39548 | Rossi | Mario |
PR | 839548 | Neri | Luca |
CREATE TABLE Automobile(
prov char(2),
targa char(6),
cognone varchar(45) NOT NULL,
nome varchar(45) NOT NULL,
PRIMARY KEY (prov, targa)
);
Infrazioni | ||||
---|---|---|---|---|
Codice | Data | Vigile | Prov | Targa |
34321 | 1/2/95 | 3987 | MI | 39548K |
53524 | 4/3/95 | 3295 | TO | E39548 |
64521 | 5/4/96 | 3295 | PR | 839548 |
73321 | 5/2/98 | 9345 | PR | 839548 |
CREATE TABLE Infrazioni(
codice int(11) PRIMARY KEY AUTO_INCREMENT,
data datetime,
vigile int(11),
prov char(2),
targa char(6),
FOREIGN KEY (vigile) REFERENCES Vigili(matricola),
FOREIGN KEY (prov, targa)
REFERENCES Automobile(prov, targa)
);
CREATE TABLE Infrazioni(
codice int(11) PRIMARY KEY AUTO_INCREMENT,
data datetime NOT NULL,
vigile int(11),
prov char(2) NOT NULL,
targa char(6) NOT NULL,
FOREIGN KEY (vigile) REFERENCES Vigili(matricola)
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (prov, targa) REFERENCES
Automobile(prov, targa)
);
Infrazioni | |||||
---|---|---|---|---|---|
Codice | Data | dataModifica | Vigile | Prov | Targa |
34321 | 1/2/95 | 1/2/95 | 3987 | MI | 39548K |
53524 | 4/3/95 | 16/4/91 | 3295 | TO | E39548 |
64521 | 5/4/96 | 11/2/84 | 3295 | PR | 839548 |
73321 | 5/2/98 | 31/12/98 | 9345 | PR | 839548 |
DROP
tabellaALTER TABLE nomeTabella
azione1
[, azione2, ...]
ALTER TABLE nomeTabella
ADD COLUMN definizioneColonna
[ FIRST | AFTER nomeColonna ]
ALTER TABLE Infrazioni
ADD COLUMN dataModifica TIMESTAMP
AFTER data
ALTER TABLE nomeTabella
DROP COLUMN nomeColonna
ALTER TABLE infrazioni
DROP COLUMN dataModifica
ALTER TABLE nomeTabella
CHANGE COLUMN nomeOriginale
nomeNuovo tipo
ALTER TABLE infrazioni
CHANGE COLUMN dataModifica
dataUltimaModifica TIMESTAMP
ALTER TABLE automobili
CHANGE COLUMN cognome
VARCHAR(100)
ALTER TABLE nomeTabella
RENAME TO nuovoNome
ALTER TABLE infrazioni
RENAME TO odiateInfrazioni
ALTER TABLE nomeTabella
ADD CONSTRAINT nome
FOREIGN KEY (...)
REFERENCES tabella(...)
ALTER TABLE nomeTabella
DROP FOREIGN KEY nome
https://www.mysqltutorial.org/getting-started-with-mysql/mysql-sample-database-aspx/
SELECT * FROM products;
SELECT attributo1 [, attributo2, ...]
FROM tabella1 [, tabella2, ...]
[WHERE condizione]
*
= tutti gli attributiFROM
= da dove (per ora)WHERE
= quali ennupleWHERE
SELECT * FROM products WHERE MSRP < 75;
SELECT productName, buyPrice, MSRP
FROM products
WHERE MSRP < 75;
AS
SELECT productName AS nomeProdotto,
productVendor AS nomeVenditore
FROM products;
SELECT productName, buyPrice, MSRP
FROM products
WHERE MSRP < 75;
in realtà stiamo scrivendo
SELECT p.productName, p.buyPrice, p.MSRP
FROM products p
WHERE p.MSRP < 75;
SELECT * FROM products;
in realtà stiamo scrivendo
SELECT productCode, productName, productLine,
productScale, productVendor,
productDescription, quantityInStock,
buyPrice, MSRP
FROM products;
SELECT * FROM products;
in realtà stiamo scrivendo
SELECT productCode, productName, productLine,
productScale, productVendor,
productDescription, quantityInStock,
buyPrice, MSRP
FROM products
WHERE true;
SELECT * FROM employees
WHERE firstName = 'Leslie';
"Leslie"
o 'Leslie'
?
'Leslie'
Ci'ao
" "
: "Ci'ao"
' '
: raddoppio → 'Ci''ao'
Ci"'"ao
" "
: raddoppio → "Ci""ao"
' '
: 'Ci"ao'
SELECT * FROM employees
WHERE lastName LIKE '%son';
SELECT * FROM employees
WHERE lastName NOT LIKE '%son';
%
= zero o più caratteri_
= esattamente un carattere%
uso \%
_
uso \_
SELECT * FROM employees
WHERE firstName LIKE '_arry';
SELECT * FROM products
WHERE productScale LIKE '1:_0';
SELECT * FROM employees
WHERE firstName LIKE 'M_r%';
SELECT productName, MSRP, buyPrice
FROM products
WHERE MSRP < 75 AND buyPrice > 30;
SELECT productName, MSRP
FROM products
WHERE MSRP < 75 OR MSRP > 150;
SELECT productName, MSRP, buyPrice
FROM products
WHERE (MSRP<75 OR MSRP>150) AND buyPrice>30;
SELECT ... FROM ...
WHERE colonna BETWEEN x AND y;
SELECT * FROM payments
WHERE amount BETWEEN 5000 AND 8000;
SELECT * FROM payments
WHERE amount BETWEEN 5000 AND 8000;
forma equivalente
SELECT * FROM payments
WHERE amount >= 5000 AND
amount <= 8000;
SELECT * FROM employees
WHERE firstName BETWEEN 'B' AND 'F';
SELECT ... FROM ...
WHERE colonna IN (val1, val2, ...)
SELECT officeCode, city, phone
FROM offices
WHERE country IN ('USA', 'FRANCE');
SELECT officeCode, city, phone
FROM offices
WHERE country IN ('USA', 'FRANCE');
forma equivalente
SELECT officeCode, city, phone
FROM offices
WHERE country = 'USA' OR
country = 'FRANCE';
SELECT * FROM products
WHERE productLine
IN ('Planes','Ships','Classic Cars');
SELECT ... FROM ...
WHERE colonna = "";
SELECT ... FROM ...
WHERE colonna IS NULL;
SELECT * FROM orders
WHERE shippedDate IS NULL;
SELECT * FROM orders WHERE
orderDate > '2005-04-30' AND
shippedDate IS NULL;
SELECT productName, MSRP/1.22 AS noIVA
FROM products;
SELECT productName, MSRP, buyPrice
FROM products
WHERE MSRP-buyPrice > 50;
length()
reverse()
right()
trim()
SELECT productName, length(productName)
FROM products
WHERE length(productName) >= 15;
day()
year()
now()
month()
monthname()
SELECT * from orders
WHERE month(orderDate) = 1;
SELECT ... FROM ... WHERE ...
ORDER BY col1 [ASC|DESC], col2 [ASC|DESC], ...
ASC
: crescente → DEFAULT!DESC
: decrescenteSELECT productName, MSRP
FROM products
ORDER BY MSRP;
SELECT customerName, country, creditLimit
FROM customers
ORDER BY country, creditLimit DESC;
SELECT * FROM orders
order by status;
FIELD(text, str1, str2, str3, ...)
Ritorna la posizione della stringa text
nella lista str1
, str2
, str3
, …
SELECT * FROM orders
ORDER BY FIELD(status, 'In Process',
'On Hold', 'Cancelled', 'Resolved',
'Disputed','Shipped');
SELECT productName,
MSRP-buyPrice as margine
FROM products
WHERE MSRP < 100
ORDER BY msrp-buyPrice DESC
duplicate: come facciamo ad eliminare i doppioni?
SELECT DISTINCT ... FROM ...
SELECT DISTINCT city FROM customers
order by city;
SELECT ... FROM tabella1, tabella2, ...
Risultato: una riga per ogni combinazione di valori tra le righe di tabella1 e di tabella2
impiegati e quella dei clienti
SELECT * FROM customers, employees;
impiegati e quella dei clienti
SELECT * FROM customers, employees;
Forma esplicita:
SELECT * FROM customers CROSS JOIN employees;
SELECT ... FROM tabella1, tabella2, ...
WHERE condizione sui valori comuni (PK e FK)
Creo una riga per ogni combinazione di valori tra le righe della tabella1 e della tabella2, ma poi salvo solo quelle sensate
SELECT customerName, salesRepEmployeeNumber,
lastName, employeeNumber
FROM customers, employees
WHERE salesRepEmployeeNumber = employeeNumber
SELECT ... FROM tabella1
INNER JOIN tabella2
ON PK = FK
SELECT customerName, salesRepEmployeeNumber,
lastName, employeeNumber
FROM customers
INNER JOIN employees
ON salesRepEmployeeNumber = employeeNumber;
SELECT ... FROM tabella1
INNER JOIN tabella2
ON tabella2.PK = tabella1.FK
SELECT productCode, productName, textDescription
FROM products INNER JOIN productlines
ON products.productline =
productlines.productline;
SELECT productCode, productName, textDescription
FROM products INNER JOIN productlines
ON products.productline = productlines.productline;
SELECT productCode, productName, textDescription
FROM products p1
INNER JOIN productlines p2
ON p1.productline = p2.productline;
SELECT productCode, productName, textDescription
FROM products INNER JOIN productlines
ON products.productline =
productlines.productline;
SELECT productCode, productName,
textDescription
FROM products INNER JOIN productlines
USING(productline);
SELECT firstName, lastName, city
FROM employees INNER JOIN offices
USING (officeCode);
Se gli unici nomi di attributi in comune tra due tabelle sono quelli di FK/PK, si può usare una forma ANCORA più abbreviata
SELECT ... FROM tabella1
NATURAL JOIN tabella2
Cosa succede se aggiungo/cambio colonne?
SELECT productCode, productName,
textDescription
FROM products NATURAL JOIN productlines;
SELECT ... FROM tabella1
LEFT OUTER JOIN tabella2
ON PK = FK
SELECT customerName, concat(firstName,' ',lastName)
FROM customers LEFT OUTER JOIN employees
ON salesRepEmployeeNumber = employeeNumber
SELECT customerName, concat(firstName,' ',lastName)
FROM customers LEFT OUTER JOIN employees
ON salesRepEmployeeNumber = employeeNumber
forma equivalente
SELECT customerName, concat(firstName,' ',lastName)
FROM customers LEFT JOIN employees
ON salesRepEmployeeNumber = employeeNumber
Inner
Left Outer
SELECT c.customerNumber, c.customerName,
o.orderNumber, o.status
FROM customers c LEFT JOIN orders o
ON c.customerNumber = o.customerNumber;
SELECT c.customerNumber, c.customerName,
orderNumber, o.status
FROM customers c LEFT JOIN orders o
ON c.customerNumber = o.customerNumber
WHERE orderNumber is NULL
SELECT ... FROM tabella1
RIGHT OUTER JOIN tabella2
ON PK = FK
Inner
Left Outer
Right Outer
SELECT c.customerNumber, c.customerName,
orderNumber, o.status
FROM orders o RIGHT JOIN customers c
ON c.customerNumber = o.customerNumber
SELECT ... FROM tabella1
[INNER|LEFT|RIGHT]JOIN tabella2
ON PK = FK
[INNER|LEFT|RIGHT]JOIN tabella3
ON PK = FK
SELECT c.customerName, e.firstName, o.phone
FROM customers c
LEFT JOIN employees e
ON c.salesRepEmployeeNumber = e.employeeNumber
LEFT JOIN offices o
USING (officeCode)
SELECT c.customerName, o.orderNumber, p.productName
FROM orderdetails d
INNER JOIN orders o
USING (orderNumber)
INNER JOIN customers c
USING (customerNumber)
INNER JOIN products p
USING (productCode)
ORDER BY o.orderNumber, d.orderLineNumber;
Inner + Inner
Inner + Left Outer
ID | Nome | Prezzo | Linea |
---|---|---|---|
1 | Audi A5 | 50,00 | A |
2 | Mercedes C | 45,00 | A |
3 | Smart | 25,00 | B |
ID | Nome | Link |
---|---|---|
A | Auto Sportive | ... |
B | Micro Auto | ... |
ID | Nome | Prezzo | Linea | ID | Nome | Link |
---|---|---|---|---|---|---|
1 | Audi A5 | 50,00 | A | A | Auto Sportive | ... |
1 | Audi A5 | 50,00 | A | B | Micro Auto | ... |
2 | Mercedes C | 45,00 | A | A | Auto Sportive | ... |
2 | Mercedes C | 45,00 | A | B | Micro Auto | ... |
3 | Smart | 25,00 | B | A | Auto Sportive | ... |
3 | Smart | 25,00 | B | B | Micro Auto | ... |
ID | Nome | Prezzo | Linea |
---|---|---|---|
1 | Audi A5 | 50,00 | A |
2 | Mercedes C | 45,00 | A |
3 | Smart | 25,00 | B |
ID | Nome | Link |
---|---|---|
A | Auto Sportive | ... |
B | Micro Auto | ... |
ID | Nome | Prezzo | Linea | ID | Nome | Link |
---|---|---|---|---|---|---|
1 | Audi A5 | 50,00 | A | A | Auto Sportive | ... |
2 | Mercedes C | 45,00 | A | A | Auto Sportive | ... |
3 | Smart | 25,00 | B | B | Micro Auto | ... |
ID | Nome | Prezzo | Linea |
---|---|---|---|
1 | Audi A5 | 50,00 | A |
2 | Mercedes C | 45,00 | NULL |
3 | Smart | 25,00 | B |
ID | Nome | Link |
---|---|---|
A | Auto Sportive | ... |
B | Micro Auto | ... |
ID | Nome | Prezzo | Linea | ID | Nome | Link |
---|---|---|---|---|---|---|
1 | Audi A5 | 50,00 | A | A | Auto Sportive | ... |
3 | Smart | 25,00 | B | B | Micro Auto | ... |
ID | Nome | Prezzo | Linea |
---|---|---|---|
1 | Audi A5 | 50,00 | A |
2 | Mercedes C | 45,00 | NULL |
3 | Smart | 25,00 | B |
ID | Nome | Link |
---|---|---|
A | Auto Sportive | ... |
B | Micro Auto | ... |
ID | Nome | Prezzo | Linea | ID | Nome | Link |
---|---|---|---|---|---|---|
1 | Audi A5 | 50,00 | A | A | Auto Sportive | ... |
2 | Mercedes C | 45,00 | NULL | NULL | NULL | NULL |
3 | Smart | 25,00 | B | B | Micro Auto | ... |
ID | Nome | Colore | Linea |
---|---|---|---|
1 | Audi A5 | 1 | A |
2 | Mercedes C | 2 | A |
3 | Smart | 2 | B |
ID | Nome | Link |
---|---|---|
A | Auto Sportive | ... |
B | Micro Auto | ... |
ID | Colore |
---|---|
1 | Rosso |
2 | Blu |
ID | Nome | Prezzo | Linea | ID | Nome | Link | ||
---|---|---|---|---|---|---|---|---|
1 | Audi A5 | 1 | A | A | Auto Sportive | ... | 1 | Rosso |
2 | Mercedes C | 2 | A | A | Auto Sportive | ... | 2 | Blu |
3 | Smart | 2 | B | B | Micro Auto | ... | 2 | Blu |
SELECT ... FROM tabella1
FULL OUTER JOIN tabella2
ON PK = FK
ID | Veicolo | Colore | Persona |
---|---|---|---|
1 | Automobile | 1 | 4 |
2 | Bici | 2 | NULL |
3 | Moto | NULL | 1 |
4 | Scooter | 3 | 3 |
ID | Cognome |
---|---|
1 | Rossi |
2 | Bianchi |
3 | Bassi |
4 | Scaini |
ID | Colore |
---|---|
1 | Verde |
2 | Giallo |
3 | Blu |
Persone che possiedono veicoli colorati
SELECT v.veicolo, c.colore, p.cognome
FROM veicolo v
INNER JOIN persona p ON v.id = p.id
INNER JOIN colore c ON v.colore = c.id ;
Veicolo | Colore | Cognome |
---|---|---|
Automobile | Verde | Scaini |
Scooter | Blu | Bassi |
ID | Veicolo | Colore | Persona |
---|---|---|---|
1 | Automobile | 1 | 4 |
2 | Bici | 2 | NULL |
3 | Moto | NULL | 1 |
4 | Scooter | 3 | 3 |
ID | Cognome |
---|---|
1 | Rossi |
2 | Bianchi |
3 | Bassi |
4 | Scaini |
ID | Colore |
---|---|
1 | Verde |
2 | Giallo |
3 | Blu |
Persone che possiedono veicoli colorati o nessun veicolo
SELECT v.veicolo, c.colore, p.cognome
FROM persona p
LEFT JOIN veicolo ON v.id = p.id
INNER JOIN colore c ON v.colore = c.id ;
Veicolo | Colore | Cognome |
---|---|---|
Automobile | Verde | Scaini |
Scooter | Blu | Bassi |
ID | Veicolo | Colore | Persona |
---|---|---|---|
1 | Automobile | 1 | 4 |
2 | Bici | 2 | NULL |
3 | Moto | NULL | 1 |
4 | Scooter | 3 | 3 |
ID | Cognome |
---|---|
1 | Rossi |
2 | Bianchi |
3 | Bassi |
4 | Scaini |
ID | Colore |
---|---|
1 | Verde |
2 | Giallo |
3 | Blu |
Persone che possiedono veicoli colorati o nessun veicolo
SELECT v.veicolo, c.colore, p.cognome
FROM veicolo v
INNER JOIN colore c ON v.colore = c.id
RIGHT JOIN persona p ON v.id = p.id;
Veicolo | Colore | Cognome |
---|---|---|
Automobile | Verde | Scaini |
Scooter | Blu | Bassi |
Rossi | NULL | NULL |
Bianchi | NULL | NULL |
SELECT ... FROM tabella1
[LEFT|RIGHT|INNER] JOIN tabella1
ON PK = FK
employeeNumber | firstName | lastName | reportsTo |
---|---|---|---|
1002 | Diane | Murphy | NULL |
1056 | Mary | Patterson | 1002 |
1076 | Jeff | Firrelli | 1056 |
1088 | William | Patterson | 1056 |
SELECT m.employeeNumber, m.firstName,
m.lastName, m.reportsTo, c.firstName,
c.lastName FROM employees m
LEFT JOIN employees c
ON m.reportsTo = c.employeeNumber;
SELECT c1.city, c1.customerName,
c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city;
SELECT c1.city, c1.customerName,c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city AND
c1.customername <> c2.customerName
SELECT c1.city, c1.customerName,c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city AND
c1.customername <> c2.customerName
forma equivalente
SELECT c1.city, c1.customerName,c2.customerName
FROM customers c1 INNER JOIN customers c2
ON c1.city = c2.city
WHERE c1.customername <> c2.customerName
SELECT ... FROM ...
UNION [DISTINCT | ALL]
SELECT ... FROM ...
[UNION [DISTINCT | ALL]
SELECT ... FROM ...]
SELECT ... FROM ...
UNION [DISTINCT | ALL]
SELECT ... FROM ...
[UNION [DISTINCT | ALL]
SELECT ... FROM ...]
DISTINCT
: default, elimina duplicatiALL
: se specificato, NON elimina duplicatiSELECT customerNumber AS id,contactLastname AS name
FROM customers
UNION
SELECT employeeNumber AS id, firstname AS name
FROM employees;
SELECT customerNumber,contactLastname
FROM customers
UNION
SELECT employeeNumber, firstname
FROM employees;
SELECT customerNumber AS id,
contactLastname AS name, "Cliente" AS tipo
FROM customers
UNION
SELECT employeeNumber AS id,
concat(firstname," ",lastname) AS name,
"Impiegato" AS tipo
FROM employees;
SELECT ... FROM ...
UNION [DISTINCT | ALL]
SELECT ... FROM ...
ORDER BY criteri
(SELECT ... FROM ...)
UNION [DISTINCT | ALL]
(SELECT ... FROM ...)
ORDER BY criteri
(SELECT customerNumber AS id,
contactLastname AS name
FROM customers)
UNION
(SELECT employeeNumber AS id, firstname AS name
FROM employees)
ORDER BY name;
SELECT country FROM offices
UNION
SELECT country FROM customers
ORDER BY country;
SELECT ... FROM ...
INTERSECT
SELECT ... FROM ...
INTERSECT
SELECT ... FROM ...
Non c’è in MySql, servono query nidificate
SELECT a1 , a2 ,...,an
FROM tabella1 WHERE condizioni
GROUP BY a1, a2,...,an
SELECT status
FROM orders
GROUP BY status
SELECT status
FROM orders
WHERE orderDate < "2003-12-31"
GROUP BY status;
SELECT status
FROM orders
WHERE orderDate < "2003-12-31"
GROUP BY status;
forma equivalente
SELECT DISTINCT status
FROM orders
WHERE orderDate < "2003-12-31"
SELECT a1, a2, ..., an, aggregatore(ax)
FROM tabella1 WHERE condizioni
Esempio:
SELECT a1, a2, ..., an, aggregatore(ax)
FROM tabella1 WHERE condizioni
Aggregatori:
COUNT
: conta il numero di valori presentiSUM
: somma dei valoriAVG
: media dei valoriMAX
/MIN
: massimo e minimoSELECT count(*)
FROM employees;
Differenza fra
SELECT count(*)
FROM employees
e
SELECT count(reportsTo)
FROM employees
SELECT count( distinct reportsTo)
FROM employees;
SELECT count(*)
FROM payments;
SELECT SUM(amount)
FROM payments;
SELECT avg(MSRP)
FROM products;
SELECT avg(MSRP), max(MSRP), min(MSRP)
FROM products;
SELECT avg(MSRP), productName
FROM products
SELECT a1, a2 , ... , an, aggregatore(ax)
FROM tabella1 WHERE condizioni
GROUP BY a1, a2, ... ,an
SELECT status, count(*)
FROM orders
GROUP BY status;
SELECT productLine, count(*), avg(MSRP)
FROM products
GROUP BY productLine;
SELECT count(*), shippedDate
FROM orders
GROUP BY shippedDate
date_part('month', attributo)
SELECT count(*), month(shippedDate)
FROM orders
WHERE shippedDate IS NOT NULL
GROUP BY month(shippedDate);
date_part('month', attributo)
date_part('year', attributo)
SELECT count(*), month(shippedDate),
year(shippedDate)
FROM orders
WHERE shippedDate IS NOT NULL
GROUP BY year(shippedDate), month(shippedDate);
Un passo alla volta:
SELECT orderNumber,
sum(quantityOrdered*priceEach)
FROM orderdetails
GROUP BY orderNumber;
SELECT customerName, orderDate,
sum(quantityOrdered*priceEach)
FROM orderdetails
INNER JOIN orders USING (orderNumber)
INNER JOIN customers USING (customerNumber)
GROUP BY orderNumber;
SELECT count(*) nOrdini, customerNumber
FROM orders
GROUP BY customerNumber
ORDER BY nOrdini DESC;
Un passo alla volta:
SELECT amount*-1, paymentDate FROM payments
WHERE customerNumber = 124
SELECT sum(quantityOrdered*priceEach),orderDate
FROM orderdetails INNER JOIN orders
USING (orderNumber)
WHERE customerNumber = 124
GROUP BY orderNumber;
SELECT amount*-1, paymentDate FROM payments
WHERE customerNumber = 124
UNION
SELECT sum(quantityOrdered*priceEach),orderDate
FROM orderdetails INNER JOIN orders
USING (orderNumber)
WHERE customerNumber = 124
GROUP BY orderNumber
ORDER BY paymentDate;
SELECT a1, a2, ... ,an, aggregatore(ax)
FROM tabella1 WHERE condizioni
GROUP BY a1, a2, ... ,an
HAVING condizioniAggregate
SELECT orderNumber,
sum(quantityOrdered*priceEach) as tot
FROM orderdetails
GROUP BY orderNumber
HAVING tot < 10000;
SELECT orderNumber,
sum(quantityOrdered) as q,
sum(quantityOrdered*priceEach) as tot
FROM orderdetails
GROUP BY orderNumber
HAVING tot < 10000 AND q > 100;
Hint: ordini spediti hanno status "Shipped"
SELECT ordernumber, status,
SUM(quantityOrdered*priceeach) total
FROM orderdetails
INNER JOIN orders USING(ordernumber)
GROUP BY ordernumber
HAVING status <> 'Shipped' AND total < 10000;
SELECT ordernumber, status,
SUM(quantityOrdered*priceeach) total
FROM orderdetails
INNER JOIN orders USING(ordernumber)
GROUP BY ordernumber
HAVING status <> 'Shipped' AND total < 10000;
forma equivalente
SELECT ordernumber, status,
SUM(quantityOrdered*priceeach) total
FROM orderdetails
INNER JOIN orders USING(ordernumber)
WHERE status <> 'Shipped'
GROUP BY ordernumber
HAVING total < 10000;
SELECT a1,a2,...,an,(QUERY singolo val.)
FROM (QUERY)
WHERE a1 > (QUERY singolo val.)
AND a2 IN (QUERY singolo attrib.)
Per adesso:
Hint: subquery nella clausola SELECT
SELECT productName, MSRP,
(SELECT max(MSRP)
FROM products) as massimo
FROM products;
Hint: subquery nella clausola WHERE
SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount =
(SELECT MAX(amount)
FROM payments);
SELECT customerNumber, checkNumber, amount
FROM payments
WHERE amount >
(SELECT AVG(amount)
FROM payments);
Hint: subquery nella clausola WHERE ... NOT IN
SELECT customername
FROM customers
WHERE customerNumber NOT IN
(SELECT DISTINCT customernumber
FROM orders);
SELECT customername
FROM customers
WHERE customerNumber NOT IN
(SELECT DISTINCT customernumber
FROM orders);
forma equivalente
SELECT customername
FROM customers
LEFT JOIN orders USING (customerNumber)
WHERE orderNumber IS NULL;
Hint: creare prima la query che somma le righe dell’ordine
SELECT max(items), min(items),
floor(avg(items)) as media
FROM (SELECT orderNumber,
SUM(quantityOrdered) AS items
FROM orderdetails
GROUP BY orderNumber) AS lineitems;
Finora:
SELECT a1,a2,...,an
FROM tab1 WHERE
a1 > (SELECT c1
FROM tab2
WHERE tab2.c2 > tab1.a1)
SELECT productname, buyprice
FROM products AS p
WHERE buyprice > (
SELECT AVG(buyprice)
FROM products
WHERE productline = p.productline);
SELECT a1,a2,...,an
FROM tab
WHERE EXISTS (QUERY singolo val.)
SELECT a1,a2,...,an
FROM tab
WHERE ...
LIMIT numero
SELECT customernumber,
customername,
creditlimit
FROM customers
LIMIT 5;
SELECT customernumber, customername,
creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;
SELECT a1,a2,...,an
FROM tab
WHERE ...
LIMIT X, N
SELECT productName, buyprice
FROM products
ORDER BY buyprice DESC
LIMIT 1, 1;
INSERT INTO tabella(col1, col2, ...)
VALUES (valore1, valore2, ...)
[, (valore1, valore2, ...), ...]
AUTO_INCREMENT: NULL
Hint: INSERT INTO tabella (col1,col2,...) VALUES (valore1,valore2,...)
INSERT INTO offices
VALUES (8, 'Trieste', '+30 040558555',
'Via Valerio 10', null, null,
'Italy', '34100', 'EMEA')
INSERT INTO tabella(col1, col2, ...)
SELECT ...
Il risultato della SELECT
deve fornire:
creare l’ordine 10426 (a mano, tabella orders)
Hint: INSERT INTO tabella (col1,col2,...) VALUES (valore1,valore2,...)
INSERT INTO orders
VALUES (10426, '2014-11-11',
'2014-11-30', null,
'In Process',
'Duplica 10425', 119);
prendere tutte le righe di 10425 (orderdetails)
Hint: conta l'ordine delle colonne, vi servirà FK 10426
SELECT 10426 AS numero, productCode,
quantityordered, priceEach,
orderLineNumber
FROM orderdetails
WHERE orderNumber = 10425;
inserire tutte queste $n$-uple nella tabella (orderdetails)
Hint: INSERT INTO tabella (col1,col2,...) SELECT ...
INSERT INTO orderdetails
SELECT 10426 AS numero, productCode,
quantityordered, priceEach,
orderLineNumber
FROM orderdetails
WHERE orderNumber=10425;
UPDATE tabella
SET col1 = valore1
[, col2 = val2...]
[WHERE condizione]
WHERE
, aggiorno tutte le $n$-uple della tabellaImpiegato 1056, mettete una email a scelta
Hint: UPDATE tabella SET col1 = valore1 WHERE condizione
UPDATE employees
SET email = 'mary.patterson@classicmodelcars.com'
WHERE employeeNumber = 1056;
Hint: UPDATE tabella SET col1 = valore1 WHERE condizione
UPDATE products
SET msrp = 500, buyprice = 200
WHERE productName = '2001 Ferrari Enzo';
Hint: UPDATE tabella SET col1 = valore1 WHERE condizione
UPDATE products
SET msrp = msrp*1.05;
Individuare i clienti senza venditore
SELECT customerNumber
FROM customers
WHERE salesRepEmployeeNumber IS NULL;
Trovare l'agente con matricola più alta
Hint: venditore ha `jobTitle = 'Sales Rep'`
SELECT max(employeeNumber)
FROM employees
WHERE jobTitle = 'Sales Rep';
Aggiornare i dati
UPDATE customers
SET salesRepEmployeeNumber =
(SELECT max(employeeNumber)
FROM employees
WHERE jobTitle = 'Sales Rep')
WHERE salesRepEmployeeNumber IS NULL;
UPDATE tabella
SET col1 = col1 +1, col2 = col1
col1
col1
DELETE FROM tabella
[WHERE condizioni]
oppure
DELETE FROM tabella
[WHERE condizioni]
SELECT *
per vedere che succedeHint: DELETE FROM tab WHERE ...
DELETE FROM customers
WHERE country = "Italy";
Opzioni di MySql WorkBench:
Error Code: 1175. You are using safe update
mode and you tried to update a table without
a WHERE that uses a KEY column To disable
safe mode, toggle the option in Preferences ->
SQL Queries and reconnect.
Vincoli interrelazionali:
Error executing SQL statement.
ERROR: update or delete on table "customers" violates foreign key constraint "orders_ibfk_1" on table "orders"
Dettaglio: Key (customernumber)=(249) is still referenced from table "orders". - Connection: PostgreSQL: 2ms
Devo prima modificare le altre tabelle!
CREATE TABLE nomeTab(
attr1 tipo1 CHECK (condizione),
attr2 tipo2,...,
CHECK (condizione))
stipendio inferiore a quello del capo
CREATE TABLE Impiegato(
matricola integer,
cognome character(20),
sesso character NOT NULL
CHECK (sesso in (‘M’,‘F’)),
stipendio integer, superiore integer,
CHECK (stipendio <=
(SELECT stipendio
FROM Impiegato J
WHERE superiore = J.matricola))
CREATE ASSERTION nome CHECK (condizione)
Stesse note del check
CREATE ASSERTION AlmenoUnImpiegato
CHECK ((SELECT count(*) FROM Impiegato) >= 1)
SET @variabile = valore;
SELECT @variabile;
SET @pippo = 'Hello, World!';
SELECT @pippo;
SET @prezzo = (SELECT max(msrp)
FROM products);
SELECT @prezzo;
SELET * FROM products
WHERE MSRP = @prezzo;
Cosa accade quando invio una query al server?
E se devo eseguire spesso la stessa query??
SET profiling = 1;
esecuzione comandi
SHOW PROFILES;
SHOW PROFILE [FOR QUERY n];
SET profiling = 0;
SHOW PROFILES
: storico dei tempi di esecuzioneSHOW PROFILE
: come ho impiegato il tempo nell’ultima query/query specificata?Tipo
ALL
: tutte le informazioniCPU
: tempo CPU per user/systemSWAPS
: utilizzo della memoria su discoSOURCE
: nome della funzione/libreria usatiSHOW PROFILE ALL FOR QUERY 4;
PREPARE nomeStatement FROM 'query';
EXECUTE nomeStatement USING p1, p2,...;
DEALLOCATE PREPARE nomeStatement;
PREPARE
: crea una query riutilizzabile, che può ricevere parametriEXECUTE
: esegue il comando salvatoDEALLOCATE PREPARE
: elimina il comandoPREPARE nomeStatement FROM
'SELECT a1,a2,...
FROM tabella
WHERE a1 = ? AND a2 = ?';
PREPARE nomeStatement(type1, type2, ...) AS
SELECT a1,a2,...
FROM tabella
WHERE a1 = $1 AND a2 = $2;
$
Hint: DPREPARE nomeStatement FROM 'SELECT a1,a2,... FROM tabella WHERE a1 = ? AND a2 = ?';
PREPARE stmt1 FROM
'SELECT productCode, productName
FROM products WHERE MSRP > ?';
EXECUTE nomeStatement
[USING @var1, @var2,...];
EXECUTE nomeStatement(arg1, arg2, ...);
Hint: EXECUTE nomeStatement
[USING @var1, @var2,...];
SET @MSRP = 100;
EXECUTE stmt1 USING @MSRP;
DEALLOCATE PREPARE nomeStatement;
DROP PREPARE nomeStatement;
PREPARE stmtListinoClienti FROM
'SELECT productCode, productName, MSRP FROM products';
Problemi:
MERGE (predefinito)
TEMPTABLE (materialized)
CREATE VIEW nomeVista AS
SELECT ...
Dettagli:
CREATE VIEW viewListinoClienti AS
SELECT productCode, productName, MSRP
FROM products;
SELECT * from viewListinoClienti;
Hint: CREATE VIEW nomeVista AS
SELECT ...
CREATE VIEW viewTotaleOrdini AS
SELECT orderNumber,
SUM(quantityOrdered * priceEach) total
FROM orderdetails
GROUP by orderNumber
SELECT total
FROM viewTotaleOrdini
WHERE orderNumber = 10102;
SHOW CREATE VIEW nomeVista;
DROP VIEW nomeVista;
ALTER VIEW nomeVista AS nuovaSELECT;
Posso modificare i dati di una vista se la SELECT:
CREATE VIEW officeInfo
AS SELECT officeCode, phone, city
FROM offices;
UPDATE officeInfo
SET phone = '+39 040 55558555'
WHERE officeCode = 4;
MySQL:
CREATE USER nome@host
IDENTIFIED BY 'password'
Oracle:
CREATE USER nome
IDENTIFIED BY password
SQL Server
CREATE USER nome
WITH PASSWORD = 'password'
'%'
per ogni host'nome@host'
crea un utente con username nome@host
legato all’host %
FLUSH PRIVILEGES
forza il reload dei datiCREATE USER pippo@localhost
IDENTIFIED BY 'pluto';
mysql
Tabella user
INSERT INTO user(host,user,password)
VALUES('localhost','pippo',
PASSWORD('pluto'));
FLUSH PRIVILEGES;
SET PASSWORD FOR user@host =
PASSWORD('Secret1970');
DROP USER user@host;
GRANT privilegio (colonne)
ON risorsa
TO account
[WITH GRANT OPTION]
privilegio
: tipo di operazione permessacolonne
: se si applica solo ad alcune colonnerisorsa
: database.tabella — wildcard: *account
: utente@hostWITH GRANT OPTION
: l’utente può propagare i permessi ad altriALL
: tuttiALTER
: modificare tabellaCREATE
: creare oggettiDELETE
: eliminare ennupleSELECT
: leggere i datiUPDATE
: modificare i datiHint: GRANT privilegio (colonne)
ON risorsa
TO account
GRANT SELECT, UPDATE, DELETE ON
classicmodels.* TO 'pippo'@'%';
GRANT ALL ON *.* TO 'super'@'localhost'
WITH GRANT OPTION;
GRANT SELECT (phone, customerName),
UPDATE (phone)
ON classicmodels.customers
TO 'someuser'@'somehost';
SHOW GRANTS FOR utente;
Sintassi molto simile a GRANT
REVOKE privilege_type [(column_list)]
[, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...
REVOKE UPDATE, DELETE ON
classicmodels.* FROM
'rfc'@'localhost';
Esempio: trasferimento di fondi da un conto A ad un conto B: o si fanno il prelevamento da A e il versamento su B o nessuno dei due
Esempio: se due assegni emessi sullo stesso conto corrente vengono incassati contemporaneamente si deve evitare di trascurarne uno
In MySQL:
START TRANSACTION
: specifica l’inizio della transazione (le operazioni non vengono eseguite sulla base di dati)COMMIT
: le operazioni specificate a partire dal begin transaction vengono eseguiteROLLBACK
: si rinuncia all’esecuzione delle operazioni specificate dopo l’ultimo begin transactionSQL Server
BEGIN TRANSACTION
COMMIT WORK
ROLLBACK WORK
start transaction;
select @orderNumber := max(orderNUmber) from orders;
set @orderNumber = @orderNumber + 1;
insert into orders(orderNumber, orderDate, requiredDate, shippedDate, status, customerNumber)
values(@orderNumber, now(), date_add(now(), INTERVAL 5 DAY),
date_add(now(), INTERVAL 2 DAY), 'In Process', 145);
insert into orderdetails(orderNumber, productCode, quantityOrdered, priceEach, orderLineNumber)
values(@orderNumber,'S18_1749', 30, '136', 1),
(@orderNumber,'S18_2248', 50, '55.09', 2);
commit;
Qualcosa è sempre in memoria e può sempre essere perso
È possibile racchiudere comandi SQL in oggetti programmabili la cui definizione rimane nelle tabelle di sistema:
insieme di comandi SQL con parametri di input e output che possono restituire recordset
particolari stored procedure che vengono associate ad una operazione su un oggetto e invocate automaticamente
consentono di raggruppare e riutilizzare codice SQL solitamente ripetuto all’interno di SP e trigger
semplificando: stored procedure scritte in linguaggi Microsoft
Anche qui dipende dal motore…
CREATE PROCEDURE nome()
BEGIN
... codice
END
MS SQL Server:
CREATE PROCEDURE nome
AS [BEGIN]
... codice
[END]
CREATE PROCEDURE sp_getAllProducts()
BEGIN
SELECT * FROM products;
END
Problema: potrei avere più istruzioni, e non voglio che il DBMS le interpreti una alla volta (ogni volta che trova un “;”)
MySql: cambio il delimitatore
DELIMITER $$
CREATE PROCEDURE nome()
BEGIN
... codice1;
... codice2;
END $$
DELIMITER ;
SQL Server: GO
!
CREATE PROCEDURE nome
AS [BEGIN]
... codice1;
... codice2;
[END]
GO
MySql
CALL nomeStoredProcedure()
SQL Server
EXEC nomeStoredProcedure
PostgreSQL
SELECT nomeStoredProcedure()
Oracle
EXECUTE nomeStoredProcedure()
Hint: CREATE PROCEDURE nome()
BEGIN
... codice
END
DELIMITER $$
CREATE PROCEDURE sp_getSalesRep()
BEGIN
SELECT * FROM employees
WHERE jobTitle = 'Sales Rep';
END
SHOW PROCEDURE STATUS
[WHERE condizioni]
Condizioni:
db
: nome del DBname
: nome della SPLIKE
, OR
, AND
, …SHOW CREATE PROCEDURE spNome
DROP PROCEDURE spNome
MySql
DROP + CREATE
SQL Server
ALTER nomeStoredProcedure
AS
...codice
GO
CREATE PROCEDURE nomeSP(
nomePar1 tipoPar1,
nomePar2 tipoPar2, ...
)
BEGIN
... codice
END
CALL nomeSP(par1, par2,...)
CREATE PROCEDURE sp_getEmployeeByType(
tipoImp varchar(50))
BEGIN
SELECT * FROM employees WHERE jobTitle = tipoImp;
END
IN
OUT
leggibili e scrivibili (bidirezionali) → INOUT
CREATE PROCEDURE nomeSP(
direzione nomePar1 tipoPar1,
direzione nomePar2 tipoPar2,...)
BEGIN ... codice END
CREATE PROCEDURE sp_conta(
INOUT count INT(4),
IN inc INT(4))
BEGIN
SET count = count + inc;
END
SET @counter = 1;
CALL set_counter(@counter,1); -- 2
CALL set_counter(@counter,1); -- 3
CALL set_counter(@counter,5); -- 8
SELECT @counter; -- 8
CREATE PROCEDURE sp_raddoppia(
INOUT valore int(11))
BEGIN
SET valore = valore * 2;
END
set @val = 10;
select @val;
CALL sp_raddoppia(@val);
select @val;
CREATE PROCEDURE sp_contaOggettiInOrdine(
IN oNumber INT,
OUT numberObjects INT)
BEGIN
SET numberObjects = (
SELECT sum(quantityOrdered) FROM
orderdetails WHERE orderNumber = oNumber
);
END
CALL sp_contaOggettiInOrdine(10100, @numObj);
select @numObj; --- 151
CREATE PROCEDURE sp_contaOggettiInOrdine(
IN oNumber INT,
OUT numberObjects INT)
BEGIN
SELECT sum(quantityOrdered)
INTO numberObjects
FROM orderdetails
WHERE orderNumber = oNumber);
END
CREATE PROCEDURE sp_contaOrdini(
IN orderStatus VARCHAR(25),
OUT total INT)
BEGIN
SELECT count(orderNumber)
INTO total FROM orders
WHERE status = orderStatus;
END
CALL sp_contaOrdini('Shipped',@total);
SELECT @total; --- 303
Provate con il cliente 141:
CALL sp_getOrderByCust(141,@shipped,@canceled,
@resolved,@disputed);
SELECT @shipped,@canceled,@resolved,@disputed;
DELIMITER $$
CREATE PROCEDURE get_order_by_cust(IN cust_no INT,
OUT shipped INT, OUT canceled INT,
OUT resolved INT, OUT disputed INT)
BEGIN
-- shipped
SELECT count(*) INTO shipped FROM orders
WHERE customerNumber = cust_no AND status = 'Shipped';
-- canceled
SELECT count(*) INTO canceled FROM orders
WHERE customerNumber = cust_no AND status = 'Canceled';
-- resolved
SELECT count(*) INTO resolved FROM orders
WHERE customerNumber = cust_no AND status = 'Resolved';
-- Disputed
SELECT count(*) INTO disputed FROM orders
WHERE customerNumber = cust_no AND status = 'Disputed';
SET @variabile = 10
DECLARE nomeVariabile tipoVariabile
SET nomeVariabile = valore
IF espressione THEN
comandi
ELSE IF espressione THEN
comandi
ELSE
comandi
END IF;
Provate con il cliente 103:
CALL sp_getCustomerLevel(103, @livello);
SELECT @livello;
DELIMITER $$
CREATE PROCEDURE sp_getCustomerLevel(
IN custNo int(11),
OUT customerLevel varchar(10))
BEGIN
DECLARE creditlim double;
SELECT creditlimit INTO creditlim
FROM customers
WHERE customerNumber = custNo;
IF creditlim > 50000 THEN
SET customerLevel = 'PLATINUM';
ELSEIF creditlim >= 10000 THEN
SET customerLevel = 'GOLD';
ELSE
SET customerLevel = 'SILVER';
END IF;
END;
WHILE espressione DO
comandi
END WHILE;
REPEAT
comandi
UNTIL espressione
END REPEAT;
Dettagli:
LEAVE
: esce dal cicloITERATE
: procede con l’iterazione successivaDELIMITER $$
CREATE PROCEDURE sp_fibonacci(IN n int, OUT out_fib int)
BEGIN
DECLARE m INT default 0;
DECLARE k INT default 1;
DECLARE i INT default 1;
DECLARE tmp INT;
WHILE (i<=n) DO
set tmp = m+k;
set m = k;
set k = tmp;
set i = i+1;
END WHILE;
SET out_fib = m;
END;
DECLARE azione HANDLER FOR
condizione [BEGIN] codice [END]
condizione
: cosa vogliamo intercettarecodice
: cosa fareazione
: come comportarsi dopo aver eseguito il codice
CONTINUE
→ continua con il restoEXIT
→ termina l’esecuzioneDECLARE azione HANDLER FOR
condizione [BEGIN] codice [END]
Condizioni:
DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
ROLLBACK;
SELECT 'Errore: ho annullato tutto!';
END;
SIGNAL SQLSTATE 'codice'
SET MESSAGE_TEXT = 'testo'
Codice definito dall’utente: 45000
Hint: SIGNAL SQLSTATE 'codice'
SET MESSAGE_TEXT = 'testo'
CREATE PROCEDURE sp_countOrders(customerNo int)
BEGIN
DECLARE conteggio INT;
SELECT count(*) INTO conteggio FROM customers
WHERE customerNumber = customerNo;
IF conteggio = 0 THEN
SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Errore generico';
END IF;
SELECT count(*) FROM orders WHERE customerNumber = customerNo;
END
DECLARE nomeCursore CURSOR FOR
SELECT ...
OPEN nomeCursore
FETCH nomeCursore INTO var1, var2, ...
CLOSE nomeCursore
Cursori
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Uso un handler: quando non ho altri dati, si verifica NOT FOUND
Terminerò il ciclo quando finished == 1
Hints:
DECLARE nomeCursore CURSOR FOR SELECT ...
OPEN/CLOSE nomeCursore
FETCH nomeCursore INTO var1,var2,...
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
Risultato: @emails: mgerard@classicmodelcars.com;ykato@classicmo...
CREATE PROCEDURE sp_buildEmailList (INOUT email_list varchar(4000))
BEGIN
DECLARE finished INTEGER DEFAULT 0;
DECLARE v_email varchar(100) DEFAULT "";
DECLARE email_cursor CURSOR FOR SELECT email FROM employees
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;
OPEN email_cursor;
WHILE (finished = 0) DO
FETCH email_cursor INTO v_email;
IF finished = 0 THEN
SET email_list = CONCAT(v_email,";",email_list);
END IF;
END WHILE;
CLOSE email_cursor;
END
CREATE FUNCTION function_name
(param1 tipo1,param2 tipo2,...)
RETURNS tipo
[NOT] DETERMINISTIC
BEGIN
statements
END
Si usa come una funzione normale (select, ecc)
Deterministic
ma non lo è: risultati non corretti (l’execution planner può decidere che non occorre ricalcolare)NON Deterministic
ma lo è: prestazioni peggiori (ricalcolo anche se non serve)Hint: CREATE FUNCTION function_name
(param1 tipo1,param2 tipo2,...)
RETURNS tipo [NOT] DETERMINISTIC
BEGIN
statements
END
CREATE FUNCTION udf_raddoppia (numero int)
RETURNS INT DETERMINISTIC
BEGIN
RETURN numero * 2;
END
CREATE FUNCTION udf_customerLevel
(p_creditLimit double)
RETURNS VARCHAR(10) DETERMINISTIC
BEGIN
DECLARE lvl varchar(10);
IF p_creditLimit > 50000 THEN
SET lvl = 'PLATINUM';
ELSEIF p_creditLimit >= 10000 THEN
SET lvl = 'GOLD';
ELSE
SET lvl = 'SILVER';
END IF;
RETURN (lvl);
END
CREATE FUNCTION udf_contaOrdini(cliente int)
RETURNS INT(11)
BEGIN
DECLARE conteggio INT;
SELECT count(*) INTO conteggio FROM orders
WHERE customerNumber = cliente;
RETURN conteggio;
END
CREATE TRIGGER nome quando
ON nomeTabella
FOR EACH ROW
BEGIN
codice
END
Che operazione controlliamo?
INSERT
, UPDATE
o DELETE
Quando devo eseguire il trigger?
BEFORE
AFTER
ES: BEFORE UPDATE
Es: OLD.contactLastName
CREATE TABLE employees_audit (
id int(11) NOT NULL AUTO_INCREMENT,
employeeNumber int(11) NOT NULL,
lastname varchar(50) NOT NULL,
changedon datetime DEFAULT NULL,
changedBy varchar(50) DEFAULT NULL,
action varchar(50) DEFAULT NULL,
PRIMARY KEY (id)
)
DELIMITER $$
CREATE TRIGGER trg_beforeUpdateEmployees
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employees_audit
SET action = 'update',
employeeNumber = OLD.employeeNumber,
lastname = OLD.lastname,
changedon = NOW(),
changedby = user();
END$$
DELIMITER ;
UPDATE employees
SET lastName = 'Phan'
WHERE employeeNumber = 1056
La sintassi è la stessa vista nelle SP
SIGNAL sqlstate '45001' SET message_text = "No way !";
Hint: IF NEW.creditLimit >
OLD.creditLimit
CREATE TRIGGER trg_beforeUpdateCustomer
BEFORE UPDATE ON customers
FOR EACH ROW BEGIN
IF NEW.creditLimit > OLD.creditLimit THEN
SIGNAL sqlstate '45001' SET message_text = "Basta crediti!";
END IF;
END
Dipartimento | |
---|---|
NroDip | MatricolaMGR |
1 | 50 |
Progetto | |
---|---|
NroProg | Obiettivo |
10 | NO |
20 | NO |
Impiegato | ||||
---|---|---|---|---|
Matricola | Nome | Salario | NDip | NProg |
50 | Rossi | 59.000 | 1 | 20 |
51 | Verdi | 56.000 | 1 | 10 |
52 | Bianchi | 50.000 | 1 | 20 |
Evento: update di Obiettivo in Progetto
Condizione: Obiettivo = ‘SI’
Azione: incrementa del 10% il salario degli impiegati coinvolti
CREATE TRIGGER Bonus
AFTER UPDATE OF Obiettivo ON Progetto
FOR EACH ROW
WHEN NEW.Obiettivo = 'SI'
BEGIN
update Impiegato
set Salario = Salario*1.10
where NProg = NEW.NroProg;
END;
Evento: update di Salario in Impiegato
Condizione: nuovo salario maggiore di quello del manager
Azione: decrementa il salario rendendolo uguale a quello del manager
CREATE TRIGGER ControllaIncremento
AFTER UPDATE OF Salario ON Impiegato
FOR EACH ROW
DECLARE X number;
BEGIN
SELECT Salario into X FROM Impiegato JOIN Dipartimento
ON Impiegato.Matricola = Dipartimento.MatricolaMGR
WHERE Dipartimento.NroDip= NEW.NDip;
IF NEW.Salario > X
update Impiegato set Salario = X
where Matricola = NEW.Matricola;
ENDIF;
END;
Evento: update di Salario in Impiegato
Condizione: decremento maggiore del 3%
Azione: decrementa il salario del solo 3%
CREATE TRIGGER ControllaDecremento
AFTER UPDATE OF Salario ON Impiegato
FOR EACH ROW
WHEN (NEW.Salario < OLD.Salario * 0.97)
BEGIN
update Impiegato
set Salario=OLD.Salario*0.97
where Matricola = NEW.Matricola;
END;
UPDATE Progetto SET Obiettivo = 'SI' WHERE NroProg = 10
Evento: update dell'attributo Obiettivo in Progresso
Condizione: vera
Azione: si incrementa del 10% il salario di Verdi
Progetto | |
---|---|
NroProg | Obiettivo |
10 | SI |
20 | NO |
Impiegato | ||||
---|---|---|---|---|
Matricola | Nome | Salario | NDip | NProg |
50 | Rossi | 59.000 | 1 | 20 |
51 | Verdi | 61.600 | 1 | 10 |
52 | Bianchi | 50.000 | 1 | 20 |
Evento: update di Salario in Impiegato
Condizione: vera (il salario dell'impiegato Verdi supera quello del manager Rossi)
Azione: si modifica il salario di Verdi rendendolo uguale a quello del manager Rossi
Impiegato | ||||
---|---|---|---|---|
Matricola | Nome | Salario | NDip | NProg |
50 | Rossi | 59.000 | 1 | 20 |
51 | Verdi | 59.000 | 1 | 10 |
52 | Bianchi | 50.000 | 1 | 20 |
Evento: update dell'attributo salario in Impiegato
Condizione: vera (il salario di Verdi è stato decrementato per più del 3%)
Azione: si decrementa il salario di Verdi del solo 3%
Impiegato | ||||
---|---|---|---|---|
Matricola | Nome | Salario | NDip | NProg |
50 | Rossi | 59.000 | 1 | 20 |
51 | Verdi | 59.752 | 1 | 10 |
52 | Bianchi | 50.000 | 1 | 20 |
Impiegato | ||||
---|---|---|---|---|
Matricola | Nome | Salario | NDip | NProg |
50 | Rossi | 59.000 | 1 | 20 |
51 | Verdi | 59.000 | 1 | 10 |
52 | Bianchi | 50.000 | 1 | 20 |
Tool e linguaggi che lavorano con i dati
Sorgente dei dati
DSN=PropDB;Uid=admin;Pwd=;
Provider=‘Microsoft.JET.OLEDB.4.0’;Data Source=‘C:\test.mdb’
SQL Server
Server=myServerAddress;Database=myDB;UserId=myUsername;Password=myPassword;
Server=myServerAddress;Database=myDB;Uid=myUsername;Pwd=myPassword;
ADO .NET è una collezione di classi, interfacce, strutture e tipi che gestiscono l’accesso ai dati da fonti relazionali all’ interno del .NET Framework
RecordSet
è il contenitore dei datiRecordSet
è una tabella che contiene tutti i datiConnection
Command
DataReader
DataAdapter
Esistono 4 tipologie diverse di JDBC
SqlConnection -> Connection
SqlCommand -> Statement
SqlDataReader -> ResultSet
// The newInstance() call is a work around for some
// broken Java implementations
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();
con = DriverManager.getConnection("jdbc:mysql://localhost/" +
"DBNAME?" +
"user=XXX&" +
"password=YYY");
Statement stmt = null;
ResultSet rs = null;
try {
stmt = con.createStatement();
rs = stmt.executeQuery("SELECT foo FROM bar");
}
catch (SQLException ex){
// handle any errors
}
finally {
// some controls...
rs.close();
stmt.close();
}
ResultSet rs = stm.executeQuery("select * from persone");
while (rs.next()) {
String col1 = rs.getString("colonna1");
}
stm.executeUpdate("UPDATE tabella SET colonna = val ...");
stm.executeUpdate("INSERT ...");
stm.executeUpdate("DELETE ...");
String sql = "SELECT nome FROM persone WHERE cognome = ?";
PreparedStatement prepared = connection.prepareStatement(sql);
prepared.setString(1, "Rossi");
ResultSet rs = stm.executeQuery();
String sql = "insert into persone (cognome, nome, eta) values (?,?,?)";
PreparedStatement prepared = connection.prepareStatement(sql);
prepared.setString(1, "Marroni");
prepared.setString(2, "Enrico");
prepared.setInt(3, 55);
prepared.executeUpdate();
CallableStatement cStm = con.prepareCall("{call sp_name(?, ?)}");
cStm.setString(1, "abcdefg");
boolean hadResults = cStm.execute();
CallableStatement cStm = con.prepareCall("{call sp_name(?, ?)}");
cStm.registerOutParameter(2, Types.INTEGER);
// or cStmt.registerOutParameter("inOutParam", Types.INTEGER);
cStm.setString(1, "abcdefg");
cStm.setString(2, 1);
// or cStmt.setString("inputParam", 1);
boolean hadResults = cStm.execute();
boolean hadResults = cStm.execute();
while (hadResults) {
ResultSet rs = cStm.getResultSet();
hadResults = cStm.getMoreResults();
}
int outputValue = cStm.getInt(2); // index-based
Scanner in = new Scanner(System.in);
String s = in.nextLine();
String sql = "SELECT * FROM Users WHERE UserId = "+s+";"
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
String col1 = rs.getString("...");
}
Input: 100 OR 1=1
String sql = "SELECT * FROM Users WHERE UserId = "+s+";"
una volta inseriti i valori diventa:
SELECT * FROM Users WHERE UserId = 100 OR 1=1;
Input: 100 OR 1=1
String sql = "SELECT UserId, Name, Password FROM Users WHERE UserId = "+s+";"
una volta inseriti i valori diventa:
SELECT UserId, Name, Password FROM Users WHERE UserId = 100 OR 1=1;
Input: 100; DROP TABLE customers
String sql = "SELECT * FROM Users WHERE UserId = "+s+";"
una volta inseriti i valori diventa:
SELECT * FROM Users WHERE UserId = 100; DROP TABLE customers;
Scanner in = new Scanner(System.in);
String user = in.nextLine();
String password = in.nextLine();
String sql = "SELECT * FROM Users WHERE Name ='" + user + "' AND Pass ='" + password + "';"
ResultSet rs = stm.executeQuery(sql);
if (rs.next()) {
// autenticato
}
Input utente: ' or ''='
Input password: ' or ''='
String sql = "SELECT * FROM Users WHERE Name ='" + user + "' AND Pass ='" + password + "'"
una volta inseriti i valori diventa:
SELECT * FROM Users WHERE Name ='' or ''='' AND Pass ='' or ''='';
Scanner in = new Scanner(System.in);
String categoria = in.nextLine();
String sql = "SELECT name, description " +
"FROM products "+
"WHERE category ='" + categoria + "' and release = 1;"
ResultSet rs = stm.executeQuery(sql);
while (rs.next()) {
// autenticato
}
Input categoria: ' UNION SELECT user, password FROM users--
String sql = "SELECT name, description " +
"FROM products "+
"WHERE category ='" + categoria + "' and release = 1;"
una volta inseriti i valori diventa:
SELECT name, description FROM products
WHERE category ='' UNION SELECT user, password FROM users--' and release = 1;
Stringa inserita: ";UPDATE ... --
String sql = "SELECT ... FROM persons WHERE id = 10";
Statement = con.createStatement();
ResultSet res = cmd.executeQuery(sql);
String name = res[0]["FIRST_NAME"];
diventa
Person p = repository.GetPerson(10);
String name = p.FirstName;
public class Studente{
private long idStud;
}
… come gli dico che id
è la chiave primaria?
annotazioni (attributi in C#)
@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private long idStud;
@Entity
@Table(name = "Studente")
public class Studente{
@Id
@Column(name = "idStud")
private long idStud;
@Column(name = "nome")
String nome;
}
persist()
merge()
Employee employee = em.find(Employee.class, 1);
Query q1 = em.createQuery
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Country> q = cb.createQuery(Country.
class);
Root<Country> c = q.from(Country.class);
q.select(c);
@NamedQuery(name="Country.findAll", query="SELECT c FROM
Country c")
Un oggetto è lazy se non contiene tutti i dati ma sa come ottenerli
Effettua la query solo quando servono i dati:
opzione dell’annotazione della relationship
@OneToMany(fetch=javax.persistence.FetchType.LAZY)
creo un metodo privato ma senza parametri
@PreUpdate
@PrePersist
@NamedStoredProcedureQuery(
name = "ReadAddressById",
resultClasses = Address.class,
procedureName = "READ_ADDRESS",
parameters = {
@StoredProcedureParameter(mode=IN,
name="P_ADDRESS_ID", type=Long.class)
}
)
@Entity
public class Address {
...
}
@org.hibernate.envers.Audited
AuditReader reader = AuditReaderFactory.get(em)
Event firstRevision = reader.find(Event.class, 2L, 1);
Event secondRevision = reader.find(Event.class, 2L, 2);
Proviamo a modellare una applicazione definendo direttamente lo schema logico della base di dati
dobbiamo pensare subito a come correlare le varie tabelle (chiavi etc.)
Il più diffuso modello concettuale, ne esistono molte versioni (più o meno) diverse
impiegato, città, conto corrente, ordine, fattura
nello schema concettuale rappresentiamo le entità, non le singole istanze
Esempi:
Chiamata anche: relazione, correlazione, associazione
Coppia di valori associati ad ogni entità che partecipa ad una relationship
Specificano il numero min e max di occorrenze di una relationship a cui una occorrenza di una entità può partecipare
Per semplicità useremo tre simboli:
È possibile associare delle cardinalità anche agli attributi, con due scopi:
Tutte le proprietà (attributi, relationship, altre generalizzazioni) dell’entità genitore vengono ereditate dalle entità figlie e non rappresentate esplicitamente
Consideriamo (senza perdita di generalità) solo generalizzazioni esclusive e distinguiamo fra totali e parziali
Entità | Descrizione | Attributi | Identificatore |
---|---|---|---|
Impiegato | Dipendente dell’azienda | Codice, Cognome, Stipendio | Codice |
Progetto | Progetti aziendali | Nome, Budget | Nome |
Dipartimento | Struttura aziendale | Nome, Telefono | Città |
Sede | Sede dell’azienda | Città, Indirizzo | Città |
Relazioni | Descrizione | Componenti | Attributi |
---|---|---|---|
Direzione | Direzione di dipartimento | Impiegato, Dipartimento, Stipendio | |
Afferenza | Afferenza a dipartimento | Impiegato, Dipartimento | Data |
Partecipazione | Partecipazione a un progetto | Impiegato, Progetto | |
Composizione | Composizione dell’azienda | Dipartimento, Sede |
Vincoli di integrità sui dati |
---|
(1) Il direttore di un dipartimento deve afferire a tale dipartimento |
(2) Un impiegato non deve avere uno stipendio maggiore del direttore del suo dipartimento |
(3) Un dipartimento con sede a Roma deve essere diretto da un impiegato con anzianità > 10 |
(4) Un impiegato che non afferisce a nessun dipartimento non deve partecipare a nessun progetto |
Le persone hanno CF, cognome ed età; gli uomini anche la posizione militare; gli impiegati hanno lo stipendio e possono essere segretari, direttori o progettisti (un progettista può essere anche responsabile di progetto); gli studenti (che non possono essere impiegati) un numero di matricola; esistono persone che non sono né impiegati né studenti (ma i dettagli non ci interessano)
Il reperimento dei requisiti è un’attività difficile e non standardizzabile
Società di formazione (1) |
---|
Si vuole realizzare una base di dati per una società che eroga corsi, di cui vogliamo rappresentare i dati dei partecipanti ai corsi e dei docenti. Per gli studenti (circa 5000), identificati da un codice, si vuole memorizzare il codice fiscale, il cognome, l'età, il sesso, il luogo di nascita, il nome dei loro attuali datori di lavoro, i posti dove hanno lavorato in precedenza insieme al periodo, l'indirizzo e il numero di telefono, i corsi che hanno frequentato (i corsi sono in tutto circa 200) e il giudizio finale. |
Società di formazione (2) |
---|
Rappresentiamo anche i seminari che stanno attualmente frequentando e, per ogni giorno, i luoghi e le ore dove sono tenute le lezioni. I corsi hanno un codice, un titolo e possono avere varie edizioni con date di inizio e fine e numero di partecipanti. Se gli studenti sono liberi professionisti, vogliamo conoscere l'area di interesse e, se lo possiedono, il titolo. Per quelli che lavorano alle dipendenze di altri, vogliamo conoscere invece il loro livello e la posizione ricoperta. |
Società di formazione (3) |
---|
Per gli insegnanti (circa 300), rappresentiamo il cognome, l'età, il posto dove sono nati, il nome del corso che insegnano, quelli che hanno insegnato nel passato e quelli che possono insegnare. Rappresentiamo anche tutti i loro recapiti telefonici. I docenti possono essere dipendenti interni della società o collaboratori esterni. |
Termine | Descrizione | Sinonimi | Collegamenti |
---|---|---|---|
Partecipante | Persona che partecipa ai corsi | Studente | Corso, Società |
Docente | Docente dei corsi. Può essere esterno | Insegnante | Corso |
Corso | Corso organizzato dalla società. Può avere più edizioni | Seminario | Docente |
Società | Ente presso cui i partecipanti lavorano o hanno lavorato | Posti | Partecipante |
Frasi di carattere generale |
---|
Si vuole realizzare una base di dati per una società che eroga corsi, di cui vogliamo rappresentare i dati dei partecipanti ai corsi e dei docenti. |
Frasi relative ai partecipanti |
---|
Per i partecipanti (circa 5000), identificati da un codice, rappresentiamo il codice fiscale, il cognome, l'età, il sesso, la città di nascita, i nomi dei loro attuali datori di lavoro e di quelli precedenti (insieme alle date di inizio e fine rapporto), le edizioni dei corsi che stanno attualmente frequentando e quelli che hanno frequentato nel passato, con la relativa votazione finale in decimi. |
Frasi relative ai datori di lavoro |
---|
Relativamente ai datori di lavoro presenti e passati dei partecipanti, rappresentiamo il nome, l'indirizzo e il numero di telefono. |
Frasi relative ai corsi |
---|
Per i corsi (circa 200), rappresentiamo il titolo e il codice, le varie edizioni con date di inizio e fine e, per ogni edizione, rappresentiamo il numero di partecipanti e il giorno della settimana, le aule e le ore dove sono tenute le lezioni. |
Frasi relative a tipi specifici di partecipanti |
---|
Per i partecipanti che sono liberi professionisti, rappresentiamo l'area di interesse e, se lo possiedono, il titolo professionale. Per i partecipanti che sono dipendenti, rappresentiamo invece il loro livello e la posizione ricoperta. |
Frasi relative ai docenti |
---|
Per i docenti (circa 300), rappresentiamo il cognome, l'età, la città di nascita, tutti i numeri di telefono, il titolo del corso che insegnano, di quelli che hanno insegnato nel passato e di quelli che possono insegnare. I docenti possono essere dipendenti interni della società di formazione o collaboratori esterni. |
Design Patterns: Elements of Reusable Object-Oriented Software (1997)
Concetto | Tipo | Volume |
---|---|---|
Sede | E | 10 |
Dipartimento | E | 80 |
Impiegato | E | 2000 |
Progetto | E | 500 |
Composizione | R | 80 |
Afferenza | R | 1900 |
Direzione | R | 80 |
Partecipazione | R | 6000 |
Operazione: trova tutti i dati di un impiegato, del dipartimento nel quale lavora e dei progetti ai quali partecipa
Operazione | Tipo | Frequenza |
---|---|---|
Assegna impiegato a progetto | Interattiva | 50/giorno |
Trova tutti i dati di un impiegato | Interattiva | 100/giorno |
Trova dati di tutti impiegati in un dipartimento | Interattiva | 10/giorno |
Trova dipartimenti in sedi | Batch | 2/settimana |
In questa fase si decide se eliminare le ridondanze o mantenerle
Concetto | Tipo | Volume |
---|---|---|
Città | E | 200 |
Persona | E | 1000000 |
Residenza | R | 1000000 |
Concetto | Costrutto | Accessi | Tipo |
---|---|---|---|
Persona | Entità | 1 | S |
Residenza | Relazione | 1 | S |
Città | Entità | 1 | L |
Città | Entità | 1 | S |
Concetto | Costrutto | Accessi | Tipo |
---|---|---|---|
Città | Entità | 1 | L |
Concetto | Costrutto | Accessi | Tipo |
---|---|---|---|
Persona | Entità | 1 | S |
Residenza | Relazione | 1 | S |
Concetto | Costrutto | Accessi | Tipo |
---|---|---|---|
Città | Entità | 1 | L |
Residenza | Relazione | 5000 | L |
Conviene se gli accessi al padre e ai figli sono contestuali
Conviene se gli accessi ai figli sono distinti
Conviene se gli accessi alle entità figlie sono separati dagli accessi al padre
Gli accessi si riducono:
Criteri:
Impiegato(Matricola, Cognome, Stipendio)
Progetto(Codice, Nome, Budget)
Partecipazione(Matricola, Codice, DataInizio)
Impiegato(Matricola, Cognome, Stipendio)
Progetto(Codice, Nome, Budget)
Partecipazione(Matricola, Codice, DataInizio)
Impiegato(Matricola, Cognome, Stipendio)
Progetto(Codice, Nome, Budget)
Partecipazione(Impiegato, Progetto, DataInizio)
Prodotto(Codice, Nome, Costo)
Composizione(Composto, Componente, Quantità)
Fornitore(PartitaIVA, Nome)
Prodotto(Codice, Genere)
Dipartimento(Nome, Telefono)
Fornitura(Fornitore, Prodotto, Dipartimento, Quantità)
Giocatore(Cognome, DataNascita, Ruolo)
Contratto(CognomeGiocatore, DataNascitaGiocatore, Squadra, Ingaggio)
Squadra(Nome, Città, ColoriSociali)
È corretto?
Giocatore(Cognome, DataNascita, Ruolo)
Contratto(CognomeGiocatore, DataNascitaGiocatore, Squadra, Ingaggio)
Squadra(Nome, Città, ColoriSociali)
Giocatore(Cognome, DataNascita, Ruolo, Ingaggio, Squadra)
Squadra(Nome, Città, ColoriSociali)
Studente(Matricola, Università, Cognome, AnnoDiCorso)
Università(Nome, Città, Indirizzo)
varie possibilità:
Impiegato(Codice, Cognome, Stipendio)
Dipartimento(Nome, Sede, Telefono, Direttore, DataInizio)
→ con vincolo di integrità referenziale, senza valori nulli
Impiegato(Codice, Cognome, Dipartimento, Sede, Data)
Dipartimento(Nome, Città, Telefono, Direttore)
Sede(Città, Via, CAP)
Progetto(Nome, Budget)
Partecipazione(Impiegato, Progetto)
Lista di coppie (termine, pagina) ordinata alfabeticamente sui termini, posta in fondo al libro e separabile da esso
Indice su un campo sul cui ordinamento è basata la memorizzazione
Esempio: indice generale di un libro
Indice su un campo con ordinamento diverso da quello di memorizzazione
Esempio: indice analitico di un libro
Contiene tutti i valori della chiave
Contiene solo alcuni valori della chiave
Indice primario | |
---|---|
Matricola | Dove |
001 | 0x22200 |
004 | 0x222AA |
006 | 0x2223B |
Dati | |
---|---|
Matricola | Nome |
001 | Giorgio |
002 | Franco |
003 | Alberto |
004 | Paolo |
005 | William |
006 | Andrea |
007 | Filippo |
008 | Marco |
Indice secondario | |
---|---|
Nome | Dove |
Alberto | 0x2231 |
Andrea | 0x2223B |
Filippo | 0x2731 |
Franco | 0x2831 |
Giorgio | 0x22200 |
Marco | 0x3231 |
Paolo | 0x222AA |
William | 0x2201 |
=, >, >=, <, <=
o BETWEEN
Fonte: wikipedia
Fonte: wikipedia
=
o <>
Impiegato | Stipendio | Progetto | Bilancio | Funzione |
---|---|---|---|---|
Rossi | 20 | Marte | 2 | tecnico |
Verdi | 35 | Giove | 15 | progettista |
Verdi | 35 | Venere | 15 | progettista |
Neri | 55 | Venere | 15 | direttore |
Neri | 55 | Giove | 15 | consulente |
Neri | 55 | Marte | 2 | consulente |
Mori | 48 | Marte | 2 | direttore |
Mori | 48 | Vedere | 15 | progettista |
Bianchi | 48 | Vedere | 15 | progettista |
Bianchi | 48 | Giove | 15 | direttore |
ID | Cognome | Nome | Telefoni |
---|---|---|---|
321 | De Lorenzo | Andrea | 555-55555; 5551-55111 |
ID | Cognome | Nome | Telefono1 | Telefono2 |
---|---|---|---|---|
321 | De Lorenzo | Andrea | 555-55555 | 5551-55111 |
ID | Cognome | Nome |
---|---|---|
321 | De Lorenzo | Andrea |
Persona | Telefono |
---|---|
321 | 555-55555 |
321 | 5551-55111 |
Matricola | Esame | Studente | Voto |
---|---|---|---|
1234 | M01 | De Lorenzo | 28 |
1234 | M03 | De Lorenzo | 30 |
1234 | I12 | De Lorenzo | 25 |
Matricola | Esame | Voto |
---|---|---|
1234 | M01 | 28 |
1234 | M03 | 30 |
1234 | I12 | 25 |
Matricola | Studente |
---|---|
1234 | De Lorenzo |
Torneo | Anno | Vincitore | Data di nascita vincitore |
---|---|---|---|
Indiana Invitational | 1998 | Al Fredrickson | 21 luglio 1975 |
Cleveland Open | 1999 | Bob Albterson | 28 settembre 1968 |
Des Moines Masters | 1999 | Al Fredrickson | 21 luglio 1975 |
Torneo | Anno | Vincitore |
---|---|---|
Indiana Invitational | 1998 | Al Fredrickson |
Cleveland Open | 1999 | Bob Albterson |
Des Moines Masters | 1999 | Al Fredrickson |
Vincitore | Data di nascita vincitore |
---|---|
Al Fredrickson | 21 luglio 1975 |
Bob Albterson | 28 settembre 1968 |
Termine per indicare un insieme di dati talmente ampio che i metodi tradizionali di persistenza e processo sono inadeguati
Da Filippo Via Roma 2, Roma | ||
Ricevuta Fiscale 1235 del 12/01/2020 | ||
3 | Coperti | 3,00 |
2 | Antipasti | 6,20 |
3 | Primi | 12,00 |
2 | Bistecche | 18,00 |
- | ||
- | ||
Totale | 39,20 |
Da Filippo Via Roma 2, Roma | ||
Ricevuta Fiscale 1240 del 13/10/2020 | ||
2 | Coperti | 2,00 |
2 | Antipasti | 7,00 |
2 | Primi | 8,00 |
2 | Orate | 20,00 |
2 | Caffè | 2,00 |
- | ||
Totale | 39,00 |
Abbiamo ristrutturato il documento dividendolo in tabelle diverse
Ricevute | ||
---|---|---|
Numero | Data | Totale |
1235 | 12/10/2020 | 39,20 |
1240 | 12/10/2020 | 39,00 |
Dettaglio | ||||
---|---|---|---|---|
Numero | Riga | Qtà | Descrizione | Importo |
1235 | 1 | 3 | Coperti | 3,00 |
1235 | 2 | 2 | Antipasti | 6,20 |
1235 | 3 | 3 | Primi | 12,00 |
1235 | 4 | 2 | Bistecche | 18,00 |
1240 | 1 | 2 | Coperti | 2,00 |
... | ... | ... | ... | ... |
Not Only SQL
scrivo un valore, lo leggo ed ottengo lo stesso dato
posso leggere/scrivere i dati
il sistema può sopravvivere a perdite di dati
sembra che il sistema funzioni sempre
il sistema non sarà sempre consistente
prima o poi il sistema diventerà consistente
tipicamente salvati in JSON
{'id': 1001,
'customer_id': 7231,
'products': [
{'product_id':4432, 'quantity': 9},
{'product_id':4422, 'quantity': 19}
]
}
// utente admin
{
name : "admin",
password : "P4ssword",
groups : [ "administrators" , "system" ],
email : "admin@units.it"
}
// utente pippo
{
name : "pippo",
openId : "http://pippo.blogspot.com",
groups : [ "users" ],
birthdate : new Date(1984, 2, 12),
email : "pippo@gmail.com"
}
// utente user
{
name : "user",
password : "passwordsicura",
score : 31,
email : "info@gmail.com"
}
{
sent : new Date(2010, 3, 5),
text : "Ciao a tutti, ..."
sender : "admin",
views : 120
}
{
sent : new Date(2010, 3, 6),
text : "Argomento a caso, ...",
sender : "user"
views : 300,
tags : [ "Java" ]
}
{
name : "user1",
address : {
zipCode : "60100",
country : "Italy"
},
messages : {
{
sent : new Date(2010, 3, 5),
text : "Ciao a tutti, ...",
views : 120
},
{
sent : new Date(2010, 3, 18),
text : "Articolo MokaByte, ...",
views : 300,
tags : [ "Java" ]
}
}
}
_id
_id
viene aggiunto un indice automaticamenteposso aggiungere indici su qualsiasi proprietà o oggetto
db.users.ensureIndex( {name : 1} )
db.users.ensureIndex( { name : 1, address : 1 } )
se voglio indicizzarla uso indice sparso
db.users.ensureIndex(
{ openId : 1 } ,
{ sparse : true }
);
--shardsvr
)--configsvr
)db.runCommand( { addshard : "shardhost" } )
db.runCommand( { enablesharding : "mydatabase" } )
db.runCommand( {
shardcollection : "mydatabase.users",
key : {name : 1} } )
programma separato che si collega al DBMS
use nome_del_db
db.nome_del_db
db.users.save( {
name : "pippo",
groups : [ "users", "writers" ],
birthdate : new Date(1985, 2, 3) }
)
db.users.save( {
name : "admin",
groups : [ "administrators" ],
system : true }
)
db.users.find()
db.users.find( { system: true } )
db.users.find({ $or : [
{groups : "administrators"},
{system : {$exists : false}}
]
})
db.users.find( { system : true } ,
{ name : 1 } )
db.users.save( { _id : ObjectId("4d7d4621473a000000006598"),
name : "admin", groups : [ "administrators" , "system" ] }
db.users.update({ name : "admin" },
{ name : "admin", groups : [ "administrators" , "system" ] }
)
db.users.remove()
db.users.remove( { name : "pippo" } )
function map(String name, String document):
for each word w in document:
emit (w, 1)
function reduce(String word, Iterator partialCounts):
sum = 0
for each pc in partialCounts:
sum += pc
emit (word, sum)
Fonte: mongoDB Documentation
{ message : "testo del messaggio", tags : [ "Java", "MongoDB", "SQL" ] }
{ message : "altro test", tags : [ "SQL" ] }
function mapTags() {
this.tags.forEach(function(t) {
emit(t, 1);
});
}
function totalValues(key, values) {
var total = 0;
for ( var i=0; i < values.length; i++)
total += values[i];
return total;
}
mrRes = db.messages.mapReduce(mapTags, totalValues)
{
"result" : "tmp.mr.mapreduce_1300279574_6",
"timeMillis" : 5,
"counts" : {
"input" : 2,
"emit" : 4,
"output" : 3
},
"ok" : 1,
}
db.find(mrRes.results)
{ "_id" : "Java", "value" : 1 }
{ "_id" : "MongoDB", "value" : 1 }
{ "_id" : "SQL", "value" : 2 }
MongoDB raggruppa i documenti per collezioni
MongoClient mongo = new MongoClient( "localhost");
DB db = mongo.getDB( "mydb" );
DBCollection coll = db.getCollection("impiegati");
DBCollection coll = db.getCollection("impiegati");
BasicDBObject doc = new BasicDBObject("name", "Pip")
.append("type", "dirigente")
.append("count", 1)
.append("info", new BasicDBObject("tel","04055555")
.append("email", "test@email.it"));
coll.insert(doc);
BasicDBObject q = new BasicDBObject("name", "Pip");
cursor = coll.find(q);
try {
while(cursor.hasNext()) {
System.out.println(cursor.next());
}
} finally {
cursor.close();
}
// i > 50
query = new BasicDBObject("i",
new BasicDBObject("$gt", 50));
cursor = coll.find(q);
try {
while(cursor.hasNext()) {
System.out.println(cursor.next());
}
} finally {
cursor.close();
}
// name == "pippo" || name == "pluto"
BasicDBList or = new BasicDBList();
or.add("pippo");
or.add("pluto");
query = new BasicDBObject("$or",
new BasicDBObject("name",or));
cursor = coll.find(q);
try {
while(cursor.hasNext()) {
System.out.println(cursor.next());
}
} finally {
cursor.close();
}
<TITLE>This is the title.</TITLE>
This is <B>bold</B> and this is <I>italic</I>
{\rtf
Ciao!\par
Ecco del testo in {\b grassetto}.\par
}
Una formula $\frac{x^2}{2}$
Una formula $\frac{x^2}{2}$
## Queste slide
sono *fatte* in **markdonw**
sono fatte in markdonw
<tag>content</tag>
<tag name="value"></tag>
<persona>
<nome>Andrea</nome>
<indirizzo>
<via>Via Belposto</via>
<civico>18</civico>
<citta>Disneyland</citta>
</indirizzo>
</persona>
<!-- commento -->
<?xml version="1.0" encoding="UTF-8"?>
Un documento è Well Formed se ha una sintassi XML corretta
Un documento è Valid XML se è well formed e risulta conforme allre regole DTD o XML Schema Definition (XSD)
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE note
[ <!ELEMENT note (to,from,heading,body)>
<!ELEMENT to (#PCDATA)>
<!ELEMENT from (#PCDATA)>
<!ELEMENT heading (#PCDATA)>
<!ELEMENT body (#PCDATA)>
]>
<note>
<to>Jo</to>
<from>Mary</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend</body>
</note>
<?xml version="1.0" encoding="UTF-8"?>
<note>
<to>Jo</to>
<from>Mary</from>
<heading>Reminder</heading>
<body>Don't forget me this weekend</body>
</note>
<!DOCTYPE note
[ <!ELEMENT note (to,from,heading,body)>
<!ELEMENT to (#PCDATA)>
<!ELEMENT from (#PCDATA)>
<!ELEMENT heading (#PCDATA)>
<!ELEMENT body (#PCDATA)>
]>
Mr. Walter C. Brown
49
Featherstone Street
LONDON
EC1Y 8SY
UK
@XmlAccessorType(XmlAccessType.FIELD)
@XmlType(name = "", propOrder = {"recipient","house","street","town",
"county", "postCode", "country"})
@XmlRootElement(name = "Address")
public class Address {
@XmlElement(name = "Recipient", required = true)
protected String recipient;
@XmlElement(name = "House", required = true)
protected String house;
@XmlElement(name = "Street", required = true)
protected String street;
@XmlElement(name = "Town", required = true)
protected String town;
@XmlElement(name = "County")
protected String county;
@XmlElement(name = "PostCode", required = true)
protected String postCode;
@XmlElement(name = "Country")
protected String country;
}
CSS | XML | |
---|---|---|
Può essere usato con HTML? | ✓ | X |
Può essere usato con XML? | ✓ | ✓ |
Transformation language? | X | ✓ |
Sintassi | CSS | XML |
CATALOG
{background-color: #ffffff; width: 100%;}
CD
{display: block; margin-bottom: 30pt; margin-left: 0;}
TITLE
{color: #FF0000; font-size: 20pt;}
ARTIST
{color: #0000FF; font-size: 20pt;}
COUNTRY,PRICE,YEAR,COMPANY
{Display: block; color: #000000; margin-left: 20pt;}
Empire Burlesque
Bob Dylan
USA Columbia
10.90
1985
.
nodo corrente..
nodo padre del nodo corrente/
nodo radice, o figlio del nodo corrente//
discendente del nodo corrente@
attributo del nodo corrente*
qualsiasi nododocument(“libri.xml”)/Elenco/Libro
for $x in doc("books.xml")/bookstore/book
where $x/price>30
order by $x/title
return $x/title
<persons>
<person username="JS1">
<name>John</name>
<family-name>Smith</family-name>
</person>
<person username="MI1">
<name>Morka</name>
<family-name>Ismincius</family-name>
</person>
</persons>
John
Morka
L'agenzia immobiliare gestisce delle transazioni di vendita e di affitto di immobili, che hanno un codice una data ed un valore. In caso di affitto, ci interessa il periodo. Ogni agenzia ha un identificativo, un nome ed una città in cui si trova.
Gli immobili hanno un codice, un indirizzo, una città, il numero di locali e la metratura. Alcuni sono di interesse storico e di questi è importante l'anno di costruzione. Altri sono ristrutturati e ci interessa la data di ultima ristrutturazione.
Gli immobili possono essere acquistati da enti, che sono o persone o società. Degli enti ci va salvato il codice fiscale, l'indirizzo, la città e la regione. Delle persone ci interessa nome e cognome, mentre delle aziende il nome e il capitale sociale.