Il Linguaggio SQL

Introduzione

La necessità di ricordare è una delle più pressanti della nostra vita: scadenze, impegni di lavoro, ricorrenze familiari, numeri di telefono, appuntamenti. Le necessità personali sono comunque circoscritte e può essere sufficiente aggiornare e consultare frequentemente la propria agenda. Ma quando i dati diventano molti e complessi, occorre un sistema strutturato: la base di dati.

Con il termine base di dati o database si intende un insieme di file, memorizzati su disco, contenenti dati collegati tra loro. Ogni file è parte integrante dell’insieme: tutte le informazioni memorizzate concorrono a costituire un’unica raccolta di dati. Ad esempio, l’insieme delle informazioni sugli studenti di una scuola: dati anagrafici, classe frequentata, voti, pagelle, assenze. Questi dati possono risiedere in file diversi, ma formano un unico database “studenti”.

Il database può poi essere ampliato a docenti, personale non docente, strutturando così la base informativa dell’intera scuola.

Il DBMS

Per operare su una base di dati – inserire, aggiornare, elaborare – serve un DBMS (Data Base Management System), cioè un sistema di gestione delle basi di dati. Spesso si confondono i termini: un database è una collezione di dati, il DBMS è l’insieme di programmi che operano su di essa.

Lo stesso DBMS può gestire basi diverse (biblioteca, contabilità, ecc.). L’espressione “uso il database” è imprecisa: in realtà si usa un DBMS per amministrare dati organizzati in uno o più database. Inoltre, “banca dati” e “base di dati” non sono sinonimi: una banca dati è un insieme di informazioni messo a disposizione di un grande pubblico di utenti, spesso ospitato in un database.

Caratteristiche di una buona base di dati

Una base di dati ben progettata deve garantire affidabilità, coerenza, efficienza e ridondanza minima, soprattutto quando gestisce grandi volumi di informazioni.

Esempio: amministrazione di un condominio

Esempio: visite mediche

Esempio: catalogazione di progetti

Il processo di normalizzazione

Gli esempi precedenti mostrano errori concettuali e come correggerli. Questo processo si chiama normalizzazione: serve a evitare ridondanze e inconsistenze, progettando basi di dati ben strutturate.

La chiave primaria

Per gestire un database occorre identificare in modo univoco i record. La chiave primaria è un campo (or insieme di campi) che permette di distinguere senza ambiguità un record dagli altri.

Esempio: nel file dei dipendenti, nome e cognome non bastano (possono esserci omonimi). Si può aggiungere la data di nascita, ma non è garantita unicità. Meglio introdurre un codice identificativo o usare il codice fiscale. Tale codice è un dato fittizio, utile solo a identificare in modo univoco.

Se vogliamo memorizzare stipendi mensili, occorre un nuovo file. In questo caso la chiave primaria è composta: codice dipendente + mese + anno, perché nessuno dei campi da solo è sufficiente.

La chiave secondaria

Una ricerca può essere fatta anche in base a un campo non primario, ad esempio la qualifica. In questo caso il campo diventa chiave secondaria.

Quando si aggiunge, modifica o cancella un record, gli indici primari e secondari devono essere aggiornati di conseguenza.

Gli indici

Gli indici sono strutture di supporto per velocizzare le ricerche. Ogni chiave viene associata alla posizione del record nel file. Il DBMS si occupa di aggiornarli automaticamente.

Relazioni tra dati

Relazione 1–1

Relazione 1–M

Relazione N–M

L’integrazione dei dati

I database consentono di mantenere un unico patrimonio informativo, utilizzato da più programmi. Ciò evita ridondanze e incoerenze: ad esempio, i dati dei prodotti sono condivisi da programmi di magazzino e di fatturazione.

Il sistema relazionale

Il modello relazionale è il più diffuso nei DBMS. Organizza i dati in tabelle (relazioni), ciascuna costituita da righe (tuple) e colonne (attributi).

Le relazioni sono collegate mediante le chiavi. Una chiave primaria identifica univocamente ogni riga di una tabella, mentre una chiave esterna consente di stabilire legami con altre tabelle.

L’integrità dei dati

Un database deve garantire integrità e coerenza. Si parla di integrità referenziale quando le chiavi esterne corrispondono sempre a valori validi nelle tabelle collegate.

Operazioni sui dati

Le principali operazioni possibili su un database relazionale sono:

• Inserimento di nuovi dati
• Modifica di dati esistenti
• Cancellazione di dati non più validi
• Ricerca ed estrazione di informazioni

Il linguaggio standard per queste operazioni è SQL, che vedremo in dettaglio nel prossimo blocco.

Protezione dei dati

I DBMS moderni includono sistemi di protezione per controllare chi può accedere ai dati e cosa può fare: lettura, inserimento, modifica, cancellazione. I permessi si gestiscono con apposite istruzioni SQL (GRANT e REVOKE).

Backup e sicurezza

Per evitare la perdita di dati a causa di guasti o errori, i database devono essere salvati periodicamente con procedure di backup. I DBMS offrono strumenti per backup automatici e ripristino (restore) dei dati.

Il linguaggio SQL

SQL (Structured Query Language) è il linguaggio standard per la gestione dei database relazionali. È nato negli anni ’70 in IBM con il progetto System R, ed è stato poi standardizzato dall’ANSI e dall’ISO.

Caratteristiche principali

• È un linguaggio dichiarativo: l’utente specifica cosa vuole ottenere, non come calcolarlo.
• Consente di definire la struttura dei dati (DDL – Data Definition Language).
• Permette di manipolare i dati (DML – Data Manipulation Language).
• Gestisce i privilegi di accesso (DCL – Data Control Language).

DDL – Data Definition Language

Comandi per definire e modificare la struttura delle tabelle:

CREATE TABLE Studenti (
  matricola INT PRIMARY KEY,
  nome VARCHAR(30),
  cognome VARCHAR(30),
  classe VARCHAR(5)
);

ALTER TABLE Studenti
ADD COLUMN data_nascita DATE;

DROP TABLE Studenti;

DML – Data Manipulation Language

Comandi per inserire, modificare, cancellare e leggere dati:

-- Inserimento
INSERT INTO Studenti (matricola, nome, cognome, classe)
VALUES (101, 'Mario', 'Rossi', '5A');

-- Modifica
UPDATE Studenti
SET classe = '5B'
WHERE matricola = 101;

-- Cancellazione
DELETE FROM Studenti
WHERE matricola = 101;

-- Selezione
SELECT nome, cognome
FROM Studenti
WHERE classe = '5A';

DCL – Data Control Language

Comandi per la gestione dei privilegi di accesso:

GRANT SELECT, INSERT ON Studenti TO docente;
REVOKE INSERT ON Studenti FROM docente;

Esempi di interrogazioni SQL

SELECT *
FROM Studenti
WHERE classe LIKE '5%';

SELECT nome, cognome
FROM Studenti
ORDER BY cognome ASC;

SELECT COUNT(*) AS Totale
FROM Studenti
WHERE classe = '5A';

SELECT matricola, AVG(voto) AS Media
FROM Voti
GROUP BY matricola;

Esercizi

Quiz a scelta multipla

Domande aperte / Query SQL

Set 1 — Biblioteca

Schema: Libro(codice_libro, autore, titolo), Lettore(codice_lettore, nome, cognome), Prestito(codice_lettore, codice_libro, data_prestito)

-- Scrivi qui la tua query

SELECT DISTINCT p.codice_lettore
FROM Prestito AS p;

-- Scrivi qui la tua query

SELECT DISTINCT l.nome, l.cognome
FROM Lettore AS l
JOIN Prestito AS p ON p.codice_lettore = l.codice_lettore;

Set 2 — Jukebox

Schema: CD(NroSerie, TitoloAlbum, Anno, Prezzo), Contiene(NroSerieDisco, CodiceReg, NroProg), Esecuzione(CodiceReg, TitoloCanz, Anno, NomeCantante), Autore(Nome, TitoloCanzone)

-- Scrivi qui la tua query

SELECT DISTINCT a.Nome
FROM Autore AS a
JOIN Esecuzione AS e ON e.TitoloCanz = a.TitoloCanzone
WHERE a.Nome = e.NomeCantante
  AND a.Nome LIKE 'A%';

-- Scrivi qui la tua query

SELECT DISTINCT cd.TitoloAlbum
FROM CD cd
JOIN Contiene c ON c.NroSerieDisco = cd.NroSerie
JOIN Esecuzione e ON e.CodiceReg = c.CodiceReg
WHERE e.Anno IS NULL;

-- Scrivi qui la tua query

SELECT c.NroProg, e.TitoloCanz
FROM Contiene c
JOIN Esecuzione e ON e.CodiceReg = c.CodiceReg
WHERE c.NroSerieDisco = 9883
ORDER BY c.NroProg ASC;