This repository contains the implementation of a database system for an e-commerce platform, developed as part of a Database Systems course project. The project is implemented using PostgreSQL and includes data integration, integrity constraints, analytical queries, triggers, views, and technical documentation written in Persian.
- Relational database schema for an online store
- ETL pipeline using staging tables
- Data cleaning and transformation
- Enforcement of business rules using constraints and triggers
- Advanced SQL analytical queries
- Indexing for better performance
- Views and reporting queries
- Persian technical report written in LaTeX (XeLaTeX)
- Database: PostgreSQL
- Query Language: SQL / PLpgSQL
- PostgreSQL 12+
- psql client
sudo apt update
sudo apt install postgresql postgresql-client texlive-xetex
createdb dbkala
From terminal:
psql dbkala -f sql/01_load_branches_suppliers_products.sql
Or inside psql:
psql dbkala
\i sql/01_load_branches_suppliers_products.sql
Run scripts in this order:
- Data loading
- Constraints
- Queries
- Views
Inside psql:
\copy stg_bdbkala_full FROM 'dataset/BDBKala_full.csv' CSV HEADER;
- Load CSV files into staging tables
- Convert text fields to correct types
- Generate internal IDs
- Populate core tables
- Email validation
- Discount limits
- Order/delivery date checks
- Status transitions
- Wallet debt limit
- Packaging restrictions
- Return validation
- Feedback validation
Includes:
- Average profit by category
- Popular products
- Valuable customers
- Category association
- Delivery delay detection
- VAT calculation
- Wallet turnover
- BNPL eligibility
- Supplier ranking
- Customer lifetime value
- JSON attribute analysis