Relational Databases and SQL
Tables, records, fields, primary and foreign keys, why splitting data across related tables removes whole categories of error, and every SQL statement the specification requires.
Relational database structure
A database is an organised, persistent store of data. A relational database stores data in linked tables.
| Term | Meaning |
|---|---|
| Table | A set of data about one type of thing, such as Students or Books |
| Record | One row of a table, describing one instance of that thing |
| Field | One column of a table, holding one attribute |
| Primary key | A field whose value is unique for every record, identifying that record |
| Foreign key | A field in one table that holds the primary key of a record in another table, creating the link |
Why data is split across tables
Suppose a library keeps everything in one table, with the borrower's name and address repeated on every loan.
Three problems follow immediately, and each has a name worth using:
- Redundancy. The same address is stored dozens of times, wasting space.
- Update anomalies. When a borrower moves, every one of those copies must be changed. Miss one and the database now holds two contradictory addresses with no way to tell which is right.
- Insertion problems. A new borrower who has not yet taken a book out cannot be recorded at all, because there is no loan row to put them in.
Splitting the data into a Borrowers table and a Loans table solves all three. The address is stored exactly once, changing it is one edit, and a borrower can exist without any loans.
The Loans table then holds a foreign key, the borrower's ID, which links each loan back to exactly one borrower.
Storing each fact once and linking tables by key removes redundancy and makes inconsistent data impossible, because there is only one copy to change.
- One table for students. Each row is a student, each column a field.
- A primary key: one field whose value is different in every row.
- A second table for courses, with its own primary key.
- A foreign key in one table holds the primary key of the other.
- That link is why the data is stored once and not repeated.
SQL: retrieving data
SQL, Structured Query Language, is the language used to work with a relational database.
SELECT
SELECT firstName, surname
FROM Students
WHERE yearGroup = 11
ORDER BY surname ASCSELECTlists the fields you want.SELECT *means every field.FROMnames the table.WHEREfilters which records are returned.ORDER BYsorts the results,ASCfor ascending andDESCfor descending.
Conditions in WHERE
WHERE mark >= 70 AND yearGroup = 11
WHERE surname = 'Patel' OR surname = 'Khan'
WHERE title LIKE '%computing%'LIKE with % as a wildcard matches part of a value, which is how you search for text containing something rather than exactly equal to it.
Joining two tables
SELECT Borrowers.surname, Loans.dueDate
FROM Borrowers, Loans
WHERE Borrowers.borrowerID = Loans.borrowerIDThe condition matching the primary key to the foreign key is what performs the join. Leave it out and every borrower is paired with every loan, which is almost never what you want.
WHERE surname = 'Khan' but WHERE yearGroup = 11. Getting this wrong is a guaranteed lost mark.
- Each clause has a job: SELECT picks columns, FROM names the table, WHERE picks rows, ORDER BY sorts.
- WHERE keeps the rows that meet the condition and drops the rest.
- The result has only the selected columns, in the order asked for.
SQL: changing data
INSERT
INSERT INTO Students(studentID, firstName, surname, yearGroup)
VALUES (4102, 'Amira', 'Hassan', 10)The list of fields and the list of values must be in the same order and of matching types.
UPDATE
UPDATE Students
SET yearGroup = 11
WHERE yearGroup = 10DELETE
DELETE FROM Students
WHERE studentID = 4102DELETE FROM Students deletes every record in the table. UPDATE Students SET yearGroup = 11 sets every student to year 11. Both statements are valid SQL and both run instantly, which is why the WHERE clause is the most important line in either.
Writing SQL in an exam
- Which table or tables hold the data. That is your FROM.
- Which records do you want. That is your WHERE.
- Which fields should appear. That is your SELECT.
- Does the order matter. That is your ORDER BY.
Answering in that order rather than left to right stops the most common error, which is selecting the right fields from the wrong table.
Key terms you must be able to define
- Relational database
- A database storing data in tables that are linked to one another by keys.
- Table
- A set of data about one type of thing, arranged in records and fields.
- Record
- One row of a table, describing a single instance.
- Field
- One column of a table, holding one attribute of every record.
- Primary key
- A field whose value uniquely identifies each record in a table.
- Foreign key
- A field holding the primary key of a record in another table, creating a link between them.
- Redundancy
- The same data being stored more than once, wasting space and risking inconsistency.
- SQL
- Structured Query Language, used to retrieve and modify data in a relational database.
- Wildcard
- A symbol such as % that stands for any sequence of characters in a LIKE comparison.
Flashcards
Say the definition out loud before you flip the card. Retrieving it from memory is what builds recall, reading it again is not.
Reach the top grade on this topic
Two things separate full marks here.
Justify table splitting with a named consequence. Not "it is tidier" but "the address is stored once, so it cannot become inconsistent when a borrower moves, and a borrower can exist before they have taken out any loans".
Write SQL that would actually run. Quotation marks round text, none round numbers, field names spelled exactly as the question gives them, and a WHERE clause on every UPDATE and DELETE. Examiners mark SQL strictly because a query that does not run does not retrieve anything.
Explain a foreign key as the mechanism, not the label. It holds the primary key of a record in another table, and matching the two in a WHERE clause is what joins them.
Mistakes that cost marks here
- Putting quotation marks round numeric values, or leaving them off text values.
- Forgetting the WHERE clause on an UPDATE or DELETE, which affects every record in the table.
- Forgetting the join condition when selecting from two tables.
- Saying a primary key is 'the first field'. It is the field whose value is unique for every record.
- Confusing a record with a field. A record is a row, a field is a column.
SQL was designed at IBM in the 1970s to be readable by people who were not programmers, which is why it reads almost like English. That decision is the main reason it is still in daily use fifty years later.
Knowledge check
Exam-style questions
Five written questions in the style of the real paper. Write a full answer, then let the marker check it against the mark scheme.
- LA primary key uniquely identifies each record in its own table
- LA foreign key holds the primary key of a record in another table, linking the two
A primary key is a field whose value is unique for every record in its own table, so it identifies that record without ambiguity. A foreign key is a field in one table that holds the primary key value of a record in another table, which is what creates the link between the two tables.
- LSelects the correct two fields
- LUses FROM Students
- LFilters on yearGroup with no quotation marks round the number
- LSorts by surname
The query is: SELECT firstName, surname FROM Students WHERE yearGroup = 11 ORDER BY surname. The two field names appear after SELECT because only those columns are wanted, Students follows FROM because that is where the data is, the WHERE clause filters to year 11 with no quotation marks because the value is numeric, and ORDER BY surname sorts the results alphabetically, which is ascending by default.
- LRepeating the details would store the same data many times, which is redundancy
- LWasted storage is one cost
- LIf a detail changes it would have to be corrected in every copy
- LMissing one copy would leave the database inconsistent, with no way to tell which version is right
Repeating the borrower's name and address on every loan record means storing the same facts dozens of times over, which wastes storage but, far more importantly, creates a maintenance problem. When a borrower moves house, every single copy of their address has to be found and changed. If one is missed the database then holds two different addresses for the same person with nothing to say which is correct, and any report using the data may be wrong. Storing the borrower once in its own table and linking each loan to it by a foreign key means each fact exists in exactly one place, so a change is a single edit and inconsistency is impossible. It also allows a borrower to be recorded before they have taken out any books, which a combined table could not do.
- LUses UPDATE on the correct table
- LUses SET to change yearGroup to 11
- LIncludes a WHERE clause identifying the single student
The statement is: UPDATE Students SET yearGroup = 11 WHERE studentID = 4102. The WHERE clause is essential rather than optional here: without it the statement would set the year group of every student in the table to 11, and it would do so immediately with no confirmation.