Bagian 4: Index dan View

-- laporan.sql — Katalog Perpustakaan
-- Bagian 3: kumpulan query laporan untuk pekerjaan harian pustakawan.
-- Jalankan: sqlite3 perpustakaan.db < laporan.sql

.headers on
.mode column

-- Laporan 1: daftar buku per kategori.
.print Laporan 1: Daftar Buku per Kategori
SELECT k.nama AS kategori, b.judul, b.penulis, b.tahun
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
ORDER BY k.nama, b.judul;

.print
-- Laporan 2: buku yang sedang dipinjam beserta peminjamnya.
.print Laporan 2: Buku Sedang Dipinjam
SELECT b.judul, a.nama AS peminjam, p.tanggal_pinjam
FROM peminjaman p
JOIN buku b    ON b.id = p.buku_id
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
ORDER BY p.tanggal_pinjam;

.print
-- Laporan 3: riwayat peminjaman setiap anggota.
.print Laporan 3: Riwayat Peminjaman per Anggota
SELECT a.nama AS anggota, b.judul, p.tanggal_pinjam,
       COALESCE(p.tanggal_kembali, 'belum kembali') AS tanggal_kembali
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN buku b    ON b.id = p.buku_id
ORDER BY a.nama, p.tanggal_pinjam;

.print
-- Laporan 4: statistik koleksi per kategori.
.print Laporan 4: Statistik Kategori
SELECT k.nama AS kategori,
       COUNT(b.id) AS jumlah_buku,
       MIN(b.tahun) AS tahun_tertua,
       MAX(b.tahun) AS tahun_terbaru
FROM kategori k
LEFT JOIN buku b ON b.kategori_id = k.id
GROUP BY k.nama
ORDER BY jumlah_buku DESC, k.nama;

.print
-- Laporan 5: eksemplar tersedia dibandingkan total eksemplar.
.print Laporan 5: Eksemplar Tersedia vs Total
SELECT b.judul,
       b.jumlah_tersedia AS tersedia,
       COUNT(p.id)       AS sedang_dipinjam,
       b.jumlah_tersedia + COUNT(p.id) AS total
FROM buku b
LEFT JOIN peminjaman p
       ON p.buku_id = b.id
      AND p.tanggal_kembali IS NULL
GROUP BY b.id
ORDER BY b.judul;

.print
-- Laporan 6: peminjaman yang melewati jatuh tempo 14 hari
-- dihitung terhadap tanggal referensi 2025-06-20.
.print Laporan 6: Peminjaman Terlambat
SELECT b.judul, a.nama AS peminjam, p.tanggal_pinjam,
       date(p.tanggal_pinjam, '+14 days') AS jatuh_tempo,
       CAST(julianday('2025-06-20') - julianday(p.tanggal_pinjam, '+14 days')
            AS INTEGER) AS hari_terlambat
FROM peminjaman p
JOIN buku b    ON b.id = p.buku_id
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
  AND julianday('2025-06-20') - julianday(p.tanggal_pinjam) > 14;

-- optimasi.sql — Katalog Perpustakaan
-- Bagian 4: index untuk query yang sering dipakai, view untuk
-- laporan berulang, dan ANALYZE untuk statistik query planner.
-- Jalankan: sqlite3 perpustakaan.db < optimasi.sql

PRAGMA foreign_keys = ON;

-- Index pada kolom foreign key: mempercepat JOIN dari tabel induk
-- ke tabel anak, misalnya mencari semua peminjaman satu buku/anggota.
CREATE INDEX idx_buku_kategori_id    ON buku(kategori_id);
CREATE INDEX idx_buku_rak_kode       ON buku(rak_kode);
CREATE INDEX idx_peminjaman_buku_id     ON peminjaman(buku_id);
CREATE INDEX idx_peminjaman_anggota_id  ON peminjaman(anggota_id);

-- Index pada kolom yang sering menjadi kriteria pencarian.
CREATE INDEX idx_buku_judul            ON buku(judul);
CREATE INDEX idx_peminjaman_tanggal    ON peminjaman(tanggal_pinjam);

-- Index parsial: hanya mengindeks baris peminjaman yang masih
-- aktif (tanggal_kembali NULL), yaitu baris yang paling sering
-- dicari pustakawan setiap hari.
CREATE INDEX idx_peminjaman_aktif ON peminjaman(buku_id)
    WHERE tanggal_kembali IS NULL;

-- View: agregat ketersediaan buku dalam satu SELECT sederhana.
CREATE VIEW v_buku_tersedia AS
SELECT b.id,
       b.judul,
       k.nama AS kategori,
       r.lokasi AS lokasi_rak,
       b.jumlah_tersedia,
       (SELECT COUNT(*)
          FROM peminjaman p
         WHERE p.buku_id = b.id
           AND p.tanggal_kembali IS NULL) AS sedang_dipinjam
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
JOIN rak r      ON r.kode = b.rak_kode;

-- View: peminjaman yang masih berjalan beserta jatuh temponya.
CREATE VIEW v_peminjaman_aktif AS
SELECT p.id,
       b.judul,
       a.nama AS peminjam,
       p.tanggal_pinjam,
       date(p.tanggal_pinjam, '+14 days') AS jatuh_tempo
FROM peminjaman p
JOIN buku b    ON b.id = p.buku_id
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL;

-- Kumpulkan statistik tabel dan index untuk query planner.
ANALYZE;

-- schema.sql — Katalog Perpustakaan
-- Bagian 1: skema database untuk katalog perpustakaan.
-- Jalankan: sqlite3 perpustakaan.db < schema.sql

PRAGMA foreign_keys = ON;

-- Lokasi fisik buku di ruang perpustakaan.
CREATE TABLE rak (
    kode   TEXT PRIMARY KEY,
    lokasi TEXT NOT NULL
);

-- Kategori buku. Dipisah dari tabel buku agar nama kategori
-- tidak ditulis ulang di setiap baris buku (normalisasi).
CREATE TABLE kategori (
    id   INTEGER PRIMARY KEY,
    nama TEXT NOT NULL UNIQUE
);

-- Data buku. Satu buku masuk tepat satu kategori dan
-- disimpan di tepat satu rak.
CREATE TABLE buku (
    id              INTEGER PRIMARY KEY,
    judul           TEXT    NOT NULL,
    penulis         TEXT    NOT NULL,
    tahun           INTEGER NOT NULL CHECK (tahun >= 1900),
    isbn            TEXT    NOT NULL UNIQUE,
    kategori_id     INTEGER NOT NULL REFERENCES kategori(id)
                             ON DELETE RESTRICT,
    rak_kode        TEXT    NOT NULL REFERENCES rak(kode)
                             ON DELETE RESTRICT,
    jumlah_tersedia INTEGER NOT NULL CHECK (jumlah_tersedia >= 0)
);

-- Anggota perpustakaan.
CREATE TABLE anggota (
    id             INTEGER PRIMARY KEY,
    nama           TEXT NOT NULL,
    email          TEXT NOT NULL UNIQUE,
    tanggal_gabung TEXT NOT NULL
);

-- Catatan peminjaman. tanggal_kembali bernilai NULL
-- selama buku masih di tangan anggota.
CREATE TABLE peminjaman (
    id              INTEGER PRIMARY KEY,
    buku_id         INTEGER NOT NULL REFERENCES buku(id)
                             ON DELETE CASCADE,
    anggota_id      INTEGER NOT NULL REFERENCES anggota(id)
                             ON DELETE CASCADE,
    tanggal_pinjam  TEXT    NOT NULL,
    tanggal_kembali TEXT,
    CHECK (tanggal_kembali IS NULL OR tanggal_kembali >= tanggal_pinjam)
);

-- seed.sql — Katalog Perpustakaan
-- Bagian 2: data awal yang realistis untuk perpustakaan.
-- Jalankan setelah schema.sql: sqlite3 perpustakaan.db < seed.sql

PRAGMA foreign_keys = ON;

BEGIN TRANSACTION;

-- Empat rak tempat buku disimpan.
INSERT INTO rak (kode, lokasi) VALUES
    ('R1', 'Lantai 1, dekat pintu masuk'),
    ('R2', 'Lantai 1, sisi kanan'),
    ('R3', 'Lantai 2, ruang baca'),
    ('R4', 'Lantai 2, pojok belakang');

-- Enam kategori buku.
INSERT INTO kategori (id, nama) VALUES
    (1, 'Fiksi'),
    (2, 'Sains'),
    (3, 'Sejarah'),
    (4, 'Teknologi'),
    (5, 'Puisi'),
    (6, 'Biografi');

-- Lima belas judul buku di seluruh kategori.
-- jumlah_tersedia = jumlah eksemplar yang ada di rak saat ini.
INSERT INTO buku (id, judul, penulis, tahun, isbn, kategori_id, rak_kode, jumlah_tersedia) VALUES
    (1,  'Bumi Manusia',                    'Pramoedya Ananta Toer',  1980, '9789799731234', 1, 'R1', 3),
    (2,  'Laskar Pelangi',                  'Andrea Hirata',          2005, '9789793062792', 1, 'R1', 2),
    (3,  'Cantik Itu Luka',                 'Eka Kurniawan',          2002, '9789799105251', 1, 'R1', 2),
    (4,  'Atheis',                          'Achdiat K. Mihardja',   1949, '9789794334017', 1, 'R1', 1),
    (5,  'Kosmos',                          'Carl Sagan',             1980, '9786024240293', 2, 'R3', 2),
    (6,  'A Brief History of Time',          'Stephen Hawking',       1988, '9780553175219', 2, 'R3', 1),
    (7,  'Sapiens: Riwayat Singkat Umat Manusia', 'Yuval Noah Harari', 2011, '9786020427433', 3, 'R4', 2),
    (8,  'Sejarah Indonesia Modern',        'M.C. Ricklefs',          1981, '9789793780824', 3, 'R4', 1),
    (9,  'Clean Code',                      'Robert C. Martin',       2008, '9780132350884', 4, 'R3', 2),
    (10, 'The Pragmatic Programmer',        'David Thomas',           1999, '9780201616224', 4, 'R3', 1),
    (11, 'The Mythical Man-Month',          'Frederick P. Brooks',    1975, '9780201835953', 4, 'R3', 1),
    (12, 'Hujan Bulan Juni',                'Sapardi Djoko Damono',   1994, '9789799145359', 5, 'R2', 1),
    (13, 'Deru Campur Debu',                'Chairil Anwar',           1949, '9789794336516', 5, 'R2', 1),
    (14, 'Steve Jobs',                      'Walter Isaacson',        2011, '9781451648546', 6, 'R4', 2),
    (15, 'Long Walk to Freedom',            'Nelson Mandela',         1994, '9780349106533', 6, 'R4', 1);

-- Lima anggota perpustakaan.
INSERT INTO anggota (id, nama, email, tanggal_gabung) VALUES
    (1, 'Budi Santoso',  '[email protected]',  '2024-01-15'),
    (2, 'Siti Rahayu',   '[email protected]',  '2024-02-03'),
    (3, 'Agus Prasetyo', '[email protected]',  '2024-05-20'),
    (4, 'Rina Wijaya',   '[email protected]',  '2025-01-08'),
    (5, 'Eko Nugroho',   '[email protected]',   '2025-03-12');

-- Enam peminjaman; baris ke-5 belum dikembalikan
-- (tanggal_kembali NULL), itulah sebabnya jumlah_tersedia
-- buku Kosmos tinggal 2 dari 3 eksemplar.
INSERT INTO peminjaman (id, buku_id, anggota_id, tanggal_pinjam, tanggal_kembali) VALUES
    (1, 1,  1, '2025-05-05', '2025-05-12'),
    (2, 2,  2, '2025-05-10', '2025-05-24'),
    (3, 9,  3, '2025-05-15', '2025-05-29'),
    (4, 7,  4, '2025-06-02', '2025-06-05'),
    (5, 5,  1, '2025-05-26', NULL),
    (6, 12, 5, '2025-06-10', '2025-06-14');

COMMIT;