Skip to content

README.md

Latest commit

 

History

History
50 lines (38 loc) · 2.02 KB

README.md

File metadata and controls

50 lines (38 loc) · 2.02 KB

AppleCare Analytics & Inventory Intelligence System using SQL and NoSQL

Implemented a SQL–NoSQL analytics database with ERD modeling and advanced queries for AppleCare-like repair and inventory intelligence.

Project Overview

Designed and implemented a hybrid SQL–NoSQL database system to analyze AppleCare repair trends and optimize parts inventory. Built a normalized relational schema with ERDs for transactional data (repairs, devices, parts usage) and a NoSQL store for semi-structured diagnostic logs. Developed complex SQL queries for business reporting, including SLA compliance, part-failure forecasting, and cost analysis. The system supports scalable analytics workflows for Apple-like retail and service operations.

ERD Design (SQL)

Core Entities

  • Device (iPhone, MacBook, Watch, etc.)
  • Customer
  • RepairJob
  • Part
  • ServiceCenter
  • Technician
  • Warranty
  • RepairPartUsage (junction table)
  • Inventory (store-wise)

Relationships

  • One customer → many repair jobs
  • One device → many repair jobs
  • One repair job → many parts used
  • One service center → many repair jobs
  • One warranty → one device

🚀 Features

SQL Relational Database (PostgreSQL/MySQL)

  • Customers, devices, repair jobs, service centers, technicians, parts, inventory, and repair-part usage.
  • Fully normalized schema with primary and foreign key constraints.
  • Supports advanced business analytics queries.

NoSQL Database (MongoDB)

  • Diagnostic logs, customer feedback, device error history, and service center activity logs.
  • Handles semi-structured JSON data for analytics.

ETL Pipeline (Python)

  • Extracts data from PostgreSQL.
  • Transforms repair job data with derived features (e.g., repair time category).
  • Loads into MongoDB for hybrid analytics.

Analytics & Business Insights

  • Repair trends, SLA compliance, parts failure forecasting, inventory optimization.
  • Customer feedback and sentiment tracking.