Skip to main content
Northern IrelandSoftware Systems DevelopmentSyllabus dot point

What is a relational database, and how do tables, keys and relationships keep data consistent?

Relational database concepts - tables, records and fields, primary and foreign keys, relationships, referential integrity, and the advantages over flat files.

A CCEA A-Level Software Systems Development answer on relational database concepts: tables, records and fields, primary and foreign keys, relationships, referential integrity, and the advantages of a relational database over flat files.

Generated by Claude Opus 4.813 min answer

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Jump to a section
  1. What this dot point is asking
  2. The answer
  3. Examples in context
  4. Try this

What this dot point is asking

CCEA wants you to explain the building blocks of a relational database: tables (relations) made of records (rows) and fields (columns), primary keys and foreign keys, how keys create relationships between tables, and the rule of referential integrity. You must also give the advantages over a flat file (reduced redundancy, better integrity). These concepts underpin entity relationship modelling, normalisation and SQL, so they are tested directly and assumed later.

The answer

Tables, records and fields

Storing each entity in its own table, and linking tables by keys, is what makes the database relational, as opposed to a single flat file that crams everything into one table and repeats data.

Primary and foreign keys

Relationships have a cardinality: one-to-many (one customer has many orders) is the most common; a many-to-many (students to courses) is resolved with a link (junction) table holding two foreign keys.

Referential integrity and advantages over flat files

Referential integrity is the rule that a foreign key value must match an existing primary key value in the related table, or be null; it cannot point to a record that does not exist. This stops orphaned links (an order for a non-existent customer) and blocks deletions that would break a relationship.

A relational database beats a single flat file by:

  • Reducing data redundancy - data is split into tables and stored once, not repeated on every row.
  • Improving integrity and consistency - updates are made in one place, and keys keep links valid, avoiding update, insertion and deletion anomalies.
  • Better security and querying - access can be granted per table, and data can be combined across tables with queries.

Worked example: designing two related tables

Examples in context

Example 1. A school database. Separate Student, Class and Teacher tables each have a primary key. A Class table holds a teacherID foreign key linking to Teacher, giving one teacher to many classes. Because each teacher's details sit once in Teacher, correcting a teacher's surname is a single update, and referential integrity prevents assigning a class to a teacher who is not on the system.

Example 2. Flat file anomalies. A single spreadsheet listing every order with the customer's full address repeated on each row suffers update anomalies (changing an address means editing many rows, risking inconsistency), insertion anomalies (a new customer cannot be stored until they place an order) and deletion anomalies (deleting a customer's only order loses their details). Splitting into Customer and Order tables linked by a key removes all three.

Try this

Q1. Define a primary key and state two properties it must have. [3 marks]

  • Cue. A field (or fields) that uniquely identifies each record; it must be unique and must not be null.

Q2. State what referential integrity ensures about a foreign key value. [2 marks]

  • Cue. A foreign key must match an existing primary key value in the related table, or be null; it cannot refer to a record that does not exist.

Q3. Give one advantage of a relational database over a single flat file. [1 mark]

  • Cue. It reduces data redundancy (data stored once) or improves data integrity and consistency, avoiding update, insertion and deletion anomalies.

Exam-style practice questions

Practice questions written in the style of CCEA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

CCEA 20186 marksExplain the terms primary key and foreign key, and describe how they are used to create a relationship between two tables.
Show worked answer →

A primary key is a field (or combination of fields) that uniquely identifies each record in a table, for example studentID in a Student table. No two records may have the same primary key value, and it cannot be left empty (null).

A foreign key is a field in one table that refers to the primary key of another table, linking the two. For example an Enrolment table might have a studentID foreign key that matches the studentID primary key in the Student table.

A relationship between two tables is created by placing the primary key of one table as a foreign key in the other. For instance, to record which students take which courses, the Enrolment table holds a studentID foreign key (matching Student) and a courseID foreign key (matching Course). Following the foreign key from Enrolment back to Student lets the database join the related records together.

Markers reward a correct definition of primary key (unique, not null identifier) and foreign key (a field matching another table's primary key), and the explanation that a relationship is formed by the foreign key referencing the primary key of the related table.

CCEA 20215 marksState what is meant by referential integrity, and explain two advantages of a relational database over a single flat file.
Show worked answer →

Referential integrity is the rule that a foreign key value must either match an existing primary key value in the related table or be null; it cannot refer to a record that does not exist. This stops, for example, an order being recorded for a customer who is not in the Customer table, and prevents deleting a customer who still has orders, so the links between tables stay valid.

Two advantages of a relational database over a single flat file:

Reduced data redundancy: data is split into related tables and stored once, rather than repeated on every row of a flat file, which saves space and avoids inconsistent duplicates.

Improved data integrity and consistency: because data is stored once and linked by keys, an update is made in one place and referential integrity keeps the relationships valid, so the data stays accurate. Other valid advantages include better security (access can be granted per table), easier querying across tables, and avoiding update, insertion and deletion anomalies.

Markers reward a correct definition of referential integrity (a foreign key must match an existing primary key or be null) and two genuine, distinct advantages of a relational database over a flat file.

Related dot points

Sources & how we know this