Chapter-chapter sebelumnya mengajarkan tiap potongan PostgreSQL secara terpisah: satu chapter untuk FOREIGN KEY, satu untuk transaksi, satu untuk window function. Potongan-potongan itu baru benar-benar terinternalisasi ketika dipakai menyelesaikan satu masalah utuh dari awal sampai akhir. Pada proyek ini kita membangun database manajemen perpustakaan dari nol: merancang skema dari cerita kebutuhan pengelola, mengelola data master buku dan anggota, menjalankan sirkulasi pinjam-kembali dengan transaksi, lalu menutupnya dengan laporan analitik dan rangkaian uji yang membuktikan seluruh aturan bisnis ditegakkan.

Yang Akan Kita Bangun #

Perpustakaan kecil butuh lebih dari sekadar daftar buku. Pengelola harus tahu judul apa saja yang dimiliki dan siapa penulisnya, mencatat siapa meminjam apa dan kapan harus kembali, menolak peminjaman yang melebihi jumlah copy yang tersedia, dan menghitung denda keterlambatan. Seluruh kebutuhan itu kita tuangkan menjadi database dengan fitur berikut:

  • Katalog buku dengan kategori, penerbit, tahun terbit, dan jumlah copy (stok).
  • Relasi banyak-ke-banyak antara buku dan penulis lewat tabel junction buku_penulis — satu buku boleh ditulis beberapa penulis.
  • Data master anggota dengan email unik sebagai identitas kontak.
  • Sirkulasi satu-ke-banyak: satu peminjaman boleh memuat beberapa buku lewat peminjaman_item, dengan tanggal_kembali per buku.
  • Penjaga ketersediaan berupa constraint trigger tertunda yang menolak peminjaman melebihi stok tepat saat COMMIT.
  • Perhitungan denda Rp1000 per hari keterlambatan memakai aritmetika tanggal dan interval.
  • Laporan analitik: buku terpopuler, anggota paling aktif, sirkulasi bulanan dengan bulan kosong tetap tampil, view ketersediaan stok, dan materialized view.
  • Berkas uji dengan pola LULUS/GAGAL yang bisa dijalankan ulang kapan saja.

Seluruh contoh memakai data deterministik dengan tanggal tetap, sehingga setiap output yang Anda lihat dapat dicocokkan persis dengan yang dihasilkan sendiri.

Skema Database #

Kebutuhan di atas diterjemahkan menjadi tujuh tabel. Diagram berikut memperlihatkan relasi antartabelnya:

Notasi ||--o{ dibaca “satu ke nol-atau-banyak”: satu kategori menampung banyak buku, satu anggota boleh berkali-kali meminjam. Dua relasi paling menarik justru yang tidak menghubungkan langsung: buku dan penulis bertemu di tabel junction buku_penulis karena relasinya banyak-ke-banyak, sedangkan buku dan peminjaman bertemu di peminjaman_item karena satu transaksi boleh memuat beberapa judul sekaligus. Kolom tanggal_kembali yang boleh NULL adalah penanda “masih dipinjam” — nilai kosong inilah yang dipakai seluruh laporan ketersediaan dan keterlambatan.

Prasyarat #

Proyek ini tidak memperkenalkan fitur baru; ia menyusun ulang materi yang sudah Anda pelajari. Pastikan Anda nyaman dengan chapter berikut:

Tidak ada extension atau tool tambahan yang dibutuhkan; cukup psql dan PostgreSQL 17 yang sudah terpasang sejak Instalasi PostgreSQL .

Peta Bagian #

Proyek dikerjakan dalam empat bagian berurutan. Setiap bagian menghasilkan database yang benar-benar bekerja pada tahapnya:

BagianFokus
Bagian 1: Perancangan SkemaDari cerita kebutuhan ke tujuh tabel: constraint, tabel junction, index, dan uji skema
Bagian 2: Pengelolaan Buku dan AnggotaCRUD data master: insert buku multi-penulis, pencarian ILIKE, koreksi anggota, upsert stok
Bagian 3: Peminjaman dan PengembalianTransaksi multi-buku, penjaga ketersediaan saat COMMIT, pengembalian, denda, dan deteksi keterlambatan
Bagian 4: Laporan dan Query LanjutanBuku terpopuler, sirkulasi bulanan dengan generate_series, view ketersediaan, dan materialized view

Setiap bagian mandiri: berkas SQL-nya membangun ulang tabel yang dibutuhkan di awal, sehingga Anda bisa masuk dari bagian mana pun tanpa membawa bekas eksekusi bagian sebelumnya.

Cara Mengikuti Proyek #

Kerjakan tiap bagian sambil mengetik ulang atau menyalin berkas SQL-nya, lalu jalankan dengan psql seperti pada chapter sebelumnya:

bash 
psql -X -f bagian/perancangan-skema/tabel-master.sql -d perpustakaan

Aturan mainnya sama dengan seluruh Course: setiap blok kode pada bagian-bagian berikut adalah berkas nyata yang dieksekusi sungguhan, dan blok Output yang Dihasilkan di bawahnya adalah hasil eksekusi tersebut, bukan tulisan tangan. Nilai-nilai kunci seperti tanggal acuan sengaja dipaku (misalnya DATE '2026-05-15') agar hasil Anda identik dengan hasil di halaman.

Selain berkas per bagian, halaman overview ini juga membawa artefak lengkap hasil akhir proyek yang dapat diunduh dari bagian Berkas Sumber di bawah halaman ini: database/skema.sql (skema final beserta penjaga ketersediaan), database/isi-data.sql (data contoh lengkap dengan riwayat empat bulan), queries/ (empat query laporan siap pakai), dan tests/ (dua berkas uji LULUS/GAGAL). Berkas-berkas itu berguna setelah Anda menyelesaikan keempat bagian: terapkan skema.sql dan isi-data.sql ke database kosong, jalankan berkas uji, lalu bandingkan hasilnya.

Skema adalah fondasi semuanya — dan keputusan desain terbesar justru diambil di sana. Mari kita mulai dari Bagian 1.

Referensi #

# Bagian 1: Perancangan Skema

Rancang tabel buku, anggota, dan sirkulasi beserta constraint dan relasinya.

Buka → # Bagian 2: Pengelolaan Buku dan Anggota

Isi dan kelola data master: CRUD buku, penulis, dan anggota.

Buka → # Bagian 3: Peminjaman dan Pengembalian

Jalankan sirkulasi dengan transaksi, validasi batas pinjam, dan denda.

Buka → # Bagian 4: Laporan dan Query Lanjutan

Susun laporan sirkulasi dengan join, window function, dan view.

Buka →

Berkas Sumber

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

database/isi-data.sql

Unduh berkas
sql 
-- isi-data.sql — data master dan riwayat sirkulasi contoh.
-- Jalankan setelah database/skema.sql pada database yang masih kosong:
--   psql -X -f database/skema.sql -f database/isi-data.sql
-- Tanggal acuan laporan yang menyertai data ini: 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');

-- Riwayat peminjaman: 13 transaksi, 19 buku.
-- Tiga item masih beredar: peminjaman 8, 10, dan 12.
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo) VALUES
    (1, DATE '2026-01-05', DATE '2026-01-19'),
    (2, DATE '2026-01-20', DATE '2026-02-03'),
    (3, DATE '2026-02-01', DATE '2026-02-15'),
    (4, DATE '2026-02-10', DATE '2026-02-24'),
    (1, DATE '2026-03-02', DATE '2026-03-16'),
    (6, DATE '2026-03-05', DATE '2026-03-19'),
    (2, DATE '2026-03-18', DATE '2026-04-01'),
    (5, DATE '2026-04-01', DATE '2026-04-15'),
    (4, DATE '2026-04-10', DATE '2026-04-24'),
    (3, DATE '2026-04-12', DATE '2026-04-26'),
    (1, DATE '2026-04-20', DATE '2026-05-04'),
    (4, DATE '2026-05-02', DATE '2026-05-16'),
    (1, DATE '2026-05-05', DATE '2026-05-19');

