TUGAS 1

Soal

Buatlah tutorial melakukan Extract dan Transform dari data .CSV ke dalam tabel operasional dalam bentuk ERD.

Tutorial boleh dalam bentuk Video Youtube atau laporan makalah atau artikel Blog.

Materi tutorial silakan gunakan file yang disertakan.

Jika sudah selesai, segera kumpulkan disini.

File Attachment

1. pasar_2014.csv
2. pasar_data.sql

Jawaban

Pada tulisan ini saya menggunakan aplikasi manajemen database berbasis GUI yang bernama MySQL Workbench pada sistem operasi Arch Linux.

Step 1: Membuat Connection

Apabila belum terdapat connection, kita dapat terlebih dahulu membuatnya dengan menekan icon (+) seperti yang ada pada Gambar di bawah.

Nanti akan terbuka window baru, seperti gambar di bawah.

Isi pada bagian text input berlabel Connection Name : dengan nama koneksi yang kalian inginkan. Sebagai contoh, saya mengisikan localhost 3306.

Lakukan tes koneksi dengan mengklik tombol Test Connection seperti yang ada pada gambar di bawah.

Apabila berhasil, akan memunculkan window seperti gambar di bawah ini.

Pilih OK, maka akan terbuat satu buah MySQL Connection yang bernama localhost 3306.

Pada sistem operasi Arch Linux, akan terdapat window yang berisi pemberitahuan bahwa versi server yang sedang digunakan adalah versi 10, sedangkan yang didukung oleh MySQL Workbench adalah veri 5.1, 5.5, 5.6, 5.7. Kita dapat mengabaikan pesan ini dan memilih Continue Anyway.

Akan terbuka sebuah tab baru dengan tampilan seperti di bawah.

Step 2: Membuat Schema

Selanjutnya, kita akan mebuat database schema, dengan cara melakukan klik kanan pada area kosong di sisi sidebar sebelah kiri pada tab Schemas.

Kemudian, pada area tengah, akan terbuka tab baru seperti di bawah.

Isi text input Name : sesuai yang diinginkan.

Apabila telah diberikan nama, pilih tombol Apply, maka akan terbuka window baru seperti di bawah.

Pilih Apply > Close.

Akan terbuat database scheme dengan struktur seperti gambar di bawah.

Step 3: Membuat Tabel-tabel

Selanjutnya kita akan memasukkan SQL Script untuk membuat tabel-tabel secara otomatis.

Pilih File > Open SQL Script…

Cari file pasar_data.sql yang telah disertakan bersama tugas. Apabila belum ada, bisa mengunduhnya terlebih dahulu di sini.

Apabila sudah, nanti akan terbuka tab baru di area tengah, seperti pada gambar di bawah.

Selanjutnya, kita akan mengeksekusi SQL Script yang sudah kita masukkan.

Tapi sebelunya, kita perlu untuk membuat database schema yang kita buat sebelumnya, untuk dijadikan default schema.

Caranya, pada sidebar kiri, pada bagian tab Schemas klik kanan pada nama schema, kemudian pilih Set as Default Schema, seperti contoh di bawah.

Kemudian, eksekusi SQL script dengan melakukan klik pada tombol bergambar petir. Seperti gambar di bawah.

Perhatikan pada bagian bawah, terdapat Output Panel, akan terdapat pesan seperti di bawah ini.

Apabila, telah berhenti memberikan output, sekarang kita pergi ke sidebar sebelah kiri, dan lakukan refresh agar menampilkan update terbaru dari database scheme.

Caranya dengan klik kanan pada area kosong dan pilih Refresh All.

Hasilnya akan seperti gambar di bawah.

Step 4: Ekstrak dan Transform Data

Terdapat 5 buah tabel:

1. detil_komoditas
2. komoditas
3. pasar
4. satuan
5. transaksi

Kita tidak dapat memasukkan data hasil ekstrak dan transform secara acak. Kita perlu membaca relationship tables (relasi antar tabel). Untuk itu kita memerlukan ERD (Entity Relationship Diagram).

4.1 Entity Relationship Diagram

Caranya, pergi ke menu Database > Reverse Engineer….

Kemudian, masukkan MySQL Connection yang telah kita buat pada Step 1, ke dalam Stored connection. Pada contoh saya localhost 3306.

Selanjutnya pilih Next.

Pilih Next.

Pilih Database Scheme yang kita buat.

Pilih Next.

Pilih Next.

Pilih Execute > Next > Close.

Nanti akan terbuka tab EER Diagram.

Dari pembacaan relasi antar tabel di atas, dapat kita urutkan tabel mana yang akan kita kerjakan terlebih dahulu.

1. Tabel pasar
2. Tabel komoditas
3. Tabel satuan
4. Tabel detil_komoditas
5. Tabel transaksi

4.2 Mengekstrak CSV

Kita akan melakukan ekstraksi file pasar_2014.csv ke dalam bentuk struktur tabel.

Caranya, kembali ke tab localhost 3306 (di bagian atas sebelah kiri, disebelah icon Home). Kemudian pada sidebar kiri, pada database schema yang kita buat, klik kanan dan pilih Table Data Import Wizard.

Nanti akan terbuka window baru seperti di bawah.

Cari file pasar_2014.csv yang telah disediakan bersama soal. Atau kalian dapat mengunduhnya di sini.

Kalo sudah, pilih Next >.

Berikan nama tabel dari file .csv yang ingin kita ekstraksi.

Pilih Next >.

