Content is user-generated and unverified.

LAPORAN PROYEK AKHIR PEMROGRAMAN BASIS DATA

Studi Kasus: Basis Data Toko Game Top Up "ABC TOPUP"

Kelompok: [Isi Nama Anggota Kelompok]
Kelas: [Isi Kelas]
Tanggal: [Tanggal Penyerahan]

1. DESAIN DAN PEMBUATAN BASIS DATA

a. Penjelasan Basis Data

Basis data abc_topup_db dirancang untuk mengelola data pelanggan, produk game yang dijual, transaksi pembelian, serta detail dari setiap transaksi. Sistem ini memungkinkan pencatatan yang rapi dan terstruktur, mulai dari pendaftaran pelanggan hingga histori pembelian diamond game. Relasi antar tabel dirancang untuk menjaga integritas data dan efisiensi query.

b. Kerangka Basis Data (ERD)

Berikut adalah deskripsi Entity-Relationship Diagram (ERD) untuk basis data abc_topup_db:

  • pelanggan ke profil_pelanggan (Relasi One-to-One): Setiap pelanggan memiliki tepat satu profil_pelanggan yang berisi informasi spesifik game (seperti User ID dan Server).
  • game ke produk (Relasi One-to-Many): Satu game dapat memiliki banyak produk.
  • pelanggan ke transaksi (Relasi One-to-Many): Satu pelanggan dapat melakukan banyak transaksi.
  • transaksi ke produk (Relasi Many-to-Many): Relasi ini dijembatani oleh tabel detail_transaksi.

c. File .sql untuk Pembuatan Basis Data

File SQL lengkap untuk membuat struktur dan mengisi data dapat diakses melalui repositori berikut:
[Link ke GitLab/GitHub Anda untuk file abc_topup_db.sql]

Berikut adalah kode SQL untuk membuat skema dan menyisipkan data awal:

sql
-- Membuat Database
CREATE DATABASE IF NOT EXISTS abc_topup_db;
USE abc_topup_db;

-- 1. Tabel pelanggan
CREATE TABLE pelanggan (
    id_pelanggan INT AUTO_INCREMENT PRIMARY KEY,
    nama_pelanggan VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    no_hp VARCHAR(15) NOT NULL,
    tanggal_daftar DATE
);

-- 2. Tabel profil_pelanggan (Relasi One-to-One dengan pelanggan)
CREATE TABLE profil_pelanggan (
    id_profil INT AUTO_INCREMENT PRIMARY KEY,
    id_pelanggan INT NOT NULL UNIQUE,
    user_id_game VARCHAR(50),
    server_game VARCHAR(10),
    catatan TEXT,
    FOREIGN KEY (id_pelanggan) REFERENCES pelanggan(id_pelanggan) ON DELETE CASCADE
);

-- 3. Tabel game
CREATE TABLE game (
    id_game INT AUTO_INCREMENT PRIMARY KEY,
    nama_game VARCHAR(100) NOT NULL,
    publisher VARCHAR(100)
);

-- 4. Tabel produk (Relasi One-to-Many dengan game)
CREATE TABLE produk (
    id_produk INT AUTO_INCREMENT PRIMARY KEY,
    id_game INT NOT NULL,
    nama_produk VARCHAR(100) NOT NULL,
    jumlah_diamond INT NOT NULL,
    harga DECIMAL(10, 2) NOT NULL,
    FOREIGN KEY (id_game) REFERENCES game(id_game)
);

-- 5. Tabel transaksi (Relasi One-to-Many dengan pelanggan)
CREATE TABLE transaksi (
    id_transaksi INT AUTO_INCREMENT PRIMARY KEY,
    id_pelanggan INT NOT NULL,
    tanggal_transaksi DATETIME DEFAULT CURRENT_TIMESTAMP,
    metode_pembayaran VARCHAR(50),
    status_pembayaran VARCHAR(20) DEFAULT 'Pending',
    FOREIGN KEY (id_pelanggan) REFERENCES pelanggan(id_pelanggan)
);

-- 6. Tabel detail_transaksi (Junction Table untuk Many-to-Many)
CREATE TABLE detail_transaksi (
    id_transaksi INT NOT NULL,
    id_produk INT NOT NULL,
    jumlah INT DEFAULT 1,
    subtotal DECIMAL(10, 2) NOT NULL,
    PRIMARY KEY (id_transaksi, id_produk),
    FOREIGN KEY (id_transaksi) REFERENCES transaksi(id_transaksi),
    FOREIGN KEY (id_produk) REFERENCES produk(id_produk)
);

-- Tabel bantu untuk Trigger
CREATE TABLE audit_log (
    id_log INT AUTO_INCREMENT PRIMARY KEY,
    nama_tabel VARCHAR(50),
    aksi VARCHAR(50),
    data_lama TEXT,
    data_baru TEXT,
    waktu_aksi TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    pengguna VARCHAR(100)
);

Memasukkan Data Awal (Minimal 5 baris per tabel):

sql
INSERT INTO pelanggan (nama_pelanggan, email, no_hp, tanggal_daftar) VALUES
('Budi Santoso', 'budi.s@example.com', '081234567890', '2023-01-15'),
('Ani Lestari', 'ani.l@example.com', '081234567891', '2023-02-20'),
('Candra Wijaya', 'candra.w@example.com', '081234567892', '2023-03-10'),
('Dewi Anggraini', 'dewi.a@example.com', '081234567893', '2023-04-05'),
('Eko Prasetyo', 'eko.p@example.com', '081234567894', '2023-05-12');

INSERT INTO profil_pelanggan (id_pelanggan, user_id_game, server_game, catatan) VALUES
(1, '12345678', '2109', 'Akun utama'),
(2, '87654321', '2234', 'Akun smurf'),
(3, '11223344', '5067', NULL),
(4, '55667788', '8899', 'Player pro'),
(5, '99887766', '1122', 'Untuk turnamen');

INSERT INTO game (nama_game, publisher) VALUES
('Mobile Legends', 'Moonton'),
('Free Fire', 'Garena'),
('Genshin Impact', 'HoYoverse'),
('PUBG Mobile', 'Tencent Games'),
('Valorant', 'Riot Games');

INSERT INTO produk (id_game, nama_produk, jumlah_diamond, harga) VALUES
(1, '172 Diamonds', 172, 50000.00),
(1, '257 Diamonds', 257, 75000.00),
(2, '140 Diamonds', 140, 25000.00),
(3, '60 Genesis Crystals', 60, 16000.00),
(1, '1000 Diamonds', 1000, 250000.00),
(2, '720 Diamonds', 720, 100000.00);

INSERT INTO transaksi (id_pelanggan, metode_pembayaran, status_pembayaran) VALUES
(1, 'Gopay', 'Selesai'),
(2, 'OVO', 'Selesai'),
(1, 'Dana', 'Pending'),
(3, 'Bank Transfer', 'Selesai'),
(4, 'Gopay', 'Gagal');

INSERT INTO detail_transaksi (id_transaksi, id_produk, jumlah, subtotal) VALUES
(1, 1, 1, 50000.00),
(1, 3, 2, 50000.00),
(2, 2, 1, 75000.00),
(3, 5, 1, 250000.00),
(4, 4, 1, 16000.00);

d. Screenshot Tabel dan Data

Tabel: pelanggan

sql
SELECT * FROM pelanggan;

Tabel: game

sql
SELECT * FROM game;

Tabel: produk

sql
SELECT * FROM produk;

Tabel: transaksi

sql
SELECT * FROM transaksi;

Tabel: detail_transaksi

sql
SELECT * FROM detail_transaksi;

2. PERINTAH-PERINTAH SQL LANJUTAN

a. Function

1. Function Tanpa Parameter

Penjelasan: Function HitungTotalGame dibuat untuk menghitung dan mengembalikan jumlah total game yang terdaftar di tabel game.

Code:

sql
DELIMITER $$
CREATE FUNCTION HitungTotalGame()
RETURNS INT
DETERMINISTIC
BEGIN
    DECLARE total INT;
    SELECT COUNT(*) INTO total FROM game;
    RETURN total;
END$$
DELIMITER ;

Eksekusi:

sql
SELECT HitungTotalGame();

2. Function dengan 2 Parameter

Penjelasan: Function CariTotalBelanjaPelanggan dibuat untuk menghitung total belanja seorang pelanggan (id_p) untuk transaksi dengan status tertentu (status_p).