INSERT INTO peminjaman_item (peminjaman_id, buku_id, tanggal_kembali) VALUES
    (1, 1, DATE '2026-01-12'),
    (1, 2, DATE '2026-01-12'),
    (2, 5, DATE '2026-02-10'),
    (3, 2, DATE '2026-02-14'),
    (3, 8, DATE '2026-02-14'),
    (4, 3, DATE '2026-02-28'),
    (4, 6, DATE '2026-02-28'),
    (5, 8, DATE '2026-03-16'),
    (6, 1, DATE '2026-03-25'),
    (6, 7, DATE '2026-03-25'),
    (7, 4, DATE '2026-03-30'),
    (8, 2, NULL),
    (8, 5, NULL),
    (9, 1, DATE '2026-04-22'),
    (10, 6, NULL),
    (11, 3, DATE '2026-04-25'),
    (12, 8, NULL),
    (12, 4, NULL),
    (13, 7, DATE '2026-05-10');

database/skema.sql

Unduh berkas
sql 
-- skema.sql — skema lengkap database manajemen perpustakaan.
-- Terapkan pada database kosong:
--   psql -X -f database/skema.sql
-- Isi data contoh dengan database/isi-data.sql setelahnya.
-- Skema ini menggabungkan seluruh hasil Bagian 1 dan Bagian 3:
-- tabel master, tabel sirkulasi, index foreign key, dan penjaga ketersediaan.

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);

COMMENT ON TABLE kategori IS 'Kelompok subjek buku, mis. Fiksi atau Teknologi.';
COMMENT ON TABLE penulis IS 'Data penulis; satu buku boleh ditulis beberapa penulis.';
COMMENT ON TABLE buku IS 'Katalog judul buku beserta jumlah copy yang dimiliki.';
COMMENT ON COLUMN buku.isbn IS 'ISBN-13 tanpa tanda hubung, tepat 13 digit angka.';
COMMENT ON COLUMN buku.stok IS 'Jumlah copy fisik milik perpustakaan, bukan yang tersedia.';
COMMENT ON TABLE buku_penulis IS 'Tabel junction relasi banyak-ke-banyak buku dan penulis.';
COMMENT ON TABLE anggota IS 'Peminjam terdaftar perpustakaan.';
COMMENT ON TABLE peminjaman IS 'Header transaksi: siapa meminjam dan kapan jatuh tempo.';
COMMENT ON TABLE peminjaman_item IS 'Buku yang diambil dalam satu transaksi peminjaman.';
COMMENT ON COLUMN peminjaman_item.tanggal_kembali IS 'NULL berarti copy masih di tangan anggota.';

-- Penjaga ketersediaan: tolak peminjaman yang membuat jumlah copy aktif
-- melebihi stok. Pemeriksaan tertunda hingga COMMIT agar transaksi
-- multi-buku tetap sah selama totalnya dalam batas 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;
$$;

CREATE CONSTRAINT TRIGGER trg_cek_ketersediaan
    AFTER INSERT ON peminjaman_item
    DEFERRABLE INITIALLY DEFERRED
    FOR EACH ROW
    EXECUTE FUNCTION cek_ketersediaan();

queries/buku-terpopuler.sql

Unduh berkas
sql 
-- buku-terpopuler.sql — judul dan penulis paling sering dipinjam.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql.

-- Judul terpopuler beserta peringkatnya; RANK() memberi peringkat sama
-- untuk hasil seri lalu melompat ke peringkat berikutnya.
SELECT b.judul,
       count(*) AS kali_dipinjam,
       rank() OVER (ORDER BY count(*) DESC) AS peringkat
FROM buku b
JOIN peminjaman_item pi ON pi.buku_id = b.id
GROUP BY b.judul
ORDER BY kali_dipinjam DESC, b.judul;

