Soluzioni compiti - Tutorato Basi di Dati

30/10/2008 primo parziale
Esercizio 1
L’ospedale Seattle Grace Hospital vi ha incaricato di progettare un sistema di gestione degli
interventi chirurgici basato su database in modo tale che non sia più necessario utilizzare la
lavagna degli interventi.
Le specifiche prevedono che per ciascun intervento occorra memorizzare la data, l’ora e la
sala operatoria in cui viene eseguito. Di ciascuna sala operatoria occorre tenere traccia del
numero della stanza, del piano a cui si trova, delle attrezzature disponibili. Ciascun intervento
è relativo ad un solo paziente (SSN, Nome, Cognome, DataNascita, Sesso, ...) e viene eseguito
da uno o più chirurgi dello staff (IDmedico, Nome, Cognome, DataNascita, Sesso,
Specializzazione). A ciascun intervento possono partecipare uno o più specializzandi
(IDspecializzando, Nome, Cognome, DataNascita, Sesso, ...).
(a) Fornire uno schema ER per il sistema informativo richiesto;
(b) specificare nelle due notazioni studiate i rapporti di cardinalità ed i vincoli di
partecipazione delle associazioni;
(c) derivare uno schema relazionale dal diagramma ER ottenuto al punto (a).
SalaOperatoria (NStanza, Piano)
Attrezzature (ID, NStanza, Descrizione)
Intervento (Data, Ora, NStanza, SSN)
Pazienti (SSN, Nome, Cognome, DataNascita, Sesso)
Chirurgi (IDmedico, Nome, Cognome, DataNascita, Sesso)
Specializzandi (IDspecializzando, Nome, Cognome, DataNascita, Sesso)
Opera (IDmedico, Data, Ora, NStanza)
Specializzandi (IDspecializzando, Data, Ora, NStanza)
Esercizio 2
Considerato lo schema relazionale:
FILM (IDFilm, IDTipoFilm, TitoloFilm, Anno, AttorePrincipale, Regista)
TIPOFILM (IDTipoFilm, TipoFilm)
PERSONE (IDPersona, NomeCognome, Attore, Regista)
RECENSIONE (IDRecensione, Data, NomeRecensore, Commento,Voto, IDFilm)
utilizzato per gestire le recensioni di film. Gli attributi Attore e Regista della relazione
PERSONE sono di tipo booleano.
a) Indicare le chiavi esterne presenti nello schema;
FILM (IDTipoFilm) ⊆ TIPOFILM (IDTipoFilm)
FILM (AttorePrincipale) ⊆ PERSONE (IDPersona)
FILM (Regista) ⊆ PERSONE (IDPersona)
FILM (IDTipoFilm) ⊆ TIPOFILM (IDTipoFilm)
RECENSIONE (IDFilm) ⊆ FILM (IDFilm)
b) Elenco di tutti i voti ottenuti dal film il cui titolo è “Star Wars I”;
RISULTATO ← πVoto (σ TitoloFilm = 'Star Wars I'( FILM * RECENSIONE))
c)
Elenco di tutti i film di tipo “Commedia”;
RISULTATO ← σTipoFilm = 'Commedia' (FILM * TIPOFILM)
d) Elenco di tutti i titoli dei film il cui voto medio nelle recensioni è >7;
ρMEDIE_VOTI
( (IDFilm, TitoloFilm) f AVG(Voto) (FILM * RECENSIONE))
ρRISULTATO (σ Media > 7 (MEDIE_VOTI))
(IDFilm, TitoloFilm, Media)
e)
Visualizzare il titolo del film ed il nome della persona che in quel film ha avuto il ruolo sia da attore sia di
regista
RISULTATO ← πTitoloFilm, NomeCognome ( σAttorePrincipale = Regista (FILM▷◁Regista = IDPersona PERSONA))
Esercizio 3
Si consideri il seguente diagramma ER che fornisce un modello concettuale di un sistema per
la gestione delle visite dei pazienti dell’ospedale Princeton-Plainsboro Teaching Hospital:
in cui le entità hanno gli attributi tipici per un problema di questo tipo;
(a) Indicare lo schema relazionale corrispondente al diagramma:
Medico(IDmedico, Nome, Sesso, DataNascita)
Visita (IDvisita, Data, Ora, SalaInfermieria)
Paziente (SSN, Nome, Sesso, DataNascita)
Ricoverato (IDricovero, SSN, DataIngresso, DataUscita, Urgenza)
Diagnosi (IDdiagnosi, IDricovero, Malattia, Trattamento)
VisitaMedico (IDmedico, IDvisita)
VisitaRicoverato (IDvisita, IDricovero)
(b) Scrivere l’interrogazione per specificare quanti pazienti hanno avuto come diagnosi la
malattia “Lupus” :
f COUNT(Malattia) (σ Malattia = 'Lupus' (π SSN, Malattia (Paziente∗Ricoverato∗Diagnosi)))
(c) scrivere l’interrogazione per elencare i pazienti che sono stati visitati almeno una volta
dal medico “Allison Cameron”:
IDvisiteCameron ←π IDvisita (σ Nome = 'Allison Cameron' (Medico∗VisitaMedico∗Visita))
Risultato ← π SSN (IDvisiteCameron∗VisitaRicoverato∗Ricoverato∗Paziente)
24/03/2010 secondo parziale
Esercizio 1
Dato il seguente schema relazionale:
ATTORE (codAttore, cognome, nome, dataNascita)
SPETTACOLO (codSpettacolo, nomeSpettacolo, descrizione, durata, genere, regista, anno)
PARTECIPAZIONE (codAttore: ATTORE, codSpettacolo: SPETTACOLO, ruolo)
(a) Scrivere il codice SQL necessario a creare lo schema, esprimendo esplicitamente i
vincoli di integrità referenziale e le relative politiche di gestione delle violazioni del
vincolo.
CREATE TABLE ATTORE(
codAttore INT(10) NOT NULL,
cognome VARCHAR(30),
nome VARCHAR(30),
dataNascita DATE,
PRIMARY KEY (codAttore));
CREATE TABLE SPETTACOLO(
codSpettacolo INT(10) NOT NULL,
nomeSpettacolo VARCHAR(30),
descrizione VARCHAR(255),
durata TIME,
genere VARCHAR(30),
regista VARCHAR(30),
anno INT(4),
PRIMARY KEY (codSpettacolo));
CREATE TABLE PARTECIPAZIONE(
codAttore INT(10) NOT NULL,
codSpettacolo INT(10) NOT NULL,
FOREIGN KEY (codAttore) REFERENCES ATTORE(codAttore),
FOREIGN KEY (codSpettacolo) REFERENCES SPETTACOLO(codSpettacolo),
FOREIGN KEY (codAttore, codSpettacolo));
(b) Scrivere il codice SQL necessario a inserire un nuovo spettacolo in cui recitano gli
attori ‘Pinco Pallino’ e ‘Tizio Caio’ (gia’ presenti nel database con codAttore = 27 e
codAttore = 49, rispettivamente)
INSERT INTO SPETTACOLO
VALUES ('1209', 'Le idi di marzo', 'Rappresentazione teatrale',
'01:30:00', 'Dramma', 'Pupi Avati', '1995');
INSERT INTO PARTECIPAZIONE VALUES ('27', '1209');
INSERT INTO PARTECIPAZIONE VALUES ('49', '1209');
Esercizio 2
Dato il seguente schema relazionale:
CONCERTO (codConcerto, titolo, descrizione, data, prezzo, nomeOrchestra: ORCHESTRA)
PEZZO (codPezzo, titoloPezzo, autore)
PROGRAMMA (codConcerto: CONCERTO, codPezzo: PEZZO, posizioneScaletta)
ORCHESTRA (nomeOrchestra, direttoreOrchestra)
ORCHESTRALE (matricolaOrchestrale, nominativoOrchestrale, strumento)
COMPOSIZIONEORCHESTRA (nomeOrchestra: ORCHESTRA,
matricolaOrchestrale: ORCHESTRALE)
(a) Scrivere la query SQL che visualizza (una sola volta!) i concerti dell’orchestra ‘ORFE’
svolti nel periodo compreso tra il 10/01/2010 e il 20/03/2010 in cui sia stato eseguito
almeno un pezzo di ‘Bach’ (codConcerto, titolo)
SELECT *
FROM CONCERTO
WHERE data BETWEEN '2010-01-10' AND '2010-03-20'
AND EXIST (SELECT *
FROM PROGRAMMA NATURAL JOIN PEZZO AS PEZZI
WHERE PEZZI.codConcerto = CONCERTO.codConcerto
AND PEZZI.autore = 'Bach')
(b) Scrivere la query SQL che visualizza il concerto del 2010 che ha in programma il
maggior numero di pezzi (codConcerto, titolo, data, numeroPezzi)
SELECT titolo, descrizione, data, prezzo, nomeOrchestra
FROM CONCERTO NATURAL JOIN PROGRAMMA
WHERE data BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY (codConcerto, titolo, descrizione, data, prezzo, nomeOrchestra)
HAVING COUNT(*) >= (SELECT COUNT(*)
FROM CONCERTO NATURAL JOIN PROGRAMMA
WHERE data BETWEEN '2010-01-01' AND '2010-12-31'
GROUP BY (codConcerto))
(c) Scrivere un’espressione di algebra relazionale OPPURE la query SQL che visualizza i
pezzi musicali (codPezzo, titoloPezzo, autore) che non sono stati inseriti nel
programma di nessun concerto dopo il 01/01/2010
SELECT *
FROM PEZZO
WHERE NOT EXISTS (SELECT *
FROM CONCERTO NATURAL JOIN PROGRAMMA AS CP
WHERE CP.codPezzo = PEZZO.codPezzo
AND CP.data > '2010-01-01')
(d) Scrivere una query SQL che visualizza, per ogni orchestra, il numero di orchestrali che
suonano ciascun strumento (nomeOrchestra, strumento, numeroOrchestrali)
SELECT nomeOrchestra, strumento, COUNT(nominativoOrchestrale) AS
numeroOrchestrali
FROM ORCHESTRALE NATURAL JOIN COMPOSIZIONE ORCHESTRA
GROUP BY nomeOrchestra, strumento
(e) Scrivere la query SQL che seleziona, per ogni mese dell’anno 2009, il concerto più
economico (mese, codConcerto, titolo, data, prezzo)
SELECT MONTH(data) AS mese, codConcerto, titolo, data, MIN(prezzo)
FROM CONCERTO
WHERE data BETWEEN '2009-01-01' AND '2009-12-31'
GROUP BY mese
Esercizio 3
È dato il seguente schema relazionale:
CORSO (codPiscina, indirizzo, città, numeroCorsia, codCorso, nomeCorso, giorno, ora)
ABBONAMENTO (codIscritto, nome, indirizzo, telefono, numeroAbbonamento, codPiscina,
codCorso)
Sapendo che:
• Ogni piscina ha più corsie, ciascuna identificata da un codice univoco all’interno della
piscina.
• Ciascun corso è identificato da un codice univoco all’interno della piscina.
• Ogni corso prevede più lezioni che si svolgono tutte nella stessa corsia, ma in giorni
diversi.
• Non si svolgono mai due lezioni dello stesso corso nello stesso giorno.
• Gli abbonamenti sono identificati da un codice univoco, hanno validità per una sola
delle piscine in archivio e danno accesso a tutte le lezioni di un corso specifico.
• Ogni abbonamento è associato a un solo iscritto.
• Ciascun iscritto, identificato univocamente dal codice, può avere più abbonamenti.
(a) si evidenzino tutte le dipendenze funzionali non banali presenti nello schema;
{codPiscina, codCorso} → {numeroCorsia}
{codCorso, giorno} → {ora}
{numeroAbbonamento} → {codPiscina, codCorso, codIscritto}
(b) si determinino le chiavi delle relazioni CORSO e ABBONAMENTO;
La chiave di CORSO è (codPiscina, codCorso, giorno)
La chiave di ABBONAMENTO è (numeroAbbonamento)
(c) se lo schema non risulta normalizzato, si determini uno schema in terza forma normale
che risulti equivalente, dal punto di vista informativo, allo schema dato.
PISCINA (codPiscina, indirizzo, città)
CORSIE (codPiscina, numeroCorsia)
CORSO (codPiscina, codCorso, nomeCorso, numeroCorsia)
LEZIONE (codPiscina, codCorso, giorno, ora)
ISCRITTO (codIscritto, nome, indirizzo, telefono)
ABBONAMENTO (codIscritto, numeroAbbonamento, codPiscina, codCorso)
24/03/2010
Esercizio 1
Si vuole realizzare un sistema informativo per la gestione di una stazione radio.
Nella stazione radio lavorano conduttori, tecnici, e personale amministrativo, per ciascuno dei
quali si vuol tenere traccia del codice fiscale e dei dati anagrafici. Per il personale
amministrativo si vuole inoltre memorizzare la qualifica, e per i tecnici la specializzazione.
Il palinsesto della radio è costituito da un insieme di trasmissioni per ciascuna delle quali si
memorizzano il titolo, i conduttori e i tecnici ad essa assegnati (al più due conduttori e tre
tecnici).
Per ciascuna trasmissione si memorizzano i giorni della settimana e gli orari in cui viene
trasmessa; la stessa trasmissione non va mai in onda due volte nello stesso giorno.
In ogni trasmissione vengono trasmessi giornalmente dei brani musicali, per i quali si vuole
tenere traccia del titolo, del cantante (o gruppo musicale), dell’anno di pubblicazione e della
durata. Lo stesso brano non può essere trasmesso due volte all’interno della stessa
trasmissione giornaliera. Durante la giornata, la radio prevede degli spazi per la trasmissione
di spot pubblicitari. Per ciascuno spot si memorizzano un titolo, una durata, un file audio
contenente la registrazione, i dati dell’azienda richiedente (partita iva, ragione sociale,
indirizzo, telefono), un costo, il periodo in cui esso deve essere trasmesso e gli orari
giornalieri di trasmissione.
(a) Fornire un diagramma ER (o EER) per l’applicazione.
(b) Fornire uno schema relazionale equivalente al diagramma concettuale sviluppato al
punto (a).
Dipendenti (CF, Nome, Cognome, Residenza, Tipo, Qualifica, Specializzazione)
Trasmissione (Titolo)
Conduce (CF, Titolo)
Assiste (CF, Titolo)
Programmazione (DataOra, Durata, Tipo)
Brani (Titolo, Artista, Anno, Durata)
TrasmetteMusica (Programmazione, Titolo, Artista)
Organizza (Programmazione, Titolo)
Spot (TitoloSpot, Durata, Audio, Costo, Periodo, Azienda)
OrariSpot (TitoloSpot, Orario)
TrasmissioneSpot (TitoloSpot, Programmazione)
Azienda (PartitaIVA, RagioneSociale, Indirizzo, Telefono)
(c) Scrivere il codice SQL necessario a creare lo schema relazionale
CREATE TABLE Dipendenti(
CF CHAR(16) NOT NULL,
Nome VARCHAR(30),
Cognome VARCHAR(30),
Residenza VARCHAR(255),
Tipo VARCHAR(20),
Qualifica VARCHAR(255),
Specializzazione VARCHAR(255),
PRIMARY KEY (CF));
CREATE TABLE Trasmissione(
Titolo VARCHAR(30) NOT NULL,
PRIMARY KEY (Titolo));
CREATE TABLE Conduce(
CF CHAR(16) NOT NULL,
Titolo VARCHAR(30) NOT NULL,
FOREIGN KEY (CF) REFERENCES Dipendenti(CF),
FOREIGN KEY (Titolo) REFERENCES Trasmissione(Titolo),
PRIMARY KEY (CF, Titolo));
CREATE TABLE Assiste(
CF CHAR(16) NOT NULL,
Titolo VARCHAR(30) NOT NULL,
FOREIGN KEY (CF) REFERENCES Dipendenti(CF),
FOREIGN KEY (Titolo) REFERENCES Trasmissione(Titolo),
PRIMARY KEY (CF, Titolo));
CREATE TABLE Programmazione(
DataOra DATETIME NOT NULL,
Durata INT(3) NOT NULL,
Tipo VARCHAR(20), NOT NULL,
PRIMARY KEY (DataOra));
CREATE TABLE Brani(
Titolo VARCHAR(30) NOT NULL,
Artista VARCHAR(30) NOT NULL,
Anno INT(4),
Durata TIME,
PRIMARY KEY (Titolo, Artista));
CREATE TABLE TrasmetteMusica(
Programmazione DATETIME NOT NULL,
Titolo VARCHAR(30) NOT NULL,
Artista VARCHAR(30) NOT NULL,
FOREIGN KEY (Programmazione) REFERENCES Programmazione(DataOra),
FOREIGN KEY (Titolo) REFERENCES Brani(Titolo),
FOREIGN KEY (Artista) REFERENCES Brani(Artista),
PRIMARY KEY (Programmazione, Titolo, Artista));
CREATE TABLE Organizza(
Programmazione DATETIME NOT NULL,
Titolo VARCHAR(30) NOT NULL,
FOREIGN KEY (Programmazione) REFERENCES Programmazione(DataOra),
FOREIGN KEY (Titolo) REFERENCES Trasmissione(Titolo),
PRIMARY KEY (Programmazione, Titolo));
CREATE TABLE Azienda(
PartitaIVA VARCHAR(30) NOT NULL,
RagioneSociale VARCHAR(255),
Indirizzo VARCHAR(255),
Telefono VARCHAR(20),
PRIMARY KEY (PartitaIVA));
CREATE TABLE Spot(
TitoloSpot VARCHAR(30) NOT NULL,
Durata TIME,
Audio VARCHAR(255),
Costo FLOAT(5,2),
Periodo VARCHAR(255),
Azienda VARCHAR(30) NOT NULL,
FOREIGN KEY (Azienda) REFERENCES Azienda(PartitaIVA),
PRIMARY KEY (TitoloSpot));
CREATE TABLE OrariSpot(
TitoloSpot VARCHAR(30) NOT NULL,
Orario TIME NOT NULL,
FOREIGN KEY (TitoloSpot) REFERENCES Spot(TitoloSpot),
PRIMARY KEY (TitoloSpot, Orario));
CREATE TABLE TrasmissioneSpot(
TitoloSpot VARCHAR(30) NOT NULL,
Programmazione DATETIME NOT NULL,
FOREIGN KEY (TitoloSpot) REFERENCES Spot(TitoloSpot),
FOREIGN KEY (Programmazione) REFERENCES Programmazione(DataOra),
PRIMARY KEY (Programmazione, TitoloSpot));
(d) Specificare le politiche di gestione delle violazioni per ogni vincolo di integrità
referenziale presente nello schema relazionale sviluppato al punto (b). Giustificare le
scelte effettuate.
Specifichiamo le politiche di gestione delle violazioni per ogni vincolo di
integrità referenziale presente nello schema relazionale:
1. Conduce(CF) ⊆ Dipendenti(CF)
2. Conduce(Titolo) ⊆ Trasmissione(Titolo)
3. Assiste(CF) ⊆ Dipendenti(CF)
4. Assiste(Titolo) ⊆ Trasmissione(Titolo)
5. TrasmetteMusica(Programmazione) ⊆ Programmazione(DataOra)
6. TrasmetteMusica(Titolo) ⊆ Brani(Titolo)
7. TrasmetteMusica(Artista) ⊆ Brani(Artista)
8. Organizza(Programmazione) ⊆ Programmazione(DataOra)
9. Organizza(Titolo) ⊆ Trasmissione(Titolo)
10.Spot(Azienda) ⊆ Azienda(PartitaIVA)
11.OrariSpot(TitoloSpot) ⊆ Spot(TitoloSpot)
12.TrasmissioneSpot(TitoloSpot) ⊆ Spot(TitoloSpot)
13.TrasmissioneSpot(Programmazione) ⊆ Programmazione(DataOra)
Per tutte le relazioni tranne 10 e 11, si tratta di relazioni del tipo “molti a
molti” e si può avere violazione dei vincoli in caso di inserimento o modifica
nelle entità referenti (chiave esterna), e in tal caso l'operazione viene annullata
per non creare inconsistenza con le entità referenziate, mentre la
cancellazione non dà problemi; in caso di entità referenziate si ha:
•
Inserimento – l'operazione viene eseguita in quanto non esistono valori in
conflitto nell'entità referente;
•
Cancellazione – viene effettuata una cancellazione in cascata;
•
Modifica – viene fatta una modifica in cascata.
Le relazioni 10 e 11 sono del tipo “molti a 1” e anche in questo caso si può
avere violazione dei vincoli in caso di inserimento o modifica nelle entità
referenti (chiave esterna), e in tal caso l'operazione viene annullata per non
creare inconsistenza con le entità referenziate, mentre la cancellazione non dà
problemi. Analizziamo gl altri casi:
◦ Inserimento Azienda e Spot – l'operazione viene eseguita in quanto non
esistono valori in conflitto nell'entità referente;
◦ Cancellazione Azienda – il valore di Spot(Azienda) viene settato a Null
oppure avviene una cancellazione in cascata;
◦ Cancellazione Spot – viene effettuata una cancellazione in cascata;
◦ Modifica Azienda e Spot – viene fatta una modifica in cascata.
Esercizio 2
Dato il seguente schema relazionale:
ATTORE (codAttore, cognome, nome, dataNascita)
SPETTACOLO (codSpettacolo, nomeSpettacolo, descrizione, durata, genere, regista, anno)
PARTECIPAZIONE (codAttore: ATTORE, codSpettacolo: SPETTACOLO, ruolo)
(a) Scrivere un’espressione di algebra relazionale ottimizzata e una query SQL che
visualizzi gli spettacoli di genere ‘Commedia’ ai quali ha partecipato l’attore Verdi
Giovanni (codSpettacolo, nomeSpettacolo )
•
FILM_VERDI ←σ codSpettacolo (PARTECIPAZIONE∗(σcognome = 'Verdi' AND nome = 'Giovanni' (ATTORE)))
π codSpettacolo, nomeSpettacolo (FILM_VERDI∗(σgenere = 'Commedia' (SPETTACOLO )))
•
SELECT codSpettacolo, nomeSpettacolo
FROM SPETTACOLO
WHERE codSpettacolo IN (SELECT codSpettacolo
FROM ATTORE NATURAL JOIN PARTECIPAZIONE
WHERE cognome = 'Verdi'
AND nome = 'Giovanni')
AND genere = 'Commedia'
(b) Scrivere un’espressione di algebra relazionale e una query SQL che visualizzi, per
ciascun genere, la durata media degli spettacoli (genere, durataMedia)
•
ρMEDIA(genere, durataMedia) ( genere f AVG(durata) (SPETTACOLO))
•
SELECT genere, AVG(durata) AS durataMedia
FROM SPETTACOLO
GROUP BY (genere)
(c) Scrivere un’espressione di algebra relazionale e una query SQL che visualizzi il
numero di attori che hanno partecipato a più di 5 spettacoli (numAttori)
•
ρTOT_PARTECIPAZIONI(codAttore, totale) ( codAttore f COUNT(codSpettacolo) (PARTECIPAZIONE ))
f COUNT(codAttore) (σ totale > 5 (TOT_PARTECIPAZIONI))
•
SELECT COUNT(*)
FROM (SELECT COUNT(*)
FROM PARTECIPAZIONE
GROUP BY codAttore
HAVING COUNT(*) > 5)
Esercizio 3
È dato il seguente schema relazionale:
CONCERTO (codConcerto, titolo, descrizione, data, prezzo, codPezzo, titoloPezzo,
posizioneScaletta,
autore,
nomeOrchestra,
direttoreOrchestra,
matricolaOrchestrale,
nominativoOrchestrale, strumento);
Sapendo che:
• Un concerto prevede l’esecuzione di un insieme di pezzi dei quali deve essere
memorizzato l’ordine di esecuzione;
• Un pezzo può essere ripetuto in concerti diversi;
• Ogni concerto è eseguito da un’orchestra, identificata da un nome univoco e composta
da diversi orchestrali;
• Un orchestrale può suonare in più orchestre, ma suona sempre lo stesso strumento;
(a) si evidenzino tutte le dipendenze funzionali non banali presenti nello schema
codConcerto, codPezzo → posizioneScaletta
codConcerto → nomeOrchestra
matricolaOrchestrale → strumento
codConcerto → titolo, descrizione, data, prezzo
codPezzo → titoloPezzo, autore
nomeOrchestra → direttoreOrchestra
matricolaOrchestrale → nominativoOrchestrale
(b) si determini la chiave della relazione CONCERTO
La chiave della relazione
matricolaOrchestrale)
CONCERTO
è
(codConcerto,
codPezzo,
(c) se lo schema non risulta normalizzato, si determini uno schema in terza forma normale
che risulti equivalente, dal punto di vista informativo, allo schema dato
CONCERTO (codConcerto, titolo, descrizione, data, prezzo, nomeOrchestra)
PEZZO_CONCERTO (codPezzo, codConcerto, posizioneScaletta)
PEZZO (codPezzo, titoloPezzo, autore)
ORCHESTRA (nomeOrchestra, direttoreOrchestra)
ORCHESTRALE (matricolaOrchestrale, nominativoOrchestrale, strumento)
ORCHESTRALE_ORCHESTRA (matricolaOrchestrale, nomeOrchestra)
04/02/2011 primo parziale
Esercizio 1
Siete stati incaricati di progettare un’applicazione basata su database per la gestione della
Camera dei Deputati di una certa nazione, con i seguenti requisiti minimi:
• Di ciascun deputato interessa il nome, il partito politico a cui appartiene, il collegio in
cui è stato eletto e la regione di appartenenza del collegio.
• Per ciascuna regione interessa il suo nome (ad es. Toscana, Catalogna, Baviera) e la
parte della nazione in cui si trova (ad es. Nord, Centro, Sud, Isole, Colonie).
• La base di dati inoltre memorizza ogni progetto di legge con il codice (un valore intero
progressivo), il nome e i deputati proponenti.
• Se il progetto di legge è stato votato, si memorizza anche la data in cui è stato votato e
l’esito della votazione (Si oppure No).
• La base dati tiene inoltre traccia di come ogni deputato ha votato su ciascun progetto
di legge (Si, No, Astenuto).
(a) Fornire uno schema ER per il sistema informativo richiesto
(b) specificare, nelle due notazioni studiate, i rapporti di cardinalità ed i vincoli di
partecipazione delle associazioni
(c) derivare uno schema relazionale dal diagramma ER ottenuto al punto (a)
Progetti (Codice, Nome, DataVoto, Esito)
Deputati (NomeDeputato, NomeRegione, Partito, Collegio)
Regione (NomeRegione, Area)
PropostaLegge (NomeDeputato, Codice)
VotoLegge (NomeDeputato, Codice, Voto)
Esercizio 2
Dato il seguente schema relazionale:
VIAGGIO (codViaggio, nome, destinazione, numeroGiorni, descrizione, tipoViaggio)
PARTENZA (codViaggio:VIAGGIO, dataPartenza, prezzo)
TAPPA (codViaggio:VIAGGIO, idTappa, kmPercorsi, cittàPartenza, cittàArrivo)
Scrivere le interrogazioni in algebra relazionale per:
(a) visualizzare i viaggi di tipo “Natura” (codViaggio, nome, destinazione, numeroGiorni)
costituiti esclusivamente da tappe di lunghezza inferiore a 300 km
VIAGGI_LUNGHI ← πcodViaggio ( σ kmPercorsi≥300 (TAPPA))
VIAGGI_CORTI ← πcodViaggio ( TAPPA)−VIAGGI_LUNGHI
NATURA_CORTI ←π codViaggio, nome, destinazione, numeroGiorni (σ tipoViaggio = 'Natura' (VIAGGI_CORTI∗VIAGGIO))
(b) visualizzare, per ogni tipo di viaggio, il viaggio (tipoViaggio, codViaggio, nome,
destinazione) che ha durata minima
ρMIN_VIAGGI(tipo, minGiorni) ( tipoViaggio f MIN (numeroGiorni) (VIAGGIO))
π tipoViaggio, codViaggio, nome, destinazione ( VIAGGIO▷◁tipoViaggio = tipo, minGiorni = numeroGiorni MIN_VIAGGI)
(c) visualizzare il numero di viaggi che fanno tappa nella città di Toronto (numViaggi)
ρ(numViaggi) ( f COUNT(codViaggio) (πcodViaggio (σ cittàPartenza = 'Toronto' OR cittàArrivo = 'Toronto' (TAPPA))))
La proiezione su codViaggio permette di eliminare i valori duplicati, in quanto
un viaggio che fa tappa intermedia a Toronto verrebeb contato due volte, una
volta per l'arrivo e una per la partenza.
(d) visualizzare, per ogni viaggio, il totale dei km percorsi nelle relative tappe (codViaggio,
totKm)
ρcodViaggio, totKm ( codViaggio f SUM(kmPercorsi) (TAPPA ))
Esercizio 3
Si consideri il seguente schema di base di dati:
PERSONA (CF , Nome, Cognome, DataNascita, LuogoNascita, Sesso)
MATRIMONIO (Moglie:PERSONA, Marito:PERSONA, DataMatrimonio, LuogoMatrimonio)
Ricavare il diagramma ER corrispondente allo schema relazionale:
Scrivere le interrogazioni in algebra relazionale per:
(a) Trovare nome e cognome delle donne che si sono sposate con almeno due uomini
diversi
ρMogli(CF, Matrimoni) ( Moglie f COUNT(Marito) (π Moglie, Marito(MATRIMONIO)))
π Nome, Cognome (σ Matrimoni > 1 (Mogli∗PERSONA))
(b) Trovare nome e cognome delle persone che sono nate a Udine e si sono sposate a
Roma prima del 2000
UD ←σ LuogoNascita = 'Udine' ( PERSONA)
MATR_RM ← σ LuogoMatrimonio = 'Roma' AND DataMatrimonio < 2000 (MATRIMONIO)
π Nome, Cognome ((UD ▷◁CF = Moglie MATR_RM )∪(UD▷◁CF = Marito MATR_RM))
(c) Trovare nome e cognome degli uomini non sposati
MARITI ← PERSONA▷◁CF = Marito (πMarito (MATRIMONIO))
(σSesso = 'M' ( PERSONA))−MARITI
(d) Trovare nome e cognome delle persone che si sono sposate entro i 20 anni di età
MARITI ← PERSONA▷◁CF = Marito AND DataMatrimonio - DataNascita < 20 (MATRIMONIO)
MOGLI ← PERSONA▷◁CF = Moglie AND DataMatrimonio - DataNascita < 20 (MATRIMONIO)
π Nome, Cognome (MARITI∪MOGLI)
22/03/2011
Esercizio 1
a) Si discuta il progetto della porzione di schema ER riportato in figura, motivando le
scelte effettuate.
Osservando il diagramma ER, si possono notare alcune interessanti scelte di
progettazione. Ad esempio, il fatto di specializzare la classe VIAGGIO in due
sottoclassi NATURA e AVVENTURA, non disgiunte (un viaggio avventuroso
può essere anche un viaggio che riguarda la natura). Ogni viaggio si presenta
inoltre con un attributo data di partenza, che può assumere un valore minimo
di 1 (1 singola data) oppure n (nel caso si abbiano più date di partenza).
b) Si fornisca un adeguato Schema Relazionale
VIAGGIO(codViaggio, nome, tipoViaggio, codDestinazione)
DATA_PARTENZA(codViaggio, dataPartenza)
DESTINAZIONE(codDest, nome)
TARIFFA(codViaggio, codPeriodo, prezzoSingola, prezzoDoppia)
PERIODO(codPeriodo, descrizione, dataInizio, dataFine)
c) Si scriva il codice SQL necessario a realizzare lo schema relazionale individuato al
punto (b)
CREATE TABLE Destinazione(
codDest VARCHAR(50) NOT NULL,
nome VARCHAR(255),
PRIMARY KEY (codDest));
CREATE TABLE Viaggio(
codViaggio VARCHAR(50) NOT NULL,
nome VARCHAR(50),
tipoViaggio VARCHAR(10),
codDestinazione VARCHAR(50),
PRIMARY KEY (codViaggio),
FOREIGN KEY (codDestinazione) REFERENCES Destinazione(codDest));
CREATE TABLE DataPartenza(
codViaggio VARCHAR(50) NOT NULL,
dataPartenza DATETIME NOT NULL,
FOREIGN KEY (codViaggio) REFERENCES Viaggio(codViaggio),
PRIMARY KEY (codViaggio, dataPartenza));
CREATE TABLE Periodo(
codPeriodo VARCHAR(50) NOT NULL,
descrizione VARCHAR(255),
dataInizio DATETIME,
dataFine DATETIME,
PRIMARY KEY (codPeriodo));
CREATE TABLE Tariffa(
codViaggio VARCHAR(50) NOT NULL,
codPeriodo VARCHAR(50) NOT NULL,
prezzoSingola FLOAT(5,2),
prezzoDoppia FLOAT(5,2),
FOREIGN KEY (codViaggio) REFERENCES Viaggio(codViaggio),
FOREIGN KEY (codPeriodo) REFERENCES Periodo(codPeriodo),
PRIMARY KEY (codViaggio, codPeriodo));
d) Si scriva il codice SQL necessario a trovare i viaggi di tipo ‘avventura’ con destinazione
‘Kenya’ con tariffa minima in giugno, luglio e agosto (del 2011)
SELECT *
FROM Viaggio
WHERE tipoViaggio = 'AVVENTURA'
AND WHERE codDestinazione IN (
SELECT codDest
IN Destinazione
WHERE nome = 'Kenya')
AND WHERE codViaggio IN (
SELECT codViaggio FROM(
# Trovo per ogni mese il viaggio con tariffa minima,
# ottengo una tabella (codViaggio, minPrezzi)
# della quale voglio solo i valori di codViaggio
SELECT codViaggio, MIN(prezzoSingola)
FROM Tariffa NATURAL JOIN Periodo
BETWEEN '2011-06-01' AND '2011-06-30'
UNION
SELECT codViaggio, MIN(prezzoSingola)
FROM Tariffa NATURAL JOIN Periodo
BETWEEN '2011-07-01' AND '2011-07-31'
UNION
SELECT codViaggio, MIN(prezzoSingola)
FROM Tariffa NATURAL JOIN Periodo
BETWEEN '2011-08-01' AND '2011-08-31'
))
Esercizio 2
Dato il seguente schema relazionale:
RICETTA (nomeRicetta, descrizione, tempoPreparazione, porzioni, istruzioni, tipoRicetta )
INGREDIENTE (nomeIngrediente, prezzoUnitario, calorie )
COMPOSIZIONE (nomeRicetta: RICETTA, nomeIngrediente: INGREDIENTE, quantità )
a) Si scriva l’espressione dell’algebra relazionale e la query SQL che visualizza gli
ingredienti presenti in almeno una ricetta di tipo “Primo piatto” (nomeIngrediente,
prezzoUnitario).
•
•
PRIMI_PIATTI ← π nomeRicetta (σ tipoRicetta = 'Primo piatto' (RICETTA))
INGREDIENTI ← π nomeIngrediente, prezzoUnitario
((PRIMI_PIATTI∗COMPOSIZIONE)∗INGREDIENTE)
SELECT nomeIngrediente, prezzoUnitario
FROM (RICETTA NATURAL JOIN COMPOSIZIONE) NATURAL JOIN INGREDIENTE
WHERE tipoRicetta = “Primo piatto”
b) Scrivere l’espressione dell’algebra relazionale e la query SQL che visualizza le ricette
di tipo “Dessert” che non contengono l’ingrediente “Burro” (nomeRicetta, descrizione,
tempoPreparazione)
•
•
DESSERT ← π nomeRicetta, descrizione, tipoPreparazione (σ tipoRicetta = 'Dessert' (RICETTA))
DESSERT_CON_BURRO ← π nomeRicetta, descrizione, tipoPreparazione
(σ nomeIngrediente = 'Burro' (DESSERT∗COMPOSIZIONE))
DESSERT_SENZA_BURRO ← DESSERT−DESSERT_CON_BURRO
SELECT nomeRicetta, descrizione, tempoPreparazione
FROM RICETTA
WHERE NOT EXISTS (SELECT *
FROM COMPOSIZIONE
WHERE COMPOSIZIONE.nomeRicetta = RICETTA.nomeRicetta
AND nomeIngrediente = 'burro')
c) Scrivere l’espressione dell’algebra relazionale e la query SQL che visualizza, per
ciascuna ricetta, il numero di ingredienti necessari per la sua preparazione
(nomeRicetta, descrizione, tipoRicetta, numeroIngredienti).
•
•
TEMP ← nomeRicetta f COUNT nomeIngrediente (RICETTA∗COMPOSIZIONE)
ρ TOT_INGREDIENTI(nomeRicetta, descrizione, tipoRicetta, numeroIngredienti) (TEMP∗RICETTA)
SELECT nomeRicetta, descrizione, tipoRicetta, COUNT(*) AS
numeroIngredienti
FROM RICETTA NATURAL JOIN COMPOSIZIONE
GROUP BY(nomeRicetta, descrizione, tipoRicetta)
d) Scrivere l’espressione dell’algebra relazionale e la query SQL che visualizza, per
ciascun tipo di ricetta, la ricetta con tempo di preparazione minore (tipoRicetta,
nomeRicetta, descrizione, tempoPreparazione)
•
•
TEMP ← tipoRicetta f MIN tempoPreparazione (RICETTA )
π tipoRicetta, nomeRicetta, descrizione, tempoPreparazione (TEMP∗RICETTA )
SELECT tipoRicetta, nomeRicetta, descrizione, MIN(tempoPreparazione) AS
tempoMin
FROM RICETTA
GROUP BY (tipoRicetta, nomeRicetta, descrizione)
Esercizio 3
È dato il seguente schema relazionale:
LAVANDERIA
(IdCliente,
nomeCliente,
recapitoCliente,
idOrdine,
dataOrdine,
dataRestituzione, costoTotale, codiceCapo, nomeServizio, tipoCapo, prezzoServizio) Sapendo
che:
• Per ciascun ordine, identificato da un codice univoco, si memorizzano il cliente, la data
dell’ordine, la data prevista per la restituzione dei capi e il costo totale (ottenuto
sommando il prezzo dei servizi richiesti per i capi consegnati).
• Un ordine si riferisce a uno o più capi (gonna, maglia, etc...) ciascuno identificato da un
codice univoco all’interno dell’ordine. Per ciascun capo vengono richiesti uno o più
servizi (lavaggio,
stiratura, etc...).
• Il prezzo dei servizi dipende dal tipo di capo e dal servizio (es. stiratura di una gonna →
5€). I prezzi sono riportati in un tariffario.
(a) Si evidenzino tutte le dipendenze funzionali non banali presenti nello schema e si
indichi almeno una chiave candidata per la relazione LAVANDERIA:
{IdOrdine} → {IdCliente, dataOrdine, dataRestituzione, costoTotale}
{IdCliente} → {nomeCliente, recapitoCliente}
{IdOrdine, codiceCapo} → {tipoCapo}
{tipoCapo, nomeServizio} → {prezzoServizio}
Una chiave candidata per la relazione LAVANDERIA è {IdOrdine, codiceCapo,
nomeServizio} in quanto la sua chiusura coincide con lo schema relazionale
LAVANDERIA.
(b) Se lo schema non risulta normalizzato, si determini uno schema in terza forma normale
che risulti equivalente, dal punto di vista informativo, allo schema dato. Giustificare le
risposte.
DETTAGLI_ORDINE_SERVIZIO (IdOrdine, codiceCapo, nomeServizio)
CLIENTE (IdCliente, nomeCliente, recapitoCliente)
ORDINE (IdOrdine, IdCliente, dataOrdine, dataRestituzione, costoTotale)
DETTAGLI_CAPO (IdOrdine, codiceCapo, tipoCapo)
TARIFFARIO (nomeServizio, tipoCapo, prezzoServizio)
15/02/2012
Esercizio 1
Un gestore di telefonia mobile vuole realizzare il sistema informativo per la gestione dei suoi
clienti, dei piani tariffari e del traffico telefonico. I piani tariffari sono di tipo abbonamento o
carta prepagata, hanno un nome, un codice, una data di inizio validità e, se non più attivi, una
data di fine. Per ogni piano si memorizzano una descrizione e i costi del piano oltre alla lista
delle opzioni attivabili. Ogni opzione ha una descrizione, un costo di attivazione, una lista di
piani per i quali è ammissibile e un periodo di validità (anche infinito). Di tutti i clienti sono
mantenuti i dati anagrafici (CF, nome, cognome). Un cliente può possedere anche più di una
“carta”. Ciascuna carta è identificata da un numero telefonico, ha un solo proprietario, un
piano telefonico attuale ed eventualmente delle opzioni, un credito residuo (se si tratta di una
ricaricabile) o la spesa non ancora fatturata (se si tratta di abbonamento) e il totale del
credito consumato (utile per selezionare i clienti più assidui). Infine sono registrate tutte le
telefonate in cui il chiamante è un numero della compagnia: per ciascuna chiamata vengono
registrati la durata, il costo e il numero di telefono del destinatario. Considerando le
specifiche sopra riportate:
a) Disegnare lo schema ER (o EER), evidenziando le entità, le associazioni e gli attributi
di maggior rilievo;
b) indicare i vincoli di partecipazione, usando la notazione (min, max) per tutte le
associazioni presenti nello schema. Si evidenzino inoltre eventuali vincoli inespressi e
attributi derivati;
c) formulare le query SQL necessarie a implementare lo schema proposto con un DBMS
relazionale.
CREATE TABLE Clienti(
CF CHAR(16) NOT NULL,
Nome VARCHAR(20) NOT NULL,
Cognome VARCHAR(20) NOT NULL,
PRIMARY KEY (CF));
CREATE TABLE PianiTariffari(
Codice VARCHAR(50) NOT NULL,
Nome VARCHAR(50),
DataInizioValidita DATETIME,
DataFineValidita DATETIME,
Costi VARCHAR(255),
Descrizione VARCHAR(255),
Tipo VARCHAR(20),
PRIMARY KEY (Codice),
CHECK (Tipo='Abbonamento' OR Tipo='Carta prepagata'));
CREATE TABLE Sim(
NumeroTelefonico VARCHAR(50) NOT NULL,
PianoTariffario VARCHAR(50) NOT NULL,
Cliente CHAR(16) NOT NULL,
Credito FLOAT(4,2) DEFAULT 0,
TotCredito FLOAT(10,2),
PRIMARY KEY (NumeroTelefonico),
FOREIGN KEY (PianoTariffario) REFERENCES PianiTariffari(Codice),
FOREIGN KEY (Cliente) REFERENCES Clienti(CF));
CREATE TABLE Chiamate(
DataOra DATETIME NOT NULL,
Chiamante VARCHAR(50) NOT NULL,
Durata TIME NOT NULL,
Costo FLOAT(10,2),
Destinatario VARCHAR(15) NOT NULL,
PRIMARY KEY (DataOra),
FOREIGN KEY (Chiamante) REFERENCES Sim(Numerotelefonico));
CREATE TABLE Opzioni(
Nome VARCHAR(50) NOT NULL,
Descrizione VARCHAR(255),
CostoAttivazione FLOAT(4,2) NOT NULL,
PeriodoValidita DATETIME,
PRIMARY KEY (Nome));
CREATE TABLE OpzioniSim(
Nome VARCHAR(50) NOT NULL,
NumeroTelefonico VARCHAR(50) NOT NULL,
FOREIGN KEY (Nome) REFERENCES Opzioni(Nome),
FOREIGN KEY (NumeroTelefonico) REFERENCES Sim(Numerotelefonico),
PRIMARY KEY (Nome, NumeroTelefonico));
CREATE TABLE OpzioniPiani(
Nome VARCHAR(50) NOT NULL,
Codice VARCHAR(50) NOT NULL,
FOREIGN KEY (Nome) REFERENCES Opzioni(Nome),
FOREIGN KEY (Codice) REFERENCES PianiTariffari(Codice),
PRIMARY KEY (Nome, Codice));
Esercizio 2
Dato il seguente schema relazionale relativo a un circolo nautico:
SOCIO (tesserino, nome, indirizzo, città, telefono, dataIscrizione)
IMBARCAZIONE (matricola, marca, modello, nome, dataVaro, larghezza, lunghezza)
SOCIOIMBARCAZIONE (tesserino: SOCIO, matricola: IMBARCAZIONE, percentualeProprietà)
CORSO(codCorso, nome, descrizione, dataInizio, dataFine)
ISCRIZIONE (tesserino: SOCIO, codCorso: CORSO)
a) Scrivere l’interrogazione in algebra relazionale e la query SQL che seleziona i corsi
(codCorso, nome) ai quali si è iscritto almeno un socio di Rimini.
•
•
SOCI_RIMINI ← π tesserino (σ città = 'Rimini' (SOCIO))
π codCorso, nome ((SOCI_RIMINI∗ISCRIZIONE)∗CORSO )
SELECT codCorso, CORSO.nome
FROM (CORSO NATURAL JOIN ISCRIZIONE) NATURAL JOIN SOCIO
WHERE città = Rimini
b) Scrivere l’interrogazione in algebra relazionale e la query SQL che seleziona i corsi
(codCorso, nome, descrizione) che non hanno ancora nessun iscritto.
•
•
CORSI_CON_ISCRITTI ← πcodCorso, nome, descrizione (CORSO∗ISCRIZIONE)
CORSI_SENZA_ISCRITTI ← π codCorso, nome, descrizione (CORSO)−CORSI_CON_ISCRITTI
SELECT codCorso, nome, descrizione
FROM CORSO
WHERE codCorso NOT IN (SELECT codCorso
FROM ISCRIZIONE)
c) Scrivere l’interrogazione in algebra relazionale e la query SQL che mostra, per ogni
socio, il numero di imbarcazioni possedute (tesserino, nome, numeroImbarcazioni).
•
•
π tesserino, nome, numeroImbarcazioni (( tesserino f COUNT matricola (SOCIOIMBARCAZIONE))∗SOCIO)
SELECT tesserino, nome, COUNT(*) AS numeroImbarcazioni
FROM SOCIOIMBARCAZIONE NATURAL JOIN SOCIO
GROUP BY (tesserino)
d) Scrivere l’interrogazione in algebra relazionale e la query SQL che seleziona il corso
(codCorso, nome, descrizione) con il numero maggiore di iscritti.
•
•
ρ CORSO_MAX_ISCRITTI(codCorso) ( codCorso f MAX numeroTesserini ( codCorso f COUNT tesserino (ISCRIZIONE)))
π codCorso, nome, descrizione (CORSO∗CORSO_MAX_ISCRITTI)
SELECT codCorso, nome, descrizione
FROM CORSO * ISCRIZIONE
HAVING COUNT(*) >= (SELECT COUNT(*)
FROM ISCRIZIONE
GROUP BY codCorso)
Esercizio 3
È dato il seguente schema relazionale:
PROMOZIONI_PIANTE
(codPianta,
descrizionePianta,
prezzoPianta,
codPromozione,
dataInizioPromozione, dataFinePromozione, quantità, sconto, codFornitore, nomeFornitore,
indirizzoFornitore).
Sapendo che:
• ciascuna promozione è identificata da un codice ed è caratterizzata da una data di
inizio e da una data di fine;
• ciascuna pianta ha un solo fornitore;
• l’attributo prezzoPianta rappresenta il prezzo al quale la pianta viene venduta al di
fuori delle promozioni;
• la percentuale di sconto applicata (attributo sconto) dipende dalla promozione, dalla
pianta e dalla quantità acquistata
a) Si individui la chiave della relazione;
La chiave della relazione è {codPianta, codPromozione, quantità}
b) Si evidenzino tutte le dipendenze funzionali non banali presenti nello schema
Le dipendenze funzionali sono:
{codPromozione} → {datainizioPromozione, dataFinePromozione}
{codPianta} → {codFornitore}
{codPromozione, codPianta, quantità} → {sconto}
{codPianta} → {descrizionePianta, prezzoPianta}
{codFornitore} → {nomeFornitore, indirizzoFornitore}
c) Se lo schema non risulta normalizzato, si determini un insieme di relazioni che siano in
prima, seconda e terza forma normale e risultino equivalenti, dal punto di vista
informativo, allo schema dato.
•
Nomralizzazione in prima forma normale
Nessun attributo multivalore, già in 1NF
•
Nomralizzazione in seconda forma normale
Avendo una chiave composta e degli attributi che hanno dipendenza
funzionale con una parte della chiave, definiamo le seguenti relazioni:
PIANTE (codPianta, descrizionePianta, prezzoPianta, codFornitore,
nomeFornitore, indirizzoFornitore)
PROMOZIONE (codPromozione, dataInizioPromozione,
dataFinePromozione)
SCONTO (codPromozione, codPianta, quantità, sconto)
•
Nomralizzazione in terza forma normale
Avendo nella relazione PIANTA degli attributi che hanno dipendenza
funzionale transitiva rispetto l'attributo codFornitore, definiamo le seguenti
relazioni:
PIANTA (codPianta, descrizionePianta, prezzoPianta, codFornitore)
FORNITORE (codFornitore, nomeFornitore, indirizzoFornitore)
PROMOZIONE (codPromozione, dataInizioPromozione,
dataFinePromozione)
SCONTO (codPromozione, codPianta, quantità, sconto)