Primo e secondo modulo

ESAME DB 12/09/2006
Prof. Giovanni Giuffrida
PRIMO MODULO (DB1)
NON DIMENTICATE IL NOME E LA MATRICOLA
Nome ________________________________________
Matricola: ___________________
INDICATE COSA DOVETE REGISTRARE
DB 9cfu
DB1 6cfu
DB2 6cfu
Altro: __________
Punteggio finale (questa parte verrà riempita dal docente)
1.1.1:
1.1.2:
1.1.3:
1.2.1:
1.2.2:
1.2.3:
1.1.4:
======== BUON LAVORO ========
1. Algebra relazionale
Date le relazioni
 CIRCUITO (id, nome, nazione, lunghezza, difficoltà)
 GRANPREMIO (id_gp, id_circuito, data)
 PILOTI (id, nome, cognome, id_squadra, punti, stagione)
 SQUADRE (id, nome, punti, stagione)
 ALBODORO(id_gp, id_pilota, stagione, posizione)
L’attributo “punti” nelle relazioni PILOTI e SQUADRE indica appunto i punti totali accumulati dai
piloti e dalle squadre rispettivamente. Mentre “posizione” in ALBODORO indica la posizione di
arrivo (1, 2, …) del pilota al granpremio indicato. Indicare le seguenti query in Algebra
Relazionale:
1. [2 punti] Trovare le caratteristiche del circuito del gran premio che si svolse il 12/9/2005;
2. [3 punti] Trovare le coppie di piloti appartenenti alla stessa squadra nella stessa stagione;
3. [4 punti] Trovare il pilota che ha vinto la stagione 2004-2005 (quindi il pilota che ha
accumulato il maggior numero di punti in quella stagione);
4. [5 punti] Trovare i piloti che hanno vinto almeno due gran premi nella loro carriera.
Indicando anche il/i nome/i della squadra/e ed il nome/i del/i circuito/i.
2. SQL
Utilizzando lo schema relazionale dato sopra indicare le seguenti query SQL. Ove possibile
utilizzate delle VIEWS possibile per rendere le vostre query più leggibili (miglior leggibilità =
miglior voto):
1. [3 punti] Trovare per ogni pilota il numero di squadre per cui ha corso;
2. [4 punti] Trovare il pilota che ha vinto il maggior numero di gran premi;
3. [5 punti] Trovare i piloti che in almeno una stagione nella loro carriera sono saliti sul podio
(sono quindi arrivati in prima, seconda o terza posizione) in TUTTI i circuiti.
4. [5 punti] Trovare i piloti che nella stagione 2004-2005 hanno accumulato un numero di
punti inferiori alla media (calcolata tra tutti i piloti nella stessa stagione);
ESAME DB 12/09/2006
Prof. Giovanni Giuffrida
SECONDO MODULO (DB2)
NON DIMENTICATE IL NOME E LA MATRICOLA
Nome ________________________________________
Matricola: ___________________
INDICATE COSA DOVETE REGISTRARE
DB 9cfu
DB1 6cfu
DB2 6cfu
Altro: __________
Punteggio finale (questa parte verrà riempita dal docente)
======== BUON LAVORO ========
2.1.1:
2.1.2:
2.1.3:
2.1.4:
1 Progettazione e SQL
Si vuole progettare un sistema per la gestione degli utenti privati di una società che fornisce energia
elettrica. Per ogni utente bisogna mantenere un codice identificativo, i dati anagrafici per il recapito
della bolletta e la tipologia di contratto (“residente”, “non residente”). Il sistema deve tenere traccia
della data di stipula del contratto.
Il sistema, bimestralmente, per ogni utente deve emettere una bolletta. Tale bolletta deve contenere
il numero di kilowatt consumati nei due mesi relativi, l’importo dovuto, la data di emissione e la
data di scadenza. Al momento del pagamento la bolletta viene marcata come pagata, e viene
indicata la data del pagamento. Se la data del pagamento è successiva alla scadenza la bolletta viene
marcata “in mora”, altrimenti viene marcata “a buon fine”.
Nel momento dell’emissione della bolletta, il sistema deve inoltre verificare la presenza di bollette
pagate precedentemente ma “in mora” e deve quindi prevedere l’addebito di 2.5€ per ogni bolletta
che si trova in questo stato. La bolletta inviata all’utente deve quindi dare indicazioni degli addebiti
per mora inclusi. Dopo l’avvenuto pagamento da parte dell’utente, tali bollette vanno riportate
nello stato “a buon fine”.
Il sistema deve prevedere inoltre un meccanismo di controllo delle bollette scadute e non pagate.
Tale meccanismo deve prevedere la possibilità di emettere una lettera di sollecito pagamento se la
bolletta è scaduta da oltre un mese e non risulta pagata. Delle lettere di sollecito pagamento si tiene
traccia della data di emissione della lettera stessa.
1. [20 punti] Sviluppare la progettazione concettuale e logica del sistema, indicare le
dipendenze funzionali (NON BANALI!!!), lo schema relazionale e discutere la
normalizzazione dello schema che avete ricavato.
2. [4 punti] Indicare una query SQL che estragga i dati necessari per la stampa della bolletta
come indicato nelle specifiche, che quindi includa insieme ai dati della bolletta quali
addebiti morosi vengono inclusi e relativi a quali bollette.
3. [4 punti] Indicare una query SQL per selezionare tutti coloro che non hanno mai ricevuto
una lettera di sollecito.
4. [4 punti] Prevedere un trigger che al momento dell’inserimento di un pagamento da parte
dell’utente marchi correttamente “a buon fine” sia la bolletta pagata che quelle morose.