Prepare Test Data for Sales Management System

l  Problem

To prepare the simulation data for a sales management system, you are required to generate 4 tables (test_Sale table, test_Client table, test_Product table, and test_Contract table) with the data requirement as given below:

 

test_Sale , the salesperson table, is structured as given below:

 

In the table, there need to be 50 salespersons (Name and ID), 5 areas (Area) , namely, NorthChina, SouthChina, CentralChina, SouthwestChina and NorthwestChina, and 4 educational backgrounds (Education) - Senior High School Degree, Associate Degree, Bachelor degree, and Master degree.

 

test_Client, the client table, is structured as given below:

There are 200 client records in the table.

 

test_Product, the product table, is structured as given below:

 

There are records about 30 types of products in the table.

 

test_Contract, the sales contract table, is structured as given below:

 

There are 10,000 sales records for the whole year of 2009.

 

l  Tip

General steps: There are two key problems on constructing the test data: first, how to input a mass of data; second, how to guarantee that the data to be inserted has some degree of randomness. You can use esProc’s loop statement and insert function to solve the first problem and the rand function to solve the second problem.

1.  Construct a test_Sale table. The ID field can be populated with numbers from 1 to 50. The Name field valuescan be generated randomly using chn function. The values of the Education and Area fields are randomly selected from given options with the rand function.

2.  Construct a test_Client table. The ID field can be filled with serial numbers; the values of the Name field are generated randomly; the Contact and Address fields are not important so you can just use multiple “-” as values. The Phone field is not important either, and you can write numbers randomly.

3.  Construct a test_Product table. The rule for ID and Name fields are to the same as the preceding two tables. The values of the Price field can be generated by calling the rand function, taking 1,000 as the lower limit so as to increase the sense of reality.

4.  Construct a test_Contract table. The values of the ID field are serial numbers too. The values of the Client, Product, and Sales fields are picked randomly with the rand function from the above tables generated, from which the ID field values are randomly retrieved. The values of the SellDate field can be generated with the relDate and rand function to guarantee that the values are within the year of 2009. The values of the Quantity field are randomly picked out within a certain range. In this case, they are picked from 1 to 5.

5.  Create the corresponding tables in the database, and write the resulting table sequences into the database.

 

l  Code

	A	B	C
1	[NorthChina,SouthChina,CentralChina,Southwest,Northwest]		/The Area sequence
2	[Senior High School,Associate Degree,Bachelor,Master]		/The Education sequence
3	50		/The number of salesperson
4	=create(ID,Name,Education ,Area)		/The test_Sale table
5	=A4.insert(0:A3,~,"Salesperson_"+string(#),A2(int(rand()*A2.len()+1)),A1(int(rand()*A1.len()+1)))		/Insert a record about the salesperson
6	200		/The number of clients
7	=create(ID,Name,Contact,Address,Phone)		/The test_Client
8	=A7.insert(0:A6,~,"Client"+string(#),"---","------","87654321")		/The record about a client
9	30		/The quantity of products
10	=create(ID,Name,Price)		/The test_Product table
11	=A10.insert(0:A9,~,"Product"+string(#),int(rand()*90)*100+1000)		/To insert a record about a product
12	1000		/The quantity of contracts
13	=create(ContractNo,Client,Product,Sale,SellDate,Quantity)		/The test_Contract table
14	for A12		/Insert 1000 records in a loop way
15		=A7(int(rand()*A7.len()+1)).ID	/Randomly get the ID of a client
16		=A10(int(rand()*A10.len()+1)).ID	/Randomly get the ID of a product
17		=A4(int(rand()*A4.len()+1)).ID	/Randomly get the ID of a salesperson
18		=elapse("2009-1-1",int(rand()*365)+1)	/Randomly generate a date within the year of 2009
19		=A13.insert(0,A14,B15,B16,B17,B18,int(rand()*5)+1)	/Insert a record about a contract
20
21	/Insert the table sequences generated into the txt
22	>file("C:\\test_Sale.txt").export@t(A4)
23	>file("C:\\test_Client.txt").export@t(A7)
24	>file("C:\\test_Product.txt").export@t(A10)
25	>file("C:\\test_Contract.txt").export@t(A13)
26	=file("C:\\test_Sale.txt").import@t()		/Browse the test_Sale table
27	=file("C:\\test_Client.txt").import@t()		/Browse the test_Client table
28	=file("C:\\test_Product.txt").import@t()		/ Browse the test_Product table
29	=file("C:\\test_Contract.txt").import@t()		/ Browse the test_Contract table

 

l  Result

 (Since data are picked randomly, the data generated after each run may vary.)

The test_Sale table is as below:

 

The test_Client table is as below:

 

The test_Product table is as below:

 

The test_Contract table is as below: