Basi di Dati

Andrea De Lorenzo, University of Trieste

Gruppo di Ingegneria Informatica

Sylvio Barbon Jr.

Fondamenti di Informatica

Progettazione del software e dei sistemi informativi

meta learning, applied ML, process mining

Alberto Bartoli

Reti di calcolatori

Cybersecurity

security, applied ML, evolutionary computation

Andrea De Lorenzo

Basi di dati

Programmazione web

security, applied AI&ML, information retrieval, GP

Eric Medvet

Programmazione avanzata

Introduction to machine learning and evolutionary robotics

evolutionary computation, embodied AI, applied ML

Laura Nenzi

Cyber-physical systems

Introduction to Artificial Intelligence

formal methods, runtime verification

Martino Trevisan

Reti di calcolatori

Sistemi operativi

Architetture dei sistemi digitali

network measurements, data privacy, big data

Orario lezioni

http://delorenzo.inginf.units.it/

Modalità lezioni

• Lezioni in presenza e registrate
• Registrazioni disponibili per circa 6 mesi nel Team del corso
• Accessi al Team del corso tramite codice

1rran50

Modalità esame

Test + Progetto + Orale

Test a risposta multipla, basta passarlo una volta Progetto va consegnato 3 giorni lavorativi prima dell’appello
Se l’esame è mercoledì, venerdì è troppo tardi per inviare il progetto!
Se l’esame è giovedì, lunedì è troppo tardi per inviare il progetto!

Gestione delle informazioni

L’essere umano genera e gestisce tante informazioni:

• idee informali
• linguaggio naturale (scritto o parlato, in lingue diverse)
• disegni, grafici, schemi
• numeri
• codici

Gestione delle informazioni

... salvate in tanti modi diversi

• memoria
• carta
• pietra
• scritta sul muro
• elettronica

Gestione delle informazioni

Anche le organizzazioni generano informazioni:

• utenze telefoniche
• conti correnti
• studenti iscritti ad un corso di laurea
• quotazioni di azioni

Codifica delle informazioni

Era informatica

Le informazioni vanno codificate

• si aggiungono elementi artificiali
• primo esempio: anagrafe
• nome e cognome
• indirizzo
• codice fiscale

Informazione vs Dato

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

Numeri

Come codifico i numeri?

• Numeri naturali: facile, in binario
• Numeri interi: devo decidere come rappresentare il segno
• Numeri razionali?

10010011110011001111

Che numero è?

Che numero è?

* con qualche zero davanti

Dati e Applicazioni

• I dati possono variare nel tempo (es: conto corrente)
• Le modalità con cui i dati sono rappresentati sono di solito stabili
• Le operazioni sui dati variano spesso (es: ricerche)

separare i dati dalle applicazioni che operano su essi

Data Base

Genericamente:

Collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione.

• schede perforate
• file CSV
• foglio di calcolo
• file XML
• Access

DataBase Management System

Software in grado di gestire collezioni di dati che siano:

• grandi: di dimensioni (molto) maggiori della memoria centrale
• persistenti: con un periodo di vita indipendente dalla singole esecuzioni dei programmi che le utilizzano
• condivise: utilizzate da applicazioni diverse

Base di Dati

Genericamente

Collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione

Per noi

Collezione di dati gestita da un DBMS

DataBase managment System

Un DBMS deve garantire:

• affidabilità: resistenza a malfunzionamenti hardware e software
• privatezza: con una disciplina e un controllo degli accessi
• efficienza: utilizzando al meglio le risorse di spazio e tempo del sistema
• efficacia: rendendo produttive le attività dei suoi utilizzatori

Condivisione

• L’integrazione e la condivisione permettono di
  • ridurre la ridondanza (evitando ripetizioni)
  • ridurre possibilità di incoerenza (o inconsistenza) fra i dati.
• Poiché la condivisione non è mai completa (o comunque non opportuna) i DBMS prevedono meccanismi per
  • privatezza dei dati
  • limitazione all’accesso (autorizzazioni).
• La condivisione richiede coordinamento degli accessi: controllo della concorrenza.

Efficienza

• Si misura in termini di tempo di esecuzione e spazio di memoria (principale e secondaria)
• I DBMS non sono necessariamente più efficienti dei file system
• L’efficienza è il risultato della qualità del DBMS e delle applicazioni che lo utilizzano

DBMS vs FS

File System

Descrizione dei dati contenuta nell'applicazione

File System

Descrizione dei dati contenuta nell'applicazione

File System

Descrizione dei dati contenuta nell'applicazione

DBMS e Descrizione dei Dati

• Il DBMS sa come persistere i dati, per l’applicazione è un atto di fede
• I dati sono INDIPENDENTI dalla forma fisica
• I programmi parlano con il DBMS per accedere ai dati

Modello Concettuale

Analisi del problema

Modello astratto

Non dipende dallo strumento utilizzato

Modello Logico

Come rappresentare i dati individuati con il modello concettuale

• Livello intermedio tra utente e implementazione
• sottintende una specifica rappresentazione dei dati (tabelle, alberi, grafi, oggetti, …)

DataBase System

• Software
  • DBMS: interposto tra il DB e l’utente
  • Utility di supporto (sviluppo, backup)
• Utenti
  • Progettista
  • Sviluppatore
  • Amministratore
  • Utente finale

DataBase System

• Schemi (struttura dei dati)
• Dati
  • come vengono salvati
  • condivisione
  • concorrenza
  • ridondanza
• Hardware

Base di Dati

• Genericamente: collezione di dati, utilizzati per rappresentare le informazioni di interesse per una o più applicazioni di una organizzazione.
• Per noi: collezione di dati gestita da un DBMS
• Per noi - 2: collezione di dati persistenti usata dal sistema di una azienda e gestita da un DBMS

Riassunto

• Data base
  • Dati, solo dati, niente altro che i dati
• Data Base Management System
  • Software che gestisce i dati
  • Diversi vendor: IBM, Oracle, Microsoft
• Data Base System
  • DB + DBMS

Vantaggi e Svantaggi

• Vantaggi
  • Dati sono risorsa in comune
  • DB fornisce un modello unificato del business
  • Controllo centralizzato dei dati, quindi standardizzazione ed economie di scala
  • Riduzione ridondanza ed inconsistenza
  • Indipendenza dei dati
• Svantaggi
  • Costo e complessità
  • Servizi ridondanti/non necessari

Vero beneficio

Indipendenza dei dati!

Vero beneficio

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.

Schema ed Istanza

In ogni base di dati esistono:

• Schema:
  • invariante nel tempo
  • descrive la struttura
  • es: intestazione tabelle
• Istanza:
  • i valori attuali
  • possono cambiare
  • es: contenuto delle tabelle

Schemi concettuali

Permettono di rappresentare i dati in modo indipendente da ogni sistema:

• cercando di descrivere i concetti del mondo reale
• sono utilizzati nelle fasi preliminare di progettazione

Modello più diffuso: Entity-Relationship

Schermi interni (o fisici)

Rappresentazione dello schema logico per mezzo di strutture di memorizzazione

• file CSV
• file XML
• file binari

Schemi logici

Come è organizzato il DB. Diverse soluzioni:

• gerarchico
• reticolare
• relazionale
• ad oggetti

Indipendenza

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)

Indipendenza

Progettista DB ≠ Sviluppatore SW

Vista

L'amministratore del DB può modificare la struttura interna dei dati senza toccarne la visibilità esterna

IMMUNITÀ DELLE APPLICAZIONI A MODIFICHE DI STRUTTURA

Vista

Vista

Schema Esterno

== VISTA

• Descrive parte della base di dati di un modello logico
• NON è una copia dei dati

Architettura ANSi/SPARC

Schemi Logici

Schemi Logici Gerarchici

Schemi Logici Gerarchici

Problemi:

• accesso sequenziale: per arrivare al figlio devo attraversare tutti i nodi
• modifica parziale complicata
• cancellazione gerarchica
• stretto legame tra programma e struttura del database
• ridondanza

Schemi Logici Gerarchici (Ridondanza)

Schemi Logici Reticolari

• COBOL, 1970
• nodi collegati da PUNTATORI
• navigazione bi-direzionale

Schemi Logici Reticolari

Schemi Logici Relazionali

Codd, 1980

Liberarsi dei puntatori fisici

• i dati sono organizzati in tabelle di valori
• le operazioni vengono eseguite sulle tabelle
• i risultati delle operazioni sono tabelle
• i riferimenti tra dati in strutture (tabelle) diverse sono rappresentati con valori

Elementi di un DBR

Tabelle: organizzazione rettangolare di dati

• Record (righe) e campi (colonne) e domini dei dati
• I campi definiscono univocamente il tipo dei dati (dominio)
• I campi hanno un nome ed un ordine, le righe no
• Esistono tabelle vuote

Elementi di un DBR

Chiavi primarie

• Una (o più) colonne che identificano UNIVOCAMENTE il record
• Non possono essere duplicate
• Una tabella in cui ogni riga è diversa dalle altre è detta RELAZIONE

Elementi di un DBR

Relazioni

• Non esistono relazioni padre-figlio
• Le relazioni sono rappresentate da DATI COMUNI manipolabili

Chiavi Esterne (Secondarie, Foreign Key)

• Una colonna in una tabella il cui valore corrisponde ad una chiave primaria
• Sono fondamentali nella creazione delle relazioni

Dodici regole di Codd

Dodici regole di Codd

1 - Informazioni

Tutte le informazioni in un DBR sono rappresentate esplicitamente da valori in tabelle (DEFINIZIONE)

Dodici regole di Codd

2 - ACCESSO GARANTITO

Ciascun valore deve essere raggiunto univocamente da un nome di tabella, chiave primaria e nome di colonna (CHIAVI PRIMARIE)

Dodici regole di Codd

3 - VALORI NULL

Sono supportati per rappresentare informazioni mancanti indipendentemente dal tipo di dato

Dodici regole di Codd

4 - SYSTEM TABLE

Un data base relazionale deve essere strutturato logicamente come i dati e gestibile con lo stesso linguaggio

Dodici regole di Codd

5 - LINGUAGGIO DI INTERROGAZIONE STANDARD

Un DBR può supportare diversi linguaggi, ma deve supportare un linguaggio “English like” dove sia possibile (DEFINIZIONE DI SQL):

• Definire dati
• Definire viste
• Manipolare dati
• Gestire l’integrità

Dodici regole di Codd

6 - VISTE MODIFICABILI

Le viste che sono modificabili teoricamente dall’utente lo devono essere anche dal sistema (cruciale per campi calcolati);

Dodici regole di Codd

6 - VISTE MODIFICABILI

Affinché una vista sia modificabile, il DBMS deve essere in grado di tracciare ciascuna colonna e ciascuna riga UNIVOCAMENTE fino alle tabelle origine

Viste Modificabili

Viste Modificabili

Dodici regole di Codd

7 - INSERIMENTO E UPDATE DA LINGUAGGIO

Inserire e aggiornare devono avere la stessa logica “a righe” dell’estrazione (SET ORIENTED)

Dodici regole di Codd

8 - INDIPENDENZA FISICA DEI DATI

I programmi applicativi non devono sentire alcuna modifica fatta sul metodo e la locazione fisica dei dati

Dodici regole di Codd

9 - INDIPENDENZA LOGICA DEI DATI

Le modifiche al livello logico non devono richiedere cambiamenti non giustificati alle applicazioni che utilizzano il database (VISTE)

Dodici regole di Codd

10 - INTEGRITÀ

Vincoli di integrità devono essere implementabili sul motore (cruciale)

Dodici regole di Codd

11 - INDIPENDENZA DI LOCALIZZAZIONE

La distribuzione di porzioni del database su una o più allocazione fisiche o geografiche deve essere invisibile agli utenti del sistema

Dodici regole di Codd

12 - DEVE PREVENIRE ACCESSI NON DESIDERATI:

Garantisce l’impossibilità di bypassare le regole di integrità

Riassunto: DB Relazionale

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

Relation

Relazione matematica (teoria degli insiemi)

Relationship

Associazione nel modello Entity-Relationship

Relazione Matematica

• $D_1, \dots, D_n$ ($n$ insiemi anche distinti) sono i domini
• prodotto cartesiano $D_1 \times \dots \times D_n$
  • insieme di tutte le $n$-uple ($d_1, \dots, d_n$) tali che $d_1 \in D_1, \dots, d_n \in D_n$
• relazione matematica su $D_1, \dots, D_n$:
  • un sottoinsieme di $D_1 \times \dots \times D_n$

Relazione Matematica (esempio)

Relazione Matematica (proprietà)

• una relazione matematica è un insieme di $n$-uple ordinate:
  • $ (d_1, \dots, d_n ) \mid d_1 \in D_1, \dots, d_n \in D_n $
• una relazione è un insieme:
  • non c’è ordinamento tra le $n$-uple
  • le $n$-uple sono distinte
  • ogni $n$-upla è ordinata: $i$-esimo valore proviene dall’$i$-esimo dominio

Tabelle e Relazioni

• Una tabella è una relazione se:
  • i valori di ogni colonna sono omogenei
  • le righe sono diverse fra di loro
  • le intestazioni delle colonne sono diverse tra di loro
• In una tabella che rappresenta una relazione:
  • l’ordinamento tra le righe è irrilevante
  • l’ordinamento tra le colonne è irrilevante

Relazione

• Relation: relazione matematica (teoria degli insiemi)
• Relationship: rappresenta una associazione nel modello Entity-Relationship

Modello basato su VALORI

I riferimento fra dati in relazioni diverse sono rappresentati per mezzo di valori dei domini che compaiono nelle $n$-uple

Struttura basata su valori

Vantaggi

• indipendenza dalla struttura fisiche (si potrebbe avere anche con puntatori HL)
• si rappresenta solo ciò che rilevante dal punto di vista dell’applicazione
• utente finale vede stessi dati del programmatore
• portabilità dei dati tra sistemi
• puntatori direzionali

Strutture Nidificate

I valori devono essere semplici

Relazioni e strutture nidificate

Siamo stai bravi?

Abbiamo rappresentato tutti gli aspetti delle ricevute?

Dipende da cosa ci interessa:

• l’ordine delle righe è rilevante?
• possono esistere linee ripetute?
• Al bar, al servizio al tavolo, ad un gruppo:
  • Cliente: “Una birra”
  • Cameriere: “Se volete altre birre ditelo subito altrimenti non posso aggiungerle!”
• Sono possibili rappresentazioni diverse

Relazioni e strutture nidificate

Informazioni Incomplete

Ovvero: gestire i valori NULL

Ogni elemento in una tabella può essere o un valore del dominio oppure il valore nullo NULL

Informazione incompleta

Il modello relazionale impone una struttura rigida

• Le informazioni sono rappresentate per mezzo di $n$-uple
• Solo alcuni formati di $n$-upla sono ammessi: quelli che corrispondono agli schemi di relazione
• I dati disponibili possono non corrispondere al formato previsto

Esempio

NULL: come fare?

E se usassi il numero 0?

Non conviene, anche se spesso si fa, usare valori del dominio (0, stringa nulla, 99, …)

• potrebbero non esistere valori “non utilizzati”
• valori “non utilizzati” potrebbero diventare significativi
• in fase di utilizzo sarebbe necessario tener conto del significato di questi valori

Tipi di valore NULL

(Almeno) 3 casi differenti

• valore sconosciuto (quanti anni ha?)
• valore inesistente (non ha il secondo nome)
• valore non applicabile (anagrafica unica studenti/professori, i professori hanno ufficio)

I DBMS non distinguono i tipi di valore nullo

Troppi valori NULL

Vincoli di integrità

Esistono istanze di basi di dati che, pur sintatticamente corrette, non rappresentano informazioni possibili per l’applicazione di interesse.

DB Errato

Vincoli di Integrità

Proprietà che deve essere soddisfatta dalle istanza che rappresentano informazioni corrette per l’applicazione

Un vincolo è una funzione booleana (un predicato): associa ad ogni istanza il valore vero o falso

Vincoli di Integrità, perché?

• Descrizione più accurata della realtà
• contributo alla “qualità dei dati”
• utili nella progettazione
• usati dai DBMS nelle interrogazioni

Vincoli di integrità: nota

Alcuni vincoli (ma non tutti) sono supportati dai DBMS

• Possiamo specificare tali vincoli e il DBMS ne impedisce violazione
• Se non supportati, la responsabilità della verifica è dell’utente/programmatore

Tipi di Vincoli

• vincoli intrarelazionali
  • vincoli su valori (o di dominio)
  • vincoli di $n$-upla
• vincoli interrelazionali

Vincoli di valore

Voto ≥ 18 && Voto ≤ 30

Vincoli di $n$-upla

Lode solo se Voto == 30

Vincoli di $n$-upla

Lordo = (Ritenute + Netto)

Vincoli interrelazionali

Chiavi: identificare le $n$-uple

Identificare le $n$-uple

• non ci sono due ennuple con lo stesso valore sull’attributo Matricola
• non ci sono due ennuple uguali su tutti e tre gli attributi Cognome, Nome e Data di Nascita

CHIAVE

Insieme di attributi che identificano le $n$-uple di una relazione

CHIAVE

Formalmente

• Un insieme di $K$ attributi è superchiave per $r$ se non contiene due $n$-uple distinte $t_1$ e $t_2$ con $t_1^K = t_2^K$
• $K$ è chiave per $r$ se è una superchiave minimale per $r$
• superchiave minimale = non contiene un’altra superchiave

Una chiave

Matricola è una chiave:

• è superchiave
• contiene un solo attributo $r$ quindi è minimale

Un'altra chiave

Cognome, nome, nascita è un'altra chiave:

• è superchiave
• è minimale

Un'altra chiave

Non ci sono $n$-uple uguali su Cognome e Corso:

• Cognome e Corso formano una chiave
• È sempre vero o è un caso?

Chiavi

Esistenza

• Una relazione non può contenere $n$-uple distinte ma uguali
• Ogni relazione ha come superchiave l’insieme degli attributi su cui è definita
• quindi ha (almeno) una chiave

Chiavi

Importanza

• l’esistenza delle chiavi garantisce l’accessibilità a ciascun dato della base di dati
• le chiavi permettono di correlare i dati in relazioni diverse
  • il modello relazionale è basato su valori

Chiavi e valori NULL

• In presenza di valori nulli, i valori della chiave non permetteranno
  • di identificare le $n$-uple
  • di realizzare facilmente i riferimenti da altre relazioni
• la presenza di valori nulli nelle chiavi deve essere limitata

Chiavi e valori NULL

Chiave primaria

• Chiave su cui non sono ammessi valori NULL
• Notazione: sottolineatura

Integrità referenziale

• informazioni in relazioni diverse sono correlate attraverso valori comuni
• in particolare, valori delle chiavi (primarie)
• le correlazioni debbono essere "coerenti"

Integrità referenziale

Integrità referenziale

Integrità referenziale

Vincolo di Integrità Referenziale

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$

Vincolo di Integrità Referenziale

Vincoli di integrità referenziale fra

• attributo Vigile della relazione Infrazioni e la relazione Vigili
• attributi Prov e Numero di Infrazioni e la relazione Auto

Vincoli su più attributi

Integrità referenziale e valori NULL

Azioni compensative

Viene eliminata una $n$-upla causando una violazione

• Comportamento “standard”:
  • Rifiuto dell’operazione
• Azioni compensative
  • Eliminazione in cascata
  • Introduzione di valori nulli

Eliminazione in cascata

Vincoli multipli su più attributi

SQL

Benefici SQL

• Indipendenza dai venditori di HW e SW
• Portabilità attraverso varia piattaforme HW
• Coperto da standard internazionali SQL1, SQL2 e SQL3
• Strategico per IBM, Oracle, Microsoft, …
• Linguaggio per data base relazionali (unico)
• Strutturato ad alto livello (English-like)

Benefici SQL

• Linguaggio programmazione (Statico/Dinamico/API)
• In grado di fornire viste diverse del data base
• Linguaggio completo (IF, triggers, …) con T-SQL e PL-SQL
• Definizione dinamica dei dati
• Client/Server

SQL Standard?

In realtà ogni motore fa un po’ come vuole

http://troels.arvin.dk/db/rdbms/

Portabilità: davvero?

Non si può fare tutto

• codici di errore non standard
• tipi di dati non sempre supportati
• tabelle di sistema non sono uguali
• definisce solo linguaggio statico, non dinamico
• sorting

SQL basics

Data Definition Language (DDL)

CREATE/DROP/ALTER TABLE/VIEW/INDEX

Data Manipulation Language (DML)

SELECT - INSERT - DELETE - UPDATE

SQL basics

Data Control Language (DCL)

GRANT - REVOKE

Transaction Control Language (TCL o T-SQL)

COMMIT - ROLLBACK

Programming Language (PL)

DECLARE - OPEN - FETCH - CLOSE

Elencare i database

SHOW DATABASES;

Ritorna l’elenco dei Database presenti nel DBMS

I comandi possono occupare anche più righe e terminano con il ;

Creare un DataBase

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à

Eliminare un DataBase

DROP DATABASE [IF EXISTS] nomeDataBase;
    

usiamo [...] per indicare le parti opzionali dei comandi.

Selezionare un DataBase

USE nomeDataBase;
    

Tutti i comandi ora saranno riferiti a questo DB.

Definizione di dati

Istruzione CREATE TABLE;

• definisce uno schema di relazione e ne crea un’istanza vuota

• specifica attributi, domini e vincoli

  CREATE TABLE [IF NOT EXISTS] nomeTabella(
          nomeAttributo1 tipo,
          attributo2 tipo,
          ...
          attributoN tipo
      )
      

Domini - Numeri Interi

INT(N): suggeriamo al motore di usare N caratteri per mostrare il dato; es: INT(11)

Domini - Numeri razionali

Virgola mobile

• float - 4 bytes
• double - 8 bytes

Virgola fissa

• numeric(i,n) salva esattamente n cifre decimali
• decimal(i,n) salva almeno n cifre decimali

Domini - Testo

Salvati in tabella

Domini - Generico

SalvatAGGIO DEDICATO

Domini - Testo

SalvatAGGIO DEDICATO

Domini - Tempo

• 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 solo

Domini - Spazio

… e molti altri

Domini - Stringhe

Sto salvando testo o sequenze di byte?

• testo: devo convertire la stringa in sequenza di byte (charset)
• sequenza e basta: posso salvarla così com’è

Domini - Stringhe

Dimensione fissa o variabile?

• fissa: devo indicare una dimensione (max 255)
• variabile: occupa lunghezza + 1; posso indicare una lunghezza massima

Domini - Stringhe

Domini - Stringhe

Dove salvo il dato?

• nella tabella: più rapido accedere al dato per interrogazioni
• storage dedicato: anche se ho molti dati la tabella resta piccola

Studenti ed esami

Creare una tabella

    CREATE TABLE Studenti(
        matricola int(11),
        cognome varchar(45),
        nome varchar(45)
    );
    

Cancellare una tabella

DROP TABLE [IF EXISTS] nomeTabella;
    

… intuitivo

DROP TABLE [IF EXISTS] nomeTabella1,
        nomeTabella2,
        nomeTabella3,
        ...;
    

Vincoli

Posso definire dei vincoli:

• PRIMARY KEY - chiave primaria (una sola, implica NOT NULL)
• NOT NULL
• UNIQUE - definisce chiavi
• CHECK - vedremo più avanti

Vincoli - Chiave primaria

    CREATE TABLE Studenti(
        matricola int(11) PRIMARY KEY,
        cognome varchar(45),
        nome varchar(45)
    );
    

Vincoli - Chiave primaria

    CREATE TABLE Studenti(
        matricola int(11),
        cognome varchar(45),
        nome varchar(45),
        PRIMARY KEY (matricola)
    );
    

Vincoli - proibire i NULL

    CREATE TABLE Studenti(
        matricola int(11) PRIMARY KEY,
        cognome varchar(45) NOT NULL,
        nome varchar(45) NOT NULL
    );
    

Vincoli - proibire i NULL

    CREATE TABLE Corsi(
        codice int(11) PRIMARY KEY,
        titolo varchar(45) NOT NULL,
        docente varchar(45)
    );
    

Vincoli - chiavi composte

    CREATE TABLE Esami(
        studente int(11) PRIMARY KEY,
        voto smallint NOT NULL,
        lode bool,
        corso int(11) PRIMARY KEY
    );
    

Vincoli - chiavi composte

    CREATE TABLE Esami(
        studente int(11),
        voto smallint NOT NULL,
        lode bool,
        corso int(11),
        PRIMARY KEY (studente, corso)
    );
    

Not NULL + unique = Primary key

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

UNIQUE e NULL MULTIPLI

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

UNIQUE su più colonne

CREATE TABLE nomeTabella (
        id int(11) PRIMARY KEY,
        campo1 int(19),
        campo2 int(12),
        CONSTRAINT [nome] UNIQUE(campo1, campo2)
    );
    

Auto Increment

• il motore si occupa di incrementare il contatore numerico
• identifico in modo chiaro una $n$-upla

• ottimo come chiave primaria!

  CREATE TABLE Studenti(
          matricola int(11) PRIMARY KEY AUTO_INCREMENT,
          cognome varchar(45),
          nome varchar(45)
      );
      

CHECK

Serve per specificare vincoli complessi

Netto = Lordo - Trattenute

• non supportato in MySql
• MySql IGNORA il comando dato alla creazione della tabella

AUTO_INCREMENT: dettagli

cosa succede se cancello una riga?

• non riciclo!
• successivo = inserito automaticamente +1

AUTO_INCREMENT: dettagli

cosa succede se imposto un valore?

• se non è duplicato viene accettato
• successivo = inserito manualmente +1

AUTO_INCREMENT: dettagli

cosa succede se modifico un valore?

• se non è duplicato viene accettato
• successivo = inserito automaticamente +1

Valori predefiniti

CREATE TABLE nome(
        nomeAttributo tipo DEFAULT valore
    );
    

Valori predefiniti

CREATE TABLE Corsi(
        codice int(11) PRIMARY KEY,
        titolo varchar(45) NOT NULL DEFAULT "nuovo",
        docente varchar(45)
    );
    

Commenti

CREATE TABLE nome(
        nomeAttributo tipo COMMENT "commento"
    );
    

Commenti

CREATE TABLE Corsi(
        codice int(11) PRIMARY KEY,
        titolo varchar(45) NOT NULL
        COMMENT "Titolo del corso",
        docente varchar(45)
    );
    

Chiavi primarie e null

Modulo, Versione e Tipo sono una PK?

• identificano $n$-upla
• non contengono altre superchiavi
• non nulle

Chiavi primarie e null

• NULL = assenza di informazioni
• '' = informazione nota, pari a VUOTO
• NULL è uguale a NULL?

Chiavi primarie e null

Vincoli Interrelazionali

• FOREIGN KEY e REFERENCES e permettono di definire vincoli di integrità referenziale
• di nuovo due sintassi
  • per singoli attributi (non in MySql)
  • su più attributi
• è possibile definire azioni compensative

FOREIGN KEY

colonne che sono FK

REFERENCES

colonne nella relazione (tabella) esterna

Studenti ed esami

Vincoli Interrelazionali

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

Definizione compatta

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

Vincoli Interrelazionali

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

Vincoli Interrelazionali con nome

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

Disattivare i vincoli interrelazionali

• sto caricando i dati: ordine importante, altrimenti errore
• voglio disattivare temporaneamente i vincoli
• disattivazione
  • SET foreign_key_checks = 0
• riattivazione
  • SET foreign_key_checks = 1

Integrità referenziale

Integrità referenziale

    CREATE TABLE Vigili(
        matricola int(11) PRIMARY KEY AUTO_INCREMENT,
        cognome varchar(45) NOT NULL,
        nome varchar(45) NOT NULL
    );
    

Integrità referenziale

    CREATE TABLE Automobile(
        prov char(2),
        targa char(6),
        cognome varchar(45) NOT NULL,
        nome varchar(45) NOT NULL,
        PRIMARY KEY (prov, targa)
    );
    

Integrità referenziale

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

Cancellazione

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

Cambiare lo schema

Cambiare lo schema

• DROP tabella
• ricreare tabella
• … perdo tutti i dati

Modificare tabelle

ALTER TABLE nomeTabella
    azione1
    [, azione2, ...]
 

• aggiungere/togliere colonne
• cambiare il tipo di dato
• rinominare la tabella
• definire chiavi primarie, esterne, ecc..

Aggiungere colonne

ALTER TABLE nomeTabella
 ADD COLUMN definizioneColonna
 [ FIRST | AFTER nomeColonna ]
 

Aggiungere colonne

ALTER TABLE Infrazioni
    ADD COLUMN dataModifica TIMESTAMP
    AFTER data
 

Rimuovere colonne

ALTER TABLE nomeTabella
    DROP COLUMN nomeColonna
 

Rimuovere colonne

ALTER TABLE infrazioni
    DROP COLUMN dataModifica
 

Modificare colonne

ALTER TABLE nomeTabella
    CHANGE COLUMN nomeOriginale
    nomeNuovo tipo
 

Modificare colonne

ALTER TABLE infrazioni
    CHANGE COLUMN dataModifica
    dataUltimaModifica TIMESTAMP
 

Modificare colonne

ALTER TABLE automobili
    CHANGE COLUMN cognome
    VARCHAR(100)
 

Rinominare tabelle

ALTER TABLE nomeTabella
    RENAME TO nuovoNome
 

Rinominare tabelle

ALTER TABLE infrazioni
    RENAME TO odiateInfrazioni

 

Aggiungere/Rimuovere Foreign Key

ALTER TABLE nomeTabella
    ADD CONSTRAINT nome
    FOREIGN KEY (...)
    REFERENCES tabella(...)
 

ALTER TABLE nomeTabella
    DROP FOREIGN KEY nome
 

DataBase di prova

classicmodels

vendita modellini

https://www.mysqltutorial.org/getting-started-with-mysql/mysql-sample-database/

DataBase di prova

classicmodels

• clienti
• dipendenti
• uffici di vendita
• prodotti
• ordini
• pagamenti

DataBase di prova

classicmodels

• customers: dati dei clienti
• products: modellini disponibili
• productlines: linee di prodotti
  • auto classiche, moto, navi, treni
• orders: ordini fatti dai clienti

DataBase di prova

classicmodels

• orderdetails: dettagli di ogni ordine dei clienti
  • linee dell’ordine
• payments: pagamenti fatti dai clienti
• employees: informazioni sui dipendenti
  • chi è il capo di chi, telefoni, ecc
• offices: uffici di vendita

Elencare elementi della tabella

Elencare tutti i modellini in vendita

SELECT * FROM products;
 

Istruzione select (base)

SELECT attributo1 [, attributo2, ...]
 FROM tabella1 [, tabella2, ...]
 [WHERE condizione]
 

• * = tutti gli attributi
• FROM = da dove (per ora)
• WHERE = quali ennuple

Tutto è una tabella

• selezione: WHERE
  • selezione una “sottotabella”
• proiezione: elenco attributi
  • mostro una tabella scegliendo quali colonne mostrare

Selezione

Mostra i modellini che costano meno di 75$

SELECT * FROM  products WHERE MSRP < 75;
 

Selezione e proiezione

Mostra nome, prezzo di acquisto e di vendita dei modellini che costano meno di 75$

SELECT productName, buyPrice, MSRP
 FROM products
 WHERE MSRP < 75;
 

Rinominare attributi

istruzione AS

SELECT productName AS nomeProdotto,
 productVendor AS nomeVenditore
 FROM products;
 

SELECT, abbreviazioni

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, abbreviazioni

SELECT * FROM products;
 

in realtà stiamo scrivendo

SELECT productCode, productName, productLine,
 productScale, productVendor,
 productDescription, quantityInStock,
 buyPrice, MSRP
 FROM products;
 

SELECT, abbreviazioni

SELECT * FROM products;
 

in realtà stiamo scrivendo

SELECT productCode, productName, productLine,
 productScale, productVendor,
 productDescription, quantityInStock,
 buyPrice, MSRP
 FROM products
 WHERE true;
 

Condizioni: testo esatto

Mostra tutti i dipendenti di nome Leslie

SELECT * FROM employees
 WHERE firstName = 'Leslie';
 

Virgolette singole o doppie?

• "Leslie" o 'Leslie'?
  • indifferente!
• standard ANSI: 'Leslie'

Virgolette singole o doppie?

per inserire ‘ in una stringa? Es: Ci'ao

• delimitata da " ": "Ci'ao"
• delimitata da ' ': raddoppio → 'Ci''ao'

per inserire ‘ in una stringa? Es: Ci"'"ao

• delimitata da " ": raddoppio → "Ci""ao"
• delimitata da ' ': 'Ci"ao'

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui cognome finisce per “son”.

SELECT * FROM employees
 WHERE lastName LIKE '%son';
 

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui cognome NON finisce per “son”.

SELECT * FROM employees
 WHERE lastName NOT LIKE '%son';
 

Condizioni: testo incompleto

• % = zero o più caratteri
• _ = esattamente un carattere
• per cercare il carattere % uso \%
• per cercare il carattere _ uso \_

Sito per test SQL

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui nome finisce per "arry" e davanti ha una sola lettera

SELECT * FROM employees
 WHERE firstName LIKE '_arry';
 

Condizioni: testo incompleto

Mostra tutti i prodotti che hanno una scala divisibile per 10 e minore di 100 (es: 1:10, 1:20, 1:30, ...)

SELECT * FROM products
 WHERE productScale LIKE '1:_0';
 

Condizioni: testo incompleto

Mostra tutti i dipendenti il cui nome inizia con M e la cui terza lettera è una r

SELECT * FROM employees
 WHERE firstName LIKE 'M_r%';
 

Più condizioni

Mostra i modellini che costano meno di 75 e che abbiamo comprato a più di 30

SELECT productName, MSRP, buyPrice
 FROM products
 WHERE MSRP < 75 AND buyPrice > 30;
 

Più condizioni

Mostra i modellini che costano meno di 75 o più di 150

SELECT productName, MSRP
 FROM products
 WHERE MSRP < 75 OR MSRP > 150;
 

Più condizioni

Mostra i modellini che costano meno di 75 o più di 150 e che comunque abbiamo comprato a più di 30

SELECT productName, MSRP, buyPrice
 FROM products
 WHERE (MSRP<75 OR MSRP>150) AND buyPrice>30;
 

Intervalli

Seleziona i valori compresi tra x e y (inclusi)

SELECT ... FROM ...
 WHERE colonna BETWEEN x AND y;
 

Intervalli

Mostra i pagamenti con importi compresi tra 5.000 ed 8.000

SELECT * FROM payments
 WHERE amount BETWEEN 5000 AND 8000;
 

Intervalli

Mostra i pagamenti con importi compresi tra 5.000 ed 8.000

SELECT * FROM payments
 WHERE amount BETWEEN 5000 AND 8000;
 

forma equivalente

SELECT * FROM payments
 WHERE amount >= 5000 AND
 amount <= 8000;
 

Intervalli

Prendi tutti i dipendenti il cui nome inizia con una lettera tra B ed F

SELECT * FROM employees
 WHERE firstName BETWEEN 'B' AND 'F';
 

Liste

Controlla se il valore è presente in una lista di valori

SELECT ... FROM ...
 WHERE colonna IN (val1, val2, ...)
 

Liste

Mostra codice ufficio, città e numero di telefono degli uffici in Francia o America

SELECT officeCode, city, phone
 FROM offices
 WHERE country IN ('USA', 'FRANCE');
 

Liste

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';
 

Liste

Mostrare i modellini del tipo "Planes", "Ships" o "Classic Cars"

SELECT * FROM products
 WHERE productLine
 IN ('Planes','Ships','Classic Cars');
 

Gestire i NULL

SELECT ... FROM ...
 WHERE colonna = "";
 

NO

Gestire i NULL

SELECT ... FROM ...
 WHERE colonna IS NULL;
 

Gestire i NULL

Mostra gli ordini non spediti

SELECT * FROM orders
 WHERE shippedDate IS NULL;
 

Gestire i NULL

Mostra gli ordini creati dopo il 30/04/2005 e non spediti

SELECT * FROM orders WHERE
 orderDate > '2005-04-30' AND
 shippedDate IS NULL;
 

Espressioni

Mostra i prezzi di vendita senza l’IVA (prezzo / 1.22)

SELECT productName, MSRP/1.22 AS noIVA
 FROM products;
 

Espressioni

Mostra i prodotti con un margine (prezzo - prezzo acquisto) superiore a 50

SELECT productName, MSRP, buyPrice
 FROM products
 WHERE MSRP-buyPrice > 50;
 

Funzioni

Stringhe

• length()
• reverse()
• right()
• trim()
• …

Funzioni

Mostra i prodotti con nomi di almeno 15 caratteri.

SELECT productName, length(productName)
 FROM products
 WHERE length(productName) >= 15;
 

Funzioni

Data e ora

• day()
• year()
• now()
• month()
• monthname()
• …

Funzioni

Mostra i prodotti orinati nel mese di gennaio

SELECT * from orders
 WHERE month(orderDate) = 1;
 

Ordinamento

Stabilire l’ordine di presentazione dei risultati

SELECT ... FROM ... WHERE ...
 ORDER BY col1 [ASC|DESC], col2 [ASC|DESC], ...
 

• ASC: crescente → DEFAULT!
• DESC: decrescente

Ordinamento

Mostra i modellini ordinandoli per prezzo di vendita crescente

SELECT productName, MSRP
 FROM products
 ORDER BY MSRP;
 

Ordinamento

Mostra i clienti ordinandoli per paese crescente e credito massimo decrescente

SELECT customerName, country, creditLimit
 FROM customers
 ORDER BY country, creditLimit DESC;
 

Ordinamento

Mostra gli ordini ordinandoli in base allo status in cui si trovano (in corso, in attesa, cancellati, ecc).

SELECT * FROM orders
 order by status;
 

NO

Ordinamento

Mostra gli ordini ordinandoli in base allo status in cui si trovano (in corso, in attesa, cancellati, ecc)

FIELD(text, str1, str2, str3, ...)
 

Ritorna la posizione della stringa text nella lista str1, str2, str3, …

Ordinamento

Mostra gli ordini ordinandoli in base allo status in cui si trovano (in corso, in attesa, cancellati, ecc).

SELECT * FROM orders
 ORDER BY FIELD(status, 'In Process',
 'On Hold', 'Cancelled', 'Resolved',
 'Disputed','Shipped');
 

Uniamo il tutto...

Mostra i prodotti venduti a meno di 100€, mettendo in cima quelli con il margine più alto

SELECT productName,
 MSRP-buyPrice as margine
 FROM products
 WHERE MSRP < 100
 ORDER BY msrp-buyPrice DESC
 

Righe Duplicate

Ogni tanto le nostre query ritornano righe duplicate: come facciamo ad eliminare i doppioni?

SELECT DISTINCT ... FROM ...
 

Righe Duplicate

Mostra tutte le città in cui si trovano i miei clienti, ordinandole alfabeticamente

SELECT DISTINCT city FROM customers
 order by city;
 

Decodificare le relazioni

Prodotto cartesiano

Creo il prodotto cartesiano di più tabelle

SELECT ... FROM tabella1, tabella2, ...
 

Risultato: una riga per ogni combinazione di valori tra le righe di tabella1 e di tabella2

Prodotto cartesiano

Crea il prodotto cartesiano tra la tabella degli

impiegati e quella dei clienti

SELECT * FROM customers, employees;
 

Cross Join

Crea il prodotto cartesiano tra la tabella degli

impiegati e quella dei clienti

SELECT * FROM customers, employees;
 

Forma esplicita:

SELECT * FROM customers CROSS JOIN employees;
 

Prodotto Cartesiano Filtrato

Non voglio vedere tutte le combinazioni, solo filtrare tabella!

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

Prodotto Cartesiano Filtrato

Mostra per ogni cliente il nome del venditore associato

SELECT customerName, salesRepEmployeeNumber,
 lastName, employeeNumber
 FROM customers, employees
 WHERE salesRepEmployeeNumber = employeeNumber
 

Inner Join

Modo migliore per scrivere il tutto

SELECT ... FROM tabella1
 INNER JOIN tabella2
 ON PK = FK
 

Inner Join

Mostra per ogni cliente il nome del venditore associato

SELECT customerName, salesRepEmployeeNumber,
 lastName, employeeNumber
 FROM customers
 INNER JOIN employees
 ON salesRepEmployeeNumber = employeeNumber;
 

Inner Join: ambiguità

Ogni tanto PK e FK hanno stesso nome

SELECT ... FROM tabella1
 INNER JOIN tabella2
 ON tabella2.PK = tabella1.FK
 

Inner Join: ambiguità

Mostra per ogni prodotto la descrizione della linea di prodotti cui appartiene

SELECT productCode, productName, textDescription
 FROM products INNER JOIN productlines
 ON products.productline =
 productlines.productline;
 

Inner Join: ambiguità

SELECT productCode, productName, textDescription
 FROM products INNER JOIN productlines
 ON products.productline = productlines.productline;
 

Inner Join: ambiguità

Forma equivalente

SELECT productCode, productName, textDescription
 FROM products p1
 INNER JOIN productlines p2
 ON p1.productline = p2.productline;
 

Inner Join

Mostra per ogni prodotto la descrizione della linea di prodotti cui appartiene

SELECT productCode, productName, textDescription
 FROM products INNER JOIN productlines
 ON products.productline =
 productlines.productline;
 

Criteri di Join

Se gli attributi hanno lo stesso nome tra le relazioni, si può usare una forma abbreviata

SELECT productCode, productName,
 textDescription
 FROM products INNER JOIN productlines
 USING(productline);
 

Criteri di Join

Mostra tutti gli impiegati e la città in cui si trova l’ufficio cui afferiscono

SELECT firstName, lastName, city
 FROM employees INNER JOIN offices
 USING (officeCode);
 

Criteri di Join

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
 

pericolose!

Cosa succede se aggiungo/cambio colonne?

Criteri di Join

Mostra per ogni prodotto la descrizione della linea di prodotti cui appartiene

SELECT productCode, productName,
 textDescription
 FROM products NATURAL JOIN productlines;
 

Problema

