sistem kasir dan pengolahan data pada toserba
DESCRIPTION
sistem kasir dan pengolahan data pada toserbaTRANSCRIPT
LAPORAN TUGAS BESARPERANCANGAN BASIS DATA RELASIONAL
Sistem Kasir dan Pengolahan Data Swalayan
Disusun oleh :
1. Muhammad Fuad (IF 33-03/113090129)2. Kresna Aditya Hendri (IF 33-03/113090130)3. Septian Ardiansyah (IF 33-03/113090139)4. Dwiaji Nuraryudha (IF 33-03/113090166)5. Muhammad Richard (IF 33-03/113090235)
INSTITUT TEKNOLOGI TELKOM
BANDUNG
2011
FUCNTIONAL REQUIERMENT
(US01) Dalam aplikasi ini, petugas (dalam hal ini, Kasir) dapat melakukan beberapa kegiatan, diantaranya :
(US01.1) Memasukkan data barang, beserta kategori, serta lokasinya
(US01.2) Melihat data barang yang telah diinput
(US01.3)Melihat data semua customer
(US01.4) Mengecek total pengeluaran
(US01.5) Mengecek total pendapatan
(US01.6) Menghapus data barang yang sudah terbeli
(US01.7) Menentukan diskon suatu barang, berdasarkan tipe membership yang dimiliki oleh member.
(US01.8) Melakukan pengecekan terhadap lokasi suatu barang.
(US01.9) Melakukan update terhadap isi-isi tabel dari database.
(US01.10) Menghitung total pembayaran kepada setiap supplier.
CONCEPTUAL DESIGN
PHYSICAL DESIGN
Supplier
column data_type
length
presicion
constraint
constraint definition
referenced tabel
referenced column
kode_supplier char 20 primary nama_supplier varchar 20 telepon varchar 20
Member
column data_type length
presicion
constraint constraint definition
referenced tabel
referenced column
id_member char 20 Primary Nama_member varchar 20 not null type_member varchar 20 not null
Petugas
column data_type
length
presicion
constraint
constraint definition
referenced tabel
referenced column
id_petugas char 20 primary nama_petugas varchar 20 not null password varchar 20 not null
Kategori
column data_type Length presicion constraint constraint definition
referenced tabel
referenced column
id_kategori char 20 Primary jenis varchar 20 not nulllorong number 20 Foreign lokasi Lorong
Barang
column data_type
length
presicion
constraint
constraint definition
referenced tabel
referenced column
kode_barang char 20 primary nama_barang varchar 20 not_null harga_beli number 20 not_null harga_jual number 20 not_null kode_supplier char 20 Foreign supplier kode_supplierid_kategori char 20 Foreign kategori id_kategori
Lokasi
column data_type Length presicion constraint constraint definition
referenced tabel
referenced column
lorong number 20 not_null
IMPLEMENTATION
#ddl
create table supplier
(
kode_supplier char(20) constraint pk_supplier primary key,
nama_supplier varchar(20) not null,
telepon varchar(20));
==========================================================================
create table member
(
id_member char(20) constraint pk_member primary key,
nama_member varchar (20) not null ;
type_member varchar(20) not null);
==========================================================================
create table petugas
(
id_petugas char(20) constraint pk_id_petugas primary key,
nama_petugas varchar2(20) not null,
password varchar(20) not null);
==========================================================================
create table kategori
(
id_kategori char(20) constraint pk_id_kategori primary key,
jenis varchar(20) not null);
==========================================================================
create table barang
(
kode_barang char(20) constraint pk_barang primary key,
nama_barang varchar(20) not null,
harga_beli number(20) not null,
harga_jual number(20) not null,
kode_supplier char(20) constraint bar_sup_fk references supplier(kode_supplier),
id_kategori char(20) constraint bar_kat_fk references kategori(id_kategori));
==========================================================================
create table laporan
(tanggal date,
kode_barang varchar(20) constraint pk_laporan primary key,
nama_barang varchar(20),
pendapatan number(20),
pengeluaran number(20),
kode_supplier char(20) constraint lap_sup_fk references supplier(kode_supplier),
id_member char(20) constraint lap_mem_fk references member(id_member),
id_petugas char(20) constraint lap_pet_fk references petugas(id_petugas));
===========================================================================
create table lokasi
(
lorong number(20) not null,
id_kategori char(20) constraint lok_kat_fk references kategori(id_kategori));
===========================================================================
#dml
insert into lokasi values('1');
insert into lokasi values('2');
insert into lokasi values('3');
insert into lokasi values('4');
insert into lokasi values('5');
================================================================================
insert into kategori values('k001','snack','1');
insert into kategori values('k002','minuman','1');
insert into kategori values('k003','alat rumahtangga','2');
insert into kategori values('k004','buah','2');
insert into kategori values('k005','sayur','5');
insert into kategori values('k006','atk','4');
================================================================================
insert into supplier values('s001','Kresna','085723580807');
insert into supplier values('s002','Dajong','085234253456');
insert into supplier values('s003','Fuad','0846572374544');
insert into supplier values('s004','Septian','082346623456');
insert into supplier values('s005','Richard','084552436535');
================================================================================
insert into barang values('0001','Sabun','2000','3000','s002','k003');
insert into barang values('0002','Aqua','1500','2000','s001','k002');
insert into barang values('0003','Pulpen','2200','2500','s003','k006');
insert into barang values('0004','Kangkung','2900','3400','s004','k005');
insert into barang values('0005','so nice','5500','6000','s005','k001');
================================================================================
insert into member values('m001','Dono','bronze');
insert into member values('m003','Doni','silver');
insert into member values('m004','Dona','gold');
insert into member values('m005','Dani','silver');
insert into member values('m006','Dodi','gold');
================================================================================
insert into petugas values('p001','Fredy','12345');
insert into petugas values('p002','Mercury','23456');
insert into petugas values('p003','John','34567');
insert into petugas values('p004','Roger','45678');
insert into petugas values('p005','Brian','56789');
================================================================================
insert into laporan values('','0020','Rokok','12000','10000','s001','','p001');
insert into laporan values('','0021','Bantal','60000','40000','s002','m001','p001');
insert into laporan values('','0022','Soda','5000','3000','s003','m001','p001');
insert into laporan values('','0023','Pelembab','10000','7000','s001','m001','p001');
insert into laporan values('','0024','Gunting','3000','2000','s002','m006','p002');
insert into laporan values('','0025','Biskuat','2000','1000','s001','m006','p002');
insert into laporan values('','0026','Sikat Gigi','5000','3000','s003','','p003');
insert into laporan values('','0027','Piring','20000','18000','s003','m001','p003');
===================================================================================
TESTING
Test Number Script (Sql) Requirement HasilT1T1.1 US01.1 OkT1.2 US01.2 OkT1.3 US01.3 OkT1.4 US01.4 OkT1.5 US01.5 OkT1.6 US01.6 OkT1.7 US01.7 OkT1.8 US01.8 OkT1.9 US01.9 OkT1.10 US01.10 Ok
Script (SQL)
T1.1 :
/*melihat data barang*/
select * from barang;
T1.2 :
/*melihat data customer*/
select * from member
T1.3 :
/*cek total pengeluaran*/
select sum(pengeluaran) as total_pengeluaran from laporan;
T1.4 :
/*cek total pendapatan*/
select sum(pendapatan) as total_pendapatan from laporan;
T1.5 :
/*cek total per hari*/
select tanggal,sum(pendapatan) as pendapatan_per_hari
from laporan
group by tanggal;
T1.6 :
/*menghapus data barang ketika barang tersebut sudah dibeli*/
CREATE OR REPLACE TRIGGER aft_ins_laporan
AFTER INSERT ON laporan
FOR EACH ROW
BEGIN
delete from barang where kode_barang = :new.kode_barang;
END;
/
T1.7 :
/*menetukan diskon berdasarakan membership*/
/*men-set trigger input tanggal sekarang*/
CREATE OR REPLACE TRIGGER bef_ins_laporan
BEFORE INSERT ON laporan
FOR EACH ROW
DECLARE
v_type member.type_member%TYPE;
BEGIN
:new.tanggal := SYSDATE;
if :new.id_member <> '' then
select type_member into v_type
from member
where id_member = :new.id_member;
if (v_type='bronze') then
:new.pendapatan := (:new.pendapatan-(:new.pendapatan * 0.02));
elsif (v_type='silver') then
:new.pendapatan := (:new.pendapatan-(:new.pendapatan * 0.05));
elsif (v_type='gold') then
:new.pendapatan := (:new.pendapatan-(:new.pendapatan * 0.1));
else :new.pendapatan := :new.pendapatan;
end if;
end if;
END;
/
T1.8 :
/*melakukan pengecekkan lokasi suatu barang*/
declare
v_nama varchar(20) := '&inputan';
v_lokasi number(20);
begin
select l.lorong into v_lokasi
from lokasi l, kategori k, barang b
where b.id_kategori = k.id_kategori
and k.lorong = l.lorong
and b.nama_barang = v_nama;
dbms_output.put_line('barang yang dicari ada di lorong '||v_lokasi);
end;
/
T1.9 :
/*update*/
update <nama_table>
set <nama kolom> = <nilainya>
where <kondisi>;
T1.10 :
/*melakukan banyaknya pengeluaran berdasarkan supplier*/
select s.kode_supplier, sum(l.pengeluaran) as total
from supplier s, laporan l
where s.kode_supplier = l.kode_supplier
group by s.kode_supplier;