First Normal Form

-- Tabel kontak lama Toko Maju Jaya: hasil impor formulir pendaftaran
-- yang meminta "semua nomor telepon, pisahkan dengan koma" dan
-- "minat belanja, pisahkan dengan koma".
CREATE TABLE pelanggan_lama (
    id      integer PRIMARY KEY,
    nama    text NOT NULL,
    telepon text NOT NULL,
    minat   text NOT NULL
);

INSERT INTO pelanggan_lama (id, nama, telepon, minat) VALUES
    (1, 'Budi Santoso', '0812-1111-2222, 0813-3333-4444', 'alat tulis, kertas'),
    (2, 'Siti Rahayu',  '0813-3333-4555',                  'kertas'),
    (3, 'Andi Wijaya',  '0815-9999-8888',                  'alat tulis, buku');

SELECT id, nama, telepon, minat
FROM pelanggan_lama
ORDER BY id;

-- Petugas mencari pemilik nomor 0813-3333-4444 dengan LIKE:
-- pola '%0813-3333-4%' menangkap SEMUA nomor yang memuat potongan itu,
-- termasuk nomor Siti yang hanya berbagi awalan.
SELECT id, nama
FROM pelanggan_lama
WHERE telepon LIKE '%0813-3333-4%'
ORDER BY id;

-- Bedah string pun berjebakan: string_to_array memotong pada koma,
-- tapi elemen kedua masih menyimpan spasi di depannya.
SELECT id, string_to_array(telepon, ',') AS daftar_telepon
FROM pelanggan_lama
ORDER BY id;

-- Akibat spasi itu, pencocokan persis lewat = ANY gagal diam-diam:
-- tidak ada satu pun baris yang dianggap cocok.
SELECT id, nama
FROM pelanggan_lama
WHERE '0813-3333-4444' = ANY (string_to_array(telepon, ','))
ORDER BY id;

-- Setelah setiap elemen dirapikan dengan trim, pencocokan baru bekerja --
-- tiga lapis pembedah string hanya untuk satu pencarian nomor.
SELECT id, nama
FROM pelanggan_lama
WHERE '0813-3333-4444' = ANY (
    SELECT trim(elemen)
    FROM unnest(string_to_array(telepon, ',')) AS elemen
)
ORDER BY id;

CREATE TABLE
INSERT 0 3
 id |     nama     |            telepon             |       minat        
----+--------------+--------------------------------+--------------------
  1 | Budi Santoso | 0812-1111-2222, 0813-3333-4444 | alat tulis, kertas
  2 | Siti Rahayu  | 0813-3333-4555                 | kertas
  3 | Andi Wijaya  | 0815-9999-8888                 | alat tulis, buku
(3 rows)

 id |     nama     
----+--------------
  1 | Budi Santoso
  2 | Siti Rahayu
(2 rows)

 id |           daftar_telepon           
----+------------------------------------
  1 | {0812-1111-2222," 0813-3333-4444"}
  2 | {0813-3333-4555}
  3 | {0815-9999-8888}
(3 rows)

 id | nama 
----+------
(0 rows)

 id |     nama     
----+--------------
  1 | Budi Santoso
(1 row)

-- Variasi kedua pelanggaran 1NF: kolom berulang (repeating groups).
-- Pemilik toko "menyelesaikan" masalah banyak nomor dengan tiga kolom.
CREATE TABLE pelanggan_kolom (
    id       integer PRIMARY KEY,
    nama     text NOT NULL,
    telepon1 text,
    telepon2 text,
    telepon3 text
);

INSERT INTO pelanggan_kolom (id, nama, telepon1, telepon2, telepon3) VALUES
    (1, 'Budi Santoso', '0812-1111-2222', '0813-3333-4444', NULL),
    (2, 'Siti Rahayu',  '0813-3333-4555', NULL, NULL);

SELECT id, nama, telepon1, telepon2, telepon3
FROM pelanggan_kolom
ORDER BY id;

-- Pencarian satu nomor kini harus menyebut ketiga kolom satu per satu;
-- lupa satu kolom berarti lupa satu tempat penyimpanan.
SELECT id, nama
FROM pelanggan_kolom
WHERE telepon1 = '0813-3333-4444'
   OR telepon2 = '0813-3333-4444'
   OR telepon3 = '0813-3333-4444'
ORDER BY id;

-- "Berapa nomor yang tersimpan untuk tiap pelanggan?" terjemahannya:
-- jumlahkan tiga kolom yang tidak kosong. Tambah telepon4, dan seluruh
-- query seperti ini harus ditulis ulang.
SELECT id, nama,
       (telepon1 IS NOT NULL)::int
     + (telepon2 IS NOT NULL)::int
     + (telepon3 IS NOT NULL)::int AS jumlah_nomor
FROM pelanggan_kolom
ORDER BY id;

CREATE TABLE
INSERT 0 2
 id |     nama     |    telepon1    |    telepon2    | telepon3 
----+--------------+----------------+----------------+----------
  1 | Budi Santoso | 0812-1111-2222 | 0813-3333-4444 | 
  2 | Siti Rahayu  | 0813-3333-4555 |                | 
(2 rows)

 id |     nama     
----+--------------
  1 | Budi Santoso
(1 row)

 id |     nama     | jumlah_nomor 
----+--------------+--------------
  1 | Budi Santoso |            2
  2 | Siti Rahayu  |            1
(2 rows)

-- Perbaikan pertama: data bernilai-banyak pindah menjadi baris di tabelnya
-- sendiri, terikat foreign key ke pelanggan.
CREATE TABLE pelanggan (
    id   integer PRIMARY KEY,
    nama text NOT NULL
);

INSERT INTO pelanggan (id, nama) VALUES
    (1, 'Budi Santoso'),
    (2, 'Siti Rahayu'),
    (3, 'Andi Wijaya'),
    (4, 'Dewi Lestari');

CREATE TABLE telepon (
    pelanggan_id integer NOT NULL REFERENCES pelanggan (id),
    jenis        text    NOT NULL,
    nomor        text    NOT NULL,
    PRIMARY KEY (pelanggan_id, nomor)
);

INSERT INTO telepon (pelanggan_id, jenis, nomor) VALUES
    (1, 'rumah',   '0812-1111-2222'),
    (1, 'pribadi', '0813-3333-4444'),
    (2, 'pribadi', '0813-3333-4555'),
    (3, 'kantor',  '0815-9999-8888');

-- Pencarian nomor menjadi pencocokan persis pada satu kolom;
-- tidak ada lagi LIKE, tidak ada lagi spasi tersembunyi.
SELECT p.nama, t.jenis, t.nomor
FROM pelanggan p
JOIN telepon t ON t.pelanggan_id = p.id
WHERE t.nomor = '0813-3333-4444'
ORDER BY p.id;

-- "Berapa nomor per pelanggan?" menjadi COUNT biasa;
-- LEFT JOIN memastikan pelanggan tanpa nomor tetap terhitung nol.
SELECT p.id, p.nama, count(t.nomor) AS jumlah_nomor
FROM pelanggan p
LEFT JOIN telepon t ON t.pelanggan_id = p.id
GROUP BY p.id, p.nama
ORDER BY p.id;

-- Pelanggan keempat mendaftar dengan nomor keempat jenisnya sendiri:
-- tanpa ALTER TABLE, tanpa query yang ditulis ulang.
INSERT INTO telepon (pelanggan_id, jenis, nomor)
VALUES (4, 'darurat', '0817-2222-3333');

SELECT p.nama, t.jenis, t.nomor
FROM pelanggan p
JOIN telepon t ON t.pelanggan_id = p.id
ORDER BY p.id, t.nomor;

CREATE TABLE
INSERT 0 4
CREATE TABLE
INSERT 0 4
     nama     |  jenis  |     nomor      
--------------+---------+----------------
 Budi Santoso | pribadi | 0813-3333-4444
(1 row)

 id |     nama     | jumlah_nomor 
----+--------------+--------------
  1 | Budi Santoso |            2
  2 | Siti Rahayu  |            1
  3 | Andi Wijaya  |            1
  4 | Dewi Lestari |            0
(4 rows)

INSERT 0 1
     nama     |  jenis  |     nomor      
--------------+---------+----------------
 Budi Santoso | rumah   | 0812-1111-2222
 Budi Santoso | pribadi | 0813-3333-4444
 Siti Rahayu  | pribadi | 0813-3333-4555
 Andi Wijaya  | kantor  | 0815-9999-8888
 Dewi Lestari | darurat | 0817-2222-3333
(5 rows)

-- Perbaikan kedua: minat belanja disimpan sebagai array PostgreSQL.
-- Tiap sel tetap berisi satu NILAI -- sebuah himpunan topik yang diperlakukan
-- sebagai satu kesatuan, dengan operator khusus untuk memeriksanya.
ALTER TABLE pelanggan ADD COLUMN minat text[];

UPDATE pelanggan SET minat = ARRAY['alat tulis', 'kertas'] WHERE id = 1;
UPDATE pelanggan SET minat = ARRAY['kertas']                WHERE id = 2;
UPDATE pelanggan SET minat = ARRAY['alat tulis', 'buku']    WHERE id = 3;
UPDATE pelanggan SET minat = ARRAY['kertas', 'kertas']      WHERE id = 4;

SELECT id, nama, minat
FROM pelanggan
ORDER BY id;

-- unnest membongkar array menjadi baris saat detailnya dibutuhkan.
SELECT id, nama, unnest(minat) AS topik
FROM pelanggan
ORDER BY id, topik;

-- Operator @> menjawab "pelanggan ini punya minat kertas?" tanpa bedah string.
SELECT id, nama
FROM pelanggan
WHERE minat @> ARRAY['kertas']
ORDER BY id;

-- Agregasi tetap sederhana: berapa pelanggan menyukai 'alat tulis'?
SELECT count(*) AS penyuka_alat_tulis
FROM pelanggan
WHERE 'alat tulis' = ANY (minat);

ALTER TABLE
UPDATE 1
UPDATE 1
UPDATE 1
UPDATE 1
 id |     nama     |         minat         
----+--------------+-----------------------
  1 | Budi Santoso | {"alat tulis",kertas}
  2 | Siti Rahayu  | {kertas}
  3 | Andi Wijaya  | {"alat tulis",buku}
  4 | Dewi Lestari | {kertas,kertas}
(4 rows)

 id |     nama     |   topik    
----+--------------+------------
  1 | Budi Santoso | alat tulis
  1 | Budi Santoso | kertas
  2 | Siti Rahayu  | kertas
  3 | Andi Wijaya  | alat tulis
  3 | Andi Wijaya  | buku
  4 | Dewi Lestari | kertas
  4 | Dewi Lestari | kertas
(7 rows)

 id |     nama     
----+--------------
  1 | Budi Santoso
  2 | Siti Rahayu
  4 | Dewi Lestari
(3 rows)

 penyuka_alat_tulis 
--------------------
                  2
(1 row)

-- Batas array: ketika elemen daftar mulai punya identitas relasional,
-- tempat yang benar adalah junction table, bukan satu kolom.
CREATE TABLE minat_pelanggan (
    pelanggan_id integer NOT NULL REFERENCES pelanggan (id),
    topik        text    NOT NULL,
    PRIMARY KEY (pelanggan_id, topik)
);

-- Migrasi array menjadi baris: unnest membongkar isi kolom,
-- DISTINCT menyapu bersih duplikat 'kertas' milik Dewi.
INSERT INTO minat_pelanggan (pelanggan_id, topik)
SELECT DISTINCT id, unnest(minat) AS topik
FROM pelanggan
ORDER BY id, topik;

SELECT pelanggan_id, topik
FROM minat_pelanggan
ORDER BY pelanggan_id, topik;

-- Pertanyaan agregasi per topik kini query biasa pada tabel biasa.
SELECT topik, count(*) AS peminat
FROM minat_pelanggan
GROUP BY topik
ORDER BY topik;

-- Join lintas tabel menjadi mungkin: siapa saja penyuka kertas?
SELECT p.nama, mp.topik
FROM pelanggan p
JOIN minat_pelanggan mp ON mp.pelanggan_id = p.id
WHERE mp.topik = 'kertas'
ORDER BY p.id;

CREATE TABLE
INSERT 0 6
 pelanggan_id |   topik    
--------------+------------
            1 | alat tulis
            1 | kertas
            2 | kertas
            3 | alat tulis
            3 | buku
            4 | kertas
(6 rows)

   topik    | peminat 
------------+---------
 alat tulis |       2
 buku       |       1
 kertas     |       3
(3 rows)

     nama     | topik  
--------------+--------
 Budi Santoso | kertas
 Siti Rahayu  | kertas
 Dewi Lestari | kertas
(3 rows)

-- Junction table menolak pasangan ganda lewat composite primary key --
-- penolakan yang tidak pernah diberikan oleh array.
INSERT INTO minat_pelanggan (pelanggan_id, topik)
VALUES (1, 'kertas');

psql:tolak-duplikat-topik.sql:4: ERROR:  duplicate key value violates unique constraint "minat_pelanggan_pkey"
DETAIL:  Key (pelanggan_id, topik)=(1, kertas) already exists.