Posting jawaban lengkap Case Study Perusahaan Air Minum

4 Responses to “Posting jawaban lengkap Case Study Perusahaan Air Minum”

1. Selvi W. says:

   June 1, 2009 at 12:09 am

   Selvi Wulandari / 1000879583 / 06 PGT

   Piece of Code: Master Product
   =============================

   CREATE TABLE MsProduct
   (
   KdProduct VARCHAR2(5) PRIMARY KEY,
   NamaProduct VARCHAR2(20),
   JenisProduct VARCHAR2(5),
   Harga NUMBER,
   );

   INSERT INTO MsProduct VALUES (’P001′,’Cup 240ml’,’Cup’,12000);
   INSERT INTO MsProduct VALUES (’P002′,’Botol 600ml’,’Botol’,19000);
   INSERT INTO MsProduct VALUES (’P003′,’Botol 1500ml’,’Botol’,19500);
   INSERT INTO MsProduct VALUES (’P004′,’Galon 19L’,’Galon’,6000);

   Piece of Code: Master Customer
   ==============================

   CREATE TABLE MsCustomer(
   KdCustomer VARCHAR2(5) NOT NULL PRIMARY KEY,
   NamaCustomer VARCHAR2 (20),
   AlamatCustomer VARCHAR2 (100),
   TelpCustomer VARCHAR (15)
   );

   INSERT INTO MsCustomer VALUES (’C001′, ‘Christina’, ‘Jl.Palmerah no.1’, ‘147258′);
   INSERT INTO MsCustomer VALUES (’C002′, ‘Daniel’, ‘Jl.Palmerah no.2’, ‘472583′);
   INSERT INTO MsCustomer VALUES (’C003′, ‘Matthew’, ‘Jl.Palmerah no.3’, ‘725836′);
   INSERT INTO MsCustomer VALUES (’C004′, ‘Sammuel’, ‘Jl.Palmerah no.4’, ‘258369′);
   INSERT INTO MsCustomer VALUES (’C005′, ‘Elia’, ‘Jl.Palmerah no.5’, ‘583691′);
   INSERT INTO MsCustomer VALUES (’C006′, ‘Joshua’, ‘Jl.Palmerah no.6’, ‘369147′);
   INSERT INTO MsCustomer VALUES (’C007′, ‘Esther’, ‘Jl.Palmerah no.7’, ‘691472′);
   INSERT INTO MsCustomer VALUES (’C008′, ‘Michael’, ‘Jl.Palmerah no.8’, ‘914725′);
   INSERT INTO MsCustomer VALUES (’C009′, ‘Timmotius’, ‘Jl.Palmerah no.9’, ‘789456′);
   INSERT INTO MsCustomer VALUES (’C010′, ‘Yoan’, ‘Jl.Palmerah no.10’, ‘456123′);

   Piece of Code: Master SalesPerson
   =================================

   CREATE TABLE MsSalesPerson (
   KdsalesPerson VARCHAR(5) NOT NULL PRIMARY KEY,
   NamaSalesPerson VARHCHAR(20),
   AlamatSalesPerson VARCHAR(100),
   TelpSalesPerson VARCHAR(15),
   );

   INSERT INTO MsSalesPerson VALUES (’S001′,’AAA’, ‘Jl. Kemanggisan no. 1′,021546234);
   INSERT INTO MsSalesPerson VALUES (’S002′,’BBB’, ‘Jl. Kemanggisan no. 2′,021589745);
   INSERT INTO MsSalesPerson VALUES (’S003′,’CCC’, ‘Jl. Kemanggisan no. 3′,021669874);

   Piece of Code: Transaksi Sales Order
   ====================================

   CREATE TABLE TrHeaderSO (
   noSO VARCHAR(5) NOT NULL PRIMARY KEY,
   tglSO DATE,
   KdCustomer VARCHAR(5),
   kdsalesPerson VARCHAR(5),
   CONSTRAINT SO_Customer FOREIGN KEY (KdCustomer) REFERENCES msCustomer (KdCustomer),
   CONSTRAINT SO_SalesPerson FOREIGN KEY (KdsalesPerson) REFERENCES msSalesPerson (KdsalesPerson)
   );

   INSERT INTO TrHeaderSO VALUES (’T001′,’05/25/2009′,’C001′,’S002′);
   INSERT INTO TrHeaderSO VALUES (’T002′,’05/26/2009′,’C003′,’S003′);
   INSERT INTO TrHeaderSO VALUES (’T003′,’05/27/2009′,’C002′,’S001′);

   CREATE TABLE TrDetailSO (
   noSO VARCHAR(5),
   kdProduk VARCHAR(5),
   qty NUMBER,
   CONSTRAINT SO_noSO FOREIGN KEY (noSO) REFERENCES TrHeaderSO (noSO),
   CONSTRAINT SO_Produk FOREIGN KEY (KdProduk) REFERENCES MsProduct (KdProduk)
   );

   INSERT INTO TrDetailSO VALUES (’T001′,’P001′,5);
   INSERT INTO TrDetailSO VALUES (’T002′,’P002′,3);
   INSERT INTO TrDetailSO VALUES (’T003′,’P004′,7);
2. august 1000843311 06pbt says:

   June 23, 2009 at 3:42 pm

   –membuat tabel master product
   CREATE TABLE product
   (
   KdProd CHAR(5),
   NmProd VARCHAR2(20),
   JnsProd VARCHAR2(20),
   Hrg NUMBER,
   CONSTRAINT prod_pk PRIMARY KEY(KdProd)
   );

   –menginsert isi tabel produk
   INSERT INTO product VALUES (’P0001′,’Cup 240ml’,’Cup’,12000);
   INSERT INTO product VALUES (’P0002′,’Botol 600ml’,’Botol’,19000);
   INSERT INTO product VALUES (’P0003′,’Botol 1500ml’,’Botol’,19500);
   INSERT INTO product VALUES (’P0004′,’Galon 19L’,’Galon’,6000);

   –membuat master tabel jenis customer
   CREATE TABLE JnsCust
   (
   KdJnsCust CHAR(5) PRIMARY KEY,
   Jenis varchar(20)
   );

   –menginsert isi dari jenis customer
   INSERT INTO JnsCust VALUES (’J0001′,’J1′);
   INSERT INTO JnsCust VALUES (’J0002′,’J2′);

   –membuat master tabel customer
   CREATE TABLE customer
   (
   KdCust CHAR(5),
   NmCust VARCHAR2(20),
   KdJnsCust CHAR(5),
   CONSTRAINT cust_pk PRIMARY KEY(KdCust),
   CONSTRAINT jnsid_fk FOREIGN KEY (KdJnsCust) REFERENCES JnsCust(KdJnsCust)
   );

   –menginsert isi dari customer
   INSERT INTO customer VALUES (’C0001′,’A’,’J0001′);
   INSERT INTO customer VALUES (’C0002′,’B’,’J0001′);
   INSERT INTO customer VALUES (’C0003′,’C’,’J0001′);
   INSERT INTO customer VALUES (’C0004′,’D’,’J0001′);
   INSERT INTO customer VALUES (’C0005′,’E’,’J0001′);
   INSERT INTO customer VALUES (’C0006′,’F’,’J0002′);
   INSERT INTO customer VALUES (’C0007′,’G’,’J0002′);
   INSERT INTO customer VALUES (’C0008′,’H’,’J0002′);
   INSERT INTO customer VALUES (’C0009′,’I’,’J0002′);
   INSERT INTO customer VALUES (’C0010′,’J’,’J0002′);

   –membuat master tabel salesperson
   CREATE TABLE salesperson
   (
   KdSP CHAR(5),
   NmSP VARCHAR(20),
   CONSTRAINT sp_pk PRIMARY KEY(KdSP)
   );

   –menginsert tabel salesperson
   INSERT INTO salesperson VALUES (’S0001′,’X’);
   INSERT INTO salesperson VALUES (’S0002′,’Y’);
   INSERT INTO salesperson VALUES (’S0003′,’Z’);

   –membuat tabel master transaksi sales order header
   CREATE TABLE SOHeader
   (
   No_TrSOHeader CHAR(5),
   KdSP CHAR(5),
   KdCust CHAR(5),
   Hari DATE,
   CONSTRAINT trsoh_pk PRIMARY KEY(No_TrSOHeader),
   CONSTRAINT spid_fk FOREIGN KEY (KdSP) REFERENCES SALESPERSON(KdSP),
   CONSTRAINT custid_fk FOREIGN KEY (KdCust) REFERENCES customer(KdCust)
   );

   –membuat tabel master transaksi sales order detail
   CREATE TABLE SODetail
   (
   No_TrSOHeader CHAR(5),
   KdProd CHAR(5),
   Qty NUMBER(3),
   CONSTRAINT trsod_pk PRIMARY KEY(No_TrSOHeader,KdProd),
   CONSTRAINT trsod_fk FOREIGN KEY(No_TrSOHeader) REFERENCES SOHeader(No_TrSOHeader),
   CONSTRAINT trsod_prod_fk FOREIGN KEY(KdProd) REFERENCES product(KdProd)
   );

   —————————————————————–—————————————–

   [asumsi yang terjadi pada transaksi : tanggal transaksi diacak, customer diacak, produk diacak, sales person diacak, jumlah barang diacak berdasarkan jenis pelanggan. Pada tanggal yang sama, customer dapat membeli lebih dari 1 jenis produk(tapi tidak boleh jenis produk yang sama)]

   —————————————————————–——————————————

   –menginsert tabel transaksi sales order header dan detail

   declare
   tgltrans date;
   cust char(5);
   jmlbrg int;
   sales char(5);
   jnscust char(5);

   NoTrans char(5);
   prod char(5);
   qty int;

   jmltrans int:=1000;

   counter int:=jmltrans;
   counter2 int;
   temp int;

   begin
   while counter>0 loop

   –Random data
   tgltrans := to_date(’1-Jan-08′,’DD-Mon-RR’) + round(dbms_random.value(0,365));
   cust := ‘C’ || lpad(round(dbms_random.value(1,10)),4,’0′);
   jmlbrg := round(dbms_random.value(1,4));
   sales := ‘S’ || lpad(round(dbms_random.value(1,3)),4,’0′);
   NoTrans := ‘T’ || lpad(jmltrans-counter+1,4,’0′);

   –Mengambil jenis customer
   select KdJnsCust into jnscust from customer where KdCust=cust;

   –Insert data header
   insert into SOHeader values (NoTrans, sales, cust, tgltrans);

   –Persiapkan JmlBarang
   counter2:=jmlbrg;

   while counter2>0 loop

   –Random Kode Barang yang dibeli dan qty
   prod := ‘P’ || lpad(round(dbms_random.value(1,4)),4,’0′);

   if(jnscust=’J0001′) then
   qty := round(dbms_random.value(1,10));
   else
   qty := round(dbms_random.value(20,50));
   end if;

   –Cek di detail ada atau tidak customer membeli barang yang sama dalam 1 hari
   select count(b.No_SOHeader) into temp from SoHeader a, SODetail b where
   a.No_SOHeader=b.No_SOHeader(+) and
   a.KdCust=cust and
   b.KdProd=prod;

   –Jika ada hapus transaksi, ulang loop
   if temp!=0 then
   delete from SODetail where No_SOHeader=NoTrans;
   delete from SOHeader where No_SOHeader=NoTrans;
   counter:=counter+1;
   counter2:=0;
   else
   insert into SODetail values(NoTrans,prod,qty);
   end if;

   –Kurangi counter jumlah
   counter2:=counter2-1;
   end loop;

   –Kurangi counter transaksi
   counter:=counter-1;

   end loop;
   end;
