Introduction to Databases

Databases

Computers are very good at storing and handling data. If that data mainly consists of numbers, and you want to carry out a lot of mathematical work on them then a spreadsheet is probably the most appropriate piece of application software.
However, computers are also good at storing large amounts of text, images and sounds ...
... and if you store a lot of data you need a system that will help you find things quickly - this is what a database does.

an unordered collection

A database is able to find data items quickly because it sorts them into order. It also collects together data items that are similar to each other.

As a simple example, look at the pictures of pencils, crayons and pens. You need to find the blue crayon in a hurry.
In the unordered collection you might be lucky and spot it first time - but you may just as easily take a long time.
If, on the other hand, you had ordered your coloured pencils before then you could go straight to the crayon collection and pick from the blue end.

Simple Databases

At first sight, this view of a database looks a bit like a spreadsheet: there are lots of rectangles arranged into columns and rows.

Each column is called a FIELD and they hold information about the items held in the database.
There is one particularly important field in each database and that is called a KEY FIELD. The key field is like an index for each item in the database. Usually the values in the key field are numbers ... but they must all be different to each other.

Each row in the database is called a RECORD. Each record is made up of a number of different fields - one of which must be the key field.
So, looking at the screen-shot of my clothing database, each item of clothing is on a different row. In other words, each item of clothing has a seperate database record. The fields the describe the clothes include a code field (this is my key field); the title of each item; how much it costs; and who makes it.
Now that I have data in my database I can get the software to analyse it.

Now, answer these questions:

What software would you use for mathematical work ?
What are the columns in a database called ?
What special field must each database have ?
What are the rows in a database called ?
In the screen-shot, which field contains the value £6.00 ?

You scored out of 5 on that test

Sorting the Database

One useful feature of database software is that it can be used to sort the data in the database into order. If you have an address book containing over a hundred people in it you might want to order the address-database into people's surnames in alphabetical order (just like the phone book).
On the other hand, you might want to find out who's going to be inviting you to their birthday party. So you could re-order your address book so that the surnames get mixed up but the values in the date-of-birth field are now in order.

There are two ways to order dates (and other fields): ascending and descending. If you order the dates in ascending order then the first record in the database will have the date nearest to January 1^st (and descending order will start with the birthday nearest December 31^st).

The database table shown here has the records sorted so that the manufacturer names are in ascending order.

Searching the Database

This is a very useful feature of the database. If the data has been sorted into a particular order then the software can find items quickly. If the data isn't sorted then the database software has to look at each record in the database in turn to see if it's the one that you were searching for.

A database search is sometimes called a filter or a query. For example, you may want to search the clothing database to find which items were made by a particular manufacturer.
So, if you typed in the filter: manufacturer = 'Cotton Crew'
you would get a list of all the items produced by them (Shorts and T-shirts).

You could type in a more complex filter: manufacturer = 'Cotton Crew' and Cost < £10
This would give you a list of all the items made by a manufacturer that cost less than £10 (T-shirts in this case).

Now, try these questions on the clothing database :

If you sorted the data by cost in ascending order, what is the first cost ?
If you sorted the data by code in descending order, what is the first cost ?
What item would you get if you used the filter: manufacturer = 'Fancy Fashions' ?
How many items would there be if you used the filter: cost < £1.00?
Which field must not have any values in it that are the same ?

You scored out of 5 on that test

Reading List

Teach Yourself Microsoft Access 2000 in 10 Minutes

I like this little book: it makes a complicated piece of database software seem straightforward - it's Teach Yourself Microsoft Access 2000 in 10 Minutes by F.Wempen; Sams Publishing; 1999; ISBN 0-672-31487-8