17 May 2008

Implementasi Kode Wilayah

Pada Tutorial SQL terdahulu telah sedikit disinggung mengenai kode wilayah. Kode tersebut sebenarnya adalah standar kodifikasi wilayah yang resmi dikeluarkan oleh Pemerintah Indonesia melalui Biro Pusat Statistik, yang fungsinya antara lain sebagai dasar pelaporan berbagai indikator berbasis wilayah. Kodifikasi tersebut cukup lengkap, yaitu meliputi pembagian wilayah administratif mulai dari propinsi, kabupaten/kota, kecamatan hingga desa/kelurahan.

Pembahasan kali ini akan difokuskan kepada bagaimana implementasi kode wilayah tersebut kedalam database dan model query yang bisa digunakan. Model implementasi ini penting sebagai landasan awal desain database untuk berbagai keperluan/kepentingan, terutama yang satu standar dengan bentuk pelaporan pada instansi-instansi pemerintah, supaya nantinya kita dapat memetik manfaat lebih dalam menggali informasi.

Untuk itu, terlebih dahulu kita perlu melihat kodenya, kita ambil sedikit contoh:

+-------------+-----------------+
| kode | nama_wilayah |
+-------------+-----------------+
| 3400000000 | D I YOGYAKARTA | << propinsi
| 3401000000 | KULON PROGO | << kabupaten
| 3401010000 | TEMON | << kecamatan
| 3401010001 | JANGKARAN | << kelurahan
| 3401010002 | SINDUTAN | << kelurahan
| 3401010003 | PALIHAN | << kelurahan
| 3401010004 | GLAGAH | << kelurahan
| 3403000000 | GUNUNG KIDUL | << kabupaten
| 3403011000 | PURWOSARI | << kecamatan
| 3403011001 | GIRIJATI | << kelurahan
| 3403011002 | GIRIASIH | << kelurahan
| 3403011003 | GIRICAHYO | << kelurahan
| 3403011004 | GIRIPURWO | << kelurahan
| 3403011005 | GIRITIRTO | << kelurahan
| 3403020000 | PALIYAN | << kecamatan
| 3100000000 | DKI JAKARTA | << propinsi
| 3171000000 | JAKARTA SELATAN | << kota
| 3171010000 | JAGAKARSA | << kecamatan
| 3171010001 | CIPEDAK | << kelurahan
| 3171010002 | SRENGSENG SAWAH | << kelurahan
| 3171010003 | CIGANJUR | << kelurahan
+-------------+-----------------+
Jika kita perhatikan, kode diatas merangkum struktur hirarki wilayah administratif dalam satu atribut numerik (angka) yang terdiri dari 10 digit dengan susunan sebagai berikut (mulai dari kiri ke kanan):
  1. Diawali 2 digit kode propinsi.
  2. Diikuti 2 digit kode kabupaten.
  3. Diikuti 3 digit kode kecamatan.
  4. Diikuti 3 digit kode desa.
Contoh 1 : 3171010003 melambangkan:
   31 << Propinsi DKI JAKARTA
71 << Kota JAKARTA SELATAN
010 << Kecamatan JAGAKARSA
003 << Kelurahan CIGANJUR
Contoh 2 : 3400000000
   34 << Propinsi D I YOGYAKARTA
00 << kota, kosong
000 << kecamatan, kosong
000 << kelurahan, kosong
Contoh 3 : 3171000000
   31 << Propinsi DKI JAKARTA
71 << kota JAKARTA SELATAN
000 << kecamatan, kosong
000 << kelurahan, kosong
Dari ketiga contoh diatas, dapat kita lihat bagaimana struktur hirarki diperoleh/disusun dalam kodifikasi tersebut. Selanjutnya struktur tersebut akan kita manfaatkan dalam query-query.


IMPLEMENTASI

Implementasi akan kita mulai dengan membuat sebuah tabel yang sederhana terlebih dahulu, yaitu tabel dengan 2 atribut, yaitu kode dan nama_wilayah:
CREATE TABLE wilayah (
kode char(10) NOT NULL default '',
nama_wilayah char(100) NOT NULL default '',
PRIMARY KEY (kode)
);
Kemudian kita isi dengan data contoh:
INSERT INTO wilayah VALUES ('3400000000','D I YOGYAKARTA');
INSERT INTO wilayah VALUES ('3401000000','KULON PROGO');
INSERT INTO wilayah VALUES ('3401010000','TEMON');
INSERT INTO wilayah VALUES ('3401010001','JANGKARAN');
INSERT INTO wilayah VALUES ('3401010002','SINDUTAN');
INSERT INTO wilayah VALUES ('3401010003','PALIHAN');
INSERT INTO wilayah VALUES ('3401010004','GLAGAH');
INSERT INTO wilayah VALUES ('3403000000','GUNUNG KIDUL');
INSERT INTO wilayah VALUES ('3403011000','PURWOSARI');
INSERT INTO wilayah VALUES ('3403011001','GIRIJATI');
INSERT INTO wilayah VALUES ('3403011002','GIRIASIH');
INSERT INTO wilayah VALUES ('3403011003','GIRICAHYO');
INSERT INTO wilayah VALUES ('3403011004','GIRIPURWO');
INSERT INTO wilayah VALUES ('3403011005','GIRITIRTO');
INSERT INTO wilayah VALUES ('3403020000','PALIYAN');
INSERT INTO wilayah VALUES ('3100000000','DKI JAKARTA');
INSERT INTO wilayah VALUES ('3171000000','JAKARTA SELATAN');
INSERT INTO wilayah VALUES ('3171010000','JAGAKARSA');
INSERT INTO wilayah VALUES ('3171010001','CIPEDAK');
INSERT INTO wilayah VALUES ('3171010002','SRENGSENG SAWAH');
INSERT INTO wilayah VALUES ('3171010003','CIGANJUR');

QUERY

Selanjutnya akan kita lakukan test untuk mengetahui apakah desain tabel yang sederhana tersebut sudah cukup mampu mengakomodasi kebutuhan informasi kita.

1. Tampilkan propinsi:
SELECT * FROM wilayah
WHERE kode LIKE '__00000000';
hasilnya:
+------------+----------------+
| kode | nama_wilayah |
+------------+----------------+
| 3400000000 | D I YOGYAKARTA |
| 3100000000 | DKI JAKARTA |
+------------+----------------+
2. Tampilkan kota/kabupaten saja:
SELECT * FROM wilayah
WHERE kode LIKE '____000000'
AND kode NOT LIKE '__00000000';
hasilnya:
+------------+-----------------+
| kode | nama_wilayah |
+------------+-----------------+
| 3401000000 | KULON PROGO |
| 3403000000 | GUNUNG KIDUL |
| 3171000000 | JAKARTA SELATAN |
+------------+-----------------+
3. Tampilkan kecamatan saja:
SELECT * FROM wilayah
WHERE kode LIKE '_______000'
AND kode NOT LIKE '____000000';
hasilnya:
+------------+--------------+
| kode | nama_wilayah |
+------------+--------------+
| 3401010000 | TEMON |
| 3403011000 | PURWOSARI |
| 3403020000 | PALIYAN |
| 3171010000 | JAGAKARSA |
+------------+--------------+
4. Tampilkan desa/kelurahan saja:
SELECT * FROM wilayah
WHERE kode NOT LIKE '0';
hasilnya:
+------------+-----------------+
| kode | nama_wilayah |
+------------+-----------------+
| 3401010001 | JANGKARAN |
| 3401010002 | SINDUTAN |
| 3401010003 | PALIHAN |
| 3401010004 | GLAGAH |
| 3403011001 | GIRIJATI |
| 3403011002 | GIRIASIH |
| 3403011003 | GIRICAHYO |
| 3403011004 | GIRIPURWO |
| 3403011005 | GIRITIRTO |
| 3171010001 | CIPEDAK |
| 3171010002 | SRENGSENG SAWAH |
| 3171010003 | CIGANJUR |
+------------+-----------------+
5. Tampilkan desa/kelurahan yang berada di kecamatan JAGAKARSA saja:
SELECT * FROM wilayah
WHERE kode LIKE '3171010%'
AND kode NOT LIKE '0';
hasilnya:
+------------+-----------------+
| kode | nama_wilayah |
+------------+-----------------+
| 3171010001 | CIPEDAK |
| 3171010002 | SRENGSENG SAWAH |
| 3171010003 | CIGANJUR |
+------------+-----------------+
6. Berapa jumlah kelurahan di propinsi D I YOGYAKARTA:
SELECT COUNT(*) FROM wilayah
WHERE kode LIKE '34%'
AND kode NOT LIKE '0';
hasilnya:
+----------+
| COUNT(*) |
+----------+
| 9 |
+----------+