3. Beny says:

   July 3, 2009 at 1:00 pm

   Beny
   1000867734
   06PGT

   pak, ini saya mau posting full of codenya. (untuk piece of code dan calculate storage sudah saya posting sebulan yang lalu. terima kasih.)

   CREATE TABLE MsProduct
   (
   KodeProduk char(5) primary key not null,
   NamaProduk varchar(25),
   JenisProduk varchar(15),
   HargaSatuan number(8)
   );

   INSERT INTO MsProduk(KodeProduk, NamaProduk, JenisProduk, HargaSatuan) VALUES(’P001′,’Cup 240ml’,’Cup’,12000);
   INSERT INTO MsProduk(KodeProduk, NamaProduk, JenisProduk, HargaSatuan) VALUES(’P002′,’Botol 600ml’,’Botol’,19000);
   INSERT INTO MsProduk(KodeProduk, NamaProduk, JenisProduk, HargaSatuan) VALUES(’P003′,’Botol 1500ml’,’Botol’,19500);
   INSERT INTO MsProduk(KodeProduk, NamaProduk, JenisProduk, HargaSatuan) VALUES(’P004′,’Galon 19L’,’Galon’,6000);

   CREATE TABLE MsCustomer
   (
   kodeCustomer char(5) primary key not null,
   namaCustomer varchar(25) not null,
   alamatCustomer varchar(40)
   );

   insert into MsCustomer values(’c0001′,’Squall’,’Jl. Empang 3’);
   insert into MsCustomer values(’c0002′,’Zidane’,’Jl. Batusari’);
   insert into MsCustomer values(’c0003′,’Rinoa’,’Jl. Pertamburan’);
   insert into MsCustomer values(’c0004′,’Tidus’,’Jl. Menteng’);
   insert into MsCustomer values(’c0005′,’Seifer’,’Jl. Pejaten Barat’);
   insert into MsCustomer values(’c0006′,’Steiner’,’Jl. Pejaten Timur’);
   insert into MsCustomer values(’c0007′,’Garnet’,’Jl. Lenteng Agung’);
   insert into MsCustomer values(’c0008′,’Yuna’,’Jl. Kuningan’);
   insert into MsCustomer values(’c0009′,’Vivi’,’Jl. Medan Merdeka’);
   insert into MsCustomer values(’c0010′,’Eiko’,’Jl. Gatot Subroto’);

   create table MsSales
   (
   KodeSales char(5) primary key not null,
   NamaSales varchar(25),
   AlamatSales varchar(40)
   );

   insert into MsSales values(’s0001′,’Ultimecia′,’Jl. Pejaten Mas’);
   insert into MsSales values(’s0002′,Necron′,’Jl. Pertanian’);
   insert into MsSales values(’s0003′,’Kuja′,’Jl. Thamrin’);

   CREATE TABLE SOHeader
   (
   KodeSO varchar(5) Primary Key,
   KodeCustomer varchar(5),
   KodeSales varchar(5),
   TanggalSO DATE,
   constraint c_1 FOREIGN KEY(KodeCustomer) REFERENCES MsCustomer(KodeCustomer),
   constraint c_2 FOREIGN KEY(KodeSales) REFERENCES MsSales (KodeSales)
   );

   insert into SOHeader values(’S0001′,’C0001′,’S0001′,TO_DATE(’31-05-2009′,’DD-MM-YY’))

   CREATE TABLE SODetail
   (
   KodeSO varchar2(5),
   KodeProduk varchar(5),
   Qty numeric,
   constraint c_3 PRIMARY KEY(KodeSO, KodeProduk),
   constraint c_4 FOREIGN KEY(KodeProduk) REFERENCES MsProduct(KodeProduk)
   );

   insert into SODetail values(’S0001′,’P0001′,15)
4. Lidya Chandra says:

   July 7, 2009 at 4:43 pm

   06PGT
   Ariane Suci Ismarani 1000851111
   Desy Ayu Puspita 1000885200
   Kania Fitriani 1000866196
   Lidya Chandra 1000878883

   CREATE TABEL MASTER

   create table Product (
   kdProd varchar(10) primary key
   nameProd varchar(30),
   kindProd varchar(20),
   price number
   )

   create table Customer (
   kdCust varchar(10) primary key
   nameCust varchar(30),
   addrCust varchar(50)
   )

   create table KindCustomer (
   kdKindCust varchar(10) primary key
   nameKindCust varchar(30)
   )

   create table SalesPerson (
   kdSP varchar(10) primary key
   nameSP varchar(30),
   addrSP varchar(50)
   )

   CREATE TABEL TRANSAKSI

   create table HeaderSalesOrder (
   kdSO varchar(10) primary key
   dateSO date,
   kdCust references Customer
   kdSP references SalesPerson
   )

   create table DetailSalesOrder (
   kdSO references SalesOrder
   kdProd references Product
   qty number
   )

   INSERT TABEL MASTER

   Insert Into Product (kdProd,nameProd,kindProd,price) Values (P001,’Cup 240ml’,’Cup’,12000)
   Insert Into Product (kdProd,nameProd,kindProd,price) Values (P002,’Botol 600ml’,’Botol’,19000)
   Insert Into Product (kdProd,nameProd,kindProd,price) Values (P003,’Botol 1500ml’,’Botol’,19500)
   Insert Into Product (kdProd,nameProd,kindProd,price) Values (P004,’Galon 19l’,’Galon’,6000);

   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00001,’Nicholas Cage’,Bintaro)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00002,’Bruce Willis’,Serpong)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00003,’Sandra Bullock’,Bintaro)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00004,’Julia Robert’,Serpong)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00005,’Vin Diesel’,Ciputat)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00006,’Russel Crowl’,Ciputat)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00007,’Ambler Tamblyin’,Slipi)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00008,’Britany Murphy’,Pamulang)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00009,’Dakota Fanning’,Tangerang)
   Insert Into Customer (kdCust,nameCust,addrCust) Values (C00010,’Denzel Washington’,Slipi);

   Insert Into KindCustomer (kdKindCust,nameKindCust) Values (KC0001,’Rumah Tangga’)
   Insert Into KindCustomer (kdKindCust,nameKindCust) Values (KC0002,’Perusahaan’);

   Insert Into SalesPerson (kdSP,nameSP,addrSP) Values (SP0001,’Desy’,’Jl.Kemanggisan Barat No.2’)
   Insert Into SalesPerson (kdSP,nameSP,addrSP) Values (SP0002,’Kania’,’Jl.Mahoni No.18 Ciputat’)
   Insert Into SalesPerson (kdSP,nameSP,addrSP) Values (SP0003,’Ane’,’Jl.Tangerang Raya No.202’);

   INSERT TABEL TRANSAKSI

   Insert Into HeaderSalesOrder (kdSO,dateSO,kdCust,kdSP) Values (SO00001,’01-01-2009’,C00002, SP0002)
   Insert Into HeaderSalesOrder (kdSO,dateSO,kdCust,kdSP) Values (SO00002,’06-22-2009’,C00008, SP0001)
   Insert Into HeaderSalesOrder (kdSO,dateSO,kdCust,kdSP) Values (SO00003,’12-10-2009’,C00005, SP0003);

   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00001,P001,10)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00001,P002,5)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00001,P003,12)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00002,P001,22)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00002,P003,7)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00002,P004,5)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00003,P001,15)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00003,P002,28)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00003,P003,15)
   Insert Into DetailSalesOrder (kdSO,kdProd,qty) Values (SO00003,P004,3);