Appunti Basi di dati + Esercizi svolti (SQL, Algebra, E-R)

Nozioni preliminari

Dati, informazioni e sistemi informativi

I dati e le informazioni sono ormai considerati risorse strategiche per le organizzazioni, utilizzati sia per l'operatività corrente sia per analisi storiche (Business Intelligence/Data Analytics).

• Dato vs informazione:
  • Dato: È l'elemento grezzo, il simbolo non interpretato (es. un numero rilevato da un sensore).
  • Informazione: È il dato elaborato e interpretato che arricchisce la conoscenza (es. l'aggregazione di più rilevazioni che indica un malfunzionamento).
• Sistema informativo vs sistema informatico:
  • Il Sistema Informativo prescinde dai computer. È l'insieme di risorse (persone, strumenti) che raccoglie, archivia, elabora e scambia informazioni in un'organizzazione (es. i vecchi libri mastro cartacei).
  • Il Sistema Informatico è esclusivamente la porzione automatizzata del sistema informativo (basata su tecnologia informatica).
  • Le Basi di Dati sono a loro volta un sottoinsieme del sistema informatico.

Basi di dati e DBMS

C'è una netta distinzione tra la base di dati e il software che la gestisce:

• Base di dati (Database - DB): La collezione logica e fisica dei dati condivisi da un’organizzazione.
• DBMS (Data Base Management System): Il software complesso che permette di creare, gestire e interrogare la base di dati (es. Oracle, SQL Server, MySQL e PostgreSQL).

Perché usare un DBMS e non dei semplici file (es. Excel)? Gestire i dati con file tradizionali crea problemi quando ci sono più utenti: si generano copie multiple del file, causando ridondanza e incoerenza (dati non aggiornati allo stesso modo ovunque). Il DBMS centralizza i dati e risolve questi problemi.

Caratteristiche fondamentali garantite da un DBMS:

1. Grandi: Gestiscono quantità di dati (GB, TB) enormemente superiori alla memoria centrale (RAM).
2. Condivise: Permettono l'accesso simultaneo a migliaia di utenti senza creare conflitti, grazie al meccanismo delle transazioni.
3. Persistenti: I dati sopravvivono alla fine dell'esecuzione del programma o alla mancanza di corrente.
4. Affidabili: Garantiscono il salvataggio e il ripristino (backup e recovery) in caso di guasti hardware/software o catastrofi.
5. Privatezza: Accesso consentito solo tramite meccanismi di autorizzazione e credenziali protette (es. password salvate tramite funzioni di hash).
6. Efficienti ed efficaci: Svolgono operazioni complesse velocemente e garantiscono il rispetto di regole e vincoli di integrità sui dati (es. un'età non può essere negativa).

Architettura software a tre livelli

Gli utenti (es. l'impiegato dell'ufficio personale) non toccano mai direttamente la base di dati, ma vi accedono tramite un'architettura a strati:

1. Livello della presentazione: L'interfaccia grafica usata dall'utente (es. Web App sul browser o App su smartphone).
2. Livello della logica applicativa: Lo strato intermedio che astrae e definisce le operazioni consentite (es. la funzione "crea nuovo utente").
3. Livello dei dati: Il server con il DBMS e i dati veri e propri.

Modello dei dati: schema e istanza

Il DBMS garantisce l'indipendenza dei dati, ovvero la separazione tra come i dati sono descritti e come sono fisicamente salvati sul disco (schema fisico gestito interamente dal DBMS, in modo trasparente per l'utente).

In ogni base di dati si distinguono due livelli:

• Schema (Livello Intensionale): Descrive la struttura della base di dati (es. il nome della tabella, i nomi delle colonne e il tipo di dato). È tendenzialmente invariante nel tempo.
• Istanza (Livello Estensionale): Sono i dati veri e propri contenuti nelle tabelle (il "corpo") in un dato momento. Cambia molto rapidamente.

I modelli di rappresentazione

• Modelli concettuali (es. Entità-Relazione): Utilizzati nella prima fase di progettazione. Servono a rappresentare la realtà di interesse (i concetti) in modo comprensibile per il cliente, indipendentemente dal DBMS che verrà usato.
• Modelli logici (es. Modello Relazionale): Utilizzati per definire la struttura vera e propria nel DBMS. Il Modello Relazionale, che è lo standard di mercato attuale, organizza i dati in tabelle (relazioni matematiche) composte da insiemi di record omogenei (ogni riga, o tupla, ha esattamente le stesse colonne/attributi).

Architettura a livelli e indipendenza dei dati

In aggiunta al classico schema logico (le tabelle) e schema fisico (i file su disco), nelle applicazioni aziendali complesse viene introdotto un terzo livello: lo Schema Esterno.

• A cosa serve lo Schema Esterno? Quando un'applicazione (es. software bancario) ha alle spalle molti anni di vita, può capitare che il database sottostante subisca una profonda ristrutturazione (modifiche allo schema logico). Per evitare di dover riscrivere da zero tutti gli applicativi (livello di presentazione e logica applicativa), si definiscono degli schemi esterni.
• Lo schema esterno è una "maschera" mappata sul nuovo schema logico tramite uno strumento SQL chiamato Vista (es. comando CREATE VIEW). Il DBMS si occupa di tradurre "al volo" le richieste fatte allo schema esterno verso il vero schema logico.
• Oltre alla retrocompatibilità, gli schemi esterni sono usati per ragioni di sicurezza, fornendo a determinati utenti l'accesso solo a porzioni ristrette e specifiche dei dati.

Questa stratificazione garantisce l'Indipendenza dei Dati:

• Indipendenza fisica: L'uso dello schema logico rende i programmi immuni a cambiamenti fisici (es. se si cambia il DBMS da SQL Server a PostgreSQL, l'applicazione non cambia).
• Indipendenza logica: L'uso degli schemi esterni permette di modificare la struttura logica del database (aggiunte o modifiche alle tabelle) senza dover alterare i vecchi programmi che vi accedono.

Controllo di concorrenza e transazioni

I DBMS permettono l'accesso simultaneo a migliaia o milioni di utenti (es. iscrizioni su Infostud o acquisti su Amazon) senza creare conflitti e inconsistenze. Il DBMS fa sembrare le operazioni contemporanee, ma in realtà esegue un'istruzione alla volta, garantendo che il risultato finale sia equivalente a un'esecuzione seriale (uno dopo l'altro).

Il concetto centrale è la Transazione, ovvero una sequenza di operazioni (es. prelievo da un conto e versamento su un altro) che deve essere considerata in modo atomico e indivisibile. Le transazioni seguono le regole ACID:

• Atomicity (Atomicità): Le istruzioni sono eseguite o tutte o nessuna.
• Consistency (Consistenza): La transazione rispetta le regole e i vincoli del database.
• Integrity (Integrità): Lascia il database in uno stato integro e non corrotto.
• Durability (Durabilità): Una volta completata, le modifiche sono permanenti e non vanno perse.

Per simulare l'esecuzione seriale, si usa il protocollo Strict 2PL (Two-Phase Locking). Quando una transazione deve fare operazioni di CRUD (Create, Read, Update, Delete), richiede un "lock" (blocco) sul dato. Nessun altro può leggere o scrivere quel dato finché la transazione non è finita.

• Il problema del Deadlock (Stallo): Avviene quando la transazione A blocca un dato e aspetta un dato bloccato dalla transazione B, ma contemporaneamente la transazione B aspetta il dato bloccato da A. Il DBMS risolve questo stallo in un solo modo: sceglie arbitrariamente una transazione e la annulla ("ammazza" il processo).

Gestione dei guasti: log e rollback

Come si garantisce l'atomicità se avviene un guasto improvviso (es. un blackout o un server crash)? Si usa il protocollo WAL (Write-Ahead Logging).

• Prima che il DBMS modifichi un dato, scrive in un file sequenziale chiamato Log l'intenzione di modifica, registrando sia il vecchio valore sia il nuovo.
• Se la transazione viene interrotta a metà (per un deadlock o un guasto), il DBMS esegue un Rollback: va a ritroso nel Log e annulla tutte le operazioni parziali, ripristinando i vecchi valori per garantire l'atomicità.
• Nota architetturale: Per prevenire la perdita totale dei dati (es. disastri fisici), organizzazioni grandi usano architetture con database primario (Primary/Master) e copie di backup (Slave) situate in sedi fisiche diverse e collegate in tempo reale.

Struttura di un DBMS

Un DBMS è un software stratificato, che non si occupa solo di gestire i blocchi e i ripristini, ma anche di ottimizzare l'accesso:

• Operatori relazionali: Il "motore" che calcola le risposte (Algebra Relazionale), basato sull'uso del linguaggio SQL.
• Ottimizzazione (Query Optimizer): Il DBMS valuta l'istruzione SQL fornita dall'utente e sceglie il piano di esecuzione più veloce ed efficiente per recuperare i dati.
• File e metodi di accesso: Il componente che traduce la logica relazionale in veri e propri file ottimizzati registrati fisicamente su disco.
• Gestione del Buffer (Memoria Tampone): Poiché le memorie secondarie (dischi) sono infinitamente più lente della memoria centrale (RAM), il gestore del buffer cerca di tenere i dati in memoria RAM e posticipa la scrittura su disco quando possibile, per massimizzare l'efficienza.

Esercizi sulle nozioni preliminari

• Esercizio del Giardiniere: Un giardiniere vuole un database economico, stand-alone e non condiviso per le sue piante. Quali funzioni deve comprare tra: controllo di concorrenza, ripristino da crash, meccanismo per le viste, linguaggio di interrogazione?

  Soluzione: L'unica funzione indispensabile è il linguaggio di interrogazione.

  • Il controllo di concorrenza e le viste non servono perché è l'unico utente (non c'è condivisione), e il ripristino da crash avanzato può essere omesso per risparmiare, trattandosi di un'app stand-alone.
• Vero o Falso sull'Indipendenza dei Dati:
  • Vere: Permette di scrivere programmi senza conoscere le strutture fisiche; permette di modificare le strutture fisiche senza dover modificare i programmi; permette di formulare interrogazioni senza conoscere le strutture fisiche.
  • False: Permette di scrivere programmi conoscendo solo lo schema concettuale (in realtà i programmi accedono tramite lo schema logico o esterno).
• Vero o Falso sui DBMS (Condivisione e Persistenza):
  • Vere: La condivisione permette di ridurre ridondanze e inconsistenze; la condivisione rende necessaria la gestione della privatezza e autorizzazioni.
  • False: La condivisione favorisce l'efficienza dei programmi (in realtà introduce overhead per i lock); la persistenza ne garantisce l'affidabilità (sono due concetti separati, un file è persistente ma non affidabile ai guasti come un DBMS); la persistenza favorisce l'efficienza.
• Vero o Falso sui Linguaggi (DDL e DML):
  • Vere: La distinzione fra DDL e DML corrisponde alla distinzione fra schema e istanza; SQL include istruzioni DML e DDL; le istruzioni DML permettono sia di interrogare che di modificare (aggiornare) la base di dati.
  • False: Le istruzioni DDL permettono di specificare la struttura ma non di modificarla (falso, esiste l'ALTER); le istruzioni DML permettono di interrogare ma non modificare; non esistono linguaggi che includono entrambi.
• Vero o Falso sulle Figure Professionali:
  • Vere: I terminalisti utilizzano programmi predefiniti.
  • False: Gli utenti casuali utilizzano programmi predefiniti (usano query interattive); gli utenti casuali progettano la base di dati (lo fa il progettista); i progettisti del DBMS realizzano i programmi per i terminalisti (lo fanno i programmatori di applicazioni); gli amministratori realizzano il DBMS (lo gestiscono/configurano).

Svolgimento "Domande a risposta aperta"

1. Illustrare, in modo sintetico ma chiaro, supponendo di rivolgersi ad un non esperto, le caratteristiche fondamentali delle basi di dati e il ruolo che esse giocano nei sistemi informativi. Le basi di dati sono grandi archivi centralizzati e strutturati, gestiti da un software apposito (il DBMS), che permettono di memorizzare e ritrovare le informazioni. Le loro caratteristiche fondamentali sono:

• Grandi: Possono contenere quantità enormi di dati, ben superiori alla memoria centrale del computer.
• Condivise: Più utenti e programmi possono accedervi simultaneamente senza creare conflitti, riducendo le ripetizioni e le incoerenze tipiche dei vecchi archivi separati.
• Persistenti: I dati sopravvivono anche quando i programmi che li usano vengono chiusi.
• Affidabili e Private: Garantiscono il ripristino in caso di guasti e proteggono i dati consentendo l'accesso solo agli utenti autorizzati.

All'interno di un sistema informativo (che è l'insieme di risorse e procedure per gestire le informazioni di un'azienda), la base di dati rappresenta il "cuore" informatico: unifica i flussi di informazioni provenienti da vari uffici, garantendo efficienza, flessibilità e una gestione centralizzata.

2. Discutere brevemente la seguente affermazione: “i dati sono una risorsa per una organizzazione, e come tali vanno considerati anche separatamente dalle applicazioni che li utilizzano”. I dati rappresentano una risorsa strategica per le organizzazioni perché sono la componente più stabile nel tempo. Mentre le tecnologie, le applicazioni software, i processi aziendali e i ruoli umani cambiano con grande frequenza, la struttura dei dati di base (si pensi ai dati anagrafici o a quelli bancari) rimane invariata per decenni. Per questo motivo, i dati devono essere considerati e gestiti separatamente dai programmi: centralizzarli in un DBMS anziché annegarli nel codice delle singole applicazioni previene la duplicazione (ridondanza) e l'incoerenza, garantendo che l'informazione rimanga un patrimonio aziendale condiviso e sempre affidabile.

3. Illustrare brevemente (non più di mezza pagina) il concetto di indipendenza dei dati. L'indipendenza dei dati è un principio fondamentale garantito dall'architettura a livelli dei DBMS, che permette di separare i programmi applicativi dalla struttura reale dei dati. Si divide in due forme:

• Indipendenza fisica: I programmi e gli utenti interagiscono con i dati facendo riferimento al livello logico o esterno, ignorando completamente come questi siano fisicamente salvati sui dischi. Questo permette di modificare l'organizzazione fisica (es. cambiare disco o metodo di memorizzazione) senza dover riscrivere una singola riga di codice dei programmi.
• Indipendenza logica: Permette al livello esterno (le viste dei dati usate dalle applicazioni) di rimanere inalterato anche se la struttura logica globale del database subisce delle modifiche. Ad esempio, se si aggiungono nuove tabelle o colonne al database centrale, le vecchie applicazioni che accedono tramite specifiche viste continueranno a funzionare in modo trasparente senza dover essere aggiornate.

Modello relazionale

Cenni sul modello relazionale

Si introduce storicamente il Modello Relazionale, ideato da Edgar F. Codd nel 1970 (che gli valse il Turing Award), subentrato ai vecchi modelli "reticolari" e "gerarchici" basati su puntatori.

• Il modello relazionale rappresenta i dati in modo molto più pulito tramite tabelle (relazioni matematiche) composte da insiemi di tuple (record).
• Si rimarca che, per definizione matematica, in un "insieme" non ci possono essere due elementi identici; lo stesso vale per le tabelle relazionali, in cui le righe non possono mai essere completamente uguali.
• Il limite di questo modello rigido (in cui ogni riga deve avere lo stesso numero di attributi/colonne) lo rende inadatto per alcuni moderni scenari sui Big Data, per i quali oggi esistono i cosiddetti database NoSQL.

Fondamenti del modello relazionale

Il modello relazionale è il modello logico matematico alla base degli attuali DBMS relazionali.

• Relazione Matematica: Dal punto di vista formale, una relazione è un sottoinsieme del prodotto cartesiano di n domini (insiemi di valori possibili, es. l'insieme di tutti i nomi, l'insieme degli interi).
• Grado e Cardinalità: Il numero di domini (colonne) definisce il grado (o arità) della relazione, mentre il numero di tuple (righe) ne definisce la cardinalità.
• Dalla Relazione alla Tabella: Le relazioni hanno una rappresentazione naturale a forma di tabella, ma vi sono alcune differenze matematiche. Essendo la relazione un insieme, le tuple non sono ordinate e non ci possono essere tuple (righe) duplicate. Nelle tabelle reali, per semplicità, l'ordinamento delle righe/colonne è irrilevante, ma si introducono i nomi degli attributi per fare da "segnaposto" ai domini sottostanti.

Struttura basata sui valori

A differenza dei primissimi modelli (gerarchici o reticolari) che usavano complessi puntatori fisici agli indirizzi di memoria, il modello relazionale è basato sui valori. I collegamenti tra tabelle diverse (es. collegare uno studente agli esami che ha sostenuto) avvengono confrontando valori comuni (es. la matricola).

• Vantaggi: Garantisce l'indipendenza dalle strutture fisiche, rende i dati più facilmente trasportabili tra computer diversi, ma soprattutto garantisce la bidirezionalità (es. posso partire da uno studente e trovare i suoi esami, o partire da un esame e trovare i dati dello studente).

Notazione formale

• Uno schema di relazione si indica con il nome della relazione e, tra parentesi, i suoi attributi (es. Giocatore(Nome, Cognome, Squadra)).
• Data una tupla t e un attributo A, il valore di quella tupla per quell'attributo si indica con t[A] oppure t.A. Es. se t è la prima riga, t[Fuori] restituisce "Lazio".

Informazione incompleta: i valori NULL

I database permettono di gestire l'assenza di dati tramite un valore speciale chiamato NULL.