Modellazione ER e SQL:
Amministrazione di una mensa

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 uno studente che preferisce rimanere anonimo.

 

TRACCIA:

Dobbiamo disegnare il database per una compagnia che gestisce la mensa di una scuola.
Ogni giorno l’azienda prepara il pranzo con portate divise in primi, secondi, frutta, bevande.
Ogni piatto ha un nome e un prezzo.
Ogni piatto inoltre ha un certo numero di ingredienti e può essere vegetariano o vegano.
Per i piatti e per i singoli ingredienti conserviamo nome e codice del fornitore.
Ogni fornitore ha un indirizzo e una o più persone di riferimento, ognuna con nome, cognome e numero di telefono.
Per ogni giorno memorizziamo gli ordini dei prodotti (piatti o ingredienti) ai fornitori, con data, quantità e prezzo.

 

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

 

CREATE DATABASE IF NOT EXISTS mensa;
USE mensa;

CREATE TABLE fornitore (
  fornitoreID INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(45) NOT NULL,
  indirizzo VARCHAR(45) NOT NULL,
  PRIMARY KEY (fornitoreID)
);

CREATE TABLE prodotto (
  prodottoID INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(45) NOT NULL,
  fornitoreRIF INT NULL DEFAULT NULL,
  prezzo DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (prodottoID),
  FOREIGN KEY (fornitoreRIF)
  REFERENCES fornitore (fornitoreID)
  ON DELETE CASCADE
);

CREATE TABLE piatto (
  piattoID INT NOT NULL AUTO_INCREMENT,
  prezzo DECIMAL(10,2) NOT NULL,
  categoria VARCHAR(45) NOT NULL,
  vegetariano TINYINT(1) NOT NULL,
  vegano TINYINT(1) NOT NULL,
  allergia VARCHAR(45) NULL DEFAULT NULL,
  FOREIGN KEY (piattoID)
  REFERENCES prodotto (prodottoID)
  ON DELETE CASCADE

);


CREATE TABLE ingrediente (
  ingredienteID INT NOT NULL AUTO_INCREMENT,
  FOREIGN KEY (ingredienteID)
  REFERENCES prodotto (prodottoID)
  ON DELETE CASCADE
);


CREATE TABLE composizione_piatto (
  piattoRIF INT NOT NULL,
  ingredienteRIF INT NOT NULL,
  PRIMARY KEY (piattoRIF, ingredienteRIF),
  FOREIGN KEY (piattoRIF)
  REFERENCES piatto (piattoID)
  ON DELETE CASCADE, 
  FOREIGN KEY (ingredienteRIF)
  REFERENCES ingrediente (ingredienteID)
  ON DELETE CASCADE
)
;

CREATE TABLE ordine (
  ordineID INT NOT NULL AUTO_INCREMENT,
  data DATETIME NOT NULL,
  prezzo_totale DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (ordineID)
);

CREATE TABLE composizione_ordine (
  ordineRIF INT NOT NULL,
  prodottoRIF INT NOT NULL,
  quantità SMALLINT NOT NULL,
  PRIMARY KEY (ordineRIF, prodottoRIF),
  FOREIGN KEY (ordineRIF)
  REFERENCES ordine (ordineID)
  ON DELETE CASCADE,
  FOREIGN KEY (prodottoRIF)
  REFERENCES prodotto (prodottoID)
  ON DELETE CASCADE
);

CREATE TABLE contatti (
  contattoID INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(45) NOT NULL,
  cognome VARCHAR(45) NOT NULL,
  telefono CHAR(11) NOT NULL,
  fornitoreRIF INT NOT NULL,
  PRIMARY KEY (contattoID),
  FOREIGN KEY (fornitoreRIF)
  REFERENCES fornitore (fornitoreID)
  ON DELETE CASCADE
);

 

DML – Data Manipilation Language

 

INSERT INTO fornitore VALUES 
(1,"Anicale","Via Ilario 55"),
(2,"Foodilo","Strada Pellegrino 2 Piano 3"),
(3,"Gasto","Piazza Russo 51"),
(4,"Vivafood","Strada Bianchi 527"),
(5,"Foodive","Contrada Mazza 14");

