Skema adalah fondasi yang menanggung seluruh sisa proyek. Database perpustakaan akan dipakai bertahun-tahun, dan kesalahan desain yang lolos hari ini menjalar ke semua query esok hari. Pada bagian ini kita memutuskan tabel apa saja yang dibutuhkan, kolom apa yang dimiliki setiap tabel, dan constraint apa yang menjaga isinya — lalu menuliskan keputusan tersebut ke schema.sql dan menguji bahwa penegakannya benar-benar bekerja. Materi intinya berasal dari Chapter Normalisasi Dasar, Merancang Relasi One-to-Many, dan Foreign Key.

Entitas dan Relasinya #

Dari cara kerja perpustakaan, kita menemukan lima entitas yang perlu tabel sendiri:

TabelPeranRelasi utama
raklokasi fisik koleksisatu rak menampung banyak buku
kategoriklasifikasi koleksisatu kategori menampung banyak buku
bukudata satu judul koleksimenunjuk satu kategori dan satu rak
anggotaorang terdaftar pemakai layanansatu anggota punya banyak peminjaman
peminjamanperistiwa meminjam satu bukumenunjuk satu buku dan satu anggota

Tiga keputusan desain di balik tabel tersebut layak dibedah:

Kategori menjadi tabel terpisah. Kita bisa saja menyimpan kolom teks kategori langsung pada buku, tetapi nama kategori akan ditulis ulang pada setiap baris dan satu salah ketik akan memecah kategori “Fiksi” menjadi dua — pokok bahasan Chapter Normalisasi Dasar. Dengan tabel kategori, setiap nama ditulis sekali dan buku cukup menyimpan angka kategori_id.

Tidak ada tabel perantara. Relasi buku–kategori cukup satu-ke-banyak karena satu judul masuk tepat satu kategori. Chapter Merancang Relasi Many-to-Many membahas tabel perantara untuk relasi seperti buku–penulis: satu buku bisa dikarya banyak penulis dan satu penulis menulis banyak buku. Katalog kita menyederhanakannya menjadi satu kolom penulis karena cukup mencatat penulis utama setiap judul.

peminjaman adalah tabel peristiwa. Dua relasi one-to-many bertemu di tabel ini: banyak peminjaman untuk satu buku, dan banyak peminjaman untuk satu anggota. Kolom tanggal_kembali sengaja boleh kosong — nilai NULL berarti buku masih berada di tangan anggota, pola yang dibahas pada Chapter Operator Lanjutan: IN, BETWEEN, IS NULL.

Kode Lengkap Bagian 1 #

Simpan listing berikut sebagai schema.sql:

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

Mari kita bedah keputusan-keputusan pentingnya:

  1. PRAGMA foreign_keys = ON; — SQLite tidak menegakkan foreign key secara bawaan; penegakan aktif per koneksi (Chapter Foreign Key). Baris ini memastikan skema dijalankan dengan penegakan menyala, dan seed.sql pada Bagian 2 mengulang pragma yang sama.
  2. CHECK (tahun >= 1900) — menolak tahun terbit yang janggal. Koleksi kita memang dimulai dari penerbitan abad ke-20, sehingga angka seperti 1899 hampir pasti salah ketik, bukan data sah.
  3. isbn TEXT NOT NULL UNIQUE — ISBN adalah identitas buku di dunia nyata. UNIQUE mencegah satu judul tercatat dua kali dengan data ganda.
  4. REFERENCES kategori(id) ON DELETE RESTRICT — kategori dan rak tidak boleh dihapus selama masih dipakai buku. Tanpa RESTRICT, satu DELETE yang tidak sengaja bisa meninggalkan buku tanpa kategori.
  5. ON DELETE CASCADE pada peminjaman — saat buku atau anggota dihapus dari database, catatan peminjamannya ikut terhapus. Riwayat peminjaman tanpa buku dan tanpa anggotanya tidak bermakna, jadi tidak pernah dibiarkan yatim.
  6. CHECK (tanggal_kembali IS NULL OR tanggal_kembali >= tanggal_pinjam) — contoh CHECK lintas kolom: buku tidak mungkin dikembalikan sebelum dipinjam.

Membangun dan Menguji Skema #

Bangun database baru dengan skema tersebut:

bash 
sqlite3 perpustakaan.db < schema.sql

Perintah tersebut selesai tanpa keluhan — sukses pada SQLite memang sunyi. Struktur hasilnya dapat diperiksa dengan .schema; berikut tampilan untuk tabel yang paling padat aturan, yaitu peminjaman:

bash 
sqlite3 perpustakaan.db ".schema peminjaman"

Output yang Dihasilkan:

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

Sekarang kita uji bahwa penegakan constraint benar-benar bekerja. Agar perpustakaan.db tetap bersih untuk Bagian 2, jalankan skema ke database uji terpisah, lalu buka sesi interaktif padanya:

bash 
sqlite3 uji.db < schema.sql
sqlite3 uji.db

Sesi berikut menunjukkan penolakan dua data rusak, penerimaan satu data valid, dan nasib DELETE pada kedua ujung relasi:

text 
sqlite> PRAGMA foreign_keys = ON;
sqlite> INSERT INTO kategori (id, nama) VALUES (1, 'Fiksi');
sqlite> INSERT INTO rak (kode, lokasi) VALUES ('R1', 'Lantai 1, dekat pintu masuk');
sqlite> INSERT INTO buku (judul, penulis, tahun, isbn, kategori_id, rak_kode, jumlah_tersedia)
   ...> VALUES ('Bumi Manusia', 'Pramoedya Ananta Toer', 1980, '9789799731234', 99, 'R1', 3);
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> INSERT INTO buku (judul, penulis, tahun, isbn, kategori_id, rak_kode, jumlah_tersedia)
   ...> VALUES ('Bumi Manusia', 'Pramoedya Ananta Toer', 1980, '9789799731234', 1, 'R1', -3);
Runtime error: CHECK constraint failed: jumlah_tersedia >= 0 (19)
sqlite> INSERT INTO buku (judul, penulis, tahun, isbn, kategori_id, rak_kode, jumlah_tersedia)
   ...> VALUES ('Bumi Manusia', 'Pramoedya Ananta Toer', 1980, '9789799731234', 1, 'R1', 3);
sqlite> DELETE FROM kategori WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed (19)
sqlite> SELECT id, judul, jumlah_tersedia FROM buku;
1|Bumi Manusia|3
sqlite> INSERT INTO anggota (id, nama, email, tanggal_gabung) VALUES (1, 'Budi Santoso', '[email protected]', '2024-01-15');
sqlite> INSERT INTO peminjaman (buku_id, anggota_id, tanggal_pinjam) VALUES (1, 1, '2025-05-05');
sqlite> SELECT id, buku_id, anggota_id, tanggal_pinjam, tanggal_kembali FROM peminjaman;
1|1|1|2025-05-05|
sqlite> DELETE FROM buku WHERE id = 1;
sqlite> SELECT COUNT(*) AS sisa_peminjaman FROM peminjaman;
0
sqlite>

Mari kita baca peristiwa dalam sesi tersebut satu per satu:

  1. INSERT pertama dan kedua untuk buku ditolak: yang pertama karena kategori_id 99 tidak ada (FOREIGN KEY constraint failed), yang kedua karena jumlah_tersedia -3 melanggar CHECK.
  2. INSERT ketiga lolos — kategori_id 1 dan rak R1 memang sudah dibuatkan barisnya di awal sesi.
  3. DELETE FROM kategori WHERE id = 1 ditolak RESTRICT: masih ada buku yang menunjuk kategori tersebut, jadi penghapusan ditahan.
  4. Setelah peminjaman untuk buku 1 dicatat, DELETE FROM buku WHERE id = 1 justru berhasil — CASCADE menghapus baris peminjaman yang menunjuk buku tersebut, terbukti dari sisa_peminjaman bernilai 0.

Terakhir, periksa integritas database yang baru dibangun:

bash 
sqlite3 perpustakaan.db "PRAGMA foreign_key_check;"
sqlite3 perpustakaan.db "PRAGMA integrity_check;"

Output yang Dihasilkan:

text 
ok

PRAGMA foreign_key_check memeriksa pelanggaran foreign key tanpa memerlukan penegakan aktif; tidak mencetak apa pun berarti tidak ada baris yatim. PRAGMA integrity_check memeriksa struktur berkas database dan menjawab ok — keduanya dibahas pada Chapter Integrity Check.

Skema kini berdiri dengan penjaga yang terbukti bekerja. Lemarinya masih kosong — Bagian 2 mengisinya. Berkas lengkap bagian ini (schema.sql) tersedia pada bagian Berkas Sumber di bawah halaman.

Berkas Sumber

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

schema.sql

Unduh berkas
sql 
-- 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)
);