Database and SQL Fundamental MySQL 6

🧩 Pertemuan 6 — Review Menyeluruh & Proyek Akhir MySQL

🎯 Tujuan Pembelajaran

Setelah pertemuan ini, kamu diharapkan mampu:

• Mengintegrasikan seluruh konsep SQL MySQL yang telah dipelajari.
• Membangun sistem database mini yang fungsional dari nol.
• Menerapkan backup dan restore database menggunakan mysqldump.
• Menyajikan hasil proyek dan menjelaskan logika query yang digunakan.

---

📘 Review Konsep — Peta Materi

┌─────────────────────────────────────────────────────────────────────┐
│                       SQL MYSQL FUNDAMENTAL                         │
├──────────────┬─────────────────────────────────────────────────────┤
│ PERTEMUAN 1  │ ERD, Entitas, Atribut, Relasi, Kardinalitas          │
│ PERTEMUAN 2  │ DDL, DML, Tipe Data, Constraint (PK, FK, UNIQUE...) │
│ PERTEMUAN 3  │ CRUD (INSERT/SELECT/UPDATE/DELETE), JOIN, VIEW       │
│ PERTEMUAN 4  │ Stored Procedure, Function, Trigger                  │
│ PERTEMUAN 5  │ ORDER BY, GROUP BY, CASE WHEN, INDEX, EXPLAIN        │
│ PERTEMUAN 6  │ Review + Backup/Restore + Proyek Akhir               │
└──────────────┴─────────────────────────────────────────────────────┘

---

📘 Review Cepat: Sintaks Kunci MySQL

DDL

CREATE DATABASE nama_db;
USE nama_db;
CREATE TABLE nama_tabel (
    id INT AUTO_INCREMENT PRIMARY KEY,
    kolom VARCHAR(100) NOT NULL UNIQUE,
    FOREIGN KEY (kolom_fk) REFERENCES tabel_lain(id)
);
ALTER TABLE nama_tabel ADD COLUMN kolom_baru INT DEFAULT 0;
ALTER TABLE nama_tabel MODIFY COLUMN kolom VARCHAR(200);
DROP TABLE nama_tabel;
TRUNCATE TABLE nama_tabel;

DML

INSERT INTO tabel (k1, k2) VALUES (v1, v2), (v3, v4);
SELECT k1, k2 FROM tabel WHERE kondisi ORDER BY k1 LIMIT 10;
UPDATE tabel SET k1 = v1 WHERE kondisi;
DELETE FROM tabel WHERE kondisi;

JOIN

-- INNER JOIN
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id_t1;
-- LEFT JOIN
SELECT * FROM t1 LEFT JOIN t2 ON t1.id = t2.id_t1;
-- Multi-table
SELECT * FROM t1 JOIN t2 ON ... JOIN t3 ON ...;

Stored Procedure & Trigger

DELIMITER //
CREATE PROCEDURE sp_nama(IN p1 INT, OUT p2 VARCHAR(100))
BEGIN
    -- logika
END //
CREATE TRIGGER trg_nama AFTER INSERT ON tabel FOR EACH ROW BEGIN -- END //
DELIMITER ;

CALL sp_nama(1, @hasil);
SELECT @hasil;

GROUP BY + HAVING + ORDER BY

SELECT kolom, COUNT(*), AVG(nilai)
FROM tabel
WHERE kondisi
GROUP BY kolom
HAVING COUNT(*) > 1
ORDER BY kolom DESC;

INDEX

CREATE INDEX idx_nama ON tabel(kolom);
CREATE UNIQUE INDEX idx_uniq ON tabel(kolom);
SHOW INDEX FROM tabel;
EXPLAIN SELECT * FROM tabel WHERE kolom = 'nilai';
DROP INDEX idx_nama ON tabel;

---

📘 Backup & Restore MySQL dengan mysqldump

Backup Database

mysqldump adalah tools bawaan MySQL untuk backup database via command line.

# Backup satu database
mysqldump -u root -p db_perpustakaan > backup_perpustakaan.sql

# Backup database dengan data dan struktur
mysqldump -u root -p --databases db_perpustakaan > backup_lengkap.sql

# Backup semua database
mysqldump -u root -p --all-databases > backup_semua.sql

