034. from flat to relational: rescuing redundant data (ks3)
Understand the difference between flat files and relational databases to prevent messy data storage.
Have you ever tried to find a specific piece of information in a massive, disorganised list? It's a nightmare! In the digital world, this problem is even bigger. Storing data in one giant 'flat file' is like throwing all your school's records into a single, messy spreadsheet. It's simple at first, but it quickly leads to repetition, mistakes, and chaos. Today, we're on a data rescue mission. We'll discover why this approach fails and learn the professional technique that keeps data accurate and organised: the relational database.
From Flat to Relational: The Data Rescue Mission
The Problem with the Giant List
Imagine your school kept all its information in one single, massive spreadsheet. This is called a flat-file database. For every single lesson a student has, there would be a new row containing the student's name, their form, the lesson name, the teacher's name, the teacher's office number, and the classroom.
At first, this seems simple. But what happens if a teacher, Mrs. Smith, moves to a new office? You would have to hunt through thousands of rows and manually change her office number every single time her name appears. If you miss just one, the data is now inconsistent and wrong. This problem is called an update anomaly.
This single-table approach suffers from a huge problem called data redundancy - storing the exact same piece of information over and over again. This wastes space and, more importantly, it makes the data unreliable. What if you spell Mrs. Smith's name "Mrs. Smyth" in one row by accident? The computer now thinks there are two different teachers!
The Relational Rescue
To solve this, database architects use a relational database. Instead of one giant table, they split the data into smaller, logical tables that each deal with one specific thing.
For our school example, we could create two tables:
A 'Students' table.
A 'Tutors' table.
The 'Tutors' table would store the information for each tutor only once. Each tutor is given a unique ID number, like a badge number. This unique identifier is called the Primary Key. Mrs. Smith's record might be: 'TutorID: 101, Name: "Smith", Office: "B204"'.
Now, in the 'Students' table, instead of typing out "Smith" and "B204" for every student she teaches, we simply use her unique ID, 101. When this Primary Key is used in another table to create a link, it's called a Foreign Key.
The Magic Link
This relationship is the magic of the system. If Mrs. Smith moves office again, we only need to change it in one single place - in her record in the 'Tutors' table. Because all the student records are linked to her via the Foreign Key '101', they are all automatically pointing to the new, correct information.
By splitting the data and linking it, we have:
Eliminated data redundancy: The tutor's details are stored only once.
Improved data integrity: We cannot make spelling mistakes or have conflicting information.
Increased efficiency: The database is smaller and faster.
This is the fundamental principle behind almost every professional database system in the world, from your bank to your social media feed.
Comprehension Questions
Make sure you have read the passage carefully. Grab yourself a piece of lined paper and put your name, class and date at the top. Attempt the questions following questions making sure to answer in full sentences.
Knowledge, recall, identification
1
What is a 'flat-file database'?
2
What does the term 'data redundancy' mean?
3
Define what a 'primary key' is used for in a database table.
Analysis & Interpretation
4
Explain the main problem with storing all of a school's data in a single, large flat-file table.
5
Compare a primary key with a foreign key. What is their relationship and how do they work together?
6
Explain why data redundancy can lead to an 'update anomaly'. Use the example of a teacher moving office to support your answer.
Synthesis & Creation
7
Imagine a flat-file database for a library that stores book details and borrower details in one big table. Design two separate tables ('Books' and 'Borrowers') that would be more efficient, listing the fields each table should contain.
8
For the library tables you designed in the previous question, suggest a suitable primary key for each table.
9
Propose how you would link the 'Books' and 'Borrowers' tables to show which person has which book. What extra information or new table might you need to make this work?
Evaluation & Justification
10
A small corner shop uses a single spreadsheet to track its 100 products. Justify why this business might not need to invest time in creating a complex relational database.
11
A large online store like Amazon uses a relational database. Evaluate the benefits of this approach for managing millions of customers, orders, and products simultaneously.
12
To what extent does splitting data into multiple linked tables make it harder for a human to get a complete picture of the information at a glance? Justify your answer.
Plugged Task: Database Architect - The Great School Split
The Head of Year has come to you with a problem. The school's student data is stored in one massive, messy spreadsheet. Every time a tutor changes their name or office, a junior administrator has to update hundreds of rows, and they keep making mistakes! The data is unreliable, and it's your job to fix it.
The Persona
You are The Data Custodian. Your mission is to protect data integrity, eliminate redundancy, and bring order to chaos. You must redesign this flat file into a clean, efficient, and professional relational database structure.
1
Analyse the Mess
First, you need to understand the problem. Look at this example of the current messy spreadsheet. Notice how the tutor's details (Mr. Jones, Mrs. Davis) are repeated over and over again. This is data redundancy.
| StudentID | FirstName | LastName | TutorName | TutorOffice |
|-----------|-----------|----------|-----------|-------------|
| 1001 | Alice | Smith | Mr. Jones | A101 |
| 1002 | Ben | Williams | Mrs. Davis| C203 |
| 1003 | Charlie | Brown | Mr. Jones | A101 |
| 1004 | David | Miller | Mr. Jones | A101 |
| 1005 | Emily | Wilson | Mrs. Davis| C203 |
2
Design the New Tables
Now, you will design the solution.
Open a new spreadsheet file in a program like Google Sheets or Microsoft Excel.
Create two separate sheets (tabs) at the bottom. Name one 'Students' and the other 'Tutors'.
3
Create the Table Structures
On each sheet, create the correct columns (fields) for your new, efficient database.
On the 'Tutors' sheet, create these columns: 'TutorID' (this will be the Primary Key), 'TutorName', 'TutorOffice'.
On the 'Students' sheet, create these columns: 'StudentID' (Primary Key), 'FirstName', 'LastName', 'TutorID' (this will be the Foreign Key).
You are a database expert. Explain the difference between a Primary Key and a Foreign Key in a relational database in under 50 words. Your audience is a 14-year-old. Use the analogy of a unique student ID number. NO intro, NO outro, NO deviation from the topic, NO follow-up questions
4
Populate your Database
It's time to move the data.
Go to the 'Tutors' sheet first. Create a unique 'TutorID' for each tutor and enter their details. Crucially, you should only have one row for Mr. Jones and one row for Mrs. Davis.
Now go to the 'Students' sheet. Enter the details for all five students.
In the 'TutorID' column, do not type the tutor's name. Instead, type the unique ID number that you gave to their tutor in the 'Tutors' table. This creates the 'magic link'.
5
Explain your Solution
In a new text box or on a new sheet in your file, write two short paragraphs to the Head of Year.
Explain why you have split the data into two tables.
Explain how the 'TutorID' now links the two tables together and what problem this solves.
Outcome
You have created a spreadsheet with two separate, correctly named sheets.
Each sheet has the correct columns (fields) as specified in the instructions.
The 'Tutors' table contains no redundant data.
The 'Students' table uses the TutorID as a Foreign Key to link to the tutors.
You have written a clear explanation of your professional solution.
Unplugged Task: ERD Blueprint - The Vet's Practice
On a piece of plain paper, you are going to design the blueprint for a relational database for a local vet's practice. This type of diagram is called an Entity-Relationship Diagram (ERD).
1
Identify the Entities: An entity is a 'thing' we need to store data about. For a vet, the main entities are 'Pet', 'Owner', and 'Appointment'. Draw a rectangular box for each of these three entities.
2
List the Attributes: Inside each box, list the fields (attributes) that entity would need. For example, for the 'Owner' entity, you might list fields like 'OwnerID', 'FirstName', 'LastName', and 'PhoneNumber'. Do this for all three entities.
3
Assign the Keys: For each entity, decide which attribute should be the unique Primary Key and label it with '(PK)'. Then, figure out where you need to place Foreign Keys to link the tables together and label them with '(FK)'. For example, how do you know which pet belongs to which owner?
4
Draw the Relationships: Draw lines connecting the boxes to show how they are related. An owner can have many pets, so you would draw a line from the 'Owner' table to the 'Pet' table to show this one-to-many relationship.
Last modified: June 22nd, 2026
