Database and data analytics - Modulo Big data

Per il continuo si fa riferimento ad un linguaggio di programmazione specifico per i database,

ovvero SQL.

02 Relazioni Algebriche

Il modello relazionale supporta diverse operazioni per manipolare i dati, tra cui selezione,

proiezione, unione, intersezione e differenza. iniziamo dalle prime 3.

Unione: L'operazione di unione combina le tuple di due tabelle mantenendo tutte le tuple,

senza duplicazioni. L'operazione di unione è indicata con il simbolo Formalmente,

∪.

l'unione di due relazioni R e S, denotata come R S, restituisce una relazione contenente

∪

tutte le tuple di R e tutte le tuple di S, senza duplicazioni.

Intersezione: L'operazione di intersezione restituisce solo le tuple comuni a due tabelle.

L'operazione di intersezione è indicata con il simbolo ∩. Formalmente, l'intersezione di due

relazioni R e S, denotata come R ∩ S, restituisce una relazione contenente solo le tuple

presenti sia in R che in S.

Differenza: L'operazione di differenza restituisce le tuple presenti in una tabella ma non

nell'altra. L'operazione di differenza è indicata con il simbolo - o \ . Formalmente, la

differenza di due relazioni R e S, denotata come R - S o R \ S, restituisce una relazione

contenente le tuple presenti in R ma non in S.

Specialmente per l’operazione di unione è necessario considerare che per le operazioni i

nomi degli attributi devono essere uguali. Un’azione preposta in questi casi è quella del

RENAME. Se per esempio avessimo una tabella con due colonne “Padri, Figlio” e un’altra

“Madri, Figlio”, sarebbe opportuno fare un rename di variabili e chiamarla “Genitore”.

Abbiamo poi:

Selezione: L'operazione di selezione è utilizzata per estrarre le tuple che soddisfano una

certa condizione da una tabella. la selezione lavora ORIZZONTALMENTE.

+----+--------+--------+

| ID | Nome | Età |

+----+--------+--------+

| 1 | Alice | 25 |

| 2 | Bob | 30 |

| 3 | Carol | 22 |

+----+--------+--------+

Se vogliamo selezionare solo le persone con età maggiore di 25 anni

+----+--------+--------+

| ID | Nome | Età |

+----+--------+--------+

| 2 | Bob | 30 |

+----+--------+--------+

Proiezione: L'operazione di proiezione è utilizzata per estrarre solo alcune colonne (attributi)

da una tabella. la proiezione lavora solo VERTICALMENTE. Nell’esempio di prima se

volessimo estrarre solo i nomi e l’età il risultato sarebbe questo:

+--------+--------+

| Nome | Età |

+--------+--------+

| Alice | 25 |

| Bob | 30 |

| Carol | 22 |

+--------+--------+

Queste operazioni possono essere utilizzate contemporaneamente, e presentano varie

casistiche particolari da attenzionare quando si lavora con i dati.

Join: L'operazione di join combina le tuple di due o più tabelle basandosi su una condizione

specifica. Ci sono diversi tipi di join, tra cui:

1)​ Inner Join: Restituisce solo le tuple che hanno corrispondenze in entrambe le tabelle.

2)​ Left (Outer) Join: Restituisce tutte le tuple della tabella a sinistra e le tuple

corrispondenti dalla tabella a destra. Se non c'è corrispondenza, vengono restituite

colonne con valori nulli dalla tabella a destra.

3)​ Right (Outer) Join: Simile al left join, restituisce tutte le tuple dalla tabella a destra e

le tuple corrispondenti dalla tabella a sinistra. Se non c'è corrispondenza, vengono

restituite colonne con valori nulli dalla tabella a sinistra.

4)​ Full (Outer) Join: Restituisce tutte le tuple quando c'è una corrispondenza in una

delle tabelle. Se non ci sono corrispondenze, restituisce colonne con valori nulli dalle

tabelle non corrispondenti.

Natural-Join: Il natural join è una forma di join in cui la condizione di join è basata su

colonne con lo stesso nome in entrambe le tabelle. La parola chiave NATURAL JOIN può

essere utilizzata per eseguire un natural join in SQL.

Theta-Join: Il theta join è un tipo generico di join in cui la condizione di join non è limitata

all'uguaglianza tra colonne. La condizione può essere qualsiasi condizione booleana

04 SQL Basics

Il linguaggio SQL supporta:

-​ I linguaggi di definizione dati (DDL) per definire la relazione tra loro (schema)

-​ I linguaggi di manipolazione (DML) (instance)

I tipi di domini elementari che conosciamo di SQL sono:

-​ character

-​ numbers

-​ temporal instants

-​ temporal intervals

-​ boolean

-​ binary data

Rifatti alle slide del professore che spiegano i vari comandi tra cui:

SELECT

FROM

WHERE

Ci sono poi gli operatori aggregati

COUNT

MAX

MIN

SUM

AVG

Questi operatori è importante capire che non possono essere utilizzati in un certo modo con

la proiezione. Quando raggruppiamo una serie di dati con questi operatori, il

raggruppamento sta dopo la preposizione “GROUP BY” . Ad esempio potremmo chiedere a

Mysql di raggruppare per dipartimento la somma dei salari di vari impiegati, ma non

possiamo ad esempio chiedere il nome dell’impiegato con il salario massimo perché non

funzionerebbe (restituisce il primo valore che trova).

Possiamo con questi operatori stabilire inoltre delle condizioni con l’operatore “HAVING”.

Attenzione, da non confondere con where che opera solo a livelli delle righe, mentre having

è un condizione sul raggruppamento. Facciamo un esempio in cui li troviamo insieme:

