Progettazione database. ‘Gestione ordini’

In questo post realizzeremo un semplice progetto di un database per la gestione degli ordini. Esso ci permetterà di misurarci con un problema che nella struttura si rifà alla seconda prova dell’Esame di Stato di Informatica degli Istituti Tecnici. Questo progetto suggerisce uno schema operativo sintetico che può essere seguito nello svolgimento della prima parte della prova d’esame. Si tenga presente che rispetto a quest’ultima manca la parte relativa all’implementazione delle pagine web dinamiche, che verrà affrontata in un altro post.

Descrizione del problema

Una catena di negozi è costituita da un certo numero di punti vendita situati in città diverse. Tutti i punti vendita ritirano gli articoli da vendere da un unico magazzino centrale. Gli ordini inoltrati verso il magazzino centrale sono caratterizzati da un codice identificativo, dalla data dell’ordine e comprendono gli articoli da ritirare. Gli articoli appartengono a diverse categorie merceologiche, ad esempio “alimentari”, “abbigliamento”, “informatica”, ecc. Per una gestione più efficiente degli ordini, si vuole dotare l’azienda di un sistema di gestione elettronico degli ordini, in modo che i punti vendita possano effettuare gli ordini attraverso il sito web della catena di negozi. In particolare si vuole che quando un punto vendita effettua l’ordine, deve essere possibile effettuarlo solo sugli articoli effettivamente disponibili in magazzino in quel momento.

Fatte le opportune ipotesi aggiuntive, si sviluppi:

L’analisi della realtà di riferimento descritta.
La progettazione concettuale della realtà descritta attraverso la produzione di uno schema (ad esempio ER, Entity‐Relationship) riportante gli attributi di ogni entità, il tipo di ogni associazione e i suoi eventuali attributi.
La traduzione dello schema concettuale in uno schema fisico (schema logico relazionale).
La definizione in linguaggio SQL di almeno tre relazioni (tabelle), tra loro correlate, definite nello schema logico.
Le seguenti interrogazioni espresse in algebra relazionale e/o in linguaggio SQL:

Elenco degli articoli non disponibili in magazzino.
Elenco degli ordini provenienti dalla città di Potenza, con codice e data dell’ordine.
Elenco degli articoli disponibili in magazzino che appartengono alla categoria merceologica “Pc e periferiche”.
Elenco degli articoli che compongono l’ordine che ha il codice: 1345.
Elenco di tutti gli ordini effettuati nel mese di novembre dal punto vendita “ShopPro Srl” della città di Potenza.
Numero di ordini effettuati da ciascun punto vendita della città di Potenza in ordine alfabetico rispetto alla ragione sociale.
Il/i punto/i vendita della città di Potenza che ha/hanno effettuato più ordini.

Soluzione proposta

1. Analisi ed ipotesi aggiuntive

Analizzando la realtà di riferimento è possibile individuare le seguenti entità fondamentali:

PuntoVendita – per tutte le istanze dei punti vendita della realtà in esame.
Ordine – per tutte le istanze degli ordini inoltrati dai punti vendita al magazzino centrale.
Articolo – per il “catalogo” degli articoli con cui è possibile comporre gli ordini fra quelli disponibili in magazzino.

Allo scopo di ridurre la ridondanza, inoltre, conviene introdurre anche le seguenti entità:

Categoria – per le categorie merceologiche a cui ciascun articolo appartiene.
Città – per le città che ospitano i punti vendita.

Per la definizione delle associazioni che legano le entità individuate, si può osservare che:

Ciascun punto vendita deve trovarsi in una città e ciascuna città può ospitare più punti vendita.
Ciascun punto vendita può inoltrare più ordini e ciascun ordine deve essere inoltrato da un punto vendita.
Ciascun ordine deve comprendere uno o più articoli (ciascuno in una certa quantità) e ciascun articolo può essere compreso in più ordini.
Ciascun articolo appartiene ad una categoria merceologica e a ciascuna categoria possono appartenere più articoli.

Lo schema E/R delle entità, delle associazioni e dei relativi attributi fondamentali che derivano dall’analisi effettuata, è quello mostrato nella figura seguente, in cui in particolare:

Per soddisfare la richiesta che l’ordine si possa effettuare solo su articoli effettivamente disponibili, nell’entità articolo è stato introdotto l’attributo giacenza (che fornisce il numero di pezzi disponibili in magazzino). Questo attributo farà si che prima della conferma dell’ordine, ci sia la possibilità di verificare che la quantità richiesta di quell’articolo sia effettivamente disponibile in magazzino.
L’attributo quantità è stato assegnato all’associazione che lega Ordine con Articolo, in quanto esso non appartiene in maniera esclusiva a nessuna delle due entità, ma dipende da entrambe. Quanti sono i pezzi di un certo articolo in un ordine, infatti, dipende sia da qual è l’articolo, sia da qual è l’ordine che si considera.

2. Schema E/R

Lo schema E/R ottenuto con l’analisi precedente viene mostrato nella figura seguente. Esso risulta essere già normalizzato fino alla terza forma normale (3FN).

3. Schema logico

Applicando le regole di derivazione dello schema logico al precedente schema E/R, si ottengono le seguenti definizioni delle tabelle:

citta(id, nome)

puntiVendita(id, denominazione, indirizzo, telefono, email, id_citta)

ordini(id, data, id_puntoVendita)

dettaglio_ordini(id_ordine, id_articolo, quantita)

Essa è una tabella aggiuntiva necessaria per rendere possibile l’implementazione dell’associazione N a N fra le entità Ordine e Articolo nel database.

articoli(id, descrizione, giacenza, idCategoria)

categorie(id, descrizione)

4. Definizione delle tabelle in linguaggio SQL

CREATE TABLE citta (
	id INT(10)PRIMARY KEY AUTO_INCREMENT,
	nome VARCHAR(30) NOT NULL
);

CREATE TABLE puntiVendita (
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	ragioneSociale VARCHAR(50) NOT NULL,
	indirizzo VARCHAR(40),
	telefono VARCHAR(30),
	email VARCHAR(40),
	idCitta INT(10),
	FOREIGN KEY(idCitta) REFERENCES citta(id)
);

CREATE TABLE ordini (
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	data DATE NOT NULL,
	idPuntoVendita INT(10),
	FOREIGN KEY(idPuntoVendita) REFERENCES puntiVendita(id)
);

CREATE TABLE categorie (
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	descrizione VARCHAR(50) NOT NULL
);
CREATE TABLE articoli (
	id INT(10) PRIMARY KEY AUTO_INCREMENT,
	descrizione VARCHAR(50) NOT NULL,
	giacenza INT(5),
	idCategoria INT(10) NOT NULL,
	FOREIGN KEY(idCategoria) REFERENCES categorie(id)
);

CREATE TABLE dettaglio_ordini (
	id_ordine INT(10),
	id_articolo INT(10),
	quantita INT(5),
	PRIMARY KEY(id_ordine, id_articolo),
	FOREIGN KEY(id_ordine) REFERENCES ordini(id),
	FOREIGN KEY(id_articolo) REFERENCES articoli(id)
);

CREATE TABLE citta (

id INT(10)PRIMARY KEY AUTO_INCREMENT,

nome VARCHAR(30) NOT NULL

);

CREATE TABLE puntiVendita (

id INT(10) PRIMARY KEY AUTO_INCREMENT,

ragioneSociale VARCHAR(50) NOT NULL,

indirizzo VARCHAR(40),

telefono VARCHAR(30),

email VARCHAR(40),

idCitta INT(10),

FOREIGN KEY(idCitta) REFERENCES citta(id)

);

CREATE TABLE ordini (

id INT(10) PRIMARY KEY AUTO_INCREMENT,

data DATE NOT NULL,

idPuntoVendita INT(10),

FOREIGN KEY(idPuntoVendita) REFERENCES puntiVendita(id)

);

CREATE TABLE categorie (

id INT(10) PRIMARY KEY AUTO_INCREMENT,

descrizione VARCHAR(50) NOT NULL

);

