Setiap database yang baik lahir dari kebutuhan yang dituliskan dengan jelas, bukan dari CREATE TABLE yang asal jadi. Pada bagian ini kita berperan sebagai perancang: kita terjemahkan kebutuhan petugas perpustakaan sekolah menjadi daftar entitas, tentukan relasi di antaranya, baru tulis skema perpustakaan_db dalam SQL. Skema hasil bagian ini menjadi fondasi tiga bagian berikutnya — data diisi di Bagian 2, alur pinjam-kembali dijalankan di Bagian 3, dan laporan disusun di Bagian 4.

Menganalisis Kebutuhan Sistem #

Mari kita bayangkan percakapan dengan petugas perpustakaan. Petugas menyebutkan hal-hal yang dikelola setiap hari: koleksi buku yang tiap judulnya punya beberapa eksemplar, siswa yang menjadi anggota, transaksi peminjaman yang mencatat siapa meminjam apa dan kapan harus kembali, serta pengembalian yang membuat buku tersedia kembali. Dari daftar tersebut kita tarik kebutuhan data yang konkret:

  • setiap buku punya kode unik, judul, penulis, tahun terbit, dan jumlah eksemplar yang tersedia di rak;
  • setiap anggota adalah siswa dengan nama, kelas, dan nomor telepon yang boleh saja kosong karena tidak semua siswa punya telepon;
  • satu peminjaman memuat anggota, tanggal pinjam, batas tanggal kembali, dan tanggal kembali efektif yang masih kosong selama buku belum kembali;
  • satu peminjaman boleh memuat beberapa judul sekaligus dengan jumlah eksemplar berbeda-beda.

Pola klasik perancangan relational database kita pakai di sini: kata benda menjadi kandidat tabel, kata kerja menjadi kandidat relasi. Buku, anggota, dan peminjaman jelas menjadi tabel; kebutuhan “beberapa judul sekaligus” menuntut satu tabel tambahan sebagai perinci.

Entitas dan Relasi #

Hubungan antar tabel kita gambar lebih dulu sebelum menulis satu baris SQL pun. Diagram berikut merangkum seluruh relasi sistem:

Terdapat dua relasi dengan sifat berbeda. Relasi anggota–peminjaman berjenis satu-ke-banyak: satu anggota boleh membuat banyak peminjaman sepanjang semester, tetapi satu peminjaman hanya milik satu anggota — foreign key diletakkan di tabel peminjaman. Sebaliknya, relasi buku–peminjaman berjenis banyak-ke-banyak: satu peminjaman memuat banyak judul, dan satu judul dipinjam di banyak peminjaman berbeda. Pola junction table dari section relational-database-design menyelesaikannya: tabel detail_peminjaman menjadi perantara yang menyimpan pasangan peminjaman_id + buku_kode beserta jumlah eksemplarnya.

Ringkasan kolom tiap tabel:

TabelKolom kunciKolom dataCatatan
bukukode_buku (PK)judul, penulis, tahun, stokstok tidak boleh negatif (CHECK)
anggotaid (PK, AUTO_INCREMENT)nama, kelas, telepontelepon boleh NULL
peminjamanid (PK), anggota_id (FK)tanggal_pinjam, tanggal_harus_kembali, tanggal_kembalitanggal_kembali NULL = masih dipinjam
detail_peminjaman(peminjaman_id, buku_kode) PK gabunganjumlahdua FK ke tabel induknya

Seluruh perancangan di atas kita tuangkan ke berkas database/skema.sql. Blok berikut berisi pernyataan yang sama dengan berkas tersebut — berkas aslinya menyertakan kepala komentar berisi tujuan dan cara pakai, sedangkan komentar penjelas tiap tabel bisa dibaca di bedah setelah blok:

sql 
DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);

CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);

CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);

CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

Output yang Dihasilkan:

text 
Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 1 row affected (0.00 sec)

Database changed
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Mari kita bedah keputusan penting pada skema di atas:

  1. DROP DATABASE IF EXISTS di baris pembuka membuat skema aman dijalankan ulang: database lama dibuang lebih dulu, sehingga skema selalu menghasilkan kondisi bersih yang sama. Seluruh bagian proyek ini memakai pola yang sama agar tiap bagian bisa diuji mandiri.
  2. kode_buku VARCHAR(15) PRIMARY KEY — perpustakaan sekolah sudah terbiasa memberi kode fisik pada buku (misalnya BK-001), sehingga kode itu layak menjadi primary key alami tanpa perlu AUTO_INCREMENT.
  3. CONSTRAINT chk_buku_stok CHECK (stok >= 0) — MySQL 8.4 menegakkan CHECK constraint sungguhan; transaksi yang mencoba mengurangi stok sampai negatif akan ditolak. Kita akan membuktikan penolakan ini di Bagian 3.
  4. telepon VARCHAR(15) NULL — kolom boleh kosong secara eksplisit; pilihan ini diakui MySQL sebagai YES pada kolom Null hasil DESC.
  5. Foreign key bernama (fk_peminjaman_anggota, fk_detail_buku) memudahkan membaca pesan error: MySQL menyebut nama constraint saat relasi dilanggar.
  6. PRIMARY KEY (peminjaman_id, buku_kode) — primary key gabungan menjamin satu judul hanya muncul sekali per peminjaman sekaligus menghemat satu kolom identitas; jumlah eksemplar judul yang sama tinggal dinaikkan lewat kolom jumlah.

Memverifikasi Struktur #

Struktur baru saja dibuat, tetapi perancang yang teliti tidak percaya sebelum melihat buktinya. Kita periksa daftar tabel, lalu kerangka kolom kedua tabel terpenting — tabel induk buku dan tabel perinci detail_peminjaman:

sql 
SHOW TABLES;

Output yang Dihasilkan:

text 
+---------------------------+
| Tables_in_perpustakaan_db |
+---------------------------+
| anggota                   |
| buku                      |
| detail_peminjaman         |
| peminjaman                |
+---------------------------+
4 rows in set (0.00 sec)
sql 
DESC buku;

Output yang Dihasilkan:

text 
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| kode_buku | varchar(15)  | NO   | PRI | NULL    |       |
| judul     | varchar(100) | NO   |     | NULL    |       |
| penulis   | varchar(80)  | NO   |     | NULL    |       |
| tahun     | year         | NO   |     | NULL    |       |
| stok      | int          | NO   |     | 0       |       |
+-----------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
sql 
DESC detail_peminjaman;

Output yang Dihasilkan:

text 
+---------------+-------------+------+-----+---------+-------+
| Field         | Type        | Null | Key | Default | Extra |
+---------------+-------------+------+-----+---------+-------+
| peminjaman_id | int         | NO   | PRI | NULL    |       |
| buku_kode     | varchar(15) | NO   | PRI | NULL    |       |
| jumlah        | int         | NO   |     | NULL    |       |
+---------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

Hasil DESC detail_peminjaman layak disimak lebih dekat. Kolom Key bernilai PRI pada dua kolom sekaligus — peminjaman_id dan buku_kode — yang menandakan primary key gabungan bekerja: pasangan keduanya yang harus unik, bukan masing-masing kolom. Pada tabel buku, nilai Default kolom stok tampil sebagai 0, bukti bahwa DEFAULT 0 pada skema dikenali dan anggota CHECK tidak mengubah tampilan kolom — constraint tetap siaga di balik layar sampai dilanggar.

Referensi #

Skema perpustakaan_db kini berdiri lengkap: empat tabel, tiga foreign key bernama, satu check constraint, dan primary key gabungan di tabel perinci. Struktur yang kosong ini menunggu isi — pada Bagian 2 kita mengisi katalog buku dan data anggota, lalu membuktikan bahwa constraint di skema ini benar-benar menolak data yang tidak valid.

Berkas Sumber

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

database/data-awal.sql

