Relational Databases (AQA GCSE Computer Science): Revision Notes
Relational databases
Why do we need relational databases?
When we store all our information in just one big table, we run into some serious problems that make databases messy and unreliable. Let's look at why this happens and how relational databases solve these issues.
The problems with single tables
Imagine you're trying to keep track of students in a school using just one massive table. At first, this might seem like a good idea - everything is in one place! But as you add more information, things start to go wrong.
This simple student table works fine when we only need basic information. But what happens when we want to add details about which classes students are in, who teaches them, and which rooms they use?
Now we can see the problems starting to appear! When we add registration class information to our student table, we create two major issues:
Data redundancy
Data redundancy happens when the same piece of information gets repeated over and over again in your database table. This wastes storage space and creates maintenance problems.
Look at the example above - we can see that "Mr Craddock" appears twice, "9A" appears twice, and room information is repeated. This means we're wasting storage space by storing the same data multiple times.
Think of it like writing your address on every single piece of homework you hand in, even when they all go to the same teacher. It's unnecessary repetition that takes up extra space and time.
Data inconsistency
Data inconsistency is even worse than redundancy - it happens when the same information is stored differently in different places, creating conflicting and unreliable data.
For example, what if someone accidentally typed "Mr Craddok" (missing the 'c') in one row but "Mr Craddock" correctly in another? Now we have conflicting information about the same teacher, and we don't know which one is correct.
The more times we repeat data, the higher the chance that someone will make a mistake and create inconsistent information. This makes our database unreliable and confusing.
What is a relational database?
A relational database is a much smarter way of organising data. Instead of cramming everything into one huge table, it uses multiple smaller tables that are connected (or "related") to each other. This eliminates the problems of data redundancy and inconsistency.
The key insight is that each table should focus on storing information about one specific type of thing. This separation of concerns makes databases more efficient and reliable.
In our school example, we can split our information into separate tables:
- One table for student information
- One table for registration class information
- One table for room information
Here's our cleaned-up student table that focuses just on student information and their registration class.
And here's a separate table that stores all the information about registration classes. Notice how each piece of information only appears once - no more redundancy!
Finally, we have a room table that gives us details about each room. This means if we need to update room information, we only need to change it in one place.
Primary keys and foreign keys
To make relational databases work, we need special fields that help connect the tables together.
Primary keys
A primary key is a field that uniquely identifies each record in a table. Think of it like a student ID number - no two students can have the same ID, so you can always use the ID to find exactly the right student record.
Every table in a relational database should have a primary key. In our student table, StudentID is the primary key because each student has a unique ID number. In the registration table, RegClass is the primary key because each registration class (like 9A, 9B, 10A) is unique.
Foreign keys
A foreign key is a field in one table that refers to the primary key in another table. It's like a bridge that connects the tables together.
For example, in our student table, the RegClass field is a foreign key that connects to the primary key in the registration table. This tells us which registration class each student belongs to, without having to repeat all the registration class information in the student table.
Benefits of relational databases
Using multiple connected tables instead of one giant table gives us several important advantages:
No data redundancy
Each piece of information is stored in only one place. Teacher names, room locations, and other details appear just once in the appropriate table.
No data inconsistency
Since each fact is stored in only one location, there's no chance of having different versions of the same information scattered around the database.
Easier to update
If a teacher changes classrooms, you only need to update one record in the registration table, and the change automatically applies to all students in that class.
More flexible
You can easily add new tables for new types of information (like subjects, exam results, or equipment) without disrupting the existing structure.
Better organisation
Each table has a clear purpose, making the database easier to understand and maintain.
Real-world example
Real-World Analogy: School Office Organization
Think of relational databases like a well-organized school office. Instead of keeping one massive file with every piece of information about every student, teacher, and room mixed together, they use separate filing systems:
- Student files contain personal information
- Class registers show which students are in which classes
- Room booking sheets track where classes happen
- Teacher records store staff information
When they need complete information about a student's timetable, they can quickly look up the connections between these different filing systems. This is exactly how relational databases work - multiple organised tables working together efficiently.
Key Points to Remember:
- Relational databases use multiple connected tables instead of one large table to avoid data problems
- Primary keys uniquely identify each record in a table (like a student ID number)
- Foreign keys connect tables together by referencing primary keys in other tables
- Data redundancy (repeating information) and data inconsistency (conflicting information) are major problems with single-table databases
- Relational databases are more efficient, reliable, and easier to maintain than single-table approaches