Bagian 3: Peminjaman dan Pengembalian

Sirkulasi adalah jantung perpustakaan — dan bagian paling rawan kesalahan datanya. Satu kali peminjaman menyentuh dua tabel sekaligus (peminjaman dan peminjaman_item); bila aplikasi crash di tengah jalan, database tidak boleh menyimpan transaksi setengah jadi tanpa detail, atau detail yatim tanpa header. Pada bagian ini kita membangun seluruh alur sirkulasi: peminjaman multi-buku dalam satu transaksi atomik, penjaga ketersediaan stok yang memeriksa aturan tepat saat COMMIT, pengembalian per buku dengan perhitungan denda Rp1000 per hari keterlambatan, dan deteksi buku yang melewati jatuh tempo.

Skema Lengkap dan Penjaga Ketersediaan #

Berkas pembuka bagian ini membangun tujuh tabel sekaligus plus satu komponen baru: constraint trigger tertunda yang menjaga aturan “copy aktif tidak boleh melebihi stok”:

sql
siapkan-tabel.sql
-- siapkan-tabel.sql — skema lengkap perpustakaan plus penjaga ketersediaan stok.
-- Versi ringkas skema Bagian 1, ditambah constraint trigger tertunda yang
-- memeriksa stok saat COMMIT, bukan saat tiap INSERT.

CREATE TABLE kategori (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL UNIQUE
);

CREATE TABLE penulis (
    id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama   text NOT NULL,
    negara text NOT NULL DEFAULT 'Indonesia'
);

CREATE TABLE buku (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn         text NOT NULL UNIQUE CHECK (isbn ~ '^[0-9]{13}$'),
    judul        text NOT NULL,
    kategori_id  integer NOT NULL REFERENCES kategori (id),
    penerbit     text NOT NULL,
    tahun_terbit smallint NOT NULL CHECK (tahun_terbit BETWEEN 1900 AND 2100),
    stok         integer NOT NULL CHECK (stok >= 0)
);

CREATE TABLE buku_penulis (
    buku_id    integer NOT NULL REFERENCES buku (id) ON DELETE CASCADE,
    penulis_id integer NOT NULL REFERENCES penulis (id) ON DELETE CASCADE,
    PRIMARY KEY (buku_id, penulis_id)
);

CREATE TABLE anggota (
    id             integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama           text NOT NULL,
    email          text NOT NULL UNIQUE,
    telepon        text NOT NULL,
    tanggal_daftar date NOT NULL
);

CREATE TABLE peminjaman (
    id             integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    anggota_id     integer NOT NULL REFERENCES anggota (id),
    tanggal_pinjam date NOT NULL,
    jatuh_tempo    date NOT NULL,
    CONSTRAINT jatuh_tempo_setelah_pinjam CHECK (jatuh_tempo > tanggal_pinjam)
);

CREATE TABLE peminjaman_item (
    peminjaman_id   integer NOT NULL REFERENCES peminjaman (id) ON DELETE CASCADE,
    buku_id         integer NOT NULL REFERENCES buku (id),
    tanggal_kembali date,
    PRIMARY KEY (peminjaman_id, buku_id)
);

CREATE INDEX idx_buku_kategori        ON buku (kategori_id);
CREATE INDEX idx_buku_penulis_penulis ON buku_penulis (penulis_id);
CREATE INDEX idx_peminjaman_anggota   ON peminjaman (anggota_id);
CREATE INDEX idx_peminjaman_item_buku ON peminjaman_item (buku_id);

-- Penjaga ketersediaan: hitung copy aktif per judul dan tolak
-- bila jumlahnya melebihi stok.
CREATE OR REPLACE FUNCTION cek_ketersediaan() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
    v_stok  integer;
    v_aktif integer;
    v_judul text;
BEGIN
    SELECT b.stok, b.judul INTO v_stok, v_judul
    FROM buku b
    WHERE b.id = NEW.buku_id;

    SELECT count(*) INTO v_aktif
    FROM peminjaman_item pi
    WHERE pi.buku_id = NEW.buku_id
      AND pi.tanggal_kembali IS NULL;

    IF v_aktif > v_stok THEN
        RAISE EXCEPTION 'stok buku "%" tidak cukup: dipinjam %, stok %',
            v_judul, v_aktif, v_stok;
    END IF;
    RETURN NULL;
END;
$$;

-- Constraint trigger tertunda: pemeriksaan bergeser ke COMMIT,
-- sehingga INSERT beberapa item dalam SATU transaksi tetap sah
-- selama total copy aktif tidak melebihi stok.
CREATE CONSTRAINT TRIGGER trg_cek_ketersediaan
    AFTER INSERT ON peminjaman_item
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE FUNCTION cek_ketersediaan();

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE FUNCTION
CREATE TRIGGER

Function cek_ketersediaan() membaca dua angka untuk tiap baris baru di peminjaman_item: stok judul yang bersangkutan dan jumlah item aktifnya (item yang tanggal_kembali-nya masih NULL). Bila copy aktif melebihi stok, RAISE EXCEPTION membatalkan transaksi dengan pesan yang menyebut judul dan kedua angkanya.

Kata kunci desainnya ada di deklarasi trigger: DEFERRABLE INITIALLY DEFERRED. Constraint biasa diperiksa persis saat baris disisipkan; constraint tertunda digeser ke COMMIT. Tanpa penundaan ini, pemeriksaan per baris akan menghitung copy aktif terlalu dini — padahal dalam satu transaksi, baris-baris item baru seharusnya dihitung bersama. Dengan penundaan, seluruh INSERT dieksekusi dulu, lalu trigger memeriksa setiap barisnya dengan pandangan penuh atas hasil transaksi. ON DELETE CASCADE tetap dipasang pada peminjaman_item agar menghapus transaksi peminjaman ikut membawa detailnya.

Data Uji Sirkulasi #

Kita pakai katalog dan anggota yang sama dengan Bagian 2 supaya skenarionya nyambung. Tanggal acuan bagian ini dipaku pada DATE '2026-05-15':

sql
isi-data-uji.sql
-- isi-data-uji.sql — data master untuk skenario sirkulasi Bagian 3.
-- Tanggal acuan skenario: 2026-05-15.

INSERT INTO kategori (nama) VALUES
    ('Fiksi'),
    ('Teknologi'),
    ('Sejarah'),
    ('Biografi');

INSERT INTO penulis (nama, negara) VALUES
    ('Andini Raharjo', 'Indonesia'),
    ('Bima Santosa', 'Indonesia'),
    ('Citra Lestari', 'Indonesia'),
    ('Dewi Anggraeni', 'Indonesia'),
    ('Eka Pratama', 'Indonesia'),
    ('Farhan Hidayat', 'Indonesia'),
    ('Rizky Maulana', 'Indonesia');

INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok) VALUES
    ('9786020000001', 'Hujan di Batavia',       1, 'Penerbit Cerah',          2019, 3),
    ('9786020000002', 'Algoritma untuk Pemula', 2, 'Penerbit Data Nusantara', 2021, 4),
    ('9786020000003', 'Jejak Rempah Nusantara', 3, 'Penerbit Lestari',        2018, 2),
    ('9786020000004', 'Kisah Sang Kapten',      4, 'Penerbit Lestari',        2020, 1),
    ('9786020000005', 'Menyelami Data Besar',   2, 'Penerbit Data Nusantara', 2022, 2),
    ('9786020000006', 'Senja di Rantau',        1, 'Penerbit Cerah',          2023, 5),
    ('9786020000007', 'Peta Perdagangan Lama',  3, 'Penerbit Lestari',        2017, 2),
    ('9786020000008', 'Belajar SQL dari Nol',   2, 'Penerbit Data Nusantara', 2024, 6);

INSERT INTO buku_penulis (buku_id, penulis_id) VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5),
    (5, 6),
    (6, 1),
    (7, 3),
    (7, 7),
    (8, 2);

INSERT INTO anggota (nama, email, telepon, tanggal_daftar) VALUES
    ('Budi Santoso',   '[email protected]',  '081210001001', DATE '2025-09-01'),
    ('Siti Rahmawati', '[email protected]',  '081210001002', DATE '2025-09-15'),
    ('Ahmad Fauzi',    '[email protected]', '081210001003', DATE '2025-10-01'),
    ('Rina Kartika',   '[email protected]',  '081210001004', DATE '2025-11-05'),
    ('Joko Wijaya',    '[email protected]',  '081210001005', DATE '2026-01-10'),
    ('Maya Puspita',   '[email protected]',  '081210001006', DATE '2026-02-20');