INSERT INTO ordine VALUES 
(1,"2022-10-10 00:00:00",4),
(2,"2022-10-11 00:00:00",5),
(3,"2022-10-12 00:00:00",6),
(4,"2022-10-13 00:00:00",7),
(5,"2022-10-14 00:00:00",8),
(6,"2022-10-15 00:00:00",9),
(7,"2022-10-16 00:00:00",0),
(8,"2022-10-17 00:00:00",7),
(9,"2022-10-18 00:00:00",6),
(10,"2022-10-19 00:00:00",4);

INSERT INTO contatti VALUES 
(1,"Brigida","Baresi","318 1044541",2),
(2,"Elda","Endrizzi","325 8055974",4),
(3,"Flavio","Lorenzo","392 9325974",1),
(4,"Donato","Lombardo","360 3361875",5),
(5,"Libero","Marcelo","322 1506395",3),
(6,"Luana","Endrizzi","366 5733412",2),
(7,"Enrica","Mazzi","389 3975034",3);

INSERT INTO prodotto VALUES 
(1,"spaghetti alla carbonara",1,7),
(2,"insalata di riso",2,7),
(3,"polpette di carne",1,7),
(4,"spezzatino",4,8),
(5,"macedonia",3,10),
(6,"arancia",5,4),
(7,"mela",5,4),
(8,"acqua",4,1),
(9,"sprite",1,2),
(10,"acqua frrizzante",2,1),
(11,"uova",1,1),
(12,"guanciale",2,2),
(13,"riso arborio",3,4),
(14,"pomodori ramati",4,2),
(15,"peperoni rossi",5,2),
(16,"olive nere",3,2),
(17,"manzo",3,6),
(18,"pane",4,1),
(19,"carne bovina",5,4),
(20,"sedano",2,3),
(21,"cipolle",2,2),
(22,"carote",1,3),
(23,"spaghetti",1,2);

INSERT INTO piatto VALUES 
(1,9,"primo",0,0,NULL),
(2,7,"secondo",1,1,NULL),
(3,8,"secondo",0,0,"si"),
(4,9,"secondo",0,0,NULL),
(5,10,"frutta",1,1,NULL);

INSERT INTO ingrediente VALUES (6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23);

INSERT INTO composizione_ordine VALUES (1,1,9),(1,16,8),(2,2,7),(2,3,5),(2,7,6),(3,1,7),(4,5,8),(5,3,5),(5,23,1),(6,8,2),(7,4,3),(8,5,2),(9,5,4),(10,2,7),(10,8,5),(10,22,9);

INSERT INTO composizione_piatto VALUES (1,11),(1,12),(1,23),(2,13),(2,14),(2,15),(2,16),(3,17),(3,18),(3,11),(4,24),(4,19),(4,20),(4,21),(4,22),(5,6),(5,7);

 

QL – Query Language

 

# selezionare i contatti della ditta Gasto
SELECT *
FROM fornitore JOIN contatti
ON fornitore.fornitoreID=contatti.fornitoreRIF
WHERE fornitore.nome="gasto"

# ordinare le ditte in base a quanto a quanto abbiamo speso presso ciascuna
SELECT fornitoreID,fornitore.nome, SUM(prezzo*composizione_ordine.quantità) AS spesa_totale
FROM fornitore JOIN prodotto ON fornitore.fornitoreID = prodotto.fornitoreRIF
JOIN composizione_ordine ON composizione_ordine.prodottoRIF=prodotto.prodottoID
GROUP BY fornitoreID

# ordinare i piatti da quello con più ingredienti a quello con meno
SELECT prodottoID, nome, COUNT(*) AS "numero_ingredienti"
FROM prodotto JOIN composizione
ON prodotto.prodottoID=composizione.piattoRIF
GROUP BY nome
ORDER BY COUNT(nome) desc

# selezionare gli ordini effettuati in un certo intervallo temporale
SELECT *
FROM ordine
WHERE ordine.`data` BETWEEN "2022-10-12" AND "2022-10-14"

 

COMMENTI SULLA SOLUZIONE:

 

1. Durante le operazioni di inserimento dei record, quindi parlando di DML bisogna sempre introdurre per motivi di sicurezza (soprattutto quando ci si appoggerà ad un software per l’operatività) di introdurre i nomi delle colonne ed evitare di andare a modificare tutte le colonne in maniera ordinata dal db.
2. Dal punto subito precedente ne deriva l’impossibilità di effettuare l’inserimento di valori auto-incrementali..