A. Sebuah Persewaan Film ingin membuat database menggunakan SQL Server. Buatlah database dengan nama VCD_nim anda
Dengan ketentuan :
- (untuk file .mdf)
Name = vcd_dat, filename = 'D:\Lat_SisDat\vcd_dat.mdf'
- (Untuk file .log)
name = vcd_log, filename = 'D:\Lat_SisDat\vcd_log.ldf',
Tabel-tabel yang dibutuhkan diantaranya :
Isilah tabel-tabel tersebut dengan data berikut..........
B. Untuk soal berikut ini tuliskan jawabannya dalam file .sql (file untuk menulis script di Query Analyzer) dengan nama file : Lat_SisDat_nim anda.sql
File disimpan komputer masing-masing dengan membuat folder di D:\LAt_SisDat\
Select Statement
1. Tampilkan daftar seluruh film
2. Tampilkan daftar transaksi yang dikenakan denda
3. Tampilkan daftar film yang tidak disewa
4. Tampilkan daftar seluruh film yang melebihi lama pinjam
5. Tampilkan penyewa yang meminjam sebanyak satu kali
Stored Procedured
6. Buatlah Stored Procedure untuk menghasilkan data informasi mengenai frekuensi peminjaman tiap film diurutkan berdasarkan kode vcd
7. buatlah Stored Procedure untuk menghasilkan data informasi peminjaman film dengan parameter kode transaksi
Trigger
8. Setiap kali ada peminjam ingin meminjam film, maka SQL Server harus memeriksa apakah film yang ingin dipinjam ada atau tidak. Cek apakah film terdaftar atau tidak, Jika tidak terdaftar, tolak transaksi. Jika film tersebut sedang dipinjam, SQL Server juga harus menolak proses tersebut. Status ‘Y’ berarti film sedang dipinjam.
Jika proses berhasil, maka secara otomatis nilai column StatusPinjam dari film tersebut menjadi ‘Y’
9. Setiap kali film dikembalikan (pada saat melakukan proses update pada column TglKembali), SQL Server harus mengembalikan nilai dari column StatusPinjam menjadi ‘T’
10. Pada saat peminjam mengembalikan film, SQL Server harus memeriksa apakah peminjam tersebut terlambat mengambalikan atau tidak. Jika ternyata peminjam terlambat mengembalikan film, maka peminjam dikenakan denda sebesar Rp. 500 perhari. Data tersebut harus otomatis dimasukkan kedalam column Denda (pada saat melakukan proses update pada column kembali)
11. pada saat data transaksi ingin di hapus, maka SQL Server harus mengecek apakah film yang dipinjam sudah dikembalikan apa belum. Jika film belum dikembalikan, SQL Server harus menolak penghapusan tersebut.
12. jika ingin melakukan update atau perubahan data pada tabel MsVcd atau data penyewa, SQL Server harus secara otomatis melakukan perubahan tersebut pada tabel TrPinjam
Soal Bonus (Boleh tidak dikerjakan bila menurut anda tidak perlu) :
1. buatlah suatu laporan menggunakan Crystal Report. Didalam laporan tersebut ditampilkan data mengenai tanggal pinjam, kode VCD, judul, penyewa, tanggal kembali dan denda. Selanjutnya data tersebut ditampilkan dengan mengelompokkannya berdasarkan kode VCD. Untuk masing-masing kode VCD, dibuat perhitungan sub total denda bagi masing-masing judul film. Selanjutnya di bagian paling bawah dihitung total dari sub totalnya.
.................Selamat Mengerjakan..................
JAWABAN
--JAWABAN LATIHAN PRA UAS
--ANDA BISA COPY DATABASE VCD di adiprtm.dikti.net
---------------------
--SELECT STATEMENT
---------------------
--1. tampilkan semua data film
select * from msvcd
--2.Tampilkan daftar transaksi yang dikenakan denda
select * from trpinjam where denda IS NOT NULL
--3. Tampilkan daftar film yang tidak disewa
select judul from msvcd
where not exists
(select kdvcd from trpinjam where kdvcd = msvcd.kdvcd)
--4. Tampilkan daftar seluruh film yang melebihi lama pinjam
select trpinjam.kdtransaksi,trpinjam.kdvcd,judul from msvcd,trpinjam
where trpinjam.kdvcd = msvcd.kdvcd and (trpinjam.tglkembali - trpinjam.tglpinjam) > 6
--5. Tampilkan penyewa yang meminjam sebanyak satu kali
select kdpeminjam from trpinjam
group by kdpeminjam
having count(kdpeminjam) = 1
--------------------
--STORED PROCEDURED
--------------------
---------------------------------------------------------------------------------------------
--6.Buatlah Stored Procedure untuk menghasilkan data informasi mengenai frekuensi peminjaman
-- tiap film diurutkan berdasarkan kode vcd
---------------------------------------------------------------------------------------------
create proc lihat_vcd
as select msvcd.kdvcd,msvcd.judul,count(trpinjam.kdvcd) as frekuensi
from msvcd left outer join trpinjam
on msvcd.kdvcd = trpinjam.kdvcd
group by msvcd.kdvcd,msvcd.judul
order by msvcd.kdvcd asc
--jalankan
exec lihat_vcd
----------------------------------------------------------------------------------------------
--7.buatlah Stored Procedure untuk menghasilkan data informasi peminjaman film dengan
-- parameter kode transaksi
----------------------------------------------------------------------------------------------
create proc lihat_trans
@kdtrans char(5)
as select * from trpinjam
where trpinjam.kdtransaksi = @kdtrans
--jalankan
exec lihat_trans 'TR001'
-------------
--TRIGGER
-------------
----------------------------------------------------------------------------------------------
--8.Setiap kali ada peminjam ingin meminjam film, maka SQL Server harus memeriksa apakah
-- film yang ingin dipinjam ada atau tidak. Cek apakah film terdaftar atau tidak, Jika
-- tidak terdaftar, tolak transaksi. Jika film tersebut sedang dipinjam, SQL Server juga
-- harus menolak proses tersebut. Status ‘Y’ berarti film sedang dipinjam.
-- Jika proses berhasil, maka secara otomatis nilai column StatusPinjam dari film tersebut
-- menjadi ‘Y’
----------------------------------------------------------------------------------------------
create trigger cek_vcd --buat trigger
on trpinjam --di tbl trpinjam
for insert --untuk operasi insert
as
declare @status char(1) --deklarasikan var @status utk menyimpan nilai statuspinjam
select @status = msvcd.statuspinjam from msvcd,inserted --isi var @status dgn nilai statuspinjam
where msvcd.kdvcd = inserted.kdvcd --dgn kondisi
IF @@rowcount = 0 --kondisi if.jika tidak ada record yg dicari, maka
begin
print 'maaf data vcd tidak ada' --cetak
rollback --pembatalan operasi
end
ELSE --kondisi ELSE
select * from msvcd,inserted --ambil data dari tbl msvcd dan tbl INSERTED
where msvcd.kdvcd = inserted.kdvcd --dimana msvcd.kdvcd=inserted.kdvcd
begin
if @status = 'Y' --jika nilai @status = 'Y' ,maka.......
begin
print 'maaf film sedang dipinjam' --cetak
rollback --batalkan proses
end
else --selain itu, bila @status = 'T', maka.....
begin
update msvcd --update tabel msvcd
set msvcd.statuspinjam = 'Y' --isi statuspinjam dgn nilai 'Y'
from inserted --nilai diambil dari tbl INSERTED
where msvcd.kdvcd=inserted.kdvcd --dgn kondisi
end
end
--<
--COBA ANDA UBAH DATA DIBAWAH INI SESUAI DGN FIL YG MEMILIKI STATUSPINJAM 'Y' ATAU 'T'
--BAGAIMANA PERUBAHAN YG TERJADI ?????
insert into trpinjam(kdtransaksi,tglpinjam,kdpeminjam,kdvcd,lamapinjam)
values('TR010','2008-01-08','P0002','KR002',6)
GO
select * from trpinjam
GO
select * from msvcd
GO
---------------------------------------------------------------------------------------------
--9.Setiap kali film dikembalikan (pada saat melakukan proses update pada column TglKembali),
-- SQL Server harus mengembalikan nilai dari column StatusPinjam menjadi ‘T’
---------------------------------------------------------------------------------------------
create trigger ubah_nilai
on trpinjam
for update
as
select * from inserted --pilih record dari tbl Inserted (tbl sementara yg menampung nilai baru)
update msvcd --update tbl msvcd
set statuspinjam = 'T' --isi field statuspinjam dgn nilai 'T'
from Inserted --nilai diambil dari tabel inserted (tabel yg menampung nilai baru)
where msvcd.kdvcd = inserted.kdvcd --dgn kondisi
--<
select * from trpinjam
go
select * from msvcd
go
update trpinjam
set tglkembali = '2008-01-11'
where kdtransaksi = 'tr008'
go
------------------------------------------------------------------------------------------------
--10.Pada saat peminjam mengembalikan film, SQL Server harus memeriksa apakah peminjam
-- tersebut terlambat mengambalikan atau tidak. Jika ternyata peminjam terlambat mengembalikan
-- film, maka peminjam dikenakan denda sebesar Rp. 500 perhari. Data tersebut harus otomatis
-- dimasukkan kedalam column Denda (pada saat melakukan proses update pada column kembali)
------------------------------------------------------------------------------------------------
create trigger cek_denda --buat trigger
on trpinjam --di tabel trpinjam
for update --untuk update
as
declare @hari int --deklarasikan variabel @hari
select @hari = cast(tglkembali - tglpinjam as int) --isi @hari dgn selisih dari tglkembali dgn tglpinjam.
--cast dipergunakan utk konversi nilai date menjadi nilai int
from inserted --data diambil dari tabel inserted (tbl sementara)
where kdtransaksi = inserted.kdtransaksi --dgn kondisi kode = kode dari tabel inserted
if @hari > 6 --jika nilai @hari > dari 6
begin
set @hari = @hari - 6 --isi @hari dgn (@hari - 6)
print 'Anda terlambat mengembalikan'
update trpinjam --update tabel trpinjam
set denda = (@hari * 500) --isi field denda dgn @hari * 500
from Inserted --data diambil dari tabel inserted (tbl sementara)
where trpinjam.kdtransaksi = inserted.kdtransaksi --dgn kondisi/syarat
end
--<
select * from trpinjam
go
update trpinjam
set tglkembali = '2008-01-11'
where kdtransaksi = 'tr008'
go
----------------------------------------------------------------------------------------------
--11.pada saat data transaksi ingin di hapus, maka SQL Server harus mengecek apakah film
-- yang dipinjam sudah dikembalikan apa belum. Jika film belum dikembalikan, SQL Server
-- harus menolak penghapusan tersebut.
----------------------------------------------------------------------------------------------
create trigger cek_hapus --BUAT TRIGGER
on trpinjam --DI TABEL TRPINJAM
for delete --UNTUK OPERASI DELETE
as
declare @status char(1) --DEKLARASI NILAI @STATUS UTK MENYIMPAN NILAI STATUS
select @status = msvcd.statuspinjam from msvcd, deleted --isi @status dgn nilai statuspinjam dari tbl msvcd
where msvcd.kdvcd = deleted.kdvcd --dgn kondisi msvcd.kdvcd=deleted.kdvcd
if @status = 'T' --jika @status = 'T'
begin
delete trpinjam from trpinjam, deleted --hapus data di TRPINJAM
where trpinjam.kdtransaksi = deleted.kdtransaksi--dgn kondisi kdtransaksi yg sama dari tbl trpinjam dan tbl deleted
end
else
begin
print 'Film belum dikembalikan, tidak bisa melakukan penghapusan data !!'
rollback --perintah pembatalan operasi
end
--<
select * from trpinjam
go
select * from msvcd
go
delete trpinjam where kdtransaksi = 'tr009'
----------------------------------------------------------------------------------------------
--12.jika ingin melakukan update atau perubahan data pada tabel MsVcd atau data penyewa,
-- SQL Server harus secara otomatis melakukan perubahan tersebut pada tabel TrPinjam
----------------------------------------------------------------------------------------------
--ubah data di tabel msvcd
create trigger ubah_data_msvcd --buat trigger
on msvcd --di tabel msvcd
for update --untuk update
as
select TRPINJAM.kdvcd from TRPINJAM,INSERTED,DELETED --pilih kdvcd dari tabel trpinjam
where TRPINJAM.kdvcd = DELETED.kdvcd --dimana kdvcd dari trpinjam = kdvcd dari tabel Deleted
--tabel DELETED merupakan tabel sementara utk menyimpan nilai yg lama
--tabel INSERTED merupakan tabel sementara utk menyimpan nilai yg baru
update TRPINJAM --update tabel trpinjam
set TRPINJAM.kdvcd = INSERTED.kdvcd from INSERTED,DELETED --isi nilai trpinjam.kdvcd dgn nilai kdvcd dari tabel Inserted (nilai baru)
WHERE TRPINJAM.kdvcd = DELETED.kdvcd --dgn kondisi
--<
select * from trpinjam --PERHATIKAN RECORD YG MEMILIKI KDVCD 'KM003'
go
select * from msvcd --PERHATIKAN RECORD YG MEMILIKI KDVCD 'KM003'
go
--UBAH DATA RECORD YG MEMILIKI KDVCD 'KM001' MENJADI 'KM003' APABILA DATA BERUBAH, OTOMATIS
--RECORD DI TRPINJAM DGN KDVCD YG SAMA AKAN IKUT BERUBAH
update msvcd --PERINTAH UPDATE TBL MSVCD
set kdvcd = 'KM003' --UBAH KDVCD DARI TBL MSVCD MENJADI 'KM003'
where kdvcd = 'KM001' --UNTUK RECORD YG MEMILIKI KDVCD 'KM001'
go
