Esame di Basi di Dati

Esame di Basi di Dati
10 Settembre 2014
Matricola
CFU (9/12/9+9)
Cognome
Nome
Progetto (Sı̀/No)
Istruzioni
• I voti verranno resi disponibili su AlmaEsami. Chi vorrà rifiutare il voto
dovrà comunicarlo tassativamente 5 giorni dalla data della pubblicazione all’indirizzo [email protected], e ricevere conferma di avvenuta
ricezione della notifica. Passato questo termine, i voti di coloro che non li
hanno esplicitamente rifiutati si intendono accettati, e il docente provvederà a registrarli successivamente — il momento preciso in cui avverrà la
registrazione non è definito a priori, e può variare da appello ad appello,
l’unico termine fisso è quello dei 5 giorni per rifiutare il voto. Quanto
detto vale per i voti maggiori o uguali a 18.
• Prima di iniziare, scrivere nome, cognome e matricola su tutti i fogli, in
modo leggibile. Nella tabella sopra riportata indicare il numero di cfu del
corso nel proprio anno di iscrizione e se si è svolto il progetto.
• Scrivere le soluzioni direttamente su questi fogli, e utilizzare i fogli protocollo solo per la brutta: i fogli protocollo verranno ritirati, ma non
verranno corretti. Si può usare il verso se necessario. Non è gradito l’uso
delle matite: si deve utilizzare una penna.
• Non si possono utilizzare libri o appunti, né tenere telefoni cellulari o altri
dispositivi di comunicazione, neanche se spenti. Il tempo a disposizione è
di due ore.
• Si consiglia di non dare soltanto le soluzioni, ma anche i ragionamenti
che hanno portato alla soluzione. Questo ci permetterà di valutare anche
risposte parziali o parzialmente false.
Firma per la presa visione delle istruzioni:
1
1
Domande a risposta multipla
1. Quale di queste non è un’attività della ristrutturazione nella progettazione
logica:
(a) Scelta degli identificatori primari.
(b) Costruzione del glossario.
(c) Eliminazione delle generalizzazioni.
(d) Analisi delle ridondanze.
2. In algebra relazionale, il join naturale su relazioni senza attributi in comune:
(a) è un join vuoto.
(b) è un join incompleto.
(c) estende, con valori nulli, le ennuple che verrebbero tagliate fuori.
(d) diventa un prodotto cartesiano.
3. Quale dei seguenti non è un vincolo intrarelazionale in SQL:
(a) NOT NULL.
(b) UNIQUE.
(c) FOREIGN KEY.
(d) PRIMARY KEY.
4. Quale delle seguenti affermazioni è vera?
(a) L’algebra relazionale e il calcolo relazionale sono entrambi linguaggi
dichiarativi.
(b) L’algebra relazionale e il calcolo relazionale sono entrambi linguaggi
procedurali.
(c) L’algebra relazionale è dichiarativa, il calcolo relazionale è procedurale.
(d) L’algebra relazionale è procedurale, il calcolo relazionale è dichiarativo.
5. In una tabella che rappresenta una relazione:
(a) l’ordinamento tra le righe è irrilevante.
(b) l’ordinamento tra le colonne è irrilevante.
(c) Entrambe le precedenti.
(d) Nessuna delle precedenti.
6. Nel modello relazionale il valore nullo:
(a) denota sempre l’assenza di un valore del dominio.
(b) denota sempre un valore sconosciuto.
(c) denota il valore minimo del dominio.
(d) denota sempre un valore senza informazione.
2
2
Query
Si considerino le seguenti tabelle:
Ingrediente
id
nome
tipo
111 melanzana
ortaggio
222 grana padano formaggio
333 fusilli
pasta
444 salsa di soia
salsa
555 spaghetti
pasta
Composizione
id ricetta id ingrediente
1111
111
1111
333
2222
111
2222
222
2222
444
Ricetta
id
nome
1111 Pasta del contadino
2222 Spaghetti all’amatriciana
3333 Riso alla cantonese
cucina
Mediterranea
Mediterranea
Asiatica
difficoltà
4
3
5
(Importante: il contenuto delle tabelle è solamente un esempio; non possono trarsi conclusioni sull’assenza o la completezza dei dati.)
1. Scrivere in SQL una query che restituisce i tipi di cucina (es. Mediterranea,
Asiatica, . . . ) che hanno più di 20 ricette contenenti aglio, ordinate in
modo decrescente su questa quantità.
2. Scrivere in SQL una query che restituisce le ricette che contengono tra gli
ingredienti almeno uno di tipo ‘pasta’ ed almeno uno di tipo ‘formaggio’
(devono contenere entrambi i tipi di ingrediente).
3. Scrivere in algebra relazionale una query che restituisce i tipi di ingredienti
che non compaiono in nessuna delle ricette di cucina asiatica.
4. Scrivere in algebra relazionale una query che restituisce la cucina delle
ricette, con difficoltà superiore a 4, che non contengono nessun ingrediente
di tipo pasta.
3
3
Progettazione
Si vuole progettare una base di dati per un sito di aste online per la compravendita di automobili. L’utente che si registra con i propri dati anagrafici può
partecipare alle aste per aggiudicarsi un automobile. Per vendere la proprio auto e quindi creare un’asta è necessario anche verificare l’account con il proprio
numero di telefono e registrare il proprio codice fiscale. Chi crea un’asta non
può partecipare all’asta che ha creato. Le aste hanno un prezzo di partenza
scelto dal venditore, una durata in giorni, una descrizione testuale dell’auto, un
titolo e il modello dell’auto, identificato univocamente con la marca, il nome
del modello e l’anno. Per correttezza, all’utente che visualizza un’auto all’asta,
viene mostrata anche la valutazione standard per quello specifico modello. Le
valutazioni non sono inserite dagli utenti ma aggiornate dai gestori del sito.
E’ anche possibile visualizzare lo storico delle offerte per le aste in corso, che
include anche la data e l’ora di evasione delle offerte. Quando l’asta termina
l’ultimo offerente si aggiudica l’automobile al prezzo di chiusura, ed ogni utente
può successivamente visualizzare le aste vinte in passato. Per ogni asta chiusa,
l’aggiudicatario e il venditore possono lasciare un feedback relativo alla stessa.
Si disegni il modello concettuale del dominio descritto utilizzando i diagrammi E/R e si indichino gli eventuali vincoli non esprimibili.
5
4
Transazioni
Descrivere brevemente il modello ACID e le sue proprietà.
7
Traccia di soluzione dell’esame di Basi di dati del
10 Settembre 2014
(1) Domande a risposta multipla
1-B; 2-D; 3-C; 4-D; 5-C; 6-A.
(2) Query
1. SELECT Ricetta.cucina, count(Ricetta.id) FROM
Ricetta JOIN Composizione on Ricetta.id = Composizione.id_ricetta
JOIN Ingrediente on Ingrediente.id = Composizione.id_ingrediente
WHERE Ingrediente.nome = "aglio"
GROUP BY Ricetta.cucina
HAVING count(Ricetta.id) > 20
ORDER BY count(Ricetta.id) DESC
2. SELECT C1.id_ricetta FROM
(Composizione as C1 JOIN Ingrediente as I1 on C1.id_ingrediente = I1.id)
INNER JOIN
(Composizione as C2 JOIN Ingrediente as I2 on C2.id_ingrediente = I2.id)
on C1.id_ricetta = C2.id_ricetta
WHERE I1.tipo = ‘formaggio’ AND I2.tipo = "pasta"
3. πtipo Ingrediente−πtipo (σcucina=0 Asiatica0 Ricetta ./Ricetta.id=Composizione.id
Composizione ./Composizione.id ingrediente=Ingrediente.id Ingrediente)
ricetta
4. πcucina σdif f icolta>4 Ricetta−πcucina (σdif f icolta>4 Ricetta ./Ricetta.id=Composizione.id
Composizione ./Composizione.id ingrediente=Ingrediente.id σtipo=0 pasta0 Ingrediente)
9
ricetta
(3) Progettazione
Vincoli non esprimibili:
1. Non è possibile fare un’offerta su un’asta scaduta.
2. L’utente che si aggiudica l’asta è l’ultimo utente ad aver eseguito un’offerta.
3. Il prezzo dell’aggiudicazione è il prezzo dell’ultima offerta.
4. Gli attributi feedback, sia per l’acquisto che per la vendita, possono essere
popolati solo successivamente alla chiusura della relativa asta.
(4) Transazioni
ACID, modello per transazioni:
1. Atomicity (indivisibilita’ delle transazione);
2. Consistency (ogni transazione lascia la base di dati in uno stato valido);
3. Isolation (le transazioni non interferiscono tra loro);
4. Durability (anche se il sistema fallisce, i risultati delle transazioni gia’
compiute rimangono).
10