Implementasi Sistem Basis Data Relasional pada Pengelolaan Membership Belanja

Pengaplikasian SQL Database dalam Pengolahan Membership Belanja

Program

Import Library

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)
library(DT)

## Warning: package 'DT' was built under R version 4.5.2

Menghubungkan Server Database Ke RStudio

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

Program

Membuat dan Mengakses Database Baru

dbExecute(dbb, "CREATE DATABASE IF NOT EXISTS MembershipBelanja")

## [1] 1

dbExecute(dbb, "USE MembershipBelanja")

## [1] 0

Membuat Tabel Data Baru Dalam Database yang Telah dibuat

1. Tabel Member

dbExecute(dbb, "
CREATE TABLE IF NOT EXISTS Member (
id_member INT PRIMARY KEY,
nama VARCHAR(50)
);
")

## [1] 0

2. Tabel Membership

dbExecute(dbb, "
CREATE TABLE IF NOT EXISTS Membership (
id_membership INT PRIMARY KEY,
kategori VARCHAR(20),
batas_poin INT
);
")

## [1] 0

3. Tabel Barang

dbExecute(dbb, "
CREATE TABLE IF NOT EXISTS Barang (
id_barang INT PRIMARY KEY,
nama_barang VARCHAR(100),
harga INT
);
")

## [1] 0

4. Tabel Pengeluaran

dbExecute(dbb, "
CREATE TABLE IF NOT EXISTS Pengeluaran (
id_transaksi INT PRIMARY KEY,
id_member INT,
id_barang INT,
jumlah INT,
total INT,
FOREIGN KEY(id_member) REFERENCES Member(id_member),
FOREIGN KEY(id_barang) REFERENCES Barang(id_barang)
);
")

## [1] 0

5. Tabel Poin

dbExecute(dbb, "
CREATE TABLE IF NOT EXISTS Poin (
id_poin INT PRIMARY KEY,
id_member INT,
total_poin INT,
FOREIGN KEY(id_member) REFERENCES Member(id_member)
);
")

## [1] 0

Memasukkan Data ke Dalam Tabel

Mengisi suatu data pada tabel-tabel yg telah dibuat

1. Memasukkan Data ke Dalam Tabel Member

dbExecute(dbb, "
INSERT INTO Member VALUES
(101,'Reza'),
(102,'Maya'),
(103,'Dilan'),
(104,'Sarah'),
(105,'Mey'),
(106,'Febri');
")

## [1] 6

2. Memasukkan Data ke Dalam Tabel Membership

dbExecute(dbb, "
INSERT INTO Membership VALUES
(201,'Silver',100),
(202,'Gold',250),
(203,'Platinum',500);
")

## [1] 3

3. Memasukkan Data ke Dalam Tabel Barang

dbExecute(dbb, "
INSERT INTO Barang VALUES
(301,'Buku',50000),
(302,'Tas',260000),
(303,'Minyak Goreng',25000),
(304,'Stiker Kartun',10000),
(305,'Sepatu',350000);
")

## [1] 5

4. Memasukkan Data ke Dalam Tabel Pengeluaran

dbExecute(dbb, "
INSERT INTO Pengeluaran VALUES
(401,105,301,2,100000),
(402,103,302,1,260000),
(403,106,305,2,700000),
(404,104,304,10,100000),
(405,101,303,6,150000),
(406,102,305,1,350000);
")

## [1] 6

5. Memasukkan Data ke Dalam Tabel Poin

dbExecute(dbb, "
INSERT INTO Poin VALUES
(501,105,100),
(502,103,260),
(503,106,700),
(504,104,100),
(505,101,150),
(506,102,350);
")

## [1] 6

Study Kasus

1. Menampilkan Isi Tabel Data Member

2. Menampilkan Isi Tabel Data Membership

3. Menampilkan Isi Tabel Data Barang

4. Menampilkan Isi Tabel Data Pengeluaran

5. Menampilkan Isi Tabel Data Poin

Laporan Membership Belanja

Ranking Poin Member

Barang Paling Banyak Dibeli

Total Pengeluaran

Membership Per Member