# Backup hanya struktur (tanpa data)
mysqldump -u root -p --no-data db_perpustakaan > struktur_perpustakaan.sql

# Backup hanya data (tanpa struktur)
mysqldump -u root -p --no-create-info db_perpustakaan > data_perpustakaan.sql

# Backup tabel tertentu saja
mysqldump -u root -p db_perpustakaan buku anggota > backup_partial.sql

Restore Database

# Restore dari file backup
mysql -u root -p db_perpustakaan < backup_perpustakaan.sql

# Jika database belum ada, buat dulu
mysql -u root -p -e "CREATE DATABASE db_perpustakaan_restore;"
mysql -u root -p db_perpustakaan_restore < backup_perpustakaan.sql

Backup dalam SQL (via Script)

-- Lihat query CREATE TABLE saat ini
SHOW CREATE TABLE buku;
SHOW CREATE TABLE anggota;

-- MySQL tidak punya perintah BACKUP langsung seperti SQL Server
-- Gunakan mysqldump dari terminal, atau gunakan phpMyAdmin / MySQL Workbench
-- untuk export/import dengan GUI

-- Alternatif: SELECT INTO OUTFILE (export ke CSV)
SELECT * FROM buku
INTO OUTFILE '/tmp/buku_export.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

-- Import dari CSV
-- LOAD DATA INFILE '/tmp/buku_export.csv'
-- INTO TABLE buku
-- FIELDS TERMINATED BY ','
-- ENCLOSED BY '"'
-- LINES TERMINATED BY '\n';

---

🧑‍💻 LATIHAN 1 — Review CRUD Lanjutan

USE db_perpustakaan;

-- 1. INSERT dengan SELECT (copy data)
-- Buat tabel backup buku
CREATE TABLE buku_arsip LIKE buku;
INSERT INTO buku_arsip SELECT * FROM buku WHERE tahun_terbit < 2000;
SELECT * FROM buku_arsip;

-- 2. UPDATE dengan JOIN
UPDATE buku b
JOIN kategori k ON b.id_kategori = k.id_kategori
SET b.harga = b.harga * 1.10
WHERE k.nama_kat = 'Teknologi';

-- 3. DELETE dengan subquery
-- Hapus log aktivitas yang lebih dari 30 hari lalu
-- DELETE FROM log_aktivitas WHERE waktu < DATE_SUB(NOW(), INTERVAL 30 DAY);

-- 4. SELECT dengan subquery
-- Tampilkan buku yang harganya di atas rata-rata
SELECT judul, harga
FROM buku
WHERE harga > (SELECT AVG(harga) FROM buku)
ORDER BY harga DESC;

-- 5. SELECT dengan EXISTS
-- Anggota yang sudah pernah meminjam minimal satu buku
SELECT nama, email
FROM anggota a
WHERE EXISTS (
    SELECT 1 FROM peminjaman p WHERE p.id_anggota = a.id_anggota
);

-- 6. Multi-level JOIN dengan CASE
SELECT
    a.nama AS anggota,
    COUNT(p.id_pinjam) AS total_pinjam,
    CASE
        WHEN COUNT(p.id_pinjam) >= 5 THEN 'Platinum'
        WHEN COUNT(p.id_pinjam) >= 3 THEN 'Gold'
        WHEN COUNT(p.id_pinjam) >= 1 THEN 'Silver'
        ELSE 'Baru'
    END AS level_member
FROM anggota a
LEFT JOIN peminjaman p ON a.id_anggota = p.id_anggota
GROUP BY a.id_anggota, a.nama
ORDER BY total_pinjam DESC;

---

🧑‍💻 LATIHAN 2 — Review Stored Procedure Lanjutan

DELIMITER //

-- Procedure: laporan peminjaman per periode
CREATE PROCEDURE sp_laporan_periode(
    IN p_dari DATE,
    IN p_sampai DATE
)
BEGIN
    SELECT
        a.nama          AS anggota,
        b.judul         AS buku,
        p.tgl_pinjam,
        p.tgl_kembali,
        p.status,
        DATEDIFF(COALESCE(p.tgl_kembali_aktual, CURDATE()), p.tgl_pinjam) AS lama_hari
    FROM peminjaman p
    JOIN anggota a ON p.id_anggota = a.id_anggota
    JOIN buku b ON p.id_buku = b.id_buku
    WHERE p.tgl_pinjam BETWEEN p_dari AND p_sampai
    ORDER BY p.tgl_pinjam;

    -- Summary
    SELECT
        COUNT(*) AS total_transaksi,
        SUM(CASE WHEN p.status = 'dipinjam' THEN 1 ELSE 0 END) AS masih_dipinjam,
        SUM(CASE WHEN p.status = 'dikembalikan' THEN 1 ELSE 0 END) AS sudah_kembali,
        SUM(CASE WHEN p.status = 'terlambat' THEN 1 ELSE 0 END) AS terlambat
    FROM peminjaman p
    WHERE p.tgl_pinjam BETWEEN p_dari AND p_sampai;
END //

-- Procedure: top N anggota terbanyak pinjam
CREATE PROCEDURE sp_top_anggota(IN p_top INT)
BEGIN
    SELECT
        a.kode_ang,
        a.nama,
        a.email,
        COUNT(p.id_pinjam) AS total_pinjam,
        MAX(p.tgl_pinjam) AS terakhir_pinjam
    FROM anggota a
    JOIN peminjaman p ON a.id_anggota = p.id_anggota
    GROUP BY a.id_anggota, a.kode_ang, a.nama, a.email
    ORDER BY total_pinjam DESC
    LIMIT p_top;
END //

DELIMITER ;

-- Test
CALL sp_laporan_periode('2026-06-01', '2026-06-30');
CALL sp_top_anggota(3);

---

🧑‍💻 LATIHAN 3 — Review Trigger Lanjutan

DELIMITER //

-- Trigger: otomatis update status buku berdasarkan stok
CREATE TRIGGER trg_after_update_stok
AFTER UPDATE ON buku
FOR EACH ROW
BEGIN
    IF NEW.stok = 0 AND OLD.stok > 0 THEN
        INSERT INTO log_aktivitas (tabel_terkait, aksi, id_record, keterangan)
        VALUES ('buku', 'UPDATE', NEW.id_buku,
            CONCAT('PERINGATAN: Stok buku "', NEW.judul, '" telah habis!'));
    END IF;
END //

-- Trigger: validasi email format sebelum insert anggota
CREATE TRIGGER trg_before_insert_anggota
BEFORE INSERT ON anggota
FOR EACH ROW
BEGIN
    IF NEW.email IS NOT NULL AND NEW.email NOT LIKE '%@%.%' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Format email tidak valid!';
    END IF;
END //

DELIMITER ;

-- Test trigger validasi email
-- Ini harus error:
-- INSERT INTO anggota (kode_ang, nama, email) VALUES ('ANG100', 'Test', 'emailsalah');

-- Ini harus berhasil:
INSERT INTO anggota (kode_ang, nama, email)
VALUES ('ANG100', 'Test Anggota', '[email protected]');

-- Update stok jadi 0 untuk test trigger peringatan
UPDATE buku SET stok = 0 WHERE id_buku = 2;
SELECT * FROM log_aktivitas ORDER BY id_log DESC LIMIT 5;

---

🧑‍💻 LATIHAN 4 — Quiz Review (Kerjakan Tanpa Melihat Catatan!)

-- QUIZ 1 (DDL):
-- Buat tabel `rak_buku` dengan kolom:
-- id_rak (PK, auto), kode_rak (UNIQUE, NOT NULL), lokasi (VARCHAR 100), kapasitas (INT, default 50)
-- Tambahkan kolom `keterangan TEXT` ke tabel tersebut
-- Lalu hapus kolom tersebut


-- QUIZ 2 (DML):
-- Masukkan 3 rak buku
-- Update kapasitas rak pertama menjadi 75
-- Hapus rak dengan kapasitas kurang dari 10


-- QUIZ 3 (SELECT lanjutan):
-- Tampilkan nama anggota, judul buku, lama peminjaman, dan denda
-- Untuk yang sudah melewati tanggal kembali dan masih berstatus 'dipinjam'
-- Denda = Rp 1000 per hari keterlambatan


-- QUIZ 4 (GROUP BY):
-- Tampilkan per bulan: jumlah peminjaman, jumlah unik anggota yang meminjam,
-- dan jumlah buku unik yang dipinjam


