National Curriculum links

Computing: undertake creative projects that involve selecting, using, and combining multiple applications to achieve challenging goals, including collecting and analysing data and meeting the needs of known users. Computing: understand how data of various types can be represented and manipulated digitally. Computing: create, reuse, revise and repurpose digital artefacts for a given audience, with attention to trustworthiness, design and usability.

Key Stage 3 Computing programme of study, National Curriculum in England (Department for Education). The concepts of records, fields, primary keys and queries bridge directly to the database content in GCSE Computer Science.

Learning objectives

By the end of the sequence, pupils will be able to:

  • explain that a database stores data in records, each made up of fields;
  • identify the fields in a postcode record and choose an appropriate data type for each;
  • explain what a primary key is and why a postcode can act as a unique identifier;
  • express simple queries to search, filter and sort data;
  • explain why data validation matters for accuracy.

Lesson outline

Starter

Ask pupils where they think the answer comes from when a website tells you the county, council and coordinates for a postcode. Establish that behind the site is a database holding a record for every one of the roughly 1.8 million UK postcodes.

Main activity

Examine a single record. Enter a postcode into the what county is my postcode tool and the postcode to latitude and longitude tool, and list the fields a record contains: the postcode itself, latitude, longitude, district, ward, constituency, region and country. Pupils draw the record structure as a table of fields and assign a sensible data type to each (text, decimal number, and so on).

Introduce the primary key: the postcode uniquely identifies each record, so no two records share one. Then move to queries. In plain English, pupils write instructions the database could carry out, such as "find every postcode in district M14", "sort these postcodes by elevation", or "filter to postcodes in Scotland". Relate each to a real feature of the site, for example the district lookup and bulk lookup tools. Finally, discuss validation: a postcode must follow a set format, so the database can reject obviously invalid entries.

Plenary

Pupils design the field list for a new database of their own choosing, identify a suitable primary key, and write two queries it should be able to answer.

Differentiation

Support and challenge

  • Support: identify the fields in a given record and write queries in plain English.
  • Challenge: express the queries as structured statements, for example SELECT postcode WHERE district = "M14", and discuss why splitting data across linked tables (such as a separate table of districts) avoids repetition, introducing the idea of a relational database.

Key vocabulary

database, record, field, data type, primary key, unique identifier, query, search, filter, sort, validation, table.

Assessment: key questions

  • Can pupils explain the difference between a record and a field?
  • Can pupils explain why a postcode is a good primary key?
  • Can pupils write a query to search, filter or sort the data?

Tools and links for this lesson