Perencanaan Proyek

Topik ini membuka proyek penutup jalur, yaitu membangun basis data utuh untuk aplikasi perpustakaan kecil. Perencanaan menyangkut empat pekerjaan, yaitu merumuskan kebutuhan aplikasi, menurunkan tabel beserta hubungannya, menulis dua berkas rancangan bernama skema.sql dan data.sql, serta menyusun rencana index. Setiap keputusan dituangkan ke dalam berkas yang dapat dijalankan ulang, sehingga rancangan dan kenyataannya tidak terpisah. Rancangan yang sama menjadi bahan baku tahap implementasi dan penalaan pada modul yang sama.

Menerjemahkan kebutuhan menjadi tabel#

Perencanaan schema dimulai dari kebutuhan aplikasi, bukan dari daftar kolom. Aplikasi perpustakaan kecil pada proyek ini dituntut menyediakan empat layanan berikut. Setiap layanan dinyatakan sebagai kalimat biasa terlebih dahulu, karena kalimat itulah yang nantinya diuji dengan query.

• pendaftaran anggota lengkap dengan nomor anggota yang unik
• katalog buku yang terkelompok menurut kategori bertingkat, misalnya kategori Sains memuat Fisika dan Astronomi
• pencatatan peminjaman dan pengembalian beserta tanggal kejadiannya
• laporan bulanan berisi total denda dan daftar buku yang sedang dipinjam

Empat layanan itu menurunkan empat tabel. Tabel kategori menampung hierarki melalui kolom id_induk yang menunjuk baris kategori lain, sehingga satu kategori dapat menjadi induk bagi kategori lain tanpa tabel tambahan. Tabel buku menunjuk satu kategori, tabel anggota menyimpan identitas peminjam, dan tabel peminjaman menghubungkan keduanya dengan tanggal serta status. Nilai denda tidak disimpan langsung, melainkan dihitung dari tanggal melalui kolom yang dihasilkan (generated column), yaitu kolom yang nilainya diturunkan dari kolom lain oleh mesin basis data.

Struktur keempat tabel dan hubungannya dirangkum dalam diagram relasi entitas (entity relationship diagram, ERD) berikut. Panah menunjuk dari kunci primer ke kolom kunci tamu yang merujuknya, sedangkan panah melingkar pada kategori menyatakan rujukan ke tabelnya sendiri. Nama tabel memakai bentuk tunggal, dan setiap tabel memakai id INTEGER PRIMARY KEY sebagai kunci primernya karena nilai itu sekaligus menjadi alias rowid yang dicari paling cepat oleh SQLite.

Menulis skema.sql#

Rancangan pada bagian sebelumnya dituangkan ke dalam satu berkas bernama skema.sql. Berkas ini menjadi sumber tunggal struktur basis data, dan isinya dijalankan pada bagian berikutnya sebagai pembuktian. Setiap aturan penting dinyatakan langsung di dalam definisinya agar error muncul di awal, bukan di tengah pengoperasian.

-- Skema basis data perpustakaan: proyek penutup jalur SQLite.
PRAGMA foreign_keys = ON;

CREATE TABLE kategori (
  id       INTEGER PRIMARY KEY,
  nama     TEXT NOT NULL UNIQUE,
  id_induk INTEGER REFERENCES kategori(id) ON DELETE RESTRICT
);

CREATE TABLE buku (
  id          INTEGER PRIMARY KEY,
  judul       TEXT NOT NULL,
  penulis     TEXT NOT NULL,
  tahun       INTEGER NOT NULL CHECK (tahun BETWEEN 1900 AND 2100),
  stok        INTEGER NOT NULL DEFAULT 1 CHECK (stok >= 0),
  kategori_id INTEGER NOT NULL REFERENCES kategori(id)
);

CREATE TABLE anggota (
  id                INTEGER PRIMARY KEY,
  nomor_anggota     TEXT NOT NULL UNIQUE,
  nama              TEXT NOT NULL,
  tanggal_bergabung TEXT NOT NULL
);

CREATE TABLE peminjaman (
  id              INTEGER PRIMARY KEY,
  anggota_id      INTEGER NOT NULL REFERENCES anggota(id),
  buku_id         INTEGER NOT NULL REFERENCES buku(id),
  tanggal_pinjam  TEXT NOT NULL,
  tanggal_kembali TEXT,
  status          TEXT NOT NULL DEFAULT 'dipinjam'
                  CONSTRAINT cek_status CHECK (status IN ('dipinjam', 'kembali')),
  denda INTEGER GENERATED ALWAYS AS (
    500 * MAX(0, CAST(julianday(COALESCE(tanggal_kembali, tanggal_pinjam))
                      - julianday(tanggal_pinjam) - 7 AS INTEGER))
  ) VIRTUAL,
  CONSTRAINT cek_tanggal_kembali CHECK (
    tanggal_kembali IS NULL OR tanggal_kembali >= tanggal_pinjam
  )
);

CREATE VIEW peminjaman_aktif AS
SELECT p.id, a.nama AS anggota, b.judul, p.tanggal_pinjam
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
JOIN buku b ON b.id = p.buku_id
WHERE p.status = 'dipinjam';

CREATE TRIGGER cek_batas_peminjaman
BEFORE INSERT ON peminjaman
WHEN NEW.status = 'dipinjam'
BEGIN
  SELECT RAISE(ABORT, 'batas peminjaman aktif untuk satu anggota adalah tiga')
  WHERE (
    SELECT COUNT(*) FROM peminjaman
    WHERE anggota_id = NEW.anggota_id AND status = 'dipinjam'
  ) >= 3;
END;

CREATE INDEX idx_buku_kategori ON buku(kategori_id);
CREATE INDEX idx_peminjaman_anggota ON peminjaman(anggota_id);
CREATE INDEX idx_peminjaman_status ON peminjaman(status);
CREATE INDEX idx_peminjaman_tanggal_kembali ON peminjaman(tanggal_kembali);

Beberapa keputusan pada berkas itu memindahkan aturan bisnis ke tingkat basis data. Kolom nomor_anggota diberi UNIQUE sehingga duplikat ditolak tanpa pemeriksaan dari kode aplikasi. CHECK pada kolom status hanya menerima nilai dipinjam atau kembali, sedangkan cek_tanggal_kembali menolak tanggal kembali yang mendahului tanggal pinjamnya. Kolom denda menghitung denda secara otomatis dengan asumsi masa pinjam tujuh hari dan tarif lima ratus rupiah per hari keterlambatan.

Aturan yang tidak mungkin dinyatakan sebagai constraint ditangani view dan trigger. View peminjaman_aktif merangkum query yang paling sering diulang, yaitu daftar peminjaman berstatus dipinjam beserta nama anggota dan judul bukunya. Trigger cek_batas_peminjaman menolak peminjaman baru ketika anggota yang bersangkutan sudah memegang tiga pinjaman aktif, dan RAISE(ABORT) menggagalkan pencatatan tersebut dengan pesan yang dapat dibaca. Satu hal penulisan patut diperhatikan, yaitu constraint tingkat tabel seperti cek_tanggal_kembali diletakkan setelah seluruh definisi kolom karena SQLite menolak definisi kolom yang muncul setelah constraint tingkat tabel. Empat pernyataan CREATE INDEX di akhir berkas dibahas pada bagian Rencana index awal.

Menyiapkan data contoh#

Data contoh dirancang kecil tetapi lengkap, yaitu tujuh kategori dalam dua tingkat, delapan buku, enam anggota, dan tiga belas peminjaman. Tujuh peminjaman berstatus kembali dengan tanggal kembaliannya, sedangkan enam sisanya masih aktif. Nama anggota memakai nama umum, judul buku dibuat netral, dan tidak ada unsur komersial pada datanya.

Seluruh tanggal ditulis sebagai teks berformat ISO 8601. Format ini membuat perbandingan tanggal berjalan dengan benar meski nilainya bertipe TEXT, karena urutan teksnya sejalan dengan urutan waktunya. Tanggal juga menjadi dasar perhitungan denda melalui fungsi julianday, sehingga konsistensi formatnya berdampak langsung pada angka laporan. Pernyataan INSERT diurutkan dari tabel tanpa kunci tamu menuju tabel yang merujuknya, yaitu kategori, buku, anggota, lalu peminjaman.

Anggota Citra sengaja memegang tiga pinjaman aktif pada data contoh. Keadaan ini mengisi batas yang diberlakukan trigger cek_batas_peminjaman sampai tepat pada ambangnya, sehingga percobaan peminjaman keempat dapat diuji pada tahap implementasi. Tanggal pada data adalah nilai contoh yang tetap, bukan tanggal saat berkas dibuat, agar setiap pembaca yang menjalankannya memperoleh angka laporan yang sama persis. Berkas data.sql tidak dijalankan berdiri sendiri karena seluruh INSERT-nya bergantung pada tabel yang diciptakan skema.sql, dan cara menjalankannya diperlihatkan pada bagian berikutnya.

-- tidak-dijalankan: berkas ini dieksekusi setelah skema.sql dalam sesi yang sama
PRAGMA foreign_keys = ON;

INSERT INTO kategori (id, nama, id_induk) VALUES
  (1, 'Sains', NULL),
  (2, 'Fisika', 1),
  (3, 'Astronomi', 1),
  (4, 'Sastra', NULL),
  (5, 'Puisi', 4),
  (6, 'Prosa', 4),
  (7, 'Sejarah', NULL);

INSERT INTO buku (id, judul, penulis, tahun, stok, kategori_id) VALUES
  (1, 'Gerak dan Gaya', 'Hana Prameswari', 2019, 3, 2),
  (2, 'Pengantar Mekanika Klasik', 'Bram Sudibyo', 2017, 2, 2),
  (3, 'Peta Langit Tropis', 'Sari Wulandari', 2021, 2, 3),
  (4, 'Catatan Observasi Bulan', 'Sari Wulandari', 2018, 1, 3),
  (5, 'Antologi Hujan Pagi', 'Damar Cakrawala', 2020, 2, 5),
  (6, 'Novel Kota Tua', 'Laras Ayuningtyas', 2016, 3, 6),
  (7, 'Jalur Kereta Pesisir', 'Bram Sudibyo', 2022, 2, 7),
  (8, 'Kamus Angin Lokal', 'Hana Prameswari', 2015, 4, 1);

INSERT INTO anggota (id, nomor_anggota, nama, tanggal_bergabung) VALUES
  (1, 'A001', 'Ali', '2025-01-10'),
  (2, 'A002', 'Budi', '2025-02-15'),
  (3, 'A003', 'Citra', '2025-03-20'),
  (4, 'A004', 'Dewi', '2025-05-05'),
  (5, 'A005', 'Eka', '2025-08-12'),
  (6, 'A006', 'Farhan', '2025-09-30');

INSERT INTO peminjaman (id, anggota_id, buku_id, tanggal_pinjam, tanggal_kembali, status) VALUES
  (1, 1, 1, '2026-09-01', '2026-09-10', 'kembali'),
  (2, 2, 3, '2026-09-05', '2026-09-12', 'kembali'),
  (3, 3, 5, '2026-09-10', '2026-09-25', 'kembali'),
  (4, 1, 6, '2026-09-15', '2026-09-20', 'kembali'),
  (5, 4, 2, '2026-09-20', '2026-10-02', 'kembali'),
  (6, 5, 4, '2026-09-28', '2026-10-05', 'kembali'),
  (7, 2, 1, '2026-10-03', '2026-10-30', 'kembali'),
  (8, 6, 7, '2026-10-10', '2026-10-14', 'kembali'),
  (9, 3, 2, '2026-10-20', NULL, 'dipinjam'),
  (10, 4, 6, '2026-10-22', NULL, 'dipinjam'),
  (11, 5, 8, '2026-10-25', NULL, 'dipinjam'),
  (12, 3, 3, '2026-10-28', NULL, 'dipinjam'),
  (13, 3, 5, '2026-10-30', NULL, 'dipinjam');

Membuktikan skema valid#

Rancangan yang belum pernah dijalankan hanyalah dugaan, sehingga perencanaan ditutup dengan pembangunan sungguhan. Dua berkas rancangan dikirim ke program sqlite3 sebagai masukan standar (stdin), dan keberhasilannya ditandai dengan tidak munculnya pesan apa pun. Objek yang tercipta kemudian didaftar dari sqlite_schema, yaitu katalog internal yang memuat seluruh objek basis data.

# membangun basis data proyek dari dua berkas rancangan
rm -f perpustakaan.db
sqlite3 perpustakaan.db < skema.sql
sqlite3 perpustakaan.db < data.sql
# mendaftar seluruh objek yang tercipta
sqlite3 perpustakaan.db "SELECT type, name FROM sqlite_schema WHERE name NOT LIKE 'sqlite_%' ORDER BY type DESC, name;"

view|peminjaman_aktif
trigger|cek_batas_peminjaman
table|anggota
table|buku
table|kategori
table|peminjaman
index|idx_buku_kategori
index|idx_peminjaman_anggota
index|idx_peminjaman_status
index|idx_peminjaman_tanggal_kembali

Daftar di atas membuktikan empat tabel, satu view, satu trigger, dan empat index benar tercipta. Tampilan dikelompokkan menurut jenisnya karena query mengurutkan kolom type secara menurun. Apabila salah satu pernyataan di dalam skema.sql mengandung error, sqlite3 menampilkan pesan pada baris yang gagal, sehingga perbaikannya dapat dituntun dari situ. Pembuktian semacam ini murah dilakukan, sedangkan nilainya besar karena seluruh tahap berikutnya berdiri di atas berkas yang sudah terbukti valid.

Rencana index awal#

Rencana index disusun dari daftar query yang diperkirakan paling sering dijalankan, bukan dari daftar kolom yang ada. Setiap query padat dipasangkan dengan syarat yang menjadi beban utamanya, lalu index yang melayani syarat itu dinyatakan secara eksplisit di dalam skema.sql. Tabel berikut merangkum pasangan tersebut untuk aplikasi perpustakaan ini.

Tiga pertimbangan menentukan isi tabel di atas. Pertama, kolom kunci tamu tidak terindeks otomatis, sehingga idx_buku_kategori dan idx_peminjaman_anggota ditulis eksplisit. Kedua, arah join menentukan perlunya index, dan query yang membaca judul buku dari peminjaman bergerak dari peminjaman ke buku melalui kolom id. Kolom id sudah menjadi jalur pencarian tercepat karena alias rowid, sehingga index pada peminjaman.buku_id sengaja tidak diciptakan. Ketiga, view peminjaman_aktif menyaring menurut status, dan filter itulah yang dilayani idx_peminjaman_status.

Rencana ini bukan garis akhir. EXPLAIN QUERY PLAN akan menunjukkan index mana yang benar-benar dipilih oleh query planner, yaitu komponen yang memilih rencana eksekusi. Audit pada masa pengoperasian dapat membuang index yang tidak pernah terpakai atau menambahkan index gabungan ketika pola query yang muncul lebih spesifik daripada perkiraan awal.

Ringkasan#

• Kebutuhan aplikasi menurunkan empat tabel, yaitu kategori bertingkat, buku, anggota, dan peminjaman, ditambah view peminjaman_aktif dan trigger pembatas tiga pinjaman aktif.
• Berkas skema.sql memuat seluruh constraint, view, trigger, dan index, sedangkan data.sql memuat data contoh yang tetap agar angka laporan dapat direproduksi.
• Pembangunan dari kedua berkas dijalankan sejak tahap perencanaan agar error rancangan terlihat lebih dahulu.
• Rencana index dipasangkan dengan syarat query yang dilayani, termasuk keputusan tidak membuat index pada peminjaman.buku_id karena joinnya sudah dilayani pencarian rowid.

Referensi#

• CREATE TABLE — SQLite — Sintaks constraint tabel, kolom yang dihasilkan, dan aturan penempatannya.
• Foreign Key Support — SQLite — Kebijakan SQLite mengenai kunci tamu, termasuk ketiadaan index otomatis pada kolom perujuk.
• CREATE INDEX — SQLite — Sintaks index satu kolom dan index komposit yang melayani syarat query.