SMART INVENTORY MANAGEMENT SYSTEM

LEE SIEW PING

UNIVERSITI TEKNIKAL MALAYSIA MELAKA

BORANG PENGESAHAN STATUS TESIS

JUDUL: SMART INVENTORY MANAGEMENT SYSTEM

SESI PENGAJIAN: 2015

Saya LEE SIEW PING mengaku membenarkan tesis (PSM/Sarjana/Doktor Falsafah) ini disimpan di Perpustakaan Fakulti Teknologi Maklumat dan Komunikasi dengan syarat-syarat kegunaan seperti berikut:

1. Tesis dan projek adalah hakmilik Universiti Teknikal Malaysia Melaka. 2. Perpustakaan Fakulti Teknologi Maklumat dan Komunikasi dibenarkan membuat

salinan untuk tujuan pengajian sahaja. 3. Perpustakaan Fakulti Teknologi Maklumat dan Komunikasi dibenarkan membuat

salinan tesis ini sebagai bahan pertukaran antara institusi pengajian tinggi. 4. ** Sila tandakan (/)

_____________SULIT (Mengandungi maklumat yang berdarjah keselamatan atau kepentingan Malaysia seperti yang termaktub di dalam AKTA RAHSIA RASMI 1972)

_____________TERHAD (Mengandungi maklumat yang berdarjah keselamatan atau kepentingan Malaysia seperti yang termaktub di dalam AKTA RAHSIA RASMI 1972)

_____________TIDAK TERHAD

_________________________ ___________________________

(TANDATANGAN PENULIS) (TANDATANGAN PENYELIA)

Alamat tetap: No.9, Jalan Wakaf Nama Penyelia: Prof. Madya Mek Zainab, 15300, Kota Bharu, Norhaziah Bt Md Salleh Kelantan. Tarikh: 12 Aug 2015 Tarikh:

CATATAN: * Tesis dimaksudkan sebagai Laporan Akhir Projek Sarjana Muda (PSM) ** Jika tesis ini SULIT atau TERHAD, sila lampirkan surat daripada pihak berkuasa.

SMART INVENTORY MANAGEMENT SYSTEM

LEE SIEW PING

This report is submitted in partial fulfillment of the requirements for the Bachelor of

Computer Science (Database Management)

FACULTY OF INFORMATION AND COMMUNICATION TEKNOLOGY UNIVERITY TEKNIKAL MALAYSIA MELAKA

2015

DECLARATION

I hereby declare that this project report entitled

SMART INVENTORY MANAGEMENT SYSTEM

is written by me and is my own effort and that no part has been plagiarized without

citations.

STUDENT : ____________________________ Date: _______________

(LEE SIEW PING)

SUPERVISOR : ____________________________ Date: _______________

(PROF. MADYA NORHAZIAH BT MD SALLEH)

DEDICATION

To my beloved parents, thank you for providing a variety of support in terms of moral,

financial and always be by my side when I am going through hard times to finish this my

tertiary studies including this project.

To my beloved supervisor, Prof. Madya Norhaziah Bt Md Salleh, thank you for guiding,

listen to me every time I face any problems and giving me the best advice all the way

through this project.

To all my beloved friends, thank you for giving support when I am facing problems

during this project.

ACKNOWLEDGEMENTS

This project is the last project in my degree life in this university, University

Techincal Malaysia Malacca (UTeM). Here, I would like to take this opportunity to

thanks a few people who helped me to complete this thesis. First of all, I would like to

thanks to the contribution of my supervisor, Prof. Madya Norhaziah Bt Md Salleh, who

gave me a lot of valuable advice and assist me to complete this project.

Besides, I would like to thanks my parents who gave me support and strength

whenever I face difficulties in the progress of this project.

Last but not least, I would like to thanks all my friends who helped me directly or

indirectly especially in developing the system. Without them, I would not complete this

project on time.

ABSTRACT

Smart Inventory Management System (SIMS) is a web based database system

which used to manage the flow of inventory in the shop. By using this system, it will

help the user to efficiently manage the flow of the stocks, effectively utilize the stock,

and generate the sales report. Besides, at a specific time period, if the inventory is below

the threshold level, the staff will be notify in a reminder to order the items from the

vendors in order to restock the required items. By using this system, it will help to save

manpower and money. In addition, this system is a user friendly system which can be

used by anyone.

ABSTRAK

Sistem Bijak Pengurusan Inventori (SIMS) merupakan satu sistem yang

berdasarkan web. Ia digunakan untuk mengurus aliran inventori di kedai. Dengan

menggunakan sistem ini, ia akan membantu pengguna untuk mengurus aliran stock

dengan effektif dan menghasilkan report dengan automatik. Selain itu, dalam masa yang

tertentu, jikalau sesetengah stok berada bawah aras yang ditentukan, ia akan memberi

notis kepada staff untuk membuat tempahan dari penjual. Di samping itu, dengan

menggunakan sistem ini, ia juga dapat membantu pengguna untuk menjimat masa dan

tenaga kerja. Sementara itu, sistem ini juga merupakan sistem yang senang digunakan

untuk mana-mana golongan sama ada muda atau tua.

TABLE OF CONTENTS

CHAPTER SUBJECT PAGE

DECLARATION i

DEDICATION ii

ACKNOWLEDGEMENT iii

ABSTRACT iv

ABSTRAK v

TABLE OF CONTENTS vi

LIST OF TABLES

CHAPTER I INTRODUCTION

1.1 Project Background 1

1.2 Problem Statement 2

1.3 Objectives 3

1.4 Scope 4

1.5 Project Significance 5

1.6 Expected Output 6

1.7 Conclusion 7

CHAPTER II PROJECT METHODOLOGY AND

PLANNING

2.1 Introduction 8

2.2 Project Methodology 9

2.3 Project Schedule and Milestones 10

2.4 Conclusion 11

CHAPTER III ANALYSIS

3.1 Introduction 12

3.2 Problem Analysis 13

3.3 The Proposed Improvements/Solutions 14

3.4 Requirement Analysis of the to-be-system 15

3.4.1 Functional Requirement 16

3.4.2 Non-Functional Requirement 17

3.4.3 Other Requirement 18

3.5 Conclusion 19

CHAPTER IV DESIGN

4.1 Introduction 20

4.2 System Architecture Design 21

4.3 Database Design 22

4.3.1 Conceptual Design 23

4.3.2 Logical Design 24

4.3.3 Physical Design 25

4.4 Graphical User Interface (GUI) Design 26

4.5 Conclusion 27

CHAPTER V IMPLEMENTATION

5.1 Introduction 28

5.2 Software Development Environment

Setup

29

5.3 Database Implementation 30

5.4 Conclusion 31

CHAPTER VI TESTING

6.1 Introduction 32

6.2 Test Plan 33

6.2.1 Test Organization 34

6.2.2 Test Environment 35

6.2.3 Test Schedule 36

6.3 Test Strategy 37

6.3.1 Classes of Tests 38

6.4 Test Design 39

6.4.1 Test Description 40

6.4.2 Test Data 41

6.5 Test Result and Analysis 42

6.6 Conclusion 43

CHAPTER VII PROJECT CONCLUSION

7.1 Introduction 44

7.2 Observation on Weakness and Strengths 45

7.3 Propositions for Improvement 46

7.4 Contribution 47

7.5 Conclusion 48

REFERENCES 81

APPENDIX 82

LIST OF TABLES

TABLE TITLE PAGE

2.1 Project Milestone

3.1 Software Requirements

3.2 Hardware Requirements

3.3 Network Requirements

4.1 Data Dictionary for Smart Inventory Management

System

4.2 Create Table

4.3 Details for Main Page

4.4 Details for Login Page

5.1 Software Setup

5.2 Hardware Setup

6.1 Test Organization

6.2 User Personal Computer Configuration

6.3 Test Schedule

6.4 Test Design for System Login Module

6.5 Test Data for System Login

6.6 Test Result for SIMS

LIST OF FIGURES

DIAGRAM TITLE PAGE

3.1 Flow of Current Existing System

3.2 Decomposition Diagram for Proposed

Improvement System

3.3 Data Flow Diagram (Context Diagram)

3.4 Data Flow Diagram (Level 1)

3.5 Data Flow Diagram (Level 2) Process 1 Manage

Customer

4.1 Two-Tier Web Based System Architecture of

SIMS

4.2 Entity Relationship Diagram for Smart Inventory

Management System

4.3 Navigation Flow of Smart Inventory Management

System

4.4 Input Design For Main Page

4.5 Input Design for Login Page

4.6 Alert Messages for Successfully Registered

4.7 Error Messages for Failed Register

4.8 Confirmation Messages to Delete Data

5.1 Two-tier client server architecture for SIMS

5.2 Oracle Database 11g Install Wizard Main Page

5.3 Main Page of Wamp Server Installation

5.4 SELECT Query

5.5 UPDATE Query

5.6 INSERT Query

5.7 DELETE Query

5.8 JOIN Query

5.9 AGGREGATE Query

5.10 Data Loading Process

LIST OF ABBREVIATIONS

SIMS - Smart Inventory Management System

SQL - Structured Query Language

ERD - Entity Relationship Diagram

DFD - Data Flow Diagram

PK - Primary Key

FK - Foreign Key

DBMS - Database Management System

LIST OF APPENDIXES

APPENDIX TITLE PAGE

APPENDIX A GANTT CHART 85

APPENDIX B STORED PROCEDURE AND

TRIGGERS

90

APPENDIX C INPUT DESIGN 109

APPENDIX D INSTALLATION STEPS FOR

ORACLE 11G

APPENDIX E INSTALLTION STEPS FOR WAMP

SERVER

APPENDIX F TEST DESIGN

APPENDIX G TEST DATA

APPENDIX H USER MANUAL

CHAPTER I

INTRODUCTION

1.1 Project Background

Organization of stock flow in an inventory shop is an important process as the

stock is the main entity of a shop. Nowadays, most of the staff and managers are forced

to keep track of inventory by hand. This means that they must count what they have sold,

what they have left at the end of each day, store the data of each item and check the

price of the items manually. They also must fill out order forms to be sent to the vendors

so that they can restock their inventory. This wastes valuable man hours and is a rather

simple task to automate using a software system. The solution that I would like to

propose based on this issue by developing software system which will keep track of the

stock in the shop.

Meanwhile, this system can help staff to efficiently manage the flow of the

stocks, effectively utilize the stock, and generate the sales report. Besides, at a specific

time period, if the inventory is below the threshold level, the staff will be notify in a

reminder to order the items from the vendors in order to restock the required items.

1.2 Problem Statement

1. Backup and Recovery

The current inventory shop does not have any system to help them to do

any backup or recovery whenever their data is loss.

2. Generate Report

Nowadays, report has become an important feature of a system as most of

the organization need report to see statistic or the sales for the shop. It is time

consuming by doing it manually.

3. Lack of record of the stock

No record of the sales are made so they can’t figure up the exactly profit

of the shop and how many stock are left.

1.3 Objective

Objectives are the goal of the proposed system. Below shows the objectives of

SIMS:

1. To develop a system with generate report features

A report can be generated by using the system automatically and the

desire parameters can be selected.

2. To reduce lead time, shelf space and errors

Time needed to record the data, space needed to put the file contains the

data and some errors can be reduced by using the system.

3. To keep record of transaction logging through triggers

By using the system, some changes that are made to the important table

such as use and role will be automatically keep track by using triggers.

4. To enable the user retrieve data easily and accurately

By using this system, all the data can be retrieved easily from the

database.

1.4 Scope

In this section, the scope includes system modules, users and software required.

1.4.1 Scope of System Module

1. Login

In the login system, only the authorized staff can login to the system by

using their own user id and password for login. The login system is important to

ensure that the system will not be access or change by unauthorized user.

2. Calculation

This module is used to calculate the total sales and the highest quantity of

item sales.

3. Inventory Module

i. Add new items

ii. Make order of items

iii. Remind the staff if the items are below the threshold level

4. Report

Generate sales report based on day, month and year.

1.4.2 Scope of users

The user that will be using SIMS is:

1. Normal Staff

Normal staff is the person who is in charge on the front end system. They

manage the order, items and the supplier. They can view the stock, add a new

stock, update data or delete unnecessary data.

1.4.3 Scope of Technologies

There are several technologies that are being used to develop SIMS which are

software, network and hardware requirements. All the scope is show as below:

1. Software

i. Programming Language

PHP

ii. Operating System

Microsoft Windows 7

iii. Web Server

Apache

iv. Database Management System (DBMS)

Oracle

2. Hardware

i. Processor: Intel ® Core ™ i3-2367M

ii. RAM: 4GB

3. Network

i. Local Area Network (LAN)

1.5 Project Significance

The SIMS will benefit the staff which is going to use this system. Through this

system, it can ease their task to operate and maintain the system. The staffs do not have

to record all the data by using handwriting. They just need to key in all the data into the

system. Besides, the system will also remind the staff to restock whenever the stock is

below the threshold level. By using this, they can easily detect the flow of the stock.

1.6 Expected Output

The expected output from this system is where all the objectives that have been

stated earlier have been achieved. It will provide the entire feature for user access

control through the interface.

1.7 Conclusion

As a conclusion, this chapter includes the problem statement, objectives, and

scope for the system. The other information about the system will be discussed in next

chapter.

CHAPTER II

PROJECT METHODOLOGY AND PLANNING

2.1 Introduction

Project Methodology is an important component in developing a system. The

methodology that has been choose for this project is Agile Software Development. Agile

consists of planning, analysis, design, implementation and testing. Agile methodology is

more suitable for this project because it is flexible. It can rapidly change whenever there

is a need to change on the requirements. In addition, some of the phase is continually

revisited until it meets the customer satisfaction.

2.2 Project Methodology

Smart Inventory Management System is proposed to improve the manual system

to an automated and computerize system. Agile Software Development is used while

developing this system.

1. Planning

In this planning phase, it is used to capture the requirement of SIMS. The

objectives are determined and the requirements are captured. All the information

is being analyzed for their validity.

2. Analysis

In this phase, the objectives, problem statement and scope will be

identified. The manual system of the inventory will be analyzed using the flow

chart while the to-be develop system will be analyzed using context diagram and

data flow diagram.

3. Design

This phase will include the design of the database model that is going to

support the operation system and the objectives. The context diagram and data

flow diagram from the analysis phase will be used to initiate the design phase by

develop the conceptual data model. Then the conceptual data model will be used

to design the logical data model. Lastly, the physical data model will be

developed.

4. Implementation

In this phase, PHP programming language will be used to develop this

system. The modules that will be developed with system interface are the front-

end user interface which can be used by the staff of the shop to do their daily

work.