Relational Database Setup (SQA National 5 Computing Science): Revision Note

Relational database

• A relational database stores data across multiple linked tables rather than one large flat file

• Each table stores data about one type of entity (for example, Students or Tutors)

• Relationships between the tables are created using keys:

  • A primary key (PK) uniquely identifies each record in a table

  • A foreign key (FK) is a field that links one table to another

• Using keys to link data:

  • Removes data redundancy (duplicate data)

  • Makes the database more efficient to store and maintain

  • Prevents inconsistencies when data changes

Example

• A school originally stores all data in one flat-file table, including each student’s tutor details

• This causes duplication because the tutor’s name and form room appear many times

• To solve this, the data is split into two tables:

• The TutorID field links the two tables

• Now, the tutor’s name and form room are stored only once in the Tutor table

• If a tutor changes their form room, this update happens in one place and applies everywhere the data is linked

• This avoids inconsistency

Referential integrity

What is referential integrity?

• Referential integrity keeps the relationships between tables valid and consistent

• It ensures that every foreign key value in one table matches an existing primary key value in the related table

• If a matching primary key does not exist, the relationship is broken, and the database is said to have lost referential integrity

Example

• Using the Student and Tutor tables:

  • Each student record includes a TutorID (FK)

  • That TutorID must exist in the Tutor table as a primary key (PK)

  • If a tutor is deleted, the database will stop the change until all linked students are updated

Why it matters

• Maintaining referential integrity:

  • Prevents orphaned records (foreign keys with no match)

  • Ensures data accuracy and consistency

  • Makes the database easier to maintain

How it is enforced

• Database management systems (DBMS) can automatically enforce referential integrity by:

  • Preventing updates or deletions that would break table links