PMG2426 1/DB Beleg/Mariella

Wiki-Quelltext dieser Seite.

<language>SQL</language>
= Musikdienst Spotify =

== Ziel der Datenbank ==
 Spotify ist ein digitaler Streamingdienst für Musik, Podcasts und Videos, mit dem du Zugriff auf Millionen Songs und andere Inhalte von kreativen Köpfen aus der ganzen Welt hast.
 Der Sinn dieser Datenbank ist die strukturierte Speicherung, Verwaltung und Auswertung von Nutzern, Songs und Playlists, um Musikinhalte effizient zu gestalten und die personalisierte Nutzung zu ermöglichen.'''

== Modellierung in einem ER-Modell ==

[[Bild:Mariella_Screenshot_2026-01-21_103301.png]]

== Transformation in das Relationenmodell == 
* Song ('''Song_ID''', Artist, Titel, Länge, Album)
* Nutzer ('''Nutzer_ID''', Nutzername, Follower, Premium)
* Playlist ('''Playlist_ID''', Erstellungsdatum, Name) 
* zugehören -> Playlistinhalt (Song_ID, Playlist_ID, Position)
* hören -> Hörerverlauf (Nutzer_ID, Song_ID, Datum, Uhrzeit, Gerät)

<code id="6979cae80f5ef">

CREATE TABLE Song (
Song_ID INT PRIMARY KEY,
Artist VARCHAR(100) NOT NULL,
Titel VARCHAR(150) NOT NULL,
Länge INT, 
Album VARCHAR(150)
);

CREATE TABLE Nutzer (
Nutzer_ID INT PRIMARY KEY,
Nutzername VARCHAR(50) NOT NULL UNIQUE,
Follower INT DEFAULT 0,
Premium BOOLEAN DEFAULT 0
);

CREATE TABLE Playlist (
Playlist_ID INT PRIMARY KEY,
Erstellungsdatum DATE,
Name VARCHAR(100) NOT NULL
);

CREATE TABLE Playlistinhalt (
Song_ID INT,
Playlist_ID INT,
Position INT, 
PRIMARY KEY (Song_ID, Playlist_ID),
FOREIGN KEY (Song_ID) REFERENCES Song(Song_ID),
FOREIGN KEY (Playlist_ID) REFERENCES Playlist(Playlist_ID)
);

CREATE TABLE Hörerverlauf (
Nutzer_ID INT,
Song_ID INT,
Datum DATE,
Uhrzeit TIME,
Gerät VARCHAR(50),
PRIMARY KEY (Nutzer_ID, Song_ID, Datum, Uhrzeit),
FOREIGN KEY (Nutzer_ID) REFERENCES Nutzer(Nutzer_ID),
FOREIGN KEY (Song_ID) REFERENCES Song(Song_ID)
);

INSERT INTO Song VALUES
(1, 'Taylor Swift', 'Anti-Hero', 200, 'Midnights'),
(2, 'The Weeknd', 'Blinding Lights', 203, 'After Hours'),
(3, 'Ed Sheeran', 'Shape of You', 234, 'Divide'),
(4, 'Dua Lipa', 'Levitating', 203, 'Future Nostalgia'),
(5, 'Harry Styles', 'As It Was', 167, 'Harrys House'),
(6, 'Bad Bunny', 'Titi Me Pregunto', 243, 'Un Verano Sin Ti'),
(7, 'Adele', 'Easy On Me', 224, '30'),
(8, 'Drake', 'One Dance', 173, 'Views'),
(9, 'BTS', 'Dynamite', 199, 'BE'),
(10, 'Billie Eilish', 'Bad Guy', 194, 'WWAFAWDWG');

INSERT INTO Nutzer VALUES
(1, 'MusicLover2026', 1250, 1),
(2, 'ChillVibesOnly', 89, 0),
(3, 'PartyQueen', 5420, 1),
(4, 'IndieFan_Max', 312, 1),
(5, 'RandomListener', 15, 0);

INSERT INTO Playlist VALUES
(1, '2024-01-15', 'Workout Motivation '),
(2, '2024-02-20', 'Chill Vibes für Sonntage'),
(3, '2020-01-01', 'Party Hits 2020 '),
(4, '2023-12-24', 'Roadtrip Classics'),
(5, '2019-04-10', 'Focus & Study Mode');

INSERT INTO Playlistinhalt VALUES
(2, 1, 1),
(4, 1, 2),
(9, 1, 3),
(7, 2, 1),
(5, 2, 2),
(1, 2, 3),
(4, 3, 1),
(6, 3, 2),
(8, 3, 3),
(10, 3, 4),
(3, 4, 1),
(2, 4, 2),
(8, 4, 3);

INSERT INTO Hörerverlauf VALUES
(1, 2, '2024-04-15', '08:30:00', 'iPhone'),
(1, 4, '2024-04-15', '08:34:00', 'iPhone'),
(1, 9, '2024-04-15', '08:38:00', 'iPhone'),
(2, 7, '2024-04-15', '20:15:00', 'Desktop'),
(2, 5, '2024-04-15', '20:19:00', 'Desktop'),
(3, 6, '2024-04-14', '23:00:00', 'Smart Speaker'),
(3, 4, '2024-04-14', '23:04:00', 'Smart Speaker'),
(4, 1, '2024-04-15', '14:00:00', 'Android'),
(5, 3, '2024-04-15', '12:00:00', 'Web Player');





</code>
Alle Songs, die die Playlist Chill Vibes für Sonntage beinhält
<run id="698219001a7c7">

SELECT Titel, Artist 
FROM Song 
WHERE Song_ID IN (
    SELECT Song_ID 
    FROM Playlistinhalt 
    WHERE Playlist_ID = 2
);

</run>
Die Länge der Playlist Workout Motivation (in Sekunden) ermitteln => So lang sollte das Workout gehen ;)
<run id="6979cae80f66f">

 

SELECT 
    (SELECT Name FROM Playlist WHERE Playlist_ID = 1) as Playlist_Name,
    SUM(Länge) as Gesamtdauer_Sekunden
FROM Song 
WHERE Song_ID IN (
    SELECT Song_ID 
    FROM Playlistinhalt 
    WHERE Playlist_ID = 1
);

</run>
Der meistgehörte Song (wird wichtig für Spotify-Awards)
<run id="69821acff3267">
SELECT Titel, Artist 
FROM Song 
WHERE Song_ID = (
    SELECT Song_ID 
    FROM Hörerverlauf 
    GROUP BY Song_ID 
    ORDER BY COUNT(*) DESC 
    LIMIT 1
);

</run>

Die meistgehörten Songs von PartyQueen => Spotify-Wrapped
<run id="69821c1c3ce33">
SELECT Titel, Artist, COUNT(*) as Wiedergaben
FROM Song
WHERE Song_ID IN (
    SELECT Song_ID 
    FROM Hörerverlauf 
    WHERE Nutzer_ID = 3
)
GROUP BY Song_ID
ORDER BY Wiedergaben DESC
LIMIT 3;
</run>

Ausgabe der beliebtesten Geräte zum Abspielen
<run id="698228570697f">
SELECT Gerät, COUNT(*) as Nutzungen
FROM Hörerverlauf
GROUP BY Gerät
ORDER BY Nutzungen DESC;
</run>

==Kritische Reflexion und Vorgehensweise==
Mein Ziel war es, die Funktionsweise der App Spotify möglichst genau nachzustellen. Dabei muss man allerdings sagen, dass Spotify weit aus komplexer ist und über viel mehr Funktionen, Songs bzw. weiteren Inhalten verfügt. Das war in der gegebenen Zeit allerdings nicht umsetzbar. Deshalb ist die gezeigte Datenbank schlichtweg eine Vereinfachung zur Veranschaulichung. Mögliche Erweiterungen könnten u.a. in Tabellen für Artists, Albums oder Genres erscheinen, welche das Modell immer näher an die Realität anpassen würden. 
Bei der Erstellung des gesamten Modells habe ich mir zunächst Gedanken über das ERM und die dazugehörigen Kardinalitäten gemacht. Daraufhin war es für mich wichtig, direkt festzulegen, was die Tabellen darstellen, die bei n:m Kardinalitäten aus Relationen entstehen.  Außerdem habe ich in meiner Spotify-App nach Eigenschaften gesucht, die später Attribute wurden. 
Bei dem Umschreiben in das RM fiel mir auf, dass ich im ERM bei der Entität „Song“ das Attribut „Titel“ vergessen habe. Dieses habe ich beim Fortfahren einfach ergänzt, damit die Abfragen bzw. deren Ausgaben später auch Sinn ergeben. Außerdem habe ich die Attribute des Playlistinhalts noch verändert, da es sinnvoller war, die Dauer später ausrechnen zu lassen und dafür die Position der Songs direkt anzugeben. 
Für das Programmieren habe ich mich an gegebenen Seiten (Learningview-> SQL-Codes, Programmingwiki -> bereits erstellte Tabellen wie Bibliothek) und fertiggestellten Seiten aus vergangenen Jahren orientiert. Für eine kreative Songauswahl bzw. Namensgebung habe ich künstliche Intelligenz hinzugezogen, um Zeit zu sparen, in der man ewig überlegen bzw. suchen würde. Bevor ich die Ausgaben in meiner Seite eingefügt habe, habe ich diese auf der zur Verfügung gestellten Übungsseite auf ihre Funktion getestet. Die Ideen dafür kamen mir durch meine eigene Spotify-Nutzung bzw. mein Wissen über diese App.
Gesamt kann man sagen, dass SQL das Darstellen und Abfragen von Datensätzen stark vereinfacht und bei der Nutzung hilft. Allerdings benötigt es eine strukturierte und durchdachte Planung von Anfang an. Bei diesen umfangreichen Projekten wie Spotify kann das sehr viel Zeit beanspruchen. Mir persönlich hat es aber den Umgang mit SQL sehr verbildlicht und alltäglicher gemacht, wodurch ich die gesamten Abläufe des Programmierens besser verstehe und selbst anwenden kann. 






== Quellen ==
https://support.spotify.com/de/article/what-is-spotify/