What is a relational database, and how are tables linked together?
The structure of a relational database (tables, fields, records, data types, primary and foreign keys), and creating relationships between tables, with the advantages of a relational database over a flat-file one.
An SQA Higher Administration and IT answer on the structure of a relational database, covering tables, fields, records, data types, primary and foreign keys, creating relationships between tables, and the advantages of a relational database over a flat-file one.
Reviewed by: AI editorial process; not yet individually human-reviewed
Have a quick question? Jump to the Q&A page
Jump to a section
What this key area is asking
Databases are a core IT application in Higher Administration and IT, and at Higher you work with relational databases (more than one linked table). The SQA expects you to understand the structure of a relational database, the terms (table, field, record, data type), the role of primary and foreign keys, how to create relationships between tables, and the advantages of a relational database over a single-table (flat-file) one. This underpins everything else you do with databases: searching, calculations, forms and reports.
The structure of a relational database
- Table: a set of records about one thing (for example a Customers table).
- Field: one column, a single item of data (for example Postcode), with a chosen data type.
- Record: one row, the full set of fields for one item (one customer).
- Data type: text, number, date/time, currency, Boolean (yes/no), chosen to match the data, which helps validation and correct sorting.
Primary and foreign keys
The primary key must be unique and present for every record. The foreign key in the "many" table matches the primary key in the "one" table, joining the two so that, for example, every order knows which customer it belongs to.
Creating relationships between tables
A relationship links two tables on a common field (the primary key of one and the foreign key of the other). The most common type is one-to-many: one record in the first table relates to many in the second (one customer has many orders; one product appears on many order lines). Creating the relationship lets you:
- pull data from several tables at once in a query, form or report;
- enforce referential integrity (an order cannot reference a customer who does not exist);
- avoid storing the same data repeatedly.
To create it, you join the primary key of the "one" table to the matching foreign key in the "many" table.
Advantages over a flat-file database
A flat-file database holds everything in one table, which repeats data (a customer's full details on every one of their orders). A relational database splits data into linked tables, giving clear advantages:
- Less duplication: each fact is stored once, saving space.
- Fewer inconsistencies: no contradictory copies of the same data.
- Easier, more accurate updating: change data once and it is correct everywhere.
- Better organisation and scalability: data is structured and easy to extend.
Examples in context
Example 1. A school database. A school uses a Pupils table (PupilID primary key) and a Subjects/Results table that stores PupilID as a foreign key. A one-to-many relationship links one pupil to many results. Each pupil's details are stored once but joined to all their results, illustrating tables, keys and relationships.
Example 2. Flat file versus relational. A club first keeps everything in one table, repeating each member's details on every payment, which wastes space and risks inconsistent addresses. Moving to a relational design (a Members table and a Payments table linked by MemberID) stores each member once, so an address change is made in one place, showing the advantages of a relational database.
Try this
Q1. Define the terms "field" and "record" in a database. [2 marks]
- Cue. A field is a single column, one piece of data (for example Surname); a record is one row, all the fields for one item (for example one customer).
Q2. Describe the purpose of a foreign key. [2 marks]
- Cue. A foreign key is a field in one table that holds the primary key value of another table (for example a CustomerID stored in an Orders table), creating the relationship between the two so related data can be joined.
Exam-style practice questions
Practice questions written in the style of SQA exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.
SQA Higher style4 marksDescribe the purpose of a primary key and a foreign key in a relational database.Show worked answer →
Worth 4 marks. Describe each key, two marks each.
Primary key (about 2 marks). A field (or combination of fields) that uniquely identifies each record in a table, for example a customer ID. No two records can have the same primary key, which prevents duplicate records and lets each record be found exactly.
Foreign key (about 2 marks). A field in one table that links to the primary key of another table, for example a customer ID stored in an orders table. It creates the relationship between the two tables so that related data (such as a customer and their orders) can be joined.
SQA Higher style4 marksDescribe two advantages of a relational database over a flat-file (single-table) database.Show worked answer →
Worth 4 marks. Describe advantages, two marks each.
Less duplication and inconsistency (about 2 marks). Data is split into linked tables, so information (for example a customer's details) is stored only once rather than repeated on every order. This saves space and avoids inconsistent or contradictory data.
Easier, more accurate updating (about 2 marks). Because data is stored once, a change (such as a new address) is made in one place and is correct everywhere it is used, reducing errors and making the database easier to maintain.
Related dot points
- Searching a relational database using advanced functions (queries with multiple criteria, AND/OR logic, comparison and wildcard operators, sorting and multi-level sorting) and using calculations in queries and reports (calculated fields and summary totals).
An SQA Higher Administration and IT answer on searching a relational database with advanced functions, covering queries with multiple criteria, AND/OR logic, comparison and wildcard operators, sorting, and using calculations such as calculated fields and summary totals in queries and reports.
- Creating and using forms for data entry, creating reports (including grouped reports with totals), printing database results in a range of formats, and exporting data to spreadsheet, word-processing and presentation applications.
An SQA Higher Administration and IT answer on creating and using database forms for data entry, creating reports including grouped reports with totals, printing results in a range of formats, and exporting data to spreadsheet, word-processing and presentation applications.
- Using spreadsheet functions, formulae and features at Higher level, including absolute and relative cell references, logical and lookup functions (IF, nested IF, COUNTIF, SUMIF, VLOOKUP), statistical functions, named ranges and conditional formatting.
An SQA Higher Administration and IT answer on spreadsheet functions, formulae and features, covering absolute and relative cell references, logical and lookup functions such as IF, nested IF, COUNTIF, SUMIF and VLOOKUP, statistical functions, named ranges and conditional formatting.
- Managing worksheets and workbooks, consolidating data across sheets, creating dynamic links between software applications, and importing and exporting spreadsheet data from and to external sources.
An SQA Higher Administration and IT answer on managing worksheets and workbooks, consolidating data across sheets, creating dynamic links between applications, and importing and exporting spreadsheet data to and from external sources.
- Creating and working with word-processed documents using advanced features (styles, sections, headers and footers, page and section breaks, columns, tables, templates, table of contents, track changes and comments) to produce well-structured business documents.
An SQA Higher Administration and IT answer on advanced word-processing features, covering styles, sections, headers and footers, breaks, columns, tables, templates, a table of contents, track changes and comments, used to produce well-structured business documents.