Scaricare - Corso Sirio Forum del Corso Sirio ad Indirizzo

Esercizio 1
Un sistema informativo memorizza alcune informazioni su uno zoo, secondo le seguenti
specifiche.
Ogni animale appartiene ad una specie ed è identificato da un codice univoco all'interno della
specie di appartenenza. Una specie è rappresentata da un nome univoco, da un habitat e da una
famiglia. Di un animale viene inoltre riportato il peso e il sesso; alcuni animali sono nati in
cattività a nello zoo: per essi si riporta la data di nascita e i due animali genitori. Ogni specie è
collocata in una certa zona dello zoo; una zona è rappresentata da un codice univoco, da
un'estensione e da un tipo di vegetazione. In una zona può essere collocata un'unica specie.
Le visite veterinarie sono organizzate per zone nel seguente modo: per una zona in una certa data
c'è un unico veterinario assegnato per le visite; viceversa, in una certa data un veterinario è
assegnato ad un'unica zona. Un veterinario è descritto dagli usuali dati anagrafici.
Per ogni specie di animale si deve indicare una dieta standard, costituita da uno o più cibi con le
relative quantità a giornaliere. Un cibo è descritto da un codice identificativo, da un tipo e da una
unità di misura. Devono essere rappresentate le eventuali limitazioni nella dieta di un singolo
animale: in una certa settimana un animale può essere privato di uno o più cibi e per ciascuna di
queste privazioni si devono riportare uno o più motivi e il veterinario che l'ha prescritta.
Progettare lo schema E/R e tradurlo in schema relazionale.
Analizzare e discutere il seguente vincolo: un animale può essere privato di un certo cibo solo se
tale cibo è nella dieta della sua specie.
Esercizio 2
Dato il seguente schema E/R, dove nello schema è presente l’attributo derivato
QuantitàDisponibile, che per una certo articolo è calcolato come la differenza tra la somma delle
quantità che entrano tramite TIR e la somma delle quantità che escono tramite vendita. Si noti
che una vendita riguarda un unico articolo, mentre un TIR trasporta più di un articolo, ciascuno
in una certa quantità.
Rispondere ai seguenti quesiti con delle istruzioni SQL:
a. Inserimento di una nuova vendita (si suppone noto e valido il codice dell'articolo venduto);
b. Visualizzare tutti i dati di un articolo. Si noti che nel caso in cui si deve determinare la
QuantitàDisponibile occorre leggere tutte le entrate e tutte le vendite di quell'articolo;
Esercizio 3
Sia dato il seguente schema relazionale:
TIR(CODTIR, TIPO, ANNO)
LINEA(CODLIN, PARTENZA, ARRIVO)
PERCORRE(DATA, CODTIR, CODLIN, TEMPO)
FK : CODTIR REFERENCES TIR
FK : CODLIN REFERENCES LINEA
Nella DATA specificata, il TIR CODTIR percorre la linea CODLIN impiegando un certo
TEMPO.
Scrivere in SQL le seguenti interrogazioni:
(a) Selezionare i Tir di tipo \Turbo" che hanno percorso una linea che parte oppure che arriva a
Modena.
(b) Selezionare il codice delle linee che arrivano a Modena e che non sono mai state percorse da
un Tir di tipo \Turbo".
(c) Selezionare i Tir che hanno percorso tutte le linee che partono da Modena e arrivano a
Palermo.
(d) Selezionare per ogni linea che parte da Modena, il suo codice e il codice del Tir che ha
percorso tale linea nel tempo pi_ u breve.
Scrivere in algebra relazionale le interrogazioni (a), (b) e (c).
Esercizio 4
Creare un database con il DBMS Access con le caratteristiche descritte nello schema relazionale
seguente. Generare le tabelle necessarie ed indicare le associazioni esistenti fra di esse gestendo i
vincoli di integrità referenziale secondo le seguenti specifiche:
Se un servizio viene eliminato eliminare in cascata tutti i servizi di viaggi ad esso relativi.
Impedire l’eliminazione di un cliente se esistono delle prenotazioni da lui effettuate.
Schema Relazionale
VIAGGIO(numero, descrizione, categoria, durata, costo)
CHECK (categoria = ‘Pacchetto’ OR categoria = ‘Crociera’)
CHECK (durata <= 21)
PRENOTAZIONE(numeroViaggio, codCliente, data)
F.K. (numeroViaggio) REFERENCES VIAGGIO(numero)
F.K. (codCliente) REFERENCES CLIENTE(codCliente)
codCliente NOT NULL
CLIENTE(codCliente, nome, cognome, nazionalità, età)
SERVIZIO(codServizio, descrizione, servizioExtra, costoExtra)
SERVIZI_VIAGGIO(numeroViaggio, codServizio)
F.K. (numeroViaggio) REFERENCES VIAGGIO(numero)
F.K. (codServizio) REFERENCES SERVIZIO(codServizio)
Progettare ed implementare le query elencate.
a) Selezionare i viaggi di costo superiore a 100.000 che non sono stati prenotati tra il 15/09/98 e
il 30/03/99.
b) Aumentare il prezzo di tutti i viaggi di categoria ‘Crociera’ del 20%.
c) Selezionare il viaggio di categoria ‘Crociera’ con il maggior numero di prenotazioni.
Esercizio 5
Una galleria d'arte vuole memorizzare dati sulla esposizione di quadri e sul personale secondo le
seguenti specifiche.
La galleria d'arte è composta di diverse sale di esposizione, ognuna delle quali è rappresentata
tramite un nome (univoco), una descrizione ed un orario di apertura e di chiusura.
Ogni sala di esposizione è custodita giornalmente da un custode, in base ad un orario settimanale
organizzato nel seguente modo: in un dato giorno della settimana un custode è assegnato ad una
unica sala e, viceversa, in un dato giorno della settimana una sala è custodita da un unico
custode. Un custode è descritto tramite il codice fiscale e l'indirizzo.
Per un custode vengono riportate le eventuali assenze: per una certa data in cui esso è assente,
viene indicata la causa dell’assenza e una persona esterna che lo ha sostituito in quella data. Del
personale esterno occorre conoscere, oltre al codice fiscale e all'indirizzo, anche il recapito
telefonico; una persona esterna può sostituire uno o più custodi.
La galleria d'arte organizza delle mostre di quadri; una mostra è descritta dal nome, dall'anno,
dall'organizzatore e dalla data di inizio e di fine; una mostra con un certo nome non può essere
svolta più di una volta nello stesso anno. Per ogni mostra devono essere riportati i quadri esposti
con le relative sale di esposizione: un quadro può essere esposto in una o più mostre e la sua sala
di esposizione può variare da mostra a mostra. Di un quadro viene riportato un codice
identificativo, il nome e l’autore.
Progettare lo schema E/R e tradurlo in schema relazionale.
Esercizio 6
Sia dato il seguente schema relazionale:
CD(CODICECD, AUTORE, CASADISCOGRAFICA);
CLIENTE(NTESSERA, NOME, INDIRIZZO);
ACQUISTO(CODICECD, NTESSERA, DATA, QTY);
FK: NTESSERA REFERENCES CLIENTE
FK: CODICECD REFERENCES CD
Il cliente identificato da NTESSERA ha acquistato, in una certa DATA, un certo numero QTY di
copie del compact disk CODICECD.
Scrivere in SQL le seguenti interrogazioni:
a) selezionare tutti i dati dei clienti che dopo il 1/1/1997 non hanno aquistato nessun CD
prodotto dalla casa discografica “DiscoJolli”.
b) selezionare il numero tessera dei clienti che hanno acquistato tutti i CD dell'autore
“Francesco Guccini”.
c) selezionare, per ogni CD, il numero totale delle copie vendute.
d) selezionare, per ogni casa discografica, il numero tessera del cliente che ha acquistato il
maggior numero di copie di CD di quella casa.
Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 7
Un sistema informativo per la gestione di una banca memorizza informazioni relative ai conti
correnti e alle carte bancomat, secondo le seguenti specifiche.
Un conto corrente ha da uno a cinque clienti intestatari; per un dato contocorrente e per un suo
cliente intestatario può essere rilasciata una sola carta bancomat, descritta da un codice univoco e
da una tipologia. Una data carta bancomat è assegnata ad un unico cliente ed ad un unico conto
corrente. Tramite una carta bancomat è possibile effettuare fino ad un massimo di cinquanta
operazioni al mese; per un’operazione, identificata da un numero progressivo, viene
memorizzato il codice dello sportello in cui è stata effettuata e il tipo dell’operazione; tra le
operazioni vi sono i prelievi, per i quali si memorizza anche l’importo prelevato.
Un conto corrente ha un codice univoco e una descrizione. I clienti sono identificati dal codice
fiscale e descritti dagli usuali dati anagrafici.
Progettare lo schema E/R e tradurlo in schema relazionale.
Esercizio 8
Sia dato il seguente schema relazionale:
CITTÀ(CITTÀ, REGIONE)
TRENO(CODTR, CITTÀ_PART, CITTÀ_ARR, ORA_PART, ORA_ARR)
COMPOSIZIONE(CODTR, CLASSE, NUMEROCAR)
Il treno CODTR ha NUMEROCAR carrozze di classe CLASSE.
Scrivere in SQL le seguenti interrogazioni:
a) Selezionare i treni regionali, cioè i treni le cui città di partenza e di arrivo sono nella stessa
regione.
b) Selezionare le città da cui non transitano (cioè non partono e non arrivano) treni con carrozze
di prima classe dopo le ore 20.20.
a) c ) Selezionare la città dalla quale partono il maggior numero di treni che hanno almeno tre
carrozze di prima classe.
Scrivere in algebra relazionale l’interrogazione b).
Esercizio 9
Dato il seguente schema di relazione:
MAGAZZINO(LOCALE,PRODOTTO,STANZA,SCAFFALE)
e considerando i seguenti vincoli:
– Un prodotto è immagazzinato in uno ed un solo locale
– Un prodotto può essere immagazzinato in uno o più stanze e in uno o più scaffali
– In una stanza di un locale, uno scaffale immagazzina un preciso prodotto
Viene richiesto di:
a. Determinare le dipendenze funzionali insite nello schema di relazione.
b. Determinare la chiave o le chiavi dello schema di relazione.
c. Determinare se lo schema di relazione è in 2NF, 3NF.
Esempio:
Esercizio 10
L’ufficio della motorizzazione vuole memorizzare informazioni sui trasferimenti di proprietà
delle automobili secondo le seguenti specifiche.
Un’automobile è caratterizzata da un modello, dall’anno di produzione, da un numero di serie
assegnatogli dal produttore, unico fra le automobili da lui prodotte. All’atto della registrazione,
all’automobile viene assegnato un numero, unico per ciascuna automobile, e la data di
registrazione. Per le automobili registrate viene memorizzata anche l’eventuale distruzione con la
relativa data in cui ciò avviene. Per i trasferimenti di proprietà vengono memorizzate le seguenti
informazioni: un codice che identifica il trasferimento, la data di trasferimento, l’automobile
trasferita, il vecchio e il nuovo proprietario. Il proprietario di un automobile può essere: il
produttore, un rivenditore oppure un privato. Le norme che vincolano il trasferimento di
un’automobile sono le seguenti:
– possono essere trasferite solo le auto registrate;
– un’automobile distrutta non può più essere trasferita;
– un’automobile può essere venduta dal suo produttore solo ad un rivenditore, e un produttore
non può acquistare automobili;
– un’automobile può essere venduta da un rivenditore solo a privati.
Per i proprietari e i rivenditori vengono riportati gli usuali dati anagrafici; un produttore è
rappresentato tramite un codice univoco, un indirizzo ed uno o più numeri telefonici.
Progettare lo schema E/R e tradurlo in schema relazionale.
Esercizio 11
Sia dato il seguente schema relazionale:
VIA(CODICE, NOME, QUARTIERE, LUNGHEZZA)
INCROCIA(CODICE-VIA-A, CODICE-VIA-B, N_VOLTE)
FK: CODICE-VIA-A REFERENCES VIA
FK: CODICE-VIA-B REFERENCES VIA
La via CODICE-VIA-A incrocia la via CODICE-VIA-B un certo numero di N_VOLTE; si
assume che se nella relazione INCROCIA è presente la tupla <codviax,codviay,5> non sia
presente la tupla simmetrica <codviay,codviax,5>;
Scrivere in SQL le seguenti interrogazioni:
a) selezionare le vie che incrociano almeno una via del quartiere “Pastena”;
b) selezionare le vie che non incrociano via “Marco Polo”;
c) selezionare le coppie (CODICE1, CODICE2) tali che le vie con codice CODICE1 e
CODICE2 abbiano la stessa lunghezza;
d) selezionare il quartiere che ha il maggior numero di vie;
e) selezionare, per ogni quartiere, la via di lunghezza maggiore .
f) selezionare le vie che incrociano tutte le vie del quartiere “Pastena”.
Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 12
Un sistema informativo memorizza dati su un’agenzia immobiliare, secondo le seguenti
specifiche.
L’agenzia pubblicizza gli immobili di cui cura la vendita su giornali. Un immobile può essere
pubblicizzato più volte, su più giornali e in date diverse, con il vincolo di non essere
pubblicizzato più di una volta nella stessa data e sullo stesso giornale. Il prezzo con il quale un
immobile viene pubblicizzato dipende solo dalla data e non dal giornale sul quale viene riportata
la pubblicità. I giornali sono identificati dalla testata e hanno una tiratura, indirizzo, telefono e
fax. Un immobile è identificato da un codice e descritto dai mq commerciali, dal valore proposto,
dal valore minimo e dalla località. L’agenzia tratta case indipendenti, appartamenti e ville. Per le
case indipendenti occorre sapere quanti sono i lati abbinati (ad altre case). Per gli appartamenti
interessa il piano e le unità della palazzina. Per le ville interessa il numero di piani e il numero di
stanze.
Progettare lo schema E/R e tradurlo in schema relazionale.
Esercizio 13
Sia dato il seguente schema relazionale:
CAUSA(CODCAUSA, TIPO, DATA)
DIFESA(CFAVV, CODCAUSA, CFCLIENTE, PARCELLA)
AK: CODCAUSA, CFCLIENTE
FK: CODCAUSA REFERENCES CAUSA
Nella causa CODCAUSA, l’avvocato CFAVV difende il cliente CFCLIENTE .
Scrivere in SQL le seguenti interrogazioni:
a) Selezionare i clienti che, in ogni causa da loro intrapresa, sono stati difesi sempre dallo stesso
avvocato;
b) Selezionare gli avvocati che hanno partecipato a tutte le cause di tipo “penale”;
c) Selezionare gli avvocati che hanno partecipato ad almeno cinque cause di tipo “penale” e che
in queste cause hanno difeso sempre lo stesso cliente;
Scrivere in algebra relazionale l’interrogazione b).
Esercizio 14
Dato il seguente schema di relazione:
PROGETTI(ANNO,PROGETTO,CAPOPROGETTO,REPARTO,RESPONSABILE)
e considerando le seguenti dipendenze funzionali:
· ANNO, PROGETTO  CAPOPROGETTO
· CAPOPROGETTO  REPARTO
· REPARTO  RESPONSABILE
· ANNO, PROGETTO  RESPONSABILE
Viene richiesto di
a. Determinare la chiave o le chiavi dello schema di relazione.
b. Determinare se lo schema di relazione è in 2NF, 3NF.
c. Produrre uno schema E/R che descriva lo schema di relazione e soddisfi le dipendenze
funzionali date.
Esercizio 15
Un sistema informativo deve gestire le gare di Coppa del Mondo di Sci, secondo le seguenti
specifiche.
Gli atleti sono individuati da un numero di tessera FIS e hanno cognome, nome, luogo e data di
nascita, nazionalità, sesso. Le gare hanno un luogo, una data, un nome della pista, un tipo
(SlalomFemminile, SlalomMaschile, GiganteFemminile, ... , LiberaMaschile). In una certa data,
non si possono svolgere due gare dello stesso tipo; in una certo luogo, non si possono svolgere
due gare dello stesso tipo. Gli atleti sono raggruppati in squadre nazionali, rispettivamente
maschili e femminili, e ogni squadra ha un allenatore, che è individuato con numero di tessera
FIS e ha cognome, nome, luogo e data di nascita, nazionalità. Un allenatore allena un’unica
squadra. Ogni gara ha un tracciatore, che è un allenatore; per le gare in due manche il tracciatore
è diverso per ogni manche.
Per ogni partecipazione di un atleta a una gara si registra la posizione di arrivo ed il tempo finale;
per le gare in due manche si registra anche il tempo di prima manche.
Progettare lo schema E/R e tradurlo in schema relazionale.
Esercizio 16
Dato il seguente schema E/R, dove nello schema è presente l’attributo derivato TOTALE-POSTI
che, per un certo treno, è calcolato come la somma, estesa a tutti i tipi di carrozze che formano il
treno, del prodotto POSTI-CARROZZA per il NUMERO-CARROZZE.
Scrivere in SQL le seguenti interrogazioni:
a. Dato il codice di un tipo di carrozza (COD-C) , incrementarne di10 il numero di posti (con
conseguente incremento del numero di posti totali di tutti i treni che contengono il tipo di
carrozza in questione);
b. Dato il codice di un treno, visualizzarne tutti i suoi dati.
Esercizio 17
Sia dato il seguente schema relazionale:
QUADRO(CQ, AUTORE,PERIODO)
MOSTRA(CM, NOME, ANNO, ORGANIZZATORE)
ESPOSIZIONE(CM, CQ, SALA)
FK: CM REFERENCES MOSTRA
FK: CQ REFERENCES QUADRO
Nella mostra CM, il quadro CQ è stato esposto in una certa SALA.
Scrivere in SQL le seguenti interrogazioni:
a) selezionare tutti i dati sulle mostre dove è stato esposto un quadro di Picasso nel 97 oppure
nel 96;
b) selezionare il nome della mostra nella quale sono stati esposti tutti i quadri di Picasso;
c) selezionare
le
quaterne
(ANNO,
NOMEMOSTRA1,
NOMEMOSTRA2,
ORGANIZZATORE) tali che nello stesso ANNO le mostre con nome NOMEMOSTRA1 e
NOMEMOSTRA2 hanno avuto lo stesso ORGANIZZATORE;
Scrivere in algebra relazionale l’interrogazione b).
Esercizio 18
Un sistema informativo per la gestione di uno studio legale memorizza informazioni relative ai
clienti, alle cause e agli avvocati secondo le seguenti specifiche.
In ogni causa, individuata da un codice univoco e descritta da un tipo e da una data, vengono
riportati i clienti e i relativi avvocati, con i seguenti vincoli:
– in una certa causa, un avvocato può partecipare al massimo una volta, cioè può difendere al
massimo un cliente; per questo compito, l'avvocato percepisce una certa parcella;
– in una certa causa, un cliente può partecipare una sola volta, in un determinato ruolo, ed è
quindi difeso da un unico avvocato.
Per ciascuna causa vengono rilasciati e memorizzati una serie di documenti; un documento,
identificato all'interno della causa da un numero progressivo, viene redatto da una segretaria e
può essere semplice oppure composto da uno a dieci documenti.
I clienti, gli avvocati e le segretarie sono identificati dal codice fiscale e descritti dagli usuali
dati anagrafici.
Progettare lo schema E/R e tradurlo in schema relazionale.
Esercizio 19
Sia dato il seguente schema relazionale:
BANCOMAT(CODBANC, CLIENTE)
PRELIEVO(CODBANC, NUMPREL, SPORTELLO, DATA, IMPORTO)
Scrivere in SQL le seguenti interrogazioni:
a) Selezionare le coppie (CODBANC-1,CODBANC-2) di bancomat dello stesso cliente.
b) Selezionare i clienti che non hanno effettuato nessun prelievo di importo pari a 500.000.
c) Selezionare i clienti che, tramite tutte le loro carte bancomat, hanno effettuato prelievi
sempre presso lo stesso sportello.
d) Selezionare, per ogni sportello, la data in cui sono stati effettuati il maggior numero di
prelievi, considerando solo i prelievi con importo maggiore di 400.000.
Scrivere in algebra relazionale l’interrogazione b).
Esercizio 20
Dato il seguente schema E/R:
si consideri la seguente traduzione in schema relazionale:
SQUADRA(CODSQUADRA, REGIONE)
STADIO(CODSTADIO, CITTÀ)
PARTITA(CODSQUADRA, DATA, NOMECAPITANO, CODSTADIO)
e si consideri sullo schema di relazione PARTITA la seguente dipendenza funzionale aggiuntiva
(che esprime il vincolo che un capitano gioca in una sola squadra):
NOMECAPITANO  CODSQUADRA
Viene richiesto di
a. Determinare la chiave o le chiavi dello schema di relazione PARTITA, prendendo in
considerazione sia i vincoli dello schema E/R sia la dipendenza funzionale aggiunta.
b. Determinare se lo schema di relazione PARTITA è in 2NF, 3NF.
Esercizio 21
Un sistema informativo memorizza dati sulle sale cinematografiche e sui film, secondo le
seguenti specifiche.
Per ogni sala cinematografica viene riportato il nome, la città, l'indirizzo e il numero dei posti; il
nome di una sala è univoco solo all'interno della rispettiva città.
La programmazione dei film nelle sale cinematografiche è organizzata settimanalmente: per ogni
settimana e per ogni sala occorre indicare il singolo film in programma.
Per i film vengono indicati il titolo (univoco), il genere, il regista e gli eventuali premi vinti con
il relativo anno in cui tale premio è stato vinto. Per ogni film occorre riportare da 3 a 6 attori
protagonisti, con i rispettivi personaggi interpretati: in un dato film, un attore può interpretare più
di un personaggio. Attori e registi sono descritti dal codice fiscale e dal nome; per gli attori si
riporta anche la nazionalità, mentre per i registi viene indicata l'età. Un attore può anche essere
regista.
Progettare lo schema E/R e tradur lo in schema relazionale.
Esercizio 22
Sia dato il seguente schema relazionale:
CALCOLATORE(CODC, DESCRIZIONE, MARCA)
RIPARAZIONE(DATA, CODC, GUASTO, COSTO)
FK: CODC REFERENCES CALCOLATORE
Nella DATA specificata, è stato fatta una riparazione sul calcolatore CODC, con spesa pari a
COSTO, per riparare il malfunzionamento riportato in GUASTO.
Scrivere in SQL le seguenti interrogazioni:
a. selezionare i dati dei calcolatori di marca “IBM” che hanno subito delle riparazioni con
costo superiore a 100.000;
b. selezionare i dati dei calcolatori che non hanno subito nessuna riparazione con costo
superiore a 200.000;
c. selezionare i dati dei calcolatori di marca “IBM” che hanno subito delle riparazioni
esclusivamente per malfunzionamento al disco rigido (segnalato con GUASTO=“HD”);
d. selezionare i dati dei calcolatori di marca “IBM” che hanno subito almeno tre riparazioni;
e. selezionare, per ogni marca di calcolatore, il guasto che ha causato il maggior numero di
riparazioni (se uno stesso guasto si verifica più volte sullo stesso calcolatore deve essere
conteggiato una sola volta).
Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 23
Un comune vuole memorizzare informazioni sulla planimetria e servizi della città secondo le
seguenti specifiche.
Le vie di comunicazione hanno un nome e, se sono strade una carreggiata in metri e una
lunghezza in metri, se invece sono piazze, un'area in metri quadrati.
In un incrocio, identificato da un nome, confluiscono due o più vie di comunicazione. Deve
essere rappresentato il numero di volte che due vie di comunicazione si incrociano. Gli edifici
storici hanno un nome, un'età e una cubatura (in metri cubi) e sono delimitati da una o più vie di
comunicazione. Un ufficio pubblico ha un nome e un indirizzo e può essere situato dentro un
edificio storico; inoltre è aperto al pubblico in determinati giorni della settimana, in una fascia
oraria mattutina e/o pomeridiana.
Gli autobus, identificati da un codice, effettuano una o più fermate; ogni fermata appartiene ad
una ed una sola via di comunicazione ed è identificata da un nome univoco all’interno della via
di comunicazione. Una fermata viene effettuata da uno o più autobus; per ogni autobus si devono
indicare come capolinea due fermate, non necessariamente distinte. Gli autobus possono non
effettuare il servizio in alcuni giorni della settimana.
Progettare lo schema E/R e tradurlo in schema relazionale.
Esercizio 24
Sia dato il seguente schema relazionale:
T(CF, INDIRIZZO, QUALIFICA, COSTO-ORARIO)
(tecnico )
PC(PC, NOME, TIPO, NOMEPROPRIETARIO)
(personalcomputer )
RG(DATA, CF, PC, ORE)
(riparazionegiornaliera )
FK: CF REFERENCES T
FK: PC REFERENCES PC
Nella DATA specificata, il tecnico CF ha riparato il personal computer PC impiegando un certo
numero di ORE
Scrivere in SQL le seguenti interrogazioni:
a) selezionare i PC di tipo “Mac” che non sono stati riparati tra il 1/7/97 e il 1/11/97;
b) selezionare i dati dei tecnici che hanno riparato tutti i PC di tipo “Mac”;
c) selezionare le coppie (CP1, CP2) tali che i PC con codice CP1 e CP2 sono stati riparati nella
stessa data dallo stesso tecnico;
d) selezionare tutti i dati dei PC che hanno richiesto almeno 10 ore di riparazione;
e) selezionare, per ogni data, il tecnico che ha riparato il maggior numero di PC.
f) selezionare il PC che ha totalizzato il maggio costo di riparazione, considerando le ore di
riparazione e il relativo costo orario.
Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 25
Sia dato il seguente schema relazionale:
TRENO(CODICETRENO, PARTENZA, DESTINAZIONE, TOTALEPOSTI, TIPOLOCOMOTIVA, VELOCITAMAX)
COMPOSIZIONETRENO(CODICETRENO, TIPOCARROZZA, NUMEROCARROZZE, NUMEROPOSTICARROZZA)
FK: CODICETRENO REFERENCES TRENO
Nel treno viene riportato il tipo della locomotiva (TIPOLOCOMOTIVA): locomotive dello stesso tipo
hanno la stessa velocità massima (VELOCITAMAX).
Nella composizione di un certo treno, individuato da CODICETRENO, partecipano varie tipologie di
carrozze (TIPOCARROZZA) ognuna in un numero dato (NUMEROCARROZZE). Ogni tipo di carrozza
ha un certo NUMEROPOSTICARROZZA:
considerando tale numero e il NUMEROCARROZZE viene determinato, nella relazione TRENO, il
TOTALEPOSTI del treno CODICETRENO.
Esempio: il treno “845-Conca D'Oro” da Milano a Palermo ha 5 carrozze di tipo “normale”,
ciascuna con 60 posti, e 6 carrozze “letto”, ciascuna con 40 posti, per un totale di 540 posti. La
locomotiva del treno è del tipo “E-444-R” con velocità massima di 200 Km/h.
Viene richiesto di descrivere tutte le dipendenze funzionali presenti nello schema e di effettuare
la relativa normalizzazione.
Esercizio 1)
Si vogliono rappresentare informazioni relative ad una catena di montaggio per autoveicoli.
Ogni modello di autoveicolo ha un nome univoco e viene assemblato con una o più fasi di
montaggio.
Le fasi di montaggio sono distinte in automatiche, semiautomatiche e manuali; ogni fase di
montaggio ha un nome
e fa parte dell'assemblaggio di un solo autoveicolo.
I dipendenti che lavorano alla catena di montaggio sono descritti tramite il codice fiscale e il
nome; i dipendenti
sono partizionati in operai e tecnici.
Una fase di montaggio manuale è eseguita da una e una sola squadra; una squadra può eseguire
fino a 2 fasi di
montaggio manuale. Ogni squadra è composta da almeno 2 e al più 4 operai ed è capeggiata da
esattamente un
tecnico. Ogni operaio fa parte di una e una sola squadra; un tecnico può capeggiare una sola
squadra.
Una fase di montaggio semiautomatica è eseguita da uno o più dipendenti tramite l’uso di
macchinari: durante una
fase di montaggio semiautomatica un dipendente utilizza uno o più macchinari per un certo
numero di ore. Un
macchinario ha un codice, un nome ed una descrizione.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
AUTO( CODAUTO ,NOMECOSTRUTTORE,CARATTERISTICHE)
ACCESSORIO( CODACC ,DESCRIZIONE,PREZZO)
INSTALLABILE( CODACC,CODAUTO )
FK: CODACC REFERENCES ACCESSORIO
FK: CODAUTO REFERENCES AUTO
L’accessorio CODACC è installabile sull’auto CODAUTO.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare i dati relativi agli accessori installabili su almeno un’auto “Fiat” (con
costruttore di nome “Fiat”);
b ) selezionare i dati relativi agli accessori che non sono installabili su nessuna auto “Fiat”;
c ) selezionare i dati relativi agli accessori installabili su tutte le auto “Fiat”;
d ) selezionare i dati relativi agli accessori installabili esclusivamente sulle auto “Fiat”;
e ) selezionare i dati relativi all’accessorio più costoso installabile su un auto “Fiat”.
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
3 ) (f a c o l t a t i v o ) Scrivere in algebra relazionale le interrogazione c) e d).
Esercizio 1)
Si vuole automatizzare il sistema di gestione di una impresa di costruzioni edili secondo le
seguenti
specifiche:
I collaboratori dell'impresa hanno un codice fiscale (CF), un nome e un indirizzo; tra i
collaboratori, si
distinguono i salariati, che hanno un salario, e i lavoratori a cottimo, che percepiscono un
compenso; nessun
salariato può lavorare a cottimo.
Ogni opera eseguita dall'impresa ha un codice, un costo, una data di inizio e una data di fine, e
un unico
direttore dei lavori, che è un salariato dell'impresa. Uno stesso codice può essere assegnato a due
opere
diverse solo se le opere sono commissionate da committenti diversi.
Ogni committente ha un nome univoco e può privato o pubblico; i committenti privati hanno una
modalità di
pagamento, che può variare da opera ad opera; per ogni committente pubblico e opera da esso
commissionata, esiste una scadenza di appalto e un importo di appalto.
Le opere eseguite dall'impresa sono costruzioni di fabbricati nuovi oppure restauri di fabbricati
esistenti. I
nuovi fabbricati hanno un unico progettista che è un collaboratore dell'impresa, ma non è né un
salariato né
un lavoratore a cottimo.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3) Scrivere in SQL le seguenti interrogazioni:
a) selezionare i CF dei committenti privati che hanno commissionate opere di costo pari a 5
miliardi;
b) selezionare il nome dei salariati che non hanno diretto alcun opera;
c) selezionare, per ogni collaborator, l'importo medio di appalto delle opere progettate (contando
solo
le opere commissionate da un ente pubblico);
c) selezionare il secondo maggiore costo tra tutte le opere;
4) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1)
Il sistema informativo di una azienda di trasporti pubblici memorizza dati sui dipendenti, le linee
di
trasporto e le vie di comunicazione servite, secondo le seguenti specifiche. I dipendenti hanno un
nome, un cognome, una età e un codice fiscale. Sono divisi in autisti, controllori e personale
amministrativo. Le linee di trasporto hanno un numero univoco. Le vie di comunicazione hanno
un
nome, univoco, una lunghezza e una carreggiata. Per gli autisti, occorre memorizzare la linea su
cui
attualmente sono impiegati. Per i controllori, le linee su cui sono impiegati e il numero di multe
registrate. Per il personale amministrativo, occorre memorizzare il rapporto di subordine tra essi:
un
dipendente del settore amministrativo è diretto da al più un altro amministrativo, ciascuno di essi
può
dirigere più di un dipendente del settore amministrativo. Occorre inoltre memorizzare le vie di
comunicazione percorse da ciascuna linea di trasporto.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3) Scrivere in SQL le seguenti interrogazioni:
a) selezionare le vie di comunicazione percorse dai controllori che hanno registrato più di 500
multe.
b) selezionare le linee sulle quali non è stata effettuata nessuna multa.
c) selezionare la lunghezza totale del percorso coperto da un autista dato.
d) selezionare le linee sulle quali sono state effettuate il maggior numero di multe.
4) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 2)
Sono date le seguenti relazioni
IMPIEGATO(CF, NOME, STIPENDIO, RUOLO, COD_DIPAR)
ordinata su TIPO con 10000 records
CF ha 10000 valori distinti (di 4 bytes)
NOME ha 10000 valori distinti (di 8 bytes)
STIPENDIO ha valori da 30 a 250 (di 2 bytes)
RUOLO ha 20 valori distinti (di 20 bytes)
COD_DIPAR ha 4000 valori distinti (di 15 bytes)
Esistono gli indici su: CF, RUOLO, COD_DIPAR
DIPARTIMENTO(COD_DIPAR, NOME, CITTÀ, PROVINCIA, NUM_DIPEND)
non ordinata, con 4000 records
COD_DIPAR ha 4000 valori distinti (di 15 bytes)
NOME assume valori di 30 bytes
CITTÀ assume valori di 20 bytes
PROVINCIA ha 10 valori distinti (di 2 bytes)
NUM_DIPEND assume valori da 5 a 70 (di 2 bytes)
Esistono gli indici su: COD_DIPAR, NUM_DIPEND
Gli indici (B+tree) sono con tids ordinate.
Assumendo che una pagina di relazione contenga un numero intero di records si calcolino le
dimensioni delle relazioni. Assumendo che una foglia di indice sia mediamente riempita al 70%,
senza
il vincolo di un numero intero di gruppi <chiave, tids>, si calcolino i numeri di foglie degli
indici. Sia
assuma 512 come dimensione delle pagine e 4 come dimensione delle tids.
Scrivere la query in SQL che seleziona gli impiegati (CF, NOME) che guadagnano meno di 50
milioni
nel ruolo di segretario dei dipartimenti della provincia di Roma e Caserta con più di 30
dipendenti.
Ottimizzare la query per il metodo nested loop.
Esercizio 1)
Una azienda di assistenza tecnica per Personal Computer vuole memorizzare
informazioni sulle riparazioni secondo le seguenti specifiche.
Le riparazioni riguardano i componenti hardware dei PC; per ogni tipo di componente
viene memorizzato il nome (univoco), il costo e una breve descrizione. Ogni PC è
identificato da un codice ed ha un proprietario, descritto tramite il codice fiscale,
l'indirizzo e il recapito telefonico.
L’assistenza tecnica per la riparazione dei PC avviene memorizzando un resoconto
orario organizzato nel seguente modo: in una certa ora di un certo giorno, un tecnico
dell’azienda opera su un unico PC riparando uno o più guasti e sostituendo zero o più
componenti; si assuma che in una certa ora di un certo giorno, su un PC operi un solo
tecnico. Un tecnico è descritto tramite il codice fiscale, l'indirizzo, la qualifica e il costo
orario.
Ogni tecnico stila mensilmente una relazione nella quale riporta il numero totale di ore
che in quel mese ha dedicato alla riparazione di PC e i tipi di componenti (fino ad un
massimo di 5) più sostituiti, con il relativo numero di sostituzioni.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
DIPENDENTE( CF ,NOME,CITTÀ)
PROGETTO( CP ,NOME,ANNO,DURATA)
LAVORA( CP,CF ,MESI,RUOLO)
FK: CP REFERENCES PROGETTO
FK: CF REFERENCES DIPENDENTE
Nel progetto CP, il dipendente CF lavora per un certo numero di MESI, svolgendo un certo
RUOLO.
Viene richiesto di:
1) scrivere in SQL le seguenti interrogazioni:
a) selezionare i dipendenti di Modena che non hanno lavorato in alcun progetto
dell’anno 1995;
b) selezionare tutti i dati dei dipendenti che non hanno mai lavorato insieme ad un
dipendente di Modena, cioè nello stesso progetto in cui lavorava anche un
dipendente di Modena;
c) selezionare le coppie (CF1, CF2) tali che i dipendenti con codice fiscale CF1 e CF2
hanno lavorato nello stesso progetto;
d) selezionare tutti i dati dei dipendenti che hanno lavorato in almeno 3 ruoli distinti;
e) selezionare, per ogni dipendente, il progetto in cui esso ha lavorato il maggior
numero di mesi.
2) Scrivere in algebra relazionale le interrogazioni a) e b).
Prof. Sonia Bergamaschi
Esercizio 1)
Una società distributrice di Compact Disk vuole memorizzare dati sulla vendita dei CD , sui
negozi e
sui clienti secondo le seguenti specifiche.
Per i CD viene riportato un codice univoco, il titolo e la casa discografica; i CD sono partizionati
in
singoli, per i quali viene rappresentato solo l'autore, e in compilation, dei quali vengono descritti,
per
ognuno dei dieci brani in esso contenuto, il titolo e l'autore; il titolo del brano in una compilation
è
identificativo.
Ogni negozio, identificato da un codice e descritto da un nome, stila mensilmente una classifica
delle
vendite dei CD nella quale riporta per i CD più venduti (fino ad un massimo di venti CD) il
relativo
numero di vendite.
Infine vengono memorizzate informazioni dettagliate sull'acquisto di CD da parte dei clienti: in
una
certa data, un cliente acquista presso un negozio una certa quantità di un CD. Del cliente si
memorizza il
suo numero di tessera (univoco) e l'indirizzo.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
CICLISTICA(NOMECICLISTA, NAZIONALITÀ, ETÀ);
GARA(NOMECORSA, ANNO, PARTENZA, ARRIVO);
edizione di un certo ANNO della corsa ciclistica NOMECORSA; PARTENZA e
ARRIVO sono rispettivamente la città di partenza e di arrivo.
PARTECIPA(NOMECORSA, ANNO, NOMECICLISTA, POSIZIONE);
FK: NOMECORSA, ANNO REFERENCES GARA
FK: NOMECICLISTA REFERENCES CICLISTICA
Il ciclista NOMECICLISTA ha partecipato all’edizione di un certo ANNO della
corsa ciclistica NOMECORSA classificandosi in una certa POSIZIONE o p p u r e
ritirandosi (POSIZIONE=‘ritirato’).
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare i ciclisti che si sono classificati in prima posizione in u n a
gara ciclistica partita da Milano.
b ) selezionare il nome dei ciclisti che non si sono mai ritirati al Giro
d’Italia (corsa con nome Giro d’Italia).
c ) selezionare le corse per le quali in ogni edizione c’è stato almeno u n
ciclista ritirato.
d ) selezionare, per ogni corsa ciclistica, l’anno in cui c’è stato il maggior
numero di ciclisti ritirati.
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1)
Una società petrolifera vuole memorizzare dati sulle stazioni di servizio e la
distribuzione di carburanti secondo le seguenti specifiche.
Ogni rifornimento giornaliero fa riferimento ad una autobotte e ad un autista che la
guida: in una certa data un autista non può effettuare più di un rifornimento e una
autobotte non può essere impegnata in più di un rifornimento. In ogni rifornimento
vengono trasportati vari tipi di carburante, per ciascuno dei quali si deve memorizzare la
relativa quantità trasportata (ad esempio, un certo rifornimento trasporta 10 tonnellate
di “BenzinaVerde”, 30 tonnellate di “BenzinaSuper”, e così via).
Ciascun rifornimento rifornisce una o più stazione di servizio, scaricando in ciascuna di
esse uno o più dei tipi di carburante trasportati in una certa quantità; (ad esempio, il
precedente rifornimento scarica nella stazione “Pioppa” 3 tonnellate di “BenzinaVerde” e
5 di “BenzinaSuper”, nella stazione “ModenaOvest” 4 tonnellate di “BenzinaVerde” e 5 di
“BenzinaSuper”, e così via).
Ciascuna stazione di servizio memorizza giornalmente, per ciascun tipo di carburante, la
quantità disponibile. In una stazione di servizio lavorano uno o più gestori, un gestore
lavora in una sola stazione.
Il personale della società petrolifera, che comprende i gestori e gli autisti, è rappresentato
con gli usuali dati anagrafici.
Le autobotti atte alla distribuzione sono identificate da una targa ed hanno una
descrizione.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Sia dato il seguente schema relazionale:
PRODOTTO(CODPROD,DESCRIZIONE,PREZZO);
ORDINE(CODORD,NOMECLIENTE,DATA);
L’ordine CODORD è effettuato dal cliente NOMECLIENTE in una certa DATA.
DETTAGLIO-ORDINE(CODORD,CODPROD, QUANTITÀ);
FK: CODORD REFERENCES ORDINE
FK: CODPROD REFERENCES PRODOTTO
Il prodotto CODPROD è presente nell’ordine CODORD in una certa QUANTITÀ.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) selezionare i clienti che non hanno mai ordinato un prodotto con prezzo superiore a 50.000 in
una
quantità maggiore di 10.
b) selezionare i prodotti che non sono mai stati ordinati assieme (cioè presenti nello stesso
ORDINE) ad
un prodotto con descrizione “Indumento”;
c ) selezionare i clienti che hanno effettuato un’ordine comprendente almeno 5 prodotti di
differente
descrizione;
d) selezionare il cliente che ha effettuato l’ordine con il massimo importo complessivo,
considerado sia il
PREZZO che la QUANTITÀ di tutti i prodotti compresi nell’ordinazione.
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1)
Si vogliono rappresentare informazioni relative alle offerte ed agli sconti praticati da una
cooperativa di vendita ai
propri soci.
La cooperativa è costituita da un certo numero di centri di vendita, descritti da un codice e dal
rispettivo indirizzo; i
centri di vendita sono suddivisi in ipermarket , caratterizzati dal numero delle casse, e
minimarket, caratterizzati
dal numero dei reparti.
In una certa data, un socio (descritto dal codice fiscale, dal nome e dal telefono) sottoscrive
l'iscrizione alla
cooperativa, in un preciso centro di vendita.
I prodotti venduti nei centri di vendita sono caratterizzati da un codice, dal prezzo e da un
descrizione.
I minimarket offrono alcuni prodotti scontati di una certa percentuale; per un dato prodotto, la
percentuale di
sconto varia da minimarket a minimarket e da mese a mese.
La cooperativa effettua delle offerte che possono essere ritirate dai soci nei vari supermarket;
un’offerta è descritta
da un codice, dal prezzo e dal periodo di validità ed è costituita da uno o più prodotti, in una
determinata quantità.
Occorre memorizzare l’ipermarket in cui un socio ritira eventualmente una certa offerta (tale
supermarket non è
necessariamente il punto vendita in cui il socio ha sottoscritto l'iscrizione); un socio non può
usufruire due volte
della stessa offerta. Infine, occorre memorizzare, per ogni ipermarket , le dieci offerte più
vendute con il relativo
numero di vendite.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
MANIFESTAZIONE( CM ,NOME)
LUOGO( NOME-LUOGO , INDIRIZZO, CITTÀ)
SPETTACOLO( CM,NUM ,ORA-INIZIO,NOME-LUOGO,DATA)
FK: CM REFERENCES MANIFESTAZIONE
FK: NOME-LUOGO REFERENCES MANIFESTAZIONE
NUM è il numero progressivo dello spettacolo all’interno della manifestazione CM.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare il codice e il nome delle manifestazioni che non hanno interessato
luoghi della città di Modena.
b ) selezionare i nomi dei luoghi che hanno ospitato tutte le manifestazioni, cioè c h e
hanno ospitato almeno uno spettacolo di ciascuna manifestazione..
c ) selezionare il nome dei luoghi che, in una certa data, ospitano più di t r e
spettacoli con inizio alle ore 15.
d ) selezionare, per ogni luogo, il numero totale delle manifestazioni e il numero
totale degli spettacoli ospitati.
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
3 ) Descrivere sinteticamente a parole e riportare in SQL l'interrogazione descritta d a l l a
seguente espressione dell'algebra relazionale:
Esercizio 1) punti 10
Un’azienda di trasporti pubblici, al fine di migliorare il servizio, si vuole dotare di un
sistema informativo di monitoraggio dei ritardi con le seguenti specifiche.
Innanzitutto, un autobus è associato all’autista che lo conduce tramite il concetto di "guida":
in una certa data un’autista può effettuare un’unica guida, mentre un autobus può essere
impegnato in una o più guide.
Un autobus è identificato da un codice e descritto dal numero posti e da un tipo; un autista è
descritto tramite gli usuali dati anagrafici.
Una fermata è identificata da un codice e descritta dalla via in cui è situata e da un tipo; una
linea è identificata da un numero e descritta da un tipo e dalla lunghezza complessiva.
Una linea effettua un certo numero di fermate; una linea non può effettuare due volte la
stessa fermata. Per ciascuna delle fermate effettuate da una certa linea si memorizzano i
seguenti orari prestabiliti giornalieri: l’orario feriale, orario del sabato, orario festivo e
orario in caso di sciopero. Ogni orario è costituito da un elenco di tempi di passaggio
ORA:MINUTO. Per ciascuno di questi tempi di passaggio, giornalmente vengono memorizzati
sia l'autista che l'autobus che hanno effettuato la fermata e il ritardo riportato rispetto
all’orario prestabilito.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2) punti 10
Sia dato il seguente schema relazionale:
P( IMMOBILE,DATA ,PREZZO)
PREZZO dell’IMMOBILE nella DATA specificata
G( IMMOBILE,DATA,GIORNALE ,PAGINA)
FK : IMMOBILE,DATA REFERENCES P
PAGINA del GIORNALE nella quale è pubblicizzato l’IMMOBILE nella DATA specificata
Viene richiesto di:
1) scrivere in SQL le seguenti interrogazioni:
a) Selezionare gli immobili di prezzo superiore a 50.000 che non sono mai stati pubblicizzati
in terza pagina;
b) Selezionare, per ogni giornale, l'immobile pubblicizzato di prezzo massimo;
c) Selezionare gli immobili il cui prezzo non • strettamente crescente rispetto alla data;
d) Selezionare le coppie di immobili (IMMOBILE1,IMMOBILE2) che, nella stessa data,
sono stati pubblicizzati nello stesso giornale e allo stesso prezzo;
2) Scrivere in algebra relazionale l'interrogazione a).
Esercizio 3) punti 13
Dato il seguente schema di relazione:
R(A,B,C,D,E)
e considerando le seguenti dipendenze funzionali
A,B  C,E
CD
DB
viene richiesto di
1. Determinare la chiave o le chiavi dello schema di relazione.
2. Determinare se lo schema di relazione è in 2NF, 3NF e BCNF.
3. Produrre eventuali decomposizioni e discutere la preservazione dei dati e delle dipendenze
funzionali.
4. Produrre uno schema E/R che descriva lo schema di relazione e soddisfi le dipendenze
funzionali date.
Esercizio 1)
Un sistema informativo memorizza dati su alcuni appartamenti, secondo le seguenti specifiche.
Ogni condominio ha un nome (che lo identifica) e un indirizzo e comprende una o più scale,
ognuna delle quali
comprende un insieme di appartamenti. Ad ogni scala è associato un codice (es: scala “A”) che
la identifica
insieme al nome del condominio. Ogni appartamento è identificato, nel rispettivo condominio,
dalla scala e da
un numero (l’interno).
Ogni condominio ha un amministratore; ogni appartamento ha un proprietario e può avere un
inquilino; per ogni
scala si devono indicare da uno a tre appartamenti i cui inquilini possiedono le chiavi
dell’ascensore.
Amministratori, proprietari ed inquilini sono descritti dal codice fiscale, dal nome e dal
cognome.
Per la parte contabile, è necessario tenere traccia delle spese sostenute dal condominio e dei
pagamenti effettuati
dai proprietari: ogni spesa è associata ad un intero condominio, oppure ad una scala o ad un
singolo
appartamento; ogni pagamento è relativo ad uno e un solo appartamento. Nella base di dati
vengono mantenuti
pagamenti e spese relativi all'esercizio finanziario in corso (di durata annuale) mentre gli esercizi
precedenti
vengono sintetizzati attraverso un singolo valore (il saldo precedente) per ciascun appartamento
che indica il
debito o il credito del proprietario. In ogni istante esiste un saldo attuale per ciascun
appartamento, definito
come somma algebrica del saldo precedente e dei pagamenti (positivi) e delle spese addebitate
(negative).
Viene richiesto di:
1 ) Progettare lo schema E/R.
Esercizio 2)
Sia dato il seguente schema relazionale:
FORNITORE( CODF ,NOMEFORNITORE,CITTÀ)
PRODOTTO( CODP ,DESCRIZIONE,PREZZO)
FORNISCE( ANNO, CODP, CODF , QTY)
FK: CODP REFERENCES PRODOTTO
FK: CODF REFERENCES FORNITORE
Nell’ANNO specificato, il prodotto CODP è stato fornito dal fornitore CODF in quantità QTY.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare i dati dei prodotti che nell’anno 1995 sono stati forniti da almeno u n
fornitore di Modena;
b ) selezionare i dati dei prodotti che non sono stati forniti da nessun fornitore di
Modena;
c ) selezionare i dati dei prodotti che nell’anno 1994 sono stati forniti esclusivamente dai
fornitori di Modena;
d ) selezionare, per ogni anno, la quantità totale dei prodotti forniti dai fornitori di
Modena.
e ) selezionare, per ogni anno, il codice del fornitore che ha fornito in totale la maggiore
quantità di prodotti.
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1)
Un sistema informativo memorizza dati sulle tappe del Giro d’Italia, secondo le seguenti
specifiche.
Per ogni tappa, identificata da un numero intero progressivo, viene riportata la data, la lunghezza
e la città di
partenza. Le tappe si suddividono in tappe in linea e a cronometro; per quelle in linea occorre
riportare anche la
città di arrivo, mentre per quelle a cronometro si deve riportare il tipo di pista.
Un ciclista partecipa ad una o più tappe del Giro; per ciascuna di queste partecipazioni si devono
riportare i
modelli di bicicletta utilizzati con i relativi problemi riscontrati. Un modello di bicicletta è
rappresentato tramite
un codice, un nome, la ditta produttrice e una descrizione.
Per ogni tappa occorre memorizzare una serie di classifiche, ciascuna delle quali ha una
denominazione: ad
esempio, la classifica generale è denominata “MagliaRosa”, quella a punti è denominata
“MagliaCiclamino”. In
una certa classifica, un posto è occupato da un preciso ciclista e, viceversa, in una certa classifica
un ciclista
occupa uno ed un sol posto; ad esempio, nella terza tappa, il ciclista “MarioCipollini” occupa la
quarta posizione
nella classifica della “MagliaRosa” e la prima posizione in quella della “MagliaCiclamino”. I
ciclisti sono
rappresentati tramite un numero univoco, un nome e la squadra di appartenenza.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello schema
l’attributo derivato CostoComplessivoProgetto che, per una certo Progetto, è calcolato come la
somma del prodotto OreLavoro*CostoOrario per ognuno degli impiegati che lavora al progetto.
Si
trascuri l’occupazione di memoria del dato derivato.
Lavora Progetto Impiegato (1,N) (1,N) CodProg
Denominazione
CF
CostoOrario
OreLavoro CostoComplessivoProgetto
operazione 1: Stampa tutti i dati di un progetto, incluso il suo CostoComplessivoProgetto.
operazione 2: Incremento del 10% del costo orario di un Impiegato; nel caso in cui il dato
derivato
CostoComplessivoProgetto sia presente, esso deve essere di conseguenza aggiornato.
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
Impiegato E 100 Oper . 1 I 10/Giorno
Progetto E 50 Oper . 2 I 5/Giorno
Lavora A 1000
VIDEOCASSETTA( CODV ,GENERE)
NOLEGGIO( DATA,CODV ,CLIENTE,NUMEROGIORNI)
FK: CODV REFERENCES VIDEOCASSETTA
Nella DATA specificata, la videocassetta CODV Ë stata noleggiata da CLIENTE, per un periodo
riportato in NUMEROGIORNI.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) selezionare i clienti che non hanno mai noleggiato una videocassetta di genere
“Documentario” per un
periodo maggiore di cinque giorni;
b) selezionare i clienti che hanno noleggiato tutti i generi di videocassette noleggiate dal cliente
“MarioRossi”;
c ) selezionare il cliente che in una stessa data ha noleggiato il maggior numero di videocassette;
d) selezionare, per ogni genere, il cliente che ha noleggiato il maggior numero di videocassette di
quel genere;
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1)
Si vogliono memorizzare dati sulle partite del campionato europeo di calcio, secondo le seguenti
specifiche.
Per le squadre partecipanti è riportato il loro nome (univoco) e il nome dell’allenatore,.
Per ogni partita va riportato il nome delle due squadre, indicate rispettivamente come prima e
seconda
squadra, la data, lo stadio, il numero di spettatori, il nome dell’arbitro ed il risultato (numero di
reti della
prima squadra e numero di reti della seconda squadra). Una squadra non può giocare due partite
nella stessa
data; inoltre, in una certa data, un arbitro può arbitrare al massimo una partita e uno stadio può
ospitare al
massimo una partita. Per ogni partita, vanno rappresentate le eventuali reti realizzate
riportandone il minuto,
inteso come numero intero compreso tra 1 e 120, ipotizzando di non avere due reti nello stesso
minuto, e il
giocatore che ha realizzato la rete. Per ogni partita, vanno rappresentate le eventuali sostituzioni
di giocatori,
riportandone il giocatore sostituito, cioè che esce, il giocatore che lo sostituisce, cioè che entra e
il minuto,
inteso come numero intero compreso tra 1 e 120, in cui avviene la sostituzione; durante una
partita si possono
avere due o più sostituzioni nello stesso minuto, un giocatore può essere sostituito (può uscire)
una ed una
sola volta e può sostituire (può entrare) una ed una sola volta.
Uno stadio è descritto da un codice, dall’indirizzo e dalla capienza massima. Gli arbitri e i
giocatori sono
descritti dagli usuali dati anagrafici (CF, nome, cognome, ...); un giocatore gioca in una ed una
sola squadra.
Per ogni squadra vengono specificati esattamente i 22 giocatori che partecipano al campionato e,
per una certa
partita, vengono specificati i giocatori che costituiscono la formazione iniziale, con i rispettivi
ruoli (il ruolo di
un giocatore può variare da partita in partita).
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3 ) Scrivere in SQL le seguenti interrogazioni:
a) selezionare tutte le informazioni relative agli stadi in cui ha giocato la squadra della Turchia.
b) selezionare nome e cognome degli arbitri che non hanno arbitrato alcuna partita della Turchia.
c) selezionare nome e cognome degli arbitri che hanno arbitrato in tutti gli stadi.
d) selezionare nome e cognome dei giocatori che hanno giocato sempre nello stesso ruolo.
e) selezionare, per ogni squadra, il numero di partite vinte.
4) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1)
Si vogliono rappresentare informazioni relative agli accessori ed ai ricambi per autoveicoli.
Un costruttore di autoveicoli, descritto dal nome (univoco), indirizzo e telefono, realizza modelli
di autoveicolo
e produce ricambi originali. Ogni modello di autoveicolo è identificato da una sigla alfanumerica
e dal
costruttore di autoveicoli che lo realizza. Ogni ricambio originale ha un codice univoco solo
nell'ambito del
produttore di autoveicoli che lo produce.
Un accessorio per autoveicoli ha un codice univoco e una descrizione. Ogni accessorio è
sostitutivo oppure non
sostitutivo. Nel primo caso può sostituire uno o più ricambi originali, nel secondo nessuno. Ogni
ricambio
originale può essere sostituito da un preciso accessorio sostitutivo.
Un accessorio è prodotto da un produttore di accessori ed è fornito da uno o più fornitori ad un
prezzo che varia
da fornitore a fornitore. I fornitori sono caratterizzati da un codice e da un indirizzo; i produttori
di accessori
sono dei fornitori.
Ogni accessorio è installabile su un numero arbitrario di modelli di autoveicolo (almeno uno).
Per ogni modello
di autoveicolo e accessorio installabile su tale autoveicolo, devono essere specificati gli anni di
produzione del
modello che sono compatibili con l'accessorio.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
LEPRE( CODLEP ,ETÀ,PESO,SESSO)
PARTO( CODLEPFEM,DATAPARTO ,CFVETERERINARIO)
FK: CODLEPFEM REFERENCES LEPRE
Parto effettuato dalla lepre CODLEPFEM in data DATAPARTO .
NASCITA( CODLEP, CODLEPMAM,DATA,PESO)
FK: CODLEP REFERENCES LEPRE
FK: CODMAM,DATA REFERENCES PARTO
Nascita della lepre CODLEP dal parto effettuato dalla lepre CODLEPMAM in data
DATAPARTO.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare tutti i dati relativi alla lepri femmine che non hanno effettuato
alcun parto;
b ) selezionare tutti i dati relativi alla lepri femmine che hanno partorito almeno
una lepre di sesso maschile;
c ) selezionare tutti i dati relativi alla lepri femmine che hanno p a r t o r i t o
esclusivamente lepri di sesso maschile;
d ) selezionare tutti i dati relativi alla lepre femmina che ha partorito, in totale, i l
maggior numero di figli.
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
3 ) (f a c o l t a t i v o ) Scrivere in algebra relazionale l’interrogazione c).
Esercizio 1)
Si vogliono rappresentare informazioni relative alla gestione di manifestazioni artistiche durante
l'estate.
Una manifestazione, descritta da un codice e da un nome, consiste di 2 o più spettacoli; ogni
spettacolo è
descritto da un numero univoco all'interno della manifestazione nella quale è inserito e dall'ora di
inizio.
Durante uno spettacolo si esibiscono uno o più artisti (un'artista si può esibire al massimo una
volta durante lo
stesso spettacolo) ricevendo un certo compenso. Un'artista è descritto dal codice SIAE e dal
nome d'arte. Per
ogni artista si deve indicare necessariamente un altro artista che lo sostituisca in caso di
indisponibilità;
un'artista può essere indicato come sostituto di più artisti.
Per ospitare gli spettacoli vengono adibiti opportuni luoghi; un luogo è caratterizzato da un nome
(univoco) e
da un indirizzo. Uno spettacolo è ospitato in un unico luogo; inoltre, in una certa data, un luogo
può ospitare al
massimo 3 spettacoli, sia della stessa manifestazione che di manifestazioni differenti.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3 ) Modificare lo schema relazionale ottenuto in 2. per esprimere i seguenti vincoli:
a) un'artista si può esibire al massimo una volta durante la stessa manifestazione (cioè si può
esibire al
massimo in un solo spettacolo di una certa manifestazione), ricevendo un certo compenso.
b) non si possono svolgere due spettacoli della stessa manifestazione nello stesso luogo.
Esercizio 2)
Sia dato il seguente schema relazionale:
DOCENTE( CFDOC ,NOME, COGNOME)
STUDENTE( CFSTUD ,NOME, COGNOME)
ARGOMENTO( CODARG ,DESCRIZIONE)
LEZIONE( CODARG, DATA, CFDOC,NUMSTUDENTI);
AK: DATA,CFDOC
FK: CODARG REFERENCES ARGOMENTO
FK: CFDOC REFERENCES DOCENTE
dove NUMSTUDENTI è il numero di studenti presenti alla lezione
INTERROGAZIONE( CODARG, DATA, CFSTUD , VOTO);
FK: CODARG, DATA REFERENCES LEZIONE
FK: CFSTUD REFERENCES STUDENTE
Interrogazione e voto relativo dello studente CFSTUD durante una lezione.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare il codice fiscale, il nome ed il cognome degli studenti che non sono
mai stati interrogati su un argomento con descrizione “Fisica”.
b ) selezionare il codice fiscale del docente che ha svolto lezioni su tutti gli
argomenti con descrizione “Fisica”.
c ) selezionare il codice fiscale del docente che ha sempre interrogato, cioè c h e
durante ogni sua lezione ha fatto almeno una interrogazione.
d ) selezionare, per ogni argomento, la media dei voti riportati dagli s t u d e n t i
interrogati sull’argomento, considerando solo gli studenti che sono s t a t i
interrogati almeno tre volte sull’argomento in questione.
e ) selezionare, per ogni studente, il codice fiscale del docente con il quale l o
studente ha effettuato il maggior numero di interrogazioni.
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1 (punti 12)
Un'azienda agricola si vuole dotare di un sistema informativo con le seguenti specifiche.
L'azienda produce frutta di vario tipo e, per ogni tipo, in un certo numero di variet_ a.
Ogni variet_ a di ogni tipo di frutta viene trattata con un certo prodotto, a certe date prestabilite e
secondo certe percentuali di diluizione del prodotto che variano da data a data. I prodotti sono
descritti tramite un codice univoco, un nome, un tipo ed una quantit_ a disponibile. I prodotti
vengono ordinati presso le ditte fornitrici in certe quantit_ a e sono da esse consegnati
accompagnati dalla bolla.
Una bolla _e caratterizzata da un numero progressivo e dalla data di consegna; una bolla pu_o
certi_care la consegna di uno o pi_ u prodotti. Le ditte fornitrice sono descritte tramite una
partita IVA, un nome, un indirizzo ed uno o pi_ u telefoni.
Le piante sono organizzate in _lari: ciascun _lare, identi_cato da un numero univoco, prevede
piante dello stesso tipo e variet_ a; per ciascun _lare deve essere inoltre memorizzato il numero
di irrigazioni richieste con le relative date previste per l'irrigazione.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3) Discutere, sia nello schema E/R che nello schema relazionale, la rappresentazione del
seguente
vincolo: ciascun _lare, prevede piante dello stesso tipo che possono essere anche di variet_ a
diverse.
Esercizio 2 (punti 10)
Sia dato il seguente schema relazionale:
DOCUMENTO(CODDOC,NOME,TIPO)
INDICE(CODDOC,PAROLA,NUMEROOCCORRENZA,PAGINA)
FK: CODDOC REFERENCES DOCUMENTO
Nel documento CODDOC, PAROLA compare pi_ u volte e, per ciascuna occorrenza, _e
riportata la relativa
PAGINA
Viene richiesto di:
1 Scrivere in SQL le seguenti interrogazioni:
a) selezionare la parola che compare in tutti i documenti di tipo didattico;
b) selezionare le parole che non compaiono nello stesso documento e alla stessa pagina in cui
compare la parola \Vacanze";
c) selezionare, per ogni tipo di documento, la parola con frequenza maggiore; se una parola
compare pi_ u volte nello stesso documento, si deve contare una sola volta.
2 Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 3 (punti 11)
Dato il seguente schema di relazione:
Tornei(Torneo,Squadra,Categoria,Capitano)
e considerando i seguenti vincoli
_
Un capitano gioca in una precisa squadra di una precisa categoria;
_
Per ogni squadra e categoria c'_e un solo capitano;
_
Per ogni torneo, una squadra partecipa in una sola categoria;
viene richiesto di
1. Determinare le dipendenze funzionali (non banali) insite nello schema di relazione.
2. Determinare la chiave o le chiavi dello schema di relazione.
3. Determinare se lo schema di relazione _e in 2NF, 3NF e BCNF.
4. Produrre eventuali decomposizioni e discutere la preservazione dei dati e delle dipendenze
funzinali.
Esempio di istanza r dello schema di relazione Tornei che soddisfa i vincoli:
Esercizio 1)
Il sistema informativo di una impresa che produce motocicli memorizza dati su: produzione di
motocicli da
competizione, disputa di gare internazionali e il personale impiegato in tali attività in base alle
seguenti
specifiche:
Ogni modello di motociclo ha un codice, un nome e una cilindrata.
Ogni membro del personale ha: un nome, cognome, una età e un codice univoco; il personale è
diviso in piloti,
meccanici e progettisti: nessuno può fare parte di due categorie. Ogni meccanico è assegnato a
un pilota; un
pilota può avere assegnati al massimo tre meccanici; i piloti hanno un ingaggio; i progettisti
hanno un titolo di
studio. Un progettista può avere progettato uno o più modelli di motocicli; un modello di
motociclo è stato
progettato da uno o più progettisti.
Ogni gara ha un nome (esempio: G.P. d'Italia, di Germania, ecc.), un anno e un autodromo in cui
è disputata.
Una gara non viene disputata sempre nello stesso autodromo, e può essere disputata in più anni,
ma viene
disputata al massimo una volta sola nello stesso anno in un unico autodromo.
Un pilota partecipa ad una gara guidando un preciso modello di motociclo; per ognuna di queste
partecipazioni
si deve memorizzare la posizione iniziale e finale del pilota e zero o più problemi riscontrati
durante la gara. Un
modello di motociclo può essere stato impiegato in una o più gare.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
CAMPO( NOMECAMPO ,TIPO,INDIRIZZO)
TENNISTA( CF ,NOME,NAZIONE)
INCONTRO( CODINC ,NOMECAMPO,GIOCAT1,GIOCAT2,SET1,SET2)
FK: NOMECAMPO REFERENCES CAMPO
FK: GIOCAT1 REFERENCES TENNISTA
FK: GIOCAT2 REFERENCES TENNISTA
Incontro, svolto nel CAMPO specificato, tra i tennisti GIOCAT1 e GIOCAT2: il punteggio è
riportato
indicando in SET1 e SET 2 il numero di set vinti da GIOCAT1 e GIOCAT2 rispettivamente.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare gli incontri che si sono disputati sull'erba, cioè su un campo con t i p o
"erba".
b ) selezionare i campi in erba sui quali non è stato disputato nessun incontro;
c ) selezionare i dati dei tennisti che hanno vinto almeno una partita sull'erba;
d ) selezionare i dati delle nazioni in cui tutti i giocatori hanno sempre vinto le partite
disputate;
e ) selezionare il campo in erba sul quale è stato disputato il maggior numero di
incontri;
2 ) Scrivere in algebra relazionale le interrogazioni a), b), c) e d).
Esercizio 1)
Si vogliono rappresentare informazioni relative ad un allevamento di lepri.
Di una lepre, identificata da un codice, occorre memorizzare il peso e l’età. Le lepri vengono
mensilmente
sottoposte a visite veterinarie: per ciascuna visita occorre riportare la lepre, il medico veterinario
(rappresentato dagli usuali dati anagrafici) che l’ha sostenuta ed il risultato della visita.
Per la riproduzione delle lepri, vengono formate delle coppie costituite da una femmina e un
maschio e tali
coppie non vengono mai cambiate, cioè una femmina (maschio) resta accoppiata sempre allo
stesso maschio
(femmina). Delle varie coppie occorre tener traccia di eventuali parti, memorizzandone la data, il
veterinario
che ha assistito il parto e le lepri nate da tale parto con il rispettivo peso al momento della
nascita.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
SOCIO( CS ,NOME,COGNOME)
PRODOTTO( CP ,DESCRIZIONE)
OFFERTA( CO , VALIDITÀ)
COMPRENDE( CO,CP ,QUANTITÀ)
FK: CO REFERENCES OFFERTA
FK: CP REFERENCES PRODOTTO
QUANTITÀ è il numero di unità del prodotto CP compresi nell’offerta CO
RITIRA( CO,CS ,DATA)
FK: CO REFERENCES OFFERTA
FK: CS REFERENCES SOCIO
DATA è il giorno in cui il socio CS ritira l’offerta CO
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare il codice e la descrizione dei prodotti che non sono mai stati offerti
insieme ad un prodotto con descrizione “Uva”.
b ) selezionare codice, nome e cognome dei soci che non hanno ritirato alcuna
offerta che comprende un prodotto con descrizione “Uva”.
c ) selezionare codice, nome e cognome dei soci che hanno ritirato t u t t e le offerte
che comprendono un prodotto con descrizione “Uva”.
d ) selezionare, per ogni socio, il numero delle offerte ritirate che comprendono u n
prodotto con descrizione “Uva”.
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1 (punti 11)
Un sistema informativo memorizza alcune informazioni su uno zoo, secondo le seguenti
speci_che.
Ogni animale appartiene ad una specie ed _e identi_cato da un codice univoco all'interno della
specie di
appartenenza; di un animale viene inoltre riportato il peso. Una specie _e rappresentata da un
nome univoco,
da un habitat e da una famiglia. Ogni specie _e collocata in una certa zona dello zoo; una zona _e
rappresentata
da un codice univoco, da un'estensione e da un tipo di vegetazione. In una zona pu_o essere
collocato un'unica
specie.
Le visite veterinarie sono organizzate per zone nel seguente modo: per una zona in una certa data
c'_e un
unico veterinario assegnato per le visite; viceversa, in una certa data un veterinario _e assegnato
ad un'unica
zona. Un veterinario _e descritto dagli usuali dati anagra_ci.
Per ogni specie di animale si deve indicare una dieta standard, costituita da uno o pi_ u cibi con
le relative
quantit_ a giornaliere. Un cibo _e descritto da un codice identi_cativo, da un tipo e da una unit_ a
di misura.
Devono essere rappresentate le eventuali limitazioni nella dieta di un singolo animale: in una
certa settimana
un animale pu_o essere privato di uno o pi_ u cibi e per ciascuna di queste privazioni si devono
riportare uno
o pi_ u motivi e il veterinario che l'ha prescritta.
Viene richiesto di:
1. Progettare lo schema E/R.
2. Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2 (punti 8)
Dato il seguente schema di relazione:
R(Corso,NomeDoc,CognomeDoc,Dipartimento,Sede)
e considerando le seguenti dipendenze funzionali:
_ (FD1) Corso  NomeDoc
_ (FD2) NomeDoc,CognomeDoc  Dipartimento
_ (FD3) Corso  CognomeDoc
_ (FD4) Dipartimento  Sede
Viene richiesto di:
1. Determinare la chiave o le chiavi dello schema di relazione;
2. Determinare se lo schema di relazione _e in 2NF, 3NF e BCNF;
3. Produrre eventuali decomposizioni e discutere la preservazione dei dati e delle dipendenze
funzionali.
1
Esercizio 3 (punti 14)
Sia dato il seguente schema relazionale:
TIR(CODTIR,TIPO,ANNO)
LINEA(CODLIN,PARTENZA,ARRIVO)
PERCORRE(DATA,CODTIR,CODLIN,TEMPO)
FK : CODTIR REFERENCES TIR
FK : CODLIN REFERENCES LINEA
Nella DATA speci_cata, il TIR CODTIR percorre la linea CODLIN impiegando un certo
TEMPO.
Viene richiesto di
1. Scrivere in SQL le seguenti interrogazioni:
(a) Selezionare i Tir di tipo \Turbo" che hanno percorso una linea che parte oppure che arriva a
Modena.
(b) Selezionare il codice delle linee che arrivano a Modena e che non sono mai state percorse da
un
Tir di tipo \Turbo".
(c) Selezionare i Tir che hanno percorso tutte le linee che partono da Modena e arrivano a
Palermo.
(d) Selezionare per ogni linea che parte da Modena, il suo codice e il codice del Tir che ha
percorso
tale linea nel tempo pi_ u breve.
2. Scrivere in algebra relazionale le interrogazioni (a), (b) e (c).
Esercizio 1)
Il Consiglio Nazionale delle Ricerche (CNR) vuole memorizzare dati sui progetti di
ricerca secondo le seguenti specifiche.
Ogni progetto ha un codice, un nome, l’anno al quale è relativo ed una durata; per ogni
progetto viene indicato un coordinatore scientifico che è un ricercatore; in un certo
anno un ricercatore può essere coordinatore al massimo di un progetto. Un ricercatore
è rappresentato dagli usuali dati anagrafici e da un indirizzo di email.
Ad ogni progetto partecipano da 1 a 8 ricercatori e per ciascuno di essi viene specificato
il numero di “mesi uomo” e il costo orario; in un certo anno, un ricercatore può
partecipare al massimo a due progetti di ricerca.
Ogni progetto ha come beneficiario un centro di ricerca CNR; viceversa, un centro di
ricerca può essere beneficiario di più progetti. Un centro di ricerca è rappresentato
tramite un codice, un nome ed un indirizzo ed ha un direttore; d’altra parte un direttore
può dirigere un solo centro di ricerca. Per un direttore vengono riportati le stesse
informazioni riportate per i ricercatori con in aggiunta l’anno di nomina a direttore.
Ogni progetto fa riferimento ad una o più discipline: per ognuno di questi riferimenti
occorre riportare da 2 a quattro parole chiave. Le discipline sono raggruppate in
settori: ogni disciplina è identificata da un codice univoco all’interno del settore di
appartenenza ed ha una descrizione; un settore ha un nome (univoco) ed un comitato.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Sia dato il seguente schema relazionale:
QUADRO( CQ ,AUTORE,PERIODO)
MOSTRA( CM , NOME,ANNO,ORGANIZZATORE)
ESPOSIZIONE( CM,CQ ,SALA)
FK: CM REFERENCES MOSTRA
FK: CQ REFERENCES QUADRO
Nella mostra CM, il quadro CQ è stato esposto in una certa SALA.
Viene richiesto di:
1) scrivere in SQL le seguenti interrogazioni:
a) selezionare le sale nelle quali è stato esposto, nell’anno 1997, un quadro di Picasso;
b) selezionare tutti i dati dei quadri di Picasso che non sono mai stati esposti nell’anno
1997;
c) selezionare tutti i dati dei quadri che non sono mai stati esposti insieme ad un
quadro di Picasso, cioè nella stessa mostra in cui compariva anche un quadro di
Picasso;
d) selezionare tutti i dati delle mostre in cui sono stati esposti quadri di almeno 5
autori distinti;
e) selezionare, per ogni mostra, l’autore di cui si esponevano il maggior numero di
quadri.
2) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1) punti 10
Un sistema informativo per la gestione di una compagnia assicurativa memorizza
informazioni relative a polizze, assicurati, rischi assicurati e premi pagati, secondo le
seguenti specifiche.
Ogni polizza ha un numero identificativo, un tipo di rischio coperto, un importo massimo di
copertura assicurativa e un premio annuale (che può variare di anno in anno). Una polizza è
riferita ad un solo assicurato e può coprire uno o più individui. Le polizze di tipo
“assicurazione sulla vita” hanno anche uno o più beneficiari.
Le polizze automobilistiche hanno una tipologia e danno copertura assicurativa ad un
veicolo. Un veicolo può essere coperto da più di una polizza, anche nello stesso anno, però
con il seguente vincolo: in un certo anno, un veicolo può avere una sola polizza di una certa
tipologia; ad esempio, il veicolo XYZ non può avere nell’anno 1998 due polizze di tipologia
“Kasco” .
Gli assicurati, gli individui coperti e i beneficiari di una polizza sono individuati dal
codice fiscale e descritti dagli usuali dati anagrafici.
Viene richiesto di:
1 ) Progettare lo schema E/R.
Esercizio 2) punti 13
Sia dato il seguente schema E/R:
Viene richiesto di:
1 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
2 ) Aggiungere allo schema relazionale ottenuto al punto 1) il vincolo che in una certa Data
lo Stato di Avanzamento di un certo Progetto è unico.
3 ) Tradurre lo schema E/R di figura in un unico schema di relazione.
3.a)Determinare le dipendenze funzionali (non banali) insite nello schema di
relazione;
3.b) Determinare la chiave o le chiavi dello schema di relazione (consiglio: utilizzare
gli assiomi di Armstrong);
3.c)Determinare se lo schema di relazione è in 2NF, 3NF e BCNF.
3.d) Produrre una eventuale decomposizione almeno in 3NF che sia lossless.
Esercizio 3) punti 10
Sia dato il seguente schema relazionale:
ESERCIZIO( CODESER ,TIPO,DIFFICOLTÀ)
SCHEDA( CODSCH ,NOMEISTRUTTORE)
La scheda CODSCH è stata compilata dall’istruttore NOMEISTRUTTORE
PREVEDE( CODSCH,CODESER,DATA )
FK: CODSCH REFERENCES SCHEDA
FK: CODESER REFERENCES ESERCIZIO
La scheda CODSCH prevede che nella DATA specificata venga effettuato l’esercizio
CODESER.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) Selezionare i nomi degli istruttori che non hanno compilato nessuna scheda che
prevede un esercizio di tipo “addominale”.
b) selezionare gli esercizi che non sono mai stati previsti assieme (cioè presenti nella
stessa SCHEDA) ad un esercizio di tipo “addominale”;
c ) Selezionare, per ogni scheda, l’esercizio di difficoltà maggiore;
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1)
Il sistema informativo di una impresa di noleggio di autoveicoli memorizza dati sui dipendenti, i
clienti, gli autoveicoli e le autorimesse, secondo le seguenti specifiche.
Ogni persona ha un nome, cognome, un codice fiscale e un indirizzo; le persone sono o clienti o
dipendenti (si ammette la possibilità che qualcuno possa fare parte di entrambe le categorie).
Ogni
dipendente ha una qualifica; ogni cliente ha una patente di guida, caratterizzata da un numero e
un tipo
(patente A, B, C,...) e dalla città di rilascio.
Gli autoveicoli hanno una targa e un modello; essi sono divisi in veicoli per il trasporto di cose e
veicoli per il trasporto di persone; ogni veicolo per il trasporto di cose ha un carico limite e può
avere
un rimorchio; ogni veicolo per il trasporto di persone ha un numero massimo di posti disponibili.
Gli autoveicoli sono parcheggiati in autorimesse; ogni autorimessa ha un indirizzo (univoco
nell'impresa) e un numero di posti veicolo. Ogni dipendente lavora in una e una sola
autorimessa; in
ogni autorimessa lavora almeno un dipendente.
Gli autoveicoli sono noleggiati ai clienti; ciascun noleggio ha una data di inizio e una data di fine
noleggio (occorre memorizzare anche i noleggi già terminati).
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3) Scrivere in SQL le seguenti interrogazioni:
a) Selezionare il codice fiscale dei clienti che hanno noleggiato autoveicoli di modello 'XYZ';
b) Selezionare le targhe degli autoveicoli che non sono mai stati noleggiati;
c) Selezionare le targhe degli autoveicoli noleggiati da persone che lavorano presso la
autorimessa in cui il veicolo è situato;
d) Selezionare tra gli autoveicoli per il trasporto di persone il modello più noleggiato.
4) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1)
Una società di vendite per corrispondenza si vuole dotare di un sistema informativo con le
seguenti specifiche.
La società pubblica periodicamente un catalogo dei prodotti caratterizzato da un codice, da un
nome e da un prezzo di copertina; ogni prodotto trattato ha un codice, un nome e una
descrizione; in un certo catalogo, un prodotto può comparire un’unica volta; il prezzo di
vendita di uno stesso prodotto può variare da un catalogo all’altro.
Tra i prodotti vi sono i piccoli elettrodomestici (per i quali occorre riportare il numero di
anni di garanzia e il voltaggio di funzionamento) e i capi di abbigliamento (per i quali si deve
riportare la stoffa, i modelli e le taglie). In particolare, le taglie disponibili dipendono dai
modelli: ad esempio, per la camicia CAM123, sono previsti i modelli “ManicaLunga” nelle
taglie 38,40,42,48 e “ManicaCorta” nelle taglie 50 e 52.
Un cliente effettua una richiesta di acquisto facendo riferimento ai prodotti contenuti in un
determinato catalogo, cioè tutti i prodotti compresi in una richiesta devono comparire in uno
stesso catalogo; per ciascuno degli articoli compresi in una richiesta viene specificata la
quantità ed eventuali note (come, ad esempio, il colore desiderato per un vestito). Una
richiesta di acquisto è completata con altre informazioni, quali la data, l’importo complessivo
e la modalità di pagamento. Per ogni richiesta evasa la società memorizza la data di invio
della merce e la modalità di trasporto.
A scopo promozionale, la società estrae a sorte, per ogni catalogo, un cliente e gli assegna un
buono spesa di un determinato importo; un cliente non può essere estratto più di una volta.
Per un cliente si memorizza il CF, il nome, il cognome, l’indirizzo e il numero telefonico.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente
conservare nello schema l’attributo derivato TotaleVenditeArticolo calcolato come la
somma del prodotto Quantità*NumeroVenditeOfferta per ognuna delle offerte in cui
compare l’articolo. Si trascuri l’occupazione di memoria del dato derivato.
In Offerta Articolo (0,N) (10,10)
Quantità
CodOff
Data
CodArt
Nome
TotaleVenditeArticolo NumeroVenditeOfferta
Operazione 1) Dato il codice di un’offerta, incrementare del 10% il numero delle vendite
(incrementando anche l’eventuale dato derivato degli articoli presenti nell’offerta);
Operazione 2) Dato il codice di un articolo, visualizzarne tutti i suoi dati.
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
Offerta E 1000 Oper . 1 I 50/Giorno
Articolo E 100 Oper . 2 I 100/Giorno
Esercizio 3)
Sia dato il seguente schema relazionale:
ARTISTA( COD ,NOME,NAZIONALITÀ)
FILM( TITOLO ,COD-REGISTA,GENERE,INCASSO)
FK: COD-REGISTA REFERENCES ARTISTA
L’artista COD-REGISTA ha diretto il film.
CAST( TITOLO,COD-ATTORE ,RUOLO)
FK: TITOLO REFERENCES FILM
FK: COD-ATTORE REFERENCES ARTISTA
L’artista COD-ATTORE ha partecipato come attore al film TITOLO interpretando un
certo RUOLO.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) selezionare i dati degli artisti che hanno partecipato come attori ai film da essi diretti,
cioè che sono sia registi che nel cast di un certo film;
b) selezionare i dati degli artisti che non hanno partecipato come attore a nessun film di un
regista di nazionalità “americana”;
c) selezionare i dati degli artisti che hanno partecipato come attore a tutti i film di genere
“horror”;
d) selezionare, per ogni attore, il film di maggiore incasso al quale esso ha partecipato,
considerando solo i film di genere “comico”;
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1)
Il sistema informativo di una facoltà memorizza dati sulle attività di studio all'estero degli
studenti,
secondo le seguenti specifiche.
Esistono bandi di concorso, ciascuno dei quali è identificato da un numero intero, dall'anno
accademico e dal paese europeo in cui l’attività di studio deve essere svolta. Ogni bando ha un
unico
professore che ne è responsabile. ed è associato ad almeno un requisito sufficiente per la
partecipazione al concorso; ogni requisito è espresso con l'anno di corso e il corso di laurea o di
dottorato a cui lo studente deve essere iscritto; ogni corso di laurea ha un consiglio composto da
professori e ogni corso di dottorato ha un coordinatore che è un professore.
Ogni bando è relativo ad una esattamente area disciplinare. Le aree disciplinari sono raccolte in
insiemi disgiunti detti settori disciplinari. Ogni area disciplinare ha un codice, univoco all'interno
del
settore disciplinare, e una denominazione. Ogni settore disciplinare ha un codice univoco e una
denominazione.
Per ogni bando di concorso vengono inoltre specificati gli eventuali esami che possono essere
sostenuti dagli studenti durante la loro attività di studio all'estero; ogni esame sostenuto all’estero
ha
una denominazione univoca all’interno di un paese europeo ed una descrizione; per un dato anno
accademico , un esame sostenuto all’estero corrisponde esattamente ad un esame della facoltà
(caratterizzato da un codice ed una descrizione) .
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3) Scrivere in SQL le seguenti interrogazioni:
a) Selezionare i bandi che prevedono almeno un esame da sostenere all’estero;
b) Selezionare i bandi che non prevedono nessun esame da sostenere all’estero;
c) Selezionare i paesi europei in cui si svolgono, nell’anno accademico 1995/96, il maggior
numero di attività di studio;
d) Selezionare il numero totale di borse di studio per anno accademico e paese europeo.
4) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1)
Un sistema informativo deve gestire delle ore di lezione di corsi di specializzazione post-laurea,
secondo le
seguenti specifiche.
Per i docenti, identificati con un codice, si richiede di registrare il nome, cognome, luogo e data
di
nascita, la residenza, l'area disciplinare, il codice fiscale, il livello, il telefono ed eventuali note.
Ogni
corso è rappresentato da un identificatore costituito da un codice assegnato da organi esterni e da
un codice
interno, inoltre possiede un titolo e una data di inizio e di fine (quest’ultima facoltativa).
Un altro soggetto coinvolto è lo studente; per ognuno di essi è richiesta una serie di informazioni
anagrafiche: nome, cognome, luogo e data di nascita, la residenza, il codice fiscale, il titolo di
studio, il
telefono, l’Università di provenienza ed un codice identificativo. Ogni studente può frequentare
più corsi
(anche in anni diversi) e le relative presenze, raccolte mensilmente, vengono registrate sia in
numero di
giorni che in numero di ore effettive. Le ore di lezione svolte dai docenti sono raccolte
mensilmente. Un
docente può svolgere ore di lezioni per diversi corsi, per più mesi e di differenti tipologie. Il
compenso per
le ore di lezione può cambiare rispetto alla tipologia, al mese, al corso e al docente.
Le tipologie di ore sono identificate con un codice e hanno una descrizione ed un coefficiente
che indica
il grado di importanza. Un docente infine può cambiare mensilmente la sua posizione fiscale
(dipendente,
libero professionista, etc.) ma non può avere nello stesso mese due posizioni fiscali diverse.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello
schema l’attributo derivato NUMERO-ISCRITTI che, per una certa facoltà, è calcolato come il
numero
degli studenti iscritti alla facoltà in questione.
Si trascuri l’occupazione di memoria del dato derivato.
ISCRITTO (1,N) (1,1) FACOLTA’ STUDENTE
NUMERO
ISCRITTI
operazione 1: Stampa tutti i dati di una facoltà, incluso il numero di iscritti
operazione 2: Iscrizione di un nuovo studente ad una facoltà già esistente
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
Facoltà E 5 Oper . 1 I 2/Giorno
Studente E 10000 Oper . 2 I 500/Giorno
Esercizio 3)
Sia dato il seguente schema relazionale:
GARA( DATA,TIPO ,LUOGO,NOMEPISTA)
Una gara di sci è identificata dalla DATA in cui si svolge e dal TIPO (SlalomFemminile,
SlalomMaschile, GiganteFemminile, ... , LiberaMaschile);
SCIATORE( TESFIS ,NOME,NAZIONE,SESSO)
PARTECIPA( DATA,TIPO, TESFIS ,POSIZIONE,TEMPO,PARZIALE)
FK: DATA,TIPO REFERENCES GARA
FK: TESFIS REFERENCES SCIATORE
Lo sciatore identificato da TESFIS ha partecipato alla gara identificata da (DATA,TIPO)
classificandosi in una certa POSIZIONE (1,2,3, ...) }
Viene richiesto di:
1) scrivere in SQL le seguenti interrogazioni:
a) selezionare i dati dei sciatori che non hanno mai vinto uno SlalomMaschile;
b) selezionare i dati delle sciatrici che si sono classificate seconde in una gara vinta da Deborah
Compagnoni.
c) selezionare, per ogni sciatore, il tipo di gara a cui ha partecipato il maggior numero di volte;
2) Scrivere in algebra relazionale l’interrogazione a e b).
Esercizio 1 (punti 12)
Progettare un sistema informativo per la gestione di documenti secondo i seguenti requisiti.
I documenti sono rappresentati da un codice univoco, da un titolo, da una data e da un numero di
versione, inoltre possono presentare un abstract (sommario).
Ogni documento _e strutturato in uno o pi_ u capitoli che sono identi_cati da un numero-capitolo
e dal
documento di appartenenza. Di ogni capitolo interessa memorizzare il titolo.
I capitoli sono a loro volta strutturati in uno o pi_ u paragra_ che sono identi_cati, all'interno del
capitolo, da un numero-paragrafo; di ogni paragrafo interessa memorizzare il titolo ed il testo.
Un
paragrafo pu_o contenere altri paragra_ ma non pu_o essere contenuto in paragra_ distinti.
Ciascun capitolo pu_o avere una bibliogra_a costituita da citazioni fatte nel capitolo e che sono
identi_cabili, all'interno del capitolo, con un numero intero positivo. Ciascuna citazione riporta un
testo
e pu_o far riferimento ad un documento.
Ogni documento contiene in_ne un indice analitico nel quale sono elencate una serie di parole
con i
rispettivi numeri di pagina in cui esse compaiono.
Ciascun capitolo _e scritto da almeno un autore di cui interessa registrare il nome e cognome e la
matricola che lo identi_ca.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
3) Discutere, sia nello schema E/R che nello schema relazionale, la rappresentazione del
seguente
vincolo: all'interno di un documento, un autore pu_o scrivere un unico capitolo.
Esercizio 2 (punti 8)
Sia dato il seguente schema relazionale:
ORARIO(TIME,AUTISTA,FERMATA,LINEA,RITARDO)
Nella data, ora e minuto riportati in TIME , l'AUTISTA ha e_ettuato una certa FERMATA di
una certa
LINEA , riportando un determinato RITARDO rispetto all'orario prestabilito
Viene richiesto di:
1 Scrivere in SQL le seguenti interrogazioni:
a) selezionare l'autista che ha e_ettuato con ritardo pari a zero tutte le fermate (per tutte le
fermate si intendono tutte quelle riportate in ORARIO) ;
b) selezionare le coppie di linee (LINEA1,LINEA2) che si incrociano, cio_e per le quali _e
prevista
la stessa fermata;
c) selezionare l'autista pi_ u puntuale, ovvero che ha e_ettuato il massimo numero di fermate con
ritardo pari a zero, considerando solo gli autisti che hanno e_ettuato pi_ u di 10000 fermate;
2 Scrivere in algebra relazionale l'interrogazione a).
Esercizio 3 (punti 8)
Dato il seguente schema di relazione: R(A,B,C,D) e considerando le seguenti dipendenze
funzionali
_ (FD1) A  B
_ (FD2) BC  D
_ (FD3) A  C
viene richiesto di
1. Determinare la chiave o le chiavi dello schema di relazione.
2. Determinare se lo schema di relazione _e in 2NF, 3NF e BCNF.
3. Produrre eventuali decomposizioni e discutere la preservazione dei dati e delle dipendenze
fun-zionali.
Esercizio 1)
Un Comune si vuole dotare di un sistema informatico di ausilio alla gestione delle multe elevate
agli utenti in
seguito ad infrazioni del codice stradale. Un sottoschema della base di dati relativo alla gestione
multe deve
soddisfare le seguenti specifiche.
Per ogni multa emessa in assenza del trasgressore viene redatto un verbale che contiene: un
numero
progressivo, il cognome del vigile che effettua la multa, la targa del veicolo, il luogo, il numero
dell’articolo
stradale dell’infrazione e l’ammontare della multa. Più vigili possono fare una multa con lo
stesso numero
progressivo. Su un veicolo non si possono fare più multe nella stessa ora dello stesso giorno.
Ogni veicolo ha
una targa univoca, un tipo e un proprietario, descritto dagli usuali dati anagrafici. Ogni articolo
del codice
stradale ha un numero univoco, una breve descrizione, la sanzione minima e massima prevista. I
verbali (di
multa) memorizzati contengono un’informazione di stato che assume i seguenti valori: saldato,
inviato, in
attesa, annullato. Un’operazione tipica è quella di spedire un’avviso di multa. L’avviso di multa
che viene
inviato è relativo ad un verbale e contiene le informazioni di riferimento al verbale, la data di
invio e la data di
scadenza. Se il veicolo è stato multato in precedenza è possibile risalire facilmente all’indirizzo
del proprietario
già memorizzato e quindi inviare l’avviso. Quando viene ricevuto il pagamento relativo ad un
verbale, tale
verbale viene messo in stato di saldato e viene memorizzata la data in cui viene saldato. Viene
inoltre conservato
l’archivio dei passaggi di proprietà; per ogni passaggio si memorizza il veicolo, il proprietario
che vende ed il
proprietario che acquista e la data della transazione.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Data la seguente porzione di schema E/R con il seguente volume dei dati e le seguenti
operazioni, decidere se è
conveniente conservare nello schema l’attributo derivato SOMMA-TOTALE-ACQUISTI, che
per un certo
cliente è calcolato come la somma di QUANTITÀ*PREZZO di tutti i prodotti acquistati.
Si trascuri l’occupazione di memoria del dato derivato.
CLIENTE PRODOTTO (1,N) (1,N)
NOME
COD-CLIENTE
ACQUISTI
SOMMA-TOTALE-ACQUISTI
NOME
COD-PRODOTTO
PREZZO
QUANTITÀ
Operazione 1) Dati i codici di un cliente e di un prodotto già esistenti, inserire l’acquisto del
prodotto da parte
del cliente;
Operazione 2) Visualizzare i dati di un cliente;
Operazione 3) Modificare il prezzo di un prodotto;
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
Prodotto E 600 Oper . 1 I 100/Giorno
Cliente E 300 Oper . 2 I 10/Giorno
Acquisti R 12000 Oper . 3 I 1/Giorno
Esercizio3)
Sia dato il seguente schema relazionale:
GARA( CODICEGARA ,NOMECAMPO,LIVELLO)
GIOCATORE-DI-GOLF( CF ,NOME,NAZIONE)
PARTECIPAZIONE( CODICEGARA,CF-GIOCATORE ,PUNTEGGIO)
FK: CODICEGARA REFERENCES GARA
FK: CF-GIOCATORE REFERENCES GIOCATORE-DI-GOLF
Il giocatore CF-GIOCATORE partecipa alla gara CODICEGARA totalizzando un certo
PUNTEGGIO.
Nota: La gara è vinta dal giocatore che totalizza il punteggio più basso .
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare i dati dei giocatori di golf che hanno partecipato ad almeno una gara
disputata a livello “nazionale”;
b ) selezionare le nazioni in cui tutti i giocatori hanno ottenuto un punteggio minore o
uguale a 0 nelle gare disputate ;
c ) selezionare i dati dei giocatori di golf che hanno partecipato a tutte le gare d i s p u t a t e
a livello “internazionale”;
d ) selezionare i dati dei giocatori di golf che hanno vinto almeno una gara disputata a
livello “internazionale”;
e ) selezionare, per ogni nazione che nelle gare di livello “internazionale” ha schierato
più di 5 giocatori distinti, il punteggio medio ottenuto dai giocatori in tali gare; si
ordini il risultato in modo decrescente rispetto al punteggio medio.
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1)
Si vogliono memorizzare dati sulle lezioni organizzate da un ente di formazione
secondo le seguenti specifiche.
Ogni lezione è tenuta da un docente e riguarda un argomento. In una certa data, u n
docente può tenere una ed una sola lezione ed un argomento può essere trattato i n
una ed una sola lezione. Per ogni lezione può essere riportato il numero totale degli
studenti presenti e le interrogazioni fatte agli studenti con il relativo voto; d u r a n t e
una lezione uno studente può essere interrogato al massimo una volta. Gli s t u d e n t i
sono organizzati in gruppi: un gruppo è descritto da un codice e da un nome ed è
costituito da esattamente tre studenti; uno studente appartiene ad uno ed un solo
gruppo. Per un dato argomento, un gruppo può effettuare una ed una sola tesina ed è
seguito in questa attività da un unico docente. Per ogni tesina deve essere riportata l a
data di consegna e zero o più parole chiave.
Viene richiesto di:
1 ) Progettare lo schema E/R.
Esercizio 2)
Sia dato il seguente schema relazionale:
OPERATORE(CODOP,NOME, COGNOME)
ARTICOLO(CODART,DESCRIZIONE)
LOTTO(CODART, CODOP, TOTESEMPLARE);
FK: CODART REFERENCES ARTICOLO
FK: CODOP REFERENCES OPERATORE
dove TOTESEMPLARE è il numero di pezzi dell’articolo CODART.
RECLAMO(CODART, CODOP, NESEMPLARE, NOMECLIENTE);
FK: CODART, CODOP REFERENCES LOTTO
Reclamo effettuato dal cliente NOMECLIENTE sull’esemplare NESEMPLARE del l o t t o
confezionato dall’operatore CODOP relativo all’articolo CODART.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare il codice e il nome degli operatori per i quali non esiste a l c u n
reclamo (cioè per i quali nessun esemplare di nessun lotto da essi
confezionato ha ricevuto un reclamo).
b ) selezionare il codice degli operatori per i quali ogni lotto da essi confezionato
contiene almeno un esemplare al quale si riferisce un reclamo.
c ) selezionare il nome del cliente che ha fatto reclami per tutti gli operatori.
d ) selezionare, per ogni articolo, il codice dell’operatore che ha confezionato il
lotto con il maggior numero di esemplari, senza considerare i lotti con u n
numero di esemplari TOTESEMPLARE non specificato.
e ) selezionare il lotto che ha ricevuto più reclami.
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e c).
Esercizio 1) punti 10
Un sistema informativo memorizza dati su una palestra, secondo le seguenti specifiche.
Per ogni iscritto si memorizzano, oltre gli usuali dati anagrafici, le date di inizio e di fine
abbonamento, le
presenze effettive in palestra (ogni presenza è descritta dalla data e dal numero di ore di
permanenza) e le schede
di allenamento da lui adottate; un iscritto può avere fino ad un massimo di tre schede. Una
scheda, identificata
da un codice, è compilata da un istruttore.
Una scheda descrive la sequenza di esercizi da effettuare durante un allenamento; una scheda
contiene fino ad
un massimo di 20 esercizi; ciascun esercizio, rappresentato da un nome univoco e da una
descrizione, deve
essere svolto in un certo numero di serie, ciascuna delle quali è caratterizzata da un certo numero
di ripetizioni
ed, eventualmente, da un peso utilizzato. Ad esempio, una certa scheda contiene l’esercizio
“distensioni panca
piana”, che deve essere svolto in 4 serie di cui la prima da 15 ripetizioni senza alcun peso, la
seconda da 12
ripetizioni con peso 10 Kg, la terza da 10 con 12 Kg, la quarta da 8 con 14 Kg.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2) punti 8
Sia dato il seguente schema relazionale:
STAZIONESERVIZIO( CODSTAZ ,NOME,CITTÀ)
AUTOBOTTE( CODAUTOB ,MODELLO,CAPACITÀ)
RIFORNISCE( CODAUTOB,TIPOCARB,CODSTAZ ,QUANTITÀ)
FK: CODAUTOB REFERENCES AUTOBOTTE
FK: CODSTAZ REFERENCES STAZIONESERVIZIO
L’autobotte CODAUTOB rifornisce con il carburante TIPOCARB la stazione di servizio
CODSTAZ,
scaricandone una certa QUANTITÀ.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) Selezionare l’autobotte che ha rifornito di “BenzinaVerde” tutte le stazioni di servizio di
Modena;
b) Selezionare, per ogni stazione di servizio, il tipo di carburante che è stato rifornito in maggiore
quantità;
c ) Selezionare l’autobotte che ha rifornito più di cinque volte una stazione di servizio di
Modena;
2 ) Scrivere in algebra relazionale l’ interrogazione a).
Esercizio 3) punti 6
Sia data la seguente relazione R( A ,B,C,D,E,F), ordinata su B, con NR record in NP pagine e
con i seguenti indici:
attributo (A i ) N° chiavi distinte (NK A i ) Indice IX(Ai)
A (Chiave) NK A =NR B + -tree con TID ordinate, con NL A foglie
B NK B B + -tree con TID ordinate, con NL B foglie
C NK C B + -tree con TID ordinate, con NL C foglie
D NK D B + -tree con TID ordinate, con NL D foglie
F NK F B + -tree con TID ordinate, con NL F foglie
Con riferimento alla seguente istruzione SQL:
delete from R
where A = val 1
and D= val 2
and C between val 3 and val 4
and ((B= val 5 ) or (E > val 6 ))
e con i seguenti fattori di filtro dei predicati dati: f A , f B , f C , f D , f E
viene richiesto di riportare, per tutte le possibili modalità di accesso, le espressioni di costo di
accesso alla
relazione, di modifica della relazione e di modifica degli indici.
Esercizio 4) punti 9
Dato il seguente schema di relazione: FORMAZIONE(SQUADRA,GIOCATORE,RUOLO)
e considerando i seguenti vincoli
· Un giocatore gioca in una ed una sola squadra
· Un giocatore può giocare in uno o più ruoli
· In una squadra, un ruolo è ricoperto da un preciso giocatore
viene richiesto di
1. Determinare le dipendenze funzionali (non banali) insite nello schema di relazione.
2. Determinare la chiave o le chiavi dello schema di relazione.
3. Determinare se lo schema di relazione è in 2NF, 3NF e BCNF.
Esempio:
Esercizio 1)
L’ufficio della motorizzazione vuole memorizzare informazioni sui trasferimenti di proprietà
delle
automobili secondo le seguenti specifiche.
Un’automobile è caratterizzata da un modello, dall’anno di produzione, da un numero di serie
assegnatogli
dal produttore, unico fra le automobili da lui prodotte. All’atto della registrazione, all’automobile
viene
assegnato un numero, unico per ciascuna automobile, e la data di registrazione. Nel caso di
distruzione,
viene registrata la data di distruzione.
Per i trasferimenti di proprietà vengono memorizzate le seguenti informazioni: un codice che
identifica il
trasferimento, la data di trasferimento, l’automobile trasferita, il vecchio e il nuovo proprietario.
Il
proprietario di un automobile può essere: il produttore, un rivenditore oppure un privato.
Le norme che vincolano il trasferimento di un’automobile sono le seguenti:
· possono essere trasferite solo le auto registrate;
· un’automobile distrutta non può più essere trasferita;
· un’automobile può essere venduta dal suo produttore solo ad un rivenditore, e un produttore
non può
acquistare automobili;
· un’automobile può essere venduta da un rivenditore solo a privati.
Per un proprietario vengono riportati gli usuali dati anagrafici; un rivenditore è rappresentato
tramite un
codice univoco, un indirizzo ed uno o più numeri telefonici.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello
schema l’attributo derivato TOTALE-POSTI che, per un certo treno, è calcolato come la somma,
estesa a
tutti i tipi di carrozze che formano il treno, del prodotto POSTI-CARROZZA per il NUMEROCARROZZE.
Si trascuri l’occupazione di memoria del dato derivato.
(1,N) (1,N)
COD-T
COD-C
TRENO TIPO
CARROZZA
POSTI
CARROZZA TOTALE-POSTI
NUMERO-CARROZZE
FORMA
Operazione 1) Dato il codice di un tipo di carrozza (COD-C) , incrementarne di10 il numero di
posti (con
conseguente incremento del numero di posti totali di tutti i treni che contengono il tipo di
carrozza in
questione);
Operazione 2) Dato il codice di un treno, visualizzarne tutti i suoi dati.
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
TIPO-CARROZZA E 50 Oper . 1 I 3/Giorno
TRENO E 100 Oper . 2 I 120/Giorno
FORMA R 1000
Esercizio3)
Sia dato il seguente schema relazionale:
VIA( CODICE , NOME, QUARTIERE, LUNGHEZZA)
INCROCIA( CODICE-VIA-A,CODICE-VIA-B , N_VOLTE)
FK: CODICE-VIA-A REFERENCES VIA
FK: CODICE-VIA-B REFERENCES VIA
La via CODICE-VIA-A incrocia la via CODICE-VIA-B un certo numero di VOLTE;
si assume che se nella relazione INCROCIA è presente la tupla <codviax,codviay,5>
non sia presente la tupla simmetrica <codviay,codviax,5>;
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) selezionare le vie che incrociano almeno una via del quartiere “Pastena”;
b) selezionare le vie che non incrociano via “Marco Polo”;
c) selezionare le coppie (CODICE1, CODICE2) tali che le vie con codice CODICE1 e
CODICE2 abbiano
la stessa lunghezza;
d) selezionare il quartiere che ha il maggior numero di vie;
e) selezionare, per ogni quartiere, la via di lunghezza maggiore .
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1) punti 10
Un sistema informativo memorizza dati su un’agenzia immobiliare, secondo le seguenti
specifiche.
L’agenzia pubblicizza gli immobili di cui cura la vendita su giornali. Un immobile può
essere pubblicizzato più volte, su più giornali e in date diverse, con il vincolo di non essere
pubblicizzato più di una volta nella stessa data e sullo stesso giornale. Il prezzo con il quale
un immobile viene pubblicizzato dipende solo dalla data e non dal giornale sul quale viene
riportata la pubblicità. I giornali sono identificati dalla testata e hanno una tiratura,
indirizzo, telefono e fax.
Un immobile è identificato da un codice e descritto dai mq commerciali, dal valore
proposto, dal valore minimo e dalla località. L’agenzia tratta case indipendenti,
appartamenti e ville. Per le case indipendenti occorre sapere quanti sono i lati abbinati (ad
altre case). Per gli appartamenti interessa il piano e le unità della palazzina. Per le ville
interessa il numero di piani e il numero di stanze.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2) punti 11
Sia dato il seguente schema relazionale:
CAUSA( CODCAUSA ,TIPO,DATA)
DIFESA( CFAVV ,CODCAUSA ,CFCLIENTE,PARCELLA)
AK: CODCAUSA, CFCLIENTE
FK: CODCAUSA REFERENCES CAUSA
Nella causa CODCAUSA, l’avvocato CFAVV difende il cliente CFCLIENTE .
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) Selezionare i clienti che non sono stati coinvolti in nessuna causa di tipo “penale”;
b) Selezionare gli avvocati che hanno partecipato a tutte le cause di tipo “penale”;
c ) Selezionare i clienti che, in ogni causa da loro intrapresa, sono stati difesi sempre
dallo stesso avvocato;
d) Selezionare gli avvocati che hanno partecipato ad almeno cinque cause di tipo
“penale” e che in queste cause hanno difeso sempre lo stesso cliente;
2 ) Scrivere in algebra relazionale le interrogazione a) e b).
Esercizio 3) punti 5
Dato il seguente schema di relazione:
PROGETTI(ANNO,PROGETTO,CAPOPROGETTO,REPARTO,RESPONSABILE)
e considerando le seguenti dipendenze funzionali
· (FD1) ANNO,PROGETTO CAPOPROGETTO
· (FD2) CAPOPROGETTO REPARTO
· (FD3) REPARTO RESPONSABILE
· (FD4) ANNO,PROGETTO RESPONSABILE
viene richiesto di
1. Determinare la chiave o le chiavi dello schema di relazione.
2. Determinare se lo schema di relazione è in 2NF, 3NF e BCNF.
3. Produrre uno schema E/R che descriva lo schema di relazione e soddisfi le dipendenze
funzionali date.
Esercizio 4) punti 7
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente
conservare nello schema l’attributo derivato NUMEROSTUDENTI , che per una certa
facoltà è calcolato contando gli studenti iscritti. Si trascuri l’occupazione di memoria del
dato derivato.
FACOL TÀ ISCRIT TO
STUDENTE
(1,1) (0,N)
NUMEROST UDENTI
MATRIC OLA
CIT TÀ
CODFACOL TÀ
Operazione 1) Inserimento di un nuovo studente (in questa operazione si suppone noto e
valido il codice della facoltà);
Operazione 2) Visualizzare tutti i dati di una facoltà, compreso il
NUMEROSTUDENTI .
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
FACOLTÀ E 100 Oper . 1 I 100/Giorno
IN A 50000 Oper . 2 I 50/Giorno
Esercizio 1)
Un sistema informativo deve gestire le gare di Coppa del Mondo di Sci, secondo le seguenti
specifiche.
Gli atleti sono individuati da un numero di tessera FIS e hanno cognome, nome, luogo e data di
nascita,
nazionalità, sesso. Le gare hanno un luogo, una data, un nome della pista, un tipo
(SlalomFemminile,
SlalomMaschile, GiganteFemminile, ... , LiberaMaschile). In una certa data, non si possono
svolgere due
gare dello stesso tipo; in una certo luogo, non si possono svolgere due gare dello stesso tipo.
Gli atleti sono raggruppati in squadre nazionali, rispettivamente maschili e femminili, e ogni
squadra ha
un allenatore, che è individuato con numero di tessera FIS e ha cognome, nome, luogo e data di
nascita,
nazionalità. Un allenatore allena un’unica squadra.
Ogni gara ha un tracciatore, che è un allenatore; per le gare in due manche il tracciatore è diverso
per ogni
manche.
Per ogni partecipazione di un atleta a una gara si registra la posizione di arrivo ed il tempo finale;
per le
gare in due manche si registra anche il tempo di prima manche.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello
schema l’attributo derivato TOTALE-POSTI che, per un certo treno, è calcolato come la somma,
estesa a
tutti i tipi di carrozze che formano il treno, del prodotto POSTI-CARROZZA per il NUMEROCARROZZE.
Si trascuri l’occupazione di memoria del dato derivato.
(1,N) (1,N)
COD-T
COD-C
TRENO TIPO
CARROZZA
POSTI
CARROZZA TOTALE-POSTI
NUMERO-CARROZZE
FORMA
Operazione 1) Dato il codice di un tipo di carrozza (COD-C) , incrementarne di10 il numero di
posti (con
conseguente incremento del numero di posti totali di tutti i treni che contengono il tipo di
carrozza in
questione);
Operazione 2) Dato il codice di un treno, visualizzarne tutti i suoi dati.
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
TIPO-CARROZZA E 50 Oper . 1 I 3/Giorno
TRENO E 100 Oper . 2 I 120/Giorno
FORMA R 1000
Esercizio 3)
Sia dato il seguente schema relazionale:
QUADRO( CQ ,AUTORE,PERIODO)
MOSTRA( CM , NOME,ANNO,ORGANIZZATORE)
ESPOSIZIONE( CM,CQ ,SALA)
FK: CM REFERENCES MOSTRA
FK: CQ REFERENCES QUADRO
Nella mostra CM, il quadro CQ è stato esposto in una certa SALA.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) selezionare tutti i dati sulle mostre dove è stato esposto un quadro di Picasso nel 97 oppure nel
96;
b) selezionare il nome della mostra nella quale sono stati esposti tutti i quadri di Picasso;
c)
selezionare
le
quaterne
(ANNO,
NOMEMOSTRA1,
NOMEMOSTRA2,
ORGANIZZATORE) tali
che nello stesso ANNO le mostre con nome NOMEMOSTRA1 e NOMEMOSTRA2 hanno
avuto lo
stesso ORGANIZZATORE;
d) selezionare tutti i dati dei quadri che sono stati esposti più di tre volte nel 1997;
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1) punti 10
Un sistema informativo per la gestione di uno studio legale memorizza informazioni
relative ai clienti, alle cause e agli avvocati secondo le seguenti specifiche.
In ogni causa, individuata da un codice univoco e descritta da un tipo e da una data, vengono
riportati i clienti e i relativi avvocati, con i seguenti vincoli:
1. in una certa causa, un avvocato può partecipare al massimo una volta, cioè può difendere
al massimo un cliente; per questo compito, l'avvocato percepisce una certa parcella;
2. in una certa causa, un cliente può partecipare una sola volta, in un determinato ruolo, ed
è quindi difeso da un unico avvocato.
Per ciascuna causa vengono rilasciati e memorizzati una serie di documenti; un documento,
identificato all'interno della causa da un numero progressivo, viene redatto da una
segretaria e può essere semplice oppure composto da uno a dieci documenti.
I clienti, gli avvocati e le segretarie sono identificati dal codice fiscale e descritti dagli usuali
dati anagrafici.
Viene richiesto di:
1) Progettare lo schema E/R.
2) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2) punti 12
Sia dato il seguente schema relazionale:
BANCOMAT( CODBANC ,CLIENTE)
PRELIEVO( CODBANC ,NUMPREL ,SPORTELLO,DATA,IMPORTO)
Viene richiesto di:
1) scrivere in SQL le seguenti interrogazioni:
a) Selezionare i clienti che hanno effettuato un prelievo di importo pari a 50.000 e un
prelievo di importo pari a 100.000.
b) Selezionare i clienti che non hanno effettuato nessun prelievo di importo pari a
500.000.
c) Selezionare le coppie (CODBANC-1,CODBANC-2) di bancomat dello stesso cliente.
d) Selezionare, per ogni sportello, la data in cui sono stati effettuati il maggior numero di
prelievi, considerando solo i prelievi con importo maggiore di 400.000.
2) Scrivere in algebra relazionale l’interrogazione a) e b).
Esercizio 3) punti 11
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello schema
l’attributo derivato PUNTEGGIOTOTALE, che per una certo giocatore è calcolato sommando
sia i punteggi
delle partite singole che quelli delle partite in squadra. Si noti che il punteggio di una partita in
squadra deve
essere sommato a tutti i giocatori della squadra. Si trascuri l’occupazione di memoria del dato
derivato.
PARTITE
SQUADRA IN GIOCATORE (0,N) (10,10) EFFETTUA PARTITE
SINGOLE (1,1) (0,N)
PUNTEGGIO TOTALE
CODPARTSQ CODPARTSING
PUNTEGGIO SINGOLO PUNTEGGIO SQUADRA
CODGIOCATORE
Operazione 1) Inserimento di una nuova partita singola (in questa operazione si suppone noto e
valido il
codice del giocatore che effettua la partita);
Operazione 2) Inserimento di una nuova partita di squadra (in questa operazione si suppongono
noti e validi
i codici dei giocatori che effettuano la partita);
Operazione 3) Visualizzare tutti i dati di un giocatore, compreso il PUNTEGGIOTOTALE. Si
noti che nel
caso in cui si deve determinare tale attributo derivato, occorre leggere tutte le partite singole e
tutte lepartite in squadra del giocatore.
Esercizio 1)
Un comune vuole memorizzare informazioni sulla planimetria e servizi della città secondo le
seguenti
specifiche.
Le vie di comunicazione hanno un nome e, se sono strade una carreggiata in metri e una
lunghezza in metri,
se invece sono piazze, un'area in metri quadrati.
In un incrocio, identificato da un nome, confluiscono due o più vie di comunicazione. Deve
essere
rappresentato il numero di volte che due vie di comunicazione si incrociano. Gli edifici storici
hanno un
nome, un'età e una cubatura (in metri cubi) e sono delimitati da una o più vie di comunicazione.
Un ufficio pubblico ha un nome e un indirizzo e può essere situato dentro un edificio storico;
inoltre è aperto
al pubblico in determinati giorni della settimana, in una fascia oraria mattutina e/o pomeridiana.
Gli autobus, identificati da un codice, effettuano una o più fermate; ogni fermata appartiene ad
una ed una
solo via di comunicazione ed è identificata da un nome univoco all’interno della via di
comunicazione. Una
fermata viene effettuata da uno o più autobus.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello
schema l’attributo derivato TOTALE-POSTI che, per un certo treno, è calcolato come la somma,
estesa a
tutti i tipi di carrozze che formano il treno, del prodotto POSTI-CARROZZA per il NUMEROCARROZZE.
Si trascuri l’occupazione di memoria del dato derivato.
(1,N) (1,N)
COD-T
COD-C
TRENO TIPO
CARROZZA
POSTI
CARROZZA TOTALE-POSTI
NUMERO-CARROZZE
FORMA
Operazione 1) Dato il codice di un tipo di carrozza, incrementarne di10 il numero di posti (con
conseguente incremento del numero di posti totali di tutti i treni che contengono il tipo di
carrozza in
questione);
Operazione 2) Dato il codice di un treno, visualizzarne tutti i suoi dati.
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
TIPO-CARROZZA E 50 Oper . 1 I 3/Giorno
TRENO E 100 Oper . 2 I 120/Giorno
FORMA R 1000
Esercizio3)
Sia dato il seguente schema relazionale:
T( CF ,INDIRIZZO,QUALIFICA,COSTO-ORARIO) ( TECNICO )
PC( PC ,NOME,TIPO,NOMEPROPRIETARIO) (PERSONALCOMPUTER )
RG( DATA,CF,PC ,ORE) ( RIPARAZIONEGIORNALIERA )
FK: CF REFERENCES T
FK: PC REFERENCES PC
Nella DATA specificata, il tecnico CF ha riparato il personal computer PC impiegando un certo
numero
di ORE
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) selezionare i PC di tipo “Mac” che sono stati riparati tra il 1/7/97 e il 1/11/97 da un
tecnico con qualifica “hardware”;
b) selezionare i PC di tipo “Mac” che non sono stati riparati tra il 1/7/97 e il 1/11/97;
c) selezionare i dati dei tecnici che hanno riparato tutti i PC di tipo “Mac”;
d) selezionare tutti i dati dei PC che hanno richiesto almeno 10 ore di riparazione;
2 ) Scrivere in algebra relazionale le interrogazioni a), b) e b).
Esercizio 1)
Una azienda di assistenza tecnica per Personal Computer vuole memorizzare informazioni sulle
riparazioni secondo le seguenti specifiche.
Le riparazioni riguardano i componenti hardware dei PC; per ogni tipo di componente viene
memorizzato il nome (univoco), il costo e una breve descrizione. Ogni PC è identificato da un
codice ed
ha un proprietario, descritto tramite il codice fiscale, l'indirizzo e il recapito telefonico.
L’assistenza tecnica per la riparazione dei PC avviene memorizzando un resoconto orario
organizzato
nel seguente modo: in una certa ora di un certo giorno, un tecnico dell’azienda opera su un unico
PC
riparando uno o più guasti e sostituendo zero o più componenti. Un tecnico è descritto tramite il
codice
fiscale, l'indirizzo, la qualifica e il costo orario.
Ogni tecnico stila mensilmente una relazione nella quale riporta il numero totale di ore che in
quel mese
ha dedicato alla riparazione di PC e i tipi di componenti (fino ad un massimo di 5) più sostituiti,
con il
relativo numero di sostituzioni.
Viene richiesto di:
1 ) Progettare lo schema E/R.
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello schema l’attributo derivato TOTALE-VIAGGI, che per una certa città è calcolato come la
somma dei viaggi extraurbani che partono o arrivono nella città e dei viaggi urbani svolti nella
città. Si
noti che un nuovo viaggio extraurbano incrementa di uno il totale viaggi della città di partenza e
della
città di arrivo, mentre uno urbano incrementa di uno il totale viaggi di una unica città
Si trascuri l’occupazione di memoria del dato derivato.
TRA (2,2) (0,N)
COD-CITTÀ COD-E
IN (1,1) (0,N)
VIAGGI
URBANI
CITTÀ VIAGGI
EXTRAURBANI
DURATA TOTALE-VIAGGI DATA COD-U
Operazione 1) Inserimento di un nuovo viaggio urbano (in questa operazione si suppone noto e
valido il codice della città nella quale il viaggio viene svolto);
Operazione 2) Visualizzare tutti i dati di un città. Si noti che nel caso in cui si deve determinare il
TOTALE-VIAGGI occorre solo contare tutti i viaggi urbani ed extraurbani che interessano
quella città.
Operazione 3) Inserimento di un nuovo viaggio extraurbanourbano (in questa operazione si
suppongono noti e validi i codice dellae città di partenza ed arrivo del viaggio);
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
Città E 200 Oper . 1 I 100/Giorno
ViaggiUrbani E 6000 Oper . 2 I 10/Giorno
ViaggiExtraUrbani E 500 Oper . 3 I 1/Giorno
Esercizio3)
Sia dato il seguente schema relazionale:
DIPENDENTE( CF ,NOME,CITTÀ)
PROGETTO( CP ,NOME,ANNO,DURATA)
LAVORA( CP,CF ,MESI,RUOLO)
FK: CP REFERENCES PROGETTO
FK: CF REFERENCES DIPENDENTE
Nel progetto CP, il dipendente CF lavora per un certo numero di MESI, svolgendo un certo
RUOLO.
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a) selezionare i dipendenti di Modena che non hanno lavorato in alcun progetto
dell’anno 1995;
b) selezionare i dipendenti di Modena che in un progetto dell’anno 1993 hanno
svolto il ruolo di “Progettista” oppure di “Disegnatore”;
c) selezionare tutti i dati dei dipendenti che hanno lavorato in almeno 3 ruoli
distinti;
d) selezionare, per ogni dipendente, il progetto in cui esso ha lavorato il maggior
numero di mesi.
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
Esercizio 1)
Una società distributrice di Compact Disk vuole memorizzare dati sulla vendita dei CD , sui
negozi e
sui clienti secondo le seguenti specifiche.
Per i CD viene riportato un codice univoco, il titolo e la casa discografica; i CD sono partizionati
in
singoli, per i quali viene rappresentato solo l'autore, e in compilation, dei quali vengono descritti,
per
ognuno dei dieci brani in esso contenuto, il titolo e l'autore; il titolo del brano in una compilation
è
identificativo.
Ogni negozio, identificato da un codice e descritto da un nome, stila mensilmente una classifica
delle
vendite dei CD nella quale riporta per i CD più venduti (fino ad un massimo di venti CD) il
relativo
numero di vendite.
Infine vengono memorizzate informazioni dettagliate sull'acquisto di CD da parte dei clienti: in
una
certa data, un cliente acquista presso un negozio una certa quantità di un CD. Del cliente si
memorizza il
suo numero di tessera (univoco) e l'indirizzo.
Viene richiesto di:
1 ) Progettare lo schema E/R (10 punti).
2 ) Tradurre lo schema E/R in schema relazionale in terza forma normale (3 punti).
Esercizio 2)
Dato il seguente schema E/R, volume dei dati e operazioni, decidere se è conveniente conservare
nello schema l’attributo derivato TOTALE-CASSA, che per una certa cassa è calcolato come la
differenza tra la somma degli importi delle entrate e la somma degli importi delle uscite. Si
supponga
che l’importo di una entrata venga aggiunto al TOTALE-CASSA di un’unica cassa mentre
l’importo
di una uscita venga sottratto al TOTALE-CASSA di due casse definite.
Si trascuri l’occupazione di memoria del dato derivato.
ESCE (2,2) (0,N)
COD-CASSA COD-U
ENTRA (1,1) (0,N)
ENTRATA CASSA USCITA
IMPORTO TOTALE-CASSA IMPORTO COD-E
Operazione 1) Inserimento di una nuova entrata (in questa operazione si suppone noto e valido il
codice della cassa sulla quale l'entrata deve essere registrata); (3 punti)
Operazione 2) Visualizzare tutti i dati di un cassa. Si noti che nel caso in cui si deve determinare
il
TOTALE-CASSA occorre leggere tutte le entrate e tutte le uscite di quella cassa. (3 punti)
Operazione 3) Inserimento di una nuova uscita (in questa operazione si suppongono noti e validi
i
codici delle due casse sulle quali l'uscita deve essere registrata); (4 punti)
Tavola dei volumi Tavola delle operazioni
CONCETTO TIPO VOLUME OPER. TIPO FREQUENZA
Cassa E 100 Oper . 1 I 100/Giorno
Entrata E 3000 Oper . 2 I 10/Giorno
Uscita E 1000 Oper . 3 I 1/Giorno
Esercizio3)
Sia dato il seguente schema relazionale:
CICLISTICA(NOMECICLISTA, NAZIONALITÀ, ETÀ);
GARA(NOMECORSA, ANNO, PARTENZA, ARRIVO);
edizione di un certo ANNO della corsa ciclistica NOMECORSA; PARTENZA e
ARRIVO sono rispettivamente la città di partenza e di arrivo.
PARTECIPA(NOMECORSA, ANNO, NOMECICLISTA, POSIZIONE);
FK: NOMECORSA, ANNO REFERENCES GARA
FK: NOMECICLISTA REFERENCES CICLISTICA
Il ciclista NOMECICLISTA ha partecipato all’edizione di un certo ANNO della
corsa ciclistica NOMECORSA classificandosi in una certa POSIZIONE o p p u r e
ritirandosi (POSIZIONE=‘ritirato’).
Viene richiesto di:
1 ) scrivere in SQL le seguenti interrogazioni:
a ) selezionare i ciclisti che si sono classificati in prima posizione in u n a
gara ciclistica partita da Milano.
b ) selezionare il nome dei ciclisti che non si sono mai ritirati al Giro
d’Italia (corsa con nome Giro d’Italia).
c ) selezionare le corse per le quali in ogni edizione c’è stato almeno u n
ciclista ritirato.
d ) selezionare, per ogni corsa ciclistica, l’anno in cui c’è stato il maggior
numero di ciclisti ritirati.
2 ) Scrivere in algebra relazionale le interrogazioni a) e b).
1 punto ad a) query ed algebra; 2 punti per ogni query da b) a d) + 2 punti p e r
algebra d).
Esercizio 2) punti 18
Dato il seguente schema di relazione:
FORMAZIONE(SQUADRA,GIOCATORE,RUOLO)
e considerando i seguenti vincoli
· Un giocatore gioca in una ed una sola squadra
· Un giocatore può giocare in uno o più ruoli
· In una squadra, un ruolo è ricoperto da un preciso giocatore
viene richiesto di
1. Determinare le dipendenze funzionali (non banali) insite nello schema di relazione.
2. Determinare la chiave o le chiavi dello schema di relazione.
3. Determinare se lo schema di relazione è in 2NF, 3NF e BCNF.
4. Data la decomposizione formata dagli schemi
GIOC_SQUADRA( GIOCATORE ,SQUADRA)
GIOC_RUOLO( GIOCATORE,RUOLO )
a. Stabilire se la decomposizione è lossless e se preserva le dipendenze funzionali individuate nel
punto 1.
b. Scrivere un’interrogazione SQL che restituisca il valore zero se è possibile inserire nella
relazione
GIOC_RUOLO una tupla (x,y) senza violare le dipendenze funzionali individuate nel punto 1.
Esempio:
Schema iniziale Decomposizione
FORMAZIONE GIOC_SQUADRA
SQUADRA GIOCATORE RUOLO GIOCATORE SQUADRA
Italia Bergomi TerzinoDestro Bergomi Italia
Italia Bergomi Libero Moriero Italia
Italia Moriero AlaDestra Ronaldo Brasile
Brasile Ronaldo Centravanti Zanetti Argentina
Argentina Zanetti TerzinoDestro Simeone Argentina
Argentina Simeone Centrocampista
GIOC_RUOLO
GIOCATORE RUOLO
Bergomi TerzinoDestro
Bergomi Libero
Moriero Ala Destra
Ronaldo Centravanti
Zanetti TerzinoDestro
Simeone Centrocampista
· Suggerimento per l’interrogazione 4b: Nello schema decomposto, la tupla
(¢Zanetti’,’CentroCampista’) non
può essere inserita: la squadra di Zanetti, l’Argentina, ha già unCentroCampista; invece la tupla
(‘Moriero’,’CentroCampista’) può essere inserita: la squadra di Moriero, l’Italia, non ha un
CentroCampista.
Esercizio 2) punti 18
Sia dato il seguente schema E/R:
(1,1)
Realizza Impiegato
CF
Città (0,N) Membro
Capo
Data
Stato Avanzamento
Progetto
(0,N)
(1,1)
CP Nome
Viene richiesto di:
1 ) Tradurre lo schema E/R in schema relazionale in terza forma normale.
2 ) Aggiungere allo schema relazionale ottenuto al punto 1) il vincolo che in una certa Data
lo Stato di Avanzamento di un certo Progetto è unico.
3 ) Tradurre lo schema E/R di figura in un unico schema di relazione.
3.a)Determinare le dipendenze funzionali (non banali) insite nello schema di
relazione;
3.b) Determinare la chiave o le chiavi dello schema di relazione (consiglio: utilizzare
gli assiomi di Armstrong);
3.c)Determinare se lo schema di relazione è in 2NF, 3NF e BCNF.
3.d) Produrre una eventuale decomposizione almeno in 3NF che sia lossless.
Esercizio 2)
Data la seguente relazione:
Orario(Giorno, Ora, Aula, Corso, Professore, Dipartimento)
e considerando i seguenti vincoli:
1) In una certa Ora di un certo Giorno (della settimana) un Corso si tiene in una precisa Aula
2) In una certa Ora di un certo Giorno (della settimana) in un’Aula si tiene un preciso Corso
3) In un certo Giorno (della settimana) un Corso è tenuto da un solo Professore
4) Il Dipartimento è unico per un dato Professore
si determinino:
a) Le dipendenze funzionali (non banali) insite nella relazione Orario;
b) La chiave o le chiavi della relazione Orario;
c) Se la relazione è in 2NF e 3NF;
d) Una eventuale decomposizione in 3NF che sia lossless.