Bagian 1: Perancangan Skema

Analitik pertanian gagal bukan karena query-nya salah, melainkan karena datanya tidak dibangun untuk dianalisis: harga dicatat menempel pada panen sehingga hari tanpa panen tidak punya harga, hasil panen tersimpan dalam satuan yang berbeda-beda, dan petak yang sama tercatat dua kali dengan nama berbeda. Semua masalah itu paling murah diselesaikan di skema — sekali dipasang sebagai constraint dan keputusan tipe, aturannya berlaku untuk semua query yang menyentuh data. Pada bagian ini kita menerjemahkan kebutuhan Koperasi Tani Sejahtera menjadi empat tabel, mengambil keputusan paling penting proyek ini — harga pasar dipisahkan dari peristiwa panen — lalu membuktikan lewat uji bahwa setiap aturan benar-benar ditegakkan PostgreSQL.

Dari Cerita Kebutuhan ke Entitas #

Koperasi Tani Sejahtera menghimpun petani anggota di lima petak sebaran empat desa: dua sawah padi, dua ladang jagung dan kedelai yang ditanam bergantian, serta satu kebun kacang tanah. Ketua koperasi, Budi, bercerita singkat: setiap kali ada panen, kami menimbang hasilnya dan mencatat petak, tanamannya, tanggal, dan berapa kilogram. Kualitas hasil juga dinilai grade A sampai C. Terpisah dari itu, harga di pasar bergerak setiap hari — kami ingin mencatatnya rutin, bukan hanya saat menjual.

Dari cerita itu entitasnya menyingkir sendiri:

lahan — petak tanah dengan nama, desa, dan luas hektar; luas akan menjadi pembagi produktivitas.
komoditas — jenis tanaman yang ditanam dan diperdagangkan.
panen — peristiwa tunggal: satu petak memanen satu komoditas pada satu tanggal, dengan hasil kilogram dan grade.
harga_pasar — harga per kilogram per komoditas per hari; deret waktu harian yang berjalan terus, panen atau tidak.

Satu keputusan diambil sejak awal: tidak ada kolom turunan yang disimpan. Produktivitas per hektar, nilai panen, dan rata-rata bergerak semuanya adalah hasil hitung yang dihitung saat dibutuhkan — menjaga angka turunan di tabel berarti merawat dua sumber kebenaran yang bisa saling bertenggar.

Tabel Master: Lahan dan Komoditas #

Dua tabel pertama memuat master data. Satuan hasil diseragamkan kilogram dan luas lahan hektar sejak awal — campuran satuan adalah bug analitik yang paling sering lolos. Berikut tabel-lahan-komoditas.sql:

sql
tabel-lahan-komoditas.sql
-- tabel-lahan-komoditas.sql — tabel master lahan dan komoditas.
-- Satuan hasil diseragamkan kilogram; luas lahan dalam hektar.

CREATE TABLE lahan (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama        text NOT NULL UNIQUE,
    desa        text NOT NULL,
    luas_hektar numeric(6,2) NOT NULL CHECK (luas_hektar > 0)
);

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

COMMENT ON TABLE lahan IS 'Petak lahan yang dipanen; satu baris satu petak.';
COMMENT ON COLUMN lahan.luas_hektar IS 'Luas petak dalam hektar, dasar hitung produktivitas.';
COMMENT ON TABLE komoditas IS 'Jenis tanaman yang dicatat, mis. padi, jagung, kedelai.';

Output yang Dihasilkan:

text
CREATE TABLE
CREATE TABLE
COMMENT
COMMENT
COMMENT

Tiga hal yang layak digarisbawahi:

luas_hektar numeric(6,2) CHECK (luas_hektar > 0): numeric menyimpan desimal exact — bukan biner mendekati — dan numeric(6,2) menampung luas hingga 9.999,99 hektar dengan dua desimal, cukup untuk petak mana pun. CHECK menolak luas nol dan negatif: petak seluas 0 hektar tidak punya arti ekonomi, dan yang mendekati nol hampir pasti salah ketik. Penjelasan lengkap tipe ini ada di chapter Numeric dan Decimal .
nama text NOT NULL UNIQUE pada kedua tabel: nama petak dan nama komoditas adalah identitas yang manusiawi. UNIQUE menjaga “Sawah Blumbang” tidak terdaftar dua kali — duplikat master adalah racun bagi agregasi, karena GROUP BY nama akan membelah total hasil panen menjadi dua baris yang tampak saudara.
GENERATED ALWAYS AS IDENTITY: primary key pengganti serial yang modern; aplikasi tidak boleh menyisipkan id manual, mencegah dua sistem pencatatan id yang bertengkar.

Keputusan Terpenting: Harga Bukan Milik Panen #

Pertanyaan desain yang membedakan database analitik dari database pencatatan biasa: di mana harga disimpan? Pilihan naif adalah kolom harga pada tabel panen — panen dijual, jadi harga menempel di panen. Pilihan itu benar untuk sistem kasir, tapi merusak untuk analisis:

Aspek	Kolom harga di `panen`	Tabel `harga_pasar` terpisah
Hari tanpa panen	harga tidak tercatat	harga tercatat setiap hari
Dua petak panen di hari sama	harga identik tersalin dua kali	satu baris harga berlaku untuk semua
Pembaruan harga harian	tidak ada tempat menulisnya	satu baris per komoditas per hari
Deret harga untuk analisis tren	bolong-bolong mengikuti jadwal panen	rapat, bisa dihitung LAG dan rata-rata bergerak

Harga pasar adalah fakta tentang pasar, bukan fakta tentang panen: pada hari yang sama, seluruh anggota koperasi menghadapi harga padi yang sama, dan harga terus bergerak di hari-hari saat tidak ada yang memanen. Karena itu harga_pasar berdiri sebagai tabel sendiri yang hanya merujuk komoditas — keputusan yang memungkinkan seluruh Bagian 3 dan Bagian 4. Konsekuensinya jujur dan menarik: menghitung nilai sebuah panen nanti butuh pencocokan dua deret waktu, dan itulah pola as-of join yang menjadi klimaks proyek.

Tabel Panen dan Harga Pasar #

Berikut tabel-panen-harga.sql — dua tabel peristiwa dengan constraint bisnisnya:

sql
tabel-panen-harga.sql
-- tabel-panen-harga.sql — tabel peristiwa panen dan deret harga pasar.
-- panen merujuk dua master sekaligus (lahan + komoditas) karena satu panen
-- adalah peristiwa "petak ini menanam komoditas itu"; harga_pasar berdiri
-- sendiri sebagai deret waktu per komoditas karena harga berlaku untuk pasar
-- keseluruhan, bukan untuk satu panen tertentu.

CREATE TYPE kualitas_panen AS ENUM ('A', 'B', 'C');

CREATE TABLE panen (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    lahan_id     integer NOT NULL REFERENCES lahan (id),
    komoditas_id integer NOT NULL REFERENCES komoditas (id),
    tanggal      date NOT NULL,
    hasil_kg     numeric(10,2) NOT NULL,
    kualitas     kualitas_panen NOT NULL,
    CONSTRAINT hasil_panen_positif CHECK (hasil_kg > 0),
    CONSTRAINT satu_panen_per_hari UNIQUE (lahan_id, komoditas_id, tanggal)
);

CREATE TABLE harga_pasar (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    komoditas_id integer NOT NULL REFERENCES komoditas (id),
    tanggal      date NOT NULL,
    harga_per_kg numeric(10,2) NOT NULL,
    CONSTRAINT harga_pasar_positif CHECK (harga_per_kg > 0),
    CONSTRAINT satu_harga_per_hari UNIQUE (komoditas_id, tanggal)
);

COMMENT ON TYPE kualitas_panen IS 'Grade hasil panen: A terbaik, B standar, C di bawah standar.';
COMMENT ON TABLE panen IS 'Satu baris = satu peristiwa panen: petak, komoditas, tanggal, hasil.';
COMMENT ON COLUMN panen.hasil_kg IS 'Hasil panen dalam kilogram; selalu positif.';
COMMENT ON TABLE harga_pasar IS 'Deret harga harian per komoditas; milik pasar, bukan milik satu panen.';
COMMENT ON COLUMN harga_pasar.harga_per_kg IS 'Harga per kilogram dalam rupiah pada tanggal tersebut.';

CREATE INDEX idx_panen_tanggal   ON panen (tanggal);
CREATE INDEX idx_panen_lahan     ON panen (lahan_id);
CREATE INDEX idx_panen_komoditas ON panen (komoditas_id);

-- Bukti: index yang tersedia pada keempat tabel.
-- UNIQUE (komoditas_id, tanggal) pada harga_pasar otomatis menjadi index
-- komposit untuk pola akses "harga komoditas ini sampai tanggal itu".
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN ('lahan', 'komoditas', 'panen', 'harga_pasar')
ORDER BY tablename, indexname;

Output yang Dihasilkan:

text
CREATE TYPE
CREATE TABLE
CREATE TABLE
COMMENT
COMMENT
COMMENT
COMMENT
COMMENT
CREATE INDEX
CREATE INDEX
CREATE INDEX
  tablename  |      indexname      |                                               indexdef                                                
-------------+---------------------+-------------------------------------------------------------------------------------------------------
 harga_pasar | harga_pasar_pkey    | CREATE UNIQUE INDEX harga_pasar_pkey ON public.harga_pasar USING btree (id)
 harga_pasar | satu_harga_per_hari | CREATE UNIQUE INDEX satu_harga_per_hari ON public.harga_pasar USING btree (komoditas_id, tanggal)
 komoditas   | komoditas_nama_key  | CREATE UNIQUE INDEX komoditas_nama_key ON public.komoditas USING btree (nama)
 komoditas   | komoditas_pkey      | CREATE UNIQUE INDEX komoditas_pkey ON public.komoditas USING btree (id)
 lahan       | lahan_nama_key      | CREATE UNIQUE INDEX lahan_nama_key ON public.lahan USING btree (nama)
 lahan       | lahan_pkey          | CREATE UNIQUE INDEX lahan_pkey ON public.lahan USING btree (id)
 panen       | idx_panen_komoditas | CREATE INDEX idx_panen_komoditas ON public.panen USING btree (komoditas_id)
 panen       | idx_panen_lahan     | CREATE INDEX idx_panen_lahan ON public.panen USING btree (lahan_id)
 panen       | idx_panen_tanggal   | CREATE INDEX idx_panen_tanggal ON public.panen USING btree (tanggal)
 panen       | panen_pkey          | CREATE UNIQUE INDEX panen_pkey ON public.panen USING btree (id)
 panen       | satu_panen_per_hari | CREATE UNIQUE INDEX satu_panen_per_hari ON public.panen USING btree (lahan_id, komoditas_id, tanggal)
(11 rows)

Bedah keputusan desainnya:

CREATE TYPE kualitas_panen AS ENUM ('A', 'B', 'C'): grade adalah daftar tertutup. Enum menyimpan label sebagai katalog internal yang padat; menulis 'D' ditolak database dengan SQLSTATE khusus, bukan diam-diam tersimpan lalu mengotori GROUP BY kualitas. Seluk-beluknya di chapter Enum .
Dua foreign key pada panen: satu panen adalah perpotongan petak × komoditas × hari — relasi ini persis pola Relasi One-to-Many yang kini dipakai dua arah sekaligus. FK menjaga panen tidak menempel pada petak atau komoditas yang tidak terdaftar.
hasil_panen_positif: hasil 0 dan negatif adalah salah ketik, bukan fakta pertanian. Nama constraint eksplisit membuat pesan error menyebut aturannya, bukan nama default yang harus ditebak.
satu_panen_per_hari UNIQUE (lahan_id, komoditas_id, tanggal): satu petak tidak dipanen komoditas yang sama dua kali sehari — dua muat truk pada hari yang sama digabung menjadi satu baris. Ini composite key bisnis seperti di chapter Composite Key , sekaligus penjaga terakhir integritas buku panen.
satu_harga_per_hari UNIQUE (komoditas_id, tanggal): satu komoditas satu harga per hari. Dua sumber memberi harga berbeda untuk hari yang sama adalah konflik data, bukan variasi.
Tiga index manual di panen: primary key dan UNIQUE otomatis membuat index, tapi kolom foreign key tidak — padahal semua analisis memfilter tanggal dan join lahan_id/komoditas_id.

Query penutup berkas membaca pg_indexes dari system catalog dan menjadi buktinya: sebelas index tampil, empat otomatis dari PK/UNIQUE tabel master, sisanya hasil constraint dan pemasangan manual.

Menguji Skema: Positif dan Negatif #

Skema belum bisa dipercaya sebelum aturannya terbukti. Berkas berikut menguji sepuluh kasus dalam satu eksekusi — satu uji positif dan sembilan uji negatif, masing-masing dibungkus BEGIN ... EXCEPTION di dalam DO block agar psql tidak berhenti di error pertama, sama seperti pola Check Constraint :

sql
uji-constraint.sql
-- uji-constraint.sql — uji positif dan uji negatif seluruh constraint skema.
-- Pelanggaran dibungkus BEGIN...EXCEPTION di dalam DO block sehingga satu
-- berkas bisa menguji banyak kasus; hasilnya dirangkum LULUS/GAGAL.

INSERT INTO lahan (nama, desa, luas_hektar)
VALUES ('Sawah Blumbang', 'Blumbang', 1.20);

INSERT INTO komoditas (nama) VALUES ('padi');

INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
VALUES (1, 1, DATE '2025-03-10', 6000.00, 'A');

INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
VALUES (1, DATE '2025-03-10', 6400.00);

CREATE TABLE hasil_uji (
    urutan integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kasus  text NOT NULL,
    hasil  text NOT NULL
);

DO $$
BEGIN
    -- Uji positif: baris sah berikutnya harus diterima.
    INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
    VALUES (1, 1, DATE '2025-03-11', 100.00, 'B');
    INSERT INTO hasil_uji (kasus, hasil)
    VALUES ('panen sah kedua diterima', 'LULUS: baris tersimpan');

    -- Uji negatif: tiap pelanggaran harus ditolak database.
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-12', 0.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen nol', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen nol', 'LULUS: ditolak CHECK hasil_panen_positif');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-12', -250.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen negatif', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen negatif', 'LULUS: ditolak CHECK hasil_panen_positif');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-12', 500.00, 'D');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('kualitas di luar A/B/C', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN invalid_text_representation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('kualitas di luar A/B/C', 'LULUS: ditolak tipe enum kualitas_panen');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (99, 1, DATE '2025-03-12', 500.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen di lahan yang tidak ada', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN foreign_key_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen di lahan yang tidak ada', 'LULUS: ditolak FK ke lahan');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 99, DATE '2025-03-12', 500.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen komoditas yang tidak ada', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN foreign_key_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen komoditas yang tidak ada', 'LULUS: ditolak FK ke komoditas');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-10', 500.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen ganda petak-komoditas-hari sama', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN unique_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen ganda petak-komoditas-hari sama', 'LULUS: ditolak UNIQUE satu_panen_per_hari');
    END;

    BEGIN
        INSERT INTO lahan (nama, desa, luas_hektar)
        VALUES ('Lahan Khayalan', 'Nowhere', 0);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('luas lahan nol', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('luas lahan nol', 'LULUS: ditolak CHECK luas_hektar');
    END;

    BEGIN
        INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
        VALUES (1, DATE '2025-03-11', 0.00);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga pasar nol', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga pasar nol', 'LULUS: ditolak CHECK harga_pasar_positif');
    END;

    BEGIN
        INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
        VALUES (1, DATE '2025-03-10', 6500.00);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga ganda komoditas-hari sama', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN unique_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga ganda komoditas-hari sama', 'LULUS: ditolak UNIQUE satu_harga_per_hari');
    END;
END $$;

SELECT urutan, kasus, hasil
FROM hasil_uji
ORDER BY urutan;

Output yang Dihasilkan:

text
INSERT 0 1
INSERT 0 1
INSERT 0 1
INSERT 0 1
CREATE TABLE
DO
 urutan |                 kasus                 |                   hasil                   
--------+---------------------------------------+-------------------------------------------
      1 | panen sah kedua diterima              | LULUS: baris tersimpan
      2 | hasil panen nol                       | LULUS: ditolak CHECK hasil_panen_positif
      3 | hasil panen negatif                   | LULUS: ditolak CHECK hasil_panen_positif
      4 | kualitas di luar A/B/C                | LULUS: ditolak tipe enum kualitas_panen
      5 | panen di lahan yang tidak ada         | LULUS: ditolak FK ke lahan
      6 | panen komoditas yang tidak ada        | LULUS: ditolak FK ke komoditas
      7 | panen ganda petak-komoditas-hari sama | LULUS: ditolak UNIQUE satu_panen_per_hari
      8 | luas lahan nol                        | LULUS: ditolak CHECK luas_hektar
      9 | harga pasar nol                       | LULUS: ditolak CHECK harga_pasar_positif
     10 | harga ganda komoditas-hari sama       | LULUS: ditolak UNIQUE satu_harga_per_hari
(10 rows)

Empat kelas exception menangkap empat jenis pelanggaran: check_violation untuk CHECK (hasil dan harga nol atau negatif, luas nol), invalid_text_representation untuk nilai enum asing, foreign_key_violation untuk panen di petak atau komoditas yang tidak terdaftar, dan unique_violation untuk duplikasi panen maupun harga. Sepuluh baris LULUS berarti sepuluh aturan bekerja sekaligus membuktikan satu hal yang sering dilupakan: uji positif sama pentingnya dengan uji negatif, karena constraint yang terlalu ketat akan menolak data yang sah.

Harga Ganda Ditolak #

Uji terakhir sengaja dibuat sebagai berkas terpisah yang memang gagal — supaya pesan error aslinya terlihat, bukan hanya dirangkum. Koreksi harga untuk hari yang sudah tercatat harus ditolak:

sql
harga-ganda.sql
-- harga-ganda.sql — harga komoditas yang sama pada hari yang sama harus
-- ditolak UNIQUE. Berkas ini sengaja berakhir dengan error; psql keluar
-- dengan kode 3.
INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
VALUES (1, DATE '2025-03-10', 6350.00);

Output yang Dihasilkan:

text

psql:harga-ganda.sql:5: ERROR:  duplicate key value violates unique constraint "satu_harga_per_hari"
DETAIL:  Key (komoditas_id, tanggal)=(1, 2025-03-10) already exists.

Dengan ON_ERROR_STOP=1, pelanggaran UNIQUE menghentikan berkas dan membuat psql keluar dengan kode 3 — kode khusus “error SQL”. Pesannya menyebut constraint satu_harga_per_hari lengkap dengan pasangan kunci yang bentrok, sehingga koreksi yang benar jelas: harga 10 Maret sudah tercatat Rp6.400, dan jika memang keliru, jalannya adalah UPDATE pada baris yang ada, bukan INSERT baru.

Skema kini berdiri dan teruji, tapi buku panennya masih kosong. Bagian Pencatatan Hasil Panen mengisinya: enam belas peristiwa panen sepanjang lima belas bulan, produktivitas per hektar, dan kalender panen koperasi.

Referensi #

Berkas Sumber

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

sql
-- harga-ganda.sql — harga komoditas yang sama pada hari yang sama harus
-- ditolak UNIQUE. Berkas ini sengaja berakhir dengan error; psql keluar
-- dengan kode 3.
INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
VALUES (1, DATE '2025-03-10', 6350.00);

sql
-- tabel-lahan-komoditas.sql — tabel master lahan dan komoditas.
-- Satuan hasil diseragamkan kilogram; luas lahan dalam hektar.

CREATE TABLE lahan (
    id          integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    nama        text NOT NULL UNIQUE,
    desa        text NOT NULL,
    luas_hektar numeric(6,2) NOT NULL CHECK (luas_hektar > 0)
);

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

COMMENT ON TABLE lahan IS 'Petak lahan yang dipanen; satu baris satu petak.';
COMMENT ON COLUMN lahan.luas_hektar IS 'Luas petak dalam hektar, dasar hitung produktivitas.';
COMMENT ON TABLE komoditas IS 'Jenis tanaman yang dicatat, mis. padi, jagung, kedelai.';

sql
-- tabel-panen-harga.sql — tabel peristiwa panen dan deret harga pasar.
-- panen merujuk dua master sekaligus (lahan + komoditas) karena satu panen
-- adalah peristiwa "petak ini menanam komoditas itu"; harga_pasar berdiri
-- sendiri sebagai deret waktu per komoditas karena harga berlaku untuk pasar
-- keseluruhan, bukan untuk satu panen tertentu.

CREATE TYPE kualitas_panen AS ENUM ('A', 'B', 'C');

CREATE TABLE panen (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    lahan_id     integer NOT NULL REFERENCES lahan (id),
    komoditas_id integer NOT NULL REFERENCES komoditas (id),
    tanggal      date NOT NULL,
    hasil_kg     numeric(10,2) NOT NULL,
    kualitas     kualitas_panen NOT NULL,
    CONSTRAINT hasil_panen_positif CHECK (hasil_kg > 0),
    CONSTRAINT satu_panen_per_hari UNIQUE (lahan_id, komoditas_id, tanggal)
);

CREATE TABLE harga_pasar (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    komoditas_id integer NOT NULL REFERENCES komoditas (id),
    tanggal      date NOT NULL,
    harga_per_kg numeric(10,2) NOT NULL,
    CONSTRAINT harga_pasar_positif CHECK (harga_per_kg > 0),
    CONSTRAINT satu_harga_per_hari UNIQUE (komoditas_id, tanggal)
);

COMMENT ON TYPE kualitas_panen IS 'Grade hasil panen: A terbaik, B standar, C di bawah standar.';
COMMENT ON TABLE panen IS 'Satu baris = satu peristiwa panen: petak, komoditas, tanggal, hasil.';
COMMENT ON COLUMN panen.hasil_kg IS 'Hasil panen dalam kilogram; selalu positif.';
COMMENT ON TABLE harga_pasar IS 'Deret harga harian per komoditas; milik pasar, bukan milik satu panen.';
COMMENT ON COLUMN harga_pasar.harga_per_kg IS 'Harga per kilogram dalam rupiah pada tanggal tersebut.';

CREATE INDEX idx_panen_tanggal   ON panen (tanggal);
CREATE INDEX idx_panen_lahan     ON panen (lahan_id);
CREATE INDEX idx_panen_komoditas ON panen (komoditas_id);

-- Bukti: index yang tersedia pada keempat tabel.
-- UNIQUE (komoditas_id, tanggal) pada harga_pasar otomatis menjadi index
-- komposit untuk pola akses "harga komoditas ini sampai tanggal itu".
SELECT tablename, indexname, indexdef
FROM pg_indexes
WHERE schemaname = 'public'
  AND tablename IN ('lahan', 'komoditas', 'panen', 'harga_pasar')
ORDER BY tablename, indexname;

sql
-- uji-constraint.sql — uji positif dan uji negatif seluruh constraint skema.
-- Pelanggaran dibungkus BEGIN...EXCEPTION di dalam DO block sehingga satu
-- berkas bisa menguji banyak kasus; hasilnya dirangkum LULUS/GAGAL.

INSERT INTO lahan (nama, desa, luas_hektar)
VALUES ('Sawah Blumbang', 'Blumbang', 1.20);

INSERT INTO komoditas (nama) VALUES ('padi');

INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
VALUES (1, 1, DATE '2025-03-10', 6000.00, 'A');

INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
VALUES (1, DATE '2025-03-10', 6400.00);

CREATE TABLE hasil_uji (
    urutan integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    kasus  text NOT NULL,
    hasil  text NOT NULL
);

DO $$
BEGIN
    -- Uji positif: baris sah berikutnya harus diterima.
    INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
    VALUES (1, 1, DATE '2025-03-11', 100.00, 'B');
    INSERT INTO hasil_uji (kasus, hasil)
    VALUES ('panen sah kedua diterima', 'LULUS: baris tersimpan');

    -- Uji negatif: tiap pelanggaran harus ditolak database.
    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-12', 0.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen nol', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen nol', 'LULUS: ditolak CHECK hasil_panen_positif');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-12', -250.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen negatif', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('hasil panen negatif', 'LULUS: ditolak CHECK hasil_panen_positif');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-12', 500.00, 'D');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('kualitas di luar A/B/C', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN invalid_text_representation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('kualitas di luar A/B/C', 'LULUS: ditolak tipe enum kualitas_panen');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (99, 1, DATE '2025-03-12', 500.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen di lahan yang tidak ada', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN foreign_key_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen di lahan yang tidak ada', 'LULUS: ditolak FK ke lahan');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 99, DATE '2025-03-12', 500.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen komoditas yang tidak ada', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN foreign_key_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen komoditas yang tidak ada', 'LULUS: ditolak FK ke komoditas');
    END;

    BEGIN
        INSERT INTO panen (lahan_id, komoditas_id, tanggal, hasil_kg, kualitas)
        VALUES (1, 1, DATE '2025-03-10', 500.00, 'A');
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen ganda petak-komoditas-hari sama', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN unique_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('panen ganda petak-komoditas-hari sama', 'LULUS: ditolak UNIQUE satu_panen_per_hari');
    END;

    BEGIN
        INSERT INTO lahan (nama, desa, luas_hektar)
        VALUES ('Lahan Khayalan', 'Nowhere', 0);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('luas lahan nol', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('luas lahan nol', 'LULUS: ditolak CHECK luas_hektar');
    END;

    BEGIN
        INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
        VALUES (1, DATE '2025-03-11', 0.00);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga pasar nol', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN check_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga pasar nol', 'LULUS: ditolak CHECK harga_pasar_positif');
    END;

    BEGIN
        INSERT INTO harga_pasar (komoditas_id, tanggal, harga_per_kg)
        VALUES (1, DATE '2025-03-10', 6500.00);
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga ganda komoditas-hari sama', 'GAGAL: baris justru diterima');
    EXCEPTION WHEN unique_violation THEN
        INSERT INTO hasil_uji (kasus, hasil)
        VALUES ('harga ganda komoditas-hari sama', 'LULUS: ditolak UNIQUE satu_harga_per_hari');
    END;
END $$;

SELECT urutan, kasus, hasil
FROM hasil_uji
ORDER BY urutan;