Esempio di progettazione di un Database

Esempio di Progettazione di un Database
Tema
Le informazioni sulle opere d’arte di molti artisti di epoche diverse, opere d’arte conservate nei musei delle
nazioni di tutto il mondo, devono essere catalogate e registrate in un archivio. In una stessa città ci possono
essere più musei, ogni museo è diretto da un direttore. Le opere possono essere di tipo diverso: tele,
sculture, ecc. Uno stesso artista può avere opere in tanti musei, così come in un museo, in genere, ci sono
opere di artisti diversi. Per sapere l’epoca di riferimento dell’artista si potrebbe registrare la data di nascita
e la data di morte (quest’ultima è vuota se l’artista è vivente). Può accadere anche che più artisti lavorino
alla creazione della stessa opera. Bisogna memorizzare la data di creazione dell’opera. L’opera inserita
nell’archivio può assumere un’identificazione numerica, come una specie di numerazione del catalogo delle
opere. Sviluppare il progetto concettuale, quello logico e realizzare l’implementazione del Database in SQL,
oltre che delle seguenti interrogazioni:
1. Elencare il titolo, il tipo e l’anno di tutte le opere d’arte realizzate tra il 1990 ed il 2000.
2. Elencare il titolo, il tipo e l’anno delle opere di tipo scultura, oltre alla denominazione del museo
che le ospita.
3. Elencare il titolo, il tipo, l’anno, il nome del museo, la città e la nazione delle opere realizzate
nell’anno 1995.
4. Elencare tutte le informazioni sulle opere di autori ancora in vita.
Soluzione
Analisi del problema
Analizzando il testo del problema si evidenziano chiaramente le seguenti entità: ARTISTA, OPERA, MUSEO,
CITTA’, DIRETTORE.
Progetto Concettuale
Progetto Logico
Nel progetto logico ricaviamo lo schema del Database secondo il modello logico di dati Relazionale,
applicando le regole di derivazione. Ecco l’elenco delle tabelle con la relativa struttura dei record.
Artista (cod_fisc, cognome, nome, data_nascita, data_morte, nazionalità)
Opera (numero, titolo, tipo, anno, cod_museo)
Direttore (cod_fisc, cognome, nome)
Museo (cod_museo, denominazione, cod_fisc, cod_città)
Città (cod_città, descrizione, nazione)
Crea (cod_fisc, numero, data_creazione)
Descriviamo ora il tipo e le proprietà dei campi dei vari record.
Tabella: Artista
Campo
Cod_fisc
cognome
nome
Data_nascita
Data_morte
nazionalità
Tabella: Opera
Campo
Numero
Titolo
Tipo
Anno
Cod_museo
Tabella: Direttore
Campo
Cod_fisc
Cognome
Nome
Tabella: Museo
Campo
Cod_museo
Denominazione
Cod_fisc
Cod_città
Tipo
Testo
Testo
Testo
Data
Data
Testo
Chiave
chiave primaria
Dimensione
Chiave
chiave primaria
16
20
20
20
Tipo
Numerico
Testo
Testo
Numerico
Testo
20
20
10
Tipo
Testo
Testo
Testo
Chiave esterna
Dimensione
Chiave
chiave primaria
Dimensione
Chiave
chiave primaria
16
20
20
Tipo
Testo
Testo
Testo
Testo
Dimensione
10
30
16
10
Chiave esterna
Chiave esterna
Tabella: Città
Campo
cod_città
Descrizione
Nazione
Tabella: Crea
Campo
cod_fisc
Numero
data_creazione
Tipo
Testo
Testo
Testo
Dimensione
Chiave
chiave primaria
Dimensione
Chiave
chiave primaria/esterna
chiave primaria/esterna
10
30
20
Tipo
Testo
Numerico
Data
16
Implementazione del Database
Comandi SQL per la creazione delle tabelle:
1. CREATE TABLE artista (Cod_fisc VARCHAR(16) PRIMARY KEY, cognome VARCHAR(20), nome
VARCHAR(20), Data_nascita DATE, Data_morte DATE, nazionalità VARCHAR(20));
2. CREATE TABLE opera (numero SMALLINT PRIMARY KEY, titolo VARCHAR(20), tipo VARCHAR(20),
anno SMALLINT, cod_museo VARCHAR(10) REFERENCES museo(cod_museo));
3. CREATE TABLE direttore (Cod_fisc VARCHAR(16) PRIMARY KEY, cognome VARCHAR(20), nome
VARCHAR(20));
4. CREATE TABLE museo (Cod_museo VARCHAR(10) PRIMARY KEY, denominazione VARCHAR(30),
cod_fisc VARCHAR(16) REFERENCES direttore(cod_fisc), cod_città VARCHAR(10) REFERENCES
città(cod_città));
5. CREATE TABLE città (Cod_città VARCHAR(10) PRIMARY KEY, descrizione VARCHAR(30), nazione
VARCHAR(20));
6. CREATE TABLE crea (Cod_fisc VARCHAR(16), numero SMALLINT, Data_creazione DATE, PRIMARY
KEY (cod_fisc, numero), FOREIGN KEY (cod_fisc) REFERENCES artista(cod_fisc), FOREIGN KEY
(numero) REFERENCES opera(numero));
Comandi SQL per l’interrogazione del Database (Query):
1. SELECT titolo, tipo, anno
FROM opera
WHERE anno>=1990 AND anno <=2000;
2. SELECT titolo, tipo, anno, denominazione
FROM opera, museo
WHERE opera.cod_museo = museo.cod_museo AND tipo=”scultura”;
3. SELECT titolo, tipo, anno, denominazione, descrizione, nazione
FROM opera, museo, città
WHERE opera.cod_museo = museo.cod_museo AND museo.cod_città = città.cod_città AND
anno=1995;
4. SELECT numero, titolo, tipo, anno, cognome, nome, nazionalità
FROM artista, crea, opera
WHERE artista.cod_fisc = crea.cod_fisc AND crea.numero = opera.numero AND data_morte IS
NULL;