Esercizio 1

CORSA_CAMPESTRE(CodCorsa, NomeCorsa, Categoria, Città, Regione, Data, CodOrganizzatore) ORGANIZZATORE(CodOrganizzatore, NomeO) CONCORRENTE(CodConcorrente, NomeC, DataNascita, Indirizzo, Città, Regione) PARTECIPA_CORSA (CodConcorrente, CodCorsa, Posizione)

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

   

   1. Creare la vista che contiene il codice delle corse a cui hanno partecipato concorrenti della Lombardia

   CREATE VIEW corseLombardia AS (
   	SELECT CodCorsa FROM CORSA_CAMPESTRE 
   	WHERE CodCorsa IN (
   		SELECT CodCorsa FROM PARTECIPA_CORSA NATURAL JOIN CONCORRENTE
   		WHERE Regione = 'Lombardia'
   	)
   )
   

2. Aumentare di due la posizione dei concorrenti di nome Mario Rossi

   UPDATE PARTECIPA_CORSA SET Posizione = Posizione + 2
   WHERE CodConcorrente IN(
   	SELECT CodConcorrente FROM CONCORRENTE WHERE NomeC = 'Mario Rossi'
   )
   

3. Cancellare dalla tabella CORSA_CAMPESTRE l’attributo Categoria

   ALTER TABLE CORSA_CAMPESTRE DROP COLUMN Categoria;
   

Esercizio 2

1. Il nome ed il codice delle persone che hanno organizzato almeno due corsi campestri di categoria diversa

   SELECT NomeO, CodOrganizzatore 
   FROM ORGANIZZATORE NATURAL JOIN CORSA_CAMPESTRE 
   GROUP BY CodOrganizzatore, NomeO
   HAVING COUNT(DISTINCT Categoria) >= 2
   

2. Il nome delle persone che, nella regione Lazio, hanno organizzato almeno una corsa campestre per la categoria Under 14 ma nessuna corsa campestre per la categoria Under 20

   SELECT NomeO 
   FROM CORSA_CAMPESTRE NATURAL JOIN ORGANIZZATORE
   WHERE Regione = 'Lazio' AND Categoria = 'Under 14'
   MINUS
   SELECT NomeO 
   FROM CORSA_CAMPESTRE NATURAL JOIN ORGANIZZATORE
   WHERE Regione = 'Lazio' AND Categoria = 'Under 20'
   

3. Il nome ed il codice dei concorrenti che hanno partecipato a tutte le corse campestri

   SELECT NomeC, CodConcorrente 
   FROM CONCORRENTE NATURAL JOIN PARTECIPA_CORSA
   GROUP BY CodConcorrente, NomeC
   HAVING COUNT(*) = (SELECT COUNT(*) FROM CORSA_CAMPESTRE)
   

4. Per ogni concorrente, il suo codice e il miglior piazzamento che ha ottenuto in una corsa del 2016

   SELECT CodConcorrente, MIN(Posizione)
   FROM PARTECIPA_CORSA NATURAL JOIN CORSA_CAMPESTRE
   WHERE EXTRACT(YEAR FROM Data) = 2016
   GROUP BY CodConcorrente
   

Esercizio 3

Si consideri lo schema ER di seguito rappresentato, dove la gerarchia di generalizzazione è totale/esclusiva.

Si rischiede 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

   

   Non avendo informazioni sul carico di lavoro si è scelto di ristrutturare l’attributo multivalore eliminando le sottocomponenti e la gerarchia di generalizzazione sostituendola con associazioni binarie (A5 e A6) che legano l’entità padre alle due entità figlie.

   Vincoli: ogni istanza di E3 deve partecipare o ad una istanza di A5 o di A6 ma non ad entrambe

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à

   

3. Tradurre in SQL i vincoli determinati al punto 1

   CREATE ASSERTION SoloUno CHECK(
   	NOT EXISTS(SELECT a31 FROM E5 INTERSECT SELECT a31 FROM E6)
   )
   
   CREATE ASSERTION AlmenoUno CHECK(
   	EXISTS(SELECT a31 FROM E3 INTERSECT SELECT a31 FROM E5) 
   	OR EXISTS(SELECT a31 FROM E3 INTERSECT SELECT a31 FROM E6)
   )