Bagian 2: Pengelolaan Produk dan Pelanggan

Laporan analitik hanya sebaik data masternya. Kode produk yang tertukar, email pelanggan ganda, atau harga yang tersimpan nol — semuanya menjalar menjadi angka laporan yang salah dan keputusan belanja yang keliru. Pada bagian ini kita menjalankan data master Toko Maju Jaya selama satu hari operasional: produk baru masuk katalog, harga dikoreksi, pelanggan baru mendaftar, sistem pusat mengirim pembaruan katalog, kasir mencari produk di tengah antrean, dan manajer menghapus data yang memang tidak terpakai — semuanya dengan database yang berdiri sebagai penjaga terakhir keabsahan data.

Fondasi Bagian #

Berkas pembuka membangun ulang skema Bagian 1 lalu mengisinya dengan data master dan satu pesanan historis dari 20 Desember 2025. Pesanan lama ini penting untuk dijelaskan: stok awal Gula Pasir dan Kopi Sachet sengaja dimuat lebih tinggi (65 dan 90) supaya setelah pengurangan stok pesanan Desember selesai, keduanya tepat berada di angka 60 dan 80 — kondisi toko pada awal 2026, titik berangkat bagian ini.

sql
siapkan.sql
-- siapkan.sql — fondasi Bagian 2: skema dari Bagian 1 + data master
-- + satu pesanan lama Desember 2025 sebagai data historis.
-- Bagian ini dimaksudkan dapat dijalankan sendiri dari database kosong.

CREATE TABLE kategori (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text   NOT NULL UNIQUE
);

CREATE TABLE produk (
    id          integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kode        text           NOT NULL UNIQUE,
    nama        text           NOT NULL,
    kategori_id integer        NOT NULL REFERENCES kategori (id),
    harga_beli  numeric(12, 2) NOT NULL CHECK (harga_beli > 0),
    harga_jual  numeric(12, 2) NOT NULL CHECK (harga_jual > 0),
    stok        integer        NOT NULL CHECK (stok >= 0)
);

CREATE TABLE pelanggan (
    id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama  text   NOT NULL,
    email text   NOT NULL UNIQUE,
    kota  text   NOT NULL
);

CREATE TABLE pesanan (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan (id),
    tanggal      date    NOT NULL,
    status       text    NOT NULL DEFAULT 'baru'
                 CHECK (status IN ('baru', 'diproses', 'selesai', 'batal'))
);

CREATE TABLE pesanan_item (
    id                   integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pesanan_id           integer        NOT NULL REFERENCES pesanan (id) ON DELETE CASCADE,
    produk_id            integer        NOT NULL REFERENCES produk (id),
    qty                  integer        NOT NULL CHECK (qty > 0),
    harga_saat_transaksi numeric(12, 2) NOT NULL,
    subtotal             numeric(12, 2) GENERATED ALWAYS AS (qty * harga_saat_transaksi) STORED
);

CREATE INDEX idx_pesanan_tanggal       ON pesanan (tanggal);
CREATE INDEX idx_pesanan_pelanggan     ON pesanan (pelanggan_id);
CREATE INDEX idx_pesanan_item_pesanan  ON pesanan_item (pesanan_id);
CREATE INDEX idx_pesanan_item_produk   ON pesanan_item (produk_id);


INSERT INTO kategori (nama) VALUES
    ('Sembako'),
    ('Minuman'),
    ('Makanan'),
    ('Kebersihan');

INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok) VALUES
    ('PRD-001', 'Beras Premium 5kg',        1, 61000.00, 68000.00,  40),
    ('PRD-002', 'Gula Pasir 1kg',           1, 15000.00, 17000.00,  65),
    ('PRD-003', 'Minyak Goreng 2L',         1, 32000.00, 36500.00,  35),
    ('PRD-004', 'Telur Ayam 1kg',           1, 27000.00, 31000.00,  25),
    ('PRD-005', 'Kopi Sachet Instan',       2,  9000.00, 12000.00,  90),
    ('PRD-006', 'Teh Celup 50 kantong',     2, 11000.00, 13500.00,  45),
    ('PRD-007', 'Air Mineral 600ml',        2,  2500.00,  4000.00, 150),
    ('PRD-008', 'Mie Instan Goreng',        3,  2800.00,  3500.00, 100),
    ('PRD-009', 'Biskuit Cokelat',          3,  7500.00,  9500.00,  30),
    ('PRD-010', 'Sabun Cuci Piring 780ml',  4, 16000.00, 19500.00,  22);

INSERT INTO pelanggan (nama, email, kota) VALUES
    ('Budi Santoso', '[email protected]',  'Bandung'),
    ('Siti Rahayu',  '[email protected]',  'Bandung'),
    ('Andi Wijaya',  '[email protected]',  'Jakarta'),
    ('Dewi Lestari', '[email protected]',  'Surabaya'),
    ('Rudi Hartono', '[email protected]',  'Bandung'),
    ('Maya Putri',   '[email protected]',  'Jakarta');

-- satu pesanan lama yang sudah selesai (stok Gula dan Kopi sengaja
-- diset lebih tinggi 65 dan 90 supaya setelah pengurangan menjadi
-- 60 dan 80 -- kondisi awal toko pada awal 2026)
BEGIN;

INSERT INTO pesanan (pelanggan_id, tanggal, status)
VALUES (2, DATE '2025-12-20', 'selesai');

INSERT INTO pesanan_item (pesanan_id, produk_id, qty, harga_saat_transaksi)
SELECT p.id, pr.id, v.qty, v.harga
FROM (VALUES
    ('PRD-005', 10, 12000.00),
    ('PRD-002',  5, 17000.00)
) AS v(kode_produk, qty, harga)
JOIN pesanan p   ON p.pelanggan_id = 2 AND p.tanggal = DATE '2025-12-20'
JOIN produk pr   ON pr.kode = v.kode_produk;

UPDATE produk pr
SET stok = pr.stok - d.qty_terjual
FROM (
    SELECT produk_id, SUM(qty) AS qty_terjual
    FROM pesanan_item
    GROUP BY produk_id
) d
WHERE d.produk_id = pr.id;

COMMIT;

-- kondisi awal Bagian 2
SELECT p.kode, p.nama, p.stok
FROM produk p
WHERE p.kode IN ('PRD-002', 'PRD-005')
ORDER BY p.kode;

SELECT count(*) AS jumlah_pesanan_tersimpan
FROM pesanan;

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
CREATE INDEX
CREATE INDEX
INSERT 0 4
INSERT 0 10
INSERT 0 6
BEGIN
INSERT 0 1
INSERT 0 2
UPDATE 2
COMMIT
  kode   |        nama        | stok 
---------+--------------------+------
 PRD-002 | Gula Pasir 1kg     |   60
 PRD-005 | Kopi Sachet Instan |   80
(2 rows)

 jumlah_pesanan_tersimpan 
--------------------------
                        1
(1 row)

Perhatikan bahwa pesanan Desember itu sendiri dibungkus BEGIN ... COMMIT — pola transaksi yang akan dibedah tuntas di Bagian 3. Verifikasi penutup menunjukkan Gula Pasir dan Kopi Sachet berada di 60 dan 80, dan tepat satu pesanan tersimpan. Mulai titik inilah kegiatan harian dimulai.

CRUD Harian Data Master #

Empat operasi paling sering dilakukan kasir dan admin toko terangkum dalam satu berkas: menambah produk baru, mengoreksi harga, mendaftarkan pelanggan, dan menerima pembaruan katalog dari sistem pusat:

sql
crud-master.sql
-- crud-master.sql — operasi harian data master: tambah produk, koreksi
-- harga, daftar pelanggan baru, dan pembaruan harga lewat upsert.

-- 1) produk baru masuk katalog, id dikembalikan tanpa query ulang
INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
VALUES ('PRD-011', 'Sosis Sapi 500g', 3, 28000.00, 33500.00, 15)
RETURNING id, kode, nama, harga_beli, harga_jual, stok;

-- 2) koreksi harga jual Teh Celup setelah harga beli naik
UPDATE produk
SET harga_jual = 14000.00
WHERE kode = 'PRD-006'
RETURNING kode, nama, harga_jual;

-- 3) pelanggan baru mendaftar lewat kasir
INSERT INTO pelanggan (nama, email, kota)
VALUES ('Rina Amelia', '[email protected]', 'Bandung')
RETURNING id, nama, email, kota;

-- 4) upsert: data produk dikirim ulang oleh sistem pusat.
--    Bila kode sudah ada, hanya harga yang diperbarui -- stok lokal
--    sengaja tidak ditimpa agar tidak bentrok dengan penjualan kasir.
INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok) VALUES
    ('PRD-010', 'Sabun Cuci Piring 780ml', 4, 16500.00, 20000.00, 22)
ON CONFLICT (kode) DO UPDATE
SET harga_beli = EXCLUDED.harga_beli,
    harga_jual = EXCLUDED.harga_jual
RETURNING kode, harga_beli, harga_jual, stok;

-- 5) verifikasi katalog dan pelanggan
SELECT p.kode, p.nama, k.nama AS kategori, p.harga_beli, p.harga_jual, p.stok
FROM produk p
JOIN kategori k ON k.id = p.kategori_id
ORDER BY p.id;

SELECT id, nama, email, kota
FROM pelanggan
ORDER BY id;

Output yang Dihasilkan:

text
 id |  kode   |      nama       | harga_beli | harga_jual | stok 
----+---------+-----------------+------------+------------+------
 11 | PRD-011 | Sosis Sapi 500g |   28000.00 |   33500.00 |   15
(1 row)

INSERT 0 1
  kode   |         nama         | harga_jual 
---------+----------------------+------------
 PRD-006 | Teh Celup 50 kantong |   14000.00
(1 row)

UPDATE 1
 id |    nama     |      email       |  kota   
----+-------------+------------------+---------
  7 | Rina Amelia | [email protected] | Bandung
(1 row)

INSERT 0 1
  kode   | harga_beli | harga_jual | stok 
---------+------------+------------+------
 PRD-010 |   16500.00 |   20000.00 |   22
(1 row)

INSERT 0 1
  kode   |          nama           |  kategori  | harga_beli | harga_jual | stok 
---------+-------------------------+------------+------------+------------+------
 PRD-001 | Beras Premium 5kg       | Sembako    |   61000.00 |   68000.00 |   40
 PRD-002 | Gula Pasir 1kg          | Sembako    |   15000.00 |   17000.00 |   60
 PRD-003 | Minyak Goreng 2L        | Sembako    |   32000.00 |   36500.00 |   35
 PRD-004 | Telur Ayam 1kg          | Sembako    |   27000.00 |   31000.00 |   25
 PRD-005 | Kopi Sachet Instan      | Minuman    |    9000.00 |   12000.00 |   80
 PRD-006 | Teh Celup 50 kantong    | Minuman    |   11000.00 |   14000.00 |   45
 PRD-007 | Air Mineral 600ml       | Minuman    |    2500.00 |    4000.00 |  150
 PRD-008 | Mie Instan Goreng       | Makanan    |    2800.00 |    3500.00 |  100
 PRD-009 | Biskuit Cokelat         | Makanan    |    7500.00 |    9500.00 |   30
 PRD-010 | Sabun Cuci Piring 780ml | Kebersihan |   16500.00 |   20000.00 |   22
 PRD-011 | Sosis Sapi 500g         | Makanan    |   28000.00 |   33500.00 |   15
(11 rows)

 id |     nama     |      email       |   kota   
----+--------------+------------------+----------
  1 | Budi Santoso | [email protected] | Bandung
  2 | Siti Rahayu  | [email protected] | Bandung
  3 | Andi Wijaya  | [email protected] | Jakarta
  4 | Dewi Lestari | [email protected] | Surabaya
  5 | Rudi Hartono | [email protected] | Bandung
  6 | Maya Putri   | [email protected] | Jakarta
  7 | Rina Amelia  | [email protected] | Bandung
(7 rows)

Empat teknik bekerja bersama di sini:

INSERT ... RETURNING mengembalikan baris yang baru tersimpan — termasuk id yang dibuat PostgreSQL — tanpa query kedua. Dalam aplikasi nyata, nilai inilah yang ditangkap kode program untuk ditampilkan atau dipakai operasi berikutnya.
UPDATE ... RETURNING memakai pola yang sama untuk koreksi harga: satu perintah, satu hasil terverifikasi. Harga jual Teh Celup naik dari 13500 menjadi 14000 setelah harga belinya naik.
ON CONFLICT (kode) DO UPDATE menangani pembaruan katalog dari sistem pusat yang tidak tahu apakah produk sudah ada di cabang. Baris PRD-010 sudah ada, jadi hanya harga_beli dan harga_jual yang diperbarui dari EXCLUDED — baris yang baru dicoba disisipkan.
Stok sengaja tidak ikut diperbarui: stok lokal adalah hasil mutasi kasir (penjualan, pengecekan fisik), bukan milik sistem pusat. Menimpanya dengan angka pusat akan menghapus jejak penjualan cabang — keputusan penting yang dibawa klausa SET yang hanya menyebut dua kolom harga.

Mencari Produk di Kasir #

Antrean di depan kasir tidak menunggu admin membuka tabel produk satu per satu. Pencarian harus bisa mengambil sepotong nama, tidak peduli besar-kecil hurufnya. Tiga query berikut adalah tiga pertanyaan yang paling sering muncul di balik meja kasir:

sql
cari-produk.sql
-- cari-produk.sql — pencarian katalog dari sisi kasir: temukan produk
-- lewat sepotong nama (ILIKE, tidak peduli besar-kecil huruf), saring
-- berdasarkan kategori dan harga, lalu tutup dengan daftar restock
-- untuk manajer toko.

-- 1) pelanggan menyebut "kopi" dan "teh"; kata kunci boleh cocok
--    di posisi mana pun dalam nama produk
SELECT kode, nama, harga_jual, stok
FROM produk
WHERE nama ILIKE '%kopi%' OR nama ILIKE '%teh%'
ORDER BY kode;

-- 2) sembako di bawah Rp50.000 yang masih ada stoknya
SELECT p.kode, p.nama, p.harga_jual, p.stok
FROM produk p
JOIN kategori k ON k.id = p.kategori_id
WHERE k.nama = 'Sembako'
  AND p.harga_jual < 50000.00
  AND p.stok > 0
ORDER BY p.harga_jual DESC, p.kode;

-- 3) daftar restock: stok yang tinggal 30 ke bawah
SELECT kode, nama, stok
FROM produk
WHERE stok <= 30
ORDER BY stok, kode;

Output yang Dihasilkan:

text
  kode   |         nama         | harga_jual | stok 
---------+----------------------+------------+------
 PRD-005 | Kopi Sachet Instan   |   12000.00 |   80
 PRD-006 | Teh Celup 50 kantong |   14000.00 |   45
(2 rows)

  kode   |       nama       | harga_jual | stok 
---------+------------------+------------+------
 PRD-003 | Minyak Goreng 2L |   36500.00 |   35
 PRD-004 | Telur Ayam 1kg   |   31000.00 |   25
 PRD-002 | Gula Pasir 1kg   |   17000.00 |   60
(3 rows)

  kode   |          nama           | stok 
---------+-------------------------+------
 PRD-011 | Sosis Sapi 500g         |   15
 PRD-010 | Sabun Cuci Piring 780ml |   22
 PRD-004 | Telur Ayam 1kg          |   25
 PRD-009 | Biskuit Cokelat         |   30
(4 rows)

Operator ILIKE adalah versi LIKE yang tidak membedakan besar-kecil huruf — 'Kopi Sachet Instan' cocok dengan pola '%kopi%'. Tanda persen di kedua ujung pola berarti kata kunci boleh muncul di posisi mana pun, sehingga kasir cukup mengetik kata yang didengar. Query kedua menggabungkan filter tiga syarat sekaligus (kategori, plafon harga, stok tersedia) untuk menjawab “sembako murah apa yang bisa saya sarankan?”. Query ketiga adalah daftar restock pagi: empat produk dengan stok tertipis, diurutkan dari yang paling mendesak — Sosis Sapi tinggal 15 dan Sabun Cuci Piring 22.

Validasi Masukan Data Master #

Aplikasi boleh punya form dengan validasi Javascript yang indah, tetapi database tetap baris pertahanan terakhir: masukan yang tidak valid harus ditolak di sini, apa pun jalurnya. Lima kasus berikut sengaja dikirim langsung ke SQL — bukan lewat aplikasi — untuk membuktikan penolakannya:

sql
validasi-master.sql
-- validasi-master.sql — masukan yang tidak valid harus ditolak database,
-- bukan hanya oleh tampilan aplikasi. Lima kasus ditangkap dalam blok
-- EXCEPTION lalu dibandingkan dengan SQLSTATE yang diharapkan.

CREATE TEMP TABLE hasil_uji (
    nomor      integer,
    kasus      text,
    diharapkan text,
    aktual     text,
    status     text
);

DO $$
DECLARE
    v_state text;
BEGIN
    -- Kasus 1: nama produk kosong (NOT NULL)
    BEGIN
        INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
        VALUES ('PRD-200', NULL, 3, 5000.00, 6500.00, 5);
        INSERT INTO hasil_uji VALUES (1, 'nama produk kosong', '23502', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (1, 'nama produk kosong', '23502', v_state,
            CASE WHEN v_state = '23502' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 2: kategori tidak ada (FOREIGN KEY)
    BEGIN
        INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
        VALUES ('PRD-201', 'Produk kategori asing', 99, 5000.00, 6500.00, 5);
        INSERT INTO hasil_uji VALUES (2, 'kategori 99 tidak ada', '23503', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (2, 'kategori 99 tidak ada', '23503', v_state,
            CASE WHEN v_state = '23503' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 3: harga beli negatif (CHECK)
    BEGIN
        UPDATE produk SET harga_beli = -1000.00 WHERE kode = 'PRD-009';
        INSERT INTO hasil_uji VALUES (3, 'harga beli negatif', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (3, 'harga beli negatif', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 4: email pelanggan sudah terpakai (UNIQUE)
    BEGIN
        INSERT INTO pelanggan (nama, email, kota)
        VALUES ('Budi Palsu', '[email protected]', 'Cimahi');
        INSERT INTO hasil_uji VALUES (4, 'email sudah terpakai', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (4, 'email sudah terpakai', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 5: koreksi stok yang membuat stok negatif (CHECK)
    BEGIN
        UPDATE produk SET stok = stok - 500 WHERE kode = 'PRD-009';
        INSERT INTO hasil_uji VALUES (5, 'stok dikurangi sampai negatif', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (5, 'stok dikurangi sampai negatif', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;
END $$;

SELECT nomor, kasus, diharapkan, aktual, status
FROM hasil_uji
ORDER BY nomor;

-- pastikan tidak ada perubahan yang bocor dari kasus yang ditolak
SELECT count(*) AS jumlah_produk, count(*) FILTER (WHERE kode LIKE 'PRD-2%') AS produk_uji_bocor
FROM produk;

Output yang Dihasilkan:

text
CREATE TABLE
DO
 nomor |             kasus             | diharapkan | aktual | status 
-------+-------------------------------+------------+--------+--------
     1 | nama produk kosong            | 23502      | 23502  | LULUS
     2 | kategori 99 tidak ada         | 23503      | 23503  | LULUS
     3 | harga beli negatif            | 23514      | 23514  | LULUS
     4 | email sudah terpakai          | 23505      | 23505  | LULUS
     5 | stok dikurangi sampai negatif | 23514      | 23514  | LULUS
(5 rows)

 jumlah_produk | produk_uji_bocor 
---------------+------------------
            11 |                0
(1 row)

Lima kasus, lima vonis LULUS: nama kosong ditolak NOT NULL (23502), kategori asing ditolak foreign key (23503), harga beli negatif dan stok minus ditolak CHECK (23514), dan email Budi yang dipakai dua kali ditolak UNIQUE (23505). Query penutup adalah asersi kebocoran: kolom produk_uji_bocor menghitung produk berkode PRD-2% — kode-kode yang dipakai kasus uji — dan hasilnya nol, bukti bahwa kelima penolakan tidak meninggalkan satu pun baris setengah jadi. Kasus 5 menunjukkan wajah lain penjaga yang sama: stok - 500 pada Biskuit Cokelat (stok 30) ditolak karena hasilnya akan negatif.

Menghapus yang Aman #

Akhir hari, admin membersihkan kesalahan input: Sosis Sapi ternyata salah kode dan pelanggan Rina mendaftar dua kali di aplikasi lain. Keduanya belum pernah tersentuh transaksi — aman dihapus, dan RETURNING membuktikan baris apa yang lenyap:

sql
hapus-produk.sql
-- hapus-produk.sql — menghapus data master yang memang belum dipakai.
-- PRD-011 baru ditambahkan di crud-master.sql dan belum pernah
-- dipesan; Rina juga belum pernah memesan. Keduanya aman dihapus.

DELETE FROM produk
WHERE kode = 'PRD-011'
RETURNING kode, nama;

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

SELECT (SELECT count(*) FROM produk)    AS jumlah_produk,
       (SELECT count(*) FROM pelanggan) AS jumlah_pelanggan,
       (SELECT count(*) FROM pesanan)   AS jumlah_pesanan;

Output yang Dihasilkan:

text
  kode   |      nama       
---------+-----------------
 PRD-011 | Sosis Sapi 500g
(1 row)

DELETE 1
    nama     |      email       
-------------+------------------
 Rina Amelia | [email protected]
(1 row)

DELETE 1
 jumlah_produk | jumlah_pelanggan | jumlah_pesanan 
---------------+------------------+----------------
            10 |                6 |              1
(1 row)

Dua DELETE masing-masing menghapus tepat satu baris, dan katalog kembali berisi sepuluh produk dengan enam pelanggan. Kondisi WHERE yang spesifik — kode produk dan email, keduanya kolom unik — adalah kunci penghapusan yang aman: perintah tak bisa mengenai baris lain meski dijalankan ulang.

Riwayat Penjualan Tidak Boleh Hilang #

Bagaimana dengan produk yang sudah terjual? Barang yang salah input boleh dibersihkan — Sosis Sapi tadi contohnya — tetapi Kopi Sachet yang tercatat di pesanan Desember adalah bagian dari omzet 2025. Percobaan berikut sengaja menghapus produk PRD-005:

sql
hapus-dilarang.sql
-- hapus-dilarang.sql — produk yang sudah pernah dipesan tidak boleh
-- dihapus: riwayat pesanan_item masih merujuk padanya. Foreign key
-- dengan perilaku NO ACTION (RESTRICT) menolak penghapusan ini.

DELETE FROM produk WHERE kode = 'PRD-005';

Output yang Dihasilkan:

text

psql:hapus-dilarang.sql:5: ERROR:  update or delete on table "produk" violates foreign key constraint "pesanan_item_produk_id_fkey" on table "pesanan_item"
DETAIL:  Key (id)=(5) is still referenced from table "pesanan_item".

PostgreSQL menolak dengan error foreign key: baris produk id 5 masih dirujuk tabel pesanan_item. Inilah perwujudan pilihan desain dari Bagian 1 — foreign key pesanan_item → produk sengaja tidak diberi ON DELETE CASCADE atau ON DELETE SET NULL, sehingga satu-satunya jalan menghapus produk terjual adalah menghapus riwayat penjualannya lebih dulu, dan itu bukan hal yang boleh dilakukan diam-diam. Database memaksa keputusan demikian terjadi secara sadar.

Data master kini hidup: katalog yang valid, pelanggan yang unik, dan riwayat yang utuh. Pusat aktivitas toko menunggu di depan — pelanggan datang, memesan beberapa produk sekaligus, dan stok harus berkurang tepat sebanyak yang terjual. Bagian Transaksi Penjualan membangun alur itu dengan transaksi atomik.

Referensi #

INSERT — termasuk klausa ON CONFLICT untuk pola upsert.
UPDATE — sintaks UPDATE ... RETURNING dan pembaruan berkondisi.
DELETE — penghapusan berkondisi dengan RETURNING.
Pattern Matching — operator LIKE/ILIKE dan pola %.

Berkas Sumber

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

sql
-- cari-produk.sql — pencarian katalog dari sisi kasir: temukan produk
-- lewat sepotong nama (ILIKE, tidak peduli besar-kecil huruf), saring
-- berdasarkan kategori dan harga, lalu tutup dengan daftar restock
-- untuk manajer toko.

-- 1) pelanggan menyebut "kopi" dan "teh"; kata kunci boleh cocok
--    di posisi mana pun dalam nama produk
SELECT kode, nama, harga_jual, stok
FROM produk
WHERE nama ILIKE '%kopi%' OR nama ILIKE '%teh%'
ORDER BY kode;

-- 2) sembako di bawah Rp50.000 yang masih ada stoknya
SELECT p.kode, p.nama, p.harga_jual, p.stok
FROM produk p
JOIN kategori k ON k.id = p.kategori_id
WHERE k.nama = 'Sembako'
  AND p.harga_jual < 50000.00
  AND p.stok > 0
ORDER BY p.harga_jual DESC, p.kode;

-- 3) daftar restock: stok yang tinggal 30 ke bawah
SELECT kode, nama, stok
FROM produk
WHERE stok <= 30
ORDER BY stok, kode;

sql
-- crud-master.sql — operasi harian data master: tambah produk, koreksi
-- harga, daftar pelanggan baru, dan pembaruan harga lewat upsert.

-- 1) produk baru masuk katalog, id dikembalikan tanpa query ulang
INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
VALUES ('PRD-011', 'Sosis Sapi 500g', 3, 28000.00, 33500.00, 15)
RETURNING id, kode, nama, harga_beli, harga_jual, stok;

-- 2) koreksi harga jual Teh Celup setelah harga beli naik
UPDATE produk
SET harga_jual = 14000.00
WHERE kode = 'PRD-006'
RETURNING kode, nama, harga_jual;

-- 3) pelanggan baru mendaftar lewat kasir
INSERT INTO pelanggan (nama, email, kota)
VALUES ('Rina Amelia', '[email protected]', 'Bandung')
RETURNING id, nama, email, kota;

-- 4) upsert: data produk dikirim ulang oleh sistem pusat.
--    Bila kode sudah ada, hanya harga yang diperbarui -- stok lokal
--    sengaja tidak ditimpa agar tidak bentrok dengan penjualan kasir.
INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok) VALUES
    ('PRD-010', 'Sabun Cuci Piring 780ml', 4, 16500.00, 20000.00, 22)
ON CONFLICT (kode) DO UPDATE
SET harga_beli = EXCLUDED.harga_beli,
    harga_jual = EXCLUDED.harga_jual
RETURNING kode, harga_beli, harga_jual, stok;

-- 5) verifikasi katalog dan pelanggan
SELECT p.kode, p.nama, k.nama AS kategori, p.harga_beli, p.harga_jual, p.stok
FROM produk p
JOIN kategori k ON k.id = p.kategori_id
ORDER BY p.id;

SELECT id, nama, email, kota
FROM pelanggan
ORDER BY id;

sql
-- hapus-dilarang.sql — produk yang sudah pernah dipesan tidak boleh
-- dihapus: riwayat pesanan_item masih merujuk padanya. Foreign key
-- dengan perilaku NO ACTION (RESTRICT) menolak penghapusan ini.

DELETE FROM produk WHERE kode = 'PRD-005';

sql
-- hapus-produk.sql — menghapus data master yang memang belum dipakai.
-- PRD-011 baru ditambahkan di crud-master.sql dan belum pernah
-- dipesan; Rina juga belum pernah memesan. Keduanya aman dihapus.

DELETE FROM produk
WHERE kode = 'PRD-011'
RETURNING kode, nama;

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

SELECT (SELECT count(*) FROM produk)    AS jumlah_produk,
       (SELECT count(*) FROM pelanggan) AS jumlah_pelanggan,
       (SELECT count(*) FROM pesanan)   AS jumlah_pesanan;

sql
-- siapkan.sql — fondasi Bagian 2: skema dari Bagian 1 + data master
-- + satu pesanan lama Desember 2025 sebagai data historis.
-- Bagian ini dimaksudkan dapat dijalankan sendiri dari database kosong.

CREATE TABLE kategori (
    id   integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama text   NOT NULL UNIQUE
);

CREATE TABLE produk (
    id          integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kode        text           NOT NULL UNIQUE,
    nama        text           NOT NULL,
    kategori_id integer        NOT NULL REFERENCES kategori (id),
    harga_beli  numeric(12, 2) NOT NULL CHECK (harga_beli > 0),
    harga_jual  numeric(12, 2) NOT NULL CHECK (harga_jual > 0),
    stok        integer        NOT NULL CHECK (stok >= 0)
);

CREATE TABLE pelanggan (
    id    integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama  text   NOT NULL,
    email text   NOT NULL UNIQUE,
    kota  text   NOT NULL
);

CREATE TABLE pesanan (
    id           integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pelanggan_id integer NOT NULL REFERENCES pelanggan (id),
    tanggal      date    NOT NULL,
    status       text    NOT NULL DEFAULT 'baru'
                 CHECK (status IN ('baru', 'diproses', 'selesai', 'batal'))
);

CREATE TABLE pesanan_item (
    id                   integer        GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    pesanan_id           integer        NOT NULL REFERENCES pesanan (id) ON DELETE CASCADE,
    produk_id            integer        NOT NULL REFERENCES produk (id),
    qty                  integer        NOT NULL CHECK (qty > 0),
    harga_saat_transaksi numeric(12, 2) NOT NULL,
    subtotal             numeric(12, 2) GENERATED ALWAYS AS (qty * harga_saat_transaksi) STORED
);

CREATE INDEX idx_pesanan_tanggal       ON pesanan (tanggal);
CREATE INDEX idx_pesanan_pelanggan     ON pesanan (pelanggan_id);
CREATE INDEX idx_pesanan_item_pesanan  ON pesanan_item (pesanan_id);
CREATE INDEX idx_pesanan_item_produk   ON pesanan_item (produk_id);


INSERT INTO kategori (nama) VALUES
    ('Sembako'),
    ('Minuman'),
    ('Makanan'),
    ('Kebersihan');

INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok) VALUES
    ('PRD-001', 'Beras Premium 5kg',        1, 61000.00, 68000.00,  40),
    ('PRD-002', 'Gula Pasir 1kg',           1, 15000.00, 17000.00,  65),
    ('PRD-003', 'Minyak Goreng 2L',         1, 32000.00, 36500.00,  35),
    ('PRD-004', 'Telur Ayam 1kg',           1, 27000.00, 31000.00,  25),
    ('PRD-005', 'Kopi Sachet Instan',       2,  9000.00, 12000.00,  90),
    ('PRD-006', 'Teh Celup 50 kantong',     2, 11000.00, 13500.00,  45),
    ('PRD-007', 'Air Mineral 600ml',        2,  2500.00,  4000.00, 150),
    ('PRD-008', 'Mie Instan Goreng',        3,  2800.00,  3500.00, 100),
    ('PRD-009', 'Biskuit Cokelat',          3,  7500.00,  9500.00,  30),
    ('PRD-010', 'Sabun Cuci Piring 780ml',  4, 16000.00, 19500.00,  22);

INSERT INTO pelanggan (nama, email, kota) VALUES
    ('Budi Santoso', '[email protected]',  'Bandung'),
    ('Siti Rahayu',  '[email protected]',  'Bandung'),
    ('Andi Wijaya',  '[email protected]',  'Jakarta'),
    ('Dewi Lestari', '[email protected]',  'Surabaya'),
    ('Rudi Hartono', '[email protected]',  'Bandung'),
    ('Maya Putri',   '[email protected]',  'Jakarta');

-- satu pesanan lama yang sudah selesai (stok Gula dan Kopi sengaja
-- diset lebih tinggi 65 dan 90 supaya setelah pengurangan menjadi
-- 60 dan 80 -- kondisi awal toko pada awal 2026)
BEGIN;

INSERT INTO pesanan (pelanggan_id, tanggal, status)
VALUES (2, DATE '2025-12-20', 'selesai');

INSERT INTO pesanan_item (pesanan_id, produk_id, qty, harga_saat_transaksi)
SELECT p.id, pr.id, v.qty, v.harga
FROM (VALUES
    ('PRD-005', 10, 12000.00),
    ('PRD-002',  5, 17000.00)
) AS v(kode_produk, qty, harga)
JOIN pesanan p   ON p.pelanggan_id = 2 AND p.tanggal = DATE '2025-12-20'
JOIN produk pr   ON pr.kode = v.kode_produk;

UPDATE produk pr
SET stok = pr.stok - d.qty_terjual
FROM (
    SELECT produk_id, SUM(qty) AS qty_terjual
    FROM pesanan_item
    GROUP BY produk_id
) d
WHERE d.produk_id = pr.id;

COMMIT;

-- kondisi awal Bagian 2
SELECT p.kode, p.nama, p.stok
FROM produk p
WHERE p.kode IN ('PRD-002', 'PRD-005')
ORDER BY p.kode;

SELECT count(*) AS jumlah_pesanan_tersimpan
FROM pesanan;

sql
-- validasi-master.sql — masukan yang tidak valid harus ditolak database,
-- bukan hanya oleh tampilan aplikasi. Lima kasus ditangkap dalam blok
-- EXCEPTION lalu dibandingkan dengan SQLSTATE yang diharapkan.

CREATE TEMP TABLE hasil_uji (
    nomor      integer,
    kasus      text,
    diharapkan text,
    aktual     text,
    status     text
);

DO $$
DECLARE
    v_state text;
BEGIN
    -- Kasus 1: nama produk kosong (NOT NULL)
    BEGIN
        INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
        VALUES ('PRD-200', NULL, 3, 5000.00, 6500.00, 5);
        INSERT INTO hasil_uji VALUES (1, 'nama produk kosong', '23502', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (1, 'nama produk kosong', '23502', v_state,
            CASE WHEN v_state = '23502' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 2: kategori tidak ada (FOREIGN KEY)
    BEGIN
        INSERT INTO produk (kode, nama, kategori_id, harga_beli, harga_jual, stok)
        VALUES ('PRD-201', 'Produk kategori asing', 99, 5000.00, 6500.00, 5);
        INSERT INTO hasil_uji VALUES (2, 'kategori 99 tidak ada', '23503', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (2, 'kategori 99 tidak ada', '23503', v_state,
            CASE WHEN v_state = '23503' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 3: harga beli negatif (CHECK)
    BEGIN
        UPDATE produk SET harga_beli = -1000.00 WHERE kode = 'PRD-009';
        INSERT INTO hasil_uji VALUES (3, 'harga beli negatif', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (3, 'harga beli negatif', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 4: email pelanggan sudah terpakai (UNIQUE)
    BEGIN
        INSERT INTO pelanggan (nama, email, kota)
        VALUES ('Budi Palsu', '[email protected]', 'Cimahi');
        INSERT INTO hasil_uji VALUES (4, 'email sudah terpakai', '23505', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (4, 'email sudah terpakai', '23505', v_state,
            CASE WHEN v_state = '23505' THEN 'LULUS' ELSE 'GAGAL' END);
    END;

    -- Kasus 5: koreksi stok yang membuat stok negatif (CHECK)
    BEGIN
        UPDATE produk SET stok = stok - 500 WHERE kode = 'PRD-009';
        INSERT INTO hasil_uji VALUES (5, 'stok dikurangi sampai negatif', '23514', NULL, 'GAGAL: diterima');
    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS v_state = RETURNED_SQLSTATE;
        INSERT INTO hasil_uji VALUES (5, 'stok dikurangi sampai negatif', '23514', v_state,
            CASE WHEN v_state = '23514' THEN 'LULUS' ELSE 'GAGAL' END);
    END;
END $$;

SELECT nomor, kasus, diharapkan, aktual, status
FROM hasil_uji
ORDER BY nomor;

-- pastikan tidak ada perubahan yang bocor dari kasus yang ditolak
SELECT count(*) AS jumlah_produk, count(*) FILTER (WHERE kode LIKE 'PRD-2%') AS produk_uji_bocor
FROM produk;