• Home
• Exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
  • Reference 1
  • Reference 2
• Skill builders
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
• Slide exercises
  • Chapter 1
  • Chapter 2
  • Chapter 3
  • Chapter 4
  • Chapter 5
  • Chapter 6
  • Chapter 7
  • Chapter 8
  • Chapter 9
  • Chapter 10
  • Chapter 11
  • Chapter 12
  • Chapter 13
  • Chapter 14
  • Chapter 15
  • Chapter 16
  • Chapter 17
  • Chapter 18
  • Chapter 19
  • Chapter 20
  • Chapter 21
  • Chapter 22
  • Chapter 23
  • Chapter 24
• Support
  • Ask Rick
  • CD case
  • Classic Models
  • Glossary
  • Java
  • Labs
  • MySQL Workbench
  • Slides
  • SQL divide
  • SQL Playbook
  • Tables
  • XML
• Instructor
  • Contact
  • Manual

To test the performance of a database, first get some data. There are two alternatives to keying in a large volume of test data. First, existing files can be used as a source of test data and imported into appropriate tables. Second, test data can be generated for each table. Here, we discuss the use of Excel to generate test data. This exercise assumes considerable Excel experience.

Generating test data with Excel

The RAND function is very useful for generating random test data because it computes an evenly distributed random number r in the range 0 <= r < 1.

Generating a range of integers

When combined with the ROUND function, RAND can be used to generate random integers. The syntax for round is ROUND(value, digits), where value is the number to round and digits specifies the number of decimal places of the rounding (i.e., ROUND(5.4,0) equals 5). To generate an integer random number between a and b, use: ROUND(RAND()*(b-a) + a, 0)

Thus, to generate quantities sold in the range 1 to 5 use:

ROUND(RAND()*4 + 1,0)

Generating a range of dates

Excel stores a date as an integer, and days since some starting point are numbered sequentially. Thus to generate random dates in a year, you can add a random integer between 1 and 365 to the last day of the previous year. For example to generate a random date in 1997, enter the following in the designated cells:

If A2 is not a date, you need to set the format of the cell (Format>Cells ...)

Randomly selecting text from a list

Imagine that a company has locations in three cities (Atlanta, New York, and Chicago) and sales are distributed, respectively, in the following proportions: .4, .3,.3. To randomly select city names to match this distribution do the following.

• Enter the following data and define A1:B10 as CITY (Insert > Name >Define)

• Use RAND with VLOOKUP to select a name from the list.

VLOOKUP(value, table, column) where value is the item to be found in the first column of the array, table is the array, and column is the number of the column to be retrieved the table.

• Using the preceding table VLOOKUP(5, CITY, 2) will return New York
• VLOOKUP(ROUND(RAND()*9+1,0),CITY,2) will return a random city name from the table CITY.

Randomly selecting a pair of values from a list

Sometimes, you may want to select two values from a list (e.g., the description of an item and its price). VLOOKUP can be used as before. Consider the case where a company sells 10 items and the following price list is established. Here we would name two arrays: ITEM A1:B10 and PRICE B1:C10

To randomly select an item from ITEM, use:

VLOOKUP(ROUND(RAND()*9+1,0),ITEM,2)

Now, another lookup is required to match the selected item to its price. Assume the selected item (e.g., Hammock) is stored in cell X100, then the lookup command would be:

VLOOKUP(X100,PRICE,2)

This would look for the value of X100 (i.e., Hammock) in the first column of PRICE and return its price, which is stored in the second column.

Your turn

Generate data for the following table:

Hint

It is a good idea to create two worksheets.

1. The data used to generate the test data
2. The test data

Exercises

1. Generate 100 items (use Edit >Fill) and import the file into your database system and answer the following queries:
   • List all sales made during the first quarter of the year
   • How many cameras have been sold?
   • What is the total value of sales?
2. Generate 1000 items, import the file and repeat the queries. Is there a discernible difference in the time to execute the queries?

Date revised: 02-Dec-2022