Output yang Dihasilkan:

text
INSERT 0 4
INSERT 0 7
INSERT 0 8
INSERT 0 10
INSERT 0 6

Perhatikan Kisah Sang Kapten yang stoknya hanya satu copy — angka ini sengaja dipilih untuk skenario penolakan di akhir bagian.

Meminjam: Satu Transaksi, Banyak Buku #

Peminjaman pertama dilakukan Budi: tiga buku sekaligus, jatuh tempo dua minggu. Peminjaman kedua dilakukan Rina yang mengambil judul yang sama dengan Budi. Keduanya masing-masing dibungkus BEGIN ... COMMIT:

sql
pinjam-buku.sql
-- pinjam-buku.sql — dua transaksi peminjaman multi-buku.
-- Header masuk ke peminjaman, tiap buku masuk ke peminjaman_item,
-- semuanya atomik dalam BEGIN...COMMIT.

BEGIN;

-- Peminjaman 1: Budi meminjam tiga buku sekaligus.
-- Jatuh tempo dihitung dari tanggal pinjam: 2026-05-15 + 14 hari.
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (1, DATE '2026-05-15', DATE '2026-05-15' + 14)
RETURNING id, anggota_id, tanggal_pinjam, jatuh_tempo;

INSERT INTO peminjaman_item (peminjaman_id, buku_id) VALUES
    (1, 1),
    (1, 2),
    (1, 4);

-- Peminjaman 2: Rina meminjam judul yang sama dengan Budi.
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (4, DATE '2026-05-15', DATE '2026-05-29')
RETURNING id, anggota_id, tanggal_pinjam, jatuh_tempo;

INSERT INTO peminjaman_item (peminjaman_id, buku_id) VALUES
    (2, 2);

COMMIT;

-- Bukti tersimpan: isi kedua transaksi hasil join empat tabel.
SELECT p.id AS peminjaman,
       a.nama AS anggota,
       b.judul,
       p.tanggal_pinjam,
       p.jatuh_tempo
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN peminjaman_item pi ON pi.peminjaman_id = p.id
JOIN buku b ON b.id = pi.buku_id
ORDER BY p.id, b.judul;

Output yang Dihasilkan:

text
BEGIN
 id | anggota_id | tanggal_pinjam | jatuh_tempo 
----+------------+----------------+-------------
  1 |          1 | 2026-05-15     | 2026-05-29
(1 row)

INSERT 0 1
INSERT 0 3
 id | anggota_id | tanggal_pinjam | jatuh_tempo 
----+------------+----------------+-------------
  2 |          4 | 2026-05-15     | 2026-05-29
(1 row)

INSERT 0 1
INSERT 0 1
COMMIT
 peminjaman |   anggota    |         judul          | tanggal_pinjam | jatuh_tempo 
------------+--------------+------------------------+----------------+-------------
          1 | Budi Santoso | Algoritma untuk Pemula | 2026-05-15     | 2026-05-29
          1 | Budi Santoso | Hujan di Batavia       | 2026-05-15     | 2026-05-29
          1 | Budi Santoso | Kisah Sang Kapten      | 2026-05-15     | 2026-05-29
          2 | Rina Kartika | Algoritma untuk Pemula | 2026-05-15     | 2026-05-29
(4 rows)

Bedah alurnya:

BEGIN membuka transaksi. Mulai titik ini, perubahan hanya terlihat oleh sesi ini sendiri sampai COMMIT.
INSERT ... RETURNING id pada header memunculkan id transaksi (angka 1) — id inilah yang dipakai baris-baris item berikutnya. Dalam aplikasi nyata, nilai RETURNING ini yang ditangkap kode program, bukan hasil tebakan.
jatuh_tempo dihitung dari tanggal: DATE '2026-05-15' + 14 menghasilkan 2026-05-29. Tipe date memang mendukung penjumlahan dengan integer jumlah hari; cara lain yang setara adalah + interval '14 days'.
COMMIT menutup transaksi — dan di sinilah trigger trg_cek_ketersediaan akhirnya bicara, memeriksa keempat item baru dengan pandangan penuh. Semua lolos karena stok masih mencukupi.

Berapa Copy yang Tersedia? #

Sebelum melayani peminjam berikutnya, petugas butuh jawaban cepat: judul apa yang masih bisa dipinjam? Ketersediaan adalah hasil hitung — stok dikurangi item aktif:

sql
cek-ketersediaan.sql
-- cek-ketersediaan.sql — stok versus copy yang sedang beredar.
-- Kolom tersedia adalah stok dikurangi item yang belum dikembalikan.

SELECT b.id,
       b.judul,
       b.stok,
       count(pi.buku_id) FILTER (WHERE pi.tanggal_kembali IS NULL) AS sedang_dipinjam,
       b.stok - count(pi.buku_id) FILTER (WHERE pi.tanggal_kembali IS NULL) AS tersedia
FROM buku b
LEFT JOIN peminjaman_item pi ON pi.buku_id = b.id
GROUP BY b.id, b.judul, b.stok
ORDER BY b.judul;

-- Judul yang sudah tidak bisa dipinjam hari ini.
SELECT b.judul
FROM buku b
JOIN peminjaman_item pi ON pi.buku_id = b.id
GROUP BY b.judul, b.stok
HAVING count(pi.buku_id) FILTER (WHERE pi.tanggal_kembali IS NULL) >= b.stok
ORDER BY b.judul;

Output yang Dihasilkan:

text
 id |         judul          | stok | sedang_dipinjam | tersedia 
----+------------------------+------+-----------------+----------
  2 | Algoritma untuk Pemula |    4 |               2 |        2
  8 | Belajar SQL dari Nol   |    6 |               0 |        6
  1 | Hujan di Batavia       |    3 |               1 |        2
  3 | Jejak Rempah Nusantara |    2 |               0 |        2
  4 | Kisah Sang Kapten      |    1 |               1 |        0
  5 | Menyelami Data Besar   |    2 |               0 |        2
  7 | Peta Perdagangan Lama  |    2 |               0 |        2
  6 | Senja di Rantau        |    5 |               0 |        5
(8 rows)

       judul       
-------------------
 Kisah Sang Kapten
(1 row)

LEFT JOIN menjaga delapan judul tampil semua — termasuk yang belum pernah dipinjam sama sekali — lalu count(...) FILTER (WHERE tanggal_kembali IS NULL) menghitung hanya item yang masih beredar. Algoritma untuk Pemula tercatat dua copy dipinjam (satu oleh Budi, satu oleh Rina), dan Kisah Sang Kapten sudah tersedia nol: satu-satunya copy ada di tangan Budi. Query kedua memakai HAVING karena filternya menyentuh hasil agregasi, bukan baris mentah. Query ini akan dikemas ulang menjadi v_buku_tersedia di Bagian 4 supaya bisa dipanggil seperti tabel.

Pengembalian dan Denda #

Budi mengembalikan satu buku lima hari sebelum jatuh tempo; Rina mengembalikan tiga hari setelahnya. Keduanya dijalankan dalam satu transaksi pengembalian, dan RETURNING menghitung dendanya langsung:

sql
kembalikan-buku.sql
-- kembalikan-buku.sql — pengembalian per buku beserta hitungan denda,
-- lalu deteksi buku yang melewati jatuh tempo.
-- Denda: Rp1000 untuk setiap hari keterlambatan.

BEGIN;

-- Budi mengembalikan "Hujan di Batavia" lima hari sebelum jatuh tempo.
UPDATE peminjaman_item pi
SET tanggal_kembali = DATE '2026-05-20'
FROM peminjaman p
WHERE pi.peminjaman_id = p.id
  AND p.id = 1
  AND pi.buku_id = 1
RETURNING pi.peminjaman_id,
          pi.buku_id,
          p.jatuh_tempo,
          (pi.tanggal_kembali - p.jatuh_tempo) * interval '1 day' AS keterlambatan,
          GREATEST(pi.tanggal_kembali - p.jatuh_tempo, 0) * 1000 AS denda;

-- Rina mengembalikan "Algoritma untuk Pemula" tiga hari terlambat.
UPDATE peminjaman_item pi
SET tanggal_kembali = DATE '2026-06-01'
FROM peminjaman p
WHERE pi.peminjaman_id = p.id
  AND p.id = 2
  AND pi.buku_id = 2
RETURNING pi.peminjaman_id,
          pi.buku_id,
          p.jatuh_tempo,
          (pi.tanggal_kembali - p.jatuh_tempo) * interval '1 day' AS keterlambatan,
          GREATEST(pi.tanggal_kembali - p.jatuh_tempo, 0) * 1000 AS denda;

COMMIT;

-- Laporan keterlambatan pada tanggal acuan 2026-06-05:
-- buku yang belum kembali padahal jatuh temponya sudah lewat.
SELECT p.id AS peminjaman,
       a.nama AS anggota,
       b.judul,
       p.jatuh_tempo,
       (DATE '2026-06-05' - p.jatuh_tempo) AS hari_terlambat,
       (DATE '2026-06-05' - p.jatuh_tempo) * 1000 AS denda
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN peminjaman_item pi ON pi.peminjaman_id = p.id
JOIN buku b ON b.id = pi.buku_id
WHERE pi.tanggal_kembali IS NULL
  AND p.jatuh_tempo < DATE '2026-06-05'
ORDER BY p.id, b.judul;

Output yang Dihasilkan:

text
BEGIN
 peminjaman_id | buku_id | jatuh_tempo | keterlambatan | denda 
---------------+---------+-------------+---------------+-------
             1 |       1 | 2026-05-29  | -9 days       |     0
(1 row)

UPDATE 1
 peminjaman_id | buku_id | jatuh_tempo | keterlambatan | denda 
---------------+---------+-------------+---------------+-------
             2 |       2 | 2026-05-29  | 3 days        |  3000
(1 row)

UPDATE 1
COMMIT
 peminjaman |   anggota    |         judul          | jatuh_tempo | hari_terlambat | denda 
------------+--------------+------------------------+-------------+----------------+-------
          1 | Budi Santoso | Algoritma untuk Pemula | 2026-05-29  |              7 |  7000
          1 | Budi Santoso | Kisah Sang Kapten      | 2026-05-29  |              7 |  7000
(2 rows)

Tiga teknik bergabung di sini:

UPDATE ... FROM ... RETURNING: klausa FROM menghadirkan tabel peminjaman sehingga RETURNING bisa menampilkan p.jatuh_tempo — kolom milik tabel lain — berdampingan dengan kolom yang baru diubah.
Aritmetika tanggal dua arah: tanggal_kembali - jatuh_tempo pada dua nilai date menghasilkan jumlah hari sebagai integer (bisa negatif), lalu * interval '1 day' mengubahnya menjadi durasi yang enak dibaca: -9 days berarti sembilan hari lebih awal, 3 days berarti tiga hari terlambat.
GREATEST(selisih, 0) * 1000: pengembalian lebih awal tidak boleh menghasilkan denda negatif, jadi selisihnya dijepit ke nol lebih dulu. Rumus sederhana ini adalah satu-satunya tempat aturan denda Rp1000 per hari hidup — dan karena itu ia diuji lagi di berkas tests/.

Laporan penutup mencari dua syarat sekaligus: tanggal_kembali IS NULL (masih beredar) dan jatuh_tempo < tanggal acuan. Hasilnya dua baris milik Budi — Algoritma untuk Pemula dan Kisah Sang Kapten — yang keduanya jatuh temponya 2026-05-29 tapi belum dikembalikan pada 2026-06-05, masing-masing terlambat tujuh hari (denda Rp7000).

Menolak Peminjaman yang Melebihi Stok #

Skenario penutup: Maya datang ingin meminjam Kisah Sang Kapten pada 2026-06-02 — padahal satu-satunya copy masih di tangan Budi. INSERT-nya sendiri berhasil; penolakan justru terjadi di COMMIT:

sql
pinjam-stok-habis.sql
-- pinjam-stok-habis.sql — satu-satunya copy "Kisah Sang Kapten" sedang
-- dipinjam Budi, sehingga peminjaman oleh Maya harus ditolak.
-- Constraint trigger tertunda baru bicara saat COMMIT, lalu seluruh
-- transaksi digulung balik. File ini sengaja berakhir dengan error;
-- psql keluar dengan kode 3.
BEGIN;

INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (6, DATE '2026-06-02', DATE '2026-06-16')
RETURNING id;

INSERT INTO peminjaman_item (peminjaman_id, buku_id) VALUES (3, 4);

COMMIT;

Output yang Dihasilkan:

text

psql:pinjam-stok-habis.sql:14: ERROR:  stok buku "Kisah Sang Kapten" tidak cukup: dipinjam 2, stok 1
CONTEXT:  PL/pgSQL function cek_ketersediaan() line 17 at RAISE

