Inventory Management System — CTEs & SQL Window Functions

Course: C11665 - DPR400210: Database Programming Instructor: Eric Maniraguha Assignment: Group Assignment — CTEs & SQL Window Functions Project Database Engine: MySQL 8.0+

---

1. Business Problem

Our company's procurement office, warehouse supervisors, and finance team currently rely on manual spreadsheets to track purchase orders, employee workload, and supplier payments. This makes it hard to answer basic operational questions quickly, for example:

• Which warehouses generate the most purchasing spend?
• Which employees are the top procurement performers, and how does that vary across warehouses?
• How is a supplier's payment history trending over time?
• What is the company's management/reporting structure?

This project models an Inventory Management System with a relational database and uses Common Table Expressions (CTEs) and Window Functions to answer these questions directly in SQL.

---

2. Database Schema

The database consists of 5 related tables:

Table	Description	Primary Key	Foreign Keys
warehouses	Company warehouses (Electronics, Pharmaceuticals, etc.)	warehouse_id	—
employees	Staff, including a self-referencing supervisor relationship	employee_id	warehouse_id → warehouses, supervisor_id → employees
suppliers	Registered suppliers	supplier_id	—
purchase_orders	Stock orders placed with a supplier	order_id	supplier_id → suppliers, employee_id → employees, warehouse_id → warehouses
payments	Charges generated per purchase order	payment_id	order_id → purchase_orders

Full DDL and sample data: 01_schema_and_data.sql

---

3. ER Diagram

erDiagram
    WAREHOUSES ||--o{ EMPLOYEES : employs
    WAREHOUSES ||--o{ PURCHASE_ORDERS : hosts
    EMPLOYEES ||--o{ EMPLOYEES : supervises
    EMPLOYEES ||--o{ PURCHASE_ORDERS : processes
    SUPPLIERS ||--o{ PURCHASE_ORDERS : fulfills
    PURCHASE_ORDERS ||--o| PAYMENTS : generates

    WAREHOUSES {
        int warehouse_id PK
        string warehouse_name
        string location
    }
    EMPLOYEES {
        int employee_id PK
        string first_name
        string last_name
        string role
        int warehouse_id FK
        int supervisor_id FK
        decimal salary
        date hire_date
    }
    SUPPLIERS {
        int supplier_id PK
        string company_name
        string contact_person
        string supplier_type
        string phone
    }
    PURCHASE_ORDERS {
        int order_id PK
        int supplier_id FK
        int employee_id FK
        int warehouse_id FK
        string item_name
        int quantity
        date order_date
        string status
    }
    PAYMENTS {
        int payment_id PK
        int order_id FK
        decimal amount
        string payment_status
        date payment_date
    }

Loading

(GitHub renders this Mermaid diagram automatically. If you also need a static image, export it via the Mermaid Live Editor and add it to a /screenshots folder.)

---

4. CTE Implementations

File: 02_cte_queries.sql

#	Type	Business Question	Business Value
A1	Simple CTE	Which suppliers had a received order and what were we billed?	Quick, readable order list without repeating JOIN logic
A2	Multiple CTEs	Which warehouse spends the most relative to its staffing?	Supports resource-allocation decisions
A3	Recursive CTE	What is the full employee reporting hierarchy (org chart)?	Visualizes management structure for HR
A4	CTE with Aggregation	What is the total/average payment amount processed per employee?	Supports performance reviews
A5	CTE + JOINs	Full purchase order history across supplier, employee, warehouse, payment	Single audit trail for supplier billing inquiries

Screenshots — CTE Results

A1. Simple CTE — Received orders with payment

A2. Multiple CTEs — Warehouse spend vs. staffing

A3. Recursive CTE — Employee reporting hierarchy

A4. CTE with Aggregation — Payment summary per employee

A5. CTE combined with JOINs — Full purchase order history

---

5. Window Function Implementations

File: 03_window_functions.sql

#	Category	Functions Used	Business Question
B1	Ranking	ROW_NUMBER(), RANK(), DENSE_RANK(), PERCENT_RANK()	Rank employees by payment volume, overall and within warehouse
B2	Aggregate	SUM() OVER(), AVG() OVER(), MIN() OVER(), MAX() OVER()	Running spend total and warehouse benchmarks per payment
B3	Navigation	LAG(), LEAD()	Compare each supplier's payment to their previous/next payment
B4	Distribution	NTILE(), CUME_DIST()	Split employees into performance quartiles

Screenshots — Window Function Results

B1. Ranking Functions — Employee payment rank (overall & by warehouse)

B2. Aggregate Window Functions — Running total & warehouse benchmarks

B3. Navigation Functions — Previous/next payment per supplier

B4. Distribution Functions — Employee performance quartiles

---

6. Analysis and Findings

Descriptive Analysis — What happened? The Electronics Warehouse generated the highest total purchasing spend, followed by the Furniture Warehouse and the Pharmaceuticals Warehouse. Individually, stock clerk Claude Mugisha (Furniture Warehouse) processed the highest total payment volume of any employee, placing him in the top performance quartile.

Diagnostic Analysis — Why did it happen? The Electronics Warehouse has the largest number of received purchase orders, which naturally increases its total spend even though individual electronics items are relatively low-cost. At the individual level, Claude Mugisha's high average payment per order (rather than sheer order count) drove his ranking, suggesting furniture items — office chairs, desks, filing cabinets — carry a higher average unit cost than routine electronics or grocery restocks.

Prescriptive Analysis — What actions should be taken?

1. Consider reviewing procurement processes for the Grocery Warehouse, which shows lower spend and fewer received orders relative to other warehouses, to see whether restocking frequency or supplier terms need adjustment.
2. Investigate the Pending payment-status orders identified in the CTE and window function queries and follow up on outstanding supplier payments.
3. Use the quartile ranking from NTILE(4) as an input to a fair, data-driven employee performance/bonus review process rather than relying on anecdotal impressions of procurement staff performance.

---

7. How to Run

mysql -u root -p < 01_schema_and_data.sql
mysql -u root -p inventory_management < 02_cte_queries.sql
mysql -u root -p inventory_management < 03_window_functions.sql

All scripts were tested end-to-end on MySQL-compatible syntax (MariaDB 10.11) and execute without errors.

---

8. References

• MySQL 8.0 Reference Manual — Window Functions
• MySQL 8.0 Reference Manual — WITH (Common Table Expressions)
• Course lecture materials, C11665 - DPR400210: Database Programming

---

9. Academic Integrity Statement

This work was produced by our group specifically for this assignment. Where external references were consulted (see References above), the queries and schema themselves are our own design and were not copied from classmates or online repositories, in accordance with UNILAK's academic integrity policy.

---

Repository Structure

database_programming_assignment1_[studentID]_[firstname]/
├── 01_schema_and_data.sql
├── 02_cte_queries.sql
├── 03_window_functions.sql
├── README.md
└── screenshots/
    ├── er_diagram.png
    ├── a1_simple_cte.png
    ├── a2_multiple_ctes.png
    ├── a3_recursive_cte.png
    ├── a4_cte_aggregation.png
    ├── a5_cte_joins.png
    ├── b1_ranking_functions.png
    ├── b2_aggregate_functions.png
    ├── b3_navigation_functions.png
    └── b4_distribution_functions.png