product management system with oracle database

Product Management System

*Overview*

A comprehensive inventory and sales management system designed for hypermarkets and large-scale product-based businesses. This solution enables administrators to efficiently manage product stock, track orders, handle suppliers, and analyze sales reports, all within a highly structured and scalable database environment.

*Project Role*

Team Leader: Marwan Amr Taher

Team Members: Ethar Waleed, Hanin Ebrahim, Merna Eid, Nour Elden Tarek

*Objectives*

Enable dynamic management of products, inventory, and restocks

Integrate loyalty and reward points system for customers

Support detailed sales reporting and data analytics

Automate product expiration handling and restocking

Improve decision-making with real-time data retrieval and dashboards

------ System Features----

*Core Modules*

Products & Categories: Handle thousands of items, grouped and sub-grouped into smart categories (e.g., Electronics, Dairy, Bakery).

Orders & Order Details: Record, track, and update customer orders with full transactional support.

Inventory Management: Monitor stock levels, reorder points, and last restock dates.

Restock Module: Generate restocking orders automatically based on thresholds.

Sales Reports: Yearly and monthly insights per category or product.

Customer Management: Store, retrieve, and analyze customer data.

Loyalty System: Manage reward points with expiry policies and update triggers.

*Database Schema Highlights*

Main Tables: Products, Orders, Customers, Suppliers, Inventory, Restock, Categories

Subcategory Extensions: Dairy, Bakery, Vegetables, Electronics, Frozen Foods

Sales Reports Table: Tracks performance per product/category

Loyalty System: Integrated with customer activities and purchase history

*System Logic & Procedures*

Subprograms & Procedures (10+):

Add Product

Calculate Total Sales in Date Range

Add Order & Update Stock

Update Loyalty Points

Apply Expiry Discounts

Check Product Availability

Restock Inventory

Packages:

StoreManagement: product, order, stock, loyalty

CustomerTransactions: loyalty management

Triggers:

Prevent expired product insertion

Loyalty point update validation

-----------APEX Integration----------------

Visual Dashboards:

Sales Reports (Yearly & Monthly)

Products requiring restocking

Customer points expiring soon

Unsold product tracking

Interactive Charts for analytics and monitoring

----------------- Sample Queries-----------

Products that haven’t been sold

Expiring loyalty points next week

Inventory status by product/supplier

Monthly profit per category

Customers with high order value

------------------Technologies Used--------------------

Database: Oracle SQL

Tools: Oracle APEX, SQL Developer

Development: PL/SQL (Procedures, Functions, Triggers)

Reporting: APEX Charts & Interactive Dashboards