Pesan errornya datang dari function penjaga kita sendiri: copy aktif Kisah Sang Kapten menjadi dua (milik Budi yang belum kembali, plus milik Maya) padahal stoknya satu. Yang terjadi setelahnya sama pentingnya dengan error itu sendiri: seluruh transaksi digulung balik — header peminjaman Maya dan itemnya lenyap seolah tidak pernah diajukan. Tidak ada setengah keputusan yang tersimpan. Setelah kejadian ini, copy Kisah Sang Kapten baru bisa dipinjam lagi setelah Budi mengembalikannya — dan penolakan serupa akan terjadi pada siapa pun yang mencoba sebelum itu.

Sirkulasi kini lengkap: pinjam, ketersediaan, kembalikan, denda, dan penolakan — semuanya ditegakkan di dalam database, bukan hanya di kode aplikasi. Data sirkulasi yang terkumpul adalah bahan baku keputusan pengelola: judul apa yang perlu ditambah stoknya, anggota mana yang paling setia, bulan apa yang paling sibuk. Bagian 4 mengolahnya menjadi laporan.

Referensi #

Berkas Sumber

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

sql
-- cek-ketersediaan.sql — stok versus copy yang sedang beredar.
-- Kolom tersedia adalah stok dikurangi item yang belum dikembalikan.

SELECT b.id,
       b.judul,
       b.stok,
       count(pi.buku_id) FILTER (WHERE pi.tanggal_kembali IS NULL) AS sedang_dipinjam,
       b.stok - count(pi.buku_id) FILTER (WHERE pi.tanggal_kembali IS NULL) AS tersedia
FROM buku b
LEFT JOIN peminjaman_item pi ON pi.buku_id = b.id
GROUP BY b.id, b.judul, b.stok
ORDER BY b.judul;

-- Judul yang sudah tidak bisa dipinjam hari ini.
SELECT b.judul
FROM buku b
JOIN peminjaman_item pi ON pi.buku_id = b.id
GROUP BY b.judul, b.stok
HAVING count(pi.buku_id) FILTER (WHERE pi.tanggal_kembali IS NULL) >= b.stok
ORDER BY b.judul;

sql
-- isi-data-uji.sql — data master untuk skenario sirkulasi Bagian 3.
-- Tanggal acuan skenario: 2026-05-15.

INSERT INTO kategori (nama) VALUES
    ('Fiksi'),
    ('Teknologi'),
    ('Sejarah'),
    ('Biografi');

INSERT INTO penulis (nama, negara) VALUES
    ('Andini Raharjo', 'Indonesia'),
    ('Bima Santosa', 'Indonesia'),
    ('Citra Lestari', 'Indonesia'),
    ('Dewi Anggraeni', 'Indonesia'),
    ('Eka Pratama', 'Indonesia'),
    ('Farhan Hidayat', 'Indonesia'),
    ('Rizky Maulana', 'Indonesia');

INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok) VALUES
    ('9786020000001', 'Hujan di Batavia',       1, 'Penerbit Cerah',          2019, 3),
    ('9786020000002', 'Algoritma untuk Pemula', 2, 'Penerbit Data Nusantara', 2021, 4),
    ('9786020000003', 'Jejak Rempah Nusantara', 3, 'Penerbit Lestari',        2018, 2),
    ('9786020000004', 'Kisah Sang Kapten',      4, 'Penerbit Lestari',        2020, 1),
    ('9786020000005', 'Menyelami Data Besar',   2, 'Penerbit Data Nusantara', 2022, 2),
    ('9786020000006', 'Senja di Rantau',        1, 'Penerbit Cerah',          2023, 5),
    ('9786020000007', 'Peta Perdagangan Lama',  3, 'Penerbit Lestari',        2017, 2),
    ('9786020000008', 'Belajar SQL dari Nol',   2, 'Penerbit Data Nusantara', 2024, 6);

INSERT INTO buku_penulis (buku_id, penulis_id) VALUES
    (1, 1),
    (2, 2),
    (3, 3),
    (4, 4),
    (5, 5),
    (5, 6),
    (6, 1),
    (7, 3),
    (7, 7),
    (8, 2);

