Citation
A FRAMEWORK FOR SPECIFYING AND GENERATING ALERTS IN RELATIONAL MEDICAL DATABASES

Material Information

Title:
A FRAMEWORK FOR SPECIFYING AND GENERATING ALERTS IN RELATIONAL MEDICAL DATABASES
Copyright Date:
2008

Subjects

Subjects / Keywords:
Database design ( jstor )
Databases ( jstor )
Drug interactions ( jstor )
Java ( jstor )
Knowledge bases ( jstor )
Metadata ( jstor )
Oracles ( jstor )
Personal names ( jstor )
SQL ( jstor )
Syntax ( jstor )

Record Information

Source Institution:
University of Florida
Holding Location:
University of Florida
Rights Management:
Copyright the author. Permission granted to the University of Florida to digitize, archive and distribute this item for non-profit research and educational purposes. Any reuse of this item in excess of fair use or other copyright exemptions requires permission of the copyright holder.
Embargo Date:
5/4/2003
Resource Identifier:
70786465 ( OCLC )

Downloads

This item is only available as the following downloads:


Full Text

PAGE 1

A FRAMEWORK FOR SPECIFYING AND GENERATING ALERTS IN RELATIONAL MEDICAL DATABASES By SHANKAR N. MANAMALKAV A THESIS PRESENTED TO THE GRADUATE SCHOOL OF THE UNIVERSITY OF FLORIDA IN PARTIAL FULFILLMENT OF THE REQUIREMENTS FOR THE DEGREE OF MASTER OF SCIENCE UNIVERSITY OF FLORIDA 2002

PAGE 2

Copyright 2002 by Shankar N. Manamalkav

PAGE 3

ACKNOWLEDGMENTS I would like to thank Dr. Douglas Dankel and Dr. Douglas Benn for their encouragement in starting this work and their contribution towards this thesis. I also wish to thank Dr. Cistrom for discussions and valuable comments. iii

PAGE 4

TABLE OF CONTENTS page ACKNOWLEDGMENTS.................................................................................................iii LIST OF TABLES............................................................................................................vii LIST OF FIGURES.........................................................................................................viii ABSTRACT.........................................................................................................................x CHAPTER 1 INTRODUCTION..........................................................................................................1 The Concept...................................................................................................................1 Organization of the Thesis.............................................................................................4 2 TECHNOLOGY OVERVIEW.......................................................................................5 Technology Used in WEB-SAM...................................................................................5 Database Independence with JDBC.........................................................................5 Java Server Pages (JSP)...........................................................................................7 Optimizing Database Performance..........................................................................7 Stored procedures...............................................................................................8 Prepared statement...........................................................................................10 Understanding JDBC Metadata.............................................................................11 Component development in Java using JavaBeans................................................12 Clinical Alerting Systems in Use.................................................................................13 Clinical Event Monitor at Columbia-Presbyterian Medical Center (CPMC)........13 Clinical Event Monitoring (CLEM) at the University of Pittsburgh.....................13 The New York Weill Cornell Medical Center (NYWCMC) Electronic Medical Record....................................................................................................................15 Summary......................................................................................................................15 3 SYSTEM DESIGN.......................................................................................................16 Overview......................................................................................................................16 Design Goals................................................................................................................17 Requirements...............................................................................................................17 Knowledge Base Maintenance...............................................................................17 Event Triggering Mechanism.................................................................................19 iv

PAGE 5

Flexibility and Ease of Use....................................................................................19 Data Requirements.................................................................................................19 Error Handling and Reporting................................................................................22 Security Requirements...........................................................................................22 System Walkthrough....................................................................................................23 Summary......................................................................................................................29 4 SYSTEM IMPLEMENTATION..................................................................................30 Database Connectivity.................................................................................................30 Session Management...................................................................................................34 Using Metadata............................................................................................................35 Database Metadata.................................................................................................36 Retrieving table names.....................................................................................37 Retrieving column names................................................................................38 ResultSet Metadata.................................................................................................38 Using PreparedStatement.............................................................................................39 Centralized Error Handling in Web-based Applications.............................................40 Data Structures Created by the Alerting System.........................................................42 Classes and Methods in the System.............................................................................45 Putting It All Together.................................................................................................50 Temporal alerts......................................................................................................50 Creation............................................................................................................50 Activation.........................................................................................................50 Generic alerts.........................................................................................................51 Creation............................................................................................................51 Activation.........................................................................................................52 Drug Interaction Alerts..........................................................................................52 Creation............................................................................................................52 Activation.........................................................................................................52 Summary......................................................................................................................53 5 CONCLUSION.............................................................................................................54 Where Can We Go from Here?....................................................................................54 Summary......................................................................................................................56 APPENDIX A THE ARDEN SYNTAX..............................................................................................57 B JAVA ENABLED DATABASES...............................................................................60 Database Triggers........................................................................................................61 Developing Stored Procedures.....................................................................................62 Calling Java from Database Triggers...........................................................................62 The Aurora JVM in Oracle 8i......................................................................................64 v

PAGE 6

LIST OF REFERENCES...................................................................................................66 BIOGRAPHICAL SKETCH.............................................................................................68 vi

PAGE 7

LIST OF TABLES Table page 3-1 Patient Personal Table.............................................................................................20 3-2 Lab Data Table........................................................................................................21 3-3 Lab Analysis Table..................................................................................................21 3-4 Suggested Medication Table...................................................................................22 4-1 Fields of Table “alert_maptable”.............................................................................42 4-2 Fields of Table “alert_lab_analysis_table”..............................................................42 4-3 Fields of Table “alert_patient_analysis_fields”......................................................42 4-4 Fields of Table “alert_patient_lab_fields”...............................................................43 4-5 Fields of Table “alert_patient_medicine_table_fields”...........................................43 4-6 Fields of Table “alert_patient_personal_fields”......................................................43 4-7 Fields of Table “alert_drug_interaction”.................................................................43 4-8 Fields of Table “alert_generated”............................................................................44 4-9 Fields of Table “alert_table”...................................................................................44 4-10 Classes (and their Methods) in WEB-SAM..........................................................45 vii

PAGE 8

LIST OF FIGURES Figure page 1-1 The Process.................................................................................................................3 2-1 Java and the Database.................................................................................................6 2-2 Execution of a JSP Script...........................................................................................8 2-3 Un-Optimized query execution.................................................................................11 2-4 Optimized query execution........................................................................................11 2-5 CLEM Architecture...................................................................................................14 3-1 Components of the Alerting System..........................................................................16 3-2 Login Screen..............................................................................................................23 3-3 Database Setup Screen 1...........................................................................................24 3-4 Database Setup Screen 2...........................................................................................25 3-5 WEB-SAM Main Screen...........................................................................................26 3-6 Screen Navigation.....................................................................................................26 3-7 Creation of a Drug Interaction Alert.........................................................................27 3-8 Creation of a Temporal Alert....................................................................................27 3-9 Creation of a Generic Alert.......................................................................................28 3-10 Output When the Alerts are Triggered....................................................................28 4-1 Establishing a Database Connection.........................................................................31 4-2 JSP Script That Uses LoginBean...............................................................................34 4-3 Retrieving a Session Object.......................................................................................35 4-4 Using Database Metadata..........................................................................................37 viii

PAGE 9

4-5 Using ResultSetMetaData.........................................................................................39 4-6 Using the PreparedStatement Class...........................................................................39 4-7 Setter Methods in PreparedStatement Class..............................................................40 4-8 JSP Script that Handles Errors..................................................................................41 A-1 Example MLM Using Arden Syntax........................................................................58 B-1 An example Java Class.............................................................................................63 B-2 Publishing the method..............................................................................................63 B-3 Creating the Table.....................................................................................................64 B-4 Creating the Database Triggers.................................................................................64 B-5 The JServer Accelerator............................................................................................65 ix

PAGE 10

Abstract of Thesis Presented to the Graduate School of the University of Florida in Partial Fulfillment of the Requirements for the Degree of Master of Science A FRAMEWORK FOR SPECIFYING AND GENERATING ALERTS IN RELATIONAL MEDICAL DATABASES By Shankar N. Manamalkav May 2002 Chair: Dr. Douglas D. Dankel II Department: Computer and Information Science and Engineering Clinical alerting is an important feature in medical databases. However, the programming effort and the medical domain knowledge required can make the development and maintenance of alerts a resource intensive process. This thesis discusses the design of a simple database independent, web-based alerting system—WEB-SAM (Web based SQL-driven Alert Manager). An alerting system generates messages based on the gathered patient data and the specified rules. The model described here pertains to asynchronous alerts—i.e., alerts run on data already gathered and stored in the database. Patient data that are stored in a relational database are accessed via modules written in Java (Beans) using JDBC. The logic portion of the rule (generally comprising medical criteria) is evaluated and, depending on the outcome of the evaluation, an appropriate action is executed. The thesis involves designing a framework permitting the alerting mechanism to be added onto any existing relational medical database (provided the basic patient data on x

PAGE 11

which the alerts can be based are present). The main components of the system are an interface for formulating alerts, an interface for activating alerts, and an interface for resolving generated alerts. This thesis focuses on the technical issues involved in the development of such a system. Study has conclusively proven that such support systems increase clinician and patient satisfaction, and, in general, significantly enhance the process of care. The ultimate goal is to transform the role of computers from being passive data storage devices to intelligent active participants. xi

PAGE 12

CHAPTER 1 INTRODUCTION Alerting systems exist in several healthcare centers. These systems can be an extremely important tool to caution the physician about a potential adverse event and thus ensure safer and more efficient clinical practices. As alerting systems gain popularity, it is imperative for physicians and healthcare providers to understand and be able to use such systems in an office setting without being overwhelmed by the programming language or other technological concepts involved. A design decision was made to create WEB-SAM as a web-based application, which can be accessed from any browser. Alerting Systems with web based user interfaces offer distinct advantages when compared to non-web based systems because the emergence of the world-wide-web promises to make information available globally, and, Web standards allow the same application to be viewed over a multitude of hardware platforms and communications media. The Concept The basic concept of “Alerting” is centered on what is called the Event-Condition-Action mechanism. What this means literally is “When an event occurs, check if the condition is true. If it is, then perform the action.” Alerting is typically implemented through the use of a knowledge base comprising if-then rules. The alerting mechanism checks the data specified in the rule against the available patient data and returns a conclusion about whether a critical event has occurred. A simple example of a real life 1

PAGE 13

2 scenario would be to signal an alert if the WBC (white blood cell) count of a patient is below a threshold value. The alerting mechanism can only function against patient data that has been gathered and is readily accessible. There is no doubt that a concerted effort must be put into the process of collecting patient data and making these data available in a structured form. Several successful implementations of monitors have used procedural languages specifically designed for healthcare to code rules, the most notable among them being the Arden Syntax [HRI94] These syntax rules are also called Medical Logic Modules. A sample Arden Syntax Medical Logic Module has been included in Appendix A. Although, it does not have all the features of a standard programming language and was designed to be easy to program and use, it is intimidating to the average physician who is not trained in programming languages [HRI96]. It would be quite rewarding to have a graphical user interface based system that permits the physician to create rules without actually having to code in a programming language. This thesis attempts to wrap simple rules entered by the user within a Structured Query Language (SQL) statement. Structured Query Language, also called sequel, is a database access language for relational databases. SQL has wide support from leaders in relational technology, and will remain the most important database access language. SQL has the advantage of being a standard [KIN98]. However, considerable time and effort may be required to develop intuitive interfaces for creating complex medical queries. Also, as the complexity of the medical rule increases, it becomes increasingly difficult to convert the rules into SQL statements. These SQL statements are

PAGE 14

3 executed using the JDBC framework (Java Database Connectivity or JDBC is Sun Microsystem’s standard application program interface for connecting to relational databases from Java. It hides the peculiarities of each database vendors specific API from the programmer). Other Java modules check the returned results for possible critical events. These processing phases that the user-specified rule goes through are shown in Figure 1-1. Use the JDBC interface to execute the SQL query Interface for creating medical rules User specified rule converted to SQL queries 1. a.b>c.d 2. x expires in d days 3. x interacts with y,z.. Select * from a,b where c=d and e=f JDBC interface used from within Java Component (Bean) Relational Database Figure 1-1 The Process There are many alternatives for implementing an application that achieves the above. In any medical system, portability and response time are of paramount importance. An applet-based application seems plausible due to the better graphic capabilities and well-defined User Interfaces. But, such a system would require a non-trivial amount of time to download on the client machine and, as such, is not preferable except in an intranet setting where download times are small and connections are fast and reliable. A Client-Server application has the drawback that the client needs to be distributed on every client machine. Also, updates on the client application (e.g., the

PAGE 15

4 addition of templates, etc.) have to be propagated to all clients. The most efficient solution is to implement a web-based application (eliminating the problems of a client-server application) using simple HTML forms (small file size ensures faster loading of pages) for the user interface. Organization of the Thesis The thesis comprises five chapters. The next chapter discusses the various technologies that have been used in the implementation of Web-based SQL Driven Alert Manager (WEB-SAM). It also discusses the minimum software requirements to use the system. Chapters 3 and 4 provide an in-depth analysis of the requirements and implementation details of WEB-SAM. Chapter 3 describes the High-level design including examples of typical scenarios of use. Chapter 4 provides low-level implementation details. An overview of some critical modules (including source code snippets) has been provided. Chapter 5 presents a summary and conclusion of the thesis including future areas of work and possible extension

PAGE 16

CHAPTER 2 TECHNOLOGY OVERVIEW This chapter gives a brief overview of the technology that has been employed in the development of WEB-SAM and information on existing clinical alert software. Technology Used in WEB-SAM A Java-based database independent approach to clinical alert management is presented. The following sections provide an explanation of the salient technological features of Java that are used in the development of WEB-SAM. Database Independence with JDBC Much in the medical field hinges on the ability to capture, retrieve, and otherwise operate on clinical information that has been gathered from the patient’s interaction with the medical provider. Different providers may choose to have different implementations to capture and store patient data and, as such, no assumptions can be made either regarding the database type (the server used may be Oracle, Sybase, DB2, etc.) or the data structures used to store the data itself. 1 To create an application that can operate on medical databases irrespective of their type, a platform independent application programming interface is required. Java Data Base Connectivity (JDBC) provides this. 1 The table structures, names and columns used in different implementations but serving the same purpose may differ. For example, one implementation may have the table that stores the prescribed medication as “prescription_table” while another may call it “prescription_details.” Also, they may not store exactly the same details with similar column names. 5

PAGE 17

6 Java Database Connectivity (JDBC) is SUN Microsystems standard Application Program Interface (API) for interfacing to relational databases from Java. JDBC presents a single uniform API that hides the peculiarities of each database vendor’s specific API from the Java programmer. Application programs can make use of JDBC and SQL to achieve database independence. For example, a Java module written to access a Sybase database can, with a two-line modification, begin accessing an Oracle database. Figure 2-1 shows how JDBC programs interact with databases. DriverManager Oracle Driver JDBC-ODBC Bridge Sybase Driver ODBC Driver Oracle Database ODBC Database SybaseDatabase ResultSet ResultSet ResultSet Statement Prepared Statement Callable Statement Connection Application Figure 2-1 Java and the Database An individual database system is accessed via a specific driver that implements the Driver interface. Drivers exist for almost every known RDBMS system. When the driver is loaded into memory, it registers itself with the DriverManager class as an

PAGE 18

7 available database driver. The DriverManager class then opens a connection to a given database (the database is specified by a specially formatted Uniform Resource Locator or URL). Java Server Pages (JSP) A Java Server Page is a simple text file consisting of HTML or XML content along with JSP elements (a sort of shorthand for Java code). When a client requests a JSP page from the web server, which has not been run before, the page is first passed to a JSP engine which compiles the page into a servlet, executes this servlet, and returns the resulting content as a simple HTML page to the client. Thereafter, the web server's servlet engine executes the compiled page. The sequence of events is as shown in Figure 2-1 [JSP01]. JSP provides a variety of ways to talk to java classes: servlets, applets, and the web server This model allows tasks to be sub-divided--a developer builds custom components and the page designer assembles the application with a few judicious method calls. In this “application assembly” model, the business logic is separated from the presentation of data, leading to clean reusable code. Optimizing Database Performance An alerting system is expected to constantly monitor data and report any adverse events. It must perform well enough to handle the required workload. The performance requirement increases as the number of rules and the number of patient records that need to be checked increase. Java provides, in the JDBC, two methods for optimizing database access: Stored Procedures and Prepared Statements [DAR98].

PAGE 19

8 Changed? JSP File Check to see if the file has changed since last compiled Execute Existing Servlet Execute the new Servlet Compile into Servlet Web Server/Servlet Engine User Request Figure 2-2 Execution of a JSP Script Stored procedures A stored procedure is pre-compiled SQL code that resides on the database server. When a client invokes a stored procedure, control is transferred to the server. The server executes the procedure and optionally returns data to the client. Stored procedures minimize network round-trips between the client and server and they improve performance by decreasing network latency. They can also be used to enforce security. Ideally, the best scenario would be to store all the medical alerts as stored procedures in the database and trigger them on certain events. However, this is not possible without knowing the database that is used to store the data beforehand. Also, it can be extremely tedious to code complex medical rules using SQL (or some slight variation) by itself. There has been a great deal of interest in active database systems or triggers over the last ten years. Many database vendors now include active database capability in their products. Nevertheless, a problem exists with many commercial trigger systems as well

PAGE 20

9 as research efforts into development of database triggers. Most work on database triggers follows the event-condition-action (ECA) rule model. In addition, trigger conditions are normally checked and actions are normally run in the same transaction as the triggering update event. In other words, the so-called immediate binding mode is used. The main difficulty with this approach is that if there are more than a few triggers, or even if there is one trigger whose condition is expensive to check, then update response time can become too slow. An Asynchronous Trigger Processor (ATP) is a system that can process triggers asynchronously, after updates have committed in a source database, or have been completed in some other data source. Processing triggers asynchronously avoids slowing down update transactions with trigger processing logic. An ATP called TriggerMan [HAN97] has been developed at the University of Florida as a vehicle for investigating issues related to asynchronous trigger processing. It is designed to be able to gather updates from a wide variety of sources, including relational databases, object-relational databases, legacy databases, flat files, the web, and others. Another development in database technology is the evolution of the “JAVA ENABLED” database. Sybase, DB2, Oracle, etc. have developed database products that are closely integrated with the Java Virtual Machine. This allows users to create modules that encapsulate complex medical logic and have them invoked by the database when a particular event occurs. An explanation of java-enabled databases is provided in Appendix B.

PAGE 21

10 It must be kept in mind that although SQL is an international standard, many vendors use proprietary SQL dialects for Stored Procedure programming. Oracle provides PL/SQL, Informix offers Stored Procedure Language (SPL), and Microsoft and Sybase support Transact-SQL (T-SQL) stored procedures. IBM DB2 Universal Database uses a different model, supporting external stored procedures written in programming languages such as Rexx, COBOL, C, and Java. JDBC provides a means of allowing developers to retain the specific functionality of a database vendor. Query Strings can be passed directly to the connected driver. This feature however, limits the freedom of the application developer to change database back-ends. Prepared statement Another method for optimizing database access is called the “Prepared Statement.” This method optimizes database access as follows: When an SQL statement is submitted to a database management system, it is processed in four phases--Parsing, Optimization, Code generation, and Execution. Depending on the contents of the statement, these phases may be separated by arbitrary intervals of time. The parser ensures that the SQL statement is syntactically valid. The optimizer accumulates the names of the tables and columns referenced in the statement, checking if they are valid. The access paths available to each of the columns are determined. This is followed by a semantic check to ensure type compatibility between the “where” clause and the referenced column. Finally, the optimizer performs access path selection after determining the optimum order of execution of the query blocks in the statement. The end result of this phase is a “plan.” The code generator is then invoked with the plan as its input resulting in the machine language code to be executed. This sequence is shown in Figure 2-3.

PAGE 22

11 RESULTSET SQL DATABASE SERVER EXECUTOR CODE GENERATOR OPTIMIZER PARSER Figure 2-3 Un-Optimized query execution Pre-executing the first three phases can optimize the SQL statement. A prepared statement is a pre-compiled SQL statement. The application sends the prepared statement to the database engine before the query is executed. This allows the database engine to parse, optimize, and plan the query. When the query is resubmitted, only the “execute” phase needs to be performed. This results in significant performance gains when queries containing different parameters need to be run repeatedly. Figure 2-4 shows how an SQL query is optimized using prepared statements. JDBC, however, does not guarantee that the database engine will take advantage of prepared statements and provide this optimization. RESULTSET DATABASE SERVER SQL EXECUTOR CODE GENERATOR OPTIMIZER PARSER Prepared SQL Statement Figure 2-4 Optimized query execution Understanding JDBC Metadata There are two interfaces within the JDBC framework that provide us with metadata information (i.e., data about data). They are as follows:

PAGE 23

12 Database Metadata: Provides information about the database itself (e.g., the number and names of accessible tables, views, synonyms, or procedures) and Resultset Metadata: Provides information regarding the type and number of columns returned in a result set when a query is executed. These interfaces are explained in detail in Chapter 4 along with sample code and are used extensively in the development of WEB-SAM to query the user’s database. Component development in Java using JavaBeans The concept of a “component” has been implemented by Java as a “JavaBean” [HAE01]. A JavaBean is a reusable platform-neutral software component. A JavaBean “knows” about the tools that will manipulate it and is compatible with those tools. It is quite simply a Java class that obeys some strict protocol. Primarily, a JavaBean should be thought of as a black box; that is, a software device with known functionality but unknown internal functioning. A Bean lives in a multithreaded environment and can therefore be addressed by multiple threads. While it is obvious that JavaBeans come to life once instantiated during run-time, it may come as a surprise that JavaBeans are equally alive during design, too! A Bean can do this purely as a result of adhering to a simple naming convention for its accessor methods [HAE01]. Clearly, this design-time “live” behavior of beans is a radical difference from the code libraries of the past that remained dead throughout the development cycle and came to life only when the application is written (complete with drivers and stubs). This software development model has some really far-reaching implications. Ideas can be converted into prototypes quickly. Another interesting and extremely handy feature of a JavaBean is “reflection.” A JSP script can automatically gather the data entered by user on the form and set the properties of a Bean (this, however, requires that the fieldnames in the form and the properties in the bean be

PAGE 24

13 identically named). The core goal of JavaBeans is to make software development easier, faster, and cheaper by encouraging code reuse on a large scale. Clinical Alerting Systems in Use This section presents some general background and architecture of clinical alerting systems that are in use. Clinical Event Monitor at Columbia-Presbyterian Medical Center (CPMC) At CPMC, a Clinical Event Monitor processes a knowledge base consisting of Medical Logic Modules (MLM’s) encoded in the Arden Syntax for Medical Logic Systems (for information about the Arden Syntax, refer to Appendix A). This monitor generates messages (alerts) for appropriate health care providers, patients, and organizations based on clinical events and patient data [CHU01]. The Clinical Event Monitor that executes the MLM’s runs on a mainframe computer. Data are stored in a relational database (DB2) and accessed via Data Access Modules. Each MLM can have one or more triggers which identify when the Clinical Event System should execute the MLM. When an MLM is evoked, the condition portion is evaluated. It generally consists of medical criteria and algorithms. The result of the evaluation is to conclude “true” or “false.” If true, the action is executed, storing a message in the patient record and sending electronic mail to the health-care provider are typical actions. Clinical Event Monitoring (CLEM) at the University of Pittsburgh An asynchronous clinical event monitor was designed and deployed at the University of Pittsburgh [WAG97]. The main design objectives were It should be scalable to the enterprise level with a large number of rules It should work in an environment that included many legacy systems It should support complex care plans as in a disease management application

PAGE 25

14 It could also function with a response time adequate for synchronous 2 mode. Each system sends data in HL-7 format through a message router to a proprietary data warehouse. The basic infrastructure needed to support event monitoring is a source of events, a source of patient data, and a notification service. The event monitor itself is an algorithm that takes as input events, patient data, and a representation of medical knowledge, and outputs messages. Legacy Systems Message Router EventDetector CLEM Sk y tel E-mail CLIPS (Expert System shell developed by N ASA) MARS (Proprietary DataWarehouse) Figure 2-5 CLEM Architecture CLEM evaluates events, in the context of everything (ideally) that can be known about a patient, and it communicates its conclusions via some communication channel to a person. The retrieval of patient data is usually a performance bottleneck for event monitors and it is therefore a key issue for scalability. Rather than calling the database incessantly during rule evaluation, CLEM stores all of a patient’s data in memory before evaluation. CLEM stores patient data in time-sorted order in memory as list structures that support efficient temporal inferences. CLEM uses email and Skytel 2-way pagers to communicate alerts to interns. 2 A Synchronous Alert is one that is run in real time. The alerting mechanism is triggered on events like insert, update, or delete on the patient data stored in the database.

PAGE 26

15 New York Weill Cornell Medical Center (NYWCMC) Electronic Medical Record The electronic medical record used at the New York Weill Cornell Medical Center, a tertiary care academic medical center, is a commercial product not explicitly designed to support clinical alerting functionality. It is a distributed C-language based application executing on a UNIX platform [OPP01]. The software is compiled on a single server and distributed to multiple servers across the hospital. The alerts themselves are written as C code modules whose execution is triggered in response to user or electronic database activity. The most significant issue with this system architecture is that development or modification of existing alerts requires recompilation and distribution of new software. After distribution, the old software must be stopped and the new program started, creating undesirable workflow interruptions. The salient points of the NYWCMC EMR are: The clinical alerts are generic and similar tasks can be handled using a single multifunctional code module The knowledge is externalized to facilitate knowledge base changes The presentation of alerts can be customized (what message, who sees, who acknowledges, etc.) to different user classes. Summary This chapter provided an introduction to the features of Java used in the development of WEB-SAM. Included is a discussion on Java Database Connectivity, Java Server Pages, and Java Beans. An explanation on methods to optimize database performance (stored procedures and Prepared statements) was presented. The principal features of a few existing Clinical Alerting Systems (CPMC, CLEM, NYCCMC) was described. The following chapter deals with the high-level system design, requirements and goals of WEB-SAM

PAGE 27

CHAPTER 3 SYSTEM DESIGN Overview The earlier chapters dealt with the concept of asynchronous alerts in medical systems and some of the technologies that are used in its implementation. This and the following chapter explain the design of WEB-SAM. WEB-SAM sits atop an existing medical database. The basic structure of the system can be divided into three components—one for formulating alerts (i.e., the Web based user interface), one for activating alerts (i.e., a mechanism for checking the condition specified in the rule against the patient data), and one for resolving alerts (i.e., the alerts that are generated are stored in a table and must be attended to by the person in charge) Interface for formulating Alerts Web Server JSP Engine Interface for activating Alerts Interface for viewing/resolving Alerts Existing Medical Database (Relational) Java Beans View 1 : View 2: View 3: Figure 3-1 Components of the Alerting System 16

PAGE 28

17 The components of this model have been implemented using JavaServer pages, Java Script, JavaBeans, and a web-server with JSP processing capabilities, the Java Server Web Development Kit (JSWDK). Using an Internet browser, authorized users submit forms triggering the execution of JSP scripts. These scripts mediate data access (using JDBC to access the database from within Beans) and provide appropriate navigation. Design Goals WEB-SAM is intended to be a user-friendly alerting system. To ensure that the system is easily extensible and modifiable, the following design issues were of paramount concern: Develop a consistent, easy-to-use web interface across pages, Isolate self-contained parts of the application by writing a separate JSP script for each, Centralize all error handling in the application, and Use the same code for different client requests (the web-server internally handles multiple requests to the same Bean using multi-threading). Requirements This section addresses the basic requirements for building an alerting system. The components that make up the system are: the knowledge base, which is a table storing the set of medical rules, the Patient data (this includes the existing medical database and the tables created by WEB-SAM), the event triggering the rule, and the user interface to WEB-SAM. Knowledge Base Maintenance The following basic operations on the rules must be possible:

PAGE 29

18 Create – An authorized user should be able to login to the system and create a rule to execute against the data for a possible critical event. Update/Modify – Modifications and changes to an existing rule in the knowledge base should be possible. Activate/De-activate – It should be possible to Enable/Disable an alert from being triggered. Database Setup Information – Because the alerting mechanism depends heavily on the underlying medical database, a provision must be provided to accommodate changes to the database itself. If the database is restructured or its name is changed, it must be tracked (however, if the value in a table is changed, it is automatically handled). The knowledge base itself is divided into the following functional classes based on the logic used and the way data is entered: Drug interaction alerts. Drug interaction alerts involve checking a prescribed drug against a repository of known drug interaction effects to caution or warn the patient of a possible hazard. Temporal/time-based checks. For the purposes of this thesis, temporal alerts check whether a particular test on a patient (e.g., blood test, x-ray, etc.) is still valid. The rationale for this type of alert is that every test that a patient is advised to undergo for some recurring problem has a validity period, and it is extremely important to redo the test once that time frame expires. For example, a person may not have been diagnosed to have high blood sugar one year ago but a similar test today may indicate high levels of blood sugar. Generic rule. Generic rules allow the user to create almost any logical rule based on the structured data that are present in the database. For example, if the system contains data on the white blood cell count (e.g., WBC_COUNT) and red blood cell count (e.g.,

PAGE 30

19 RBC_COUNT) of a patient, an alert could be created to test: (WBC_COUNT>5 and RBC_COUNT<120). Event Triggering Mechanism For the rule to be checked against the patient data, some triggering mechanism is essential. There are various alternatives for this: The user may issue an explicit request to check a particular record or all records against the alerts, A timer mechanism may trigger the alerts at periodic intervals or at a time (and date) specified by the user, or An internal capability of active databases may trigger events. Database triggers are trapped and processed by the DBMS itself and, therefore, its use could lead to improved performance. Note that this method can only be used if the patient data is stored in a database supporting triggers. Flexibility and Ease of Use The user interface for WEB-SAM was developed by iterative design, meaning that new versions of the interface were designed each time a weakness was discovered in the current design. A consistent visual design provides the necessary orientation cues and navigational controls needed by users. Applying the same techniques consistently also provides users with a predictable location for information and controls on every page, thus enhancing usability. Data Requirements In general, the rule engine checks the pattern specified in the rule against the available data and returns a conclusion about whether a critical event has occurred. So, the alerting system is only as good as the patient data that are gathered. This requirement cannot be emphasized enough. Information management is one of the most significant activities of the healthcare industry. Most modern hospitals have computerized patient

PAGE 31

20 records. However, most systems are usually proprietary. Several standards are being developed to ensure standardization throughout the health care industry. To enable the alerting mechanism to trigger a rule, it is necessary that all the required data on a patient need to be located in a common database (or somehow be linked together). The data requirements for the purpose of this system (with the current functionality) are straightforward. It should be identified that there may be numerous variations in the way a health care provider actually stores data which differs from what is presented below. In such a scenario, it may be necessary to introduce an additional data-mining step to consolidate the data. An alternative is to customize WEB-SAM according to the local database setup. This, however, defeats the aim of trying to build a database independent, widely usable product. WEB_SAM is organized to work off a series of tables each containing different parts of a patient’s data. During the setup phase, a series of table names (and field names) are collected. In each of the Tables 4-1 through 4-4 the bold italicized field names are the fields on which the alerting mechanism applies. The Patient Personal Table stores personal details about the patient. Generally, this data entry is done when the patient first meets the provider seeking help for a particular ailment. The schema for this table might look similar to Table 3-1. Table 3-1 Patient Personal Table Field Name Explanation Patient_id Field that stores the primary key Patient_name Field that stores the patient names Age Field that stores patient age Nature_of_problem Field that stores the complaint of the patient Etc. And, possibly several other fields

PAGE 32

21 The Lab Data Table stores data on the various tests performed on the patient. This data might include an explanation about the test performed, the patient on which it was performed, the dates on which the test was done, and other related data. The schema for this could look similar to Table 3-2. Table 3-2 Lab Data Table Field Name Explanation Patient_id Field that stores the primary key Date_of_Test Field that stores the date of test administration Time_of_Test Field that stores the time of the day when the test was taken Test_Name Field that stores the name of the test that the patient was advised Etc. And, possibly several other fields The Laboratory Test Analysis/Results Table stores the results of the tests that were administered on the patients. The schema for this table could look similar to the information in Table 3-3. Since various parameters need to be collected for each test, it seems intuitive to have various tables, each containing specific test data. For example, there could be a table for blood test results, which might have RBC_Count, WBC_Count, etc. as fields, while another table for blood pressure might have “systolic,” “diastolic,” etc. as fields. Table 3-3 Lab Analysis Table Field Name Explanation Patient_id Field that stores the primary key Parameter – 1 Field that stores a particular value/parameter gathered from the test Parameter – 2 Field that stores a particular value/parameter gathered from the test Etc. And, possibly several other fields

PAGE 33

22 The Suggested Medication Table stores the details of drugs prescribed to patients. The schema for this could look similar to Table 3-4. Table 3-4 Suggested Medication Table Field Name Explanation Patient_id Field that stores the primary key Date Field that stores the date on which the prescription was prepared Time Field that stores the time on which the prescription was prepared Drug_name Field that stores the name of the prescribed drug Dosage Field that stores the recommended dosage of the drug Etc. And, possibly several other fields Error Handling and Reporting It is impossible to eliminate errors altogether in any software system. A shared system such as this has many different users and rules often change over a period of time. Syntax errors (if any) in the rules are detected immediately and reported by an error handling mechanism. But, since this is an asynchronous system, there is also a possibility of several runtime errors. Java’s internal exception handling mechanisms are exploited to catch run time errors and display them to the user. This feature is explained in detail in the next chapter. Security Requirements Any discussion about an Internet based application is incomplete without mentioning security implications. The main issue is one of restricting access to only those individuals who are authorized users. The username and password used to access the database are also used to access the system. All commercial relational databases use a secure authentication mechanism.

PAGE 34

23 All web pages are linked only through the initial login page. Once the username and password are entered, they are encapsulated in an object and stored in an internal session variable (this is a feature provided by JSP – sessions are created for each user using the system and are maintained internally by the web server). Therefore, sensitive information is never retransmitted. System Walkthrough The user is first required to login to the medical database. This requires a Username, password, the Data Source Name (also called the URL of the database), and the connect string (which is the name of the driver – each database has a specific driver). Figure 3-2 shows the login screen of WEB-SAM. Figure 3-2 Login Screen When the user submits the form with the above details completed, a server-side script is invoked that checks the credentials supplied to see if they are correct. If not, an error

PAGE 35

24 message describing the nature of error is displayed. If the proper information is provided, a check is then made on the database to verify whether the particulars of the medical database (e.g., the name of the table that stores the patient personal data, the lab test details, etc.) are available. If the data structures do not already exist, WEB-SAM initiates a setup process requesting the user to enter the installation specific details. The setup phase gathers the required table names and field names, creates appropriate data structures, and creates a persistent store in the database. Figures 3-3 and 3-4 below show the data gathered in the setup process. Figure 3-3 Database Setup Screen 1

PAGE 36

25 Figure 3-4 Database Setup Screen 2 Once a connection to the database is established, and the table structures as required by WEB-SAM is determined to be present in the database, the user is forwarded to the main screen. Ideally, the user will have to go through this process only once (unless of course, there is a change in the database that requires the setup process to be redone). The details of the data structures created are explained in the next chapter. The main menu contains options for managing rules (create, modify, activate, and deactivate), viewing the existing rules, and for changing the setup information. The main screen of WEB-SAM is shown in Figure 3-5 and the basic screen flow is shown in Figure 3-6. Figures 3-7, 3-8 and 3-9 show the user interfaces for the creation of Drug Interaction, Temporal and Generic alerts respectively.

PAGE 37

26 Figure 3-5 WEB-SAM Main Screen Modif y Lo g ou t Setu p View Activate/ Deactivate Create Main Menu Lo g in Interaction Other Other Interaction Temporal Other Interaction Temporal Figure 3-6 Screen Navigation

PAGE 38

27 Figure 3-7 Creation of a Drug Interaction Alert Figure 3-8 Creation of a Temporal Alert

PAGE 39

28 Figure 3-9 Creation of a Generic Alert When these alerts are triggered, an entry is made to a table named “alerts_generated” in the database, and the screen shown in Figure 3-10 below is displayed Figure 3-10 Output When the Alerts are Triggered

PAGE 40

29 Summary This chapter described the design of WEB-SAM. The requirements of the system in terms of Knowledge base Maintenance, Event Triggering, Flexibility of Use, Patient Data Requirements, Error Handling, and Security considerations was explained. A system walkthrough was provided to make the user conversant with the basic features of WEB-SAM. The following chapter provides low-level implementation details and explains how WEB-SAM was built using the concepts presented this far

PAGE 41

CHAPTER 4 SYSTEM IMPLEMENTATION This chapter provides low-level system implementation details. Where necessary, actual code used in WEB-SAM is provided. Database Connectivity Using Java Data Base Connectivity (JDBC), it is possible to communicate with a database from within an application program. The class code shown in Figure 4-1 demonstrates how all the information required for establishing a connection to a database can be encapsulated in a single JavaBean. package Alert; import java.sql.*; import java.util.*; public class LoginBean //this bean takes the uid,pwd,driver and connect string //and establishes a connection to the database. { //these are the parameters required to establish the connection. private String url; private String userid; private String password; private String driver; public void setUrl(String url) { this.url = url; } public void setUserid(String userid) { 30

PAGE 42

31 this.userid = userid; } public void setPassword(String password) { this.password = password; } public void setDriver(String driver) { this.driver = driver; } public String getUserid() { return userid; } //The above methods get called by the webserver automatically (by REFLECTION) //now, we have all the parameters needed to establish //a connection with the database. public Connection getConnectresult() throws SQLException,ClassNotFoundException,NullPointerException { Connection con=null; driver = driver; url = url; userid = '"'+userid+'"'; password = '"'+password+'"'; Class.forName(driver); con = DriverManager.getConnection(url,userid,password); //return the connection object return con; } //end method }//end class LoginBean Figure 4-1 Establishing a Database Connection The “get” and “set” methods are coded to comply with the standards for JavaBeans. When this Bean is invoked from a form with field names named “url,” “userid,” “password,” and “driver,” the “set” methods are automatically invoked by a

PAGE 43

32 mechanism called “Reflection.” This is a very powerful feature and makes gathering data from forms easier. Note that the class is required to be in a Package (Alert in this case) so the web server can locate it. Once we have this bean, invoking the method getConnectresult() on its instance provides the connection object. Since database connections are a very limited resource, it is important to dispose them of properly after the task is completed (using the close() method on the connection object) so that other jobs can make use of them. The Connect() method typically performs the following steps [REE97]: 1. Check to see if the URL is valid, 2. Open a TCP connection to the host and port number specified, 3. Attempt to access the database, and 4. Return an instance of the connection object. The possible errors that could occur in this process are: The drivers for the database are unavailable (ClassNotFoundException), The DriverManager.getConnection() fails because the URL is incorrect (SQLException), or Database or page errors, which should be shown as such (Exception). Once the Connection object is returned, the application developer may create a Statement object to issue against the database. The result of a Statement is a ResultSet object, which contains the results (if any). Database connections include the ability to control changes made to the actual data stored through transactions. A transaction is a set of operations all of which must be executed to completion (if any operation encounters a problem, then all the operations that comprise the transaction must be “rolled back”). Handling transactions is discussed later in this chapter.

PAGE 44

33 Note that there is no error handling performed in this code, except the mandatory declaration of throw clauses. The context of the error is best handled in the JSP where the call was made. JSP provides an extremely powerful, centralized error reporting mechanism. The JSP script shown in Figure 4-2 invoked when the login form is submitted, illustrates several key features of JSP: <%@ page language="java" import="Alert.*" errorPage="../AlertCommon/errorpage.jsp" %> <%@ page import="java.util.*" %> <%@ page import="java.sql.*" %> <% Connection con=null; Vector v=new Vector(); try { if ((con=connect.getConnectresult())!=null) { session.putValue("USER",connect);//put the connection into a session object DBMetadataKit kit = new DBMetadataKit(); v = kit.getTables(con); con.close(); } } catch (ClassNotFoundException e) { throw new ServletException("Database driver that has been specified is not available", e); } catch (NullPointerException e) { throw new ServletException("Your session has expired. Please login to the system again"); }

PAGE 45

34 catch (SQLException e) { throw new ServletException("Database URL is wrong.", e); } %> Figure 4-2 JSP Script That Uses LoginBean 1. The errorPage="../AlertCommon/errorpage.jsp" declaration in the second line of the JSP is an indication to the Webserver, that if any exception is thrown during the course of execution, the page specified (errorpage.jsp in this case) should be displayed. 2. The directive tells the webserver that the class named LoginBean, present in the Alert package, should be instantiated and the object “connect” should be created. 3. The directive is used to invoke the Reflection feature. This directive automatically invokes the “setter” methods that are defined in the Bean (note that the names of properties should exactly match the names used in the input form for this feature to function properly). 4. Once the object is instantiated, it is possible to invoke the methods of the object just like any other Java object. The con=connect.getConnectresult() statement returns a connection object if the call is successful. Otherwise, the errors mentioned earlier are thrown as the case may be. Session Management HTTP (Hyper Text Transfer Protocol) is a stateless protocol. It does not provide a way for a server to recognize that a sequence of requests is from the same client. The server typically accepts input for processing from the client, returns the results, and then forgets all information about the client. Traditional methods to implement session management involve hidden form fields, URL rewriting, or Cookies. However, all these methods require additional programming effort to accomplish a rather “standard” task.

PAGE 46

35 One of the major advantages provided by Java Servlets (and JSP) is the ability to preserve state information using a “session” object [JSP01]. Objects can be added to the session object using the “putValue()” method, which guarantees their maintenance by the web server for the duration of the clients session . The stored object can be retrieved at anytime using the “getValue()” method. After a user successfully logs into the system, it should be possible to store the fact that the particular client is authenticated. We store the LoginBean object as a session variable using the command session.putValue(“USER”,connect). It is now possible to access the connect object on any web page using the command session.getValue(“USER”). A call to getValue() returns a Java Object. It is necessary to “cast” it into an appropriate type before it can be used. For example, when accessing the connect object, the code in Figure 4-3 may be used. <% Object conobj = session.getValue("USER"); LoginBean lb = new LoginBean(); lb = (LoginBean)conobj; %> Figure 4-3 Retrieving a Session Object Using Metadata JDBC provides interfaces that may be used to dynamically determine information specific to the database or the ResultSet. They are called Database Metadata and ResultSet Metadata, respectively.

PAGE 47

36 Database Metadata The getMetaData() method on the “connection” object, returns a Database Metadata object. The code in Figure 4-4 for class DBMetadataKit indicates how methods can be written to extract the tables and table columns from the database. package Alert; import java.util.*; import java.sql.*; public class DBMetadataKit { DatabaseMetaData metadata=null; public Vector getColumnNames(Connection con,String tablename) { //Given the tablename, this function //returns the columns in the table tablename = tablename.trim(); Vector veccol = new Vector(); try { metadata = con.getMetaData(); ResultSet columns = metadata.getColumns(null,null,tablename,null); while (columns.next()) { String colname=columns.getString("COLUMN_NAME"); veccol.add(colname); } } catch (SQLException e) { return null; } return veccol; } public Vector getTables(Connection con) { String name; Vector tables = new Vector(); String[] names= {"TABLE"}; try { metadata = con.getMetaData();

PAGE 48

37 ResultSet tableNames = metadata.getTables(null,null,null,names); while (tableNames.next()) { name = tableNames.getString("TABLE_NAME"); tables.addElement(name); } } catch (SQLException e) { System.out.println("Error"); } return tables; } } Figure 4-4 Using Database Metadata The class contains two important methods getTables() and getColumnNames(). When getTables() is invoked with a connection object as parameter, it returns a Vector containing all user accessible tables in the database. Retrieving table names When retrieving table names, a call is made to the getMetaData() method on the connection object. It returns a DatabaseMetaData object. The getTables() method of class DatabaseMetadata takes four parameters [BRO01]: 1. String catalog – Only used in databases that support catalogs. May be null, 2. String schemaPattern – Only used by databases that support schemas. May be null, 3. String tableNamePattern – the pattern of the table name to be matched, and 4. String types[] – an array of the types of table names to retrieve. Commonly seen types are “TABLE,” “VIEW,” “SYSTEM TABLE,” and “SYNONYM.” The directive tells the webserver that the class named LoginBean, present in the Alert package, should be instantiated and the object “connect” should be created.

PAGE 49

38 The two pattern parameters (items 2 and 3 above) take Strings that can match the JDBC pattern-matching format. A Percent character (%) matches zero or more characters, while an underscore (_) matches any single character. Execution of this method returns a ResultSet with the following columns: 1. A String named “TABLE_CAT” representing the table catalog. May be null, 2. A String named “TABLE_SCHEM” representing the table schema. May be null, 3. A String named “TABLE_NAME” that gives the name of the table, 4. A “TABLE_TYPE” column that gives the type for that table (one of the values in the types[] array), and 5. A “REMARKS” column that gives any explanatory comments about the table. Retrieving column names Invoking the getColumnNames() method of class DBMetadataKit with the connection object and table name as parameters, returns a Vector containing all columns within the table. The getColumns() method in class DatabaseMetadata is used to retrieve the details of columns in a particular table. The getColumns() method is very similar to the getTables() method. It returns a ResultSet that contains COLUMN_NAME, TYPE_NAME, COLUMN_SIZE, DECIMAL_DIGITS, and NULLABLE. ResultSet Metadata The ResultSetMetaData is very similar in concept to the DatabaseMetaData but is specific to a ResultSet. For example, it is possible to get the number of columns and the names of columns returned by a query using the method getColumnCount() on the ResultSetMetaData object, as shown in Figure 4-5. ResultSet rs = stmt.executeQuery(sql); ResultSetMetaData meta = rs.getMetaData();

PAGE 50

39 Int columns = meta.getColumnCount(); String columnname = meta.getColumnName(1); Figure 4-5 Using ResultSetMetaData Using PreparedStatement Use of PreparedStatement increases the efficiency of the database engine by eliminating the overhead of building the entire execution plan for the query. The code sample in Figure 4-6 shows how the PreparedStatement feature of JDBC can be used to optimize database operations. package Alert; import java.sql.*; import Alert.*; public class AlertsGeneratedTable { private Connection con; public AlertsGeneratedTable(Connection con) { this.con = con; } public void addRecord(String patient_id, String alert_name, String message, String date_generated,String time_generated, String handled) throws SQLException, Exception { String sql = "insert into alert_generated values(?,?,?,?,?,?);"; PreparedStatement pstmnt = con.prepareStatement(sql); pstmnt.clearParameters(); pstmnt.setString(1,patient_id); pstmnt.setString(2,alert_name); pstmnt.setString(3,message); pstmnt.setString(4,date_generated); pstmnt.setString(5,time_generated); pstmnt.setString(6,handled); pstmnt.executeUpdate(); pstmnt.close(); } }//end class Figure 4-6 Using the PreparedStatement Class

PAGE 51

40 The code inserts data into the table “alert_generated.” The data to be inserted into the table is passed to the method addRecord(). Parameterized prepared statements like the one above, gives us the ability to create a pre-compiled SQL statement and bind new parameter values prior to execution. To set the parameters, the PreparedStatement class provides a collection of setXXX methods for setting strings, ints, floats, and other Java data types. Some of these “setter” methods are shown in Figure 4-7 below. public void setInt(int parameterIndex, int anInt) public void setString(int parameterIndex, String aString) public void setFloat(int parameterIndex, float aFloat) Figure 4-7 Setter Methods in PreparedStatement Class By using parameterized prepared statements, the application can efficiently submit multiple queries to the database. The end user of the application receives a quicker response from the database engine. Centralized Error Handling in Web-based Applications JSP provides a mechanism to redirect all errors to a single page in the Application. To exploit this feature, the page must declare itself using the “isErrorPage” directive set to true. JSP has an implicit object named “exception” that stores the exception details. Using the getMessage() method on the exception object gives us a textual description of the error that was encountered. The code sample in Figure 4-8 from the application explains this feature. Every page that intends to use this page to report errors must indicate that errors should be redirected to this page (using the errorPage=filename directive). “Throwing” a ServletException generates errors.

PAGE 52

41 <%@ page language="java" isErrorPage="true" import="java.util.*, java.sql.*" %> OOPS

 

OOPS!

 

There was a problem....

 


Details:

<%=exception.getMessage() %>


Please use your browsers back button and retry

Figure 4-8 JSP Script that Handles Errors

PAGE 53

42 Data Structures Created by the Alerting System The alerting mechanism checks the rule knowledge base against the patient data to draw conclusions. The system creates the necessary tables in the database so that the information regarding the medical database is persistent. Tables 4-1 to 4-9 below describe the various tables that are created by WEB-SAM, the fields that they contain, and their purpose Table 4-1 Fields of Table “alert_maptable” Field Name Explanation Reason Patient_personal The name of the table in the medical database that stores patient personal data Accessing this field of table alert_maptable gives us the name of the table that is used to store the personal details in the medical database Patient_medication The name of the table maintaining prescription data Accessing this field of table alert_maptable gives us the name of the table that is used to store the prescription details in the medical database Patient_labresults The name of the table that stores the tests administered to the patient Accessing this field of table alert_maptable gives us the name of the table that is used to store the lab tests advised in the medical database Table 4-2 Fields of Table “alert_lab_analysis_table” Field Name Explanation Reason Lab_analysis_tables The names of the tables in the medical database that store the test results of tests that the patient has taken Each record of this table is the name of a table that stores the results of a particular test. When a generic alert is formulated, the field names for the alerts can be picked out of the tables that are rows of this table Table 4-3 Fields of Table “alert_patient_analysis_fields” Field Name Explanation Reason Patient_id The name of the field in the patient_analysis table that stores the patient id Accessing this field gives us the name of the primary key in the patient_analysis table

PAGE 54

43 Table 4-4 Fields of Table “alert_patient_lab_fields” Field Name Explanation Reason Patient_id The name of the field in the patient_lab table that stores the patient id Accessing this field gives us the name of the primary key in the patient_lab table Test_name The name of the field that stores the test taken by the patient This field gives the name of the field in the patient_lab table that stores the test name Date_field The name of the field that stores the date of test This field gives the name of the field in the patient_lab table that stores the date when the test was administered Table 4-5 Fields of Table “alert_patient_medicine_table_fields” Field Name Explanation Reason Patient_id The name of the field in the patient_medicine_table that stores the patient id Accessing this field gives us the name of the primary key in the patient_medicine_table Drug The name of the field in the medicine table field that stores the recommended drug This field gives us the name of the field that stores the drug name in the medicine table Table 4-6 Fields of Table “alert_patient_personal_fields” Field Name Explanation Reason Patient_id The name of the field in the patient_personal table that stores the patient id Accessing this field gives us the name of the primary key in the patient_personal table Table 4-7 Fields of Table “alert_drug_interaction” Field Name Explanation Drug_name The name of the drug that causes an interaction Interacts_with The drug/food/condition that interacts with the drug Criticality The criticality of the interaction – can be High, Moderate, or Low Explanation A brief explanation about the nature of interaction

PAGE 55

44 Table 4-8 Fields of Table “alert_generated” Field Name Explanation Patient_id The name of the field in the patient_analysis table that stores the patient id Alert_name The name of the alert Message Message that is to be displayed if the alert is triggered Date_generated The system date on which the alert was fired Time_generated The system time on which the alert was triggered Handled A flag indicating whether the alert was handled by the person in charge (The default is “N.” After an appropriate step is taken, this can be reset to “Y.”) Table 4-9 Fields of Table “alert_table” Field Name Explanation Alert_name The name given to the alert. Each alert in the system is identified with a unique name. Title Title of the alert Institution Name of the institution that formulated the alert condition Author The name of the author of the alert Date_written The date on which the alert was first written Purpose The purpose of the alert Explanation A brief text about what the alert actually does (Verbal description) Alert_type The type of alert (This is assigned by the system as Temporal “T” or Other “O”) Condition This field stores the actual condition on which an alert should be triggered by the system. For temporal alerts, this field has the form “testname;no. of days” For other alerts, it contains a Boolean expression of the form tablename.fieldname op value (AND|OR tablename.fieldname op) * Message The message that is to be displayed in case the alert is triggered Active This field specifies whether the alert is active (Y) or inactive (N) Criticality The criticality of the alert. Can be high, moderate, or low

PAGE 56

45 Classes and Methods in the System Table 4-10 below presents some of the classes in the system and their publicly accessible methods. An explanation about the input parameters is provided Table 4-10 Classes (and their Methods) in WEB-SAM Class Name Method Name Explanation AlertDITable Constructor:GetTableNames (Connection con) Initialize, pass the connection object as parameter void addRecord(String drug_name, String interacts_with, String criticality,String explanation) Insert a row in the table “alert_drug_interaction.” The fields are passed to the function as parameters. AlertsGeneratedTable Constructor: AlertsGeneratedTable (Connection con) Initialize, pass the connection object as parameter. void addRecord(String patient_id, String alert_name, String message, String date_generated, String time_generated, String handled) Insert a row in the table “alert_generated.” The fields are passed to the function as parameters. AlertTable Constructor: AlertTable(Connection con) Initialize, pass the connection object as parameter. addRecord(String alert_name, String title, String institution,String version, String author, String date_written, String purpose, String explanation, String alert_type, String condition, String message, String active,String criticality) Insert a row in the table “alert_table.” The fields are passed to the function as parameters. void updateRecord(String alert_name,String condition, String message,String criticality) Update the alert with name “alert_name” with the new condition, message, and criticality passed as parameters

PAGE 57

46 Table 4-10 Continued Class Name Method Name Explanation TableBean Constructor: TableBean(String exp) Initialize, pass the expression as a String. The expression has the form “a.b (relop) val|(c.d) AND|OR a.b(relop) val|(c.d) where a, c are table names; B,d are field names; >,<,<>,=,>=,<= are relop’s; and, val is any numeric value. Vector getTables() Returns a vector containing only the table names in the expression. Vector getFields() Returns a vector containing only the field names in the expression. Vector getTableFields() Returns a vector containing both the table names and the field names separated by a period. BuildSql Constructor: BuildSql(String expr) Initialize, pass the expression as parameter. The getTables(), getFields() and getTableFields() methods discussed in class TableBean are internally invoked. String getSql() This method wraps the expression in an SQL statement and returns the built SQL statement. DateDifference Constructor: DateDifference(int year1, int month1, int day1, int year2, int month2, int day2) Constructor that takes 6 arguments. It does not matter which order the two year, month, day argument sets are entered as the object sorts them internally into low and high dates.

PAGE 58

47 Table 4-10 Continued Class Name Method Name Explanation int getDayDifference() Determines the difference in the number of days between a beginning date and end date. The begin and end dates themselves are not included in the calculation. DBMetadataKit Vector getTables(Connection con) Takes the connection object as parameter and returns a vector of tables. Vector getColumnNames(Connection con,String tablename) Takes the connection object and table name as parameters and returns a vector containing column names in the table. DBOp void executeSql(Connection con, String sql) Used to run “action” SQL queries like insert, update, create, delete that do not return a resultset after execution. This method takes the connection object and the SQL string as parameters. ResultSet returnResultset(Connection con,String sql) This method is used to execute SQL queries that return a resultset after execution. Typical examples of this are “Select” statements. This method takes the connection object and the SQL string as input parameters. GetAlertNames Constructor: GetAlertNames(Connection con) Initialize, pass the connection object as parameter. Vector get_alert_names() Returns a vector that contains the names of all alerts defined in the system.

PAGE 59

48 Table 4-10 Continued Class Name Method Name Explanation Vector get_alert_temporal_names() Returns a vector containing the names of temporal alerts in the system (alert_type=“T”). Vector get_alert_other_names() Returns a vector containing the names of Other/Generic alerts in the system (alert_type=“O”). GetTableNames Constructor: GetTableNames(Connection con) Initialize, pass the connection object as parameter. String patient_labresults_table() Returns the name of the table in the medical database, in which the lab results are stored. patient_medication_table() Returns the name of the table in the medical database, in which the patient prescription/ medication details are stored. String patient_personal_table() Returns the name of the table in the medical database, in which the patient personal details are stored. GetMedicineTableFieldNames GetMedicineTableFieldNames(Connection con) Initialize, pass the connection object as parameter String patient_id_field() Returns the field that represents the patient_id field in the medicine table. String drug_family_field() Returns the field that represents the drug name in the medicine table. GetPatientLabAnalysisTables GetPatientLabAnalysisTables(Connection con) Initialize, pass the connection object as parameter. Vector getAnalysisTables() Returns a vector that contains all the tables in the lab_analysis_table table.

PAGE 60

49 Table 4-10 Continued Class Name Method Name Explanation GetPatientLabFieldNames GetPatientLabFieldNames(Connection con) Initialize, pass the connection object as parameter. String patient_id_field() Returns the field that represents the patient_id in the lab details table. String test_name_field() Returns the field that represents the test_name in the lab details table. String date_field() Returns the field that represents the date field in the lab details table. LabAnalysisFields LabAnalysisFields(Connection con, Vector tables) Initialize, pass the connection object and a vector containing the table names as parameters. Vector setFieldNames() This module loops through each table in the Vector “Tables” (passed to the constructor) and returns the field names in them. Vector getFieldNames() Returns a vector that contains all the fields in all tables collected in the setup phase as those containing lab analysis data in the form tablename.fieldname. LoginBean Connection getConnectresult() Returns the connection object after authenticating the user to the database. RaiseAlert RaiseAlert(Connection con) Initialize, pass the connection object as parameter. int raiseTemporal(String PatientID) Returns 1 if a temporal alert is generated for the patient whose id is PatientID, 0 otherwise.

PAGE 61

50 Table 4-10 Continued Class Name Method Name Explanation int raiseOtherAlerts(String PatientID) Returns 1 if a generic/other alert is generated for the patient whose id is PatientID, 0 otherwise. Int raiseInteractionAlerts(String PatientID) Returns 1 if an interaction alert is generated for the patient whose id is PatientID, 0 otherwise. Putting It All Together This section explains how the system is built from the ground up by means of simple algorithms that use the data structure, classes, and methods mentioned above. Temporal alerts Creation The clinician is presented with a template, which gathers information regarding the time-based alert. Typical details include the name of the test, time frame (in days or months) beyond which the results are invalid, the criticality, and the name of the alert. These details are stored in a table named alert_table. The field “alert_type” is set to “T” to indicate that this is a time-based alert, the field “condition” has the form “testname; no. of days” and, the field “active” is set to “Y” (default). For example, if the “condition” field of a temporal alert has the value Blood Test; 180, It means that the rule should be triggered if the blood test has been taken more than 180 days ago. Activation The following algorithm is employed to check whether a patient’s medical record triggers a temporal alert (invoking the raiseTemporal() method of the RaiseAlert class):

PAGE 62

51 1. Get the name of the table that stores patient lab test data. This is done by invoking the “patient_labresults_table()”method on the GetTableNames object. 2. Get the fields that store the patient_id, test_name, and test_date by invoking the patient_id_field(), test_name_field(), and the date_field() methods of the GetPatientLabFieldNames object. 3. Run a query that retrieves all temporal alerts from the knowledge-base (alert_table). 4. For each record obtained in step 3, get the testname and the validity period (this can be obtained from the “condition” field in the table). Loop through steps 5 and 6 for each record obtained. 5. Formulate a PreparedStatement that selects all records in the lab results table (obtained from step 1) that pertain to the particular Patient who has taken the test (i.e., the testname obtained from step 4), ordered by descending order of date. The rationale being that only the latest test should be checked for a time period lapse. 6. For each record returned in step 5, check whether the test is good (by comparing the date on which it was administered to the current date). If it is not, insert the patient record in the alert_generated table with the “handled” field set to “N” (default), indicating that it is an event that needs to be handled by the staff in charge. The date and time when the alert was generated is also logged. Generic alerts Creation Based upon the information that is gathered in the setup, the user is required to formulate the alert condition using the template provided for the purpose. The alert is a Boolean expression that is supposed to trigger when it evaluates to true. Prior to saving the rule in the knowledge base, a check is done to ensure that it is syntactically correct. The rule is required to be of the form: Table name.field name RELATIONAL_OP [table name.field name|numeric value] ([AND|OR] Table name.field name RELATIONAL_OP [table name.field name|numeric value])* where RELATIONAL_OP can be >,<,=,>=,<=, or <>.

PAGE 63

52 Activation The following algorithm is employed to check whether a patient’s medical record triggers a generic alert (invoking the raiseOther() method of the RaiseAlert class): 1. Get all the alerts that have alert_type =“O” form the knowledge base (alert_table). For each record obtained, loop through the following steps. 2. Retrive the “condition” field. Using this string, instantiate a BuildSql object, that wraps the expression into an SQL statement. Invoking the getSql() method on the object returns the SQL statement. Depending on the number of tables involved in the rule, the join condition is appended to the SQL statement. 3. Using the returnResultSet() method on the DBOp object (for details about DBOp, refer to Table 4-10), check whether the SQL statement created in step 2 resulted in any records being returned. If there is a match, insert the patient record in the alert_generated table with the “handled” field set to “N” (default), indicating that it is an event that needs to be handled by the staff in charge. The date and time when the alert was generated is also logged. Drug Interaction Alerts Creation The reason for keeping drug interaction alerts as a separate knowledge repository is that extensive studies have been conducted on various drug interactions and the results well documented. Knowledge acquisition from the existing systems must be made as easy as possible. Using the simple template provided, the user can enter the interaction of a particular drug with another drug, condition or food. One drug may have multiple interactions. Activation The following algorithm is employed to check whether a patient’s medical record triggers a drug interaction alert (invoking the raiseInteraction() method of the RaiseAlert class):

PAGE 64

53 1. Get the name of the table that stores patient medication data. This is done by invoking the “patient_medication_table()”method on the GetTableNames object. 2. Get the fields that store the patient_id and drug family by invoking the patient_id_field(), and the drug_family_field() methods of the GetMedicineTableFieldNames object. 3. Get all records from the patient medicine table (obtained in step 1) for the particular patient_id. 4. For each record obtained, loop through the steps 5 7. 5. Get the drug_name field in the record. 6. Retrieve all records in the table “alert_drug_interaction” corresponding to the drug_name obtained in step 5. 7. If there are any records obtained, insert the patient record in the alert_generated table with the “handled” field set to “N” (default), indicating that it is an event that needs to be handled by the staff in charge. The date and time when the alert was generated is also logged. Summary This chapter described the implementation details of WEB-SAM. Actual code samples were used to illustrate the concepts of Connection Establishment, Session Management, and Database Metadata. The use of Prepared Statement to enhance database performance and the centralized error handling mechanism used, have been explained in detail. A summary of the tables created by WEB-SAM and the classes (along with their member functions) was provided. Pseudocode for the generation of Temporal, Drug Interaction, and Generic alerts has been discussed..

PAGE 65

CHAPTER5 CONCLUSION This thesis presented a method to create a simple web-based SQL-driven clinical alerting system that can be used by a health care provider to create medical alerts on data in an existing medical database. The key idea is to use the database independence provided by JDBC and provide an application that wraps the rule entered by the user within an SQL statement that is executed at the server. Keeping in view, the relatively high volume of database processing that may be involved in such a rule processing system, some optimization techniques were presented. Where Can We Go from Here? The use of clinical alerting system for enhancing healthcare is becoming increasingly prevalent. As in many industries, the Internet and Web technology are seen as enablers for easy exchange of information. The rapid growth of technology, however, results in obsoletion. The best one can do is to develop software using good software engineering methods, with well-isolated modules and well-defined layers. Changes can then be incorporated with minimal down time. The following additions to WEB-SAM can be implemented to enhance the features it currently provides: Ability to incorporate the Arden Syntax based medical logic modules that have already been developed: There are already a huge number of medical modules (each of which perform a particular task) that are being used in some medical establishments. They contain some well-known medical rules coded in the Arden Syntax. It may be possible to devise a strategy to import those rules into a format that can be used 54

PAGE 66

55 by this system. The table that stores the alerts (alert_table) has been modeled to include most of the important fields present in the current specification of the Arden syntax. Of course, the arden syntax can be used to create extremely complex medical rules (because it is a programming language based mechanism), which may not be possible to import into WEB-SAM without substantial changes. For example , it may be required to break down a complex MLM into a set of simple rules. Notification /Acknowledgement system. If the logic engine and the process control determine that further action needs to be taken, a method of notifying the person in charge must be devised. In the situation that the alerting mechanism includes synchronous events (events that are generated in real time), this can be handled by a message box displayed directly on the screen that the physician is using. For asynchronous events however, the notification can be via email, pager, cell phone, or other such means. First, the recipients of the message must be determined. Maintaining a table that associates a clinician to a patient, and performing a lookup on it can easily accomplish this. Thereafter, a decision needs to be made regarding the technology to be employed to deliver the notification. This can be based on the criticality of the message and the communication devices available with the clinician. It is also necessary to have an acknowledgement system in place to determine whether a clinician has actually received the alert message Templates for Alert Entry. Various templates for alert entry can be provided based on feedback received from various users. The system developed provides templates to capture three different clinical scenarios – drug interactions, time based alerts, and general alerts. There are, however, many other clinical encounters that may require

PAGE 67

56 tracking. An extensive requirement analysis can help formulate templates that can cover more uses of the system. Aggregate data trends. Key indicators in patients can be observed over time and the system can be used to draw conclusions of clinical trends. Substitute Therapy Alerts. Over a period of time, the system will have enough data on which decisions can be based. For instance, if it is determined that a particular drug has a reaction to another drug, condition, or food, the system may suggest alternatives based on the repository of information that it already has, on best practices or on other criteria fed into the system. Summary This chapter served as the conclusion to the described design of WEB-SAM. A system with the specifications described in this report has been implemented and it seems to be an efficient GUI-based, user-friendly method to implement clinical alerts. It can be extended without too much effort to incorporate the ideas presented above

PAGE 68

APPENDIX A THE ARDEN SYNTAX The Arden specification covers the sharing of computerized health knowledge bases among personnel, information systems, and institutions. The scope has been limited to those knowledge bases that can be represented as a set of discrete modules. Each module, referred to as a Medical Logic Module (MLM) contains sufficient knowledge to make a single decision. Each MLM also contains management information to help maintain a knowledge base of MLMs. Health personnel can create MLMs directly using this format, and the resulting MLMs can be used directly by an information system that conforms to this specification [JEN01]. History: The Arden Syntax evolved from alerts and reminder systems at LDS Hospital in Salt Lake City, Utah; the Regenstrief Institute in Indianapolis, Indiana; Columbia Presbyterian Medical Center in New York City, New York; and several other academic efforts. The group first met at the Arden Homestead in Harriman, NY, which resulted in its name. The Arden Syntax was born out of the realization that the power of these alerts and reminder systems was in the knowledge, and that there was a need to make this knowledge portable, shareable, between information systems. Many commercial vendors adopted the standard and included it in their products. The Arden Syntax makes it possible to code the rules in a programming language. However, the relative difficulty coding new rules leads to other alternatives. A sample Arden Syntax Medical Logic Module for warning the provider of anemia is given in Figure A-1 [HRI96]. 57

PAGE 69

58 Maintenance: Title: Alert on low hematocrit;; Filename: low_hematocrit;; Version: 1.00;; Institution : CPMC; Author: George Hripcsak, M.D.;; Specialist: ;; Date : 1993-10-31;; Validation : testing; Library: Purpose: warn provider of new or worsening anemia;; Explanation : whenever a blood count result is obtained, the chmatocrit is checked to see whether it is below 30 Keywords: anemia; hmatocrit;; Knowledge: Type:data-driven; Data: Blood_count_storage:=event {‘complete blood count’} Hematocrit:= read last {‘hematocrit’} Evoke: blood_count_storage; Logic: If hematocrit is not number then conclude false; Endif; If hematocrit<30 then conclude true; Endif;; Action : Write “The patient’s hematocrit is low”;; End: Figure A-1 Example MLM Using Arden Syntax The Arden syntax is also based on the concept of Active Databases (Event Condition , Action principle). This is reflected by the slots evoke, data, logic, action, etc. The Arden Syntax isolates references to the local data environment (database) using curly braces (“{}”). Arden Syntax is built to be embedded in existing clinical information systems. It clearly defines the hooks to clinical databases and defines how an MLM can be called (evoked) from a trigger event.

PAGE 70

59 Because database schema, clinical vocabulary, and data access methods vary widely, the encoding of clinical knowledge using MLM must be adapted to the local institution to use the local clinical repository. This hinders the sharing of knowledge.

PAGE 71

APPENDIX B JAVA ENABLED DATABASES The appendix emphasizes the fact that Java Enabled Databases hold a great promise for high performance databases that are required for the healthcare industry. It is a successful attempt to blend Java portability advantages without losing the original capabilities of the underlying relational database. Using Java for creating triggers and stored procedures is much more attractive than using proprietary database languages. Active databases can trigger events and maintain the integrity of the data that is stored. Writing modules in Java that can be executed at the server can extend these SQL servers. Java can add User defined functions (UDF’s), stored procedures, and types to a database. The fact that Java is portable is important to most major manufacturers of databases – IBM, Sybase, Oracle, and Informix. Processing complex functions, String manipulation, statistical analysis, and rule execution is easier and more efficient when coded with a programming language instead of using SQL queries. A stored procedure is part of the application logic that executes on the database server. Depending on the DBMS, procedures are coded in SQL, a slight variation of standard SQL, or a programming language. Although the International Standards Organization (ISO) released a standard for procedures (or persistent stored modules), major database companies coalesced around Java as a stored procedure language. Major database vendors are committed to Java as a solution for server extensibility. 60

PAGE 72

61 Stored procedures are Java methods published to SQL and stored in the database for general use. To use these Java methods within the database, a run-time system must be invoked, which maps Java method names, parameter types, and return types to their SQL counterparts. Except for graphical-user-interface (GUI) methods, any Java method can be run in the DBMS. Database Triggers A database trigger is a stored procedure that is tightly associated with a specific table or view. The database engine invokes (fires) the trigger automatically whenever a given DML operation modifies the table or view. A trigger has three parts: a triggering event (DML operation), an optional trigger constraint, and a trigger action. When the event occurs, the trigger fires and a CALL statement invokes a Java method to perform the action. Database triggers, which are defined using the SQL statement CREATE TRIGGER, enable customization of the RDBMS. Typically, triggers are used to enforce complex business rules, derive column values automatically, prevent invalid transactions, log events transparently, audit transactions, or gather statistics. Stored procedures are compiled once and stored in executable form, so procedure calls are quick and efficient. Executable code is automatically cached and shared among users. This lowers memory requirements and invocation overhead. By grouping SQL statements, a stored procedure allows them to be executed with a single call. This minimizes the use of slow networks, reduces network traffic, and improves round-trip response time. Broad access to stored procedures permits sharing of business logic across applications. For example, a stored procedure that implements a business rule can be

PAGE 73

62 called from various client-side applications, all of which can share that business rule. Another advantage is that, maintaining a procedure on the server is easier than maintaining copies on various client machines. Developing Stored Procedures To develop Java stored procedures, the following four steps must be followed [ORA99]: 1. Write or Reuse Java Procedures: Procedures can be written using any Java IDE, or existing procedures can be reused. Programs can be developed in popular Java IDEs such as Symantec’s Visual Caf, Oracle’s JDeveloper, and Borland’s JBuilder. 2. Load the Procedures into the RDBMS: The Java source, class, and resource files should be loaded into the RDBMS as objects using the tools available in the database. For example, Oracle provides the loadjava command-line utility. 3. Publish the Procedures in the Data Dictionary: For each Java method that is callable from SQL, a specification needs to be written, which exposes the method’s entry point to the database. 4. Call the Procedures from SQL and PL/SQL: The Java stored procedures can now be directly called from SQL DML statements and from PL/SQL blocks and subprograms. Calling Java from Database Triggers A database trigger is a stored program associated with a specific table or view. The DBMS executes (fires) the trigger automatically whenever a given DML operation affects the table or view. A trigger has three parts: a triggering event (DML operation), an optional trigger constraint, and a trigger action. When the event occurs, the trigger fires and either a PL/SQL block or a CALL statement performs the action. A statement trigger fires once, before or after the triggering event. A row trigger fires once for each row affected by the triggering event. Within a database trigger, the new and old values of changing rows can be referenced using the correlation names new and old. In the trigger-action block or CALL statement, column names must be prefixed with “:new” or “:old”

PAGE 74

63 Suppose we want to create a database trigger that uses the Java class shown in Figure B-1 to log out-of-range salary increases, we would need to perform the following steps (this example is specifically for Oracle 8i but the concepts involved for any other Java enabled database system are similar): 1. As shown in Figure B-1 below, create the Java classes that handle Database Operations. The class DBTrigger has one method named “logSal,” which inserts a row into the database table named “sal_audit.” import java.sql.*; import java.io.*; import oracle.jdbc.driver.*; public class DBTrigger { public static void logSal (int empID, float oldSal, float newSal) throws SQLException { Connection conn = new OracleDriver().defaultConnection(); String sql = "INSERT INTO sal_audit VALUES (?, ?, ?)"; try { PreparedStatement pstmt = conn.prepareStatement(sql); pstmt.setInt(1, empID); pstmt.setFloat(2, oldSal); pstmt.setFloat(3, newSal); pstmt.executeUpdate(); pstmt.close(); } catch (SQLException e) {System.err.println(e.getMessage()); } } } Figure B-1 An example Java Class 2. Register the class with the Database and indicate that it is an entity external to the database. Because logSal is a void method, it needs to be published as a procedure (and not a function). This is shown in Figure B-2. CREATE OR REPLACE PROCEDURE log_sal ( emp_id NUMBER, old_sal NUMBER, new_sal NUMBER) AS LANGUAGE JAVA NAME 'DBTrigger.logSal(int, float, float)'; Figure B-2 Publishing the method.

PAGE 75

64 3. Create the database table sal_audit, as shown in Figure B-3. CREATE TABLE sal_audit ( empno NUMBER, oldsal NUMBER, newsal NUMBER );. Figure B-3 Creating the Table 4. Finally, as shown in Figure B-4, create the database trigger, which fires automatically when a salary increase exceeds twenty percent. CREATE OR REPLACE TRIGGER sal_trig AFTER UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal > 1.2 * old.sal) CALL log_sal(:new.empno, :old.sal, :new.sal);. Figure B-4 Creating the Database Triggers The Aurora JVM in Oracle 8i Oracle’s Java Virtual Machine (known as the "Aurora JVM") is a complete, JDK 1.1.6-compliant Java execution environment. The Aurora JVM runs in the same process space and address space as the RDBMS kernel, sharing its memory heaps and directly accessing its relational data. This design optimizes memory use and increases throughput. The Aurora JVM includes a native-code compiler called Jserver Accelerator that speeds up the execution of Java programs by eliminating interpreter overhead. It translates standard Java class files into specialized C source files that are processed by a platform-dependent C compiler into native libraries, which the Aurora JVM can load dynamically. Unlike just-in-time (JIT) compilers, the JServer Accelerator is portable to all OS and hardware platforms. To speed up applications, the Aurora JVM is supplied with natively compiled versions of the core

PAGE 76

65 Java class libraries, embedded ORB, and JDBC drivers. Figure B-5 shows how the Jserver Accelerator speeds up database operations [ORA99]. Execution Speed = 10X Execution Speed = X C Compiler C Source Code Jserver Accelerator Java Intepreter Java B y teCode Java Compiler Java Source Code Native Code Figure B-5 The JServer Accelerator.

PAGE 77

LIST OF REFERENCES [BAL02] Balano, Brossier Kirsten, Information About Drug Interactions, http://www.ucsf.edu/warmline/drugcht/druglist.html , visited 3 rd Jan. 2002. [BER00] BergSten, Hans, JavaServer Pages, O’Reilly & Associates, Dec. 2000. [BRO01] Brown, Kyle, Understanding JDBC Metadata, http://members.aol.com/kgb1001001/Articles/JDBCMetadata/JDBC_Metadata.htm visited 10 th Nov 2001. [CHU01] Chuang, Jen-Hsiang, Clinical Event Monitor at Columbia-Presbyterian Medical Center (CPMC), http://www.cpmc.columbia.edu/homepages/chuangj/arden , visited 15 th Oct. 2001. [CIM96] Cimino, James J., Sengupta, Soumitra , Clayton, Paul D., Patel, VL, Kushniruk, A., Huang, X., Architecture for a web-based clinical information system that keeps design open and the access closed, Proceedings AMIA, 121-125, 1998. [DAR98] Darby, Chad, Optimizing Database Transactions with Java Database Connection (JDBC), http://www.j-nine.com/pubs/optjdbc , Java Report, 20 th May 1998. [HAE01] Haefal, Richard Monson, Enterprise JavaBeans, O’Reilly & Associates, Cambridge MA, 1 st Sept. 2001. [HAN97] Hanson, Eric N, Al-Fayoumi, Nabeel, Carnes, Chris, Kandil, Mohktar, Liu, Huisheng, Lu Ming, Park J.B, Vernon, Albert, TriggerMan-An Asynchronous Trigger Processor as an Extension to an Object-Relational DBMS, Univ. of Florida CISE Dept. TR97-024, Dec. 1997. [HRI94] Hripcsak, George, The Arden Syntax for medical logic modules:introduction, Computers in Biology and Medicine 24(5):329-30, 1994. [HRI96] Hripscak, George, Clayton, Paul D., Jenders, Robert A., Cimino, James J., Johnson, Stephen B., Design of a Clinical Event Monitor, Computers and Biomedical Research 29, 194-221, 1996. [HRI99] Hripcsak, George, Cimino, James J., Sengupta, Soumitra,WebCIS: Large Scale Deployment of a Web-based Clinical Information System, American Medical Informatics Association, 804-808,1999. [HUN98] Hunter, Jason, Crawford, William, Java Servlet Programming, O’Reilly & Associates, Cambridge MA, Oct. 1998 66

PAGE 78

67 [JEN01] Jenders, Robert.A, The official Arden Syntax website, http://www.cpmc.columbia.edu/arden , visited 8 th Aug 2001. [JEP97] Jepson, Brian, Java Database Programming, Wiley Computer Publishing, New York, 1997. [JSP01] JSP Reference, http://www.jspinsider.com/reference/jsp/index.html , visited 8 th Nov. 2001. [KIN98] King, Nelson, Java in the Database Server, DBMS, June 1998. [NOR99] North, Ken, Java in the Database, JavaPro, June 1999. [OPP01] Oppenheim, Michael I., Mintz, Ronald J., Boyer, Aurelia G., Frayer, William W., Design of a Clinical Alert System to Facilitate Development, Testing, Maintenance and User Specific Notification, http://www.amia.org/pubs/symposia/D200326.PDF , visited 15 th Oct 2001. [ORA99] Oracle8 I, Java Stored Procedures Developer’s Guide, Release 8.1.5, Feb. 1999. [REE97] Reese, George, Database Programming with JDBC and Java, O’Reilly and Associates, Cambridge MA, 1997. [SEL79] Selinger, Astrahan, Chamberlin, Lorie, Access path selection in a Relational database Management System, Price; IBM Research Division, San Jose, California, ACM 0-89791-001-X/79/0500-0023, 1979. [SYB92] Sybase, Inc. User Manual, Emeryville, CA Sybase Inc, 1992. [WAG97] Wagner, Michael M., Pankaskie, Marvin, Hogan, William, Tsui, Fu-Chang, Eisenstadt, Stuart A., Rodriguez, Eric, Vries, John K., Clinical Event Monitoring at the University of Pittsburgh, American Medical Informatics Association, Nashville, TN, 188-192, Oct 1997

PAGE 79

BIOGRAPHICAL SKETCH Shankar Manamalkav was born on August 19, 1977 in Coimbatore, a town in Tamil Nadu, India. Most of his schooling was in Bharatiya Vidya Bhavan’s, Hyderabad. He received his bachelor’s degree in computer science and engineering from Osmania University, Hyderabad, in 1998. Before studying at the University of Florida for his master’s degree, Shankar worked as a Senior Software Engineer with Satyam Computer Services (India) from August 1998 to August 2000. His areas of interest include database optimization and programming languages. 68