Relational databases

Welcome to the World of Databases!

In this chapter, we are going to explore how computers store and organize massive amounts of information. Think about your favorite streaming app, your school's register, or an online shop. They all have one thing in common: they use databases to keep track of everything.

Don't worry if this seems a bit technical at first—we’re going to break it down using simple examples like your school library or a video game inventory. Let's get started!

1. What exactly is a Database?

In the simplest terms, a database is a persistent, organized store of data. Persistent just means it stays saved even when the computer is turned off.

While you could keep a list of your friends' phone numbers in a simple text file, a database is much more powerful. It allows you to search, sort, and link information together very quickly.

The specific type of database you need to know for your exam is a relational database.

A relational database is one that stores data in more than one table. These tables are linked together using special "keys." This is much better than keeping everything in one giant, messy spreadsheet!

Quick Review:
A database is an organized collection of data. A relational database uses multiple tables that are linked together.

2. The Anatomy of a Database Table

To understand databases, you need to know how a table is built. Let's imagine a table called Students.

Fields and Records

Field: This is a single column in a table. It stores one specific category of information. In our Students table, the fields might be FirstName, Surname, and DateOfBirth.
Record: This is a single row in a table. It contains all the data about one specific item or person. One record would be all the information about a student named "Alex Smith".
Table: The whole collection of records and fields.

Data Types

Just like in programming, every field in a database must have a data type. This tells the database what kind of data to expect (e.g., text for a name, integer for a house number, or date for a birthday).

Memory Aid: The "Library" Analogy
Imagine a library. The Table is the bookshelf for "Fiction." A Record is a single book on that shelf. A Field is a specific detail about that book, like its Title or its Author.

3. The "Keys" to the Database

In a relational database, we use two special types of keys to keep everything organized and linked.

The Primary Key

Every table must have a primary key. This is a field that uniquely identifies each record.

Think about it: two students might have the same name ("John Smith"). To tell them apart, the school gives each student a unique Student ID number. That ID is the primary key because no two people can have the same one.

The Foreign Key

A foreign key is a field in one table that is the primary key in another table. It is used to "link" the two tables together.

Example: If we have a Results table, we don't want to type the student's full name and address every time they take a test. Instead, we just put the Student ID (the primary key from the Students table) into the Results table. Now, the tables are linked!

Common Mistake to Avoid:
Students often think a primary key has to be a number. While it usually is (like an ID), it just has to be unique for every row!

4. Why bother with Relational Databases?

Why not just put everything in one big table? Relational databases help us avoid two big problems:

Data Redundancy: This is when the same data is stored more than once. If we had one big table, we might have to type a student's address every time they checked out a book. In a relational database, we only store it once in the Students table.
Data Inconsistency: This happens when redundant data is updated in one place but not another. If a student moves house and we have their address written in five different places, we might forget to update one. By only storing it once, we keep the data accurate.

Key Takeaway:
Relational databases save space (reducing redundancy) and keep data accurate (preventing inconsistency).

5. Talking to the Database: SQL

SQL (Structured Query Language) is the language we use to "talk" to a database. You need to know how to use it to find, add, change, and delete information.

Retrieving Data (The SELECT Statement)

To find information, we use this structure:
SELECT [which fields you want]
FROM [which table]
WHERE [the condition it must meet]
ORDER BY [how to sort it]

Example: To find the names of all students in Year 11, sorted by surname:
SELECT FirstName, Surname
FROM Students
WHERE YearGroup = 11
ORDER BY Surname ASC (ASC means ascending, A-Z)

Adding Data (The INSERT Statement)

To add a new record:
INSERT INTO Students (FirstName, Surname, YearGroup)
VALUES ('Sam', 'Jones', 10)

Editing and Deleting Data

To change existing data:
UPDATE Students
SET YearGroup = 11
WHERE Surname = 'Jones'

To remove data:
DELETE FROM Students
WHERE StudentID = 502

Did you know?
The WHERE clause is incredibly important. If you forget to include a WHERE clause in a DELETE statement, you might accidentally delete every single person in your database! Always double-check your conditions.

Quick Summary Review

Database: A persistent, organized store of data.
Table: Made of records (rows) and fields (columns).
Primary Key: A unique ID for a record.
Foreign Key: A key used to link two tables together.
Redundancy: Storing the same data twice (Bad!).
SQL: The language used to manage databases (SELECT, INSERT, UPDATE, DELETE).

* The content provided by thinka is generated by AI and may not always be accurate or up-to-date. Please use it as a supplementary resource and verify with official materials.