TdE SOL BD1 01 07 2022 ITA Bozza soluzioni

Page 1

1) Specificare i comandi di creazione della tabella Affitto: CREATE TABELE AFFITTO( CF_Cliente CHAR(16) REFERENCES CLIENTE(CF) ON UPDATE CASCADE ON DELETE NO ACTION, ID_Immobile CHAR(10) REFERENCES IMMOBILE(ID) ON UPDATE CASCADE ON DELETE NO ACTION, Data_Inizio DATE, Data_Fine DATE NOT NULL, Costo_Totale INT NOT NULL, PRIMARY KEY(CF_Cliente, ID_Immobile, Data_Inizio) ) 2) Trovare per ogni città l’immobile che ha generato i maggiori guadagni: CREATE VIEW Guadagni(ID, Città, Guadagno_Totale) AS( SELECT ID, Città, SUM(Costo_Totale) FROM AFFITTO JOIN IMMOBILE ON ID_Immobile=ID GROUP BY ID, Città ) SELECT G1.Città, G1.ID FROM Guadagni AS G1 WHERE G1.Guadagno_Totale = (SELECT MAX(G2.Guadagno_Totale) FROM Guadagni AS G2 WHERE G1.Città=G2.Città) 3) Mostrare i clienti che hanno affittato tutte le tipologie di immobile almeno una volta: SELECT CF_Cliente FROM AFFITTO JOIN IMMOBILE ON ID_Immobile=ID GROUP BY CF_Cliente HAVING COUNT(DISTINCT Tipo) = (SELECT COUNT(DISTINCT Tipo) FROM IMMOBILE) 4) Si esprima il vincolo che verifica che non ci siano affitti con sovrapposizioni: CREATE ASSERTION Controllo_Sovrapposizioni CHECK( NOT EXIXSTS ( SELECT * FROM AFFITTO AS A1 JOIN AFFITTO AS A2 ON A1.ID_Immobile=A2.ID_Immobile WHERE (A1.Data_Inizio BETWEEN A2.Data_Inizio AND A2.Data_Fine) ) ) Nota: non qualificando la relazione temporale sull'inizio di A1 e A2, (A1.Data_Inizio BETWEEN A2.Data_Inizio AND A2.Data_Fine) è sufficiente (se A1 inizia prima è vero il prima, se A2 inizia dopo è vero il secondo... ma gli stessi due affitti faranno match scambiandosi i ruoli) AND (A1.Data_Inizio <> A2.Data_Inizio OR A1.CF<>A2.CF)