APLIKASI

Sudah tergambar idenya? Kami kira tidak terlalu sulit bagaimana aplikasi praktisnya. Kita ambil contoh sebuah metode sederhana survei kasus-kasus penyakit x, dengan cukup menggunakan satu tabel untuk data kasus dengan struktur sebagai berikut:
+--------------+----------+
| Field | Type |
+--------------+----------+
| kasus_no | int(10) | << nomor kasus
| kode | char(10) | << kode wilayah
+--------------+----------+
Berikut ini perintah untuk membuat tabel dan sekaligus mengisinya dengan contoh datanya:
CREATE TABLE kasus (
kasus_no int(10) unsigned NOT NULL default '0',
kode char(10) NOT NULL default '',
PRIMARY KEY (kasus_no)
);

INSERT INTO kasus VALUES(1,'3171010003');
INSERT INTO kasus VALUES(2,'3171010002');
INSERT INTO kasus VALUES(3,'3171010001');
INSERT INTO kasus VALUES(4,'3403011005');
INSERT INTO kasus VALUES(5,'3403011004');
INSERT INTO kasus VALUES(6,'3403011005');
INSERT INTO kasus VALUES(7,'3403011003');
INSERT INTO kasus VALUES(8,'3401010004');
INSERT INTO kasus VALUES(9,'3171010003');
INSERT INTO kasus VALUES(10,'3403011003');
INSERT INTO kasus VALUES(11,'3403011005');
INSERT INTO kasus VALUES(12,'3171010003');
INSERT INTO kasus VALUES(13,'3401010004');
INSERT INTO kasus VALUES(14,'3401010003');
INSERT INTO kasus VALUES(15,'3401010001');
INSERT INTO kasus VALUES(16,'3401010002');
INSERT INTO kasus VALUES(17,'3401010003');
INSERT INTO kasus VALUES(18,'3171010001');
INSERT INTO kasus VALUES(19,'3401010003');
INSERT INTO kasus VALUES(20,'3171010003');
Lalu segera kita coba hasilnya dengan query berikut:
SELECT b.nama_wilayah as 'Wilayah',
COUNT(a.kasus_no) as 'Jumlah Kasus'
FROM kasus a
LEFT JOIN wilayah b USING(kode)
GROUP BY a.kode ORDER BY a.kode;
hasilnya:
+-----------------+--------------+
| Wilayah | Jumlah Kasus |
+-----------------+--------------+
| CIPEDAK | 2 |
| SRENGSENG SAWAH | 1 |
| CIGANJUR | 4 |
| JANGKARAN | 1 |
| SINDUTAN | 1 |
| PALIHAN | 3 |
| GLAGAH | 2 |
| GIRICAHYO | 2 |
| GIRIPURWO | 1 |
| GIRITIRTO | 3 |
+-----------------+--------------+
Kita coba lagi dengan query yang lain:
SELECT b.nama_wilayah as Wilayah,
COUNT(a.kasus_no) as 'Jumlah Kasus'
FROM kasus a
LEFT JOIN wilayah b
ON b.kode = CONCAT(SUBSTRING(a.kode,1,2),'00000000')
GROUP BY b.kode
ORDER BY b.kode;
hasilnya:
+----------------+--------------+
| Wilayah | Jumlah Kasus |
+----------------+--------------+
| DKI JAKARTA | 7 |
| D I YOGYAKARTA | 13 |
+----------------+--------------+
Selanjutnya terserah Anda. Tentunya akan lebih menarik lagi jika struktur tabel kasus tersebut dilengkapi dengan atribut lain misalnya: waktu, jenis kelamin, usia, dll. Okay, sekian dan enjoy ...

