Topic 9 · Databases · 9.1

Database Structure Explained Simply

A database organises related data so it can be stored, searched and updated efficiently.

Start the lessonExplore the Learning Hub
Invitation

How does a database keep thousands of pieces of data organised?

A database stores related data in an organised structure. Instead of keeping everything in one long list, the data is arranged into tables.

This makes it easier to search, sort and update information later.

A database is organised storage for related data.
Figure 1
The structure at a glance
DATABASE
↓
TABLE
↓
FIELDS + RECORDS
Big Idea

A table is made from fields and records

Look at Figure 2. The table is called Students. The headings across the top are the fields. Each completed row underneath is one record.

StudentIDNameAgeHouse
101Alice15Blue
102Ben16Green
103Chen15Blue
Fields run down columns. Records run across rows.
Teaching Chip

See the whole database structure in one picture

Database: the complete organised collection.

Table: one set of related data.

Field: one category of data, such as Name or Age.

Record: all the data about one item or person.

Table = collection. Field = column. Record = row.
Figure 2
The database table chip
Students table
IDNameAge
101Alice15
102Ben16
103Chen15
Column headings are fields. Each row is a record.
Core Terms

Four words unlock database questions

Database

An organised collection of related data.

Table

Data arranged in rows and columns.

Field

One category of data in a column.

Record

All the fields about one item in a row.

Primary Key

Every record needs a unique identity

A primary key is a field containing a unique value for every record.

In the Students table, StudentID is suitable because no two students share the same ID.

🔑
Primary key rule:
Unique for every record. Never repeated.
Figure 3
Why Name is not always safe
StudentID 101 → Alice
StudentID 102 → Ben
StudentID 103 → Chen
Names can repeat. Unique IDs should not.
Data Types

Each field stores one suitable type of data

FieldExampleSuitable data type
StudentID101Integer
NameAliceText / Alphanumeric
Age15Integer
PresentTRUEBoolean
Exam habit: database questions normally use Text or Alphanumeric for words, rather than the programming term String.
SQL Journey

This structure prepares the database for questions

Once the table is organised, SQL can ask a question and return an output. We will use the same Students table throughout Topic 9.

DATABASE
Stored table
→
SQL QUERY
Ask a question
→
OUTPUT
See the answer
Database → SQL query → Output.
Exam Tip

Count rows for records and columns for fields

Students often reverse these two terms in exam questions.

✗ Incorrect

4 records and 3 fields

This reverses the rows and columns.

✓ Correct

3 records and 4 fields

Three data rows and four column headings.

Focused answer: Fields are the columns. Records are the rows.
Common Mistake

Do not choose a primary key that can repeat

A field such as Name, Age or House may appear more than once. It is therefore not reliably unique.

Better answer: “StudentID is suitable as the primary key because every value is unique and identifies one record.”
Summary

Five ideas organise a database

1A database stores related data.
2A table uses rows and columns.
3Fields are columns.
4Records are rows.
5A primary key uniquely identifies each record.
Remember: Table = collection. Field = column. Record = row. Primary key = unique identifier.