Unduh berkas
sql 
-- data-awal.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: mengisi data contoh deterministik (buku dan anggota fiktif)
--   sebagai titik awal konsisten untuk seluruh bagian proyek.
-- Cara pakai: jalankan setelah skema.sql:
--   mysql -u root -p perpustakaan_db < database/data-awal.sql

INSERT INTO buku (kode_buku, judul, penulis, tahun, stok) VALUES
  ('BK-001', 'Laskar Pelangi', 'Andrea Hirata', 2005, 3),
  ('BK-002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 1980, 2),
  ('BK-003', 'Hujan Bulan Juni', 'Sapardi Djoko Damono', 1994, 2),
  ('BK-004', 'Dilan 1990', 'Pidi Baiq', 2014, 4),
  ('BK-005', 'Atomic Habits', 'James Clear', 2018, 2),
  ('BK-006', 'Matematika untuk SMA Kelas X', 'Tim Pusat Kurikulum', 2019, 6),
  ('BK-007', 'Kamus Besar Bahasa Indonesia', 'Badan Pengembangan Bahasa', 2016, 2),
  ('BK-008', 'Fisika Terapan untuk SMK', 'Ratna Wijaya', 2021, 3);

INSERT INTO anggota (nama, kelas, telepon) VALUES
  ('Budi Santoso', 'X-IPA-1', '081234560001'),
  ('Siti Rahmawati', 'X-IPA-2', '081234560002'),
  ('Andi Wijaya', 'XI-IPA-1', '081234560003'),
  ('Dewi Lestari', 'XI-IPS-1', '081234560004'),
  ('Rina Marlina', 'XII-IPA-1', '081234560005'),
  ('Tono Sutrisno', 'XII-IPS-1', NULL),
  ('Sari Puspita', 'X-IPS-1', '081234560007'),
  ('Joko Prasetyo', 'XI-IPS-2', NULL);

database/skema.sql

Unduh berkas
sql 
-- skema.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: membuat database perpustakaan_db beserta keempat tabelnya
--   (buku, anggota, peminjaman, detail_peminjaman) lengkap dengan
--   primary key, foreign key, dan check constraint.
-- Cara pakai: mysql -u root -p < database/skema.sql

DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

-- Katalog buku; kolom stok menyimpan jumlah eksemplar yang tersedia di rak.
CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);

-- Anggota perpustakaan (siswa); telepon boleh kosong (NULL).
CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);

-- Satu baris per transaksi peminjaman; tanggal_kembali NULL berarti masih dipinjam.
CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);

-- Rincian buku per peminjaman; jembatan banyak-ke-banyak antara buku dan peminjaman.
CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

queries/laporan-anggota-aktif.sql

Unduh berkas
sql 
-- laporan-anggota-aktif.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menampilkan frekuensi peminjaman dan total buku yang dipinjam
--   tiap anggota, termasuk anggota yang belum pernah meminjam.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-anggota-aktif.sql

SELECT a.id, a.nama, a.kelas,
       COUNT(DISTINCT p.id) AS jumlah_peminjaman,
       COALESCE(SUM(dp.jumlah), 0) AS total_buku
FROM anggota a
LEFT JOIN peminjaman p ON p.anggota_id = a.id
LEFT JOIN detail_peminjaman dp ON dp.peminjaman_id = p.id
GROUP BY a.id, a.nama, a.kelas
ORDER BY jumlah_peminjaman DESC, a.nama;

queries/laporan-buku-terpopuler.sql

Unduh berkas
sql 
-- laporan-buku-terpopuler.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: memeringkat buku berdasarkan total eksemplar yang dipinjam,
--   termasuk buku yang belum pernah dipinjam, memakai window function RANK().
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-buku-terpopuler.sql

SELECT b.kode_buku, b.judul,
       COALESCE(SUM(dp.jumlah), 0) AS total_dipinjam,
       RANK() OVER (ORDER BY COALESCE(SUM(dp.jumlah), 0) DESC) AS peringkat
FROM buku b
LEFT JOIN detail_peminjaman dp ON dp.buku_kode = b.kode_buku
GROUP BY b.kode_buku, b.judul
ORDER BY total_dipinjam DESC, b.judul;

