Esercizio 1

Si consideri il seguente schema di base di dati

PARTITI(Codice, NomePartito, Coalizione) CANDIDATI(CFCand, NomeCand, CognomeCand, CPartito, Città) SONDAGGI(CodPart, DataRilevazione, PercFavorevoli)

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

   Cpartito^(PARTITI)

   CodPart^(PARTITI)

2. Creare una vista che contenga i nomi dei partiti che hanno ricevuto in almeno una rilevazione una percentuale di favorevoli tra il 20% e il 30%

   CREATE VIEW vista1 AS
   (SELECT NomePartito 
   FROM PARTITI JOIN SONDAGGI ON (Codice = CodPart)
   WHERE PercFavorevoli BETWEEN 20 AND 30);
   

3. Cancellare i sondaggi effettuati prima del 10/1/2012

   DELETE FROM SONDAGGI
   WHERE DataRilevazione < '10-01-2012'DATE
   

4. Aumentare del 10% la percentuale di favorevoli nei sondaggi relativi al partito di nome P11

   UPDATE SONDAGGI SET PercFavorevoli = PercFavorevoli * 1.1
   WHERE CodPart = (
   	SELECT Codice FROM PARTITI WHERE NomePartito = 'P11'
   )
   

Esercizio 2

1. Il codice dei partiti che hanno ottenuto la stessa percentuale di favorevoli in due date diverse

   SELECT CodPart FROM SONDAGGI S1 JOIN SONDAGGI S2 USING(CodPart)
   WHERE S1.DataRilevazione <> S2.DataRilevazione 
   	AND S1.PercFavorevoli = S2.PercFavorevoli;
   

2. Il nome e cognome dei candidati il cui partito non ha mai ricevuto un sondaggio favorevole

   SELECT NomeCand, CognomeCand FROM CANDIDATI
   EXCEPT
   SELECT NomeCand, CognomeCand 
   FROM CANDIDATI JOIN SONDAGGI ON (CPartito = CodPart)
   WHERE PercFavorevoli > 0
   

3. Per i partiti con almeno 10 candidati, restituire il loro codice e la massima e minima percentuale di favorevoli ottenuta in un sondaggio

   SELECT CodPart, MAX(PercFavorevoli), MIN(PercFavorevoli)
   FROM SONDAGGI
   WHERE CodPart IN (
   	SELECT CPartito FROM CANDIDATI
   	GROUP BY CPartito
   	HAVING COUNT(*) > 10
   )
   GROUP BY CodPart
   

4. I partiti che hanno candidati sia di Varese che di Milano ma non di Saronni

   SELECT CPartito FROM CANDIDATI WHERE Città = 'Varese' 
   INTERSECT
   SELECT CPartito FROM CANDIDATI WHERE Città = 'Milano' 
   EXCEPT
   SELECT CPartito FROM CANDIDATI WHERE Città = 'Saronno'
   

Esercizio 3

Si consideri lo schema ER di seguito rappresentato, dove la gerarchia di generalizzazione è totale/condivisa. Si consideri inoltre il seguente carico di lavoro: lettura a2: 100 volte al di; lettura E41 ed E4: 200 volte al dì

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

   

   Vincoli: essendo la gerarchia totale, ogni istanza di E4 deve partecipare ad almeno una associazione tra A5 e A6

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à

   E1(a1, a2, a3) E2(a10, r1, a1(E1)) E3(a12, a8, a9) E4(a4, a12(E3)) E41(a4(E4), a7, a5) E42(a4(E4), a6) a11(a11.1) A2(a12(E3), a10(E2), R2) A3(a4(E), a12(E3))

3. Tradurre in SQL i vincoli determinati al punto 1

   CREATE ASSERTION Almeno_Uno
   CHECK EXISTS((
   SELECT a4 FROM E4 INTERSECT SELECT a4 FROM E41
   )OR(
   SELECT a4 FROM E4 INTERSECT SELECT a4 FROM E42	
   ));