basi_di_dati/compitino/secondo_compitino/schema_concettuale.tex

% !TEX root = ../main.tex

\paragraph{Elenco degli ingredienti disponibili}
*l* committente ha chiesto espressamente l'elenco degli ingredienti disponibili.
Tuttavia, questo è calcolabile a partire dagli acquisti e dalle produzioni.
Ho convinto *l* committente a non memorizzare separatamente l'inventario degli
ingredienti, rassicurandol* che avrei fornito una vista logica ``Inventario".

\begin{lstlisting}[style=SQLu][float,floatplacement=H]
CREATE VIEW IngredientiAcquistatiTotali (IdIngrediente, Ingrediente,
                                         Totale)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(a.Quantità) Totale
   FROM Acquisti a
   JOIN Ingredienti i ON i.IdIngrediente = a.IdIngrediente
   GROUP BY i.IdIngrediente, i.Descrizione;

CREATE VIEW IngredientiInUso (IdIngrediente, Ingrediente, InUso)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(ir.Quantità) InUso
   FROM IngredientiRicette ir
   JOIN Ingredienti i ON i.IdIngrediente = ir.IdIngrediente
   JOIN Produzioni p ON p.IdRicetta = ir.IdRicetta
   WHERE p.Stato IS NULL
   GROUP BY i.IdIngrediente, i.Descrizione;

CREATE VIEW IngredientiUsati (IdIngrediente, Ingrediente,
                              Usati)
AS SELECT i.IdIngrediente IdIngrediente, i.Descrizione Ingrediente,
          SUM(ir.Quantità) Usati
   FROM IngredientiRicette ir
   JOIN Ingredienti i ON i.IdIngrediente = ir.IdIngrediente
   JOIN Produzioni p ON p.IdRicetta = ir.IdRicetta
   WHERE p.Stato = 0
   GROUP BY i.IdIngrediente, i.Descrizione;

CREATE VIEW Inventario (IdIngrediente, Ingrediente, QuantitàTotale,
                        QuantitàDisponibile)
AS SELECT iat.IdIngrediente IdIngrediente,
          iat.Ingrediente Ingrediente,
          (iat.Totale - COALESCE(iu.Usati, 0)) QuantitàTotale,
          (iat.Totale - COALESCE(iu.Usati, 0)
            - COALESCE(iiu.InUso, 0)) QuantitàDisponibile
   FROM IngredientiAcquistatiTotali iat
   LEFT JOIN IngredientiUsati iu
             ON iu.IdIngrediente = iat.IdIngrediente
   LEFT JOIN IngredientiInUso iiu
             ON iiu.IdIngrediente = iat.IdIngrediente
   WHERE iat.Totale - COALESCE(iu.Usati, 0) > 0;
\end{lstlisting}

Ogni volta che si inizia una produzione, l'applicazione controlla che  la
quantità di ingredienti disponibili superi la quantità degli ingredienti
necessari alla preparazione.

L'applicazione può anche mostrare una ``lista della spesa" basandosi su ricette
che si vogliono preparare e sulla vista inventario.

L'applicazione mostrerà ad ogni birrai* solo le ricette di cui è aut*r* o di un
birrificio per cui lavora.

\begin{minipage}{\linewidth}
    \paragraph{Vincoli intra-relazionali}
    \begin{itemize}
    \itemsep0em
        \item Non possono esistere due persone con lo stesso codice fiscale.
        \item Non possono esistere due birrai* con lo stesso soprannome.
        \item Non possono esistere due fornitori con la stessa partita IVA né
        con la stessa ragione sociale.
        \item Il tipo di ingrediente determina l'unità di misura.
        Esiste un breve elenco di tipi ingredienti disponibili con la relativa unità di
        misura.
        Non ho creato una classe ``TipiIngredienti" per contenere il numero di classi,
        ma in effetti il tipo determina funzionalmente l'unità di misura ed esistono
        pochi tipi, mentre ci sono molti ingredienti per ogni tipo.
    \end{itemize}
    \paragraph{Vincoli inter-relazionali}
    \begin{itemize}
    \itemsep0em
        \item Alla registrazione, l'utente deve inserire un soprannome e/o un indirizzo
        di spedizione: il vincolo di copertura impone che l'unione di Clienti e
        Birraie sia Persone, non devono esistere persone che non sono né clienti
        né birrai*.
        \item In ogni produzione, il NumeroBottiglie diviso per 0.5 non deve superare la
        CapacitàProduttiva del birrificio.
        \item Ogni produzione deve iniziare con stato `in corso';
        non può iniziare una produzione se un altra è `in corso' nello stesso
        birrificio.
        \item Ogni nota deve fare riferimento a una produzione.
        \item Ogni prenotazione deve fare riferimento a una produzione.
        \item Ogni prenotazione deve fare riferimento ad un* cliente.
        \item La quantità di bottiglie di un produzione prenotate non deve
        superare il numero di bottiglie prodotte.
        \item Ogni produzione deve seguire una ricetta.
        \item Due produzioni di uno stesso birrificio non devono avere lo stesso lotto.
        \item Ogni ricetta deve avere un* creat*r* e un birrificio di riferimento.
        \item Ogni fattura deve fare riferimento ad un birrificio e un fornitore.
        \item Ogni acquisto deve riferirsi a una fattura e un ingrediente.
        \item Ogni ricetta deve avere almeno un ingrediente per ciascuno dei
        seguenti tipi: malto, luppolo, lievito.
    \end{itemize}
\end{minipage}