Year 8 Digital Technologies · Data and Information · 9m read

Structured Data — Databases and Spreadsheets

Ever wonder how Netflix knows exactly which shows to recommend to you, or how your school instantly pulls up your grades? That's the power of databases — organised filing systems that store and connect data so computers can find what they need in milliseconds. You'll learn how these systems work and how to use spreadsheets to sort, filter, and analyse real data yourself, turning raw numbers into useful information that actually answers questions.

Learning objective: Describe relational databases and use spreadsheet data analysis tools to create information.

By the end of this lesson you can:

I explain what a relational database is and describe tables, fields and records.
I explain how relationships connect tables in a relational database.
I use filtering, sorting and formulas to analyse data in a spreadsheet.

A database is a super-organised filing cabinet. Each drawer is a table, each folder a record, each label a field. Finding anything is instant.

Databases and spreadsheet analysis

DATABASE: an organised collection of structured data.
More powerful than spreadsheets for large, complex datasets.

RELATIONAL DATABASE:
Data stored in multiple TABLES that are related to each other.
TABLE: a grid of rows (records) and columns (fields).
FIELD: a category of data (column). e.g. StudentID, Name, DateOfBirth.
RECORD: one complete entry (row). e.g. (1001, "Maya Chen", "2012-04-15").
PRIMARY KEY: unique identifier for each record. No duplicates, no nulls.
e.g. StudentID, EmployeeNumber, ISBN.
FOREIGN KEY: a field in one table that links to the primary key of another.
Enables relationships between tables.

RELATIONSHIPS:
ONE-TO-MANY: one record in Table A links to many records in Table B.
One teacher → many students.
One customer → many orders.
MANY-TO-MANY: many records in A link to many in B. Requires a junction table.
Many students ↔ many subjects.

DATABASE ADVANTAGES over spreadsheets:
Reduced data redundancy (no repeated data).
Data integrity (relationships enforced).
Multiple users simultaneously.
Better security (role-based access).
Efficient querying of millions of records.

SPREADSHEET ANALYSIS TOOLS:
SORTING: arrange data by a column value (A-Z, smallest to largest).
FILTERING: display only rows that match criteria.
FUNCTIONS: =SUM(), =AVERAGE(), =MAX(), =MIN(), =COUNTIF(), =VLOOKUP().
PIVOT TABLES: summarise large datasets by grouping and aggregating.
CHARTS: visualise patterns in data.

Database design rules

Primary key must be unique: Every record needs a primary key that uniquely identifies it. Never use names or emails as primary keys (they can change or repeat). Use an ID number.
Normalisation reduces redundancy: Store each piece of information in ONE place. If a customer's address appears in every order row, update errors cause inconsistency. Move to a separate Customers table.
Foreign key creates the link: A foreign key in one table references the primary key of another, creating a relationship. This is the core of "relational" databases.

Quick reference

| Term | Meaning |
|---|---|
| Table | Collection of records |
| Record (row) | One item |
| Field (column) | One attribute |
| Primary key | Unique identifier |
| Query | Search for data |
| Spreadsheet formula | =SUM(A1:A10) |

Worked example (Medium)

Design a simple relational database for a school library. Include at least two tables with a relationship.

Table 1: Books. BookID (primary key), Title, Author, ISBN, Genre, CopiesAvailable.
Table 2: Members. MemberID (primary key), Name, Year Level, Email.
Table 3: Loans (junction table). LoanID (primary key), BookID (foreign key → Books), MemberID (foreign key → Members), DateBorrowed, DateDue, DateReturned.
Relationship. Books ↔ Members is MANY-TO-MANY (one book can be borrowed by many members over time; one member can borrow many books). Loans table resolves this.

Answer: Books + Members + Loans (junction). BookID and MemberID as foreign keys in Loans create a many-to-many relationship.

Worked example (Hard)

A spreadsheet contains 200 student records with columns: Name, Year Level, Subject, Score, Grade. Write a formula to count how many Year 8 students scored above 80, and explain how a pivot table could summarise performance by year level.

COUNTIFS formula. =COUNTIFS(B2:B201, "Year 8", D2:D201, ">80") — counts rows where Year Level is "Year 8" AND Score is above 80. COUNTIFS allows multiple criteria across different columns.
Pivot table setup. Row field: Year Level. Values field: Score (set to AVERAGE). This creates a table showing the average score for each year level in one click.
Extended pivot analysis. Add Subject as a Column field → now the table shows average score per year level PER SUBJECT. Filter to show only Year 8 → instant subject comparison for that year level.

Answer: COUNTIFS for multi-criteria counting. Pivot table summarises 200 rows into a compact cross-tabulation of year level × subject × average score.

Common mistakes

Using a person's name as a primary key Names are not unique — two students can have the same name. Names can also change (e.g. after marriage). A primary key must be permanently unique. Fix: Always use a generated ID number (StudentID, BookID) as the primary key. Names, emails and phone numbers are NOT reliable identifiers.
Storing repeated data in every row If a customer's address appears in every order row, updating the address requires changing hundreds of rows. Missing one creates inconsistency. Fix: Normalise: store the address once in a Customers table. Reference it via foreign key from the Orders table.

SQL — the language that talks to databases

Every time you search a website, filter products or log in, a database query runs behind the scenes. Most relational databases use SQL (Structured Query Language) — a standardised language for creating, reading, updating and deleting data.

A basic query looks like: `SELECT Name, Score FROM Students WHERE YearLevel = 'Year 8' AND Score > 80 ORDER BY Score DESC`. This reads as: "Give me the names and scores of Year 8 students who scored above 80, sorted from highest to lowest."

SQL is declarative — you describe WHAT data you want, not HOW to find it. The database engine figures out the most efficient way to retrieve it. This is fundamentally different from procedural programming (Python, JavaScript) where you specify step-by-step instructions.

SQL powers every major platform: Google, Netflix, banks, hospitals, government services. Learning SQL is one of the most employable skills across every industry — not just tech. Many data analysts, marketers and researchers use SQL daily without being programmers.

Practice questions

What is a PRIMARY KEY in a database table?
1. The most important column in the table
2. A unique identifier for each record that cannot be duplicated or null
3. The first column in the table
4. A key used for encryption
(1 marks)
Explain why a school would use a RELATIONAL DATABASE rather than a single spreadsheet for student information, timetables and assessments.
(2 marks)

Summary

Explain what a relational database is and describe tables, fields and records.
Explain how relationships connect tables in a relational database.
Use filtering, sorting and formulas to analyse data in a spreadsheet.