Document information
- University
- Politecnico di Milano
- Degree programme
- Computer Engineering
- Subject
- Basi di Dati
- Academic year
- 2022-2023
- Classification
- Exam · Full exam
- Content
- Solution only
- Original format
- Text
- Searchable text
← Back
ExamFull examSolution only
TdE BD1 27 06 23 PARTE1 SOLUZIONI
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
Basi di DatiFull exam
Document information
What's included in this study material
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.
Extracted content from the 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.
Page 1
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…
Preview
First page of the document.