3.1 Membuat Koneksi Database

Program

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

Output

0
## [1] 0

Penjelasan

Pada tahap ini dilakukan persiapan awal sebelum membangun sistem basis data. Paket DBI, RMySQL, dan RMariaDB diimpor untuk memungkinkan R terhubung dengan MySQL. Output 0 berarti perintah berhasil dieksekusi.

3.2 Membuat dan Mengakses Database

Program

dbExecute(dbb, "DROP DATABASE IF EXISTS BengkelMotor;")
## [1] 4
dbExecute(dbb, "CREATE DATABASE BengkelMotor;")
## [1] 1
dbExecute(dbb, "USE BengkelMotor;")
## [1] 0

Output

c(4,1,0)
## [1] 4 1 0

Penjelasan

Perintah DROP, CREATE, dan USE dijalankan untuk mengatur database yang akan digunakan.

3.3 Membuat Tabel

Program

dbExecute(dbb, "
CREATE TABLE Pelanggan (
  ID_Pelanggan VARCHAR(15) PRIMARY KEY NOT NULL,
  Nama_Pelanggan VARCHAR(50) NOT NULL,
  Nomor_HP VARCHAR(20) NOT NULL,
  Alamat VARCHAR(100)
);
")
## [1] 0
dbExecute(dbb, "
CREATE TABLE Mekanik (
  ID_Mekanik VARCHAR(10) PRIMARY KEY NOT NULL,
  Nama_Mekanik VARCHAR(50) NOT NULL,
  Keahlian VARCHAR(50) NOT NULL
);
")
## [1] 0
dbExecute(dbb, "
CREATE TABLE Layanan (
  ID_Layanan VARCHAR(10) PRIMARY KEY,
  Nama_Layanan VARCHAR(100) NOT NULL,
  Harga INT NOT NULL
);
")
## [1] 0
dbExecute(dbb, "
CREATE TABLE Transaksi (
  ID_Transaksi VARCHAR(10) PRIMARY KEY,
  ID_Pelanggan VARCHAR(15) NOT NULL,
  ID_Mekanik VARCHAR(10) NOT NULL,
  ID_Layanan VARCHAR(10) NOT NULL,
  Tanggal DATE NOT NULL,
  Total INT,
  Metode_Bayar VARCHAR(20) NOT NULL,
  FOREIGN KEY (ID_Pelanggan) REFERENCES Pelanggan(ID_Pelanggan),
  FOREIGN KEY (ID_Mekanik) REFERENCES Mekanik(ID_Mekanik),
  FOREIGN KEY (ID_Layanan) REFERENCES Layanan(ID_Layanan)
);
")
## [1] 0

Output

c(0,0,0,0)
## [1] 0 0 0 0

3.4 Mengisi Data

Program

dbExecute(dbb, "
INSERT INTO Pelanggan VALUES
('PL001','Emi','081222','Kambara'),
('PL002','Askar','081333','Katela'),
('PL003','Nila','081444','Waumere'),
('PL004','Yusran','081555','Waturempe');
")
## [1] 4
dbExecute(dbb, "
INSERT INTO Mekanik VALUES
('MK001','Angga','Servis Mesin'),
('MK002','Ikel','Ganti Oli'),
('MK003','Ujang','Tune Up');
")
## [1] 3
dbExecute(dbb, "
INSERT INTO Layanan VALUES
('L001', 'Ganti Oli', 30000),
('L002', 'Servis Ringan', 50000),
('L003', 'Tune Up Lengkap', 80000);
")
## [1] 3
dbExecute(dbb, "
INSERT INTO Transaksi VALUES
('T001','PL001','MK002','L001','2025-12-01',30000,'Cash'),
('T002','PL002','MK001','L002','2025-12-01',50000,'Qris'),
('T003','PL003','MK003','L003','2025-12 02',80000,'Transfer'),
('T004','PL004','MK002','L001','2025-12-03',30000,'Cash'),
('T005','PL002','MK003','L003','2025-12-04',80000,'Qris'),
('T006','PL001','MK001','L002','2025-12-05',50000,'Debit'),
('T007','PL003','MK002','L001','2025-12-06',30000,'Cash'),
('T008','PL004','MK003','L003','2025-12-07',80000,'Qris');
")
## [1] 8

Output

c(4,3,3,8)
## [1] 4 3 3 8

3.5 Menampilkan Data

Program

dbGetQuery(dbb, "SELECT * FROM Pelanggan;")
##   ID_Pelanggan Nama_Pelanggan Nomor_HP    Alamat
## 1        PL001            Emi   081222   Kambara
## 2        PL002          Askar   081333    Katela
## 3        PL003           Nila   081444   Waumere
## 4        PL004         Yusran   081555 Waturempe
dbGetQuery(dbb, "SELECT * FROM Mekanik;")
##   ID_Mekanik Nama_Mekanik     Keahlian
## 1      MK001        Angga Servis Mesin
## 2      MK002         Ikel    Ganti Oli
## 3      MK003        Ujang      Tune Up
dbGetQuery(dbb, "SELECT * FROM Layanan;")
##   ID_Layanan    Nama_Layanan Harga
## 1       L001       Ganti Oli 30000
## 2       L002   Servis Ringan 50000
## 3       L003 Tune Up Lengkap 80000
dbGetQuery(dbb, "SELECT * FROM Transaksi;")
##   ID_Transaksi ID_Pelanggan ID_Mekanik ID_Layanan    Tanggal Total Metode_Bayar
## 1         T001        PL001      MK002       L001 2025-12-01 30000         Cash
## 2         T002        PL002      MK001       L002 2025-12-01 50000         Qris
## 3         T003        PL003      MK003       L003 0000-00-00 80000     Transfer
## 4         T004        PL004      MK002       L001 2025-12-03 30000         Cash
## 5         T005        PL002      MK003       L003 2025-12-04 80000         Qris
## 6         T006        PL001      MK001       L002 2025-12-05 50000        Debit
## 7         T007        PL003      MK002       L001 2025-12-06 30000         Cash
## 8         T008        PL004      MK003       L003 2025-12-07 80000         Qris

Hasil tabel akan tampil otomatis tanpa gambar.

3.6 INNER JOIN

Program

dbGetQuery(dbb, "
SELECT tr.ID_Transaksi, pl.Nama_Pelanggan, mk.Nama_Mekanik, ly.Nama_Layanan, tr.Tanggal, tr.Total
FROM Transaksi tr
INNER JOIN Pelanggan pl ON tr.ID_Pelanggan = pl.ID_Pelanggan
INNER JOIN Mekanik mk ON tr.ID_Mekanik = mk.ID_Mekanik
INNER JOIN Layanan ly ON tr.ID_Layanan = ly.ID_Layanan;
")
##   ID_Transaksi Nama_Pelanggan Nama_Mekanik    Nama_Layanan    Tanggal Total
## 1         T001            Emi         Ikel       Ganti Oli 2025-12-01 30000
## 2         T002          Askar        Angga   Servis Ringan 2025-12-01 50000
## 3         T003           Nila        Ujang Tune Up Lengkap 0000-00-00 80000
## 4         T004         Yusran         Ikel       Ganti Oli 2025-12-03 30000
## 5         T005          Askar        Ujang Tune Up Lengkap 2025-12-04 80000
## 6         T006            Emi        Angga   Servis Ringan 2025-12-05 50000
## 7         T007           Nila         Ikel       Ganti Oli 2025-12-06 30000
## 8         T008         Yusran        Ujang Tune Up Lengkap 2025-12-07 80000

3.7 RIGHT JOIN (semua layanan)

Program

dbGetQuery(dbb, "
SELECT ly.ID_Layanan, ly.Nama_Layanan, tr.ID_Transaksi, tr.Total
FROM Transaksi tr
RIGHT JOIN Layanan ly ON tr.ID_Layanan = ly.ID_Layanan
ORDER BY ly.ID_Layanan;
")
##   ID_Layanan    Nama_Layanan ID_Transaksi Total
## 1       L001       Ganti Oli         T001 30000
## 2       L001       Ganti Oli         T004 30000
## 3       L001       Ganti Oli         T007 30000
## 4       L002   Servis Ringan         T002 50000
## 5       L002   Servis Ringan         T006 50000
## 6       L003 Tune Up Lengkap         T003 80000
## 7       L003 Tune Up Lengkap         T005 80000
## 8       L003 Tune Up Lengkap         T008 80000

3.8 FULL JOIN (simulasi dengan UNION)

Program

dbGetQuery(dbb, "
SELECT pl.ID_Pelanggan, pl.Nama_Pelanggan, tr.ID_Transaksi, tr.Total
FROM Pelanggan pl
LEFT JOIN Transaksi tr ON pl.ID_Pelanggan = tr.ID_Pelanggan
UNION
SELECT pl.ID_Pelanggan, pl.Nama_Pelanggan, tr.ID_Transaksi, tr.Total
FROM Pelanggan pl
RIGHT JOIN Transaksi tr ON pl.ID_Pelanggan = tr.ID_Pelanggan;
")
##   ID_Pelanggan Nama_Pelanggan ID_Transaksi Total
## 1        PL001            Emi         T001 30000
## 2        PL002          Askar         T002 50000
## 3        PL003           Nila         T003 80000
## 4        PL004         Yusran         T004 30000
## 5        PL002          Askar         T005 80000
## 6        PL001            Emi         T006 50000
## 7        PL003           Nila         T007 30000
## 8        PL004         Yusran         T008 80000

3.9 UPDATE Harga Layanan

Program

dbExecute(dbb, "
UPDATE Layanan
SET Harga = 35000
WHERE ID_Layanan = 'L001';
")
## [1] 1

3.10 DELETE Layanan & Tambah Baru

Program

dbExecute(dbb, "
INSERT IGNORE INTO Layanan (ID_Layanan, Nama_Layanan, Harga)
VALUES ('L004', 'Servis Rem', 45000);
")
## [1] 1
dbExecute(dbb, "UPDATE Transaksi SET ID_Layanan='L004' WHERE ID_Layanan='L002';")
## [1] 2
dbExecute(dbb, "DELETE FROM Layanan WHERE ID_Layanan='L002';")
## [1] 1

3.11 Laporan Pendapatan

Program

dbGetQuery(dbb, "
SELECT SUM(Total) AS TotalPendapatan, AVG(Total) AS RataRataPendapatan
FROM Transaksi;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 0 imported
## as numeric
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 1 imported
## as numeric
##   TotalPendapatan RataRataPendapatan
## 1          430000              53750