createStarSchema.PractII.VarmaA.R - Complete Deep Dive

🎯 File Purpose

This file creates the empty database structure (schema) for our analytics data warehouse. Think of it as building the skeleton of a house before adding furniture - we create the rooms (tables) before filling them with data.

When to run: ONCE, before running the ETL file (loadAnalyticsDB.R)

What it creates:

3 Dimension Tables (dim_date, dim_country, dim_sport)
1 Fact Table (fact_streaming_summary) with partitioning
Multiple indexes for query optimization

📝 Line-by-Line Code Explanation

Section 1: Header & Environment Setup (Lines 1-17)

# ==============================================================================
# Program: createStarSchema.PractII.VarmaA.R
# Author: Ayush Varma
# Semester: Fall 2025
# Purpose: Create star schema for SportsTV streaming analytics datamart
# ==============================================================================

rm(list = ls())

# Install and load required packages
required_packages <- c("DBI", "RMySQL")
for (pkg in required_packages) {
  if (!require(pkg, character.only = TRUE, quietly = TRUE)) {
    install.packages(pkg)
    library(pkg, character.only = TRUE)
  }
}

What's happening:

Line	Code	Purpose
1-6	Header comments	Professional documentation - identifies author, purpose, semester
8	`rm(list = ls())`	Clears R environment - ensures fresh start, no leftover variables
11	`required_packages <- c("DBI", "RMySQL")`	Lists packages needed: DBI (database interface), RMySQL (MySQL driver)
12-16	Package installation loop	Smart loading - only installs if not already installed

Why this pattern for packages?

require() returns FALSE if package isn't installed
The if (!require(...)) pattern auto-installs missing packages
Makes code portable - works on any machine

Section 2: Database Connection (Lines 19-38)

# ------------------------------------------------------------------------------
# 1. DATABASE CONNECTION
# ------------------------------------------------------------------------------
cat("========================================\\n")
cat("SPORTSTV ANALYTICS - STAR SCHEMA SETUP\\n")
cat("========================================\\n\\n")

get_mysql_connection <- function() {
  dbConnect(RMySQL::MySQL(),
            host = "mysql-a965b4-northeastern-9ffb.f.aivencloud.com",
            port = 15435,
            dbname = "defaultdb",
            user = "avnadmin",
            password = "AVNS_IGPzE2ooZt4AQ09DAr_")
}

cat("Connecting to Aiven MySQL database...\\n")
mysql_conn <- get_mysql_connection()
cat("Successfully connected to MySQL\\n\\n")