🧋 Beverage Shop POS & Inventory Management System

Database Systems Final Project

This project implements a full beverage shop ordering system, including POS ordering, order management, inventory tracking, and database integration, built using SQLite + Python + SQL.

The system includes:

• 📋 Menu & ordering interface
• 🧾 Order creation & order detail management
• 📦 Inventory tracking with expiry and low-stock warnings
• 🗃️ Fully designed relational database schema
• 🔍 SQL query functionality (time range, fuzzy search, JSON responses)

---

📁 Table of Contents

• System Overview
• Database Schema
• User Interface Overview
• Order System
• Inventory System
• Database Implementation
• SQL Features
• Future Improvements

---

🧠 System Overview

This system simulates a full beverage shop workflow:

1. Customers place drink orders
2. Cashier selects drink options (sweetness / ice / size / quantity)
3. Order is stored into the SQLite database
4. Inventory is updated
5. Admin can review past orders & monitor inventory status

The application provides an intuitive UI with multiple functional pages:

• Home page
• Ordering page
• Order review page
• Inventory management modules

---

🗄️ Database Schema

Based on the project slides :contentReference[oaicite:2]{index=2}, the following tables were designed:

INVENTORY (
ingredient_id PRIMARY KEY,
ingredient_name,
quantity,
unit,
expiry_date,
last_update
)

INVENTORY_DETAILS (
ingredient_id,
quantity,
expiry_date,
last_update
)

MENU (
item_id PRIMARY KEY,
item_name,
price_medium,
price_large,
description
)

ORDER (
order_id PRIMARY KEY,
order_datetime,
total_quantity,
total_price,
cashier_id,
order_status CHECK(order_status IN ('Completed', 'Cancelled'))
)

ORDER_DETAILS (
order_id,
item_id,
sweetness CHECK(...),
ice CHECK(...),
size,
quantity
)

✔ Highlights

• Use of CHECK constraints to enforce valid order status & drink attributes
• Separate Inventory and Inventory_Details tables for tracking expiry batches
• Relational design supports flexible querying and reporting

---

🖥️ User Interface Overview

🏠 Home Page

Links to:

• Ordering page
• Order history page
• Inventory management

☕ Ordering Interface

• Category selection
• Drink option popup modal (sweetness / ice / size / qty)
• Items temporarily stored before checkout

🧾 Order System

1️⃣ Drink Selection

• Users choose:
• Sweetness
• Ice level
• Size (M/L)
• Quantity

2️⃣ Order Summary

• Displays:
• Item name
• Item ID
• Customization
• Delete button for mistaken items

3️⃣ Checkout

• Final confirmation window summarizing:
• Total cups
• Total price
• Writes order + order details into database

4️⃣ Order History

• Search by time range using BETWEEN ? AND ?

📦 Inventory System

The inventory module features 5 sections:

1. Inventory List — default view
2. Expiring Soon — items expiring in 1 / 3 / 5 months
3. Low Stock Warning — quantity between 1–5 units
4. Add Inventory — for new deliveries
5. Total Waste Report

➕ Add New Inventory Updates:

• ingredient name
• quantity
• expiry date
• Shows confirmation message when successful.

🗃️ Database Implementation

Python scripts include functions to:

📌 Create Database & Tables (using SQLite) create_database() create_table_order() create_table_order_details() create_table_inventory() create_table_inventory_details()

📌 Insert Data via CSV Imports menu, inventory, price difference, orders, order_details Uses datetime module to auto-fill expiry and last-update dates

🔍 SQL Features

The project demonstrates several SQL techniques:

✔ Atomic commits Ensures updates remain consistent during concurrent operations.

✔ Fuzzy search using LIKE + % Used for searching inventory items.

✔ Time-range querying

SELECT * FROM Orders 
WHERE order_datetime BETWEEN ? AND ?;

✔ JSON response formatting Query results converted into JSON for UI display.

✔ Inventory & order lookup by item ID

🚀 Future Improvements

• Add a full database to link each user/cashier with personalized settings
• Automate waste forecasting based on expiry patterns
• Connect to external systems (food ordering, supplier APIs)
• Cloud deployment instead of local-only execution
• Improve UI consistency between desktop and mobile

✅ Conclusion This project successfully integrates:

• A functional POS system
• Comprehensive inventory management
• Database-backed ordering & reporting
• User-friendly interface
• SQL techniques including constraints, fuzzy search, time-range querying, and JSON formatting
• It demonstrates complete understanding of relational database design, SQL operations, and real-world system integration.