Gawe relasi nang mysql

November 19, 2008 at 1:06 pm 2 comments

Janjane gawe relasi nang mysql kue keprie sih ? jane aku dewek ya wis tahu jejel pas lagi jamane mysql anyaran metu nganggo versi 3, jan ndadak ngeset macem-macem, aku dewek ya maklum jenenge be gratis. Nek dibandingaken karo SQL server nggone Microsoft ya adoh wektu semono, nggawe relasi ya ora gampang, create view durung metu, replikasi ya durung support. Syukur alhamdulillah siki wis bisa kabeh. Apa sing dijanjikaken nang tutorial versi sedurunge wis ditepati nang ngaring pengembang-pengembange. Wah siki aku kari jejel bae lah. Tak jejel nggawe relasi sing study kasus sisan :

Conto DDL karo DMLe kaya kie :

Rencana database sing arep tak gawe ya kue database pembelian barang (rencana ya tekan penjualan) kie tak jejel tekan pembelian sit. Paling ora uji cobane dadi sit.

Critane aku gawe database sing isine ana 5 table

1. tuser = tak enggo nyimpen data user e

2. tbarang = nggo nyimpen data barang, kode, nama dan lain lain

3. tsuplier = nggo nyimpen data supplier, dadi genah gole tuku barang aring sapa ?

4. tnotabeli = nggo nyimpen data pembelian barang, sing intine sing disimpen ukur no notane, tukune supliere sapa ? karo kapan gole tuku ?

5. tpembelian = nggo nyimpen data sing ana kaitane karo table tnotabeli, maksude nek transaksine nonota ora nana nang aring table tnotabeli ya otomatis transaksi ora bisa mlebu nang table tpembelian

DDL dan DML nya sbb :

create database penjualan;

use penjualan;

