Bagian 4: Laporan dan Query Lanjutan

Database sirkulasi yang sehat menghasilkan sesuatu yang tak kalah berharga: data. Empat bulan riwayat pinjam-kembali menyimpan jawaban atas pertanyaan pengelola — judul apa yang perlu ditambah stoknya, anggota mana yang paling setia, bulan apa yang paling sibuk, berapa piutang denda yang belum tertagih. Pada bagian penutup ini kita mengolah data tersebut menjadi laporan: agregasi lintas tabel dengan GROUP BY, peringkat dengan window function RANK(), laporan bulanan yang tidak kehilangan bulan kosong berkat generate_series, laporan rutin yang dikemas sebagai VIEW, dan laporan yang disimpan ke disk sebagai MATERIALIZED VIEW.

Skema dan Riwayat Empat Bulan #

Berkas pembuka membangun ulang tujuh tabel (versi ringkas, tanpa trigger ketersediaan karena bagian ini hanya membaca data). Riwayatnya kali ini bukan kosong: 13 transaksi peminjaman dengan 19 buku tersebar dari Desember 2025 — yang sengaja kosong — sampai Mei 2026, dengan tiga item yang masih beredar:

sql
siapkan-tabel.sql
-- siapkan-tabel.sql — skema lengkap untuk analitik, versi ringkas.
-- Trigger ketersediaan dari Bagian 3 tidak disertakan karena bagian ini
-- hanya membaca data; skema final lengkap ada di database/skema.sql.

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

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

Seluruh laporan pada bagian ini memakai tanggal acuan DATE '2026-05-15' — angka-angka yang Anda lihat di output akan persis sama bila data contoh berikut diterapkan apa adanya:

sql
isi-data.sql
-- isi-data.sql — data master dan riwayat sirkulasi empat bulan terakhir.
-- Tanggal acuan seluruh laporan pada bagian 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');

Output yang Dihasilkan:

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

Berkas inilah versi panjang dari database/isi-data.sql pada Berkas Sumber proyek — data yang sama dipakai berkas uji tests/uji-sirkulasi.sql untuk memverifikasi angka-angka kunci laporan. Riwayat sengaja memuat tiga macam keterlambatan: yang sudah diselesaikan dengan denda terbayar, yang masih beredar dan menunggu penagihan, serta peminjaman yang berjalan normal — ketiganya akan tampak berbeda perannya di tiap laporan.

Buku Terpopuler dan Anggota Paling Aktif #

Laporan pertama menjawab tiga pertanyaan sekaligus. Peringkatnya dihitung window function RANK():

sql
laporan-sirkulasi.sql
-- laporan-sirkulasi.sql — judul, anggota, dan penulis paling sibuk.

-- Buku terpopuler: berapa kali tiap judul dipinjam.
-- RANK() memberi peringkat sama untuk hasil seri, lalu melompat.
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;

-- Anggota paling aktif; LEFT JOIN menjaga anggota tanpa riwayat tetap tampil.
SELECT a.nama,
       count(p.id) AS jumlah_peminjaman
FROM anggota a
LEFT JOIN peminjaman p ON p.anggota_id = a.id
GROUP BY a.nama
ORDER BY jumlah_peminjaman DESC, a.nama;

-- Penulis paling banyak dibaca lewat buku-bukunya;
-- buku multi-penulis menghitung sekali untuk setiap penulisnya.
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;

Output yang Dihasilkan:

text
         judul          | kali_dipinjam | peringkat 
------------------------+---------------+-----------
 Algoritma untuk Pemula |             3 |         1
 Belajar SQL dari Nol   |             3 |         1
 Hujan di Batavia       |             3 |         1
 Jejak Rempah Nusantara |             2 |         4
 Kisah Sang Kapten      |             2 |         4
 Menyelami Data Besar   |             2 |         4
 Peta Perdagangan Lama  |             2 |         4
 Senja di Rantau        |             2 |         4
(8 rows)

      nama      | jumlah_peminjaman 
----------------+-------------------
 Budi Santoso   |                 4
 Rina Kartika   |                 3
 Ahmad Fauzi    |                 2
 Siti Rahmawati |                 2
 Joko Wijaya    |                 1
 Maya Puspita   |                 1
(6 rows)

      nama      | kali_dipinjam 
----------------+---------------
 Bima Santosa   |             6
 Andini Raharjo |             5
 Citra Lestari  |             4
 Dewi Anggraeni |             2
 Eka Pratama    |             2
 Farhan Hidayat |             2
 Rizky Maulana  |             2
(7 rows)

Hasilnya kaya informasi: tiga judul seri di peringkat pertama dengan tiga kali pinjam, lalu RANK() melompat langsung ke peringkat 4 — perilaku khas RANK() yang meninggalkan celah, berbeda dari DENSE_RANK() yang akan melanjutkan ke 2. Budi Santoso tampil sebagai anggota paling aktif dengan empat transaksi. Pada laporan penulis, Bima Santosa menempati puncak dengan enam kali pinjam karena dua bukunya sama-sama terpopuler — dan Andini Raharjo menyusul dengan lima. Perhatikan pula pola ORDER BY pada ketiga query: agregat diikuti kolom nama sebagai tie-break, karena ORDER BY count(*) DESC saja tidak menjamin urutan hasil seri yang stabil.

Laporan Bulanan Tanpa Bulan Hilang #

Aggregasi langsung atas peminjaman hanya menghasilkan bulan yang ada transaksinya — Desember 2025 akan hilang tanpa kabar. Padahal bulan kosong adalah informasi: ia menunjukkan sepi ramainya layanan. generate_series membuat kalender bulanannya lebih dulu, lalu data nyata di-LEFT JOIN kepadanya:

sql
laporan-bulanan.sql
-- laporan-bulanan.sql — sirkulasi per bulan dengan bulan kosong tetap tampil.
-- generate_series membuat kalender bulanan, lalu data asli di-LEFT JOIN
-- sehingga bulan tanpa transaksi muncul dengan angka nol.

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;

Output yang Dihasilkan:

text
  bulan  | peminjaman | buku_dipinjam | kumulatif 
---------+------------+---------------+-----------
 2025-12 |          0 |             0 |         0
 2026-01 |          2 |             3 |         2
 2026-02 |          2 |             4 |         4
 2026-03 |          3 |             4 |         7
 2026-04 |          4 |             5 |        11
 2026-05 |          2 |             3 |        13
(6 rows)

Cara kerjanya per lapis CTE:

bulan memanggil generate_series dengan tiga argumen date dan interval '1 month' — ia mengembalikan satu baris per bulan dari Desember 2025 sampai Mei 2026, enam baris terlepas dari ada tidaknya transaksi.
sirkulasi dan buku_pinjam mengagregasi data nyata: date_trunc('month', ...) memotong tanggal ke awal bulannya supaya bisa disandingkan dengan kalender.
LEFT JOIN + COALESCE(..., 0) mengubah ketiadaan pasangan menjadi angka nol — Desember 2025 tampil dengan dua angka nol, bukan hilang.
sum(...) OVER (ORDER BY b.bulan) adalah running total versi window function: jumlah pinjaman berjalan dari bulan ke bulan hingga 13 di akhir periode.

Laporan semacam ini layak dibaca dua arah: per bulan untuk melihat lonjakan April (empat transaksi), dan kumulatif untuk melihat pertumbuhan layanan sepanjang semester.

View untuk Laporan Rutin #

Query ketersediaan dan piutang denda dipakai setiap hari; mengetik ulang join empat tabel tiap kali adalah ajangan salah ketik. CREATE VIEW menyimpan query sebagai objek bernama yang bisa dipanggil seperti tabel:

sql
buat-view.sql
-- buat-view.sql — view ketersediaan stok dan piutang denda.
-- View menyimpan query sebagai objek bernama di database.

-- Ketersediaan tiap judul: stok dikurangi copy yang sedang beredar.
CREATE VIEW v_buku_tersedia AS
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;

-- Judul yang tidak bisa dipinjam lagi pada 2026-05-15.
SELECT judul, stok, sedang_dipinjam, tersedia
FROM v_buku_tersedia
WHERE tersedia = 0
ORDER BY judul;

-- Piutang denda: item yang melewati jatuh tempo dan belum kembali,
-- dihitung pada tanggal acuan 2026-05-15.
CREATE VIEW v_peminjaman_terlambat AS
SELECT p.id AS peminjaman_id,
       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';

SELECT peminjaman_id, anggota, judul, jatuh_tempo, hari_terlambat, denda
FROM v_peminjaman_terlambat
ORDER BY peminjaman_id, judul;

-- Ringkasan denda: piutang yang belum tertagih.
SELECT sum(denda) AS total_denda_belum_bayar
FROM v_peminjaman_terlambat;

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

Output yang Dihasilkan:

text
CREATE VIEW
       judul       | stok | sedang_dipinjam | tersedia 
-------------------+------+-----------------+----------
 Kisah Sang Kapten |    1 |               1 |        0
(1 row)

CREATE VIEW
 peminjaman_id |   anggota   |         judul          | jatuh_tempo | hari_terlambat | denda 
