How rishabhnmishra/sql_music_store_analysis works
Project type and purpose
• This is a data-analysis/SQL training project, not a full-stack application.
• Purpose: load, query, and report on an online music-store dataset using PostgreSQL and pgAdmin4.
Main components
a. Data Sources
• album2.csv, music store data.zip (raw CSV playlists, albums, tracks, customers, invoices)
• Music_Store_database.sql (DDL + data-load scripts)
b. Database Engine
• PostgreSQL instance (hosted locally or on a server)
• Schema: tables for album, artist, customer, invoice, invoice_line, playlist, track
c. Analysis Layer
• SQL query scripts (Music_Store_Query.sql)
• pgAdmin4 client
d. Outputs
• Query results (tables, charts, CSV exports)
• PDF report (Music Store Analysis-Questions.pdf)
• Video tutorial link
Relationships and data flow
• Raw CSV files → (via Music_Store_database.sql) → PostgreSQL
• pgAdmin4 GUI → connects to PostgreSQL → runs Music_Store_Query.sql
• PostgreSQL executes queries → returns result sets
• Results → exported/reported in PDF or CSV → used for business insights
Architectural patterns and principles
• Simple ETL pipeline: Extract (CSV), Transform/Load (SQL scripts → DB)
• Analytical query layer separated from data ingestion layer
• Single relational database as the system’s core
Technologies/frameworks
• PostgreSQL (database engine)
• pgAdmin4 (SQL client)
• SQL scripts (schema + data load + analysis queries)
Essential details to capture
• Exact file names (Music_Store_database.sql vs. Music_Store_Query.sql)
• Tool connection (pgAdmin4 ↔ PostgreSQL)