ATS SPC's database is based on Microsoft's 'Jet' database engine (v4) as used in Microsoft Access 2000. This means that you can use Access to look at the data you have collected in ATS SPC and to produce customised reports etc. This chapter describes how ATS SPC's data is structured and describes, in principle, how to use queries to find the data you need. The use of Microsoft Access itself is beyond the scope of this manual and you should refer to the appropriate Microsoft documentation if you are unfamiliar with Access in particular or relational databases in general. However the chapter is structured to introduce concepts gradually and, even if you have little previous knowledge of relational databases or the Structured Query Language you should still find it useful.
A relational database stores data in 'tables' where each table holds information about a particular type of object, a customer in your accounts system for example. The information about a particular customer in the database is known as a 'record' – there will be as many records as customers. Each record has one or more 'fields' which each hold a single item of information about the customer.
It is often useful to think of a database table as a number of rows and columns – here each row is a single customer and each column is a field – a piece of information about that customer – his name or telephone number for example.
Related objects are stored in their own tables. For example, in an ordering system there might be a table for customer details, a table for orders placed, and another for the items in each order. Dividing the data this way saves space and repetition – you don't have to type in the customer's name and address again each time he places an order or adds an item to it, you just refer to the customers table.
One customer may place many orders and each order will contain a number of different items. This defines a 'one-to-many' relationship between customers and orders and between orders and items. A diagram shows this more clearly.
The crow's foot end of the link lines shows the 'many' end of the one-to-many relationship while the plain, single, end shows the 'one'.
This is a designer's view of the database with each table clearly shown in relation to other tables and each field shown with the type of data it holds – number, text string, date etc. etc.
Items in one table may be related to items in another by a 'key' field. You can find a list of items that belong to a particular order by searching the database for item records that have an OrderID value equal to that of the order record. The Orders table uses the OrderID field as its key. A relational database manager, such as Microsoft Access, provides powerful functions for searching the database rapidly and for building 'queries' which combine data from a number of tables, effectively forming temporary tables from which you can extract the combined data records.
ATS SPC uses a table for batches, another one for features, and another one for data. There is a one-to-many relationship between batches and features and between features and data. One batch may have several features and each feature may have lots of measured data values. This gives the hierarchical structure shown on the left.
Features are linked to a parent batch record by having a BatchID value equal to the same field in the batch record. Data records are linked to features in the same way.
In practice ATS SPC's database has a little more to it than that since it allows for different types of feature and for storing statistical results for easy reference. In detail the batch-feature-data hierarchy looks like this.
The features table is effectively split into two tables with a one-to-one relationship and the data belongs now to the VarFeatures table rather than directly to features. There is also a statistics record which ATS SPC doesn't use itself but updates as data is entered. This is to allow database reporting programs to obtain statistics results without having to mimic ATS SPC's own statistical formulae.
This splitting arrangement allows for future support of other types of feature – say attribute features, which might also require a different type of data record as well.
Naturally there is even more to it than this – we haven't yet looked at logbook messages, data tags or batch tags. A full database structure diagram is given later in this chapter but you now know enough about it to begin writing queries to extract the data you want.