Designing a database that works
A good table has one row per thing and one fact per column. Get that right and every query afterwards is easy.
Part of your national curriculum
- 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 and query a simple database
Key learning points
- • Fields, records, keys
- • Querying
This lesson at a glance
- 25 minutes
- 9 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 9
Watch first
Designing a database that works
An English teaching video on this topic from Lucid Software. Watch it, then scroll down for the lesson.
In a nutshell
Design and query a simple database
Explore the idea
Part 2 of 9
Learn
Fields, records, keys
A field is a column, a record is a row, and a primary key uniquely identifies each record. Never store two facts in one field.
Explore the idea
Part 3 of 9
Learn
Querying
Filter with conditions, sort by a field, and combine conditions with AND and OR. Ask what question the query must answer before writing it.
Quick check
Part 4 of 9
Quick check
A primary key must be…
Quick check
Part 5 of 9
Quick check
'Species and habitat' in one field is…
Quick check
Part 6 of 9
Quick check
Which returns fewer results?
Challenge round
Part 7 of 9
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 8 of 9
Marked quiz
End of lesson quiz: Designing a database that works
4 questions, marked with the reasoning shown. No timer.
1. Why use a list rather than 30 separate variables for 30 test scores?
2. A primary key must be…
3. 'Species and habitat' in one field is…
4. Which returns fewer results?
Lesson round-up
Part 9 of 9
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 9 · Watch & discover
How this is going
- 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