Esercizio 1

Persona(CF, Nome, Cognome, DataNascita) Città(Id, Nome, NumeroAbitanti) Ditta(PIVA, NumeroImpiegati, CapitaleSociale) HaAbitato(CFPersona, IdCittà, DaAnno, AAnno) HaLavorato(CFPersona, PIVADitta, DaAnno, AAnno) HaAvutoSede(PIVADitta, IdCittà, DaAnno, AAnno)

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

    Untitled

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

    CREATE TABLE HaLavorato(
	CFPersona VARCHAR(16) REFERENCES Persona
		ON DELETE CASCADE
		ON UPDATE CASCADE,
	PIVADitta VARCHAR(11) REFERENCES Ditta
		ON UPDATE CASCADE,
	DaAnno DECIMAL(4),
	AAnno DECIMAL(4),
	PRIMARY KEY (CFPersona, PIVADitta) 
);

  3. Aumentare del 10% il capitale sociale delle ditte che hanno o hanno avuto sede a Milano.

    UPDATE Ditta SET CapitaleSociale = CapitaleSociale * 1.1
WHERE PIVA IN(
	SELECT PIVADitta 
	FROM HaAvutoSede JOIN Città ON (IdCittà = Id)
	WHERE Nome = 'Milano'
);

  4. Aggiungere alla tabella Città l’attributo Regione

    ALTER TABLE Città ADD COLUMN Regione VARCHAR(20);

Esercizio 2

  1. Il codice fiscale delle persone che hanno lavorato almeno una volta in una città in cui abitano o hanno abitato

    SELECT CFPersona 
FROM HaAbitato A JOIN HaLavorato USING(CFPersona) 
	JOIN HaAvutoSede S USING(PIVADitta)
WHERE A.IdCittà = S.IdCittà;

  2. La partita Iva delle ditte che hanno (o hanno avuto) sedi in tutte le città

    SELECT PIVADitta FROM HaAvutoSede 
GROUP BY PIVADitta 
HAVING COUNT(IdCittà) = (
	SELECT COUNT(*) FROM Città
);

  3. Per le persone che hanno lavorato in almeno 4 città diverse, il loro CF ed il numero di anni complessivi di lavoro

    SELECT CFPersona, SUM(AAnno - DaAnno) 
FROM HaLavorato JOIN HaAvutoSede USING(PIVADitta)
GROUP BY CFPersona
HAVING COUNT(DISTINCT IdCittà) >= 4

  4. Il nome e cognome delle persone nate dopo il 1990 che non hanno mai lavorato in una ditta con numero di impiegati maggiore di 30

    SELECT Nome, Cognome FROM Persona WHERE EXTRACT(YEAR FROM DataNascita) > 1990 AND CF NOT IN(
	SELECT CFPersona 
	FROM HaLavorato JOIN Ditta ON(PIVADitta = PIVA) 
	WHERE NumeroImpiegati > 30
)

  5. Per ogni anno e per ogni identificatore di città, il numero di persone che hanno iniziato ad abitare in quella città in quell’anno (ad esempio 2009, 123, numero di persone che hanno iniziato ad abitare nella città con id 123 nel 2009)

    SELECT DaAnno, IdCittà, COUNT(*)
FROM HaAbitato
GROUP BY DaAnno, IdCittà

Esercizio 3

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

Untitled

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.

    Untitled

    Vincoli: se una istanza di E1 partecipa ad A4 non può partecipare ad A5 e viceversa

  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à

    Untitled