Code:

sql
DELIMITER $$
CREATE FUNCTION CariTotalBelanjaPelanggan(id_p INT, status_p VARCHAR(20))
RETURNS DECIMAL(12, 2)
READS SQL DATA
BEGIN
    DECLARE total_belanja DECIMAL(12, 2);
    SELECT SUM(dt.subtotal) INTO total_belanja
    FROM transaksi t
    JOIN detail_transaksi dt ON t.id_transaksi = dt.id_transaksi
    WHERE t.id_pelanggan = id_p AND t.status_pembayaran = status_p;
    RETURN IFNULL(total_belanja, 0);
END$$
DELIMITER ;

Eksekusi:

sql
SELECT CariTotalBelanjaPelanggan(1, 'Selesai');

3. Tampilkan Daftar Function

Code:

sql
SHOW FUNCTION STATUS WHERE Db = 'abc_topup_db';

b. Procedure

1. Procedure Tanpa Parameter

Penjelasan: Procedure TampilkanProdukTermahal akan mencari dan menampilkan detail produk dengan harga paling tinggi.

Code:

sql
DELIMITER $$
CREATE PROCEDURE TampilkanProdukTermahal()
BEGIN
    DECLARE max_harga DECIMAL(10, 2);
    SELECT MAX(harga) INTO max_harga FROM produk;

    IF max_harga IS NOT NULL THEN
        SELECT * FROM produk WHERE harga = max_harga;
    ELSE
        SELECT 'Tidak ada data produk.' AS Info;
    END IF;
END$$
DELIMITER ;

Eksekusi:

sql
CALL TampilkanProdukTermahal();

2. Procedure dengan 2 Parameter

Penjelasan: Procedure UpdateStatusTransaksiLoop akan mengupdate status transaksi dari id_awal hingga id_akhir.

Code:

sql
DELIMITER $$
CREATE PROCEDURE UpdateStatusTransaksiLoop(
    IN id_awal INT,
    IN id_akhir INT,
    IN status_baru VARCHAR(20)
)
BEGIN
    DECLARE current_id INT;
    SET current_id = id_awal;

    update_loop: LOOP
        IF current_id > id_akhir THEN
            LEAVE update_loop;
        END IF;

        UPDATE transaksi
        SET status_pembayaran = status_baru
        WHERE id_transaksi = current_id;

        SET current_id = current_id + 1;
    END LOOP update_loop;

    SELECT 'Update status selesai.' AS Keterangan;
END$$
DELIMITER ;

Eksekusi:

sql
CALL UpdateStatusTransaksiLoop(3, 3, 'Selesai');

3. Tampilkan Daftar Procedure

Code:

sql
SHOW PROCEDURE STATUS WHERE Db = 'abc_topup_db';

c. Trigger

1. BEFORE INSERT

Penjelasan: Trigger trg_before_pelanggan_insert akan memvalidasi email dan mengubah nama menjadi huruf besar sebelum data pelanggan baru dimasukkan.

Code:

sql
DELIMITER $$
CREATE TRIGGER trg_before_pelanggan_insert
BEFORE INSERT ON pelanggan
FOR EACH ROW
BEGIN
    IF NEW.email NOT LIKE '%@%.%' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Format email tidak valid.';
    END IF;
    SET NEW.nama_pelanggan = UPPER(NEW.nama_pelanggan);
END$$
DELIMITER ;

Eksekusi:

sql
INSERT INTO pelanggan(nama_pelanggan, email, no_hp, tanggal_daftar) 
VALUES ('Fajar Nugraha', 'fajar.n@example.com', '08122334455', '2023-11-11');

2. AFTER INSERT

Penjelasan: Trigger trg_after_transaksi_insert akan mencatat setiap transaksi baru ke audit_log.

Code:

sql
DELIMITER $$
CREATE TRIGGER trg_after_transaksi_insert
AFTER INSERT ON transaksi
FOR EACH ROW
BEGIN
    INSERT INTO audit_log(nama_tabel, aksi, data_baru, pengguna)
    VALUES ('transaksi', 'INSERT',
            CONCAT('ID: ', NEW.id_transaksi, ', Pelanggan ID: ', NEW.id_pelanggan, ', Status: ', NEW.status_pembayaran),
            USER());
