UNIVERSITAS BINA NUSANTARA ______________________________________________________________________________ Jurusan Teknik Informatika Skripsi Sarjana Komputer Semester Ganjil tahun 2005/2006 ANALISIS DAN PERANCANGAN SISTEM BASISDATA PEMBELIAN, PERSEDIAAN DAN PENJUALAN PADA CV.MEGAH PRIMA MANDIRI APRIANTINI FIQIH 0700699646 ANDI SATYA PERDANA 0700702861 EKO PRASETYO 0700726230 Kelas / Kelompok : 07PAT / 05 Abstrak CV. Megah Prima Mandiri adalah perusahaan yang bergerak dalam bidang perdagangan barang khususnya sebagai supplier (penyalur) untuk alat – alat mekanikal, elektrikal, alat tulis kantor, konstruksi dan telekomunikasi. Adapun tujuan dan perancangan basisdata ini adalah menghasilkan sebuah sistem basisdata yang mampu mendukung perusahaan dalam mengolah data dan informasi yang ada dalam perusahaan tersebut. Metodologi yang digunakan adalah metode pengumpulan data dan informasi yang dilakukan dengan melakukan pengamatan langsung pada sistem yang sedang berjalan pada perusahaan tersebut. Selain itu juga, membaca referensi dari buku – buku yang berkaitan dengan topik skripsi yang diajukan. Pada awalnya perusahaan belum memiliki sistem basisdata sehingga data – data transaksi dicatat secara manual. Hasil yang dicapai dari skripsi ini adalah merancang dan membangun sistem basisdata untuk CV. Megah Prima Mandiri yang nantinya dapat digunakan untuk mencatat data – data transaksi secara komputerisasi. Kata Kunci Analisis, pembelian, persediaan, penjualan, perancangan basisdata
iv
PRAKATA
Puji syukur kepada Tuhan Yang Maha Esa atas berkat dan anugerah-Nya sehingga penulis dapat menyelesaikan skripsi ini yang merupakan syarat untuk meraih gelar kesarjanaan pada jenjang pendidikan S-1 (Strata-1) tepat pada waktunya. Pada kesempatan ini dengan segala ketulusan dan kerendahan hati, penulis hendak menyampaikan rasa terima kasih kepada semua pihak yang telah memberikan bantuan, nasihat, petunjuk, kesempata, dan dorongan selama penulis menyelesaikan skripsi ini, yaitu : 1. Bapak Doddy Koeswandy, S.Kom, MM, selaku dosen pembimbing skripsi yang telah banyak memberikan petunjuk, masukan dan saran sehingga skripsi ini dapat terselesaikan. 2. Bapak Chairil, SE, selaku Direktur Utama CV. Megah Prima Mandiri beserta para staff yang telah mengijinkan penulis untuk melakukan analisa dan penelitian sebagai bahan dasar penyusunan skripsi. 3. Orang tua kami yang senantiasa terus – menerus mendukung kami baik moral maupun materiil yang sangat tidak ternilai harganya. 4. Bapak Prof. Dr. Geraldus Polla M.App., Sc., selaku Rektor Universitas Bina Nusantara. 5. Bapak dan Ibu staff perpustakaan yang mengijinkan kami menjaga kami di perpustakaan dari saat buka hingga malam menjelang tutup. 6. Teman – teman seperjuangan skripsi dalam satu kelas.
v
7. Semua pihak yang tidak dapat kami sebutkan satu persatu yang banyak membantu penulis baik secara langsung maupun tidak langsung sehingga skripsi ini dapat terselesaikan dengan baik. Merupakan suatu kebahagiaan bagi penulis apabila skripsi ini dapat bermanfaat bagi pembaca semua dan bagi adik – adik kelas kami untuk dijadikan salah satu referensi bacaan untuk melakukan penelitian di kemudian hari. Penulis menyadari bahwa tidak ada yang sempurna di dunia ini, begitu juga dengan skripsi ini, sangat jauh dari kesempurnaan. Penulis pun mengharapkan kritik dan saran yang membangun dari berbagai pihak. Akhir kata, semoga skripsi dapat bermanfaat bagi semua pihak yang berkepentingan.
Jakarta, 14 January 2007
Penulis
vi
DAFTAR ISI Halaman Judul Luar ..................................................................................................................
i
Halaman Judul Dalam ................................................................................................................
ii
Halaman Persetujuan Hardcover ..............................................................................................
iii
Abstrak .........................................................................................................................................
iv
Prakata .........................................................................................................................................
v
Daftar Isi ......................................................................................................................................
vii
Daftar Tabel .................................................................................................................................
xii
Daftar Gambar ............................................................................................................................
xiv
PENDAHULUAN ........................................................................................................
1
1.1
Latar Belakang ................................................................................................
1
1.2
Ruang Lingkup ................................................................................................
2
1.3
Tujuan dan Manfaat .........................................................................................
2
1.4
Metodologi Penelitian .....................................................................................
3
1.5
Sistematika Penulisan ......................................................................................
4
LANDASAN TEORI ...................................................................................................
6
2.1
Sejarah Teknologi Basisdata ...........................................................................
6
2.2
Teori Basisdata ................................................................................................
7
2.2.1 Pengertian Basisdata ............................................................................
7
2.2.2 Database Management System (DBMS) .............................................
9
2.2.3 Data Definition Language (DDL) .......................................................
14
2.2.4 Data Manipulation Language (DML) .................................................
15
2.2.5 Normalisasi ..........................................................................................
16
BAB 1
BAB 2
vii
BAB 3
2.2.6 Fourth Generation Language (4GL) ...................................................
19
2.2.7 Siklus Hidup Basisdata Aplikasi Basis Data .......................................
20
2.2.7.1
Database Planning (Perencanaan Basisdata) ....................
21
2.2.7.2
System Definition (Definisi Sistem) ..................................
22
2.2.7.3
Requirement Collection and Analysis (Pengumpulan Kebutuhan dan Analisis) ...................................................
23
2.2.7.4
Database Design (Perancangan Basisdata) .......................
26
2.2.7.5
DBMS Selection (Pemilihan DBMS) .................................
27
2.2.7.6
Application Design (Perancangan Aplikasi) ......................
28
2.2.7.7
Prototyping (Bentuk Dasar) ..............................................
30
2.2.7.8
Implementation (Implementasi) ........................................
30
2.2.7.9
Data Conversion and Loading (Perubahan dan Pengambilan Data) ............................................................
31
2.2.7.10
Testing (Pengetesan) ..........................................................
31
2.2.7.11
Operational Maintenance (Perawatan Operasional) .........
32
2.2.8 Desain Konseptual, Logikal, dan Fisikal Basisdata ............................
32
2.3
Teori Penjualan ................................................................................................
44
2.4
Teori Pembelian ...............................................................................................
47
2.5
Teori Persediaan ..............................................................................................
48
ANALISIS SISTEM YANG BERJALAN .................................................................
51
3.1
Sejarah Organisasi CV. Megah Prima Mandiri ...............................................
51
3.2
Struktur Organisasi CV. Megah Prima Mandiri ..............................................
51
viii
BAB 4
3.3
Wewenang dan Tanggung Jawab ....................................................................
52
3.4
Diagram Alir Dokumen Sistem yang berjalan ................................................
54
3.4.1 Bagian Penjualan .................................................................................
54
3.4.2 Bagian Pembelian ................................................................................
57
3.4.3 Bagian Invoicing .................................................................................
60
3.4.4 Bagian Penerimaan ..............................................................................
61
3.4.5 Bagian Pembayaran .............................................................................
62
3.5
Pemasalahan yang dihadapi .............................................................................
63
3.6
Analisis Kebutuhan Informasi .........................................................................
63
3.7
Usulan Pemecahan Masalah ............................................................................
64
PERANCANGAN DAN IMPLEMENTASI ..............................................................
65
4.1
Perancangan Basisdata ....................................................................................
65
4.1.1 Perancangan Basisdata Konseptual .....................................................
65
4.1.1.1
Identifikasi Tipe Entiti .......................................................
65
4.1.1.2
Identifikasi Tipe Relasional ...............................................
67
4.1.1.3
Identifikasi Atribut dan Domain Atribut ...........................
69
4.1.1.4
Identifikasi Kandidat dan Primary Key .............................
87
4.1.1.5
Validasi Model Konseptual dengan User Transaction ......
89
4.1.2 Perancangan Basisdata Logikal ...........................................................
91
4.1.2.1
Penghilangan Fitur-Fitur yang tidak Kompatibel ..............
91
4.1.2.2
Menentukan Model Logikal Data Lokal ...........................
92
4.1.2.3
Relasi model dengan Normalisasi .....................................
99
ix
4.2
4.1.2.4
Mendefinisikan Kendala Integrity .....................................
113
4.1.2.5
Validasi Model Logikal lokal dengan Data Global ...........
118
4.1.3 Perancangan Basisdata Fisik ...............................................................
126
4.1.3.1
Pemilihan DBMS ...............................................................
126
4.1.3.2
Perancangan Base Relation ...............................................
129
4.1.3.3
Analisis Transaksi............................................................... 141
4.1.3.4
Pemilihan Organisasi File………………………………...
145
4.1.3.5
Penentuan Indeks…………………………………………
146
4.1.3.6
Estimasi Kapasitas Penyimpanan ......................................
149
4.1.3.7
Perancangan Mekanisme Keamanan .................................
152
Perancangan Fisik ............................................................................................ 154 4.2.1 Structure Chart (Bagan Terstruktur) ...................................................
154
4.2.2 Struktur Menu ......................................................................................
171
4.2.3 Perancangan State Transition Diagram (STD) ...................................
172
4.2.4 Spesifikasi Proses ................................................................................
179
4.3
Perancangan Input dan Output ........................................................................
198
4.4
Rancangan Implementasi ................................................................................. 231
4.5
4.4.1 Spesifikasi Perangkat Keras ................................................................
231
4.4.2 Spesifikasi Perangkat Lunak ...............................................................
232
4.4.3 Cara pengoperasian Aplikasi ...............................................................
233
Pengujian (Testing) .......................................................................................... 275 4.5.1 Kriteria Pengujian ................................................................................
x
275
4.5.2 Hasil Pengujian .................................................................................... 4.6 BAB 5
276
Evaluasi ............................................................................................................ 277
SIMPULAN DAN SARAN .......................................................................................... 279 5.1
Simpulan ..........................................................................................................
279
5.2
Saran ................................................................................................................
280
DAFTAR PUSTAKA ..................................................................................................................
281
DAFTAR RIWAYAT HIDUP .................................................................................................... 282 LAMPIRAN Fotocopy Surat Survey ...............................................................................................
L1
Fotocopy Absensi Survey ..........................................................................................
L2
APPENDIX
xi
DAFTAR TABEL Tabel 3.1
Tabel Analisis Kebutuhan Informasi......................................................................... 63
Tabel 4.1
Tabel Identifikasi Tipe-Tipe Entiti............................................................................ 65
Tabel 4.2
Tabel ER Multiplicity ...............................................................................................
68
Tabel 4.3
Tabel Identifikasi Atribut .........................................................................................
69
Tabel 4.4
Tabel Candidate & Primary Key setiap Entiti .........................................................
88
Tabel 4.5
Tabel Skema Relasi Integrity Constraint..................................................................
113
Tabel 4.6
Tabel Model Global Principal...................................................................................
118
Tabel 4.7
Tabel Model Global Customer .................................................................................
118
Tabel 4.8
Tabel Model Global Shipper.....................................................................................
119
Tabel 4.9
Tabel Model Global Karyawan.................................................................................
119
Tabel 4.10 Tabel Model Global Barang......................................................................................
120
Tabel 4.11 Tabel Model Global Retur......................................................................................... 120 Tabel 4.12 Tabel Model Global Pembelian................................................................................. 121 Tabel 4.13 Tabel Model Global Penjualan.................................................................................. 122 Tabel 4.14 Tabel Model Global Detail Pembelian...................................................................... 123 Tabel 4.15 Tabel Model Global Detail Penjualan.......................................................................
124
Tabel 4.16 Tabel Model Global Detail Retur.............................................................................. 124 Tabel 4.17 Tabel Model Global Pembayaran Jual......................................................................
125
Tabel 4.18 Tabel Model Global Pembayaran Beli......................................................................
125
Tabel 4.19 Tabel Perbandingan MS SQL Server 2000 dengan Oracle 10g................................ 126 Tabel 4.20 Tabel Analisis Transaksi...........................................................................................
142
Tabel 4.21 Tabel Estimasi Penyimpanan....................................................................................
149
xii
Tabel 4.22 Tabel Perhitungan Ruang Penyimpanan...................................................................
151
Tabel 4.23 Tabel Pemberian Hak Akses.....................................................................................
153
Tabel 4.24 Tabel Kebutuhan Spesifikasi Hardware untuk Server.............................................. 231 Tabel 4.25 Tabel Kebutuhan Spesifikasi Hardware untuk Workstation....................................
232
Tabel 4.26 Tabel Spesifikasi Perangkat Software.......................................................................
232
xiii
DAFTAR GAMBAR Gambar 2.1
Bagaimana normalisasi dapat digunakan untuk mendukung desain basisdata .............................................................................................................
17
Gambar 2.2
Diagram ilustrasi dari proses normalisasi ..........................................................
19
Gambar 2.3
Siklus Hidup Aplikasi Basisdata ........................................................................
21
Gambar 2.4
Centralized approach untuk menangani multiple user views ............................
24
Gambar 2.5
View Integration approach untuk menangani multiple user views .................... 25
Gambar 2.6
Contoh Entiti dan Relasinya ............................................................................... 34
Gambar 3.1
Struktur Organisasi ............................................................................................. 51
Gambar 3.2
Diagram alir dokumen bagian penjualan............................................................
56
Gambar 3.3
Diagram alir dokumen bagian pembelian...........................................................
59
Gambar 3.4
Diagram alir dokumen bagian invoicing.............................................................
60
Gambar 3.5
Diagram alir dokumen bagian penerimaan.........................................................
61
Gambar 3.6
Diagram alir dokumen bagian pembayaran........................................................
62
Gambar 4.1
ER Diagram konseptual......................................................................................
67
Gambar 4.2
ER Diagram konseptual dengan penambahan multiplicity.................................
67
Gambar 4.3
ER Diagram konseptual dengan penambahan primary key................................
89
Gambar 4.4
ER Diagram dengan penambahan user transaction............................................
90
Gambar 4.5
Penghilangan Atribut multivalued pada Entiti Customer.................................. 91
Gambar 4.6
Penghilangan Atribut multivalued pada Entiti Karyawan................................... 91
xiv
Gambar 4.7
Penghilangan Atribut multivalued pada Entiti Principal..................................... 91
Gambar 4.8
Penghilangan Atribut multivalued pada Entiti Shipper....................................... 92
Gambar 4.9
ER Diagram logikal ternormalisasi.....................................................................
112
Gambar 4.10
Structure Chart Proses Pembelian......................................................................
155
Gambar 4.11
Structure Chart Diagram Penjualan....................................................................
156
Gambar 4.12
Structure Chart Diagram Pembayaran Jual........................................................
157
Gambar 4.13
Structure Chart Diagram Pembayaran Beli........................................................
158
Gambar 4.14
Structure Chart Diagram Retur........................................................................... 159
Gambar 4.15
Structure Chart Diagram Data Customer............................................................ 160
Gambar 4.16
Structure Chart Diagram Data Principal............................................................. 161
Gambar 4.17
Structure Chart Diagram Data Shipper............................................................... 162
Gambar 4.18
Structure Chart Diagram Data Karyawan........................................................... 163
Gambar 4.19
Structure Chart Diagram Data Barang................................................................ 164
Gambar 4.20
Structure Chart Diagram Delivery_Order..........................................................
Gambar 4.21
Structure Chart Diagram RequestOfQuotation................................................... 166
Gambar 4.22
Structure Chart Diagram Faktur_pajak............................................................... 167
Gambar 4.23
Structure Chart Diagram Invoice........................................................................ 168
Gambar 4.24
Structure Chart Diagram PO Jual.......................................................................
Gambar 4.25
Structure Chart Diagram PO Beli....................................................................... 170
Gambar 4.26
Struktur Menu Utama.......................................................................................... 171
xv
165
169
Gambar 4.27
STD Layar Window............................................................................................
172
Gambar 4.28
STD Master.........................................................................................................
173
Gambar 4.29
STD Transaksi..................................................................................................... 174
Gambar 4.30
STD Retur...........................................................................................................
175
Gambar 4.31
STD Laporan.......................................................................................................
176
Gambar 4.32
STD Arsip...........................................................................................................
177
Gambar 4.33
STD Pembayaran................................................................................................. 178
Gambar 4.34
Rancangan Tampilan Halaman Login................................................................. 198
Gambar 4.35
Rancangan Tampilan Halaman Utama................................................................ 199
Gambar 4.36
Rancangan Tampilan Halaman Master Customer............................................... 200
Gambar 4.37
Rancangan Tampilan Halaman Insert Master Customer..................................... 201
Gambar 4.38
Rancangan Tampilan Halaman Master Karyawan.............................................. 202
Gambar 4.39
Rancangan Tampilan Halaman Insert Master Karyawan.................................... 203
Gambar 4.40
Rancangan Tampilan Halaman Master Principal................................................ 204
Gambar 4.41
Rancangan Tampilan Halaman Insert Master Principal...................................... 205
Gambar 4.42
Rancangan Tampilan Halaman Master Barang................................................... 206
Gambar 4.43
Rancangan Tampilan Halaman Insert Master Barang......................................... 207
Gambar 4.44
Rancangan Tampilan Halaman Insert Transaksi Pembelian............................... 208
Gambar 4.45
Rancangan Tampilan Halaman Insert Transaksi Penjualan................................
209
Gambar 4.46
Rancangan Tampilan Halaman Transaksi Pembelian.........................................
210
xvi
Gambar 4.47
Rancangan Tampilan Halaman Transaksi Penjualan..........................................
Gambar 4.48
Rancangan Tampilan Halaman Detail Penjualan................................................ 212
Gambar 4.49
Rancangan Tampilan Halaman Detail Pembelian............................................... 213
Gambar 4.50
Rancangan Tampilan Halaman Retur.................................................................
214
Gambar 4.51
Rancangan Tampilan Halaman Insert Retur.......................................................
215
Gambar 4.52
Rancangan Tampilan Halaman Detail Retur....................................................... 216
Gambar 4.53
Rancangan Tampilan Halaman Arsip RoQ.........................................................
Gambar 4.54
Rancangan Tampilan Halaman Insert RoQ......................................................... 218
Gambar 4.55
Rancangan Tampilan Halaman Arsip Delivery Order........................................
219
Gambar 4.56
Rancangan Tampilan Halaman Detail Delivery Order.......................................
220
Gambar 4.57
Rancangan Tampilan Halaman Arsip Invoice....................................................
221
Gambar 4.58
Rancangan Tampilan Halaman Arsip Purchase Order........................................ 222
Gambar 4.59
Rancangan Tampilan Halaman Insert Purchase Order.......................................
223
Gambar 4.60
Rancangan Tampilan Halaman Laporan Pembelian...........................................
224
Gambar 4.61
Rancangan Tampilan Halaman Laporan Penjualan............................................
225
Gambar 4.62
Rancangan Tampilan Halaman Search...............................................................
226
Gambar 4.63
Rancangan Tampilan Pembayaran Beli..............................................................
227
Gambar 4.64
Rancangan Tampilan Halaman Insert New Pembayaran Beli............................
228
Gambar 4.65
Rancangan Tampilan Halaman Pembayaran Jual...............................................
229
Gambar 4.66
Rancangan Tampilan Halaman Insert New Pembayaran Jual............................
230
xvii
211
217
Gambar 4.67
Halaman Login...................................................................................................
Gambar 4.68
Halaman Utama................................................................................................... 234
Gambar 4.69
Halaman Master Karyawan.................................................................................
Gambar 4.70
Halaman Form Pengisian Karyawan................................................................... 236
Gambar 4.71
Halaman Master Barang...................................................................................... 237
Gambar 4.72
Halaman Form Pengisian Barang........................................................................ 238
Gambar 4.73
Halaman Master Shipper.....................................................................................
Gambar 4.74
Halaman Form Pengisian Shipper....................................................................... 240
Gambar 4.75
Halaman Master Principal...................................................................................
Gambar 4.76
Halaman Form Pengisian Principal..................................................................... 242
Gambar 4.77
Halaman Master Customer.................................................................................. 243
Gambar 4.78
Halaman Form Pengisian Customer.................................................................... 244
Gambar 4.79
Halaman Utama Transaksi Penjualan.................................................................
245
Gambar 4.80
Halaman Detail Penjualan...................................................................................
246
Gambar 4.81
Halaman Cetak Detail Penjualan......................................................................... 247
Gambar 4.82
Halaman Insert New Transaksi Penjualan..........................................................
248
Gambar 4.83
Halaman Utama Transaksi Pembelian................................................................
249
Gambar 4.84
Halaman Detail Pembelian.................................................................................. 250
Gambar 4.85
Halaman Cetak Detail Pembelian.......................................................................
251
Gambar 4.86
Halaman Insert New Transaksi Pembelian.........................................................
252
xviii
233
235
239
241
Gambar 4.87
Halaman Insert New Transaksi Pembelian (lanj.)............................................... 253
Gambar 4.88
Halaman Request of Quotation...........................................................................
254
Gambar 4.89
Halaman Insert New RoQ...................................................................................
255
Gambar 4.90
Halaman Delivery Order.....................................................................................
256
Gambar 4.91
Halaman Detail Delivery Order..........................................................................
257
Gambar 4.92
Halaman Cetak Detail Delivery Order................................................................
258
Gambar 4.93
Halaman Invoice.................................................................................................
259
Gambar 4.94
Halaman Cetak Invoice.......................................................................................
260
Gambar 4.95
Halaman Daftar Purchase Order.........................................................................
261
Gambar 4.96
Halaman Insert New PO...................................................................................... 262
Gambar 4.97
Halaman Laporan Pembelian..............................................................................
263
Gambar 4.98
Halaman Laporan Penjualan...............................................................................
264
Gambar 4.99
Halaman Grafik Laporan Penjualan....................................................................
265
Gambar 4.100
Halaman Laporan Persediaan.............................................................................. 266
Gambar 4.101
Halaman Search Result.......................................................................................
267
Gambar 4.102
Halaman Pembayaran Beli..................................................................................
268
Gambar 4.103
Halaman Insert Transaksi Pembayaran beli........................................................
269
Gambar 4.104
Halaman Pembayaran Jual..................................................................................
270
Gambar 4.105
Halaman Insert Transaksi Pembayaran Jual........................................................ 271
Gambar 4.106
Halaman Retur..................................................................................................... 272
xix
Gambar 4.107
Halaman Detail Retur.......................................................................................... 273
Gambar 4.108
Halaman Cetak Detail Retur...............................................................................
274
Gambar 4.109
Halaman Insert Retur..........................................................................................
275
xx
