ESAME DB 13/06/2005 Prof. Giovanni Giuffrida PRIMO MODULO (DB1) Per ogni esercizio è indicato un punteggio massimo. Il punteggio massimo è dato se l’esercizio è privo di errori. 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 a) b) 1.2 a) b) c) d) e) f) g) h) i) ======== BUON LAVORO ======== 1.1 SQL Dato il seguente schema relazionale per modellare un torneo di calcio: giocatori( id_giocatore, nome, cognome, eta, id_squadra, ruolo) squadre( id_squadra, nome, citta) calendario( id_partita, id_sq_casa, id_sq_ospite, data) gol( id_partita, id_giocatore, minuto) Il significato delle tabelle è generalmente intuitivo. Il campo id_sq_casa indica l’id della squadra che gioca in casa, mentre id_sq_ospite è quello della squadra ospite (fuori casa). Il campo ruolo nella tabella giocatore indica il ruolo del giocatore: ‘portiere’, ‘terzino’, ‘attaccante’, ecc. Il campo minuto indica il minuto di gioco della partita quando è stato segnato il gol. Scrivere le seguenti query in SQL. Riportare in output SOLO le colonne richieste e rispettare l’ordinamento richiesto. Cercate di trovare la soluzione più semplice per le query che scrivete, la semplicità viene premiata. a) [1 punto] Elenco giocatori (nome, cognome, età) del Milan ordinati per età crescente. b) [1 punto] Elenco partite (squadre e data) che si svolgeranno a Roma. c) [2 punti] Per il giorno ‘15/05/2005’, per ogni squadra mostrare l’elenco dei gol fatti indicando, per ogni gol, il giocatore che ha segnato il gol ed il minuto di gioco. d) [2 punti] Nome e Cognome dei centrocampisti che hanno segnato almeno un gol in tutto il campionato. e) [2 punti] Nome e Cognome degli attaccanti che non hanno segnato nessun gol in tutto il campionato. f) [3 punti] Nome, Cognome e Squadra di appartenenza del giocatore più giovane del campionato. Nota: Potrebbero essercene più di uno. g) [4 punti] Per ogni squadra indicare il nome ed il numero totale di gol segnati dall’inizio del campionato. h) [4 punti] Indicare le partite (squadre e data) che sono terminate al punteggio di 1 a 1. i) [4 punti] Usando una tecnica diversa da quella utilizzata nel punto h), indicare le partite (squadre e data) che sono terminate al punteggio di 0 a 0. 1.2 Algebra Relazionale a) [da -9 a +9 punti] Per ogni punto dell’esercizio precedente indicare se la query può essere tradotta in algebra relazionale. Indicare solo SI o NO cerchiando la risposta. NON dovete scrivere la query in A.R., ma solo indicare se è fattibile o meno. Avrete 1 punto per ogni risposta corretta, -1 per ogni risposta sbagliata e 0 se non rispondete. Quindi, meglio non provare ad indovinare la risposta! a) b) c) d) e) f) g) h) i) SI SI SI SI SI SI SI SI SI NO NO NO NO NO NO NO NO NO b) Per ogni query per cui avete indicato SI nel punto precedente, guadagnate 2 punti se la scrivete in algebra relazionale. =========== FINE MODULO UNO (DB1) ============= ESAME DB 13/06/2005 Prof. Giovanni Giuffrida SECONDO MODULO (DB2) 2.1 TRIGGERS E’ data la seguente relazione: Impiegato( id, stipendio, progetto) nella quale esiste l’integrità referenziale: “id stipendio”. Un impiegato può lavorare a più progetti, quindi la chiave della relazione è: (id, progetto) a) [5 punti] Indicare (cerchiando la risposta SI/NO) per quali delle seguenti operazioni è necessario definire un trigger per garantire l’integrità referenziale indicata. NOTA: Prendete 1 punto per ogni risposta corretta, 0 se non rispondete e -1 per una risposta sbagliata, quindi: non indovinate, se non siete sicuri meglio non rispondere! insert into impiegato … delete from impiegato … update impiegato set id=… update impiegato set stipendio=… update impiegato set progetto=… SI SI SI SI SI NO NO NO NO NO b) [5 punti] Scrivere un trigger a scelta tra quelli che hai individuato nel punto precedente. Cercare di usare la sintassi IBM-DB2 per la scrittura del codice. 2.2 DIPENDENZE FUNZIONALI E NORMALIZZAZIONE Considerate le due relazioni seguenti: vendita( impiegato, sede, città, data, id_articolo, misura, colore); articolo( id_articolo, misura, colore, prezzo); In “vendita” vengono registrate le transazioni di vendita di un certo articolo da parte di un certo impiegato in una specifica sede in una certa data. In “articolo” vengono conservate le caratteristiche di tutti gli articoli. Considerate adesso le seguenti specifiche: Ogni impiegato è assegnato ad una sola sede; Ogni sede è in una città; Ogni articolo è disponibile in più misure e più colori; Un articolo ha lo stesso prezzo a prescindere dalla misura e dal colore; Indicate le soluzioni ai seguenti problemi: a) [2 punti] Basandovi sulle specifiche indicate (e solo quelle) definite le chiavi per le due relazioni “vendita” e “articolo”; b) [3 punti] Basandovi sulle specifiche indicate (e solo quelle) indicate delle dipendenze funzionali (non-banali) per le due relazioni date; c) [5 punti] Normalizzate lo schema relazionale dato ed indicate se e’ in 4^ forma normale o Boyce-Codd.