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
- 12 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 12
Visual introductionPicture this
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.
In a nutshell
Design and query a simple database
Explore the idea
Part 2 of 12
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 12
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.
Reset break
Part 4 of 12
Pause
That's sitting 1 of 3 done
Stretch, get a drink, look out of the window. There is no timer and nothing is counting down — your place is saved, so you can come back in five minutes or tomorrow.
Quick check
Part 5 of 12
Quick check
Part 6 of 12
Quick check
Part 7 of 12
Reset break
Part 8 of 12
Pause
That's sitting 2 of 3 done
Stretch, get a drink, look out of the window. There is no timer and nothing is counting down — your place is saved, so you can come back in five minutes or tomorrow.
Challenge round
Part 9 of 12
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
Challenge round
Part 10 of 12
Game · Recall cards
Why use a list rather than 30 separate variables for 30 test scores?
Card 1 of 4
Mastery quiz
Part 11 of 12
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 field storing population should be…
3. A lookup list is a form of…
4. A primary key must be…
Lesson round-up
Part 12 of 12
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 LunaPart 1 of 12 · 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