Lebih Lanjut Dengan MySQL
6.1 Merancang
Database
Ketika Anda
menggunakan sistem menajemen database relasional seperti MySQL, langkah pertama
dalam menciptakan dan menggunakan database adalah menetapkan struktur database
(dikenal pula dengan skema database). Perancangan database, dikenal juga dengan
pemodelan database, merupakan hal krusial dalam pengelolaan informasi. Dengan
menggunakan sebuah proses yang dinamakan dengan normalisasi, Anda dapat secara
hatu-hati mengeliminasi redudansi dan masalah-masalah lain yang akan mengurangi
integritas dari database Anda.
Teknik-teknik
yang akan Anda pelajari pada beberapa halaman ke depan akan membantu Anda untuk
memastikan kegunaan dan kehandalan database Anda.
6.2
Normalisasi
Normalisasi
dikembangkan oleh peneliti IBM yang bernama E.F. Codd pada awal 1970an (ia juga
yang menemukan database relasiona). Database relasional merupakan sekumpulan
data, yang diorganisir dengan cara tertentu, dan Dr. Codd menciptakan sejumlah
aturan yang dinamakan dengan normal form
untuk mendefinisikan organisasi data tersebut. Pada bab ini, tiga normal form
pertama akan didiskusikan, yang menjadi syarat cukup untuk hampir semua
perancangan database.
Sebelum Anda
mulia menormalisasi database, Anda perlu mendefinisikan peran dari aplikasi
yang sedang dikembangkan karena pemahaman akan bagaimana informasi akan
diakases akan menentukan arah pemodelan. Jadi,
proses ini membutuhkan kertas dan pena bukan hanya memerlukan
perangkat-lunak MySQL.
Pada contoh ini,
Anda akan menciptakan sebuah papan pesan dimana para pengguna dapat mengirimkan
pesan dan pengguna lain dapat menjawabnya. Anda tentu membayangkan bahwa para
pengguna perlu melakukan registrasi, kemudian melakukan login dengan kombinasi
nama user dan password, agar bisa mengirimkan pesan. Anda juga tentu
mengharapkan terdapat sejumlah forum untuk pelbagai topik diskusi. Beberapa
contoh dari baris data ditampilkan pada Tabel 6.1. Database diberi nama dengan forum.
TABEL 6.1 Beberapa contoh
dari baris data yang disimpan di dalam database.
Item
|
Contoh
|
nama user
|
kristof99
|
password
|
passku
|
nama aktual
|
Kristof Manahan
|
email user
|
contoh@gmail.com
|
forum
|
MySQL
|
judul pesan
|
Pertanyaan tentang normalisasi.
|
tubuh pesan
|
Saya ingin bertanya tentang…
|
tanggal pesan
|
February 2, 2015 12:20 AM
|
6.3
Kunci
Seperti telah
dijelaskan secara singkat pada Bab 4, kunci merupakan bagian integral di dalam
database yang ternormalisasi. Ada dua jenis kunci: kunci primer (primary key) dan kunci luar (foreign key). Kunci primer merupakan
pengenal unik yang terikat oleh beberapa aturan:
·
Selalu memiliki
nilai (tidak bisa berupa NULL).
·
Memiliki nilai
yang tidak berubah.
·
Memiliki nilai
unik untuk tiap rekaman di dalam tabel.
Contoh praktis
yang paling nyata dari kunci primer adalah nomor induk kependudukan (NIK) pada
kartu tanda penduduk (KTP) Anda, dimana setiap NIK merupakan nomor yang unik
dan tidak bisa berubah.
Jenis kunci
kedua adalah kunci luar atau foreign key.
Kunci luar merupakan representasi kunci primer pada Tabel A di dalam Tabel B.
Jika Anda memiliki sebuah database sinema dengan sebuah tabel film dan tabel
sutradara, maka kunci primer dari tabel sutradara akan dihubungkan sebagai
kunci luar di dalam tabel film. Anda akan lebih memahami hal ini ketika proses
normalisasi dilakukan.
Database forum
hanya memuat satu tabel (Tabel 6.1), tetapi sebelum memulai proses normalisasi,
Anda perlu mengidentifikasi sedikitnya satu kunci primer.
Menugaskan kunci primer
1. Lihat kembali semua bidang yang dapat memenuhi ketiga syarat untuk kunci
primer. Pada contoh ini (Tabel 6.1), tidak ada kolom yang memenuhi ketiga
kriteria yang bisa dijadikan kunci primer. Bidang nama user dan email akan
unik untuk setiap forum tetapi tidak unik untuk tiap rekaman di dalam database
(karena user yang sama dapat mengirimkan pesan lebih dari satu). Bidang judul pesan yang sama juga dapat
digunakan beberapa kali. Bidang tubuh
pesan kemungkinan bisa unik untuk setiap pesan tetapi ia dapat berubah
(jika diedit), yang melanggar salah satu aturan dari kunci primer.
2. Jika tidak ada kandidat logis untuk kunci primer, Anda perlu
menciptakannya (Tabel 6.2). Pada banyak kasus, Anda perlu menciptakan kunci
primer ketika tidak ditemukan kandidat logis dari kolom-kolom yang ada. Pada
contoh ini, ID pesan akan diciptakan.
TABEL 6.2 Sebuah kunci
primer ditambahkan ke dalam tabel sebagai cara mudah untuk mengakses rekaman.
Item
|
Contoh
|
ID pesan
|
234
|
nama user
|
kristof99
|
password
|
passku
|
nama aktual
|
Kristof Manahan
|
email user
|
contoh@gmail.com
|
forum
|
MySQL
|
judul pesan
|
Pertanyaan tentang normalisasi.
|
tubuh pesan
|
Saya ingin bertanya tentang…
|
tanggal pesan
|
February 2, 2015 12:20 AM
|
6.4
Relasi
Relasi database terkait
dengan bagaimana data pada satu tabel berelasi dengan data pada tabel lain. Ada
tiga jenis relasi antar dua tabel: satu-ke-satu, satu-ke-banyak, atau
banyak-ke-banyak. (Dua tabel di dalam sebuah tabel bisa saja tidak berelasi).
Relasi dikatakan
satu-ke-satu jika hanya satu item di dalam Tabel A yang berelasi dengan satu
item di dalam Tabel B. Sebagai contoh, setiap penduduk Indonesia hanya memiliki
satu NIK, dan setiap NIK hanya dimiliki oleh satu orang warga negara Indonesia;
Tidak boleh seorang warga Indonesia memiliki dua NIK, dan satu NIK tidak bisa
digunakan untuk dua warga Indonesia.
Relasi dikatakan
satu-ke-banyak jika satu item di dalam Tabel A berelasi dengan beberapa item di
dalam Tabel B. Terminologi perempuan dan lelaki dapat diterapkan pada banyak
orang, tetapi setiap orang hanya dapat berjenis kelamin perempuan atau lelaki.
Relasi satu-ke-banyak merupakan relasi antar tabel yang paling sering dijumpai
pada database yang ternormalisasi.
Terakhir, relasi
dikatakan banyak-ke-banyak jika beberapa item di dalam Tabel A berelasi dengan
beberapa item di dalam Tabel B. Sebuah album rekaman dapat memuat beberapa lagu
yang dinyanyikan oleh beberapa penyanyi, dan beberapa penyanyi dapat membuat
beberapa album. Sebisa mungkin Anda perlu menghindari relasi banyak-ke-banyak
pada perancangan database Anda karena sering menyebabkan redudansi dan
permasalahan integritas. Untuk menggantikan relasi banyak-ke-banyak, database
yang dirancang dengan baik biasanya menggunakan tabel-tabel perantara yang memecah
satu relasi banyak-ke-banyak menjadi dua relasi satu-ke-banyak.
Relasi dan kunci
dipakai secara bersamaan dimana satu kunci di dalam suatu tabel berelasi dengan
satu kunci di dalam tabel lain.
Pemodelan
database menggunakan beberapa konvensi untuk merepresentasikan struktur
database. Simbol-simbol untuk ketiga jenis relasi ditampilkan pada Gambar 6.1.
Gambar 6.1 Simbol-simbol
ini umumnya dipakai untuk merepresentasikan relasi-relasi dalam skema pemodelan
database.
6.5
Normal Form Pertama (1NF)
Seperti
dinyatakan sebelumnya, normalisasi atas sebuah database merupakan proses
pengubahan struktur database menggunakan beberapa aturan (form). Database Anda
harus memenuhi setiap aturan ini secara berurutan.
Setiap tabel di
dalam sebuah database harus memiliki dua watak untuk memenuhi Normal Form
pertama (1NF):
·
Setiap kolom
hanya memuat satu nilai (kolom bersifat atomik).
·
Tidak ada tabel
yang memiliki sekelompok data yang berelasi.
Sebuah tabel satu
bidang untuk keseluruhan alamat seseorang (jalan, kota, propinsi, kode pos,
negara) tidak memenuhi 1NF, karena ada beberapa nilai pada satu kolom, yang
melanggar watak pertama. Sebuah tabel film yang memiliki kolom-kolom seperti
aktor1, aktor2, aktor3, dan seterusnya tidak memenuhi watak kedua dari 1NF karena
setiap kolom mencantumkan jenis informasi yang sama.
Anda akan
memulai proses normalisasi dengan memeriksa struktur yang telah ada (Tabel 6.2)
untuk ketaatan pada dua watak dari 1NF. Sembarang kolom yang tidak bersifat
atomik akan dipecah menjadi beberapa kolom. Jika sebuah tabel memiliki
kolom-kolom yang hampir sama, maka kolom-kolom tersebut akan dipisahkan pada
tabel-tabel sendiri.
Membuat database yang taat pada 1NF
1. Identifikasi setiap bidang yang memuat beberapa informasi. Lihat pada
Tabel 6.2, satu bidang tidak taat pada 1NF: nama aktual. Contoh rekaman memuat nama depan dan nama belakang
pada kolom ini. Bidang tanggal pesan
memuat hari, bulan, dan tahun, ditambah dengan waktu. Tetapi hal ini dapat
ditangani dengan baik dengan menggunakan tipe DATETIME.
Contoh-contoh masalah lain adalah
ketika sebuah tabel menggunakan satu kolom untuk beberapa nomor telepon (hp,
rumah, kantor), atau ketika sebuah tabel menyimpan beberapa minat dari
seseorang (memasak, menari, membaca, dll) pada sebuah kolom.
2. Pecah setiap bidang yang ditemukan pada langkah 1 menjadi bidang-bidang
terpisah (Tabel 6.3). Untuk menyelesaikan permasalahan ini, Anda akan
menciptakan bidang nama depan dan
bidang nama belakang secara
terpisah, yang masing-masing hanya memuat satu nilai.
TABEL 6.3 Kolom nama
aktual dipecah menjadi dua kolom.
Item
|
Contoh
|
ID pesan
|
234
|
nama user
|
kristof99
|
password
|
passku
|
nama depan
|
Kristof
|
nama belakang
|
Manahan
|
email user
|
contoh@gmail.com
|
forum
|
MySQL
|
judul pesan
|
Pertanyaan tentang normalisasi.
|
tubuh pesan
|
Saya ingin bertanya tentang…
|
tanggal pesan
|
February 2, 2015 12:20 AM
|
3. Tempatkan kolom-kolom yang berulang menjadi tabel-tabel. Database forum tidak memiliki permasalahan ini
sekarang, jadi untuk mendemonstrasikan hal ini, ciptakan Tabel 6.4.
Tabel tersebut memuat beberapa kolom
berulang yang mengintroduksi dua permasalahan. Pertama, fakta bahwa setiap film
memiliki keterbatasan jumlah aktor. Bahkan jika Anda menambahkan kolom aktor 1 sampai aktor 100, tetap saja ada keterbatasan jumlah aktor (100 aktor).
Kedua, sembarang rekaman yang tidak memiliki jumlah aktor maksimum akan
memiliki nilai-nilai NULL pada kolom-kolom yang tak terisi. Anda sebaiknya
menghindari nilai NULL pada skema database. Selain itu, kolom aktor dan kolom sutradara tidak berwatak atomik.
TABEL 6.4 Tabel film ini melanggar aturan 1NF karena
dua alasan. Pertama, ia memiliki kolom-kolom berulang untuk data yang mirip (aktor 1, aktor 2, dan aktor 3).
Kedua, kolom aktor dan kolom sutradara tidak berwatak atomik.
Database sinema: Tabel film
|
|
Kolom
|
Nilai
|
ID film
|
456
|
judul film
|
Casablanca
|
tahun rilis
|
1943
|
sutradara
|
Michael Curtiz
|
aktor 1
|
Humphrey Bogart
|
aktor 2
|
Ingrid Bergman
|
aktor 3
|
Peter Lorre
|
Untuk menyelesaikan kedua
permasalahan ini di dalam tabel film, tabel kedua akan diciptakan (Tabel 6.5).
Tabel ini menggunakan satu baris untuk setiap aktor di dalam tabel film, yang
menyelesaikan permasalahan yang disebut pada paragraf terakhir tersebut. Nama-nama
aktor dipecah agar berwatak atomik. Perhatikan pula bahwa kolom kunci primer
sebaiknya ditambahkan pada tabel baru.
TABEL 6.5 Untuk membuat
tabel film (Tabel 6.4) menaati 1NF,
aktor-aktor yang terkait dengan sebuah film akan ditempatkan pada tabel ini.
Tabel Film-Aktor
|
|||
ID
|
Film
|
Nama Depan Aktor
|
Nama Belakang Aktor
|
1
|
Casablanca
|
Humphrey
|
Bogart
|
2
|
Casablanca
|
Ingrid
|
Bergman
|
3
|
Casablanca
|
Peter
|
Lorre
|
4
|
The Maltese Falcon
|
Humphrey
|
Bogart
|
5
|
The Maltese Falcon
|
Peter
|
Lorre
|
4. Periksa kembali bahwa semua kolom baru dan tabel-tabel yang diciptakan
pada langkah 2 dan langkah 3 menaati 1NF.
6.6
Normal Form Kedua (2NF)
Agar database
menaati normal form kedua (2NF), ia pertama-tama harus menaati 1NF (Anda perlu
menormalisasi database secara berurutan). Kemudian, setiap kolom di dalam tabel
yang bukan sebuah kunci harus bergantung pada kunci primer.
Kembali ke
contoh sinema, tabel film (Tabel
6.4) bisa saja akan memiliki sutradara Martin Scorsese dicantumkan sebanyak dua
puluh satu kali. Ini melanggar 2NF karena kolom yang menyimpan nama-nama
sutradara tidak berupa kunci dan tidak akan bergantung pada kunci primer (ID film). Untuk menyelesaikan
permasalahan ini Anda perlu menciptakan sebuah tabel sutradara yang terpisah yang menyimpan informasi sutradara-sutradara
dan menugaskan kunci primer kepada setiap sutradara. Untuk mengikat kembali sutradara ke dalam tabel film, kunci primer dari sutradara akan
dijadikan kunci luar pada tabel film.
Lihat Tabel 6.5
(untuk aktor-aktor film), nama film dan nama-nama aktor juga melanggar 2NF
(karena tidak berupa kunci dan tidak bergantung pada kunci primer pada tabel). Di
akhir, database sinema akan
memerlukan empat tabel (Gambar 6.2). Setiap nama sutradara, nama film, dan nama
aktor hanya akan disimpan sekali saja, dan sembarang kolom tak-kunci pada
sebuah tabel bergantung pada kunci primer tabel tersebut.
Jadi,
normalisasi dapat disimpulkan sebagai proses penciptaan tabel-tabel sampai
semua redudansi tereliminasi.
Gambar 6.2 Untuk membuat
database sinema menaati 2NF, empat tabel diperlukan. Setiap sutradara
direpresentasikan di dalam tabel film melalui kunci ID sutradara; setiap film
direpresentasikan di dalam tabel film-aktor melalui kunci ID film; dan setiap
aktor direpresentasikan di dalam tabel film-aktor melalui kunci ID aktor.
Membuat database yang taat pada 2NF
1. Identifikasi semua kolom tak-kunci yang tidak bergantung pada kunci
primer. Lihat Tabel 6.3, nama user, nama depan, nama belakang, email,
dan nilai-nilai forum semuanya tak-kunci (ID pesan merupakan satu-satunya kolom
kunci), dan tidak ada yang bergantung pada ID pesan. Namun, judul pesan, tubuh pesan, dan tanggal pesan ketiganya tak-kunci
tetapi bergantung pada ID pesan.
2. Ciptakan tabel-tabel (Gambar 6.3). Modifikasi yang paling logis untuk
database forum adalah dengan membuat
tiga tabel: Tuser, Tforum, dan Tpesan. Dalam representasi visual dari database, ciptakan sebuah
kotak untuk tiap tabel, dengan nama tabel sebagai kepala kolom dan semua kolom
(atribut) berada di bawahnya.
Gambar 6.3 Untuk membuat
database forum menaati 2NF, tiga tabel diperlukan.
3. Tugaskan atau ciptakan kunci-kunci primer baru (Gambar 6.4). Dengan
menggunakan beberapa teknik yang telah dijelaskan sebelumnya, pastikan bahwa
setiap tabel baru memiliki sebuah kunci primer. Di sini, Anda menambahkan
bidang ID user pada tabel Tuser dan sebuah bidang ID forum pada tabel Tforum. Keduanya berperan sebagai kunci
primer. Karena bidang nama user pada
tabel Tuser dan bidang nama pada tabel Tforum harus unik untuk tiap rekaman dan harus selalu memiliki
sebuah nilai, Anda bisa saja menjadikannya sebagai kunci primer untuk tabelnya
masing-masing. Namun, ini beberti nilai-nilainya tidak akan berubah dan
database akan lebih lambat, karena menggunakan kunci berbasis teks bukan
berbasis numerik.
Gambar 6.4 Setiap tabel
memerlukan kunci primer sendiri.
4. Ciptakan kunci-kunci luar yang diperlukan dan indikasikan relasinya
(Gambar 6.5). Langkah terakhir dalam menaati 2NF adalah memanfaatkan
kunci-kunci luar untuk menghubungkan tabel-tabel yang berelasi. Ingat bahwa
satu kunci primer pada sebuah tabel akan menjadi kunci luar pada tabel yang
lain.
Dengan contoh ini, ID user dari tabel Tuser dihubungkan ke kolom ID user pada tabel Tpesan. Oleh karena itu, Tuser
memiliki relasi satu-ke-banyak dengan Tpesan
(karena setiap user dapat mengirim beberapa pesan tetap setiap pesan hanya
dapat dikirim oleh satu user).
Selain itu, kolom ID forum juga dihubungkan, menciptakan
sebuah relasi satu-ke-banyak antara Tpesan
dan Tforum (setiap pesan hanya berada
di dalam satu forum tetapi setiap forum bisa memiliki beberapa pesan). Tidak
ada relasi antara Tuser dan Tforum.
Gambar 6.5 Untuk
merelasikan tiga tabel, dua kunci luar ditambahkan pada tabel Tpesan, dan tiap
kunci tiap kunci itu merepresentasikan salah satu dari dua tabel lain.
6.7
Normal Form Ketiga (3NF)
Sebuah database
menaati 3NF jika ia telah menaati 2NF dan setiap kolom tak-kunci independen
secara mutual. Jika Anda mengikuti proses normalisasi sampai titik ini, Anda
tidak akan mengalami kesulitan memahami 3NF. Anda akan mengetahui bahwa
database akan melanggar 3NF jika pengubahan nilai pada suatu kolom mensyaratkan
pengubahan nilai pada kolom lain. Pada database forum (lihat Gambar 6.5), ia tidak mengalami masalah apapun dengan
3NF.
Ambil sebuah
contoh, misalnya, terdapat sebuah tabel yang menyimpan informasi tentang bisnis
dari beberapa klien: nama depan, nama belakang, nomor telepon, alamat jalan,
kota, propinsi, kode pos, dan seterusnya. Tabel semacam itu tidak akan menaati
3NF karena banyak kolom yang saling bergantung: alamat jalan sangat bergantung
dengan kota; kota bergantung pada propinsi; dan kode pos bergantung pada
negara, dan seterusnya. Untuk menormalisasi database ini, Anda akan menciptakan
sebuah tabel untuk propinsi, satu tabel untuk kota (dengan satu kunci luar yang
menghubungkan ke tabel propinsi), dan satu tabel lain untuk kode pos. Ketiga
tabel ini akan dihubungkan balik ke tabel Tklien.
Membuat database yang menaati 3NF
1. Identifikasi semua bidang pada tiap tabel yang saling bergantung.
Seperti dijelaskan sebelumnya, apa yang Anda cari adalah kolom-kolom yang sangat
bergantung satu sama lain (seperti kota dan propinsi). Pada database forum, ini tidak menjadi masalah. Lihat
tabel Tpesan, setiap judul pesan spesifik untuk sebuah ID pesan, setiap tubuh
pesan spesifik untuk sebuah ID pesan, setiap tubuh pesan spesifik untuk ID
pesan, dan seterusnya.
2. Ciptakan tabel-tabel baru yang diperlukan. Jika Anda menemukan ada
masalah pada langkah 1, seperti kota dan propinsi pada contoh klien, Anda perlu
menciptakan tabel Tkota dan tabel Tpropinsi secara terpisah.
3. Tugaskan atau ciptakan kunci-kunci primer baru. Setiap tabel harus
memiliki satu kunci primer, jadi tambahkan ID
kota dan ID propinsi pada
tabel-tabel baru.
4. Ciptakan kunci-kunci luar yang diperlukan untuk menghubungkan setiap
relasi (Gambar 6.6). Terakhir, tambahkan ID
propinsi pada tabel Tkota dan ID kota pada tabel Tklien. Ini secara efektif menghubungkan rekaman setiap klien ke
kota dan propinsi dimana ia tinggal.
Gambar 6.6 Dua tabel baru
diciptakan untuk menyimpan nilai-nilai kota dan propinsi.
6.8
Menciptakan Database
Ada tiga langkah
dalam merancang database:
1. Memeriksa ulang semua informasi yang diperlukan untuk disimpan.
2. Mengidentifikasi tipe-tipe data kolom.
3. Menamai semua elemen database.
Tabel 6.6
menunjukkan perancangan database akhir. Satu kolom telah ditambahkan pada
tabel-tabel yang ditunjukkan pada Gambar 6.5. Karena satu pesan bisa menjadi
jawaban untuk pesan lain, beberapa metode untuk mengindikasikan relasi itu
diperlukan. Solusinya adalah menambahkan kolom id_induk pada Tpesan.
Jika sebuah pesan merupakan jawaban untuk pesan lain, maka nilai id_induk akan menjadi id_pesan dari pesan semula (jadi id_pesan berperan sebagai kunci luar
pada tabel yang sama). Jika sebuah pesan memiliki id_induk bernilai 0, maka ia merupakan pesan baru, bukan jawaban
dari pesan lain.
TABEL 6.6 Rancangan akhir
dari database forum. Perhatikan
bahwa setiap kolom integer bertipe data UNSIGNED, dan tiga kolom kunci primer
juga diberikan properti AUTO_INCREMENT, dan setiap kolom diberikan NOT NULL.
Database forum
|
||
Nama Kolom
|
Tabel
|
Tipe Kolom
|
id_forum
|
Tforum
|
TINYINT
|
nama
|
Tforum
|
VARCHAR(60)
|
id_pesan
|
Tpesan
|
INT
|
id_forum
|
Tpesan
|
TINYINT
|
id_induk
|
Tpesan
|
INT
|
id_user
|
Tpesan
|
MEDIUMINT
|
judul
|
Tpesan
|
VARCHAR(100)
|
tubuh
|
Tpesan
|
LONGTEXT
|
tanggal_masuk
|
Tpesan
|
TIMESTAMP
|
id_user
|
Tuser
|
MEDIUMINT
|
namauser
|
Tuser
|
VARCHAR(30)
|
pass
|
Tuser
|
CHAR(40)
|
nama_depan
|
Tuser
|
VARCHAR(20)
|
nama_belakang
|
Tuser
|
VARCHAR(40)
|
email
|
Tuser
|
VARCHAR(80)
|
Menciptakan database
1. Ciptakan database forum
(Gambar 6.7).
CREATE DATABASE forum;
USE forum;
Gambar 6.7 Langkah
pertama untuk menciptakan dan memilih database.
2. Ciptakan tabel Tforum (Gambar
6.8).
CREATE TABLE Tforum (
id_forum TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
nama VARCHAR(60) NOT NULL,
PRIMARY KEY (id_forum)
);
Gambar 6.8 Menciptakan
tabel pertama.
3. Ciptakan tabel Tpesan (Gambar
6.9).
CREATE TABLE Tpesan (
id_pesan INT UNSIGNED NOT NULL
AUTO_INCREMENT,
id_forum TINYINT UNSIGNED NOT NULL,
id_induk INT UNSIGNED NOT NULL,
id_user MEDIUMINT UNSIGNED NOT NULL,
judul VARCHAR(100) NOT NULL,
tubuh LONGTEXT NOT NULL,
tanggal_masuk TIMESTAMP NOT NULL,
PRIMARY KEY (id_pesan)
);
Gambar 6.9 Menciptakan
tabel kedua.
4. Menciptakan tabel Tuser
(Gambar 6.10).
CREATE TABLE Tuser (
id_user MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
namauser VARCHAR(30) NOT NULL,
pass CHAR(40) NOT NULL,
nama_depan VARCHAR(20) NOT NULL,
nama_belakang VARCHAR(40) NOT NULL,
email VARCHAR(80) NOT NULL,
PRIMARY KEY (id_user)
);
Gambar 6.10 Menciptakan
tabel ketiga.
5. Jika diinginkan, pastikan struktur database yang telah diciptakan
(Gambar 6.11).
SHOW TABLES;
SHOW COLUMNS FROM Tforum;
SHOW COLUMNS FROM Tpesan;
SHOW COLUMNS FROM Tpengguna;
Gambar 6.11 Memeriksa
struktur database atau tabel menggunakan SHOW.
6.9
Mengisi Database
1. Tambah beberapa rekaman baru pada tabel Tforum (Gambar 6.12).
INSERT INTO Tforum (nama) VALUES
(‘MySQL’), (‘PHP’), (‘Sport’),
(‘HTML’), (‘CSS’), (‘JavaScript’);
Karena tabel Tpesan sangat bergantung pada nilai-nilai yang ada pada tabel Tforum dan Tuser, kedua tabel tersebut perlu diisi lebih dahulu.
Gambar 6.12 Menambah
beberapa rekaman pada tabel Tforum.
2. Tambahkan beberapa rekaman pada tabel Tuser (Gambar 6.13).
INSERT INTO Tuser (namauser, pass,
nama_depan, nama_belakang, email)
VALUES ('pria ganteng', SHA1('passku'),
'Kristof', 'Gonggom', 'kg@contoh.com'),
('wanita lucu', SHA1('monyet'), 'Vivian',
'Patricia', 'vp@contoh.com'),
('orang keren', SHA1('keren'), 'Jusak',
'Mangindaan', 'jm@contoh.com');
Gambar 6.13 Menambah
beberapa rekaman pada tabel Tuser.
3. Tambahkan beberapa rekaman Tpesan
(Gambar 6.14).
SELECT * FROM Tforum;
SELECT id_user, namauser FROM Tuser;
INSERT INTO Tpesan (id_forum,
id_induk, id_user, judul, tubuh)
VALUES (1, 0, 1, 'Pertanyaan tentang
normalisasi.', 'Saya masih bingung tentang normalisasi. Untuk 2NF, saya...'),
(1, 0, 2, 'Perancangan Database',
'Saya menciptakan sebuah database dan memiliki masalah dengan strukturnya...'),
(1, 2, 1, 'Perancangan Database',
'Jumlah tabel pada database Anda mencakup...'),
(1, 3, 2, 'Perancangan Database',
'Oke, Terimakasih!'),
(2, 0, 3, 'Error PHP', 'Saya
menggunakan skript dari Bab 3 dan saya tidak bisa menjalankan contoh
kalkulator...');
Gambar 6.14 Database
ternormalisasi seringkali mensyaratkan Anda untuk mengetahui nilai-nilai dari
suatu tabel untuk memasukkan rekaman-rekaman pada tabel lain. Pengisian tabel Tpesan mensyaratkan Anda mengetahui
nilai-nilai kunci luar dari tabel Tuser
dan Tforum.
4. Ulangi langkah 2 dan 3 untuk mengisi database. Anda bisa menggunakan tab
SQL pada phpMyAdmin untuk mengisi data pada tiap kolom (Gambar 6.15 dan Gambar
6.16).
Gambar 6.15 Mengisi tabel Tuser menggunakan phpMyAdmin.
Gambar 6.16 Mengisi tabel Tpesan menggunakan phpMyAdmin.
6.10
Melakukan Join
Karena database
relasional memiliki struktur yang lebih kompleks, adakalanya diperlukan
statemen-statemen query untuk membaca data yang paling dibutuhkan. Sebagai
contoh, jika Anda ingin mengetahui pesan-pesan apa yang ada pada forum
JavaScript, Anda terlebih dahulu perlu mencari id_forum untuk JavaScript, dan kemudian menggunakan nomor itu untuk
membaca semua rekaman dari tabel Tpesan
yang memiliki id_forum tersebut.
Situasi sederhana ini memerlukan dua query terpisah. Dengan join, Anda dapat
melakukannya dengan satu langkah.
Join merupakan
sebuah query SQL yang menggunakan dua atau lebih tabel, dan menghasilkan sebuah
tabel virtual yang memuat hasil. Dua jenis join yaitu inner dan outer.
Inner join
menghasilkan semua rekaman dari tabel-tabel yang disebutkan ketika sebuah
kecocokan ditemukan. Sebagai contoh, untuk mencari setiap pesan pada forum JavaScript, inner join akan dituliskan
sebagai (Gambar 6.17)
SELECT * FROM Tpesan INNER JOIN Tforum
ON Tpesan.id_forum = Tforum.id_forum
WHERE
Tforum.nama = ‘JavaScript’
Gambar 6.17 Query join ini
menghasilkan setiap kolom dari kedua tabel dimana id_forum merepresentasikan forum JavaScript (6).
Operasi join ini
menyeleksi setiap kolom dari kedua tabel di bawah dua kondisi. Pertama, kolom Tforum.nama harus memiliki sebuah nilai
dari JavaScript (ini akan menghasilkan id_forum
bernilai 6). Kedua, nilai id_forum
di dalam tabel Tforum harus cocok
dengan nilai id_forum di dalam tabel
Tpesan. Karena perbandingan
ekualitas dilakukan lintas dua tabel (Tpesan.id_forum
= Tforum.id_forum), ini dikenal dengan equijoin.
Inner join dapat
pula dituliskan tanpa secara formal menggunakan INNER JOIN:
SELECT * FROM Tpesan, Tforum
WHERE Tpesan.id_forum = Tforum.id_forum
AND
Tforum.nama = 'JavaScript';
Ketika
menyeleksi dari beberapa tabel, Anda harus menggunakan sintaksis dot
(tabel.kolom) jika tabel-tabel yang dicantumkan di dalam query memiliki kolom-kolom
dengan nama sama. Ini biasa terjadi pada database relasional karena kunci
primer dari satu tabel akan memiliki nama sama yang menjadi kunci luar pada
tabel lain. Jika Anda tidak secara eksplisit mereferensi kolom, Anda akan
mendapatkan error (Gambar 6.18).
Gambar 6.18 Pada query ini,
terjadi error karena penyebutan kolom tidak dilakukan secara eksplisit.
Operasi outer
join berbeda dari inner join dimana outer join dapat menghasilkan
rekaman-rekaman yang tidak memenuhi sebuah kondisi. Ada tiga jenis outer join:
left, right, dan full. Contoh dari sebuah left join adalah
SELECT * FROM Tforum LEFT JOIN Tpesan
ON Tforum.id_forum = Tpesan.id_forum
Pertimbangan
paling penting pada left join adalah tabel mana yang dicantumkan pertama kali.
Pada contoh ini, semua rekaman pada tabel Tforum
akan dihasilkan berikut dengan informasi dari tabel Tpesan, jika kecocokan ditemukan. Jika tidak ada rekaman-rekaman
pada tabel Tpesan yang memiliki
kecocokan dengan sebuah baris pada tabel Tforum,
maka nilai-nilai NULL akan dihasilkan (Gambar 6.19).
Pada kedua inner
join dan outer join, jika kolom pada kedua tabel yang dipakai pada perbandingan
ekualitas memiliki nama sama, maka Anda dapat menyederhanakan query dengan:
SELECT * FROM Tpesan INNER JOIN Tforum
USING (id_forum)
WHERE Tforum.nama = 'JavaScript'
SELECT * FROM Tforum LEFT JOIN Tpesan
USING
(id_forum)
Gambar 6.19 Sebuah outer
join menghasilkan lebih banyak rekaman dari sebuah inner join karena semua
rekaman dari tabel pertama dihasilkan.
Menggunakan join
1. Baca nama forum dan judul pesan untuk tiap rekaman pada tabel Tpesan (Gambar 6.18).
SELECT f.nama, m.judul
FROM Tforum AS f INNER JOIN Tpesan AS
m
USING (id_forum)
ORDER BY f.nama;
Gambar 6.20 Operasi inner
join dasar yang hanya menghasilkan dua kolom nilai.
Query ini, yang memuat sebuah inner
join, akan secara efektif mengganti nilai id_forum
di dalam tabel Tpesan dengan nilai
nama terkait dari tabel Tforum untuk
tiap rekaman di dalam tabel Tpesan.
Hasil akhir yang didapatkan adalah bahwa ia menampilkan versi tekstual dari
nama forum untuk tiap judul pesan. Perhatikan bahwa Anda masih dapat
menggunakan klausa ORDER BY di dalam join.
2. Baca judul dan tanggal masuk untuk setiap pesan yang dikirim oleh user wanita lucu (Gambar 6.21).
SELECT m.judul,
DATE_FORMAT(m.tanggal_masuk, '%M %D, %Y')
AS Tanggal
FROM Tuser AS u INNER JOIN Tpesan AS
m
USING (id_user)
WHERE u.namauser = 'wanita
lucu';
Gambar 6.21 Versi yang
lebih kompleks dari sebuah inner join, menggunakan tabel Tuser dan Tpesan.
Join ini juga menggunakan dua tabel,
Tuser dan Tpesan. Kolom penghubung untuk kedua tabel adalah id_user, sehingga ia ditempatkan di
dalam klausa USING. Kondisional WHERE mengidentifikasi user yang menjadi
target, dan fungsi DATE_FORMAT() akan membantu memformat nilai tanggal_masuk.
3. Baca id pesan, judul, dan nama forum untuk setiap pesan yang dikirimkan
oleh user pria ganteng (Gambar 6.22).
SELECT m.id_pesan, m.judul,
f.nama FROM Tuser AS u INNER JOIN
Tpesan AS m
USING (id_user)
INNER JOIN Tforum AS f
USING (id_forum)
WHERE u.namauser = 'pria
ganteng';
Gambar 6.22 Sebuah inner
join lintas tiga tabel.
4. Baca nama user, judul pesan, dan nama forum untuk setiap user (Gambar
6.23).
SELECT u.namauser, m.judul, f.nama
FROM Tpengguna AS u LEFT JOIN Tpesan
AS m
USING (id_user)
LEFT JOIN Tforum AS f
USING (id_forum);
Jika Anda akan menjalankan sebuah
inner join yang serupa dengan ini, user yang tidak pernah mengirimkan pesan
tidak akan dihasilkan (Gambar 6.24). Jadi outer join diperlukan untuk
menampilkan semua user.
Gambar 6.23 Left join ini
menghasilkan, untuk setiap user, setiap judul pesan yang dikirim dan setiap
nama forum. Jika user tidak pernah mengirimkan pesan, maka judul pesan dan nama
forum akan diberi NULL.
Gambar 6.24 Inner join ini
tidak akan menghasilkan user yang tidak pernah mengirimkan pesan.
6.11
Mengelompokkan Hasil
Pada bab
terdahulu, dua klausa ORDER BY dan LIMIT, telah dikenalkan untuk memengaruhi
hasil yang didapatkan dari query. Klausa ORDER BY mendikte urutan dimana
baris-baris terseleksi akan dihasilkan; klausa LIMIT membatasi banyak dari
hasil query. Klausa berikutnya, GROUP BY, berbeda dari kedua klausa itu. Klausa
ini mengelompokkan data yang dihasilkan ke dalam blok-blok informasi yang
serupa. Sebagai contoh, untuk mengelompokkan semua pesan berdasarkan forum,
Anda akan menggunakan
SELECT
* FROM Tpesan GROUP BY id_forum
Tabel 6.7
mencantumkan sejumlah fungsi pengelompok pada MySQL.
Anda dapat
mengkombinasikan WHERE, ORDER BY, dan LIMIT dengan GROUP BY seperti ini:
SELECT kolom_apa FROM tabel
WHERE kondisi GROUP BY kolom
ORDER
BY kolom LIMIT x, y
TABEL 6.7 Fungsi-fungsi
pengelompok MySQL
Fungsi
|
Menghasilkan
|
AVG()
|
Rerata dari nilai-nilai di dalam kolom.
|
COUNT()
|
Banyak nilai di dalam sebuah kolom.
|
GROUP_CONCAT()
|
Penyambungan atas nilai-nilai pada sebuah kolom.
|
MAX()
|
Nilai terbesar di dalam sebuah kolom.
|
MIN()
|
Nilai terkecil di dalam sebuah kolom.
|
SUM()
|
Penjumlahan atas semua nilai di dalam sebuah kolom.
|
Mengelompokkan data
1. Hitung banyaknya user yang telah melakukan registrasi (Gambar 6.25).
SELECT COUNT(id_user) FROM Tuser;
COUNT() mungkin fungsi penglompok
yang paling populer. Dengan menggunakan fungsi ini, Anda dapat dengan cepat
menghitung banyak rekaman, seperti banyak rekaman di dalam tabel Tuser di sini. Perhatikan bahwa tidak
semua query yang menggunakan fungsi agregat perlu memiliki klausa GROUP BY.
Gambar 6.25 Query
pengelompok ini menghitung banyak nilai id_user
di dalam tabel Tuser.
2. Hitung berapa kali setiap user mengirimkan sebuah pesan (Gambar 6.26).
SELECT namauser,
COUNT(id_pesan) AS Jumlah
FROM Tuser LEFT JOIN Tpesan AS m
USING (id_user) GROUP BY (m.id_user);
Gambar 6.26 Query GROUP BY
menghitung berapa kali setiap user mengirimkan pesan.
3. Cari dua user teratas yang paling banyak mengirimkan pesan (Gambar
6.27).
SELECT namauser,
COUNT(id_pesan) AS Jumlah
FROM Tuser LEFT JOIN Tpesan AS m
USING (id_user) GROUP BY (m.id_user)
ORDER BY Jumlah DESC LIMIT 2;
Gambar 6.27 Sebuah klausa
ORDER BY ditambahkan untuk mengurutkan pengirim pesan terbanyak berdasarkan
jumlah pesan yang dikirimkan. Klausa LIMIT memotong hasil menjadi dua hasil
saja.
6.12
Melakukan Pencarian
Di sini Anda
akan melakukan pencarian terhadap item-item informasi dari tabel-tabel yang
ada.
1. Jalankan pencarian atas kata database (Gambar 6.28).
SELECT judul, tubuh FROM Tpesan
WHERE MATCH (tubuh, judul)
AGAINST('database');
Gambar 6.28 Sebuah
pencarian sederhana.
2. Jalankan pencarian yang juga menunjukkan relevansi (Gambar 6.29).
SELECT judul, tubuh, MATCH (tubuh,
judul) AGAINST('database') AS R
FROM Tpesan WHERE MATCH (tubuh, judul)
AGAINST('database');
Gambar 6.29 Relevansi dari
pencarian yang dilakukan. Pada kasus ini, Anda akan melihat bahwa dua rekaman
dengan kata ‘database’ pada kedua judul dan tubuh memiliki relevansi yang lebih
tinggi daripada rekaman yang memuat kata tersebut pada judul pesan.
3. Jalankan pencarian menggunakan beberapa katakunci (Gambar 6.30).
SELECT judul, tubuh FROM Tpesan
WHERE MATCH (tubuh, judul)
AGAINST('html xhtml');
Dengan query ini, kecocokan akan
didapatkan jika judul atau tubuh memuat kata yang dicari. Sembarang rekaman
yang memuat kedua rekaman akan diranking lebih tinggi.
Gambar 6.30 Dengan
pencarian, Anda dapat dengan mudah mencari pesan-pesan yang memuat beberapa
katakunci.
4. Jalankan pencarian Boolean untuk menemukan HTML, XHTML, atau (X)HTML
(Gambar 6.31)
SELECT judul, tubuh FROM Tpesan
WHERE MATCH(tubuh, judul)
AGAINST('*HTML' IN BOOLEAN MODE)\G
Gambar 6.31 Sebuah
pencarian Boolean sederhana.
6.13
Melakukan Transaksi
1. Ciptakan sebuah tabel baru, akun
(Gambar 6.32).
CREATE TABLE akun (
id INT UNSIGNED NOT NULL AUTO_INCREMENT,
nama VARCHAR(40) NOT NULL,
saldo DECIMAL(10,2) NOT NULL DEFAULT
0.0,
PRIMARY KEY (id)
) ENGINE=InnoDB;
Gambar 6.32 Sebuah tabel
baru diciptakan untuk kepentingan demonstrasi transaksi.
2. Isi tabel (Gambar 6.33).
INSERT INTO akun (nama, saldo)
VALUES ('Sarah Munaroh', 5460.23),
('David Jono', 909325.24),
('Kojo Nandi', 892.00);
Gambar 6.33 Pengisian
tabel akun.
3. Mulai sebuah transaksi dan tampilkan isi tabel saat ini (Gambar 6.34).
START TRANSACTION;
SELECT * FROM akun;
Gambar 6.34 Sebuah
transaksi dimulai dan rekaman-rekaman di dalam tabel ditampilkan.
4. Kurangi Rp.100 dari akun David Jono.
UPDATE akun
SET saldo = (saldo-100)
WHERE id=2;
5. Tambahkan Rp.100 pada akun Sarah Munaroh.
UPDATE akun
SET akun = (akun+100)
WHERE id=1;
6. Konfirmasi hasil (Gambar 6.35).
SELECT * FROM akun;
Gambar 6.35 Dua Query
UPDATE diekseksi dan hasil ditampilkan.
7. Batalkan transaksi.
ROLLBACK;
8. Konfirmasi hasil (Gambar 6.36).
SELECT * FROM akun;
Gambar 6.36 Pembuktian
pembatalan transaksi.
No comments:
Post a Comment