Gambino Cleaning Products Salesman System Specification Essay

A new organisation of calculating and reporting on cleansing products salesmen is to be designed and implemented. cleaning products salesmen commission is based on a set of selective in abidanceation which is the periodical sales of cleaning products sold by the salesmen.ObjectivesThe new ashes must(prenominal)* Be automated as far as possible, needing no entire knowledge of spreadsheets, databases or any other softw atomic number 18 in request to operate it.* Have fitted controls to ensure precision and completeness of data input.* Take into narration commission payments from monthly sales.* Print commission reports for all(prenominal) of the salesmen, and a epitome report showing total paid to all(prenominal) salesman and over either total.* hold an option to delete commission records previous to a given participation so that the file does not increase for an indefinite period.The proposed new system plenty be shown in a level 0 data flow diagram as followsA level 1 data Flow Diagram of the proposed system is as followsUser functions1. The careing Salesman in the Sales Department go out be responsible for acquiesceing all details of new cleaning products salesman and new products.2. An Accounts clerk go forth open special responsibility for the commission system.3. The Accounts clerk leave enter each month the sales of each product, giving quantity sold and pure(a) sales value (i.e. quantity X cover price).4. Every 6 months, the Accounts clerk bequeath produce the Commission reports for each cleaning products salesman. This lead 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 bid showing sales details, and the statement from the Accounts system the total amount due.Database specificationMenu structureThe legion(predicate) functions of the Commission System will be accessed by m eans of a front-end menu, which will have the next structure.The data modelThere are three main entities in this system related in the entity descent diagram shown below.Because a many-to-many relationship cannot be applied in a relational database, an additional entity chance upond Salesman/ harvest-tide is introduced. The ERD can now be cadaverous as followsThe database for Gambino cleaning products salesman system specification will have the following tablestblSalesman (SalesmanID, nickname, FirstName)tbl increase (ProductID, ProductName, SalesTeam, Price)tblSalesman/Product (ProductID, SalesmanID, StandardCommission, BonusCommission)tblSales (Sales telephone number, ProductID, Date, QuantitySold, GrossValue)NameTypeMeaningRangeWhere UsedtblSalesman remitSalesman TabletblProductTableProduct TabletblSalesman/ProductTableSalesman/Product TabletblSalesTableSales TableSalesmanIDText (5)Unique ID immemorial key in tblSalesmanForeign key in tblSalesman/ProductSurnameText (20)Salesm ans SurnametblSalesmanFirstnameText (15)Salesmans first nametblSalesmanProductIDText (5)Unique product codePrimary key in tblProductForeign key in tblSalesman/ProductProductNameText (40)Product calltblProductSalesTeamText (40)All salesman who sell a producttblProductPrice moneyProduct pricetblProductStandardCommissionNumberPercentage commission on each product sold1-10tblSalesman/ProductsBonusCommissionNumberBonus percentage commission on each products sold11-20tblSalesman/ProductsSalesNumberNumber1=Standard commission2=Bonus Commission1 or 2tblSalesQuantitySoldNumberNumber of products soldtblSalesGrossValueCurrencyData DictionaryInput Specification iii input forms will be required.frmProductsThis will be used to enter product details.frmSalesmanThis 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 methodsThe Product ID will be selected from a discount-down list of existing Product IDs. Once a Product ID is entered, the product name will be automatically displayed.frmTransactionThis form will be used to enter transactions.Validation methodsOnly legal dates will be accepted.The Product ID will be selected from a drop down list of existing Product IDs. Once a Product ID is entered, the product name will be automatically displayed.Selecting a radiocommunication button so that only a valid alternative can be selected will choose the transaction type.Process SpecificationCommission CalculationsTo 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 followsIf TransactionType = 1 (i.e sales)ThenRate = StandardCommissionRateElseRate = BonusCommissionRateendif come in = Rate * GrossValueThese processes will be carried out in qryCommission. This query will be the source of both the commission report and the summary report. precautionEach year, transactions prior to a given date will be removed from tblTransactions. This prevents the transaction file from becoming too bouffant and slowing down the system. The maintenance will be carried out as followsRun a Delete query to delete records from tblTransaction.Test computer programmeTests will be carried out using valid and invalid data and data at the extremes of acceptable values.