TUGAS MODUL 6

TUGAS 2 MODUL 6

Sebuah database universitas memiliki 3 buah tabel sebagai berikut:

Buat database universitas

Melihat database

Buat Table pada Database Universitas

Table Instruktur

Table Instruktur CREATE TABLE instruktur ( nip INT NOT NULL, namains VARCHAR(25), jurusan VARCHAR(25), asalkota VARCHAR(25), PRIMARY KEY (nip));         

Buat table instruktur

Table matakuliah

            CREATE TABLE matakuliah ( nomk VARCHAR(6) NOT NULL, namamk VARCHAR(30), sks INT, PRIMARY KEY (nomk));

Membuat table matakuliah

Table kuliah

CREATE TABLE kuliah ( nip INT, nomk VARCHAR(6), ruangan INT, jmlmhs INT,

FOREIGN KEY (nip) REFERENCES instruktur(nip), FOREIGN KEY (nomk) REFERENCES matakuliah(nomk));

Membuat table kuliah

1.      Tuliskan query untuk membuat ketiga tabel tersebut. Tipe data menyesuaikan dengan nilai setiap  kolom, seperti contoh data sebagai berikut:

Tabel instruktur

INSERT INTO instruktur VALUES

(1, 'Steve Wozniak', 'Ilmu Komputer', 'Bantul'),

(2, 'Steve Jobs', 'Seni Rupa', 'Solo'),

(3, 'James Gosling', 'Ilmu Komputer', 'Klaten'),

(4, 'Bill Gates', 'Ilmu Komputer', 'Magelang');

Table Matakuliah

INSERT INTO matakuliah VALUES

('KOM101', 'Algoritma dan Pemrograman', 3),

('KOM102', 'Basis Data', 3),

('SR101', 'Desain Elementer', 3),

('KOM201', 'Pemrograman Berorientasi Objek', 3);

Table Kuliah

INSERT INTO kuliah VALUES

(1, 'KOM101', 101, 50),

(1, 'KOM102', 102, 35),

(2, 'SR101', 101, 45),

(3, 'KOM201', 101, 55);

2.      Tuliskan  query   untuk   mendapatkan   data-data   di   bawah   ini.   Tambahkan   data   pada   tabel   sesuai dengan kebutuhan.

a.      Instruktur-instruktur jurusan ‘Ilmu Komputer’

SELECT * FROM instruktur WHERE jurusan='Ilmu Komputer';

b.      Nomor mata kuliah yang pesertanya lebih dari 40 orang

SELECT nomk FROM kuliah WHERE jmlmhs>40;

c.       Nomor dan mata kuliah yang pesertanya lebih dari 40 orang

SELECT B.nomk, B.namamk FROM kuliah A JOIN matakuliah B ON A.nomk = B.nomk WHERE A.jmlmhs>40;

d.      nip instruktur yang mengampu mata kuliah dengan nomor ‘KOM102′

SELECT nip FROM kuliah WHERE nomk='KOM102';

e.       nip instruktur yang mengampu mata kuliah ‘Basis Data’

SELECT A.nip FROM kuliah A JOIN matakuliah B ON A.nomk = B.nomk WHERE B.namamk='Basis Data';

f.       nip dan nama instruktur yang mengampu mata kuliah ‘Basis Data’

SELECT A.nip, C.nama FROM kuliah A JOIN matakuliah B ON A.nomk = B.nomk  JOIN instruktur C ON A.nip = C.nip WHERE B.namamk='Basis Data';

g.      Nama mata kuliah dan ruangan yang diampu oleh ‘Steve Jobs’

SELECT namamk, ruangan FROM kuliah JOIN matakuliah ON kuliah.nomk = matakuliah.nomk JOIN instruktur ON kuliah.nip = instruktur.nip WHERE namains='Steve Jobs';

h.      Jumlah total mahasiswa yang diampu oleh ‘Steve Wozniak’

SELECT SUM(jmlmhs) AS jmlmhs FROM kuliah JOIN instruktur ON kuliah.nip = instruktur.nip WHERE namains='Steve Jobs';

i.        Nomor dan nama instruktur yang mengampu mahasiswa terbanyak

SELECT instruktur.nip, instruktur.namains FROM kuliah JOIN instruktur ON kuliah.nip = instruktur.nip WHERE kuliah.jmlmhs=(SELECT MAX(jmlmhs) FROM kuliah);

j.        Nomor dan nama instruktur yang belum mengampu mata kuliah apapun

SELECT B.nip, B.namains FROM instruktur B LEFT JOIN kuliah A ON A.nip= B.nip WHERE A.nip IS NULL;

3.      Buatlah view  untuk mendapatkan data berikut ini:

a.      Nomor dan nama instruktur yang belum mengampu mata kuliah apapun

CREATE VIEW view3 AS

SELECT B.nip, B.namains FROM instruktur B LEFT JOIN kuliah A ON A.nip= B.nip WHERE A.nip IS NULL;

Query membuat view view3

SELECT * FROM view3;

b.      Jumlah mata kuliah yang diampu oleh setiap instruktur

CREATE ALGORITHM = TEMPTABLE VIEW view4(namains, jumlah_matkul) AS SELECT B.namains, count(A.nip) FROM kuliah A INNER JOIN instruktur B ON A.nip = B.nip GROUP BY A.nip;

Membuat view view4

SELECT * FROM view4;

4.      Buatlah   Trigger   untuk   pencatatan   perubahan   ruangan   untuk   sebuah   mata   kuliah.   Catatan  perubahan disimpan dalam tabel berikut:

Table roomChanges

CREATE TABLE roomChanges(user_id varchar(15),deskripsi varchar(100));

DESC roomChanges;

DELIMITER //

CREATE TRIGGER log_roomChanges AFTER UPDATE

ON kuliah

FOR EACH ROW

BEGIN

INSERT INTO roomChanges

VALUES(user(), CONCAT('Merubah ruangan',NEW.nomk, 'dari ruang ',OLD.ruangan,' ke ruang ',NEW.ruangan));

END //

DELIMITER ;

Buat trigger log_roomChanges pada table kuliah

UPDATE kuliah SET ruangan=102 WHERE nomk='KOM101';

Menjalankan perintah update pada table kuliah

SELECT * FROM roomChanges;

Melihat table pada roomChanges

5.      Buatlah fungsi atau prosedur sesuai kasus berikut ini:

a.      Fungsi   untuk   menampilkan   jumlah   kuliah   yang   diadakan   di   sebuah   ruangan   (nama   ruangan  dimasukkan sebagai input)

DELIMITER //

CREATE FUNCTION countroom_A(getRoom INT)

RETURNS INT DETERMINISTIC

BEGIN

DECLARE jml INT;

SELECT count(ruangan) INTO jml FROM kuliah WHERE ruangan=102;

RETURN jml;

END //

DELIMITER ;

Membuat function countroom_A

SELECT countroom_A(102);

Menjalankan perintah function countroom_A

b.      Fungsi untuk mendapatkan nama ruangan tempat sebuah mata kuliah diadakan (nomor mata kuliah dimasukkan sebagai input). Berikan nilai ‘not found’ jika sebuah mata kuliah belum diberi  ruangan.

DELIMITER //

CREATE FUNCTION getroom_B1(getNomk varchar(10))

RETURNS VARCHAR(10) DETERMINISTIC

BEGIN

DECLARE room INT;

SELECT ruangan INTO room FROM kuliah WHERE nomk=getNomk;

IF (room>0) THEN

RETURN room;

ELSE

RETURN"Not Found";

END IF;

END //

DELIMITER ;

Buat function getroom_B1

SELECT getroom_B1(‘KOM102’);

Menjalankan perintah function getroom_b1

SELECT getroom_B1(‘KOM103’);

Pemanggilan function apabila tidak ditemukan

c.       Prosedur   untuk   menampilkan   nama   mata   kuliah   dan   ruangan   yang   diampu   oleh   seorang instruktur (nama instruktur dimasukkan sebagai input)

DELIMITER //

CREATE PROCEDURE showRoom1(getNamains varchar(225))

BEGIN

SELECT namamk, ruangan

FROM

kuliah

INNER JOIN matakuliah ON kuliah.nomk = matakuliah.nomk

INNER JOIN instruktur ON kuliah.nip = instruktur.nip

WHERE instruktur.namains = getNamains;

END //

DELIMITER ;

Membuat procedure showRoom

CALL showRoom(Stave Jobs’);

Memanggil perintah procedure showRoom

CALL showroom('Steve Wozniak');

Memanggil procedure shoowRoom

d.      Prosedur   untuk   menampilkan   jumlah   SKS   yang   diampu   oleh   seorang   instruktur   (nama instruktur dimasukkan sebagai input)

DELIMITER //

CREATE PROCEDURE getSks(getNamains varchar(10))

BEGIN

SELECT namains, sum(sks)

FROM

kuliah

INNER JOIN matakuliah ON kuliah.nomk = matakuliah.nomk

INNER JOIN instruktur ON kuliah.nip = instruktur.nip

LIKE getNamains;

END //

DELIMITER ;

Membuat procedure getSks

CALL getSks('Steve Jobs');

Memanggil Procedure getSks

CALL getSks('Steve Wozniak');

Memanggil perintah procedure getSks

Review Materi SMBD

-         RANCANGAN STRUKTUR TABEL DATABASE YG DIGUNAKAN (DLM BENTUK 3NF)

-         PRIMARY KEY & FOREIGN KEY

Tabel: user
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	user_name	VarChar	20	No	-	Primary Key
2	password	VarChar	20	No	-	-
3	level_user	Char	1	No	5	1 =  admin; 2 =  kepala sekolah; 3 =  kepala perpustakaan 4 =  anggota; 5 = pengunjung

Tabel: tanggal_libur
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	tanggal_libur	Date		no	-	Primary Key
2	Keterangan_libur	Varchar	50	no	-	-

Tabel: tarif_denda
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	jumlah_hari_maksimal	Num	3	No	-	Primary Key
2	jumlah_denda	Num	7	No	-	-

Tabel: tarif _hilang
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	harga_maksimal	Num	7	No	-	Primary Key
2	jumlah_denda	Num	7	No	-	-

Tabel: tarif _rusak
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	harga_maksimal	Num	7	No	-	Primary Key
2	jumlah_denda	Num	7	No	-	-

Tabel: anggota
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_anggota	Char	6	no	-	Primary Key
2	nama_anggota	VarChar	100	no	-	-
3	alamat	VarChar	100	no	-	-
4	kode_kecamatan	Char	6	no	-	Foreign Key
5	telepon	VarChar	12	yes	-	-
6	email	VarChar	20	yes	-	-
7	tgl_mulai_anggota	Date		no	-	-
8	jenis_anggota	Char	1	no	2	1 =  guru/ karyawan; 2 =  siswa
9	status_anggota	Char	1	no	2	1 = aktif; 2 = tidak aktif

Tabel: jenis
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_jenis_buku	Char	4	No	-	Primary Key
2	nama_jenis_buku	VarChar	20	No	-	-
3

Tabel: bidang
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_bidang	Char	4	No	-	Primary Key
2	nama_bidang	VarChar	20	No	-	-

Tabel: penerbit
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_penerbit	Char	4	No	-	Primary Key
2	nama_penerbit	VarChar	30	No	-	-
3	alamat	VarChar	100	no	-	-
4	kode_kecamatan	Char	6	no	-	Foreign Key
5	telepon	VarChar	12	yes	-	-
6	email	VarChar	20	yes	-	-

Tabel: penulis
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_penulis	Char	4	No	-	Primary Key
2	nama_penulis	VarChar	100	No	-	-
3	alamat	VarChar	100	no	-	-
4	kode_kecamatan	Char	6	no	-	Foreign Key
5	telepon	VarChar	12	yes	-	-
6	email	VarChar	20	yes	-	-

Tabel: propinsi
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_propinsi	Char	2	No	-	Primary Key
2	nama_propinsi	VarChar	30	No	-	-

Tabel: kabupaten
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_kabupaten	Char	4	No	-	Primary Key
2	nama_kabupaten	VarChar	30	No	-	-
3	kode_propinsi	Char	2	No	-	Foreign Key

Tabel: kecamatan
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_kecamatan	Char	6	No	-	Primary Key
2	nama_kecamatan	VarChar	30	No	-	-
3	kode_kabupaten	Char	4	No	-	Foreign Key

Tabel: pinjam
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_pinjam					Primary Key
2	kode_anggota	Char	6	No	-	Primary Key / Foreign Key
3	kode_buku	Char	10	No	-	Primary Key / Foreign Key
4	tanggal_pinjam	Date		No		Primary Key / Foreign Key
5	tanggal_harus_kembali	Date		N0	-	-

