What does the Database Design and Development area cover in SQA Higher Computing Science?

It is an optional section of the question paper (Section 2, you sit either it or Web Design and Development) and an optional assignment task. It covers analysing a database problem and designing the data model with entity-relationship diagrams, entities and attributes, relationships and cardinality, and primary, foreign and compound keys; documenting it in a data dictionary; entity and referential integrity; validation of input; and writing SQL to query (SELECT, joins, ORDER BY, wildcards, computed values, aggregates and GROUP BY) and to change data (INSERT, UPDATE, DELETE).

What is referential integrity in a database?

Referential integrity means that every foreign-key value must match an existing primary-key value in the table it refers to (or be empty where allowed), so a record can only refer to something that really exists. It prevents orphan records: you cannot insert a foreign key for a record that does not exist, and deleting a record that others refer to is either blocked or cascaded so no dangling references are left. Entity integrity, separately, requires every record to have a unique, non-empty primary key.

How do you join two tables in SQL at Higher?

You join two tables by matching a foreign key in one table to the primary key it refers to in the other, in the WHERE clause, for example WHERE Order.customerID = Customer.customerID. This pairs each order with its customer. The newer FROM Order JOIN Customer ON Order.customerID = Customer.customerID form does the same thing. Omitting the join condition returns every combination of rows, which is incorrect.

What do aggregate functions and GROUP BY do in SQL?

Aggregate functions summarise many rows into one value: COUNT (number of rows), SUM (total), AVG (average), MIN (smallest) and MAX (largest). On their own they summarise the whole result set. With GROUP BY, the aggregate is calculated separately for each group, for example one count and average price per product category. Any non-aggregated field in the SELECT must also appear in the GROUP BY clause.

Why is the WHERE clause so important in UPDATE and DELETE?

The WHERE clause selects exactly which records an UPDATE changes or a DELETE removes. Without a WHERE clause, an UPDATE changes every record in the table and a DELETE removes every record, which is a common cause of accidental mass changes and data loss. A safe habit is to test the WHERE with a SELECT first, and to keep backups, before running an UPDATE or DELETE in a real database.

ScotlandComputer Science

SQA Higher Computing Science Area Database Design and Development: a complete overview of analysis and design, ER diagrams and keys, integrity and validation, and SQL

A deep-dive SQA Higher Computing Science guide to the Database Design and Development area. Covers analysis and design (ER diagrams, entities, relationships, cardinality and keys), the data dictionary, entity and referential integrity, validation, and SQL querying and data manipulation.

Generated by Claude Opus 4.816 min readHigherUpdated 2026-06-16

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

Jump to a section

What Database Design and Development actually demands
Analysis and design
Integrity and validation
SQL querying
SQL data manipulation
How Database Design and Development is examined
Check your knowledge

What Database Design and Development actually demands

Database Design and Development (DDD) is an optional pathway in Higher Computing Science: in the question paper you sit either the Database section or the Web section, and the same choice appears in the assignment. It runs from modelling a real problem as linked tables through to writing SQL that queries and changes the data. The examiners reward correct ER modelling, precise use of keys and integrity rules, and accurate SQL on prepopulated databases of three or more linked tables.

This guide walks through the key areas of DDD, then sets out the patterns the SQA repeats. Each key area has a matching dot-point page with practice questions; this overview ties them together.

Analysis and design

The area opens with analysis and design. An entity is a thing we store data about and its attributes are its fields; entities become tables. Relationships link entities with a cardinality (most often one-to-many), shown in an entity-relationship diagram with a crow's foot at the many end; a many-to-many relationship is resolved with a link entity. A primary key uniquely identifies a record, a foreign key refers to another table's primary key, and a compound key uses two or more attributes together. A data dictionary documents each attribute's name, type, size and constraints.

Integrity and validation

Integrity and validation keep the data trustworthy. Entity integrity requires a unique, non-empty primary key. Referential integrity requires every foreign key to match an existing primary key, preventing orphan records on insert or delete. Validation checks input as it is entered: a presence check (not empty), a restricted choice check (an allowed set) and a field length check.

SQL querying

SQL querying reads data with SELECT, filtered by WHERE, joining tables by matching a foreign key to a primary key, sorted with ORDER BY. Wildcards with LIKE match text patterns, computed values calculate new columns, aliases name them, and aggregate functions (COUNT, SUM, AVG, MIN, MAX) with GROUP BY summarise rows per group.

SQL data manipulation

SQL data manipulation changes data: INSERT adds records, UPDATE changes them (SET with a WHERE), and DELETE removes them. The critical rule is that UPDATE and DELETE without a WHERE affect every record. Referential integrity governs the implications across linked tables.

How Database Design and Development is examined

A typical SQA profile for DDD:

Writing SQL. Most marks come from writing correct SELECT queries (often with a join, an aggregate and GROUP BY or ORDER BY) and INSERT, UPDATE and DELETE statements.
Design. Drawing ER diagrams, identifying keys and cardinality, and completing a data dictionary.
Explanation. Referential and entity integrity, validation choices, and the implications of data-manipulation operations.

Check your knowledge

A mix of design, SQL and explanation questions covering DDD. Attempt them, then check against the solutions.

State what an entity becomes in the finished database. (1 mark)
State the most common cardinality between two linked tables at Higher. (1 mark)
State what referential integrity requires of a foreign key. (1 mark)
State the SQL clause that joins two tables by their keys. (1 mark)
Name the aggregate function that returns the number of rows. (1 mark)
State what happens if a DELETE is run without a WHERE clause. (1 mark)

Sources & how we know this

SQA Higher Computing Science Course Specification (C816 76) — SQA (2023)