CS21Relational Databases and SQL

A database is an organised collection of persistent data. Persistent means it stays stored when the program closes or the computer is switched off.

• A relational database is a database with multiple tables that are linked together (that reference each other).
• The tables are linked by keys: a field in one table holds values of the primary key of another table.
• A database with just one table is sometimes called a flat file. Putting everything in one table means the same details get typed again and again.
• Splitting data into linked tables means each set of details (a customer’s name and phone number, say) is stored once. That cuts duplication and the mistakes it causes.
• Examples: a shop’s customers and orders, a doctor’s surgery’s patients and appointments, a streaming service’s users and playlists.

Database

An organised collection of persistent data.

Relational database

A database with multiple tables that are linked together (that reference each other).

Persistent

Kept after the program ends or the power goes off, because it is held in secondary storage.

A bike-hire shop keeps a Customer table. Each row is a record and each column is a field.

• Table: a structured collection of data organised into records (rows) and fields (columns). The table above has 4 records and 4 fields.
• Record: a single entry in a table. It holds all the data about one item, such as one customer.
• Field: a single piece of data within a record, such as a first name. Every record has the same fields.
• Field names have no spaces: FirstName, not First Name.
• Data type: each field has one data type, which decides what values it can hold. Choose the most suitable one:
• Integer for whole numbers (Hours, Stock); real for numbers with decimals (Price); string (text) for names and anything with letters, spaces or leading zeros (a phone number); Boolean for yes/no fields (Paid); date for dates (HireDate).
• A database record and a programming record (a RECORD structure in code) are different ideas with the same name. Here, a record is one row of a table.

Table

A structured collection of data organised into records (rows) and fields (columns).

Record

A single entry in a table, holding the data about one item.

Field

A single piece of data within a record, such as a first name.

Data type

What kind of value a field holds, such as integer, real, string, Boolean or date.

Keys let the database pick out one record and link tables together.

• Primary key: a field that is unique for every record in a table. CustomerID identifies exactly one customer.
• Names, towns and dates make poor primary keys because two records can share them. That’s why tables usually have an ID field.
• Foreign key: a field in one table that is also a primary key in another table. Foreign keys are used to link one table to another.
• In Hire, CustomerID is a foreign key. Hire 103 has CustomerID 1, so it belongs to Priya Shah.
• A foreign key value can repeat: Priya has two hires, so 1 appears twice in Hire’s CustomerID column. A primary key value can’t repeat within its own table.
• To find the primary key of a table, ask: which field is different in every record, now and in the future?

CustomerID is the primary key of Customer and a foreign key in Hire.

Primary key

A field that is unique for every record in a table.

Foreign key

A field in one table that is also a primary key in another table, used to link one table to another.

Relational databases help to remove two problems that come from storing the same data more than once.

• Data redundancy: the unnecessary duplication of data within a database. In one big table, a customer’s name and phone number are typed again for every hire.
• Data inconsistency: the same piece of data is stored in various parts of the database, but the copies are not identical, so the data can’t be relied on. One copy of Priya’s number ends 101 and another ends 110: which is right?
• Redundancy causes inconsistency: every extra copy is another chance for a typing mistake, or for one copy to be updated and the others missed.
• The fix: split the data into linked tables. Customer details go in a Customer table, stored once. Each hire stores only the CustomerID (a foreign key).
• Now a phone number is changed in one place, and every hire linked to that customer uses the new value.
• Redundancy also wastes storage space, and a flat file can’t hold a customer until they’ve made a hire.

Storing Priya’s details once removes the duplicates and the mismatch.

Data redundancy

The unnecessary duplication of data within a database.

Data inconsistency

When the same data is stored in various parts of a database but the copies are not identical, so the data is unreliable.

Flat file

A database that keeps all its data in one table.

SQL (structured query language) is used to retrieve, add, change and delete data in a relational database. A SELECT query retrieves data.

• SELECT lists the fields to show, separated by commas. FROM names the table. WHERE gives the condition a record must meet.
• Text values go in quotes: WHERE Town = 'Ashford'. Single or double quotes are both fine. Numbers don’t need quotes. Dates go in quotes too: WHERE HireDate = '2026-05-09'.
• Comparison operators: =, <> (not equal), <, >, <=, >=. SQL uses a single = to compare.
• Join conditions with AND (both must be true) or OR (either can be true). A range needs two conditions:

• SELECT HireID, BikeType
  FROM Hire
  WHERE Hours >= 3 AND Hours <= 5

• On the Hire table above this returns hires 101 (Road), 102 (Mountain) and 104 (Road).
• With OR, write the field name again each time: WHERE BikeType = 'Road' OR BikeType = 'Electric', not BikeType = 'Road' OR 'Electric'.
• SELECT * means every field. It is real SQL, but exam questions name the fields they want, and the mark needs those fields and no others.

Each clause answers one question about the data you want.

SQL

Structured query language: the language used to retrieve and change data in a relational database.

Query

A request to retrieve data from a database, written with SELECT.

ORDER BY goes at the end of a query and sorts the results by a field.

• ASC sorts in ascending order: A to Z, smallest number first, earliest date first. If you leave ASC and DESC out, ascending is used.
• DESC sorts in descending order: Z to A, largest first, latest date first.

• SELECT FirstName, LastName
  FROM Customer
  ORDER BY LastName ASC

• This gives Novak, Okafor, Reid, Shah.
• You can sort by more than one field: ORDER BY Town ASC, LastName ASC sorts by town, then by last name within each town.
• The order of clauses is fixed: SELECT, FROM, WHERE, then ORDER BY.
• Read the question’s wording: “most recent first”, “highest first” and “Z to A” all mean DESC; “oldest date first”, “cheapest first” and “alphabetical” mean ASC.

ASC

Ascending: A to Z, smallest first, earliest date first. The default.

DESC

Descending: Z to A, largest first, latest date first.

When the fields you need are in two tables, the query must say how the tables link. Exam queries use no more than two tables.

• Method 1, a join in WHERE: list both tables in FROM and match the primary key to the foreign key in WHERE.

• SELECT FirstName, HireDate
  FROM Customer, Hire
  WHERE Customer.CustomerID = Hire.CustomerID
  AND BikeType = 'Electric'
  ORDER BY HireDate ASC

• Method 2, INNER JOIN … ON: AQA credits this equally.

• SELECT FirstName, HireDate
  FROM Customer INNER JOIN Hire
  ON Customer.CustomerID = Hire.CustomerID
  WHERE BikeType = 'Electric'
  ORDER BY HireDate ASC

• Both return Priya, 2026-05-09 and Sam, 2026-05-16.
• Table.Field: when the same field name is in both tables (the linking field), put the table name first with a dot: Customer.CustomerID. Table first, field second.
• Table names in front of the other fields are allowed too (Customer.FirstName), as long as each is the right table.
• Forgetting the join condition matches every customer with every hire, so the results are wrong.

Each hire is matched to its customer, then the WHERE condition filters the matches.

Join

Combining records from two tables by matching a foreign key to a primary key.

INNER JOIN … ON

SQL that joins two tables, with the matching condition after ON.

INSERT INTO adds a new record to a table.

• The form is INSERT INTO table (field1, field2, …) VALUES (value1, value2, …).

• INSERT INTO Customer (CustomerID, FirstName, LastName, Town)
  VALUES (5, 'Leah', 'Brown', 'Dover')

• The values must be in the same order as the fields listed, with the same number of each.
• Each value must suit its field’s data type: text and dates in quotes, numbers without (quotes round numbers are tolerated). A value of the wrong type, or in the wrong place, puts the wrong data in a field.
• The keyword is VALUES, plural, even for one value.
• The new record needs a primary key value that isn’t already used: inserting a second customer with ID 5 would be rejected.

INSERT INTO

SQL that adds a new record: INSERT INTO table (fields) VALUES (values).

UPDATE changes data in existing records. DELETE removes whole records. Both use WHERE to say which records.

• UPDATE: UPDATE table SET field1 = value1, field2 = value2 WHERE condition. Separate the changes with commas.

• UPDATE Hire
  SET BikeType = 'Mountain', Hours = 4
  WHERE HireID = 103

• Using the primary key in WHERE changes exactly one record. A condition that matches several records changes all of them: UPDATE Hire SET Hours = Hours + 1 WHERE BikeType = 'Road' adds an hour to both road-bike hires.
• DELETE: DELETE FROM table WHERE condition. It removes the whole record, so no field names are listed.

• DELETE FROM Hire
  WHERE CustomerID = 1 AND BikeType = 'Electric'

• This removes hire 103 only. Both conditions must be true, and when one condition alone would match the wrong records, you need both.
• Without WHERE, every record is changed or deleted. DELETE FROM Hire empties the table. A good habit: test the WHERE condition with a SELECT first.
• Deleting a record that doesn’t exist does nothing. It isn’t an error.

UPDATE

SQL that changes data in existing records: UPDATE table SET field = value WHERE condition.

DELETE

SQL that removes whole records: DELETE FROM table WHERE condition.