-- QUIZ 5 (JOIN + CASE):
-- Buat laporan buku dengan kolom:
-- Judul, Penulis, Kategori, Total Dipinjam, Stok Saat Ini,
-- Rekomendasi: 'Beli Lebih' jika total dipinjam > stok, 'Cukup' jika sebaliknya

---

🧠 PROYEK AKHIR — Sistem Database Mini

Deskripsi

Bangun sebuah sistem database mini menggunakan MySQL dengan tema bebas. Proyek ini mengintegrasikan semua konsep yang telah dipelajari selama 6 pertemuan.

---

Pilihan Tema

Pilih salah satu atau ajukan tema sendiri ke instruktur:

Tema	Entitas Minimal
Toko Online	Pelanggan, Produk, Kategori, Pesanan, Detail Pesanan, Pembayaran
Rumah Sakit	Pasien, Dokter, Jadwal, Konsultasi, Obat, Resep
Hotel	Tamu, Kamar, Tipe Kamar, Reservasi, Fasilitas, Pembayaran
Sekolah	Siswa, Guru, Mata Pelajaran, Kelas, Jadwal, Nilai
Bioskop	Film, Studio, Jadwal Tayang, Kursi, Tiket, Pelanggan
Restoran	Menu, Kategori Menu, Meja, Pesanan, Detail Pesanan, Kasir

---

Kriteria Proyek (Wajib)

1. Desain Database (ERD + DDL)

-- a. Buat ERD terlebih dahulu (gambar di draw.io / MySQL Workbench / tulis tangan)
-- b. Minimal 5 tabel yang saling terhubung
-- c. Gunakan constraint lengkap: PK, FK, UNIQUE, NOT NULL, DEFAULT, CHECK
-- d. Gunakan minimal 5 tipe data berbeda

-- Contoh skeleton untuk tema Toko Online:
CREATE DATABASE db_toko_final;
USE db_toko_final;

CREATE TABLE kategori_produk (
    -- ...
);

CREATE TABLE produk (
    -- ...
    FOREIGN KEY (...) REFERENCES kategori_produk(...)
);

-- dst...

2. Data Dummy (DML — INSERT)

-- Minimal 10 data per tabel
-- Data harus konsisten (FK harus merujuk ke data yang benar)
-- Gunakan INSERT beberapa baris sekaligus

3. Query SELECT Kompleks (min. 10 query)

-- Wajib mencakup:
-- a. SELECT dengan WHERE + multiple conditions (AND/OR/IN/BETWEEN/LIKE)
-- b. INNER JOIN minimal 3 tabel
-- c. LEFT JOIN untuk menemukan data yang tidak berelasi
-- d. GROUP BY + HAVING
-- e. ORDER BY multi-kolom
-- f. Subquery (minimal 1)
-- g. CASE WHEN dalam SELECT
-- h. Aggregate functions (COUNT, SUM, AVG, MAX, MIN)

4. Stored Procedure (min. 3)

-- Contoh untuk tema Toko Online:

-- a. sp_proses_pesanan(IN id_pelanggan, IN id_produk, IN jumlah, OUT total, OUT pesan)
--    - Cek stok produk
--    - Kurangi stok jika tersedia
--    - Buat record pesanan
--    - Return total harga dan pesan sukses/gagal

-- b. sp_laporan_penjualan(IN dari_tgl DATE, IN sampai_tgl DATE)
--    - Tampilkan semua transaksi dalam periode
--    - Summary: total transaksi, total pendapatan, produk terlaris

-- c. sp_top_pelanggan(IN top_n INT)
--    - Tampilkan N pelanggan dengan pembelian terbanyak

5. Trigger (min. 2)

-- Contoh:
-- a. trg_kurangi_stok: otomatis kurangi stok saat pesanan baru masuk
-- b. trg_log_perubahan: catat setiap perubahan harga produk ke tabel log
-- c. trg_validasi_stok: tolak pesanan jika stok tidak cukup (BEFORE INSERT)

6. View (min. 2)

-- Contoh:
-- a. v_laporan_lengkap: gabungan semua tabel untuk laporan transaksi
-- b. v_stok_produk: status stok setiap produk beserta keterangannya

7. Index (min. 3)

-- Buat index pada kolom yang sering digunakan di WHERE dan JOIN
-- Gunakan EXPLAIN untuk membuktikan efektivitas index

8. Backup Database

