Client-Server Computing / Part Three: Oracle Card, by Rodney B. Murray, Ph.D.

This is part three of a series on "client-server" computing and how it will revolutionize academic computing at Jefferson. Part one discussed network terminology and issues surrounding data security and access (Medibytes, Fall '92). Part two discussed SQL database basics (Medibytes, Winter '93).

Background

Oracle Card (Figure 1) is a version of the PLUS authoring system (Spinnaker Software, Inc.) specially adapted by Oracle Corporation to work with its relational database. For those familiar with HyperCard or PLUS, Oracle Card is very easy to learn. All three programs are object oriented authoring systems based on the stack of cards metaphor. For more advanced programming, an English-like scripting language called Oracle Talk is available (similar to HyperTalk).

There is one important difference between PLUS and Oracle Card. A database built in PLUS would be a simple "flat file" database where each card in a stack would be a different record in the database. This has obvious disadvantages in a multi-user situation such as ours, where many students need to access the same information. Every time the database is changed, the PLUS stacks would have to be updated on every machine. Client-server computing simplifies this.

In Oracle Card, one card on the client computer is used to display a view of the data residing on the database server -- in this case, the OAC's IBM RS/6000 on the University ethernet network. As the user clicks on the "next record" button, it appears that you are flipping through a stack of cards, but in reality, data is coming over the network, filling in the fields as you advance from record to record.

Designing the Database

A relational database is made up of linked tables. (See Part 2 of this series for a more detailed discussion of SQL database basics.) Oracle Card comes with Table Builder and Query Builder (both written in Oracle Card). Table Builder makes it very easy to create tables while sitting at the Mac or PC client station, without ever having to log in to the server machine (or read the voluminous Oracle manuals). Likewise, Query Builder allows one to build ad hoc queries with little or no knowledge of SQL.

The following example will use the relational database model for the Patient Encounter database introduced in the last installment of Client-Server Computing: SQL Database Basics.

Step one is to create a Patient Table. Table Builder allows the design of a table using a simple point and click interface. First, click on "Create" and name the table PATIENT. Next, click on "Add Column" (a column is equivalent to a field) and fill in a dialog box entering the type and length of data intended for this field (Figure 2).

After adding all four columns to the Patient table, create the next table, ENCOUNTER, and add its six columns. Figure 3 shows the Table Builder screen after defining these two tables. Note that three different data types, NUMBER, DATE, and CHAR (for text), were used to define column data. (It is also possible to store graphic data or scanned images using the PICTURE data type.) This helps Oracle to handle the data in an efficient way and also serves as a guard against data entry error. Several fields were also defined as NOT NULL. This forces the user to enter important data into the database before it can be saved.

During these steps, the Oracle Card client constructed a series of SQL commands and sent them to the Oracle server over the network. Then, the Oracle server created the necessary space to store the data and indexes to keep track of the data. Now the user needs a way to get data into the tables.

Creating an Oracle Card Application

To create a custom application stack to allow data entry, pull down the File menu and click "New." Name the custom application "Patient Encounter DB." A dialog box asks for the name of the table to link to the first card in the stack -- select "PATIENT" from the supplied list. (Note that behind the scenes Oracle Card used SQL to query the server to send back a list of all tables which the user has permission to use.) The next dialog asks for the "operations" which are to be used on this card. Select all: FIND, CLEAR, SAVE, DELETE, PREVIOUS, NEXT, and REVERT. This action cause Oracle Card to automatically create buttons on the card to carry out the indicated actions by imbedding the appropriate SQL code into the Oracle Talk scripts executed by the buttons. The resulting card appears in Figure 4. Note that the fields are temporarily labeled with the name of column data that will appear.

Next, the user might want to rearrange the fields, add some explanatory labels and text to enhance the appearance of the first card in the stack -- a window into the PATIENT table stored on the server. A similar procedure is followed to add a second card to the stack -- a window into the ENCOUNTER table. When creating the second card Oracle Card asks whether this card is a MASTER or DETAIL card. DETAIL is selected since there will be several encounter records for each patient record. A dialog box asks to specify which columns are used to link the two cards -- PATIENT-ID is selected for both tables, since it is the key field that links the two tables. Buttons to move back and forth between the two cards are added at this stage. Figures 5 and 6 show the complete, if simplistic, cards with some sample data.

Once the custom data entry application has been created and data has been entered, the user may want to create a report to access the data.

Creating a Report

Query Builder is used to easily create ad hoc queries or reports from the database. Although it is somewhat limited in its formatting options, it requires little knowledge of Oracle or SQL. Alternately, one can build a reporting mechanism into the Patient Encounter DB application by writing Oracle Talk scripts combined with appropriate SQL code.

Figure 7 shows a Query Builder card as a query is being defined using a simple point and click procedure. This query will list all of the patients in the database. Note, at the top of the screen, the actual SQL code appears as it is being assembled. All queries can be saved for use at a later time.

In another Query Builder card (not shown), the appearance of the report generated by the query can be modified. The final report is illustrated in Figure 8. Note that reports can be saved in a file, viewed on the screen or saved to the clipboard for insertion into a text file or spreadsheet.

Summary

Although this was a very abbreviated tour of Oracle Card and used a very simplistic example, the power and ease of use of Oracle Card should be obvious. The client-server scenario is ideally suited to many data display and management problems. In the next installment of this series, "Q&A Student" will be discussed -- the front end application for a question and answer database used for self assessment by Jefferson students.