🏪 E-commerce Management System with Oracle Database

A comprehensive e-commerce management system demonstrating advanced Oracle database concepts including tablespaces, partitioning, PL/SQL procedures, triggers, and materialized views, with a modern Streamlit web interface.

📋 Table of Contents

Overview
Features
Architecture
Project Structure
Prerequisites
Installation
Configuration
Usage
Database Schema
API Documentation
Testing
Contributing
Authors
License

Overview

This project implements a complete e-commerce management system focusing on:

Oracle Database Architecture with custom tablespaces, partitioning, and optimized indexes
PL/SQL Business Logic with generic CRUD procedures and error handling
Audit Mechanisms through triggers for complete operation tracking
Web Interface built with Streamlit for functional demonstration

The system is designed to showcase advanced database administration and programming concepts in Oracle.

Features

🗄️ Database Layer

Custom Tablespaces: Separate storage for data (TBS_ALFATECH_DATA) and indexes (TBS_ALFATECH_IDX)
Automatic Sequences: Primary key generation for all entities
Strategic Indexes: Optimized query performance
Range Partitioning: Order table partitioned by date for efficient data management

⚙️ Business Logic

Generic CRUD Procedures: PR_GENERICO_INSERT, PR_GENERICO_UPDATE, PR_GENERICO_DELETE
Specific Business Procedures: Customer management, orders, payments, stock control
Exception Handling: Centralized error logging in LOG_ERROS table
External Tables: CSV data loading without disk space consumption
Materialized Views: Pre-computed reports for performance

📝 Audit & Monitoring

DML Triggers: Automatic tracking of all INSERT/UPDATE/DELETE operations
Audit Table: Complete operation history with user and timestamp
Error Logging: Failed operation tracking for debugging
History Purge: Automated data archival process

🖥️ Web Interface

Dashboard: KPIs and aggregated data visualization
Customer Management: Full CRUD operations with validation
Order Processing: Multi-step transaction workflow
Product Reviews: Rating and comment system
Performance Demo: Index impact visualization with hints
Administration: Tablespace and partition management

Architecture

┌─────────────────────────────────────────────────────────────┐
│                    Streamlit Frontend                        │
│  (Dashboard | Customers | Orders | Reviews | Admin)          │
└─────────────────────────┬───────────────────────────────────┘
                          │
┌─────────────────────────▼───────────────────────────────────┐
│                     Python Layer                             │
│              (utils/db.py - Connection Pool)                 │
└─────────────────────────┬───────────────────────────────────┘
                          │
┌─────────────────────────▼───────────────────────────────────┐
│                   Oracle Database 12c                        │
│  ┌─────────────┐  ┌─────────────┐  ┌─────────────────────┐  │
│  │  PL/SQL     │  │  Triggers   │  │  Materialized Views │  │
│  │  Procedures │  │  (Audit)    │  │  (Reports)          │  │
│  └─────────────┘  └─────────────┘  └─────────────────────┘  │
│  ┌─────────────────────────────────────────────────────────┐│
│  │              Tables (Partitioned)                        ││
│  │  CLIENTE | PRODUTO | ENCOMENDA | PAGAMENTO | AUDITORIA  ││
│  └─────────────────────────────────────────────────────────┘│
│  ┌─────────────────────────────────────────────────────────┐│
│  │         Tablespaces: TBS_ALFATECH_DATA | _IDX           ││
│  └─────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────┘

Project Structure

TP-CBD/
├── app.py                      # Main Streamlit application
├── requirements.txt            # Python dependencies
├── .gitignore                  # Git ignore rules
├── LICENSE                     # MIT License
├── README.md                   # This file
├── CONTRIBUTING.md             # Contribution guidelines
│
├── .streamlit/
│   └── secrets.toml.example    # Database configuration template
│
├── modules/                    # Application modules
│   ├── __init__.py
│   ├── dashboard.py            # KPIs and materialized views
│   ├── clientes.py             # Customer CRUD operations
│   ├── produtos.py             # Product and stock management
│   ├── encomendas.py           # Order processing
│   ├── financeiro.py           # Payments and invoices
│   ├── auditoria.py            # Audit log visualization
│   ├── performance.py          # Index performance demo
│   ├── avaliacoes.py           # Product reviews
│   └── avancado.py             # Data loading and administration
│
├── utils/
│   └── db.py                   # Database connection utilities
│
├── scripts/                    # SQL scripts
│   ├── hostshare/              # Main SQL scripts (execute in order)
│   │   ├── 01_structure_tablespaces.sql
│   │   ├── 02_structure_sequences.sql
│   │   ├── 03_structure_tables.sql
│   │   ├── ...
│   │   └── 25_app_generate_data.sql
│   └── sql_loader/             # SQL*Loader control files
│
├── docs/                       # Additional documentation
│
└── relatorio_e_imagens/        # Reports and diagrams
    └── Anexos/                 # ER diagrams and screenshots

Prerequisites

Python 3.8+
Oracle Database 12c or higher (local or remote)
Git (for cloning the repository)

Installation

1. Clone the Repository

git clone https://github.com/DevPool1/TP-CBD.git
cd TP-CBD

2. Create Virtual Environment (Recommended)

python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

3. Install Dependencies

pip install -r requirements.txt

4. Configure Database Connection

cp .streamlit/secrets.toml.example .streamlit/secrets.toml

Edit .streamlit/secrets.toml with your Oracle credentials:

[oracle]
user = "YOUR_USERNAME"
password = "YOUR_PASSWORD"
dsn = "localhost:1521/orcl"

5. Execute SQL Scripts

Run the SQL scripts in numerical order using SQL*Plus or SQL Developer:

# Connect to Oracle as your user
sqlplus YOUR_USERNAME/YOUR_PASSWORD@localhost:1521/orcl

# Execute scripts in order
@scripts/hostshare/01_structure_tablespaces.sql
@scripts/hostshare/02_structure_sequences.sql
@scripts/hostshare/03_structure_tables.sql
# ... continue with remaining scripts

Script Categories:

Scripts	Description
01-06	Database structure (tablespaces, tables, indexes)
07-09	Core functionality (error handling, generic CRUD)
10-16	Data loading (seed data, external tables)
17-22	Maintenance (partitioning, audit, purge)
23-25	Application support (views, specific procedures)

6. Run the Application

streamlit run app.py

The application will open automatically at http://localhost:8501

Configuration

Database Connection

The application uses Streamlit's secrets management. Create .streamlit/secrets.toml:

[oracle]
user = "BSCBD"
password = "your_secure_password"
dsn = "localhost:1521/orcl"

Environment Variables (Alternative)

You can also configure the connection using environment variables:

export ORACLE_USER=BSCBD
export ORACLE_PASSWORD=your_secure_password
export ORACLE_DSN=localhost:1521/orcl

Usage

Web Interface Navigation

🏠 Global View: Dashboard with KPIs and materialized views
📂 Data Loading: External tables and bulk data generation
👥 CRUD & Errors: Customer management with error demonstration
🛒 Transactions: Order creation workflow
⭐ Reviews: Product rating system
💰 Financial: Payments and invoices
📝 Audit & Logs: Operation tracking
⚡ Performance: Index impact demonstration
🔧 Administration: System maintenance tools

Example Operations

Create a Customer

# The application calls the stored procedure
SP_CLIENTE_INSERT(p_nif, p_nome, p_email, p_password, p_telefone, p_data_nasc)

Generate Test Data

Navigate to "📂 Data Loading" → "🎲 Random Generation" and specify the quantity.

View Audit Trail

Navigate to "📝 Audit & Logs" to see all recorded DML operations.

Database Schema

Core Entities

Table	Description
`CLIENTE`	Customer information
`PRODUTO`	Product catalog
`ENCOMENDA`	Order headers (partitioned by date)
`LINHA_ENCOMENDA`	Order line items
`PAGAMENTO`	Payment records
`FATURA`	Invoice documents
`AVALIACAO`	Product reviews
`AUDITORIA`	Audit trail
`LOG_ERROS`	Error logging

ER Diagram

See relatorio_e_imagens/Anexos/diagrama_modelo.png for the complete entity-relationship diagram.

API Documentation

Database Utilities (`utils/db.py`)

# Execute SELECT query
df = run_query("SELECT * FROM CLIENTE")

# Call stored procedure
success, message = run_proc("SP_CLIENTE_INSERT", [nif, nome, email, pwd, tel, data])

Stored Procedures

Procedure	Parameters	Description
`SP_CLIENTE_INSERT`	nif, nome, email, password, telefone, data_nasc	Create customer
`SP_CLIENTE_UPDATE`	id, nome, email, telefone, ativo	Update customer
`SP_CLIENTE_DELETE`	id	Remove customer
`SP_ENCOMENDA_INSERT`	cliente_id, tipo, morada_id, loja_id, endereco_fat_id	Create order
`SP_EXPURGAR_HISTORICO`	data_corte	Archive old orders
`PR_GENERICO_INSERT`	tabela, colunas, valores	Generic insert

Testing

Manual Testing

CRUD Operations: Create, edit, and delete customers
Audit Verification: Check AUDITORIA table after operations
Error Handling: Try duplicate NIF to see error in LOG_ERROS
Performance: Compare query times with and without indexes
Data Generation: Generate bulk orders and verify

Validation Queries

-- Check audit records
SELECT * FROM AUDITORIA ORDER BY DATA_AUDITORIA DESC;

-- View error logs
SELECT * FROM LOG_ERROS ORDER BY ID_LOG DESC;

-- Partition statistics
SELECT PARTITION_NAME, NUM_ROWS 
FROM USER_TAB_PARTITIONS 
WHERE TABLE_NAME='ENCOMENDA';

Contributing

We welcome contributions! Please see CONTRIBUTING.md for guidelines.

Quick Start for Contributors

Fork the repository
Create a feature branch (git checkout -b feature/AmazingFeature)
Commit your changes (git commit -m 'Add some AmazingFeature')
Push to the branch (git push origin feature/AmazingFeature)
Open a Pull Request

Authors

Group 3 - Complementos de Base de Dados (CBD)

Alexandru Tutunaru
André Nunes
Cosmin Ciocan
Firmino Santos

Institution: Universidade do Algarve

License

This project is licensed under the MIT License - see the LICENSE file for details.

Acknowledgments

Note: This project was developed as part of the "Complementos de Base de Dados" course to demonstrate advanced database concepts and their practical application in a real-world scenario.

Name		Name	Last commit message	Last commit date
Latest commit History 38 Commits
.streamlit		.streamlit
modules		modules
relatorio_e_imagens		relatorio_e_imagens
scripts		scripts
utils		utils
wiki		wiki
.gitignore		.gitignore
CONTRIBUTING.md		CONTRIBUTING.md
LICENSE		LICENSE
README.md		README.md
app.py		app.py
requirements.txt		requirements.txt

Folders and files

Latest commit

History

Repository files navigation

🏪 E-commerce Management System with Oracle Database

📋 Table of Contents

Overview

Features

🗄️ Database Layer

⚙️ Business Logic

📝 Audit & Monitoring

🖥️ Web Interface

Architecture

Project Structure

Prerequisites

Installation

1. Clone the Repository

2. Create Virtual Environment (Recommended)

3. Install Dependencies

4. Configure Database Connection

5. Execute SQL Scripts

6. Run the Application

Configuration

Database Connection

Environment Variables (Alternative)

Usage

Web Interface Navigation

Example Operations

Create a Customer

Generate Test Data

View Audit Trail

Database Schema

Core Entities

ER Diagram

API Documentation

Database Utilities (utils/db.py)

Stored Procedures

Testing

Manual Testing

Validation Queries

Contributing

Quick Start for Contributors

Authors

License

Acknowledgments

About

Resources

Contributing

Stars

Watchers

Forks

Releases

Packages

Contributors

Languages

Database Utilities (`utils/db.py`)