Latihan Basis Data

Pada posting pertama ini, saya akan menjelaskan tentang query-query pada MySQL. Untuk memudahkan

Diberikan sejumlah tabel untuk memodelkan sebagian dari suatu sistem akademik perguruan tinggi sebagai berikut.

1. Matakuliah(kodemk,namamk,sks)

2. Dosen(nip,nama)

3. Mahasiswa(nim,nama,dosen pembimbing)

4. Kuliah(kodekuliah,kodemk,nip,thnakademik,semester)

5. Peserta(nim,kodekuliah,nilai) nilai mahasiswa dalam ‘a’, ‘b’ s.d. ‘e’.

Asumsi

1. matakuliah hanya ditawarkan sekali dalam setahun, yaitu pada

semester 1 (ganjil) saja atau semester 2 (genap) saja dan tidak

kedua-duanya

2. bila pernah mengulang matakuliah, nilai yang diikutkan untuk

perhitungan IP adalah nilai terakhir

3. Matakuliah dikatakan lulus bila nilai yang diperoleh minimal ‘d’

Tuliskan perintah SQL untuk permasalahan berikut:

A. Tampilkan banyaknya SKS yang telah diselesaikan oleh masing-masing mahasiswa

i. bila matakuliah yang diulang semua masuk dalam perhitungan

ii. bila matakuliah yang diulang hanya yang sesuai dengan asumsi yang masuk dalam perhitungan

B. Tampilkan data-data yang bisa untuk melakukan perhitungan IPK (berdasar asumsi di atas)

C. Tampilkan dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak lebih dari 15 mahasiswa.

D. Bila kode matakuliah untuk Tugas Akhir adalah ‘M0012’, tampilkan banyaknya siswa yang telah lulus tugas akhir

E. Tampilkan daftar nomor dan nama mahasiswa beserta IP (Indeks Prestasinya) berdasar dua asumsi di atas

F. Bila asumsi ke-2 di atas diganti bahwa nilai yang dipakai untuk penentuan IP adalah nilai yang terbaik (dari serangkaian mengulang matakuliah), tuliskan perintah SQL untuk menghitung IP

A.i. Query untuk menampilkan banyaknya SKS yang telah diselesaikan oleh masing-masing mahasiswa bila matakuliah yang diulang semua masuk dalam perhitungan

SELECT peserta.nim, sum( matakuliah.sks ) jumlahsks

FROM peserta, matakuliah, kuliah

WHERE peserta.kodekuliah = kuliah.kodekuliah

AND matakuliah.kodemk = kuliah.kodemk

GROUP BY nim

Pada query ini kita kita akan menjumlahkan seluruh sks yang telah ditempuh seorang mahasiswa. Untuk mencari sks setiap mata kuliah yang ditempuh seorang mahasiswa maka kita harus menghubungkan kodekuliah pada peserta dengan kodekuliah pada kuliah, dan kodemk pada kuliah dengan kodemk pada matakuliah. Lalu jumlah sks dicari dengan menggunakan sum(), maksud dari ‘sum( matakuliah.sks ) jumlahsks’ adalah pertama kita menjumlahkan semua kolom sks pada tabel matakuliah setelah dijumlahkan kemudian kolom tersebut kita beri nama baru yakni kolom jumlahsks. Terakhir data ditampilkan berdasarkan nim.

A.ii. Query untuk menampilkan banyaknya SKS yang telah diselesaikan oleh masing-masing mahasiswa bila matakuliah yang diulang hanya yang sesuai dengan asumsi yang masuk dalam perhitungan

CREATE VIEW ai AS SELECT peserta.nim, matakuliah.sks, kuliah.kodemk, kuliah.thnakademik

FROM peserta, matakuliah, kuliah

WHERE peserta.kodekuliah = kuliah.kodekuliah

AND matakuliah.kodemk = kuliah.kodemk

Pertama- tama kita harus membuat tabel baru yang menampilkan matakuliah yang diambil mahasiswa beserta jumlah sks beserta tahun ambil. Untuk membuat tabel baru kita menulisakan perintah ‘CREATE VIEW x AS SELECT’ x disini merupakan nama tabel baru yang telah kita buat. Setelah selesai mebuat tabel baru, kemudian menuliskan perintah berikut

SELECT nim, sum( sks )

FROM (

SELECT nim, kodemk, max( thnakademik ) , sks

FROM `ai`

GROUP BY nim, kodemk

) AS jumlahsks

GROUP BY nim

Pada query ini kita mengolah tabel ‘ai’ yaitu dengan menggunakan fungsi agregat max() dan sum(), max max adalah untuk mengurutkan dalam hal ini tahun akademik kemudian diambil tahun terakhir, sementara fungsi sum adalah untuk menjumlahkan sks.

B. Query untuk menampilkan data-data yang bisa untuk melakukan perhitungan IPK (berdasar asumsi di atas)

SELECT mahasiswa.nim, mahasiswa.nama, peserta.nilai, kuliah.kodemk, max( kuliah.thnakademik ) tahunakademik , matakuliah.sks

FROM matakuliah, mahasiswa, peserta, kuliah

WHERE peserta.nim = mahasiswa.nim AND peserta.kodekuliah = kuliah.kodekuliah AND kuliah.kodemk = matakuliah.kodemk

GROUP BY nim, kodemk

Pada query ini kita akan menampilkan kolom-kolom yang dapat dipergunakan untuk menghitung ipk, yakni kolom nim, nama nilai, kodemk tahun ambil, sks.

Nb. karena tabel ini akan digunakan untuk perhitungan IPK pada soal E, maka untuk mempermudah lebih kita akan membuat tabel baru dari data-data diatas yakni dengan query

CREATE VIEW dataipk AS ()

  1. Query untuk menampilkan dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak lebih dari 15 mahasiswa.

CREATE VIEW c AS SELECT dosen.nama, dosen.nip, kuliah.kodekuliah, count( peserta.nim ) jml
FROM dosen, peserta, kuliah
WHERE peserta.kodekuliah = kuliah.kodekuliah
AND kuliah.nip = dosen.nip
GROUP BY dosen.nama, dosen.nip, kuliah.kodekuliah

Pertama-tama kita akan membuat tabel baru, yakni tabel yang menampilkan nama dosen beserta nipnya kodekuliah dan jumlah peserta yang mengambil. Kemudian kita menuliskan query,

SELECT nama, jml
FROM c
WHERE jml <=15
GROUP BY nama

Kemudian di query ini kita tinggal mengolah tabel baru c yaitu untuk melihat nama-nam dosen yang pernah mengampu kelas yang pesertanya kurang dari 15.

D. Query untuk menampilkan banyaknya siswa yang telah lulus tugas akhir (M0012)

SELECT count( nim ) jumlahsiswa
FROM peserta, kuliah
WHERE kuliah.kodekuliah = peserta.kodekuliah
AND kodemk = ‘M0012’ AND nilai <= ‘d’

Pada query kali ini kita akan mengambil jumlah mahasiswa yang telah lulus dari tugas akhir (M0012) dan ketentuan untuk lulus adalah minimal mendapatkan nilai D (asumsi ketiga). Dalam menentukan lulus atau tidak kita menggunakan perintah nilai <= ‘d’ hal ini disebabkan karena yang dilihat adalah nilai ASCII , dalam ASCII nilai A lebih kecil daripada nilai ASCII B, sehingga nilai asci C lebih kecil daripada D.

E. Query untuk menampilkan daftar nomor dan nama mahasiswa beserta IP

SELECT nama, nim, (sum(sks*( (69 – ascii( upper( nilai )))))) / sum( sks )

FROM `dataipk`

GROUP BY nim

Pada query ini kita akan mengolah tabel baru ‘dataipk’ yang telah dibuat pada soal B. Perhitungan IPK adalah. Pertama yang harus kita lakukan adalah mengubah nilai yang semula berbentuk A,B,C,D,E dirubah kedalam bentuk ASCII, setelah itu 69 dikurangi ASCII nilai, kenapa harus 69 karena untuk memperoleh konveersi A= 4, B=3, C=2, D=1, E=0 (sesuai aturan perhitungan IPK).

Kemudian setelah dirubah menjadi angka kemudian dikali dengan jumlah sks, setelah dikali kemudian dijumlahkan dengan masing-masing nilai kodemk, baru setelah itu kita bagi dengan jumlah sks yang telah diambil.

F. Query untuk menampilkan IPK bila asumsi ke-2 di atas diganti bahwa nilai yang dipakai untuk penentuan IP adalah nilai yang terbaik.

Sebelum kita memulai perhitungan maka sebaiknya kita membuat tabel baru berdasarkan soal diatas

CREATE VIEW dataipk2 AS SELECT mahasiswa.nim, mahasiswa.nama, min( upper( peserta.nilai ) ) AS nilai, kuliah.kodemk, matakuliah.sks
FROM matakuliah, mahasiswa, peserta, kuliah
WHERE peserta.nim = mahasiswa.nim AND peserta.kodekuliah = kuliah.kodekuliah AND kuliah.kodemk = matakuliah.kodemk

GROUP BY nim, kodemk

Pada query ini kita akan menampilkan kolom-kolom nim, nama, nilai, kodemk,sks. Pada tabel ini kita tidak menampilkan tahun ambil, karena hal tersebut tidak dibutuhkan lagi

SELECT nama, nim, (sum(sks*( (69 – ascii( upper( nilai )))))) / sum( sks )

FROM `dataipk2`

GROUP BY nim

Untuk perhitungan IPK kita menggunakan query yang sama persis dengan soal E, hanya diubah dari tabel dataipk menjadi dataipk2.

Comments (3)

yuliApril 15th, 2010 at 14:12

dokumentasi kuliah ya?
salam kenal ya..

zulkanApril 16th, 2010 at 13:37

thx bang, saya contek ya, mau gw tumpuk hari ini klo bisa

feryApril 20th, 2010 at 19:39

hello to, 3 Responses to “TUGAS BASIS DATA”.

Leave a comment

Your comment