Codifica SQL

Definizione dello schema

CREATE TABLE Fornitori (
Codice NUMERIC(3) PRIMARY KEY,
Nome VARCHAR(30) NOT NULL,
Citta VARCHAR(20),
Telefono VARCHAR(18) NOT NULL,
Via VARCHAR(40),
NCivico VARCHAR(5),
CAP CHAR(5),
Fax VARCHAR(18),
Email VARCHAR(30),
Tipo CHAR(6) NOT NULL CHECK (Tipo LIKE 'ProdNC' OR Tipo LIKE 'Rotoli')
)

CREATE TABLE Clienti (
Codice NUMERIC(3) PRIMARY KEY,
Nome VARCHAR(30) NOT NULL,
Citta VARCHAR(20),
Telefono VARCHAR(18) NOT NULL,
Via VARCHAR(40),
NCivico VARCHAR(5),
CAP CHAR(5),
Fax VARCHAR(18),
Email VARCHAR(30)
)

CREATE TABLE Serbatoio (
Nome CHAR(5) PRIMARY KEY
)

CREATE TABLE CollSerbatoio (
NGruppo NUMERIC(2) UNIQUE,
Serbatoio CHAR(5) UNIQUE REFERENCES Serbatoio(Nome),
PRIMARY KEY (NGruppo, Serbatoio)
)

CREATE TABLE Conf (
NGruppo CHAR(5) REFERENCES CollSerbatoio(NGruppo),
ProdMac VARCHAR(40),
PRIMARY KEY (NGruppo, ProdMac)
)

CREATE TABLE Util (
NGruppo CHAR(5) REFERENCES CollSerbatoio(NGruppo),
RotMac VARCHAR(50),
PRIMARY KEY (NGruppo, RotMac)
)

CREATE TABLE Macchine (
Nome CHAR(5) PRIMARY KEY,
Ngruppo NUMERIC(2) NOT NULL REFERENCES CollSerbatoio(NGruppo),
Stato VARCHAR(10) NOT NULL CHECK (Stato LIKE 'Produzione' OR
Stato LIKE 'Riserva' OR Stato LIKE 'Guasta')
)

CREATE TABLE ProdNCM (
TipoProd VARCHAR(40) PRIMARY KEY,
Quantita NUMERIC(6) NOT NULL CHECK (Quantita >= 0)
)

CREATE TABLE RotoliM (
TipoRot VARCHAR(50) PRIMARY KEY,
Lunghezza NUMERIC(6) NOT NULL CHECK (Lunghezza >= 0),
ProdCont VARCHAR(40) NOT NULL REFERENCES ProdNCM(TipoProd)
)

CREATE TABLE ProdNC (
Codice NUMERIC(8) PRIMARY KEY,
TipoProd VARCHAR(40) NOT NULL REFERENCES ProdNCM(TipoProd),
Quantita NUMERIC(6) NOT NULL CHECK (Quantita > 0)
)

CREATE TABLE Rotoli (
Codice NUMERIC(8) PRIMARY KEY,
TipoRot VARCHAR(50) NOT NULL REFERENCES RotoliM(TipoRot),
Lunghezza NUMERIC(6) NOT NULL CHECK (Lunghezza > 0)
)

CREATE TABLE Ordine (
Codice NUMERIC(8) PRIMARY KEY,
TipoProd VARCHAR(40) NOT NULL,
DataConsegna DATE NOT NULL,
Quantita NUMERIC(6) NOT NULL CHECK (Quantita > 0),
TipoRot VARCHAR(50) NOT NULL,
Capacita NUMERIC(6) NOT NULL CHECK (Capacita > 0),
DataRichiesta DATE NOT NULL,
CodiceCliente NUMERIC(3) NOT NULL REFERENCES Clienti(Codice)
)

CREATE TABLE ProdConf (
Codice NUMERIC(8) PRIMARY KEY REFERENCES Ordine(Codice),
LungRotolo NUMERIC(6) NOT NULL CHECK (LungRotolo > 0),
NomeMacchina CHAR(5) NOT NULL REFERENCES Macchine(Nome)
)

CREATE TABLE ConfProd (
Rotoli VARCHAR(50) REFERENCES RotoliM(TipoRot),
Macchine CHAR(5) REFERENCES Macchine(Nome),
PRIMARY KEY (Rotoli, Macchine)
)

CREATE TABLE Cont (
Serbatoio CHAR(5) REFERENCES Serbatoio(Nome),
ProdSerb VARCHAR(40),
PRIMARY KEY (Serbatoio, ProdSerb)
)

CREATE TABLE FornitRotoli (
Codice NUMERIC(8) REFERENCES Rotoli(Codice),
Fornitore NUMERIC(3) REFERENCES Fornitori(Codice),
Spesa NUMERIC(10) NOT NULL CHECK (Spesa >= 0),
Data DATE NOT NULL,
PRIMARY KEY (Codice, Fornitore)
)

CREATE TABLE FornitProdNC (
Codice NUMERIC(8) REFERENCES ProdNC(Codice),
Fornitore NUMERIC(3) REFERENCES Fornitori(Codice),
Spesa NUMERIC(10) NOT NULL CHECK (Spesa >= 0),
Data DATE NOT NULL,
PRIMARY KEY (Codice, Fornitore)
)

CREATE TABLE Vendita (
Codice NUMERIC(8) REFERENCES ProdConf(Codice),
Cliente NUMERIC(3) REFERENCES Clienti(Codice),
Ricavo NUMERIC(10) NOT NULL CHECK (Ricavo >= 0),
Data DATE NOT NULL,
PRIMARY KEY (Codice, Cliente)
)

Per rispettare il vincolo RV13, per il quale in ogni gruppo di macchine è presente sempre una ed una sola macchina di riserva, creiamo la seguente vista ed asserzione:

CREATE VIEW NumRiserve(NGruppo, Riserve) AS
SELECT Ngruppo, COUNT(ALL Stato)
FROM Macchine
WHERE Stato LIKE 'Riserva'
GROUP BY Ngruppo

CREATE ASSERTION ControlloMacchineDiRiserva
CHECK ((SELECT COUNT(NGruppo)
FROM NumRiserve) =
(SELECT COUNT(ALL Riserve)
FROM NumRiserve
WHERE Riserve = 1))

Codifica delle operazioni

