Kelir…

09/05/2008

Database apotik (praktikum PBD)

Filed under: share aja... — mrtosa @ 10:52

Iseng2 gw posting nih, query buat desain database apotik praktikum gw…nih desain gw buat dalam rangka ngerjain praktikum Basis data gw.Nih query bisa di pke di Oracle10g,9g dll…bisa di praktekin di power designer juga ko…klo udh tau mana primary key ama foreign keynya…Moga aja bermanfaat klo lu baca…n thx udh baca

create table jabatan(
id_jabatan char(8),
nama_jabatan varchar2(10),
gaji_per_shift number (6),
deskripsi_jabatan varchar2(100)
);
alter table jabatan add constraint pk_jabatan primary key(id_jabatan);

create table pegawai(
id_pegawai char(8),
nama_pegawai varchar2(30),
alamat_pegawai varchar2(50),
notelp_pegawai char(15),
password varchar2(50)
);
alter table pegawai add constraint pk_pegawai primary key(id_pegawai);
alter table pegawai add id_jabatan char(8);
alter table pegawai add constraint fk_pegawai_relations_jabatan foreign key(id_jabatan)
references jabatan(id_jabatan);

create table penggajian(
id_penggajian char(8),
id_pegawai char(8),
tgl_keluar_gaji date,
tgl_ambil_gaji date,
bonus number(8),
potongan number(8),
gaji_pokok number( 8 )
);
alter table penggajian add constraint pk_penggajian primary key(id_penggajian);
alter table penggajian add constraint fk_penggaji_relations_pegawai foreign key(id_pegawai)
references pegawai(id_pegawai);

create table absensi(
id_absensi char(8),
id_pegawai char(8),
tgl_absensi date,
keterangan_absensi varchar2(5)
);
alter table absensi add constraint pk_absensi primary key(id_absensi);
alter table absensi add constraint fk_absensi_relations_pegawai foreign key(id_pegawai)
references pegawai(id_pegawai);

create table shiftjaga(
id_shift char(8),
waktu_shift varchar2(11),
hari_shift varchar2(6)
);
alter table shiftjaga add constraint pk_shiftjaga primary key(id_shift);

create table penjualan(
id_penjualan char(8),
id_pegawai char(8),
tgl_penjualan date,
total_penjualan number(8,2),
pembeli varchar2(30)
);
alter table penjualan add constraint pk_penjualan primary key(id_penjualan);
alter table penjualan add constraint fk_penjualan_relations_pegawai foreign key(id_pegawai)
references pegawai(id_pegawai);

create table golonganobat(
kode_gol varchar2(3),
keterangan varchar2(50)
);
alter table golonganobat add constraint pk_golonganobat primary key(kode_gol);

create table obat(
id_obat char(8),
kode_gol varchar2(3),
nama_obat varchar2(30),
khasiat_obat varchar2(100),
harga_jual number(9,2)
);
alter table obat add constraint pk_obat primary key(id_obat);
alter table obat add constraint fk_obat_relations_golonganobat foreign key(kode_gol)
references golonganobat(kode_gol);

create table supplier(
id_supplier char(8),
nama_supplier varchar2(30),
alamat varchar2(50),
rekening varchar2(100)
);
alter table supplier add constraint pk_supplier primary key(id_supplier);

create table pembelian(
id_pembelian char(8),
id_supplier char(8),
id_pegawai char(8),
tanggal_pembelian date,
total_harga number(8,2)
);
alter table pembelian add constraint pk_pembelian primary key(id_pembelian);
alter table pembelian add constraint fk_pemb_relations_supplier foreign key(id_supplier)
references supplier(id_supplier);
alter table pembelian add constraint fk_pembelian_relations_pegawai foreign key(id_pegawai)
references pegawai(id_pegawai);

create table stockobat(
id_obat char(8),
kadaluarsa date,
jumlah_stock number(10)
);
alter table stockobat add constraint pk_stockobat primary key(id_obat, kadaluarsa);
alter table “STOCK_OBAT” add constraint fk_stockobat_relations_obat foreign key(id_obat)
references obat(id_obat);