CREATE TABLE articoli (

id INT(10) PRIMARY KEY AUTO_INCREMENT,

descrizione VARCHAR(50) NOT NULL,

giacenza INT(5),

idCategoria INT(10) NOT NULL,

FOREIGN KEY(idCategoria) REFERENCES categorie(id)

);

CREATE TABLE dettaglio_ordini (

id_ordine INT(10),

id_articolo INT(10),

quantita INT(5),

PRIMARY KEY(id_ordine, id_articolo),

FOREIGN KEY(id_ordine) REFERENCES ordini(id),

FOREIGN KEY(id_articolo) REFERENCES articoli(id)

);

5. Interrogazioni espresse in linguaggio SQL

a) Elenco degli articoli non disponibili in magazzino.

SELECT *
FROM articoli
WHERE giacenza = 0;

SELECT *

FROM articoli

WHERE giacenza = 0;

b) Elenco degli ordini provenienti dalla città di Potenza, con codice e data dell’ordine.

SELECT ordini.id, data
FROM ordini, puntiVendita, citta
WHERE ordini.idPuntoVendita = puntiVendita.id AND
puntiVendita.idCitta = citta.id AND
citta.nome = “Potenza”;

SELECT ordini.id, data

FROM ordini, puntiVendita, citta

WHERE ordini.idPuntoVendita = puntiVendita.id AND

puntiVendita.idCitta = citta.id AND

citta.nome = “Potenza”;

oppure:

SELECT ordini.id, data
FROM ordini
INNER JOIN puntiVendita ON ordini.idPuntoVendita = puntiVendita.id
INNER JOIN citta ON puntiVendita.idCitta = citta.id
WHERE citta.nome = “Potenza”;

SELECT ordini.id, data

FROM ordini

INNER JOIN puntiVendita ON ordini.idPuntoVendita = puntiVendita.id

INNER JOIN citta ON puntiVendita.idCitta = citta.id

WHERE citta.nome = “Potenza”;

c) Elenco degli articoli disponibili in magazzino che appartengono alla categoria merceologica “Pc e periferiche”.

SELECT articoli.id, articoli.descrizione
FROM articoli, categorie
WHERE articoli.idCategoria = categorie.id AND
categorie.descrizione LIKE “%Pc%perif%” AND
articoli.giacenza >0;

SELECT articoli.id, articoli.descrizione

FROM articoli, categorie

WHERE articoli.idCategoria = categorie.id AND

categorie.descrizione LIKE “%Pc%perif%” AND

articoli.giacenza >0;

oppure:

SELECT articoli.id, articoli.descrizione
FROM articoli INNER JOIN categorie ON articoli.idCategoria = categorie.id
WHERE categorie.descrizione LIKE “%Pc%perif%” AND articoli.giacenza >0;

SELECT articoli.id, articoli.descrizione

FROM articoli INNER JOIN categorie ON articoli.idCategoria = categorie.id

WHERE categorie.descrizione LIKE “%Pc%perif%” AND articoli.giacenza >0;

d) Elenco degli articoli che compongono l’ordine che ha il codice: 1345.

SELECT articoli.descrizione, quantita
FROM articoli, dettaglio_ordini 
WHERE articoli.id = id_articolo
AND id_ordine = 1345;

SELECT articoli.descrizione, quantita

FROM articoli, dettaglio_ordini

WHERE articoli.id = id_articolo

AND id_ordine = 1345;

oppure:

SELECT articoli.descrizione, quantita
FROM articoli INNER JOIN dettaglio_ordini ON articoli.id = id_articolo
WHERE ordini.id = 1345;

SELECT articoli.descrizione, quantita

FROM articoli INNER JOIN dettaglio_ordini ON articoli.id = id_articolo

WHERE ordini.id = 1345;

e) Elenco di tutti gli ordini effettuati nel mese di novembre dal punto vendita “ShopPro Srl” della città di Potenza.