“select Father, avg(Income) from Children where Age < 30 group by Father having

avg(Income) > 20”

Infine, abbiamo gli operatori di insieme. L’unico supportato da Mysql è UNION. Mentre se

volessimo considerare esempi di intersezione o esclusione abbiamo a disposizione quelle

che vengono chiamate nested queries:

in: restituisce un valore true

not in: restituisce un valore true se la condizione è verificata

Esempio di utilizzo:

“select Surname from Employee where Dept in (select Name from Department where City =

‘Milan’)”

select FirstName, Salary from Employee where Salary in (select max(Salary) from

Employee)

BONUS: ESERCIZI

Database sul Catering

—---------------------------------------------------------------------------------------------------------------------

-- Lezione sulla creazione ed eliminazione dei Database:

-- Per creare un database utilizziamo il comando CREATE DATABASE

CREATE DATABASE db_prova;

-- Per eliminarlo invece, utilizziamo DROP DATABASE

DROP DATABASE db_prova;

-- Vediamo invece come creare una tabella:

CREATE TABLE nome_tabella (campo_1 tipo_dato constraint,

campo_2 tipo_dato constraint,

campo_1 tipo_dato constraint);

-- Ogni tabella possiede le seguenti caratteristiche:

-- 1. Il nome del campo che sarà la nostra colonna per intenderci

-- 2. Il tipo di dato (vedi immagine 1 sotto per tutti i tipi di dati esistenti)

-- 3. Il constraint (spiegazione più definita sotto)

Immagine 1: Tipi di dato

Tra i constraint (vincoli in italiano) abbiamo nel linguaggio SQL:

-​ NOT NULL: quando vogliamo che un valore nel nostro DB non sia mai nullo

-​ PRIMARY KEY: è la chiave primaria del DB, ovvero quel valore che

contraddistingue una riga, un valore che è unico ed immutabile. Un esempio

concreto potrebbe essere in un DB di auto, la targa dell’auto, o se ci riferiamo a delle

persone, al codice fiscale o ad un numero id.

-​ unique: può confondere con la chiave primaria, ma questo campo può mutare nel

tempo nonostante sia comunque unico. L’esempio da fare è il numero di telefono,

deve essere unico, ma può sicuramente cambiare.

-​ default: inserisce un valore che scegliamo, basta scrivere il valore default tra ‘_’

-​ check: controlla se un valore che stiamo inserendo rispetti certi criteri

-​ FOREIGN KEY: sta per chiave esterna, sono delle chiavi che noi utilizziamo

esternamente per collegare varie tabelle. Solitamente è collegato nel codice di

creazione di una tabella attraverso la sintassi REFERENCES.

-- Per eliminare una tabella si utilizza comunque il comando DROP TABLE

Come inseriamo i valori all’interno delle tabelle?

-- Per inserire dei record nel nostro DB utilizziamo la funzione INSERT INTO

INSERT INTO clienti (denominazione, partita_iva, indirizzo, telefono)

VALUES ("Ortrofrutta Ciancio snc", "6634589035", "Via del Bosco, Catania", "3387560054");

-- NOTA BENE:

-- Non ho inserito L'ID perché abbiamo utilizzato la funzione AUTO_INCREMENT

-- Inoltre, è possibile non inserire i nomi dei campi ma dovremmo a questo punto inserire

anche l'ID

-- Vediamo il comando SELECT

-- Serve principalmente per richiamare degli elementi che abbiamo all'interno del DB

-- La sintassi è:

SELECT colonna1, colonna2...

FROM nome_tabella;

-- per prendere tutto utilizziamo "*"

SELECT * FROM nome_tabella;

Prima di passare alle condizioni, è importante conoscere gli operatori logici di SQL

(immagine 2)

Immagine 2: Operatori logici

-- Adesso mettiamo una condizione

-- WHERE

SELECT colonne FROM nome_tabella WHERE condizione;

-- AND, OR e NOT

SELECT colonne FROM nome_tabella

WHERE condizione1 AND condizione2 AND condizione3 ... ;

SELECT colonne FROM nome_tabella

WHERE condizione1 OR condizione2;

SELECT colonne FROM nome_tabella

WHERE NOT condizione1;

-- Se vogliamo combinare le condizioni basta circoscrivere con delle parentesi

-- IN

SELECT colonne FROM nome_tabella

WHERE colonna IN (valore1, valore2...); -- Come se fosse una lista di valori

-- BETWEEN

SELECT colonne FROM nome_tabella

WHERE colonna BETWEEN val_min AND val_max;

-- Per l'ordinamento invece si utilizza ORDER BY

-- Possiamo ordinare sia a livello numerico, che di date, che alfabetico

SELECT colonne FROM nome_tabella

ORDER BY colonna ASC/DESC; -- ASC o DESC si sceglie se vogliamo i dati in ordine

crescente o decrescente

-- Possiamo ordinari più campi contemporaneamente, ma il primo campo scritto è il campo

"LEADER"

-- Un po' più avanzata è la funzione LIMIT

-- Facciamo un esempio. Mettiamo caso che vogliamo conoscere nella nostra azienda le 3

persone che

-- guadagnano di più. Dobbiamo prima ordinarli in modo discendente e poi utilizzare LIMIT

SELECT colonne FROM nome_tabella

ORDER BY stipendio DESC LIMIT 3;

-- Con questa funzione possiamo anche ottenere l'ultimo record che abbiamo inserito nella

nostra tabella

-- Come fare?

SELECT colonne FROM nome_tabella

ORDER BY id_dipendente DESC LIMIT 1; -- In base all'ID la funzione trova l'ultimo record

inserito

-- Esiste infine il limite "offse