Implementazione in Oracle di un semplice progetto

Oracle e SQL
Implementazione in Oracle di un semplice progetto

Operazioni preliminari
La versione del DBMS Oracle a cui si farà riferimento di qui in seguito è la 10g Express Edition, liberamente scaricabile
dal sito http://www.oracle.com/technology/software/products/database/index.html nelle versioni Linux e Windows.
Nel corso dell’installazione verrà chiesto di indicare una password per l’utente system, l’amministratore principale
del database.
Dopo aver installato il software è possibile accedere alla home page per la gestione del database dal link nel menu
Start, cliccando su “Vai alla home page del database” nella cartella appena creatasi all’interno del gruppo Programmi.
Link alla home del database nel menu Start
Si presenterà la schermata di login; per connettersi, è sufficiente per adesso specificare l’user system e la password
scelta in fase di installazione.
La finestra di login del database
1
Una volta nella home page, per poter agire sul database bisogna accedere alla pagina Comandi SQL, mediante la
quale si possono eseguire sia per operazioni di amministrazione che di interrogazione.
La home page del database
Per raggiungerla è sufficiente cliccare sulle icone SQL
, dunque Comandi SQL
.
La pagina Comandi SQL si presenta con una finestra di testo bianca, all’interno della quale è possibile scrivere il proprio
codice SQL (ed eseguirlo cliccando sul pulsante Esegui), ed un riquadro che mostra i risultati dell’operazione eseguita.
La pagina Comandi SQL
In alternativa, è possibile accedere alle funzionalità del database mediante la riga di comando SQL, che può essere
avviata anch’essa dal menu Start.
Tutte le operazioni descritte in seguito possono essere eseguite allo stesso modo tramite questa interfaccia testuale,
con l’unica differenza che l’accesso andrà eseguito tramite il comando:
CONNECT user/password
2

Creazione degli utenti e del tablespace
La prima operazione da eseguire è la creazione di un tablespace apposito per il database che ci si accinge a creare;
dalla finestra Comandi SQL si esegue il comando:
CREATE TABLESPACE voli
DATAFILE 'E:\voli.dbf'
SIZE 5M
AUTOEXTEND ON NEXT 5M
MAXSIZE 10M;
In questo modo si è creato un nuovo tablespace, voli, ospitato nel datafile voli.dbf.
Trattandosi di un database di esempio, non c’è bisogno di dedicare molto spazio al tablespace che lo conterrà: i 5
Megabyte assegnatigli saranno sicuramente più che sufficienti ad ospitarne il contenuto; tuttavia è lecito chiedersi
cosa succederebbe se il contenuto dovesse eccedere i 5 MB inizialmente allocati: Oracle si rifiuterebbe di accettare ogni
successivo inserimento!
Dunque è buona norma definire anche una dimensione massima per il tablespace e il “passo” con cui avviene
l’incremento man mano che lo spazio assegnato si esaurisce.
Adesso si devono creare due utenti: il primo avrà il compito di amministrare il database, il secondo invece sarà
abilitato solo all’esecuzione di query per accedere alle informazioni memorizzate.
Verosimilmente tale utente non si connetterà al database tramite la home page, ma attraverso una pagina web
dinamica appositamente costruita per accedere alle informazioni del database.
CREATE USER voli_dba
IDENTIFIED BY password
DEFAULT TABLESPACE voli
QUOTA unlimited ON voli;
GRANT dba TO voli_dba;
CREATE USER voli_user
IDENTIFIED BY password
DEFAULT TABLESPACE voli;
GRANT CONNECT, SELECT ANY TABLE TO voli_user;
Si noti che mentre per l’utente voli_user si sogno assegnati singolarmente i privilegi necessari (ossia quello di
connessione al database e di interrogazione delle tabelle) per l’utente voli_dba si è utilizzato per comodità il ruolo
dba, che incorpora i privilegi di connessione, interrogazione, creazione tabelle, creazione viste…
Inolte, si è assegnato all’utente amministratore una quota illimitata sul tablespace voli; questo significa che
potrebbe, potenzialmente, saturarlo.
In condizioni reali, è opportuno assegnare a ogni utente solo una quota limitata, esprimendola in kilo o megabyte.
3

Creazione delle tabelle
Prima di procedere alla creazione delle tabelle, bisogna disconnettersi e rieseguire il login con le credenziali appena
indicate per l’utente voli_dba. Infatti, l’utente system con il quale si è attualmente autenticati è da utilizzarsi solo
per operazioni di mantenimento e il suo tablespace, SYSTEM, contiene tabelle generate in automatico dal DMBS che è
buona norma tenere separate dalle proprie.
La struttura del database alla quale si è pervenuti è la seguente:
VOLI
Codice
Codice aeroporto di partenza
Codice aeroporto di arrivo
Durata
Ora partenza
Ora imbarco
Gate
AEROPORTI
Codice
Nome
PRENOTAZIONI
Cognome
Nome
Codice volo
Posto
Classe
Seating
Si è utilizzata la convenzione di sottolineare gli attributi che costituiscono la chiave primaria della relazione, e di
evidenziare i vincoli di chiave esterna mediante una freccia che punta dall’attributo referente a quello riferito.
Tuttavia queste informazioni preliminari non sono ancora sufficienti; la traduzione di tale schema in DDL richiede:
1. La definizione dei tipi di dati degli attributi
(Il codice dell’aeroporto sarà, ad esempio, un valore alfanumerico di 3 caratteri, mentre il nome di un cliente può
essere una stringa di lunghezza variabile)
2. L’imposizione di eventuali vincoli di dominio
(Se la durata è espressa in minuti, chiaramente non avranno senso valori negativi per il campo; similmente, ci si
aspetta che ogni aeroporto abbia un nome è che dunque il campo Nome sia NOT NULL)
3. L’individuazione dei comportamenti da attuare in risposta a eventuali cancellazioni
(Se un aeroporto dovesse essere disgraziatamente abbattuto, ci si aspetta che la tupla relativa sia eliminata dalla
tabella aeroporti e che tutti i voli associati a tale aeroporto siano annullati, dunque eliminati in cascata.
Se invece ad essere cancellato è un volo, sarà opportuno non eliminare tutte le voci relative ai clienti che avevano
prenotato un biglietto, ma settare il campo Codice volo ad un valore di default, in modo da poter reperire i dati
per un eventuale rimborso)
L’implementazione in DDL seguente tiene conto di tutti gli accorgimenti indicati:
CREATE TABLE
codice
codaerpart
codaerarr
durata
orapartenza
oraimbarco
gate
voli (
VARCHAR
VARCHAR
VARCHAR
NUMBER,
DATE,
DATE,
VARCHAR
(10),
(3) NOT NULL,
(3) NOT NULL,
(5)
4
CONSTRAINT pk_voli PRIMARY KEY (codice)
);
CREATE TABLE aeroporti (
cod VARCHAR (3),
nome VARCHAR (50) NOT NULL
CONSTRAINT pk_aeroporti PRIMARY KEY (cod)
);
CREATE TABLE prenotazioni (
cognome VARCHAR (50),
nome
VARCHAR (50),
codvolo VARCHAR (10),
posto
VARCHAR (5),
classe VARCHAR (10),
seating NUMBER,
CONSTRAINT pk_prenotazioni PRIMARY KEY (cognome, nome, codvolo)
);
ALTER TABLE voli
ADD CONSTRAINT fk_voli_aeroporti_1 FOREIGN KEY (codaerpart)
REFERENCES aeroporti (cod)
ON DELETE CASCADE;
ALTER TABLE voli
ADD CONSTRAINT fk_voli_aeroporti_2 FOREIGN KEY (codaerarr)
REFERENCES aeroporti (cod)
ON DELETE CASCADE;
ALTER TABLE prenotazioni
ADD CONSTRAINT fk_prenotazioni_voli FOREIGN KEY (codvolo)
REFERENCES voli (codice)
/*ON DELETE SET DEFAULT –non implementato in Oracle-*/;
Si è scelto, per i vincoli, di indicarli sempre separatamente dalla definizione strutturale della tabella.
Se per la chiave primaria l’accorgimento ha soltanto lo scopo di poter indicare un nome per il vincolo rendendo più
semplice l’interpretazione dei messaggi di errore in caso di violazioni, per la chiave esterna questo accorgimento
permette di semplificare la fase di creazione delle tabelle: infatti costruendole senza vincoli di relazione e indicandoli in
seguito tramite ALTER TABLE non è necessario alcun accorgimento particolare sull’ordine in cui le tabelle vengono
create.
Il testo tra /* e */ è un commento: la politica di cancellazione ON DELETE SET DEFAULT non è infatti al
momento implementata in Oracle. Talvolta è possibile utilizzare in sua vece ON DELETE SET NULL, ma in questo
caso trattandosi codvolo di un attributo della chiave primaria non è possibile settarlo NULL.
5

Esempi di inserimento e violazione dei vincoli
Prima di popolare il database, è opportuno notare che non sarà possibile svolgere alcuna operazione di inserimento
sulle tabelle voli e prenotazioni prima di aver inserito degli aereoporti, a causa dei vincoli di chiave esterna
imposti:
INSERT INTO prenotazioni
VALUES ('Pallino', 'Pinco', 002, 'A12', 'Economica', 5);
ORA-02291: restrizione di integrità violata (VOLI_DBA.FK_PRENOTAZIONI_VOLI) chiave madre non trovata
Si procede quindi anzitutto all’inserimento di alcuni aeroporti, dunque dei voli (in quanto la tabella prenotazioni
riferisce a voli), e infine delle prenotazioni
INSERT
INSERT
INSERT
INSERT
INTO
INTO
INTO
INTO
aeroporti
aeroporti
aeroporti
aeroporti
VALUES
VALUES
VALUES
VALUES
('NA',
('PAC',
('RMF',
('RMC',
'Napoli Capodichino');
'Paris Charles de Gaulle');
'Roma Fiumicino');
'Roma Ciampino');
INSERT INTO voli VALUES (
'A001001',
'NA',
'PAC',
65,
to_date('01-04-2009 07:00:00', 'dd-mm-yyyy hh24:mi:ss'),
to_date('01-04-2009 06:45:00', 'dd-mm-yyyy hh24:mi:ss'),
15
);
INSERT INTO voli VALUES (
'A001004',
'RMF',
'PAC',
50,
to_date('01-04-2009 08:30:00', 'dd-mm-yyyy hh24:mi:ss'),
to_date('01-04-2009 08:00:00', 'dd-mm-yyyy hh24:mi:ss'),
8
);
INSERT INTO prenotazioni VALUES (
'Quiriconi',
'Gilberto',
'A001001',
'A15',
'Economica',
20
);
Per l’inserimento dei campi orapartenza e oraimbarco si è utilizzata la funzione to_date, il cui prototipo è:
6
to_date('stringa', 'formato')
La funzione converte una stringa testuale formattata nel formato utilizzato dal DBMS per memorizzare variabili di tipo
DATE .
Il formato deve essere rispettato sia per quanto riguarda il numero di cifre che per i separatori (spazi o caratteri) tra i
numeri.
Si noti inoltre che il parametro formato è opzionale: se ignorato, Oracle assumerà utilizzato quello di default, DDMON-YY. Inoltre, è possibile utilizzare un formato diverso senza doverlo specificare ad ogni chiamata di funzione
modificando la variabile globale NLS_DATE_FORMAT:
ALTER SESSION SET NLS_DATE_FORMAT='formato';
Come ci si aspetta dalle politiche di cancellazione indicate, l’eliminazione di una tupla aeroporto provoca l’eliminazione
in cascata dei voli da e per quella destinazione:
DELETE FROM aeroporti WHERE cod = 'RMC'
Si può verificare facilmente l’avvenuta cancellazione del volo Roma Fiumicino - Parigi con la query:
SELECT * FROM voli
7