Sistem Manajemen Data Pelanggan Barbershop

Pendahuluan

Studi kasus ini mengimplementasikan sistem basis data untuk manajemen pelanggan barbershop menggunakan SQLite dalam R.

1. Persiapan dan Koneksi Database

# Instal paket (jika belum)
# install.packages(c("DBI", "RSQLite", "dplyr", "knitr"))

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)

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

## 
## 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(knitr)

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

# Membuat koneksi database
con <- dbConnect(SQLite(), ":memory:")
cat("Koneksi database berhasil dibuat. Output:", dbGetQuery(con, "SELECT 1")[[1]], "\n")

## Koneksi database berhasil dibuat. Output: 1

2. Membuat Tabel Database

# Tabel Pelanggan
dbExecute(con, "
CREATE TABLE pelanggan (
    id_pelanggan INTEGER PRIMARY KEY AUTOINCREMENT,
    nama TEXT NOT NULL,
    no_hp TEXT UNIQUE,
    email TEXT,
    tanggal_daftar DATE DEFAULT CURRENT_DATE
);")

## [1] 0

# Tabel Layanan
dbExecute(con, "
CREATE TABLE layanan (
    id_layanan INTEGER PRIMARY KEY AUTOINCREMENT,
    nama_layanan TEXT NOT NULL,
    harga INTEGER NOT NULL,
    durasi INTEGER DEFAULT 30
);")

## [1] 0

# Tabel Kapster
dbExecute(con, "
CREATE TABLE kapster (
    id_kapster INTEGER PRIMARY KEY AUTOINCREMENT,
    nama_kapster TEXT NOT NULL,
    spesialisasi TEXT,
    pengalaman INTEGER
);")

## [1] 0

# Tabel Booking
dbExecute(con, "
CREATE TABLE booking (
    id_booking INTEGER PRIMARY KEY AUTOINCREMENT,
    id_pelanggan INTEGER NOT NULL,
    id_layanan INTEGER NOT NULL,
    id_kapster INTEGER NOT NULL,
    tanggal_booking DATE NOT NULL,
    waktu_booking TIME NOT NULL,
    status TEXT DEFAULT 'Dipesan',
    catatan TEXT,
    FOREIGN KEY(id_pelanggan) REFERENCES pelanggan(id_pelanggan),
    FOREIGN KEY(id_layanan) REFERENCES layanan(id_layanan),
    FOREIGN KEY(id_kapster) REFERENCES kapster(id_kapster)
);")

## [1] 0

# Tabel Transaksi
dbExecute(con, "
CREATE TABLE transaksi (
    id_transaksi INTEGER PRIMARY KEY AUTOINCREMENT,
    id_booking INTEGER NOT NULL,
    metode_bayar TEXT DEFAULT 'Tunai',
    total_bayar INTEGER NOT NULL,
    tanggal_bayar DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY(id_booking) REFERENCES booking(id_booking)
);")

## [1] 0

cat("Kelima tabel berhasil dibuat.\n")

## Kelima tabel berhasil dibuat.

3. Mengisi Data

# Data Pelanggan
dbExecute(con, "
INSERT INTO pelanggan (nama, no_hp, email) VALUES
('Ahyum', '08123456789', 'ahyum@email.com'),
('Rifqy', '08234567890', 'rifqy@email.com'),
('Bagas', '08345678901', 'ezhar@email.com');")

## [1] 3

# Data Layanan
dbExecute(con, "
INSERT INTO layanan (nama_layanan, harga, durasi) VALUES
('Potong Rambut', 50000, 30),
('Cukur Rambut', 30000, 20),
('Styling Rambut', 40000, 40),
('Facial Treatment', 80000, 60);")

## [1] 4

# Data Kapster
dbExecute(con, "
INSERT INTO kapster (nama_kapster, spesialisasi, pengalaman) VALUES
('Ezhar', 'Potong Modern', 5),
('Shafwan', 'Cukur Tradisional', 8),
('Eca', 'Styling & Coloring', 4);")

## [1] 3

# Data Booking
dbExecute(con, "
INSERT INTO booking (id_pelanggan, id_layanan, id_kapster, tanggal_booking, waktu_booking, status) VALUES
(1, 1, 1, '2025-06-01', '14:00', 'Selesai'),
(2, 3, 3, '2025-06-01', '16:30', 'Dipesan'),
(3, 2, 2, '2025-06-02', '10:00', 'Dipesan');")

## [1] 3

# Data Transaksi
dbExecute(con, "
INSERT INTO transaksi (id_booking, metode_bayar, total_bayar) VALUES
(1, 'Tunai', 50000);")

## [1] 1

cat("Data berhasil dimasukkan ke semua tabel.\n")

## Data berhasil dimasukkan ke semua tabel.

4. Query dan Analisis

4.1 Menampilkan Data Booking

booking_data <- dbGetQuery(con, "
SELECT 
    b.id_booking,
    p.nama AS pelanggan,
    l.nama_layanan,
    k.nama_kapster,
    b.tanggal_booking,
    b.waktu_booking,
    b.status
FROM booking b
JOIN pelanggan p ON b.id_pelanggan = p.id_pelanggan
JOIN layanan l ON b.id_layanan = l.id_layanan
JOIN kapster k ON b.id_kapster = k.id_kapster
ORDER BY b.tanggal_booking DESC;")

kable(booking_data, caption = "Data Booking Barbershop")

Data Booking Barbershop

id_booking	pelanggan	nama_layanan	nama_kapster	tanggal_booking	waktu_booking	status
3	Bagas	Cukur Rambut	Shafwan	2025-06-02	10:00	Dipesan
1	Ahyum	Potong Rambut	Ezhar	2025-06-01	14:00	Selesai
2	Rifqy	Styling Rambut	Eca	2025-06-01	16:30	Dipesan

4.3 Data Transaksi

transaksi_data <- dbGetQuery(con, "
SELECT 
    t.id_transaksi,
    p.nama AS pelanggan,
    l.nama_layanan,
    t.metode_bayar,
    t.total_bayar,
    t.tanggal_bayar
FROM transaksi t
JOIN booking b ON t.id_booking = b.id_booking
JOIN pelanggan p ON b.id_pelanggan = p.id_pelanggan
JOIN layanan l ON b.id_layanan = l.id_layanan;")

kable(transaksi_data, caption = "Data Transaksi")

Data Transaksi

id_transaksi	pelanggan	nama_layanan	metode_bayar	total_bayar	tanggal_bayar
1	Ahyum	Potong Rambut	Tunai	50000	2025-12-10 15:23:20

5. Kesimpulan

Sistem basis data untuk manajemen pelanggan barbershop telah berhasil diimplementasikan menggunakan SQLite dalam R. Sistem ini mampu mengelola data pelanggan, layanan, kapster, booking, dan transaksi secara terstruktur.