# Jalankan dari terminal, sertakan hasilnya dalam laporan
mysqldump -u root -p db_toko_final > backup_db_toko_final_2026.sql

---

Format Pengumpulan

Proyek_Akhir_NamaKamu/
├── ERD_NamaKamu.png          (gambar ERD)
├── db_NamaKamu.sql            (script SQL lengkap)
├── backup_NamaKamu.sql        (hasil mysqldump)
└── Laporan_NamaKamu.pdf       (opsional, berisi penjelasan)

Script SQL harus terstruktur dengan komentar yang jelas:

-- ============================================================
-- PROYEK AKHIR DATABASE MYSQL
-- Nama  : [Nama Kamu]
-- Tema  : [Tema yang dipilih]
-- Tanggal: [Tanggal]
-- ============================================================

-- =====================
-- BAGIAN 1: DDL (CREATE)
-- =====================

-- =====================
-- BAGIAN 2: DML (INSERT)
-- =====================

-- =====================
-- BAGIAN 3: QUERY SELECT
-- =====================

-- =====================
-- BAGIAN 4: STORED PROCEDURE
-- =====================

-- =====================
-- BAGIAN 5: TRIGGER
-- =====================

-- =====================
-- BAGIAN 6: VIEW
-- =====================

-- =====================
-- BAGIAN 7: INDEX
-- =====================

---

Rubrik Penilaian

Aspek	Bobot	Kriteria
ERD & Desain	15%	Entitas lengkap, relasi benar, kardinalitas tepat
DDL & Constraint	15%	Semua constraint diterapkan dengan benar
Data & DML	10%	Data konsisten, jumlah cukup, beragam
Query SELECT	20%	Kompleks, menggunakan JOIN/GROUP BY/CASE/Subquery
Stored Procedure	15%	Fungsional, ada parameter, ada logika kondisional
Trigger	10%	Otomatis berjalan, logika benar
Index & Optimasi	10%	Index relevan, ada bukti EXPLAIN
Kerapian & Komentar	5%	Script mudah dibaca, komentar jelas

---

🏆 Tantangan Opsional (Nilai Tambah)

-- 1. TRANSACTION: gunakan COMMIT dan ROLLBACK untuk operasi yang memerlukan atomicity
START TRANSACTION;
-- Operasi 1
-- Operasi 2
-- Jika ada error: ROLLBACK;
-- Jika sukses: COMMIT;

-- 2. FUNCTION KUSTOM: buat fungsi sendiri (bukan procedure)
DELIMITER //
CREATE FUNCTION fn_format_rupiah(jumlah DECIMAL(15,2))
RETURNS VARCHAR(50) DETERMINISTIC
BEGIN
    RETURN CONCAT('Rp ', FORMAT(jumlah, 0, 'id_ID'));
END //
DELIMITER ;

-- Test
SELECT fn_format_rupiah(1500000); -- Output: Rp 1.500.000

-- 3. SUBQUERY LANJUTAN
-- Tampilkan produk yang lebih mahal dari rata-rata harga di kategorinya
SELECT p.nama, p.harga, k.nama_kat
FROM produk p
JOIN kategori_produk k ON p.id_kategori = k.id_kategori
WHERE p.harga > (
    SELECT AVG(p2.harga)
    FROM produk p2
    WHERE p2.id_kategori = p.id_kategori
);

---

🔍 Refleksi Akhir

Jawab pertanyaan berikut sebagai penutup course:

1. Konsep SQL apa yang menurut kamu paling sulit? Mengapa?
2. Kapan kamu memilih menggunakan Stored Procedure dibanding menjalankan query langsung dari aplikasi?
3. Apa 3 hal yang harus diperhatikan agar database berjalan efisien?
4. Mengapa backup database itu penting? Seberapa sering idealnya dilakukan?
5. Apa perbedaan utama antara MySQL dan SQL Server yang kamu rasakan?

---

📚 Referensi Tambahan

• MySQL Official Documentation: https://dev.mysql.com/doc/refman/8.0/en/
• MySQL Tutorial: https://www.mysqltutorial.org/
• W3Schools MySQL: https://www.w3schools.com/mysql/
• SQLZoo Practice: https://sqlzoo.net/
• MySQL Workbench (GUI): https://www.mysql.com/products/workbench/
• draw.io untuk ERD: https://app.diagrams.net/