Databases and Spreadsheets

What you'll learn

Databases and spreadsheets are the two most important tools for organising and processing data. A database stores large amounts of structured data so it can be searched, sorted and reported on; a spreadsheet performs calculations, models "what if" situations and produces charts. This guide covers database structure (tables, records, fields, data types and keys), flat-file and relational databases, queries with AND and OR, sorting, forms and reports, and then spreadsheets: cells, formulas, functions such as SUM, AVERAGE, MAX, COUNTIF and IF, relative and absolute references, modelling and charts. It ends with choosing between the two.

Exam questions include practical tasks — writing query criteria, predicting the result of a formula, choosing a data type or chart — as well as explanations of why a design is good or bad. Precision matters: one wrong operator or reference loses the mark.

Key terms and definitions

Database — An organised collection of data that can be searched and sorted.

Table — A set of related records about one type of thing, such as customers.

Record — All the data about one item or person; a row in a table.

Field — One category of data, such as surname; a column in a table.

Data type — The kind of data a field holds: text, integer, real (decimal), currency, date/time or Boolean.

Primary key — A field that holds a unique value for each record.

Foreign key — A field in one table that links to the primary key of another table.

Flat-file database — A database with all its data in a single table.

Relational database — A database with several linked tables.

Query — A search that finds records meeting set criteria.

Data redundancy — The same data stored many times.

Cell — The box where a row and a column meet in a spreadsheet.

Formula — A calculation in a cell, beginning with an equals sign.

Function — A built-in formula, such as SUM or AVERAGE.

Relative reference — A cell reference that changes when the formula is copied.

Absolute reference — A cell reference, marked with dollar signs such as $R$1, that stays fixed when copied.

Core concepts

Database structure

A database table is like a grid: each row is a record (one customer, one book, one pupil) and each column is a field (surname, date of birth, phone number). Every field has a name and a data type:

• Text — letters, symbols, and numbers that are not calculated, such as names, postcodes and phone numbers. Phone numbers are stored as text so a leading zero is kept.
• Integer — whole numbers, such as stock quantity.
• Real/decimal — numbers with decimal places, such as weight.
• Currency — money values.
• Date/time — dates and times, which can then be sorted correctly and used to calculate ages or durations.
• Boolean — one of two values, such as Yes/No or True/False.

Choosing the right data type matters. Stored as text, dates sort as characters rather than by date, and calculations do not work.

Primary keys

A primary key uniquely identifies each record. Names are unsuitable because two people can share them; instead, systems use a customer ID, student number or product code.

Flat-file versus relational databases

A flat-file database keeps everything in one table. It is simple, but it often repeats data. If a shop stores each order with the customer's full address, a customer who orders 30 times has their address stored 30 times. This data redundancy wastes space and causes inconsistency: when the customer moves, every record must be updated, and any that are missed will hold the old address.

A relational database splits data into linked tables. A Customers table holds each customer's details once, with CustomerID as its primary key. An Orders table includes CustomerID as a foreign key, linking each order to the right customer. A change of address is made in one place and applies everywhere. Relational databases reduce redundancy, improve consistency and make data easier to maintain.

Queries and sorting

A query finds records that meet criteria. Criteria use comparison operators: = (equals), <> (not equal to), > (greater than), < (less than), >= and <=.

• AND requires both conditions to be true: City = "Paris" AND Orders > 100 finds customers in Paris with more than 100 orders.
• OR requires at least one to be true: City = "Paris" OR City = "Lyon" finds customers in either city.

Dates work the same way: "born after 1 January 2010" is DateOfBirth > 01/01/2010.

Sorting arranges records in ascending (A–Z, smallest first) or descending order on one or more fields. Sorted reports are much easier to read.

Forms and reports

A form is a user-friendly screen for entering and viewing data one record at a time. Forms can include labels, drop-down lists and validation, reducing errors and protecting the underlying tables. A report presents selected data in a formatted, printable layout, often grouped and sorted, with totals.

Spreadsheets: cells and formulas

A spreadsheet is a grid of cells, each identified by its column letter and row number. A cell can contain text, a number or a formula. Formulas begin with an equals sign and use operators: + − * / and ^ for powers.

The power of a spreadsheet is automatic recalculation: change an input value and every formula that depends on it updates immediately.

Common functions

COUNTIF counts; it does not add. To total values that meet a condition you would need SUMIF. IF checks a condition: if cell R2 holds 48, =IF(R2>=50,"Pass","Fail") displays "Fail".

Relative and absolute references

When a formula is copied, relative references adjust to their new position. =P2*Q2 in row 2, copied down one row, becomes =P3*Q3 — usually exactly what you want.

