Databases

After studying this section you should be able to:

give the advantages of a database approach
explain how databases are indexed
describe primary and secondary keys
describe a query method

Database management systems

A database is a collection of related data. In the early days of information processing each application had its own master file. This led to a number of problems:

Data duplication leading to wasted space and wasted time entering the data.
Data inconsistency – updating an employee’s address on the personnel file but not on the payroll file.
It was not possible to share data among systems easily.

The database management system (DBMS) is a piece of software that is placed between the application and the file system. This allows the file system to be regarded as a related set of data. The main features of a database system are:

The database stores data as a number of linked records.
The database may have multiple indexes that allow data to be obtained using different record keys.
The format of the data can be specified and the database system will check for consistent data.
In a database system, data duplication will be minimised, so space is saved and data inconsistency is reduced.

Relational databases

The relational database stores its data in tables. An example might be a table of cars sold in a secondhand car showroom.

A row in a table is similar to a record in a file and a column is called an attribute. Each table will have a primary key that must be unique. This will be one or more of the attributes and is used to identify the row. In the graph above this would be the registration number. One table is often related to another table and to enable this one or more of the attributes may be designated foreign keys.

Notice that we have introduced a customer number to give a primary key to this table.

In this case the car registration number will be a foreign key.

Queries

Another feature of databases is the ability to ask questions of the database (called a query). In the case of a relational database the result of a query will be a table of values. An example of a query might be ‘Select all the FORDs we have for sale’.

There has to be a standard language for these queries and one method is query by example. In query by example you set conditions for the information you require by composing relational expressions. A relational expression is made up from column names, constant values and relational operators. An example might be:

Make = ‘FORD’

This will select all the rows where the column Make contains ‘FORD’. Other relational operators are < (less than), > (greater than), <= (less than or equal to) and >= (greater than or equal to). Relational expressions can also be combined using AND, OR and NOT. It is possible to produce sophisticated queries that obtain data from more than one table, for example using the tables above we could select the customers who have purchased FORD cars as follows:

CUSTOMERS.CAR = CARS.Registration_No AND CARS.Make = ‘FORD’

KEY POINT - A database provides a range of features to handle the data that are held on magnetic disks and tapes. It separates the user from the physical storage of the data.

Log in