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

Partie 1 : Schéma relationnel

ARTISTES                          ALBUMS
+----+---------+                  +----+--------+-----------+
| id | nom     |                  | id | titre  | id_artiste|
| PK | ...     |<─────────────────| PK | ...    | FK        |
+----+---------+                  +----+--------+-----------+
                                          │
                                          │
                                          ▼
                                  TITRES
                                  +----+------+----------+
                                  | id | nom  | id_album |
                                  | PK | ...  | FK       |
                                  +----+------+----------+
                                          │
                                          │
UTILISATEURS                              │
+----+--------+                           │
| id | pseudo |                           │
| PK | ...    |                           │
+----+--------+                           │
      │                                   │
      │         ECOUTES                   │
      │         +----+--------------+-----+
      └────────>| id | id_utilisateur| id_titre |
                | PK | FK           | FK       |
                +----+--------------+----------+

Partie 2 : Requêtes de base

1. Tous les artistes

SELECT * FROM ARTISTES;

2. Artistes français

SELECT nom, pays FROM ARTISTES WHERE pays = 'France';

Résultat : Daft Punk, PNL, Aya Nakamura

3. Albums sortis après 2015

SELECT titre FROM ALBUMS WHERE ann_sortie > 2015;

4. Utilisateurs premium

SELECT pseudo FROM UTILISATEURS WHERE type_abo = 'premium';

Résultat : music_lover_33, kevin_hiphop, thomas_electro, jp_kpop

5. Titres de plus de 4 minutes

SELECT nom, duree_sec FROM TITRES WHERE duree_sec > 240;

6. Artistes Hip-Hop ou Pop

SELECT * FROM ARTISTES WHERE genre_principal IN ('Hip-Hop', 'Pop');
-- ou
SELECT * FROM ARTISTES WHERE genre_principal = 'Hip-Hop' OR genre_principal = 'Pop';

Partie 3 : Requêtes avec jointures

7. Titres avec nom d'album

SELECT T.nom AS titre, A.titre AS album
FROM TITRES T
INNER JOIN ALBUMS A ON T.id_album = A.id;

8. Titres avec nom d'artiste

SELECT T.nom AS titre, AR.nom AS artiste
FROM TITRES T
INNER JOIN ALBUMS AL ON T.id_album = AL.id
INNER JOIN ARTISTES AR ON AL.id_artiste = AR.id;

9. Albums de Daft Punk

SELECT AL.titre, AL.ann_sortie
FROM ALBUMS AL
INNER JOIN ARTISTES AR ON AL.id_artiste = AR.id
WHERE AR.nom = 'Daft Punk';

Résultat : Random Access Memories (2013), Discovery (2001)

10. Écoutes avec pseudo et titre

SELECT U.pseudo, T.nom AS titre, E.date_ecoute
FROM ECOUTES E
INNER JOIN UTILISATEURS U ON E.id_utilisateur = U.id
INNER JOIN TITRES T ON E.id_titre = T.id;

11. Titres des artistes français

SELECT T.nom AS titre, AR.nom AS artiste
FROM TITRES T
INNER JOIN ALBUMS AL ON T.id_album = AL.id
INNER JOIN ARTISTES AR ON AL.id_artiste = AR.id
WHERE AR.pays = 'France';

12. Écoutes complètes

SELECT U.pseudo, T.nom AS titre, AL.titre AS album, AR.nom AS artiste
FROM ECOUTES E
INNER JOIN UTILISATEURS U ON E.id_utilisateur = U.id
INNER JOIN TITRES T ON E.id_titre = T.id
INNER JOIN ALBUMS AL ON T.id_album = AL.id
INNER JOIN ARTISTES AR ON AL.id_artiste = AR.id;

Partie 4 : Agrégations

13. Nombre de titres

SELECT COUNT(*) AS nb_titres FROM TITRES;

Résultat : 43

14. Durée moyenne des titres

SELECT AVG(duree_sec) AS duree_moyenne FROM TITRES;

Résultat : environ 227 secondes

15. Titre le plus long et le plus court

-- Le plus long
SELECT nom, duree_sec FROM TITRES WHERE duree_sec = (SELECT MAX(duree_sec) FROM TITRES);
-- Résultat : Get Lucky (369 sec)

-- Le plus court
SELECT nom, duree_sec FROM TITRES WHERE duree_sec = (SELECT MIN(duree_sec) FROM TITRES);
-- Résultat : SMS (164 sec)

