14. 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:

27-1

 

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:

27-2

There are 200 client records in the table.

 

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

27-3

 

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

 

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

27-4

 

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: