Application software:Database software

Database software

A database is a set of data organized into records. A software package which allows you to organize data in this way is called a database management system (DBMS). There are two types of database software:

• Packages which are able to handle just one file of records at a time, sometimes called card box or flat-file packages.

• Relational database packages, which are able to handle several files at a time, and to build links (or relation­ ships) between files.

Relational databases are needed for more complex applica­tions. One such application is invoicing, where customer details (such as the name and address) have to be extracted from the customer file, and product details (such as the price) have to be extracted from the stock file (see below). Relational databases can be (and often are) used for single­ file applications, but, being more complex than flat-file packages, they may make those applications unnecessarily difficult.

To illustrate the difference between flat-file and relational databases, I'll enlarge on the invoicing application men­tioned in the last paragraph. This may have two main files:

• The customer file, which has one record per customer with his or her name and address, and onto which you enter details of all orders received from customers - the products purchased, the price, the date of purchase, and so on.

• The product file, which has one record per product line, holding details such as the price, quantities received from suppliers and dates, quantities issued to customers and dates, the balance in stock and so on.

You can imagine that the job of the invoicing clerk would be made very efficient if, when he or she keys in the customer code number, the code number of a product purchased, and the quantity, the computer automatically displays both the customer details and the product details, and updates both the customer file and the product file with the relevant details of the transaction.

That is exactly what a relational database does - it links files together, so that information from both can be dis­ played or printed, and enabling both to be updated.

Often, more than two files will be linked in this way. In the above sales situation, for instance, there may be three files:

• An order file containing details of the order (the custom­er's code number, the product code numbers and quan­tities, and the date), from which the total order value is calculated and invoices and delivery notes produced.

• A customer file containing the customer's name and address, together with the value of orders and their dates, from which the monthly statement is produced.

• A product file containing the product details such as quantities received and issued, and from which purchase orders are produced to send to suppliers.

The order file picks up the customer details from the customer file, and the product details from the product file. It in turn updates the customer file with the value of the order, and the product file with the number of issues.

This is a simplified description of what actually goes on in a sales accounting situation, but it illustrates the value of relational databases.

Client/server database architecture

In some of the more advanced database systems, the task of data storage is separated from the task of processing the data. The former is carried out by server software, while the latter is carried out by the client package. The server software normally resides in a mainframe of minicomputer, or perhaps on a network server, and it handles basic data storage tasks. The client can be any database package that is able to accept data from the server; it provides the user interface as well as the various input and output facilities. Other kinds of application package, such as spreadsheets, can also act as clients. The server software is also referred to as the back-end, and the application software is referred to as the front-end.

In recent years Structured Query Language (SQL) has become important. This is a database language that allows clients to communicate with servers. The client sends instructions to the server in this language, one example being the instruction to find all records that match certain criteria. Normally, the user can simply select a menu choice offered by the client, and the software generates the necessary instructions. Often, the client package will also allow the user to type the commands directly, should he so wish. The data will be retrieved by the server, passed to the client, and will appear at the right place in the application (which might be a spreadsheet or word processed document) as though it had been typed in.

A number of (client) application packages now support SQL and can therefore make use of server software. They include Paradox and Data Base. SQL is also available for Excel, Microsoft's spreadsheet package. Two well-known and powerful SQL servers are Oracle and Ingres.

Record-keeping concepts

Let's turn now to the main record-keeping concepts. These are data, file, record, field, and template.

Data. The purpose of record keeping is to store, retrieve, and analyse data on a variety of topics. Some data are numbers, examples being ages of people, prices of goods, and quantities. Other data, however, are alphabetic or alphanumeric information, examples being names and postcodes.

File. A record-keeping package enables you to set up files to store data on a variety of topics. For example, you might keep one file containing names and addresses, another containing details of customers' orders, and another containing details of stock held in your department. Not only does the type of data differ from file to file, but the way in which it is organized, or structured, differs also. On personal computer systems, a file (of records) is simply another word for database.

Record. A record is a single unit of structured data in a file. In a name-and-address file, for example, a record is the data relating to one individual, organized in the following way:

the name several lines of address the telephone number.

All the records in a file have an identical structure. That structure serves the same purpose as the printed design on the cards in a card index. The data varies from record to record, but the structure remains the same.

Field. A field is the space in a record occupied by a single item of data, such as a name, or a line of an address, or a price, or a quantity. The set of fields, and the spaces allocated to them, determine the structure of a record.

Key. One of the fields in a record may be a key field,

meaning that the data in this field uniquely identifies the record and distinguishes it from all other records in the file. Often, it is this field that will be used when the computer searches for a specific record. In many cases, a code number will be the key; in the case of an employee record, for example, a works number or personnel number will be the key.

Template. A template is the structure of a file without the data. When you create a file, your first task is to design the template. It's a bit like designing the printing that is to go on the cards in a card index. You decide the fields you need, their position, the space to be allocated to each, and any labels (names) that you are going to give them. In the case of a computer-based system, you can build other things into your design, such as automatic date-stamping when a record is added to a file, automatic calculation of balances and totals of numeric fields, and automatic checks on the validity (i.e. reasonableness) of the data when it is entered.

Figure 6.2 shows a demonstration template for a student record system. (A real-life template would be bigger, with

more subjects.) There are fields, and field labels, to record the name, address, and other personal details of each student, more fields in the boxes in the lower half of the template to enter assignment and exam grades for the various subjects, and a final field at the bottom for the tutor's comments. In this template, the student's average grades are automatically recalculated whenever assignment grades are added to his record; Q&A, the software package used to create this file, allows the template to be pro­ grammed so that this sort of feature is easily built it. The computer displays this template on the screen whenever a record is added to the student record file, allowing you to add data into each field. When you retrieve the record later on, the template is again displayed, together with that record's data.