SELECT ordini.id, data
FROM ordini, puntiVendita, citta
WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id
AND ragioneSociale LIKE “%ShopPro%” AND citta.nome = “Potenza”
AND YEAR(data) = YEAR(CURDATE()) AND MONTH(data) = 11;

SELECT ordini.id, data

FROM ordini, puntiVendita, citta

WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id

AND ragioneSociale LIKE “%ShopPro%” AND citta.nome = “Potenza”

AND YEAR(data) = YEAR(CURDATE()) AND MONTH(data) = 11;

oppure:

SELECT ordini.id, data
FROM ordini INNER JOIN puntiVendita ON idPuntoVendita = puntiVendita.id
INNER JOIN citta ON idCitta = citta.id
WHERE ragioneSociale LIKE “%ShopPro%” AND citta.nome = “Potenza”
AND YEAR(data) = YEAR(CURDATE()) AND MONTH(data) = 11;

SELECT ordini.id, data

FROM ordini INNER JOIN puntiVendita ON idPuntoVendita = puntiVendita.id

INNER JOIN citta ON idCitta = citta.id

WHERE ragioneSociale LIKE “%ShopPro%” AND citta.nome = “Potenza”

AND YEAR(data) = YEAR(CURDATE()) AND MONTH(data) = 11;

f) Numero di ordini effettuati da ciascun punto vendita della città di Potenza in ordine alfabetico rispetto alla ragione sociale.

SELECT puntiVendita.id, ragioneSociale AS “Punto vendita”, COUNT(*) AS “Ordini effettuati”
FROM ordini, puntiVendita, citta
WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id
AND citta.nome = “Potenza”
GROUP BY puntiVendita.id
ORDER BY ragioneSociale;

SELECT puntiVendita.id, ragioneSociale AS “Punto vendita”, COUNT(*) AS “Ordini effettuati”

FROM ordini, puntiVendita, citta

WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id

AND citta.nome = “Potenza”

GROUP BY puntiVendita.id

ORDER BY ragioneSociale;

g) Il punto vendita della città di Potenza che ha effettuato più ordini.

Per la scrittura di questa interrogazione può essere utile considerare la query del punto precedente. Essa, infatti, restituisce una tabella (che chiamo per comodità T) con il seguente schema:

T(id, ragione_sociale, ordini_effettuati)

che permette di realizzare l’interrogazione g) con una query annidata che abbia la seguente struttura:

SELECT T.ragioneSociale
FROM T
WHERE ordini_effettuati = (
	SELECT MAX(ordini_effettuati)
	FROM T
);

SELECT T.ragioneSociale

FROM T

WHERE ordini_effettuati = (

SELECT MAX(ordini_effettuati)

FROM T

);

Per ottenere la query definitiva non ci resta che sostituire a T la query del punto precedente:

SELECT T2.ragioneSociale
FROM (
	SELECT puntiVendita.id , ragioneSociale, COUNT(*) AS ordini_effettuati
	FROM ordini, puntiVendita, citta
	WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id AND citta.nome = "Potenza"
	GROUP BY puntiVendita.id
	) AS T2
WHERE ordini_effettuati = (
	SELECT MAX(ordini_effettuati)
	FROM (
		SELECT puntiVendita.id, COUNT(*) AS ordini_effettuati
		FROM ordini, puntiVendita, citta
		WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id AND citta.nome = "Potenza"
		GROUP BY puntiVendita.id
		) AS T1
	);

SELECT T2.ragioneSociale

FROM (

SELECT puntiVendita.id , ragioneSociale, COUNT(*) AS ordini_effettuati

FROM ordini, puntiVendita, citta

WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id AND citta.nome = "Potenza"

GROUP BY puntiVendita.id

) AS T2

WHERE ordini_effettuati = (

SELECT MAX(ordini_effettuati)

FROM (

SELECT puntiVendita.id, COUNT(*) AS ordini_effettuati

FROM ordini, puntiVendita, citta

WHERE idPuntoVendita = puntiVendita.id AND idCitta = citta.id AND citta.nome = "Potenza"

GROUP BY puntiVendita.id

) AS T1

);

Articoli che ti potrebbero interessare