Relational Databases: Tables, Keys and Relationships
What Is a Database?
A database is an organised collection of data. Unlike a simple spreadsheet, a database is designed to store large amounts of information and allow it to be searched, sorted and updated quickly. Schools use databases to store pupil records; hospitals use them to track patient appointments; online shops use them to manage product stock. Anywhere large amounts of structured data need to be managed, a database is the tool of choice.
Relational Databases
A relational database stores data in multiple linked tables rather than one large list. Each table holds data about a single topic (for example, students, or courses). The tables are connected to each other using special key fields. This structure was developed to solve two serious problems that arise when you try to keep everything in one place: data redundancy and data inconsistency.
Data redundancy means storing the same piece of data in more than one place. Data inconsistency means different copies of the same data ending up with different values. Relational databases tackle both by storing each piece of data in exactly one place.
Tables, Records and Fields
Every relational database is made up of tables. Each table has a name (for example, Students) and is divided into columns called fields (or attributes) and rows called records (or tuples).
- A field (also called an attribute) is a single category of data — for example, student_name or year_group. Each field has a specified data type: text, integer, date, Boolean and so on.
- A record (also called a tuple or row) is one complete entry in the table — all the data about one particular student, for example.
Think of a table like a grid: the column headings are the fields, and each row is a record. Here is a sample Students table:
The Students table has four fields: student_id, student_name, year_group, tutor_group.
Sample records:
1 | Aisha Khan | 10 | 10A
2 | Ben Okafor | 11 | 11B
3 | Chloe Davis | 10 | 10A
Here student_id is the primary key — each student has a unique number that is never repeated or reused. The other fields store descriptive information about the student. Notice that 10A appears twice: this is fine because it is just a tutor group label, not the student’s main identifier.
Primary Keys
Every table must have a primary key. This is a field whose value is different for every record, so it can be used to pinpoint exactly one row. In the Students table, student_id is the primary key. You cannot use student_name as a primary key because two students could share the same name.
Primary keys are often auto-generated integers (1, 2, 3…) although they can be any unique value — an NHS number, a National Insurance number, a barcode. The key point is that no two records can ever have the same primary key value, and the field must always have a value (it cannot be left blank).
Foreign Keys
A second table—say, an Enrolments table—needs to know which student each enrolment belongs to. Rather than copy the student’s full name and year group into the Enrolments table (which would create redundancy), we store only the student’s student_id. In the Enrolments table, this field is called a foreign key.
The foreign key “points to” the primary key in the other table. This is how the two tables are linked. If you want to find a student’s name for a given enrolment, you look up the student_id in the Students table using the foreign key as a reference.
We have two tables:
Students (student_id [PK], student_name, year_group)
Courses (course_id [PK], course_title)
Enrolments (enrolment_id [PK], student_id [FK], course_id [FK])
The Enrolments table uses student_id as a foreign key linking back to Students, and course_id as a foreign key linking back to Courses. This means:
- Aisha’s name is stored once in Students — there is no need to write “Aisha Khan” in every enrolment row for her.
- If Aisha’s name is ever corrected, you update it in one place and it is immediately correct everywhere — no inconsistency possible.
Relationships Between Tables
The most common relationship at GCSE is a one-to-many relationship. In our example, one student can have many enrolments, but each enrolment belongs to exactly one student. The foreign key always sits in the “many” side of the relationship (the Enrolments table), pointing back to the primary key on the “one” side (the Students table).
A primary key uniquely identifies a record in its own table. A foreign key in one table references the primary key in another table, linking the two tables together. In a one-to-many relationship, the foreign key is always in the “many” table.
Why Not Just Use One Big Table?
Imagine trying to store all student and course information in a single flat table with columns like: student_id, student_name, year_group, course_title, teacher_name, room_number. Every time a student takes two courses, you would need two rows — with the student’s name and year group repeated in both. This is data redundancy. Now imagine the student changes year group: you would need to update every row for that student. Miss one, and you now have inconsistent data. The relational approach solves both problems by splitting data across properly linked tables.
Vocabulary Equivalence Note
The same concepts appear with different names depending on context. When discussing an abstract data model: an entity maps to a table; an attribute maps to a field; an entity identifier maps to a primary key. Exam papers use table, field and primary key for both real databases and abstract models, so use those words in your answers. Learn the abstract set as well, so you recognise it if you meet it elsewhere.