A. PROGRAM

1. Import Library

Pengguna Mengimport library yang dibutuhkan untuk mengakses SQL pada server MySQL yang dimiliki

library(RMySQL)
## Warning: package 'RMySQL' was built under R version 4.5.2
## Loading required package: DBI
## Warning: package 'DBI' was built under R version 4.5.2
library(DBI)
server <- dbConnect(
  RMySQL::MySQL(),
  host="127.0.0.1",
  user="root",
  password=""
)

2. Membuat dan Mengakses Database Baru

dbExecute(server, "CREATE DATABASE IF NOT EXISTS `DonasiOnline`")
## [1] 0
dbExecute(server, "USE `DonasiOnline`")
## [1] 0

3.Membuat tabel data baru dalam sebuah database

#Tabel Bencana
dbExecute(server, "
CREATE TABLE IF NOT EXISTS BENCANA(
  id_bencana VARCHAR(10) PRIMARY KEY,
  nama_bencana VARCHAR(100),
  lokasi VARCHAR(100),
  tanggal DATE,
  status VARCHAR(20)
)
")
## [1] 0
#Tabel Donatur
dbExecute(server, "
CREATE TABLE IF NOT EXISTS DONATUR(
  id_donatur VARCHAR(10) PRIMARY KEY,
  nama VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  nohp VARCHAR(20)
)
")
## [1] 0
#Tabel Donasi
dbExecute(server, "
CREATE TABLE IF NOT EXISTS DONASI(
  id_donasi VARCHAR(10) PRIMARY KEY,
  id_bencana VARCHAR(10),
  id_donatur VARCHAR(10),
  nominal INT,
  tanggal DATE,
  metode VARCHAR(20),
  FOREIGN KEY(id_bencana) REFERENCES BENCANA(id_bencana),
  FOREIGN KEY(id_donatur) REFERENCES DONATUR(id_donatur)
)
")
## [1] 0

4. Memasukkan Data pada Masing-Masing Tabel Data yang telah Dibuat

#Tabel Bencana
dbExecute(server, "DELETE FROM DONASI")   # hapus data donasi dulu (foreign key)
## [1] 14
dbExecute(server, "DELETE FROM DONATUR") # hapus data donatur
## [1] 12
dbExecute(server, "DELETE FROM BENCANA") # hapus data bencana
## [1] 5
dbSendQuery(server,"
INSERT INTO BENCANA VALUES
('BCN001','Gempa Cianjur','Cianjur','2023-02-01','Aktif'),
('BCN002','Banjir Semarang','Semarang','2023-03-10','Aktif'),
('BCN003','Erupsi Semeru','Lumajang','2023-02-25','Selesai'),
('BCN004','Tsunami Aceh','Aceh','2023-04-30','Aktif'),
('BCN005','Angin Puting Beliung','Garut','2023-05-12','Aktif');
")
## <MySQLResult:12,0,8>
#Tabel Donatur
dbSendQuery(server,"
INSERT INTO DONATUR VALUES
('DTR001','Andi Saputra','andi@gmail.com','0812123123'),
('DTR002','Bella Rahma','bella@gmail.com','0813123123'),
('DTR003','Chandra Putra','chandra@gmail.com','0821121111'),
('DTR004','Dewi Lestari','dewi@gmail.com','0812232323'),
('DTR005','Febrian Nur','febrian@gmail.com','0822332332'),
('DTR006','Gina Aprilia','gina@gmail.com','081234111'),
('DTR007','Hendra Salim','hendra@gmail.com','081452313'),
('DTR008','Indah Maharani','indah@gmail.com','0812349898'),
('DTR009','Joko Prasetyo','joko@gmail.com','081225212'),
('DTR010','Karmila Putri','karmila@gmail.com','081228812'),
('DTR011','Lutfi Idris','lutfi@gmail.com','081399321'),
('DTR012','Meisya Anggraini','meisya@gmail.com','081299222')
")
## <MySQLResult:12,0,9>
#Tabel Donasi
dbSendQuery(server,"
INSERT INTO DONASI VALUES
('DNS001','BCN001','DTR001',500000,'2023-02-10','Transfer'),
('DNS002','BCN001','DTR004',750000,'2023-02-11','QRIS'),
('DNS003','BCN002','DTR002',1000000,'2023-03-15','Transfer'),
('DNS004','BCN003','DTR003',300000,'2023-02-01','OVO'),
('DNS005','BCN004','DTR005',1250000,'2023-04-30','Transfer'),
('DNS006','BCN005','DTR006',450000,'2023-05-15','QRIS'),
('DNS007','BCN002','DTR007',600000,'2023-03-18','Transfer'),
('DNS008','BCN003','DTR008',850000,'2023-04-01','OVO'),
('DNS009','BCN001','DTR009',200000,'2023-02-15','QRIS'),
('DNS010','BCN005','DTR010',300000,'2023-05-20','DANA'),
('DNS011','BCN004','DTR011',700000,'2023-04-29','Transfer'),
('DNS012','BCN003','DTR012',950000,'2023-02-05','QRIS'),
('DNS013','BCN002','DTR001',1200000,'2023-03-21','Transfer'),
('DNS014','BCN005','DTR004',400000,'2023-05-23','OVO')
")
## <MySQLResult:12,0,10>

5. Menampilkan Nilai Tabel Data Pada Database

### Melihat nilai tabel data pada database
library(DT)
## Warning: package 'DT' was built under R version 4.5.2
# Tabel Bencana
tampilkan.bencana <- dbGetQuery(server, "SELECT * FROM BENCANA;")
datatable(tampilkan.bencana, options = list(pageLength = 10, autoWidth = TRUE))
# Tabel Donatur
tampilkan.donatur <- dbGetQuery(server, "SELECT * FROM DONATUR;")
datatable(tampilkan.donatur, options = list(pageLength = 10, autoWidth = TRUE))
# Tabel Donasi
tampilkan.donasi <- dbGetQuery(server, "SELECT * FROM DONASI;")
datatable(tampilkan.donasi, options = list(pageLength = 10, autoWidth = TRUE))

6. Analisis Donasi

#Total donasi per bencana

total_bencana <- dbGetQuery(server, "
SELECT B.nama_bencana, SUM(D.nominal) AS total_donasi
FROM DONASI D
JOIN BENCANA B ON D.id_bencana = B.id_bencana
GROUP BY B.nama_bencana
ORDER BY total_donasi DESC;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 1 imported
## as numeric
datatable(total_bencana, options = list(pageLength = 10, autoWidth = TRUE),
caption = 'Total Donasi per Bencana')
#Total donasi per metode pembayaran

total_metode <- dbGetQuery(server, "
SELECT metode, SUM(nominal) AS total
FROM DONASI
GROUP BY metode
ORDER BY total DESC;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 1 imported
## as numeric
datatable(total_metode, options = list(pageLength = 10, autoWidth = TRUE),
caption = 'Total Donasi per Metode Pembayaran')
#Donatur paling dermawan

donatur_top <- dbGetQuery(server, "
SELECT D.id_donatur, N.nama, SUM(D.nominal) as total
FROM DONASI D
JOIN DONATUR N ON D.id_donatur=N.id_donatur
GROUP BY D.id_donatur
ORDER BY total DESC;
")
## Warning in dbSendQuery(conn, statement, ...): Decimal MySQL column 2 imported
## as numeric
datatable(donatur_top, options = list(pageLength = 10, autoWidth = TRUE),
caption = 'Donatur Paling Dermawan')