Basis Data Praktikum 10 (DML VIEW)
A. LATIHAN
1.
Himpunan Entitas
Dalam latihan ini, digunakan 5 buah tabel
yaitu tabel mahasiswa, matakuliah, ambil_mk, dosen, dan jurusan. Untuk itu,
ciptakan terlebih dahulu tabel-tabel tersebut apabila belum ada.
a. Membuat tabel Mahasiswa
CREATE TABLE Mahasiswa (
Nim INT PRIMARY KEY,
nama VARCHAR(255),
Jenis_kelamin CHAR(1),
Alamat VARCHAR(255)
);
b. Mengisi data ke dalam
tabel Mahasiswa
INSERT INTO Mahasiswa (Nim, nama, Jenis_kelamin, Alamat)
VALUES
(101, 'Arif', 'L', 'Jl.
Kenangan'),
(102, 'Budi', 'L', 'Jl.
Jombang'),
(103, 'Wati', 'P', 'Jl.
Surabaya'),
(104, 'Ika', 'P', 'Jl.
Jombang'),
(105, 'Tono', 'L', 'Jl.
Jakarta'),
(106, 'Iwan', 'L', 'Jl.
Bandung'),
(107, 'Sari', 'P', 'Jl.
Malang');
c. Membuat tabel Dosen
CREATE TABLE Dosen (
Kode_dos INT PRIMARY KEY,
Nama_dos VARCHAR(255),
Alamat_dos VARCHAR(255)
);
d. Mengisi data ke dalam
tabel Dosen
INSERT INTO Dosen (Kode_dos, Nama_dos, Alamat_dos)
VALUES
(10, 'Suharto', 'Jl. Jombang'),
(11, 'Martono', 'Jl.
Kalpataru'),
(12, 'Rahmawati', 'Jl.
Jakarta'),
(13, 'Bambang', 'Jl. Bandung'),
(14, 'Nurul', 'Jl. Raya
Tidar');
CREATE TABLE Matakuliah (
Kode_mk VARCHAR(255) PRIMARY
KEY,
Nama_mk VARCHAR(255),
Sks INT,
Semester INT,
Kode_dos INT,
FOREIGN KEY (Kode_dos)
REFERENCES Dosen(Kode_dos)
);
f. Mengisi data ke dalam
tabel Matakuliah
INSERT INTO Matakuliah (Kode_mk, Nama_mk, Sks, Semester, Kode_dos)
VALUES
('PTI447', 'Praktikum Basis
Data', 1, 3, 11),
('TIK342', 'Praktikum Basis
Data', 1, 3, 11),
('PTI333', 'Basis Data
Terdistribusi', 3, 5, 10),
('TIK123', 'Jaringan Komputer',
2, 5, 10),
('TIK333', 'Sistem Operasi', 3,
5, 10),
('PTI123', 'Grafika Komputer',
3, 5, 13),
('PTI777', 'Sistem Informasi',
2, 3, 13);
g. Membuat tabel Ambil_mk
CREATE TABLE Ambil_mk (
Nim INT,
Kode_mk VARCHAR(255),
FOREIGN KEY (Nim) REFERENCES
Mahasiswa(Nim),
FOREIGN KEY (Kode_mk)
REFERENCES Matakuliah(Kode_mk)
);
h. Mengisi data ke dalam
tabel Ambil_mk
INSERT INTO Ambil_mk (Nim, Kode_mk)
VALUES
(101, 'PTI447'),
(103, 'TIK333'),
(104, 'PTI333'),
(104, 'PTI777'),
(105, 'PTI123'),
(107, 'PTI123');
i. Membuat tabel Jurusan
CREATE TABLE Jurusan (
Kode_jur VARCHAR(255) PRIMARY
KEY,
Nama_jur VARCHAR(255),
Kode_dos INT,
FOREIGN KEY (Kode_dos)
REFERENCES Dosen(Kode_dos)
);
j. Mengisi data ke dalam
tabel Jurusan
INSERT INTO Jurusan (Kode_jur, Nama_jur, Kode_dos)
VALUES
('TE', 'Teknik Elektro', 10),
('TM', 'Teknik Mesin', 12),
('TS', 'Teknik Sipil', 14);
2. Pembuatan View
Secara umum, pembuatan view tidak berbeda
dengan objek-objek database lainnya.
a. Membuat view
CREATE VIEW vgetmhs
AS SELECT nim, nama, jenis_kelamin,
alamat
FROM Mahasiswa;
b. Menampilkan view
SELECT * FROM
vgetmhs;
c. Menampilkan Query View
SHOW CREATE
VIEW vgetmhs;
d. Memodifikasi View
ALTER VIEW vgetmhs
AS SELECT nim, nama, alamat
FROM Mahasiswa
WHERE nim > 1;
SELECT * FROM vgetmhs;
f. Menghapus View
DROP VIEW vgetmhs;
3. View Kompleks
View dapat mendefinisikan suatu pernyataan yang
kompleks, misalnya melibatkan fungsi-fungsi agregat, join atau bahkan subquery.
Sebagai ilustrasi view berikut melibatkan join untuk mendapatkan matakuliah
yang tidak diambil oleh mahasiswa terdaftar.
a. Buat view
“vJOIN” dengan query sebagai berikut:
CREATE VIEW vjoin
AS SELECT m.kode_mk, m.nama_mk,
m.sks, m.semester, m. kode_dos
FROM matakuliah m
LEFT JOIN ambil_mk
A ON m.kode_mk =
a.kode_mk
WHERE a.kode_mk IS
NULL;
SELECT * FROM vjoin;
Umumnya view
diciptakan dengan mengacu pada tabel (seperti contoh-contoh sebelumnya). namun
juga tak menutup kemungkinan bagi kita untuk menciptakan view yang mengacu pada
view. pendekatan inilah yang dikenal sebagai view bersarang (nested view).
a. membuat view vmk
create view vmk
as select
kode_mk, nama_mk, sks, semester, kode_dos
from matakuliah;
b. membuat view vmk5
create view vmk5
as select
vmk.kode_mk, vmk.nama_mk, vmk.sks, vmk. semester, vmk.kode_dos, ambil_mk.nim
from vmk
join ambil_mk
on vmk.kode_mk =
ambil_mk.kode_mk
where semester = 5;
select * from vmk;
c. Lakukan Browse pada vMK5
SHOW CREATE VIEW vmk5;
d. Lakukan Browse pada vMK5
5. Updatable View
Sebagaimana disinggung
di awal, view dapat bersifat updatable .
Untuk mengetahui lebih
jelasnya, perhatikan dan ikuti langkah-langkah berikut:
a. Buatlah view sederhana sebagai berikut:
create view
vupdate
as select nim,
nama, jenis_kelamin, alamat
from mahasiswa;
b. Periksa hasilnya
select * from vupdate;
update vupdate
set alamat =
"jl. mangga"
where nim =
"107";
Select * from vupdate;
e. Periksa record dari tabel mahasiswa
6. Check Option
Pada saat menciptakan
updatable view, MySQL mengizinkan kita untuk menspesifikasikan bagaimana parser
akan bekerja. Langkah ini dilakukan dengan mengaktifkan CHECK OPTION.
Sederhananya, opsi ini mengakibatkan parser me-review klausa WHERE ketika
memproses pernyataan update di view. Ada dua jenis keyword yang bisa digunakan
saat aktivasi check option: LOCAL dan CASCADED. Keyword LOCAL membatasi
pemeriksaan hanya sebatas pada view yang didefinisikan, sedangkan mencakup
semua view yang terkait misalkan dalam kasus nested view.
Untuk mengetahui
penggunaan check option, perhatikan langkah-langkah berikut:
a. Definisikan updatable view sebagai berikut:
create view
vmkoption
as select
kode_mk, nama_mk, sks, semester, kode_dos
from matakuliah
where sks <
2;
b. Defnisikan nested view dengan local check
option sebagai berikut:
create view
vmklocal
as select
kode_mk, nama_mk, sks, semester, kode_dos
from vmkoption
where sks < 0
with local check
option;
c. Defnisikan nested view dengan Cascaded Check
Option sebagai berikut:
create view
vmkcascade
as select
kode_mk, nama_mk, sks, semester, kode_dos
from vmkoption
where sks < 0
with cascaded
check option;
d. Eksekusi perintah Insert pada vmklocal
insert vmklocal (kode_mk, nama_mk, sks,
semester, kode_dos)
values ('pti999', 'statistika', 2, 4, 12);
e. Eksekusi perintah Insert pada vmkcascade
insert vmkcascade (kode_mk, nama_mk, sks,
semester, kode_dos)
values ('pti123', 'workshop jaringan
komputer', 3, 5, 14);
Penambahan pada view vMkCascade gagal dilaksanakan karena terhambat oleh rule opsi CASCADED dimana view induk (vMkOption) menyaratkan bahwa sks harus kurang dari 2.
B. TUGAS PRAKTIKUM
as select
kode_mk, nama_mk, sks, semester, kode_dos
from matakuliah
where sks >
2;
SELECT * FROM mhslebih2sks;
2. Definisikan
view dosen yang mengajar mahasiswa beserta jumlah mahasiswa yang diajar!
create view
dosen_mengajar
as select
d.kode_dos, d.nama_dos, count(m.nim)
as jumlah_mahasiswa
from dosen d
join matakuliah
matkul
on d.kode_dos=
matkul.kode_dos
join ambil_mk
a on
matkul.kode_mk = a.kode_mk
join mahasiswa m
on a.nim = m.nim
group by d.kode_dos,
d.nama_dos;
select * from
dosen_mengajar;
3. Definisikan
nested view dari tugas praktikum nomor 2 hanya dosen yang mengajar mahasiswa
terbanyak!
create view
dosen_terbanyak
as select
d.kode_dos, d.nama_dos, dm.jumlah_mahasiswa
from dosen_mengajar
dm
join (select
max(jumlah_mahasiswa)
as
max_jumlah_mahasiswa
from dosen_mengajar)
max_dosen
on dm.jumlah_mahasiswa
= max_dosen.max_jumlah_mahasiswa
join dosen d on
dm.kode_dos = d.kode_dos;
select * from
dosen_terbanyak;
C. TUGAS RUMAH
1. Definisikan
nested view untuk mendapatkan data mahasiswa yang jenis kelaminnya laki-laki
dengan main view berupa mahasiswa yang mengambil sembarang matakuliah yang
sksnya lebih dari sks terkecil!
create view
sksterkecil
as select
min(sks) as sks_terkecil
from matakuliah;
create view
mhs_laki_laki
as select m.nim,
m.nama, m.jenis_kelamin
from mahasiswa m
join ambil_mk
a on m.nim = a.nim
join matakuliah
mk
on a.kode_mk =
mk.kode_mk
join sksterkecil
st
on mk.sks > st.sks_terkecil
where m.jenis_kelamin
= 'L';
select* from
mhs_laki_laki;
2. Definisikan
updatable view dengan local check option untuk mendapatkan data matakuliah
dengan sks diantara 1 dan 4.
create view umk
as select
kode_mk, nama_mk, sks, semester, kode_dos
from matakuliah
where sks between 1 and 4
with local check
option;
SELECT * FROM umk;
Komentar
Posting Komentar