create table rincianpembelian(
id_pembelian char(8),
seqbeli number(3),
id_obat char(8),
kadaluarsa date,
jumlah number(10),
harga_satuan number(9,2)
);
alter table rincianpembelian add constraint pk_rincianpembelian primary key(seqbeli);
alter table rincianpembelian add constraint fk_rincian_relations_pembelian foreign key(id_pembelian)
references pembelian(id_pembelian);
alter table rincianpembelian add constraint fk_rincian_relations_stockobat foreign key(id_obat, kadaluarsa)
references “STOCK_OBAT”(id_obat, kadaluarsa);

create table jadwaljaga(
id_pegawai char(8),
id_shift char( 8 )
);
alter table “JADWAL_JAGA” add constraint pk_jadwaljaga primary key(id_pegawai, id_shift);
alter table “JADWAL_JAGA” add constraint fk_jadwal_relations_pegawai foreign key(id_pegawai)
references pegawai(id_pegawai);
alter table “JADWAL_JAGA” add constraint fk_jadwal_relations_shift foreign key(id_shift)
references “SHIFT_JAGA”(id_shift);

create table rincianpenjualan(
id_penjualan char(8),
seqjual number(3),
id_obat char(8),
sto_id_obat char(8),
kadaluarsa date,
jumlah number(10),
hargasatuan number(9,2)
);
alter table “RINCIAN_PENJUALAN” add constraint pk_rincianpenjualan primary key(id_penjualan, seqjual);
alter table “RINCIAN_PENJUALAN” ADD CONSTRAINT fk_rincian_relations_penjualan foreign key(id_penjualan)
REFERENCES PENJUALAN(ID_PENJUALAN);
alter table “RINCIAN_PENJUALAN” ADD CONSTRAINT fk_rincian_relations_obat foreign key (id_obat)
references obat(id_obat);
alter table “RINCIAN_PENJUALAN” ADD CONSTRAINT fk_rincian_dijual_stockobat foreign key(sto_id_obat,kadaluarsa)
references “STOCK_OBAT”(id_obat, kadaluarsa);

11 Comments »

  1. Minta sourcenya opotik dong….

    Comment by Dieq — 21/05/2009 @ 11:02 | Reply

  2. iyah..btuh sourcenya..bsa krimin g?

    @devy : source apa’an maksudnya??desainnya ta??

    Comment by devy — 15/09/2009 @ 09:59 | Reply

  3. bSA mnta desainnya gk?

    Comment by Ivan — 14/01/2010 @ 12:29 | Reply

  4. mas. minta gambar relasi tabelnya donk.
    ntr klo ada kebingungan gw boleh tanya lagi ya..😀 thx

    Comment by isnin — 04/04/2010 @ 12:22 | Reply

    • ya ntar saya coba carikan relasi tabelnya…
      maaf baru bales, baru bisa nge net soalnya…ntar saya coba kirim ke email saudara…

      Comment by mrtosa — 08/04/2010 @ 05:58 | Reply

  5. ok mas saya tunggu. ^^

    Comment by isnin — 08/04/2010 @ 08:09 | Reply

  6. ya….ad source ny gak mas???
    krimin dong mas ????pleasee

    Comment by pendra — 22/03/2011 @ 02:54 | Reply

  7. sipp brow…. tp lbh sip lg lw d lengkapi ma flowcartnya skalian, biar lbh jelas kmn alur datanya kmn.
    lanjutkn brow.

    Comment by muhib — 15/11/2011 @ 18:21 | Reply

  8. terima kasihh mas atas ilmunya😀

    Comment by tyodongss — 06/12/2011 @ 12:45 | Reply

  9. relasinya klo aday

    Comment by tyo — 31/03/2012 @ 12:16 | Reply

  10. mas minta gmbar tabelnya beserta relasinya bisa gak?

    Comment by Azunk Nugroho Elnino — 25/06/2012 @ 14:32 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: