A COMPONENTS DATABASE DESIGN AND IMPLEMENTATION FOR ACCELERATORS AND DETECTORS

Stanford Linear Accelerator Center*, Stanford, CA, 94309

* Work Supported by the U.S. Department of Energy under contract DE-AC03-76SF00515.

Abstract

Many accelerator and detector systems being fabricated for the PEP-II Accelerator and BABAR Detector needed configuration control and calibration measurements tracked for their components. Instead of building a database for each distinct system, a Components Database was designed and implemented that can encompass any type of component and any type of measurement. In this paper we describe this database design that is especially suited for the engineering and fabrication processes of the accelerator and detector environments where there are thousands of unique component types. We give examples of information stored in the Components Database, which includes accelerator configuration, calibration measurements, fabrication history, design specifications, inventory, etc. The World Wide Web interface is used to access the data, and templates are available for international collaborations to collect data off-line.

1 RATIONALE

In the early design stage of the PEP-II/BABAR Project Database, it was recognized that many very different kinds of measurements needed to be recorded for many different kinds of components of the accelerator and detector systems. At that time, it was decided that flexibility to store this diverse set of data was paramount in order to avoid redesigning the database for each set of measurements and each component type.

2 THE DESIGN

2.1 Items to be stored

The items to be stored describe the components, information about function and installation of components, the processes by which they are manufactured, refurbished, and measured, performance measurements which are recorded on paper and on-line travelers and information to track who did what and when it was done. This was distilled into the following datasets:

• Components
• Component-Types
• Travelers
• Traveler-Tasks
• Metrics
• Functions
• Installation-Data
• Parameters
• People - The people dataset was previously developed as part of other PEP-II/BABAR Project database systems[1] including meetings, drawings, and publications as well as basic personnel information.

This vertical design using separate tables linked together to describe components and metrics allows for the required flexibility to store the diverse data of the many accelerator and detector systems. Rather than creating a new table with different columns for each traveler, new travelers with different metrics for different components can be stored as new rows in these few tables without creating multitudes of tables with new columns for the all of the travelers.

The horizontal design would have, for example, a table with a column for magnet current, a column for coil temperature, a column for iron temperature, a column for ambient temperature and so forth. In the vertical design, that we use here, rather than columns for each metric, there are rows in the table PARAMETERS like:

• Magnet Current
• Coil Temperature
• Iron Temperature
• Ambient Temperature

Each of the metrics is linked to one of the rows of the PARAMETERS table. Creating a new kind of metric is as simple as adding a new line to the bulleted list above.

An Entity-Relationship Diagram of the PEPII/BABAR Components/Functions/Metrics database is shown in Figure 1.

Figure 1 -- Tables of the PEP-II/BABAR Components, Functions and Metrics Database

2.2 The datasets

• The Components dataset stores basic information generic to all components -- serial number, barcode number, component type and so forth. This is the COMPONENTS table.
• The Component-Types dataset contains a description of each kind of component. This is the COMPONENT_MASTER_LISTS table. Each record in the components table refers to one of these component-types. Examples are
  • HER Quadrupole - 550
  • HER Main Bend
  • LER Main Bend
  • BABAR Calorimeter Crystal
  • Drift Chamber Wire
• The Travelers dataset contains the names and identifying number of the each "process" to be performed on all components. These processes include manufacturing, refurbishing, and measuring the components. This is the TRAVELERS table. Each measurement refers to one of these travelers to describe the measurement Examples are:
  • BABAR Calorimeter Crystals / Performance Measurements with Phototube
  • HER Dipole Magnets / Resistance and Temperature Measurements
• The Traveler-Tasks dataset contains the steps of each Traveler. This is the TRAVELER_TASKS table. Each measurement refers to one of these traveler-tasks to describe the measurement. Examples are:
  • Resistance and Temperature Measurements at 10 Minutes
  • Integrated Gradient vs. Current
• The Metrics dataset contains all measurements of all components on all travelers. This is the COMPINFO_METRICS table. Columns include Date-Measured, Measured-By, Metric-Value and columns to refer to the other tables to describe the measurement.
• The Functions dataset contains a record for each "Formal Device Name" in the ring, as well as a few devices not in the beam-line. This is the FUNCTION_DEVICE_NAMES table. There is an FDN for each magnet, vacuum pump, vacuum chamber and other items that make up the accelerator. Each FDN may have an actual device assigned to it by a reference to the components table.
• The Installation-Data dataset contains information about the installation of the FDNs. This is the INSTALLATION_DATA table. This data includes dates when various items where installed, and notes by the installers. In addition, a history is maintained of components that have filled each slot.
• The Parameters dataset contains basic descriptions of measurements. This is the PARAMETERS table. The Metrics and Installation-data datasets refer to this table. Examples are:
  • Current
  • Voltage
  • Passed LY Test

• The People dataset contains basic personnel information about people. This is the PEPII_PERSONS table. Many other tables refer to this one to indicate who did something.
• The components/travelers/metrics database has been used for all sorts of components for the PEP-II ring as well as parts of the BABAR detector:
  • Calorimeter Crystals
  • Instrumented Flux Return Modules
  • Drift Chamber Wires

3 HOW IT IS USED

3.1 Loading

Due to the distributed, international locations where the data is collected, a method was needed to allow off-line collection of data. In order to accomplish this, an MS Excel template was developed that could be loaded by a loader program (Fig 2, Fig 3). With little modification to the template, any manufacturing, refurbishing or measuring traveler can be loaded. Some on-line data acquisition systems that were developed after the template was developed, were written to create data files in the format of the Excel template, thus avoiding the need to type the data into Excel. For those data files created before the template was developed, custom programs were created to load them.

Figure 2 -- Example Excel Input Template - Calorimeter Crystals Identification Traveler Figure 3 -- Example Excel Input Template - Calorimeter Crystals Phototube Traveler

3.2 Reporting

The World-Wide-Web is used extensively to make reports available to the international collaboration (Fig 4). UNIX utilities such as GNUPLOT are used to provide charts and histograms of data for display directly on the WWW browser (Fig 5). In addition, on any of the reports, the data can be downloaded to a tab-delimited file on the user's local disk drive. This tab-delimited file is suitable for loading into a spreadsheet program such as MS Excel for local calculations, and charting (Fig 6).

Figure 4 -- Example Generic Traveler Output on WWW Calorimeter Crystals Identification Traveler

Figure 5 -- Example GNUPLOT Output on WWW Calorimeter Crystals Uniformity Plots

Figure 6 -- Example Output from WWW as Tab-Delimited Data in a Spreadsheet File

4 LIMITATIONS

4.1 Query Time on World Wide Web

The use of the World Wide Web for reports leads to a limitation on run time for queries. Most WWW browsers will time out after 5 minutes have elapsed from the time the HTTP request is submitted to the server. Reports must be completed prior to expiration of the time-out period. Efforts to improve the efficiency of the SQL to alleviate the problem will not scale to hundreds or thousands of records. Using the CGI scripting language to retrieve a portion of the data and provide hyperlinks to see the "next page" and "previous page" is one solution.

4.2 Improving Query Time by Rotation of Tables

The use of the normalized "vertical" table design complicates the SQL even for "simple" queries as most of the tables must be linked in order to select the appropriate data, while simplifying the design of the database. Two solutions that may be applied once the data is loaded and stabilized are:

• Separate data into "subset" tables of identical design. Each logical set of data is copied to its own table to reduce the time to search for the required data -- if a given subset table has only data for a given traveler, then the traveler tables need not be linked.
• Rotate the data into custom-designed tables. This simplifies the queries by reducing the number of tables involved and the number of rows that must be fetched.

5 ACKNOWLEDGMENTS

The authors gratefully acknowledged the continued support of the PEP-II management and the assistance of various Technical Division departments.

6 OUR ADDRESS

The URL for the PEP-II/BABAR Project Database is:
http://www.slac.stanford.edu/accel/pepii/db.htm

REFERENCES

[1] A. Chan, G. Crane, I. MacGregor, S. Meyer, 'The PEP-II/BABAR Project-Wide Database Using World Wide Web and Oracle*CASE', Proceedings of 1995 International Accelerator Database Group Workshop, Argonne National Lab, November 6-8, 1995.