17 December 2006

INDEX

Setiap orang yang menggeluti SQL pasti akan bertemu dengan INDEX. Apa dan bagaimana yang dimaksud dengan INDEX? Dari hasil googling dengan keyword "define:index" (tanpa tanda petik) akan langsung ketemu banyak definisi dari INDEX yang tersebar di internet (catatan: "define:" adalah kata kunci spesial yang dapat digunakan untuk mencari berbagai definisi menggunakan google), namun dari hasil yang banyak tersebut saya memilih definisi yang berikut ini:

A special database object that lets you quickly locate particular records based on key column values. Indexes are essential for good database performance.
www.orafaq.com/glossary/faqglosi.htm
Ada 3 cara untuk membuat INDEX didalam database MySQL:
  1. Pada saat CREATE TABLE
    Kita dapat langsung menyisipkan syntax untuk membuat index, misalnya:
    CREATE TABLE person (
    nomor_id int(10) unsigned NOT NULL default '0',
    nama char(100) NOT NULL default '',
    INDEX nomor_id_idx (nomor_id)
    );
  2. Dengan perintah CREATE INDEX
    Dengan cara ini, tabel pada mulanya di-CREATE tanpa INDEX, kemudian index dibuat belakangan dengan perintah CREATE INDEX:
    CREATE INDEX nomor_id_idx ON person (nomor_id);
    Syntax-nya secara empiris dapat ditulis:
    CREATE INDEX nama_index ON nama_tabel (nama_kolom, ...);
  3. Dengan perintah ALTER TABLE
    Hampir mirip dengan CREATE INDEX, index ini dibuat belakangan, dengan perintah:
    ALTER TABLE person ADD INDEX nomor_id_idx (nomor_id);
    Dan bentuk empirisnya:
    ALTER TABLE nama_tabel ADD INDEX nama_index (nama_kolom, ...);
Dari ketiga cara di atas, terserah kepada kita untuk menggunakan yang mana, semuanya sama saja.

Sedangkan untuk menghapus index menggunakan perintah:
ALTER TABLE nama_tabel DROP INDEX nama_index;

Setelah index dibuat, lalu bagaimana cara menggunakannya? Penggunaan index didalam query biasanya terjadi secara otomatis oleh database engine-nya, namun otomatisasi tersebut dapat kita arahkan atau kendalikan, yaitu dengan panduan atau patokan sebagai berikut: Yaitu index akan digunakan:
  • Pada klausa WHERE
    Maka kemungkinan kita perlu untuk membuat index pada setiap kolom yang tersebut didalam klausa WHERE. Misalnya:
    SELECT * FROM person WHERE nama = 'John';
    Jika jumlah record hanya sedikit, INDEX tidak begitu berpengaruh. Namun jika record sudah mencapai puluhan ribu bahkan ratusan ribu, maka kita perlu membuat INDEX untuk kolom nama:
    CREATE INDEX nama_idx ON person (nama(10));
    Perintah diatas akan membuat index untuk kolom nama namun hanya menggunakan 10 karakter pertama saja. Dengan hanya 10 karakter saja, index menjadi lebih ramping dan dapat diakses lebih cepat. Teknik ini cukup optimal dibandingkan dengan membuat index seluruh panjang data kolom (100 karakter) yang mengakibatkan ukuran index menjadi sangat besar dan lambat. Namun untuk mengetahui ukuran optimalnya, apakah 10 atau 15 atau 50 karakter yang akan di-index, kita harus melihat karakteristik datanya, misalnya untuk nama orang - 10 karakter sudah cukup menjadi pembeda.

  • Pada klausa JOIN:
    SELECT a.id_pasien,b.nama
    FROM pasien a
    LEFT JOIN person b ON b.nomor_id = a.nomor_id
    Maka kita perlu membuat index untuk kolom nomor_id pada tabel person (sudah kita buat untuk contoh awal). Mengapa yang perlu dibuat adalah index untuk kolom nomor_id pada tabel person, bukan pada tabel pasien? Karena pencarian berdasarkan kolom nomor_id hanya akan dilakukan pada tabel person saja, dan pencarian inilah yang akan menggunakan index (jika ada).

  • Pada klausa ORDER BY
    SELECT * FROM person ORDER BY nama;
    Query tersebut akan mengurutkan hasil query berdasarkan nama. Sedangkan sifat alamiah dari index ialah berurutan, maka query tersebut akan sangat terbantu jika sudah ada index pada kolom tersebut.

  • Pada klausa GROUP BY
    SELECT COUNT(*) FROM person GROUP BY usia;
    Hampir sama dengan ORDER BY, jika ada index pada kolom usia, maka proses query akan menggunakannya.

