Gambino Cleaning Products Salesman System Specification Essay

A new system of calculating and reporting on cleaning products salesmen is to be designed and implemented. Cleaning products salesmen commission is based on a set of data which is the monthly sales of cleaning products sold by the salesmen. Objectives The new system must: * Be automated as far as possible, needing no thorough knowledge of spreadsheets, databases or any other software in order to operate it. * Have fitted controls to ensure precision and completeness of data input. * Take into account commission payments from monthly sales. * Print commission reports for each of the salesmen, and a summary report showing total paid to each salesman and overall total. * Include an option to delete commission records previous to a given date so that the file does not increase for an indefinite period. The proposed new system can be shown in a level 0 data flow diagram as follows: A level 1 Data Flow Diagram of the proposed system is as follows: User functions 1. The Commissioning Salesman in the Sales Department will be responsible for entering all details of new cleaning products salesman and new products. 2. An Accounts clerk will have special responsibility for the commission system. 3. The Accounts clerk will enter each month the sales of each product, giving quantity sold and gross sales value (i.e. quantity X cover price). 4. Every 6 months, the Accounts clerk will produce the Commission reports for each cleaning products salesman. This will show the commission that has been earned in the previous 6 months. A payment is then recorded and a cheque issued to each cleaning products salesman to whom commission is due, together with the Commission statement showing sales details, and the statement from the Accounts system the total amount due. Database specification Menu structure The many functions of the Commission System will be accessed by means of a front-end menu, which will have the following structure. The data model There are three main entities in this system related in the entity relationship diagram shown below. Because a many-to-many relationship cannot be applied in a relational database, an additional entity named Salesman/Product is introduced. The ERD can now be drawn as follows: The database for Gambino cleaning products salesman system specification will have the following tables: tblSalesman (SalesmanID, Surname, FirstName) tblProduct (ProductID, ProductName, SalesTeam, Price) tblSalesman/Product (ProductID, SalesmanID, StandardCommission, BonusCommission) tblSales (SalesNumber, ProductID, Date, QuantitySold, GrossValue) Name Type Meaning Range Where Used tblSalesman Table Salesman Table tblProduct Table Product Table tblSalesman/Product Table Salesman/Product Table tblSales Table Sales Table SalesmanID Text (5) Unique ID Primary key in tblSalesman Foreign key in tblSalesman/Product Surname Text (20) Salesman’s Surname tblSalesman Firstname Text (15) Salesman’s first name tblSalesman ProductID Text (5) Unique product code Primary key in tblProduct Foreign key in tblSalesman/Product ProductName Text (40) Product title tblProduct SalesTeam Text (40) All salesman who sell a product tblProduct Price Currency Product price tblProduct StandardCommission Number Percentage commission on each product sold 1-10 tblSalesman/Products BonusCommission Number Bonus percentage commission on each products sold 11-20 tblSalesman/Products SalesNumber Number 1=Standard commission 2=Bonus Commission 1 or 2 tblSales QuantitySold Number Number of products sold tblSales GrossValue Currency Data Dictionary Input Specification Three input forms will be required. frmProducts This will be used to enter product details. frmSalesman This will be used to enter details of a salesman and all products on which they receive commission. The commission percentages for each product will be entered in a subform of the main form. Validation methods: The Product ID will be selected from a drop-down list of existing Product ID’s. Once a Product ID is entered, the product name will be automatically displayed. frmTransaction This form will be used to enter transactions. Validation methods: Only valid dates will be accepted. The Product ID will be selected from a drop down list of existing Product ID’s. Once a Product ID is entered, the product name will be automatically displayed. Selecting a radio button so that only a valid alternative can be selected will choose the transaction type. Process Specification Commission Calculations To calculate the commission for a given transaction, a query has to be performed to combine attributes from tblTransaction, tblProduct, tblProduct/Salesman and tblSalesman. The calculation of the commission is performed as follows: If TransactionType = 1 (i.e ‘sales’) ThenRate = StandardCommissionRate ElseRate = BonusCommissionRate endif Amount = Rate * GrossValue These processes will be carried out in qryCommission. This query will be the source of both the commission report and the summary report. Maintenance Each year, transactions prior to a given date will be removed from tblTransactions. This prevents the transaction file from becoming too large and slowing down the system. The maintenance will be carried out as follows: Run a Delete query to delete records from tblTransaction. Test Plan Tests will be carried out using valid and invalid data and data at the extremes of acceptable values.