Databases

Record structure

Before setting up a database, the record structure must be decided, to make best use of the memory and backing store and to make searching and reporting easier.

For example, a car showroom records details of cars it sells. The answers to these questions would need to be decided first:

· What information is needed?

· What validation could there be?

The record structure might start like this:

Field name	Field type	Format
Registration number	Alphanumeric	Up to 7 characters - the key field
Make	Alphanumeric	Up to 15 characters
Model	Alphanumeric	Up to 15 characters
Date first registered	Date	DDMMYY
Price	Numeric	Up to 5 numbers
Taxed	Yes/No (Boolean)	1 character Y/N
...

A Database must contain a key field.

The key field is unique - different for all records. The following are examples of key fields.

Fields:

· Car registration number

· National Insurance Number

· Your school's examination centre number

· Your own examination candidate number

A database consists of one or more files.

Data capture

Before setting up a database, the data must be collected. This may be done using a data capture form.

A data capture form is a form especially designed for collecting data.

Data capture forms normally use boxes or spaces for the answers to help you.

Why we use databases

· Databases on disk take up less space than on paper · It is very easy and quick to find information · It is easy to add new data and to edit or delete old data · Data can be searched easily, for example to find all the Fords

· Data can be sorted easily, for example into 'date first registered' order · Data can be taken into other applications, for example a mail-merge letter to a customer saying that an MOT test is due · More than one computer can access the same database at the same time - multi-access · Security may be better than in paper files

Why code data?

Data is often put into a code in a database, for example in the file above Y is used for yes and N for no. Codes like this are used because:

· It is quicker to type in

· It uses less disk space

· It is easy to validate

Using an AND/OR query

It is possible to narrow down a search, for example to look for those records where the car is a Ford and the price is less than £8000. This is an AND search. Both conditions have to be true.

Some programs use a query language for searches. In query language the above search would be something like:

[Make] = "Ford" AND [ Price ] < 8000

You can widen the search with an OR search, for example to look for those records where the car is either a Ford or a Rover.

In query language this would be something like: [Make] = "Ford" OR [Make] = "Rover"

Who uses databases?

· The police have details of all criminals in a database.

· Your school will probably use a database to store details of its pupils.

· A hospital will store details of all its patients.

Use this space to create a mind map: