The Data Center has developed the Station Information System (SIS) to manage station metadata for the California Integrated Seismic Network (CISN) Southern California Management Center (SCMC). The goal of this project was to develop a simplified database-driven system that can interact with a single database source to enter, update and retrieve station metadata easily and efficiently.

Over the course of this project, the SCEDC: redesigned the database schema, built a dynamic PHP website to allow users to view hardware and other station information held in the SIS database at: http://www.data.scec.org/stations/views/sta_hardware.php (this is also available from the "Display Station Hardware Information" link from our main Stations/Instrumentation page at: http://www.data.scec.org/stationinfo.html), built a Graphical User Interface to interact with the database, and have migrated all of the online broadband, K2 and short-period telemetered stations data into the SIS database.

All station field changes that result in a change of a station's response have been recorded in the SIS using the SIS GUI since 11/01/2005. Dataless SEED volumes are now generated from data held in SIS via a stored procedure, and all SEED volumes created since 11/01/2005 are from the SIS database. The IRIS DMC has verified the dataless volumes produced by this system.

Redesigned Database Schema

The SCEDC staff has considerable education and experience with databases, so we knew that we should invest significant time and effort on the data model, which has had a very positive impact on the end product. The SIS's highly normalized logical data model (an ERD is available from http://www.data.scec.org/stations/SIS/SIS_ERDV4.jpg) is implicitly designed for performance. If a database is not well modeled, it becomes clear to the applications and the users. The SIS's well-designed data model reduces the need for programming changes and increases application maintainability.

Normalization in a database design allows for efficient access and storage of data in a relational database. The purpose of the normalization process is to reduce redundancy (same information stored more than once) and secure data integrity (ensure that the database contains valid information). This is achieved by reducing large entities into several other, lesser entities, which together contains the same information without repeating it.

Every time a decision to denormalize the database is made, a price is paid. The cost is lost flexibility, future scalability, performance, and data integrity. By denormalizing, there is data redundancy in the database, which needs to be managed through program code, either at the GUI or by using triggers. Denormalization may solve one part of dealing with performance, but it creates possible performance problems in several other areas and data integrity is at high risk. A clean, normalized database always gives good performance and preserves data integrity.

Database Packages, Procedures and Functions

With this project, the SCEDC requested that embedded SQL statements NOT be allowed in applications. All SQL that is routinely executed was written as stored procedures and functions, contained in database packages. Database stored procedures for most common field operations (sensor swaps, new station installs) have been written and are used by the GUI. A user can record a sensor swap for a station and have all updates to gain and epochs for all relevant channels immediately available in the database in one step.

What are the benefits?

1. Programmers do not need to worry about the database structure, which is beneficial because most programmers don't like working with databases that are as well-designed as the SISDB.
2. Changes in the database structure/access paths do not influence application logic
3. Tuning of SQL is done independently of how many times (or places) this access path is in use
4. Stored procedures outperform any programmer's SQL - our DBA can write the statements to utilize indexes and performance improving hints that programmers typically aren't aware of.

SIS Graphical User Interface

The SIS interface is a Java program that directly accesses and updates the SIS database. The users requested that the design provide drop-down lists, radio buttons, and drill-down (nested tree) capabilities. For instance, a user can type in a minimum set of parameters and then assemble a station from drop-down lists of components in inventory (i.e., components that are not installed at other stations.). The use of drop-down lists, radio buttons, and forms that are pre-populated with default configurations have significantly reduced problems associated with typos and cut and paste errors. When a technician enters a new instrument into inventory, s/he can accept the nominal values for an instrument of that type, or can modify the values if desired. Field staff make their changes directly in the SIS interface and trigger a pre-compiled email to the operators' mailing list when the changes are submitted.

Process

During the development of this system, the Data Center met with many users of metadata to determine what their needs were for a Station Information System and we discovered that each user community had different priorities and used station data in different ways. We investigated a number of currently-existing systems that deal with station-metadata. We thieved the best ideas from areas that these systems did particularly well and developed strategies to avoid methods did not work well in some systems.

As our development progressed, we held regular bi-weekly meetings with the SCSN field technicians and showed them our progress and received feedback that helped guide our work to meet their needs. The filed staff provides two different styles of information to the SIS, so they have two function-based areas: one for fieldwork (Field Maintenance) and another tab for lab-based actions (Hardware Maintenance).

We went through a period of extensive testing including user-testing where all users of the SIS GUI were given a worksheet package containing scenarios for users to work through that tested the interface and determine if any modifications were necessary to improve the system.

Future Work:

• The Data Center will populate the SIS with as much information as we have available for historic stations. We have file cabinets of paper records for historical stations that have never been looked at, because the systems that we had in place were not flexible enough to allow entry of incomplete information or of non-standard station configurations.
• As a direct result of our SIS efforts, the SCEDC is finally in a position where we can effectively manage our metadata and easily exchange our metadata with our CISN partners and other organizations. We are looking forward to this next phase of SIS development.

Acknowledgements

The Station Information System project was financed with joint special funding to the Data Center from the USGS/ANSS and IRIS. This project has been a tremendous success, which we could not have achieved without this financial sponsorship. The SIS Gang sincerely thanks these organizations for their support.


I. Email Virus Alert

Recently there has been a worm spreading through emails that claim to be from the SCEDC. The message may appear to come from an address like webmgr@quakedc.gps.caltech.edu or postmaster@quakedc.gps.caltech.edu (but is actually forged) and have a subject line similar to "hi, ive a new email address." The body of the email may look like:

hey its me, my old address dont work at time. i dont know why?! in the last days ive got some mails. i' think thaz your mails but im not sure!

plz read and check ... cyaaaaaaa