🏪 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

1. 🏠 Global View: Dashboard with KPIs and materialized views
2. 📂 Data Loading: External tables and bulk data generation
3. 👥 CRUD & Errors: Customer management with error demonstration
4. 🛒 Transactions: Order creation workflow
5. ⭐ Reviews: Product rating system
6. 💰 Financial: Payments and invoices
7. 📝 Audit & Logs: Operation tracking
8. ⚡ Performance: Index impact demonstration
9. 🔧 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

1. CRUD Operations: Create, edit, and delete customers
2. Audit Verification: Check AUDITORIA table after operations
3. Error Handling: Try duplicate NIF to see error in LOG_ERROS
4. Performance: Compare query times with and without indexes
5. 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

1. Fork the repository
2. Create a feature branch (git checkout -b feature/AmazingFeature)
3. Commit your changes (git commit -m 'Add some AmazingFeature')
4. Push to the branch (git push origin feature/AmazingFeature)
5. 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

• Oracle Database Documentation
• Streamlit Documentation
• Python oracledb Driver

---

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.