CREATE TABLE `tuser` (

`user` varchar(15) NOT NULL,

`namaDepan` varchar(25) NOT NULL,

`namaBelakang` varchar(30) NOT NULL,

`JK` char(1) NOT NULL,

`bagian` varchar(20) NOT NULL,

`password` varchar(15) NOT NULL,

PRIMARY KEY (`user`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tuser` VALUES (‘Anwar’,’Anwar’,’Listianto’,’L’,’casir’,’hgs’);

INSERT INTO `tuser` VALUES (‘fitri’,’Fitri’,’Agustina’,’P’,’member’,’hgs’);

INSERT INTO `tuser` VALUES (‘imam’,’Imam’,’Makhfadi’,’L’,’admin’,’dpln’);

INSERT INTO `tuser` VALUES (‘Listiani’,’Listiani’,’Astuti’,’P’,’Casir’,’ecd’);

INSERT INTO `tuser` VALUES (‘yuli’,’Yuli’,’Listiani’,’P’,’casir’,’hgs’);

DDL karo DML nang duwur kue wis genah keton nek sing lanang alias L ana wong 2 sing P (Perempuan) ana 3.

Nek cek karo perintah select * from tuser metune kaya kie :

relasi1


tampilan nang duwur kue aku nganggo mysql front

Nah ben gampang, tur bisa otomatis ngerti lanange pira wadone pira ? ya gawe query. Nek nang acces ya query, nek nang mysql jenenge view. Nah DLLe kaya kie :

create view qjenisklm as SELECT tuser.jk, Count(tuser.jk) AS jumlahjk

FROM tuser GROUP BY tuser.jk;

Nek dijejel karo select * from qjenisklm hasile :

relasi2

Nah keton kan L ana 2 P ana 3

Nah kari diterusna maning nggawe table supplier DDL karo DML e :

CREATE TABLE `tsuplier` (

`kodesp` varchar(10) NOT NULL default ”,

`nama` varchar(30) default NULL,

`alamat` varchar(35) default NULL,

`telp` varchar(18) default NULL,

PRIMARY KEY (`kodesp`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tsuplier` VALUES (‘SP001′,’PT. Sinar Abadi’,’Jl. Janti No 60′,’0274-789765′);

INSERT INTO `tsuplier` VALUES (‘SP002′,’PT. Perkasa Jaya’,’Bulaksumur No. 23′,’0274-456782′);

Terus sekalian table barange :

CREATE TABLE `tbarang` (

`kodebrg` varchar(10) NOT NULL,

`namabrg` varchar(30) NOT NULL,

`satuan` varchar(20) NOT NULL,

`kategori` varchar(25) NOT NULL,

PRIMARY KEY (`kodebrg`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tbarang` VALUES (‘BRG001′,’Sabun GIV’,’buah’,’Sabun mandi’);

INSERT INTO `tbarang` VALUES (‘BRG002′,’Sabun Lifeboy’,’buah’,’Sabun mandi’);

INSERT INTO `tbarang` VALUES (‘BRG003′,’Soklin 1 Kg’,’buah’,’Sabun Cuci’);

INSERT INTO `tbarang` VALUES (‘BRG004′,’Rinso 1 Kg’,’buah’,’Sabun Cuci’);

Nah terusaken sisan ngawe table nggo nyimpen transaksine, nek dideleng formulire pembelian barang mestine kaya kie (contoh tok)

No Nota : ………………………….. Tanggal,………………

Supplier :……………………………

No

Kode Barang

Nama Barang

Satuan

QTY

Harga

Jumlah

Nek formulire kaya kue, logise berarti siji nomer nota ning barang sing dituku akeh, kepie jal rancangan table nang database e ? meh pada karo transaksi mbayar SPP ? bocah siji. Siji NIS e tapi bayare 12 wulan. (nek masalah kepriye ngrancange bisa diwaca maning lah nang buku perancangan database). Langsung bae tak gawe gisit table tnotabeli :

CREATE TABLE `tnotabeli` (

`nonota` varchar(15) NOT NULL,

`user` varchar(15) NOT NULL,

`kodesp` varchar(10) NOT NULL,

`tglbeli` date default NULL,

PRIMARY KEY (`nonota`),

KEY `FK_tnotabeli_tuser` (`user`),

KEY `FK_tnotabeli_tsuplier` (`kodesp`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Nek wis tekan kene kari jejel ngawe relasi sekalian, mestine transaski pembelian bisa diisi nek nonota ne ora kembar (wong wis digawe primary key) tur nek diisi kodesp ne ora nana nang tsuplier ya kudune ora gelem. Gampangane data supliere be urung dientry ko garep tuku, kan ya ora isa. Nek wis la bisa. Nah kie perlu direlasikna carane tambahna DLL e :

ALTER TABLE `tnotabeli`

ADD FOREIGN KEY (`kodesp`) REFERENCES `tsuplier` (`kodesp`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD FOREIGN KEY (`user`) REFERENCES `tuser` (`user`) ON DELETE CASCADE ON UPDATE CASCADE;

Nah kari di insert mlebu kan ? nek diinsert data sing ora bener kan nolak, nah kue hebate relasi.

INSERT INTO `tnotabeli` VALUES (‘B001′,’fitri’,’SP001′,’2008-01-12′);

INSERT INTO `tnotabeli` VALUES (‘B002′,’fitri’,’SP002′,’2008-01-13′);

Terusna maning, data sing tembe dientry nggo transaksi pembelian kan tembe nonota, user e sing tuku sapa ? kode supplier e, karo tanggal tuku ne ?

Kan durung genah sing dituku apa bae ? nah kie sing kudu digawe. DLL e :

CREATE TABLE `tpembelian` (

`nonota` varchar(15) NOT NULL,

`kodebrg` varchar(10) NOT NULL,

`harga` int(10) NOT NULL,

`jumlah` int(5) NOT NULL,

KEY `FK_tpembelian_tnotabeli` (`nonota`),

KEY `FK_tpembelian_tbarang` (`kodebrg`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

San gawe relasine :

ALTER TABLE `tpembelian`

ADD FOREIGN KEY (`kodebrg`) REFERENCES `tbarang` (`kodebrg`) ON DELETE CASCADE ON UPDATE CASCADE,

ADD FOREIGN KEY (`nonota`) REFERENCES `tnotabeli` (`nonota`) ON DELETE CASCADE ON UPDATE CASCADE;

Terus DML e :

INSERT INTO `tpembelian` VALUES (‘B001′,’BRG001’,1000,10);

INSERT INTO `tpembelian` VALUES (‘B001′,’BRG002’,1200,20);

INSERT INTO `tpembelian` VALUES (‘B001′,’BRG003’,1500,30);

INSERT INTO `tpembelian` VALUES (‘B001′,’BRG004’,1800,30);

INSERT INTO `tpembelian` VALUES (‘B002′,’BRG001’,1000,10);

INSERT INTO `tpembelian` VALUES (‘B002′,’BRG002’,1200,20);

INSERT INTO `tpembelian` VALUES (‘B002′,’BRG003’,1500,30);

INSERT INTO `tpembelian` VALUES (‘B002′,’BRG004’,1800,30);

Tak entry sisan datane ana 8 maksude ben genah ana 2 (loro) transaksi pembelian. Sing siji no notane B001 karo B002, sing dituku ya kue werna-werna. Nek dibayangaken menawa table tnotabeli karo tpembelian dadi siji mesti datane akeh sing dobel-dobel alias redudance.

Paling ora dadine kaya kie :

Nonota user kodesp tglbeli kodebrg harga jumlah

B001 fitri SP001 ‘2008-01-12’ BRG001 1000 10

B001 fitri SP001 ‘2008-01-12’ BRG002 1200 20

B001 fitri SP001 ‘2008-01-12’ BRG003 1500 30

B001 fitri SP001 ‘2008-01-12’ BRG004 1800 30

B002 fitri SP002 ‘2008-01-13’ BRG001 1000 10

B002 fitri SP002 ‘2008-01-13’ BRG002 1200 20

B002 fitri SP002 ‘2008-01-13’ BRG003 1500 30

B002 fitri SP002 ‘2008-01-13’ BRG004 1800 30

Tak cetak abang ben genah, bisa dibayangaken kok dobel-dobel ya ?

Mulane perlu dipecah maning tabele ? kaya sing nang duwur, ning ya kue kudu ana relasine.

Nah akhire limang table wis digawe, bisa dibuktikna mestine :

nota siji bisa nggo traksaksi tuku barang werna-werna,

no nota ora mungkin pada

kode supplier ya ora entuk pada

pas tuku kudu bener kodesp sing dilebokna

pas tuku kode barang sing dilekna ya kudu ana nang tbarang

nek data transaksi pembelian sing nang tnotabeli ya otomatis sing nang tpembelian ya melu ilang.

Lan sapiturute


Ben genah yakin relasine senajan wis dijejel ya bisa di deleng kanti visual nganggo software EMS SQL bisa didownload nang http://www.mysqlmanager.com

relasi3

Nah tek terusna maning gawe query alias view sing bisa nampilaken data nonota, nama suplier, kode barang, nama barang, satuan, harga, jumlah/qty dan jumlah belinya (harga * jumlah):

CREATE VIEW `qpembelian` AS select `tnotabeli`.`nonota` AS `nonota`,`tsuplier`.`nama` AS `nama`,`tpembelian`.`kodebrg` AS `kodebrg`,`tbarang`.`namabrg` AS `namabrg`,`tbarang`.`satuan` AS `satuan`,`tpembelian`.`harga` AS `harga`,`tpembelian`.`jumlah` AS `jumlah`,(`tpembelian`.`harga` * `tpembelian`.`jumlah`) AS `jmlbeli` from (`tsuplier` join ((`tpembelian` join `tnotabeli` on((`tpembelian`.`nonota` = `tnotabeli`.`nonota`))) left join `tbarang` on((`tbarang`.`kodebrg` = `tpembelian`.`kodebrg`))) on((`tsuplier`.`kodesp` = `tnotabeli`.`kodesp`)));

Dadi nek tek select * from qpembelian hasile :

relasi4

Nah dadi kan ?

Terusaken gawe view sing bisa nampilaken rekap transaksine (tak jajal ora karo As ya gelem) :

create view qtotbelian as SELECT tnotabeli.nonota, tnotabeli.tglbeli, tsuplier.nama, Sum(QPembelian.jmlbeli) AS JUMLAH

FROM tsuplier INNER JOIN (tnotabeli INNER JOIN QPembelian ON tnotabeli.nonota = QPembelian.nonota) ON tsuplier.kodesp = tnotabeli.kodesp

GROUP BY tnotabeli.nonota;

Nah nek di select * from qtotbelian hasil e :

relasi5

nah keton siki nek transaksi no nota B001 kue tukune maring suplier e PT. Sinar Abadi tanggal 12-01-2008 jumlah regane 133000, transaksi sing sijine yang metu. Ben yakin apa relasine karo viewne dadi ora, ya tek jejel tak tambahi data nganggo DML kaya kie :

insert into tnotabeli values (‘B003′,’fitri’,’SP001′,’2008/01/13′)

INSERT INTO `tpembelian` VALUES (‘B003′,’BRG002’,1200,10);

INSERT INTO `tpembelian` VALUES (‘B003′,’BRG003’,1500,20);

INSERT INTO `tpembelian` VALUES (‘B003′,’BRG004’,1800,20);

Nah nggo buktikna select * from qtotbelian dirun, hasil e :

relasi6

Nah benar kan ? dadi. Karo konsep sing kaya kie mestine dewek nek ngrancang database bener ya kepenak, tur validasi entry data wis ditangani nang DBMS. Gawe laporan ya kepenak wong view alias query bisa digawe, tur gelem nyambut gawe dewek, ora perlu programmer mikir na rumus e apa ? coding e kepie ?

“Wah dadi jaman tambah maju kepenak ya dadi programmer, wong DBMS e wis bisa ngode dewek”, jare ciplus

“Ya bener plus, dadi mestine programmer codinge ora nemen-nemen mumete lah, kaya mung kari loading database bae, masalah validasi karo transaksi DBMS wis nyambut gawe dewek ya ?” jare si cimol

Ya kurang lewih e kaya kue ya ? ya pada-pada

Nang postinganku kie ana ralat titik, jeneng database luwes-luwese ya pembelian dudu penjualan, kie anu kurang pas karo topike, maklum wingi tes mulang transaksi penjualan dadi kegawa-gawa.


Entry filed under: Database. Tags: .

Menu Login dienscripsi dengan Ascii (php + mysql) Transaksi penjualan dengan mysql

2 Comments Add your own

  • 1. Transaksi penjualan dengan mysql « Jingklak’s Weblog  |  March 27, 2009 at 8:30 am

    […] artikel sebelumya saya sudah memposting masalah relasi dimysql dengan judul “Gawe relasi nang mysql” yang saya buat dengan bahasa kebumenan, kini saya lanjutkan lagi dengan penjualannya. Dengan […]

    Reply
  • 2. nunus  |  July 5, 2009 at 1:33 am

    makasih ya mas….
    artikelya mudah dimengerti…

    Reply

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

Trackback this post  |  Subscribe to the comments via RSS Feed


November 2008
M T W T F S S
« Oct   Dec »
 12
3456789
10111213141516
17181920212223
24252627282930

%d bloggers like this: