Inilah bagian di mana database perpustakaan benar-benar bernyawa: buku keluar dari rak, berpindah tangan, lalu kembali. Alur pinjam-kembali menuntut dua tabel berubah serentak — peminjaman mencatat transaksinya sementara buku mengubah stoknya — dan di situlah transaksi MySQL menunjukkan fungsinya: kedua perubahan harus terjadi bersama-sama atau tidak sama sekali. Pada bagian ini kita jalankan alur lengkapnya, batalkan satu kesalahan dengan ROLLBACK, lalu sengaja coba meminjam melebihi stok untuk melihat CHECK constraint menahan garis batas terakhir.

Menyiapkan Ulang Database #

Sesuai kebiasaan proyek ini, kita mulai dari keadaan bersih: skema dan data awal dibangun ulang sehingga angka-angka pada bagian ini dapat diikuti ulang kapan pun. Blok pertama dan kedua berikut menjalankan pernyataan yang sama dengan database/skema.sql dan database/data-awal.sql:

sql 
DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);

CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);

CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);

CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

Output yang Dihasilkan:

text 
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
sql 
INSERT INTO buku (kode_buku, judul, penulis, tahun, stok) VALUES
  ('BK-001', 'Laskar Pelangi', 'Andrea Hirata', 2005, 3),
  ('BK-002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 1980, 2),
  ('BK-003', 'Hujan Bulan Juni', 'Sapardi Djoko Damono', 1994, 2),
  ('BK-004', 'Dilan 1990', 'Pidi Baiq', 2014, 4),
  ('BK-005', 'Atomic Habits', 'James Clear', 2018, 2),
  ('BK-006', 'Matematika untuk SMA Kelas X', 'Tim Pusat Kurikulum', 2019, 6),
  ('BK-007', 'Kamus Besar Bahasa Indonesia', 'Badan Pengembangan Bahasa', 2016, 2),
  ('BK-008', 'Fisika Terapan untuk SMK', 'Ratna Wijaya', 2021, 3);

INSERT INTO anggota (nama, kelas, telepon) VALUES
  ('Budi Santoso', 'X-IPA-1', '081234560001'),
  ('Siti Rahmawati', 'X-IPA-2', '081234560002'),
  ('Andi Wijaya', 'XI-IPA-1', '081234560003'),
  ('Dewi Lestari', 'XI-IPS-1', '081234560004'),
  ('Rina Marlina', 'XII-IPA-1', '081234560005'),
  ('Tono Sutrisno', 'XII-IPS-1', NULL),
  ('Sari Puspita', 'X-IPS-1', '081234560007'),
  ('Joko Prasetyo', 'XI-IPS-2', NULL);

Output yang Dihasilkan:

text 
Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Alur Peminjaman #

Satu peminjaman menyentuh tiga tabel sekaligus: peminjaman mendapat baris baru, detail_peminjaman mendapat rincian judulnya, dan buku kehilangan eksemplar. Diagram berikut memetakan urutannya:

Siti Rahmawati (anggota id 2) meminjam dua judul pada 2 Maret 2026: dua eksemplar Dilan 1990 dan satu eksemplar Laskar Pelangi, dengan batas kembali seminggu kemudian. Keempat pernyataan berikut berjalan sebagai satu unit kerja:

sql 
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (2, '2026-03-02', '2026-03-09');
INSERT INTO detail_peminjaman (peminjaman_id, buku_kode, jumlah) VALUES
  (LAST_INSERT_ID(), 'BK-004', 2),
  (LAST_INSERT_ID(), 'BK-001', 1);
UPDATE buku SET stok = stok - 2 WHERE kode_buku = 'BK-004';
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-001';
COMMIT;

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Mari kita bedah alur di atas:

  1. START TRANSACTION membuka unit kerja: sejak titik ini, perubahan hanya terlihat oleh sesi kita dan belum permanen.
  2. INSERT INTO peminjaman membuat baris transaksi untuk Siti. Kolom id terisi otomatis oleh AUTO_INCREMENT — bernilai 1 karena tabel baru dibangun ulang, dan tanggal_kembali sengaja tidak diisi sehingga tetap NULL (masih dipinjam).
  3. LAST_INSERT_ID() mengambil id yang barusan dihasilkan pernyataan kedua, sehingga dua baris detail_peminjaman terhubung ke peminjaman yang tepat tanpa perlu menuliskan angka manual.
  4. Dua UPDATE stok menjumlahkan pengurangan sesuai jumlah eksemplar tiap judul — pola stok = stok - N menjaga kebenaran walau stok berubah beberapa kali.
  5. COMMIT mengukuhkan seluruh perubahan sekaligus.

Bukti setelah transaksi kita kumpulkan dalam satu blok pemeriksaan:

sql 
SELECT p.id, a.nama, p.tanggal_pinjam, p.tanggal_harus_kembali, p.tanggal_kembali
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
ORDER BY p.id;
SELECT dp.peminjaman_id, dp.buku_kode, b.judul, dp.jumlah
FROM detail_peminjaman dp
JOIN buku b ON b.kode_buku = dp.buku_kode
ORDER BY dp.peminjaman_id, dp.buku_kode;
SELECT kode_buku, judul, stok FROM buku
WHERE kode_buku IN ('BK-001', 'BK-004')
ORDER BY kode_buku;

Output yang Dihasilkan:

text 
+----+----------------+----------------+-----------------------+-----------------+
| id | nama           | tanggal_pinjam | tanggal_harus_kembali | tanggal_kembali |
+----+----------------+----------------+-----------------------+-----------------+
|  1 | Siti Rahmawati | 2026-03-02     | 2026-03-09            | NULL            |
+----+----------------+----------------+-----------------------+-----------------+
1 row in set (0.00 sec)

+---------------+-----------+----------------+--------+
| peminjaman_id | buku_kode | judul          | jumlah |
+---------------+-----------+----------------+--------+
|             1 | BK-001    | Laskar Pelangi |      1 |
|             1 | BK-004    | Dilan 1990     |      2 |
+---------------+-----------+----------------+--------+
2 rows in set (0.00 sec)

+-----------+----------------+------+
| kode_buku | judul          | stok |
+-----------+----------------+------+
| BK-001    | Laskar Pelangi |    2 |
| BK-004    | Dilan 1990     |    2 |
+-----------+----------------+------+
2 rows in set (0.00 sec)

Hasilnya konsisten satu sama lain: peminjaman 1 milik Siti dengan tanggal_kembali masih NULL, dua baris rincian berisi BK-004 sebanyak dua dan BK-001 sebanyak satu, serta stok yang turun dari 4 ke 2 untuk Dilan 1990 dan dari 3 ke 2 untuk Laskar Pelangi. Satu angka tidak bisa berbohong tanpa angka lain ikut berubah — itulah konsistensi yang dijaga transaksi.

Peminjaman kedua menyusul tiga hari kemudian: Andi Wijaya (id 3) meminjam satu Atomic Habits. Polanya sama persis, cukup ringkas:

sql 
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (3, '2026-03-05', '2026-03-12');
INSERT INTO detail_peminjaman (peminjaman_id, buku_kode, jumlah)
  VALUES (LAST_INSERT_ID(), 'BK-005', 1);
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-005';
COMMIT;
SELECT kode_buku, stok FROM buku WHERE kode_buku = 'BK-005';

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

+-----------+------+
| kode_buku | stok |
+-----------+------+
| BK-005    |    1 |
+-----------+------+
1 row in set (0.00 sec)

Stok Atomic Habits turun dari 2 menjadi 1, dan peminjaman Andi mendapat id 2 dari AUTO_INCREMENT.

Alur Pengembalian #

Pengembalian adalah cermin dari peminjaman: tanggal_kembali diisi dan stok bertambah kembali. Siti mengembalikan kedua bukunya pada 7 Maret 2026:

sql 
START TRANSACTION;
UPDATE peminjaman SET tanggal_kembali = '2026-03-07'
WHERE id = 1 AND tanggal_kembali IS NULL;
UPDATE buku SET stok = stok + 2 WHERE kode_buku = 'BK-004';
UPDATE buku SET stok = stok + 1 WHERE kode_buku = 'BK-001';
COMMIT;
SELECT id, tanggal_pinjam, tanggal_harus_kembali, tanggal_kembali
FROM peminjaman ORDER BY id;
SELECT kode_buku, judul, stok FROM buku
WHERE kode_buku IN ('BK-001', 'BK-004')
ORDER BY kode_buku;

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

+----+----------------+-----------------------+-----------------+
| id | tanggal_pinjam | tanggal_harus_kembali | tanggal_kembali |
+----+----------------+-----------------------+-----------------+
|  1 | 2026-03-02     | 2026-03-09            | 2026-03-07      |
|  2 | 2026-03-05     | 2026-03-12            | NULL            |
+----+----------------+-----------------------+-----------------+
2 rows in set (0.00 sec)

+-----------+----------------+------+
| kode_buku | judul          | stok |
+-----------+----------------+------+
| BK-001    | Laskar Pelangi |    3 |
| BK-004    | Dilan 1990     |    4 |
+-----------+----------------+------+
2 rows in set (0.00 sec)

Peminjaman 1 kini menampilkan tanggal_kembali = 2026-03-07 (tercatat sehari sebelum batas waktu), dan stok kedua judul kembali ke angka awal: Dilan 1990 menjadi 4, Laskar Pelangi menjadi 3. Syarat tambahan AND tanggal_kembali IS NULL pada UPDATE pertama bukan hiasan — syarat itu membuat pengembalian bersifat idempoten: bila petugas tidak sengaja menjalankan blok ini dua kali, pemanggilan kedua menemukan Rows matched: 0 dan tidak menambah stok dua kali.

Membatalkan Perubahan dengan ROLLBACK #

Bagaimana bila petugas salah memasukkan tanggal kembali? Sebelum COMMIT terjadi, kesalahan masih bisa dibatalkan seluruhnya. Blok berikut sengaja mengubah tanggal kembali peminjaman Andi lalu membatalkannya:

sql 
START TRANSACTION;
UPDATE peminjaman SET tanggal_kembali = '2026-03-08' WHERE id = 2;
SELECT id, tanggal_kembali FROM peminjaman WHERE id = 2;
ROLLBACK;
SELECT id, tanggal_kembali FROM peminjaman WHERE id = 2;

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

+----+-----------------+
| id | tanggal_kembali |
+----+-----------------+
|  2 | 2026-03-08      |
+----+-----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

+----+-----------------+
| id | tanggal_kembali |
+----+-----------------+
|  2 | NULL            |
+----+-----------------+
1 row in set (0.00 sec)

Dua SELECT di dalam satu blok yang sama memperlihatkan dua keadaan berbeda. SELECT pertama — yang berjalan di dalam transaksi yang belum ditutup — melihat 2026-03-08 karena sesi kita membaca perubahan miliknya sendiri. SELECT kedua, setelah ROLLBACK, melihat NULL lagi: seluruh efek UPDATE dihapus seolah tidak pernah ditulis. Perubahan di luar transaksi (peminjaman yang sudah di-COMMIT sebelumnya) tidak tersentuh sama sekali.

Menolak Peminjaman Melebihi Stok #

Skenario penutup: seorang siswa ingin meminjam lima Atomic Habits, padahal stok tersisa satu. Kode aplikasi semestinya menolak lebih dulu, tetapi bila aplikasi punya bug, CHECK constraint menjadi benteng terakhir yang menahan data merusak masuk:

sql 
-- @error
UPDATE buku SET stok = stok - 5 WHERE kode_buku = 'BK-005';

Output yang Dihasilkan:

text 
Query OK, 0 rows affected (0.00 sec)

ERROR 3819 (HY000): Check constraint 'chk_buku_stok' is violated.
Bye

MySQL menolak UPDATE tersebut dengan error 3819: constraint chk_buku_stok yang kita pasang di Bagian 1 menolak stok negatif. Perhatikan bahwa pernyataan ini berdiri sendiri di luar transaksi — penolakan terjadi sebelum satu baris pun berubah, sehingga stok tetap 1 dan peminjaman bermasalah tidak pernah tercatat.

Referensi #

Sirkulasi pinjam-kembali kini berjalan dengan jaminan konsistensi: transaksi menjaga kesatuan perubahan, guard menjaga idempotensi, dan CHECK menjaga batas stok. Satu hal yang belum kita miliki adalah pandangan menyeluruh — pada Bagian 4 kita menyusun laporan dari dua bulan riwayat: buku terpopuler, anggota teraktif, dan peminjaman yang melewati batas waktu.

Berkas Sumber

Berkas pendukung halaman ini dapat diunduh dan dijalankan langsung sesuai penjelasan di atas.

database/data-awal.sql

Unduh berkas
sql 
-- data-awal.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: mengisi data contoh deterministik (buku dan anggota fiktif)
--   sebagai titik awal konsisten untuk seluruh bagian proyek.
-- Cara pakai: jalankan setelah skema.sql:
--   mysql -u root -p perpustakaan_db < database/data-awal.sql

INSERT INTO buku (kode_buku, judul, penulis, tahun, stok) VALUES
  ('BK-001', 'Laskar Pelangi', 'Andrea Hirata', 2005, 3),
  ('BK-002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 1980, 2),
  ('BK-003', 'Hujan Bulan Juni', 'Sapardi Djoko Damono', 1994, 2),
  ('BK-004', 'Dilan 1990', 'Pidi Baiq', 2014, 4),
  ('BK-005', 'Atomic Habits', 'James Clear', 2018, 2),
  ('BK-006', 'Matematika untuk SMA Kelas X', 'Tim Pusat Kurikulum', 2019, 6),
  ('BK-007', 'Kamus Besar Bahasa Indonesia', 'Badan Pengembangan Bahasa', 2016, 2),
  ('BK-008', 'Fisika Terapan untuk SMK', 'Ratna Wijaya', 2021, 3);

INSERT INTO anggota (nama, kelas, telepon) VALUES
  ('Budi Santoso', 'X-IPA-1', '081234560001'),
  ('Siti Rahmawati', 'X-IPA-2', '081234560002'),
  ('Andi Wijaya', 'XI-IPA-1', '081234560003'),
  ('Dewi Lestari', 'XI-IPS-1', '081234560004'),
  ('Rina Marlina', 'XII-IPA-1', '081234560005'),
  ('Tono Sutrisno', 'XII-IPS-1', NULL),
  ('Sari Puspita', 'X-IPS-1', '081234560007'),
  ('Joko Prasetyo', 'XI-IPS-2', NULL);

database/skema.sql

Unduh berkas
sql 
-- skema.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: membuat database perpustakaan_db beserta keempat tabelnya
--   (buku, anggota, peminjaman, detail_peminjaman) lengkap dengan
--   primary key, foreign key, dan check constraint.
-- Cara pakai: mysql -u root -p < database/skema.sql

DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

-- Katalog buku; kolom stok menyimpan jumlah eksemplar yang tersedia di rak.
CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);

-- Anggota perpustakaan (siswa); telepon boleh kosong (NULL).
CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);

