Page 236 - Mechanical Engineers' Handbook (Volume 2)

5 Data Storage 225

specific time frames that go across file boundaries, or relationships between variables that
may not be effectively evaluated within the time frame of one file. The analysis task usually
consists of importing a number of files into some analysis tool and then using the analysis
tool to look for relationships. This means that the importation process has to include the
organizing of data and identifying relationships between events, a difficult task at best. A
common tactic is to import data via a script or macro for a given time range, so the user
only has to specify a beginning and ending time.

5.3 Database Storage
Database technology has been improving for many years, resulting in database management
systems being increasingly the storage tool of choice for data acquisition systems. Database
management systems provide organization tools, compression of data, access aids in the
form of indexes, and easy access for analysis tools. A special benefit of database management
systems is that they allow the combination of discrete data and time-based data collected on
different time intervals. Relational databases are now the dominant database management
system type. Data are organized in tables. Each table is composed of a set of rows, each
row having a fixed set of columns. Indexes are provided to speed access to data. In data
acquisition systems, the designer often adds a time stamp column to each row to facilitate
retrieval and analysis of data. An example of a simple database is given in Table 5.
The TimeData table contains data that are sampled every second, while the BatchEvent
table contains a record for each batch that has occurred. The SQL language is a common
language used to examine and extract data in the tables. An example of its power is that a
query can be constructed to use the BeginBatchTime and EndBatchTime to extract data from
the TimeData table and combine it with related batch events in the BatchEvent table. A
sample query to combine event- and time-based data is as follows:
Select BatchEvent.Timestamp, Batchevent.BatchNumber,
Batchevent.MixPercent,
Min(TimeData.TemperatureA),Max(TimeData.TemperatureA)
From BatchEvent, TimeData
Where BatchEvent.OutputProductType ’BENCH’ and TimeData.Timestamp
between BatchEvent.BeginBatchTime and BatchEvent.EndBatchTime
Group by BatchEvent.BatchNumber
Order by BatchEvent.BatchNumber
The above query searches for batches that created a certain output product type
(BENCH) and reports the maximum and minimum temperatures from those batches. This

Table 5 Example Database Structure
Time Data Table Batch Event Table
Timestamp datetime Timestamp datetime
TemperatureA float BatchNumber integer
TemperatureB float MixPercent float
TemperatureC float InputMaterialAQty integer
Rate1 integer InputMaterialBQty integer
Rate2 integer OutputProductType varchar(50)
Rate3 integer OutputProductQty integer
BeginBatchTime datetime
EndBatchTime datetime