Merancang Skema Database

-- Hasil akhir langkah 1-4: lima tabel yang masing-masing punya satu tugas.

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

CREATE TABLE buku (
    id           integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    isbn         text NOT NULL UNIQUE,
    judul        text NOT NULL,
    tahun_terbit integer NOT NULL CHECK (tahun_terbit BETWEEN 1900 AND 2026)
);

-- Junction M:N: satu buku boleh ditulis banyak penulis,
-- satu penulis boleh menulis banyak buku.
CREATE TABLE buku_penulis (
    id_buku    integer NOT NULL REFERENCES buku (id) ON DELETE CASCADE,
    id_penulis integer NOT NULL REFERENCES penulis (id) ON DELETE CASCADE,
    PRIMARY KEY (id_buku, id_penulis)
);

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

-- Satu peminjaman membawa banyak buku lewat item_peminjaman.
CREATE TABLE peminjaman (
    id                  integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    id_anggota          integer NOT NULL REFERENCES anggota (id),
    tanggal_pinjam      date NOT NULL,
    tanggal_jatuh_tempo date NOT NULL,
    CHECK (tanggal_jatuh_tempo > tanggal_pinjam)
);

-- Junction dengan atribut tambahan: tanggal_kembali milik PASANGAN
-- peminjaman-buku, bukan milik peminjaman maupun milik buku.
CREATE TABLE item_peminjaman (
    id              integer PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    id_peminjaman   integer NOT NULL REFERENCES peminjaman (id) ON DELETE CASCADE,
    id_buku         integer NOT NULL REFERENCES buku (id),
    tanggal_kembali date,
    CHECK (tanggal_kembali IS NULL OR tanggal_kembali >= DATE '2026-01-01')
);

CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE

-- Data uji: ukuran kecil, tapi setiap kebutuhan punya kasusnya.
-- Ada buku berpenulis ganda, peminjaman yang sudah kembali,
-- peminjaman yang masih berjalan, dan peminjaman yang terlambat.

INSERT INTO penulis (nama) VALUES
    ('Ayu Prameswari'),
    ('Bintang Sanjaya'),
    ('Citra Maharani');

INSERT INTO buku (isbn, judul, tahun_terbit) VALUES
    ('978-602-0001-01-8', 'Panduan Berkebun Sayur', 2023),
    ('978-602-0002-02-2', 'Matahari Pagi',          2021),
    ('978-602-0003-03-9', 'Memasak untuk Pemula',   2022),
    ('978-602-0004-04-6', 'Puisi Hujan',            2020);

-- Buku 1 ditulis dua penulis sekaligus -- bukti relasi M:N hidup.
INSERT INTO buku_penulis (id_buku, id_penulis) VALUES
    (1, 1),
    (1, 2),
    (2, 3),
    (3, 2),
    (4, 3);

INSERT INTO anggota (nama, email, tanggal_gabung) VALUES
    ('Budi Santoso', '[email protected]', DATE '2025-11-02'),
    ('Siti Rahayu',  '[email protected]', DATE '2025-12-15'),
    ('Dewi Lestari', '[email protected]', DATE '2026-01-20');

INSERT INTO peminjaman (id_anggota, tanggal_pinjam, tanggal_jatuh_tempo) VALUES
    (1, DATE '2026-02-01', DATE '2026-02-15'),
    (2, DATE '2026-02-05', DATE '2026-02-19'),
    (3, DATE '2026-03-01', DATE '2026-03-15'),
    (1, DATE '2026-03-05', DATE '2026-03-19'),
    (2, DATE '2026-03-10', DATE '2026-03-24');

INSERT INTO item_peminjaman (id_peminjaman, id_buku, tanggal_kembali) VALUES
    (1, 1, DATE '2026-02-10'),
    (1, 2, DATE '2026-02-12'),
    (2, 1, DATE '2026-02-14'),
    (3, 1, NULL),
    (3, 4, NULL),
    (4, 3, NULL),
    (5, 2, NULL);

-- Peta data: peminjaman 1-2 sudah selesai, 3-5 masih berjalan;
-- per 2026-03-20, peminjaman 3 dan 4 melewati jatuh tempo.

INSERT 0 3
INSERT 0 4
INSERT 0 5
INSERT 0 3
INSERT 0 5
INSERT 0 7

-- Ujian akhir desain: satu query untuk setiap kebutuhan narasi.
-- Skema yang benar menjawab semua kebutuhan tanpa operasi pembedah string,
-- tanpa join ganda, tanpa asumsi yang tidak tertulis.

-- Kebutuhan 1: buku apa saja yang sedang tidak ada di rak, dan siapa
-- yang memegangnya? (tanggal_kembali NULL = masih dipinjam)
SELECT b.judul, a.nama AS pemegang, pm.tanggal_jatuh_tempo
FROM item_peminjaman i
JOIN peminjaman pm ON pm.id = i.id_peminjaman
JOIN anggota a     ON a.id = pm.id_anggota
JOIN buku b        ON b.id = i.id_buku
WHERE i.tanggal_kembali IS NULL
ORDER BY b.judul, a.nama;

