Attività sportive studentesche
Le informazioni relative alle attività sportive studentesche devono essere organizzate in una base di dati. Gli studenti, dei quali si conservano le informazioni anagrafiche, frequentano gli Istituti superiori, e possono partecipare a una o più manifestazioni sportive (specialità sportive diverse, giornate diverse, campionati che durano per mesi o gare di un giorno). Per ogni attività sportiva le scuole indicano un professore che svolge la funzione di riferimento e di allenatore: ogni professore segue una sola manifestazione, ma una stessa manifestazione può essere seguita da professori diversi di scuole diverse.
Definire il modello del database e rappresentare le seguenti interrogazioni:
• Numero degli studenti che partecipano a una determinata manifestazione sportiva. • Elenco anagrafico degli allenatori di un’attività sportiva.
• Elenco delle scuole (denominazione) con il numero di studenti che partecipano alle attività sportive. • Elenco delle scuole (con denominazione, indirizzo, telefono) con studenti che partecipano a
una determinata manifestazione sportiva.
ESERCIZI da 37 a 39 pag. 251 e da 73 a 75 pag. 253
• Elenco dei professori (cognome e nome) e scuole (denominazione) di appartenenza in ordine alfabetico.
• Numero degli studenti partecipanti di una determinata scuola per ciascuna delle manifesta- zioni sportive.
Analisi dei dati
Le entità che possono essere individuate nel problema sono:
• Istituto, per gli istituti di appartenenza di studenti e professori; suoi attributi sono: codice dell’istituto (chiave primaria), denominazione, indirizzo, telefono, tutti di tipo carattere. • Studente, per gli studenti delle diverse scuole che partecipano alle manifestazioni, con
attributi: codice dello studente (chiave Primaria), cognome, nome, data di nascita, classe; il codice dello studente e la classe sono di tipo numerico, la data di nascita è di tipo data mentre gli altri attributi sono di tipo carattere.
• Professore, per rappresentare i docenti che svolgono le funzioni di allenatore; suoi attributi sono: codice del professore, di tipo numerico (PK), cognome e nome di tipo carattere. • Manifestazione, per le attività sportive, con attributi: codice della manifestazione (PK),
descrizione, luogo, data di inizio per i tornei e i campionati o di svolgimento per le gare di una giornata. Tutti gli attributi sono di tipo carattere ad eccezione della data di inizio che è di tipo data.
Nell’ipotesi che i dati sugli Istituti siano comunque inseriti nel database, anche se non ci sono studenti o professori che partecipano alle manifestazioni di un determinato anno scolastico, si può disegnare il seguente modello Entità/Associazioni.
Modello E/R N 1 Frequentare Studente CodiceStud {PK} CognStud NomeStud DataNascita Classe Istituto CodiceIstituto {PK} Denominazione Indirizzo Telefono N Partecipare N 1 Impiegare N 1 N Coordinare Manifestazione CodiceManif {PK} Descrizione Luogo DataInizio Professore CodiceProf {PK} CognProf NomeProf
Associazione molti a uno, perché ci sono più studenti di un istituto che partecipano alle gare e uno studente frequenta un solo istituto.
Lettura del modello
Ogni istituto può essere frequentato da uno o più studenti, ogni studente deve frequentare un solo istituto.
Associazione molti a molti perché ogni studente partecipa a una o più manifesta- zioni e a ogni manifestazione partecipano più studenti.
Lettura del modello
Ogni studente può partecipare a una o più manifestazioni, ogni manifestazione può essere seguita da uno o più studenti.
Associazione uno a molti, perché ogni istituto può avere molti docenti che seguono le manifestazioni sportive e ogni docente afferisce a un solo istituto.
Lettura del modello
Ogni istituto può impiegare uno o più professori, ogni professore deve essere impiegato presso un solo istituto.
Associazione uno a molti perché ogni manifestazione può avere più professori coordinatori, provenienti da scuole diverse e c’è il vincolo che ogni professore segua una sola manifestazione.
Lettura del modello
Ogni manifestazione può essere coordinata da uno o più professori, ogni professore può coordinare una sola manifestazione.
Utilizzando le regole di derivazione si passa dal modello concettuale al modello logico, cioè alle tabelle: le associazioni uno a molti si rappresentano introducendo le chiavi esterne nelle tabelle Studenti e Professori per indicare l’istituto di appartenenza; nella tabella Professori viene aggiunta anche un’altra chiave esterna per l’associazione con l’entità Manifestazione; l’associazione molti a molti genera una nuova tabella Iscrizioni che contiene le chiavi primarie di Studenti e Manifestazioni più eventuali attributi dell’associazione, per esempio la data di iscrizione.
Tabelle
Istituti (CodiceIstituto, Denominazione, Indirizzo, Telefono)
Studenti (CodiceStud, CognStud, NomeStud, DataNascita, Classe, CodiceIstituto) Manifestazioni (CodiceManif, Descrizione, Luogo, DataInizio)
Professori (CodiceProf, CognProf, NomeProf, CodiceIstituto, CodiceManif) Iscrizioni (ID, CodiceStud, CodiceManif, DataIscrizione).
Le caratteristiche degli attributi delle tabelle sono illustrate nello schema seguente:
Tabella Attributo Chiave Formato Indicizzato
Istituti CodiceIstituto PK Carattere (4) sì
Denominazione Carattere (30) sì
Indirizzo Carattere (30) no
Telefono Carattere (12) no
Studenti CodiceStud PK Numerico (intero) sì
CognStud Carattere (30) sì
NomeStud Carattere (20) no
DataNascita Data/ora no
Classe Numerico (byte) no
CodiceIstituto FK Carattere (4) sì Manifestazioni CodiceManif PK Carattere (3) sì
Descrizione Carattere (40) sì
Luogo Carattere (20) no
DataInizio Data/ora no
Professori CodiceProf PK Numerico (intero) sì
CognProf Carattere (30) sì
NomeProf Carattere (20) no
CodiceIstituto FK Carattere (4) sì
CodiceManif FK Carattere (3) sì
Iscrizioni ID PK Numerico (autoinc) sì
CodiceStud FK Numerico (intero) sì
CodiceManif FK Carattere (3) sì
DataIscrizione Data/ora no
Interrogazioni
Le costanti utilizzate nelle condizioni di ricerca sono indicate tra parentesi quadre.
Nella rappresentazione delle interrogazioni mediante pseudocodifica sono stati inseriti com- menti nei punti dove sono previste operazioni quali ordinamenti, raggruppamenti e calcoli che non sono previste nel modello relazionale e non hanno una forma di rappresentazione standardizzata.
Numero degli studenti che partecipano a una determinata manifestazione sportiva.
1. σPManifestazioni Temp1 = Selezione di Manifestazioni per Descrizione = [manifestazione]
2. Temp1PK IscrizioniFK Temp2 = Congiunzione di Temp1 e Iscrizioni suCodiceManif
3. Conteggio delle righe di Temp2
SELECT COUNT(*) AS NumeroStudenti FROM Manifestazioni, Iscrizioni
WHERE Manifestazioni.CodiceManif = Iscrizioni.CodiceManif AND
Descrizione = [manifestazione da controllare];
Elenco anagrafico degli allenatori di un’attività o manifestazione sportiva.
1. σPManifestazioni Temp1 = Selezione di Manifestazioni per Descrizione = [manifestazione]
2. Temp1PK ProfessoriFK Temp2 = Congiunzione di Temp1 e Professori su CodiceManif
3. ΠL Temp2 Proiezione di Temp2 suCognProf, NomeProf Ordinato su CognProf e NomeProf
SELECT CognProf, NomeProf FROM Manifestazioni, Professori
WHERE Manifestazioni.CodiceManif = Professori.CodiceManif AND
Descrizione = [manifestazione da controllare]
ORDER BY CognProf, NomeProf;
Elenco delle scuole (denominazione) con il numero di studenti che partecipano alle attività sportive.
1. IstitutiPK StudentiFK Temp1 = Congiunzione di Istituti e Studenti su CodiceIstituto
2. Temp2 = Raggruppamento per Denominazione e Conteggio 3. ΠL Temp2 Proiezione di Temp2 su Denominazione, Conteggio
SELECT Denominazione, COUNT (DISTINCT CodiceStud) AS Numero FROM Studenti, Istituti
WHERE Studenti.CodiceIstituto = Istituti.CodiceIstituto GROUP BY Denominazione;
Elenco delle scuole (con denominazione, indirizzo, telefono) con studenti che partecipano a una determinata manifestazione sportiva.
1. σPManifestazioni Temp1 = Selezione di Manifestazioni per Descrizione = [manifestazione]
2. Temp1PK IscrizioniFK Temp2 = Congiunzione di Temp1 e Iscrizioni su CodiceManif
3. Temp2FK StudentiPK Temp3 = Congiunzione di Temp2 e Studenti suCodiceStud
4. Temp3FK IstitutiPK Temp4 = Congiunzione di Temp3 e Istituti su CodiceIstituto
5. Temp5 = Raggruppamento di Temp4 per Denominazione 6. ΠL Temp5 Proiezione di Temp5 su Denominazione, Indirizzo, Telefono
SELECT Denominazione, Indirizzo, Telefono
FROM Manifestazioni, Iscrizioni, Studenti, Istituti
WHERE Manifestazioni.CodiceManif = Iscrizioni.CodiceManif AND
Studenti.CodiceStud = Iscrizioni.CodiceStud AND Studenti.CodiceIstituto = Istituti.CodiceIstituto AND Descrizione = [manifestazione da controllare]
Elenco alfabetico dei professori (cognome e nome) e scuole (denominazione) di appartenenza.
1. IstitutiPK ProfessoriFK Temp1 = Congiunzione di Istituti e Professori suCodiceIstituto
2. ΠL Temp1 Proiezione di Temp1 su CognProf, NomeProf, Denominazione
ordinato su CognProf e NomeProf
SELECT CognProf, NomeProf, Denominazione FROM Professori, Istituti
WHERE Professori.CodiceIstituto = Istituti.CodiceIstituto ORDER BY CognProf, NomeProf;
Numero degli studenti partecipanti di una determinata scuola per ciascuna delle manifestazio- ni sportive.
1. σPIstituti Temp1 = Selezione di Istituti perDenominazione = [scuola]
2. Temp1PK StudentiFK Temp2 = Congiunzione di Temp1 e Studenti su CodiceIstituto
3. Temp2PK IscrizioniFK Temp3 = Congiunzione di Temp2 e Iscrizioni su CodiceStud
4. Temp3FK ManifestazioniPK Temp4 = Congiunzione di Temp3 e Manifestazioni suCodiceManif
5. Temp5 = Raggruppamento di Temp4 per Descrizione e Conteggio 6. ΠL Temp5 Proiezione di Temp5 su Descrizione, Conteggio
SELECT Descrizione, COUNT(*) AS Numero
FROM Manifestazioni, Iscrizioni, Studenti, Istituti
WHERE Manifestazioni.CodiceManif = Iscrizioni.CodiceManif AND
Studenti.CodiceStud = Iscrizioni.CodiceStud AND Studenti.CodiceIstituto = Istituti.CodiceIstituto AND Denominazione = [scuola da controllare]
GROUP BY Descrizione;
Viaggi e vacanze.
Un’agenzia di viaggi intende organizzare in una base di dati tutte le informazioni riguardanti la sua attività: i viaggi o vacanze, le organizzazioni turistiche o tour operator, i clienti dei propri servizi, le nazioni o località o città che sono destinazione dei viaggi/vacanze.
Tutte le attività proposte ai clienti sono dei pacchetti: ciascuno di essi si riferisce a un’organiz- zazione turistica, e riguardano una precisa zona del mondo o nazione o città. Ci possono essere offerte di viaggi con la stessa destinazione da parte di organizzazioni diverse.
Per esempio ci può essere una crociera nel Mediterraneo proposta dall’organizzazione turistica A, oppure un soggiorno di una settimana a Vienna offerta dall’organizzazione turistica B. Di ogni pacchetto offerto occorre conoscere modalità e prezzi; di ogni località o nazione è opportuno tenere memorizzate informazioni su condizioni climatiche, moneta, ecc.
Un cliente può naturalmente acquistare in date diverse molti viaggi o vacanze. Definire il modello del database e rappresentare le seguenti interrogazioni: • Elenco dei viaggi/vacanza con prezzo inferiore a un prezzo prefissato. • Elenco dei soggiorni con prezzo in una località prefissata.
• Elenco dei clienti, con nome e indirizzo, che hanno fatto un viaggio con una determinata destinazione.
• Numero dei pacchetti offerti da un’organizzazione turistica prefissata.
• Elenco di tutte le nazioni o località con il numero di pacchetti che si riferiscono ad esse.
ESERCIZIO GUIDATO
APPLICAAnalisi dei dati
Le entità che possono essere individuate nel problema sono:
• Nazione, per rappresentare gli stati o le località che sono destinazioni dei pacchetti di vacanze; attributi di Nazione sono: il codice della nazione o località, denominazione più dettagliata.
• Organizzazione, per le società di turismo che offrono i viaggi e i soggiorni di vacanze, con attributi: codice dell’organizzazione, nome, telefono.
• Cliente, per l’anagrafica dei clienti dell’agenzia di viaggi; attributi di Cliente sono: codice del cliente, cognome e nome, indirizzo, telefono.
• Pacchetto, per rappresentare i pacchetti di viaggio o soggiorno, con attributi: codice del pacchetto, descrizione, modalità, prezzo.
Modello E/R 1 N Offrire Organizzazione CodiceOrg {PK} NomeOrg TelefonoOrg Pacchetto CodicePac {PK} Descrizione Modalità Prezzo Cliente CodiceCli {PK} Cognome Nome Indirizzo Telefono N N Acquistare Nazione CodiceNaz {PK} Denominazione Moneta Clima Essere destinazione N 1
Utilizzando le regole di derivazione si passa dal modello concettuale al modello logico, cioè alle tabelle: le associazioni uno a molti si rappresentano introducendo le chiavi esterne nella tabella Pacchetti per indicare l’organizzazione proponente e la nazione o località di destina- zione.
L’associazione molti a molti genera una nuova tabella Acquisti. Essa ha come chiave un numero progressivo di registrazione e contiene le chiavi di Pacchetti e Clienti più eventuali attributi dell’associazione, per esempio la data in cui il cliente ha acquistato il pacchetto.
Associazione uno a molti, perché un’organizzazione può offrire molti pacchetti, ma ogni pacchetto deve essere inteso come offerto da una precisa organizzazione.
Associazione molti a molti, perché un cliente può acquistare, in date diverse, più viaggi e lo stesso pacchetto viene acquistato da più clienti.
Lettura del modello
Ogni organizzazione può offrire uno o più pacchetti, ogni pacchetto deve essere offerto da una sola organizzazione.
Associazione uno a molti perché ci possono essere più viaggi che hanno come destinazione una certa nazione.
Lettura del modello
Ogni pacchetto può essere acquistato da uno o più clienti, ogni cliente può acquistare uno o più pacchetti.
Lettura del modello
Ogni nazione può essere destinazione di uno o più pacchetti, ogni pacchetto deve avere destinazione in una sola nazione.
Tabelle
Nazioni (CodiceNaz, Denominazione, Moneta, Clima) Clienti (CodiceCli, Cognome, Nome, Indirizzo, Telefono) Organizzazioni (CodiceOrg, NomeOrg, TelefonoOrg)
Pacchetti (CodicePac, Descrizione, Modalità, Prezzo, CodiceNaz, CodiceOrg) Acquisti (NumReg, CodiceCli, CodicePac, DataAcquisto)
Lo schema seguente illustra le caratteristiche degli attributi delle tabelle.
Tabella Attributo Chiave Formato Indicizzato
Nazioni CodiceNaz PK Carattere (3) sì
Denominazione Carattere (20) sì
Moneta Carattere (15) no
Clima Carattere (30) no
Clienti CodiceCli PK Numerico (intero) sì
Cognome Carattere (30) sì
Nome Carattere (20) no
Indirizzo Carattere (25) no
Telefono Carattere (12) sì
Organizzazioni CodiceOrg PK Carattere (4) sì
NomeOrg Carattere (30) sì
TelefonoOrg Carattere (12) no
Pacchetti CodicePac PK Carattere (5) sì
Descrizione Carattere (50) sì
Modalità Carattere (40) no
Prezzo Numerico (2 decimali) no
CodiceNaz FK Carattere (3) sì
CodiceOrg FK Carattere (4) sì
Acquisti NumReg PK Numerico (autoinc) sì
CodiceCli FK Numerico (intero) sì
CodicePac FK Carattere (5) sì
DataAcquisto Data/ora no
Interrogazioni
Elenco dei viaggi/vacanza con prezzo inferiore a un prezzo prefissato.
1. σPPacchetti Selezione di Pacchetti perPrezzo < [prezzo massimo]
SELECT * FROM Pacchetti
WHERE Prezzo < [prezzo massimo];
Elenco dei soggiorni con prezzo in una località prefissata.
1. σPNazioni Temp1 = Selezione di Nazioni perDenominazione = [località]
2. Temp1PK PacchettiFK Temp2 = Congiunzione di Temp1 e Pacchetti su CodiceNaz
SELECT Descrizione, Modalità, Prezzo FROM Nazioni, Pacchetti
WHERE Nazioni.CodiceNaz = Pacchetti.CodiceNaz AND Denominazione = [località prefissata];
Elenco dei clienti, con cognome, nome e indirizzo, che hanno fatto un viaggio con una deter- minata destinazione.
1. σPNazioni Temp1 = Selezione di Nazioni perDenominazione = [località]
2. Temp1PK PacchettiFK Temp2 = Congiunzione di Temp1 e Pacchetti suCodiceNaz
3. Temp2PK AcquistiFK Temp3 = Congiunzione di Temp2 e Acquisti su CodicePac
4. Temp3FK ClientiPK Temp4 = Congiunzione di Temp3 e Clienti suCodiceCli
5. ΠL Temp4 Proiezione di Temp4 suCognome, Nome, Indirizzo
SELECT Cognome, Nome, Indirizzo
FROM Nazioni, Pacchetti, Acquisti, Clienti
WHERE Nazioni.CodiceNaz = Pacchetti.CodiceNaz AND
Pacchetti.CodicePac = Acquisti.CodicePac AND Acquisti.CodiceCli = Clienti.CodiceCli AND Denominazione = [località da controllare];
Numero dei pacchetti offerti da un’organizzazione turistica prefissata.
1. σPOrganizzazioni Temp1 = Selezione di Organizzazioni perNomeOrg = [organizzazione]
2. Temp1PK PacchettiFK Temp2 = Congiunzione di Temp1 e Pacchetti su CodiceOrg
3. ΠL Temp2 Proiezione di Temp2 su Conteggio
SELECT COUNT(*) AS Numero FROM Pacchetti, Organizzazioni
WHERE Pacchetti.CodiceOrg = Organizzazioni.CodiceOrg AND
NomeOrg = [organizzazione da controllare];
Elenco di tutte le nazioni o località con il numero di pacchetti che si riferiscono ad esse.
1. NazioniPK PacchettiFK Temp1 = Congiunzione di Nazioni e Pacchetti suCodiceNaz
2. Temp2 = Raggruppamento di Temp1 per Denominazione e Conteggio 3. ΠL Temp2 Proiezione di Temp2 suDenominazione, Conteggio
SELECT Denominazione, COUNT(*) AS Numero FROM Nazioni, Pacchetti
WHERE Nazioni.CodiceNaz = Pacchetti.CodiceNaz GROUP BY Denominazione;