Database Servers

Structured Query Language (SQL) is IBM's English-like database query language. It was developed to provide a relatively simple method for entering, retrieving, and changing data in an IBM database. Because IBM used it, SQL quickly became a standard other database vendors wanted to support. ANSI has standardized SQL as well. Using a common database language makes it easier to develop network database applications.

A database server is software that manages data in a database. It updates, deletes, adds, changes, and protects data, which is usually stored on a powerful computer with a large disk to which many users have access.

The database server software also protects data. Since network data can be accessed by many people, it must be protected from several potential problems. First, when two or more people work on the same database record at a time, they could overwrite each other's changes and cause data to be corrupted. Database server software must establish rules to regulate access to multiple simultaneous users. These rules are called concurrency control. Second, not every company employee needs access to every piece of data in the shared database. Therefore, a database server must also provide rules to regulate access, including passwords, access rights, and data encryption. These rules are called access control.

The Front End

The database server does not contain the software needed for user interfaces, writing reports , generating applications, and other software associated with databases. The database server or back-end only manipulates data. Presenting, entering, and updating data is handled by the front-end. The front-end application usually runs on a different machine from the database server software.

Front-end software takes many forms because data can be manipulated and used differently. For example, Acme Widget Company has a database that contains customer information and inventory data. Different front-ends allow sales people to do promotional mailings to Acme's customer list. Outside distributors and salespeople on the road can dial into the database to learn which products are in stock. The service department can call up a customer's file when he calls and access all past ordering information. The accounting department can prevent customers from ordering more products if they are delinquent on their payments. Each group has a front-end tailored to their jobs, but they access the same database.

Different front-ends use a common access method-SQL-to communicate with the back-end. While the front and back ends communicate relatively easily, SQL is not a good language for nontechnical users. While programmers are not fazed by its syntax, salespeople, accountants , and technical support people do not want to type SQL commands.

In most applications, SQL will run "behind the scenes." Query-By-Example (QBE) is a simple front-end application. A QBE program asks users to provide an example of the type of data they want to get. A spreadsheet talks SQL to the back-end server, but the user continues to work with a familiar interface. If the user wishes, most front-end applications also permit users to execute SQL commands.

Server Advantages

A database server has several advantages over the traditional file server model of databases. In the file server model, there is only one database program. Although this program is stored on the file server, it executes in the workstation. The one piece of software performs both front- and back-end functions. When a user loads the database, the program file is downloaded to his or her workstation. When data is requested, the software gets the database file from the server, searches through the file, finds the requested records, and then downloads the information to the workstation and displays it on the screen. The whole database travels across the network from the file server to the workstation.

In a database server, the tasks are divided. The front-end requests the data, the request is translated into SQL, and it is sent over the network to the server. The database server executes the search on the machine where the data exists. Only the requested records are returned to the front-end using SQL and a network transport mechanism. This cuts down on network traffic and makes execution faster, especially when many people are using the same data.

Data is not the only traffic that is eliminated. In the file server model, concurrency control information must be handled by the workstation. With a database server, these commands do not have to be transmitted over the network, since they are handled locally. A database server provides better data integrity.

As mentioned, another advantage is that different front-ends can access the same back-end. This is not possible with the file server model.

Because a database server is intelligent , it can shorten the application development cycle and allow applications to execute faster. One method is stored procedures. Stored procedures are short programs that reside on the server, waiting to be executed. Any front-end can call the stored procedures. This saves programmers from including redundant code segments in every application. It also saves memory in the workstation. Triggers are similar to stored procedures; however, stored procedures are compiled code, and triggers are uncompiled code.

Despite the usage of SQL, no standard method exists by which all front-ends can talk to back-ends from different vendors. Each database server has its own access methods that the front-ends must use because of different types of SQL used as well as differences in transport protocols. The result is that applications written to work on one database server will not work on another. Although this is inconvenient for developers, it is a less serious problem for end users. When users choose a database management system, they choose a set of unique programming environments.

Database servers are an enormous improvement over other database management systems. They are faster, more powerful, more secure, more reliable, and easier to use. Their ability to accommodate a myriad of front-ends will make data sharing much easier. Their client-server design make them the next step in network databases.

This tutorial, number 16, was originally published in the November 1989 issue of LAN Magazine/Network Magazine.