Implementasi Sistem Basis Data Relasional pada Aktivitas Penyewaan PlayStation ## Program ### Import Library dan Meghubungkan Server Ke RStudio Mengimport library terlebihi dahulu agar program dapat membaca package yang akan digunakan

library(DBI)
## Warning: package 'DBI' was built under R version 4.5.2
library(RSQLite)
## Warning: package 'RSQLite' was built under R version 4.5.2
library(dplyr)
## 
## Attaching package: 'dplyr'
## The following objects are masked from 'package:stats':
## 
##     filter, lag
## The following objects are masked from 'package:base':
## 
##     intersect, setdiff, setequal, union
library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.5.2
## 
## Attaching package: 'RMySQL'
## The following object is masked from 'package:RSQLite':
## 
##     isIdCurrent

Menghubungkan ke Server

dbb <- dbConnect(
  RMySQL::MySQL(),
  host = "127.0.0.1",
  user = "root",
  password = "")

Membuat Database

db_file <- "Rental_PS_Kuu.db"
con <- dbConnect(RSQLite::SQLite(), dbname = db_file)
con
## <SQLiteConnection>
##   Path: D:\BASDAT\Rental_PS_Kuu.db
##   Extensions: TRUE

Membust Struktur Tabel

dbExecute(con, "DROP TABLE IF EXISTS detail_sewa;")
## [1] 0
dbExecute(con, "DROP TABLE IF EXISTS sewa;")
## [1] 0
dbExecute(con, "DROP TABLE IF EXISTS game;")
## [1] 0
dbExecute(con, "DROP TABLE IF EXISTS konsol;")
## [1] 0
dbExecute(con, "DROP TABLE IF EXISTS pelanggan;")
## [1] 0
dbExecute(con, "
  CREATE TABLE pelanggan (
    pelanggan_id INT AUTO_INCREMENT PRIMARY KEY,
    nama_pelanggan VARCHAR(100) NOT NULL,
    alamat VARCHAR(150),
    no_hp VARCHAR(20)
    );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE konsol (
    konsol_id INT AUTO_INCREMENT PRIMARY KEY,
    jenis_konsol VARCHAR(50) NOT NULL,
    harga_sewa_per_jam DECIMAL(10,2) NOT NULL,
    status_konsol VARCHAR(20) DEFAULT 'Tersedia'
    );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE game (
    game_id INT AUTO_INCREMENT PRIMARY KEY,
    nama_game VARCHAR(100) NOT NULL,
    genre VARCHAR(50),
    rating VARCHAR(10)
    );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE sewa (
    sewa_id INT AUTO_INCREMENT PRIMARY KEY,
    pelanggan_id INT NOT NULL,
    konsol_id INT NOT NULL,
    tanggal_sewa DATETIME NOT NULL,
    tanggal_kembali DATETIME,
    total_bayar DECIMAL(10,2),

    FOREIGN KEY (pelanggan_id) REFERENCES pelanggan(pelanggan_id),
    FOREIGN KEY (konsol_id) REFERENCES konsol(konsol_id)
    );
")
## [1] 0
dbExecute(con, "
  CREATE TABLE detail_sewa (
    detail_id INT AUTO_INCREMENT PRIMARY KEY,
    sewa_id INT NOT NULL,
    game_id INT NOT NULL,
    durasi_main_jam INT NOT NULL,

    FOREIGN KEY (sewa_id) REFERENCES sewa(sewa_id),
    FOREIGN KEY (game_id) REFERENCES game(game_id)
    );
")
## [1] 0

Mengisi Data Tabel

dbExecute(con, "
  INSERT INTO pelanggan (pelanggan_id, nama_pelanggan, alamat, no_hp) VALUES
('1001', 'Shafar Amanah', 'Jl. Banteng No. 12', '081234567890'),
('1002', 'Intan Puspita', 'Jl. Lakidende No. 23', '082233445566'),
('1003', 'Anita Meronda', 'Jl. Ade Irma Nasution No. 22', '083345667788');
")
## [1] 3
dbExecute(con, "
  INSERT INTO konsol (konsol_id, jenis_konsol, harga_sewa_per_jam, status_konsol) VALUES
('511', 'PlayStation 3', 5000, 'Tersedia'),
('512', 'PlayStation 4', 8000, 'Tersedia'),
('513', 'PlayStation 5', 12000, 'Tersedia');
")
## [1] 3
dbExecute(con, "
  INSERT INTO game (game_id, nama_game, genre, rating) VALUES
('201', 'FIFA 23', 'Sports', 'E'),
('202', 'God of War Ragnarok', 'Action', 'M'),
('203', 'GTA V', 'Adventure', 'M');
")
## [1] 3
dbExecute(con, "
  INSERT INTO sewa (sewa_id, pelanggan_id, konsol_id, tanggal_sewa, tanggal_kembali, total_bayar) VALUES
('111', '1001', '512', '2024-01-10 10:00:00', '2024-01-10 13:00:00', 24000),
('112', '1002', '511', '2024-01-11 14:00:00', '2024-01-11 16:00:00', 10000),
('113', '1003', '513', '2024-01-12 09:00:00', '2024-01-12 12:00:00', 36000);
")
## [1] 3
dbExecute(con, "
  INSERT INTO detail_sewa (sewa_id, game_id, durasi_main_jam) VALUES
(111, 201, 3),
(112, 202, 1),
(113, 203, 2);
")
## [1] 3

Menampilkan Seluruh Tabel

dbListTables(con)
## [1] "detail_sewa" "game"        "konsol"      "pelanggan"   "sewa"
dbGetQuery(con, "SELECT * FROM pelanggan")  
##   pelanggan_id nama_pelanggan                       alamat        no_hp
## 1         1001  Shafar Amanah           Jl. Banteng No. 12 081234567890
## 2         1002  Intan Puspita         Jl. Lakidende No. 23 082233445566
## 3         1003  Anita Meronda Jl. Ade Irma Nasution No. 22 083345667788

Mengurutkan Tabel

dbGetQuery(con, "
  SELECT *
  FROM sewa
  ORDER BY total_bayar ASC;
")
##   sewa_id pelanggan_id konsol_id        tanggal_sewa     tanggal_kembali
## 1     112         1002       511 2024-01-11 14:00:00 2024-01-11 16:00:00
## 2     111         1001       512 2024-01-10 10:00:00 2024-01-10 13:00:00
## 3     113         1003       513 2024-01-12 09:00:00 2024-01-12 12:00:00
##   total_bayar
## 1       10000
## 2       24000
## 3       36000
dbGetQuery(con, "
  SELECT *
  FROM sewa
  ORDER BY total_bayar DESC;
")
##   sewa_id pelanggan_id konsol_id        tanggal_sewa     tanggal_kembali
## 1     113         1003       513 2024-01-12 09:00:00 2024-01-12 12:00:00
## 2     111         1001       512 2024-01-10 10:00:00 2024-01-10 13:00:00
## 3     112         1002       511 2024-01-11 14:00:00 2024-01-11 16:00:00
##   total_bayar
## 1       36000
## 2       24000
## 3       10000

Menampilkan Data Tertentu

dbGetQuery(con, "
  SELECT *
  FROM pelanggan
  WHERE nama_pelanggan = 'Intan Puspita';
")
##   pelanggan_id nama_pelanggan               alamat        no_hp
## 1         1002  Intan Puspita Jl. Lakidende No. 23 082233445566

Mengupdate Data

dbExecute(con, "
  UPDATE konsol
  SET status_konsol = 'Rusak'
  WHERE konsol_id = 511;
")
## [1] 1
dbGetQuery(con, "SELECT * FROM konsol WHERE konsol_id = 511")
##   konsol_id  jenis_konsol harga_sewa_per_jam status_konsol
## 1       511 PlayStation 3               5000         Rusak

Menghapus Data

dbExecute(con, "
  DELETE FROM game
  WHERE game_id = 203;
")
## [1] 1
dbGetQuery(con, "SELECT * FROM game;")
##   game_id           nama_game  genre rating
## 1     201             FIFA 23 Sports      E
## 2     202 God of War Ragnarok Action      M

Menamapilkan Data Gabungan

dbGetQuery(con, "
  SELECT s.sewa_id, p.nama_pelanggan, k.jenis_konsol,
         s.tanggal_sewa, s.tanggal_kembali, s.total_bayar
  FROM sewa s
  INNER JOIN pelanggan p ON s.pelanggan_id = p.pelanggan_id
  INNER JOIN konsol k ON s.konsol_id = k.konsol_id;
")
##   sewa_id nama_pelanggan  jenis_konsol        tanggal_sewa     tanggal_kembali
## 1     111  Shafar Amanah PlayStation 4 2024-01-10 10:00:00 2024-01-10 13:00:00
## 2     112  Intan Puspita PlayStation 3 2024-01-11 14:00:00 2024-01-11 16:00:00
## 3     113  Anita Meronda PlayStation 5 2024-01-12 09:00:00 2024-01-12 12:00:00
##   total_bayar
## 1       24000
## 2       10000
## 3       36000

ALIAS SUM & AVG

dbGetQuery(con, "
  SELECT SUM(total_bayar) AS total_pemasukan
  FROM sewa;
")
##   total_pemasukan
## 1           70000

Laporan Transaksi

dbGetQuery(con, "
  SELECT 
    s.sewa_id,
    p.nama_pelanggan,
    k.jenis_konsol,
    g.nama_game,
    d.durasi_main_jam,
    s.tanggal_sewa,
    s.tanggal_kembali,
    s.total_bayar
  FROM sewa s
    INNER JOIN pelanggan p ON s.pelanggan_id = p.pelanggan_id
    INNER JOIN konsol k ON s.konsol_id = k.konsol_id
    INNER JOIN detail_sewa d ON s.sewa_id = d.sewa_id
    INNER JOIN game g ON d.game_id = g.game_id
  ORDER BY s.sewa_id ASC;
")
##   sewa_id nama_pelanggan  jenis_konsol           nama_game durasi_main_jam
## 1     111  Shafar Amanah PlayStation 4             FIFA 23               3
## 2     112  Intan Puspita PlayStation 3 God of War Ragnarok               1
##          tanggal_sewa     tanggal_kembali total_bayar
## 1 2024-01-10 10:00:00 2024-01-10 13:00:00       24000
## 2 2024-01-11 14:00:00 2024-01-11 16:00:00       10000