Stanford Linear Accelerator Center*, Stanford, CA, 94309
* Work Supported by the U.S. Department of Energy under contract DE-AC03-76SF00515.
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.
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:
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:
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
3 HOW IT IS USED
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
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.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:
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:
 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.