Databases and spreadsheets

A database is used to store information so that it can be searched and sorted in various ways. Each item of information is sorted in various ways. Each item of information is stored in a field. A collection of related fields forms a record. Simple searches can often be created by allowing the user to type the required field information into a blank record, and pressing a search button. More advanced database programs usually use a query language known as SQL (Structural Query Language). This allows users to type statements using logical operators to specify the search conditions.

If you only known part of the field you are searching for, you can use special symbols, called wildcards, to represent combinations of characters. Although the actual wildcard symbols used can vary, most programs use a standardized set of wildcard symbols.

A spreadsheet program is used for calculating formulae. It is made up of a grid or array of rectangular boxer called cells, as shown below.

The columns are labelled A, B, C … and the rows of cells are numbered 1, 2, 3 … To refer to a particular cell, you use the column label followed by the row number. For example, the cell in the top left corner of the spreadsheet is A1, the one to the right of it is B1, and the one below it is A2. The cells can contain text, numbers, or formulae. The formulae are written using the cell references, e.g. to add the first three cells in the A column, you would use the formula A1+A2+A3, or the sum function Sum (A1:A3), i.e. the sum of cells A1 to A3. When a formula is assigned to a cell, the result of the formula is displayed in the cell rather than the formula itself. By varying spreadsheet values and formulae, different possible outcomes can be analysed.

Other common mathematical symbols used in formulae are shown in the table below.

17Databases and spreadsheets

Tuning-in

Task 1 Study this example of a record from a database of company employees.

What fields do you think it contains? What other fields might be useful?

Task 2 Work in pairs. What fields would you include in a database for:

1 a national police computer?

2 a national driver and vehicle licensing centre?

Reading: Database search

Task 3 Study this simple database of volcanoes and answer the questions.

1 How many fields are there? 3 List the volcanoes in North America.

2 How many records are there? 4 List the volcanoes over 6,000 metres.

Task 4 Read this text on database searches and answer the questions which follow.

1 What does box a contain?

2 What does box b contain?

3 Which selection rule is entered?

4 What is the function of button c?

5 How many records will this search find?

Task 5 What are the selection rules to find:

1 all active volcanoes?

2 all volcanoes over 6,000 metres?

3 all volcanoes in South America?

4 all active volcanoes in Ecuador?

5 all active volcanoes in South America higher than 5,500 metres?

Listening: Spreadsheet

Task 6 Study this extract from a spreadsheet for sales from a fast food outlet. Answer these questions.

1 How many columns are there?

2 How many rows are there?

3 What is in cell A3?

Task 7 Study this table. Explain what each of the spreadsheet formulae (1-5) means.

Example = A2*B2 (formula) equals cell A2 multiplied by/times cell B2 (explanation)

Task 8 Listen to the recording. Fill in the gaps in the spreadsheet in Task 6 by entering the numbers, text, and formulae in the correct cells.

Task 9 In most databases you can use wildcard characters when you do not know exactly what you are searching for. Study these examples.

? any single character in this position

* any number of characters in this position

# a single number in this position

[ ] find these characters

[!] don't find these characters

Using these characters in a search, we can be certain what we will find and what we will not find.

Example If you search for Sm?th, you will find Smith and Smyth, but you won't find Smit.

Write similar sentences for these searches.

1 Br?wn - Brown, Brawn, Braun.

2 t*e - tongue, the, tea, true

3 #th -12th, 4th, earth

4 Paul[ao] - Paul, Paula, Paulo

5 Mari[!a] - Marie, Maria, Mary

Task 10 Complete these If- sentences using an appropriate expression of certainty.

Example If there is power failure, you may lose all your data.

1 If there is power failure, you ___________ lose all your data.

2 If you have a virus, it ___________ corrupt your files.

3 If you don't back up your files regularly, you ________ lose some of them.

4 If you choose a simple password, someone _________ access your files.

5 If you don't give your files meaningful names, you __________ forget what

they contain.

Problem-solving

Task 11 Some databases use symbols rather than words for selection rules. Here are some of the symbols and their meanings.

Study this extract from a database of members of a sports club, and the results of five searches. Write selection rules to obtain these results. Use the symbols above.

Example Result - Helen Trim Selection rule - Occupation = technician .AND. Sex = F

Search results

1 Lillias Brown, Alan Brew, John Walls

2 John Pond

3 Lillias Brown, Helen Trim

4 John Walls

5 Arnold Bright

Writing

Task 12 Go back to Task 1. Explain which fields you would include in a database for a national driver and vehicle licensing centre. Give reasons for each field.