-- Satu baris per transaksi peminjaman; tanggal_kembali NULL berarti masih dipinjam.
CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);

-- Rincian buku per peminjaman; jembatan banyak-ke-banyak antara buku dan peminjaman.
CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

queries/laporan-anggota-aktif.sql

Unduh berkas
sql 
-- laporan-anggota-aktif.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menampilkan frekuensi peminjaman dan total buku yang dipinjam
--   tiap anggota, termasuk anggota yang belum pernah meminjam.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-anggota-aktif.sql

SELECT a.id, a.nama, a.kelas,
       COUNT(DISTINCT p.id) AS jumlah_peminjaman,
       COALESCE(SUM(dp.jumlah), 0) AS total_buku
FROM anggota a
LEFT JOIN peminjaman p ON p.anggota_id = a.id
LEFT JOIN detail_peminjaman dp ON dp.peminjaman_id = p.id
GROUP BY a.id, a.nama, a.kelas
ORDER BY jumlah_peminjaman DESC, a.nama;

queries/laporan-buku-terpopuler.sql

Unduh berkas
sql 
-- laporan-buku-terpopuler.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: memeringkat buku berdasarkan total eksemplar yang dipinjam,
--   termasuk buku yang belum pernah dipinjam, memakai window function RANK().
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-buku-terpopuler.sql

SELECT b.kode_buku, b.judul,
       COALESCE(SUM(dp.jumlah), 0) AS total_dipinjam,
       RANK() OVER (ORDER BY COALESCE(SUM(dp.jumlah), 0) DESC) AS peringkat
FROM buku b
LEFT JOIN detail_peminjaman dp ON dp.buku_kode = b.kode_buku
GROUP BY b.kode_buku, b.judul
ORDER BY total_dipinjam DESC, b.judul;

queries/laporan-terlambat.sql

Unduh berkas
sql 
-- laporan-terlambat.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menemukan peminjaman yang melewati batas tanggal_harus_kembali
--   dan belum dikembalikan, dihitung terhadap tanggal acuan literal.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-terlambat.sql
-- Ganti tanggal acuan '2026-02-28' sesuai hari laporan dijalankan.

SELECT p.id, a.nama, a.kelas, p.tanggal_pinjam, p.tanggal_harus_kembali,
       DATEDIFF('2026-02-28', p.tanggal_harus_kembali) AS hari_terlambat
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
  AND p.tanggal_harus_kembali < '2026-02-28'
ORDER BY hari_terlambat DESC;

queries/stok-tersedia.sql

Unduh berkas
sql 
-- stok-tersedia.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menampilkan stok tiap judul berdampingan dengan jumlah eksemplar
--   yang sedang dipinjam, sehingga kecukupan koleksi terlihat sekali baca.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/stok-tersedia.sql

