Basis Data Lanjut 1.5

BASIS DATA LANJUT

SISTEM INFORMASI APOTEK <1.5>

Nama :

Lusi Susanti ( 08120077 )

Erfan Rachmad S ( 09120073 )

TEKNIK INFORMATIKA

UNIVERSITAS WIJAYA KUSUMA SURABAYA

2010-2011

CDM ( Concept Data Model )

Jenis_Golongan

supply

punya

punya

(D)

punya

catat

Relationship_7punya

punya

catat

jenis_pegawai

bentuk_obat

Jenis_Obat

Obat

ID_Obat

Nama_Obat

Khasiat_Obat

Harga_Jual

Satuan

Status_Obat

kadaluarsa

<pi> Variable characters (7)

Variable characters (50)


Integer


Integer

Date

golongan_obat

id_golongan

nama_golongan

keterangan_golongan

<pi> Integer



golongan_obat

...

<pi>

Pegawai

id_pegawai

nama_pegawai

alamat_pegawai

no_telpon_pegawai

tgl_masuk_pegawai





Date

pegawai <pi>

Stok_Obat

numbering

jumlah_stok

<pi> Integer

Integer

<M>

<M>

Identifier_1

...

<pi>

Supplier

ID_Supplier

Nama_Supplier

Alamat_Supplier

No_Telpon_Supplier





Supplier <pi>

Pembelian

id_pembelian

tgl_pembelian

keterangan_pembelian


Date


id_pembelian <pi>

detail_pembelian

num_pembelian

jumlah_pembelian

harga_satuan

<pi> Integer

Integer

Money

<M>

<M>

<M>

detail_pembelian

...

<pi>

Pelanggan

id_pelanggan

nama_pelanggan

alamat_pelanggan

no_telpon_pelanggan





<M>

<M>

pelanggan <pi>

penjualan

id_penjualan

tgl_penjualan

total_harga_penjualan

keterangan_penjualan


Date & Time

Money


<M>

penjualan <pi>

detail_penjualan

num_penjualan

jumlah_barang

harga_satuan

<pi> Integer

Integer

Money

<M>

<M>

<M>

detail_penjualan <pi>

pegawai_tetap

gaji_tetap Integer <M>

pegawai_ttetap

gaji_harian Integer <M>

Bentuk_Obat

ID_bentuk_obat

Bentuk_Obat

Keterangan_bentuk_obat

<pi> Integer



Identifier_1 <pi>

Obat_Paten

Kode_Paten Variable characters (25)

Obat_Generik

Kode_Generik Variable characters (25)

Deskripsi

Obat memiliki jenis obat (Obat paten dan Obat

generik)

Obat memiliki jenis golongan

(bebas,terbatas,dll)

Obat memliki bentuk antara lain

(cair,pil,kapsul,dll)

Obat memiliki stok obat yang diupdate secara

regular berdasar transaksi

Pegawai mencatat 2 jenis transaksi, Pembelian

dan Penjualan

Setiap Penjualan memiliki detail Penjualan (

yang didalamnya melibatkan Entity Obat, Pegawai dan Pelanggan )

Setiap Pembelian memiliki detail Pembelian

(yang didalamnya melibatkan Entity Obat, Pegawai dan Supplier )

Pegawai dibagi menjadi 2 jenis yaitu pegawai

tetap dan tidak tetap

PDM (Physical Data Model)

Obat

ID_Obat

ID_bentuk_obat

id_golongan

Nama_Obat

Khasiat_Obat

Harga_Jual

Satuan

Status_Obat

kadaluarsa

Kode_Paten

Kode_Generik

varchar(7)

int

int

varchar(50)

varchar(50)

int

varchar(25)

int

datetime

varchar(25)

varchar(25)

<pk>

<fk2>

<fk1>

golongan_obat

id_golongan

nama_golongan

keterangan_golongan

...

int

varchar(50)

varchar(59)

<pk>

Pegawai

id_pegawai

nama_pegawai

alamat_pegawai

no_telpon_pegawai

tgl_masuk_pegawai

gaji_tetap

gaji_harian

varchar(5)

varchar(50)

varchar(50)

varchar(15)

datetime

int

int

<pk>

Stok_Obat

numbering

ID_Obat

jumlah_stok

...

int

varchar(7)

int

<pk>

<fk>

Supplier

ID_Supplier

Nama_Supplier

Alamat_Supplier

No_Telpon_Supplier

...

varchar(3)

varchar(50)

varchar(50)

varchar(15)

<pk>

Pembelian

id_pembelian

ID_Supplier

id_pegawai

tgl_pembelian

keterangan_pembelian

...

varchar(10)

varchar(3)

varchar(5)

datetime

varchar(50)

<pk>

<fk1>

<fk2>

detail_pembelian

num_pembelian

id_pembelian

ID_Obat

jumlah_pembelian

harga_satuan

...

int

varchar(10)

varchar(7)

int

money

<pk>

<fk1>

<fk2>

Pelanggan

id_pelanggan

nama_pelanggan

alamat_pelanggan

no_telpon_pelanggan

...

varchar(10)

varchar(25)

varchar(50)

varchar(15)

<pk>

penjualan

id_penjualan

id_pegawai

id_pelanggan

tgl_penjualan

total_harga_penjualan

keterangan_penjualan

...

varchar(10)

varchar(5)

varchar(10)

datetime

money

varchar(50)

<pk>

<fk1>

<fk2>

detail_penjualan

num_penjualan

id_penjualan

ID_Obat

jumlah_barang

harga_satuan

...

int

varchar(10)

varchar(7)

int

money

<pk>

<fk2>

<fk1>

Bentuk_Obat

ID_bentuk_obat

Bentuk_Obat

Keterangan_bentuk_obat

...

int

varchar(25)

varchar(50)

<pk>

Active Database Model

Stored Procedure

sp_delete_all_record

Stored procedure untuk melakukan penghapusan keseluruhan record pada

tabel yang ada

sp_contoh_database

Stored procedure untuk contoh database apotek

Function

fn_kerja_tahun

Fungsi untuk menghitung waktu kerja pegawai dengan satuan tahun

fn_kerja_bulan

Fungsi untuk menghitung waktu kerja pegawai dengan satuan bulan

fn_kerja_hari

Fungsi untuk menghitung waktu kerja pegawai dengan satuan hari

Trigger

tg_update_stokObat

Melakukan update data pada tabel stok_obat apabila terjadi perubahan

pada tabel penjualan, maka pada tabel stok_obat akan dilakukan update

pada column jumlah_stok

tg_insert_stokObat

Melakukan update data pada tabel stok_obat apabila terjadi perubahan

pada tabel pembelian, maka pada tabel stok_obat akan dilakukan update

pada column jumlah_stok

Package

PK_Apotek

Package yang terdiri dari 2 procedure dengan nama :

delete_golongan_obat, dan insert golongan_obat

Lampiran Script Implementasi

SQL SERVER 2000 ENTERPRISE

Script pembuatan Database : USE MASTER

GO

if EXISTS( select CATALOG_NAME FROM INFORMATION_SCHEMA.SCHEMATA

WHERE CATALOG_NAME='APOTEK')

DROP DATABASE APOTEK

GO

CREATE DATABASE APOTEK

GO

USE APOTEK

GO

if exists (select 1

from sysobjects

where id = object_id('BENTUK_OBAT')

and type = 'U')

drop table BENTUK_OBAT

go

if exists (select 1

from sysobjects

where id = object_id('DETAIL_PEMBELIAN')

and type = 'U')

drop table DETAIL_PEMBELIAN

go

if exists (select 1

from sysobjects

where id = object_id('DETAIL_PENJUALAN')

and type = 'U')

drop table DETAIL_PENJUALAN

go

if exists (select 1

from sysobjects

where id = object_id('GOLONGAN_OBAT')

and type = 'U')

drop table GOLONGAN_OBAT

go

if exists (select 1

from sysobjects

where id = object_id('OBAT')

and type = 'U')

drop table OBAT

go

if exists (select 1

from sysobjects

where id = object_id('PEGAWAI')

and type = 'U')

drop table PEGAWAI

go

if exists (select 1

from sysobjects

where id = object_id('PELANGGAN')

and type = 'U')

drop table PELANGGAN

go

if exists (select 1

from sysobjects

where id = object_id('PEMBELIAN')

and type = 'U')

drop table PEMBELIAN

go

if exists (select 1

from sysobjects

where id = object_id('PENJUALAN')

and type = 'U')

drop table PENJUALAN

go

if exists (select 1

from sysobjects

where id = object_id('STOK_OBAT')

and type = 'U')

drop table STOK_OBAT

go

if exists (select 1

from sysobjects

where id = object_id('SUPPLIER')

and type = 'U')

drop table SUPPLIER

go

/*==============================================================*/

/* Table: BENTUK_OBAT */

/*==============================================================*/

create table BENTUK_OBAT (

ID_BENTUK_OBAT int not null,

BENTUK_OBAT varchar(25) not null,

KETERANGAN_BENTUK_OBAT varchar(50) null,

constraint PK_BENTUK_OBAT primary key nonclustered (ID_BENTUK_OBAT)

)

go

/*==============================================================*/

/* Table: DETAIL_PEMBELIAN */

/*==============================================================*/

create table DETAIL_PEMBELIAN (

NUM_PEMBELIAN int identity,

ID_PEMBELIAN varchar(10) not null,

ID_OBAT varchar(7) null,

JUMLAH_PEMBELIAN int not null,

HARGA_SATUAN money not null,

constraint PK_DETAIL_PEMBELIAN primary key nonclustered

(NUM_PEMBELIAN)

)

go

/*==============================================================*/

/* Table: DETAIL_PENJUALAN */

/*==============================================================*/

create table DETAIL_PENJUALAN (

NUM_PENJUALAN int identity,

ID_PENJUALAN varchar(10) not null,


JUMLAH_BARANG int not null,

HARGA_SATUAN money not null,

constraint PK_DETAIL_PENJUALAN primary key nonclustered

(NUM_PENJUALAN)

)

go

/*==============================================================*/

/* Table: GOLONGAN_OBAT */

/*==============================================================*/

create table GOLONGAN_OBAT (

ID_GOLONGAN int not null,

NAMA_GOLONGAN varchar(50) null,

KETERANGAN_GOLONGAN varchar(59) null,

constraint PK_GOLONGAN_OBAT primary key nonclustered (ID_GOLONGAN)

)

go

/*==============================================================*/

/* Table: OBAT */

/*==============================================================*/

create table OBAT (

ID_OBAT varchar(7) not null,

ID_BENTUK_OBAT int not null,

ID_GOLONGAN int not null,

NAMA_OBAT varchar(50) not null,

KHASIAT_OBAT varchar(50) null,

HARGA_JUAL int not null,

SATUAN varchar(25) null,

STATUS_OBAT int not null,

KADALUARSA datetime null,

KODE_PATEN varchar(25) null,

KODE_GENERIK varchar(25) null,

constraint PK_OBAT primary key nonclustered (ID_OBAT)

)

go

/*==============================================================*/

/* Table: PEGAWAI */

/*==============================================================*/

create table PEGAWAI (

ID_PEGAWAI varchar(5) not null,

NAMA_PEGAWAI varchar(50) null,

ALAMAT_PEGAWAI varchar(50) null,

NO_TELPON_PEGAWAI varchar(15) null,

TGL_MASUK_PEGAWAI datetime null,

GAJI_TETAP int null,

GAJI_HARIAN int null,

constraint PK_PEGAWAI primary key nonclustered (ID_PEGAWAI)

)

go

/*==============================================================*/

/* Table: PELANGGAN */

/*==============================================================*/

create table PELANGGAN (

ID_PELANGGAN varchar(10) not null,

NAMA_PELANGGAN varchar(25) not null,

ALAMAT_PELANGGAN varchar(50) null,

NO_TELPON_PELANGGAN varchar(15) null,

constraint PK_PELANGGAN primary key nonclustered (ID_PELANGGAN)

)

go

/*==============================================================*/

/* Table: PEMBELIAN */

/*==============================================================*/

create table PEMBELIAN (

ID_PEMBELIAN varchar(10) not null,

ID_SUPPLIER varchar(3) not null,


TGL_PEMBELIAN datetime not null,

KETERANGAN_PEMBELIAN varchar(50) null,

constraint PK_PEMBELIAN primary key nonclustered (ID_PEMBELIAN)

)

go

/*==============================================================*/

/* Table: PENJUALAN */

/*==============================================================*/

create table PENJUALAN (

ID_PENJUALAN varchar(10) not null,


ID_PELANGGAN varchar(10) not null,

TGL_PENJUALAN datetime null,

TOTAL_HARGA_PENJUALAN money null,

KETERANGAN_PENJUALAN varchar(50) null,

constraint PK_PENJUALAN primary key nonclustered (ID_PENJUALAN)

)

go

/*==============================================================*/

/* Table: STOK_OBAT */

/*==============================================================*/

create table STOK_OBAT (

NUMBERING int identity,


JUMLAH_STOK int not null,

constraint PK_STOK_OBAT primary key nonclustered (NUMBERING)

)

go

/*==============================================================*/

/* Table: SUPPLIER */

/*==============================================================*/

create table SUPPLIER (

ID_SUPPLIER varchar(3) not null,

NAMA_SUPPLIER varchar(50) null,

ALAMAT_SUPPLIER varchar(50) null,

NO_TELPON_SUPPLIER varchar(15) null,

constraint PK_SUPPLIER primary key nonclustered (ID_SUPPLIER)

)

go

Stored Procedure

--@sp_contoh_database

USE APOTEK

IF EXISTS (SELECT name FROM sysobjects Where name='sp_contoh_database'

AND type='P')

DROP PROCEDURE sp_contoh_database

GO

Create Procedure sp_contoh_database

As

--tabel bentuk Obat

Insert into Bentuk_Obat Values(1,'Pulvis','')

Insert into Bentuk_Obat Values(2,'Pulveres','')

Insert into Bentuk_Obat Values(3,'Tablet','')

Insert into Bentuk_Obat Values(4,'Pil','')

Insert into Bentuk_Obat Values(5,'Kapsul','')

Insert into Bentuk_Obat Values(6,'Kaplet','')

Insert into Bentuk_Obat Values(7,'Larutan','')

Insert into Bentuk_Obat Values(8,'Suspensi','')

Insert into Bentuk_Obat Values(9,'Emulsi','')

Insert into Bentuk_Obat Values(10,'Galenik','')

Insert into Bentuk_Obat Values(11,'Ekstrak','')

Insert into Bentuk_Obat Values(12,'Infusa','')

Insert into Bentuk_Obat Values(13,'Imunoserum','')

Insert into Bentuk_Obat Values(14,'Salep','')

Insert into Bentuk_Obat Values(15,'Suppositoria','')

Insert into Bentuk_Obat Values(16,'Obat tetes','')

----------------------------------------------------------------

--tabel golongan obat

INSERT into GOLONGAN_OBAT Values(1,'OB','Obat Bebas')

INSERT into GOLONGAN_OBAT Values(2,'OBT','Obat Bebas Terbatas')

INSERT into GOLONGAN_OBAT Values(3,'OK','Obat Keras')

---------------------------------------------------------------

--Tabel Pegawai

--0XXXX (Tetap) 1XXXXX (Tidak Tetap)

INSERT into Pegawai

Values('00001','John Krasus','Jalan Dinoyo 3/5','081678902','03/11/2005',1250000,null)

INSERT into Pegawai

Values('00002','Shiva Saracehan','Jalan Diponegoro II/2','088332200','02/17/2005',1450000,null)

INSERT into Pegawai

Values('00003','Titik','Jalan Holahola 3/13','08967325','01/19/2005',1650000,null)

INSERT into Pegawai

Values('10001','Lola','Jalan Mentari II/3','08967334','01/02/2007',null,50000)

INSERT into Pegawai

Values('10002','CeceNi','Jalan Umum 3/12','082324511','08/01/2007',null,50000)

--TABEL Pelanggan

INSERT into Pelanggan

Values ('P00001','Nadia Aini','Jalan JemurSari','031-84990952')


Values ('P00002','Refi Syahmudin','Jalan SariJemur','031-8495351')


Values ('P00003','Bapak Trio','Jalan Tidar','081592939')


Values ('P00004','Ibu Tri','Jalan Mengganti','0819999')

-------------------------------------------------------------------------

------------------------

--TABEL SUPPLIER

INSERT into SUPPLIER

VALUES ('001','PT.Kcimia Farma','Jalan Tambungan Jakarta','021-567123')


VALUES ('002','PT.Cholrin Sun Jaya','Jalan Merak Medan','071-321001')


VALUES ('003','PT.Surya Indah Farma','Jalan Mendet Jakarta','021-567122')

--TABEL OBAT

--GENERIK

--

(ID_OBAT,ID_BENTUK,ID_GOLONGAN,NAMA_OBAT,KHASIAT_OBAT,HARGA_JUAL,SATUAN,STATUS,STATUS_

OBAT,

--KADALURASA,KODE_PATEN,KODE_GENERIK)

--GENERIK

INSERT into OBAT

VALUES('G000001',4,1,'Parasetamol','Demam,Nyeri',12000,'Kaplet',1,'04/30/2011',null,'GX1111')

INSERT into OBAT

VALUES('G000002',3,1,'Asetosal','Sakit Kepala',13000,'Kaplet',1,'02/1/2012',null,'GX11112')

INSERT into OBAT

VALUES('G000003',6,1,'Amoksisilin','Anti Infeksi',12000,'Sashet',1,'05/10/2011',null,'GX11113')

INSERT into OBAT

VALUES('G000004',5,1,'Ergotamin','Anti Migrain',22000,'Biji',1,null,null,'GX11114')

--PATEN(MEREK)

INSERT into OBAT

VALUES('P000001',5,1,'Ultra Flu','Anti Migrain',5000,'Sachet',1,null,'PX11112',null)

INSERT into OBAT

VALUES('P000002',5,1,'Incidal','Anti Alergi',2500,'Biji',1,null,'PX11112',null)

INSERT into OBAT

VALUES('P000003',5,1,'Komix','Batuk',7500,'Kaplet',1,null,'PX11113',null)

--TABEL PEMBELIAN

--pembelian#1

INSERT into PEMBELIAN VALUES('BU-00-0001','001','00001','3/10/2011','Pembelian cash')

--TABEL DETAIL PEMBELIAN

INSERT into DETAIL_PEMBELIAN (ID_PEMBELIAN,ID_OBAT,JUMLAH_PEMBELIAN,HARGA_SATUAN)

VALUES ('BU-00-0001','G000001',100,10000)


VALUES ('BU-00-0001','G000002',50,12000)


VALUES ('BU-00-0001','P000001',75,4000)

--Penambahan stok OBAT (contoh AWAL)

INSERT into STOK_OBAT (ID_OBAT,JUMLAH_STOK)

VALUES('G000001',100)


VALUES('G000002',50)


VALUES('P000001',75)

GO

--@sp_delete_all_record

USE APOTEK

IF EXISTS (SELECT name FROM sysobjects Where name='sp_delete_all_record' AND type='P')

DROP PROCEDURE sp_delete_all_record

GO

Create Procedure sp_delete_all_record

As

DELETE From Bentuk_Obat

DELETE From Detail_Pembelian

DELETE From Detail_Penjualan

DELETE From Golongan_Obat

DELETE From Obat

DELETE From Pegawai

DELETE From Pelanggan

DELETE From Pembelian

DELETE From Penjualan

DELETE From Stok_Obat

DELETE From Supplier

GO

Function

IF EXISTS (SELECT name FROM sysobjects

WHERE name='fn_gaji_tetap' and type='FN')

DROP FUNCTION fn_gaji_tetap

GO

CREATE FUNCTION fn_gaji_tetap(@ID_PEGAWAI varchar(5))

RETURNS int

AS

BEGIN

DECLARE @nilai int

SET @nilai=(SELECT GAJI_TETAP FROM PEGAWAI WHERE ID_PEGAWAI=@ID_PEGAWAI)

RETURN @nilai

END

GO


WHERE name='fn_kerja_tahun' and type='FN')

DROP FUNCTION fn_kerja_tahun

GO

CREATE FUNCTION fn_kerja_tahun(@ID_PEGAWAI varchar(5),@date_now datetime)

RETURNS int

AS

BEGIN

DECLARE @tahun1 int,@tahun2 int,@tgl datetime,@hari float

DECLARE @bulan1 int,@bulan2 int,@hari1 int,@hari2 int

SET @tgl=(SELECT TGL_MASUK_PEGAWAI FROM PEGAWAI WHERE ID_PEGAWAI=@ID_PEGAWAI)

SET @tahun1=DATEPART(year,@tgl)

SET @bulan1=DATEPART(month,@tgl)

SET @hari1=DATEPART(day,@tgl)

SET @tahun2=DATEPART(year,@date_now)

SET @bulan2=DATEPART(month,@date_now)

SET @hari2=DATEPART(day,@date_now)

IF @tahun2 <= @tahun1

SET @hari=0

ELSE

IF @bulan2 > @bulan1

SET @hari=@tahun2-@tahun1

ELSE

SET @hari=(@tahun2-@tahun1)-1

RETURN @hari

END

GO


WHERE name='fn_kerja_bulan' and type='FN')

DROP FUNCTION fn_kerja_bulan

GO

CREATE FUNCTION fn_kerja_bulan(@ID_PEGAWAI varchar(5),@date_now datetime)

RETURNS int

AS

BEGIN

DECLARE @tahun1 int,@tahun2 int,@tgl datetime,@hari float










SET @hari=0

ELSE

IF @bulan2 > @bulan1

If @hari2 >=@hari1

SET @hari=@bulan2-@bulan1

ELSE

SET @hari=(@bulan2-@bulan1)-1

--SET @hari=@tahun2-@tahun1

ELSE

IF @hari2 >= @hari1

SET @hari=(@tahun1+12)-@tahun2

ELSE

SET @hari=(@tahun1+12)-@tahun2-1

RETURN @hari

END

GO


WHERE name='fn_kerja_hari' and type='FN')

DROP FUNCTION fn_kerja_hari

GO

CREATE FUNCTION fn_kerja_hari(@ID_PEGAWAI varchar(5),@date_now datetime)

RETURNS int

AS

BEGIN

DECLARE @tahun1 int,@tahun2 int,@tgl datetime,@hari int










SET @hari=0

ELSE

IF @hari2 >= @hari1

SET @hari=@hari2-@hari1

ELSE

SET @hari=(@hari2+30)-@hari1

RETURN @hari

END

GO

TRIGGER

'tg_update_stokObat'

IF EXISTS(SELECT name FROM sysobjects

WHERE name='tg_update_stokObat' AND type='TR')

DROP TRIGGER tg_update_stokObat

GO

Create TRIGGER tg_update_stokObat on DETAIL_PENJUALAN

FOR INSERT

As

UPDATE STOK_OBAT set stok_obat.jumlah_stok=STOK_OBAT.Jumlah_stok - DETAIL_PENJUALAN.jumlah_barang

From Stok_Obat join inserted DETAIL_PENJUALAN on STOK_OBAT.id_obat=DETAIL_PENJUALAN.id_obat

GO

--trigger tambah stok akibat pembelian

'tg_insert_StokObat'

IF EXISTS(SELECT name FROM sysobjects

WHERE name='tg_insert_StokObat' AND type='TR')

DROP TRIGGER tg_insert_StokObat

GO

Create TRIGGER tg_insert_StokObat on DETAIL_PEMBELIAN

FOR INSERT

As

DECLARE @id varchar(10),@jumlah int,@record int

SELECT @id= id_obat from inserted

SELECT @jumlah=jumlah_pembelian from inserted

IF NOT EXISTS (SELECT ID_OBAT from STOK_OBAT WHERE ID_OBAT=@id)

BEGIN

PRINT 'Record BELUM ada'

PRINT @id + ' ' + cast(@jumlah as varchar(10))


VALUES(@id,@jumlah)

END

ELSE

BEGIN

PRINT 'Record SUDAH ada'

UPDATE STOK_OBAT SET jumlah_stok=jumlah_stok+detail_pembelian.jumlah_pembelian

FROM STOK_OBAT join inserted DETAIL_PEMBELIAN on STOK_OBAT.id_obat=DETAIL_PEMBELIAN.id_obat

END

GO

/*

View pendukungan

*/

--VIEW OBAT

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_NAME='view_obat')

DROP VIEW view_obat

GO

CREATE VIEW view_obat

AS

SELECT OBAT.ID_OBAT,OBAT.ID_BENTUK_OBAT,BENTUK_OBAT,OBAT.ID_GOLONGAN,NAMA_GOLONGAN,

NAMA_OBAT,KHASIAT_OBAT,HARGA_JUAL,SATUAN,STATUS_OBAT,KODE_PATEN,KODE_GENERIK,CONVERT

(varchar,KADALUARSA,103)as KADALUARSA,JUMLAH_STOK

FROM OBAT inner join BENTUK_OBAT on OBAT.ID_BENTUK_OBAT=BENTUK_OBAT.ID_BENTUK_OBAT join

GOLONGAN_OBAT on OBAT.ID_GOLONGAN=GOLONGAN_OBAT.ID_GOLONGAN join STOK_OBAT on

OBAT.ID_OBAT=STOK_OBAT.ID_OBAT

GO

--

--VIEW PEGAWAI

IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS

WHERE TABLE_NAME='view_pegawai')

DROP VIEW view_pegawai

GO

CREATE VIEW view_pegawai

AS

SELECT ID_PEGAWAI,NAMA_PEGAWAI,ALAMAT_PEGAWAI,GAJI_TETAP,

CONVERT(varchar,TGL_MASUK_PEGAWAI,103) as TANGGAL_MASUK,

cast(dbo.fn_kerja_tahun(ID_PEGAWAI,getdate())as varchar) + ' Tahun' + ' ' +

cast(dbo.fn_kerja_bulan(ID_PEGAWAI,getdate())as varchar) + ' Bulan' +' '+

cast(dbo.fn_kerja_hari(ID_PEGAWAI,getdate())as varchar) + ' Hari' As Lama_Kerja

from pegawai

GO

//Catatanscript demo implementasi

exec sp_contoh_database

insert into detail_pembelian

values('px11','G000001',99,50000)

insert into detail_penjualan

values('gx11','G000001',16,50333)

delete detail_pembelian where id_pembelian='px11'

delete stok_obat where ID_OBAT='G000003'

select * from detail_pembelian

select * from stok_obat

select * from view_pegawai

ORACLE 10g ENTERPRISE

Script pembuatan tabel & sequence

drop table BENTUK_OBAT cascade constraints;

drop table DETAIL_PEMBELIAN cascade constraints;

drop table DETAIL_PENJUALAN cascade constraints;

drop table GOLONGAN_OBAT cascade constraints;

drop table OBAT cascade constraints;

drop table PEGAWAI cascade constraints;

drop table PELANGGAN cascade constraints;

drop table PEMBELIAN cascade constraints;

drop table PENJUALAN cascade constraints;

drop table STOK_OBAT cascade constraints;

drop table SUPPLIER cascade constraints;

--drop sequence

drop sequence sq_detail_pembelian;

drop sequence sq_detail_penjualan;

drop sequence sq_stok_obat;

/*==============================================================*/

/* Table: BENTUK_OBAT */

/*==============================================================*/

create table BENTUK_OBAT (

ID_BENTUK_OBAT INTEGER not null,

BENTUK_OBAT VARCHAR2(25) not null,

KETERANGAN_BENTUK_OBAT VARCHAR2(50),

constraint PK_BENTUK_OBAT primary key (ID_BENTUK_OBAT)

);

/*==============================================================*/

/* Table: DETAIL_PEMBELIAN */

/*==============================================================*/

create table DETAIL_PEMBELIAN (

NUM_PEMBELIAN INTEGER not null,

ID_PEMBELIAN VARCHAR2(10) not null,

ID_OBAT VARCHAR2(7),

JUMLAH_PEMBELIAN INTEGER not null,

HARGA_SATUAN NUMBER(8,2) not null,

constraint PK_DETAIL_PEMBELIAN primary key (NUM_PEMBELIAN)

);

--autoincrement

create sequence sq_detail_pembelian

start with 1

increment by 1

nomaxvalue;

/*==============================================================*/

/* Table: DETAIL_PENJUALAN */

/*==============================================================*/

create table DETAIL_PENJUALAN (

NUM_PENJUALAN INTEGER not null,

ID_PENJUALAN VARCHAR2(10) not null,


JUMLAH_BARANG INTEGER not null,

HARGA_SATUAN NUMBER(8,2) not null,

constraint PK_DETAIL_PENJUALAN primary key (NUM_PENJUALAN)

);

--autoincrement

create sequence sq_detail_penjualan

start with 1

increment by 1

nomaxvalue;

/*==============================================================*/

/* Table: GOLONGAN_OBAT */

/*==============================================================*/

