breve presentazione curata da
Aldo Pappalepore
L’SQL (Structured Query Language) è il linguaggio per l’accesso ai
database (base di dati), sia per l’interrogazione che per la creazione,
la modifica e la gestione degli stessi.
SQL non è un programma ma uno standard definito dall’ANSI (American National Standards Institute) e dall’ISO (International Standards
Organization).
Lo standard SQL viene invece implementato in speciali programmi
chiamati DBMS (Data Base Management System) o sistemi per la gestione di basi di dati, di cui i più famosi sono Oracle, Microsoft SQL
Server, Sybase e Mysql.
Il fondamento formale di SQL è il modello relazionale basato sulla teoria matematica di relazioni tra insiemi.
sql: introduzione
2
Un database (base di dati) è l’insieme dei dati utilizzati in uno specifico
sistema informativo, di tipo aziendale, scientifico, gestionale, amministrativo, etc.
Un database è composto da due diversi tipi di informazione, apparteneti a distinti livelli di astrazione:
• i dati, che rappresentano le entità del sistema da modellare e che si
esprimono in termini di valori (di tipo numerico, alfanumerico, etc.).
I dati sono raggruppati in categorie in base alla loro struttura comune
(come Libri, Autori dell’esempio 1).
• le strutture (metadati), che descrivono le caratteristiche comuni delle
varie categorie di dati quali i nomi ed i tipi dei valori con cui si esprimono.
sql: database
3
Il database utilizzato in una biblioteca può contenere dati relativi ai
Libri ed agli Autori raggruppati nelle due tabelle che seguono:
Libri
Codice_Libro
1
2
3
4
Titolo
Il PC
Sql
Java 2
Word
Collocazione
12/a/3
7/g/12
2/r/8
6/d/11
sql: database
4
Autori
Codice_Autore Cognome
Nome
1
Giorgio
Scoppa
2
Enzo
Vita
3
Aldo
Riccio
I dati sono quelli scritti in blu
Le strutture, composte dai nomi (in rosso) dei dati e dal loro tipo,
può essere descritto nel modo seguente:
sql: database
5
Libri:
Codice_Libro: numerico
Titolo:
testo(50)
Collocazione: testo(10)
Autori
Codice_Autore: numerico
Cognome:
testo(30)
Nome:
testo(30)
sql: database
6
Un database, oltre ai dati veri e propri, deve rappresentare anche le
relazioni fra i dati, ovvero le connessioni logiche presenti tra le varie
categorie di dati.
Per esempio deve essere rappresentata l’associazione che lega ciascun
autore ai propri libri e viceversa.
sql: database
7
Il database deve inoltre rispondere ai seguenti requisiti:
• i dati devono essere organizzati con ridondanza minima, ossia non
essere inutilmente duplicati e ciò sia per ridurre la memoria impegnata sia per evitare la gestione di copie multiple.
• i dati devono essere utilizzabili contemporaneamente da più utenti.
Bisogna cioè evitare che ogni utente lavori su una propria copia dei
dati (vedi sopra); deve esistere una sola versione dei dati, cui tutti gli
utenti possano accedere senza che si generino conflitti per l’eventuale uso contemporaneo dei dati.
• i dati devono essere permanenti e cioè devono essere preservati in
caso di malfunzionamento del sistema.
sql: database
8
Il DataBase Management System (DBMS), o sistema per la gestione
di basi di dati, è il programma che consente la gestione di uno o più
database secondo i requisiti prima indicati. Prima dei DBMS l’archiviazione dei dati avveniva attraverso l’utilizzo diretto delle strutture
del file system.
Nell’approccio file system ogni applicazione accede direttamente agli
archivi dei dati con la conseguenza che deve conoscere la struttura interna degli archivi, deve farsi carico di rappresentare le relazioni tra i
dati, deve consentire l’utilizzo contemporaneo degli archivi da parte di
più applicazioni, etc..
Nell’approccio DBMS invece le applicazioni rivolgono le proprie richieste di accesso al DBMS, che gestisce i dati svincolando le applicazioni da questo onere (figura 1).
sql: DBMS
9
appl. 1
appl. 2
appl. 3
appl. 1
appl. 2
D
B
M
S
Base
Dati
appl. 3
sql: DBMS
10
Per passare da una realtà di interesse alla realizzazione di un database
si fa riferimento ai modelli da utilizzare nelle varie fasi di questo percorso.
Un modello stabilisce le convenzioni per esprimere i diversi aspetti
della realtà d’interesse e costituisce un supporto alla sua rappresentazione che viene definita schema.
Il processo di progettazione di un database si articola in tre fasi fondamentali, ciascuna delle quali si riferisce ad un diverso livello di astrazione. Le tre fasi sono: progetto concettuale, progetto logico e progetto
fisico (figura 2).
La scomposizione in fasi del processo di progettazione ha lo scopo di
separare le diverse risoluzioni dei problemi e poter modificare le soluzioni dei livelli inferiori senza toccare quelle dei livelli superiori.
A ciascuna fase di progettazione coorispondono specifici modelli per
la rappresentazione dei dati.
sql: modelli dei dati
11
realtà d’interesse
modello concettuale
Schema concettuale
modello logico
Schema logico
modello fisico
Schema fisico
sql: modelli dei dati
12
• progettazione concettuale:
Obiettivo della fase di progettazione concettuale è la rappresentazione
completa ed efficace della realtà d’interesse ai fini informativi, in maniera indipendente da qualsiasi specifico DBMS. Tale rappresentazione, detta schema concettuale, è la rappresentazione più astratta, ovvero
più vicina alla logica umana, nella definizione di dati e relazioni.
Tra i modelli dei dati usati nella progettazione concettuale il più diffuso è il modello Entity-Relationship (E-R, Entità-Relazione) che introduce una rappresentazione grafica dello schema concettuale.
Il modello E-R prevede come prime attività della progettazione concettuale 1) l’individuazione di oggetti concreti o astratti rilevanti per il sistema informativo, e 2) la loro classificazione in insiemi omogenei detti entità rappresentati mediante rettangoli.
sql: modelli dei dati
13
• progettazione concettuale:
Le proprietà caratteristiche di ciascuna entità, e quindi di ciascun oggetto che ne fa parte, vengono descritte mediante gli attributi.
Per esempio attributi dell’entità Veicolo possono essere:Targa, Cilindrata, Combustibile, Cavalli Fiscali, Velocità, Posti, Immatricolazione.
Veicolo
Targa
Cilindrata
Combustibile
Cavalli_Fiscali
Velocità
Posti
Immatricolazione
sql: modelli dei dati
14
• progettazione concettuale:
Ciascun attributo è caratterizzato da un nome e da un dominio, che
rappresenta l’insieme dei valori che può assumere; per esempio
l’attributo Posti potrebbe assumere valori nel dominio {1,2,3,4,5}.
Il modello prevede la rappresentazione di vincoli di integrità i quali
descrivono le regole che soddisfano gli oggetti della realtà. Per
esempio nel rettangolo dell’entità Veicolo è sottolineato l’attributo
Targa sta rappresentare il vincolo d’integrità per cui ad ogni
targa corrisponde uno ed un solo veicolo.
Le dipendenze logiche o associazioni tra i dati da rappresentare vengono espresse nello schema E-R mediante relazioni tra le corrispondenti entità. Graficamente una relazione viene rappresentata da un
arco che collega i rettangoli che raffigurano le due entità correlate,
interrotto da un ovale che contiene il nome della relazione.
sql: modelli dei dati
15
• progettazione concettuale:
Una relazione R tra due insiemi di entità E1 ed E2 viene classificata in
base alla sua cardinalità.
• R ha cardinalità 1:1 (uno a uno) se ad un elemento di E1 può corrispondere un solo elemento di E2 e viceversa.Un esempio di relazione
1:1 è quella tra nazioni e capitali:
Nazione
1
R-1
Naz_Cod
Naz_Nome
sql: modelli dei dati
1 Capitale
Cap_Cod
Cap_Nome
16
• progettazione concettuale:
• R ha cardinalità 1:N (uno a molti) se ad ogni elemento di E1 possono corrispondere più elementi di E2, mentre ad ogni elemento di E2
corrisponde al massimo un elemento di E1. Un esempio di relazione
1:N è quella tra ordini e righe_ordine:
Ordine
1
R-2
N.Ordine
Cliente
N
Righe_Ordine
N.Riga
Prodotto
sql: modelli dei dati
17
• progettazione concettuale:
• R ha cardinalità N:N (molti a molti) se ad ogni elemento di E1 possono corrispondere più elementi di E2 e viceversa.
Un esempio di relazione N:N è quella tra libri ed autori:
Libro
N
R-3
Lib_Cod
Lib_Titolo
N
Autore
Aut_Cod
Aut_Nome
Il modelle E-R prevede che anche le relazioni possano avere degli
attributi che ne specifichino le caratteristiche.
Vediamo ora un esempio (esempio 4) di schema concettuale.
sql: modelli dei dati
18
In un ufficio di Motorizzazione bisogna realizzare, nell’ambito del
sistema informativo, il database Registro Automobilistico in grado
di rappresentare la seguente realtà d’interesse:
• di ciascun veicolo interessa registrare la targa, la cilindrata, i cavalli
fiscali, la velocità, il numero di posti, la data di immatricolazione;
• i veicoli sono classificati in categorie (automobili, ciclomotori, camion, rimorchi,ecc.);
• ciascun veicolo appartiene a uno specifico modello;
• tra i dati relativi ai veicoli vi è anche il tipo di combustibile usato;
• di ciascun modello di veicolo va registrata la fabbrica di produzione
ed il numero delle versioni prodotte;
• ciascun veicolo può avere uno o più proprietari, che si succedono
nel corso della durata del veicolo e di cui interessa la data di acquisto
e la data di cessione del veicolo; di ciascun proprietario interessa registrare cognome, nome ed indirizzo di residenza.
sql: modelli dei dati
19
Nel progetto concettuale vengono individuate le seguenti entità:
Entità
Attributi
Categoria
Cod_Categoria
Nome_Categoria
Veicolo
Targa
Cilindrata
Cavalli_Fiscali
Velocità
Posti
Immatricolazione
Modello
Cod_Modello
Nome_Modello
Numero_Versioni
Fabbrica
Cod_Fabbrica
Nome_Fabbrica
sql: modelli dei dati
20
Entità
Attributi
Proprietario
Cod_Proprietario
Cognome
Nome
Indirizzo
Provincia
Combustibile
Cod_Combustibile
Descrizione_Combustibile
sql: modelli dei dati
21
Tra tali entità sussistono le seguenti relazioni:
• Categoria e Veicolo sono in relazione 1:N, perché ciascuna categoria
è comune a più veicoli, mentre un veicolo può appartenere ad 1 cat;
• Modello e Veicolo sono in relazione 1:N, perché ciascun modello è
comune a più veicoli e ciascun veicolo è di un solo modello;
• Fabbrica e Modello sono in relazione 1:N, perché una fabbrica può
produrre più modelli, mentre un determinato modello viene prodotto da una sola fabbrica;
• Combustibile e Veicolo sono in relazione 1:N, perché un tipo di combustibile è comune a più veicoli, mentre ciascun veicolo utilizza un
solo tipo di combustibile;
• Veicolo e Proprietario sono in relazione N:N, in quanto una persona
può possedere più veicoli e più persone possono succedersi nella
proprietà di un veicolo; la relazione ha come attributi la data di
acquisto e la data di cessione del veicolo.
sql: modelli dei dati
22
Fabbrica
Categoria
Cod_Fabbrica
Nome_Fabbrica
Cod_Categoria
Nome_Categoria
1
1
R-3
R-1
N
N
Veicolo
Targa
Cilindrata
Cavalli_Fiscali
Velocità
Posti
Immatricolazione
Modello
N
R-2
1
Cod_Modello
Nome_Modello
Numero_Versioni
N
N
R-5
R-4
1
Combustibile
Data_Acquisto
Data_Cessione
Proprietario
Cod_Combustibile
Nome_Combustibile
N
sql: modelli dei dati
Cod_Proprietario
Cognome
Nome
Indirizzo
Provincia
23
La fase di progettazione logica del database consiste nel convertire lo
schema concettuale di base in un modello logico (schema logico) dei
dati.
Un modello logico dei dati è la tecnica di organizzazione ed accesso
ai dati utilizzata dai singoli DBMS. Lo schema logico allora dipende
strettamente dal tipo di DBMS utilizzato ed in particolare dal suo modello logico dei dati.
Un ulteriore compito della progettazione logica è di individuare allo
interno dello schema logico del database le parti rilevanti per le applicazioni quali le viste.
sql: modelli dei dati
24
Per comodità di rappresentazione è conveniente descrivere una relazione
algebrica in forma di tabella come in figura:
CODICE CATEGORIA
NOME CATEGORIA
01
Autovettura
02
Rimorchio
03
Motociclo
04
Furgone
In base a questa rappresentazione i concetti di relazioni algebriche, di
n-uple e di attributi vengono indicati con i termini più familiari di tabelle, di righe e di colonne.
Nel seguito il termine “tabella” sostituirà quello di “relazione algebrica”, mentre il termine “relazione” indicherà, come nel modello EntitàRelazione, un’associazione tra dati.
sql: database relazionali
25
Dalla definizione di tabella (relazione) come insieme di righe (e-nuple)
derivano due conseguenze fondamentali:
• in una tabella non possone esistere due righe uguali;
• l’ordine tra le righe di una tabella non è significativo.
Da ciò consegue che è possibile e/o necessario individuare in ciascuna tabella uno o più attributi (colonne) in base ai quali possono venire identificate le singole righe e che rappresentano quindi una chiave di accesso univoca alle informazioni contenute nella tabella stessa.
Questa singola (o questo insieme di) colonna/e, che va definita/o in fase di
creazione dello schema logico, è detto chiave primaria (primary key) della
tabella.
sql: database relazionali
26
Nel passaggio dalla progettazione concettuale a quella logica, per la creazione dello schema logico relazionale, partendo dallo schema concettuale
definito in base al modello Entità-Relazione, è necessario applicare le regole seguenti:
• le entità (entity set) dello schema concettuale divengono tabelle nello
schema logico;
• le relazioni (relation set) dello schema concettuale vengono rappresentate nello schema logico facendo uso delle cosidette chiavi esterne. Una
chiave esterna (foreign key) di una tabella è un insieme di attributi che
corrispondono a quelli che costituiscono la chiave primaria di un’altra tabella, e stabiliscono quindi un riferimento tra le righe delle due tabelle.
Nella rappresentazione di una relazione tra le tabelle T1 e T2 bisogna distinguere tra le cardinalità 1:1, 1:N, N:N individuate nello schema concettuale.
sql: database relazionali
27
Relazione 1:1
Agli attributi di T1 vanno aggiunti, come chiave esterna, gli attributi che
costituiscono la chiave primaria di T2, o alternativamente a T2 vanno aggiunti, come chiave esterna, gli attributi che costituiscono la chiave primaria di T1. Le due soluzioni sono del tutto equivalenti. Per esempio,
considerate le tabelle:
Nazioni:
Capitali:
COD_NAZIONE
NOME_NAZIONE
COD_CAPITALE
NOME CAPITALE
001
Austria
001
Parigi
002
Francia
002
Roma
003
Italia
003
Vienna
sql: database relazionali
28
la relazione tra esse viene rappresentata estendendo la tabella Nazioni nel
modo seguente:
Nazioni:
COD_NAZIONE
NOME_NAZIONE
COD_CAPITALE
001
Austria
003
002
Francia
001
003
Italia
002
Oppure in alternativa estendendo la tabella Capitali nel modo seguente:
Capitali:
COD_CAPITALE
NOME CAPITALE
COD_NAZIONE
001
Parigi
002
002
Roma
003
003
Vienna
001
sql: database relazionali
29
Relazione 1:N
Agli attributi di T2 vanno aggiunti, come chiave esterna, gli attributi che
costituiscono la chiave primaria di T1 (il viceversa in questo caso non è
equivalente). Per esempio, considerate le tabelle:
Ordini:
N_ORDINE
DATA
CLIENTE
1
12/01/99
Bianchi s.p.a.
2
23/08/99
Rossi s.r.l.
3
05/09/99
Verdi s.n.c.
Righe_Ordine:
N_RIGA
QUANTITA’
ARTICOLO
1
Tavolo
2
2
Sedia
10
1
Armadio
1
2
Sedia
20
sql: database relazionali
30
la relazione tra esse viene rappresentata estendendo la tabella Righe_Ordine nel modo seguente:
Righe_Ordine:
N_ORDINE
N_RIGA
QUANTITA’
ARTICOLO
1
1
Tavolo
2
1
2
Sedia
10
2
1
Armadio
1
2
2
Sedia
20
sql: database relazionali
31
Relazione N:N
In questo caso va definita una nuova tabella T3 che contiene, come chiavi
esterne, le chiavi primarie sia di T1 che di T2; è da notare come in questo
caso la chiave primaria della tabella T3 possa essere costituita dalla totalità dei suoi attributi. Per esempio, considerate le tabelle:
Libri:
CODICE_LIBRO
TITOLO
COLLOCAZIONE
1
Java
2/D/3
2
Internet
2/A/1
3
Le reti wireless
5/Q/2
4
La programmazione
6/H/8
ed
sql: database relazionali
32
Autori:
CODICE_AUTORE
COGNOME
NOME
1
Benzi
Aldo
2
Rossi
Renato
3
Esposito
Valerio
la relazione tra esse viene rappresentata nel modo seguente:
Libri-Autori:
CODICE_LIBRO
CODICE_AUTORE
1
1
1
2
2
1
2
2
Come previsto dal modello Entità-Relazione, anche una relazione può
possedere degli attributi, che vengono inclusi come attributi della tabella
in cui è rappresentata la relazione, che contiene le chiavi esterne
sql: database relazionali
33
Realizziamo ora lo schema logico relazionale del database Registro
Automobilistico di cui già abbiamo costruito lo schema concettuale.
I passi per tradurre lo schema concettuale nello schema logico corrispondente sono i seguenti.
1) Per ciascuna entità dello schema concettuale viene definita una tabella nello schema logico: Veicoli, Categorie, Combustibili, Modelli, Fabbriche e Proprietari (figura ).
sql: database relazionali
34
Categorie
Cod_Categoria
Nome_Categoria
Fabbriche
testo (2)
testo (30)
Cod_Fabbrica
Nome_Fabbrica
Proprietari
Veicoli
Targa
Cilindrata
Cavalli_Fiscali
Velocità
Posti
Immatricolazione
testo (10)
numerico
numerico
numerico
numerico
data
Cod_Proprietario
Cognome
Nome
Indirizzo
Provincia
testo (5)
testo (30)
testo (30)
testo (30)
testo (2)
Modelli
Combustibili
Cod_Combustibile
Nome_Combustibile
testo (3)
testo (30)
testo (2)
testo (30)
sql: database relazionali
Cod_Modello
Nome_Modello
Numero_Versioni
testo (30)
testo (30)
numerico
35
2) Per ciascuna tabella viene definito un insieme di attributi con funzione di chiave primaria che ne identifichi univocamente le righe.
Se tale insieme non è individuabile tra gli attributi della tabella, va
aggiunto un nuovo attributo finalizzato a questo scopo.
TABELLA
CHIAVE PRIMARIA
Veicoli
Targa
Categorie
Cod_Categoria
Combustibili
Cod_Combustibile
Modelli
Cod_Modello
Fabbriche
Cod_Fabbrica
Proprietari
Cod_Proprietario
sql: database relazionali
36
3) Vengono definite le chiavi esterne (oltre alle interne già presenti)
per la rappresentazione delle relazioni 1:N tra Categorie e Veicoli,
tra Combustibili e Veicoli, tra Modelli e Veicoli e tra Fabbriche e
Modelli.
Veicoli
Targa
Cod_Categoria
Cod_Combustibile
Cod_Modello
Cilindrata
Cavalli_Fiscali
Velocità
Posti
Immatricolazione
Modelli
testo (10)
testo (2)
testo (2)
testo (3)
numerico
numerico
numerico
numerico
data
Cod_Modello
Cod_Fabbrica
Nome_Modello
Numero_Versioni
sql: database relazionali
testo (30)
testo (3)
testo (30)
numerico
37
4) Viene definita la nuova tabella Proprietà per la rappresentazione
della relazione N:N tra Veicoli e Proprietari. Questa contiene, come chiave primaria, le chiavi primarie di Veicoli e Proprietari, e
gli attributi Data_Acquisto e Data_Cessione, che costituiscono gli
attributi della relazione.
Proprietà
Targa
Cod_Proprietario
Data_Acquisto
Data_Cessione
testo (10)
testo (5)
data
data
Tabella
Chiave primaria
Proprietà
Targa+Cod_Proprietario
In figura 4 viene presentata infine una rappresentazione dello schema logico relazionale del database
sql: database relazionali
38
Categorie
1
Cod_Fabbrica
Nome_Fabbrica
Cod_Categoria
Nome_Categoria
Modelli
1
1
N
Veicoli
Targa
Cod_Categoria
Cod_Combustibile
Cod_Modello
Cilindrata
Cavalli_Fiscali
Velocità
Posti
Immatricolazione
N
1
Fabbriche
N
Cod_Modello
Cod_Fabbrica
Nome_Modello
Numero_Versioni
N
N
Proprietà
1
Targa
Cod_Proprietario
Data_Acquisto
Data_Cessione
Proprietari
1
Combustibili
N
Cod_Combustibile
Nome_Combustibile
sql: database relazionali
Cod_Proprietario
Cognome
Nome
Indirizzo
Provincia
39
Modalità d’uso:
In un DBMS vengono distinti tre tipi di linguaggi in base alle funzioni
eseguite sui dati:
• DDL (Data Description Language), per la definizione dello schema logico del database;
• DML (Data Manipulation Language), per le operazioni di interrogazione e di aggiornamento dei dati quali inserimento, modifica, etc.;
• DCL (Data Control Language), per operazioni di controllo dei dati, gestione degli utenti, assegnazione dei diritti di accesso, etc.
sql: database relazionali
40
Definire lo schema concettuale del database Mobili_Componibili.
La realtà da rappresentare è costituita dai dati, appartenenti al sistema informativo di un mobilificio, relativi alla vendita di mobili componibili. In particolare:
• degli articoli, che sono suddivisi in categorie, interessa archiviare
la descrizione, il prezzo, l’aliquota IVA e le spese di trasporto;
• ciascun articolo è costituito da una serie di componenti, di cui
vengono archiviati la descrizione e il costo;
• i componenti vengono prodotti da laboratori, di ciascuno dei quali
vengono memorizzati l’indirizzo, la città ed il telefono;
sql: esercitazioni
41
Costruire lo schema logico del database Mobili_Componibili avendo come base lo schema concettuale definito nell’esercizio precedente.
sql: esercitazioni
42
Definire lo schema concettuale del database Archivio_Biblioteca.
La realtà da rappresentare è costituita dai dati, appartenenti al sistema informativo di una biblioteca universitaria, relativi alla gestione
del suo archivio di libri. In particolare:
• dei libri, che sono suddivisi per argomenti, hanno una certa collocazione, sono stati scritti da uno o più autori e pubblicati da una casa
editrice, interessa archiviare il titolo, la descrizione, il prezzo, l’anno
di pubblicazione;
• ogni collocazione è individuata da un numero di mobile ed un numero di stanza;
• di ogni autore e di ogni casa editrice occorre archiviare l’indirizzo,
la città ed il telefono;
sql: esercitazioni
43
Costruire lo schema logico del database Archivio_Biblioteca avendo come base lo schema concettuale definito nell’esercizio precedente.
sql: esercitazioni
44