16. Nombre d'albums par artiste

SELECT AR.nom, COUNT(*) AS nb_albums
FROM ARTISTES AR
INNER JOIN ALBUMS AL ON AR.id = AL.id_artiste
GROUP BY AR.id
ORDER BY nb_albums DESC;

17. Nombre d'écoutes par utilisateur

SELECT U.pseudo, COUNT(*) AS nb_ecoutes
FROM UTILISATEURS U
INNER JOIN ECOUTES E ON U.id = E.id_utilisateur
GROUP BY U.id
ORDER BY nb_ecoutes DESC;

18. Nombre de titres par genre

SELECT AR.genre_principal, COUNT(*) AS nb_titres
FROM ARTISTES AR
INNER JOIN ALBUMS AL ON AR.id = AL.id_artiste
INNER JOIN TITRES T ON AL.id = T.id_album
GROUP BY AR.genre_principal
ORDER BY nb_titres DESC;

Partie 5 : Requêtes avancées

19. Artistes avec plus d'un album

SELECT AR.nom, COUNT(*) AS nb_albums
FROM ARTISTES AR
INNER JOIN ALBUMS AL ON AR.id = AL.id_artiste
GROUP BY AR.id
HAVING COUNT(*) > 1;

20. Top 5 des titres les plus écoutés

SELECT T.nom, COUNT(*) AS nb_ecoutes
FROM TITRES T
INNER JOIN ECOUTES E ON T.id = E.id_titre
GROUP BY T.id
ORDER BY nb_ecoutes DESC
LIMIT 5;

21. Utilisateurs ayant écouté Stromae

SELECT DISTINCT U.pseudo
FROM UTILISATEURS U
INNER JOIN ECOUTES E ON U.id = E.id_utilisateur
INNER JOIN TITRES T ON E.id_titre = T.id
INNER JOIN ALBUMS AL ON T.id_album = AL.id
INNER JOIN ARTISTES AR ON AL.id_artiste = AR.id
WHERE AR.nom = 'Stromae';

22. Durée totale d'écoute par utilisateur (en minutes)

SELECT U.pseudo, SUM(E.duree_ecoute_sec) / 60.0 AS duree_totale_min
FROM UTILISATEURS U
INNER JOIN ECOUTES E ON U.id = E.id_utilisateur
GROUP BY U.id
ORDER BY duree_totale_min DESC;

23. Artistes sans titres explicites

SELECT DISTINCT AR.nom
FROM ARTISTES AR
WHERE AR.id NOT IN (
    SELECT DISTINCT AL.id_artiste
    FROM ALBUMS AL
    INNER JOIN TITRES T ON AL.id = T.id_album
    WHERE T.explicite = TRUE
);

24. Écoutes par pays d'utilisateur

SELECT U.pays, COUNT(*) AS nb_ecoutes, SUM(E.duree_ecoute_sec) / 60.0 AS duree_totale_min
FROM UTILISATEURS U
INNER JOIN ECOUTES E ON U.id = E.id_utilisateur
GROUP BY U.pays
ORDER BY nb_ecoutes DESC;

Partie 6 : Analyse business

25. Taux de conversion

SELECT type_abo, COUNT(*) AS nb_utilisateurs
FROM UTILISATEURS
GROUP BY type_abo;

-- Ou en pourcentage
SELECT
    type_abo,
    COUNT(*) AS nb,
    ROUND(COUNT(*) * 100.0 / (SELECT COUNT(*) FROM UTILISATEURS), 1) AS pourcentage
FROM UTILISATEURS
GROUP BY type_abo;

26. Artistes populaires auprès des Français

SELECT AR.nom, COUNT(*) AS nb_ecoutes
FROM ARTISTES AR
INNER JOIN ALBUMS AL ON AR.id = AL.id_artiste
INNER JOIN TITRES T ON AL.id = T.id_album
INNER JOIN ECOUTES E ON T.id = E.id_titre
INNER JOIN UTILISATEURS U ON E.id_utilisateur = U.id
WHERE U.pays = 'France'
GROUP BY AR.id
ORDER BY nb_ecoutes DESC;

27. Utilisateurs Daft Punk mais pas Stromae

