Basisdata Studi Kasus Akademik


Persoalan :

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’.

Berikut ini adalah Diagram Relasional database Akademik :

Diasumsikan dalam suatu DBMS terdapat:

– fungsi upper(string) yang akan mengembalikan string dalam huruf kapital,

– fungsi concat(s1, s2, .., sn) yang mengembalikan gabungan string s1, s2 … sn (misalnya concat(‘aku’, ‘kamu’) akan menghasilkan string ‘akukamu’).

– fungsi ASCII(char) yang akan mengembalikan kode ASCII dari karakter char. Detahui pula kode ASCII huruf ‘K’ lebih besar daripada kode ASCII huruf ‘B’.

– fungsi substring(s,n,k) yang akan menghasilkan sub string dari string s diambil sebanyak k karakter dimulai pada karakter ke n. Contoh: substring(‘hanafi’,4,3) akan menghasilkan sub string “afi”

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

A.i. Bila matakuliah yang diulang semua masuk dalam perhitungan (asumsi diabaikan): Untuk penyelesaian pada kasus ini maka Query yang dimasukan adalah

SELECT p.nim, ms.nama, sum( m.sks ) AS jumlah_sks
FROM peserta AS p, matakuliah AS m, kuliah AS k, mahasiswa AS ms
WHERE p.kodekuliah = k.kodekuliah
AND k.kodemk = m.kodemk
AND ms.nim = p.nim
GROUP BY p.nim

Penjelasan untuk Query diatas :
Pertama masukan query SELECT adalah untuk memilih kolom yang akan ditampilkan. Kolom yang ditampilkan diambil dari tabel-tabel yang berada setelah kalusa FROM. Untuk menghubungkan suatu tabel ke tabel yang lain digunakan sintaks WHERE. Didalam klausa WHERE terdapat p.kodekuliah=k.kodekuliah yang artinya tabel p yang mempunyai kolom kodekuliah di cocokan dengan tabel k yang memiliki kolom kodekuliah (tabel p dan k adalah hasil pergantian nama/alias dari tabel peserta dan kuliah. Tujuan nya agar penulisan query lebih mudah). Fungsi agregat sum dipakai untuk menghitung banyaknya sks yang diambil. Fungsi agregat selain sum adalah max,min,avg,dan count. Fungsi agregat biasanya diikuti dengan query GROUP BY setelah klausa where.

A.ii. Bila matakuliah yang diulang hanya yang sesuai dengan asumsi matakuliah yang ditawarkan pada semester ganjil atau genap saja ,dan matakuliah terakhir yang masuk dalam perhitungan IP, maka query diatas harus dijadikan sebagai tabel temporer/inline view. Tabel temporer digunakan bila terdapat permasalahan yang tidak dapat diselesaikan hanya dengan satu langkah penulisan query. Tabel temporer biasanya diletakan setelah sintaks FROM pada main query, sehingga dapat ditulis sebagaimana query dibawah ini :

SELECT nim, nama, sum( sks ) AS Jumlah_Sks
FROM (
SELECT ms.nama AS nama, p.nim AS nim, m.kodemk, m.sks AS sks,
max( k.kodekuliah ) AS kodekuliah
FROM kuliah AS k, peserta AS p, matakuliah AS m, mahasiswa AS ms
WHERE p.kodekuliah = k.kodekuliah
AND k.kodemk = m.kodemk
AND ms.nim = p.nim
GROUP BY p.nim, k.kodemk
) AS peserta_asumsi
GROUP BY nim

Query di dalam from  (yang berwarna biru)  adalah untuk menampilkan semua mahasiswa yang pernah mengikuti matakuliah dan yang diambil adalah matakuliah terakhir bila ada yang mengulang. Untuk menampilkan hasil sesuai asumsi,maka ditambahkan max(k.kodekuliah) serta group by p.nim dan k.kodemk.

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

CREATE VIEW data_hitung_ipk AS

SELECT p.nim AS nim, ms.nama AS nama, m.kodemk, m.sks AS sks,

