Esercizio 1

Si consideri il seguente schema di base di dati:

Robot(codice, modello, serie, colore, funzione, prezzo) Cliente(CF, cognome, nome, tel, dataNascita, città) Acquisto(CFcliente, codRobot, dataAcq, modPagamento)

Scrivere i comandi SQL per:

1. Creare la tabella Acquisto 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 Acquisto(
   	CFCliente VARCHAR(16),
   	codRobot NUMERIC(5),
   	dataAcq DATE,
   	modPagamento VARCHAR(20),
   	PRIMARY KEY(CFCliente, codRobot, dataAcq),
   	FOREIGN KEY CFCliente REFERENCES Cliente(CF)
   		ON UPDATE CASCADE
   		ON DELETE CASCADE,
   	FOREIGN KEY codRobot REFERENCES Robot(codice)
   		ON UPDATE CASCADE
   		ON DELETE RESTRICT
   );
   

2. Cancellare gli acquisti di Robot verdi avvenuti prima del 1/1/2015

   DELETE FROM Acquisto
   WHERE dataAcq < '2015-01-01' DATE AND codRobot IN (
   	SELECT codice FROM Robot JOIN Acquisto ON codice = codRobot
   	WHERE colore = 'verde'
   )
   

3. Aggiungere alla tabella Cliente la colonna email

   ALTER TABLE Cliente ADD COLUMN email VARCHAR(50) DEFAULT NULL
   

4. Aumentare del 10% il prezzo dei Robot di serie S1

   UPDATE Robot 
   SET prezzo = prezzo * 1.1
   WHERE serie = 'S1'
   

Esercizio 2

In riferimento allo schema proposto nell’Esercizio 1, formulare in SQL le interrogazioni per restituire:

1. Il CF ed il cognome dei clienti che hanno acquistato un robot con funzione “aspirapolvere” e un robot con funzione “lavapavimenti”

   SELECT CF, cognome FROM Cliente JOIN Acquisto ON (CFCliente = CF)
   WHERE codRobot IN (SELECT codice FROM Robot WHERE funzione = 'aspirapolvere'
   INTERSECT
   SELECT CF, cognome FROM Cliente JOIN Acquisto ON (CFCliente = CF)
   WHERE codRobot IN (SELECT codice FROM Robot WHERE funzione = 'lavapavimenti'
   

2. Il CF dei clienti che hanno acquistato tutti i robot della serie ‘AX500’

   SELECT CFCliente FROM Robot JOIN Acquisto ON (codRobot = codice)
   WHERE serie = 'AX500'
   GROUP BY CFCliente
   HAVING COUNT(DISTINCT(codRobot)) = (
   	SELECT COUNT(*) FROM Robot WHERE serie = 'AX500'
   )
   

3. Il codice e il modello dei robot acquistati da almeno 3 clienti di Milano

   SELECT codice, modello 
   FROM Robot JOIN Acquisto ON codice = codRobot JOIN Cliente ON CFCliente = CF
   WHERE città = 'Milano'
   GROUP BY codice, modello 
   HAVING COUNT(DISTINCT CF) >= 3
   

4. Il codice ed il modello dei robot di colore rosso di prezzo massimo

   SELECT codice, modello
   FROM Robot WHERE colore = 'rosso' AND prezzo >= ALL(
   	SELECT prezzo FROM Robot WHERE colore = 'rosso'
   )
   

5. Il codice fiscale e la città di clienti che non hanno mai fatto acquisti con modalità di pagamento Paypal

   SELECT DISTINCT CF, città
   FROM Cliente c JOIN Acquisto ON CF = CFCliente
   WHERE NOT EXIST(
   	SELECT * FROM Cliente cc JOIN Acquisto ON cc.CF = CFCliente
   	WHERE c.CF = cc.CF AND modPagamento = 'Paypal'
   )
   

Esercizio 3

Si consideri lo schema ER di seguito illustrato, dove la gerarchia di generalizzazione è totale/esclusiva ed il seguente carico di lavoro: lettura di E1 (50 volte al giorno)

Si richiede di:

1. In base al carico di lavoro, podurre uno schema ER ristrutturato giustificando, ove necessario, le scelte effettuate. Elencare in linguaggio naturale gli eventuali vincoli derivanti dalla ristrutturazione no rappresentabili nello schema ristrutturato

   

   Le entità E11 e E12 vengono “inglobate” dentro l’entità E1 che avrà quindi gli attributi a4, a5, a6, e l’attributo “tipo” che assumerà valori “E11” o “E12” (non può essere NULL). Se a4 e a5 sono NOT NULL, allora a6 dovrà essere NULL, e viceversa.

   Nell’entità E4 l’attributo composto a11 viene tradotto in attributo classico, accorpando a12 e a13.

   L’attributo a9 diventa un’entità con attributo a9.1 che la identifica. L’entità a9 è legata all’entità E3 attraverso un’associazione molti a molti.