-- Penulis paling banyak dibaca lewat buku-bukunya.
SELECT pe.nama,
       count(*) AS kali_dipinjam
FROM penulis pe
JOIN buku_penulis bp ON bp.penulis_id = pe.id
JOIN peminjaman_item pi ON pi.buku_id = bp.buku_id
GROUP BY pe.nama
ORDER BY kali_dipinjam DESC, pe.nama;

queries/laporan-bulanan.sql

Unduh berkas
sql 
-- laporan-bulanan.sql — sirkulasi per bulan dengan bulan kosong tetap tampil.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql.

WITH bulan AS (
    SELECT generate_series(DATE '2025-12-01', DATE '2026-05-01', interval '1 month')::date AS bulan
),
sirkulasi AS (
    SELECT date_trunc('month', p.tanggal_pinjam)::date AS bulan,
           count(*) AS peminjaman
    FROM peminjaman p
    GROUP BY 1
),
buku_pinjam AS (
    SELECT date_trunc('month', p.tanggal_pinjam)::date AS bulan,
           count(*) AS buku_dipinjam
    FROM peminjaman p
    JOIN peminjaman_item pi ON pi.peminjaman_id = p.id
    GROUP BY 1
)
SELECT to_char(b.bulan, 'YYYY-MM') AS bulan,
       COALESCE(s.peminjaman, 0) AS peminjaman,
       COALESCE(bp.buku_dipinjam, 0) AS buku_dipinjam,
       sum(COALESCE(s.peminjaman, 0)) OVER (ORDER BY b.bulan) AS kumulatif
FROM bulan b
LEFT JOIN sirkulasi s ON s.bulan = b.bulan
LEFT JOIN buku_pinjam bp ON bp.bulan = b.bulan
ORDER BY b.bulan;

queries/stok-tersedia.sql

Unduh berkas
sql 
-- stok-tersedia.sql — ketersediaan copy per judul.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql.
-- Tersedia = stok dikurangi copy yang belum dikembalikan.

SELECT 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.judul, b.stok
ORDER BY b.judul;

queries/terlambat-dan-denda.sql

Unduh berkas
sql 
-- terlambat-dan-denda.sql — piutang denda pada tanggal acuan 2026-05-15.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql.
-- Denda: Rp1000 untuk setiap hari keterlambatan.