• Voglio vedere i clienti ✓
• Voglio vedere il nome dei venditori assegnati ✓
• Voglio vedere anche i clienti senza venditore assegnato (?)

Left Outer Join

Mostra tutti i dati della prima tabella, e se possibile associa le informazioni della seconda

SELECT ... FROM tabella1
 LEFT OUTER JOIN tabella2
 ON PK = FK
 

Left Outer Join

Mostra tutti i clienti; se il cliente ha un venditore associato, mostrane i dati

SELECT customerName, concat(firstName,' ',lastName)
 FROM customers LEFT OUTER JOIN employees
 ON salesRepEmployeeNumber = employeeNumber
 

Left Outer Join

Mostra tutti i clienti; se il cliente ha un venditore associato, mostrane i dati

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 vs Left Outer Join

Inner

Left Outer

Left Outer Join

Mostra tutti i clienti ed i relativi ordini, inclusi i clienti che non hanno fatto ordini

SELECT c.customerNumber, c.customerName,
 o.orderNumber, o.status
 FROM customers c LEFT JOIN orders o
 ON c.customerNumber = o.customerNumber;
 

Left Outer Join

Mostra tutti i clienti che non hanno ordini

SELECT c.customerNumber, c.customerName,
 orderNumber, o.status
 FROM customers c LEFT JOIN orders o
 ON c.customerNumber = o.customerNumber
 WHERE orderNumber is NULL
 

Right Outer Join

Mostra tutti i dati della SECONDA tabella, e se possibile associa le informazioni della prima

SELECT ... FROM tabella1
 RIGHT OUTER JOIN tabella2
 ON PK = FK
 

Inner vs Left vs Right Outer Join

Inner

Left Outer

Right Outer

Right Outer Join

Mostra tutti i clienti ed i relativi ordini, inclusi i clienti che non hanno fatto ordini

SELECT c.customerNumber, c.customerName,
 orderNumber, o.status
 FROM orders o RIGHT JOIN customers c
 ON c.customerNumber = o.customerNumber
 

Join Multiple

Decodificare il contenuto di più tabelle in una sola query

SELECT ... FROM tabella1
     [INNER|LEFT|RIGHT]JOIN tabella2
     ON PK = FK
     [INNER|LEFT|RIGHT]JOIN tabella3
     ON PK = FK
     

Join Multiple

Mostra tutti i clienti, il nome dell’impiegato associato ed il numero di telefono dell’ufficio

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)
     

Join Multiple

Stampare ogni riga dell’ordine, indicando il nome del cliente, numero d’ordine ed il nome del prodotto ordinato

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;
     

Join multiple

Inner + Inner

Inner + Left Outer

Cross Join

Inner Join

Inner Join e NULL

Left Outer Join

Join Multiple

Full Outer Join

Mostra tutti i dati della prima tabella, e se possibile associa le informazioni della seconda.

Mostra comunque tutti i dati della seconda tabella.

SELECT ... FROM tabella1
     FULL OUTER JOIN tabella2
     ON PK = FK
     

Join Multiple

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 ;

Join Multiple

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 ;

Join Multiple

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;

Self join

Join di una tabella con se stessa

SELECT ... FROM tabella1
     [LEFT|RIGHT|INNER] JOIN tabella1
     ON PK = FK
     

• avrò sicuramente nomi di attributi duplicati
• dovrò introdurre degli alias

Self Join

Self Join

Mostra tutti i dipendenti ed il nome del loro capo

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;
     

Self Join

Mostra tutte le coppie di clienti che abitano nella stessa città

SELECT c1.city, c1.customerName,
     c2.customerName
     FROM customers c1 INNER JOIN customers c2
     ON c1.city = c2.city;
     

NO

Self Join

Mostra tutte le coppie di clienti che abitano nella stessa città

SELECT c1.city, c1.customerName,c2.customerName
     FROM customers c1 INNER JOIN customers c2
     ON c1.city = c2.city AND
     c1.customername <> c2.customerName
     

Self Join

Mostra tutte le coppie di clienti che abitano nella stessa città

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
     

Union join

Unisce i risultati di più query

SELECT ... FROM ...
 UNION [DISTINCT | ALL]
 SELECT ... FROM ...
 [UNION [DISTINCT | ALL]
 SELECT ... FROM ...]
 

ATTENZIONE!

• stesso numero di attributi
• attributi omogenei

UNION

SELECT ... FROM ...
 UNION [DISTINCT | ALL]
 SELECT ... FROM ...
 [UNION [DISTINCT | ALL]
 SELECT ... FROM ...]
 

• DISTINCT: default, elimina duplicati
• ALL: se specificato, NON elimina duplicati

UNION

Mostra l’identificativo ed il nome di tutti gli impiegati e di tutti i clienti

SELECT customerNumber AS id,contactLastname AS name
 FROM customers
 UNION
 SELECT employeeNumber AS id, firstname AS name
 FROM employees;
 

UNION

Mostra l’identificativo ed il nome di tutti gli impiegati e di tutti i clienti

SELECT customerNumber,contactLastname
 FROM customers
 UNION
 SELECT employeeNumber, firstname
 FROM employees;
 

• Se non specifico l’alias prende i nomi della prima query

UNION

Mostra l’id ed il nome di tutti gli impiegati e di tutti i clienti, scrivendo per ognuno cosa sia

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;
 

UNION e ordinamento

E se volessi ordinare i risultati?

SELECT ... FROM ...
 UNION [DISTINCT | ALL]
 SELECT ... FROM ...
 ORDER BY criteri
 

ATTENZIONE!

• l’ultima riga è riferita al RISULTATO della union, non all’ultima query
• se si inserisce la clausola all’interno della singola query verrà ignorata

UNION e ordinamento

Posso usare le parentesi per fare ordine

(SELECT ... FROM ...)
 UNION [DISTINCT | ALL]
 (SELECT ... FROM ...)
 ORDER BY criteri
 

Union ed ordinamento

Mostrare id e nome di clienti ed impiegati ordinandoli per nome

(SELECT customerNumber AS id,
 contactLastname AS name
 FROM customers)
 UNION
 (SELECT employeeNumber AS id, firstname AS name
 FROM employees)
 ORDER BY name;
 

Union ed ordinamento

Mostrare i paesi in cui c’è un ufficio o un cliente, ordinati per nome

SELECT country FROM offices
 UNION
 SELECT country FROM customers
 ORDER BY country;
 

Intersect

Restituisce l’intersezione di più query

SELECT ... FROM ...
 INTERSECT
 SELECT ... FROM ...
 INTERSECT
 SELECT ... FROM ...
 

Non c’è in MySql, servono query nidificate

Raggruppare i dati

Voglio raggruppare le ennuple in sottogruppi in base ad uno o più valori

SELECT a1 , a2 ,...,an
FROM tabella1 WHERE condizioni
GROUP BY a1, a2,...,an

Raggruppare i dati

Mostra tutti gli stati degli ordini esistenti

SELECT status
FROM orders
GROUP BY status

Raggruppare i dati

Mostra tutti gli stati degli ordini fatti prima del 31/12/2003

SELECT status
FROM orders
WHERE orderDate < "2003-12-31"
GROUP BY status;

Raggruppare i dati

Mostra tutti gli stati degli ordini fatti prima del 31/12/2003

SELECT status
FROM orders
WHERE orderDate < "2003-12-31"
GROUP BY status;

forma equivalente

SELECT DISTINCT status
FROM orders
WHERE orderDate < "2003-12-31"

Funzioni di aggregazione

Permettono di effettuare calcoli su tutti i valori che l’attributo assume nella query eseguita

SELECT a1, a2, ..., an, aggregatore(ax)
FROM tabella1 WHERE condizioni

Esempio:

• quanti ordini sono stati fatti?
• quanto costa in media un prodotto?

Funzioni di aggregazione

SELECT a1, a2, ..., an, aggregatore(ax)
FROM tabella1 WHERE condizioni

Aggregatori:

• COUNT: conta il numero di valori presenti
• SUM: somma dei valori
• AVG: media dei valori
• MAX/MIN: massimo e minimo

Funzioni di aggregazione

Quanti dipendenti ci sono in azienda?

SELECT count(*)
FROM employees;

Funzioni di aggregazione - NULL

Differenza fra

SELECT count(*)
FROM employees

e

SELECT count(reportsTo)
FROM employees

Funzioni di aggregazione - DISTINCT

Quanti capi ci sono in azienda?

SELECT count( distinct reportsTo)
FROM employees;

Funzioni di aggregazione

Quanti pagamenti ho ricevuto?

SELECT count(*)
FROM payments;

Funzioni di aggregazione

Quanti soldi ho ricevuto con i pagamenti?

SELECT SUM(amount)
FROM payments;

Funzioni di aggregazione

Qual è il prezzo medio di vendita di un prodotto?

SELECT avg(MSRP)
FROM products;

Funzioni di aggregazione

Qual è il prezzo medio di vendita di un prodotto? Quale il massimo? Quale il minimo?

SELECT avg(MSRP), max(MSRP), min(MSRP)
FROM products;

Funzioni di aggregazione ERRATE

Attenzione a non chiedere cose assurde

SELECT avg(MSRP), productName
FROM products

• quale productName devo mostrare?
• standard ANSI: errore
• MySql risponde… con il primo valore!

Funzioni di aggregazione e raggruppamenti

Voglio raggruppare le ennuple in sottogruppi in base ad uno o più valori, mostrando anche valori calcolati su ogni gruppo

SELECT a1, a2 , ... , an, aggregatore(ax)
FROM tabella1 WHERE condizioni
GROUP BY a1, a2, ... ,an

Funzioni di aggregazione e raggruppamenti

Mostra gli stati degli ordini e quanti ordini si trovano in ciascuno stato

SELECT status, count(*)
FROM orders
GROUP BY status;

Funzioni di aggregazione e raggruppamenti

Mostra quanti prodotti ho per ogni categoria ed il prezzo medio di vendita

SELECT productLine, count(*), avg(MSRP)
FROM products
GROUP BY productLine;

Aggreghiamo...

Mostrare quanti ordini ho spedito ogni giorno

SELECT count(*), shippedDate
FROM orders
GROUP BY shippedDate

Aggreghiamo...

Mostrare quanti ordini ho spedito nei vari mesi (una riga per mese)

• PostgreSQL: usate date_part('month', attributo)

SELECT count(*), month(shippedDate)
FROM orders
WHERE shippedDate IS NOT NULL
GROUP BY month(shippedDate);

Aggreghiamo...

Mostrare quanti ordini ho spedito nei vari mesi (una riga per mese ed anno)

• PostgreSQL (mese): usate date_part('month', attributo)
• PostgreSQL (anno): usate date_part('year', attributo)

SELECT count(*), month(shippedDate),
year(shippedDate)
FROM orders
WHERE shippedDate IS NOT NULL
GROUP BY year(shippedDate), month(shippedDate);

Aggreghiamo…

Mostrare per ogni ordine: il nome del cliente, la data dell’ordine ed il totale dell’ordine

Un passo alla volta:

• calcoliamo il totale di ogni ordine
• estraiamo i dati dalle altre tabelle

Aggreghiamo...

Mostrare per ogni ordine: il totale dell’ordine

SELECT orderNumber,
sum(quantityOrdered*priceEach)
FROM orderdetails
GROUP BY orderNumber;

Aggreghiamo...

Mostrare per ogni ordine: il nome del cliente, la data dell’ordine ed il totale dell’ordine

SELECT customerName, orderDate,
sum(quantityOrdered*priceEach)
FROM orderdetails
INNER JOIN orders USING (orderNumber)
INNER JOIN customers USING (customerNumber)
GROUP BY orderNumber;

Aggreghiamo...

Mostrare quanti ordini ha fatto ogni cliente, mettendo in cima quelli più assidui

SELECT count(*) nOrdini, customerNumber
FROM orders
GROUP BY customerNumber
ORDER BY nOrdini DESC;

Aggreghiamo…

Mostrare l’estratto conto del cliente 124

• pagamenti cliente: importo negativo
• debiti cliente: importo positivo

Un passo alla volta:

1. estrarre i pagamenti fatti con la relativa data
2. prendere i totali degli ordini del cliente
3. unire i due risultati, ordinandoli per data

Aggreghiamo...

Mostrare l’estratto conto del cliente 124

estrarre i pagamenti fatti con la relativa data

SELECT amount*-1, paymentDate FROM payments
WHERE customerNumber = 124

Aggreghiamo...

Mostrare l’estratto conto del cliente 124

prendere i totali degli ordini del cliente

SELECT sum(quantityOrdered*priceEach),orderDate
FROM orderdetails INNER JOIN orders
USING (orderNumber)
WHERE customerNumber = 124
GROUP BY orderNumber;

Aggreghiamo...

Mostrare l’estratto conto del cliente 124

unire i due risultati, ordinandoli per data

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;

Filtrare dati aggregati

Come applicare un filtro al risultato di una funzione di aggregazione?

SELECT a1, a2, ... ,an, aggregatore(ax)
    FROM tabella1 WHERE condizioni
    GROUP BY a1, a2, ... ,an
    HAVING condizioniAggregate
    

Filtrare dati aggregati

Mostrare tutti gli ordini il cui totale è < 10.000

SELECT orderNumber,
    sum(quantityOrdered*priceEach) as tot
    FROM orderdetails
    GROUP BY orderNumber
    HAVING tot < 10000;
    

Filtrare dati aggregati

Mostrare tutti gli ordini il cui totale è < 10.000 e per i quali verranno spediti più di 100 pezzi

SELECT orderNumber,
    sum(quantityOrdered) as q,
    sum(quantityOrdered*priceEach) as tot
    FROM orderdetails
    GROUP BY orderNumber
    HAVING tot < 10000 AND q > 100;
    

Filtrare dati aggregati

Mostrare tutti gli ordini il cui totale è < 10.000 e che non sono stati spediti

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;
    

Filtrare dati aggregati

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;
    

Subquery

Posso annidiare le query una dentro l’altra

SELECT a1,a2,...,an,(QUERY singolo val.)
    FROM (QUERY)
    WHERE a1 > (QUERY singolo val.)
    AND a2 IN (QUERY singolo attrib.)
    

Per adesso:

• le subquery vivono di vita propria
• possiamo scriverle separatamente, poi incorporarle

Subquery - singolo valore

Mostrare per ogni articolo il prezzo di vendita ed il prezzo del prodotto più caro

Hint: subquery nella clausola SELECT

SELECT productName, MSRP,
    (SELECT max(MSRP)
    FROM products) as massimo
    FROM products;
    

Subquery - singolo valore

Mostrare i dati del pagamento più alto ricevuto

Hint: subquery nella clausola WHERE

SELECT customerNumber, checkNumber, amount
    FROM payments
    WHERE amount =
    (SELECT MAX(amount)
    FROM payments);
    

Subquery - singolo valore

Mostra i pagamenti superiori alla media

SELECT customerNumber, checkNumber, amount
    FROM payments
    WHERE amount >
    (SELECT AVG(amount)
    FROM payments);
    

Subquery - singolo valore

Mostrare i clienti che non hanno fatto ordini

Hint: subquery nella clausola WHERE ... NOT IN

SELECT customername
    FROM customers
    WHERE customerNumber NOT IN
    (SELECT DISTINCT customernumber
    FROM orders);
    

Subquery - singolo attributo

Mostrare i clienti che non hanno fatto ordini

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;
    

Subquery - FROM

Mostrare il numero massimo, minimo e medio di pezzi inseriti negli ordini

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;
    

Subquery Correlate

Finora:

• posso eseguire la subquery da sola
• il motore la esegue una volta
• … non è sempre così

Subquery Correlate

SELECT a1,a2,...,an
    FROM tab1 WHERE 
    a1 > (SELECT c1
    FROM tab2
    WHERE tab2.c2 > tab1.a1)
    

• non eseguibile “da sola”
• eseguita per ogni riga della query principale
• visibilità variabili: solo da query a subquery

Subquery Correlate

Mostrare i prodotti il cui prezzo di acquisto è superiore alla media della linea cui afferiscono

SELECT productname, buyprice
FROM products AS p
WHERE buyprice > (
SELECT AVG(buyprice)
FROM products
WHERE productline = p.productline);
    

EXISTS

Operatore booleano (per WHERE): ritorna vero se una sottoquery ha valori

SELECT a1,a2,...,an
FROM tab
WHERE EXISTS (QUERY singolo val.)
    

Limiti

Non voglio tutte le righe che soddisfano il filtro, solo le TOP N

SELECT a1,a2,...,an
FROM tab
WHERE ...
LIMIT numero
    

Limiti

Mostrare i primi 5 clienti (codice cliente, nome e limite di credito)

SELECT customernumber,
customername,
creditlimit
FROM customers
LIMIT 5;
    

Limiti

Mostrare i 5 clienti con il credito più elevato

SELECT customernumber, customername,
creditlimit
FROM customers
ORDER BY creditlimit DESC
LIMIT 5;
    

Limiti

Non voglio tutte le righe che soddisfano il filtro: solo le prime N a partire dalla riga X.

SELECT a1,a2,...,an
    FROM tab
    WHERE ...
    LIMIT X, N
    

• X → dalla riga Xesima dei risultati (si parte da 0)
• N → quante righe prendere

Limiti

Mostrare il secondo prodotto più costoso (buyprice) in listino

SELECT productName, buyprice
    FROM products
    ORDER BY buyprice DESC
    LIMIT 1, 1;
    

Aggiungere dati

Como possono inserire una nuova $n$-upla?

INSERT INTO tabella(col1, col2, ...)
    VALUES (valore1, valore2, ...)
    [, (valore1, valore2, ...), ...]
    

• se imposto TUTTI gli attributi della tabella, posso omettere i nomi delle colonne (col1, col2,…)
• attributi AUTO_INCREMENT: NULL

Aggiungere dati

Inserire un nuovo ufficio a Trieste

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

Aggiungere dati e subquery

Vorrei usare una subquery per alimentare l’inserimento dei dati

INSERT INTO tabella(col1, col2, ...)
    SELECT ...
    

Il risultato della SELECT deve fornire:

• lo stesso numero di attributi della tabella di destinazione
• attributi dello stesso dominio di destinazione

Aggiungere dati

Duplicare l’ordine 10425

1. creare l’ordine 10426 (a mano, tabella orders)
2. prendere tutte le righe di 10425 (orderdetails)
3. inserire tutte queste $n$-uple nella tabella (orderdetails)

Aggiungere dati

Duplicare l'ordine 10425

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

Aggiungere dati

Duplicare l'ordine 10425

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;
    

Aggiungere dati

Duplicare l'ordine 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;
    

Modificare dati

Come posso modificare $n$-uple esistenti?

UPDATE tabella
SET col1 = valore1
[, col2 = val2...]
[WHERE condizione]
    

• ogni campo può assumere un valore esplicito, o il risultato di una funzione, sottoquery, ecc…
• se non uso la clausola WHERE, aggiorno tutte le $n$-uple della tabella

Modificare dati

Cambiare indirizzo email a Mary Patterson

Impiegato 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;
    

Modificare dati

Cambiare prezzo di acquisto e vendita della '2001 Ferrari Enzo'

Hint: UPDATE tabella SET col1 = valore1 WHERE condizione

UPDATE products
    SET msrp = 500, buyprice = 200
    WHERE productName = '2001 Ferrari Enzo';
    

Modificare dati

Aumentare del 5% tutti i prezzi di vendita

Hint: UPDATE tabella SET col1 = valore1 WHERE condizione

UPDATE products
    SET msrp = msrp*1.05;
    

Modificare dati

Associare ai clienti senza venditore l’agente con matricola più alta (appena arrivato)

1. individuare i clienti senza venditore
2. trovare l’agente con matricola più alta
3. aggiornare i dati

Modificare dati

Associare ai clienti senza venditore l’agente con matricola più alta

Individuare i clienti senza venditore

SELECT customerNumber
    FROM customers
    WHERE salesRepEmployeeNumber IS NULL;
    

Modificare dati

Associare ai clienti senza venditore l’agente con matricola più alta

Trovare l'agente con matricola più alta

Hint: venditore ha `jobTitle = 'Sales Rep'`

SELECT max(employeeNumber)
    FROM employees
    WHERE jobTitle = 'Sales Rep';
    

Modificare dati

Associare ai clienti senza venditore l’agente con matricola più alta

Aggiornare i dati

UPDATE customers
    SET salesRepEmployeeNumber =
    (SELECT max(employeeNumber)
    FROM employees
    WHERE jobTitle = 'Sales Rep')
    WHERE salesRepEmployeeNumber IS NULL;
    

Attenzione

UPDATE tabella
    SET col1 = col1 +1, col2 = col1
    

• MySql: la seconda operazione è fatta con il valore aggiornato di col1
• SQL standard: la seconda operazione è fatta con il valore originale di col1

Eliminare dati

Come posso eliminare dati dalla tabella?

DELETE FROM tabella
[WHERE condizioni]

oppure

DELETE FROM tabella
[WHERE condizioni]

once and for all:

• non si torna indietro
• prima di eseguire la query, provare a metterci una SELECT * per vedere che succede

Eliminare dati

Eliminare tutti i clienti italiani

Hint: DELETE FROM tab WHERE ...

DELETE FROM customers
    WHERE country = "Italy";
    

Eliminare dati

Perchè non funziona?

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.
    

Eliminare dati

Perchè non funziona?

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!

Check

Permette di introdurre vincoli di integrità generici

CREATE TABLE nomeTab(
    attr1 tipo1 CHECK (condizione),
    attr2 tipo2,...,
    CHECK (condizione))
    

• le condizioni sono espressioni booleane
• possono essere complesse (es: subquery)
• non funzionano in MySql

CHECK

• il genere può essere solo M o F

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

ASSERTION

Permettono di introdurre vincoli di integrità a livello di schema

CREATE ASSERTION nome CHECK (condizione)
    

Stesse note del check

ASSERTION

La tabella impiegato deve avere almeno un nominativo

CREATE ASSERTION AlmenoUnImpiegato
    CHECK ((SELECT count(*) FROM Impiegato) >= 1)
    

Variabili

Posso definire variabili da usare nelle query

SET @variabile = valore;
SELECT @variabile;
    

• vivono e muoiono nella sessione
• il valore può essere anche una query che ritorna un solo dato
• MySQL: case insensitive, solo tipi semplici (integer, decimal, string, …)

Variabili

Creare una variabile di nome “pippo”, assegnarci il valore “Hello Word!” e mostrarne in contenuto

SET @pippo = 'Hello, World!';
SELECT @pippo;
    

Variabili

Salvare nella variabile “prezzo” il prezzo più alto (MSRP) presente a listino e mostrarne il valore

SET @prezzo = (SELECT max(msrp)
FROM products);
SELECT @prezzo;
    

Variabili

Mostrare i prodotti in cui il valore MSRP è pari alla variabile appena impostata

SELET * FROM products
WHERE MSRP = @prezzo;
    

Esecuzione query

Cosa accade quando invio una query al server?

1. Parser: trasforma il testo in un albero di comandi
2. PreProcessor: la sintassi è corretta?
3. Security: l’utente può fare questo?
4. Optimizer: posso riscrivere la query in modo più intelligente?
5. Execution Engine: effettua l’operazione
6. Trasmissione Dati

E se devo eseguire spesso la stessa query??

Profiling (MySQL)

Cosa fa il motore?

SET profiling = 1;
esecuzione comandi
SHOW PROFILES;
SHOW PROFILE [FOR QUERY n];
SET profiling = 0;
    

• SHOW PROFILES: storico dei tempi di esecuzione
• SHOW PROFILE: come ho impiegato il tempo nell’ultima query/query specificata?

Profiling

Cosa posso vedere?

Tipo

• ALL: tutte le informazioni
• CPU: tempo CPU per user/system
• SWAPS: utilizzo della memoria su disco
• SOURCE: nome della funzione/libreria usati

Privilegi

Mostra tutti i dati dell’ultima query

SHOW PROFILE ALL FOR QUERY 4;
    

Prepared statement

Posso precompilare le query che uso più spesso

PREPARE nomeStatement FROM 'query';
EXECUTE nomeStatement USING p1, p2,...;
DEALLOCATE PREPARE nomeStatement;
    

• PREPARE: crea una query riutilizzabile, che può ricevere parametri
• EXECUTE: esegue il comando salvato
• DEALLOCATE PREPARE: elimina il comando
• vivono e muoiono nella sessione

Prepared Statement: PREPARE

Creare lo statement (MySQL)

PREPARE nomeStatement FROM
    'SELECT a1,a2,...
    FROM tabella
    WHERE a1 = ? AND a2 = ?';

• la query da eseguire è passata come stringa
• ogni “?”” corrisponde ad un parametro che verrà comunicato in sede di esecuzione

Prepared Statement: PREPARE

Creare lo statement (PostgreSQL)

PREPARE nomeStatement(type1, type2, ...) AS
        SELECT a1,a2,...
        FROM tabella
        WHERE a1 = $1 AND a2 = $2;
    

• specifico i tipi tra parentesi
• si fa riferimento ai paramentri tramite $

Prepared Statement: PREPARE

Creare lo statement “stmt1” il quale seleziona productCode e productName dal listino mostrando solo le ennuple con MSRP maggiore del parametro che verrà fornito.

Hint: PREPARE nomeStatement FROM 'SELECT a1,a2,... FROM tabella WHERE a1 = ? AND a2 = ?';

PREPARE stmt1 FROM
    'SELECT productCode, productName FROM products 
    WHERE MSRP > ?';
    

Prepared Statement: EXECUTE

Eseguire lo statement (MySQL)

EXECUTE nomeStatement
    [USING @var1, @var2,...];
    

• i parametri devono essere variabili
• in teoria sono opzionali (posso creare statement senza parametri, ma è meglio evitarlo)

Prepared Statement: EXECUTE

Eseguire lo statement (PostgreSQL)

EXECUTE nomeStatement(arg1, arg2, ...);
    

Prepared Statement: PREPARE

Usando stmt1 mostrare i prodotti con prezzo superiore ai 100$

Hint: EXECUTE nomeStatement [USING @var1, @var2,...];

SET @MSRP = 100;
EXECUTE stmt1 USING @MSRP;
    

Prepared Statement: DEALLOCATE

Eliminare lo statement

DEALLOCATE PREPARE nomeStatement;
DROP PREPARE nomeStatement;
    

Schema esterno

Schema Esterno

Creare uno schema esterno che mostri il listino ai clienti (codice e nome prodotto, MSRP).

PREPARE stmtListinoClienti FROM
    'SELECT productCode, productName, MSRP FROM products';
    

Problemi:

• codice inserito nell’applicazione
• sparisce quando chiudo la connessione
• va creato per ogni connessione
• accesso diverso da una normale tabella

Viste

Rappresentazione alternativa dei dati

• SQL SELECT salvata nel motore
• persistente: non muore alla chiusura della connessione
• posso usare query per interrogarla
• posso aggiornare i dati (con dei limiti)

Viste

Vantaggi

• semplificare query complesse
• nascondere dati sensibili
• gestire gli accessi
• campi calcolati appaiono come colonne normali
• compatibilità

Viste

Svantaggi

• se modifico le tabelle, devo aggiornare le viste
• non accetta parametri
• non è compilata
• performance leggermente peggiori (in particolare se uso viste che contengono altre viste)

Viste

Come funzionano? [MySQL]

MERGE (predefinito)

• nella query inserisco il codice contenuto nella vista
• eseguo il codice ottenuto

Viste

Come funzionano? [MySQL]

TEMPTABLE (materialized)

• creo una tabella temporanea in cui salvo il risultato
• obbligatoria se la vista non ha una relazione biunivoca con la tabella sottostante:
  • funzioni di aggregazione, group by, having
  • distinct, limit
  • union
  • subquery nella lista degli attributi

Viste

Definizione di una vista

CREATE VIEW nomeVista AS
    SELECT ...
    

Dettagli:

• la SELECT può essere eseguita da sola
• posso usare subquery nella clausola WHERE
• non posso usarle nella clausola FROM

Viste

Creare uno schema esterno “viewListinoClienti” che mostri il listino per i clienti (codice e nome prodotto, MSRP), quindi mostrarne i dati

