Full exam for Basi di Dati in the Computer Engineering degree programme at Politecnico di Milano. The document covers: 1---------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE Prenotazione( CF CHAR(16) REFERENCES Cliente(CF) ON UPDATE CASCADE ON DELETE NO ACTION, Codice CHAR(32) REFERENCES Centro(Codice) ON
Full exam for Basi di Dati in the Computer Engineering degree programme at Politecnico di Milano. The document covers: 1---------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE Prenotazione( CF CHAR(16) REFERENCES Cliente(CF) ON UPDATE CASCADE ON DELETE NO ACTION, Codice CHAR(32) REFERENCES Centro(Codice) ON
Import quality: text was extracted directly from the original document.
Representative passages recognised in different parts of the material. The full extracted text remains available to search, while this compact preview makes the page easier to read.
1---------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE Prenotazione( CF CHAR(16) REFERENCES Cliente(CF) ON UPDATE CASCADE ON DELETE NO ACTION, Codice CHAR(32) REFERENCES Centro(Codice) ON UPDATE CASCADE ON DELETE NO ACTION, Data DATE, Prezzo Decimal(10,2) NOT NULL, //vedi nota! check (Prezzo>0), PRIMARY KEY(CF, Codice, Data) ) //NOTA sulla scelta del �po per i prezzi: float non è un �po esato e può accumulare errori //vedi ad esempio htps://floa�ng-point-gui.de/basic/ CREATE TABLE Prenotazione( CF CHAR(16), Codice CHAR(32), Data DATE, Prezzo Decimal(10,2) NOT NULL, PRIMARY KEY(CF, Codice, Data), FOREIGN KEY (CF) REFERENCES Cliente(CF) ON UPDATE CASCADE ON DELETE NO ACTION, FOREIGN KEY (Codice) REFERENCES Centro(Codice) ON UPDATE CASCADE ON DELETE NO ACTION ) 2---------------------------------------------------------------------------------------------------------------------------------- CREATE VIEW CentroNumPrenotazioni (co,ci,cp) AS ( SELECT C.Codice, C.Cita, count(*) as CountPrenotazioni FROM Prenotazioni P NATURAL JOIN Centro WHERE Data > ‘27/06/2023’ GROUP BY C.Codice, C.Cita) CREATE VIEW CitaMaxNumPrenotazioni (mci,mcp) AS ( SELECT ci, max(cp) as mcp FROM CentroNumPrenotazioni GROUP BY ci) SELECT co FROM CentroNumPrenotazioni WHERE (ci,cp) in (SELECT mci, mcp FROM CitaMaxNumPrenotazioni) SELECT C.Codice, C.Cita, count(*) as CountPrenotazioni FROM Prenotazioni P NATURAL JOIN Centro C WHERE Data > ‘27/06/2023’ GROUP BY C.Codice, C.Cita HAVING CountPrenotazioni >= ALL(SELECT count(*) FROM Prenotazioni P1 NATURAL JOIN Centro C1 WHERE Data > ‘27/06/2023’ AND C1.Cita = C.Cita) CREATE VIEW CentroNumPrenotazioni (co,ci,cp) AS ( SELECT C.Codice, C.Cita, count(*) as CountPrenotazioni…
First page of the document.