---------------+-------------+------------------------+-------------+----------------+-------
             8 | Joko Wijaya | Algoritma untuk Pemula | 2026-04-15  |             30 | 30000
             8 | Joko Wijaya | Menyelami Data Besar   | 2026-04-15  |             30 | 30000
            10 | Ahmad Fauzi | Senja di Rantau        | 2026-04-26  |             19 | 19000
(3 rows)

 total_denda_belum_bayar 
-------------------------
                   79000
(1 row)

 total_denda_terkumpul 
-----------------------
                 27000
(1 row)

Dua view berperan berbeda. v_buku_tersedia adalah kalkulasi murni atas kondisi terkini — angkanya berubah begitu ada peminjaman atau pengembalian baru, tanpa perintah apa pun. v_peminjaman_terlambat memakai tanggal acuan yang dibakukan di dalam definisinya; dalam sistem produksi ia biasanya dibuat sebagai function berparameter, atau tanggalnya diganti current_date dengan konsekuensi hasilnya berubah tiap hari. Laporan terakhir menunjukkan dua sisi denda: Rp27000 sudah terkumpul dari pengembalian terlambat, dan Rp79000 masih menjadi piutang — uang yang oleh berkas uji tests/uji-sirkulasi.sql dikonfirmasi tepat.

Materialized View untuk Laporan yang Disimpan #

View biasa menjalankan querynya setiap kali dipanggil. Untuk laporan berat yang jarang berubah — seperti rekap bulanan — PostgreSQL menyediakan MATERIALIZED VIEW: hasilnya disimpan ke disk seperti tabel, dan hanya diperbarui saat diminta:

sql
materialized-view.sql
-- materialized-view.sql — laporan bulanan sebagai materialized view:
-- hasil query disimpan ke disk dan hanya berubah saat di-REFRESH.

CREATE MATERIALIZED VIEW mv_sirkulasi_bulanan AS
SELECT date_trunc('month', p.tanggal_pinjam)::date AS bulan,
       count(*) AS peminjaman
FROM peminjaman p
GROUP BY 1
ORDER BY 1;

SELECT to_char(bulan, 'YYYY-MM') AS bulan, peminjaman
FROM mv_sirkulasi_bulanan
ORDER BY bulan;

-- Data baru masuk: peminjaman pertama di bulan Desember 2025.
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (6, DATE '2025-12-20', DATE '2026-01-03');

INSERT INTO peminjaman_item (peminjaman_id, buku_id, tanggal_kembali)
VALUES (14, 1, DATE '2025-12-28');

-- Materialized view TIDAK berubah otomatis: Desember masih absen.
SELECT to_char(bulan, 'YYYY-MM') AS bulan, peminjaman
FROM mv_sirkulasi_bulanan
ORDER BY bulan;

-- Segarkan setelah data berubah.
REFRESH MATERIALIZED VIEW mv_sirkulasi_bulanan;

-- Baru setelah REFRESH, Desember 2025 muncul.
SELECT to_char(bulan, 'YYYY-MM') AS bulan, peminjaman
FROM mv_sirkulasi_bulanan
ORDER BY bulan;

Output yang Dihasilkan:

text
SELECT 5
  bulan  | peminjaman 
---------+------------
 2026-01 |          2
 2026-02 |          2
 2026-03 |          3
 2026-04 |          4
 2026-05 |          2
(5 rows)

INSERT 0 1
INSERT 0 1
  bulan  | peminjaman 
---------+------------
 2026-01 |          2
 2026-02 |          2
 2026-03 |          3
 2026-04 |          4
 2026-05 |          2
(5 rows)

REFRESH MATERIALIZED VIEW
  bulan  | peminjaman 
---------+------------
 2025-12 |          1
 2026-01 |          2
 2026-02 |          2
 2026-03 |          3
 2026-04 |          4
 2026-05 |          2
(6 rows)

Urutan kejadian di output adalah pelajaran utamanya: query pertama menampilkan lima bulan; sebuah transaksi Desember 2025 masuk; query kedua masih menampilkan lima bulan yang sama — materialized view tidak bereaksi; REFRESH MATERIALIZED VIEW dijalankan; query ketiga baru menampilkan enam bulan dengan Desember berisi satu. Ketinggalan semacam ini bukan bug melainkan konsekuensi desain: kecepatan baca ditukar dengan kesegaran data. Kapan materialized view layak dipilih? Ketika query dasarnya berat (ringkasan jutaan baris), pembacaannya jauh lebih sering daripada perubahannya, dan jadwal REFRESH dapat disepakati — misalnya tiap malam lewat cron. Untuk ketersediaan stok yang harus selalu mutakhir, view biasa tetap pilihan yang benar.

