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 Workoutgehen ;)
<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>














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