Aller au contenu

TP : Base de données d'un service de streaming musical

Contexte

Vous êtes data analyst chez Musicfy, un service de streaming musical concurrent de Spotify et Deezer. Votre mission est d'analyser les données d'écoute pour comprendre les habitudes des utilisateurs et proposer des améliorations.

Vous disposez d'une base de données contenant les informations sur les artistes, albums, titres, utilisateurs et leurs écoutes.

Objectifs

  • Pratiquer les requêtes SQL sur une base de données réaliste
  • Maîtriser les jointures entre plusieurs tables
  • Utiliser les fonctions d'agrégation pour l'analyse de données
  • Comprendre l'organisation d'une base de données relationnelle

Partie 1 : Découverte de la base de données

1.1. Création de la base

  1. Ouvrez DB Browser for SQLite et créez une nouvelle base de données musicfy.db.

  2. Exécutez les requêtes suivantes pour créer les tables :

-- Table des artistes
CREATE TABLE ARTISTES (
    id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    pays TEXT,
    genre_principal TEXT,
    ann_debut INTEGER
);

-- Table des albums
CREATE TABLE ALBUMS (
    id INTEGER PRIMARY KEY,
    titre TEXT NOT NULL,
    id_artiste INTEGER,
    ann_sortie INTEGER,
    nb_titres INTEGER,
    FOREIGN KEY (id_artiste) REFERENCES ARTISTES(id)
);

-- Table des titres (chansons)
CREATE TABLE TITRES (
    id INTEGER PRIMARY KEY,
    nom TEXT NOT NULL,
    id_album INTEGER,
    duree_sec INTEGER,
    explicite BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (id_album) REFERENCES ALBUMS(id)
);

-- Table des utilisateurs
CREATE TABLE UTILISATEURS (
    id INTEGER PRIMARY KEY,
    pseudo TEXT NOT NULL,
    email TEXT UNIQUE,
    pays TEXT,
    type_abo TEXT DEFAULT 'gratuit',
    date_inscription DATE
);

-- Table des écoutes
CREATE TABLE ECOUTES (
    id INTEGER PRIMARY KEY,
    id_utilisateur INTEGER,
    id_titre INTEGER,
    date_ecoute DATE,
    duree_ecoute_sec INTEGER,
    FOREIGN KEY (id_utilisateur) REFERENCES UTILISATEURS(id),
    FOREIGN KEY (id_titre) REFERENCES TITRES(id)
);

1.2. Insertion des données

Exécutez les requêtes suivantes pour insérer les données :

-- Artistes
INSERT INTO ARTISTES (id, nom, pays, genre_principal, ann_debut) VALUES
(1, 'Daft Punk', 'France', 'Electro', 1993),
(2, 'Kendrick Lamar', 'USA', 'Hip-Hop', 2004),
(3, 'Adele', 'Royaume-Uni', 'Pop', 2006),
(4, 'BTS', 'Corée du Sud', 'K-Pop', 2013),
(5, 'Stromae', 'Belgique', 'Electro', 2009),
(6, 'Taylor Swift', 'USA', 'Pop', 2004),
(7, 'PNL', 'France', 'Hip-Hop', 2014),
(8, 'Beyoncé', 'USA', 'R&B', 1997),
(9, 'Ed Sheeran', 'Royaume-Uni', 'Pop', 2004),
(10, 'Aya Nakamura', 'France', 'Pop', 2014);

