Primo e secondo modulo

ESAME DB 04/07/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.2.1:
1.2.1:
1.1.2:
1.2.2:
1.3.2:
1.2.3:
1.2.4:
======== BUON LAVORO ========
1.1 SQL
1. [3 punti] Descrivere la differenza tra “semi-join” e “outer join”;
2. [5 punti] Mostrare DUE diverse possibili implementazioni SQL di un semi-join partendo da
un esempio significativo di uno schema relazionale da te scelto a piacere;
1.2 SQL
Dato il seguente schema relazionale:
studente( matricola, Nome, Cognome, Indirizzo);
esami( matricola, id_corso, voto);
corsi( id_corso, nome);
Rispondere alle seguenti query in SQL:
1. [2 punti] Elencare i corsi (nome e id del corso) presi da meno di 5 studenti;
2. [4 punti] Per ogni corso dare il nome dello studente/i col voto più basso;
3. [4 punti] Elenco degli studenti (matricola, nome e cognome) di tutti coloro che hanno
sostenuto al massimo un solo esame (nota bene: 0 o 1 esame, non di più!);
4. [5 punti] Elenco degli studenti che hanno sostenuto tutti gli esami, cioè che hanno dato tutti i
corsi disponibili;
1.3 Algebra relazionale
A vostro piacimento scrivete in algebra relazionale DUE delle query SQL del punto precedente. [4
punti per ogni query].
ESAME DB 04/07/2006
Prof. Giovanni Giuffrida
SECONDO MODULO (DB2)
NON DIMENTICATE IL NOME E LA MATRICOLA
Nome ________________________________________
Matricola: ___________________
INDICATE COSA DOVETE REGISTRARE
DB 9cfu
2.1.1:
2.2.1:
2.3:
DB1 6cfu
DB2 6cfu
Altro: __________
Punteggio finale (questa parte verrà riempita dal docente)
2.1.2:
2.2.2:
2.1.3:
2.2.3:
2.1.4:
======== BUON LAVORO ========
Considerate il seguente schema relazionale relativo ad un’attività di noleggio film:
clienti( id, nome, cognome, indirizzo);
film( id, titolo, lingua, lingua_sottotitoli);
dvd( id, film_id, prezzo, stato);
prenotazioni( id_cliente, id_film, data_prenotazione);
noleggio( id_cliente, id_film, id_dvd, data_noleggio, data_restituzione);
Nota la differenza tra film e dvd: per “film” s’intende l’opera artistica in quanto tale, per “dvd”
s’intende l’oggetto fisico che la contiene; quindi si potrebbero avere più dvd in magazzino con lo
stesso film. Il campo “stato” indica se il dvd e’ disponibile (d) o noleggiato (n). La
“data_restituzione” indica la data in cui il cliente ha restituito il film, potete usare NULL per
indicare che il cliente non ha ancora restituito il film.
2.1 Vincoli
Utilizzando a vostro piacimento qualsiasi delle tecniche che abbiamo visto a lezione, mostrare
l’implementazione in SQL per garantire i seguenti vincoli d’integrità:
1. [3 punti] L’affitto di un dvd non può costare più di 4 euro;
2. [3 punti] Un film non può avere la stessa lingua per il parlato e per i sottotitoli;
3. [4 punti] Lo stesso film (nota: non dvd) non può essere prenotato dallo stesso cliente nello
stesso giorno;
4. [4 punti] Un cliente non può avere contemporaneamente più di tre film in noleggio (anche se
noleggiati in date diverse)
2.2 Dipendenze funzionali, chiavi e normalizzazione
1. [4 punti] Definite delle dipendenze funzionali significative e NON BANALI!! per il
problema indicato;
2. [3 punti] Definite le chiavi primarie ed esterne sullo schema;
3. [6 punti] Discutere la normalizzazione, in particolare, se lo schema dato non è
normalizzato secondo le vostre dipendenze funzionali, provvedete a normalizzarlo.
2.3 Trigger
[4 punti] Definire un trigger in formato IBM-DB2 sullo schema sopra indicato che inserisca in una
tabella “sorvegliati(id_cliente)” quei clienti che nell’anno precedente la data di restituzione del loro
ultimo film noleggiato hanno, per almeno tre volte, restituito un film dopo cinque o più giorni.