2022
Music Streaming Service Analysis
The Music Streaming Service Database Project aimed to develop a scalable and efficient database model to support a music streaming platform, facilitating comprehensive data management and insightful analysis.
Phase 1: Context and Requirements Gathering
Music streaming services offer flexible listening across devices, requiring a scalable database to manage relationships between users, artists, albums, songs, playlists, and subscriptions for seamless, personalized experiences.
Phase 2: Database Design
An entity-relationship diagram (ERD) was created to visualize relationships between entities. Key tables included ALBUM, ARTIST, and SONG (connected via COMPOSITION), and PLAYLIST and USER (connected via PLAYLIST_SONG and USER_PLAYLIST). The SUBSCRIPTION table linked PLAN, PAYMENT_CHANNEL, and USER. This phase involved defining table structures and ensuring referential integrity.
Phase 3: Database Implementation
The database schema was translated into tables, and sample data was inserted to test relationships and performance, ensuring efficient data handling.
Phase 4: Query Development
Complex SQL queries were developed to extract insights based on business rules, such as retrieving songs by an artist, finding playlists with a specific song, and listing users with active subscriptions.
Phase 5: Data Visualization
Power BI was used to create visualizations, developing dashboards and reports to provide actionable insights, such as user subscription trends and popular songs.
Conclusion
The efficient and scalable database model included SQL queries based on business rules. This project ensured effective data management, insightful analysis, and enhanced visualization, leading to improved user engagement, retention, and data-driven strategies, supporting platform growth and success.