library(DBI)## Warning: package 'DBI' was built under R version 4.5.2library(RMariaDB)## Warning: package 'RMariaDB' was built under R version 4.5.2server <- dbConnect(
RMariaDB::MariaDB(),
host = "localhost",
user = "root",
password = ""
)# Membuat database baru
dbExecute(server, "CREATE DATABASE IF NOT EXISTS Manajemen_Muusik;")## [1] 1dbExecute(server, "USE Manajemen_Muusik;")## [1] 0#Tabel User
dbExecute(server, "
CREATE TABLE USER (
ID_User VARCHAR(10) PRIMARY KEY,
Nama VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Tanggal_Registrasi DATE NOT NULL,
Preferensi_Genre ENUM('Pop', 'Rock', 'Jazz', 'Classical', 'Hip-Hop', 'Electronic') NOT NULL
);
")## [1] 0## Membuat Tabel Baru Pada Sebuah Database
#Tabel Artist
dbExecute(server, "
CREATE TABLE ARTIST (
ID_Artist VARCHAR(10) PRIMARY KEY,
Nama_Artist VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL,
Tanggal_Daftar DATE NOT NULL,
Genre ENUM('Pop', 'Rock', 'Jazz', 'Classical', 'Hip-Hop', 'Electronic') NOT NULL
);
")## [1] 0#Tabel Album
dbExecute(server, "
CREATE TABLE ALBUM (
ID_Album VARCHAR(10) PRIMARY KEY,
ID_Artist VARCHAR(10) NOT NULL,
Nama_Album VARCHAR(100) NOT NULL,
Tanggal_Rilis DATE NOT NULL,
Jenis_Album ENUM('Studio', 'Live', 'Compilation', 'EP') NOT NULL,
FOREIGN KEY (ID_Artist) REFERENCES ARTIST(ID_Artist)
);
")## [1] 0#Tabel Track
dbExecute(server, "
CREATE TABLE TRACK (
ID_Track VARCHAR(10) PRIMARY KEY,
ID_Album VARCHAR(10) NOT NULL,
Nama_Track VARCHAR(100) NOT NULL,
Durasi TIME NOT NULL,
Genre ENUM('Pop', 'Rock', 'Jazz', 'Classical', 'Hip-Hop', 'Electronic') NOT NULL,
FOREIGN KEY (ID_Album) REFERENCES ALBUM(ID_Album)
);
")## [1] 0#Tabel Playlist
dbExecute(server, "
CREATE TABLE PLAYLIST (
ID_Playlist VARCHAR (10) PRIMARY KEY,
ID_User VARCHAR (10) NOT NULL,
Nama_Playlist VARCHAR(100) NOT NULL,
Tanggal_Dibuat DATE NOT NULL,
FOREIGN KEY (ID_User) REFERENCES USER(ID_User)
);
")## [1] 0#Tabel Streaming
dbExecute(server, "
CREATE TABLE STREAMING(
ID_Streaming VARCHAR(10) PRIMARY KEY,
ID_User VARCHAR(10) NOT NULL,
ID_Track VARCHAR(10) NOT NULL,
Tanggal_Streaming DATE NOT NULL,
Waktu_Mulai TIME NOT NULL,
Durasi_Diputar TIME,
FOREIGN KEY (ID_User) REFERENCES USER(ID_User),
FOREIGN KEY (ID_Track) REFERENCES TRACK(ID_Track)
);
")## [1] 0# Pastikan menggunakan database Manajemen_Musik
dbExecute(server, "USE Manajemen_Muusik;")## [1] 0# memasukkan data pada tabel User
dbExecute(server, "
INSERT INTO USER (ID_User, Nama, Email, Tanggal_Registrasi, Preferensi_Genre) VALUES
('USR001', 'Budi Santoso', 'budi@email.com', '2025-01-15', 'Pop'),
('USR002', 'Sari Wijaya', 'sari@email.com', '2025-02-20', 'Rock'),
('USR003', 'Andi Rahman', 'andi@email.com', '2025-03-10', 'Jazz');
")## [1] 3# Memasukkan data pada tabel Artist
dbExecute(server, "
INSERT INTO ARTIST (ID_Artist, Nama_Artist, Email, Tanggal_Daftar, Genre) VALUES
('ART001', 'Coldplay', 'coldplay@official.com', '2000-01-01', 'Pop'),
('ART002', 'Metallica', 'metallica@official.com', '1981-10-28', 'Rock'),
('ART003', 'Miles Davis', 'miles@official.com', '1945-11-12', 'Jazz'),
('ART004', 'tenxi', 'tenx@official.com', '1945-11-21', 'Rock'),
('ART005', 'Deabdil', 'deabdls@official.com', '1945-12-10', 'Pop');
")## [1] 5# Memasukkan data Pada Tabel Album
dbExecute(server, "
INSERT INTO ALBUM (ID_Album, ID_Artist, Nama_Album, Tanggal_Rilis, Jenis_Album) VALUES
('ALB001', 'ART001', 'Music of the Spheres', '2021-10-15', 'Studio'),
('ALB002', 'ART002', 'Master of Puppets', '1986-03-03', 'Studio'),
('ALB003', 'ART003', 'Kind of Blue', '1959-08-17', 'Studio'),
('ALB004', 'ART004', 'Mejikuhibiniu', '1990-01-17', 'Studio'),
('ALB005', 'ART005', 'Kapsul Waktu', '1959-08-17', 'Studio');
")## [1] 5# Memasukkan data pada tabel Track
dbExecute(server, "
INSERT INTO TRACK (ID_Track, ID_Album, Nama_Track, Durasi, Genre) VALUES
('TRK001', 'ALB001', 'Higher Power', '00:04:14', 'Pop'),
('TRK002', 'ALB001', 'My Universe', '00:02:47', 'Pop'),
('TRK003', 'ALB002', 'Battery', '00:05:12', 'Rock'),
('TRK004', 'ALB003', 'So What', '00:09:22', 'Jazz');
")## [1] 4# memasukkan data pada Playlist
dbExecute(server, "
INSERT INTO PLAYLIST (ID_Playlist, ID_User, Nama_Playlist, Tanggal_Dibuat) VALUES
('PLY001', 'USR001', 'Pop Favorites', '2025-01-20'),
('PLY002', 'USR002', 'Rock Classics', '2025-02-25'),
('PLY003', 'USR003', 'Jazz Nights', '2025-03-15');
")## [1] 3# memasukkan data pada tabel Streaming
dbExecute(server, "
INSERT INTO STREAMING (ID_Streaming, ID_User, ID_Track, Tanggal_Streaming, Waktu_Mulai, Durasi_Diputar) VALUES
('STR001', 'USR001', 'TRK001', '2025-12-01', '20:30:00', '00:03:45'),
('STR002', 'USR002', 'TRK003', '2025-12-02', '22:15:00', '00:04:20'),
('STR003', 'USR003', 'TRK004', '2025-12-03', '19:45:00', '00:08:30');
")## [1] 3#menampilkan kolom-kolom yang dibuat
print(dbListTables(server))## [1] "album" "artist" "playlist" "streaming" "track" "user"#Memilih tabel
User_dataa <- dbGetQuery(server, "SELECT * FROM USER;")
print(User_dataa)## ID_User Nama Email Tanggal_Registrasi Preferensi_Genre
## 1 USR001 Budi Santoso budi@email.com 2025-01-15 Pop
## 2 USR002 Sari Wijaya sari@email.com 2025-02-20 Rock
## 3 USR003 Andi Rahman andi@email.com 2025-03-10 JazzArtist_dataa <- dbGetQuery(server, "SELECT * FROM ARTIST;")
print(Artist_dataa)## ID_Artist Nama_Artist Email Tanggal_Daftar Genre
## 1 ART001 Coldplay coldplay@official.com 2000-01-01 Pop
## 2 ART002 Metallica metallica@official.com 1981-10-28 Rock
## 3 ART003 Miles Davis miles@official.com 1945-11-12 Jazz
## 4 ART004 tenxi tenx@official.com 1945-11-21 Rock
## 5 ART005 Deabdil deabdls@official.com 1945-12-10 PopAlbum_dataa <- dbGetQuery(server, "SELECT * FROM ALBUM;")
print(Album_dataa)## ID_Album ID_Artist Nama_Album Tanggal_Rilis Jenis_Album
## 1 ALB001 ART001 Music of the Spheres 2021-10-15 Studio
## 2 ALB002 ART002 Master of Puppets 1986-03-03 Studio
## 3 ALB003 ART003 Kind of Blue 1959-08-17 Studio
## 4 ALB004 ART004 Mejikuhibiniu 1990-01-17 Studio
## 5 ALB005 ART005 Kapsul Waktu 1959-08-17 StudioTrack_dataa <- dbGetQuery(server, "SELECT * FROM TRACK;")
print(Track_dataa)## ID_Track ID_Album Nama_Track Durasi Genre
## 1 TRK001 ALB001 Higher Power 00:04:14 Pop
## 2 TRK002 ALB001 My Universe 00:02:47 Pop
## 3 TRK003 ALB002 Battery 00:05:12 Rock
## 4 TRK004 ALB003 So What 00:09:22 JazzPlaylist_dataa <- dbGetQuery(server, "SELECT * FROM PLAYLIST;")
print(Playlist_dataa)## ID_Playlist ID_User Nama_Playlist Tanggal_Dibuat
## 1 PLY001 USR001 Pop Favorites 2025-01-20
## 2 PLY002 USR002 Rock Classics 2025-02-25
## 3 PLY003 USR003 Jazz Nights 2025-03-15Streaming_dataa <- dbGetQuery(server, "SELECT * FROM STREAMING;")
print(Streaming_dataa)## ID_Streaming ID_User ID_Track Tanggal_Streaming Waktu_Mulai Durasi_Diputar
## 1 STR001 USR001 TRK001 2025-12-01 20:30:00 00:03:45
## 2 STR002 USR002 TRK003 2025-12-02 22:15:00 00:04:20
## 3 STR003 USR003 TRK004 2025-12-03 19:45:00 00:08:30#Memperbarui / mengubah data di tabel
Update_Userr <- dbExecute(server, "UPDATE USER SET Preferensi_Genre = 'pop' WHERE ID_User = 'USR001';")
print(Update_Userr)## [1] 0Update_Artistt <- dbExecute(server, "UPDATE ARTIST SET Genre = 'Rock' WHERE Nama_Artist = 'Miles Davis';")
print(Update_Artistt)## [1] 1#menghapus data dari tabel
Delete_Streamingg <- dbExecute(server, "DELETE FROM STREAMING WHERE ID_Streaming = 'STR001';")
print(Delete_Streamingg)## [1] 1Delete_Playlistt <- dbExecute(server, "DELETE FROM PLAYLIST WHERE Nama_Playlist = 'Jazz Nights';")
print(Delete_Playlistt)## [1] 1#Mengurutkan data
Track_Sorted_Ascc <- dbGetQuery(server, "SELECT Nama_Track, Durasi FROM TRACK ORDER BY Durasi ASC;")
print(Track_Sorted_Ascc)## Nama_Track Durasi
## 1 My Universe 00:02:47
## 2 Higher Power 00:04:14
## 3 Battery 00:05:12
## 4 So What 00:09:22Track_Sorted_Descc <- dbGetQuery(server,"SELECT Durasi, Nama_Track FROM TRACK ORDER BY Nama_Track DESC;")
print(Track_Sorted_Descc)## Durasi Nama_Track
## 1 00:09:22 So What
## 2 00:02:47 My Universe
## 3 00:04:14 Higher Power
## 4 00:05:12 BatteryInner Join : jenis join yang hanya menampilkan data yang memiliki kecocokan pada kedua tabel.Jika tidak ada pasangan yang cocok antara tabel A dan tabel B, maka baris tersebut tidak dimasukkan ke hasil.
Left Join : join yang mengambil seluruh data dari tabel sebelah kiri, dan data dari tabel sebelah kanan yang cocok saja.Jika tidak ada kecocokan, kolom dari tabel kanan akan berisi NULL.
Rigth Join : kebalikan dari LEFT JOIN. Join ini mengambil seluruh data dari tabel sebelah kanan, sementara data dari tabel kiri hanya ditampilkan jika ada kecocokan.Jika tidak cocok, kolom dari tabel kiri menjadi NULL.
Full Join : join yang mengambil semua data dari tabel kiri dan tabel kanan, baik yang cocok maupun yang tidak cocok.Jika ada baris yang tidak memiliki pasangan, maka kolom dari tabel yang tidak memiliki pasangan akan berisi NULL.
# Menggabungkan tabel User dan Playlist dengan INNER JOIN
data_user_playlist_inner <- dbGetQuery(server, "
SELECT USER.Nama, PLAYLIST.Nama_Playlist
FROM USER
INNER JOIN PLAYLIST ON USER.ID_User = PLAYLIST.ID_User
")
print(data_user_playlist_inner)## Nama Nama_Playlist
## 1 Budi Santoso Pop Favorites
## 2 Sari Wijaya Rock Classics# Menggabungkan tabel Artist dan Album dengan LEFT JOIN
data_artist_album_left <- dbGetQuery(server, "
SELECT ARTIST.Nama_Artist, ALBUM.Nama_Album
FROM ARTIST
LEFT JOIN ALBUM ON ARTIST.ID_Artist = ALBUM.ID_Artist
")
print(data_artist_album_left)## Nama_Artist Nama_Album
## 1 Coldplay Music of the Spheres
## 2 Metallica Master of Puppets
## 3 Miles Davis Kind of Blue
## 4 tenxi Mejikuhibiniu
## 5 Deabdil Kapsul Waktu# Menggabungkan tabel Album dan Track dengan RIGHT JOIN
data_album_track_right <- dbGetQuery(server, "
SELECT ALBUM.Nama_Album, TRACK.Nama_Track
FROM ALBUM
RIGHT JOIN TRACK ON ALBUM.ID_Album = TRACK.ID_Album
")
print(data_album_track_right)## Nama_Album Nama_Track
## 1 Music of the Spheres Higher Power
## 2 Music of the Spheres My Universe
## 3 Master of Puppets Battery
## 4 Kind of Blue So What# FULL OUTER JOIN User dan Playlist menggunakan UNION
data_user_playlist_full <- dbGetQuery(server, "
SELECT USER.Nama, PLAYLIST.Nama_Playlist
FROM USER
LEFT JOIN PLAYLIST ON USER.ID_User = PLAYLIST.ID_User
UNION
SELECT USER.Nama, PLAYLIST.Nama_Playlist
FROM USER
RIGHT JOIN PLAYLIST ON User.ID_User = PLAYLIST.ID_User
WHERE USER.ID_User IS NULL
")
print(data_user_playlist_full)## Nama Nama_Playlist
## 1 Budi Santoso Pop Favorites
## 2 Sari Wijaya Rock Classics
## 3 Andi Rahman <NA>