Jigi BoxZ

Background & Context

This academic project was developed for the Introduction to Database Systems course, aiming to apply fundamental database design and SQL implementation skills in a practical business context.

The case study, Jigi BoxZ, represents an electronic retail company that manages product purchases from vendors and sales to customers. At the time of the case, the company was still using a manual management process, which caused difficulties in data tracking, transaction processing, and record accuracy.

Our task was to design and implement a relational database system to digitalize Jigi BoxZ’s operations, focusing on structuring relationships among entities such as Staff, Vendor, Customer, Product, Purchase, and Sales.

Project Objective

To design, build, and test a complete database system that could manage the company’s purchasing and sales activities efficiently using SQL Server Developer 2019.
The project aimed to strengthen practical understanding of:

• Entity-Relationship Diagram (ERD) design

• Data Definition Language (DDL) for database creation

• Data Manipulation Language (DML) for data insertion and transaction simulation

• Advanced SQL queries using joins, subqueries, and views

Project Scope & Deliverables

1. Database Design (Conceptual & Logical)

• Developed an Entity Relationship Diagram (ERD) modeling six core entities: Staff, Customer, Vendor, Product, Category, and Transaction.

• Defined primary and foreign key relationships to support both sales and purchase transactions.

• Applied integrity constraints such as:

  • Staff and Customer DOB must be before 2000

  • Product Price between 1000–2200

  • Product Name length > 10 characters

  • Category limited to 10 predefined options (e.g., “Smartphones”, “Wireless Earbuds”, etc.)

2. Database Implementation (DDL & DML)

• Created database schema using DDL to define tables, constraints, and data types.

• Populated master and transaction tables with realistic test data:

  1. ≥10 records for master tables (e.g., product, category, customer)

  2. ≥15 transaction records

  3. ≥25 transaction detail records

• Simulated sales and purchase processes using DML INSERT and UPDATE queries.

3. Query Development & Data Retrieval

Designed and executed advanced SQL queries to extract business insights, including:

• Total transactions per customer and staff in 2023

• Product sales performance with filtering and aggregation

• Staff purchase history and vendor collaboration statistics

• Quarter-based sales performance reports

• Alias subqueries for price comparisons, sales summaries, and date formatting

• Custom views (MostAndLessBoughtProductPerCustomer and SpentAboveMaximumTotalSalesCustomerIn2023) for managerial analysis

4. Views & Data Presentation

• Created dynamic SQL views combining multiple tables to highlight:

  1. Most and least purchased products per customer

  2. Customers who spent above the maximum average transaction in 2023

• Ensured query readability through aliasing, data concatenation, and formatting (e.g., converting IDs, adding currency symbols, and date formatting).