SELECT b.kode_buku, b.judul, b.stok AS stok_tersedia,
       COALESCE(dipinjam.sedang, 0) AS sedang_dipinjam
FROM buku b
LEFT JOIN (
  SELECT dp.buku_kode, SUM(dp.jumlah) AS sedang
  FROM detail_peminjaman dp
  JOIN peminjaman p ON p.id = dp.peminjaman_id
  WHERE p.tanggal_kembali IS NULL
  GROUP BY dp.buku_kode
) dipinjam ON dipinjam.buku_kode = b.kode_buku
ORDER BY b.kode_buku;

tests/pemeriksaan.sql

Unduh berkas
sql 
-- pemeriksaan.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: pemeriksaan akhir seluruh fitur proyek (skema, peminjaman,
--   pengembalian, laporan terpopuler, dan deteksi keterlambatan).
--   Berkas ini MANDIRI: membangun skenario uji kecilnya sendiri,
--   lalu membersihkan database setelah selesai.
-- Cara pakai: mysql -u root -p < tests/pemeriksaan.sql
-- Ekspektasi tiap hasil ditulis sebagai komentar di atas query terkait.

DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);
CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);
CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);
CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

INSERT INTO buku (kode_buku, judul, penulis, tahun, stok) VALUES
  ('BK-001', 'Laskar Pelangi', 'Andrea Hirata', 2005, 3),
  ('BK-002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 1980, 2),
  ('BK-003', 'Hujan Bulan Juni', 'Sapardi Djoko Damono', 1994, 2);
INSERT INTO anggota (nama, kelas, telepon) VALUES
  ('Budi Santoso', 'X-IPA-1', '081234560001'),
  ('Siti Rahmawati', 'X-IPA-2', '081234560002');

-- Pemeriksaan 1: peminjaman mengurangi stok secara atomik.
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (1, '2026-01-05', '2026-01-12');
INSERT INTO detail_peminjaman VALUES (1, 'BK-001', 2);
UPDATE buku SET stok = stok - 2 WHERE kode_buku = 'BK-001';
COMMIT;
-- Ekspektasi: BK-001 stok 1
SELECT kode_buku, stok FROM buku WHERE kode_buku = 'BK-001';

-- Pemeriksaan 2: pengembalian mengembalikan stok dan menutup pinjaman.
UPDATE peminjaman SET tanggal_kembali = '2026-01-10' WHERE id = 1;
UPDATE buku SET stok = stok + 2 WHERE kode_buku = 'BK-001';
-- Ekspektasi: BK-001 stok 3
SELECT kode_buku, stok FROM buku WHERE kode_buku = 'BK-001';
-- Ekspektasi: peminjaman 1 sudah memiliki tanggal_kembali
SELECT id, tanggal_kembali FROM peminjaman WHERE id = 1;

-- Pemeriksaan 3: peminjaman kedua (id 2) selesai, peminjaman ketiga (id 3) terbuka.
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (2, '2026-01-15', '2026-01-22');
INSERT INTO detail_peminjaman VALUES (2, 'BK-002', 1), (2, 'BK-001', 1);
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-002';
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-001';
UPDATE peminjaman SET tanggal_kembali = '2026-01-20' WHERE id = 2;
UPDATE buku SET stok = stok + 1 WHERE kode_buku = 'BK-002';
UPDATE buku SET stok = stok + 1 WHERE kode_buku = 'BK-001';
COMMIT;
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (2, '2026-02-01', '2026-02-08');
INSERT INTO detail_peminjaman VALUES (3, 'BK-001', 1);
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-001';
COMMIT;
-- Ekspektasi: BK-001 total 4, BK-002 total 1, BK-003 total 0
SELECT b.kode_buku,
       COALESCE(SUM(dp.jumlah), 0) AS total_dipinjam
FROM buku b
LEFT JOIN detail_peminjaman dp ON dp.buku_kode = b.kode_buku
GROUP BY b.kode_buku
ORDER BY total_dipinjam DESC, b.kode_buku;

-- Pemeriksaan 4: hanya peminjaman 3 (harus kembali 2026-02-08) yang terlambat.
-- Ekspektasi: 1 baris - peminjaman 3, 20 hari terlambat
SELECT p.id, DATEDIFF('2026-02-28', p.tanggal_harus_kembali) AS hari_terlambat
FROM peminjaman p
WHERE p.tanggal_kembali IS NULL
  AND p.tanggal_harus_kembali < '2026-02-28'
ORDER BY p.id;

-- Pemeriksaan 5: stok akhir konsisten dengan seluruh mutasi.
-- Ekspektasi: BK-001 stok 2, BK-002 stok 2, BK-003 stok 2
SELECT kode_buku, stok FROM buku ORDER BY kode_buku;

DROP DATABASE perpustakaan_db;