Kita telah menempuh hampir seluruh Course MySQL — mulai dari CREATE TABLE, foreign key, transaksi, sampai window function. Proyek Database Manajemen Perpustakaan merangkai semuanya menjadi satu database yang benar-benar dipakai: sistem perpustakaan sekolah yang mencatat koleksi buku, anggota siswa, transaksi peminjaman, dan pengembaliannya. Proyek ini dikerjakan dalam empat bagian berurutan; setiap bagian membangun lapisan baru di atas bagian sebelumnya, dan setiap bagian dapat diuji ulang dari awal karena selalu membangun kembali database-nya sendiri.

Tujuan Proyek #

Sistem yang kita bangun harus mampu menjawab kebutuhan nyata petugas perpustakaan sekolah:

  • menyimpan katalog buku beserta jumlah eksemplar yang tersedia di rak,
  • mendata anggota siswa lengkap dengan kelas dan nomor telepon yang boleh kosong,
  • mencatat peminjaman yang bisa memuat beberapa judul sekaligus,
  • mengembalikan stok buku saat eksemplar dikembalikan,
  • menjaga konsistensi stok dengan transaksi dan CHECK constraint,
  • menyajikan laporan: buku terpopuler, anggota paling aktif, dan peminjaman yang melewati batas waktu.

Setiap kebutuhan tersebut dipetakan ke fitur MySQL yang sudah kita pelajari: skema relasional dengan foreign key untuk struktur data, INSERT/UPDATE/DELETE untuk pengelolaan sehari-hari, START TRANSACTION ... COMMIT untuk alur pinjam-kembali yang atomik, dan JOIN + fungsi agregat + window function untuk laporan.

Prasyarat #

Proyek ini memakai materi dari beberapa section sebelumnya. Sebelum mulai, pastikan kita sudah nyaman dengan topik berikut:

MateriDipakai untuk
dasar-sql — membuat database dan tabelBagian 1: perancangan skema
relational-database-design — relasi dan junction tableBagian 1: relasi banyak-ke-banyak buku-peminjaman
manipulasi-data — INSERT, UPDATE, DELETEBagian 2: pengelolaan buku dan anggota
transactions — COMMIT dan ROLLBACKBagian 3: alur peminjaman dan pengembalian
agregasi-data dan window-functionsBagian 4: laporan dan peringkat

Peta Bagian #

Proyek dikerjakan dalam empat bagian. Setiap bagian merujuk ke berkas SQL yang sama sehingga kondisi proyek tetap utuh di setiap tahap:

  1. Bagian 1: Perancangan Skema — menganalisis kebutuhan, mengidentifikasi entitas dan relasi, lalu menulis skema perpustakaan_db lengkap dengan primary key, foreign key, dan CHECK constraint.
  2. Bagian 2: Pengelolaan Buku dan Anggota — mengisi data awal, menambah dan mengubah data, serta membuktikan constraint bekerja menolak data yang tidak valid.
  3. Bagian 3: Peminjaman dan Pengembalian — menjalankan alur inti perpustakaan dalam transaksi: stok berkurang saat dipinjam dan kembali penuh saat dikembalikan.
  4. Bagian 4: Laporan dan Query Lanjutan — menyusun laporan buku terpopuler, anggota aktif, dan keterlambatan, lalu menjalankan berkas pemeriksaan akhir seluruh proyek.

Struktur Berkas Proyek #

Seluruh berkas SQL proyek hidup di dalam bundle ini dan tampil pada bagian Berkas Sumber di bawah halaman:

text 
database-manajemen-perpustakaan/
├── database/skema.sql          -- CREATE DATABASE + keempat tabel
├── database/data-awal.sql      -- data contoh buku dan anggota
├── queries/stok-tersedia.sql   -- stok rak vs yang sedang dipinjam
├── queries/laporan-buku-terpopuler.sql
├── queries/laporan-anggota-aktif.sql
├── queries/laporan-terlambat.sql
└── tests/pemeriksaan.sql       -- pemeriksaan akhir, mandiri dan deterministik

Diagram berikut merangkum struktur data yang kita bangun — tiga tabel inti dihubungkan oleh tabel detail_peminjaman sebagai jembatan:

Relasi antara anggota dan peminjaman berjenis satu-ke-banyak: satu anggota boleh berkali-kali meminjam. Sebaliknya, buku dan peminjaman berhubungan banyak-ke-banyak — satu peminjaman memuat banyak judul, dan satu judul muncul di banyak peminjaman — sehingga membutuhkan tabel detail_peminjaman sebagai perantara. Detail rancangan tiap kolom kita bahas tuntas di Bagian 1.

Mulailah dari Bagian 1 dan ikuti urutannya, karena setiap bagian dibangun di atas konsep dan data dari bagian sebelumnya.

# Bagian 1: Perancangan Skema

Analisis kebutuhan perpustakaan sekolah, identifikasi entitas dan relasinya, lalu tulis skema perpustakaan_db lengkap dengan primary key, foreign key, dan check constraint — diverifikasi dengan SHOW TABLES dan DESC.

Buka → # Bagian 2: Pengelolaan Buku dan Anggota

Isi data awal katalog buku dan anggota, jalankan operasi CRUD sehari-hari petugas perpustakaan, dan buktikan constraint skema menolak kode buku ganda serta peminjaman dari anggota yang tidak ada.

Buka → # Bagian 3: Peminjaman dan Pengembalian

Jalankan alur inti perpustakaan dalam transaksi: peminjaman multi-judul yang mengurangi stok, pengembalian yang memulihkannya, pembatalan dengan ROLLBACK, dan penolakan pinjaman melebihi stok oleh CHECK constraint.

Buka → # Bagian 4: Laporan dan Query Lanjutan

Susun laporan perpustakaan dari dua bulan riwayat — buku terpopuler dengan RANK(), anggota terpasif, dan keterlambatan terhadap tanggal acuan — lalu jalankan berkas pemeriksaan akhir yang menguji seluruh fitur proyek.

Buka →

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;