Si vuole progettare una base di dati per la gestione delle gite organizzate da un'agenzia di viaggi.
Per ogni gita si vogliono memorizzare:
- la descrizione;
- la data di partenza;
- la durata;
- il prezzo;
- il responsabile della gita;
- l'elenco dei partecipanti;
- l'itinerario.
Di ogni partecipante si vogliono memorizzare:
- nome;
- cognome;
- data di nascita.
Ogni gita è associata a un itinerario, costituito da una o più tappe.
Di ogni tappa si vogliono memorizzare:
- la località;
- la durata del soggiorno.
Analisi del problema
Si deve costruire uno schema relazionale che e' funzionale alle interrogazioni da fare sul database. Si suppone che l'agenzia di viaggio sia una sola e che non si voglia memorizzare informazioni relative alla agenzia di viaggi.
Le principali entità della base di dati sono:
- Gita
- Partecipante
- Tappa
- Comune
Associazione Gita-Partecipante
Tra Gita e Partecipante esiste una associazione molti-a-molti.
Infatti: una gita comprende molti partecipanti e un partecipante può prendere parte a più gite organizzate dall'agenzia. Nello traduzione verso lo schema relazionale, si ha una tabella di collegamento.
Le associazioni Cliente-Servizio sono di tipo molti a molti (ad esempio Cliente-AbbonamentoPalestra, Cliente-AnalisiMedica, Cliente-CorsoDiBallo, ecc.) perche' si suppone che il cliente si registra la prima volta quando usa quel servizio e, quando vuole usare quel servizio una seconda volta, il cliente risulta gia' registrato nel database. Anche l'associazione studente-scuola e' una associazione molti a molti, perche' uno studente puo' fare un anno presso un liceo e poi passare ad un istituto tecnico; per cui, se si vuole tener traccia dello storico, si deve modellare l'associazione come N:M. Allo stesso modo, se si vuole sapere a quante gite ha partecipato un cliente, si deve usare una associazione N:M.
Entita' Responsabile
Il responsabile e' un partecipante speciale alla gita, l'entita responsabile NON puo' essere visto una entita' specializzazione di partecipante. In modellazione E-R, una specializzazione (ISA) significa che il responsabile è un'entità con caratteristiche proprie, ma qui non ha nessun attributo aggiuntivo. È semplicemente un partecipante che, per una certa gita, svolge il ruolo di responsabile. Quindi basta una FK. Non serve introdurre il concetto di specializzazione.
Associazione Gita-Responsabile
L'associazione gita-responsabile e' di tipo uno ad uno. Ogni gita ha un responsabile. Il responsabile della gita e' un partecipante alla gita, in piu' e' anche il responsabile della gita. Il responsabile della gita cui ha tutti gli attributi di una partecipante e non è necessario duplicarne i dati anagrafici. È sufficiente memorizzare nella tabella Gite una chiave esterna che fa riferimento alla tabella Partecipanti. In questo modo il responsabile mantiene tutte le caratteristiche di un partecipante, evitando ridondanze.
Nota: L'associazione gita-responsabile e' come l'associazione ufficio-capoufficio di tipo uno ad uno, il capoufficio e' un impiegato che lavora in quell'ufficio ed e' anche il capo dell'ufficio. E' un errore aggiungere cognome e nome del responsabile alla gita; non sarebbe in 3FN, perche' nome e cognome non dipendono da idGita ma da idPartecipante.
Entita Gita
Gli attributi di Gita sono: descrizione, durata in giorni, prezzo, data di partenza. L'attributo descrizione vuole indicare, brevemente, la meta delle gita: ad esempio "Le Cinque Terre", "Arezzo-Cortona", "Isola del Giglio". La gita ha un itinerario, cioe' tutte le tappe della gita, ad esempio per le cinque terre le tappe sono: Monterosso al Mare, Vernazza, Corniglia, Manarola e Riomaggiore.
Associazione Gita-Tappe
Ogni gita è costituita da una o più tappe. Ogni tappa appartiene a una sola gita. La relazione tra Gita e Tappa è quindi uno-a-molti. Ogni tappa è inoltre associata a un comune.
Entita Tappa
Gli attributi di tappa sono durata e localita'. Siccome ci sono delle localita' che sono frazioni, non un comune, posso fare riferimento ad un comune. Un comune puo' essere associato a piu' tappe, una tappa si trova in solo un comune.
Schema relazionale
Gite(
IDGita,
Descrizione,
Durata,
Prezzo,
Data,
IDResponsabile (FK))
Partecipanti(
IDPart,
Cognome,
Nome,
DataNascita)
Gite_Partecipanti(
IDGita (FK),
IDPart (FK))
Tappe(
IDTappa,
Durata,
Localita,
IDComune (FK),
IdGita(FK))
Comuni(
IDComune,
NomeComune)
Lo schema rispetta la terza forma normale. Ogni attributo descrive esclusivamente la propria entità, non sono presenti dipendenze parziali né dipendenze transitive e le relazioni sono collegate tramite chiavi esterne.
Oltre alla verifica della normalizzazione, bisogna controllare se lo schema relazionale e' valido. Si controlla che la base di dati soddisfa i requisiti funzionali richiesti dalla applicazione, cioe' si controlla se si possono eseguire le interrogazioni che servono al committente. Ad esempio, per trovare nome e cognome di tutti i partecipanti alla gita "Cinque Terre", si eseguono due join, in cui l'ordine in cui si incrociano le tabelle non conta:
Esempio 1 - Partecipanti alla gita "Cinque Terre"
Algebra relazionale
π Cognome, Nome
(
σ Descrizione = 'Cinque Terre'
(
Gite
⋈ Gite_Partecipanti
⋈ Partecipanti
)
)
SQL
SELECT p.Cognome,
p.Nome
FROM Gite g
JOIN Gite_Partecipanti gp
ON g.IDGita = gp.IDGita
JOIN Partecipanti p
ON gp.IDPart = p.IDPart
WHERE g.Descrizione = 'Cinque Terre';
Esempio 2 - Tutte le gite di un partecipante
Algebra relazionale
π Descrizione, DataPartenza
(
σ IDPart = 15
(
Gite
⋈ Gite_Partecipanti
)
)
SQL
SELECT g.Descrizione,
g.DataPartenza
FROM Gite g
JOIN Gite_Partecipanti gp
ON g.IDGita = gp.IDGita
WHERE gp.IDPart = 15;
Esempio 3 - Elenco delle tappe di una gita
Algebra relazionale
π Localita, Durata
(
σ Descrizione = 'Cinque Terre'
(
Gite
⋈ Tappe
)
)
SQL
SELECT t.Localita,
t.Durata
FROM Gite g
JOIN Tappe t
ON g.IDGita = t.IDGita
WHERE g.Descrizione = 'Cinque Terre';
Esempio 4 - Nome del responsabile della gita
Algebra relazionale
π Nome, Cognome
(
σ Descrizione = 'Cinque Terre'
(
Gite
⋈ Partecipanti
)
)
con la condizione: Gite.IDResponsabile = Partecipanti.IDPart
SQL
SELECT p.Nome,
p.Cognome
FROM Gite g
JOIN Partecipanti p
ON g.IDResponsabile = p.IDPart
WHERE g.Descrizione = 'Cinque Terre';
