-- =====================================================================
--
-- nanoMAG
--
-- Gestione di un magazzino didattico.
--
-- =====================================================================
--
-- Creazione della relazione "Articoli", contenente l'anagrafica
-- degli articoli di magazzino.
--
CREATE TABLE Articoli (articolo INTEGER NOT NULL,
descrizione CHAR(30) NOT NULL,
um CHAR(2) DEFAULT 'pz',
listino NUMERIC(8,2) DEFAULT 0,
scorta_min NUMERIC(12,3) DEFAULT 0,
PRIMARY KEY (articolo));
--
-- Creazione della relazione "Causali", contenente l'elenco delle
-- causali di magazzino, ovvero le descrizioni dei movimenti,
-- con la specificazione se trattasi di carico o di scarico.
--
CREATE TABLE Causali (causale INTEGER NOT NULL,
descrizione CHAR(20) NOT NULL,
var NUMERIC(1) DEFAULT 0,
PRIMARY KEY (causale));
--
-- Creazione della relazione "Fornitori", contenente l'anagrafica
-- dei fornitori.
--
CREATE TABLE Fornitori (fornitore INTEGER NOT NULL,
ragione_sociale CHAR(30) NOT NULL,
indirizzo CHAR(30) NOT NULL,
cap CHAR(9),
citta CHAR(30) NOT NULL,
pr CHAR(2) DEFAULT '',
telefono CHAR(20) DEFAULT '',
fax CHAR(20) DEFAULT '',
cf_pi CHAR(30) DEFAULT '',
PRIMARY KEY (fornitore));
--
-- Creazione della relazione "Clienti", contenente l'anagrafica
-- dei clienti.
--
CREATE TABLE Clienti (cliente INTEGER NOT NULL,
ragione_sociale CHAR(30) NOT NULL,
indirizzo CHAR(30) NOT NULL,
cap CHAR(9),
citta CHAR(30) NOT NULL,
pr CHAR(2) DEFAULT '',
telefono CHAR(20) DEFAULT '',
fax CHAR(20) DEFAULT '',
cf_pi CHAR(30) DEFAULT '',
PRIMARY KEY (cliente));
--
-- Creazione della relazione "Movimenti", contenente i carichi
-- e gli scarichi del magazzino.
--
CREATE TABLE Movimenti (movimento INTEGER NOT NULL,
articolo INTEGER NOT NULL,
causale INTEGER NOT NULL,
data DATE NOT NULL,
cliente INTEGER,
fornitore INTEGER,
quantita NUMERIC(12,3),
valore NUMERIC(11,2),
PRIMARY KEY (movimento));
--
-- Creazione della vista "Movimenti_extra"
--
CREATE VIEW Movimenti_extra AS
SELECT Movimenti.movimento AS movimento,
(Movimenti.valore / Movimenti.quantita) AS valore_un,
(Movimenti.quantita * Causali.var) AS quantita
FROM Movimenti, Causali
WHERE Movimenti.causale = Causali.causale;
--
-- Creazione della vista "Situazione_magazzino"
--
CREATE VIEW Situazione_magazzino AS
SELECT Movimenti.articolo AS articolo,
SUM(Movimenti_extra.quantita) AS esistenza,
SUM(Movimenti_extra.quantita * Movimenti_extra.valore_un)
AS valore,
(SUM(Movimenti_extra.quantita * Movimenti_extra.valore_un)
/ SUM(Movimenti_extra.quantita))
AS costo_medio
FROM Movimenti, Movimenti_extra, Articoli
WHERE Movimenti.movimento = Movimenti_extra.movimento
AND Movimenti.articolo = Articoli.articolo
AND Movimenti.valore IS NOT NULL
GROUP BY Movimenti.articolo;
--
-- Creazione del grilletto relativo alla relazione "Articoli",
-- in fase di inserimento.
--
CREATE TRIGGER Articoli_ins
BEFORE INSERT ON Articoli
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.articolo <= 0)
THEN
RAISE (ABORT, 'ERR: articolo <= 0!')
WHEN (NEW.listino <= 0)
THEN
RAISE (ABORT, 'ERR: listino <= 0!')
WHEN (NEW.scorta_min < 0)
THEN
RAISE (ABORT, 'ERR: scorta_min < 0!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Articoli",
-- in fase di aggiornamento.
--
CREATE TRIGGER Articoli_upd
BEFORE UPDATE ON Articoli
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.articolo <= 0)
THEN
RAISE (ABORT, 'ERR: articolo <= 0!')
WHEN (NEW.listino <= 0)
THEN
RAISE (ABORT, 'ERR: listino <= 0!')
WHEN (NEW.ScortaMin < 0)
THEN
RAISE (ABORT, 'ERR: scorta_min < 0!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Articoli",
-- in fase di cancellazione.
--
CREATE TRIGGER Articoli_del
BEFORE DELETE ON Articoli
FOR EACH ROW
BEGIN
SELECT CASE
WHEN ((SELECT articolo FROM Movimenti
WHERE articolo = OLD.articolo) IS NOT NULL)
THEN
RAISE (ABORT, 'ERR: articolo ancora usato in Movimenti!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Causali",
-- in fase di inserimento.
--
CREATE TRIGGER Causali_ins
BEFORE INSERT ON Causali
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.causale <= 0)
THEN
RAISE (ABORT, 'ERR: causale <= 0!')
WHEN (NEW.var > 1)
THEN
RAISE (ABORT, 'ERR: attributo ''var'' non valido!')
WHEN (NEW.var < -1)
THEN
RAISE (ABORT, 'ERR: attributo ''var'' non valido!')
WHEN (NEW.var = 0)
THEN
RAISE (ABORT, 'ERR: attributo ''var'' non valido!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Causali",
-- in fase di aggiornamento.
--
CREATE TRIGGER Causali_upd
BEFORE UPDATE ON Causali
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.causale <= 0)
THEN
RAISE (ABORT, 'ERR: causale <= 0!')
WHEN (NEW.var > 1)
THEN
RAISE (ABORT, 'ERR: attributo ''var'' non valido!')
WHEN (NEW.var < -1)
THEN
RAISE (ABORT, 'ERR: attributo ''var'' non valido!')
WHEN (NEW.var = 0)
THEN
RAISE (ABORT, 'ERR: attributo ''var'' non valido!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Causali",
-- in fase di cancellazione.
--
CREATE TRIGGER Causali_del
BEFORE DELETE ON Causali
FOR EACH ROW
BEGIN
SELECT CASE
WHEN ((SELECT causale FROM Movimenti
WHERE causale = OLD.causale) IS NOT NULL)
THEN
RAISE (ABORT, 'ERR: causale ancora usata in Movimenti!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Fornitori",
-- in fase di inserimento.
--
CREATE TRIGGER Fornitori_ins
BEFORE INSERT ON Fornitori
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.fornitore <= 0)
THEN
RAISE (ABORT, 'ERR: fornitore <= 0!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Fornitori",
-- in fase di aggiornamento.
--
CREATE TRIGGER Fornitori_upd
BEFORE UPDATE ON Fornitori
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.fornitore <= 0)
THEN
RAISE (ABORT, 'ERR: fornitore <= 0!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Fornitori",
-- in fase di cancellazione.
--
CREATE TRIGGER Fornitori_del
BEFORE DELETE ON Fornitori
FOR EACH ROW
BEGIN
SELECT CASE
WHEN ((SELECT fornitore FROM Movimenti
WHERE fornitore = OLD.fornitore) IS NOT NULL)
THEN
RAISE (ABORT, 'ERR: fornitore ancora usato in Movimenti!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Clienti",
-- in fase di inserimento.
--
CREATE TRIGGER Clienti_ins
BEFORE INSERT ON Clienti
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.cliente <= 0)
THEN
RAISE (ABORT, 'ERR: cliente <= 0!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Clienti",
-- in fase di aggiornamento.
--
CREATE TRIGGER Clienti_upd
BEFORE UPDATE ON Clienti
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.cliente <= 0)
THEN
RAISE (ABORT, 'ERR: cliente <= 0!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Clienti",
-- in fase di cancellazione.
--
CREATE TRIGGER Clienti_del
BEFORE DELETE ON Clienti
FOR EACH ROW
BEGIN
SELECT CASE
WHEN ((SELECT cliente FROM Movimenti
WHERE cliente = OLD.cliente) IS NOT NULL)
THEN
RAISE (ABORT, 'ERR: cliente ancora usato in Movimenti!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Movimenti",
-- in fase di inserimento.
--
CREATE TRIGGER Movimenti_ins
BEFORE INSERT ON Movimenti
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.movimento <= 0)
THEN
RAISE (ABORT, 'ERR: movimento <= 0!')
WHEN (NEW.quantita <= 0)
THEN
RAISE (ABORT, 'ERR: quantità <= 0!')
WHEN (NEW.valore < 0)
THEN
RAISE (ABORT, 'ERR: valore < 0!')
WHEN ((SELECT articolo FROM Articoli
WHERE articolo = NEW.articolo) IS NULL)
THEN
RAISE (ABORT, 'ERR: articolo inesistente!')
WHEN ((NEW.cliente IS NOT NULL)
AND ((SELECT cliente FROM Clienti
WHERE cliente = NEW.cliente) IS NULL))
THEN
RAISE (ABORT, 'ERR: cliente inesistente!')
WHEN ((SELECT causale FROM Causali
WHERE causale = NEW.causale) IS NULL)
THEN
RAISE (ABORT, 'ERR: causale inesistente!')
WHEN ((NEW.fornitore IS NOT NULL)
AND ((SELECT fornitore FROM Fornitori
WHERE fornitore = NEW.fornitore) IS NULL))
THEN
RAISE (ABORT, 'ERR: fornitore inesistente!')
END;
END;
--
-- Creazione del grilletto relativo alla relazione "Movimenti",
-- in fase di inserimento, per il calcolo automatico del costo medio.
-- Attualmente il grilletto non è operativo.
--
--CREATE TRIGGER Movimenti_costo_medio
-- BEFORE INSERT ON Movimenti
-- BEGIN
-- UPDATE Movimenti
-- SET valore =
-- (SELECT costo_medio * NEW.quantita
-- FROM Situazione_magazzino
-- WHERE articolo = NEW.articolo)
-- WHERE valore IS NULL;
-- END;
--
-- Creazione del grilletto relativo alla relazione "Movimenti",
-- in fase di aggiornamento.
--
CREATE TRIGGER Movimenti_upd
BEFORE UPDATE ON Movimenti
FOR EACH ROW
BEGIN
SELECT CASE
WHEN (NEW.movimento <= 0)
THEN
RAISE (ABORT, 'ERR: movimento <= 0!')
WHEN (NEW.quantita <= 0)
THEN
RAISE (ABORT, 'ERR: quantità <= 0!')
WHEN (NEW.valore < 0)
THEN
RAISE (ABORT, 'ERR: valore < 0!')
WHEN (NEW.valore IS NULL)
THEN
RAISE (ABORT,
'ERR: valore indeterminato in fase di variazione!')
WHEN ((SELECT articolo FROM Articoli
WHERE articolo = NEW.articolo) IS NULL)
THEN
RAISE (ABORT, 'ERR: articolo inesistente!')
WHEN ((NEW.cliente IS NOT NULL)
AND ((SELECT cliente FROM Clienti
WHERE cliente = NEW.cliente) IS NULL))
THEN
RAISE (ABORT, 'ERR: cliente inesistente!')
WHEN ((SELECT causale FROM Causali
WHERE causale = NEW.causale) IS NULL)
THEN
RAISE (ABORT, 'ERR: causale inesistente!')
WHEN ((NEW.fornitore IS NOT NULL)
AND ((SELECT fornitore FROM Fornitori
WHERE fornitore = NEW.fornitore) IS NULL))
THEN
RAISE (ABORT, 'ERR: fornitore inesistente!')
END;
END;
--
-- Creazione della vista "Listato_articoli"
--
CREATE VIEW Listato_articoli AS
SELECT articolo AS Articolo,
descrizione AS 'Descrizione ',
um AS UM,
listino AS Listino,
scorta_min AS Scorta_min
FROM Articoli
ORDER BY articolo;
--
-- Creazione della vista "Listato_causali"
--
CREATE VIEW Listato_causali AS
SELECT causale AS Causale,
descrizione AS 'Descrizione ',
var AS Var
FROM Causali
ORDER BY causale;
--
-- Creazione della vista "Listato_fornitori".
--
CREATE VIEW Listato_fornitori AS
SELECT fornitore AS Fornitore,
ragione_sociale AS 'Ragione_sociale ',
indirizzo AS 'Indirizzo ',
cap AS CAP,
citta AS 'Citta ',
pr AS PR,
telefono AS 'Telefono ',
fax AS 'Fax ',
cf_pi AS 'CF_PI '
FROM Fornitori
ORDER BY fornitore;
--
-- Creazione della vista "Listato_clienti".
--
CREATE VIEW Listato_clienti AS
SELECT cliente AS Cliente,
ragione_sociale AS 'Ragione_sociale ',
indirizzo AS 'Indirizzo ',
cap AS CAP,
citta AS 'Citta ',
pr AS PR,
telefono AS 'Telefono ',
fax AS 'Fax ',
cf_pi AS 'CF_PI '
FROM Clienti
ORDER BY cliente;
--
-- Creazione della vista "Listato_movimenti".
--
CREATE VIEW Listato_movimenti AS
SELECT movimento AS Movimento,
articolo AS Articolo,
causale AS Causale,
data AS Data,
cliente AS Cliente,
fornitore AS Fornitore,
quantita AS Quantita,
valore AS Valore
FROM Movimenti
ORDER BY movimento;
--
-- Creazione della vista "Listato_movimenti_dettagliato".
--
CREATE VIEW Listato_movimenti_dettagliato AS
SELECT Movimenti.movimento AS Movimento,
Movimenti.articolo AS C_articolo,
Articoli.descrizione AS 'Articolo ',
Movimenti.causale AS C_causale,
Causali.descrizione AS 'Causale ',
Movimenti.data AS Data,
Movimenti.cliente AS Cliente,
Movimenti.fornitore AS Fornitore,
Movimenti_extra.quantita AS Quantita,
Movimenti.valore AS Valore,
Movimenti_extra.valore_un AS Valore_un
FROM Articoli,
Causali,
Movimenti,
Movimenti_extra
WHERE Movimenti.articolo = Articoli.articolo
AND Movimenti.causale = Causali.causale
AND Movimenti.movimento = Movimenti_extra.movimento
ORDER BY Movimenti.movimento;
--
-- Creazione della vista "Listato_situazione".
--
CREATE VIEW Listato_situazione AS
SELECT Situazione_magazzino.articolo AS Articolo,
Articoli.descrizione AS 'Descrizione ',
Articoli.um AS UM,
Situazione_magazzino.esistenza AS Esistenza,
Situazione_magazzino.costo_medio AS Costo_medio
FROM Situazione_magazzino, Articoli
WHERE Situazione_magazzino.articolo = Articoli.articolo
ORDER BY Situazione_magazzino.articolo;
--
--
--
|