SILICIO E NUVOLE

9 – Ottimizzazione Query e Indici 1) Concetto di indice Un indice è una struttura dati (tipicamente B-Tree o Hash) che accelera la ricerca di righe in una tabella. Senza indice, il DBMS esegue una full table scan : legge ogni riga della tabella per trovare i risultati. Con un indice, il DBMS può accedere direttamente alle righe corrispondenti ai criteri di ricerca. Esempio analogico : cercare un nome in un libro senza indice = leggere tutte le pagine; con indice = usare l’indice alfabetico finale. 2) Tipi di indici comuni Tipo di indice Descrizione B-Tree Standard per colonne con uguaglianze e range; supporta ordinamento. Hash Ottimo per uguaglianze ( = ); non per range ( <, > ). Unico (UNIQUE) Garantisce valori unici nella colonna. Composito Indice su più colonne; utile per query che filtrano su più campi. Full-text Ricerca testo completo (MySQL: FULLTEXT INDEX ). Spatial Dati geografici (PostGIS su PostgreSQL). 3) Creazione e g...

8 – Gestione Utenti e Sicurezza Obiettivi Imparare a creare utenti e ruoli sul database. Assegnare/revocare permessi (GRANT / REVOKE). Comprendere le transazioni (BEGIN / COMMIT / ROLLBACK) e le proprietà ACID. Simulare un ambiente multiutente con permessi diversi e osservare il comportamento delle transazioni. 1) Creazione utenti e ruoli: concetti essenziali Utente : account che si autentica al DBMS; ha credenziali e, opzionalmente, permessi. Ruolo (role): gruppo logico di permessi che può essere assegnato a più utenti; facilita la gestione dei privilegi. Principio del least privilege : assegnare soltanto i permessi strettamente necessari. Differenze rapide tra MySQL e PostgreSQL MySQL: CREATE USER 'alice'@'host' IDENTIFIED BY 'pwd'; PostgreSQL: CREATE USER alice WITH PASSWORD 'pwd'; (o CREATE ROLE alice LOGIN PASSWORD 'pwd'; ) Postgres ha un modello di ruoli più flessibile (role = user o group a seconda de...

7 Subquery e Query Avanzate 1) Subquery: concetto e tipi Una subquery (o query nidificata) è una query inclusa dentro un’altra query. Viene usata per calcolare valori che la query esterna userà. Tipi principali: Scalar subquery → restituisce un singolo valore (colonna/row). Subquery che restituisce una colonna di valori → usata con IN o = ANY . Correlated subquery → dipende dalla riga della query esterna; valutata riga per riga. Derived table (subquery nel FROM ) → trattata come tabella temporanea con alias. Esempio (subquery nel WHERE) Restituisco prodotti il cui prezzo è maggiore del prezzo medio: SELECT product_id, name, price FROM Products WHERE price > ( SELECT AVG(price) FROM Products ); Esempio (derived table / subquery nel FROM) Calcolo vendite mensili e poi seleziono i top mesi: SELECT month, total_sales FROM ( SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS total_sales FROM Orders GROUP BY DATE...

6 – Relazioni tra Tabelle Contenuti Chiavi primarie e chiavi esterne La chiave primaria (Primary Key, PK) è un attributo (o un insieme di attributi) che identifica in modo univoco una tupla in una tabella. La chiave esterna (Foreign Key, FK) è un attributo che crea un legame tra due tabelle, puntando a una PK di un’altra tabella. Relazioni tra tabelle 1:1 (uno a uno) → ogni record di una tabella corrisponde a uno solo in un’altra tabella. Esempio: tabella Persone e tabella CodiciFiscali . 1:N (uno a molti) → un record di una tabella è collegato a più record di un’altra. Esempio: un Corso seguito da più Studenti . N:M (molti a molti) → gestita tramite una tabella di collegamento. Esempio: Studenti ↔ Corsi , con tabella Iscrizioni . Join tra tabelle INNER JOIN → restituisce solo i record con corrispondenza in entrambe le tabelle. LEFT JOIN → restituisce tutti i record della tabella di sinistra e i corrispondenti (se esistono) della destra. ...

5 – Funzioni e Operazioni su Dati Funzioni di aggregazione: COUNT() : conta il numero di righe. SUM() : calcola la somma. AVG() : media aritmetica. MIN() : valore minimo. MAX() : valore massimo. -- Esempio: calcolare il totale e la media delle vendite SELECT SUM(importo) AS TotaleVendite, AVG(importo) AS MediaVendite, MIN(importo) AS VenditaMin, MAX(importo) AS VenditaMax, COUNT(*) AS NumeroOrdini FROM vendite; Raggruppamento con GROUP BY : Permette di aggregare i dati per categorie. -- Vendite per ogni mese SELECT MONTH(data) AS Mese, SUM(importo) AS TotaleMensile FROM vendite GROUP BY MONTH(data); Filtraggio con HAVING : Simile a WHERE , ma usato dopo l'aggregazione. -- Mostra solo i mesi con vendite totali superiori a 10.000 SELECT MONTH(data) AS Mese, SUM(importo) AS TotaleMensile FROM vendite GROUP BY MONTH(data) HAVING SUM(importo) > 10000; Funzioni su stringhe e date: ...

4 – Query di Selezione SELECT: selezione e filtro dei dati (base) Schema di lavoro (tabella di esempio) Usiamo una tabella chiamata prodotti con questa struttura: CREATE TABLE prodotti ( id INTEGER PRIMARY KEY, nome VARCHAR(100), categoria VARCHAR(50), prezzo DECIMAL(10,2), disponibilita INTEGER, data_inserimento DATE ); Dati di esempio (INSERT) Ecco alcuni record di prova che puoi inserire per seguire gli esempi: INSERT INTO prodotti (id, nome, categoria, prezzo, disponibilita, data_inserimento) VALUES (1, 'Maglietta Basic', 'Abbigliamento', 19.90, 120, '2024-01-15'), (2, 'Sneakers Run', 'Calzature', 89.50, 45, '2024-02-10'), (3, 'Zaino Trek', 'Accessori', 59.00, 30, '2024-03-05'), (4, 'Giacca Invernale', 'Abbigliamento', 149.00, 12, '2023-11-20'), (5, 'Cappello Lana', 'Accessori', 25.00, 0, '2024-01-05'), (6, 'Sandali Mare', 'Calzatu...

3 – Inserimento, Aggiornamento e Cancellazione Obiettivi Comprendere e utilizzare le istruzioni fondamentali DML: INSERT , UPDATE , DELETE . Imparare le buone pratiche: transazioni, prepared statements, controllo integrità, prevenzione SQL injection. Realizzare una tabella clienti e popolarla con esempi pratici. 1. Concetti preliminari: ACID e transazioni Prima di modificare dati in un database è importante conoscere le proprietà ACID: Atomicità : tutte le operazioni di una transazione avvengono tutte o nessuna. Consistenza : la transazione porta il DB da uno stato consistente a un altro. Isolamento : transazioni concorrenti non si interferiscono. Durabilità : una volta confermata, la transazione è persistente. Usare BEGIN / COMMIT / ROLLBACK per raggruppare operazioni e poter annullare in caso di errore. Esempio (PostgreSQL / MySQL-ish): BEGIN; -- serie di operazioni INSERT/UPDATE/DELETE COMMIT; -- o in caso di errore ROLLBACK; 2. INSERT: inserir...

2 -  Creazione e gestione tabelle Questo modulo spiega tutto quello che serve per progettare e costruire schemi relazionali: creare database, dichiarare tabelle, scegliere tipi di dato (INT, VARCHAR, DATE, BOOLEAN), imporre vincoli, modificare strutture con ALTER TABLE , cancellare con DROP TABLE . Ogni concetto è corredato da esempi pratici e da consigli operativi e di deploy. Nota: mostro esempi compatibili con MySQL / MariaDB e PostgreSQL quando ci sono differenze rilevanti. Le DDL sono in SQL standard ove possibile; per operazioni specifiche evidenzio la sintassi dialettale. 1) Creare un database Sintassi di base MySQL CREATE DATABASE IF NOT EXISTS ecommerce CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci; USE ecommerce; PostgreSQL CREATE DATABASE ecommerce WITH OWNER = myuser ENCODING = 'UTF8' LC_COLLATE = 'en_US.utf8' LC_CTYPE = 'en_US.utf8' TEMPLATE = template0; -- poi connettersi: \c eco...