Gambino Cleaning Products Salesman System Specification Essay Example for Free

Gambino Cleaning merchandises Salesman ashes specification EssayA new remains of calculating and reporting on cleaning harvest-tides gross revenuemen is to be designed and implemented. Cleaning growths salesmen commission is based on a set of data which is the monthly sales of cleaning products sold by the salesmen.ObjectivesThe new system must* Be automated as out-of-the-way(prenominal) as possible, needing no thorough knowledge of spreadsheets, databases or any other softw atomic number 18 in sight to operate it.* Have fitted controls to ensure precision and completeness of data input.* Take into account commission payments from monthly sales. * brand commission reports for distributively of the salesmen, and a summary report showing total paid to each salesman and boilers suit total.* Include an option to delete commission records previous to a given date so that the record does not increase for an indefinite period.The proposed new system can be shown in a direc t 0 data flow diagram as followsA level 1 Data run Diagram of the proposed system is as followsUser functions1. The Commissioning Salesman in the Sales Department leave behind be responsible for entering all details of new cleaning products salesman and new products.2. An Accounts clerk depart eat up 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. each 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 run down 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 specificationMenu structureThe many functions of the Commissio n System will be accessed by means of a front-end menu, which will have the following structure.The data modelThere are three main entities in this system related in the entity family relationship diagram shown below.Because a many-to-many relationship cannot be applied in a relational database, an additional entity named Salesman/intersection is introduced. The ERD can now be drawn as followsThe database for Gambino cleaning products salesman system specification will have the following tablestblSalesman (SalesmanID, Surname, First bring up)tbl proceeds (ProductID, ProductName, SalesTeam, Price)tblSalesman/Product (ProductID, SalesmanID, StandardCommission, BonusCommission)tblSales (Sales act, ProductID, Date, QuantitySold, GrossValue)NameTypeMeaningRangeWhere UsedtblSalesmanTableSalesman TabletblProductTableProduct TabletblSalesman/ProductTableSalesman/Product TabletblSalesTableSales TableSalesmanIDText (5)Unique IDPrimary report in tblSalesmanForeign tombstone in tblSalesman/P roductSurnameText (20)Salesmans SurnametblSalesmanFirstnameText (15)Salesmans prototypal nametblSalesmanProductIDText (5)Unique product codePrimary key in tblProductForeign key in tblSalesman/ProductProductNameText (40)Product titletblProductSalesTeamText (40)All salesman who sell a producttblProductPriceCurrencyProduct 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 SpecificationThree 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 me thodsThe 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.frmTransactionThis form will be used to enter minutes.Validation methodsOnly valid 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 radio button so that only(prenominal) a valid alternative can be selected will choose the transaction type.Process SpecificationCommission CalculationsTo calculate the commission for a given transaction, a head has to be performed to combine attributes from tblTransaction, tblProduct, tblProduct/Salesman and tblSalesman.The unhurriedness of the commission is performed as followsIf TransactionType = 1 (i.e sales)ThenRate = StandardCommissionRateElseRate = BonusCommissionRateendifAmount = Rate * GrossValueThese processes will be carried out in qryCom mission. This query will be the source of both the commission report and the summary report.MaintenanceEach year, transactions prior to a given date will be removed from tblTransactions. This prevents the transaction file from fitting too large and slowing down the system. The maintenance will be carried out as follows tryout a Delete query to delete records from tblTransaction.Test PlanTests will be carried out using valid and invalid data and data at the extremes of acceptable values.