8.2 The Light-Bulb Factory Example

The recipes in this chapter all make use of some sales and quality-assurance data from a hypothetical light bulb factory. This factory is building a quality-support system to store and analyze light-bulb usage data. Managers from every product line send small quantities of light bulbs several times a day to a testing lab. Equipment in the lab then tests the life of each bulb and stores the resulting data into a SQL database. In addition to the bulb-life data, our company also tracks sales data on a monthly basis. Our job is to build queries that support basic statistical analysis of both the bulb-life test data and the sales data.

8.2.1 Bulb-Life Data

Bulb-life test data is stored in the following table. Each bulb is given a unique ID number. Each time a bulb is tested, the test equipment records the bulb's lifetime in terms of hours.

CREATE TABLE BulbLife (
   BulbId INTEGER,
   Hours INTEGER,
   TestPit INTEGER
)

The test floor is divided into several test pits. In each test pit, a number of light bulbs can be tested at the same time. The table stores the number of hours each light bulb lasted in the testing environment before it burned out. Following is a sampling of data from two test pits:

BulbId      Hours       TestPit     
----------- ----------- ----------- 
1           1085        1
2           1109        1
3           1093        1
4           1043        1
5           1129        1
6           1099        1
7           1057        1
8           1114        1
9           1077        1
10          1086        1
1           1103        2
2           1079        2
3           1073        2
4           1086        2
5           1131        2
6           1087        2
7           1096        2
8           1167        2
9           1043        2
10          1074        2

8.2.2 Sales Data

The marketing department of our hypothetical light-bulb company tracks monthly sales. The sales numbers are stored in a table named BulbSales and are used to analyze the company's performance. The table is as follows:

CREATE TABLE BulbSales(
   Id INT IDENTITY, 
   Year INT,
   Month INT,
   Sales FLOAT
)

The following records represent a sampling of the data from the BulbSales table:

Id           Year         Month       Sales                                                 
----------- ----------- ----------- --------- 
1           1995        1           9536.0
2           1995        2           9029.0
3           1995        3           8883.0
4           1995        4           10227.0
5           1995        5           9556.0
6           1995        6           9324.0
7           1995        7           10174.0
8           1995        8           9514.0
9           1995        9           9102.0
10          1995        10          9702.0

You can download a file from the book's web site that contains the complete set of sample sales data used for this chapter's examples. Figure 8-2 shows a graphical representation of this sales data. The company produces specialized light-bulb equipment and has three major customers. Historically, all three customers generally order three-months worth of supplies every three months and at approximately the same time. The sales record, therefore, shows a peak every three months. A six-month moving average can be used to smooth out this data and show the long-term trend. You see this moving average as the smoother line on the chart.

Figure 8-2. Monthly light-bulb sales

The chart in Figure 8-2 is only an example of the type of statistics that need to be produced from the sales and quality-assurance data. You'll see how to generate the moving average needed to create this chart in a recipe later in this chapter.