The Effect of Relational Database Technology on Administrative Computing at Carnegie Mellon University


Copyright 1990 CAUSE From _CAUSE/EFFECT_ Volume 13, Number 4, Winter 
1990. Permission to copy or disseminate all or part of this material is 
granted provided that the copies are not made or distributed for 
commercial advantage, the CAUSE copyright and its dateappear, and notice 
is given that copying is by permission of CAUSE, the association for 
managing and using information resources in higher education. To 
disseminate otherwise, or to republish, requires written permission. For 
further information, contact CAUSE, 4840 Pearl East Circle, Suite 302E, 
Boulder, CO 80301, 303-449-4430, e-mail info@CAUSE.colorado.edu


               THE EFFECT OF RELATIONAL DATABASE TECHNOLOGY ON
           ADMINISTRATIVE COMPUTING AT CARNEGIE MELLON UNIVERSITY
                  by Cynthia Golden and Dorit Eisenberger

************************************************************************

Cynthia Golden is currently Associate Director of Administrative Systems
at Carnegie Mellon University, responsible for various aspects of
administrative computing, including new development projects as well as
long-range planning, system maintenance and production, training, and
end-user support. Ms. Golden's previous work as a database analyst was
centered on relational database design and development. She holds a
master's degree in information science from the University of
Pittsburgh.

Dorit Eisenberger is currently employed by the government of Israel.
While on sabbatical leave at Carnegie Mellon University, she spent two
years as a senior programmer/analyst in the Administrative Systems
department, working with relational database systems. She has fourteen
years' experience in both technical and managerial areas of information
technology. Ms. Eisenberger holds an MS degree in industrial management
from The Technion in Haifa, Israel.

************************************************************************

ABSTRACT: This article looks at the impact of Carnegie Mellon's decision 
to standardize its administrative system development efforts on 
relational database technology and structured query language by 
examining one of the University's larger, more widely-used applications, 
the University Information System. Advantages of relational technology, 
as well as new responsibilities and challenges associated with its 
adoption, are discussed from the point of view of both users and the 
central administrative computing unit.

Carnegie Mellon University has chosen to standardize its new
administrative system development efforts on relational database
technology and SQL (structured query language). The INGRES relational
database management software is currently in use in several development
projects. One administrative computing application, the University
Information System (UIS), uses relational database technology to
distribute access to student and employee data. The UIS serves as a good
test project for examining how relational database technology has
affected administrative computing at Carnegie Mellon. Before discussing
the UIS, however, an overview of the computing environment and
administrative computing direction at Carnegie Mellon is needed to
provide the context for such an evaluation.

The computing environment at Carnegie Mellon can easily be described as
diverse.[1] Students use a variety of personal computers, workstations,
and some mainframes for coursework and research. The administrative
computing environment is slightly more homogeneous. Word processing and
office data processing functions take place primarily on personal
computers, particularly Macintosh and IBM models, which are often linked
together through local area networks to share resources like printers
and file servers. A VAX cluster, Sequent Symmetry, Sequent Balance 8,
and several other workstation-class machines currently support central
administrative computing.

The Administrative Systems (AS) department, which serves the
central computing needs of the University, is divided into three groups:

  *  Systems Development is responsible for all current development
efforts in the areas of human resource management, student information,
and alumni information.

  *  Systems Support manages the day-to-day production needs of the
University.

  *  Systems Technology is responsible for research into new methods of
managing administrative computing, for beta-testing new software, and
for investigating new technologies, including new hardware options such
as multi-processors or parallel processors.

The stated direction of administrative computing at Carnegie Mellon
encompasses the relational database/SQL standard combined with hardware
and operating system independence. The INGRES product, selected for use
as a basis for all development projects, runs in many operating system
environments, including UNIX, VM, and DOS.[2] The operating system and
hardware independence will allow us to take advantage of new
opportunities presented by constantly changing hardware technology by
giving us the freedom to be able to easily move applications from one
environment to another.

It is both useful and interesting now to look at the impact of our
decision to standardize our development efforts on relational database
technology. Several relational applications have been in use since
development began in early 1984. These include systems like inventory
control, work order management, and telecommunications management. In
the past one and one-half years, several new systems based completely on
relational database technology have been released to the campus. These
include large-scale systems with many users, such as the human resource
management system, the student information system (which manages student
records, admissions, and student accounts receivable), and some smaller
applications used by only a few offices, such as the property management
and fixed assets system. Many other development projects are in
progress.

