Social Media DBMS
Database Management System with Java CLI
Overview
A comprehensive social media database management system featuring a fully normalized schema with 10+ interconnected entities designed to handle complex social networking operations. The system efficiently manages 1000+ transactions per second with optimized query performance and data integrity.
Built with Java and MySQL, this project demonstrates expertise in database design principles, SQL optimization, and enterprise application development. The interactive command-line interface provides an intuitive way to interact with the database, showcasing CRUD operations, complex queries, and transaction management.
Key Features
Normalized Database Schema
Fully normalized database design (3NF) with 10+ entities including Users, Posts, Comments, Likes, Follows, Messages, Groups, and more. Eliminates redundancy and ensures data consistency.
Interactive Java CLI
User-friendly command-line interface built with Java providing menu-driven navigation, input validation, and formatted output. Supports user authentication and role-based access.
High-Performance Transactions
Handles 1000+ transactions per second with optimized SQL queries, proper indexing, and connection pooling. Implements transaction isolation levels for data consistency.
Complex Query Support
Advanced SQL queries including JOINs across multiple tables, aggregate functions, subqueries, and window functions for analytics and reporting features.
Social Features Implementation
Complete social media functionality including user profiles, posts, comments, likes, follows, friend requests, groups, messaging, and notification system.
Data Integrity & Security
Enforced referential integrity with foreign keys, check constraints, and triggers. Implements prepared statements to prevent SQL injection attacks.
Technical Implementation
Database Schema Design
• Users table with profile information and authentication data
• Posts, Comments, and Likes tables with hierarchical relationships
• Followers/Following junction table for many-to-many relationships
• Groups and Group_Members for community features
• Messages table with sender-receiver relationships
• Notifications table with polymorphic associations
Java Application Architecture
• Layered architecture: Presentation (CLI) → Business Logic → Data Access
• DAO (Data Access Object) pattern for database operations
• JDBC for database connectivity with connection pooling
• Service layer for business logic and transaction management
• Exception handling with custom error messages
SQL Optimization
• Composite indexes on frequently queried columns
• Query optimization with EXPLAIN ANALYZE
• Denormalization for read-heavy operations (follower counts)
• Stored procedures for complex business logic
• Views for frequently used JOIN queries
CLI Features
• Menu-driven interface with clear options
• Input validation and sanitization
• Formatted table output for query results
• Session management and authentication
• Error handling with user-friendly messages
Database Entities
Core Entities
Users, Posts, Comments, Likes, Shares - fundamental social media components with proper relationships and constraints
Social Relationships
Followers, Following, Friends, Blocks - managing complex user-to-user relationships with junction tables
Community Features
Groups, Group_Members, Group_Posts - supporting community building and group interactions
Communication
Messages, Conversations, Notifications - enabling user-to-user communication and system notifications
Content Management
Media, Hashtags, Tags - managing multimedia content and content discovery features
Analytics
User_Activity, Post_Analytics - tracking engagement metrics and user behavior patterns
Challenges & Solutions
Challenge: Complex Relationship Modeling
Designing database schema to represent complex many-to-many relationships like followers, groups, and bidirectional friendships.
Solution: Implemented junction tables with composite primary keys and proper foreign key constraints. Used self-referencing tables for user relationships and added timestamps for temporal queries. Created database views to simplify complex JOIN operations.
Challenge: Query Performance at Scale
Slow query performance when retrieving user feeds with posts from multiple users, comments, and likes aggregated together.
Solution: Created strategic composite indexes on frequently queried columns (user_id, created_at). Implemented query optimization using EXPLAIN plans. Added denormalized counters for likes and comments to avoid expensive aggregate queries. Used pagination to limit result sets.
Challenge: Data Integrity and Consistency
Maintaining referential integrity when users delete posts or accounts while having dependent comments, likes, and relationships.
Solution: Implemented cascading deletes where appropriate and soft deletes for user accounts. Added database triggers to maintain aggregate counts. Used transactions with proper isolation levels to ensure consistency during concurrent operations.
Challenge: SQL Injection Prevention
Risk of SQL injection attacks through user inputs in the CLI application.
Solution: Exclusively used PreparedStatement instead of Statement for all database operations. Implemented input validation and sanitization at the application layer. Added parameter binding for all user inputs and escaped special characters.
Performance Metrics
Key Learnings
Database Design Principles
Mastered normalization techniques (1NF, 2NF, 3NF), ER modeling, and designing scalable database schemas with proper relationships and constraints.
Advanced SQL
Expertise in complex queries including JOINs, subqueries, aggregate functions, window functions, and query optimization techniques.
Java Database Connectivity
Hands-on experience with JDBC, prepared statements, connection pooling, and implementing DAO pattern for database operations.
Transaction Management
Understanding ACID properties, isolation levels, and implementing proper transaction handling for data consistency.
Performance Optimization
Learned indexing strategies, query optimization, denormalization trade-offs, and using EXPLAIN plans for performance tuning.
Application Architecture
Implemented clean architecture with separation of concerns, layered design, and design patterns for maintainable code.
Technologies & Tools
Core Technologies
- • Java 17 - Application development
- • MySQL 8.0 - Relational database
- • JDBC - Database connectivity
- • SQL - Query language
- • Maven - Dependency management
Database Features
- • Stored Procedures - Complex logic
- • Triggers - Automated actions
- • Views - Query simplification
- • Indexes - Performance optimization
- • Transactions - ACID compliance
CLI Capabilities
User Operations
- • User registration and authentication
- • Profile management and updates
- • View user feed and timeline
- • Search users and profiles
Content Operations
- • Create, edit, and delete posts
- • Comment on posts
- • Like and share content
- • View trending posts and hashtags
Social Features
- • Follow/unfollow users
- • Send friend requests
- • Direct messaging
- • Create and join groups
Analytics
- • View engagement statistics
- • Track follower growth
- • Popular content analysis
- • User activity reports
Project Outcome
Successfully designed and implemented a production-ready social media database system that demonstrates enterprise-level database design skills and Java application development. The project showcases deep understanding of relational database concepts, normalization principles, and query optimization techniques.
The interactive CLI provides a practical interface for database operations, making it easy to demonstrate the system's capabilities. The implementation handles high transaction volumes efficiently while maintaining data integrity and security, making it suitable for real-world social media application backends.