Full exam for Basi di Dati in the Computer Engineering degree programme at Politecnico di Milano. The document covers: 1) CREATE TABLE Componente( Codice VARCHAR(16) PRIMARY KEY , Nome VARCHAR(255) NOT NULL, Tipo ENUM('Strada','Piazza','Giardino') NOT NULL, Cap INT NOT NULL, -- VARCHAR(5) Dimensione INT NOT NULL, IDProge�sta VARCHAR(16) REFERENCES Proge�sta(ID) ON UPDATE CASCADE ON DELETE SET
Full exam for Basi di Dati in the Computer Engineering degree programme at Politecnico di Milano. The document covers: 1) CREATE TABLE Componente( Codice VARCHAR(16) PRIMARY KEY , Nome VARCHAR(255) NOT NULL, Tipo ENUM('Strada','Piazza','Giardino') NOT NULL, Cap INT NOT NULL, -- VARCHAR(5) Dimensione INT NOT NULL, IDProge�sta VARCHAR(16) REFERENCES Proge�sta(ID) ON UPDATE CASCADE ON DELETE SET
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 Componente( Codice VARCHAR(16) PRIMARY KEY , Nome VARCHAR(255) NOT NULL, Tipo ENUM('Strada','Piazza','Giardino') NOT NULL, Cap INT NOT NULL, -- VARCHAR(5) Dimensione INT NOT NULL, IDProge�sta VARCHAR(16) REFERENCES Proge�sta(ID) ON UPDATE CASCADE ON DELETE SET NULL ) CREATE TABLE Componente( Codice VARCHAR(16), Nome VARCHAR(255) NOT NULL, Tipo ENUM('Strada','Piazza','Giardino') NOT NULL, Cap INT NOT NULL, Dimensione INT NOT NULL, IDProge�sta VARCHAR(16), PRIMARY KEY(Codice), FOREIGN KEY (IDProge�sta) REFERENCES Proge�sta(ID) ON UPDATE CASCADE ON DELETE SET NULL ) 2) SELECT C1.Codice, C1.Nome FROM Componente C1 JOIN Incrocio I on C1.Codice = I.Componente1 JOIN Componente C2 on C2.Codice = I.Componente2 WHERE C1.Tipo = 'Piazza' AND C1.IdProge�sta is NULL AND C2.Tipo = 'Strada' GROUP BY C1.Codice HAVING COUNT(I.Componente2) >= 4 CREATE VIEW CodicePiazza (ID) AS ( SELECT Codice FROM Componente WHERE Tipo = ‘Piazza’ AND IdProge�sta is NULL) SELECT C1.Codice, C1.Nome FROM Componente C2 JOIN Incrocio I on C2.Codice = I.Componente2 WHERE C2.Tipo = 'Strada' AND C2.IdProge�sta is NULL AND I.Componente1 IN (SELECT ID FROM CodicePiazza) GROUP BY I.Componente1 HAVING COUNT(I.Componente2) >= 4 SELECT C1.Codice, C1.Nome FROM Componente C1 WHERE C1.Tipo = 'Strada' AND C1.IdProge�sta is NULL AND (SELECT COUNT(I.Componente2) FROM Incrocio I WHERE I.Componente1 = C1.Codice AND I.Componente2 IN (Select * FROM Componente WHERE Tipo = ‘Strada’) ) >= 4 3) SELECT C1.* FROM Componente C1 WHERE Tipo = 'Strada' AND C1.Codice IN (SELECT Componente1 FROM Incrocio WHERE Semaforo IS TRUE) AND NOT EXISTS (SELECT * FROM Incrocio I JOIN Componente C2 ON I.Componente2 = C2.Codice WHERE I.Componente1 = C1.Codice AND C2.Tipo = 'Giardino') SELECT C1.* FROM Componente C1 WHERE Tipo =…
First page of the document.