queries/laporan-terlambat.sql

Unduh berkas
sql 
-- laporan-terlambat.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menemukan peminjaman yang melewati batas tanggal_harus_kembali
--   dan belum dikembalikan, dihitung terhadap tanggal acuan literal.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/laporan-terlambat.sql
-- Ganti tanggal acuan '2026-02-28' sesuai hari laporan dijalankan.

SELECT p.id, a.nama, a.kelas, p.tanggal_pinjam, p.tanggal_harus_kembali,
       DATEDIFF('2026-02-28', p.tanggal_harus_kembali) AS hari_terlambat
FROM peminjaman p
JOIN anggota a ON a.id = p.anggota_id
WHERE p.tanggal_kembali IS NULL
  AND p.tanggal_harus_kembali < '2026-02-28'
ORDER BY hari_terlambat DESC;

queries/stok-tersedia.sql

Unduh berkas
sql 
-- stok-tersedia.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: menampilkan stok tiap judul berdampingan dengan jumlah eksemplar
--   yang sedang dipinjam, sehingga kecukupan koleksi terlihat sekali baca.
-- Cara pakai: mysql -u root -p perpustakaan_db < queries/stok-tersedia.sql

SELECT b.kode_buku, b.judul, b.stok AS stok_tersedia,
       COALESCE(dipinjam.sedang, 0) AS sedang_dipinjam
FROM buku b
LEFT JOIN (
  SELECT dp.buku_kode, SUM(dp.jumlah) AS sedang
  FROM detail_peminjaman dp
  JOIN peminjaman p ON p.id = dp.peminjaman_id
  WHERE p.tanggal_kembali IS NULL
  GROUP BY dp.buku_kode
) dipinjam ON dipinjam.buku_kode = b.kode_buku
ORDER BY b.kode_buku;

tests/pemeriksaan.sql

Unduh berkas
sql 
-- pemeriksaan.sql — Proyek Database Manajemen Perpustakaan
-- Tujuan: pemeriksaan akhir seluruh fitur proyek (skema, peminjaman,
--   pengembalian, laporan terpopuler, dan deteksi keterlambatan).
--   Berkas ini MANDIRI: membangun skenario uji kecilnya sendiri,
--   lalu membersihkan database setelah selesai.
-- Cara pakai: mysql -u root -p < tests/pemeriksaan.sql
-- Ekspektasi tiap hasil ditulis sebagai komentar di atas query terkait.

DROP DATABASE IF EXISTS perpustakaan_db;
CREATE DATABASE perpustakaan_db;
USE perpustakaan_db;

CREATE TABLE buku (
  kode_buku VARCHAR(15) PRIMARY KEY,
  judul VARCHAR(100) NOT NULL,
  penulis VARCHAR(80) NOT NULL,
  tahun YEAR NOT NULL,
  stok INT NOT NULL DEFAULT 0,
  CONSTRAINT chk_buku_stok CHECK (stok >= 0)
);
CREATE TABLE anggota (
  id INT AUTO_INCREMENT PRIMARY KEY,
  nama VARCHAR(60) NOT NULL,
  kelas VARCHAR(10) NOT NULL,
  telepon VARCHAR(15) NULL
);
CREATE TABLE peminjaman (
  id INT AUTO_INCREMENT PRIMARY KEY,
  anggota_id INT NOT NULL,
  tanggal_pinjam DATE NOT NULL,
  tanggal_harus_kembali DATE NOT NULL,
  tanggal_kembali DATE NULL,
  CONSTRAINT fk_peminjaman_anggota
    FOREIGN KEY (anggota_id) REFERENCES anggota(id)
);
CREATE TABLE detail_peminjaman (
  peminjaman_id INT NOT NULL,
  buku_kode VARCHAR(15) NOT NULL,
  jumlah INT NOT NULL,
  PRIMARY KEY (peminjaman_id, buku_kode),
  CONSTRAINT fk_detail_peminjaman
    FOREIGN KEY (peminjaman_id) REFERENCES peminjaman(id),
  CONSTRAINT fk_detail_buku
    FOREIGN KEY (buku_kode) REFERENCES buku(kode_buku)
);

INSERT INTO buku (kode_buku, judul, penulis, tahun, stok) VALUES
  ('BK-001', 'Laskar Pelangi', 'Andrea Hirata', 2005, 3),
  ('BK-002', 'Bumi Manusia', 'Pramoedya Ananta Toer', 1980, 2),
  ('BK-003', 'Hujan Bulan Juni', 'Sapardi Djoko Damono', 1994, 2);
INSERT INTO anggota (nama, kelas, telepon) VALUES
  ('Budi Santoso', 'X-IPA-1', '081234560001'),
  ('Siti Rahmawati', 'X-IPA-2', '081234560002');

-- Pemeriksaan 1: peminjaman mengurangi stok secara atomik.
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (1, '2026-01-05', '2026-01-12');
INSERT INTO detail_peminjaman VALUES (1, 'BK-001', 2);
UPDATE buku SET stok = stok - 2 WHERE kode_buku = 'BK-001';
COMMIT;
-- Ekspektasi: BK-001 stok 1
SELECT kode_buku, stok FROM buku WHERE kode_buku = 'BK-001';

-- Pemeriksaan 2: pengembalian mengembalikan stok dan menutup pinjaman.
UPDATE peminjaman SET tanggal_kembali = '2026-01-10' WHERE id = 1;
UPDATE buku SET stok = stok + 2 WHERE kode_buku = 'BK-001';
-- Ekspektasi: BK-001 stok 3
SELECT kode_buku, stok FROM buku WHERE kode_buku = 'BK-001';
-- Ekspektasi: peminjaman 1 sudah memiliki tanggal_kembali
SELECT id, tanggal_kembali FROM peminjaman WHERE id = 1;

-- Pemeriksaan 3: peminjaman kedua (id 2) selesai, peminjaman ketiga (id 3) terbuka.
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (2, '2026-01-15', '2026-01-22');
INSERT INTO detail_peminjaman VALUES (2, 'BK-002', 1), (2, 'BK-001', 1);
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-002';
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-001';
UPDATE peminjaman SET tanggal_kembali = '2026-01-20' WHERE id = 2;
UPDATE buku SET stok = stok + 1 WHERE kode_buku = 'BK-002';
UPDATE buku SET stok = stok + 1 WHERE kode_buku = 'BK-001';
COMMIT;
START TRANSACTION;
INSERT INTO peminjaman (anggota_id, tanggal_pinjam, tanggal_harus_kembali)
  VALUES (2, '2026-02-01', '2026-02-08');
INSERT INTO detail_peminjaman VALUES (3, 'BK-001', 1);
UPDATE buku SET stok = stok - 1 WHERE kode_buku = 'BK-001';
COMMIT;
-- Ekspektasi: BK-001 total 4, BK-002 total 1, BK-003 total 0
SELECT b.kode_buku,
       COALESCE(SUM(dp.jumlah), 0) AS total_dipinjam
FROM buku b
LEFT JOIN detail_peminjaman dp ON dp.buku_kode = b.kode_buku
GROUP BY b.kode_buku
ORDER BY total_dipinjam DESC, b.kode_buku;

-- Pemeriksaan 4: hanya peminjaman 3 (harus kembali 2026-02-08) yang terlambat.
-- Ekspektasi: 1 baris - peminjaman 3, 20 hari terlambat
SELECT p.id, DATEDIFF('2026-02-28', p.tanggal_harus_kembali) AS hari_terlambat
FROM peminjaman p
WHERE p.tanggal_kembali IS NULL
  AND p.tanggal_harus_kembali < '2026-02-28'
ORDER BY p.id;

-- Pemeriksaan 5: stok akhir konsisten dengan seluruh mutasi.
-- Ekspektasi: BK-001 stok 2, BK-002 stok 2, BK-003 stok 2
SELECT kode_buku, stok FROM buku ORDER BY kode_buku;

DROP DATABASE perpustakaan_db;