max( k.kodekuliah ) AS kodekuliah, k.semester AS semester, k.thnakademik,

69-ASCII(UPPER(p.nilai)) AS nilai

FROM kuliah AS k, peserta AS p, matakuliah AS m, mahasiswa AS ms

WHERE p.kodekuliah = k.kodekuliah

AND k.kodemk = m.kodemk

AND ms.nim = p.nim

GROUP BY p.nim, k.kodemk

Fungsi ASCII digunakan untuk mengubah nilai dalam huruf ke dalam bentuk bilangan. Sintaks CREATE VIEW berfungsi untuk menyimpan tabel temporer.

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

Langkah pertama untuk kasus tersebut adalah menampilkan semua peserta pada setiap kodekuliah :

SELECT p.nim AS nim,p.kodekuliah

FROM peserta AS p, kuliah

WHERE p.kodekuliah = kuliah.kodekuliah

Tabel temporer/inline view berdasarkan query di atas dipakai untuk menampilkan jumlah peserta beserta

dosen yang mengampunya pada setiap kode kuliah,:

SELECT count( kodekuliah ) AS jumlah_peserta, kodekuliah, nip

FROM (

SELECT p.nim AS nim, p.kodekuliah AS kodekuliah, k.nip AS nip

FROM peserta AS p, kuliah AS k

WHERE p.kodekuliah = k.kodekuliah

) AS mhs_kodekul

GROUP BY kodekuliah

ORDER BY `mhs_kodekul`.`nip` ASC

Menampilkan semua dosen yang pernah mengampu kelas matakuliah yang pesertanya tidak lebih dari 15 mahasiswa berdasarkan kodekuliahnya :

SELECT count( mhs_kodekul.kodekuliah ) AS jumlah_peserta,

mhs_kodekul.kodekuliah,dosen.nama as nama_dosen, mhs_kodekul.nip

FROM (

SELECT p.nim AS nim, p.kodekuliah AS kodekuliah, k.nip AS nip

FROM peserta AS p, kuliah AS k

WHERE p.kodekuliah = k.kodekuliah

) AS mhs_kodekul,dosen

where dosen.nip=mhs_kodekul.nip

GROUP BY kodekuliah

HAVING jumlah_peserta<=15

Tabel temporer/inline view dipakai untuk menampilkan jumlah peserta beserta dosen yang mengampunya pada setiap kode kuliah.

Kalau ingin menampilkan identitas dosen nya saja,tinggal tambahkan sintak DISTINCT yang diikuti dengan nama_dosen dan nip di main query nya.

SELECT DISTINCT nama_dosen,nip

FROM

(SELECT count( mhs_kodekul.kodekuliah ) AS jumlah_peserta,

mhs_kodekul.kodekuliah,

dosen.nama AS nama_dosen,

mhs_kodekul.nip

FROM (

SELECT p.nim AS nim, p.kodekuliah AS kodekuliah, k.nip AS nip

FROM peserta AS p, kuliah AS k

WHERE p.kodekuliah = k.kodekuliah) AS mhs_kodekul, dosen

WHERE dosen.nip = mhs_kodekul.nip

GROUP BY kodekuliah, nip

HAVING jumlah_peserta <=15

) AS kelas_kurang

ORDER BY `kelas_kurang`.`nama_dosen` ASC

D.Tampilkan mahasiswa yang lulus 100 SKS :

SELECT nim, nama, sum( sks ) AS jumlah_sks

FROM

(SELECT ms.nama AS nama, p.nim AS nim, m.kodemk, m.sks AS sks,

max( k.kodekuliah ) AS kodekuliah

FROM kuliah AS k, peserta AS p, matakuliah AS m, mahasiswa AS ms

WHERE p.kodekuliah = k.kodekuliah

AND k.kodemk = m.kodemk

AND ms.nim = p.nim

AND p.nilai < ‘E’

GROUP BY p.nim, k.kodemk

) AS peserta_asumsi

GROUP BY nim

HAVING jumlah_sks >100

Sintak query nya sama seperti nomor A.ii. , hanya ditambahkan p.nilai<‘E’ pada klausa where di tabel temporer peserta_asumsi. p.nilai < ‘E’ berarti keadaan dimana mahasiswa yang lulus nilai nya kurang dari ‘E’ (ASCII dalam konteks ini berlaku). Maka kriteria mahasiswa yang lulus adalah ‘D’,’C’,’B’,’A’. Kemudian ditambhakan pula query jumlah_sks > 100 pada klausa having di main query.

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

Langkah Pertama, mahasiswa yang lulus Tugas Akhir ditampilkan dengan menggunakan QUERY berikut :

SELECT p.nim, ms.nama, k.kodemk

FROM peserta AS p, mahasiswa AS ms, kuliah AS k

WHERE

ms.nim = p.nim

AND p.kodekuliah = k.kodekuliah

AND k.kodemk = ‘M0012’

AND p.nilai < ‘E’

Langkah Kedua, banyak nya mahasiswa yang lulus Tugas akhir dihitung dengan menggunakan fungsi agregat COUNT di main query. Sedangkan query pada langkah pertama digunakan sebagai inline view :

SELECT COUNT(nim) as jumlah_lulus_TA from

(SELECT p.nim as nim, ms.nama, k.kodemk

FROM peserta AS p, mahasiswa AS ms, kuliah AS k

WHERE

ms.nim = p.nim

AND p.kodekuliah = k.kodekuliah

AND k.kodemk = ‘M0012’

AND p.nilai < ‘E’)

as mhs_lulus_TA

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

SELECT nim, nama, semester, thnakademik,

sum( sks * nilai ) / sum( sks ) AS IP

FROM data_hitung_ipk

GROUP BY thnakademik, semester, nim

Untuk query diatas,, penghitungan IP diambil dari query no.B yang telah disimpan sebagai view dengan nama

data_hitung_ipk.

G. 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 :

SELECT p.nim AS nim, ms.nama AS nama, m.kodemk, m.sks AS sks,

k.kodekuliah AS kodekuliah, k.semester AS semester, k.thnakademik,

max( 69 – ASCII( UPPER( p.nilai ) ) ) AS nilai

FROM kuliah AS k, peserta AS p, matakuliah AS m, mahasiswa AS ms

WHERE p.kodekuliah = k.kodekuliah

AND k.kodemk = m.kodemk

AND ms.nim = p.nim

GROUP BY p.nim,k.kodemk

Untuk merubah asumsi, dilakukan perubahan dari query no. B, yaitu max( 69 – ASCII( UPPER( p.nilai ) ) ) AS nilai. Dan max( k.kodekuliah ) pada no.B diganti menjadi k.kodekuliah. Kemudian, untuk menghitung IP ,query tersebut dijadikan tabel temporer (teks yang berwarna biru) seperti dibawah ini:

SELECT nim, nama,semester, thnakademik, sum( sks * nilai ) / sum( sks ) AS IP

FROM

(SELECT p.nim AS nim, ms.nama AS nama, m.kodemk, m.sks AS sks,

k.kodekuliah AS kodekuliah, k.semester AS semester, k.thnakademik,

max( 69 – ASCII( UPPER( p.nilai ) ) ) AS nilai

FROM kuliah AS k, peserta AS p, matakuliah AS m, mahasiswa AS ms

WHERE p.kodekuliah = k.kodekuliah

AND k.kodemk = m.kodemk

AND ms.nim = p.nim

GROUP BY p.nim, k.kodemk

) AS max_nilai

GROUP BY thnakademik, semester, nim

ORDER BY `max_nilai`.`nim` ASC

Masih banyak contoh kasus yang lain. Untuk lebih lengkapnya silahkan download file dibawah ini, telah dilengkapi file SQL :

Studi Kasus Basisdata Akademik

Semoga Bermanfaat…😀

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s