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.