Bagian 2: Pengelolaan Buku dan Anggota

Skema yang dirancang di Bagian 1 ibarat rak kosong: strukturnya sudah benar, tapi belum ada satu judul pun yang bisa dipinjam. Sebelum sirkulasi bisa berjalan, pengelola perpustakaan menjalani rutinitas yang jauh lebih sering daripada meminjamkan buku — menambah judul baru, mencari katalog, mendaftarkan anggota, dan menyesuaikan stok saat ada pengadaan. Pada bagian ini kita membangun seluruh alur kerja data master itu: insert beruntun kategori–penulis–buku, pengikatan penulis lewat tabel junction, pencarian dengan ILIKE, koreksi dan pembersihan data anggota, hingga upsert pengadaan yang aman dijalankan berulang.

Menyiapkan Tabel Master #

Setiap bagian proyek ini mandiri: berkasnya membangun ulang tabel yang dibutuhkan dari nol, sehingga Anda tidak membawa bekas eksekusi bagian sebelumnya. Untuk alur kerja data master, tabel sirkulasi belum terlibat, jadi cukup lima tabel master:

sql
siapkan-tabel.sql
-- siapkan-tabel.sql — tabel master katalog dan keanggotaan, versi ringkas.
-- Skema yang sama dengan Bagian 1 tanpa komentar; tabel sirkulasi belum
-- dibutuhkan karena bagian ini hanya mengelola data master.

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 INDEX idx_buku_kategori        ON buku (kategori_id);
CREATE INDEX idx_buku_penulis_penulis ON buku_penulis (penulis_id);

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

Definisinya identik dengan Bagian 1 — kolom yang sama, constraint yang sama — hanya tanpa COMMENT ON supaya fokus tetap pada data. Jika Anda melanjutkan dari bagian sebelumnya di database yang sama, jalankan berkas ini pada database baru; constraint UNIQUE dan PRIMARY KEY akan menolak pembuatan tabel duplikat di database yang sudah berisi skema.

Menambah Buku dan Menghubungkan Penulisnya #

Menambah satu judul buku sebenarnya adalah tiga INSERT berurutan: kategori (bila belum ada), buku, lalu baris-baris buku_penulis. Urutan ini bukan gaya-gayaan — foreign key memaksa induk ada lebih dulu. Berikut tambah-buku.sql yang mengisi delapan judul:

sql
tambah-buku.sql
-- tambah-buku.sql — mengisi katalog: kategori, penulis, buku,
-- lalu hubungkan buku dengan penulisnya lewat buku_penulis.

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

-- Dua buku ditulis lebih dari satu penulis: relasi banyak-ke-banyak.
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);

-- Verifikasi: katalog lengkap dengan daftar penulis tiap judul.
SELECT b.id,
       b.judul,
       k.nama AS kategori,
       b.tahun_terbit,
       b.stok,
       string_agg(pe.nama, ', ' ORDER BY pe.nama) AS penulis
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
JOIN buku_penulis bp ON bp.buku_id = b.id
JOIN penulis pe ON pe.id = bp.penulis_id
GROUP BY b.id, b.judul, k.nama, b.tahun_terbit, b.stok
ORDER BY b.id;

Output yang Dihasilkan:

text
INSERT 0 4
INSERT 0 7
INSERT 0 8
INSERT 0 10
 id |         judul          | kategori  | tahun_terbit | stok |           penulis            
----+------------------------+-----------+--------------+------+------------------------------
  1 | Hujan di Batavia       | Fiksi     |         2019 |    3 | Andini Raharjo
  2 | Algoritma untuk Pemula | Teknologi |         2021 |    4 | Bima Santosa
  3 | Jejak Rempah Nusantara | Sejarah   |         2018 |    2 | Citra Lestari
  4 | Kisah Sang Kapten      | Biografi  |         2020 |    1 | Dewi Anggraeni
  5 | Menyelami Data Besar   | Teknologi |         2022 |    2 | Eka Pratama, Farhan Hidayat
  6 | Senja di Rantau        | Fiksi     |         2023 |    5 | Andini Raharjo
  7 | Peta Perdagangan Lama  | Sejarah   |         2017 |    2 | Citra Lestari, Rizky Maulana
  8 | Belajar SQL dari Nol   | Teknologi |         2024 |    6 | Bima Santosa
(8 rows)

Dari sepuluh baris junction, dua buku memuat dua penulis: Menyelami Data Besar (Eka Pratama dan Farhan Hidayat) dan Peta Perdagangan Lama (Citra Lestari dan Rizky Maulana). Relasi banyak-ke-banyak terlihat paling jelas pada Andini Raharjo yang muncul di dua judul sekaligus. Query verifikasinya merangkai empat tabel dengan join dan string_agg untuk merapatkan daftar penulis menjadi satu kolom — perhatikan ORDER BY pe.nama di dalam string_agg agar urutan nama dalam kolom itu sendiri pasti, di samping ORDER BY b.id untuk urutan baris hasil.

Mencari Buku dengan ILIKE #

Pencarian katalog adalah operasi paling sering dilakukan petugas di meja sirkulasi. Pola %kata% dengan ILIKE menemukan judul yang memuat kata tersebut tanpa peduli huruf besar/kecil:

sql
cari-buku.sql
-- cari-buku.sql — pencarian katalog dengan ILIKE dan join ke penulis.

-- ILIKE tidak peduli huruf besar/kecil: pola huruf besar tetap
-- menemukan judul yang ditulis huruf kecil.
SELECT b.id, b.judul, k.nama AS kategori, b.stok
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
WHERE b.judul ILIKE '%BELAJAR%'
ORDER BY b.judul;

-- Pencarian satu pintu: cocokkan judul ATAU nama penulis.
-- Buku dengan beberapa penulis tetap tampil per penulis.
SELECT b.id, b.judul, pe.nama AS penulis
FROM buku b
JOIN buku_penulis bp ON bp.buku_id = b.id
JOIN penulis pe ON pe.id = bp.penulis_id
WHERE b.judul ILIKE '% data %'
   OR pe.nama ILIKE '%andini%'
ORDER BY b.judul, pe.nama;

-- Kandidat pengadaan: buku Sejarah yang stoknya di bawah tiga.
SELECT b.judul, b.stok
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
WHERE k.nama = 'Sejarah'
  AND b.stok < 3
ORDER BY b.stok, b.judul;

Output yang Dihasilkan:

text
 id |        judul         | kategori  | stok 
----+----------------------+-----------+------
  8 | Belajar SQL dari Nol | Teknologi |    6
(1 row)

 id |        judul         |    penulis     
----+----------------------+----------------
  1 | Hujan di Batavia     | Andini Raharjo
  5 | Menyelami Data Besar | Eka Pratama
  5 | Menyelami Data Besar | Farhan Hidayat
  6 | Senja di Rantau      | Andini Raharjo
(4 rows)

         judul          | stok 
------------------------+------
 Jejak Rempah Nusantara |    2
 Peta Perdagangan Lama  |    2
(2 rows)

Query pertama membuktikan sifat terpenting ILIKE: pola '%BELAJAR%' dengan huruf kapital semua tetap menemukan judul Belajar SQL dari Nol yang ditulis campuran — sesuatu yang LIKE biasa akan lewatkan. Query kedua adalah pencarian satu pintu: kata kunci diuji terhadap judul atau nama penulis, sehingga pengunjung yang hanya ingat nama pengarang tetap sampai ke bukunya. Pola '% data %' dengan spasi pengapit mencari kata data, bukan potongan seperti pada kata “merdata” — trik sederhana yang menaikkan presisi pencarian teks. Query ketiga menggabungkan filter kategori dan ambang stok: daftar belanja pengadaan berikutnya.

Mengelola Anggota #

Data anggota berubah karena hal-hal manusiawi: nomor telepon berganti, pendaftaran uji perlu dibersihkan. Operasi UPDATE dan DELETE pada bagian ini selalu diakhiri RETURNING supaya setiap perubahan meninggalkan bukti baris apa yang tersentuh:

sql
kelola-anggota.sql
-- kelola-anggota.sql — daftar anggota baru, koreksi data, dan pembersihan
-- baris uji, semuanya dengan bukti lewat RETURNING.

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

