Project Overview
This project involved designing a database system for a social media platform, Fakebook, through three phases: relational database schema design, Java-based SQL query execution, and a transition to a NoSQL MongoDB environment for analytics.
Phase 1: Relational Database Design
We built the Fakebook database schema using SQL, modeling user interactions, friendships, posts, and activity logs. Key components included:
- Entity-Relationship (ER) modeling to define users, friends, and interactions.
- SQL schema creation with foreign key constraints and optimized indexing.
- Queries to retrieve user connections, event participation, and trending posts.
Phase 2: SQL Query Execution with JDBC
Implemented advanced SQL queries using Java Database Connectivity (JDBC) to efficiently retrieve data from Fakebook's relational database. This phase involved:
- Writing complex SQL queries for social media insights.
- Integrating queries into a Java application for real-time data retrieval.
- Optimizing query execution time and indexing strategies.
Phase 3: Migration to MongoDB
Transitioned Fakebook’s analytics to a NoSQL MongoDB system to handle unstructured and scalable data.
- Exported relational database data to JSON for MongoDB ingestion.
- Designed efficient MongoDB queries to analyze user activity.
- Implemented friend recommendation and engagement analytics using MongoDB’s aggregation pipeline.
Key Takeaways
- Structured data retrieval with SQL complemented by flexible NoSQL analytics.
- Hands-on experience in database migration from relational to NoSQL.
- Optimized data query performance using indexing and query refinement.