Periksa bagian Columns :, apakah sudah sesuai dengan kategori dari masing-masing kolom / fields.

Pilih Next >.

Pilih Next >.

Nah, kalo sudah menampilkan seperti di bawah, proses import data artinya telah sukses.

Pilih Next >.

Selanjutnya periksa banyaknya data yang telah berhasil diimport, harus sesuai dengan banyaknya data yang ada pada file pasar_2014.csv, yaitu sebanyak 18.940 records.

Pilih Finish.

Kemudian, kembali ke sidebar sebelah kiri. Belum terdapat tabel data yang baru saja kita buat dari hasil ekstrak file pasar_2014.csv. Kita perlu melakukan Refresh All pada database schema. Setelah melakukan refresh, akan muncul tabel data seperti gambar di bawah.

Kita akan melihat hasil ekstrak dari file .csv yang sudah kita import. Dengan cara melakukan klik pada icon di samping.

Akan memunculkan hasil seperti di bawah.

4.3 Transform Hasil Exstrak

Selanjutnya kita akan melakukan transformasi dari tabel data yang seelunya kita ekstrak dari file pasar_2014.csv.

Untuk melakukan transform data, kita berpindah saja ke tab Query 1. Di sini kita akan mengetikkan perintah-perintah SQL untuk melakukan transformasi tabel data.

Selanjutnya kita akan melakukan transformasi dari tabel data berurut sesuai dengan hasil pembacaan relasi tabel ERD.

4.3.1 Tabel Pasar

Kita akan mentransformasi fields namapasar dari tabel data, dengan queries seperti di bawah ini.

1SELECT @nomor:=@nomor+1 as id, namapasar
2FROM data,(SELECT @nomor:=0)r
3GROUP BY namapasar;

Kemudian Excute dengan menekan tombol bergambar petir.

Akan menghasilkan tabel hasil transformasi dari tabel data seperti gambar di bawah.

Kemudian, export ke dalam format .csv, dengan menekan tombol Export.

berikan nama, misal data_pasar.csv, karena kita akan memasukkan ke dalam tabel pasar.

Buka tabel pasar, atau view tabel pasar. Klik tombol Import untuk mengambil file data_pasar.csv yang tadi sudah kita export.

Akan memunculkan hasil seperti gambar di bawah.

Kita perlu menghapus baris pertama karena bukan berisi record yang kita perlukan.

Dan akan menghasilkan isi tabel seperti gambar di bawah.

Apabila seperti di atas, artinya records data yang kita perlukan sudah tepat.

Selanjutnya kita perlu melakukan Apply. Klik tombol Apply pada pojok kanan bawah.

Akan terbuka window seperti di bawah yang berisi queries untuk memasukkan data ke dalam tabel data.

Pilih Apply.

Apabila berhasil dan tidak ada error, pilih Close. Data yang kita import tadi akan masuk ke dalam tabel pasar.

Apabila gagal, maka kita perlu meninjau kembali data yang kita dapat dari hasil queries yang kita buat saat melakukan transformasi dari tabel data, apakah sudah sesuai dengan relasi dari tabel yang kita tuju.

PERHATIAN!

Untuk tabel selanjutnya, proses Export dan Import sama seperti tabel pasar, sehingga tidak saya tuliskan kembali.

Selanjutnya saya hanya akan menuliskan query syntax nya saja untuk melakukan transformasi tabel data ke bentuk yang diperlukan oleh tabel tujuan.

4.3.2 Tabel Komoditas

Kita akan mentransformasi fields komoditas dari tabel data, dengan queries seperti di bawah ini.

1SELECT @nomor:=@nomor+1 as id, komoditas
2FROM data,(SELECT @nomor:=0)r
3GROUP BY komoditas;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel komoditas.

4.3.3 Tabel Satuan

Kita akan mentransformasi fields satuan dari tabel data, dengan queries seperti di bawah ini.

1SELECT @nomor:=@nomor+1 as id, satuan
2FROM data,(SELECT @nomor:=0)r
3GROUP BY satuan;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel satuan.

4.3.4 Tabel Detil Komoditas

Kita akan mentransformasi fields detilkomoditas dari tabel data yang berelasi dengan tabel komoditas, dan satuan dengan queries seperti di bawah ini.

1SELECT
2@nomor:=@nomor+1 as id,
3data.detilkomoditas,
4komoditas.id_komoditas,
5satuan.id_satuan
6
7FROM
8data,
9komoditas,
10satuan
11
12WHERE
13data.komoditas=komoditas.nama_komoditas
14AND
15data.satuan=satuan.nama_satuan;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel detil_komoditas.

4.3.5 Tabel Transaksi

Kita akan mentransformasi fields tanggal, harga, jumlahterjual dari tabel data yang berelasi dengan tabel detil_komoditas dan tabel pasar dengan queries seperti di bawah ini.

1SELECT
2detil_komoditas.id_detil_komoditas,
3pasar.id_pasar,
4data.tanggal,
5data.harga,
6data.jumlahterjual
7
8FROM
9data,
10detil_komoditas,
11pasar
12
13WHERE
14data.detilkomoditas=detil_komoditas.nama_detil_komoditas
15AND
16data.namapasar=pasar.nama_pasar;

Kemudian Excute dengan menekan tombol bergambar petir.

Proses Export dan Import sama seperti tabel pasar, hanya saja proses import dilakukan di tabel transaksi.

Maka proses ekstrak dan transformasi dari file pasar_2014.csv ke dalam tabel operasional ERD telah selesai.