-- Kebutuhan 2: siapa saja yang terlambat mengembalikan per 2026-03-20?
-- Terlambat = belum kembali DAN melewati jatuh tempo.
SELECT a.nama, b.judul, pm.tanggal_jatuh_tempo
FROM item_peminjaman i
JOIN peminjaman pm ON pm.id = i.id_peminjaman
JOIN anggota a     ON a.id = pm.id_anggota
JOIN buku b        ON b.id = i.id_buku
WHERE i.tanggal_kembali IS NULL
  AND pm.tanggal_jatuh_tempo < DATE '2026-03-20'
ORDER BY a.nama, b.judul;

-- Kebutuhan 3: buku terpopuler dihitung dari riwayat peminjaman.
SELECT b.judul, count(*) AS kali_dipinjam
FROM item_peminjaman i
JOIN buku b ON b.id = i.id_buku
GROUP BY b.id, b.judul
ORDER BY count(*) DESC, b.judul;

-- Kebutuhan 4: daftar karya seorang penulis -- relasi M:N dibaca
-- lewat junction tanpa perlu tahu siapa penulis pertamanya.
SELECT b.judul, b.tahun_terbit
FROM buku_penulis bp
JOIN buku b     ON b.id = bp.id_buku
JOIN penulis pn ON pn.id = bp.id_penulis
WHERE pn.nama = 'Citra Maharani'
ORDER BY b.tahun_terbit;

-- Kebutuhan 5: riwayat peminjaman satu anggota, buku yang belum
-- kembali tetap tampil dengan kolom tanggal_kembali kosong.
SELECT b.judul, pm.tanggal_pinjam, i.tanggal_kembali
FROM item_peminjaman i
JOIN peminjaman pm ON pm.id = i.id_peminjaman
JOIN anggota a     ON a.id = pm.id_anggota
JOIN buku b        ON b.id = i.id_buku
WHERE a.nama = 'Budi Santoso'
ORDER BY pm.tanggal_pinjam, b.judul;

         judul          |   pemegang   | tanggal_jatuh_tempo 
------------------------+--------------+---------------------
 Matahari Pagi          | Siti Rahayu  | 2026-03-24
 Memasak untuk Pemula   | Budi Santoso | 2026-03-19
 Panduan Berkebun Sayur | Dewi Lestari | 2026-03-15
 Puisi Hujan            | Dewi Lestari | 2026-03-15
(4 rows)

     nama     |         judul          | tanggal_jatuh_tempo 
--------------+------------------------+---------------------
 Budi Santoso | Memasak untuk Pemula   | 2026-03-19
 Dewi Lestari | Panduan Berkebun Sayur | 2026-03-15
 Dewi Lestari | Puisi Hujan            | 2026-03-15
(3 rows)

         judul          | kali_dipinjam 
------------------------+---------------
 Panduan Berkebun Sayur |             3
 Matahari Pagi          |             2
 Memasak untuk Pemula   |             1
 Puisi Hujan            |             1
(4 rows)

     judul     | tahun_terbit 
---------------+--------------
 Puisi Hujan   |         2020
 Matahari Pagi |         2021
(2 rows)

         judul          | tanggal_pinjam | tanggal_kembali 
------------------------+----------------+-----------------
 Matahari Pagi          | 2026-02-01     | 2026-02-12
 Panduan Berkebun Sayur | 2026-02-01     | 2026-02-10
 Memasak untuk Pemula   | 2026-03-05     | 
(3 rows)

-- Lapisan performa: PK dan UNIQUE otomatis membuat index,
-- tetapi kolom foreign key TIDAK -- kita buat sendiri untuk dua
-- jalur join yang paling sering dipakai.
CREATE INDEX idx_item_buku ON item_peminjaman (id_buku);
CREATE INDEX idx_peminjaman_anggota ON peminjaman (id_anggota);

-- Peta index akhir: index _pkey dan _key lahir otomatis dari
-- PRIMARY KEY serta UNIQUE; index idx_* adalah dua yang kita tambahkan.
SELECT tablename, indexname
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY tablename, indexname;

CREATE INDEX
CREATE INDEX
    tablename    |       indexname        
-----------------+------------------------
 anggota         | anggota_email_key
 anggota         | anggota_pkey
 buku            | buku_isbn_key
 buku            | buku_pkey
 buku_penulis    | buku_penulis_pkey
 item_peminjaman | idx_item_buku
 item_peminjaman | item_peminjaman_pkey
 peminjaman      | idx_peminjaman_anggota
 peminjaman      | peminjaman_pkey
 penulis         | penulis_pkey
(10 rows)