Creare il database

Un database in un sistema relazionale è composto da un'insieme di
tabelle, che corrispondono alle relazioni del modello relazionale. Nella
terminologia usata nell'SQL non si fa accenno alle relazioni, cosi' come non
viene usato il termine attributo, ma viene usata la parola colonna, e non si
parla di tupla, ma di riga. Nel seguito verranno usate indifferentemente le
due terminologie, quindi tabella varra' per relazione, colonna per attributo,
riga per tupla, e viceversa.

In pratica la creazione del database consiste nella creazione delle
tabelle che lo compongono. In realta' prima di poter procedere alla creazione
delle tabelle normalmente occorre creare in effetti il database, il che
di solito significa definire uno sp azio dei nomi separato per ogni
insieme di tabelle. In questo modo per un DBMS è possibile gestire
piu' database indipendenti contemporaneamente, senza che ci siano dei
conflitti con i nomi che vengono utilizzati in ciascuno di essi.

Il sistema previsto dallo standard per creare degli spazi dei nomi
separati consiste nell'utilizzo dell'istruzione SQL "CREATE SCHEMA". Di
solito tale sistema non viene utilizzato (o almeno non con gli scopi ed il
significato previsti dallo standard), ma ogni DBMS prevede una procedura
proprietaria per creare un database. Normalmente viene esteso il
linguaggio SQL introducendo un'istruzione non prevista nello standard:
"CREATE DATABASE".

La sintassi utilizzata da PostgreSQL, ma anche dai piu' diffusi DBMS, è
la seguente:

CREATE DATABASE nome_database

Con PostgreSQL è anche disponibile un comando invocabile dalla shell
Unix (o dalla shell del sistema utilizzato) che esegue la stessa
operazione:

createdb nome_database

Per creare il nostro database bibliografico utilizzeremo
quindi il comando:

createdb biblio

Una volta creato il database è possibile creare le tabelle che lo
compogono. L'istruzione SQL preposta a questo scopo è:

CREATE table nome_tabella (
nome_colonna tipo_colonna [ clausola_default ] [ vincoli_di_colonna ]
[ , nome_colonna tipo_colonna [ clausola_default ] [ vincoli_di_colonna ]
... ]
[ , [ vincolo_di tabella] ... ] )

nome_colonna: è il nome della colonna che compone la tabella.
Sarebbe meglio non esagerare con la lunghezza degli identificatori di
colonna, dal momento che l'SQL Entry Level prevede nomi non piu' lunghi di
18 caratteri. Si consulti comunque la documentazione dello specifico
database. I nomi devono iniziare con un carattere alfabetico.

tipo_colonna: è l'indicazione del tipo di dato che la colonna potra'
contenere. I principali tipi previsti dallo standard SQL sono:

• CHARACTER(n)
  Una stringa a lunghezza fissa di esattamente n caratteri. CHARACTER puo'
  essere abbreviato con CHAR
• CHARACTER VARYING(n)
  Una stringa a lunghezza variabile di al massimo n caratteri. CHARACTER
  VARYING puo' essere abbreviato con VARCHAR o CHAR VARYING.
• INTEGER
  Un numero intero con segno. Puo' essere abbreviato con INT. La precisione,
  cioè la grandezza del numero intero che puo' essere memorizzato in una
  colonna di questo tipo, dipende dall'implementazione del particolare
  DBMS.
• SMALLINT
  Un numero intero con segno con precisione non superiore a INTEGER.
• FLOAT(p)
  Un numero a virgola mobile, con precisione p. Il valore massimo di p
  dipende dall'implementazione del DBMS. È possibile usare FLOAT senza
  indicazione della precisione, utilizzando quindi la precisione di default,
  anch'essa dipendente dall'implementazione. REAL e DOUBLE PRECISION sono dei
  sinonimi per un FLOAT con una particolare precisione. Anche in questo caso
  le precisioni dipendono dall'implementazione, con il vincolo che la
  precisione del primo non sia superiore a quella del secondo.
• DECIMAL(p,q)
  Un numero a virgola fissa di almeno p cifre e segno, con q cifre dopo la
  virgola. DEC è un'abbreviazione per DECIMAL. DECIMAL(p) è un'abbreviazione
  per DECIMAL(p,0). Il valore massimo di p dipende
  dall'implementazione.
• INTERVAL
  Un periodo di tempo (anni, mesi, giorni, ore, minuti, secondi e frazioni
  di secondo).
• DATE, TIME e TIMESTAMP
  Un preciso istante temporale. DATE permette di indicare l'anno, il mese e
  il giorno. Con TIME si possono specificare l'ora, i minuti e i secondi.
  TIMESTAMP è la combinazione dei due precedenti. I secondi sono un numero
  con la virgola, permettendo cosi' di specificare anche frazioni di
  secondo.

clausola_default: indica il valore di default che assumera' la
colonna se non gliene viene assegnato uno esplicitamente nel momento della
creazione della riga. La sintassi da utilizzare è la seguente:

DEFAULT { valore | NULL }

dove, valore è un valore valido per il tipo con cui la colonna è stata
definita.

vincoli_di_colonna: sono vincoli di integrita' che vengono applicati
al singolo attributo. Sono:

• NOT NULL, che indica che la colonna non puo' assumere il valore
  NULL.
• PRIMARY KEY, che indica che la colonna è la chiave primaria della
  tabella.
• una definizione di riferimento, con cui si indica che la colonna è una
  chiave esterna verso la tabella e i campi indicati nella definizione. La
  sintasi è la seguente:

  REFERENCES nome_tabella [ ( colonna1 [ , colonna2 ... ] ) ]
  [ ON DELETE { CASCADE | SET DEFAULT | SET NULL } ]
  [ ON UPDATE { CASCADE | SET DEFAULT | SET NULL } ]

  Le clausole ON DELETE e ON UPDATE indicano quale azione deve essere compiuta
  nel caso in cui una tupla nella tabella referenziata venga eliminata o
  aggiornata. Infatti in tali casi nella colonna referenziante (che è quella
  che si sta definendo) potrebbero esserci dei valori inconsistenti. Le azioni
  possono essere:

  • CASCADE: eliminare la tupla contenente la colonna referenziante (nel
    caso di ON DELETE) o aggiornare anche la colonna referenziante (nel caso di
    ON UPDATE).
  • SET DEFAULT: assegnare alla colonna referenziante il suo valore di
    default.
  • SET NULL: assegnare alla colonna referenziante il valore NULL.
• un controllo di valore, con il quale si permette o meno l'assegnazione
  di un valore alla colonna, in base al risultato di un espressione. La
  sintassi da usare è:

  CHECK (espressione_condizionale)

  dove espressione_condizionale è un'espressione che restituisce vero o
  falso.
  Ad esempio, se stiamo definendo la colonna COLONNA1, definendo il
  seguente controllo:

  CHECK ( COLONNA1 < 1000 )

  in tale colonna potranno essere inseriti solo valori inferiori a
  1000.

vincolo_di_tabella: sono vincoli di integrita' che possono riferirsi
a piu' colonne della tabella. Sono:

• la definizione della chiave primaria:

  PRIMARY KEY ( colonna1 [ , colonna2 ... ] )

  Si noti che in questo caso, a differenza della definizione della chiave
  primaria come vincolo di colonna, essa puo' essere formata da piu' di un
  attributo.

• le definizioni delle chiavi esterne:

  FOREIGN KEY ( colonna1 [ , colonna2 ... ] )
  definizione_di_riferimento

  La definizione_di_riferimento ha la stessa sintassi e significato di
  quella che puo' comparire come vincolo di colonna.

• un controllo di valore, con la stessa sintassi e significato di quello
  che puo' essere usato come vincolo di colonna.

Per chiarire meglio l'utilizzo dell'istruzione CREATE table, esaminiamo
alcuni comandi che implementano il database bibliografico di
esempio.

CREATE table Publication (
ID INTEGER PRIMARY KEY,
type CHAR(18) NOT NULL
);

La precedente istruzione crea la tabella Publication, formata dalle due
colonne ID di tipo INTEGER, e type di tipo CHAR(18). ID è la chiave
primaria della relazione. Sull'attributo type è posto un vincolo di non
nullita'.

CREATE table Book (
ID INTEGER PRIMARY KEY REFERENCES Publication(ID),
title VARCHAR(160) NOT NULL,
publisher INTEGER NOT NULL REFERENCES Publisher(ID),
volume VARCHAR(16),
series VARCHAR(160),
edition VARCHAR(16),
pub_month CHAR(3),
pub_year INTEGER NOT NULL,
note VARCHAR(255)
);

Crea la relazione Book, formata da nove attributi. La chiave primaria è
l'attributo ID, che è anche una chiave esterna verso la relazione
Publication. Sugli attributi title, publisher e pub_year sono posti dei
vincoli di non nullita'. Inoltre l'attributo publisher è una chiave esterna
verso la tabella Publisher.

CREATE table Author (
publicationID INTEGER REFERENCES Publication(ID),
personID INTEGER REFERENCES Person(ID),
PRIMARY KEY (publicationID, personID)
);

Crea la relazione Author, composta da due attributi: publicationID e
personID. La chiave primaria in questo caso è formata dalla combinazione
dei due attributi, come indicato dal vincolo di tabella PRIMARY KEY.
PublicationID è una chiave esterna verso la relazione Publication, mentre
personID lo è verso la relazione Person.

Il file create_biblio.sql contiene tutti i comandi necessari per creare la struttura del database bibliografico di esempio.

NOTA SU POSTGRESQL: In PotgreSQL, almeno fino alla versione 6.5.1, non sono ancora stati
implementati i vincoli sulle chiavi esterne. Il parser comunque accetta le
sintassi SQL che li riguardano, quindi i costrutti FOREIGN KEY e REFERENCES
non producono un errore, ma solo un warning.