Primo e secondo modulo

ESAME BASI DI DATI I
Prof. Giovanni Giuffrida
3/10/2006
NON DIMENTICATE IL NOME E LA MATRICOLA
Nome ________________________________________
Matricola: ___________________
Punteggio finale (questa parte verrà riempita dal docente)
Algebra relazionale 1.1
1.2
1.3
1.4
SQL
1.2
1.3
1.4
1.1
Algebra relazionale
1. Date le relazioni
a. CHIAMATA(id_chiamata, id_operatore, id_cliente, data_ora, buonfine)
b. OPERATORE (id_operatore, nome, cognome)
c. CLIENTI (id_cliente, nome, cognome, numero_telefonico)
d. CONTRATTO(id_chiamata, id_rivista, data_inizio, durata_mesi)
e. RIVISTE(id_rivista, titolo, costo_mensile)
f. TENTATIVI(id_cliente, totale_chiamate)
Relative ad un call-center con degli operatori che provano a convincere i clienti a sottoscrivere degli
abbonamenti ad un certo numero di riviste. L’attributo “buonfine” nella relazione CHIAMATA è un
boolean che assume valore vero se il cliente accetta l’offerta illustrata nella chiamata. Nel caso di
accettazione viene indicato in CONTRATTO il tipo di abbonamento. Il significato del resto delle
tabelle è intuitivo.
Indicare le seguenti query in Algebra Relazionale:
1. [3 punti] Trovare il contratto che il sig. Rossi Giacomo ha accettato ed indicare l’operatore
chiamante (nota: potrebbero essere più di un contratto accettati);
2. [4 punti] Trovare i clienti che sono stati chiamati almeno due volte in un periodo inferiore a
due giorni;
3. [4 punti] Trovare gli operatori che non hanno mai chiuso un contratto nel 2005;
4. [5 punti] Trovare quei clienti che hanno ricevuto il numero minore di chiamate.
SQL
Utilizzando lo schema relazionale dato sopra indicare le seguenti query SQL. Ove possibile
utilizzate delle VIEWS per rendere le vostre query più leggibili (miglior leggibilità = miglior voto):
1. [3 punti] Trovare il numero di chiamate effettuate da ogni operatore nel 2006 ed indicare
solo gli operatori che hanno eseguito almeno dieci chiamate;
2. [3 punti] Trovare il numero di clienti il cui prefisso inizia per 095 che hanno accettato un
contratto nell’intervallo di date che va dal 1-9-2005 all’1-9-2006;
3. [4 punti] Trovare gli operatori che hanno chiuso il numero massimo di contratti nel giorno
15/09/2006;
4. [5 punti] Trovare gli operatori che hanno chiuso un numero di contratti superiore al numero
medio globale tra tutti gli operatori.
ESAME BASI DI DATI II
Prof. Giovanni Giuffrida
3/10/2006
NON DIMENTICATE IL NOME E LA MATRICOLA
Nome ________________________________________
Matricola: ___________________
Punteggio finale (questa parte verrà riempita dal docente)
2.1
2.2
2.3
2.1 Progettazione [25 punti]
Si vuole progettare una base di dati per la gestione di un ristorante. Un ristorante è composto da
diversi tavoli ognuno con un certo numero di sedie. Ogni tavolo è identificato univocamente. Un
cliente deve effettuare la prenotazione per telefono per un certo numero di persone per una certa
data ed una certa ora del giorno. Per ogni prenotazione bisogna inoltre conservare i dati del cliente
in modo da identificare questi univocamente (potete usare il numero di telefono del cliente).
Il cameriere al tavolo prende delle ordinazioni che devono essere quindi associate alla persona che
ha effettuato la prenotazione di quel tavolo. Un’ordinazione ad un tavolo è quindi composta da un
insieme di piatti (i.e., ricette), ogni ordinazione deve far capo alla persona che ha effettuato la
prenotazione del tavolo. Per ogni piatto bisogna archiviare nel DB gli ingredienti che servono e la
quantità necessaria per ognuno di questi. Bisogna anche gestire la parte di magazzino del ristorante.
Per ogni ingrediente bisogna quindi conservare la quantità disponibile in cucina e la data di
scadenza. Ogni ricetta è quindi composta da più ingredienti e bisogna garantire al cameriere che al
momento dell’ordinazione tutti gli ingredienti necessari per le ricette che vengono ordinate siano
disponibili.
Indicare quindi lo schema logico e relazionale indicando le dipendenze funzionali. Indicare le chiavi
primarie ed esterne e vincoli vari. Discutere la normalizzazione dello schema che avete progettato.
2.2 Trigger [4 punti]
Definire un trigger in formato IBM-DB2 che garantisca che al momento dell’ordinazione di una
ricetta tutti gli ingredienti necessari siano in quel momento disponibili in cucina, far fallire la
richiesta in caso contrario.
2.3 SQL [4 punti]
Fare un report in SQL che indichi per ogni cliente tutti gli ingredienti che quel cliente NON ha mai
mangiato, cioè che NON erano presenti nei piatti che quel cliente ha ordinato in tutta la sua storia.
(Nota: Potete usare il telefono del cliente che ha effettuato la prenotazione come riferimento).