Operazione 1
Inserire un nuovo fornitore (in media 1 volta all'anno)

INSERT INTO Fornitori(Codice, Nome, Citta, Telefono, Via, NCivico, CAP, Fax, Email, Tipo)
VALUES (<codice>, <nome>, <città>, <telefono>, <via>, <cap>, <fax>, <email>, <tipo>)

Operazione 2
Inserire un nuovo cliente (in media 1 volta al mese)

INSERT INTO Clienti(Codice, Nome, Citta, Telefono, Via, NCivico, CAP, Fax, Email)
VALUES (<codice>, <nome>, <città>, <telefono>, <via>, <ncivico>, <cap>, <fax>, <email>)

Operazione 3
Inserire i dati relativi all'acquisto di un prodotto non confezionato (in media 4 volte al giorno)

INSERT INTO ProdNC(Codice, TipoProd, Quantita)
VALUES(<codice>, <tipoprodotto>, <quantità>)

INSERT INTO FornitProdNC(Codice, Fornitore, Spesa, Data)
VALUES(<codice>, <codicefornitore>, <spesa>, <data>)

UPDATE ProdNCM
SET Quantita = Quantita + <quantità>
WHERE TipoProd = <tipoprodotto>

Operazione 4
Inserire i dati relativi all'acquisto di rotoli di tetrapak (in media 3 volte a settimana)

INSERT INTO Rotoli(Codice, TipoRot, Lunghezza)
VALUES(<codice>, <tiporotolo>, <lunghezza>)

INSERT INTO FornitRotoli(Codice, Fornitore, Spesa, Data)
VALUES(<codice>, <codicefornitore>, <spesa>, <data>)

UPDATE RotoliM
SET Lunghezza = Lunghezza + <lunghezza>
WHERE TipoRot = <tiporotolo>

Operazione 5
Inserire i dati relativi alla produzione e vendita di un prodotto confezionato (in media 20 volte al giorno)

INSERT INTO ConfProd(Rotoli, Macchine)
VALUES (<tiporotolo>, <macchinautilizzata>)

INSERT INTO ProdConf(Codice, LungRotolo, NomeMacchina)
VALUES (<codiceordine>, <lunghezzarotolo>, <macchinautilizzata>)

INSERT INTO Vendita(Codice, Cliente, Ricavo, Data)
VALUES (<codiceordine>, <codicecliente>, <ricavo>, <data>)

UPDATE ProdNCM
SET Quantita = Quantita - <quantità>
WHERE TipoProd = <tipoprodotto>

UPDATE RotoliM
SET Lunghezza = Lunghezza - <lunghezzarotolo>
WHERE TipoRot = <tiporotolo>

Operazione 6
Inserire i dati relativi alla richiesta di produzione di prodotti confezionati (in media 20 volte al giorno)

INSERT INTO Ordine(Codice, TipoProd, DataConsegna, Quantita, TipoRot, Capacita, DataRichiesta, CodiceCliente)
VALUES (<codice>, <tipoprodotto>, <dataconsegna>, <quantità>, <tiporotolo>, <capacità>, <datarichiesta>,
<codicecliente>)

Operazione 7
Acquisto di una nuova macchina (in media ogni 3 anni)

INSERT INTO Macchine(Nome, Ngruppo, Stato)
VALUES (<nomemacchina>, <numerogruppo>, 'Produzione')

Se il gruppo è nuovo, prima di inserire la macchina:
INSERT INTO CollSerbatoio(NGruppo, Serbatoio)
VALUES (<numerogruppo>, <nomeserbatoio>)

Per ogni prodotto confezionabile e rotolo utilizzabile:
INSERT INTO Conf(NGruppo, ProdMac)
VALUES (<numerogruppo>, <tipoprodotto>)

INSERT INTO Util(NGruppo, RotMac)
VALUES (<numerogruppo>, <tiporotolo>)

Operazione 8
Acquisto di un nuovo serbatoio asettico (in media ogni 5 anni)

INSERT INTO Serbatoio(Nome)
VALUES (<nomeserbatoio>)

Per ogni prodotto contenibile:
INSERT INTO Cont(Serbatoio, ProdSerb)
VALUES (<nomeserbatoio>, <tipoprodotto>)

Operazione 9
Eliminazione di una macchina (in media ogni 3 anni)

DELETE FROM Macchine
WHERE Nome = <nomemacchina>

DELETE FROM CollSerbatoio
WHERE NOT EXISTS (SELECT *
FROM Macchine
WHERE CollSerbatoio.NGruppo = Macchine.Ngruppo)

DELETE FROM Conf
WHERE NOT EXISTS (SELECT *
FROM Macchine
WHERE Conf.NGruppo = Macchine.Ngruppo)

DELETE FROM Util
WHERE NOT EXISTS (SELECT *
FROM Macchine
WHERE Util.NGruppo = Macchine.Ngruppo)

Operazione 10
Eliminazione di un serbatoio asettico (in media ogni 5 anni)

DELETE FROM Serbatoio
WHERE Nome = <nomeserbatoio>

DELETE FROM Cont
WHERE NOT EXISTS (SELECT *
FROM Serbatoio
WHERE Cont.Serbatoio = Serbatoio.Nome)

Operazione 11
Modificare lo stato delle macchine (in media 5 volte al giorno)

DROP ASSERTION ControlloMacchineDiRiserva

UPDATE Macchine
SET Stato = <nuovostatomacchina>
WHERE Nome = <nomemacchina>

UPDATE Macchine
SET Stato = 'Riserva'
WHERE Nome = <nuovamacchinadiriserva>

CREATE ASSERTION ControlloMacchineDiRiserva
CHECK ((SELECT COUNT(NGruppo)
FROM NumRiserve) =
(SELECT COUNT(ALL Riserve)
FROM NumRiserve
WHERE Riserve = 1))

Operazione 12
Visualizzazione dello stato delle macchine (in media 10 volte al giorno)

SELECT *
FROM Macchine
ORDER BY Ngruppo, Stato

Operazione 13
Visualizzazione dei serbatoi non utilizzati (in media una volta al giorno)

SELECT Nome
FROM Serbatoio
WHERE Nome <> ALL (SELECT Serbatoio
FROM CollSerbatoio)

Operazione 14
Riutilizzare una macchina guasta dopo la sua riparazione (in media ogni 6 mesi)

UPDATE Macchine
SET Stato = 'Produzione'
WHERE Nome = <nomemacchina>

Operazione 15
Visualizzazione dei prodotti confezionati da vendere la successiva settimana (in media una volta al giorno)

SELECT Ordine.Codice, Ordine.TipoProd, Ordine.TipoRot, Ordine.Quantita,
Ordine.Capacita, Ordine.DataConsegna, Ordine.DataRichiesta, Clienti.Nome
FROM Ordine, Clienti
WHERE Ordine.CodiceCliente = Clienti.Codice AND
Ordine.DataConsegna >= <dataodierna> AND
Ordine.DataConsegna <= (<dataodierna> + 7) AND
Ordine.Codice <> ALL (SELECT ProdConf.Codice
FROM ProdConf)
ORDER BY Ordine.DataConsegna, Ordine.DataRichiesta

Per le operazioni che coinvolgono le spese dell’azienda (le numero 16, 17, 18, 22 e 23) si utilizza la seguente vista:

CREATE VIEW Spese(Spesa, Data) AS
SELECT Spesa, Data
FROM FornitProdNC
UNION ALL
SELECT Spesa, Data
FROM FornitRotoli

Operazione 16
Visualizzazione delle spese settimanali (in media una volta a settimana)

SELECT SUM (ALL Spesa) AS SpeseSettimanali
FROM Spese
WHERE Data <= <dataodierna> AND Data >= (<dataodierna> - 7)

Operazione 17
Visualizzazione delle spese mensili (in media una volta al mese)

SELECT SUM (ALL Spesa) AS SpeseMensili
FROM Spese
WHERE MONTH(Data) = MONTH(<dataodierna>) AND
YEAR(Data) = YEAR(<dataodierna>)

Operazione 18
Visualizzazione delle spese annuali (in media una volta all'anno)

SELECT SUM (ALL Spesa) AS SpeseAnnuali
FROM Spese
WHERE YEAR(Data) = YEAR(<dataodierna>)

Operazione 19
Visualizzazione dei ricavi settimanali (in media una volta a settimana)

SELECT SUM (ALL Ricavo) AS RicaviSettimanali
FROM Vendita
WHERE Data <= <dataodierna> AND Data >= (<dataodierna> - 7)

Operazione 20
Visualizzazione dei ricavi mensili (in media una volta al mese)

SELECT SUM (ALL Ricavo) AS RicaviMensili
FROM Vendita
WHERE MONTH(Data) = MONTH(<dataodierna>) AND
YEAR(Data) = YEAR(<dataodierna>)

Operazione 21
Visualizzazione dei ricavi annuali (in media una volta all'anno)

SELECT SUM (ALL Ricavo) AS RicaviAnnuali
FROM Vendita
WHERE YEAR(Data) = YEAR(<dataodierna>)

Operazione 22
Visualizzazione del bilancio mensile (in media una volta al mese)

DROP VIEW SpMens

DROP VIEW RicMens

CREATE VIEW SpMens(SpeseMensili) AS
SELECT SUM (ALL Spesa)
FROM Spese
WHERE MONTH(Data) = MONTH(<dataodierna>) AND
YEAR(Data) = YEAR(<dataodierna>)

CREATE VIEW RicMens(RicaviMensili) AS
SELECT SUM (ALL Ricavo)
FROM Vendita
WHERE MONTH(Data) = MONTH(<dataodierna>) AND
YEAR(Data) = YEAR(<dataodierna>)

SELECT R.RicaviMensili - S.SpeseMensili AS BilancioMensile
FROM RicMens AS R, SpMens AS S

Operazione 23
Visualizzazione del bilancio annuale (in media un volta all'anno)

DROP VIEW SpAnno

DROP VIEW RicAnno

CREATE VIEW SpAnno(SpeseAnnuali) AS
SELECT SUM (ALL Spesa)
FROM Spese
WHERE YEAR(Data) = YEAR(<dataodierna>)

CREATE VIEW RicAnno(RicaviAnnuali) AS
SELECT SUM (ALL Ricavo)
FROM Vendita
WHERE YEAR(Data) = YEAR(<dataodierna>)

SELECT R.RicaviAnnuali - S.SpeseAnnuali AS BilancioAnnuale
FROM RicAnno AS R, SpAnno AS S

Operazione 24
Visualizzazione dei prodotti presenti in magazzino (in media una volta al giorno)

CREATE VIEW Magazzino(Oggetto, QuantitaLunghezza, Tipo) AS
SELECT TipoProd, Quantita, 'Prodotto Non Confezionato'
FROM ProdNCM
UNION
SELECT TipoRot, Lunghezza, 'Rotolo'
FROM RotoliM

[indice] - [Analisi dei requisiti] - [Progettazione concettuale] - [Progettazione logica] - [Testing]