-- Salah ketik saat pendaftaran: koreksi nomor telepon Joko.
UPDATE anggota
SET telepon = '081310009999'
WHERE email = '[email protected]'
RETURNING id, nama, telepon;

-- Baris uji yang tidak seharusnya tersimpan: hapus dan buktikan.
INSERT INTO anggota (nama, email, telepon, tanggal_daftar)
VALUES ('Uji Sementara', '[email protected]', '081200000000', DATE '2026-03-01');

DELETE FROM anggota
WHERE email = '[email protected]'
RETURNING id, nama;

-- Daftar anggota terkini.
SELECT id, nama, email, tanggal_daftar
FROM anggota
ORDER BY id;

Output yang Dihasilkan:

text
INSERT 0 6
 id |    nama     |   telepon    
----+-------------+--------------
  5 | Joko Wijaya | 081310009999
(1 row)

UPDATE 1
INSERT 0 1
 id |     nama      
----+---------------
  7 | Uji Sementara
(1 row)

DELETE 1
 id |      nama      |       email       | tanggal_daftar 
----+----------------+-------------------+----------------
  1 | Budi Santoso   | [email protected]  | 2025-09-01
  2 | Siti Rahmawati | [email protected]  | 2025-09-15
  3 | Ahmad Fauzi    | [email protected] | 2025-10-01
  4 | Rina Kartika   | [email protected]  | 2025-11-05
  5 | Joko Wijaya    | [email protected]  | 2026-01-10
  6 | Maya Puspita   | [email protected]  | 2026-02-20
(6 rows)

Perhatikan bahwa UPDATE maupun DELETE menyaring berdasar email — kolom unik — bukan nama. Mencari dengan WHERE nama = 'Joko Wijaya' berisiko menyentuh beberapa orang bernama sama sekaligus. Setelah operasi selesai, SELECT penutup menampilkan enam anggota dengan id berurutan 1 sampai 6; anggota bernomor 7 (baris uji) tidak ada lagi. Nomor id itu sendiri tidak dipakai ulang oleh identity column setelah barisnya dihapus.

Menyesuaikan Stok dan Upsert Pengadaan #

Ketika kiriman pengadaan tiba, pengelola menambah stok buku yang sudah ada dan mendaftarkan judul yang benar-benar baru. Dua kasus ini bisa disatukan dalam satu perintah INSERT ... ON CONFLICT:

sql
ubah-stok.sql
-- ubah-stok.sql — penyesuaian jumlah copy dan pengadaan lewat upsert.

-- Pengadaan rutin: tambah dua copy buku yang permintaannya tinggi.
UPDATE buku
SET stok = stok + 2
WHERE isbn = '9786020000008'
RETURNING isbn, judul, stok;

-- Pengadaan idempoten: bila ISBN sudah ada, tambah satu copy;
-- bila belum ada, simpan sebagai judul baru. Tidak perlu cek-then-insert
-- yang rawan balapan antar sesi.
INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
VALUES ('9786020000008', 'Belajar SQL dari Nol', 2, 'Penerbit Data Nusantara', 2024, 1)
ON CONFLICT (isbn) DO UPDATE
    SET stok = buku.stok + 1
RETURNING isbn, judul, stok;

-- Jalur insert murni dari upsert yang sama: judul benar-benar baru.
INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
VALUES ('9786020000009', 'Jaringan Komputer Dasar', 2, 'Penerbit Data Nusantara', 2025, 3)
ON CONFLICT (isbn) DO UPDATE
    SET stok = buku.stok + 1
RETURNING isbn, judul, stok;

-- Stok terkini kategori Teknologi setelah pengadaan.
SELECT b.judul, b.stok
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
WHERE k.nama = 'Teknologi'
ORDER BY b.judul;

Output yang Dihasilkan:

text
     isbn      |        judul         | stok 
---------------+----------------------+------
 9786020000008 | Belajar SQL dari Nol |    8
(1 row)

UPDATE 1
     isbn      |        judul         | stok 
---------------+----------------------+------
 9786020000008 | Belajar SQL dari Nol |    9
(1 row)

INSERT 0 1
     isbn      |          judul          | stok 
---------------+-------------------------+------
 9786020000009 | Jaringan Komputer Dasar |    3
(1 row)

INSERT 0 1
          judul          | stok 
-------------------------+------
 Algoritma untuk Pemula  |    4
 Belajar SQL dari Nol    |    9
 Jaringan Komputer Dasar |    3
 Menyelami Data Besar    |    2
(4 rows)

Urutan kejadiannya terbaca jelas di output: UPDATE menaikkan stok Belajar SQL dari Nol dari 6 ke 8, lalu upsert pertama berjalan lewat jalur DO UPDATE (ISBN sudah ada) dan menaikkannya lagi ke 9, sedangkan upsert kedua berjalan lewat jalur insert murni karena ISBN-nya benar-benar baru. Dalam DO UPDATE, rujukan buku.stok mengacu pada baris lama — nilainya diambil sebelum perubahan diterapkan.

Mengapa tidak cukup pola “cek dulu dengan SELECT, baru INSERT”? Karena dua sesi yang mengeksekusi pemeriksaan itu hampir bersamaan akan sama-sama menyimpulkan “ISBN belum ada”, lalu salah satunya gagal dengan error duplikat. ON CONFLICT membuat keputusan menjadi satu operasi atomik di dalam database — tidak ada celah balapan di antara cek dan insert.

Menolak ISBN Duplikat #

Sebelum menutup bagian ini, buktikan sekali lagi bahwa penjaga UNIQUE benar-benar berdiri. Judul baru yang mencoba memakai ISBN milik Hujan di Batavia harus ditolak:

sql
isbn-duplikat.sql
-- isbn-duplikat.sql — dua judul berbeda tidak boleh berbagi ISBN.
-- File ini sengaja berakhir dengan error; psql keluar dengan kode 3.
INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
VALUES ('9786020000001', 'Hujan di Batavia Edisi Berbeda', 1, 'Penerbit Cerah', 2020, 2);

Output yang Dihasilkan:

text

psql:isbn-duplikat.sql:4: ERROR:  duplicate key value violates unique constraint "buku_isbn_key"
DETAIL:  Key (isbn)=(9786020000001) already exists.

Pesan errornya menyebut constraint buku_isbn_key — index unik yang dibuat otomatis oleh deklarasi UNIQUE — lengkap dengan pasangan kunci yang bentrok. Dengan ON_ERROR_STOP=1, psql keluar dengan kode 3: tanda bahwa di dunia nyata aplikasi harus menangkap konflik ini dan memutuskan, misalnya beralih ke jalur ON CONFLICT seperti pengadaan di atas.

Katalog dan daftar anggota kini hidup: delapan judul (bertambah satu lewat pengadaan) dan enam anggota siap meminjam. Data master saja tidak membuat perpustakaan berfungsi — yang menentukan hidup-matinya layanan adalah sirkulasi. Bagian 3 membangun inti itu: transaksi peminjaman multi-buku, penjaga ketersediaan stok, pengembalian, dan denda.

Referensi #

Berkas Sumber

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

sql
-- cari-buku.sql — pencarian katalog dengan ILIKE dan join ke penulis.

-- ILIKE tidak peduli huruf besar/kecil: pola huruf besar tetap
-- menemukan judul yang ditulis huruf kecil.
SELECT b.id, b.judul, k.nama AS kategori, b.stok
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
WHERE b.judul ILIKE '%BELAJAR%'
ORDER BY b.judul;

-- Pencarian satu pintu: cocokkan judul ATAU nama penulis.
-- Buku dengan beberapa penulis tetap tampil per penulis.
SELECT b.id, b.judul, pe.nama AS penulis
FROM buku b
JOIN buku_penulis bp ON bp.buku_id = b.id
JOIN penulis pe ON pe.id = bp.penulis_id
WHERE b.judul ILIKE '% data %'
   OR pe.nama ILIKE '%andini%'
ORDER BY b.judul, pe.nama;

