Argomenti della lezione

La modellazione fisica

Come già visto, la metodologia per la progettazione di una banca dati prevede tre fasi indipendenti:

• Modellazione concettuale: si definisce cosa il DB dovrà memorizzare, a partire dall’universo del problema, mediante raccolta e analisi dei requisiti. La metodologia maggiormente usata prevede la creazione di un modello ER.

• Modellazione logica: in questa fase si definisce come un DB dovrà mantenere le informazioni. Questa fase dipende strettamente dal tipo di DBMS che si intenderà usare; nel caso di DBMS relazionali il prodotto di questo step è il diagramma relazionale.

• Modellazione fisica: è l’ultimo passo nel processo di progettazione e implementazione di una banca dati. In questa sede il modello fisico viene completato con le specifiche dei parametri fisici di memorizzazione (motore di storaging, indici,..)

SQL (1/2)

L’SQL (Structured Query Language) nasce nel 1974 nei laboratori dell’IBM, come strumento per interagire con i database basati sul modello relazionale. Si tratta di un linguaggio di tipo dichiarativo, molto simile alla lingua parlata inglese.

Dopo il 1983, data di rilascio di DB2 (un famoso DBMS relazionale ancora largamente in uso), l’SQL diventa uno standard de facto in ambito industriale.

L’SQL ha subito negli anni un processo di standardizzazione, sia ad opera dell’ANSI (1986, 1992, 1999, 2003, 2006) che della ISO (1987), che mirava alla creazione di un linguaggio comune a tutti i DBMS relazionali; tuttavia questo obiettivo non è stato mai raggiunto.

Attualmente ne esistono diverse implementazioni, che incorporano almeno in parte le operazioni standard (o almeno il sottoinsieme SQL-87), e ne incrementano le funzionalità attraverso estensioni procedurali (PL/SQL, PL/PgSQL, SQL PL…)

SQL (2/2)

L’SQL è caratterizzato da tre sottolinguaggi, ciascuno avente obiettivi differenti:

• DDL (Data Definition Language): insieme di direttive per la descrizione delle strutture che memorizzeranno i dati. Attraverso questo sottolinguaggio può essere descritto il modello fisico di un database relazionale.

• DML (Data Manipulation Language): insieme di direttive per la manipolazione dei dati; permettono operazioni CRUD (Create, Read, Update e Delete)

• DCL (Data Control Language): insieme di direttive per il controllo dell’accesso ai dati (creazione di utenti/gruppi, privilegi a livello di DBMS, DB, Tabella e singole colonne)

DDL (Data Definition Language)

Il DDL è il linguaggio che permette di creare, modificare o eliminare gli oggetti in un database. In particolare è possibile operare su:

• Database

• Domini (definizione di tipi di dati personalizzati, a partire da tipi di dati primitivi)

• Schemi (contenitori delle definizioni di tutti gli oggetti di un database – domini, tabelle, viste,..)

• Tabelle

• Indici

Definizione di database

Come visto in precedenza, un DBMS è un software che permette l’organizzazione e la gestione di uno o più database. In quest’ottica l’SQL deve prevedere delle istruzioni per la creazione, modifica e rimozione di questi ultimi:

CREAZIONE:

CREATE DATABASE db_name
[create_specification [, create_specification] …]

MODIFICA:

ALTER DATABASE db_name
 [DEFAULT] CHARACTER SET charset_name
 | [DEFAULT] COLLATE collation_name

CANCELLAZIONE:

DROP DATABASE nome_db

Definizione di domini (1/2)

Per dominio si intende un tipo di dato personalizzato, definito a partire da un tipo primitivo, da cui ne eredita tutte le caratteristiche, ma sottoposto a vincoli sui possibili valori che un tale attributo può assumere. Non tutti i DBMS implementano questa funzionalità

CREAZIONE DI UN DOMINIO:

CREATE DOMAIN nome_dominio AS tipo_primitivo [DEFAULT default_value] [constraint […]]

ELIMINAZIONE DI UN DOMINIO:

DROP DOMAIN nome_dominio [CASCADE | RESTRICT]

Definizione di domini (2/2)

MODIFICA DI UN DOMINIO:

ALTER DOMAIN nome_dominio
     {SET DEFAULT default_value | DROP DEFAULT}

ALTER DOMAIN nome_dominio
    { SET | DROP } NOT NULL

ALTER DOMAIN nome_dominio
    ADD nome_vincolo

ALTER DOMAIN nome_dominio
    DROP CONSTRAINT nome_vincolo [ RESTRICT | CASCADE ]

Definizione di schemi (1/2)

Uno schema è una sorta di collezione di oggetti all’interno di un database. Questi oggetti possono essere tabelle, domini, viste, procedure...

L’introduzione di queste strutture all’interno di un DBMS nasce da due esigenze:

• evitare la collisione di nomi
• aggiungere un livello a granularità più elevata nella gestione dei privilegi

Definizione di schemi (2/2)

AGGIUNTA DI UNO SCHEMA:

CREATE SCHEMA nome_schema [ AUTHORIZATION username ] [ schema_element [ … ] ]

MODIFICA DI UNO SCHEMA:

ALTER SCHEMA nome RENAME TO nuovo_nome
ALTER SCHEMA nome OWNER TO nuovo_propietario

CANCELLAZIONE DI UNO SCHEMA:

DROP SCHEMA [ IF EXISTS ] nome_schema [, …] [ CASCADE | RESTRICT ]

Creazione di tabelle (1/2)

Il comando create table ha la funzione di creare una nuova tabella (o relazione). Il nome della tabella può essere scritto indifferentemente in MAIUSCOLO o in minuscolo (in alcuni ambienti il DBMS farà distinzione a seconda dei casi). È necessario che IL NOME DI UNA TABELLA rispetti le seguenti regole:

1. Può essere formato da lettere e numeri, ma il primo carattere deve sempre essere una lettera;
2. Non può superare i 30 caratteri di lunghezza;
3. Non può avere lo stesso nome di una tabella o vista già esistente sullo stesso utente di database.

Oltre a definire gli attributi di una tabella è possibile definire dei vincoli.

La tabella può essere creata vuota oppure può essere creata e riempita di dati (create table mediante select); nel secondo caso la struttura della tabella è definita implicitamente dal numero di colonne (o campi) estratti dalla select

Creazione di tabelle (2/2)

SINTASSI

• Creazione mediante definizione dello schema:

  CREATE TABLE nome_tabella(
   nome_colonna1 tipo_dato [vincolo (…)],
   nome_colonna2 tipo_dato [vincolo (…)],
   nome_colonna3 tipo_dato [vincolo (…)],
   …
   nome_colonnaN tipo di dato constraint (opzionale) 
  );
  

• Creazione mediante select:

  CREATE TABLE nome_tabella AS 
  SELECT …;
  

Modifica di una tabella

Il comando alter table ha la funzione di modificare la struttura della tabella. Supporta tre operatori:

• ADD: consente di inserire una nuova colonna su una tabella esistente oppure di aggiungere dei vincoli alle colonne della tabella.
• MODIFY: consente di cambiare il tipo di dato e/o i vincoli propri di ogni colonna di una tabella.
• DROP: consente di eliminare una colonna o un vincolo proprio di colonna dalla tabella.

Es.:

ALTER TABLE esame ADD COLUMN voto int not null default 18;

Cancellazione di una tabella

Per eliminare una tabella da un database (cancellando di conseguenza tutti i dati in essa contenuti) si utilizza il comando:

DROP TABLE nome_tabella [CASCADE | RESTRICT]

Utilizzando l’opzione CASCADE il DBMS eliminerà anche tutte le foreign keys che si riferiscono a dati della tabella cancellata.

Definizione di indici

Gli indici sono particolari strutture messe a disposizione dal DBMS per ottimizzare le operazioni di ricerca e ordinamento. È definire indici semplici (su un’unica colonna) o composti (su gruppi di colonne, per miglirare le operazioni di filtering miltiplo).

Nota: la definizione di una chiave primaria (vincolo PRIMARY KEY nella CREATE TABLE) presuppone la creazione automatica di un indice di unicità su tale colonna.

Sintassi:

CREATE [UNIQUE] INDEX nome_indice
  ON nome_tabella (nome_colonna [ASC|DESC])

DROP INDEX nome_indice

Algebra relazionale

In aggiunta alle direttive di definizione della struttura e dei vincoli di un database, il modello relazionale prevede un sottoinsieme di operazioni per la manipolazione dei dati detto algebra relazionale

