🏴‍☠️ One Piece Database System

A comprehensive MySQL database management system themed around the One Piece universe, featuring advanced SQL operations, relational database design, and an interactive Python CLI application.

📋 Overview

This project implements a fully-featured relational database system inspired by the One Piece universe. It manages a complex web of characters, organizations, territories, devil fruits, bounties, swords, haki abilities, and intelligence reports through 18 interconnected tables. Built with raw SQL (no ORM) and featuring an interactive CLI for comprehensive CRUD operations.

✨ Features

• 18 Interconnected Tables: Complex relational schema with proper normalization
• Advanced SQL Operations: JOINs, subqueries, aggregations, GROUP BY, HAVING
• Interactive CLI: User-friendly command-line interface for all operations
• Transaction Management: Manual commit/rollback for data integrity
• Parameterized Queries: Protection against SQL injection
• Comprehensive Analytics: Bounty evolution, power rankings, crew statistics

Database Schema (18 Tables)

Core Entities:

1. Territory - Islands and locations across the seas
2. Person - Base table for all characters
3. Pirate - Pirate-specific attributes
4. Marine - Marine ranks and justice types
5. Revolutionary - Revolutionary Army members
6. Celestial_Dragon - World Nobles
7. Organization - Crews, divisions, and factions
8. Devil_Fruit - Paramecia, Zoan, and Logia fruits

🚀 Quick Start

Prerequisites

• MySQL 8.0+ installed and running
• Python 3.8+
• pip (Python package manager)

Installation

1. Clone the repository

   git clone https://github.com/yourusername/one-piece-database-system.git
   cd one-piece-database-system

2. Install Python dependencies

   pip install -r requirements.txt

3. Set up MySQL databasenizations

4. Intel_Report - Intelligence gathering records

5. Threat_Level - Danger classification system

6. Threat_Assessment - Territory threat evaluations

7. Historical_Event - Major incidents and events

8. Major_Incident - Critical historical moments

PREREQUISITES

• MySQL 8.0+
• Python 3.8+
• PyMySQL (pip install pymysql)

SETUP STEPS

1. Install dependency: pip install pymysql

   (If you want a dedicated project user instead of root, inside mysql client run)

-- 1. Create the user (uses standard password authentication) CREATE USER 'pirate_king'@'localhost' IDENTIFIED BY 'onepiece123';

-- 2. Grant full control over your specific database GRANT ALL PRIVILEGES ON mini_world_db.* TO 'pirate_king'@'localhost';

-- 3. Save changes FLUSH PRIVILEGES;

-- 4. Exit MySQL EXIT;

2. Create schema: mysql -u -p < src/schema.sql
3. Populate data: mysql -u -p < src/populate.sql
4. Run application: python src/main_app.py You will be prompted for username/password. Autocommit disabled; writes require explicit commit.

💻 Application Features

📊 Read Operations (Queries)

1. View High Bounty Pirates - Display pirates sorted by latest bounty with epithet
2. Search Person Database - Comprehensive profile search by name (partial match)
3. List Devil Fruit Users - Show all current fruit users with fruit types
4. Filter Marines by Rank - Find marines by exact rank with justice philosophy
5. View Organization Territories - Display territories controlled by organizations
6. Organization Power Rankings - Aggregate statistics: members, territories, total bounties
7. Bounty Evolution Timeline - Track historical bounty progression for pirates
8. Strongest Pirate Crews Ranking - Calculate crew power scores with detailed metrics

✏️ Write Operations (Transactions)

9. File Intel Report (INSERT) - Add new intelligence reports with validation
10. Update Person Status (UPDATE) - Modify character status with enum validation
11. Erase Territory (DELETE) - Remove territories with impact analysis and confirmation

DEMONSTRATION IN VIDEO Reads (1–8): Execute each menu item once, show output. Writes (9–11): In MySQL client first run a SELECT to show state; in Python CLI perform operation; rerun same SELECT to show change. Order matches list above. Detailed sequences used: INSERT (Menu 9) Pre: SELECT * FROM Intel_Report WHERE Filing_Agent_ID = 26 AND Report_SNo = 999; -- expect Empty set

🔍 Usage Examples

Query Example: Search Person

Enter name to search: Luffy

Person Profile:
- Name: Monkey D. Luffy
- Status: Active
- Home: East Blue - Fūsha Village
- Devil Fruit: Gomu Gomu no Mi (Paramecia)
- Haki: Conqueror's, Armament, Observation
- Current Bounty: 3,000,000,000
- Role: Captain of Straw Hat Pirates

Transaction Example: Update Status

## 🛠️ Technical Implementation

### Core Principles
- **Raw SQL Only** - No ORM, no pandas, pure SQL for maximum control
- **Parameterized Queries** - All user inputs sanitized via `%s` placeholders
- **Transaction Management** - Manual commit/rollback for data integrity
- **DictCursor** - Dictionary-based row access for cleaner code
- **Input Validation** - Type checking, enum validation, non-empty constraints
- **Error Handling** - Try/except blocks around all database operations

### Advanced SQL Features
- **Complex JOINs** - Multi-table INNER and LEFT joins across 6+ tables
- **Correlated Subqueries** - Fetch latest bounties per pirate
- **Aggregations** - COUNT, SUM, COALESCE, GROUP_CONCAT
- **GROUP BY + HAVING** - Advanced filtering for analytics
- **Computed Metrics** - Percentage calculations, power score formulas
## 📁 Project Structure

one-piece-database-system/ ├── src/ │ ├── schema.sql # Database structure (DDL) │ ├── populate.sql # Sample data (DML) │ └── app.py # Python CLI application ├── requirements.txt # Python dependencies

📊 Database Statistics

Sample Data Coverage:

• 60+ characters (Pirates, Marines, Revolutionaries, Celestial Dragons)
• 36 territories across all Blues and Grand Line
• 24 organizations (crews, divisions, government entities)
• 40+ devil fruits (Paramecia, Zoan, Logia)
• 15 named swords with grades
• 85+ bounty records for timeline tracking
• 80+ affiliation records
• 15 major historical incidents

🔒 Data Integrity

Constraints Implemented:

• Primary keys on all 18 tables
• Foreign keys with CASCADE/SET NULL policies
• CHECK constraints (coordinate ranges, positive bounties)
• UNIQUE constraints (territory names, devil fruit names)
• NOT NULL on critical attributes
• ENUM types for controlled vocabularies

🤝 Contributing

Contributions are welcome! Please feel free to submit a Pull Request. For major changes, please open an issue first to discuss what you would like to change.

📝 License

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

🙏 Acknowledgments

• Database design and application developed as an educational project
• One Piece universe created by Eiichiro Oda
• Inspired by the world's greatest treasure hunt adventure

📧 Contact

For questions or feedback, please open an issue in this repository.

---

Note: This is a fan project for educational purposes. One Piece and all related characters are property of Eiichiro Oda and Shueishs closing connection.

SUBMISSION LAYOUT .zip .mp4 phase3.pdf README.md src/ schema.sql populate.sql main_app.py

CREDITS Database and application developed for Phase 4 project. One Piece theme inspired by Eiichiro Oda.