-- Item yang melewati jatuh tempo dan belum dikembalikan.
SELECT p.id AS peminjaman,
       a.nama AS anggota,
       b.judul,
       p.jatuh_tempo,
       (DATE '2026-05-15' - p.jatuh_tempo) AS hari_terlambat,
       (DATE '2026-05-15' - 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-05-15'
ORDER BY p.id, b.judul;

-- Total piutang denda yang belum tertagih.
SELECT sum((DATE '2026-05-15' - p.jatuh_tempo) * 1000) AS total_denda_belum_bayar
FROM peminjaman p
JOIN peminjaman_item pi ON pi.peminjaman_id = p.id
WHERE pi.tanggal_kembali IS NULL
  AND p.jatuh_tempo < DATE '2026-05-15';

-- Total denda yang sudah terkumpul dari pengembalian terlambat.
SELECT sum(GREATEST(pi.tanggal_kembali - p.jatuh_tempo, 0) * 1000) AS total_denda_terkumpul
FROM peminjaman_item pi
JOIN peminjaman p ON p.id = pi.peminjaman_id
WHERE pi.tanggal_kembali IS NOT NULL;

tests/uji-integritas.sql

Unduh berkas
sql 
-- uji-integritas.sql — uji positif dan negatif seluruh constraint skema.
-- Jalankan pada database KOSONG yang sudah diberi database/skema.sql:
--   psql -X -f database/skema.sql -f tests/uji-integritas.sql
-- Lulus bila setiap baris kolom hasil berawalan LULUS.
-- Aturan ketersediaan stok diuji terpisah di Bagian 3 karena pemeriksaannya
-- terjadi saat COMMIT, bukan saat INSERT.

INSERT INTO kategori (nama) VALUES ('Fiksi');
INSERT INTO penulis (nama, negara) VALUES ('Andini Raharjo', 'Indonesia');
INSERT INTO anggota (nama, email, telepon, tanggal_daftar)
VALUES ('Budi Santoso', '[email protected]', '081210001001', DATE '2025-09-01');
INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
VALUES ('9786020000001', 'Hujan di Batavia', 1, 'Penerbit Cerah', 2019, 3);
INSERT INTO buku_penulis (buku_id, penulis_id) VALUES (1, 1);

CREATE TABLE hasil_uji (
    urutan integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kasus  text NOT NULL,
    hasil  text NOT NULL
);

DO $$
BEGIN
    INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
    VALUES ('9786020000006', 'Senja di Rantau', 1, 'Penerbit Cerah', 2023, 5);
    INSERT INTO hasil_uji (kasus, hasil)
    VALUES ('buku sah diterima', 'LULUS: baris tersimpan');

    BEGIN
        INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
        VALUES ('978602ABC0001', 'ISBN Salah', 1, 'Uji', 2020, 1);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('ISBN bukan 13 digit angka', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('ISBN bukan 13 digit angka', 'LULUS: ditolak CHECK isbn');
    END;

    BEGIN
        INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
        VALUES ('9786020000099', 'Buku Tua', 1, 'Uji', 1899, 1);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('tahun terbit 1899', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('tahun terbit 1899', 'LULUS: ditolak CHECK tahun_terbit');
    END;

    BEGIN
        INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
        VALUES ('9786020000098', 'Stok Aneh', 1, 'Uji', 2020, -1);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('stok negatif', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('stok negatif', 'LULUS: ditolak CHECK stok');
    END;

    BEGIN
        INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
        VALUES ('9786020000097', NULL, 1, 'Uji', 2020, 1);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('judul kosong', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN not_null_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('judul kosong', 'LULUS: ditolak NOT NULL');
    END;

    BEGIN
        INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
        VALUES ('9786020000001', 'Judul Berbeda, ISBN Sama', 1, 'Uji', 2020, 1);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('ISBN duplikat', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN unique_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('ISBN duplikat', 'LULUS: ditolak UNIQUE buku.isbn');
    END;

    BEGIN
        INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
        VALUES ('9786020000096', 'Kategori Hantu', 999, 'Uji', 2020, 1);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('kategori tidak ada', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN foreign_key_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('kategori tidak ada', 'LULUS: ditolak FOREIGN KEY');
    END;

    BEGIN
        INSERT INTO buku_penulis (buku_id, penulis_id) VALUES (1, 1);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('pasangan buku-penulis duplikat', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN unique_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('pasangan buku-penulis duplikat', 'LULUS: ditolak PRIMARY KEY junction');
    END;

    BEGIN
        INSERT INTO anggota (nama, email, telepon, tanggal_daftar)
        VALUES ('Budi Kedua', '[email protected]', '081200000000', DATE '2026-01-01');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('email anggota duplikat', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN unique_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('email anggota duplikat', 'LULUS: ditolak UNIQUE anggota.email');
    END;

    BEGIN
        INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
        VALUES (1, DATE '2026-01-10', DATE '2026-01-10');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('jatuh tempo sama dengan tanggal pinjam', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('jatuh tempo sama dengan tanggal pinjam', 'LULUS: ditolak CHECK jatuh_tempo');
    END;
END $$;

SELECT urutan, kasus, hasil
FROM hasil_uji
ORDER BY urutan;

tests/uji-sirkulasi.sql

Unduh berkas
sql 
-- uji-sirkulasi.sql — uji angka-angka kunci hasil olah data sirkulasi.
-- Jalankan pada database yang memuat database/skema.sql dan database/isi-data.sql:
--   psql -X -f database/skema.sql -f database/isi-data.sql -f tests/uji-sirkulasi.sql
-- Lulus bila setiap baris kolom hasil bernilai LULUS.
-- Tanggal acuan pengujian: 2026-05-15; denda Rp1000 per hari keterlambatan.

SELECT 1 AS no,
       'jumlah transaksi peminjaman = 13' AS pengujian,
       CASE WHEN (SELECT count(*) FROM peminjaman) = 13
            THEN 'LULUS' ELSE 'GAGAL: aktual ' || (SELECT count(*) FROM peminjaman)::text END AS hasil
UNION ALL
SELECT 2,
       'jumlah buku yang pernah dipinjam = 19',
       CASE WHEN (SELECT count(*) FROM peminjaman_item) = 19
            THEN 'LULUS' ELSE 'GAGAL: aktual ' || (SELECT count(*) FROM peminjaman_item)::text END
UNION ALL
SELECT 3,
       'judul dengan 3 kali pinjam ada 3',
       CASE WHEN (SELECT count(*) FROM (
                    SELECT b.judul FROM buku b
                    JOIN peminjaman_item pi ON pi.buku_id = b.id
                    GROUP BY b.judul
                    HAVING count(*) = 3) t) = 3
            THEN 'LULUS' ELSE 'GAGAL: aktual ' || (SELECT count(*) FROM (
                    SELECT b.judul FROM buku b
                    JOIN peminjaman_item pi ON pi.buku_id = b.id
                    GROUP BY b.judul
                    HAVING count(*) = 3) t)::text END
UNION ALL
SELECT 4,
       'anggota paling aktif: Budi Santoso (4)',
       CASE WHEN (SELECT a.nama FROM anggota a
                    JOIN peminjaman p ON p.anggota_id = a.id
                    GROUP BY a.nama
                    ORDER BY count(*) DESC, a.nama
                    LIMIT 1) = 'Budi Santoso'
            THEN 'LULUS' ELSE 'GAGAL: aktual berbeda' END
UNION ALL
SELECT 5,
       'denda terkumpul dari keterlambatan = 27000',
       CASE WHEN (SELECT sum(GREATEST(pi.tanggal_kembali - p.jatuh_tempo, 0) * 1000)
                    FROM peminjaman_item pi
                    JOIN peminjaman p ON p.id = pi.peminjaman_id
                    WHERE pi.tanggal_kembali IS NOT NULL) = 27000
            THEN 'LULUS' ELSE 'GAGAL: aktual berbeda' END
UNION ALL
SELECT 6,
       'piutang denda per 2026-05-15 = 79000',
       CASE WHEN (SELECT sum((DATE '2026-05-15' - p.jatuh_tempo) * 1000)
                    FROM peminjaman p
                    JOIN peminjaman_item pi ON pi.peminjaman_id = p.id
                    WHERE pi.tanggal_kembali IS NULL
                      AND p.jatuh_tempo < DATE '2026-05-15') = 79000
            THEN 'LULUS' ELSE 'GAGAL: aktual berbeda' END
UNION ALL
SELECT 7,
       'item yang masih beredar = 5',
       CASE WHEN (SELECT count(*) FROM peminjaman_item
                    WHERE tanggal_kembali IS NULL) = 5
            THEN 'LULUS' ELSE 'GAGAL: aktual ' || (SELECT count(*) FROM peminjaman_item
                    WHERE tanggal_kembali IS NULL)::text END
UNION ALL
SELECT 8,
       'Kisah Sang Kapten tersedia = 0',
       CASE WHEN (SELECT b.stok - count(pi.buku_id) FILTER (WHERE pi.tanggal_kembali IS NULL)
                    FROM buku b
                    LEFT JOIN peminjaman_item pi ON pi.buku_id = b.id
                    WHERE b.judul = 'Kisah Sang Kapten'
                    GROUP BY b.stok) = 0
            THEN 'LULUS' ELSE 'GAGAL: aktual berbeda' END
ORDER BY no;