Si consideri il seguente schema di base di dati:

GARA(CodG, Luogo, Data, Disciplina) ATLETA(CodA, Nome, Nazione, DataNascita, Specialità) PARTECIPAZIONE(CodG, CodA, PosizioneArrivo, Tempo)

  1. Identificare le chiavi esterne presenti nello schema indicando, per ognuna di esse, relazione referente e riferita

    PARTECIPAZIONE.CodG (referente) chiave esterna su GARA.CodG (riferita)

    PARTECIPAZIONE.CodA (referente) chiave estenra su ATLETA.CodA (riferita)

  2. Scrivere i comandi SQL per:

    1. Creare la tabella PARTECIPAZIONE specificando gli opportuni vincoli di integrità e le opzioni per gestire l’integrità referenziale (motivando le scelte effettuate per la gestione dell’integrità referenziale)

      CREATE TABLE PARTECIPAZIONE(
	codG NUMERIC(5) REFERENCES GARA ON UPDATE CASCADE,
																	ON DELETE RESTRICT,
	codA NUMERIC(5) REFERENCES ATLETA ON DELETE CASCADE
																		ON UPDATE CASCADE,
	posizioneArrivo NUMERIC(3), 
	tempo TIME,
	PRIMARY KEY (codG, codA)
);

      Se si modifica il codice di un atleta nella tabella ATLETA o il codice di una gara nella tabella GARA, la modifica viene effettuata anche sulla tabella PARTECIPAZIONE.

      Non è consentita la cancellazione di una GARA se esistono tuple nella tabella PARTECIPAZIONE che la riferiscono.

      Se si cancella un atleta, vengono cancellate anche le informazioni sulle gare a cui ha partecipato.

    2. Cancellare le le gare avvenute prima del 1/1/1999

      DELETE FROM GARA
WHERE data < '1999-01-01' DATE;

    3. Aumentare di due unità la posizione di arrivo degli atleti di nome Rossi

      UPDATE PARTECIPAZIONE SET posizioneArrivo = posizioneArrivo + 2
WHERE codA IN (SELECT codA FROM ATLETI WHERE nome = 'Rossi');

Esercizio 2

Forumulare in SQL le interrogazioni per restituire:

  1. Il nome e la data di nascita degli atleti tedeschi che non hanno partecipato a nessuna gara di slalom speciale

    SELECT nome, dataNascita 
FROM ATLETA a 
WHERE nazione = 'Germania' AND a.codA NOT IN (
	SELECT codA 
	FROM PARTECIPAZIONE p NATURAL JOIN GARA
	WHERE disciplina = 'slalom speciale'
);

  2. Le nazioni per cui concorrono almeno 6 atleti nati dopo il 1995, ciascuno dei quali abbia partecipato ad almeno 10 gare di sci di fondo

    SELECT Nazione FROM ATLETA
WHERE dataNascita > '1995-01-01' DATE AND codA IN (
	SELECT codA 
	FROM PARTECIPAZIONE NATURAL JOIN GARA
	WHERE disciplina = 'sci di fondo'
	GROUP BY codA
	HAVING COUNT(*)>=10
)
GROUP BY nazione
HAVING COUNT(*) >= 6;

  3. Il nome degli atleti che nel 2000 si sono classificati sia terzi (posizione = 3) che primi in una gara ma mai undicesimi

    SELECT nome 
FROM ATLETA NATURAL JOIN PARTECIPAZIONE NATURAL JOIN GARA
WHERE EXTRACT(YEAR FROM Data) = 2000 AND posizioneArrivo = 3
INTERSECT
SELECT nome 
FROM ATLETA NATURAL JOIN PARTECIPAZIONE NATURAL JOIN GARA
WHERE EXTRACT(YEAR FROM Data) = 2000 AND posizioneArrivo = 1
MINUS
FROM ATLETA NATURAL JOIN PARTECIPAZIONE NATURAL JOIN GARA
WHERE EXTRACT(YEAR FROM Data) = 2000 AND posizioneArrivo = 1

  4. Le gare di discesa libera svolte a Courmayeur a cui hanno partecipato il massimo numeri di atleti di nazionalità italiana

    SELECT codG 
FROM GARA NATURAL JOIN ATLETA NATURAL JOIN PARTECIPAZIONE
WHERE disciplina = 'discesa libera' AND luogo = 'Courmayeur'
	AND nazione = 'Italia'
GROUP BY codG 
HAVING COUNT(*) >= ALL(
	SELECT COUNT(*) 
	FROM GARA NATURAL JOIN ATLETA NATURAL JOIN PARTECIPAZIONE
	WHERE disciplina = 'discesa libera' AND luogo = 'Courmayeur'
		AND nazione = 'Italia'
	GROUP BY codG
)

Esercizio 3

Si consideri lo schema ER di seguito rappresentato, dove la gerarchia di generalizzazione è parziale/esclusiva. Si consideri inoltre il seguente carico di lettura: lettura a11: 20 volte al giorno; lettura E3: 40 volte al giorno.

Untitled

In base al carico di lavoro ed alle precedenti informazioni, si richiede di:

  1. produrre uno schema ER ristrutturato, giustificando le scelte effettuate, ove più opzioni siano possibili per la ristrutturazione. Elencare in linguaggio naturale gli eventuali vincoli derivanti dalla ristrutturazione.

    L’entità E5 e E6 vengono accorpate in E3, che avrà quindi attributi a31, a51, a52, a61 e un attributo “tipo” che potrà assumere valori NULL, E5 o E6. Se tipo è NULL allora anche a51, a52 e a61 lo dovranno essere, se tipo ha valore “E5” allora a51 e a52 dovranno avere valori non nulli, e a61 dovrà avere valore NULL, se tipo ha valore “E6” allora a51 e a52 dovranno essere nulli, mentre a61 potrà sia essere NULL sia NOT NULL.

    Considerato il carico di lavoro, l’attributo a11 viene ristrutturato eliminando le sue sottocomponenti.

    L’attributo multivalore a42 viene ristrutturato rendendolo un’entità a42 con attributo a42.1, legata a E4 da un’associazione molti a molti opzionale.

  2. Tradurre lo schema ER ottenuto al punto 1 in uno schema logico equivalente indicando, per ogni relazione, chiavi, chiavi esterne e vincoli di obbligatorietà

    E2(a21, a22) E1(a10, a21^E2, a11) E3(a31, a51(0), a52(0), a61(0), tipo(0)) E4(a41, a31^E3, a3) A42(a421) A2(a21^E2, a31^E3, a2) A4(a41^E4, a421^A42)

  3. Tradurre in SQL i vincoli determinati al punto 1

    ALTER TABLE E3 ADD CONSTRAINT TipoOK CHECK(Tipo IN (E5, E6));
ALTER TABLE E3 ADD CONSTRAINT Gerarchia CHECK((Tipo = E5 AND a51 IS NOT NULL AND a52 IS NOT NULL AND a61 IS NULL) OR (Tipo = E6 AND A51 IS NULL AND A52 IS NULL) OR (Tipo = NULL AND A51 IS NULL AND A52 IS NULL AND A61 IS NULL));