Tabel: kembali
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_pinjam					Primary Key / Foreign Key
2	kode_kembali					Primary Key
3	kode_anggota	Char	6	No	-	Primary Key / Foreign Key
4	kode_buku	Char	10	No	-	Primary Key / Foreign Key
5	tanggal_pinjam	Date		No		Primary Key / Foreign Key
6	tanggal_kembali	Date		No	-	-

Tabel: bayar_denda
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_kembali					Primary Key / Foreign Key
2	kode_anggota	Char	6	No	-	Primary Key/ Foreign Key
3	kode_buku	Char	10	No	-	Primary Key/ Foreign Key
4	tanggal_pinjam	Date		No		Primary Key/ Foreign Key
5	tanggal_bayar	Date		No	-	-
6	jumlah_denda	Num	9	No	0	Dihitung otomatis

Tabel: bayar_hilang
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_pinjam					Primary Key  / Foreign Key
2	kode_anggota	Char	6	No	-	Primary Key  / Foreign Key
3	kode_buku	Char	10	No	-	Primary Key  / Foreign Key
4	tanggal_pinjam	Date		No		Primary Key  / Foreign Key
5	tanggal_bayar	Date		No	-	-
6	jumlah_denda	Num	9	No	0	Dihitung otomatis

Tabel: bayar_rusak
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_pinjam					Primary Key / Foreign Key
2	kode_anggota	Char	6	No	-	Primary Key / Foreign Key
3	kode_buku	Char	10	No	-	Primary Key / Foreign Key
4	tanggal_pinjam	Date		No		Primary Key / Foreign Key
5	tanggal_bayar	Date		No	-	-
6	jumlah_denda	Num	9	No	0	Dihitung otomatis

Tabel: buku
No	Nama Field	Tipe	Ukuran	Null	Default	Keterangan
1	kode_buku	Char	10	No	-	Primary Key
2	Judul_buku	VarChar	100	No	-	-
3	kode_jenis	Char	4	No	-	Foreign Key
4	kode_bidang	Char	4	No	-	Foreign Key
5	kode_penulis_utama	Char	4	No	-	Foreign Key
6	kode_penerbit	Char	4	No	-	Foreign Key
7	jumlah	Num	1	No	-	-
8	bahasa	Char	1	No	1	1 = Indonesia; 2 = Asing
9	isbn	Char	12	No	-	-
10	tahun	Char	4	No	-	-
11	jumlah_halaman	Num	1	No	-	-
12	edisi	Char	1	No	1	-
13	cetakan_ke	Char	1	No	1	-
14	status	Char	1	No	1	1 = Baik; 2 = Rusak

DIAGRAM KERELASIAN ANTAR TABEL DATABASE 

PERINTAH SQL

1.create database perpustakaan;

2. membuat table;

·         create table user (

user_namevarchar(20) primary key not null,

password varchar(20) not null,

user char(1) default ‘5’check(level_user=‘1’ or level_user=‘2’ or level_user=‘3’ or level_user=‘4’ or level_user=‘5’));

·         create table anggota (

kd_anggota char(6) primary key not null,

nama_anggota varchar(50) not null,

alamat varchar(50) not null,

telepon varchar(12),

jenis_anggota varchar(20));

·         create table jenis_buku (

kd_jenis_buku char(4) primary key not null,

nama_jenis_buku varchar(30) not null);

·         create table bidang  (

kd_bidang char(4) primary key not null,

nama_bidang varchar(30) not null);

·         create table penerbit (

kd_penerbit char(4) primary key not null,

nama_penerbit varchar(30) not null,

alamat varchar(50) not null,

kd_kecamatan char(6) not null,

telepon varchar(12)not null,

email varchar(20));

·         create table propinsi (

kd_propinsi char(2)  primary key not null,

nama_propinsi varchar(30) not null);

·         create table kabupaten (

kd_kabupaten char(6) primary key not null,

nama_kabupaten varchar(30) not null,

kd_propinsi char(2) not null,

foreign key(kd_propinsi) references propinsi on update cascade on delete cascade);

·         create table kecamatan (

kd_kecamatan char(6) primary key not null,

nama_kecamatan varchar(30) not null,

kd_kabupaten char(6) not null,

foreign key(kd_kabupaten) references kabupaten on update cascade on delete cascade);

