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,
                              QuantitàUsata)
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 Ogni produzione deve seguire una ricetta.
        \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}