Esercizio 8.7
Considero il database in figura:
Utilizzando la solita notazione:
PROPEDEUTICITÀ(Codice_insegnamento) ⊆ INSEGNAMENTO(Codice_insegnamento)
MODULO(Codice_insegnamento) ⊆ INSEGNAMENTO(Codice_insegnamento)
VOTAZIONE(Numero_studente) ⊆ STUDENTE(Numero_studente)
VOTAZIONE(Indicatore_modulo) ⊆ MODULO(Indicatore_modulo)
Per definire la base di dati, possiamo usare il seguente set di istruzioni DDL SQL:
CREATE TABLE STUDENTE (Nome VARCHAR(30) NOT NULL,
Numero_studente INTEGER NOT NULL,
Anno_corso CHAR NOT NULL,
Corso_laurea CHAR(3),
PRIMARY KEY (Numero_studente) );
CREATE TABLE INSEGNAMENTO (Nome_insegnamento VARCHAR(30) NOT NULL,
Codice_insegnamento CHAR(5) NOT NULL,
Ore INTEGER,
Dipartimento CHAR(4),
PRIMARY KEY (Codice_insegnamento),
UNIQUE (Nome_insegnamento) );
CREATE TABLE PROPEDEUTICITÀ(Codice_Insegnamento CHAR(5) NOT NULL,
Codice_propedeuticità CHAR(3) NOT NULL,
PRIMARY KEY (Codice_Insegnamento, Codice_propedeuticità),
FOREIGN KEY (Codice_Insegnamento) REFERENCES
INSEGNAMENTO (Codice_Insegnamento),
FOREIGN KEY (Codice_propedeuticità) REFERENCES
INSEGNAMENTO (Codice_Insegnamento) )
CREATE TABLE MODULO (Identificatore_modulo INTEGER NOT NULL,
Codice_Insegnamento CHAR(5) NOT NULL,
Semestre VARCHAR(4) NOT NULL,
Anno CHAR(4) NOT NULL,
Docente VARCHAR(15),
PRIMARY KEY (Identificatore_modulo),
FOREIGN KEY (Codice_Insegnamento) REFERENCES
INSEGNAMENTO(Codice_Insegnamento) );
1
CREATE TABLE VOTAZIONE (Numero_studente INTEGER NOT NULL,
Identificatore_modulo INTEGER NOT NULL,
Voto INTEGER,
PRIMARY KEY (Numero_studente, Identificatore_modulo),
FOREIGN KEY (Numero_studente) REFERENCES
STUDENTE (Numero_studente),
FOREIGN KEY (Identificatore_modulo) REFERENCES
MODULO (Identificatore_modulo) );
Esercizio 8.8
Ripetiamo l'esercizio precedente, ma con questo database
Vediamo i vincoli di integrità referenziale, come visti negli esercizi del capitolo 7.
PUÓ_ATTERRARE(Codice_aeroporto) ⊆ AEREOPORTO(Codice_aeroporto)
PUÓ_ATTERRARE(Nome_tipo_aeroplano) ⊆ TIPO_AEROPLANO(Nome_tipo)
TRATTA_DI_VOLO(Codice_aeroporto_partenza) ⊆ AEREOPORTO(Codice_aeroporto)
TRATTA_DI_VOLO(Codice_aeroporto_arrivo) ⊆ AEREOPORTO(Codice_aeroporto)
TRATTA_DI_VOLO(Numero_volo) ⊆ VOLO(Numero)
TARIFFE(Numero_volo) ⊆ VOLO(Numero)
2
ISTANZA_DI_TRATTA(Numero_volo, Numero_tratta) ⊆ TRATTA_DI_VOLO(Numero_volo, Num_tratta)
ISTANZA_DI_TRATTA(Codice_aeroporto_partenza) ⊆ AEREOPORTO(Codice_aeroporto)
ISTANZA_DI_TRATTA(Codice_aeroporto_arrivo) ⊆ AEREOPORTO(Codice_aeroporto)
ISTANZA_DI_TRATTA(ID_Aeroplano) ⊆ AEROPLANO(ID_aeroplano)
AEROPLANO(Tipo_aeroplano) ⊆ TIPO_AEROPLANO(Nome_tipo)
PRENOTAZIONE_POSTI(Numero_volo, Numero_tratta, Data) ⊆ ISTANZA_DI_TRATTA(Numero_volo, Numero_tratta,
Data)
Per definire la base di dati, possiamo usare il seguente set di istruzioni DDL SQL:
CREATE TABLE AEROPORTO (Codice_aeroporto CHAR(3) NOT NULL,
Nome VARCHAR(30) NOT NULL,
Città VARCHAR(20) NOT NULL,
Stato VARCHAR(20),
PRIMARY KEY (Codice_aeroporto) );
CREATE TABLE VOLO (Numero VARCHAR(6) NOT NULL,
Compagnia_aerea VARCHAR(15) NOT NULL,
Giorni_settimana VARCHAR(10) NOT NULL,
PRIMARY KEY (Numero) );
CREATE TABLE TRATTA_VOLO (Numero_volo VARCHAR(6) NOT NULL,
Numero_tratta INTEGER NOT NULL,
Codice_aeroporto_partenza CHAR(3) NOT NULL,
Orario_previsto_partenza TIMESTAMP WITH TIME ZONE,
Codice_aeroporto_arrivo CHAR(3) NOT NULL,
Orario_previsto_arrivo TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (Numero_volo, Numero_tratta),
FOREIGN KEY (Numero_volo) REFERENCES VOLO(Numero),
FOREIGN KEY (Codice_aeroporto_partenza) REFERENCES
AEROPORTO (Codice_aeroporto),
FOREIGN KEY (Orario_previsto_arrivo) REFERENCES
AEROPORTO (Codice_aeroporto) );
CREATE TABLE ISTANZA_DI_TRATTA (Numero_volo VARCHAR(6) NOT NULL,
Numero_tratta INTEGER NOT NULL,
Data DATE NOT NULL,
Numero_di_posti_disponibili INTEGER,
Id_Aeroplano INTEGER,
Codice_aeroporto_partenza CHAR(3),
Orario_partenza TIMESTAMP WITH TIME ZONE,
Codice_aeroporto_arrivo CHAR(3),
Orario_arrivo TIMESTAMP WITH TIME ZONE,
PRIMARY KEY (Numero_volo, Numero_tratta, Data),
FOREIGN KEY (Numero_volo, Numero_tratta) REFERENCES
TRATTA_VOLO (Numero_volo, Numero_tratta),
FOREIGN KEY (Id_aeroplano) REFERENCES
AEROPLANO (Id_aeroplano),
FOREIGN KEY (Codice_aeroporto_partenza) REFERENCES
AEROPORTO (Codice_aeroporto),
FOREIGN KEY (Codice_aeroporto_arrivo) REFERENCES
AEROPORTO (Codice_aeroporto) );
CREATE TABLE TARIFFA (Numero_volo VARCHAR(6) NOT NULL,
Codice_tariffa VARCHAR(10) NOT NULL,
Importo DECIMAL(8,2) NOT NULL,
Limitazioni VARCHAR(200),
PRIMARY KEY (Numero_volo, Codice_tariffa),
FOREIGN KEY (Numero_volo) REFERENCES VOLO(Numero) );
3
CREATE TABLE TIPO_AEROPLANO (Nome_tipo VARCHAR(20) NOT NULL,
Posti_max INTEGER NOT NULL,
Casa_costruttrice VARCHAR(15) NOT NULL,
PRIMARY KEY (Nome_tipo) );
CREATE TABLE PUÓ_ATTERRARE (Nome_tipo_aeroplano VARCHAR(20) NOT NULL,
Codice_aeroporto CHAR(3) NOT NULL,
PRIMARY KEY (Nome_tipo_aeroplano, Codice_aeroporto),
FOREIGN KEY (Nome_tipo_aeroplano) REFERENCES
TIPO_AEROPLANO (Nome_tipo),
FOREIGN KEY (Codice_aeroporto) REFERENCES
AEREOPORTO (Codice_aeroporto) );
CREATE TABLE AEROPLANO (Id_aeroplano INTEGER NOT NULL,
Numero_totale_di_posti INTEGER NOT NULL,
Tipo_aeroplano VARCHAR(20) NOT NULL,
PRIMARY KEY (Id_aeroplano),
FOREIGN KEY (Tipo_aeroplano) REFERENCES TIPO_AEROPLANO (Nome_tipo) );
CREATE TABLE PRENOTAZIONE_POSTI (Numero_volo VARCHAR(6) NOT NULL,
Numero_tratta INTEGER NOT NULL,
Data DATE NOT NULL,
Numero_posto VARCHAR(4),
Nome_cliente VARCHAR(30) NOT NULL,
Telefono_cliente CHAR(12),
PRIMARY KEY (Numero_volo, Numero_tratta, Data, Numero_posto),
FOREIGN KEY (Numero_volo, Numero_tratta, Data) REFERENCES
ISTANZA_DI_TRATTA (Numero_volo, Numero_tratta, Data) );
Esercizio 8.9
Considerato lo schema BIBLIOTECA:
Vediamo le scelte possibili. In generale, se non è chiaro quale azione scegliere, REJECT è l'opzione migliore, poiché
non permette delle modifiche non desiderate, create dalla propagazione della modifica; vediamo le varie relazioni.
AUTORI_LIBRO(Id_libro) ⊆ LIBRO(Id_libro)
Si può utilizzare CASCADE sia nel caso di DELETE che di UPDATE (finchè esiste una corrispondenza con un attributo
multi-valore di LIBRO).Quindi, se una tupla di LIBRO è cancellata, il valore di Id_libro viene aggiornato (modificato), la
cancellazione o modifica viene automaticamente propagata alla tupla referenziata da AUTORI_LIBRO
4
LIBRO(Nome_editore) ⊆ EDITORE(Nome)
Si può utilizzare una REJECT per una DELETE (non si dovrebbe cancellare una tupla di EDITORE che ha una tupla di
LIBRO che referenzia una tupla di EDITORE)
Si può utilizzare una CASCADE per una UPDATE (se l'attributo Nome di una tupla di EDITORE viene aggiornata, la
modifica viene propagata automaticamente a tutte le tuple referenziate in LIBRO).
PRESTITI_LIBRO(Id_libro) ⊆ LIBRO(Id_libro)
Possiamo usare una CASCADE sia in caso di DELETE che UPDATE (se una tupla di LIBRO viene cancellata, il valore
corrispondente dell'attributo Id_libro viene aggiornato, e la cancellazione o modifica viene automaticamente propagata
alle tuple referenziate in PRESTITI_LIBRO); si potrebbe anche scegliere REJECT in caso di DELETE.
COPIE_LIBRO(Id_libro) ⊆ LIBRO(Id_libro)
Possiamo usare una CASCADE sia in caso di DELETE che di UPDATE (se una tupla di LIBRO viene cancellata, il
valore corrispondente dell'attributo Id_libro viene aggiornato, e la cancellazione o modifica viene automaticamente
propagata alle tuple referenziate in COPIE_LIBRO);
PRESTITI_LIBRO(Num_tessera) ⊆ UTENTE(Num_tessera)
Si può usare una CASCADE sia in caso di DELETE che di UPDATE (se una tupla di UTENTE viene cancellata, o il
valore di Num_tessera viene aggiornato (modificato), la cancellazione o modifica viene automaticamente propagata
alle tuple referenziate in PRESTITI_LIBRO); si può anche scegliere una REJECT su una DELETE, con l'ipotesi che se
un UTENTE viene cancellato, è necessario notificare con una stampa tutti i PRESTITI_LIBRO di quell'utente, prima di
cancellare definitivamente la tupla di UTENTE. In questo caso, le tuple in PRESTITO_LIBRO che referenziano la tupla
di UTENTE che si vuole cancellare, devono essere cancellate dopo la stampa e prima che la tupla di UTENTE venga
cancellata.
COPIE_LIBRO(Id_sede) ⊆ SEDE_BIBLIOTECA(Id_sede)
Si può usare una CASCADE sia in caso di DELETE che di UPDATE (se una tupla di SEDE_BIBLIOTECA viene
cancellata, o il valore dell'attributo Id_sede viene aggiornato(modificato), la cancellazione o modifica viene
automaticamente propagata alla tupla referenziata in COPIE_LIBRO). Avremmo potuto anche scegliere una REJECT
in caso di DELETE.
PRESTITI_LIBRO(Id_sede) ⊆ SEDE_BIBLIOTECA(Id_sede)
Si può usare una CASCADE sia in caso di DELETE che di UPDATE (se una tupla di SEDE_BIBLIOTECA viene
cancellata, o il valore dell'attributo Id_sede viene aggiornato(modificato), la cancellazione o modifica viene
automaticamente propagata alla tupla referenziata in PRESTITI_LIBRO).. Avremmo potuto anche scegliere una
REJECT in caso di DELETE.
Esercizio 8.10
Consideriamo la base di dati BIBLIOTECA vista nell'esercizio precedente, e scriviamo le istruzioni in SQL DDL per
dichiarare la base di dati. Una soluzione possibile è il seguente set di istruzioni.
CREATE TABLE LIBRO (Id_libro CHAR(10) NOT NULL,
Titolo VARCHAR(30) NOT NULL,
Nome_editore VARCHAR(20),
PRIMARY KEY (Id_libro),
FOREIGN KEY (Nome_editore) REFERENCES EDITORE (Nome) ON UPDATE CASCADE );
CREATE TABLE AUTORI_LIBRO (Id_libro CHAR(10) NOT NULL,
Nome_autore VARCHAR(30) NOT NULL,
PRIMARY KEY (Id_libro, Nome_autore),
FOREIGN KEY (Id_libro) REFERENCES LIBRO (Id_libro)
ON DELETE CASCADE ON UPDATE CASCADE);
CREATE TABLE COPIE_LIBRO (Id_libro CHAR(10) NOT NULL,
Id_sede INTEGER NOT NULL,
N_di_copie INTEGER NOT NULL,
PRIMARY KEY (Id_libro, Id_sede),
FOREIGN KEY (Id_libro) REFERENCES LIBRO (Id_libro)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Id_sede) REFERENCES SEDE_BIBLIOTECA(Id_sede)
ON DELETE CASCADE ON UPDATE CASCADE );
5
CREATE TABLE EDITORE (Nome VARCHAR(20) NOT NULL,
Indirizzo VARCHAR(30) NOT NULL,
Telefono CHAR(12),
PRIMARY KEY (Nome));
CREATE TABLE UTENTE (Num_tessera INTEGER NOT NULL,
Nome VARCHAR(30) NOT NULL,
Indirizzo VARCHAR(40) NOT NULL,
Telefono CHAR(12),
PRIMARY KEY (Num_tessera));
CREATE TABLE PRESTITI_LIBRO(Num_tessera INTEGER NOT NULL,
Id_libro CHAR(10) NOT NULL,
Id_sede INTEGER NOT NULL,
Data_di_prestito DATE NOT NULL,
Data_di_riconsegna DATE NOT NULL,
PRIMARY KEY (Num_tessera, Id_libro, Id_sede),
FOREIGN KEY (Num_tessera) REFERENCES UTENTE (Num_tessera)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Id_sede) REFERENCES SEDE_BIBILIOTECA (Id_sede)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (Id_libro) REFERENCES LIBRO (Id_libro)
ON DELETE CASCADE ON UPDATE CASCADE );
CREATE TABLE SEDE_BIBLIOTECA (Id_sede INTEGER NOT NULL,
Nome_sede VARCHAR(20) NOT NULL,
Indirizzo VARCHAR(40) NOT NULL,
PRIMARY KEY (Id_sede));
Esercizio 8.11
Scriviamo le interrogazioni SQL corrispondenti alle query:
a) Quante copie del libro intitolato La tribu perduta possiede la sede della biblioteca si nome 'Sharpstown'.
SELECT N_di_copie
FROM ((LIBRO NATURAL COPIE_LIBRO) NATURAL JOIN SEDE_BIBLIOTECA)
WHERE Titolo='La tribù perduta' AND Nome_sede='Sharpstown'
b) Quante copie del libro intitolato La tribu perduta possiede la sede della biblioteca si nome 'Sharpstown'.
SELECT Id_sede, N_di_copie
FROM ((LIBRO NATURAL JOIN COPIE_LIBRO) NATURAL JOIN SEDE_BIBLIOTECA)
WHERE Titolo='La tribù perduta'
c) Si trovino i nomi di tutti gli utenti della biblioteca che non hanno alcuni libro in prestito.
SELECT Nome
FROM UTENTE U
WHERE NOT EXIST (SELECT *
FROM PRESTITO_LIBRO P
WHERE U.Num_Tessera = P. Num_Tessera )
d) Per ogni libro in prestito proveniente dalla sede di 'Sharpstown' e la cui Data_di_riconsegna è oggi, si trovino il titolo
del libro, e il nome e indirizzo di chi lo ha preso in prestito.
SELECT L.Titolo, U.Nome, U.Indirizzo
FROM LIBRO L, UTENTE U, PRESTITI_LIBRO P, SEDE_BIBLIOTECA B
WHERE B.Nome_sede='Sharpstown' AND B.Id_sede=P.Id_sede AND
P.Data_di_riconsegna='today' AND P.Num_tessera=U. Num_tessera AND P.Id_libro=L. Id_libro
e) Per ogni sede della biblioteca si trovi il nome della sede e il numero totale di libri in prestito provenienti da quella
sede.
SELECT B.Nome_sede, COUNT(*)
FROM COPIE_LIBRO C, SEDE_BIBLIOTECA B
WHERE C.Id_sede = B. Id_sede
GROUP BY B.Nome_sede
6
f) Per tutti gli utenti che hanno più di 5 libri in prestito, si trovino i nomi, gli indirizzi e il numeri di libri attualmente in
prestito.
SELECT U.Num_tessera, U.Nome, U.Indirizzo, COUNT(*)
FROM UTENTE U, PRESTITI_LIBRO P
WHERE U.Num_tessera = P.Num_tessera
GROUP BY U.Num_tessera
HAVING COUNT(*) > 5
g) Per ogni libro fra i cui autori c'è 'Stephen King', si trovi il titolo e il numero di copie possedute dalla sede della
biblioteca il cui nome è 'Central'.
SELECT Titolo, N_di_copie
FROM (((AUTORI_LIBRO NATURAL JOIN LIBRO) NATURAL JOIN COPIE_LIBRO)
NATURAL JOIN SEDE_BIBLIOTECA)
WHERE Nome_autore = 'Stephen King' and Nome_sede = 'Central'
Esercizio 8.12
Una tecnica che viene usata frequentemente per controllare il vincolo di chiave è quella di creare un indice
sull'insieme di attributi che costituiscono ciascuna chiave (primaria o secondaria). Prima di inserire una nuova tupla,
viene verificato che nell'indice non esista già un altro valore uguale a quello della chiave della nuova tupla che si vuole
inserire. Se questo non accade, il record viene inserito con successo.
Per controllare il vincolo sulla chiave esterna, viene creato un indice sulla chiave primaria di ogni relazione che viene
referenziata. Ogni volta che viene inserita una nuova tupla in una delle tabelle referenziante, la sua chiave esterna
viene utilizzata per una ricerca nell'indice corrispondente alla chiave primaria della tabella referenziata; se la tupla
referenziata esiste, allora nuova tupla può essere inserita con successa nella tabella referenziante.
In caso di cancellazione di un record referenziato, è utile aver creato un indice sulla chiave esterna di ogni tabella
referenziante, in modo da poter determinare in maniera efficiente se una data tupla referenzia una tupla da eliminare.
(Se non sono disponibili strutture di accesso come indici o strutture di ogni altro tipo, si può effettuare una ricerca
lineare per controllare l'esistenza delle chiavi referenziate, anche se è un metodo molto inefficiente.)
Esercizio 8.13
Vediamo una possibile soluzione (per il risultato delle interrogazioni, rimandiamo alla soluzione dell'esercizio 6.16 data
in precedenza).
a)
Si trovino i nomi di tutti gli impiegati del dipartimento 5 che lavorano più di 10 ore la settimana sul progetto
'ProdottoX'.
SELECT Nome_batt, Cognome
FROM IMPIEGATO, LAVORA_SU, PROGETTO
WHERE (Num_d=5 AND SSN=SSN_I AND N_p= Numero_p AND Nome_p='ProdottoX' AND Ore>10)
Oppure:
SELECT Nome_batt, Cognome
FROM IMPIEGATO
WHERE Num_d=5 AND SSN IN ( SELECT SSN_I
FROM LAVORA_SU
WHERE Ore>10 AND N_p IN ( SELECT Numero_p
FROM PROGETTO
WHERE Nome_p='ProdottoX' ))
b)
Si fornisca un elenco dei nomi di tuti gli impiegati che hanno una persona a carico con il loro stesso nome di
battesimo.
SELECT Nome_batt, Cognome
FROM IMPIEGATO, PERSONA_A_CARICO
WHERE SSN=SSN_I AND Nome_batt=Nome_persona_a_carico
Oppure:
SELECT Nome_batt, Cognome
FROM IMPIEGATO
WHERE EXISTS ( SELECT *
FROM PERSONA_A_CARICO
WHERE Nome_batt=Nome_persona_a_carico AND SSN=SSN_I )
7
c)
Si trovino i nomi di tutti gli impiegati che sono direttamente sorvegliati da 'Franklin Wong'.
SELECT I.Nome_batt, I.Cognome
FROM IMPIEGATO I, IMPIEGATO S
WHERE S.Nome_batt='Franklin' AND S.Cognome='Wong' AND I.SUPER_SSN=S.SSN
Oppure:
SELECT Nome_batt, Cognome
FROM IMPIEGATO
WHERE SUPER_SSN IN (SELECT SSN
FROM IMPIEGATO
WHERE Nome_batt='Franklin' AND Cognome='Wong')
d)
Per ogni progetto si elenchi il nome del progetto e il numero totale di ore settimanali dedicate al progetto (da
parte di tutti gli impiegati).
SELECT Nome_p, SUM (Ore)
FROM PROGETTO, LAVORA_SU
WHERE Numerp_p=N_p
GROUP BY Nome_p
e)
Si trovino i nomi di tutti gli impiegati che lavorano su ogni progetto.
SELECT Nome_batt, Cognome
FROM IMPIEGATO
WHERE NOT EXISTS (SELECT Numero_p
FROM PROGETTO
WHERE NOT EXISTS (SELECT *
FROM LAVORA_SU
WHERE Numero_p=N_p AND SSN_I=SSN ) )
f)
Si trovino i nomi di tutti gli impiegati che non lavorano a nessun progetto.
SELECT Nome_batt, Cognome
FROM IMPIEGATO
WHERE NOT EXISTS (SELECT *
FROM LAVORA_SU
WHERE SSN_I=SSN)
g)
Per ogni dipartimento si trovi il nome del dipartimento e lo stipendio medio di tutti gli impiegati che lavorano in
quel dipartimento.
SELECT Nome_d, AVG (Stipendio)
FROM DIPARTIMENTO, IMPIEGATO
WHERE Numero_d=N_d
GROUP BY Nome_d
h)
Si trovi lo stipendio medio di tutti gli impiegati di sesso femminile.
SELECT AVG (Stipendio)
FROM IMPIEGATO
WHERE Sesso='F'
i)
Si trovino i nomi e gli indirizzi di tutti gli impiegati che lavorano su almeno un progetto con sede a Houston ma
il cui dipartimento non ha sede a Houston.
SELECT Nome_batt, Cognome, Indirizzo
FROM IMPIEGATO
WHERE EXISTS (SELECT *
FROM LAVORA_SU, PROGETTO
WHERE SSN=SSN_I AND N_p=Numero_p AND Sede_p='Houston' )
AND NOT EXISTS (SELECT *
FROM SEDI_DIP
WHERE N_d=Numero_d AND Sede_d='Houston' )
j)
Si fornisca un elenco dei cognomi di tutti i direttori di dipartimento che non hanno persone a carico
SELECT Cognome
FROM IMPIEGATO
WHERE EXISTS (SELECT *
FROM DIPARTIMENTO
WHERE SSN=SSN_DIR )
AND NOT EXISTS (SELECT *
FROM PERSONA_A_CARICO
WHERE SSN=SSN_I)
8
Esercizio 8.14
a) Si trovino i nomi dei dipartimenti e i numeri degli impiegati che lavorano in ogni dipartimento i cui impiegati
hanno uno stipendio medio di più di 30.000 dollari.
SELECT Nome_d, COUNT (*)
FROM DIPARTIMENTO, IMPIEGATO
WHERE Numero_d=N_d
GROUP BY Nome_D
HAVING AVG (Stipendio) > 30000
Risultato:
Nome_d
Numero_d Count(*)
Ricerca
5
4
Amministrazione 4
3
Sede centrale
1
1
b) Si supponga di voler individuare il numero di impiegati maschi in ciascun dipartimento anziché tutti i
dipendenti. Si può specificare una interrogazione in SQL? Perchè o perchè no?
SELECT Nome_d, COUNT (*)
FROM DIPARTIMENTO, IMPIEGATO
WHERE Numero_d=N_d AND SEX='M' AND N_d IN (SELECT N_d
FROM IMPIEGATO
GROUP BY N_d
HAVING AVG (Stipendio) > 30000 )
GROUP BY Nome_d
Risultato:
Nome_d
Numero_d Count(*)
Ricerca
5
3
Amministrazione 4
1
Sede centrale
1
Esercizio 8.15
Vediamo una possibile soluzione, ricordando che alcuni degli inserimenti violano i vincoli di integrità referenziale.
a. Inserimento di
<'Robert', 'F', 'Scott', '943775543', '1952-06-21', '2365 Newcastle Rd, Bellaire, TX', M, 58000, '888665555', 1> in
IMPIEGATO
INSERT INTO IMPIEGATO
VALUES ('Robert', 'F', 'Scott', '943775543', '1952-06-21', '2365 Newcastle Rd, Bellaire, TX',M, 58000, '888665555',
1)
b. Inserimento di < 'ProdottoA', 4, 'Bellaire', 2 > in PROGETTO
INSERT INTO PROGETTO
VALUES ('ProdottoA', 4, 'Bellaire', 2)
c. Inserimento di < 'Produzione', 4, '943775543', '1988-10-01' > in DIPARTIMENTO
INSERT INTO DIPARTIMENTO
VALUES ('Produzione', 4, '943775543', '1988-10-01' )
d. Inserimento di < '677678989', null, '40,0' > into LAVORA_SU
INSERT INTO LAVORA_SU
VALUES ('677678989', NULL, '40,0')
e. Inserimento di < '453453453', 'John', M, '1970-12-12', 'coniuge' > in PERSONA_A_CARICO
INSERT INTO PERSONA_A_CARICO
VALUES ('453453453', 'John', M, '1970-12-12', 'coniuge')
f. Cancellazione della tupla con SSN_I= '333445555' da LAVORA_SU
DELETE FROM LAVORA_SU
WHERE SSN_I= '333445555'
9
g. Cancella da IMPIEGATO la tupla con SSN= '987654321'.
DELETE FROM IMPIEGATO
WHERE SSN= '987654321'
h. Cancella da PROGETTO la tupla con Nome_p= 'ProdottoX'.
DELETE FROM PROGETTO
WHERE Nome_p= 'ProdottoX'
i. Modifica il SSN_DIR e Data_inizio_dir della tupla di DIPARTIMENTO con Numero_d=5 in '123456789' e '1999-1001', rispettivamente
UPDATE DIPARTIMENTO
SET SSN_DIR = '123456789', Data_inizio_dir = '1999-10-01'
WHERE Numero_d= 5
j. Modifica l'attributo SUPER_SSN della tupla di IMPIEGATO con SSN= '999887777' in '943775543'.
UPDATE IMPIEGATO
SET SUPER_SSN = '943775543'
WHERE SSN= '999887777'
k. Modifica l'attributo Ore della tupla di LAVORA_SU con SSN_I= '999887777' e N_p= 10 in '5,0'.
UPDATE LAVORA_SU
SET HOURS = '5,0'
WHERE SSN_I= '999887777' AND N_p= 10
Esercizio 8.16
Dato lo schema:
STUDENTE(Nome, Numero_studente, Anno_corso, Corso_laurea)
INSEGNAMENTO(Nome_insegnamento, Codice_insegnamento, Ore, Dipartimento)
MODULO(Identificatore_modulo, Codice_insegnamento, Semestre, Anno, Docente)
VOTAZIONE(Numero_studente, Identificatore_modulo, Voto)
PROPEDEUTICITÀ(Codice_insegnamento, Codice_propedeuticità)
a. Si trovino i nomi di tutti gli studenti dell'ultimo anno che si specializzano in CS (computer science, informatica).
SELECT Nome
FROM STUDENTE
WHERE Corso_laurea='CS'
b. Si trovino i nomi di tutti i corsi tenuti dal professor King dal 2004 al 2005.
SELECT Nome_insegnamento
FROM INSEGNAMENTO, MODULO
WHERE INSEGNAMENTO.Codice_insegnamento=MODULO.Codice_insegnamento AND Docente='King'
AND (Anno='2004' OR Anno='2005')
Oppure:
SELECT Nome_insegnamento
FROM INSEGNAMENTO
WHERE Codice_insegnamento IN (SELECT Codice_insegnamento
FROM SECTION
WHERE Docente='King' AND (Anno='2004' OR Anno='2005'))
c. Per ciascun modulo di corso tenuto dal professor King si trovino il numero di corso, il semestre, l'anno e il numero
di studenti che scelgono il modulo.
SELECT Codice_insegnamento, Semestre, Anno, COUNT(*)
FROM MODULO, VOTAZIONE
WHERE Docente='King' AND MODULO.Identificatore_modulo=VOTAZIONE.SectionIdentifier
GROUP BY Codice_insegnamento, Semestre, Anno
d. Si trovino il nome e la trascrizione del libretto universitario di ogni studente dell'ultimo anno (AnnoCorso = 4) che si
specializza in CS. Una trascrizione include il nome dell'insegnamento, il codice dell'insegnamento completato dallo
studente.
SELECT Nome, Nome_insegnamento, I.Codice_insegnamento, Semestre, Anno, Voto
FROM STUDENTE ST, INSEGNAMENTO I, MODULO M, VOTAZIONE V
WHERE Anno_corso=4 AND Corso_laurea='CS' AND ST.Numero_studente=V.Numero_studente AND
V.Identificatore_modulo=M.Identificatore_modulo AND M.Codice_insegnamento=I.Codice_insegnamento
10
e. Si trovino i nomi e i dipartimenti di specializzazione degli studenti più meritevoli (studenti che hanno conseguito il
massimo dei voti in tutti i loro insegnamenti).
SELECT Nome, Corso_laurea
FROM STUDENTE
WHERE NOT EXISTS ( SELECT *
FROM VOTAZIONE
WHERE Numero_studente= STUDENT.Numero_studente AND NOT(Voto='30L'))
f. Si trovino i nomi e i dipartimenti di specializzazione di tutti gli studenti che non hanno il massimo dei voti in tutti gli
insegnamenti da loro frequentati.
SELECT Nome, Corso_laurea
FROM STUDENTE
WHERE NOT EXISTS ( SELECT *
FROM VOTAZIONE
WHERE Numero_studente= STUDENTE.Numero_studente AND Voto='30L' )
Esercizio 8.17
Data la base di dati dell'esercizio precedente:
a. Si inserisca un nuovo studente <'Johnson', 25, 1, 'MATH'> nella base di dati.
INSERT INTO STUDENTE
VALUES ('Johnson', 25, 1, 'MATH')
b. Si cambi la classe dello studente 'Smith' in 2.
UPDATE STUDENTE
SET Anno_corso = 2
WHERE Name='Smith'
c. Si inserisca un nuovo corso <'Ingegneria della conoscenza','CS4390', 3,'CS'>.
INSERT INTO INSEGNAMENTO
VALUES ('Ingegneria della conoscenza','CS4390', 3,'CS')
d. Si cancelli il record dello studente il cui nome è 'Smith' e il cui numero è 17.
DELETE FROM STUDENTE
WHERE Nome='Smith' AND Numero_studente=17
11
Esercizio 8.18
Data la base di dati COMPAGNIA_AEREA, scriviamo in SQL le query seguenti.
N. B. Questo esercizio preferivamo discuterlo in classe, in quanto la soluzione che abbiamo fornito si presta a discussione..
a. Per ogni volo si elenchi il numero del volo, l'aeroporto di partenza per la prima tratta del volo e l'aeroporto di
arrivo per l'ultima tratta del volo.
SELECT Numero_volo, P.Nome, A.Nome
FROM VOLO NATURAL JOIN (TRATTA_VOLO JOIN VOLO ON Codice_aeroporto_partenza =
Codice_aeroporto) AS P NATURAL JOIN (TRATTA_VOLO JOIN VOLO ON
Codice_aeroporto_arrivo = Codice_aeroporto) AS A
WHERE P.Numero_tratta <= ALL (SELECT Numero_tratta
FROM TRATTA_VOLO
WHERE Numero_volo = Numero)
AND A.Numero_tratta >= ALL (SELECT Numero_tratta
FROM TRATTA_VOLO
WHERE Numero_volo = Numero)
b. Si fornisca un elenco dei numeri di volo e dei giorni della settimana per tutti i voli o le tratte di volo che partono
dall'Aeroporto Intercontinentale di Houston (codice aeroporto 'IAH') e arrivano all'Aeroporto Internazionale di
Los Angeles (codice aeroporto 'LAX').
SELECT Numero, Giorni_settimana
FROM TRATTA_VOLO, VOLO
WHERE Codice_aeroporto_partenza = 'IAH' AND Codice_aeroporto_arrivo = 'LAX' AND
Numero = Numero_volo
12
c.
Si elenchi il numero di volo, il codice dell'aeroporto di partenza, l'orario di partenza previsto, il codice
dell'aeroporto di arrivo, l'orario di arrivo previsto nonché i giorni della settimana di tutti i voli o le tratte di volo
che partono da un aeroporto della città di Houston e arrivano a un aeroporto della città di Los Angeles.
SELECT Numero_volo, Codice_aeroporto_partenza, Orario_previsto_partenza, Codice_aeroporto_arrivo,
Orario_previsto_arrivo, Giorni_settimana
FROM TRATTA_VOLO NATURAL JOIN VOLO
WHERE Codice_aeroporto_partenza IN (SELECT Codice_aeroporto
FROM AEROPORTO
WHERE Città = 'Houston')
AND Codice_aeroporto_partenza IN (SELECT Codice_aeroporto
FROM AEROPORTO
WHERE Città = 'Los Angeles')
d. Si elenchino tutte le informazioni sulle tariffe per il volo numero 'CO 197'.
SELECT *
FROM TARIFFE
WHERE Numero_volo = 'CO 197'
e. Si trovi il numero di posti disponibili per il volo numero 'CO 197' in data '1999-10-09'.
SELECT MIN(Num_posti)
FROM ISTANZA_DI_TRATTA
WHERE (Numero_volo = 'CO 197' AND Data = '1999-10-09')
NB: è stato fatto il MIN sull'attributo NUMERO_POSTI_DISPONIBILI poiché un volo, è composto da più tratte, e
non è detto che in ciascuna tratta venga utilizzato lo stesso aeroplano, all'interno dello stesso volo.
Esercizio 8.20
In riferimento all'esercizio 6.19:
CLIENTE(#Cliente, NomeC, Città)
ORDINE(#Ordine, DataO, #Cliente, Imp_Ord)
ORDINE_ARTICOLO(#Ordine, #Articolo, Qtà)
ARTICOLO(#Articolo, Prezzo_unitario)
SPEDIZIONE(#Ordine, #Magazzino, Data_sped)
MAGAZZINO(#Magazzino, Città)
Su in riferimento a questo database, specifichiamo le interrogazioni in DDL SQL:
a. Si fornisca il #Ordine e la Data_sped per tutti gli ordini spediti dal magazzino denominato 'W2'.
SELECT #Ordine, Data_sped
FROM SPEDIZIONE
WHERE #Magazzino ='W2'
b. Si elenchino le informazioni sui magazzini dai quali il cliente di nome 'Jose Lopez' ha ricevuto i suoi ordini. Si
produca un elenco #Ordine, #Magazzino.
SELECT #Ordine, #Magazzino
FROM SPEDIZIONE S
WHERE EXIST (SELECT #Ordine
FROM ORDINE O, CLIENTE C
WHERE O.#Cliente = C.#Cliente AND
S.#Ordine = O.#Ordine AND
C.NomeC = 'Jose Lopez')
c. Si produca un elenco: NOME_CLIENTE, #DI_ORDINI, IMP_MEDIO_ORDINE, dove la colonna di mezzo
corrisponde al numero totale di ordini provenienti dal cliente e l'ultima colonna corrisponde all'importo medio
per ordine di quel cliente.
SELECT NomeC, Count(#Ordine), AVG(Imp_Ord)
FROM ORDINE O, CLIENTI C
WHERE O.#Cliente = C.#Clientex
d. Si elenchino gli ordini non spediti nell'arco di 30 giorni dalla data di emissione dell'ordine.
SELECT *
FROM ORDINE O, SPEDIZIONE S
WHERE Data_sped > (DataO + 30) AND
O.#Ordine = S.#Ordine
13
e. Si elenchi il #Ordine per gli ordini spediti da tutti i magazzini che l'azienda possiede a New York.
SELECT #Ordine
FROM SPEDIZIONE S
WHERE EXSIST (SELECT *
FROM MAGAZZINO M
WHERE M.#Magazzino = S.#Magazzino
AND M.Città = 'New York' )
In riferimento all'esercizio 6.20
RAPPRESENTANTE(SSN, Nome, Anno_Inizio, Num_Dip)
VIAGGIO(SSN, Città_Partenza, Città_Arrivo, Data_Partenza, Data_Ritorno, ID_viaggio)
COSTO(ID_viaggio, #Conto, Importo)
a. Si forniscano i dettagli (cioè tutti gli attributi della relazione VIAGGIO) per i viaggi il cui costo supera i 2000
dollari).
SELECT *
FROM VIAGGIO V
WHERE EXIST (SELECT SUM(Importo)
FROM COSTO C
WHERE V.ID_viaggio = C.ID_viaggio AND
HAVING SUM(Importo)>2000)
Nella seconda select il GROUP BY non serve perchè ho già eliminato con la clausola WHERE tutti i costi che
non mi interessano.
b. Si stampi il valore di SSN del rappresentante che effettua viaggi a 'Honolulu'.
SELECT SSN
FROM RAPPRESENTANTE R, VIAGGIO V
WHERE Città_Arrivo = 'Honolulu' AND R.SSN = V.SSN
c. Si stampino i costi totali di viaggio sostenuti dal venditore con SSN='234-56-7890'
SELECT SUM(Importo)
FROM VIAGGIO V, COSTO C
WHERE V.SSN = '234-56-7890' AND
V.ID_viaggio = C.ID_viaggio
In riferimento all'esercizio 6.21
STUDENTE(SSN, Nome, Specializzazione, DataN)
INSEGNAMENTO(#Insegnamento, NomeI, Dip)
ISCRIZIONE(SSN, #Insegnamento, Trimestre, Voto)
ADOZIONE_LIBRO(#Insegnamento, Trimestre, ISBN_Libro)
TESTO(ISBN_Libro, Titolo_Libro, Editore, Autore)
a. Si elenchi il numero di insegnamenti seguiti da tutti gli studenti di nome 'John Smith' nel trimestre dell'inverno
1999 (ossia Trimestre='W99', W = winter).
SELECT COUNT(#Insegnamento)
FROM ISCRIZIONE I, STUDENTE S
WHERE Nome = 'John Smith' AND
Trimestre ='W99' AND
S.SSN = I.SSN
b. Si produca un elenco di libri di testo (che comprende #Insegnamento, ISBN_Libro, Titolo_Libro) per gli
insegnamenti offerti dal dipartimento 'CS' (CS = Computer Science) che hanno fatto uso di più di 2 libri.
SELECT #Insegnamento, ISBN_Libro, Titolo_Libro
FROM INSEGNAMENTO I, TESTO T
WHERE (SELECT COUNT(DISTINCT ISBN_Libro)
FROM ADOZIONE_LIBRO A
WHERE I.Dip = 'CS' AND
I.#Insegnamento = A.#Insegnamento AND
A.ISBN_Libro = T.ISBN_Libro
HAVING COUNT(DISTINCT ISBN_Libro)>2
14
c.
Si elenchi ogni dipartimento che ha tutti i libri adottati da 'AWL Publishing'.
SELECT Dip
FROM INSEGNAMENTO I
WHERE NOT EXIST ( SELECT *
FROM TESTO T, ADOZIONE_LIBRO A
WHERE I.#Insegnamento = A.#Insegnamento
AND A.ISBN_Libro = T.ISBN_Libro
AND NOT (Editore = 'AWL Publishing'))
In riferimento all'esercizio 6.23
Dato il database:
AUTOMOBILE(Num-Serie, Modello, Produttore, Prezzo)
OPZIONI(Num-Serie, Nome-Opzione, Prezzo)
VENDITE(Id-Venditore, Num-Serie, Data, Prezzo-Vendita)
VENDITORE(Id-Venditore, Nome, Telefono)
Rispetto al database, risolviamo le query in algebra relazionale.
a. Per il venditore di nome 'Jane Doe' si fornisca un elenco con le seguenti informazioni per tutte le automobili
che ha venduto: Num-Serie, Produttore, Prezzo-Vend
SELECT Num-Serie, Produttore, Prezzo-Vend
FROM AUTOMOBILE A, VENDITE V, VENDITORE Z
WHERE Nome = 'Jane Doe' AND
A.Num-Serie = V.Num-Serie AND
V.Id-Venditore = Z.Id-Venditore
b. Si elenchi il Num-Serie e il Modello delle automobili che non hanno opzioni.
SELECT Num-Serie, Modello
FROM AUTOMOBILE, OPZIONI
WHERE Num-Serie NOT IN (SELECT *
FROM OPZIONI)
Esercizio 8.21
Specifichiamo in SQL, le interrogazioni sulla base di dati AZIENDA:
IMPIEGATO(Nome_batt, Iniz_int, Cognome, SSN, Data_n, Indirizzo, Sesso, Stipendio, Super_SSN, N_d)
DIPARTIMENTO(Nome_d, Numero_d, SSN_dir, Data_inizio_dir)
SEDI_DIP(Numero_d, Sede_d)
PROGETTO(Nome_p, Numero_p, Sede_p, Num_d)
LAVORA_SU(SSN_I, N_p, Ore)
PERSONA_A_CARICO(SSN_I, Nome_persona_a_carico, Sesso, Data_n, Parentela)
a. Si mostrino i nomi degli impiegati che lavorano nello stesso dipartimento dell'impiegato che percepisce lo stipendio
più elevato.
SELECT Nome_batt, Cognome
FROM IMPIEGATO WHERE N_d = (SELECT N_d FROM IMPIEGATO WHERE Stipendio =
(SELECT MAX(Stipendio) FROM IMPIEGATO) )
b. Si mostrino i nomi degli impiegati il cui supervisore ha un supervisore che possiede un SSN con valore
'888665555'.
SELECT Nome_batt, Cognome
FROM IMPIEGATO WHERE Super_SSN IN
(SELECT SSN FROM IMPIEGATO WHERE Super_SSN = ‘888665555’ )
c. Si mostrino i nomi degli impiegati che percepiscono oltre $10.000 in più dell'impiegato con lo stipendio più basso.
SELECT Nome_batt, Cognome FROM IMPIEGATO
WHERE Stipendio >= 10000 +
(SELECT MIN(Stipendio) FROM IMPIEGATO)
15
Prova scritta – 15 Febbraio 2012 – 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)
Scriviamo l’espressione delle query SQL che:
a)
Seleziona i corsi (codCorso, nome) ai quali si è iscritto almeno un socio di Rimini.
SELECT codCorso, CORSO.nome
FROM (CORSO NATURAL JOIN ISCRIZIONE) NATURAL JOIN SOCIO
WHERE città = Rimini
b)
Seleziona i corsi (codCorso, nome, descrizione) che non hanno ancora nessun iscritto.
SELECT codCorso, nome, descrizione
FROM CORSO
WHERE codCorso NOT IN (SELECT codCorso
FROM ISCRIZIONE)
c)
Mostra, per ogni socio, il numero di imbarcazioni possedute (tesserino, nome, numeroImbarcazioni).
SELECT tesserino, nome, COUNT(*) AS numeroImbarcazioni
FROM SOCIOIMBARCAZIONE NATURAL JOIN SOCIO
GROUP BY (tesserino)
d)
Seleziona il corso (codCorso, nome, descrizione) con il numero maggiore di iscritti.
SELECT codCorso, nome, descrizione
FROM CORSO
WHERE codCorso IN (SELECT MAX(codCorso)
FROM (SELECT SUM(*)
FROM ISCRIZIONE
GROUP BY codCorso))
16
Prova scritta – 22 Marzo 2011 – Esercizio 2
Dato il seguente schema relazionale:
RICETTA (nomeRicetta, descrizione, tempoPreparazione, porzioni, istruzioni, tipoRicetta )
INGREDIENTE (nomeIngrediente, prezzoUnitario, calorie )
COMPOSIZIONE (nomeRicetta: RICETTA, nomeIngrediente: INGREDIENTE, quantità )
Scriviamo l’espressione delle query SQL che:
a)
Visualizza gli ingredienti presenti in almeno una ricetta di tipo “Primo piatto” (nomeIngrediente, prezzoUnitario).
SELECT nomeIngrediente, prezzoUnitario
FROM (RICETTA NATURAL JOIN COMPOSIZIONE) NATURAL JOIN INGREDIENTE
WHERE tipoRicetta = “Primo piatto”
b)
Visualizza le ricette di tipo “Dessert” che non contengono l’ingrediente “Burro” (nomeRicetta, descrizione,
tempoPreparazione).
SELECT nomeRicetta, descrizione, tempoPreparazione
FROM RICETTA NATURAL JOIN COMPOSIZIONE
WHERE nomeIngrediente IS NOT “Burro”
GROUP BY (nomeRicetta)
c)
Visualizza, per ciascuna ricetta, il numero di ingredienti necessari per la sua preparazione (nomeRicetta,
descrizione, tipoRicetta, numeroIngredienti).
SELECT nomeRicetta, descrizione, tipoRicetta, COUNT(*) AS numeroIngredienti
FROM RICETTA NATURAL JOIN COMPOSIZIONE
GROUP BY(nomeRicetta)
d)
Visualizza, per ciascun tipo di ricetta, la ricetta con tempo di preparazione minore (tipoRicetta, nomeRicetta,
descrizione, tempoPreparazione).
SELECT tipoRicetta, nomeRicetta, descrizione, MIN(tempoPreparazione) AS tempoPreparazione
FROM RICETTA
GROUP BY (tipoRicetta)
17