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 == 
 Kardinalitäten:
 Ein Song gehört zu mehreren Playlists. Zu einer Playlist gehören mehrere Songs. 
 Ein Nutzer erstellt mehrere Playlists. Eine Playlist wird von einem Nutzer erstellt.
 Ein Nutzer hört mehrere Songs. Ein Song wird von mehreren Nutzern gehört.
 Im vorliegenden Datenbankmodell entstehen genau zwei Relationstabellen durch die Umwandlung von m:n-Beziehungen aus dem ER-Modell ins Relationenmodell:
 Die Tabelle Playlistinhalt realisiert die m:n-Beziehung zwischen Song und Playlist. 
 Die Tabelle Hörerverlauf realisiert die m:n-Beziehung zwischen Nutzer und Song (unter Berücksichtigung des Zeitpunkts).
 Alle übrigen Beziehungen sind 1:n-Beziehungen und werden lediglich durch Fremdschlüssel abgebildet, ohne eigene Tabellen zu erzeugen.
 Normalformen sind Regeln zur Strukturierung von Datenbanken. Sie verhindern doppelte Daten und Probleme beim Speichern, Ändern oder Löschen.
 In meinem Modell sind alle drei Normalformen erfüllt.
* 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 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/
https://hsqldb.org/doc/guide/sqlgeneralchapt.html