CREATE VIEW viewListinoClienti AS
    SELECT productCode, productName, MSRP
    FROM products;
    SELECT * from viewListinoClienti;
    

Viste

Creare uno schema esterno “viewTotaleOrdini” che mostri il numero dell’ordine e l’importo totale

Hint: CREATE VIEW nomeVista AS SELECT ...

CREATE VIEW viewTotaleOrdini AS
    SELECT orderNumber,
    SUM(quantityOrdered * priceEach) total
    FROM orderdetails
    GROUP by orderNumber
    

Viste

Usando “viewTotaleOrdini” mostrare il totale dell’ordine 10102

SELECT total
    FROM viewTotaleOrdini
    WHERE orderNumber = 10102;
    

Viste

Cosa fa una vista?

SHOW CREATE VIEW nomeVista;
    

Eliminare una vista

DROP VIEW nomeVista;
    

Viste

Modificare una vista

ALTER VIEW nomeVista AS nuovaSELECT;
    

Viste modificabili

Posso modificare i dati di una vista se la SELECT:

• è riferita ad una sola tabella
• non contiene GROUP BY o HAVING
• non contiene DISTINCT
• non fa riferimento a viste non modificabili
• la selezione non contiene espressioni

Viste Modificabili

Creare la vista “officeInfo” mostrando codice ufficio, telefono e città degli uffici. Provare a modificare qualche dato (es: n. tel.)

CREATE VIEW officeInfo
    AS SELECT officeCode, phone, city
    FROM offices;
    

UPDATE officeInfo
    SET phone = '+39 040 55558555'
    WHERE officeCode = 4;
    

Controllo Accessi

Connessione

• utente e password valide?
• [connessione da client autorizzato]?

Controllo Accessi

Richiesta

• l’utente connesso può fare questa operazione?
• può accedere a questo DB?
• può accedete a questa tabella?
• può accedete a questo attributo?
• può eseguire questa procedura?

Aggiungere utenti

Dipende dal motore

MySQL:

CREATE USER nome@host
IDENTIFIED BY 'password'

Oracle:

CREATE USER nome
IDENTIFIED BY password

SQL Server

CREATE USER nome
WITH PASSWORD = 'password'

Aggiungere utenti

Dettagli per MySQL:

• posso usare wildards nell’host se le racchiudo tra apici - '%' per ogni host
• 'nome@host' crea un utente con username nome@host legato all’host %
• FLUSH PRIVILEGES forza il reload dei dati

Aggiungere Utenti

Creare l’utente “pippo” con password “pluto” che possa connettersi solo dal vostro computer

CREATE USER pippo@localhost
IDENTIFIED BY 'pluto';

Controllo accessi

12 regole di Codd

1. INFORMAZIONI: tutte le informazioni in un DBR sono rappresentate esplicitamente da valori in tabelle (DEFINIZIONE)

Controllo Accessi - MySQL

Connessione

• Utente e password valide?
• Connessione da un client autorizzato?

DataBase mysql

• Tabella user

  INSERT INTO user(host,user,password)
  VALUES('localhost','pippo',
  PASSWORD('pluto'));
  FLUSH PRIVILEGES;
  

Modificare gli utenti

Cambiare la password

SET PASSWORD FOR user@host =
PASSWORD('Secret1970');

Eliminare un utente

DROP USER user@host;

Assegnare i permessi

Un utente appena creato non può fare nulla

GRANT privilegio (colonne)
ON risorsa
TO account
[WITH GRANT OPTION]

• privilegio: tipo di operazione permessa
• colonne: se si applica solo ad alcune colonne
• risorsa: database.tabella — wildcard: *
• account: utente@host
• WITH GRANT OPTION: l’utente può propagare i permessi ad altri

Privilegi

• ALL: tutti
• ALTER: modificare tabella
• CREATE: creare oggetti
• DELETE: eliminare ennuple
• SELECT: leggere i dati
• UPDATE: modificare i dati
• … e tanti altri

Privilegi

Permettere all’utente pippo di leggere, modificare e cancellare dati al DB dei modellini.

Hint: GRANT privilegio (colonne) ON risorsa TO account

GRANT SELECT, UPDATE, DELETE ON
classicmodels.* TO 'pippo'@'%';

Privilegi

Creare un altro amministratore

GRANT ALL ON *.* TO 'super'@'localhost'
WITH GRANT OPTION;

Permettere ad un utente di leggere e modificare i numeri di telefono dei clienti e di vederne i nomi

GRANT SELECT (phone, customerName),
UPDATE (phone)
ON classicmodels.customers
TO 'someuser'@'somehost';

Visualizzare i permessi

Posso vedere i privilegi di ogni utente

SHOW GRANTS FOR utente;

Revocare i permessi

Sintassi molto simile a GRANT

REVOKE privilege_type [(column_list)]
[, priv_type [(column_list)]]...
ON [object_type] privilege_level
FROM user [, user]...

Revocare i permessi

REVOKE UPDATE, DELETE ON
classicmodels.* FROM
'rfc'@'localhost';

Transazioni

Insieme di operazioni da considerare indivisibile (“atomico”), corretto anche in presenza di concorrenza e con effetti definitivi

ACID

Proprietà transazioni:

• Atomicità
• Consistenza
• Isolamento
• Durabilità (persistenza)

Atomicità

La sequenza di operazioni sulla base di dati viene eseguita per intero o per niente.

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

Consistenza

Al termine dell’esecuzione di una transazione, i vincoli di integrità debbono essere soddisfatti

Durante l’esecuzione ci possono essere violazioni, ma se restano alla fine allora la transazione deve essere annullata per intero (“abortita”)

Isolamento

L’effetto di transazioni concorrenti deve essere coerente (ad esempio equivalente all’esecuzione separata)

Esempio: se due assegni emessi sullo stesso conto corrente vengono incassati contemporaneamente si deve evitare di trascurarne uno

Durabilità

La conclusione positiva di una transazione corrisponde ad un impegno (“commit”) a mantenere traccia del risultato in modo definitivo, anche in presenza di guasti e di esecuzione concorrente

Transazioni

La sintassi dipende dal motore

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 eseguite
• ROLLBACK: si rinuncia all’esecuzione delle operazioni specificate dopo l’ultimo begin transaction

Transazioni

SQL Server

• BEGIN TRANSACTION
• COMMIT WORK
• ROLLBACK WORK

Esempio Transazione

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;

Transazioni in un DBMS

Due moduli fondamentali:

• gestore della concorrenza
  • garantisce isolamento e consistenza
  • scheduler delle operazioni
• gestore dell’affidabilità
  • garantisce atomicità e durevolezza
  • consentire il recupero in caso di guasti

Gestore dell’affidabilità

Idee di base:

• registrare tutte le azioni eseguite in un file di registro (“log”)
• se si rompe qualcosa durante la transazione, so come tornare indietro

Attenzione:

Qualcosa è sempre in memoria e può sempre essere perso

Log delle transazioni

Come salvo le transazioni?

• Write Ahead Logging:
  • il log contiene i blocchi modificati
  • commit = copiare i dati dal log al file del DB
  • scelto da quasi tutti i motori
• Command Logging:
  • il log contiene lo storico delle istruzioni
  • commit = eseguire realmente le operazioni

Redo Logging

Soluzione di MySQL (Write Ahead)

• salvo i dati in un log che risiede in memoria
• sposto piccole porzioni in un log su disco
• ogni tanto unisco il log su disco ai dati reali

SQL

Programming Language (PL)

Oggetti programmabili in SQL

È possibile racchiudere comandi SQL in oggetti programmabili la cui definizione rimane nelle tabelle di sistema:

• facilitano il riutilizzo del piano di esecuzione (precompilate)
• incapsulano la logica applicativa di accesso ai dati
• nascondono al client la complessità della base dati

Oggetti programmabili in SQL

Viste

Stored Procedure

insieme di comandi SQL con parametri di input e output che possono restituire recordset

Oggetti programmabili in SQL

Trigger

particolari stored procedure che vengono associate ad una operazione su un oggetto e invocate automaticamente

User Defined Function

consentono di raggruppare e riutilizzare codice SQL solitamente ripetuto all’interno di SP e trigger

Assemblies SQLCLR

semplificando: stored procedure scritte in linguaggi Microsoft

Stored Procedure

Subroutine che contengono tutto il codice necessario per effettuare una operazione

• incapsulano task ripetitivi
• codice ammesso: tutto (DDL, DML, TSQL,…)
• accettano parametri di input
• producono zero, uno o più output
  • parametri di output
  • resultset

Stored Procedure

FONDAMENTALI per incapsulare la logica di

• accesso alle tabelle
• manipolazione dei dati

Stored Procedure

Permettono la creazione di un livello di astrazione del modello fisico del database

• aiutano a mantenere un alto disaccoppiamento
• garantiscono la possibilità di intervenire sul database senza necessariamente modificare le applicazioni che lo usano
  • introduzione di nuove funzionalità necessarie ad altre applicazioni (un db non è privato)
  • miglioramento performance

Stored Procedure: vantaggi

• mascherano lo schema logico del DB
• riutilizzo del codice
• permettono l’implementazione di arbitrari meccanismi di sicurezza
• migliorano le performance (cached execution plans)
  • MySql: ricompilata per ogni connessione
• riducono il traffico di rete

Stored Procedure: svantaggi

• aumentano il carico (CPU, memoria) del DBMS
• difficile farne il debug
  • MySql: non possibile
• sintassi particolare
• non sono transazionali di per se
  • …ma possono diventarlo usanto T-SQL

Creare Stored Procedure

Anche qui dipende dal motore…

CREATE PROCEDURE nome()
BEGIN
... codice
END

MS SQL Server:

CREATE PROCEDURE nome
AS [BEGIN]
... codice
[END]

Creare Stored Procedure

Creare una SP che prenda tutti i dati dei prodotti

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 “;”)

Creare Stored Procedure

MySql: cambio il delimitatore

DELIMITER $$
CREATE PROCEDURE nome()
BEGIN
... codice1;
... codice2;
END $$
DELIMITER ;

Creare Stored Procedure

SQL Server: GO!

CREATE PROCEDURE nome
AS [BEGIN]
... codice1;
... codice2;
[END]
GO

Eseguire Stored Procedure

Dipende dal motore

MySql

CALL nomeStoredProcedure()

SQL Server

EXEC nomeStoredProcedure

PostgreSQL

SELECT nomeStoredProcedure()

Oracle

EXECUTE nomeStoredProcedure()

Stored Procedure

Creare una SP che mostri i dati di tutti i dipendenti che siano "SalesRep"

Hint: CREATE PROCEDURE nome() BEGIN ... codice END

DELIMITER $$
CREATE PROCEDURE sp_getSalesRep()
BEGIN
SELECT * FROM employees
WHERE jobTitle = 'Sales Rep';
END

Visualizzare le Stored Procedure

Per vedere tutte le SP nel motore:

SHOW PROCEDURE STATUS
[WHERE condizioni]

Condizioni:

• db: nome del DB
• name: nome della SP
• posso usare uguaglianza, LIKE, OR, AND, …

Visualizzare ed eliminare SP

Per vedere il codice di una SP:

SHOW CREATE PROCEDURE spNome

Per eliminare una SP:

DROP PROCEDURE spNome

Modificare Stored Procedure

Dipende dal motore.

MySql

DROP + CREATE

SQL Server

ALTER nomeStoredProcedure
AS
...codice
GO

Parametri

Una Stored Procedure può ricevere dei parametri:

CREATE PROCEDURE nomeSP(
    nomePar1 tipoPar1,
    nomePar2 tipoPar2, ...
)
BEGIN
... codice
END
CALL nomeSP(par1, par2,...)

Parametri

Creare una SP che mostri i dati di tutti i dipendenti che siano della tipologia passata come parametro (Principal, Sales Rep, ecc.)

CREATE PROCEDURE sp_getEmployeeByType(
tipoImp varchar(50))
BEGIN
    SELECT * FROM employees WHERE jobTitle = tipoImp;
END

Parametri

I parametri sono passati:

• in sola lettura (solo input) → IN
  • è l’opzione di default
• in sola scrittura (solo output) → OUT
  • durante l’esecuzione uso variabili

• leggibili e scrivibili (bidirezionali) → INOUT

  CREATE PROCEDURE nomeSP(
      direzione nomePar1 tipoPar1,
      direzione nomePar2 tipoPar2,...)
  BEGIN ... codice END
  

Parametri

Creare una SP che funga da contatore

CREATE PROCEDURE sp_conta(
    INOUT count INT(4),
    IN inc INT(4))
BEGIN
    SET count = count + inc;
END

Verifica

SET @counter = 1;
CALL sp_conta(@counter,1); -- 2
CALL sp_conta(@counter,1); -- 3
CALL sp_conta(@counter,5); -- 8
SELECT @counter; -- 8

Parametri

Creare una SP che raddoppi il valore passato

CREATE PROCEDURE sp_raddoppia(
    INOUT valore int(11))
BEGIN
    SET valore = valore * 2;
END

Verifica

set @val = 10;
select @val;
CALL sp_raddoppia(@val);
select @val;

Parametri

Creare una SP che prenda in input il numero d'ordine e ritorni il numero di oggetti comprati

CREATE PROCEDURE sp_contaOggettiInOrdine(
    IN oNumber INT,
    OUT numberObjects INT)
BEGIN
    SET numberObjects = (
        SELECT sum(quantityOrdered) FROM
        orderdetails WHERE orderNumber = oNumber
    );
END

Verifica

CALL sp_contaOggettiInOrdine(10100, @numObj);
select @numObj; --- 151

SELECT INTO

Se devo scrivere direttamente il risultato di una query in una variabile

CREATE PROCEDURE sp_contaOggettiInOrdine(
    IN oNumber INT,
    OUT numberObjects INT)
BEGIN
    SELECT sum(quantityOrdered)
    INTO numberObjects
    FROM orderdetails
    WHERE orderNumber = oNumber);
END

SELECT INTO

Creare una SP che riceva in input lo status dell'ordine e dica quanti ordini vi sono

CREATE PROCEDURE sp_contaOrdini(
    IN orderStatus VARCHAR(25),
    OUT total INT)
BEGIN
    SELECT count(orderNumber)
    INTO total FROM orders
    WHERE status = orderStatus;