·         create table buku (

kd_buku char(10) primary key not null,

judul_buku varchar(35) not null,

kd_jenis char(4) not null,

kd_bidang char(4) not null,

kd_penulis char(4) not null,

kd_penerbit char(4) not null,

jumlah int(10) not null,

tahun char(4) not null,

jumlah_halaman mediumint(4) not null,

edisi char(1) not null,

cetakan_ke char(1) not null,

status char(1) default ‘1’ check(status=’1’ or status=’2’),

foreign key(kode_jenis) references jenis on update cascade on delete cascade,

foreign key(kode_bidang) references bidang on update cascade on delete cascade,

foreign key(kode_penulis) references penulis on update cascade on delete cascade,

foreign key(kode_penerbit) references penerbit on update cascade on delete cascade);

·         create table penulis (

kd_penulis char(4) primary key not null,

nama_penulis varchar(30) not null,

alamat varchar(50) not null,

kd_kecamatan char(6) not null,

telepon varchar(12),

email varchar(20),

foreign key(kode_kecamatan) references kecamatan on update cascade on delete cascade);

·         create table pinjam (

Kd_pinjam char (6) primary key not null,

kd_anggota char(6) not null,

kd_buku char(10) not null,

tanggal_pinjam date not null,

tanggal _kembali date not null,

foreign key(kd_anggota) references anggota on update cascade on delete cascade,

foreign key(kd_buku) references buku on update cascade on delete cascade,

primary key(kd_anggota, kd_buku, tanggal_pinjam);

·         create table kembali (

Kd_pinjam char(6),

Kd_kembali char(6),

kd_anggota char(6) not null,

kd_buku char(10) not null,

tanggal_pinjam date not null,

tanggal_ kembali date not null,

foreign key(kd_anggota, kd_buku, tanggal_pinjam) references pinjam on update cascade on delete cascade

primary key(kd_anggota, kd_buku, tanggal_pinjam);

·         create table bayar_denda (

kd_anggota char(6) not null,

kd_buku char(10) not null,

tanggal_pinjam date not null,

tanggal_kembali date not null,

jumlah_denda numeric(9),

foreign key(kd_anggota, kd_buku, tanggal_pinjam) references kembali on update cascade on delete cascade

primary key(kd_anggota, kd_buku, tanggal_pinjam);

·         create table bayar_hilang (

kd_pinjam char(6) not null,

kd_anggota char(10) not null,

kd_buku char(6),

tanggal_pinjam date not null,

tanggal_bayar  date not null,

jumlah  numeric(9),

foreign key(kd_pinjam,kd_anggota, kd_buku, tanggal_pinjam) references kembali on update cascade on delete cascade

primary key(kd_anggota, kd_buku, tanggal_pinjam);

3.     select sum(jumlah) as jumlahsemuabuku from buku;

4.     select judul_buku, nama_penerbit from penerbit left join buku on buku.kode_penerbit=penerbit.kode_penerbit left join pinjam on buku.kode_buku=pinjam.kode_buku order by tanggal_pinjam;

5.     select* from anggota left join pinjam on anggota.kode_anggota=pinjam.kode_anggota where pinjam.kode_anggota=not null order by tanggal_pinjam;

6.     select anggota.kode_anggota,nama_anggota, alamat, kode_kecamatan, telepon, email, tgl_mulai_anggota, jenis_anggota, status_anggota from anggota left join bayar_denda on anggota.kode_anggota=bayar_denda.kode_anggota where bayar_denda.kode_anggota=not null order by nama_anggota;

7.     select anggota.kode_anggota,nama_anggota, alamat, kode_kecamatan, telepon, email, tgl_mulai_anggota, jenis_anggota, status_anggota from anggota left join bayar_hilang on anggota.kode_anggota=bayar_hilang.kode_anggota where bayar_hilang.kode_anggota=not null order by nama_anggota;

8.     select anggota.kode_anggota,nama_anggota, alamat, kode_kecamatan, telepon, email, tgl_mulai_anggota, jenis_anggota, status_anggota from anggota left join bayar_rusak on anggota.kode_anggota=bayar_rusak.kode_anggota where bayar_rusak.kode_anggota=not null order by nama_anggota;