Dengan ini proyek perpustakaan tuntas dari ujung ke ujung: skema dengan constraint yang teruji, alur CRUD data master, sirkulasi atomik dengan penjaga ketersediaan, dan laporan analitik yang siap dipakai pengelola. Seluruh artefak final — skema lengkap, data contoh, empat query laporan, dan dua berkas uji — tersedia pada bagian Berkas Sumber di halaman overview proyek. Pola yang baru saja Anda kerjakan — rancang, isi, transaksikan, laporkan, uji — adalah pola universal database aplikasi; proyek berikutnya, Proyek: Database Inventaris Barang , menerapkannya kembali pada domain gudang dengan tantangan barunya: mutasi stok antarlokasi.

Referensi #

Berkas Sumber

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

sql
-- buat-view.sql — view ketersediaan stok dan piutang denda.
-- View menyimpan query sebagai objek bernama di database.

-- Ketersediaan tiap judul: stok dikurangi copy yang sedang beredar.
CREATE VIEW v_buku_tersedia AS
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;

-- Judul yang tidak bisa dipinjam lagi pada 2026-05-15.
SELECT judul, stok, sedang_dipinjam, tersedia
FROM v_buku_tersedia
WHERE tersedia = 0
ORDER BY judul;

-- Piutang denda: item yang melewati jatuh tempo dan belum kembali,
-- dihitung pada tanggal acuan 2026-05-15.
CREATE VIEW v_peminjaman_terlambat AS
SELECT p.id AS peminjaman_id,
       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';

SELECT peminjaman_id, anggota, judul, jatuh_tempo, hari_terlambat, denda
FROM v_peminjaman_terlambat
ORDER BY peminjaman_id, judul;

-- Ringkasan denda: piutang yang belum tertagih.
SELECT sum(denda) AS total_denda_belum_bayar
FROM v_peminjaman_terlambat;

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

sql
-- isi-data.sql — data master dan riwayat sirkulasi empat bulan terakhir.
-- Tanggal acuan seluruh laporan pada bagian 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');

sql
-- laporan-bulanan.sql — sirkulasi per bulan dengan bulan kosong tetap tampil.
-- generate_series membuat kalender bulanan, lalu data asli di-LEFT JOIN
-- sehingga bulan tanpa transaksi muncul dengan angka nol.

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;

sql
-- laporan-sirkulasi.sql — judul, anggota, dan penulis paling sibuk.

-- Buku terpopuler: berapa kali tiap judul dipinjam.
-- RANK() memberi peringkat sama untuk hasil seri, lalu melompat.
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;

-- Anggota paling aktif; LEFT JOIN menjaga anggota tanpa riwayat tetap tampil.
SELECT a.nama,
       count(p.id) AS jumlah_peminjaman
FROM anggota a
LEFT JOIN peminjaman p ON p.anggota_id = a.id
GROUP BY a.nama
ORDER BY jumlah_peminjaman DESC, a.nama;

-- Penulis paling banyak dibaca lewat buku-bukunya;
-- buku multi-penulis menghitung sekali untuk setiap penulisnya.
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;

sql
-- materialized-view.sql — laporan bulanan sebagai materialized view:
-- hasil query disimpan ke disk dan hanya berubah saat di-REFRESH.

CREATE MATERIALIZED VIEW mv_sirkulasi_bulanan AS
SELECT date_trunc('month', p.tanggal_pinjam)::date AS bulan,
       count(*) AS peminjaman
FROM peminjaman p
GROUP BY 1
ORDER BY 1;

SELECT to_char(bulan, 'YYYY-MM') AS bulan, peminjaman
FROM mv_sirkulasi_bulanan
ORDER BY bulan;

-- Data baru masuk: peminjaman pertama di bulan Desember 2025.
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, jatuh_tempo)
VALUES (6, DATE '2025-12-20', DATE '2026-01-03');

INSERT INTO peminjaman_item (peminjaman_id, buku_id, tanggal_kembali)
VALUES (14, 1, DATE '2025-12-28');

-- Materialized view TIDAK berubah otomatis: Desember masih absen.
SELECT to_char(bulan, 'YYYY-MM') AS bulan, peminjaman
FROM mv_sirkulasi_bulanan
ORDER BY bulan;

-- Segarkan setelah data berubah.
REFRESH MATERIALIZED VIEW mv_sirkulasi_bulanan;

-- Baru setelah REFRESH, Desember 2025 muncul.
SELECT to_char(bulan, 'YYYY-MM') AS bulan, peminjaman
FROM mv_sirkulasi_bulanan
ORDER BY bulan;

sql
-- siapkan-tabel.sql — skema lengkap untuk analitik, versi ringkas.
-- Trigger ketersediaan dari Bagian 3 tidak disertakan karena bagian ini
-- hanya membaca data; skema final lengkap ada di database/skema.sql.

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