END

Verifica

CALL sp_contaOrdini('Shipped',@total);
SELECT @total; --- 303

Più istruzioni

Creare una SP che riceva in input il codice cliente e restituisca tanti valori:

• numero ordini spediti (status = Shipped)
• numero ordini cancellati (Canceled)
• numero ordini risolti (Resolved)
• numero ordini confutati (Disputed)

Provate con il cliente 141:

CALL sp_getOrderByCust(141,@shipped,@canceled,
@resolved,@disputed);
SELECT @shipped,@canceled,@resolved,@disputed;

Più istruzioni

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';
END $$
DELIMITER ;

Variabili

MySql

Le variabili hanno tre livelli di visibilità:

• globali (@@): tutti le vedono
• connessione (@): connessione vede le proprie
  • SET @variabile = 10
• locali (senza @): nascono e muoiono nella SP
  • DECLARE nomeVariabile tipoVariabile
  • SET nomeVariabile = valore

Condizioni

Nelle Stored Procedure posso inserire IF:

IF espressione THEN
    comandi
ELSEIF espressione THEN
    comandi
ELSE
    comandi
END IF;

Condizioni

Creare una SP che prenda in input il codice cliente e restituisca una stringa che vale:

• PLATINUM se il credito è > 50.000
• GOLD se il credito è > 10.000 e <= 50.000
• SILVER altrimenti

Provate con il cliente 103:

CALL sp_getCustomerLevel(103, @livello);
SELECT @livello;

Condizioni

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;

Cicli

Posso ripetere più volte la stessa operazione:

WHILE espressione DO
comandi
END WHILE;

REPEAT
comandi
UNTIL espressione
END REPEAT;

Dettagli:

• LEAVE: esce dal ciclo
• ITERATE: procede con l’iterazione successiva

Cicli

SP che calcoli la serie di Fibonacci

DELIMITER $$
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;

Gestire gli errori

Voglio gestire gli errori nella SP

DECLARE azione HANDLER FOR
    condizione [BEGIN] codice [END]
    

• condizione: cosa vogliamo intercettare
• codice: cosa fare
• azione: come comportarsi dopo aver eseguito il codice
  • CONTINUE → continua con il resto
  • EXIT → termina l’esecuzione

Gestire gli errori

DECLARE azione HANDLER FOR
    condizione [BEGIN] codice [END]
    

Condizioni:

• codice errore MySql
  • Es: 1062, chiave duplicata
• SQLSTATE ‘codiceNumerico’
  • Es: 22012, divisione per zero

Gestire gli errori

SQLSTATE

• SQLWARNING: scorciatoria per SQLSTATE che iniziano con 01
• NOT FOUND: scorciatoria per SQLSTATE che iniziano con 02
• SQLEXCEPTION: scorciatoria per SQLSTATE che non iniziano con 00, 01 o 02

Gestire gli errori

In caso di errore, annullare la transazione e dare un messaggio

DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Errore: ho annullato tutto!';
    END;

Segnalare errori

Posso lanciare messaggi di errore

SIGNAL SQLSTATE 'codice'
SET MESSAGE_TEXT = 'testo'

Codice definito dall’utente: 45000

Segnalare errori

Scrivere una SP che ritorni il numero di ordini di un cliente. Se non esiste, dare un errore

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

Cursori

Permettono di processare singole righe di un resultset

• Read-only: non posso aggiornare i dati usando il cursore
• Non-scrollable: posso scorrere il dataset senza cambiarne l’ordinamento
• Asensitive: puntano ai dati reali, non ad una copia → rapidi a crearsi, ma modifiche fatte ai dati da altre connessioni si ripercuotono sul cursore

Cursori

Definisco il nome del cursore e la query che userà

DECLARE nomeCursore CURSOR FOR
SELECT ...

Cursori

Apro il cursore, eseguendo la query

OPEN nomeCursore

Cursori

…uso il cursore in un ciclo

FETCH nomeCursore INTO var1, var2, ...

Cursori

Chiudo il cursore (libero memoria):

CLOSE nomeCursore

Cursori

Cursori

Quando smetto di usare il cursore?

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

Cursori

Creare una SP che ritorni in un singolo valore tutti gli indirizzi email dei dipendenti

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

Cursori

Creare una SP che ritorni in un singolo valore tutti gli indirizzi email dei dipendenti

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

User Defined Function

• Scalar Functions
  • Simile ad una built-in function
  • Ritorna un singolo valore costruito con una serie di statements
• Multi-Statement Table-valued Functions
  • Contenuto simile ad una stored procedure
  • Referenziata come una Vista
• In-line Table-valued Functions
  • Simile ad una Vista con parametri
  • Ritorna una tabella come risultato di uno statement SELECT singolo

UDF vs SP

• Risultato
  • SP: restituisce 0 o N valori
  • UDF: restituisce sempre 1 valore
• Parametri
  • SP: input/output
  • UDF: solo input
• Modifiche
  • SP: non può modificare il DB
  • UDF: solo SELECT (Myql, Oracle: tutto, ma meglio evitare)

UDF vs SP

• Chi richiama chi
  • SP: può richiamare UDF
  • UDF: non può richiamare SP
• SELECT
  • SP: non può essere usata in una SELECT
  • UDF: può essere usata in una SELECT
• RecordSet
  • SP: se ritorna una tabella non posso riusarla (no select)
  • UDF: posso usarla come una normale tabella

User Defined Function

Come definire una UDF

CREATE FUNCTION function_name
(param1 tipo1,param2 tipo2,...)
RETURNS tipo
[NOT] DETERMINISTIC
BEGIN
    statements
END

Si usa come una funzione normale (select, ecc)

Deterministic o no?

Aiuto il motore a capire come ottimizzare

• Deterministic: se l’input e lo stato del DB non variano, l’output non varia
• NON Deterministic: l’output può variare anche se l’input non varia
  • now()
  • rand()

Deterministic: e se sbaglio?

Il motore si fida

• dico Deterministic ma non lo è: risultati non corretti (l’execution planner può decidere che non occorre ricalcolare)
• dico NON Deterministic ma lo è: prestazioni peggiori (ricalcolo anche se non serve)

User Defined Function

Creare una UDF che riceve in input un numero e ne restituisca il doppio, ed usarla in una query

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

User Defined Function

Creare una UDF che riceva il credito del cliente e restituisca il livello (platinum, ... )

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

User Defined Function

Creare una UDF che riceva il codice cliente e restituisca il numero di ordini che ha fatto.

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

Trigger

Operazioni da eseguire quando si verifica un certo evento

• non viene richiamata direttamente
• parte automaticamente quando si effettua una operazione su una certa tabella (insert, delete, update)
• possono essere legati ad eventi temporali
• limiti MySQL: non possono usare UDF, SP, prepared statements

Trigger

VANTAGGI

• ulteriore controllo dell’integrità dei dati
  • controlli non possibili per limiti del motore (es: CHECK in MySql)
  • controlli aggiuntivi sulla logica del programma
• molto comodi per l’audit (registrazione) delle modifiche

Trigger

Creazione di un trigger

CREATE TRIGGER nome quando
ON nomeTabella
FOR EACH ROW
BEGIN
    codice
END

• ogni trigger ha un nome
• ogni trigger è riferito ad una tabella

Trigger: quando

Tralasciamo i trigger temporali

Che operazione controlliamo?

• INSERT, UPDATE o DELETE

Quando devo eseguire il trigger?

• BEFORE
  • es: i dati sono coretti?
• AFTER
  • registro chi ha modificato i dati, ricalcolo valori

ES: BEFORE UPDATE

Trigger: granularità

Statement level (default SQL Server)

• il trigger viene eseguito una volta sola per ogni comando che lo ha attivato, indipendentemente dal numero di tuple modificate
• è il modo più vicino all’approccio tradizionale dei comandi SQL, che sono di norma set-oriented

Trigger: granularità

Row level

• FOR EACH ROW, unico per MySQL
• il trigger viene eseguito una volta per ciascuna tupla che è stata modificata dal comando
• consente di scrivere i trigger in modo più semplice
• può essere meno efficiente

Trigger: OLD & NEW

Permettono di distinguere il record prima e dopo la modifica

• OLD: valore precedente
  • usabile nel DELETE
  • usabile nel BEFORE UPDATE
• NEW: valore dopo le modifiche
  • usabile nell’INSERT
  • usabile nel BEFORE UPDATE

Es: OLD.contactLastName

Creiamo una tabella di AUDIT

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

Trigger che registra le modifiche

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 ;

Trigger che registra le modifiche

UPDATE employees
SET lastName = 'Phan'
WHERE employeeNumber = 1056

Trigger per il controllo dei dati

Se qualcosa non va si segnala un errore

La sintassi è la stessa vista nelle SP

SIGNAL sqlstate '45001' SET message_text = "No way !";

Trigger per il controllo dei dati

Creare un trigger che verifichi non venga incrementato il limite di credito

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

Conflitti tra trigger

Se vi sono più trigger associati allo stesso evento, SQL:1999 prescrive

• vengono eseguiti i trigger BEFORE statement-level
• vengono eseguiti i trigger BEFORE row-level
• si applica la modifica e si verificano i vincoli di integrità definiti sulla base di dati
• vengono eseguiti i trigger AFTER row-level
• vengono eseguiti i trigger AFTER statement-level

Conflitti tra trigger

Se vi sono più trigger della stessa categoria, l’ordine di esecuzione viene scelto dal sistema in un modo che dipende dall’implementazione

Modello di esecuzione

• SQL:1999 prevede che i trigger vengano gestiti in un Trigger Execution Context (TEC)
• l’esecuzione dell’azione di un trigger può produrre eventi che fanno scattare altri trigger, che dovranno essere valutati in un nuovo TEC interno
• in ogni istante possono esserci più TEC per una transazione, uno dentro l’altro, ma uno solo può essere attivo

Modello di esecuzione

• per i trigger row-level il TEC tiene conto di quali tuple sono già state considerate e quali sono da considerare
• si ha quindi una struttura a stack
  • TEC0 -> TEC1 -> … -> TECn
• quando un trigger ha considerato tutti gli eventi, il TEC si chiude e si passa al trigger successivo

Interazione tra trigger

Trigger T1: Bonus

Evento: update di Obiettivo in Progetto

Condizione: Obiettivo = ‘SI’

Azione: incrementa del 10% il salario degli impiegati coinvolti

Trigger T1: Bonus

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;

Trigger T2: ControllaIncremento

Evento: update di Salario in Impiegato

Condizione: nuovo salario maggiore di quello del manager

Azione: decrementa il salario rendendolo uguale a quello del manager

Trigger T2: ControllaIncremento

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;

Trigger T3: ControllaDecremento

Evento: update di Salario in Impiegato

Condizione: decremento maggiore del 3%

Azione: decrementa il salario del solo 3%

Trigger T3: ControllaDecremento

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;

Attivazione di T1

UPDATE Progetto SET Obiettivo = 'SI' WHERE NroProg = 10

Attivazione di T2

Attivazione di T3

• Si attiva nuovamente T3 - condizione è falsa
• Si attiva T2 - condizione vera

Attivazione di T2

• Si attiva nuovamente T3 - condizione è falsa
• L'attivazione dei trigger ha raggiunto lo stato di terminazione

Tecniche di accesso ai dati

Accedere ai dati

Data Consumer

Tool e linguaggi che lavorano con i dati

Data Providers

Sorgente dei dati

Breve storia dell’accesso ai dati

• API proprietarie (es: VB Objects)
• Data Access Objects (DAO/Jet)
• Open Database Connectivity (ODBC)
• OLE for Databases (OLE/DB)
• ActiveX Data Objects (ADO)
• .NET→ ADO.NET
• Object-relational Mapping (ORM)

Sistemi proprietari

• dipendono da Data Consumer
  • linguaggio e piattaforma
• dipendono da Data Provider
• …a volte ancora necessari
  • es: accedere ad un MDB in linux

ODBC

• sviluppato da Microsoft e ceduto al W3C
• pensato per motori relazionali
• indipendente da DBMS e sistema operativo

ODBC

Componenti principali

• ODBC Driver: layer tra l’applicazione ed il DBMS
• l’applicazione usa un Driver Manager per accedere ai vari driver
• Data Source Names (DNS): informazioni per la connessione; gestiti dal Driver Manager

ODBC

PRO:

• non intrusivo sul server; sono i Driver ad avere un’interfaccia verso i Data Provider

Contro:

• non sempre esiste il driver (gratis)…
• API difficili da usare
• richiedono molto codice da implementare nell’applicazione

ActiveX Data Objects (ADO)

• interfaccia più user-friendly per il programmatore
• parte di Microsoft Data Access Components (MDAC)
• qualsiasi fonte ODBC o OLE/DB
• accessibile da diversi linguaggi (C++, Java, .NET, …)

ADO Connection String

Ci sono tanti possibili provider

• serve la password?
• devo dare un nome di file?
• l’indirizzo di un server?

ADO Connection String

Stringa di connessione:

• serie di coppie chiave-valore
• separatore: “;”
• chiave=valore

ADO Connection String

ODBC

DSN=PropDB;Uid=admin;Pwd=;

Access

Provider=‘Microsoft.JET.OLEDB.4.0’;Data Source=‘C:\test.mdb’

ADO Connection String

SQL Server

Server=myServerAddress;Database=myDB;UserId=myUsername;Password=myPassword;

MySql

Server=myServerAddress;Database=myDB;Uid=myUsername;Pwd=myPassword;

http://www.connectionstrings.com/

ADO .NET

Evoluzione di ADO

ADO .NET è una collezione di classi, interfacce, strutture e tipi che gestiscono l’accesso ai dati da fonti relazionali all’ interno del .NET Framework

ADO vs ADO .NET

ADO

• progettato per accessi connessi
• totalmente legato al modello fisico dei dati
• RecordSet è il contenitore dei dati
• RecordSet è una tabella che contiene tutti i dati
• se si vuole estrarre dati da più di una tabella: JOIN
• i dati sono “flattened”: si perdono le relazioni, la navigazione è sequenziale

ADO vs ADO .NET

ADO .NET

