So far in this section we have only looked at databases with one table. But, databases can also have multiple tables that are linked to each other by primary keys.
A relational database is a database containing multiple tables, in order to reduce data inconsistency and redundancy.
Table 1 shows part of the Students table, copied over from 7.01.
Table 1
| StudentID | FirstName | LastName | DateOfBirth |
|---|---|---|---|
| 1225 | William | Smith | 15/04/2009 |
| 1226 | Sid | Chopra | 04/11/2008 |
| 1227 | Freddie | Brown | 28/01/2009 |
| 1228 | William | Jones | 13/09/2008 |
Table 2 shows part of a new Grades table in the same database.
Table 2
| StudentID | Subject | Percentage | Grade |
|---|---|---|---|
| 1225 | Maths | 78 | 8 |
| 1227 | Computer Science | 61 | 6 |
| 1226 | Chemistry | 94 | 9 |
| 1227 | Chemistry | 81 | 8 |
| 1228 | Geography | 69 | 7 |
Because there are multiple records in the Grades table for each student, it would not be
possible to fit everything in one table without redundancy (rewriting the same data over and over again).
A foreign key is a field (column) which is the primary key of another table.
The foreign key in the Grades table is StudentID (because it is the
primary key of the Students table).
You can also use SQL to extract data from relational databases.
Example 1 shows a SQL command to SELECT information about all grades which are at least an 8.
Example 1
SELECT FirstName,
LastName, Subject,
Grade
FROM
Students, Grades
WHERE
Students.StudentID =
Grades.StudentID
AND Grade >= 8
Table 3 shows the output of Example 1.
Table 3
| FirstName | LastName | Subject | Grade |
|---|---|---|---|
| William | Smith | Maths | 8 |
| Sid | Chopra | Chemistry | 9 |
| Freddie | Brown | Chemistry | 8 |
We have got data from both tables because we linked them together with the primary and foreign key.
Is the relationship between Students and Grades "one-to-one", "one-to-many", or "many-to-many"?
It is "one-to-many", because each student has many grades, but each grade refers to one specific student.
An example of a "one-to-one" relationship is between headteachers and schools: each school has one headteacher, and each headteacher works at one school.
An example of a "many-to-many" relationship is between teachers and students: each teacher teaches many students, and each student is taught by many teachers.