Revision notes for AQA A Level Computer Science Relational databases. Open the guide for explanations and worked examples. Written against the AQA A Level Computer Science specification, so the content matches what's examinable rather than general Computer Science background.

Relational databases

What you'll learn

  • How a relational database stores data in tables called relations.
  • What attributes, primary keys, composite primary keys and foreign keys mean.
  • How keys connect separate tables without duplicating the same data everywhere.
  • How to choose sensible keys in a simple database design.

The big idea: data in related tables

A database is an organised collection of data that can be searched, updated and managed. In a relational database, the data is not stored as one enormous list. Instead, it is split into tables that represent different things, such as students, courses or enrolments.

An entity is a thing about which data is stored. For example, STUDENT could be an entity, and each student would be one row in the student table.

Definition

Relational database

A relational database is a database made up of related tables, called relations. Each table stores data about one type of entity, and relationships between tables are represented using matching key values.

Key Idea

Tables are linked by data values

A relational database does not usually store one table physically “inside” another. Instead, rows in different tables are connected by shared identifier values, such as a StudentID appearing in both STUDENT and ENROLMENT.

Why split data into tables?

Suppose a school stored every course enrolment in one large table:

StudentIDStudentNameCourseIDCourseNameTeacher
1001Amelia RobertsC101MathematicsMr Smith
1002Benjamin ClarkC101MathematicsMr Smith
1003Chloe PatelC104Computer ScienceMr Brown

The course name and teacher may be repeated many times. If Mr Smith is replaced, every matching row must be updated. If one row is missed, the data becomes inconsistent.

A relational design stores course details once in a COURSE table, then uses a key value such as CourseID to refer to that course from other tables.

Example

Reducing duplicated course data

  1. Identify the repeated data: CourseName and Teacher are repeated for every student taking the same course.

  2. Separate the repeated course data into its own table: COURSE can store CourseID, CourseName and Teacher once per course.

  3. Keep only the link in the enrolment table: ENROLMENT stores StudentID and CourseID, so each row says which student takes which course.

  4. The design now needs fewer repeated values, and changing a course teacher requires one update in COURSE rather than many updates in ENROLMENT.

Relations, records and attributes

Definition

Relation

A relation is a table in a relational database. It is made up of rows and columns, and it normally stores one type of entity.

A record is one row in a table. It represents one instance of the entity. For example, one row in STUDENT represents one student.

Definition

Attribute

An attribute is a named column in a table. It stores one piece of data about each record, such as Surname, DateOfBirth or CourseID.

For example:

STUDENT(StudentID, Forename, Surname, TutorGroup)

Here, StudentID, Forename, Surname and TutorGroup are attributes of the STUDENT relation.

Primary keys

When a table contains many records, the database needs a reliable way to identify exactly one row.

Definition

Primary key

A primary key is an attribute, or set of attributes, chosen to uniquely identify each record in a table. A primary key value must not be repeated in that table, and every record must have a value for it.

A good primary key should be:

  • unique — no two rows have the same value
  • present — every row has a value
  • stable — it should not need to change often

Names are usually poor primary keys because two people can have the same name, and names can change.

Example

Choosing a primary key

Consider this STUDENT table:

StudentIDNameDateOfBirthTutorGroup
1001Amelia Roberts2008-06-0512A
1002Amelia Roberts2008-06-0512B
1003Ben Clark2008-02-2112A

  1. Test Name: two students are called Amelia Roberts, so Name cannot uniquely identify a row.

  2. Test DateOfBirth and TutorGroup: both values can repeat, so neither attribute uniquely identifies a student.

  3. Test StudentID: each value is different, and the school controls the assignment of IDs, so StudentID is the best primary key.

Common Mistake

Using a name as a primary key

A person’s name is rarely safe as a primary key. The exam may include duplicate names deliberately, so always check whether the attribute uniquely identifies every record.

Composite primary keys

Sometimes one attribute alone is not enough to identify a row, but a combination of attributes is enough.

Definition

Composite primary key

A composite primary key is a primary key made from two or more attributes. The combination of values must be unique, although each individual attribute may repeat.

Composite primary keys are common in tables that represent a relationship between two other tables. For example, a student can enrol on many courses, and a course can have many students. The pair StudentID and CourseID can identify one specific enrolment.

Foreign keys

A relational database becomes “relational” because tables can refer to each other.

Definition

Foreign key

A foreign key is an attribute, or set of attributes, in one table that refers to the primary key of another table. It creates a relationship between the tables and helps maintain referential integrity, meaning links point to existing records.

For example, if ENROLMENT.StudentID is a foreign key referring to STUDENT.StudentID, then every student ID stored in ENROLMENT should match a real student in STUDENT.

Tip

Spotting a foreign key

If an attribute’s values look like identifiers from another table, ask: “Can this value be checked against that other table’s primary key?” If yes, it is probably a foreign key.

The diagram below shows the complete picture: STUDENT and COURSE each have their own primary key, while ENROLMENT uses foreign keys to connect them. Notice that the same identifier can appear many times in ENROLMENT, but only once in the table where it is the primary key.

Three related database tables showing STUDENT, COURSE and ENROLMENT with primary keys, foreign keys and a composite primary key

Example

Choosing keys for enrolments

Suppose ENROLMENT contains these pairs of values: (1001, C101), (1001, C102), (1002, C101) and (1003, C103).

  1. Test StudentID alone: 1001 appears in two rows, so it does not identify one enrolment uniquely.

  2. Test CourseID alone: C101 appears in two rows, so it also does not identify one enrolment uniquely.

  3. Test the combination StudentID and CourseID: each pair appears once, so the pair identifies one student on one course.

  4. Decide the key roles: StudentID and CourseID together form the composite primary key of ENROLMENT, while each individual attribute is also a foreign key to its original table.

Common Mistake

Foreign keys are not necessarily unique

A foreign key value often repeats. For example, many enrolment rows may contain CourseID C101 because many students can take the same course.

Writing table designs clearly

In exam answers, it is useful to write each relation with its attributes in brackets. Flag the primary key attributes clearly.

For example:

  • STUDENT(StudentID, Forename, Surname, TutorGroup)
  • COURSE(CourseID, CourseName, Teacher)
  • ENROLMENT(StudentID, CourseID, DateJoined, Grade)

In ENROLMENT, the primary key is composite: StudentID and CourseID together identify each row. Both are also foreign keys:

  • ENROLMENT.StudentID refers to STUDENT.StudentID
  • ENROLMENT.CourseID refers to COURSE.CourseID

Relationships in plain English

You should be able to explain relationships without overcomplicating them:

  • One student can have many enrolment records.
  • One course can have many enrolment records.
  • Each enrolment record belongs to one student and one course.

This is how a many-to-many relationship between students and courses is represented using an extra table. A many-to-many relationship means many records in one table can be associated with many records in another table.

Key Idea

Keys give tables their meaning

A table’s primary key identifies its own rows. A foreign key points to rows in another table. A composite primary key uses a combination of attributes when one attribute alone is not enough.

Exam technique

In the exam

  1. When defining a relational database, mention tables/relations, rows/records, columns/attributes and links using keys.

  2. For a primary key, always state that it uniquely identifies a record in its table.

  3. For a composite primary key, make clear that the combination is unique, even if the individual attributes repeat.

  4. For a foreign key, state both sides: the table containing the foreign key and the primary key table it refers to.

Self review

Check yourself

  • Why would Name usually be a poor choice of primary key for a STUDENT table?
  • In ENROLMENT(StudentID, CourseID, DateJoined), why might StudentID and CourseID together form a composite primary key?
  • What problem could occur if a foreign key value did not match any primary key value in the referenced table?

Relational databases Revision Guide