Modellazione ER e SQL:
Amministrazione di stabili

Il seguente esercizio è dato come soluzione alla traccia: TASK – Modelazione ER e MySQL 08/07/2022 – Percorso di Data Engineer

La soluzione è stata sviluppata e gentilmente resa disponibile per tutti gli studenti da Claudia Micolaucich.

 

TRACCIA:

L’amministrazione stabili Benvenuto opera nella città di Trieste, dove amministra condomini. La società si compone di cinque persone (tutte donne, un boost alla disoccupazione femminile!) e pensa di espandersi. Ogni persona nell’azienda possiede un badge, di cui si conosce la data di rilascio e le aree a cui dà accesso. Di ogni lavoratore si vuole sapere l’email, il nome, il cognome e il ruolo ricoperto in azienda. La società amministra condomini e di ogni condominio viene registrato un codice identificativo, l’indirizzo, e il numero di condomini (persone) che ospita. I condomini possono necessitare lavorazioni. Di ogni lavorazione si sa la data di inizio della procedura (quando viene presa a carico) e una descrizione (che viene aggiornata per tener traccia della fase di completamento del progetto). Le fasi delle lavorazioni dipendono dal tipo di lavorazione e sono sempre diverse. L’unica fase in comune è quella finale, quando la lavorazione viene conclusa. Un condominio può avere una o più lavorazioni in corso. Ad ogni lavorazione è assegnato un responsabile, che agisce da focal point per le comunicazioni esterne. Per ogni lavorazione bisogna richiedere un preventivo ad almeno tre fornitori. Di ogni preventivo si vuole sapere il fornitore ad esso associato e il valore del preventivo.

 

IL DIAGRAMMA ER:

Il seguente diagramma è consultabile o modificabile direttamente dal suo file sorgente con il programma draw.io scaricabile dall’indirizzo https://www.diagrams.net/ o utilizzabile direttamente sul sito.

SCARICA IL DIAGRAMMA: CLICCA QUI

 

 

DDL – Data Definition Language

 

DROP DATABASE IF EXISTS Amministrazione_stabile;
CREATE DATABASE Amministrazione_stabile;
USE Amministrazione_stabile;

CREATE TABLE Badge (
  codice varchar(20) NOT NULL PRIMARY KEY,
  area_accesso varchar(300) NOT NULL,
  data_rilascio datetime DEFAULT CURRENT_TIMESTAMP
 );

CREATE TABLE Condominio (
  codice varchar(20) NOT NULL PRIMARY KEY,
  indirizzo varchar(500) NOT NULL,
  numero_condomini int(11) NOT NULL
);

CREATE TABLE Responsabile (
  email varchar(100) NOT NULL PRIMARY KEY,
  nome varchar(100) NOT NULL,
  cognome varchar(100) NOT NULL,
  ruolo varchar(20) NOT NULL,
  cod_badge varchar(20),
  FOREIGN KEY (cod_badge) REFERENCES Badge (codice) ON DELETE SET NULL
);

CREATE TABLE Lavorazione (
  codice varchar(20) NOT NULL PRIMARY KEY,
  fase TEXT NOT NULL,
  completato varchar(1) NOT NULL CHECK (completato in ("s", "n")),
  data_inizio datetime DEFAULT CURRENT_TIMESTAMP,
  responsabile_id varchar(100) NOT NULL,
  condominio_id varchar(20) NOT NULL,
  FOREIGN KEY (responsabile_id) REFERENCES Responsabile(email) ON DELETE CASCADE,
  FOREIGN KEY (condominio_id) REFERENCES Condominio(codice) ON DELETE CASCADE
);

CREATE TABLE Preventivo(
  codice VARCHAR(20) NOT NULL PRIMARY KEY,
  fornitore VARCHAR(250) NOT NULL,
  data_prev DATETIME DEFAULT current_timestamp,
  stima FLOAT NOT NULL
);
  
CREATE TABLE Preventivo_Lavorazione(
  codice_preventivo VARCHAR(20),
  codice_lavorazione VARCHAR(20),
  FOREIGN KEY (codice_preventivo) REFERENCES Preventivo(codice),
  FOREIGN KEY (codice_lavorazione) REFERENCES Lavorazione(codice),
  PRIMARY KEY (codice_preventivo, codice_lavorazione)
);

 

DML – Data Manipilation Language

 

INSERT INTO Badge(codice, area_accesso) VALUES
("AAAA", "reception, uffici, archivio"),
("AAAB", "reception, uffici, archivio"),
("AABA", "reception, uffici"),
("AABB", "reception, uffici"),
("ABAA", "reception");

INSERT INTO Condominio VALUES
("C1", "Via Aleardi", 8),
("C2", "Via Alonsi", 30),
("C3", "Via Scorcola", 3),
("C4", "Via Commerciale", 22),
("C5", "Via Unità", 7),
("C6", "Via Speranza", 13);

INSERT INTO Responsabile VALUES
("Michela@gmail.com", "Miki", "Benvenuto", "socia", "AAAA"),
("Erika@gmail.com", "Erika", "Pu", "socia", "AAAB"),
("Samanta@gmail.com", "Sammy", "Nonso", "impiegata", "AABA"),
("Claudia@gmail.com", "Claudia", "Nonricordo", "apprendista", "AABB"),
("Agnese@gmail.com", "Agnese", "Lanuova", "tirocinante", "ABAA");

INSERT INTO Lavorazione VALUES
("JJJJ", "Valutazione energetica, contatto fornitore", "n", "2022-07-01 09:00:00", "Michela@gmail.com", "C1"),
("JJJK", "Valutazione energetica, contatto fornitore", "n", "2022-07-13 09:00:00", "Michela@gmail.com", "C2"),  
("KJJJ", "Fognature", "s", "2022-06-01 09:00:00", "Erika@gmail.com", "C1"),
("KKJJ", "Fognature, contatto fornitore", "n", "2022-07-13 09:00:00", "Samanta@gmail.com", "C3"),
("LLJJ", "Facciata nuova", "n", "2022-07-20 09:00:00", "Claudia@gmail.com", "C4"),  
("LLLJ", "Facciata, contatto fornitore, firma", "n", "2022-07-01 09:00:00", "Claudia@gmail.com", "C1"),
("JJKK", "Valutazione energetica", "s", "2022-07-01 09:00:00", "Samanta@gmail.com", "C6");

INSERT INTO Preventivo (codice, fornitore, stima) VALUES
("CCCC", "Costruzione SRL", 1889.98),
("CCCD", "Costruzione SRL", 15000),
("CCDD", "Costruzione SRL", 3000),
("CDDD", "QSW", 7896.36),
("DDDD", "PPP & co", 1500),
("FDDD", "PPP & co", 150);

INSERT INTO Preventivo_Lavorazione VALUES
("CCCC", "LLJJ"),
("CCCD", "KKJJ"),
("CCDD", "JJJJ"),
("CDDD", "JJJK"),
("DDDD", "KJJJ"),
("FDDD", "JJKK");

 

QL – Query Language

 

SELECT * FROM Preventivo
JOIN Preventivo_Lavorazione ON Preventivo.codice=Preventivo_Lavorazione.codice_preventivo
JOIN Lavorazione ON Lavorazione.codice=Preventivo_Lavorazione.codice_lavorazione
JOIN Responsabile ON Responsabile.email=Lavorazione.responsabile_id
JOIN Condominio ON Condominio.codice = Lavorazione.condominio_id
JOIN Badge ON Badge.codice= Responsabile.cod_badge;

SELECT ROUND(AVG(Preventivo.stima), 2) AS Stima_media, Condominio.codice, Condominio.indirizzo FROM Preventivo
JOIN Preventivo_Lavorazione ON Preventivo.codice=Preventivo_Lavorazione.codice_preventivo
JOIN Lavorazione ON Lavorazione.codice=Preventivo_Lavorazione.codice_lavorazione
JOIN Responsabile ON Responsabile.email=Lavorazione.responsabile_id
JOIN Condominio ON Condominio.codice = Lavorazione.condominio_id
JOIN Badge ON Badge.codice= Responsabile.cod_badge
GROUP BY Condominio.codice;

SELECT COUNT(Lavorazione.codice) AS Numero_lavorazioni_in_corso, Condominio.codice, Condominio.indirizzo FROM Preventivo
JOIN Preventivo_Lavorazione ON Preventivo.codice=Preventivo_Lavorazione.codice_preventivo
RIGHT JOIN Lavorazione ON Lavorazione.codice=Preventivo_Lavorazione.codice_lavorazione
JOIN Responsabile ON Responsabile.email=Lavorazione.responsabile_id
JOIN Condominio ON Condominio.codice = Lavorazione.condominio_id
JOIN Badge ON Badge.codice= Responsabile.cod_badge
GROUP BY Condominio.codice;

SELECT COUNT(Condominio.numero_condomini) AS Numero_condomini_a_carico, Responsabile.nome FROM Responsabile
LEFT JOIN Lavorazione ON Responsabile.email = Lavorazione.responsabile_id
JOIN Condominio ON Lavorazione.condominio_id=Condominio.codice
GROUP BY Responsabile.email;

SELECT Preventivo.stima, Condominio.codice, Condominio.indirizzo FROM Preventivo
JOIN Preventivo_Lavorazione ON Preventivo.codice=Preventivo_Lavorazione.codice_preventivo
JOIN Lavorazione ON Preventivo_Lavorazione.codice_lavorazione=Lavorazione.codice
JOIN Condominio ON Lavorazione.condominio_id=Condominio.codice
WHERE Preventivo.stima>5000;

SELECT Preventivo.fornitore, ROUND(AVG(Preventivo.stima), 2) FROM Preventivo
WHERE Preventivo.fornitore LIKE "Costruzione%"
GROUP BY Preventivo.fornitore;

 

COMMENTI SULLA SOLUZIONE:

 

1. La tabella di appoggio Preventivo_Lavorazione può avere un vincolo di unicità multiplo del tipo UNIQUE anziché PRIMARY KEY poiché non ci sono altri campi al di fuori delle FOREIGN KEY.
2. La soluzione benché corretta dal punto di vista realizzativo soffre di problemi di performances durante la navigazione tramite i JOIN, sarebbe più corretto in questo caso utilizzare delle chiavi AUTO_INCREMENT disponibili in MySQL.