Queste operazioni hanno sempre come operandi una o più relazioni, e come risultato producono una nuova relazione, che può essere successivamente manipolata con la stessa algebra relazionale.

In genere si suddivide l’algebra relazionale in due sottoinsiemi:

• operazioni sugli insiemi (unione, intersezione, differenza, prodotto cartesiano)
• operazioni specifiche dei db relazionali (selezione, proiezione, join)

Nota: selezione, proiezione, ridenominazione, prodotto cartesiano, unione e differenza sono operatori fondamentali, in quanto nessuno di essi può essere omesso senza togliere espressività al linguaggio.

Operatori unari

Per operatore unario si intende una funzione di R→R, prende cioè come argomento un’unica relazione, e restituisce in output un’unica nuova relazione contenente le tuple risultato. L’algebra relazionale prevede i seguenti operatori unari:

• selezione: permette di filtrare le tuple di una relazione sulla base di una o più condizioni di selezione
• proiezione: permette di proiettare una relazione di dimensione n (n attributi) in un’altra relazione di dimensione m (con m<=n)
• ridenominazione: permette di rinominare le colonne di una relazione, per successive operazioni sugli insiemi

Operatori binari

Gli operatori binari dell’algebra relazionale sono funzioni di RxR→R, prendono come argomento due relazioni e ne restituiscono una nuova con le tuple rosultato.

• Unione: dati due insiemi A e B, l’unione AuB è data dall’insieme C contenete tutti gli elementi di A e tutti gli elementi di B, con occorrenze singole in caso di elementi ripetuti
• Intersezione: dati due insiemi A e B, l’intersezione A^B è data dall’insieme C contenete tutti gli elementi presenti sia in A che in B
• Differenza tra insiemi: dati due insiemi A e B, la differenza A-B è data dall’insieme C contenete tutti gli elementi presenti in A e non in B
• Prodotto cartesiano: date due relazioni A di dimensione n e cardinalità r e B di dimensione m e cardinalità s, AxB è la relazione C di dimensione n+m e cardinalità r*s data da tutte le combinazioni tra gli elementi di A e gli elementi di B
• Join (natural e outer): simili al prodotto cartesiano, vengono scartate tutte le tuple che non soddisfano la condizione di join (logicamente equivale ad un prodotto cartesiano seguito da una selezione)

Funzioni di aggregazione

Le funzioni di aggregazione eseguono un calcolo su un set di valori e restituiscono un valore singolo. Ad eccezione della funzione COUNT, le funzioni di aggregazione ignorano i valori Null.

Nota: Tutte le funzioni di aggregazione sono deterministiche. Ciò significa che le funzioni di aggregazione restituiscono lo stesso valore ogni volta che vengono chiamate con un set specifico di valori di input. Per ulteriori informazioni sulle funzioni deterministiche.

Tra le più comuni funzioni di aggregazione si devono annoverare:

DML (Data Manipulation Language)

Un DML è il sottoinsieme di un linguaggio di livello fisico fornito dal DBMS per la manipolazione dei dati.

L’SQL prevede una serie di direttive per il supporto alle seguenti operazioni:

• Inserimento di dati nel DB
• Modifica di dati precedentemente inseriti
• Cancellazione
• Selezione, Proiezione, Ridenominazione (Algebra relazionale)
• Ordinamento
• Prodotto cartesiano, join, natural join, outer join, unione, intersezione, differenza (Operazioni sugli insiemi)

Inserimento di tuple

Il comando ha la funzione di inserire i dati nelle tabelle.

Le colonne (o campi) di destinazione dei valori possono essere o meno dichiarate nel comando. Se non vengono dichiarate, è necessario passare al comando un valore per ogni colonna della tabella, rispettando rigorosamente l’ordine delle colonne stesse. Se, invece, le colonne di destinazione vengono dichiarate, è possibile indicare le sole colonne per le quali vengono passati dei valori, purché vengano inseriti comunque i valori per tutte le colonne not null

Sintassi:

INSERT INTO nome_tabella [(elenco dei campi interessati dall’inserimento)]
VALUES (elenco valori);

Modifica di tuple

Il comando update ha la tripla funzione di modificare i dati delle tabelle.

Il nome di ogni campo che deve essere modificato va dichiarato dopo la parola chiave SET e deve essere seguito dal simbolo " = " (uguale) e dal nuovo valore che deve assumere.

È possibile modificare più campi della stessa riga in un unico comando update, separandoli l’uno dall’altro con il simbolo " , " (virgola).

Il comando generico aggiorna tutte le righe della tabella. È possibile restringerne il numero applicando la parola chiave aggiuntiva WHERE.

Sintassi:

UPDATE nome_tabella
SET nome_campo1 = ‘valore1_nuovo’,
    nome_campo2 = ‘valore2_nuovo’
[WHERE nome_campo3 = ‘valore’];

Cancellazione di tuple

Il comando delete ha la funzione di cancellare i dati dalle tabelle.

Come il comando update anche delete può operare in modo generico cancellando tutte le righe della tabella oppure può identificare le righe da cancellare mediante la parola chiave aggiuntiva WHERE e la condizione (o le condizioni) ad essa associata.

Sintassi:

DELETE FROM nome_tabella
[WHERE nome_campo = ‘valore’];

Nota: alcuni DBMS implementano la direttiva TRUNCATE (non standard) per la cancellazione incondizionata, che ottimizza l’operazione agendo direttamente sul file system.

Selezione

La sintassi generica di un comando di manipolazione in SQL è nella forma:

SELECT … FROM … WHERE

In particolare, l’operazione unaria di selezione si ottiene mediante la dichiarazione:

SELECT *
FROM tabella
WHERE espressione_condizionale

Operatori matematici e logici

Ogni condizione di selezione deve essere nella forma: colonna operatore valore, dove operatore può valere:

• >,=,<,<=,>= (operatore di confronto)
• IN (operatore di inclusione insiemistica)
• LIKE (operatore di confronto tra stringhe)

È possibile concatenare più condizioni di selezione attraverso gli operatori logici:

• AND (le due condizioni a dx e sx devono verificarsi contemporaneamente)
• OR (almeno una delle condizioni deve essere vera)
• NOT (negazione logica)

Proiezione

L’operazione di proiezione dell’algebra relazionale può essere ottenuta in SQL in modo molto semplice, specificando gli attributi da selezionare subito dopo la clausola SELECT:

SELECT lista_attributi
FROM tabella

Nota: è possibile combinare operazioni di proiezione e selezione (per la loro commutatività), specificando contemporaneamente la lista degli attributi da proiettare e le condizioni di selezione

Es.

SELECT nome, cognome, età
FROM impiegato
WHERE età < 50 and salario > 3000

Prodotto cartesiano

Il prodotto cartesiano tra due relazioni è un’operazione teoricamente corretta ma di scarsa utilità. Èsso è definito dalla seguente direttiva SQL:

SELECT * 
FROM tabella1, tabella2

Join (Natural)

Il natural join tra due relazioni è implementato in SQL attraverso due sintassi differenti:

SELECT * 
FROM tabella1, tabella2
WHERE condizione_di_join

SELECT * 
FROM tabella1 JOIN tabella2
USING (condizione_di_join)

La seconda sintassi è preferibile perché più leggibile (nella prima forma, deprecata nell’SQL-99, vengono utilizzati gli operatori di selezione e proiezione per effettuare una join)

Nota: quando le colonne della condizione di join hanno nomi differenti, si utilizza la clausola ON (colonna1=colonna2) al posto della USING

Join (OUTER, LEFT OUTER, RIGHT OUTER)

Segue la sintassi base per le operazioni di outer join:

SELECT * 
FROM tabella1 [LEFT | RIGHT] OUTER JOIN tabella2
[USING (condizione_di_join) | ON (condizione_di_join)]

Nota: le operazioni di outer join (normale, sinistro e destro NON possono essere definite mediante operatori di selezione e proiezione)

Ordinamento

Per come è definito il modello relazionale (logica e teoria degli insiemi), data una relazione non esiste una relazione d’ordine preferenziale sulla stessa.

Questo presuppone che un qualsiasi DML deve permettere la definizione di un qualche ordinamento sulle tuple. In SQL per tale scopo si utilizza la clausola ORDER BY:

SELECT attributi_di_proiezione
FROM nome_tabella
WHERE condizione_di_selezione
ORDER BY colonna1 [ASC | DESC] [, colonna2 [ASC | DESC] ...]