Sometimes a reference must not change. If a VAT rate is stored once in cell R1, the formula =P2*$R$1 copied down becomes =P3*$R$1: the absolute reference $R$1 stays fixed. Without the dollar signs it would become R2, R3 and so on — empty cells — giving wrong answers.

Modelling

A spreadsheet model represents a real situation with formulas, such as a business's costs and profits. Changing inputs to see the effect — "what if we raise prices by 5%?" — is called what-if analysis. Models are useful for planning, but their predictions are only as good as their assumptions and input data; an unrealistic assumption produces misleading results.

Charts

Choose the chart to match the purpose:

• Bar or column chart — comparing amounts between categories.
• Line graph — showing trends over time, such as monthly sales.
• Pie chart — showing parts of a whole, such as shares of a budget, for a small number of categories.
• Scatter graph — showing whether two variables are related.

Good charts have a title, labelled axes with units and a legend where needed.

Database or spreadsheet?

Use a spreadsheet for calculations, modelling and charts on modest amounts of data. Use a database for large volumes of structured data, many users at once, linked tables, searching and reporting, and controlling access. A company with 50,000 customer records used by many staff needs a database.

Validation and data quality

Both tools can stop bad data at the point of entry. In a database, each field can have validation rules: a range check on a quantity, a presence check on a surname, a format check on a postcode, or a lookup check that limits a department field to a list of valid departments, often displayed as a drop-down list. Spreadsheets offer similar data validation on cells, such as accepting only whole numbers between 1 and 100.

Validation matters because every later step depends on the data. A query cannot find "Paris" if some records say "paris " with a trailing space, and a total is wrong if one cell holds text instead of a number. Consistent, validated data makes searching, sorting, calculating and reporting reliable — the principle often summarised as "garbage in, garbage out".

Real-world uses

Databases run school pupil records, hospital patient systems, library catalogues, online shops' product and order systems, and police records. Spreadsheets are used for budgets, cash-flow forecasts, timetables, mark books, sales analysis and scientific results. Many organisations use both: data is stored and managed in a database, then exported (for example as a CSV file) to a spreadsheet for analysis and charts.

Worked examples

Example 1: Query criteria

Write criteria to find Year 11 students born after 1 January 2010.

Answer: DateOfBirth > 01/01/2010 AND Year = 11. AND is needed because both conditions must apply to each record.

Example 2: Predicting formula results

Cells Q2 to Q5 contain 10, 20, 30 and 40. What do =SUM(Q2:Q5), =AVERAGE(Q2:Q5) and =MAX(Q2:Q5) return?

Answer: SUM = 100; AVERAGE = 100 ÷ 4 = 25; MAX = 40.

Example 3: Absolute reference

Explain why the formula in cell S2, =P2$R$1, uses $R$1.* (2 marks)

Answer: R1 holds a single value used by every row, such as a VAT rate (1). The dollar signs make the reference absolute, so it stays pointing to R1 when the formula is copied down the column (1).

Example 4: Improving a database design

A shop's single table stores each order with the customer's name and address. Explain the problem and a better design.

Answer: The customer's details are repeated on every order (data redundancy), which wastes space and causes inconsistencies when details change. A better design uses a Customers table with a CustomerID primary key and an Orders table containing CustomerID as a foreign key, so each customer's details are stored once.

Common mistakes and how to avoid them

• Mixing up records and fields. Records are rows (one item); fields are columns (one attribute).
• Storing phone numbers as numbers. Leading zeros are lost; use text.
• Using OR when AND is needed. Read whether both conditions must apply.
• Forgetting the equals sign at the start of a formula.
• Confusing COUNTIF with SUMIF. One counts cells, the other adds values.
• Forgetting dollar signs when a formula must keep referring to one fixed cell.
• Choosing a pie chart for trends over time. Use a line graph.

Exam technique for "Databases and Spreadsheets"

• Write criteria and formulas exactly, with correct operators, quotation marks for text and brackets.
• When predicting results, show the calculation.
• When justifying a data type, say why: "integer, because stock is a whole number used in calculations".
• When comparing flat-file and relational designs, use the words redundancy and inconsistency.

Quick revision summary

• Table → records (rows) → fields (columns); each field has a data type.
• Primary keys are unique; foreign keys link tables.
• Relational databases store data once, reducing redundancy and inconsistency.
• Queries use operators with AND (both) or OR (either); sort ascending or descending.
• Forms help data entry; reports present output.
• Formulas start with =; functions include SUM, AVERAGE, MAX, MIN, COUNT, COUNTIF and IF.
• Relative references change when copied; absolute ($R$1) do not.
• Models enable what-if analysis but depend on assumptions.
• Match the chart to the purpose: bar (compare), line (trend), pie (parts of a whole).