create table GOLONGAN_OBAT (

ID_GOLONGAN INTEGER not null,

NAMA_GOLONGAN VARCHAR2(50),

KETERANGAN_GOLONGAN VARCHAR2(59),

constraint PK_GOLONGAN_OBAT primary key (ID_GOLONGAN)

);

/*==============================================================*/

/* Table: OBAT */

/*==============================================================*/

create table OBAT (

ID_OBAT VARCHAR2(7) not null,

ID_BENTUK_OBAT INTEGER not null,

ID_GOLONGAN INTEGER not null,

NAMA_OBAT VARCHAR2(50) not null,

KHASIAT_OBAT VARCHAR2(50),

HARGA_JUAL INTEGER not null,

SATUAN VARCHAR2(25),

STATUS_OBAT INTEGER not null,

KADALUARSA DATE,

KODE_PATEN VARCHAR2(25),

KODE_GENERIK VARCHAR2(25),

constraint PK_OBAT primary key (ID_OBAT)

);

/*==============================================================*/

/* Table: PEGAWAI */

/*==============================================================*/

create table PEGAWAI (

ID_PEGAWAI VARCHAR2(5) not null,

NAMA_PEGAWAI VARCHAR2(50),

ALAMAT_PEGAWAI VARCHAR2(50),

NO_TELPON_PEGAWAI VARCHAR2(15),

TGL_MASUK_PEGAWAI DATE,

GAJI_TETAP INTEGER,

GAJI_HARIAN INTEGER,

constraint PK_PEGAWAI primary key (ID_PEGAWAI)

);

/*==============================================================*/

/* Table: PELANGGAN */

/*==============================================================*/

create table PELANGGAN (

ID_PELANGGAN VARCHAR2(10) not null,

NAMA_PELANGGAN VARCHAR2(25) not null,

ALAMAT_PELANGGAN VARCHAR2(50),

NO_TELPON_PELANGGAN VARCHAR2(15),

constraint PK_PELANGGAN primary key (ID_PELANGGAN)

);

/*==============================================================*/

/* Table: PEMBELIAN */

/*==============================================================*/

create table PEMBELIAN (

ID_PEMBELIAN VARCHAR2(10) not null,

ID_SUPPLIER VARCHAR2(3) not null,


TGL_PEMBELIAN DATE not null,

KETERANGAN_PEMBELIAN VARCHAR2(50),

constraint PK_PEMBELIAN primary key (ID_PEMBELIAN)

);

/*==============================================================*/

/* Table: PENJUALAN */

/*==============================================================*/

create table PENJUALAN (

ID_PENJUALAN VARCHAR2(10) not null,


ID_PELANGGAN VARCHAR2(10) not null,

TGL_PENJUALAN DATE,

TOTAL_HARGA_PENJUALAN NUMBER(8,2),

KETERANGAN_PENJUALAN VARCHAR2(50),

constraint PK_PENJUALAN primary key (ID_PENJUALAN)

);

/*==============================================================*/

/* Table: STOK_OBAT */

/*==============================================================*/

create table STOK_OBAT (

NUMBERING INTEGER not null,


JUMLAH_STOK INTEGER not null,

constraint PK_STOK_OBAT primary key (NUMBERING)

);

--autoincrement

create sequence sq_stok_obat

start with 1

increment by 1

nomaxvalue;

/*==============================================================*/

/* Table: SUPPLIER */

/*==============================================================*/

create table SUPPLIER (

ID_SUPPLIER VARCHAR2(3) not null,

NAMA_SUPPLIER VARCHAR2(50),

ALAMAT_SUPPLIER VARCHAR2(50),

NO_TELPON_SUPPLIER VARCHAR2(15),

constraint PK_SUPPLIER primary key (ID_SUPPLIER)

);

------------------------------------------------------------------------

--VIEW

CREATE or REPLACE VIEW VIEW_OBAT

AS

SELECT

OBAT.ID_OBAT,OBAT.ID_BENTUK_OBAT,BENTUK_OBAT,OBAT.ID_GOLONGAN,NAMA_GOLONG

AN,

NAMA_OBAT,KHASIAT_OBAT,HARGA_JUAL,SATUAN,STATUS_OBAT,KODE_PATEN,KODE_GEN

ERIK,

JUMLAH_STOK

FROM OBAT inner join BENTUK_OBAT on

OBAT.ID_BENTUK_OBAT=BENTUK_OBAT.ID_BENTUK_OBAT join

GOLONGAN_OBAT on OBAT.ID_GOLONGAN=GOLONGAN_OBAT.ID_GOLONGAN join

STOK_OBAT on OBAT.ID_OBAT=STOK_OBAT.ID_OBAT;

CREATE or REPLACE VIEW "view_pegawai"

AS

SELECT ID_PEGAWAI,NAMA_PEGAWAI,ALAMAT_PEGAWAI,GAJI_TETAP,

to_char(TGL_MASUK_PEGAWAI,'dd/mm/yyyy') as TANGGAL_MASUK,

CAST("fn_kerja_tahun"(ID_PEGAWAI, to_date(sysdate,'dd/mm/yyyy')) as VARCHAR2(50)) || ' Tahun ' ||

CAST("fn_kerja_bulan"(ID_PEGAWAI, to_date(sysdate,'dd/mm/yyyy')) as VARCHAR2(50)) || ' Bulan ' ||

CAST("fn_kerja_hari"(ID_PEGAWAI, to_date(sysdate,'dd/mm/yyyy')) as VARCHAR2(50)) || ' Hari '

As Lama_Kerja

from pegawai;

Stored Procedure

SP_CONTOH_DATABASE

CREATE OR REPLACE Procedure SP_CONTOH_DATABASE

IS

BEGIN

--tabel bentuk Obat

Insert into Bentuk_Obat Values(1,'Pulvis','');

Insert into Bentuk_Obat Values(2,'Pulveres','');

Insert into Bentuk_Obat Values(3,'Tablet','');

Insert into Bentuk_Obat Values(4,'Pil','');

Insert into Bentuk_Obat Values(5,'Kapsul','');

Insert into Bentuk_Obat Values(6,'Kaplet','');

Insert into Bentuk_Obat Values(7,'Larutan','');

Insert into Bentuk_Obat Values(8,'Suspensi','');

Insert into Bentuk_Obat Values(9,'Emulsi','');

Insert into Bentuk_Obat Values(10,'Galenik','');

Insert into Bentuk_Obat Values(11,'Ekstrak','');

Insert into Bentuk_Obat Values(12,'Infusa','');

Insert into Bentuk_Obat Values(13,'Imunoserum','');

Insert into Bentuk_Obat Values(14,'Salep','');

Insert into Bentuk_Obat Values(15,'Suppositoria','');

Insert into Bentuk_Obat Values(16,'Obat tetes','');

--tabel golongan obat

INSERT into GOLONGAN_OBAT Values(1,'OB','Obat Bebas');

INSERT into GOLONGAN_OBAT Values(2,'OBT','Obat Bebas Terbatas');

INSERT into GOLONGAN_OBAT Values(3,'OK','Obat Keras');

---------------------------------------------------------------

--Tabel Pegawai

--0XXXX (Tetap) 1XXXXX (Tidak Tetap)

INSERT into Pegawai

Values('00001','John Krasus','Jalan Dinoyo 3/5','081678902',"TO_DATE"('03/11/2005',

'mm/dd/yyyy'),1250000,null);

INSERT into Pegawai

Values('00002','Shiva Saracehan','Jalan Diponegoro II/2','088332200',"TO_DATE"('02/17/2005',

'mm/dd/yyyy'),1450000,null);

INSERT into Pegawai

Values('00003','Titik','Jalan Holahola 3/13','08967325',"TO_DATE"('01/19/2005', 'mm/dd/yyyy'),1650000,null);

INSERT into Pegawai

Values('10001','Lola','Jalan Mentari II/3','08967334',"TO_DATE"('01/02/2007', 'mm/dd/yyyy'),null,50000);

INSERT into Pegawai

Values('10002','CeceNi','Jalan Umum 3/12','082324511',"TO_DATE"('08/01/2007', 'mm/dd/yyyy'),null,50000);

-------------------------------------------------------------------------

--TABEL Pelanggan


Values ('P00001','Nadia Aini','Jalan JemurSari','031-84990952');


Values ('P00002','Refi Syahmudin','Jalan SariJemur','031-8495351');


Values ('P00003','Bapak Trio','Jalan Tidar','081592939');


Values ('P00004','Ibu Tri','Jalan Mengganti','0819999');

-------------------------------------------------------------------------

--TABEL SUPPLIER


VALUES ('001','PT.Kcimia Farma','Jalan Tambungan Jakarta','021-567123');


VALUES ('002','PT.Cholrin Sun Jaya','Jalan Merak Medan','071-321001');


VALUES ('003','PT.Surya Indah Farma','Jalan Mendet Jakarta','021-567122');

-------------------------------------------------------------------------

--TABEL OBAT

--GENERIK

--

(ID_OBAT,ID_BENTUK,ID_GOLONGAN,NAMA_OBAT,KHASIAT_OBAT,HARGA_JUAL,SATUAN,ST

ATUS,STATUS_OBAT,

--KADALURASA,KODE_PATEN,KODE_GENERIK)

--GENERIK (to_date('12/03/2006','mm/dd/yyyy'));

INSERT into OBAT

VALUES('G000001',4,1,'Parasetamol','Demam,Nyeri',12000,'Kaplet',1,"TO_DATE"('30/04/2011',

'dd/mm/yyyy'),null,'GX1111');

INSERT into OBAT

VALUES('G000002',3,1,'Asetosal','Sakit Kepala',13000,'Kaplet',1,"TO_DATE"('02/1/2012', 'mm/dd/yyyy')

,null,'GX11112');

INSERT into OBAT

VALUES('G000003',6,1,'Amoksisilin','Anti Infeksi',12000,'Sashet',1,"TO_DATE"('05/10/2011',

'mm/dd/yyyy'),null,'GX11113');

INSERT into OBAT

VALUES('G000004',5,1,'Ergotamin','Anti Migrain',22000,'Biji',1,null,null,'GX11114');

--PATEN(MEREK)

INSERT into OBAT

VALUES('P000001',5,1,'Ultra Flu','Anti Migrain',5000,'Sachet',1,null,'PX11112',null);

INSERT into OBAT

VALUES('P000002',5,1,'Incidal','Anti Alergi',2500,'Biji',1,null,'PX11112',null);

INSERT into OBAT

VALUES('P000003',5,1,'Komix','Batuk',7500,'Kaplet',1,null,'PX11113',null);

-------------------------------------------------------------------------

--TABEL PEMBELIAN

--pembelian#1

INSERT into PEMBELIAN VALUES('BU-00-0001','001','00001',"TO_DATE"('3/10/2011',

'mm/dd/yyyy'),'Pembelian cash');

--TABEL DETAIL PEMBELIAN

INSERT into DETAIL_PEMBELIAN

(NUM_PEMBELIAN,ID_PEMBELIAN,ID_OBAT,JUMLAH_PEMBELIAN,HARGA_SATUAN)

VALUES (SQ_DETAIL_PEMBELIAN.nextval, 'BU-00-0001','G000001',100,10000);



VALUES (SQ_DETAIL_PEMBELIAN.nextval,'BU-00-0001','G000002',50,12000);



VALUES (SQ_DETAIL_PEMBELIAN.nextval,'BU-00-0001','P000001',75,4000);

--Penambahan stok OBAT (contoh AWAL)

INSERT into STOK_OBAT (NUMBERING,ID_OBAT,JUMLAH_STOK)

VALUES(SQ_STOK_OBAT.NEXTVAL,'G000001',100);


VALUES(SQ_STOK_OBAT.NEXTVAL,'G000002',50);


VALUES(SQ_STOK_OBAT.NEXTVAL,'P000001',75);

