Inventory, Ordering and Sales Management Database Design Process - Report Example

Week 6 Project Deliverable 3 al Affiliation) PART Inventory, Ordering and Sales Management Database Design Process The chief objective of the particular design is to ensure the production of an integrated database that guarantees security and accuracy of the process that are being incorporated in the model. The main objective is to guide and foster the achievement of the company’s business goal in an efficient way while reducing the errors and redundancies that may exist. Introduction The system that is going to be used and adopted in the organization is going to comprise of many elements in the form of tables since it involves the synchronization of three information systems that work in unison in order to facilitate the business operation. The database is going to be designed based on the various requirements that are to be integrated in the Order management System, Inventory System and the Supply processing system. The general idea for the implementation of the database schema would adopt a general flow basing on the fact transactions in a certain business entity take a general format of: 1. A customer will sign into the portal. 2. If the customer is new, he or she will be required to sign up into the system providing the relevant and necessary details that are required for the unique identification of the client 3. The customer will then have an access to the list of products that exist. 4. The products will be able to show if they are available in terms of quantity. 5. This process therefore facilitates the ordering process. 6. The ordering process will capture the essential requirements of the customer for instance the customer name, physical address, contact information, the type of goods that have been ordered and their specific quantities. 7. The ordering process be able to forward a report to the supply processing system where stock issues will be done and will be able to make the necessary adjustments on the stock level quantities upon retrieval from the stores Database Design Phase The database design process will be divided into different autonomous design tasks relative to the followed design process. Particular emphasis will be laid on the logical database design and the physical database design (Teorey et al, 2011). Logical Database Design The logical database design main objective is to determine the relevant user requirements, examination of the process flows of the existing system with an aim of identifying the challenges that are faced by the user of the system. It is also aimed at studying the specific business environment of the company. Therefore, the expected outcome will be the emergence and formulation of a system-independent database description that meets the key requirements (Teorey et al, 2011). Physical Database Design It represents the database actual implementation process in relation to the system that exists. The physical database design is dependent on the RDBMS (Relational Database Management System) which is adopted by the user (Teorey et al, 2011). The software and the hardware environments greatly influence the structure. General Database structure that is adopted by the specific study is: Figure 1: A representation of the key phases involved in the database design Requirements Collections The process and the design is determinant on the information acquired from the Staff records Invoices Product Lists Investor lists Inventory records Client records Client complaint records Incoming Order Records Users of the System The system will be used by different personalities of which important roles will have to be allocated and assigned to. The design of the database will be dependent on the various key roles performed by the individuals (Teorey et al, 2011). These individuals who rely on the system include: Warehouse manager Sales Manager Order process manager Customer Database Requirements Initial Database Size The company comprises of different representatives across different departments where the systems are integrated. The company also has various supplies. The products supplied by the different suppliers differ. All the products that exist in the inventory are all stored in the database (Teorey et al, 2011). Database Scalability The company will intend to increase the number of suppliers and manufacturers. The company will also intend to increase the types and number of products that will be distributed to the number of rational consumers that exist (Teorey et al, 2011). When the company will acquire more customers and increase its product range, then the size of the database will increase. Searches and Queries Majority of the searches and the queries will be conducted by the sales staff and the sales manager. The query rate by the sales staff will be high during the initial business hour. The query rate will reduce and the rate increase again at midday (Teorey et al, 2011). The staff at the warehouse will be conducting searches of records every afternoon before closing the books of records. Shared Access and Networking requirements Different workstations shall be created for the inventory or warehouse staff and the sales staff. The workstations will be connected to a centralized local server which will host the system database. The local workstations will comprise of installed interface programs which will facilitate accessibility to the system by the system users (Teorey et al, 2011). The system server will run and obtain connections to the local intranet hence making accessibility only limited to the local workstations. There will also be a web platform which will ensure accessibility to the users where product orders will be placed. Security Passwords will form the basic protection mechanisms of the system. System access privileges will be allocated to each user of the system. The system users will only obtain accessibility to the information that they are permitted to view only (Teorey et al, 2011). The system will completely be disconnected from the internet so as to protect the local workstations against possible cracking and hacking. Functional Requirements These functionalities differ in relation to the user groups. The system will have the following functional requirements: System Administrator Functional Requirements System Login Password can be changed upon first login Ability to create new users of the system and allocate privileges Ability to add new items and create new categories in the system product list Can edit and update various product prices Can remove items and categories from the inventory with the necessary precaution messages observed System users can be deleted and updated Sales Manager Functional Requirements System login Password can be changed upon first login Can view the status of the inventory Can be able to search a product through the search function using product code, product name or product category Can be able to check daily generated bills Cab be able to check the transactions involving money that have been conducted during the day. Cancelled bills can also be checked Returned products can also be checked Sales-trend graphs can also be generated Inventory Management Functional Requirements System login Password can be changed upon first login Product details and prices can be added into the system. Inventory status can be checked in the system in order to determine the maximum and minimum stock points and the order point Can be able to conduct updating of the inventory with regards to the sales that have been conducted during the previous day Generation of inventory reports with regards to prices and category Inventory-trend graphs can be generated Sales Personnel Functional Requirements System login Password can be changed upon first login Products can be searched using their product names, categories or code Generation of the purchase order through the retrieving of the order details. Cancellation of various bills in case of errors arising Entering of the product details for the specific return orders Users’ Transaction Requirements Data Entry will involve the entry of the following specific details: Supplier details Product Details Product category Details New and existing details of staff Customer details Order ordered details Dispatched order details Payment details Data update or deletion will affect the following entries: Staff details Product Details Product Category Details Order order details Customer details Supplier details Payment details Data query will be important when finding out the following information in list form: List of staff details List of individual staff roles List comprising of supplier details List of each product detail Products in stock Products about to reach the minimum stock level Products that have hit the re-order level Products by category Available category details Customer details Incoming order details Dispatched orders Payment details affecting recent orders Credit payments together with the customer details Order numbers total Orders that have been paid in full with no balances. Functional Analysis The general flow of data and information in a system is usually represented as: Input  Process  Output Input It represents the type of data that will be fed into the system by the system user. The system users will feed different types of data and they will include the following: Login usernames, passwords and new passwords New User details Details of products and categories Details of incoming products Details of orders Cancelled bills Details of returned products Process Username and password authentication Save alterations to the database arising from the processes of adding, deletion, updating Database search Bill generation of sales item Cancelled bills generation in the event that errors occur Generation of invoices to complement purchase orders Returned purchases bills and reports Inventory stocks validation Reports on order points and maximum inventory levels Output Majority of the output in this system will be in the form of reports and they will include: User information Client information Product details upon query Due payment reports covering a specific duration of times Inventory graphs and trends Sales and purchases reports Minimum, maximum and stock re-order levels Conceptual Database Design Conceptual Views Sales View Rationale: In order for a sale to be conducted, a customer must place an order for and available product. Once the items availability has been confirmed, transactions in the form of payment must exist in order for delivery to take place. An order is specifically attached to a products. Many products can be requested through one order. A customer can have many orders. Payments can only be done by one customer irrespective of the number of orders Inventory/ Warehouse View Rationale: A warehouse can store many product. The products exist in different categories. These category of goods are supplied by different manufacturers or suppliers ER Diagram Showing entities and their primary keys Staff user’s view Rationale: The process consists of different entities that are dependent on each other’s operations. Entity Description Alias Occurrence Staff Refers to all the people in the company Employee Sales and warehouse representatives Product Refers to all the commodities the company will be selling Distributing items Individual differentiated products together with supplier Supplier These are manufacturers who supply items to the company for sales and distribution Manufacturer Each supplier with each product Customers Individuals or retail shops Individuals and retail shops Many individuals and retail shops Orders Item and quantity list as requested by a customer Customer orders Many customer orders Payments Money exchange as a show of purchase Full of partial payments by customer Partial or full payments These entities form the basic operation involved in the whole process. They therefore relate using unique identifies called primary keys which facilitate information flow in the business cycle. Attributes, association and identification The process of database design will therefore comprise of tables and field/ attributes which will relate with each other. TABLE NAME (ENTITY) FIELDS (ATTRIBUTES) PRIMARY KEYS Staff StaffID FName LNAme DOB Addrs Sex PhoneNo roleID username password StaffID Role roleID roleName Descr roleID Customer CID BRegNo FName LName Address Phone Email StaffID CID Order OrderID StaffID CID Odate ErrMsg CID ODetail OrderID BillNo ODetaIlID ProductID USP Size OrdQuant DelQuant Discount Total DelDate OrdDAte ODetaIlID Payment BillNo PayType CrAmount CrDate DrAmount DrDate Balance BillNo Product ProductID PName PDescr CatID supplierID QPerUnit Uprice USP Uweight Usize Discount UInStock UInOrder ReOrLevel Note ProductID Category CatID CatName Desc CatID Supplier SupplierID ComName ConfName ConlName ConTitle Address Phone Fax Email PayMeth DisType SupplierID UML Diagram showing entities and primary keys Database Schema Validating relations through normalization The principle aim of normalization is to regulate the entity attributes that support the data requirements. Data redundancy is minimized and update anomalies are solved using this technique. However, some redundancies become vital in the formation of joints which form a transition of relationships between entities. The database entities have been normalized to the third normal form (Teorey et al, 2011). Real World Data Flow Diagram The logical model achieved in this particular scenario is tallied with user experience in the real world to ensure that basic processes have been captured. The data flow diagram that is represented above depicts real time data flowing in the enterprise and saved in the database (Teorey et al, 2011). PART 2 UPDATED GANNT CHART Activity Start End Jan Feb Mar Apr May Jun Jul Aug Sept Oct Nov Dec Requirements collection Jan Feb Analysis Feb Mar Logical Database Design Mar Apr Physical Database Design Apr May Development May July Implementation Jul Aug Rollout August References Teorey, T. J., Lightstone, S. S., Nadeau, T., & Jagadish, H. V. (2011). Database modeling and design: logical design. Elsevier. Read More