Almost any of the relational systems at Carnegie Mellon could be
used as the "example system" in this article, but we have chosen the UIS
because it is one of the University's larger, more widely-used
administrative relational database applications, and because it was used
as a "transition system" to help us move into a wider use of this
technology.


THE UNIVERSITY INFORMATION SYSTEM

The UIS is a relational database application that provides users with
query access to student and/or employee data. Vice presidents,
department heads, and administrative and clerical employees from both
the academic and administrative sides of the University make up the
approximately 200 people who have access to the database.

The need for such an inquiry system was evident at Carnegie Mellon
several years ago, when it was apparent that the central systems,
written in COBOL and housed on Digital Equipment Corporation's DEC-20s,
were not serving the needs of the user community. These systems allowed
access to data in a very limited fashion, and this access was slowed by
problems with over-loaded machines that not only had no upgrade path but
were in fact being discontinued by the vendor. While plans were being
formed to replace these systems, particularly the student records and
payroll/personnel systems, the new versions of the applications were too
far away and would not solve immediate problems related to user access
to data. Though some data were available to users in the form of
standard screens and printed reports, the need existed for access to
data in a more ad-hoc fashion. That is, users wanted to be able to
produce their own class rosters, teaching load reports, and salary
surveys as they were needed, or to examine data in their files quickly
and easily.

Because the technology to allow users this kind of access to data
was available in the form of relational database software, and because
the need for access to information on campus was so strong, we chose to
implement an interim solution. The UIS was built to give users a way to
get the data they needed to do their jobs while the new systems were
being designed and developed. Formatted screens and standard reports,
similar to those available in the old system but more flexible and
robust, were included in the UIS, along with the availability of SQL for
users to write and run their own ad-hoc queries.

Previous versions of the UIS received their data on a nightly
basis, via file transfer protocol from the source systems on the old
DEC-20 systems. Since the new relational-based student information
system and human resource information system have been released, data
contained in the UIS continue to be extracted from the new systems and
shipped on a daily basis to a Sequent Symmetry running the Dynix
operating system, where they are loaded into the database.

The decision to continue to maintain an "inquiry-only" database
after the release of the new systems was made for several reasons.
First, having the second database would reduce the load on the primary
database. This would be helpful during the first months of the release
of the new systems, while bugs were being worked out and the system was
monitored. Second, since the feed to the UIS was already in place and
the applications were in use, it would not require much effort to simply
continue the data transfer. This also allowed the development of the
"inquiry" portions of the new systems to be delayed until a later date.

Screens, queries, and reports

As mentioned earlier, the UIS data consist of student and employee
information. Separate applications exist for access to these data. Each
application consists of standardized screens, queries, and reports. A
main menu allows the user to choose which subset of the application she
wishes to run. The menu, which is written in C, allows the user to
choose to run the screens, queries, or reports, print copies of the
documentation, check when the data were last updated, and/or enter the
SQL query facility.

All the UIS screen applications consist of an entry screen and data
screens. The entry screen gives the user the ability to search for a
student or employee by name through the "name search" option. A list of
all names that match the criteria typed by the user will be displayed in
a special screen if the "name search" option is selected. The user can
then select the student or employee he needs and call one of the data
screens.

Each data screen in the UIS contains data about one entity: a
student or a course or an employee. For example, in the student screens
application, the "roster" screen shows all students in a certain course
and section for a given semester. The user can move easily from screen
to screen by typing the new screen code over the current screen code.
Similarly, semester or ID number can be changed to see new data.

Commonly-used queries were written and installed in the UIS in a
menu format. No special skills, such as knowledge of SQL, are required
to run the query option of the application. Users simply select the
desired query and enter a few simple parameters. When a query is run,
the results are written to the screen as well as to a file, which users
can manipulate further. Many users take the query results and transfer
them to their personal workstation for use in spreadsheet programs,
statistical packages, or label generation.

The report generation option gives users the opportunity to produce
results for larger, complex queries that are needed on hard copy, and
that are not of a time-critical nature. Again, the desired report is
selected from a menu of available reports, and parameters are entered
prior to execution. In order to control system load during the day and
avoid large disk space allocations for each user that would be required
by INGRES to run the reports, the report requests are submitted to a
batch queue where they are executed when system utilization is low.
Generally, users have less than a 24-hour turn-around time from the time
they submit the report request until the time they receive the hard-copy
results.

The data: access and protection

One of the driving forces behind the implementation of the UIS was
the desire of the users to have better access to data. The UIS screens,
queries, and reports were duplications of and enhancements to the
features available on the old central systems. The ad-hoc query facility
available as part of the relational database management system (DBMS)
was the key to providing the broader access that many users required in
order to be able to retrieve data not available through standard reports
or on the inquiry screens. The ad-hoc query option is available from the
main menu and requires a knowledge of SQL, the standard data
manipulation language used by most relational database systems. Users
may form their own statements to select data from the database, perform
aggregations, or do simple file extracts.

The UIS database consists of approximately 350 megabytes of data
representing information from 1975 through the present, contained in
over 100 tables or relations. Several types of tables exist in the UIS:
main data tables, index tables, translation or utility tables, and user-
owned tables. The main data tables contain data shipped to the UIS from
the central systems. For example, the student schedule table, which
contains a record for every student for every course taken during a
semester, consists of almost one million rows 30 bytes wide. Index
tables are created when the database administrator defines primary and
secondary keys for data access, and are important for efficient
execution of queries. Translation tables hold codes and their meanings,
and are used consistently by the screens, queries, and reports to
provide the English translation for a given code. Other utility tables
include those used for tracking access authorization for system users.

The UIS data reflect the contents of the central systems, and are
available to users for query access only. Users are not permitted to
change any data in the UIS. Any errors found must be corrected at the
source, which is the central system. All data in the UIS are considered
to be sensitive data (grades, salaries), so tight protection is needed.
The database management system provides a permit facility that allows
data to be protected at the table level or to be based on values in
fields. Once specific "permits" on data tables are applied for a
specific user, that user's world of view is limited to a defined
population. Whether he is running the screens, a query, or a report, or
executing his own SQL statement, the data available are always that same
base population.


ADVANTAGES OF RELATIONAL TECHNOLOGY

What has advancing from the previous generation of traditional, COBOL-
based systems to applications using relational database management
software brought to the University? The amount of effort involved in
making this move is very clear, but sometimes it is difficult to see in
advance what the benefits or problems will be.
From our present vantage point, the benefits of using a relational
model for administrative computing are many, for both users and the AS
organization.

For users

We have found the primary advantages of using relational database
technology from the users' point of view are improved data access and
manipulation and the ability to participate in the design process.

     Improved data access and manipulation

For those users who have no need or desire to go beyond the use of
simple inquiry screens, any differences between an application written
using a relational database manager and an application written in COBOL
may go unnoticed. However, those users who in the past were well aware
of the structure and limitations of their COBOL application seem to
appreciate the additional power they have with direct SQL-based access
to the data.

The relational model is easy for novices to understand. The
database is structured in the way that one would naturally describe
data, grouping data into tables of logically related information about a
single entity. As Chris Date, a well known authority on relational
database management, explains it: "A table consists of a row of column
headings together with zero or more rows of data values. For a given
table, (a) the column heading row specifies one or more columns; (b)
each data row contains exactly one value for each of the columns
specified in the column heading row."[3] The relationship between tables
in the database is expressed by common, key fields. The concept of "data
independence" insulates the users, as well as the programmers, from
having to understand complex data structures, pointers, or underlying
data access methods in order to manipulate data.

Even the novice user can use the tools that are standard in most
relational DBMSs to do simple, forms-based inquiries of the data.With a
basic understanding of the table structure and SQL, the novice can
perform simple select statements to retrieve data.

More sophisticated users have found the relational DBMS and its
associated tools to be very powerful. With some training to become
familiar with SQL and learn the database structure associated with an
application, users are able to do ad-hoc, complex data manipulation.
Many of our users have created their own tables to use in conjunction
with the application tables. These "private" tables can be joined easily
with the up-to-date data in the databases. Users have also found it
unnecessary to store redundant data in their own personal computer
applications, since the central data are now easily accessible. It is
also much easier now to move any needed data to personal computer
applications for use with other software packages, due to the easy-to-
use command to extract data from the databases and the widely available
access to data transfer programs like FTP or KERMIT.

Data can be viewed in ways previously impossible without complex
programming, largely due to the data independence in relational
databases, or the separation of users and user programs from the details
of the way the data are stored and retrieved. For example, using the old
systems to produce a class roster required that a complex COBOL program
be run that would produce a roster for every class offered that term.
The output file was then divided and distributed to the appropriate
departments. Changes to this procedure required users to request AS to
do the programming and testing, and then to wait weeks for the results.
Now, not only can a user do a simple "join" of a few tables, she can
limit her request to rosters for a single department or even a single
course, all in one SQL "select" statement.

Views can also be defined to make life easier for an end user. A
"view" is a way to allow a user to look at one or more tables as one
entity, providing logical data independence. When defining the view, the
links between the tables are established, and the joins are executed
every time the view is selected.

The users' ability to do ad-hoc selections of data on their own has
in many cases reduced their dependency on central computing staff.
Administrative users on campus need not wait the several weeks it could
have taken in the past to have a requested report written, tested,
debugged, and run by the central computing staff, who address requests
in order of priority. They can write their own request, submit it, and
have their results almost immediately. The Office of University Planning
in particular has made extensive use of the query and report generation
facilities available in the UIS to do the large amount of reporting
functions for which they are responsible. Individual departments on
campus are also producing their own class rosters, reports, and academic
audit records with the UIS database.

     Participation in the design process

Perhaps some of the most unique advantages of the relational
database for users concern their participation in the system design
process. In more traditional system development environments, user
requirements were translated into an information system by the
programmers. Often, by the time the system was completed user
requirements would change. Now, because of the easy, rapid prototypes
that can be done by the system developers, users get to see the system
in earlier stages in the development life cycle and consequently offer
suggestions or criticisms earlier. Users do not have to anticipate all
their needed reports or functions during the system design stage. It is
relatively easy to write a new report or add a new screen once the
initial system design is in place. Adding such objects does not require
any changes to the underlying database and can be done easily using the
relational technology tools.

For Administrative Systems

The relational database and its fourth-generation language (4GL)
tools have also provided many advantages from an administrative
computing perspective at Carnegie Mellon. Overall, the relational model
and its way of representing data in tables has been a useful model for
developers. In the database design process, the relational model forces
the developers and the users to view data in sets and examine the
relationship between those sets. This helps to break down the process of
designing large, integrated databases into smaller tasks and to
facilitate communication between user and developer. The tools have
improved programmer productivity, enhanced the flexibility of systems
development, provided portability for administrative computing
applications, simplified maintenance and security, and paved the way for
a distributed database environment in the future.

     Productivity

Most relational database packages include standard tools to build
applications, which have helped increase both the level of productivity
and the overall effectiveness of applications programmers. The logic
portion of the application is specified in an application builder while
screen construction is accomplished through a forms builder, often
referred to as a "screen painter." This separation allows for easier and
faster changes to be made to an application, since the two functions can
be done independently.

By using a forms builder, it is simple to specify and later change
field location, field validation, screen titles, and field display
attributes, such as on-screen highlighting, default values, or color.
Once a screen has been created, any feature can be easily adjusted. The
DBMS software handles all the screen I/O, so no complex program is
needed. In a matter of a few minutes, a developer can construct a simple
screen and be entering or selecting data from the database.

Also available with most relational DBMS packages are forms-based
query tools that allow developers to add, change, or delete information
in the database tables, as well as simple forms-based reporting tools
that can enable them to do quick reports. Most systems also include a
report-writer that allows more complex reports to be written quickly,
using an often proprietary interpreted language that builds a definition
for the report and stores it in the database. The required commands are
usually quite simple to use, and allow fairly complex reports to be
written without using a more complex programming language like COBOL or
C.

Finally, the application builder allows the developer to tie
together the pieces of the application written, using all the tools just
mentioned as well as a 4GL used to specify most of the application
logic. The availability of the 4GL has in most cases reduced the
necessity to write C or COBOL routines. In our experience, only in
situations where the procedure required was too complex for the 4GL to
efficiently handle or where high performance was critical, was the 4GL
application converted to C and optimized for maximum performance.

A simple application can be produced using the 4GL and other tools
in less than a day. During the application development process, this
ability to very quickly make a system prototype has been very valuable
to our developers. The developer can start with an application "shell"
that contains the basic screens and functions, and work with users to
alter or enhance the application. Very little work is required up front
for this process to take place, and consequently little work is lost in
designing something the user does not like or wants to change
substantially. By using the 4GL application builder, the prototype of
the system is the real basis for the final product, i.e., in most cases
the prototype becomes the final product.
In general, we have experienced an increase in overall development
productivity since the relational database and 4GL tools have been used.
The decrease in development time has given us the opportunity to spend
more time up front on systems analysis issues, and has given us the
ability to get system prototypes in the hands of the users faster than
ever before.

     Flexibility

