Tugas Basis Data Dosen : Khabib Mustofa
Diberikan sejumlah tabel untuk memodelkan sebagian dari suatu sistem akademik perguruan tinggi sebagai berikut. Matakuliah(kodemk,namamk,sks)
berisi daftar matakuliah yang
ditawarkan Dosen(nip,nama) daftar dosen pengampu matakuliah Mahasiswa(nim,nama,dosenpembimbing) daftar mahasiswa Kuliah(kodekuliah,kodemk,nip,thnakademik,semester) daftar matakuliah dan dosen pengampu. Seorang dosen bisa mengajar matakuliah yang sama untuk kelas yang berbeda pada suatu semester. semester bernilai '1' untuk ganjil atau '2' untuk genap. thnakademik dinyatakan dalam format panjang seperti '2007-2008' Peserta(nim,kodekuliah,nilai) nilai mahasiswa dalam ‘a’, ‘b’ s.d. ‘e’. Catatan :
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. Tampilkan nama mahasiswa yang telah lulus lebih dari 100 SKS
Jawab :
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 Penjelasan : i) Sebelumnya terlebih dulu kita buat tabel untuk memudahkan penampilan jumlah semua sks yang telah diselesaikan oleh mahasiswa, yaitu dengan membuat tabel dengan kolom nim, nama, kodekuliah, sks yang diambil dari tabel mahasiswa, kuliah, peserta, matakuliah. Yang kemudian di’inner join’kan.
Query : CREATE VIEW `ngulang` AS SELECT peserta.nim, mahasiswa.nama, kuliah.kodekuliah, matakuliah.sks FROM kuliah, matakuliah, peserta, mahasiswa WHERE kuliah.kodekuliah = peserta.kodekuliah AND matakuliah.kodemk = kuliah.kodemk AND peserta.nim = mahasiswa.nim Kemudian kita lakukan penjumlahan sks terhadap tabel ‘ngulang’. Query : SELECT nim, nama, sum( sks ) AS totalsks FROM ngulang GROUP BY nim, nama ORDER BY nim, nama Gambaran hasilnya seperti di bawah ini :
ii) Untuk masalah yang kedua juga dibutuhkan tabel baru untuk
memudahkan penyelesaiannya. Tabel baru ini akan menampilkan beberapa kolom, yaitu nim, nama, kodekuliah, sks, kodemk, nilai, tahunterakhir dan semester yang diambil dari tabel kuliah, matakuliah, peserta, mahasiswa. Konsep yang digunakan hampir sama dengan permasalahan di atas. Tabel ini akan digunakan seterusnya untuk menyelesaikan permasalahan berikutnya. Query : CREATE VIEW `takulang` AS SELECT peserta.nim, mahasiswa.nama, kuliah.kodekuliah, matakuliah.sks, matakuliah.kodemk, peserta.nilai, max(kuliah.thnakademik ) AS tahunterakhir, max( semester ) as semester FROM kuliah, matakuliah, peserta, mahasiswa WHERE kuliah.kodekuliah = peserta.kodekuliah AND matakuliah.kodemk = kuliah.kodemk AND peserta.nim = mahasiswa.nim GROUP BY peserta.nim, mahasiswa.nama, kuliah.kodemk order by nim Kemudian kita lakukan penjumlahan pada kolom sks untuk menghasilkan jumlah sks yang telah diselseaikan oleh mahasiswa.
Query : SELECT nim, nama, sum( sks ) as totalsks FROM takulang GROUP BY nama, nim order by nim Hasil dari query di atas adalah seperti ini :
Pada gambar di atas tidak semua data ditampilkan.
B. Tampilkan data-data yang bisa untuk melakukan perhitungan IPK (berdasar asumsi di atas) Penjelasan : Untuk melakukan perhitungan IPK kita membutuhkan tabel yang memuat kolom nim, nama, kodekuliah, sks, kodemk, nilai, thnakademik dan semester yang diambil dari tabel kuliah, matakuliah, peserta, mahasiswa. Kemudian dari tabel-tabel tersebut dilakukan join. Syarat untuk melakukan perhitungan terakhir yaitu nilai yang diambil adalah nilai terakhir. Oleh karena itu, dilakukan fungsi agregat max pada thnakademik dari tabel kuliah. Query
:
SELECT peserta.nim, mahasiswa.nama, kuliah.kodekuliah, matakuliah.sks, matakuliah.kodemk, peserta.nilai, max(kuliah.thnakademik ) AS tahunterakhir, max( semester ) as semester FROM kuliah, matakuliah, peserta, mahasiswa WHERE kuliah.kodekuliah = peserta.kodekuliah AND matakuliah.kodemk = kuliah.kodemk AND peserta.nim = mahasiswa.nim GROUP BY peserta.nim, mahasiswa.nama, kuliah.kodemk order by nim Dari query di atas dihasilkan beberapa contoh data sebagai berikut :
Pada gambar di atas tidak semua data ditampilkan.
C. Tampilkan dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak lebih dari 15 mahasiswa. Penjelasan : Dalam menyelesaikan permasalahan di atas dibutuhkan tabel view untuk memudahkan langkah-langkah pemrosesan selanjutnya. Tabel yang dibuthkan memuat kolom nip, namadosen, nim, namamahasiswa, dan kodekuliah yang diambil dari tabel mahasiswa, dosen, kuliah, peserta. Query : CREATE VIEW Dosen AS SELECT dosen.nip, dosen.nama AS namadosen, mahasiswa.nim, mahasiswa.nama AS namamahsiswa, kuliah.kodekuliah FROM mahasiswa, dosen, kuliah, peserta WHERE kuliah.kodekuliah = peserta.kodekuliah AND peserta.nim = mahasiswa.nim AND kuliah.nip = dosen.nip order by nip Kemudian dilakukan fungsi agregat count untuk mendapatkan jumlah peserta pada kelas mata kuliah yang diampu oleh dosen.
Query : SELECT count( nip ) AS pesertakuliah, nip, namadosen, kodekuliah FROM dosen1 GROUP BY nip, namadosen, kodekuliah HAVING pesertakuliah <15 Dari query di atas dihasilkan beberapa contoh data sebagai berikut :
Pada gambar di atas tidak semua data ditampilkan.
D. Bila kode matakuliah untuk Tugas Akhir adalah ‘M0012’, tampilkan banyaknya siswa yang telah lulus tugas akhir Penjelasan : Dalam menentukan banyaknya mahasiswa yang telah lulus tugas akhir dengan syarat kodemk adalah M0012 dan nilai minimal D maka dibutuhkan tabel dengan kolom nim dan nama yang diambil dari tabel view takulang yang telah dibuat pada soal (A.i) di atas. Kemudian ditambahkan syarat ‘ASCII( nilai ) <69’, maksudnya adalah nilai lebih dari E (kode ASCII ‘E’ adalah 69). Lalu dilakukan fungsi agregat count untuk mendapatkan jumlah mahasiswa yang telah lulus tugas akhir. Query : SELECT count( nim ) AS mhasiswalulus FROM (SELECT nim, nama FROM takulang WHERE kodemk = 'M0012'AND ASCII( nilai ) <69) AS tabel
E. Tampilkan daftar nomor dan nama mahasiswa beserta IP (Indeks Prestasinya) berdasar dua asumsi di atas Penjelasan : Pada permasalahan ini diminta untuk menampilkan IP yang didapat oleh mahasiswa. Dibutuhkan kolom nim, nama, dan ip. Syarat IP adalah nilai terakhir yang diambil. Kemudian untuk mendapatkan IP, digunakan fungsi agregat sum SUM( ( 69 - ASCII( nilai ) ) * sks ) / SUM( sks ). Jumlah ASCII dari kolom nilai digunakan untuk mengurangi 69 dimana akan menghasilkan bobot pada kolom nilai lalu dikalikan dengan kolom sks dan dibagi dengan jumlah sks. Query : CREATE VIEW `ip` AS SELECT nim, nama, SUM( ( 69 - ASCII( nilai ) ) * sks ) / SUM( sks ) AS ip FROM takulang GROUP BY nim, nama order by nim Beberapa contoh data yang dihasilkan dari data di atas :
F. Tampilkan nama mahasiswa yang telah lulus lebih dari 100 SKS Penjelasan :
Dari permasalahan ini diharapkan dapat menampilkan mahasiswa yang telah lulus lebih dari 100 sks. Syarat kelulusan mata kuliah adalah nilai minimal D. Kolom yang dibutuhkan adalah nim, nama, skslulus. Yang dimana data diambil dari tabel’takulang’ yang telang dibuat pada soal (A.ii). Kemudian diberi kondisi WHERE ASCII( nilai ) <69 kode ASCII untuk nilai ‘E’ adalah 69.
Query : SELECT nim, nama, sum( sks ) AS skslulus FROM takulang WHERE ASCII( nilai ) <69 GROUP BY nim, nama having skslulus>99 Tidak data yang dihasilkan karena belum ada mahasiswa yang telah lulus dari 100 sks.
