DESIGNING A LOGICAL DATA MODEL FOR A SALES AND INVENTORY MANAGEMENT SYSTEM - PDF

Please download to get full document.

View again

of 41
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information Report
Category:

DocStoc

Published:

Views: 8 | Pages: 41

Extension: PDF | Download: 0

Share
Related documents
Description
Bachelor's thesis Information Technology Networking 2013 Hari Krishna Mahat DESIGNING A LOGICAL DATA MODEL FOR A SALES AND INVENTORY MANAGEMENT SYSTEM ii BACHELOR S THESIS ABSTRACT TURKU UNIVERSITY OF
Transcript
Bachelor's thesis Information Technology Networking 2013 Hari Krishna Mahat DESIGNING A LOGICAL DATA MODEL FOR A SALES AND INVENTORY MANAGEMENT SYSTEM ii BACHELOR S THESIS ABSTRACT TURKU UNIVERSITY OF APPLIED SCIENCES Degree programme Information technology December Instructor: Yngvar Wikstrom M.Eng Author: Hari Krishna Mahat DESIGNING A LOGICAL DATA MODEL FOR A SALES AND INVENTORY MANAGEMENT SYSTEM The aim of this thesis was to design a Sales and Inventory Management System (SIM), that is, a database describing the customer orders and products distributed by the company. The SIM system shall help in the management of product sales and processing, inventory management process and operation outside the book-keeping system already in use. The SIM system shall be based on a Database Management System. The purpose of the SIM database system is to maintain the data that is used and generated from the warehouse staff and sales staff. Then the data stored will be used to facilitate smooth running of sales operation and stock management. The sole aim and objective of this thesis was to create a logical data model independent of Relational Database Management System for Sales and Inventory Management System. All the data generated during the business process was studied to design a very well functioning database that will comfort the growth of company. Sales staff and warehouse staff were interviewed, in total 11, on their needs and requirements. These needs and requirements were given high importance throughout the design process. A system independent logical database design was produced from this project work. The data dictionary, ER diagrams, conceptual data model, high level data transaction details, data flow diagrams and logical data model are the outcome of this project work. KEYWORDS: (Data model, database, entity-relation, requirements analysis) TURKU UNIERSITY OF APPLIED SCIENCE, BACHELOR S THESIS Hari Krishna Mahat iii FOREWORD Sales and Inventory Management System design is a design process of a database system for a company named RB and Sons Private Limited. RB and Sons is a distributor company which has acquired contracts from three different manufacturers to solely distribute their products in the Kathmandu region, Nepal. RB and Sons is currently using the traditional paper and pen format to maintain the inventory and to serve their customers. All the billings and stocking are done by writing reports and producing bills in paper format. This way of keeping records is becoming challenging as the business is growing as they are acquiring more contracts from more manufacturers and more customers to serve, hence causing more work load on inventory management and sales operations. With more products in line for sales and marketing, the work for sales staff is also increasing exponentially. RB and Sons is currently employing 7 employees in sales, 2 in delivery, the owner as the CEO and 1 accountant. In total for the time being, RB and Sons employs 11 members of staff. I would like to thank and express my appreciation to my supervisor Mr. Yngvar Wikstrom for being a great advisor and a mentor. All his advices, support and encouragement during my thesis have been a source of inspiration to me. I would also like to thank RB and Sons family, for their trust and support on me during my thesis. Their views, ideas, and suggestion help me very much during many phases of design process. I am very grateful towards every member of RB and Sons, and looking forward to work with them also in application development process , Turku Hari Krishna Mahat TURKU UNIERSITY OF APPLIED SCIENCE, BACHELOR S THESIS Hari Krishna Mahat CONTENT 1. INTRODUCTION 1 2. SALES AND INVENTORY MANAGEMENT DATABASE DESIGN PROCESS Objective of the SIM system Sales and Inventory Management Database Design phase Logical Database Design Physical Database Design 3 3. REQUIREMENT COLLECTION AND ANALYSIS Requirements collections Examining Documentation Interviewing and Questionnaires Database Requirements Initial Database Size Database Growth Searches and inquiry Networking and shared access requirements Security Functional Requirements Administration Functional Requirements Sale Manager Functional Requirements Inventory Manager Function Requirements Salesmen Functional requirements Users transaction requirements Data entry Data update/deletion Data query 10 4 FUNCTIONAL ANALYSIS Input Process Output 13 5 CONCEPTUAL DATABASE DESIGN Definition Conceptual Database Design Methodology Identifying entity types Identifying relationship types Determining attribute domains Determining candidate, primary, and alternate keys Redundancy check for the conceptual model Conceptual model validation with users transaction Reviewing the conceptual data model with users 24 6 LOGICAL DATABASE DESIGN Definition Logical database design methodology Deriving relations for the logical data model Validating relations using normalization Validating relations against user transactions Checking integrity constraints Reviewing logical data model with user 32 7 DESIGNING SECURITY MECHANISM Database secutiy threats Computer-based controls Authentication and authorization Access controls Views Encryption Whitelist and blacklist 39 8 OVERVIEW OF THE PROJECT WORK Problems faced Error! Bookmark not defined. 8.2 Preparation and collecting specifications 40 9 SUMMARY ERROR! BOOKMARK NOT DEFINED. REFERENCES FIGURES Figure 1. A simplified diagram to illustrate the main phases of database design (Elmasri Navathe, 2011, p.201). 3 Figure 3. Sales view 16 Figure 4. Warehouse view 17 Figure 5. staff users view. 17 Figure 6. Domain pool for ID attributes possible value 21 Figure 7. UML diagram with entities and primary keys 22 Figure 8. Using pathways to validate conceptual model meets user transaction requirements. 23 Figure 9. UML data model showing all attributes at this stage of development phase. 26 Figure 10. UML diagram representing pk fk mechanism for relationships 29 Figure 11. Users Real world data flow diagram (DFD) 32 Figure 12. Modified using Entity-Relation (ER) method for global data model (Elmasri & Navathe, 2011,ch. 3) 33 Figure 13. Users' system environment 36 TABLES Table 1. Description of entities to form data dictionary (Connolly, 2005, p.444) 15 Table 2. Documentation of attributes and entities (Connolly, 2005, p.450) 19 Table 3. Associating entities with candidate keys, primary key and alternate keys 21 Table 4. Referential integrity constraints 31 Table 5. Threat documentation (Connolly, 2005, p.544) 35 Table 6. Login rule 37 Table 7. Blacklist and whitelist prototype table 39 NOTATION ak CEO DBDL DBMS ER ERD EERD fk IT pk RDBMS SIM sk UML Alternate Key Chief Executive Officer Database Design Language Database Management System Entity-Relationship Entity-Relationship Diagram Enhanced Entity-Relationship Diagram Foreign Key Information Technology Primary key Relational Database Management System Sales and inventory management system Secondary Key Unified Modelling Language 1 1. INTRODUCTION A distributor company, which is using paper pen format to keep their records till the day wishes to use computer technology to keep track of all its transactions and day to day operation to achieve its business goal. The company acquires products from the manufacturers and distributes them to the retail shop in the area. The business is basically buying and selling different kinds of consumer goods. The goal of the company is to make warehouse staffs work effectively efficient by using computer technology. Warehouse staffs were having problems all the time to keep track of the goods coming in and going out of the warehouse. The sales staffs are using papers for the orders they receive from the retailers. The problem with paper orders is that, they are all the time misplaced. On times when several sales persons need to see the same record, records being in paper format cause problems. These problems have increased the need for computer technology. The company at the moment is fully dependent on its staff skills of recording information using the paper and pen. This dependency of owner among the workers is wasting money and time. The designed system will be used to maintain the data that is used and generated to support the distribution operations of the company. Data stored in the system will be shared among the staff of the company to facilitate the cooperation and sharing of information between sales and warehouse staff. This thesis includes only the conceptual design of the computer system and not a complete implementation of the designed data model. The designed logical data model will be forwarded to the database programmer who will convert the logical data model into a fully working database with a user friendly interface. However thesis includes brief planning of database security and user authentication. 2 2. Sales and Inventory Management Database Design Process The aim of database design is to produce an integrated database which is accurate and secure. The objective is to achieve the business goal of the company in a more efficient way than in the current system. The process aims to design and develop an integrated database system which will support the application system effectively and efficiently. 2.1 Objective of the SIM system The company wanted to resolve all the current problems introducing a computer-based solution. A Sales and Inventory Management System was considered as the viable solution for the problems. Using the currently available SIM software would require technical staff to handle the software. SIM software available in the market today is expensive for the company to afford and maintain. In addition to the expenses of the SIM software, hiring a new IT member of staff to maintain the software would be more costly. Most of the SIM software available would not exactly meet the company s needs and requirements. Hence, the company decided to develop its own SIM system, naming it SIM plus which would meet all the needs and requirements of the company. SIM plus would be developed studying the company s requirements. Staff would be familiar about the system from the beginning of its development phase. The SIM plus system would serve the data storage needs of the company. The System will keep records of all the transaction made during the business and will provide for future references. The system will be designed and made ready for the implementation, studying the operational environment and needs of the company. The focus of thesis work is identifying user requirements and the development of system independent logical database design which will meet user requirements. 2.2 Sales and Inventory Management Database Design phase The database design process is divided into different sets of design tasks according to the design process being followed. This thesis work shall focus on the logical database design. The physical database design phase shall only be introduced. 3 2.3 Logical Database Design The focus on logical database design is to find the user requirements, study the existing system finding the problems users are having with the current system and study the company business environment. The outcome is the development of a system-independent description of a database that shall fulfil all the requirements. 2.4 Physical Database Design The physical database design is the actual implementation of the database into the system. The physical database design depends on the Relational Database Management System (DBMS) selected by the user or designer. It depends on the hardware and software environment. The graphical representation of the database design approach that is followed for this purpose is shown below:- Figure 1. A simplified diagram to illustrate the main phases of database designs (Elmasri Navathe, 2011, p.201). 4 3. Requirement collection and Analysis Knowing the requirements and needs of clients is vital to the success of a project. Hence we will be following some approaches to finding facts. The following are some of the most widely used fact-finding techniques used to find facts about the needs and requirements of the company:- Examining documentation Interviewing Observing the enterprise in operation Research Questionnaires. The purpose of using this fact-finding technique is to find out the aims and objectives of SIM plus from users point of view the different users view the system requirements and performances as well as security requirements The functional features company wishes to have in the system. 3.1 Requirements collections In this step, the methods used to collect the enterprise data requirements have been described Examining Documentation All the documents from the past operations of company were examined and studied. The following were the documents that were studied during the process:- a. Incoming order records b. Clients records c. Clients complaint records d. Inventory records e. Manufacturers records f. Products list g. Invoices h. Staff records. 5 From the study of the company s documentation, all the possible data and data types were recorded Interviewing and Questionnaires The CEO and all staff members currently employed in the company were interviewed. The purpose of the interviews was to gather further information on the problems the staff are facing and their possible solution from the new system that is to be developed. The interviews were conducted as a structured interview. All the interviewees were asked both open-ended as well as closed-ended questions. The following were the questions asked during the interview. Questions for the CEO a. What is your business about? We are a distributor company. We receive goods from different manufacturers and supply them to retailers. b. Why do you feel that you need computer technology? Currently we are keeping records in paper sheets. The problems with paper sheets are they get misplaced and records gets lost most of the time, to find one particular record we have to go through piles of documentation. We thought computer technology will help us to overcome this problem we are facing with paper records. c. Why don t you choose existing Sales and Inventory System? They are quite expensive to buy and maintain, needs to have one more IT staffs, which in our current situation seems more costly. Moreover, existing system does not exactly meet our needs despite of investing lots of money; they will still require further development and changes to meet our needs. d. How do you think, development of new system will solve your problem? This system will be developed according to our business environment. We can always put our thoughts and ideas in its development. We will familiar with it since the day of its development. We can develop it considering our future growth and requirements. 6 Questions for the warehouse manager a. What is your job description? Being a warehouse manager, my job mostly involves managing records of incoming and outgoing products from the warehouse. Observe the stock in warehouse, make reports and manage warehouse staff. b. What kinds of task do you perform in daily basis? Most part of my job includes managing records and keeping track of goods available in the warehouse and generate reports. I also have to notify the CEO when there is a change in the profit margin offered to us by the manufacturer. Labelling of products and organizing products in warehouse. c. What kind of data do you work with? I keep records of products from different manufacturers coming in warehouse. I keep records of orders sent to my sales personnel and dispatch the orders. I send re-ordering of products to our suppliers and manufacturers. d. What do your need to make your work easier and faster? A system where I can easily record all the products with proper descriptions. A system which enables to keep track of all products quantity in warehouse and alarms when stock is less than a certain amount. A system which generates incoming and outgoing products report graphically. Questions for the sales manager a. What is your job description? I manage the entire sales personnel for the company. I deploy our sales personnel to their local area to collect the orders from our client retailers. I collect all the sales orders and forward them to warehouse for the delivery. I keep records of all clients and possible clients in our business region and get us new clients. I make reports about the most ordered goods in certain time. b. What kind of task do you perform in daily basis? I deal with sales orders and our sales personnel. I deploy our sales personnel on a certain region based on weekly routines. I make calls and receive calls from clients and possible clients. 7 c. What kind of data do you work with? I work with sales orders which include products, quantity, amount, tax. I deal with clients inquiries about certain product details. I make frequent inquiries about the stock quantity for products in the warehouse. Using these fact finding techniques as described by Connolly in his book: A Practical Approach to Design, Implementation and Management, the requirements of the company were collected and analyzed. 3.2 Database Requirements Initial Database Size Currently the company employs in total eleven staffs, five sales staffs, two warehouse staffs a C.E.O and two delivery staff and an accountant. The company is working as a distributor for three different manufacturers. The number of products each manufacturer supplies are different from each other. All the products the company distributes shall be stored in the database of the SIM plus system Database Growth In future the company expects to acquire more distribution contract from other manufacturers and suppliers. Increases in the types of products from the manufacturers will result in increased data in the database. The company wishes to grow more acquiring distributorships from other manufacturers in the future. When company receives more manufacturers goods to distribute, the data that company shall handle will increase exponentially Searches and inquiry Most of the searches are made by the sales manager and sales staff. The frequency of queries will be high during the first business hour by the sales staff. The inquiry shall be less after the first business hour and will be high again during the mid-day. Warehouse staff will most likely check the records every afternoon before the closing so the chunks of data will be searched during that time. Networking and shared access requirements Every sales and warehouse member of staff will have their own workstations. Each workstation will be connected to the local server where the system database will reside. Each system user will access the system from the interface program installed in the local workstation. The system server will be connected only to the local intranet. The system can only be accessed from the local intra network Security The system will be password-protected. Each user will be assigned system access privileges appropriate to the particular user view. Staff members shall see only the data necessary to perform their job. The system will be disconnected from World Wide Web to prevent all possible hacking and cracking from the Internet. The system should only be accessed from local workstations. 3.3 Functional Requirements After the staff interviews and the study carried of the documents, the functional requirements of the company were identified. The functional requirements of the system vary for each user group. Hence the functional requirements of the system are categorized for each user. The following are the functional requirements of the system Administration Functional Requirements The CEO acts as the system administrator. System login function, with password change functionality after login Create new user of the system with staff details, limit their privileges according to their job des
Recommended
View more...
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks