Restaurant Sales, Inventory Control, and Profit Analysis Using Database Systems

A practical guide to database modeling, normalization (2NF), and SQL reporting for restaurant business management.

Question:

a) Madam Kofo, owns a thriving restaurant which sells both local and foreign delicacies in the heart of her town. For some time now, she observed her business has been having very high sales but the profits don’t seem to be commensurate with the sales. She suspects some fraudulent activities and has consulted you as a Computer guru if you could help her in any way. Essentially,she wants to have the following summary at the end of each business day:

i. Total sales per day ii. Total sales per day for each type of food iii. Total quantity of each type of food available before any sale and after the last sale per day. iv. Total amount (price) of each type of food available before any sale and after the last sale per day. v. Total profit at the end of each day’s sale

b) Help Madam Kofo design a simple but properly modeled database information.(6 marks)

c) Ensure that your database is normalized, at least, to 2NF (7 marks)

d) Write appropriate SQL queries that will help answer her six requests.(7 marks)

Solution

a) There are entities needed from Madam Kofo’s needs.

• List of foods she sells (Entity — FOOD)
• Sales Transactions (Entity — SALES)
• Food inventory (Entity — INVENTORY)

i. Total sales per day (SUM of all sales for day GROUPED BY day)

-- This is a pseudocode, not a real SQL query

GROUP sales BY sale_date
CALCULATE SUM(total_amount)
DISPLAY total sales per day

ii. Total sales per day for each type of food (SUM of sales for day GROUPED BY day and food type)

-- This is a pseudocode, not a real SQL query

JOIN sales WITH food
GROUP records BY sale_date, food_type
CALCULATE SUM(total_amount)
DISPLAY total sales for each food type per day

iii. Total quantity of each type of food available before any sale and after the last sale per day (Retrieve opening and closing quantities for each food item per day)

-- This is a pseudocode, not a real SQL query

JOIN inventory WITH food
GROUP records BY stock_date, food_name
RETRIEVE opening_qty, closing_qty
DISPLAY quantity before first sale and after last sale

iv. Total amount (price) of each type of food available before any sale and after the last sale per day (Retrieve opening and closing quantities MULTIPLIED by the unit price of each food item)

-- This is a pseudocode, not a real SQL query

JOIN inventory WITH food
CALCULATE opening_qty × selling_price
CALCULATE closing_qty × selling_price
DISPLAY opening value and closing value of each food item

v. Total profit at the end of each day’s sale (total sales for each day minus total cost of each food item)

-- This is a pseudocode, not a real SQL query

JOIN sales WITH food
CALCULATE revenue = quantity_sold × selling_price
CALCULATE cost = quantity_sold × cost_price
CALCULATE profit = revenue - cost
GROUP records BY sale_date
CALCULATE SUM(profit)
DISPLAY total daily profit

Please note, for assessment or exam’s sake, the pseudocodes are not exactly needed but they could give extra marks. 😄

b) Database Design

1. FOOD — food_id (PK), food_name, food_type, cost_price, selling_price Master list of all dishes with cost and selling price

2. SALES — sales_id (PK), food_id (FK), category_id (FK), quantity_sold, amount_paid, sales_datetime Every sale event with time, quantity, and amount

3. INVENTORY — inventory_id (PK), food_id (FK), opening quantity, closing_quantity, inventory_date Opening and closing stock per food per day

4. FOOD_CATEGORY — category_id (PK), category_name Classifies food (e.g. soups, grills, beverages)

Crow’s foot ER diagram showing the database design

Cardinality or Relationships

• FOOD → SALES (food_id FK): one food item can appear in many sales records.
• FOOD_CATEGORY → SALES (category_id FK): one category classifies many sales, allowing sales to be grouped and reported by food type/category.
• FOOD → INVENTORY (food_id FK): one food item has many daily inventory records tracking its opening and closing stock.

Note that FOOD_CATEGORY is linked directly to the SALES table, not the FOOD table as defined in the schema. This means the category is recorded at the exact time each sale is made.

This design is important because it helps detect errors or fraud, such as when an item is wrongly recorded under a different category during a sale.

c) Normalization to 2NF

First Normal Form (1NF) is satisfied because every table has a primary key, all columns are atomic (single-valued), and there are no repeating groups.

Second Normal Form (2NF) requires 1NF plus no partial dependencies and every non-key attribute must depend on the whole primary key, not just part of it. This is most relevant to composite keys.

Sales table will change,

From:

• SALES — Sales_id, food_id, category_id, quantity_sold, amount_paid, sales_datetime
• FOOD — food_id (PK), food_name, food_type, cost_price, selling_price

To:

• SALES — sales_id (PK), food_id (FK), quantity_sold, amount_paid, sales_datetime
• FOOD — food_id (PK), food_name, category_id (FK), food_type, cost_price, selling_price

Now every non-key attribute depends entirely on the primary key. And database is in 2NF.

You can draw tables for question c which is expected but i was unable to draw a table because of the tools available inside Medium.

d) Queries for a1–5

i. Total sales per day

SELECT
    sale_date,
    SUM(amount_paid) AS total_daily_sales
FROM sales
GROUP BY sale_date
ORDER BY sale_date DESC;

ii. Total sales per day for each type of food (local vs foreign)

SELECT
    st.sale_date,
    fi.food_type,
    SUM(st.amount_paid) AS total_sales_by_type
FROM sales st
JOIN food fi ON st.food_id = fi.food_id
GROUP BY st.sale_date, fi.food_type
ORDER BY st.sale_date DESC, fi.food_type;

iii. Total quantity of each food before and after sales per day

SELECT
    di.inventory_date,
    fi.food_name,
    fi.food_type,
    di.opening_quantity  AS quantity_before_sales,
    di.closing_quantity  AS quantity_after_sales
FROM inventory di
JOIN food fi ON di.food_id = fi.food_id
ORDER BY di.inventory_date DESC, fi.food_name;

iv. Total amount (value) of each food in stock before and after sales per day

SELECT
    di.inventory_date,
    fi.food_name,
    fi.food_type,
    (di.opening_quantity * fi.unit_selling_price) AS stock_value_before_sales,
    (di.closing_quantity * fi.unit_selling_price) AS stock_value_after_sales
FROM inventory di
JOIN food fi ON di.food_id = fi.food_id
ORDER BY di.inventory_date DESC, fi.food_name;

v. Total profit at the end of each day

Profit = (selling price × quantity sold) − (cost price × quantity sold)

SELECT
    st.sale_date,
    SUM((fi.unit_selling_price - fi.unit_cost_price) * st.quantity_sold) AS total_daily_profit
FROM sales st
JOIN food fi ON st.food_id = fi.food_id
GROUP BY st.sale_date
ORDER BY st.sale_date DESC;

Conclusion

This design gives Madam Kofo a complete audit trail. If total sales are high but profits are low, she can compare amount_paid in SALES against the expected unit_selling_price × quantity_sold per transaction making any under-reporting or theft immediately visible.