-- Albums
INSERT INTO ALBUMS (id, titre, id_artiste, ann_sortie, nb_titres) VALUES
(1, 'Random Access Memories', 1, 2013, 13),
(2, 'Discovery', 1, 2001, 14),
(3, 'DAMN.', 2, 2017, 14),
(4, 'To Pimp a Butterfly', 2, 2015, 16),
(5, '21', 3, 2011, 11),
(6, '30', 3, 2021, 12),
(7, 'Map of the Soul: 7', 4, 2020, 20),
(8, 'Racine carrée', 5, 2013, 13),
(9, 'Multitude', 5, 2022, 12),
(10, '1989', 6, 2014, 13),
(11, 'Midnights', 6, 2022, 13),
(12, 'Dans la légende', 7, 2016, 17),
(13, 'Deux frères', 7, 2019, 18),
(14, 'Lemonade', 8, 2016, 12),
(15, 'Renaissance', 8, 2022, 16),
(16, 'Divide', 9, 2017, 16),
(17, 'Nakamura', 10, 2018, 14),
(18, 'DNK', 10, 2023, 15);

-- Titres (sélection)
INSERT INTO TITRES (id, nom, id_album, duree_sec, explicite) VALUES
(1, 'Get Lucky', 1, 369, FALSE),
(2, 'Instant Crush', 1, 337, FALSE),
(3, 'Lose Yourself to Dance', 1, 353, FALSE),
(4, 'One More Time', 2, 320, FALSE),
(5, 'Digital Love', 2, 301, FALSE),
(6, 'Harder Better Faster Stronger', 2, 224, FALSE),
(7, 'HUMBLE.', 3, 177, TRUE),
(8, 'DNA.', 3, 185, TRUE),
(9, 'LOYALTY.', 3, 227, TRUE),
(10, 'Alright', 4, 219, TRUE),
(11, 'King Kunta', 4, 234, TRUE),
(12, 'Rolling in the Deep', 5, 228, FALSE),
(13, 'Someone Like You', 5, 285, FALSE),
(14, 'Set Fire to the Rain', 5, 241, FALSE),
(15, 'Easy On Me', 6, 224, FALSE),
(16, 'Oh My My', 6, 218, FALSE),
(17, 'Dynamite', 7, 199, FALSE),
(18, 'ON', 7, 242, FALSE),
(19, 'Papaoutai', 8, 234, FALSE),
(20, 'Formidable', 8, 192, FALSE),
(21, 'Alors on danse', 8, 205, FALSE),
(22, 'Santé', 9, 188, FALSE),
(23, 'L''enfer', 9, 224, FALSE),
(24, 'Shake It Off', 10, 219, FALSE),
(25, 'Blank Space', 10, 231, FALSE),
(26, 'Style', 10, 231, FALSE),
(27, 'Anti-Hero', 11, 201, FALSE),
(28, 'Lavender Haze', 11, 202, FALSE),
(29, 'Le monde ou rien', 12, 228, TRUE),
(30, 'DA', 12, 195, TRUE),
(31, 'Au DD', 13, 199, TRUE),
(32, 'A l''ammoniaque', 13, 234, TRUE),
(33, 'Formation', 14, 226, TRUE),
(34, 'Sorry', 14, 232, FALSE),
(35, 'BREAK MY SOUL', 15, 279, FALSE),
(36, 'CUFF IT', 15, 225, FALSE),
(37, 'Shape of You', 16, 233, FALSE),
(38, 'Castle on the Hill', 16, 261, FALSE),
(39, 'Perfect', 16, 263, FALSE),
(40, 'Djadja', 17, 176, FALSE),
(41, 'Pookie', 17, 185, TRUE),
(42, 'Copines', 18, 179, FALSE),
(43, 'SMS', 18, 164, FALSE);