-- Kandidat pengadaan: buku Sejarah yang stoknya di bawah tiga.
SELECT b.judul, b.stok
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
WHERE k.nama = 'Sejarah'
  AND b.stok < 3
ORDER BY b.stok, b.judul;

sql
-- isbn-duplikat.sql — dua judul berbeda tidak boleh berbagi ISBN.
-- File ini sengaja berakhir dengan error; psql keluar dengan kode 3.
INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
VALUES ('9786020000001', 'Hujan di Batavia Edisi Berbeda', 1, 'Penerbit Cerah', 2020, 2);

sql
-- kelola-anggota.sql — daftar anggota baru, koreksi data, dan pembersihan
-- baris uji, semuanya dengan bukti lewat RETURNING.

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

-- Salah ketik saat pendaftaran: koreksi nomor telepon Joko.
UPDATE anggota
SET telepon = '081310009999'
WHERE email = '[email protected]'
RETURNING id, nama, telepon;

-- Baris uji yang tidak seharusnya tersimpan: hapus dan buktikan.
INSERT INTO anggota (nama, email, telepon, tanggal_daftar)
VALUES ('Uji Sementara', '[email protected]', '081200000000', DATE '2026-03-01');

DELETE FROM anggota
WHERE email = '[email protected]'
RETURNING id, nama;

-- Daftar anggota terkini.
SELECT id, nama, email, tanggal_daftar
FROM anggota
ORDER BY id;

sql
-- siapkan-tabel.sql — tabel master katalog dan keanggotaan, versi ringkas.
-- Skema yang sama dengan Bagian 1 tanpa komentar; tabel sirkulasi belum
-- dibutuhkan karena bagian ini hanya mengelola data master.

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 INDEX idx_buku_kategori        ON buku (kategori_id);
CREATE INDEX idx_buku_penulis_penulis ON buku_penulis (penulis_id);

sql
-- tambah-buku.sql — mengisi katalog: kategori, penulis, buku,
-- lalu hubungkan buku dengan penulisnya lewat buku_penulis.

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

-- Dua buku ditulis lebih dari satu penulis: relasi banyak-ke-banyak.
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);

-- Verifikasi: katalog lengkap dengan daftar penulis tiap judul.
SELECT b.id,
       b.judul,
       k.nama AS kategori,
       b.tahun_terbit,
       b.stok,
       string_agg(pe.nama, ', ' ORDER BY pe.nama) AS penulis
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
JOIN buku_penulis bp ON bp.buku_id = b.id
JOIN penulis pe ON pe.id = bp.penulis_id
GROUP BY b.id, b.judul, k.nama, b.tahun_terbit, b.stok
ORDER BY b.id;

sql
-- ubah-stok.sql — penyesuaian jumlah copy dan pengadaan lewat upsert.

-- Pengadaan rutin: tambah dua copy buku yang permintaannya tinggi.
UPDATE buku
SET stok = stok + 2
WHERE isbn = '9786020000008'
RETURNING isbn, judul, stok;

-- Pengadaan idempoten: bila ISBN sudah ada, tambah satu copy;
-- bila belum ada, simpan sebagai judul baru. Tidak perlu cek-then-insert
-- yang rawan balapan antar sesi.
INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
VALUES ('9786020000008', 'Belajar SQL dari Nol', 2, 'Penerbit Data Nusantara', 2024, 1)
ON CONFLICT (isbn) DO UPDATE
    SET stok = buku.stok + 1
RETURNING isbn, judul, stok;

-- Jalur insert murni dari upsert yang sama: judul benar-benar baru.
INSERT INTO buku (isbn, judul, kategori_id, penerbit, tahun_terbit, stok)
VALUES ('9786020000009', 'Jaringan Komputer Dasar', 2, 'Penerbit Data Nusantara', 2025, 3)
ON CONFLICT (isbn) DO UPDATE
    SET stok = buku.stok + 1
RETURNING isbn, judul, stok;

-- Stok terkini kategori Teknologi setelah pengadaan.
SELECT b.judul, b.stok
FROM buku b
JOIN kategori k ON k.id = b.kategori_id
WHERE k.nama = 'Teknologi'
ORDER BY b.judul;