END$$
DELIMITER ;

Eksekusi:

sql
INSERT INTO transaksi(id_pelanggan, metode_pembayaran, status_pembayaran) 
VALUES (5, 'Dana', 'Pending');

3. BEFORE UPDATE

Penjelasan: Trigger trg_before_produk_update mencegah harga produk menjadi negatif.

Code:

sql
DELIMITER $$
CREATE TRIGGER trg_before_produk_update
BEFORE UPDATE ON produk
FOR EACH ROW
BEGIN
    IF NEW.harga < 0 THEN
        SET NEW.harga = 0;
    END IF;
END$$
DELIMITER ;

Eksekusi:

sql
UPDATE produk SET harga = -5000 WHERE id_produk = 3;

4. AFTER UPDATE

Penjelasan: Trigger trg_after_produk_update mencatat perubahan harga ke audit_log.

Code:

sql
DELIMITER $$
CREATE TRIGGER trg_after_produk_update
AFTER UPDATE ON produk
FOR EACH ROW
BEGIN
    IF OLD.harga <> NEW.harga THEN
        INSERT INTO audit_log(nama_tabel, aksi, data_lama, data_baru, pengguna)
        VALUES ('produk', 'UPDATE HARGA',
                CONCAT('ID: ', OLD.id_produk, ', Harga Lama: ', OLD.harga),
                CONCAT('ID: ', NEW.id_produk, ', Harga Baru: ', NEW.harga),
                USER());
    END IF;
END$$
DELIMITER ;

Eksekusi:

sql
UPDATE produk SET harga = 30000.00 WHERE id_produk = 3;

5. BEFORE DELETE

Penjelasan: Trigger trg_before_game_delete mencegah game dihapus jika masih ada produk terkait.

Code:

sql
DELIMITER $$
CREATE TRIGGER trg_before_game_delete
BEFORE DELETE ON game
FOR EACH ROW
BEGIN
    DECLARE product_count INT;
    SELECT COUNT(*) INTO product_count FROM produk WHERE id_game = OLD.id_game;
    IF product_count > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Tidak dapat menghapus game yang masih memiliki produk terkait.';
    END IF;
END$$
DELIMITER ;

Eksekusi:

sql
DELETE FROM game WHERE id_game = 1;

6. AFTER DELETE

Penjelasan: Trigger trg_after_pelanggan_delete mencatat data pelanggan yang dihapus ke audit_log.

Code:

sql
DELIMITER $$
CREATE TRIGGER trg_after_pelanggan_delete
AFTER DELETE ON pelanggan
FOR EACH ROW
BEGIN
    INSERT INTO audit_log(nama_tabel, aksi, data_lama, pengguna)
    VALUES ('pelanggan', 'DELETE',
            CONCAT('ID: ', OLD.id_pelanggan, ', Nama: ', OLD.nama_pelanggan, ', Email: ', OLD.email),
            USER());
END$$
DELIMITER ;

Eksekusi:

sql
DELETE FROM pelanggan WHERE id_pelanggan = 5;

7. Tampilkan Daftar Trigger

Code:

sql
SHOW TRIGGERS FROM abc_topup_db;

d. Index

1. Membuat Index saat Membuat Tabel Baru

Code:

sql
CREATE TABLE pembayaran (
    id_pembayaran INT AUTO_INCREMENT PRIMARY KEY,
    id_transaksi INT,
    metode_bayar VARCHAR(50),
    status VARCHAR(20),
    jumlah DECIMAL(10,2),
    INDEX idx_metode_status (metode_bayar, status)
);

2. Membuat Index dengan CREATE INDEX

Code:

sql
CREATE INDEX idx_game_harga ON produk(id_game, harga);

3. Membuat Index dengan ALTER TABLE

Code:

sql
ALTER TABLE pelanggan ADD INDEX idx_nama_hp (nama_pelanggan, no_hp);

4. Tampilkan Daftar Index

Code:

sql
SHOW INDEX FROM pelanggan;

e. View

1. Horizontal View

Code:

sql
CREATE VIEW view_transaksi_sukses AS
SELECT *
FROM transaksi
WHERE status_pembayaran = 'Selesai';

2. Vertical View

Code:

sql
CREATE VIEW view_kontak_pelanggan AS
SELECT nama_pelanggan, email, no_hp
FROM pelanggan;

3. View Inside View dengan WITH CHECK OPTION

Code:

sql
-- View dasar
CREATE VIEW view_produk_ml AS
SELECT id_produk, nama_produk, harga
FROM produk
WHERE id_game = 1;

-- View inside view
CREATE VIEW view_produk_ml_mahal AS
SELECT *
FROM view_produk_ml
WHERE harga > 70000
WITH LOCAL CHECK OPTION;

4. Eksekusi UPDATE dan INSERT via View

Eksekusi INSERT Gagal:

sql
INSERT INTO view_produk_ml_mahal (nama_produk, harga) 
VALUES ('Produk ML Murah', 60000);

Eksekusi UPDATE Berhasil:

sql
UPDATE view_kontak_pelanggan 
SET email = 'budi.santoso.new@example.com' 
WHERE nama_pelanggan = 'BUDI SANTOSO';

5. Tampilkan Daftar View

Code:

sql
SHOW FULL TABLES WHERE Table_type = 'VIEW';

f. Database Security

1. Membuat User Baru

Code:

sql
CREATE USER 'user1'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'user2'@'localhost' IDENTIFIED BY 'password123';
CREATE USER 'user3'@'localhost' IDENTIFIED BY 'password123';

2. Membuat Role Baru

Code:

sql
CREATE ROLE 'finance', 'human_dev', 'warehouse';

3 & 4. Memberikan Privilege ke User

Privilege User 1 (Akses Tabel):

sql
GRANT SELECT, UPDATE(no_hp) ON abc_topup_db.pelanggan TO 'user1'@'localhost';

Penjelasan: user1 hanya bisa melihat data tabel pelanggan dan hanya bisa mengupdate kolom no_hp.

Privilege User 2 (Akses View):

sql
GRANT SELECT ON abc_topup_db.view_transaksi_sukses TO 'user2'@'localhost';

Penjelasan: user2 hanya bisa melihat data dari view view_transaksi_sukses.

5. Memberikan Privilege ke Role

Privilege Role Finance (Akses Procedure):

sql
GRANT EXECUTE ON PROCEDURE abc_topup_db.TampilkanProdukTermahal TO 'finance';

Menetapkan Role ke User:

sql
GRANT 'finance' TO 'user3'@'localhost';
SET DEFAULT ROLE 'finance' FOR 'user3'@'localhost';
FLUSH PRIVILEGES;

6. Eksekusi Pembuktian Privilege

Pembuktian user1:

sql
-- Login sebagai user1
SELECT id_pelanggan, nama_pelanggan, no_hp FROM abc_topup_db.pelanggan; -- BERHASIL
UPDATE abc_topup_db.pelanggan SET no_hp = '0899999999' WHERE id_pelanggan = 1; -- BERHASIL
UPDATE abc_topup_db.pelanggan SET email = 'coba@email.com' WHERE id_pelanggan = 1; -- GAGAL
SELECT * FROM abc_topup_db.game; -- GAGAL

Pembuktian user2:

sql
-- Login sebagai user2
SELECT * FROM abc_topup_db.view_transaksi_sukses; -- BERHASIL
SELECT * FROM abc_topup_db.transaksi; -- GAGAL

Pembuktian user3 (via Role finance):

sql
-- Login sebagai user3
CALL abc_topup_db.TampilkanProdukTermahal(); -- BERHASIL
SELECT * FROM abc_topup_db.produk; -- GAGAL

KESIMPULAN

Proyek basis data "ABC TOPUP" telah berhasil diimplementasikan dengan menerapkan berbagai konsep database lanjutan meliputi Functions, Procedures, Triggers, Index, Views, dan Database Security. Sistem ini mampu mengelola data toko game top up dengan struktur yang terorganisir dan aman, serta dilengkapi dengan fitur-fitur yang mendukung integritas data dan keamanan akses.

Catatan: Dokumen ini merupakan laporan proyek akhir mata kuliah Pemrograman Basis Data dengan studi kasus toko game top up "ABC TOPUP".

Content is user-generated and unverified.
    Laporan Proyek Akhir Pemrograman Basis Data - ABC TOPUP | Claude