SELECT DISTINCT U.pseudo
FROM UTILISATEURS U
INNER JOIN ECOUTES E ON U.id = E.id_utilisateur
INNER JOIN TITRES T ON E.id_titre = T.id
INNER JOIN ALBUMS AL ON T.id_album = AL.id
INNER JOIN ARTISTES AR ON AL.id_artiste = AR.id
WHERE AR.nom = 'Daft Punk'
AND U.id NOT IN (
    SELECT DISTINCT U2.id
    FROM UTILISATEURS U2
    INNER JOIN ECOUTES E2 ON U2.id = E2.id_utilisateur
    INNER JOIN TITRES T2 ON E2.id_titre = T2.id
    INNER JOIN ALBUMS AL2 ON T2.id_album = AL2.id
    INNER JOIN ARTISTES AR2 ON AL2.id_artiste = AR2.id
    WHERE AR2.nom = 'Stromae'
);

28. Pourcentage de contenu explicite

SELECT
    ROUND(SUM(CASE WHEN explicite = TRUE THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS pct_explicite
FROM TITRES;

29. Utilisateurs anciens actifs

SELECT DISTINCT U.pseudo, U.date_inscription
FROM UTILISATEURS U
INNER JOIN ECOUTES E ON U.id = E.id_utilisateur
WHERE U.date_inscription < '2021-01-01'
AND E.date_ecoute BETWEEN '2024-01-01' AND '2024-01-31';

30. Vue synthétique par artiste

SELECT
    AR.nom AS artiste,
    AR.pays,
    AR.genre_principal AS genre,
    COUNT(DISTINCT AL.id) AS nb_albums,
    COUNT(DISTINCT T.id) AS nb_titres,
    COUNT(E.id) AS nb_ecoutes
FROM ARTISTES AR
LEFT JOIN ALBUMS AL ON AR.id = AL.id_artiste
LEFT JOIN TITRES T ON AL.id = T.id_album
LEFT JOIN ECOUTES E ON T.id = E.id_titre
GROUP BY AR.id
ORDER BY nb_ecoutes DESC;

Partie 7 : Modifications de données

31. Ajouter Angèle

INSERT INTO ARTISTES (id, nom, pays, genre_principal, ann_debut)
VALUES (11, 'Angèle', 'Belgique', 'Pop', 2014);

32. Ajouter l'album Brol

INSERT INTO ALBUMS (id, titre, id_artiste, ann_sortie, nb_titres)
VALUES (19, 'Brol', 11, 2018, 15);

33. Changer l'abonnement d'alex_beats

UPDATE UTILISATEURS
SET type_abo = 'etudiant'
WHERE pseudo = 'alex_beats';

34. Supprimer les écoutes de jp_kpop

DELETE FROM ECOUTES
WHERE id_utilisateur = (SELECT id FROM UTILISATEURS WHERE pseudo = 'jp_kpop');

Questions de réflexion

1. Pourquoi séparer artistes et albums ? - Éviter la redondance : les infos de l'artiste ne sont stockées qu'une fois - Faciliter les mises à jour : changer le pays d'un artiste se fait en un seul endroit - Garantir la cohérence : pas de risque d'avoir "Daft Punk" et "DaftPunk" - C'est le principe de normalisation des bases de données

2. Problème de suppression d'un artiste - Violation de contrainte d'intégrité référentielle : les albums font référence à cet artiste via la clé étrangère - Solutions possibles : - Empêcher la suppression (comportement par défaut) - Supprimer en cascade tous les albums et titres associés (ON DELETE CASCADE) - Mettre la référence à NULL (ON DELETE SET NULL)

3. Gérer les collaborations - Créer une table de liaison COLLABORATIONS :

CREATE TABLE COLLABORATIONS (
    id_titre INTEGER,
    id_artiste INTEGER,
    role TEXT, -- 'principal', 'featuring', 'producteur'
    PRIMARY KEY (id_titre, id_artiste)
);

- Ou modifier la table TITRES pour permettre plusieurs artistes

4. Informations supplémentaires pour un vrai service - Playlists : table PLAYLISTS avec les titres associés - Historique complet : horodatage précis des écoutes - Préférences : genres favoris, artistes suivis - Social : amis, partages, playlists collaboratives - Paiements : historique des abonnements, factures - Métadonnées audio : tempo, tonalité, énergie (pour les recommandations) - Paroles : table LYRICS liée aux titres - Podcasts : extension du modèle pour d'autres types de contenu

Auteur : Florian Mathieu

Licence CC BY NC

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.