We have also been pleased with the flexibility of our relational
databases. In cases where they were needed, changes to the system were
easy to make, even after the final production release of the application
was in place. Although adding a field to a table does require that the
data in that table be unloaded and then reloaded, it does not affect the
applications already in place. The application code will still run,
without change or re-compilation. This is a true advantage over more
traditional hierarchical or network databases, where application code is
based upon the file structure. For example, a major change made to the
UIS after its initial release was a re-structuring of the student
biographical data table. A decision was made to change the database to
more accurately reflect University record-keeping, which would reduce
the table size from 800,000 rows to 40,000 rows. This change -- which
required changes to the data loading programs, some screens, queries,
and reports, and the database format -- took less than one week to
implement. In a non-relational system, this would have involved a
complete re-write and re-compilation of much of the application code.

     Portability

Portability of applications across different hardware and operating
system environments also is important at Carnegie Mellon. To take
advantage of new technological advances as they arise as well as to
reduce dependence on proprietary hardware or operating systems, the
ability to easily move an application from one environment to another
will become essential. With the relational database software
we have been able to demonstrate that this is a reasonably simple thing
to do. As an example, the UIS was moved from its original home on an IBM
3083 to a VAX 8700 to free the machine for academic use. The process of
moving the application took less than one month, with most of the time
being spent on making any operating-system specific changes, such as
path names in references to files, in re-compiling COBOL code that had
embedded calls to the database, and in re-writing the application menu,
which was converted from REXX to DCL. A second port of the UIS was
required so that free machine cycles on a Sequent could be used. The UIS
(database only) was moved from the VAX 6430 to a Sequent Balance 8
machine overnight. The pieces of the code written in COBOL and DCL were
re-written in C on the Balance, to make the application as portable as
possible. As a test, we were able to move the entire application from
Dynix back to VMS in less than a day.

      Maintenance and security

Development with 4GLs has greatly simplified system maintenance.
Because the application builder allows us to tie together many
procedures and screens, if a change must be made to one part of the
application, the entire application need not be re-compiled. Storing
data in tables rather than having it hard-coded in programs also allows
users to change applications with no programmer intervention.

As mentioned earlier, the underlying permit facility handles access
to data at all levels and is very flexible. Programmers are not required
to write data access routines for each application. Facilities for
database auditing, "checkpointing," and "journaling" have also
eliminated the need for any programs to be written to perform these
functions important to database security, backup, and recovery.

     Distributed database capability

Finally, we see distributed database capability as being a true
asset to our administrative computing efforts in the years to come.
Distributed DBMSs allow two or more databases to be opened
simultaneously, whether they are on the same node or on different nodes.
This feature is used frequently in administrative applications when
connectivity to other databases is required, and allows us to eliminate
duplication of data.

A further benefit of distributed database technology is found in
the ability to distribute data across nodes which are linked together
via a communications network, like DECnet or TCP/IP. The data can be
stored on any number of nodes and users at any node can see any of the
data without having to know the storage location. In this way, more data
are available to more people, while at the same time it is possible to
store each piece of data on the machine where it is most often used to
maintain efficiency.


NEW RESPONSIBILITIES AND CHALLENGES

Although the benefits of relational database are many, it is also
important to look at some of the side-effects associated with moving to
this technology and at what is required to use it effectively and
efficiently. Many database vendors make great claims about the rate at
which your productivity will increase, how simple the tools are to use,
and how much less work you will have to do. Be careful not to be fooled
by such claims. Although productivity does tend to increase, nothing
happens overnight! Use of these tools and technology at Carnegie Mellon
has brought an accompanying set of new responsibilities and challenges,
for both users and AS.

For users

Along with improved access to their own data, users now have the
responsibility of learning how to effectively use the tools that are
available to them. If they choose not to take advantage of SQL or the
database tools, most of the advantages of the move to relational
database will be lost to the user. We have found that support of the
system by upper management is important to the learning process and the
general use of the system. High-level support for the technology has had
an effect on its use at Carnegie Mellon, particularly in the case of the
UIS. When users at the vice-presidential level used the UIS and saw the
advantages that this system afforded, the word spread to others on
campus who were also able to take advantage of the newly available data.

It is important to users that they set aside time to attend classes
to learn the structure of the database and to learn SQL. An in-depth
understanding of what data are contained in which tables is extremely
important to the user who will be writing SQL statements to retrieve
this data. We have found it important to have users reserve some time
each day to spend in practice sessions in order to become familiar with
INGRES and SQL, the database, and the operating system. We have seen
many users attend an SQL class and then not practice what was covered in
class. When they need to do an ad-hoc query, they have forgotten what
they had learned, and are unable to make full use of the system.

Although users are required to know more than ever before about how
their systems work and may find this learning to be a time-consuming
process, it is important to stress to them how the time invested
initially in learning the database layout, the application system, and
SQL will pay off in the future. They will have better access to their
own data, will usually not have to go through a "middle man"
(administrative computing) to get data they need, and will be able to
respond to their users in a more efficient fashion.

It is important to remember that SQL is still a programming
language, and sometimes SQL and database concepts will be difficult for
an end user to master. One should be realistic in these cases, and
encourage a user who is having difficulty to master the formatted screen
applications first, then attempt SQL use at a later date, once
comfortable with the screens. In some cases, due to lack of initiative,
understanding, or ability, some users never become comfortable enough
with SQL to do their own ad-hoc queries. Many users will be content with
the "fill-in-the-form" variety of applications interfaces.[4]

Although some claim that end users will never use SQL, our
experience has shown that a few "key" users arise in each department or
administrative area.[5] These individuals tend to become the resident 
SQL experts and are most often also the people in the department who 
have a  good understanding of the data. These people have been able to 
serve as consultants for others in their department experimenting with 
SQL.

For Administrative Systems

Administrative Systems has found that adopting relational
technology increased our responsibilities with respect to user training
and support, as well as staff training in the transition period, and
raised a number of challenges related to access and performance.

     Training and support

Training new users was a significant part of the successful
deployment of all our relational database applications. As the user
community grew, many people who previously did not have access to any
electronic data also needed to be trained. Our experience with the UIS
demonstrated that AS needed to plan for and present training on the
database layout, SQL, and other INGRES tools, as well as some operating
system concepts. Several sessions, spanning days or weeks, are usually
required to give most users a basic, working knowledge of the
application and the database manager. A few days between training
sessions was also found helpful. This time lapse gave users time to
practice what was discussed in class, and to return with questions in
the following session.

We have found that our department must plan for ongoing support of
these users once the initial training needs are met. It is important to
have a staff member available to take "emergency" calls from users who
are having trouble with simple tasks like running the screen
application, as well as to consult with more sophisticated users who are
attempting to write complex queries or report-writer scripts. Unless
help is available, users will sometimes give up on the application.

When the transition to relational database was begun in earnest in
1986, Administrative Systems had a staff of approximately twenty
programmers, only four or five of whom had experience with relational
DBMS design, development, and support. The rest of the staff were
individuals whose training and experience was founded in third-
generation COBOL-based systems. To bring everyone to a point where they
could begin development work in this new technology, training was
provided in various forms. One-week courses offered by the vendor and
covering database design, programming, use of the tools, and performance
techniques were held on-site, and proved to be extremely useful.
Experienced staff members also conducted seminars on work in progress or
on various DBMS programming techniques. An INGRES User Group was started
within the department, where informal meetings open to all were held
providing an opportunity to discuss various topics related to the
implementation of relational database systems.

Most staff members were able to make the transition with little
difficulty, and in three to six months were very productive with the new
technology. For one or two it proved to be a longer than average
process. The staff who were last to make the transition were those who
were needed to support the older systems while the new development was
in progress. We tried to keep these individuals "current" by assigning
small, relational DBMS projects to work on while they were maintaining
the older systems.

     Access and performance issues

As mentioned, there are definite benefits from distributing ad-hoc
access to data with SQL, but this method also has its drawbacks. In
addition to users having problems formulating their queries, there is
the danger that they may write and execute "bad" queries. Most often,
these are "disjoint" queries that do not properly join tables. The usual
consequence of this action is that the database manager executes a
Cartesian product and the user's query runs out of disk space and fails.
On a few occasions, however, the databases have become inconsistent and
consequently inaccessible to all users until a "restore" command can be
executed by the database administrator. This problem has been eliminated
in later releases of the DBMS software.

In addition to the problem with disjoint queries, another
unanticipated access problem has arisen. Users on occasion are found to
be running large, complex queries in interactive mode that are competing
with regular production for machine cycles on the central time-sharing
systems. No good solution as to how to handle this problem has yet been
determined. Not all users can afford to own their own workstation, where
they could make use of their own machine resources. Access to the
database can be limited to certain hours of the day, or users can be
forced to execute all queries in batch mode at a lower priority. This
second option would prohibit users from running simple queries during
the day. Our current practice is to try to educate the users who are
writing the more sophisticated queries about how their work affects the
rest of the users on the machines, and when and how they should do their
work. We are working on devising a more permanent solution.

Many sites have chosen to avoid the problem of users causing
inconsistencies in the production databases by providing them with an
additional inquiry database. Having a duplicate database for inquiry
purposes also provides an additional level of security (no changes can
be made to the "live" database) and helps improve performance for the
users of the online inquiry screens, who are not competing against
massive update, delete, or add transactions. This solution requires that
additional disk space be available, that another database be maintained,
and that the additional database be updated on a regular basis. Although
this method introduces the possibility of the two databases being "out
of sync," depending on the volatility of the data in the source system
and the resources available, this may be a good method to use when
production databases are involved.

Getting good performance from a database application is an
important part of database management and should be given a high
priority. First and foremost, good database design is critical to good
performance. A large percentage of the development effort, probably
somewhere around 35 to 45 percent, should be spent in the design phase.
We have found that when enough time was spent on developing a good
design, the system performance issues were easier to resolve.

Once the design is in place and the applications written, if a
database is not properly "tuned," the result will be a slow system that
will cause many complaints. Database programmers must be properly
trained in how to take advantage of the database management system and
to use it in conjunction with the operating system to its fullest
potential. Sufficient time should be allotted during the system testing
phase of any project to tune the database and application for maximum
performance. The "tuning" process includes properly establishing file
structures and secondary indices, distributing the data across disks,
and gathering statistics used by the internal query optimizer in query
execution. Fine tuning should be an ongoing maintenance process. Careful
management of all aspects of the database, including indexing,
optimization, locking, database integrity, and user permits, is crucial
to the efficient operation of the application.


SUMMARY AND FUTURE DIRECTIONS

Since the implementation of the first relational database application
for administrative use at Carnegie Mellon, a trend has existed toward
broader distribution of data and improved access to information. The
standardization of all administrative computing on the relational model
has brought with it many advantages, both to the user and to the data
processing staff. Though some special training and expertise is required
to properly maintain, administer, and use these systems, the benefits
they bring are of critical importance to the operation of Carnegie
Mellon University.

Future directions for relational database technology in
administrative computing include even more work being done in the area
of the distributed database environment, to provide our users with
access to many different databases appearing to be a single system. We
plan to take advantage of the knowledge management facilities included
in the current release of INGRES, which will provide us with better ways
to ensure the integrity of our data while reducing the amount of special
coding required to enforce the business rules for data management. We
also see the gateway products, which provide links to other databases
(like DEC's Rdb or files such as RMS files) to be useful to link
existing non-relational systems with INGRES databases.

Finally, we hope to soon be taking greater advantage of the natural
language interfaces to databases, which will allow our end users to have
full access to their data by simply making their requests in English. We
hope that this approach will provide users with greater flexibility in
their work, and eliminate the requirement of learning SQL to make the
greatest use of the data in their systems.

========================================================================

Footnotes

1  Carnegie Mellon University is an undergraduate and graduate
institution located in Pittsburgh, Pennsylvania. Founded in 1905, the
former Carnegie Institute of Technology now enrolls 6,900 students and
employs approximately 700 faculty and 2,600 staff.

2  INGRES is a product of the Ingres Corporation, Alameda, California.

3  C. J. Date, Database: A Primer (Reading, Mass.: Addison-Wesley,
1983).

4  L. Rowe, "Fill-in-the-form programming," in Proceedings of the 1985
Very Large Data Base Conference, Stockholm, Sweden, August 1985.

5  M. Stonebraker, "Future Trends in Database Systems," IEEE
Transactions on Knowledge and Data Engineering 1:1 (March 1989).

========================================================================