Dengan memahami karakteristik tersebut diatas maka barulah kita dapat merancang query yang optimal dan efisien. Terutama saya, pada query dengan JOIN, kadang kala saya lupa untuk membuat index, setelah query bertambah rumit baru dirasa lambat sekali. Kemudian setelah dibuat index untuk semua kolom yang tersebut didalam klausa JOIN, barulah query dapat selesai dalam waktu sekejap.

21 November 2006

Query dengan JOIN

Untuk apa kita perlu query join? Query join berfungsi untuk menggabungkan beberapa tabel menjadi sebuah tabel gabungan. Teknik menggabung tersebut adalah keunggulan dari basis data relasional (RDBMS: Relational Database Management System), dimana data dapat disimpan dalam beberapa tabel kecil yang terpisah, bukannya satu tabel besar dengan banyak kolom. Pada dunia nyata pemrograman, query join sangat banyak dan hampir selalu digunakan, karena dengan itu sebenarnya kunci pengolahan data dengan SQL dan dari situ dapat dihasilkan informasi-informasi yang lebih bermakna dibandingkan dengan query satu tabel saja.

Cara kerja join ialah dengan mencari kecocokan kolom tertentu pada sebuah tabel dengan kolom tertentu pada tabel lain, kemudian menampilkan hasilnya sebagai satu set data gabungan. Secara garis besar, terdapat 3 macam join, yaitu INNER JOIN, LEFT JOIN dan RIGHT JOIN. Namun, selain dari 3 macam tersebut, terdapat tambahan beberapa varian tergantung dari RDBMS yang digunakan.

Sebagai contoh, kita akan men-join tabel pasien yang menyimpan informasi biodata pasien-pasien, dengan tabel symptom yang menyimpan data-data symptom yang diderita oleh pasien. Kedua tabel tersebut akan di-join berdasarkan kolom yang berisi nomor identitas pasien (id) yang terdapat pada kedua tabel.


CREATE TABLE pasien (id int(10), nama char(30));
INSERT INTO pasien (id,nama) VALUES ('1','John');
INSERT INTO pasien (id,nama) VALUES ('2','Jane');
INSERT INTO pasien (id,nama) VALUES ('3','Mike');
INSERT INTO pasien (id,nama) VALUES ('4','Doel');

CREATE TABLE symptom (id int(10), symptom char(30));
INSERT INTO symptom (id,symptom) VALUES ('1','Demam');
INSERT INTO symptom (id,symptom) VALUES ('2','Pusing');
INSERT INTO symptom (id,symptom) VALUES ('3','Mual');

INNER JOIN

Dengan INNER JOIN hanya akan ditampilkan baris-baris yang satu sama lain memiliki kecocokan.

Contoh:
SELECT a.nama, b.symptom
FROM pasien a
INNER JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam |
| Jane | Pusing |
| Mike | Mual |
+------+---------+

LEFT JOIN

Sebuah query dengan LEFT JOIN akan menampilkan semua baris dari tabel sebelah kiri (pertama/utama atau pasien) dengan gabungan dari tabel sebelah kanan (kedua atau symptom).

Contoh:
SELECT a.nama, b.symptom
FROM pasien a
LEFT JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam |
| Jane | Pusing |
| Mike | Mual |
| Doel | NULL |
+------+---------+
Perbedaannya dengan INNER JOIN ialah, pada LEFT JOIN - seluruh baris dari tabel pertama akan ditampilkan meskipun tidak ada kecocokan baris pada tabel kedua. Lihat contoh diatas, pasien Doel tidak memiliki data symptom sehingga tertampil NULL (NULL disini menyatakan bahwa tidak ada data pada kolom tersebut). Selain itu, posisi tabel jadi mempunyai makna, yaitu tabel kiri atau pertama (ialah tabel sebelum 'LEFT JOIN') akan menjadi tabel utama yang diquery terlebih dahulu, baru kemudian dicarikan kecocokannya dengan tabel kanan atau kedua (ialah tabel setelah 'LEFT JOIN').


RIGHT JOIN

Query RIGHT JOIN hampir sama dengan LEFT JOIN, hanya saja posisinya terbalik, yaitu akan menampilkan semua baris dari tabel kanan (kedua) meskipun tidak ada kecocokan pada tabel kiri (pertama).

Contoh:
SELECT a.nama, b.symptom
FROM pasien a
RIGHT JOIN symptom b
ON b.id = a.id;
+------+---------+
| nama | symptom |
+------+---------+
| John | Demam |
| Jane | Pusing |
| Mike | Mual |
+------+---------+


Tips dan Pengalaman Pribadi

Jika ada yang bertanya dengan sebuah kasus, harus menggunakan join yang mana? Maka kita perlu melihat keperluan terlebih dahulu, selain itu optimasi juga perlu menjadi pertimbangan, intinya ialah ketiga macam join dapat digunakan. Namun, saya pribadi paling sering menggunakan LEFT JOIN dibanding jenis JOIN yang lain. Alasan saya ialah, query LEFT JOIN lebih fleksibel dan lebih mudah dioptimasi. Mari kita ambil contoh query dengan INNER JOIN diatas, dapat juga dihasilkan dengan query LEFT JOIN, dengan menambahkan klausa WHERE:
SELECT a.nama, b.symptom
FROM pasien a
LEFT JOIN symptom b
ON b.id = a.id
WHERE b.symptom IS NOT NULL;
Atau pun query RIGHT JOIN diatas dihasilkan dengan model LEFT JOIN, hanya dengan membalik urutan tabel:
SELECT a.nama, b.symptom
FROM symptom b
LEFT JOIN pasien a
ON b.id = a.id;
Kemudian salah satu pertimbangan kecenderungan saya menggunakan LEFT JOIN ialah untuk optimasi. Contoh, misalnya kita dihadapkan pada tabel pasien dan tabel symptom, jika ada pertanyaan untuk menampilkan pasien dengan sebuah symptom tertentu, misalnya demam, maka saya akan menempatkan tabel symptom sebagai tabel utama, baru kemudian tabel pasien sebagai tabel kedua:
SELECT a.id,b.nama
FROM symptom a
LEFT JOIN pasien b
ON b.id = a.id
WHERE a.symptom = 'Demam';
Bagaimana query tersebut lebih optimal? Penjelasannya begini: query tersebut akan mencari record-record dari tabel symptom yang memenuhi kriteria symptom = 'Demam', hal ini akan menghindari full table scan (dengan catatan jika ada indeks berdasarkan symptom), selanjutnya akan dicari padanan id di tabel pasien. Sebagai catatan: full tabel scan akan memakan sumber daya memori dan tenaga proses di CPU, membuat query menjadi sangat lambat terutama jika tabel memiliki jutaan record. Mengenai indeks pada tabel akan saya jelaskan pada kesempatan lain.

Lalu bagaimana dengan RIGHT JOIN? Sebenarnya RIGHT JOIN dapat pula didayagunakan seperti LEFT JOIN, hanya masalah kebiasaan saja. Kadang-kadang saya juga menggunakan RIGHT JOIN untuk kasus tertentu demi alasan code readability atau simplicity.