Mission 1 · Spec 3.7.1
Relational databases
Tables, records, fields and keys, and why splitting data into linked tables avoids redundancy and inconsistency.
- Starter 5 min
- Learn 10 min
- Lab 15 min
- Quiz 10 min
- Exam 10 min
Spot the problem
| StudentID | Name | Club | ClubDay | Teacher |
|---|---|---|---|---|
| 1 | Aisha Khan | Robotics | Wednesday | Ms Okafor |
| 7 | Grace Stewart | Robotics | Wednesday | Ms Okafor |
| 12 | Omar Ali | Robotics | Tuesday | Ms Okafor |
What's wrong with storing the data like this? What would happen if Robotics moved to Thursday?
Reveal
The club details are repeated in every row (data redundancy), wasting space. Someone updated one row but not the others, so Omar's row now disagrees (data inconsistency). Moving the club means changing every row. The fix is to store clubs once in their own table and link to them.
Key ideas
Database
A persistent, organised store of data. A relational database stores data in separate tables that are linked together.
Table, record, field
A table holds data about one type of thing. Each row is a record (one student). Each column is a field (e.g. Surname) with a data type.
Primary key
A field that uniquely identifies each record in a table, e.g. StudentID.
Foreign key
A field in one table that is the primary key of another table. It creates the link (relationship) between them.
Clubs(ClubID, ClubName, Day, Teacher)
ClubID in Students is a foreign key linking to Clubs.
Because each club is stored once, there's no redundancy, and changing a club's day means updating one record, so the data can't become inconsistent.
Database vocabulary
Good primary key?
For a table of customers, which fields could be a primary key?Exam-style questions
1. Using the Students and Clubs tables above, identify the primary key of Clubs and a foreign key in Students.
[2 marks]Mark scheme
- Primary key of Clubs: ClubID (1)
- Foreign key in Students: ClubID (1)
2. Explain how using two linked tables instead of one reduces data inconsistency.
[2 marks]Mark scheme
- Each club's details are only stored once / there is no data redundancy (1)
- So a change only has to be made in one place, and copies can't disagree (1)
3. Define the term record.
[1 mark]Mark scheme
- A collection of related fields about one item / entity; one row in a table (1)
TUTOR NOTES
- Misconception: a foreign key is "a key from another database". It's a field in this table that matches another table's primary key.
- Misconception: redundancy and inconsistency are the same. Redundancy causes inconsistency.
- Exam habit: in the Students(StudentID, …) notation, the primary key is underlined.
- Extension: why can't a student belong to two clubs with this design? How would you fix it?
Mission 2 · Spec 3.7.2
SQL: asking questions
Retrieve exactly the data you want with SELECT, FROM, WHERE and ORDER BY, from one table or two.
- Starter 5 min
- Learn 10 min
- Lab 25 min
- Quiz 10 min
- Exam 10 min
Ask the database
In plain English, how would you ask a friend holding a class list for "the names of everyone in 10B, in alphabetical order"? Which parts of your question say what, where from, which ones and in what order?
Reveal
SQL has a keyword for each part: SELECT (what), FROM (where from), WHERE (which ones), ORDER BY (what order). SELECT FirstName, Surname FROM Students WHERE Form = '10B' ORDER BY Surname ASC
SELECT queries
FROM table
WHERE condition
ORDER BY field ASC | DESC
*means every field:SELECT * FROM Clubs- Text values go in quotes:
WHERE Form = '10B'. Numbers don't:WHERE Age >= 15 - Combine conditions with
ANDandOR. ASCsorts A to Z / smallest first;DESCsorts Z to A / largest first.- Two tables: list both after FROM, and link them in the WHERE clause:
WHERE Students.ClubID = Clubs.ClubID
SQL playground
A real database runs in your browser. Solve each challenge.Exam-style questions
1. Write an SQL query to show the ClubName and Teacher of all clubs that meet on Monday.
[3 marks]Mark scheme
SELECT ClubName, Teacher(1)FROM Clubs(1)WHERE Day = 'Monday'(1)
2. Write an SQL query to show the Surname and Form of students aged 16, in descending order of surname.
[4 marks]Mark scheme
SELECT Surname, Form(1)FROM Students(1)WHERE Age = 16(1)ORDER BY Surname DESC(1)
3. Write an SQL query to show the FirstName of each student and the Day their club meets.
[3 marks]Mark scheme
SELECT Students.FirstName, Clubs.Day(1)FROM Students, Clubs(1)WHERE Students.ClubID = Clubs.ClubID(1)
TUTOR NOTES
- Misconception: forgetting quotes around text values, or putting them around numbers.
- Misconception: missing the link condition in two-table queries, which returns every combination of rows.
- Lab prompt: run challenge 7 without the WHERE clause. Why are there 50 rows?
- Extension: show students who are NOT in Robotics.
Mission 3 · Spec 3.7.2
SQL: changing data
Add, change and remove records with INSERT, UPDATE and DELETE.
- Starter 5 min
- Learn 10 min
- Lab 20 min
- Quiz 10 min
- Exam 10 min
The dangerous DELETE
What do you think DELETE FROM Students does, with no WHERE clause?
Reveal
It deletes every student. The same is true of UPDATE: without WHERE it changes every record. Always check your WHERE clause first.
Changing data
INSERT
INSERT INTO Clubs (ClubID, ClubName, Day, Teacher)
VALUES (6, 'Art', 'Tuesday', 'Mr Byrne')
If you give a value for every field in order, the field list can be left out.
UPDATE
UPDATE Students
SET Form = '11B'
WHERE StudentID = 10
DELETE
DELETE FROM Students
WHERE StudentID = 4
Tip
Use the primary key in the WHERE clause to be sure you change exactly one record.
SQL playground
Your changes are checked against the table you should end up with.Exam-style questions
1. Write an SQL statement to add a club with ClubID 6, name 'Art', meeting on Tuesday, run by 'Mr Byrne'.
[3 marks]Mark scheme
INSERT INTO Clubs(1)VALUES(1)(6, 'Art', 'Tuesday', 'Mr Byrne')in the correct order (1)
2. Write an SQL statement to change the teacher of the Chess club to 'Ms Wood'.
[3 marks]Mark scheme
UPDATE Clubs(1)SET Teacher = 'Ms Wood'(1)WHERE ClubName = 'Chess'orWHERE ClubID = 1(1)
3. Write an SQL statement to delete all students in form 11B.
[2 marks]Mark scheme
DELETE FROM Students(1)WHERE Form = '11B'(1)
TUTOR NOTES
- Misconception:
DELETE * FROM. There's no * in DELETE. - Misconception:
UPDATE … WHERE … SET. SET comes before WHERE. - Exam habit: copy table and field names exactly as they appear in the question.
- Extension: what should happen to students whose club is deleted? (Referential integrity.)