Designing a useful dataset
A database is only as good as its fields. Decide what question you want to answer before deciding what to store.
Part of your national curriculum
- Statistics: Construct and interpret appropriate tables, charts and diagrams, including bar charts, pie charts and pictograms for categorical data, and vertical line charts for ungrouped discrete numerical data
- Algorithms and programming: Use a variety of data structures, including lists, tables or arrays, and design modular programs that use procedures or functions
- Data and representation: Understand how instructions are stored and executed within a computer system, and how data of various types can be represented and manipulated digitally
Lesson overview
What you'll learn in this lesson
Design a simple relational data structure
Key learning points
- • Records, fields and types
- • Validation
This lesson at a glance
- 25 minutes
- 10 parts to scroll through
- 3 quick checks
- Marked quiz at the end
- Gentle pace: short sittings with pauses
Words to know
Scroll down — the lesson carries on below
Watch & discover
Part 1 of 10
Watch first
Designing a useful dataset
An English teaching video on this topic from Lucid Software. Watch it, then scroll down for the lesson.
In a nutshell
Design a simple relational data structure
Explore the idea
Part 2 of 10
Learn
Records, fields and types
Each row is a record; each column a field with a type. Storing 'population' as text stops you sorting it numerically.
Explore the idea
Part 3 of 10
Learn
Validation
Range checks, presence checks and lookup lists prevent nonsense entries such as a conservation status of 'blue'.
Explore the idea
Part 4 of 10
Worked example
Worked example
A species table: id (unique), common_name (text), status (list: LC/NT/VU/EN/CR), population (number), region (lookup to a regions table).
Quick check
Part 5 of 10
Quick check
A field storing population should be…
Quick check
Part 6 of 10
Quick check
A lookup list is a form of…
Quick check
Part 7 of 10
Quick check
A primary key must be…
Challenge round
Part 8 of 10
Game · Sort it
Which of these are true?
Drag each card into the right column. Tap a card first if dragging is fiddly.
True
Not true
Mastery quiz
Part 9 of 10
Marked quiz
End of lesson quiz: Designing a useful dataset
3 questions, marked with the reasoning shown. No timer.
1. A field storing population should be…
2. A lookup list is a form of…
3. A primary key must be…
Lesson round-up
Part 10 of 10
Lesson round-up
Ready when you are
Quiz score
Not sat
Games
Not played
Points this lesson
0
Best run
0 in a row
Luna: 0 out of 3 on the practice checks. Only if you feel up to it — one more?
Ask LunaDo the work
Your written work for this lesson
This is the part you'd do in your book at school. Pick the pieces below, write here or on paper, and photograph anything you draw or work out.
How a supermarket checkout knows the price
When a barcode is scanned, the checkout does not store the price itself. It sends the barcode number to a database and reads back a record: product name, price, whether it needs an age check. The record is the single place the price lives, so changing one row changes every till in the shop at once.
Problems come from bad data, not bad scanners. If two products share a barcode, or a price update reaches the shop overnight but the till holds an old copy in its cache, customers are charged the wrong amount. Shops check this with test transactions: scan a known item, expect a known price, record what actually happened.
Good systems also log every scan. If a customer complains, the log shows exactly which record was used and when.
Written for Good Learning Co.
Reading questions
- Write in your own words what the checkout sends and what it receives back.
- Explain one way bad data could cause a wrong price, and how logging helps find it.
- Design a test table with four tests for a new checkout, with expected and actual results.
Reading and notes
ReadingRead the lesson text again and note the key terms and how each one is used, in your own words — as you would for revision.
Half a page of notes
Extended writing
WritingExplain a real system that uses this idea — how it works, where it can go wrong, and how you would make it safer or better.
250–350 words
Algorithm on paper
DiagramWrite your steps out as pseudocode or a flowchart before you type anything, then photograph it.
1 flowchart or 10 lines
Code write-up
WritingCopy your code in, then explain underneath what each part does and what you had to fix.
Code + 5 sentences
Test table
Working outMake a test table: what you tried, what you expected, what actually happened.
4–6 tests
Saved as you type.
Part 1 of 10 · Watch & discover
How this is going
- Not looked at yet
Construct and interpret appropriate tables, charts and diagrams, including bar charts, pie charts and pictograms for categorical data, and vertical line charts for ungrouped discrete numerical data
- Not looked at yet
Use a variety of data structures, including lists, tables or arrays, and design modular programs that use procedures or functions
- Not looked at yet
Understand how instructions are stored and executed within a computer system, and how data of various types can be represented and manipulated digitally