Esercizio 1

Prova Scritta di Basi di Dati
4 Giugno 2010
COGNOME:
NOME:
MATRICOLA:
Si prega di risolvere gli esercizi direttamente sui fogli del testo, negli spazi indicati.
Usare il foglio protocollo solo per la brutta copia.
Esercizio
1
2
3
4
Totale
Punti
previsti
12
8
10
3
33
Punti
assegnati
Esercizio 1
Si vuole progettare una base di dati a supporto dell'attivita` del centralino del servizio radio-taxi, che riceve le chiamate di
richiesta di un taxi. Al centralino lavorano le telefoniste, caratterizzate da un codice, cognome, nome, data di nascita. Le
chiamate vengono tutte registrate e sono caratterizzate da un numero di chiamata, la data e ora della chiamata, il luogo della
partenza e il luogo di destinazione (che le telefoniste richiedono sempre, non accettando chiamate se questo non viene
fornito); per ogni chiamata, si vuole anche sapere quale è stata la telefonista che la ha ricevuta. Le chiamate sono poi
suddivise in chiamate urgenti e chiamate per appuntamento; per queste ultime si vuole conoscere anche la data e ora
dell’appuntamento richiesto dal cliente.
I taxi sono caratterizzati da un codice, il nome e cognome del proprietario (che e` anche il conducente); i taxi si dividono in
disponibili (che possono quindi servire le chiamate) e indisponibili, e di questi ultimi si vuole sapere fino a che data e ora
sono indisponibili.
Le chiamate vengono servite dai taxi; si vuole sapere in un certo momento quale taxi sta servendo una certa chiamata, con
l’indicazione, se nota, dell’ora presunta alla quale il taxi terminerà di servire la chiamata; in più, si vuole anche tenere storia
di tutte le volte che un taxi ha servito una chiamata, con l’indicazione della data e ora di inizio e data e ora di fine servizio.
Da ultimo, i taxi disponibili vengono riservati per servire le chiamate ad appuntamento.
Si richiede di:
a)
Definire uno schema ER per la situazione sopra descritta (ristrutturare le specifiche se necessario). Precisare la
cardinalità delle associazioni e degli attributi (se diversa da quella di default) ed i vincoli di identificazione.
b) Fornire, come documentazione aggiuntiva allo schema ER, il tipo delle eventuali generalizzazioni presenti nello
schema e l’elenco dei vincoli di integrità non rappresentabili nello schema ER proposto. L’elenco deve contenere
almeno due vincoli non banali, cioe` vincoli che coinvolgono piu` di una entita` o associazione.
c)
Generare lo schema ristrutturato corrispondente allo schema ER definito al punto a), e presentare l’insieme dei vincoli
di integrita’ per lo schema ristrutturato.
d) Realizzare uno schema relazionale corrispondente allo schema ristrutturato definito al punto c), indicando le chiavi,
primarie ed alternative, le chiavi esterne, gli attributi che possono essere nulli ed i vincoli di integrita’ per tale schema,
indicando la tipologia di ciascuno di essi.
Esercizio 2
Si consideri il seguente schema relazionale:
TECA(CodT,Nome,Collocazione, Tipo)
FARFALLA (CodF, NomeLatino, Famiglia,AperturaAlare)
ESPOSIZIONE(CodTTeca, CodFFarfalla, DataInizio, DataFine)
Si richiede di formulare le seguenti interrogazioni in algebra o calcolo relazionale:
a)
Algebra relazionale: Determinare le farfalle con apertura alare maggiore di 4 cm in esposizione in una teca di tipo
'quadro' a maggio 2010.
b) Algebra relazionale: Determinare le teche di tipo 'tavolo' collocate nella sala 'Rossa' che hanno ospitato in due periodi
distinti farfalle della famiglia 'Zygaenidae'.
c)
Algebra relazionale: Determinare le teche di tipo 'quadro' nelle quali e` stata esposta almeno una farfalla per ogni
famiglia.
d) Calcolo relazionale: Determinare la farfalle in esposizione il 5 giugno 2010 nella teca con codice 14.
Esercizio 3
Si consideri il seguente schema relazionale:
TECA(CodT,Nome,Collocazione, Tipo)
FARFALLA (CodF, NomeLatino, Famiglia,AperturaAlare)
ESPOSIZIONE(CodTTeca, CodFFarfalla, DataInizio, DataFine)
Presentare i comandi SQL corrispondenti alle seguenti operazioni:
a)
Determinare le farfalle con apertura alare maggiore di 4 cm insieme al nome e al codice della teca nella quale sono state
esposte il 15 maggio 2010, se esistente.
b) Determinare le teche di tipo 'tavolo' collocate nella sala 'Rossa' che hanno ospitato in due periodi distinti farfalle della
famiglia 'Zygaenidae'.
c)
Si vogliono rimuovere dall'esposizione le farfalle con apertura alare inferiore alla media dell'apertura alare delle farfalle
della loro stessa famiglia.
d) Garantire che le teche di tipo 'quadro' contengano soltanto farfalle con apertura alare superiore a 5 cm. Utilizzare a
questo proposito un'asserzione.
Esercizio 4
Discutere, utilizzando al massimo 10 righe, la differenza tra DBMS passivi e DBMS attivi.
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
___________________________________________________________________________________
CREATE TABLE Nome Tabella
( [ LIKE Nome Tabella, ]
spec_col1,....,spec_coln
)
[, PRIMARY KEY (Lista Nomi Colonne)]
[, UNIQUE (Lista Nomi Colonne)]
[, FOREIGN KEY (Lista Nomi Colonne)
REFERENCES Nome Tabella Riferita
[ON DELETE { NO ACTION | RESTRICT | CASCADE |
SET NULL | SET DEFAULT } ]
[ON UPDATE { NO ACTION | RESTRICT | CASCADE |
SET NULL | SET DEFAULT } ] ]
[, FOREIGN KEY … ]
[CHECK Condizione];
:::
[CHECK Condizione];
);
CREATE TABLE Nome Tabella [ WITH DATA ] AS
Query;
spec_coli = Nome Colonnai Dominioi { [GENERATED ALWAYS AS Espressione ] |
[DEFAULT Valore Default] [NOT NULL] [PRIMARY KEY]
[UNIQUE] [CHECK Condizione] }
CREATE ASSERTION Nome Vincolo
CHECK Condizione;
DROP TABLE Nome Tabella {RESTRICT | CASCADE};
ALTER TABLE Nome Tabella ADD COLUMN spec_col;
ALTER TABLE Nome Tabella ALTER COLUMN Nome Colonna
{ SET DEFAULT Valore default | DROP DEFAULT };
ALTER TABLE Nome Tabella DROP COLUMN Nome Colonna {RESTRICT | CASCADE};
SELECT [DISTINCT] Ri1.C1,…,Rin.Cn
FROM R1,…,Rn
WHERE Condizione
[ GROUP BY Lista Nomi Colonne
[HAVING Condizione su aggregazione] ]
[ ORDER BY Lista Nomi Colonne ];
Clausola FROM puo’ anche contenere operazioni di JOIN:
- Nome Relazione CROSS [ LEFT OUTER | RIGHT OUTER | INNER ] JOIN Nome Relazione
- Nome Relazione [ LEFT OUTER | RIGHT OUTER | INNER ] JOIN Nome Relazione ON Predicato
- Nome Relazione [ LEFT OUTER | RIGHT OUTER | INNER ] JOIN Nome Relazione USING Lista Nomi Colonne
- Nome Relazione NATURAL [ LEFT OUTER | RIGHT OUTER | INNER ] JOIN Nome Relazione
Condizione e’ una combinazione booleana di predicati, inclusi i seguenti:
- C BETWEEN v1 AND v2
- C IN (v1,…,vn), C IN sq
- C LIKE pattern, con pattern stringa di caratteri che puo’ contenere i caratteri speciali % e _
- EXISTS sq, NOT EXISTS sq
- ANY sq, ALL sq
- UNION, INTERSECT, EXCEPT tra query
Funzioni di gruppo:
- MAX, MIN, SUM, AVG, COUNT
- Per ciascuna due versioni f(DISTINCT Nome Colonna) f(Nome Colonna)
- Per COUNT: COUNT(*) COUNT(DISTINCT Nome Colonna) COUNT(Nome Colonna)
INSERT INTO Nome Tabella [ ( C1, ... , Cn ) ]
{ VALUES (e1, ... , en ) | sq } ;
DELETE FROM Nome Tabella [alias]
[ WHERE Condizione ];
UPDATE Nome Tabella [alias]
SET C1 = { e1 | NULL } , … , Cn = { en | NULL }
[ WHERE Condizione ];
CREATE VIEW Nome [ ( Lista Nomi Colonne ) ]
AS Query
[ WITH [ { LOCAL | CASCADED} ] CHECK OPTION];
CREATE TRIGGER Nome
{BEFORE|AFTER} Evento ON Relazione
[REFERENCING {OLD AS Var | NEW AS Var | OLD AS Var | NEW AS Var}]
[FOR EACH {ROW | STATEMENT}]
[WHEN Condizione ]
Comandi SQL
con



Evento: INSERT, DELETE, UPDATE [OF Lista Attributi]
Condizione: clausola WHERE SQL
Se l’azione comprende piu’ di un comando: BEGIN ATOMIC … END;