• progettato per accessi disconnessi
• può modellare i dati a livello logico
• il DataSet rimpiazza il RecordSet
• DataSet contiene tabelle multiple
• estrarre dati da più di una tabella non richiede una JOIN
• Relationships conservate e la navigazione è relazionale

Classi principali di ADO .NET

Connection

• usato per parlare al DB
• le proprietà includono dataSource, username e password

Command

• Uno statement SQL o Stored Procedure

Classi principali di ADO .NET

DataReader

• sola lettura monodirezionale, ma molto veloce
• connesso
• una vista unidirezionale connessa dei dati (simile ad ADO Recordset)

DataAdapter

• lettura e scrittura
• disconnesso
• gestisce DataSet

JDBC

Equivalente JAVA di ADO .NET

Esistono 4 tipologie diverse di JDBC

Tipo 1: JDBC-ODBC bridge

• il driver dipende dal SO (chiamate ad ODBC)
• PRO: se ho il driver ODBC posso accedere al DB
• CONTRO: overhead, ed il driver ODBC deve essere installato sul client
• fornito in Java: sun.jdbc.odbc.JdbcOdbcDriver

Tipo 2: Native-API Driver

• comunica con le API del DB (dipende dal SO)
• PRO: più veloce del Tipo 1
• CONTRO: le librerie client del DB vanno installate sulle macchine

Tipo 3: Network-Protocol Driver

• MiddleWare Driver
• comunica con un application server (es: J2EE) che converte le chiamate nel linguaggio del DB

Tipo 4: Database-Protocol Driver

• Pure Java Driver
• PRO: Platform-independent
• CONTRO: ogni DBMS deve scrivere il suo

ADO .NET -> JDBC

SqlConnection -> Connection

SqlCommand -> Statement

SqlDataReader -> ResultSet

Caricare il driver

// The newInstance() call is a work around for some
// broken Java implementations
Class.forName("com.mysql.cj.jdbc.Driver").newInstance();

Connettersi al DB

con = DriverManager.getConnection("jdbc:mysql://localhost/" +
                                    "DBNAME?" +
                                    "user=XXX&" +
                                    "password=YYY");

Eseguire una query

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();  
}

Leggere i risultati

ResultSet rs = stm.executeQuery("select * from persone");
while (rs.next()) {
   String col1 = rs.getString("colonna1");
}

Modificare i dati

stm.executeUpdate("UPDATE tabella SET colonna = val ...");
stm.executeUpdate("INSERT ...");
stm.executeUpdate("DELETE ...");

Prepared Statement

String sql = "SELECT nome FROM persone WHERE cognome = ?";
PreparedStatement prepared = connection.prepareStatement(sql);
prepared.setString(1, "Rossi");
ResultSet rs = stm.executeQuery();

Prepared Statement

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();

Chiamare una SP

CallableStatement cStm = con.prepareCall("{call sp_name(?, ?)}");
cStm.setString(1, "abcdefg");
boolean hadResults = cStm.execute();

Parametri SP

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(); 

Risultato SP

boolean hadResults = cStm.execute();
while (hadResults) {
   ResultSet rs = cStm.getResultSet();
   hadResults = cStm.getMoreResults();
}
int outputValue = cStm.getInt(2); // index-based

Python

import mysql.connector
mydb = mysql.connector.connect(
  host="localhost", user="userdb",
  password="***", database="classicmodels"
)

mycursor = mydb.cursor()
sql = "SELECT * FROM employees WHERE lastName = %s"
adr = ("Smith", )
mycursor.execute(sql, adr)
myresult = mycursor.fetchall()
for x in myresult:
  print(x)

… cose da non fare

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("...");
}
 

SQL Injections

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;
 

SQL Injections

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;
 

SQL Injections

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;
 

SQL Injections

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
}
 

SQL Injections

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 ''='';

SQL Injections

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
}
 

SQL Injections

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;

SQL Injections

Stringa inserita: ";UPDATE ... --

Object-relational Mapping

Integrazione tra linguaggi di programmazione ad oggetti e DBMS relazionali

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;

SQL? Inutile?

• impossibile usare ORM se non si sa progettare DB
• molti sistemi non li usano
• a volte meno efficiente che non scrivere query a mano

Object-relational Mapping

Cosa si fa?

• conversione di oggetti in valori scalari
• mantenimento delle relazioni tra gli oggetti

Tanti framework diversi risolvono il problema

Object-relational Mapping

Java Persistance API (JPA)

• Hibernate
• EclipseLink
• TopLink

Object-relational Mapping

Framework .NET

• LINQ to SQL (solo SQL server)
• ADO Entity Framework
• NHibernate

Hibernate

• utilizzabile in JAVA (estende JPA)
• utilizzabile in C# (NHibernate)
• supporta moltissimi DBMS diversi

Configurare [N]Hibernate

Occorre specificare

• lato Java: quale implementazione JPA usare
• Connection Proveider
• dettagli della connessione
• strategia generazione delle tabelle
• lista delle entità

Configurazione a run-time

File di configurazione

Mapping

public class Studente{
    private long idStud;
}

… come gli dico che id è la chiave primaria?

Mapping

Impostazione chiave primaria

• file XML che definisce le impostazioni

• annotazioni (attributi in C#)

  @Id
  @GeneratedValue(strategy = GenerationType.AUTO)
  private long idStud;
  

Mapping

Come faccio mapping?

@Entity
@Table(name = "Studente")
public class Studente{
    @Id
    @Column(name = "idStud")
    private long idStud;
    @Column(name = "nome")
    String nome;
}

EntityManager, Factory & co

EntityManagerFactory

• apre il database
• aggiorna la struttura del database
• operazione pesante e complicata
• ne basta una per applicazione (static)

EntityManager, Factory & co

EntityManager

• connessione di breve durata al DB
• permette di eseguire operazioni sul DB

EntityManager, Factory & co

EntityTransaction

• racchiude operazioni che modificano il contenuto del DB

Query

• utilizzano una sintassi particolare (JPQL)

Entity Object Life Cycle

Persist o Merge?

persist()

• si applica su nuove entità
• genera una INSERT in SQL
• non restituisce nulla

Persist o Merge?

merge()

• entità nuova o detached
• genera una INSERT o una UPDATE
• restituisce la versione managed dell’oggetto

tutto va sempre racchiuso in una transazione

Ottenere oggetti

Ricerca per chiave primaria

Employee employee = em.find(Employee.class, 1);

JPA Query Language

Query q1 = em.createQuery

Ottenere oggetti

JPA Criteria Query

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Country> q = cb.createQuery(Country.
class);
Root<Country> c = q.from(Country.class);
q.select(c);

Named Query

@NamedQuery(name="Country.findAll", query="SELECT c FROM
Country c")

Accesso Lazy

Un oggetto è lazy se non contiene tutti i dati ma sa come ottenerli

Effettua la query solo quando servono i dati:

• ottengo studente XYZ, non vado a leggere subito i dati di tutti gli esami e di tutti i corsi collegati

• opzione dell’annotazione della relationship

  @OneToMany(fetch=javax.persistence.FetchType.LAZY)
  

Triggers

Equivalente dei triggers nel mondo JPA

creo un metodo privato ma senza parametri

• @PreUpdate
• @PrePersist
• …

Stored Procedure

Non posso crearle con JPA

Posso richiamarle (JPA >= 2.1)

@NamedStoredProcedureQuery(
    name = "ReadAddressById",
    resultClasses = Address.class,
    procedureName = "READ_ADDRESS",
    parameters = {
        @StoredProcedureParameter(mode=IN,
        name="P_ADDRESS_ID", type=Long.class)
    }
)
@Entity
public class Address {
...
}

Versioni

Hibernate può tenere traccia delle modifiche operate sugli oggetti

@org.hibernate.envers.Audited

Versioni

Posso ricercare versioni precedenti dello stesso oggetto:

AuditReader reader = AuditReaderFactory.get(em)
Event firstRevision = reader.find(Event.class, 2L, 1);
Event secondRevision = reader.find(Event.class, 2L, 2);

ORM: tutti li odiano!

• curva di apprendimenti ripida
• scomodi per fare cose semplici
  • ... alternativa è fare tutto con JDBC
• sono nati i "micro-ORM"

micro-ORM: norm

Una possibile alternativa è norm

• usa le annotazioni JPA esistenti
• facile da configurare: usa JDBC
• Se necessario permette di usare SQL

Configurazione

Per inizializzare basta usare una connessione JDBC

Database db = new Database();
db.setJdbcUrl(STRINGA_JDBC);
db.setUser("root");
db.setPassword("password");

Mappatura

Posso mappare usando le annotazioni JPA

@Table(name="employees")
class Employee {
    public String firstName;
    public String lastName;
    /* .. */
}

Inserimento

Creo nuove istanze come oggetti

Database db = new Database();

Employee joe = new Employee();
joe.firstName = "Joe";
joe.lastName = "Doe";

db.insert(joe);

Ricerca

Ottenere tutti gli elementi

List<Employee> employees = db.results(Employee.class);

Filtrare e ordinare elementi

List<Employee> employees = db.where("lastname=?", "Doe")
        .orderBy("lastName")
        .results(Employee.class);

Modifica

Specificando con un "where"

int rowsAffected = db.table("employees")
        .where("firstName=?", "Joe")
        .delete();

Usando un oggetto

int rowsAffected = db.delete(joe);

SQL

Per ottenere una istanza particolare

List<Order> order = db.sql(
    "select sum(quantityOrdered*priceEach),orderDate " +
    "FROM orderdetails INNER JOIN orders " +
    "USING (orderNumber) " +
    "WHERE customerNumber = ?", "124")
    .results(Order.class);

Mappe e primitive

Se non vogliamo costruire un oggetto, possiamo usare una collection o una primitiva

List<Map> list = db.sql("select * from employees")
        .results(HashMap.class);

Long count = db.sql("select count(*) from employees")
        .first(Long.class);

Transazioni

Transaction trans = db.startTransaction();
try {
    db.transaction(trans).insert(row1);
    db.transaction(trans).update(row2);
    trans.commit();
} catch (Throwable t) {
    trans.rollback();
}

Progettazione base di dati

Ricordate?

In ogni base di dati esistono:

• lo schema: descrive la struttura della base di dati e non varia nel tempo
  • nel modello relazionale: intestazioni delle tabelle
• l’istanza: i valori attuali, che possono variare anche rapidamente
  • nel modello relazionale: le righe delle tabelle

Circle of life

• Studio di fattibilità: definizione costi e priorità
• Raccolta e analisi dei requisiti: studio delle proprietà del sistema
• Progettazione: di dati e funzioni
• Realizzazione (prototipo?)
• Validazione e collaudo: sperimentazione
• Funzionamento: il sistema diventa operativo

Modelli principali

• modelli logici
• modelli concettuali

Modelli logici

utilizzati nei DBMS esistenti per l’organizzazione dei dati

• utilizzati dai programmi
• indipendenti dalle strutture fisiche
• esempi: relazionale, reticolare, gerarchico…

Modelli concettuali

rappresentano i dati in modo indipendente da ogni sistema

• cercano di descrivere i concetti del mondo reale
• sono utilizzati nelle fasi preliminari di progettazione
• il più noto è il modello Entity-Relationship

Modelli concettuali, perché?

Proviamo a modellare una applicazione definendo direttamente lo schema logico della base di dati

Da dove cominciamo?

dobbiamo pensare subito a come correlare le varie tabelle (chiavi etc.)

Modelli concettuali, perché?

Modelli concettuali

• permettono di rappresentare le classi di dati di interesse e le loro correlazioni
• prevedono efficaci rappresentazioni grafiche (utili anche per documentazione)
• indipendenti dal DBMS scelto

Modello Entity-Relationsip

Entità - Relazione

Il più diffuso modello concettuale, ne esistono molte versioni (più o meno) diverse

Aspeti chiavi modello E-R

• entità
• relationship
• attributo
• identificatore
• generalizzazione

Entità

Classe di oggetti (fatti, persone, cose) della applicazione di interesse con proprietà comuni e con esistenza autonoma

Esempi:

impiegato, città, conto corrente, ordine, fattura

Istanza: elemento della classe

nello schema concettuale rappresentiamo le entità, non le singole istanze

Relationship

Legame logico fra due o più entità, rilevante nell’applicazione di interesse

Esempi:

• Residenza (fra persona e città)
• Esame (fra studente e corso)

Chiamata anche: relazione, correlazione, associazione

Attributo

• proprietà elementare di un’entità o di una relationship, di interesse per l’applicazione
• associa ad ogni occorrenza di entità o relationshio un valore del dominio dell’attributo

Attributi composti

• raggruppano attributi di una medesima entità o relationship che presentano affinità nel loro significato o uso
• es: Via, Numero civico e CAP formano “Indirizzo”

Altri costrutti del modello E-R

• cardinalità
• identificatore
• generalizzazione

Altri costrutti del modello E-R

Cardinalità

• di relationship
• di attributo

Identificatore

• interno
• esterno

Cardinalità

Coppia di valori associati ad ogni entità che partecipa ad una relationship

Specificano il numero min e max di volte che un'occorrenze di una rentità puà essere associata ad un'occorrenza di un'altra entità coinvolta nella relationship

Tre tipi di Relationships

Con riferimento alle cardinalità massime, abbiamo realtionship:

• uno a uno
• uno a molti
• molti a molti

Identificatore di una entità

Strumento per l’identificazione univoca delle occorrenze di un’entità costituito da:

• attributi dell’entità → identificatore interno
• (attributi +) entità esterne attraverso relationship → identificatore esterno

Identificatore di una entità

Attenzione:

• ogni entità deve possedere almeno un identificatore, ma può averne in generale più di uno
• una identificazione esterna è possibile solo attraverso una relationship a cui l’entità da identificare partecipa con cardinalità (1,1)

Generalizzazione

Mette in relazione una o più entità $E_1, E_2, \dots, E_n$ con una entità $E$

• $E$ è generalizzazione di $E_1, E_2, \dots, E_n$
• $E_1, E_2, \dots, E_n$ sono specializzazioni (o sottotipi) di $E$

Generalizzazione

Ereditarietà

Tutte le proprietà (attributi, relationship, altre generalizzazioni) dell’entità genitore vengono ereditate dalle entità figlie e non rappresentate esplicitamente