END;

SP_DELETE_ALL_RECORD

CREATE OR REPLACE Procedure sp_delete_all_record

IS

BEGIN

DELETE From Bentuk_Obat;

DELETE From Detail_Pembelian;

DELETE From Detail_Penjualan;

DELETE From Golongan_Obat;

DELETE From Obat;

DELETE From Pegawai;

DELETE From Pelanggan;

DELETE From Pembelian;

DELETE From Penjualan;

DELETE From Stok_Obat;

DELETE From Supplier;

END;

Function

FN_KERJA_TAHUN

CREATE OR REPLACE

FUNCTION "fn_kerja_tahun" (id_pegawai IN VARCHAR2, date_now IN DATE)

RETURN int

AS

tahun1 int;

tahun2 int;

tgl DATE;

hari INT;

bulan1 int;

bulan2 int;

hari1 int;

hari2 int;

id_nya VARCHAR2(20):=id_pegawai;

BEGIN

SELECT TGL_MASUK_PEGAWAI into tgl FROM PEGAWAI WHERE ID_PEGAWAI=id_nya;

tahun1:=to_char(tgl,'YYYY');

bulan1:=to_char(tgl,'mm');

hari1:=to_char(tgl,'dd');

tahun2:=to_char(date_now,'YYYY');

bulan2:=to_char(date_now,'mm');

hari2:=to_char(date_now,'DD');

IF (tahun2 <= tahun1) THEN

hari:=0;

ELSE

IF (bulan2 > bulan1) THEN

hari:=tahun2-tahun1;

ELSE

hari:=(tahun2-tahun1)-1;

END IF;

END IF;

RETURN hari;

END;

FN_KEJA_BULAN

CREATE OR REPLACE

FUNCTION "fn_kerja_bulan" (id_pegawai IN VARCHAR2, date_now IN DATE)

RETURN int

AS

tahun1 int;

tahun2 int;

tgl DATE;

hari INT;

bulan1 int;

bulan2 int;

hari1 int;

hari2 int;


BEGIN









hari:=0;

ELSE

IF (bulan2 > bulan1) THEN

IF (hari2 > hari1) THEN

hari:=bulan2-bulan1;

ELSE

hari:=(bulan2-bulan1)-1;

END IF;

ELSE

IF (hari2 >= hari1) THEN

hari:=(tahun1+12)-tahun2;

ELSE

hari:=(tahun1+12)-tahun2-1;

END IF;

--hari:=(tahun2-tahun1)-1;

END IF;

END IF;

RETURN hari;

END;

FN_KERJA_HARI

CREATE OR REPLACE

FUNCTION "fn_kerja_hari" (id_pegawai IN VARCHAR2, date_now IN DATE)

RETURN int

AS

tahun1 int;

tahun2 int;

tgl DATE;

hari INT;

bulan1 int;

bulan2 int;

hari1 int;

hari2 int;


BEGIN









hari:=0;

ELSE

IF (hari2 >= hari1) THEN

hari:=hari2-hari1;

ELSE

hari:=(hari2+30)-hari1;

END IF;

END IF;

RETURN hari;

END;

TRIGGER

TG_UPDATE_STOKOBAT

CREATE OR REPLACE TRIGGER "ERFAN"."tg_update_stokobat" AFTER INSERT ON

"ERFAN"."DETAIL_PENJUALAN" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW

BEGIN

UPDATE STOK_OBAT SET JUMLAH_STOK=JUMLAH_STOK-:new.JUMLAH_BARANG;

END;

ALTER TRIGGER "ERFAN"."tg_update_stokobat" ENABLE

TG_INSERT_STOKOBAT

CREATE OR REPLACE TRIGGER "ERFAN"."tg_insert_stokobat" AFTER INSERT ON

"ERFAN"."DETAIL_PEMBELIAN" REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW

DECLARE

cek number;

BEGIN

SELECT COUNT(*) into CEK FROM STOK_OBAT

WHERE STOK_OBAT.ID_OBAT=:NEW.ID_OBAT;

IF cek=0 THEN

INSERT into STOK_OBAT

VALUES(SQ_STOK_OBAT.NEXTVAL,:NEW.ID_OBAT,:NEW.JUMLAH_PEMBELIAN);

ELSE

UPDATE STOK_OBAT SET JUMLAH_STOK=JUMLAH_STOK+:NEW.JUMLAH_PEMBELIAN

WHERE STOK_OBAT.ID_OBAT=:NEW.ID_OBAT;

END IF;

END;

ALTER TRIGGER "ERFAN"."tg_insert_stokobat" ENABLE

PACKAGE

PK_APOTEK

CREATE OR REPLACE

PACKAGE pk_apotek AS

PROCEDURE insert_golongan_obat

(

id_golongan NUMBER,

nama_gol VARCHAR2,

ket_gol VARCHAR2

);

PROCEDURE delete_golongan_obat

(

nama_gol VARCHAR2

);

end;

CREATE OR REPLACE

PACKAGE BODY "PK_APOTEK" AS

PROCEDURE DELETE_GOLONGAN_OBAT(NAMA_GOL IN VARCHAR2) AS

BEGIN

DELETE FROM GOLONGAN_OBAT

WHERE NAMA_GOLONGAN=DELETE_GOLONGAN_OBAT.NAMA_GOL;

END;

PROCEDURE INSERT_GOLONGAN_OBAT(ID_GOLONGAN IN NUMBER, NAMA_GOL IN

VARCHAR2, KET_GOL IN VARCHAR2) AS

BEGIN

INSERT INTO GOLONGAN_OBAT

VALUES(ID_GOLONGAN,NAMA_GOL,KET_GOL);

END;

END;

Kritik & Saran :

1. Lusi Susanti ( [email protected] )

2. Erfan Rachmad S ( [email protected] )

mailto:[email protected]

mailto:[email protected]

Basis Data Lanjut 1.5

Documents

Transcript of Basis Data Lanjut 1.5