INSERT INTO anggota (nama, email, telepon, tanggal_daftar) VALUES
    ('Budi Santoso',   '[email protected]',  '081210001001', DATE '2025-09-01'),
    ('Siti Rahmawati', '[email protected]',  '081210001002', DATE '2025-09-15'),
    ('Ahmad Fauzi',    '[email protected]', '081210001003', DATE '2025-10-01'),
    ('Rina Kartika',   '[email protected]',  '081210001004', DATE '2025-11-05'),
    ('Joko Wijaya',    '[email protected]',  '081210001005', DATE '2026-01-10'),
    ('Maya Puspita',   '[email protected]',  '081210001006', DATE '2026-02-20');

sql
-- kembalikan-buku.sql — pengembalian per buku beserta hitungan denda,
-- lalu deteksi buku yang melewati jatuh tempo.
-- Denda: Rp1000 untuk setiap hari keterlambatan.

BEGIN;

-- Budi mengembalikan "Hujan di Batavia" lima hari sebelum jatuh tempo.
UPDATE peminjaman_item pi
SET tanggal_kembali = DATE '2026-05-20'
FROM peminjaman p
WHERE pi.peminjaman_id = p.id
  AND p.id = 1
  AND pi.buku_id = 1
RETURNING pi.peminjaman_id,
          pi.buku_id,
          p.jatuh_tempo,
          (pi.tanggal_kembali - p.jatuh_tempo) * interval '1 day' AS keterlambatan,
          GREATEST(pi.tanggal_kembali - p.jatuh_tempo, 0) * 1000 AS denda;

-- Rina mengembalikan "Algoritma untuk Pemula" tiga hari terlambat.
UPDATE peminjaman_item pi
SET tanggal_kembali = DATE '2026-06-01'
FROM peminjaman p
WHERE pi.peminjaman_id = p.id
  AND p.id = 2
  AND pi.buku_id = 2
RETURNING pi.peminjaman_id,
          pi.buku_id,
          p.jatuh_tempo,
          (pi.tanggal_kembali - p.jatuh_tempo) * interval '1 day' AS keterlambatan,
          GREATEST(pi.tanggal_kembali - p.jatuh_tempo, 0) * 1000 AS denda;

COMMIT;

-- Laporan keterlambatan pada tanggal acuan 2026-06-05:
-- buku yang belum kembali padahal jatuh temponya sudah lewat.
SELECT p.id AS peminjaman,
       a.nama AS anggota,
       b.judul,
       p.jatuh_tempo,
       (DATE '2026-06-05' - p.jatuh_tempo) AS hari_terlambat,
       (DATE '2026-06-05' - p.jatuh_tempo) * 1000 AS denda
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN peminjaman_item pi ON pi.peminjaman_id = p.id
JOIN buku b ON b.id = pi.buku_id
WHERE pi.tanggal_kembali IS NULL
  AND p.jatuh_tempo < DATE '2026-06-05'
ORDER BY p.id, b.judul;

sql
-- pinjam-buku.sql — dua transaksi peminjaman multi-buku.
-- Header masuk ke peminjaman, tiap buku masuk ke peminjaman_item,
-- semuanya atomik dalam BEGIN...COMMIT.

BEGIN;

-- Peminjaman 1: Budi meminjam tiga buku sekaligus.
-- Jatuh tempo dihitung dari tanggal pinjam: 2026-05-15 + 14 hari.
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (1, DATE '2026-05-15', DATE '2026-05-15' + 14)
RETURNING id, anggota_id, tanggal_pinjam, jatuh_tempo;

INSERT INTO peminjaman_item (peminjaman_id, buku_id) VALUES
    (1, 1),
    (1, 2),
    (1, 4);

-- Peminjaman 2: Rina meminjam judul yang sama dengan Budi.
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (4, DATE '2026-05-15', DATE '2026-05-29')
RETURNING id, anggota_id, tanggal_pinjam, jatuh_tempo;

INSERT INTO peminjaman_item (peminjaman_id, buku_id) VALUES
    (2, 2);

COMMIT;

-- Bukti tersimpan: isi kedua transaksi hasil join empat tabel.
SELECT p.id AS peminjaman,
       a.nama AS anggota,
       b.judul,
       p.tanggal_pinjam,
       p.jatuh_tempo
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN peminjaman_item pi ON pi.peminjaman_id = p.id
JOIN buku b ON b.id = pi.buku_id
ORDER BY p.id, b.judul;

