DBMS Prototype - Database Management System

A comprehensive, file-based Database Management System (DBMS) prototype built in Java, implementing core database functionalities including SQL query processing, transaction management, indexing, user authentication, and concurrency control.

Author: Bryan Vela
Version: 1.0.0

Features

Core Database Operations

• Database Management: Create, use, and list databases
• Table Operations: Create tables with column definitions and data types
• SQL Query Support: SELECT, INSERT, UPDATE, DELETE operations
• Data Types: INTEGER, INT, VARCHAR(n), STRING, TEXT, FLOAT, DATE, BOOLEAN

Security & Authentication

• User Authentication: Secure login with SHA-256 password hashing
• Session Management: Session-based access control
• Password Recovery: Security question-based password reset
• Audit Logging: Comprehensive security event tracking
• Access Control: User-based database access permissions

Advanced Database Features

• B-Tree Indexing: Order 5 B-Tree implementation for efficient data retrieval
• Transaction Management: Full ACID-compliant transactions
• Concurrency Control: Lock management and deadlock detection
• Query Parser & Validator: Robust SQL parsing and semantic validation
• File-Based Storage: Custom storage system with organized file structure

User Interface

• Interactive Console: Full-featured command-line interface
• Command History: Track and retrieve previous commands
• Contextual Prompts: Dynamic prompts showing current database context
• Comprehensive Help: Built-in help system and command documentation

Architecture

Core Components

src/com/dbms/
├── Main.java                 # Application entry point
├── controller/               # Business logic layer
│   └── DBMSController.java   # Main database operations controller
├── model/                    # Data models
│   ├── Database.java         # Database entity
│   ├── Table.java            # Table entity
│   ├── Column.java           # Column definition
│   └── User.java             # User entity
├── query/                    # SQL processing
│   ├── QueryParser.java      # SQL statement parsing
│   ├── QueryExecutor.java    # Query execution engine
│   ├── QueryValidator.java   # Semantic validation
│   └── [Query Types].java    # Specific query implementations
├── storage/                  # Persistence layer
│   ├── StorageManager.java   # File-based storage system
│   ├── FileHandler.java      # File I/O operations
│   └── IStorageProvider.java # Storage abstraction
├── index/                    # Indexing system
│   ├── BTree.java            # B-Tree implementation
│   ├── BTreeNode.java        # B-Tree node structure
│   └── IndexManager.java     # Index management
├── transaction/              # Transaction management
│   ├── TransactionManager.java # ACID transaction control
│   ├── Transaction.java      # Transaction entity
│   └── TransactionOperation.java # Operation tracking
├── concurrency/              # Concurrency control
│   └── LockManager.java      # Lock and deadlock management
├── security/                 # Security layer
│   ├── Authentication.java   # User authentication
│   ├── AuditLogger.java      # Security event logging
│   ├── SessionManager.java   # Session management
│   └── PasswordRecovery.java # Password reset functionality
├── ui/                       # User interface
│   └── ConsoleUI.java        # Interactive command-line interface
└── util/                     # Utilities
    └── Constants.java        # Application constants

Storage Structure

storage/
├── users/          # User account data
├── logs/
│   ├── audit/      # Security audit logs
│   └── transactions/ # Transaction logs
├── database/       # Database files
├── indexes/        # B-Tree index files
├── security/       # Security-related files
├── config/         # Configuration files
└── general/        # General purpose files

Getting Started

Prerequisites

• Java JDK 8 or higher
• Terminal/Command Prompt access

Installation & Setup

1. Clone the repository

   git clone [repository-url]
   cd dbms-prototype

2. Compile the project

   javac -cp . -d bin src/com/dbms/**/*.java

3. Run the application

   java -cp bin com.dbms.Main

First Time Setup

1. Register a new user

   • Choose option 2 from the login menu
   • Provide username, password, email, and security question

2. Login and start using the DBMS

   • Use your credentials to login
   • Start creating databases and tables

📖 Usage Guide

Basic Commands

Database Operations

CREATE DATABASE mydb          -- Create a new database
USE mydb                     -- Select a database to use
SHOW DATABASES              -- List all databases

Table Operations

-- Create a table
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name VARCHAR(50),
    email STRING,
    age INTEGER
);

SHOW TABLES                 -- List tables in current database
DESCRIBE users              -- Show table structure

Data Operations

-- Insert data
INSERT INTO users (id, name, email, age)
VALUES (1, 'John Doe', 'john@email.com', 25);

-- Query data
SELECT * FROM users;
SELECT name, email FROM users WHERE age > 20;

-- Update data
UPDATE users SET age = 26 WHERE id = 1;

-- Delete data
DELETE FROM users WHERE id = 1;

System Commands

HELP                        -- Show available commands
STATUS                      -- Show system status
LOGOUT                      -- Logout current user
EXIT                        -- Exit application

Advanced Features

Transaction Management

BEGIN TRANSACTION;          -- Start a transaction
-- Execute multiple operations
COMMIT;                     -- Commit changes
-- or
ROLLBACK;                   -- Rollback changes

🔧 Technical Specifications

Supported Data Types

• INTEGER / INT: Whole numbers
• VARCHAR(n): Variable-length strings with max length
• STRING / TEXT: Variable-length strings
• FLOAT: Decimal numbers
• DATE: Date values
• BOOLEAN: True/false values

B-Tree Indexing

• Order: 5 (maximum 4 keys per node)
• Operations: Insert, search, delete, range queries
• Performance: O(log n) for basic operations

Transaction Properties

• Atomicity: All operations succeed or none do
• Consistency: Database maintains valid state
• Isolation: Concurrent transactions don't interfere
• Durability: Committed changes persist

Security Features

• Password Hashing: SHA-256 with salt
• Session Management: Secure session tokens
• Audit Logging: All security events tracked
• Access Control: User-based permissions

📁 Project Structure

dbms-prototype/
├── README.md               # Project documentation
├── src/                    # Source code
├── bin/                    # Compiled classes
├── docs/                   # Architecture diagrams
│   ├── 1_MainArchitecture.png
│   ├── 2_UML_Diagram.png
│   ├── 3_FileStorageStructure.png
│   ├── 4_AuthenticationFlow.png
│   ├── 5_PasswordRecoveryFlow.png
│   ├── 6_QueryExecutionFlow.png
│   ├── 7_TransactionFlow.png
│   ├── 8_ConcurrencyControlFlow.png
│   └── 9_IndexUpdateFlow.png
├── storage/                # Runtime data files
└── lib/                    # External dependencies (if any)

Testing

Manual Testing

The system can be tested through the interactive console interface:

1. User Management: Registration, login, password recovery
2. Database Operations: Create/use databases, create tables
3. Query Operations: Insert, select, update, delete data
4. Transaction Testing: Begin, commit, rollback operations
5. Security Testing: Authentication, session management

Sample Test Scenarios

# Test database creation and table operations
CREATE DATABASE testdb;
USE testdb;
CREATE TABLE products (id INTEGER, name STRING, price FLOAT);

# Test data operations
INSERT INTO products (id, name, price) VALUES (1, 'Laptop', 999.99);
SELECT * FROM products;
UPDATE products SET price = 899.99 WHERE id = 1;
DELETE FROM products WHERE id = 1;

🛠️ Development

Building from Source

# Compile all Java files
javac -cp . -d bin src/com/dbms/**/*.java

# Run the application
java -cp bin com.dbms.Main

Code Organization

• Modular Design: Clear separation of concerns
• Interface-based: Abstractions for storage and other components
• Exception Handling: Comprehensive error management
• Documentation: Javadoc comments throughout

---

Contact: Bryan Vela