Basi di Dati

Andrea De Lorenzo, University of Trieste

Gruppo di Ingegneria Informatica

Sylvio Barbon Jr.

Fondamenti di Informatica

ML operations

meta learning, applied ML, process mining

Alberto Bartoli

Reti di calcolatori

Cybersecurity

security, applied ML, evolutionary computation

Andrea De Lorenzo

Basi di dati

Web programming

security, applied AI&ML, information retrieval, GP

Eric Medvet

Programmazione avanzata

Introduction to machine learning

evolutionary computation, embodied AI, applied ML

Laura Nenzi

Cyber-physical systems

Information retrieval and data visualization

Laboratorio di programmazione

formal methods, runtime verification

Alessandro Renda

Fondamenti di informatica

Laboratorio di cybersecurity

explainable AI; federated learning; data streaming

Martino Trevisan

Architetture dei calcolatori

Sistemi operativi

Aadvanced computer networks

network measurements, data privacy, big data

Orario lezioni

https://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

h69aw6b

Modalità esame

Test + Progetto + Orale

• Test a risposta multipla e codice SQL, basta passarlo una volta
• Progetto a tema libero (da consegnare 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

Aggiorno il programma di gestione ordini

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

Associazione nel modello Entity-Relationship

• 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

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

Condizioni: testo incompleto

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

Più condizioni

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

Più condizioni

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

Più condizioni

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

Intervalli

Seleziona i valori compresi tra x e y (inclusi)

Intervalli

Mostra i pagamenti con importi compresi tra 5.000 ed 8.000

Intervalli

Mostra i pagamenti con importi compresi tra 5.000 ed 8.000

SELECT * FROM payments
 WHERE amount BETWEEN 5000 AND 8000;
 

forma equivalente

Intervalli

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

Liste

Controlla se il valore è presente in una lista di valori

Liste

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

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"

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

Gestire i NULL

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

Espressioni

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

Espressioni

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

Funzioni

Stringhe

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

Funzioni

Mostra i prodotti con nomi di almeno 15 caratteri.

Funzioni

Data e ora

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

Funzioni

Mostra i prodotti orinati nel mese di gennaio

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

Ordinamento

Mostra i clienti ordinandoli per paese crescente e credito massimo decrescente

Ordinamento

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

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

Uniamo il tutto...

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

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

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

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

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

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

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

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

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

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

Join Multiple

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

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

Self Join

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

Self Join

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

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

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

Union ed ordinamento

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

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

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?

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?

Funzioni di aggregazione

Quanti soldi ho ricevuto con i pagamenti?

Funzioni di aggregazione

Qual è il prezzo medio di vendita di un prodotto?

Funzioni di aggregazione

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

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

Funzioni di aggregazione e raggruppamenti

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

Aggreghiamo...

Mostrare quanti ordini ho spedito ogni giorno

Aggreghiamo...

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

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

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)

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

Aggreghiamo...

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

Aggreghiamo...

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

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

Aggreghiamo...

Mostrare l’estratto conto del cliente 124

prendere i totali degli ordini del cliente

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

Filtrare dati aggregati

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

Filtrare dati aggregati

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

Hint: ordini spediti hanno status "Shipped"

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

Subquery - singolo valore

Mostrare i dati del pagamento più alto ricevuto

Hint: subquery nella clausola WHERE

Subquery - singolo valore

Mostra i pagamenti superiori alla media

Subquery - singolo valore

Mostrare i clienti che non hanno fatto ordini

Hint: subquery nella clausola WHERE ... NOT IN

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

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

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)

Limiti

Mostrare i 5 clienti con il credito più elevato

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

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

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

Aggiungere dati

Duplicare l'ordine 10425

prendere tutte le righe di 10425 (orderdetails)

Hint: conta l'ordine delle colonne, vi servirà FK 10426

Aggiungere dati

Duplicare l'ordine 10425

inserire tutte queste $n$-uple nella tabella (orderdetails)

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

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

Modificare dati

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

Hint: UPDATE tabella SET col1 = valore1 WHERE condizione

Modificare dati

Aumentare del 5% tutti i prezzi di vendita

Hint: UPDATE tabella SET col1 = valore1 WHERE condizione

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

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

Modificare dati

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

Aggiornare i dati

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

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