sql
-- pinjam-stok-habis.sql — satu-satunya copy "Kisah Sang Kapten" sedang
-- dipinjam Budi, sehingga peminjaman oleh Maya harus ditolak.
-- Constraint trigger tertunda baru bicara saat COMMIT, lalu seluruh
-- transaksi digulung balik. File ini sengaja berakhir dengan error;
-- psql keluar dengan kode 3.
BEGIN;

INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (6, DATE '2026-06-02', DATE '2026-06-16')
RETURNING id;

INSERT INTO peminjaman_item (peminjaman_id, buku_id) VALUES (3, 4);

COMMIT;

sql
-- siapkan-tabel.sql — skema lengkap perpustakaan plus penjaga ketersediaan stok.
-- Versi ringkas skema Bagian 1, ditambah constraint trigger tertunda yang
-- memeriksa stok saat COMMIT, bukan saat tiap INSERT.

CREATE TABLE kategori (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text NOT NULL UNIQUE
);

CREATE TABLE penulis (
    id     integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama   text NOT NULL,
    negara text NOT NULL DEFAULT 'Indonesia'
);

CREATE TABLE buku (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn         text NOT NULL UNIQUE CHECK (isbn ~ '^[0-9]{13}$'),
    judul        text NOT NULL,
    kategori_id  integer NOT NULL REFERENCES kategori (id),
    penerbit     text NOT NULL,
    tahun_terbit smallint NOT NULL CHECK (tahun_terbit BETWEEN 1900 AND 2100),
    stok         integer NOT NULL CHECK (stok >= 0)
);

CREATE TABLE buku_penulis (
    buku_id    integer NOT NULL REFERENCES buku (id) ON DELETE CASCADE,
    penulis_id integer NOT NULL REFERENCES penulis (id) ON DELETE CASCADE,
    PRIMARY KEY (buku_id, penulis_id)
);

CREATE TABLE anggota (
    id             integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama           text NOT NULL,
    email          text NOT NULL UNIQUE,
    telepon        text NOT NULL,
    tanggal_daftar date NOT NULL
);

CREATE TABLE peminjaman (
    id             integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    anggota_id     integer NOT NULL REFERENCES anggota (id),
    tanggal_pinjam date NOT NULL,
    jatuh_tempo    date NOT NULL,
    CONSTRAINT jatuh_tempo_setelah_pinjam CHECK (jatuh_tempo > tanggal_pinjam)
);

CREATE TABLE peminjaman_item (
    peminjaman_id   integer NOT NULL REFERENCES peminjaman (id) ON DELETE CASCADE,
    buku_id         integer NOT NULL REFERENCES buku (id),
    tanggal_kembali date,
    PRIMARY KEY (peminjaman_id, buku_id)
);

CREATE INDEX idx_buku_kategori        ON buku (kategori_id);
CREATE INDEX idx_buku_penulis_penulis ON buku_penulis (penulis_id);
CREATE INDEX idx_peminjaman_anggota   ON peminjaman (anggota_id);
CREATE INDEX idx_peminjaman_item_buku ON peminjaman_item (buku_id);

-- Penjaga ketersediaan: hitung copy aktif per judul dan tolak
-- bila jumlahnya melebihi stok.
CREATE OR REPLACE FUNCTION cek_ketersediaan() RETURNS trigger
LANGUAGE plpgsql AS $$
DECLARE
    v_stok  integer;
    v_aktif integer;
    v_judul text;
BEGIN
    SELECT b.stok, b.judul INTO v_stok, v_judul
    FROM buku b
    WHERE b.id = NEW.buku_id;

    SELECT count(*) INTO v_aktif
    FROM peminjaman_item pi
    WHERE pi.buku_id = NEW.buku_id
      AND pi.tanggal_kembali IS NULL;

    IF v_aktif > v_stok THEN
        RAISE EXCEPTION 'stok buku "%" tidak cukup: dipinjam %, stok %',
            v_judul, v_aktif, v_stok;
    END IF;
    RETURN NULL;
END;
$$;

-- Constraint trigger tertunda: pemeriksaan bergeser ke COMMIT,
-- sehingga INSERT beberapa item dalam SATU transaksi tetap sah
-- selama total copy aktif tidak melebihi stok.
CREATE CONSTRAINT TRIGGER trg_cek_ketersediaan
    AFTER INSERT ON peminjaman_item
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE FUNCTION cek_ketersediaan();