-- Utilisateurs
INSERT INTO UTILISATEURS (id, pseudo, email, pays, type_abo, date_inscription) VALUES
(1, 'music_lover_33', 'music33@email.com', 'France', 'premium', '2020-03-15'),
(2, 'alex_beats', 'alex.b@email.com', 'France', 'gratuit', '2021-06-22'),
(3, 'sophie_melody', 'sophie.m@email.com', 'Belgique', 'premium', '2019-11-08'),
(4, 'dj_max', 'maxime.dj@email.com', 'France', 'famille', '2022-01-10'),
(5, 'luna_music', 'luna.music@email.com', 'Canada', 'etudiant', '2021-09-01'),
(6, 'kevin_hiphop', 'kevin.hh@email.com', 'USA', 'premium', '2020-07-19'),
(7, 'emma_pop', 'emma.pop@email.com', 'Royaume-Uni', 'gratuit', '2023-02-28'),
(8, 'thomas_electro', 'thomas.e@email.com', 'France', 'premium', '2018-05-12'),
(9, 'clara_world', 'clara.w@email.com', 'Espagne', 'etudiant', '2022-10-05'),
(10, 'jp_kpop', 'jp.kpop@email.com', 'Japon', 'premium', '2021-03-20');

-- Écoutes (échantillon)
INSERT INTO ECOUTES (id, id_utilisateur, id_titre, date_ecoute, duree_ecoute_sec) VALUES
(1, 1, 1, '2024-01-15', 369),
(2, 1, 4, '2024-01-15', 320),
(3, 1, 19, '2024-01-15', 234),
(4, 2, 7, '2024-01-15', 177),
(5, 2, 8, '2024-01-15', 150),
(6, 2, 29, '2024-01-15', 228),
(7, 3, 19, '2024-01-16', 234),
(8, 3, 20, '2024-01-16', 192),
(9, 3, 22, '2024-01-16', 188),
(10, 4, 1, '2024-01-16', 369),
(11, 4, 6, '2024-01-16', 224),
(12, 5, 17, '2024-01-16', 199),
(13, 5, 18, '2024-01-16', 242),
(14, 6, 7, '2024-01-17', 177),
(15, 6, 10, '2024-01-17', 219),
(16, 6, 11, '2024-01-17', 234),
(17, 7, 12, '2024-01-17', 228),
(18, 7, 24, '2024-01-17', 219),
(19, 7, 37, '2024-01-17', 233),
(20, 8, 1, '2024-01-17', 369),
(21, 8, 4, '2024-01-17', 320),
(22, 8, 5, '2024-01-17', 301),
(23, 8, 6, '2024-01-17', 224),
(24, 9, 40, '2024-01-18', 176),
(25, 9, 42, '2024-01-18', 179),
(26, 10, 17, '2024-01-18', 199),
(27, 10, 18, '2024-01-18', 242),
(28, 1, 19, '2024-01-18', 234),
(29, 1, 21, '2024-01-18', 205),
(30, 2, 31, '2024-01-18', 199),
(31, 3, 23, '2024-01-19', 224),
(32, 4, 35, '2024-01-19', 279),
(33, 5, 27, '2024-01-19', 201),
(34, 6, 8, '2024-01-19', 185),
(35, 7, 39, '2024-01-19', 263),
(36, 8, 2, '2024-01-19', 337),
(37, 9, 41, '2024-01-20', 185),
(38, 10, 17, '2024-01-20', 199),
(39, 1, 1, '2024-01-20', 369),
(40, 2, 7, '2024-01-20', 177);

1.3. Schéma de la base

Dessinez le schéma relationnel de la base de données en identifiant : - Les clés primaires (PK) - Les clés étrangères (FK) - Les relations entre les tables

Partie 2 : Requêtes de base

Pour chaque question, écrivez la requête SQL et notez le résultat.

  1. Affichez tous les artistes de la base de données.

  2. Affichez le nom et le pays des artistes français.

  3. Affichez les titres de tous les albums sortis après 2015.

  4. Affichez les pseudos des utilisateurs ayant un abonnement premium.

  5. Affichez les titres (chansons) qui durent plus de 4 minutes (240 secondes).

  6. Affichez les artistes dont le genre principal est "Hip-Hop" ou "Pop".

Partie 3 : Requêtes avec jointures

  1. Affichez le nom des titres avec le nom de leur album.

  2. Affichez le nom des titres avec le nom de l'artiste qui les a créés. (Indice : vous devez passer par la table ALBUMS)

  3. Affichez les albums de Daft Punk avec leur année de sortie.

  4. Affichez la liste des écoutes avec le pseudo de l'utilisateur et le nom du titre écouté.

  5. Affichez tous les titres des artistes français.

  6. Pour chaque écoute, affichez le pseudo de l'utilisateur, le nom du titre, le nom de l'album et le nom de l'artiste.

Partie 4 : Agrégations

  1. Combien y a-t-il de titres dans la base de données ?

  2. Quelle est la durée moyenne des titres (en secondes) ?

  3. Quel est le titre le plus long ? Le plus court ?

  4. Combien d'albums a sorti chaque artiste ? Affichez le nom de l'artiste et le nombre d'albums.

  5. Combien d'écoutes ont été effectuées par chaque utilisateur ? Affichez le pseudo et le nombre d'écoutes.

  6. Quel est le nombre total de titres par genre musical ? (Indice : il faut passer par ARTISTES et ALBUMS)

Partie 5 : Requêtes avancées

  1. Quels sont les artistes ayant sorti plus d'un album ?

  2. Quels sont les 5 titres les plus écoutés ? Affichez le nom du titre et le nombre d'écoutes.

  3. Quels utilisateurs ont écouté des titres de Stromae ?

  4. Quelle est la durée totale d'écoute (en minutes) par utilisateur ?

  5. Quels artistes n'ont aucun titre explicite dans leurs chansons ?

  6. Pour chaque pays d'utilisateur, calculez le nombre d'écoutes et la durée totale d'écoute.

Partie 6 : Analyse business

Le département marketing vous demande des analyses pour améliorer le service.

  1. Taux de conversion : Combien d'utilisateurs ont un abonnement payant (premium, famille ou étudiant) vs gratuit ?

  2. Artistes populaires en France : Quels artistes sont les plus écoutés par les utilisateurs français ?

  3. Recommandations : Trouvez les utilisateurs qui ont écouté Daft Punk mais pas Stromae (ces utilisateurs pourraient aimer Stromae).

  4. Contenu explicite : Quel pourcentage des titres est marqué comme explicite ?

  5. Rétention : Quels utilisateurs inscrits avant 2021 ont écouté de la musique en janvier 2024 ?

  6. Rapport final : Créez une vue synthétique montrant pour chaque artiste : son nom, son pays, son genre, le nombre d'albums, le nombre de titres et le nombre total d'écoutes.

Partie 7 : Modifications de données (Bonus)

  1. Ajoutez l'artiste "Angèle" (Belgique, Pop, 2014).

  2. Ajoutez son album "Brol" sorti en 2018 avec 15 titres.

  3. Un utilisateur change d'abonnement : mettez à jour alex_beats vers l'abonnement "etudiant".

  4. Supprimez toutes les écoutes de l'utilisateur jp_kpop.

Questions de réflexion

  1. Pourquoi a-t-on séparé les artistes et les albums dans des tables différentes au lieu de tout mettre dans une seule table ?

  2. Quel problème pourrait survenir si on supprimait un artiste qui a des albums dans la base ?

  3. Comment pourrait-on améliorer la base pour gérer les collaborations entre artistes sur un même titre ?

  4. Quelles informations supplémentaires pourrait-on ajouter pour un vrai service de streaming ?

Barème indicatif

Partie Points
Partie 1 : Création et schéma 2
Partie 2 : Requêtes de base 3
Partie 3 : Jointures 4
Partie 4 : Agrégations 4
Partie 5 : Requêtes avancées 4
Partie 6 : Analyse business 3
Total 20

Auteur : Florian Mathieu

Licence CC BY NC

Licence Creative Commons
Ce cours est mis à disposition selon les termes de la Licence Creative Commons Attribution - Pas d'Utilisation Commerciale - Partage dans les Mêmes Conditions 4.0 International.